Descarga la aplicación para disfrutar aún más
Vista previa del material en texto
Capítulo Análisis de Datos II En este capítulo trataremos: Herramientas de análisis estadístico Uso de cuadros de histogramas Uso de Solver SENATI-Computación e Informática 85 s de datos III Herramientas de Análisis Estadístico Excel presenta un conjunto de herramientas para proyectar resultados de ingresos, gastos de su negocio. A continuación se describe el uso de estas herramientas. Tendencia lineal de ajuste perfecto automáticamente En una serie lineal, el incremento, o diferencia entre el primer valor de la serie y el siguiente, se agrega al valor inicial y, a continuación, a cada uno de los valores siguientes. Paso a Paso: Tendencia lineal de ajuste perfecto 1. La empresa “Productos Agrícolas SAC” desea proyectar sus ventas del último semestre del año, para ello nos presenta la información de ventas de los seis primeros meses. 2. Utilizaremos el procedimiento para proyectar la tendencia lineal a. Seleccionar el bloque de celdas A4:B9 b. Señalar con su mouse este cuadradito y arrastrar con el botón derecho del mouse hasta la fila 15, luego soltar el mouse c. Del menú contextual presentado elegir La opción “Tendencia lineal” SENATI-Computación e Informática 87 Microsoft Office Excel 2007 3. Se debe mostrar como resultado el siguiente cuadro Ventas del primer semestre Ventas proyectadas con tendencia lineal automática, para el último semestre Tendencia geométrica de forma automática En una serie geométrica, el valor inicial se multiplica por el incremento para obtener el siguiente valor en la serie. El resultado y los siguientes resultados se multiplican a continuación por el incremento. Paso a Paso: Tendencia geométrica de forma automática 1. Utilizando el cuadro anterior, pero esta vez proyectar con tendencia geométrica. b. Seleccionar el bloque de celdas B4:B9 b. Arrastrar con el botón derecho del mouse hasta la fila 15, luego soltar el mouse c. Del menú contextual presentado elegir La opción “Tendencia geométrica” 88 SENATI-Computación e Informática s de datos III 2. Se presentará como resultado el siguiente cuadro Ventas del primer semestre Ventas proyectadas con tendencia geométrica, para el último semestre Tendencia lineal o geométrica de forma manual En una serie lineal, los valores iniciales se aplican al algoritmo de tendencia lineal (y = mx+b) para generar la serie. En una serie geométrica, los valores iniciales se aplican al algoritmo de curva exponencial (y=b*m^x) para generar la serie. En ambos casos, se omite el incremento. La serie creada es equivalente a los valores devueltos por las funciones TENDENCIA o CRECIMIENTO. Paso a Paso: Tendencia lineal o geométrica de forma manual 1. Diseñar el siguiente cuadro a. Arrastre este recuadro pequeño con el botón derecho del mouse, hasta la celda B9. Y suelte el mouse b. Hacer clic sobre la opción Series… SENATI-Computación e Informática 89 Microsoft Office Excel 2007 2. Se presenta el siguiente cuadro de diálogo. Elegir columna, para que la serie se extienda hacia abajo Seleccione tipo de tendencia: Lineal o geométrica Active la casilla Tendencia 3. Hacer clic en el botón para terminar Se genera la tendencia reemplazando a los tres primeros valores e la serie original Agregar una línea de tendencia a un gráfico Las líneas de tendencia se utilizan para el estudio de problemas de predicción, lo que se denomina también análisis de regresión. En un gráfico si tiene los datos de ventas de los primeros meses del año, puede agregar una línea de tendencia al gráfico que muestre la tendencia general de las ventas (creciente, decreciente o uniforme) o que muestre la tendencia prevista para los meses venideros. Media móvil. Se puede crear una media móvil, que suaviza las fluctuaciones en los datos y muestra la trama o tendencia con más claridad. Tipos de gráfico que admiten líneas de tendencias Pueden agregarse líneas de tendencia a las series de datos en los siguientes gráficos: 90 SENATI-Computación e Informática s de datos III • Áreas 2D no apiladas • Barras • Columnas • Líneas • Cotizaciones • Tipo XY (Dispersión), y • Burbujas. No pueden agregarse líneas de tendencia a las series de datos en los gráficos 3D, radiales, circulares, de superficie o de anillos. Si se cambia un gráfico o una serie de datos de modo que ya no permita la línea de tendencia asociada (por ejemplo, si se cambia el tipo de gráfico por un gráfico de áreas 3D o si se cambia la vista de un informe de gráfico dinámico o de un informe de tabla dinámica asociado), se perderán las líneas de tendencia. Paso a Paso: Agregar línea de tendencia a un gráfico 1. Diseñar el siguiente cuadro 2. Seleccionar el bloque de de celdas A2:B7 a. Hacer clic en botón c. Seleccionar las celdas A2:B7 b. Hacer clic en la ficha Insertar d. Elegir este tipo de gráfico SENATI-Computación e Informática 91 Microsoft Office Excel 2007 3. Se muestra el siguiente gráfico Tasa de inflación Perú 4 3 2 1 0 2003 2004 2005 2006 2007 4. Para agregar la línea de tendencia, realice las siguientes operaciones a. Hacer un clic sobre el gráfico para seleccionarlo. b. Hacer clic sobre la ficha presentación c. Hacer clic sobre el botón , , Mas opciones de línea de tendencia d. Se presenta el siguiente cuadro de diálogo e. Elegir Lineal f. Activar la casilla “Presentar ecuación en el gráfico” 92 SENATI-Computación e Informática s de datos III 5. Se muestra el siguiente gráfico 4 2 y = -0.27x + 3.09 0 2003 2004 2005 2006 2007 Tasa de inflación Perú Lineal (Tasa de inflación Perú) Si se reemplaza “x” en la ecuación por el número del año siguiente se obtienen los pronósticos de la inflación de los próximos años (2008, 2009, etc.) Proyectar valores Función PRONÓSTICO Calcula un valor futuro utilizando los valores existentes. El valor previsto es un valor del eje Y para un valor del eje X dado. Los valores conocidos son valores de x e y existentes, y el nuevo valor se calcula utilizando una regresión lineal. Esta función se puede utilizar para prever las ventas futuras, las necesidades de inventario y las tendencias de los consumidores. Sintaxis PRONOSTICO(x;conocido_y;conocido_x) Argumentos X Es el punto de datos cuyo valor se desea predecir. Conocido_y Es la matriz o rango de datos dependientes. Conocido_x Es la matriz o rango de datos independientes. La ecuación de la función pronóstico es a + bx, donde: y Y donde x e y son las medias de muestra PROMEDIO(conocido_x) y PROMEDIO (conocido y). SENATI-Computación e Informática 93 MicrosoftOffice Excel 2007 Paso a Paso: Uso de la función Pronóstico 1. Diseñar el siguiente cuadro 94 SENATI-Computación e Informática s de datos III Función TENDENCIA Devuelve valores que resultan de una tendencia lineal. Ajusta una recta (calculada con el método de mínimos cuadrados) a los valores de las matrices definidas por los argumentos conocido_y y conocido_x. Devuelve, a lo largo de esa recta, los valores y correspondientes a la matriz definida por el argumento nueva_matriz_x especificado. Sintaxis TENDENCIA(conocido_y;conocido_x;nueva_matriz_x;constante) Argumentos Conocido_y Es el conjunto de valores de y que se conocen en la relación y = mx+b. Conocido_x Es un conjunto opcional de valores x que se conocen en la relación y = mx+b. Nueva_matriz_x Son los nuevos valores de x para los cuales desea que TENDENCIA devuelva los valores de y correspondientes Paso a Paso: Uso de la función Tendencia 1. Diseñar el siguiente cuadro SENATI-Computación e Informática 95 Microsoft Office Excel 2007 Uso de cuadros de Histogramas para el cálculo de frecuencias individuales y acumulativas El gráfico de la distribución de frecuencias, se llama histograma. El histograma de frecuencias es una representación visual de los datos en donde se evidencian fundamentalmente tres características: forma, acumulación o tendencia posicional y dispersión o variabilidad. El histograma (de frecuencias) en si es una sucesión de rectángulos construidos sobre un sistema de coordenadas de la siguiente manera: 1. Las bases de los rectángulos se localizan en el eje horizontal. La longitud de la base es igual al ancho del intervalo. 2. Las alturas de los rectángulos se registran sobre el eje vertical y corresponden a las frecuencias de los intervalos. 3. Las áreas de los rectángulos son proporcionales a las frecuencias de las clases. Para que usar los histogramas 1. Los histogramas de frecuencia son una herramienta útil cuando hay que analizar una gran cantidad de datos. a. Para mostrar en forma de gráficos de barras las características de un producto o servicio: - Tipos de defectos - Problemas - Riesgos de seguridad, etc. 2. Un histograma toma datos de mediciones a. Temperatura, presiones, alturas, pesos, etc. b. Muestra su distribución. 3. Un histograma revela la cantidad de variación propia de un proceso. Datos necesarios para construir un histograma en Excel Datos de Entrada. Estos son los datos que desea analizar mediante la herramienta Histograma. Números de clase. Estos números representan los intervalos que desea que utilice la herramienta Histograma para medir los datos de entrada en el análisis de datos. Paso a Paso: Construcción de un histograma 1. Diseñar el siguiente cuadro 96 SENATI-Computación e Informática s de datos III , , 2. Hacer clic en la ficha se presenta el siguiente cuadro de diálogo. 3. Hacer clic sobre la opción “Histograma” 4. Hacer clic en el botón Aceptar. Se presenta el siguiente cuadro de diálogo. Seleccionar rango de entrada y rango de clase según se muestra en la gráfica. Como opciones de salida hacer clic en la opción “En una hoja nueva”. Hacer clic en “Crear gráfico”. SENATI-Computación e Informática 97 , Programa N Microsoft Office Excel 2007 5. Hacer clic en el botón se agregará una nueva hoja con el cuadro de clases, frecuencias y la gráfica del histograma. Interpretación de un Histograma Se trata de identificar y clasificar la pauta de variación del conjunto de datos estudiado, que relacione la variación con el proceso o fenómeno en estudio. El resultado de este análisis es una teoría sobre el funcionamiento del proceso o sobre la causa del problema que se está investigando. A continuación se presentan pautas de variación típicas: a. Distribución en forma de campana Es la distribución normal. La desviación respecto a esta forma puede indicar la existencia de problemas externas al proceso. La forma de campana no asegura, por sí misma y sin analizar su valor medio y el recorrido de los datos, que el proceso funcione de forma satisfactoria. Pico b. Distribución con doble campana o con doble pico Representa generalmente la combinación de dos distribuciones y sugiere la presencia de dos procesos distintos. s de datos III c. Distribución plana Representa un caso típico de departamentos que no tienen el trabajo bien definido y cada cual lo hace "a su manera". Varias distribuciones en campana con sus centros distribuidos uniformemente a lo largo del recorrido de los datos. d. Distribución en peine Esta pauta de variación es típica de errores de medición, errores en la forma de agrupar los datos o sesgos sistemáticos de redondeo. Debe revisar los procesos de recogida de datos y construcción del Histograma. Valores altos y bajos se alternan de forma regular e. Distribución con un pico aislado El proceso con el pico pequeño será una anormalidad o deficiencia que no sucede a menudo o regularmente. Estos picos unidos a distribuciones sesgadas o truncadas indican falta de eficacia en la eliminación de elementos defectuosos. Esta forma sugiere la existencia de dos procesos distintos SENATI-Computación e Informática 99 Microsoft Office Excel 2007 f. Distribución con un pico en el extremo Esta forma se presenta cuando la cola de una distribución regular se ha cortado y acumulado en una sola categoría en el extremo del recorrido de los datos. Suele indicar un registro poco cuidadoso o sesgado de los datos. Un pico situado en un extremo de una distribución regular g. Distribución sesgada o truncada Esta distribución es típica de procesos con límites prácticos a un lado del valor nominal o a datos parciales de un proceso (distribuciones con parte de los datos suprimidos). Distribución sesgada Distribución truncada Pico descentrado Descendencia suave de la cola. Descendencia brusca de la cola Posibles problemas y deficiencias de interpretación a. Si los datos utilizados no son adecuados (sesgados, inexactos, anticuados, poco significativos, etc) las conclusiones no reflejarán la situación real. b. Muestra pequeña y poco representativa. Se requiere mínimo cuarenta observaciones para cada uno de los Histogramas que se desee realizar. c. Aceptar las conclusiones del análisis como hechos. La interpretación de un Histograma es una simple teoría y por tanto deberá ser confirmada posteriormente mediante el análisis adicional y la observación de los hechos reales. 100 SENATI-Computación e Informática s de datos III Definición y resolución de problemas con Solver Solver se utiliza cuando queremos encontrar la mejor manera de hacer algo. O dicho de un modo más formal: queremos encontrar los valores de determinadas celdas de una hoja de cálculo que optimicen (aumenten o disminuyan) un determinadoobjetivo. Generalidades sobre Solver Un modelo de optimización consta de tres partes: la celda objetivo, las celdas cambiantes y las restricciones. a. La celda objetivo representa el objetivo como, por ejemplo, aumentar las ganancias mensuales. b. Las celdas cambiantes son las celdas de la hoja de cálculo que podemos cambiar o ajustar para optimizar la celda objetivo como, por ejemplo, la cantidad de cada producto fabricada durante un mes. c. Las restricciones son delimitaciones que se aplican a las celdas cambiantes como, por ejemplo, no usar más recursos que los disponibles y no producir más cantidad de un producto que la que pueda venderse. Carga del programa de complemento Solver 1. Hacer clic en el Botón Microsoft Office , 2. Hacer clic en la opción 3. En el cuadro de diálogo “Administrar”, hacer clic en la opción “Complementos de Excel” y luego hacer clic en el botón “Ir”. 4. En el cuadro de diálogo Complementos disponibles, active la casilla de verificación “Complemento Solver” Hacer clic para carga el complemento “Solver” SENATI-Computación e Informática 101 Microsoft Office Excel 2007 5. Para terminar hacer clic en el botón Paso a Paso: Desarrollo de casos utilizando Solver 1. La empresa “Comida a su puerta” basado en la experiencia del año anterior sabe que por cada sol gastado en publicidad ingresa aproximadamente como promedio 8.75 soles en pedidos. El negocio está sujeto a variaciones estaciónales muy acentuadas. Este año 2009 se tiene proyectado unos pedidos de 300,000 nuevos soles la pregunta es cuanto se debe invertir en publicidad. 2. Diseñar el siguiente cuadro se considera 20,000 en inversión en publicidad distribuido entre los 4 trimestres. =B5*$F$2*B4 =SUMA(B5:E5) 3. Hacer clic en la ficha , luego hacer clic en el botón 4. Se presenta el cuadro de diálogo parámetros de Solver Escriba F5. Que define la celda que mostrará el valor en pedidos que se desea alcanzar Grupo de celdas cambiantes Valor de la celda objetivo, Monto que deseamos obtener como pedidos 5. Haga clic en el botón para hallar una solución 102 SENATI-Computación e Informática s de datos III . . Hacer clic para guardar este escenario 6. Hacer clic en el botón para almacenar esta solución 7. Para terminar haga clic en el botón Se presenta la siguiente solución. Paso a Paso: Agregar restricciones a Solver 1. Del ejemplo anterior busque una solución que mantenga el presupuesto publicitario total por debajo de 25,000. 2. Hacer clic en la ficha Luego hacer clic en el botón 3. Se presenta el cuadro de diálogo parámetros de Solver 4. Hacer clic en el botón 5. Defina la siguiente restricción. Presupuesto de publicidad <= 25,000 SENATI-Computación e Informática 103 Microsoft Office Excel 2007 6. Haga clic en el botón . Su cuadro de diálogo debe quedar así. 7. Haga clic en el botón , . Para mostrar la nueva solución con la restricción especificada Cómo configura Solver 1. Hacer clic en la ficha Luego hacer clic en el botón 2. Del cuadro de diálogo presentado Hacer clic en el botón 104 SENATI-Computación e Informática s de datos III Se aplica sólo a los problemas no lineales. Se indica mediante una fracción entre 0 y 1. Cuantos más decimales tenga el número, menor será la convergencia Porcentaje donde la celda objetivo da una solución satisface las restricciones externas. Una tolerancia mayor tiende a acelerar el proceso de solución. Tiempo que tarda el proceso de solución. Tiempo que tarda el proceso de solución. Debe indicarse la precisión mediante una fracción entre 0 (cero) y 1. Cuantas más posiciones decimales tenga el número que se escriba, mayor será la precisión; por ejemplo, 0,0001 indica una precisión mayor que 0,01. Para resolver un problema de optimización lineal. Hace que Solver presuponga un límite de 0 (cero) para todas las celdas ajustables en las que no se haya establecido un límite inferior en el cuadro Restricción Utilizar la escala automática cuando haya grandes diferencias de magnitud entre las entradas y los resultados Utiliza la extrapolación lineal de un vector tangente. Utiliza la extrapolación cuadrática, que puede mejorar en gran medida los resultados de problemas no lineales Progresiva Se utilizan para la mayor parte de los problemas, en los que los valores de restricción cambian relativamente poco. Central Se utiliza en los problemas en que las restricciones cambian rápidamente, en especial cerca de los límites. Newton Utiliza un método quasi-Newton que normalmente necesita más memoria pero menos iteraciones que el método de gradiente conjugada. Conjugado Necesita menos memoria que el método Newton, pero normalmente necesita más iteraciones para alcanzar un nivel de exactitud concreto 3. En este cuadro de diálogo puede especificar sus opciones de cálculo. Modificación de forma de búsqueda de soluciones en Solver 1. Hacer clic en la ficha Luego hacer clic en el botón 2. Del cuadro de diálogo presentado Hacer clic en el botón SENATI-Computación e Informática 105 Microsoft Office Excel 2007 Cambie las opciones de Estimación, Derivadas y Buscar para optimizar su modelo Desarrollo de casos tipo utilizando Solver 1. Diseñar el siguiente cuadro 2. Ajustar los precios de los productos de forma que el precio de venta al público (P.V.P.) (F7) se rebaje a 17000. Se debe tener en cuenta que el precio de cada producto no puede ser superior o inferior a un precio determinado. Grupo de celdas cambiantes Escriba F7. Que define la celda que mostrará el valor en pedidos que se desea alcanzar Valor de la celda objetivo, Monto que deseamos ajustar el precio de venta. 106 SENATI-Computación e Informática s de datos III 3. Hacer clic en la ficha , luego hacer clic en el botón 4. Haga clic en el botón para hallar una solución 5. Para terminar haga clic en el botón Se presenta la siguiente primera solución sin aplicar restricciones. 6. Ahora aplicar a los precios de los productos las siguientes restricciones. B4 > 900 and B4 <1250 B5 > 1300 and B5 <1500 B6 > 1600 and B6 <1850 7. Hacer clic en la ficha , luego hacer clic en el botón 8. Se presenta el cuadro de diálogo parámetros de Solver 9. Hacer clic en el botón se presenta el cuadro de diálogo parámetros de Solver. Como no se puede comprar fracciones de artefactos modificaremos el rango de celdas cambiantes a: B4:B6 Agregar la siguiente lista de restricciones 8. Haga clic en el botón para hallar una solución SENATI-Computación e Informática 107 Microsoft Office Excel 2007 9. Para terminar haga clicen el botón Se presenta la siguiente solución esta vez incluye las restricciones. Cuestionarios 1. Cuál es la utilidad de las herramientas de análisis estadísticos. 2. Cuál es la diferencia entre tendencia lineal y geométrica. 3. Cuál es la utilidad de un Histograma. 4. En que aplicaría Solver. 5. Cuál es la diferencia de las funciones Pronóstico y Tendencia. 108 SENATI-Computación e Informática
Compartir