Previsión en Excel, prónostico de datos futuros.

Escrito por Iván Antona en 3 agosto, 2020. Publicado en Excel Avanzado, Excel Intermedio, Herramientas Integradas.

Previsión en Excel

La previsión en Excel nos ayuda a pronosticar con un cierto grado de certeza (intervalo de confianza) un valor en base a una serie de datos históricos.
Necesitaremos entonces un listado con datos históricos donde deben aparecer de forma obligatoria dos variables necesarias, la variable fecha y la variable que pretendemos pronosticar, podría ser por ejemplo las ventas, los elementos de stock de un almacén, el número de clientes, etc…

Ejemplo de manejo de Previsión en Excel

Supongamos que tenemos un listado con el promedio de las ventas de 2019 de forma mensualizada y queremos mediante la herramienta de Previsión en Excel pronosticar como se comportaran las ventas en enero, febrero y marzo de 2020.

Seleccionaremos todos los datos de la serie incluyendo las fechas y las ventas y seguidamente dentro de la pestaña de datos iremos a la opción “Previsión” dentro encontraremos la opción de “previsión” y haciendo clic sobre ella automáticamente aparecerá un asistente que nos pinta por defecto la gráfica con le previsión en Excel.

Haciendo clic en la opción crear obtendremos el resultado de la previsión, así como la gráfica de esta. Para este caso en enero de 2020 pronosticamos unas ventas de 1,34. Para febrero y marzo, 1,35 y 1,36 respectivamente. Además, podemos observar los límites superiores e inferiores en nuestra gráfica, pero también en la tabla.
Estos límites están íntimamente relacionados con el intervalo de confianza o el nivel de certeza. Por defecto la herramienta de previsión en Excel establece un intervalo de confianza del 95%

Opciones de la previsión en Excel.

Cuando hacemos clic en “Opciones” dentro del asistente de previsión en Excel y tras haber seleccionado la serie de datos nos encontramos con diferentes opciones:
Final de pronóstico: Fecha hasta la cual queremos pronosticar. En nuestro ejemplo se trataba de marzo de 2020.

Inicio de pronósticos: Fecha a partir de la cual comenzamos a pronosticar.
Intervalo de confianza: El asistente lo mantiene por defecto en 95%. Esto significa que en base a los cálculos y el histórico esperamos que al menos el 95% de los puntos futuros pronosticados caerán dentro de ese intervalo o predicción. La base de este razonamiento es la distribución normal.
Estacionalidad: Por defecto se calcula automáticamente y se refiere al patrón que sigue la serie temporal. Por ejemplo, en nuestro caso como tratamos una serie de 12 meses el valor de la estacionalidad sería 12. Microsoft recomienda que cuando modifiquemos esta variable nunca sea menor a 3 para que el algoritmo funcione correctamente.
Intervalo de escala de tiempo: Se refiere a la columna que contiene las fechas en las que se miden los acontecimientos que posteriormente se utilizarán para la previsión en Excel.
Intervalo de valores: Se refiere a los valores en sí. Debe coincidir obviamente con la serie temporal o dicho de otra manera debe existir una relación entre cada elemento de la columna con un elemento adyacente de la columna de “escala de tiempo”
Rellenar los puntos que faltan con: Esta función nos permite que la herramienta de previsión en Excel rellene los valores faltantes con un promedio ponderado de los datos vecinos, esto es lo que se denomina interpolación. Para que Excel rellene mediante interpolación datos de la serie temporal que faltan se debe cumplir que no falten mas del 30% de los datos. Otra función que se nos permite es rellenar los datos faltantes con el valor “0”.
Agregar duplicados con: En aquellos casos en los que para una misma fecha tengamos varios valores, Excel realizará el promedio de los valores existentes. Otros cálculos adicionales para esta funcionalidad están disponibles como por ejemplo la mediana o el conteo. Dependerá del caso así lo trataremos.
Incluir estadísticas de previsión: Al activar esta casilla Excel en la hoja resultado añade información estadística adicional. Mas info sobre estos estadísticos aquí.

Si quieres ampliar información sobre las herramientas de previsión no dejes de consultar los análisis de hipótesis de:

Tabla de datos en Excel manejar variables con tabla de doble entrada

Escrito por Iván Antona en 3 julio, 2020. Publicado en Excel Básico.

Mediante la tabla de datos en Excel podremos realizar simulaciones con dos variables para obtener resultados en una tabla de doble entrada de forma muy sencilla. Deberemos tener un cálculo en el que las dos variables que vamos a simular en nuestra tabla de datos intervengan. Estas dos variables como su nombre indican cambian su valor para cada uno de los escenarios de la simulación.

Supuesto para el manejo de tabla de datos en Excel

Vimos en artículos anteriores el manejo del administrador de escenarios en donde habíamos ejemplificado el pago de la letra de un vehículo que adquirimos en el concesionario. En aquel ejemplo habíamos definido la siguiente tabla:

Estábamos calculando la cuota mensual que tendremos que pagar en base a los valores iniciales:

Coste del vehículo = 20.000€
Tasación (monto que nos descuentan del coste inicial por la entrega de nuestro vehículo viejo) = 1.000€
Plazo en meses (duración del préstamo) = 72 meses
Tipo de interés (interés que pagamos al banco que nos presta) = 7%

En base a esto datos calculamos el valor de la cuota de forma muy sencilla dividiendo el coste del vehículo menos la tasación entre el número de cuotas. Al resultado le aplicamos el interés.
Hasta aquí nada nuevo…¿Qué ocurre si vamos a visitar varios bancos y tenemos diferentes ofertas de tipo de interés? ¿Nos podría interesar un préstamo a menos tiempo pero que tenga una tipo de interés inferior? En esta situación existen dos variables que alterarán la cuota mensual:

Plazo en meses
Tipo de interés.

Veamos entonces como montar una tabla de datos en Excel que nos ayude de un vistazo a simular diferentes escenarios con los diferentes valores que hemos recuperado al visitar varios bancos.

¿Cómo defino mi tabla de datos en Excel con la información anterior?

Tendremos que crear una tabla de doble entrada. En nuestro ejemplo hemos puesto la variable “Tipo de interés” en la columna F (Rango F8:F11) y la variable “Plazo meses” en la fila 7 (Rango G7:I7). También necesitaremos que exista un cálculo en la celda F7 precisamente es el cálculo de la cuota que habíamos definido como =((D7-D8)/D9)*(1+D10).

Lo que hacemos en esta celda F7 es decir que es igual a la celda D11 que refleja el cálculo descrito. ¡Ojo! Este detalle es clave para que nuestro simulador con tabla de datos en Excel funcione correctamente.

Nuestro simulador utilizará las variables que hemos definido en F8:F11 y G7:I7 y los colocará dentro del cálculo de forma transparente para nosotros y nos presentará el resultado en el espacio G8:I11.

Para ello debemos seleccionar todo el rango incluyendo el cálculo es decir el rango F7:I11 y haremos clic en la pestaña Datos-Previsión-Análisis de hipótesis-Tabla de datos. Se nos presenta un asistente muy sencillo que nos pregunta:

Celda de entrada (fila): ¿Qué variable definimos en nuestra tabla en la fila 7? La variable “Plazo meses”
Celda de entrada (columnas): ¿Qué variable definimos en nuestra tabla en la columna F? La variable “Tipo de interés”

Nótese que hemos seleccionado entonces las celdas:

Esto es así porque en nuestro cálculo original así están definidas.

Finalmente y tras hacer clic en “aceptar” nuestra tabla de datos se rellena automáticamente con los resultados de la sustitución de las variables dentro del cálculo. Ahora ya podemos comenzar a analizar los resultados para tomar una decisión con respecto a la entidad con la que queremos negociar nuestro préstamo.

Descarga el ejemplo

Ejemplo_Tabla_datos_en_excel

Función DESREF en Excel

Escrito por Iván Antona en 26 julio, 2018. Publicado en Excel Avanzado, Funciones Búsqueda y Referencia.

La función DESREF en Excel sirve para devolvernos una referencia o un rango a partir de una celda o un rango cualquiera de Excel. Además podemos indicar el número de filas o columnas del rango devuelto.

Argumentos de la función DESREF en Excel.

La función DESREF en Excel admite cinco argumentos de los cuales tres de ellos son obligatorios y los dos últimos opcionales.

Referencia: Es la posición a partir de la que nos desplazamos para recuperar el rango deseado.
Filas: A partir de “la referencia” establecida como argumento anterior nos desplazaremos tantas filas como aquí definamos.
Columnas: A partir de “la referencia” establecida como argumento anterior nos desplazaremos tantas columnas como aquí definamos.
Alto: Establece el número de filas que tendrá el rango a recuperar.
Ancho: Establece el número de columnas que tendrá el rango a recuperar.

Ejemplo de uso de la función DESREF en Excel.

La función DESREF en Excel es muy interesante para devolver rangos a partir de una determinada posición. Así por ejemplo podremos devolver un valor concreto de una rango indicando un desplazamiento de filas y columnas desde una posición concreta.

En el ejemplo nos posicionamos en la celda A4. A partir de esa celda nos desplazamos una fila y una columna hasta llegar a la celda B5. Finalmente indicamos que queremos recuperar una matriz de una fila por una columna que se concreta en una celda la celda B5 cuyo valor es 5.

Sin embargo la función DESREF en Excel cobra especial interés cuando trabaja con otras funciones. Por ejemplo con la función SUMA.

Sabemos que en una celda solamente podemos devolver una valor. Así que apartir de los mismos datos utilizados en el ejemplo anterior vamos a ver como sumar la matrir completa a partir de la posición A4.

Nos hemos posicionado en A4 y hemos definido un desplazamiento de 0 filas y de 0 columnas. Sin embargo le decimos que queremos que nos devuelva un rango de 3 filas y 3 columnas, precisamente, la información que deseamos sumar.

Esta matriz resultante es el argumento de entrada de la función suma. Se suman los 9 datos para conseguir el resultado final 45.

Estos ejemplos son didácticos para poder comprender como actúa la función pero poco útiles en el entorno de la oficina.

No obstante existe un concepto “la búsqueda dinámica” que nos permite buscar un determinado valor dentro de un listado. Este listado puede ampliarse y reducirse y nuestra búsqueda dinámica seguirá funcionando. Para lograr este comportamiento es preciso combinar tres funciones muy poderosas de Excel. La función DESREF, la función BUSCARV y la función CONTARA o alguna de sus variantes. A continuación presentamos un ejemplo de búsqueda dinámica.

Ejemplo de uso de la función DESREF en Excel dentro de la función BUSCARV búsquedas dinámicas.

Partimos de un ejemplo muy sencillo. Un listado donde controlamos el estado de stock de una frutería. La frutería cuenta en su almacén con cajas adicionales de diferentes frutas y verduras. Por ejemplo, 1 caja de tomates, 2 cajas de lechugas…

Vamos a configurar en la celda D2 una búsqueda de por ejemplo “Plátanos”. Entonces lo definimos como BUSCARV(D2; $A$1:$B$6; 2; FALSO)

La problemática que se plantea es la siguiente. ¿Qué ocurre si entra un nuevo producto en el almacén? Por ejemplo “Cerezas”. Ampliamos el listado de stock de almacén. Incrementamos las 10 cajas. Pero la búsqueda que habíamos parametrizado no funciona. Tenemos como resultado un #N/A. ¿Por qué? Bueno si nos fijamos en nuestra matriz de búsqueda el rango A1:B6 ya no es válido ahora el rango correcto será A1:B7.

Pero también podría ocurrir que las ventas fuesen muy elevadas y hubiésemos vendido parcialmente parte de los elementos del almacén y entonces la matriz de búsqueda podría ser A1:B4

Entonces ¿Cómo podemos hacer que el rango de la matriz de búsqueda se ajuste al número de elementos de la matriz de búsqueda? Lo vamos a conseguir gracias a la combinación de tres funciones. BUSCARV, DESREF y CONTARA. Pero veámoslo paso a paso.

Partimos de la función de BUSQUEDA y el primer argumento será el producto del cuál queremos consultar el estado del STOCK.

BUSCARV( “Sandía”…

El segundo argumento de nuestra función BUSCARV es un rango o matriz de datos. Sabemos que la función DESREF es capaz de devolver un rango o matriz así haremos uso de ella en este punto.

BUSCARV(“Sandía”; DESREF(A1;0;0;

Nos hemos posicionado en la primera celda del rango y no nos hemos desplazado ni filas ni columnas puesto que nos interesa precisamente el rango que comienza en A1:B?

Seguimos dentro de la función DESREF y tenemos que definir en los dos argumentos que faltan el número de filas y número de columnas que queremos recuperar. Entonces utilizaremos la tercera función la función CONTARA para averiguar cuantos datos hay en la columna A que contiene los productos.

BUSCARV(“Sandía”; DESREF(A1;0;0;CONTARA(A:A);2)

Además decimos el número de columnas que queremos recuperar que para este ejemplo son 2 y así lo hemos indicado.

Ahora solo nos resta volver a nuestra función de búsqueda y rellenar los argumentos restantes. El indicar de columnas IC y tipo de coincidencia “Exacta”.

BUSCARV(“Sandía”; DESREF(A1;0;0;CONTARA(A:A);2);2;FALSO)

De esta manera si añadimos un nuevo producto la búsqueda seguirá funcionando correctamente.

Función DESREF de Excel en Inglés.

La función en inglés se puede escribir como

=OFSSET(reference; rows; cols;[height]; [width])

Para qué sirve la función COINCIDIR en Excel.

Escrito por Iván Antona en 16 julio, 2018. Publicado en Excel Intermedio, Funciones, Funciones Búsqueda y Referencia.

Para qué sirve la función COINCIDIR en Excel.

La función COINCIDIR en Excel nos busca un valor dentro de una lista y si lo localiza nos dice en qué posición concreta dentro de la lista se encuentra. Así por ejemplo si contamos con la lista {Pedro, Juan, Rosa, Marcos, Manuel, Aurora} y aplicamos la función sobre dicho listado indicando como valor buscado “Rosa” entonces el resultado de la función será “3” puesto que Rosa ocupa la posición “3” en la lista. Esta función nos devuelve un valor de tipo entero.

Argumentos de la función COINCIDIR.

La función COINCIDIR en Excel admite tres argumentos de los cuales dos de ellos son obligatorios y el tercero opcional.

• Valor_buscado: Es el valor que deseamos localizar dentro de la lista

• Matriz_Buscada: Se trata de una lista de una dimensión donde localizaremos el dato.

• [Tipo_de_coincidencia]: Es un valor numérico, 1(Menor que),0(Coincidencia Exacta), -1(Mayor que).

Ejemplo de uso de la función COINCIDIR en Excel.

La función coincidir por sí sola no tiene un interés específico. Sin embargo combinada con otras funciones tiene un gran potencial. Vamos a ver un primer ejemplo meramente académico. Contamos con una matriz de datos, en concreto queremos averiguar qué posición ocupa el mes de Junio dentro del “vector de etiquetas” de la matriz.

El resultado de la función es 3. La primera posición del vector se corresponde con “C2” la segunda posición para “Mayo” y finalmente posición 3 para “Junio”.

Ejemplo de uso de la función COINCIDIR dentro de la función BUSCARV.

Es habitual combinar la función COINCIDIR con otras funciones para aprovechar su potencial. Por ejemplo con la función BUSCARV podemos controlar el argumento “indicador de columnas” mediante esta función.

Partimos de una idea muy sencilla, la función COINCIDIR en Excel, devuelve un valor entero. El indicador de columnas es precisamente un valor entero que se refiere a la columna en la que reside el dato que queremos recuperar con BUSCARV.

Aprovechando el potencial de COINCIDIR podemos averiguar en qué posición se encuentra “Mayo” dentro del rango G2:K6 que son precisamente las etiquetas de meses de la “matriz de búsqueda”. Al localizar el valor en la posición 2 entonces la función COINCIDIR en Excel da como salida precisamente ese dato que entonces se convierte en argumento de entrada de BUSCARV.

Si quisiéramos extender la función para los meses de Junio, Julio y Agosto, tendremos que controlar las referencias relativas de las celdas implicadas en la operación.

Función COINCIDIR en Inglés.

La función en inglés se puede escribir como

=MATCH(array; row_num;[num_Colum])

Función INDICE en Excel.

Escrito por Iván Antona en 13 julio, 2018. Publicado en Excel Intermedio, Funciones, Funciones Búsqueda y Referencia.

Descripción de la función INDICE en Excel

La función INDICE en Excel permite dado un listado de datos y una posición dentro del listado recuperar el valor que ocupa esa posición. Así por ejemplo si contamos con la lista {Pedro, Juan, Rosa, Marcos, Manuel, Aurora} y aplicamos la función sobre dicho listado indicando como posición “3” la función nos devolverá “Rosa” puesto que “Rosa” ocupa la tercera posición en la lista. En definitiva la función INDIRECTO nos devuelve el contenido de una celda que hemos referenciado. Este contenido puede ser un texto, un número, etc…

Argumentos de la función.

La función índice en Excel admite tres argumentos cuando se trabaja en modo referencia de los cuales dos de ellos son obligatorios y el tercero opcional.

Matriz: Es la lista de datos
Número de fila: Es el número de la fila donde reside el dato que queremos recuperar.
[Número de columna]: Es el número de la columna donde reside el dato. Este argumento no es obligatorio si la lista es unidimensional.

La función índice en Excel admite cuatro argumentos cuando se trabaja en modo matricial

Referencia: Listados que se utilizarán en la función, cada listado se separa del anterior mediante “;”
Número de fila: Es el número de la fila donde reside el dato que queremos recuperar.
[Número de columna]: Es el número de la columna donde reside el dato. Este argumento no es obligatorio si la lista es unidimensional.
[Núm_área]: Se refiere a algún listado de los definidos en el primer argumento.

Ejemplo de uso de la función INDICE en Excel. Con una lista de una dimensión.

Decimos que una lista es de una dimensión cuando los datos se presentan en una única fila o en una única columna. Así por ejemplo las siguientes listas son de una dimensión.

Mediante la función INDICE en Excel podremos recuperar cualquier dato de la lista conociendo previamente que dato en concreto queremos recuperar. Así por ejemplo del primer listado si queremos recuperar “Ciudad lineal” Introducimos como argumentos de la función el listado B4:B16 y precisamente la posición de “Ciudad Lineal” en la lista que es 10.

De igual manera podremos recuperar valores de una lista que está configurada horizontalmente así por ejemplo si queremos recuperar el mes “Febrero” de la segunda lista tendremos que seleccionar el listado completo E4:H4 y la posición concreta en la lista 3.

Nótese que al tratarse de un listado configurado horizontalmente también podremos definir la función como:

=INDICE(E4:H4;3)

=INDICE(E4:H4;1;3)

=INDICE(E4:H4;;3)

Siendo las dos última la forma más lógica de plantear la función. Tengamos en cuenta que Excel no deja de ser “una matriz”. Lo veremos mucho más claro al trabajar con listas o matrices de más de una dimensión.

Ejemplo de uso de la función INDICE en Excel. Con una lista de varias dimensiones.

Cuando nuestro listado contempla más de una fila y más de una columna tendremos que definir la coordenada exacta del valor que deseamos recuperar. La forma que tenemos en Excel de referenciar una celda de forma unívoca es mediante la columna y la fila. Entonces para recuperar un dato de un listado mediante la función indirecto será preciso indicar la fila y columna donde se ubica el dato.

Así para recuperar el valor “11” tendremos que indicar que se encuentra en la fila 3 y la columna 3.

Ejemplo de uso de la función INDICE en Excel. Con varias listas de varias dimensiones. Formato matricial.

Para utilizar este formato de función precisamos de varios listados que normalmente estarán configurados de forma similar. Por ejemplo si tratamos las ventas de un conjuntos de vendedores en varios cuatrimestres tendremos una hoja con el siguiente modelo.

La función INDICE en formato matricial nos permite ver por ejemplo las ventas para “Marta” en cualquier cuatrimestre para el tercer mes del cuatrimestre.

Nótese que la función en su primer argumento precisa recibir los rangos en el orden que después controlaremos con el 4º argumento.

Así el primer cuatrimestre se corresponde con el valor 1, 2 para el segundo y 3 para el tercero. Internamente Excel enlaza el rango A2:E6 con el valor 1 el rango G2:K6 con el valor 2 y el rango M2:Q6 con el valor 3.

Así para ver las ventas de Marta en el segundo cuatrimestre para el mes de Julio la función será:

Función INDICE en Inglés.

La función en inglés se puede escribir como

=Index(array; row_num;[num_Colum])

=Index(ref,núm_fila;[núm_columna];[núm_área])