2018-02-13
294
На практиці дуже часто виникає потреба підведення підсумків за даними, розташованим на різних робочих аркушах. Нехай, наприклад, є 12 робочих аркушів, розташованих в одному або декількох файлах, кожний з який містить відомості про діяльність підприємства за місяць. На основі цих даних потрібно створити новий робочий аркуш, де б підводилися підсумки діяльності за рік. Подібні задачі можна легко вирішити, скориставшись опцією Консолидация з меню Данные. Вона дозволяє підсумувати або якось інакше підбити підсумки, базуючись на даних у відповідних комірках робочих аркушів, що містять вхідні дані.
Відповідність між комірками може встановлюватися або за їхнім розташуванням на робочому аркуші (наприклад, будуть підсумовані ліві верхні комірки всіх аркушів і т.д.), або по найменуваннях рядків і стовпців. Наприклад, вміст комірки, що знаходиться на перетинанні стовпця Витрати і рядка Реклама, буде підсумовано з вмістом однойменних комірок інших робочих аркушів незалежно від того, де ці комірки розташовані.
|
|
|
 |
Щоб виконати консолідацію, необхідно перейти на новий робочий аркуш, де передбачається розміщення підсумкових даних, і вибрати в меню Данные опцію Консолидация. При цьому на екрані з'явиться вікно, показане на рис. 12.
У графі Функция треба зазначити спосіб підведення підсумків. Звичайно там встановлюється опція Сумма, проте можна обрати й інший спосіб підведення підсумків: Количество значений, Среднее, Максимум, Минимум тощо.
У графу Ссылка один за іншим вводять діапазони комірок, що підлягають консолідації. Для цього треба перейти на потрібний робочий аркуш, виділити діапазон комірок мишкою і натиснути кнопку Добавить. В результаті даний діапазон буде додано в Список диапазонов
Якщо відповідності між комірками встановлюються за найменуваннями рядків і стовпців, то комірки діапазону треба виділяти разом з їх найменуваннями. Якщо ж відповідності встановлюються по розташуванню комірок, то виділяти слід тільки комірки з даними, а найменування рядків і стовпців у підсумкову таблицю треба скопіювати з одного з робочих аркушів.
Після введення всіх діапазонів, перед натисканням кнопки ОК, у нижній частині вікна слід зазначити чи потрібно при консолідації враховувати найменування рядків і стовпців вхідних таблиць, а також чи потрібно встановлювати зв'язок із цими робочими аркушами. Якщо ви встановите такий зв'язок, то в підсумковій таблиці буде створена структура, у якій можна буде побачити і вхідні дані, і результат їхньої консолідації.
Контрольні питання
1. Що таке консолідація і для чого вона використовується?
|
|
|
2. Як встановлюється відповідність між комірками при консолідації?
3. Як виконати консолідацію?
Завдання для виконання
1. Створіть у робочій книзі october таблицю відповідно до зразка, показаного на рис.13, де Т1 - найменування товарів, Т2 - прізвища продавців, Ч1 - вартість даного товару, проданого даним продавцем. Для цього скопіюйте в буфер обміну першу зведену таблицю, створену в лабораторній роботі № 5, і виберіть в меню Правка опцію Специальная вставка, скопіювавши у файл october не самому зведену таблицю, а тільки значення, що в ній містяться.
2. Створіть ще два файли робочих книг і дайте їм імена november і december.
3. Робочий аркуш, що містить дану таблицю, скопіюйте (шляхом перетягування за допомогою мишки) із файла october у файли november і december.
4. Внесіть довільні зміни в числові значення Ч1 файлів november і december, щоб дані за листопад і грудень дещо відрізнялись від жовтневих.
5. На окремому робочому аркуші файла december виконаєте консолідацію даних із жовтня по грудень, встановивши відповідності між комірками по їхньому розташуванню в таблиці.
6. Внесіть деякі зміни в структуру таблиць у файлах november і december, додавши нові, або вилучивши наявні, або змінивши розташування рядків і стовпців Т1, Т2.
7. На наступному робочому аркуші файла december виконаєте консолідацію даних із жовтня по грудень, встановивши відповідності між комірками по найменуваннях рядків і стовпців.
8. Збережіть на диску всі створені файли.
Лабораторна робота № 7
Використання майстра шаблонів
