Заполнение таблиц с помощью двухсвязного списка

Опубликовано в Функции работы со ссылками и массивами

В качестве примера рассмотрим, как можно автоматически заполнить таблицу Заказ наименованиями товара и соответствующими ценами, имеющимися в таблице Прейскурант, используя выпадающий список и функции ДВССЫЛ и ВПР.

Первый шаг — заполнение столбца Категория таблицы Заказ.

Для этого воспользуемся выпадающим списком: выделяем ячейки А3:А5, вызываем диалоговое окно Проверка вводимых значений, выбираем тип данных Список. В качестве источника можно указать текстовые значения: Конфеты; Торт или создать дополнительный диапазон с перечнем категорий и сослаться на него.

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

Второй шаг — заполнение столбца Наименование.

Вначале создадим имена Конфеты и Торт, которые будут ссылаться на диапазоны С3:С5 и С6:С8 листа Прейскурант с соответствующими наименованиями товаров.

Далее, заполним столбец Наименование, значения которого также будем выбирать из выпадающего списка, причем, содержимое списка будет зависеть от выбранного в первом столбце товара. Для этого используем функцию ДВССЫЛ(ссылка_на_ячейку;а1), которая преобразовывает значение ячейки, заданной аргументом ссылка_на_ячейку, в ссылку (а1 — указывает на тип ссылки; если аргумент опущен, ссылка имеет стандартный вид: А1).

Выделяем ячейки В3:В5 таблицы заказов и вызываем диалоговое окно Проверка вводимых значений, в котором указываем тип данных — Список, а в качестве источника вводим формулу:

=ДВССЫЛ(А3:А5)

Таким образом, в столбце Наименование получим выпадающие списки, содержимое которых зависит от вида товара, указанного в столбце Категория. Если в первом списке выбрано значение Конфеты, то с помощью функции ДВССЫЛ оно преобразуется в адрес — имя диапазона С3:С5 листа Прейскурант, и тогда второй список отображает наименования конфет. Если же в первом столбце выбрана категория Торт, то во втором списке отобразятся наименования тортов.

Теперь заполняем столбец, выбирая нужные значения из полученных списков, и указываем требуемое количество товара в столбце Кол-во.

Третий шаг — определение суммы заказа по каждому наименованию.

Для этого, можно использовать функцию ВПР (Искомое_значение; Таблица; Номер_столбца; Интервальный_просмотр), которая ищет заданное значение в крайнем левом столбце массива и возвращает значение из другого столбца той же строки. Она имеет следующие аргументы:

Искомое_значение — значение, которое необходимо найти в таблице (может быть задано значением или ссылкой);

Таблица — диапазон данных, в первом столбце которого выполняется поиск указанного значения;

Номер_стол6ца — номер столбца диапазона, указанного в аргументе Таблица, из которого необходимо взять значение (строка соответствует положению искомого значения);

Интервальный_просмотр — логическое значение: ЛОЖЬ — допускается только точное соответствие параметру; ИСТИНА или аргумент опущен — поиск осуществляется до ближайшего значения, которое меньше аргумента Искомое_значение. При этом интервальный диапазон ячеек должен быть отсортирован по возрастанию содержимого первого столбца, иначе результат будет непредсказуем. Значение ИСТИНА используется по умолчанию.

Для заполнения столбца Сумма, р. в таблице Заказ нужно:

1. Установить для интервала D3:D5 формат Денежный.

2. Выбрать ячейку 03 и нажатием кнопки &А Вставка функции в строке формул вызвать мастер функций.

3. В диалоговом окне мастера в категории Ссылки и массивы выбрать функцию ВПР и нажать кнопку ОК.

4. В открывшемся окне ввести аргументы функции:

Искомое_значение: В3;

Таблица:       Прейскурант! $С$3:$Е$8;

Номер_столбца:  3;

Интервальный_просмотр: ЛОЖЬ и нажать кнопку ОК.

5. Полученную в ячейке D3 формулу распространить на ячейки D4:D5 — получим суммы заказов по каждому виду товара.

=ВПР ( В3; Прейскурант! $С$3: $Е$8; 3; ЛОЖЬ) *С3