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:

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

Elaborando informes con tablas dinámicas – II

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

En éste artículo «Elaborando informes con tablas dinámicas – II» 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:

¿Qué cifra de ventas se ha obtenido en cada zona para cada servicio? Puedes consultar como contestamos a esta pregunta en el artículo anterior.
Evolución de las ventas mensual y trimestral de cada servicio
Cuál es nuestro representante de ventas que ha conseguido más ventas y el que menos en cada servicio.

Evolución de las ventas mensual y trimestral de cada servicio

De nuevo comenzamos pensando que campos utilizaremos en nuestro informe. El precio ira en la zona de “Valores” y parece lógico que además sea agrupado con la suma. Los “Servicios” como en el anterior caso pueden ir colocados en “Columnas”. Y finalmente el campo “Fecha” lo llevaremos a zona de filas.

Si presentamos la información tal y como hemos diseñado en nuestro esquema anterior obtendremos un resultado como el que presentamos a continuación:

Sin embargo, en la pregunta que nos formulan, hablan de meses y trimestres. Para resolver esta problemática nos situaremos en nuestro informe de tabla dinámica y haremos clic con el botón derecho sobre cualquiera de los valores de fecha. En el menú contextual elegiremos la opción “Agrupar” y haremos clic sobre «Meses y Trimestres» para que queden sombreados en azul como en la imagen. Seguidamente haremos clic en “Aceptar”.

Como resultado veremos que la información queda segmentada tal y como andábamos buscando. Además en la zona de campos de tabla dinámica aparece un nuevo campo “Trimestres”

Para finalizar nuestro informe convertiremos cada una de las cifras a formato moneda € y además centraremos los datos para que queden alineados visualmente.

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

Tablas dinámicas en excel- Entrada 53-002

Elaborando informes con tablas dinámicas

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

En los apartados anteriores hemos visto cómo realizar un informe de tabla dinámica. Simplemente es necesario revisar los datos del origen de datos para que no existan filas o columnas en blanco, que además existan datos homogéneos y estructurados bajo columnas etiquetadas con nombres claros y relevantes. Ahora vamos a continuar elaborando informes con tablas dinámicas.

Puedes consultar más información en los apartados anteriores:

Una vez conseguidos los datos bajo esos criterios y lanzado el asistente de tablas dinámicas podremos conseguir distintos informes en función de cómo coloquemos los campos en la zona de diseño. Sin embargo vamos a profundizar un poco más esta forma de trabajar.

Recuerda que el fichero que se utiliza en las explicaciones se puede descargar aquí:

[wpdm_package id=’2801′]

Elaborando informes con tablas dinámicas. Batería de preguntas.

Como podemos observar en nuestra base de datos tenemos datos de transacciones o ventas de dos tipos de servicios, formación y consultoría, en diferentes zonas dentro de España.

Además en cada zona existe un responsable de oficina de ventas. Mediante distintos informes de tablas dinámicas queremos contestar a las siguientes preguntas:

¿Qué cifra de ventas se ha obtenido en cada zona desglosada por servicio?
Evolución de las ventas mensual y trimestral de cada servicio.
¿Cuál es nuestro representante de ventas que ha conseguido más ventas en cada uno de los servicio ofrecido? ¿y el que menos?

¿Qué cifra de ventas se ha obtenido en cada zona desglosada por servicio?

Esta pregunta quedo contestada en apartados anteriores. Sin embargo en esta ocasión vamos a abordar el problema realizando un pequeño análisis.

¿Qué campos estarán involucrados en este informe? ¿Podremos contestarlo elaborando informes con tablas dinámicas.?

Vamos a necesitar los campos de Zona, Servicio y Precio. Ahora tenemos que decidir cómo lo vamos a presentar en nuestro informe de tabla dinámica.

Una vez hemos definido como colocaremos los campos en nuestro informe nos disponemos a añadir la tabla dinámica seleccionando los datos e insertando la tabla en una nueva hoja. Una vez creada la tabla arrastramos los campos en la zona de diseño según hemos establecido en la anterior figura. Cabe destacar que el “Valor” por defecto nos devuelve la suma de todas las cifras de ventas agrupadas por zona y tipo de servicio. El tipo de cálculo realizado se denomina “Campo calculado” y no necesariamente tiene porque ser una suma. Más adelante veremos otro ejemplo con otro tipo de operación.

[wpdm_package id=’2801′]