Консолідація даних.

Консолідація виконується в тому випадку, коли потрібно підсумувати дані, які розташовані в різних діапазонах таблиці. Діа­пазони можуть знаходитися на одному робочому листі або на різних листа і в різних книгах. Наприклад, якщо в різних книгах міститься певна інформація, то для створення підсумкового листа можна використати консолідацію. Цю команду можна використовувати декількома способами. Можна консолідувати дані з початковими даними, так щоб зміни в початкових листах приводили до змін в підсумковому листі. Або можна консолідувати дані без зв'язків. Консолідувати дані можна по розташуванню (by position) або по категорії (by сategory). Якщо консолідуються дані по розташуванню, то збирається інфор­мація з однаково розташованих комірок всіх початкових листів. При консолідації даних по категорії EXCEL використовує за основу об'єднання листів заголовки стовпців або рядків. Консолідація по категорії надає більшу свободу для організації даних.

Консолідація за розташуванням.

Нехай маємо на листах І семестр і 2 семестр список студентів з оцінками по відповідних предметах. До цих таблиць застосований автоформат і вони мають вигляд:

З допомогою консолідації за розташуванням хочемо отримати середнє ариф­метичне кожного з студентів за два семестри. Спочатку підготуємо місце для втримання результатів. Скопіюємо стовпець А з листа 1 семестр або 2 семестр на новий лист Середнє в комірки стовпця А. Потім скопіюємо комірки B1:D1 у відповідні комірки листа Середнє. Позначаємо діапазон комірки B2:D7 на листі Середнє і виконуємо команду Дані => Консолідація. З'являється діалогове вікно.

У рядку функції вибираємо тип функції. Для нашого прикладу СРЗНАЧ. У рядку адреса вибираємо діапазони комірок або набираємо з клавіатури, або шляхом позначення діапазонів (вікно консолідація можна зменшити або перенести). Наприклад, переходимо на лист 1 се-местр і визначаємо діапазон $B$2:$D$7, клацаємо на клавіші Додати. Переходимо на лист 2 семестр і позначаємо такий самий діапазон і знову клацаємо на клавіші Додати. У полі список діапазонів висвітлюються всі позначені діапазони. Якщо діапазони комірок знаходяться в інших книгах, то клацаємо на клавіші Переглянути, знаходимо файл, відкриваємо його і позначаємо потрібні комірки. Якщо певні комірки непотрібні, позначаємо їх в полі Список діапазонів і клацаємо на клавіші Витерти. Після цього клацаємо на ОКі з'являєть­ся таблиця виду, де обчислюється середнє арифметичне за два семестри.

Консолідація по категорії.

Тепер розглянемо більш складний при­клад. Нехай у другому семестрі у групі з'явилися ще два студенти, яких не було у першому семестрі. Розташуємо курсор у чарунці А1 на листі категорія Дані будемо брати з листів 1 семестр і 2семестр. Але у листі 2семестр додається інформація що про двох студентів. Для консолідації по категорії виконаємо команду Дані => Консолідація. З'являється діалого­ве вікно. У рядку Функції вибираємо срзнач. У рядку адреса вибираємо відповідні діапазони комірок. З лис­та 1 семестр вибираємо $A$1:$D$7 і клацаємо на Додати з листа 2семестр вибираємо $AS1:$D$9 і клацаємо на ADD (додати). Для консолідації по рядках активізуємо команду значення лівого стовпця і кла­цаємо на ОК.

Стовпець А містить прізвища сту­дентів і заголовок. Інші стовпці без заголовків. Але заголовки можна ско­піювати з відповідної таблиці і вико­ристати команду автоформат для кра­щого вигляду таблиці. Якщо почат­кові значення змінюються, то консол­ідуючу таблицю потрібно поновлю­вати. Для цього потрібно ввімкнути опцію створити зв'язок з початковими даними. Встановлюється динамічний зв'язок і автоматично забезпечується оновлення консолідуючої таблиці.

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

а) Відсортуйте прізвища учасників за алфавітом і змініть їх нумерацію;

б) Відсортуйте турнірну таблицю по кількості очок учасників від найбільшої до найменшої, сформуйте новий стовпець Місце і введітьмісця учасників.

в) Виведіть дані про учасників з України, скориставшись Автофільтром

г) Відобразіть всю таблицю і виведіть дані про спортсменів, які зайняли три перші або три останні місця

д) Відобразіть всю таблицю і виведіть дані про спортсменів, які набрали понад 5000 очок.

Завдання 2. Сформуйте телефоний довідник працівників фірми який складається з 20 записів у вигляді, показанному на рисунку:

а) знайдіть у базі всі записи, які містять прізвище «Бойко», скориставшись Розширеним фільтром;

б) Відшукайте всі записи, які містять ім’я «Роман»;

в) відфільтруйте базу даних так, щоб у ній було видно ті записи, що стосуються тих абонентів, для яких номери робочих телефонів знаходяться в діапазоні від 420000 до 440000.

г) Виведіть список місцевих абонентів.

д) Виведіть всі записи, для яких прізвище працівника починається з літери «А» або «Б»

Завдання 3. Створіть базу даних “Успішність”, що містить відомості про оцінки студентів вашої групи з різних предметів і виконайте наступні завдання:

Екзаменаційні оцінки
№	Прізвище	Ім'я	По-батькові	Матем.	Фізика	Чис.мет.	Анг.мова	Інформ.
1	Артист	Іван	Петрович	3	3	3	4	5
2	Бодак	Марта	Василівна	5	5	4	5	4
3	Пелех	Іра	Миронівна	4	4	4	4	5
4	Ковальчук	Ліля	Іванівна	3	3	4	3	4
5	Захарко	Оксана	Ігорівна	5	5	5	5	5

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

б) Додайте у базу ще 4 записи і вдсортуйте студентів у алфавітному порядку.

в) Змініть нумерацію записів, користуючись формою (у полі № введіть число 1 і натисніть клавішу Enter. Введіть 2 і Enter, і так далі)

г) Виведіть дані про студентів, які одержали за перший екзамен оцінку 3, скориставшись кнопкою Критерії. З допомогою кнопок Далі і Назад перегляньте дані про відповідних студентів.

д) Виведіть дані про студентів, які одержуватимуть стипендію (оцінки по всіх предметах не менше 4).

е) Виведіть дані про студентів, які отримуватимуть підвищену стипендію- всі оцінки «5».

є) Вилучіть запис, що стосується вас, скориставшись формою.

Збережіть вашу робочу книгу.

Завдання 4. Побудувати три таблиці, які містять дані по місяцях для кожної фірми витрати різних категорій Таблиці містяться на різних листах, які мають відповідно назви Фірма 1, Фірма2; Фірма З. Таблиці повинні виглядати, наприклад, наступним чином:

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