 |
Тема 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% от оклада), сумму удержанного налога на доходы физических лиц (ПН) равного:
12%, если сумма оклада и премии равна или меньше 20-кратного размера МРОТ и
15%, если сумма оклада и премии больше 20-кратного размера МРОТ, но меньше или равна30-кратному размеру МРОТ,
если больше – 20%.
| МРОТ | |||||||
| Коэффициент | |||||||
| Nп/п | Ф.И.О. | Должность | Разряд | Оклад | Премия | Удержано ПН | К выдаче |
| Аванесян | менеджер | ||||||
| Алахвердова | экономист | ||||||
| Бабаян | продавец |
Рис. 1.40. Ведомость начисления заработной платы
Задача 3. Рассчитайте сумму премии:
50% от оклада, если сумма оклада не превышает 400 руб.,
40% от оклада – если равен или более 4000 руб.,
сумму удержанного налога на доходы физических лиц (ПН) равного 20% от премии и оклада, сумму к выдаче. Постройте круговую диаграмму, показывающую удельный вес з/п к выдаче по каждому работнику в общей сумме зарплаты.
| МРОТ | |||||||
| Nп/п | Ф.И.О. | Должность | Разряд | Оклад | Премия | Удержано ПН | К выдаче |
| Кудилина | зам. Директора | ||||||
| Легейда | продавец | ||||||
| Малютина | бухгалтер |
Рис. 1.41. Ведомость начисления заработной платы
|
|
|
Задача 4. Рассчитайте сумму оклада (оклад = номер разряда * МРОТ * коэффициент), рассчитайте сумму премии (50% от оклада), сумму удержанного налога на доходы физических лиц (ПН) равного:
20%, если сумма оклада и премии равна или меньше 20-кратного размера МРОТ,
25%, если сумма оклада и премии больше 20-кратного размера МРОТ.
| МРОТ | |||||||
| Nп/п | Ф.И.О. | Должность | Разряд | Оклад | Премия | Удержано ПН | К выдаче |
| Коньков | Директор | ||||||
| Легина | продавец | ||||||
| Малютина | бухгалтер |
Рис. 1.42. Ведомость начисления зар. платы
Задача 5. Фирма «Дельта М» занимается поставками бытовой техники. В марте со склада по договорам был отпущен товар основным покупателям в количестве 15200 единиц. Создайте таблицу, отражающую общие суммы продаж магазина «Дельта +» в рублях за март по каждому из покупателей.
Исходные данные смотрите в таблице на рис. 1.43.
Расчетная таблица представлена на рис 1.44.
Рис. 1.43. Продажи фирмы за март
Рис 1.44. Таблица для расчета сумм продаж за март по каждому покупателю
Задача 6. Используя данные таблицы на рисунке 1.43, определите, сколько всего единиц миксеров, СВЧ печей, кофеварок, чайников, аэрогрилей было продано в марте.
Задача 7. Фирма занимается реализацией бытовой техники. В феврале были заключены сделки и отпущена продукция четырем основным покупателям. Фирма имеет несколько складских помещений, с которых отпускается товар. Покупатели пользуются тем складом, который расположен ближе остальных. Так «Авангард» и «Дом» получают товар со склада №2, «Уют» - №3, «Крокодил» - №4.
Распределите с какого склада какому из покупателей был отпущен товар в феврале (функция ЕСЛИ). Рассчитайте полученные суммы денег по каждой сделке, предварительно записав цену каждого вида отпускаемого товара (функция ЕСЛИ).
Рис 1.45. Продажи фирмы за февраль
|
|
|
