Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Как при одинаковом населении получить из справочника разные города
 
#1
19.11.2019 06:49:28

Доброго дня всем.

Столкнулся с задачей, которую не получается решить.

Суть: дана таблица из 3-х столбцов «страна», «город», «население». Необходимо по каждой из стран (столбец B) найти 5 городов  (столбец С) с наименьшим населением (столбец D).

Мое решение: в ячейке I1 раскрывающейся список стран, в столбце J формула  массива -=НАИМЕНЬШИЙ(ЕСЛИ($B$4:$B$82=$I$1;$D$4:$D$82;"");I4) – эта формула точно определяет 5 наименьших кхм… численностей,  населения  по каждой стране. Но когда необходимо напротив найденных численностей указать название соответствующего города возникает проблема.

Так, формула массива в столбце K =ИНДЕКС($C$4:$C$82;ПОИСКПОЗ(НАИМЕНЬШИЙ(ЕСЛИ($B$4:$B$82=$I$1;$D$4:$D$82;"-");I4);$D$4:$D$82;0)) выдает название города соответствующее численности в столбце J, но если существует 2 одинаковых значения численности, то будет проставлено название города, идущее первым сверху в списке, а не искомое. Я специально для этого примера проставил по некоторым городам одинаковые значения населения.

Например если в раскрывающемся списке выбрать США, то первым городом в списке будет Москва, т.к. по ней значение населения соответствует Вашингтону, но Москва идет выше по списку городов.

Собственно вопрос  - как сделать так, что бы в столбце K указывалось название города соответствующее населению и стране.
 
 
 
#2
19.11.2019 07:21:29
если допустим доп столбец то вот вариант
Лень двигатель прогресса, доказано!!!
 
 
 
#3
19.11.2019 07:30:03
Добрый день!
Можно использовать такую массивную формулу:
Код
=ИНДЕКС(C:C;ПРАВБ(НАИМЕНЬШИЙ(ПОДСТАВИТЬ(D$4:D$82;СИМВОЛ(160);)*100+СТРОКА($4:$82);I4);2))
А если в исходных данных предварительно удалить символы с кодом 160, то формула будет проще:
Код
=ИНДЕКС(C:C;ПРАВБ(НАИМЕНЬШИЙ(D$4:D$82*100+СТРОКА($4:$82);I4);2))
И для численности можно использовать массивную формулу:
Код
=НАИМЕНЬШИЙ(--ПОДСТАВИТЬ(D$4:D$82;СИМВОЛ(160););I4)
 
 
 
#4
19.11.2019 07:33:08
название темы:

как при одинаковом населении получить из справочника разные города

ответ: посчитайте дополнительно, сколько городов с таким населением уже выведено в таблице
формула массива
Код
=ИНДЕКС($C:$C;НАИМЕНЬШИЙ(ЕСЛИ(($B$4:$B$82=$I$1)*($D$4:$D$82=J7);СТРОКА($D$4:$D$82));СЧЁТЕСЛИ($J$3:J7;J7)))
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#5
19.11.2019 07:44:43
Сначала не понял задачу.
Код
=ИНДЕКС(C:C;ПРАВБ(НАИМЕНЬШИЙ(ЕСЛИ($B$4:$B$82=$I$1;ПОДСТАВИТЬ(D$4:D$82;СИМВОЛ(160);)*100+СТРОКА($4:$82));I4);2))
Код
=НАИМЕНЬШИЙ(ЕСЛИ($B$4:$B$82=$I$1;--ПОДСТАВИТЬ(D$4:D$82;СИМВОЛ(160);));I4)

*Если в списке будет больше 99 строк, то 100 заменим на 1000, а в функции ПРАВБ вместо 2 запишем 3.

Изменено: Светлый - 19.11.2019 07:48:13
 
 
 
#6
19.11.2019 10:00:16
Всем спасибо, но:

Сергей - в вашем файле нет дополнительных столбцов, на которые вы ссылаетесь.

Ігор Гончаренко - ваш вариант работает, но не могли бы вы пояснить, зачем нужны расчеты в столбце Е, сортировка столбца D. Ну и в целом синтаксис формулы. Т.к. я пытался ее адаптировать из примера в реальный файл и там не получил искомого результата.

Светлый - у меня Excel 2010, может по этому у меня нет функции ПРАВБ?

Всем спасибо, но нет ли еще вариантов? Может через СМЕЩ?
 
 
 
#7
19.11.2019 10:27:30
в колонке Е я искал города с одинаковім населением, можете его очистить, посортировал Д с той же целью, сортировка не важна для работы формулы
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#8
19.11.2019 11:39:24
Цитата
Gaust написал:
нет функции ПРАВБ
Используйте ПРАВСИМВ. И формула массива вводится одновременным нажатием Ctrl+Shift+Enter.
Цитата
Gaust написал:
Может через СМЕЩ?
*Пожалуйста. Тоже массивная, без контроля кода 160:
Код
=СМЕЩ(C$3;ПРАВСИМВ(НАИМЕНЬШИЙ(ЕСЛИ(B$4:B$82=I$1;D$4:D$82*100+СТРОКА($1:$79));I4);2);)
Изменено: Светлый - 19.11.2019 12:00:59
 
 
 
#9
19.11.2019 12:06:30
Цитата
Gaust написал:
но: Сергей  - в вашем файле нет дополнительных столбцов
есть только он был скрыт, вот с отображенным с поправленной формулой
Лень двигатель прогресса, доказано!!!
 
 
 
#10
19.11.2019 12:09:25
Gaust, Вы в курсе, что для Германии ряд значений не числовые, а текстовые?
 
 
 
#11
19.11.2019 12:15:56
Все работает! Всем спасибо!
 
 
 
Страницы: 1
Наверх