Джозеф Ентоні

Джозеф Ентоні є фахівцем у сфері оподаткування, проживає у м. Портленд (шт. Орегон), пише про фінансові та податкові проблеми, з якими стикається малий бізнес. Надіслати листа Джозефу.

Великі та малі компанії накопичують значні обсяги даних у таблицях Excel. Це добре, але як краще впорядковувати та обробляти їх?

Один із шляхів — скористатися технологією зведених таблиць (Pivot Table), вбудованою у програму Microsoft Excel.

За допомогою зведених таблиць у підсумкові звіти можна швидко додавати рядки даних (наприклад, інформацію про продажі або наявність товарів). Після отримання звіту ви можете змінити формат, щоб створити інші звіти або підсумкові дані.

Припустимо, що у вас є таблиця, де дані про продажі впорядковані за покупцями. Якщо в ній є інформація про продажі за регіонами, торговими представниками або продуктами, то ви можете перетягнути дані про покупців із таблиці та перемістити у таблицю іншу категорію даних (наприклад, дані про торгових представників або продукти).

Білл Джелен, знавець Excel, який працює із електронними таблицями та іншими подібними програмами ще з часів Lotus 1-2-33,  називає зведені таблиці надзвичайно корисною річчю.

«Зведені таблиці надають можливість протягом двох годин ознайомитися з дуже складними функціями баз даних та за 20 секунд створити дуже потужні підсумкові звіти», — стверджує Джелен.

Джелен підтримує відомий веб-сайт MrExcel.com (www.mrexcel.com), присвячений Excel та рішенням на основі цієї програми. На сайті розміщено понад 500 статей про Excel, а також діє форум, в якому більше 75 тис. доступних для пошуку повідомлень.

Джелен зазначає, що за даними, зібраними на його веб-сайті, близько 60 % користувачів Excel не застосовують зведені таблиці взагалі.

Він демонструє мені, як на основі таблиці, що містить 50 тис. рядків із даними, виконавши менше десяти натискань кнопки миші, можна створити таблиці, в яких дані щодо продажів будуть впорядковані за покупцями, товарами або регіонами.

Джелен починає зі створення звіту про прибутки вигаданої компанії за товарами та регіонами:

1. Відкрийте таблицю PivotExample.xls, на яку вказує посилання. Вона містить вигадані дані щодо продажів.

2. Виберіть одну клітинку з даними. Нехай це буде клітинка A2.

3. У меню Дані виберіть команду Зведена таблиця.

4. На кроці 1 майстер перевіряє, що дані є у списку Excel. Це так, тому натискайте Далі.

5. На кроці 2 майстер перевіряє, чи містяться дані в діапазоні A1:G564. Знову натисніть Далі.

6. Якщо у вас Excel 2000 або новіша версія, наступний крок буде 3 із 3. Натисніть кнопку Макет у лівому нижньому кутку вікна майстра. Якщо у вас Excel 97, перейдіть до кроку 6..

7. У діалоговому вікні, що відкриється, всі імена полів подані як сірі кнопки праворуч. Ви можете перетягувати ці кнопки до одного із чотирьох розташувань: Рядок, Стовпець, Дані або Сторінка.

8. Перетягніть поле Прибуток в область даних. Значення зміниться на «Сума прибутків». Перетягніть поле Регіон в область Рядок. Перетягніть поле Продукт в область Стовпець. Натисніть OK.

9. У вікні майстра натисніть кнопку Готово, щоб перенести зведену таблицю на новий аркуш.

10. Все! 563 рядки впорядковані у новій таблиці на аркуші Sheet1.

«Добре те, що після отримання підсумків ви можете легко впорядковувати їх, натискаючи на один із заголовків та переносячи його у іншу частину звіту, — каже Джелен. — Це дуже зручно для побудови звітів потрібної форми, групування та підсумовування даних у будь-який спосіб».

Щоб реорганізувати дані, замініть поле Регіон на поле Клієнт, використовуючи зведену таблицю з нашого прикладу:

1. У Excel 2002 або новішій версії список доступних полів ви побачите на панелі Список полів зведеної таблиці. Клацніть поле Клієнт та перетягніть його до лінії між стовпцями А та В. Тут існує прихована можливість. Подивіться трохи нижче вказівника миші. Ви бачите піктограму, схожу на вікно розмітки зведеної таблиці? Одна її область буде виділена блакитний кольором (це доступна для перетягування область). В цьому випадку блакитна область буде зліва, нижче поля Регіон. Якщо ви випадково перетягнете поле у область даних, то це викличе плутанину. В такому разі натисніть клавіші Ctrl+Z, щоб скасувати дію.

2. Тепер погляньте на новий звіт Клієнти, який ви створили. Внизу, в рядку 32, ви побачите суму за полем Центральні. Це буде непоганий звіт для менеджера з продажів (наприклад).

3.  Наступний крок — позбавитися поля Регіон. Клацніть на сірому полі Регіон у зведеній таблиці та перетягніть його за межі таблиці. Коли під час перетягування ви побачите темно-червону літеру X нижче вказівника миші, це означає, що поле буде видалене. Клацніть у цьому місці.

Зведені таблиці можна використовувати для створення списку на зразок Top 10. За допомогою додаткових опцій можна підрахувати суму та обмежити її лише кращими 10 або 5 клієнтами, продуктами, регіонами продажу тощо. Оскільки зазвичай 90 % надходжень від продажу забезпечує лише 10 % клієнтів, це може бути надзвичайно важливим методом швидкого виявлення найбільш цінних клієнтів, продуктів тощо.

Скористайтеся нашим прикладом.

1. Клацніть двічі у полі Клієнти на клітинці A4.

2. Натисніть кнопку Додатково.

3. У вікні, що відкриється, ви зміните лише п’ять параметрів. Клацніть OK, щоб закрити вікно з додатковими параметрами, а потім клацніть ОК, щоб закрити вікно для обчислення полів таблиці.

4. У звіті тепер показано лише перших п’ять клієнтів.

Поле Клієнти буде виділено блакитним кольором, це означає, що таблиця відображає не всіх клієнтів.

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

Щоб отримати більше інформації про використання таблиць, завітайте на сторінки порталу, присвячені Excel, на сайт Microsoft Office Online або сайт Джелена MrExcel (www.mrexcel.com).