Консолідація даних в EXCEL
Зміст
1. Консолідація даних в EXCEL
1.1 Загальне опис процесу консолідації
1.1.2 Консолідація даних по фізичному розташуванню
1.1.3 Консолідація по заголовках рядків і стовпців
1.1.4 Консолідація даних з використанням посилань
1.1.5 Ручна консолідація робочих листів
1.1.6 Створення зведеної таблиці на основі даних, що знаходяться в декількох діапазонах консолідації
1.2 Зв'язування об'єктів
1.2.1 Зв'язування з осередками і діапазонами клітинок робочої книги
1.2.2 Зв'язування осередків за допомогою команд Копіювати і Вставити посилання
1.2.3 Зв'язування із зображеннями комірок робочого аркуша
1.2.4 Оновлення пов'язаних зображень осередків
1.2.5 Зміна зв'язку із зображенням осередків
1.3. Основні прийоми роботи з пов'язаними книгами
1.3.1 Відкриття пов'язаних робочих книг
1.3.2 Зміна і оновлення посилань
1.3.3 Збереження робочих книг зі зв'язками
Список використаних джерел
1. Консолідація даних в EXCEL
В Excel дані, використовувані для обробки та аналізу, можуть розміщуватися в різних осередках і діапазонах комірок одного аркуша, на декількох аркушах однієї книги і навіть в різних книгах. При цьому за допомогою консолідації та зв'язування об'єктів процес отримання і відображення результатів обробки даних можна істотно спростити.
Консолідація об'єктів - це процес об'єднання даних з різних джерел у вигляді підсумкових значень, що розміщуються в діапазоні призначення.
Об'єктом консолідації або джерелом консолідованих даних можуть бути окремі осередки і діапазони клітинок, розміщені на одному або декількох аркушах, в одній чи різних книгах. Якщо об'єктом консолідації є дані, розміщені на одному робочому аркуші, на декількох аркушах однієї книги або в різних книгах, то говорять про консолідацію даних, листів і книг відповідно.
Консолідація листів і книг використовується для обробки даних, вступників, наприклад, з різних відділень однієї компанії.
Можливі наступні види консолідації:
- по фізичній розташуванню (п.1.2);
- по заголовках рядків і стовпців (п.1.3);
- з використанням посилань (п.1.4);
- ручна консолідація (п.1.5).
Між вихідними і консолідованими даними, а також між осередками та діапазонами комірок, діапазонами комірок і графічними об'єктами, у тому числі і малюнками, може бути встановлено зв'язок, що дозволяє миттєво оновлювати дані після зміни вихідних значень.
Зв'язок об'єктів часто використовується для розподілу великого обсягу інформації по різних робочих книгах або листам, які по окремо піддаються перевірці, а потім об'єднуються у вигляді єдиної інформаційної системи.
Проблемам зв'язування об'єктів присвячений розділ 2.
1.1 Загальний опис процесу консолідації
Процес консолідації припускає обов'язкове зазначення
- діапазону призначення;
- джерел даних;
- способу консолідації;
- наявності зв'язку між об'єктами консолідації;
- типу (функції) консолідації.
Останні чотири дії виконуються за допомогою діалогового вікна Консолідація, яке викликається по команді Дані - Консолідація (рис.1).
Рис.1. Вид вікна Консолідація
Діапазон призначення вказується за допомогою виділення на робочому:
- окремої комірки;
- рядки комірок;
- стовпця осередків;
- діапазону комірок, куди необхідно помістити узагальнені дані.
Заповнення його здійснюється за правилами, представленим у табл.1.
Таблиця 1. Заповнення діапазону призначення
Виділення
Результат
Осередок
Заповнюються всі комірки, необхідні для всіх консолідованих категорій (елементів) вихідних даних
Рядок осередків
Заповнюються осередку вниз від виділення. Ширина області призначення в точності збігається з шириною виділення
Стовпець осередків
Заповнюються осередку вправо від виділення. Висота області призначення в точності збігається з висотою виділення
Діапазон осередків
консолідує рівно стільки категорій, скільки поміститься у виділеному діапазоні. Якщо діапазон недостатній, з'явиться відповідне повідомлення
Примітки:
- Якщо в якості області призначення виділена одна комірка, перевірте, чи не накладуться чи консолідовані дані на необхідну інформацію, розташовану тут же на аркуші.
- Текст і формули не переносяться з вихідних листів в область призначення. Їх необхідно створити вручну перед консолідацією.
Джерела даних являють собою діапазони клітинок. Число діапазонів може досягати 255. Джерела даних не зобов'язані бути відкриті під час консолідації. Вихідні області оброблюваних даних задаються або тривимірними формулами безпосередньо в осередках діапазону призначення, або в полі Посилання діалогового вікна Консолідація (рис.1). Джерела даних можуть знаходитися на тому ж аркуші, що і таблиця консолідації, на інших аркушах тієї ж книги, в інших книгах або в файлах Lotus 1-2-3.
Для зазначення джерел даних можуть бути використані два способу:
- виділення початкового діапазону за допомогою миші;
- введення посилання на діапазон з клавіатури.
Виділення вихідного діапазону за допомогою миші здійснюється стандартними прийомами.
Введення посилань на діапазон з клавіатури. Існує два види посилань: внутрішні і зовнішні.
Внутрішнє посилання - це посилання на діапазони клітинок робочої книги. Вона є окремим випадком зовнішнього посилання.
Синтаксис внутрішньої посилання:
= 'Імя_ліста'! Адрес_діапазона
Приклад 1.
Необхідно записати в осередок В2 першого аркуша (Лист1) значення осередку D4 наступного листа (Лист2).
Для цього у комірку В2 введіть формулу:
= 'Аркуш2'! $ D
Зовнішнє посилання-це посилання на діапазони клітинок, розташованих в інших книгах.
Синтаксис зовнішнього посилання:
= 'Шлях [Імя_рабочей_кнігі] Імя_ліста'! Адрес_діапазона
Приклад 2.
Необхідно в клітинку В2 першого аркуша (Лист1) записати значення комірки А1 з листа 2кв1996 файлу КВАРТАЛ2.xls, що знаходиться на диску Е: в папці USERS.
Для цього у комірку В2 слід ввести наступну формулу:
= 'E: USERS [КВАРТАЛ2.xls] 2кв1996'! $ A
Якщо вихідна книга закрита, повний шлях до неї слід вказувати обов'язково. Шлях можна набрати з клавіатури або скористатися кнопкою Огляд вікна Консолідація (рис.1) і вибрати файл на диску.
Для полегшення роботи з вихідними областями часто використовують іменні посилання. Для цього діапазонам вихідних даних, листам і книгам, де вони знаходяться, присвоюють імена.
Приклад 3. Вихідні області і область призначення знаходяться на одному аркуші. Наприклад, включити діапазон Бюджет (діапазону комірок попередньо присвоєно ім'я Бюджет).
Для цього вводимо посилання
= Бюджет
Приклад 4. Вихідні області ...
