Створення вкладених функцій. Фільтрація даних в ms excel

Мета: Познайомитися з загальними правилами редагування таблиць. Отримати навички роботи з логічною функцією ЕСЛИ та з функцією ВПР за допомогою майстра функцій. Використання складних функцій. Навчитися використовувати автофільтр та розширений фільтр.

Теоретичні відомості

Назви діапазонів. Для діапазонів можна використовувати літерні назви. Наприклад, якщо діапазону А2:А5 дати назву «Вартість», то формули =СУММ(вартість) і =СУММ(А2:А5) будуть давати однаковий результат. Щоб діапазону дати назву, потрібно виділити його і виконати команду Формулы-Присвоить имя. У діалоговому вікні вказати назву діапазону і натиснути ОК. Літерні назви діапазонів мають таку перевагу як унікальність: їх можна викликати на будь-якому листі книги, в той час як звичайні назви комірок повторюються на кожному листі і деякі функції не можуть їх «побачити» з іншого листа.

Перевірка введення даних. В комірках можна обмежити введення даних. Дані, що вводяться у комірки, можуть бути перевірені на правильність введення інформації. Тобто, для деяких комірок встановлюється контроль, який не дасть ввести помилкові дані.

Порядок введення контролю:

1. Виділити необхідну комірку або групу комірок.
2. Викликати діалогове вікно Данные-Проверка данных-Проверка данных (Рис.9.1).
3. У діалоговому вікні встановити обмеження на дані, які будуть вводитися, і повідомлення діалогових вікон при вводі даних і у разі виникнення помилки.

Рис. 9.1

Ця команда може мати декілька застосувань. Наприклад, якщо потрібно ввести дані в комірку В1 з раніше створеного списку А1:А4, то вибирають тип Список і в параметрах вказують цей діапазон (Рис.9.2). Тоді в комірці В1 з’явиться кнопка з меню елементів списку.

Рис. 9.2

Щоб відмінити контроль введення даних, потрібно виділити комірки, викликати команду Данные-Проверка данных і у діалоговому вікні натиснути Очистить все-ОК.

Створення вкладених функцій. В Excel є можливість створення вкладених функцій. Наприклад, для запису формули потрібно використати дві функції - модуль і косинус:

= ABS(COS(х)).

Щоб створити таку формулу потрібно:

1. За допомогою Майстра функцій вибрати першу функцію ABS. Встановити курсор у рядок аргументів цієї функції.
2. У вікні назви комірки розкрити список функцій (Рис.9.3).
3. Вибрати другу функцію COS. У діалоговому вікні ввести аргументи для функції COS.
4. Натиснути ОК.

Рис. 9.3

Другий варіант введення складних формул. Для введення формули можна використати рядок формул.

1. За допомогою Майстра функцій вибирається функція СТЕПІНЬ і заповнюються її аргументи: число і степінь.
2. Не натискуючи кнопки ОК, перейти у рядок формул (Рис.9.4).
3. Продовжити введення формули, поставивши „+” і вибрати зі списку вікна назви комірки наступну функцію КОРІНЬ.
4. У діалоговому вікні ввести аргумент для другої функції і натиснути ОК.

Рис. 9.4

Функція ЕСЛИ. При розв’язанні багатьох задач значення комірки має приймати те або інше значення, в залежності від виконання або невиконання умови. Для розв’язання таких задач застосовують умовну функцію ЕСЛИ. Ця функція має формат:

ЕСЛИ (Логічний_вираз(умова), значення_Істина, значення_Неправда).

Перший аргумент – умова, приймає одне з двох значень: «Так» або «Ні». Якщо умова виконана, результатом функції ЕСЛИ буде значення_Істина, а якщо умова не виконана – значення_Неправда.

Рис. 9.5 Схема роботи функції ЕСЛИ

Наприклад: =ЕСЛИ(A1<0;A1*A1;A1*2). Нехай у комірці А1 стоїть число 12, тоді результат виконання дії буде 24 (Рис.9.6).

 

 

Рис. 9.6

В якості значення може бути число, формула або інша функція. Наприклад: маємо дві функції ЕСЛИ, вкладених одна в одну (Рис.9.7):

ЕСЛИ(Логічний_вираз(умова1); значення1_1; ЕСЛИ(Логічний_вираз(умова2); значення2_1; значення2_2)).

Рис. 9.7 Схема роботи вкладених функцій ЕСЛИ

Функція ВПР (вертикальный просмотр). Ця функція має дуже широке застосування. Найчастіше її використовують для вибору даних із однієї таблиці в іншу. Формат функції:

=ВПР(шукане значення;таблиця;номер стовпця; інтервальний огляд)

Дія функції: у першому стовпці вказаної Таблиці машина шукає Шукане значення й із вказаного Стовпця бере результат. Параметр Інтервальний огляд вказує на те, чи відсортовано перший стовпець за алфавітом чи ні, і приймає відповідно значення 1 або 0.

Приклад функції: =ВПР(F2;B2:D5;2;0) (Рис.9.8)

 

 

Рис. 9.8

У прикладі функція ВПР у першому стовпцеві таблиці B2:D5 шукає значення, яке стоїть у F2, та в якості результату бере число, яке стоїть у другому стовпцеві вказаної таблиці. Для таблиці B2:D5 другий стовпець - це Площа, тому результат функції буде 603,7.

Абсолютна та відносна адресація. Посилання на комірки бувають відносні, абсолютні та мішані. Посилання, яке включає назву колонки і номер рядка, є відносним. При копіюванні формули таке посилання модифікується. При копіюванні формули вліво чи вправо – змінюється назва колонки (Рис.9.9. а).

Наприклад: формулу “=С3+25” скопіювали вліво на 1 колонку, формула змінилась на “=В3+25”, в разі копіювання вправо формула стала виглядати, як “=D3+25”.

При копіюванні формули вниз або вверх – змінюються номери рядків. Якщо скопіювати формулу на 2 рядка вгору, то номер рядка зменшиться на 2 одиниці, якщо донизу – збільшиться на 2 одиниці.

В абсолютних посиланнях перед назвою колонки або номером рядка стоїть спеціальний символ - $. Такі посилання не модифікуються, тобто при копіюванні, переміщенні такі адреси залишаються без змін. Наприклад: адреса - $А$1(Рис.9.9.б).

У мішаних посиланнях абсолютною є назва колонки або номер рядка. У них модифікується тільки відносна частина посилання. Наприклад: адреси $А1; А$1(Рис.9.9. в,г).

На клавіатурі кнопка F4 переключає циклічно усі види посилань. Щоб нею скористатися, потрібно поставити курсор у формулі на адресі комірки і натиснути декілька разів F4.

 

Рис. 8.9 Приклади копіювання формул з відносними, мішаними
та абсолютним посиланнями

 

Якщо у формулі є посилання на комірки, які знаходяться на іншому листі, то посилання повинно містити ім’я листа, знак оклику та адресу комірки. Наприклад: Лист3!В2.

Аналогічно додається назва файлу, якщо у формулі є посилання на комірки іншого файлу. Наприклад: Книга2!Лист3!С5.

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

Застосування Автофильтра.

1. Виділити діапазон, для якого буде створений фільтр.
2. Вибрати команду Данные/Фильтр. Після цього автоматично створюється в комірках верхнього рядка виділеного діапазону спеціальна кнопка ▼, що розкриває список фільтру (Рис.9.10).
3. Натиснувши на кнопку ▼, вибрати один із варіантів відбору даних: перші десять рядків списку, задати умову фільтрації та ін.

Умова користувача може складатися з одного або двох логічних виразів. В останньому випадку вираження з'єднуються логічними операндами И або ИЛИ.

Щоб зняти фільтр, необхідно повторно вибрати команду Данные/Фильтр. Режим фільтрації можна відмінити командою Снять фильтр с … в меню фільтра.

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

Для того, щоб використати розширений фільтр, потрібно:

1. Побудувати таблицю з умовами фільтру (діапазон умов).
2. Винести(скопіювати) окремо заголовки стовпців, які потрібно отримати (діапазон розміщення даних).
3. Визвати вікно розширеного фільтру: Данные-Расширенный фильтр. Встановити параметри, натиснути кнопку ОК.

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

Рис. 9.10

ЗАВДАННЯ

1. Відкрити новий файл.

2. Оформити Довідник посад, що містить оклади. Лист назвати Оклади.

3. Для використання цих даних на іншому листі слід коміркам В4:С7 дати назву Оклад (виділити діапазон; виконати команду Формулы→Присвоить имя; в рядку Ім’я набрати Оклад; в рядку Діапазон буде проставлено назву діапазону; натиснути ОК).

4. Аналогічно, діапазону В4:В7 дайте назву Посада.

5. На окремому листі побудувати відомість заробітної плати. Лист назвати Січень.

6. Для заповнення назви посади для кожного робітника скористаємося списком посад на листі Оклади (виділити діапазон С6:С14 на листі Січень; виконати команду Данные → Проверка данных; в рядку Тип вибрати Список; в рядку Источник набрати формулу =Посада; натиснути ОК).

7. Заповніть стовпчик Посада, використовуючи кнопку вибору, яка з’явиться у комірках С6:С14.

8. Оклад слід отримати з листа Оклад, застосувавши функцію ВПР: =ВПР(C6;оклад;2;0)

 

9. Обчислити суму нарахованих грошей за відпрацьовану кількість днів за формулою:

¾ Нараховано =(Оклад/К-ть робочих днів)*Відпрац. дні

¾ Премія дорівнює 20% від Нараховано, якщо відпрацьовані дні дорівнюють кількості днів у місяці (використати функцію ЕСЛИ і абсолютні посилання).

¾ Загальна сума грошей обчислюється за формулою:

Сума=Нараховано+Премія

¾ Порахувати Прибутковий податок, який розраховується за наступних умов:

Якщо Сума < 2300, то прибутковий податок дорівнює 0.

Якщо Сума < 2600, то прибутковий податок дорівнює 10% від Суми.

Якщо Сума >=3000, то прибутковий податок дорівнює 20% від Суми.

(використати вкладені функції ЕСЛИ)

¾ Знайти відрахування до Пенсійного фонду, які дорівнюють 2% від Суми.

¾ Порахувати Суму до видачі, яка розраховується за формулою:

¾ Сума до видачі=Сума – Прибутковий податок – Пенсійний фонд

¾ Порахувати загальну суму по полю Сума до видачі.

10. На окремих листах скласти аналогічні відомості для лютого і березня.

11. На наступні два листи скопіювати таблицю Відомість заробітної плати.

12. Змінити назви листів відповідно на Лютий і Березень.

13. Змінити в них дані по полю Кількість робочих днів: для лютого – 17, для березня – 21.

14. У полі Відпрацьовані дні обох таблиць змінити дані відповідно до наступної таблиці.

Прізвища	Відпрац. дні лютий	Відпрац. дні березень
Іващенко
Сидорук
Коваленко
Гаврилов
Денисенко
Петренко
Давидов
Карпенко
Симоненко

15. Оформити на окремому листі загальну відомість за квартал.