Note: The other languages of the website are Google-translated. Back to English
Увійти  \/ 
x
or
x
Реєстрація  \/ 
x

or

Як створити динамічну перевірку даних та автоматично розширити спадне меню?

Зазвичай, коли ви створюєте випадаючий список на робочому аркуші, випадаючий список не змінюватиметься, коли нові дані вводяться динамічно, у цьому випадку вам потрібно змінити вихідні дані, щоб розширити список вручну. У цій статті я розповім, як зробити динамічну перевірку даних та автоматично розширити розкривний список при введенні нових даних.

Створіть динамічну перевірку даних та автоматично розширте спадне меню, створивши таблицю

Створіть динамічну перевірку даних та автоматично розширте спадне меню, визначивши назву діапазону


стрілка синя права міхур Створіть динамічну перевірку даних та автоматично розширте спадне меню, створивши таблицю

Щоб створити динамічний список перевірки даних, спочатку можна створити таблицю, а потім застосувати функцію перевірки даних. Будь ласка, виконайте наступне:

Створіть формат таблиці для списку вихідних даних:

1. Виберіть список даних, який потрібно використовувати як вихідні дані для випадаючого списку, а потім натисніть Insert > таблиця, в вискочив Створити таблицю діалогове вікно, перевірте У моїй таблиці є заголовки якщо ваш список даних має заголовки, див. знімок екрана:

doc динамічна перевірка даних 1

2. Потім натисніть OK , таблиця створена, див. знімок екрана:

doc динамічна перевірка даних 2

Визначте назву діапазону для таблиці:

3. Потім виберіть таблицю, яку ви створили, але ігноруючи заголовок, і введіть назву для цієї таблиці в Назва і натисніть вводити ключ, див. знімок екрана:

doc динамічна перевірка даних 3

Створіть динамічний список перевірки даних:

4. Визначивши назву таблиці, а потім виділіть клітинки, куди потрібно вставити випадаючий список, і натисніть дані > Перевірка достовірності даних > Перевірка достовірності даних, див. скріншот:

doc динамічна перевірка даних 4

5. І в Перевірка достовірності даних у діалоговому вікні під настройки вкладка, виберіть список від дозволяти розділу, а потім введіть цю формулу: = Намеліст (Намеліст - це ім'я, яке ви створили для таблиці на кроці 3) у джерело текстове поле, див. знімок екрана:

doc динамічна перевірка даних 5

6. Потім натисніть OK , випадаючий список вставлений у вибрані комірки, відтепер, коли ви вводите нове ім'я у список вихідних даних, воно також автоматично додається до випадаючого списку, див.

doc динамічна перевірка даних 6


стрілка синя права міхур Створіть динамічну перевірку даних та автоматично розширте спадне меню, визначивши назву діапазону

Окрім створення таблиці, ви також можете завершити це завдання, визначивши назву діапазону за допомогою формули, виконайте такі дії:

1. Клацання Формули > Визначте ім’я, див. скріншот:

doc динамічна перевірка даних 7

2, в Нове ім'я у діалоговому вікні вкажіть ім’я, яке вам потрібно, і введіть цю формулу: = OFFSET (Аркуш1! $ A $ 2,0,0, COUNTA (Аркуш1! $ A: $ A), 1) в Відноситься до текстове поле, див. знімок екрана:

doc динамічна перевірка даних 8

примітки: У наведеній вище формулі, Sheet1 - назва робочого аркуша містить список даних, A2 - перша комірка у списку даних, A: A - стовпець із списком даних.

3. Клацання OK , а потім перейдіть до Перевірка достовірності даних діалогове вікно, клацнувши дані > Перевірка достовірності даних > Перевірка достовірності даних, В Перевірка достовірності даних діалог, виберіть список від дозволяти розділу, а потім введіть цю формулу: = Намеліст (Намеліст - це ім'я, яке ви створили на кроці 2) у джерело текстове поле, див. знімок екрана:

doc динамічна перевірка даних 9

4. А потім клацніть OK, тепер, коли ви вводите нове ім'я в список даних, випадаючий список буде автоматично розширений. Дивіться знімок екрана:

doc динамічна перевірка даних 10


Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel вирішує більшість ваших проблем і збільшує продуктивність на 80%

  • Повторне використання: Швидко вставте складні формули, діаграми і все, що ви використовували раніше; Шифрувати комірки з паролем; Створити список розсилки та надсилати електронні листи ...
  • Супер формула бар (легко редагувати кілька рядків тексту та формули); Макет читання (легко читати та редагувати велику кількість комірок); Вставте у відфільтрований діапазон...
  • Об’єднати клітинки / рядки / стовпці без втрати даних; Вміст розділених комірок; Об'єднати повторювані рядки / стовпці... Запобігання дублюючим клітинам; Порівняйте діапазони...
  • Виберіть Повторюваний або Унікальний Рядки; Виберіть Пусті рядки (усі клітинки порожні); Супер знахідка та нечітка знахідка у багатьох робочих зошитах; Випадковий вибір ...
  • Точна копія Кілька клітинок без зміни посилання на формулу; Автоматичне створення посилань на кілька аркушів; Вставте кулі, Прапорці та інше ...
  • Витяг тексту, Додати текст, Видалити за позицією, Видаліть пробіл; Створення та друк проміжних підсумків підкачки; Перетворення вмісту комірок та коментарів...
  • Супер фільтр (зберегти та застосувати схеми фільтрів до інших аркушів); Розширене сортування за місяцем / тижнем / днем, частотою та іншим; Спеціальний фільтр жирним, курсивом ...
  • Поєднайте робочі зошити та робочі аркуші; Об’єднати таблиці на основі ключових стовпців; Розділіть дані на кілька аркушів; Пакетне перетворення xls, xlsx та PDF...
  • Понад 300 потужних функцій. Підтримує Office / Excel 2007-2019 та 365. Підтримує всі мови. Простота розгортання на вашому підприємстві чи в організації. Повна функція 30-денної безкоштовної пробної версії. 60-денна гарантія повернення грошей.
вкладка kte 201905

Вкладка Office забезпечує інтерфейс з вкладками для Office і значно спрощує вашу роботу

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
дно офісної таблиці
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Bartek · 2 years ago
    Thank you very much, it is very powerful and simple solution at the same time.
  • To post as a guest, your comment is unpublished.
    Nico · 2 years ago
    thanks for this. Really helpful
  • To post as a guest, your comment is unpublished.
    Tom · 2 years ago
    Do you know how to create a dynamic range using drop boxes to expand and contract the data range to be graphed (without deleting any data or hiding it)? I've only seen one person do this. https://youtu.be/sHfWRb2yUrM
    Unfortunately I need to do this on a mac.
  • To post as a guest, your comment is unpublished.
    Adam · 2 years ago
    Thansk for the info - just what I was looking for!!
  • To post as a guest, your comment is unpublished.
    Alex The Great · 2 years ago
    @Alex The Great Solution was to put name of the sheet, e.g. MyLists, rather than Sheet1 in Step 2
  • To post as a guest, your comment is unpublished.
    Alex The Great · 2 years ago
    I get "The source currently evaluates to an error" on Step 4 of "Create Dynamic Data Validation And Auto Extend The Drop Down By Defining Range Name"
  • To post as a guest, your comment is unpublished.
    Christopher · 3 years ago
    I LOVE YOU!
  • To post as a guest, your comment is unpublished.
    geoff · 3 years ago
    I find it is more simple to use a Table than write this whole Offset function. However since a Table cannot be used directly as a data validation source, I have to create a Name that refers to the Table[Column], and point the data validation list to the Name.