Створення вкладених функцій. Фільтрація даних в ms excel
КОНТРОЛЬНІ ПИТАННЯ
- Які формати чисел підтримує Excel?
- Як вибирається обрамлення для заголовків колонок і рядків створеної таблиці?
- Що таке діапазон чарунок? Якими способами можна виділити діапазон чарунок?
- Які операції редагування доступні для виділеного діапазону?
- Які типи посилань на комірки використовують у формулах?
- Якими способами можна вставити функцію у формулу?
- Як за допомогою миші спростити ручний ввід формули?
- Описати два засоби використання функції додавання.
- Які типи помилок можливі при обчисленні формул?
Мета: Познайомитися з загальними правилами редагування таблиць. Отримати навички роботи з логічною функцією ЕСЛИ та з функцією ВПР за допомогою майстра функцій. Використання складних функцій. Навчитися використовувати автофільтр та розширений фільтр.
Теоретичні відомості
Назви діапазонів. Для діапазонів можна використовувати літерні назви. Наприклад, якщо діапазону А2:А5 дати назву «Вартість», то формули =СУММ(вартість) і =СУММ(А2:А5) будуть давати однаковий результат. Щоб діапазону дати назву, потрібно виділити його і виконати команду Формулы-Присвоить имя. У діалоговому вікні вказати назву діапазону і натиснути ОК. Літерні назви діапазонів мають таку перевагу як унікальність: їх можна викликати на будь-якому листі книги, в той час як звичайні назви комірок повторюються на кожному листі і деякі функції не можуть їх «побачити» з іншого листа.
Перевірка введення даних. В комірках можна обмежити введення даних. Дані, що вводяться у комірки, можуть бути перевірені на правильність введення інформації. Тобто, для деяких комірок встановлюється контроль, який не дасть ввести помилкові дані.
Порядок введення контролю:
- Виділити необхідну комірку або групу комірок.
- Викликати діалогове вікно Данные-Проверка данных-Проверка данных (Рис. 1).
- У діалоговому вікні встановити обмеження на дані, які будуть вводитися, і повідомлення діалогових вікон при вводі даних і у разі виникнення помилки.
Рис. 1
Ця команда може мати декілька застосувань. Наприклад, якщо потрібно ввести дані в комірку В1 з раніше створеного списку А1:А4, то вибирають тип Список і в параметрах вказують цей діапазон (Рис.2). Тоді в комірці В1 з’явиться кнопка з меню елементів списку.
Рис. 2
Щоб відмінити контроль введення даних, потрібно виділити комірки, викликати команду Данные-Проверка данных і у діалоговому вікні натиснути Очистить все-ОК.
Створення вкладених функцій. В Excel є можливість створення вкладених функцій. Наприклад, для запису формули потрібно використати дві функції - модуль і косинус:
= ABS(COS(х)).
Щоб створити таку формулу потрібно:
- За допомогою Майстра функцій вибрати першу функцію ABS. Встановити курсор у рядок аргументів цієї функції.
- У вікні назви комірки розкрити список функцій (Рис.3).
- Вибрати другу функцію COS. У діалоговому вікні ввести аргументи для функції COS.
- Натиснути ОК.
Рис. 3
Другий варіант введення складних формул. Для введення формули можна використати рядок формул.
- За допомогою Майстра функцій вибирається функція СТЕПІНЬ і заповнюються її аргументи: число і степінь .
- Не натискуючи кнопки ОК, перейти у рядок формул (Рис.4).
- Продовжити введення формули, поставивши „+” і вибрати зі списку вікна назви комірки наступну функцію КОРІНЬ.
- У діалоговому вікні ввести аргумент для другої функції і натиснути ОК.
Рис. 4
Функція ЕСЛИ. При розв’язанні багатьох задач значення комірки має приймати те або інше значення, в залежності від виконання або невиконання умови. Для розв’язання таких задач застосовують умовну функцію ЕСЛИ. Ця функція має формат:
ЕСЛИ (Логічний_вираз(умова), значення_Істина, значення_Неправда).
Перший аргумент – умова, приймає одне з двох значень: «Так» або «Ні». Якщо умова виконана, результатом функції ЕСЛИ буде значення_Істина, а якщо умова не виконана – значення_Неправда.
Рис. 5 Схема роботи функції ЕСЛИ
Наприклад: =ЕСЛИ(A1<0;A1*A1;A1*2). Нехай у комірці А1 стоїть число 12, тоді результат виконання дії буде 24 (Рис.7).
В якості значення може бути число, формула або інша функція. Наприклад: маємо дві функції ЕСЛИ, вкладених одна в одну (Рис. 6):
ЕСЛИ(Логічний_вираз(умова1); значення1_1; ЕСЛИ(Логічний_вираз(умова2); значення2_1; значення2_2)).
Рис. 6 Схема роботи вкладених функцій ЕСЛИ
Рис. 7
Функція ВПР(вертикальный просмотр). Ця функція має дуже широке застосування. Найчастіше її використовують для вибору даних із однієї таблиці в іншу. Формат функції:
=ВПР(шукане значення;таблиця;номер стовпця; інтервальний огляд)
Дія функції: у першому стовпці вказаної Таблиці машина шукає Шукане значення й із вказаного Стовпця бере результат. Параметр Інтервальний огляд вказує на те, чи відсортовано перший стовпець за алфавітом чи ні, і приймає відповідно значення 1 або 0.
Приклад функції: =ВПР(F2;B2:D5;2;0) (Рис.8)
Рис. 8
У прикладі функція ВПР у першому стовпцеві таблиці B2:D5 шукає значення, яке стоїть у F2, та в якості результату бере число, яке стоїть у другому стовпцеві вказаної таблиці. Для таблиці B2:D5 другий стовпець - це Площа, тому результат функції буде 603,7.
Абсолютна та відносна адресація.Посилання на комірки бувають відносні, абсолютні та мішані. Посилання, яке включає назву колонки і номер рядка, є відносним. При копіюванні формули таке посилання модифікується. При копіюванні формули вліво чи вправо – змінюється назва колонки (Рис. 9 а).
Наприклад: формулу “=С3+25” скопіювали вліво на 1 колонку, формула змінилась на “=В3+25”, в разі копіювання вправо формула стала виглядати, як “=D3+25”.
При копіюванні формули вниз або вверх – змінюються номери рядків. Якщо скопіювати формулу на 2 рядка вгору, то номер рядка зменшиться на 2 одиниці, якщо донизу – збільшиться на 2 одиниці.
В абсолютних посиланняхперед назвою колонки або номером рядка стоїть спеціальний символ - $. Такі посилання не модифікуються, тобто при копіюванні, переміщенні такі адреси залишаються без змін. Наприклад: адреса - $А$1(Рис. 9б).
У мішанихпосиланнях абсолютною є назва колонки або номер рядка. У них модифікується тільки відносна частина посилання. Наприклад: адреси $А1; А$1(Рис. 9 в,г).
На клавіатурі кнопка F4 переключає циклічно усі види посилань. Щоб нею скористатися, потрібно поставити курсор у формулі на адресі комірки і натиснути декілька разів F4.
Рис. 9 Приклади копіювання формул з відносними, мішаними
та абсолютним посиланнями
Якщо у формулі є посилання на комірки, які знаходяться на іншому листі, то посилання повинно містити ім’я листа, знак оклику та адресу комірки. Наприклад: Лист3!В2.
Аналогічно додається назва файлу, якщо у формулі є посилання на комірки іншого файлу. Наприклад: Книга2!Лист3!С5.
Фільтрє конструкцією, яка призначена для відбору тих рядків таблиці, що задовольняють даній умові, і тимчасового приховання інших.
Застосування Автофильтра.
- Виділити діапазон, для якого буде створений фільтр.
- Вибрати команду Данные/Фильтр. Після цього автоматично створюється в комірках верхнього рядка виділеного діапазону спеціальна кнопка ▼, що розкриває список фільтру (Рис. 10).
- Натиснувши на кнопку ▼, вибрати один із варіантів відбору даних: перші десять рядків списку, задати умову фільтрації та ін.
Застосування фільтру буде некоректним, якщо таблиця містить об’єднані комірки.
Умова користувача може складатися з одного або двох логічних виразів. В останньому випадку вираження з'єднуються логічними операндами И або ИЛИ.
Щоб зняти фільтр, необхідно повторно вибрати команду Данные/Фильтр. Режим фільтрації можна відмінити командою Снять фильтр с… в меню фільтра.
Рис. 10
Розширений фільтр. Розширений фільтр має можливість переносу відфільтрованих даних у окрему таблицю. При цьому переносяться не всі стовпці таблиці, а лише потрібні користувачу.
Для того, щоб використати розширений фільтр, потрібно:
- Побудувати таблицю з умовами фільтру (діапазон умов).
- Винести окремо заголовки стовпців, які потрібно отримати (діапазон розміщення даних).
- Визвати вікно розширеного фільтру: Данные-Расширенный фильтр. Встановити параметри, натиснути кнопку ОК.
При фільтрації даних необхідно, щоб назви заголовків стовпців були ідентичними (краще використовувати копіювання заголовків).