Wyznaczenie wartości średniej z zawężonego przedziału liczbowego to obliczenie, które może sprawić kłopot niejednemu użytkownikowi Excela. Aby je szybko wykonać, powinieneś użyć formuły tablicowej, ale o nieco bardziej skomplikowanej strukturze. Trudności przy jej budowaniu może Ci sprawić zamieszczenie dwóch warunków w tego typu formule. Tworzy je się inaczej niż w przypadku zwykłych formuł.
Uwaga
Pamiętaj, że formuły tablicowe zawsze zatwierdzasz za pomocą kombinacji klawiszy Ctrl + Shift + Enter.
Składnia formuły tablicowej, w której zawarto dwa warunki, przedstawia się następująco:
=ŚREDNIA(JEŻELI((zakres>=dolna_granica)
*(zakres<=górna_granica);A7:C11))
W miejsce słowa zakres wpisujesz adres komórek, w których znajdują się wszystkie dane źródłowe.
Zamiast słowa dolna_granica podajesz adres komórki z pierwszą liczbą ograniczającą przedział, a w miejsce słowa górna_granica wpisujesz adres komórki z drugą liczbą ograniczającą przedział. Obie wartości mają za zadanie określić, które liczby będą brane do wyznaczania średniej.
Jeśli w obliczeniach za pomocą formuły tablicowej oba warunki mają być spełnione, należy je połączyć znakiem mnożenia (*).
W komórkach B3 i B4 wprowadziliśmy wartości graniczne. Aby obliczyć średnią arytmetyczną z wartości znajdujących się wewnątrz przedziału, w komórce B5 użyliśmy następującej formuły:
=ŚREDNIA(JEŻELI((A7:C11>=B4)*(A7:C11<=B3);A7:C11)).
Wyjaśnienie działania formuły
W pierwszym argumencie funkcji JEŻELI sprawdzane są dwa warunki i tworzone są dwie tablice wartości logicznych PRAWDA (równoznaczna z liczbą 1) lub FAŁSZ (równoznaczna z liczbą 0).
Odpowiadające sobie elementy obu tablic są następnie mnożone i jest tworzona nowa tablica wartości logicznych, ale tym razem stanowiących wyniki iloczynów. Na przykład: dla wartości z komórki A1 w pierwszym teście (A1>=B4) formuła przekazuje do tablicy wartość FAŁSZ (0). Wynikiem drugiego testu (A1<=B4) także będzie także wartość FAŁSZ (0). Iloczyn dwóch zer daje w wyniku zero, a więc do tablicy przekazywana jest wartość FAŁSZ. W analogiczny sposób sprawdzana jest każda komórka zakresu A7:C11.
Do tak utworzonej tablicy podstawiane są liczby z komórek, ale tylko w miejsce wartości logicznych PRAWDA.
Z tego zestawu liczb wyznaczana jest średnia.
