Якщо ви використовуєте Excel/Google Sheets у своїй повсякденній роботі, є кілька дуже корисних формул, до яких ви будете повертатися, якщо вивчите їх один раз.
Існує, мабуть, більше сотні різних формул електронних таблиць, які працюють для різних типів розрахунків.
Моя мета в цьому блозі - розповісти про найпростіші з них, ті, що формують основу всіх цих складних обчислень, які ви зможете вивчити пізніше.
Спочатку ми розглянемо деякі базові арифметичні команди, а потім я познайомлю вас з кількома способами, за допомогою яких ви зможете робити більш складні аналізи.
До них належать оператори IF, VLOOKUP, INDEX, MATCH тощо.
І це простіше, ніж ви думаєте!
Основні висновки
- Excel та Google Таблиці мають багато вбудованих команд формул, які корисні для виконання обчислень.
- SUM, AVERAGE, COUNT, VLOOKUP, INDEX і MATCH - ось деякі формули, які можуть допомогти в повсякденних обчисленнях. Ви також можете погратися з ними, вводячи умовні логіки.
- У написанні формули не повинно бути помилок, інакше ви не отримаєте потрібних результатів. Зокрема, переконайтеся, що ви уникаєте циклічних посилань і не допускаєте, щоб Excel потрапляв у нескінченний цикл обчислень.
Що таке формула електронної таблиці
Команди, за допомогою яких можна обчислювати дані, називаються формулами електронних таблиць.
І Microsoft Excel, і Google Docs завантажені такими командами, і саме тому вони є основними інструментами на будь-якому робочому місці.
Знак (=) стоїть перед будь-якою формулою, написаною в Excel. Це вказівка для Excel розглядати наступний текст як формулу для виконання необхідних обчислень, а не як звичайний текстовий запис.
Більше ніколи не турбуйтеся про те, що ШІ виявить ваші тексти. Undetectable AI Може допомогти тобі:
- Зробіть так, щоб ваше письмо з допомогою штучного інтелекту з'являлося на екрані схожий на людину.
- Обхід всі основні інструменти виявлення ШІ лише одним кліком.
- Використання ШІ безпечно і впевнено у школі та на роботі.
Окрім використання формул для дат в електронних таблицях Google Таблиці або Excel, ось кілька причин, чому Excel і Google Таблиці мають сотні команд для формул:
- Швидко автоматизує розрахунки
Усім нам потрібно робити базові розрахунки, такі як арифметика або обчислення середніх значень, незалежно від характеру нашої роботи.
Якщо ваша робота пов'язана з великими обсягами даних, вам знадобляться більш об'ємні обчислення з набагато вищою частотою.
Дуже зручно, якщо ви можете зробити ці розрахунки в електронній таблиці, де зберігаються ваші дані, замість того, щоб імпортувати їх на калькуляторі.
Ви також можете задати формулу один раз, і вона буде продовжувати працювати для стількох рядків або даних, які ви введете, скільки потрібно.
- Зменшує помилки ручного введення
Експорт даних до програми-калькулятора не лише забирає багато часу, але й може призвести до помилок. Один невірний запис - і весь ваш майбутній розрахунок буде недостовірним.
Навіть після багаторазового вичитування ви не можете бути впевненими, що ви ввели всі значення точно.
Запуск формули в електронній таблиці зменшує ймовірність помилок.
- Логічно пов'язує дані
За допомогою обчислень ви можете набагато логічніше осмислити ваші набори даних. Сортуйте дані, фільтруйте їх, проводьте аналіз і краще розумійте, чому відповідають всі ці числа в кожній комірці вашої електронної таблиці.
Він чудово підходить для:
- Бюджетування
- Ведення особистого або ділового фінансового обліку
- Викладачі, які бажають вести облік успішності своїх студентів на іспитах
- Дані досліджень
- Розрахунок прибутковості інвестицій
Тепер, якщо вам потрібно знати формули електронних таблиць Google або вам потрібен список формул електронних таблиць Excel, ви можете знайти їх у наступному розділі.
Основні формули, необхідні кожному користувачеві
І Excel, і Google Таблиці мають сотні формул, які виконують різні обчислення. Якщо ви захочете вивчити їх усі, вам знадобиться кілька місяців. Однак нижче я описав кілька дуже зручних формул.
- SUM для підсумків
Команда SUM() використовується для підсумовування всіх виділених клітинок. Звичайно, щоб вона спрацювала, у клітинці повинно бути числове значення. Формула не працюватиме для нечислових клітинок.
У дужці потрібно вказати першу та останню клітинки сегмента електронної таблиці, який потрібно додати, через двокрапку.
Отже, якщо я хочу додати вміст рядків з 3 по 7 у першому стовпчику, я напишу формулу так: SUM(A3:A7). Ця формула також працює для кількох рядків.
Крім того, ця формула залишається однаковою як для Google Sheets, так і для Excel.
Подивіться на цей приклад:
Крім найпростішої функції SUM, ви також можете виконувати наступні типи додавання:
- SUMIF: Додати числа в діапазоні, які задовольняють одній умові
- СУМИ: додавання чисел у діапазоні, які відповідають декільком умовам
- СУММАРНИЙ ПРОДУКТ: Перемножте відповідні значення в масивах, а потім додайте результати
- SUMSQ: Додати квадрати чисел у діапазоні
- SUMX2MY2: Додати результат (x² - y²) для відповідних значень у двох діапазонах
- SUMX2PY2: Додати результат (x² + y²) для відповідних значень у двох діапазонах
- SUMXMY2: Додати квадрати різниць (x - y)² для двох діапазонів
- DSUM: додавання значень у стовпці, які відповідають критеріям, визначеним у таблиці бази даних
- IMSUM: Додати комплексні числа.
- СЕРІЄС: Обчислити суму степеневого ряду за заданими коефіцієнтами.
- СЕРЕДНІЙ показник для інсайтів
Формула СРЗНАЧ() використовується для обчислення середнього значення заданого набору клітинок у вашій електронній таблиці.
Знову ж таки, робота дуже схожа. Ви вводите перший і останній номер клітинки в дужки через двокрапку, наприклад, AVERAGE(E2:E11), і отримуєте середнє значення для вибраних клітинок.
Знову ж таки, формула для середнього значення в Excel така ж сама, як і в Google Таблицях.
Невеликі варіації у формулі СЕРЕДНЄ можуть допомогти вам в умовних розрахунках, таких як:
- СЕРЕДНЄ: Обчислення середнього арифметичного значення чисел у діапазоні
- AVERAGEA: Використовуйте її, коли вам потрібно отримати середнє значення, включаючи числа, текст і логічні значення
- AVERAGEIF: Ця команда призначена для обчислення середнього значення чисел, які відповідають одній умові
- AVERAGEIFS: обчислює середнє значення чисел, які відповідають декільком умовам
- DAVERAGE: Обчислює середнє значення в стовпці бази даних, що відповідає заданим критеріям
- COUNT для відстеження товарів
Ця команда корисна для підрахунку кількості клітинок у вашій електронній таблиці, що містять числові дані.
Немає сенсу використовувати його, якщо ваші дані обмежені лише кількома рядками, але якщо у вас є сотні рядків даних, і ви хочете знати, скільки з них є числовими значеннями, то це саме те, що вам потрібно.
Вона також записується аналогічно, тобто починається зі знаку =, потім згадується команда COUNT, відкривається дужка, згадується перша і остання комірка, розділені двокрапкою, і дужка закривається.
Формула однакова як в Google Sheets, так і в Excel.
Наприклад, =COUNT(E1:E100)
Як і формули SUM і AVERAGE, COUNT також може використовуватися кількома різними способами, а саме,
- COUNTA: Підрахувати клітинки, які не є порожніми (числа, текст або значення).
- COUNTBLANK: рахує лише порожні клітинки в діапазоні
- COUNTIF: Підраховує клітини, які відповідають одній умові
- COUNTIF: Використовуйте його для підрахунку комірок, які відповідають декільком умовам
- DCOUNT: Підраховує числові значення у стовпчику бази даних, які відповідають заданим критеріям
- DCOUNTA: Використовується для підрахунку непорожніх значень у стовпці бази даних, які відповідають заданим критеріям
Розширені формули для аналізу даних
Для більшості користувачів формул, про які я говорив раніше, буде більш ніж достатньо. Деякі формули, однак, потрібні для більш складних розрахунків, і вони стануть у нагоді, якщо ваша робота пов'язана з аналізом даних.
- VLOOKUP для пошуку
Формула VLOOKUP використовується для пошуку значення в таблиці і повернення пов'язаної інформації з того ж рядка.
Простіше кажучи, коли ви вводите VLOOKUP, ви просите електронну таблицю знайти значення в першому стовпчику таблиці, а потім виводите значення з іншого стовпчика в цьому рядку.
У нашому прикладі, якщо стовпець A містить ідентифікатори товарів, а стовпець C - ціну за одиницю, ми можемо використати VLOOKUP для пошуку певного ідентифікатора товару і отримати його ціну зі стовпця C.
Він буде записаний як VLOOKUP(“P-1001”, A:C, 3, FALSE)
- Оператори IF для логіки
Функція ЕСЛИ використовується для прийняття логічних рішень в електронній таблиці. Вона просто вказує аркушу, що робити, коли умова істинна, і що робити, коли вона хибна.
Щоб написати оператор IF, ви починаєте зі знака =, включаєте команду IF, розкриваєте дужки, а потім описуєте свою умову.
Після цього через кому вкажіть, що ви хочете, щоб аркуш зробив, якщо умова буде виконана. Після цього через кому вкажіть, що ви хочете, щоб відбулося, якщо команда не буде виконана.
Наприклад, якщо ми уявимо собі аркуш з результатами студентів і хочемо вказати умову, що бал 50 або вище - це прохідний бал, а нижче 50 - двійка, ми можемо використати логіку ЯКЩО, або ЯКЩО.
Ось як це можна записати: =IF(A1>=50, “Pass”, “Fail”)
Аркуш просканує значення в комірці, і якщо воно більше 50, він покаже, що учень пройшов тест. В іншому випадку буде вказано, що він не пройшов.
- INDEX & MATCH для пошуку
ІНДЕКС і СПІВПАДАННЯ використовуються разом для пошуку інформації в таблиці більш гнучко, ніж формула ПОИСК.
Функція МАТЧ відповідає за пошук позиції значення в рядку або стовпчику. Використовуйте функцію МАТЧ для пошуку значення у вашій таблиці, і вона не поверне саме значення. Вона поверне число, яке вказує на те, де знаходиться це значення.
Наприклад, якщо ідентифікатор товару є п'ятим елементом у списку, MATCH поверне 5.
Функція INDEX використовує цей номер позиції для повернення значення з певного місця в таблиці або стовпчику. Отже, вона просто повертає будь-яке значення, що існує у вказаному вами рядку.
Поради щодо уникнення поширених помилок у формулах
Як ви вже могли бачити, і Excel, і Google Таблиці вимагають, щоб ви вводили формули з точністю до 100%.
Будь-які помилки в написанні формули не дадуть вам результатів, ви просто отримаєте повідомлення про помилку з написом #N/A.
Ось кілька порад, які допоможуть вам уникнути цього повідомлення про помилку.
- Уважно перевірте дужки
Формула повинна бути написана в точному форматі, описаному вище. Якщо ви випадково залишили дужки відкритими або просто описали діапазон клітинок без дужок, ваші дані не будуть обчислені.
Завжди слідкуйте за тим, щоб відкриваючі та закриваючі дужки були на своїх місцях. Також переконайтеся, що є двокрапка, щоб відрізнити першу та останню клітинки вашого діапазону.
- Типи даних для перегляду
Формули електронних таблиць найкраще працюють лише з певними типами даних. Наприклад, ви не можете розраховувати середнє значення для даних, які не піддаються кількісній оцінці, тобто для описових форм даних.
Щоразу, коли вставляєте формули, перевіряйте дані клітинок двічі, щоб не вводити невірний діапазон клітинок.
- Уникайте циклічних посилань
Циклічне посилання означає, що ваша електронна таблиця повертається до власної комірки, де воно описане. Це створює нескінченний цикл, в якому ви не зможете отримати відповідь, а натомість отримаєте лише повідомлення про помилку.
Наприклад, якщо ви введете SUM(A1:A10) в комірку A10, ви створите круговий цикл, в якому комірка буде постійно додавати будь-яке значення, що підсумовується в комірці A10, і цей процес ніколи не закінчиться.
Формулі потрібно знати власний результат, щоб знайти значення клітинки A10.
Завжди переконайтеся, що клітинка, в яку ви ввели формулу в електронній таблиці, не входить до діапазону клітинок з формулами.
Поєднання формул для кращої ефективності
Поєднання формул в електронних таблицях дозволяє вирішувати складні завдання більш ефективно. Використовуючи прості формули, про які я говорив раніше, ви будете обчислювати щось крок за кроком у багатьох клітинках.
У комбінованій формулі можна вкласти кілька обчислень так, щоб одна формула безпосередньо входила в іншу.
Наприклад, ви можете комбінувати IF з SUM, щоб скласти значення, які відповідають лише певним умовам.
Ви також можете комбінувати COUNT або AVERAGE з умовами для більш точного аналізу даних.
Тим не менш, об'єднання формул спочатку дається нелегко. Існує реальна крива навчання, і якщо ви будете переходити до складних вкладених формул, не використовуючи прості багато разів, ви врешті-решт заплутаєтесь у них.
Тому завжди тренуйтеся спочатку з простими формулами. Як тільки ви відчуєте себе комфортно, поступово нарощуйте об'єднання двох функцій, потім трьох і, зрештою, більш просунутих налаштувань.
Використання формул для вирішення реальних завдань
Як тільки ви освоїте ці формули, ви почнете помічати, як часто вони застосовуються до того, що ви вже робите.
Наприклад:
- Власник малого бізнесу може скористатися формулою SUMIF відстежувати витрати за категоріями
- Або вчитель може поєднувати ЯКЩО з COUNTIF функції для позначення студентів, які відстають у виконанні завдань
- Те саме стосується фрілансерів, які керують рахунками клієнтів. Вони можуть використовувати простий ПЕРЕГЛЯД щоб витягти тарифи клієнтів з основного списку, не прокручуючи рядки вручну.
Справа в тому, що знати, яка формула підходить для вашої конкретної ситуації, - це майже половина успіху. І саме в цьому випадку Undetectable's Чат-бот зі штучним інтелектом може бути твоїм провідником.
Ви можете описати те, що ви намагаєтеся зробити, простою мовою, і він запропонує правильну формулу для вашого завдання.
Тепер у вас є колега, який розуміється на електронних таблицях, з яким ви можете зв'язатися, коли застрягнете, і це не обмежується лише запитами до Excel або Google таблиць.
Дізнайтеся, як наш ШІ-детектор і гуманізатор можуть покращити ваш контент, нижче!
Заключні думки
Сподіваюся, ця стаття допомогла вам дізнатися дещо про формули електронних таблиць.
Чим більше ви ними користуєтесь, тим краще вони стають, і щоразу, коли ви не знаєте, який рядок формули використовувати, просто зверніться до Undetectable AI. Чат зі штучним інтелектом, і це допоможе вам вирішити, яка команда найкраще підійде для потрібного вам обчислення.
Ви також можете перевірити формулу ще раз, запустивши її в чаті AI, щоб переконатися, що ви не робите помилок.
Спробуй ШІ, який неможливо виявити сьогодні!