Jakiś czas temu napisała do mnie Dorota z bardzo ciekawym problemem. Zobaczcie:
Mam dość duży zakres danych, z których interesują mnie wartości znajdujące się w co którejś kolumnie, a jest tych kolumn kilkadziesiąt (tych, które mnie interesują). Potrzebuję z tych danych zrobić średnią, z pominięciem zer. Średnia.jeżeli nie działa, ponieważ ma za dużo atrybutów (te wszystkie komórki, wpisane oddzielnie). Masz jakiś pomysł, jak można by to zrobić?
Dodam, że zależy mi na utrzymaniu tych danych w takim układzie, w jakim są, więc wyciąganie ich do oddzielnego arkusza, po to, żeby skorzystać z średniej jeżeli, odpada. No chyba, że się poddam. 😉
Czyli Dorota ma ogromniastą tabelę z „milionem” kolumn i z niektórych z nich chce wyciągnąć średnią. Jeśli w jakiejś z wybranych kolumn jest zero – ma nie zostać uwzględnione w liczeniu średniej. Czyli tak na prawdę mamy dwa warunki do spełnienia:
1 – liczby niezerowe,
2 – odpowiednia kolumna.
Formatka wygląda tak (oczywiście w moim przykładzie jest znacznie mniej danych, a przede wszystkim – kolumn ;)):
Formatka
Analiza sytuacji
Załóżmy, że chcielibyśmy wyliczyć średnią niezerowych wartości z kolumn 1, 3, 6 i 7. Czyli dla pierwszego wiersza z danymi na obrazku powyżej liczylibyśmy średnią z liczb: 20, 0, 0 i 10. Natomiast po pominięciu zer mamy: 20 i 10, czyli (20+10)/2 = 15. Zastanówmy się nad możliwymi rozwiązaniami.
Zwykła funkcja ŚREDNIA uwzględni zera, niestety. Można jej co prawda wskazać ręcznie kolumny, z których chcemy liczyć średnią, ale i tak nie poradzi sobie ona z zerami. Poza tym pamiętajcie, że ta funkcja ma być odporna na ilość kolumn.
Funkcja ŚREDNIA.JEŻELI z kolei poradzi sobie z zerami, wystarczy jej podać jako warunek, że uśredniane wartości mają być różne od zera. Nie poradzi sobie jednak z wyborem kolumn, bo ma tylko jeden argument, w którym określamy zakres liczenia średniej.
Tylko jeden taki argument ma też funkcja ŚREDNIA.WARUNKÓW. Na pierwszy rzut oka będzie więc też nieprzydatna. „Nic bardziej mylnego” – jak to mawia Radek Kotarski.;) Funkcja ma bowiem jedną, ogromną zaletę: ma bardzo dużo argumentów, w których można podać warunki, które weźmie pod uwagę przy wyliczaniu średniej. I to właśnie wykorzystamy. Na początku artykułu już się trochę zdradziłam, że tymi warunkami są:
- uśredniana liczba musi być różna od zera,
- uśredniana liczba musi znajdować się w określonej kolumnie.
Jedyna trudność, jaka się tutaj pojawia, to: jak wskazać kolumnę? Moim ulubionym na to sposobem jest po prostu dodanie dodatkowego wiersza, który ma być uwzględniony przez funkcję. W tym wierszu po prostu wpiszemy jakiś znacznik, który ma wskazać funkcji wybrane kolumny. Tym znacznikiem może być np. słowo „tak”, jakaś liczba czy cokolwiek. 🙂 Stąd właśnie żółte pola na formatce. Oczywiście nie muszą być żółte. Najlepiej, żeby w ogóle ich nie było widać, ponieważ są tylko techniczne (zakładam oczywiście, że użytkownik ma ich nie tykać – najlepiej więc ukryć cały drugi wiersz).
No ale do rzeczy…
Formuła
Formuła więc jest taka:
=ŚREDNIA.WARUNKÓW(B4:I4;$B$2:$I$2;"tak";B4:I4;"<>0")
Czyli uśredniamy liczby w zakresie B4:I4, jeśli są w kolumnie, w której w wierszu 2. (zakres B2:I2) jest słowo „tak” (to jest wspomniany znacznik) i są różne od zera.
Funkcja działa sprawnie oprócz sytuacji, w której w wybranych kolumnach są tylko zera. Funkcja nie ma wtedy liczb do uśredniania, czyli ich suma jest równa zero. Funkcja natomiast bardzo chce obliczyć średnią, więc wartości sobie dodaje, a potem dzieli przez ich liczbę, czyli przez zero. Ale przecież „pamiętaj ******o nie dziel przez zero”, więc zwraca w wyniku błąd #DZIEL/0!. Aby temu zapobiec, wrzućmy jeszcze naszą funkcję w JEŻELI.BŁĄD:
=JEŻELI.BŁĄD(ŚREDNIA.WARUNKÓW(B4:I4;$B$2:$I$2;"tak";B4:I4;"<>0");0)
Najlepsze, co przyszło mi do głowy, to wyświetlenie zera w takim przypadku. 🙂 Oczywiście możecie wyświetlić, co chcecie.
I to by było na tyle. Uzyskujemy taki efekt:
Wynik
Voilà!
Przyda Wam się? Dajcie znać w komentarzach poniżej artykułu. 🙂
Plik do pobrania:
MalinowyExcel_Średnia z wybranych kolumn dw.xlsx
I wersja wideo:
Redakcja językowa: Aleksandra Wasiak
www.kulturajezyka.pl
0 komentarzy