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:

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])

Validación de datos en Excel

Escrito por Iván Antona en 11 abril, 2017. Publicado en Excel Intermedio, Herramientas Integradas.

Cómo utilizar la validación de datos en Excel

Cuando trabajamos en entornos de oficina donde una hoja de Excel es compartida por varias personas y cada una de ellas realiza labores diferentes sobre la hoja entonces es fundamental facilitar la entrada de datos de manera que se eviten los fallos de mecanografía es lo que se conoce como validación de datos en Excel.

Además podemos preestablecer de partida mediante la validación de datos una nomenclatura común de cara a convertir los ficheros en estructuras de información homogénea. Por ejemplo si estamos trabajando con una columna en la que varias personas actualizan “fechas” estaremos entonces de acuerdo en que el preestablecer de partida como se rellenan esas fechas es un factor importante para evitar errores.

Se puede ajustar como se rellenan los datos en cada celda y presentar un mensaje por pantalla a la persona que rellena la celda cuando intenta introducir un valor fuera del estándar predefinido. De esta manera todos los que trabajaran con la hoja de Excel compartida utilizarán un criterio común en el rellenado de datos gracias a la validación de datos en Excel.

Por ejemplo mediante la validación de datos en Excel se pueden definir reglas para introducir números dentro de unos umbrales (números mayores que 0 y menores que 10), listas de valores estáticas, fechas entre unos rangos determinados (fechas entre el 01-01-2017 y 31-12-2017), horas concretas (09:00 y 19:00), cadenas de texto con un máximo y mínimos de caracteres e incluso podremos predefinir una función que evalúe el dato introducido y en base a algún criterio permita que se escriba o no. Para acceder a la validación de datos en Excel tendremos que hacer clic sobre el menú “Datos” y después sobre la opción “Validación de datos”.

Ajustes de la validación de datos en Excel

Dentro del menú de validación de datos entonces encontraremos tres pestañas. Nos detenemos ahora en la primera de ellas. “Configuración”. Y veremos un ejemplo de validación de datos en Excel para cada una de las opciones del menú desplegable.

Cualquier valor: No existe validación de datos. Es el estado por defecto definido para todas las celdas de Excel.
Número entero: Utilizaremos ésta validación de datos cuando necesitamos que se rellene una celda con un valor entero y no decimal. Por ejemplo el campo “Edad”. Podemos definir que se introduzcan valores comprendidos entre 1 y 110 años.

Número decimal: Con los números decimales se sigue el mismo procedimiento que con los números enteros. Sin embargo Excel nos mostrará un error cuando el dato introducido sea diferente a un número decimal.Un ejemplo puede ser el formatear toda una columna para que nos rellenen los porcentajes de descuento de un determinado producto o servicio.

Lista: El ejemplo de validación de datos en Excel con uso de listas es el más extendido y además el más vistoso. Nos permite dentro de una celda mostrar un desplegable que muestre diferentes valores y la persona que rellena la celda tendrá que seleccionar uno de esos valores. Evitando así cualquier tipo de error en la mecanografía. Además se utiliza para preparar listas enlazadas en Excel combinado con el uso de la función indirecto en Excel. El efecto que se conseguido tras aplicar esta validación de datos en un listado desplegable que como hemos mencionado evita que la persona que rellena la hoja de datos introduzca textos y así ayudaremos a que no se cometan errores de escritura.

Fechas: El caso de fechas al igual que ejemplos anteriores obligará a que los datos que se rellenen se encuentren entre dos fechas predefinidas. Si por ejemplo solo se pueden rellenar fechas de un trimestre o un año concreto esta validación de datos en Excel cubrirá nuestras necesidades.
Horas: En el caso de las fechas podremos definir una horquilla de tiempo entre las 9:00 am y las 19:00 de la tarde si por ejemplo nos referimos a un horario de atención o un horario en el que nuestros comerciales se desplazan a realizar visitas a los clientes.
Texto de un número determinado de caracteres: Para esta validación y como su nombre indica lo que se controla es que no se introduzcan cadenas de caracteres superiores a un determinado número de letras. Pero también podemos usarlo para obligar a que al menos se escriba una palabra o un texto mínimo.
Personalizado: Es el último y más complejo de todos los sistemas de validación de datos. En este tipo de validación se comprueba el valor introducido en la celda mediante una función de Excel (Ojo! Solamente aquellas funciones o fórmulas cuya salida o resultado sea valor verdadero o falso) y si se cumple la condición definida en la fórmula o función entonces se permitirá el dato introducido. Por ejemplo si queremos validar que solo se introduzcan valores enteros mayores o iguales a 0 podremos utilizar la siguiente validación de datos en Excel personalizada.

Redondear con un círculo datos no válidos

Gracias a la herramienta redondear con un círculo podremos averiguar dentro de nuestra hoja de cálculo que celdas no cumplen las reglas de validación de datos.

Hemos visto que cuando una celda esta vacía y previamente se ha aplicado sobre ella una regla de validación de datos entonces a la hora de rellenar esa celda la regla deberá ser cumplida. Sin embargo si aplicamos reglas de validación de datos en hojas de Excel que ya tenían datos en las celdas Excel permite que esos datos permanezcan en su celda original y no se estará aplicando la regla de validación de datos. Entonces, ¿Cómo podemos averiguar que datos de los que ya estaban cargados en la hoja incumplen las reglas de validación que hemos creado?

Gracias a redondear con un círculo los datos no válidos podremos resaltar aquellos datos que no cumplen con la validación que hubiéramos aplicado. Seleccionaremos todo el rango sobre el que hemos creado las reglas de validación de datos en Excel y seguidamente seleccionamos la opción “Marcar con un circulo los datos inválidos”

Como resultado veremos en nuestro fichero de Excel todos aquellos datos que estaban precargados en la hoja de Excel y que no cumplen con las normas que hemos establecido en validación de datos.

Podemos hacer desaparecer los círculos en la opción “Eliminar círculos de validación.”

Elaborando informes con tablas dinámicas – III

Escrito por Iván Antona en 25 marzo, 2017. Publicado en Excel Intermedio, Herramientas Integradas, Tablas dinámicas.

En éste artículo “Elaborando informes con tablas dinámicas – III” continuaremos la serie que habíamos comenzado en anteriores entradas al blog donde habíamos entendido las tablas dinámicas, después habíamos preparado los datos para lanzar tablas dinámicas, y una vez los datos estaban listos habíamos creado la primera tabla dinámica .

Finalmente habíamos comenzado a elaborar distintos informes de tablas dinámicas que contestaran a preguntas como:

Cuál es nuestro representante de ventas que ha conseguido más ventas y el que menos en cada servicio.

Comenzamos como en casos anteriores visualizando mentalmente las etiquetas de columna que formarán parte en nuestro informe de tabla dinámica. En esta ocasión serán los “Vendedores”, los “Servicios” y finalmente las cifras de venta que como en casos anteriores obtendremos de las ventas.

Una vez analizados los datos que queremos mostrar representamos la información con el informe de tabla dinámica.

Vemos que los datos no están ordenados. Queremos visualizar en primer lugar aquel vendedor que ha realizado el mayor número de ventas de consultoría. Igualmente ocurre con las cifras de formación. Para resolverlo situamos el ratón sobre la primera cifra y de nuevo como cuando agrupamos fechas hacemos clic con el botón derecho. Sin embargo esta vez seleccionaremos la opción “Ordenar” y después “De mayor a menor”.

Ahora daremos a las cifras el formato de moneda y observamos lo que ocurre si tratamos de ordenar los datos de ventas de “Formación” también de mayor a menor.Vemos que nos ha desordenado “Consultoría”

Para resolver el problema enviaremos el campo “Servicio” a “Filtros” y obtendremos un informe para Consultoría y otro informe distinto para Formación. Finalmente eliminaremos el campo “Servicio” de la zona de columnas y así tendremos un informe con el resultado de ambos tipo transacciones para cada representante de ventas. Una vez arrastrado el campo “Servicio” a la zona de filtros debemos desplegar en la zona de presentación de informe y marcar el filtro que deseamos aplicar como se muestra en la imagen. Realizaremos la misma operación para “Formación” y en ambos casos ordenaremos los datos de mayor a menor.

Para averiguar el mejor y peor vendedor tendremos en cuenta el total acumulado tanto de formación como de consultoría. Para ello eliminaremos el campo servicio de nuestro informe de tabla dinámica.

Recuerda que puedes descargar el fichero con los datos originales para que pruebes tu mismo a hacer las tablas dinámicas desde este acceso directo:

[wpdm_package id=’2801′]