Пикуза В., Гаращенко А. Экономические и финансовые расчеты в Excel

Подождите немного. Документ загружается.

Применение

модуля

для анализа данных

275

^-;-<

.И?!

*Н

Times New

Roman

"13 ..... ......

МПериод

01.02.2002

В том

числе

Отец

Мать

Ребенок

том числе

;

540,оо

; 856.00:

[

....

636

-°°

448s'oo;

Анализ

•

IOIXQO%;

34,31%!

19.07%;

14И7%;

ИОплата

жилья

^р

Машина

J4-1

Одежда

4jl

Обувь

Питание

528.00:

948]obl

1420^00:

' '?52.рО:

640.00

;

100,00%:

11,76%!

21.12%[

зТб4%!

'21,2

l%i

14.26%:

•

ГО!Г'ВО

Расходы

по

членам

семьи

Расходы

но

статьям

•

Рис. 9.15. Лист с таблицей и диаграммами, отражающими расходы

на каждого члена семьи

Скопируйте ее из строки формул и

произведите

вставку в ячейку Вб листа Рас-

ходы}.

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

Выделив адрес ячейки А2, отметьте мышью на листе Расходы 1 ячейку

В1.

При

этом вместо имеющейся в формуле ссылки появится имя Периоде.

2. Выделив адрес ячейки A3, отметьте мышью на листе

Расходы!

ячейку В2. По-

сле этого содержащаяся в формуле ссылка будет указывать на имя

ПериодПо.

3. Выделив адрес ячейки А4, поместите указатель мыши в ячейку А6 на листе

Расходы

4. Нажав комбинацию клавиш

[Ctrl+Shift+Enter],

введите признак формулы

массива.

Скопируйте полученную формулу в ячейки В7:В9. Формула в ячейке

В12

созда-

ется аналогичным образом. Только в этом случае выполняется вложение формул

из ячеек В2, ВЗ и В5 модуля (рис.

9.13):

{

=СУММ(ЕСЛИ(Дата>-

ПериодС;

ЕСЛИ

(Дата<=-

ПериодПо; ЕСЛИ

(Откуда_Куда-А12,•

Расход;0);0);0))}

С помощью этой формулы нетрудно определить, какая сумма за определенный

период времени была потрачена на каждого члена семьи и на какие цели кон-

кретно.

276

Глава 9. Учет доходов и расходов в быту и бизнесе

Соотношение расходов по отдельным статьям можно установить путем деления

суммы расхода по каждой статье на общую сумму. Следовательно, в ячейке D6

должна быть такая формула:

=B6/$BS4

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

Результаты анализа мы представим в виде диаграмм. Применение различных гра-

фиков и диаграмм позволяет наглядно продемонстрировать происходящие про-

цессы и тенденции.

Для построения диаграмм в Excel существует мастер диаграмм, вызываемый ко-

мандой Вставка/Диаграмма либо нажатием кнопки Мастер диаграмм (рис. 9.16),

расположенной на стандартной панели инструментов. Мастер диаграмм в про-

цессе работы последовательно выводит на экран несколько диалоговых окон.

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

Для создания диаграммы выделите на рабочем листе

Расходы!

диапазон ячеек

А6:В9, на основании данных которых будет построена верхняя диаграмма. Же-

лательно, чтобы в выделенный диапазон входили ячейки с названиями столбцов,

которые используются в легенде диаграммы. После вызова мастера диаграмм

появляется его первое диалоговое окно (рис.

9.17),

которое содержит две вкладки:

Стандартные и Нестандартные. С помощью этого окна определяется тип и вид

будущей диаграммы.

Точечная

С областями

Кольцевая

Лепестковая

Поверхность

Пузырьковая

j£JL

Биржевая

<'<hf-

!мь,:и

еармам

|д«агр*и*|

иШ11

•Дал«е>

;

Готово

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

Применение модуля

для

анализа данных

277

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

• список Тип — для выбора типа диаграммы;

• поле Вид — для выбора формы представления диаграммы.

Для представления структуры расходов подходит круговая объемная диаграмма.

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

образец.

Определив тип и формат диаграммы, можно нажать кнопку Просмотр результата

в диалоговом окне и просмотреть в поле Образец

будущее

изображение созда-

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

кнопки Готово завершит построение диаграммы. Нажатие кнопки Далее приво-

дит к появлению второго диалогового окна мастера диаграмм (рис.

9.18).

Второе диалоговое окно состоит из двух вкладок: Диапазон данных и Ряд, пред-

назначенных для подтверждения (или изменения) области расположения число-

вых данных, на основании которых будет построена диаграмма.

В поле Диапазон вкладки Диапазон данных указан адрес диапазона выделенных

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

Мастер

диаграмм

(шаг Z из 4):

источник

Диапамн

длины/

j РЯД

ш.

Ряды в;

Далее.

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

вкладка Диапазон данных

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

изменить ориентацию данных диаграммы. При построении нашей диаграммы

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

278

Глава 9. Учет доходов и расходов в быту и бизнесе

При нажатии кнопки Далее программа

переходит

к третьему диалоговому окну,

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

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

На вкладке Заголовки (рис. 9.19) вносятся название диаграммы и названия осей

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

Мастер диаграмм

(шаг

3 из

4):

параметры,

Заголовки

пег

вида

] Подписи

данных

ШШРасходы

по членам семьи

• I |

'..У!/-;.;;

•-

Рис. 9.19. Диалоговое окно Мастер диаграмм

(шагЗ

из 4): параметры диаграммы,

вкладка Заголовки

На вкладке Легенда (рис. 9.20) при выборе опции Добавить легенду в диаграмму

добавляется блок с описанием обозначений. Отметьте в области Размещение один

из переключателей, определяющих местоположение

легенды.

В нашем случае ле-

генда размещена справа.

Мастер диаграмм (шаг 3

4):

парапет

ек.и

o«w..

Подписи

данных

Добавить

яд"енду

Расходы по

членам

семьи

<""

правом

ьдоенем

утпу

geepxy

(

ГПРВЛ

Огнена

Дапее

)

Г-этовс

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

вкладка Легенда

Применение модуля для анализа данных

279

При нажатии на кнопку Далее программа переходит к последнему, четвертому

диалоговому окну (рис.

9.21),

где нужно указать место построения диаграммы:

• на отдельном листе;

на листе

Расходы!,

где расположена исходная таблица.

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

появится на листе. Теперь переместите ее в верхнюю его часть.

ПРИМЕЧАНИЕ

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

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

(Мастер

диаграмм

(шаг

4 из

4):

Поместить

диаграмму

на

листе:

"^Ждельним;

Диаграмма!

Рис.

9.21.

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

Изменять внешний вид диаграммы можно и после се создания. Если лист Диа-

грамма I

открыт

или диаграмма на листе

сданными

выделена, можно любым спо-

собом вызвать мастер диаграмм (например, нажатием соответствующей кнопки

стандартной панели инструментов) и внести изменения.

Два раза щелкнув на оси диаграммы, можно вызвать диалоговое окно Формат оси,

которое имеет пять вкладок. Это окно можно открыть и по-другому:

• поместить указатель в область оси, щелкнуть правой кнопкой мыши и выбрать

в контекстном меню команду Формат оси;

• нажать комбинацию клавиш

[Ctrl+1];

• вызвать команду Формат/Выделенная ось.

Выбрав вкладку Шкала этого окна, вы

можете

установить нужные параметры.

Два раза щелкнув на названии диаграммы, названии оси диаграммы или области

расположения легенды, можно вызвать соответственно диалоговые окна Формат

названия диаграммы, Формат названия оси или Формат легенды, каждое из которых

имеет три вкладки. С их помощью можно изменить вид надписей. Эти диалого-

вые окна вызываются также, если после активизации области названия диаграм-

мы, названия оси диаграммы или области расположения легенды воспользовать-

ся следующими способами:

• щелкнуть правой кнопкой мыши и выбрать команду в контекстном меню;

• нажать комбинацию клавиш

[Ctr!+l];

ш вызвать соответствующую команду меню Формат.

280

Глава 9. Учет доходов и расходов в быту и бизнесе

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

по статьям

На рис. 9.22 приведена таблица с информацией о суммах, израсходованных на

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

(Расходы2).

oft

Ексе!

03.

Домдюнт

бюдж

1 -

.,-'1

:-.-

••-•;•

tp'fuv

,*,

i>.

;й«'

„,.

•&

,•„

Times New Roman

:Расходы

С'ПЛ.-.ТЛ

ЖИШ.Ч

| .:цвчу\а"

,:: Обувь

i't«

0^4.......

Мать

К-:.:

'^n-.'

._и.!»ге.

40.00

936,00

444,00

120,00

1 540.00

100.00

88,00

508.00

160,00

856,00

396.00

528,00

808.00

240,00 120,00

636.00 I

456.00

948,00

420.00

952.00

640.00

4 488,00

По статьям на каждого члена семьи

i'l..

Рис. 9.22. Структура расходов на каждого члена семьи по статьям

Формула в ячейке В5 производит вычисление суммы, потраченной за указанный

период времени на каждого члена семьи по конкретной статье расходов:

(=СУММ(ЕСЛИ(Дата>=ПериодС;ЕСЛИ(Дата<ПериодПо;ЕСЛИ(Кто=В$4;

ЕСЛИ(Откуда_Куда=$А5;Расход;0);0);0)

;

0))

}

В этой формуле, в отличие от предыдущей, кроме временного

периода

имеются

еще два критерия отбора:

• на кого потрачено — диапазон с именем

Кто',

• по какой статье расходов потрачено — диапазон с именем

Откуда_Куда.

Формула создается путем вложения четырех формул из ячеек В2:В5 модуля

(рис.

9.13).

В ней содержится абсолютная ссылка на столбец А — для поиска ин-

формации на листе

ЖурналРегистрации

в именованном диапазоне

Откуда_Куда

и абсолютная ссылка на строку 4

-°-

для поиска информации в диапазоне Кто.

Ссылки введены для удобства при копировании формулы, созданной в ячейке

В5. Копирование формулы в таблицу необходимо выполнять в последовательно-

сти, описанной ниже.

Применение модуля для анализа данных

281

Скопируйте содержимое ячейки, где находится формула массива, в диапазон

ячеек В6:В9.

2. Скопировав в буфер обмена диапазон ячеек В5:В9 и выделив диапазон С5:Е5,

произведите вставку из буфера обмена.

Таблица 3: расходы по статьям с детализацией

Таблица для подробного анализа статей расходов показана на рис. 9.23. Формула

в ячейке В5 создается и копируется во все расчетные ячейки таблицы аналогично

формуле из таблицы 2:

(=СУММ(ЕСЛИ

(Дата>=ПериодС;ЕСЛИ

(Дата<ПериодПо;ЕСЛИ

(Откуда__Куда=В$4;

ЕСЛИ(На

что=$А5;Расход;0);0);0);0))}

i©

Microsoft

Excel

-03.

Домашний

6юджс1.и14

ШЙРЖ!

Tunes

New

Roman

:",

И™

Период

3 : Расходы

r.is

•-'

Сает

-. ;;, fl4,1."Tlt

••

r^'it-.h';;

Uripa-.jibi

' ь<шчк

Bof-хняя

Кмл.-няя

ЛЧ1НЯЯ

Зямняя

Длмиоеюнная

Всего

^Й<^.'-'-

01.02.2002:29.02.2002

огшагажчлй

132,00

176,00

220,00

528,00

Машин»:

^о|е*Ш

боо.оо

60,00

68,00

220,00

948,00

!..У..!

936,00

88,00

396,00

1 420,00

1Й1?*ШМ

Обувь

644,00

308,00

952,00

-Й

"•:

питание

;

120.00

240,00

280,00

640,00

ИКХОДЬН

ДО,

ЯД!

| < |

ИТОГО

132.00

176.00

220.00

600,00

60,00

68,00

220,00

936,00

88,00

644.00

308,00

396.00

120,00

240.00

280,00

4488,00

I-kl

,:::::

—-••"' :

Рис. 9.23. Рабочий лист

РасходыЗс

числовым примером

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

с детализацией

Таблица для анализа статей расходов на каждого члена семьи

(Расходы4)

показана

на рис. 9.24. Формула в ячейке В5 имеет вид:

{=СУММ(ЕСЛИ(Дата>=ПериодС;ЕСЛИ(Дата<ПериодПо;ЕСЛИ(Кто=В$

ЕСЛИ(На

что=$А5,-Расход;0);0);0);0))}

282

Глава 9. Учет доходов и расходов в быту и бизнесе

|йп

встала

:ФФри-5

•

i№

*i8

-i

Период

01.02.2002

29.02.2002

Расходы

Газ

Счет

Водл

ЗЗГЧГ1СП1

стоянка

Штрафы

ЬчНДИК

Верхняя

Нижняя

Летняя

Зимняя

Демисезонная

МЯСО 1

Мучное

<1>рукты

ВСвго

Отвц_

40,00

936,00

444,00

120,00

1 540,00

M.1TI»

100,00

88,00

200,00

308.00

160,00

856,00

Р<.'Г'1,№К

356.00

240,00

636,00

Обще*

132.00

176,00

220,00

600.00

60,00

68,00

80,00

120,00

1 456,00

Итого

132,00

176,00

220,00

600,00

60,00

68,00

220,00

936,00

88,00

644,00

308,00

396,00

120,00

240.00

280,00

4488,00

Рис. 9.24. Рабочий лист

Расходы4с

числовым примером

Таблица 5: доходы

Если вместо диапазона Расход в формулах указать диапазон Доход, можно опре-

делить структуру полученных семьей доходов (рис. 9.25). После замены диапазона

формула в ячейке В6 будет выглядеть следующим образом:

{=СУММ(ЕСЛИ(Дата>=ПериодС;ЕСЛИ(Дата<=ПериодПо;ЕСЛИ(Кто=Аб;Доход;0);0)

;0)))

Здесь вычисляется сумма, полученная конкретным членом семьи за указанный

период времени.

В ячейке

В10

находится формула, определяющая, из какого источника получены

доходы за указанный период:

(^СУММ(ЕСЛИ(Дата>=ПериодС;ЕСЛИ(Дата<=ПериодПо;

ЕСЛИ(Откуда_Куда=А10;Доход;0);0);0)))

Ячейка

В16

содержит формулу, которая определяет, в каких учебных заведениях

и какой гонорар получил отец:

(=СУММ(ЕСЛИ(Дата>='ПериодС;ЕСЛИ(Дата<=ПериодПо;ЕСЛИ(На_что=А16;

Доход;0);0);0))}

А в ячейке В20 содержится формула

(-СУММ(ЕСЛИ(Дата>=ПериодС;ЕСЛИ(Дата<=ПериодПо;

ЕСЛИ(Откуда_Куда=$А$13;

ЕСЛИ(Что

именно=А20;Доход;0);0);0);0))}

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

283

где вычисляется сумма гонорара, выплаченного отцу за период времени, задан-

ный в ячейках

ПериодС

ПериодПо.

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

диапазона с именем

Откуда_Куда

с ячейкой

А13,

которая содержит текст «Гонорар».

ЛИ

Microsoft

Excel •

Домашний

бюджет

• Бета D Сигма D Гонорар

!|Период

Институт

Университет

•

I'ifSjГонорары

за период

'•

(нварь

2000

01.02.00;

29.02ЛО:'

1 522,00!

777,00

1 522,00;

АНал

Доходы всего:

В том числе

Отец

Мать |

В том числе

Альфа

Бета

Сигма

Гонорар

1 /2J.LIU:

2 022,00! \

700,00!

2722,00!

500,00:

ЩооГТ"

400,00:

1 522,00!

1UU.UU%

71,28%!

25,72%;

100,00%:

18,37%:

11,02%:

14,70%

55,91%:

Рис.

9.25.

Рабочий лист Доходы с числовым примером

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

периоды времени

Если учет доходов и расходов ведется в семье в течение довольно продолжитель-

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

имели члены семьи в разное время.

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

лицу учета ежемесячных доходов каждого члена семьи.

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

Решение задачи начнем с построения промежуточной таблицы с простыми фор-

мулами (рис. 9.26), которые затем будут объединены. Эта таблица выполняет сле-

дующие функции:

• по имени члена семьи, введенному в ячейку К1, определяет в столбце N его

доход за все время учета;

• по номеру года, введенному в ячейку

Ml,

автоматически формирует год в

столбце К, на основании которого в столбце L будет рассчитываться общий

доход семьи;

284

Глава 9. Учет доходов и расходов в быту и

бизнесе

• по номеру месяца, введенному в ячейку М2, формирует данные в столбце

номера месяцев в столбце должны оказывать влияние на столбец К при оп-

ределении номера года;

• по номеру месяца в столбце J производить вычисление доходов, полученных

всеми членами семьи за данный месяц.

Формула в ячейке J5 задает для таблицы номер месяца, с которого начинается

анализ:

=М2

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

2;1; J5

Если это номер

(последний месяц года), то ячейка будет содержать значение

1 (первый месяц года). Во всех других случаях к значению предыдущей ячейки

прибавляется число 1 (следующий месяц).

Формула в ячейке К5 задает номер года, с которого начинается анализ:

-Ml

Формула в ячейке

Кб

сравнивает номер месяца, указанный в столбце J, со зна-

чением 1 (первый месяц следующего года):

=ЕСЛИ

(

J6=l

;

К5+1

;

К5

)

/!i

.;',

:>i-

Дата

05.01.2000

0402.2000

05.03.2000

04.04.2000

04.05.2000

03.062000

03.07.2000

02.08.2000

01.09.2000

01.10.2000

31.10.2000

3011.2000

30.12.2000

29.01.2001

28.02.2001

30.03.2001

29.04.2001

29.05.2001

28.06.2001

2807.2001

27.08.2001

26.09.200!

26.102001

25112001

25.12.2001

2401.2002

23.02.2002

ЯШ

;

и,'1

|Щ

Доход

2 000,00

1 600,00

936,00

2 000,00

1 600,00

1 380,00

2 168,00

888,00

1 200,00

1 424,00

3 108,00

2 664,00

1 776,00

1 332,00

600,00

4936,00

936,00

3 060,00

3 504,00

2 268,00

948,00

1 380,00

632,00

3 324,00

1 380,00

4444,00

936,00

.алРегист,

Кто

Отец

Мать

Отец

Мать

Отец

Мать

Отец

Мать

Отец

Мать

Отец

«"wwJill

Кто

Месяц

.г,;/

Отец

Год

2001

2002

Год

Месяц

За год

27 300,00

5 380,00

3 380,00

5 380,00

380,00

5 380,00

3 380,00

2001

За месяд*

7 776,00

136,00

5 872,00

2 936,00

4660,00

4884,00

4 436,00

4836,00

2 580,00

5 164,00

988,00

156,00

7 776,00

136,00

5 872,00

2 936,00

4 660,00

4884,00

4436,00

4836,00

2 580,00

164,00

5 988,00

3156,00

Кто

44 352,00

44352,00

44 352,00

44352,00

'44352,00

44352,00

44"35'2,66

44 352,00

44 332,00

352,00

44352,00

;

;||

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