Лабораторная работа 5

Цель работы: освоить методы построения и заполнения документов бухгалтерской отчетности в программе Microsoft Excel с использованием функций ЕСЛИ и СУММЕСЛИ.

Задание. Предположим, что в первый отчетный период деятельности предприятия учредители внесли деньги в уставный капитал в размере 50 %. Для обеспечения деятельности предприятия были приобретены товары и материалы, оплачена аренда помещения. Для получения дохода был заключен договор с заказчиком на выполнение работ (оказание услуг) и получен аванс. Необходимо отразить все перечисленное в журнале операций [1].

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

Далее рассчитайте суммарные обороты по счетам. Для этого необходимо автоматизировать занесение сумм из столбца С в ячейки по дебету или по кредиту каждого счета, в случае, если этот счет используется в проводке (в столбцах D и Е). Для этого примените формулу, в основе которой лежит логическая функция ЕСЛИ(). Функции можно заносить вручную или с помощью Мастера функций. Делается это следующим образом.

Выберете ячейку в первой строке дебета 51 счета — это ячейка FЗ — и вызовите окно Мастер функций - в категории Логические выберете функцию ЕСЛИ.

В строке Логическое_выражение необходимо записать выражение, выполнение или не выполнение которого приведет к получению определенного результата. Выражение представляет собой сравнение содержащее (обязательно) один из знаков равенства - неравенства – «=», «>=», «<=», «>», «<».

Для того, чтобы разнести суммы по счету 51, по каждой операции необходимо с помощью функции ЕСЛИ записать формулу, которая, в случае использования в операции счета «Касса», автоматически будет заносить сумму этой операции в соответствующую строку по дебету или кредиту счета 51. Формула для дебета 51 счета (ячейки FЗ) будет иметь вид:

=ЕСЛИ(DЗ=F1;CЗ;"") - ЕСЛИ «номер счета в ячейке DЗ равен номеру счета столбца F» истинно, функция возвращает значение суммы из ячейки CЗ. В противном случае функция возвращает *пустое значение, так как в поле Значение_если_ложь введены две кавычки, означающее пустую текстовую строку.

Такую же операцию выполните со всеми ячейками журнала операций. Конечно, вручную для каждой операции это выполнять не имеет смысла, поэтому воспользуйтесь функцией автозаполнения. При этом следует помнить об относительных и абсолютных ссылках на ячейку. В данном случае необходимо воспользоваться абсолютной ссылкой на ячейку F1(рисунок 33).

Так как необходимо распространить эту логическую формулу и на остальные ячейки таблицы (столбцы), ее следует модифицировать следующим образом:

Аналогично выглядит формула для кредита в соседней ячейке:

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

Далее рассчитайте суммы в столбцах Дебет и Кредит каждого счета. Для этого в результирующую строку каждого столбца поместите суммирующую формулу.

Задание 1. Используя данные из таблицы на рисунке 32 рассчитайте сумму оборотов по каждому счету по дебету и по кредиту.

Для этого занесите в столбец С, ниже первоначального журнала операций, изображенного на рисунке 32, перечень всех используемых в примере счетов.

Далее воспользуйтесь функцией СУММЕСЛИ*(рисунок 34).

В первую строку этого перечня справа от значения счета (то есть в столбец D) занесите формулу:

Такая запись означает, что необходимо просуммировать значения тех операций, где дебетуется счет 51, и таким образом в ячейке D14 получится оборот по дебету счета 51.

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

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

Проверьте правильность расчетов, просуммировав вручную оборот по дебету 60 счета (рисунок 35).

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

Решение. Для решения создайте два новых листа. Один назовите Покупатели, другой - Товары. Лист с исходными данными назовите Продажи.

На лист Покупатели (рисунок 38) поместите в левый столбец наименования покупателей, скопировав их с листа Продажи (рисунок 37), а в столбец «Сумма», справа от него, функцию СУММЕСЛИ (рисунок 36). В данном случае она будет выглядеть так:

Формула приведена для ячейки В2. Формулы в последующих ячейках будут отличаться только номером строки критерия.

Лист Товары организован аналогично, но по товарам. Кроме их общей стоимости интересует их количество, поэтому в эту таблицу добавлено поле Количество (рисунок 39.)

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

Для контроля просчитайте суммарные результаты по столбцам Сумма на всех трех листах. Если эта величина везде равна 43340000, значит, расчеты проведены правильно.

Ø Какими двумя способами можно записывать функцию ЕСЛИ в ячейку?

Ø Что означает термин «возвращает» применительно к функциям рабочего листа электронных таблиц?

Ø В каких случаях используется функция ЕСЛИ?

Ø В каких случаях используется функция СУММЕСЛИ?

Ø В чем отличие между возможностями функции ЕСЛИ и СУММЕСЛИ?

* Термин «возвращает» применительно к функциям рабочего листа электронных таблиц означает результат действия функции.

При наборе формулы используйте ввод адресов ячеек с помощью выбора нужных ячеек мышью, вводя вручную только символ = и две кавычки [1].

* Функция СУММЕСЛИ складывает цифры, если они отвечают определенным условиям. Структура функции такова:

СУММЕСЛИ(интервал;критерий;сумм_интервал).

Значения параметров функции следующие:

• Интервал — это массив ячеек, по которому проверяется условие суммирования;

• Критерий — это условие суммирования в форме числа, выражения или текста, — например, критерий может быть выражен как 32, «32», «>32», «яблоки»;

• Сумм_интервал — это массив ячеек, параллельный массиву Интервал, по которому осуществляется суммирование; ячейки в сумм_интервал суммируются, только если соответствующие им ячейки в аргументе интервал удовлетворяют критерию[1].

ЗАДАНИЯ ДЛЯ САМОСТОЯТЕЛЬНОЙ РАБОТЫ: