Rozhodnite sa aproximačný problém experimentálne údaje - znamená zostaviť regresnú rovnicu. Problém aproximácie vzniká, ak je to potrebné, analyticky, to znamená vo forme matematickej závislosti, opísať skutočné javy, ktorých pozorovania sú uvedené vo forme tabuľky obsahujúcej hodnoty ukazovateľa v rôznych časoch alebo pri rôzne významy nezávislý argument. napr.
Ukazovatele zisku sú známe (možno ich určiť Y) v závislosti od výšky investície ( X);
Objem predaja spoločnosti je známy ( Y) za šesť týždňov jej práce. V tomto prípade, X Ide o sled týždňov.
Niekedy hovoria, že postaviť treba empirický model . Empirický sa nazýva model zostavený na základe skutočných pozorovaní. Ak sa model nájde, je možné predpovedať správanie sa skúmaného javu a procesu v budúcnosti a prípadne zvoliť optimálny smer jeho vývoja.
Všeobecne aproximačný problém experimentálne údaje majú nasledovné inscenovanie :
Nechajte údaje získané praktickým spôsobom (v priebehun experimenty alebo pozorovania), ktoré môžu byť reprezentované dvojicami čísel (X i; v i) ... Vzťah medzi nimi je uvedený v tabuľke:
| X | x 1 | x 2 | x 3 | … | x n |
| Y | y 1 | y 2 | y 3 | … | y n |
Existuje trieda rôznych funkciíF ... Je potrebné nájsť analytické (t.j. matematické) vyjadrenie vzťahu medzi týmito ukazovateľmi, to znamená, že je potrebné vybrať z množiny funkcií F funkciuf , také že. ktorý najlepšia cesta by vyhladil experimentálny vzťah medzi premennými a čo najpresnejšie by odrážal všeobecný trend vzťahu medziX a Y, s výnimkou chýb merania a náhodných odchýlok.
Podobu funkcie zistíte buď z teoretických úvah, alebo rozborom umiestnenia bodov (x i; y i) na súradnicovej rovine.
Riešiť graficky aproximačný problém znamená nakresliť takú krivku, ktorej body (x i; ŷ i) bude čo najbližšie k východiskovým bodom (x i; y i) zobrazenie experimentálnych údajov.
Pre riešenia problémy s aproximáciou použitie metóda najmenších štvorcov .
V tomto prípade sa funkcia považuje za najlepšiu aproximáciu toho, či má pre ňu súčet štvorcov odchýlok „teoretických“ hodnôt zistených empirickým vzorcom od zodpovedajúcich experimentálnych hodnôt. najmenšia hodnota v porovnaní s inými funkciami, z ktorých sa vyberá požadovaná aproximácia.
Matematický zápis metóda najmenších štvorcov je:
kde n - počet pozorovaní ukazovateľov.
Problém aproximácie sa teda delí na dve časti.
Najprv sa určí typ závislosti a podľa toho aj typ empirického vzorca, to znamená, že sa rozhodne, či je lineárny, kvadratický, logaritmický alebo nejaký iný. Ak neexistujú teoretické úvahy pre výber tvaru vzorca, funkčná závislosť sa zvyčajne vyberá z najjednoduchších, pričom sa ich grafy porovnávajú s grafom danej funkcie.
Potom sa určia číselné hodnoty neznámych parametrov zvoleného empirického vzorca, pre ktoré sa aproximácia k danej funkcii ukáže ako najlepšia.
Najjednoduchším typom empirického modelu s dvomi parametrami používanými na aproximáciu experimentálnych výsledkov je lineárna regresia, opísaná napr lineárna funkcia:
kde a, b sú požadované parametre.
Pre lineárny regresný model bude napísaná metóda najmenších štvorcov (1):
Na vyriešenie (2) vzhľadom na a a b sa parciálne derivácie rovnajú nule:
Výsledkom je, že na nájdenie a a b je potrebné vyriešiť systém lineárnych algebraických rovníc v tvare:
(3)
Existujú rôzne spôsoby implementácie metódy najmenších štvorcov pre lineárnu regresiu v Exceli.
1 spôsob. Zostrojte sústavu lineárnych algebraických rovníc dosadením všetkých známych hodnôt do (3) a vyriešte ju napríklad maticovou metódou (pozri úlohu 4).
Vo vzorcovej forme je prvok výpočtovej tabuľky znázornený na obr. 26.
Metóda 2. Vyriešte problém s optimalizáciou (2) v Exceli aplikáciou Hľadanie riešenia(pozri zadok 5).
Poznámka 1. Treba poznamenať, že pre objektívnu funkciu S je vhodné použiť vstavanú matematická funkcia SUMKVRAZN (pole1, pole2), v dôsledku čoho sa vypočíta súčet druhých mocnín rozdielov dvoch polí. V našom prípade by sme mali zadať rozsah počiatočných hodnôt ako pole1 a „teoretické“ hodnoty vypočítané vzorcom ako pole2, kde a a b - to sú adresy buniek s požadovanými hodnotami.
Poznámka 2. V dialógovom okne príkazu Nájsť riešenie by ste mali určiť cieľovú bunku, smer cieľa - na minimum a bunky, ktoré sa majú zmeniť (obr. 28). Táto úloha neobsahuje žiadne obmedzenia.
Poznámka 3. Nelineárne modely formulára môžu byť tiež použité ako empirické modely s dvoma parametrami:
Opísaná metóda na riešenie metódy najmenších štvorcov je použiteľná pre nelineárne závislosti.
Metóda 3. Ak chcete nájsť hodnoty parametrov a a b pre lineárnu regresiu môžete použiť nasledujúce vstavané štatistické funkcie v Exceli:
SLOPE (známe_y; známe_x)
INTERCEPT (známe_Y; známe_X)
LINEST (známe_y; známe_x)
Okrem toho funkcia SLOPE () vracia hodnotu parametra a , funkcia INTERCEPT () vráti hodnotu parametra b. LINEST () vracia oba parametre lineárnej závislosti súčasne, keďže ide o funkciu poľa. Preto, aby ste zadali funkciu LINREGRESE () do tabuľky, musíte dodržiavať dodržiavanie pravidiel:
Vyberte dve susediace bunky
Zadajte vzorec
· Na konci stlačte súčasne kombináciu kláves Ctrl + Shift + Enter.
V dôsledku toho bude ľavá bunka obsahovať hodnotu parametra a a vpravo - hodnota parametra b.
Na vyriešenie problému s aproximáciou graficky v Exceli musíte vytvoriť graf na základe počiatočných údajov, napr. bodový diagram s hodnotami spojenými vyhladzovacími čiarami (pozri bod 1). Excel dokáže vykresliť tento graf Trendová línia... Trendovú čiaru je možné pridať do ľubovoľného radu údajov pomocou nasledujúce typy grafy: plošné grafy, čiarové grafy, stĺpcové grafy, stĺpcové grafy alebo bodové grafy.
Keď vytvoríte trendovú čiaru v Exceli na základe údajov grafu, použije sa jedna alebo druhá aproximácia. Excel vám umožňuje vybrať si jednu z piatich približných čiar alebo vypočítať čiaru zobrazujúcu kĺzavý priemer.
Okrem toho Excel poskytuje možnosť vybrať hodnoty priesečníka trendovej čiary s osou Y, ako aj pridať do grafu aproximačnú rovnicu a hodnotu spoľahlivosti aproximácie (R 2). Môžete tiež určiť budúce a minulé hodnoty údajov na základe trendovej čiary a súvisiacej aproximačnej rovnice.
Polynomická aproximácia spojitej funkcie na segmente.
Aproximácia (z latinského "približného" - "priblížiť sa") - približné vyjadrenie akýchkoľvek matematických objektov (napríklad čísel alebo funkcií) prostredníctvom iných jednoduchších, pohodlnejších na použitie alebo jednoducho známejších. Vo vedeckom výskume sa aproximácia používa na opis, analýzu, zovšeobecnenie a ďalšie použitie empirické výsledky.
Ako viete, medzi veličinami môže existovať presný (funkčný) vzťah, keď jedna hodnota argumentu zodpovedá jednej konkrétnej hodnote, a menej presný (korelačný) vzťah, keď jednej konkrétnej hodnote argumentu zodpovedá približná hodnota resp. množina hodnôt funkcie, ktoré sú si navzájom viac-menej blízke. Pri dirigovaní vedecký výskum, spracovanie výsledkov pozorovania alebo experimentu sa zvyčajne musí zaoberať druhou možnosťou. Pri štúdiu kvantitatívnych závislostí rôznych ukazovateľov, ktorých hodnoty sú stanovené empiricky, spravidla existuje určitá variabilita. Čiastočne je to dané heterogenitou skúmaných objektov neživej a najmä živej prírody, čiastočne chybou pozorovania a kvantitatívneho spracovania materiálov. Poslednú zložku nie je vždy možné úplne vylúčiť, možno ju minimalizovať iba starostlivým výberom adekvátnej výskumnej metódy a presnosťou práce. Preto pri vykonávaní akejkoľvek výskumnej práce vyvstáva problém identifikovať skutočnú povahu závislosti študovaných ukazovateľov, ten či onen stupeň je maskovaný nedostatočnou zodpovednosťou za variabilitu hodnôt. Na to slúži aproximácia - približný popis korelačnej závislosti premenných vhodnou rovnicou funkčná závislosť, sprostredkúvajúci hlavný trend závislosti (alebo jej „trend“).
Pri výbere aproximácie by sa malo vychádzať z konkrétneho výskumného problému. Zvyčajne platí, že čím jednoduchšia rovnica sa použije na aproximáciu, tým približnejší bude popis závislosti.
Preto je dôležité prečítať si, aké významné a čo spôsobilo odchýlky konkrétnych hodnôt od výsledného trendu. Pri empirickom popise závislosti určité hodnoty oveľa väčšiu presnosť možno dosiahnuť pomocou nejakej zložitejšej, viacparametrickej rovnice. Nemá však zmysel snažiť sa s maximálnou presnosťou sprostredkovať náhodné odchýlky hodnôt v špecifických sériách empirických údajov. Oveľa dôležitejšie je uchopiť všeobecný vzorec, ktorý v v tomto prípade najlogickejšie as prijateľnou presnosťou je vyjadrené práve dvojparametrovou rovnicou výkonová funkcia... Výberom aproximačnej metódy teda výskumník vždy robí kompromis: rozhoduje, do akej miery je v tomto prípade účelné a vhodné „obetovať“ detaily a podľa toho, ako všeobecne má byť závislosť porovnávaných premenných vyjadrená. Spolu s identifikáciou vzorov maskovaných náhodnými odchýlkami empirických údajov od všeobecný vzor aproximácia tiež umožňuje riešiť mnohé ďalšie dôležité problémy: formalizovať nájdenú závislosť; nájsť neznáme hodnoty závislej premennej interpoláciou alebo, ak je to vhodné, extrapoláciou.
Tu sa bude diskutovať o polynomiálnej aproximácii. To znamená, že našou úlohou je, že na základe počiatočných údajov (funkcie a segmentu) je potrebné nájsť polynóm, ktorého čiarová odchýlka od grafu počiatočnej funkcie bude minimálna.
Najpopulárnejšou metódou polynómovej aproximácie je metóda najmenších štvorcov. V Exceli sa implementuje pomocou grafu a trendovej čiary.
Poďme analyzovať túto metódu v Exceli.
Počiatočné údaje:
Najprv musíme rozdeliť tento segment pomocou rozdelenia "Chebyshev", pretože daný pohľad delenie vždy poskytuje presnejší výsledok.
Do stĺpca I (obr. 1) zapisujeme čísla od 0 do 8, pretože Segment rozdelíme na 8 častí.
V stĺpci z sa bunky vypočítajú pomocou vzorca: COS (3,141593 * I / 8). Na výpočet každej bunky použijeme zodpovedajúce I.
Hodnota každého x sa zistí podľa vzorca: 2 * z + 1.
V stĺpci F (x) vypočítajte hodnotu tejto funkcie pre každé x.
Obrázok 1
Ďalej v bunkách H2, I2, J2 nastavíme počiatočné hodnoty koeficientov a, b a c v požadovanom polynóme (obr. 2).
Obrázok 2
V stĺpci F z buniek 2 až 10 vypočítame hodnoty odchýlok, t.j. modul rozdielu medzi hodnotou počiatočnej funkcie a nájdeného polynómu.
Vzorec: ABS ((1 + x ^ 2) ^ 0,5 + 2 ^ (- x) - ($ H $ 2 * x ^ 2 + $ I $ 2 * x + $ J $ 2)).
Bunka B11 vypočítava súčet rozptylov a bunka B12 vypočítava priemernú odchýlku (obrázok 3).
Obrázok 3
Pomocou Sprievodcu grafom vytvorte bodový graf na základe údajov v stĺpcoch x a F (x). Teraz na karte „Diagram“ vyberte „Pridať trendovú čiaru“ a začiarknite políčko potrebné na zobrazenie rovnice v diagrame (obr. 4).
Obrázok 4
Teraz dosadíme koeficienty z výslednej rovnice do buniek H2, I2 a J2 (obr. 5).
Obrázok 5
Ako vidíte, priemerná odchýlka je 0,117006252.
Nájdený polynóm: 0,363 * x² - 0,6901 * x + 2,2203.
Navrhujeme inú metódu polynómovej aproximácie.
Otvorte kartu „Služba“ a vyberte „Hľadať riešenia“. V zobrazenom okne zadajte F11 ako cieľovú bunku a rovná sa minimálnej hodnote. V poli "zmena buniek" zadajte H2, I2 a J2.
Kliknite na tlačidlo "Spustiť". Po dokončení postupu vidíme, že výsledky sa zmenili (obr. 6).
Obrázok 6
Tentoraz je priemerná odchýlka 0,106084329.
Nájdený polynóm: 0,35724 * x² - 0,702 * x + 2,259158.
Tento výsledok je oveľa presnejší ako predchádzajúci, čo potvrdzuje výhodu použitia minimalizácie súčtu odchýlok v porovnaní s metódou najmenších štvorcov.
ZÁVISLOSTI
Excel má nástroje na predpovedanie procesov. Problém aproximácie nastáva, keď je potrebné analyticky opísať javy, ktoré sa odohrávajú v živote a sú uvedené vo forme tabuliek obsahujúcich hodnoty argumentu (argumentov) a funkcií. V prípade zistenia závislosti je možné predpovedať správanie sa skúmaného systému v budúcnosti a prípadne zvoliť optimálny smer jeho vývoja. Takáto analytická funkcia (nazývaná aj trendová) môže mať iný druh a inú úroveň zložitosti v závislosti od zložitosti systému a požadovanej presnosti zobrazenia.
10.1. Lineárna regresia
Najjednoduchšia a najobľúbenejšia je priama aproximácia – lineárna regresia.
Predpokladajme, že máme aktuálne informácie o úrovni zisku Y v závislosti od veľkosti X kapitálových investícií - Y (X). Na obr. 10.1-1 sú znázornené štyri takéto body M (Y, X). Aj my máme dôvod predpokladať, že táto závislosť je lineárna, t.j. má formu Y = A + BX. Ak by sme dokázali nájsť koeficienty A a B a použiť ich na zostrojenie priamky (napríklad ako na obrázku), v budúcnosti by sme mohli urobiť informované predpoklady o dynamike podnikania a možnom komerčnom stave podniku v budúcnosti. Je zrejmé, že by sme sa uspokojili s priamkou umiestnenou čo najbližšie k známym bodom M (Y, X), t.j. s minimálnym množstvom odchýlok alebo množstvom chýb (na obrázku sú odchýlky znázornené prerušovanými čiarami). Je známe, že takáto priamka je len jedna.
|
|
Na vyriešenie tohto problému sa používa metóda najmenších štvorcov chýb. Rozdiel (chyba) medzi známa hodnota Y1 bodu М1 (Y1, X1) a hodnota Y (X1), vypočítaná rovnicou priamky pre rovnakú hodnotu X1, bude
D1 = Y1 - A - B X1.
Rovnaký rozdiel
pre X = X2 bude D2 = Y2 - A - B X2;
pre X = X3 D3 = Y3 - A - B X3;
a pre X = X4 D4 = Y4 - A - B X4.
Napíšme výraz pre súčet druhých mocnín týchto chýb
Ф (A, B) = (Y1 – A – B X1) 2 + (Y2 – A – B X2) 2 + (Y3 – A – B X3) 2 + (Y4 – A – B X4) 2
alebo v skrátenej forme Ф (B, A) = е (Yi - A - BXi) 2.
Tu poznáme všetky X a Y a neznáme koeficienty A a B. Hľadanú čiaru nakreslíme tak (teda zvolíme A a B tak), aby tento súčet štvorcov chýb Ф (A, B) je minimálny. Podmienkou minimalizácie sú dobre známe vzťahy
¶Ф (A, B) / ¶A = 0 a ¶Ф (A, B) / ¶B = 0.
Odvoďme tieto výrazy (vynecháme indexy v znamienku súčtu):
¶ [å (Yi – A – B Xi) 2] / ¶A = å (Yi – A – B Xi) (- 1)
¶ [å (Yi – A – B Xi) 2] / ¶B = å (Yi – A – B Xi) (- Xi).
Výsledné vzorce transformujeme a prirovnáme k nule
Riešenie aproximačných úloh pomocou Excelu
lekár fyz.- mat. vedy, profesor Gavrilenko V.V. asistent Parochnenko L.M.
(Národná dopravná univerzita)
Teoretické informácie. V praxi pri modelovaní rôzne
procesy, najmä ekonomické, fyzikálne, technické, sociálne,
široko sa používajú rôzne metódy výpočtu približných hodnôt
funkcie od ich známych hodnôt v niektorých pevných bodoch.
Takéto problémy aproximácie funkcií často vznikajú:
∙ pri konštrukcii približných vzorcov na výpočet hodnôt charakteristiky
hodnoty skúmaného procesu podľa tabuliek získaných v
výsledok fyzikálneho alebo výpočtového experimentu;
∙ s numerickou integráciou, numerickou diferenciáciou, numerickým
n rozhodnutie diferenciálne rovnice atď.;
∙ ak je potrebné vypočítať hodnoty funkcií v medziľahlých bodoch
kah uvažovaného intervalu;
∙ pri určovaní hodnôt charakteristických veličín procesu mimo
interval, najmä v prípade potreby nahliadnuť do
„Minulosť“), to znamená pri určovaní hodnôt procesných ukazovateľov predtým
hodiny pozorovania;
∙ v prognózovaní, teda pri príjme predbežné hodnotenia Budem-
hodnoty procesných ukazovateľov záujmu (schopnosť pozerať sa
do budúcnosti“).
Ak na simuláciu určitého procesu uvedeného v tabuľke,
zostavte približný popis tejto procesnej funkcie na základe
metóda najmenších štvorcov, potom sa nazýva aproximačná funkcia
(regresia), a samotný problém konštrukcie aproximačných funkcií je tzv
problém aproximácie.
Tento článok pojednáva o možnostiach balíka Excel pri riešení
aproximačné problémy, menovite metódy a techniky konštrukcie
(vytváranie) regresií pre funkcie definované v tabuľke, čo je základom regresnej analýzy.
Excel má tieto funkcie na vytváranie regresií:
1) pridanie vybraných regresií (trendových čiar) do diagramu zostaveného na základe dátovej tabuľky pre študovanú charakteristiku procesu (tento nástroj je možné použiť len vtedy, ak existuje zostrojený dia-
2) pomocou vstavaných štatistických funkcií pracovného hárka Excel,
čo umožňuje získať regresie (trendové čiary) na základe tabuľky výsledkov
údajov (použitie tohto nástroja nie je predtým prepojené
s prítomnosťou zodpovedajúceho diagramu).
Pridanie trendových čiar do grafu
Pre tabuľku údajov popisujúcich určitý proces a reprezentovaných diagramom má Excel efektívny nástroj regresná analýza
pre, umožňujúce:
∙ stavajte na základe metódy najmenších štvorcov a pridajte do diagramu päť typov regresií (trendových čiar), ktoré sú viac či menej presné
sti simulovať skúmaný proces;
∙ pridajte rovnicu zostrojenej regresie do diagramu;
∙ určiť mieru, do akej sa vybraná regresia zhoduje s údajmi zobrazenými v grafe.
Skonštruované procesné modely - zobrazenie trendových čiar
trend zmeny údajov, umožňujú určiť hodnoty štúdie
charakteristiky v medziľahlých bodoch, predpovedajú správanie tohto procesu v budúcnosti (problém extrapolácie) a tiež nahliadnu do jeho minulosti.
Na základe údajov z grafu vám Excel umožňuje získať tieto typy regresie
toto alebo trendové čiary, ako sú lineárne, polynomické, logaritmické, ste-
penové, exponenciálne, ktoré sú dané rovnicou y = y (x), kde x nie je
Závislá premenná, ktorá často nadobúda hodnoty postupnosti prirodzených čísel (1; 2; 3; ...) a produkuje napríklad odpočítavanie doby chodu skúmaného procesu.
1. Lineárna regresia je vhodná na modelovanie charakteristík, ktoré sa zvyšujú alebo znižujú konštantnou rýchlosťou. Toto je najjednoduchší na zostavenie, ale najmenej presný model skúmaného procesu.
y = m x + b,
kde m je uhol sklonu lineárnej regresie k osi x; b - súradnica priesečníka lineárnej regresie so zvislou osou.
2. Polynomická trendová čiara je užitočná na opis charakteristík,
s niekoľkými výraznými extrémami (maximum a minimum)
mov). Voľba stupňa polynómovej trendovej čiary (polynómu) je určená počtom extrémov študovanej charakteristiky. Polynóm druhého stupňa teda môže dobre opísať charakteristiku, ktorá má len jedno maximum
mama alebo minimum; polynóm tretieho stupňa - nie viac ako dva extrémy; na-
línia štvrtého stupňa - nie viac ako tri extrémy atď.
Skonštruované v súlade s rovnicou
y = c0 + c1 x + c2 x2 + c3 x3 + c4 x4 + c5 x5 + c6 x6,
kde koeficienty c 0, c 1, c 2, ... c 6 sú konštanty.
3. Logaritmické trendová čiara sa úspešne používa v modelovaní
charakteristiky, ktorých hodnoty sa spočiatku rýchlo zvyšujú alebo znižujú a potom sa postupne stabilizujú.
Skonštruované v súlade s rovnicou
y = c × ln (x) + b,
4. Výkonová trendová čiara dáva dobré výsledky, ak sú hodnoty štúdie
Tieto závislosti sú charakterizované neustálou zmenou rýchlosti rastu.
Príkladom takejto závislosti je graf rovnomerne zrýchleného pohybu.
niya auto. Ak údaje obsahujú nulové alebo záporné hodnoty, nemôžete použiť silovú trendovú čiaru.
Skonštruované v súlade s rovnicou
y = c × xb,
kde koeficienty b, c sú konštanty.
5. Exponenciálny Trendová čiara by sa mala použiť, keď sa rýchlosť zmeny údajov neustále zvyšuje. Pre údaje obsahujúce nulové alebo záporné hodnoty sa tento druh aproximácie nedá použiť.
Skonštruované v súlade s rovnicou
y = c × eb × x,
kde koeficienty b, c sú konštanty.
Pri výbere trendovej čiary Excel automaticky vypočíta hodnotu R 2, ktorá charakterizuje presnosť aproximácie: čím je hodnota R 2 bližšie k jednej, tým spoľahlivejšie sa trendová čiara približuje skúmanému
môj proces. V prípade potreby je možné hodnotu R 2 vždy zobraziť
diagram.
Určené vzorcom
R2 = 1- | Σ1 = ∑ (yj - Yj) 2 | S2 = ∑Y j 2 - | × (∑Yj) 2 | ||||||
Ak chcete pridať trendovú čiaru do série údajov:
1. Aktivujte graf zostavený na základe dátového radu, t.j. klikni-
v oblasti grafu. Dia-
2. Po kliknutí na túto položku sa na obrazovke zobrazí ponuka, v ktorej vyberte príkaz Pridať trendovú čiaru.
umiestnite kurzor myši na graf vykreslený v rade údajov a kliknite pravým tlačidlom myši
pomocou myši a v zobrazenej kontextovej ponuke vyberte príkaz Pridať
trendová čiara. Na obrazovke sa rozbalí dialógové okno Trendová čiara
záložka Typ (obr. 1).
Obr. Karta Typ v dialógovom okne Formát trendovej čiary
3. Vyberte požadovaný typ trendovej čiary na karte Typ (štandardne je vybratý typ Lineárny). Pre typ polynómu v poli Stupeň ďalej
úder pre nastavenie stupňa zvoleného polynómu.
4. Pole Plotted on Series obsahuje zoznam všetkých radov údajov príslušného grafu. Ak chcete pridať trendovú čiaru ku konkrétnej sérii údajov, vyberte jej názov v poli Plotted on Series.
5. V prípade potreby môžete prejsť na kartu Parametre (obr. 2).
nastavený trend nasledujúce parametre:
∙ Zmeňte názov trendovej čiary v poli Názov aproximácie
(vyhladená) krivka;
∙ Nastavte počet období (dopredu alebo dozadu) pre predpoveď v Pro-
∙ Zobrazte rovnicu trendovej čiary v oblasti diagramu, pre ktorú je potrebné
Začiarknite políčko pre možnosť „zobraziť rovnicu v diagrame“.
∙ Zobrazte hodnotu aproximačnej spoľahlivosti R 2 v oblasti diagramu,
pre ktoré by ste mali začiarknuť políčko pre možnosť „umiestniť do diagramu
identita aproximačnej spoľahlivosti (R ^ 2)“.
∙ Nastavte priesečník trendovej čiary s osou Y, pre ktorú by ste ju mali nastaviť
Zaškrtnite políčko pri možnosti „priesečník krivky s osou y v bode:“. 6. Stlačte tlačidlo OK.
Obr. Karta Možnosti v dialógovom okne Trendová čiara
Ak chcete upraviť už vytvorenú trendovú čiaru, mali by ste:
1. Kliknite ľavým tlačidlom myši na želanú trendovú čiaru
zmeniť.
2. Stlačte tlačidlo Formát v hlavnej ponuke a kontextovej ponuke, ktorá sa zobrazí
nude vyberte príkaz Vybraná trendová čiara.
Položky 1-2 sa tiež dajú ľahko implementovať ďalší trik: poslať
Ukazovateľom myši na čiarový graf trendu kliknite pravým tlačidlom myši a v zobrazenej kontextovej ponuke vyberte príkaz Formátovať čiaru trendu.
Položky 1–2 sa implementujú ešte jednoduchšie: dvojitým kliknutím ľavého tlačidla myši na graf trendu.
3. Na obrazovke sa objaví dialógové okno Formát trendovej čiary (obr. 3), ktoré obsahuje
ktorý obsahuje tri karty: Zobraziť, Typ, Parametre a obsah kariet je Typ,
Parametre sa úplne zhodujú s podobnými kartami dialógového okna.
na trendovej čiare (obr. 1–2).
4. V prípade potreby môžete prechodom na kartu Zobraziť (obr. 3) nastaviť typ čiary, jej farbu a hrúbku pre trendovú čiaru.
5. Stlačte tlačidlo OK.
Ak chcete vymazať už vytvorenú trendovú čiaru, vyberte trendovú čiaru, ktorá sa má vymazať, a stlačte kláves Delete.
Výhody tohto nástroja regresnej analýzy sú:
∙ relatívna jednoduchosť vykresľovania trendových čiar do grafov bez vytvárania
tabuľky s údajmi;
∙ pomerne široký zoznam typov navrhovaných trendových čiar a tento zoznam obsahuje najčastejšie používané regresie;
∙ schopnosť náhodne predpovedať správanie skúmaného procesu
voľný (v medziach zdravého rozumu) počet krokov vpred, ako aj späť;
∙ schopnosť získať rovnicu trendovej čiary v analytickej forme;
∙ možnosť, ak je to potrebné, získať posúdenie spoľahlivosti
aproximácia údajov.
Nevýhody zahŕňajú nasledujúce body:
∙ konštrukcia trendovej čiary sa vykonáva iba vtedy, ak existuje diagram zostavený z množstva údajov;
∙ proces vytvárania radov údajov pre výskum je trochu neprehľadný
moje charakteristiky založené na rovniciach trendových čiar získaných pre to
áno, pretože koeficienty týchto rovníc pri každej zmene hodnôt série
áno údaje sú prepočítané, ale iba v rámci oblasti grafu;
∙ v zostavách kontingenčného grafu, keď zmeníte zobrazenie grafu alebo prepojenej zostavy kontingenčnej tabuľky existujúce trendové čiary sa neuložia,
to znamená pred nakreslením trendových čiar alebo iným formátovaním zostavy súhrn
diagramy, mali by ste sa uistiť, že rozloženie správy spĺňa požadované
požiadavky.
Obr. Karta Zobraziť v dialógovom okne Formát trendovej čiary
Trendové čiary možno použiť na doplnenie radov údajov uvedených v grafe.
ficks, histogramy, ploché nenormalizované grafy s plochami, príp
nahé, rozptylové, bublinové a akciové grafy.
Nie je možné pridať trendové čiary do dátových radov na objemových, normalizovaných
Kúpeľne, Radarové, Koláčové a Donutové grafy. Pri zmene typu dia-
gramov na jednu z vyššie uvedených a keď zmeníte zobrazenie zostavy kontingenčného grafu alebo prepojenej zostavy kontingenčnej tabuľky,
trendové čiary, ktoré zodpovedajú údajom, sa stratia.
Používanie vstavaných funkcií programu Excel
Excel tiež poskytuje nástroj regresnej analýzy na vykresľovanie trendových čiar mimo oblasti grafu. Na tento účel možno použiť množstvo štatistických funkcií pracovného hárka, ale všetky umožňujú zostaviť iba lineárne alebo exponenciálne regresie.
Excel má niekoľko možností na zostavenie lineárnej regresie (či už
nervový trend), najmä:
∙ pomocou funkcie TREND;
∙ pomocou funkcie LINREGRESE;
∙ s funkciami TILT a INTERCEPT.
Excel má tiež niekoľko možností na vytvorenie exponenciálnej trendovej čiary, najmä:
∙ pomocou funkcie GROWTH;
∙ pomocou funkcie LGRFPRIBL.
Je potrebné poznamenať, že techniky na vytváranie regresií pomocou funkcií
TREND a RAST sú takmer rovnaké. To isté možno povedať o dvojici funkcií LINEST a LGRFPRIBL. Pre všetky tieto štyri funkcie sa pri vytváraní tabuľky hodnôt používajú funkcie Excelu, ako sú vzorce poľa, čo trochu komplikuje proces vytvárania regresie.
toto. Všimnite si tiež, že konštrukciu (vytvorenie) lineárnej regresie je podľa nášho názoru najjednoduchšie vykonať pomocou funkcií SLOPE a INTERCEPT,
kde prvý definuje sklon lineárna regresia a druhá
raj - segment odrezaný regresiou na osi y.
Výhody tohto nástroja regresnej analýzy sú:
∙ je pomerne jednoduchý proces rovnakého typu vytvárania radov údajov
nasledujúcu charakteristiku pre všetky vstavané štatistické funkcie,
dávať trendové čiary;
∙ štandardná technika na vytváranie trendových čiar na základe generovaných radov údajov;
∙ schopnosť v prípade potreby predvídať správanie skúmaného procesu
počet krokov vpred alebo vzad.
Medzi nevýhody tohto nástroja patrí skutočnosť, že Excel nemá vstavané funkcie na vytváranie ďalších (okrem lineárnych a exponenciálnych).
rôzne) typy trendových čiar. Táto okolnosť často neumožňuje výber dostatočne presného modelu pomocou vyššie uvedených vstavaných funkcií.
del vyšetrovaného procesu, ako aj získať predpovede blízke realite.
Taktiež pri použití funkcií TREND a GROWTH nie sú známe rovnice trendovej čiary.
tyi - on konkrétne príklady pri rozhodovaní ukázať možnosti balíka Excel
nii problémy aproximácie; ukázať, aké účinné sú nástroje
Excel má nástroje na vytváranie regresií a prognóz; proil-
Na ilustráciu toho, ako relatívne jednoducho môže takéto problémy vyriešiť aj používateľ, ktorý nemá hlboké znalosti o regresnej analýze.
Metodika navrhovaná v článku na zvládnutie zručností riešenia prostriedkov
mi Excel tohto druhu problémov (pozri tiež, kde metódy riešenia systémov lineárnych algebraických rovníc, nelineárnych rovníc,
optimalizačné problémy, dopravné problémy) môžu byť pre používateľov užitočné a zaujímavé. To je spôsobené tým, že Excel je nainštalovaný na takmer každom modernom počítači, zatiaľ čo taký známy špecializovaný
matematické balíky ako Mathematica, Maple, Matlab, Mathcad,
s výkonnejšími schopnosťami na vytváranie regresií a
prognózovanie využíva podstatne menej vlastných auditov
Nižšie sú uvedené riešenia konkrétnych problémov pomocou nástrojov uvedených v balíku Excel.
Cieľ 1 Pre tabuľku údajov o zisku podniku motorovej dopravy za roky 1995-2002. musíte urobiť nasledovné.
(Venujte si pozornosť dodatočnej časti zo dňa 06.04.2017 na konci článku.)
Účtovníctvo a kontrola! Tí, čo majú nad 40 rokov, by si mali dobre zapamätať toto heslo z éry budovania socializmu a komunizmu u nás.
Ale bez zabehnutého účtovníctva efektívne fungovanie ani krajiny, ani regiónu, ani podniku, ani domácnosti v akejkoľvek sociálno-ekonomickej formácii spoločnosti! Na vytváranie prognóz a plánov aktivít a rozvoja sú potrebné počiatočné údaje. Kde ich zohnať? Len jeden spoľahlivý zdroj je tvojštatistické údaje za predchádzajúce časové obdobia.
Zohľadňovať výsledky svojej činnosti, zbierať a zaznamenávať informácie, spracovávať a analyzovať údaje, aplikovať výsledky analýzy na prijatie správne rozhodnutia v budúcnosti by mal, podľa môjho chápania, každý rozumný človek. Nejde o nič iné ako o hromadenie a racionálne využívanie vašich životných skúseností. Ak si nevediete záznamy o dôležitých údajoch, po určitom čase ich zabudnete a keď sa týmito problémami začnete znova zaoberať, znova urobíte tie isté chyby, ktoré ste urobili, keď ste to urobili prvýkrát.
"Pamätám si, že pred 5 rokmi sme vyrábali až 1000 kusov takýchto predmetov mesačne a teraz ich sotva dokážeme nazbierať 700!" Otvoríme štatistiku a vidíme, že pred 5 rokmi sa nevyrobilo 500 kusov ...
„Koľko nabehne kilometer vášho auta, berúc do úvahy zo všetkých náklady?" Štatistika otvorenia - 6 rubľov / km. Cesta do práce - 107 rubľov. Lacnejšie ako taxík (180 rubľov) viac ako jeden a pol krát. A boli časy, keď bol taxík lacnejší ...
"Ako dlho trvá výroba kovových konštrukcií pre 50 m vysokú rohovú komunikačnú vežu?" Otvárame štatistiku - a za 5 minút je odpoveď pripravená ...
"Koľko bude stáť rekonštrukcia izby v byte?" Zvyšujeme staré záznamy, robíme úpravy o infláciu za posledné roky, berieme do úvahy, že naposledy sme nakupovali materiály o 10 % lacnejšie Trhová cena a - už poznáme odhadované náklady ...
Vedenie záznamov o vašich odborná činnosť, budete vždy pripravení odpovedať šéfovi na otázku: "Kedy !!! ???". Vedením evidencie domácnosti je jednoduchšie plánovať výdavky na veľké nákupy, dovolenky a iné výdavky v budúcnosti, prijať vhodné opatrenia na privyrobenie alebo zníženie zbytočných výdavkov už dnes.
V tomto článku som na jednoduchý príklad Ukážem, ako môžete zozbierané štatistické údaje spracovať v Exceli pre možnosť ďalšieho využitia pri prognózovaní budúcich období.
Aproximácia štatistických údajov v Exceli pomocou analytickej funkcie.
Výrobný závod vyrába stavebné kovové konštrukcie z valcovania plechov a profilov. Stránka funguje stabilne, zákazky sú rovnakého typu, počet pracovníkov mierne kolíše. Existujú údaje o produkcii výrobkov za predchádzajúcich 12 mesiacov a o množstve valcovaného kovu spracovaného počas týchto časových období podľa skupín: plechy, I-nosníky, kanály, uholníky, kruhové rúry, profily obdĺžnikový rez, okrúhle valcované výrobky. Po predbežnej analýze počiatočných údajov sa predpokladalo, že celková mesačná produkcia kovových konštrukcií výrazne závisí od počtu rohov v zákazkách. Overme si tento predpoklad.
Najprv pár slov o aproximácii. Budeme hľadať zákon - analytickú funkciu, teda funkciu, daný rovnicou, ktorý lepšie ako iné popisuje závislosť celkového výkonu kovových konštrukcií od počtu uhlov v realizovaných zákazkách. Toto je aproximácia a nájdená rovnica sa nazýva aproximačná funkcia pre pôvodnú funkciu, ktorá je uvedená vo forme tabuľky.
1. Zapnite Excel a umiestnite na hárok tabuľku so štatistickými údajmi.
2. Ďalej vytvoríme a naformátujeme bodový graf, v ktorom nastavíme hodnoty argumentu pozdĺž osi X - počet spracovaných rohov v tonách. Na os Y vynesieme hodnoty pôvodnej funkcie - celková produkcia kovových konštrukcií za mesiac, ako je uvedené v tabuľke.
3. Myš „prejdeme“ na ktorýkoľvek z bodov na grafe a kliknutím pravým tlačidlom myši vyvoláme kontextové menu (ako hovorí jeden môj dobrý priateľ – keď pracuješ v neznámom programe, keď nevieš, čo máš robiť, kliknite pravým tlačidlom myši častejšie ...). V rozbaľovacej ponuke vyberte možnosť „Pridať trendovú čiaru ...“.
4. V okne "Trendline", ktoré sa zobrazí, na karte "Typ" vyberte "Lineárne".
6. Na grafe sa objavila priamka, ktorá aproximuje našu tabuľkovú závislosť.
Okrem samotnej priamky vidíme rovnicu tejto priamky a hlavne vidíme hodnotu parametra R 2 - veľkosť aproximačnej spoľahlivosti! Čím je jej hodnota bližšie k 1, tým presnejšie sa zvolená funkcia približuje tabuľkovým údajom!
7. Vytvárame trendové čiary pomocou mocninných, logaritmických, exponenciálnych a polynomických aproximácií, podobne ako pri vytváraní lineárnej trendovej čiary.
Najlepšie zo všetkých vybraných funkcií aproximuje naše údaje polynómom druhého stupňa, má maximálny faktor spoľahlivosti R 2.
Chcem vás však varovať! Ak vezmete polynómy vyšších stupňov, možno dostanete ďalšie najlepšie skóre ale krivky budú zložité... Tu je dôležité pochopiť, že hľadáme funkciu, ktorá má fyzický význam... Čo to znamená? To znamená, že potrebujeme aproximatívnu funkciu, ktorá bude dávať adekvátne výsledky nielen v rámci uvažovaného rozsahu hodnôt X, ale aj mimo neho, čiže odpovie na otázku: „Aký bude výstup kovových konštrukcií, ak počet rohov spracovaných za mesiac je menej ako 45 a viac ako 168 ton!" Preto neodporúčam nechať sa unášať polynómami vysokých stupňov a parabolu (polynóm druhého stupňa) vyberať opatrne!
Musíme teda zvoliť funkciu, ktorá nielen dobre interpoluje tabuľkové údaje v rozsahu hodnôt X = 45 ... 168, ale umožňuje aj adekvátnu extrapoláciu mimo tohto rozsahu. V tomto prípade volím logaritmickú funkciu, aj keď si môžete vybrať lineárnu, ako najjednoduchšiu. V tomto príklade pri výbere lineárnej aproximácie v programe Excel budú chyby väčšie ako pri výbere logaritmickej, ale nie o veľa.
8. Odstráňte všetky trendové čiary z poľa grafu okrem logaritmickej funkcie. Ak to chcete urobiť, kliknite pravým tlačidlom myši na nepotrebné riadky a v rozbaľovacej ponuke vyberte možnosť "Vymazať".
9. Nakoniec pridajte chybové úsečky do tabuľkových údajových bodov. Ak to chcete urobiť, kliknite pravým tlačidlom myši na ktorýkoľvek z bodov grafu a v kontextovej ponuke vyberte možnosť „Formátovať rad údajov ...“ a nastavte údaje na karte „Chyby Y“ ako na obrázku nižšie.
10. Potom klikneme pravým tlačidlom myši na ktorýkoľvek z riadkov chybových rozsahov, v kontextovom menu zvolíme "Formátovať chybové úsečky ..." a v okne "Formátovať chybové úsečky" na karte "Zobraziť" nastavíme farbu a hrúbka čiar.
Všetky ostatné objekty grafu sú naformátované rovnakým spôsobom.Excel!
Konečný výsledok diagramu je zobrazený na nasledujúcom obrázku.
Výsledky.
Výsledkom všetkých predchádzajúcich akcií bol výsledný vzorec aproximačnej funkcie y = -172,01 * ln (x) +1188,2. Na základe toho a počtu rohov v mesačnom súbore prác je možné s vysokou pravdepodobnosťou (± 4% - pozri chybové úsečky) predpovedať celkovú výrobu kovových konštrukcií na mesiac! Napríklad, ak je v mesačnom pláne 140 ton rohov, potom bude celkový výkon s najväčšou pravdepodobnosťou, ak sú ostatné veci rovnaké, 338 ± 14 ton.
Na zvýšenie spoľahlivosti aproximácie štatistických údajov by toho malo byť veľa. Dvanásť hodnotových párov nestačí.
Z praxe poviem, že nájdenie aproximačnej funkcie s faktorom spoľahlivosti R 2 > 0,87 by sa malo považovať za dobrý výsledok. Vyborny vysledok- keď R2 > 0,94.
V praxi môže byť ťažké určiť jeden najdôležitejší určujúci faktor (v našom príklade hmotnosť rohov spracovaných za mesiac), ale ak sa pokúsite, vždy ho nájdete v každej konkrétnej úlohe! Samozrejme, celkový výkon za mesiac skutočne závisí od stoviek faktorov, ktoré si vyžadujú značné mzdové náklady pre štandardizátorov a iných špecialistov. Len výsledok bude stále približný! Takže stojí za to, ak je k dispozícii oveľa lacnejšie matematické modelovanie!
V tomto článku som sa len dotkol špičky ľadovca s názvom zber, spracovanie a praktické využitieštatistické údaje. Či sa mi podarilo vzbudiť váš záujem o túto tému alebo nie, dúfam, že sa dozviem z komentárov a hodnotenia článku vo vyhľadávačoch.
Nastolená otázka aproximácie funkcie jednej premennej má široký praktické využitie v rôznych oblastiachživota. Ale riešenie problému aproximácie funkcie je oveľa užitočnejšie niekoľko nezávislých premenné…. Prečítajte si o tom a ešte viac v nasledujúcich článkoch blogu.
Prihlásiť sa na odber pre oznámenia článkov v okne umiestnenom na konci každého článku alebo v okne v hornej časti stránky.
Nezabudni potvrdiť predplatné kliknutím na odkaz v liste, ktorý vám príde na určenú poštu (môže prísť do priečinka « Nevyžiadaná pošta » )!!!
So záujmom si prečítam vaše komentáre, milí čitatelia! Napíšte!
P.S. (04.06.2017)
Veľmi presná, krásna náhrada tabuľkových údajov s jednoduchou rovnicou.
Nie ste spokojní so získanou presnosťou aproximácie (R 2<0,95) или вид и набор функций, предлагаемые MS Excel?
Nelahodí oku veľkosť výrazu a tvar čiary aproximačného polynómu vysokého stupňa?
Kontaktujte prostredníctvom stránky "" pre presnejší a kompaktnejší výsledok aproximácie vašich tabuľkových údajov a za účelom osvojenia si jednoduchej techniky riešenia problémov s vysokou presnosťou aproximácie funkciou jednej premennej.
Pri použití navrhovaného algoritmu akcií bola nájdená veľmi kompaktná funkcia, ktorá poskytuje najvyššiu presnosť aproximácie: R 2 = 0,9963 !!!