Ako vytvoriť krátky lineárny regresný model v programe Excel?
Vyberte rozsah buniek H3: H22 a s H3 ako aktívnou zvýraznenou bunkou zadajte w/o úvodzovky vzorec „= (B3-X_BAR)*(C3-Y_BAR)“.
Excel robí dobrú prácu so štatistikami, alebo nechá tretiu stranu napísať svoj doplnok, ktorý je k dispozícii zadarmo. V tomto článku sa naučíte, ako navrhnúť trend pomocou Excelu a lineárnej regresnej analýzy.
Časť 1 z 3: návod
- 1Otvorte nový zošit v programe Excel a vytvorte tri pracovné hárky: údaje, graf a uloženia. Uložte zošit ako lineárnu regresiu - stručnú lekciu alebo niečo podobné do priečinka s logickými súbormi.
- 2Nastaviť predvoľby: Otvorte Predvoľby v ponuke Excel. Odporúčané nastavenia: Nastavte položku Všeobecné na hodnotu R1C1 Vypnuté a zobrazte 10 najnovších dokumentov; Upraviť - nastavte, aby boli začiarknuté všetky hlavné možnosti okrem Automaticky previesť systém dátumu. Zobraziť počet desatinných miest = prázdne (pre celé čísla prednostné), Zachovať zobrazenie dátumov a nastaviť 30 pre prerušenie 21. storočia; Zobraziť - zobraziť riadok vzorcov a stavový riadok, umiestnite kurzor myši na komentáre a všetky objekty, zobrazte mriežku a všetky políčka, ktoré sú automaticky alebo začiarknuté; Graf - pri umiestnení kurzora zobrazte názvy grafov a dátové značky. Nechajte zatiaľ odpočinok nezaškrtnutý; Výpočet - Automaticky a výpočet pred uložením, max. Zmena 0,000000,00000000,01 bez čiarok, pretože pri hľadaní cieľa sa veľa robí a ukladá hodnôt externých odkazov a používa systém 1904; Kontrola chýb - začiarknite všetky; Uložiť - uloží ukážkový obrázok s novými súbormi a uloží automatické obnovenie po 5 minútach; Pás s nástrojmi - všetko začiarknuté, okrem Skryť názvy skupín a Vývojárov.
- 3Vyberte medzi 1 a A v ľavom hornom rohu pracovného hárka, aby ste vybrali celý hárok, a urobte formátovanie buniek, vodorovný stred a veľkosť písma 9 alebo 10, alebo čokoľvek, čo vám vyhovuje prezeranie.
- 4Zadajte hlavičky stĺpcov do riadka 1: B1: X; C1: Y; D1: X. E1: Y F1: Y. Zadajte hlavičky stĺpcov v riadkoch 2: A2: Študent; B2 SAT; C2: GPA; D2: VARIANCIA; E2: VARIANCIA; F2: TREND, G2: zmena.
- 5Upraviť, prejsť na rozsah buniek a3: A22, zadajte 1 a urobte Úpravu Vyplniť stĺpce radu Lineárny krok Hodnota 1 OK, 20 študentov chce poznať trend svojich závislých GPA vzhľadom na nezávislú premennú skóre testu SAT pri zadávaní nová vysoká škola, tj. pravdepodobne bude ich známka rásť, klesať alebo zostane približne rovnaká?
- 6Na zodpovedanie tejto otázky zadajte údaje. V pároch {x, y}, ako je znázornené na obrázku nižšie, krok 22 alebo nasledovne: pre študenta 1, {X, Y} = {935 pre X alebo SAT, 2,2 pre Y alebo GPA}; študent 2 {12603,1}; potom {11052,6}; {13203,3}; {14503,8}; {9602,2; {13603,2}; {9002,2}; {10202,2}; {13803,6}; {9402,3}; {11902,8}; {10002,2}; {9452,3}; {9902,4}; {10002,2}; {10402,3}; {15703,9}; {15303,8}; {9802,4}.
- 7Vyberte bunku a23 a zadajte ZMENU. Zadajte vzorec bez úvodzoviek do bunky B23" = PRIEMERNÝ (B3: B22)" a skopírujte ju a prilepte do bunky C23. Vyberte bunku C23 a vložte názov Definujte názov Y_Bar pre bunku $ C17€ Vyberte bunku B23 a vložte názov Definujte názov X_Bar pre bunku $ B17€ Vyberte rozsah buniek B23: C23 a formátujte bunky Farba písma červená a tučná.
- 8Upraviť, prejdite na rozsah buniek d3: d22 a pri bunke d3 na aktívnu a zvýraznenú bunku, zadajte bez úvodzoviek vzorec „= (b3-x_bar)^2“ a upravte vyplnenie. Upraviť Prejsť na rozsah buniek E3: E22 a pri bunke E3 na aktívnu a zvýraznenú bunku zadajte bez úvodzoviek vzorec „= (C3-Y_BAR)^2“ a položku Upraviť vyplňte. Vyberte rozsah buniek D3: D23 a vykonajte formátovanie buniek Číslo Počet Desatinná miesta 4.
- 9Vyberte bunku d23 a zadajte bez úvodzoviek vzorec „= súčet (d3: d22)/(20-1)“, skopírujte ich a prilepte do bunky e23. Vyberte rozsah buniek E3: E23 a urobte Formát buniek Počet Číslo Desatinná miesta 6. Vyberte bunku A24 a zadajte VARIANCE a vyberte bunku B24 a zadajte vzorec „= D23“ a skopírujte ju a prilepte do bunky C24. Vyberte rozsah buniek B24: D25 a vykonajte formátovanie buniek Číslo Počet Desatinná miesta 6.
- 10Vyberte bunku a25 a zadajte STD DEV. (pre štandardnú odchýlku), vyberte bunku B25 a zadajte bez úvodzoviek vzorec „= STDEVPA (B3: B22)“ a skopírujte ju a prilepte do bunky C25. Formátujte bunky Číslo Počet Desatinné miesta 7 pre vybraný rozsah buniek B25: C25.
- 11Vyberte bunku a27 a zadajte PROGNÓZU, vyberte bunku b27 a zadajte bez výrazov vzorec „= stdevpa (b3: b22)“, čo je vzorec poľa, takže musíte správne zadať vzorec poľa so zátvorkami stlačením klávesu Ctrl+ shift+ Enter. To je projekcia GPA jedného študenta pri SAT 1290 a zvyšku populácie alebo vzorových údajov. Keď naformátujete bunky s číslom a číslom s desatinnými miestami 9, môže očakávať GPA vo výške 3170 409 192 (čo sa presne zhoduje aj neskôr). Niežeby bola taká presná GPA potrebná, ale ide o dokázanie vzorca a to si vyžaduje určitú presnosť.
- 12Upraviť, prejsť na rozsah buniek f3: f22 a zadať vzorec bez úvodzoviek „= trend (c3: c22, b3: b22,, true)“, čo je vzorec poľa, takže na správne zadanie poľa musíte stlačiť klávesovú skratku+ shift+ enter vzorec so zátvorkami. to je projekcia GPA študentskej populácie vzhľadom na ich predchádzajúci výkon.
- 13Upraviť, prejsť na rozsah buniek g3: g22 a pomocou g3 na aktívnu zvýraznenú bunku, zadajte vzorec bez úvodzoviek „= f3-c3“ a upravte výplň. Robiť Formát buniek Číslo Vlastné +0,0; -0,0; +0,0.
Vyberte rozsah buniek I3: I22 a s I3 ako aktívnou zvýraznenou bunkou zadajte bez úvodzoviek vzorec „= (B3-X_BAR)^2“.
Časť 2 z 3: vysvetľujúce schémy, schémy, fotografie
- 1Vytvorte graf (v závislosti od vyššie uvedených údajov tutoriálu). Vyberte rozsah buniek B3: C23 a prejdite na pás s nástrojmi (alebo urobte sprievodcu grafom) a vyberte položku Grafy, Všetky, posuňte sa nadol na Rozptyl, Označený bodový bod.. Potom Upraviť vystrihnúť alebo skopírovať nový graf do pracovného hárka Grafy. Urobte názov grafu rozloženia grafu - názov nad grafom a vyplňte ho „Regresia čiar - SATs vs. GPA“ (bez úvodzoviek). Vyberte mriežky Vertikálne mriežky Začiarknuté hlavné mriežky. Vyberte názvy osí Horizontálny názov osi, názov titulu pod osou a upravte ich v „SAT Score“ (bez úvodzoviek). Vyberte názvy osí Názov zvislej osi, Horizontálny názov a úpravy v „GPA“ (bez úvodzoviek). Podľa konvencie ide o nezávislú premennú x na dolnú vodorovnú os a závislú premennú y o ľavú zvislú os.
- 2Nájdite údajový priemer pre priemer na {1143,752.755}, ktorý bude čítať "rad 1 bod 1143,75" (1143,75, 2,8), keď naň prejdete alebo naň kliknete. Kliknite na ňu a urobte ponuku Formát, Štýl značky dátových bodov Automatická veľkosť 9, potom Farba výplne značky Červená.
- 3Vráťte sa do pracovného hárka údajov a zistite regresnú priamku y '= mx + b, kde m = sklon a b = priesečník y. Pri pohľade na údaje v grafe s nízkymi hodnotami pod 10 vľavo a hodnotami okolo 1000 v spodnej časti by sa dal očakávať veľmi mierny desatinný sklon a y-intercept blízky 0. Menšie grafy môžu niekedy klamať.
- 4Vyberte bunku g1 a zadajte Y. Vyberte bunku H1 a zadajte m a skopírujte H1 a prilepte ju na I1 pomocou príkazu c, zvoľte I1 a príkaz v. Vyberte bunku H2 a zadajte Číslovač a vyberte bunku I2 a zadajte Menovateľ. Vyberte rozsah buniek H3: H22 a s H3 ako aktívnou zvýraznenou bunkou zadajte w/o úvodzovky vzorec „= (B3-X_BAR)*(C3-Y_BAR)“. Upraviť Vyplniť. Vyberte rozsah buniek I3: I22 a s I3 ako aktívnou zvýraznenou bunkou zadajte bez úvodzoviek vzorec „= (B3-X_BAR)^2“. Upraviť Vyplniť. Vyberte stĺpce H a I a formátujte bunky Číslo Počet Desatinná miesta 1.
- 5Vyberte bunku h23 a zadajte vzorec bez úvodzoviek „= súčet (h3: h22)“ a naformátujte bunky tak, aby ohraničili čierny tučný obrys, a skopírujte ho do i23.
- 6Vyberte bunku h24 a zadajte príkaz m a naformátujte bunky červenou farbou písma. Skopírujte ho do bunky H25 a do bunky H25 zadajte b. Vyberte bunku I24 a zadajte bez citácie vzorca „= H23/I23“, čo je sklon m, a potom vyberte bunku I25 a zadajte vzorec bez úvodzoviek „= Y_BAR-I24*X_BAR“, tj. y-zachytenie b = Y_Mean-m*X_Mean.
- 7Skopírujte bunku h25 a prilepte ju do bunky i26 a zadajte výraz i26 y ' - Mx + b. Vyberte bunku H27 a zadajte Predpoveď. Potom zadajte vzorec do úvodzoviek I27 bez úvodzoviek „= I24*1290+I25“. Vaša odpoveď by sa mala presne zhodovať s odpoveďou FORECAST v B27, akonáhle máte formát buniek, počet, číslo, desatinné miesta 9.
- 8Upraviť skopírovanie bunky i26 do rozsahu buniek h29: i29. Do bunky H29 zadajte riadok X a do bunky I29 zadajte riadok Y. Do bunky H30 zadajte 800 a do bunky H31 1600. Vyberte bunku I 27 a skopírujte jej vzorec do panela vzorcov - nekopírujte bunku a prilepte ju - nebude to fungovať správne. Vyberte bunku I30 a do editačného panela vzorcov vložte vzorec, ktorý ste práve skopírovali. To isté urobte s bunkou I31. Upravte vzorec bunky I31 tak, aby znel „= I24*H31+I25“, stlačte kláves Enter a potom v riadku vzorcov upravte vzorec bunky I30 tak, aby znel „= I24*H30+I25“ a stlačte kláves Enter. Výsledok I30 by mal byť 1,7786108729206 a výsledok I31 by mal byť 4 05093465957812 (čo si uvedomujem, je vyššie ako 4,0 GPA, ale vytvárame regresnú čiaru, takže na tom až tak nezáleží).
- 9Aktivujte pracovný hárok grafu a kliknite na graf a v ponuke, urobte pridanie údajov do grafu a v odpovedi na dotaz na rozsah sa vráťte späť do pracovného hárka údajov a vyberte rozsah buniek h30: i31. Teraz môj príde zle a musím sériu upraviť. Nie je to nič vážne. Vyberte značku údajov a na riadku vzorcov upravte sériu tak, aby čítala „= SÉRIA (, List1! $ H22€: $ H23€, List1! $ I22€: $ I230€)“ a kliknite na značku údajov na čísle {8001, 78} a urobte čiaru červenú a hmotnosť 0,75 pt, potom farbu výplne značky červenou, potom štýl značkovača okrúhla bodka 5.
Upraviť, prejdite na rozsah buniek d3: d22 a pri bunke d3 na aktívnu a zvýraznenú bunku, zadajte bez úvodzoviek vzorec „= (b3-x_bar)^2“ a upravte vyplnenie.
Časť 3 z 3: užitočné usmernenie
- 1Pri vykonávaní tohto tutoriálu použite pomocné články:
- Zoznam článkov týkajúcich sa Excelu, geometrického a/alebo trigonometrického umenia, grafov/diagramov a algebraických formulácií nájdete v článku Ako vytvoriť dráhu špirálovitých spinových častíc alebo náhrdelník alebo sférický okraj.
- Ak chcete získať ďalšie grafy a grafy, môžete tiež kliknúť na položku Kategória: snímky Microsoft Excel, Kategória: matematika, Kategória: tabuľky alebo Kategória: grafika a zobraziť mnohé pracovné hárky a grafy programu Excel, v ktorých sa trigonometria, geometria a kalkulus zmenili na umenie, alebo jednoducho kliknite na kategóriu, ako sa zobrazuje v pravej hornej bielej časti tejto stránky alebo v ľavej dolnej časti stránky.
Upraviť Prejsť na rozsah buniek E3: E22 a pri bunke E3 na aktívnu a zvýraznenú bunku zadajte bez úvodzoviek vzorec „= (C3-Y_BAR)^2“ a položku Upraviť vyplňte.
- CHYBY: Ak máte chyby alebo chybové hodnoty, buď je hárok neúplný a potrebuje ďalšie zadanie alebo vyhľadávacie tabuľky pre kritické premenné, alebo ste niekde v riadku urobili chybu. Ak boli pokyny dokončené a stále existujú chyby, vyberte bunku s hodnotou chyby, ktorá je najviac vľavo a úplne hore. Hľadaj preklep vo vzorci alebo neprekonateľné zátvorky. Definovaný názov je pravdepodobne nesprávny - je potrebné ich zadať do vzorcov presne tak, ako boli definované. Začiarknutím políčka Vložiť meno definujte. Ak máte DIV/0! Ja nie, tak hľadajte premennú, ktorá sa nejako asi nevyplnila hodnotou. V každom prípade to, čo chcete urobiť, je vybrať bunku s chybou a po skontrolovaní všetkých týchto typických chýb vykonať Nástroje Auditovanie predchodcov sledovania a/alebo Chyba sledovania.Ak oprava všetkých chýb úplne hore vľavo neopraví ostatné chyby na pracovnom hárku, možno bude potrebné to urobiť náročným spôsobom, zdola doprava hore a potom doľava; to je pomalý, ale istý spôsob, ako opraviť všetky chyby.
- Chyby v údajoch grafu sa tiež pravdepodobne vykreslia ako nuly. To môže byť dokonca prijateľné alebo žiaduce. Ak sa však príliš veľa riadkov (alebo kriviek) vracia k 0, môže to znamenať logickú chybu v údajoch - alebo príliš veľa malých hodnôt a potom je možno potrebné zmeniť mierku grafu kontrolou horizontálnej a vertikálnej osi a ich zmenou na nulu. v probléme. Umiestnite kurzor myši na značku údajov na sériovom grafe alebo naň kliknite a potom v príslušnom stĺpci vyhľadajte príslušnú hodnotu a identifikujte jej precedensy.
- Microsoft Excel. Vyššie uvedené bolo vykonané s programom MicroSoft® Excel® for Mac 2011, v 14,3.1 a ďalšie verzie sú kompatibilné, okrem doplnkov Reflection, Shadow, Glow a ďalších novších efektov.
Prečítajte si tiež: Ako vyriešiť desatinné exponenty?
Otázky a odpovede
- Aké sú vlastnosti exponenciálnej krivky?Exponenciálna krivka prechádza bodom P (0 | 1) a je podľa osi X asymptotická.
