Przegląd
- Microsoft Excel jest doskonałym narzędziem do nauki i wykonywania funkcji statystycznych
- Tutaj znajdziesz 12 funkcji statystycznych w Excelu, które powinieneś opanować, aby odnieść sukces w karierze analityka
Zajmijmy się statystyką!
„Statystyka jest gramatyką Nauki.” – Karl Pearson
Uczyńmy to nieco bardziej adekwatnym dla nas – Statystyka jest gramatyką „Data” Science. Zauważysz, że prawie każdy profesjonalista w dziedzinie data science lub analityki ma solidne zrozumienie statystyki – ale czy to oznacza, że potrzebujesz tytułu magistra w tym temacie?
Absolutnie nie!
Podejdziemy do tego artykułu używając słynnej Zasady Pareto – 80% konsekwencji pochodzi z 20% przyczyn. Dlatego też skupimy się głównie na 20% pojęć i funkcji, które przydają się w 80% przypadków! Teraz, jako analityk, zadajemy sobie pytanie, jak zacząć wdrażać statystyki?
Jasne, możesz wdrożyć i przećwiczyć te koncepcje w językach programowania, takich jak Python i R. Ale wstrzymaj się na chwilę i pomyśl – jakie jest najczęściej używane narzędzie w większości organizacji?
To Microsoft Excel! Szczerze mówiąc, Excel to Szwajcarski Nóż Wojskowy dla analityków, który pozwala skupić się na tym, co ważne (w naszym przypadku na statystykach), a sam zajmuje się resztą obliczeń i dostosowań.
Ten artykuł jest dla wszystkich, którzy rozpoczynają swoją podróż do statystyki z wykorzystaniem Excela. Użyjemy 10 kluczowych funkcji statystycznych w Excelu, aby odpowiedzieć na pytania dla firmy sportowej Khelo, podczas przeglądania ich danych.
Jeśli jesteś początkujący w świecie analityki i Excela, gorąco polecam przejście przez te darmowe kursy:
- Wprowadzenie do analityki biznesowej
- Microsoft Excel: Formulas & Functions
Jak podejdziemy do tych 1o funkcji statystycznych w Excelu
Podzieliłem te funkcje statystyczne w Excelu na dwie kategorie:
- Podstawowe funkcje statystyczne
- Pośrednie funkcje statystyczne
Zacznijmy!
Zrozumienie danych i postawienie problemu
Będziemy rozwiązywać kilka kluczowych pytań dotyczących naszej fikcyjnej firmy produkującej sprzęt sportowy i odzież – Khelo. Oto kolumny, które mamy:
- Sprzęt
- Liczba sprzedanych pozycji
- Koszt każdej pozycji
- Dyskonto %
- Dochód
W trakcie tego artykułu odpowiemy na następujące pytania:
- Ile pozycji jest przecenionych?
- Ile przedmiotów/elementów wyposażenia jest sprzedawanych przez sklep?
- Jaka jest liczba produktów sprzedawanych bez rabatu?
- Czy są sprzedawane produkty o koszcie powyżej 2000 i rabacie większym niż 50%?
- Jaka jest średnia liczba sprzedanych produktów?
- Jaka jest mediana liczby sprzedanych produktów?
- Jaki jest najczęstszy procent rabatu?
- Jakie jest odchylenie standardowe liczby sprzedanych produktów?
- Czy istnieje związek między liczbą sprzedanych produktów a procentem rabatu?
Podstawowe funkcje statystyczne w Excelu
MS Excel udostępnia szereg przydatnych funkcji statystycznych. Zacznijmy od kilku podstawowych, ale niezwykle potężnych funkcji. Szczerze mówiąc, przekonasz się, że korzystasz z podstawowych funkcji statystycznych przez 90% czasu, a pozostałe 10% czasu zajmują funkcje pośrednie i zaawansowane.
Będziemy tutaj mówić głównie o różnych rodzajach funkcji zliczania. Są one bardzo podobne do innych funkcji takich jak suma, max, min, średnia.
Funkcja licz
Funkcji licz używamy gdy musimy policzyć ilość komórek zawierających daną liczbę. Pamiętaj TYLKO LICZBY! Zobaczmy jak działa funkcja:
- COUNT(wartość1, , …)
Postarajmy się zatem znaleźć odpowiedź na nasze pierwsze pytanie – Ile przedmiotów było na przecenie?
Na przecenie jest 11 produktów.
Funkcja count
Podczas gdy funkcja count zlicza tylko wartości liczbowe, funkcja COUNTA zlicza wszystkie komórki w zakresie, które nie są puste. Funkcja ta jest przydatna do zliczania komórek zawierających dowolny typ informacji, w tym wartości błędów i pustego tekstu.
- COUNTA(wartość1, , …)
Na drugie pytanie odpowiemy używając funkcji counta, ponieważ jest ona w stanie zliczyć wszystkie niepuste wartości – Ile przedmiotów/elementów wyposażenia sprzedaje sklep?Całkowita liczba przedmiotów sprzedawanych przez sklep wynosi 13.
Countblank
Funkcja COUNTBLANK zlicza liczbę pustych komórek w danym zakresie komórek. Komórki z formułami zwracającymi pusty tekst są również zliczane, ale komórki z wartościami zerowymi nie są zliczane. Jest to świetna funkcja do podsumowywania pustych komórek podczas analizowania dowolnych danych.
- COUNTBLANK(zakres)
Podsumowywanie pustych komórek jest wymogiem dla naszego trzeciego pytania – Jakich produktów nie ma w sekcji przecen? Zastosujmy funkcję!
Na przecenach nie ma tylko 2 produktów.
Funkcja Countifs
Countifs to jedna z najczęściej używanych funkcji statystycznych w Excelu. Funkcja COUNTIFS stosuje jeden lub więcej warunków do komórek w podanym zakresie i zwraca tylko te komórki, które spełniają wszystkie warunki.
- COUNTIFS(kryteria_zakresu1, kryteria1, …)
Uwaga: Każdy nowy zakres musi mieć taką samą liczbę wierszy i kolumn jak argument kryteria_zakresu1. Zakresy nie muszą ze sobą sąsiadować. Funkcja ta wydaje się być idealna do odpowiedzi na czwarte pytanie – Czy są jakieś produkty sprzedawane o koszcie powyżej 2000 wraz z rabatem większym niż 50%? Pytanie wydawało się skomplikowane, ale odpowiedź na nie można było znaleźć w Excelu bardzo łatwo. Tylko 1 produkt, tj. trampki, kosztował więcej niż 2000 i został sprzedany z rabatem większym niż 20%. Cudownie, prawda? Do tej pory przeszliśmy przez kilka podstawowych funkcji statystycznych w MS Excel. Następnie przyjrzyjmy się pośrednim funkcjom statystycznym.
Pośrednie funkcje statystyczne w Excelu
Przedyskutujemy tutaj niektóre z pośrednich funkcji statystycznych w MS Excel związane z tendencją centralną i rozproszeniem. Funkcje te są bardzo przydatne w codziennym życiu analityka.
Funkcja średnia
Najczęściej używaną funkcją w naszym codziennym życiu jest średnia (lub średnia). Funkcja AVERAGE zwraca po prostu średnią arytmetyczną wszystkich komórek w danym zakresie:
- AVERAGE(liczba1, , …)
Ale jest jedna prosta wada używania średnich – są one podatne na wartości odstające. W związku z tym, mogą namalować bardzo nierealistyczny obraz w naszej analizie. Sprawdźmy, jaka jest średnia liczba sprzedanych towarów:rozproszonaŚrednia wychodzi ~ 365,2. Podobne obliczenia będziemy wykonywać również dla kosztów.
Funkcja mediany
Problem wartości odstających można rozwiązać stosując inną funkcję tendencji centralnej – medianę. Funkcja mediany zwraca wartość środkową z podanego zakresu komórek. Składnia jest dość prosta:
- MEDIAN(liczba1, , …)
Znajdźmy medianę liczby sprzedanych towarów w naszym sklepie sportowym i zobaczmy, jak blisko jest ona naszej średniej wartości:
Widzimy, że mediana wychodzi ~ 320, co jest całkiem blisko średniej wartości. Oznacza to, że nie ma dużych wahań w naszych danych. Zobaczmy, czy tak jest w przypadku kosztu towarów:Mediana i średnia wartość dla kosztu każdej pozycji różnią się znacznie. Na przykład koszt piłki to 50, ale koszt kija to 2000 – co powoduje duże rozproszenie.
Funkcja mode
Dla wartości liczbowych, średnia i mediana zazwyczaj wystarczają, ale co z wartościami kategorycznymi? Tutaj z pomocą przychodzi funkcja mode. Funkcja mode zwraca najczęściej występującą i powtarzającą się wartość w podanym zakresie wartości:
- MODE.SNGL(liczba1,,…)
Uwaga: MODE.SNGL zwraca tylko pojedynczą wartość, podczas gdy MODE.MULT zwraca tablicę najczęściej występujących wartości.
Cóż, to jest proste. Znajdźmy najczęstszą wartość rabatu udzielanego przez sklep sportowy:
Ta wartość rabatu wynosi 10%.
Funkcja odchylenia standardowego
Odchylenie standardowe jest jednym ze sposobów ilościowego określenia rozproszenia. Jest to miara tego, jak bardzo wartości są rozproszone od wartości średniej.
W tym miejscu będziemy korzystać z funkcji STDEV.P, która służy do obliczania odchylenia standardowego na podstawie całej populacji podanej jako argumenty:
- STDEV.P(liczba1,,…)
Uwaga: Funkcja STDEV.P zakłada, że jej argumenty są całą populacją. Jeśli tak nie jest, można użyć funkcji STDEV.S().Dla dużej liczebności próby, odchylenie standardowe populacji i próby zwróci w przybliżeniu podobne wartości. Poprzednio obliczyliśmy średnią i medianę, aby uzyskać obraz tendencji centralnej. Sprawdźmy odchylenie standardowe, aby zobaczyć poziom rozproszenia:
Jak można się spodziewać, odchylenie standardowe ilości sprzedanych produktów jest mniejsze, co oznacza, że rozproszenie jest mniejsze, podczas gdy odchylenie standardowe dla kosztu produktów jest wysokie.
Funkcje kwartyli
Jest to kolejna funkcja z licznymi zastosowaniami w przemyśle. Dzięki niej możemy podzielić populację na grupy. QUARTILES.INC zwraca kwartyle zbioru danych, w oparciu o wartości percentyli od 0 do 1 włącznie.
Na przykład, możesz użyć tej funkcji, aby znaleźć górne 25% swojej bazy klientów.
- QUARTILE.INC(array, quart)
Funkcja korelacji
Funkcja CORREL() jest moim osobistym faworytem. Dostarcza ona naprawdę potężnych spostrzeżeń, które nie są oczywiste gołym okiem. Funkcja CORREL zwraca współczynnik korelacji dwóch zakresów komórek. Ale co to jest? W zasadzie mówi nam, jak silna jest zależność między dwiema zmiennymi.
Uwaga: Nie przedstawia żadnego związku przyczynowo-skutkowego.
- CORREL(array1, array2)
Zakres wartości korelacji wynosi od -1 do 1.
Przejdźmy do ostatniego i najbardziej interesującego nas pytania – czy istnieje zależność pomiędzy liczbą sprzedanych towarów a procentem rabatu?
Więc, korelacja wynosi ~0,8, co jest dość wysoką wartością. Wygląda na to, że są one pozytywnie powiązane – czyli większy rabat, większa ilość sprzedanego towaru.
Uwagi końcowe
W tym artykule omówiliśmy ponad 10 początkujących i średnio zaawansowanych funkcji statystycznych w MS Excel, począwszy od prostej funkcji count() do zaawansowanej correl(). Statystyka jest jednym z najważniejszych narzędzi w zestawie analityka i możesz osiągnąć wiele swoich celów statystycznych po prostu używając Excela.
Zalecam Ci przejrzenie następujących dodatkowych zasobów Excela:
- 3 Ambitne wykresy Excela, aby zwiększyć swoje portfolio analityczne i wizualizacyjne
- 5 Przydatnych sztuczek Excela, aby stać się efektywnym analitykiem
W przyszłości będę omawiał zaawansowane funkcje statystyczne. Daj mi znać niektóre z Twoich ulubionych funkcji statystycznych, a ja postaram się uwzględnić je w moich nadchodzących artykułach.
Możesz również przeczytać ten artykuł na naszej aplikacji mobilnej