Metodo di approssimazione in Microsoft Excel

Tra i vari metodi di previsione hè impossibile micca distinguere l'approssimazione. Cù u so aiutu, pudete fà calculi approssimativi è calculà i indicatori previsti sostituendu l'oggetti originali cù quelli più semplici. In Excel, ci hè ancu a possibilità di aduprà stu metudu per prognosi è analisi. Dai un ochju à cume stu metudu pò esse applicatu à u prugrammu specificatu cù strumenti integrati.

Esecuzione di l'approssimazione

U nomu di stu metudu deriva da a parola latina Prima - "vicinu", è una approssimazione simplificendu è lisificà indicatori cunnisciuti, cummendendu in una tendenza è hè a so basa. Ma questu metudu pò esse adupratu micca solu per prognosi, ma dinò per ricercà i risultati esistenti. Dopu tuttu, approssimazione hè, in realtà, una simplificazione di i dati originali, è una versione simplificata hè più faciule da esplore.

L'uttrenu principali cù u quale a lisciatura hè svolta in Excel hè a custruzzioni di una linea di tendenza. L'ultimu puntu hè chì, basatu nantu à l'indicatori dispunibili, a pianificazione di a funzione hè cumpletata per i periodi futuri. U scopu principale di a linea di tendenza, cume ùn hè micca difficiule di ghjunghje, hè di fà prevedenze o identificà una tendenza generale.

Ma pò esse custruitu cù unu di i cinque tippi di approssimazione:

• Lineare;
• Esponenziale;
• Logaritmicu;
• Polinomiale;
• Potenza.

Considerà ciascuna di e opzioni in dettaglio in più separatu.

Lezione: Cumu custruisce una linea di tendenza in Excel

Metudu 1: Rallentu lineare

Prima di tutti, cunsideremu a più simile approssimazione, cioè, aduprendu una funzione lineare. Aduprà quant'ellu di più in dettu, postu chì spiegamu i punti generali chì sò tipichi di l'altri metudi, cioè, trama è qualchì altre sfumature chì ùn aburdaremo micca quandu consideranu l'uppunimenti successivi.

Prima di tuttu, truvamu un graficu nantu à u quali baseremu una procedura di lisciatura. Per fà un graficu, pigliemu una tavula in cui u costi unitariu di produzione pruduttu da un'impresa è u profittu corrispondente in un periodu determinatu sò indicati mensilmente. A funzione grafica chì custruemu rifletteva a dipindenza di l'incremento di prufitti nantu à a diminuzione di u costu di produzione.

1. Per lanciari u grafico, prima di quì, selezziunate e colonne "Costu di produzione unitu" è "Prufittu". Dopu à chì passà à a tab "Inserisci". Dopu cliccate nantu à u bottinu in u blocu di u "Diagrammi" "Spot". In a lista chì si apre, selezziunu u nome "Dot cù curre lisse è marcatori". Hè stu tippu di grafie chì hè più adatta per u travagliu cù a linea di tendenza, è cusì, per l'applicazione di u metudu di approssimazione in Excel.



2. Pianu custruitu.



3. Per aghjunghje una linea di tendenza, selezziunela cliccendu nantu à u cliccu giuvuli di u mouse Un menu contestuale vene. Sceglite un articulu in essa "Aghjuntu una linea di tendenza ...".

   

   Ci hè un'altra opzione di aghjunghje. In un gruppu addizionale di tabs nantu à u nastro "U travagliu cù Grafici" move in tab "Layout". Next in u casella di attrezzi "Analisi" cliccate nantu à u bottinu "Linea di tendenza". A lista si aprite. Chì avemu bisognu di applicà una approssimazione lineare, da e pusizioni presentate si scelenu "Approssimazione lineare".



4. Tuttavia, se avete selezionatu a prima opzione di azzioni cù l'addizione da u menù contestuale, allora a finestra di u formatu si aprirà.

   In u blocu di parametri "Custruendu una linea di tendenza (approssimazione è lisciatura)" mette u switch in situ "Linear".
   Se vulete, pudete piazzà una marcata vicina à a posizione "Mostra equazione nant'à u grafico". Dopu à quellu, u diagramma mostrerà l'equazione di a funzione di lisciatura.

   In u nostru casu, per confrontà e varie opzioni di approssimazione, hè impurtante à verificà a casella "Mette in u grafico u valore di una approssimazione fidabile (R ^ 2)". Stu indicatore pò varia da 0 finu à 1. Quale hè più altu, a più approssimazione (più affidabile). Si crede chì quandu u valore di stu indicatore 0,85 è a lisciatura più alta pò esse considerata affidabile, è se a figura hè inferiore, allora - no.

   Dopu avete avutu tutti i settori sopra ripresentati. Pressu u pulsante "Chiudi"ponu à u fondu di a finestra.



5. Comu pudete vede, a linea di tendenza hè tracciata in u grafico. In u approssimazione lineare, hè denotata da una linea dritta nera. Questu tipu di lisciatura pò esse applicatu in i casi più semplici, quandu i dati cambianu abbastanza à tempu è a dependenza di u valore di a funzione nantu à l'argumentu hè ovvia.

A lisciatura, chì hè usata in stu casu, hè descritta da a formula seguente:

y = ax + b

In u nostru casu particulare, a formula tace u seguitu forma:

y = -0.1156x + 72.255

A grandezza di a precisione di l'approssimazione hè uguale à noi 0,9418, chì hè un risultatu abbastanza accettabile, chì descrive a lisciatura cum'è affidabile.

Metudu 2: Approssimazione Esponenziale

Ora cunsideremu u tippu esponenziale di approssimazione in Excel.

1. Per cambià u tippu di linea di tendenza, selezziunu cliccendu sì u bottinu di dirittu di u mouse è in u menu di menu a sceglie selezziunate l'elemento "Formatu di linea di tendenza ...".



2. Dopu à quessa, a finestra di formattazione chì ci hè dighjà familiare hè lanciata. In u blocu per a selezzione di u tipu di approssimazione, setate u switch à "Esponenziale". U restu di l'impostazioni restanu uguale cum'è in u primu casu. Cliccate nantu à u bottinu "Chiudi".



3. Dopu à ciò, a linea di tendenza sarà tracciata. Comu pudete vede, quandu utilizza stu metudu, hà una forma leggermente curva. U livellu di cunfidenza hè 0,9592, chì hè più altu chè quandu usu approssimazione lineare. U metanu esponenziale hè adupratu prublemu quandu i valori cambianu prima rapidu è poi pigghianu una forma equilibrata.

A visione generale di a funzione di lisciatura hè cum'è seguitu:

y = esse ^ x

dove e - questu hè a basa di u logaritmu naturale.

In u nostru casu particulare, a formula hà presu a forma seguente:

y = 6282.7 * e ^ (- 0.012 * x)

Metudu 3: Firmaghju di Log

Avà hè u turnu di cunsiderà u metodo di approssimazione logaritmica.

1. Da u listessu modu cum'è in l'ultimu tempu, cù u menù contestuale, lanciate a finestra di mudellu di u mudellu in linea. Piazzà u switch in posizione "Logarithmic" è cliccate nantu à u pulsante "Chiudi".



2. Ci hè una procedura di custruzione di linea di tendenza cun approssimazione logaritmica. Come in u casu precedente, sta opzione hè di megliu ad aduprà quandu i dati anu cambiatu rapidamente, è poi fate un aspettu equilibratu. Comu pudete vede, u livello di cunfidenza hè 0,946. Questu hè più altu chè quandu usu u metudu lineare, ma inferiore a a qualità di a linea di tendenza cù u lisugamentu esponenziale.

In generale, a formula di lisciatura sembra quessa:

y = a * ln (x) + b

dove In hè a magnitudine di u logaritmu naturale. Quindi u nome di u metudu.

In u nostru casu, a formula tace u seguitu forma:

y = -62,81ln (x) +404.96

Metudu 4: lischje polinomiale

Hè ora di considerà u metudu di lisciatura polinomiale.

1. Andate in a finestra di mudellu di u mudellu, cum'è avete fattu più di una volta. In blocu "Custruendu una linea di tendenza" mette u switch in situ "Polynomial". À a destra di questu articulu hè un campu "Degree". Quandu sceglite "Polynomial" diventa attivu. Quì pudete specificà qualunu valore di putenza da 2 (setatu per difettu) à 6. St'indicatore determina u numeru di massimi è i minimi di a funzione. Quandu hè installatu un polinomu di u secondu gradu, solu un massimu hè descrittu, è quandu un polinomu di sestimu grado hè installatu, ponu esse discuprese finu à cinque massimi. Per cumincià, lascemu l'impostazioni di difettu, chì vogliu specificà u secondu. I restanti settori restanu uguale cume li ponemu in i metudi precedenti. Pressu u pulsante "Chiudi".



2. A linea di tendenza chì utilizza stu metudu hè stata custruita. Comu pudete vede, hè ancu più curva chì quandu usava approssimazione esponenziale. U livelli di cunfidenza hè più altu chè cù qualcunu di i metudi usati in precedenza, è hè 0,9724.

   

   Questu metudu pò esse applicatu più bè si i dati cambianu continuamente. A funzione chì descrive stu tipu di lisciatura hè così:

   y = a1 + a1 * x + a2 * x ^ 2 + ... + an * x ^ n

   In u nostru casu, a formula hà presu a forma seguente:

   y = 0,0015 * x ^ 2-1.7202 * x + 507.01

3. Avà da cambià u gradu di i polinomi per vede se u risultatu serà diversu. Ci ritornu à a finestra di formattazione. U tippu di approssimazione hè polinomu di sinistra, ma di fronte à questu in a finestra di graduu, ponemu u valori massimu pussibule - 6.



4. Comu pudete vede, dopu à ciò, a nostra linea di tendenza hà presu a forma di una curva pronunciata, in chì u numaru di alti hè di sei. U fiducia hà aumentatu ancu di più, facendu 0,9844.

A formula chì descrive stu tipu di lisciatura, hà presu a forma seguente:

y = 8E-08x ^ 6-0,0003x ^ 5 + 0.3725x ^ 4-269.33x ^ 3 + 109525x ^ 2-2E + 07x + 2E + 09

Metudu 5: Rallentu di putenza

In conclusione, cunsiderate u metodo di approssimazione di u putere in Excel.

1. Move a finestra "Format di linea di tendenza". Imposta u switch di vista lisciatura à a posizione "Potenza". Mettendu l'equazione è u livellu di cunfidenza, cum'è sempre, lasciateli attivu. Pressu u pulsante "Chiudi".



2. U prugramma forma una linea di tendenza. Comu pudete vede, in u nostru casu, hè una linea cù una leggera curva. U livellu di cunfidenza hè 0,9618chì hè una figura assai alta. Di tutti i metudi descritti sopra, u livello di cunfidenza hè statu più altu solu quandu utilizava u metudu polinomiale.

Stu metudu hè adupratu efficacemente in casi di cambiamenti intensivi di dati di funzione. Hè impurtante notà chì questu opzione hè applicabile solu sì a funzione è l'argumentu ùn accettanu micca valori negativi o zero.

A formula generale chì descrive stu metudu hè u seguitu:

y = bx ^ n

In u nostru casu particulare, simu questu:

y = 6E + 18x ^ (- 6.512)

Comu pudete vede, quandu utilizanu i dati specifici chì avemu usatu per l'esempiu, u metodo di approssimazione polinomiale cù u polinomu in u sestu gradu (0,9844), u livello più scardu di cunfidenza in u metudu lineare (0,9418). Ma quistione ùn significa micca chì a stessa tendenza sarà quandu usanu altri esempi. No, u livellu di efficienza di i metudi sopra descritti pò differisce significativamente, dipende da u tipu specificu di funzione per cui a linea di tendenza sarà custruita. Dunque, se u metudu sceltu hè più efficace per sta funzione, quessa ùn significa micca chì sarà ancu ottimale in un'altra situazione.

Se ùn pudete micca determinà immediatamente, basatu annantu à e raccomandazioni sopra riportate, chì tipu di approssimazione si adatta specificamente à u vostru casu, allora hà bisognu di pruvà tutti i metodi. Dopu avè custruutu una linea di tendenza è visualizà u so livello di fiducia, pudete sceglie a scelta migliore.