Jak provést regresní analýzu v aplikaci Excel

Obsah:

Anonim

Regrese je analytický nástroj, který používáme k analýze velkého množství dat a vytváření předpovědí a předpovědí v aplikaci Microsoft Excel.

Chcete předvídat budoucnost? Ne, nebudeme se učit astrologii. Jsme na čísla a dnes se naučíme regresní analýzu v Excelu.

Abychom předpovídali budoucí odhady, budeme studovat:

  • REGRESNÍ ANALÝZA S VYUŽITÍM FUNKCÍ EXCEL (RUČNÍ NAJÍMÁNÍ REGRESE)
  • REGRESNÍ ANALÝZA POUŽITÍ PŘÍSLUŠENSTVÍ EXCEL’S ANALYSIS TOOLPAK
  • REGRESNÍ TABULKA V EXCELU

Pojďme na to…

Scénář:

Předpokládejme, že prodáváte nealkoholické nápoje. Jak skvělé to bude, když můžete předvídat:

  • Kolik nealkoholických nápojů bude prodáno v příštím roce na základě údajů z předchozího roku?
  • Na která pole je třeba se zaměřit?
  • A jak můžete zvýšit své prodeje změnou strategie?

Bude to ziskově úžasné. Ano?… Já vím. Začněme tedy.

Máte 11 záznamů o prodaných prodejcích a nealkoholických nápojích.

Nyní na základě těchto údajů chcete předpovědět počet prodejců potřebných k dosažení 2 000 prodejů nealkoholických nápojů.

Regresní rovnice je nástrojem k tak blízkým odhadům. Abychom to mohli udělat, musíme nejprve znát regresi.

REGRESNÍ ANALÝZA S VYUŽITÍM FUNKCÍ EXCEL (RUČNÍ NAJÍMÁNÍ REGRESE)

Tato část vám umožní lépe porozumět regresi, než jen říkat excelní regresní proceduru.

Úvod:

Jednoduchá lineární regrese:

Studium vztahu mezi dvěma proměnnými se nazývá jednoduchá lineární regrese. Kde jedna proměnná závisí na druhé nezávislé proměnné. Závislá proměnná se často nazývá názvy jako Driven, Response a Target. A nezávislá proměnná je často vyslovována jako Driving, Predictor nebo jednoduše Independent variable. Tato jména je jasně popisují.

Pojďme to nyní porovnat s vaším scénářem. Chcete znát počet prodejců, kterých je třeba dosáhnout 2000 prodejů. Zde je tedy závislou proměnnou počet prodejců a nezávislou proměnnou prodány nealkoholické nápoje.

Nezávislá proměnná je většinou označována jako X a závislá proměnná jako y.

V našem případě se prodávají nealkoholické nápoje X a počet prodejců je y.

Chceme -li vědět, kolik nealkoholických nápojů bude prodáno, pokud je jmenujeme 200 prodejců, pak bude scénář opačný.

Posouvat se.

„Jednoduchá“ matematika rovnice lineární regrese:

No, není to jednoduché. Ale Excel to udělal snadno.

Potřebujeme předpovědět požadovaný počet prodejců pro všech 11 případů, abychom získali 12. nejbližší predikci.

Řekněme:

Prodává se nealkoholický nápoj X

Číslo prodejců je y

Předpovídal y (počet prodejců) také volal Regresní rovnice, bylo by

x*Sklon+Zachytit (uvolněte se, mám to zakryté)

Nyní vás jistě zajímá, kde stat dostanete sklon a zachytit. Nebojte se, excel pro ně má funkce. Nemusíte se učit, jak najít svah a zachytit ho ručně.

Pokud chcete, připravím k tomu samostatný návod. Dejte mi vědět v sekci komentáře. Toto jsou některé důležité nástroje pro analýzu dat.

Nyní přistoupíme k našemu výpočtu:

Krok 1: Připravte si tento malý stůl

Krok 2: Najděte sklon regresní přímky

Funkce Excel pro sjezdovky je

= SLOPE (známá_roka, známá_x)

Vaše známá_va jsou v dosahu B2: B12 a známé_x jsou v dosahu C2: C12

V cele B16, napište vzorec níže

= SLOPE (B2: B12, C2: C12)

(Poznámka: Sklon se v regresní rovnici také nazývá koeficient x)

Dostanes 0.058409. Zaokrouhlete nahoru na 2 desetinná místa a dostanete 0.06.

Krok 3: Najděte čáru zachycení regrese

Funkce Excelu pro zachycení je

=INTERCEPT (known_y’s, known_x’s)

Víme, co naše známá xa y

V cele B17, zapište si tento vzorec

= INTERCEPT (B2: B12, C2: C12)

Získáte hodnotu -1,118969. Zaokrouhlete na 2 desetinná místa. Dostanes -1.11.

Naše lineární regresní rovnice je = x*0,06 + (-1,11). Nyní můžeme snadno předpovědět možné y v závislosti na cílovém x.

Krok 4: Do D2 napište vzorec níže

=C2*$ B $ 16+$ B $ 17(Regresní rovnice)

Získáte hodnotu 13.55.

Vyberte D2 až D13 a stiskněte CTRL+D k vyplnění vzorce v rozsahu D2: D13

V cele D13 máte požadovaný počet prodejců.

Proto k dosažení cíle 2000 Prodej nealkoholických nápojů, potřebujete odhad 115,71 prodejců nebo řekněme 116, protože je nezákonné rozřezat lidi na kusy.

Nyní pomocí toho můžete snadno provádět analýzu What-If v Excelu. Stačí změnit počet prodejů a ukáže vám, kolik prodejců bude zapotřebí k dosažení tohoto prodejního cíle.

Hrajte a zjistěte:

Kolik pracovní síly potřebujete ke zvýšení tržeb?

Kolik prodejů se zvýší, pokud zvýšíte své prodejce?

Zvyšte spolehlivost svého odhadu:

Nyní víte, že k dokončení 2 000 prodejů potřebujete 116 prodejců.

V analytice se nic neříká a nevěří. Svému odhadu musíte dát procento spolehlivosti. Je to jako dát osvědčení o své rovnici.

Vzorec korelačního koeficientu:

Další věc, na kterou se vás zeptáme, je, jak moc tyto dvě proměnné spolu souvisejí. Ve statických termínech musíte říci koeficient korelace.

Excel funkce pro korelaci je

= CORREL (pole1, pole2)

Ve vašem případě jsou pole known_x a Know_y’s pole1 a pole2 bez ohledu na to.

V B18 zadejte tento vzorec

= CORREL ((B2: B12, C2: C12)

Budete mít 0.919090. Naformátujte buňku B2 na procento. Nyní mít 92% korelace.

Co teď? 92% prostředek. To znamená, tam 92% šance na prodej se zvýší, pokud zvýšíte počet prodejců a 92% snížení prodeje, pokud snížíte počet prodejců. To se nazývá Kladný korelační koeficient.

R Squire (R^2):

Hodnota R Squire vám řekne, o jaké procento není vaše regresní rovnice náhoda. Nakolik je přesný podle poskytnutých údajů.

Funkce Excel pro R squire je RSQ.

RSQ (známé_y, známé_x)

V našem případě získáme hodnotu R squire v buňce B19.

V B19 zadejte tento vzorec

= RSQ (B2: B12, C2: C12)

Máme tedy 84% hodnoty r Square. Což je velmi dobré vysvětlení naší regrese. Říká se, že 84% našich dat není jen náhoda. Y (počet prodejců) je velmi závislý na X (prodeje nealkoholických nápojů).

Existuje mnoho dalších testů, které můžeme s těmito daty udělat, abychom zajistili naši regresi. Ale ručně to bude složitý a zdlouhavý postup. Proto excel poskytuje Analysis Toolpak. Pomocí tohoto nástroje můžeme tuto regresní analýzu provést během několika sekund.

REGRESE V EXCELU POUŽÍVÁNÍ PŘÍSLUŠENSTVÍ EXCEL'S ANALYSIS TOOLPAK

Pokud již víte, co jsou regresní rovnice, a chcete jen rychle dosáhnout výsledků, pak je tato část pro vás. Pokud však chcete snadno porozumět regresním rovnicím, přejděte nahoru na ANALÝZU REGRESE S POUŽITÍM FUNKCÍ EXCEL (RUČNÍ NAJÍMÁNÍ REGRESE).

Excel poskytuje v nástroji Analysis Toolpak celou řadu nástrojů pro analýzu. Ve výchozím nastavení není na kartě Data k dispozici. Musíte to přidat. Pojďme to tedy nejprve přidat.

Přidání nástroje pro analýzu do aplikace Excel 2016

Pokud nevíte, kde je analýza dat v aplikaci Excel, postupujte takto

Krok 1: Přejděte na Možnosti aplikace Excel: Soubor? Možnosti? Doplňky

Krok 2: Klikněte na Doplňky. Zobrazí se seznam dostupných doplňků.

Vyberte Analysis ToolPak a ve spodní části okna najděte správu. Ve správě vyberte Doplňky aplikace Excel a klikněte na GO.

Otevře se okno doplňků. Zde vyberte Analysis ToolPak. Poté klikněte na tlačítko ok.

Nyní máte přístup ke všem funkcím nástroje DataPack pro analýzu dat z karty Data.

Použití nástroje Analysis ToolPak pro regresi

Krok 1: Přejděte na kartu Data, najděte Analýza dat. Poté na něj klikněte.

Zobrazí se dialogové okno.

Krok 2: Najděte „Regrese“ v seznamu Analysis Tools a klikněte na tlačítko OK.

Regrese vyskočí vstupní okno. Zobrazí se řada dostupných možností zadávání. Ale prozatím se budeme soustředit pouze na rozsahy Y a X, přičemž vše ostatní ponecháme ve výchozím nastavení.

Krok 4: Poskytněte vstupy:

Počet prodejců je Y

Prodej nealkoholických nápojů je X

Proto

  • Rozsah Y = B2: B11

A

  • Rozsah X = C2: C11

Pro výstupní rozsah jsem vybral E4 na stejném listu. Můžete vybrat nový list a získat výsledky na novém listu ve stejném sešitu nebo v úplném novém sešitu. Až budete se zadáním hotovi, stiskněte tlačítko OK.

Výsledek:

Z vašich dat vám budou poskytnuty různé informace. Nenechte se zahltit. Nemusíte konzumovat všechna jídla.

Budeme se zabývat pouze těmi výsledky, které nám pomohou odhadnout požadovaný počet prodejců

Krok 5: Známe regresní rovnici pro odhad y, to je

x*Sklon+Zachytit

Musíme jen najít Sklon a Zachytit ve výsledcích.

A tady jsou.

Koeficient zachycení je jasně uveden.

Svah je psán jako „Proměnná X 1‘, Někdy také uváděné jako koeficient X. Zaokrouhlete je a dostaneme -1,11 jako Intercept a 0,06 jako sklon.

Krok 6: Z výsledků můžeme řídit regresní rovnici. A to by bylo

= x*(0,06) + (-1,11)

Připravte si tuto tabulku v Excelu.

Pro teď, X je 2000, což je v buňce E2.

Do buňky F2 zadejte tento vzorec

= E2*F21+F20

Získáte výsledek 115.7052757.

Zaokrouhlení nám dá 116 povinných prodejců.

Naučili jsme se tedy, jak vytvořit regresní rovnici ručně a pomocí Analysis ToolPak. Jak můžete pomocí této rovnice odhadovat budoucí statistiky?

Nyní pojďme pochopit regresní výstup daný nástrojem Analysis Toolpak.

Pochopení regresního výstupu:

Neexistuje žádný přínos, pokud provádíte regresní analýzu pomocí sady nástrojů pro analýzu v aplikaci Excel a nemůžete interpretovat její význam.

Shrnutí:

Jak název napovídá, jde o souhrn údajů.

    1. Multiple R: Udává, jak je regresní rovnice vhodná pro data. Říká se mu také korelační koeficient.

V našem případě je 0.919090619 nebo 0.92 (zátah). To znamená, že pokud zvýšíme počet prodejců, existuje 92% šance na zvýšení tržeb.

    1. R Square: Udává spolehlivost nalezené regrese. Říká nám, kolik pozorování je součástí naší regresní linie. V našem případě je to 0,844727566 nebo 0,85. To znamená, že naše regrese vyhovuje 85%.
    2. Upravený čtverec R: Theadjusted square je jen více osvědčenou verzí R square. Hodí se zejména při vícenásobné regresní analýze.
    3. Standardní chyba: Zatímco R. Squire vám řekne, kolik datových bodů spadá do blízkosti regresní přímky, standardní chyba vám řekne, jak daleko může datový bod od regresní přímky dojít.

V našem případě je 6.74.

  1. Pozorování: Toto je jednoduše počet pozorování, což je v našem případě 11.

Sekce Anova:

Tato část se v lineární regresi téměř nepoužívá.

  1. df. Je to určitý stupeň svobody. Používá se při ručním výpočtu regrese.
  2. SS. Součet čtverců. Je to jen součet čtverců odchylek. Slouží k nalezení hodnot R squire.
  3. SLEČNA. To znamená druhou hodnotu.
  4. A 5. F a význam F. Pokud je významnost F (p-hodnota sklonu) menší než F-test, můžete zrušit nulovou hypotézu a svoji hypotézu dokázat. V jednoduchém jazyce můžete dojít k závěru, že při změně existuje určitý vliv x na y.

V našem případě F je 48,96264 a význam F je 0,000063. To znamená, že naše regrese odpovídá datům.

Regresní sekce:

V této části máme dvě nejdůležitější hodnoty pro naši regresní rovnici.

  1. Intercept: Máme zde intercept, který říká, kde x zachycuje na Y. Toto je důležitá část regresní rovnice. V našem případě je -1,11.
  2. X proměnná 1 (Sklon). Také se nazývá koeficient x. Definuje tangens regresní přímky.

REGRESNÍ TABULKA V EXCELU

V Excelu je snadné vykreslit regresní graf. Postupujte podle těchto kroků. Chcete -li přidat graf regrese v aplikaci Excel 2016, 2013 a 2010, postupujte podle těchto jednoduchých kroků.

Krok 1. V prvním sloupci uveďte svá známá x a ve druhém y.

V našem případě víme, že Known_ x’s jsou prodávány nealkoholické nápoje. A known_y’s jsou Prodavači.

Krok 2. Vyberte svá známá rozmezí x a y.

Krok 3: Přejděte na kartu Vložit a klikněte na bodový graf.

Budete mít graf, který vypadá takto.

Krok 4. Přidejte trendovou čáru: Přejděte na rozložení a vyhledejte možnost trendové čáry v sekci analýzy.

Ve volbě Trendline klikněte na Lineární trendová čára.

Váš graf bude vypadat takto.

Toto je váš regresní graf.

Pokud nyní přidáte níže uvedená data a zvolená data rozšíříte. V grafu uvidíte změnu.

Pro náš příklad jsme přidali 2 000 k prodaným nealkoholickým nápojům a prodavače jsme nechali prázdné. A když rozšíříme rozsah grafu, budeme mít toto.

Poskytne požadovaný počet prodejců pro provedení 2 000 prodejů nealkoholických nápojů v grafické podobě. Což je v grafu lehce pod 120. A z naší regresní rovnice víme, že je 116.

V tomto článku jsem se pokusil pokrýt vše pod regresní analýzou aplikace Excel. Vysvětlil jsem regresi v Excelu 2016. Regrese v Excelu 2010 a Excelu 2013 je stejná jako v Excelu 2016.

Pro jakýkoli další dotaz na toto téma použijte sekci komentáře. Položte otázku, vyjádřete svůj názor nebo jen zmíňte mé gramatické chyby. Všechno je vítáno. Neváhejte použít sekci komentářů.

Jak vypočítat funkci MODE v aplikaci Excel

Jak vypočítat průměrnou funkci v aplikaci Excel

Jak vytvořit graf standardní odchylky

Popisné statistiky v aplikaci Microsoft Excel 2016

Jak používat funkci Excel NORMDIST

Jak používat Paretův graf a analýzu

Oblíbené články:

50 Zkratka aplikace Excel pro zvýšení produktivity

Jak používat funkci VLOOKUP v Excelu

Jak používat funkci COUNTIF v aplikaci Excel 2016

Jak používat funkci SUMIF v aplikaci Excel