Нещодавно дочка звернулася з питанням, чи не можна в Excel випадає в осередку список зробити контекстним, наприклад, залежать від змісту комірки, що знаходиться зліва від осередку зі списком (рис. 1)? Я досить давно не використовував в роботі випадають списки, тому для початку вирішив освіжити свої знання з питання перевірки даних в Excel.
Мал. 1. Склад списку залежить від змісту сусідній осередку
Команда Перевірка даних знаходиться на вкладці Дані, область Робота з даними.
Примітка. Іноді команда Перевірка даних може бути недоступна:
- Можливо, в даний час вводяться дані. Під час введення даних в осередок команда Перевірка даних недоступна. Щоб завершити введення даних, натисніть клавішу ENTER або ESC.
- Можливо, аркуш захищений або є загальним. Якщо аркуш захищений або є загальним, змінити параметри перевірки даних неможливо. Зніміть захист або скасуйте режим «загальний».
- Можливо, таблиця Excel пов'язана з вузлом SharePoint. Неможливо додати перевірку даних в таблицю Excel, яка пов'язана з вузлом SharePoint. Щоб додати перевірку даних, необхідно видалити зв'язок таблиці Excel або перетворити її в діапазон.
На жаль, Excel в своєму стандарті дозволяє робити списки тільки на основі:
- імені масиву
- діапазону комірок
- прямого перерахування елементів списку (рис. 2).
Примітка. Елементи списку вводите через стандартний роздільник елементів списку Microsoft Windows (в українському Excel за замовчуванням це крапка з комою).
Мал. 2. Можливі джерела списку: вгорі - ім'я масиву; посередині - діапазон комірок; внизу - елементи списку
Спроба ввести формулу в полі Джерело діалогового вікна Перевірка вводятьсязначень закінчується невдачею (рис. 3). Видно, що Excel не сприйняв значення осередку D2 ( "колір"), як ім'я масиву, і просто включив це значення в якості єдиного елемента списку.
Мал. 3. Неприпустимий джерело списку - формула
Примітки. Ширина списку визначається шириною осередки, для якої застосовується перевірка даних. Ширину комірки можна налаштувати так, щоб не обрізати допустимі записи, ширина яких перевищує ширину списку.
Переконайтеся, що увімкнено Архів допустимих значень. В іншому випадку поряд з вічком не буде відображена стрілка списку. Хоча обмеження на введення значень в осередок працювати буде.
Щоб вказати, як обробляти порожні (нульові) значення, встановіть прапорець Ігнорувати порожні клітинки. При включеному прапорці осередок можна буде залишити порожній.
Якщо допустимі значення задані ім'ям діапазону комірок, серед яких є порожня клітинка, установка прапорця Ігнорувати порожні клітинки дозволить вводити в перевіряється осередок будь-які значення.
Після зміни процедури перевірки одного осередку можна автоматично застосувати ці зміни до всіх інших осередків, які мають такі ж параметри. Для цього відкрийте діалогове вікно Перевірка даних і на вкладці Параметри встановіть прапорець Поширити зміни на інші осередки з тим же умовою.
І все ж в Excel є одна функція непрямої дії. Англійською мовою у неї говорить назва - INDIRECT. Російською - назва функції ні про що - ДВССИЛ ... У чому ж полягає непряме дію? На відміну від інших функцій Excel, ДВССИЛ повертатися не значення, яке зберігається в осередку, а посилання, що зберігається в осередку. Не зрозуміло? Сам «продирався» через це з трудом 🙂 Спробую пояснити. Ось що написано в довідці Excel: ДВССИЛ - повертає посилання, задане текстовим рядком. Посилання негайно обчислюються для виведення їх вмісту (рис. 4).
Мал. 4. Як працює функція ДВССИЛ: вгорі - формули, внизу - значення
Розумію, що якщо ви вперше зіткнулися з функцією ДВССИЛ, то розібратися складно. Пробуйте, експериментуйте, і розуміння з часом прийде.
Мал. 5. Формування списку, залежить від значення в лівій клітинці