Dominio de las tablas de Excel: cómo hacerlas y usarlas como un profesional

1. Introducción: Mi nombre es Al Chen, soy Arquitecto de Soluciones en Coda, una herramienta que reúne lo mejor de hojas de cálculo, docs, aplicaciones. He sido un power-user de Excel desde hace 10 años más, impartí más de 10 mil alumnos de MBA. En estos días, paso mi tiempo creando tutoriales y videos sobre hojas de cálculo de Excel y otras herramientas relacionadas. actualidad, te estaré enseñando a usar tablas de Excel una característica relativamente desconocida dentro de Excel. Una vez que aprendas el poder de las tablas de Excel, nunca mirarás atrás. Te estaré enseñando a crear tablas de Excel, a crear referencias de estructura, y también a dar formato a tu tabla, que se vean muy bonitas y bonitas para tu equipo y para tus compañeros. Estoy muy emocionado de que los alumnos tomen esta clase porque una vez aprendas las características y funciones básicas de las tablas de Excel, verás el poder que desbloquea para el resto de tus dashboards, tu análisis de datos, tareas, y otras cosas relacionadas con las hojas de cálculo de excel. solo aprender esta única característica simple, esperemos que cambie la forma en que te acercas a Excel en el futuro. Una vez que hayas terminado de tomar esta clase espero que participes en los proyectos de la clase. Se trata de analizar los datos de retraso de vuelo del Departamento de Transporte a partir de 2015, convertirlos en una tabla de Excel, y también hacer análisis de estos datos mediante la creación de un panel de control, creación de un informe para el usuario final, y también escribiendo algunas ideas sobre lo que puedes reunir al analizar los datos después de usar tablas de Excel. Realmente espero que publiques tus hallazgos en los datos dentro de la galería del proyecto. Empecemos de inmediato con encontrar los beneficios de las tablas de Excel. 2. Los beneficios de las tablas de Excel: En esta lección, voy a estar mostrándote los beneficios de las tablas de Excel haciendo una demostración rápida de una tabla que he construido en el libro de clases, donde te mostraré cómo filtrar y ordenar, agregar una fila de resumen a tus datos, y también agregar nuevos datos a la tabla de Excel. Estaré explorando estos temas en más profundidad más adelante en la clase. Estamos viendo una mesa de Excel en este momento. En el paso 1, solo vamos a hacer clic en la tabla de abajo y desplazarnos hacia abajo más allá de la primera fila de encabezado, que es la fila 11. A medida que me desplace, notarás que las columnas se volverán pegajosas en la barra de herramientas superior o en la cinta. Esta es una de las características agradables de las tablas de Excel donde se pueden ver las columnas aquí arriba a medida que se desplazan más allá. Es como congelar tu primera fila alguna manera si ya lo has hecho antes en tus archivos de Excel. También puedo filtrar y ordenar rápidamente mis datos con solo hacer clic en el “Descendente” aquí en una de estas columnas, y puedo hacer clic en “Ascendente” o “Descendente” y filtrar cosas como has hecho antes en tus archivos de Excel. Esta es una de las características predeterminadas que vienen con tu tabla de Excel. Digamos que quiero hacer una fila de resumen en el paso 3. Puedo hacer clic dentro de mi tabla, hacer clic en la “pestaña Tabla” en la cinta, y luego nota que las casillas de verificación de fila total desmarcan por defecto. Si hago clic en esto, verás en la fila 39, hay una fila de resumen la cual básicamente puedes resumir tus datos por promedios, sumas, conteos, todas estas métricas típicas. Voy a desmarcar esto por ahora. Aquí en el paso 4 se enumeran una de las características más poderosas con tablas. Quiero que intentes agregar una nueva fila de datos a la tabla. Si solo hago clic aquí y golpeo “Enter” y empiezo a escribir algo como S510, y esta es una película, si quiero escribir en otra fila S200, notan que la tabla agrega automáticamente el formato a las nuevas filas de datos, y estas filas de datos se incluyen en la tabla como un objeto. Ahora, una cosa en la que quiero que pienses es imaginar tener que crear esta tabla usando un formato condicional. Tendrías que hacer muchas fórmulas y funciones realmente interesantes para que estas filas de bandas se coloreen así. Lo bonito de las tablas de Excel es que Excel se encarga de todo ese formato condicional para ti, modo que cuando añadas nuevas filas de datos automáticamente obtengan código de color para que los datos destaquen. Por último, quiero que intentes poner una fórmula, cualquier fórmula en la columna K, donde dice, Mi Fórmula. Voy a escribir igual golpeó una celda en tal vez la misma fila aquí, y notan que le da estas referencias, la columna, en lugar de un tipo de referencia tradicional acorazado, tenemos A1 y B5. Es un poco diferente a lo que verás, pero hablaremos más de qué es esto y por qué es realmente poderoso en una futura lección sobre referencias estructuradas. Tan solo para recapitular, caminamos por algunos de los beneficios y características de las tablas de Excel, te mostramos cómo hacer que tus tablas sean un poco menos propensas a errores al tener este formato condicional que se aplica fácilmente, y también caminamos por muy rápido cómo se puede filtrar y ordenar las tablas. Una clave para llevar de esta lección es simplemente empezar a jugar con la mesa en el cuaderno de clases. En la siguiente lección, vamos a hablar de cómo crear tu propia mesa desde cero para que puedas empezar a hacer algún análisis. 3. Crear una tabla de Excel: Estaremos creando una mesa de Excel desde cero usando una lista de restaurantes en el cuaderno de clases. Empecemos. Tenemos una lista de restaurantes aquí con la ubicación, las ventas, el estado de franquicias, y puedes conocer más sobre este conjunto de datos haciendo clic en esta URL que tengo aquí en esta celda. Pero recorremos paso a paso cómo crear una tabla y usar algunas de las características de esta tabla. Establece uno vamos a dar clic en “Insertar” y luego en “Mesa”, y vamos a nombrar a esta mesa, Restaurantes. Quiero que primero selecciones todos tus datos. Son alrededor de 50 filas. A continuación, vaya a Insertar tabla. Por defecto, va a decir que mi tabla tiene encabezados. Normalmente, se quiere mantener esto como comprobado porque nuestra tabla sí tiene encabezados, se puede ver aquí en la fila 10. Voy a dar clic, “Ok”, y así es como Excel crea una tabla para ti por defecto. Te das cuenta de que obtienes las filas de colores bandas, y también obtienes estos desplegables para filtrar y clasificar rápidamente. Ves cuáles son los beneficios aquí de la mesa cuando golpeas Enter y comienzas a escribir de nuevo un restaurante nuevo y dices que esto es de Arby, Excel expandirá automáticamente esa tabla para incluir esa nueva fila de datos. Si añado nuevas filas de datos, Taco Bell, verás cómo la tabla Excel codificará automáticamente las filas para ti para que no tengas que preocuparte por el formato condicional como lo harías normalmente con una normal lista de datos. Ahora voy a eliminar algunos datos de la tabla e intentar navegar por las celdas vacías en el paso 2. Solo eliminemos algunas de estas filas. Te das cuenta de que todo esto se ha ido, pero Excel todavía piensa que estas filas son probablemente tabla. Ahora, antes de eliminar estas filas, quiero que navegues presionando “Comando” o “Control” con las teclas de flecha. Te das cuenta de que el comportamiento es un poco diferente a lo que esperarías. Se llega a ir a los bordes de la tabla, en lugar de cuando hay una nueva celda con un valor. Solo voy a seleccionar estas filas, hacer clic derecho, y luego presionar “Eliminar”, eliminar filas de tabla, y esas filas se eliminarán de mi tabla para que no se conten como filas vacías. Eliminamos las filas vacías, y ahora quiero que hagas algunas otras opciones para tablas de Excel como podemos convertir esta tabla en una Tabla Pivot si queremos haciendo clic en “Tabla” en la cinta, y luego haciendo clic en “Resumir con tabla dinámica”. Voy a usar la hoja de trabajo existente en este caso y el rango va a ser la celda J14. Una vez que haga clic en “Ok” para la Tabla Pivot, verás que esto se crea por aquí, así que tenemos la opción de crear una Tabla Pivot si queremos. Si en algún momento quieres convertir tu tabla de Excel a un rango normal de datos, todo lo que tienes que hacer es volver a hacer clic en la cinta “Tabla”, y una de las opciones de la barra de herramientas es convertir a rango. Si hago clic en “Convertir a rango”, Excel eliminará automáticamente los desplegables por mí, pero el formato condicional seguirá en la mesa, por lo que puedes eliminar eso borrando el formato si quieres. Voy a deshacer ese convertir a rango. Una última cosa que quiero hacer, me olvidé de mencionar es en el paso 1 es que queremos renombrar esta mesa a restaurantes en la parte superior izquierda. Si hago clic en una celda dentro de la tabla y hago clic en la pestaña “Tabla” de la cinta de nuevo, se nota por defecto, Excel me dio un nombre de tabla de Table4. Voy a renombrar esto a Restaurantes y golpear “Enter” para que en el futuro, si alguna vez quiero referirme a esta tabla, simplemente puedo llamarla Restaurantes en lugar de Table4 o lo que Excel me dé como predeterminado. En esta lección, caminamos por algunos de los beneficios de usar una tabla de Excel y cómo crear una tabla de Excel, cómo podemos eliminar filas y aplicar algún formato rápido a las tablas. En la siguiente lección, vamos a recorrer más en profundidad cómo filtrar, ordenar y crear columnas calculadas en tu tabla de Excel. 4. Filtrar y clasificar una tabla de Excel: Estaremos buceando más profundamente en filtrar y ordenar tu tabla de Excel, y también en cómo agregar una nueva fórmula a tu tabla, que se denominan columnas calculadas. Vamos a sumergirnos. Tenemos la misma mesa de restaurantes también como una mesa de Excel. Si hago click en “Tabla” aquí verás que esta tabla se llama Rest2. Como ya has visto antes, puedes ordenar por cualquiera de estas columnas haciendo clic en “Ascendente” o “Descendente”, y solo puedo añadir este tipo muy rápidamente a cualquier columna que quiera. Yo quiero que ahora en el paso dos, es agregar una fila de resumen a esta tabla y encontrar el promedio de ventas, que está en la columna D en nuestra lista de restaurantes. Si vas a Table, click en “Total Row' y te das cuenta en la parte inferior de la fila 61 obtenemos esta fila azul oscuro para nuestros totales. En la columna de ventas, quiero que haga clic en el menú desplegable y solo haga clic en “Promedio”. Ahora solo podemos encontrar el promedio de ventas en todos estos restaurantes, que es de unos 34 millones de dólares. Esa es una manera realmente bonita de obtener rápidamente el recuento de sumas promedio, al igual que lo harías en una tabla pivotante tradicional, pero en una tabla de Excel, se maneja a través de esta última fila en la parte inferior de tu tabla. En el paso tres, vamos a crear una rebanadora basada en la columna Franquicias. Ahora rebanadora al igual que una manera agradable visualmente para que tu equipo, tus colegas, interactúen rápidamente con tu tabla Excel, voy a dar clic en mi “Mesa” aquí, asegúrate de que estás en la pestaña Tabla en la cinta y haz clic en “Insertar Cortador”. Obtendrás este menú aquí donde podrás seleccionar en qué columna quieres rebanar. Solo voy a dar clic en “Franquicias”, toca “Vale”, y obtendrás este pequeño menú donde podrás escoger y elegir Sí o No. Te das cuenta de que al hacer clic en Sí o No, esta columna Franquicias se filtra automáticamente a ese valor específico. Esa es una característica realmente bonita de tener con tus rebanadoras. Si hago clic en esta opción aquí mismo, esta opción de marca de verificación en el rebanador, ahora puedo seleccionar múltiples opciones en mi caja de rebanadora. Solo voy a quitar la rebanadora por ahora, presiona “Eliminar” en eso, y volver a mi vista de tabla principal aquí. Por último, vamos a escribir una fórmula en la primera columna vacía a la derecha, que es la columna I, para calcular el promedio de ventas por tienda. Déjame deshacer este filtro aquí. Si hago clic en la celda I10 y escribo promedio de ventas, nota Excel automáticamente agrega esa columna como una nueva columna a mi tabla, y si empiezo a escribir igual en la celda I11, igual a ventas, que es la columna D dividida por la columna F, que es localizaciones, mira la fórmula que se crea, las ventas divididas por ubicaciones, vamos a golpear “Enter” y ver qué pasa. La fórmula se llena automáticamente hasta la última fila de mi tabla, así que no tengo que hacer ningún doble clic o arrastrar mi fórmula hasta abajo. Las fórmulas en tablas se aplican automáticamente a toda la columna todo de una sola vez, para que no tengas que preocuparte por todos estos pasos manuales extra para crear la fórmula. Es posible que haya hecho algo similar a esto, que en una tabla de Excel se llama columna calculada. Es posible que haya hecho algo similar en una tabla dinámica, que se denomina campo calculado. Simplemente estamos haciendo lo [inaudible] en una tabla de Excel. Dando un paso atrás un poco, si tuvieras que construir esta fórmula y construir esta tabla desde cero, y para también obtener los datos resumidos aquí en esta fila 61, tendrías que hacer una complicada lista de fórmulas. Podría estar usando una fórmula COUNT A para seleccionar todas las celdas aquí que tienen un valor vacío. Lo bonito de esta fila total es que automáticamente se agrega al fondo de la tabla, no tienes que preocuparte por agregar todas estas fórmulas adicionales que solo te hacen la vida un poco más difícil de hacer. Hablamos de cómo hacer un poco más de filtrado, clasificación y creación de columnas calculadas en una tabla de Excel. Ahora, la fórmula de tabla de Excel que viste en la columna para ventas promedio parece complicada, así que vamos a profundizar en eso en la siguiente lección sobre referencias estructuradas. 5. Fórmulas dentro de referencias estructuradas: Estaremos buceando más profundamente en referencias estructuradas. Estaré caminando por diferentes formas en las que puedes crear fórmulas con referencias estructuradas dentro de tus tablas de Excel. Una nota rápida sobre estas referencias estructuradas es que cuando estás dentro de la tabla misma, como tenemos con ventas promedio, te refieres a la columna poniendo este ampersand más el nombre de la columna rodeado de corchetes. Esto es diferente a cuando haces igual, celular K5 o algo así, o K9. En las tablas de Excel, haces referencia a toda la columna así que por eso cuando escribes una fórmula, la fórmula se rellena a través de toda la columna por sí sola. Si estás fuera de la mesa, como tenemos aquí en el paso 1, vamos a escribir iguales. Voy a referirme nuevamente a la columna de ventas. Se nota que primero tengo que poner el nombre de la tabla Rest3, seguido de la referencia estructurada, que es @Sales. Esto me dará sólo el valor para esa fila, pesar de que hace referencia a toda la columna. Esta es una cosa a la que acostumbrarse ya que estás haciendo referencia a celdas y filas de tu tabla. Tan sólo para confirmar, voy a dar clic en “Tabla” aquí, de hecho llamado Rest3. Por último, en el paso 2, quiero que hagas un ejercicio más con referenciar esta tabla es escribir igual y tratar de seleccionar toda la tabla con los encabezados. Te das cuenta de que dice Rest3 #All. Esto es decirle a Excel que estás haciendo referencia a toda la tabla a la vez. Si solo quieres hacer referencia a los datos, solo puedes escribir igual y solo seleccionar todo sin el encabezado y solo vas a ver, dice Rest3, pero también puedes escribir [#Data y obtendrás el mismo resultado. Esto me dará un error porque estoy representando la tabla en una celda para que eso no vaya a funcionar. Quiero que escribas una fórmula SUMA en la columna de ubicaciones fuera de la tabla usando lo que acabamos de aprender con fórmulas y autorellenos con referencias de tabla. Escribamos la fórmula aquí mismo, es igual a suma. El nombre de nuestra mesa es Rest3. Puedo hacer click en cualquiera de estas referencias estructuradas aquí, Rest3 e intentemos referirnos solo a la columna de ubicaciones. Puedo escribir corchete izquierdo. Al minuto que escribes un corchete izquierdo, te da la opción de seleccionar cualquiera de las columnas de tu tabla. Ahora puedo dar click en “Ubicación” dar click en paréntesis derecho. Lo que estoy haciendo aquí es que necesitaré escribir ubicaciones con una S para hacer referencia a esta columna, columna F. Lo que estoy diciendo aquí es sumar todo en la columna ubicaciones dentro de mi tabla Rest3. Golpea “Enter” y esto son 1,735. Yo sólo voy a formatear esto para ser un número. Te podría estar preguntando ¿por qué es esto más fácil que escribir igual a suma y luego seleccionar todas las celdas justo aquí? Tradicionalmente lo haces con una lista normal de datos, pero lo bueno de estas referencias estructuradas es si voy aquí abajo hasta la última fila de mi tabla y agrego una nueva fila, 51, Taco Bell, y agrego un valor a mi columna Ubicaciones, digamos a 100 o 1,000, este número se actualizará automáticamente en mi suma porque esta referencia estructurada se expande y se contrae en función de lo que hay en tu tabla. Esa es una de las características más poderosas de las tablas de Excel es medida que se agregan nuevos datos a tu tabla, las referencias estructuradas se actualizarán. Imagina tener que hacer esto con una fórmula COUNT la cual podrías estar usando hoy para contar todos los valores de tu columna. Las referencias de estructura lo convierten en una forma mucho más limpia, menos propensa a errores para hacer referencia a todos los valores de su tabla. Por último, vamos a hacer un poco de ejercicio en crear algunas estadísticas de resumen aquí en la columna M y N. Quiero que escribas una fórmula SUMIFS para resumir todas las ventas de estos restaurantes en base a ubicaciones con 75 grado o plus, 50 o 75 para restaurantes que tengan franquicias y sin franquicias. Empecemos a escribirlo. Puedo escribir igual a sumifs. Yo sólo voy a acercar aquí para que puedan ver todos mis datos, igual a sumifs. Voy a usar mi cursor para chupar mi alcance. Mi rango de suma solo va a ser mis datos de ventas, hit coma, y luego mi primer criterio va a ser ubicaciones. Quiero todas las ubicaciones que sean [inaudibles]. Voy a eliminar esta fila aquí sólo para asegurarme de que tenemos los datos de ubicación correctos. Voy a dar clic en “Eliminar filas”. Podría estar preguntándose ¿cómo voy a poner en los criterios? Normalmente en mi archivo Excel pongo estas pequeñas columnas de ayudante. Yo soy algunos para escribir el número 1,000, 75, 50, 25, y 0. De esta manera puedo decirle a la fórmula SUMIFS qué hacer referencia como mis criterios. Adelante y escribamos la fórmula es igual a sumifs. Mi rango de suma va a ser ventas otra vez, coma, mi rango de criterios va a ser ubicaciones que son menores a 1,000. En este caso, tuve que poner un ampersand menor a 1,000, coma, pero mayor que o igual a 75 para esta fila, sólo voy a poner la comilla doble ahí, ampersand. Entonces también quiero ver solo tiendas que tengan franquicia en este caso. Puedo poner franquicias de coma, sí. Sólo para recapitular, estoy tomando las ventas, sumando las ventas si las ubicaciones son menores a 1,000 pero mayores o iguales a 75, hit “Enter”. Ahora construimos mi fórmula, pega “Enter”. Si acabo de llenar esta fórmula hacia abajo, automáticamente me dará el valor porque tengo mis criterios y columna L de referencia dinámicamente. Voy a copiar la misma fórmula y ponerla en estas celdas aquí mismo. Pero lo principal es que voy a cambiar la franquicia aquí por no porque estas son células. Quiero que mi reporte aquí no tenga franquicias. Ahora voy a sumar estos valores realmente rápido solo para asegurarme de que el total de ventas sea lo que espero que sea, que es 1.689. Nuevamente, si bajo a mi columna de ventas aquí y escribo suma, esto es efectivamente 1.689. Es así como utilizarías la fórmula SUMIFS con referencias de celdas dinámicas para nuestras ubicaciones, pero usando referencias estructuradas para sacar realmente los datos de la tabla de Excel. Vimos cómo puedes hacer referencia a las columnas tu tabla usando referencias estructuradas y también mostramos cómo puedes usar referencias estructuradas en algunas de las fórmulas más populares que usas hoy en día como SUM y SUMIFS. También vimos cómo al escribir tus fórmulas con referencias estructuradas, tus fórmulas son un poco más fáciles de leer y mucho más fáciles de depurar, sobre todo si tus compañeros están recogiendo las fórmulas o tu archivo Excel de ti. Ahora discutiremos cómo puedes usar referencias estructuradas de tus tablas de Excel con una de las fórmulas más populares que estás usando hoy en día, que es BUSCOP. 6. BUSCARV y referencias estructuradas: Estaremos usando la fórmula VLOOKUP ahora para buscar una tabla de Excel para llevar los datos a nuestra tabla principal de datos. Trabajando fuera de nuestras mismas listas de restaurantes ahora, estás viendo las columnas L y M, tengo el nombre de la ciudad más el número de población de esa ciudad específica. En el paso 1, quiero que conviertas esa lista de ciudades y población en una tabla de Excel llamada Pop. Seleccionemos estas celdas. Puedes hacer clic en “Insertar tabla” o un atajo que me gusta usar es este presionando Control T, que solo te trae el menú de crear tabla. Mi mesa tiene encabezados hit “ok” y ahora tenemos esto como tabla. Tan solo para estar seguros de dar click en “Tabla” y recordar, queremos renombrar este nombre de tabla para que sea Pop para nuestra población. Tenemos dos tablas ahora en esta hoja de trabajo, tenemos Rest312, y luego tenemos nuestra tabla Pop, y se puede ver esto en la parte superior izquierda de nuestra hoja de trabajo. Te quiero ahora en el paso 2 es agregar una nueva columna a nuestra tabla principal y escribir un VLOOKUP para buscar a la población de nuestra ciudad en base a la columna C, que son nuestros datos de ubicación. Llamemos a esta población y al igual que hemos visto antes, cuando se agrega en una nueva columna, la tabla de Excel recoge automáticamente esa columna como parte de la tabla. Empecemos a escribir la fórmula VLOOKUP que estás muy acostumbrado a VLOOKUP. Queremos buscar la columna de ubicación desde la mesa de nuestro restaurante y notar cómo eso es referencia estructurada sólo se convierte en una ubicación de símbolo de sombrero. Nuestro array de tablas es que puedes simplemente literalmente escribir Pop y notar cómo cuando escribo Pop, resalta las celdas de mi tabla Pop. También podría escribir hashtag de corchete izquierdo todo y esto hace referencia a toda la tabla más los encabezados, que realmente no necesito, pero realmente no impactará mi fórmula. Dejémoslo como Pop, 2, 0 porque quiero devolver la segunda columna, que es la población y cuando golpeo “Enter”, automáticamente llena la fórmula para toda mi lista de restaurantes. Tenemos un montón de no aplicable porque no todas las ciudades en mi mesa de restaurantes están en nuestra tabla de ubicación. Pero digamos que quiero agregar una nueva fila de datos a mi tabla Pop, que es el paso 4. Digamos que quiero sumar la población para Louisville, Kentucky. Sólo voy a copiar esto por ahora a través de una pasta aquí y digamos que Louisville es de 50,000. Observe cómo esta fila en mi mesa de restaurantes se llena automáticamente porque mi fórmula VLOOKUP siempre está haciendo una búsqueda a mi mesa Pop y al minuto que agrego una ciudad a mi mesa pop, expande automáticamente la referencia estructurada dentro de mi búsqueda, que está en esta columna justo aquí. Podemos hacer lo mismo con el índice match, que puede que estés usando antes. Puedo escribir índice y quiero seleccionar toda mi población aquí. Quiero hacer coincidir esta columna de ubicación dentro de mi Pop city location hit “Enter”, y en realidad quiero hacer una coincidencia exacta, razón por la cual me está dando valores en cada celda. Hagamos coma cero. Debería estar funcionando así pero, oh, lo siento, está dentro de este partido. Esto le estará dando exactamente los mismos resultados que el VLOOKUP pero observe cómo estamos usando la referencia estructurada en múltiples tablas ahora. Estamos mirando a la población desde una mesa Pop porque es un valor que quieres retroceder. Estamos haciendo coincidir la columna de ubicación, que es esta columna, y luego estamos comparando eso con la columna de la ciudad, que es esta tabla Pop. Una última cosa a tener en cuenta sobre las referencias sociales es que cuando ves el símbolo @, esa también es una forma de decirle a Excel esto está haciendo referencia a esta fila en mi tabla, razón por la cual solo ves Seattle, Washington, pero no toda la columna que sería simplemente ubicación por sí misma. Esto seguiría funcionando pero para ser más específico, quieres poner ese símbolo @ para asegurarte de que estás haciendo referencia solo a esa fila de datos. Nos fijamos en cómo se puede utilizar la fórmula VLOOKUP, una fórmula realmente común utilizada hoy en día, con referencias de estructura de sus tablas de Excel para buscar datos dinámicamente para que cuando se agrega un nuevo dato a su tabla de búsqueda, para tu tabla de estadísticas principales, que no automáticamente se refresca y se mete correctamente. Vimos cómo puedes usar referencias estructuradas y fórmulas, ahora vamos a entrar en un caso de uso más avanzado de usar referencias estructuradas con una lista desplegable y validación de datos en la siguiente lección. 7. Listas desplegables y validación de datos: Vamos a construir una Lista desplegable usando tablas de Excel, y te mostraremos cómo estas opciones flexibles pueden autoexpandirse usando la tabla. Con nuestras mismas listas de restaurantes, tenemos esta lista separada de datos aquí llamada KnownFor. Simplemente tenemos algunos atributos diversos con los que queremos etiquetar nuestros restaurantes. Pero quiero convertir esta lista de atributos en una lista desplegable que podemos utilizar en nuestra tabla principal de restaurantes. En el paso 1, vamos a convertir la lista Conocida por en una tabla llamada Conocida por. Aquí arriba, vamos a insertar tabla y mi tabla tiene encabezados. Entonces OK. En el nombre de la tabla, queremos llamar a este KnownFor. Tenemos esto como nuestra tabla principal para seleccionar atributos. Quiero que ahora vaya a Fórmulas y luego vaya a Definir Nombre y le dé a esta tabla un nombre definir de tipos. No queremos incluir los encabezados. Ahora el nombre de la tabla y definir nombres son dos conceptos separados. Esto es algo que se pone un poco complicado al construir lista desplegable con Validación de Datos. Vamos a ir a “Fórmulas”, “Definir Nombre” y te das cuenta aquí ya tengo un montón de nombres definidos, que incluye tablas. Voy a dar click en “Plus”, y vamos a llamar a este tipo. ¿ Cuál es el rango de células aquí? Simplemente puedes escribir KnownFor porque esa va a ser nuestra lista de nuestra mesa. Te das cuenta de cómo cuando hago clic en la tabla aquí, todavía dice conocidoFor, si vuelvo a fórmulas definir nombre, verás que si hago clic en tipos, hace referencia a mi rango de celdas, que se conoce For. Ahora, vamos al paso 3. Quiero crear una nueva columna en mi tabla principal llamada KnownFor y luego ir a Validación de datos, que ya has hecho antes probablemente y crear una lista desplegable, donde vamos a usar los tipos definir nombre. Tan solo para recapitular, tenemos un Definir Nombre llamado tipos, que hace referencia a nuestra tabla ConocidoFor. Demos click en “Columna J” Conocido Por y ahora vamos a “Datos” “Validación de Datos”. Vamos a cambiar esta lista permitida y ¿cuál es nuestra fuente va a ser? Puede que te sientas tentado a escribir igual, KnownFor como nuestra lista, pero en realidad tenemos que usar nuestro nombre define llamados tipos, que hace referencia a nuestra tabla KnownFor. Escribamos iguales tipos, golpeemos “OK”, y veamos qué pasa en este desplegable. Te das cuenta de que obtenemos todos los valores de nuestra tabla ConocidoFor. También puedo simplemente arrastrar esta fórmula hacia abajo y puedes hacer click en el menú desplegable, puedes ver todos estos valores. Algo a tener en cuenta es que si haces click en Fórmulas, Definir Nombre y en mi ConocidoPara referencia de tabla aquí, si hubieras seleccionado accidentalmente toda la tabla aquí así, te das cuenta de cómo cambia la referencia de ConocidoFor a ConocidoPara Todo ConocidoFor. Sé que esto parece complicado, pero la razón por la que esto es problemático es si golpeas bien, esto ahora incluirá el encabezado de columna ConocidoFor dentro del menú desplegable, que no quieres que suceda. Por eso voy a redefinir esto sólo para ser conocidoFor. De esta forma solo tienes las opciones seleccionables en la tabla ConocidoFor. Si arrastro esto todo el camino hasta el resto de mi tabla, ahora tengo este atributo que luego puedo seleccionar de mi tabla ConocidoFor. Otra de las advertencias sobre la lista desplegable, que quizá hayas visto antes es si cambio esto de smoothies a Smoothies ym, te das cuenta de que todos mis sándwiches siguen siendo sándwiches. Tengo que hacer click en el desplegable y renombrar esto a Sandwiches-ym. Solo para asegurarte si estás corrigiendo algún valor aquí en tu lista desplegable ConocidoPara, tienes que cambiarlo manualmente aquí en tu tabla principal. Ahora, por supuesto, el beneficio de usar tablas Excel en nuestra lista desplegable aquí, es si agrego un nuevo valor a mi conocidoPara mesa, digamos que llamo a esto panqueques, ahora consigo panqueques como opción seleccionable en mi lista desplegable, que es realmente agradable de ver porque no tengo que preocuparme por la fórmula que hace referencia a las referencias celulares adecuadas en esta columna. Simplemente puedo hacer referencia a la tabla a través del nombre definir. Ahora, un paso más aquí es, podemos simplificar un poco esto usando la función indirecta y nuestro campo fuente para la validación de datos. Eliminemos todo esto por ahora y digamos que hago clic en Validación de Datos. En este campo fuente, lugar de escribir tipos, que hace referencia a este nombre definir, que hace referencia a esta tabla, en realidad puedo escribir la función indirecta y escribir el nombre de la tabla que se conoce For. Después pon el nombre de la columna, que es (“ConocidoPara [ConocidoPara]”) Lo que le estoy diciendo a Excel aquí es dame la tabla ConocidoFor, que es esto y el nombre de la columna es Conocido Por, pulsa “OK” y esto también me dará el desplegable lista para mi validación de datos. Simplemente otra forma de no tener que usar un Definir Nombre, que luego hace referencia a la tabla, si solo quieres atajarla y simplemente hacer referencia a la tabla misma mediante el uso de esta solución indirecta. Para recapitular, piensa en lo que tendrías que hacer para crear esta validación desplegable con una lista de expansión automática dinámicamente utilizando algo como la función de recuento o la función de desplazamiento. El beneficio de este Definir Nombre Ahora es que puedes usar esto en cualquier parte de tu archivo Excel donde quieras hacer referencia a esta tabla específica. Nuevamente, volviendo a la lección anterior, piense en cómo sus nombres de tabla y sus nombres definen pueden ser variables que hacen referencia a todas las tablas diferentes en su archivo Excel. Construimos una lista desplegable utilizando la función Validación de datos en Excel. Utilizamos tablas de Excel como las opciones seleccionables en nuestra lista desplegable. Hemos hecho una variedad de cosas diferentes en tablas de Excel con referencias estructuradas y fórmulas. Ahora vamos a sumergirnos en formatear nuestras mesas para que se vean realmente bonitas y bonitas para nuestro equipo y para nuestros colegas. 8. Dar formato a tablas de Excel: Hablaremos de formas rápidas en las que puedes formatear tus mesas para que se vean realmente bonitas y bonitas y bien formateadas para tu equipo y para tus colegas. Tenemos un nuevo conjunto de datos en esta lección. Se trata de una lista de precios de Bitcoin por fecha en 2013. Como se puede ver, el precio era obviamente mucho más bajo en aquel entonces. Tenemos el abierto, alto, bajo, cierre, y el spread para estos diversos precios diarios de Bitcoin. En el paso 1, quiero que haga clic en el desplegable en la sección de formato de tabla para crear su propio formato de tabla personalizado. Si hago clic en la tabla, pestaña Tabla en la cinta, y si hago clic en este desplegable aquí puedes ver una variedad de diferentes opciones de formato que puedes aplicar a tu tabla en función de lo que necesites para tus informes, tus dashboards y también lo que tus compañeros y compañeros de equipo podrían encontrar útil para ellos. Ahora, una cosa que es realmente beneficiosa de estos formattings es que puedes personalizar esto aún más creando un formato de tabla personalizado haciendo clic derecho en formato de tabla en esta cinta, haz clic en Duplicar, y luego puedes hacer clic en llamemos a esto AlbitCointable. El motivo por el que podría querer personalizar mi tabla en este escenario específico es que tal vez quiero destacar igual que los precios de Bitcoin de fin de semana, que es día de la semana seis y siete, luego resaltar el día de la semana precios que son día de la semana uno a cinco. Mi primera raya fila, eso es como la primera fila codificada por colores. Puedo cambiar esto en lugar de ser un tamaño de uno, cambia a un tamaño de cinco. Entonces en mi raya de segunda fila, puedo cambiar esto de ser un tamaño de uno a un tamaño de dos. Lo que esto hace es en lugar de alternar colores cada fila, alterna colores cada cinco filas, y luego dos filas, cinco filas, dos filas corresponden a días de semana y fines de semana. Además, también puedes hacer clic en formato aquí para realmente solo ir y dar formato al color de la banda específica si quieres. Sólo voy a pegarle a “Ok” por ahora. Si vuelvo a hacer clic en el desplegable aquí en mis formatos de tabla, puedo hacer clic en este formato personalizado. Te das cuenta de cómo el formato ahora cambia de cada otra fila para ser codificado por colores, para ser primero cinco filas, luego dos filas para resaltar realmente los precios de fin de semana y día de semana de Bitcoin a partir de 2013. Por último, una cosa más sobre el formato de tablas es que a veces es posible que desee codificar por color partes específicas de sus columnas. Volvamos a nuestro formato de tabla regular aquí, que es algo así. Si hace clic en la primera columna, observa cómo resalta automáticamente la primera columna de datos a llamar. Última columna solo resalta la última columna y luego las columnas bandas. Si tienes un tipo específico de formato donde tienes codificación de color por bandas. Volvamos a hacer clic en éste. Observe cómo por defecto tiene filas en bandas. Si hago clic en columnas con bandas y filas con bandas sin marcar, resalta cada otra columna en lugar de filas. Tan solo un poco más de formato adicional que puedes aplicar a tu mesa se basa en las necesidades de tu equipo. Caminamos por cómo puedes personalizar el formato de tu tabla con algunos de los formatos integrados de Excel para tablas de Excel, pero también cómo puedes personalizar el número alterno de colores de fila para tu tabla de Excel. Aprendimos mucho sobre las formas en que puedes usar tablas de Excel para fórmulas, para referencias estructuradas, para formatear. Ahora, vamos a envolver todo en tu proyecto para esta clase. 9. Reflexiones finales: Enhorabuena, has tomado esta clase en tablas de Excel, y aprendimos todo sobre cómo crear tablas de Excel, cómo usar referencias estructuradas en tus fórmulas cotidianas de Excel como VLookup, algunos SUMIFS. Cómo puedes aplicarlos a listas desplegables y validación de datos para que tus tablas en Excel, y tus paneles, e informes sean más fáciles de leer, tus fórmulas son más fáciles de depurar. Tus compañeros estarán mucho más felices cuando estén mirando tus archivos de Excel. Espero que tomes todos los aprendizajes de esta clase y los apliques al proyecto de la clase, donde estarás analizando un conjunto de datos del Departamento de Transporte sobre retrasos de vuelos. Espero que compartas tus hallazgos y capturas de pantalla de tus reportes en la galería de proyectos para que todos los vean. Muchas gracias por tomar esta clase, y espero ver sus proyectos.