Obliczanie średniej z pominięciem wartości zerowych
Proste użycie funkcji ŚREDNIA() do obliczenia średniej arytmetycznej z zakresu liczb da w efekcie wynik, na który wpływ miały wszystkie liczby z ujętego zakresu – również wartości zerowe.
Z matematycznego punktu widzenia tak właśnie liczy się średnią arytmetyczną, jednak często okazuje się, że wartości zerowe są niepożądane i nie należy brać ich pod uwagę w obliczeniach. Aby uwzględnić warunek pomijania wartości zerowych, zostanie użyta formuła złożona z funkcji ŚREDNIA() i JEŻELI().
Film ilustruje dwie metody obliczania średniej: z wartościami zerowymi i bez tych wartości.
Jak widzisz, obie obliczone wartości znacznie się od siebie różnią. Pierwsza wartość powstała w wyniku zastosowania funkcji ŚREDNIA() dla zakresu B4:B15, czyli:
Linia 1. znak równości ŚREDNIA otwórz nawias okrągły B4 dwukropek B15 zamknij nawias okrągły.
=ŚREDNIA(B4:B15)
natomiast w drugim przypadku zastosowana została formuła tablicowa, którą widzisz na pasku formuły:
Linia 1. znak równości ŚREDNIA otwórz nawias okrągły JEŻELI otwórz nawias okrągły B4 dwukropek B15 znak równości 0 średnik cudzysłów cudzysłów średnik B4 dwukropek B15 zamknij nawias okrągły zamknij nawias okrągły.
=ŚREDNIA(JEŻELI(B4:B15=0;"";B4:B15))
Wyjaśnienie działania formuły:
Funkcja JEŻELI() przyrównuje każdą komórkę z podanego zakresu do zera. Jeśli w wyniku sprawdzenia wystąpiła zgodność, do funkcji ŚREDNIA() przekazywany jest pusty ciąg znaków. Gdy sprawdzenie daje wynik negatywny, przekazywana jest rzeczywista wartość z testowanej komórki. Ostatecznie funkcja ŚREDNIA() przelicza tylko niezerowe wartości, a więc uwzględnia 6 spośród 12 liczb w zakresie.
R1DEVYwFnvdWk
Nagranie filmowe dotyczące obliczania średniej z pominięciem wartości zerowych.
Nagranie filmowe dotyczące obliczania średniej z pominięciem wartości zerowych.
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.
W ramach projektu twoja klasa musi przeprowadzić ankietę na temat częstości spożywania mięsa w ciągu tygodnia przez waszych rówieśników. Oblicz średnią liczbę spożywanych posiłków mięsnych w ciągu tygodnia. Pamiętaj, aby w swoich obliczeniach nie uwzględniać wegan i wegetarian. Dodatkowo przedstaw częstość spożywania mięsa w tygodniu na wykresie kolumnowym.
Przykładowe rozwiązanie zadania:
RYnOa5kAclMyO
Na zrzucie ekranu widoczny jest fragment arkusza Excel. W kolumnach A i B wprowadzono dane dotyczące częstości spożywania mięsa w ciągu tygodnia przez respondentów ankiety. W arkuszu kolejno dodano opisy: w komórce A1 numer osoby, w komórce B2 jak często. W kolumnie A, w komórkach od A2 do A21 wpisano kolejny numer osoby. W kolumnie B, w komórkach od B2 do B21 wpisano wartości częstości spożywania mięsa. Dodatkowo zaprezentowano sposób obliczania średniej. Formułę obliczania należy wpisać w odpowiedniej komórce. W tym przypadku w komórce E2. Brzmi ona następująco: ŚREDNIA(JEŻELI(B2:B21=0;"";B2:B21). Dodano także formuły obliczania ilości osób i jak często jedzą mięso. Formuły wpisano w kolumnie E, w komórkach od E5 do E14. Formuła w komórce E5 brzmi następująco: =LICZ.JEŻELI($B$2:$B$21;D5). Formuła w komórce E6 brzmi następująco: =LICZ.JEŻELI($B$2:$B$21;D6). Formuła w komórce E7 brzmi następująco: =LICZ.JEŻELI($B$2:$B$21;D7). Formuła w komórce E8 brzmi następująco: =LICZ.JEŻELI($B$2:$B$21;D8). Formuła w komórce E9 brzmi następująco: =LICZ.JEŻELI($B$2:$B$21;D9). Formuła w komórce E10 brzmi następująco: =LICZ.JEŻELI($B$2:$B$21;D10). Formuła w komórce E11 brzmi następująco: =LICZ.JEŻELI($B$2:$B$21;D11). Formuła w komórce E12 brzmi następująco: =LICZ.JEŻELI($B$2:$B$21;D12). Formuła w komórce E13 brzmi następująco: =LICZ.JEŻELI($B$2:$B$21;D13). Formuła w komórce E14 brzmi następująco: =LICZ.JEŻELI($B$2:$B$21;D14). Na arkuszu znajduje się także wykres słupkowy prezentujący wyniki ankiety.
Polecenie 2
Twoi rodzice prowadzą firmę zajmującą się przewozem osób. Poprosili cię o pomoc w obliczeniu wynagrodzenia, jakie powinni otrzymać zatrudnieni kierowcy. Za każdy przejechany kilometr otrzymują oni 0,5 zł. Jeśli pracownik przejedzie więcej kilometrów niż wynosi dzienna średnia (liczona tylko z przejazdów osób, które tego dnia wyruszyły w trasę), to otrzymuje premię w wysokości 30% swojego dziennego wynagrodzenia.
Przykładowe rozwiązanie zadania:
R10yhqRJhR0qi
Na zrzucie ekranu widoczny jest fragment arkusza Excel. W kolumnach A i B wprowadzono dane dotyczące osób zatrudnionych w firmie i przejechanych przez nie kilometrów. W arkuszu kolejno dodano opisy: w komórce A1 osoby, w komórce B1 ile przejechali. W kolumnie A, w komórkach od A2 do A6 wpisano imiona osób. W kolumnie B, w komórkach od B2 do B6 wpisano wartości przejechanych kilometrów. Dodatkowo zaprezentowano sposób obliczania wynagrodzenia oraz średniej. Formułę obliczania wynagrodzenia należy wpisać w odpowiedniej komórce. W tym przypadku w komórkach od C2 do C6. Formuła w komórce C2 brzmi następująco: JEŻELI(B2>$F$2;B2*0,5*1,3;B2*0,5). Formuła w komórce C3 brzmi następująco: JEŻELI(B3>$F$2;B3*0,5*1,3;B3*0,5). Formuła w komórce C4 brzmi następująco: JEŻELI(B4>$F$2;B4*0,5*1,3;B4*0,5). Formuła w komórce C5 brzmi następująco: JEŻELI(B5>$F$2;B5*0,5*1,3;B5*0,5). Formuła w komórce C6 brzmi następująco: JEŻELI(B6>$F$2;B6*0,5*1,3;B6*0,5). Formułę dotyczącą obliczania średniej należy wpisać w komórce F2. Brzmi ona następująco: =ŚREDNIA(JEŻELI(B2:B6=0;"";B2:B6)).
