Analiza korelacji w Excelu. Przykład wykonania analizy korelacji

Analiza korelacji jest powszechną metodą badawczą stosowaną do określenia stopnia zależności 1. wartości od 2. wartości. Arkusz kalkulacyjny posiada specjalne narzędzie, które pozwala na realizację tego typu badań.

Istota analizy korelacji

Konieczne jest określenie relacji między dwiema różnymi wielkościami. Innymi słowy, pokazuje w jakim kierunku (mniejszy/większy) zmienia się wartość w zależności od zmian w drugim.

Cel analizy korelacji

Zależność ustala się, gdy rozpoczyna się identyfikacja współczynnika korelacji. Metoda ta różni się od analizy regresji tym, że istnieje tylko jeden wskaźnik obliczany za pomocą korelacji. Interwał zmienia się z +1 na -1. Jeśli jest dodatnia, to wzrost pierwszej wartości przyczynia się do wzrostu drugiej. Jeśli jest ujemna, wzrost pierwszej wartości przyczynia się do spadku drugiej. Im wyższy współczynnik, tym silniejsza jedna wartość wpływa na drugą.

Ważne! Przy zerowym współczynniku nie ma związku między wielkościami.

Obliczanie współczynnika korelacji

Przeanalizujmy obliczenia na kilku próbkach. Na przykład istnieją dane tabelaryczne, w których wydatki na promocję reklam i wielkość sprzedaży opisane są miesiącami w osobnych kolumnach. Na podstawie tabeli dowiemy się, jaka jest zależność wielkości sprzedaży od pieniędzy wydanych na promocję reklamową.

Metoda 1: Określanie korelacji za pomocą kreatora funkcji

CORREL – funkcja pozwalająca na zaimplementowanie analizy korelacji. Formularz ogólny – WSPÓŁPRACA(masa1;masa2). Szczegółowe instrukcje:

1. Konieczne jest wybranie komórki, w której planowane jest wyświetlenie wyniku obliczeń. Kliknij „Wstaw funkcję” po lewej stronie pola tekstowego, aby wprowadzić formułę.

2. Otworzy się Kreator funkcji. Tutaj musisz znaleźć WSPÓŁPRACA, kliknij na niego, a następnie na „OK”.

3. Otworzy się okno argumentów. W wierszu „Array1” należy wprowadzić współrzędne interwałów pierwszej z wartości. W tym przykładzie jest to kolumna Wartość sprzedaży. Musisz tylko zaznaczyć wszystkie komórki znajdujące się w tej kolumnie. Podobnie musisz dodać współrzędne drugiej kolumny do wiersza „Array1”. W naszym przykładzie jest to kolumna Koszty reklamy.

4. Po wprowadzeniu wszystkich zakresów kliknij przycisk „OK”.

Współczynnik był wyświetlany w komórce wskazanej na początku naszych działań. Otrzymany wynik to 0,97. Wskaźnik ten odzwierciedla wysoką zależność pierwszej wartości od drugiej.

Metoda 2: Oblicz korelację za pomocą pakietu Analysis ToolPak

Istnieje inna metoda określania korelacji. Tutaj używana jest jedna z funkcji znajdujących się w pakiecie analitycznym. Przed użyciem musisz aktywować narzędzie. Szczegółowe instrukcje:

1. Przejdź do sekcji „Plik”.

2. Otworzy się nowe okno, w którym należy kliknąć sekcję „Ustawienia”.
3. Kliknij „Dodatki”.
4. Na dole znajduje się element „Zarządzanie”. Tutaj musisz wybrać „Dodatki programu Excel” z menu kontekstowego i kliknąć „OK”.

5. Otworzyło się specjalne okno dodatków. Umieść znacznik wyboru obok elementu „Pakiet analizy”. Klikamy „OK”.
6. Aktywacja powiodła się. Przejdźmy teraz do Danych. Pojawił się blok „Analiza”, w którym należy kliknąć „Analiza danych”.
7. W nowym oknie, które się pojawi, wybierz element „Korelacja” i kliknij „OK”.

8. Na ekranie pojawiło się okno ustawień analizy. W wierszu „Interwał wejściowy” należy wpisać zakres absolutnie wszystkich kolumn biorących udział w analizie. W tym przykładzie są to kolumny „Wartość sprzedaży” i „Koszty reklamy”. Ustawienia wyświetlania wyników są początkowo ustawione na Nowy arkusz, co oznacza, że ​​wyniki będą wyświetlane w innym arkuszu. Opcjonalnie możesz zmienić lokalizację wyjściową wyniku. Po dokonaniu wszystkich ustawień kliknij „OK”.

Ostateczne wyniki są już dostępne. Wynik jest taki sam jak w pierwszej metodzie – 0,97.

Definicja i obliczanie współczynnika korelacji wielokrotnej w MS Excel

Aby określić poziom zależności kilku wielkości, stosuje się wiele współczynników. W przyszłości wyniki będą podsumowywane w osobnej tabeli zwanej macierzą korelacji.

Szczegółowy przewodnik:

1. W sekcji „Dane” znajdujemy znany już blok „Analiza” i klikamy „Analiza danych”.

2. W wyświetlonym oknie kliknij element „Korelacja” i kliknij „OK”.
3. W wierszu „Interwał wejściowy” wjeżdżamy w interwał dla trzech lub więcej kolumn tabeli źródłowej. Zakres można wprowadzić ręcznie lub po prostu wybrać go LPM, a automatycznie pojawi się w żądanym wierszu. W „Grupowanie” wybierz odpowiednią metodę grupowania. W „Parametr wyjściowy” określa lokalizację, w której będą wyświetlane wyniki korelacji. Klikamy „OK”.

4. Gotowy! Zbudowano macierz korelacji.

Współczynnik korelacji par w programie Excel

Zastanówmy się, jak poprawnie narysować współczynnik korelacji par w arkuszu kalkulacyjnym Excel.

Obliczanie współczynnika korelacji par w Excelu

Na przykład masz wartości x i y.

X jest zmienną zależną, a y jest niezależną. Konieczne jest znalezienie kierunku i siły związku między tymi wskaźnikami. Instrukcja krok po kroku:

1. Znajdźmy wartości średnie za pomocą funkcji SERCE.

2. Policzmy każdy х и xśr, у и Średnia za pomocą operatora «-».

3. Obliczone różnice mnożymy.

4. Obliczamy sumę wskaźników w tej kolumnie. Licznik to znaleziony wynik.

5. Oblicz mianowniki różnicy х и x-średnia, y и y-średnie. Aby to zrobić, wykonamy kwadraturę.

6. Korzystanie z funkcji AUTOSUMA, znajdź wskaźniki w wynikowych kolumnach. Robimy mnożenie. Korzystanie z funkcji ROOT podnieś wynik do kwadratu.

7. Obliczamy iloraz używając wartości mianownika i licznika.

8. CORREL to zintegrowana funkcja, która pozwala zapobiec skomplikowanym obliczeniom. Przechodzimy do „Kreatora funkcji”, wybieramy CORREL i określamy tablice wskaźników х и у. Budujemy wykres, który wyświetla uzyskane wartości.

Macierz współczynników korelacji parami w programie Excel

Przeanalizujmy, jak obliczyć współczynniki sparowanych macierzy. Na przykład istnieje macierz czterech zmiennych.

Instrukcja krok po kroku:

1. Przechodzimy do „Analiza danych”, znajdującego się w bloku „Analiza” w zakładce „Dane”. Z wyświetlonej listy wybierz opcję Korelacja.
2. Ustawiamy wszystkie niezbędne ustawienia. „Interwał wejściowy” – interwał wszystkich czterech kolumn. „Interwał wyjściowy” – miejsce, w którym chcemy wyświetlić sumy. Klikamy przycisk „OK”.
3. W wybranym miejscu zbudowano macierz korelacji. Każde przecięcie wiersza i kolumny to współczynnik korelacji. Numer 1 jest wyświetlany, gdy współrzędne się zgadzają.

Funkcja CORREL do określania relacji i korelacji w programie Excel

CORREL – funkcja służąca do obliczania współczynnika korelacji między 2 tablicami. Spójrzmy na cztery przykłady wszystkich możliwości tej funkcji.

Przykłady użycia funkcji CORREL w Excelu

Pierwszy przykład. Znajduje się tam tabliczka z informacjami o średnich zarobkach pracowników firmy na przestrzeni jedenastu lat oraz kursie $. Konieczne jest zidentyfikowanie związku między tymi dwiema wielkościami. Tabela wygląda tak:

Algorytm obliczeniowy wygląda tak:

Wyświetlany wynik jest zbliżony do 1. Wynik:

Wyznaczenie współczynnika korelacji wpływu działań na wynik

Drugi przykład. Dwóch oferentów zwróciło się do dwóch różnych agencji o pomoc w piętnastodniowej promocji. Codziennie przeprowadzano sondaż społeczny, który określał stopień poparcia dla każdego wnioskodawcy. Każdy rozmówca mógł wybrać jednego z dwóch kandydatów lub sprzeciwić się wszystkim. Należy określić, w jakim stopniu każda promocja reklamowa wpłynęła na stopień wsparcia aplikantów, która firma jest bardziej efektywna.

Korzystając z poniższych wzorów, obliczamy współczynnik korelacji:

• =WSPÓŁPRACA(A3:A17;B3:B17).
• =WSPÓŁPRACA(A3:A17;C3:C17).

Wyniki:

Z uzyskanych wyników jasno wynika, że ​​stopień poparcia dla pierwszego wnioskodawcy rósł z każdym dniem promocji reklamy, dlatego współczynnik korelacji zbliża się do 1. Kiedy reklama została uruchomiona, drugi wnioskodawca cieszył się dużym zaufaniem, a dla 1 dni był pozytywny trend. Potem stopień zaufania spadł i piętnastego dnia spadł poniżej początkowych wskaźników. Niskie wyniki sugerują, że promocja negatywnie wpłynęła na wsparcie. Nie zapominaj, że inne współistniejące czynniki, które nie są brane pod uwagę w formie tabelarycznej, mogą również wpływać na wskaźniki.

Analiza popularności treści poprzez korelację wyświetleń i repostów wideo

Trzeci przykład. Osoba promująca własne filmy na hostingu wideo YouTube korzysta z sieci społecznościowych do reklamowania kanału. Zauważa, że ​​istnieje pewien związek między liczbą repostów w sieciach społecznościowych a liczbą wyświetleń kanału. Czy można przewidzieć przyszłe wyniki za pomocą narzędzi arkusza kalkulacyjnego? Konieczne jest określenie zasadności zastosowania równania regresji liniowej do przewidywania liczby wyświetleń wideo w zależności od liczby repostów. Tabela z wartościami:

Teraz konieczne jest określenie obecności relacji między 2 wskaźnikami zgodnie z poniższym wzorem:

0,7;JEŻELI(CORREL(A3:A8;B3:B8)>0,7;”Silny związek bezpośredni”;”Silny związek odwrotny”);”Słaby lub brak związku”)' class='formuła'>

Jeżeli otrzymany współczynnik jest wyższy niż 0,7, bardziej odpowiednie jest użycie funkcji regresji liniowej. W tym przykładzie robimy:

Teraz budujemy wykres:

Stosujemy to równanie do określenia liczby wyświetleń przy 200, 500 i 1000 udostępnieniach: =9,2937*D4-206,12. Otrzymujemy następujące wyniki:

Funkcjonować PROGNOZA pozwala określić liczbę wyświetleń w danej chwili, jeśli było na przykład dwieście pięćdziesiąt repostów. Stosujemy: 0,7;PREDYKCJA(D7;B3:B8;A3:A8);”Wartości nie są ze sobą powiązane”)' class='formuła'>. Otrzymujemy następujące wyniki:

Funkcje korzystania z funkcji CORREL w programie Excel

Ta funkcja ma następujące cechy:

1. Puste komórki nie są brane pod uwagę.
2. Komórki zawierające informacje typu Boolean i Text nie są brane pod uwagę.
3. Podwójna negacja „-” służy do uwzględnienia wartości logicznych w postaci liczb.
4. Liczba komórek w badanych tablicach musi być zgodna, w przeciwnym razie zostanie wyświetlony komunikat #N/D.

Ocena istotności statystycznej współczynnika korelacji

Podczas testowania istotności współczynnika korelacji przyjmuje się hipotezę zerową, że wskaźnik ma wartość 0, podczas gdy alternatywa nie. Do weryfikacji używany jest następujący wzór:

Wnioski

Analiza korelacji w arkuszu kalkulacyjnym to prosty i zautomatyzowany proces. Aby to zrobić, musisz tylko wiedzieć, gdzie znajdują się niezbędne narzędzia i jak je aktywować za pomocą ustawień programu.