Дебет-Кредит
Украинский бухгалтерский еженедельник
#52 '2000: Практичная бухгалтерия - Автоматизация бухучета: Excel

Автоматизация бухучета:
Бухгалтерский учёт с помощью Excel.
3-й урок

Владимир ЛАВРЕНОВ, Вячеслав ВАРЕНЯ


Продолжение. Начало см. "ДК" №№ 47, 49.


Следующий урок


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

Расчет подоходного налога. Операторы сравнения

Следующим шагом будет рассмотрение механизма расчета подоходного налога.

По условиям нашего примера сотрудники Кротов Ю. В., Загреба О. Ф., Гетьман Н. А., Лисовский С. А., Рябоконь Д. В., Петренко И. В. работают на предприятии по совместительству. Следовательно, в соответствии с действующим законодательством Украины, начисленный им доход должен облагаться подоходным налогом по ставке 20%.

Рисунок 1.

В примере Кротову Ю. В. начислено 571,43 грн аванса (см. рис. 2). Сумма подоходного налога с дохода Кротова Ю.В. составляет 114,29 грн (рис. 2, ячейка J5 "Всего начислено") и расcчитана по формуле "=J5*0,2", что, соответственно, означает 571,43 грн х 0,2. Сумма подоходного налога с дохода Загребы О. Ф. рассчитывается аналогично.

Рисунок 2.

Формула расчета суммы подоходного налога для работников предприятия, для которых оно является основным местом работы, сложнее, поскольку она должна учитывать дифференцированную шкалу сумм подоходного налога, утвержденную Декретом Кабинета Министров Украины от 26.12.92 г. №13-92 "О подоходном налоге с граждан" (см. рис. 2, ячейка К6). Такой расчет производится, как вы знаете, в зависимости от размера начисленной зарплаты, с выполнением нескольких условий.

Для того чтобы ввести в ячейку формулу, в которой необходимо выполнить одновременно несколько условий, используются операторы сравнения. В Excel доступны шесть операторов сравнения. Они используются, чтобы оценить утверждения: "ИСТИНА" или "ЛОЖЬ". Если утверждение верно, то ячейка, содержащая формулу, будет иметь значение "ИСТИНА".

Допустим, в ячейку С2 введена формула "=С5<С6" (c ее помощью мы сравним общий стаж двух работников). После того как вы подтвердите ее ввод нажатием кнопки "ОК", это утверждение будет оценено как ложное, и в ячейке С2 появится текст "ЛОЖЬ".

Внимание! Вместо этого текста в ячейке С2 вы можете увидеть (см. рис. 1) несколько знаков диез (####). Не удивляйтесь - Excel сообщает вам, что размер ячейки недостаточен для ее результата. Для того чтобы увидеть его в ячейке, растяните ее, установив курсор на границе указателя столбцов между С и D и, удерживая нажатой ЛКМ, потяните курсор вправо до тех пор, пока в ячейке С2 не появится текст "ЛОЖЬ".

Для одновременного выполнения нескольких операций сравнения используется функция "ЕСЛИ", которая записывает в ячейку одно значение, если указанное условие истинно, и записывает другое значение, если условие ложно. Например, в ячейку, в которой записана формула "=ЕСЛИ (J6<17;0;4)", будет введено значение 0, если значение ячейки J6 меньше 17, а если значение ячейки J6 больше 17, будет записано число 4.

Рассмотренный фрагмент формулы "=ЕСЛИ (J6<17;0;ЕСЛИ(J6<85;(J6-17)*0,1", введенной в ячейку К6, означает:

- если сумма в ячейке J6 меньше 17 грн (один не облагаемый налогом минимум доходов граждан) - ставка подоходного налога равна 0;

- если сумма в ячейке J6 больше 17, будет выполняться следующая функция - "ЕСЛИ(J6<85;(J6-17)*0,1". Это означает, что если сумма в ячейке J6 находится в пределах от 18 до 85 грн, то из указанной суммы вычитается 17 грн и результат умножается на 0,1.

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

Для ввода формулы, указанной на рис. 2, в ячейку К6 установите курсор, войдите в меню "Вставка", опцию "Функция", а в ней выберите функцию "ЕСЛИ". В результате указанной операции появится контекстное меню (см. рис. 3). Более простой способ введения в формулу функции - выбрать ее в разворачиваемом меню функций.

Рисунок 3.

В строку открывшегося меню "Логическое_выражение" введите "J6<17". Для ввода J6 достаточно установить курсор на эту ячейку (с этой целью щелкните ЛКМ на пиктограмме, расположенной справа от строки "Логическое_выражение", меню свернется и появится доступ к рабочему листу). В строку "Значение_если_истина" введите 0 и нажмите "ОК" или клавишу Enter.

Для того чтобы в формулу можно было внести соответствующие дополнения, щелкните ЛКМ на ячейке К6, а затем установите курсор в строке формул после числа 0 и введите точку с запятой ";". После этого вы можете вносить необходимые дополнения в формулу, используя автоматический ввод функций, расположенный в левой части строки формул (см. рис. 3).

Для редактирования формулы в ячейке дважды щелкните ЛКМ в ячейке. Выделите с помощью курсора обозначение функции "ЕСЛИ" (эти буквы при этом окрашены в черный цвет) и скопируйте ее, нажав опцию "Копировать" в меню "Правка" (или сочетание клавиш CTRL-C), и вставьте в необходимую позицию в общей формуле (рис. 3), нажав опцию "Вставить" в меню "Правка" (Ctrl-V).

Скопированный фрагмент попадает в буфер обмена и может быть вставлен в нужную ячейку (формулу) необходимое число раз или пока вы не замените его другим скопированным фрагментом.

Внимание! Редактируя формулу, не забывайте о регистре вводимого аргумента и о количестве разделительных знаков в формуле.

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

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

В колонке 12 Таблицы (ячейки L5:L16) отображены суммы начисленного сбора в Пенсионный фонд Украины. Напоминаем, что, в соответствии с Законом Украины от 22.10.98 г. №208-XIV "О внесении изменений в Закон Украины "О сборе на обязательное государственное пенсионное страхование", сбор в Пенсионный фонд взимается в размере 1%, если совокупный налогооблагаемый доход не превышает 150 грн, и 2% - если совокупный налогооблагаемый доход превышает 150 грн (о 32% расскажем позже).

Учитывая это, в формуле расчета суммы сбора в Пенсионный фонд также используется функция "ЕСЛИ".

В ячейку L5 введена следующая формула: "=ЕСЛИ(D5<=150;J5*0,01;ЕСЛИ(D5>150;(J5)*0,02))", что, соответственно, означает :

- если сумма в ячейке D5 меньше или равна 150 грн, тогда сумма в ячейке J5 умножается на ставку сбора 1%;

- если сумма в ячейке D5 больше 150 грн, сумма в ячейке J5 умножается на 2%.

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

Сумма сбора на случай безработицы (0,5%) (столбец 13 Таблицы) рассчитывается намного проще, чем сумма сбора в Пенсионный фонд и подоходного налога.

Например, в ячейке М5 введена формула "=J5*0,5/100". Эту формулу еще можно записать в таком виде: "=J5*0,005". Указанная формула является одинаковой для всех ячеек столбца 13, так что смело копируйте ее из ячейки М5 в остальные ячейки столбца.

В столбце 15 (см. рис. 3) отображена сумма удержаний, а в столбце 16 - сумма к выдаче.

Сумма удержаний рассчитывается с помощью функции автосуммы. Выделите диапазон К5:О15, для чего щелкните ЛКМ на ячейке К5 и, удерживая ее нажатой, потяните до ячейки К15, а затем вправо до ячейки О15. После этого либо нажмите на главном меню кнопку со значком е , или выберите в меню "Вставка" опцию "Функция", а в ней формулу "СУММ". В результате этих действий в ячейках К16:О16 появятся суммы соответствующих столбцов.

Сумма к выдаче рассчитывается путем вычитания из данных в столбце 10 "Всего начислено" данных столбца 15 "Всего удержано". Для осуществления этой операции введите в ячейку Р5 формулу "=J5-O5", а затем скопируйте ее в остальные ячейки столбца 16 известным вам способом.

Связывание данных

При заполнении ячейки I7 и ячеек N5:N15 ("Сумма выданного аванса") на рабочем листе "ЗП февраль" используются данные, которые были рассчитаны в рабочем листе "Аванс февраль". Это достигнуто с помощью важнейшего свойства программы Excel - связывания, которое применяется для отображения на рабочем листе информации, собранной из различных мест. Исходные данные могут поступать из многих источников: из другого рабочего листа, других рабочих книг и даже из других программ. При связывании данные, используемые из ячеек источника, остаются в том же месте, а в целевой ячейке появляется копия источника. Программа Excel позволяет настроить целевую ячейку так, что она будет автоматически реагировать на изменение в источнике (интерактивная связь), или чтобы информация в целевой ячейке обновлялась только по вашему указанию.

Рассмотрим пример установления интерактивной связи между листами одной книги. По условиям нашего примера между предприятием и его сотрудником Загребой О. Ф. заключен договор аренды его легкового автомобиля. По условиям этого договора предприятие обязано ежемесячно в течение его действия выплачивать Загребе О. Ф. арендную плату в размере 1500 грн. Как видно на рис. 4, Загреба О. Ф. получил 50% указанной суммы при выдаче аванса за февраль (столбец 9, ячейка I7). Следовательно, при выдаче заработной платы за февраль Загребе О. Ф. нужно доплатить 750 грн. Подобный расчет с помощью Excel можно сделать, связав листы "ЗП февраль" и "Аванс февраль" между собой.

Рисунок 4.

Для этого активизируйте лист "ЗП февраль", переместите курсор в ячейку I7 и введите знак "=" (признак формулы), а затем активизируйте лист "Аванс февраль" и установите курсор в ячейку I7 этого листа. В результате произведенных действий в ячейку I7 книги "ЗП февраль" будет введена следующая формула: "='Аванс февраль'!I7".

Теперь для окончательного расчета преобразуйте эту формулу в такой вид: "='Аванс февраль'!I7+750". Это означает, что вы к 750 грн, начисленных Загребе О. Ф. при выдаче аванса, прибавляете еще 750 грн.

Для правильного расчета всех выплаченных (подлежащих выплате) и начисленных сумм необходимо в листе "ЗП февраль" заполнить ячейки N5:N16. Для этого также воспользуемся возможностями установления интерактивной связи. С этой целью в ячейку N5 листа "ЗП февраль" введите указанным выше способом формулу "='Аванс февраль'!P5". Затем скопируйте эту формулу в остальные ячейки столбца 14 (рис. 3).

Следующим примером установления интерактивной связи является механизм заполнения ячеек К17:М17 "Оплата налогов по авансу". Заполнение этих ячеек позволяет бухгалтеру осуществлять контроль за правильностью расчета сумм уплаченных и подлежащих уплате налогов и сборов. Введите в ячейку К17 листа "ЗП февраль" известным вам способом формулу "='Аванс февраль'!K16" и скопируйте ее в ячейки L17, M17, потянув влево ЛКМ за правый нижний угол ячейки К17.

Если вы не хотите вручную считать, сколько надлежит доплатить в бюджет при выплате заработной платы, рекомендуем заполнить ячейки К18:М18 листа "ЗП февраль". Для этого в ячейку К18 листа "ЗП февраль" введите формулу "=K16-K17" и скопируйте ее в ячейки L18:M18 способом, указанным выше.

Внимание! Если имя рабочего листа (например, "Аванс февраль"), в котором находится ячейка-источник, содержит пробелы, при вводе формулы в целевую ячейку имя этого рабочего листа должно быть заключено в одинарные кавычки, например, "'Аванс февраль'!" (восклицательный знак после имени листа обязателен, он отделяет ссылку на лист от ссылки на ячейку).

Если вы забыли набрать кавычки, щелкните на кнопке "Отмена" (в строке формул - значок "х" ). Дважды щелкните на целевой ячейке, наберите кавычки в нужных местах и нажмите Enter.

Автоматизация расчета отчислений при расчете издержек на оплату труда тоже очень актуальна для любого бухгалтера. Таблицу "Отчисления", которая поможет вам автоматизировать такие расчеты, можно создать на листе "ЗП февраль" (ячейки В20: F25, рис. 5).

Рисунок 5.

Для выполнения необходимых расчетов введите в ячейки D21: F25 листа "ЗП февраль" формулы, представленные на рис. 6. Обратите внимание на ячейки Е21:Е23. В них введены формулы, использующие данные ячеек другого листа - "Аванс февраль".

Рисунок 6.

Итак, вы самостоятельно создали электронную ведомость по выплате заработной платы. Проверьте, все ли формулы введены правильно. Для того, чтобы удобно было проконтролировать правильность введения формул, нажмите на клавиатуре вместе клавишу Ctrl и левую кавычку "'" - крайнюю левую клавишу в цифровом ряду. Рабочий лист изменится, и в ячейках вместо числовых значений появятся введенные вами формулы. Может оказаться , что "горячие" клавиши у вас не выбраны, тогда вызовите в меню "Сервис" окно "Параметры", откройте в нем вкладку "Вид" и активизируйте в ней переключатель "Формулы", а выбор подтвердите "ОК" (рис. 7). После этих действий на активном рабочем листе появятся формулы.

После проверки введенных формул можно выключить опцию "Формулы", щелкнув в соответствующем окошке ЛКМ (см. рис.  7).

Рисунок 7.

Внимание! Не забудьте сохранить рабочую "КНИГУ1" под своим именем (например, "Зарплата") - и можно переходить к созданию нового документа.

Внесение изменений в таблицу. Депонирование заработной платы

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

В составленной нами таблице с целью упрощения не было введено несколько параметров, которые на практике всегда используются бухгалтерами при учете заработной платы. Такой информацией могут быть сведения о депонировании заработной платы. В нашем примере депонирование зарплаты не производилось, поэтому для иллюстрации изменений в таблице введем дополнительные столбцы "Получено" и "Депонировано". Для того чтобы внести изменения в таблицу, нужно добавить соответствующее количество столбцов или строк. Введем в созданную таблицу на листе "ЗП февраль" и "Аванс февраль" дополнительный столбец "Депонированная зарплата".

Для вставки дополнительного столбца в таблицу необходимо выделить столбец, перед которым вставляются дополнительные ячейки (в данном случае это столбец Р - "К выдаче"), и вызвать контекстное меню нажатием ПКМ на выделенной группе ячеек. В контекстном меню выберите строку "Добавить ячейки", а в появившемся окошке "Добавление ячеек" - строку "Ячейки, со сдвигом вправо" и нажмите кнопку "ОК". В результате таких действий в таблице появится пустой столбец Р, а ячейки "К выдаче" переместятся в столбец Q.

В шапку таблицы введите наименование графы - "Депонированная зарплата".

Вы обратили внимание, что нумерация столбцов сбилась. Введенный столбец оказался непронумерованным. Для восстановления сквозной нумерации снова скопируйте формулу ячейки В4 ("=А4+1") во все ячейки строки В.

Вставка в таблицу дополнительного столбца в нашем случае привела к рассогласованию данных, полученных в столбце "К выдаче". В соответствующие ячейки этого столбца введена формула "=Jn-On" (n - номер строки), поэтому скорректируйте ее, учитывая депонирование зарплаты. Введите в ячейку Q5 формулу "=J5-O5-P5" и скопируйте ее в ячейки Q6:Q15.

Обратите внимание на то, что формула в ячейке Q16 ("Всего к выдаче") после ввода дополнительного столбца изменилась и имеет вид "=СУММ(Q5:Q15)". Эта формула вычисляет сумму ячеек, расположенных прямо над ней, поэтому при переносе ячеек столбца аргументы результирующей формулы "=СУММ(Q5:Q15)" тоже изменились.

Для добавления строк принцип аналогичен.

Удаление отдельных ячеек, столбцов или строк выполняется также с помощью контекстного меню выбором в нем строки "Удалить" (см. рис. 8).

Рисунок 8.

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

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

Внимание! При выборе в окошке "Добавление ячеек" строки "Столбец" произойдет сдвиг всего столбца Р. Это может быть нежелательно, если в столбце Р находятся ячейки, не связанные с данными изменениями и перемещать которые не нужно.


УВАЖАЕМЫЕ ЧИТАТЕЛИ! В этих уроках вы познакомились только с некоторыми основными аспектами программы Excel применительно к задачам бухгалтерского учета. Надеемся, на конкретном примере вы убедились, что использование программы Excel дает возможность не только облегчить рутинную работу бухгалтера, но и сделать ее более интересной и творческой.

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

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

С уважением, Владимир ЛАВРЕНОВ

Следующий урок

© 2000
"Дебет-Кредит"
Редакция: debet-kredit@gc.kiev.ua