5.1.6.3. Використання функцій суммесли і счётесли

Дані функції дозволяють задавати критерії безпосередньо у формулі.

Функція СЧЁТЕСЛИ має наступний синтаксис:

=СЧЁТЕСЛИ(діапазон;критерій)

, де діапазон - це діапазон, у якому потрібно підрахувати кількість значень, а критерій - це текстове значення, що задає умова. Так, для підрахунку числа співробітниць у списку (Рис. 2) можна використовувати функцію =СЧЁТЕСЛИ(E2:E7;"ж") . Функція поверне результат 3.

Функція СУММЕСЛИ має синтаксис:

=СУММЕСЛИ(диапазон; критерий; диапазон_суммирования)

, де критерий - умова, застосовувана до діапазону, а диапазон_суммирования задає діапазон значень, яки підсумовуються.

Так, для підрахунку сумарного окладу, одержуваного чоловіками, для списку (Рис. 2) можна використовувати функцію =СУММЕСЛИ(E2:E7;"м";D2:D7). Функція поверне результат 1550.

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

1. Що таке список у Excel?

2. Які можливості по редагуванню баз даних надає команда ФОРМА ?

3. Які критерії можна задавати в команді форма ?

4. Для чого потрібна сортування записів і як її здійснити ?

5. Як задати порядок вторинного сортування ?

6. Як відсортувати тільки один стовпець у таблиці ?

7. Як здійснити сортування по рядках ?

8. Чи розрізняються верхній і нижній регістр при сортуванні ?

9. Що можна зробити за допомогою команди АВТОФИЛЬТР ?

10. Які параметри потрібно установити щоб дані вибиралися при одночасному виконанні двох умов?

11. Які параметри потрібно установити щоб дані вибиралися при виконанні хоча б одного з двох умов?

12. Що можна зробити за допомогою команди РАСШИРЕННЫЙ ФИЛЬТР ?

Практичне завдання № 1 (4 год.) створення і використання бази даних у Excel

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

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

Наприклад:

Таб. №	Прізв.	Ім'я	По батькові.	Стать	Дата нар.	Освіта	Посада	Оклад	Дата пр. на роб.	Іноз. мова
123	Петренко	Петро	Іванович	ч	01.01.81	вища	юрист	980	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. КОНСОЛІДАЦІЯ ДАНИХ СПИСКУ