Формирование списка по условию

Дата: Четверг, 07.10.2021, 10:57 | Сообщение № 1

Группа: Пользователи

Ранг: Участник

Сообщений: 88

Репутация: 0 ±

Замечаний: 0% ±

Excel 2003

Доморощенных знаний не хватает, прошу помощи.
Задача (см. приложение): есть список адресов с условными именами м01-м09, конкретный адрес выбирается в ячейке а1. Нужно, чтобы в ячейке b1 была возможность выбора из списка, сформированного из перечня g5-g13 при совпадении условных имён адресов. Например: в a1 выбрано "Благовещенск", в b1 д.б. список "Эксплуатация КА, СЭВИ этап-1, Запуск 6-й этап".
Заранее благодарен.

К сообщению приложен файл: 4966025.xls (188.5 Kb)

Дата: Четверг, 07.10.2021, 13:44 | Сообщение № 2

Группа: Проверенные

Ранг: Ветеран

Сообщений: 514

Репутация: 185 ±

Замечаний: 0% ±

Excel 2013/2016

С доп. списком в диапазоне L5:L13 (можно скрыть) и формулой в диспетчере имен.
Поскольку у вас указан Excel 2003, то делал под эту версию.

К сообщению приложен файл: 8022779.xls (221.0 Kb)

Дата: Четверг, 07.10.2021, 14:33 | Сообщение № 3

Группа: Пользователи

Ранг: Участник

Сообщений: 88

Репутация: 0 ±

Замечаний: 0% ±

Excel 2003

Egyptian, спасибо. Да, именно 2003. К сожалению, файл открывается непонятно как (см. приложение), а вернее - не открывается.

К сообщению приложен файл: 5777685.png (60.0 Kb)

Дата: Четверг, 07.10.2021, 14:46 | Сообщение № 4

Группа: Пользователи

Ранг: Участник

Сообщений: 88

Репутация: 0 ±

Замечаний: 0% ±

Excel 2003

В 2010-м без проблем, когда сохраняешь в 2003-м, пишет - возможны ошибки - и они таки есть. А нужно именно в 2003-м...

Serge_007

Дата: Четверг, 07.10.2021, 15:06 | Сообщение № 5

Группа: Админы

Ранг: Местный житель

Сообщений: 16475

Репутация: 2749 ±

Замечаний: ±

Excel 2016

vev57, пробуйте

К сообщению приложен файл: 20211007_vev57.xls (265.0 Kb)

ЮMoney:41001419691823 | WMR:126292472390

Дата: Четверг, 07.10.2021, 15:10 | Сообщение № 6

Группа: Проверенные

Ранг: Ветеран

Сообщений: 514

Репутация: 185 ±

Замечаний: 0% ±

Excel 2013/2016

vev57, В одном из столбцов, по ошибке оставил несовместимую формулу, может в этом была причина. Также почистил книгу от лишних элементов.
Пробуйте.

К сообщению приложен файл: 8022779-1.xls (33.0 Kb)

Дата: Четверг, 07.10.2021, 16:25 | Сообщение № 7

Группа: Пользователи

Ранг: Участник

Сообщений: 88

Репутация: 0 ±

Замечаний: 0% ±

Excel 2003

Serge_007, примерно так было сделано, но не устраивает дублирование строк в разделе "Тема". Спасибо.

Serge_007

Дата: Четверг, 07.10.2021, 16:30 | Сообщение № 8

Группа: Админы

Ранг: Местный житель

Сообщений: 16475

Репутация: 2749 ±

Замечаний: ±

Excel 2016

Цитата vev57, 07.10.2021 в 16:25, в сообщении № 7 (

)

не устраивает дублирование строк в разделе "Тема"

Можно и не дублировать, но это неоправданно сильно усложняет формулу
Старайтесь не использовать несколько идентификаторов в одной строке
Одно из правил БД: одна запись - одна строка

ЮMoney:41001419691823 | WMR:126292472390

Дата: Четверг, 07.10.2021, 16:42 | Сообщение № 9

Группа: Пользователи

Ранг: Участник

Сообщений: 88

Репутация: 0 ±

Замечаний: 0% ±

Excel 2003

Egyptian, То, что надо! Ещё бы понять, зачем тут ссылка на R5...и.д.!R$5:ИНДЕКС(и.д.!$L$5:$L$13;СЧЁТЕСЛИ(и.д.!$L$5:$L$13;"*?")). Или в данном случае это чисто формально?

Дата: Четверг, 07.10.2021, 17:16 | Сообщение № 10

Группа: Проверенные

Ранг: Ветеран

Сообщений: 514

Репутация: 185 ±

Замечаний: 0% ±

Excel 2013/2016

Цитата vev57, 07.10.2021 в 16:42, в сообщении № 9 (

)

Или в данном случае это чисто формально?

Никаких формальностей, это ошибка - наверное сместился диапазон из-за закрепления по строкам.
Поправьте на

Код

=и.д.!$L$5:INDEX(и.д.!$L$5:$L$13;COUNTIF(и.д.!$L$5:$L$13;"*?"))

и.д.!$L$5 нужен в начале, чтобы уточнить для выпадающего списка, что он должен начинаться с этой ячейки. Затем

Код

СЧЁТЕСЛИ(и.д.!$L$5:$L$13;"*?"))

подсчитывает количество символов и передает индексу. В итоге получается диапазон: начальная ячейка+последняя заполненная ячейка.

Дата: Четверг, 07.10.2021, 20:27 | Сообщение № 11

Группа: Пользователи

Ранг: Участник

Сообщений: 88

Репутация: 0 ±

Замечаний: 0% ±

Excel 2003

Цитата Serge_007, 07.10.2021 в 16:30, в сообщении № 8 (

)

Можно и не дублировать, но это неоправданно сильно усложняет формулу

Для меня важнее удобство использования "программы" людьми, который в EXELе не очень разбираются

Дата: Четверг, 07.10.2021, 21:05 | Сообщение № 12

Группа: Пользователи

Ранг: Участник

Сообщений: 88

Репутация: 0 ±

Замечаний: 0% ±

Excel 2003

Цитата Egyptian, 07.10.2021 в 17:16, в сообщении № 10 (

)

...это ошибка - наверное сместился диапазон из-за закрепления по строкам

Поясни, плз, функцию вот этого аргумента:
СТРОКА($A1)...($А8)
при построении списка

Сообщение отредактировал vev57 - Четверг, 07.10.2021, 21:06

Дата: Четверг, 07.10.2021, 23:07 | Сообщение № 13

Группа: Проверенные

Ранг: Ветеран

Сообщений: 514

Репутация: 185 ±

Замечаний: 0% ±

Excel 2013/2016

Это счетчик для последовательного извлечения номеров строк в возрастающем порядке. Т.е. в процессе работы функция ЕСЛИ выдает массив строк и передает их
функции НАИМЕНЬШИЙ. Та, в свою очередь, извлекает их благодаря нехитрому счетчику и последовательно, при протяге вниз, передает в качестве аргументов функции ИНДЕКС.

http://www.excelworld.ru/publ/hacks/tricks/extract_unique/17-1-0-10

Serge_007

Дата: Пятница, 08.10.2021, 10:18 | Сообщение № 14

Группа: Админы

Ранг: Местный житель

Сообщений: 16475

Репутация: 2749 ±

Замечаний: ±

Excel 2016

Цитата vev57, 07.10.2021 в 21:05, в сообщении № 12 (

)

Поясни, плз, функцию вот этого аргумента:
СТРОКА($A1)...($А8)
при построении списка

ЮMoney:41001419691823 | WMR:126292472390