Microsoft Excel (често наричан Excel) е мощен софтуер за електронни таблици, предназначен да съхранява големи набори от данни. Освен това позволява на потребителите да организират и анализират данни и дори да извършват основни до сложни изчисления върху записаните данни. Основните задачи на Excel за анализ на данни включват главно изчисляване или намиране на средна стойност (или средно аритметично), режим и медиана.
В този урок обсъждаме различни методи стъпка по стъпка или решения за това как да изчислим средната стойност в Excel. Преди да обсъдим методите, нека първо разберем накратко концепцията за Mean и нейните предимства.
Какво е средното в Excel?
В Excel средната аритметична стойност не е нищо друго освен средно число, което обикновено се изчислява чрез добавяне на всички числа от даден набор от данни и след това разделяне на сумата на общия брой стойности (или брой) на дадени елементи.
Въпреки че е лесно да се изчисли средната аритметична стойност за малки набори от данни в реалния живот, става трудно да се работи с големи набори от данни. Това е мястото, където мощна програма за електронни таблици като Excel идва на помощ. Може лесно да изчисли средната стойност на стотици до хиляди числа. Освен това е по-малко склонен към грешки.
Има много основни приложения на средната стойност при анализиране на данни, като например:
Linux команди
- За сравняване на исторически данни
- За подчертаване на 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)/БРОЙ(1,2,3,4,5)
Тук функцията SUM сумира дадените стойности, докато функцията COUNT определя броя на дадените стойности.
Сега, ако запишем тези пет числа в диапазон A1:A5, формулата по-долу в клетка A6 ще изчисли средното аритметично:
=СУМА(A1:A5)/БРОЙ(A1:A5)
Резултатът (или средната стойност), получен в горното изображение, е същият като този, който изчислихме от математическата формула, т.е. 3.
Изчисляване на средна стойност в Excel: функция AVERAGE
Тъй като средната стойност е средна стойност, функцията AVERAGE на Excel е най-ефективният начин за изчисляване на средната аритметична стойност. Това е една от най-често използваните статистически функции за изчисляване на средната стойност за определен диапазон, ако няма специално условие или ограничение. Това е вградената функция на Excel.
Синтаксис
Синтаксисът на функцията AVERAGE на Excel е както следва:
=СРЕДНО(число1, [число2], ...)
Number1 е задължителният аргумент, което означава, че един аргумент е задължителен във функцията 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 е както следва:
=СРЕДНА(стойност1, [стойност2], ...)
Подобно на функцията AVERAGE, първият аргумент във функцията AVERAGEA е задължителен. Въпреки това можем да използваме множество аргументи, ако е необходимо.
Синтаксисът на функциите AVERAGE и AVERAGEA е почти подобен; тяхната работна концепция обаче е малко по-различна. Функцията AVERAGE може да изчисли само средната аритметична стойност за числови стойности, докато функцията AVERAGEA може да изчисли същото за всеки тип данни, включително текст и булеви стойности.
Стъпки за изчисляване на средната стойност с помощта на функцията AVERAGEA
Нека повторим предишните примерни набори от данни, където имаме имена на плодове в колона A (A2:A9) и техните цени в колона B (B2:B9). Този път обаче нямаме цената за гроздето (записано като „Не е налично“ в съответната клетка B6), както е показано по-долу:
Можем да изпълним стъпките по-долу и да изчислим средната стойност с помощта на функцията AVERAGEA:
- Подобно на предишния метод, първо избираме получената клетка B10, за да запишем средната стойност. След това въвеждаме знака за равенство (=), въвеждаме или избираме нашата функция AVERAGEA и предаваме целия диапазон от данни на функцията, затворена в скобите.
- И накрая, трябва да натиснете Въведете ключ за получаване на ефективна продукция (или средна стойност).
Горното изображение връща средната стойност от 57,125 с функцията AVERAGEA.
Ако използваме функцията AVERAGE в този пример и изчислим средната аритметична стойност, виждаме огромна разлика между резултатите, получени от двете функции. Средният резултат от 65,285 се появява с функцията AVERAGE, както е показано по-долу:
Разликите в средното аритметично в този пример имат смисъл, защото броят на делението е различен и за двете функции. Броят на числата за функцията AVERAGEA е 8, тъй като тя също оценява ТЕКСТА като валидни данни. Но за функцията AVERAGE броят на делението е 7, тъй като тя не включва данните TEXT в своето изчисление.
Вместо ТЕКСТОВИ данни, ако наборът от данни съдържа нула (0), същият резултат (или средно аритметично) ще се появи и за двете функции (СРЗНА и СРЗНА), тъй като и двете включват нула като валидни данни в изчислението.
Изчисляване на средна стойност в 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 е разширена или разширена версия на функцията AVERAGEIF. Функцията 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, обикновено използваме помощта на логическия оператор OR. Операторът OR връща TRUE и FALSE в зависимост от това дали даденото условие удовлетворява. Да предположим например, че трябва да проверим дали клетка A2 съдържа Apple. В този случай можем да използваме оператора OR по следния начин:
=ИЛИ(A2='Apple')
Вмъкваме горното условие в нашия примерен лист, за да тестваме или локализираме клетки с плода Apple. И така, създаваме нова колона (C) и въвеждаме конкретното условие в клетка C2. След това копираме същото условие в останалите клетки на колона C. Изглежда така:
Изображението по-горе показва, че операторът OR връща TRUE за клетки, съответстващи на плода Apple, докато за други връща FALSE. Въз основа на този резултат сега прилагаме функцията AVERAGEIFS и вмъкваме множество условия за клетки със стойност TRUE по следния начин:
- Подобно на други методи, отново първо избираме получената клетка (т.е. B12).
- След това въвеждаме формулата AVERAGEIFS по следния начин:
=СРЕДНОIFS(B2:B9;C2:C9;'ВЯРНО';B2:B9;'>40')
С горната формула AVERAGEIFS включихме само плода „Ябълка“ и цени над 40. - И накрая, трябва да натиснете Въведете ключ за получаване на резултата от формулата. За нашия набор от данни формулата върна средното аритметично на 81,5, същото като нашия математически изчислен резултат.
Специални случаи
Въпреки че може да имаме различни сценарии, докато изчисляваме средната аритметична стойност в Excel, тук се обсъждат двата най-често срещани. Трябва да сме внимателни, когато изчисляваме средната аритметична стойност в тези случаи.
Изчисляване на средна стойност, включително нули
Когато трябва да изчислим средната стойност с нули, обикновено можем да използваме функцията AVERAGE и да предоставим ефективните клетки, включително клетките с нулева стойност. Функцията AVERAGE включва нулите като валидни числа в своето изчисление.
В следния примерен набор от данни прилагаме функцията AVERAGE, за да изчислим средната стойност за две различни оценки на ученици:
Изображението по-горе показва различните средни стойности за двата ученика. Ако погледнем оценките, сумата от оценките е еднаква и за двамата ученици. Вторият ученик има нула оценки по един предмет (клетка с нулева стойност), докато първият ученик отсъства (клетка с текстова стойност) по един предмет.
Тъй като функцията AVERAGE игнорира текстови стойности, но включва нулеви стойности, броят на делението варира и за двата ученика. За първия ученик делението е 4, докато за друг ученик е 5. По този начин има разлика в средните резултати. Въпреки това можем също изрично да изключим нулеви стойности, ако е необходимо.
Изчисляване на средна стойност без нули
Когато трябва да изчислим средната стойност, като изключим нулите, трябва да се възползваме от функцията AVERAGEIF на Excel. Можем да приложим специфичното условие в тази функция, като инструктираме Excel да не включва клетките с нулева стойност по време на изчислението.
Формулата AVERAGEIF по-долу може да се използва за изключване на нулеви стойности при изчисляване на средната стойност в Excel:
=AVERAGEIF(клетка или диапазон,'>0')
Ако вмъкнем горната формула за нашите примерни данни, резултатите от една и съща средна стойност се появяват и за двамата студенти.
Горната формула AVERAGEIF игнорира текста и клетките с нулева стойност. И така, броят на делението е еднакъв (т.е. 4), както и средното аритметично за оценките на двамата ученици.
Важни моменти, които трябва да запомните
- Важно е да се отбележи, че всяка функция в Excel, включително AVERAGE, AVERAGEA, AVERAGEIF и AVERAGEIFS, може да приеме максимум 255 аргумента.
- Не се препоръчва ръчно въвеждане на името на функцията в клетка на Excel. Избирането на желаната функция от списъка с предложения е по-добре, за да се избегнат неправилно изписани имена на функции и да се премахнат грешките в резултатите.