El mètode de mitjana mòbil és una eina estadística amb la qual podeu resoldre diversos tipus de problemes. En particular, s’utilitza sovint en la predicció. A Excel, aquesta eina també es pot utilitzar per resoldre diverses tasques. Vegem com s'utilitza la mitjana mòbil a Excel.
Aplicació de la mitjana mòbil
El significat d’aquest mètode és que amb la seva ajuda hi ha un canvi dels valors dinàmics absoluts de la sèrie seleccionada a les mitjanes aritmètiques per a un període determinat per suavitzar les dades. Aquesta eina s’utilitza per a càlculs econòmics, prediccions, en procés de negociació a la borsa de valors, etc. El millor és utilitzar el mètode de mitjana mòbil a Excel amb l’ajuda de l’eina més potent per al processament de dades estadístiques, que s’anomena Paquet d’anàlisi. A més, per a aquests propòsits, podeu utilitzar la funció d’Excel integrada. MEDIA.
Mètode 1: Paquet d’anàlisi
Paquet d’anàlisi és un complement d'Excel que està desactivat per defecte. Per tant, en primer lloc, és necessari per habilitar-lo.
- Aneu a la pestanya "Fitxer". Feu clic a l'element. "Opcions".
- A la finestra de paràmetres que comença, aneu a la secció Complements. A la part inferior de la finestra al camp "Gestió" cal establir el paràmetre Complements d'Excel. Feu clic al botó "Vés".
- Ens endinsem en la finestra de complements. Establiu una marca de prop a l’ítem "Paquet d’anàlisi" i feu clic al botó "D'acord".
Després d’aquest paquet d’acció "Anàlisi de dades" activat, i el botó corresponent apareix a la cinta de la pestanya "Dades".
Vegem ara com podeu utilitzar directament les capacitats del paquet. Anàlisi de dades per treballar el mètode de mitjana mòbil. Anem a, basant-nos en la informació sobre els ingressos de l’empresa durant els 11 períodes anteriors, fer una previsió per al dotzè mes. Per fer-ho, utilitzem la taula plena de dades i eines. Paquet d’anàlisi.
- Aneu a la pestanya "Dades" i feu clic al botó "Anàlisi de dades"que es col·loca a la cinta d'eines del bloc "Anàlisi".
- Una llista d’eines disponibles a Paquet d’anàlisi. Escollim el nom "Mitjana mòbil" i feu clic al botó "D'acord".
- Es llança la finestra d’entrada de dades per a la predicció mitjana de moviment.
Al camp "Interval d’entrada" especifiqueu l’adreça de l’interval, on la quantitat mensual d’ingressos es troba sense una cel·la, les dades en què s’hauran de calcular.
Al camp "Interval" especifiqueu l'interval de valors de processament mitjançant el mètode de suavitzat. Per començar, posem el valor de suavitzat en tres mesos i, per tant, introduïm la figura "3".
Al camp "Espaiat de sortida" heu d’especificar un interval buit arbitrari al full, on es mostraran les dades després del processament, que hauria de ser una cel·la més gran que l’interval d’entrada.
Marca també la casella situada al costat "Errors estàndard".
Si cal, també podeu marcar la casella al costat de "Traçat" per a demostració visual, encara que en el nostre cas no sigui necessari.
Un cop fetes totes les opcions, feu clic al botó. "D'acord".
- El programa mostra el resultat del processament.
- Ara executarem el suavitzat durant el període de dos mesos per tal de revelar quin resultat és més correcte. Per a això, tornem a executar l’eina. "Mitjana mòbil" Paquet d’anàlisi.
Al camp "Interval d’entrada" Deixeu els mateixos valors que en el cas anterior.
Al camp "Interval" posa el número "2".
Al camp "Espaiat de sortida" especificem l’adreça de l’abast buit nou, que, de nou, ha de ser una cel·la més gran que l’interval d’entrada.
La configuració restant no es modifica. Després, feu clic al botó "D'acord".
- A continuació, el programa calcula i mostra el resultat a la pantalla. Per tal de determinar quins dels dos models són més precisos, hem de comparar errors habituals. Com més petit sigui aquest indicador, major serà la probabilitat d’exactitud del resultat. Com podeu veure, per a tots els valors de l’error estàndard en el càlcul del lliscament de dos mesos és inferior a la mateixa xifra durant 3 mesos. Per tant, el valor previst de desembre es pot considerar el valor calculat pel mètode de lliscament per al darrer període. En el nostre cas, aquest valor és de 990,4 mil rubles.
Mètode 2: utilitzeu la funció MEDIA
A Excel hi ha una altra manera d’utilitzar el mètode de mitjana mòbil. Per utilitzar-lo, heu d’aplicar diverses funcions del programa estàndard, que són bàsiques per al nostre propòsit MEDIA. Per exemple, utilitzarem la mateixa taula d’ingressos empresarials que en el primer cas.
Com la darrera vegada, haurem de crear una sèrie temporal suavitzada. Però aquesta vegada les accions no seran tan automatitzades. Calculeu el valor mitjà de cada dos i després tres mesos per poder comparar els resultats.
En primer lloc, calculem els valors mitjans dels dos períodes anteriors utilitzant la funció MEDIA. Ho podem fer només a partir del mes de març, ja que per a dates posteriors hi ha un salt de valors.
- Seleccioneu la cel·la de la columna buida de la fila de març. A continuació, feu clic a la icona "Insereix la funció"que es troba a prop de la barra de fórmules.
- Finestra activada Màsters de funcions. A la categoria "Estadística" buscant valor "SRZNACH"seleccioneu-lo i feu clic al botó "D'acord".
- S'inicia la finestra d’argument d’operador. MEDIA. La seva sintaxi és la següent:
= MEDI (nombre1; número2; ...)Només es requereix un argument.
En el nostre cas, al camp "Nombre1" hem d’enviar un enllaç a l’interval on s’indiquen els ingressos dels dos períodes anteriors (gener i febrer). Situeu el cursor al camp i seleccioneu les cel·les corresponents al full a la columna "Ingressos". Després, feu clic al botó "D'acord".
- Com podeu veure, el resultat del càlcul de la mitjana dels dos períodes anteriors s’ha mostrat a la cel·la. Per tal de realitzar càlculs similars per a tots els mesos restants del període, hem de copiar aquesta fórmula a altres cel·les. Per fer-ho, ens convertim en el cursor a la cantonada inferior dreta de la cel·la que conté la funció. El cursor es converteix en un marcador de farciment, que sembla una creu. Premeu el botó esquerre del ratolí i arrossegueu-lo fins al final de la columna.
- Aconseguim el càlcul dels resultats mitjans dels dos mesos anteriors al final de l’any.
- Ara seleccioneu la cel·la a la següent columna buida de la fila d’abril. Truqueu a la finestra d'argument de la funció MEDIA de la mateixa manera que anteriorment descrita. Al camp "Nombre1" introduïu les coordenades de les cel·les a la columna "Ingressos" de gener a març. A continuació, feu clic al botó "D'acord".
- Utilitzant el marcador d’ompliment, copieu la fórmula a les cel·les de la taula de sota.
- Per tant, hem calculat els valors. Ara, com en el temps anterior, haurem de determinar quin tipus d’anàlisi és millor: amb antialiasing en 2 o 3 mesos. Per fer-ho, calculeu la desviació estàndard i alguns altres indicadors. Primer, calculem la desviació absoluta amb la funció d’Excel estàndard. ABS, que en lloc de números positius o negatius, retorna el seu mòdul. Aquest valor serà igual a la diferència entre els ingressos reals del mes seleccionat i la previsió. Situeu el cursor a la següent columna buida en una fila per al mes de maig. Truca Auxiliar de funcions.
- A la categoria "Matemàtica" seleccioneu el nom de la funció "Abs". Premeu el botó "D'acord".
- S'inicia la finestra d'argument de la funció. ABS. En un sol camp "Nombre" especifiqueu la diferència entre el contingut de les cel·les a les columnes "Ingressos" i "2 mesos" de maig. A continuació, feu clic al botó "D'acord".
- Utilitzant el marcador d’ompliment, copiem aquesta fórmula a totes les files de la taula fins al mes de novembre inclusivament.
- Calculeu el valor mitjà de la desviació absoluta durant tot el període utilitzant la funció que ja ens és familiar MEDIA.
- Realitzem el mateix procediment per calcular la desviació absoluta d’un lliscant durant 3 mesos. Primer utilitzem la funció ABS. Només en aquesta ocasió, considerem la diferència entre el contingut de les cèl·lules amb l’ingrés real i el previst calculat amb el mètode de mitjana mòbil durant 3 mesos.
- A continuació, calculem la mitjana de totes les dades de desviació absoluta mitjançant la funció MEDIA.
- El següent pas és calcular la desviació relativa. És igual a la relació entre la desviació absoluta i l'indicador real. Per evitar valors negatius, tornem a utilitzar les possibilitats que ofereix l’operador ABS. Aquesta vegada utilitzant aquesta funció, dividim el valor de desviació absoluta quan s'utilitza el mètode de mitjana mòbil durant 2 mesos per l’ingrés real del mes seleccionat.
- Però la desviació relativa normalment es mostra com un percentatge. Per tant, seleccioneu l'interval adequat al full, aneu a la pestanya "Inici"on en eines de bloc "Nombre" Al camp de formatació especial, definiu el format de percentatge. Després d'això, el resultat del càlcul de la desviació relativa es mostra en percentatge.
- Realitzem una operació similar per calcular la desviació relativa amb les dades mitjançant el suavitzat durant 3 mesos. Només en aquest cas, per calcular com a dividend, fem servir una altra columna de la taula, que tenim el nom "Abs. Apagat (3 m)". A continuació, traduïm els valors numèrics al percentatge.
- Després d'això, calculem els valors mitjans de les dues columnes amb desviació relativa, com abans d'utilitzar per a aquest propòsit la funció MEDIA. Com que prenem valors percentuals per a la funció com a arguments de la funció, no necessitem conversió addicional. L’operador de la sortida dóna el resultat ja en format de percentatge.
- Ara arribem al càlcul de la desviació estàndard. Aquest indicador ens permetrà comparar directament la qualitat del càlcul en utilitzar anti-aliasing durant dos i tres mesos. En el nostre cas, la desviació estàndard serà igual a l'arrel quadrada de la suma dels quadrats de les diferències en ingressos reals i la mitjana mòbil dividida pel nombre de mesos. Per tal de fer el càlcul en el programa, hem d’utilitzar diverses funcions, en particular ROOT, SUMMKRAVN i COMPTE. Per exemple, per calcular la desviació estàndard quan s'utilitza la línia de suavitzat durant dos mesos al maig, en el nostre cas s'aplicarà la fórmula següent:
= ROOT (SUMKVRAZN (B6: B12; C6: C12) / COMPTE (B6: B12))El copiem a altres cel·les de la columna amb el càlcul de la desviació estàndard mitjançant un marcador de farciment.
- Realitzem una operació similar per calcular la desviació estàndard de la mitjana mòbil durant 3 mesos.
- Després d'això, calculem el valor mitjà de tot el període per a aquests dos indicadors, aplicant la funció MEDIA.
- Després d’haver realitzat una comparació dels càlculs utilitzant el mètode de mitjana mòbil amb suavitzat als 2 i 3 mesos utilitzant indicadors com la desviació absoluta, la desviació relativa i la desviació estàndard, podem afirmar amb seguretat que dos mesos de suavitzat donen resultats més fiables que l’ús de tres mesos de suavitzat. Això es desprèn del fet que els indicadors anteriors per a la mitjana mòbil de dos mesos són inferiors als tres mesos.
- Així, els ingressos previstos de la companyia al desembre seran de 990,4 milers de rubles. Com podeu veure, aquest valor és el mateix que el que hem rebut, fent el càlcul utilitzant les eines Paquet d’anàlisi.
Lliçó: Auxiliar de funcions d'Excel
Hem calculat la previsió utilitzant el mètode de mitjana mòbil de dues maneres. Com podeu veure, aquest procediment és molt més fàcil de fer amb eines. Paquet d’anàlisi. No obstant això, alguns usuaris no sempre confien en el càlcul automàtic i prefereixen utilitzar la funció per als càlculs. MEDIA i operadors relacionats per verificar l’opció més fiable. Tot i que, si tot es fa correctament, a la sortida el resultat dels càlculs hauria de ser completament igual.
