Информатика
.pdf
This document is created with trial version of Document2PDF Pilot 2.16.100.
Виконання:
1.Додати нові аркуші в книгу – клацнути правою кнопкою миші по ярличку останнього листа → к. Додати → вкладка Загальні → виділити значок Лист → ОК (з'явиться новий лист). Повторювати потрібну кількість разів.
2.Упорядкувати аркуші: перетягнути мишкою ярличок листа, що стоїть не один по одному, на місце.
3. Клацнути по ярличку |
Лист1, натиснути клавішу |
[Shift] і одночасно |
клацнути по ярличку Лист5. |
|
|
4.На першому виділеному листі створити зазначену форму c формулами.
5.Зняти виділення з аркушів, клацнувши по ярличку Лист6.
6.У кожнім листі заповнити прізвище матеріально-відповідальної особи. Додати в сформовану книгу (даного приклада) звітні форми по двом новим
реалізаторам. Послідовність дій:
1.Виділити групу аркушів з 5 по 7.
2.На листі 5 виділити усю форму звіту, м. Виправлення → к. Заповнити → п.
По аркушах → покажчик Усі → ОК.
3.Зняти виділення з групи аркушів і відкоригувати прізвища на аркушах 6 і 7. Примітка: Якщо копійований звіт був зап овнений, виділити групу нових аркушів (6 і 7), потім виділити предметну частину листа 6 і натиснути клавішу [Delete].
При формуванні підсумкової таблиці по створених звітних формах можна
використовувати об'ємні формули , що можуть підсумовувати дані відразу |
по |
декількох однойменних осередках різних аркушів. |
|
Консолідація аркушів
При виконанні робіт зі створення зведених звітів по однотипних детальних звітах, крім об'ємних формул, Excel дозволяє використовувати ще один досить простий і зручний прийом – консолідацію даних.
Команда Консолідація з меню Дані може об'єднати інформацію з вихідних аркушів (до 255 аркушів) в одному підсумковому листі. Вихідні аркуші можуть розташовуватися в тій же самій книзі, у якій знаходиться підсумковий лист, або в
161
This document is created with trial version of Document2PDF Pilot 2.16.100.
інших книгах. |
|
|
Команду Консолідація |
можна використовувати декількома способами. |
|
Можна зв'язати консолідовані дані з вихідними даними, щоб наступні зміни у |
||
вихідних аркушах відображалися в підсумковому листі. Або можна просто |
|
|
консолідувати вихідні дані без створення зв'язків. |
|
|
Консолідувати дані можна |
по розташуванню або по категорії. |
Якщо |
консолідують дані по розташуванню, Microsoft Excel збирає інформацію з однаково розташованих осередків кожного вихідного листа. При консолідації даних по категорії Excel використовує як основу для об'єднання аркушів заголовки стовпців або рядків. Можна консолідувати аркуші, використовуючи будь -яку функцію, приведену в поле зі списком, що розкривається, Функція у вікні діалогу Консолідація. За замовчуванням використовується ф ункція Сума, що підсумовує дані з кожного вихідного листа і поміщає результат у підсумковий лист. Також можна використовувати кожну з наступних функцій: Кількість значень, Середнє, Максимум, Мінімум, Добуток, Кількість чисел, Незміщене відхилення, Зміщен е відхилення, Незміщена дисперсія і Зміщена дисперсія.
Можна консолідувати аркуші з відкритих у даний момент книг, що знаходяться на диску. Книга, що містить лист, у який містяться консолідовані дані, повинна бути відкрита, але вихідні книги можуть бути за криті, звичайно, за умови, що ви надали Excel правильні зведення про їхнє місцезнаходження, інакше він не зможе знайти усі файли цих книг. Ви повинні зберегти усі вихідні книги, перш ніж почати консолідацію.
Нижче даються два приклади консолідації: по роз ташуванню, по категорії. У прикладах розглянута консолідація аркушів однієї книги, але точно так само можна консолідувати аркуші, що знаходяться в різних книгах.
Консолідація по розташуванню
При консолідації по розташуванню Exc el застосовує підсумкову функцію (Сума, Середнє або будь -яку іншу з перерахованих раніше) до осередків з однаковими адресами в кожнім вихідному листі. Це найпростіший спосіб консолідації, при якому консолідовані дані у всіх вихідних аркушах повинні мати зовсім однакове розташування.
Приклад 1.1. Виконати консолідацію звітів про реалізації холодильної техніки за тиждень. Розташувати консолідовані звіти по розташуванню і категоріям на окремих аркушах.
Виконання:
1. Додати в книгу зі звітами 2 нових листи і п ривласнити їх ярличкам імена
Консолід_по_розташ. і Консолід_по категор.
2. Скопіювати в лист Консолід_по_розташ форму звіту по реалізації товару, очистити кінцеву область (область призначення), тобто блок осередків, у котрий будуть поміщені консолідовані дані – В7:К15. Виділення не знімати!
3. Виберіть команду Консолідація в меню Дані (рисунок 1).
162
This document is created with trial version of Document2PDF Pilot 2.16.100.
Рисунок 1 - Діалогове вікно консолідації.
4. Для підрахунку значень з кожного вихідного листа випливає в поле зі списком Функція, у ві кні діалогу Консолідація вибрати функцію Сума. Залишіть прапорці в секції Використовувати мітки невстановленими. Оскільки ми не
збираємося створювати зв'язку з вихідними аркушами, прапорець |
Створювати |
|
зв'язку з вихідними даними також залишіть невстановленим. |
Посилання |
|
5. Уведіть посилання для кожного вихідного діапазону в поле |
||
або виділите ці діапазони за допомогою миші: клацнути по полю |
Посилання → |
|
клацнути по ярличку Лист1 → виділити осередку |
В7:К15 (зменшити діалогове |
|
вікно можна клацнути по кнопці нап рикінці поля |
Посилання) → клацнути по |
|
кнопці Додати (розгорнути діалогове вікно можна щигликом по тій же кнопці наприкінці поля Посилання) – зазначений діапазон осередків повинний з'явитися в поле Список діапазонів → клацнути по ярличку Лист2 → виділити осередку В7:К15 (при створенні форм документів у групі діапазон осередків буде встановлений автоматично) → клацнути по кнопці Додати → повторити зазначені дії для всіх підсумкових звітів → ОК.
Звичайно, використання миші є найбільш простим способом уведення посилань, але якщо необхідно послатися на вихідні аркуші закритих у даний момент книг, то вам доведеться ввести ці посилання з клавіатури. (Можна використовувати кнопку Огляд, щоб визначити місцезнаходження файлу, а потім вручну ввести посилання на осередо к.) посилання, що вводиться, повинна мати наступну форму:
{Ім'я файлу}Ім'я листа! Посилання Якщо вихідному діапазонові було призначене ім'я, можна використовувати
це ім'я замість посилання.
Зауваження. Після виконання консолідації, посила ння, введені у вікні діалогу Консолідація, запам'ятовуються при збереженні книги. Якщо при наступному відкритті книги необхідно обновити консолідовані значення, варто вибрати команду Консолідація і натиснути кнопку ОК.
Примітка. Консолідація виконується по блоці суміжних осередків, тому
163
This document is created with trial version of Document2PDF Pilot 2.16.100.
графа «Ціна», як довідковий матеріал, винесена в кінець звіту, щоб не розривати консолідований діапазон.
Консолідація по категорії
Розглянутий приклад 1.1. припускав повну ідентичність звітів у частині розглянутих категорій, у даному випадку, реалізованих товарів, що в практичній діяльності зовсім не обов'язково. Список реалізованих товарів у кожної підзвітної особи може змінюватися. У цьому випадку необхідно використовувати консолідацію по категоріях.
Приклад 1.2. Додати в звіт про реалізацію товарів на |
Лист4 товар “BOSCH |
KGV34X05”, на Лист5 – “LIEBHERR KT1430”. На листі |
Консол_по_категор |
створити консолідований звіт по всім реалізаторам за тиждень. Виконання.
1.На лист Консол_по_категор скопіювати заголовну частину таблиці звіту ( А1:L6) з будь-якого листа.
2.Виділити осередок А7 як кінцеву область. При завданні одного осередку як кінцеву область команда Консолідація заповнює необхідну область нижче і праворуч від цього осередку.
3.М. Дані → к. Консолідація → заповнити вікно діалогу Консолідація →
вибрати Сума в поле зі списком, Функція → установити прапорець У лівому
стовпці в секції Використовувати мітки.
4.Увести посилання для кожного вихідного діапазону в поле Посилання або
виділити ці діапазони за допомогою миші: клацнути |
по полю Посилання → |
клацнути по ярличку Лист1 → виділити осередку |
А7:К15 (у діапазон повинні |
бути включені осередки з категоріями) → клацнути по кнопці Додати → клацнути по ярличку Лист2 → виділити осередку А7:К15 → клацнути по кнопці Додати → повторити зазначені дії для всіх підсумкових звітів, виділяючи дані про реалізовані товари → ОК.
5.Підсумковий лист тепер містить рядки, що відповідають унікальним елементам рядків вихідних аркушів (у даному випадку, для кожного товару). Якщо кілька аркушів мають одн акові елементи рядків, підсумковий лист виконує обрану математичну операцію на відповідних значеннях у кожнім стовпці.
6.Консолідований звіт можна відформатувати, додати підсумкові рядки з формулами, довідкову інформацію (наприклад, ціну).
Примітка. Катего рії можуть розташовуватися або в лівому стовпці, або у верхньому рядку виділюваного діапазону, на що повинно вказувати прапорець у секції Використовувати мітки. Тільки в одному стовпці або в одному рядку.
8. Завдання для лабораторної роботи № 4 на тему “Microsoft Excelконсолідація даних”
Варіант 1
1.Табличний процесор Microsoft Excel. Консолідація даних таблиць.
1.1. Завантажити табличний процесор Microsoft Excel.
164
This document is created with trial version of Document2PDF Pilot 2.16.100.
1.2.На п'ятьох аркушах одночасно створити звіти за такою формою ( в осередки
з«*» варто поставити формули):
Дані про реалізацію товарів по фірмі ТОВ “Холод-Маркет”
за ______________
Найменування |
Залишок |
Прихід |
Витрата |
Залишок на кінець |
товару |
на початок |
|
|
(гр.2+гр.3-гр4) |
LG GC 051SS |
|
|
|
* |
NORD DX431-7 |
|
|
|
* |
ZANUSSI ZRG316 |
|
|
|
* |
INDESIT SD125 |
|
|
|
* |
Разом: |
|
|
|
* |
1.3.На листі 6 створити зведений звіт, використовуючи консолідацію по розташуванню, застосувавши підсумкову функцію Сума (консолідувати звіти аркушів 1 - 3)
1.4.Внести зміни в звіти на аркушах 4 – 5, розширивши асортимент товарів або помінявши послідовність назв товарів.
1.5.На листі 7 створити зведений звіт, використовуючи консолідацію по категорії, застосувавши підсумкову функцію Сума (консолідувати звіти аркушів 1 - 5)
Варіант 2
1.Табличний процесор Microsoft Excel. Консолідація даних таблиць.
1.1.Завантажити табличний процесор Microsoft Excel.
1.2.На п'ятьох аркушах одночасно створити звіти за такою формою і
заповнити довільними даними не менш 5 рядків ( в осередки з «*» варто поставити формули):
Звіт про рух тари за ______________
|
Найменування тари |
Залишок |
Прихід |
Витрата |
Залишок на |
|
|
на початок |
|
|
кінець |
|
|
|
|
|
* |
|
|
|
|
|
* |
|
|
|
|
|
… |
|
|
|
|
|
* |
|
Разом: |
* |
* |
* |
* |
|
|
|
|
|
|
1.3. На листі 6 створити зведений звіт, використовуючи консолідацію по розташуванню, застосувавши підсумкову фун кцію Сума (консолідувати звіти аркушів 1 - 3)
1.4. Внести зміни в звіти на аркушах 4 – 5, розширивши асортимент товарів або помінявши послідовність назв товарів.
165
This document is created with trial version of Document2PDF Pilot 2.16.100.
1.5. На листі 7 створити зведений звіт, використовуючи консолідацію по категорії, застосувавши підсумк ову функцію Сума (консолідувати звіти аркушів 1 - 5)
Варіант 3
1.Табличний процесор Microsoft Excel. Консолідація даних таблиць.
1.1.Завантажити табличний процесор Microsoft Excel.
1.2.На п'ятьох аркушах одночасно створити звіти за такою формою і заповнити довільними даними не менш 5 рядків ( в осередки з «*» варто поставити формули):
Відомість реалізації товарів за ____________
Структурний |
Сума |
Торгівельна |
Знижка на |
Разом |
підрозділ |
|
знижка |
відходи |
(гр2+ гр3+ гр4) |
|
|
|
|
* |
|
|
|
|
* |
|
|
|
|
… |
|
|
|
|
* |
Разом: |
* |
* |
* |
* |
1.3.На листі 6 створити зведений звіт, використовуючи консолідацію по розташуванню, застосувавши підсумкову функцію Сума (консолідувати звіти аркушів 1 - 3)
1.4.Внести зміни в звіти на аркушах 4 – 5, розширивши асортимент товарів або помінявши послідовність назв товарів.
1.5.На листі 7 створити зведений звіт, використовуючи консолідацію по категорії, застосувавши підсумкову функцію Сума (консолідувати звіти аркушів 1 - 5)
Варіант 4
1.Табличний процесор Microsoft Excel. Консолідація даних таблиць.
1.1.Завантажити табличний процесор Microsoft Excel.
1.2.На п'ятьох аркушах одночасно створити звіти за такою формою і
заповнити довільними даними не менш 5 рядків ( в осередки з «*» варто поставити формули):
166
This document is created with trial version of Document2PDF Pilot 2.16.100.
|
Дані про заключення договорів постачання |
|
||||||||
за ____________________________ |
|
|
(тис.грн.) |
|
||||||
|
|
|
|
|
|
|
|
|
|
|
Постачальник |
|
П.І.Б., що заключили договір |
|
|
Разом |
|||||
Іванов |
|
Сидоров А.А. |
|
|
|
|
|
|
|
|
|
В.В. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
* |
|
|
|
|
|
|
|
|
|
|
* |
|
|
|
|
|
|
|
|
|
|
* |
|
|
|
|
|
|
|
|
|
|
* |
|
|
|
|
|
|
|
|
|
|
* |
Разом: |
* |
|
* |
* |
|
* |
|
* |
|
* |
1.3.На листі 6 ство рити зведений звіт, використовуючи консолідацію по розташуванню, застосувавши підсумкову функцію Сума (консолідувати звіти аркушів 1 - 3)
1.4.Внести зміни в звіти на аркушах 4 – 5, розширивши асортимент товарів або помінявши послідовність назв товарів.
1.5.На ли сті 7 створити зведений звіт, використовуючи консолідацію по категорії, застосувавши підсумкову функцію Сума (консолідувати звіти аркушів 1 - 5)
Варіант 5
1.Табличний процесор Microsoft Excel. Консолідація даних таблиць.
1.1.Завантажити табличний процесор Microsoft Excel.
1.2.На п'ятьох аркушах одночасно створити звіти за такою формою і
заповнити довільними даними не менш 5 рядків ( в осередки |
з «*» варто |
|||||||
поставити формули): |
|
|
|
|
|
|
||
|
Зведення про виробництво продукції. |
|
||||||
|
|
|
за ____________ кв. |
|
|
|
||
Найменування |
|
|
|
Місяці |
|
Усього за період |
||
продукції |
|
січень |
|
лютий |
березень |
квітень |
|
|
|
|
|
|
|
|
|
|
* |
|
|
|
|
|
|
|
|
* |
|
|
|
|
|
|
|
|
… |
|
|
|
|
|
|
|
|
* |
Разом: |
|
* |
|
* |
* |
* |
|
* |
167
This document is created with trial version of Document2PDF Pilot 2.16.100.
1.3. |
На листі 6 створити зведений звіт, використовуючи консолідацію |
по |
|
розташуванню, застосувавши підсумкову функцію Сума (консолідувати |
|
|
звіти аркушів 1 - 3) |
|
1.4. |
Внести зміни в звіти на аркушах 4 – 5, розширивши асортимент товарів |
|
|
або помінявши послідовність назв товарів. |
по |
1.5. |
На листі 7 створити зведений звіт, використовуючи консолідацію |
|
|
категорії, застосувавши підсумкову функцію Сума (консолідувати зв іти |
|
|
аркушів 1 - 5) |
|
|
Варіант 6 |
|
1. Табличний процесор Microsoft Excel. Консолідація даних таблиць.
1.1.Завантажити табличний процесор Microsoft Excel.
1.2.На п'ятьох аркушах одночасно створити звіти за такою формою і
заповнити довільними даними не менш 5 ряд ків (в осередки з «*» варто поставити формули):
Дані для аналізу стану товарних запасів (тис. грн.)
Найменування |
|
Норматив |
Фактичні |
|
Відхилення від |
||
товарних груп |
|
товарних запасів |
запаси |
|
нормативу |
|
|
Телевізори |
|
|
|
|
* |
|
|
Відеомагнітофони |
|
|
|
|
* |
|
|
Аудіотехніка |
|
|
|
|
* |
|
|
Кондиціонери |
|
|
|
|
* |
|
|
Вентилятори |
|
|
|
|
* |
|
|
Разом: |
|
* |
* |
|
* |
|
|
1.3. |
На листі 6 створити зведений звіт, використовуючи консолідацію |
по |
|||||
розташуванню, застосувавши підсумкову функцію Сума |
(консолідувати |
|
|||||
звіти аркушів 1 - 3) |
|
|
|
|
|
||
1.4. |
Внести зміни в звіти на аркушах 4 |
– 5, розширивши асо ртимент товарів |
|||||
або помінявши послідовність назв товарів. |
|
по |
|||||
1.5. |
На листі 7 створити зведений звіт, використовуючи консолідацію |
||||||
категорії, застосувавши підсумкову функцію Сума (консолідувати звіти |
|
||||||
аркушів 1 - 5) |
Варіант 7 |
|
|
||||
|
|
|
|
|
|||
1.Табличний процесор Microsoft Excel. Консолідація даних таблиць.
1.1.Завантажити табличний процесор Microsoft Excel.
1.2.На п'ятьох аркушах одночасно створити звіти за такою формою і
заповнити довільними даними не менш 5 рядків ( в осередки з «*» варто поставити формули):
168
This document is created with trial version of Document2PDF Pilot 2.16.100.
Дані про реалізацію мороженого (грн.) за___________
по реалізатору ________________________________
|
Назва морозива |
|
|
|
Дні |
|
|
|
|
Разом |
|
|
|
|
1 |
2 |
|
3 |
4 |
|
5 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Спортивне |
|
|
|
|
|
|
|
|
* |
|
|
|
Ескімо |
|
|
|
|
|
|
|
|
|
* |
|
|
Ласуня |
|
|
|
|
|
|
|
|
|
* |
|
|
Торнадо |
|
|
|
|
|
|
|
|
* |
|
|
|
|
|
|
|
|
|
|
|
|
|
* |
|
|
|
Разом: |
* |
* |
|
* |
* |
|
* |
|
* |
|
|
1.3. |
На листі 6 створити зведений |
звіт, використовуючи консолідацію |
по |
|||||||||
розташуванню, застосувавши підсумкову функцію Сума |
(консолідувати |
|
|
|||||||||
звіти аркушів 1 - 3) |
|
|
|
|
|
|
|
|
|
|
||
1.4. |
Внести зміни в звіти на аркушах 4 |
– 5, розширивши асортимент товарів |
||||||||||
або помінявши послідовність назв товарів. |
|
|
|
|
|
по |
||||||
1.5. |
На листі 7 створити зведений звіт, використовуючи консолідацію |
|||||||||||
категорії, застосувавши підсумкову функцію Сума |
(консолідувати звіти |
|
|
|||||||||
аркушів 1 - 5) |
|
|
|
|
|
|
|
|
|
|
||
|
|
Варіант 8 |
|
|
|
|
|
|
|
|||
1. Табличний процесор Microsoft Excel. Консолідація даних таблиць.
1.1.Завантажити табличний процесор Microsoft Excel.
1.2.На п'ятьох аркушах одночасно створити звіти за такою формою і
заповнити довільними даними не менш 5 рядків ( в осередки з «*» варто поставити формули):
Відомість витрати пального за ___________
Марка |
|
Перевезено |
Расход горючего |
Відхилення |
|
|||
автомашини |
|
вантажів |
по нормі |
|
фактично |
|
|
|
|
|
|
(т*км) |
|
|
|
|
|
|
|
|
|
|
|
|
* |
|
|
|
|
|
|
|
|
* |
|
|
|
|
|
|
|
|
… |
|
|
|
|
|
|
|
|
* |
|
|
Разом: |
|
* |
* |
|
* |
* |
|
1.3. |
На листі |
6 створити зведений звіт, використовуючи консолідацію |
по |
|||||
розташуванню, застосувавши підсумкову функцію Сума |
(консолідувати |
|
||||||
звіти аркушів 1 - 3) |
|
|
|
|
|
|||
1.4. |
Внести зміни в звіти на аркушах 4 |
– 5, розширивши асортимент товарів |
||||||
або помінявши послідовність назв товарів.
169
This document is created with trial version of Document2PDF Pilot 2.16.100.
1.5. На листі 7 створити зведений звіт, використовуючи консолідацію по категорії, застосувавши підсумкову функцію Сума (консолідувати звіти аркушів 1 - 5)
Варіант 9
1.Табличний процесор Microsoft Excel. Консолідація даних таблиць.
1.1.Завантажити табличний процесор Microsoft Excel.
1.2.На п'ятьох аркушах одночасно створити звіти за такою формою і
заповнити довільними даними не менш 5 рядків ( |
в осередки з «*» варто |
|||
поставити формули): |
|
|
||
|
|
Відомість оплати за товари. |
|
|
|
|
|
|
|
Наймену |
Сума |
Торгівельна |
Знижка на |
Разом |
вання |
|
знижка |
відходи |
гр.2 - (гр.3 + гр.4) |
магазина |
|
|
|
|
|
|
|
|
* |
|
|
|
|
* |
|
|
|
|
* |
|
|
|
|
* |
|
|
|
|
* |
Разом: |
* |
* |
* |
* |
1.3. |
На листі 6 створити зведений звіт, використовуючи консолідацію |
по |
|
розташуванню, застосувавши підсумкову функцію Сума (консолідувати |
|
||
звіти аркушів 1 - 3) |
|
|
|
1.4. |
Внести зміни в звіти на аркушах 4 – 5, розширивши асортимент товарів |
||
або помінявши послідовність назв товарів. |
|
по |
|
1.5. |
На листі 7 створити зведений звіт, використовуючи консо |
лідацію |
|
категорії, застосувавши підсумкову функцію Сума (консолідувати звіти |
|
||
аркушів 1 - 5) |
|
|
|
|
Варіант 10 |
|
|
1. Табличний процесор Microsoft Excel. Консолідація даних таблиць.
1.1. Завантажити табличний процесор Microsoft Excel.
1.2. На п'ятьох аркушах одночасно ство рити звіти за такою формою і заповнити довільними даними не менш 5 рядків ( в осередки з «*» варто поставити формули):
170