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

Вход

Регистрация

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

 

= Мир MS Excel/ранжир в зависимости от двух столбцов - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » ранжир в зависимости от двух столбцов (Формулы/Formulas)
ранжир в зависимости от двух столбцов
АЛЕКСАНДР1986 Дата: Пятница, 22.04.2016, 13:10 | Сообщение № 1
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 169
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
всем добрый день!
есть изначально желтая таблица (в область таблицы в конце могут входить пустые строки) нужно получить красную таблицу которая представлена на примере одного субъекта РФ!
для каждого субъекта РФ из столбца "E" свой ранжир муниципальных образований
сначала должны идти наименования где встречается "район" в порядке ранжира и по алфавиту
потом где встречается "город" в порядке ранжира и по алфавиту
ну а потом где встречается "образований"
наверное легче это будет сделать с помощью дополнительных столбцов!
заранее спасибо за помощь!
К сообщению приложен файл: 6379001.xlsm (41.1 Kb)
 
Ответить
Сообщениевсем добрый день!
есть изначально желтая таблица (в область таблицы в конце могут входить пустые строки) нужно получить красную таблицу которая представлена на примере одного субъекта РФ!
для каждого субъекта РФ из столбца "E" свой ранжир муниципальных образований
сначала должны идти наименования где встречается "район" в порядке ранжира и по алфавиту
потом где встречается "город" в порядке ранжира и по алфавиту
ну а потом где встречается "образований"
наверное легче это будет сделать с помощью дополнительных столбцов!
заранее спасибо за помощь!
Автор - АЛЕКСАНДР1986
Дата добавления - 22.04.2016 в 13:10
krosav4ig Дата: Пятница, 22.04.2016, 13:43 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 2347
Репутация: 989 ±
Замечаний: 0% ±

Excel 2007,2010,2013
как вариант
Доп. столбец+Сортировка и фильтр
формула в столбце [vba]
Код
=МАКС(ЕЧИСЛО(ПОИСК({"район":"город":"образований"};F7))*({3:2:1}/1%%+G7%))
[/vba]
К сообщению приложен файл: 4537984.xlsm (48.8 Kb)

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460
 
Ответить
Сообщениекак вариант
Доп. столбец+Сортировка и фильтр
формула в столбце [vba]
Код
=МАКС(ЕЧИСЛО(ПОИСК({"район":"город":"образований"};F7))*({3:2:1}/1%%+G7%))
[/vba]
Автор - krosav4ig
Дата добавления - 22.04.2016 в 13:43
АЛЕКСАНДР1986 Дата: Пятница, 22.04.2016, 13:46 | Сообщение № 3
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 169
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
к сожалению это после себя тянет еще кучу расчетов и ссылок поэтому нужны все таки формулы без фильтров!
 
Ответить
Сообщениек сожалению это после себя тянет еще кучу расчетов и ссылок поэтому нужны все таки формулы без фильтров!
Автор - АЛЕКСАНДР1986
Дата добавления - 22.04.2016 в 13:46
_Boroda_ Дата: Пятница, 22.04.2016, 16:14 | Сообщение № 4
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16674
Репутация: 6479 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
"Шелеховский муниципальное образование" мешается. Переделал его в "Шелеховский районное муниципальное образование". Тем более, что это действительно Шелеховский район.
2 вида - с сортировкой по субъекту и субъект остается на своем месте.
Для первого допстолбец
Код
=--ЕСЛИ(E7="";10^12;СЧЁТЕСЛИ(E$7:E$431;"<"&E7)&ПРОСМОТР(;-ПОИСК({"":"район":"город"};F7);{3:1:2})&ТЕКСТ(СТРОКА();"000"))

для второго
Код
=--ЕСЛИ(E7="";10^12;СЧЁТЕСЛИ(E$6:E6;"*ДОБАВЛ")&ТЕКСТ(СЧЁТЕСЛИ(E$7:E$431;"<"&E7);"000")&ПРОСМОТР(;-ПОИСК({"":"район":"город"};F7);{3:1:2})&ТЕКСТ(СТРОКА();"000"))

и формула вывода значений (допстолбец в Н)
Код
=ИНДЕКС(E$1:E$431;ПРАВБ(НАИМЕНЬШИЙ($H$7:$H$431;СТРОКА(K1));3))

До 999 строк. Иначе нужно менять 000 на 0000
К сообщению приложен файл: 6379001_1.xlsm (84.1 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение"Шелеховский муниципальное образование" мешается. Переделал его в "Шелеховский районное муниципальное образование". Тем более, что это действительно Шелеховский район.
2 вида - с сортировкой по субъекту и субъект остается на своем месте.
Для первого допстолбец
Код
=--ЕСЛИ(E7="";10^12;СЧЁТЕСЛИ(E$7:E$431;"<"&E7)&ПРОСМОТР(;-ПОИСК({"":"район":"город"};F7);{3:1:2})&ТЕКСТ(СТРОКА();"000"))

для второго
Код
=--ЕСЛИ(E7="";10^12;СЧЁТЕСЛИ(E$6:E6;"*ДОБАВЛ")&ТЕКСТ(СЧЁТЕСЛИ(E$7:E$431;"<"&E7);"000")&ПРОСМОТР(;-ПОИСК({"":"район":"город"};F7);{3:1:2})&ТЕКСТ(СТРОКА();"000"))

и формула вывода значений (допстолбец в Н)
Код
=ИНДЕКС(E$1:E$431;ПРАВБ(НАИМЕНЬШИЙ($H$7:$H$431;СТРОКА(K1));3))

До 999 строк. Иначе нужно менять 000 на 0000
Автор - _Boroda_
Дата добавления - 22.04.2016 в 16:14
АЛЕКСАНДР1986 Дата: Пятница, 22.04.2016, 16:57 | Сообщение № 5
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 169
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
в очередной раз спасибо за помощь!
не подскажите только для понимания что за ячейки считает
Код
=СЧЁТЕСЛИ(E$7:E$431;"<"&E7)

и что означают цифры {3:1:2} для функции просмотр?

И такой еще вопрос можно как то муниципалы выстраивать по алфавиту внутри субъекта РФ имеется ввиду те у которых одинаковые значения внутри районов или городских округов (может быть с добавлением еще дополнительных столбцов)? 2 вариант намного предпочтительней!


Сообщение отредактировал АЛЕКСАНДР1986 - Пятница, 22.04.2016, 17:36
 
Ответить
Сообщениев очередной раз спасибо за помощь!
не подскажите только для понимания что за ячейки считает
Код
=СЧЁТЕСЛИ(E$7:E$431;"<"&E7)

и что означают цифры {3:1:2} для функции просмотр?

И такой еще вопрос можно как то муниципалы выстраивать по алфавиту внутри субъекта РФ имеется ввиду те у которых одинаковые значения внутри районов или городских округов (может быть с добавлением еще дополнительных столбцов)? 2 вариант намного предпочтительней!
Автор - АЛЕКСАНДР1986
Дата добавления - 22.04.2016 в 16:57
_Boroda_ Дата: Пятница, 22.04.2016, 23:44 | Сообщение № 6
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16674
Репутация: 6479 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Цитата АЛЕКСАНДР1986, 22.04.2016 в 16:57, в сообщении № 5
что за ячейки считает
=СЧЁТЕСЛИ(E$7:E$431;"<"&E7)
Считает кол-во ячеек, меньших Е7. Для текста это тоже применимо. Например, "а"<"я".
Цитата АЛЕКСАНДР1986, 22.04.2016 в 16:57, в сообщении № 5
что означают цифры {3:1:2} для функции просмотр?
Если находит город, то пишет 2, если города нет, но есть район, то пишет 1, если ничего нет, то 3. "Образований" искать не нужно, они сортируются по вхождению слова "ДОБАВЛ" в первый столбец.
Цитата АЛЕКСАНДР1986, 22.04.2016 в 16:57, в сообщении № 5
можно как то муниципалы выстраивать по алфавиту внутри субъекта РФ
Если Вы про муниципалов с одинаковым количеством, то допстолбец можно так:
Код
=--ЕСЛИ(E7="";10^15;СЧЁТЕСЛИ(E$6:E6;"*ДОБАВЛ")&ПРОСМОТР(;-ПОИСК({"":"район":"город"};F7);{3:1:2})&ТЕКСТ(99-G7;"00")&ТЕКСТ(СЧЁТЕСЛИ(F$7:F$431;"<"&F7);"000")&ТЕКСТ(СТРОКА();"000"))

Он формируется в порядке 1 2 33 444 555 (пробелы добавлены для наглядности), где
1 Порядковый номер субъекта в исходной таблице
2 Район-город-образований
3 Чем больше кол-во, тем меньше этот номер
4 Порядковый номер муниципала в исходной таблице
5 Номер строки (нужен для ИНДЕКСа в конечной таблице)
К сообщению приложен файл: 6379001_2.xlsm (70.8 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
Цитата АЛЕКСАНДР1986, 22.04.2016 в 16:57, в сообщении № 5
что за ячейки считает
=СЧЁТЕСЛИ(E$7:E$431;"<"&E7)
Считает кол-во ячеек, меньших Е7. Для текста это тоже применимо. Например, "а"<"я".
Цитата АЛЕКСАНДР1986, 22.04.2016 в 16:57, в сообщении № 5
что означают цифры {3:1:2} для функции просмотр?
Если находит город, то пишет 2, если города нет, но есть район, то пишет 1, если ничего нет, то 3. "Образований" искать не нужно, они сортируются по вхождению слова "ДОБАВЛ" в первый столбец.
Цитата АЛЕКСАНДР1986, 22.04.2016 в 16:57, в сообщении № 5
можно как то муниципалы выстраивать по алфавиту внутри субъекта РФ
Если Вы про муниципалов с одинаковым количеством, то допстолбец можно так:
Код
=--ЕСЛИ(E7="";10^15;СЧЁТЕСЛИ(E$6:E6;"*ДОБАВЛ")&ПРОСМОТР(;-ПОИСК({"":"район":"город"};F7);{3:1:2})&ТЕКСТ(99-G7;"00")&ТЕКСТ(СЧЁТЕСЛИ(F$7:F$431;"<"&F7);"000")&ТЕКСТ(СТРОКА();"000"))

Он формируется в порядке 1 2 33 444 555 (пробелы добавлены для наглядности), где
1 Порядковый номер субъекта в исходной таблице
2 Район-город-образований
3 Чем больше кол-во, тем меньше этот номер
4 Порядковый номер муниципала в исходной таблице
5 Номер строки (нужен для ИНДЕКСа в конечной таблице)
Автор - _Boroda_
Дата добавления - 22.04.2016 в 23:44
StoTisteg Дата: Суббота, 23.04.2016, 13:32 | Сообщение № 7
Группа: Авторы
Ранг: Старожил
Сообщений: 1161
Репутация: 103 ±
Замечаний: 0% ±

Excel 2010
[offtop] АЛЕКСАНДР1986, Кашира с 1 января — городской округ. И вообще не мучайтесь, добавьте ОКАТО и различайте по третьему знаку.

Интуитивно понятный код - это когда интуитивно понятно, что это код.
 
Ответить
Сообщение[offtop] АЛЕКСАНДР1986, Кашира с 1 января — городской округ. И вообще не мучайтесь, добавьте ОКАТО и различайте по третьему знаку.
Автор - StoTisteg
Дата добавления - 23.04.2016 в 13:32
Мир MS Excel » Вопросы и решения » Вопросы по Excel » ранжир в зависимости от двух столбцов (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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