Напевно, у вакансіях ви бачили схожу вимогу: «Знання Excel на рівні просунутого користувача (ВПР, зведені таблиці, формули)». Звучить складно, але насправді освоїти ці функції досить легко. Почнемо зі зведених таблиць: розберемося, що таке, навіщо вони потрібні як їх робити.

Що таке зведена таблиця Excel і чому це зручно

Зведена таблиця в Excel — це потужний інструмент аналізу та обробки великих обсягів даних. Він допомагає зрозуміти, як дані співвідносяться один з одним, виявити тенденції, закономірності, згрупувати їх за обраними критеріями та показати результат. Таблиця дозволяє швидко створити зведений звіт із сотень тисяч рядків даних за кілька кліків.

Безкоштовний профорієнтаційний проект

Пройдіть тест та визначте ваш напрямок у IT. Вигравайте призи, отримуйте подарунки та особистий план розвитку через безкоштовні гайди та кар'єрну консультацію.

Зведені таблиці створив розробник Піто Салас у 1986 році. Розробляючи програму для роботи з електронними таблицями Lotus Improv, він почав помічати дані закономірності, які виникали при об'єднанні різних таблиць. Він зрозумів, що це може бути корисним інструментом для аналізу даних і створив зведені таблиці. Microsoft додала функціональність зведених таблиць в Excel лише 1994 року. З того часу на зведених таблицях тримається багато в різних галузях: фінансах, маркетингу, продажах, закупівлях. І не лише фінансова. Джерело Наприклад, маркетолог складає маркетинговий план, в якому докладно описує поступ у різних каналах.Щоб керівник міг швидко та легко оцінити ефективність цього плану, маркетолог створює зведену таблицю, яка поєднує інформацію з усіх документів, що задіяні. У цій таблиці можна порівняти доходи, витрати та ефективність різних каналів в одному місці, без необхідності перемикатися між документами. Зведені таблиці можна використовувати навіть аналізу особистого бюджету. Наприклад, щоб подивитися, скільки грошей витратили на кожну категорію товарів протягом року, ви можете створити зведену таблицю, де рядки групуватимуться за категорією товарів, а стовпці — за місяцями.

Як зробити просту зведену таблицю

Починаємо роботу

Спробуємо зробити просту зведену таблицю. У нашому прикладі буде невелика ІТ-компанія, яка займається розробкою на замовлення. Проектів багато, тому без зведених таблиць не обійтись. У нашій таблиці будуть колонки "Назва проекту", "Тип проекту", "Команда", яка займається цим проектом, "Бюджет", який виділив замовник, та "Термін (у місяцях)" – час, який піде на виконання проекту. Ми використовуємо Excel 2021 року. Якщо у вас інша версія, зовнішній вигляд програми може трохи відрізнятися, але основні кроки будуть ті ж. Щоб створити зведену таблицю, перейдіть до розділу «Вставка» та виберіть «Зведена таблиця». Відкриється вікно створення таблиці. За замовчуванням воно виглядає так: Виберемо потрібну таблицю. Щоб це зробити, прописуємо назву аркуша (у нас «Проекти») та діапазон, який займає таблиця. Осередки можна прописати вручну або виділити потрібну область мишкою, тоді діапазон пропишеться автоматично. Зручніше створити зведену таблицю на новому аркуші – так дані джерела вам не заважатимуть.Натискаємо "Ок" і бачимо, що ми опинилися на новому аркуші. Справа з'явився конструктор зведених таблиць. В основній області перераховані назви стовпців, які були у нашій вихідній таблиці. Їх можна вибирати – ставити галочки поряд або переносити мишкою. Фільтри допоможуть приховати або відобразити потрібні дані, стовпці та рядки відповідають за зовнішній вигляд таблиці: дані будуть поділені по стовпцям або рядкам відповідно, а значення потрібні для обчислення даних.

Налаштовуємо зведену таблицю

Допустимо, керівник нашої компанії хоче подивитися, які проекти ведуть команди. Для цього він обирає стовпці «Команда» та «Назва проекту». За замовчуванням вони потрапляють у поле «Рядки». Тепер наша зведена таблиця виглядає так: Якщо вибрати спочатку Назва проекту, а потім Команда, таблиця виглядає зовсім по-іншому. Порядок вибору шпальт важливий. Подивимося на фільтри. Наша вихідна таблиця невелика, команд небагато, тому вивести все й одразу досить зручно. Якби команд чи проектів було більше, то таке відображення не підійшло б. Тут виручать фільтри. Наприклад, можна додати команду в поле Фільтри. Тоді всі наші команди стануть пунктами меню, а проекти будуть розташовуватися нижче і тільки для обраної команди. Гарно, зручно, наочно. А що коли керівник захоче подивитися, скільки заробить кожна команда на проектах? Вибираємо "Команда", вибираємо "Бюджет". Зверніть увагу, що "Команда", як завжди, опинилася в полі "Рядки", а ось "Бюджет" потрапив у "Значення" автоматично, тому що в цьому стовпці були записані числа. Зведена таблиця сформована, можна переглянути необхідну інформацію. Загальний результат вважався автоматично.Якщо ми перенесемо "Команду" до "Стовпців", то побачимо, що зовнішній вигляд таблиці змінився. Вибір зовнішнього вигляду залежить від особистих уподобань та від мети створення таблиці. Працюючи з числовими значеннями часто застосовуються фільтри. Наприклад, так наша IT-компанія може дізнатися, який із напрямків їй приносить більше грошей. Для цього потрібно перемістити «Тип проекту» у поле «Фільтри», а «Бюджет» сам потрапить до «Значень». Тепер можна вибирати напрямок і дивитися, який із них найвигідніший для компанії. Можна перенести «Тип проекту» в «Стовпці» та побачити відразу всі напрямки в одній таблиці. Так створюються прості зведені таблиці. Якщо потрібно виконати більш глибокий аналіз даних, можна використовувати складніші варіанти. Вони дозволяють працювати з даними у різних вимірах, виконувати угруповання, обчислювати відсотки, середні значення та багато іншого.

Як оновити дані у зведеній таблиці

Що робити, якщо вихідні дані змінились? Наприклад, якийсь проект призначили Team 2, а чи не Team 1? Перейдіть до розділу «Аналіз зведеної таблиці» та натисніть «Оновити». Жодних підтверджуючих вікон не з'явиться, але актуальні дані підтягнуться до вашої зведеної таблиці. Якщо ж ваша таблиця змінилася суттєво, наприклад, додалися новий рядок або новий стовпець, тобто змінився її діапазон, потрібно вибрати новий. Для цього перейдіть до розділу «Аналіз зведеної таблиці» та натисніть «Джерело даних». Ви опинитеся на аркуші з вашими вихідними даними. Тут з'явиться вікно, в якому ви зможете вказати новий діапазон (або виділити потрібну область мишкою). Натисніть «Ок» та продовжуйте роботу над зведеною таблицею.

Як зробити зведену таблицю з кількох аркушів

Зведені таблиці дозволяють об'єднувати дані з кількох джерел в одну таблицю для зручного аналізу та порівняння. Наприклад, у вас є інформація, яка зберігається на різних аркушах. У зведеній таблиці можна переглядати дані, не перемикаючись між вкладками Excel. Припустимо, ми маємо ще одну таблицю на іншому аркуші — з тимлідами (керівниками команд). Компанія у нас маленька, тому керівники команд відповідають за проекти, які веде їхня команда. Керівник вирішує подивитися, за скільки проектів зараз відповідає кожен тимлід. Тут знадобиться зведена таблиця. Але спочатку потрібно змінити наші таблиці — зробити їх «розумними». Для цього у розділі "Вставка" потрібно вибрати "Таблиця". Після цього виділити потрібний діапазон або прописати розташування даних вручну. Обов'язково вибирайте опцію «Таблиця із заголовками». Так ви зможете змінити назву таблиці із системного «Таблиця1» на свою. Робимо це обох таблиць. Тепер вони виглядають так: Після цього створюємо зведену таблицю наших вихідних даних: таблиці з проектами. Обов'язково ставимо галочку у вікні «Додати ці дані до моделі даних». Ми вже звичний конструктор зведених таблиць. Але як пов'язати дані з наших двох таблиць? Звернемося до конструктора праворуч. Перейдемо до розділу «Всі». Побачимо обидві наші таблиці! «Проекти» та «Тімліди» — це назви, які ми вигадали. Клікаємо на кожен, розкриваємо списки та бачимо назви стовпців. Щоб переглянути навантаження на тимлідів, у списку «Проекти» ми виберемо стовпець «Назва проекту», у списку «Тімліди» — стовпець «Тімлід». Розмістимо їх у полях «Стовпці», щоб надати таблиці потрібний вигляд, та у полі «Значення», щоб автоматично порахувати кількість проектів.В нас з'являється жовте поле вгорі. Якщо вибрати «Виявлення», Excel сам зрозуміє, що ми хочемо зробити. Але за складних процедур це може не спрацювати. Виберемо "Створити" і зробимо все вручну. Відкрилося віконце створення стосунків. У «Таблиця» виберемо основну таблицю (з проектами), у «Зв'язана таблиця» – другий аркуш (з тимлідами). У «Стовпець» і «Пов'язаний стовпець» мають бути одні й самі стовпці. Щоб зв'язати дві таблиці, у них має бути загальний стовпець, яким і буде створено ставлення. У нас це команда. Вуаль! Після натискання «Ок» побачимо бажану таблицю і зрозуміємо, що тимлід Петров А. завантажений найменше і ми можемо дати йому ще проектів!

Корисні функції зведеної таблиці

Розкажемо про кілька функцій, які можуть бути корисними під час роботи зі зведеними таблицями.

Обчислювані поля

Ця функція потрібна для додаткових математичних процесів з даними зведеної таблиці в Excel. Припустимо, в компанії прийнято, що 3% від усього бюджету команди йде на її витрати: п'ятничні посиденьки з піцею, подарунки на дні народження та інші невеликі радості. Це також можна порахувати в зведених таблицях. Зробимо розподіл бюджетів за командами, як і у минулому прикладі. У розділі «Аналіз зведеної таблиці» потрібно знайти меню «Поля, елементи та набори». Клацаємо і вибираємо «Полі, що обчислюється». З'являється вікно, в якому можна написати ім'я нового стовпця, у нас це «Витрати команди» і вибрати стовпці, які знадобляться для розрахунку нового. Ми вибираємо "Бюджет" (він сам підставиться в полі "Формула") і дописуємо множення на 0,03 (3% від бюджету). Після натискання "Ок" у нашій зведеній таблиці з'явився новий стовпець. Все пораховано!

Рекомендовані таблиці

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

Підсумовуючи

• Продумайте, які дані ви хочете проаналізувати. Що ви хочете дізнатися? Яка інформація вам потрібна для цього?
• Виберіть правильні поля для вашої зведеної таблиці. Вони визначають, як будуть групуватися ваші дані та які обчислення виконуватимуться.
• Використовуйте фільтри та сортування, щоб налаштувати зведену таблицю. Фільтри та сортування можуть допомогти вам зосередитись на конкретних даних або побачити дані в новому світлі.
• Додайте діаграми та графіки до свого зведення. Вони допоможуть візуалізувати дані та зробити їх зрозумілішими.

А якщо захочете піти далі в освоєнні Excel, то можете познайомитися з курсом «Excel + Google-таблиці».

Аналітики впливають на зростання бізнесу. Вони з'ясовують, який товар і коли більше купують. Вважають юніт-економіку. Оцінюють окупність рекламної кампанії. Тому компанії шукають та переманюють таких фахівців.

Якщо необхідно протягом короткого терміну перетворити величезну кількість даних на короткий звіт, можна скористатися інструментом Excel, як зведені таблиці. Крім цього, вони дають можливість здійснювати аналіз різними методами простим перетягуванням полів із однієї області до іншої. На жаль, багато користувачів не можуть оцінити цей інструмент в повній мірі.Як правило, вони не розуміють весь спектр можливостей, які він надає.

Чим зведені таблиці корисні

Давайте уявимо ситуацію, що немає взагалі жодних зведених таблиць. Припустимо, ви працевлаштовані в організації, яка продає певні товари. Їхній асортимент невеликий – всього 4 штуки. І лише кілька десятків покупців із різних областей здійснюють замовлення. Після того, як продаж товару було здійснено, дані вносяться до окремого рядка.

Іноді начальство ставить завдання створити короткий звіт, з цих даних, розподілених у різних регіонах. Щоб її виконати, необхідно спочатку зробити шапку таблиці, до якої вноситимуться лише значення товарів та регіонів. Після цього створюється копія стовпця з товарами, а дублікати видаляються.

Далі необхідно здійснити транспонування стовпця в рядок. Над областями здійснюються такі самі дії, тільки транспонування не використовується. В результаті вийде така шапка для майбутнього звіту.

Ця таблиця має бути заповнена, тобто треба дізнатися суму по виручці з потрібних регіонів за кожною з товарних позицій. Це завдання легко виконується, якщо використовувати функцію СУМІСЛИМН. Крім того, слід додати підсумки. Після цього з'явиться зведений звіт щодо кожної області.

Ура, тепер ви задоволені і несете звіт начальнику, який окинув його поглядом і попросив втілити ще ряд ідей:

1. При складанні звіту використовувати не прибуток, а прибуток.

1. Показати по шпальтах – регіони, а по рядах – товари.
2. Робити аналогічні звіти кожного менеджера окремо.

Щоб виконати це завдання, доведеться витратити багато часу, навіть якщо рівень вашого професіоналізму досить високий.При цьому великий ризик припустити багато помилок. Їх можна запобігти за допомогою зведеної таблиці. Тоді ці завдання вдасться виконати буквально за п'ять хвилин (а за належного досвіду – і швидше).

Створення зведеної таблиці Excel

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

Далі нам слід виділити будь-яку комірку і відкрити вкладку «Вставити». У лівій частині стрічки розташовано два пункти «Зведена таблиця» і «Зведені таблиці, що рекомендуються».

Друга опція потрібна для тих людей, які не знають, як має виглядати підсумковий результат. Виходячи з того, які дані введені в діапазон, Excel запропонує декілька варіантів. Користувачеві залишається лише вибрати той, який йому найбільше підходить.

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

Може бути завдання зробити зведену таблицю самостійно. У такому разі необхідно натиснути на звичайну кнопку "Зведена таблиця". У вікні необхідно ввести діапазон, який буде використовуватися для створення зведеної таблиці і місце її розташування (стандартний варіант – новий лист).

Як правило, жодних додаткових змін на цьому етапі не потрібно.Після того, як користувач підтвердить дії шляхом натискання кнопки «ОК», на новому аркуші з'явиться макет зведеної таблиці, який поки не містить жодних даних.

Щоб налаштувати його, необхідно скористатися панеллю "Поля зведеної таблиці", розташованої у правій частині аркуша.

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

Щоб видалити поле, просто зняти відповідний прапорець.

Складові компоненти

До складу зведеної таблиці входять такі компоненти:

1. Область значень. Під нею мається на увазі основна частина таблиці зі значеннями. Excel їх отримує за допомогою агрегування вихідних даних методом, який вибере користувач. Як правило, це робиться за допомогою підсумовування. Цей метод встановлений автоматично за умови, що всі дані у вихідному діапазоні знаходяться в комірці, що має числовий формат. Якщо ж хоча в одному осередку є текст або відсутня будь-яка інформація, то автоматично буде здійснюватися підрахунок кількості осередків. Усього є близько 20 різних видів обчислень. Найпростіше змінити його з допомогою правого кліка по будь-якій клітинці необхідного поля зведеної таблиці і здійснити вибір методу агрегування.
2. Область рядків. Власне, сюди входять назви рядків, що знаходяться у крайньому лівому стовпці.Це все значення стовпця, унікальні у своєму роді та які були обрані користувачем. Тут може бути кілька полів. Така таблиця називається багаторівневою. Як правило, тут знаходяться будь-які не кількісні дані, такі як назви товарів, регіонів тощо.
3. Область стовпців. Те саме, що й область рядків, тільки стосується стовпців. Сюди можуть входити роки, місяці, і навіть групи своєї продукції.
4. Область фільтра. Застосовується у тому, щоб показувати лише певні значення, відповідні конкретній ознакі. Наприклад, є можливість відображення даних лише з конкретної галузі, за певний період тощо. У цьому випадку необхідно помістити поле фільтрації в область фільтра та вибрати потрібне значення у списку, який розкривається.

Саме область фільтра дозволяє зробити гнучке налаштування зрізу даних. Теоретично воно дещо незрозуміло звучить. Давайте зробимо все на конкретному прикладі. Давайте зробимо таку ж таблицю, як і вище. Для цього необхідно в область "Значення" перетягнути поле "Виручка", в область "Рядки" – "Область", а в область "Стовпці" – "Товар".

Після цього перед нами з'явиться повноцінна зведена таблиця.

І що дивно? Щоб її створити, знадобилося лише 10 секунд.

Операції зі зведеними таблицями

Редагування зведеної таблиці – така ж проста задача, як і її створення. Давайте подивимося, як у практиці втілюються у життя ті ідеї, які директор запропонував вище.

Давайте здійснимо заміну виручки на прибуток.

За допомогою простого перетягування можна поміняти місцями товари та області.

У нашому випадку ми скористалися областю фільтрів, куди розмістили поле «Менеджер».

Існує ще кілька методів фільтрації даних, але цей – основний та найпростіший. Все, достатньо лише кількох секунд, щоб виконати це нехитре завдання.

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

Корисні поради щодо підбору джерела даних

Щоб виконувати завдання зі зведеними таблицями найефективніше, потрібна відповідність вихідних даних ряду критеріїв. Дуже важливо, щоб над кожним стовпчиком була назва, що дозволяє визначати, що за дані. Крім цього, дотримуйтесь таких корисних рекомендацій:

1. Найкраще як джерело даних використовувати розумну таблицю. Її головна перевага полягає в тому, що кожна колонка має свою назву, і якщо додавати колонки або рядки діапазон даних автоматично розширюється на відповідну їх кількість.
2. Не рекомендується повторення груп у колонках. Так, бажано, щоб дати розташовувалися в одному стовпці, а не розбивалися місяцями в окремих колонках.
3. Правильно форматуйте поля. Важливо, щоб усі числа були у числовому форматі, а дати були дійсно у форматі дати. Інакше Excel буде важко правильно згрупувати та обробити дані. В принципі Excel автоматично визначає тип даних, але в деяких випадках можливі глюки. Тому перед тим, як створювати зведену таблицю, необхідно переконатися, що вся інформація у вихідній знаходиться у правильному форматі.

Як оновлювати дані у зведеній таблиці

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

Також аналогічну операцію можна провернути через вкладку "Дані" на стрічці. Для цього достатньо натиснути кнопку «Оновити все», виділену червоним прямокутником на скріншоті.

Здається, що це незручно і значно краще було б зробити автоматичне оновлення. Але насправді, у такому разі витрачалося б надто багато оперативної пам'яті. Для економії ресурсів комп'ютера робота здійснюється не безпосередньо, а через посередницьку ланку у вигляді кешу.

Як створити дашборд в Excel

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

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

Як правило, в компаніях створення розумних таблиць і обмежуються, в той час як дашборди мають величезну кількість переваг:

1. Дає можливість гнучко керувати елементами звіту, наголосивши на найбільш актуальних показниках або замінювати їх у разі потреби.
2. Дозволяє компактно вмістити всю необхідну інформацію буквально на одному аркуші, що дозволяє заощаджувати папір, якщо начальство вимагає друкувати звіти.
3. За допомогою дашбордів легко порівнювати ключові показники за різні періоди.

Крім іншого, вміння працювати з дашбордами говорить про професіоналізм співробітника. Така навичка відразу піднімає його на голову вище в очах керівництва.

Існує безліч способів створення дашбордів Excel, але завжди потрібно починати з ескізів прямо на аркуші паперу. Потрібно відмалювати, які блоки на яких місцях будуть. Далі значно простіше створюватиме дашборд. Зокрема створити дашборд в Excel можна за допомогою надбудови PowerView. Також візуалізація може здійснюватися такими методами:

1. Фігури та об'єкти Word Art. Вони дозволяють малювати все, що завгодно, аж до інженерних креслень. Крім цього, є безліч текстових міток, які дозволяють описати будь-яку складову дашборду.
2. Використання зведених таблиць.
3. Графіки, які можуть як дані також використовувати вихідний діапазон.

Усі вони можуть використовуватись для створення дашбордів. Причому завдяки цим засобам візуалізації можна робити їх буквально за короткий термін.

Висновки

Таким чином, створювати зведені таблиці в Excel – зовсім нескладне завдання, яке допоможе заощадити багато годин під час створення звітів. Достатньо натиснути лише кілька кнопок, і звітна таблиця буде створена автоматично. Далі її можна редагувати та підганяти під себе. Успіхів.