Завдання. створити в осередку, що випадає для зручного введення інформації. Варіанти для списку повинні братися із заданого динамічного діапазону, тобто якщо завтра в нього внесуть зміни - наприклад, видалять непотрібні елементи або допишуть ще кілька нових - вони повинні автоматично відбитися в випадаючому списку:
Виділіть діапазон варіантів для списку (A1: A5 в нашому прикладі вище) і на Головній (Home) вкладці натисніть кнопку Форматувати як таблицю (Home - Format as Table). Дизайн можна вибрати будь-який - це ролі не грає:
Тепер виділіть комірки, в якій ви хочете створити списки, що випадають (в нашому прикладі вище - це D2) і виберіть в старих версіях Excel в меню Дані - Перевірка (Data - Validation). а в нових натисніть кнопку Перевірка даних (Data Validation) на вкладці Дані (Data). У вікні, на вкладці Параметри (Settings) виберіть варіант Список (List) і введіть в поле Джерело (Source) ось таку формулу:
Залишилося тільки натиснути на ОК. Якщо тепер дописати до нашої таблиці нові елементи, то вони будуть автоматично в неї включені, а значить - додадуться до нашого випадному списку. З видаленням - те ж саме.
Фактично, цим ми створюємо іменований динамічний діапазон, який посилається на дані з нашої розумної таблиці. Тепер ім'я цього діапазону можна ввести у вікні створення списку в поле Джерело (Source).
Функція СЧЁТЗ (COUNTA) підраховує кількість непустих осередків в стовпці з прізвищами, тобто кількість рядків в діапазоні для списку. Функція зміщений (OFFSET) формує посилання на діапазон з потрібними нам іменами і використовує наступні аргументи:
- A2 - початкова осередок
- 0 - зсув початкової осередки по вертикалі вниз на задану кількість рядків
- 0 - зсув початкової осередки по горизонталі вправо на задану кількість стовпців
- СЧЁТЗ (A2: A100) - розмір одержуваного на виході діапазону по вертикалі, тобто стільки рядків, скільки у нас зайнятих осередків в списку
- 1 - розмір одержуваного на виході діапазону по горизонталі, тобто один стовпець
Тепер виділіть комірки, де ви хочете створити списки, що випадають, і виберіть в старих версіях Excel в меню Дані - Перевірка (Data - Validation). У вікні, на вкладці Параметри (Settings) виберіть варіант Список (List) і введіть в поле Джерело (Source) ось таку формулу:
Після натискання на ОК ваш динамічний список в виділених осередках готовий до роботи.