Jak korzystać z funkcji CZĘSTOTLIWOŚĆ w programie Excel?

Funkcja CZĘSTOTLIWOŚĆ programu Excel pozwala policzyć, ile razy wartości mieszczą się w określonych zakresach. Jeśli na przykład masz w arkuszu kalkulacyjnym wiek grupy osób, możesz obliczyć, ile osób należy do różnych przedziałów wiekowych. Przyjrzyjmy się, jak obliczyć rozkłady częstotliwości i, z niewielką modyfikacją, procenty częstotliwości.

Co robi funkcja CZĘSTOTLIWOŚĆ?

Funkcja tablicowa CZĘSTOTLIWOŚĆ programu Excel pozwala obliczyć rozkład częstotliwości zestawu danych. Podajesz numeryczny zestaw danych (czyli rzeczywiste komórki używane jako źródło), listę progów pojemników (czyli kategorie, według których sortujesz dane), a następnie naciśnij klawisze Ctrl+Shift+Enter.

Jak więc możesz go użyć? Cóż, oto krótki przykład. Załóżmy, że jesteś nauczycielem i masz arkusz kalkulacyjny, który pokazuje wszystkie wyniki testów liczbowych Twojego ucznia. Możesz użyć funkcji CZĘSTOTLIWOŚĆ, aby obliczyć, ilu uczniów otrzymało ocenę A, B, C, D lub F. Wyniki testu liczbowego to zbiór danych, a oceny literowe tworzą progi przedziału.

Należy zastosować funkcję CZĘSTOTLIWOŚĆ do listy wyników testów ucznia, a funkcja zliczy, ilu uczniów otrzymało daną ocenę literową, porównując każdy wynik testu z zakresem wartości, które definiują różne oceny literowe.

Jeśli zaokrąglisz wyniki do najbliższej dziesiątej części procenta, zastosowanie będą miały następujące zakresy:

K <= 59,9 < D <= 69,9 < C <= 79,9 < B <= 89,9 < A

Excel przypisałby wynik 79,9 do zakresu C, podczas gdy wynik 98,2 należałby do zakresu A. Excel przeglądałby listę wyników testów, kategoryzował każdy wynik, zliczał całkowitą liczbę wyników mieszczących się w każdym zakresie i zwracał tablicę z pięcioma komórkami pokazującymi całkowitą liczbę wyników w każdym zakresie.

Funkcja CZĘSTOTLIWOŚĆ wymaga dwóch tablic jako danych wejściowych: „tablicy_danych” i „tablicy_bin”. Tablice to po prostu listy wartości. "Data_array" musi zawierać wartości - takie jak oceny liczbowe dla uczniów - które program Excel może porównać z serią progów zdefiniowanych w "Bins_array" - podobnie jak oceny literowe w tym samym przykładzie.

Spójrzmy na przykład

W naszym przykładzie obliczymy rozkład częstości i procenty częstości zbioru 18 liczb z przedziału od 0 do 10. To tylko proste ćwiczenie, w którym określimy, ile z tych liczb mieści się w przedziale od 1 do 2, od 2 do 2 trzy i tak dalej.

W naszym prostym przykładowym arkuszu kalkulacyjnym mamy dwie kolumny: tablica_danych i tablica_bin.

Kolumna „Data_array” zawiera liczby, a kolumna „Bins_array” zawiera progi pojemników, których będziemy używać. Zauważ, że zostawiliśmy pustą komórkę u góry kolumny „Bins_array”, aby uwzględnić liczbę wartości w tablicy wyników, która zawsze będzie zawierać o jedną wartość więcej niż „Bins_array”.

Zamierzamy również utworzyć trzecią kolumnę, w której mogą znajdować się nasze wyniki; nazywamy go „Wyniki”.

Najpierw wybierz komórki, w których chcesz umieścić wyniki. Teraz przejdź do menu „Formuły” i kliknij przycisk „Więcej funkcji”. W menu rozwijanym wskaż podmenu "Statystyczne", przewiń nieco w dół, a następnie kliknij funkcję "CZĘSTOTLIWOŚĆ".

Pojawi się okno Argumenty funkcji. Kliknij pole "Tablica_danych", a następnie zaznacz komórki w kolumnie "Tablica_danych" (możesz również wpisać numery komórek, jeśli wolisz).

Jeśli otrzymasz komunikat o błędzie informujący, że nie możesz edytować tylko części tablicy, oznacza to, że nie zaznaczyłeś wszystkich komórek tablicy. Kliknij "OK", a następnie naciśnij klawisz Esc.

Aby edytować formułę tablicy lub usunąć tablicę, należy najpierw podświetlić wszystkie komórki tablicy.

Teraz kliknij pole „Bins_array”, a następnie wybierz wypełnione komórki w kolumnie „Bins_array”.

Kliknij przycisk „OK”.

Zobaczysz, że tylko pierwsza komórka kolumny „Wyniki” ma wartość, pozostałe są puste.

Aby zobaczyć inne wartości, kliknij wewnątrz paska "Formuła", a następnie naciśnij Ctrl + Shift + Enter.

W kolumnie Wyniki będą teraz wyświetlane brakujące wartości.

Widać, że program Excel znalazł cztery wartości, które były mniejsze lub równe jeden (podświetlone na czerwono), a także znalazł liczebności każdego z naszych innych zakresów liczbowych. Do naszego arkusza kalkulacyjnego dodaliśmy kolumnę „Opis wyniku”, dzięki czemu możemy wyjaśnić logikę, której program Excel używa do obliczania każdego wyniku.

Jak obliczyć procenty częstotliwości

To wszystko dobrze i dobrze, ale co jeśli zamiast surowych liczebności w wynikach chcielibyśmy zamiast tego zobaczyć procenty. Na przykład, jaki procent naszych liczb spadł między jednym a dwoma.

Aby obliczyć procenty częstotliwości każdego przedziału, możemy zmienić formułę tablicową za pomocą paska funkcji programu Excel. Zaznacz wszystkie komórki w kolumnie „Wyniki”, a następnie dodaj następujące elementy na końcu formuły na pasku funkcji:

 /LICZBA(B3:B20)

Ostateczna formuła powinna wyglądać tak:

 =CZĘSTOTLIWOŚĆ(B3:B20;C3:C20)/LICZBA(B3:B20)

Teraz ponownie naciśnij Ctrl+Shift+Enter.

Nowa formuła dzieli każdy element tablicy Results przez łączną liczbę wartości w kolumnie „Data_array”.

Wyniki nie są automatycznie formatowane jako wartości procentowe, ale łatwo to zmienić. Przejdź do menu „Home”, a następnie naciśnij przycisk „%”.

Wartości będą teraz wyświetlane w procentach. Na przykład teraz widać, że 17% liczb w kolumnie „Data_array” mieści się w zakresie 1-2.

Co najlepsze, teraz, gdy formuła znajduje się w kolumnie „Wyniki”, możesz zmienić dowolne wartości w kolumnach „Tablica_danych” i „Tablica_bin”, a program Excel automatycznie odświeży wyniki zaktualizowanymi wartościami.

Omijanie menu formuł i korzystanie z paska funkcji

Jeśli wolisz pisać i znać sposób nazewnictwa kolumn i komórek, zawsze możesz ominąć przekopywanie się przez menu „Formuły”, po prostu wpisując funkcje bezpośrednio na pasku funkcji programu Excel, a następnie naciskając Ctrl + Shift + Enter.

Aby obliczyć rozkład częstotliwości, użyj następującej składni:

 {=CZĘSTOTLIWOŚĆ( tablica_danych , tablica_bins )}

Aby obliczyć procenty częstotliwości, użyj zamiast tego tej składni:

 {=CZĘSTOTLIWOŚĆ( tablica_danych , tablica_bin )/LICZBA( tablica_danych )}

Pamiętaj tylko, że jest to formuła tablicowa, więc musisz nacisnąć Ctrl + Shift + Enter zamiast tylko Enter. Obecność {nawiasów klamrowych} wokół formuły wskazuje, że została ona wprowadzona jako formuła tablicowa.