Консолідація даних в excel
За замовчуванням використовується функція Сума.
1.1.2 Консолідація даних по фізичній розташуванню
Консолідація по розташуванню осередків можлива, якщо дані одного типу на всіх аркушах розташовані в одних і тих же позиціях щодо вихідних діапазонів. Самі діапазони при цьому можуть розташовуватися по-різному. Дані в діапазоні призначення будуть розташовані так само, як і в діапазонах-джерелах.
Щоб здійснити консолідацію цього типу необхідно виконати наступні дії:
1. Виділити діапазон призначення, керуючись табл. 1.
Враховуйте тільки область даних. Текстові заголовки в консолідації не беруть участь. Для нашого прикладу це діапазон A2: A5 листа призначення (рис.3). Оскільки зазначений діапазон комірок, то консолідується тільки чотири значення за кількістю осередків в діапазоні (див.табл.1).
2. Вибрати команду Дані, Консолідація. З'явиться діалогове вікно Консолідація (рис. 1).
5. У списку Функція вибрати тип консолідації. За замовчуванням використовується функція сума.
6. Скинути обидва прапорця в групі Використовувати як імена, оскільки осередки прив'язані до своїх позицій, а не до заголовків.
7. Встановити при необхідності прапорець в опції Створити зв'язку з вихідними даними. Тоді результати будуть оновлюватися при зміні даних, а в області призначення буде створена структура.
8. Натиснути кнопку ОК.
Результат консолідації представлений на рис.3.
Консолідація по розташуванню використовується рідко, оскільки необхідно, щоб вихідні діапазони мали абсолютно однакову структуру, а цього складно домогтися, наприклад, якщо дані надходять з різних відділень компанії. У відділеннях можуть використовуватися додаткові поля, щоб виділити який-небудь товар, регіон або статтю витрат. В цьому випадку краще консолідація по заголовкам рядків і стовпців.
1.1.3 Консолідація по заголовкам рядків і стовпців
Цей метод консолідації дозволяє консолідувати вихідні дані, що містять поряд з полями, загальними для всіх діапазонів, будь-які унікальні поля. Порядок розташування полів не має значення. Зіставлення інформації здійснюється у назвах рядків і стовпців.
Таким чином, досягається велика гнучкість.
Необхідно скласти зведену відомість витрат двох різних філій компанії (книги Філіал1.xls і Філіал2.xls). Назви статей і їх порядок проходження можуть відрізнятися. Вихідні діапазони представлені на рис.5. Заголовки стовпців збігаються, заголовки рядків немає.
Решта кроки в алгоритмі побудови зведеної таблиці розглянуті в Прімері 11.
1.2 Зв'язування об'єктів
Зв'язки дозволяють використовувати на одному робочому аркуші дані з інших аркушів робочої і навіть інших книг.
Робоча книга, яка містить вихідні дані, називається вихідної книгою або книгою-джерелом, книга, яка отримує дані називається книгою-одержувачем або цільової робочої книгою.
Незалежно від того, чи відкрита вихідна робоча книга або закрита, цільова робоча книга отримає необхідні дані через зв'язок. Якщо під час відкриття цільової робочої книги вихідна робоча книга відкрита, то зв'язку оновлюються автоматично. Якщо вихідна робоча книга закрита, то при відкритті цільової користувачеві буде поставлено питання, чи хоче він працювати з даними, що залишилися після останнього збереження або їх слід оновити з вихідної робочої книги (см.п. "Відкриття пов'язаних робочих книг").
Можна встановити зв'язок вихідних даних
1) з осередками і діапазонами комірок
2) із зображеннями осередків робочого аркуша.
Зв'язок з даними дозволяє уникнути проблем, властивих роботі з надто великими, громіздкими робочими книгами. Завдяки можливості пов'язувати дані один з одним, можна створювати невеликі робочі книги з невеликими робочими листами, які відповідають певним завданням користувача. Потім ці книги можна пов'язувати між собою, будуючи з них великі інформаційні системи. У наведеному нижче списку перераховані основні переваги побудови систем, що складаються з невеликих робочих книг, дані яких пов'язані один з одним:
- За допомогою зв'язків можна передавати від однієї робочої книги в іншу будь-які дані, числа і текст, які потім можуть бути використані в формулах
- Пов'язані дані можуть бути відформатовані так само, як і будь-які інші дані в інших осередках робочого аркуша. При роботі потрібна менша кількість пам'яті, т. К. В повному обсязі робочі книги, що входять в інформаційну систему, повинні обов'язково бути відкриті
- Інформаційні системи, що складаються з декількох робочих книг, дуже гнучкі, до того ж в них легко вносити зміни. Ви можете перебудовувати, тестувати і налаштовувати один компонент системи, що не перебудовуючи всю систему в цілому
- Невеликі робочі книги перераховуються набагато швидше
- Можна створювати окремі компоненти, для роботи з ними на різних комп'ютерах. Компоненти можуть бути пов'язані один з одним за допомогою узагальнюючої електронної таблиці, тоді при оновленні цієї таблиці результати роботи багатьох користувачів будуть зібрані в одному місці. Такі інформаційні системи мають ряд переваг: багато користувачів може працювати одночасно на різних комп'ютерах, загальна робота може бути виконана швидше, і, нарешті, значно менша ймовірність того, що недосвідчений оператор зашкодить всю інформаційну систему
- Систему, що складається з окремих компонентів, простіше обслуговувати і налагоджувати
- Робочі книги, що входять до складу системи, можуть бути перероблені для використання в іншій системі
Велика кількість зв'язків може уповільнити процес відкриття цільової робочої книги. Крім того, розміри робочої книги з великою кількістю зовнішніх зв'язків можуть бути дуже великими, так як Excel зберігає в робочій книзі останню копію використаних в роботі зовнішніх даних, щоб мати можливість працювати з робочою книгою без оновлення її зв'язків. Якщо вихідна робоча книга завжди відкривається разом з цільовою, або якщо ви завжди оновлюєте зв'язку при відкритті, або ж якщо ви просто хочете заощадити дисковий простір, вимкніть збереження копії зовнішніх даних разом з робочою книгою. Для цього виберіть команду Сервіс, Параметри. в діалоговому вікні розкрийте вкладку Обчислення, зніміть прапорець Зберігати значення зовнішніх зв'язків та натисніть кнопку ОК (рис.20).
Зв'язок осередок - діапазон комірок.
Приклад 13. У комірці В6 прикладу 12 може розташовуватися така формула:
Зв'язок діапазону комірок з діапазоном осередків.
Можна пов'язати діапазон комірок з іншим діапазоном тільки такого ж розміру. Такий зв'язок використовує формулу масиву.
Приклад 14. Наприклад формула, що зв'язує осередки В2: В5 робочої книги Річні звіти з осередками Е2: Е5 робочої книги Квартал 1, може виглядати наступним чином:
Щоб встановити цей зв'язок виконайте наступні дії:
1. Відкрийте робочі книги, між якими ви хочете встановити зв'язок.
2. Активізуйте вихідну робочу книгу.
3. Виділіть діапазон комірок, з яким ви хочете встановити зв'язок.
4. Виберіть команду Правка, Копіювати
5. Активізуйте кінцеву робочу книгу, в якій потрібно встановити зв'язок з даними. Виділіть лівий верхній кут діапазону комірок, в який ви хочете помістити зв'язок.
6. Виберіть команду Правка, Спеціальна вставка або, клацнувши правою кнопкою миші по виділеній комірці, виберіть команду Спеціальна вставка з контекстного меню. На екрані з'явиться відповідне діалогове вікно.
7. Встановіть перемикач. Вставити в положення Все, a перемикач Операція - в положення Ні (рис.13.)
8. Натисніть кнопку Вставити зв'язок. В осередках з'являться посилання.
1.2.3 Зв'язування з зображеннями осередків робочого аркуша
Щоб на одному аркуші зібрати дані з різних документів, можна використовувати зв'язок зображення області робочого листа з іншим листом. Це є прекрасним способом для створення легко оновлюваних звітів, що виводяться як на екран, так на друк. Пов'язані зображення осередків робочого аркуша мають наступні переваги перед пов'язаними осередками і діапазонами:
- Пов'язані зображення осередків можуть бути швидко відкриті і оновлені. При подвійному натисканні по зображенню відкривається лист-джерело і виділяється відповідний діапазон. Якщо файл робочої книги, що містить необхідний лист закрито, то він відкриється автоматично. Це дозволяє легко вносити зміни в дані і, отже, в малюнок
- Пов'язані зображення осередків можна відформатувати більшістю способів, що застосовуються до текстових полів, що забезпечує високу якість друкованих документів
- Пов'язані об'єкти, на відміну від самих осередків листа, можуть бути вільно переміщені, збільшені або зменшені. Така гнучкість дозволяє домагатися високоякісного оформлення сторінок, які об'єднують різні дані
- Пов'язані зображення осередків можуть бути виведені на друк разом із залежними від них діаграмами
- Пов'язані зображення осередків можуть бути асоційовані з макросами, що виконуються в момент вибору об'єкта
Однак у пов'язаних зображень осередків робочого аркуша є і недоліки:
- Не можна вводити дані безпосередньо в пов'язані зображення осередків
- В обчисленнях пов'язані зображення осередків використовувати не можна. Для цієї мети слід користуватися зв'язком з самими осередками і діапазонами робочого листа.
Щоб зв'язати зображення осередків робочого аркуша за допомогою кнопки Камера, виконайте наступні дії:
- Виділіть вихідний діапазон, малюнок якого ви хочете створити.
- Натисніть кнопку Камера. Покажчик миші матиме вигляд маленького хрестика.
- Перейдіть на робочий лист, на якому ви хочете помістити малюнок.
- Клацанням миші вкажіть те місце, в якому хочете бачити лівий верхній кут малюнка осередків. На робочому аркуші з'явиться зображення осередків, виділене чорними маркерами.
Щоб зробити те ж саме за допомогою команд меню і клавіатури, виконайте наступні дії:
- Виділіть вихідний діапазон, малюнок якого ви хочете створити.
- Виконайте команду Правка, Копіювати або натисніть комбінацію клавіш
- Перейдіть на робочий лист, на якому ви хочете розмістити малюнок.
- Виділіть клітинку, яка повинна знаходитися в верхньому лівому кутку малюнка.
Натиснувши і утримуючи клавішу
На новому робочому аркуші сформувати малюнок для побудованої в Прімері 11 зведеної таблиці.
Для цього необхідно:
- виділити вихідний діапазон А30: Е42,
- клацнути по кнопці Копіювати в буфер, перейти до осередку А1 нового листа,
-утримуючи клавішу
