En este video, demostraremos la aplicación de la función de interpolación en NumXL en la búsqueda de de valores para una observación intermedia faltante en series de tiempo igualmente distribuidas.
Para los propósitos de nuestra demostración, hemos usado cuatro métodos de interpolación distintos: For our demo purposes, we used four different interpolation methods: constante hacia adelante, constante hacia atrás, lineal y tira cúbica.
Scene 1:
Welcome to part one of the time series data preparation mini tutorial. In this video we'll use NumXL to interpolate a missing value for an intermediate observation in Excel.
To start I've already plotted the observations, note that Excel graphs ignore missing values. Let's start with interpolation. We will begin with a forward flat interpolation method.
Using NumXL, type in interpolate in the formula bar and click on the equation editor button.
Scene 2:
The function wizard will pop up. Note how each argument is represented on the separate row and the description is displayed. Now select the data range for the X variable.
Next let's select the Y range or the dependent value. Select the value cell range, don't worry about missing values in the range as the interpolate function will truncate that for you.
Now let's pick the target, or explanatory value for interpolation. Choose the data value in column a, lock the cell for row movement.
Choose the interpolation method, one for forward flat interpolation. The interpolate function supports four different methods, each method is designated by an inclusive integer value between 1 and 4.
Scene 3:
The function returns div by 0 as the date value is outside the interpolation range, an extrapolation is disabled by default.
Copy the formula to the rest of the column, notice how only observations with missing values are affected.
Scene 4:
Let's update the graph to plot the forward flat values. Move the column to C. The red mark represents the interpolated point, note how it is on the same level as the prior observation.
Now let's do the backward interpolation.
First, let's copy the formula from column C and then edit the formula and change the interpolation method. Select the copied cell and click on the formula toolbar button to launch the function wizard.
Scene 5:
Change the argument value to 2 for backward flat interpolation then click OK.
Scene 6:
Copy the value to the rest of the cells in the column.
Once again update the cell range of the graph to reference the backward flat column. Note that the interpolated point in red is now at the same level as the next non interpolated value.
Now let's do the linear interpolation. Copy the formula from the first cell in column D to column E.
Scene 7:
Edit the formula and change the interpolation algorithm to three or linear interpolation then click OK.
Scene 8:
Now copy the formula to the remaining cells in the column, let's see how they look. Update the input column from D to E. The interpolated point in red is positioned halfway between the surrounding points.
Lastly, let's do the cubic spline interpolation. Copy the formula from column E to column F and edit it in column F.
Scene 9:
Change the interpolation method to four or cubic spline then click OK.
Scene 10:
Copy the formula in the first cell to the rest of the cells in the same column. Let's update the graph and change the input cell range.
Move the input from column E to column F. The interpolated point in red is between the two points but slightly different from the linear interpolation.
Scene 11:
That is it for now, thank you for watching!
Comentarios
El artículo está cerrado para comentarios.