Анализ доходов семьи в разные периоды времени

Если учет доходов и расходов ведется в семье в течение довольно продолжительного периода, то может возникнуть желание проанализировать, какие доходы имели члены семьи в разное время.
Для этой цели сформируем в области J1:N28 журнала регистрации таблицу учета ежемесячных доходов каждого члена семьи.
Предварительная таблица анализа доходов
Решение задачи начнем с построения промежуточной таблицы с простыми формулами , которые затем будут объединены. Эта таблица выполняет следующие функции:
— по имени члена семьи, введенному в ячейку К1, определяет в столбце N его доход за все время учета;
— по номеру года, введенному в ячейку M1, автоматически формирует год в столбце К, на основании которого в столбце L будет рассчитываться общий доход семьи;
— по номеру месяца, введенному в ячейку М2, формирует данные в столбце J; номера месяцев в столбце должны оказывать влияние на столбец К при определении номера года;
— по номеру месяца в столбце J производить вычисление доходов, полученных всеми членами семьи за данный месяц.
Формула в ячейке J5 задает для таблицы номер месяца, с которого начинается анализ:

=М2

Формула в ячейке J6 определяет, какой номер месяца указан в ячейке выше:

=ECЛИ(J5=12;1;J5+1)

Если это номер 12 (последний месяц года), то ячейка будет содержать значение 1 (первый месяц года). Во всех других случаях к значению предыдущей ячейки прибавляется число 1 (следующий месяц).
Формула в ячейке К5 задает номер года, с которого начинается анализ:

=М1

Формула в ячейке Кб сравнивает номер месяца, указанный в столбце J, со значением 1 (первый месяц следующего года):

=ЕСЛИ(J6=1;К5+1;К5)

Если результат сравнения положительный, к номеру года в ячейке К5 прибавляется 1 (следующий год). В противном случае номер года остается без изменений.
В ячейке L5 должна располагаться формула массива:

{=СУММ(ЕСЛИ(ГОД(Дата)=К5;Доход;0))}

Она сравнивает номер года, сформированный в столбце К, с номером года в записях журнала регистрации. Если они совпадают, формула определяет общий доход всех членов семьи за указанный год.
В ячейке М5 также содержится формула массива. Она сравнивает номер месяца, сформированный в столбце J, с номером месяца в записях журнала регистрации. Если они совпадают, формула вычисляет доход, полученный всеми членами семьи за этот месяц:

{=СУММ(ЕСЛИ(МЕСЯЦ(Дата)=J5;Доход;0))}

В ячейке N5 опять-таки находится формула массива. Она производит вычисление дохода члена семьи, имя которого введено в ячейку К1, за весь период ведения учета:

{=СУММ(ЕСЛИ(Кто=$К$1;Доход;0))}

Доход члена семьи за конкретный период времени
Произведите вложение формул для диапазона ячеек L5:N5. После этой операции формула в ячейке L5 должна иметь вид:

{=СУММ(ЕСЛИ(ГОД(Дата)=К5;ЕСЛИ(МЕСЯЦ(Дата)=J5;ЕСЛИ(Кто=$К$1;Доход;0);0);0))}

В ней вычисляется доход за год, указанный в строке 5 столбца К, и за месяц, который указан в столбце J, того члена семьи, чье имя содержится в ячейке К1. При изменении любого из трех заданных параметров таблица обновляется.
Для того чтобы проследить, как изменяется ежемесячный средний доход за период учета, в освободившуюся ячейку М5 введите формулу

=ОКРУГЛ(СУММ(L$5:L5)/СЧЕТЕСЛИ(К$5:К5;">0");2)

которая делит всю полученную сумму

СУММ(L$5:L5)

на количество месяцев в периоде

СЧЕТЕСЛИ(K$5:К5;">0")

и округляет результат до копеек.
В завершение измените заголовки в таблице, приведенной на. Вместо заголовков в ячейках L4 и М4 введите новые — Доход и Средний соответственно .
Не прибегая к мастеру диаграмм, выделите диапазон ячеек L4:L28, нажмите клавишу [F11], и Excel построит на отдельном листе Диаграмма1 гистограмму получаемых доходов .
Учет при мелкотоварном производстве
Разработанные нами таблицы можно применять не только для учета семейных доходов и расходов. Продемонстрируем это на следующем примере. Предположим, частный предприниматель, специализирующийся на изготовлении столярных изделий под заказ, решил вести расчеты с помощью электронных таблиц, приняв за основу таблицы, созданные для учета семейных доходов и расходов.
Предприниматель изготавливает лишь такую продукцию, как столы и стулья (два наименования). Эти изделия условно делятся на два вида: большие и маленькие. По желанию заказчика он может изготовить их либо из сосны, либо из дуба. В процессе изготовления применяются следующие материалы и инструменты: лак, клей, шурупы.
От журнала регистрации к журналу операций
Для ведения учета в нужном объеме в журнал регистрации следует внести некоторые изменения. Переименуйте лист, на котором он расположен, в ЖурналОпе-раций. Для отражения производимых операций измените названия столбцов: D — Наименование, Е — Размер, F — Из чего.
Удалите имена, которые были ранее присвоены столбцам журнала. Для этого нужно, выполнив команду Вставка/Имя/Присвоить, вызвать диалоговое окно Присвоение имени, выделить старое имя в списке Имя и нажать кнопку Удалить.
Выделив диапазон ячеек, в котором расположена таблица журнала операций, присвойте ячейкам новые имена, нажав комбинацию клавиш [Ctrl+Shift+F3].
Заполнение журнала операций
Информацию в журнал операций рекомендуем заносить следующим образом.
1. Данные о покупке материалов (денежные затраты):
   - столбец Наименование — наименование материала;
   - столбец Расход — сумма, уплаченная за материал.
2. Сведения об использовании материалов:
   - столбец Что именно — наименование материала;
   - столбцы Наименование, Размер и Из чего — сведения об изделии, на которое расходуется материал;
   - столбец Дохой — стоимость использованного материала с отрицательным знаком.
3. Операции по продаже готовых изделий:
   - столбцы Наименование, Размер и Из него — сведения об изделии;
   - столбец Доход — сумму, полученную от продажи изделия.
Итак, журнал операций ведется, теперь настало время оценить результаты деятельности предпринимателя.

 

Таблица 1: покупка и расход материалов
На показан лист с таблицами и диаграммой, с помощью которых можно проследить за операциями по приобретению и расходу материалов. Здесь же рассчитываются и остатки материала. Лист разделен на пять областей.
— Область определения временного интервала, за который производится расчет:
   - ячейка В1 — с какого числа;
   - ячейка В2 — по какое число,
Область расчета суммы расходов на приобретение материалов (область А4:В10):
   - диапазон А6:А10 — название материала;
   - диапазон В6:В10 — расчет сумм, потраченных на приобретение каждого материала;
   - ячейка В4 — сумма, уплаченная за все материалы.
— Область расчета суммы израсходованных материалов (область А12:В18):
   - диапазон А14:А18 — название материала;
   - диапазон В14:В18 — расчета стоимости израсходовано материала;
   - ячейка В12 — стоимость всех израсходованных материалов,
— Область расчета остатков материалов (область А20:В26):
   - диапазон А22:А26 — название материала;
   - диапазон В22:В26 — расчет стоимости остатков материала;
   - ячейка В20 — полная стоимость остатков материалов.
Объемная разрезанная круговая диаграммы, демонстрирующая расход материала на все изделия.
В ячейке В6 должна содержаться формула, предназначенная для вычисления суммы, израсходованной за определенный период времени на приобретение материала, указанного в ячейке А6:

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

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

{=-СУММ(ЕСЛИ(Дата>=ПериодС;ЕСЛИ(Дата<=ПериодПо;
ЕСЛИ(Что_именно=А14;Доход;0);0);0))}

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

=В6-В14 

 

Таблица 2: расход материалов на изделия
В таблице на листе Расходы2 отражается расход материалов на изготовление изделий всех наименований. Из нее можно почерпнуть информацию и о стоимости материалов, израсходованных на изготовление определенных изделий.
Ячейки В4:Е4 и F4:T4 объединены и имеют адреса В4 и F4 соответственно. Ячейки В5:С5, D5:E5, F5:G5 и Н5:15 также объединены и имеют адреса В5, D5, F5 и Н5 соответственно. Формула в ячейке В7 должна выглядеть следующим образом:

{=-СУММ(ЕСЛИ(Дата>=ПериодС;
ЕСЛИ(Дата<=ПериодПо;ЕСЛИ(Из_чего=$В$4;
ЕСЛИ(Наименование=$В$5;ЕСЛИ(Размер=B$6;
ЕСЛИ(Что_именно=$А7;Доход;0);0);0);0);0);0))}

Она определяет, сколько материала, указанного в ячейке А7, потрачено на изготовление маленьких дубовых столов за указанный период времени. В результате копирования ячейки В7 с формулой и последующей вставки в ячейку С7 изменится только ссылка на ячейку, указывающую размер изделия.
После вставки этой же формулы в ячейку D7 необходимо скорректировать адрес ячейки, указывающей наименование изделия. Измените адрес на $D$5. Формула в ячейке D7 будет иметь вид:

{=-СУММ(ЕСЛИ(Дата>=ПериодС;
ЕСЛИ(Дата<=ПериодПо;ЕСЛИ(Из_чего=$В$4/ЕСЛИ(Наименование=$D$5;
ЕСЛИ(Размер=D$6;ЕСЛИ(Что_именно=$А7;Доход;0);0);0);0);0);0))}

При копировании формулы в ячейку F7 скорректируйте адреса ячеек, указывающих наименование изделия и материал, из которого они изготовлены. Измените адреса на SFS5 и SFS4 соответственно. Формула в ячейке F7 будет выглядеть следующим образом:

{=-СУММ(ЕСЛИ(Дата>=ПериодС;
ЕСЛИ(Дата<=ПериодПо;ЕСЛИ(Из_чего=$В54;ЕСЛИ(Наименование=$D$5;
ЕСЛИ(Размер=Е$6;ЕСЛИ(Что_именно=$А7;Доход;0);0);0);0);0);0))}

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

{=СУММ(ЕСЛИ(Дата>=ПериодС;ЕСЛИ(Дата<=ПериодПо;ЕСЛИ(Наименование=В$5; 
ЕСЛИ(Размер=$А6;ЕСЛИ(Что_именно=0;ЕСЛИ(Из_чего=$В$4;Доход;0);0);0);0);0);0))}

Формулы в столбцах С, D и Е строятся на ее основе по тем же принципам, что и в предыдущем примере. В столбце F вычисляются итоговые значения.
Таблица 4: распределение прибыли по изделиям
Таблица на листе Прибыль отражает, какая прибыль получена от реализации изделия каждого вида. Формула в ячейке В6 имеет вид:

{=СУММ(ЕСЛИ(Дата>=ПериодС;
ЕСЛИ(Дата<ПериодПо;ЕСЛИ(Наименование=В$5;
ЕСЛИ(Размер=$А6;ЕСЛИ(Что_именно=0;
ЕСЛИ(Из_чего=$В$4;Доход;0);0);0);0);0);0))+СУММ
(ЕСЛИ(Дата>=ПериодС;ЕСЛИ(Дата<ПериодПо;
ЕСЛИ(Наименование=В$5;ЕСЛИ(Размер=$А6;
ЕСЛИ(Что_именно>0;ЕСЛИ(Из_чего=$В$4;Доход;0);0);0);0);0);0))}

В формуле производится сложение двух компонентов, значения которых формируются в зависимости от выполнения следующих условий:
— значения в столбце Что_именно равны нулю;
— значения в столбце Что_именно больше нуля.

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