Список, що випадає в ms excel на основі перевірки даних - сумісно з microsoft excel 2018, excel

При заповненні осередків даними, часто необхідно обмежити можливість введення певних списком значень. Наприклад, є осередок, куди користувач повинен внести назву департаменту, вказавши де він працює. Логічно, попередньо створити список департаментів організації і дозволити користувачеві лише вибирати значення з цього списку. Цей підхід допоможе прискорити процес введення та зменшити кількість помилок.

Список, що випадає можна створити за допомогою Перевірки даних

У цій статті створимо Список, що випадає з допомогою Перевірки даних (Дані / Робота з даними / Перевірка даних) з типом даних Список.

Список, що випадає можна сформувати по різному.

А. Найпростіший випадає - введення елементів списку безпосередньо в полі Джерело

Найпростішим способом створення списку, що випадає є введення елементів списку безпосередньо в полі Джерело інструменту Перевірка даних.

Припустимо, в осередку B1 необхідно створити список, що випадає для введення одиниць вимірювань. Виділимо осередок B1 і викличемо Перевірку даних.

Якщо в поле Джерело вказати через крапку з комою одиниці виміру шт; кг; кв.м; куб.м. то вибір буде обмежений цими чотирма значеннями.

Тепер дивимося, що вийшло. Виділимо осередок B1. При виділенні комірки праворуч від осередку з'являється квадратна кнопка зі стрілкою для вибору елементів зі списку.

Недоліки цього підходу: елементи списку легко втратити (наприклад, видаливши рядок або стовпець, що містять осередок B1); не зручно вводити велику кількість елементів. Підхід годиться для маленьких (3-5 значень) незмінних списків.
Перевага. швидкість створення списку.

Б. Введення елементів списку в діапазон (на тому ж аркуші, що і список, що випадає)

Елементи для списку можна розмістити в діапазоні на аркуші EXCEL, а потім в поле Джерело інструменту Перевірки даних вказати посилання на цей діапазон.

Перевага. наочність переліку елементів і простота його модифікації. Підхід годиться для рідко змінюються списків.
Недоліки. якщо додаються нові елементи, то доводиться вручну змінювати посилання на діапазон. Правда, в якості джерела можна визначити відразу більш широкий діапазон, наприклад, A1: A100. Але, тоді, що випадає може містити порожні рядки (якщо, наприклад, частина елементів була видалена або список тільки що був створений). Щоб порожні рядки зникли необхідно зберегти файл.

Позбудемося спочатку від другого недоліку - розмістимо перелік елементів списку на іншому аркуші.

B. Введення елементів списку в діапазон (на будь-якому аркуші)

У правилах Перевірки даних (також як і умовного форматування) не можна прямо вказати посилання на діапазони іншого листа (див. Файл прикладу):

Нехай осередки, які повинні містити Список, що випадає. розміщені на аркуші Приклад,

а діапазон з переліком елементів розмістимо на іншому аркуші (на аркуші Список в файлі прикладу).

Для створення списку, елементи якого розташовані на іншому аркуші, можна використовувати два підходи. Один заснований на використанні іменованого діапазону. інший - функції ДВССИЛ ().

• виділяємо А1: А4,
• натискаємо Формули / Певні імена / Присвоїти ім'я
• в поле Ім'я вводимо Спісок_елементов. в поле Область вибираємо Книга;

Тепер на аркуші Приклад. виділимо діапазон комірок, які будуть містити Список, що випадає.

• викликаємо Перевірку даних;
• в поле Джерело вводимо посилання на створене ім'я: = Спісок_елементов.

Примітка
Якщо передбачається, що перелік елементів буде доповнюватися, то можна відразу виділити діапазон більшого розміру, наприклад, А1: А10. Однак, в цьому випадку Список, що випадає може містити порожні рядки.

Позбутися від порожніх рядків і врахувати нові елементи переліку дозволяє Динамічний діапазон. Для цього при створенні Імені Спісок_елементов в поле Діапазон необхідно записати формулу = зміщений (Список! $ A $ 1 ;;; СЧЁТЗ (Список! $ A: $ A))

Використання функції СЧЁТЗ () передбачає, що заповнення діапазону комірок (A: A), який містить елементи, ведеться без пропусків рядків (див. Файл прикладу. Лист Динамічний діапазон).

Використовуємо функцію ДВССИЛ ()

Альтернативним способом посилання на перелік елементів, розташованих на іншому аркуші, є використання функції ДВССИЛ (). На аркуші Приклад. виділяємо діапазон комірок, які будуть містити список, що випадає, викликаємо Перевірку даних, в Джерелі вказуємо = ДВССИЛ ( "список! A1: A4").

Недолік. при перейменуванні листа - формула перестає працювати. Як це можна частково обійти см. В статті Визначаємо ім'я листа.

Введення елементів списку в діапазон комірок, що знаходиться в іншій книзі

Якщо необхідно перенести діапазон з елементами списку в іншу книгу (наприклад, в книгу Істочнік.xlsx), то потрібно зробити наступне:

• в книзі Істочнік.xlsx створіть необхідний перелік елементів;
• в книзі Істочнік.xlsx діапазону комірок містить перелік елементів надайте Ім'я. наприклад СпісокВнеш;
• відкрийте книгу, в якій передбачається розмістити осередки з списком, що випадає;
• виділіть потрібний діапазон комірок, викличте інструмент Перевірка даних, в поле Джерело вкажіть = ДВССИЛ ( "[Істочнік.xlsx] лист1! СпісокВнеш");

При роботі з переліком елементів, розташованим в іншій книзі, файл Джерело .xlsx повинен бути відкритий і перебувати в тій же папці, інакше необхідно вказувати повний шлях до файлу. Взагалі посилань на інші листи краще уникати або використовувати Особисту книгу макросів Personal.xlsx або Надбудови.

Якщо немає бажання привласнювати ім'я діапазону в файлі Істочнік.xlsx. то формулу потрібно змінити на = ДВССИЛ ( "[Істочнік.xlsx] лист1! $ A $ 1: $ A $ 4")

ПОРАДА:
Якщо на аркуші багато осередків з правилами Перевірки даних. то можна використовувати інструмент Виділення групи осередків (Головна / Знайти і виділити / Виділення групи осередків). Опція Перевірка даних цього інструменту дозволяє виділити осередки, для яких проводиться перевірка допустимості даних (задана за допомогою команди Дані / Робота з даними / Перевірка даних). При виборі перемикача Всіх будуть виділені всі такі осередки. При виборі опції Етіхже виділяються тільки ті осередки, для яких встановлені ті ж правила перевірки даних, що і для активного осередку.

Примітка.
Якщо випадає кількість контактів перевищує 25-30 значень, то працювати з ним стає незручно. Список, що випадає одночасно відображає тільки 8 елементів, а щоб побачити інші, потрібно користуватися смугою прокрутки, що не завжди зручно.

В EXCEL не передбачена регулювання розміру шрифту списку. При великій кількості елементів має сенс сортувати список елементів і використовувати додаткову класифікацію елементів (тобто один список, що випадає розбити на 2 і більше).

Наприклад, щоб ефективно працювати зі списком співробітників налічує понад 300 співробітників, то його слід спочатку впорядкувати в алфавітному порядку. Потім створити список, що випадає. що містить букви алфавіту. Другий список, що випадає повинен містити тільки ті прізвища, які починаються з літери, обраної першим списком. Для вирішення такого завдання може бути використана структура Зв'язаний список або Вкладений пов'язаний список.

пов'язані статті

Прочитайте інші статті, які вирішують схожі завдання в MS Excel. Це дозволить Вам вирішувати широкий клас подібних задач.