Связанный список в 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 .

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


© Copyright 2013 - 2020 Excel2.ru. All Rights Reserved