Создание автоматически заполняемых списков в Excel
В данной задачи создадим автоматически заполняемые списки с помощью табличного процессора Excel. Все данные вводятся в один большой список и которого записи автоматически группируются по новым спискам.
Результат автоматического распределения записей по спискам
Реализуем такие, автоматически заполняемые списки, подробно объясняя этапы решения задачи.
Суть решения
Принцип действия
Пользователи, которые уже поработали в Excel, очевидно заметят, что принцип действия таких динамических списков очень схож с таковым у функции ВПР, либо более продвинутого аналога данной функции – связки ИНДЕКС и ПОИСКПОЗ.
Действительно, нам нужно найти в левом столбце «маркер» списка, т.е. название и вернуть элемент такого списка уже из правого столбца. Однако, здесь есть один нюанс, который заключается в том, что функция ВПР, равно как и вышеупомянутая сборка-аналог вернут только первую запись, т.е. максимум мы сможем выбрать таким способом по одному элементу для каждого списка.
Соответственно, нам нужно сделать реализации чего-то на подобие множественного ВПР, когда значения будут выбираться все значения, а не только первое.
Основная идея реализации
Основу в реализации такого множественного выбора будет составлять чрезвычайно полезная функция ИНДЕКС, которая возвращает значение ячейки, которая находится на пересечении указанных строки и столбца. Столбце у нас известен из условия – это столбец со всеми значениями (правый в «сырой» таблице), а вот номер строки мы будем подставлять динамически.
Данную функции еще часто используют в связке с другой полезной функцией ПОИСКПОЗ, однако, сейчас нам нужна не связка, поскольку номер строки (а именно за поиск номера строки в функции ИНДЕКС отвечает функция ПОИСКПОЗ) мы найдем отдельно.
Решение:
Теория:
Принцип работы данной формулы становится понятен, если взглянуть на следующий рисунок:
Мы получили номера строк, которые разбросаны по всему диапазону вычислений, нам нужно их собрать и для этого, во-первых, следует построить простой номерной ряд, банально пронумеровав один столбец, а, во-вторых, с помощью функции НАИМЕНЬШИЙ, собрать все значения по каждому столбцу, отсортировав по возрастанию.
Функция НАИМЕНЬШИЙ похожа на вычисление минимального, т.е. функцию МИН, за тем исключением, что позволяет найти не только минимальное, но и 2-е, 3-е и т.д. наименьше значение после минимального.
Как можно догадаться, вспомогательный числовой ряд будет использоваться для указания того, какое наименышее значение по порядку следует вернуть. Таким образом, конечная формула, сортирующая найденные номера строк будет выглядеть вот так:
Первым аргументом функции указана ссылка полностью на строку, если будете указывать на диапазон, не забудьте позаботиться о том, чтобы его зафиксировать (сделав абсолютную или смешанную ссылку). Второй аргумент – это смешанная ссылка на вспомогательный ряд, т.е., в первом случае, когда ссылка идет на цифру «1», формула вернет минимальное значение, потом — 2-е после минимального и т.д.
Как и предполагалось, мы просто записали ранее разбросанные значения с вычислением номера строки.
Если вы раньше работали с функцией ИНДЕКС, то построение финальной формулы не вызовет абсолютно никаких трудностей, фактически нам нужно вернуть значение для ячейки, когда у нас известен и номер строки, и номер столбца (он изначально был известен, т.к. всего один столбце со значениями).
Здесь мы, как и с упорядочиванием, используем ссылку на столбец, однако, делаем его абсолютной ссылкой, поскольку формулу будем растягивать вправо и нужно чтобы столбец не менялся (в случае поиска наименьшего нам нужно было, чтобы столбец менялся).
Все хорошо, за исключением ошибки, которая находится в не заполненных ячейках. Ошибку легко скрыть, если «обернуть» конечную формулу в функцию ЕСЛИОШИБКА, указав, в качестве второго аргумента, пустую ячейку (просто двойные кавычки).
Результат использования функции ЕСЛИОШИБКА для перехвата ошибок
Когда мы заполняем неупорядоченный диапазон, левый столбец, по сути, может принимать фиксированные значения (список1, 2, 3 и т.д.), с целью исключения ошибки и ускорения ввода первоначальных данных, для левого столбца можно создать выпадающий список из допустимых значений.
Вообще будет хорошей идеей в одном месте записать допустимые значения (например, пускай это будут заголовки упорядоченных списков), а в остальных местах, где они должны использоваться просто ссылаться на них.
Процесс создания выпадающего списка в Excel детально рассмотрен в этой статье, здесь мы заострять внимание на этом не будем.
Файл с примером
Уважаемые пользователи, файл с примером вы можете также скачать в ветке нашего форума, где задать вопрос, или просто обсудить материал.
[expert_bq id=»1570″]Обе вкладки этого окна содержат одни и те же настройки, рассортированные в первом случае по алфавиту, во втором по категориям. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Если исходная таблица содержит пропуски, то нужно использовать другую формулу массива (см. лист с пропусками файла примера ):=ЕСЛИОШИБКА(ИНДЕКС($A$5:$A$19;
ПОИСКПОЗ( 0;ЕСЛИ(ЕПУСТО($A$5:A19);»»;СЧЁТЕСЛИ($B$4:B4;$A$5:$A$19));0)
);»»)
Выпадающий список в excel 2010
- Создайте именованный диапазон: Этот шаг необходим для создания списка, который мы будем использовать в раскрывающемся списке.
- Добавить проверку данных: Это не столь очевидный шаг, когда вы заполняете ячейку для отображения выпадающего списка.
- Добавить именованный диапазон к проверке данных: Наконец, вы соберете все вместе и определите источник раскрывающегося списка (т. Е. Ранее названный вами диапазон).
- Установить входное сообщение для проверки данных: Этот шаг не является обязательным. Это позволяет вам добавить всплывающее сообщение для руководства ваших пользователей электронных таблиц.
Введите следующий текст в Источник коробка. Заменить «$ B $ 2»Со ссылкой на ячейку, содержащую ваш основной выпадающий список. Держите знаки доллара в ячейке ссылки. Это указывает на абсолютную ссылку на ячейку, которая не изменится, даже если вы скопируете или переместите формулу, которая ссылается на эту ячейку.
Создание автоматически заполняемых списков в Excel — Центр обучения
- Выделяем ячейки, где хотим создать выпадающие списки.
- На вкладке Данные жмем на кнопку Проверка данных (Data – Validation).
- Выбираем в раскрывающемся списке разрешенных типов данных вариант Список (List) и вводим в поле Источник (Source) следующую формулу: =ДВССЫЛ(«Таблица_Справочник») . В англоязычной версии Excel это будет =INDIRECT(«Таблица_Справочник») .
с раскрывающимся списком справа от ячейки отображается стрелка вниз для выбора параметра. Кнопка со стрелкой вниз отображается только при выборе ячейки. Мы покажем вам способ отображать стрелку вниз рядом с раскрывающимся списком все время позже в этой статье.
