miércoles, julio 01, 2009

Cálculos frecuencias en Excel usando tablas dinámicas

Un lector me consulta cómo crear un reporte que muestre cuántos de los artículos de un inventario tienen una antigüedad de hasta 3 meses, cuántos entre 3 y 6 meses, cuántos entre 6 y 9 y así sucesivamente.

Hay varias formas de hacer esto, pero cuando tenemos que enfrentarnos con listas de muchos artículos, digamos centenas o miles, la herramienta más eficiente son las tablas dinámicas.

De hecho vamos a mostrar cómo crear una tabla de frecuencias usando tablas dinámicas. EN nuestro caso lo aplicaremos al ejemplo del inventario.

Nuestro inventario se ve así



cálculo de frecuencia en Excel

EL primer paso es agregar un campo (columna) donde calculamos la antigüedad de cada artículo en relación a la fecha corriente. Usamos la función SIFECHA de esta manera


=SIFECHA(C2,HOY(),"m")

cálculo de frecuencia en Excel

Luego construimos una tabla dinámica, preferentemente en una hoja separada.


Ahora arrastramos el campo Antigüedad al área de campos de fila (si, a pesar de que Antigüedad es un campo de datos numéricos)

cálculo de frecuencia en Excel

Luego arrastramos los campos Artículo y Descripción al área de filas.

El campo Artículo lo volvemos a arrastrar pero esta vez al área de datos. Inmediatamente después quitamos los subtotales de todos los campos.

Nuestra tabla dinámica debe verse así

cálculo de frecuencia en Excel

Seleccionamos la celda A4 (Antigüedad) y apretamos el icono Ocultar detalle del asistente de Tablas Dinámicas

cálculo de frecuencia en Excel

El resultado es

cálculo de frecuencia en Excel

Ahora usamos Agrupar para crear los intervalos. En el formulario de agrupar ponemos 3 (o el tamaño de intervalo deseado) en la casilla Por

cálculo de frecuencia en Excel

Volvemos a elegir la celda A4 (Antigüedad) y nuevamente apretamos Ocultar Detalle.

El resultado es una tabla dinámica agrupada por intervalos de 3 meses, que totaliza el total de artículos en cada intervalo (0-2 meses, 3-5 meses, etc).

cálculo de frecuencia en Excel

Algunas observaciones:


# - Si queremos que el primer intervalo sea 1-3, deberemos modificar levemente la fórmula que calcula la antigüedad


=SIFECHA(C2,HOY(),"m")+1


Una vez modificada la fórmula, actualizamos la tabla dinámica

cálculo de frecuencia en Excel

# - La función HOY() que usamos en la fórmula de antigüedad es volátil, lo cual puede influir negatívamente en el tiempo de recálculo en hojas con muchos datos. En lugar de HOY() podemos poner la fecha en relación a la cual queremos calcular la antigüedad en una celda fuera del rango de la lista o en un nombre.



Technorati Tags:

8 comentarios:

  1. Muchas gracias por el recurso me fue muy útil, esta en otra versión de excel pero es completamente vigente, microsoft lo que hace con sus nuevas versiones es un poco de maquillaje.

    Nuevamente muchas gracias

    ResponderBorrar
  2. Hola Jorge,
    una tabla dinamica tiene para calcular: maximo, minimo,media...pero Como podria calcular la moda?.
    Gracias,

    jimasgo2000@yahoo.es

    ResponderBorrar
  3. No creo que se pueda. Lo única que se me ocurre es arrastrar el campo de valores al área de filas y al área de datos. Luego usar la función CUENTA para totalizar los datos. Esto te mostraría cuantas veces aparece cada valor permitiéndote así ver cuál es el más frecuente.

    ResponderBorrar
  4. Hola Jorge

    Hay una curiosidad que me carcome: ¿por qué incluyes artículo y descripcíón en filas para luego ocultar detalles? Yo probé poniendo solamente antigüedad en filas y funciona bien, da el mismo resultado.

    Gracias,
    SL

    ResponderBorrar
  5. Toda tabla de inventario suele incluir el número de código del artículo y la descripción. Estos campos no cumplen ninguna función en el ejercicio que muestro. Pero si quisieras mostrar el detalle de los artículos con un determinada antigüedad, estos campo son necesarios.
    ¿O no entendí la pregunta?

    ResponderBorrar
  6. GRACIAS ME AYUDO MUCHO EN UN TRABAJO Q TENGO

    ResponderBorrar
  7. Muchas gracias por la información. ¿Podría ayudarme con este problema?, cuando selecciono agrupar, se une todo en un solo grupo, no aparece el formulario. Gracias

    ResponderBorrar
  8. Tienes que asegurarte que todos los valores en el campo sean numéricos y que no hayan celdas en blanco.

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.