Mètode d'aproximació a Microsoft Excel

Entre els diversos mètodes de predicció és impossible no distingir l’aproximació. Amb la seva ajuda, podeu fer càlculs aproximats i calcular els indicadors previstos substituint els objectes originals per uns més senzills. A Excel, també hi ha la possibilitat d’utilitzar aquest mètode per a la previsió i l’anàlisi. Vegem com es pot aplicar aquest mètode al programa especificat amb eines integrades.

Execució de l’aproximació

El nom d’aquest mètode prové de la paraula llatina proxima, la seva aproximació, simplificant i suavitzant els indicadors, construint-los en una tendència i és la seva base. Però aquest mètode es pot utilitzar no només per a la previsió, sinó també per investigar els resultats existents. Després de tot, l'aproximació és, de fet, una simplificació de les dades originals, i una versió simplificada és més fàcil d'explorar.

L’eina principal amb la qual es realitza el suavitzat a Excel és la construcció d’una línia de tendència. El resultat final és que, segons els indicadors ja disponibles, l’horari de la funció es completa per a períodes futurs. El propòsit principal de la línia de tendència, ja que no és difícil d'endevinar, és fer previsions o identificar una tendència general.

Però es pot construir utilitzant un dels cinc tipus d'aproximació:

• Lineal;
• Exponencial;
• Logarítmica;
• Polinomi;
• Potència.

Penseu en cadascuna de les opcions amb més detall per separat.

Lliçó: Com crear una línia de tendència a Excel

Mètode 1: suavitzat lineal

En primer lloc, considerem l’aproximació més senzilla, és a dir, utilitzant una funció lineal. Ho parlarem amb més detall, ja que vam exposar els punts generals que són característics d'altres mètodes, és a dir, traçar i alguns altres matisos que no es faran en considerar les opcions posteriors.

En primer lloc, construirem un gràfic sobre el qual realitzarem el procediment de suavitzat. Per construir un gràfic, prenem una taula on s'indiquen mensualment el cost unitari de producció produït per una empresa i el benefici corresponent en un període determinat. La funció gràfica que construïm reflectirà la dependència de l’augment dels beneficis sobre la disminució del cost de producció.

1. Per construir el gràfic, primer seleccioneu les columnes "Cost unitari de producció" i "Benefici". Després passem a la pestanya "Insereix". A continuació, a la cinta del bloc de la caixa d'eines "Diagrames", feu clic al botó "Spot". A la llista que s'obre, seleccioneu el nom "Pinta amb corbes suaus i marcadors". És aquest tipus de gràfics més adequats per treballar amb la línia de tendència i, per tant, per aplicar el mètode d'aproximació a Excel.



2. Programació construïda.



3. Per afegir una línia de tendència, seleccioneu-la fent clic amb el botó dret del ratolí. Apareixerà un menú contextual. Trieu un element en ell "Afegeix una línia de tendència ...".

   

   Hi ha una altra opció per afegir-la. En un grup addicional de pestanyes a la cinta "Treballar amb gràfics" aneu a la pestanya "Disposició". A continuació, a la caixa d'eines "Anàlisi" feu clic al botó "Línia de tendència". S'obrirà una llista. Com hem d'aplicar una aproximació lineal, des de les posicions presentades que escollim "Aproximació lineal".



4. Tanmateix, si heu seleccionat la primera opció d’accions amb l’addició a través del menú contextual, s’obrirà la finestra del format.

   Al bloc de paràmetres "Construint una línia de tendència (aproximació i suavitzat)" establiu el commutador a la posició "Lineal".
   Si ho desitgeu, podeu establir una marca a prop de la posició "Mostra l'equació en el gràfic". Després d'això, el diagrama mostrarà l’equació de la funció de suavitzat.

   També, en el nostre cas, per comparar les diferents opcions d'aproximació, és important marcar la casella "Posa a la taula el valor d’una aproximació fiable (R ^ 2)". Aquest indicador pot variar de 0 fins a 1. Com més gran és, la millor aproximació (més fiable). Es creu que quan el valor d’aquest indicador 0,85 i un suavitzat superior es pot considerar fiable i, si la xifra és menor, llavors - no.

   Després de tenir tots els paràmetres anteriors. Premeu el botó "Tanca"situat a la part inferior de la finestra.



5. Com podeu veure, la línia de tendència es dibuixa a la taula. En el cas d’una aproximació lineal, es denota per una línia recta negra. Aquest tipus de suavitzat es pot aplicar en els casos més senzills, quan les dades canvien amb força rapidesa i la dependència del valor de la funció a l’argument és obvi.

El suavitzat, que s’utilitza en aquest cas, es descriu amb la següent fórmula:

y = ax + b

En el nostre cas particular, la fórmula té la forma següent:

y = -0.1156x + 72.255

La magnitud de la precisió de l’aproximació és igual a nosaltres 0,9418, que és un resultat bastant acceptable, descrivint el suavitzat com a fiable.

Mètode 2: aproximació exponencial

Ara considerem el tipus exponencial d’aproximació a Excel.

1. Per canviar el tipus de línia de tendència, seleccioneu-lo fent clic amb el botó dret del ratolí i, al menú desplegable, seleccioneu l'element "Format de línia de tendència ...".



2. Després d'això, es llança la finestra de format que ja ens és familiar. Al bloc per seleccionar el tipus d'aproximació, establiu el commutador a "Exponencial". La resta de paràmetres es mantenen igual que en el primer cas. Feu clic al botó "Tanca".



3. Després d'això, es traçarà la línia de tendència. Com podeu veure, quan utilitzeu aquest mètode, té una forma lleugerament corbada. El nivell de confiança és 0,9592, que és superior a l’ús d’una aproximació lineal. El mètode exponencial s'utilitza millor quan els valors primer canvien ràpidament i després prenen una forma equilibrada.

La vista general de la funció de suavitzat és la següent:

y = ser ^ x

on e - Aquesta és la base del logaritme natural.

En el nostre cas particular, la fórmula va adoptar la següent forma:

y = 6282,7 * e ^ (- 0,012 * x)

Mètode 3: suavitzat de registre

Ara és el torn de considerar el mètode d’aproximació logarítmica.

1. De la mateixa manera que en el temps anterior, a través del menú contextual, engegueu la finestra del format de línia de tendència. Poseu el commutador en posició "Logarítmica" i feu clic al botó "Tanca".



2. Hi ha un procediment de construcció de línies de tendència amb una aproximació logarítmica. Igual que en el cas anterior, aquesta opció és millor utilitzar-la quan les dades canvien inicialment ràpidament i després es veuen equilibrades. Com podeu veure, el nivell de confiança és de 0,946. Això és superior a l’ús del mètode lineal, però inferior a la qualitat de la línia de tendència amb suavitzat exponencial.

En general, la fórmula de suavitzat té aquest aspecte:

y = a * ln (x) + b

on ln és la magnitud del logaritme natural. D'aquí el nom del mètode.

En el nostre cas, la fórmula té la forma següent:

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

Mètode 4: suavitzat polinòmic

És hora de considerar el mètode de suavitzat polinòmic.

1. Aneu a la finestra del format de línia de tendència, com ja ho heu fet més d'una vegada. En bloc "Construint una línia de tendència" establiu el commutador a la posició "Polinomi". A la dreta d’aquest ítem hi ha un camp "Grau". En seleccionar "Polinomi" es torna actiu. Aquí podeu especificar qualsevol valor de poder de 2 (per defecte) a 6. Aquest indicador determina el nombre de màxims i mínims de la funció. En instal·lar un polinomi de segon grau, només es descriu un màxim i, quan s'instal·la un polinomi de sisè grau, es poden descriure fins a cinc màxims. Per començar, deixem la configuració per defecte, és a dir, especifiquem el segon grau. Els paràmetres restants es mantenen igual que els establerts en els mètodes anteriors. Premeu el botó "Tanca".



2. Es construeix una línia de tendència que utilitza aquest mètode. Com podeu veure, és encara més corbat que quan s’utilitza una aproximació exponencial. El nivell de confiança és més alt que amb qualsevol dels mètodes utilitzats anteriorment, i ho és 0,9724.

   

   Aquest mètode es pot aplicar amb més èxit si les dades canvien constantment. La funció que descriu aquest tipus de suavitzat és així:

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

   En el nostre cas, la fórmula va adoptar la següent forma:

   y = 0.0015 * x ^ 2-1.7202 * x + 507.01

3. Ara anem a canviar el grau dels polinomis per veure si el resultat serà diferent. Tornem a la finestra de format. El tipus d'aproximació és el polinomi esquerre, però al davant, a la finestra de graus, establim el valor màxim possible - 6.



4. Com podeu veure, després d’aquest moment, la nostra línia de tendència va prendre la forma d’una corba pronunciada, en la qual el nombre màxim és de sis. El nivell de confiança va augmentar encara més i va augmentar 0,9844.

La fórmula que descriu aquest tipus de suavitzat, va prendre la següent forma:

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

Mètode 5: suavitzat d'energia

En conclusió, considereu el mètode d’aproximació d’energia a Excel.

1. Aneu a la finestra "Format de línia de tendència". Situeu el commutador de vista de suavitzat a la posició "Poder". Mostrant l’equació i el nivell de confiança, com sempre, deixeu-lo activat. Premeu el botó "Tanca".



2. El programa forma una línia de tendència. Com podeu veure, en el nostre cas, és una línia amb un lleuger revolt. El nivell de confiança és 0,9618que és una xifra força elevada. De tots els mètodes descrits anteriorment, el nivell de confiança només era superior quan es va utilitzar el mètode polinomi.

Aquest mètode s’utilitza efectivament en casos de canvis intensius en les dades de la funció. És important assenyalar que aquesta opció només és aplicable si la funció i l’argument no accepten valors negatius o zero.

La fórmula general que descriu aquest mètode és la següent:

y = bx ^ n

En el nostre cas particular, sembla així:

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

Com podeu veure, quan utilitzeu les dades específiques que hem utilitzat per a l’exemple, el mètode d’aproximació polinòmica amb el polinomi en el sisè grau (0,9844), el nivell de confiança més baix en el mètode lineal (0,9418). Però això no vol dir que la mateixa tendència sigui quan es facin servir altres exemples. No, el nivell d’eficiència dels mètodes anteriors pot variar significativament, depenent del tipus de funció específic per al qual es construirà la línia de tendència. Per tant, si el mètode escollit és més eficaç per a aquesta funció, això no vol dir que sigui també òptim en una altra situació.

Si no podeu determinar immediatament, segons les recomanacions anteriors, quin tipus d’aproximació s’adaptarà específicament en el vostre cas, llavors té sentit provar tots els mètodes. Després de construir una línia de tendència i veure el seu nivell de confiança, podeu triar la millor opció.