Создание модуля с вложенной формулой массива

О цели и принципах вложения формул достаточно подробно было рассказано в предыдущих главах, поэтому на этом вопросе мы останавливаться не будем. Для решения же поставленной задачи требуется произвести последовательное вложение всех формул, приведенных на рис. 9.13, в одну.

Начните с того, что формулу, находящуюся в ячейке ВЗ; вложите в формулу ячейки В2. С этой целью нужно активизировать ячейку ВЗ и в строке формул выделить фрагмент

ЕСЛИ(Дата< =А; Расход; 0)

Скопируем его в буфер обмена. Теперь необходимо отключить режим редактирования формулы в ячейке ВЗ, нажав комбинацию клавиш [Ctrl + Shift+ Enter] или клавишу [Esc].

Перейдите к ячейке В2 и в строке формул выделите второй аргумент функции ЕСЛИ, являющийся именем диапазона ячеек на листе Журнал Регистрации - Расход.

Далее необходимо выполнить вставку из буфера обмена скопированного фрагмента формулы. Нажав комбинацию клавиш [Ctrl+Shift+Enter], присвойте формуле признак массива.

Во всех пяти формулах, находящихся в ячейках В2: В6, второй аргумент функции ЕСЛИ одинаков в том смысле, что он представляет собой имя диапазона ячеек Расход. На место этого аргумента производится вложение следующей формулы.

В результате вложения формулы из ячейки ВЗ в формулу из ячейки В2 должна получиться следующая формула:

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

Она производит суммирование всех значений, указанных в ячейках диапазона А1: А1000 на листе ЖурналРегистраций, при условии, что эти значения относятся к интервалу дат, заданному в ячейках В2 и ВЗ. В этой формуле определяется сумма денег, израсходованная семьёй за указанный период времени.

Скопируйте такой же фрагмент формулы из ячейки В4 и вставьте его вместо второго аргумента второй функции ЕСЛИ формулы, находящейся в ячейке В2. Полученная формула должна быть такой:

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

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

Дополнив формулу в ячейке В2 фрагментом формулы из ячейки В5, вы получите:

{=СУММ(ЕСЛИ(Дата> =А2;

ЕСЛИ(Дата< =А3; ЕСЛИ(Кто=А4;

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

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

Последнее вложение фрагмента формулы из ячейки В6 даст следующую формулу (рис. 9.14):

{=СУММ(ЕСЛИ(Дата> =А2;

ЕСЛИ(Дата< =А3;

ЕСЛИ(Кто=А4;

ЕСЛИ(Откуда_Куда=А5;

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

Рис. 9.14. Итоговая формула, полученная путем вложения нескольких формул

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

Какую информацию позволяет получить модуль

Модуль, показанный на рис. 9.14, позволяет для любого указанного периода получить следующие данные:

- сколько денег на какого члена семьи потрачено;

- сколько денег проходит по определенной статье расходов;

- что именно приобретено по этой статье расходов.

Модуль состоит из шести ячеек, пять из которых представляют собой вход модуля (А2: А6), а одна ячейка (В2) производит вычисления и одновременно является его выходом. Изменяя текст в ячейках А4: А6, можно получить детальную информацию о расходах за период времени, указанный в ячейках А2 и A3. По-разному комбинируя первичные формулы, входящие во вложенную формулу, можно получить формулы, с помощью которых производятся различного рода исследования и оценки.

Применение модуля для анализа данных

Рассмотрим принцип применения созданных формул и внедрения их в таблицы анализа. Опишем пять таблиц, задача которых - показать все преимущества использования полученных формул.

Таблица 1: расходы на каждого члена семьи и по статьям

На рис. 9.15 представлен лист с таблицей и диаграммами, которые отражают расходы на каждого члена семьи и структуру расходов по статьям. Лист включает четыре области и две диаграммы.

- Область задания временного интервала, в котором производится расчет (В1: В2):

- ячейка В1- с какого числа;

- ячейка В2 - по какое число.

- Область расчета расходов на каждого члена семьи (А4: В9):

- диапазон А6: А9 - список членов семьи и пункт общих затрат;

- диапазон В6: В9 - формулы расчета расхода на каждого члена семьи и общих затрат;

- ячейка В4 - итоговая сумма расходов.

- Область расчета расходов по статьям (А11: В16):

- диапазон А12: А16 - перечень статей расходов;

- диапазон В12: В16 - формулы расчета по каждой статье;

- ячейка ВП - итоговая сумма расходов.

- Область определения доли расходов по каждому члену семьи и конкретной статье в общей сумме расходов (область D1: D16).

- Диаграммы:

- объемный вариант разрезанной круговой диаграммы относительных расходов на каждого члена семьи;

- объемный вариант круговой диаграммы относительных расходов по отдельным статьям.

Ячейкам с датами, задающими период вычислений, присвоим имена. Например, для ячейки В] задайте имя Периоде, а для ячейки В2 - имя ПериодПо.

Формула в ячейке В4 листа Расходы1 производит вычисление всех расходов за указанный период времени:

=СУММ(В6: В9)

В ячейке В6 листа Расходы1 должная быть формула, которая определяет сумму денег, потраченную за указанный период времени на конкретного члена семьи:

{=СУММ(ЕСЛИ(Дата> = Периоде; ЕСЛИ(Дата< = ПериодПо; ЕСЛИ(Кто=А6; Расход; 0); 0); 0))}

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

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

Рис. 9.15. Лист с таблицей и диаграммами, отражающими расходы на каждого члена семьи

Скопируйте ее из строки формул и произведите вставку в ячейку В6 листа Расходы]. В полученной формуле необходимо выполнить следующие изменения:
1. Выделив адрес ячейки А2, отметьте мышью на листе Расходы 1 ячейку В1. При этом вместо имеющейся в формуле ссылки появится имя Периоде.

2. Выделив адрес ячейки A3, отметьте мышью на листе Расходы} ячейку В2. После этого содержащаяся в формуле ссылка будет указывать на имя ПериодПо.

3. Выделив адрес ячейки А4, поместите указатель мыши в ячейку А6 на листе Расходы I.

4. Нажав комбинацию клавиш [Ctrl+Shift+Enter], введите признак формулы массива.

Скопируйте полученную формулу в ячейки В7: В9. Формула в ячейке В12 создается аналогичным образом. Только в этом случае выполняется вложение формул из ячеек В2, ВЗ и В5 модуля (рис. 9.13):

{=СУММ(ЕСЛИ(Дата> = ПериодС; ЕСЛИ(Дата< = ПериодПо;

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

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

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

=В6/$В$4