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

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

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

285

Если результат сравнения положительный, к номеру года в ячейке К5 прибавля-

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

В ячейке

должна располагаться формула массива:

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

Она сравнивает номер года, сформированный в столбце К, с номером года в за-

писях журнала регистрации. Если они совпадают, формула определяет общий

доход всех членов семьи за указанный год.

В ячейке М5 также содержится формула массива. Она сравнивает номер месяца,

сформированный в столбце J, с номером

месяца

в записях журнала регистрации.

Если они совпадают, формула вычисляет доход, полученный всеми членами се-

мьи за этот месяц:

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

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

дохода члена семьи, имя которого введено в ячейку

К1,

за весь период ведения

учета:

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

'••..'.I

.11,",",

',-0*1-1

-Т

•1

\ В И

•

Кто

Пала

Месяц

-М2

=ЕСЛИ(.15-12,

1.J5+1)

-ЕСЛдаб-12;

ИвЩ^ЕШШ!

Год

-Ml

-ЕСЛИуб-1;

К5+ЦК5)

-ЕСЛИ(Л-1;

—Г Г

ПЫ/'

9—1

, ;

,.,

Ч-..ЦИ''

Гсд

12000

Месяц : 2

За год

(-СУММ

(ЕСЛИ(ГОД(Дата)-К5,

ДоходО))}

(-СУММ

(ЕСЛИ(ГОД(Дата)=Кб,

ДоходО))}

=СУММ(ЕСЛИ(ГОД<:Д

.VV'v'l'.jVff

rlH/rnWTT

\т

За месяц

{-СУММ

(ЕСЛИ(МЕСЯЦ(ДатаН

;

Доход;0))}

{-СУММ

(ЕСЛИ(МЕСЯЦ(Дата)=1б;

Доход;0))}

Кто

{-СУММ

(ЕСЛИ(Кто-Ш1-,

Доход,

0))}

{-СУММ

(ЕСЛН(Кто-в31;

Доход,0))}

-СУММ(ЕСЛИ(МЕСЯЦСД[=СУММСЕСЛИГКт(

-----

,п-,

лг.

-virr

пы-ч^

.ic-ran.

тт

-Г",.",

'ТГ.Л'ТГГ-ПМГЬ-,.,

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

Доход члена семьи за конкретный период

времени

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

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

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

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

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

К1.

При изменении любого из трех заданных параметров таблица обновляется.

286

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

Для того чтобы проследить, как

изменяется

ежемесячный

средний доход за пе-

риод учета, в освободившуюся ячейку М5 введите формулу

-ОКРУГЛ(СУММ(LS

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

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

СУММ(Ь$5:Ь5)

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

СЧЕТЕСЛИ(К55:К5;

">0"

)

и округляет результат до копеек.

В завершение измените заголовки в

таблице,

приведенной на рис. 9.26. Вместо

заголовков в ячейках L4 и М4 введите новые — Доход и Средний соответственно

(рис. 9.28).

Не прибегая к мастеру диаграмм, выделите диапазон ячеек L4:L28, нажмите кла-

вишу

[F11],

и Excel построит на отдельном листе Диаграмма 1 гистограмму полу-

чаемых доходов (рис. 9.29).

£3

Microsoft

Excel

-04.

Учет floxd&oeids

-i

-•</

•

•'-]

Флй.п

QpaCKii

Ьпя

Bc'ioira

ttcp;.i,jT

Lijp&HC

Данные

<>HU

Спроса

.-.JSJ..

;

is.

':i

.76

т.

1..

Ктв

Отец

Месяц

Год

2001

2002

Год

2001

Месяц 1

Доход

1 332,00

600,00

4936.00

936,00

3 060,00

3 504,00

2 268,00

1 380,00

3 324,00

1 380,00

4444,00

936,00

Средшш

1 332,00

966,00

2 289,33

951,00

2 172,80

2 394,67

2 376,57

2 079,50

2 001,78

1 801,60

1 940,00

1 893,33

2 089,54

2007,14

1 873,33

756,25

1 652,94

1 561,11

478,95

1 405,00

1 338,10

1 277,27

1 221,74

1 170,83

Ш1

;

Н]\ЖурналРегистра1(ии/'диагра]

||;

**•:

^-^.

••..'.•

Рис. 9.28. Данные о ежемесячных доходах члена семьи

и расчет среднемесячных доходов за период

Учет при мелкотоварном производстве

287

1Ш

Microsoft

Excel

-04.

Учет

jSQ

<£айп

.Оэёжа

Вид

Сорвис.

Диаграмм

Окно

Справка

Доход

1 1 3

7 8 9 10 И И 13 1+

1»

17 13 1»

'2:

(.Диаграмма!

Рис. 9.29. Гистограмма доходов члена семьи

Учет при мелкотоварном производстве

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

доходов и расходов. Продемонстрируем это на следующем примере. Предполо-

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

ных изделий под заказ, решил вести расчеты с помощью электронных таблиц,

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

Предприниматель изготавливает лишь такую продукцию, как

столы

и стулья (два

наименования). Эти изделия условно делятся на два вида: большие и маленькие.

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

В процессе изготовления применяются следующие материалы и инструменты:

лак, клей, шурупы.

От журнала регистрации к журналу операций

Для ведения учета в нужном объеме в журнал регистрации следует внести неко-

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

Журнал

Опе-

раций. Для отражения производимых операций измените названия столбцов: D —

Наименование, Е — Размер, F — Из чего.

Удалите имена, которые были ранее присвоены столбцам журнала. Для этого нужно,

выполнив команду

Вставка/Имя/Присвоить,

вызвать диалоговое окно Присвое-

ние имени (см. главу 7, рис. 7.2), выделить старое имя в списке Имя и нажать

кнопку Удалить.

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

присвойте ячейкам новые имена, нажав комбинацию клавиш

[Ctrl+Shift+F3].

288

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

быту и бизнесе

Miciosofl

Excel

Мелкий

бизнес

02.00

1 ;

030200

04.02.00

:'.

05.02.00

'$-'•

05.02.00

06.02.00

0702.00

В 0802.00

ю*:

090200

1.1

100200

11.0200

12.02.00

14:

13.0200

УШ

Н.0200

15.0200

1602.00

№',

17.0200

19:

180200

20]

19.0200

•21:

19.02.00

;Й||

20 02 00

;№i

21.02.00

22.0200

-70,00

-100,00

-41.00

-45,00

-160,00

-200,00

-120,00

-150,00

-8,00

-10,00

-5,00

-5.00

-21,00

-18,00

-15.00

-15,00

-5,00

Расход

400,00

700,00

100,00

50,00

200.00

Наименование

Сосна

Дуб

Шурупы

Клей

Лак

Стулья

Стелы

Стопы

Стулья

Стопы

Столы

Стопы

Стулья

Размер

Большие

Маленькие

Большие

Маленькие

Большие

Маленькие

аленькие

Большие

Маленькие

Большие

Из чего

Сосновые

Дубовые

Сосновые

Дубовые

Сосновые

Дубое

Сосно е

Дубов

Сосно е

Дубов

Сосно е

Дубов

Сосно е

Дубов

Сосно е

Дубов

Сосновые

Дубовые

Что

именно

Сосна

Дуб

Сосна

Дуб

Сосна

Дуб

Сосна

Дуб

LUvpvrai

Шурупы

Шуоупы

Шурупы

Клей

Рис. 9.30. Фрагмент рабочего листа

ЖурналОпераций

Заполнение журнала операций

Информацию в журнал операций рекомендуем заносить следующим образом.

1. Данные о покупке материалов (денежные затраты):

столбец Наименование — наименование материала;

столбец Расход — сумма, уплаченная за материал.

2. Сведения об использовании материалов:

П столбец Что именно — наименование материала;

П столбцы Наименование, Размер и Из чего — сведения об изделии, на которое

расходуется материал;

П столбец

Доход

— стоимость использованного материала с отрицательным

знаком.

3. Операции по продаже готовых изделий:

П столбцы Наименование, Размер и Из чего — сведения об изделии;

П столбец Доход — сумму, полученную от продажи изделия.

Итак, журнал операций ведется, теперь настало время оценить результаты дея-

тельности предпринимателя.

Таблица

покупка и расход материалов

На рис.

9.31

показан лист с таблицами и диаграммой, с помощью которых можно

проследить за операциями по приобретению и расходу материалов. Здесь же рас-

считываются и остатки материала. Лист разделен на пять областей.

Учет при мелкотоварном производстве

289

Область определения временного интервала, за который производится расчет:

ячейка

В1

— с какого числа;

ячейка В2 — по какое число.

Область расчета суммы расходов на приобретение материалов (область

А4:В10):

П диапазон А6:А10 — название материала;

П диапазон

В6:В10

— расчет сумм, потраченных на приобретение каждого

материала;

П ячейка В4 — сумма, уплаченная за все материалы.

Область расчета суммы израсходованных материалов (область

А12:В18):

П диапазон

А14:А18

— название материала;

П диапазон В14:В18 — расчета стоимости израсходовано материала;

П ячейка В12 — стоимость всех израсходованных материалов.

Область расчета остатков материалов (область А20:В26):

П диапазон А22:А26 — название материала;

П диапазон В22:В26 — расчет стоимости остатков материала;

П ячейка В20 — полная стоимость остатков материалов.

Объемная разрезанная круговая диаграммы, демонстрирующая расход мате-

риала на все изделия.

т*

!''

•

т 1

• .

i...

01.02.00:

Анали!

29.0200;

Расходы

материалов на все изделия

Рис.

9.31.

Лист

Расходы

290

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

В ячейке В6 должна содержаться формула, предназначенная для вычисления сум-

мы, израсходованной за определенный период времени на приобретение мате-

риала, указанного в ячейке

Аб:

{

-СУММ

(ЕСЛИ

(

Дата>=ПериодС

; ЕСЛИ (

Дата<=ПериодПо

; ЕСЛИ

(Наименование=А6

;

Рас-

ход;

)

;0)

) )

В ячейке

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

А6 материала, израсходованного в течение определенного времени:

(=-СУММ

(ЕСЛИ

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

ЕСЛИ

(Дата<=ПериодПо;

ЕСЛИ

(Что_именно=А1

До-

ход;

)

;0)

) )

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

израсходованным материалом за указанный период времени:

Таблица 2: расход материалов на изделия

В таблице на листе

Расходы!

(рис. 9.32) отражается расход материалов на изготов-

ление изделий всех наименований. Из нее можно почерпнуть информацию и о стои-

мости материалов, израсходованных на изготовление определенных изделий.

ш'Жм

Шурупы

сета

Дубовые

Маленькие

Болыпие

150.00

15,00

7,50

202.50

200,00

10,00

40,00

Стулья

Маленькие ) Большие

Маленькие

45,00

5,00

2,50

10,00

62,50

100.00

5,00

20,00

135.00

6,00

17,00

158,00

21,00

10,00

40,00

231,00

Стулья

Маленькие Большие

40.00

5.00

10.00

57,50

Расходы

материалов

на

изделия

Маленькие

Большие

Маленькие

Большие Маленькие

Большие

Стулья

Стопы

Стулья

Сосковью

Рис. 9.32. Лист

Расходы2

Ячейки В4:Е4 и F4:I4 объединены и имеют адреса В4 и F4 соответственно. Ячейки

В5:С5, D5:E5, F5:G5 и

Н5:15

также объединены и имеют адреса В5, D5, F5 и

Н5

соответственно. Формула в ячейке В7 должна выглядеть следующим образом:

(=-СУММ(ЕСЛИ(Дата>=ПериодС;ЕСЛИ(Дата<=ПериодПо;ЕСЛИ(Из_чего=$В$4;ЕСЛИ(Найме-

нов^ние=$В$5,•

ЕСЛИ(Раэмер=В$6;ЕСЛИ(Что

именно-$А7;Доход;0);0)

;0)

;

0) ) }

Учет при мелкотоварном производстве

291

Она определяет, сколько материала, указанного в ячейке А7, потрачено на изго-

товление маленьких дубовых столов за указанный период времени. В результате

копирования ячейки

В7

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

С7

изме-

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

После вставки этой же формулы в ячейку D7 необходимо скорректировать адрес

ячейки, указывающей наименование изделия. Измените адрес на $D$5. Формула

в ячейке D7 будет иметь вид:

{=-СУММ(ЕСЛИ(Дата>=ПериодС;ЕСЛИ(Дата<=ПериодПо;ЕСЛИ(Из_чего=$В$4;ЕСЛИ(Наиме-

нование=$0$5;ЕСЛИ(Размер=0$б;ЕСЛИ(Что_именно=$А7;Доход;0)

;0);0);0)

;0);0))

}

При копировании формулы в ячейку F7 скорректируйте адреса ячеек, указываю-

щих наименование изделия и материал, из которого они изготовлены.

Измените

адреса на

SFS5

и SFS4 соответственно. Формула в ячейке F7 будет

выглядеть

сле-

дующим образом:

{=-СУММ(ЕСЛИ(Дата>=ПериодС;ЕСЛИ(Дата<=ПериодПо;ЕСЛИ(Из_чего-$В$4;ЕСЛИ(Наиые-

нование=$0$5;ЕСЛИ(Размер=Е$6;ЕСЛИ(Что_именно=$А7;Доход;0);0);0);0);0);0))}

Представленная здесь же гистограмма отображает расход материала в денежном

выражении по каждому виду изделий.

Таблица 3: распределение выручки по изделиям

Таблица на листе Выручка отражает, какая сумма выручки припадает на каждый

вид продукции. В ячейке В6 должна быть такая формула:

(=СУММ(ЕСЛИ(Дата>=ПериодС;ЕСЛИ(Дата<=ПериодПо;ЕСЛИ(Наименование=В$5;

ЕСЛИ(Размер=$А6;ЕСЛИ(Что_именно=0;ЕСЛИ(Из_чего-$В$4;Доход;0);0);0);0);0);0))}

Формулы в столбцах С, D и Е строятся на ее основе по тем же принципам, что

и в предыдущем примере. В столбце F вычисляются итоговые значения.

Рис. 9.33. Лист Выручка

292

Глава 9. Учет доходов и

расходов

в быту и

бизнесу

Таблица 4: распределение прибыли по изделиям

Таблица на листе Прибыль отражает, какая прибыль получена от реализации из-

делия каждого вида. Формула в ячейке В6 имеет вид:

{=СУММ(ЕСЛИ(Дата>=ПериодС;ЕСЛИ(Дата<ПериодПо;ЕСЛИ(Наименование=В$5;:ЕСЛИ(Раз-

мер=$А6;ЕСЛИ(Что_именно=0;ЕСЛИ(Из_чего=$В$4;Доход;0);0);0);0);0);0))+СУММ

(ЕСЛИ(Дата>=ПериодС;ЕСЛИ(Дата<ПериодПо;ЕСЛИ(Наименование=В$5;ЕСЛИ(Размер=$Аб;

ЕСЛИ

(Что_именно>0;

ЕСЛИ

(Из_чего=$В$4;

Доход;

,-0)

;

;0)

;

) }

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

мируются в зависимости от выполнения следующих условий:

• значения в столбце

Что_именно

равны нулю;

• значения в столбце

Что_именно

больше нуля.

Обратитесь к журналу операций, и вы увидите, что записи, удовлетворяющие

первому условию, отражают реализацию товара и содержат в столбце Доход по-

ложительные значения. А вот записи, которые отвечают второму условию, отно-

сятся к операциям по расходу материалов и поэтому соответствующие суммы в

столбце Доход занесены со знаком «минус».

Рис. 9.34. Лист Прибыль

Резюме

Разработанные в этой главе таблицы станут хорошим подспорьем в деле плани-

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

связанных с ведением мелкого бизнеса или производства. После несложной пе-

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

варных потоков при осуществлении небольших по объему торговых операций

Резюме 293

(в небольших магазинах или ларьках). Чтобы вести учет отдельно по товарам и

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

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

кам, укажите названия таковых в столбце А.

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

таблицы для своих нужд. Формулы для анализа можно составить свои, а можно

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

При работе с таблицами анализа желательно, чтобы журнал регистрации нахо-

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

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

Можно воспользоваться и

ручным

режимом вычислений, для чего следует вы-

полнить команду Сервис/Параметры и на вкладке Вычисления диалогового окна

Параметры (см. главу 2, рис. 2.27)

активизировать

переключатель Вручную. В та-

ком случае перерасчет формул будет производиться после нажатия клавиши [F9].

НА ДИСКЕТЕ

В папке 09-Учет

доходов

и расходов в быту и бизнесе содержатся пять файлов с

разработанными в данной главе таблицами.

•

Глава 10

Функции рабочего листа

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

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

них доступны сразу после инсталляции Excel, остальные — только после загрузки

соответствующей надстройки. В Excel имеется программа Мастер функций, при-

менение которой позволяет значительно упростить процедуру задания функций.

Функции рабочего листа можно применять как непосредственно в ячейках ра-

бочего листа, так и в макросах. В качестве аргументов функций могут использо-

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

функции (такие функции называются вложенными).

Данная глава содержит краткий обзор функций рабочего листа (за исключением

инженерных). Функции распределены по категориям, и дано краткое описание

каждой категории. Более полную информацию обо всех функциях, включая не

вошедшие в данное приложение, пользователь всегда сможет найти в справочной

подсистеме: для этого достаточно выделить имя функции и нажать кнопку Справ-

ка в диалоговом окне Мастер функций.

В описании функций приведен синтаксис каждой функции и перечислены ее

аргументы.

ПРИМЕЧАНИЕ"

В данной главе троеточие (...) в конце списка аргументов означает, что предшест-

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

что функция может содержать не более 30 аргументов. Кавычки внутри текстового

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

Примените кавычки

("""")

порождает следующий текст: Примените кавычки

("").

Финансовые функции

Финансовые функции применяются при планировании и анализе финансово-

хозяйственной деятельности предприятия, а также при решении задач, связанных

с инвестированием средств. Большинство финансовых функций доступно только

после загрузки соответствующих надстроек. Среди финансовых функций можно

выделить три больших блока функций, которые используются для расчета амор-

тизационных отчислений, рентных платежей и доходов от ценных бумаг. Учиты-

вая огромное практическое значение этих функций, в данной книге мы решили