Инструмент проверки данных Microsoft Excel позволяет добавлять раскрывающийся список в ячейку на основе существующих данных в столбце. Однако то, как это работает, зависит от того, являются ли исходные данные частью отформатированной таблицы Excel. Давайте рассмотрим несколько примеров.
Использование столбца в форматированной таблице Excel
Представьте, что у вас есть эта отформатированная таблица Excel под названием «Scores», содержащая имена игроков, нации и очки, и вам нужно извлечь некоторые сводные данные. В частности, в ячейке H2 вы хотите создать раскрывающийся список всех наций, перечисленных в столбце B, а в ячейках I2 и J2 отобразить имена игроков и средний счет, соответственно, в соответствии с выбранной нацией.
Чтобы создать раскрывающийся список, выберите ячейку, в которой вы хотите его разместить (в данном случае ячейку I2), и на вкладке «Данные» на ленте нажмите «Проверка данных» в раскрывающемся списке с таким же названием.
Затем в поле «Разрешить» вкладки «Настройки» выберите «Список».
Следующий шаг включает ввод опций, которые вы хотите видеть в раскрывающемся списке, в поле Источник. Для этого вы можете ввести опции вручную, выбрать соответствующие ячейки в вашей электронной таблице или использовать формулу.
В идеале вы должны уметь набирать:
=Очки[Нация]
в поле Источник, точно так же, как при ссылке на этот столбец в формуле, где «Scores» представляет имя таблицы, а «Nation» представляет заголовок столбца. Однако, к сожалению, это возвращает сообщение об ошибке, так как инструмент проверки данных распознает только ссылки на ячейки, формулы и именованные диапазоны как источники данных, а не заголовки столбцов таблицы.
К счастью, есть два способа обойти это.
Метод 1: Ссылка на ячейки в таблице
Сначала активируйте поле «Источник» в диалоговом окне «Проверка данных», чтобы курсор мигал, и наведите курсор на соответствующий заголовок столбца в строке 1, пока не увидите маленькую черную стрелку вниз. Когда вы это сделаете, щелкните один раз, чтобы выбрать все ячейки данных в этом столбце.
Будьте осторожны, чтобы не выбрать весь столбец, нажав на стрелку, которая появляется при наведении курсора на букву ссылки на столбец (в данном случае «B»), так как это выделит весь столбец, включая заголовок в строке 1 и ячейки под вашей таблицей. Вы успешно выбрали правильный диапазон, если только ячейки в столбце внутри таблицы окружены пунктирной линией.
Теперь, после нажатия «ОК», при выборе ячейки H2 появляется кнопка раскрывающегося списка, которую можно нажать, чтобы выбрать вариант из источника. Обратите внимание также на то, как в списке отображаются только уникальные значения — другими словами, Excel распознает повторяющиеся значения и отображает их только один раз.
Раскрывающиеся списки проверки данных в Excel принимают тот же порядок, что и источник. В этом примере, чтобы упорядочить страны в алфавитном порядке, вам нужно будет отсортировать исходные данные по столбцу B.
Преимущество использования этого метода в том, что если вы добавляете больше строк в отформатированную таблицу Excel, источник проверки данных автоматически настраивается для включения дополнительной ячейки или ячеек.
Теперь вы можете использовать функцию ФИЛЬТР в ячейке I2, чтобы вывести список игроков выбранной страны:
=ФИЛЬТР(Результаты[Игрок],Результаты[Страна]=H2)
Наконец, используйте функцию СРЗНАЧЕСЛИ в ячейке J2, чтобы отобразить средний балл игроков из этой страны:
=СРЗНАЧЕСЛИ(Очки[Страна],H2,Очки[Всего])
Метод 2: Назовите диапазон
Второй способ создания раскрывающегося списка из столбца в отформатированной таблице заключается в присвоении имени исходному диапазону.
Перед запуском диалогового окна «Проверка данных» выберите все ячейки в таблице, содержащие значения, которые вы хотите включить в раскрывающийся список, введите имя диапазона в поле имени в верхнем левом углу окна Excel и нажмите клавишу Enter.
Для простоты (и запоминаемости!) имя, которое вы вводите для выбранного диапазона, должно совпадать с заголовком столбца.
Затем в поле Источник диалогового окна Проверка данных введите знак равенства (=), а затем имя, которое вы только что назначили диапазону, и нажмите Enter. В этом примере вам нужно ввести:
=Нация
Как и в предыдущем методе, Excel распознает, что данные находятся в отформатированной таблице, поэтому автоматически расширяет диапазон, если данные добавляются в следующую строку.
Использование именованных диапазонов в Excel имеет много преимуществ. Например, именование диапазонов делает вашу книгу более доступной для людей, использующих программы чтения с экрана. Кроме того, вы можете быстро переходить к именованным диапазонам, вводя их в поле имени или выбирая их из вариантов при нажатии стрелки вниз, а именованные диапазоны также можно использовать в формулах.
Теперь, как и в методе 1, вы можете использовать функции ФИЛЬТР и СРЗНАЧЕСЛИ для завершения извлечения данных.
Однако на этот раз формулы могут быть более простыми, поскольку вы можете ссылаться на диапазон, который вы назвали «Нация», не ссылаясь на таблицу, в которой находится этот диапазон.
Итак, для ФИЛЬТРА в ячейке I2 это:
=ФИЛЬТР(Очки[Игрок],Нация=H2)
а для СРЗНАЧЕСЛИ в ячейке J2 это:
=СРЗНАЧЕСЛИ(Нация,H2,Очки[Всего])
Использование столбца в неформатированном наборе данных
Некоторые типы данных, например, разрозненные массивы, невозможно отформатировать в виде таблицы Excel, поэтому могут возникнуть ситуации, когда вам понадобится найти способ создания раскрывающегося списка из столбца в неформатированном наборе данных.
Выбрав ячейку, которая будет содержать раскрывающийся список, и нажав кнопку «Проверка данных» на вкладке «Данные» на ленте, выберите «Список» в поле «Разрешить».
Затем в поле «Источник» используйте функции ДВССЫЛ и СЧЁТЗ вместе, чтобы указать Excel, где найти параметры для раскрывающегося списка.
В этом случае введите:
=ДВССЫЛ(«B2:B»&СЧЁТA(B:B))
включает в себя все значения в столбце B от ячейки B2 до ячейки B22 в качестве источника.
Давайте разберем эту исходную формулу, чтобы увидеть, как она работает.
- =КОСВЕННЫЙ(: Это сообщает Excel, что вы хотите, чтобы источник определялся с помощью динамической ссылки.
- «Б2:Б»: В частности, динамическая ссылка начинается с ячейки B2 и заканчивается в другой ячейке в столбце B.
- &СЧЁТA(B:B)): Это подсчитывает все ячейки в столбце B, которые не являются пустыми, и добавляет итог к ссылке. В этом случае 22 ячейки в столбце B содержат значения, поэтому это превращает B2:B в B2:B22.
Таким образом, если вы добавите еще одну строку данных внизу, функция COUNTA подхватит это и сообщит ссылке INDIRECT в правиле проверки данных, что источник расширился вниз на дополнительную строку.
Использование формулы для определения того, какие значения следует включить в источник проверки данных, а не просто выбирать весь столбец, считается наилучшей практикой, поскольку это предотвращает включение строки заголовка и пустых строк в раскрывающийся список.
Вы можете перепроверить это, выбрав ячейку, в которой находится раскрывающийся список, и снова открыв диалоговое окно «Проверка данных». Когда вы выбираете поле «Источник», даже если формула не изменилась, пунктирная линия на электронной таблице подтверждает, что она подбирает новые значения в добавленной строке.
Теперь, когда ваш раскрывающийся список готов к использованию, используйте функции ДВССЫЛ и СЧЁТЗ вместе с функцией ФИЛЬТР в ячейке I2 и функцией СРЗНАЧЕСЛИ в ячейке J2, чтобы заполнить электронную таблицу.
Раскрывающиеся списки, добавленные через инструмент проверки данных, невероятно мощные и универсальные, и могут использоваться во многих сценариях в Excel. Например, вы можете использовать раскрывающиеся списки, чтобы сделать обычные диаграммы динамичными — верный способ произвести впечатление на друзей и коллег.
