Transcripciones
1. Introducción a los consejos de la tabla dinámicos:: Oigan, todo el mundo. Esta siguiente sección del curso se trata de explorar y analizar datos con tablas pivote . Te mostraré que tenías un grupo fechas y valores. Defina sus propias listas de ordenar personalizadas e incluso reviva los datos de origen eliminados del efectivo de pivote. Tenemos mucho que cubrir, así que empecemos.
2. Personalización de la lista de campo: Yo quiero compartir una tabla pivotante. Pro tip, chicos. Es un consejo muy básico. Una estrella, pero muy útil sin embargo puede hablar sobre cómo usar algunas
opciones específicas de lista de campos de tabla dinámica para ayudarte a mantenerte organizado. Ahora, forma
predeterminada, la lista de campos de tabla dinámica suele estar apilada, por lo que ves una lista de campos en la parte superior y eres áreas dolores en la parte inferior, tus filtros, filas, columnas y valores. Por lo que la parte superior de esa lista de sensaciones verás un icono de engranaje que te dará un menú de opciones. Y así es como se ve generalmente el defecto, que para muchos casos estará bien. Pero en los casos en que tengas un número muy grande, tablas de
fuentes como Si estás usando poder, pivote encima de un modelo de datos complejo o vinculando a una hoja con muchos, muchos, muchos campos o columnas. Ahí hay un par de opciones más que pueden ser útiles para usted. número uno está mostrando tus campos y áreas uno al lado del otro, que es esta segunda opción aquí. Lo que me gusta de esto es que te da ese dolor vertical entero que aloja tus campos para que sea
mucho más fácil buscar y desplazarse por esas columnas si tienes un número muy grande de ellas, y entonces la segunda opción es ordenar a a a C en contraposición al orden de la fuente de datos. Por lo tanto, si tienes tus columnas alineadas de manera significativa en tu tabla de origen, es posible
que no te importe esta opción, pero de lo contrario, ordenar alfabéticamente puede ayudarte a acceder a esos campos un poco más rápidamente. Casos de uso tan comunes otra vez. Actualizando ese diseño para mostrar todos sus campos en un solo dolor vertical, especialmente para tratar con modelos de datos y para ordenar la mayoría de los campos alfabéticamente en
casos en que su orden de origen no sea necesariamente significativo. Así que vamos a saltar a nuestro cuaderno de trabajo pro tip si quieres seguir y te voy a mostrar un par de ejemplos muy rápidos de cómo es esto. Muy bien, así que alrededor del cuaderno pro tip en la pestaña Tabla de Contenidos para una navegación rápida. Estamos en la sección de punta de tabla pivotante gris, y voy a buscar de nuevo la demo de opciones de lista de campo. Una estrella. Demo muy básico. Es como justo fuera, y aquí tenemos una mesa pivotante contra la base de datos de películas I Am Devi. Estamos viendo títulos de películas y géneros y calificaciones, junto con algunos valores como ingresos brutos y presupuesto. Entonces aquí está mi lista de campos en el lado derecho de mi pantalla. Si no ves tu lista de campos, puedes hacer clic derecho en celda en el pivote y cambiar ish hide o show, generalmente el último elemento del menú. O puedes entrar en herramientas de mesa pivotante y esto mostrar dolor aquí todo el camino a la derecha. Bueno, medida que se activa y desactiva esa lista de campos. Ahora, dentro de esa lista de campos, aquí está ese icono de engranaje que te permitirá personalizar el diseño y por defecto, donde apilar nuestras áreas y campos. Aquí está ese segundo ítem, que básicamente crea a Collins. Podemos arrastrarlo hacia fuera para darnos un poco más de espacio. Ahora tenemos todas nuestras columnas o campos aquí en esta columna izquierda, y tenemos nuestros cuatro dolores o áreas en la derecha o filtros, filas, columnas y valores. Ahora, si miramos nuestros campos, veremos que no están ordenados en ningún tipo de orden alfabético, han titulado y fecha de lanzamiento color género lenguaje, y en este caso esto está bien, porque yo poner mis campos calculados en mis medidas numéricas al final, lo cual es un hábito que generalmente sigo. Pero si ese no es el caso, puedes volver atrás y decir: Vamos a ordenar estos campos un mareado presupuesto fundido color country,
etcétera, etcétera, lo que puede hacer que esta lista de campos sea un poco más fácil de navegar, sobre todo si tienes cientos de viene aquí. También tienes una barra de búsqueda, lo que hace que sea muy fácil perforar y encontrar campos individuales de esa manera también. Entonces es realmente un simple es que tienes algunas otras opciones aquí, como solo mostrar los campos o simplemente mostrar las áreas. Personalmente no encuentro esos muy útiles, así que generalmente me apego a una de estas 1er 2 opciones. En este caso, volvamos a nuestra opción apilada tipo de arrastrarla de nuevo hacia fuera. Y ahí tienes un campo de tabla personalizable o pivote. Escuchar, maquetación
3. Anchura de la columna autofitting: todo bien. Quiero tomarme unos minutos y hablar de cómo podemos evitar que nuestras columnas de tabla pivotante ajusten
automáticamente ahí con ahora por defecto, cada campo y un pivote ajustarán la columna subyacente con automáticamente para acomodar el valor más largo en ese campo. Y normalmente esto es útil porque significa que todo el texto en nuestros campos será visible por defecto. Pero puede ser realmente inconveniente si estás trabajando con una serie de
campos basados en texto muy largos que podrían tener cientos de caracteres o palabras. Por lo que puede terminar con algo que se parece a esto aquí. Tenemos un campo de descripción basado en texto largo, y hemos jalado en etiquetas de fila, y como resultado, se estira la columna B mucho más allá del ancho de nuestra ventana. Ella está haciendo que sea muy difícil trabajar con ahora simple fix aquí para evitar que ocurra ese re dimensionamiento automático o auto fitting. Todo lo que tenemos que hacer es saltar a nuestra pestaña de opciones o botón de opciones dentro de su pestaña analizada, y vamos a hacer clic o desmarcar esta casilla que dice Anchos de columna de ajuste automático al actualizar. Y eso está en la ciudad de diseño y formato del cuadro de diálogo Opciones de tabla dinámica. Y luego una vez que hagas
eso, básicamente evitará que la columna B vuelva a dimensionar o en la que viva la llamada en este campo arrastras ese campo a tus etiquetas de fila o columna. Por lo que otro consejo aquí, puedes usar la opción de ajustar texto de tu ciudad natal para crear algo que se vea
así en el que aún puedes ver todo el texto en ese campo. Simplemente lo enrolla en rosa nueva en lugar de poner todo ese texto en una sola línea . Entonces los casos de uso aquí, trabajando con campos de texto como resultados de encuestas o publicaciones en redes sociales caso de uso muy común. O si solo quieres forzar tu columna de tabla pivotante con para que permanezca fija, no importa cómo haya cambiado o ajustado tu salida de tabla. Entonces vamos a saltar a un cuaderno de trabajo pro tip y te mostraré cómo se ve esta opción. Está bien, así que si quieres seguir
adelante, abre tu libro de Excel Protect. Estoy en la tabla de contenidos y busco la sección de consejos de tabla pivote gris. Específicamente la columna Auto Fit con demo y seguir adelante y enlazar directamente para intentar. Ahora lo que tenemos aquí es un simple pivote. En los campos aquí, basados en datos de cata de vinos, tenemos información sobre vinos y bodegas individuales, país de origen y provincia. También tenemos los nombres del catador y esta descripción basada en texto de la cata, así
como su calificación de punto y un precio por botella de vino. Ahora bien, si agarramos este largo campo de descripción basado en
texto y lo metemos en nuestras etiquetas de rol después del nombre del catador, veamos qué sucede aquí Se estira la columna B
camino, salida, algo más allá de la ventana. Y ha hecho que sea muy difícil y incómodo trabajar con, como puedes ver, simplemente intentarincluso simplemente navegar por la vista aquí, cual es bastante molesto porque encaja a la descripción más larga de todo este campo. Así que mira lo que voy a hacer aquí. Voy a sacar la descripción, que va a encogerse la columna B de vuelta hacia abajo. Voy a entrar en herramientas de tabla dinámica, analizar y perforar mis opciones aquí y dentro de esa pestaña de diseño y formato. Esta casilla que dice anchos de columna de ajuste automático en la actualización generalmente se comprueba por defecto. Todo lo que tenemos que hacer es desmarcar esa casilla presionar OK, y luego ver lo que sucede cuando volvemos a meter la descripción. Ahí vamos ahora. columna B es una columna promedio con ella no la estiró para auto fit, y ahora tengo un par de opciones. Aiken Estirarlo yo mismo con eso me siento cómodo, pero ten en cuenta que sigue cortando mi texto. ¿ Quién es una descripción? Perot. Una actualización que a menudo me gusta hacer casos como este. Es seleccionar todo de la columna B cabeza a mi ciudad natal y activar esta opción de texto de ajuste en el dolor de
alineación y que básicamente simplemente extiende o envuelve el texto a múltiples filas en los
casos en que se hubiera cortado. Y ahora soy capaz de ver todo el texto en estas descripciones y personalizar exactamente qué ancho de ojos como la columna B dos b Así que ahí lo tienes. Punta sencilla pero eficaz. Impedir que sus campos de tabla dinámica se ajusten automáticamente. Esos anchos de columna
4. Diseños de esquemas y tabular: bien, Este siguiente pro tip es uno extremadamente importante para cualquier persona que trabaje con tablas pivote en Excel. Te mostraremos cómo podemos mostrar pivotes en diferentes tipos de diseños o formas de tabla, específicamente de contorno o forma tabular. Ahora, mayoría de las tablas pivote por defecto se mostrarán en lo que se llama forma compacta y eso agrupa todas
sus etiquetas de rol juntas o anida, um, en una sola columna. Entonces, desde la pestaña de la herramienta de tabla dinámica, el grupo de diseño, específicamente para ver un desplegable de diseño de informe, mostrando sus diferentes diseños de tabla u opciones de formulario. Y así es como se ve típicamente la forma compacta por defecto. Aquí tenemos una columna de valor, el precio promedio, y tenemos que remar etiquetas, país y un campo de provincia. Ahora el problema es que en forma compacta, estos dos campos se anidan juntos y ambos viven dentro de la columna A. Y eso significa que tenemos un encabezado de columna accesible para cosas como ordenar y filtrar. Y no podemos aplicar nuestras propias reglas de clasificación o filtrado a cada uno de esos dos campos individualmente, por lo que eso limita seriamente nuestra capacidad de rebanar y dados y explorar estas diferentes
etiquetas de rol . Y por esa razón exacta. Te recomiendo que a menudo utilizaras la segunda opción aquí. Forma esbozada. Una forma de contorno se ve muy, muy similar a la forma compacta. La única diferencia es que toma esas etiquetas en rollo y las rompe en sus propias columnas. Lo que significa que ahora tenemos acceso a encabezados de columna y etiquetas de columna adicionales, incluidas aquellas herramientas de clasificación y filtrado. La forma tan delineada es un formato fantástico para usar en cualquier momento que realmente necesites explorar o analizar datos de Irán, y luego también tienes forma tabular. Y en este caso, estoy mirando el diseño tabular con etiquetas de elementos repetidas y forma tabular, esencialmente formatos. Tu pivote, algo así como una mesa se deshace de esos espaciados extra. Rose y formatear reglas cosas así. Y con un poco de formato inteligente y unas cuantas opciones como repetir las etiquetas de ítem ocultando los sub totales, elegir no mostrar grandes totales, podemos terminar con algo que se vea así, que esencialmente se ve justo como una bonita tabla de datos de fuente limpia que podemos usar para análisis
posterior. Ahora, Una cosa a tener en cuenta, puedes cambiar tu comportamiento de tabla dinámica y ajustar tu predeterminado de forma compacta a algo como contorno o tabular. Te mostraré justo lo que se ve eso en un segundo,
pero de nuevo, pero de nuevo, para recapitular esos casos de uso común forma de esbozo. Gran manera de explorar sus datos ordenar y filtrar múltiples campos de
forma individual y tabular con etiquetas repetidas y sin totales generales o subtotales podría ser el enfoque perfecto para crear nuevos datos de origen que pueda empaquetar copia, pegar y utilizar para otros fines analíticos. Entonces con eso, saltemos a excel. Te voy a mostrar cómo se ve cada una de estas formas o diseños de mesa. Y entonces cómo podemos usar nuestro menú de opciones para cambiar el comportamiento por defecto. Muy bien, así que adelante y abre tu libro de trabajo pro tip y vamos a perforar en la demo de esquema y diseño
tabular en nuestra sección de consejos de tabla pivotante gris. Adelante y enlaza a esa hoja, y lo que estamos viendo aquí es una sencilla mesa pivotante construida encima de nuestros
datos de cata de vinos . Ahora bien, si has estado siguiendo el curso, esto debería parecer bastante familiar. Países, provincias, vinos, catadores y algunos valores como puntos y precio. Ahora te darás cuenta es que he jalado en dos campos en las etiquetas de rol aquí, país y provincia. Pero debido a que estamos en el diseño compacto por defecto, ambos campos han sido anidados juntos en la columna A y como solo tengo este desplegable, hace que
sea muy, muy difícil aplicar diferentes ordenamientos o filtrar reglas a cada campo de forma individual. Al igual que, por ejemplar, si quisiera mostrar solo los cinco países principales pero también ordenar mis provincias por cierta métrica, esos tipos de combinaciones o ajustes de clasificación de filtros individuales realmente no funcionan muy bien en este diseño compacto. Entonces voy a hacer es cabeza a tabla pivote herramientas diseñadas tam va a reportar diseño y
cambiarlo para mostrar un formulario de esquema. Todo lo que va a hacer todo lo demás va a permanecer igual excepto esos dos campos que habían sido anidados en la Columna A ahora se dividen en una columna de país y una columna de provincia. Y lo que es más importante, ahora tengo etiquetas asignadas a cada una de esas columnas, lo
que hará
mucho, mucho más fácil aplicar esas diferentes combinaciones de reglas de filtrado y clasificación. Entonces, en términos generales, utilizo forma esbozada casi exclusivamente. Muy raramente uso forma compacta por esa razón exacta. Ahora tomemos un minuto y hablemos de formato tabular. Entonces tenemos datos detrás de bambalinas o fuente. Los datos son bastante granulares, recorre todo el camino hasta el nivel individual del vino. Y digamos que queríamos enrollar que los datos se agregaron a un nivel más alto de granularidad como tal vez solo queremos estos cuatro campos, por ejemplo. Sólo nos importan los datos a nivel provincial en casos como esa tabla pivotante, igual que estamos haciendo aquí, puede hacer un excelente trabajo agregando esos números arriba. Por lo que estamos viendo la calificación general de puntos y el precio promedio general por provincia y por país. El problema es que
si copiara y agarrara los datos de este pivote, seguiría teniendo mucho trabajo manual por hacer para ponerlo en un formato agradable, limpio, limpio,
tabular para el análisis. Tengo este tipo de encabezado y sub total rose aquí. Tengo filas en blanco que tendría que rellenar, y aquí es donde realmente entra en juego la forma tabular. Entonces aquí en nuestra ciudad de diseño, pasemos de contorno a forma tabular, y lo que haremos es volver a ese menú y elegir repetir todas las etiquetas de elementos que llenen esos espacios en blanco. Y nos estamos acercando lo que necesitamos aquí. El único problema es que todavía tenemos estas rosas totales extra, que realmente no es algo bueno a la hora de producir un conjunto de datos crudos y
tabulares para su análisis. Entonces todo lo que necesitamos hacer sub totales y decir que no queremos mostrar sub totales y totales generales. No queremos un gran total. Tan apagado para filas y columnas. Y ahí tienes. A medida que se desplaza por. Esto parece un conjunto de datos agradable, limpio, sin formar, enmarañado, sin formar,
enmarañado,
tabular. Entonces lo que yo haría aquí es entonces tomar esto. Copia los datos y el control de pivote desplaza hacia abajo para agarrar toda la lista. Copiarlo, pegado estos valores en otro lugar. Y entonces estás a menudo corriendo. Se puede analizar estos datos, sin embargo lo considere conveniente. Por lo que conseguí una gran manera de especie de convertir un conjunto de datos muy granular en dos formatos diferentes o diferentes niveles de granularidad para diferentes análisis o propósitos diferentes. Ahora, lo
último que quiero mostrarte muy rápido aquí. Si quieres cambiar el comportamiento por defecto y decirle a tus pivotes que nunca tengan por defecto esa forma
compacta. En realidad puedes hacer eso en tus opciones de archivo de Excel Principal. Entonces opciones y va a tu pestaña de datos aquí y ver justo aquí en la parte superior, dice. Realizar cambios en el diseño predeterminado de las tablas pivote. Adelante y haga clic en ese botón de edición, y aquí es donde se puede decir, Muy bien, aquí está quiero tratar los sub totales, los totales generales y el diseño del informe. Esta es la clave aquí. Observe cómo dice show en forma compacta. Eso es probablemente lo que tu versión de Excel está mostrando también lo que puedes hacer. Cambia a otra opción aquí y recomiendo encarecidamente el formulario de esquema. Entonces ahí lo tienes. Puedes cambiar tu diseño, cambiar tu comportamiento predeterminado y crear el estilo de diseño o tabla que mejor se adapte a tus necesidades.
5. Cómo contar campos no numéricos: todo bien para este pro tip. Quiero compartir con ustedes cómo podemos usar tablas dinámicas para contar
campos no numéricos o basados en texto . Entonces cuando estás trabajando con un pivote que valora el dolor en tu lista de campos casi siempre se usa para campos numéricos cuantitativos. Tus números, estos aeródromos que puedes agregar, multiplica rastreados y resumirlos de diferentes tipos de formas. Pero aquí está la cosa. También se puede utilizar para analizar el conteo o la frecuencia de los campos no numéricos de los campos
basados en texto . Y debido a que realmente no se puede agregar o resumir texto basado en un verano en promedio o Max, lo que vamos a hacer es usar el recuento de estación de verano, que es el predeterminado para mostrar frecuencias para cada uno de esos valores basados en texto. Entonces permítanme mostrarles un ejemplo de lo que quiero decir con eso. Vamos a estar mirando aquí su base de datos de películas i m D B, y la idea es entender la distribución de nuestros datos de nuestros títulos. A partir de otros campos como el género, por ejemplo, tiramos el género tanto en las etiquetas de fila como en los valores como cuenta. Podemos producir una vista como esta y porque sabemos que nuestros datos de origen están en el nivel de título
individual de la película, lo que significa que cada fila representa un título diferente. Esto nos dice que hay 900 títulos que caen en el género de acción. Existen 362 títulos o rosa que caen en el género de aventura y así sucesivamente y así sucesivamente, y puedes seguir este mismo enfoque para cualquier campo no numérico como las clasificaciones. Por ejemplo, seguir ese mismo proceso pobre calificación tanto en etiquetas de lanzamiento como en valores como en cuenta. Y aquí podemos ver que ahí hay 91 títulos clasificados G, 553 títulos puntuados PG y así sucesivamente. Por lo que una gran manera de explorar nuestros datos desde un ángulo diferente para entender realmente la distribución de títulos basados en diferentes categorías aquí. Ahora, una cosa a tener en cuenta. Si metes un campo numérico en que valora el dolor y por defecto da cuenta así como esto, cuando esperarías que sea algo así como un poco que normalmente indica que hay un problema detrás de las escenas que o bien valora el aire formateado como texto. O es posible que tengas rosa en blanco o faltante que estén confundiendo a Excel. Por lo tanto, ten eso en cuenta Para recapitular estos casos de uso común. Número uno analizando el número de filas u observaciones en un conjunto de datos que caen en categorías o cubos
específicos, tal como estamos mostrando aquí. También muy útil para generar datos fuente para gráficos de estadísticas como Hissed A Gramos o
Gráficos Pareto , los
cuales están diseñados para trabajar con frecuencias y distribuciones. Entonces con eso, saltemos a excel y practiquemos contando algunos de nuestros no nuevos campos milagrosos. Está bien, así que si quieres seguir, estoy en la tabla de contenidos aquí, ve a tu demo de campos de texto de conteo en la sección de tabla dinámica gris. Adelante y enlaza a esa hoja y aquí tenemos una tabla dinámica de diseño de tabla básica que han creado en mi base de datos de películas I mdb. Y esto es algo así como esperarías ver bien. Tengo un
campo basado en texto, no numérico, no numérico,como país aquí en mis etiquetas de rol, y tengo un campo de valor, una columna numérica como ingresos brutos aquí, mis valores y mostrando esos ingresos como hijo. Ahora bien, ¿y si no me importara el total de ingresos que cada país producía? En realidad sólo quiero ver cuántas veces aparece cada país en mi conjunto de datos. Bueno, lo que puedo hacer es sacar los ingresos brutos, y todo lo que necesito hacer es agarrar otra instancia de país, dejarla caer en valores de los que va a por defecto contar, porque realmente no puedo resumirlo de otra manera. Y ahí tienes. Entonces ahora estos conteos básicamente me están diciendo el número de veces que este país apareció en la rosa de mis datos fuente. Por lo que Afganistán apareció una vez Australia 39 Francia 104. Y ahora, conociendo mis datos fuente porque mis datos fuente están a nivel de título, puedo interpretar este conteo de país como el número de títulos que fueron producidos por cada uno de estos países y la columna A. Así que mientras nos desplazamos a través, veo USA compone la gran mayoría, 2944 títulos o subieron en mi conjunto de datos, y puedo ajustarme para modificar este conteo igual que cualquier otro valor numérico. Ya sabes, puedo ordenar mis países descendiendo por ese conteo, ejemplo,
para entender realmente la distribución de títulos en base al del país y nuevamente se aplica el mismo proceso sin importar qué campo no numérico que estás mirando aquí. Entonces en lugar de países, podemos sacar ambos de esos y decir:
OK, OK, vamos a tirar idiomas En cambio, obtenemos una lista única de todos los idiomas aquí cuando lo meto en mis etiquetas de rol. Después agarramos la segunda instancia en valores como cuenta como cabría esperar. Aquí vemos la gran mayoría de títulos cayendo en el idioma inglés 3543 37
títulos franceses , 12 japoneses y así sucesivamente. Ahora, una última cosa que quiero mostrarles. Si sacamos el lenguaje y realmente agarramos el título y metimos el título en surgió así como sus valores. Lo que veremos aquí es uno por cada fila. Y recuerdas por qué ese es el caso? Porque esta es la granularidad de nuestros datos. Nuestros datos están a nivel de título, lo que significa que cada fila está representada de manera única por un título de película, y por eso vemos un recuento de uno en cada fila de esta tabla. Por lo que ahí tienes una gran herramienta para guardar en tu bolsillo trasero. Excelente manera de tipo de observar y analizar tus datos de una manera ligeramente diferente y entender la distribución contando estos campos o categorías no numéricos.
6. Fechas de agrupación: Este siguiente pro tip es popular porque es un tema muy común del que escucho todo el tiempo. ¿ Cuál es cómo agrupo mis fechas, o cómo manejo mi fecha? Los campos específicos dentro de mis tablas dinámicas ahora pivotan de forma predeterminada. Hacer incluir herramientas estándar para agrupar campos de fecha. Se pueden convertir días en meses o trimestres o años, etcétera. Ahora, a veces eres fechas se agruparán automáticamente, y a veces no lo harán. Y esto depende de tu configuración de excel. Te voy a mostrar cómo podemos dirigirnos a nuestro menú de opciones para ver si esa función de agrupación automática de
fechas está habilitada o no . Ahora, para esta demo, vamos a estar trabajando con fechas de estreno del título de película, que obviamente es a nivel de día, y les voy a mostrar dos enfoques. En primer lugar, vamos a mostrar cómo dedo del pie agrupa automáticamente esas fechas ajustando nuestra configuración. Y luego vamos a hacer clic derecho y perforar manualmente esas opciones de agrupación. Y en este caso, estamos agrupando fechas por meses, trimestres y años. Y cuando hacemos eso, terminamos con algo como esto. Notarás que hemos creado nuevos campos que solo existen en nuestro pivote, no los datos fuente titulados Years and Quarters. Y esos campos se pueden utilizar igual que cualquier otro. Se puede sacar cuartos. Puedes sacar la fecha de lanzamiento, que ahora realmente está capturando el nombre del mes y básicamente manipulándolos como cualquier otra dimensión de tu tabla. Pero si eres como yo, podrías encontrar este tipo de torpe y engorroso tipo de nidos todos juntos. Hace que sea muy difícil acceder a tu campo de fecha de lanzamiento original una vez que los hayas agrupado. Entonces, en realidad voy a mostrarte un enfoque alternativo también, que me parece un poco más flexible al crear en realidad algunos campos nuevos en nuestros
datos fuente a través de columnas calculadas usando algunas funciones simples de fecha y hora. Entonces los casos de uso aquí, independientemente del enfoque que tomes. Se trata de enrollar datos granulares como datos diarios con el fin de analizar tendencias o
patrones a un nivel superior, como mes o trimestre o año, o crear tablas de resumen de alto nivel o gráficos que se originan de fuentes más granulares datos para que abramos nuestro cuaderno de trabajo pro tip. Echemos un vistazo a estas fechas de estreno y exploremos algunas formas diferentes de agrupar o agregar esas fechas en diferentes niveles. Muy bien, Así que desde la tabla de contenidos, adelante y busca las fechas de agrupación Demo en la sección de consejos de tabla pivot gris y enlaza directamente a esa hoja. Y lo que tenemos aquí es una mesa pivote en blanco. Va a ser proveniente de nuestra base de datos de películas I m D B. Haber hecho que esos datos de origen sean accesibles en la siguiente pestaña. Agrupación fechas, paréntesis, datos. Así que conjunto de datos bastante simple aquí. Tan solo una muestra rápida de nuestra base de datos de películas I. M D B. Tenemos títulos y columna a las fechas de lanzamiento y columna B y luego algunas métricas
numéricas diferentes . Número total de revisiones y los ingresos brutos en la columna C y D Así de vuelta a nuestra pestaña de pivote. El tabulador de fechas de agrupación porque mi configuración de Excel se define para agrupar automáticamente fechas y pivotes. Mira lo que pasa cuando agarro esta columna de fecha de lanzamiento y la meto en Rose. Ve cómo lo creó Tipo de estalló en tres campos diferentes años, trimestres y fecha de lanzamiento, y es una especie de anidado los datos todos juntos en trimestres años y en este caso meses Y lo que notarás es que aunque vaya en mi pestaña de diseño, cambie esto a un formulario de esquema. Todavía es como que se derrumba y se enrolla una especie de forma incómoda que personalmente
no me gusta. Ahora podría sacar trimestres y liberar fecha y solo agregar datos por año, Por
ejemplo, ya
sabes, tirando de mis métricas numéricas como algunos de ingresos o lo que te voy a mostrar en tan solo un minuto. Podría definir una columna de año en mis datos de origen reales y tenerla accesible para mí así
como mis fechas de lanzamiento originales. Entonces voy a golpear Control Z para deshacer algunas veces todo el camino de vuelta a mi pivote original sin nada en mis áreas dolores ahora, dependiendo de tu configuración, puede
que no hayas visto esas fechas de estreno agrupar de la manera que lo hicieron cuando jalé en etiquetas de fila. Qué vamos a hacer para comprobar que es ir a nuestro menú de archivos perforando a nuestras opciones y tenía a las opciones de datos aquí ahora en la sección de opciones de datos justo aquí en la parte inferior, verás una casilla de verificación que dice desactivar agrupación automática de columnas de fecha y hora en tablas pivote . Por lo que en la mayoría de los casos esto se desmarcará, lo que significa que la agrupación automática está habilitada. Si no quieres que tus fechas se agrupen, solo sigue adelante y marca esa casilla y pulsa OK. En realidad prefiero mantener esa casilla marcada porque me gusta definir las agrupaciones por mi cuenta exactamente como las quiero. Entonces ahora si agarro esa fecha de lanzamiento y la meto en Rose, solo
se queda ahí como fecha de estreno en su forma original. No agrupados. Entonces a partir de aquí, si quisiéramos un grupo, estos datos, digamos hasta el nivel de año o mes podríamos hacer clic derecho y el acceso son herramientas de agrupación , que también puedes acceder en tu tabla dinámica. Herramientas de agrupación de pestañas analizadas aquí mismo grupo sentir Ambos te llevarán a este cuadro de
diálogo de agrupación , que básicamente te permite determinar. De acuerdo, aquí está el rango de fechas que me importan. Fechas de lanzamiento de 1923 2015 y seleccione campos individuales que desee incluir en su agrupación. Entonces, por ejemplo, días, meses, trimestres y años en prensa OK y se han creado tres columnas extra aquí para cada uno de esos niveles de
agrupación, y los está anidado aquí mismo dentro de la columna A. Ahora, nuevo, eso puede ser exactamente lo que quieres. Eso puede estar bien. Puedes usar cualquiera de esos campos individualmente, pero te voy a mostrar un enfoque que generalmente tiendo a tomar con más frecuencia. Entonces voy a controlar a Z para deshacer eso. Y lo que voy a hacer en realidad es navegar a mis datos fuente, que está en esta agrupación fechas, pestaña de
datos, e insertemos dos columnas aquí justo después de que Colin sea, y voy a nombrar una de ellas mes liberado, uno de ellos año de estreno. Y aquí solo podría usar funciones básicas de fecha y hora para hacer referencia a esa fecha de lanzamiento y categorizar un mes y un año. Entonces columna ver, sería un mes de B dos y año sería igual año ser a y que estos aire formateado tipo de tontería porque básicamente han heredado el formato de fecha de lanzamiento aquí. Entonces eso es correcto. Haga clic en dar formato a las celdas como prensa general. OK, ahí tienes. Entonces mes es 9 septiembre estrena boom 1920, DoubleClick Y ahora tenemos meses y años como una nueva columna aquí mismo en nuestro
conjunto de datos fuente . Y si volvemos a nuestras herramientas de tabla pivote pivot se pueden refrescar. Desde que hemos hecho un cambio dentro de los límites de nuestra tabla original, y ahora tenemos estos dos campos disponibles para nosotros. Entonces al igual que cualquier otro debilitar agarra mes, tire de él en las etiquetas de fila. ¿ Hay todas las fechas en enero y todas las fechas nos desplazamos hacia abajo en febrero, marzo, abril, etcétera? O podemos sacar esos campos individuales y decir: ¿
Sabes qué? Yo solo quiero que toe organice o agrupe mis datos por año. Echemos un vistazo a la suma total de los ingresos brutos en base al año de la fecha de lanzamiento aquí, así que prefiero este enfoque me da un poco más de flexibilidad ahora. Esos campos están realmente definidos en mis datos de origen si quiero usarlos en otro lugar también . Pero de nuevo, aquí no
hay bien ni mal, totalmente depende de ti. Dos formas diferentes de agrupar tus fechas usando tablas dinámicas de Excel
7. Activar varios filtros: está bien, hora de compartir uno de mis consejos pro de tabla pivot favoritos, aplicando múltiples filtros a un solo campo de tabla pivotante. Ahora, forma
predeterminada, puede aplicar filtros de etiqueta, que se basan en filtros de texto o de valor, que se basan en números a un solo campo dado en una tabla dinámica, pero no ambos al mismo tiempo. Entonces, en otras palabras, si tuvieras aplicado un filtro de etiquetas y luego agregara un filtro de valor, ese nuevo filtro de valor sobrescribiría o borraría el filtro de etiquetas existente. Entonces, para cambiar esto, lo que vamos a hacer es dirigirnos a nuestras opciones de mesa pivotante. Vamos a perforar en nuestra pestaña de totales y filtros y marcar esta casilla que dice, permitir múltiples filtros por campo. Ahora, como cabría esperar, lo que esto nos permitirá hacer es aplicar filtros que estén basados tanto en el texto como en
los atributos de valor al mismo tiempo. Entonces en este caso, lo que vamos a hacer es filtrar hacia abajo a títulos que terminan en el número dos, que es un filtro de etiqueta y también impulsó cierto volumen de ingresos, que es un filtro de valor. Entonces el caso de uso aquí es realmente justo en cualquier momento que quieras. Aplica filtros más complejos o personalizados a tus tablas dinámicas que incorporen tanto esas condiciones o criterios basados en el texto como en el valor. Por lo tanto, saltemos a excel y practiquemos, filtrando algunos campos basados tanto en filtros de etiqueta como de valor. Está bien. Ahora, si lo estás siguiendo, dirígete a tu libro de trabajo pro tip y ve a la demo de múltiples filtros de habilitación en la gran sección de consejos de tabla
dinámica. Y cuando nos vinculamos directamente, lo que encontrarás son algunos datos de título de película aquí. Tienes títulos y columna A. Tenemos los ingresos brutos que cada título producido se generan aquí en la columna B, y como puedes ver, estamos ordenados descendentes por ingresos brutos. Por lo que estas películas más taquilleras aquí en la cima incluyen Avatar, Titanic, Jurassic World, The Avengers, etcétera. Así que digamos que estamos explorando un poco estos datos, y queremos ver el rendimiento para nuestras Sequels específicamente para que simplemente pudiéramos perforar en su encabezado de columna. Aquí, en este caso, aplicaría un filtro de etiqueta, ya que es un filtro basado en texto y diríamos, Vamos a mostrar los títulos que terminan con un espacio y luego un dos derecha presiona OK, y ahí vamos, nuestros títulos han filtrado a Onley. Estos títulos que terminan en dos, que en la mayoría de los casos serían Secuelas, y eso es útil por sí mismo. Pero lo que quiero hacer, realidad, es producir una lista de filtro abajo basada en los ingresos brutos también. Por lo que no quiero mostrar todas las Sequels aquí. Yo solo quiero mostrar a las Sequels que, ya
sabes, en este caso, digamos, condujo más de 200 millones de dólares de ingresos por lo que debería producir ya sabes, estos 10 primeros artículos en este caso para que podamos volver a nuestra encabezado de columna y saltemos en filtros de
valor. Esta vez, digo, Vamos a filtrar los títulos que son mayores a donde los ingresos brutos son mayores a los 200 millones, que es un dos, seguido de ocho ceros. Riggio y presiona OK, y mira lo que pasó aquí. Nuestra etiqueta de título, Filtro para secuelas, ya
se ha ido, y si nos desplazamos hacia abajo o columna de ingresos brutos, um, sí termina justo por encima de los 200 millones para que el filtro de valor esté funcionando. Pero nuestro filtro de etiquetas ya se ha ido, y si nos fijamos en nuestro encabezado, lo
verás marcará casilla junto a los filtros de valor lo que nos dice que sí tenemos un
filtro mayor que aplicado. Pero ese cheque que estaba al lado de los filtros de etiqueta ahora se ha ido por completo. Ahora, lo que eso nos dice es que necesitamos habilitar múltiples filtros aquí para poder hacer lo que
necesitamos . Entonces vamos a perforar en nuestras opciones de tiempo de herramienta de tabla pivotante aquí abajo a la izquierda, vamos a entrar en nuestros totales y filtros Tab y justo aquí en el medio, en la sección de filtros, verás una casilla de verificación que dice, permitir múltiples filtros por campo. Adelante y dale un cheque a eso y pulsa OK. Y ahora, en teoría, todavía consiguió nuestro filtro de valor aplicado aquí. Por lo que ahora solo deberíamos poder volver atrás y aplicar ese filtro de etiqueta termina con para taladrar hacia abajo a solo son Sequels. Por lo que nuevamente termina con espacio para presionar. OK, y ahí lo tienes. Ahora nos quedan sólo esos 10 títulos que ambos terminan en el número para etiquetar filtro y condujo al menos 200 millones en ingresos nuestro filtro de valor y mirando el encabezado aquí
vemos esas marcas de verificación aplicadas a ambos tipos de filtros. Por lo que obviamente puedes usar esto de muchas, muchas maneras diferentes, usando cualquier combinación de texto y criterios basados en valores. Por lo tanto, mantén esa en tu bolsillo trasero. Gran manera de crear criterios de filtro más complejos o personalizados al habilitar múltiples filtros en tus pivotes.
8. Acumulación de valores: Quiero tomarme unos minutos para hablar de una mesa pivot pro tip que en realidad no
descubrí hasta hace muy poco. Y así es como usar pivotes para agrupar valores numéricos. Ahora, muchas personas están familiarizadas con agrupar campos de texto o fechas y tablas dinámicas. Pero lo que la mayoría de los usuarios no se dan cuenta es que también puedes agrupar campos numéricos en
compartimentos o cubos personalizados . Ahora, para hacer esto, lo que vamos a hacer en esta demo, vamos a meter un campo numérico en nuestras etiquetas de rol como este. En este caso, estamos mirando el punto de precio por botella de vino, y esto se siente un poco mal porque siempre nos enseñan, sabes, a tirar esos campos cuantitativos, numéricos hacia el valora dolor de nuestra lista de campos, no en filas o columnas. Pero en este caso, eso es exactamente lo que queremos. Y lo que vamos a hacer es hacer clic derecho en uno de los valores de esta columna y navegar a la opción de
grupo, igual que haríamos con una fecha o un campo de texto. Pero la diferencia es que este cuadro de diálogo que obtenemos es específico para tratar con valores que podamos determinar un valor inicial un valor final y el grupo o sido tamaño. Por lo que en este caso particular decían que nuestros precios del vino comienzan en $0. Quieres extenderlo a 1000. Queremos un grupo estos precios en cubos o cubetas de 200 dólares, y eso escupe algo que se ve como esto. Ahora tenga en cuenta que en lugar de mostrar cada punto de precio individual que se ha traducido estas agrupaciones y tirando en un campo como el recuento del nombre del vino, eso nos está diciendo que hay 48,383 vinos y se muestrean que tienen un precio de 0 a $99 ahí, 1282 que tienen un precio desde 100 toe 1 99 y luego todo el camino hacia abajo. En el otro extremo del espectro, tenemos tres vinos únicos que tienen un precio superior a los $1000 por botella. Ahora, si querías deshacer y volver a tus puntos de precio únicos originales, justo a la derecha, haz clic en esas agrupaciones y elige un nuevo grupo para volver a donde empezaste. Por lo que este tipo de agrupación de valores es una excelente manera de analizar cosas como la frecuencia de observaciones que caen en diferentes cubos. De hecho, es una forma perfecta de crear bins para usar como datos de origen para un gráfico HIST un gramo o Pareto, que están diseñados específicamente para visualizar distribuciones. Ahora para recapitular, estos casos de uso nuevamente analizando la frecuencia de contador de observaciones que caen en grupos o bins
específicos de valores, o creando tablas o gráficos de resumen de nivel superior a partir de fuentes de datos más granulares. Así que vamos a saltar a un cuaderno de trabajo pro tip. Abramos este pivote basado en nuestros datos de cata de vinos y practiquemos agrupar o agrupar los valores de precios. Está bien, así que si quieres seguir, abre tu libro de trabajo pro tip, dirígete a la tabla de contenidos y busca los valores de agrupación Demo en la sección de
Consejos de Tabla Pivot gris y sigue adelante y vincula directamente a esa hoja. Y lo que encontrarás aquí es un diseño básico de tabla pivotante. Está construido a partir de nuestros datos de cata de vinos, y sólo tenemos a campos a la vista en este momento. Consiguió variedades de vino y columna A. Esas son nuestras etiquetas de papel, y tenemos el precio promedio por botella en la columna B. Esos son valores, y esto es muy estándar tipo muy común de disposición de tabla pivot. Consiguió un campo basado en texto como etiquetas de rol. Tienes un campo numérico o cuantitativo como valores. Entonces desde aquí podríamos aplicar, ya
sabes, algunas agrupaciones basadas en texto, ya
sabes, tal vez quieras seleccionar todo con cabernet sauvignon en el título tipo de vino y crear un grupo que contenga sólo esos artículos. Ese es un tipo muy común de agrupación con tablas pivote, pero no vamos a hacer eso. Vamos a tomar un enfoque ligeramente diferente y en realidad vamos a meter nuestro
campo de valor en nuestras etiquetas de rol. Y vamos a tirar un campo de texto en este caso, el nombre del vino a nuestros valores. Por lo que vamos a una especie de volteado el guión aquí para crear una vista totalmente diferente de nuestros datos. Entonces, para hacer eso, saquemos la variedad de vino, y vamos a tomar ese campo de precios y sacarlo directamente de los valores a Rose. Y cuando hacemos eso, ahora
vemos una lista única de puntos de precio listados fila por fila. Y desde aquí, lo que podemos hacer es básicamente decir OK por cada uno de estos puntos de precio por $4 vientos, $5 vino, $6 vinos. Cuántas observaciones son rosa existen en nuestro conjunto de datos, y como nuestro conjunto de datos está a nivel individual del vino, puedo agarrar esta columna del nombre del vino, tirarla a los valores y representarla como un recuento. Esto me está diciendo que hay siete vinos que tienen un precio de 4 dólares la botella. Tenemos 16 con un precio de $5 y así sucesivamente y así sucesivamente a medida que nos desplazamos por esta tabla. Entonces esto es genial. Ya es una forma útil de entender la distribución de los precios del vino en la muestra, pero es un poco abrumador porque aquí tengo tantos puntos de precio diferentes. Para tratar de darle sentido a esto es un gran caso de uso para agrupar estos precios en cubos o cubos. Así que vamos a la derecha Haga clic puede seleccionar cualquiera de estos precios Grupo de queso y aquí recibió este cuadro de diálogo que es específico para agrupar campos numéricos. Entonces digamos que queremos iniciar nuestro cubo primer cubo a 0 dólares y en este caso conseguimos algunas líneas que van hasta el final tan alto, pero muy, muy pocas. Entonces terminemos en 1000 y la opción de compra, donde dice 100 ese es el tamaño de cada sido. Entonces, ¿queremos grupos de precios en incrementos de $10 o 100 o 1000? Empecemos con 100 a ver cómo se ve eso y simplemente presione. OK, entonces ¿qué hace eso es básicamente convertir esos puntos de precio individuales en estos cubos o cubos? Y desde aquí tenemos cuentas de nombres de vinos para que veas a la gran mayoría caer por debajo 100 dólares. Tenemos alrededor de 1200 casi 1300 que cayendo rango de 100 a 200 puntos y así sucesivamente así podemos tratar estos valores como cualquier cosa que podamos ordenar nuestros cubos. Podemos crear un gráfico pivotante basado en estas frecuencias,
copiarlo, copiarlo, pegarlas en una nueva hoja y crear un gráfico HIST, un gramo o Pareto. Pero la conclusión es que mediante el uso de estas herramientas de agrupación de valores fueron capaces de explorar y analizar este conjunto de datos desde un ángulo totalmente diferente o a través de un lente totalmente diferente, y realmente obtener una idea de cómo estos puntos de precio se distribuyen a lo largo de nuestro conjunto de datos. Por lo que ahí lo tienes curso rápido de crash sobre el uso de herramientas de agrupación de valores de tabla pivot
9. Añadir cálculos del valor: bien, hablemos de una de las herramientas de tabla dinámica más útiles y potentes que analice sus datos utilizando cálculos de valor Now. En pocas palabras, los cálculos de
valor permiten mostrar valores brutos subyacentes desde todo tipo de
ángulos diferentes . Entonces, en lugar de solo mostrar un estándar algunos o conteo de o promedio, puedes mostrar esos campos basados en valor de maneras muy interesantes, como el porcentaje de una columna o fila dada o el porcentaje de una categoría padre dada, puedes mostrar valores como la diferencia con o por ciento de diferencia con respecto a un elemento base dado. Entonces eso es genial para cosas como el mes, a lo largo de mes o año con año cambios. Puede calcular totales en ejecución, que muestra esos valores como un total acumulado dentro de un campo base dado. O puedes mostrar esos como rangos que al igual que
suena, no muestra el volumen real. Pero muestra el rango de ese volumen basado en bajo a alto u ocultar muchas reglas. Ahora ten en cuenta que esto no es una lista exhaustiva. También hay opciones adicionales, pero básicamente lo que esto nos va a permitir hacer es mostrar algo como esto, o estamos viendo totales de ingresos brutos por mes en este caso mes uno a nueve. Pero en lugar de simplemente detenernos ahí, realidad
podemos hacer clic derecho en esos totales de ingresos o crear copia duplicada y mostrar esos valores de diferentes maneras. Entonces aquí te dejamos algunas de las opciones. Hablamos sobre el porcentaje de grand, total por ciento de fila por ciento de diferencia de padres con respecto al total de correr y así sucesivamente y así sucesivamente . Por lo que aquí puedes seleccionar una de estas opciones, como ejecutar Total en,
por ejemplo, por ejemplo, mes de Campo de Lanzamiento basado en
seleccionado, que no es la columna A. Lo que eso hace es mostrar esos ingresos como un acumulado corriendo total mes a mes a mes. Ahora, un par de cosas rápidas para anotar aquí número uno un consejo rápido y recomendar arrastrar en múltiples copias del campo que estás buscando analizar para que puedas mantener un original como lo estamos haciendo aquí. Y en comparación con estas versiones de cálculo de valor de ese campo y segunda nota aquí, ten en cuenta que en realidad no estás cambiando los valores por sí mismos. El núcleo de los valores brutos subyacentes no están cambiando. Todo lo que estamos haciendo es cambiar la forma en que esos valores se muestran dentro de nuestra tabla pivotante y Onley dentro de su tabla pivotante. Casos de uso tan comunes aquí. Uno es analizar datos serios de tiempo como un total corrido por día, mes o año o tiempo calculando. Tendencias serias como mes a mes, año a año, trimestre a trimestre, etcétera. Segundo caso de uso común explorando la composición de tus valores en términos del
porcentaje de participación por cosas como categoría mes, año, etcétera. Entonces lo que vamos a hacer es saltar a excel y vamos a echar un vistazo a alguna práctica de
datos de medallas olímpicas analizando y explorando esos datos usando este tipo de cálculos de valor. Está bien, así que tenía tu cuaderno de trabajo pro tip. Estamos en la sección de consejos de tabla dinámica aquí, y vamos a perforar en el enlace de demostración de cálculos de valor directo a esa hoja. Y lo que estamos viendo aquí son datos de seis juegos olímpicos diferentes consiguieron el invierno 2006 a través de los juegos de verano 2016. Y en este caso, estamos viendo el conteo de medallas por medallas de oro, plata y bronce para Estados Unidos específicamente. Por lo que estos valores se ven en la columna B ¿hay tipo de sin calcular sólo el recuento básico de metales. A partir de cada uno de estos años, podemos ver que la U. S. 1 992 total de medallas en general durante este periodo. Pero ¿y si queremos potenciar este punto de vista un poco analizado? Estos metales cuentan en algunos tipos diferentes de formas. Tipo de agregar un poco más de contexto o aprender un poco más sobre lo que está pasando aquí. Bueno, la mejor manera de hacerlo es con valores calculados. Pero antes de que solo haga clic derecho y muestre valores y diferentes tipos de formas, que sobrescribirían estos valores originales sin calcular y la columna B. Lo que voy a hacer es simplemente dirigirme a mi lista de campos, agarrar ese campo de metal y voy a tirar en cuatro copias duplicadas más de esa columna. Entonces tengo conteo de metal contado mental 234 y cinco, todos los cuales actualmente está mostrando ese mismo conteo crudo. Ahora la primera pregunta que podría querer contestar es que conozco el US 1 992 medallas totales. Pero muéstrame cómo se descompone ese total para cada uno de estos seis Juegos Olímpicos. Y si quiero demostrar que como porcentaje o composición de los recuentos metálicos que pueden
hacer clic derecho en esta columna C de segunda generación y vamos a mostrar estos valores como el porcentaje del total de la columna. Ahora ves que la fila de totales grandes suman el 100% y ese 100% se desglosa por cada una de las etiquetas de
seis filas. Por lo que podemos ver que en 2008 Estados Unidos ganó el 31% de todos los metales que obtuvo en toda
esta muestra en comparación con 2006 donde sólo 14.84%. Y en lugar de mantener el encabezado algo que es muy confuso, como el recuento de metal,
también, también, realidad
puede simplemente hacer clic en la barra de fórmulas y darle a este un título personalizado como por ciento uh, columna en este caso. Y ahora para Colin D. Este recuento de metal tres dicen que queremos contar una historia similar, Pero en lugar de mostrar el porcentaje uno cada año, me gustaría clasificar estos años con base en el volumen de metales. Entonces para algo así, para simple como mostrar valores como rango y porque un gran número es algo bueno en este caso , queremos clasificar de mayor a menor, y nuestro campo base aquí, en el que estamos ranking se basa en los años prensa OK y el mismo tipo de historia sale a la superficie. 2008 fue el número uno clasificado Juegos Olímpicos en esta muestra para la U. S. Ganamos 309 medallas, y por el lado de vuelta, 2006 fue el recuento de medallas más bajo, que es el sexto año clasificado y misma historia aquí. Podemos cambiar nuestro encabezado a algo así como rango. ¿ Y ahora y si realmente no nos importan las medallas ganadas cada año individual? Todo lo que nos importa en este caso particular es el total y cómo ese total está cambiando con el tiempo. Bueno, para algo así, el cálculo total corriendo es una gran opción, por lo que puede mostrar estos valores ya que un total corrido también puede usar el total total total corrido, que apenas se suma al 100%. Vamos a seguir adelante y hacer justo el total llano corriendo en nuestro campo base de nuevo es nuestro papel. Las etiquetas son años. Presione OK, y ahora 2006 muestra las 48 medallas 2008. Agregamos 309 al 48 2010. Agregamos otros 89. Se puede ver cómo este recuento total de medallas apenas crece con el tiempo como este cono acumulativo. Entonces hay un cambio total rápido corriendo, ese encabezado corriendo total. Y como ejemplo final ahora, lo que realmente quiero entender es cómo el recuento de medallas ha cambiado año con año o, en este caso, juego
olímpico sobre los Juegos Olímpicos. Ya que es una brecha de dos años, y lo que podemos hacer aquí es usar algunas opciones diferentes. Podemos usar ya sea algo como la diferencia de o el porcentaje de diferencia de. Entonces en este caso, si te importan los porcentajes, podrías elegir esta opción para preocuparte por el recuento real de metales, tu cerveza. Podrías usar esta primera opción. Adelante y hagamos esto. Y ahora nuestro campo base, igual que siempre, son nuestras etiquetas de regla del año. Ahora tenemos otra opción aquí también, que es el ítem base, y esta es la base a partir de la cual estás calculando esa diferencia. Entonces en este caso, no siempre queremos calcular nuestra diferencia frente a un año en particular como 2006 o 2008. Siempre queremos calcular esta diferencia en base al elemento anterior en nuestras etiquetas de fila, por lo que vamos a seleccionar anterior aquí y presionar. OK, y ahora echa un vistazo a esto. Nos está diciendo que en 2008 Estados Unidos obtuvo 261 medallas más que en 2006 . Pero luego en 2010 ganaron 220 medallas, menos de 2008. Por lo que esto ahora está mostrando ese patrón de año con año diferencias en los recuentos de metales. Entonces sigamos adelante y cambiemos ese encabezado a diferencia de. Y ahí tienes. Por lo que aquí hay opciones adicionales, algunas más complicadas que cubrirán y otros cursos como la opción de índice. Pero esta es una gran demostración de cómo se pueden usar algunas de las opciones más comunes para analizar y explorar tus datos en una tabla dinámica de varias maneras interesantes diferentes.
10. Mostrar elementos vacíos: Tomemos unos minutos para hablar de cómo podemos personalizar los errores y las filas en blanco dentro de una tabla
dinámica. Ahora por defecto. Como era de esperar, los pivotes no mostrarán etiquetas de fila para los elementos donde no existan valores. No obstante, puedes forzar a excel a mostrar esos elementos, incluso en los casos en que no haya datos en tu tabla de origen. Entonces déjame mostrarte lo que quiero decir con eso. Aquí estamos viendo estos algunos de los títulos de películas cruzadas de ingresos brutos categorizados por países y géneros. Y tenemos casos como Australia, donde tenemos muchos títulos en nuestra muestra a través de muchos géneros diferentes. Acción aventura, animación, biografía, etcétera y otros países como Afganistán, Argentina o Aruba. Eso puede tener sólo un pequeño número de títulos en nuestra muestra que abarcan Onley uno o dos géneros como drama, crimen o acción si queremos el género. Colin columna B En este caso, para mostrar todos los géneros posibles para cada país. Lo que podríamos hacer es simplemente correcto. Haga clic en ese encabezado de columna en este género de caso. Navega a nuestra configuración de campo y marca esta casilla en la parte inferior de la lista que dice mostrar elementos sin datos y lo que eso hará es obligar a la tabla dinámica a mostrar el conjunto completo de géneros para cada país en el diseño de la tabla. Y desde aquí, lo que podemos hacer es navegar a nuestras opciones de diseño y formato y personalizar exactamente cómo se muestran estos valores
vacíos, así
como los posibles valores de error valores
vacíos,así
como los posibles valores de error. Ahora tal vez te estés preguntando por qué alguna vez realmente querrías hacer esto y tipo de crear Rose para datos que igual a cero de todos modos. Y no es muy común en absoluto. Pero un caso de uso es para mostrar todos estos elementos posibles dentro de su diseño de tabla, incluso esos espacios en blanco o ceros, que
pueda crear un diseño o plantilla consistente para otros fines o
caso de uso similar . A lo mejor esperas que todos estos elementos estén poblados, y si no lo son, quieres poder marcarlos o llamar la atención sobre esos valores faltantes. Otra opción aquí. Volver a formatear espacios en blanco o errores simplemente para limpiar informes o paneles de control orientados al usuario construidos con una tabla dinámica. Así que vamos a saltar a nuestro cuaderno de trabajo pro tip. Te voy a mostrar cómo podemos agregar estos elementos sin datos a una tabla dinámica y luego personalizar exactamente cómo se muestran. Muy bien, Así que si quieres seguir adelante, dirígete a tu tabla de contenidos, vamos a perforar en la demostración de artículos vacíos en nuestra sección de consejos de tabla pivotante gris. Adelante y enlaza directamente. Y al igual que
describimos, estamos viendo la base de datos de películas IMDB. Aquí obtuvo la suma de ingresos y parte de presupuesto y la columna C y D, desglosada por país y por género. Y al igual que habíamos descrito, tienes países como Australia, Canadá y Francia que han generado títulos a través de una serie de géneros diferentes y otros países como Colombia o Finlandia, donde tal vez sólo uno o dos géneros están representados aquí en nuestro conjunto de datos. Entonces en este caso, digamos que me gustaría incluir una fila para cada género posible en cada país. Para ello,
puedo hacer clic derecho en el encabezado de columna en este caso, ser uno para el género. Dirígete a mi configuración de lista de campos o ajustes de campo, y en la pestaña de diseño e impresión, todo lo que necesito hacer es marcar esta casilla que dice mostrar elementos sin prensa de datos, OK, y ahí vamos. Ahora todos los 17 géneros posibles están representados para cada país dentro de nuestro diseño de tabla . Y una vez que tengamos esto en su lugar, lo que podemos hacer es personalizar exactamente cómo son estas rosas en blanco,
Rose sin datos realmente se ven. Entonces para hacer eso, vamos a entrar en opciones de herramientas de tabla dinámica y aquí mismo en la pestaña de diseño y formato, tenemos que marcar casillas aquí,
una para personalizar cómo dar formato a los valores de error y otra para personalizar cómo dar formato a las celdas vacías . Entonces por defecto, esas celdas vacías aire formateadas como lo adivinaste, celdas
vacías. Pero podemos cambiar eso a un valor diferente. Si quisiéramos, podría usar texto como una A podría usar un valor como cero aquí, ya que, técnicamente, eso es preciso. Ya sabes, las películas de
comedia en Afganistán hicieron dr $0 en ingresos, pero también es un poco engañoso porque el hecho es que esos valores simplemente faltan en nuestro conjunto de datos. Entonces vamos a saltar atrás. Y creo que prefiero aquí la celda en blanco para esas celdas vacías. Adelante y pulsa OK, Y ahora qué pasaría si tuviéramos un campo calculado aquí en nuestra lista de valores también? Algo así como nuestro aliado, por ejemplo, que es simplemente ingresos brutos divididos por presupuesto. En este caso, eso debería ser un porcentaje. Entonces cambiemos la configuración del campo de valor. Trump Cambiemos el formato numérico. Disculpe, 2%. Aquí vamos. Y ahora, debido a que este cálculo se está ejecutando contra cada fila de nuestro pivote, incluyendo rose con denominadores en blanco, obtenemos aquí todos estos mensajes div zero. Um, y este es el comportamiento por defecto. Lo que podemos hacer es tener de nuevo en nuestras opciones y personalizar cómo se ven
esos mensajes de error . Entonces es marcar esa casilla y podemos mostrarlos como un espacio en blanco, como así que creo que en realidad prefiero. O, si quieres, puedes personalizar el valor aquí algo así como una A Y otra vez, este es un caso donde no reemplazaría esos mensajes de error por un cero, porque eso hará cosas como sesgar cualquier promedios te podría tomar, o cualquier cálculo que pudiera hacer en contra de esto son Y Field. Y cuenta una especie de ah, historia
engañosa porque son por qué el retorno de la inversión no fue del 0%. Para estos géneros, simplemente no existía. Entonces volvamos a las opciones en realidad prefieren usar espacios en blanco para ambos, que todo
lo que veamos son las carreteras que están pobladas con datos de nuestra tabla de fuentes. Por lo que ahí lo tenemos mostrando elementos en su tabla pivotante con datos vacíos. No es la punta de mesa pivotante más común en el mundo, sino una que podría ser de gran ayuda cuando lo necesites.
11. Configuración de slicers y líneas de tiempo: Quiero tomarme unos minutos y hablar de una de mis características favoritas de tabla pivotante, agregando filtros visuales con rebanadoras y líneas de tiempo. Ahora estos suenan como herramientas muy lujosas, complejas, pero en realidad son bastante simples. rebanadoras son básicamente solo versiones interactivas de un filtro, y las líneas de tiempo son solo rebanadoras que funcionan con fechas. Entonces es un simple es que insertar? Estos son muy sencillos también. Puedes soltar hombres directamente de la lista de campos haciendo clic derecho en un campo o columna dado y eligiendo en una rebanadora. O bien, si estás mirando un campo específico de fecha, agrega como línea de tiempo. Y cuando agregue ese rebanador en, creará un nuevo objeto de libro de trabajo que contenga una lista de posibles selecciones de usuario. Y esas selecciones se atarán dinámicamente y filtrarán la propia tabla pivotante al igual que un filtro regular en una celda desplegable. Ahora, por defecto rebanadoras, Onley
interactuará con la tabla pivotante a partir de la cual fueron creadas. Pero dicho eso, si tienes múltiples tablas pivote generadas a partir de los mismos datos de origen, puedes usar las opciones de conexión de informes desde tu menú Herramientas de Slicer para esencialmente atar un solo rebanador a múltiples tablas pivote y en la demo son a punto de sumergirse en. Vamos a hacer justamente esto. Vamos a controlar a un atleta, pivote y un pivote de evento usando un solo conjunto de cortadores y líneas de tiempo. Ahora recuerda, cada uno de estos pivotes necesita compartir los mismos datos de origen o estar relacionado de alguna manera. No se puede simplemente tratar de controlar dos tablas no relacionadas usando una sola rebanadora. Ahora nota final aquí por defecto, las selecciones o los ítems que ves en una rebanadora, indicarán visualmente casos en los que no se disponga de datos. Entonces si tuvieras una rebanadora para temporada y una rebanadora para deporte y seleccionaras el verano como la temporada, no
querrías mostrar todas las opciones de deporte invernal en esa segunda rebanadora porque esas no generarán valores. En la mayoría de los casos, esos serán grandes fuera por defecto o en algunos casos ni siquiera visibles en la rebanadora. Y esa es sin duda una característica útil. Pero una cosa a tener en cuenta es que puede ralentizar el rendimiento si estás lidiando con
unas tablas
muy, muy grandes o complejas con muchas interacciones entre muchas rebanadoras Ahora, casos de uso
común aquí para uno, agregando filtros visuales fáciles de usar a informes o paneles que construya con tablas dinámicas y gráficos dinámicos. Y segundo, usar rebanadoras para indicar clara y visualmente exactamente cómo se estaba filtrando una tabla. Entonces con eso, saltemos a excel y practiquemos, filtrando algunos datos usando estas rebanadoras y líneas de tiempo. Está bien, así que si quieres seguir
adelante, abre tu cuaderno de trabajo pro tip y vamos a dirigirnos a la demo de rebanadoras y líneas de tiempo en la sección de consejos de tabla pivot gris. Así que adelante y salta directamente a esa pestaña. Cortadoras y líneas de tiempo y lo que verás aquí son para pivotar. Las mesas no están en su lugar. una de las izquierdas se le llama atletas y a la que la derecha se le llama eventos y en ambos casos apenas estaban mirando el conteo de medallas de oro, plata y bronce ganadas ya sea por evento o por atleta individual. Y hemos creado un poco de espacio por encima de ambas tablas para que esencialmente podamos crear un panel de control con rebanadoras y líneas de tiempo para filtrar dinámicamente las tablas de abajo. Por lo que es casi como crear un mini pequeño tipo de tablero aquí. Entonces empecemos con nuestro tiempo atleta cabeza a nuestra lista de campo, y la primera rebanadora que me gustaría insertar es una simple para temporada solo dos opciones verano o invierno click derecho agregar como rebanadora y ahí vamos. Se inserta. Este nuevo objeto de hoja de cálculo que podría tratarse igual que cualquier otro objeto podría contener a Ault . Ajustarlo a tu cuadrícula si quieres, puedes redimensionarlo, arrastrando así como así, y ver qué pasa mientras hago diferentes selecciones a esta tabla de pivote de rebanadora a partir de la cual se creó. Filtros al igual que lo hubiera sido esto en filtro normal a diferencia de una rebanadora. Entonces cuando tengo seleccionado el verano, veo a los mejores ganadores de metal para los deportes olímpicos de verano. Michael Phelps y Ryan encerraron a ambos nadadores de Estados Unidos. En este caso, lo mismo va para el invierno. Ahora vemos en Lee los atletas de invierno en el pivote, así que sigamos adelante y sin filtrar. Volvamos a su primer pivote. Quiero agregar una segunda rebanadora ahora para deportes, así que haga clic derecho Añadir una rebanadora, arrástrela en su lugar. Y una cosa a destacar aquí es que en este caso tengo una lista muy alta y larga tipo de empacada en un pequeño pedazo de bienes raíces. Entonces lo que podemos hacer es cambiar las herramientas de rebanadora para ajustar cosas como esta
disposición de alineación de estilo y el número de botones. Entonces en este caso, hagamos este color diferente para especie de diferenciarlo de nuestra rebanadora de temporada. Vamos a arrastrarlo un poco aquí y en lugar de una sola columna de botones, hagamos de esto algo así como tres columnas que simplemente lo hacen un poco más legible, y hace que la barra de desplazamiento sea un poco más fácil de interactuar con Here. Ahora, echa un vistazo a esto. Si selecciono el verano de mi rebanadora de temporada y me desplaza hacia abajo en la parte inferior de mi rebanadora deportiva , verás estas grandes opciones de salida Esquí alpino en bobsleigh y notarás que por ninguna coincidencia, esas son los deportes de invierno. Por lo que sobresale identificar visualmente las opciones inválidas aquí. Porque si fuéramos a seleccionar hockey sobre hielo, por ejemplo, anotar que no quedan caminos en el pivote porque ningún deportista compitió en hockey sobre hielo durante una Olimpiada de Verano, lo que podemos seguir adelante y sin filtrar. Eso y una cosa que podemos hacer para tipo de personalizar cómo funciona esto es seleccionar el rebanador entra en nuestras herramientas de rebanadora y ajustes de rebanadora aquí a la izquierda y ver esta casilla de verificación. Estamos indicando visualmente elementos sin datos. Ese fue el estilo desvanecido que acabamos de ver. Otra opción que tiende a funcionar realmente bien es ocultar elementos por completo que no tener datos . Entonces si marcamos esa casilla en su lugar y presionamos OK ahora, esos deportes de invierno ni siquiera aparecen dentro de esta lista. Lo mismo si hacemos clic en invierno. Ahora vemos en Lee los deportes de invierno y las opciones de verano no existen. Por lo que esta es una buena opción de enfoque de estilo
limpio y agradable para usar. Pero nuevamente recuerda que puede ralentizar las cosas Si estás trabajando con mesas muy grandes o complejas. Y otra cosa a tener en cuenta aquí por defecto, tienes activada la selección única, lo que significa que a medida que cambias tu selección, solo
estás tratando con un elemento o selección en particular fuera de tiempo. Consulta esta casilla con las marcas de verificación que habilita multi select para que pueda ver a los atletas que compitieron en esquí alpino o biatlón o bobsleigh, y eso depende de ti, dependiendo de los datos. Si desea o no habilitar esta opción multi select este caso. Mantengámoslo como soltero. Seleccione por ahora. Voy a seguir adelante y despejar esto, y vamos a hacer un ejemplo más aquí de nuestro atleta. Pivot de vuelta a nuestra lista de campo. Esta vez quiero trabajar con el campo año cuando hago clic derecho, nota que ahora este último anuncio de ítem como línea de tiempo está finalmente activo. Y no fue por otros dos casos. Temporada en el deporte. Si hacemos clic, agregamos como línea de tiempo, es crea otro objeto como rebanador excepto con un tipo diferente de aspecto y tacto. Porque esta es una línea de tiempo diseñada específicamente para trabajar con fechas, un par de cosas que llamar aquí es posible que veas que es una especie de enrollar las cosas de una manera divertida. Sólo tenemos datos por años, así que realmente no nos importan los meses aquí en absoluto. Por lo que puedes cambiar este pequeño desplegable dos años, y podemos entrar en herramientas de línea de tiempo, que se ven muy similares a las herramientas de rebanadora. Y podemos hacer cosas como ajustar el estilo, tal vez querer que esto sea verde y luego interactuar con esta línea de tiempo. Es un simple está haciendo click. Arrastreando. Puedes extender las asas puedes hacer clic con el dedo del pie Mira los años individuales de Onley, y notarás que al igual que las otras versiones,
las rebanadoras, la tabla pivotante filtra dinámicamente. Entonces, esencialmente, lo que hemos hecho es crear un conjunto de controles visuales que los usuarios puedan utilizar para explorar cualquier combinación
diferente de datos de este pivote, basado en la temporada, el deporte y un marco de tiempo específico. Podrás mirar a los atletas de verano, por ejemplo, que compitieron en tiro con arco y solo de 2014 a 2016 e instantáneamente pudieron
perforar nuestra mesa de pivote para mostrar esa combinación exacta de Rose. Ahora, lo último que quiero mostrarles es esta opción de conexiones de reporte. Entonces eso está sin filtrar. Cada una de estas rebanadoras y líneas de tiempo existentes y lo que vamos a hacer una por una, seleccione la rebanadora y dentro de nuestras herramientas de rebanadora. Haga clic en esta opción de conexión de informe. Ahora esto me muestra cualquier pivote válido en la hoja de trabajo que pueda conectar este rebanador, también. Y como puedes ver, tienes una casilla de verificación junto a deportistas y una casilla de verificación en blanco junto a eventos, que es este segundo pivote justo aquí. Todo lo que tenemos que hacer es marcar esa casilla presionar OK y ahora ver qué pasa? Seleccionamos el verano y tanto los atletas como los eventos filtran en consecuencia. Lo mismo con el invierno. Y básicamente acabamos de atar este rebanador a estos dos pivotes. Vamos a hacer exactamente lo mismo para el deporte. Denunciar conexiones apretadas a eventos también. Y la línea de tiempo, línea de tiempo, herramientas, conexiones de
informes. Sí, eso es lo primero. Está bien. Y ahora, igual que antes, puedes filtrar en cualquier combinación de valores. Y ahora ambos campos. Ambos pivotes se actualizarán para reflejar esas selecciones. Por lo que son hace rebanadoras y líneas de tiempo. Gran manera de agregar filtros visuales y herramientas fáciles de usar a tus tablas dinámicas de excel.
12. Formato condicional: De acuerdo, hablemos de cómo podemos darle vida a nuestras tablas pivote con el formato condicional. Ahora se trata de un consejo de cuatro estrellas, bastante avanzado, y eso es sólo porque aquí hay algunos matices que hacen que el formato condicional con tablas
pivote sea un poco más engañoso que el formato condicional con valores de celda estándar. Entonces vamos a empezar por agregar reglas de formato condicional. Esas mismas viejas reglas familiares de las barras de datos de la ciudad natal escalas de color. Icahn sets, etcétera para resaltar patrones o tendencias en la propia tabla de pivote. Algo así. En este caso, hemos agregado formato para visualizar patrones en precios medios y puntuaciones puntuales promedio para diferentes vinos para un determinado país filtrado, en este caso, Canadá. Ahora, a diferencia del formato tradicional de celdas, en realidad
podemos especificar algunas opciones de cómo reacciona el formato de tabla dinámica a los cambios en el diseño de la tabla. Entonces, en otras palabras, estas opciones aquí nos permiten determinar el alcance del formato que hemos aplicado, pueda ampliar ese formato a un rango seleccionado de celdas a todas las celdas para esa
métrica dada en este caso precio
promedio o a todas las celdas para esa métrica dada, y esas etiquetas específicas de rol, que es nombre del vino en este caso Ahora, términos generales, prefiero esa segunda opción todas las celdas que muestren estos valores particulares. Y lo que eso va a hacer es permitir que ese formato se pegue. Incluso si sacamos vino, nombre fuera de las etiquetas de fila y un campo diferente como la variedad de vino en, te
voy a mostrar exactamente cómo se ve eso. Asume que saltamos a excel pero rápido resumen de los casos de uso común, utilizando escalas de color para llamar la atención sobre patrones o tendencias en los datos y aplicando reglas de
formato en general que se pegan independientemente de cómo cambie el diseño de la tabla dinámica. Entonces con eso, saltemos a excel y practiquemos, aplicando algunos formatos condicionales personalizados a nuestra tabla pivot. Está bien, así que si quieres seguir adelante, abre tu libro de trabajo pro tip y dirígete a la demo de formato condicional en la sección de consejos de Pivot
Table gris , y aquí encontrarás un pivote relacionado con el vino. Estamos viendo nombres de viento en las etiquetas de Roe y para cada uno de estos vinos, están rastreando el precio promedio y la calificación de puntos promedio. Y, como se puede ver, aquí se filtraron Onley dedo del pie Mira las líneas canadienses específicamente. Ahora la idea es convertir esto de una vieja mesa llana en una herramienta visual que podamos utilizar para identificar las mejores combinaciones de precio y calificación de puntos. Es decir, quiero crear algún tipo de herramienta de seguimiento que me ayude a llamar la atención sobre aquellos vinos que son asequibles y muy valorados. Y para ello, vamos a aplicar algunas reglas de formato condicional, como escalas de color y barras de datos. Por lo tanto, empecemos con nuestra columna de precio medio aquí. En términos generales, sugeriría seleccionar aquí la primera fila y usar algo como el atajo de
flecha de desplazamiento de control hacia abajo para resaltar todos los valles con la columna NYT. Pero en este caso, por el bien de la demostración, sólo
voy a seleccionar un trozo de datos aquí alrededor de 10 filas más o menos. Ahora, mira lo que sucede cuando aplico un formato condicional en este caso, una escala de colores. Y como los precios bajos son buenos en este caso, quiero que esos precios bajos sean verdes. Voy a elegir la segunda opción aquí rojo,
amarillo, verde, verde, y cuando haga clic, notarás que aparece este pequeño pop up justo donde normalmente veríamos algo así como las opciones de auto fill y Esta es un menú especial de formato de tabla dinámica que nos permite determinar el alcance de esta regla de formato. Por lo que por defecto está configurado para ser celdas seleccionadas que yo había arrastrado. Pero tengo otras dos opciones aquí también. Cualquier celda que muestre valores de precio promedio, independientemente de las etiquetas de rol, o cualquier celda que muestre valores de precio promedio específicamente para nombres de vino. Entonces para mostrarte un ejemplo, elijamos esa tercera opción. puede ver que se lo aplica a cada venta en la columna, pesar de que sólo había seleccionado unas 10 u 11 celdas. Pero mira lo que pasa ahora si sacamos mi nombre. Pull variedad de vino en ese formato se pierde porque habíamos determinado el
formato Scope toe Onley cuando estamos mirando tanto el precio promedio como el nombre del viento específicamente. Así que sigamos adelante y cambiemos de nuevo la variedad. ¿ Por qué el nombre de nuevo y ahí está nuestro formato de nuevo. Ahora ya no podemos llegar a ese mismo menú pop up en este momento, pero podemos acceder a esas mismas herramientas pero rumbo al formato condicional administrar reglas, y podemos editar esta escala de colores que acabamos de crear, y verás esos mismos tres opciones justo aquí en la parte superior de nuestra lista. Entonces, en lugar de esta tercera opción aquí, vamos a seleccionar todas las celdas que muestren valores de precio promedio de nuevo, independientemente de las etiquetas de fila o columna presione O'Kane. Aplicar esa regla. Como podemos ver, nada cambia. Pero ahora mira lo que pasa. Pero ¿por qué nombrar variedad de vino en y revisarlo los palos de formato, pesar de que intercambiamos en diferentes etiquetas de rol. Entonces, en términos generales, ese es el alcance que me gusta aplicar cuando estoy jugando con formatos en tablas pivote. Y sólo volvamos a revertir eso que estamos mirando. ¿ Por qué los nombres otra vez? Ahora centrémonos en esta columna de calificación de puntos aquí. Entonces lo mismo puedo seleccionar una celda. Puedo seleccionar múltiples celdas. Toda la columna. Sea lo que sea que elijas. Este caso, quiero barras de datos. De acuerdo, Entonces en lugar de una opción estándar de barra de datos, aquí
hay algunos otros valores. Otras opciones que me gustaría personalizar. Entonces en realidad voy a entrar en más reglas en la parte inferior de ese menú, y aquí de nuevo, podemos acceder a estas opciones de alcance aquí. Vamos a elegir la segunda opción, y en lugar de mostrar la calificación de puntos real como texto, vamos a Onley mostrar las barras de datos marcando esa casilla. Y como aquí hay un rango muy estrecho de puntajes, realmente no
es nada menor de 75 u 80 y nada por encima de 100. Establezcamos un número mínimo de 75. Tan sólo dos más claramente. Ve las diferencias entre estas barras ahora duran pero no menos importante, va a darle una especie de color azul oscuro. Esto dependía totalmente de ti y presiona OK, y ahí lo tienes. Se aplica esas barras de datos personalizadas todo el camino a través de la columna, tal y como cabría esperar. Por lo que ahora el propósito de nuestra herramienta está empezando a especie de emerger bien. Estamos buscando líneas de precio bajo en la parte superior de la lista que también tengan una calificación de punto alta en
función del tamaño de esta barra. Entonces vemos algunas opciones bastante buenas aquí. Grandes opciones ahí. Buena aquí. Una última cosa que podemos hacer para realmente llevar esto a la meta es aplicar. Una regla más a esta columna de calificación de punto que va al formato condicional resalta las reglas de
celda. Y digamos básicamente cualquiera de estas celdas que sean mayores a 90 puntos démosle un relleno
verde con un texto verde. Presione Aceptar
y, a continuación, haga clic en el elemento pop up y aplíquelo a todas las celdas que muestren valores promedio de calificación de puntos y verifique. Eso acaba de destacar aquí cualquiera de estas células con una calificación de puntos de 91 o mayor, por lo que podemos ver que estos vinos canadienses obtienen algunos vinos realmente buenos de alta calificación por puntos de
precio muy bajo . Sólo 17 dólares en estos casos, 18 dólares en estos. Y ahora, porque hemos construido esta herramienta y porque aplicamos estas reglas de formato a unatabla dinámica y
flexible, tabla dinámica y
flexible, significa que podemos hacer algo como esto en lugar de mirar los vinos canadienses. A lo mejor quieres mirar las líneas italianas. Presione OK. Todas nuestras mismas reglas de formato se aplican automáticamente, lo cual es increíble. Por lo que ahora pasemos por estos vinos italianos ya que no podemos ver nada por encima de los 90. Y hasta que lleguemos a Aquí vamos. Entonces aquí está la primera carretera que fue abanderada como un vino de 91 puntos. Se trata de un Chianti Classico 2010 por $12 así que básicamente acabamos de crear una pequeña y agradable herramienta que nos permite identificar estas grandes oportunidades para obtener líneas de alta calificación por un precio muy asequible. Y ahí lo tienes trayendo vida tus pivotes dedo del pie usando formatos condicionales
13. Eliminar y volver a recuperar los datos de la fuente: todo bien. Este siguiente pro tip es increíblemente importante para cualquier persona que trabaje con tablas pivote y excel . Vamos a hablar sobre el efectivo de la tabla pivot y específicamente cómo ese efectivo se puede usar
tanto para eliminar como para revivir los datos de origen de la tabla dinámica. Ahora, lo que la mayoría de los usuarios no se dan cuenta es que cada vez que insertas una nueva tabla pivot, también se genera
un efectivo pivot. Por lo tanto, piense en ese efectivo pivot como esencialmente una copia comprimida y
duplicada de sus datos de origen que vive fuera de la vista. Detrás de las escenas sobre este efectivo es increíblemente importante porque lo que te permite hacer es realmente eliminar tus datos originales de origen sin impactar realmente el propio pivote. Y eso suena a locura. En realidad puedes eliminar el rango de tabla o celdas que utilizaste para crear un pivote y seguir usando ese pivote igual que lo harías normalmente. Podrías modificar tus diseños de tabla, tus etiquetas de fila y columna en rebanadoras y tablas dinámicas, todo porque ahora estás trabajando con los datos en efectivo. Y hay un par de razones por las que podría querer hacer esto, una de las cuales es reducir significativamente los tamaños de los archivos, especialmente si se trata de tablas grandes que no necesitan actualizarse o actualizarse con tiempo. Por lo que para que esto funcione, necesitamos ajustar algunos ajustes muy específicos. Yo diría que tenemos una tabla de pivote básica como esta para esta demo. Vamos a estar mirando los salarios de San Francisco. Por lo que estamos viendo el salario base promedio para 2011 2012 y el total general. Y si perforamos nuestra tabla de pivote, las herramientas son opciones. Menú. Mira la pestaña de datos. Aquí verás tres opciones diferentes en el menú de datos de la tabla dinámica, y queremos revisar aquí las 1 ª 2 casillas. El 1er 1 guardar datos fuente con archivos asegura que ese efectivo de tabla dinámica se guarde con su libro de trabajo. En segundo lugar, habilitar mostrar detalles. Esta es básicamente tu línea de vida en caso de que necesites esos datos de origen de vuelta porque lo te
permite hacer es hacer doble clic en cualquier celda sin filtrar, gran total en el pivote como esta, y recrea toda la tabla de fuentes desde cero de dinero en efectivo en una hoja de trabajo totalmente nueva. Te voy a mostrar exactamente cómo se ve esto. Un excel, pero obviamente muy, muy potente, muy útil herramienta a tener en cuenta. Ahora, respecta a los casos de uso común, hay dos que vienen a la mente primero, como mencioné, eliminar datos de origen estáticos para reducir el tamaño de su archivo o aumentar su velocidad de procesamiento. Y segundo, es una gran manera de limitar la accesibilidad con el fin de evitar que los usuarios realmente accedan
o cambien los propios datos sin procesar a la vez que les permite explorar o analizar esos datos a través de la interfaz de tabla dinámica. Así que vamos a saltar a un cuaderno de trabajo pro tip va a mostrarte exactamente cómo
funciona este efectivo de mesa pivot . Está bien, así que si tienes ganas de seguir, abre tu cuaderno de trabajo pro tip, dirígete al contenido de tu tabla y vamos a perforar justo en la
demo de eliminación y reactivación de datos que obligó a nuestra demo en una tabla pivotante gris sección de consejos Haga clic en enlace. Y lo que encontrarás es una tabla de tablas de datos llamada salarios SANFRAN. Tenemos nombres de empleados por año, su título laboral y dos métricas con las que estamos trabajando aquí pago base y pago de horas extras. Y si solo usara el atajo de flecha de control hacia abajo para saltar al fondo, pude ver que tenemos poco más de 16,000 filas u observaciones de datos aquí 16,085. Ahora mantén ese número en el fondo tu mente porque en realidad vamos a volver a ese número en tan solo un poco. Entonces no es una gran cantidad de datos, pero es considerable a suficiente para especie de guardar algún tamaño de archivo si fuera capaz de aclarar esto. Entonces la idea aquí es crear un pivote basado en los datos de origen y luego deshacernos de la tabla en sí para que solo podamos trabajar desde efectivo y reducir el tamaño de nuestro archivo. Entonces con cualquier celda aquí en la tabla seleccionada va a insertar Tibbett, caer en una nueva hoja de trabajo y vamos a nombrarle algo significativo. Doble click. A lo mejor es amigo de la arena. Dáselo. Ahí vamos. Solo vamos a crear una vista básica aquí, pero un año en filtros, tal vez los títulos de empleo en Rose base pagan en valores. Y mostrémoslos como promedio aquí se ve bien, y, ah, cuando filtramos a sólo 2012 bien, así que tengo un buen punto de partida aquí. Diseño básico de tabla pivote. No te preocupes demasiado por los detalles. Lo que realmente necesitamos para asegurarnos es que nuestra configuración se especifique de una manera que nos
permita eliminar o eliminar esa tabla fuente real. Por lo tanto, vamos a las herramientas de tabla pivote. Opciones perforan en la pestaña de datos, y estas tres casillas de verificación son las que nos importan en este momento. número uno guardó los datos de origen con archivo Active esa casilla porque, sí, queremos que el efectivo se guarde con este número de libro de trabajo para habilitar mostrar detalles. Marque esa casilla porque, sí, esa es nuestra línea de vida en caso de que necesitemos que nuestra fuente vuelva a declarar. Y luego este tercer cuadro actualiza los datos al abrir el archivo que cuando no es necesario
comprobar porque refrescar los datos no será posible una vez que eliminemos esta tabla fuente porque Excel no va a poder encontrar la tabla o rango de celdas de las que se creó este
pivote. Por lo que no tendrá nada que refrescar. De hecho, si has intentado actualizar algunos de los otros pivotes a lo largo de este curso, posible
que hayas obtenido un error de referencia no válido. Y eso es exactamente lo que ves aquí porque lo que he hecho es despojar todos los
datos de origen para reducir el tamaño del archivo para este libro de trabajo del proyecto. Entonces sigamos adelante y presionemos OK. Aquí. Los envios se ven bien. Ahora mira esto. Acude a nuestros datos de origen reales. Podemos hacer clic derecho en la pestaña y eliminar toda la pestaña que contiene toda esa tabla de datos. Volvemos a saltar a San Fran. Pivot, échale un vistazo. A lo mejor no queremos 2012. Datos pueden querer 2011. Boom. Se ve bien. Pongamos en el pago de horas extras también. Vamos a verlo como un promedio. No un hijo. Se ve bien. Y entonces, hey, en lugar de títulos de empleo, es polos en filtros y en realidad traer el nombre de los empleados de nuestra lista de campo. Ahí vamos. Entonces como puedes ver, somos capaces de modificar y jugar solo con este pivote exactamente como lo haríamos si nuestros datos fuente aún existieran. Pero nuestro tabulador se ha ido. Se ha eliminado por completo, y es por eso que es tan poderoso trabajo del dedo del pie fuera del efectivo cuando estás lidiando con tablas estáticas como esta. Ahora ten en cuenta si fuimos a las herramientas de tabla pivote tratando de refrescarse. Aquí está ese error que estamos recibiendo. referencia no es válida porque X L ya no puede encontrar esa tabla de Sanford y salarios, y eso está bien, porque lo que podemos hacer aquí está claro. Cualquier filtro que tengamos así o puedes entrar en la opción clara y decir Borrar cualquier filtro Y ni siquiera importa cómo se vea nuestra lista de campos. Qué valores hay aquí, lo que estamos buscando para las etiquetas de Roar COLUMNA Todo lo que necesitamos hacer es saltar hacia abajo a cualquier celda de gran total. O ser 15 7 18 o C y hacer doble clic y ver lo que pasa Creamos en hoja nueva. En mi caso, se llama
ella 12 e incluye todos esos datos fuente el nombre año, el título del puesto basado, pagando el pago de horas extras y comprobarlo si nos desplazamos hasta el fondo 16,085. Por lo que hemos revivido todos esos datos de origen usando esa función de mostrar Detalles de DoubleClick. Entonces a partir de aquí, tal vez agregamos algunas nuevas columnas calculadas. A lo mejor apilamos en algunos datos que necesitamos. Podemos refrescar nuestro pivote ahora que nuestra tabla existe de nuevo y luego seguir adelante y eliminarla una vez más. Por lo que obviamente una herramienta increíblemente poderosa y una con la que muchos usuarios de Excel realmente no se sienten
muy cómodos trabajando. Entonces ahí lo tienes ese es tu curso acelerado en el efectivo de la tabla pivot y cómo se puede usar para eliminar realmente y luego revivir tus datos de origen.
14. Añadir listas de ordenadas personalizadas: Quiero compartir con ustedes una punta de mesa pivotante de cinco estrellas. Vamos a hablar de organizar campos usando listas de clasificación personalizadas. Ahora la razón por la que este es un consejo de nivel experto no tiene nada que ver con el nivel de dificultad. De hecho, es bastante fácil de implementar y todo que ver con el hecho de que el 99% de los usuarios de Excel por ahí simplemente no tienen idea de que esta opción incluso existe. Por lo que de forma predeterminada, Excel tiene un número integrado en listas de clasificación para ayudar a organizar campos que no se pueden ordenar con reglas
tradicionales. Entonces cosas como nombres de semana, días y meses, que no siguen ningún tipo de lógica alfabética o alfabética inversa. Sólo hay que saber que el lunes llega antes del martes. Marzo llega antes de abril, y así sucesivamente y así sucesivamente, y eso es exactamente para lo que están diseñadas las listas de clasificación personalizadas. Y en esta demo, vamos a usar un proceso muy similar para ordenar medallas olímpicas porque, igual que entre semana, aquí
hay un orden muy particular que no necesariamente sigue ninguna regla estándar. Entonces lo que vamos a hacer es tener que archivar opciones avanzadas y luego perforar todo el camino hasta la sección general y hacer clic en este botón editar lista personalizada donde dice crear listas para usar en tipos y secuencias de relleno. Y lo que vamos a hacer es crear una nueva lista y decirle a Excel, Hey, estas medallas olímpicas aéreas, son significativas. Siempre quiero que el oro sea primero, seguido de la plata, luego el bronce, luego n A. Y una vez que hayas guardado esa lista y vuelvas a tus datos o a tu tabla pivotante ahora, esa ordenación personalizada tomará precedente sobre las reglas de clasificación alfabética estándar. Y puedes usar esa misma vieja una opción mareada para ahora ordenar en el orden que has definido herramienta
tan útil, sobre todo si tienes muchos elementos y no puedes hacer clic y arrastrarlos manualmente al orden que necesites. Tan común uso casos cualquier campo que no se pueda ordenar con medios tradicionales como
tallas de ropa pequeñas, medianas y grandes o como vemos aquí, medallas
olímpicas Oro. Silver Bronze también puede ser realmente útil para crear calendarios fiscales personalizados para anular clasificación de meses
estándar. Entonces, por ejemplo, si tu ejercicio fiscal comienza en mayo, podrías crear una lista personalizada para definir el orden del mes para tu ejercicio fiscal específico. Así que saltemos a Excel y practiquemos realmente crear y aplicar una de estas listas de
clasificación personalizadas . Está bien, ahora, si te gustaría
seguirme conmigo, adelante y abre tu cuaderno de trabajo pro tip, dirígete a tu tabla de contenidos y busca las listas de clasificación aduaneras demo de cinco estrellas en el Pivot
gris Sección de consejos de mesa. Y cuando te vinculas a esa oveja qué, deberías ver sus datos de los Juegos Olímpicos de Verano 2016 y estamos viendo el conteo de medallas , el conteo de medallas otorgadas con base en el metal tipo oro, plata, bronce o cualquiera, y también con base en estos deportes. Ahora el reto aquí es que este campo metálico y la columna B intenten ordenarlo alfabéticamente . Un mareado. Terminamos con bronce en la parte superior y plata en la parte inferior, lo cual no tiene mucho sentido. Misma historia si la revertimos día Tizzy. Ahora solo conseguimos plata en la parte superior, seguido de cualquiera y luego oro y bronce. Entonces tenemos dos opciones. En casos como este, Opción uno es seleccionar los campos, el
cursor sobre el borde hasta obtener la flecha de cuatro puntas y hacer clic y arrastrarlos a un orden
diferente y eso está bien. Para casos como este, sólo cuatro elementos en el campo. Definitivamente soy factible. La otra opción, que es un poco más escalable, es crear una lista de clasificación personalizada. Entonces déjame mostrarte cómo funcionaría exactamente eso. Vamos a entrar en nuestro archivo tam taladro en nuestras opciones, tuvimos que avanzar y luego desplazarnos casi todo el camino hasta la parte inferior hasta llegar a esta sección
general. Y el último elemento de la sección general dice crear listas para su uso en ordenamientos y
secuencias de relleno , Editar listas personalizadas. Eso es lo que quieres comprobar y llegas a este cuadro de diálogo Aquí puedes ver esas listas personalizadas de nombre de semana, día y mes que mencioné antes ya horneadas aquí y para sobresalir . Y todo lo que vamos a hacer es añadir nueva lista. Vamos a escribir en el orden de esas medallas que separan cada línea usando la tecla enter. Es una plata de oro. Entra bronce, entra una A y sigamos adelante y clica. Agrega y se agrega esa lista aquí mismo en una ventana de lista personalizada presiona OK on. De acuerdo, otra vez. Ahora, cuando volvamos a nuestro pivote, notarás que no se clasificó automáticamente Así que puede que tengas que volver a esa columna y ahora presionar un mareado y ver cómo lo ordena. Ahora es oro, plata, bronce y a porque esa lista personalizada que acabamos de crear tiene precedente. Y esencialmente anulamos el orden que se habría generado usando la clasificación
alfabética tradicional . Qué caso bronce habría aparecido en la parte superior. Entonces ahí lo tienes. Este fue un ejemplo bastante sencillo, pero un gran consejo para guardar en tu bolsillo trasero cada vez que tengas una lista que sigue un orden de clasificación
no tradicional.
15. Resolver fórmulas de pedido y lista: todo bien para este pro tip. Quieres hablar de cambiar estos resolver orden para priorizar tus cálculos de tabla pivotante. Ahora se trata de un consejo de cinco estrellas, nivel
experto. Las probabilidades son que esto es algo que quizás nunca necesites usar, pero es una herramienta útil para tener en tu bolsillo trasero por si acaso. Entonces donde esto sí entra en juego es si eres pivote. La tabla contiene varios elementos y campos calculados, que podrían superponerse potencialmente. En esos casos, utilizaría el cuadro de diálogo Ordenar resolver para determinar la prioridad en la que se
evalúan esas fórmulas . Por lo que desde sus herramientas de tabla dinámica, puede caer en sus campos ítems y sets menú y hacer clic en el botón resolver orden dedo del pie acceder a ese cuadro de diálogo ahora dentro de esa ventana
del cuadro de diálogo, la última fórmula en esa lista anulará las anteriores. Te voy a mostrar un ejemplo de exactamente por qué eso es tan importante en nuestra demo. En tan solo un segundo ahora, es posible que hayas notado que dentro de esos campos, los artículos y conjuntos cayeron. Ahí hay otra opción. Se llaman fórmulas de lista, y lo que realmente hace las fórmulas de lista se genera en nueva pestaña en Excel para documentar todos tus elementos calculados, y campos así como ese orden de clasificación. Por lo que esa podría ser una herramienta realmente útil para proporcionar documentación a tus usuarios finales, sobre todo si estás lidiando con tablas muy complejas. Entonces, para recapitular aquí, número uno caso de uso determinando qué cálculos deben tener prioridad en
casos conflictivos y el número dos nuevamente. Si necesita producir alguna documentación para sus tablas dinámicas, especialmente si contienen una gran cantidad de elementos calculados y campos que listan fórmulas , Tool es una gran manera de hacerlo. Entonces con eso, vamos a subirnos a nuestra demo y excel, y te mostraré exactamente cuándo, por qué y cómo se puede usar esta herramienta de órdenes de resolver. Muy bien, Así que adelante y abre tu cuaderno de trabajo pro tip. Si lo estás siguiendo, dirígete a tu tabla de contenidos y estamos buscando la sección de consejos de tabla dinámica gris aquí , específicamente la demo de fórmulas de orden de resolución y lista. Así que adelante y enlaza con esa oveja, y lo que encontramos es lo que parece ser un diseño de tabla pivotante bastante simple construido en nuestra base de datos de películas I. M. M.
D B. Estamos viendo aquí los valores de ingresos
brutos, desglosados por idioma en regiones de rosa en las capturas de columnas. También tenemos algunos artículos calculados aquí en juego también. Específicamente, este cálculo por ciento de inglés en Roast siete y este Cálculo Regional de Norteamérica aquí en la columna D. Ahora, para ver qué está pasando aquí, podemos perforar nuestras herramientas de tabla pivotante, campos, elementos y conjuntos y abre nuestro cuadro de diálogo de resolución de orden. Aquí. Se puede ver que efectivamente hay dos de estos ítems calculados que están presentes en nuestro diseño por ciento inglés, que está tomando los valores de inglés y dividiéndose por el total en los cuatro idiomas Inglés, Francés, Japonés y español. Y luego tenemos este cálculo regional de Norteamérica que está tomando los valores de Canadá y sumando a los valores de Estados Unidos. Ahora recuerda cómo funciona este cuadro de diálogo de orden de resolución. El valor en una celda está determinado por la última fórmula en el orden de resolución. Y ahora mismo esa fórmula de Norteamérica vive por debajo del cálculo por ciento inglés. Por lo que América del Norte, en este caso, tendrá prioridad. Entonces vamos a cerrar de esta caja. Regresa a nuestro pivote, muéstrale lo que está pasando aquí. Por lo que en este punto son por ciento de partida calculada en inglés. Técnicamente se supone que impacta estas tres celdas Eso es lo más destacado en verde B siete, C siete y D siete al mismo tiempo son Norteamérica. El cálculo está impactando estas celdas d 3456 y siete y anote aquí el Intersect, que es una celda que es D siete. Entonces tienes dos cosas conflictivas ocurriendo aquí mismo en D siete. Eres por ciento inglés. El cálculo es decirle a Excel, Hey, toma este valor en D tres y dividido por la suma de D 3 36 y dame el porcentaje. Al mismo tiempo. El cálculo de Norteamérica está diciendo:
No, No, no, no
hagas eso. Toma el valor en B siete y simplemente agrégalo al valor en C siete Ahora ambos cálculos no pueden ser ciertos. No puede suceder al mismo tiempo, y ahí es exactamente donde entra en juego la orden de resolver. Y recuerda que debido a que la fórmula de Norteamérica ocurrió más baja en la lista, esa toma prioridad. Y de hecho, eso es exactamente lo que está pasando aquí. Simplemente estamos sumando estos dos porcentajes, lo que genera 1 98.69% y eso realmente no tiene sentido aquí, porque el porcentaje del cálculo en inglés debe ser un porcentaje por debajo del 100%. Y en teoría, ese debería ser realmente el que tenga prioridad aquí, a diferencia
de este cálculo de Norteamérica. Entonces todo lo que necesitamos hacer es volver a nuestras herramientas, siente artículos y establece orden resuelto, y tomaremos este cálculo en inglés o por ciento en inglés, y no podemos arrastrar. Se hará clic en el botón mover hacia abajo, moviéndose todo el camino en la parte inferior, por lo que siempre tiene prioridad. Así que vamos a seguir adelante y hacer clic en cerrar, y ahora comprobarlo. Ese valor cambia de 1 98 a 99.94 que es el cálculo correcto basado en esa lógica por ciento inglesa. Entonces ese es el orden de resolver y por qué es importante. Lo último que te mostraré rápidamente es la opción de fórmulas de lista, que puedes acceder aquí mismo desde ese mismo menú, campos, elementos y conjuntos. Al hacer clic en esas fórmulas de lista, Excel creará una hoja de trabajo completamente nueva para ti que enumera todos tus
campos calculados todos tus ítems calculados, así
como el orden resuelto específico en el que se evalúan esas fórmulas. Así que de nuevo, herramienta de documentación
muy útil, sobre todo cuando se trata de tablas pivote muy confusas o complejas Así que vamos a seguir adelante y eliminar esa hoja. No necesitamos la documentación en este momento, y ahí la tienes usando las herramientas de fórmulas de orden resolver y listar.