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

Автоматизації бухобліку: Excel

Регістри бухгалтерського обліку на малому підприємстві. Урок 20

Володимир ЛАВРЄНОВ


Попередні уроки "Автоматизації бухобліку: Excel"

Гадаю, ви пересвідчилися, що Журнал операцій, виконаний з допомогою Excel, цілком може бути базою для ведення аналітичного обліку на підприємстві (див. попередній урок у "ДК" №46/2001). Використовуючи спеціальний модуль, ви легко отримаєте згруповані дані для заповнення багатьох журналів-регістрів.

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

Зведена таблиця - універсальний регістр бухгалтерського обліку

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

Рисунок 1

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

Створення Зведеної таблиці

Основою для створення ЗТ може бути будь-який список, що підсумовує групи даних. А ми використовуємо Журнал операцій, перед тим дещо змінивши його. У бухгалтерському обліку зручно узагальнювати й аналізувати дані, пов'язані належністю до однієї операції, одного договору тощо. Тому для створення такого "сполучного елемента" додайте в свій Журнал ще одну графу. Для вставки додаткової графи "№ документа" виділіть у таблиці стовпчик "Сума операції" (рис. 2) і в контекстному меню виберіть команду "Добавить ячейки со сдвигом вправо". Якщо у вас був установлений автофільтр, зніміть його перед коригуванням таблиці.

Рисунок 2

Увага! Щоб не порушити роботу модуля аналізу рахунка і посилальну цілісність введених імен, виділіть не весь стовпчик, а тільки ту його частину, яка належить таблиці. Усі додаткові графи, що вставляються у Журнал, повинні розташовуватися перед графою "Сума операції".

У нову графу ви будете записувати номер документа або договору, якого стосується те чи інше проведення. Для зручності рекомендую на окремому аркуші вести список таких документів (рис. 2). Доповнити Журнал операцій можна також і графою, де зазначатиметься належність проведення до податкового обліку. Тоді у Зведеній таблиці ви легко згрупуєте й проаналізуєте правильність ведення податкового обліку.

Створимо Зведену таблицю: крок за кроком.

Крок перший. У меню "Данные" виберіть рядок "Сводная таблица". З'явиться вікно Майстра зведених таблиць (рис. 3). На цьому етапі ви можете задати будь-яке джерело даних, на основі яких створюватиметься ЗТ. У нашому прикладі потрібно вибрати варіант "В списке или базе данных MSExcel". Якщо ви захочете використати несуміжні графи або розміщені на інших аркушах, виберіть перемикач "В нескольких диапазонах консолидации". Тепер можна продовжити, натиснувши кнопку "Далее".

Рисунок 3

Крок другий. Вибираємо діапазон початкових даних (також див. рис. 3). Діапазон можна вказати, ввівши у поле адреси комірок. Однак зручніше скористатися кнопкою і перейти в Журнал операцій. Виділіть у таблиці Журналу, наприклад, комірки С11:К113 (відмініть поєднання комірок у "шапці" таблиці, якщо такі є, див. рис. 2).

Увага! Перший рядок обраного діапазону обов'язково повинен містити назви граф.

Розмір вибраного у відповідному полі Майстра ЗТ діапазону має бути досить великим, щоб забезпечити запис проведень у Журналі, наприклад, протягом кварталу, півроку або року (залежно від кількості здійснюваних на підприємстві операцій). Тоді це дозволить підтримувати ЗТ актуальною протягом усього цього періоду. Враховуйте при цьому, що розміри діапазонів комірок, яким присвоєно на цьому аркуші імена (ДатаОпер, ДебетЖ, КредитЖ, ВсьогоЖ), також повинні бути відповідними. Після вибору діапазону тисніть на кнопку "Далее".

Крок третій. Оскільки розміри ЗТ змінюватимуться залежно від порівнюваних даних і можуть досягати значних розмірів, бажано розмістити ЗТ на новому аркуші. Встановіть перемикач "Новый лист". А тепер - головний етап у створенні ЗТ. Клацніть кнопкою "Макетѕ", щоб, властиво, і відкрити конструктор ЗТ. Для створення повноцінної Зведеної таблиці, яка забезпечить усі можливі комбінації записаних в Журнал даних, досить розташувати кнопки полів, як показано на рисунку 4. Головне - помістіть кнопку "Дебет" на поле "Строка", кнопку "Кредит" - на поле "Столбик", а "Сума операції" - на поле "Данные" (містить підсумкові дані). інші кнопки можна сміливо перетягувати на поле "Сторінка", хоча використовувати всі кнопки для побудови ЗТ не обов'язково (це можна зробити безпосередньо у створеній ЗТ). Під час роботи із ЗТ ви зможете розставити кнопки інакше, щоб отримати ті ж дані, але в іншому розрізі.

Рисунок 4

У цьому ж вікні (рис. 4), натиснувши кнопку "Параметри...", можна встановити певні параметри для ЗТ. У загальному випадку покладіться на встановлення за замовчуванням, однак перевірте в цьому вікні три поля:

цей прапорець повинен бути встановлений, щоб при відкритті файла ЗТ оновлювалася автоматично.

встановлення цього прапорця дасть можливість переглядати більш докладні дані ділянки СТ (при подвійному натисненні на комірку).

встановіть прапорець і залиште праве поле порожнім. інакше в комірках поля "Данные" з нульовими значеннями буде записано "0,00".

На цьому створення зведеної таблиці закінчено і можна натискати кнопку "Готово".

Після цього у книзі Баланс (якщо саме в ній міститься Журнал операцій) з'явиться новий аркуш. Присвойте йому ім'я, наприклад "Шахматка", і відкрийте його.

Як працювати зі Зведеною таблицею

і, справді, створена вами Зведена таблиця дуже схожа на всім відому Шахову відомість, однак можливості її набагато більші порівняно з будь-яким паперовим чи електронним аналогом (рис. 5). Кожне поле ЗТ (крім поля "Даннные") має список, що розкривається, в якому можна вибрати або з якого прибрати один або декілька показників.

Рисунок 5

На рисунку 5 показано, як можна приховати кредитові обороти за рахунками, використаними у Журналі операцій. Підбираючи певні комбінації дебетів і кредитів рахунків, можна побудувати потрібний регістр бухгалтерського обліку. Наприклад, залиште у полі "Кредит" тільки рахунки класу 4 і отримаєте регістр - Журнал №7.

Видозмінювати зведену таблицю і тим самим отримувати потрібний спосіб обробки даних можна різними способами. Найбільш застосовуваний - перетягування "затіненої" кнопки у потрібні рядки і стовпчики Зведеної таблиці.

Припустімо, треба перевірити виконання договору №3, з деталізацією за днями проведень, що стосуються його.

Для цього виберіть у списку, що розкривається з комірки В1, потрібний рядок ("3"). Потім перетягніть кнопку "Дата операції" в зону кнопки "Дебет", так щоб за нею з'явився знак "двотавра" (його зображення див. на рис. 6), і відпустіть її. Зведена таблиця набере вигляду, показаного на рисунку 6. А тепер клацніть двічі на комірці, розташованій у полі "Данные" (наприклад, Е9), і відкриється рядок Журналу операцій, що деталізує кореспонденцію Д-т 205 - К-т 631 за цим договором.

Рисунок 6

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

Наприклад, можна видозмінити Зведену таблицю зі стандартного подання "дебет-кредит" у "кредит-дебет". Таке подання використовується для побудови регістра-відомості Журналу №1 "III. З кредиту рахунка 31 "Рахунки в банках" у дебет рахунків". Отримати таблицю даних для цього регістра дуже просто. У зведеній таблиці поміняйте місцями кнопки "дебет" і "кредит", а в списку, що розгортається (комірка А6, рис. 7) залиште виділеним тільки рахунок 31.

Рисунок 7

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


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

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