В этом руководстве вы узнаете, как создать раскрывающийся список в Excel (это займет всего несколько секунд), а также обо всех удивительных возможностях, которые вы можете с ним делать.
- Выберите ячейку, в которой вы хотите создать раскрывающийся список.
- Перейдите в раздел «Данные» –> «Инструменты данных» –> «Проверка данных».
- В диалоговом окне "Проверка данных" на вкладке "Настройки" выберите "Список" в качестве критериев проверки.
- Как только вы выберете список, появится исходное поле.
- В поле "Источник" введите =$A$2:$A$6 или просто щелкните поле "Источник", выберите ячейки с помощью мыши и нажмите "ОК". Это добавит раскрывающийся список в ячейку C2.
- Убедитесь, что установлен флажок в раскрывающемся списке "В ячейке" (он установлен по умолчанию). Если этот флажок не установлен, в ячейке не отображается раскрывающийся список, однако вы можете вручную ввести значения в список.
ол>р>
Примечание. Если вы хотите создать раскрывающиеся списки в нескольких ячейках за один раз, выберите все ячейки, в которых вы хотите его создать, а затем выполните указанные выше действия. Убедитесь, что ссылки на ячейки являются абсолютными (например, $A$2), а не относительными (например, A2, или A$2, или $A2).
В приведенном выше примере ссылки на ячейки используются в поле "Источник". Вы также можете добавить элементы напрямую, введя их вручную в исходное поле.
Например, предположим, что вы хотите отобразить два варианта, Да и Нет, в раскрывающемся списке в ячейке. Вот как вы можете ввести его непосредственно в поле источника проверки данных:
Это создаст раскрывающийся список в выбранной ячейке. Все элементы, перечисленные в исходном поле, разделенные запятой, перечислены в разных строках раскрывающегося меню.
Все элементы, введенные в исходное поле, разделенные запятой, отображаются в разных строках раскрывающегося списка.
Примечание. Если вы хотите создать раскрывающиеся списки сразу в нескольких ячейках, выберите все ячейки, в которых вы хотите его создать, и выполните описанные выше действия.
Помимо выбора из ячеек и ввода данных вручную, вы также можете использовать формулу в исходном поле для создания раскрывающегося списка Excel.
Любую формулу, возвращающую список значений, можно использовать для создания раскрывающегося списка в Excel.
Например, предположим, что у вас есть набор данных, как показано ниже:
Вот шаги для создания раскрывающегося списка Excel с использованием функции OFFSET:
При этом будет создан раскрывающийся список со всеми названиями фруктов (как показано ниже).
Примечание. Если вы хотите создать раскрывающийся список в нескольких ячейках за один раз, выберите все ячейки, в которых вы хотите его создать, а затем выполните описанные выше шаги. Убедитесь, что ссылки на ячейки являются абсолютными (например, $A$2), а не относительными (например, A2, или A$2, или $A2).
Как работает эта формула??
В приведенном выше случае мы использовали функцию OFFSET для создания раскрывающегося списка. Возвращает список элементов из ra
Он возвращает список элементов из диапазона A2:A6.
Вот синтаксис функции СМЕЩ: =СМЕЩ(ссылка, строки, столбцы, [высота], [ширина])
Он принимает пять аргументов, где мы указали ссылку как A2 (начальная точка списка). Строки/столбцы указаны как 0, так как мы не хотим смещать ссылочную ячейку. Высота указана как 5, так как в списке пять элементов.
Теперь, когда вы используете эту формулу, она возвращает массив со списком пяти фруктов в A2:A6. Обратите внимание: если вы введете формулу в ячейку, выделите ее и нажмите F9, вы увидите, что она возвращает массив названий фруктов.
Создание динамического раскрывающегося списка в Excel (с использованием смещения)
Приведенный выше метод использования формулы для создания раскрывающегося списка можно расширить и для создания динамического раскрывающегося списка. Если вы используете функцию OFFSET, как показано выше, даже если вы добавите больше элементов в список, раскрывающийся список не будет обновляться автоматически. Вам придется вручную обновлять его каждый раз, когда вы меняете список.
Вот способ сделать его динамичным (и это не что иное, как небольшая поправка в формуле):
- Выберите ячейку, в которой вы хотите создать раскрывающийся список (ячейка C2 в этом примере).
- Перейдите в раздел «Данные» –> «Инструменты данных» –> «Проверка данных».
- В диалоговом окне "Проверка данных" на вкладке "Настройки" выберите "Список" в качестве критериев проверки. Как только вы выберете список, появится исходное поле.
- В исходном поле введите следующую формулу: =СМЕЩ($A$2,0,0,СЧЁТЕСЛИ($A$2:$A$100,"<>"))
- Убедитесь, что флажок раскрывающегося списка "В ячейке" установлен.
- Нажмите "ОК".
В этой формуле я заменил аргумент 5 на СЧЁТЕСЛИ($A$2:$A$100,"<>").
Функция СЧЁТЕСЛИ подсчитывает непустые ячейки в диапазоне A2:A100. Следовательно, функция СМЕЩ настраивается таким образом, чтобы включать все непустые ячейки.
Примечание.
- Чтобы это работало, между заполненными ячейками НЕ должно быть пустых ячеек.
- Если вы хотите создать раскрывающийся список сразу в нескольких ячейках, выберите все ячейки, в которых вы хотите его создать, а затем выполните описанные выше действия. Убедитесь, что ссылки на ячейки являются абсолютными (например, $A$2), а не относительными (например, A2, или A$2, или $A2).
Копирование и вставка раскрывающихся списков в Excel
Вы можете скопировать и вставить ячейки с проверкой данных в другие ячейки, и проверка данных также будет скопирована.
Например, если у вас есть раскрывающийся список в ячейке C2, и вы хотите применить его также к ячейке C3:C6, просто скопируйте ячейку C2 и вставьте ее в ячейку C3:C6. Это скопирует раскрывающийся список и сделает его доступным в C3:C6 (вместе с раскрывающимся списком будет также скопировано форматирование).
Если вы хотите скопировать только раскрывающийся список, а не форматирование, выполните следующие действия:
При этом будет скопирован только раскрывающийся список, а не форматирование скопированной ячейки.
Предостережение при работе с раскрывающимся списком Excel
Вы должны быть осторожны при работе с раскрывающимися списками в Excel.
При копировании ячейки (не содержащей раскрывающегося списка) поверх ячейки, содержащей раскрывающийся список, раскрывающийся список теряется.
Хуже всего то, что Excel не будет отображать никаких предупреждений или подсказок, чтобы сообщить пользователю, что раскрывающийся список будет перезаписан.
Как выбрать все ячейки, в которых есть раскрывающийся список
Иногда трудно понять, какие ячейки содержат раскрывающийся список.
Следовательно, имеет смысл пометить эти ячейки, придав им четкую границу или цвет фона.
Вместо того, чтобы вручную проверять все ячейки, есть быстрый способ выбрать все ячейки, в которых есть раскрывающиеся списки (или любые правила проверки данных).
Это позволит мгновенно выбрать все ячейки, к которым применено правило проверки данных (включая раскрывающиеся списки).
Теперь вы можете просто отформатировать ячейки (задать рамку или цвет фона) так, чтобы они были визуально видны, и вы случайно не скопировали на них другую ячейку.
Вот еще одна техника Джона Акампоры, которую вы можете использовать, чтобы всегда держать значок раскрывающейся стрелки видимым. Вы также можете увидеть некоторые способы сделать это в этом видео от Mr. Excel.
Создание зависимого/условного раскрывающегося списка Excel
Вот видео о том, как создать зависимый раскрывающийся список в Excel.
Примечание. Если вы не хотите, чтобы пользователи имели доступ к элементам на Листе2, вы можете скрыть Лист2. Для этого щелкните правой кнопкой мыши вкладку листа Sheet2 и выберите "Скрыть".
<р>2. На первом листе выберите ячейку B1.
<р>3. На вкладке "Данные" в группе "Инструменты данных" нажмите "Проверка данных".
Откроется диалоговое окно "Проверка данных".
<р>4. В поле Разрешить нажмите Список.
<р>5. Нажмите в поле Источник и выберите диапазон A1:A3 на Листе2.
Примечание: чтобы скопировать/вставить раскрывающийся список, выберите ячейку с раскрывающимся списком и нажмите CTRL + c, выберите другую ячейку и нажмите CTRL + v.
<р>7. Вы также можете вводить элементы непосредственно в поле Источник вместо использования ссылки на диапазон.
Примечание: это делает ваш раскрывающийся список чувствительным к регистру. Например, если пользователь введет "да", появится предупреждение об ошибке.
Разрешить другие записи
Вы также можете создать раскрывающийся список в Excel, который позволяет вводить другие данные.
<р>1.Во-первых, если вы введете значение, которого нет в списке, Excel покажет предупреждение об ошибке.
Ширина
Чтобы разрешить другие записи, выполните следующие действия.
<р>2. На вкладке "Данные" в группе "Инструменты данных" нажмите "Проверка данных".
Откроется диалоговое окно "Проверка данных".
<р>3. На вкладке Оповещение об ошибке снимите флажок "Показывать оповещение об ошибке после ввода неверных данных".
<р>5. Теперь вы можете ввести значение, которого нет в списке.
Добавить/удалить элементы
Вы можете добавлять или удалять элементы из раскрывающегося списка в Excel, не открывая диалоговое окно "Проверка данных" и не изменяя ссылку на диапазон. Это экономит время.
<р>1. Чтобы добавить элемент в раскрывающийся список, перейдите к элементам и выберите элемент.
<р>2. Щелкните правой кнопкой мыши и выберите "Вставить".
<р>3. Выберите «Сдвинуть ячейки вниз» и нажмите «ОК».
Примечание. Excel автоматически изменил ссылку на диапазон с Sheet2!$A$1:$A$3 на Sheet2!$A$1:$A$4. Вы можете проверить это, открыв диалоговое окно "Проверка данных".
<р>4. Введите новый элемент.
<р>5. Чтобы удалить элемент из раскрывающегося списка, на шаге 2 нажмите "Удалить", выберите "Сдвинуть ячейки вверх" и нажмите "ОК".
Динамический раскрывающийся список
Вы также можете использовать формулу, которая автоматически обновляет раскрывающийся список при добавлении элемента в конец списка.
<р>1. На первом листе выберите ячейку B1.
<р>2. На вкладке "Данные" в группе "Инструменты данных" нажмите "Проверка данных".
Откроется диалоговое окно "Проверка данных".
<р>3. В поле Разрешить нажмите Список.
<р>4. Щелкните поле Источник и введите формулу: =СМЕЩ(Лист2!$A$1,0,0,СЧЁТ(Лист2!$A:$A),1)
Объяснение: функция OFFSET принимает 5 аргументов. Ссылка: Sheet2!$A$1, строки для смещения: 0, столбцы для смещения: 0, высота: COUNTA(Sheet2!$A:$A) и ширина: 1. COUNTA(Sheet2!$A:$A) подсчитывает число значений в столбце A на Sheet2, которые не пусты. Когда вы добавляете элемент в список на Листе2, СЧЕТЧИК(Лист2!$A:$A) увеличивается. В результате диапазон, возвращаемый функцией OFFSET, расширяется, а раскрывающийся список обновляется.
<р>6. На втором листе просто добавьте новый элемент в конец списка.
Удалить раскрывающийся список
Чтобы удалить раскрывающийся список в Excel, выполните следующие действия.
<р>1. Выберите ячейку с раскрывающимся списком.
<р>2. На вкладке "Данные" в группе "Инструменты данных" нажмите "Проверка данных".
Откроется диалоговое окно "Проверка данных".
<р>3. Нажмите «Очистить все».
Примечание. Чтобы удалить все другие раскрывающиеся списки с такими же настройками, установите флажок "Применить эти изменения ко всем другим ячейкам с такими же настройками", прежде чем нажимать "Очистить все".
Зависимые раскрывающиеся списки
Хотите узнать больше о раскрывающихся списках в Excel? Узнайте, как создавать зависимые раскрывающиеся списки.
<р>1. Например, если пользователь выбирает Пиццу из первого раскрывающегося списка.
<р>2. Второй раскрывающийся список содержит элементы пиццы.
<р>3. Но если пользователь выбирает китайский язык в первом раскрывающемся списке, второй раскрывающийся список содержит китайские блюда.
Магия стола
Вы также можете хранить элементы в таблице Excel, чтобы создать динамический раскрывающийся список.
<р>1. На втором листе выберите элемент списка.
<р>2. На вкладке "Вставка" в группе "Таблицы" нажмите "Таблица".
<р>3. Excel автоматически выбирает данные за вас. Нажмите "ОК".
<р>4. Если выбрать список, Excel покажет структурированную ссылку.
Ширина
<р>5. Используйте этот структурированный справочник для создания динамического раскрывающегося списка.
Объяснение: функция ДВССЫЛ в Excel преобразует текстовую строку в допустимую ссылку.
<р>6. На втором листе просто добавьте новый элемент в конец списка.
Примечание: попробуйте сами. Загрузите файл Excel и создайте раскрывающийся список.
<р>7. При использовании таблиц используйте функцию UNIQUE в Excel 365/2021 для извлечения уникальных элементов списка.
Примечание: эта функция динамического массива, введенная в ячейку F1, заполняет несколько ячеек. Ух ты! Такое поведение в Excel 365/2021 называется сбросом.
<р>8. Используйте этот диапазон для создания волшебного раскрывающегося списка.
Объяснение: всегда используйте первую ячейку (F1) и символ решетки для обозначения диапазона разлива.
Примечание. Когда вы добавляете новые записи, функция UNIQUE автоматически извлекает новые уникальные элементы списка, а Excel автоматически обновляет раскрывающийся список.
Использование раскрывающегося списка в Excel может значительно сократить время, необходимое для ввода данных в электронную таблицу. К счастью, в Excel очень просто создать раскрывающийся список.
Для этого существует несколько способов, от простых до сложных. В этой статье вы узнаете все способы сделать это.
Создание раскрывающегося списка в Excel: простой метод
Самый простой способ создать раскрывающийся список в Excel — ввести его вручную. Это идеально подходит для ситуаций, когда вам нужен раскрывающийся список только в одной ячейке, и вам не нужно часто его обновлять.
Например, если вы отправляете людям файл, в котором вы хотите, чтобы они заполнили некоторые поля, а в одном поле есть простой список вариантов, этот метод идеален.
Чтобы создать раскрывающийся список простым способом:
<р>1. Выберите ячейку или ячейки, в которых вы хотите создать список.
<р>2. Выберите «Данные» в меню и выберите «Проверка данных» на ленте.
<р>3. Откроется окно проверки данных. Выберите раскрывающийся список в разделе Разрешить и выберите Список.
<р>4. Это позволяет вам вводить элементы, которые вы хотите добавить в список, вручную. Просто введите каждый элемент в поле «Источник», разделив каждый элемент запятыми.
<р>5. Выберите ОК. Теперь вы увидите, что рядом с выбранной ячейкой есть стрелка раскрывающегося списка. Когда вы выберете эту стрелку, вы увидите, что все введенные вами элементы включены в этот список.
Хотя это самый быстрый способ создания раскрывающегося списка в Excel, его также сложнее всего поддерживать. Для изменения списка необходимо вернуться к настройкам проверки и обновить поле Источник.
Если вы создаете много таких раскрывающихся списков в Excel, их изменение может занять много времени. Вот почему важно использовать этот метод только для отдельных ячеек и для списков, которые вы не ожидаете изменить.
Создание раскрывающегося списка в Excel: использование диапазона
Если вам нужно более гибкое решение, вы можете извлечь элементы для своего списка из диапазона других ячеек в Excel.
<р>1. Сначала перечислите все элементы, которые вы хотите добавить в свой список, в любой столбец ячеек. Вы можете ввести их в текущую электронную таблицу или на любой другой лист.
<р>2. Повторите описанный выше процесс, чтобы выбрать ячейку и открыть окно проверки данных. Установите в поле Разрешить значение Список. На этот раз вместо того, чтобы вводить что-либо в поле «Источник», выберите значок со стрелкой вверх справа от этого поля. Это параметр выбора диапазона, который позволяет выбрать поле, из которого вы хотите извлечь элементы списка.
<р>3. Вы увидите, как окно проверки данных свернется, чтобы вы могли видеть весь лист. Перетащите указатель мыши вниз по всему диапазону ячеек, содержащему все элементы списка, которые вы хотите включить. Когда вы закончите это делать, выберите значок со стрелкой вниз справа от поля выбора. Это снова развернет окно проверки данных.
<р>4. Вы увидите, что выбранный вами диапазон теперь отображается в поле «Источник». Просто нажмите OK, чтобы принять эти настройки.
<р>5. Теперь, когда вы выберете стрелку раскрывающегося списка справа от ячейки, которую вы настроили как раскрывающийся список, вы увидите все элементы, включенные в только что выбранный диапазон.
Преимущество этого подхода заключается в том, что вы можете изменить любой элемент в этом списке, просто изменив любую ячейку в диапазоне. Любые внесенные вами изменения будут обновлять каждый созданный вами раскрывающийся список, в котором вы выбрали этот диапазон в качестве источника.
Этот метод лучше всего подходит, если вы хотите преобразовать большое количество ячеек в раскрывающийся список, используя одни и те же элементы списка. Вы можете сделать так, чтобы содержимое одного диапазона управляло элементами всех этих списков, и не имеет значения, сколько их.
Добавление элементов в список
Помимо изменения элементов в вашем ассортименте для обновления списков, вы также можете добавлять новые элементы. Вы не сможете добавить элемент в конец диапазона, поскольку выбор диапазона ограничен первой и последней выбранными вами ячейками.
Вместо этого вам нужно будет вставить новую запись где-то в середине диапазона. Excel динамически обновит выбранный вами диапазон в настройках проверки данных, чтобы включить новый диапазон, который вы увеличили на одну ячейку.
<р>1. Щелкните правой кнопкой мыши любую ячейку в исходном диапазоне и выберите "Вставить" в раскрывающемся меню.
<р>2. В маленьком окне выбора выберите Сдвиг ячеек вниз и выберите ОК. Это сдвинет все ячейки в диапазоне на одну вниз, вставив пустую ячейку в выбранное вами место.
<р>3. Введите новый элемент, который вы хотите добавить, в только что созданную пустую ячейку.
Теперь, когда вы нажмете значок со стрелкой вниз справа от созданной вами ячейки раскрывающегося списка, вы увидите новый элемент, который вы только что добавили в диапазон.
Это простой способ добавить новые элементы в раскрывающийся список, но он требует нескольких дополнительных действий. Конечно, это не так просто, как просто добавить новый элемент в конец диапазона.
Если вы хотите это сделать, вам просто нужно изменить настройки проверки данных в соответствии с вашим диапазоном. Вы можете узнать, как это сделать, в следующем разделе.
Динамическое добавление элементов в список
Для более удобного способа добавления элементов в раскрывающийся список путем простого ввода нового элемента в конце настроенного диапазона вам потребуется использовать функцию СМЕЩЕНИЯ.
Выбрав ячейку раскрывающегося списка, выберите «Данные» в меню и «Проверка данных» на ленте.
В окне "Проверка данных" измените источник на следующее:
=СМЕЩЕНИЕ($E$1,0,0,СЧЕТЧИК($E:$E),1)
Измените $E$1 и $E:$E в формуле, чтобы использовать букву столбца, в который вы ввели свой список элементов. Нажмите OK, чтобы подтвердить эту новую конфигурацию проверки данных.
Вот как работает формула:
- Аргументы 0 в функции OFFSET указывают ей не применять смещение к столбцам или строкам.
- Выходные данные функции СЧЁТЗ сообщают функции СМЕЩЕНИЕ высоту диапазона.
- Функция COUNTA подсчитывает количество непустых ячеек в столбце, содержащем ваш диапазон.
Теперь всякий раз, когда вы добавляете новое значение в этот столбец, параметр высоты функции OFFSET увеличивается на единицу, а функция OFFSET возвращает весь диапазон, обновленный для включения вашей новой записи.
Чтобы увидеть это в действии, просто добавьте новую запись в список товаров. Выберите стрелку раскрывающегося списка справа от раскрывающейся ячейки, и вы увидите, что ваша новая запись появится в раскрывающемся списке.
Имейте в виду, что вам может понадобиться использовать полосу прокрутки справа, если ваш список элементов длинный.
Удаление раскрывающегося списка в Excel
Наконец, если вы когда-нибудь захотите удалить раскрывающийся список из любой из ячеек, где вы это настроили, просто выберите эту ячейку и снова откройте окно проверки данных.
В раскрывающемся списке Разрешить выберите любое значение и нажмите кнопку ОК.
Как только вы измените этот параметр, вы увидите, что ячейка вернется в нормальное состояние.
Создание раскрывающихся списков в Excel
Создать раскрывающийся список в Excel очень просто, и у вас есть множество вариантов на выбор. Выбранный вами метод зависит только от того, сколько ячеек вы планируете включить в один список элементов, и насколько гибким вы хотите, чтобы список элементов был. Если вы планируете часто менять элементы списка, обязательно используйте динамический подход.
Райан пишет инструкции и другие статьи о технологиях в Интернете с 2007 года. Он имеет степень бакалавра наук в области электротехники, 13 лет работал в области автоматизации, 5 лет — в ИТ, а сейчас работает инженером по приложениям. Прочитать полную биографию Райана
Понравился ли вам этот совет? Если это так, загляните на наш собственный канал на YouTube, где мы рассказываем о Windows, Mac, программном обеспечении и приложениях, а также предлагаем множество советов по устранению неполадок и видео с практическими рекомендациями. Нажмите кнопку ниже, чтобы подписаться!
Раскрывающиеся списки — очень полезные инструменты в Microsoft Excel, особенно для создания форм или предоставления рекомендаций другим пользователям.
Существует множество различных способов создания раскрывающегося списка и, соответственно, различные методы, которые вам могут понадобиться для редактирования этих списков, если вы хотите внести изменения.
Если ваш список основан на таблице Excel, то этот процесс невероятно прост — просто добавьте или удалите элементы из таблицы, и Excel автоматически обновит раскрывающийся список.
Однако, если раскрывающийся список основан на диапазоне ячеек, именованном диапазоне или был введен вручную, потребуется несколько дополнительных шагов.
Вот как отредактировать раскрывающийся список в Excel в каждом случае с помощью программного обеспечения на вашем ПК или компьютере Mac.
Ознакомьтесь с продуктами, упомянутыми в этой статье:
MacBook Pro (от 1299,99 долларов США в Best Buy)
Lenovo IdeaPad 130 (от 299,99 долларов США в Best Buy)
Как редактировать раскрывающийся список в Excel на основе диапазона ячеек
<р>1. Перейдите к рабочему листу или области текущего рабочего листа, где находятся элементы списка, а затем добавьте или удалите нужный элемент.
<р>2. Выберите ячейку, содержащую раскрывающийся список, затем в меню "Данные" в верхней части экрана нажмите "Проверка данных" и снова выберите "Проверка данных" в меню.
<р>3. На вкладке «Настройки» всплывающего меню в разделе «Источник» настройте диапазон ячеек, чтобы включить новую ячейку, в которую вы ввели данные, или исключить ту, из которой вы их удалили. Затем нажмите OK, чтобы сохранить изменения.
Как редактировать раскрывающийся список в Excel на основе именованного диапазона
<р>1. Добавьте в список новый элемент или удалите ненужный.
<р>2. Перейдите на вкладку "Формулы" и выберите "Диспетчер имен".
<р>3. Выберите именованный диапазон, на котором основан ваш раскрывающийся список, и отредактируйте формулу «Ссылки», чтобы либо включить ячейку, которую вы только что добавили, либо исключить ту, которую вы удалили. Затем нажмите "Закрыть", чтобы сохранить изменения, и "Да" для подтверждения.
Как отредактировать раскрывающийся список в Excel, введенный вручную
<р>1. На вкладке «Данные» нажмите «Проверка данных».
<р>2. На вкладке настроек всплывающего окна в разделе «Источник» добавьте или удалите элементы списка по мере необходимости, убедившись, что все элементы разделены запятой и ничем другим. Затем нажмите OK, чтобы сохранить изменения.
Читайте также: