Уроки Microsoft Excel

Работа с таблицами Excel

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

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

Для этой цели сформируем в области J1:N28 журнала регистрации (рис. 9.1) таблицу учета ежемесячных доходов каждого члена семьи.

Предварительная таблица анализа доходов

Решение задачи начнем с построения промежуточной таблицы с простыми формулами (рис. 9.26), которые затем будут объединены. Эта таблица выполняет следующие функции:

- по имени члена семьи, введенному в ячейку К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)

Рис. 9.26. Предварительная таблица с числовым примером

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

В ячейке L5 должна располагаться формула массива:

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

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

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

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

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

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

Рис. 9.27. Таблица предварительного анализа с формулами