Логічні, статистичні функції в Excel

Опис документу:
Теоретичний матеріал (методичний посібник до уроку)

Відображення документу є орієнтовним і призначене для ознайомлення із змістом, та може відрізнятися від вигляду завантаженого документу. Щоб завантажити документ, прогорніть сторінку до кінця

Перегляд
матеріалу
Отримати код Поділитися

План уроку

Тема № 2. Обробка табличної інформації.

Тема уроку: Логічні, статистичні функції в Excel.

Мета уроку: Ознайомити з використанням логічних та статистичних функцій в Excel. Розглянути приклади використання логічних та статистичних функцій, їх можливості та призначення. Розвивати логічне мислення, пам’ять, увагу, самоконтроль і вміння конспектувати; Виховувати інтерес до предмету та наполегливість у навчанні, інформаційну культуру, акуратність, дисциплінованість, посидючість.

Метод проведення уроку: лекція.

Тип уроку: пояснювально – демонстраційний.

Обладнання: зошит, підручник, опорний конспект, наочний матеріал, роздатковий матеріал, комп’ютер.

Хід уроку

І. Оргмомент.

ІІ. Актуалізація опорних знань учнів.

ІІІ. Оголошення теми та мети уроку.

ІV. Вивчення нового матеріалу:

ЛОГІЧНІ ФУНКЦІЇ

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

1. = Дорівнює;

2. > Більше;

3. < Менше;

4. >= Більше або дорівнює;

5. <= Менше або дорівнює;

6. <> Не дорівнює.

Результатом логічного вираження є логічне значення «ИСТИНА» (1) або логічне значення «ЛОЖЬ» (0).

Excel містить наступні логічні функції:

  • «ЕСЛИ»;

  • «И»;

  • «ИЛИ»;

  • «ИСТИНА»;

  • «ЛОЖЬ»;

  • «НЕ».

Для роботи з логічними функціями в Excel використовується «Мастер функций», який можна викликати за допомогою команди Головного меню - Вставка \ Функция…, або за допомогою кнопки «Вставка функции» яка розташована поряд з Рядком формул вікна MS Excel. Після виклику вікна «Мастер функций» потрібно у ньому ж обрати категорію «Логические» (див. мал. 1) після чого у відповідному полі буде відображено всі логічні функції Excel, значення яких можна дізнатися за допомогою гіперпосилання «Справка по этой функции», що знаходиться у цьому ж вікні, тим самим викликавши вікно довідки MS Excel.

Мал. 1

Мал. 2

Функція «ЕСЛИ»:

Дія цієї функції ґрунтується на використанні мулевої алгебри, у якій змінні можуть набувати тільки значення «ИСТИНА» або «ЛОЖЬ» (1 або 0). Ця функція має наступний синтаксис:

ЕСЛИ(логічний вираз; значення_якщо_істина; значення_якщо_брехня)

У першому випадку функція набуває значення істини, у другому – значення брехні.

При конструюванні більш складних перевірок в якості значень можуть бути вкладені функції. Число вкладених функцій «ЕСЛИ» не має бути більшим від семи.

Розглянемо приклад використання логічної функції «ЕСЛИ»:

Наприклад, у навчальному закладі відкривається математичний клас (клас поглибленого вивчення математики). Для того щоб перевести учня зі звичайного класу у математичний він повинен мати загальну кількість балів з геометрії та алгебри не менше 20 балів. Для визначення результатів ми використовуємо Excel, де створюємо таблицю яка містить ПІБ учня, оцінки з геометрії та алгебри і колонку Переведення в якій повинно міститися підтвердження про переведення того чи іншого учня до математичного класу. Для виконання цієї умови ми скористаємося логічною функцією «ЕСЛИ», у діалоговому вікні «Аргументы функции» зазначимо наступну умову:

=ЕСЛИ(СУММ(B2:C2)>=20;"Перевести";"Не переводити")

що означає:

Якщо, сума діапазону комірок В2:С2 більше або дорівнює 20, то значення істини буде «Перевести» (тобто в колонці «Переведення» з’явиться слово «Перевести»), а значення брехні – «Не переводити» (тобто в колонці «Переведення» з’являться слова «Не переводити») (див. мал. 2).

Функція «И, ИЛИ»:

Якщо умов багато, записувати вкладені функції «ЕСЛИ» стає незручно. У цьому випадку на місці логічного виразу можна вказати одну з двох логічних функцій: «И» або «ИЛИ». Синтаксис функцій однаковий:

И(логічний вираз 1; логічний вираз 2…)

ИЛИ(логічний вираз 1; логічний вираз 2…)

Функція И(логічний вираз 1; логічний вираз 2…) повертає значення «ИСТИНА», якщо всі логічні вирази мають значення «ИСТИНА», і повертає значення «ЛОЖЬ», якщо хоча б один логічний вираз має значення «ЛОЖЬ».

Функція ИЛИ(логічний вираз 1; логічний вираз 2…) повертає значення «ИСТИНА», якщо хоча б один логічний вираз має значення «ИСТИНА».

Розглянемо приклад використання логічної функції «И»:

1. Наприклад, потрібно визначити чи пройшов учень атестацію. Хай Excel повертає текст "Пройшов", якщо учень має середній бал більше 4 (комірка А2), і пропуск занять менше 3 (комірка А3). Формула матиме наступний вигляд:

=ЕСЛИ(И(А2>4;А3<3);"Пройшов";"Не пройшов")

Мал. 3

Мал. 4

що означає:

У відповідній комірці буде виводитися слово "Пройшов" за умови якщо учень має середній бал більше 4 і пропусків занять менше 3 або "Не пройшов" за умови якщо учень має середній бал менше 4 і пропусків занять більше 3 (див. мал. 3).

2. Наприклад, нам потрібно щоб було виведено значення «ИСТИНА» для числа 50. Для цього використовуємо логічну функцію «И» синтаксис якої буде наступним:

=И(1<A2; A2<100)

що означає:

Так як 50 (комірка А2) більше 1 і менше 100 (комірка А3) - значення «ИСТИНА» (див. мал. 4).

3. Таблиця для функцій «И», «ИЛИ»:

х

у

И (х; у)

ИЛИ (х; у)

ЛОЖЬ

ЛОЖЬ

ЛОЖЬ

ЛОЖЬ

ЛОЖЬ

ИСТИНА

ЛОЖЬ

ИСТИНА

ИСТИНА

ЛОЖЬ

ЛОЖЬ

ИСТИНА

ИСТИНА

ИСТИНА

ИСТИНА

ИСТИНА

Функція «НЕ»:

Функція «НЕ» змінює на протилежне логічне значення свого аргументу. Використовується в тих випадках, коли необхідно упевнитися в тому, що значення не дорівнює деякій конкретній величині. Синтаксис:

НЕ(логічне значення)

Логічне значення - це величина або вираз, які можуть набувати двох значень: «ИСТИНА» або «ЛОЖЬ».

Розглянемо принцип роботи логічної функції «НЕ»:

Формула

Опис (результат)

=НЕ(ЛОЖЬ)

Змінює на протилежне значення «ЛОЖЬ» («ИСТИНА»)

=НЕ(1+1=2)

Змінює значення «ИСТИНА», якому дорівнює логічний вираз, на протилежне («ЛОЖЬ»)

СТАТИСТИЧНІ ФУНКЦІЇ

В Excel найбільш широко представлені функції, призначені для проведення різного роду статистичних обчислень: максимальне і мінімальне значення діапазону, значення вірогідності, розподілу випадкових величин, середні дисперсії, підрахування елементів у виділеному фрагменті і т.д.

Статистичні функції частіше за все працюють на діапазони комірок.

Діапазони задаються індексами кутових комірок, записаними через двокрапку.

До групи статистичних функцій входять такі популярні функції, як:

  • «СРЗНАЧ»;

  • «МАКС»;

  • «МИН»;

  • «СЧЁТ»;

  • «НАИБОЛЬШИЙ»;

  • «НАИМЕНЬШИЙ»;

  • «МОДА»;

  • «СЧЁТЕСЛИ»;

  • «РАНГ».

Д

Мал. 5

ля роботи зі статистичними функціями в Excel використовується «Мастер функций», який можна викликати за допомогою команди Головного меню - Вставка \ Функция…, або за допомогою кнопки «Вставка функции» яка розташована поряд з Рядком формул вікна MS Excel. Після виклику вікна «Мастер функций» потрібно у ньому ж обрати категорію «Статистические» (див. мал. 5) після чого у відповідному полі буде відображено всі логічні функції Excel, значення яких можна дізнатися за допомогою гіперпосилання «Справка по этой функции», що знаходиться у цьому ж вікні, тим самим викликавши вікно довідки MS Excel.

Розглянемо докладніше деякі з цих статистичних функцій:

Функція «РАНГ»

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

Синтаксис функції:

РАНГ(число;посилання;порядок)

Аргументами цієї функції є:

Число – число, для якого визначається ранг;

Посилання – масив або посилання на список чисел, для яких визначається ранг. Нечислові значення ігноруються.

Порядок – число, яке визначає спосіб упорядкування. Якщо порядок дорівнює 0, то Excel визначає ранг числа так, якщо б посилання було списком, відсортованим у порядку спадання (від найвищого до найнижчого). Якщо порядок – будь – яке ненульове число, то Excel визначає ранг числа так, якщо б посилання було списком, відсортованим у порядку зростання (від найнижчого до найвищого).

Тут слід звернути увагу, що функція «РАНГ» присвоює числам, які повторюються, однаковий ранг. При цьому наявність чисел, що повторюються, впливає на ранг наступних чисел. Наприклад, якщо у списку цілих чисел двічі трапляється число 10, яке має ранг 5, то число 11 матиме ранг 7 (зверніть увагу, що тут жодне з чисел не матиме рангу 6).

Приклад №1.

Продемонструємо використання функції «РАНГ» на такому прикладі:

Припустимо, що на уроці учні отримали такі оцінки:

  • Петрик – 8;

  • Мишко – 9;

  • Марічка – 10;

  • Юрко – 6;

  • Сашко – 6;

  • Катруся – 8.

Слід проранжувати Петрика за успішністю роботи на уроці.

Для цього в стовпчик А (з 1 по 6 рядок) занесемо імена учнів, а у стовпчик В – отримані учнями оцінки. Тоді у комірку В7 записуємо формулу:

=РАНГ(В1;В1:В6;1)

Отже після натискання клавіші Enter ми отримаємо результат – 4.

Для визначення рангу оцінки Мишка ми запишемо таку форму:

=РАНГ(В2;В1:В6;1)

Отримаємо результат – 5.

Функція «МОДА»

Повертає значення, яке найчастіше повторюється в певному інтервалі даних. Функція «МОДА» є мірою взаємного розташування значень.

Синтаксис функції:

МОДА(число1;число2…)

число1; число2… - від 1 до 30 аргументів, для яких обчислюється мода.

Можна використовувати одне на масив посилання замість аргументів, які розділяються крапкою з комою. Якщо у множині даних відсутні числа, які повторюються, то функція «МОДА» повертає значення помилки #Н/Д.

Наприклад, для того, щоб визначити, яку оцінку найчастіше учні отримали на уроці (див. приклад №1) слід записати таку формулу:

=МОДА(В1:В6)

Функція «СЧЁТЕСЛИ»

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

Синтаксис функції:

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

Діапазон – діапазон, у якому потрібно підрахувати комірки.

Критерій – критерій у формі числа, виразу чи тексту, який визначає, які комірки потрібно підрахувати. Наприклад, критерій може бути таким: 12, «32», «>10».

Наприклад, для того щоб визначити, хто з учнів на уроці отримав оцінку 10 та вище (див. приклад №1), слід записати таку формулу:

=СЧЁТЕСЛИ(В1:В6; «>10»)

Функція «СРЗНАЧ»

Повертає середнє арифметичне своїх аргументів.

Синтаксис функції:

СРЗНАЧ(число1; число2…)

число1; число2… - це від 1 до 30 аргументів, для яких визначається середнє арифметичне.

Отже, з прикладу №1 обчислимо, яку середню оцінку отримали за урок учні:

=СРЗНАЧ(В1:В6)

Зауваження

(для функцій «РАНГ», «МОДА», «СРЗНАЧ»)

Аргументи мають бути числами. Якщо аргумент, який є посиланням, містить текст, логічне значення або порожні комірки, то ці значення ігноруються; комірки, які містять 0 (нуль), не враховуються.

V. Закріплення нового матеріалу.

VІ. Підведення підсумку уроку.

VІІ. Завдання до наступного уроку: конспект.

Викладач: Теницький С. М.

5

Зверніть увагу, свідоцтва знаходяться в Вашому особистому кабінеті в розділі «Досягнення»

Курс:«Інтернет-ресурси для опитування і тестування»
Левченко Ірина Михайлівна
24 години
490 грн
490 грн