Tworzenie prognozy w programie Excel dla systemu Windows

Jeśli masz dane historyczne oparte na czasie, możesz użyć ich do utworzenia prognozy. Podczas tworzenia prognozy program Excel nowy arkusz zawierający zarówno tabelę wartości historycznych i prognozowanych, jak i wykres, który przedstawia te dane. Prognoza umożliwia przewidywanie na przykład przyszłego poziomu sprzedaży, wymagań dotyczących zapasów lub trendów konsumpcyjnych.

Informacje na temat sposobu obliczania prognozy oraz opcji, które można zmienić, znajdziesz na końcu tego artykułu.

Part of a spreadsheet showing the table of forecasted numbers and a forecast chart

Tworzenie prognozy

  1. W arkuszu wprowadź dwie powiązane serie danych:

    • serię dat lub godzin w celu określenia osi czasu,

    • serię odpowiadających im wartości.

      Prognoza będzie dotyczyła tych wartości w przyszłości.

    Uwaga: Oś czasu wymaga spójnych interwałów między punktami danych. Mogą to być na przykład interwały miesięczne z wartościami pierwszego dnia każdego miesiąca, interwały miesięczne lub liczbowe. Nie szkodzi, jeśli seria osi czasu nie zawiera maksymalnie 30% punktów danych lub zawiera kilka liczb z jednakową sygnaturą czasową. Prognoza będzie nadal dokładna. Jednak podsumowanie danych przed utworzeniem prognozy zapewnia dokładniejsze wyniki prognozy.

  2. Zaznacz obie serie danych.

    Porada: Jeśli zaznaczysz komórkę w jednej z serii, pozostałe dane zostaną zaznaczone automatycznie w programie Excel.

  3. Na karcie Dane w grupie Prognoza kliknij pozycję Arkusz Prognoza.

    Przycisk Arkusz Prognoza na karcie Dane

  4. W oknie Tworzenie arkusza prognozy wybierz wykres liniowy lub wykres kolumnowy, aby uzyskać wizualną reprezentację prognozy.

    Zrzut ekranu przedstawiający okno dialogowe Tworzenie arkusza prognozy ze zwiniętym obszarem Opcje

  5. W polu Koniec prognozy wybierz datę końcową, a następnie kliknij przycisk Utwórz.

    Excel tworzy nowy arkusz zawierający zarówno tabelę wartości historycznych i prognozowanych, jak i wykres, który przedstawia te dane.

    Nowy arkusz znajduje się po lewej stronie ("przed") arkuszem, w którym wprowadzono serię danych.

Dostosowywanie prognozy

Jeśli chcesz zmienić dowolne zaawansowane ustawienia prognozy, kliknij pozycję Opcje.

Informacje o każdej z opcji znajdziesz w poniższej tabeli.

Opcje prognozy

Opis

Początek prognozy

Wybierz datę, od której rozpocznie się prognoza. Jeśli wybierzesz datę wcześniejszą niż ostatnia data w danych historycznych, w prognozie uwzględnione zostaną wyłącznie dane poprzedzające datę początkową prognozy (co nazywa się niekiedy „prognozowaniem wstecznym”).

Porady: 

  • Rozpoczęcie prognozy od punktu względem ostatniego punktu historycznego pozwala określić dokładność prognozowania przez porównanie serii prognozy z danymi rzeczywistymi. Jeśli jednak rozpoczniesz prognozę od zbyt wczesnego punktu, może ona odbiegać od prognozy wygenerowanej na podstawie wszystkich danych historycznych. Użycie wszystkich danych historycznych zapewnia większą dokładność prognozowania.

  • Jeśli dane mają charakter sezonowy, zaleca się rozpoczęcie prognozy od punktu wcześniejszego niż ostatni punkt danych historycznych.

Przedział ufności

Zaznacz lub wyczyść pole wyboru Przedział ufności, aby pokazać lub ukryć tę wartość. Przedział ufności to zakres wokół każdej z prognozowanych wartości, w którym według prognozy powinno mieścić się 95% przyszłych wartości (przy założeniu rozkładu normalnego). Przedział ufności może pomóc w ustaleniu dokładności prognozy. Mniejszy przedział oznacza większą pewność prognozy dla określonego punktu. Domyślny, 95-procentowy poziom ufności można zmienić przy użyciu strzałek w górę i w dół.

Sezonowość

Sezonowość to liczba długości (liczba punktów) wzorca sezonowości i jest wykrywana automatycznie. Na przykład w cyklu sprzedaży rocznie, z każdym punktem reprezentującym miesiąc sezonowość wynosi 12. Automatyczne wykrywanie można zastąpić, wybierając pozycję Ustaw ręcznie, a następnie wybierając liczbę.

Uwaga: W przypadku ręcznego ustawienia sezonowości należy wybrać wartość umożliwiającą wyróżnienie co najmniej 2 cykli w danych historycznych. Jeśli liczba cykli wynosi mniej niż 2, w programie Excel nie jest możliwe określenie elementów sezonowych. Jeśli sezonowość nie jest wystarczająco istotna, aby była wykrywana przez algorytm, zostanie ponownie zastosowana prognoza liniowa.

Zakres osi czasu

W tym miejscu możesz zmienić zakres osi czasu. Zakres musi odpowiadać zakresowi określonemu w polu Zakres wartości.

Zakres wartości

W tym miejscu możesz zmienić zakres serii wartości. Zakres musi być taki sam jak zakres określony w polu Zakres osi czasu.

Uzupełnij brakujące punkty przy użyciu

W programie Excel do uzupełnienia brakujących punktów jest stosowana interpolacja, co oznacza, że jako wartość brakującego punktu przyjmuje się średnią ważoną sąsiednich punktów — o ile w serii brakuje nie więcej niż 30% punktów. Aby zamiast tego traktować brakujące punkty jako zera, kliknij pozycję Zera na liście.

Agregowanie duplikatów przy użyciu

Jeśli dane zawierają wiele wartości o tej samej sygnaturze czasowej, wartości te zostaną uśrednione w programie Excel. Aby użyć innej metody obliczeń, takiej jak MedianalubLicznik, wybierz z listy obliczenia, które chcesz.

Dołącz statystykę prognozy

Zaznacz to pole wyboru, jeśli chcesz, aby w nowym arkuszu zostały dołączone dodatkowe informacje statystyczne dotyczące prognozy. W ten sposób dodamy tabelę statystyk wygenerowanych przy użyciu funkcji PROGNOZA. ETS. Funkcja STATYSTYKA i zawiera miary, takie jak współczynniki wygładzania (alfa, beta, gamma) i metryki błędów (MASE, SMAPE, MAE, RMSE).

Formuły używane w prognozowaniu danych

Podczas tworzenia prognozy za pomocą formuły zwracana jest tabela z danymi historycznymi i prognozowanymi oraz wykres. Prognoza przewiduje przyszłe wartości na podstawie istniejących danych opartych na czasie, przy użyciu wersji AAA algorytmu wygładzania wykładniczego (ETS).

Tabela może zawierać następujące kolumny, z których trzy są kolumnami obliczeniowymi:

  • Kolumna czasu historycznego (wprowadzona seria danych czasowych)

  • Kolumna wartości historycznych (seria danych z odpowiednimi wartościami)

  • Kolumna wartości prognozowanych (obliczonych przy użyciu funkcji REGLINX.ETS)

  • Dwie kolumny przedstawiające przedział ufności (obliczony przy użyciu funkcji REGLINX.ETS.CONFINT). Te kolumny są wyświetlane tylko po zaznaczoniu pola wyboru Przedział ufności w sekcji Opcje w oknie dialogowym.

Pobierz skoroszyt z przykładami

Kliknij ten link, aby pobrać skoroszyt z Excel FORECAST. Przykłady funkcji ETS

Potrzebujesz dodatkowej pomocy?

Zawsze możesz zadać pytanie ekspertowi w społeczności technicznej programu Excel, uzyskać pomoc techniczną w społeczności witryny Answers bądź zasugerować nową funkcję lub ulepszenie w witrynie UserVoice dotyczącej programu Excel.

Tematy pokrewne

Funkcje prognozowania

Potrzebna dalsza pomoc?

Rozwijaj umiejętności związane z pakietem Office
Poznaj szkolenia
Uzyskuj nowe funkcje w pierwszej kolejności
Dołącz do niejawnych testerów pakietu Office

Czy te informacje były pomocne?

Dziękujemy za opinię!

Dziękujemy za opinię! Wygląda na to, że połączenie Cię z jednym z naszych agentów pomocy technicznej pakietu Office może być pomocne.

×