Ing.
Jazmin Washbrum
Ing. Jazmin Washbrum
Introducción
Validación de Datos
Criterios de validación
Descripción del control de datos
Acceso a validación de datos
Validación de tipo: Decimal, hora, fecha, Evitar duplicados, Crear lista
desplegable
Ing. Jazmin Washbrum
Es la ciencia que se encarga de examinar un conjunto de datos con el propósito de sacar
conclusiones sobre la información para poder tomar decisiones, o simplemente ampliar los
conocimientos sobre diversos temas.
El análisis
Ayuda de datos para Ayuda
la toma
de decisiones
SE LO CONSIDERA UNA
MÉTODOLOGÍA Crear soluciones que
Enriquecer nuestros se centren en
productos las necesidades de
la empresa
Las industrias usan el análisis de datos para sacar conclusiones y decidir acciones a implementar.
Ing. Jazmin Washbrum
Podría definirse como la ciencia que se encarga de recopilar, organizar, procesar,
analizar e interpretar los datos con el fin de deducir las características de una
población
El objetivo de la estadística es mejorar la comprensión
de hechos a partir de datos. (Moore, 2006, p.267)
Ing. Jazmin Washbrum
Ing. Jazmin Washbrum
Ing. Jazmin Washbrum
Es una importante herramienta en Excel que nos ayudará a evitar la introducción de
datos incorrectos en la hoja de cálculo de manera que podamos mantener la
integridad de la información en nuestra base de datos.
Es importante porque nos aseguramos que el usuario ingrese el tipo de dato
correcto.
Ing. Jazmin Washbrum
En la siguiente imagen se puede observar que
la celda C5 muestra un error en el cálculo de la
edad ya que el dato de la celda B5 no
corresponde a una fecha válida.
Este tipo de error puede ser
prevenido si utilizamos
la validación de datos en
Excel al indicar que la celda B5
solo aceptará fechas válidas.
Ing. Jazmin Washbrum
El comando Validación de datos que utilizaremos se encuentra en la ficha: Datos y
dentro del grupo Herramientas de datos.
Al pulsar dicho comando se abrirá el cuadro de
diálogo Validación de datos donde, de manera
predeterminada, la opción Cualquier valor estará
seleccionada, lo cual significa que está permitido
ingresar cualquier valor en la celda.
Ing. Jazmin Washbrum
Podremos elegir alguno de los criterios de
validación disponibles para hacer que la
celda solo permita el ingreso de un
número entero, un decimal, una lista, una
fecha, una hora o una determinada
longitud del texto.
Ing. Jazmin Washbrum
Para aplicar la validación de datos sobre una celda
específica, se deberá asegurarse de seleccionar dicha
celda y posteriormente ir al comando Datos >
Herramientas de Datos > Validación de datos.
Por el contrario, si se quiere
aplicar el mismo criterio de
validación a un rango de celdas,
se deberá seleccionar dicho
rango antes de ejecutar el
comando Validación de datos y
eso hará que se aplique el mismo
criterio para todo el conjunto de
celdas.
Ing. Jazmin Washbrum
Todos los criterios de validación mostrarán una caja de selección con el texto Omitir blancos.
De manera predeterminada, la opción Omitir blancos estará seleccionada para cualquier criterio, lo cual
significará que al momento de entrar en el modo de edición de la celda podremos dejarla como una celda
en blanco
Si quitamos la selección de la opción Omitir blancos, estaremos obligando al usuario a ingresar un valor
válido una vez que entre al modo de edición de la celda. Podrá pulsar la tecla Esc para evitar el ingreso del
dato, pero no podrá pulsar la tecla Entrar para dejar la celda en blanco.
Ing. Jazmin Washbrum
Seleccionamos una celda específica o un rango de celdas, y escogemos la opción: Datos >
Herramientas de Datos > Validación de datos.
Se revisara en Excel todos los casos y todas las opciones:
Ing. Jazmin Washbrum
Si deseas eliminar el criterio de validación
de datos aplicado a una celda o a un rango,
deberás seleccionar dichas celdas, abrir el
cuadro de diálogo Validación de datos y
pulsar el botón Borrar todos.
Al pulsar el botón Aceptar se habrá removido cualquier validación
de datos aplicada sobre las celdas seleccionadas.
Ing. Jazmin Washbrum
Ing. Jazmin Washbrum
Manejo de Errores y auditoria
Tipos de errores
Función SI.ERROR()
Uso de la ventana inspección
Auditoria en formulas
Ing. Jazmin Washbrum
Los errores en Excel son comunes cuando trabajamos con
fórmulas, pero existen diferentes métodos para tratar con cada uno
de ellos de manera que podemos tomar una decisión adecuada al
momento de detectar alguno de ellos.
Una razón por la que generalmente deseamos tratar con los errores
de Excel es porque deseamos desplegar un mensaje personalizado
en lugar de permitir que Excel muestre sus códigos de error. En
cualquier caso, el primer paso que debemos dar es conocer si un
valor es efectivamente un error.
Ing. Jazmin Washbrum
Cuando estamos trabajando con Excel a veces
obtenemos errores, es decir, un valor en una
celda que comienza con el signo numeral "#".
Esto no siempre es malo, de hecho, puede estar
bien si interpretamos correctamente lo que el
programa nos está indicando.
Es conveniente saber que para corregir el error
puede que sea necesario modificar la fórmula
misma o alguna fórmula o valor de otras celdas o
rangos a las que está haciendo referencia.
Existen siete tipos de errorres y Excel los utiliza
para advertirnos de algún "posible" problema.
Ing. Jazmin Washbrum
Si se observa una celda llena de símbolos # (numeral), lo que Excel
está tratando de decir es que el contenido de esa celda no cabe
dentro de ella.
Solución: Ajusta el ancho de la columna y el mensaje desaparecerá
de inmediato.
ERROR #¡DIV/0!
El error se produce cuando estás dividiendo un valor entre cero.
Solución: Remover la división entre cero.
Ing. Jazmin Washbrum
Este error significa que Excel no encuentra el valor al que se está
refiriendo. Es decir, el valor no está disponible. Este tipo de error es
muy común al utilizar la función BUSCARV. (N/A=Not Available=N/D)
Solución: Utiliza la función SI.ERROR para atrapar el error:
=SI.ERROR(VALOR;VALOR_SI_ERROR)
=SI.ERROR(BUSCARV(…);“ERROR EN CALCULO”)
Ing. Jazmin Washbrum
La razón más común por la que se genera este error es el haber
escrito mal el nombre de una función. También se produce por
escribir mal un rango nombrado.
Solución: Asegúrate de haber escrito el nombre de la función
correctamente así como todos los rangos nombrados que utilices en
las funciones.
Ing. Jazmin Washbrum
Este error es poco común, pero generalmente es consecuencia de
haber especificado incorrectamente un rango. Una fórmula que
regresará este tipo de error es la siguiente:
=SUMA(A1:A5 B1:B5)
El error se produce porque olvidé colocar el separador (;) entre los
rangos especificados.
Solución: Asegúrate de que has especificado correctamente los
rangos dentro de la fórmula.
Ing. Jazmin Washbrum
Hay ocasiones en que los cálculos en Excel son muy exhaustivos y
las fórmulas llegan a calcular valores mucho más grandes que las
capacidades de Excel y es cuando obtienes un error de este tipo.
Solución: Verifica que no estás llamando repetitivamente a las
funciones y que generen un número demasiado grande.
Ing. Jazmin Washbrum
Este es uno de los errores más comunes de Excel y sucede cuando
una celda trata de referenciar otra celda que no puede ser
encontrada porque se ha eliminado. Por ejemplo, escribe la
siguiente fórmula:
=SUMA(D1:D5;E1:E5;F1:F5)
Posteriormente elimina la columna F. Inmediatamente se mostrará
este tipo de error.
Solución: Verifica que los rangos a los que hace referencia la
fórmula aún existan o de lo contrario realiza los ajustes necesarios.
Ing. Jazmin Washbrum
Es generado por utilizar argumentos de tipo texto cuando en
realidad la función espera valores numéricos. Considera la
siguiente función:
=(D1/E1)
La función DIVISION regresará este tipo de error porque los
argumentos no nos todos numéricos.
Solución: Verifica que los tipos de datos de los argumentos son
los adecuados, en especial los argumentos que esperan un
valor numérico.
Ing. Jazmin Washbrum
Puede usar la función si.error para interceptar y controlar errores en
una fórmula. Si.error devuelve un valor que se especifica si una fórmula
lo evalúa como un error; en caso contrario, devuelve el resultado de la
fórmula.
Sintaxis SI.ERROR(valor; valor_si_error)
value Obligatorio. Es el argumento donde busca un error.
value_if_error Obligatorio. El valor que se devuelve si la fórmula se
evalúa como un error. Se evalúan los siguientes tipos de error: #N/A,
#¡VALOR!, #¡REF!, #¡DIV/0!, #¡NUM!, #¿NOMBRE? o #¡NULO!.
Si el valor o el value_if_error es una celda vacía, si. error la tratará como
un valor de cadena vacía ("")
Ing. Jazmin Washbrum
nos ayuda a supervisar los valores de determinadas celdas de
manera que siempre tengamos a la vista su resultado aun
cuando estemos trabajando en múltiples hojas.
En la ficha Fórmulas, dentro del grupo Auditoría de fórmulas,
debo hacer clic en el botón Ventana Inspección.
La ventana de inspección podemos
verla en todo momento incluso es
plegable, se la puede juntar en la parte
superior en formulas o en la parte
inferior, se puede ir a otra hoja del
archivo y siempre vemos la ventana.
Ing. Jazmin Washbrum
Rastrear Precedentes: Muestran flechas que indican las celdas que afectan al valor de la
celda seleccionada actualmente.
Rastrear Dependientes: Muestran flechas que indican las celdas afectadas por el valor de la
celda seleccionada actualmente.
Quitar Flechas: Quita las flechas insertadas del resultado de rastrear precedentes y
pendientes.
Mostrar Formulas: Muestra la formula utilizada en cada celda en vez del valor resultante.
Comprobación de Errores: Busca errores comunes en las celdas, y rastrea las celdas que
tengan este error.
Evaluar Formula: Evalúa la formula de manera individual o parte por parte para depurar el
proceso.
ING. JAZMIN WASHBRUM
ING. JAZMIN WASHBRUM
Predicción de datos
La Herramienta Previsión o
Pronóstico
Predecir tendencias de
datos
ING. JAZMIN WASHBRUM
Es el arte de anticipar las tendencias de datos.
El análisis predictivo consiste en la definición de modelos a
través de algoritmos que utilizan los datos recopilados. Con
estos modelos, se pretende anticipar y predecir las tendencias
relativas al comportamiento del consumidor.
ING. JAZMIN WASHBRUM
Si tiene datos históricos de duración definida, puede usarlos para
crear una previsión. Cuando crea una previsión, Excel crea una nueva
hoja de cálculo con una tabla de los valores históricos y
pronosticados y un gráfico que muestra dichos datos. Una previsión
puede ayudarle a predecir aspectos, como las ventas futuras, los
requisitos de inventario o las tendencias de los consumidores, etc.
ING. JAZMIN WASHBRUM
Crear una previsión:
1.- En una hoja de cálculo, escriba dos series de datos que se corresponden
la una a la otra:
Una serie con entradas de fecha u hora para la escala de tiempo
Una serie con los valores correspondientes
Estos valores se pronosticarán para fechas futuras.
2.- Seleccione los datos de la tabla de Ventas.
3.- En la pestaña Datos, en el grupo Previsión, haga clic en Hoja de
previsión.
4.- En el cuadro crear hoja de previsión , seleccione un gráfico de líneas o
un gráfico de columnas para la representación visual de la previsión.
5.- En el cuadro Fin de la previsión, seleccione una fecha de finalización y,
luego, haga clic en Crear.
ING. JAZMIN WASHBRUM
ING. JAZMIN WASHBRUM
Excel crea una nueva hoja de cálculo con una tabla de los valores históricos y
pronosticados y un gráfico que muestra dichos datos.
Encontrará la nueva hoja de cálculo justo a la izquierda ("delante de") la hoja en la que
ha especificado la serie de datos.
ING. JAZMIN WASHBRUM
Se puede extender una serie de valores que se ajusten a una tendencia lineal
simple o una tendencia geométrica exponencial mediante el controlador de
relleno.
ING. JAZMIN WASHBRUM
Crear una serie de tendencia lineal de ajuste perfecto
En una serie de tendencia lineal, valor del paso: la diferencia entre el valor inicial y
el valor siguiente de la serie, se agrega al valor inicial y, a continuación, a cada valor
posterior.
Se tienen 3 formas de realizarlo en Excel:
Opción #1:
Seleccione al menos dos celdas que contengan los valores de inicio de la tendencia.
Para aumentar la precisión de las series de tendencia, seleccione más de dos
valores de inicio.
Arrastre el controlador de relleno en la dirección de aumentar o reducir los valores.
Por ejemplo, si seleccionó las celdas F1: H1 y los valores iniciales de estas celdas
son 3, 5 y 7, arrastre el controlador de relleno hacia la derecha para rellenar con
valores de tendencia crecientes, o arrastre hacia la izquierda para rellenar con
valores inferiores.
ING. JAZMIN WASHBRUM
Opción # 2:
Con esta opción se debe marcar las celdas con datos y sin datos arrastrando el cursor
con clic derecho, y seleccionamos tendencia lineal
ING. JAZMIN WASHBRUM
Opción #3:
Otra opción es con la formula =TENDENCIA
ING. JAZMIN WASHBRUM
Crear una serie de tendencias de crecimiento
exponencial
En una serie de tendencia geométrica, el valor inicial se multiplica por el valor de
paso para obtener el valor siguiente de la serie. El producto resultante y cada
producto posterior se multiplica el valor del paso.
Seleccione al menos dos celdas que contengan los valores de inicio de la tendencia.
Para aumentar la precisión de las series de tendencia, seleccione más de dos valores
de inicio.
Mantenga presionada la tecla CONTROL y arrastre el controlador de relleno en la
dirección que desea rellenar con valores crecientes o decrecientes.
Por ejemplo, si seleccionó las celdas C1:E1 y los valores iniciales de estas celdas son 3,
5 y 7, arrastre el controlador de relleno hacia la derecha para rellenar con valores de
tendencia crecientes, o arrastre hacia la izquierda para rellenar con valores inferiores.
ING. JAZMIN WASHBRUM
Liberar CONTROL y el botón del mouse y, a continuación, en el menú contextual,
haga clic en Tendencia geométrica.
Excel calcula la tendencia geométrica y continúa la serie en las celdas
seleccionadas.
Para mejor entendimiento
de esta opción: se muestra
como se debe marcar las
celdas con datos y sin datos
arrastrando el cursor con
clic derecho, y
seleccionamos tendencia
GEOMETRICA
Ing. Jazmin Washbrum
Herramienta análisis de datos - Excel
Ing. Jazmin Washbrum
Serie de Tiempo: Conjunto de observaciones correspondientes a los valores de una variable, medidas
en puntos sucesivos a lo largo del tiempo o durante periodos sucesivos de tiempo.
Pronóstico: Es la predicción de los valores futuros de una serie de tiempo.
Suavizamiento exponencial: Técnica de pronóstico en la que se emplea un promedio ponderado de
valores pasados de la serie de tiempo.
Constante de suavizamiento: Es el parámetro que se emplea en el modelo de suavizamiento
exponencial como peso para el valor más reciente de la serie de tiempo.
Cuadrado medio debido al error (CME) Es una medida de la exactitud que se obtiene con un método
de pronóstico. Esta medida es el promedio de la suma de los cuadrados de las diferencias entre los
valores pronosticados para la serie de tiempo y sus valores reales.
Ing. Jazmin Washbrum
Se estudian tres métodos de pronóstico:
Promedios móviles
Promedios móviles ponderados, y
Suavizamiento exponencial
Estos métodos tienen por objeto suavizar las fluctuaciones aleatorias ocasionadas por el componente
irregular de la serie de tiempo, razón por la que se les conoce como métodos de suavizamiento. Los
métodos de suavizamiento son adecuados para series de tiempo estables; es decir, para aquellas
series que no muestran efectos importantes de tendencia, cíclicos o estacionales porque se adaptan
muy bien a los cambios en el nivel de la serie de tiempo.
Ing. Jazmin Washbrum
Los métodos de suavizamiento son fáciles de utilizar y, por lo general, se obtiene una buena exactitud
en pronósticos a corto plazo, como, por ejemplo, pronósticos para el periodo siguiente.
Uno de estos métodos, el suavizamiento exponencial, tiene requerimientos mínimos de datos por lo
que es un método no costoso para las empresas y adecuado cuando se requiere de pronósticos para un
gran número de artículos.
Ing. Jazmin Washbrum
Usa un promedio ponderado de los valores pasados de la serie de tiempo para
calcular un pronóstico; es un caso especial del método de promedios ponderados
móviles; en este caso sólo hay que elegir un peso, el peso para la observación
más reciente. Los pesos para los demás datos se calculan automáticamente y son
más pequeños a medida que los datos son más antiguos.
Ing. Jazmin Washbrum
El criterio que se usará para determinar el valor adecuado para la constante de suavizamiento
α es el mismo que el propuesto para determinar el número de periodos a incluir en el cálculo
de los promedios móviles. Es decir, se elige el valor α que minimice el cuadrado medio
debido al error (CME).
Ing. Jazmin Washbrum
A continuación se muestran las instrucciones para algunas herramientas y funciones estadísticas que
son de utilidad para el cálculo de pronósticos, con las que cuenta Excel.
Ing. Jazmin Washbrum
Para mostrar cómo usar Excel para
obtener pronósticos mediante el
método de suavizamiento
exponencial, realizar lo siguiente:
Paso 1. Seleccionar Datos del menú
de Herramientas.
Paso 2. Elegir Análisis de datos.
Paso 3. Elegir Suavizamiento
exponencial en la lista Funciones
para análisis – Y clic en Aceptar.
Ing. Jazmin Washbrum
Paso 4. En el cuadro de diálogo Suavizamiento
exponencial, colocar lo siguiente:
Rango de entrada: Seleccionar la columna
de datos históricos con su
rótulo/encabezado.
Factor de suavizamiento: Colocar el valor
de (1- α).
Activar la casilla “Rótulos” (si el rótulo se
incluyo en el rango de entrada).
Rango de salida: Identificar una celda
donde se colocarán todos los resultados.
Activar la casilla “Crear gráfico”.
Y clic en Aceptar.
Ing. Jazmin Washbrum
Paso 5. Aparece los resultados en la columna Pronóstico y el gráfico respectivo.
