Завантажити файл, який використовується в відеоуроці:
Пов'язані випадають спіскі.xls (216,5 KiB, 1 137 завантажень)
Щоб зрозуміти про що піде мова в статті спочатку необхідно розуміти що таке випадає і як його створити . Тепер спробуємо розібратися що означає вираз "пов'язаний список, що випадає". Я б ще назвав такий список залежним. Тобто коли список значень одного списку залежить від значення, обраного в іншому списку, що випадає або просто забитого в клітинку. Уявімо ситуацію: є осередок А2. У ній створено список, що випадає зі значеннями: Овочі, Фрукти, М'ясо, Напої. А в осередку В2 нам потрібен такий список, щоб значення цього самого списку змінювалися в залежності від того, яке значення ми вкажемо в осередку А2 - тобто список обраної категорії продуктів. Наприклад вибрали в А2 значення Овочі - в В2 з'явився список, що випадає, що містить значення: Морква, Капуста, Картопля, Редиска, Помідори. Вибрали в А2 М'ясо - в В2 з'явився список, що випадає, що містить значення: Яловичина, Телятина, Свинина, Курка, Індичка. І т.д.
Для початку нам потрібно створити всі ці списки. Щось на зразок цього:
Далі для кожного з цих списків необхідно призначити іменований діапазон . Створити можна будь-яким способом з описаних в цій статті. Головне пам'ятати - якщо самі списки розташоване на аркуші, відмінному від того, на якому списки випадають - то обов'язково створювати іменований діапазон з призначенням області дії - Книга.
У доданому до статті прикладі діапазони мають імена категорій - їх можна бачити в заголовках.
Якщо ваші категорії містять пробіл - необхідно замінити його на нижнє підкреслення (_) або видалити, тому що в якості іменованого діапазону таке значення не підійде і нічого в результаті не вийде.
Створення залежних списків
В осередку А2 створюємо "список списків" - основний список, на підставі значень якого буде створюватися другий список. Цей список може бути створений будь-яким способом ( як створити список, що випадає ). Назвемо його Список категорій.
В осередку В2 потрібно створити список на підставі формули, хоч по суті і так само, як і інші: вкладка Дані (Data) - Перевірка даних (Data validation) - Список (List). Але тепер замість прямої вказівки імені списку необхідно вказати посилання на іменований діапазон, який ми виберемо в Списку категорій (осередок А2), на підставі його імені. У цьому нам допоможе функція ДВССИЛ (INDIRECT). Просто записуємо цю формулу в полі Джерело (Source): = ДВССИЛ ($ A2) 
На що звернути увагу: якщо ви плануєте поширювати такий список на стовпець, то посилання повинно виглядати саме так: $ A2. Перед цифрою не повинно бути знаку долара ($ A $ 2 - неправильно). Інакше залежний список буде завжди формуватися виключно на підставі значенні осередку А2.
Джерело з іншої книги
Самі списки товару можуть знаходиться і в іншій книзі. Якщо книга називається Книга зі спіском.xls і на Лист1 в осередку А1 в цій книзі знаходиться ім'я потрібного нам списку, то формула буде виглядати так:
= ДВССИЛ ( " '[Книга зі спіском.xls] Лист1'!" & $ A $ 1)
На що звернути увагу: краще завжди перед ім'ям книги і після імені листа ставити апостроф - '. Так ви уникнете проблем і непорозумінь, якщо ім'я листа або книги містить прогалини і інші специфічні символи. На відміну від списків всередині однієї книги в даному випадку знак долара повинен бути і перед буквою і перед цифрою. В іншому випадку можливі помилки (якщо, звичайно, це не було зроблено спеціально з розумінням того, що робилося).
Обмеження: даний спосіб створення списків хороший, але не обійшлося і без ложки дьогтю. Навіть двох:
- обидві книги повинні бути відкриті. Якщо ви закриєте книгу зі списками, то отримаєте помилку - списки, що випадають просто перестануть працювати
- створені подібним чином пов'язані списки не працюватимуть з динамічними іменованими діапазонами
І нічого з цими обмеженнями не поробиш при подібному підході.
завантажити приклад
Tips_Lists_Connect_Validation.xls (26,5 KiB, 16 027 завантажень)
Так само див .:
випадають списки
іменовані діапазони
Динамічні іменовані діапазони
Відео уроки { "Bottom bar": { "textstyle": "static", "textpositionstatic": "bottom", "textautohide": true, "textpositionmarginstatic": 0, "textpositiondynamic": "bottomleft", "textpositionmarginleft": 24, " textpositionmarginright ": 24," textpositionmargintop ": 24," textpositionmarginbottom ": 24," texteffect ":" slide "," texteffecteasing ":" easeOutCubic "," texteffectduration ": 600," texteffectslidedirection ":" left "," texteffectslidedistance " : 30, "texteffectdelay": 500, "texteffectseparate": false, "texteffect1": "slide", "texteffectslidedirection1": "right", "texteffectslidedistance1": 120, "texteffecteasing1": "easeOutCubic", "texteffectduration1": 600 , "texteffectdelay1" 1000, "texteffect2": "slide", "texteffectslidedirection2": "right", "texteffectslidedistance2": 120, "texteffecteasing2": "easeOutCubic", "texteffectduration2": 600, "texteffectdelay2": 1500, " textcss ":" display: block; padding: 12px; text-align: left; "," textbgcss ":" display: block; position: absolute; top: 0px; left: 0px; width: 100%; height: 100% ; background-color: # 333333; opacity: 0.6; filter: a lpha (opacity = 60); "," titlecss ":" display: block; position: relative; font: bold 14px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; color: #fff; "," descriptioncss ":" display: block; position: relative; font: 12px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; color: #fff; margin-top: 8px; "," buttoncss ":" display: block; position: relative; margin-top: 8px; "," texteffectresponsive ": true," texteffectresponsivesize ": 640," titlecssresponsive ":" font-size: 12px; "," descriptioncssresponsive ":" display: none! important; "," buttoncssresponsive ": "", "addgooglefonts": false, "googlefonts": "", "textleftrightpercentforstatic": 40}}
