Створення діапазону умов для першого звітного періоду
Для виконання вбудованої функції БД СУММ необхідно підготувати умови фільтрації для кожного рахунку, субрахунку. Умова містить імена полів списку (БД Microsoft Excel)-ЖГО. Таким чином , перед кожним рядком на листі Критерії, яка містить суброхунок, необхідно вставити назву полів.
Для виконання вставки заголовку полів створюється макрос , який копіює даний рядок заголовків і вставляє перед черговим рахунком, субрахунком.
Послідовність дій для створення макросу
- встановити курсор в рядку заголовків стовпчика - Д;
- виконати команду меню Сервис – Макрос - Начать запись;
- в діалоговому вікні Запись макроса ввести: ім`я макросу, призначити сполучення клавіш для виклику макросу - Ctrl+t, вказати збереження макросу в даній робочій книзі далі - ОК;
- на панелі інструментів Остановить запись натиснути кнопку Относительная ссылка;
- виділити діапазон ячейок, які містять імена полів заголовку даного рядка, скопіювати;
- встановити курсор на два рядка нижче вихідної в ячейку, яка містить номер рахунку;
- виконати команду меню Вставка-Скопированные ячейки, вказати доданий діапазон з зсувом вниз;
- встановити курсор в ячейку рядку заголовків, яка містить ім`я стовпця -Д;
- натиснути клавішу Ctrl+t для виконання вставки рядку заголовків;
- повторити попередні дії для всіх рядків таблиць, що залишились.
Створення діапазону умов для наступних облікових періодів
Вихідний діапазон ячейок стовпців А-F копіюється в праву частину таблиці, із додержанням проміжку в один стовпець. Таким чином, шаг зміщується по горизонталі, складаючи 7 стовпців вправо для кожного наступного звітного періоду. При цьому необхідно змінювати дату початку і закінчення звітного періоду.
Послідовність виконання
1. Виділити стовпці A-F та скопіювати.
2. Підготувати діапазон умови для нового звітного періоду:
- в ячейку H1 вставить;
- виділити стовпець J (нижня границя дати);
- виконати команду меню Правка-Замінити з вказівкою : що -.01. змінити на -.02. і - Змінити все;
- виділити стовпець К (верхня границя дати);
- виконати команду меню Правка- Змінити з вказівкою: що -.02. змінити на -.03. і - Змінити все.
ПРИМІТКА.Дії даного пункту повторити для всіх звітних періодів, переміщувати курсор в область для нового звітного періоду.
3. Команда меню Сервис- Защита-Защитить лист.
4. Закрити файл.
Завдання 7. Формування оборотів по рахунку
Обороти по рахунку, субрахунку за встановлений звітний період розраховується на листі ОСВ на підставі даних ЖГО з допомогою вбудованої функції БДСУММ, яка використовує умови фільтрації записів ЖГО, підготовлених на листі Критерії.
Послідовність виконання
1. Відкрити файл БУХГАЛТЕРСЬКИЙ ЗВІТ.XLS.
2. Перейти на лист ОСВ.
Формування дебетових оборотів по рахунку, субрахунку
Послідовність виконання
1. Активізувати ячейку G5 блока ДО1.
2. Ввести формулу:
=БДСУММ(БазаДаних; 7; СМЕЩ (Критерії ! $ A $ 2; $BF5; E$ 1; 2; 4))
де БазаДаних –блок на листі ЖГО; 7- порядковий номер поля Сума, умови фільтрації формується вбудованою функцією СМЕЩ. В функції СМЕЩ використовуються параметри:
- Критерії!$a$2 - початок області комірок на листі Критерії; абсолютне посилання;
- $BF5 - зміщення по строкам від початку вказаної області на листі Критерії, абсолютне посилання по стовпцю;
- E$1 - зміщення по стовпцям від початку вказаної області на листку Критерії, абсолютне посилання по строчці;
- 2 - висота області умови на листі Критерії (кількість строк);
- 4-ширина області умови на листі Критерії (кількість стовпчиків).
За допомогою СМЕЩ виділяється область умови для фільтрації записів блока База Даних по дебету кожного рахунку, субрахунку.
Для тиражування формули дебетових оборотів слідує:
- активізувати ячейку G5;
- за допомогою автозаповнювання, занести дані сім ячейкам блока ДО1;
- виділити блок ДО1;
- скопіювати дані до блоків ДО2, ДО3, ДО4, ДО5.
В наслідок чого будуть створені формули для врахування дебетового обороту кожного рахунку, субрахунку по всім звітним періодам.