www.cognity.pl
Analiza statystyczna w MS Excel - Otrzymywanie wartości modalnej kategorii za pomocą formuły
Wiemy, że funkcja WYST.NAJCZĘŚCIEJ() w Excelu nie będzie działała, gdy jako jej argument podamy wartości tekstowe. Poniżej przedstawiamy jak możemy otrzymać wartość modalną za pomocą arkusza. Jest to temat w całości omawiany podczas kursu Excel w Analizie Statystycznej. Pokazuje nam ona, która wartość tekstowa może najczęściej występować w interesującym nas zbiorze danych. Żeby nie musieć prowadzić odwołania do wykresu przestawnego i otrzymać wartość modalną jednej grupy wartości tekstowych, możemy wyczytać ich wartość za pomocą formuły: =INDEKS(B2:B21;WYST.NAJCZĘSCIEJ(PODAJ.POZYCJĘ(B2:B21;B2:B21;0))) Zakładamy że wartości tekstowe są w komórkach B2:B21. W skrócie składniki formuły działają tak: PODAJ POZYCJĘ() oddaje nam pozycję w tablicy wartości, gdzie po raz
pierwszy może wystąpić każda poszczególna wartość Argument trzeci w funkcji PODAJ.POZYCJĘ(), ukazuje Excelowi, że ten przypadek wymaga dokładnego dopasowania, a tablica nie może ulec sortowaniu. Na przykład dla każdego wystąpienia nazwy AUDI w tablicy funkcja PODAJ POZYCJĘ() zwraca nam 1, a dla każdego wystąpienia wyrazu RENAULT zwraca nam 2, a gdy występuje HONDA zwraca nam 4 Jako argument funkcji WYST.NAJCZĘŚCIEJ służą wyniki funkcji PODAJ
POZYCJĘ() Wynik z funkcji WYST.NAJCZĘŚCIEJ () Jest drugim argumentem funkcji
INDEKS. Argumentem pierwszym jest badana przez nas tablica. Drugi argument określa nam jak daleko szukać mamy w tablicy. Excel szuka w tablicy pierwszej wartości , która jest tu AUDI. Jeżeli HONDA była wartością występującą wartością tekstową funkcji znaną nam jako WYST.NAJCZĘŚCIEJ() zwróciłaby nam 4, a INDEKS () posłużyłby się tą wartością do znalezienia HONDA w tablicy.
www.cognity.pl
Zliczanie wartości za pomocą formuły tablicowej Z wartością modalną (tutaj dla nas jest nią RENAULT) w garści wciąż chcemy wiedzieć , ile razy ta wartość występuje. Oto licznik następującej formuły: =SUMA(JEŻELI (B2:B21=C1;1:0)) To formuła tablicowa i musi ją potwierdzić specjalną sekwencją przycisków CTRL+SHIFT+ENTER. Widzimy, że formuła została wprowadzona jako formuła tablicy, jeżeli zobaczymy nawiasy klamrowe jakie występują wokół niej w polu formuły. Gdybyśmy chcieli wprowadzić jako tablicową poprzednia formułę miała by ona taki wygląd w polu formuły: {=SUMA(JEŻELI(B2:B21=C1;1;0)0} Nie wprowadzajmy jednak sami nawiasów klamrowych jeśli to zrobimy Excel odczyta to jako tekst, a nie formułę. Ponieważ występuje 20 komórek w zakresie naszego Excela B2:B21, fragment ten zwraca nam tablice wartości PRAWDA i FAŁSZ:PRAWDA , gdy komórka zawiera tekst AUDI, FAŁSZ w przeciwnym wypadku. Tablica wygląda tak: {PRAWDA;FAŁSZ;PRAWDA;FAŁSZ;FAŁSZ;FAŁSZ;PRAWDAFAŁSZ;PRAWDA;PRA WDA;FAŁSZ;FAŁSZ;FAŁSZ;PRAWDA;PRAWDA;FAŁSZ;FAŁSZ;PRAWDA;FAŁSZ;FA ŁSZ} Komórka B2 zawiera tekst AUDI, więc zdaje ten test: pierwsza wartością tablicy jest PRAWDA. Komórka A4 nie zawiera słowa AUDI, więc nie zdaje nam tego testu dlatego drugą wartością w tej tablicy będzie FAŁSZ – itp. dla wszystkich 20 komórek. Popatrzmy na funkcję JEŻELI(), w którą jako pierwszy argument jest wliczona analizowanych przez nas tablica wartości PRAWDA i FAŁSZ.
www.cognity.pl
Ta funkcja przyjmuje trzy argumenty: Pierwsza może mieć wartość PRAWDA lub FAŁSZ. Tutaj jest to każda
wartość w tablicy w zakresie B2:B21=C1. Drugim argumentem jest wartość, którą chcemy by zwróciła funkcja
JEŻELI(), w tej tabeli jest to 1 Argumentem trzecim w kolejce do zwrócenia przez funkcję JEŻELI() jest
wartość gdy pierwszym argumentem jest Fałsz , tutaj 0. Funkcja JEŻELI() bada w naszej tablicy każdą wartość , aby zobaczyć czy jest to PRAWDA , czy może FAŁSZ. Gdy wartość wystąpi jako PRAWDA funkcja jeżeli zwróci nam, a 0 będzie w przeciwnym wypadku. JEŻELI (B2:B21=C1;1;0) Zwraca tablicę jedynek i zer, odpowiadającej pierwszej tablicy wartości to jest PRAWDA i FAŁSZ. Jedynki odpowiadają komórką w zakresie B2-B21, które zawierają wartość AUDI, a zera komórkom nie zawierającym słowo AUDI w tym zakresie. W finale tablica jedynek i zer jest wrzucana do funkcji SUMA(), która dodaje wartości z naszej tabeli. Tutaj suma wynosi 8.