Разное

Выпадающий список: Создание раскрывающегося списка в Excel

05.04.2019

Содержание

Создание раскрывающегося списка в Excel

Excel 2007-2013

Можно повысить эффективность использования листа, вставляя в него раскрывающиеся списки. Пользователю, использующему ваш лист, достаточно щелкнуть стрелку, а затем запись в списке. Для создания списка следует использовать функцию проверки данных.

  1. Выберите ячейки, в которой должен отображаться список.

  2. На ленте на вкладке «Данные» щелкните «Проверка данных».

  3. На вкладке «Параметры» в поле «Тип данных» выберите пункт «Список».

  4. Щелкните в поле «Источник» и введите текст или числа (разделенные запятыми), которые должны появиться в списке.

  5. Чтобы закрыть диалоговое окно, в щелкните «ОК».

Excel Online

Раскрывающиеся списки пока что невозможно создавать в Excel Online, бесплатной сетевой версии Excel. Однако вы можете просматривать и работать с раскрывающимся списком в Excel Online, если добавите его на свой лист в классическом приложении Excel. Вот как это можно сделать, если у вас имеется классическое приложение Excel:

  1. В Excel Online щелкните «Открыть в Excel» для открытия файла в классическом приложении Excel.

  2. В классическом приложении создайте раскрывающийся список.

  3. Теперь сохраните вашу книгу.

  4. В Excel Online откройте книгу для просмотра и использования раскрывающегося списка.

Узнайте больше о работе с раскрывающимися списками в Excel Online

Excel для Mac 2011

Можно повысить эффективность использования листа, вставляя в него раскрывающиеся списки. Пользователю, использующему ваш лист, достаточно щелкнуть стрелку, а затем запись в списке. Для создания списка следует использовать функцию проверки данных.

  1. Выберите ячейки, в которой должен отображаться список.

  2. На вкладке «Данные» в разделе «Инструменты» щелкните «Проверить».

  3. Щелкните вкладку «Параметры», а затем во всплывающем меню «Разрешить» выберите пункт «Список».

  4. Щелкните в поле «Источник» и введите текст или числа (разделенные запятыми), которые должны появиться в списке.

  5. Чтобы закрыть диалоговое окно, в щелкните «ОК».

Узнайте больше о работе с раскрывающимися списками в Excel Online Узнайте больше о создании раскрывающихся списков в Excel для Mac 2011

Выпадающий список в ячейке листа

Видео

 У кого мало времени и нужно быстро ухватить суть — смотрим обучающее видео:

В этом руководстве вы узнаете, как создать раскрывающийся список в Excel (это займет всего несколько секунд) вместе с все классные вещи, которые вы можете с ним делать.

Как создать раскрывающийся список в Excel

В этом разделе вы узнаете, как именно создать раскрывающийся список Excel:

  1. Использование данных из ячеек.
  2. Ввод данных вручную.
  3. Используя формулу СМЕЩЕНИЕ.

# 1 Использование данных из ячеек

Допустим, у вас есть список элементов, как показано ниже:

Вот шаги для создания раскрывающегося списка Excel:

  1. Выберите ячейку, в которой вы хотите создать выпадающий список.
  2. Перейдите в Data -> Data Tools -> Data Validation.
  3. В диалоговом окне «Проверка данных» на вкладке «Параметры» выберите «Список» в качестве критерия проверки.
    • Как только вы выберете Список, появится поле источника.
  4. В поле «Источник» введите = $ A $ 2: $ A $ 6 или просто щелкните поле «Источник», выберите ячейки с помощью мыши и нажмите «ОК». Это вставит раскрывающийся список в ячейку C2.
    • Убедитесь, что в раскрывающемся списке «В ячейке» установлен флажок (по умолчанию). Если этот параметр не установлен, в ячейке не отображается раскрывающийся список, однако вы можете вручную ввести значения в список.

Примечание: Если вы хотите создать раскрывающиеся списки в нескольких ячейках за один раз, выберите все ячейки, в которых вы хотите создать его, и затем выполните указанные выше действия.Убедитесь, что ссылки на ячейки являются абсолютными (например, $ A $ 2), а не относительными (например, A2, или A $ 2, или $ A2).

# 2 Ввод данных вручную

В приведенном выше примере ссылки на ячейки используются в поле «Источник». Вы также можете добавлять элементы напрямую, вводя их вручную в поле источника.

Например, предположим, что вы хотите отобразить две опции, Да и Нет, в раскрывающемся списке в ячейке. Вот как вы можете напрямую ввести его в поле источника проверки данных:

  • Выберите ячейку, в которой вы хотите создать раскрывающийся список (ячейка C2 в этом примере).
  • Перейдите в Data -> Data Tools -> Data Validation.
  • В диалоговом окне «Проверка данных» на вкладке «Параметры» выберите «Список» в качестве критерия проверки.
    • Как только вы выберете Список, появится поле источника.
  • В поле «Источник» введите «Да», «Нет».
    • Убедитесь, что в раскрывающемся списке установлен флажок «В ячейке».
  • Нажмите ОК.

Это создаст раскрывающийся список в выбранной ячейке. Все элементы, перечисленные в поле источника, разделенные запятой, перечислены в разных строках раскрывающегося меню.

Все элементы, введенные в поле источника, разделенные запятой, отображаются в разных строках в раскрывающемся списке.

Примечание: Если вы хотите создать раскрывающиеся списки в нескольких ячейках за один раз, выберите все ячейки, в которых вы хотите создать его, и затем выполните указанные выше действия.

# 3 Использование формул Excel

Помимо выбора из ячеек и ввода данных вручную, вы также можете использовать формулу в поле источника для создания раскрывающегося списка Excel.

Любую формулу, которая возвращает список значений, можно использовать для создания раскрывающегося списка в Excel.

Например, предположим, что у вас есть набор данных, как показано ниже:

Вот шаги для создания раскрывающегося списка Excel с помощью функции СМЕЩЕНИЕ:

  • Выберите ячейку, в которой вы хотите создать раскрывающийся список (ячейка C2 в этом примере).
  • Перейдите в Data -> Data Tools -> Data Validation.
  • В диалоговом окне «Проверка данных» на вкладке «Параметры» выберите «Список» в качестве критерия проверки.
    • Как только вы выберете Список, появится поле источника.
  • В поле «Источник» введите следующую формулу: = СМЕЩЕНИЕ ($ A $ 2,0,0,5)
    • Убедитесь, что в раскрывающемся списке «В ячейке» установлен флажок.
  • Нажмите ОК.

Будет создан раскрывающийся список, в котором перечислены все названия фруктов (как показано ниже).

Примечание: Если вы хотите создать раскрывающийся список из нескольких ячеек за один раз, выберите все ячейки, в которых вы хотите его создать, и затем выполните указанные выше действия. Убедитесь, что ссылки на ячейки являются абсолютными (например, $ A $ 2), а не относительными (например, A2, или A $ 2, или $ A2).

Как работает эта формула ??

В приведенном выше случае мы использовали функцию OFFSET для создания раскрывающегося списка. Он возвращает список элементов из диапазона

Он возвращает список элементов из диапазона A2: A6.

Вот синтаксис функции СМЕЩ: = СМЕЩ (ссылка, строки, столбцы, [высота], [ширина])

Требуется пять аргументов, где мы указали ссылку как A2 (начальная точка списка) .Строки / столбцы указаны как 0, поскольку мы не хотим смещать ссылочную ячейку. Высота указана как 5, поскольку в списке пять элементов.

Теперь, когда вы используете эту формулу, она возвращает массив со списком пяти фруктов в A2: A6. Обратите внимание: если вы введете формулу в ячейку, выберите ее и нажмите F9, вы увидите, что она возвращает массив названий фруктов.

Создание динамического раскрывающегося списка в Excel (с помощью OFFSET)

Вышеупомянутый метод использования формулы для создания раскрывающегося списка может быть расширен для создания динамического раскрывающегося списка. Если вы используете функцию СМЕЩЕНИЕ, как показано выше, даже если вы добавляете дополнительные элементы в список, раскрывающийся список не будет обновляться автоматически. Вам придется вручную обновлять его каждый раз, когда вы меняете список.

Вот способ сделать его динамическим (и это всего лишь небольшая поправка в формуле):

  • Выберите ячейку, в которой вы хотите создать раскрывающийся список (ячейка C2 в этом примере).
  • Перейдите в Data -> Data Tools -> Data Validation.
  • В диалоговом окне «Проверка данных» на вкладке «Параметры» выберите «Список» в качестве критерия проверки.Как только вы выберете Список, появится поле источника.
  • В поле источника введите следующую формулу: = СМЕЩЕНИЕ ($ 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 не будет показывать никаких предупреждений или подсказок, чтобы дать пользователю знать, что раскрывающийся список будет перезаписан.

Как выбрать все ячейки, в которых есть раскрывающийся список

Иногда трудно определить, какие ячейки содержат раскрывающийся список.

Следовательно, имеет смысл пометить эти ячейки, придав им четкую границу или цвет фона.

Вместо того, чтобы вручную проверять все ячейки, есть быстрый способ выбрать все ячейки, в которых есть раскрывающиеся списки (или любое правило проверки данных).

  • Перейти на главную -> Найти и выбрать -> Перейти к специальному.
  • В диалоговом окне «Перейти к специальному» выберите «Проверка данных».
    • Проверка данных имеет два варианта: Все и То же. All выберет все ячейки, к которым применено правило проверки данных. То же самое будет выбирать только те ячейки, которые имеют то же правило проверки данных, что и для активной ячейки.
  • Нажмите ОК.

Это позволит мгновенно выбрать все ячейки, к которым применено правило проверки данных (включая раскрывающиеся списки).

Теперь вы можете просто отформатировать ячейки (задать цвет границы или фона) так, чтобы они были визуально видимыми, и вы случайно не скопировали в них другую ячейку.

Вот еще одна техника Джона Акампоры, которую вы можете использовать, чтобы всегда отображать значок со стрелкой вниз. Вы также можете увидеть некоторые способы сделать это в этом видео от Mr. Excel.

Создание зависимого / условного раскрывающегося списка Excel

Вот видео о том, как создать зависимый раскрывающийся список в Excel.

Если вы предпочитаете читать видео, продолжайте читать.

Иногда у вас может быть более одного раскрывающегося списка, и вы хотите, чтобы элементы, отображаемые во втором раскрывающемся списке, зависели от того, что пользователь выбрал в первом раскрывающемся списке.

Они называются зависимыми или условными раскрывающимися списками.

Ниже приведен пример условного / зависимого раскрывающегося списка:

В приведенном выше примере, когда элементы, перечисленные в «Выпадающем списке 2», зависят от выбора, сделанного в «Выпадающем списке 1».

Теперь давайте посмотрим, как это создать.

Вот шаги для создания зависимого / условного раскрывающегося списка в Excel:

Теперь, когда вы делаете выбор в раскрывающемся списке 1, параметры, перечисленные в раскрывающемся списке 2, будут автоматически обновляться.

Скачать файл примера

Как это работает? — Условный раскрывающийся список (в ячейке E3) относится к = КОСВЕННО (D3). Это означает, что когда вы выбираете «Фрукты» в ячейке D3, раскрывающийся список в E3 ссылается на именованный диапазон «Фрукты» (с помощью функции КОСВЕННО) и, следовательно, перечисляет все элементы в этой категории.

Важное примечание при работе с условными раскрывающимися списками в Excel:

  • Когда вы сделали выбор, а затем изменили родительский раскрывающийся список, зависимый раскрывающийся список не изменится и, следовательно, будет быть неправильной записью. Например, если вы выберете США в качестве страны, а затем выберите Флориду в качестве штата, а затем вернетесь и измените страну на Индию, штат останется как Флорида. Вот отличный учебник от Дебры по очистке зависимых (условных) раскрывающихся списков в Excel при изменении выбора.
  • Если основная категория состоит из более чем одного слова (например, «Сезонные фрукты» вместо «Фрукты»), тогда вам необходимо использовать формулу = КОСВЕННО (ПОДСТАВИТЬ (D3, ”“, ”_”)) вместо простой функции КОСВЕННО, показанной выше. Причина этого в том, что Excel не допускает пробелов в именованных диапазонах. Поэтому, когда вы создаете именованный диапазон, используя более одного слова, Excel автоматически вставляет подчеркивание между словами.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *