Формули Excel & Функції: Навчіться на базових ПРИКЛАДАХ

Формули та функції - це основи роботи з числовими даними в Excel. Ця стаття знайомить вас із формулами та функціями.

У цій статті ми розглянемо наступні теми.

• Що таке формули в Excel?
• Помилок, яких слід уникати при роботі з формулами в Excel
• Що таке функція в Excel?
• Важливість функцій
• Загальні функції
• Числові функції
• Функції рядка
• Функції дати і часу
• V Функція пошуку

Дані навчальних посібників

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

Бюджет товарів для дому

S / N	ПУНКТ	КІЛЬКІСТЬ	ЦІНА	ПІДЗЕМЛЯ	Це доступно?
1	Манго	9	600
2	Апельсини	3	1200
3	Помідори	1	2500
4	Рослинне масло	5	6500
5	Тонізуюча вода	13	3900

Графік проекту будівництва будинків

S / N	ПУНКТ	ДАТА ПОЧАТКУ	КІНЦЕВА ДАТА	ТРИВАЛІСТЬ (ДНІ)
1	Оглядова земля	02.04.2015	02.07.2015
2	Основа мирян	02.10.2015	15.02.2015
3	Покрівельні роботи	27.02.2015	03.03.2015
4	Живопис	03.09.2015	21.03.2015

Що таке формули в Excel?

ФОРМУЛИ В EXCEL - це вираз, який оперує значеннями в діапазоні адрес комірок та операторів. Наприклад, = A1 + A2 + A3, яке знаходить суму діапазону значень від комірки A1 до комірки A3. Приклад формули, складеної з дискретних значень, наприклад = 6 * 3.

=A2 * D2 / 2

ТУТ,

• говорить Excel, що це формула, і вона повинна її оцінити.
• "A2" * D2" робить посилання на адреси комірок A2 і D2, а потім множить значення, знайдені в цих адресах комірок.
• "/" є діленням арифметичного оператора
• "2" є дискретним значенням

Формули практична вправа

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

• Створіть нову книгу в Excel
• Введіть дані, наведені в бюджеті товарів для дому вище.
• Ваш аркуш повинен виглядати наступним чином.

Зараз ми напишемо формулу, яка обчислює проміжні підсумки

Встановіть фокус на комірку E4

Введіть наступну формулу.

=C4*D4

ТУТ,

• "C4*D4" використовує множення арифметичного оператора (*) для множення значення адреси комірки C4 і D4.

Натисніть клавішу Enter

Ви отримаєте наступний результат

Наступне анімоване зображення показує, як автоматично вибрати адресу комірки та застосувати ту саму формулу до інших рядків.

Помилок, яких слід уникати при роботі з формулами в Excel

1. Запам’ятайте правила дужок ділення , множення, додавання та віднімання (BODMAS). Це означає, що вирази в дужках обчислюються спочатку. Для арифметичних операторів спочатку обчислюється ділення, а потім множення, потім додавання та віднімання є останнім, що обчислюється. Використовуючи це правило, ми можемо переписати наведену вище формулу як = (A2 * D2) / 2. Це забезпечить, що A2 і D2 спочатку обчислюються, а потім діляться на два.
2. Формули електронних таблиць Excel зазвичай працюють з числовими даними; Ви можете скористатися перевіркою даних, щоб вказати тип даних, які має приймати клітинка, тобто лише цифри.
3. Щоб переконатися, що ви працюєте з правильними адресами комірок, на які посилаються формули, ви можете натиснути клавішу F2 на клавіатурі. Це виділить адреси комірок, які використовуються у формулі, і ви можете перехресно перевірити, щоб переконатися, що це бажані адреси комірок.
4. Коли ви працюєте з багатьма рядками, ви можете використовувати серійні номери для всіх рядків і мати кількість записів внизу аркуша. Вам слід порівняти кількість серійних номерів із загальною кількістю записів, щоб переконатися, що ваші формули включали всі рядки.

Ознайомтесь з 10 найкращими формулами електронних таблиць Excel

Що таке функція в Excel?

FUNCTION IN EXCEL - це заздалегідь визначена формула, яка використовується для конкретних значень у певному порядку. Функція використовується для швидких завдань, таких як пошук суми, підрахунку, середнього, максимального значення та мінімальних значень для діапазону комірок. Наприклад, клітинка A3 нижче містить функцію SUM, яка обчислює суму діапазону A1: A2.

• СУМА для підсумовування діапазону чисел
• СЕРЕДНЯ для обчислення середнього значення даного діапазону чисел
• COUNT для підрахунку кількості елементів у заданому діапазоні

Важливість функцій

Функції підвищують продуктивність користувачів при роботі з Excel . Скажімо, ви хотіли б отримати загальну суму для вищезазначеного бюджету товарів для дому. Щоб спростити, ви можете скористатися формулою для отримання загальної суми. Використовуючи формулу, вам доведеться посилати комірки E4 на E8 одну за одною. Вам доведеться використовувати наступну формулу.

= E4 + E5 + E6 + E7 + E8

За допомогою функції ви можете записати наведену вище формулу як

=SUM (E4:E8)

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

Загальні функції

Давайте розглянемо деякі найбільш часто використовувані функції у формулах ms excel. Почнемо із статистичних функцій.

S / N	ФУНКЦІЯ	КАТЕГОРІЇ	ОПИС	ВИКОРИСТАННЯ
01	СУММА	Math & Trig	Додає всі значення в діапазон комірок	= SUM (E4: E8)
02	ХВ	Статистичний	Знаходить мінімальне значення в діапазоні комірок	= МІН. (E4: E8)
03	МАКС	Статистичний	Знаходить максимальне значення в діапазоні комірок	= МАКС (E4: E8)
04	СЕРЕДНІЙ	Статистичний	Обчислює середнє значення в діапазоні комірок	= СЕРЕДНЯ (E4: E8)
05	РАХУВАТИ	Статистичний	Підраховує кількість клітин у діапазоні комірок	= COUNT (E4: E8)
06	ЛІН	Текст	Повертає кількість символів у текстовому рядку	= LEN (B7)
07	SUMIF	Math & Trig	Додає всі значення в діапазон комірок, які відповідають заданим критеріям. = SUMIF (діапазон, критерії, [діапазон_суми])	= SUMIF (D4: D8, "> = 1000", C4: C8)
08	AVERAGEIF	Статистичний	Обчислює середнє значення в діапазоні комірок, які відповідають зазначеним критеріям. = AVERAGEIF (діапазон, критерії, [середній_діапазон])	= AVERAGEIF (F4: F8, "Так", E4: E8)
09	ДНІ	Дата, час	Повертає кількість днів між двома датами	= ДНІ (D4, C4)
10	ЗАРАЗ	Дата, час	Повертає поточну системну дату та час	= ЗАРАЗ ()

Числові функції

Як випливає з назви, ці функції працюють із числовими даними. У наступній таблиці наведено деякі загальні числові функції.

S / N	ФУНКЦІЯ	КАТЕГОРІЇ	ОПИС	ВИКОРИСТАННЯ
1	ІЗНОМ	Інформація	Повертає True, якщо надане значення є числовим, і False, якщо воно не є числовим	= ІЗНОМ (A3)
2	RAND	Math & Trig	Генерує випадкове число від 0 до 1	= RAND ()
3	КРУГЛИЙ	Math & Trig	Округлює десяткове значення до вказаної кількості знаків після коми	= КРУГЛИЙ (3.14455,2)
4	МЕДІАН	Статистичний	Повертає число в середині набору заданих чисел	= МЕДІАН (3,4,5,2,5)
5	PI	Math & Trig	Повертає значення математичної функції PI (π)	= PI ()
6	ПОТУЖНІСТЬ	Math & Trig	Повертає результат числа, піднятого до степеня. POWER (кількість, потужність)	= ПОТУЖНІСТЬ (2,4)
7	MOD	Math & Trig	Повертає залишок, коли ділите два числа	= MOD (10,3)
8	РИМАН	Math & Trig	Перетворює число в римські цифри	= РИМСЬКИЙ (1984)

Функції рядка

Ці основні функції Excel використовуються для обробки текстових даних. У наступній таблиці наведено деякі загальні функції рядків.

S / N	ФУНКЦІЯ	КАТЕГОРІЇ	ОПИС	ВИКОРИСТАННЯ	КОМЕНТАР
1	ВЛІВО	Текст	Повертає кількість вказаних символів із початку (ліворуч) рядка	= ВЛІВО ("GURU99", 4)	Залишилося 4 символи "GURU99"
2	ПРАВО	Текст	Повертає кількість вказаних символів з кінця (праворуч) рядка	= ВПРАВО ("GURU99", 2)	Праворуч 2 символи "GURU99"
3	СЕРЕДНЯ	Текст	Отримує кількість символів із середини рядка із зазначеної початкової позиції та довжини. = MID (текст, номер_початку, число_знаків)	= MID ("GURU99", 2,3)	Отримання символів від 2 до 5
4	ІСТЕКСТ	Інформація	Повертає True, якщо наданим параметром є Text	= ISTEXT (значення)	value - значення для перевірки.
5	ЗНАЙТИ	Текст	Повертає початкове положення текстового рядка в іншому текстовому рядку. Ця функція враховує регістр. = ЗНАЙТИ (знайти_текст, всередині_тексту, [номер_початку])	= ЗНАЙТИ ("oo", "Покрівля", 1)	Знайдіть oo у розділі "Покрівля", результат 2
6	ЗАМІНИТЕ	Текст	Замінює частину рядка іншим вказаним рядком. = ЗАМІНИ (старий_текст, номер_початку, числові символи, новий_текст)	= ЗАМІНИ ("Покрівля", 2,2, "хх")	Замініть "oo" на "xx"

Функції дати та часу

Ці функції використовуються для маніпулювання даними. У наступній таблиці наведено деякі загальні функції дати

S / N	ФУНКЦІЯ	КАТЕГОРІЇ	ОПИС	ВИКОРИСТАННЯ
1	ДАТА	Дата, час	Повертає число, яке представляє дату в коді Excel	= ДАТА (2015,2,4)
2	ДНІ	Дата, час	Знайдіть кількість днів між двома датами	= ДНІ (D6, C6)
3	МІСЯЦ	Дата, час	Повертає місяць із значення дати	= МІСЯЦЬ ("02.04.2015")
4	ХВИЛИНА	Дата, час	Повертає хвилини за значенням часу	= ХВИЛИНА ("12:31")
5	РІК	Дата, час	Повертає рік із значення дати	= РІК ("02.02.2015")

Функція VLOOKUP

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

=VLOOKUP (C12, A4:B8, 2, FALSE)

ТУТ,

• "=VLOOKUP" викликає функцію вертикального пошуку
• "C12" визначає значення, яке потрібно шукати в самому лівому стовпці
• "A4:B8" вказує масив таблиці з даними
• "2" вказує номер стовпця зі значенням рядка, що повертається функцією VLOOKUP
• "FALSE," повідомляє функції VLOOKUP, що ми шукаємо точну відповідність наданого значення пошуку

Анімоване зображення нижче показує це в дії

Завантажте вищезгаданий код Excel

Резюме

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

Ось список важливих формул та функцій Excel

• Функція SUM = =SUM(E4:E8)

• Функція MIN = =MIN(E4:E8)

• Функція MAX = =MAX(E4:E8)

• СЕРЕДНЯ функція = =AVERAGE(E4:E8)

• Функція COUNT = =COUNT(E4:E8)

• Функція DAYS = =DAYS(D4,C4)

• Функція VLOOKUP = =VLOOKUP (C12, A4:B8, 2, FALSE)

• Функція DATE = =DATE(2020,2,4)