Microsoft Excel (często nazywany Excelem) to potężne oprogramowanie do obsługi arkuszy kalkulacyjnych przeznaczone do przechowywania dużych zbiorów danych. Umożliwia także użytkownikom organizowanie i analizowanie danych, a nawet wykonywanie podstawowych i złożonych obliczeń na zarejestrowanych danych. Podstawowe zadania analizy danych w programie Excel obejmują głównie obliczanie lub znajdowanie średniej (lub średniej arytmetycznej), trybu i mediany.
W tym samouczku omawiamy różne metody i rozwiązania krok po kroku dotyczące obliczania średniej w programie Excel. Zanim omówimy metody, najpierw krótko zrozummy koncepcję średniej i jej korzyści.
Jaka jest średnia w Excelu?
W programie Excel średnia arytmetyczna to nic innego jak średnia liczba, którą zwykle oblicza się poprzez dodanie wszystkich liczb z danego zbioru danych, a następnie podzielenie sumy przez całkowitą liczbę wartości (lub liczbę) danych elementów.
Chociaż w prawdziwym życiu łatwo jest obliczyć średnią arytmetyczną dla małych zbiorów danych, praca z dużymi zbiorami danych staje się trudna. Tutaj z pomocą przychodzi potężny program do obsługi arkuszy kalkulacyjnych, taki jak Excel. Może łatwo obliczyć średnią setek do tysięcy liczb. Ponadto jest mniej podatny na błędy.
Istnieje wiele podstawowych zastosowań średniej w analizie danych, takich jak:
- Aby porównać dane historyczne
- Aby wyróżnić KPI (kluczowe wskaźniki wydajności) i wyceny
- Aby znaleźć punkt środkowy dla podanych punktów danych
- Aby określić zarządzanie projektami i biznesplany
Podstawowym scenariuszem, w którym konieczne może być obliczenie średniej w programie Excel, jest sytuacja, w której musimy poznać średnią sprzedaż przedmiotu w całym miesiącu, kwartale, roku itp.
Jak obliczyć średnią w Excelu?
Niestety nie ma określonej funkcji MEAN umożliwiającej obliczenie lub określenie średniej arytmetycznej danych. Istnieje jednak kilka skutecznych sposobów. Ogólnie rzecz biorąc, zwykle możemy zastosować matematyczną koncepcję dodawania wartości, a następnie dzielenia ich sumy przez liczbę wartości w komórkach Excela. Ponadto istnieją funkcje takie jak ŚREDNIA, ŚREDNIA, ŚREDNIA JEŻELI i ŚREDNIA JEŻELI, które również pomagają nam obliczyć średnią arytmetyczną w różnych przypadkach użycia.
Aby lepiej zrozumieć, jak te pojęcia sprawdzają się przy obliczaniu średniej arytmetycznej, omówmy każdy z osobna na przykładzie:
Obliczanie średniej w Excelu: metoda ogólna
Możemy zastosować funkcje SUMA i COUNT w arkuszu Excela, aby zastosować podejście polegające na dodawaniu wartości i dzieleniu ich przez liczbę uwzględnionych wartości.
Załóżmy na przykład, że podaliśmy liczby od 1 do 5 i musimy obliczyć średnią arytmetyczną. Matematycznie najpierw dodajemy wszystkie te liczby, tj. (1+2+3+4+5). Następnie dzielimy ich SUMĘ przez 5, ponieważ jest to łączna liczba danych liczb.
Zatem cały wzór na obliczenie średniej wygląda następująco:
(1+2+3+4+5)/ 5
Zwraca to średnią arytmetyczną 3.
W Excelu możemy wykorzystać powyższy wzór matematyczny łącząc go z funkcjami SUMA i LICZBA w następujący sposób:
=SUMA(1,2,3,4,5)/LICZBA(1,2,3,4,5)
Tutaj funkcja SUM sumuje podane wartości, natomiast funkcja COUNT określa liczbę podanych wartości.
Jeśli teraz zapiszemy te pięć liczb w zakresie A1:A5, poniższa formuła w komórce A6 obliczy średnią arytmetyczną:
=SUMA(A1:A5)/LICZBA(A1:A5)
Wynik (lub średnia) uzyskany na powyższym obrazku jest taki sam, jak obliczony ze wzoru matematycznego, tj. 3.
Obliczanie średniej w Excelu: Funkcja ŚREDNIA
Ponieważ średnia jest średnią, funkcja ŚREDNIA w programie Excel jest najskuteczniejszym sposobem obliczenia średniej arytmetycznej. Jest to jedna z najczęściej używanych funkcji statystycznych do obliczania średniej dla określonego zakresu, jeśli nie ma specjalnego warunku ani ograniczenia. Jest to wbudowana funkcja programu Excel.
Składnia
Składnia funkcji ŚREDNIA w programie Excel jest następująca:
ciąg.wartość Java
=ŚREDNIA(liczba1, [liczba2], ...)
Liczba1 jest wymaganym argumentem, co oznacza, że w funkcji ŚREDNIA jeden argument jest obowiązkowy. Możemy jednak podać wiele argumentów w różnych formach, takich jak liczby, komórki, zakresy, tablice, stałe itp. Obliczając średnią arytmetyczną, powinniśmy podać co najmniej dwa argumenty w funkcji ŚREDNIA.
Etapy obliczania średniej za pomocą funkcji ŚREDNIA
Przyjrzyjmy się teraz działaniu funkcji ŚREDNIA i obliczmy średnią arytmetyczną dla grupy liczb w arkuszu Excela, korzystając z przykładowych danych.
Załóżmy, że w arkuszu programu Excel wyszczególniliśmy niektóre owoce wraz z odpowiadającymi im cenami. Nazwy owoców zapisano w kolumnie A (A2:A9), a ich ceny w kolumnie B (B2:B9). Musimy znaleźć lub obliczyć średnią cenę (lub średnią arytmetyczną cen) w komórce B10.
Musimy wykonać poniższe kroki, aby obliczyć średnią arytmetyczną w naszym przykładowym arkuszu:
- Najpierw musimy wybrać lub podświetlić komórkę, w której chcemy zapisać wynikową średnią arytmetyczną, czyli komórkę B10.
- Następnie należy wstawić nazwę funkcji zaczynającą się od znaku równości i podać niezbędne argumenty w funkcji. W naszym przypadku wpisujemy znak równości (=) i funkcję ŚREDNIA. Funkcję możemy także wybrać z listy funkcji sugerowanej przez Excela.
- Po wpisaniu nazwy funkcji musimy wpisać nawias początkowy i podać nasz efektywny zakres zbiorów danych. Ponieważ mamy dane w sąsiednich komórkach lub zakresie (od B2 do B9), cały zakres możemy przekazać w funkcji w następujący sposób:
- Na koniec musimy wprowadzić nawias zamykający i nacisnąć Wchodzić na klawiaturze, aby uzyskać efektywny wynik lub średnią arytmetyczną dla danego zbioru danych. Poniższy obrazek przedstawia średnią arytmetyczną cen w komórkach od B2 do B9:
Na powyższym obrazku użyliśmy sąsiednich komórek i dostarczyliśmy cały zakres zestawów danych za jednym razem. Możemy jednak również obliczyć średnią dla nieprzylegających komórek w naszym arkuszu Excel, podając komórki indywidualnie.
Załóżmy, że musimy obliczyć średnią arytmetyczną cen tylko trzech owoców: Jabłko, mango i winogrona. W takim przypadku każdą komórkę możemy przekazać w funkcji osobno w następujący sposób:
=ŚREDNIA(B2,B3,B6)
Tutaj B2, B3 i B6 to pojedyncze komórki z cenami jabłek, mango i winogron.
Chociaż funkcji ŚREDNIA można używać z pustymi komórkami, tekstami, zerami, wartościami logicznymi, a nawet mieszanymi argumentami, podczas obliczeń ignoruje ona puste komórki, tekst i wartości logiczne. Jednakże komórki o wartości zerowej są uwzględniane w funkcji ŚREDNIA.
Obliczanie średniej w Excelu: Funkcja ŚREDNIA
Funkcja ŚREDNIA jest nieco zaawansowaną wersją funkcji ŚREDNIA. Funkcja ŚREDNIA pomaga nam także obliczyć średnią arytmetyczną dla podanych zbiorów danych i działa we wszystkich wersjach Excela. Jedną z zalet funkcji ŚREDNIA jest to, że może ona uwzględniać wartości tekstowe i typy danych Boolean podczas obliczania średniej w programie Excel. Wartości tekstowe są oceniane jako zera, podczas gdy wartości logiczne PRAWDA i FAŁSZ są oceniane odpowiednio jako 1 i 0.
Składnia
Składnia funkcji ŚREDNIA w Excelu jest następująca:
=ŚREDNIAA(wartość1, [wartość2], ...)
Podobnie jak w przypadku funkcji ŚREDNIA, wymagany jest pierwszy argument funkcji ŚREDNIA. W razie potrzeby możemy jednak użyć wielu argumentów.
Składnia funkcji ŚREDNIA i ŚREDNIA jest prawie podobna; jednak ich koncepcja działania jest nieco inna. Funkcja ŚREDNIA może obliczyć tylko średnią arytmetyczną dla wartości liczbowych, podczas gdy funkcja ŚREDNIA może obliczyć to samo dla dowolnego typu danych, w tym tekstu i wartości logicznych.
Etapy obliczania średniej przy użyciu funkcji ŚREDNIA
Powtórzmy poprzednie przykładowe zbiory danych, w których mamy nazwy owoców w kolumnie A (A2:A9) i ich ceny w kolumnie B (B2:B9). Tym razem jednak nie mamy ceny winogron (zapisanej jako „Niedostępne” w odpowiedniej komórce B6), jak pokazano poniżej:
Możemy wykonać poniższe kroki i obliczyć wartość średnią za pomocą funkcji ŚREDNIA:
- Podobnie jak w poprzedniej metodzie, najpierw wybieramy wynikową komórkę B10, aby zapisać średnią. Następnie wpisujemy znak równości (=), wpisujemy lub wybieramy naszą funkcję ŚREDNIA i przekazujemy cały zakres danych do funkcji zawartej w nawiasie.
- Na koniec musimy nacisnąć Wchodzić klucz do uzyskania efektywnej produkcji (lub średniej).
Powyższy obraz zwraca średni wynik 57,125 przy użyciu funkcji ŚREDNIA.
Jeśli w tym przykładzie użyjemy funkcji ŚREDNIA i obliczymy średnią arytmetyczną, zobaczymy ogromną różnicę pomiędzy wynikami uzyskanymi przez obie funkcje. Średni wynik 65,285 pojawia się z funkcją ŚREDNIA, jak pokazano poniżej:
Różnice w średniej arytmetycznej w tym przykładzie mają sens, ponieważ liczba dzieleń jest różna dla obu tych funkcji. Liczba liczb w funkcji ŚREDNIA wynosi 8, ponieważ funkcja ta również ocenia TEKST jako prawidłowe dane. Jednak w przypadku funkcji ŚREDNIA liczba dzieleń wynosi 7, ponieważ w obliczeniach nie uwzględnia ona danych TEKSTOWYCH.
Zamiast danych TEKSTOWYCH, jeśli zestaw danych zawiera zero (0), ten sam wynik (lub średnie arytmetyczne) pojawi się dla obu funkcji (ŚREDNIA i ŚREDNIA), ponieważ obie uwzględniają zero jako prawidłowe dane w obliczeniach.
Obliczanie średniej w Excelu: Funkcja ŚREDNIA.JEŻELI
Funkcja ŚREDNIA JEŻELI jest odpowiednią funkcją funkcji ŚREDNIA i pomaga nam obliczyć średnią arytmetyczną przy danym warunku lub kryterium. Dzięki funkcji ŚREDNIAJEŻELI możemy obliczyć średnią tylko dla określonych liczb spełniających określone kryteria. Excel najpierw ocenia określone kryteria, lokalizuje efektywne komórki zgodnie z tymi kryteriami, a następnie odpowiednio oblicza średnią tylko dla zlokalizowanych komórek.
Składnia
Składnia funkcji ŚREDNIA JEŻELI w programie Excel jest następująca:
=ŚREDNIA AIF(zakres, kryteria, [średni_zakres])
W tym przypadku zakres i kryteria są wymaganymi argumentami, natomiast średni zakres jest opcjonalny.
- Zakres zawiera komórki, które mają zostać przetestowane pod kątem podanych kryteriów.
- Kryterium odnosi się do argumentu, w którym możemy określić żądany warunek, który ma zostać spełniony. Może zawierać tekst, wartości liczbowe, odwołania do komórek, zakres i wyrażenie logiczne.
- Średni_zakres to argument, w którym wymieniamy komórki (lub zakres), dla których chcemy obliczyć średnią. Jeśli ten argument nie zostanie określony, Excel automatycznie użyje zakresu.
Etapy obliczania średniej przy użyciu funkcji ŚREDNIA.JEŻELI
W tym samym przykładzie załóżmy, że kilka razy wymieniamy niektóre owoce z różnymi cenami. Tym razem musimy jedynie obliczyć średnią dla dowolnego konkretnego powtarzającego się owocu (np. jabłka) w komórce B11. Dlatego używamy funkcji ŚREDNIA JEŻELI i stosujemy warunek podczas obliczania średniej. Musimy wykonać poniższe kroki, aby obliczyć średnią cenę Apple:
- Najpierw wybieramy wynikową komórkę (B11) i wstawiamy formułę ŚREDNIA JEŻELI w następujący sposób:
=ŚREDNIA JEŻELI(A2:B9; „Jabłko”, B2:B9)
Tutaj A2:B9 reprezentuje efektywny zakres, Apple reprezentuje nasze pożądane kryteria/stan, a B2:B9 reprezentuje średni_zakres.
Tutaj ręcznie podaliśmy opcjonalny średni zakres, aby uwzględnić tylko komórki z cenami. W naszym przykładzie, jeśli nie przekażemy średniego zakresu, funkcja użyje podstawowego zakresu (A2:B9) z danymi tekstowymi i spowoduje #DIV! błąd.
Uwaga: Należy pamiętać, że funkcja ŚREDNIA JEŻELI ignoruje puste komórki w swoim argumencie średni_zakres. Dodatkowo, jeśli jakakolwiek komórka określona w kryterium jest pusta, funkcja traktuje ją jako komórkę o wartości zerowej.
Obliczanie średniej w programie Excel: funkcja ŚREDNIA.IFS
Funkcja ŚREDNIAJEŻELI jest rozszerzoną lub zaawansowaną wersją funkcji ŚREDNIAJEŻELI. Funkcja ŚREDNIA JEŻELI pozwala nam zastosować tylko jeden warunek na raz. Jednakże dzięki funkcji ŚREDNIA.JEŻELI możemy zastosować wiele kryteriów do jednoczesnego testowania. Dodatkowo funkcja ŚREDNIA.JEŻELI umożliwia nam używanie komórek z wartościami logicznymi PRAWDA i FAŁSZ, co ułatwia nam testowanie wielu kryteriów. Nie jest to również możliwe w przypadku funkcji ŚREDNIA.JEŻELI.
Składnia
Składnia funkcji ŚREDNIA.JEŻELI w programie Excel jest następująca:
=ŚREDNIAAIFS(zakres_średni, kryteria1_zakres1, kryteria1, [zakres_kryteriów2, kryteria2], ...)
W tym przypadku wymaganymi argumentami są średni_zakres, kryteria1_zakres1 i kryteria1. Możemy jednak podać opcjonalne argumenty w oparciu o nasze efektywne zbiory danych. Co więcej, kryteria 1 odnoszą się do warunku, który ma zostać przetestowany w zakresie 1, kryterium 2 odnosi się do warunku, który ma zostać przetestowany w zakresie 2 i nie tylko.
Etapy obliczania średniej przy użyciu funkcji ŚREDNIA.IFS
Rozważmy te same przykładowe dane, które wykorzystaliśmy w poprzedniej metodzie. W poprzedniej metodzie skorzystaliśmy z funkcji ŚREDNIA.JEŻELI i obliczyliśmy średnią dla cen powtarzających się owoców (Jabłko). W takim wypadku mieliśmy tylko jeden warunek.
Załóżmy teraz, że musimy obliczyć średnią cen Apple, ale uwzględniamy tylko te komórki, w których ceny przekraczają 40.
Mamy w tym przypadku dwa różne kryteria, dlatego nie można zastosować funkcji ŚREDNIA.JEŻELI.
Kryteria 1: W zakresie A2:A9 początkowym kryterium jest sprawdzenie, czy „Apple” jest spełnione przez trzy komórki (lub wiersze)
Kryteria 2: W zakresie B2:B9 drugim kryterium jest sprawdzenie cen powyżej 40 (>40), które są spełnione przez dwie z trzech komórek (lub wiersza) z owocem Jabłko
Zatem, jeśli matematycznie obliczymy średnią, skorzystamy z poniższego wzoru:
(95+68)/2
Zwraca to średnią wartość 81,5. Sprawdźmy, czy takie same wyniki otrzymamy za pomocą funkcji ŚREDNIA.JEŻELI w Excelu.
Kiedy musimy przetestować warunki w Excelu, zwykle korzystamy z operatora logicznego OR. Operator LUB zwraca PRAWDA i FAŁSZ w zależności od tego, czy dany warunek jest spełniony. Załóżmy na przykład, że musimy sprawdzić, czy komórka A2 zawiera Apple. W tym przypadku możemy użyć operatora OR w następujący sposób:
=LUB(A2='Jabłko')
Wstawiamy powyższy warunek do naszego przykładowego arkusza, aby przetestować lub zlokalizować komórki z owocem Apple. Tworzymy więc nową kolumnę (C) i wpisujemy konkretny warunek w komórce C2. Następnie kopiujemy ten sam warunek do pozostałych komórek kolumny C. Wygląda to tak:
Powyższy obrazek pokazuje, że operator OR zwraca wartość PRAWDA dla komórek odpowiadających owocowi Apple, natomiast dla pozostałych zwraca wartość FAŁSZ. W oparciu o ten wynik stosujemy teraz funkcję ŚREDNIA.JEŻELI i wstawiamy wiele warunków dla komórek z wartością PRAWDZIWĄ w następujący sposób:
- Podobnie jak w przypadku innych metod, ponownie najpierw wybieramy wynikową komórkę (tj. B12).
- Następnie wprowadzamy formułę ŚREDNIAJEŻELI w następujący sposób:
=ŚREDNIA.IFS(B2:B9;C2:C9;'PRAWDA';B2:B9;'>40')
W powyższym wzorze ŚREDNIA IFS uwzględniliśmy tylko owoc „Jabłko” i ceny powyżej 40. - Na koniec musimy nacisnąć Wchodzić aby uzyskać wynik formuły. W przypadku naszego zbioru danych formuła zwróciła średnią arytmetyczną 81,5, taki sam, jak nasz obliczony matematycznie wynik.
Specjalne przypadki
Chociaż przy obliczaniu średniej arytmetycznej w Excelu możemy mieć różne scenariusze, tutaj omówione zostaną dwa najczęściej spotykane. W takich przypadkach należy zachować ostrożność przy obliczaniu średniej arytmetycznej.
Obliczanie średniej łącznie z zerami
Kiedy musimy obliczyć średnią za pomocą zer, zwykle możemy użyć funkcji ŚREDNIA i podać efektywne komórki, w tym komórki o wartości zerowej. Funkcja ŚREDNIA uwzględnia w swoich obliczeniach zera jako prawidłowe liczby.
W poniższym przykładowym zbiorze danych stosujemy funkcję ŚREDNIA, aby obliczyć średnią dla dwóch różnych ocen uczniów:
Powyższy obrazek pokazuje różne wartości średnie dla obu uczniów. Jeśli spojrzymy na oceny, suma ocen jest taka sama dla obu uczniów. Drugi uczeń ma zero ocen z jednego przedmiotu (komórka o wartości zerowej), natomiast pierwszy uczeń jest nieobecny (komórka z wartością tekstową) z jednego przedmiotu.
Ponieważ funkcja ŚREDNIA ignoruje wartości tekstowe, ale uwzględnia wartości zerowe, liczba dzieleń jest różna dla obu uczniów. Dla pierwszego ucznia liczba podziałów wynosi 4, natomiast dla innego ucznia – 5. Istnieje zatem różnica w średnich wynikach. Jednak w razie potrzeby możemy również wyraźnie wykluczyć wartości zerowe.
Obliczanie średniej bez zer
Kiedy musimy obliczyć średnią bez zer, musimy skorzystać z funkcji Excela ŚREDNIA JEŻELI. Możemy zastosować w tej funkcji określony warunek, instruując program Excel, aby nie uwzględniał komórek o wartości zerowej podczas obliczeń.
Poniższego wzoru ŚREDNIA JEŻELI można użyć do wykluczenia wartości zerowych podczas obliczania średniej w programie Excel:
=ŚREDNIA JEŻELI(komórka lub zakres,'>0')
Jeśli wstawimy powyższy wzór do naszych przykładowych danych, u obu uczniów pojawią się te same wyniki wartości średnich.
Powyższa formuła ŚREDNIAJEŻELI ignoruje komórki tekstowe i o wartości zerowej. Zatem liczba dzieleń jest taka sama (tj. 4), podobnie jak średnia arytmetyczna ocen obu uczniów.
Ważne punkty do zapamiętania
- Należy pamiętać, że dowolna funkcja w programie Excel, w tym ŚREDNIA, ŚREDNIA, ŚREDNIA JEŻELI i ŚREDNIA.JEŻELI, może przyjąć maksymalnie 255 argumentów.
- Nie zaleca się ręcznego wpisywania nazwy funkcji w komórce programu Excel. Lepiej jest wybrać żądaną funkcję z listy sugestii, aby uniknąć błędów w nazwach funkcji i wyeliminować błędy w wynikach.