Microsoft Excel (часто згадується як Excel) — це потужне програмне забезпечення для роботи з електронними таблицями, призначене для зберігання великих наборів даних. Крім того, це дозволяє користувачам упорядковувати та аналізувати дані та навіть виконувати базові та складні обчислення на основі записаних даних. Основні завдання аналізу даних Excel включають в основному обчислення або знаходження середнього (або середнього арифметичного), моди та медіани.
У цьому посібнику ми обговорюємо різні покрокові методи або рішення щодо того, як обчислити середнє значення в Excel. Перш ніж обговорювати методи, давайте коротко розберемо концепцію середнього значення та її переваги.
Що таке середнє значення в Excel?
У Excel середнє арифметичне — це не що інше, як середнє число, яке зазвичай обчислюється шляхом додавання всіх чисел заданого набору даних, а потім ділення суми на загальну кількість значень (або кількість) даних елементів.
Хоча в реальному житті легко обчислити середнє арифметичне для невеликих наборів даних, працювати з великими наборами даних стає важко. Ось тут і стане в нагоді потужна програма для роботи з електронними таблицями, наприклад Excel. Він може легко обчислити середнє від сотень до тисяч чисел. Крім того, він менш схильний до помилок.
Існує багато важливих застосувань середнього значення в аналізі даних, наприклад:
- Для порівняння історичних даних
- Щоб виділити KPI (ключові показники ефективності) і котирування
- Щоб знайти середину для заданих точок даних
- Визначати управління проектами та бізнес-плани
Основний сценарій, коли нам може знадобитися обчислити середнє значення в Excel, це коли нам потрібно дізнатися середні продажі товару за цілий місяць, квартал, рік тощо.
Як обчислити середнє значення в Excel?
На жаль, не існує певної функції MEAN для обчислення або визначення середнього арифметичного для даних. Однак є пара ефективних способів. Зазвичай ми можемо застосувати математичну концепцію додавання значень і подальшого ділення їх суми на кількість значень у клітинках Excel. Крім того, існують такі функції, як AVERAGE, AVERAGEA, AVERAGEIF і AVERAGEIFS, які також допомагають нам обчислити середнє арифметичне в різних випадках використання.
Щоб отримати краще уявлення про те, як ці концепції працюють для обчислення середнього арифметичного, давайте обговоримо кожну окремо на прикладі:
Обчислення середнього в Excel: загальний метод
Ми можемо застосувати функції SUM і COUNT на аркуші Excel, щоб використати підхід додавання значень і ділення їх на кількість включених значень.
Наприклад, припустимо, що ми дали числа від 1 до 5 і повинні обчислити середнє арифметичне. Математично ми спочатку складаємо всі ці числа, тобто (1+2+3+4+5). Потім ми ділимо їх SUM на 5, оскільки це загальна кількість даних чисел.
Таким чином, вся формула для обчислення середнього стає такою:
(1+2+3+4+5)/ 5
Це повертає середнє арифметичне 3.
В Excel ми можемо використовувати наведену вище математичну формулу, поєднавши її з функціями SUM і COUNT таким чином:
=SUM(1,2,3,4,5)/COUNT(1,2,3,4,5)
Тут функція SUM додає задані значення, а функція COUNT визначає кількість заданих значень.
Тепер, якщо ми запишемо ці п’ять чисел у діапазон A1:A5, наведена нижче формула в клітинці A6 обчислить середнє арифметичне:
=СУММ(A1:A5)/КІЛЬКІСТЬ(A1:A5)
Результат (або середнє значення), отриманий на зображенні вище, такий самий, як і той, який ми розрахували за математичною формулою, тобто 3.
Обчислення середнього в Excel: функція AVERAGE
Оскільки середнє значення є середнім, функція Excel AVERAGE є найефективнішим способом обчислення середнього арифметичного. Це одна з найбільш часто використовуваних статистичних функцій для обчислення середнього значення для певного діапазону, якщо немає спеціальних умов або обмежень. Це вбудована функція Excel.
Синтаксис
Синтаксис функції AVERAGE Excel такий:
=СЕРЕДНЄ(число1, [число2], ...)
Число1 є обов’язковим аргументом, тобто один аргумент є обов’язковим у функції AVERAGE. Однак ми можемо надати кілька аргументів у різних формах, таких як числа, клітинки, діапазони, масиви, константи тощо. Під час обчислення середнього арифметичного ми повинні надати принаймні два аргументи у функції AVERAGE.
Кроки для обчислення середнього за допомогою функції AVERAGE
Давайте тепер розберемося з роботою функції AVERAGE і обчислимо середнє арифметичне для групи чисел на аркуші Excel, використовуючи зразки даних.
Припустімо, ми перераховуємо деякі фрукти на аркуші Excel із відповідними цінами. Назви фруктів записуються в колонці A (A2:A9), а їх ціни – у наступній колонці B (B2:B9). Нам потрібно знайти або обчислити середню ціну (або середнє арифметичне для цін) у клітинці B10.
Нам потрібно виконати наведені нижче кроки, щоб обчислити середнє арифметичне в нашому прикладі аркуша:
виконати оболонку сценарію
- По-перше, ми повинні вибрати або виділити клітинку для запису результуючого середнього арифметичного, тобто клітинку B10.
- Далі нам потрібно вставити назву функції, яка починається зі знака рівності, і надати необхідні аргументи у функції. У нашому випадку ми вводимо знак рівності (=) і функцію AVERAGE. Ми також можемо вибрати функцію зі списку функцій, запропонованих Excel.
- Після введення назви функції ми повинні ввести початкову дужку та надати наш ефективний діапазон наборів даних. Оскільки у нас є дані в суміжних клітинках або діапазоні (від B2 до B9), ми можемо передати весь діапазон у функцію таким чином:
- Нарешті, ми повинні ввести круглу дужку та натиснути клавішу Введіть на клавіатурі, щоб отримати ефективний результат або середнє арифметичне для заданого набору даних. На зображенні нижче показано середнє арифметичне для цін у клітинках від B2 до B9:
На наведеному вище зображенні ми використали суміжні клітинки та надали весь діапазон наборів даних за один раз. Однак ми також можемо обчислити середнє значення для несуміжних комірок у нашому аркуші Excel, надавши комірки окремо.
Припустимо, нам потрібно обчислити середнє арифметичне цін лише на три фрукти: Яблуко, манго та виноград. У цьому випадку ми можемо передати кожну клітинку окремо у функції таким чином:
=СЕРЕДНЄ(B2;B3;B6)
Тут B2, B3 і B6 — окремі комірки з цінами на яблуко, манго та виноград.
Хоча функцію AVERAGE можна використовувати з порожніми клітинками, текстом, нулями, логічними значеннями та навіть змішаними аргументами, вона ігнорує порожні клітинки, текст і логічні значення під час обчислення. Проте комірки з нульовим значенням включено до функції AVERAGE.
Обчислення середнього в Excel: функція AVERAGEA
Функція AVERAGEA є дещо вдосконаленою версією функції AVERAGE. Функція AVERAGEA також допомагає нам обчислити середнє арифметичне для заданих наборів даних і працює в усіх версіях Excel. Однією з переваг функції AVERAGEA є те, що вона може включати текстові значення та логічні типи даних під час обчислення середнього значення в Excel. Текстові значення оцінюються як нулі, тоді як логічні значення TRUE і FALSE оцінюються як 1 і 0 відповідно.
Синтаксис
Синтаксис функції AVERAGEA в Excel такий:
=AVERAGEA(значення1; [значення2]; ...)
Як і для функції AVERAGE, перший аргумент у функції AVERAGEA є обов’язковим. Однак ми можемо використовувати кілька аргументів за потреби.
Синтаксис функцій AVERAGE і AVERAGEA майже схожий; однак їх робоча концепція дещо відрізняється. Функція AVERAGEA може обчислювати лише середнє арифметичне для числових значень, тоді як функція AVERAGEA може обчислювати те саме для будь-якого типу даних, включаючи текст і логічні значення.
Кроки для обчислення середнього за допомогою функції AVERAGEA
Давайте повторимо попередній приклад наборів даних, де ми маємо назви фруктів у стовпці A (A2:A9) і їхні ціни в стовпці B (B2:B9). Однак цього разу у нас немає ціни на виноград (записано як «Недоступно» у відповідній клітинці B6), як показано нижче:
Ми можемо виконати наведені нижче дії та обчислити середнє значення за допомогою функції AVERAGEA:
- Як і в попередньому методі, ми спочатку вибираємо результуючу клітинку B10, щоб записати середнє значення. Далі ми вводимо знак рівності (=), вводимо або вибираємо нашу функцію AVERAGEA та передаємо весь діапазон даних функції, укладеній у дужки.
- Нарешті, ми повинні натиснути Введіть ключ для отримання ефективного результату (або середнього).
Зображення вище повертає середнє значення 57,125 за допомогою функції AVERAGEA.
Якщо ми використовуємо в цьому прикладі функцію AVERAGE і обчислюємо середнє арифметичне, ми бачимо величезну різницю між результатами, отриманими обома функціями. Середнє значення 65,285 відображається за допомогою функції AVERAGE, як показано нижче:
Різниця в середньому арифметиці в цьому прикладі має сенс, оскільки число ділення різне для обох цих функцій. Число для функції AVERAGEA дорівнює 8, оскільки вона також оцінює TEXT як дійсні дані. Але для функції AVERAGE кількість ділення дорівнює 7, оскільки вона не включає дані TEXT у свій обчислення.
Замість ТЕКСТОВИХ даних, якщо набір даних містить нуль (0), той самий результат (або середнє арифметичне) відображатиметься для обох функцій (AVERAGE та AVERAGEA), оскільки обидві містять нуль як дійсні дані в обчисленні.
Обчислення середнього в Excel: функція AVERAGEIF
Функція AVERAGEIF є релевантною функцією функції AVERAGE і допомагає нам обчислити середнє арифметичне за заданою умовою або критерієм. За допомогою функції AVERAGEIF ми можемо обчислити середнє лише для певних чисел, які відповідають вказаним критеріям. Excel спочатку оцінює конкретні критерії, знаходить ефективні клітинки за цими критеріями, а потім відповідно обчислює середнє значення лише для розташованих клітинок.
Синтаксис
Синтаксис функції AVERAGEIF Excel такий:
=AVERAGEAIF(діапазон; критерії; [середній_діапазон])
Тут діапазон і критерії є обов’язковими аргументами, тоді як середній_діапазон є необов’язковим.
- Діапазон містить клітинки, які потрібно перевірити на відповідність заданим критеріям.
- Критерій відноситься до аргументу, де ми можемо вказати бажану умову, якої слід дотримуватися. Він може містити текст, числові значення, посилання на клітинки, діапазон і логічний вираз.
- Середній_діапазон — це аргумент, у якому ми згадуємо клітинки (або діапазон), для яких ми хочемо обчислити середнє значення. Якщо цей аргумент не вказано, Excel автоматично використовує діапазон.
Кроки для обчислення середнього за допомогою функції AVERAGEIF
У тому ж прикладі припустимо, що ми кілька разів перелічуємо деякі фрукти з різними цінами. Цього разу нам потрібно лише обчислити середнє для будь-якого конкретного повторного фрукта (тобто яблука) у клітинці B11. Отже, ми використовуємо функцію AVERAGEIF і застосовуємо умову під час обчислення середнього значення. Щоб обчислити середню ціну для Apple, ми повинні виконати наведені нижче дії.
- Спочатку ми вибираємо результуючу клітинку (B11) і вставляємо формулу AVERAGEIF наступним чином:
=AVERAGEIF(A2:B9; 'Apple'; B2:B9)
Тут A2:B9 представляє ефективний діапазон, Apple представляє наші бажані критерії/умови, а B2:B9 представляє середній_діапазон.
Тут ми вручну вказали необов’язковий середній_діапазон, щоб включити лише клітинки з цінами. У нашому прикладі, якщо ми не передаємо середній_діапазон, функція використовуватиме основний діапазон (A2:B9) із текстовими даними та викличе #DIV! помилка.
Примітка. Важливо зауважити, що функція AVERAGEIF ігнорує порожні клітинки в своєму аргументі середній_діапазон. Крім того, якщо будь-яка клітинка, указана в критеріях, пуста, функція розглядає її як клітинку з нульовим значенням.
Обчислення середнього в Excel: функція AVERAGEIFS
Функція AVERAGEIFS є розширеною або вдосконаленою версією функції AVERAGEIFS. Функція AVERAGEIF дозволяє нам застосовувати лише одну умову за раз. Однак за допомогою функції AVERAGEIFS ми можемо застосувати кілька критеріїв для одночасного тестування. Крім того, функція AVERAGEIFS дає нам змогу використовувати комірки з логічними значеннями TRUE та FALSE, допомагаючи нам легко перевіряти кілька критеріїв. Це також неможливо з функцією AVERAGEIF.
Синтаксис
Синтаксис функції AVERAGEIFS Excel такий:
=AVERAGEAIFS(середній_діапазон, критерій1_діапазон1, критерій1, [критерій2_діапазон2, критерій2], ...)
Тут середній_діапазон, критерій1_діапазон1 і критерій1 є обов’язковими аргументами. Однак ми можемо надати додаткові аргументи на основі наших ефективних наборів даних. Крім того, критерій1 відноситься до умови, яку потрібно перевірити в діапазоні1, критерій2 стосується умови, яку потрібно перевірити в діапазоні2, тощо.
Кроки для обчислення середнього за допомогою функції AVERAGEIFS
Давайте розглянемо ті самі приклади даних, які ми використовували в попередньому методі. У попередньому методі ми використали функцію AVERAGEIF і обчислили середнє для цін на повторювані фрукти (яблуко). У такому разі у нас була лише одна умова.
Тепер припустімо, що нам потрібно обчислити середнє для цін Apple, але включити лише ті комірки, ціни в яких перевищують 40.
У цьому випадку ми маємо два різні критерії, тому функцію AVERAGEIF не можна використовувати.
Критерії 1: У діапазоні A2:A9 початковим критерієм є перевірка 'Apple', яка відповідає трьом клітинкам (або рядкам)
Критерій 2: У діапазоні B2:B9 іншим критерієм є перевірка цін вище 40 (>40), які задовольняють дві з трьох клітинок (або рядок) із фруктовим яблуком
Таким чином, якщо ми математично обчислюємо середнє значення, ми використовуємо наведену нижче формулу:
(95+68)/2
Це повертає середнє значення 81.5. Давайте перевіримо, чи ми отримуємо однакові результати за допомогою функції AVERAGEIFS в Excel.
Коли нам потрібно перевірити умови в Excel, ми зазвичай використовуємо логічний оператор АБО. Оператор АБО повертає TRUE і FALSE залежно від того, чи виконується дана умова. Наприклад, припустімо, що нам потрібно перевірити, чи клітинка A2 містить Apple. У цьому випадку ми можемо використовувати оператор АБО так:
=АБО(A2='Apple')
Ми вставляємо наведену вище умову в наш приклад аркуша, щоб перевірити або знайти клітинки з фруктом Apple. Отже, ми створюємо новий стовпець (C) і вводимо конкретну умову в клітинку C2. Після цього ми копіюємо ту саму умову в решту клітинок стовпця C. Це виглядає так:
"яка різниця між левом і тигром"
На зображенні вище показано, що оператор OR повертає TRUE для клітинок, які відповідають фрукту Apple, тоді як для інших повертає FALSE. На основі цього результату ми тепер застосуємо функцію AVERAGEIFS і вставимо кілька умов для комірок зі значеннями TRUE таким чином:
- Як і в інших методах, ми знову спочатку вибираємо результуючу комірку (тобто B12).
- Далі ми вводимо формулу AVERAGEIFS таким чином:
=AVERAGEIFS(B2:B9;C2:C9;'ІСТИНА';B2:B9;'>40')
За наведеною вище формулою AVERAGEIFS ми включили лише фрукти «Яблуко» та ціни вище 40. - Нарешті, ми повинні натиснути Введіть ключ для отримання результату формули. Для нашого набору даних формула повернула середнє арифметичне 81,5, те саме, що й наш математично обчислений результат.
Особливі випадки
Хоча під час обчислення середнього арифметичного в Excel ми можемо мати різні сценарії, тут обговорюються два найпоширеніші. У цих випадках ми повинні бути обережними, обчислюючи середнє арифметичне.
Обчислення середнього з нулями
Коли нам потрібно обчислити середнє значення з нулями, ми зазвичай можемо використовувати функцію AVERAGE і надати ефективні комірки, включно з комірками з нульовими значеннями. Функція AVERAGE включає нулі як дійсні числа під час обчислення.
У наведеному нижче прикладі набору даних ми застосовуємо функцію AVERAGE, щоб обчислити середнє значення для двох різних оцінок студентів:
На зображенні вище показано різні середні значення для обох студентів. Якщо ми подивимося на оцінки, сума оцінок в обох учнів однакова. Другий учень має нуль з одного предмету (комірка з нульовим значенням), а перший учень відсутній (комірка з текстовим значенням) з одного предмету.
Оскільки функція AVERAGE ігнорує текстові значення, але включає нульові значення, кількість ділень різна для обох студентів. Для першого учня ділення дорівнює 4, а для іншого учня – 5. Таким чином, є різниця в середніх результатах. Однак ми також можемо явно виключити нульові значення, якщо це необхідно.
Обчислення середнього без урахування нулів
Коли нам потрібно обчислити середнє без урахування нулів, ми повинні скористатися функцією Excel AVERAGEIF. Ми можемо застосувати певну умову в цій функції, вказуючи Excel не включати клітинки з нульовими значеннями під час обчислення.
Наведену нижче формулу AVERAGEIF можна використовувати для виключення нульових значень під час обчислення середнього значення в Excel:
=AVERAGEIF(комірка або діапазон,'>0')
Якщо ми вставимо наведену вище формулу для наших вибіркових даних, для обох студентів з’являться однакові результати середнього значення.
Наведена вище формула AVERAGEIF ігнорує текст і клітинки з нульовим значенням. Таким чином, кількість ділень однакова (тобто 4), а також середнє арифметичне для оцінок обох учнів.
Важливі моменти, про які слід пам’ятати
- Важливо зауважити, що будь-яка функція в Excel, включаючи AVERAGE, AVERAGEA, AVERAGEIF і AVERAGEIFS, може приймати максимум 255 аргументів.
- Вручну вводити назву функції в клітинку Excel не рекомендується. Вибравши потрібну функцію зі списку пропозицій, краще уникнути неправильного написання назви функцій і усунути помилки в результатах.