• Гідрологія і Гідрометрія
• Господарське право
• Економіка будівництва
• Економіка природокористування
• Економічна теорія
• Земельне право
• Історія України
• Кримінально виконавче право
• Медична радіологія
• Методи аналізу
• Міжнародне приватне право
• Міжнародний маркетинг
• Основи екології
• Предмет Політологія
• Соціальне страхування
• Технічні засоби організації дорожнього руху
• Товарознавство продовольчих товарів

Створення вкладених функцій. Фільтрація даних в 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. Визвати вікно розширеного фільтру: Данные-Расширенный фильтр. Встановити параметри, натиснути кнопку ОК.

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

Читайте також:

1. ACCESS. СТВОРЕННЯ ЗВІТІВ
2. ACCESS. СТВОРЕННЯ ФОРМ
3. Git виконує перевірку цілісності даних
4. INSERT INTO EXCELLENT
5. ISO 15504. Причини та історія створення
6. Microsoft Excel кестелік процессоры
7. А. Створення власної папки.
8. Автоматизований банк даних (АБД).
9. Автоматичне і ручне створення об’єктів.
10. Адаптація законодавства України до законодавства ЄС - один із важливих інструментів створення в Україні нової правової системи та громадянського суспільства
11. Адаптація законодавства України до законодавства ЄС - один із важливих інструментів створення в Україні нової правової системи та громадянського суспільства
12. Аденогіпофіз, його гормони, механізм впливу, прояви гіпер- та гіпофункцій.

Переглядів: 1494