Уроки Microsoft Excel

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

Таблица расходов членов семьи по статьям

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

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

2. Область расчета расходов на каждого члена семьи (А4:В9):
      - диапазон А6:А9 - список членов семьи и пункт общих затрат;
      - диапазон В6:В9 - формулы расчета расхода на каждого члена семьи и общих затрат;
      - ячейка В4 - итоговая сумма расходов.

3. Область расчета расходов по статьям (А11:В16):
      - диапазон А12:А16 - перечень статей расходов;
      - диапазон В12:В16 - формулы расчета по каждой статье;
      - ячейка В11 - итоговая сумма расходов.

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

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

Ячейкам с датами, задающими период вычислений, присвоим имена. Например, для ячейки В1 задайте имя Периоде, а для ячейки В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