Использование формул массива

В Excel имеется возможность заменить множество формул, показанных на рис. 9.12, одной. Для вычислений, производимых с использованием данных диапазона ячеек, может применяться одна формула - формула массива, включающая много формул (см. раздел " Формулы массива" главы 8).

Давайте на базе формул массива создадим в диапазоне А1: В6 таблицу, которая будет выполнять анализ расходов по заданным критериям. По окончании работы она должна выглядеть, как на рис. 9.13.

Рис. 9.13. Лист с числовыми данными и с формулами

В столбец А будем вводить критерии, в соответствии с которыми производится выборка данных из листа ЖурналРегистраци. В столбце В должны находиться предназначенные для этой цели формулы массива.

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

{=СУММ(ЕСЛИ(Дата> =А2; Расход; 0))}

Она суммирует все значения, которые вносятся в диапазон ячеек С1: С1000 (Расход) листа Журнал Регистрации, если в строке столбца А листа (Дата) содержится дата, равная или больше даты, указанной в ячейке А1 листа, где находится формула массива. Весь указанный диапазон ячеек А1: А1000 (Дата) и С1: С1.000 (Расход) обрабатывается программой как единое целое. Формула суммирует деньги, израсходованные семьей начиная с 5 февраля и заканчивая датой проведения расчета.

В ячейке ВЗ находится формула

{=СУММ(ЕСЛИ(Дата< =АЗ; Расход; 0))}

В ней анализируются даты, указанные в столбце А листа Журнал Регистрации, то есть определяется, являются они меньшими или равны значению даты, указанному в ячейке A3. При выполнении условия формула суммирует все значения, находящиеся в соответствующих строках диапазона С1: СЮОО (Расход). Формула определяет сумму, израсходованную семьей за период со дня начала ведения учета по 15 февраля.

В ячейке В4 находится формула

{=СУММ(ЕСЛИ(Кто=А4; Расход; 0))}

которая в столбце D (Кто) производит поиск значения, указанного в ячейке A3, и суммирует данные, внесенные в столбец С (Расход). При выполнении условия значения совпадают. Формула определяет, какая сумма денег была потрачена на нужды матери за весь период ведения учета в Excel.

Находящаяся в ячейке В5 формула

{=СУММ(ЕСЛИ(Откуда_Куда=А5; Расход; 0))}

производит в столбце Е поиск значения, указанного в ячейке А5, и, при выполнении условия, суммирует данные, внесенные в столбец С. Формула определяет сумму, потраченную на приобретение обуви, за весь период ведения учета в Excel.

Следующая формула, находящаяся в ячейке В6,

{=СУММ(ЕСЛИ(На_что=А6; Расход; 0))}

выполняет в столбце F (На_что) поиск значения, указанного в ячейке Аб, и суммирует данные, внесенные в столбец С, при выполнении условия. Формула определяет, какая сумма денег за период ведения учета в Excel была потрачена на покупку летней обуви.

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

- после 5.02.2002 г. расходы семьи составили 3352, 00 руб.;

- с момента ведения учета до 15.02.2002 г. было потрачено 2900, 00 руб.; м на нужды матери за все время ведения учета израсходовано 856, 00 руб.;

- на приобретение обуви за время ведения учета семьей потрачено 952, 00 руб.;

- на приобретение летней обуви за время ведения учета потрачено 644, 00 руб. В результате вложения одной из формул в другую автоматически выполняется учет двух условий. Например, вложив формулу из ячейки В5 в ячейку ВЗ, можно найти сумму потраченных денег на обувь с момента организации учета в Excel и до той даты, которая указана в ячейке A3.

В зависимости от указанных в ячейках А2 и A3 дат можно выбирать временной диапазон, в котором производится анализ. Ячейки А4, А5 и Аб задают направление поиска.