La previsió és un element molt important en gairebé qualsevol camp d’activitat, des de l’economia a l’enginyeria. Hi ha un gran nombre de programes especialitzats en aquesta àrea. Malauradament, no tots els usuaris saben que el processador de fulls de càlcul habitual d’Excel compta amb les seves eines d’arsenal per realitzar prediccions, que en la seva eficàcia no són molt inferiors als programes professionals. Descobrim quines són aquestes eines i com fer una previsió a la pràctica.
Procediment de previsió
L'objectiu de qualsevol predicció és identificar la tendència actual i determinar el resultat esperat en relació amb l'objecte objecte d'estudi en un moment determinat en el futur.
Mètode 1: línia de tendència
Un dels tipus més populars de predicció gràfica a Excel és una extrapolació realitzada mitjançant la creació d’una línia de tendència.
Intentarem predir l'import dels beneficis de l'empresa en 3 anys basant-nos en les dades d'aquest indicador dels darrers 12 anys.
- Construïu un gràfic de dependències basat en dades tabulars que constin dels arguments i valors de la funció. Per fer-ho, seleccioneu l'espai de taula i, a continuació, estar a la pestanya "Insereix", feu clic a la icona del tipus de diagrama desitjat, que es troba al bloc "Gràfics". A continuació, seleccionarem el tipus adequat per a la situació específica. El millor és triar un gràfic d’esparso. Podeu triar una vista diferent, però després, perquè les dades es mostrin correctament, haureu d’editar, en particular, eliminar la línia d’argument i seleccionar una escala diferent de l’eix horitzontal.
- Ara hem de construir una línia de tendència. Feu clic amb el botó dret sobre qualsevol dels punts del diagrama. Al menú contextual activat, atureu la selecció de l’element "Afegeix una línia de tendència".
- S'obrirà la finestra de format de la línia de tendència. És possible triar un dels sis tipus d'aproximació:
- Lineal;
- Logarítmica;
- Exponencial;
- Potència;
- Polinomi;
- Filtratge lineal.
Comencem per una aproximació lineal.
Al quadre de configuració "Previsió" al camp "Endavant" establiu el número "3,0", ja que hem de fer una previsió durant tres anys. A més, podeu marcar les caselles "Mostra l'equació en el gràfic" i "Posa en el gràfic el valor de la precisió de l’aproximació (R ^ 2)". L’últim indicador mostra la qualitat de la línia de tendència. Un cop feta la configuració, feu clic al botó. "Tanca".
- La línia de tendència es construeix i la podem utilitzar per determinar la quantitat aproximada de beneficis després de tres anys. Com podeu veure, hauria de passar per 4.500 mil rubles. Coeficient R2, com es va esmentar anteriorment, mostra la qualitat de la línia de tendència. En el nostre cas, el valor R2 està a punt 0,89. Com més gran sigui el coeficient, més alta és la fiabilitat de la línia. El seu valor màxim pot ser igual 1. Es considera que quan la proporció ha acabat 0,85 la línia de tendència és fiable.
- Si no esteu satisfets amb el nivell de confiança, podeu tornar a la finestra del format de línia de tendència i seleccionar qualsevol altre tipus d'aproximació. Podeu provar totes les opcions disponibles per trobar el més precís.
Cal assenyalar que la previsió efectiva que fa servir l’extrapolació a través de la línia de tendència pot ser que el període de previsió no excedeixi del 30% de la base del període analitzat. És a dir, en l'anàlisi del període de 12 anys, no podem fer una previsió efectiva de més de 3-4 anys. Però fins i tot en aquest cas, serà relativament fiable, si durant aquest temps no hi haurà força major o, per contra, circumstàncies extremadament favorables, que no van ser en períodes anteriors.
Lliçó: Com crear una línia de tendència a Excel
Mètode 2: PREVISIÓ DE L'operador
L’extrapolació de dades tabulars es pot fer a través de la funció d’Excel estàndard. PREVISIÓ. Aquest argument pertany a la categoria d’eines estadístiques i té la següent sintaxi:
= PREDICT (x; conegut_i; valors coneguts_x)
"X" és un argument, el valor de la funció per a la qual voleu determinar. En el nostre cas, l’argument serà l’any per al qual s’hauria de fer la previsió.
"Valors Y coneguts" - base dels valors coneguts de la funció. En el nostre cas, el seu paper és l’import del benefici dels períodes anteriors.
"Conegut x" - Són els arguments que corresponen als valors coneguts de la funció. En el seu paper tenim la numeració dels anys per als quals es va recopilar informació sobre els beneficis d'anys anteriors.
Naturalment, l’argument no hauria de ser necessàriament un període de temps. Per exemple, pot ser temperatura, i el valor de la funció pot ser el nivell d’expansió de l’aigua quan s'escalfa.
En calcular aquest mètode s'utilitza el mètode de regressió lineal.
Vegem els matisos de l’operador PREVISIÓ en un exemple concret. Agafeu tota la mateixa taula. Haurem de conèixer la previsió de beneficis per al 2018.
- Seleccioneu la cel·la buida del full on voleu mostrar el resultat del processament. Premeu el botó "Insereix la funció".
- Obre Auxiliar de funcions. A la categoria "Estadística" seleccioneu el nom "PREVISIÓ"i després feu clic al botó "D'acord".
- S'inicia la finestra d'argument. Al camp "X" especifiqueu el valor de l’argument al qual voleu trobar el valor de la funció. En el nostre cas, aquest és el 2018. Per tant, fem un registre "2018". Però és millor indicar aquest indicador a la cel·la del full i al camp "X" només heu de donar-li un enllaç. Això permetrà automatitzar els càlculs en el futur i canviar l’any fàcilment si és necessari.
Al camp "Valors Y coneguts" especifiqueu les coordenades de la columna "Benefici de l’empresa". Això es pot fer posant el cursor al camp i, a continuació, mantenint el botó esquerre del ratolí i seleccionant la columna corresponent del full.
De la mateixa manera al camp "Conegut x" introduïm l’adreça de la columna "Any" amb dades del període anterior.
Un cop introduïda tota la informació, feu clic al botó. "D'acord".
- L'operador calcula sobre la base de les dades introduïdes i mostra el resultat a la pantalla. Per al 2018, es preveu obtenir beneficis en la zona de 4564,7 mil rubles. Basant-nos en la taula resultant, podem construir un gràfic utilitzant les eines de creació de diagrames, que es van discutir anteriorment.
- Si canvieu l’any de la cel·la que s’utilitzava per introduir l’argument, el resultat canviarà en conseqüència i el gràfic s'actualitzarà automàticament. Per exemple, segons les previsions del 2019, l’import dels beneficis serà de 4637,8 mil rubles.
Però no oblideu que, com en la construcció de la línia de tendència, el període de temps abans del període de previsió no hauria de superar el 30% de tot el període per al qual s'ha acumulat la base de dades.
Lliçó: Extrapolació d’Excel
Mètode 3: operador TENDÈNCIA
Per a la predicció, podeu utilitzar una altra funció - TENDÈNCIES. També pertany a la categoria d'operadors estadístics. La seva sintaxi és molt similar a la sintaxi de l’eina. PREVISIÓ i sembla així:
= TREND (valors coneguts_y; valors coneguts_x; vals_noms_x; [const])
Com podeu veure, els arguments "Valors Y coneguts" i "Conegut x" corresponen completament als mateixos elements de l’operador PREVISIÓi l’argument "Valors x nous" coincideix amb l’argument "X" eina anterior. A més, TENDÈNCIES hi ha un argument addicional "Constant"però no és obligatori i només s'utilitza si hi ha factors constants.
Aquest operador s’utilitza amb més eficàcia en presència d’una dependència lineal de la funció.
Vegem com funcionarà aquesta eina amb la mateixa matriu de dades. Per tal de comparar els resultats obtinguts, es defineix el punt de predicció el 2019.
- Fem una designació de cel·la per mostrar el resultat i executar-se Auxiliar de funcions de la manera habitual. A la categoria "Estadística" busqueu i seleccioneu el nom "TREND". Premeu el botó "D'acord".
- S'obrirà la finestra d’argument d’operador TENDÈNCIES. Al camp "Valors Y coneguts" ja descrit anteriorment, introduïu les coordenades de la columna "Benefici de l’empresa". Al camp "Conegut x" introduïu l’adreça de la columna "Any". Al camp "Valors x nous" introduïu la referència a la cel·la on es troba el nombre de l’any al qual s’hauria d’indicar la previsió. En el nostre cas, aquest és el 2019. Camp "Constant" deixa en blanc. Feu clic al botó "D'acord".
- L’operador processa les dades i mostra el resultat a la pantalla. Com podeu veure, l'import del resultat previst per al 2019, calculat pel mètode de la dependència lineal, serà, com en el mètode de càlcul anterior, de 4637,8 mil rubles.
Mètode 4: operador GROWTH
Una altra funció que es pot utilitzar per predir en Excel és l’operador GROWTH. També pertany al grup estadístic d'eines, però, a diferència dels anteriors, no utilitza el mètode de dependència lineal, sinó el mètode exponencial per calcular-lo. La sintaxi d’aquesta eina té aquest aspecte:
= CREIXEMENT (valors coneguts; valors coneguts_x; valors_nous_x; [const])
Com podeu veure, els arguments d’aquesta funció repeteixen exactament els arguments de l’operador TENDÈNCIESde manera que no ens aturem a la seva descripció per segona vegada, però immediatament es tornarà a aplicar aquesta eina a la pràctica.
- Seleccioneu la cel·la de sortida del resultat i truqueu-la de la manera habitual. Auxiliar de funcions. A la llista d’operadors estadístics s’està buscant un element "CREIXEMENT"seleccioneu-lo i feu clic al botó "D'acord".
- Es produeix l’activació de la finestra d’argument de la funció anterior. Introduïu les dades en els camps d’aquesta finestra és completament la mateixa que les vam introduir a la finestra d’argument de l’operador TENDÈNCIES. Un cop introduïda la informació, feu clic al botó "D'acord".
- El resultat del processament de dades es mostra al monitor a la cel·la especificada anteriorment. Com podeu veure, aquesta vegada el resultat és de 4682,100 rubles. Diferències del processament de dades de l'operador TENDÈNCIES insignificants, però estan disponibles. Això es deu al fet que aquestes eines utilitzen diferents mètodes de càlcul: el mètode de dependència lineal i el mètode de dependència exponencial.
Mètode 5: operador LINEST
Operador LÍNIA en calcular s'utilitza el mètode d'aproximació lineal. No s'ha de confondre amb el mètode lineal que utilitza l’eina. TENDÈNCIES. La seva sintaxi és:
= LINEST (Valors coneguts_y; Valors coneguts_x; New_values_x; [const]; [Estadístiques])
Els dos últims arguments són opcionals. Estem familiaritzats amb els dos primers pels mètodes anteriors. Però probablement heu notat que en aquesta funció no hi ha cap argument que apunta als nous valors. El fet és que aquesta eina només determina el canvi d’ingressos d’una unitat de període, que en el nostre cas és d’un any, però hem de calcular el resultat total per separat, afegint a l’últim valor real del benefici el resultat del càlcul de l’operador. LÍNIAmultiplicat pel nombre d’anys.
- Feu una selecció de la cel·la en què es realitzarà el càlcul i engegueu el Màster de funcions. Seleccioneu el nom "LINEYN" a la categoria "Estadística" i feu clic al botó "D'acord".
- Al camp "Valors Y coneguts"de la finestra d’argument que s’obre, introduïu les coordenades de la columna "Benefici de l’empresa". Al camp "Conegut x" introduïu l’adreça de la columna "Any". Els camps restants queden en blanc. A continuació, feu clic al botó "D'acord".
- El programa calcula i mostra a la cel·la seleccionada el valor de la tendència lineal.
- Ara hem de conèixer el valor del benefici previst per al 2019. Establiu el signe "=" a qualsevol cel·la buida del full. Feu clic a la cel·la que conté l'import real del benefici de l'últim any estudiat (2016). Posem un signe "+". A continuació, feu clic a la cel·la que conté la tendència lineal calculada anteriorment. Posem un signe "*". Ja que entre l’últim any del període d’estudi (2016) i l’any per al qual s’hauria de fer la previsió (2019), el període de tres anys es troba, establim el nombre a la cel·la "3". Per calcular, feu clic al botó. Introduïu.
Com podeu veure, el valor previst del benefici, calculat pel mètode d'aproximació lineal, el 2019 serà de 4614,9 mil rubles.
Mètode 6: operador LOGEST
L’última eina que considerem serà LGGRPRIBL. Aquest operador realitza càlculs basats en el mètode d'aproximació exponencial. La seva sintaxi té l’estructura següent:
= LOGPLPR (valors coneguts_y; valors coneguts_x; valors_nous_x; [const]; [estadístiques])
Com podeu veure, tots els arguments repeteixen completament els elements corresponents de la funció anterior. L’algorisme per calcular la previsió canviarà lleugerament. La funció calcula la tendència exponencial, que mostrarà quantes vegades el volum d’ingressos canviarà en un període, és a dir, en un any. Haurem de trobar la diferència de guany entre l’últim període real i el primer pla, multiplicar-lo pel nombre de períodes previstos. (3) i afegiu al resultat la suma de l’últim període real.
- A la llista d’operadors de l’Auxiliar de funcions, seleccioneu el nom LGRFPRIBL. Feu clic al botó. "D'acord".
- S'inicia la finestra d'argument. En ella introduïm les dades exactament tal com ho feia, utilitzant la funció LÍNIA. Feu clic al botó "D'acord".
- El resultat de la tendència exponencial es calcula i es mostra a la cel·la indicada.
- Posem un signe "=" en una cel·la buida. Obriu els claudàtors i seleccioneu la cel·la que conté el valor dels ingressos de l'últim període real. Posem un signe "*" i seleccioneu la cel·la que conté la tendència exponencial. Posem un signe menys i, de nou, feu clic a l’element en què es troba l’import dels ingressos de l’últim període. Tanqueu el claudàtor i conduïu els personatges. "*3+" sense pressupostos. De nou, feu clic a la mateixa cel·la que es va seleccionar per última vegada. Per al càlcul, feu clic al botó Introduïu.
La quantitat de guanys prevista el 2019, que es va calcular mitjançant el mètode d'aproximació exponencial, serà de 4.639,2 mil rubles, que de nou no difereix gaire dels resultats obtinguts en el càlcul pels mètodes anteriors.
Lliçó: Altres funcions estadístiques en Excel
Hem descobert com fer una predicció al programa d’Excel. Gràficament, això es pot fer mitjançant l’aplicació de la línia de tendència i, analíticament, mitjançant una sèrie de funcions estadístiques integrades. Com a resultat del processament de dades idèntiques per part d’aquests operadors, pot resultar diferent. Però això no és sorprenent, ja que tots utilitzen diferents mètodes de càlcul. Si la fluctuació és petita, totes aquestes opcions aplicables a un cas en particular es poden considerar relativament fiables.
