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

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

  1. Область задания временного интервала, в котором производится расчет (В1: В2):
    • ячейка В1- с какого числа;
    • ячейка В2 - по какое число.
  2. Область расчета расходов на каждого члена семьи (А4:В9):
    • диапазон А6:А9 - список членов семьи и пункт общих затрат;
    • диапазон В6:В9 - формулы расчета расхода на каждого члена семьи и общих затрат;
    • ячейка В4 - итоговая сумма расходов.
  3. Область расчета расходов по статьям (А11:В16):
    • диапазон А12:А16 - перечень статей расходов;
    • диапазон В12:В16 - формулы расчета по каждой статье;
    • ячейка ВП - итоговая сумма расходов.
  4. Область определения доли расходов по каждому члену семьи и конкретной статье в общей сумме расходов (область D1:D16).
  5. Диаграммы:
    • объемный вариант разрезанной круговой диаграммы относительных расходов на каждого члена семьи;
    • объемный вариант круговой диаграммы относительных расходов по отдельным статьям.

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

Построение диаграмм

Результаты анализа мы представим в виде диаграмм. Применение различных графиков и диаграмм позволяет наглядно продемонстрировать происходящие процессы и тенденции.

Для построения диаграмм в Excel существует мастер диаграмм, вызываемый командой Вставка/Диаграмма либо нажатием кнопки Мастер диаграмм (рис. 9.16), расположенной на стандартной панели инструментов. Мастер диаграмм в процессе работы последовательно выводит на экран несколько диалоговых окон.

Кнопка Мастер диаграмм.

Рис. 9.16. Кнопка Мастер диаграмм.

Для создания диаграммы выделите на рабочем листе Расходы! диапазон ячеек А6:В9, на основании данных которых будет построена верхняя диаграмма. Желательно, чтобы в выделенный диапазон входили ячейки с названиями столбцов, которые используются в легенде диаграммы. После вызова мастера диаграмм появляется его первое диалоговое окно (рис. 9.17), которое содержит две вкладки: Стандартные и Нестандартные. С помощью этого окна определяется тип и вид будущей диаграммы.

иалоговое окно Мастер диаграмм (шаг 1 из 4): тип диаграммы

Рис. 9.17. Диалоговое окно Мастер диаграмм (шаг 1 из 4): тип диаграммы.

На вкладке Стандартные находятся:

Для представления структуры расходов подходит круговая объемная диаграмма. Выберите в списке Тип элемент Круговая, а в поле Вид отметьте соответствующий образец.

Определив тип и формат диаграммы, можно нажать кнопку Просмотр результата в диалоговом окне и просмотреть в поле Образец будущее изображение создаваемой диаграммы. Если изображение нас устраивает, то последующее нажатие кнопки Готово завершит построение диаграммы. Нажатие кнопки Далее приводит к появлению второго диалогового окна мастера диаграмм (рис. 9.18).

Второе диалоговое окно состоит из двух вкладок: Диапазон данных и Ряд, предназначенных для подтверждения (или изменения) области расположения числовых данных, на основании которых будет построена диаграмма.

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

Диалоговое окно Мастер диаграмм (шаг 2 из 4): источник данных диаграммы, вкладка Диапазон данных

Рис. 9.18. Диалоговое окно Мастер диаграмм (шаг 2 из 4): источник данных диаграммы, вкладка Диапазон данных.

При активизации переключателей строках и столбцах в области Ряды в можно изменить ориентацию данных диаграммы. При построении нашей диаграммы программа определила, что должен быть активизирован переключатель столбцах.

При нажатии кнопки Далее программа переходит к третьему диалоговому окну, Мастер диаграмм (шаг 3 из 4): параметры диаграммы, которое состоит из нескольких вкладок и служит для задания параметров форматирования диаграммы.

На вкладке Заголовки (рис. 9.19) вносятся название диаграммы и названия осей в поля Название диаграммы, Ось X (категорий), Ось Y (значений).

Диалоговое окно Мастер диаграмм (шаг 3 из 4): параметры диаграммы, вкладка Заголовки.

Рис. 9.19. Диалоговое окно Мастер диаграмм (шаг 3 из 4): параметры диаграммы, вкладка Заголовки.

На вкладке Легенда (рис. 9.20) при выборе опции Добавить легенду в диаграмму добавляется блок с описанием обозначений. Отметьте в области Размещение один из переключателей, определяющих местоположение легенды. В нашем случае легенда размешена справа.

Диалоговое окно Мастер диаграмм (шаг 3 из 4): параметры диаграммы, вкладка Легенда.

Рис. 9.20. Диалоговое окно Мастер диаграмм (шаг 3 из 4): параметры диаграммы, вкладка Легенда.

При нажатии на кнопку Далее программа переходит к последнему, четвертому диалоговому окну (рис. 9.21), где нужно указать место построения диаграммы:

Активизируйте переключатель имеющемся, нажмите кнопку Готово и диаграмма появится на листе. Теперь переместите ее в верхнюю его часть.

ПРИМЕЧАНИЕ

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

Диалоговое окно Мастер диаграмм (шаг 4 из 4): размещение диаграммы.

Рис. 9.21. Диалоговое окно Мастер диаграмм (шаг 4 из 4): размещение диаграммы.

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

Два раза щелкнув на оси диаграммы, можно вызвать диалоговое окно Формат оси, которое имеет пять вкладок. Это окно можно открыть и по-другому:

Выбрав вкладку Шкала этого окна, вы можете установить нужные параметры.

Два раза щелкнув на названии диаграммы, названии оси диаграммы или области расположения легенды, можно вызвать соответственно диалоговые окна Формат названия диаграммы, Формат названия оси или Формат легенды, каждое из которых имеет три вкладки. С их помощью можно изменить вид надписей. Эти диалоговые окна вызываются также, если после активизации области названия диаграммы, названия оси диаграммы или области расположения легенды воспользоваться следующими способами: