Практичне завдання № 1 (4 год. )

СТВОРЕННЯ І ВИКОРИСТАННЯ БАЗИ ДАНИХ У Excel

Створіть список з даними відділу кадрів у якому передбачте обов'язкові стовпці:

Табельний №, прізвище, ім'я, по батькові, стать, дата народження, освіта, посада, оклад, дата прийняття на роботу, володіння іноземною мовою і будь-які інші.

Наприклад:

Таб. №	Прізв.	Ім'я	По батькові.	Стать	Дата нар.	Освіта	Посада	Оклад	Дата пр. на роб.	Іноз. мова
	Петренко	Петро	Іванович	ч	01.01.81	вища	юрист		01.02.04	фр.

У списку повинно бути не менш ніж 20 записів.

Використайте список як базу даних для пошуку записів по наступним умовам (після виконання кожного пункту необхідно скопіювати результат в окремий аркуш книги):

1. Визначите, чи є на підприємстві співробітник на прізвище Петренко.

2. Визначите, чи є чи на підприємстві співробітники 1983 року народження і скільки їх?

3. Визначите, чи є на підприємстві юристи, що володіють англійською мовою і скільки їх?

4. Визначите, чи є на підприємстві співробітники у віці від 25 до 30 років, що мають вищу освіту і скільки їх?

5. Визначите, чи є на підприємстві співробітники у віці до 30 чи після 40 років, що мають середню освіту і скільки їх?

6. Знайдіть запису про всіх юристів, прийнятих на роботу в період з 1 січня 1986 року по 30 листопада 2005 року

7. Визначите, скільки на підприємстві жінок з не вищою освітою?

8. Знайдіть записи про всіх співробітників, що мають вищу освіту, прізвища яких починаються із символів "Б" чи "П", скільки таких співробітників?

9. Хто на підприємстві одержує самий високий оклад і найнижчий оклад і які розміри цих окладів?

10. Хто зі співробітників підприємства був прийнятий на роботу першим і коли це відбулося?

11. Хто наймолодший співробітник на підприємстві і хто самий старий?

12. Знайдіть записи про всіх співробітників, прийнятих на роботу після 01 вересня 2001 р.

13. Знайдіть записи про всіх бухгалтерів і економістів старше 40 років.

14. Визначите співробітників, яки мають оклади від 600 до 1000 гривень.

15. Визначите співробітників, яки мають оклади більше середнього окладу.

16. Визначите співробітників, яки мають оклади більш 1000 і менш 600 гривень.

17. Знайдіть записи про всіх співробітників прийнятих на роботу в 1989 році та скільки їх.

18. Відсортуйте рядки таблиці по двох групах: група чоловіків і група жінок, а усередині цих груп по прізвищах.

19. Відсортуйте рядки таблиці по полю Освіта у спадному порядку, а в групах, що вийшли - за алфавітом прізвищ у висхідному порядку.

20. Видати прізвища, імена й оклади усіх юристів у віці від 30 до 40 років (результат помістити в окремий діапазон листа).

21. Видати прізвища та дати народження усіх економістів, яки старше 30 років або у яких оклади більше 1000 гр. (результат помістити в окремий діапазон листа).

5.2. КОНСОЛІДАЦІЯ ДАНИХ СПИСКУ

5.2.1. ОСНОВНІ ПОНЯТТЯ.

Під консолідацією розуміється узагальнення однорідних даних з різних джерел. Уміння консолідувати інформацію з різних джерел дуже високо цінується у всіх сферах ділового життя. Наприклад, при консолідації на основі значень декількох клітинок листу Excel формується значення e результуючої клітинці шляхом підсумовування, обчислення середнього значення, максимуму і т.д. Типовий приклад використання консолідації – узагальнення даних по виторзі, прибутку і т.д.

Наприклад, на трьох аркушах у Вас розташовані однакові за структурою таблиці за січень, лютий і березень. А Вам потрібно одержати сумарні дані за перший квартал. Це і виходить, що потрібно консолідувати три таблиці в одну.

Інструмент Консолідація дозволяє об'єднати дані таблиць, що знаходяться в різних місцях однієї книги, різних книг (тобто різних файлів!). Усі таблиці повинні мати абсолютно ідентичну структуру. З'єднання даних не є механічним.

У Excel існує два основних види консолідації даних:

· консолідація по фізичному розташуванню осередків і

· консолідація по заголовках.

Ці два види консолідації розрізняються способом, котрим задається зв'язок вихідних осередків з підсумковими.

При консолідації по фізичному розташуванню вказуються номери осередків, у яких містяться вихідні дані.

При консолідації по заголовках використовуються заголовки рядків і стовпців. При цьому консолідуються дані, розташовані в рядках і стовпцях з однаковими назвами.

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

Консолідацію по категорії варто використовувати у випадку, якщо потрібно узагальнити набір аркушів, що мають однакові заголовки, але з різною організацією даних. Цей спосіб дозволяє консолідувати дані з однаковими заголовками з всіх аркушів.

КОНТРОЛЬНІ ПИТАННЯ.

1. Що таке консолідація даних?

2. Які існують види консолідації і чим вони відрізняються?

3. Як перейменувати лист книги?

4. Приведіть приклад задачі, розв'язуваної за допомогою консолідації.

5. У якому випадку варто використовувати консолідацію даних по фізичному розташуванню?

5.2.2. ПРАКТИЧНЕ ЗАВДАННЯ № 2.

КОНСОЛІДАЦІЯ ДАНИХ ПО ФІЗИЧНОМУ РОЗТАШУВАННЮ.

Відкрийте нову книгу. Перейменуйте аркуш Лист 1 на Січень і наберіть невелику таблицю починаючи з клітинки а1

2. Аналогічно створіть таблицю на наступному аркуші Лист1, якому дайте ім'я " Лютий ":

3. Аналогічно створіть аркуш " Березень ":

4. Створіть ще аркуш " 1 квартал ":

5. Тепер виділите інтервал b1:b3 на аркуші " 1 квартал " і виконайте команди Данные > Консолидация. Ви побачите діалогове вікно " Консолидация ".

6. Поставте прапорець ліворуч від напису " Создавать связи с исходными данными ". Це значить, якщо у Вас будуть мінятися первинні таблиці, автоматично буде перераховуватися і підсумовуюча таблиця.

Примітка: Зв'язки не можна використовувати, якщо вихідна область і область призначення знаходяться на одному листі. Після установки зв'язків не можна додавати нові вихідні області і змінювати вихідні області, що уже входять у консолідацію.

7. Клацніть по віконцю " Ссылка ". Тепер потрібно вказати, що саме Ви збираєтеся консолідувати.

8. Клацніть по закладці аркушу " Січень " і виділите інтервал b1:b3. Клацніть по кнопці " Добавить ".

9. Перейдіть на аркуш " Лютий " і повторите цю процедуру. Зрозуміло, що і з аркушом " Березень " потрібно проробити те ж. А от тепер можна і клацнути по кнопці ОК. Перевірте: на аркуші " 1 квартал " повинні бути підсумовуючі дані.

10. Збережіть отриману книгу з ім'ям Консолідація-1.

Кнопки зі знаком + дозволяють подивитися на дані, по яких виконувалось підсумовування і змінювати їх.

Питання. Що буде, якщо змінити дані, наприклад, за березень? Чи зміняться підсумовуючі дані?

Нагадаємо, Ви включили режим " Создать связи с исходными данными ". До речі, як Ви думаєте, чи завжди потрібно його включати?

Примітка: Консолідацію можна робити не тільки за допомогою підсумовування. Можна використовувати й інші функції, наприклад, максимум чи середнє (для цього при консолідації потрібно клацнути по віконцю " Функция " і вибрати потрібну функцію).

Що буде, якщо консолідувати дані, що знаходяться не в однакових інтервалах (у нас усе було в інтервалах b1:b3, а могло б бути і так: у січні c4:c6, у березні - a8:a10, а в лютому - b1:b3)?

Слід зазначити, що консолідація може бути застосована і до тільки однієї таблиці, при цьому попереднє сортування не потрібно.

Дата добавления: 2014-11-29