Зведена таблиця Excel: як створити і працювати? Робота зі зведеними таблицями Excel

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

На щастя, порівняно недавно з`явився MS Office 2010-2013, який не тільки включає в себе ряд оновлених програм для обробки текстових файлів, таблиць, баз даних і презентацій, але і дозволяє працювати з ними одночасно декільком працівникам, що в корпоративному середовищі просто безцінне.

Чому саме нові версії?

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

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

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

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

Що це таке і для чого вона потрібна?

Найбільш значущим зміною в нових версіях «Офісу» є їх повністю перекроєний інтерфейс, який був названий творцями Ribbon (стрічка). У стрічках всі 1500 команд зручно згруповані за категоріями, а тому вам не доведеться їх довго шукати. Щоб повністю відповідати цим параметрам, розробники додали в Excel ще й поліпшені зведені таблиці.

Сьогодні розглянемо Excel «для чайників». Зведені таблиці - це спеціальний інструмент, в якому наочно групуються результати якого-небудь процесу. Простіше кажучи, з їх допомогою можна побачити, скільки тих чи інших товарів продав кожен продавець за свою робочу зміну. Крім того, їх використовують в тих випадках, коли необхідно:

• Підготувати аналітичні викладки для написання підсумкових звітів.
• Вирахувати кожен їхній показник окремо.
• Неунікальна даних за їх типами.
• Зробити фільтрацію і поглиблений аналіз отриманих даних.

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

Створюємо потрібний документ

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

• Час продажу.
• Проданий товар.
• Вартість всього збуту.

Таким чином, між усіма параметрами в кожному конкретному стовпці утворюється зв`язок: припустимо, що кросівки були продані в 9:00 ранку, причому прибуток склав n-рублів.

Форматування таблиці

Приготувавши все вихідні відомості, ставите курсор до першої комірки першого ж стовпчика, відкриваєте вкладку «Вставка», після чого клікаєте по кнопці «Зведена таблиця». Відразу з`явиться діалогове вікно, в якому ви можете виконати наступні операції:

• Якщо ви відразу ж натиснете на кнопку «ОК», то зведена таблиця Excel відразу ж буде виведена на окремий лист.
• Повністю налаштувати виведення.

В останньому випадку розробники дають нам можливість визначити діапазон комірок, в який буде виводитися шукана інформація. Після цього користувач повинен визначити, де конкретно буде створена нова таблиця: на вже існуючому або ж на новоствореному аркуші. Натиснувши «ОК», ви тут же побачите перед собою готову таблицю. На цьому створення зведених таблиць в Excel практично закінчено.

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

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

Створення і угрупування часових рядів

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

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

Використовувані формули

Для прикладу новостворені стовпці можна назвати «Рік», «Місяць», «Місяці-Роки». Щоб отримати цікаві для нас дані, в кожен з них доведеться прописати окрему формулу для розрахунків:

• У «Річний» вставляємо формулу виду: «= ГОД» (посилаючись при цьому на дату).
• Місяць потрібно доповнити виразом: «= МІСЯЦЬ» (також з посиланням на дату).
• У третій стовпчик вставляємо формулу виду: «= СЦЕПИТЬ» (посилаючись при цьому на рік і на місяць).

Відповідно, отримуємо три стовпчики з усіма вихідними даними. Зараз необхідно перейти в меню «Зведена», клацаємо по будь-якому вільному місцю в таблиці правою клавішею миші, після чого в розпочатому контекстному меню вибираємо пункт «Оновити». Зверніть увагу! Уважно пишіть формули в зведених таблицях Excel, так як будь-яка помилка призведе до неточності прогнозу.

Як проводити аналіз продажів по роках?

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

Щоб отримати наочну динаміку місячних змін продажів по роках, поле «Місяць» потрібно перетягнути прямо у річній стовпець, раніше вже переміщений в «Назва стовпців». Виконавши цю операцію, ви побачите таблицю в наступному поданні:

• Сума продажів конкретного товару за весь рік.
• Динаміку продажів кожного з них по роках.

Прибираємо дані з видачі

Припустимо, що з видачі нам потрібно прибрати дані за жовтень, так як у нас немає повної статистики по ньому. У зведеній таблиці шукаємо «Фільтр звіту», після чого перетягуємо на нього «Рік - місяць».

Над таблицею з`являється фільтр, в якому потрібно поставити прапорець навпроти пункту «Виділити кілька елементів». Після цього в списку, що з`явився знімаємо прапорець з жовтня, після чого натискаємо на «ОК».

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

Розраховуємо прогнози

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

Щоб зробити це, ставимо курсор в полі «Загальний підсумок», після чого натискаємо на кнопку «Видалити загальний підсумок». Після цього виконуємо автоматичне оновлення зведеної таблиці Excel способом, описаним нами вище (тиснемо на праву клавішу миші, вибираємо «Оновити»).

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

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

Використовуємо інші джерела даних для формування зведеної таблиці

Пора розглянути більш складні уроки Excel. Зведена таблиця може бути створена на основі документів, які були відредаговані іншими користувачами.

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

Функція злиття в пізніх версіях «Офісу» практично не змінилася в порівнянні з Office 2003. Проте запити до баз даних для вибору джерела даних можуть виконуватися із застосуванням Microsoft Query. Щоб отримати дані зведеної таблиці Excel, необхідна наявність двох об`єктів:

• Основний документ, що містить потрібну вам інформацію, а також поля, що вказують програмі Excel, які дані слід вставляти в документ. Основна таблиця є стандартним документом табличного процесора, однак необхідно вказати у вікні діалогу Злиття (Mail Merge Helper), що саме він повинен використовуватися як основний.
• Джерело даних, що містить ту інформацію, яка необхідна для складання прогнозу. Можна створити нову таблицю тим способом, який ми зазначали вище.

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

Варто тільки клікнути по потрібному вам полю лівою клавішею миші, і ви зможете подивитися дані джерела замість імен полів, які відображаються в основному документі. Поля можна вставляти в будь-яке місце документа.

Використовувані категорії

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

Коли все готово для створення складеного документа, вікно діалогу Merge надає користувачеві можливість виявити і усунути окремі помилки до їх появи. Коли все перевірено, то можна сміливо тиснути на кнопку «Об`єднати». Якщо ж злиття повинно здійснюватися через електронну пошту, то вийде запит на установку поштової програми (якщо у вас вона не встановлена).

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

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