Дані, які слід об’єднати, розташовані в однакових клітинках на різних аркушах

Дані, які потрібно об’єднати, розташовані в різних клітинках на різних аркушах

Об’єднання даних за допомогою формули.

Інші способи об’єднання даних

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

1. У головному аркуші скопіюйте або введіть підписи стовпців або рядків, які потрібно об'єднати.

2. Виберіть клітинку, яка має містити об'єднані дані.

3. Введіть формулу, яка містить посилання на вихідні клітинки в кожному аркуші або об'ємне посилання, що містить дані, які потрібно об'єднати. Залежно від типу посилання виконайте такі дії:

· Введіть формулу (окрему для кожного аркуша) з посиланнями на клітинки на інших аркушах. Наприклад, щоб об’єднати дані з аркушів «Продаж» (у клітинці В4), «Кадри» (у клітинці F5) і «Маркетинг» (у клітинці В9), у клітинці А2 на головному аркуші введіть:

Рисунок 16.8 – Приклад опису функції для даних з різних клітинок та аркушів

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

· Введіть формулу з тривимірним посиланням, яка посилається на діапазон назв аркушів. Наприклад, щоб об’єднати дані з клітинок А2 аркушів «Продажі» — «Маркетинг» (включно), на головному аркуші у клітинці А2 введіть:

Рисунок 16.9 – Приклад опису функції для даних з однакових клітинок але різних аркушів

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

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

Звіт зведеної таблиці Звіт зведеної таблиці. Інтерактивний перехресний звіт Microsoft Excel, який містить підсумкові дані та виконує аналіз таких даних, як записи бази даних із різних джерел, у тому числі поза межами Microsoft Excel. можна створити на основі кількох діапазонів для об’єднання. Цей спосіб схожий на об’єднання за категорією, але надає більше можливостей для повторного впорядкування категорій. Додаткові відомості див. у статті Швидке створення звіту зведеної таблиці.

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

Найбільш потужним засобом обробки даних в Excel є команда Зведена таблиця. В результаті її виконання створюється нова таблиця. Зведені таб­лиці надають користувачу можливості:

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

§ для побудові зведених таблиць використати запити до баз даних або інших джерел зовнішніх даних.

Зведена таблиця - це плоска або об'ємна (складається з декількох сторінок або шарів) прямокутна таблиця котра дає можливість виконати складний аналіз великих масивів даних. В такій таблиці поряд із звичай­ними списками можуть використовуватися дані з іншої зведеної таблиці, а також запити до зовнішніх даних. Для підсумкових розрахунків можна вибирати потрібну функцію зведення, або інший метод обчислення.

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

Створювати зведені таблиці можна двома способами. Розглянемо кожен з них.

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

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

2. Відкрийте вкладку «Вставка» і виберіть з розділу «Таблицы» інструмент «Сводная таблица». Якщо разом зі зведеною таблицею потрібно створити і зведену діаграму - натисніть на стрілку в нижньому правому куті значка «Зведена таблиця» і виберіть пункт «Зведена діаграма».

3. У діалоговому вікні «Создание сводной таблицы» виберіть щойно створену таблицю з даними або її діапазон. Для цього виділіть потрібну область.

Рисунок 16.10 – Вікно створення зведеної таблиці

В якості даних для аналізу можна вказати зовнішнє джерело: встановіть перемикач у відповідне поле і виберіть потрібне з'єднання зі списку доступних.

4. Далі потрібно буде вказати, де розміщувати звіт зведеної таблиці. Найзручніше це робити на новому аркуші.

5. Після підтвердження дії натисненням кнопки " ОК ", буде створений і відкритий макет звіту. Розглянемо його.

У правій половині вікна створюється панель основних інструментів управління - «Список полей сводной таблицы». Всі поля (заголовки стовпців в таблиці вихідних даних) будуть перераховані в області «Выберите поля для добавления в отчет». Позначте необхідні пункти і звіт зведеної таблиці з вибраними полями буде створений.

Рисунок 16.11 – Приклад вибору полів зведеної таблиці

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

Інші опції для редагування звітів доступні з меню «Робота зі зведеними таблицями» на вкладках «Параметры» і «Конструктор». Майже кожен з інструментів цих вкладок має масу настройок і додаткових функцій.

Спосіб 2. Створення зведеної таблиці з використанням інструменту «Мастер сводных таблиц и диаграмм». Щоб застосувати цей спосіб, доведеться зробити доступним інструмент, який за замовчуванням на стрічці не відображається. Процедура подібна до тої, де додавали кнопку «Форма…» на панель швидкого доступу. Відкрийте вкладку " Файл " - " Параметры " – «Панель быстрого доступа». У списку «Выбрать команды с» відзначте пункт «Команды не на ленте». А нижче, з переліку команд, виберіть «Мастер сводных таблиц и диаграмм». Натисніть кнопку " Добавить ". Іконка майстра з'явиться вгорі, на панелі швидкого доступу. Майстер зведених таблиць в Excel 2010 зовсім не багатьом відрізняється від аналогічного інструменту в інших версіях. Для створення зведених таблиць з його допомогою виконайте наступне.

1. Клацніть по іконці майстра в панелі швидкого допуску. У діалоговому вікні поставте перемикач на потрібний вам пункт списку джерел даних:

- «в списке или базе данных Microsoft Excel» - джерелом буде база даних робочого листа, якщо така є;

- «во внешнем источнике данных» - якщо існує підключення до зовнішньої бази, яке потрібно буде вибрати з доступних;

- «в нескольких диапазонах консолидации» - якщо потрібне об'єднання даних з різних джерел;

- «данные в другой сводной таблице или сводной диаграмме» - в якості джерела береться вже існуюча зведена таблиця або діаграма.

Рисунок 16.12 – Вікно майстра зведеної таблиці

2. Після цього вибирається вид створюваного звіту - «сводная таблица» або «сводная диаграмма (с таблицей)».

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

- Якщо ж необхідно консолідувати дані з декількох джерел, поставте перемикач у відповідну область і виберіть тип звіту. А після потрібно буде вказати, яким чином створювати поля сторінки майбутньої зведеної таблиці: одне поле або декілька полів.

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

Рисунок 16.13 – Заповнення параметрів зведеної таблиці

Після завершення натисніть кнопку «Далее», виберіть місце розташування майбутньої зведеної таблиці - на поточному аркуші або на іншому, натисніть «Готово» і ваш звіт, зібраний з декількох джерел, буде створений. При виборі зовнішнього джерела даних використовується додаток Microsoft Query, що входить в комплект поставки Excel 2010 або, якщо потрібно підключитися до даних Office, використовуються опції вкладки «Данные». Якщо в документі вже присутній звіт зведеної таблиці або зведена діаграма - в якості джерела можна використовувати їх. Для цього достатньо вказати їх розташування і вибрати потрібний діапазон даних, після чого буде створена нова зведена таблиця.

Дата добавления: 2014-12-25