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

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

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

Виділення і об'єднання комірок

Необхідність в об'єднанні комірок може виникнути, наприклад, якщо ви будуєте таблицю, в якій кілька стовпчиків розміщені під однією шапкою (мал. 1).

Наприклад, назва шапки: "Нарахування", а під нею розташовані стовпчики з назвами "Збір до Пенсійного фонду", "Прибутковий податок" і т. д.

У таблиці, яку ви створюєте, в комірку D1 був введений текст "Розрахункова відомість заробітної плати", який розташувався поверх сусідніх комірок E1, F1, G1. Розглянемо процес об'єднання комірок, з'єднавши комірки D1, E1, F1 і G1. Для цього виділіть зазначені комірки, натиснувши ЛКМ на комірці D1 (курсор - у вигляді білого хрестика), і, не відпускаючи ЛКМ, потягніть курсор до комірки G1. При цьому комірки E1, F1 і G1 будуть забарвлені в чорний колір. Увійдіть у меню "Формат", виберіть опцію "Ячейки" і вкладку "Выравнивание". У вкладці "Выравнивание" навпроти рядка "Объединение ячеек" розташоване віконце. Активізуйте його, клацнувши на ньому ЛКМ (поява "галочки"), і натисніть кнопку "ОК". Внаслідок цих дій вибрані вами комірки будуть об'єднані в одну (мал. 2).

Аналогічно можна об'єднати й інші елементи таблиці, наприклад, у рядках 17 і 18.

Увага! Щоб виділити кілька окремо розташованих комірок, клацайте ЛКМ на потрібних комірках з натиснутою клавішею Ctrl. Над виділеними таким чином комірками можна одночасно здійснювати операції форматування як з однією коміркою.

Форматування таблиці. Контекстне меню

Для того щоб ваша відомість набрала вигляду, показаного в першому уроці на мал. 2, ви повинні на комірки А3:Р16 накласти сітку і відформатувати її.

Виділіть масив А3:Р16 і натисніть кнопку в головному меню (мал. 3).

У вікні, що відкрилося, виберіть спосіб обрамлення комірок, наприклад кнопку .

В результаті на вибрані вами комірки як всередині їх, так і по периметру буде накладена сітка.

Якщо в головному меню вашої програми не встановлена ця кнопка, накласти сітку на вибрані комірки також можна, якщо, увійшовши в меню "Формат", вибрати опцію "Ячейки", а в ній вкладку "Граница".

Як видно на мал. 4, в цій вкладці ви можете вибрати тип лінії сітки, її колір, а також розташування рамки. Встановивши всі прийнятні для вас параметри обрамлення комірок, натисніть клавішу "ОК", яка розташована в цій же вкладці.

Отже, після накладення сітки на вибрані вами комірки вони набрали вигляду справжньої таблиці.

Програма Excel дозволяє отримувати доступ до різних сервісних функцій за допомогою контекстного меню, яке викликається натисненням ПКМ. Якщо покажчик мишки (білий хрестик) розташований на робочому аркуші, то після натиснення ПКМ на екрані будуть показані команди, які використовуються для обробки комірки (мал. 5).

Ергономічність вашої таблиці можна підвищити, забарвивши певні ділянки робочого аркуша й області таблиці в кольори, які вам найбільше подобаються. Для прикладу забарвимо комірки F5:I15 в жовтий колір. Для цього виділіть масив F5:I15, встановивши курсор в комірку F5. Клацніть ЛКМ (комірка отримає обрамлення у вигляді подвійної рамки) і, утримуючи ЛКМ натиснутою, потягніть її до комірки I15. Потім у контекстному меню виберіть рядок "Формат ячеек", а у вікні, що з'явилося, - закладку "Вид" (мал. 4). Виберіть будь-який колір, що вам сподобався, для залиття комірок, виділивши його на палітрі, і підтвердьте вибір натисненням кнопки "ОК".

Можна скористатися й іншими варіантами форматування, з якими ви ознайомитеся у процесі роботи з програмою.

Увага! Значно легше виконувати форматування таблиці після введення всіх даних, маючи перед очима вже складену відомість.

Формат комірок

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

Виділіть масив D5:P16, увійдіть у меню "Формат", активізуйте вкладку "Число" в опції "Ячейки". У цій вкладці ви можете встановити потрібний вам формат комірок (мал. 6).

У нашому прикладі треба вибрати формат "Числовой", встановити кількість десяткових знаків і натиснути кнопку "ОК". Якщо в цих комірках встановлений такий формат, усі дані в них будуть з двома десятковими знаками після коми. Оскільки в комірках С5:С15 і Е5:Е15 значення повинні бути вказані без десяткових знаків, при визначенні для них формату "Числовой" у віконці рядка "Число десятичных знаков" встановіть 0. Не забудьте заздалегідь виділити на робочому аркуші ці комірки. Для комірок В5:У15 встановіть формат "Текстовой" і натисніть кнопку "ОК". Після того як ви встановите необхідний формат для всіх елементів таблиці, можна розпочинати введення даних.

Створення платіжної відомості

Після проведеного форматування ваша таблиця має набрати вигляду, показаного на мал.7. Використовуючи готову структуру відомості із заробітної плати, розглянемо практичний приклад з розрахунку заробітної плати на невеликому підприємстві.

Розрахунок основних показників у платіжній відомості. Введення даних у комірки

Складання відомості із заробітної плати почнемо з аркуша "ЗП лютий". Не забудьте, що аркуші "ЗП лютий" і "Аванс лютий" згруповані (якщо цього не зроблено, згрупуйте їх відомим вам способом), тому дані, що вводяться, будуть копіюватися і в аркуш "Аванс лютий".

Для введення даних в стовпчик 2 встановіть курсор в комірку В5 (мал.8), натисніть клавішу F2 (у вибраній комірці з'явиться блимаючий курсор), введіть прізвище й ініціали працівника підприємства і натисніть кнопку Enter.

Підтвердити введення даних у комірку можна, клацнувши ЛКМ у рядку формул на кнопці . Помилкове введення даних можна скасувати, клацнувши на кнопці .

Дані в комірки В6:У15 вводяться аналогічно.

На підставі даних, наведених на мал.2 попереднього уроку, заповніть стовпчик 3 "Загальний стаж" і стовпчик 4 "Оклад" (D5:D15).

Вбудована в програму Excel можливість автоматизувати обчислення вже на цьому етапі дозволить вам пересвідчитися в простоті цього процесу. Виділіть уже відомим вам способом комірки з D5 до D15 включно і клацніть ЛКМ кнопку суми в панелі інструментів. Внаслідок таких дій в комірці D16 з'явиться сума окладів всіх працівників. Якщо клацнути ЛКМ на цій комірці, то в рядку формул ви побачите "=СУММ(D5:D15)".

Увага! Визначити суму кількох комірок, не вводячи результату до відповідної комірки, можна й іншим способом. Виділіть комірки, які треба просумувати, наприклад Е5:Е15, і в інформаційному рядку, який розташований у нижній частині вікна програми, з'явиться: "сумма=212".

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

Основні поняття і вимоги щодо введення формул. Використання функцій

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

Формула може включати в собі функції і оператори (інструкції, вживані для певних задач), посилання на комірки (абсолютні, відносні), значення (текстові або числові).

Математичні дії в формулі відбиваються за допомогою відомих вам операторів (+, -, *, /).

В Excel використовуються три адресні оператори: двокрапка, кома, пробіл.

Двокрапка ":" означає діапазон комірок. Наприклад, вираз "D5:D15" посилається на комірки з D5 по D15 включно. Кома "," посилається на окрему комірку (вираз "D5, D15" посилається тільки на дві комірки - D5 і D15). Пробіл вказує на комірки, розташовані в проміжку між двома групами комірок (наприклад, вираз "=СУММ(А1:А6 А4:А10)" просумує значення, вміщені в комірки А4, А5, А6).

Увага! Формула завжди має починатися зі знака "=". Якщо знака рівності "=" на початку формули немає, програма сприймає формулу як текст і обчислення не проводить.

Знак ";" звичайно застосовується під час введення умовних операторів (функції "Если" і т.п.) і означає розділення умови, яка буде виконуватися при обчисленні формули.

Наприклад, у комірку L5 (мал. 9) введена формула "=ЕСЛИ(J5<=150;J5*0,01;ЕСЛИ(J5>150;(J5)*0,02))". Ця формула в нашому прикладі використовується для розрахунку суми збору в Пенсійний фонд і, відповідно, означає:

- якщо сума в комірці J5 менша або дорівнює 150 грн, то для визначення збору в ПФ використовується ставка податку 1% (фрагмент формули "J5*0,01;");

- якщо перша умова не відповідає введеним в комірку J5 даним, то виконується друга умова, тобто якщо сума в комірці J5 більша за 150 грн, то вміст комірки J5 множиться на 0,02.

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

Для того щоб заповнити в таблиці комірки J5:J15, необхідно підсумовувати дані в комірках F, G, H, I.

Встановіть курсор у комірку J5, введіть знак "=", потім установіть курсор у комірку F5 (формула набере вигляду "=F5"), натисніть кнопку "+",

установіть курсор в комірку G5, а потім аналогічно послідовно підсумовуйте комірки H5 і I5 і натисніть кнопку Enter.

Формула в комірці J5 після зазначених дій набере такого вигляду: "=F5+G5+H5+I7".

Більш поширений спосіб введення формул у комірку - використання функції.

Програма Excel надає у ваше розпорядження безліч спеціальних функцій, в які формули вже введені. Спосіб завдання функцій завжди один і той же. Відмінність - тільки в кількості аргументів, що вводяться.

Наведені вище дії простіше виконати, ввівши в комірку J5 функцію "=СУММ" і без пробілу додавши до неї аргумент, вказаний в круглих дужках - (F5:I7). Результат буде той самий.

Формули, що містять функції, можна копіювати так само, як і прості формули (типу А5+1). Тому для введення формули "=СУММ(F5:I7)" в інші комірки стовпчика J використайте її копіювання.

Абсолютна адресація

На робочих аркушах "Аванс лютий" і "ЗП лютий" сума, нарахована за окладом кожному працівнику, буде відображена у відповідній комірці стовпчика 6 (F5:F15). Так, наприклад, сума нарахованого авансу Кротову Ю.В. буде відображена в комірці F5 відповідного аркуша і за умовами нашого прикладу розрахована за формулою "=D5*E5/$J$2".

У цій формулі використано знак долара "$", який означає ознаку абсолютної адресації комірок. Зазвичай потрібно, щоб формула змінювала свій вигляд під час копіювання, але якщо зміна формули небажана, застосовують знак "$" для того, щоб змінити відносне посилання, встановлене за умовчанням, на абсолютне.

У нашому прикладі комірка J2 (кількість робочих днів у місяці) є абсолютним посиланням, оскільки вона в незмінному вигляді використовується для розрахунку показників стовпчика 6 таблиці.

Отже, в комірку F5 введена формула "=D5*E5/$J$2" (сума нарахованого авансу Кротову Ю.В.). У комірку F6, відповідно, введена формула "=D6*E6/$J$2" (сума нарахованого авансу ЛістьєвуА.Ф.). Обидві зазначені формули мають загальний елемент "$J$2" або посилаються на комірку J2.

Якби в цьому випадку комірка J2 не мала абсолютної адресації, то під час копіювання формули з комірки F5 в комірку F6 елемент формули J2 зміниться, відповідно, на J3, внаслідок чого, замість очікуваного числового результату, в комірці F6 з'явиться напис "ІЗНАЧ!". Це означає, що відсутні цифрові дані для формули, оскільки комірка J3 має текстовий формат і становить назву стовпчика 10 ("Усього нараховано").

Для введення даних в інші комірки стовпчика скористайтеся відомим методом копіювання (перетягання комірки за її правий нижній кут).

Під час заповнення стовпчика 6 на аркуші "Аванс лютого" треба мати на увазі, що на кожному підприємстві є свій порядок нарахування авансу і його видачі. Наприклад, кожному працівникові може бути встановлено індивідуальний відсоток від його посадового окладу для нарахування авансу або сума нарахованого авансу може бути жорстко пов'язана з кількістю відпрацьованих працівником робочих днів. Зазначені умови вимагають дещо видозмінити формулу, вказану в комірці F5 (у цьому випадку не забудьте розгрупувати робочі аркуші).

Припустімо, сума авансу працівника Гетьман Н.А. нараховується виходячи з 35% її посадового окладу, отже, формула в комірці F8 буде мати такий вигляд: "= D8*0,35". У випадку якщо сума нарахованого авансу має жорстку прив'язку до кількості відпрацьованих днів, в комірку F8 буде введена формула "= D8/$J$2*E8". Підсумок нарахованих сум можна також підрахувати, використовуючи функцію підсумовування.

Увага! Перед тим як в стовпчик 5 ввести кількість фактично відпрацьованих кожним працівником підприємства робочих днів на дату виплати авансу (E5:E15), розгрупуйте аркуши робочої книги. В іншому разі в авансовій і в підсумковій відомостях за місяць число відпрацьованих днів буде однаковим.

У разі якщо для підсумовування вибраних вами елементів неможливо або недоцільно використати зазначені раніше способи, то бажаний результат можна отримати іншим методом. Розглянемо його на простому прикладі. Нехай необхідно дізнатися питому вагу суми, нарахованої за лікарняним листом (комірка Н12), у загальній сумі нарахованого фонду оплати праці (J16). Для цього можна використати формулу "=Н12/ J16*100" (мал.10).

Активізуйте комірку, в якій хочете отримати результат (наприклад, D2). У рядку формул клацніть ЛКМ на знаку "дорівнює", потім ЛКМ клацніть на комірці Н12 (її адреса автоматично з'явиться в формулі, що вводиться), введіть знак ділення "/", клацніть на комірці J12 і виконайте інші арифметичні дії.

Запам'ятайте! Арифметичні дії у формулах можна робити, якщо вміст комірки має числове значення. Для встановлення формату комірки скористайтеся розглянутим вище методом.


Початкові дані

  • На підприємстві працює 11 осіб.
  • Працівники Кротов Ю.В., Загреба О.Ф., Гетьман Н.А., Лісовський С.А., Рябокінь Д.В., Петренко І.В. працюють на підприємстві за сумісництвом.
  • Розмір посадового окладу і кількість фактично відпрацьованих працівником підприємства днів вказані на мал.2 уроку 1.
  • Працівникам Гетьман Н.А. і Трохимову І.В. виплачена компенсація за невикористану відпустку, відповідно 269,07 і 123,07 гривень.
  • Стріха В.О. був на лікарняному протягом 3 днів - з 2 по 4 лютого. Оклад Стріхи В.О. в грудні 1999 р. і січні 2000 р. встановлений у розмірі 700 грн.
  • Між підприємством і працівником Загребою О.Ф. укладено договір оренди легкового автомобіля. За умовами цього договору підприємство зобов'язане щомісяця протягом його дії виплачувати ЗагребіО.Ф. орендну плату 1500 грн.
  • Кількість робочих днів у грудні 1999 року - 20, а в січні 2000 року - 19.

Наступний урок

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