Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Отсортировать по алфавиту от А до Я + дополнительный порядок - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Отсортировать по алфавиту от А до Я + дополнительный порядок (Формулы/Formulas)
Отсортировать по алфавиту от А до Я + дополнительный порядок
stalber Дата: Понедельник, 10.09.2018, 15:11 | Сообщение № 1
Группа: Проверенные
Ранг: Обитатель
Сообщений: 369
Репутация: 7 ±
Замечаний: 40% ±

Excel 2007
День добрый.

Дан список:
один_два
три_два
четыре_два
шесть_три
семь_четыре
семь_два
восемь_три
четыре_три

Это правая часть слова, после нижнего подчёркивания, именно в таком порядке сначала Четыре сортируется по алфавиту, потом где есть слово три, и затем где есть слово два:
четыре
три
два

Конечный результат который необходимо увидеть:
семь_четыре
восемь_три
четыре_три
шесть_три
один_два
семь_два
три_два
четыре_два

Помогите пожалуйста формулой.
К сообщению приложен файл: vopros_10092018.xlsx (10.9 Kb)
 
Ответить
СообщениеДень добрый.

Дан список:
один_два
три_два
четыре_два
шесть_три
семь_четыре
семь_два
восемь_три
четыре_три

Это правая часть слова, после нижнего подчёркивания, именно в таком порядке сначала Четыре сортируется по алфавиту, потом где есть слово три, и затем где есть слово два:
четыре
три
два

Конечный результат который необходимо увидеть:
семь_четыре
восемь_три
четыре_три
шесть_три
один_два
семь_два
три_два
четыре_два

Помогите пожалуйста формулой.

Автор - stalber
Дата добавления - 10.09.2018 в 15:11
_Boroda_ Дата: Понедельник, 10.09.2018, 15:30 | Сообщение № 2
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Так нужно?
Код
=ИНДЕКС(C:C;ПРАВБ(НАИМЕНЬШИЙ(ПОИСКПОЗ(ПСТР(C$5:C$12;ПОИСК("_";C$5:C$12)+1;9);F$5:F$7;)/1%%+СТРОКА(C$5:C$12);СТРОКА(D1));3))

Формула массива. Вводится одновременным нажатием Ctrl+Shift+Enter
К сообщению приложен файл: 9996846.xlsx (12.1 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеТак нужно?
Код
=ИНДЕКС(C:C;ПРАВБ(НАИМЕНЬШИЙ(ПОИСКПОЗ(ПСТР(C$5:C$12;ПОИСК("_";C$5:C$12)+1;9);F$5:F$7;)/1%%+СТРОКА(C$5:C$12);СТРОКА(D1));3))

Формула массива. Вводится одновременным нажатием Ctrl+Shift+Enter

Автор - _Boroda_
Дата добавления - 10.09.2018 в 15:30
stalber Дата: Понедельник, 10.09.2018, 15:48 | Сообщение № 3
Группа: Проверенные
Ранг: Обитатель
Сообщений: 369
Репутация: 7 ±
Замечаний: 40% ±

Excel 2007
Спасибо за помощь!

Немного не то, у вас получилось:
семь_четыре
шесть_три
восемь_три
четыре_три
один_два
три_два
четыре_два
семь_два

а необходимо по алфавиту ещё:
семь_четыре
восемь_три
четыре_три
шесть_три
один_два
семь_два
три_два
четыре_два


Сообщение отредактировал awaddwaawddw - Понедельник, 10.09.2018, 15:49
 
Ответить
СообщениеСпасибо за помощь!

Немного не то, у вас получилось:
семь_четыре
шесть_три
восемь_три
четыре_три
один_два
три_два
четыре_два
семь_два

а необходимо по алфавиту ещё:
семь_четыре
восемь_три
четыре_три
шесть_три
один_два
семь_два
три_два
четыре_два

Автор - stalber
Дата добавления - 10.09.2018 в 15:48
Светлый Дата: Понедельник, 10.09.2018, 16:03 | Сообщение № 4
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 0% ±

Excel 2013, 2016
Добрый день!
У меня такая массивная формула получилась:
Код
=ИНДЕКС(C$5:C$12;ПОИСКПОЗ(НАИБОЛЬШИЙ(МУМНОЖ(--(ПСТР(C$5:C$12;ПОИСК("_";C$5:C$12)+1;99)>ТРАНСП(ПСТР(C$5:C$12;ПОИСК("_";C$5:C$12)+1;99)));1^СТРОКА($5:$12))/1%+МУМНОЖ(--(ЛЕВБ(C$5:C$12;ПОИСК("_";C$5:C$12)-1)<ТРАНСП(ЛЕВБ(C$5:C$12;ПОИСК("_";C$5:C$12)-1)));1^СТРОКА($5:$12));СТРОКА(G1));МУМНОЖ(--(ПСТР(C$5:C$12;ПОИСК("_";C$5:C$12)+1;99)>ТРАНСП(ПСТР(C$5:C$12;ПОИСК("_";C$5:C$12)+1;99)));1^СТРОКА($5:$12))/1%+МУМНОЖ(--(ЛЕВБ(C$5:C$12;ПОИСК("_";C$5:C$12)-1)<ТРАНСП(ЛЕВБ(C$5:C$12;ПОИСК("_";C$5:C$12)-1)));1^СТРОКА($5:$12));))

*Можно все +1 и -1 убрать.


Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Понедельник, 10.09.2018, 16:36
 
Ответить
СообщениеДобрый день!
У меня такая массивная формула получилась:
Код
=ИНДЕКС(C$5:C$12;ПОИСКПОЗ(НАИБОЛЬШИЙ(МУМНОЖ(--(ПСТР(C$5:C$12;ПОИСК("_";C$5:C$12)+1;99)>ТРАНСП(ПСТР(C$5:C$12;ПОИСК("_";C$5:C$12)+1;99)));1^СТРОКА($5:$12))/1%+МУМНОЖ(--(ЛЕВБ(C$5:C$12;ПОИСК("_";C$5:C$12)-1)<ТРАНСП(ЛЕВБ(C$5:C$12;ПОИСК("_";C$5:C$12)-1)));1^СТРОКА($5:$12));СТРОКА(G1));МУМНОЖ(--(ПСТР(C$5:C$12;ПОИСК("_";C$5:C$12)+1;99)>ТРАНСП(ПСТР(C$5:C$12;ПОИСК("_";C$5:C$12)+1;99)));1^СТРОКА($5:$12))/1%+МУМНОЖ(--(ЛЕВБ(C$5:C$12;ПОИСК("_";C$5:C$12)-1)<ТРАНСП(ЛЕВБ(C$5:C$12;ПОИСК("_";C$5:C$12)-1)));1^СТРОКА($5:$12));))

*Можно все +1 и -1 убрать.

Автор - Светлый
Дата добавления - 10.09.2018 в 16:03
stalber Дата: Понедельник, 10.09.2018, 16:21 | Сообщение № 5
Группа: Проверенные
Ранг: Обитатель
Сообщений: 369
Репутация: 7 ±
Замечаний: 40% ±

Excel 2007
Спасибо!

Я немного не разобрался в вашей формуле Светлый, но она работает.

Можно как то в формуле сделать привязку к ячейкам в которых прописаны по порядку вторая часть слова, как у меня в экселе в примере, например я там сменю расположение, и чтобы формула это учла и сразу вывела правильную таблицу или в будущем допишу не три окончания а больше.


Сообщение отредактировал awaddwaawddw - Понедельник, 10.09.2018, 16:21
 
Ответить
СообщениеСпасибо!

Я немного не разобрался в вашей формуле Светлый, но она работает.

Можно как то в формуле сделать привязку к ячейкам в которых прописаны по порядку вторая часть слова, как у меня в экселе в примере, например я там сменю расположение, и чтобы формула это учла и сразу вывела правильную таблицу или в будущем допишу не три окончания а больше.

Автор - stalber
Дата добавления - 10.09.2018 в 16:21
_Boroda_ Дата: Понедельник, 10.09.2018, 16:46 | Сообщение № 6
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
необходимо по алфавиту ещё:

Так?
Код
=ИНДЕКС(C:C;ПРАВБ(НАИМЕНЬШИЙ(ПОИСКПОЗ(ПСТР(C$5:C$12;ПОИСК("_";C$5:C$12)+1;9);G$5:G$7;)/1%%%%+СЧЁТЕСЛИ(C$5:C$12;"<"&C$5:C$12)/1%%+СТРОКА(C$5:C$12);СТРОКА(D1));3))


Для увеличенных диапазонов
Код
=ИНДЕКС(C:C;ПРАВБ(НАИМЕНЬШИЙ(ЕСЛИОШИБКА(ПОИСКПОЗ(ПСТР(C$5:C$99;ПОИСК("_";C$5:C$99)+1;9);G$5:G$9;);9^9)/1%%%%+СЧЁТЕСЛИ(C$5:C$99;"<"&C$5:C$99)/1%%+СТРОКА(C$5:C$99);СТРОКА(D1));3))
К сообщению приложен файл: 9920176.xlsx (12.3 Kb) · 9012188.xlsx (12.6 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
необходимо по алфавиту ещё:

Так?
Код
=ИНДЕКС(C:C;ПРАВБ(НАИМЕНЬШИЙ(ПОИСКПОЗ(ПСТР(C$5:C$12;ПОИСК("_";C$5:C$12)+1;9);G$5:G$7;)/1%%%%+СЧЁТЕСЛИ(C$5:C$12;"<"&C$5:C$12)/1%%+СТРОКА(C$5:C$12);СТРОКА(D1));3))


Для увеличенных диапазонов
Код
=ИНДЕКС(C:C;ПРАВБ(НАИМЕНЬШИЙ(ЕСЛИОШИБКА(ПОИСКПОЗ(ПСТР(C$5:C$99;ПОИСК("_";C$5:C$99)+1;9);G$5:G$9;);9^9)/1%%%%+СЧЁТЕСЛИ(C$5:C$99;"<"&C$5:C$99)/1%%+СТРОКА(C$5:C$99);СТРОКА(D1));3))

Автор - _Boroda_
Дата добавления - 10.09.2018 в 16:46
Светлый Дата: Понедельник, 10.09.2018, 19:09 | Сообщение № 7
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 0% ±

Excel 2013, 2016
Можно как то в формуле сделать привязку к ячейкам в которых прописаны по порядку вторая часть слова, как у меня в экселе в примере

Код
=ИНДЕКС(C$5:C$12;ПОИСКПОЗ(НАИБОЛЬШИЙ(МУМНОЖ(--(ЛЕВБ(C$5:C$12;ПОИСК("_";C$5:C$12))<ТРАНСП(ЛЕВБ(C$5:C$12;ПОИСК("_";C$5:C$12))));1^СТРОКА($5:$12))-ПОИСКПОЗ(ПСТР(C$5:C$12;ПОИСК("_";C$5:C$12)+1;99);E$5:E$7;)/1%;СТРОКА(G1));МУМНОЖ(--(ЛЕВБ(C$5:C$12;ПОИСК("_";C$5:C$12))<ТРАНСП(ЛЕВБ(C$5:C$12;ПОИСК("_";C$5:C$12))));1^СТРОКА($5:$12))-ПОИСКПОЗ(ПСТР(C$5:C$12;ПОИСК("_";C$5:C$12)+1;99);E$5:E$7;)/1%;))


Программировать проще, чем писать стихи.
 
Ответить
Сообщение
Можно как то в формуле сделать привязку к ячейкам в которых прописаны по порядку вторая часть слова, как у меня в экселе в примере

Код
=ИНДЕКС(C$5:C$12;ПОИСКПОЗ(НАИБОЛЬШИЙ(МУМНОЖ(--(ЛЕВБ(C$5:C$12;ПОИСК("_";C$5:C$12))<ТРАНСП(ЛЕВБ(C$5:C$12;ПОИСК("_";C$5:C$12))));1^СТРОКА($5:$12))-ПОИСКПОЗ(ПСТР(C$5:C$12;ПОИСК("_";C$5:C$12)+1;99);E$5:E$7;)/1%;СТРОКА(G1));МУМНОЖ(--(ЛЕВБ(C$5:C$12;ПОИСК("_";C$5:C$12))<ТРАНСП(ЛЕВБ(C$5:C$12;ПОИСК("_";C$5:C$12))));1^СТРОКА($5:$12))-ПОИСКПОЗ(ПСТР(C$5:C$12;ПОИСК("_";C$5:C$12)+1;99);E$5:E$7;)/1%;))

Автор - Светлый
Дата добавления - 10.09.2018 в 19:09
stalber Дата: Вторник, 11.09.2018, 00:00 | Сообщение № 8
Группа: Проверенные
Ранг: Обитатель
Сообщений: 369
Репутация: 7 ±
Замечаний: 40% ±

Excel 2007
Благодарю за помощь то что нужно!


Сообщение отредактировал awaddwaawddw - Вторник, 11.09.2018, 02:04
 
Ответить
СообщениеБлагодарю за помощь то что нужно!

Автор - stalber
Дата добавления - 11.09.2018 в 00:00
stalber Дата: Вторник, 11.09.2018, 02:35 | Сообщение № 9
Группа: Проверенные
Ранг: Обитатель
Сообщений: 369
Репутация: 7 ±
Замечаний: 40% ±

Excel 2007
В случае если ниже формулу опускать появляется не нужная информация, я её в файле выделил, можно пожалуйста сделать чтобы были просто пустые ячейки.
К сообщению приложен файл: vvv11092018.xlsx (45.7 Kb)


Сообщение отредактировал awaddwaawddw - Вторник, 11.09.2018, 02:38
 
Ответить
СообщениеВ случае если ниже формулу опускать появляется не нужная информация, я её в файле выделил, можно пожалуйста сделать чтобы были просто пустые ячейки.

Автор - stalber
Дата добавления - 11.09.2018 в 02:35
ASWP Дата: Вторник, 11.09.2018, 07:58 | Сообщение № 10
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 183
Репутация: 24 ±
Замечаний: 0% ±

Excel 2007
awaddwaawddw, нужно добавить в начало формулы
Код
=ЕСЛИ(ЕПУСТО(A1);"";
К сообщению приложен файл: vvv11092018-1-.xlsx (48.5 Kb)


я знаю что ничего не знаю, но другие не знают и этого

Сообщение отредактировал ASWP - Вторник, 11.09.2018, 08:09
 
Ответить
Сообщениеawaddwaawddw, нужно добавить в начало формулы
Код
=ЕСЛИ(ЕПУСТО(A1);"";

Автор - ASWP
Дата добавления - 11.09.2018 в 07:58
_Boroda_ Дата: Вторник, 11.09.2018, 09:32 | Сообщение № 11
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
В случае если ниже формулу опускать

Не в этом дело. В первом примере над списком была пустая строка в 1:1, а во втором примере ее нет (см. файл _3)
Если Вы протягиваете формулу больше, чем на 999 строк, то вторым аргументом ПРАВБ нужно поставить не 3, а 4 и вместо 9^9 написать 7^8
А если начало таки с первой строки, то можно так
Код
=ИНДЕКС(A:A;ПРАВБ(НАИМЕНЬШИЙ(ЕСЛИОШИБКА(ПОИСКПОЗ(ПСТР(A$1:A$999;ПОИСК("_";A$1:A$999)+1;9);F$2:F$15;);7^8)/1%%%%+СЧЁТЕСЛИ(A$1:A$999;"<"&A$1:A$999)/1%%+СТРОКА(A$1:A$999);СТРОКА(D1));3))&""

Кстати, значения в столбце А можно не подряд писать, а с промежуточными пустыми ячейками
К сообщению приложен файл: vvv11092018_2.xlsx (43.1 Kb) · vvv11092018_3.xlsx (42.4 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
В случае если ниже формулу опускать

Не в этом дело. В первом примере над списком была пустая строка в 1:1, а во втором примере ее нет (см. файл _3)
Если Вы протягиваете формулу больше, чем на 999 строк, то вторым аргументом ПРАВБ нужно поставить не 3, а 4 и вместо 9^9 написать 7^8
А если начало таки с первой строки, то можно так
Код
=ИНДЕКС(A:A;ПРАВБ(НАИМЕНЬШИЙ(ЕСЛИОШИБКА(ПОИСКПОЗ(ПСТР(A$1:A$999;ПОИСК("_";A$1:A$999)+1;9);F$2:F$15;);7^8)/1%%%%+СЧЁТЕСЛИ(A$1:A$999;"<"&A$1:A$999)/1%%+СТРОКА(A$1:A$999);СТРОКА(D1));3))&""

Кстати, значения в столбце А можно не подряд писать, а с промежуточными пустыми ячейками

Автор - _Boroda_
Дата добавления - 11.09.2018 в 09:32
stalber Дата: Вторник, 11.09.2018, 22:58 | Сообщение № 12
Группа: Проверенные
Ранг: Обитатель
Сообщений: 369
Репутация: 7 ±
Замечаний: 40% ±

Excel 2007
Благодарю за решение верхней задачи.

Возникла ещё одна похожая задача, условие чуть изменилось. Помогите пожалуйста.

Необходимо теперь отфильтровать от А до Я, не по правой части, а по левой части + в словах добавились числа слева.

Необходима подобная формула что и выше, там где есть запас от 1 до 999 строк A$1:A$999.

Файл прикрепил, справа в файле я написал то что нужно увидеть, а слева сами данные.
К сообщению приложен файл: aaa110920182249.xlsx (8.8 Kb)


Сообщение отредактировал awaddwaawddw - Вторник, 11.09.2018, 22:58
 
Ответить
СообщениеБлагодарю за решение верхней задачи.

Возникла ещё одна похожая задача, условие чуть изменилось. Помогите пожалуйста.

Необходимо теперь отфильтровать от А до Я, не по правой части, а по левой части + в словах добавились числа слева.

Необходима подобная формула что и выше, там где есть запас от 1 до 999 строк A$1:A$999.

Файл прикрепил, справа в файле я написал то что нужно увидеть, а слева сами данные.

Автор - stalber
Дата добавления - 11.09.2018 в 22:58
_Boroda_ Дата: Среда, 12.09.2018, 10:13 | Сообщение № 13
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
условие чуть изменилось
Нууу, это не "чуть", это довольно сильно усложняет решение
Код
=ИНДЕКС(A:A;ПРАВБ(НАИМЕНЬШИЙ(ЕСЛИОШИБКА(ПОИСКПОЗ(ПСТР(ЛЕВБ(A$1:A$99;ПОИСК("_";A$1:A$99)-1);ПОИСК(" ";A$1:A$99)+1;99);D$2:D$15;)/1%%%%+ПОИСК(ПСТР(A$1:A$99;ПОИСК("_";A$1:A$99)+1;2);"водвдеодпясетрчеше")/1%%+СТРОКА(A$1:A$99);9^99);СТРОКА(E1));3))&""
К сообщению приложен файл: 8501470.xlsx (11.5 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
условие чуть изменилось
Нууу, это не "чуть", это довольно сильно усложняет решение
Код
=ИНДЕКС(A:A;ПРАВБ(НАИМЕНЬШИЙ(ЕСЛИОШИБКА(ПОИСКПОЗ(ПСТР(ЛЕВБ(A$1:A$99;ПОИСК("_";A$1:A$99)-1);ПОИСК(" ";A$1:A$99)+1;99);D$2:D$15;)/1%%%%+ПОИСК(ПСТР(A$1:A$99;ПОИСК("_";A$1:A$99)+1;2);"водвдеодпясетрчеше")/1%%+СТРОКА(A$1:A$99);9^99);СТРОКА(E1));3))&""

Автор - _Boroda_
Дата добавления - 12.09.2018 в 10:13
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Отсортировать по алфавиту от А до Я + дополнительный порядок (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!