viernes, 24 de julio de 2009

Iniciación a las Tablas dinámicas.

No podemos dejar pasar la oportunidad de aprender algunos pequeños conceptos de qué es una Tabla dinámica, y cómo podemos trabajar con ella. La Tabla dinámica o 'Pivot table', para los amantes de la versión en inglés, la podemos definir como aquel Informe de referencias cruzadas adaptable totalmente, y constantemente, a las necesidades del usuario. Una tabla dinámica nos permite construir, basándonos en un origen de datos estructurado en forma de tabla (es decir, una línea con cabecera de rótulos, y el resto de filas con información tabulada de registros individuales), un informe elaborado adaptado a nuestra necesidad de análisis.
La pretensión de implantar un Informe de tabla dinámica en nuestras hojas de cálculo es alcanzar un informe final, o intermedio, donde poder comparar subtotales (de cualquier tipo) de los distintos campos que componen nuestro origen de datos. Por tanto, en los informes de tabla dinámica, cada columna o campo de los datos de origen se convierte en un campo de tabla dinámica que resume varias filas de información.
Plantearemos un pequeño ejemplo de cómo y para qué utilizar una Tabla dinámica.
Supongamos un pequeño comerciante que se dedica a vender un único producto, por ejemplo, un 'Manual de Excel para todos los niveles'. Este pequeño comerciante lleva un control de manuales vendidos por cada día, desde el comienzo de su negocio (el 1 de enero de 2008); su registro llega hasta el 28 de febrero de 2008 (para facilitar el ejemplo). Por tanto nuestro pequeño comerciante dispone de un listado con dos campos (Fecha y número de ventas). Para analizar la información tabulada de la que dispone aplicaremos un Informe de Tabla dinámica; para ello ejecutaremos el asisente de Excel para tal efecto.


El primer paso del asistente nos solicita que indiquemos con qué tipo de origen de datos vamos a trabajar:


Para nuestro ejemplo, ya que disponemos del listado del pequeño comerciante en una hoja de cálculo, seleccionaremos 'Lista o base de datos'; al tiempo marcaremos la opción de crear sólo un Informe de tabla dinámica (tendremos más ocasiones de aprender a usar al tiempo los gráficos dinámicos).
En el siguiente paso del asistente definimos el rango de celdas donde está el listado de datos (en un futuro aplicaremos en este momento del asistente lo ya aprendido sobre 'rangos dinámicos'). En nuestro ejercicio marcamos el rango A1:B60.


El tercer paso nos solicita el lugar donde deseamos colocar nuestro informe de tabla dinámica:



EJEMPLO EXCEL


Por comodidad, para su posterior vista, nos decantamos por situarla en la misma Hoja donde se encuentra el origen de datos (el listado de fechas y ventas). Es importante, si nos decidimos por esta opción, asegurarse que tenemos espacio suficiente a la derecha y abajo de la celda destino; ya que la tabla dinámica, una vez creada, tendrá asignado un espacio variable en nuestra hoja de cálculo, dependiendo de las combinaciones que ejecutemos.
Finalizamos el asistente, y vemos la estructura (sin datos) de cómo es un Tabla dinámica:


Vemos que la tabla dinámica se divide en cuatro zonas:
  • Área de datos: principalmente situaremos campos de nuestro listado que sean numéricos (admitiría culquier tipo de campo)

  • Área de filas: podremos incluir los campos necesarios de nuestro listado (sólo una vez cada campo) para obtener distintas formas de agrupación por filas.

  • Área de columnas: podremos incluir los campos necesarios de nuestro listado (sólo una vez cada campo) para obtener distintas formas de agrupación por columnas

  • Área de páginas. este área nos permite realizar, de otra manera, agrupaciones y muestra de información; además de habilitar alguna opción particular.

En nuestro ejemplo, al tener sólo dos campos, y puesto que estamos iniciándonos en el uso de las Tablas dinámicas, terminaremos de construir el Informe. Observamos que al activar la Tabla dinámica se abre una ventana con la Lista de campos de tabla dinámica:


Desde esta ventana, pinchando y arrastrando los campos sobre la zona de la tabla dinámica donde deseemos incorporar el campo(o desplegando el área, en la parte inferior de la ventana, elegida) empezamos a dar forma a nuestro informe. Arrastraremos el campo Fecha al área de filas, y el campo Ventas al área de datos. Y vemos cómo responde el informe a nuestras acciones; podremos mover los campos a las distintas áreas y observar el dinamismo de la estructura creada.
En nuestro ejemplo, tal cual hemos finalizado, no vemos ninguna ventaja, ya que tenemos duplicado un listado. NO ERA EL OBJETIVO de este post trabajar con las posibilidades de la Tabla dinámica, SÓLO APRENDER A CREARLA.
En posteriores entradas de este blog continuaremos viendo las enormes posibilidades de esta herramienta.
Adjuntamos ejercicio.

Tablas dinamicas
Tablas dinamicas.x...
Hosted by eSnips

44 comentarios:

  1. por que no ponen los errores que pueden presentarse???he??

    ResponderEliminar
  2. Estimad@ amig@,
    será un placer intentar resolver cualquier consulta que tenga relacionada sobre alguno de los temas expuestos en este blog; por favor , enumere a qué errores se refiere.
    Las Tablas dinámicas probablemente sea una de las herramientas de Excel que menos errores provoque, por eso, seguro que su colaboración ayudará a otros lectores.
    Un cordial saludo

    ResponderEliminar
  3. que problema hay cuando no se te activa en el excel 2003 la cuestión informe de tablas y gráficos dinámicos,tengo una tabla de datos y cuando voy al menú datos,no está activado dicho enlace.
    Muchas gracias

    ResponderEliminar
  4. Es extraño tu problema, ya que no es necesario habilitar ningún Complemento para trabajar con Tablas dinámicas, a veces en la versión Excel 2003, no se desplegan todos los items de los menús...¿podría ser este tu caso?. Mira si al desplegar el menú Datos al final tienes unas 'flechitas' que te permitirán ver todas las opciones, y entre ellas la de Informe de tablas y gráficos dinámicos...
    Si no es esta la cuestión, de verdad, no se me ocurre cuál puede ser...
    Saludos

    ResponderEliminar
  5. hola tengo un problema con una tabla dinamica de excel 2007
    cuando pongo en la operacion de producto unas cantidades me las pone bien y otras me pone como si fueran valores exponenciales??
    gracias de antemano por su respuesta

    ResponderEliminar
  6. Hola,
    en principio, tal cual expones el problema, parece que la solución pasa por cambiar el formato de número de las celdas en cuestión. Selecciona el campo donde tengas esos valores exponenciales y cambia el formato de número desde la configuración de campo.
    El formato de una tabla dinámica funciona exactamente igual que una celda normal, por tanto, si te aparecen valores en forma exponencial es que trabajas con valores muy altos.
    Saludos y espero haber contestado a tu pregunta

    ResponderEliminar
  7. MMMM MI DUDA ES SABER SI SE PUEDE ACCDER A LOS ARCHIVOS AL FIN DE ENRIQUECER MI CLASE DE INGENIERIA ECONOMICA ATTE ING. RAFAEL MOLINA LÓPEZ

    ResponderEliminar
  8. Hola Rafael,
    no hay ningún inconveniente en que emplees cualquier información de este blog para tus clases de Ingenieria económica, agradeciendote de antemano señales la fuente.
    Respecto al uso de archivos, en casi todos los post de la etiqueta 'Financieras' existe un fichero adjunto, si deseas algún otro, por favor, coméntamelo via email (excelforo@gmail.com).
    Un saludo

    ResponderEliminar
  9. tengo que elaborar una tabla con valores en numero y respuestas si-no que van incluidas en un solo campo, 1 para si 0 para no,como podria organizarla teneidno en cuenta que la base de datos corresponde a una encuesta-gracias

    ResponderEliminar
  10. Hola,
    si he entendido bien, pretendes realizar una tabla con dos campos (Valor y si/no); supongo que el campo valores serán o bien el número de la pregunta o bien una posible puntuación otorgada según la respuesta, y que por tanto no tienes problema alguno con esto. Para el campo Si/No yo emplearía la herramienta Validación, en su opción Lista
    http://excelforo.blogspot.com/search/label/Validacion
    dando los valores Sí, No (o 1, 0) con lo que te sería fácil ir rellenando la encuesta.
    Si tu idea era otra y no lo he comprendido bien, por favor, coméntamelo en excelforo@gmail.com
    Saludos

    ResponderEliminar
  11. hola tengo una base de datos dividida en 4 hojas (tiene 3 millones de filas). puedo hacer una tabla dinámica en el excel con todo unido?
    Muchas gracias
    Cristina

    ResponderEliminar
  12. Hola,
    siempre recuerdo que Excel es una hoja de cálculo y no un Gestor de bases de datos, y menos para tal volumen de registros; si fueran en total hasta un millón te recomendaria la entrada
    http://excelforo.blogspot.com/2009/10/macro-para-agrupar-datos-de-diferentes.html
    por tanto, en tu caso intentaría trabajar en Access primero, y después exportar los datos trabajados a Excel.
    Yo es lo que haría...
    Un saludo

    ResponderEliminar
  13. hola, tengo un problema con las tablas dinamicas. Yo trabao con el excel 2007 y cuando las mando por mail a usuarios de excel 2003 no las pueden manipular, o sea, quedan estáticas. Probe mil formas, primero creando en el 2007 una tabla desde mi consolidado y despues dandole un aspecto dinamico, pero no hay caso. También marque la casilla para que me muestre la vista clasica y tampoco. Siempre me pasa lo mismo, asi que estoy por pedir que me lo saquen y me pongan otra vez el 2003. Les pido que me avisen si hay alguna forma.
    Muchas gracias,
    Saludos,

    le dejo mi mail para que me contesten, floraaguirre@hotmail.com

    ResponderEliminar
  14. Hola,
    efectivamente, para poder trabajar con Tablas dinámicas creadas con Excel 2007, desde versiones anteriores es necesario que las hayas creado en modo compatibilidad, es decir, que la Tabla dinámica se haya creado cuando el fichero de trabajo haya sido guardado en versión excel 97-2003, de este modo no perderás ninguna funcionalidad.
    En resumen, antes de crear tu tabla dinámica, guárdalo como versión excel 97-2003, cierra y vuelve a abrir el fichero, asegúrate que en la barra donde te aparece el nombre del fichero abierto actual entre corchete pone Modo compatibilidad; ahora ya puedes crear tu tabla dinámica sin problema para que desde otras versiones puedan trabajarlas.
    Espero que te sirva de ayuda.

    ResponderEliminar
  15. hola tengo un detallito (por no llamarlo problema) necesito hacer una tabla dinamica con dos opciones ya sea que se alimente de 129 arcchiso que dan un totla de 8 millones de filas o un tabla para anlizar 8 millones de registro alguna idea?

    ResponderEliminar
  16. Hola,
    creo que algún otro lector preguntó por un problema similar al tuyo, y mi respuesta entonces fue la siguiente. Aunque en Excel 2007 se puede trabajar con un número de registro muy alto (hasta un millón), para tal número de registros y archivos te recomiendo sin duda lo hagas con Access u otro gestor de datos. También con estas herramientas podrás crear tus tablas dinámicas y en último caso importarlas a Excel.
    Saludos
    P.D.: Excel es una hoja de cálculo, no un gestor de bases de datos.

    ResponderEliminar
  17. Hola!
    Trabajo con Tablas pivote normalmente, pero me encuentro en Alemania y manejarlas en otro idioma complica las cosas, no encuentro facil las funciones. Quisiera saber si existe una funcion que me permita rellenar los campos de la fila. Es decir, la tabla dinamica siempre agrupa los valores o nombres y muestra el principal, ej:
    Me muestra esto pero quiero esto
    CArlos Hobby Nadar CArlos Hobby Nadar
    edad 25 Carlos Edad 25
    Enrique Hobby leer Enrique Hobby leer
    edad 15 Enrique edad 15
    Altura 1,50 Enrique Altura 1,50

    en mi caso, necesito que me muestre todos los valores asi se repitan, pues luego debo usar esta tabla para filtrar valores y los espacios en blanco no me funcionan. LA base de datos es muy grande para rellenarla manualmente.
    GRACIAS

    ResponderEliminar
  18. la tabla no salio como queria me refiero a esto

    Carlos Hobby nadar
    (Espacio)Edad 25
    Enrique Hobby leer
    (espacio)Edad 15
    (espacio) Altura 1,50

    Yo quiero
    Carlos hobby nadar
    Carlos edad 25
    Enrique hobby leer
    enrique edad 15
    Enrique altura 1,50

    ResponderEliminar
  19. Hola!
    necesitaría ver un ejemplo de la base de datos desde donde construyes la tabla dinámica ¿Podrías enviarlo a excelforo@gamil.com??.
    Quizá la solución pase por no emplear una tabla dinámica??? o en todo caso emplearla como paso intermedio, y una vez creada tal cual la tienes (con los espacios en blanco), a parte, empleando funciones completar un listado de datos con los los valores completados. Usando el SI.
    Si tienes por ejemplo en la columna A el campo de tu tabla dinámica donde te aparecen los Nombres (con los saltos según las agrupaciones), en otra columna aplicaríamos la función empezando en F2
    =SI(A2="";F1;A2)
    y arrastramos hacia abajo tantas celdas como necesitemos...
    De todas formas, mejor envíame un ejemplo y te doy una solución...
    Slds

    ResponderEliminar
  20. Hola buen día !!
    Tengo una BD y de la cual cree una TD, al consultar el número de registros de un campo de datos en vez de mostrasme p.e. sólo los 5 registros de ese campo, lo que hace es mostrame todos los registros correspondientes correspondientes a una lista.
    Como corrijo ésto??

    ResponderEliminar
  21. Discúlpame pero no termino de entender el problema, te agradecería me enviaras un correo a
    excelforo@gmail.com
    con un pequeño ejemplo.
    Slds

    ResponderEliminar
  22. Hola, quisiera que me indicaran como filtro en la tabla dinamica para que me saque el reporte por numero de dias abiertos de un caso. correo: luzmy0279@hotmail.com

    ResponderEliminar
  23. Estimad@ amig@,
    sin más datos de los que aportas mi respuesta es muy limitada.
    Envíame un ejemplo de tu base de datos a
    excelforo@gmail.com
    y te propondré una respuesta lo antes posible.
    Un cordial saludo

    ResponderEliminar
  24. buen dia amig@s tengo una duda manejo tablas pivote exportadas de un servidor,mi error es que cuando quiero actualizar la informacion me envia un mensaje que dice "Initialitation of the data souce failed" que contacte a mi administrador.
    Otro de los errores que tengo es que en otro archivo pivote "Error in the OLE DB provider. An error ocurred while loading the connection dialog box component for prompting" Alguien que pueda orientarme en la solucion, puesto que antes si los podia abrir pero hoy en dia ya no. Saludos esperando orientacion favorable.

    ResponderEliminar
  25. Hola,
    no parece un error de Excel, si no más bien de la conexión ODBC con ese servidor remoto; puede deberse a varias cosas... asegurate en primer lugar que la ruta de acceso no ha cambiado o que sigues manteniendo derechos (de lectura o escritura) sobre esa base de datos; otra opción podría ser, leyendo el segundo error que comentas, que se te hayan retirado los 'drivers' que te dan acceso a la BD externa.
    Quizá tu departamento de sistemas puedan darte una mejor solución. ;-)
    Saludos

    ResponderEliminar
  26. Gracias por su tiempo y pronta respuesta y si mi problema era que los cubos a los cuales accesaba ya no se encuentran disponibles.
    Gracias Saludos..

    ResponderEliminar
  27. Hola buen día: quiero consultar el porque cuando quiero filtrar en una tabla pivote me aparece mensaje de error "excel cannot complete this task with available resources" y despues aparece "microsoft office excel has encountered a problem and needs to close" y se cierra. como puedo dar solucion a este problema. De antemano mil gracias por su pronto atencion.

    ResponderEliminar
  28. Bueno, no soy especialista en errores de la aplicación, pero parece por lo que comentas que tu tabla dinámica es bastante grande, por lo que quizá sea esta la explicación al primero de los mensajes, 'quejándose de falta de memoria'.
    De alguna forma debe generar el segundo de los errores que te echa de la aplicación.
    Te recomiendo reportes los errores a Microsoft, seguro ellos te darán la solución adecuada.
    Slds y siento no poder decirte nada más.

    ResponderEliminar
  29. Hola. Tengo una tabla dinamica que se encuentra en la Hoja4 de mi libro Excel, y cuyo origen de datos se ubica en la Hoja 3 del mismo libro. Cuando envio este archivo por correo. El otro usuario realiza modificaciones en el origen de datos y quiere actualizar la tabla dinamica, pero sale un error que indica que el origen de datos es otro. ¿Hay alguna manera de mantener el origen de datos estático, que no sea guardando el archivo en un servidor de acceso comun? De antemano gracias por su respuesta. Rosa

    ResponderEliminar
  30. Hola Rosa,
    es un poco raro que estando TD y origen de datos en el mismo libro de el error que comentas. Habría que asegurarse que efectivamente la relación es la que comentas.
    Te puedo decir que yo he trabajado enviando ficheros con TD y nunca me dió tal problema.
    En cualquier caso, vigila los vínculos al abrir el Libro...
    Lo siento pero poco más te puedo decir ssin ver el erro exacto.
    Un saludo

    ResponderEliminar
  31. Tengo montada una formula en campo calculado año anterior/24 dias* numero de dia habil que va transcurriendo.Cuando la fui a cambiar ma da error. como la cambio sin que me de error?

    ResponderEliminar
  32. ='venta neta año anterior'/25*14
    esta es la forma como esta formulado el campo calculado. en excel 2003 realizo el cambio cambiando el 25 y el 14 y lo toma, realizo la misma operacion en 2007 y sale error. por favor me dicen que paso debom seguir en el 2007 para que funcione??

    ResponderEliminar
  33. En principio no hay cambios entre las dos versiones de Excel, por lo que debe funcionar exactamente igual.
    Envíame el ejemplo a
    excelforo@gmail.com
    y le echaré un vistazo.
    Slds

    ResponderEliminar
  34. no puedo generar un grafico dinamico interactivo
    apartir de una tabla dinamica. como resultado me da un grafico normal y yo lo requiero con filtris o cajas desplegables en el grafico no requiero el panel de filtro

    ResponderEliminar
  35. Para obtener un gráfico dinámico, con filtros y toda la funcionalidad de la herramienta dinámica, debes Insertar gráfico (el que quieras) desde la TD ya creada; o bien generarlo desde el principio sobre el origen de datos, navegando por el menú Insertar > Tablas > Gráfico dinámico.
    Slds

    ResponderEliminar
  36. Hola,

    Tengo un problema con las graficas dinamicas en Windows Vista.
    En versiones anteriores excel 2003 donde cree los graficos funcionan de maravilla debido a que puedo hacer los filtros, pero cuando abro el mismo archivo en vista, los graficos estan estatiticos.
    Que debo hacer para que las graficas funcionen con los filtros en vista o que comando debo activar para que funcionen correctamente.

    Muchas gracias por su ayuda.

    Saludos.

    ResponderEliminar
  37. Hola,
    hasta donde yo se (hasta ahora me he librado de trabajar con Vista) el sistema operativo no debería influir en tus trabajos con cualquier versión de Excel; otra cosa sería que ahora trabajes con Excel 2007 o Excel 2010 en la que existen algunas incompatibilidades entre versiones.
    Poco más te puedo comentar. Lo siento.
    Saludos

    ResponderEliminar
  38. Hola,

    Llevo varios dias con errores en excel, utilizo excel 2007. Resulta que se me bloquea el libro en el que estoy trabajando, no tengo opción de guardar, ni nada.
    Como se me queda todo bloqueado, voy a administrador de tareas y cierro excel.
    Entónces no guarda los datos nuevos ingresados, dejándome en un estado de nervios irreparableee!

    Gracias de antemano.
    Maria José.

    ResponderEliminar
  39. Hola María José...
    tendría que ver el problema por el que se queda bloqueado (¿quizá te da algún mensaje de error previo??).
    En cualquier caso, siempre existe una remota posibilidad, y es que se quede grabajo el fichero con las últimas modificaciones en la carpeta de temporales. No es seguro que se hayan grabado, pero inténtalo.
    En cualquier caso sería más práctico buscar el problema por el que se bloquea, quiero decir ¿es un problema de instalación o del archivo de trabajo?.
    Poco más puedo decirte. Lo siento.
    Un saludo

    ResponderEliminar
  40. Hola,
    tengo un problemilla, por error en lugar de pulsar el símbolo de la autosuma pulsé el de €, con lo cual, al lado de las unidades se me ha colocado el simbolo del € y ahora no soy capaz de quitarlo. Me podéis ayudar?
    Un saludo

    ResponderEliminar
  41. Hola,
    prueba pulsando Ctrl+1 ('Formato de celdas'), y en la pestaña de 'Número' selecciona la categoría, por ejemplo, 'General'.
    Slds

    ResponderEliminar
  42. Hola como puedo hacer para agrupar y desagrupar columnas cuando la opción no está activada

    ResponderEliminar
    Respuestas
    1. Hola,
      puedes probar con el método abreviado.
      Agrupar: Alt+Mayus+flecha derecha
      Desagrupar: Alt+Mayus+flecha izquierda

      Slds

      Eliminar