Консолідація даних

Після створення декількох списків або таблиць зі схожою структурою часто буває корисно об'єднати окремі набори даних для відображення в так називаному консолідованому списку або таблиці. Для цієї мети призначена команда Консолидация... з меню Данные Консолідація - це один із видів об'єднання даних і служить для спільного аналізу даних з декількох джерел за допомогою використання спеціальних функцій.

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

1. Вибрати місце розташування узагальнюючих даних.

Обов'язково потрібно перевірити, чи помістяться консолідовані дані в передбаченому діапазоні. У противному випадку, частина вихідних даних може просто ігноруватися.

2. Скористайтеся командою Данные\Консолидация.

3. Зі списку, що розкривається, виберіть одну з функцій для аналізу даних.

4. У поле Ссылки вкажіть перший діапазон вхідних даних. Для більш зручного виділення діапазону комірок діалогове вікно можна згорнути або перемістити. Якщо діапазон даних має ім'я, уведіть його в поле Ссылки. Часто доводиться використовувати дані, розташовані на різних робочих листах. У цьому випадку потрібно вибрати потрібний лист, а потім виділити необхідний діапазон даних. Якщо в якості вихідних даних використовується кілька робочих книг, їх необхідно відкрити перед використанням команди Консолидация.

5. Після того як діапазон вхідних даних обраний, його потрібно додати в список діапазонів. Для цього скористайтеся кнопкою Добавить (рис. 28.).

6. Якщо потрібно, щоб консолідовані дані обновлялися автоматично при зміні набору вхідних даних, встановіть прапорець Создавать связи с исходньіми данными.

7. Натисніть на кнопку ОК.

Рис. 28. Діалогове вікно Консолідація

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

Розташуємо на чотирьох листах однакові за структурою таблиці за січень, лютий і березень та перший квартал, (табл. 1.) Робочим листам дамо відповідні назви: січень, лютий, березень, 1 квартал, (рис. 29.)

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

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

Таблиця 1.

Код товару	Назва товару	Ціна товару	Кількість проданого товару	Сума проданого товару
	Ручка кулькова	0,80
	Ручка гелева	1,20
	Олівець простий	0,50
	Олівець механічний	1,50

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

Потім внести кількість проданого товару по кожному місяцю окремо. Для створення квартального звіту по продажу товарів за квартал, відкриємо лист 1 квартал, виділимо діапазон комірок D2:D5 тавиконаємо команду Данные\Kонсолидация.

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

Клацніть по віконцю Ссылки. Тепер потрібно вказати, що саме необхідно консолідувати. Клацніть по ярлику Січень і виділіть інтервал D2:D5, де знаходяться дані про продаж товарів у січні. Клацніть по кнопці Добавить. Перейдіть на лист Лютий і повторіть цю процедуру. Зрозуміло, що і з березнем потрібно проробити те ж саме. А от тепер можна і клацнути по кнопці ОК. Перевірте: на листі 1 квартал повинні бути підсумовані дані, (рис.29. та 30.)

Рис. 30. Вигляд таблиці з консолідованими даними

Рис. 30. Вигляд таблиці з розкритими консолідованими даними

Зведені таблиці. Зведені таблиці являють собою засіб аналізу і підсумовування інформації в довгих списках Ехсеl. Зведені таблиці - потужний і ефективний засіб упорядкування й аналізу даних.

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

Зведена таблиця в Ехсеl створюється за допомогою Мастeра сводых таблиц. Ехсеl пропонує послідовність вікон діалогу, що дозволяють зробити всі необхідні кроки для одержання даних, на основі яких будується зведена таблиця для аналізу. Мастер сводных таблиц дозволяє зазначити, як повинні бути використані в зведеній таблиці мітки рядків і стовпців і яким саме чином повинні бути подані дані в її полях.

При побудові зведених таблиць на основі списку слід враховувати:

• Заголовки стовпців. Дані в першому рядку списку використовуються в Ехсеl як імена полів зведеної таблиці, тому в списку обов'язково повинні бути заголовки стовпців.

• Імена діапазонів. Якщо для списку застосовується ім'я діапазону, то при зміні списку зведену таблицю легше обновлювати іредагувати.

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

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

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

Рис.31. Список продаж комп'ютерів за лютий місяць

На основі цього списку можна створити декілька зведених таблиць.

• Загальну кількість проданих комп'ютерів кожного типу.

• Загальну кількість проданих комп'ютерів кожного типу, придбаних відповідним покупцем.

• Загальну кількість проданих комп'ютерів кожного типу, придбаних на певну дату відповідним покупцем.

• Загальну суму, отриману від продажу певного типу комп'ютерів.

• Загальну суму, отриману від продажу певного типу комп'ютерів на відповідну дату.

• Загальну суму, отриману від продажу певного типу комп'ютерів на відповідну дату кожним покупцем.

• Максимальне значення суми від продажу кожного типу комп'ютера на відповідну дату.

Список повинен містити тільки один ряд заголовків стовпців. У зведеній таблиці вони будуть представлені полями.

Щоб створити зведену таблицю необхідно виконати наступні дії:

1. Перед запуском майстра зведених таблиць потрібно виділити у списку будь-яку комірку. Ехсеl автоматично виділить діапазон комірок, що утворююь список, так само як при сортуванні або фільтрації даних.

2. В меню Данные вибрати команду Сводная таблица. На екрані з'явиться перше діалогове вікно Мастера сводных таблиц, зображене на рис. 32.

3. У першому вікні Мастера сводных таблиц необхідно вказати джерело даних, на підставі якого буде створюватися зведена таблиця. За замовчуванням приймається список або база даних Місrosoft Ехсеl. Потім натиснути на кнопку Далее.

4. У другому вікні Мастера сводных таблиц відображається діапазон комірок списку, як показано на рис.33. Ехсеl автоматично виділяє весь список, у якому знаходилася активна комірка перед запуском майстра зведених таблиць. Якщо автоматично вибраний не той діапазон комірок, ви можете виділити необхідний діапазон вручну, а також ввести діапазон, якщо, звичайно, він був створений.

Рис. 32. Перше діалогове вікно Мастера сводных таблиц

Рис. 33. У діалоговому вікні відображене абсолютне посилання

на вибраний діапазон комірок

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

У третьому вікні Мастера сводных таблиц (рис.34) створюється безпосередньо структура зведеної таблиці. Поля бази даних можна розміщувати на запропонованому планшеті довільним чином аналізувати результати розрахунків. З правої сторони в діалоговому вікні з'являться кнопки з назвами полів, що представляють заголовки стовпців списку. Ці поля можна перетягувати мишею в одну з чотирьох областей зведеної таблиці.

Рис.34. Макет зведеної таблиці

Рис. 35. Макет зведеної таблиці за списком "Продаж комп'ютерів за лютий"

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

Рядок. У область Строка розміщують поле (називається поле рядків), дані якого утворять позиції по рядках зведеної таблиці.

Стовпець. У область Столбец розміщують поле (називається поле стовпців), дані якого утворять позиції по стовпцях зведеної таблиці.

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

Сторінка. У область Страница розміщують поле (називається полем сторінки), що використовується для фільтрації даних у зведеній таблиці.

Щоб помістити поле в ту або іншу область макета зведеної таблиці, необхідно перетягнути мишею ім'я поля в цю область. На рис.35. поле "Тип комп." поміщене в область Строка, поле "Код замовника" - в область Столбец, а поле "Сума до виплати" - в область Данные. У цьому прикладі розраховується загальна сума виплат за кожним типом комп'ютера на певну дату.

7. Закінчивши роботу над макетом зведеної таблиці потрібно натиснути на кнопку Далее. На екрані з'явиться останнє вікно Мастера сводных таблиц (рис.36).

8. У останньому вікні Мастера сводных таблиц виберіть місце для своєї зведеної таблиці. Можете помістити її або на новий робочий лист в активній робочій книзі, або в будь-яке інше місце.

Рис. 36. Діалогове вікно останнього (4) кроку Мастера сводных таблиц

• Новий лист. У активну робочу книгу додайте новий лист із зведеною таблицею.

• Існуючий лист. Зведена таблиця може бути розташована на вже існуючий робочий лист в активній або в будь-якій іншій книзі. Якщо вибрати перемикач существующий. лист, у полі введення з'являється курсор, що мигкотить. Якщо лист міститься в іншій відкритій робочій книзі, потрібно скористатися меню Окно, щоб відкрити на екрані книгу і вибрати лист та комірку на цьому листі, в якій буде починатися зведена таблиця. Якщо робоча книга закрита, вам доведеться вводити повний шлях до файлу книги, включаючи ім'я файлу книги, ім'я листа і посилання на комірку.

9. Далі натисніть на кнопку Готово, щоб завершити роботу з Мастером сводных таблиц. На екрані з'явиться зведена таблиця разом із панеллю інструментів Сводные таблицы (див.рис.37).

Рис. 37. Результат роботи Мастера сводных таблиц

Форматування і редагування зведеної таблиці

Зведена таблиця побудована таким чином, що можна легко вибирати і змінювати будь-які її частини. У табл.2. пояснюється, яка частина зведеної таблиці буде вибрана при натискуванні на тому чи іншому місці (як приклад узята таблиця на рис. 37.).

Таблиця 2. Вибір частин зведеної таблиці, що форматуються.

Місце натискування	Вибір частини, що форматується
Поле рядків (Тип. комп.)	Всі позиції рядків (типи комп'ютерів)
Позиція в рядку (РІ)	Всі дані в цьому рядку, включаючи загальний підсумок для рядка
Поле стовпців ("Код замовника)	Всі позиції стовпців (коди виконаних робіт)
Позиція в стовпці (1001)	Всі дані в цьому стовпці, включаючи загальний підсумок для стовпця
Поле даних	Вся таблиця {суми виплат)
Поле Общий итог стовпця або рядка	Всі загальні підсумки в цьому стовпці або рядку

Форматування застосовується для поліпшення зовнішнього вигляду таблиці. Найшвидший спосіб форматування таблиці - це застосування команди Автоформат. Потрібно натиснути на поле даних зведеної таблиці (виділиться вся таблицю), а потім в меню Формат вибрати команду Автоформат. У діалоговому вікні Автоформат вибрати один із декількох вмонтованих форматів, і таблиця відразу набуде професійного вигляду. Всю зведену таблицю також можна виділити, натиснувши Ctrl+shift+*.

Для форматування окремих частин зведеної таблиці потрібно вибрати комірки зведеної таблиці, які необхідно форматувати, а потім скористатися або кнопками на панелі інструментів Форматирование, або командою Ячейки в меню Формат.

Щоб змінити формат чисел, виберіть потрібні комірки в області даних зведеної таблиці, а потім на панелі інструментів зведеної таблиці натисніть на кнопку Поле сводной таблицы Відкриється діалогове вікно обчислення поля зведеної таблиці, зображене на рис.38. Далі необхідно натиснути на кнопку Формат у правій частині цього вікна. З'явиться вікно Формат ячеек, в якому можна встановити потрібний формат чисел для даних таблиці. У зведеній таблиці можна також замінити одне поле іншим або перевпорядкувати їх, а також згрупувати рядки і стовпці. Наприклад, якщо в стовпчиках показані місяці року, то перші три місяці можете згрупувати в перший квартал. Щоб видалити поле і замінити його іншим, спочатку необхідно вибрати комірку в зведеній таблиці, а потім виконати наступні дії.

1. На панелі інструментів Сводные таблицы натиснути на кнопку Мастер сводных таблиц. Відкриється третє діалогове вікно Майстра зведених таблиць.

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

3. Натиснути на кнопку Готово, щоб внести зміни в зведену таблицю.

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

Рис. 38. Діалогове вікно для зміни формату чисел і типу операцій в полях зведеної таблиці

У зведеній таблиці можна налагоджувати параметри, загальні для всієї зведеної таблиці або для окремих її полів. Це налагодження можна здійснювати або при створенні зведеної таблиці, або вже після її створення. Щоб відкрити діалогове вікно Параметры сводной таблицы, потрібно вибрати команду Параметри в списку Сводная таблиця, що відкривається на панелі інструментів Сводные таблицьі. На рис.39. зображене діалогове вікно Параметры

Рис. 39. Діалогове вікно Параметры сводной таблицы сводной таблицьі.

На рис.39 показано встановлення параметрів зведеної таблиці за замовчуванням. Для модифікації зведеної таблиці змініть ці встановлення. Наприклад, порожні комірки зведеної таблиці за замовчуванням містять пропуски. Можна зробити так, що в порожніх комірках будуть відтворюватися «0» або «-».

Оновлення зведеної таблиці

Коли дані в списку змінюються, зведена таблиця, створена на його основі, не буде оновлюватися автоматично. Щоб змінити зведену таблицю, потрібно скористатися командою Обновить данные. На панелі Сводные таблицы ця команда подана у вигляді кнопки зі знаком оклику Якшо панель інструментів Сводные таблицы відсутня на екрані, необхідно вибрати в меню Данные команду Обновить данные.

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

Обчислення в зведених таблицях

Крім функції додавання у зведених таблицях для розрахунків існує ще декілька типів операцій, результати яких можуть бути відображені в зведеній таблиці (див. табл. З.).

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

Таблиця 3. Найбільш розповсюджені операції розрахунків у зведених таблицях

Ім'я функції	Опис
СРЗНАЧ	Обчислює середнє значення по полях у списку
МИН	Повертає найменше значення в списку
МАКС	Повертає найбільше значення в списку
СЧЕТЗ	Підраховує тільки кількість чисел у стовпці, що може містити і текст
ПРОИЗВЕД	Обчислює здобуток усіх чисел у списку

Таблиця 4. Додаткові обчислення в полі даних зведеної таблиці

Ім'я операції	Опис
Відмінність	Значення комірок області даних відтворюються у вигляді різниці ззаданим обчислювальним елементом поля
Частка	Значення комірок області даних відтворюються у відсотках до заданого обчислювального елементу поля
3 наростаючим підсумком у полі	Значення комірок області даних відтворюються у вигляді наростаючого підсумку для послідовних елементів
Частка від суми по рядку	Значення комірок області даних відтворюються у відсотках від підсумку рядка
Частка від суми по стовпцю	Значення комірок області даних відтворюються у відсотках від підсумку стовпчика
Частка від загальної суми	Значення комірок області даних відтворюються у відсотках від загального підсумку зведеної таблиці

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

1. За допомогою панелей інструментів Сводные таблицм викликати Мастер сводных таблиц, щоб відкрити третє вікно майстра.

2 Двічі натиснути на поле в області даних, щоб відкрити діалогове вікно Вычисления поля сводной таблицы

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

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

5. Натиснути на кнопку ОК. Зведена таблиця буде перетворена.

Створення і друк сценаріїв ізвітів

Спроба аналізу ситуацій типу «А что, если?» дає можливість вставляти в таблицю різні числа і дивитися, як вони впливають на результати. Що, якщо банк зменшить процентну ставку по позиці на 2%? Що, якщо в наступному кварталі показники попиту підскочать на 3000 грн.? Такий аналіз ситуацій за схемою «а что, если?» в Ехсеl дозволяє миттєво обігравати різноманітні ситуації. Усе, що від користувача потрібно, це підставляти в таблицю потрібні дані і дозволяти функціям автоматичного перерахунку Ехсеl справно виконувати свою роботу.

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

Наприклад, показана на рис.40. таблиця преміальних містить рівні обсягу продажів і відповідні норми преміальних. На даний момент витрати на преміальні дорівнюють 21,2235 грн. (Е10). Припустимо, що обговорюються три можливі норми преміальних. Яка буде загальна сума виплачуваних преміальних для кожної із цих альтернатив?

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

Для того, щоб створити сценарій, необхідно виконати наступні дії.

1. Потрібно виділити комірки, що містять значення, які потрібно буде змінювати. Не потрібно виділяти при цьому заголовки стовпців. Наприклад, у таблиці, показаній на рис.40., такими комірками будуть комірки від 16 до 19.

Рис.40. Таблиця преміальних

2. Потрібно вибрати Сервис/Сценарии/Диспетчер сценариев (рис.41).

Рис.41.Діалогове вікно Диспетчер сценариев

Потрібно вибрати кнопку Добавить. З'явиться діалогове вікно Добавление сценария. Необхідно ввести у ньому ім'я сценарію для поточних значень. На рис.42. - ім'я Стандартний. Комірки, на які буде посилатися сценарій, що створюється, атакож автор і дата створення сценарію вводяться у діалогове вікно Добавление Сценария автоматично. Потрібно вибрати ОК. З'явиться діалогове вікно Значение ячеек сценарію (рис. 43). Оскільки перший сценарій повинен відображати поточні значення, не робіть ніяких змін.

Рис.42. Діалогове вікно Добавление сценария

Рис.43. Діалогове вікно Значення ячеек сценария

4. Щоб створити додаткові сценарії, необхідно вибрати кнопку Добавить. У діалоговому вікні Добавление сценария, шо з'явилося, потрібно ввести ім'я нового сценарію і натиснути на ОК. У діалоговому вікні Значення ячеек сценария слід ввести значення для нового сценарію.

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

Таблиця 5.

Стандартний	0%	3%	5%	7%
Низький	0%	0%	3%	5%
Високий	0%	5%	7%	9%
Особливий	3%	5%	9%	11%

6 Щоб відобразити будь-який сценарій на робочому листі, потрібно виділити ім'я сценарію і вибрати Вывести. Щоб закрити діалогове вікно Диспетчер сценариев, потрібно натиснути на кнопку Закрыть.

Дата добавления: 2014-01-11