Лекція 10. Основні можливості опрацювання даних в Microsoft Excel

Введення даних Microsoft Excel

Для того, щоб створити книгу в Microsoft Excel необхідно відкрити його, обрати Пуста книга або натиснувши комбінацію клавіш Ctrl+N.

Введення даних

Для того, щоб заповнити ряд даних необхідно ввести два перші значення ряду, наприклад "Січень" і "Лютий" або "2020" і "2021". Виділити дві комірки, що утворюють початок ряду, і перетягнути маркер заповнення  вбік або вниз.

Типи посилань на комірки

Для того, щоб створити таку послідовність, як «1, 2, 3, 4, 5...», необхідно ввести 1 і 2 в перші дві комірки. Щоб створити таку послідовність, як «2, 4, 6, 8...», необхідно ввести 2 та 4. Щоб створити послідовність типу «2, 2, 2, 2...», введіть 2 лише в першу комірку.

Далі необхідно перетягнути маркер заповнення.

Закріплення та розділення рядків і стовпців

Закріплення та розділення рядків і стовпців

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

Суцільна лінія під рядком 1 вказує, що рядок закріплено для відображення заголовків стовпців у місці, під час прокручування.

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

Додатково про розділення областей для блокування рядків або стовпців в окремих частинах аркуша.

ПРИМІТКА: Не можна закріпити області та розділення областей одночасно. Ви можете ввімкнути лише один із двох.

Закріплення верхнього рядка

Зверніть увагу, що межа під рядком 1 дещо темніша порівняно з іншими межами, а це свідчить про те, що рядок вище цієї межі закріплено.

Закріплення першого стовпця

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

Тепер лінія праворуч стовпця А дещо темніша порівняно з іншими лініями, а це свідчить про те, що стовпець ліворуч цієї лінії закріплено.

Відкріплення рядків або стовпців

• На вкладці вигляд > вікно > Звільнити області.

Обчислення за допомогою функції Microsoft Excel

Функція «Сума» (SUM)

Для того, щоб використати функцію «Сума» необхідно виділити комірки під числами, які потрібно додати, далі обрати вкладку Основне → Сума → Enter.

Ще один спосіб:

Функція «Мінімум» (MIN) та «Максимум» (MAX)

Для того, щоб використати функцію «Мінімум» («Максимум») необхідно виділити комірки під числами, з яких потрібно визначити мінімальне чи максимальне значення, далі обрати вкладку Основне → Максимум (Мінімум) → Enter.

Функція «Середнє» (AVERANGE)

Для того, щоб використати функцію «Середнє» необхідно виділити комірки під числами, з яких потрібно визначити середнє значення, далі обрати вкладку Основне → Середнє  → Enter.

Статистичні функції, дати та часу

Функція «Середнє» (AVERANGE)

Для того, щоб використати функцію «Середнє» необхідно виділити комірки під числами, з яких потрібно визначити середнє значення, далі обрати вкладку Основне → Середнє  → Enter.

Функція COUNT

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

Функція COUNTIF

За допомогою функції COUNTIF, яка належить до статистичних функцій, можна порахувати кількість комірок, які відповідають певній умові (наприклад, скільки разів якесь місто з’являється в певному списку).

Функція Місяць (MONTH)

За допомогою функції MONTH можна повернути місяць дати, заданої порядковим номером. Місяць подається як ціле число в діапазоні від 1 (січень) до 12 (грудень).

Функція RANK (РАНГ)

За допомогою функції RANK можна повернути ранг числа у списку чисел. Ранг числа ‒ це його місце за величиною серед інших значень у списку. Якщо відсортувати список, то ранг числа буде дорівнювати його позиції.

Синтаксис

RANK(число;посилання;[порядок])

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

Число – обов’язковий аргумент. Число, ранг якого потрібно визначити.

Посилання – обов'язковий аргумент. Масив або посилання на список чисел. Нечислові значення в посиланні ігноруються.

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

Логічні функції

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

Логічні функції перевіряють, чи виконуються задані користувачем умови: якщо виконуються, це розцінюється як ІСТИНА, у іншому випадку ‒ ХИБНІСТЬ. Залежно від того, що дає перевірка умов ‒ істину або брехня, логічні функції відображають задане значення (числове, текстове) або виконують обчислення по заданій користувачем формулі.

Логічні функції в Microsoft Excel 2016

‒  зробити активною клітинку, в якій потрібно відобразити результат;
‒  ввести символ =;
‒  натиснути значок Вставити функцію  розташованому на верхній панелі інструментів;
‒  обрати зі списку групу функцій Логічні;
‒  обрати необхідну функцію;
‒  ввести потрібні аргументи через крапку з комою у рядку формул або в діалоговому вікні функції;
‒ натиснути клавішу Enter.

Функція IF

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

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

У функції IF всі аргументи є обов’язковими:

‒ логічний вираз – умова, яку необхідно перевірити при виконанні функцій;
‒ значення, якщо істина ‒ значення, яке повертається, якщо результат умови логічний_вираз має значення TRUE;
‒ значення, якщо хибність ‒ значення, яке повертається, якщо результат умови лог_вираз має значення FALSE.

Поширені проблема при застосуванні функції IF:

‒  0 (нуль) у комірці ‒ не вказано значення для аргументу значення_якщо_істина або значення_якщо_хибність. Щоб побачити правильне значення, додайте текст для обох аргументів або додайте до аргументу значення TRUE чи FALSE.

‒ #NAME? у комірці ‒ формулу введено неправильно.

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

При необхідності одночасної перевірки декількох умов, наприклад, щоб визначити, чи мають усі вони (AND) або одна з них (OR) значення TRUE або FALSE або якщо потрібно переконатися, що умова не відповідає критеріям (NOT), поєднують застосування даних функцій з функцією IF.

Синтаксис поєднання функцій:

‒ IF(AND()) ‒  

‒ IF(OR()) ‒ 

‒ IF(NOT()) – 

Функції AND і OR підтримують до 255 окремих умов функцій, але таку велику кількість використовувати недоцільно, тому що складні вкладені формули дуже важко створювати, перевіряти та змінювати. Функція NOT приймає лише одну умову.

Функції FALSE, TRUE

Функція FALSE в Excel повертає логічне значення FALSE, і не використовує ніяких вхідних аргументів функції.

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

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

Функція TRUE

Функція TRUE в Excel повертає логічне значення TRUE, і не використовує ніяких вхідних аргументів функції. Дана функція виступає оберненою до функції FALSE.

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

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

Функції AND, OR

Функція AND

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

Зазвичай функція AND використовується разом з іншими функцію, які виконують логічні перевірки. Наприклад, функція IF повертає одне значення, якщо логічна перевірка дає результат TRUE, та інше значення, якщо перевірка дає результат FALSE. Використання функції AND в аргументі логічного виразу функції IF, дозволяє перевіряти виконання кількох умов замість однієї. Таким чином, можна перевірити істинність від 1 до 255 логічних умов

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

Функція OR

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

Зазвичай функція OR використовується разом з іншими функцію, які виконують логічні перевірки. Використовуючи функцію OR в аргументі логічного виразу функції IF, можна перевіряти виконання кількох умов замість однієї.

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

Рис. Приклад застосування функції OR

Функції NOT, ХOR

Функція NOT

Зазвичай функція NOT використовується разом з іншими функцію, які виконують логічні перевірки. Використовуючи функцію NOT в аргументі логічного виразу функції IF, можна перевіряти виконання кількох умов замість однієї.

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

Логічне значення ‒ обов'язковий аргумент функції, будь-яке значення або вираз, які можуть мати значення TRUE або FALSE.

Рис. Діалогове вікно функції TRUE

Функція ХOR

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

Примітки:

‒  Аргументи приймати логічні значення TRUE чи FALSE або можуть бути масивами чи посиланнями, які містять логічні значення.
‒  Якщо масив або посилання містить текст чи пусті клітинки, ці значення ігноруються.
‒  Якщо вказаний діапазон не містить логічних значень, функція XOR повертає #VALUE! .
‒  Щоб переглянути, чи є в масиві значення, можна скористатися формулою масиву XOR