Тема 8. Учет семейных доходов и расходов.

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

В качестве примера рассмотрим семью с одним ребенком.

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

Система учета семейных расходов будет состоять из:

таблицы с журналом регистрации, в которую вносятся данные о доходах и расходах, а также об их источниках

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

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

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

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

При заполнении таблицы удобно использовать такой прием, как выбор из списка. На ячейке, в которую нужно ввести текст, вызовите контекстное меню и выберите команду Выбрать из раскрывающегося списка, и на экране появится список со всеми элементами, которые были внесены в столбец ранее.

Присвоение имен ячейкам журнала регистрации

Для того чтобы программа поддерживала имена диапазонов, выполните Сервис^Параметры^-вкладка Вычисления^включите опцию Допускать названия диапазонов.

Выделите диапазон А1:0100.

Нажмите комбинацию Ctrl+Shift+F3 и вызовите диалоговое окно Создать имена.

Отметьте в нем опцию В строке выше и нажмите ОК. На рисунке 49 представлен лист Журнал регистрации.

Ш Microsoft Excel - Домашний бюджет щ файл Правка Вид Вставка формат Сервис Данные Окно Справка _ в ; J j'ldl J _j J A -з ./ ч д т - si | ш т я 1 : Times Ne Сохранить | , 10 - Ж К ч \т ш т : Ш\Щ % ООО 35 • А - J С2В - f* 1 А В с D Е F G н л 1 Дата Доход Расход Kltl Откуда/Куда На что Что нменно 2 01.02.2006 6 000.00 Отец Ань ф а Зарплата Январь 2006 3 02.02.2006 3 600,00 Мать Сигма Зарплата Январь 2006 4 03.02.2006 1 936,00 Отец Одежда Верхняя ПаПЬТО 5 04.02.2006 60,00 Ребенок Питание Фрукты Дыня 6 05.02.2006 1 600,00 Отец Гонорар Университет Январь 2006 7 06.02.2006 1 308,00 Мать Обувь Зимняя Туфли 8 07.02.2006 40,00 Ребенок Питание Фрукты Арбуз 9 08.02.2006 188,00 Мать Одежда Нижняя Рубашка 10 09.02.2006 4200,00 Мать Бета Зарплата Январь 2006 11 10.02.2006 900,00 Мать Обувь Л етняя Туфли 12 11 02 2006 3 108,00 Отец Гонорар Институт Январь 2006 13 12.02.2006 132,00 Общее Оплата жилья Газ Январь 2006 14 13.02.2006 600,00 Общее Оплата жилья Свет Январь 2006 15 14.02.2006 220,00 Общее Оплата жилья Вода Январь 2006 16 15.02.2006 600,00 Общее Машина Запчасти Колесо 17 16.02.2006 160,00 Общее Машина Стоянка За янв арь 18 17.02.2006 68,00 Общее Машина Штрафы Перекресток 19 18.02.2006 80,00 Общее Машина Бензин На дачу 20 19022006 400,00 Отец Машина Бензин На работу 21 20.02.2006 1 380,00 Отец Гонорар Университет Февраль 2006 22 21.02.2006 100,00 Мать Машина Бензин К подруге 23 22 022006 900,00 Общее Питание Мясо На неделю 24 23.02.2006 250,00 Мать Питание Обеды На неделю 25 24.02.2006 300,00 Отец Питание Обеды На неделю 26 25.02.2006 906,00 Ребенок Одежда Демисезонная Куртка ж 27 26.02.2006 1 444,00 Отец Обувь Летняя На работу 28 1 V H <4 > н \ Журнал регистрации / l< > 1 | Готово NUM Рисунок 49

Анализ данных с помощью сводной таблицы

Сводная таблица - это вспомогательная таблица, построенная с помощью мастера сводных таблиц и анализирующая данные исходной таблицы.

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

критерии, по которым производится анализ (столбцы Дата, Кто, Откуда/Куда, На что, Что именно);

значения (столбцы Доход и Расход).

Создание сводной таблицы

Выделите диапазон А1:027 с журналом регистрации и выполните Дан- ные^-Сводная таблица.

В окне Мастер сводных таблиц шаг 1 из 4 оставьте заданный по умолчанию переключатель в списке или базе данных Microsoft Excel, нажмите Далее.

В окне Мастер сводных таблиц шаг 2 из 4 необходимо указать диапазон ячеек сводной таблицы.

В окне Мастер сводных таблиц шаг 3 из 4 определяется структура создаваемой таблицы. В центре диалогового окна расположены области: Строка - используется в качестве заголовка строки в сводной таблице; Столбец - служит заголовком столбца в сводной таблице;

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

Данные - рассчитанные итоговые значения.

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

Определите по заголовку Откуда/Куда и статье Питание, сколько денег

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

на семью в целом. Для этого выполните следующие перемещения:

заголовок Откуда/Куда - в область Страница;

заголовок На что - в область Строка;

заголовок Кто - в область Столбец;

заголовок Расход - в область Данные.

При помещении кнопки с заголовком Расход в область Данные заголовок последней изменится на Кол-во значений по полю Расход. В результате окно будет выглядеть следующим образом: Мастер сводных таблиц и диаграмм - шкет CZ

Перетащите кнопки полей в нужные области диаграммы. Дата | |Что имен|

Доход

Откуда^ Откуда/1- Кто |Столбец На что Строка количество по полр Данные Справка Для того чтобы изменить вид или способ вычисления данных сводной таблицы, необходимо дважды щелкнуть мышью на каждом из размещенных в различных областях заголовках.

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

В результате двойного щелчка на заголовке Данные в списке Операция укажите Сумма. В поле Имя будет указано имя операции - Сумма по полю Расход.

Вычисление поля сводной таблицы

Имя: | Откуда/Куда Итоги ® ^автоматические! О другие

О нет Сумма а

Количество

Среднее

Максимум

Минимум

Произведение Скрыть элементы: шж Машина

Обувь

Одежда Дополнительно..

Макет...

СИ Отображать пустые элементы Нажмите Далее.

5.

? Microsoft Excel - Домаицщй бюджет - | шш I: SJ Файл Правка Вид Вставка Формат Сервис Данные Окно Справка Введите вопрос _ й1 X 1 и J Я Л j 1J Д1 ? а 1 А 4а ffl' J1 -0 - ' Ч -i Е - Si и 1Ш ^ ^ - в : Times New Roman - 10 . Ж К '1 = = = рр 1 ЕН =, """ "' '"J^ И • & ¦ А . 1 А1 -fx Откуда/Куда А в С D Е F G ¦ л 1 ОткудаЛСуда Гне сколько элементов"! (Ў 2 Ш1

j-- Машина ;-¦ Обувь !•¦¦• Одежда •¦¦• Оплата жилья : Питание

OK | [ Отмена 3 Сvmmа по полю Расход Кто [Ў 4 На что |т Мать Общее Отец Ребенок Общий итог 3 Мясо 500 900 6 Обеды 250 300 350 7 Фрукты 100 100 8 Общий итог 250 900 300 100 1330 9 10 Сводные таблицы - X 11 Сводная таблица - | ? Щ | J I 3 12 |vj 1

Гото > и >, Сводная таблица / Журнал регистрации / во |< > | 1 Рисунок 50

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

Создание собственных средств анализа данных

Определим сумму, потраченную за период с 5 по 15 февраля на покупку летней обуви для матери.

способ. Простое суммирование.

способ. На новом листе Анализ данных создается шапка таблица: ? Microsoft Excel - Долюшний бюджет щ файл Правка Вид Вставка 1 Формат Сервис Данные Окно 1: Справка - & х si iiia : 10 '|ж Г к^тВ % 1 ? ' • -Si. А- _в А1 Ў fx Условие 1 А В | С D Е F A 1 | Условие 1 1 Условие 2 Сумма за 2 Дата 1 Сумма Дата 2 Сумма П61ШОД 3 05.02.2006 15.02.2006 4 ¦ч V А > н / Журнал регистрации \ Дна лих данных/ < 1 > 1 1 Готово NUM в которую переносятся данные, относящиеся к интересующему нас периоду, для этого:

Определите записи, у которых дата больше или равна 05.02.06. Для этого в ячейку А4 занесите формулу:

=ЕСЛИ('журнал регистрации'! А2>='анализ'! $A$3;1;0)

Формула работает следующим образом: если условие соблюдается, то в ячейку заносится число 1, иначе 0.

В ячейку В4 поместите формулу для определения расходов с листа Журнал регистрации: =ЕСЛИ(A4=0;0;'журнал регистрации'Ю2)

В ячейку С4 занесите формулу, определяющую записи, у которых даты меньше 15.02.06: =ЕСЛИ('журнал регистрации'!A2<=анализ!$C$3;1;0)

В ячейку D4 поместите формулу, определяющую расходы с листа Журнал регистрации: =ЕСЛИ(C4=0;0;'журнал регистрации'Ю2)

В столбце Е проверьте, выполняются ли условия в столбцах А и С: =ЕСЛИ(Л4+С4=2;Б4;0)

В ячейке В3 и Е3 соответственно, происходит суммирование всех отобранных предыдущими формулами значений.

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

Ш Microsoft Excel - Домашний бюджет Введите вопрос

^ j Файл Правка Вид Вставка Формат Сервис Данные Окно Справка щ

> - А

| Times New Romao j^lO '||ж| К Ч | 1: s = S | ^ '/. ООО i°S | iW |

f. 05.Ш.20СБ А | В с D Е ПГ 1 Условие 1 Условие 2 Сумма за 2 Дата 1 Сумма Дата 2 Сумма период 3 05.022006 =СУММ(В4:В29) 15.02:2006 =CyMM(D4:D29) =СУММ(Е4:Е29) 4 =ЕСЛИ(^Журнал регистрации1! А2>$А$3; 1Д1 =ЕСЛИ(А4=0;0; Журнал регистрации!С2) =ЕСЛИ(?Журнал регистрации1! А2<=$С$3; 1;0) =ЕСЛИ(С4=0Д 'Журнал регистр ации'!С2) =ЕСЛИ(А4+С4=2;Б4;0) н > и / Журнал регистрации /Дна л их даннык/ > 1 Рисунок 51

Microsoft Excel - Долишний бюджет

Ы1п||х|

i Файл Правка Вид Вставка Формат Сервис Данные Окно

I 10 • | Ж К 1= = = Й] 1 % ? «I- д - Ё A3 fx 05.02.2006 А В С D Е F — 1 Условие 1 Условие 2 Сумма за 2 Дата 1 Сумма Дата 2 Сумма период 3 05.02.20061 8596 15.02.2006 5984 3988 4 0 0 1 0 0 5 0 0 1 0 0 6 0 0 1 1936 0 7 0 0 1 60 0 8 0 0 1 0 0 9 1308 1 1308 1308 10 40 1 40 40 11 188 1 188 188 12 0 1 0 0 13 900 1 900 900 14 0 1 0 0 15 132 1 132 132 16 600 1 600 600 17 220 1 220 220 18 600 1 600 600 19 160 0 0 0 20 68 0 0 0 21 80 0 0 0 22 400 0 0 0 23 0 0 0 0 24 100 0 0 0 25 900 0 0 0 26 250 0 0 0 27 300 0 0 0 28 906 0 0 0 29 1444 0 0 0 30 V > и 1/ Журнал регистрации X Аналих даннык / |< > Готово NUM Рисунок 52

Использование формул массива для анализа данных Массив - это множество ячеек, содержимое которых обрабатывается как единое целое. Такие ячейки могут указываться как именованный диапазон. Формула массива - это формула, оперирующая с одним или несколькими массивами.

При работе с формулами массива необходимо знать: признаком формулы массива являются фигурные скобки в начале и конце формулы, которые вводятся нажатием Ctrl+Shift+Enter либо после завершения ввода формулы, либо в процессе ее редактирования.

На новом листе создадим таблицу, выполняющую анализ расходов по заданным критериям.

Ш Microsoft Excel - Домашний бюджет •PJ Файл Правка Вид Вставка Формат Сервис Данные Окно Справка _ й1 X : j l3 >j л j 1 1 ^ а 1 Jt ^ % -1 - р -1 а, ? - и 1 [aueJ : Times New Roman .10 - Ж К *J = = % I I I -^Л - A - ^ A2 -fx 05.02.2DD6 A В 0 1 Критерий Данные 2 1 05.02.20061 {=СУ MMffiC ЛИ(Д ата>=А2 .Расход. 0)1} 3 15.02.2006 (=ОУ ММ(ЕО ЛИ(Д ата<=A3 расход ДД} 4 -'Журнал регистр ации'ГОЗ {=СУ ММ(ЕС ЛИ(Кто=А4,Расход,0В} 5 -Журнал регистрации1^ { =СУММ(ЕСЛИ(Откуда Куда=А5;Расход;0))} 6 -Журнал регистр ацииЧРП { =СУММСЕСЛИСН а что=А6;Расход,0))] 7 8 v 14 4 > н \ Анализ данных с помощью массива/ | > | Готово NUM Рисунок 53

Алгоритм расчета.

В ячейку В2 введем формулу, которая суммирует все значения расходов, произведенных 5.02.06 и далее.

{=СУММ(ЕСЛИ( Дата>=А2;Расход ;0))}

Дата и Расход - это имена диапазонов, они вставляются в формулу командой Вставка^Имя^Вставить.

В ячейку В3 введем формулу, которая суммирует все значения расходов, произведенные до 15.02.06 и далее: {=СУММ(ЕСЛИ(Дата<=А3;Расход;0))}

Определяется, какая сумма потрачена на мать: {=СУММ(ЕСЛИ(Кто=А4;Расход;0))}

Определяется, какая сумма потрачена на Обувь: {=СУММ(ЕСЛИ(Откуда_Куда=А5 ;Расход ;0))}

Определяется, какая сумма потрачена на летнюю обувь: {=СУММ(ЕСЛИ(На_что=А6;Расход ;0))}

Для создания модуля последовательно вложим формулы друг в друга и получим, сколько потрачено на летнюю обувь для мамы: {=СУММ(ЕСЛИ(Дата>=А2; ЕСЛИ(Дата<=А3; ЕСЛИ(Кто=А4; ЕСЛИ(Откуда_Куда=А5; ЕСЛИ(На_что=А6;Расход;0);0);0);0);0))}

Рисунок 54

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

какая денежная сумма потрачена на определенного члена семьи; какая денежная сумма проходит по определенной статье расходов; что именно приобретено по этой статье расходов.

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

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

На листе Расходы! создайте таблицу: Ш Microsoft Excel - Домашний бюджет : Ipj файл Правка Вид Вставка Формат Сервис Данные Окно Справка Введите воорос - .

с

Расходы по членам семьи

В том числе

Отец

Мать

Ребенок

Общее

В том числе

Расходы всего

38,52% 25,93% 9,50% 26,06%

100%

2746 1006 2760

Отец ¦ Мать

Ребенок

10392 952 1408 3030 3652 1550

Расходы по статьям

Оплата жилья

Машина

Одежда

Обувь

Питание

8,99% 13,29% 28,61% 34,48% 14,63%

Общее

Оплата жилья и Машина

Одежда

Обувь ¦ Питание |<

NUM

и it й / Журнал регистрации \ Расходы 1 /

Готово Алгоритм.

Ячейкам В1 и В2 присваиваются имена ПериодС и ПериодПо соответственно.

В ячейке В4 просуммируйте расходы за указанный период: =СУММ(В6:В9).

В ячейке В6 определяется сумма денег, потраченная за указанный период времени на конкретного члена семьи. Для создания формулы воспользуйтесь модулем, разработанным ранее. Скопируйте формулу, находящуюся в ячейке В2 листа Модуль для анализа данных, и отредактируйте следующим образом:

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

Для всех остальных членов семьи формулы копируются.

Аналогично определяются формулы для ячеек В12:В16: {=СУММ(ЕСЛИ(Дата>=ПериодС;ЕСЛИ(Дата<=ПериодПо; ЕСЛИ(Откуда_Куда=A12;Расход;0);0);0))}

В столбце D определите процентное соотношение расходов, например, в ячейку D6 введите формулу: =B6/$B$4.

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

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

На листе Расходы2 создайте таблицу:

Microsoft Excel - Домашним бюджет U Введите вопрос I юо% - | # j

1 Файл Правка Вид Вставка Формат Сервис Данные Окно Справка

¦4 211

j is а ^ gi^ai * д.- J\ - -i&s- TIMES NEW ROMAN
^Ж X ч |j I 1 % QOO % I W iW I ? . <3» » A , | N12 I

I

н

к

I Период

01.02.2006 26.02.2006 Расходы

Расход по статьям на каждого члена семьи Общее

Отец

Ребенок ¦ Оплата жнлъя

Оплата жилья

Одежда

Обувь

Машина

Одежда

Обувь

Питание 1<

> I

и 4 > н/ Журнал регистрации / Расходы! \Расходы2/ РасходыЗ / Даты берутся с листа Расходы 1.

В ячейке В 5 вычислите сумму, потраченную за указанный период времени на каждого члена семьи по конкретной статье расходов: {=СУММ(ЕСЛИ(Дата>=ПериодС;ЕСЛИ(Дата<ПериодПо;ЕСЛИ(Кто=В$4; ЕСЛИ(Откуда_Куда=$А5;Расход;0);0);0);0))}

Расходы по статьям с детализацией

На листе РасходыЗ создайте таблицу: 00®

Q Microsoft Excel - Домашний бюджет i^J Файл Правка Вид Вставка Формат Сервис Данные Окно Справка

j j а ^ j I .jf д I у a I jt ^са- л ч - ^ ч а, ^ - a! I j

: Times New Roman

I Ж к ч I E I I i I f % ооо Щ - A - ^ .122 А в С Е F О н 1 Период 01.02.2006 26.022006 2 3 Расходы 4 Оплата жилья Машина Одежда Обуиь Питание И п| in 3 Газ 132,00 0,00 0,00 0,00 0,00 132,00 6 Свет 600.00 0,00 0,00 0,00 0,00 600,00 7 Вода 220,00 0,00 0,00 0,00 0,00 220,00 8 Запчасти 0,00 600,00 0,00 0,00 0,00 600,00 9 Стоянка 0,00 160,00 0,00 0,00 0,00 160,00 10 Штрафы 0,00 68,00 0,00 0,00 0,00 68,00 11 Бешнн 0,00 580,00 0,00 0,00 0,00 580,00 12 Верхняя 0,00 0,00 1936,00 0,00 0,00 1936,00 13 Нижняя 0,00 0,00 188,00 0,00 0,00 188,00 14 Летняя 0,00 0,00 0,00 2344,00 0,00 2344,00 15 Знганяя 0,00 0,00 0,00 1308,00 0,00 1308,00 16 Детонсеюнная 0,00 0,00 906,00 0,00 0,00 906,00 17 Мясо 0,00 0,00 0,00 0,00 900,00 900,00 18 Мучное 0,00 0,00 0,00 0,00 0,00 0,00 19 Фрукты 0,00 0,00 0,00 0,00 100,00 100,00 20 Обеды 0,00 0,00 0,00 0,00 550,00 550,00 21 Всего 952,00 1408,00 3030,00 3652,00 1550,00 10592,00 22 н 1/ Журнал регистрации / Расходы! / Расходы2 \ РасходыЗ f~ 1 < > |

Готово NUM

Формула в ячейке В 5 создается и копируется во все остальные ячейки диапазона: {=СУММ(ЕСЛИ(Дата>=ПериодС;ЕСЛИ(Дата<ПериодПо; ЕСЛИ(Откуда_Куда=$В4; ЕСЛИ(На_что=$А5;Расход;0);0);0);0))}

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

Предположим, частный предприниматель, специализирующийся на изготовлении столярных изделий под заказ (столы и стулья), ведет расчеты с помощью электронных таблиц. Условно изделия разделяются на два ви-

да: большие и маленькие. Вид материала: сосна, дуб. В процессе изготовления применяются следующие материалы и инструменты: лак, клей, шурупы.

Журнал операций будет иметь вид: ЕВ®

? Microsoft Excel - Мелкотоварное производство Файл Правка Вид Вставка Формат Сервис Данные Окно Справка Times Wew Roman

—' Е

ж ^ а » ¦ я sain* А2

? 01.02.2001 А В 0 D Е F G н 1 Дата Доход Расход Наименование Размер Из чего Что именно 2 01.02.2001 400,00 Сосна 3 03.02.2001 700,00 Дуб 4 04.02.2001 100,00 Шурупы 5 05.02.2001 50,00 Клей 6 05.02.2001 200,00 Лак 7 06.02.2001 -70,00 Стулья Большие Сосновые Сосна 8 07.02.2001 -100,00 Стулья Большие Дубовые Дуб 9 08.02.2001 -40,00 Стулья Маленькие Сосновые Сосна 10 09.02.2001 -45,00 Стулья Маленькие Дубовые Дуб И 10.02.2001 -160,00 Сшпы Большие Сосновые Сосна 12 11.02.2001 -200,00 Стелы Большие Дубовые Дуб 13 12.02.2001 -120,00 Сшпы Маленькие Сосновые Сосна 14 13.02.2001 -150,00 Сшпы Маленькие Дубовые Дуб 13 14.02.2001 -8,00 Стулья Большие Сосновые Шурупы 16 15.02.2001 -10,00 Стулья Большие Дубовые Шурупы 17 16.02.2001 -5,00 Стулья Маленькие Сосновые Шурупы 18 17.02.2001 -5,00 Стулья Маленькие Дубовые Шурупы 19 18.02.2001 -21,00 Сшпы Большие Сосновые Шурупы 20 19.02.2001 -18,00 Сшпы Большие Дубовые Шурупы 21 19.02.2001 -15,00 Сшпы Маленькие Сосновые Шурупы 22 20.02.2001 -15,00 Сшпы Маленькие Дубовые Шурупы 23 21.02.2001 -5,00 Стулья Большие Сосновые Клей 24 22.02.2001 -5,00 Стулья Большие Дубовые Клей >IК ЖурнапОпераций /

Покупка и расход материала ЕЕН

? Microsoft Excel - Мелкотоварное производство U Введите вопрос I -ч; 1ю% - ф |

Файл Правка Вид Вставка Формат Сервис Данные Окно Справка

SI 1\

j^d ^JH^qi^aijt ^ Д' J\ "IS ? -

> - А

iPlf Е

i Times New Roman т 10 т | Ж К Ч | Щ Щ Щ ^ | ^ % QQQ I

С D

Е F G Н I J^ К Период

01.02.2001 29.02.2001

Расход материалов на все изделия Куплено всего ¦ 15%

В том числе

Сосна

Дуб

Шурупы

Использовано

Дуб

Шурупы

Клей

Лак 32,0 40,6

Дуб

Остаток материала на все изделия Шурупы

Клей Шурупы

Дуб

Сосна

Дуб

Шурупы

Клей

Лак

0,6% J,u л 4,3% 1,3%|

> I

н ^ > н К ЖурналОпераций \ Расходы! / Расходы2 Готово

/ Выручка / Прибыль , | <

NUM Расчет.

1. В ячейку В6 введите формулу, вычисляющую сумму, израсходованную за определенный период времени на приобретение материала, указанного в ячейке А6:

{=СУММ(ЕСЛИ(Дата>=ПериодС;ЕСЛИ(Дата<=ПериодПо; ЕСЛИ(Наименование=A6;Расход ;0);0);0))}

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

{=-СУММ(ЕСЛИ(Дата>=ПериодС;ЕСЛИ(Дата<=ПериодПо; ЕСЛИ(Что_именно=А 14; Доход ;0);0);0))}

В ячейку В22 введите формулу, определяющую разность между купленным и израсходованным материалом за указанный период времени: =B6-B14

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

ЕЗ Microsoft Excel - Мелкотоварное производство ^И Введите вопрос

i- A-l

¦ *§J Файл Правка Вид Вставка Формат Сервис Диаграмма Окно Справка

и j а л ji^ lAiEgai а а- /I ^ - р- ч & г . и

12 - Орфография | - | g

Times New Roman

Область диа... Ў I

I

I

I

I

I

J К L Период

01.02.2001 29.02.2001 Дуб'

Стулья

Стулья

Наименование i Малек

Большие.

70,00 390,00 Шурупы

Клен

jflaK Сосна

Дуб

Шурупь

Клей

Лак

200,00 150,00 100,00 50,00 0,00

Расходы материалов на изделия п п Lb !¦ ¦ _ 1 1 ш rJ П _ Маленькие | Большие Стопы

Маленькие | Большие Стулья

Маленькие | Большие Стопы

Маленькие | Большие Стулья > I

и < > м \ ЖурналОпераций / Расходы 1 \Расходы2 / Выручка / Прибыль / Готово Расчет.

Ячейки В4:Е4 и F4:I4 объединены и имеют адреса В4 и F4 соответственно. Ячейки В5:С5, D5:E5, F5:G5, H5:I5 также объединены и имеют адреса В5, D5, F5 и H5 соответственно.

Формула в ячейке В7 определяет, сколько материала, указанного в ячейке А7, потрачено на изготовление маленьких дубовых столов за указанный период времени:

{=-СУММ(ЕСЛИ(Дата>=ПериодС;ЕСЛИ(Дата<=ПериодПо; ЕСЛИ(Из_чего=$B$4;ЕСЛИ(Наименование=$B$5; ЕСЛи(Размер=B$6;ЕСЛИ(Что_именно=$A7;Доход;0);0);0);0);0);0))} В ячейку С7 формула копируется.

После вставки этой же формулы в ячейку D7 необходимо скорректировать адрес ячейки, указывающей наименование изделия. Измените адрес на $D$5. Формула в ячейке D7 будет иметь вид: {=-СУММ(ЕСЛИ(Дата>= ПериодС;ЕСЛИ(Дата<=ПериодПо; ЕСЛИ(Из_чего=$B$4;ЕСЛИ(Наименование=$D$5;ЕСЛИ(Размер=D$6; ЕСЛи(что_именно=$A7; Доход ;0);0);0);0);0);0))}

При копировании формулы в ячейку F7 скорректируйте адреса ячеек, указывающих наименование изделия и материал, из которого они изготовлены. Измените адреса на $F$5 и $F$4 соответственно. Формула в ячейке F7 примет вид: {=-СУММ(ЕСЛИ(Дата>= ПериодС;ЕСЛИ(Дата<=ПериодПо; ЕСЛИ(Из_чего=$F$4;ЕСЛИ(Наименование=$F$5; ЕСЛи(Размер=F$6;ЕСЛИ(Что_именно=$A7;Доход;0);0);0);0);0);0))}

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

Распределение прибыли по изделиям

Ш Microsoft Excel - Мелкотоварное производство ¦ Файл Правка Вид Вставка Формат Сервис Данные Окно Справка

. ®

ijija^ji^^i^-au д - /1-1- -1 & ^ - ЙН1 i А -I

Times New Roman

- ж к ч ш ш т ^ % см а if ^ ? ¦ 122 Период

01Л2.2001 29.02:2001 Прибыль Наименование

Дубо]

Столы Сгаульп

Оямм Стулья Прибыль по изделиям Дубовые Столы

Дубовые Стулья

Сосвювые Столы

Сосвювые Стулья

Большие

0,00

ЕЁЁ

Маленькие > I

MUM

и ^ > н / Расходы1 / Раскоды2 / Выручка У Прибыль / Готово 1. Таблица отражает, какая прибыль получена от реализации изделий каждого вида.

Формула в ячейке В6 имеет вид: {=СУММ(ЕСЛИ(Дата>=ПериодС;ЕСЛИ(Дата<ПериодПо; ЕСЛИ(Наименование=B$5;ЕСЛИ(Размер=$A6; ЕСЛи(что_именно=0;ЕСЛИ(Из_чего=$B$4;Доход;0);0);0);0);0);0))+ СУММ(ЕСЛИ(Дата>=ПериодС;ЕСЛИ(Дата<ПериодПо;ЕСЛИ(Наименование=B $5; ЕСЛИ(Размер=$A6;ЕСЛИ(Что_именно>0; ЕСЛИ(Из_чего=$B$4;Доход;0);0);0);0);0);0))}

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

значения в столбце Что именно равны 0 - записи, удовлетворяющие этому условию, отражают реализацию товара и содержат в столбце Доход положительные значения;

значения в столбце Чтоименно больше 0 - записи относятся к операциям по расходу материалов и поэтому соответствующие суммы в столбе Доход занесены со знаком минус.

Самостоятельные задания

На депозит положили 1000000 рублей под сложный процент. Период капитализации - 1 день (каждый день начисляется процент на основную сумму и на начисленные за предыдущее время проценты). Рассчитать, как будет расти сумма на депозите с 1.06.06 по 1.07.06, если формула расчета сложных процентов следующая: Р1=Р0*(1+1)л(п2-щ), где п2 - последующий день; п1 - предыдущий день.

Ответ.

При каком размере ежемесячного платежа удастся накопить миллион к 50 годам без изменения ставки 15%, если вам сейчас 35 лет.

Ответ. =ПЛТ(0,15/12;(50-35)*12;;1000000), ответ - 1495,87 руб. Отрицательное значение означает расход средств.

Вычислить n-годичную ипотечную ссуду покупки квартиры за Р рублей с годовой ставкой i% и начальным взносом А%. Сделать расчет для ежемесячных и ежегодных выплат. Вариант n Р I А 1 4 500000 7 10 2 5 600000 8 9 3 6 700000 9 8 4 7 800000 10 7 5 8 900000 11 6 6 9 100000 12 5 7 10 1500000 13 5 8 15 2000000 14 5 9 20 2500000 15 5 10 25 3000000 16 5 4. Вас просят дать в долг Р руб. и обещают вернуть Pi руб. через год, Р2 руб. - через два года и т.д., наконец Pn руб. через n лет. При какой годовой процентной ставке эта сделка имеет смысл? Вариант n Р Р1 Р2 Рз Р4 Рз 1 3 17000 5000 7000 8000 2 4 20000 6000 6000 9000 7000 3 5 22000 5000 8000 8000 7000 5000 4 3 30000 5000 10000 18000 5 4 35000 5000 9000 10000 18000 6 5 21000 4000 5000 8000 10000 11000 7 3 25000 8000 9000 10000 8 4 31000 9000 10000 10000 15000 9 5 32000 8000 10000 10000 10000 11000 10 3 36000 10000 15000 21000 5. Вас просят дать в долг Р руб. и обещают возвращать по А руб. в течение n лет. При какой годовой процентной ставке эта сделка имеет смысл?

71 Вариант n Р А 1 7 170000 30000 2 8 200000 31000 3 9 220000 33000 4 10 300000 34000 5 11 350000 41000 6 7 210000 32000 7 8 250000 37000 8 9 310000 40000 9 10 320000 35000 10 11 360000 41000 6. Составить отчетную ведомость реализации товаров п магазинами с

месяца А по месяц В

Вариант А В п 1 май декабрь 3 2 июнь январь 4 3 июль октябрь 5 4 август январь 6 5 сентябрь декабрь 7 6 октябрь март 8 7 ноябрь март 9 8 декабрь июль 10 9 январь июль 4 10 февраль август 5 1. Создайте два окна для листа База. Расположите их рядом, друг под другом; разделите лист на области.

Создайте области так, чтобы были видны:

столбцы: порядковый номер, табельный номер, фамилия;

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

Используя Автофильтр, выведите на экран:

6 наибольших табельных номеров;

15% наименьших идентификационных номеров;

строки с порядковыми номерами с 5 до 11;

фамилии, начинающиеся на П;

должности, заканчивающиеся на К или А;

сотрудников, работающих в определенном отделе;

Используя Расширенный фильтр, выведите сотрудников:

c именем Иван или отчеством Петрович;

принятых на работу после 1.01.2000;

мужчин, старше 50 лет.

Используя Расширенный фильтр, выведите данные сотрудников, имеющих заданные табельные номера.

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

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

Запишите прописью произвольное четырехзначное число.

На основании журнала регистрации создайте таблицу, анализирующую:

расходы на каждого члена семьи с детализацией;

доходы семьи, постройте диаграмму.

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

питание с 1.02.06 по 14.02.06.

обувь для ребенка с 3.02.06 по 24.02.06.

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

Годовой фонд заработной платы работника на предприятии составляет 36000 руб. Работник одинок, на его иждивении находятся двое детей. Рассчитать налоги, если:

заработная плата выплачивается равномерно в течение всех 12 месяцев года по 3000 руб;

фонд заработной платы разделен пополам. Первая (18000 руб.) половина выплачивается равномерно в течение 12 месяцев (ежемесячная зарплата 1500 руб.), а вторая половина выплачивается как вознаграждение по итогам работы за год. Начисляется эта премия в декабре этого же года и соответственно входит в фонд заработной платы декабря этого года.