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

Автоматизація бухобліку: Урок 3.
Бухгалтерський облік за допомогою EXCEL

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

Продовження. Початок див. "ДК" №№47, 49.


Розрахунок прибуткового податку. Оператори порівняння

Наступним кроком буде розгляд механізму розрахунку прибуткового податку.

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

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

Малюнок 1.

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

Малюнок 2.

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

Припустімо, в комірку С2 введена формула "=С5<С6" (за її допомогою ми порівняємо загальний стаж двох працівників). Після того як ви підтвердите її введення натисненням кнопки "ОК", це твердження буде оцінене як помилкове, і в комірці С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