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

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




Скачати 90.61 Kb.
НазваКонсолідація даних
Дата конвертації17.12.2012
Розмір90.61 Kb.
ТипДокументи


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

  • із застосуванням тривимірних формул;

  • за допомогою команди Консолідація.

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

Нехай ми маємо дані діяльності магазинів за шість місяців, що мають вигляд:



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

Додамо до таблиці аркуш «підсумок1» та скопіюємо вміст перших двох стовпців з довільного аркушу. Додамо до таблиці стовпчик «Виторг» та до комірок внесемо формули.



Обидві формули є тривимірними і рівноправними. Скопіюємо формулу до всіх інших комірок та отримаємо консолідовану таблицю для подальшої обробки.

Розглянемо другий спосіб консолідування даних. Для цього створимо у нашій електронній книзі аркуш «підсумок 2» і проведемо підготовчу роботу, аналогічну попередньому. Виділимо пусту комірку (згідно демонстрації та викличемо команду Консолідація закладини Дані:



Виконаємо відповідні налаштування:



Крім сумування можна при консолідації використовувати і інші функції:



Після застосування таблиці отримаємо консолідовану таблицю виду:




Практична частина

    1. Відкрийте файл заготовки konsilid.xls.

    2. Створіть додатковий аркуш з назвою «Консолідація» та підготуйте таблицю для застосування об’єднання даних з різних аркушів.

    3. Проведіть консолідацію даних за полем «Кількість», використовуючи функції:

  • Визначення загальної кількості за допомогою тривимірних формул для першої двійки;

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

    1. Проаналізуйте отримані дані дайте та запишіть до зошиту відповіді на запитання (оформіть у вигляді таблиці):

      1. Назвіть код товару, що має найбільший продаж. У якому з магазинів були зафіксовані ці дані?

      2. Яка загальна кількість проданого товару з кодом 678?

      3. Яка найменша середня кількість товару була продана за визначений термін і якому коду товару вона відповідає.

      4. Яка найбільша середня кількість товару була продана за визначений термін і якому коду товару вона відповідає.





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



Зведені таблиці

Що ж таке зведені таблиці, і навіщо вони потрібні? Ми часто стикаємося з ситуаціями, коли у нас є багато різноманітних даних (які можна назвати статистичними), але нас цікавлять якісь загальні висновки або проміжні підсумки.

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

За 17 днів продажів у нас вийшла велика таблиця на 350 записів. Але ця таблиця не вирішує наших проблем. Нам необхідно дізнатися обсяги продажів у грошовому та кількісному виразі по датах і по окремих магазинах, але як це зробити? Сортувати таблицю і підсумовувати окремі її частини? Це вимагає часу, а завтра надійдуть нові дані, і всю роботу потрібно буде знову повторити.

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

Як Ви бачите на ілюстрації, всі дані автоматично згрупувалися по датах. Тепер можна відразу побачити кількість проданих телефонів і загальну суму виторгу. Крім того, використовуючи фільтр - список, який знаходиться в лівому верхньому кутку сторінки, ми можемо відобразити узагальнені дані по окремо взятому магазину. Для цього достатньо натиснути на значок фільтра в правій частині клітинки В2, і вибрати потрібний нам магазин зі списку:



Таблиця відразу ж відобразить потрібні нам результати:



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


  • Дуже простий спосіб створення такої таблиці, який не потребує багато часу;

  • Можливість консолідувати дані з різних таблиць і навіть з різних джерел;

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


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

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

Для створення зведеної таблиці нам потрібна проста базова таблиця.

^ Алгоритм створення зведеної таблиці

      1. Для створення зведеної таблиці відкрийте вкладку ^ Вставка, де в групі Таблицы виберіть команду Сводная таблица.






      1. Відкриється наступне діалогове вікно:

У цьому вікні Excel пропонує нам вказати вихідну таблицю або діапазон значень, на підставі яких буде будуватися зведена таблиця. Якщо Ви виконали команду Зведена таблиця, попередньо встановивши курсор на листі, де знаходяться будь-які дані, Excel автоматично заповнить це поле. Якщо ж на листі дані відсутні, або вони знаходяться в іншому місці, Вам потрібно буде вказати адресу діапазону даних вручну.

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





      1. Натиснувши кнопку Ок після налаштування потрібних нам умов, ми отримуємо наступний робочий лист:


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

а) у верхній частині вікна налаштувань відзначаємо всі назви необхідних нам стовпців:

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

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

г) Поля ^ Сумма по полю Объем продаж, шт. і Сумма по полю Сумма выручки перетягуємо в область Значения. Дані всіх стовпців з цієї області Excel підсумує і відобразить в рядках зведеної таблиці.

Налаштування нашої таблиці повинне виглядати ось так:




Тоді наша зведена таблиця буде мати такий вигляд:


Тепер ми відразу можемо дізнатися обсяги продажів мобільних телефонів в грошовому та кількісному виразі на будь-яку потрібну нам дату як загалом по мережі, так і за окремим магазином

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

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




Перейдемо до питання форматування зведеної таблиці.

При створенні нової зведеної таблиці Excel автоматично іменує її стовпці та заголовки. Однак це легко виправити - досить відредагувати клітинку заголовка стовпця або таблиці. Наприклад, ми перейменували попередній заголовок таблиці:



або в більш зрозумілій



Тепер ми спробуємо змінити зовнішній вигляд таблиці. Excel пропонує дуже зручний інструмент автоматичного форматування з використанням готових стилів. Для встановлення готового стилю таблиці необхідно виконати клік по області розміщення зведеної таблиці - в панелі інструментів відкриються вкладки під загальною назвою Работа со сводными таблицами. Перейдіть на вкладку Конструктор і у групі Стили сводной таблицы виберіть той стиль, який відповідає Вашим смакам. Наприклад, ось такий:


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

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




Група ^ Макет вкладки Конструктор містить кнопки, які дозволяють налаштувати сам макет нашої таблиці, а саме - Макет отчета, Промежуточные итоги, Общие итоги та Пустые строки.

Команда Макет отчета пропонує такі варіанти:

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




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




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

^ Промежуточные итоги - тут можна вказати, як потрібно виводити проміжні підсумки: на початку групи, в кінці, чи не виводити взагалі.

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

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

а ось так після:




Практичне завдання

І частина

    1. Завантажте редактор Ms Excel 2007.

    2. Відкрийте файл заготовки C:\зразки\zv_tab.xls.

    3. Використовуючи карту з інструкцією, створіть на основі даної електронної книги зведену таблицю.

    4. Застосуйте до таблиці форматування за своїм смаком.

ІІ частина

Виконати аналіз даних таблиці за допомогою зведеної таблиці. Дати відповіді на запитання та запишіть їх до зошита:

  1. Які загальні показники торгу на дату 15 травня 2011 року мережі та магазину №1 окремо?

  2. В якому магазині мережі продаж телефонів Samsung на дату 15 травня 2011 року був найменшим?

  3. Телефони якої марки найбільше повертають у точки продажу?

  4. Визначіть найвдаліший день другого тижня травня (дату) по мережі.

  5. Який відсоток від загальної виручки складає обсяг продажу телефонів Samsung на дату 16 травня 2011 року?









Схожі:

Консолідація даних iconЗадача асоціації. Поняття транзакції. Алгоритм а priori
Консолідація даних. Приклади задач інформаційного бізнесу, при розв’язуванні яких використовується консолідація даних
Консолідація даних iconТема12: Консолідація даних
Мета: Ознайомити учнів з можливістю консолідації даних в електронних таблицях. Вдосконалювати навички роботи з табличним процесором...
Консолідація даних iconІнформація щодо заповнення форми
Лист даних" служить формою внесення моніторингових даних; лист "Примітки" служить для внесення приміток по даних. Також, файл має...
Консолідація даних iconКонцепція типу даних
Структура даних, прості і складені типи даних, схема, приклади деяких простих типів, призначення
Консолідація даних iconПідготовка до контрольної роботи «Бази даних Access»
База даних – це структурована сукупність взаємопов’язаних даних певної предметної області (реальних об’єктів, процесів)
Консолідація даних iconТема. Робота з об'єктами бази даних у середовищі субд
Завдання. Здійснити пошук та впорядкування даних бази даних Бібліотека за визначеними умовами
Консолідація даних iconТема. Поняття моделі даних, бази даних
Обладнання та наочність: дошка, комп'ютер, презентація «Бази даних. Субд», інструкції з тб в комп'ютерному кабінеті
Консолідація даних iconЗгода на збір та обробку персональних даних
Головному управлінні освіти і науки уточнену інформацію та подавати оригінали відповідних документів для внесення моїх та моєї дитини...
Консолідація даних iconІнформація про володільця бази персональних даних  Юридична особа Х  Резидент Х
Прошу зареєструвати базу персональних даних у Державному реєстрі баз персональних даних
Консолідація даних iconІнформація про володільця бази персональних даних  Юридична особа х  Резидент х
Прошу зареєструвати базу персональних даних у Державному реєстрі баз персональних даних
Додайте кнопку на своєму сайті:
Документи


База даних захищена авторським правом ©te.zavantag.com 2000-2017
При копіюванні матеріалу обов'язкове зазначення активного посилання відкритою для індексації.
звернутися до адміністрації
Документи