Excel є, мабуть, найзатребуваним програмним продуктом офісного пакета Microsoft. Завдяки унікальним обчислювальним можливостям електронних таблиць, Excel став надійним інструментом бухгалтера в його повсякденній роботі. Якщо ви ще не випробували його обчислювальну потужність, зробити це можна прямо зараз, автоматизувавши розрахунок індексації заробітної плати. Отже, ми починаємо.
Трохи теорії
Ви вже ознайомилися з теоретичними особливостями індексації заробітної плати. У декількох статтях «Школи бухгалтера» на наочних прикладах була показана послідовність виконання таких розрахунків. Ви переконалися, наскільки трудомісткий у деяких випадках алгоритм обчислення індексації зарплати. Але ж у результаті помилки, допущеної хоча б на одному з етапів обчислень, можуть виникнути проблеми, які призведуть до серйозних фінансових наслідків. Тільки автоматизація таких розрахунків за допомогою електронних таблиць MS Excel дозволить уникнути цього.
Давайте спочатку ознайомимося з основними елементами електронної таблиці, які будуть використані при автоматизації індексації зарплати.
Формули. Секрет обчислювальних можливостей програми Excel полягає, насамперед, у структурі її робочого документа, що є таблицею, у кожній комірці якої можуть автоматично здійснюватися математичні та логічні операції. Ці обчислювальні операції виконуються за допомогою введених в комірки формул.
Структура формули Excel аналогічна звичайній математичній формулі. Формула в Excel завжди починається зі знака рівності (=). Наприклад, відомо, що зарплата, що індексується, дорівнює 548 грн. Щоб обчислити, на скільки буде збільшена зарплата працівника при індексації, якщо приріст споживчих цін для даного періоду дорівнює 1,8 %, необхідно до комірки, що обчислює, записати: =548*1,8/100.
Зараз ми розглянемо елементи, які будуть використовуватися нами при створенні формул, що автоматизують індексацію зарплати.
Посилання. У формулах можуть використовуватися як числові або текстові (не будемо їх використовувати) значення, так і посилання на інші комірки таблиці, що містять дані. Записану вище формулу можна представити й у вигляді посилань, якщо значення, які необхідно використовувати у формулі, вже містяться в інших комірках. На малюнку показано такий випадок. В комірці Н14 (H назва стовпчика, 14 номер рядка) записана формула, в якій як значення використані посилання на комірки F14 і G14.
Відмінність електронних таблиць Excel від інших продуктів полягає також у використанні унікального механізму копіювання. Досить перемістити маркер заповнення, розташований у нижньому правому куті виділеної комірки, у діапазон, що примикає до комірки, і її вміст буде скопійовано до всіх комірок цього діапазону. Звичайно, можна застосувати і звичайний спосіб копіювання. Однак ми особливо акцентуємо на цьому вашу увагу, тому що цей механізм буде широко використовуватися при створенні таблиці, що автоматизує індексацію зарплати.
Головною особливістю механізму копіювання в Excel є те, що при зміні позиції комірки, що містить формулу, посилання може змінюватися або не змі нюватися. Це буде залежати від того, який вид посилання використовується у формулі. В електронних таблицях використовуються три види посилань:
Відносні посилання. При копіюванні комірки, що містить формулу з відносними посиланнями, уздовж рядків або уздовж стовпчиків посилання автоматично коригується. Наприклад, при копіюванні комірки Н14, в якій записана формула з використанням відносних посилань = F14*G14/100, до сусідньої комірки Н15 скопійована формула набуде вигляду: = F15*G15/100. Тому що формула перемістилася в межах одного стовпчика вниз на один рядок, в посиланнях змінився тільки номер рядка (на одну одиницю).
Абсолютні посилання комірок у формулі завжди посилаються на комірку, розташовану у певному місці та при копіюванні комірки абсолютні посилання не змінюються. На абсолютне посилання вказує наявність символу «$» перед заголовками стовпчика або рядка комірки. Наприклад, якщо скопіювати формулу =$F$14*$G$14/100 до будьякої комірки таблиці, то обчислене значення завжди буде однаковим.
Змішані посилання містять тільки або відносне посилання на стовпчик і абсолютне посилання на рядок, або абсолютне посилання на стовпчик і відносне посилання на рядок. Наприклад, якщо формулу =F$14*G14/100, записану до комірки Н14, скопіювати до комірки Н15, то вона зміниться на =F$14*G15/100, тому що в цій формулі посилання на рядок 14 абсолютне і при копіюванні воно не коригується. У результаті обчислене значення дорівнюватиме 25,55.
Функції використовуються для спрощення дії над значеннями, що входять до формули. Є кілька категорій функцій, але ми будемо використовувати тільки математичні і логічні. Наприклад, уже знайому нам формулу можна записати з використанням математичної функції =ПРОИЗВЕД(F14;G14)/100. Як бачите, у цьому випадку посилання на комірки використовуються як аргументи функції. У деяких випадках функція може використовуватися як аргумент іншої функції. Цей прийом ми будемо застосовувати при автоматизації розрахунку індексації зарплати.
Логічні функції дозволяють здійснювати обчислення при виконанні певних умов. Тому що при розрахунку індексації заробітної плати теж необхідно враховувати багато параметрів, які перебувають у певній залежності один від одного, то ми будемо активно використовувати логічні функції. Усього в електронних таблицях Excel є 6 таких функцій, нам буде необхідно для роботи тільки три: ЕСЛИ, ИЛИ, И. Проілюструємо особливість використання логічної функції на прикладі.
Функція ЕСЛИ. Припустимо, необхідно визначити розмір зарплати, що буде індексуватися, за умови, що величина прожиткового мінімуму дорівнює 483 грн. Відомо, що величина прожиткового мінімуму не постійна і періодично зростає, у той же час і розмір зарплати може змінюватися. Цю динамічну залежність для конкретного періоду «січень 2006» (див. малюнок на с. 29) можна відобразити формулою, записаною до комірки F5: =ЕСЛИ(D5<E5;D5;E5). Цей запис означає що: перша умова - якщо значення в комірці D5 (прожитковий мінімум) менше значення в комірці E5 (нарахованої зарплати), тоді в комірці F5 буде відображено значення комірки D5 (тобто зарплата, що індексується, дорівнюватиме величині прожиткового мінімуму). Якщо перша умова не виконується (тобто прожитковий мінімум більше або дорівнює нарахованій зарплаті), то набирає сили друга умова - в комірці F5 буде відображено значення комірки E5 (тобто розмір зарплати, що індексується, дорівнюватиме сумі нарахованої зарплати). Саме другу умову і виконано для періоду «Січень 2006». А 1 лютого виконано першу умову, та при розрахунку розміру зарплати, що індексується та обчислюється в комірці F6 формулою =ЕСЛИ(D6<E6;D6;E6), в комірці F6 буде відображено значення комірки D6.
Функція ИЛИ. Визначаючи місяць, з якого почнеться обчислення порога індексу споживчих цін, необхідно виконати одне з двох умов: вирахування порога індексації починається або з місяця, наступного за тим, в якому було підвищено зарплату, або з місяця, наступного за тим, в якому поріг інфляції наростаючим підсумком перевищив 101%. Ця умова саме і вимагає використання функції ИЛИ. Тільки в цьому випадку вона буде вкладена як аргумент до функції ЕСЛИ. Умовно цей приклад можна відобразити формулою:
=ЕСЛИ(ИЛИ(E4>E3;C4>101);B5.
Функція И. Ця функція, як і функція ИЛИ оперує одночасно кількома умовами. Однак на відміну від функції ИЛИ, в якої для одержання результату має бути виконано лише одне з використовуваних умов, для функції И необхідно одночасно виконати всі використовувані у функції умови.
Оператори. Ці символи використовуються у формулах, щоб задати тип обчислення. Є арифметичні оператори (наприклад «*», що позначає «помножити»), оператори порівняння (наприклад, «>», що позначає «більше») а є оператори посилання, які поєднують кілька комірок в одне посилання. Наприклад, щоб підбити діапазон комірок від Е4 до Е12 застосовується оператор «:» (двокрапка) і формула буде такою: =СУМ(Е4:Е12). Якщо необхідно знайти суму тільки цих двох комірок, треба використовувати оператор посилання «;» (крапка з комою), тоді формула буде мати вигляд: =СУМ(Е4;Е12).
Константи. Це готові значення, які не потрібно обчислювати за допомогою формул. Наприклад, дата 01.07.2007, число 525, текст «Нарахована зарплата»» є константами.
Деякі особливості використання формул
1. Ім’я функції завжди пишеться ПРОПИСНИМИ буквами, використовуючи російську або англійську розкладку клавіатури.
2. Формули обчислюють значення в певному порядку, з урахуванням пріоритету операторів (спочатку множення ділення, потім додавання віднімання).
3. Можна змінити порядок обчислення, якщо помістити частину формули в круглі дужки, наприклад, =(100+G14)*C13.
4. Функції можна використовувати як аргументи іншої функції, але не більше 7 рівнів вкладення. У формулі =ЕСЛИ(ИЛИ(E4>E3;C4>101);B5 функція ИЛИ є функцією 2го рівня. Функція, вкладена як аргумент до функції ИЛИ, буде функцією 3го рівня і так далі.
5. Для спрощення запису формул можна використовувати каталог функцій (меню Вставка - Функція).
Будуємо таблицю
Навіть тим, хто ніколи не працював з електронними таблицями, отриманої в цій статті інформації буде досить, щоб побудувати таблицю індексації та автоматизувати її формулами.
Відкрийте нову книгу (так в Excel називається робочий документ). Рекомендуємо відразу ж зберегти її на комп’ютері в зручній папці під ім’ям Індексація зарплати (у процесі роботи над таблицею регулярно здійснюйте операцію збереження). До рядка номер 1 послідовно, стовпчик за стовпчиком, введіть назви граф таблиці, як показано на малюнку. Зверніть увагу, що графа «Індекс споживчих цін» має дві підграфи, назви яких необхідно ввести відповідно до комірок В2 і З2. Як бачите, за рахунок цих підграф шапка таблиці буде займати два рядки. Щоб зробити таблицю такою, що краще читається, протягніть по комірках А1 та А2 курсор при натиснутій лівій клавіші миші і клацніть правою клавішею. У контекстному меню виберіть рядок «Формат ячеек», а у вікні, що відкрилося, увімкніть опцію «Объединение ячеек» і натисніть ОК. Таку саму операцію проведіть і з комірками стовпчиків D, E, F, G, H, I.
До робочих таблиць входять усі графи і збережені їхні назви, наведені в Додатку 1 до Порядку проведення індексації грошових доходів населення, затвердженого Постановою КМУ №1078 від 17.07.03. Це дозволить вам перевірити правильність запропонованого алгоритму автоматизації.
У кожному рядку таблиці розміщені вихідні дані, що відносяться до конкретного місяця. Однак це не означає, що для розрахунку величини індексації зарплати, наприклад, за «Травень 2006» досить буде даних з комірок 9го рядка. Для цього буде необхідно проаналізувати і врахувати дані за кілька попередніх місяців, розташованих у більш ніж 10 комірках (на малюнку вони виділені сірим фоном).
Однак вам не доведеться цим займатися, всі розрахунки автоматично зробить Excel, лише попередньо необхідно буде ввести формули. У нашій таблиці з дев’яти граф, що до неї входять, лише в комірках шести граф будуть застосовані формули для автоматизації розрахунку індексації. Вони будуть універсальні, тобто досить ввести до верхньої комірки графи одну формулу і скопіювати її в нижні комірки стовпчика.
Почнемо з першої графи «Період», в комірках якої буде записаний числовий параметр дати, що відповідає місяцю, для якого розраховується індекс інфляції. Таблицею, яку ми будуємо, можна буде користуватися, починаючи з будьякого місяця. Більше того, ми плануємо показати вам спосіб ув’язування таблиці індексації з таблицею обліку заробітної плати. Тому заповненню цієї графи приділимо деяку увагу.
Всі дати в Excel’і є числами, з якими можна здійснювати математичні обчислення. Тому для автоматичного заповнення комірок графи «Період» досить до першої комірки графи (А3) ввести будьяку дату, а потім додавати до неї по одному місяцю. Таку операцію може виконувати формула =ДАТАМЕС(A3;1), яку необхідно ввести до комірки А4 і скопіювати її до нижніх комірок. До них будуть записані послідовно дати, що відстоять одна від одної рівно на 1 місяць.
Якщо функція ДАТАМЕС недоступна або після її введення повертається помилка #ИМЯ?, завантажте надбудову «Пакет анализа» (меню СервисНадстройки).
Для більшої наочності ви можете змінити подання дати формату «01.01.2006 р.» на формат, наприклад, «Січень 2006». Для цього виділіть комірку й у вікні «Формат ячейки» (меню Формат) виберіть будьякий формат дати.
Наступний параметр, який розраховується за допомогою формули, - це «величина індексу споживчих цін для порога індексації» (далі - ИСЦП, формулювання відповідно до Постанови КМУ № 1078).
Перша формула, яку необхідно записати до комірки С5, має такий вигляд
=ЕСЛИ(ИЛИ(СУМ(E$5:E5)=0;E5>МАКС(E$4:E4));0;
ЕСЛИ(ИЛИ(E4>МАКС(E$3:E3);C4>101);B5;B5*C4/100))
Важливо! У формулі не має бути пробілів!
Ця формула є універсальною, тобто при копіюванні її структура залишиться незмінною для будьякої комірки цієї графи. Щоб вона автоматизувала розрахунок величини порога індексації за будьякий місяць, її досить скопіювати до нижніх комірок. При цьому відносні посилання на рядки в цій формулі відповідно змінять свої значення.
Давайте розберемо структуру формули. У ній використано дві умови:
1. ЕСЛИ(ИЛИ(СУМ(E$5:E5)=0;E5>МАКС(E$4:E4));0
Виконанням цієї умови визначається базовий місяць розрахунку індексації. В комірку для такого місяця буде повернуте значення «0». Тому що базовим стає місяць при виконанні однієї з двох умов, для одержання такого значення у формулі використовується оператор ИЛИ. Фактично цю частину формули можна представити у вигляді двох умов:
ЕСЛИ(СУМ(E$5:E5)=0;0) і ЕСЛИ(E5>МАКС(E$4:E4);0).
Ось що вони означають:
- СУМ(E$5:E5)=0 дає можливість установлювати дату першого нарахування зарплати з будьякого місяця року. Зрозуміло, що до першого нарахування ніяких нарахувань не мало бути. Щоб у цьому впевнитися, ми повинні перевірити, що сума всіх попередніх нарахувань дорівнювала 0. Саме це і забезпечує ця функція.
Як бачите, тут ми застосували особливу конструкцію E$5:E5, що дозволяє автоматично збільшувати діапазон комірок, які сумуються, залежно від того, до якого місяця відноситься формула. Початком діапазону завжди буде залишатися комірка E5 (тому що це посилання абсолютне), і при копіюванні формули посилання в ній завжди буде на цю комірку. Кінець діапазону буде змінюватися залежно від того, у якій комірці знаходиться формула. Наприклад, для комірки С8 буде підрахована сума для діапазону Е5:Е8.
E5>МАКС(E$4:E4) - умова, що визначає місяць, в якому підвищена зарплата. Якби ця умова мала вигляд E5>E4, то вона б не враховувала випадків, коли за поточний місяць нарахована зарплата була б менше, ніж за попередній місяць (враховано лікарняний, відпустку за свій рахунок і т.д.). В результаті наступний місяць, коли зарплата буде нарахована в повному обсязі, формула визначила б як базовий. А це було б помилкою. Тому в цьому випадку використана функція МАКС, що вибирає максимальне значення із зарплат, нарахованих у попередні періоди. Вона дозволить не врахо вувати тимчасові зниження в нарахуваннях зарплати.
2. ЕСЛИ(ИЛИ(E4>МАКС(E$3:E3));C4>101);B5
Ця частина формули визначає місяць, з якого почнеться обчислення порога індексу споживчих цін (у цьому місяці значення ІСЦП буде прирівняно до значення індексу інфляції згідно даних Держкомстату). При цьому до комірки (наприклад, С5) буде записано значення сусідньої комірки В5. Це досягається виконанням однієї з двох умов (для цього теж використовується оператор ИЛИ):
- E4>МАКС(E$3:E3) виконує умову, що вирахування порога індексації починається з місяця, наступного за тим, в якому було підвищено зарплату. Наприклад, якщо зарплата, записана до комірки E10, більше зарплати, зазначеної в комірці Е9, то в комірці С8 буде автоматично записано значення, що дорівнює значенню комірки В8 (див. малюнок на с. 30).
- C4>101 виконує основну умову проведення індексації - поріг індексації в попередньому місяці, обумовлений наростаючим підсумком, перевищив 101%. Наприклад, у лютому 2006 р. (комірка С6) поріг індексації, обчислюваний для цього співробітника, склав 101,8%. Отже, в комірці С7 буде автоматично записано значення, що дорівнює значенню комірки В7 (див. ма люнок на с. 30).
3. B5*C4/100
Індекс споживчих цін для визначення порога індексації обчислюється шляхом множення місячних індексів споживчих цін. До комірки буде записано це значення у випадку, якщо не виконується жодна з попередніх умов. Це означає, що до комірки буде записано значення індексу інфляції, розраховане наростаючим підсумком.
Компенсація несвоєчасно виплаченої заробітної плати Практика І індексація зарплати, і компенсація її несвоєчасної виплати по суті покликані виконувати ту саму функцію — згладжувати вплив інфляції на рівень доходів працівників (зокрема, заробітної плати, що нарахована та підлягає до видачі). Відрізняються вони.....
Не у відпустку — у відрядження — на конференцію за кордон № 22 (2.6.2008) :: Практика З роками популярність фірмових виїздів за кордон для проведення нарад, семінарів, корпоративних заходів зростає. Розглянемо, яким чином оформити зв’язок витрат на подібні заходи, забезпечивши їх «валововитратність». Службове відрядження – це поїздк...
Доставка товару покупцям № 22 (2.6.2008) :: Практика Товар, придбаний покупцем, може бути вивезений ним самостійно або доставлений постачальником. Доставка товару покупцеві може здійснюватися як власним транспортом постачальника, так і за допомогою сторонніх транспортних організацій. При цьому вартість...
