Excel 2007: зведені таблиці-2
 | Продовжуючи тему роботи зі зведеними таблицями Excel 2007, розглянемо особливості їх використання при роботі з кількома ключами, подивимося, що таке автоматичне групування даних, побудуємо таблиці з багатьма полями в області обробки даних. Нарешті, розберемося, як в інструменті зведених таблиць реалізувати перехресний запит. І, як завжди, зупинимося на тих новинках, що з’явилися в інструменті зведених таблиць Excel 2007. Микола КАРПЕНКО, канд. техн. наук, доцент кафедри прикладної математики та інформаційних технологій Харківської національної академії міського господарства |
Однією з головних переваг зведених таблиць є можливість швидкої зміни їх умісту. Достатньо тільки переформувати макет зведеного звіту — і ви практично миттєво отримаєте новий результат. Минулого разу ми переконалися в тому, що отримати зведений звіт за базою даних в Excel 2007 — справа однієї хвилини. Але ми зупинилися на побудові таблиці за одним ключовим полем. А тепер подивимося, як поведеться Excel 2007 при використанні кількох ключових полів.
ЗВЕДЕНА ТАБЛИЦЯ: КІЛЬКА ПОЛІВ В ОБЛАСТІ ДАНИХ
Для прикладу ми продовжимо роботу з базою даних, зображеною на рис. 1. Нагадаю, що в ній зібрано дані про продажі, які організовано в таблицю із семи колонок (полів). Призначення колонок таке:
— «
Дата» — дата реєстрації продажу;— «
Покупатель» — назва контрагента, який купив товар;— «
НаимТов» — назва проданого товару;— «
Кол» — кількість проданого товару;— «
Цена» — ціна товару;— «
Сумма» — сума продажу;— «
НомДок» — номер накладної, за якою відпущений товар.Тепер наше завдання буде таким: знайти сумарні обсяги продажів у кількісному (у штуках) та вартісному (у гривнях) виразі щодо кожного контрагента з наявної бази. Виконуємо такі дії:
1) натискуємо на «
Shift+F11» — вставляємо новий лист. На цьому листі ми будуватимемо зведений звіт;2) подвійним клацанням по ярличку листа викликаємо редагування назви. Змінюємо її: я назвав лист із майбутнім зведеним звітом «
СВ»;3) ставимо покажчик активної комірки на «
A1» листа «СВ». Звідси починатиметься зведена таблиця;4) викликаємо розділ «
Вставка» основного меню;5) на стрічці меню «
Вставка» знаходимо групу «Таблицы», де клацаємо по іконці «Сводная таблица» (рис. 2); 
6) з меню, що з’явилося, вибираємо пункт «Сводная таблица» (рис. 2). Розкриється вікно «Создание сводной таблицы», зображене на рис. 3;
7) у цьому вікні клацаємо по полю введення для параметра «Таблица или диапазон:»;
8) переходимо на лист «Продажи», обводимо діапазон колонок «A:G»;
9) у вікні «Создание сводной таблицы» (рис. 3) натискуємо на кнопку «ОК». На робочому листі з’явиться макет зведеного звіту і відкриється вікно настройок з назвою «Список полей сводной таблицы», як показано на рис. 4;
10) у цьому вікні ставимо галочку біля поля «Покупатель». Перелік покупців негайно з’явиться в області рядків макета зведеної таблиці (рис. 4);
11) ставимо покажчик миші на поле «Кол»;
12) коли покажчик набуде хрестоподібної форми, натискуємо на ліву кнопку миші та перетягуємо поле «Кол» в область даних на робочому листі. Зведений звіт відразу змінить свою форму. У ньому залишиться стовпець «Покупатель», над ним з’явиться заголовок «Количество по полю Кол», у правій частині звіту з’явиться колонка «Итог» (рис. 5 на с. 27);
Важливо!
Поле «Кол» потрібно саме перемістити до області даних. Просто включити його до звіту, поставивши галочку у вікні на рис. 4, не можна, — структура зведеного звіту вийде зовсім іншою.13) у вікні «
Список полей сводной таблицы» в області «Значения» клацаємо по елементу «Количество по полю Кол». З’явиться меню, як показано на рис. 4;14) із цього меню вибираємо «
Параметры полей значений…». З’явиться вікно, як показано на рис. 6 (див. с. 27); 
15) у полі «Пользовательское имя:» вводимо текст «Кол, шт.». Це буде заголовок першої колонки зведеного звіту.
Важливо! Заголовок «Пользовательское имя:» не може збігатися з назвою поля бази даних. Наприклад, значення «Кол» у параметрі «Пользовательское имя:» використовувати не можна, оскільки в базі є колонка з назвою «Кол». Таке обмеження неважко обійти. Додайте, наприклад, пробіл до тексту заголовка — і проблему буде вирішено (назва «Кол » цілком допустима);
16) у списку «
Операция» вікна «Параметры поля значений» вибираємо варіант «Сумма» (рис. 6);17) у вікні «
Параметры поля значений» натискуємо на «ОК»;18) вибираємо поле «
Сумма» (рис. 4), перетягуємо його в будь-яке місце колонки «Итог» зведеного звіту (рис. 5);19) розтягуємо вікно «
Список полей сводной таблицы», щоб збільшити його розмір по вертикалі. В області «Значения» ми побачимо два елементи: «Кол, шт.» і «Количество по полю Сумма» (рис. 7); 
20) у вікні «Список полей сводной таблицы» в області «Значения» клацаємо по елементу «Количество по полю Сумма»;
21) з меню, що розкрилося, вибираємо «Параметры полей значений». З’явиться вікно, як показано на рис. 6;
22) у цьому вікні в полі «Пользовательское имя:» вводимо текст «Сумма, грн.». У списку «Операция» вибираємо «Сумма»;
23) у вікні «Параметры поля значений» натискуємо на «ОК». Результат нашої роботи показано на рис. 8 (див. с. 28).
Отже, що ми отримали? У підсумковому звіті для кожного підприємства відведено два рядки — «Кол., шт.» і «Сумма, грн.». Обидва вони знаходяться в колонці «Данные». У цих рядках підраховано сумарний обсяг реалізації всіх товарів у кількісному та вартісному виразі для кожного контрагента окремо.
Важливо!
Кількість рядків в області даних зведеного звіту зазвичай залежить від кількості впроваджених до цієї області полів.Але так буває не завжди. В Excel 2007 є інший спосіб створення макета, і зараз ми з ним ознайомимося. Виконуємо такі дії:
1) на листі «
СВ» натискуємо на «Ctrl+A» (виділити все);2) клацаємо по виділеній області правою кнопкою миші, з контекстного меню вибираємо «
Удалить». Ми стерли звіт зведеної таблиці;3) стаємо на комірку «
A1» листа «СВ»;4) викликаємо розділ «
Вставка» основного меню;5) на стрічці меню «
Вставка» клацаємо по іконі «Сводная таблица» (рис. 2);6) у вікні «
Создание сводной таблицы» (рис. 3) у параметрі «Таблица или диапазон:» вказуємо колонки «A:G» листа «Продажи»;7) натискуємо на кнопку «
ОК». На листі з’явиться макет зведеного звіту;8) у вікні настройок «
Список полей сводной таблицы» (рис. 4) ставимо галочку біля поля «Покупатель». До цього моменту всі наші дії були, як у попередньому випадку. А тепер — відмінність;9) за допомогою миші перетягуємо поля «
Кол» і «Сумма» до області «Значения» (вікно «Список полей сводной таблицы» на рис. 4). В області «Значения» з’являться два елементи — «Количество по полю Кол» і «Количество по полю Сумма»;10) клацаємо по елементу «
Количество по полю Кол» в області «Значения»;11) входимо до параметрів поля та встановлюємо операцію «
Сумма», назва поля — «Кол., шт.»;12) аналогічно для поля «
Количество по полю Сумма» вибираємо операцію підсумовування та назву «Сумма, грн.»;13) у вікні «
Параметры поля значений» натискуємо на «ОК». Форму зведеного звіту показано на рис. 9. 
Тепер поля даних розташовано по горизонталі (тобто кожне поле в окремій колонці).
Важливо!
Можливість розташувати поля в області даних як у горизонтальному, так і у вертикальному напрямку — маленька, але приємна новинка зведених таблиць Excl 2007. В окремих випадках горизонтальне розташування полів має принципове значення, оскільки саме таку форму зведеного звіту найпростіше перетворити в базу даних.
ВИДАЛЕННЯ ПОЛЯ ЗІ ЗВЕДЕНОЇ ТАБЛИЦІ
Є кілька способів видалити поле зі зведеного звіту.
Спосіб 1:
1) клацаємо по зведеній таблиці, відкриваємо вікно «Список полей сводной таблицы» (рис. 4);
2) знімаємо галочку біля того поля, яке потрібно видалити зі зведеного звіту.
Спосіб 2:
1) відкриваємо зведену таблицю;
2) клацаємо правою кнопкою миші по полю, що видаляється. З’явиться контекстне меню;
3) з цього меню вибираємо пункт «Удалить…».
Спосіб 3:
1) клацаємо по зведеній таблиці, відкриваємо вікно «Список полей сводной таблицы» (рис. 4);
2) у нижній частині вікна є область із чотирьох параметрів із загальною назвою «
Перетащите поля между указанными ниже областями:»;3) в одній з цих областей знаходимо параметр, що видаляється;
4) утримуючи натисненою ліву кнопку миші, переміщаємо поле до верхньої частини вікна в область «
Выберите поля для добавления в отчет:» (рис. 4);5) як тільки курсор змінить форму, відпускаємо кнопку миші.
Спосіб 4:
1) відкриваємо зведену таблицю, включаємо вікно «Список полей сводной таблицы» (рис. 4);
2) в області «Перетащите поля между указанными ниже областями:» знаходимо поле, що видаляється;
3) клацаємо по ньому лівою кнопкою миші. Відкриється меню, як показано на рис. 4;
4) із цього меню вибираємо пункт «
Удалить поле».
ЗМІНА ПОРЯДКУ РОЗТАШУВАННЯ ПОЛІВ В ОБЛАСТІ ДАНИХ
Сьогодні наше завдання — дізнатися, як можна змінити порядок дотримання полів в області даних і що з цього вийде. Виконуємо такі дії:
1) відкриваємо зведену таблицю, як показано на рис. 9. У нашому прикладі її розташовано на листі «
СВ»;2) ставимо покажчик активної комірки всередину зведеної таблиці. З’явиться вікно «Список полей сводной таблицы» (рис. 4, 10, див. с. 29);
Порада
Якщо вікна «Список полей сводной таблицы» на екрані немає, клацніть правою кнопкою по області зведеної таблиці та виберіть з контекстного меню пункт «Показать список полей».3) у цьому вікні знаходимо параметр «
Значения». У нашому звіті він об’єднує два елементи — «Кол, шт.» і «Сумма, грн.» (рис. 10);4) ставимо покажчик миші на поле «
Кол., шт.»;5) утримуючи натисненою ліву кнопку миші, перетягуємо його вниз так, щоб елемент «
Сумма, грн.» опинився зверху. Зведений звіт змінився: колонки «Сумма, грн.» та «Кол, шт.» помінялися місцями.Тепер подивимося, як можна переорієнтувати область даних зведеного звіту. Наше завдання — розташувати поля області даних по вертикалі (тобто повернутися до форми звіту на рис. 8). Порівняйте склад параметрів на рис. 7 та 10. У цих макетах є відмінності в області «Значения» і «
Названия строк». У макеті на рис. 7 у параметрі «Названия строк» розташовано два елементи — «Покупатель» і «Значение». Водночас, як у макеті на рис. 10, область «Названия строк» складається з одного елемента — «Покупатель». А поле «Значения» знаходиться в області «Названия строк». Виконуємо такі дії:1) ставимо покажчик миші на елемент «
Значения» (рис. 10);2) утримуючи натисненою ліву кнопку, переміщаємо цей елемент до області «
Названия строк». Поля в області даних розташувалися по вертикалі, як і в таблиці на рис. 8.
СТВОРЕННЯ ЗВЕДЕНОЇ ТАБЛИЦІ ЗА КІЛЬКОМА КЛЮЧОВИМИ ПОЛЯМИ
Роботу зі зведеними таблицями ми продовжимо зі звіту, зображеного на рис. 8. У цьому звіті використано єдине поле для групування даних — це «
Покупатель». Тепер наше завдання буде таким: визначити для кожного покупця докладні відомості про обсяги продажів кожного товару. Виконуємо такі дії:1) відкриваємо файл зі звітом, ставимо покажчик активної комірки всередину зведеної таблиці. З’явиться вікно «
Список полей сводной таблицы» (рис. 4);2) в області
«Выберите поля для добавления в отчет:» ставимо галочку в рядку «НаимТов». Форма зведеної таблиці змінилася. Тепер вона має вигляд, як показано на рис. 11. Відбулися зміни і всередині вікна «Список полей сводной таблицы»: в області «Названия строк» з’явився додатковий елемент «НаимТов» (рис. 12). 
Висновок: Поставивши галочку напроти будь-якого елемента у вікні «Список полей сводной таблицы», ми переносимо цей елемент до області рядків. Щоб включити поле бази даних до іншої частини зведеного звіту, потрібно перетягнути це поле на відповідне місце у зведеній таблиці чи у вікні «Список полей сводной таблицы».
Повернемося до таблиці на рис. 11. У ній теж відбулися певні зміни. У колонці «Покупатель» біля кожного елемента з’явився прямокутник зі знаком «-» (мінус). Це — значок групування даних. Клацаємо лівою кнопкою по значку групування біля фірми «ООО «Эталон». Для цього покупця на екрані залишаться дані тільки про сумарні обсяги продажів, тобто група даних щодо фірми «ООО «Эталон» набере вигляду, як показано на рис. 8. При цьому значок групування зміниться, він набуде вигляду знака «+» (плюс). Інші дані у зведеному звіті залишаться без змін. Повторне клацання по значку групування розкриє деталізацію щодо «ООО «Эталон» до рівня товарів.
Зведену таблицю за двома полями ми отримали. Однак її зовнішній вигляд недостатньо наочний. Логічніше було б для кожного підприємства спочатку згрупувати дані за товарами, а потім показати значення за сумою та за кількістю продажів. Виконуємо такі дії:
1) відкриваємо документ, клацаємо лівою кнопкою миші всередині зведеної таблиці. З’явиться вікно «Список полей сводной таблицы» (рис. 12);
2) ставимо покажчик миші на полі «НаимТов» в області «Названия строк» вікна «Список полей сводной таблицы»;
3) коли курсор набуде хрестоподібної форми, натискуємо на ліву кнопку миші. Утримуючи її натисненою, переміщаємо поле «НаимТов» угору;
4) коли поле «НаимТов» займе позицію між елементами «Покупатель» і «Значения» (рис. 13), відпускаємо ліву кнопку миші. Фрагмент трансформованої зведеної таблиці показано на рис. 14.
У цьому звіті дані щодо «ООО «Эталон» показано у згорнутому вигляді. Іншу інформацію наведено докладно. Групування полів звіту змінилося. Тепер дані спочатку зібрано за покупцями. На другому рівні можна побачити деталізацію щодо товарів. Для кожного товару у зведеному звіті показано обсяги продажів за кількістю та вартістю. А в кінці групи товарів для кожного конкретного покупця показано проміжний підсумок (рядки «20» і «21» на рис. 14).
ЗВЕДЕНІ ТАБЛИЦІ ТА ПЕРЕХРЕСНІ ЗАПИТИ
Наші подальші перетворення зведеного звіту стосуються параметра «
Номера столбцов». За основу ми візьмемо таблицю, зображену на рис. 14. Виконаємо такі дії:1) переходимо на робочий лист, ставимо покажчик миші на полі «
НаимТов»;2) утримуючи натисненою ліву кнопку, перетягуємо це поле на комірку із заголовком «
Итог» (у прикладі на рис. 14 це комірка з адресою «D1»). Зведений звіт набув форми, як показано на рис. 15 (див. с. 31). Тепер у ньому назви товарів розташовано в окремих колонках. На перетині рядка з назвою підприємства та колонки з товаром можна побачити обсяги продажів у кількостях та в гривнях. 
Той самий результат можна отримати і в інший спосіб — скористатися вікном «Список полей сводной таблицы». Для цього виконуємо такі дії:
1) ставимо покажчик на елемент «НаимТов» в області «Названия строк»;
2) утримуючи натисненою ліву кнопку миші, перетягуємо цей елемент до області «Названия строк» (напрямок такого переміщення показано на рис. 16, див. с. 31);
3) коли елемент «НаимТов» займе своє місце, відпускаємо кнопку миші.
Зведена таблиця, показана на рис. 15, — це результат так званого перехресного запиту. Для бухгалтера робота з перехресними запитами дуже важлива. Це — не тільки інструмент зручного та компактного зображення даних. Перехресні запити дозволяють створити дуже потужну схему перевірки даних та пошуку помилок. Одну з наших статей щодо Excel ми повністю присвятимо цій проблемі.
Важливо!
Зведені таблиці — єдиний інструмент в Excel 2007 для реалізації перехресних запитів.А поки що підіб’ємо невеликий підсумок — які висновки можна зробити з нашого невеликого досвіду роботи з інструментом зведених таблиць Excel 2007?
Перше, що впадає в очі, — ми досі не зустрілися з вікном створення макета (як в Excel 2003). Структуру зведеного звіту в Excel 2007 доводиться будувати або безпосередньо на робочому листі, або у вікні «
Список полей сводной таблицы» (рис. 4). Забігаючи наперед, зауважу: звичного вікна роботи з макетом в Excel 2007 немає. На мій погляд, таке рішення є спірним. Але тут, як мовиться, «маємо те, що маємо», з розробниками не подискутуєш. Водночас не можна не побачити і позитивних моментів. Наприклад, в Excel 2007 поля в області даних можна розташувати не тільки одне під іншим, а й «розгорнути» їх по горизонталі (рис. 9). Така можливість — явна перевага нової версії програми, оскільки результуючий звіт простіше перетворити в повноцінну базу даних. Цікаво, що Excel 2003 відкриває зведені таблиці з горизонтальною орієнтацією полів, як показано на рис. 9. Він успішно їх оновлює, але створювати не може. Принаймні мені невідомо, як це зробити.В Excel 2007 істотно розширилися можливості вікна «
Список полей сводной таблицы». Фактично воно замінило собою формування макета зведеного звіту. Тепер у ньому можна сформувати структуру зведеної таблиці, настроїти параметри полів, видалити поле з обробки. У цьому вікні передбачено навіть можливість накладати фільтр на зведену таблицю. У попередній версії програми такої можливості не було. Звичайно, є й інші особливості, але ми з ними розберемося в міру подальшого вивчення зведених таблиць Excel 2007.А поки що завершимо нашу статтю і наступного разу подивимося: як можна змінити джерело обробки даних, які можливості відкриває Конструктор зведених таблиць Excel 2007, що нового з’явилося в параметрах зведених звітів, як користуватися додатковими обчисленнями, що дає дублювання полів у макеті зведеної таблиці.
Чекаю ваших запитань, листів, пропозицій та зауважень на
bk@id.factor.ua, nictomkar@rambler.ru або на форумі редакції www.bk.factor.ua/forum . Успішної роботи!
