Использование формул массива
В Excel имеется возможность заменить множество формул, показанных на рис. 9.12, одной. Для вычислений, производимых с использованием данных диапазона ячеек, может применяться одна формула – формула массива, включающая много формул (см. раздел "Формулы массива" главы 8).
Давайте на базе формул массива создадим в диапазоне А1:В6 таблицу, которая будет выполнять анализ расходов по заданным критериям. По окончании работы она должна выглядеть, как на рис. 9.13.
Рис. 9.13. Лист с числовыми данными и с формулами
В столбец А будем вводить критерии, в соответствии с которыми производится выборка данных из листа ЖурналРегистраци. В столбце В должны находиться предназначенные для этой цели формулы массива.
В ячейку А2 необходимо ввести дату, с которой начинается анализируемый период. В ячейке В2 должна содержаться формула:
{= СУММ(ЕСЛИ(Дата >= A2; Расход; 0))}Она суммирует все значения, которые вносятся в диапазон ячеек С1:С1000 (Расход) листа Журнал Регистрации, если в строке столбца А листа (Дата) содержится дата, равная или больше даты, указанной в ячейке А1 листа, где находится формула массива. Весь указанный диапазон ячеек А1:А1000 (Дата) и С1:С1.000 (Расход) обрабатывается программой как единое целое. Формула суммирует деньги, израсходованные семьей начиная с 5 февраля и заканчивая датой проведения расчета.
В ячейке В3 находится формула:
{= СУММ(ЕСЛИ(Дата <= A3; Расход; 0))}В ней анализируются даты, указанные в столбце А листа Журнал Регистрации, то есть определяется, являются они меньшими или равны значению даты, указанному в ячейке A3. При выполнении условия формула суммирует все значения, находящиеся в соответствующих строках диапазона С1:С1000 (Расход). Формула определяет сумму, израсходованную семьей за период со дня начала ведения учета по 15 февраля.
В ячейке В4 находится формула:
{= СУММ(ЕСЛИ(Кто = A4; Расход; 0))}…которая в столбце D (Кто) производит поиск значения, указанного в ячейке A3, и суммирует данные, внесенные в столбец С (Расход). При выполнении условия значения совпадают. Формула определяет, какая сумма денег была потрачена на нужды матери за весь период ведения учета в Excel.
Находящаяся в ячейке В5 формула:
{= СУММ(ЕСЛИ(Откуда_Куда = A5; Расход; 0))}…производит в столбце Е поиск значения, указанного в ячейке А5, и, при выполнении условия, суммирует данные, внесенные в столбец С. Формула определяет сумму, потраченную на приобретение обуви, за весь период ведения учета в Excel.
Следующая формула, находящаяся в ячейке В6:
{= СУММ(ЕСЛИ(На_что = A6; Расход; 0))}…выполняет в столбце F (На_что) поиск значения, указанного в ячейке А6, и суммирует данные, внесенные в столбец С, при выполнении условия. Формула определяет, какая сумма денег за период ведения учета в Excel была потрачена на покупку летней обуви.
Проанализируем полученные в результате наших вычислений данные (рис. 9.13). При этом будем исходить из того, что каждая из формул решает отдельную задачу:
- после 5.02.2002 г. расходы семьи составили 3352.00 руб.;
- с момента ведения учета до 15.02.2002 г. было потрачено 2900.00 руб.; м – на нужды матери за все время ведения учета израсходовано 856.00 руб.;
- на приобретение обуви за время ведения учета семьей потрачено 952.00 руб.;
- на приобретение летней обуви за время ведения учета потрачено 644.00 руб. В результате вложения одной из формул в другую автоматически выполняется учет двух условий. Например, вложив формулу из ячейки В5 в ячейку В3, можно найти сумму потраченных денег на обувь с момента организации учета в Excel и до той даты, которая указана в ячейке A3.
В зависимости от указанных в ячейках А2 и A3 дат можно выбирать временной диапазон, в котором производится анализ. Ячейки А4, А5 и Аб задают направление поиска.