Якщо ви читаєте цей підручник, є велика ймовірність, що ви чули (або навіть використовували) зведену таблицю Excel. Це одна з найпотужніших функцій Excel (без жартів).
Найкраще у використанні зведеної таблиці полягає в тому, що навіть якщо ви нічого не знаєте в Excel, ви все одно можете робити з нею досить дивовижні речі, знаючи про це дуже просто.
Давайте розпочнемо.
Натисніть тут завантажити зразки даних і продовжити.
Що таке зведена таблиця і чому вам це важливо?
Зведена таблиця - це інструмент у Microsoft Excel, який дозволяє швидко узагальнити величезні набори даних (за кілька кліків).
Навіть якщо ви новачок у світі Excel, ви можете легко скористатися зведеною таблицею. Створювати звіти так само просто, як перетягувати заголовки рядків/стовпців.
Припустимо, у вас є набір даних, як показано нижче:
Це дані про продажі, які складаються з ~ 1000 рядків.
Він містить дані про продажі за регіонами, типом роздрібної торгівлі та клієнтами.
Тепер ваш начальник може захотіти дізнатися кілька речей з цих даних:
- Яким був загальний обсяг продажів у Південному регіоні у 2016 році?
- Що входить у п’ятірку кращих роздрібних продавців за обсягами продажів?
- Наскільки ефективність The Home Depot порівняно з іншими роздрібними торговцями на Півдні?
Ви можете продовжити і скористатися функціями Excel, щоб дати вам відповіді на ці питання, але що, якщо раптом ваш начальник придумає список із ще п’яти питань.
Вам доведеться повертатися до даних і створювати нові формули щоразу, коли відбуваються зміни.
Саме тут зводні таблиці Excel дуже зручні.
Протягом декількох секунд зведена таблиця дасть відповідь на всі ці питання (як ви дізнаєтесь нижче).
Але справжня перевага полягає в тому, що він може прийняти вашого вибагливого начальника, керованого даними, негайно відповідаючи на його запитання.
Це настільки просто, що ви можете взяти кілька хвилин і показати своєму начальнику, як це зробити самому.
Сподіваємось, тепер у вас є уявлення про те, чому зведені таблиці такі чудові. Давайте продовжимо і створимо зведену таблицю з використанням набору даних (показано вище).
Вставка зведеної таблиці в Excel
Нижче наведено кроки для створення зведеної таблиці з використанням даних, наведених вище:
- Клацніть будь -де в наборі даних.
- Перейдіть до пункту Вставка -> Таблиці -> Зведена таблиця.
- У діалоговому вікні Створення зведеної таблиці параметри за замовчуванням добре працюють у більшості випадків. Ось кілька речей, які слід перевірити:
- Таблиця/діапазон: Він заповнюється за замовчуванням на основі вашого набору даних. Якщо у ваших даних немає порожніх рядків/стовпців, Excel автоматично визначить правильний діапазон. При необхідності це можна змінити вручну.
- Якщо ви хочете створити зведену таблицю в певному місці, у опції «Виберіть, де ви хочете розмістити звіт зведеної таблиці», вкажіть розташування. Інакше створюється новий робочий аркуш із зведеною таблицею.
- Натисніть OK.
Як тільки ви натискаєте «ОК», створюється новий аркуш із зведеною таблицею.
Під час створення зведеної таблиці ви не побачите в ній жодних даних. Все, що ви побачите, - це назва зведеної таблиці та інструкція з одного рядка зліва та поля зведеної таблиці праворуч.
Тепер, перш ніж перейти до аналізу даних за допомогою цієї зведеної таблиці, давайте розберемося, які гайки та болти складають зведену таблицю Excel.
Гайки та болти зведеної таблиці Excel
Для ефективного використання зведеної таблиці важливо знати компоненти, які створюють зведену таблицю.
У цьому розділі ви дізнаєтесь про:
- Зведений кеш
- Область цінностей
- Площа рядів
- Площа стовпців
- Зона фільтрів
Зведений кеш
Щойно ви створюєте зведену таблицю з використанням даних, у бекенді щось відбувається. Excel робить знімок даних і зберігає їх у своїй пам’яті. Цей знімок називається Pivot Cache.
Коли ви створюєте різні подання за допомогою зведеної таблиці, Excel не повертається до джерела даних, а використовує зведений кеш для швидкого аналізу даних та надання підсумків/результатів.
Причина, по якій генерується зведений кеш, полягає в оптимізації функціонування зведеної таблиці. Навіть якщо у вас є тисячі рядків даних, зведена таблиця дуже швидко узагальнює дані. Ви можете перетягувати елементи у рядки/стовпці/значення/фільтри, і це миттєво оновить результати.
Примітка: Одним недоліком зведеного кешу є те, що він збільшує розмір вашої книги. Оскільки це репліка вихідних даних, під час створення зведеної таблиці копія цих даних зберігається у зведеному кеші.
Детальніше: Що таке зведений кеш і як його найкраще використовувати.
Область цінностей
Область значень - це те, що містить розрахунки/значення.
Виходячи з набору даних, показаного на початку підручника, якщо ви швидко хочете розрахувати загальний обсяг продажів за регіонами кожного місяця, ви можете отримати зведену таблицю, як показано нижче (ми побачимо, як це створити пізніше у підручнику) .
Помаранчевою областю виділяється область значень.
У цьому прикладі він має загальний обсяг продажів за кожен місяць для чотирьох регіонів.
Площа рядів
Заголовки ліворуч від області Значення складають область Ряди.
У наведеному нижче прикладі область рядків містить регіони (виділені червоним):
Площа стовпців
Заголовки у верхній частині області Значення становлять область Стовпці.
У наведеному нижче прикладі область Стовпці містить місяці (виділені червоним):
Зона фільтрів
Область фільтрів - це додатковий фільтр, який можна використовувати для подальшого детального аналізу набору даних.
Наприклад, якщо ви хочете бачити продажі лише для роздрібних продавців Multiline, ви можете вибрати цей варіант зі спадного меню (виділено на зображенні нижче), а зведена таблиця оновлюватиметься лише даними для роздрібних продавців Multiline.
Аналіз даних за допомогою зведеної таблиці
Тепер давайте спробуємо відповісти на запитання, використовуючи створену зведену таблицю.
Натисніть тут завантажити зразки даних і продовжити.
Щоб аналізувати дані за допомогою зведеної таблиці, потрібно вирішити, як ви хочете, щоб підсумок даних виглядав у кінцевому результаті. Наприклад, вам можуть знадобитися всі регіони зліва та загальний обсяг продажів поруч із ним. Якщо ви маєте це на увазі, ви можете просто перетягнути відповідні поля у зведеній таблиці.
У розділі Поля зведеної таблиці у вас є поля та області (як виділено нижче):
Поля створюються на основі даних бекенда, що використовуються для зведеної таблиці. Розділ "Області" - це місце, де ви розміщуєте поля, і відповідно до того, де знаходиться поле, ваші дані оновлюються у зведеній таблиці.
Це простий механізм перетягування, де ви можете просто перетягнути поле і помістити його в одну з чотирьох областей. Як тільки ви це зробите, він з’явиться у зведеній таблиці на аркуші.
Тепер давайте спробуємо відповісти на питання, які виник у вашого менеджера за допомогою цієї зведеної таблиці.
Q1: Якими були загальні продажі в Південному регіоні?
Перетягніть поле «Регіон» у області «Рядки» та поле «Дохід» у зоні «Значення». Він автоматично оновлює зведену таблицю на аркуші.
Зауважте, що як тільки ви опускаєте поле Дохід у області Значення, воно стає Сумою доходу. За замовчуванням Excel підсумовує всі значення для певної області та показує загальну суму. Якщо ви хочете, ви можете змінити це значення на кількість, середнє значення або інші статистичні показники. У цьому випадку сума - це те, що нам потрібно.
Відповідь на це питання буде 21225800.
Q2 Що входить у п’ятірку кращих роздрібних продажів за обсягом продажів?
Перетягніть поле Клієнт в області Рядок і Дохід у області значень. Якщо у розділі області є інші поля, і ви хочете їх видалити, просто виберіть його та перетягніть із нього.
Ви отримаєте зведену таблицю, як показано нижче:
Зауважте, що за умовчанням товари (в даному випадку клієнти) сортуються в алфавітному порядку.
Щоб отримати п’ять кращих роздрібних продавців, ви можете просто відсортувати цей список і використовувати п’ять найменувань клієнтів. Зробити це:
- Клацніть правою кнопкою миші будь-яку клітинку в області Значення.
- Перейдіть до Сортування -> Сортувати від найбільшого до найменшого.
Це дасть вам відсортований список на основі загальних продажів.
Q3: Як ефективність The Home Depot порівняно з іншими роздрібними торговцями на Півдні?
Ви можете проаналізувати це питання, але давайте просто спробуємо порівняти продажі.
Перетягніть поле регіону в області рядків. Тепер перетягніть поле Клієнт у область Рядки під полем Регіон. Якщо ви це зробите, Excel зрозуміє, що ви хочете класифікувати свої дані спочатку за регіонами, а потім за клієнтами в регіонах. У вас буде щось, як показано нижче:
Тепер перетягніть поле Дохід у область Значення, і ви отримаєте продажі для кожного клієнта (а також загального регіону).
Ви можете відсортувати роздрібних продавців на основі показників продажів, виконавши наступні кроки:
- Клацніть правою кнопкою миші клітинку, яка має ціну продажів для будь-якого продавця.
- Перейдіть до Сортування -> Сортувати від найбільшого до найменшого.
Це дозволить миттєво відсортувати всіх роздрібних торговців за вартістю продажів.
Тепер ви можете швидко переглянути південний регіон і визначити, що продажі Home Depot склали 3004600, і це було краще, ніж у чотирьох роздрібних торговців у південному регіоні.
Зараз існує кілька способів зняти шкіру з кішки. Ви також можете помістити Регіон у область Фільтр, а потім вибрати лише Південний регіон.
Натисніть тут завантажити зразки даних.
Сподіваюся, цей підручник надасть вам основний огляд зведених таблиць Excel і допоможе вам розпочати його.
Ось ще деякі підручники зведеної таблиці, які вам можуть сподобатися:
- Підготовка вихідних даних для зведеної таблиці.
- Як застосувати умовне форматування до зведеної таблиці в Excel.
- Як групувати дати в зведених таблицях у Excel.
- Як групувати числа в зведеній таблиці в Excel.
- Як фільтрувати дані у зведеній таблиці в Excel.
- Використання зрізів у зведеній таблиці Excel.
- Як замінити порожні клітинки нулями у зведених таблицях Excel.
- Як додати та використовувати обчислювані поля зведеної таблиці Excel.
- Як оновити зведену таблицю в Excel.
