Создание собственных средств анализа данных

Если вас не устраивают функции анализа данных, которые имеются в Excel, можно провести нужный анализ, составив соответствующие формулы.
Конечно, средства анализа должны предоставлять возможность обобщать внесенные в журнал регистрации данные в любых комбинациях и за любой период. Лучше, если готовые таблицы с формулами анализа будут без вмешательства пользователя производить вычисления и представлять результаты в удобном виде.
Решение задачи такого рода продемонстрируем на следующем примере: попытаемся на основании данных журнала регистрации определить сумму, потраченную за период с 5 по 15 февраля на покупку летней обуви для матери. Задачу придется разбить на несколько этапов:
1. Определение расходов всей семьи начиная с 5-го февраля по настоящее время.
2. Определение расходов всей семьи с начала регистрации таковых по 15 февраля включительно.
3. Определение суммы, потраченной на покупки для матери за все время регистрации расходов.
4. Определение суммы, потраченной на приобретение обуви за все время регистрации расходов.
5. Определение суммы, потраченной на приобретение летней обуви за все время регистрации расходов.

 

Использование изученных ранее методов
Давайте попытаемся определить сумму, потраченную всей семьей за период с 5 по 15 февраля. Это можно сделать двумя методами.
— Во-первых, можно, выделив на рабочем листе ЖурналРегистрации строки 2:27, нажать на стандартной панели инструментов кнопку Сортировка по возрастанию, после чего, активизировав, например, ячейку В30, нажать кнопку Автосумма стандартной панели инструментов и выделить на рабочем листе диапазон С5:С16. Полученная в результате формула будет иметь вид:

=СУМM(С5:С16)

В случае изменения данных операцию необходимо будет повторить.
— Во-вторых, можно создать на отдельном листе таблицы с формулами и производить нужные расчеты с их помощью.
Рассмотрим подробнее второй метод. Перейдите на лист Лист1 и сформируйте в первых двух строках шапку таблицы, как показано на. В ячейки A3 и С3 занесите даты.
Теперь мы перенесем из журнала регистрации данные, которые относятся к интересующему нас периоду. В первую очередь необходимо определить записи, у которых в столбце А журнала регистрации дата равна или больше даты, указанной в ячейке A3 (то есть 5 февраля). Для этого занесите в ячейку А4 следующую формулу:

=ЕСЛИ(ЖурналРегистрации!А2>=$А$3;1;0)

Она работает следующим образом. Если условие соблюдается, формула выдает значение 1. Если условие не соблюдается, то будет выдано значение 0.
Содержимое ячейки А4 анализируется формулой в ячейке В4. Если в ячейке А4 содержится 1, то ячейка В4 должна возвратить значение, находящееся в соответствующей ячейке столбца С (расходы) листа ЖурналРегистрации. Таким образом, формула в ячейке В4 будет имеет вид

=ЕСЛИ(А4=0;0;ЖурналРегистрации!С2)

В столбце С листа Лист1 проводится анализ даты, указанной в столбце А листа ЖурналРегистрации. Здесь проверяется, является ли она меньшей или равной дате, указанной в ячейке СЗ. Поэтому ячейка С4 содержит такую формулу:

=ЕСЛИ(ЖурналРегистрации!А2<=$С$3;1;0)

Формулы в столбце D аналогичны формулам в столбце В. В частности, ячейка D4 содержит следующую формулу:

=ЕСЛИ(С4=0;0;ЖурналРегистрации!С2)

В столбце Е проверяется, выполняются или нет условия в формулах столбцов А и С. Если да, то в ячейке Е4 отражается значение, указанное в ячейке D4. Формула имеет вид:

=ЕСЛИ(А4+С4=2;D4;0)

Далее в ячейке ЕЗ происходит суммирование всех отобранных предыдущими формулами значений. В ячейке ЕЗ будет такая формула:

=СУММ(Е4:Е1000)

На приведен рабочий лист с числовым примером решения данного задания, а на — с формулами.
Поставленную в начале этого раздела задачу можно решить, если далее проводить сравнения со словами: "Мать", "Обувь" и "Летняя", а затем вложить одну формулу в другую. Поскольку журнал регистрации может включать сотни строк, то сотни таких формул займут много места и значительно снизят быстродействие программы. Избежать этой проблемы позволяют формулы массива.
Использование формул массива
В Excel имеется возможность заменить множество формул, показанных на, одной. Для вычислений, производимых с использованием данных диапазона ячеек, может применяться одна формула — формула массива, включающая много формул ("Формулы массива").
Давайте на базе формул массива создадим в диапазоне А1:В6 таблицу, которая будет выполнять анализ расходов по заданным критериям. По окончании работы она должна выглядеть, как на.
В столбец А будем вводить критерии, в соответствии с которыми производится выборка данных из листа ЖурналРегистраци. В столбце В должны находиться предназначенные для этой цели формулы массива.
В ячейку А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 была потрачена на покупку летней обуви.
Проанализируем полученные в результате наших вычислений данные . При этом будем исходить из того, что каждая из формул решает отдельную задачу:
— после 5.02.2002 г. расходы семьи составили 3352,00 руб.;
— с момента ведения учета до 15.02.2002 г. было потрачено 2900,00 руб.; м на нужды матери за все время ведения учета израсходовано 856,00 руб.;
— на приобретение обуви за время ведения учета семьей потрачено 952,00 руб.;
— на приобретение летней обуви за время ведения учета потрачено 644,00 руб. В результате вложения одной из формул в другую автоматически выполняется учет двух условий. Например, вложив формулу из ячейки В5 в ячейку ВЗ, можно найти сумму потраченных денег на обувь с момента организации учета в Excel и до той даты, которая указана в ячейке A3.

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