martes, 7 de septiembre de 2010

Box Score para medir el rendimiento de los equipos (OEE) con Tablas Dinámicas

Share

Introducción

En el tiempo que me he dedicado a dar consultoría vi la necesidad por parte de las empresas en crear soluciones para agilizar y precisar el cálculo de sus indicadores, pues la mayoría de ellas desarrollan soluciones poco viables, ya que su forma de calcular se realiza de forma no automatizada, los números mostrados se reducen a una cifra que pone en duda su veracidad, la fuente de información no se encuentra al alcance fácilmente, el cálculo de los mismos requiere dedicarle mucho tiempo, y por si fuera poco si le sumamos el factor error humano el resultado al enfocar mejorar sería poco preciso y mínimo en su impacto.

Al ver estas necesidades se me ocurrió una propuesta sencilla y práctica, que ayude mejorar la precisión de los indicadores, que se generen de manera automatizada, y que la inversión de tiempo sea mínima o nula en sus cálculos.

Para los impacientes pueden descargar el archivo de Microsoft Excel gratuitamente aquí:

 

Box Score

    El Box Score se define como un tablero de resultado que proporciona un extracto del desempeño de una cadena de valor. Un Box Score típicamente muestra la actualización semanal del estado financiero y operacional de la cadena. 

    También muestra una evaluación de los efectos operacionales y financieros que proporciona el sistema Lean Manufacturing, y provee una forma sencilla de entender las diferentes maneras de cómo Lean crea valor al ser implementado en una empresa.

    Existen un sinnúmero de aplicaciones y usos para el Box Score. Como realizar revisiones semanales comparativas del estado actual de la cadena vs el estado meta futuro, Toma de decisiones al introducir un nuevo producto, proporciona información de la capacidad de la cadena y el rendimiento de la misma, etc. Generalmente la revisión del Box Score se realiza de manera semanal por el líder de la cadena y su equipo para monitorear la evolución de los métricos y proponer mejora en la cadena.

    La estructura del Box Score se distribuye en tres categorías:
  • Operacional
  • Capacidad
  • Financiera

Operacional

    Los indicadores operativos están relacionados con la productividad de una cadena de valor, dichos indicadores monitorean los resultados de cada evento de mejora que se aplica a un cadena, sirve para evaluar su impacto y calibrar la eficiencia de las actividades de mejoras continuas mediante el diseño de futuras iniciativas de mejora.
  • Unidades por persona
  • Entregas a tiempo
  • Calidad a la primera
  • Tiempo de puerta a puerta
  • Costo unitario
  • Días cuentas por pagar
  • OEE
  • Vueltas de inventario
Capacidad

    Son los indicadores que miden la capacidad productiva de una cadena representada en porcentaje y generalmente se utiliza para conocer el potencial productivo de una cadena de valor.
  • % Productivo
  • % No productivo
  • % Capacidad disponible.
Financiera

    Estos métricos se encargan de visualizar la salud financiera de la cadena de valor; estos engloban todos los costos relacionados con la cadena como lo son los costos, directos e indirecto, fijos y variables, egresos e ingresos, etc. Estos resultan indispensable para medir el rendimiento de los eventos de mejora que se implementan en la cadena, que se retroalimenta en los ahorros generados por la misma. También nos ayuda a obtener un panorama completo para generar metas a futuro y toma de decisiones.
  • Ingresos
  • Costo de Material
  • Costo de conversión
  • Valor del inventario
  • Utilidad bruta de la cadena de valor
  • Retorno de la inversión de la cadena
Podría extenderme en la explicación más detallada de dichos conceptos y el cálculo de los mismos en Excel, pero hoy nos enfocaremos en realizar un Box Score especialmente aplicado al cálculo de OEE (Eficiencia Global de los equipos).

OEE (Eficiencia Global de los equipos)  

 

    Sin duda unos de los indicadores claves para realizar mejoras potencialmente visibles en una cadena es el indicador OEE que su acrónimo lo define como "Eficiencia Global de los Equipos" y sus siglas en ingles significan "Overall Equipment Effectiveness"; la función de este indicador como KPI (indicador clave de desempeño) es la de medir en unidad porcentual la eficiencia productiva de las maquinas o procesos encargados de la transformación de insumos o productos en proceso en producto final, es decir este indicador se encarga de medir la capacidad potencial que tiene la maquia con respecto a la nominal. 

Las maquinas al ser adquiridas contienen recomendaciones del fabricante del buen uso de ella, así como también su capacidad de producción, pero en la practica la capacidad de producción se queda muy por debajo de la capacidad teórica. Esto se debe a diferentes factores que afectan el buen desempeño de la misma, como es la reducción de velocidad, fabricación de productos defectuosos, paros continuos, cambios de utilería, etc., cada uno de estos factores aportan tiempo no productivo que disminuyen el rendimiento de la maquina.

Este indicador es muy útil a la hora de efectuar eventos de mejora enfocados a procesos productivos; algunas de las herramientas que se apoyan en este indicador es TPM (Mantenimiento Productivo Total) y SMED (cambio de utilería en pocos minutos).

El OEE se compone de tres indicadores claves que monitorean tres características que pueden generar desperdicios o valor no agregado a los procesos, estas son:
  • Disponibilidad
  • Eficiencia
  • Calidad
Disponibilidad

La Disponibilidad es el porcentaje del tiempo disponible que aprovecha la máquina para producir, su forma de calcular es la siguiente:

Primero se calcula el tiempo disponible (TD) que es la sustracción de el tiempo disponible total (TDT) – Tiempo no programado (TNP), este es el tiempo disponible que la máquina tiene para producir, después se divide el tiempo operativo (TO) que es el tiempo efectivo en que la maquina estuvo produciendo entre el tiempo disponible

Disponibilidad = TO / TD

Eficiencia

    Todas las máquinas cuenta con un estándar de producción por hora proporcionada por el fabricante del equipo, pero esta cifra no se lleva a cabo en la realidad, pues en ocasiones las maquinas manifiestan una disminución en su velocidad productiva a causa de un mantenimiento inadecuado o nulo.

    La eficiencia es representada de la división del número de piezas reales producidas VS la teórica.

Eficiencia = Producción real / Producción teórica

Calidad

    La calidad es la diferencia del número de piezas buenas producidas (Producción realRechazos) entre el total de piezas producidas.

    Calidad = (Producción realRechazos) / Producción real
 
En la siguiente imagen se representa gráficamente al indicador OEE con sus componentes.

Como crear un Box Score para medir el indicador OEE en Tablas Dinámicas


Para crear el Box Score con tablas dinámicas primero debemos diseñar la base de datos donde se asignará la información con la que trabajaremos, el siguiente paso es crear la tabla dinámica, para ello explicaré el procedimiento tanto en la versión 2007 de Excel como la 2003.

Box Score para OEE con Tablas dinámicas en Excel 2007

    Cuando hablamos de crear una Tabla Pivote o Tabla Dinámica primero debemos definir de donde se extraerá la fuente de información con la que vamos a trabajar, para ello debemos insertar Tabla dinámica (menú >
Insertar > Tabla dinámica) y seleccionar el rango de interés que queremos analizar del menú desplegable "Crear tabla dinámica"; en este caso sería el rango "A$1:$J$113" de la hoja "BD OEE".


    
    Al crear la tabla dinámica, en automático se crea el esqueleto de la Tabla y emerge un menú llamado "Lista de campos de tabla dinámica" la cual está constituida por 5 áreas que son: Lista de selección de campos, "Filtro de informe", "Rótulos de columna", "Rótulos de fila" y "Valores", en dichas áreas realizaremos la labor de organizar nuestra información, es decir, crearemos la estructuración visual de cómo queremos ver desplegada nuestra información.

Como tip recomiendo que la información relacionada con el tiempo (fechas, mes, año, semanas, etc.) se ubique en el área "Rótulo de columna"; la información relacionada con números o datos vinculados con el análisis de un proceso se situé en el área de "Valores" y la información relacionada con la descripción de los datos a analizar ( nombres de indicadores, procesos, productos, descripción, etc.), que en la mayoría de los casos es presentada como texto, se ubique en el área "Rótulos de fila".

Después de este breve paréntesis, la siguiente función es situar los campos en sus áreas correspondientes, esta acción se lleva a cabo mediante de un arrastre, primero seleccionamos el campo a interés y lo arrastramos hacia su área designada. El campo "Fecha" y "Semana" se agrega en el área "Rótulos de columna", el campo "Maquina" se inserta en "Rótulos de fila" , los campos "Tiempo Programando (hrs)", "Tiempo Disponible (hrs) "Producción Teórica", "Producción Real" y "Rechazos" se mueven a el área "Valores", ojo al agregar estos campos se genera en automático un campo llamado "Valores" en el área "Rótulo de columna" este ultimo se deberá arrastrar hacia el campo "Rótulo de fila".


Después de realizar estas acciones la tabla se deberá ver como la siguiente imagen.


    Ya se ve que está tomando forma nuestro Box Score, ahora el siguiente paso es ocultar los campos donde se encuentran las sumas totales tanto en las filas y las columnas, primero ubicamos el cursos en cualquier área de la "Tabla dinámica" para activar el menú de la "Herramientas de tabla dinámica" y seleccionamos el comando "opciones" que se encuentra ubicado en el menú "Herramientas de tabla dinámica > opciones > Nombre de tabla dinámica: > opciones", esta selecciona desplegará un menú llamado "Opciones de tabla dinámica", ahí seleccionamos la pestaña "Totales y filtros" y deseleccionamos las casillas "Mostrar totales generales de las filas" y "Mostrar totales generales de las columnas", de la opción "Totales generales"


Para quitar los totales del campo "Fecha" mantenemos seleccionado el campo "Fecha" en la celda ubicada en la tabla dinámica, después seleccionamos del menú "Herramientas de tabla dinámica" la opción "configuración de campo" (menú > Herramientas de tabla dinámica > opciones > configuración de campo), este comando despliega una ventana llamado "configuración de campo" en este seleccionamos la opción "Ninguno" de la opción "Subtotales"

¨
… para que la tabla se vea de esta manera.


El siguiente paso es la de agrupar las fechas en meses y en años, para ello primero seleccionamos la celda del campo "Fecha" después elegimos la opción "Agrupar selección" del menú "Agrupar" ubicado en "Herramientas de tabla dinámica > opciones > Agrupar", se abrirá una ventana llamada "Agrupar", ahí seleccionamos mes y año, y aceptamos.


Este comando nos ayuda a agrupar cualquier tipo de información numérica o textual, de una manera organizada y nos ahorra la necesidad de agregar otro de campo en la base de datos fuente.

La siguiente imagen representa la forma en que se vería la tabla con el procedimiento que realizamos.


Ahora debemos de diseñar los indicadores del OEE, para ello utilizaremos el comando "Campo calculado…" situado en el menú "Herramientas de tabla dinámica > opciones > Herramientas >Fórmulas > Campo calculado…", que abrirá una ventana llamada "Insertar campo calculado", en ella nos situaremos en el cuadro llamado "Nombre:", ahí escribiremos el nombre de nuestro indicador, que en este caso será "Disponibilidad", consecutivamente nos situaremos en el cuadro "Fórmula:" y escribiremos la fórmula para calcular la Disponibilidad (Tiempo operativo /Tiempo programado); el procedimiento es el siguiente, primero seleccionamos el campo "Tiempo Operativo (hrs)" de la lista "Campos:" y oprimimos el botón Insertar campo para que aparezca el campo seleccionado en el cuadro "Fórmula:", después escribimos el carácter para dividir ("/") e insertamos el campo " Tiempo Disponible (hrs)" , y aceptamos; este mismo procedimiento se realizará de la misma manera con los demás indicadores.


Después de este último paso nuestro Box Score contaría con todos los elementos necesarios para ejercer su análisis, pues ya contaría con todo la información necesaria para medir el OEE, de una manera organizada y sencilla de leer. El Box Score se vería de esta forma.


Este formato de análisis de indicadores como esta, ya se funcional y se puede trabajar con él, pero como lo he recalcado en mis artículos anteriores nunca hay que menospreciar la importancia a la presentación de nuestros desarrollos, pues, es sinónimo de profesionalismo e imagen, así que creo que todavía podemos mejorarlo, que tal si omitimos el texto "suma de" que se encuentra agregado en los nombres de nuestros indicadores, también le podemos cambiar el formato numérico de nuestros datos por uno más apropiado, quizás agregarle un formato condicional de conjunto de iconos para conocer la salud de nuestros indicadores, y también podemos agregarle algo de color, para que se vea de esta manera.


Con esto concluimos el desarrollo del Box Score automatizado para monitorear el rendimiento de los equipos (OEE).

Puedes descargar aquí el libro de Microsoft Excel gratuitamente:

 
Box Score para OEE con Tablas dinámicas en Excel 2003

El desarrollo de una tabla dinámica en la versión 2003 de Excel es un poco diferente a la 2007, empecemos por crear la tabla dinámica, para ello primero seleccionamos la hoja destino donde se va a incluir la tabla, en este caso es Box Score, seleccionamos la celda A4 (ubicación de la tabla dinámica) y utilizamos el comando "Informa de tablas y gráficos dinámicos", del menú "Datos > Informa de tablas y gráficos dinámicos"; a continuación se desplegará un ventana, ahí seleccionamos "Lista o base de datos de Microsoft Office Excel" y "Tabla dinámica", oprimimos "siguiente" después otra ventana se despliega preguntando por la ubicación de la base de datos a analizar, seleccionamos el rango A1:J113 de la hoja BD OEE y finalizamos.


Esta acción creará el esqueleto de la tabla pivote, ahí realizaremos un arrastre de campos desde la "Lista de campos de de tabla dinámica" a las áreas de la tabla; el campo "Fecha" y "semana" se colocan en el área de campos de columna, "Maquina" se ubicara en campos de fila, los campos "Tiempo Programando (hrs)", "Tiempo Disponible (hrs) "Producción Teórica", "Producción Real" y "Rechazos" se arrastrará a el área datos.


La constitución de a tabla dinámica se verá de manera similar como el ejemplo presentando en la versión 2007 de Excel.


Después removemos los totales de los campos "Datos" utilizando la opción "Opciones de tabla dinámica…" del menú "Tabla dinámica > Opciones de tabla dinámica…", al seleccionar dicha opción se desplegará una ventana llamada "Opciones de tabla dinámica" en donde deshabilitaremos las opciones "Totales generales de filas" y "Totales generales de filas".


Para deshabilitar los totales del campo "Fecha" seleccionamos la celda donde se encuentra situado este campo,
y empleamos el comando "Configuración de campo…" situado en el menú "Tabla dinámica > Configuración de campo…", se desplegará una ventana con el nombre "Campo de la tabla dinámica" ahí habilitamos la opción "Ninguno" del área subtotales y aceptamos.


    Aquí se muestra la tabla sin Totales en columnas y filas.


Lo siguiente es agrupar la fecha en meses y años, con la ayuda del comando agrupar localizado en el menú "Tabla dinámica > Agrupar y mostrar detalles > Agrupar…"; seleccionamos los campos meses y años y aceptamos.

Como en la versión 2007 a la tabla se le agregaron dos campos más que agrupan al campo "semana".


Casi nuestro Box Score está terminado lo único que falta es desarrollar los indicadores del OEE, el procedimiento de cálculo es similar a la de la versión 2007 comentada recientemente, lo único que cambia es la ubicación del comando, que se encuentra en el menú "Tabla dinámica > Fórmulas > Campo calculado…", ahí realizamos exactamente los mismos pasos explicados anteriormente.


    Nuestro Box Score se vería de esta manera.


    Hasta aquí nuestro Box Score es funcional y se puede trabajar con él, solo faltaría darle algunos retoques para mejorar su presentación.


Con esto concluyo este artículo, esperando con gusto que esta herramienta que presento hoy les sirva como apoyo para la realización de sus proyectos de mejorar en su empresa.

Puedes descargar aquí el libro de Microsoft Excel gratuitamente:

 
Recuerden visitar el Blog Máster Excel, ahí encontrarán interesantes herramientas de análisis para profesionistas.

[Foro en Facebook

¿Qué sigue?

    En el siguiente artículo hablaré sobre el desarrollo de un gráfico de Pareto con multiniveles usando gráficos dinámicos, también explicaré el poder y el impacto que puede proporcionar esta herramienta a la hora de tomar decisiones de mejoras en procesos productivos.

Saludos, kind regards.

miércoles, 18 de agosto de 2010

Pareto

Share


 
Introducción
 
Antes de empezar la explicación de cómo desarrollar un gráfico de Pareto en Microsoft Excel, creo que es conveniente conocer un poco de la historia de este gráfico y la potente contribución que dio tanto este mismo como su progenitor.

 
Para los impacientes pueden descargar el archivo de Microsoft Excel gratuitamente aquí:

 
Vilfredo Pareto

 
    Vilfredo Federico Damaso Pareto (15 Julio 1848 – 19 Agosto 1923) también conocido como Wilfried Fritz Pareto fue un Economista, sociólogo y filosofo muy importante en su época. Nación en parís, Francia, descendiente de una familia italiana de marqueses que fue desterrada de su país, aunque más tarde regresaría a Italia en 1858, por lo cual a Pareto se le refiere como Italiano. Sus estudios los realizo en el Instituto politécnico de Turín especializándose en Ferrocarriles y metalurgia, obteniendo un grado en ciencias matemáticas y un doctorado en 1870. Gracias a sus hábitos de estudio y su pasión a la lectura le dieron una enorme erudición que se ve reflejada en su obra maestra de economía. Pareto realizo varias aportaciones al mundo de la economía, unas de ella fue el concepto "optimo de pareto" y el "principio de pareto" (concepto pilar de la construcción del gráfico de Pareto), que fue presentada en una fórmula que explica que la distribución de ingresos es desigual, siendo basada su teoría en la desproporcional distribución de las clases sociales, la cual solo unos cuantos deciden lo que afectará a la mayoría, en resumen el concepto es: "Pocos deciden la suerte de muchos" (pocos vitales de muchos triviales). 

Su polémica teoría al ser disertada y publicada en artículos, le causo grades problemas por parte del gobierno.
En 1893 le designaron una ponencia como profesor de economía política en la Universidad de Laussana (Suiza), donde permaneció por el resto de su vida.

Principio de Pareto

    El principio de Parteo (también conocido como regla del 80-20,     ley de los pocos vitales, principio del factor de escases), recibió el nombre en honor a su autor Vilfredo Pareto.

    Pareto enuncio dicho principio basándose en el conocimiento empírico, el observo que su sociedad se dividía naturalmente en 2 clases, los "pocos de muchos" y los "muchos de pocos" (pocos vitales de muchos triviales), de esa manera se establecían 2 tipos de grupos (80-20), en el cual el grupo minoritario conformado por el 20% de la población acaparaba el 80% de algo, e inversamente proporcional el grupo de mayor volumen, el 80%, ostentaba el 20% de ese algo mismo.

    Las cifras dictadas por la regla 80-20 son arbitrarias, no son exactas y pueden variar, su aplicación es basada en un fenómeno, y como tal solo pueden existir aproximaciones a la regla, siendo adaptable a cada caso particular.

 
Aplicaciones

    La sociedad quedo fascinada sobre esta controvertida noticia y la búsqueda de aplicaciones de este concepto no se hizo esperar; se realizaron diferentes experimentos en situaciones de diferente índole, cuyos resultados revelaron que este fenómeno no solo existe en las masas sociales, y es aplicable a una infinidad de casos diferentes.

Actualmente existe una cuantiosa gama de aplicaciones en donde destaca: el comercio, la logística, ingeniería de software, control de calidad etc. "Joseph Duran" adopto esta herramienta como una de las 7 herramientas básicas, siendo la herramienta de uso clave para el "Control Total de la Calidad" y "Seis sigma".

 
Gráfica de Pareto

    Este gráfico contiene 2 gráficos traslapados, barras y línea. Las barras representa la frecuencia reincidente de variables que afectan un fenómeno, objeto o cosa en un lapso de tiempo establecido, se agrupan de forma descendiente, se pueden representar como costo o alguna otra unidad de medida, la gráfica de línea representa el porcentaje acumulado del total de ocurrencias de las variables y se gráfica de forma decreciente como una función cóncava.

 
Como crear un gráfico de Pareto en Excel

    Para crear un gráfico de Pareto primero debemos de definir qué problema o información queremos analizar y graficar (servicios, problemas en procesos productivos, gastos excesivos, quejas de clientes, etc.), conocer la fuente de información donde se sacaran los datos de interés (lugar de los hechos), definir el intervalo de tiempo de los datos recolectados a graficar (hora, día, semana, mes, año, etc.), clasificar los datos por categorías (tipo de servicios, tipo de procesos, tipo de defectos, lugar, región, empleados, método, etc.), diseñar una hoja de conteo de datos por categorías y con totales, diseñar una base de datos donde se almacenará dicha información.

    Después de haber definido, desarrollado (en el caso de hoja de conteo y la base de datos) y obtenido la información, proseguiremos a crear la tabla para el diagrama de Pareto.

    La tabla deberá tener los siguientes campos:
  • Categoría: Es el nombre de las variables que afectan dicho problema, que se quiere analizar.
  • Frecuencia: Es el total de reincidencias de las variables en un intervalo de tiempo.
  • Frecuencia acumulada: Es la suma interpolada de las frecuencias de cada categoría que se encuentra organizada de forma descendiente por su valor.
  • Porcentaje de la frecuencia: Es
    porcentaje de participación de cada categoría.
  • Porcentaje acumulado: Es la suma interpolada de los porcentajes de cada categoría. 

Procedimiento para crear un gráfico de Pareto.


    Me gustaría explicar la creación de este gráfico, relatándolo con un ejemplo.

La compañía ACME es la empresa líder en el desarrollo de productos de ensueño, todo tipo de productos que se creían ser imposibles de manufacturar, ellos los hacen realidad, desde lo más simple hasta lo imposible, desde comunes molda dientes, hasta píldoras para terremoto, todos y cada uno de ellos son desarrollados de una manera precisa y minuciosa, tal y como sus clientes los han divisado. Sin embargo ACME ha detectado significativos puntos fluctuantes en sus costos productivos, síndrome de un heterogéneo cuidado en la fabricación de sus productos, ellos están interesados en realizar un análisis en sus ventas para conocer su producto estrella y así enfocar sus mejoras productivas en ella, con el fin de disminuir sus costos operativos y amplificar la fidelidad de sus clientes. 

ACME buscó varias maneras de realizar dichoso análisis pero para colmo de su desdicha todos sus intentos resultaron ambiguos; ellos al ver este abrumado y desolado escenario, acudieron a"Excel4Lean" para que los asesorará e instruyera en la resolución de su problema, Excel4Lean analizo su situación y les propuso realizar una gráfico de Pareto en "Microsoft Excel" para analizar el porcentaje de participación de cada uno de sus productos en el mercado y así encontrar el producto estrella, que es el que genera aproximadamente el 80% de sus ganancias.

    La lección fue la siguiente, primero se deberá realizar un catálogo de los productos de interés a analizar.

Catálogo de productos ACME.


    Ya obtenido el catálogo se deberá contabilizar las ventas obtenidas por cada producto durante los últimos 4 meses, para después crear la "tabla de Pareto".

Creación de la tabla de Pareto.

En la creación de la tabla de Pareto se deben de incluir los campo, Categoría, Frecuencia, Cantidad acumulada, Porcentaje acumulado, después se deberá organizar de forma descendiente las cantidades del campo Frecuencia, junto con su total.
 

Para sacar la cantidad acumulada se direcciona el valor de la primera celda del campo "Frecuencia" a la primera celda del campo "Cantidad Acumulada", después nos ubicamos en la segunda celda del campo "Cantidad Acumulada", agregamos una fórmula que sume la primera celda "Cantidad Acumulada" mas la celda de la misma fila que se encuentra en el campo "Frecuencia", por ultimo realizamos un arrastre de formulas con Ctrl+j (Ctrl+d vs EN) seleccionando el rango desde la segunda celda del campo "Cantidad Acumulada" hasta la última celda del mismo campo, ubicado en la fila del último registro del campo "Frecuencia" consecutivamente realizamos el proceso de arrastrar formulas.


El procedimiento para calcular la cantidad acumulada es el siguiente: 

Nos ubicamos en la primera celda del campo "Porcentaje Acumulado" e insertamos la formula dividiendo el primero valor del campo "Cantidad Acumulada" entre el total de la frecuencia (debemos tomar en cuenta en fijar la celda del Total con F4), después realizamos un arrastre de formulas seleccionado el rango desde la primera celda del campo "Porcentaje Acumulado" hasta la última celda, y arrastramos.


Con esto concluimos nuestra Tabla de Pareto y estamos hábiles para crear nuestra gráfica.


Gráfica de Pareto en Excel

    Para dar una explicación más completa sobre el diseño de este gráfico, explicaré como se crea este gráfico en la versión 2007 y 2003 de Excel.

Gráfico de Pareto en Excel 2007

    El primer paso es seleccionar los campos ¨Categorías", "Frecuencia" y "Porcentaje Acumulado" junto con sus datos, si se darán cuenta no se menciono el campo "Cantidad Acumulada" y es porque no se necesita para graficar el Pareto, pero el problema es que los campos interesados en seleccionar se encuentran separados por el campo de "Cantidad Acumulada", para solventar dicho problema primero seleccionamos el rango desde categoría hasta frecuencia (A1:B17), a continuación mantenemos oprimido el botón "Ctrl" (permite seleccionar múltiples celdas que se encuentra separadas) y seleccionamos con el cursor el rango desde el encabezado del campo "Porcentaje Acumulado" hasta su último registro del campo "Frecuencia" (D1:D17).


Después insertamos el gráfico "Columna agrupada" (del menú: "Insertar > Columna > Columna agrupada")…


… al parecer la gráfica necesita algunos ajustes, como añadir el eje para visualizar el porcentaje acumulado; para añadir el segundo eje, primero seleccionamos la grafica para que se active las "Herramientas de gráficos" y
seleccionamos la serie "Porcentaje Acumulado", ubicado en el menú "Formato > Selección actual > Elementos de gráfico", desplegamos la lista y seleccionamos Serie "Porcentaje Acumulado", …


… en la misma sección del menú activamos la opción "Aplicar formato a la selección"; al desplegarse el menú de formato de serie, seleccionamos la pestaña "Opciones de serie", activamos la casilla "Eje secundario" y cerramos,…


… al cerrar el menú, la gráfica de la serie "Porcentaje Acumulado" se desplegaría de esta forma:


Ya podemos empezar a ver que nuestra gráfica está tomando forma, ahora la siguiente acción es cambiar la gráfica de barras de la serie "Porcentaje Acumulado" por la de línea, y para eso sin perder la selección de esta serie, seleccionamos la grafica "Líneas" ubicada en el menú "Insertar > Línea > Líneas", para que el gráfico se muestre de esta manera.


Por fin hemos creado el tan esperado gráfico de Pareto, de seguro que los directivos de ACME han de estar ansiosos por conocer los resultados, pero creo que le falta algo más a ese gráfico. En mi artículo anterior hice hincapié en un tema muy importante, esa es la estética; en cualquier presentación que realicemos nunca debemos menospreciar el toque estético, pues es la imagen y reflejo de ti mismo que induces a tu público y audiencia, y la credibilidad que trasmites, así que siento que todavía podemos mejorar esa gráfica, igual podemos aumentar el ancho de las barras, ubicar los encabezado arriba, y poner algún color agradable, para que la gráfica se vea de esta manera:


Qué gran diferencia ¿no?, ahora si podemos presumir que hemos creado un gráfico perfectamente funcional y estético.

 
Puedes descargar aquí el libro de Microsoft Excel gratuitamente:

 
Gráfico de Pareto en Excel 2003

    Así como el primer paso en el caso de la versión 2007, seleccionamos los rangos comprendidos por los campos ¨Categorías", "Frecuencia" y "Porcentaje Acumulado" (A1:B17 y D1:D17), junto con sus encabezados, después insertamos el gráfico "Líneas y columnas 2" ubicada en la pestaña "Tipos personalizados" del menú "Asistente para gráficos" ("Insertar > Gráficos…")…


… y oprimimos el botón finalizar .


Después le damos unos toques estéticos para que se vea de la misma manera como en el ejemplo presentado en la versión 2007.

Puedes descargar aquí el libro de Microsoft Excel gratuitamente:

 
Caso ACME

Regresando con ACME, la compañía quedo sorprendida de ver los números manejados por sus 2 productos estrellas, "Destructor" y "Píldoras para terremotos", dándose la tarea de mejorar los procesos productivos de esos artículos, para así retener el título y el prestigio que los respaldan. ACME agradeció a Excel4Lean por su grata colaboración y apoyo que tuvo a lo largo de esta odisea.

Recuerden que en la aplicación un proyecto de mejora es indispensable atacar las variables que generan 80% de los problemas, y verán que de esta manera su trabajo será sencillo y eficiente.



 
Espero que les haya sido de su agrado este articulo esperándolos de nuevo es este espacio es que para ustedes.


Recuerden visitar mi otro Blog Master Excel, ahí encontrarán interesantes herramientas de análisis para profesionistas.

 
¿Que sigue?

En el siguiente artículo voy a hablar de cómo crear un Box Score para medir el rendimiento de los equipos (OEE) con Tablas Dinámicas.



"Disfruta el compratir conocimineto"

Saludos, Best Regards.

martes, 10 de agosto de 2010

Histograma

Share


Un histograma se podría definir como la representación gráfica de la distribución de una muestra poblacional en forma de barras donde la longitud de cada barra es proporcional a la frecuencia; cada muestra se puede organizar por categorías. Este grafico nos puede ayudar a conocer la tendencia, distribución y comportamientos de una población.
     Tiene una infinidad de aplicaciones; en la rama industrial se utiliza para medir el desempeño de los procesos cuando analizamos alguna variable, entre otros.

Para los impacientes pueden descargar el archivo de Microsoft Excel gratuitamente aquí:


 
Como crear un Histograma en Excel

Para desarrollar un gráfico de histograma primero debemos definir los siguientes conceptos.
Rango (R): Es valor máximo de una muestra menos el valor mínimo de la muestra.
Clase (K): Representa el numero de segmentos que será dividido la diferencia de los valores límites, máximos y mínimos que albergan en la muestra. K = sqr ( N ).
Unidad de medición (U)
Ancho: Es el acho del intervalo de cada clase. Ancho = ( R + U ) / K.
Frontera inferior (K0): Es el primer intercalo de la clase. K0 = "valor mínimo de la muestra" – U/2.
Frontera superior (Ki): Es el último intervalo de la clase.
Ki = K0 + Ancho de clase.

 
Procedimiento para crear un histograma.

Los siguientes datos representa una muestra de la medición longitudinal de 120 varillas, cada dato tiene una pequeña variación milimétrica y nos interesa conocer la distribución de las muestras representadas en un histograma.


Valor máximo y mínimo.

Para sacar el valor máximo y mínimo de las muestras utilizaremos las funciones: =max() y =min().
En cada formula agregaremos el rango donde se encuentran las 120 muestras para extraer los valores máximos y mínimos.
=MAX($B$2:$K$13) y =MIN($B$2:$K$13).


Rango (R).

El rango es la resta de el valor máximo(74.03) menos el mínimo(73.967), para sacarlo creamos una formula y seleccionamos la celda donde se encuentra el valor máximo y lo restamos con la celda del valor mínimo de la muestra.
=M2-M4


Clase (K).

La clase la podemos sacar con la formula "raíz cuadrada de N" pero yo sugeriría que manipuláramos el valor de "K" introduciendo el numero directamente con el fin poder ajustar automáticamente el numero de clases según nuestras necesidades; para este ejemplo yo sugiero que "K" sea igual a 19.


Unidad de medición (U).

 Como "U" es un constante y normalmente se utiliza para modificar el valor de la" Frontera inferior (K0)"; y crear un pequeño desfase de los intervalos considerando que el valor base de "U" es cero, podemos introducir el valor "U" directamente y acoplarlo a nuestras necesidades.
Recomiendo utilizar valores no mayores al "Ancho de intervalos" ya que de hacer lo contrario estropearía la resolución creada por la cantidad de clases (K).

Ancho de clase. 
 
Al igual que el rango nos basaremos en la fórmula para sacar el "Ancho de clase" , en una celda aparte creamos una fórmula que sume la celda donde se encuentra el rango, la sumamos con la celda de la unidad de medición y el resultado lo dividimos entre la clase.
=(M9+$M$13)/M11


Frontera inferior.

Antes de crear la frontera inferior debemos crear una tabla donde vamos a introducir el valor de nuestras clases, intervalos y frecuencias.


Ya terminada la tabla vamos a ubicar la fórmula para sacar la "frontera inferior" en la primer celda del campo "Intervalo", ahí introducimos la formula y seleccionamos la celda donde se encuentra el "valor mínimo de los datos" y lo restamos con el resultado de la "unidad de medición" entre dos.
=$M$4-($M$10/2)


Intervalos.

Al obtener la "Frontera inferior" las consecutivas celdas del campo "Intervalo" se le sumará la celda superior mas el "Ancho de clase" hasta llegar a la ultima celda indicada por el numero de la "clase".

El procedimiento de llenado es el siguiente, seleccionamos la celda consecutiva a la celda donde se guardo el valor de la "Frontera inferior" e insertamos una fórmula que seleccione la celda donde se encuentra el valor de la "Frontera inferior" (primer valor del intervalo) y lo sumamos con la celda donde se encuentra el valor del "Ancho de clase" fijando la dirección de la celda del "Ancho de clase" con el botón "F4".


A continuación seleccionamos la segunda celda donde empieza el campo "Intervalo" (celda P2) hasta la última celda del mismo campo (celda P22), después realizamos un arrastre de formulas oprimiendo las teclas "Ctrl + J" , (en versión en ingles se utiliza las teclas "Ctrl + D"), para llenar las demás celdas del campo "Intervalo".


Frecuencia.

Existe dos técnicas para sacar la frecuencia, pero en este articulo explicaré solo una, que es la más sencilla. Vamos a utilizar una función llamada "FRECUENCIA(Datos,Grupos)" en la versión en ingles se llama "FREQUENCY(Data_Array,Bins_array)".

La función frecuencia necesita 2 argumentos que son los datos y los grupos.

Datos: Es una matriz donde se encuentra los valores de la muestra, cuya frecuencia se desea contar.

Grupos: Es una matriz donde se encuentran un conjunto de intervalos de la muestra que se desea agrupar.

El procedimiento es el siguiente:

Primero seleccionamos el rango donde se almacenará los datos de la frecuencia, que se encuentra en el campo "Frecuencia", después nos ubicamos con la tecla Tab (tabulador) hasta la primera celda del campo "Frecuencia".


Después seleccionamos la "barra de fórmulas" sin perder la selección de los rangos.


En la "barra de fórmulas" escribimos la función "FRECUENCIA(Datos,Grupos)" o "FREQUENCY(Data_Array,Bins_array)"; en el argumento "Datos" seleccionamos el rango donde se encuentra los datos de la muestra ($B$2:$K$13) y fijamos rangos con "F4", en el argumento "Grupos", seleccionamos el rango donde se encuentra los intervalos de la muestra ($P$2:$P$22) fijando rangos con "F4".

 
Y por ultimo oprimimos el juego de teclas Ctrl + Shift + Enter (formula matricial) para sacar la frecuencia de los rangos.


Graficar.

        Por fin hemos llegamos a la sección por el cual se creó este artículo, que es la creación la dichosa grafica de Histograma, para ello daré una explicación de cómo se realiza esta gráfica, en la versión de Excel 2003 y la 2007.

Creación del gráfico de Histograma en la versión Excel 2007.

Primero seleccionamos el campo "Frecuencia" incluyendo el encabezado, seleccionamos del menú el gráfico "Columna agrupada" - "Menú > Insertar > Columna > Columna agrupada" (Gráfico de barras).


La gráfica generada se vería de esta forma:


Si ven ya empieza a tener forma la gráfica como un Histograma, solo falta agregarle al eje Horizontal las leyendas de los intervalos; el paso es el siguiente.

Seleccionamos el eje horizontal de la gráfica que se encuentra abajo del grafico, oprimimos la tecla derecha del ratón y seleccionamos la opción "Seleccionar datos …".


Oprimimos el botón Editar que se encuentra en la sección "Etiquetas del eje horizontal (categorías)" seleccionamos el campo "Intervalo" sin incluir el encabezado y Aceptamos cambios.


Después le podemos poner algunos toques estéticos como aumentar el ancho de las barras, cambiar el formato del eje horizontal a 3 decimales, etc., para que la gráfica se vea de esta manera.



E aquí, por fin terminamos de desarrollar un bonito, estético y funcional Gráfico de Histograma.

Puedes descargar aquí el libro de Microsoft Excel gratuitamente:


Creación del gráfico de Histograma en la versión Excel 2003.

De la misma manera que la versión 2007 seleccionamos el campo "Frecuencia" incluyendo el encabezado e insertamos gráfico "Insertar > Gráfico > Columna" consecutiva mente seleccionamos la pestaña "Serie" en el campo de "Rótulo del eje de categorías (X)" y seleccionamos el campo "Intervalo" si seleccionar el encabezado.


El gráfico que resultante es el mismo que el que generamos en la versión 2007 de Microsoft Excel.

Puedes descargar aquí el libro de Microsoft Excel gratuitamente:


Con esto concluimos este articulo espero que les haya sido de su agrado.


Recuerden visitar mi otro Blog Master Excel, ahí encontrarán interesantes herramientas de análisis para profesionistas.


¿Qué Sigue?
En el siguiente artículo explicaré como crear un "Gráfico de Pareto" con Microsoft Excel.


"Disfruta el compartir conocimiento"

Saludos, best regards.

Comentarios