Analiza statystyczna w MS Excel

- Otrzymywanie wartoci modalnej kategorii za pomoc formuy

Wiemy, e funkcja WYST.NAJCZCIEJ() w Excelu nie bdzie dziaaa, gdy jako jej

argument podamy wartoci tekstowe.

Poniej przedstawiamy jak moemy otrzyma warto modaln za pomoc

arkusza. Jest to temat w caoci omawiany podczas kursu Excel w Analizie

Statystycznej. Pokazuje nam ona, ktra warto tekstowa moe najczciej

wystpowa w interesujcym nas zbiorze danych.

eby nie musie prowadzi odwoania do wykresu przestawnego i otrzyma

warto modaln jednej grupy wartoci tekstowych, moemy wyczyta ich warto

za pomoc formuy:

=INDEKS(B2:B21;WYST.NAJCZSCIEJ(PODAJ.POZYCJ(B2:B21;B2:B21;0)))

Zakadamy e wartoci tekstowe s w komrkach B2:B21. W skrcie skadniki

formuy dziaaj tak:

PODAJ POZYCJ() oddaje nam pozycj w tablicy wartoci, gdzie po raz

pierwszy moe wystpi kada poszczeglna warto

Argument trzeci w funkcji PODAJ.POZYCJ(), ukazuje Excelowi, e ten

przypadek wymaga dokadnego dopasowania, a tablica nie moe ulec

sortowaniu. Na przykad dla kadego wystpienia nazwy AUDI w tablicy

funkcja PODAJ POZYCJ() zwraca nam 1, a dla kadego wystpienia wyrazu

RENAULT zwraca nam 2, a gdy wystpuje HONDA zwraca nam 4

Jako argument funkcji WYST.NAJCZCIEJ su wyniki funkcji PODAJ

POZYCJ()

Wynik z funkcji WYST.NAJCZCIEJ () Jest drugim argumentem funkcji

INDEKS. Argumentem pierwszym jest badana przez nas tablica. Drugi

argument okrela nam jak daleko szuka mamy w tablicy.

Excel szuka w tablicy pierwszej wartoci , ktra jest tu AUDI. Jeeli HONDA

bya wartoci wystpujc wartoci tekstow funkcji znan nam jako

WYST.NAJCZCIEJ() zwrciaby nam 4, a INDEKS () posuyby si t

wartoci do znalezienia HONDA w tablicy.

Zliczanie wartoci za pomoc formuy tablicowej

Z wartoci modaln (tutaj dla nas jest ni RENAULT) w garci wci chcemy

wiedzie , ile razy ta warto wystpuje.

Oto licznik nastpujcej formuy:

=SUMA(JEELI (B2:B21=C1;1:0))

To formua tablicowa i musi j potwierdzi specjaln sekwencj przyciskw

CTRL+SHIFT+ENTER. Widzimy, e formua zostaa wprowadzona jako formua

tablicy, jeeli zobaczymy nawiasy klamrowe jakie wystpuj wok niej w polu

formuy. Gdybymy chcieli wprowadzi jako tablicow poprzednia formu miaa

by ona taki wygld w polu formuy:

{=SUMA(JEELI(B2:B21=C1;1;0)0}

Nie wprowadzajmy jednak sami nawiasw klamrowych jeli to zrobimy Excel

odczyta to jako tekst, a nie formu.

Poniewa wystpuje 20 komrek w zakresie naszego Excela B2:B21, fragment ten

zwraca nam tablice wartoci PRAWDA i FASZ:PRAWDA , gdy komrka zawiera

tekst AUDI, FASZ w przeciwnym wypadku.

Tablica wyglda tak:

{PRAWDA;FASZ;PRAWDA;FASZ;FASZ;FASZ;PRAWDAFASZ;PRAWDA;PRA

WDA;FASZ;FASZ;FASZ;PRAWDA;PRAWDA;FASZ;FASZ;PRAWDA;FASZ;FA

SZ}

Komrka B2 zawiera tekst AUDI, wic zdaje ten test: pierwsza wartoci tablicy

jest PRAWDA. Komrka A4 nie zawiera sowa AUDI, wic nie zdaje nam tego

testu dlatego drug wartoci w tej tablicy bdzie FASZ itp. dla wszystkich 20

komrek.

Popatrzmy na funkcj JEELI(), w ktr jako pierwszy argument jest wliczona

analizowanych przez nas tablica wartoci PRAWDA i FASZ.

Ta funkcja przyjmuje trzy argumenty:

Pierwsza moe mie warto PRAWDA lub FASZ. Tutaj jest to kada

warto w tablicy w zakresie B2:B21=C1.

Drugim argumentem jest warto, ktr chcemy by zwrcia funkcja

JEELI(), w tej tabeli jest to 1

Argumentem trzecim w kolejce do zwrcenia przez funkcj JEELI() jest

warto gdy pierwszym argumentem jest Fasz , tutaj 0.

Funkcja JEELI() bada w naszej tablicy kad warto , aby zobaczy czy jest to

PRAWDA , czy moe FASZ. Gdy warto wystpi jako PRAWDA funkcja jeeli

zwrci nam, a 0 bdzie w przeciwnym wypadku.

JEELI (B2:B21=C1;1;0)

Zwraca tablic jedynek i zer, odpowiadajcej pierwszej tablicy wartoci to jest

PRAWDA i FASZ.

Jedynki odpowiadaj komrk w zakresie B2-B21, ktre zawieraj warto AUDI,

a zera komrkom nie zawierajcym sowo AUDI w tym zakresie. W finale tablica

jedynek i zer jest wrzucana do funkcji SUMA(), ktra dodaje wartoci z naszej

tabeli. Tutaj suma wynosi 8.