Funkcja CZĘSTOŚĆ i grupowanie cen (lub innych wartości) w przedziały

by Marcin

Dzisiaj będzie o statystykach sprzedaży oraz o funkcji CZĘSTOŚĆ.

Mam długą listę sprzedaży produktów określonego rodzaju. Za każdym razem, kiedy następuje sprzedaż dodawany jest nowy wiersz do tabeli Excela. W każdym wierszu (poza innymi informacjami) znajduje się nazwa produktu oraz cena sprzedaży. W wielkim uproszczeniu wygląda to mniej więcej tak, jak na rysunku.

Funkcja Częstość i grupowanie w przedziały

Postanowiłem sprawdzić popularność różnych grup produktów, ale grupując produkty według cen sprzedaży (w przedziały „od … do …”). Chciałem się dowiedzieć czy lepiej sprzedają się produkty tanie, drogie, czy też jest to bez znaczenia.

Idealna do rozwiązania tego typu problemu wydała mi się funkcja CZĘSTOŚĆ.

W uproszczeniu można powiedzieć, że funkcja oblicza jak często określona wartość występuje na liście. Gdyby jednak kończyło się tylko na tym, nie różniłaby się ona np. od funkcji LICZ.JEŻELI, która może zrobić to samo. Cała magia funkcji CZĘSTOŚĆ polega na tym, że potrafi ona określić częstość występowania w ramach przedziałów, które sami definiujemy. Jeżeli brzmi to jeszcze tajemniczo, na pewno zrozumiesz wszystko analizując przykład opisany niżej. Najpierw jednak kilka słów o funkcji CZĘSTOŚĆ.

Przede wszystkim jest to funkcja nieco nietypowa. W odróżnieniu od innych funkcji musi zostać wprowadzona jako formuła tablicowa. Jako wynik zwraca także tablicę (pionową) liczb, dlatego też stosujemy ją na zakresie komórek.

Składnia funkcji wygląda następująco:

CZĘSTOŚĆ (dane; przedziały)

Jako pierwszy parametr podajemy zakres z danymi, które chcemy policzyć. W moim przypadku jest to kolumna z cenami, które chcę jednak pogrupować w przedziały. Tu przychodzi z pomocą drugi parametr – zakres komórek z interesującymi nas przedziałami (określonymi przez podanie górnych granic przedziałów).

Mnie interesowały następujące przedziały cenowe:

Produkty o cenie:
do 100 zł
101-150 zł
151 – 200 zł
201 – 300 zł
301-400 zł
401-500 zł
501 – 1000 zł
W związku z tym mój zakres z przedziałami wygląda następująco.

Funkcja Częstość i grupowanie w przedziały

Musisz pamiętać, że funkcja CZĘSTOŚĆ automatycznie utworzy jeden dodatkowy wiersz, w którym znajdzie się liczba wszystkich cen (lub innych wartości) niemieszczących się w żadnym z podanych przeze mnie przedziałów. W moim przypadku będą to ceny większe od 1000 zł.

Pomimo więc, że zakres z granicami przedziałów to tylko 7 komórek, w swoim skoroszycie zaznaczyłem 8 komórek (w układzie pionowym) i wpisałem formułę tablicową:

=CZĘSTOŚĆ(ceny; E5:E11)

gdzie ceny to nazwany, dynamiczny zakres z cenami sprzedanych produktów (kolumna C na pierwszym rysunku), a E5:E11 to zakres siedmiu komórek definiujących moje przedziały (na rysunku poniżej jest to zakres z 8 komórkami określonymi jako „Sprzedana ilość”).

W rezultacie (po odpowiednim sformatowaniu i dodaniu kolumny z opisem przedziałów) otrzymałem tabelkę widoczną na rysunku.

Funkcja Częstość i grupowanie w przedziały

Jak widać, najpopularniejsze są produkty, których cena jednostkowa mieści się w granicach 201-300 zł. Druga popularna grupa to te w cenach 401-500 zł.

Dla porządku dodam jeszcze, że jeżeli masz zainstalowany dodatek Analysis ToolPak (wchodzący w skład Excela 2007) nie musisz wcale wpisywać funkcji CZĘSTOŚĆ ręcznie. Możesz skorzystać z narzędzia Histogram (w menu Narzędzia, wybierz Analiza danych i następnie Histogram)

{ 3 comments… read them below or add one }

methaforR

Fajne i uzyteczne. Dzieki za podzielenie sie wiedza 🙂

Blebleble

robie DOKŁADNIE tak jak tutaj autor zrobil, mam zbiór 365 danych , ale wyskakują jakieś kocopoły bo skoro juz 218 na 365 danych jest zajętych to jak następny przedział może mieć 277…

ale notatka na dole o HISTOGRAMIE mnie uratowała, powiem wam że to działa, i dzięki za ratunek ! 😀

mw

czy jest możliwość wrzucenia tutaj pliku excela z przykładem? Będę bardzo wdzięczny 🙂

Leave a Comment

Previous post:

Next post: