Центр обученияExcelПрактические занятия по ExcelСоздание автоматически заполняемых списков в Excel

Создание автоматически заполняемых списков в Excel

Описание занятия

Видеоверсия

Текстовая версия

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

Результат автоматического распределения записей по спискам
Результат автоматического распределения записей по спискам

Записи в перечне слева «сырые», т.е. они не сгруппированы, содержат «маркера (т.е. указатель номера списка)» и саму запись, в списках справа — распределены. Количество в 3 списка взято в качестве примера, это количество может быть произвольным, равно как и название «списки». Особым плюсом можно считать то, что, изменяя маркер в «сыром перечне», можно изменять место расположение записи в сгруппированных списках.

Реализуем такие, автоматически заполняемые списки, подробно объясняя этапы решения задачи.

Суть решения

Видеоверсия

Текстовая версия

Принцип действия

Пользователи, которые уже поработали в Excel, очевидно заметят, что принцип действия таких динамических списков очень схож с таковым у функции ВПР, либо более продвинутого аналога данной функции – связки ИНДЕКС и ПОИСКПОЗ.

Действительно, нам нужно найти в левом столбце «маркер» списка, т.е. название и вернуть элемент такого списка уже из правого столбца. Однако, здесь есть один нюанс, который заключается в том, что функция ВПР, равно как и вышеупомянутая сборка-аналог вернут только первую запись, т.е. максимум мы сможем выбрать таким способом по одному элементу для каждого списка.

Соответственно, нам нужно сделать реализации чего-то на подобие множественного ВПР, когда значения будут выбираться все значения, а не только первое.

Основная идея реализации

Основу в реализации такого множественного выбора будет составлять чрезвычайно полезная функция ИНДЕКС, которая возвращает значение ячейки, которая находится на пересечении указанных строки и столбца. Столбце у нас известен из условия – это столбец со всеми значениями (правый в «сырой» таблице), а вот номер строки мы будем подставлять динамически.

Данную функции еще часто используют в связке с другой полезной функцией ПОИСКПОЗ, однако, сейчас нам нужна не связка, поскольку номер строки (а именно за поиск номера строки в функции ИНДЕКС отвечает функция ПОИСКПОЗ) мы найдем отдельно.

Решение:

  1. Поиск номера строки

Видеоверсия

Текстовая версия

Для поиска номера строки воспользуемся довольно незамысловатой конструкцией:

=ЕСЛИ($A2=S$1;СТРОКА(A2))

Принцип работы данной формулы становится понятен, если взглянуть на следующий рисунок:

Поиск номера строки
Поиск номера строки

В заголовках записываем значений, которые соответствуют маркерам в первом столбце неупорядоченного списка, в нашем случае это: список1, список2, список3, затем сравниваем значение из заголовка с текущим маркером (в сравнении используются смешанные ссылки, чтобы не записывать одну и туже формулу для трех столбцов, а просто автозаполнением растянуть ее для 2-го и третьего списка), если условие выполняется, тогда возвращаем номер строки из первоначального неупорядоченного списка, если условие ложно, формула вернет ошибку.

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

  1. Упорядочивание промежуточных вычислений

Видеоверсия

Текстовая версия

Мы получили номера строк, которые разбросаны по всему диапазону вычислений, нам нужно их собрать и для этого, во-первых, следует построить простой номерной ряд, банально пронумеровав один столбец, а, во-вторых, с помощью функции НАИМЕНЬШИЙ, собрать все значения по каждому столбцу, отсортировав по возрастанию.

Функция НАИМЕНЬШИЙ похожа на вычисление минимального, т.е. функцию МИН, за тем исключением, что позволяет найти не только минимальное, но и 2-е, 3-е и т.д. наименьше значение после минимального.

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

=НАИМЕНЬШИЙ(I:I;$L2)

Первым аргументом функции указана ссылка полностью на строку, если будете указывать на диапазон, не забудьте позаботиться о том, чтобы его зафиксировать (сделав абсолютную или смешанную ссылку). Второй аргумент – это смешанная ссылка на вспомогательный ряд, т.е., в первом случае, когда ссылка идет на цифру «1», формула вернет минимальное значение, потом — 2-е после минимального и т.д.

Общая картина вычислений выглядит так:

Упорядочивание чисел с помощью функции НАИМЕНЬШИЙ
Упорядочивание чисел с помощью функции НАИМЕНЬШИЙ

Как и предполагалось, мы просто записали ранее разбросанные значения с вычислением номера строки.

  1. Построение финальной формулы

Видеоверсия

Текстовая версия

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

Таким образом, пишем:

=ИНДЕКС($B:$B;M2;1)

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

В конечном итоге получаем вот такой результат:

Результат применения функции ИНДЕКС
Результат применения функции ИНДЕКС

Все хорошо, за исключением ошибки, которая находится в не заполненных ячейках. Ошибку легко скрыть, если «обернуть» конечную формулу в функцию ЕСЛИОШИБКА, указав, в качестве второго аргумента, пустую ячейку (просто двойные кавычки).

=ЕСЛИОШИБКА(ИНДЕКС($B:$B;M2;1);"")

И вот такой результат:

Результат использования функции ЕСЛИОШИБКА для перехвата ошибок
Результат использования функции ЕСЛИОШИБКА для перехвата ошибок

  1. Последние штрихи

Видеоверсия

Текстовая версия

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

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

Процесс создания выпадающего списка в Excel детально рассмотрен в этой статье, здесь мы заострять внимание на этом не будем.

Файл с примером

Уважаемые пользователи, файл с примером вы можете также скачать в ветке нашего форума, где задать вопрос, или просто обсудить материал.

Чтобы скачивать файлы, пожалуйста, войдите.

Функции

Формула Применение в рамках данной задачи
ИНДЕКС (INDEX) Основа формулы, используется для сопоставления заголовков таблицы и заголовков диапазонов исходного списка
НАИМЕНЬШИЙ (SMALL) Используется для передачи первой ячейки плавающего диапазона в ВПР

Вспомогательные, т.е., без которых можно обойтись:

Формула Применение в рамках данной задачи
ЕСЛИОШИБКА (IFERROR) Используется для придания эстетического вида таблице, т.е. замене ошибок на пустые значения ячейки.

Ссылки по теме

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

Функции работы с данными в Excel

Как сделать так, чтобы ячейки в Excel не заполнялись нолями

Типы ссылок в MS Excel