Този урок ще покаже как да се изчисли средната стойност на стойностите, които отговарят на определени критерии в Excel и Google Sheets.
СРЕДНА функция
Функцията MEDIAN изчислява средната стойност в поредица от числа.
= СРЕДНИ (C2: C10)
Въпреки това, няма вградена функция „Median If“ за изчисляване на средната стойност само за числа, които отговарят на определени критерии.
Забележка: Може да сте запознати с функцията AVERAGEIFS, която ще изчисли средната стойност за числа, които отговарят на определени критерии. Няма средна алтернатива.
Средна формула If - масив
Така че, за да постигнем целта си да изчислим средната стойност на числата, които отговарят на определени критерии, трябва да използваме формула за масив. С нашата формула на масива ще използваме функцията IF вътре във функцията MEDIAN, така че да вземем само медианата на стойностите, които отговарят на критериите, въведени във функцията IF.
Използвайки предишния ни набор от данни, нека вземем средната стойност за Apple.
= СРЕДНИ (АКО ($ A $ 2: $ A $ 10 = $ E2, $ C $ 2: $ C $ 10))
Excel 2022 и по -рано
В Office 365 и версии на Excel след 2022 г. можете просто да въведете горната формула, както обикновено (чрез натискане ENTER).
За Excel 2022 и по -ранни обаче трябва да въведете формулата, като натиснете CTRL + SHIFT + ENTER. След като направите това, ще забележите, че около формулата се появяват къдрави скоби:
{= СРЕДНИ (АКО ($ A $ 2: $ A $ 10 = $ E2, $ C $ 2: $ C $ 10))}
Важно: не пишете сами тези къдрави скоби, те трябва да се въвеждат само с CTRL + SHIFT + ENTER.
Как действа формулата?
Не забравяйте, че това е нашата формула:
{= СРЕДНИ (АКО ($ A $ 2: $ A $ 10 = $ E2, $ C $ 2: $ C $ 10))}
Формулата работи, като оценява критериите на всяка стойност като TRUE или FALSE.
= СРЕДНИ (IF ({FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; TRUE; FALSE}, {1287; 1205; 1243; 1482; 1261; 1042; 1090; 1748; 1909}))
След това функцията IF замества всяка стойност с FALSE, ако нейното условие е изпълнено.
= СРЕДНИ ({FALSE; 1205; FALSE; FALSE; 1261; FALSE; 1090; 1748; FALSE})
Сега функцията MEDIAN игнорира FALSE стойностите и изчислява медианата на останалите стойности (1233 е средната стойност на двете средни стойности: 1205 и 1261).
Медиана IF - Множество критерии
Можете също така да изчислите медиана въз основа на множество критерии, като използвате булева логика.
Нека да разгледаме нов набор от данни с колони Местоположение, Година и Запас:
Ето формулата за изчисляване на медианата въз основа на множество критерии:
= СРЕДНИ (IF ((A2: A4 = "B")*(B2: B4 = 2008), C2: C4))
Забележете тук, че умножаваме два критерия заедно:
(G8: G10 = "b")*(H8: H10 = 2008)
Ако и двата критерия са TRUE, той ще се изчисли като TRUE, но ако един (или повече) критерии са FALSE, той ще се изчисли като FALSE.
Използвайки тази методология, можете да добавите много различни критерии.
Средна формула If в Google Таблици
Всички горни примери работят абсолютно същите в Google Sheets, както и в Excel.
