Связанный список в MS EXCEL и кавычки в заголовках

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

В статье Связанный список (linked ListBox) уже было показано как создавать такой список. Здесь рассмотрим ситуацию, когда в элементах выпадающего списка, от которого зависит связанный, будут содержаться двойные кавычки " (см. ячейку А6 на рисунке ниже).

Напомним, что такое Связанный список. Элементы Связанного списка зависят от значения в определенной ячейке. В нашем случае, если в ячейке А7 содержится значение ЗАО "Большие копыта", то Связанный список (ячейка А10) будет содержать элементы товар1, товар2, ... (выпадающий список формируется на основе диапазона С7:С12). Если в ячейке А7 содержится значение ООО "Малые рога", то Связанный список (ячейка А10) будет содержать элементы услуга1, услуга2, ... (см. диапазон B7:B12).

Причем здесь вообще кавычки? Какое отношение они имеют к Связанному списку?

Чтобы в этом разобраться, попробуем создать Связанный список, используя подход изложенный в статье предложенный в статье Связанный список. Для этого сделаем следующее:

1. Создадим Выпадающий список на основе проверки данных в ячейке А7 (содержащий названия компаний) на основе заголовков таблички С6:B12

2. Создадим именованные диапазоны. Для работы Связанного списка использованы Имена (вкладка Формулы/ группа Определенные имена/ Диспетчер имен). Эти имена созданы с помощью команды Создать из выделенного (вкладка Формулы/ группа Определенные имена). Для этого выделите D6:E12, вызовите Создать из выделенного, оставьте галочку только у флажка В строке выше. Так как кавычки в именах не допускаются, то они будут проигнорированы (в начале и конце имени) или заменены нижним подчеркиванием (_) в середине имени. В итоге будут созданы имена ЗАО__Большие_копыта и ООО__Малые_рога.

3. Теперь создадим Выпадающий список на основе проверки данных в ячейке А10, т.е. Связанный список с помощью формулы =ДВССЫЛ(А7), который, естественно, работать не будет, т.к. формула вернет значения ЗАО "Большие копыта" или ООО "Малые рога", которые не совпадут с ранее созданными именами ЗАО__Большие_копыта и ООО__Малые_рога.

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

1. Как и прежде создадим Выпадающий список на основе проверки данных в ячейке А7 (содержащий названия компаний) на основе заголовков таблички С6:B12

2. При создании выпадающего списка в ячейке А10, т.е. Связанного списка используем формулу =СМЕЩ(C7;;ПОИСКПОЗ(A7;C6:D6;0)-1;6), которая будет возвращать либо диапазон С7:С12 или B7:B12 в зависимости от выбранного в А7 значения.

Разберем подробнее: формула ПОИСКПОЗ(A7;C6:D6;0) будет возвращать 1 или 2 (в зависимости от того, какое название компании из диапазона C6:D6 содержится в А7 (первое или второе)), тем самым задавая смещение по столбцам относительно ячейки С7.

Совет. Подробнее см. статью про функцию СМЕЩ().

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 5 (2 оценок)