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

Середнє арифметичне в excel. Таблиці Excel, якнайкраще підходять для будь-яких обчислень. Вивчивши Excel Ви зможете вирішувати завдання з хімії, фізики, математики, геометрії, біології, статистики, економіки та багато інших. Ми навіть не замислюємося, який потужний інструмент знаходиться на наших комп'ютерах, а отже, і не використовуємо його на повну силу. Багато батьків думають, що комп'ютер – це просто дорога іграшка. А даремно! Звичайно для того, щоб дитина могла на ньому дійсно займатися, Вам самим необхідно навчиться на ньому працювати, а потім і дитину навчити. Ну це вже інша тема, а сьогодні я хочу поговорити з Вами про те, як знайти середнє арифметичне в Excel.

Як знайти середнє арифметичне в Excel

Про швидке в Excel ми вже з вами говорили, а сьогодні поговоримо про середнє арифметичне.

Виділимо комірку С12та за допомогою Майстри функцій запишемо до неї формулу обчислення середнього арифметичного. Для цього на панелі інструментів Стандартна клацніть по кнопці — Вставка функції –fx (На малюнку вище червона стрілка зверху). Відкриється діалогове вікно Майстер Функцій .

• Виберіть поле Категорії — Статистичні ;
• В полі Виберіть функцію: Відмінник ;
• Натисніть кнопку ОК .

Відкриється наступне вікно Аргументи та функції .

В полі Число1ви побачите запис С2:С11– програма сама визначила діапазон осередків, для яких необхідно знайти середнє арифметичне.

Натисніть кнопку ОКі в осередку С12з'явиться середнє арифметичне балів.

Виявляється, обчислити середнє арифметичне в Excel зовсім не складно. А я завжди боялася будь-яких формул. Ех, не на той час ми вчилися.

Цей табличний процесор упорається практично з усіма розрахунками. Він ідеально підходить для бухгалтерського обліку. Для обчислень існують спеціальні інструменти – формули. Їх можна застосовувати до діапазону або до окремих осередків. Щоб дізнатися мінімальну чи максимальну цифру групи клітин, необов'язково шукати їх самостійно. Найкраще скористатися призначеними для цього опціями. Також корисно буде розібратися, як порахувати середнє значення Excel.

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

Максимальне та мінімальне

Ось як знайти максимальне значення в Excel:

1. Поставте курсор-осередок у будь-яке місце.
2. Перейдіть до меню "Формули".
3. Натисніть кнопку «Вставити функцію».
4. У списку оберіть «МАКС». Або напишіть це слово у полі «Пошук» та натисніть «Знайти».
5. У вікні «Аргументи» введіть адреси діапазону, максимальне значення якого потрібно дізнатися. В Excel імена клітин складаються з літери та цифри («B1», «F15», «W34»). А назва діапазону - це перший і останній осередки, які до нього входять.
6. Замість адреси можна написати кілька чисел. Тоді система покаже найбільше їх.
7. Натисніть "OK". У клітці, де стояв курсор, з'явиться результат.

Наступний крок – вкажіть діапазон значень

Тепер буде легко розібратися, як знайти мінімальне значення в Excel. Алгоритм дій цілком ідентичний. Просто замість "МАКС" виберіть "МІН".

Середнє

Середнє арифметичне обчислюється так: скласти всі цифри з множини і поділити на їх кількість. В Екселі можна порахувати суми, дізнатися, скільки осередків у рядку і таке інше. Але це надто складно та довго. Прийде використовувати багато різних функцій. Пам'ятайте інформацію. Або навіть щось записувати на листочок. Але можна спростити алгоритм.

Ось як знайти середнє значення в Excel:

1. Поставте комірку курсор у будь-яке вільне місце таблиці.
2. Перейдіть на вкладку Формули.
3. Натисніть кнопку «Вставити функцію».
4. Виберіть «ЗНАЧ».
5. Якщо цього пункту немає у списку, відкрийте його за допомогою опції «Знайти».
6. В області «Число1» введіть адресу діапазону. Або напишіть кілька цифр у різних полях «Число2», «Чісло3».
7. Натисніть "OK". У комірці з'явиться необхідне значення.

Так можна проводити розрахунки не тільки з позиціями в таблиці, але і з довільними множинами. Excel, власне, грає роль просунутого калькулятора.

Інші способи

Максимальне, мінімальне та середнє можна дізнатися й іншими способами.

1. Знайдіть панель функцій із позначкою «Fx». Вона над основною робочою областю таблиці.
2. Поставте курсор у будь-яку комірку.
3. Введіть аргумент у полі «Fx». Він починається зі знаку рівності. Потім йде формула та адреса діапазону/клітини.
4. Повинно вийти щось на кшталт «=МАКС(B8:B11)» (максимальне), «=МІН(F7:V11)» (мінімальне), «=СРЗНАЧ(D14:W15)» (середнє).
5. Клацніть на «галочку» поруч із полем функцій. Або просто натисніть клавішу Enter. У виділеному осередку з'явиться потрібне значення.
6. Формулу можна скопіювати безпосередньо до самої клітини. Ефект буде той самий.

Знайти та обчислити допоможе Excel-інструмент «Автофункції».

1. Поставте курсор у комірку.
2. Знайдіть кнопку, назва якої починається на "Авто". Це залежить від обраної в Excel налаштування за замовчуванням («Автосума», «Авточисло», «Автозміщ», «Автоіндекс»).
3. Натисніть на чорну стрілку під нею.
4. Виберіть "МІН" (мінімальне значення), "МАКС" (максимальне) або "СРЗНАЧ" (середнє).
5. У зазначеній клітині з'явиться формула. Клацніть на будь-яку іншу комірку - вона буде додана в функцію. Розтягніть рамку навколо неї, щоб охопити діапазон. Або клацайте по сітці із затиснутою клавішею Ctrl, щоб виділяти по одному елементу.
6. Коли закінчите, натисніть клавішу Enter. Результат відобразиться у клітці.

В Excel вирахувати середнє значення досить легко. Не треба складати, а потім ділити суму. І тому існує окрема функція. Також можна знайти мінімум і максимум у множині. Це набагато легше, ніж рахувати вручну або шукати цифри у величезній таблиці. Тому Ексель популярний у багатьох сферах діяльності, де потрібна точність: бізнес, аудит, кадрове діловодство, фінанси, торгівля, математика, фізика, астрономія, економіка, наука.

Excel перетворив обчислення середнього арифметичного кількох осередків на дуже просте завдання– просто використовуйте функцію Відмінник(AVERAGE). Але що робити, якщо деякі значення мають більшу вагу, ніж інші? Наприклад, на багатьох курсах тести мають більшу вагу, ніж завдання. Для таких випадків необхідно розраховувати середнє зважене.

В Excel немає функції для розрахунку середньозваженого значення, зате є функція, яка зробить за Вас більшу частину роботи: СУМПРОВИЗВ(SUMPRODUCT). І навіть якщо Ви ніколи не використовували цю функцію раніше, то до кінця цієї статті працюватимете з нею як профі. Метод, який ми використовуємо, працює у будь-якій версії Excel, а також в інших електронних таблицях, таких як Google Sheets.

Готуємо таблицю

Якщо Ви збираєтеся обчислювати середнє зважене, Вам знадобиться щонайменше два стовпці. Перший стовпець (у нашому прикладі – стовпець B) містить оцінки кожного завдання чи тесту. Другий стовпець (стовпець C) містить ваги. Більша вага означає більший вплив завдання чи тесту на підсумкову оцінку.

Щоб зрозуміти, що таке вага, Ви можете уявити його як відсоток від підсумкової оцінки. Насправді це не так, оскільки в такому разі ваги у сумі мають становити 100%. Формула, яку ми розберемо в цьому уроці, підраховуватиме все правильно і не залежатиме від суми, в яку складаються ваги.

Вводимо формулу

Тепер, коли наша таблиця готова, ми додаємо формулу в комірку B10(підійде будь-який порожній осередок). Як і з будь-якою іншою формулою в Excel, починаємо зі знаку рівності (=).

Перша частина нашої формули – це функція СУМПРОВИЗВ(SUMPRODUCT). Аргументи повинні бути поміщені в дужки, тому відкриваємо їх:

СУМПРОВИЗВ(
=SUMPRODUCT(

Далі додаємо аргументи функції. СУМПРОВИЗВ(SUMPRODUCT) може мати кілька аргументів, але зазвичай використовують два. У нашому прикладі, першим аргументом буде діапазон осередків B2:B9, Що містить оцінки.

СУМПРОВИЗВ(B2:B9
=SUMPRODUCT(B2:B9

Другим аргументом буде діапазон осередків C2:C9, В якому містяться ваги. Між цими аргументами повинен стояти роздільник крапка з комою (кома). Коли все буде готове, закриваємо дужки:

СУМПРОВИЗВ(B2:B9;C2:C9)
=SUMPRODUCT(B2:B9,C2:C9)

Тепер додамо другу частину нашої формули, яка поділить результат функцією, що обчислюється СУМПРОВИЗВ(SUMPRODUCT) на суму ваги. Згодом ми обговоримо, чому це важливо.

Щоб виконати операцію поділу, продовжуємо вже введену формулу символом / (прямий сліш), а далі записуємо функцію СУМ(SUM):

СУМПРОВИЗВ(B2:B9;C2:C9)/СУМ(
=SUMPRODUCT(B2:B9, C2:C9)/SUM(

Для функції SUM(СУМ) ми вкажемо лише один аргумент – діапазон осередків C2:C9. Не забудьте після введення аргументу закрити дужки:

СУМПРОВИЗВ(B2:B9;C2:C9)/СУМ(C2:C9)
=SUMPRODUCT(B2:B9, C2:C9)/SUM(C2:C9)

Готово! Після натискання клавіші Enter Excel розрахує середнє зважене значення. У нашому прикладі підсумковий результат дорівнюватиме 83,6 .

Як це працює

Давайте розберемо кожну частину формули, починаючи з функції СУМПРОВИЗВ(SUMPRODUCT), щоб зрозуміти, як вона працює. Функція СУМПРОВИЗВ(SUMPRODUCT) обчислює добуток оцінки кожного завдання на його вагу, а потім підсумовує усі отримані добутки. Інакше кажучи, функція знаходить суму творів (sum of the products), звідси і отримала свою назву. Отже, для Завдання 1множимо 85 на 5, а для Тестумножимо 83 на 25.

Якщо Вас дивує, навіщо перемножувати значення в першій частині, уявіть, що чим більша вага завдання, тим більша кількістьраз ми маємо враховувати оцінку за нього. Наприклад, Завдання 2пораховано 5 разів, а Підсумковий іспит- 45 разів. Ось чому Підсумковий іспитмає більший вплив на підсумкову оцінку.

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

Якби Ви могли заглянути під капот функції СУМПРОВИЗВ(SUMPRODUCT), то побачили, що насправді вона вважає ось що:

=(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)+(B6*C6)+(B7*C7)+(B8*C8)+(B9*C9)

На щастя, нам не потрібно писати таку довгу формулу, оскільки СУМПРОВИЗВ(SUMPRODUCT) робить все це автоматично.

Сама по собі функція СУМПРОВИЗВ(SUMPRODUCT) повертає нам величезну кількість – 10450 . У цей момент включається друга частина формули: /СУМ(C2:C9)або /SUM(C2:C9), яка повертає результат у нормальний діапазон оцінок, даючи відповідь 83,6 .

Друга частина формули дуже важлива, т.к. дозволяє автоматично коригувати обчислення. Пам'ятаєте, що ваги не повинні складатися на суму 100%? Все це завдяки другій частині формули. Наприклад, якщо ми збільшуємо одне або кілька значень ваг, друга частина формули просто виконає поділ на більше значення, знову призводячи до правильної відповіді. Або ми можемо зробити ваги набагато менше, наприклад, вказати такі значення як 0,5 , 2,5 , 3 або 4,5 , і формула, як і раніше, буде працювати правильно. Здорово, правда?

Для того, щоб знайти середнє значення в Excel (при тому неважливо числове, текстове, відсоткове або інше значення) існує багато функцій. І кожна з них має свої особливості та переваги. Адже у цій задачі може бути поставлені певні умови.

Наприклад, середні значення ряду чисел у Excel вважають за допомогою статистичних функцій. Можна також вручну ввести власну формулу. Розглянемо різні варіанти.

Як знайти середнє арифметичне чисел?

Щоб знайти середнє арифметичне, необхідно скласти всі числа у наборі та поділити суму на кількість. Наприклад, оцінки школяра з інформатики: 3, 4, 3, 5, 5. Що виходить за чверть: 4. Ми знайшли середнє арифметичне за такою формулою: =(3+4+3+5+5)/5.

Як це швидко зробити за допомогою функції Excel? Візьмемо для прикладу ряд випадкових чиселв рядку:

Або: зробимо активним осередок і просто вручну впишемо формулу: = СРЗНАЧ(A1:A8).

Тепер подивимося, що ще вміє функція СРЗНАЧ.

Знайдемо середнє арифметичне двох перших та трьох останніх чисел. Формула: = СРЗНАЧ (A1: B1; F1: H1). Результат:

Середнє значення за умовою

Умовою перебування середнього арифметичного може бути числової критерій чи текстовий. Будемо використовувати функцію: =ДІЙСНО().

Знайти середнє арифметичне чисел, які більші або рівні 10.

Функція: =ЗНАЧАЛИ(A1:A8;">=10")

Третій аргумент - "Діапазон усереднення" - опущений. По-перше, він не обов'язковий. По-друге, аналізований програмою діапазон містить ТІЛЬКИ числові значення. У осередках, зазначених у першому аргументі, і буде здійснюватися пошук за прописаною умовою в другому аргументі.

Увага! Критерій пошуку можна вказати в осередку. А у формулі зробити на неї посилання.

Знайдемо середнє значення чисел за текстовим критерієм. Наприклад, середній продаж товару «столи».

Функція буде виглядати так: =ЗНАЧАЛЬНІ($A$2:$A$12;A7;$B$2:$B$12). Діапазон – стовпець із найменуваннями товарів. Критерій пошуку - посилання на комірку зі словом "столи" (можна замість посилання A7 вставити саме слово "столи"). Діапазон усереднення – ті осередки, у тому числі братимуться дані до розрахунку середнього значення.

В результаті обчислення функції отримуємо таке значення:

Увага! Для текстового критерію (умови) діапазон усереднення вказувати обов'язково.

Як порахувати середньозважену ціну в Excel?

Як ми дізналися середньозважену ціну?

Формула: = СУМПРОВИЗВ (C2: C12; B2: B12) / СУМ (C2: C12).

З допомогою формули СУММПРОИЗВ ми дізнаємося загальну виручку після реалізації кількості товару. А функція СУММ - сумує кількість товару. Поділивши загальний виторг від реалізації товару на загальну кількість одиниць товару, ми знайшли середньозважену ціну. Цей показник враховує «вага» кожної ціни. Її частку у загальній масі значень.

Середнє квадратичне відхилення: формула в Excel

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

Для розрахунку цього статистичного показника складається формула дисперсії. З неї витягується корінь. Але в Excel існує готова функція для знаходження середньоквадратичного відхилення.

Середньоквадратичне відхилення має прив'язку масштабу вихідних даних. Для образного ставлення до варіації аналізованого діапазону цього недостатньо. Щоб отримати відносний рівень розкиду даних, розраховується коефіцієнт варіації:

середньоквадратичне відхилення / середнє арифметичне значення

Формула в Excel виглядає так:

СТАНДОТКЛОНП (діапазон значень)/СРЗНАЧ (діапазон значень).

Коефіцієнт варіації вважається у відсотках. Тому в осередку встановлюємо відсотковий формат.

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

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

Зазначені статті загалом мають дати гарне уявлення про правила розрахунку та коректне використання середніх величин. Але зараз на дворі 21-е (двадцять перше) століття і вручну вважають досить рідко, що, на жаль, не в кращий біквідбивається на розумових здібностях громадян. Навіть калькулятори не в моді (у тому числі програмовані та інженерні), тим паче рахунки та логарифмічні лінійки. Коротше всілякі статистичні розрахунки нині роблять у такій програмі як табличний процесор Excel. Дещо про Excel я вже писав, але згодом тимчасово закинув. Вирішив поки що приділити більше уваги теоретичним питанням аналізу даних, щоб, описуючи розрахунки, наприклад, в Excel, можна було б послатися на базові знання про статистику. Загалом сьогодні ми поговоримо про те, як розрахувати середнє значення в Excel. Уточню лише, що мова піде про середню арифметичну (так-так, є й інші середні величини, але вони використовуються набагато рідше).

Середня арифметична величина – один із найчастіше використовуваних статистичних показників. Аналітику просто необхідно вміти використовувати Ексель для розрахунку, а також для розрахунку інших показників. Та й загалом аналітик без віртуозного володіння Ексель – самозванець, а не аналітик.

Допитливий читач може запитати: а що там рахувати? - Написав формулу і все. Воно-то, звичайно, так, Excel вважає за формулою, але вид формули та результат сильно залежать від вихідних даних. А вихідні дані бувають дуже різні, у тому числі динамічні, тобто мінливі. Тому налаштувати одну формулу, щоб вона підходила на всі випадки життя, не така вже й тривіальна справа.

Почнемо з простого, потім перейдемо до складнішого і, відповідно, цікавішого. Найпростіше - це, якщо потрібно намалювати табличку з даними, і внизу, у підсумковому рядку, показати середнє значення. Для цього, якщо Ви «блондинка», можна використовувати підсумовування окремих осередків за допомогою плюсика (попередньо взявши у дужки) з наступним розподілом на кількість цих осередків. Якщо Ви «брюнетка», то замість окремо позначених осередків через знак «+» можна використовувати формулу підсумовування СУММ() і потім розділити на кількість значень. Проте більш просунуті користувачі Ексель знають, що є готова формула – СРЗНАЧ(). У дужках вказується діапазон вихідних даних, якими розраховується середнє значення, що зручно робити мишкою (комп'ютерної).

Формула СРЗНАЧ

Статистична функція ExcelСРЗНАЧ використовується дуже часто. Виглядає це приблизно так.

Ця формула має чудову властивість, яка надає їй цінність і вигідно відрізняє від ручного підсумовування з розподілом на кількість значень. Якщо діапазоні, яким розраховується формула, присутні порожні комірки (не нульові, саме порожні), то це значення ігнорується і виключається з розрахунку. Таким чином, за наявності відсутності даних за деякими спостереженнями середня величина не занижуватиметься (при підсумовуванні порожній осередок сприймається Екселем як нуль). Цей фактробить формулу СРЗНАЧ цінним інструментом у арсеналі аналітика.

Дістатись формули можна по-різному. Спочатку потрібно виділити комірку, в якій стоятиме формула. Саму формулу можна прописати вручну в рядку формул або скористатися її наявністю на панелі завдань – закладка «Головна», справа вгорі є кнопка, що розгортається, зі значком автосуми Σ:

Після виклику формули в дужках потрібно прописати діапазон даних, якими буде розраховуватися середнє значення. Зробити це можна мишкою, натиснувши ліву клавішу та простягнувши по потрібному діапазону. Якщо діапазон даних не суцільний, то, утримуючи клавішу Ctrl на клавіатурі, можна виділити потрібні місця. Далі натискаємо "Введення". Цей спосіб дуже зручний та часто використовується.

Є стандартний для всіх функцій спосіб виклику. Потрібно натиснути кнопку fxна початку рядка, де прописуються функції (формули) і цим викликати Майстер функцій. Потім або за допомогою пошуку або просто за списком вибрати функцію СРЗНАЧ (можна попередньо відсортувати весь список функцій за категорією «статистичні»).

Після вибору функції натискаємо "Введення" або "Ок" і далі виділяємо діапазон або діапазони. Знову тиснемо на "Введення" або "Ок" Результат розрахунку відобразитися в комірці з формулою. Все просто.

Розрахунок середньої арифметичної зваженої в Excel

(Module 111)

Як неважко здогадатися, формула СРЗНАЧ вміє вважати лише середню арифметичну просту, тобто все складає і ділить кількість доданків (з відрахуванням кількості порожніх осередків). Однак найчастіше доводиться мати справу із середньою арифметичною зваженою. Готової формули в Екселі немає, Крайній мірі, я не знайшов. Тому тут доведеться використати кілька формул. Лякатися не потрібно, це не набагато складніше, ніж використання СРЗНАЧ, хіба що потрібно буде зробити пару зайвих рухів.

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

Загалом розробники Excel явно цей момент не доопрацювали. Доводиться викручуватися і проводити обчислення середньої виваженої в режимі напівавтомат. Однак є можливість скоротити кількість розрахунків. Для цього існує чудова функціяСУМПРОВИЗВ. За допомогою цієї функції можна уникнути проміжного розрахунку в сусідньому стовпці та розрахувати чисельник однією функцією. Розділити на суму терезів можна в цьому ж осередку, дописавши формулу вручну, або в сусідній.

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

Розрахунок середньої арифметичної за умовою

При обчисленні середнього значення можуть виникнути ситуації, коли до розрахунку потрібно включити не всі значення, а лише потрібні, які відповідають деяким умовам (наприклад, товари за товарними групами). Для цього є готова формула ПРИЗНАЧИЛИ.

Буває, що середнє значення потрібно розраховувати за відфільтрованими значеннями. Є і така можливість – функція ПРОМІЖНІ ПІДСУМКИ. У параметрі вибору формули слід поставити 1 (а не 9, як у випадку підсумовування).

Excel пропонує досить великі можливості для розрахунку середньої величини. Я лише описав основні та найзатребуваніші способи. Усе існуючі варіантирозібрати не можна, їх мільйони. Однак те, що описано вище, зустрічається в 90% випадків і цілком достатньо для успішного застосування. Головне тут чітко розуміти, що робиться і для чого. Excel не аналізує, а лише допомагає швидко зробити обчислення. За будь-якими формулами повинен стояти холодний розрахунок і тверезе розуміння аналізу, що проводиться.

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

Нижче бачимо ролик про функцію РОЗЗНАЧИЛИ і розрахунку середньої арифметичної зваженої в Excel