Regresje liniowe modelują związek między zależnymi i niezależnymi zmiennymi danych statystycznych. Mówiąc prościej, podkreślają trend między dwiema kolumnami tabeli w arkuszu kalkulacyjnym. Na przykład, jeśli skonfigurujesz tabelę arkusza kalkulacyjnego Excel z kolumną miesiąca x i zarejestrujesz zestaw danych dla każdego miesiąca w sąsiedniej kolumnie y, regresja liniowa podkreśli trend między zmiennymi x i y, dodając linie trendu do tabeli wykresy. W ten sposób możesz dodać regresję liniową do wykresów Excela.
Dodawanie linii trendu regresji liniowej do wykresu
- Najpierw otwórz pusty arkusz kalkulacyjny Excel, wybierz komórkę D3 i wpisz „Miesiąc” jako nagłówek kolumny, która będzie zmienną x.
- Następnie kliknij komórkę E3 i wprowadź „Wartość Y” jako nagłówek kolumny zmiennej y. Jest to w zasadzie tabela z zarejestrowaną serią wartości danych dla miesięcy od stycznia do maja.
- Wprowadź miesiące w komórkach od D4 do D8 i wartości danych dla nich w komórkach od E4 do E8, jak pokazano na migawce bezpośrednio poniżej.
Teraz możesz ustawić wykres punktowy dla tej tabeli.
- Zaznacz kursorem wszystkie komórki w tabeli.
- Kliknij kartę Wstaw i wybierz Rozpraszać > Rozpraszanie za pomocą samych znaczników aby dodać wykres do arkusza kalkulacyjnego, jak poniżej. Alternatywnie możesz nacisnąć skrót klawiszowy Alt + F1, aby wstawić wykres słupkowy.
- Następnie należy kliknąć wykres prawym przyciskiem myszy i wybrać Zmień typ wykresu >X Y (rozproszenie) >Rozpraszanie za pomocą samych znaczników.
Następnie możesz dodać linię trendu do wykresu punktowego
- Wybierz jeden z punktów danych na wykresie punktowym i kliknij prawym przyciskiem myszy, aby otworzyć menu kontekstowe, które zawiera Dodaj linię trendu opcja.
- Wybierać Dodaj linię trendu aby otworzyć okno pokazane na zrzucie bezpośrednio poniżej. To okno ma pięć zakładek, które zawierają różne opcje formatowania linii trendu regresji liniowej.
3. Kliknij Opcje linii trendu i wybierz stamtąd typ regresji. Możesz wybrać Wykładniczy, Liniowy, Logarytmiczne, Średnia ruchoma, Moc oraz Wielomian opcje typu regresji stamtąd.
4. Wybierz Liniowy i kliknij Blisko aby dodać tę linię trendu do wykresu, jak pokazano bezpośrednio poniżej.
Linia trendu regresji liniowej na powyższym wykresie podkreśla, że istnieje ogólna zależność w górę między zmiennymi x i y pomimo kilku spadków na wykresie. Zwróć uwagę, że linia trendu regresji liniowej nie nakłada się na żaden z punktów danych na wykresie, więc nie jest to to samo, co średni wykres liniowy, który łączy każdy punkt.
Formatowanie linii trendu regresji liniowej
Formatowanie linii trendu regresji liniowej jest ważnym narzędziem do tworzenia czytelnych, przejrzystych wykresów w programie Excel.
- Aby rozpocząć formatowanie linii trendu, kliknij ją prawym przyciskiem myszy i wybierz Formatuj linię trendu.
- Spowoduje to ponowne otwarcie okna Formatuj linię trendu, z którego możesz kliknąć Kolor linii.
- Wybierać Linia ciągła i kliknij Kolor okno, aby otworzyć paletę, z której możesz wybrać alternatywny kolor dla linii trendu.
- Aby dostosować styl linii, kliknij kartę Styl linii. Następnie możesz dostosować szerokość strzałki i skonfigurować ustawienia strzałki.
- wciśnij Ustawienia strzałek przyciski, aby dodać strzałki do linii.
Możesz także dodawać efekty do swojej linii trendów w celach estetycznych
- Dodaj efekt blasku do linii trendu, klikając Blask oraz Miękkie krawędzie. Spowoduje to otwarcie poniższej zakładki, z której możesz dodać blask, klikając Presety przycisk.
- Następnie wybierz wariant blasku, aby wybrać efekt. Kliknij Kolor aby wybrać alternatywne kolory dla efektu, i możesz przeciągnąć Rozmiar oraz Przezroczystość paski do dalszej konfiguracji blasku linii trendu.
Prognozowanie wartości za pomocą regresji liniowej
Po sformatowaniu linii trendu możesz również prognozować za jej pomocą przyszłe wartości. Załóżmy na przykład, że musisz prognozować wartość danych trzy miesiące po maju na sierpień, której nie ma w naszej tabeli.
- kliknij Opcje linii trendu i wpisz „3” w polu tekstowym Prześlij dalej.
- Linia trendu regresji liniowej podkreśla, że wartość sierpnia prawdopodobnie będzie nieco powyżej 3500, jak pokazano poniżej.
Każda linia trendu regresji liniowej ma własne równanie i wartość kwadratową, którą można dodać do wykresu.
- Kliknij Wyświetl równanie na wykresie pole wyboru, aby dodać równanie do wykresu. To równanie zawiera wartość nachylenia i przecięcia.
- Aby dodać wartość kwadratową r do wykresu, kliknij Wyświetl wartość R-kwadrat na wykresie pole wyboru. To dodaje r do kwadratu do wykresu tuż pod równaniem, jak na poniższym zrzucie ekranu.
- Przeciągnij równanie i pole korelacji, aby zmienić jego położenie na wykresie punktowym.
Funkcje regresji liniowej
Excel zawiera również funkcje regresji liniowej, za pomocą których można znaleźć wartości nachylenia, przecięcia i kwadratu r dla tablic danych y i x.
- Wybierz komórkę arkusza kalkulacyjnego, do której chcesz dodać jedną z tych funkcji, a następnie naciśnij Wstaw funkcję przycisk. Funkcje regresji liniowej są statystyczne, więc wybierz Statystyczny z menu rozwijanego kategorii.
- Następnie możesz wybrać RSQ, NACHYLENIE lub PRZECHWYCIĆ aby otworzyć ich okna funkcji, jak poniżej.
Okna RSQ, SLOPE i INTERCEPT są prawie takie same. Obejmują one pola Znane_y i Znane_x, do których możesz dodać wartości zmiennych y i x z tabeli. Pamiętaj, że komórki muszą zawierać tylko liczby, więc zastąp miesiące w tabeli odpowiednimi liczbami, takimi jak 1 dla stycznia, 2 dla lutego itd. Kliknij ok aby zamknąć okno i dodać funkcję do arkusza kalkulacyjnego.
Teraz możesz urozmaicić wykresy w arkuszu kalkulacyjnym Excel za pomocą linii trendu regresji liniowej. Podkreślą one ogólne trendy dla punktów danych na wykresach, a dzięki równaniom regresji są również przydatnymi narzędziami prognostycznymi.
Masz jakieś wskazówki, sztuczki lub pytania związane z liniami trendu regresji liniowej w programie Excel? Daj nam znać w sekcji komentarzy poniżej.