Формули та функції Excel: вивчайте на базових ПРИКЛАДАХ
Шарлотта Міллер
Формули та функції є складовими для роботи з числовими даними в Excel. Ця стаття знайомить вас із формулами та функціями.
Дані підручників
У цьому посібнику ми працюватимемо з наступними наборами даних.
Бюджет товарів для дому
| S / N | ITEM | Кількість | ЦІНА | SUBTOTAL | Це доступно? |
|---|---|---|---|---|---|
| 1 | Манго | 9 | 600 | ||
| 2 | Апельсини | 3 | 1200 | ||
| 3 | помідори | 1 | 2500 | ||
| 4 | Рослинне масло | 5 | 6500 | ||
| 5 | Тонізуюча вода | 13 | 3900 |
Графік будівництва будинку
| S / N | ITEM | ДАТА ПОЧАТКУ | ДАТА ЗАКІНЧЕННЯ | ТРИВАЛІСТЬ (ДНІВ) |
|---|---|---|---|---|
| 1 | Обстеження землі | 04/02/2015 | 07/02/2015 | |
| 2 | Lay Foundation | 10/02/2015 | 15/02/2015 | |
| 3 | Покрівля | 27/02/2015 | 03/03/2015 | |
| 4 | Живопис | 09/03/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.
Натисніть клавішу введення
Ви отримаєте наступний результат
Наступне анімоване зображення показує, як автоматично вибрати адресу комірки та застосувати ту саму формулу до інших рядків.
Помилок, яких слід уникати під час роботи з формулами в Excel
- Запам'ятайте правила Brackets ділення, множення, додавання та віднімання (БОДМАС). Це означає, що першими обчислюються вирази в дужках. Для арифметичних операторів спочатку обчислюється ділення, а потім множення, а потім останніми обчислюються додавання та віднімання. Використовуючи це правило, ми можемо переписати наведену вище формулу як =(A2 * D2) / 2. Це гарантує, що A2 і D2 спочатку обчислюються, а потім діляться на два.
- Формули електронної таблиці Excel зазвичай працюють із числовими даними; ви можете скористатися перевагами перевірки даних, щоб вказати тип даних, які повинні прийматися коміркою, тобто лише числа.
- Щоб переконатися, що ви працюєте з правильними адресами комірок, на які посилаються у формулах, можна натиснути F2 на клавіатурі. Це виділить адреси клітинок, які використовуються у формулі, і ви зможете перехресно перевірити, щоб переконатися, що це потрібні адреси клітинок.
- Коли ви працюєте з багатьма рядками, ви можете використовувати порядкові номери для всіх рядків і мати кількість записів у нижній частині аркуша. Ви повинні порівняти кількість серійних номерів із загальною кількістю записів, щоб переконатися, що ваші формули містять усі рядки.
Перевіряти
10 найкращих формул таблиць Excel
Що таке функція в Excel?
ФУНКЦІЯ В EXCEL це попередньо визначена формула, яка використовується для певних значень у певному порядку. Функція використовується для швидких завдань, як-от знаходження суми, кількості, середнього, максимального та мінімального значень для діапазону комірок. Наприклад, клітинка A3 нижче містить функцію SUM, яка обчислює суму діапазону A1:A2.
- SUM для підсумовування діапазону чисел
- СЕРЕДНЯ для обчислення середнього заданого діапазону чисел
- COUNT для підрахунку кількості елементів у заданому діапазоні
Важливість функцій
Функції підвищують продуктивність користувача при роботі з excel. Припустімо, ви хочете отримати загальну суму для вищезазначеного бюджету товарів для дому. Щоб зробити це простіше, ви можете скористатися формулою для отримання загальної суми. Використовуючи формулу, ви повинні посилатись на комірки від E4 до E8 одну за одною. Ви повинні використовувати наступну формулу.
= E4 + E5 + E6 + E7 + E8
За допомогою функції ви б записали наведену вище формулу як
=SUM (E4:E8)
Як ви можете бачити з наведеної вище функції, яка використовується для отримання суми діапазону комірок, набагато ефективніше використовувати функцію для отримання суми, ніж використовувати формулу, яка матиме посилання на багато комірок.
Загальні функції
Давайте розглянемо деякі з найпоширеніших функцій у формулах MS Excel. Ми почнемо зі статистичних функцій.
| S / N | ФУНКЦІЯ | КАТЕГОРІЇ | ОПИС | ВИКОРИСТАННЯ |
|---|---|---|---|---|
| 01 | SUM | Math & Trig | Додає всі значення в діапазоні клітинок | = SUM (E4: E8) |
| 02 | MIN | Статистичний | Знаходить мінімальне значення в діапазоні клітинок | =MIN(E4:E8) |
| 03 | MAX | Статистичний | Знаходить максимальне значення в діапазоні клітинок | =МАКС(E4:E8) |
| 04 | СЕРЕДНЯ | Статистичний | Обчислює середнє значення в діапазоні клітинок | = СЕРЕДНЯ (E4: E8) |
| 05 | COUNT | Статистичний | Підраховує кількість клітинок у діапазоні клітинок | = COUNT (E4: E8) |
| 06 | LEN | текст | Повертає кількість символів у тексті рядка | = 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, якщо воно не є числовим | = ISNUMBER (A3) |
| 2 | РАНД | Math & Trig | Генерує випадкове число від 0 до 1 | = RAND () |
| 3 | КРУГЛИЙ | Math & Trig | Округлює десяткове значення до вказаної кількості десяткових знаків | =ROUND(3.14455,2;XNUMX) |
| 4 | MEDIAN | Статистичний | Повертає число в середині набору заданих чисел | =МЕДІАН(3,4,5,2,5;XNUMX;XNUMX;XNUMX;XNUMX) |
| 5 | PI | Math & Trig | Повертає значення математичної функції PI(π) | =PI() |
| 6 | ПОТУЖНІСТЬ | Math & Trig |
Повертає результат числа, піднесеного до степеня. POWER( число, ступінь ) |
=ПОТУЖНІСТЬ(2,4;XNUMX) |
| 7 | MOD | Math & Trig | Повертає залишок, коли ви ділите два числа | =MOD(10,3;XNUMX) |
| 8 | ROMAN | Math & Trig | Перетворює число на римські цифри | =РОМАН(1984) |
Рядкові функції
Ці основні функції Excel використовуються для роботи з текстовими даними. У наведеній нижче таблиці показано деякі поширені рядкові функції.
| S / N | ФУНКЦІЯ | КАТЕГОРІЇ | ОПИС | ВИКОРИСТАННЯ | ЯК |
|---|---|---|---|---|---|
| 1 | ЛІВИЙ | текст | Повертає кількість указаних символів із початку (ліворуч) рядка | =LEFT(“GURU99”;4) | Ліворуч 4 символи «GURU99» |
| 2 | ПРАВО | текст | Повертає кількість указаних символів із кінця (правої частини) рядка | =ПРАВО(“ГУРУ99”;2) | Праворуч 2 символи «GURU99» |
| 3 | MID | текст |
Отримує кількість символів із середини рядка з указаної початкової позиції та довжини. =MID (текст, start_num, num_chars) |
=MID(“GURU99”;2,3;XNUMX) | Отримання символів від 2 до 5 |
| 4 | ІСТЕКСТ | Інформація | Повертає True, якщо наданим параметром є Text | =ISTEXT(значення) | значення – значення для перевірки. |
| 5 | ЗНАЙТИ | текст |
Повертає початкову позицію текстового рядка в іншому текстовому рядку. Ця функція чутлива до регістру. =ЗНАЙТИ(знайти_текст, у_тексті, [початковий_номер]) |
=ЗНАЙТИ(“оо”,”Покрівля”,1) | Знайдіть oo в «Покрівлі», результат 2 |
| 6 | ЗАМІНА | текст |
Замінює частину рядка іншим указаним рядком. =ЗАМІНИТИ (старий_текст, початковий номер, кількість_символів, новий_текст) |
=ЗАМІНИ(“Покрівля”,2,2;XNUMX;”xx”) | Замініть «oo» на «xx» |
Функції дати та часу
Ці функції використовуються для маніпулювання значеннями дати. У наведеній нижче таблиці показано деякі поширені функції дати
| S / N | ФУНКЦІЯ | КАТЕГОРІЇ | ОПИС | ВИКОРИСТАННЯ |
|---|---|---|---|---|
| 1 | ДАТА ПРОВЕДЕННЯ | Дата, час | Повертає число, яке представляє дату в коді Excel | = ДАТА (2015,2,4) |
| 2 | ДНІВ | Дата, час | Знайдіть кількість днів між двома датами | =ДНІ(D6;C6) |
| 3 | МІСЯЦЬ | Дата, час | Повертає місяць зі значення дати | =МІСЯЦЬ("4") |
| 4 | ПУТІВКИ | Дата, час | Повертає хвилини зі значення часу | =МІНУТА(“12:31”) |
| 5 | РІК | Дата, час | Повертає рік зі значення дати | =YEAR("04") |
Функція 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 =
=AVERAGE(E4:E8)
- Функція COUNT =
=COUNT(E4:E8)
- Функція DAYS =
=DAYS(D4,C4)
- Функція VLOOKUP =
=VLOOKUP (C12, A4:B8, 2, FALSE)
- Функція ДАТА =
=DATE(2020,2,4)
