Випадаючий список при перевірці даних є часто використовуваною функцією в Excel. У цій статті ми представимо два методи автоматичного оновлення розкривного списку.
У нашій попередній статті Як створити випадаючий список із діапазону комірок у вашому Excel, ми детально ознайомили вас із розкривним списком. Зміна вихідного діапазону також впливає на розкривний список. Щоразу, коли ви додаєте або видаляєте елемент у діапазоні, вам потрібно перевірити розкривний список у цільовій комірці. І це може бути дуже дратуюче. Але тепер ми знайшли для вас два ефективних методи. Використовуючи ці методи, розкривний список оновлюватиметься автоматично.
Спосіб 1: Використовуйте функцію OFFSET
У цьому методі ви можете використовувати функцію OFFSET для перевірки даних. Малюнок нижче - діапазон джерел на аркуші. У цьому асортименті 6 найменувань товарів.
- Клацніть цільову клітинку, в якій потрібно створити список. Тут ми клацнемо клітинку A2 на іншому аркуші.
- А потім клацніть на стрічці вкладку «Дані».
- Після цього натисніть кнопку «Перевірка даних» на панелі інструментів.
- У новому спливаючому вікні виберіть “Список” у текстовому полі “Дозволити”.
- А потім введіть цю формулу в текстове поле "Джерело":
= OFFSET ('Діапазон джерела'! $ A $ 2,0,0, COUNTA ('Діапазон джерела'! $ A: $ A) -1)
Ви можете змінити певні елементи у формулі відповідно до фактичного аркуша.
- А потім натисніть кнопку “OK” на стрічці, щоб зберегти налаштування.
Таким чином, у комірці створено випадаючий список. Наступного разу, коли ви додасте або видалите елемент із діапазону джерел, елементи у списку автоматично оновляться. Наприклад, ми додаємо новий елемент до початкового діапазону в комірці A8. І є 7 предметів. У випадаючому списку ви також можете побачити 7 елементів.
Наступного разу, коли вам потрібно буде створити випадаючий список та звернутися до іншого діапазону, ви можете скористатися цим методом. Але використовуючи цей метод, вам потрібно переконатися, що в тому ж стовпці чи порожньому елементі в межах діапазону немає додаткового елемента.
Спосіб 2: Визначте назву та таблицю використання
За винятком використання формули, ви також можете створити таблицю на аркуші та визначити назву для цього діапазону.
- Виберіть діапазон джерела.
- А потім клацніть на стрічці вкладку «Формула».
- Після цього натисніть кнопку «Визначити ім’я» на панелі інструментів.
- Далі ви побачите нове вікно. Введіть ім’я у текстове поле „Ім'я”. Тут ми введемо “Товар”.
- А потім введіть діапазон у текстове поле "Відноситься до".
- Далі натисніть кнопку “OK”, щоб зберегти діапазон.
- На цьому кроці клацніть клітинку в межах вихідного діапазону.
- А потім натисніть стрічку на вкладці «Вставити».
- Після цього натисніть кнопку «Таблиця» на панелі інструментів.
- У вікні “Створити таблицю” перевірте можливість заголовків відповідно до ваших потреб.
- Далі натисніть кнопку “OK”, щоб зберегти налаштування.
- Тепер клацніть цільову клітинку, в якій потрібно створити розкривний список.
- Повторіть кроки 2-4 у попередній частині.
- А потім введіть цю формулу в текстове поле "Джерело":
= Товар
Це ім'я визначення, яке ви створили на кроці 4.
- Далі натисніть “OK”, щоб зберегти перевірку даних.
І тепер ви закінчили налаштування. Наступного разу, коли ви додасте новий елемент у діапазоні джерел, розкривний список також оновиться. Коли потрібно видалити елемент, не забудьте видалити рядок таблиці. В іншому випадку в списку буде порожній пункт.
Порівняння двох методів
Обидва ці методи дуже ефективні. Але все-таки вони мають переваги піщаних недоліків. Ви також можете звернутися до таблиці нижче.
|
порівняння |
Використовуйте функцію OFFSET |
Визначте назву та таблицю використання |
|
Переваги |
1. Цей метод містить менше кроків. І це легко виконати.
2. Використовуючи функцію, на робочому аркуші не буде безладу. |
1. За допомогою цього методу ви все ще можете вводити елементи в інші комірки того самого рядка чи стовпця.
2. Коли вам також потрібно використовувати таблицю або визначити ім'я, ви можете заощадити багато часу на виконання інших завдань. |
|
Недоліки |
1. Якщо ви не знайомі з функцією OFFSET, при зміні формули можуть виникнути помилки.
2. Коли в діапазоні є інші елементи або порожні клітинки, у розкривному списку буде безлад. |
1. У цьому методі є більше кроків. Ви можете витратити більше часу на виконання процесу.
2. Коли ви видаляєте елементи, вам потрібно видалити рядок таблиці, а не лише значення. |
Наступного разу, коли вам потрібно буде створити випадаючий список, який може автоматично оновлюватися, ви можете вибрати один із методів. Обидва вони дуже ефективні.
Виправте помилки файлу Excel
Іноді ви зустрінетесь із корупцією в Excel. І ця катастрофа даних може бути спричинена багатьма різними причинами. Перш ніж виправити ці помилки, вам слід з’ясувати причини. Однак, якщо ви нічого не знаєте про відновлення даних, не намагайтеся виправити файли Excel самостійно. Ви можете проконсультуватися зі спеціалізованою компанією з відновлення для отримання допомоги. Крім того, ви також можете інвестувати інструмент ремонту Excel. Цей інструмент здатний відновити пошкоджені дані xls - легко і швидко. Таким чином, ви отримаєте всі дані та інформацію з цих пошкоджених файлів.
Вступ автора:
Анна Ма - експерт із відновлення даних у DataNumen, Inc., яка є світовим лідером у галузі технологій відновлення даних, в тому числі виправити помилку файлу Word та перспективні програмні продукти для ремонту. Для отримання додаткової інформації відвідайте WWW.datanumen.com