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

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

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

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

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

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

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

Рис. 1

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

Рис. 2

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

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

= ABS(COS(х)).

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

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

Рис. 3

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

1. За допомогою Майстра функцій вибирається функція СТЕПІНЬ і заповнюються її аргументи: число і степінь .
2. Не натискуючи кнопки ОК, перейти у рядок формул (Рис.4).
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.

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

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

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

Застосування фільтру буде некоректним, якщо таблиця містить об’єднані комірки.

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

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

Рис. 10

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

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

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

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