Tablas dinámicas de Excel: técnicas avanzadas de tablas dinámicas para analizar y presentar los datos con mayor rapidez

1. Introducción: Cuando empecé mi carrera como analista financiero y aprendí a usar tablas pivote, me dio una manera realmente poderosa de explorar y analizar mis datos para encontrar tendencias que luego podría reportar nuevo a mi equipo y ejecutivo gestión para diferentes reuniones, informes, y cuadros de mando. Soy Al Chen, Arquitecto de Soluciones de Coda. La clase de hoy se va a tratar de usar tablas pivote con técnicas avanzadas para refrescar, para ordenar y filtrar, y para agregar campos calculados a tablas pivote. Sigo usando tablas pivote después de 10 años más de ser un usuario potente de Excel porque estoy constantemente frente a conjuntos de datos realmente grandes. Necesito una forma rápida de resumir los datos en informes y cuadros de mando. Eso es lo que las tablas pivote, una característica realmente clásica en Excel, me permite hacer. Aprenderemos algunas técnicas avanzadas de tabla dinámica en esta clase, como agregar campos calculados a tus tablas pivote para hacer cálculos rápidos sobre la marcha. También aprenderemos cómo puedes agregar fórmulas a tus archivos de Excel usando la fórmula GETPIVOTDATA para sacar datos de tus tablas dinámicas. También discutiremos cómo puedes extender tus tablas pivote usando gráficos pivote, usando segmentadores, y hacer que tus tablas pivote sean más interactivas. Además, cómo puedes refrescar tus tablas dinámicas usando macros y botones en Excel. Esta clase se trata de cómo puedes agregar estas técnicas avanzadas a tus tablas pivote para que tus tablas pivote sean más interactivas, pero también para ayudarte como analista de datos a explorar las tendencias y los conocimientos sobre tus datos. Después de tomar esta clase, espero que participes en el proyecto de clase, que implica mirar un gran conjunto de datos de desgaste de los empleados. Tu objetivo es tomar estos datos, resumirlos en estos paneles realmente fáciles de usar para tu audiencia final, y hacer que los datos sean mucho más interactivos y perspicaces para tu audiencia final. Espero que agregues tu proyecto a la galería de proyectos de clase. Tengo muchas ganas de ver tus preguntas, comentarios, y lo que es más importante, las ideas que recojas al hacer este proyecto. 2. Crear y configurar tablas dinámicas: Estaremos creando algunas tablas dinámicas. Esta puede ser una revisión para algunos de ustedes, pero después de crearlos, les mostraré algunas técnicas más avanzadas para configurar su tabla dinámica y para visualizar sus datos en su archivo Excel. Tenemos un archivo Excel bastante en blanco aquí. En el paso 1, queremos seleccionar todos los datos de la hoja de trabajo de datos sin procesar, que es la última hoja de trabajo de nuestro archivo, y vamos a crear una tabla dinámica e insertarla aquí abajo. Voy a dar click sobre “raw_data”. Se trata de un montón de datos relacionados con la información de telecomunicaciones, por lo que los clientes que se han suscrito a un servicio de telecomunicaciones con Internet y teléfono. Después de seleccionar estos datos, puedo hacer clic en “Insertar”, “Tabla dinámica”, y voy a hacer clic en “Hoja de trabajo existente” y hacer clic en alrededor de “A14" vuelta en esta hoja de trabajo, y luego presionar “Ok”. Ahora tenemos nuestra tabla dinámica en blanco. Añadamos múltiples dimensiones a esta tabla dinámica. Empecemos primero sumando cargos totales, que es la cantidad total que los clientes están pagando por su servicio telefónico e Internet, por lo que los cargos totales entran en valores. Te das cuenta por defecto que cambia a contar, así que voy a hacer clic con el botón derecho en esto, ir a Configuración de campo y convertir esto en una suma. Voy a agregar método de pago a la columna, así es como el cliente pagó su servicio de Internet o teléfono, así que arrastra el pago a columnas, luego finalmente, poniendo el género en las filas. Ahora tenemos una bonita tabla dinámica de estas diferentes dimensiones. Una última cosa que me gustaría hacer es convertir estos números en moneda, porque ahora mismo es sólo un número regular absoluto. Voy a ir de nuevo a Configuración de campo, y luego hacer clic en “Número”, cambiar esto a una moneda. Ahora, eso es en un formato que me gusta ver porque ahora sé saber que esto es $s reales Déjame solo agregar una columna o dimensión más a las filas solo para que sea un poco más extensa, así que ahí vamos. Ahora para el paso 3, vamos a agregar la columna de contrato al filtro de reporte. Echemos un vistazo aquí, este es el tipo de contrato que tiene el cliente con su servicio telefónico. Si hago clic en este filtro de reporte, puedo filtrar mi tabla dinámica solo a un tipo de contrato determinado dependiendo de qué datos quiera ver. Esa es una característica realmente bonita y útil para agregar a tus tablas dinámicas. Ahora, si nos fijamos en el paso 4, vamos a hacer doble clic en una celda de una tabla dinámica para encontrar los datos subyacentes detrás de ese punto de datos específico. Por ejemplo, si quiero mirar los pagos con tarjeta de crédito que son realizados por nuestros clientes que son mujeres y no tienen dependencia alguna, puedo hacer doble clic en este valor de “$654,000". Si hago doble clic en ese valor, notarás que el resultado son todas las filas subyacentes de mi dataset sin procesar que componen ese valor, por lo que puedes desglosar ese valor específico. Esto realmente ayuda con hacer análisis de datos exploratorios, que puedas encontrar los verdaderos controladores para tus datos. Voy a eliminar esta hoja de trabajo por ahora. Por último, una característica que me gusta hacer a veces es en lugar de crear múltiples tablas dinámicas donde quiero ver un filtro por solo mes a mes y luego otra por un año. A veces los separo en tres hojas de trabajo separadas, si tengo tres filtros diferentes aquí mismo. Lo que puedo hacer es hacer clic en la “Tabla dinámica”, clic en “Analizar tabla dinámica”, luego hacer clic en este menú desplegable “Opciones”, y hacer clic en “Mostrar páginas de filtro de informes”. Si hago clic en “Mostrar Páginas de Filtro de Denunciar”, me va a dar esta opción de solo crear páginas fuera de contratos, que es el filtro de informes que he agregado aquí. Doy click en “Contract”, hit “Okay”. Verás lo que surge son tres hojas de trabajo individuales donde cada una de estas tres tablas dinámicas se filtra hasta el contrato. Este es de dos años, éste es de un año, y éste es de mes a mes. Más importante aún, también cambia el nombre de las hojas de trabajo, por lo que no tengo que entrar y cambiar el nombre de las hojas de trabajo por mi cuenta. Esta es una forma de ser más productivo con la creación de múltiples tablas dinámicas basadas en un filtro que establezca en su filtro de informe. Algunos consejos sobre cómo mirar tablas dinámicas. Me gusta mirar mis tablas dinámicas en el clásico diseño de tabla dinámica. El modo en que puedes hacer eso es ir a Opciones aquí. En la pestaña Display, puedes hacer clic en “Diseño clásico de tabla dinámica”, pulsa “Ok”. El motivo por el que hago eso es porque, entonces puedo ver un encabezado de columna para cada una de las propiedades en mis filas y en mis columnas, por lo que solo hace que sea más fácil saber qué tipo de datos estoy viendo. Mira esto, compara con esto, que en mi opinión no es tan fácil ver cómo se ven mis datos. Un último consejo para llamar es la métrica que arrastras en tu cuadro de valores aquí, es probablemente la parte más importante de tu tabla dinámica para llegar a la derecha. Esta suele ser la divisa, el conteo de clientes, alguna métrica específica que desea calcular dentro de su tabla dinámica. En este caso, para nosotros, van a ser cargos totales, que es monto total que nuestros clientes están pagando por servicio telefónico e Internet. En esta lección, creamos una tabla dinámica y agregamos un montón de campos a la tabla dinámica para ver nuestros datos, específicamente, en este caso, los datos de telecomunicaciones para nuestros clientes. En la siguiente lección, nos meteremos en filtrar, ordenar y rebanar nuestros datos, para que podamos ver mejor las tendencias dentro de nuestro conjunto de datos. 3. Filtrar, clasificar y segmentar: En esta lección, estaremos hablando de cómo puedes filtrar y ordenar tus datos dentro de una tabla dinámica por diferentes dimensiones. También hablaremos de cómo puedes agregar rebanadoras a tu tabla dinámica para filtrar tu tabla de una manera muy interactiva para que sea más fácil de usar para tus compañeros de equipo y para tus compañeros. Tenemos nuestra tabla dinámica aquí y está configurada con unas cuantas dimensiones ya en las columnas y filas. Empecemos con el Paso 1 y agreguemos la tenencia a las filas como un filtro de informe adicional a la tabla dinámica. Primero voy a hacer clic en Tenencia, arrastra esto a filas. Se puede ver que esto se agrega a las filas aquí. Añadamos un reporte adicional a nuestro filtro. A lo mejor podemos arrastrar en PhoneService. Ahora tenemos dos de estos filtros, los cuales tenemos en nuestra tabla dinámica. Esta es una tabla dinámica bastante larga ahora. Ahora voy a volver a comprobar que el cargo total por un método de pago dado está coincidiendo con lo que vemos en esta tabla dinámica, solo doble comprobación. Echemos un vistazo al método de pago transferencia bancaria aquí con pago por mujeres sin pareja. Si hago doble clic allí y me desplaza hacia la derecha de mi conjunto de datos, debería ver mi columna de cargos totales. Si resumo estos números realmente rápido, son $1,245. También puedo agregar un número de suma aquí, sólo para asegurarme 1245. Eso coincide con lo que vemos en esta celda de aquí mismo, lo cual es bueno para nosotros porque sabemos que nuestra tabla dinámica se hace correctamente. Pero esto es sólo para enfatizar aún más el punto que siempre se puede volver a comprobar los números en la tabla dinámica para ver cuáles son los datos subyacentes para un número determinado en la tabla dinámica. Eliminemos esa hoja de trabajo por ahora. Voy a quitar socio ahora de la tabla dinámica y agregar desde el Paso 4, agregar en Servicio de Internet. Vamos a arrastrar en el Servicio de Internet. Pondré esto justo entre Género y Tenencia. De hecho, saltemos de nuevo a este paso aquí, Paso 3. Queremos ordenar por cargos totales descendentes y filtrar por los únicos 10 valores superiores. En realidad se puede ordenar por dos cosas diferentes, por los valores a lo largo de las columnas y los valores a lo largo de las filas. Yo sólo voy a ordenar por cargos totales por ahora. Haga clic en aquí, la Tenencia, y luego ordene por suma de los cargos totales, y haga clic en “descendente”. verás que ahora la Tenencia no está ordenada, sino que los cargos totales a lo largo del lado derecho de mi gran total ahora están ordenados. Ahora, tal vez sólo quiero ver los 10 cargos más altos para las mujeres con servicio de internet, tenencia, así sucesivamente y así sucesivamente. Otra forma en que puedo filtrar rápidamente mi tabla dinámica es haciendo clic en este “filtro por valor”, top 10. Digamos que quiero ver los 10 primeros artículos aquí. Ahora mi tabla dinámica está más ordenada y filtrada para mostrar solo los diez cargos totales principales para cada una de estas categorías que he configurado en mi tabla dinámica. Podrás jugar con estos diferentes filtros y ordenamientos para mostrar solo los datos que importan para ti y para tu público. Voy a deshacer algunos de esos filtros por ahora. Vayamos al Paso 5. Vamos a añadir una rebanadora ahora en la columna Contrato, y vamos a permitir múltiples selecciones en el rebanador. Si hago clic en la tabla dinámica, Analizar tabla dinámica, Insertar rebanadora. Me va a preguntar a qué columna quiero agregar una rebanadora. Voy a dar click en Contract. Está justo aquí abajo. Golpea “Ok”. Obtendrás este pequeño desplegable aquí mismo el cual podrás formatear a tu gusto. Lo que es realmente interesante ahora es que si hago clic en, digamos, déjame simplemente estrechar un poco estas columnas. Si hago clic en “Mes a Mes”, notarás que no solo cambia este filtro, sino que también filtra toda mi tabla dinámica. Si hace clic en un año, todos los datos cambiarán. Si quiero hacer clic en múltiples valores a la vez, solo tengo que hacer clic en estas casillas de verificación, y luego puedo hacer clic en y seleccionar múltiples formas de filtrar mi conjunto de datos usando este rebanador. El rebanador es una forma mucho más interactiva de filtrar datos versus tener que hacer clic en estos desplegables y seleccionar estas casillas de verificación. Esta es solo una mejor manera para que tus compañeros filtren los datos que quieren ver en un panel o informe. Ahora, voy a la tenencia de esta tabla dinámica, y ahora tenemos una simple rebanadora que me permite cortar y cortar este conjunto de datos. Si copio esta tabla dinámica y luego lo pongo aquí y solo hago una pasta, notan que si hago clic en esta rebanadora, todavía filtra esta tabla dinámica aquí abajo. Quiero tal vez desconectar esta tabla dinámica de mi rebanadora. ¿Cómo puedo hacer eso? Al hacer clic derecho en el rebanador e ir a reportar conexiones, verás que hay dos tablas dinámicas, estas dos que están conectadas a este rebanador. Si solo desmarco esta tabla dinámica, que es la tabla dinámica 8, este rebanador ahora no impactará. Bueno, al igual que PivotTable 8 fue ésta, no impactará en esa rebanadora. Siempre se puede ver el nombre de una tabla dinámica haciendo clic en la tabla dinámica, haciendo clic en Analizar tabla dinámica, y luego verás el nombre aquí arriba en la parte superior izquierda. Por último, ¿y si quieres bloquear tu archivo Excel pero permitir que tus compañeros puedan usar las rebanadoras? Esta es una forma realmente común si no quieres que tus compañeros de equipo se involucren y estropeen tus datos o estropeen la estructura de tu archivo Excel. Lo que puedes hacer es hacer clic en la tabla dinámica, clic derecho en ella y ir a la configuración de la rebanadora, lo siento, vamos a hacer clic derecho y luego ir a, vamos a traer este menú aquí. Tamaño y Propiedades, creo que lo es. Haga clic en propiedades y desmarque bloqueado. Sólo algunas otras cosas aquí. También puedes reubicar aquí tu tabla dinámica y agregar columnas adicionales si lo deseas para que parezca más fácil de usar. ocasiones tienes muchas propiedades diferentes en tu rebanadora, que quizá quieras organizar un poco mejor en tu rebanadora. Se puede desmarcar bloqueado aquí. Después, en tu pestaña de revisión, haz clic en Revisar, Proteger hoja, quieres permitir que los usuarios seleccionen celdas bloqueadas, desmarcaremos eso, pero también usamos gráfico de tabla dinámica. Desmarca seleccionar celdas bloqueadas, desactiva usar informes de tabla dinámica y luego pulsa “Ok”. Ahora bien, esta hoja de trabajo está bloqueada, no puedo conseguir ninguna de las celdas. Pero aún puedo usar el rebanador, lo cual es importante para que tu equipo y tus compañeros puedan que dos aún interactúen con tu tabla dinámica para ver los datos que quieren ver. Hicimos un poco de filtrado y clasificación rápidos en nuestra tabla dinámica, pero también agregamos una segadora para que nuestra tabla dinámica sea más interactiva para nuestros colegas. También hablamos de cómo puedes bloquear tus archivos de Excel para que solo las rebanadoras de tabla dinámica disponibles para usar para mirar y analizar los datos. En la siguiente lección, hablaremos sobre cómo puedes agregar campos calculados a tu tabla dinámica para crear nuevas métricas que no estén disponibles en tu conjunto de datos actual. 4. Añadir campos calculados: En esta lección, estaremos hablando de todos los campos calculados, que son fórmulas que se están ejecutando constantemente en tus tablas dinámicas a medida que estás moviendo dimensiones en tu tabla dinámica. Contamos con una tabla dinámica aquí basada en nuestros datos de telecomunicaciones de clientes. Contamos con método de pago, género en las filas, servicio telefónico en las columnas, contrato en el filtro y total de cargos en los valores. Y si queremos saber cuál es nuestro cargo mensual promedio para todos nuestros clientes, no importa cómo estructuremos nuestra tabla dinámica. Aquí es donde haríamos un campo calculado. Puedo hacer click en Tabla dinámica Analizar en la cinta. Haga clic en campos, elementos y conjuntos, haga clic en campo calculado. El modo en que podemos calcular el cargo mensual es tomando cargos totales divididos por tenencia. Por lo general, cuando tienes un campo calculado, es posible que quieras agregar algo antes del nombre de los campos, algo así como calc, o incluso algo así como una estrella. A mí me gusta usar Calc y llamaremos a este cargo mensual. La fórmula que queremos decirle a Excel que básicamente tome los cargos totales, que es el total de cargos corridos que nuestros clientes nos están pagando, y dividiéndolo por la tenencia. Una vez que tengamos esta fórmula en nuestro campo CalcMonthlyCharge, puedo golpear bien. Ahí puedes ver Excel agrega automáticamente esta columna de cargo mensual a mi tabla dinámica y puedo ver rápidamente cuál es el cargo mensual promedio para mi conjunto de datos. Ahora mucha gente se pregunta por qué teníamos que hacer este campo calculado, por qué no pudimos hacer esto en nuestro conjunto de datos. Podríamos ir a nuestra última pestaña, que son nuestros datos brutos y se podría en teoría agregar una columna aquí que diga cargo mensual promedio y se podría tomar iguales cargos totales divididos por la tenencia, que está justo aquí y luego arrastra esto desde el lado negativo. Ahora tienes el cargo mensual promedio a nivel de fila para cada uno de nuestros clientes. Si vuelvo a mi tabla dinámica, puedo cambiar la fuente de datos para incluir esa columna extra de datos que agregué. Antes de que fuera en la columna t, sólo voy a cambiar esto a la letra u, pega bien. Ya verás que ahora hay una columna de cargo mensual promedio, que calculé manualmente en el backend. Pero notan que cuando agrego a mi tabla dinámica, no coincide con lo que está en mi campo calculado CalcMonthlyCharge. Eso se debe a que no importa cómo cambies este ajuste, puedes cambiar esto a un promedio, realmente no coincide con lo que realmente es este valor, y este es un concepto importante a captar porque el calculado se aplica al cálculo. Cuando se agrega el cargo mensual promedio, que es esa columna que agregamos aquí en nuestro conjunto de datos al final, no coincide con lo que vemos en nuestro campo calculado porque el campo calculado calcula en el nivel de fila, mientras que cuando tomas esto, solo estás tomando un promedio del cálculo. Esta es la forma más precisa de ver el cargo mensual promedio de nuestro conjunto de datos. Voy a arrastrar esto por ahora y sólo volver a colapsar un poco estas comunicaciones. Es importante cuando haces campos calculados para hacerlos fuera de las métricas sin procesar en lugar de fuera de columnas que contienen fórmulas dentro de tu dataset sin procesar. Ese es uno de los puntos clave para el paso dos. Paso tres, vamos a crear un elemento calculado, que es diferente de un campo calculado combinando estas tres categorías diferentes, transferencia bancaria, cheque electrónico y cheque enviado por correo. Vamos a combinar estos en un solo grupo y sólo vamos a llamarlos a todos los cheques. En realidad sólo vamos a combinar cheque por correo y cheque electrónico en un grupo llamado todos los cheques porque queremos consolidar un poco nuestro reporte. Puedo dar click en Tabla dinámica Analizar. Tienes que asegurarte de que estás haciendo clic dentro de uno de tus campos. Ve al campo, artículos y conjuntos, artículo calculado, y vamos a llamar a esto, el nombre all check. La fórmula simplemente va a ser cheque electrónico, insertar elemento, más cheque de correo, insertar artículo, golpear bien. Ya verás que tengo este elemento adicional todo cheque calculado el cual es un resumen de mis propiedades de cheque de correo y cheque electrónico. En este caso, probablemente quisiera desmarcar electrónica y enviada por correo porque mi grupo todo cheque ya contiene esos dos valores. Vamos a profundizar en cómo podemos hacer algunos campos calculados más adelantados. Vamos a crear un campo calculado llamado CalcServiceFee para cualquier cliente que tenga un cargo mensual promedio de unos pocos dólares o más, les vamos a cobrar dos por ciento por solo administrar facturas altas, llamemos a eso. Voy a volver a la tabla dinámica, analizar, campos y conjuntos, campo calculado, y vamos a llamar a éste CalcServiceFee. Lo que es interesante aquí es que en realidad puedes agregar sentencias if a tus campos calculados. Puedo escribir si, paréntesis de izquierda, el cargo mensual, que es, si recuerdas, este es otro campo calculado que calculamos muy comenzando. Si el cargo mensual calculado es mayor a 50, entonces vamos a tomar el CalcMonthlyCharge, y multiplicarlo por dos por ciento. Si no es mayor a 50, sólo vamos a devolver un cero y luego cerrar nuestra declaración if. Algo importante a tener en cuenta aquí es que puedes usar sentencias if y puedes crear campos calculados a partir de otros campos calculados en tu tabla dinámica. ¿Está bien? Puedes ver aquí, es una cantidad realmente pequeña, pero déjame solo añadir algunos lugares decimales más a este número. Añadamos dos decimales. Aquí se puede ver que estos números son básicamente dos por ciento del cargo mensual promedio. Pero lo importante es que se pueden agregar estas sentencias if y campos calculados encima de otros campos calculados. Voy a arrastrar la tarifa por servicio de calc y los cargos totales por ahora. Hagamos otro más complicado que es crear un campo calculado llamado cuota de transacción calc solo para clientes que paguen con tarjeta de crédito y esto va a ser equivalente a 2.9 por ciento multiplicado por el cargo mensual más 0.30 dólares, que es un cargo bastante común que añades para los clientes que pagan con tarjeta de crédito. Voy a dar click en Tabla dinámica Analizar, campos, ítems, conjuntos, campo calculado. Vamos a llamar a esto CalcTransactionFee y esto es si el método de pago, busquemos eso por aquí, inserte. Si este método de pago es igual a tarjeta de crédito automática. Asegurémonos de que tengamos los casos correctos. Entonces queremos tomar el CalcMonthlyCharge u otro Campo Calculado, multiplicar eso por 2.9 por ciento y además agregar en ese cálculo de $0.30 para cargos de tarjeta de crédito, cero para cerrar el caso falso de nuestro estado de cuenta if. A ver qué pasa si me pego bien. Vas a ver que te da un montón de ceros. El motivo de esto es que los campos calculados, una copia sobre estos campos calculados más avanzados es que no puedes usar un encabezado de columna o un encabezado de campo, en este caso, PaymentMethod, dentro de tu estado de cuenta if en un campo calculado. La solucion para este escenario es volver a tus datos sin procesar, y vamos a agregar una columna de ayudante llamada tarjeta de crédito? Solo eliminemos esto relacionado por ahora. Básicamente vamos a añadir un uno o un cero a esta columna si el PaymentMethod es una tarjeta de crédito. Escribiremos igual si la celda R2 equivale a tarjeta de crédito automática. Dale uno a éste, de lo contrario dame un cero. Arrastremos esta fórmula hacia abajo. Ahora esto es simplemente decirnos si el cliente pagó o no con una tarjeta de crédito y eso se denota por un uno o un cero. Ahora si vuelvo a mi tabla dinámica donde tengo mi cuota de transacción, déjame asegurarme de que esto esté haciendo referencia al conjunto de datos correcto. Tenemos que asegurarnos de que va todo el camino a la columna u, que es correcto, golpea bien. Solo voy a refrescar esta vez solo para asegurarme de tener los datos más actualizados. Ahora voy a volver a mi campo calculado, haga clic en el menú desplegable aquí, calc cuota de transacción, y en lugar de PaymentMethod equivale a tarjeta de crédito, voy a decir eliminar esta parte del si condicional estado de cuenta y decir tarjeta de crédito? esa fue la nueva columna que agregamos. Si eso es mayor que cero, entonces haremos el cobro mensual calc, que es nuestro campo calculado para calcular cargos múltiples, multiplicado por 0.029 o 2.9 por ciento y agregamos $0.30, hit ok. Ahora puedes ver que la cuota de transacción solo cambió ligeramente alguna vez para nuestras transacciones con tarjeta de crédito. Simplemente agregaré algunos lugares decimales más a este número para que puedas ver más detalle cómo se ve esto. Se puede ver que sólo tenemos comisiones de transacción para el pago con tarjeta de crédito porque eso fue lo que mostramos en nuestro último conjunto de datos. Para transferencia bancaria y todo cheque, no coincide con la condición if, por lo que, por lo tanto, sólo vemos comisiones de transacción para este campo calculado. Hablamos de cómo puedes agregar campos calculados a tus tablas dinámicas y también hacer algunas cosas más avanzadas a tus fórmulas de tabla dinámica agregando sentencias if con otros campos calculados en ellas. En la siguiente lección, vamos a hablar de las mejores formas de visualizar datos de una tabla dinámica, que es el uso de gráficos dinámicos. 5. Crear gráficos dinámicos: En esta lección, estaremos hablando de cómo puedes crear gráficos dinámicos, que son tan flexibles como las tablas dinámicas para visualizar tus datos. También discutiremos cómo puedes guardar plantillas que coincidan con las pautas de marca de tu empresa. Al mirar a nuestros datos de telecomunicaciones de clientes, queremos crear un gráfico dinámico básico a partir de esta tabla dinámica. Puedo hacer clic en “Analizar tabla dinámica”, y luego en “PivotcChart” justo aquí. Este es un gráfico dinámico bastante básico fuera de tu tabla dinámica. No se ve súper elegante. Vamos a añadir un poco de formato básico a esto. A lo mejor podemos cambiar este color de barra a otro color. Vamos a cambiar esto a un color de relleno diferente aquí mismo. Cambiemos este por tal vez como un bonito verde, y tal vez podamos hacer que el fondo de esto sea un poco más interesante de ver. Puedo ir a Format Axis. En realidad, podemos simplemente dejarlo así. Dejémoslo así por ahora. También queremos poder filtrar esta tabla en base a esta columna de contrato aquí que está en nuestro filtro de reporte. Añadamos una rebanadora a nuestra tabla dinámica como hicimos en una lección anterior. Gráfico dinámico y Pivot Analizar, Insertar rebanadora. Vamos a estar usando de nuevo la columna de contrato. Golpea “Ok”. Acerquémonos un poco aquí. Notarás que al hacer clic en una de estas opciones, no sólo cambia la tabla dinámica, sino que también cambia el gráfico dinámico. Esta es una forma en que puedes conectar todos tus datos, tu tabla dinámica, y tus gráficos juntos en un solo lugar para que todo sea súper dinámico. Lo importante aquí a saber es que puedes editar y agregar imágenes a tus PivotCharts. Pero lo que es más importante, puedes convertir esto en una plantilla. Si hago clic con el botón derecho en este gráfico y voy a Guardar como plantilla, sus plantillas se agregan a esta carpeta de plantillas de gráficos en su escritorio. Voy a llamar a este MyChartFormat. Golpea “Guardar”. Ahora podemos reutilizar ese gráfico para usarlo como plantilla para un gráfico futuro que creemos. Vamos a crear un nuevo PivotChart. Ir a Analizar tabla dinámica, Gráfico dinámico. Tenemos este mismo gráfico aburrido que obtenemos por defecto. Pero ahora vamos a hacer clic derecho en el gráfico, ir a “Cambiar tipo de gráfico”, y luego ir a “Plantillas” Puedes ver “MyChartFormat” que guardé en el paso anterior aparece aquí. Cuando hago clic en eso, cambia automáticamente el formato de ese gráfico para coincida con cualquier plantilla que tenía configurada aquí. Puedes dedicar mucho tiempo a conseguir que las pautas de marca y los colores de la marca, agregar logotipos de la empresa se vean perfectamente en este gráfico, y luego reutilizar esa plantilla en futuros gráficos como este de aquí. Una última cosa que me gustaría hacer con mis gráficos es hacer que el título del gráfico sea muy dinámico. Hagamos clic en “Diseño” de este gráfico, “Agregar elemento de gráfico”, “Título del gráfico”, “Arriba del gráfico”. Ahora, por defecto, dice título del gráfico, y normalmente, probablemente simplemente escribirías manualmente algo en este título. Pero si vas a la barra de direcciones de fórmula, escribes igual, y luego haces clic en “Celda A1", en este caso, pulsa “Enter”, notarás que el gráfico de tabla dinámica cambiará dependiendo de lo que esté en esta celda aquí mismo. Puedo decir mi gráfico cool y esto cambiará automáticamente. Es una forma mucho más dinámica de editar los títulos de tus PivotCharts o gráficos ordinarios a medida que los estás construyendo para informes y cuadros de mando. No tienes que hacer doble clic en esto y cambiar el título del gráfico cada vez. Acabamos de recorrer cómo puedes agregar gráficos dinámicos a tus tablas dinámicas para que tus datos sean realmente agradables visualmente para tus colegas y para tus equipos. Ahora nos meteremos en el uso de la fórmula GETPIVOTDATA en Excel para sacar datos de tus tablas dinámicas para utilizarlos en informes y tableros. 6. IMPORTARDATOSDINÁMICOS para informes personalizados: Vas a aprender a usar la fórmula GETPIVOTDATA en Excel para obtener datos de tu tabla dinámica. Esto es realmente útil ya que tu tabla dinámica está cambiando y quieres aplicar algún formato personalizado a los valores que saques de tu tabla dinámica. Tenemos esta tabla dinámica aquí con método de pago, StreamingMovies, TotalCargos, Tenencia promedio y solo para volver a comprobar aquí, se puede ver aquí que la tenencia promedio no es una suma porque queremos encontrar el promedio de longitud de todos nuestros clientes para esta empresa de telecomunicaciones para estas métricas dadas. Vayamos al Paso 1. Solo hagamos referencia a una celda de tabla de pivote en la celda G15. Si acerco un poco aquí y escribo igual a una celda, digamos, voy a 1,145, te das cuenta de lo que me da esta función GETPIVOTDATA. El primer parámetro para esta función es la suma de la métrica en cuestión. En este caso, se trata de cargos totales, cuánto se estaban pagando total de nuestro cliente. A12 es simplemente la ubicación de nuestra mesa de pivote. Entonces los siguientes parámetros son donde solía en una fórmula SUMIFS donde tiene criterios diferentes y el rango de criterios reales. StreamingMovies es no, PaymentMethod es transferencia bancaria automática. Por eso este 1,145 corresponde a este número. Suma de los cargos totales donde es una transferencia bancaria y el StreamingMovies es no. En las celdas G18 y G19, quiero que encuentres la suma de los cargos totales y la tenencia media, pero quiero que formulaicamente obtengan estos números de la tabla pivot. Ahora, íbamos a estar mirando también clientes que pagan con tarjeta de crédito y no tienen servicio telefónico. Sólo escribamos esto desde cero. Escribamos igual a GETPIVOTDATA. Queremos obtener la referencia dinámica de suma de los cargos totales, que es esta celda aquí mismo. Ya te he escrito esto. El cuadro pivote es sí, en la celda A12 y podemos hacer esto una referencia absoluta y eso lo conseguimos al mirar nuestra función anterior GETPIVOTDATA. Queremos ver el método de pago para quienes paguen con tarjeta de crédito. Es, creo, Tarjeta de crédito automática. Entonces también para personas que no tenían películas en streaming. Escribamos StreamingMovies es la columna y la respuesta es no. Obtendrás un error. El motivo es que cuando se hace referencia al F17, hay múltiples cargos en la métrica que queremos sumar, realidad hay que agregar un signo comercial y dos comillas dobles después de él para asegurarse que la función GETPIVOTDATA sepa cómo para hacer referencia a los valores correctos. Si arrastro esto hacia abajo, también verán que este promedio de tenencia es de 36, que coincide con quienes pagan con tarjeta de crédito, y tenían un no streaming de películas, y tenían una tenencia promedio de 36. Una última cosa de la función GETPIOVDATA en cuanto a opciones métricas generales es asegurarte de que tengas activada la función GETPIOVDATA en primer lugar, la cual puedes encontrar en las opciones solo asegúrate de La función Generar GETPIVOTDATA está activada. Ahora, en realidad construyamos un tablero a partir H34 usando referencias celulares al tipo de pago, ya sea que el cliente transmita películas y la tenencia. Empecemos aquí mismo. Básicamente queremos llenar esta tabla en nuestro archivo Excel. El motivo por el que usaríamos esta tabla en lugar de usar solo la tabla pivotada directamente es porque podemos dar formato mejor a esto a nuestras necesidades versus formatear en una tabla dinámica, lo cual podría ser un poco complicado. Empecemos a escribir la función GETPIVOTDATA en esta celda aquí mismo. Vamos a reutilizar esta función aquí mismo porque ya tenemos configurada la función GETPIVOTDATA. Voy a copiar eso, inserte aquí mismo. Suma de cargos totales, queremos conseguir eso para StreamingMovies. En lugar de codificar esta nota aquí, en realidad podemos hacer referencia a esta columna aquí mismo, cual he codificado con sí y no como columna auxiliar. Puedo bloquear la referencia de columna. Entonces para PaymentMethod, solo voy a seleccionar esta celda aquí mismo, que contiene el PaymentMethod y bloquear la columna pero también notan que también necesito tirar en la tenencia. Eso se desató por estos números. De hecho, arrastremos en la columna de tenencia a esta tabla de pivote. Tenencia, pondremos eso en filas. Quitemos la tenencia promedio por ahora, no necesitamos eso. Ahora, tenemos la tenencia desatada por PaymentMethod y StreamingMovies por cargos totales. Volvamos a nuestra función de tabla dinámica. StreamingMovies es sí. Esto es por alguna razón se puso en las cotizaciones. Hagamos esto al G34 como referencia y vamos a bloquear esta columna. Entonces por fin sólo vamos a escribir “tenencia”, ese parámetro va a ser este número, que es sólo el número de meses de bloqueo de tenencia en la fila. Si presiono “Enter”, ahora puedo arrastrar esto desde todo el camino a la derecha y también arrastrarlo hacia abajo. Puedo ver los cargos totales por estos métodos de pago y por estos tipos de streaming. Si arrastro copiar esta fórmula aquí abajo, también va a funcionar correctamente porque estoy haciendo referencia al PaymentMethod y en este caso el StreamingMovies va a ser no. Puedo arrastrar esto hacia abajo y también hacia la derecha. Muy fácil. Es así como puedes crear un tablero muy hecho a medida con todo el formato de color que necesitas sacando datos de tu tabla dinámica incluso cuando tu tabla dinámica está cambiando la estructura. Lo importante a tener en cuenta aquí es que sean los campos que tire o haga referencia a la función GETPIOVDATA, que tener esos campos en la tabla dinámica misma, razón por la que tuve que poner tenencia en las filas. El paso final, hagamos referencia dinámicamente a tablas pivote en otras hojas de trabajo utilizando la función INDIRECTA. Verás cómo tienes que usar las comillas simples para usar la función INDIRECTA. En lecciones anteriores, ¿recuerdas cómo saltamos estos filtros, estos reportes en mes a mes, un año, y dos años? Estas son solo tres tablas pivote diferentes pero filtradas por contrato. Volvamos a esta tabla y queremos agregar referencias a esos valores para cargos totales por género para estos filtros de reporte específicos. Si escribo igual, quiero conseguir masculino mes a mes. Vayamos a la pestaña Mes a Mes. El total para masculino va a estar aquí mismo. Si golpeo “Enter”, echemos un vistazo a la fórmula y veamos cómo podemos hacer que esto sea más dinámico. Podemos cambiar este valor, en lugar de ser codificado duro, cambiar esto a masculino y hacer esta columna cabecera y bloquear en la fila. ¿ Cómo cambiamos este mes a mes para que podamos referirnos dinámicamente a estas células aquí mismo? Bueno, aquí es donde utilizarías la función INDIRECTA. Voy a eliminar el mes a mes justo aquí por ahora. Voy a escribir INDIRECTO, paréntesis de izquierda y voy a escribir cotización doble, cotización simple, cita doble, ampersand, luego referencia mes a mes, esta celda de aquí y voy a bloquear en la columna referencia, poner otra cotización doble, cotización doble, cotización simple, cotización doble, golpear otro ampersand, y luego poner este parque de exclamación A3 dentro de comillas dobles también. Golpea nuestros paréntesis. Esta INDIRECTA ahora hará referencia adecuadamente estas celdas como el trabajo que quiero referirme. Ahora, puedes ver esto me da el mismo valor pero si arrastro esto hacia abajo, este 1,958 ahora hace referencia a la hoja de trabajo de un año para macho, que es de 1,958 y básicamente puedo arrastrar esto a hembra también, rellenar adecuadamente usando el Función GETPIVOTDATA pero también esta pequeña solución para hacer referencia a otras hojas de trabajo dentro de mi archivo usando la función INDIRECTA. Hablamos de diferentes formas en las que puedes usar la función GETPIOVDATA para sacar datos de la tabla dinámica en informes con formato más personalizados que puedes presentar a tu equipo y a tus colegas. En la siguiente lección, vamos a hablar de formas en que puedes refrescar tu tabla dinámica usando macros. 7. Actualización de tablas dinámicas con macros: En esta lección, vamos a usar macros para actualizar automáticamente diferentes diseños y vistas de tus tablas dinámicas. Tenemos una tabla dinámica impulsada de nuestro conjunto de datos principal. Pero una cosa que quiero hacer de manera diferente con esta tabla dinámica, es ir a raw_data y convertir esto, nuestro conjunto de datos sin procesar, en una tabla de Excel llamada TelecomData. Simplemente selecciona todo desde la celda A1 hasta el final. Tenemos alrededor de 6 mil filas de datos. Ve a “Insertar”, “Tabla” pega “OK” con los encabezados y solo vamos a llamar a esto TelecomData. Ahora cuando cambie esta tabla, cuando agreguemos nuevas filas o nuevas columnas, esto actualizará automáticamente nuestra fuente. Es realmente importante cuando ahora cambiamos la fuente de esta tabla dinámica para hacer referencia a esa nueva tabla. Si voy a “Cambiar fuente” en lugar de ubicación, solo puedes decir TelecomData. Si refresco esto, todo saldrá como se esperaba. No se han cambiado datos, por lo que no veremos ningún cambio en este momento. Quiero que ahora solo agregues rápidamente un nuevo rol de datos a esta tabla, nuestros datos brutos. Esto es click, baja aquí, pulsa “Enter”. Vamos a darnos sólo algunos valores aleatorios. Te diste cuenta de cómo obtenemos nuevos datos agregados. Pero vayamos a los cargos totales. Vamos a añadir un número realmente grande, como un millón. Si vuelvo a mi tabla dinámica, puede hacer clic derecho en “Actualizar” o simplemente hacer clic en “Analizar tabla dinámica” y pulsar “Actualizar”. Verás este nuevo Total en blanco agregado a la parte inferior porque nuestra tabla TelecomData cambió y por lo tanto nuestros datos brutos cambiaron y por lo tanto nuestra tabla dinámica cambió. Solo eliminemos esta fila por ahora, porque no queremos estropear nuestro conjunto de datos. Eliminar fila de nuestra tabla. Volvamos a este set. Ahora, vamos a activar la pestaña Desarrollador en nuestro archivo Excel. Esto va a ser diferente para los usuarios de PC y los usuarios de Mac. Pero en el Mac, puedes hacer clic en “Excel”, “Preferencias”, “Cinta” y “Barra de herramientas” y luego en la parte inferior, deberías ver en el lado derecho, “Desarrollador”, pulsa “Guardar”. Ahora vas a ver la pestaña Desarrollador agregada a tu Cinta. Quiero que haga clic en “Desarrollador” y luego haga clic en “Macro”. Lo siento, quiero que prefieras hacer clic en “Grabar Macro” y llamar a esta Macro “PaymentOnly”. Lo que quiero que hagas ahora es hit “OK” y luego la Macro está grabando ahora. Qué es una Macro esencialmente estamos grabando un montón de pasos para decirle a nuestro archivo Excel que haga esto cada vez que ejecutamos la Macro. Ahora mismo es grabación. Quiero que vayas dando click en la “Tabla dinámica”, “Analizar tabla dinámica”. Vamos a decir “Borrar todo”. Después también haga clic en “Refrescar”. Esto va a refrescar nuestros datos. Ahora vamos a configurar nuestra tabla dinámica para mostrar el diseño que queremos. Tomemos “Cargos totales”, agreguemos eso a nuestros “Valores”. Solo asegurémonos de que esto sea una suma de nuestros valores. Además, solo asegúrate, la Configuración de Campo tenemos el formato numérico que queremos. Golpea “OK”. Ahora vamos a dar click en “Método de pago”. Pon esto en las filas. Simplemente aumentamos un poco el tamaño de la fuente para que podamos ver este dato un poco mejor. También puedo acercarme aquí. Ahora solo tenemos las PivotTables configuradas el método de pago. Ahora puedo volver para dejar de grabar. No verás que pase nada desde aquí, pero repitamos los pasos cuatro y cinco con dos macros más para el género de internet o el interno. T servicios en filas. Género y columnas y otro Macro llamado ContractTenencia donde la tenencia está en las filas y el contrato está en las columnas. Sigamos esto otra vez. Grabar Macro. Vamos a grabar uno ahora, llamado Género de Internet. Golpea “OK”. Los pasos son, ve a “Borrar todo”, “Actualizar” y ahora vamos a agregar Género a las columnas y luego Servicio de Internet. Vamos donde está el Servicio de Internet en las filas. Entonces finalmente Total de Cargos en los Valores. Entonces sólo nos aseguramos de que esto también sea una suma. Después golpea “Stop” en la grabación. Entonces haz esto una última vez para la Tenencia Contract. Yo quiero mostrarles algo al final. Grabar Macro. Estamos llamando a este uno Contract Tenencia. Golpea “OK”. Haga clic en la “Tabla dinámica”, “Analizar” y vamos a volver a hacer clic en “Borrar todo”. Probablemente muy acostumbrado a esto ahora, “Refrescar”. Vamos a volver a poner Cargos Totales en los Valores. Hagamos de esto una suma. Entonces queremos tener Tenencia en las filas, Contratos en las columnas. Vamos a golpear “Stop” en la Macro. Está bien. Ahora tenemos tres macros diferentes y vamos a asignar cada una de esas macros a un botón en nuestro archivo Excel. Ahora ya puedes usar un botón como este, uno de estos botones estándar de la pestaña Desarrollar. Podemos llamar a esto así Button1_click. Asignar esto a Tenencia por Contrato. Tenemos nuestras tres macros aquí mismo cuando hice clic en “Botón”, puedo asignar esta a Contract Tenence. Puedo hacer clic derecho en esto y cambiarle el nombre a Contract Tenencia. Pero me gusta realmente usar el tipo regular de opciones de dibujo dentro de Excel. Simplemente creo que puedes editar los botones un poco mejor y agregar más colores, por lo que se ve un poco más divertido de usar. Vayamos a “Insertar”, “Dibujar una forma” y solo hagamos una forma básica como esta. Podemos llamar a este solo Pago. Lo que puedes hacer aquí es, puedes hacer clic derecho en este botón y Asignar una Macro. Vamos a asignar este al botón Solo Pago. Entonces vamos a crear otra forma para nuestro último Macro. Asignemos esto un Macro de Género de Internet. Simplemente escribe en Internet Género. Ahora si hago clic en alguno de estos botones, mi tabla dinámica se actualizará automáticamente para mostrar ese tipo específico de diseño que quiero. El motivo por el que podrías hacer esto es porque a veces quieres tener una forma rápida de cambiar el diseño realmente rápido sin que tu equipo y tus compañeros tengan que arrastrar y soltar columnas en la mesa. La alternativa es que puedes tener tablas dinámicas integradas en hojas de trabajo individuales como esta, pero esto puede ser un poco difícil de recorrer. Dependiendo de tus casos de uso donde pienses en cómo quieres mejorar la experiencia de usuario y la interfaz de usuario. El importante es que no puedes deshacer los resultados de nuestras macros, así que solo asegúrate de hacerlo bien la primera vez cuando estés grabando tus macros. Por último, si quieres ver el código de tus macros, puedes ir a “Desarrollador”, “Visual Basic” y luego hacer clic en “Módulos” aquí y verás el código de todas las macros que has escrito en este archivo. Aquí puedes ver esto es Solo Pago. Esto es Género en Internet, y esto es Tenencia por Contrato. Esto está más allá del alcance de esta clase, pero también puedes meterte en la edición real del código de nuestro código de Visual Basic para tus macros. Te mostramos cómo puedes usar macros para automatizar la actualización de tus tablas dinámicas y cómo también puedes editar el código de tus macros y asignarlas a botones para facilitar que tus compañeros y equipos actualicen tu Tablas dinámicas. 8. Reflexiones finales: Enhorabuena por tomar esta clase sobre técnicas avanzadas de tabla pivot en Excel. Algunos de los puntos clave de esta clase, aprendimos sobre cómo puedes usar tablas pivote y hacerlas mucho más interactivas para tu equipo y para tus compañeros. También hablamos de cómo puedes agregar campos calculados para hacer cálculos en ejecución en tus tablas dinámicas, todo el camino hasta macros para refrescar automáticamente tus tablas pivote para usarlas. Otra cosa que quiero llamar es que las técnicas que aprendiste en esta clase de tablas dinámicas avanzadas se pueden aplicar a muchas otras plataformas de análisis de datos como Google Sheets y Tableau. De verdad espero que tomes las habilidades que aprendes de esta clase y las apliques al proyecto de la clase, donde estarás construyendo informes y gráficos de tablas pivote y en última instancia, un tablero fuera de los datos de desgaste de los empleados, que luego puedes usar para presentarle a alguien internamente en tu empresa, y también quiero que tomes insights que puedes obtener a partir del análisis de datos de tabla pivot. Espero ver tu proyecto en la galería de proyectos para esta clase. Siéntete libre de publicar tu análisis, hacernos preguntas, también estoy feliz de contestar cualquier pregunta que tengas. Buena suerte con los proyectos.