Jak znormalizować dane w Excelu?

Wyobraź sobie dwóch sprzedawców kwiatów, którzy hodują róże w swoim ogrodzie różanym. Chcą się dowiedzieć, kto jest lepszy w zdobywaniu większej ilości róż w swoim ogrodzie. Jeden z nich, Jack, mówi: "Mam tylko 5 gałęzi róż, ale w tym roku zebrałem 30 róż!", a druga Mary odpowiada: "Nie jesteś w tym dobra, w tym miesiącu zebrałam 100 róż!".

Jeśli po prostu porównasz 80 róż Jacka i 100 róż Mary, to rzeczywiście wydaje się, że Mary jest lepszą ogrodniczką. Ale pomyśl tak: co by było, gdyby Mary musiała posadzić 25 gałązek róż, aby uzyskać takie zbiory?

Wystarczy "znormalizować" dane, aby uzyskać bardziej sprawiedliwy sposób porównywania tych dwojga. Zamiast liczyć całkowitą liczbę róż, porównajmy obu ogrodników za pomocą sprawiedliwego standardu - ile róż każdy z nich wyhodował na jednej gałęzi?

Jack wyhodował 30 róż na 5 gałęziach. 30/5 = 6 róż na gałąź.

Mary wyhodowała 100 róż na 25 gałęziach. 100/25 = 4 róże na gałąź.

Po znormalizowaniu danych widać wyraźnie, że ogrodnik Jack jest o wiele lepszy!

Co to są dane znormalizowane?

Dane znormalizowane to dane przekształcone tak, aby mieściły się w pewnym przedziale, który zazwyczaj jest prostszy. Załóżmy, że mamy listę wartości z zakresu od y do z.

Chcemy uprościć tę listę poprzez nadanie jej innego zakresu, np. od a do b.

Aby ustalić wzór, powiedzmy, że lista zaczyna się od liczby X.

X: pierwsza wartość pojawiająca się na liście

Wzór na normalizację wartości X to;

Po ustaleniu formuły dla pierwszej wartości X, możemy ją powielić dla pozostałych komórek, aby znormalizować wszystkie wartości na liście. W ten sposób, na końcu, otrzymujemy prostszy zakres danych, który jest łatwiejszy do odczytania i zrozumienia.

Tak właśnie będzie wyglądała lista po wykonaniu obliczeń normalizacyjnych:

Jaka jest różnica między normalizacją a standaryzacją?

Normalizacja i standaryzacja to pojęcia, które często są ze sobą mylone.

Możemy znormalizować listę wartości za pomocą powyższego obliczenia, jednak jeśli chcemy znormalizować wartości, Excel posiada również własną formułę do jej obliczania, zwaną Normalizuj.

Podczas gdy normalizacja przekształca oryginalne wartości tak, aby mieściły się w pewnym zakresie, normalizacja przekształca je tak, aby mieściły się w rozkładzie, który ma średnią równą 0 i odchylenie standardowe równe 1. Ta operacja jest również nazywana uzyskiwaniem współczynników Z lub wyśrodkowaniem średniej:

Jak znormalizować dane w Excelu?

Obliczanie normalizacji jest dość proste. Powiedzmy, że znowu mamy listę wartości z zakresu od y do z i zaczyna się od liczby X.

X: pierwsza wartość pojawiająca się na liście

Wzór na standaryzację wartości X to;

X_standaryzowany = (X - średnia zakresu) / odchylenie standardowe zakresu

Formuła Excela do tego obliczenia to:

=STANDARDIZE(X; średnia z przedziału; odchylenie standardowe z przedziału)

Oczywiście, aby napisać tę formułę, musimy również znać wzór obliczania średniej i wzór obliczania odchylenia standardowego.

Formuła obliczania średniej: =AVERAGE(zakres wartości)

Wzór na obliczenie odchylenia standardowego: =STDEV(zakres wartości)

Zanurzmy się w tych formułach na poniższym przykładzie w naszym arkuszu kalkulacyjnym:

Krok 1: Znajdź średnią:

Po pierwsze, musisz obliczyć średnią zestawu danych. W tym celu użyjmy formuły =AVERAGE(zakres wartości).

Krok 2: Znajdź odchylenie standardowe:

Teraz pozwól Excelowi obliczyć dla Ciebie odchylenie standardowe. Zapisz =STDEV(zakres wartości) przed normalizacją zestawu danych.

Krok 3: Znormalizuj wartości:

Skoro mamy już wszystko, czego potrzebujemy, łatwo jest znormalizować dane za pomocą formuły: =STANDARDIZE (X, średnia zakresu, odchylenie standardowe zakresu)

Metody używane do normalizacji i standaryzacji danych:

Normalizacja danych jest ogólnie używana na 2 sposoby:

1) W celu uczynienia zakresu danych łatwiejszym do zrozumienia i oceny:

Na przykład; mamy listę wyników z matematyki 10 studentów. Wyniki wahają się od 0 do 100, ale chcemy, aby wahały się od 0 do 1, aby łatwiej było je ocenić. Kiedy dokonamy obliczeń, otrzymamy wyniki jak poniżej:

Dzięki normalizacji możemy wywnioskować, że bardziej skutecznymi studentami są Jason i Mike.

Aby sprawdzić poprawność obliczeń, możemy zrobić wykresy i zobaczyć, że wykresy liniowe obu kolumn mają te same tendencje (ale różne zakresy).

2) W celu porównania więcej niż jednego zestawu danych z różnymi zakresami:

Na przykład; mamy listę wyników egzaminu z matematyki i egzaminu z fizyki i chcemy porównać, kto jest bardziej udany na czym. Jednakże; egzamin z matematyki był oceniany na 100 punktów, a egzamin z fizyki na 50. Ponieważ zakresy są różne, ocena z oryginalnych wartości liczbowych może być myląca. Kiedy znormalizujemy wyniki, otrzymamy:

Teraz widzimy wszystkie wyniki tak, jakby egzaminy były oceniane na 1.

Kiedy tworzymy wykresy zarówno z oryginalnych wyników jak i znormalizowanych wyników, widzimy, że oryginalne dane są mylące, ponieważ, na przykład, Jason wygląda jakby był lepszy z matematyki, jednak w rzeczywistości jest lepszy z fizyki. Również sukces Harveya na obu egzaminach jest podobny, jednak w rzeczywistości jest on o wiele lepszy z fizyki niż z matematyki.

Standaryzacja może być używana na dwa różne sposoby:

1) W celu uproszczenia różnych zmiennych o średniej równej 0 i odchyleniu standardowym równym 1:

Na przykład; mamy portfel 8 kont giełdowych ze średnią 23,5 i odchyleniem standardowym 22,1. Kiedy znormalizujemy wartości, jest to znacznie wygodniejsze do odczytania i oceny:

Standaryzacja mówi nam o odchyleniu standardowym wartości od średniej. Jeśli wartość ma ujemną wartość znormalizowaną, oznacza to, że jej wartość jest mniejsza niż średnia. I odwrotnie, jeśli wartość ma dodatnią wartość znormalizowaną, oznacza to, że jej wartość jest większa niż średnia.

Na przykład: tutaj Walmart ma 0,610 odchylenia standardowego poniżej średniej (ponieważ ma minus), a Apple ma 1,513 odchylenia standardowego powyżej (ponieważ jest liczbą dodatnią).

Zalecana lektura: Analiza SWOT firmy Apple

2) Aby ułatwić ocenę fluktuacji różnych zestawów danych, jeśli mają one różne średnie i / lub odchylenie standardowe:

Załóżmy, że mamy miesięczne wartości 2 różnych kont giełdowych. Kiedy znormalizujemy te dane, otrzymamy;

Dlaczego należy normalizować zmienne?

Normalizacja i standaryzacja są świetnymi metodami, szczególnie gdy mamy do czynienia z dużą liczbą danych lub danymi o różnych skalach, ponieważ pomagają one sprawić, że duże dane kurczą się do mniejszych rozmiarów i ułatwiają wnioskowanie i prawidłowe wykorzystanie danych.

Jeśli analiza danych, analiza skupisk lub modelowanie statystyczne jest częścią twojej pracy, te dwie główne techniki wstępnego przetwarzania danych mogą pomóc ci w tworzeniu modeli predykcyjnych i wykrywaniu relacji i korelacji pomiędzy zestawami danych, jeśli takie istnieją.

Słowa końcowe

Podsumowując, czy chcesz zrobić prezentację dla swojej firmy na konkretny temat, czy chcesz zmniejszyć rozmiar danych, z którymi masz do czynienia, aby dokonać wyraźnych potrąceń dla siebie, rozważ użycie funkcji normalizacji lub standaryzacji w programie Excel.