Ведення бухгалтерського обліку в Excel, частина третя

Розглянемо, як можна побудувати оборотну відомість на основі інформації з шахматки, описаної в попередній частині статті, але спочатку додамо опис плану рахунків, щоб використовувати назви рахунків в оборотній відомості. Перейменуйте залишився порожнім лист в План рахунків і перейдіть на нього. Введіть заголовок План рахунків і заголовки стовпців Рахунок і Найменування. Клацніть правою кнопкою миші на заголовку стовпця A і виберете в допоміжному меню команду Формат клітинок. Відкриється діалог налаштування формату, в якому ви повинні перейти на вкладку Число. У списку виберіть Текстовий і натисніть кнопку OK. Тепер числа в першому стовпці будуть сприйматися як текст, і ви зможете ввести значення 01, 02 та аналогічні. Заповніть план рахунків, ввівши номери і опису використовуваних рахунків. Субрахунки можна не вводити.
Далі можна приступати до створення оборотної відомості. Додайте лист книгу і назвіть його Оборотна відомість. Введіть у першій рядку заголовок відомості, після чого слід ввести заголовки стовпців. В комірку A3 введіть заголовок Рахунок, а в клітинку B3 – Найменування рахунку. Об’єднайте комірки С2 і D2, введіть в них назва Сальдо на початок періоду. Аналогічно об’єднайте попарно наступні чотири стовпця введіть назви Обороти за період та Сальдо на кінець періоду. У третьому рядку введіть в стовпцях C, E і G назва Дебет, а в стовпці D, F і H введіть назву Кредит. Заголовок таблиці сформований, і можна приступати до введення формул. Слід ввести всього один рядок, після чого розмножити її засобом автозаповнення. Отже, заповнимо осередку четвертого рядка таблиці.
Введіть у комірку A4 формулу =ТЕКСТ(Шахматка!A6;”00″). В комірку буде вставлено номер рахунку з першого рядка шахматки, причому номер буде виглядати як 01, 02 і так далі, а не 1 або 2. Щоб за номером рахунку визначити його найменування, у комірку В2 введіть формулу =ВПР(A4;’План рахунків’!$A$3:$B$63;2). Взявши номер рахунку з комірки А4, Excel знайде на аркуші з планом рахунків список, що починається в комірці A3 і закінчується в комірці B63. Якщо у вашому плані рахунків розмір списку іншої, поставте потрібне число з номером останньої рядки у списку замість 63, наприклад $B$55 або $B$101. У виявленому списку Excel знайде рядок, в першому стовпці якої заданий номер рахунку, і видасть значення з другої комірки таблиці, тобто назву рахунку. Слід пам’ятати, що для правильної роботи план рахунків повинен бути відсортований в порядку зростання номерів. Якщо ви вводили послідовно всі рахунки, він так і буде відсортовано. До речі, якщо при додаванні допоміжних проводок в журнал операцій, в яких вказано однаковий рахунок по дебету і кредиту, ви описали не всі рахунки, зазначені в плані рахунків, оборотна відомість буде розраховуватися невірно. Далі слід заповнити розрахункові комірки оборотній відомості. У стовпцях Дебет і Кредит, об’єднаних заголовком Сальдо на початок періоду, нічого вводити не треба, так як початкове сальдо буде надалі вводитися вручну.
В комірку Е4 введемо формулу для розрахунку дебетових оборотів за період =ВПР(VALUE(A4);Шахматка!$A$6:$AA$28;27). Конкретні значення у формулу залежать від кількості використовуваних вами рахунків. Взявши з поля A4 значення номера рахунку, Excel шукає його в першому стовпці зведеної таблиці, створеної раніше шахматки. Зведена таблиця у нас розташовується до комірки $AA$28. У цій клітинці відображаються дебетові обороти по рахунку з найбільшим номером. Можливо, вам буде потрібно ввести інший адресу клітинки, наприклад, $Y$26. Будьте уважні, так як правильне зазначення адреси важливо для виконання вірних обчислень. Останнє число у формулі вказує на номер стовпця в списку, з якого слід взяти потрібне значення. Якщо ви все робили правильно, зведена таблиця повинна містити однакову кількість рядків та стовпців, і дане число на одиницю менше номери останньої використовуваної рядка. В нашому випадку, при посиланні $AA$28, число дорівнює 27. Якщо у вас вийшла посилання $Y$26, то слід вказати число 25. Якщо після введення формули ви побачите в клітинці замість числа текст #Н/Д #VALUE! або #REF!, то ви неправильно вказали параметри. Виправте формулу у відповідності з вашими конкретними умовами.
Далі слід ввести кредитові обороти. Для цього в комірку F4 введіть формулу =ГПР(VALUE(A4);Шахматка!$C$4:$Y$30;27). HLOOKUP працює майже так само, як і ВВР, але шукає не по рядках, а по стовпцях. На відміну від розрахунку дебетових оборотів, в цій формулі шукається стовпець у зведеній таблиці, у першій рядку якого вказаний потрібний рахунок. В якості результату підставляється значення з останнього рядка зведеної таблиці. Зверніть увагу, що в якості списку виділена інша частина зведеної таблиці. При розрахунку дебіту з зведеної таблиці не були взяті перші два рядки, а тепер не приймаються в розрахунок перші два стовпці. Крім того, останній осередком вважається не та, в якій відображаються дебетові обороти по рахунку з найбільшим номером, а клітинка, в якій відображаються кредитові обороти по даному рахунку. Якщо в першому випадку ви підставили у формулу значення $Y$26, то тепер слід підставити значення $W$28. Останнє число в формулі те ж, що і в попередній формулі.
Найбільш складна частина оборотній відомості описана. Ви ввели формули для розрахунку оборотів за дебетом і кредитом. Якщо хочете, можете виділити комірки Е4 і F4, після чого задати формат для виведення цілих чисел, щоб в оборотній відомості не виводилися копійки. Далі слід ввести формули для розрахунку кінцевого сальдо. Введіть у комірку G4 формулу =C4+E4-D4-F4, а в комірку H4 формулу =D4+F4-C4-E4. В результаті в комірках відображаються однакові числа, але з різними знаками. Щоб не відображати нульові та від’ємні суми, виділіть комірку G4 і H4, після чого виберіть команду меню Формат – Осередки. У діалозі перейдіть на вкладку Число і виберіть у списку Числові формати елемент (всі формати). У полі Тип введіть шаблон формату 0;;. Нуль означає, що позитивне число повинне виводитися як ціле число. Два знака точки з коми, після яких нічого не вказано, означають, що нульові та від’ємні значення не відображаються. Натисніть кнопку OK, і діалог закриється. Тепер з двох сум відображається тільки позитивна. Якщо ця сума у стовпчику дебету, то в стовпці кредиту нічого не відображається, і навпаки.
Ми ввели формули для одного рядка, а тепер слід розмножувати їх. Виділіть клітинки з A4 по H4, підведіть покажчик миші до маркера заповнення, розташованому в правому нижньому куті області виділення, після чого натисніть кнопку миші та перемістіть покажчик вниз на кілька рядків. Слід заповнити стільки рядків, скільки рахунків використовується у вашому плані рахунків. Якщо в нижніх рядках в комірках з’явилися повідомлення #Н/Д #VALUE! або #REF!, ви заповнили занадто багато осередків, і зайві рядки слід очистити.
Нам залишилося тільки ввести формули для підрахунку сум по стовпцях. Введіть у клітинку на перетині стовпця C і рядка, розташованої через одну за останнім рядком таблиці, вираз =СУМ(C4:C26). Необхідно відзначити, що замість C26 ви повинні підставити номер останнього рядка у вашій таблиці, наприклад C24. Перетягнувши маркер заповнення вправо, заповніть клітинки в трьох наступних стовпцях. Суми початкового сальдо та оборотам розраховані.
Для розрахунку сум на кінець періоду слід ввести більш складну формулу. Як ви пам’ятаєте, ми з допомогою форматування чисел не відображаємо від’ємні суми, однак при розрахунках від’ємні числа клітинок також будуть підсумовуватися. Тому в клітинку суми в стовпці G слід ввести формулу =SUMIF(G4:G26;”>0″). Так само, як і в попередній формулі, вам слід змінити значення G26 на адресу останньої комірки в стовпці. У цій формулі вказується, що слід підсумовувати тільки позитивні значення клітинок. З допомогою автозаповнення введете аналогічну формулу в стовпець H. Тепер залишилося ввести початкові залишки, і оборотна відомість готова.
Ви можете змінити проводки в журналі операцій або ввести нові операції. Після цього слід перейти на лист Шахматка, клацнути правою кнопкою миші на зведеної таблиці і вибрати команду меню Оновити дані. Зведена таблиця побудована заново, при цьому автоматично буде перерахована оборотна відомість. На основі інформації з оборотної відомості можна побудувати безліч корисних звітів, але ми не будемо цього робити. Ви можете самостійно створити стандартні бланки в Excel, скориставшись прийомами, описаними в попередньому розділі, і додати посилання на потрібні комірки оборотній відомості.
При посиланні на комірки оборотній відомості зовсім не обов’язково поміщати стандартні бланки в ту ж книгу, в якій організований бухгалтерський облік. Excel дозволяє посилатися на комірки таблиці, розташованої в іншому файлі. Наприклад, якщо ви хочете вставити посилання на комірку G4 оборотній відомості, введіть формулу ='[Бухгалтерський учет.xls]Оборотна відомість’!$G$4. зверніть увагу, що для використання в іменах прогалин потрібно помістити назви в одинарні лапки.
При відкритті файлу з посиланнями на клітинки таблиці іншого файлу, на екрані з’явиться діалог з попередженням. Ви можете оновити зв’язку, отримавши останні дані. Для цього потрібно натиснути кнопку Оновити в діалозі. При цьому діалог закриється, а в клітинках з’являться останні дані з бухгалтерського обліку. Якщо ж ви натиснете кнопку Не оновлювати, то після закриття діалогу інформація в комірках залишиться незмінною.
Розглянутий приклад показує, що з допомогою Excel можна автоматизувати різні ділянки бухгалтерського обліку, причому якість автоматизації нічим не гірше, ніж при використанні спеціалізованих бухгалтерських програм. При цьому слід пам’ятати, що можливості Excel значно ширше, і в ньому можна використовувати програми, написані на мові програмування Visual Basic. Однак написання таких програм вимагає певних знань, і краще, щоб цим займався програміст, а не бухгалтер.