4.3. Создание таблицы подстановки с несколькими расчетными формулами
В качестве примера рассмотрим таблицу подстановки, которая рассчитывает сумму НДС и выручку от продажи товаров различных наименований, которые отпускаются в количестве 1 штуки. Цены товаров указаны в справочнике, а наименования отпускаемых товаров в последовательности их отпуска поступают в Excel из внешнего источника – из файла Товары.txt.
Рис. 4.4
Рис. 4.5
В книгу Итоги вставить новый лист (рис. 4.6) и назвать его Таблица подстановки 2. На этом листе, начиная с ячейки В4, разместить макет таблицы, содержащей 6 столбца и 13 строк. В заголовках столбцов ввести названия: № п/п, Наименование, Номер в справочнике, Цена, Сумма НДС, Выручка. Отформатировать ячейки заголовков столбцов аналогично заголовкам столбцов таблицы на листе Таблица подстановки 1. Над таблицей разместить заголовок и текущую дату, а в ячейках С3 и D3 поместить значение ставки НДС 0,2.
Выделить ячейку С6 и с помощью команды Данные, Внешние данные, Импорт текстового файла открыть файл Товары.txt. При этом запускается Мастер текстов (импорт), включающий 3 шага настройки импорта.
Рис. 4.7
Рис. 4.6
На втором шаге сбросить все флажки символов-разделителей и в списке ограничителя строк выбрать вариант нет. Нажать кнопку Далее.
На третьем шаге никаких действий настройки не требуется. Нажать кнопку Готово. Откроется окно Импортировать данные. В этом окне нажать кнопку Свойства. Откроется окно Свойства внешнего диапазона (рис. 4.7).
В этом окне установить флажки и переключатели так, как показано на рис. 4.7. Нажать кнопку ОК. После этого ячейки столбца С заполнятся наименованиями товаров из файла Товары.txt.
В полученной таблице ячейка С5 будет выполнять функцию ячейки ввода. В нее последовательно автоматически будут подставляться текстовые значения из ячеек С6:С15 – наименования товара Ячейки D5:G5 предназначены для размещения соответствующих расчетных формул.
В ячейку D5 следует поместить формулу выбора номера строки справочника цен, в которой находится наименование товара, совпадающее с наименованием, которое последовательно будет подставляться из ячеек С6:С15: в ячейку ввода С5. Для этого предназначена функция ПОИСКПОЗ, которая сравнивает значение, находящееся в ячейке С5 с ячейками B2:B11 справочника цен и возвращает целое число, равное номеру строки справочника цен при совпадении значений. Однако, если наименования товара введены не во все ячейки С6:С15 (рис. 4.8), то в очередной раз в ячейку ввода С5 не будет подставлено текстовое значение наименования товара, и функция ПОИСКПОЗ будет выполнена с ошибкой, так как пустая строка отсутствует в ячейках B2:B11 справочника цен. Чтобы исключить возможную ошибку, следует выполнять функцию ПОИСКПОЗ только если в ячейке ввода С5 имеется текстовое значение. Для проверки этого условия используется функция ЕТЕКСТ. Тогда результирующая формула примет вид: =ЕСЛИ(ЕТЕКСТ(C5);ПОИСКПОЗ(C5;'Справочник цен'!B2:B11);""). При наличии в ячейке С5 текстового значения выполняется функция ПОИСКПОЗ, которая помещает в соответствующую ячейку диапазона D6:D15 целое число, равное номеру строки, по которому можно будет выбрать цену товара. Если в ячейке С5 текстовое значение отсутствует, то в соответствующую ячейку диапазона D6:D15 будет помещено «пустое» значение – "" (две парные кавычки).
В ячейку Е5 ввести формулу выбора цены товара из справочника цен с использованием функция ИНДЕКС. Эту функцию следует выполнять только в том случае, если в ячейке ввода D5 находится число – номер строки. В противном случае в ячейку Е5 поместить 0. Формула имеет вид: =ЕСЛИ(ЕЧИСЛО(D5);ИНДЕКС('Справочник цен'!C2:C11;D5);0).
В ячейку F5
Рис. 4.8
Далее следует выделить диапазон ячеек С5:G15, выполнить команду Данные, Таблица подстановки, в окне Таблица подстановки в поле Подставлять значения по строкам в указать ячейку ввода $С$5. Нажать кнопку ОК. После этого таблица подстановки заполнится расчетными значениями (рис. 4.8).
Для коррекции исходных данных необходимо развернуть окно Блокнот с панели задач, изменить наименования товара в файле Товары.txt и снова сохранить этот файл. Для обновления данных вручную щелкнуть правой кнопкой мыши на листе Таблица подстановки 2 в ячейке С6 и из контекстного меню выбрать команду Обновить данные.
Для автоматического обновления данных следует из контекстного меню выбирать команду Свойства диапазона данных. Откроется окно Свойства внешнего диапазона (см. рис. 4.7). В этом окне установить флажок обновлять каждые и указать период времени обновления, например, 1 мин. Через каждую минуту будет происходить обновление данных и пересчет всех значений в таблице подстановки. Если в течение 1 минуты внести изменения в файл Товары.txt и сохранить его, то в ячейках С6:С15 автоматически появятся обновленные наименования товара. Повторить 1-2 раза коррекцию данных в файле Товары.txt и проверить автоматическое обновление и пересчет данных в таблице подстановки.