Excel 2007: обчислення у зведених таблицях
 | Розглядаючи зведені таблиці Excel 2007, ми досі обговорювали лише «зовнішній» бік цього інструменту. Нас цікавили головним чином способи формування макета зведеного звіту, групування та зображення даних. Сьогодні наше завдання — заглянути всередину зведених таблиць, тобто познайомитися з механізмом організації обчислень у зведеному звіті. Насправді, для бухгалтера це питання є дуже актуальним. Не всі розрахунки потрібно робити формулами на робочому листі. Деякі операції має сенс виконувати саме у зведених таблицях і не інакше. Тим більше, що Excel 2007 надає для цього всі необхідні можливості. Микола КАРПЕНКО, канд. техн. наук, доцент кафедри прикладної математики та інформаційних технологій Харківської національної академії міського господарства |
При створенні зведених звітів ми вже користувалися операціями підсумовування даних та визначення кількості значень для обчислюваного поля. Зараз наше завдання розглянути, які додаткові можливості у плані організації обчислень можуть запропонувати бухгалтеру зведені таблиці Excel 2007. І зробимо ми це на прикладі бази даних продажів з попередньої статті. План наших дій буде таким.
За основу візьмемо базу даних, зображену на рис. 1. У ній зібрано відомості про продажі, зафіксовані за кожну дату звітного періоду. Спочатку на прикладі цієї бази побудуємо декілька нескладних звітів, попрацюємо з групою основних операцій зведеної таблиці та з’ясуємо особливості їх поведінки при використанні декількох ключових полів. Потім перейдемо до додаткових операцій зі зведеними звітами. Причому цю частину операцій розглянемо у поєднанні з прийомом створення так званого робочого ключа. Ми вже користувалися робочими ключами при консолідації даних («Б & К», 2010, № 7, с. 27, «Excel 2007: консолідація даних (ч. 2)»). І тепер подивимося, як це працює у зведених таблицях.
Нагадаю, що робочий ключ — це допоміжне поле (колонка значень), яке можна використовувати для специфічного групування інформації. Зазвичай ключ створюють формулою на основі наявних значень у базі даних. За своїм призначенням робочий ключ схожий на режими групування зведених таблиць, але між ними є істотні відмінності. Щоб розібратися, у чому вони полягають, вчинимо так. Спочатку створимо декілька зведених звітів, застосовуючи різні режими групування значень у поєднанні з додатковими обчисленнями. Потім виконаємо ті самі дії, але замість групування скористаємося робочим ключем та подивимося, який спосіб роботи кращий. Ось такий план. Але щоб приступити до його реалізації, потрібно підготувати дані. З цього ми й почнемо.
Підготовка вихідних даних
Відкриваємо базу даних, зображену на рис. 1. За допомогою зведених таблиць ми можемо отримати з цієї бази підсумкові значення, але вони будуть надмірно деталізовані. Так, зібравши підсумки за датами, ми побачимо обсяги реалізації за кожний день. А цих днів у нашій базі більше 200. Зрозуміло, аналізувати такий обсяг інформації практично нереально. Тому, щоб отримати обсяги реалізації за місяцями або за кварталами (не вдаючись до групування даних!), нам знадобиться інше ключове поле. Припустимо, ми вирішили об’єднати дані за місяцями. Для цього в окремій комірці запишемо формулу, яка для кожної дати поверне значення у форматі «Номер місяця — Номер року». Це і буде перший робочий ключ. Використовуючи цей ключ при створенні зведеної таблиці, ми без проблем визначимо обсяги реалізації за кожний місяць звітного періоду. Другий робочий ключ повинен для кожної дати сформувати рядок з номером кварталу. Маючи ці дані, ми зможемо миттєво порахувати обсяги реалізації за кожний квартал. У принципі, для вирішення такого завдання можна скористатися механізмом групування значень у полі «
Дата». Але це не завжди зручно. По-перше, групування вимагає точного визначення блока у вихідній базі, і ми це вже знаємо. По-друге, при виконанні обчислень зручніше користуватися робочою колонкою, а не згрупованими значеннями. І ми в цьому невдовзі переконаємося. А поки що робимо так: 
1) відкриваємо базу даних, зображену на рис. 1;
2) стаємо на комірку «H1» та вводимо заголовок «Мес.»;
3) переходимо на комірку «H2», сюди вводимо формулу: «=ЕСЛИ(МЕСЯЦ(A2)=1;”Янв”;ЕСЛИ (МЕСЯЦ(A2)=2;”Фев”;ЕСЛИ(МЕСЯЦ(A2)=3; ”Март”;ЕСЛИ(МЕСЯЦ(A2)=4;”Апр”;ЕСЛИ (МЕСЯЦ(A2)=5;”Май”;ЕСЛИ(МЕСЯЦ(A2)=6; ”Июнь”;ЕСЛИ(МЕСЯЦ(A2)=7;”Июль”;””)))))))&ЕСЛИ(МЕСЯЦ(A2)=8;”Авг”;ЕСЛИ(МЕСЯЦ(A2)=9;”Сен”;ЕСЛИ(МЕСЯЦ(A2)=10;”Окт”; ЕСЛИ(МЕСЯЦ(A2)=11;”Ноябрь”;ЕСЛИ(МЕСЯЦ (A2)=12;”Дек”;””)))))»;
4) копіюємо формулу на всю висоту таблиці. Тепер у колонці «H» для кожної дати з’явиться назва місяця.
Кілька слів про те, як створити формулу. Вона довга, але по суті проста. Зробіть так:
1) станьте в комірку «H1»;
2) у рядку формул натисніть «fx», з’явиться Майстер функцій;
3) за допомогою Майстра функцій вставте в «H1» функцію «Если()». Параметри її не заповнюйте, вкажіть фіктивні значення, наприклад, «1», «1», «0». Тобто в першому наближенні ви отримаєте формулу «Если(1;1;0)»;
4) перейдіть до комірки «H3»;
5) за допомогою Майстра функцій вставте до цієї комірки формулу «Месяц(A2)». Функцію «МЕСЯЦ()» ви знайдете в категорії «Дата и время»;
6) натисніть «F2», щоб перейти в режим редагування комірки «H3»;
7) виділіть текст формули без символу «=» та скопіюйте його в буфер обміну;
8) натисніть «Esc», щоб повернутися на робочий лист;
9) перейдіть на комірку «H2», увійдіть до режиму редагування формули;
10) замість першого параметра функції «Если(1;1;0)» (цей параметр виділено напівжирним шрифтом) вставте вміст буфера обміну. У результаті має вийти «Если(Месяц(A2);1;0)»;
11) завершіть створення умови перевірки, доповніть його операцією порівняння «=1». Ви повинні отримати вираз «Если(Месяц(A2)=1;1;0)» (зміни виділено напівжирним шрифтом);
12) замість другого параметра функції «Если()» наберіть «Янв». У вас вийде «Если(Месяц(A2)=1; “Янв”;0)»;
13) скопіюйте функцію «Если()» без символу «=» у буфер обміну. Нагадаю, що зараз ми перебуваємо в режимі редагування формули, тому «F2» натискувати не потрібно;
14) перейдіть у кінець формули, видаліть третій параметр (зараз це «0») і вставте замість нього вміст буфера обміну. Ви отримаєте таку формулу: «ЕСЛИ(МЕСЯЦ(A2)=1; “Янв”; ЕСЛИ(МЕСЯЦ(A2)=1;”Янв”;0))»;
15) у другій (вкладеній) функції «ЕСЛИ()» змініть умову порівняння: замість «=1» потрібно ввести «=2». Крім того, значення «Янв» у цій функції замініть на «Фев». У результаті ви повинні отримати такий вираз: «=ЕСЛИ (МЕСЯЦ(A2)=1;”Янв”;ЕСЛИ(МЕСЯЦ(A2)=2;”Фев”;0))»;
16) видаліть останній параметр «0» вкладеної функції «Если()», ще раз вставте вираз із буфера обміну та відкоригуйте вираз для третього місяця. Повторіть ці дії сім разів.
У результаті ви отримаєте частину формули, яка обробляє місяці з номерами з «1» по «7». Далі продовжувати нарощувати формулу не можна, оскільки максимальна кількість вкладених функцій в Excel 2007 дорівнює семи. Але це не проблема. В окремій комірці аналогічно побудуйте функцію «Если()» для місяців з «8» по «12» і потім об’єднайте отримані вирази операцією «&». Робочий ключ для групування дат за місяцями ми створили. Залишилося зробити те саме для кварталів. Робимо так:
1) стаємо на комірку «I1» та вводимо заголовок «Кв.»;
2) переходимо на комірку «H2», сюди вводимо формулу: «=ЕСЛИ(ЦЕЛОЕ((МЕСЯЦ(A2)+2)/3)= 1;”1 кв.”; ЕСЛИ(ЦЕЛОЕ((МЕСЯЦ(A2)+2)/3)=2; “2 кв.”; ЕСЛИ(ЦЕЛОЕ((МЕСЯЦ(A2)+2)/3)=3; “3 кв.”; ЕСЛИ(ЦЕЛОЕ((МЕСЯЦ(A2)+2)/3)=4;”4 кв.”;””))))». У цій формулі номер кварталу за датою знаходить формула «=ЦЕЛОЕ((МЕСЯЦ(A2)+2)/3)». А вкладені функції «Если()» потрібні для того, щоб замість знеособленого номера поставити текстове значення;
3) копіюємо формулу на всю висоту таблиці. Тепер у колонці «I» для кожної дати з’явиться номер кварталу. Остаточний вигляд таблиці з вихідною базою даних наведено на рис. 1.
Група основних операцій у зведеній таблиці
Отримавши зведений звіт, ви можете виявити, що за підсумковими числами потрібно виконати додаткові обчислення. Наприклад, визначити приріст показника у відсотках, або знайти його питому вагу в загальній сумі. Я неодноразово був свідком того, як для вирішення такого завдання використовували звичайні формули. І цілком даремно. Зрозуміло, без формул в Excel не обійтися, але в цьому конкретному випадку є своя специфіка.
По-перше, створювати посилання на комірки зведеної таблиці незручно. Ви напевно пам'ятаєте, що при створенні посилання на комірку зведеного звіту Excel вставляє не адресу робочого листа, а функцію «
Получить.данные.сводной.таблицы()», що само по собі не завжди доречно. По-друге, упровадження формул безпосередньо до зведеного звіту неможливе. Для цього доведеться спочатку перетворити зведену таблицю на звичайну, і тільки потім робити в ній обчислення. А як бути, якщо ви бажаєте зберегти зведений звіт?.. Нарешті, виникає закономірне запитання: для чого робити зайву роботу зі створення та копіювання формул, якщо можна спробувати все отримати прямо у зведеному звіті? І зараз ми подивимося, що для цього можна зробити. Почнемо з основних обчислень. Робимо так:1) відкриваємо документ із базою даних. Комбінацією клавіш «
Shift+F11» вставляємо новий лист (у прикладі він називається «СВ»). Тут ми формуватимемо зведений звіт;2) ставимо покажчик активної комірки на «
A1» листа «СВ»;3) викликаємо розділ «
Вставка» основного меню, у групі «Таблицы» клацаємо по іконці «Сводная таблица»;4) у меню, що відкрилося, вибираємо пункт «
Сводная таблица». З’явиться вікно «Создание сводной таблицы»;5) клацаємо по параметру «
Таблица или диапазон:». Переходимо на лист «Продажи» та обводимо діапазон «A:I» (так, щоб захопити робочі колонки);6) у вікні «
Создание сводной таблицы» натискуємо на кнопку «ОК». На робочому листі з’явиться макет зведеного звіту та відкриється вікно настройок із назвою «Список полей сводной таблицы»;7) у цьому вікні поле «
НаимТов» ставимо в область рядків, в область «Значения» три рази додаємо поле «Сумма» (рис. 2); 
8) клацаємо лівою кнопкою по першому полю «Сумма» в області «Значения». Відкриється меню, наведене на рис. 2;
9) вибираємо пункт «Параметры полей значений…». Відкриється вікно «Параметры поля значений» (рис. 3);
10) на закладці «Операция» вибираємо варіант «Сумма». До поля «Пользовательское имя:» вводимо «Сумма, грн.»;
11) натискуємо «ОК»;
12) клацаємо по другому полю «Сумма» в області «Значения» вікна «Список полей сводной таблицы»;
13) для цього поля вибираємо операцію «Количество», в області «Пользовательское имя:» вводимо «Кол., шт.»;
14) натискуємо «ОК»;
15) клацаємо по третьому полю «Сумма» в області «Значения»;
16) як операцію вибираємо «Максимум», в області «Пользовательское имя:» вводимо «Макс. сумма, грн.»;
17) натискуємо «ОК». У результаті отримаємо звіт, наведений на рис. 4.
Подивимося, що ж ми отримали. У першому рядку звіту підсумовано обсяги продажів за кожним товаром. Наприклад, для ТМЦ «Блокнот» ця сума склала 18165,35 грн. (комірка «C2»). Рядком нижче знаходиться те саме поле «Сумма», але для нього ми визначили операцію «Количество». Тому число в комірці «С3» — це кількість фактів продажів ТМЦ «Блокнот» за весь період. Таких продажів у нашому випадку 470. У третьому рядку звіту ми показали максимальне значення для поля «Сумма». Значення «88,75» у комірці «С4» — це максимальна сума правочину щодо товару «Блокнот», яка міститься в нашій базі даних.
Нам залишилося відповісти на останнє запитання: як діятиме операція пошуку максимального значення при створенні зведеного звіту за двома ключовими полями? Робимо так:
1) відкриваємо зведений звіт, як показано на рис. 4;
2) ставимо в область колонок назву кварталу (поле «Кв.» на рис. 1). Фрагмент оновленого звіту наведено на рис. 5.
Як і слід було очікувати, максимальне значення Excel визначив за тими фрагментами вихідної бази даних, які брали участь у розрахунку показників конкретної комірки зведеного звіту. Наприклад, у комірці «С5» записана величина 88,75. Це не що інше, як максимальна сума правочину за ТМЦ «Блокнот» у I кварталі 2009 року. Значення в комірці «F5» становить 117,15. Його Excel визначив за всіма продажами товару «Блокнот», які є у вихідній базі даних.
Мабуть, решта операцій із групи основних обчислень розглядати немає сенсу — усі вони працюють за одним принципом. На мій погляд, у практиці бухгалтери можуть стати у пригоді такі варіанти:
— «Сумма», визначення суми значень для ключового поля;
— «Количество», визначення кількості значень ключового поля;
— «Максимум», пошук максимального значення для ключового поля;
— «Минимум», пошук мінімального значення для ключового поля;
— «Среднее», обчислення середнього арифметичного значення для ключового поля;
— «Произведение», обчислення добутку значень ключового поля.
Решта операцій орієнтована на статистичну обробку бази даних і у практиці бухгалтерського обліку практично не застосовується.
Додаткові обчислення у зведеній таблиці
Абсолютні значення показників, безумовно, важливі. Однак вони не завжди відображають реальну картину того, що відбувається. Іноді потрібно аналізувати процентні зміни, тенденції, темпи зростання тощо. Такі розрахунки широко представлені у зведених таблицях Excel 2007. Отримати доступ до них можна через закладку «
Дополнительные вычисления» у вікні «Параметры поля значений» (рис. 3). І зараз розглянемо, як цим скористатися, але спочатку змінимо завдання. Ми побудуємо звіт щодо поквартальних обсягів продажів кожним контрагентом. По-перше, такий звіт буде компактнішим. По-друге, у ньому логічніше виглядають такі показники, як зміна обсягів реалізації, частка в загальному обсязі тощо. Почнемо.
ОПЕРАЦІЯ
«отличие» за ЕЛЕМЕНТАми РЯДКІВ ЗВЕДЕНОГО ЗВІТУЗ неї ми почнемо знайомство з можливостями додаткових обчислень у зведених таблицях Excel 2007. Робимо так:
1) відкриваємо зведену таблицю, зображену на рис. 5: її ми візьмемо за основу;
2) в область рядків переносимо поле «
Покупатель». Відповідно поле «НаимТов» із області рядків видаляємо;3) в області колонок залишаємо поле «
Кв.» (номери кварталів, отримані з робочого ключа!);4) в області «
Значения» залишаємо поле «Сумма», за яким обчислюється виручка у гривнях (у звіті на рис. 5 цей рядок озаглавлено «Сумма, грн.»);5) решта полів із області «
Значения» видаляємо — нам простіше додати елементи, яких бракує, заново;6) переносимо в область «
Значения» поле «Сумма»;7) клацаємо лівою кнопкою миші по цьому полю. Відкриється меню, наведене на рис. 2;
8) вибираємо пункт «
Параметры полей значений…». Відкриється вікно «Параметры поля значений» (рис. 3);9) на закладці «
Операция» вибираємо варіант «Сумма». У полі «Пользовательское имя:» з’явиться текст «Сумма по полю Сумма» (запам’ятайте цей факт!);10) клацаємо лівою кнопкою по полю «
Пользовательское имя:», друкуємо назву «Прирост, грн.»;11) клацаємо по
закладці «Дополнительные вычисления». Вікно набере вигляду, як наведено на рис. 6; 
12) клацаємо по параметру «Дополнительные вычисления». Відкриється список, наведений на рис. 7;
13) вибираємо варіант «Отличие»;
14) у лівій частині вікна на рис. 6 знаходиться список «поле:». У цьому списку вибираємо елемент «Кв.»;
15) у списку «элемент:» указуємо значення «1 кв.»;
16) натискуємо «ОК». Звіт набуде форми, як зображено на рис. 8.
У цьому документі, крім абсолютного значення виручки від продажів, ми визначили зміну цієї суми щодо I кварталу 2009 року. Для перевірки отриманого значення введіть, наприклад, формулу: «=D3-C3». Результат її роботи буде 1261,7 (6924,85 - 5663,15) (рис. 8).
Узагальнимо цей результат, повернувшись до таблиці на рис. 6. Операція у списку «Дополнительные вычисления» вказує на характер розрахунків. Значення «Отличие» означає, що ми будемо знаходити різницю між певними елементами зведеного звіту.
У списку «поле:» ми вказуємо одну зі змінних, які розташовані в області рядків або колонок. У нашому випадку ми вибрали поле «Кв.». Виходячи з цього Excel «знає», що відмінність (приріст/убуток) потрібно визначати між парами змінних в області даних, які знаходяться в одному рядку (тобто між сусідніми кварталами). Тепер залишається вказати, який із кварталів має служити базою для порівняння. Цей параметр визначає значення зі списку «элемент:». На рис. 6 ми вибрали в цьому списку значення «1 кв.». А це означає, що відхилення поточного значення обчислюватиметься щодо I кварталу 2009 року.
ОПЕРАЦІЯ «отличие» за ЕЛЕМЕНТАми КОЛОНОК ЗВЕДЕНОГО ЗВІТУ
Змінимо зведену таблицю, зображену на рис. 8. Тепер наше завдання — порівняти обсяги реалізацій між окремими контрагентами в межах кожного кварталу. Робимо так:
1) стаємо всередину зведеної таблиці, відкриваємо вікно «
Список полей сводной таблицы»;2) клацаємо лівою кнопкою по полю «
Прирост, грн.» (воно знаходиться в області «Значения»);3) із меню, що розкрилося, вибираємо «
Параметры полей значений…» (рис. 2). Відкриється вікно «Параметры поля значений»;4) переходимо на закладку «
Дополнительные вычисления»;5) як операцію залишаємо «
Отличие», у списку «поле:» вибираємо «Покупатель»;6) переходимо на список «
элемент:» та вказуємо «ООО “Эталон”»;7) натискуємо «
ОК». Звіт набуде форми, як показано на рис. 9. 
У цьому звіті приріст/убуток обсягів реалізації обчислено щодо контрагента «ООО “Эталон”». Наприклад, у І кварталі 2009 року сума продажів за «ООО “Талан”» перевищила аналогічний показник «ООО “Эталон”» на величину 6988,25 - 5663,15 = 1325,10 (грн.), комірка «С6».
ВИБІР БАЗИ ДЛЯ ОПЕРАЦІЇ «отличие»
Зараз наше завдання — змінити базу розрахунків. Тобто ми бажаємо змінити число, щодо якого Excel рахує приріст/убуток заданого показника. Для цього повертаємося до зведеної таблиці на рис. 8 та робимо так:
1) стаємо всередину зведеного звіту;
2) відкриваємо вікно «
Список полей сводной таблицы»;3) клацаємо по полю «
Прирост, грн.» в області «Значения»;4) із меню, що з’явилося, вибираємо пункт «
Параметры полей значений…» (рис. 2). Відкриється вікно «Параметры поля значений»;5) переходимо на закладку «
Дополнительные вычисления»;6) на цій закладці заповнюємо такі параметри: як додаткові обчислення ставимо операцію «
Отличие», у списку «поле:» вибираємо «Кв.», у списку «элемент:» вказуємо «3 кв.»;7) натискуємо «
ОК». Звіт набуде форми, як наведено на рис. 10. Тепер усі зміни обчислено щодо показників III кварталу 2009 року. Наприклад, за контрагентом «ООО “Эталон”» обсяг реалізації в II кварталі 2009 року був на 5183,50 грн. менше, ніж у III кварталі (6924,85 - 12107,9 = -5183,05 грн.). 
Виконаємо перетворення звіту на рис. 10, вибравши в області обчислень поле «Мес.» (рис. 2). На місці значень приросту/убутку з’явиться текст «#Н/Д».
Важливо! При настроюванні додаткових обчислень у списку «поле:» вікна «Параметры поля значений» (рис. 6) вибирайте тільки ті елементи, які розташовано в області рядків або колонок зведеного звіту.
ВИЗНАЧЕННЯ ДИНАМІКИ АБО ТЕМПІВ ЗРОСТАННЯ
Цікаву можливість при роботі зі зведеними таблицями відкривають операції «
(назад)» і «(далее)» (вони розташовані на закладці «Дополнительные вычисления», рис. 6). За їх допомогою можна визначити темпи зростання значень. Наприклад, якщо при створенні звіту, наведеного на рис. 8, як базу у списку «элемент:» вибрати варіант «(назад)», то отримаємо таблицю, зображену на рис. 11. Тепер у рядку «Прирост, грн.» Excel покаже темп зміни обсягів виручки в поточному місяці відносно попереднього місяця. Наприклад, у комірці «D4» значення «1261,70» фактично отримано за формулою «=D3-C3», значення «5183,05» у комірці «E4» пораховане як «=E3-D3» тощо. Інакше кажучи, для кожного контрагента ми отримали динаміку зміни обсягів продажів за кварталами у вартісному виразі. 
ЧАСТКА та відсотковІ ЗМІНИ
Темпи зростання у поєднанні з показниками зміни значень відносно вибраної бази добре демонструють динаміку та тенденцію зміни показників. Однак бухгалтеру іноді зручніше аналізувати ці цифри у відсотковому зображенні. Такий розрахунок можна зробити прямо у зведеній таблиці, звернувшись до закладки додаткових обчислень. Робимо так:
1) відкриваємо звіт, як наведено на рис. 11;
2) стаємо всередину зведеної таблиці, відкриється вікно «
Список полей сводной таблицы» (рис. 2). Зараз наш звіт містить два поля «Сумма» в області значень. Додамо це саме поле в область значень ще двічі. Для цього продовжуємо так:3) знаходимо елемент «
Сумма» у списку «Выберите поле для добавления в отчет:» та двічі перетягуємо його в область «Значения» (рис. 2). Тепер в області значень у нас чотири елементи і всі вони є тим самим полем — «Сумма»;4) клацаємо лівою кнопкою миші по третьому полю «
Сумма». З’явиться випадне меню, з якого вибираємо пункт «Параметры полей значений…» (рис. 2);5) у вікні, що відкрилося, на закладці «
Операция» вибираємо «Сумма»;6) у полі «
Пользовательское имя:» пишемо «Доля, %»;7) клацаємо по закладці «
Дополнительные вычисления», вікно набуде форми, як наведено на рис. 6;8) клацаємо по полю «
Дополнительные вычисления». Відкриється список, як наведено на рис. 7;9) вибираємо варіант «
Доля»;10) у лівій частині вікна (рис. 6) у списку «
поле:» вибираємо елемент «Кв.»;11) у списку «
элемент:» вказуємо значення «1 кв.»;12) натискуємо «
ОК»;13) клацаємо лівою кнопкою миші по четвертому полю «
Сумма» в області значень (це останнє додане нами поле);14) із меню, що відкрилося, вибираємо пункт «
Параметры полей значений…» (рис. 2);15) на закладці «
Операция» вибираємо варіант «Сумма»;16) у полі «
Пользовательское имя:» пишемо «Изменение, %»;17) переходимо на закладку «
Дополнительные вычисления». Відкриється однойменний список, як наведено на рис. 7;18) із запропонованих варіантів вибираємо «
Приведенное отличие»;19) у списку «
поле:» ставимо «Кв.», у списку «элемент:» вказуємо значення «1 кв.»;20) натискуємо «
ОК». Звіт набуде форми, як показано на рис. 12. У цьому звіті з’явилися два додаткові рядки. Рядок «Доля, %» показує відсоткову зміну суми виручки відносно I (базового) кварталу. Так, у комірці «D5» величина «122,28 %» отримана як «=D3/C3*100». У рядку «Изменение, %» бачимо приріст виручки відносно І кварталу (формула для «D6» має такий вигляд: «=(1-D3/C3)*100», тобто (1-6924,85 : 5663,15) х 100 = 22,28 %).Гадаю, переліченими варіантами додаткових обчислень у зведених таблицях Excel 2007 можна обмежитися. Для практичної роботи їх більш ніж достатньо. Інші операції над даними зведеного звіту наведено в табл. 1.
Таблиця 1. Призначення операцій закладки «
Дополнительные вычисления»| Операція | Дія |
| 1 | 2 |
| Ні | Відключає додаткові обчислення у зведеній таблиці |
| Відмінність | Показує відхилення (різницю) поточного значення щодо базового значення. Це значення визначають параметри «поле:» та «элемент:» зведеного звіту |
| Частка | Показує частку у відсотках щодо базового значення |
| Зведена відмінність | Показує відхилення у відсотках щодо базового значення |
| З наростаючим підсумком у полі | Визначає значення у вигляді наростаючого підсумку для базових елементів, які визначені параметрами «поле:» та «элемент:» зведеного звіту |
| Частка від суми за рядком | Частка від суми за рядком. Визначає частку у відсотках для поточного значення щодо загального підсумку в кожному рядку або категорії зведеного звіту |
| Частка від суми за стовпцем | Відображує частку у відсотках для поточного значення щодо загального підсумку в кожній колонці зведеного звіту |
| Частка від загальної суми | Визначає частку (у відсотках) для поточної комірки щодо загальної суми у зведеному звіті |
| Покажчик | Провадить обчислення так: ((Значення в поточній комірці) x (Загальний підсумок)) : ((Підсумок рядка) x (Підсумок стовпця)) |
ДОДАТКОВІ ОБЧИСЛЕННЯ та ГРУПУВАННЯ у ЗВЕДЕНИХ ТАБЛИЦЯХ
Останній момент, який я хотів би обговорити в контексті виконання розрахунків у зведених таблицях, — це взаємодія обчислювальних операцій з групуванням даних. Тепер наше завдання буде таким: знайти сумарні обсяги продажів у кількісному (у штуках) та вартісному (у гривнях) виразі за кожним контрагентом поквартально. Але як квартали не використовуватимемо робоче поле. Укажемо дати та згрупуємо їх засобами зведених таблиць. Робимо так:
1) натискуємо «
Shift+F11» — додаємо новий робочий лист;2) подвійним клацанням по ярличку листа відкриваємо його назву. Вводимо текст. Я назвав лист з майбутнім зведеним звітом «
СВ_»;3) ставимо покажчик активної комірки на «
A1» листа «СВ_»;4) викликаємо розділ «
Вставка» основного меню;5) на стрічці меню «
Вставка» знаходимо групу «Таблицы», у ній клацаємо по іконці «Сводная таблица»;6) із меню, що відкрилося, вибираємо
пункт «Сводная таблица». З’явиться вікно «Создание сводной таблицы»;7) у цьому вікні клацаємо в полі введення для параметра «
Таблица или диапазон:»;8) переходимо на лист «
Продажи», обводимо діапазон колонок «A1:I2445». У моєму прикладі база даних була саме такого розміру;9) у вікні «
Создание сводной таблицы» натискуємо кнопку «ОК». На робочому листі з’явиться макет зведеного звіту та відкриється вікно настройок із назвою «Список полей сводной таблицы»;10) перетягуємо поле «
Дата» в область «Названия строк», поле «Покупатель» — в область «Названия столбцов». В область «Значения» двічі переносимо поле «Сумма»;11) у вікні «
Список полей сводной таблицы» в області «Значения» клацаємо по першому елементу з полем «Сумма». З’явиться випадне меню (рис. 2);12) із цього меню вибираємо «
Параметры полей значений…». З’явиться вікно, як наведено на рис. 3;13) у полі «
Пользовательское имя:» вводимо текст «Сумма, грн.». Це буде заголовок першої колонки зведеного звіту;14) у списку «
Операция» вікна «Параметры поля значений» вибираємо варіант «Сумма»;15) у вікні «
Параметры поля значений» натискуємо «ОК»;16) клацаємо правою кнопкою миші по будь-якій комірці, де записана дата у зведеному звіті;
17) із контекстного меню вибираємо пункт «
Группировать…». З’явиться вікно «Группирование»;18) у цьому вікні у списку «
с шагом:» вибираємо варіант «Кварталы»;19) у вікні «
Группировать…» натискуємо «ОК»;20) вибираємо друге поле «
Сумма» в області «Значения»;21) для цього поля задаємо такі параметри: в область «
Параметры полей значений…» вводимо текст «Прирост, грн.», як операцію вибираємо «Сумма»;22) у вікні «
Параметры поля значений» клацаємо по закладці «Дополнительные вычисления». З’явиться вікно, як наведено на рис. 13; 
23) у списку «
Дополнительные вычисления» вибираємо «Отличие»;24) параметр «
поле:» ставимо в положення «Дата»;25) у списку «
элемент:» вибираємо значення «Кв-л1». І тут потрібно бути дуже уважним!Важливо! Зверніть увагу на перелік значень у списку «элемент:» поля «Дата» (рис. 13). Незважаючи на те що в базі записана інформація тільки щодо перших трьох кварталів, у цьому списку є, наприклад, елемент «Кв-л4». Це — наслідок роботи механізму групування значень поля «Дата»;
26) у вікні «Параметры поля значений» натискуємо «ОК» — закінчуємо створення зведеного звіту. Його остаточний вигляд наведено на рис. 14.
РОБОЧИЙ КЛЮЧ АБО ГРУПУВАННЯ?
У принципі, всі дані у звіті на рис. 14 обчислено правильно. У рядках розташовані назви кварталів, у колонках — контрагенти. У самому тілі звіту показано обсяги продажів, а також зміна суми за кожним контрагентом у межах кварталів. Та все ж цей приклад я запропонував вам не випадково. Незважаючи на те що групування за датою спрацювало правильно, при його використанні є дві незручності.
По-перше, щоб отримати доступ до інструментів групування, нам довелося вказати точний діапазон із даними на робочому листі. А це певною мірою обмежує можливість поповнення бази новими записами.
А по-друге, до переліку «
элемент:» вікна «Параметры поля значений» можуть потрапити такі, яких немає у вихідних даних для створення зведеного звіту. Вибір такого елемента може стати додатковим джерелом помилок.Саме тому я раджу завжди користуватися робочим ключем та намагатися створювати його самому за допомогою формул. У цьому випадку ви завжди триматимете процес обробки у зведеному звіті під контролем та виконуватимете групування полів саме так, як це потрібно для вашого завдання.
Важливо! При виконанні основних та додаткових обчислень до зведеної таблиці можна застосувати будь-які режими групування даних. Але правильним рішенням, на мій погляд, буде використання робочого ключа.
Остання дія над нашим зведеним звітом буде такою:
1) відкриваємо зведену таблицю, як наведено на рис. 14;
2) стаємо на будь-яке значення в колонці «
Дата». Наприклад, на комірку «A3»;3) клацаємо правою кнопкою миші, із контекстного меню вибираємо «
Группировать…»;4) у вікні «
Группирование» для поля «Дата» вибираємо два значення: «Месяцы» та «Кварталы» (рис. 15). Фрагмент оновленого зведеного звіту показано на рис. 16 (див. с. 40). Як бачимо, у рядку «Прирост, грн.» значення обчислено неправильно.
Важливо! При кожній зміні групування або макета таблиці перевірте настройки додаткових обчислень. Найімовірніше, параметри цих обчислень доведеться ввести заново.
Звідси висновок: спочатку добре продумайте структуру всього звіту, звернувши особливу увагу на спосіб групування ключових полів. І лише після цього визначайте параметри додаткових обчислень в області даних.
І ще одне невелике спостереження. Відкрийте звіт, як показано на рис. 16, та ввійдіть до параметрів додаткових обчислень для поля «
Дата». У вікні «Параметры поля значений» перейдіть на закладку «Дополнительные вычисления», укажіть спосіб розрахунку «Отличия» та подивіться на склад списку «элементы:». У ньому буде перелічено всі назви місяців (подібно до того, як це було при організації обчислень за кварталами). Тобто за наявності декількох групувань ключового поля (у нас у цьому випадку працює групування за місяцями та кварталами) додаткові обчислення можна визначити тільки для найнижчого (докладного) рівня. А от при роботі з робочим ключем такої проблеми немає! Ми не даремно створили колонку «Мес.» у вихідній базі даних, і ви зараз у цьому зможете переконатися. Переформуйте зведений звіт, поставивши в область рядків спочатку «Кв.», а потім — поле «Мес.». При цьому в області «Названия строк» поле «Мес.» має стояти нижче за поле «Кв.». За формою зведена таблиця залишиться такою самою, як наведено на рис. 16. Зате в ній ви зможете довільно обчислити відхилення в обсягах реалізації як за місяцями, так і за кварталами. 
На цьому ми завершимо тему організації обчислень у зведених звітах. Наступного разу приступимо до настроювання параметрів зведених таблиць та роботи з декількома діапазонами консолідації.
Чекаю ваших листів, пропозицій та зауважень на
bk@id.factor.ua , nictomkar@rambler.ru або на форумі редакції www.bk.factor.ua/forum. Успішної роботи!
