Тема 5. Логические функции Microsoft Excel

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

q Функция ЕСЛИ

Пример.

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

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

Рис. 1.32. Журнал хозяйственных операций

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

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

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

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

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

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

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

=ЕСЛИ($DЗ=F$1;$CЗ;"").

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

=ЕСЛИ($E3=F$1;$CЗ;"").

Рис. 1.33. Функция ЕСЛИ

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

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

q Функция СУММЕСЛИ

Пример.

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

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

Рис 1.34. Функция СУММЕСЛИ

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

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

=СУММЕСЛИ(D$3:D$9;$C14;$C$3:$C$9)

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

Рис. 1.35. Учетный регистр

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

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

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

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

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

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

=СУММЕСЛИ(продажи!A2:A17;A2;продажи!F2:F17)

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

Рис. 1.36. Лист «Продажи»

Рис. 1.37. Лист «Покупатели»

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

Рис. 1.38. Лист товары

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

В ячей­ке В2:

=СУММЕСЛИ(продажи!C2:C17;A2;продажи!E2:E17),

В ячейке С2:

=СУММЕСЛИ(продажи!C2:C17;A2;продажи!F2:F17).

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

Контрольные вопросы

Какие аргументы содержит функция ЕСЛИ?

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

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

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

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

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

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

Задача 1. Определите товарооборот за год, рассчитайте сумму налога учитывая, что, если сумма товарооборота не превышает или равна 15 000 000 руб. налоговая процентная ставка равна 10%, иначе - 24%. Рассчитайте сумму налогов и определите чистую прибыль.

Чистая прибыль предприятия _________

Рис. 1.39. Товарооборот предприятия

Задача 2. Рассчитайте сумму оклада (оклад = номер разряда * МРОТ * коэффициент), рассчитайте сумму премии (50% от оклада), сумму удержанного налога на доходы физических лиц (ПН) равного: