photosdematures.com

Interpolació lineal d'Excel

La interpolació lineal a Excel significa predir o endevinar el proper valor següent de qualsevol variable determinada donada a les dades actuals, aquí creem una línia recta que connecta dos valors i estimem el valor futur a través d’ella, a Excel fem servir la funció de previsió i una cerca funció per fer una interpolació lineal.

La interpolació és una eina matemàtica o estadística que s’utilitza per predir els valors entre 2 punts d’una corba o línia. Aquesta eina no només s'utilitza en estadístiques, sinó que també s'utilitza en molts altres camps, com ara empreses, ciències, etc., sempre que hi hagi l'oportunitat de predir valors entre dos punts de dades.

Com es fa la interpolació lineal a Excel?

Podeu descarregar aquesta plantilla Excel d’interpolació lineal aquí: plantilla Excel d’interpolació lineal

Exemple 1

Realitzant interpolació per conèixer la temperatura del temps durant diferents zones horàries

En primer lloc, elimineu les xifres de temperatura de la regió de Bangalore per a cada hora i les dades serien les següents: -

Les dades mostren que hem obtingut els detalls de la temperatura de la regió de Bangalore en alguna data. A la columna puntual que tenim els fusos horaris de tota la columna del dia i de l’hora, hem esmentat el número d’hores des de l’inici del dia, com ara les 00: 00h seria 0 hores, la 01: 00h seria 1 hora, i així encès.

Ara farem una interpolació de les dades per treure el valor de temperatura de la zona horària requerida, que pot ser en qualsevol moment, no només l’hora exacta.

Per realitzar la interpolació, hem d’utilitzar algunes fórmules a Excel, com ara PREVISIÓ, OFFSET, MATCH. Vegem breument aquestes fórmules abans de seguir endavant.

PREVISIÓ () - Aquesta funció Forecast Excel calcula o prediu el valor futur en funció dels valors existents juntament amb una tendència lineal.

• X - Aquest és el valor pel qual volem predir.
• Coneguts - Són els valors dependents de les dades i un camp obligatori que cal omplir
• Conegut_xs - Es tracta dels valors independents de les dades i d'un camp obligatori que cal omplir.

PARTIT () - Aquesta funció de coincidència excel retornarà la posició relativa d'un valor de cerca en una fila, columna o taula que coincideixi amb el valor especificat en un ordre especificat.

• Valor_cerca - Aquest és el valor que s'ha de fer coincidir des de la matriu de cerca
• Look__array - Aquest és el rang de cerca

[match_type]: pot ser 1,0, -1. El valor per defecte seria 1. Per a 1 - Match trobarà el valor més gran que sigui inferior o igual al valor de cerca i el valor hauria d'estar en ordre ascendent. Per a 0: Match troba el primer valor exactament igual a lookup_value i no cal ordenar-lo. Per a -1 - Match trobarà el valor més petit que sigui superior o igual al valor look_up i que s'hagi d'ordenar en ordre descendent.

OFFSET () - Aquesta funció Offset retornarà una cel·la o un interval de cel·les amb un nombre especificat de files i columnes. La cel·la o l'interval de cel·les dependrà de l'alçada i l'amplada de les files i columnes que especifiquem.

• Referència - Aquest és el punt de partida des d'on es farà el recompte de files i columnes.
• Fileres - Nombre de files per compensar per sota de la cel·la de referència inicial.
• Columnes - Nombre de columnes per compensar des de la cel·la de referència inicial.
• [alçada] - L'altura en files de la referència retornada. Això és opcional.
• [amplada] - L'amplada en columnes de la referència retornada. Això és opcional.

Com hem vist breument les fórmules que farem servir per realitzar la interpolació. Ara realitzem la interpolació de la següent manera:

Escriviu la fórmula en una cel·la que necessitem per veure la temperatura de la zona horària diferent. Això indica que hem de seleccionar la cel·la que cal preveure i que s'utilitza la funció de compensació i coincidència per seleccionar els coneguts_ys i els coneguts_xs.

PREVISIÓ ($ F $ 5 - Seleccioneu la cel·la que tingui la zona horària a pronosticar.

COMPENSACIÓ ($ C $ 3: $ C $ 26, MATCH ($ F $ 5, $ B $ 3: $ B $ 26,1) -1,0,2) - S'utilitza per seleccionar els coneguts_ys, ja que es fa referència a la columna temporal perquè són els valors dependents. La funció de concordança s’utilitza per generar la posició del valor que hem de preveure i calcular el nombre de files. Les columnes haurien de ser 0 perquè volem que el valor dependent de la mateixa columna que ha seleccionat i l'alçada sigui 2, ja que hem de realitzar la previsió basada en els 2 darrers valors.

COMPENSACIÓ ($ B $ 3: $ B $ 26, MATCH ($ F $ 5, $ B $ 3: $ B $ 26,1) -1,0,2) - S'utilitza per seleccionar els coneguts_xs, ja que es fa referència a la columna d'hores perquè són valors independents i el descans és el mateix que havíem fet per al recompte de files.

Ara doneu una zona horària a la cel·la que havíem considerat predir. Aquí el valor introduït és 19,5, que és a les 19:30 i obtindrem la temperatura de 30 que es preveu a partir dels valors de temperatura que es donen per hora.

De la mateixa manera, podem veure xifres temporals per a diferents zones horàries a partir d’aquesta fórmula.

Exemple 2

Realització d’interpolació lineal per conèixer les vendes d’una organització el 2018

Suposem que vam obtenir els detalls de vendes d’una organització el 2018, tal com es mostra a continuació. Disposem de dades en termes de dies i de vendes acumulades. Vam obtenir vendes de 7844 unitats durant els primers 15 dies de l'any, de 16094 unitats en 50 dies de l'any, etc.

Podem utilitzar la mateixa fórmula que hem utilitzat en la interpolació per pronosticar el valor de vendes per a dies diferents que no es va esmentar a les dades que considerem. Aquí les vendes es realitzen en línia recta (lineal) tal com havíem fet acumulativament.

Si volem veure el nombre de vendes que havíem aconseguit en 215 dies, podem obtenir el nombre de vendes previst per a 215 dies, tal com es mostra a continuació, tenint en compte les dades de vendes donades.

De la mateixa manera, podem esbrinar el nombre de vendes d’aquest any mitjançant la predicció entre els punts que es donen.

Coses que cal recordar

• És el mètode menys precís, però és ràpid i precís si els valors de la taula estan molt distanciats.
• Això també es pot utilitzar per estimar valors per a un punt de dades geogràfiques, precipitacions, nivells de soroll, etc.
• És molt fàcil d'utilitzar i no és molt precís per a funcions no lineals.
• A part de la interpolació lineal d'Excel, també tenim diferents tipus de mètodes com la interpolació polinòmica, la interpolació spline, etc.