miércoles, 8 de junio de 2011

Tablas Dinámicas y Campos calculados

Descargar el fichero: TDcampocalculado.xlsx


Las Tablas Dinámicas han supuesto la revolución en Hojas de Cálculo de los últimos años. Permiten generar informes rápidos y flexibles. Si usted llega a conocer bien su funcionamiento puede cambiar radicalmente la gestión de su departamento o unidad de negocio.

En este artículo vamos a crear una Tabla Dinámica partiendo de una base de datos. En la tabla dispondremos de los costes de diferentes departamentos de la empresa para el año 2010 y la previsión para 2011. Crearemos un campo calculado que nos permita observar el incremento de cada departamento en estos años.

La base de datos de partida es sencilla.

Coste por Proyecto y Departamento


En Excel 2007 vamos al menú Insertar y luego Tabla Dinámica. Siguiendo unos sencillos pasos llegamos a crear una tabla dinámica como la que se muestra en la siguiente imagen:



Disponemos de los costes del año 2010 y la previsión para 2011 por cada uno de los departamentos. Los cuatro proyectos se han establecido como filtro de página en la parte superior de la tabla dinámica.

Ahora deseamos disponer de una columna más que nos indique la variación porcentual experimentada por los costes entre los años 2010 y 2011. Este objetivo se podría lograr por varios métodos:

  1. Escribiendo en la celda D5 la fórmula: =C5/B5-1. Esta fórmula nos da el incremento en tanto por uno. Para verlo en porcentaje basta pulsar sobre el icono de porcentaje (%).
  2. Establecer la fórmula anterior pero vinculando sobre las celdas C5 y B5. En este caso veremos que la fórmula utiliza la función IMPORTARDATOSDINAMICOS. Esta forma de trabajar tiene la ventaja de que ésta función apunta a la tabla dinámica y por tanto no perdemos el vínculo dinámico con la base de datos.
  3. Crear un campo calculado. Este es el método que utilizaremos en este artículo.


Creación del campo calculado

En Excel 2007 con el cursor sobre la tabla dinámica veremos arriba una nueva opción denominada:

Herramientas de tabla dinámica

Al pulsar sobre ella se abren un nuevo menú sobre el que pulsaremos sobre Formulas.


La imagen anterior puede diferir de la que usted pueda ver en pantalla, ya que en Excel 2007 la cinta de opciones muestra diferentes iconos, o los muestra más o menos resumidos en función de la resolución de su pantalla y del tamaño de ventana que utilice.

Al pulsar sobre Fórmulas elegimos Campo calculado.


Aparece una ventana denominada Insertar campo calculado en el que crearemos la fórmula:

=’2011′/’2010′-1

La fórmula se crea introduciendo los campos (columnas) de la tabla dinámica. En este caso calculamos el porcentaje de variación por la clásica fórmula:

Valor Final / Valor Inicial -1

Expresión que es igual a la siguiente:

(Valor Final – Valor Inicial) / Valor Inicial

En nuestro caso los costes del año 2010 son los valores iniciales y las previsiones para 2011 son los valores finales.


Esto genera una nueva columna que denominamos Var.% que recoge la variación porcentual de los costes entre los años 2010 y 2011. Inicialmente los valores que nos dan están en tanto por uno y hemos de ser nosotros los que debemos dar formato a esos valores como Porcentaje de dos decimales.


Los campos calculados son muy útiles al trabajar con tablas dinámicas y tienen la ventaja de que no perdemos el vínculo dinámico con la base de datos.

Gráfico Dinámico

Sitúa el cursor sobre la tabla dinámica y pulsa sobre la opción que verás arriba denominada Herramientas de tabla dinámica. Luego pulsa sobre el icono que te permite crear un gráfico dinámico tal y como se muestra en la siguiente imagen.


Elegimos el tipo de gráfico y de forma instantánea dispondremos de un gráfico muy flexible con muchas opciones que podemos modificar.



Ejercicio propuesto

En la Hoja3 disponemos de una base de datos con 200 registros con los siguientes campos: Fecha, Artículo, Facturación y Unidades. Nuestro objetivo es crear una tabla dinámica agrupada por meses y trimestres en la que introducimos un campo calculado que nos proporcione el precio medio de venta en cada mes.

Todos los datos de la base de datos son aleatorios. Así la fecha es un valor aleatorio del primer semestre del año 2011, y se genera con la fórmula:

=ALEATORIO.ENTRE(FECHA(2011;1;1);FECHA(2011;6;30))

Los posibles artículos son cinco y se generan aleatoriamente con la fórmula:

=ELEGIR(ALEATORIO.ENTRE(1;5);”Art1″;”Art2″;”Art3″;”Art4″;”Art5″)

En Excel 2003 y anteriores para que no de error la fórmula ALEATORIO.ENTRE debemos haber activado el complemento de Herramientas para análisis. Esto se puede activar en el menú Herramientas, Complementos.

Agrupando las fechas simultáneamente por meses y por trimestres obtenemos la tabla dinámica que se muestra en la imagen.



Ahora hemos de crear el campo calculado que insertará una nueva columna en la tabla dinámica. Pretendemos calcular el precio medio, por tanto hemos de dividir la facturación entre el número de unidades.


La tabla dinámica que obtenemos ya incorpora el campo calculado Precio medio.


Los resultados numéricos que usted obtenga serán diferentes de los que se muestran en la anterior imagen, esto es debido a que la base de datos trabaja con valores aleatorios.

Podemos ver cómo cambian los valores de la tabla dinámica al actualizarla. Para ello pulse con el botón derecho del ratón sobre la tabla dinámica y elija Actualizar.



Excel 2010

Para crear un campo calculado en Excel 2010 sigue estos pasos:
  1. Partimos de los datos originales
  2. Creamos la tabla dinámica pulsando sobre: Insertar, Tabla dinámica, y diseñamos la tabla según nuestras preferencias
  3. La tabla dinámica ya esta creada. Ahora nos situamos con el cursor dentro de cualquier celda de la tabla dinámica y veremos arriba una pestaña denominada “Herramientas de tabla dinámica”. Esta pestaña tiene dos sub-pestañas denominadas: Opciones y Diseño. Nos situamos en Opciones.
  4. Pinchamos sobre Cálculos y luego sobre Campos, elementos y conjuntos, y finalmente pinchamos sobre Campo calculado.
  5. Luego se siguen los pasos vistos en Excel 2007 ya que el proceso de creación del campo calculado es similar.

32 comentarios:

  1. Solicito de la manera mas atenta la explicacion del ejercicio de campos calculados, donde se agrega el campo de trimestre y se cambia el formato de fecha y se muestra solamente el nombre del mes, por mas qu le busco no he logrado poder pasar este paso, espero contar con su apoyo.

    saludos.


    Atte.

    Ruben Rosales Rocha

    ResponderEliminar
  2. Cordial saludo Ruben, espero estés muy bien.

    Lo que se hace en el ejemplo es hacer clic derecho sobre las fechas y elegir la opción Agrupar del menú contextual, y en cuadro que se muestra, en las casillas de verificación seleccionar tanto trimestre como mes. De esta manera te aparece tanto el trimestre como los meses que pertenecen a cada trimestre.

    Espero haber colaborado. Éxitos!

    ResponderEliminar
  3. pff, me has salvado la vida amigo jaja busque y busque no encontraba nada, un saludo y buen blog

    ResponderEliminar
  4. Buenos días,

    Tengo una tabla con dos columnas un tipo de incidencia(de 4 posibles) y la fecha de la incidencia. Quiero crear una tabla dinámica que agrupe por tipo y fecha y me diga el número de registros.

    Gracias de antemano

    ResponderEliminar
  5. Hola Buenos dias, solicito su amable ayuda, mi problema radica en que tengo una TD en office 2007 de procedimientos (texto enorme) y en base a esto la TD no me muestra el texto completo sino solo una parte de el. alguien podria decirme como hago para que la TD muestre el texto completo?

    desde ya muy agradecido, me reitero a sus ordenes

    ResponderEliminar
  6. Gracias por su acertada explicación, pero que pasa cuando quiero hallar un campo calculado con los datos de una columna que es el resultado del conteo agrupado de la columna.

    ejemplo: (conteo columna dinamica) /8

    He intentado pero no encuentro esa columna en las opciones para agregar el campo resultadio del conteo a la formula.

    Gracias por su ayuda

    ResponderEliminar
  7. Al hacer el ejercicio no se porque el nombre de las columnas es diferente a lo que debería hacer. Me aparece suma de 2010 y suma de 2011. ¿que tendria que hacer para cambiarlo?

    ResponderEliminar
  8. Excelente material, gracias por compartir.
    Mi pregunta es : ¿Se pueden incluir celdas específicas en los campos cálculados de forma que pueda realizar una realcion entre 2 tablas en un campo calculado de excel?
    es decir en campo calculado en donde ponga pueda realizar [campo1 * si(campo1 > 3 , celda2*campo5, celda4*campo5)]

    Agradeceré su ayuda
    Gennerj

    ResponderEliminar
  9. Me importa mucho la matematica ya que creo que es esencial en cualquier trabajo o cualquier aspecto de nuestra vida. Por eso trato de tomar los ejercicios de logaritmos que me dan en la facultad para mejorar en mi performance

    ResponderEliminar
  10. necesito una tabla con 200 registros si me pudieras ayudar te lo agradecería mucho ya que es para usar los filtros en excel:)

    ResponderEliminar
  11. A pesar de seleccionar la columna correspondiente en la tabla dinámica, la opción de elemento calculado sigue deshabitad. ¿Que es lo que sucede?

    ResponderEliminar
  12. Hola, tengo una tabla dinámica y en la columna de "valores" tengo un conteo de un campo llamado ordenes, y otro con la suma de un campo llamado metros, cuando intento hacer el campo calculado, este seria metros/ordenes, pero el resultado que me arroja es la (suma de metros/SUMA de ordenes), cuando lo que necesito es (suma de metros / CONTEO de ordenes), para saber los metros promedio de cada orden.

    Mil gracias por la ayuda

    ResponderEliminar
  13. Mi enhorabuena por tu blog. Haber si se puede hacer esto mediante una TD, que yo creo que no. Quiero sacar un informe de TD aplicando dos filtros a los dos campos que tengo en mi zona de valores (uno a cada uno). De manera que sobre el 1er campo me saque aquellos que cumplan la condición de mayor de 100.000, y sobre el resultado filtrado quiero a su vez que solo saque aquellos que cumplan la condición de mayor de 50 sobre el 2do campo.

    Informe de TD sin hacer ningún filtro:

    PUESTO CAMPO_1 CAMPO_2
    uno 95.000 43
    dos 101.888 70
    tres 106.000 53
    cuatro 108.000 35

    Después de realizar los dos filtros el resultado debería de ser este:

    PUESTO CAMPO_1 CAMPO_2
    dos 101888 70
    tres 106000 53

    Es imposible, seguro, y gracias de antemano por tus comentarios-

    ResponderEliminar
  14. Hola, quisiera hacerle una consulta...
    Se puede establecer un filtro para un subtotal en una tabla dinámica?
    Por ejemplo, si los trimestres tuviesen el subtotal, que se mostrasen solamente los que sean superiores a una cantidad establecida...
    Gracias y un saludo,

    ResponderEliminar
  15. Hola, por favor, necesito ayuda. En mi tabla excel al final de cada columna tengo la fórmula de subtotales, pero cuando realizo el filtro desaparece esa fila donde se debería ver el subtotal de cada columna, nunca me habia pasado y no sé como solucionarlo, Me gustaría que alguien me dijera como hacerlo. Saludos

    ResponderEliminar
  16. Me ha sido muy útil. Seguiré todas tus explicaciones sobre Tablas Dinámicas. Gracias

    ResponderEliminar
  17. Excelente explicación. ¿Sabría responderme qué causa posible puede haber para que la opción "Campo calculado" me aparezca como no seleccionable? (casi todo ese menu en realidad)

    ResponderEliminar
    Respuestas
    1. Hola David.
      En el siguiente post se explica un caso donde no deja elegir los Elementos Calculados de una Tabla dinámica.
      Tablas Dinámicas y Elementos Calculados
      No se exactamente en tu caso cuál es el motivo, pero puede que te de alguna idea. En el caso que se comenta depende de donde tengas puesto el cursor, así te deja seleccionar o no.
      Un saludo.

      Eliminar
  18. Hola, por favor necesito ayuda, tengo en el rotulo de fila; código y descripción pero me los trae en una sola columna necesito que me aparezca en dos columnas.
    a la espera de su ayuda.
    Gracias.

    ResponderEliminar
    Respuestas
    1. Intenta moviendo con el ratón uno de los dos campos a las etiquetas de fila y otro a las etiquetas de columna.
      Un saludo.

      Eliminar
  19. Enhorabuena por la página Don Adolfo!. era responsable dpto. administración de una gran empresa y por causas económicas de la misma hemos pactado mi salida. Ahora tengo tiempo de profundizar más en excel que siempre quedan cosas por aprender... y aquí se explica claramente y con ejemplos. Gracias por permitirnos aprender y disfrutar de forma desinteresada! un abrazo y mucha suerte en todo.

    ResponderEliminar
  20. Buenas tardes.
    He insertado un campo calculado en una tabla dinamica pero hay en una celda donde no me sale valor y tiene las dos columnas que utiliza para el campo calculado con valores.
    ¿Sabes que puede haber pasado?
    Muchas gracias de antemano

    ResponderEliminar
  21. hola, tengo office 2003 estoy creando un grafico dinamico a partir de una tabla, todo se actualiza correctamente el problema es que al actualizarse la tabla se borran mis configuraciones de la grafica, la grafica es una linea-columna de dos ejes y cuando se actualiza queda en grafica de barras al aplicarle el filtro hace lo mismo y tengo que volver a configurarla que puedo hacer para que no me pase esto

    ResponderEliminar
  22. ayuda:
    quier dividir un campo calculado con otro campo normal y no me sale. en uno tengo la cuenta de varios registros y en el otro tengo un resultado en HORAS, lo que deseo es dividir estos dos campos y no se como.
    ejemplo:
    Vi Desv./Horas
    6 23:38
    7 21:45
    5 21:02
    quiero dividir estos dos campos siendo el primero un conteo de un grupo de registros y el otro la suma de varias horas del mismo grupo de registros.

    ResponderEliminar
  23. Alguien me puede orientar por que no se activa la opción de campo calculado, tengo la base, pero no se activa esta función, lo único que aparece activo es herramientas OLAP. Agradezco su orientación

    ResponderEliminar
  24. Alguien me puede orientar por que no se activa la opción de campo calculado, tengo la base, pero no se activa esta función, lo único que aparece activo es herramientas OLAP. Agradezco su orientación

    ResponderEliminar
  25. Ayuda:
    tengo una tabla dinámica y me surge, que tengo una campo llamado modelo y no me muestra solo uno de los modelos si me pueden ayudar a porfavor.

    Desde ya Gracias

    ResponderEliminar
  26. Hola seria este el ejemplo uno me muestra 0 y no el modelo que corresponde

    Tengo una tabla dinámica y me surge, que tengo una campo llamado modelo y no me muestra solo uno de los modelos si me pueden ayudar a por favor.

    Etiquetas de fila
    LUXINTELL
    GWPDLX
    LUX-1750
    XQG70-A812E
    LUX-2100
    0
    DV-70
    XQG70-Q7

    ResponderEliminar
  27. Y como usar condiciones en Campo calculado de a tabla dinámica, en la misma tabla, no en la data de origen

    ResponderEliminar
  28. Buenas tardes
    Cómo puedo dividir una campo calculado como recuento entre un campo calculado como suma al realizar al ejecutar la operación me genera error al dividir en 0 osea #!DIV/0

    ResponderEliminar
  29. Buenas noches, Sr. Aparicio. Quiero preguntarle o plantearle algo. Yo hice una tabla dinámica a partir de una base de datos en Excel, en una misma hoja Excel, considerando la información que deseo analizar según lo establecido en los campos de valor y campos de fila, en esa tabla dinámica. El punto es que si borro todos los datos iniciales de la base de datos, entonces en la tabla dinámica persisten los resultados que inicialmente estaban antes. Porqué eso, si yo solo lo que quiero es que con nuevos datos la tabla dinámica me dé nuevos resultados? Será que me podría ayudar, usted? Reciba mis gracias anticipadas!

    ResponderEliminar
  30. Buenos Dias, Mi problema es que en una tabla tengo 4 campos, 2 de ellos con importes y las otras 2 con cantidades. Cuando quiero agregar 2 campos calculados con los importes promedios, uno funciona bien, pero el otro (similar fórmula) me da como resultado 0 (cero) No en cuentro el problema. Desde ya Muchas gracias por su ayuda

    ResponderEliminar