Informații teoretice

În practică, la modelarea diferitelor procese - în special, economice, fizice, tehnice, sociale - una sau alta metodă de calcul a valorilor aproximative ale funcțiilor din valorile lor cunoscute în anumite puncte fixe este utilizată pe scară largă.

Acest tip de problemă de aproximare a funcției apare adesea:

• la construirea unor formule aproximative pentru calcularea valorilor cantităților caracteristice ale procesului studiat folosind date tabelare obținute în urma experimentului;
• în integrarea numerică, diferențierea, rezolvarea ecuațiilor diferențiale etc.;
• dacă este necesar să se calculeze valorile funcțiilor în punctele intermediare ale intervalului considerat;
• la determinarea valorilor cantităților caracteristice ale unui proces în afara intervalului considerat, în special la predicție.

Acest articol discută capacitățile pachetului MS Excel pentru rezolvarea acestui tip de probleme, în plus, oferă metode și tehnici pentru construirea (crearea) regresiilor pentru funcțiile tabulate (care stă la baza analizei regresiei).

Excel are două opțiuni pentru a construi regresii.

1. Adăugarea regresiilor selectate (linii de tendință) la o diagramă construită pe baza unui tabel de date pentru caracteristica procesului studiat (disponibilă numai dacă a fost construită o diagramă);
2. Folosind funcțiile statistice încorporate ale foii de lucru Excel, permițându-vă să obțineți regresii (linii de tendință) direct pe baza tabelului de date sursă.

Adăugarea liniilor de tendință la un grafic

Pentru un tabel de date care descrie un proces și este reprezentat printr-o diagramă, Excel are un instrument eficient de analiză a regresiei care vă permite să:

• construiți pe baza metodei celor mai mici pătrate și adăugați cinci tipuri de regresii la diagramă, care modelează procesul studiat cu diferite grade de precizie;
• adăugați la diagramă ecuația de regresie construită;
• determinați gradul de corespondență a regresiei selectate cu datele afișate pe diagramă.

y = y(x)

unde x este o variabilă independentă care ia adesea valorile unei secvențe de numere naturale (1; 2; 3; ...) și produce, de exemplu, o numărătoare inversă a timpului procesului studiat (caracteristici).

1 . Regresia liniară este bună pentru modelarea caracteristicilor ale căror valori cresc sau scad la o rată constantă. Acesta este cel mai simplu model de construit pentru procesul studiat. Ea

y = mx + b

unde m este tangenta pantei de regresie liniara la axa x; b - coordonata punctului de intersecție al regresiei liniare cu axa ordonatelor.

2 . O linie de tendință polinomială este utilă pentru descrierea caracteristicilor care au mai multe extreme distincte (maxime și minime). Alegerea gradului polinomului este determinată de numărul de extreme ale caracteristicii studiate. Astfel, un polinom de gradul doi poate descrie bine un proces care are doar un maxim sau un minim; polinom de gradul al treilea - nu mai mult de două extreme; polinom de gradul al patrulea - nu mai mult de trei extreme etc.

În acest caz, linia de tendință este construită în conformitate cu ecuația:

y = c0 + c1x + c2x2 + c3x3 + c4x4 + c5x5 + c6x6

unde coeficienții c0, c1, c2,... c6 sunt constante ale căror valori sunt determinate în timpul construcției.

3 . Linia de tendință logaritmică este utilizată cu succes la modelarea caracteristicilor ale căror valori se modifică inițial rapid și apoi se stabilizează treptat.

Construit în conformitate cu ecuația:

y = c ln(x) + b

4 . O linie de tendință a legii puterii dă rezultate bune dacă valorile relației studiate sunt caracterizate de o schimbare constantă a ratei de creștere. Un exemplu de astfel de dependență este graficul mișcării uniform accelerate a unei mașini. Dacă există valori zero sau negative în date, nu puteți utiliza o linie de tendință de putere.

Construit în conformitate cu ecuația:

y = c xb

unde coeficienții b, c sunt constante.

5 . O linie de tendință exponențială ar trebui utilizată atunci când rata de modificare a datelor crește continuu. Pentru datele care conțin valori zero sau negative, acest tip de aproximare nu este, de asemenea, aplicabil.

Construit în conformitate cu ecuația:

y = c ebx

unde coeficienții b, c sunt constante.

La selectarea unei linii de tendință, Excel calculează automat valoarea lui R2, care caracterizează fiabilitatea aproximării: cu cât valoarea R2 este mai aproape de unitate, cu atât linia de tendință aproximează mai fiabil procesul studiat. Dacă este necesar, valoarea R2 poate fi întotdeauna afișată pe diagramă.

Determinat prin formula:

Pentru a adăuga o linie de tendință la o serie de date:

• activați o diagramă bazată pe o serie de date, adică faceți clic în zona diagramei. Elementul Diagramă va apărea în meniul principal;
• după ce faceți clic pe acest articol, pe ecran va apărea un meniu în care ar trebui să selectați comanda Adăugare linie de tendință.

Aceleași acțiuni pot fi implementate cu ușurință prin deplasarea cursorului mouse-ului peste graficul corespunzător uneia dintre seriile de date și făcând clic dreapta; În meniul contextual care apare, selectați comanda Adăugare linie de tendință. Caseta de dialog Trendline va apărea pe ecran cu fila Tip deschisă (Fig. 1).

După aceasta aveți nevoie de:

Selectați tipul de linie de tendință necesar în fila Tip (tipul Linear este selectat implicit). Pentru tipul Polinom, în câmpul Grad, specificați gradul polinomului selectat.

Dacă este necesar, accesând fila Parametri (Fig. 2), puteți seta următorii parametri pentru linia de tendință:

• schimbați numele liniei de tendință în câmpul Numele curbei de aproximare (netezite).
• setați numărul de perioade (înainte sau înapoi) pentru prognoză în câmpul Prognoză;
• afișați ecuația liniei de tendință în zona graficului, pentru care ar trebui să activați caseta de selectare „afișați ecuația pe diagramă”;
• afișați valoarea fiabilității aproximării R2 în zona diagramei, pentru care ar trebui să activați caseta de selectare Plasați valoarea fiabilității aproximării pe diagramă (R^2);
• setați punctul de intersecție al liniei de tendință cu axa Y, pentru care ar trebui să activați caseta de selectare pentru intersecția curbei cu axa Y într-un punct;
• Faceți clic pe butonul OK pentru a închide caseta de dialog.

Pe ecran va apărea caseta de dialog Trend Line Format (Fig. 3), care conține trei file: View, Type, Parameters, iar conținutul ultimelor două coincide complet cu file similare din caseta de dialog Trend Line (Fig. 1). -2). În fila Vizualizare, puteți seta tipul de linie, culoarea și grosimea acesteia.

Pentru a șterge o linie de tendință care a fost deja desenată, selectați linia de tendință de șters și apăsați tasta Ștergere.

Avantajele instrumentului de analiză de regresie considerată sunt:

• ușurința relativă de a construi o linie de tendință pe diagrame fără a crea un tabel de date pentru aceasta;
• o listă destul de largă de tipuri de linii de tendință propuse, iar această listă include cele mai frecvent utilizate tipuri de regresie;
• capacitatea de a prezice comportamentul procesului studiat printr-un număr arbitrar (în limitele bunului simț) de pași înainte, precum și înapoi;
• capacitatea de a obține ecuația liniei de tendință în formă analitică;
• posibilitatea, dacă este necesar, de a obține o evaluare a fiabilității aproximării.

Nu puteți adăuga linii de tendință la seriile de date în diagrame 3D, normalizate, radar, plăcinte și gogoși.

Folosind funcțiile încorporate ale Excel

Excel are, de asemenea, un instrument de analiză de regresie pentru trasarea liniilor de tendință în afara zonei diagramei. Există o serie de funcții ale foii de lucru statistice pe care le puteți utiliza în acest scop, dar toate vă permit doar să construiți regresii liniare sau exponențiale.

Excel are mai multe funcții pentru construirea regresiei liniare, în special:

• TENDINŢĂ;
• LINEST;
• PANTĂ și TĂIERE.

Precum și câteva funcții pentru construirea unei linii de tendință exponențială, în special:

• ÎNĂLŢIME;
• LGRFPRIBL.

Avantajele instrumentului de funcții încorporate pentru analiza regresiei sunt:

• un proces destul de simplu, uniform de generare a serii de date ale caracteristicii studiate pentru toate funcțiile statistice încorporate care definesc liniile de tendință;
• metodologie standard pentru construirea liniilor de tendință bazate pe serii de date generate;
• capacitatea de a prezice comportamentul procesului studiat prin numărul necesar de pași înainte sau înapoi.

Trebuie remarcat faptul că autorii nu și-au propus să prezinte cursul analizei de regresie cu niciun grad de completitudine. Sarcina sa principală este de a arăta, folosind exemple specifice, capacitățile pachetului Excel la rezolvarea problemelor de aproximare; să demonstreze ce instrumente eficiente are Excel pentru a construi regresii și prognoză; ilustrează modul în care astfel de probleme pot fi rezolvate relativ ușor chiar și de către un utilizator care nu are cunoștințe extinse de analiză de regresie.

Exemple de rezolvare a unor probleme specifice

Să ne uităm la rezolvarea unor probleme specifice utilizând instrumentele Excel enumerate.

Problema 1

Cu un tabel de date privind profitul unei întreprinderi de transport auto pe perioada 1995-2002. trebuie să faceți următoarele:

1. Construiți o diagramă.
2. Adăugați în diagramă linii de tendință liniare și polinomiale (pătratice și cubice).
3. Folosind ecuațiile liniei de tendință, obțineți date tabelare despre profiturile întreprinderii pentru fiecare linie de tendință pentru 1995-2004.
4. Faceți o prognoză pentru profitul întreprinderii pentru 2003 și 2004.

Rezolvarea problemei

1. În intervalul de celule A4:C11 din foaia de lucru Excel, introduceți foaia de lucru prezentată în Fig. 4.
2. După ce am selectat intervalul de celule B4:C11, construim o diagramă.
3. Activăm diagrama construită și, conform metodei descrise mai sus, după selectarea tipului de linie de tendință în caseta de dialog Linie de tendință (vezi Fig. 1), adăugăm alternativ în diagramă linii de tendință liniare, pătratice și cubice. În aceeași casetă de dialog, deschideți fila Parametri (vezi Fig. 2), în câmpul Numele curbei de aproximare (netezite), introduceți numele tendinței care se adaugă, iar în câmpul Forecast forward for: periods, setați valoarea 2, deoarece se preconizează realizarea unei previziuni de profit pentru doi ani înainte. Pentru a afișa ecuația de regresie și valoarea de fiabilitate a aproximării R2 în zona diagramei, activați casetele de selectare afișare ecuație pe ecran și plasați valoarea de fiabilitate a aproximării (R^2) pe diagramă. Pentru o mai bună percepție vizuală, schimbăm tipul, culoarea și grosimea liniilor de tendință construite, pentru care folosim fila View din caseta de dialog Trend Line Format (vezi Fig. 3). Diagrama rezultată cu linii de tendință adăugate este prezentată în Fig. 5.
4. Pentru a obține date tabelare privind profiturile întreprinderii pentru fiecare linie de tendință pentru 1995-2004. Să folosim ecuațiile liniei de tendință prezentate în Fig. 5. Pentru a face acest lucru, în celulele din intervalul D3:F3, introduceți informații text despre tipul liniei de tendință selectate: Tendință liniară, Tendință patratică, Tendință cubică. Apoi, introduceți formula de regresie liniară în celula D4 și, folosind marcatorul de umplere, copiați această formulă cu referințe relative la intervalul de celule D5:D13. Trebuie remarcat faptul că fiecare celulă cu o formulă de regresie liniară din intervalul de celule D4:D13 are ca argument o celulă corespunzătoare din intervalul A4:A13. În mod similar, pentru regresia pătratică, completați intervalul de celule E4:E13, iar pentru regresia cubică, completați intervalul de celule F4:F13. Astfel, a fost realizată o prognoză a profitului întreprinderii pentru 2003 și 2004. folosind trei tendințe. Tabelul de valori rezultat este prezentat în Fig. 6.

Problema 2

1. Construiți o diagramă.
2. Adăugați în grafic linii de tendință logaritmice, de putere și exponențiale.
3. Deduceți ecuațiile liniilor de tendință obținute, precum și valorile de fiabilitate ale aproximării R2 pentru fiecare dintre ele.
4. Folosind ecuațiile liniei de tendință, obțineți date tabelare despre profitul întreprinderii pentru fiecare linie de tendință pentru 1995-2002.
5. Faceți o prognoză a profitului companiei pentru 2003 și 2004 folosind aceste linii de tendință.

Rezolvarea problemei

Urmând metodologia dată în rezolvarea problemei 1, obținem o diagramă cu linii de tendință logaritmice, de putere și exponențiale adăugate acesteia (Fig. 7). În continuare, folosind ecuațiile liniei de tendință obținute, completăm un tabel de valori pentru profitul întreprinderii, inclusiv valorile prezise pentru 2003 și 2004. (Fig. 8).

În fig. 5 și fig. se poate observa că modelul cu tendință logaritmică corespunde celei mai mici valori a fiabilității aproximării

R2 = 0,8659

Cele mai mari valori ale lui R2 corespund modelelor cu tendință polinomială: pătratică (R2 = 0,9263) și cubică (R2 = 0,933).

Problema 3

Cu tabelul de date privind profitul unei întreprinderi de transport cu motor pentru perioada 1995-2002, prezentat în sarcina 1, trebuie să efectuați următorii pași.

1. Obțineți serii de date pentru linii de tendință liniare și exponențiale folosind funcțiile TREND și GROW.
2. Folosind funcțiile TREND și GROWTH, faceți o prognoză a profitului întreprinderii pentru 2003 și 2004.
3. Construiți o diagramă pentru datele originale și seria de date rezultate.

Rezolvarea problemei

Să folosim foaia de lucru pentru problema 1 (vezi Fig. 4). Să începem cu funcția TREND:

1. selectați intervalul de celule D4:D11, care trebuie completat cu valorile funcției TREND corespunzătoare datelor cunoscute despre profitul întreprinderii;
2. Apelați comanda Funcție din meniul Inserare. În caseta de dialog Function Wizard care apare, selectați funcția TREND din categoria Statistical, apoi faceți clic pe butonul OK. Aceeași operațiune poate fi efectuată făcând clic pe butonul (Insert Function) din bara de instrumente standard.
3. În caseta de dialog Function Arguments care apare, introduceți intervalul de celule C4:C11 în câmpul Known_values_y; în câmpul Known_values_x - intervalul de celule B4:B11;
4. Pentru a face formula introdusă să devină o formulă matrice, utilizați combinația de taste + + .

Ca urmare, intervalul de celule D4:D11 este umplut cu valorile corespunzătoare ale funcției TREND (Fig. 9).

1. selectați intervalul de celule D12:D13 în care vor fi introduse valorile prezise de funcția TREND.
2. apelați funcția TREND și în caseta de dialog Function Arguments care apare, introduceți în câmpul Known_values_y - intervalul de celule C4:C11; în câmpul Known_values_x - intervalul de celule B4:B11; iar în câmpul New_values_x - intervalul de celule B12:B13.
3. transformați această formulă într-o formulă matrice folosind combinația de taste Ctrl + Shift + Enter.
4. Formula introdusă va arăta astfel: =(TREND(C4:C11;B4:B11;B12:B13)), iar intervalul de celule D12:D13 va fi completat cu valorile prezise ale funcției TREND (vezi Fig. 9).

Seria de date este completată în mod similar utilizând funcția GROWTH, care este utilizată în analiza dependențelor neliniare și funcționează exact în același mod ca omologul său liniar TREND.

Figura 10 prezintă tabelul în modul de afișare a formulei.

Pentru datele inițiale și seria de date obținute, diagrama prezentată în Fig. 11.

Problema 4

Cu tabelul de date privind primirea cererilor de servicii de către serviciul de expediere al unei întreprinderi de transport auto pentru perioada de la 1 la 11 a lunii în curs, trebuie să efectuați următoarele acțiuni.

1. Obțineți serii de date pentru regresia liniară:folosind funcțiile SLOPE și CUT; folosind funcția LINEST.
2. Obțineți o serie de date pentru regresia exponențială folosind funcția LGRFPRIBL.
3. Folosind funcțiile de mai sus, faceți o prognoză despre primirea cererilor către serviciul de expediere pentru perioada 12-14 a lunii în curs.
4. Creați o diagramă pentru seriile de date originale și primite.

Rezolvarea problemei

Rețineți că, spre deosebire de funcțiile TREND și GROWTH, niciuna dintre funcțiile enumerate mai sus (SLOPE, INTERCEPT, LINEST, LGRFPRIB) nu este regresie. Aceste funcții joacă doar un rol de susținere, determinând parametrii de regresie necesari.

Pentru regresiile liniare și exponențiale construite folosind funcțiile SLOPE, INTERCEPT, LINEST, LGRFPRIB, aspectul ecuațiilor acestora este întotdeauna cunoscut, spre deosebire de regresiile liniare și exponențiale corespunzătoare funcțiilor TREND și GROWTH.

1 . Să construim o regresie liniară cu ecuația:

y = mx+b

Pentru a face acest lucru, efectuăm următoarele acțiuni:

1. introduceți tabelul original în intervalul de celule A4:B14;
2. valoarea parametrului m va fi determinată în celula C19. Selectați funcția Pantă din categoria Statistică; introduceți intervalul de celule B4:B14 în câmpul cunoscute_valori_y și intervalul de celule A4:A14 în câmpul cunoscute_valori_x. Formula va fi introdusă în celula C19: =SLOPE(B4:B14,A4:A14);
3. Folosind o tehnică similară, se determină valoarea parametrului b din celula D19. Și conținutul său va arăta astfel: =SEGMENT(B4:B14,A4:A14).Astfel, valorile parametrilor m și b necesari pentru construirea regresiei liniare vor fi stocate în celulele C19, respectiv D19;
4. Apoi, introduceți formula de regresie liniară în celula C4 sub forma: =$C*A4+$D. În această formulă, celulele C19 și D19 sunt scrise cu referințe absolute (adresa celulei nu ar trebui să se schimbe în timpul unei posibile copii). Semnul de referință absolut $ poate fi tastat fie de la tastatură, fie folosind tasta F4, după plasarea cursorului pe adresa celulei. Folosind mânerul de umplere, copiați această formulă în intervalul de celule C4:C17. Obținem seria de date necesară (Fig. 12). Datorită faptului că numărul de aplicații este un întreg, ar trebui să setați formatul numeric cu numărul de zecimale la 0 în fila Număr a ferestrei Format de celule.

y = mx+b

Pentru a face acest lucru:

1. Introduceți funcția LINEST ca formulă matrice în intervalul de celule C20:D20: =(LINEST(B4:B14,A4:A14)). Ca rezultat, obținem valoarea parametrului m în celula C20 și valoarea parametrului b în celula D20;
2. introduceți formula în celula D4: =$C*A4+$D;
3. copiați această formulă folosind marcatorul de umplere în intervalul de celule D4:D17 și obțineți seria de date dorită.

y = bmx

În fig. Figura 13 prezintă un tabel în care puteți vedea funcțiile pe care le folosim cu intervalele de celule necesare, precum și formulele.

Pentru datele inițiale și seria de date obținute, diagrama prezentată în Fig. 14.

Pentru a ilustra vizual tendințele prețurilor, se folosește o linie de tendință. Elementul analizei tehnice este o imagine geometrică a valorilor medii ale indicatorului analizat.

Să vedem cum să adăugați o linie de tendință la un grafic în Excel.

Adăugarea unei linii de tendință la un grafic

De exemplu, să luăm prețurile medii ale petrolului din 2000 din surse deschise. Să introducem datele pentru analiză în tabel:

O linie de tendință în Excel este un grafic al unei funcții de potrivire. De ce este nevoie - să se facă prognoze pe baza datelor statistice. În acest scop, este necesară extinderea liniei și determinarea valorilor acesteia.

Dacă R2 = 1, atunci eroarea de aproximare este zero. În exemplul nostru, alegerea aproximării liniare a dat fiabilitate scăzută și rezultate slabe. Prognoza va fi inexactă.

Atenţie!!! Nu puteți adăuga o linie de tendință la următoarele tipuri de grafice și diagrame:

• petală;
• circular;
• suprafaţă;
• inelar;
• volum;
• cu acumulare.

Ecuația liniei de tendință în Excel

În exemplul de mai sus, aproximarea liniară a fost aleasă doar pentru a ilustra algoritmul. După cum a arătat valoarea fiabilității, alegerea nu a avut succes în totalitate.

Ar trebui să alegeți tipul de afișare care ilustrează cel mai bine tendința în introducerea utilizatorului. Să ne uităm la opțiuni.

Aproximație liniară

Imaginea sa geometrică este o linie dreaptă. Prin urmare, aproximarea liniară este utilizată pentru a ilustra un indicator care crește sau scade la o rată constantă.

Să luăm în considerare numărul condiționat de contracte încheiate de manager pe o perioadă de 10 luni:

Pe baza datelor din tabelul Excel, vom crea un grafic de dispersie (va ajuta la ilustrarea tipului liniar):

Selectați graficul - „adăugați linia de tendință”. În parametri, selectați tipul liniar. Adăugăm valoarea de încredere a aproximării și ecuația liniei de tendință în Excel (doar bifați casetele din partea de jos a ferestrei „Parametri”).

Obtinem rezultatul:

Fiţi atenți! Cu tipul de aproximare liniară, punctele de date sunt situate cât mai aproape de linia dreaptă. Această vizualizare folosește următoarea ecuație:

y = 4,503x + 6,1333

• unde 4,503 este indicele pantei;
• 6,1333 – deplasări;
• y – succesiune de valori,
• x – numărul perioadei.

Linia dreaptă din grafic arată o creștere constantă a calității muncii managerului. Valoarea de fiabilitate a aproximării este 0,9929, ceea ce indică un acord bun între linia calculată și datele originale. Prognozele trebuie să fie exacte.

Pentru a prezice numărul de contracte încheiate, de exemplu, în perioada 11, trebuie să înlocuiți numărul 11 ​​în loc de x în ecuație. Pe parcursul calculelor, aflăm că în a 11-a perioadă acest manager va încheia 55-56 de contracte.

Linie de tendință exponențială

Acest tip este util dacă valorile de intrare se modifică la o rată în continuă creștere. Potrivirea exponențială nu este utilizată atunci când există caracteristici zero sau negative.

Să construim o linie de tendință exponențială în Excel. Să luăm, de exemplu, valorile condiționate ale furnizării productive de energie electrică în regiunea X:

Construim un program. Adăugați o linie exponențială.

Ecuația arată astfel:

y = 7,6403е^-0,084x

• unde 7,6403 și -0,084 sunt constante;
• e este baza logaritmului natural.

Indicatorul de fiabilitate a aproximării a fost 0,938 – curba corespunde datelor, eroarea este minimă, prognozele vor fi exacte.

Linie de tendință logaritmică în Excel

Este utilizat pentru următoarele modificări ale indicatorului: mai întâi, creștere sau scădere rapidă, apoi stabilitate relativă. Curba optimizată se adaptează bine acestui „comportament” al cantității. Tendința logaritmică este potrivită pentru prognoza vânzărilor unui produs nou care tocmai este introdus pe piață.

În etapa inițială, sarcina producătorului este de a crește baza de clienți. Când un produs are propriul cumpărător, acesta trebuie păstrat și servit.

Să construim un grafic și să adăugăm o linie de tendință logaritmică pentru a estima vânzările unui produs condiționat:

R2 este aproape ca valoare de 1 (0,9633), ceea ce indică o eroare minimă de aproximare. Să prognozăm volumele vânzărilor în perioadele ulterioare. Pentru a face acest lucru, trebuie să înlocuiți numărul perioadei din ecuație în loc de x.

De exemplu:

Pentru calcularea cifrelor de prognoză s-a folosit o formulă de forma: =272,14*LN(B18)+287,21. Unde B18 este numărul perioadei.

Linie de tendință polinomială în Excel

Această curbă se caracterizează prin creșteri și scăderi variabile. Pentru polinoame (polinoame), gradul este determinat (de numărul de valori maxime și minime). De exemplu, un extrem (minim și maxim) este al doilea grad, două extreme sunt al treilea grad, trei sunt al patrulea.

Tendința polinomială în Excel este utilizată pentru a analiza un set mare de date despre o cantitate instabilă. Să ne uităm la exemplul primului set de valori (prețurile petrolului).

Pentru a obține o astfel de valoare a fiabilității aproximării (0,9256), a fost necesar să o setăm la gradul 6.

Descărcați exemple de diagrame cu o linie de tendință

Dar această tendință ne permite să facem prognoze mai mult sau mai puțin precise.

Salutări, dragi tovarăși! Astăzi ne vom uita la una dintre metodele subiective de tranzacționare - tranzacționarea folosind linii de tendință. Să ne uităm la următoarele întrebări:

1) Ce este o tendință (acesta este important ca punct de plecare)
2) Desenarea liniilor de tendință
3) Utilizare în comerțul practic
4) Subiectivitatea metodei

1) Ce este o tendință
_________________
Înainte de a trece la construirea unei linii de tendință, trebuie să înțelegeți tendința în sine. Nu vom intra în dispute academice și pentru simplitate vom accepta următoarea formulă:

O tendință (în sus) este o secvență de creșteri și scăderi, fiecare maxim (și scăzut) ulterioar fiind mai mare decât cele anterioare.

O tendință (în scădere) este o secvență de scădere (scădere) maxime și scăderi, în care fiecare minim (și maxim) ulterioară este MAI JUC decât precedentul.

O linie de tendință este o linie trasată între două maxime (dacă tendința este descendentă) sau două minime (dacă tendința este ascendentă). Adică, în esență, linia de tendință ne arată că există o tendință pe grafic! Dar poate să nu existe (în cazul unui apartament).

2) Desenarea liniilor de tendință
____________________________

Aceasta este cea mai dificilă întrebare! Am văzut discuții care au durat multe pagini despre CUM SĂ DESENEZ CORECT o linie de tendință! Dar trebuie nu doar să construim, ci și să schimbăm pe el...

Pentru a construi o linie de tendință, trebuie să aveți cel puțin două maxime (tendință descendentă) sau două minime (tendință ascendentă). Trebuie să conectăm aceste puncte extreme cu o linie.

Este important să urmați următoarele reguli atunci când construiți linii:

— Unghiul liniei de tendință este important. Cu cât unghiul de înclinare este mai abrupt, cu atât este mai puțin fiabil.
— Este optim să construiți o linie folosind două puncte. Dacă construiți pe trei sau mai multe puncte, fiabilitatea liniei de tendință scade (defalcarea acesteia este probabilă).
- Nu încercați să construiți o linie în orice condiții. Dacă nu îl puteți desena, atunci cel mai probabil nu există nicio tendință. Prin urmare, acest instrument nu este potrivit pentru utilizare în condițiile actuale de piață.

Aceste reguli vă vor ajuta să construiți corect linii de tendințe!

3) Tranzacționarea de-a lungul liniilor de tendință
____________________________

Avem două posibilități fundamental diferite:
A) Folosiți linia ca nivel de suport (rezistență) pentru a intra de-a lungul ei în direcția tendinței
B) Folosiți linia de tendință Forex pentru a juca pentru o spargere (inversare) a tendinței.

Ambele metode sunt bune dacă știi să „le gătești corect”.

Deci, am construit o linie folosind două puncte. De îndată ce prețul atinge linia, trebuie să intrăm pe piață în direcția trendului existent. Pentru a intra, folosim ordine de tipul „limită de cumpărare sau limită de vânzare”.

Totul aici este simplu și clar. Singurul lucru pe care trebuie să-l rețineți este că, cu cât prețul testează mai des linia de tendință, pornind de la ea, cu atât este mai mare probabilitatea ca următoarea atingere să fie o erupție a liniei!

Dacă vrem să jucăm pentru a sparge linia de tendință, atunci trebuie să acționăm puțin diferit:
1) Așteptați ca linia să se atingă
2) În așteptarea unei reveniri
3) Plasați un ordin stop de cumpărare (sau oprire de vânzare) în caseta de selectare rezultată.
Fii atent la imagine.

Am așteptat să apară bifa și am plasat un ordin stop de cumpărare la maximum.

După ceva timp, comanda a fost declanșată și am intrat pe piață.
Apare o întrebare firească: de ce a fost imposibil să intri pe piață imediat?
Ideea este că nu știm dacă testarea liniei de tendință va avea succes sau nu. Și așteptând „căpușa” ne creștem dramatic șansele de succes (eliminăm semnalele false).

4) Subiectivitatea metodei
_________________________

Totul pare simplu? De fapt, folosind această metodă, vom întâmpina următoarele dificultăți:
A) Unghiul pantei liniei (puteți construi oricând linii de tendință cu pante diferite.
B) Ce este considerat o ruptură a unei linii de tendință (câte puncte sau puncte procentuale ar trebui prețul să „rupă” linia pentru a o considera o ruptură)?
Î) Când este considerată o linie „învechită” și construită una nouă?

Fii atent la imagine.

Linia roșie indică una dintre opțiunile de design. Un comerciant fără experiență ar putea să tragă linia în acest fel (și să plătească pentru asta).

Experiența practică este importantă în această chestiune. Adică, nu este posibil să reduceți totul la câteva reguli simple de construcție. Acesta este motivul pentru care nu există un indicator de linie de tendință. Mai exact, poate să existe, dar le construiește „strâmb” și incorect. Această tehnică a fost inițial „adaptată” la experiența și abilitățile comerciantului.

Personal, rareori folosesc liniile de tendințe ca instrument de sine stătător. Dar, cu toate acestea, vorbesc despre ele dintr-un motiv simplu. Cert este că mulți alți comercianți le folosesc. Prin urmare, noi (tu și eu) trebuie să fim conștienți de tehnicile concurenților noștri.

Dacă acest instrument este necesar în tranzacționarea dvs., vă decideți!

Noroc și tranzacționare fericită. Arthur.
blog-forex.org

Postări înrudite:

Conceptul de tranzacționare în tendințe (video)

Modele în tendințe (cifre)

Videoclip pe acest subiect:

Partea 10. Selectarea formulelor conform orarului. Linia de tendințe

Anterior12345678910111213141516Următorul

Pentru problemele discutate mai sus, a fost posibil să se construiască o ecuație sau un sistem de ecuații.

Dar în multe cazuri, la rezolvarea problemelor practice, există doar date experimentale (rezultate măsurători, statistice, de referință, experimentale). Folosindu-le, cu un anumit grad de proximitate, ei încearcă să reconstruiască o formulă empirică (ecuație), care poate fi folosită pentru a găsi o soluție, modela, evalua soluții și face previziuni.

Procesul de selectare a unei formule empirice P(x) pentru dependenta cu experienta F(x) numit apropiere(netezire). Pentru dependențe cu o necunoscută, Excel folosește grafice, iar pentru dependențe cu multe necunoscute, perechi de funcții din grup Statistic LINEAR și TREND, LGRFPRIBL și GROWTH.

Această secțiune discută despre aproximarea datelor experimentale folosind grafice Excel: pe baza datelor, se creează un grafic și un linie de tendință , adică o funcţie de aproximare care se apropie de dependenţa experimentală cu gradul maxim de apropiere.

Se estimează gradul de similitudine al funcției selectate coeficientul de determinare R2 . Dacă nu există alte considerații teoretice, atunci alegeți o funcție cu un coeficient R 2 tinzând spre 1. Rețineți că selectarea formulelor folosind linia de tendință ne permite să stabilim atât tipul formulei empirice, cât și să determinăm valorile numerice ale parametrilor necunoscuți.

Excel oferă 5 tipuri de funcții de aproximare:

1. Linear – y=cx+b. Aceasta este cea mai simplă funcție care reflectă creșterea și declinul datelor la o rată constantă.

2. Polinom – y=c 0 +c 1 x+c 2 x 2 +…+c 6 x 6. Funcția descrie alternativ creșterea și descreșterea datelor. Un polinom de gradul 2 poate avea o extremă (min sau max), un polinom de gradul 3 - până la 2 extreme, un polinom de gradul 4 - până la 3 etc.

3. Logaritmic – y=c ln x+b. Această funcție descrie date în creștere (în scădere) rapidă, care apoi se stabilizează.

4. Putere – y=cx b, (X>0i y>0). Funcția reflectă date cu o rată de creștere constantă (în scădere).

5. Exponențial – y=ce bx, (e– baza logaritmului natural). Funcția descrie date în creștere (în scădere) rapidă, care apoi se stabilizează.

Prognoza este un element foarte important în aproape orice domeniu de activitate, de la economie la inginerie. Există o cantitate mare de software specializat în acest domeniu. Din păcate, nu toți utilizatorii știu că un procesor obișnuit de foi de calcul Excel are în arsenal instrumente pentru efectuarea prognozelor, care nu sunt cu mult inferioare ca eficiență față de programele profesionale. Să aflăm care sunt aceste instrumente și cum să facem o prognoză în practică.

Scopul oricărei prognoze este de a identifica tendința actuală și de a determina rezultatul așteptat în raport cu obiectul studiat la un anumit moment în timp în viitor.

Metoda 1: linia de tendință

Unul dintre cele mai populare tipuri de prognoză grafică în Excel este extrapolarea realizată prin construirea unei linii de tendință.

Să încercăm să anticipăm valoarea profitului întreprinderii în 3 ani pe baza datelor despre acest indicator pentru ultimii 12 ani.

Metoda 2: operator FORECAST

Extrapolarea datelor tabelare se poate face folosind funcția standard Excel PREDICȚIE. Acest argument este clasificat ca instrument statistic și are următoarea sintaxă:

PREDICȚIE(X, valori_cunoscute_y, valori_cunoscute_x)

"X" este un argument pentru care trebuie determinată valoarea funcției. În cazul nostru, argumentul va fi anul pentru care ar trebui făcută prognoza.

„Valori y cunoscute”— o bază de valori cunoscute ale funcției. În cazul nostru, rolul său este jucat de valoarea profitului din perioadele anterioare.

„Valori cunoscute ale lui x”— acestea sunt argumentele care corespund valorilor cunoscute ale funcției. În rolul lor, folosim numerotarea anilor pentru care au fost colectate informații despre profiturile anilor anteriori.

Desigur, argumentul nu trebuie să fie neapărat o perioadă de timp. De exemplu, poate fi temperatura, iar valoarea funcției poate fi nivelul de expansiune al apei atunci când este încălzită.

Când se calculează în acest mod, se utilizează metoda regresiei liniare.

Să ne uităm la nuanțele utilizării operatorului PREDICȚIE pe un exemplu concret. Să luăm aceeași masă. Va trebui să cunoaștem prognoza de profit pentru 2018.

Dar nu uitați că, la fel ca atunci când construiți o linie de tendință, perioada de timp înainte de perioada de prognoză nu trebuie să depășească 30% din întreaga perioadă pentru care a fost acumulată baza de date.

Metoda 3: operator TREND

Pentru prognoză, puteți utiliza încă o funcție - TENDINŢĂ. De asemenea, se încadrează în categoria operatorilor statistici. Sintaxa sa este în multe privințe similară cu sintaxa instrumentului PREDICȚIE si arata cam asa:

TREND(Valori_y_cunoscute; valori_x_cunoscute; valori_x_nou; [const])

După cum vedem, argumentele „Valori y cunoscute”Şi „Valori cunoscute ale lui x” corespund pe deplin elementelor similare ale operatorului PREDICȚIE, și argumentul „Noi valori x” se potrivește cu argumentul "X" instrumentul anterior. În plus, la TENDINŢĂ există un argument suplimentar "Constant", dar nu este obligatoriu și se folosește numai în prezența unor factori constanți.

Acest operator este utilizat cel mai eficient atunci când există o dependență liniară a funcției.

Să vedem cum va funcționa acest instrument cu aceeași matrice de date. Pentru a compara rezultatele obținute, vom defini 2019 ca punct de prognoză.

Metoda 4: operator GROWTH

O altă funcție care poate fi folosită pentru a face prognoze în Excel este operatorul GROWTH. De asemenea, aparține grupului statistic de instrumente, dar, spre deosebire de cele anterioare, în calcul folosește nu o metodă de dependență liniară, ci una exponențială. Sintaxa acestui instrument arată astfel:

CREȘTERE(Valori cunoscute_y; valori cunoscute_x; valori_noi_x; [const])

După cum puteți vedea, argumentele acestei funcții repetă exact argumentele operatorului TENDINŢĂ, așa că nu ne vom opri asupra descrierii lor a doua oară, ci vom trece imediat la utilizarea acestui instrument în practică.

Metoda 5: operator LINEST

Operator LINEST Când calculează, folosește metoda aproximării liniare. Aceasta nu trebuie confundată cu metoda relației liniare utilizată de instrument TENDINŢĂ. Sintaxa sa arată astfel:

LINIE(Valori_y_cunoscute; valori_x_cunoscute; valori_x_nou;[const];[statistici])

Ultimele două argumente sunt opționale. Suntem familiarizați cu primele două din metodele anterioare. Dar poate ați observat că acestei funcție îi lipsește un argument care indică noile valori. Cert este că acest instrument determină doar modificarea sumei veniturilor pe unitate de perioadă, care în cazul nostru este egală cu un an, dar trebuie să calculăm separat rezultatul total adăugând rezultatul calculului operatorului la ultima valoare reală a profitului LINEST, înmulțit cu numărul de ani.

După cum putem vedea, profitul proiectat, calculat prin metoda aproximării liniare, în 2019 va fi de 4.614,9 mii de ruble.

Metoda 6: operator LGRFPRIBL

Ultimul instrument la care ne vom uita va fi LGRFPRIBL. Acest operator efectuează calcule pe baza metodei de potrivire exponențială. Sintaxa sa are următoarea structură:

LGRFPRIBL (Valori cunoscute_y; valori cunoscute_x; valori_nou_x; [const]; [statistici])

După cum puteți vedea, toate argumentele repetă complet elementele corespunzătoare ale funcției anterioare. Algoritmul de calcul al prognozei se va modifica ușor. Funcția va calcula o tendință exponențială, care va arăta de câte ori se va modifica suma veniturilor într-o perioadă, adică într-un an. Va trebui să găsim diferența de profit dintre ultima perioadă reală și prima perioadă planificată, să o înmulțim cu numărul de perioade planificate (3) și adăugați suma ultimei perioade efective la rezultat.

Valoarea estimată a profitului în 2019, care a fost calculată folosind metoda de aproximare exponențială, va fi de 4639,2 mii de ruble, ceea ce, din nou, nu este foarte diferit de rezultatele obținute prin calculul folosind metodele anterioare.

Am aflat cum să facem prognoze în Excel. Acest lucru se poate face grafic prin utilizarea unei linii de tendință și analitic prin utilizarea unui număr de funcții statistice încorporate. Prelucrarea datelor identice de către acești operatori poate duce la rezultate diferite. Dar acest lucru nu este surprinzător, deoarece toți folosesc metode de calcul diferite. Dacă fluctuația este mică, atunci toate aceste opțiuni, aplicabile unui anumit caz, pot fi considerate relativ fiabile.

Privind orice set de date distribuite în timp (seri temporale), putem determina vizual creșterea și scăderea indicatorilor pe care îi conține. Un model de creșteri și scăderi se numește tendință, care ne poate spune dacă datele noastre sunt în creștere sau în scădere.

Poate voi începe seria de articole despre prognoză cu cel mai simplu lucru - construirea unei funcții de trend. De exemplu, să luăm date despre vânzări și să construim un model care descrie dependența vânzărilor de timp.

Concepte de bază

Cred că toată lumea a fost familiarizată cu funcția liniară încă de la școală, tocmai aceasta este ceea ce stă la baza tendinței:

Y(t) = a0 + a1*t + E

Y este volumul vânzărilor, variabila pe care o vom explica în timp și de care depinde, adică Y(t);

t este numărul perioadei (numărul ordinal al lunii) care explică planul de vânzări Y;

a0 este coeficientul de regresie zero, care arată valoarea lui Y(t), în absența influenței factorului explicativ (t=0);

a1 este coeficientul de regresie, care arată cât de mult depinde indicatorul de vânzări studiat Y de factorul de influență t;

E sunt perturbații aleatorii care reflectă influența altor factori neluați în considerare în model, cu excepția timpului t.

Clădire model

Deci, știm volumul vânzărilor din ultimele 9 luni. Iată cum arată semnul nostru:

Următorul lucru pe care trebuie să-l facem este să determinăm coeficienții a0Şi a1 pentru a prognoza volumul vânzărilor pentru luna a 10-a.

Determinarea coeficienților de model

Construim un program. Pe orizontal vedem lunile amanate, pe verticala volumul vanzarilor:

În Foi de calcul Google selectăm Editor de diagrame -> Adiţionalși pune o căpușă lângă Linii de tendință. În setări selectăm Eticheta — EcuaţieŞi Arată R^2.

Dacă faceți totul în MS Excel, faceți clic dreapta pe diagramă și selectați „Adăugați o linie de tendință” din meniul derulant.

În mod implicit, este construită o funcție liniară. În dreapta, selectați „Afișați ecuația pe diagramă” și „Valoarea fiabilității aproximării R^2”.

Iată ce s-a întâmplat:

Pe grafic vedem ecuația funcției:

y = 4856*x + 105104

Descrie volumul vânzărilor în funcție de numărul lunii pentru care dorim să prognozăm aceste vânzări. În apropiere vedem coeficientul de determinare R^2, care indică calitatea modelului și cât de bine descrie vânzările noastre (Y). Cu cât este mai aproape de 1, cu atât mai bine.

Am R^2 = 0,75. Acesta este un indicator mediu, indică faptul că modelul nu ia în considerare niciun alt factor semnificativ în afară de timpul t, de exemplu, poate fi sezonalitate.

Noi prezicem

y = 4856*10 + 105104

Avem 153664 vânzări luna viitoare. Dacă adăugăm un nou punct în grafic, vedem imediat că R^2 s-a îmbunătățit.

În acest fel, puteți prognoza datele cu câteva luni înainte, dar fără a ține cont de alți factori, prognoza dvs. se va situa pe linia de tendință și nu va fi atât de informativă pe cât v-ați dori. În plus, o prognoză pe termen lung făcută în acest fel va fi foarte aproximativă.

Puteți crește acuratețea modelului adăugând sezonalitate la funcția de tendință, ceea ce vom face în articolul următor.

Ceea ce face diferită efectuarea sarcinilor pentru a construi o linie de tendință este că datele sursă pot fi un set de numere care nu au legătură între ele.

Prognoza folosind un program regulat este imposibilă, deoarece coeficientul său de determinism (R^2) va fi aproape de zero.

Acesta este motivul pentru care se folosesc funcții speciale.

Acum le vom construi, configura și analiza.

Versiune de construcție ușoară

Procesul de construire a unei linii de tendință constă din trei etape: introducerea datelor inițiale în Excel, trasarea unui grafic, selectarea unei linii de tendință și a parametrilor acesteia.

Să începem cu introducerea datelor.

1. Creați un tabel în Excel cu datele sursă.

(Figura 1)

2. Selectați celulele B3:B17 și mergeți la fila „Insert” și selectați „Graph”.

(Figura 2)

3. Odată ce graficul este creat, puteți adăuga subtitrări și un titlu.

Mai întâi, faceți clic stânga pe marginea graficului pentru a-l selecta.

Apoi accesați fila „Design” și selectați „Layout 1”.

(Figura 3)

4. Să trecem la construirea unei linii de tendințe. Pentru a face acest lucru, selectați din nou diagrama și accesați fila „Aspect”.

(Figura 4)

5. Faceți clic pe butonul „Linie de tendință” și selectați „potrivire liniară” sau „potrivire exponențială”.

(Figura 5)

Așa că am construit o linie de tendință primară, care poate să nu corespundă prea mult cu realitatea.

Acesta este rezultatul nostru intermediar.

(Figura 6)

Și, prin urmare, va trebui să ajustam parametrii liniei noastre de tendință sau să selectăm o altă funcție.

Versiune profesională: selectarea unei linii de tendință și setarea parametrilor

6. Faceți clic pe butonul „Linie de tendință” și selectați „Parametri suplimentari și linii de tendință”.

(Figura 7)

7. În fereastra „Format de linie de tendință”, bifam caseta de lângă „plasați valoarea de fiabilitate a aproximării R^2 pe diagramă și faceți clic pe butonul „închidere”.

Vedem în diagramă coeficientul R^2= 0,6442

(Figura 8)

8. Anulați modificările. Selectați graficul, faceți clic pe fila „Aspect”, butonul „Linie de tendință” și selectați „Nu”.

9. Accesați fereastra „Format linie de tendință”, dar pentru a selecta linia de tendință „Polinom”, schimbăm gradul, realizând indicatorii de coeficienți R^2 = 0,8321

(Figura 9)

Prognoza

Dacă trebuie să ghicim ce date ar putea fi obținute în următoarea măsurare, în fereastra „Format linie tendință”, indicăm numărul de perioade pentru care se face prognoza.

(Figura 10)

Pe baza prognozei, putem presupune că pe 25 ianuarie numărul de puncte marcate ar fi cuprins între 60 și 70.

Concluzie

Și, în sfârșit, dacă sunteți interesat de formula prin care este construită tendința, în secțiunea „Format de linie de tendință”, bifați caseta de lângă „afișați ecuația pe grafic”.

Acum știți cum să finalizați sarcina și să construiți o linie de tendințe, chiar și într-un program precum Excel 2010.

Pune întrebări, nu fi timid.