Domina las funciones de búsqueda de Excel para el análisis de datos

1. Bienvenido a el curso de búsqueda de miradas: Bienvenido a Excel busca para curso de análisis de datos. Mi nombre es Chen do. En este curso aprenderás a usar fórmulas de Excel como VLookup x lookup, índice match, filtro HLookup. Y cómo combinar estas funciones para diversas situaciones prácticas en tu línea de trabajo. Empezamos con el uso básico de todas las funciones de búsqueda importantes en Excel. Y luego voy a enseñar técnicas como la fórmula de coincidencia de índice. Buscamos cómo hacer todos los resultados coincidentes con una fórmula de búsqueda. Cómo realizar una búsqueda de criterios múltiples. Cómo consolidar datos en tablas utilizando las consultas a punto de realizar la búsqueda en derivar con columnas, y cómo escribir las consultas que devuelven múltiples salidas de columna. También vamos a ver los errores comunes que suceden cuando estás escribiendo locaciones en cómo solucionarlas de manera fácil. En este curso hay mucho contenido valioso, pero todo está tan apretado y producido manera concisa para que aprendas al máximo, en la mínima cantidad de tiempo cada lección viene con un video y un ejemplo. Te sugiero que descargues los libros de trabajo, practiques los conceptos de búsqueda, y aprendas a medida que avanzamos. Hay algunos problemas de ejemplo de tarea para que lo descifres, así como un proyecto de clase usando algunas fórmulas de búsqueda. Te recomiendo encarecidamente que completes estos problemas de tarea y el proyecto de clase y compartas tus resultados con nosotros en el área de la comunidad. Dirijo un popular sitio web de Excel y Power BI llamado Chengdu.org. También dirijo un canal de YouTube con el mismo nombre. Entre mi sitio web y mi canal de YouTube. Ayudo a más de 0.5 millones de personas cada año a convertirse en impresionantes en su línea de trabajo. Es mi misión de vida hacerte impresionante en tu trabajo. He estado haciendo esto desde hace más de 13 años. Vivo en Wellington hermosa pero ocasionalmente ventosa en Nueva Zelanda. Está todo el camino en la esquina del mundo. Cuando no estoy enseñando Excel, me gusta pasar mi tiempo construyendo Lego con mis hijos o rejugando uno de los juegos de Zelda son llevar a nuestro perro, Excel a pasear, o hacer un hermoso y delicioso curry con mi esposa, Joy. Los necesito. Estoy súper emocionado de tenerte en este curso. Te deseo toda la genialidad en los lookups. 2. Conceptos básicos de VLOOKUP, XLOOKUP y HLOOKUP: Bienvenido a nuestra sesión de introducción sobre búsquedas en Excel. En este video, aprenderemos a usar tres de las fórmulas de búsqueda más importantes en Excel. Están arriba, Xp y Hup. En este video, también te presentaré nuestro conjunto de datos de muestra, y a lo largo del resto de la clase, estaremos usando más o menos este conjunto de datos. Se trata de un conjunto de datos de empleados conformado donde tengo sus nombres, género, departamento en el que trabajan, la fecha en que se han incorporado a la organización, y cuánto salario obtienen. Ahora, la primera fórmula que aprenderemos es la fórmula de la hep La Hep es una forma corta para la búsqueda vertical. Te permitirá hacer una pregunta específica sobre tus datos y conseguirte la respuesta. Por ejemplo, dados datos como este, puedo hacer una pregunta como, cuál es el salario de Hussain barrena y arriba te dará respuesta Una forma sencilla de pensar en Weeks, puedes comparar eso ya sea con el puntero del mouse o con el dedo índice. Ahora imagina que esto está impreso y estás escaneando estos datos para encontrar dónde está Hussain Auger Y luego solo vas a través de la pantalla. Para encontrar su salario. Esto es exactamente lo que también hace loop. Ahora, veamos cómo escribir esta función. Para escribir el talón hacia arriba, comienzas diciendo igual a L, y luego abres. Puedes escribir L y luego presionar tecla tabulador y xL escribirá la fórmula, abre el corchete. Entonces el valor de búsqueda es el valor que quieres buscar. Simplemente escriba el nombre augur, y ahora la tabla A es donde están tus datos, selecciona tus datos Entonces el número de índice de columna es la columna en la que se encuentran sus datos. En este caso, quiero buscar usinagur y luego obtener Usinagur es el nombre columna que es el número uno, género es dos, el departamento es tres, fecha conjunta es cuatro y el salario Queremos que se devuelva la quinta columna. Y entonces el último parámetro es si quieres una coincidencia aproximada o exacta. Ahora, en la mayoría de las situaciones de negocios, siempre quieres una coincidencia exacta, así que esto será falso. Y cuando presionas centro, te devolverán su salario como valor. Entonces aquí se puede ver que son 67,910. Eso es lo que dice nuestra fórmula. Ahora, volvamos y observemos esta fórmula. Aquí, hemos estado escribiendo un nombre en códigos dobles, pero alternativamente podría hacer que el nombre aparezca en otra celda, ya sea en esta hoja de trabajo o alguna otra hoja de trabajo y vincularlo también. Hagámoslo escribiendo otro nombre aquí. Voy a copiar esto, pegarlo aquí, y usaremos este nombre para buscar a Jan Morph como sea el nombre en ocho, esa es la celda Queremos el departamento de esa persona. Departamento de esta persona estaría arriba yo ocho mis datos y departamento es columna índice tres caídas. Así es como puedes configurarlo. Y voy a obtener la respuesta de en qué departamento están trabajando. Ahora que entendió el uso básico de la copa de rueda, exploremos la función de búsqueda X, que es como una versión mejorada de la función heel up. Entonces para hacer lo mismo, exactamente lo que hicimos hace un momento para Hoseinager, puedo usar x lookup como esta X. valor es lo que quieres buscar En este caso, barrena y En lugar de seleccionar todos tus datos para Xp, necesitas seleccionar dos conjuntos de datos Es necesario indicarle a EXL dónde los datos que desea encontrar, en este caso, la columna de nombre, así como cuál es la columna de la que desea devolver los resultados coincidentes En este caso, queremos el salario. Entonces no tenemos que seleccionar todos los datos. Simplemente seleccionamos nombre y salario. Eso es, ni siquiera necesitas especificar nada más en el ajuste por defecto, cuando das la columna de nombre y columna de salario, obtendrás el resultado del salario. En cierto modo, XP es más corto de escribir porque solo estás recogiendo los datos necesarios y especificando lo que quieres y EXL te dará el resultado Entonces porque X Loup es una versión mejorada de copa en el resto del curso, siempre que sea posible, te enseñaré los enfoques basados en Xup para hacer los problemas Ahora, escribamos la misma fórmula de departamento de Jan con Xp para que te familiarices más con esto. Vamos a decir arriba. Mi valor de búsqueda está aquí. Mi matriz de búsqueda es la columna de nombre, y mi matriz de retorno es la columna de departamento. Ahora, mientras estás escribiendo la fórmula xp, notas que en realidad hay una opción llamada No encontré. Usemos esto y luego especifiquemos dentro de códigos dobles no encontrados. Obviamente, Jan Morpor ser nuestro empleado, el departamento llega a través Pero como esta es una celda de entrada, puedo ir y cambiar esto. Pongamos esto como endo, y luego veamos eso. Ahora, nuestra fórmula original de taza de rueda devuelve un error hash, mientras que esta fórmula de copa XL, porque hemos especificado lo que queremos cuando no se encuentra el valor, dirá no encontrado. Esta es en realidad otra poderosa aplicación de X lookup. Xp agrega estas características adicionales para que como analista de datos, no tengas que pensar en qué hacer cuando hay un error. Simplemente lidias con eso dentro de la fórmula. Una de las mayores limitaciones de la búsqueda es que solo puede ir de izquierda a derecha. Con eso, a lo que me refiero se le dan los datos así, puedo buscar un nombre y obtener su fecha de incorporación o salario. Pero no puedo buscar un salario y obtener el nombre de la persona a través de Whop porque la hep está levantando la vista en una columna de la izquierda y luego yendo a la Este ha sido un gran punto de dolor para los usuarios de Excel durante más de una década. Es por eso que Microsoft introdujo la función de búsqueda X porque de esa manera, se puede buscar en cualquier columna y devolver otra columna. Busquemos el salario de 48,170. Este es el salario que quiero buscar y la persona será X. En este caso, queremos buscar ese salario en la columna de sueldos y obtener el nombre de la persona. Observe cómo hemos cambiado el orden de estos dos porque esta vez estoy buscando en la columna de sueldos y luego estoy devolviendo el nombre. Al instante, puedo averiguar quién es esa persona y puedes verificar dos veces con tus datos que este es en realidad Jan Morfort Hasta ahora, hemos estado usando Vp y Xp en rangos regulares de celdas de Excel. Si notas estas fórmulas, en todas partes hemos estado seleccionando los datos como C cinco a G 20 o C cinco a C 20 así. Estos se llaman rangos regulares. Pero también puedes usar estas fórmulas cuando tus datos están en una tabla. Cuando tus datos están en formato de tabla o tar, las fórmulas también se vuelven más naturales y sencillas en inglés, esta manera, no tienes que pensar mucho a las fórmulas también se vuelven más naturales y sencillas en inglés, de esta manera, no tienes que pensar mucho a la hora de escribir la fórmula. Usemos lo mismo sobre la mesa. Sólo para que te vuelvas faamiliar con el concepto. Busquemos un nombre. Sólo voy a copiar esto. Ese es el nombre que queremos buscar, y vamos a escribir dos fórmulas. Uno está arriba y el otro es X, que sus salarios. Buscamos en Paty, y mis datos ya están en una tabla llamada staff Todo lo que tengo que hacer es decir personal, esos son todos mis datos, y luego salario es la quinta columna. El último argumento sería falso y vamos a conseguir el salario de Kings. Con X, la fórmula se convierte en X L J ocho nuestra celda de entrada. Nombre del personal y sueldo del personal. La diferencia clave entre arriba y x buscar aquí está en arriba especificas toda la tabla, mientras que en Xp, estás especificando dos columnas individuales, la columna de búsqueda y la columna de retorno, y obtendrás la respuesta. Utilizaré tablas para demostrar el resto de las fórmulas en esta clase porque así es como se mantienen los datos en situaciones de negocios. Si bien la fórmula up funciona, cuando tus datos están orientados verticalmente como valores que bajan por la pantalla, si tienes valores que van por la pantalla, necesitas usar la fórmula up. Echemos un vistazo a eso también. Digamos que obtuvimos nuestros datos de ventas del último año en un formato de matriz como este. Entonces obtuve mi nombre y luego obtuve mis valores de ventas. Y solo quiero saber cuáles son las ventas de junio para Doughty strutn Pongamos aquí el nombre del mes, junio en una celda. Observe la celda C seis, y escribiremos dos fórmulas. Una es una búsqueda de dH y la otra es Xp, porque Xp también es capaz de hacer búsquedas horizontales Otra razón por la que X Loup es mejor que las búsquedas tanto el cup como las de setos Empecemos por el seto aquí arriba. Hetch el valor de búsqueda, que es mi junio y tabla Ahora bien, debido a que los nombres de los meses están en la parte principal de la tabla, necesitamos seleccionar realmente toda la tabla, no solo la parte de datos, modo que eso se convertirá en hash de ventas A. Y luego necesitamos especificar el número de índice de fila porque tenemos que buscar junio, pero bajar y obtener los datos de una fila específica que pertenece a Doughty strut En este caso, Doughty pasa a estar en la fila número 13, si se cuenta de nombre en adelante, decimos 13, y luego decimos cae La sintaxis es H y siguen el mismo patrón, pero en H tu orientación de datos es horizontal, obtendrás la respuesta, que es exactamente lo que son las ventas de junio para Doughty strut Ahora hagamos lo mismo con X. X hasta junio. Esta vez, desea especificar la fila de encabezado de la tabla porque quiero buscar June en los encabezados de la tabla de ventas, y quiero obtener los datos de Doughty Strutle Seleccionamos la fila que corresponde a Doughty y cerramos el corchete y volveremos a obtener el mismo número En este punto, tal vez te estés preguntando, él Chen, esto es bueno. Pero y si tengo dos insumos, tengo que tener el insumo de junio, pero también quiero tener un insumo de Doughty Strut, poder buscar las ventas de mayo para Andrea Kimpton o algo más Tales búsquedas se llaman búsquedas de dos vías o dos D, y tengo una lección en el plan de clase que habla de esa técnica también Así que quédate y mira eso también. Por, 3. La fórmula INDEX+MATCH: Bienvenido a LevelUp Excel Loops Class. En este video, entenderemos la fórmula de coincidencia de índices. Digamos que tiene una fecha unida en una celda y sus datos están sentados aquí. Te gustaría saber cuál es el nombre de la persona que se incorporó en esta fecha. Ahora, como se explicó en el video anterior, podrías usar la función Xp para hacer esto. Pero digamos que o no puedes usar X Loup porque no estás en EXL 365 o no quieres usar X Entonces, ¿cómo respondes a esta pregunta? Para obtener la respuesta para eso, necesitas hacerlo en dos pasos porque la función p irá de izquierda a derecha, así que realmente no podemos buscar la fecha e ir al lado derecho para obtener el nombre. El paso número uno es encontrar dónde ocurre esta fecha en esta lista y obtener la posición de esa fecha. Esta es en realidad la quinta fecha de la lista, por lo que es el quinto ítem. El paso número dos es obtener el quinto nombre. El problema se vuelve doble, encuentra la posición y consigue el artículo correspondiente Para encontrar la posición de un ítem en una lista, podemos usar la función match. Bien. Así que empareja un valor en una lista de valores. Entonces en este caso, nuestra lista de valores la fecha del personal se unió, y cómo quieres igualarla, siempre quieres hacer una coincidencia exacta menos que estés haciendo algo muy específico. Entonces el último argumento se convierte en cero y cierra el paréntesis y obtendrás la posición de esa fecha. Sea cual sea esta fecha, si tiene una posición en esta lista, aparecerá. Por lo que cinco el 19 de abril se convierte en cinco, pero el 18 de mayo de 2018 se convierte en la octava fecha. Ahora, sigamos adelante y busquemos el nombre de la persona. Conozco la posición. Quiero obtener el octavo nombre o el quinto ítem de la lista. Esto es cuando se usa el otro índice de función. Indique, una lista, termine el ítem que desee. Ocho. Esto te dará como nombre Andrea Kimpton Y si vuelves a cambiar a tu fecha original, cinco, 19 de abril, obtendremos el nombre de Curtis Advani Juntas, estas dos fórmulas crean una nueva construcción llamada fórmula de coincidencia de índice. La forma en que esto funciona es que escribes índice rápido, especificas la lista de la que quieres obtener eso. Estamos mirando la fecha y queremos el nombre. Entonces comenzamos con el nombre, y luego escribimos la función match. Nosotros decimos, esta es la fecha que estoy buscando. En la columna de fecha con una coincidencia exacta, y obtendremos el nombre. Ahora que entiende la fórmula de coincidencia de índices, veamos algunos reemplazos para esto El reemplazo número uno que debes probar es el de búsqueda X. Ya lo hemos visto en el video anterior. Solo volveré a hacer una demostración rápida. Esa fecha, en la lista de fecha se unió, y queremos el nombre. Vamos a buscar a Curtis Sadvan. Otra opción que podrías usar es index plus x match. La función x match se forma en la misma familia que x. Es una de las funciones más nuevas, y esto funciona como este nombre de índice, fecha de coincidencia en la junta de fecha. Para x match no es necesario especificar el modo de coincidencia. Siempre será coincidencia exacta por defecto, volverás a obtener Curtis Avan Así es como funcionan las funciones de coincidencia de índice 4. Búsqueda de dos forma (fila y columna): En esta lección, entendamos cómo realizar búsquedas bidireccionales usando Excel Te voy a mostrar tres técnicas diferentes, y dependiendo de cómo estén disponibles tus datos y qué versión de Excel estés usando, usa uno de estos métodos. Para este problema de ejemplo, nos gustaría averiguar las ventas en marzo por botella de ajedrez. Este es el valor que estamos tratando de obtener. La primera técnica que utilizaremos es la técnica de coincidencia de índice. Ya sabemos que la función index se puede utilizar para obtener un valor particular en una lista. Si digo nombres y después doy cuatro, obtendré el cuarto nombre, que es GG. Pero la función index tiene otro uso. Si proporciona una A grande, entonces un rango grande en lugar de solo una lista, si selecciono toda esta tabla, la tabla de ventas, y luego digo cuatro K tres, obtendré el valor en la fila número cuatro, columna número tres. Entonces obtendremos las ventas de Gigi Boling en febrero porque esta es la columna uno, dos, tres, y ese es el número 1234, y terminaremos con Podemos usar este método para averiguar cuál es el valor aquí simplemente usando dos fórmulas de coincidencia donde número cuatro y tres están disponibles. Probemos esto aquí. Diremos índice. Necesitamos seleccionar solo la porción de datos de nuestra tabla. No desea seleccionar el nombre también porque entonces sus fórmulas de búsqueda deben ser ligeramente diferentes. Y luego decir partido el partido primero estará en el nivel de fila, así que queremos igualar el bonel de ajedrez en todos los nombres con una coincidencia exacta La segunda fórmula de partido que queremos hacer es igualar marzo en la lista de meses. Simplemente seleccione los meses en su tabla, y nuevamente, una coincidencia exacta y cierre la fórmula del índice. Observe cómo se configura este índice de sus datos y una coincidencia en el elemento de nivel de fila y una coincidencia en los elementos de nivel de columna. Y llegaremos a los 36,415 como respuesta. El segundo método para hacer esto es de lejos mi favorito también, que es usar dos funciones X up. fórmula de copa EXL, como ya has visto en videos anteriores, es un reemplazo para arriba, pero también tiene otra superpotencia Lo demostraré para que entiendas a lo que me refiero con esto. Entonces decimos arriba. Busquemos ajedrez borel en la columna de nombre, y devolvamos toda la tabla de ventas Entonces, en lugar de especificar una sola columna como ventas de Jan o ventas de marzo, decimos buscar nombre C siete en la columna de nombres, y luego obtener toda la tabla. Lo que esto hará es que te dará toda esa fila para biberón en el pecho. Excel 2000 x 3605 tiene esta hermosa característica de derrame, lo que hará es que imprimirá el valor y se cuenta de que en realidad hay 13 valores enteros, por lo que simplemente extenderá el resultado a esas 13 celdas automáticamente Este es uno de los poderes ocultos de la copa X. En realidad, puede devolver un rango completo de valores en lugar de un solo valor. Ahora todo lo que tengo que hacer es conseguir ese valor. Ahora que tenemos toda esta lista, si envío esto a otra copa X L, y después pido el valor de marzo. Conseguirás esa. Entonces empezamos con X. Buscaremos el bonel de pecho en la columna de nombre y devolveremos uno de estos valores Esto cuando presionas al centro , te dará todos los 12 valores. No te va a dar el nombre, pero te va a dar todo. Se puede ver que esos valores van a pasar así. Usaremos otra x x. Esta vez estamos buscando marzo en los nombres de los meses, y luego devolveremos el valor correspondiente. Entonces eso te dará 36 415, lo que de nuevo es lo mismo. Entendamos un poco de cerca esta fórmula. Entonces, como se puede ver, estamos usando dos X lops. Este xp interno te dará todos los 12 valores correspondientes al bonel de pecho Si seleccionas todo esto y presionas control igual a, puedo ver que devolverá esa matriz y este bucle x externo buscará entonces march en los encabezados. Por lo que encuentra que March es el tercer encabezado, por lo que obtendrá el tercer valor correspondiente a partir de ese, que será ese valor. Así es como funciona este método Xp x loup. Aquí hay un truco de bonificación. Podrías en lugar de buscar primero el número de fila, buscar primero la columna y luego volver a buscar la fila. Así es como funcionaría eso. Diremos x look , buscaremos march en los encabezados, y luego obtendremos los valores para march. Como puedes adivinar lo que hace esta función es, te dará todos los valores de marcha para todos. Entonces esta x mira hacia arriba, puedo enviarla a una X X más buscar bonel de ajedrez en la columna de nombre, y luego obtener el valor correspondiente de eso Esto también funcionará. No importa en qué dirección vayas rápido, puedes ir fila rápido y luego columna o columna rápido y luego remar. Mismo resultado. Ahora veamos el tercer método, que en realidad es una mezcla de uno y dos. Podemos usar index x match x match porque x match function es una versión más corta de la función match, puedo usar eso. Diré indexar mis datos, solo la parte del número, X match chess bonel en la columna de nombres, X match marzo en las cabeceras del mes, y obtendremos la misma respuesta Ahí van tres métodos diferentes. Ahora bien, si te estás preguntando qué método usar, si estás usando Excel 3605, este debería ser tu método preferido porque es bastante sencillo y utiliza métodos modernos de hacer las cosas Pero si estás usando una versión anterior de Excel, este se convierte en tu enfoque predeterminado. Por diversión, también podrías probar el número tres. Cualquiera que sea adecuado, puedes probarlos. Espero que le haya resultado útil todo esto. Nos vemos de nuevo en el siguiente video. 5. Todos los resultados coincidiendo con Lookup: Una de las limitaciones clave de X L p o cualquier otro tipo de funciones de búsqueda en EXL's. Solo pueden buscar el primer resultado coincidente. Digamos que estás buscando el departamento de finanzas y quieres encontrar a todas las personas de ese departamento. Aquí tienes tu fórmula de búsqueda, X L, el valor en el departamento y nombre, y en el momento en que pones finanzas, simplemente encontrará a la primera persona, que es Curtis Advani e imprimirá que no hay otra manera de recuperar estos otros nombres a través de las funciones de búsqueda En este video, permítame demostrar una técnica para generar todos los resultados de coincidencia posibles cuando escribe un departamento. Si digo ventas, voy a conseguir a toda la gente en el sitio web del departamento de ventas, voy a conseguir sitio web, si pongo un departamento que no existe en nuestros datos, no obtendré resultados. Te voy a mostrar dos técnicas, una para hacer esto con XCL 3605 y otra para versión antigua de Excel Vamos a ver cómo se puede hacer esto. Excel 3605 introdujo un nuevo conjunto de funciones llamadas funciones de matriz dinámica Una de estas funciones se conoce como filtro. Lo que hace la función de filtro es, puede tomar algunos datos y puede filtrar en función de las condiciones que desee. Podemos usar la función de filtro como una función de búsqueda en EXCL 365 Por ejemplo, puedes decir datos de personal donde departamento de personal es igual a y luego en códigos dobles finanzas, y esto prácticamente te dará un subconjunto de datos que pertenece solo a la gente de finanzas. Por lo que es casi como si aplicaras filtros en los datos y escojas finanzas. Sea cual sea ese resultado, eso es lo que estás consiguiendo aquí. Pero esta es una fórmula. Para que lo puedas parametrizar, puedes cambiar la forma en que aparece, y todo eso puedes cambiar la forma en que aparece, y todo Entonces así es como he hecho esta parte. Puse aquí mis criterios de entrada para poder escribir lo que quiera. Y aquí he escrito una función de filtro simplemente diciendo filtro staff staff department es igual a d tres, que es mi celda de entrada. Y si no hay resultados, simplemente imprima la palabra sin resultados, y así es como los resultados llegarán a través. Una de las limitaciones de la función de filtro es que no devolverá los datos según el formato original Aquí, las fechas y las monedas están muy bien formateadas, mientras que aquí la fecha aparecerá como un número, también lo es la moneda. Tendrás que seleccionar las celdas donde podrían aparecer tus resultados y aplicar la regla de formato de antemano. Observe que cada vez que escribe la función de filtro, es una de esas funciones de matriz dinámica. Automáticamente, dependiendo del tamaño de tu resultado, Excel encogerá o crecer el área de resultados de esta fórmula. Esto se llama rango de derrames. Y cuando tengas una fórmula que tenga un rango de derrames, Excel lo resaltará con esta caja de color azul. Entonces, cualquier parte del conjunto de resultados que selecciones, Excel mostrará ese rectángulo azul alrededor de ese dicho, todo esto es parte del rango de derrames. Puede examinar la fórmula que devuelve el rango de derrame seleccionando la celda y buscando en la barra de fórmulas. Ahora, la barra de fórmulas gris la fórmula si selecciona alguna celda. Pero si seleccionas la celda superior izquierda del rango de derrames, verás que es editable, así que en realidad puedo ir y cambiar la fórmula también Así es como puede usar la función de filtro para devolver todos los resultados coincidentes. Déjame mostrarte otro ejemplo. Digamos que no quiero ver todos sus detalles. Yo sólo quiero los nombres de la gente en el departamento de finanzas. Aquí es cómo podríamos hacer eso. Vamos a decir nombres, y luego vamos a decir filtrar nombres de personal. Entonces esta vez, solo quiero que el nombre del personal se filtre en función el departamento de personal sea igual a T tres, y solo voy a obtener los nombres. Aparte del filtro, Microsoft también introdujo otras fórmulas como sort uniq, etcétera. Entonces, por ejemplo, puedo enviar los resultados de este filtro a una función de ordenación para que pueda ver estos nombres en orden alfabético. Esto es muy útil, especialmente para tales cosas de visualización donde sus datos podrían estar en cualquier orden, pero las salidas están en un orden muy bien ordenado. Si estás usando Office 3605, recomiendo encarecidamente que pruebes estas funciones e implementarlas en tus flujos Vamos a entender cómo hacer lo mismo en una versión anterior de Excel. El conjunto de fórmulas que vamos a utilizar en esta parte del video son un poco más avanzadas y complicadas si nunca has hecho este tipo de cosas. Pero te recomiendo que lo veas y veas cuáles son las ideas clave que puedes llevar e implementar en tus flujos de trabajo. Si pongo aquí el nombre del departamento, obtendré los resultados. A los efectos de este ejemplo, sólo estoy mostrando los cinco primeros resultados. Si pongo ventas, obtendré los dos primeros y los resultados, y si pongo sitio web, obtendré los primeros cinco resultados. Para conseguir este trabajo, primero tenemos que entender algunas de las cosas internas. Entonces, sigamos adelante y examinemos esos primero. Así que ya configuré algunos cálculos en la hoja de trabajo y simplemente los escondí. Vamos a desenvolver esto. Entonces obtuvimos una celda de entrada donde voy a escribir mi departamento que quiero ver. Esto es D tres en la hoja de trabajo de Excel antigua de coincidencias A. El primer paso es averiguar si D tres en esa hoja es igual a uno de estos valores. Entonces donde sea igual, quiero devolver el número de fila particular en la tabla. Aquí es donde utilizo una forma sencilla de vida. Personal en el departamento de tarifas que es el departamento en esta fila es igual a mi D tres. Entonces quiero obtener el número de fila de esa celda en particular. Aquí es donde entra la función de fila. Fila de personal en el departamento menos el número de fila del valor de cabecera. De esta manera, puedo generar un número relativo. De lo contrario, esto va a quedar en blanco. Aquí voy a conseguir para este sitio web en particular persona dos, tres, seis, siete, ocho, nueve y 12. Dondequiera que no concuerde, simplemente quedará en blanco. Podemos pensar en esto ya que tengo una lista de números, y solo quiero averiguar cuáles son los primeros cinco números pequeños porque sólo estamos mostrando los primeros cinco resultados. Aquí es donde genero los números del uno al cinco. Simplemente escríbalos en la celda. Y quiero averiguar cuál es el primer número más pequeño, el segundo, el número más pequeño así. Excel también tiene otra función llamada small, y puedes usarla para hacer esto. Dices pequeño de este rectángulo azul. Esto tiene todos mis números, y cuál es el primero, el número más pequeño, el segundo, el número más pequeño. Una vez que tenemos estos, todo lo que tenemos que hacer es obtener toda la segunda fila, obtener toda la tercera fila, obtener toda la sexta fila así. Para ello, podríamos usar la función index. Te mostraré cómo obtener un índice de fila específico. Entonces diremos índice, datos. Entonces en este caso, staff, y el número de fila que queremos es dos. Queremos todas las columnas esta tabla que está en la segunda fila. Cuando quieras todas las columnas, solo puedes decir coma e ignorar la parte del número de columna de la fórmula del índice. Si simplemente dices dos, no obtendrás la respuesta, necesitas decir coma y luego cerrar el corchete En este punto, lo que hace la función index es que te dará toda la segunda fila. Y debido a que EXL 3635 tiene una funcionalidad de derrame, en realidad tomará el valor y lo derramará Pero en versiones anteriores de X, selecciona todas estas cinco celdas, toma eso y luego presiona control shift enter para obtener el resultado. Pero ahora esta función se va en cinco celdas, mostrando cada celda el valor de columna correspondiente. Este es el método que estoy usando aquí. He generado los números de identificación 23678, y luego los enchufo aquí en estas celdas, cinco celdas, y aquí solo estoy usando índice de esos datos y luego dejo eso Esta parte va a generar esa fila entera y estamos imprimiendo que a través de control shift enter. Ahora, sólo hay un problema extra, que es lo que si el departamento no existe. Entonces, por ejemplo, ponemos RRHH. Estos datos no están ahí en nuestra tabla de personal. Entonces necesitamos imprimir un resultado como ningún resultado o algo así. Aquí es donde estoy usando la función de error. Si mi cosa de la fórmula de índice está saliendo como un error, imprima este mensaje amigable. Así es como puedes hacer todos los resultados de procesamiento por lotes en versiones anteriores de Excel Si bien esta técnica en particular no es relevante para los usuarios de EXL 365 porque tienes la mejor fórmula de filtro Siento que aprender este uso particular de la fórmula de índice puede ayudarte en otras situaciones. Espero que todo esto haya sido de mucha ayuda. Te veré de nuevo en la siguiente lección. 6. Lookups de columnas Derived: En este video, entendamos cómo hacer búsquedas cuando el criterio o el cálculo es un poco más complicado A estos los llamo como una búsqueda en columnas derivadas. Examinaremos cómo escribir fórmulas de búsqueda para estas cinco situaciones. Y luego al final del video, también te pediré que trabajes en tres problemas más como tarea. Vamos uno a la vez. Queremos conocer a la persona con salario máximo. Es decir, me gustaría averiguar el nombre de esta persona, pero todo a través de fórmulas. Número uno, se convertirá en el valor que estás buscando es el máximo en la columna salarial. Entonces dirías ya sea x búsqueda o arriba. Vayamos con la búsqueda X. Máximo del sueldo. Este es el valor que quiero buscar en la columna de sueldos del personal. Y luego queremos obtener su nombre, así que diremos el nombre del personal. Eso te dará el nombre de Plaqans. El segundo es el alquiler de pruebas En este caso, queremos averiguar quién es la última persona en unirse a la organización. Entonces eso significa que quien tenga la fecha se unió que es la última, esa es la persona a la que nos gustaría reunir. Y nuevamente, la fórmula se vuelve muy sencilla una vez que entiendes la lógica. Decimos p máximo en la columna de fecha unida, porque técnicamente, el fechas son números, por lo que podemos usar Max para hacer lo último destinado a hacer lo más temprano. Y luego diremos personal fecha se unió. Y nuevamente, el nombre del personal para obtener su nombre. Y vamos a averiguar quién es esa persona. En este caso, es Barfony el seis de octubre de 2019. La tercera es, nos gustaría conseguir a la persona que se unió a nosotros en el año 2017. Entonces sí tenemos un valor de búsqueda, que es 2017 pero primero necesitamos calcular el año a partir de la fecha conjunta. Fecha conjunta contiene la fecha completa y solo queremos buscar en base a solo el componente año de la misma. Da la casualidad de que solo hay un valor, pero si hay múltiples valores, entonces como miramos hacia arriba o X lo haría, ellos solo te darán el primer resultado. Aquí es exactamente donde x up realmente brilla porque puedes hacer esos cálculos derivados fácilmente Diremos que buscar arriba es 2017, y la búsqueda ra es Es fecha de incorporación, pero no es directamente fecha de incorporación. No podemos usar la fecha de incorporación como tal. En cambio, queremos tomar la fecha de incorporación y enviarla a la función de oído para que podamos averiguar todos los años a partir de esto. Cuando el oído lee esto, generará una lista o una matriz de números que irán como 2000 1919 1918 así, y luego queremos devolver el nombre. Te voy a dar Jan Morfor quien es la persona que se unió en 2017 Este es un constructo muy poderoso. Entonces voy a demostrar esto nuevo con el cuarto ejemplo, que es que las tres primeras letras son RAF. Aquí, se vuelve a proporcionar el valor que quiero buscar. Queremos buscar RAF. Pero el para Luca está en el nombre. Pero en lugar de solo el nombre, queremos sólo las tres primeras letras de la columna del nombre. Podemos usar una función como left en la columna name. Diremos izquierda de nombre tres, y luego eso me dará las tres primeras letras como una matriz, y luego queremos obtener su nombre. Esto te dará otra vez afata. Ahora, vamos a entender cómo va a funcionar la izquierda interna. Selecciono esta porción y la evalúo sola con control igual a, y puedo ver todas las tres letras, bar Dongg eso Entonces, debido a que estas son las tres letras, copa XL intentará entonces igualar a RAF en eso y coincidirá con el valor, y luego dará el nombre correspondiente. El quinto ejemplo que necesitamos calcular es el salario es de 88 mil Ahora, se puede ver por la columna salarial que no existe tal persona. Y efectivamente, cuando haces un X Lp o incluso subes a 88,000 y solo dices, quieres el salario y quieres el nombre, obtendrás un error porque no existe esa persona Pero lo real que queremos ir a buscar es esta persona, el bonel de pecho La pregunta ahora se convierte en, primero tenemos que llevar el salario alrededor él a 2000 y luego hacer el partido. Así es como puedes hacerlo. Se puede tomar X L buscar 88, y luego tomar el sueldo en sí, enviarlo a la función de ronda. Tomamos la columna salarial, luego decimos ronda de sueldo del personal dividido por 1,000 a cero dígitos. Esto básicamente redondeará a todos a sus miles, 88,050 dólares se convierten en 88, y luego queremos el nombre, y eso te dará botella Este es otro ejemplo. Estos tres ejemplos siguen el mismo patrón, pero luego te muestran que X L puede tomar cálculos en lugar de columnas directas, ahí el nombre de esta lección derivó columnas, como prometí, sí tengo tres problemas de tarea para ti. Quiero que se entere de la persona con el salario mínimo, persona cuyas dos letras de las dos últimas letras del nombre son ND y persona que se incorporó en el mes de agosto. No importa en qué año. Todo lo que sabemos es que se han unido en el mes de agosto y solo queremos obtener sus nombres. Descubre las fórmulas de talonera o las fórmulas de copa Excel que te darán estas respuestas y compártelas en la sección de comentarios. Eso es todo por ahora. Espero que hayan disfrutado de esta lección. Nos vemos en la siguiente. Adiós. 7. Múltiples criterios (multi-condition): En este video, vamos a aprender a construir una búsqueda de múltiples criterios usando Excel. Específicamente, crearemos algo como esto en el que pueda ingresar un vendedor y un nombre de país, y luego obtendrá estos resultados Hagamos una demostración rápida aquí. Voy a cambiar esto al ajedrez y dejar el país como Reino Unido, y se puede ver que la fórmula que he escrito ha calculado que hay dos registros, y está mostrando el primero, que también se resalta aquí. Entonces, ¿cómo hacemos una búsqueda basada en múltiples condiciones? Ya sé que si uso Xp o algo así. Entonces por ejemplo, X arriba y luego decir ajedrez en la columna de nombre de mi tabla de ventas y luego obtener la cantidad, voy a obtener la primera cantidad para ajedrez. Por lo que no será necesariamente en la cantidad del Reino Unido. Quiero decir, en este caso, sería Reino Unido porque ese es el primer valor. Pero podría ser otra cosa dependiendo de cómo se ajusten tus datos. Entonces, por ejemplo, si cambiamos esto a bar, entonces queremos la cantidad de barras UK, que es 2499, pero mi fórmula X L me dará barras cantidad USA porque ese es el primer valor Entonces, ¿cómo hacemos múltiples condiciones? Bueno, la técnica es utilizar el concepto de columnas derivadas que han cubierto en el video anterior, y extender eso. Por ejemplo, diremos X look, buscaremos uno, y luego la matriz de búsqueda necesita ser corchete abierto. Nombre columna es igual a mi nombre, que es una L seis. Multiplicar eso con la columna país es igual a mi país. Entonces la columna que regresa debería ser o porque eso es lo que queremos buscar. Ahora, cuando presentas, obtendrás el resultado correcto, que sería 2499 Pero, ¿cómo funciona esto? Bien. Bueno, vamos a entender esto. Aquí estamos diciendo buscar uno, y luego estamos tomando una lista, que es mi columna de nombre de ventas, y luego comprobando eso contra L seis. Si solo hago este bit, y luego lo evalúo para evaluar una porción de fórmula, puede seleccionarlo y presionar control igual a o la tecla F nueve, y obtendrá los resultados de la evaluación. Sería cierto donde sea que la persona esté bar. Entonces puedes ver que el segundo valor es cierto porque estamos comprobando contra barra, y luego el quinto valor también es cierto porque la quinta persona es bar. esta misma manera, esta lista también será un montón de valores verdaderos y falsos. Cuando tomas aquí un montón de verdaderos valores falsos, multiplícalos con los verdaderos valores falsos ahí. Dondequiera que los valores correspondientes sean verdaderos en ambas listas. Eso significa que el nombre es igual a bar es cierto. País es igual a Reino Unido también es cierto. El resultado neto será dos veces cierto, que será uno en el mundo Excel. Porque Excel trata true y cae como cero, uno y cero, así que una vez uno se convierte en uno. Por eso estamos buscando uno. Estamos diciendo buscar uno y luego hacer toda esta multiplicación aquí, y luego obtener la cantidad Dondequiera que sea uno, producirá una lista de unos y ceros, todo esto Si selecciono todo esto y presiono control igual a, se puede ver que esto en realidad es cero todo el camino excepto el quinto disco, que es bar y Reino Unido. El siguiente look up dirá, encontré el ítem coincidente en la quinta posición, así que obtendrá el monto correspondiente de la columna de cantidad, que sería mi 2499 Así es como he escrito esa fórmula. único bit adicional que he hecho es he puesto en información, no hay mensaje de información cada vez que no se encuentra nada. Entonces hice lo mismo con los clientes y las cajas. Cuando pongamos esto, me va a dar ese resultado. Ahora probemos bar USA en donde tenemos múltiples registros. Tenemos tres registros. Debido a que XL solo encontrará el primero, estoy mostrando un mensaje opcional aquí e imprimiendo los primeros detalles del registro solo. ¿Estoy calculando el número de registros? Bueno, esto es muy sencillo. Podemos usar la función countifs para buscar cuántas veces aparecieron la combinación bar y USA. Vamos a decir nombre es bar, país es USA y luego me van a dar tres veces. Aquí estoy diciendo que si mi número de registros es mayor que uno, entonces solo imprima el mensaje mostrando el primer registro, de lo contrario manténgase callado. Así es como se construye eso. Puedes tomar esta técnica en particular y extenderla a cualquier cantidad de situaciones. Ahora aquí hay un proyecto extra para ti? Se puede tratar esto como un proyecto de clase. ¿Cómo traerías los tres discos y los mostrarías? En lugar de uno, quiero que muestres el número dos y el número tres debajo usando las otras técnicas que hemos cubierto en los videos anteriores. Trate eso como un desafío, y luego, si tiene algún problema, vuelva a consultar el libro de trabajo de la solución en los enlaces de descripción Nos vemos de nuevo en el siguiente video. Adiós. 8. Hacer que los IFs anidados: En esta lección, voy a demostrar cómo calcular el bono usando la fórmula de búsqueda. Aquí tengo los datos de nuestros empleados, y nos gustaría ofrecer un bono basado en estas reglas Sueldo hasta $60,000, obtendrás 5% de bonificación hasta 75, Y si tu salario es de hasta 90.000, obtienes 3% algo por encima, obtienes 2% Entonces, ¿cómo calculamos el bono? Podría tener la tentación de escribir una fórmula anidada larga. Pero puedes usar una función de búsqueda más corta para hacer esto. Antes de hacer eso, necesitamos configurar realmente nuestros datos de bonificación en un formato de tabla. Quieres ajustar tus datos de tal manera que el ítem rápido sea cero, y te diría cuál es el bono por eso. La forma de leer esto es de cero a 60,000, eso lo consigues. 60 a 75, obtienes esto, 75 90, obtienes eso. Cualquier cosa por encima de 90, se obtiene esto. Así es como quieres ajustar tus datos y crear una tabla de mapeo. Una vez que eso está ahí, puedes usar la función de búsqueda así. , Observe que esto no es arriba, búsqueda H, o búsqueda x, es una función de búsqueda simple, y seleccione el salario, y luego buscar vector es la primera columna de su tabla, y luego resultado es la segunda columna de esta tabla. Ahora bien, si estos datos no están en formato de tabla, están en rangos de venta. Asegúrese de que está haciendo todos estos como referencias absolutas cambiándolos al formato dólar. Puede seleccionar toda la gama y presionar F four, y EXL agregará los dólares necesarios para usted Una vez hecho esto, cierra el soporte, pulsa enter, y EXL calculará los porcentajes de bonificación necesarios e imprimirlos ahí. Vamos a verificar dos veces. Esta persona está recibiendo 75 mil sueldo, deberían estar en 3% porque están por encima de 75 mil no hasta 75 mil, por lo que caerán en Mientras que esta persona está ganando más de 90 mil, por lo que van a entrar en 2% Esa persona es menor de 60 mil, por lo que obtienen el 5% así Así es como puedo calcular mi porcentaje de bonificación. También puedo calcular mi monto en dólares de bonificación ya sea tomando esto y multiplicando así o cambiando esta fórmula y tomando el resultado de búsqueda multiplicando con ese Siga adelante y use función de búsqueda en lugar de fórmulas anidadas Lo único de precaución que debes tener en cuenta es esta tabla debe ser ordenada en orden ascendente. Realmente no puedes ponerlos fuera de servicio. Es necesario pasar de cero y establecer los límites claramente para que Excel pueda presentar correctamente los valores . Te volveré a ver en el siguiente video. 9. Una búsqueda y múltiples columnas como resultado: En este video, te voy a mostrar cómo usar tanto x loop up para buscar múltiples columnas con una sola función Entonces para ello, pondremos nuestro nombre de búsqueda en la celda J four, e instantáneamente veremos todos los detalles de ese empleado ya sea horizontal o verticalmente. Entonces vamos a entender esto. Yo sólo voy a escribir botella de ajedrez aquí, y al instante, obtendré todos los resultados. Ahora bien, esto no es en realidad cinco fórmulas. Esta es una fórmula única. Entonces, ¿cómo funciona? Empecemos de cero. Decimos x valor de búsqueda, y luego dónde está el elemento de búsqueda, entonces está en el nombre. Pero cuando se trata de devolver array, en lugar de seleccionar una sola columna, seleccionaremos toda la tabla y luego cerraremos el corchete. Observe que la columna de retorno no está restringida a una sola columna, le dará toda la fila de bonel de ajedrez aquí, y luego la imprimirá muy bien a lo largo de la pantalla Esta es una funcionalidad que se introduce en EXL 3605 junto con la funcionalidad de copa L. Así que cada vez que escribas la copa clo y proporcionas más de una columna como criterio de salida, automáticamente tomará los valores y los extenderá en la pantalla. Este comportamiento se llama comportamiento ortográfico, y toda esta funcionalidad se llama funcionalidad de matriz dinámica. También hemos visto esto en otra parte del ejemplo de fórmula de filtro. Si bien esto es bueno, ¿cómo tomo eso y lo vuelvo vertical? Bueno, puedes usar eso junto con la función de transposición. Aquí estoy haciendo eso. De nuevo, lo voy a mostrar desde cero. Digamos x, bonnle de pecho en la columna del nombre. No consigamos toda la mesa. En cambio, consigamos solo el género a través del salario. Esta es la columna de retorno, y obtendremos cuatro resultados pasando por la pantalla. Ahora, en lugar de ir a través de la pantalla, entonces podemos usar la función de transposición al principio de esto y enviar los resultados de copa a la función de transposición Lo que hace la transposición es, tomará un montón de valores y cambiará la orientación Si le envías un montón de filas, las convertirá en columnas, viceversa. Aquí la transposición toma eso y los volteará y luego los mostrará a través de la pantalla Ahora, veamos esto aplicado para un formato diferente de datos. Aquí, obtuve mis nombres, y luego cada mes, los valores de ventas se listan en formato de matriz. Y quiero saber por un nombre dado, cual es el Q q total. Q q aquí sería octubre, noviembre, diciembre. Aquí está mi fórmula de copa XL. Suma de Andrea Kimpton taza de un Kimpton en la columna de nombre, y luego queremos que los resultados lleguen de octubre a diciembre Y luego una vez que la copa da esos tres valores, simplemente los resumimos. También puedes usar esto con el buen bucle viejo así. Dirías suma de búsqueda Andrea Kimpton en la tabla de ventas, y la devolución debe ser de 11, 12 y 13 columnas entre corchetes. Y entonces dirás falso. Lo que bucle hará en esta situación es que devolverá los tres valores como una lista, y luego sum lo resumirá. Ahora bien, si estás usando esta función dentro de Excel 365, puedes simplemente presionar enter. Pero en una versión anterior de Excel, aún puedes usar esta construcción, pero debes presionar control, shift, enter para obtener el resultado. Te volveré a ver en el siguiente video. 10. Combina dos tablas (consolidación) con Lookup: En esta lección, entenderemos cómo combinar dos tablas usando la fórmula de búsqueda. Aquí tengo un conjunto de datos de empleados un poco más largo con 1,000 empleados, y tenemos varias columnas de información de empleados. Pero también tenemos otra parte del rompecabezas aquí con su nombre y fecha de nacimiento disponibles en una tabla separada, y nos gustaría fusionar estas dos para crear una vista combinada. Entonces aquí, por ejemplo, puedo decir fecha de nacimiento, y luego usar ya sea up o x lookup. Entonces diremos X, mi valor de búsqueda es nombre, y luego la matriz de búsqueda es la columna de nombre en el DobStable y la columna de retorno es mi fecha de nacimiento DOBS Y al cerrar esto, obtendrás algunos valores, algunos lugares donde el empleado no tiene una fecha de nacimiento correspondiente, obtendrás un error. Solo arreglemos primero el error para que podamos ir y luego dejarlo en blanco o poner algo más como la fecha de nacimiento que falta, y luego seleccionar toda esta columna, aplicar rápidamente un formato de fecha en ella, y tendremos nuestra información de fecha de nacimiento disponible. Entonces así es como puedes usar la búsqueda Vp o X para combinar dos tablas. Ahora, vamos a hacerlo un poco más complicado. Y si tu tabla de fecha de nacimiento no tiene el nombre, pero tiene apellido y nombre como dos columnas separadas y el valor de fecha de nacimiento. Aquí te explicamos cómo puedes hacer esto. Agregar una nueva columna. Llamemos a esto DO dos. En esta columna, escribiremos una fórmula de búsqueda para que podamos tomar el nombre completo aquí y compararlo con la combinación de apellido y nombre ahí. Tomemos nota de este nombre estable. Estable se llama DOB S dos. Podemos escribir la fórmula aquí directamente ahora X Loop valor es mi nombre. La matriz de búsqueda debe ser la combinación de columnas de nombre rápido y apellido de mi tabla DOBS dos Esto sería DOB, fname, ampersend space, y luego ampersen Observe cómo con el propio Luka para, estamos tomando dos columnas diferentes de esa tabla y combinándolas, y luego la columna de retorno tiene que ser DOBS dos fecha de nacimiento Entonces si no se encuentra ningún valor, simplemente imprimiremos un valor en blanco y volveremos a obtener el resultado. Vamos a aplicar algún formato. Puedes aplicar formato de fecha desde casa aquí, pero aquí hay un atajo que normalmente me gusta usar. Me gusta usar Control shift tres para convertir rápidamente los valores en formato de fecha con formato DMM y La idea de usar dos columnas y combinarlas aquí es similar a la idea de columnas derivadas que has visto en algún otro video. Ambos enfoques le brindan una manera poderosa de combinar datos aunque el formato no sea consistente. Espero que esto te haya resultado útil. Te veré de nuevo en otro video. 11. Extracto de datos con miradas de una gran mesa grande: En esta lección, te voy a mostrar cómo extraer algunas columnas y filas de un conjunto de datos grande. Entonces aquí tengo datos de mil empleados, y tenemos varias columnas de información, así que va todo el camino hasta la columna CD. Y todo esto se compone al azar, pero este tipo de conjuntos de datos son bastante comunes. De hecho, tuve que usar una técnica similar en lugar de un cliente varias veces lo largo del año pasado. Entonces, ¿cómo hacemos esto? Permítanme explicar primero lo que está pasando aquí. Puedo seleccionar cualquier número de columnas. Puedo especificarlos aquí, los números de columna que quiero extraer, y luego puedo dar los nombres, y esta parte aquí me mostrará los datos relevantes. Entonces por ejemplo aquí, en vez de dos, quiero la columna número tres, pongo tres ahí, y al instante, voy a conseguir el título del trabajo, y luego el título se imprime aquí. Puedo cambiar estos orden. No tienen que ser así. Entonces, por ejemplo, después de las tres, puedo tener dos, y luego siete, y luego uno, y obtendré los detalles como ahí se muestra. Entonces, ¿cómo hacemos esto? Primero, configura tus datos originales en una tabla. No tiene que estar en una mesa, pero tenerlo en una mesa te hace la vida sencilla. Entonces en nuestro caso, pongo esto en la mesa que se llama staff. Y ahora vamos a la página del extracto. Aquí es donde nos gustaría extraer. Lo mínimo que necesitas son los nombres o el identificador único. Entonces en este caso, tengo mis nombres listados en la columna C. Y entonces también necesito identificar qué columnas quiero extraer en qué orden? Así que imprímalos en la pantalla a lo largo del camino así. Y ahora para la primera fila, usaremos la fórmula de índice para obtener el encabezado de la segunda columna, el encabezado séptima columna, el encabezado de la sexta columna, e imprimirlos ahí. Esta fórmula de índice va como este índice de mis encabezados hash de tabla de pentagrama, Así que esto te dará acceso a toda la información de encabezado en la tabla de pentagrama. Y entonces el encabezado que quiero aquí es el número dos, así que señalaremos eso y cerraremos el corchete. Voy a obtener el encabezado de la segunda columna, que es género, y luego simplemente arrastramos esto hacia los lados para ver los encabezados relevantes para todo Ahora viene el bit donde tenemos que extraer esto. Simplemente borraré esto y escribiremos la fórmula. Entonces queremos hacer una mirada X sobre este valor en la columna mismísimo name. Entonces diremos el nombre del personal. Pero, ¿qué regresamos? Ahora bien, la columna que necesitamos regresar sería dos aquí porque esa es la columna de género, pero sería siete aquí, seis allá, 12 allá. Entonces la columna que queremos es dinámica. Aquí es donde la función index viene muy bien. Diremos índice de la mesa de personal. Queremos todas las filas, así que solo diremos a para indicar que no quiero una fila específica, quiero todas las filas. Pero el número de columna que quiero tiene que ser ese, así que lo señalaremos a D cuatro. Ahora, hay que hacer algunas de estas referencias referencias mixtas. Esto siempre estará en la fila número cuatro. Sólo voy a cambiar esto a D dólar cuatro. De igual manera, esto tiene que estar siempre en la columna C, así que voy a cambiar esta referencia a dólar C siete. Nuevamente, para cambiar estos estilos de referencia, colocas ahí tu cursor y sigues presionando la tecla f four hasta que la metas en el estilo deseado. Una vez hecho esto, cerramos ahí la fórmula del índice y luego cerramos el corchete. Eso es. Obtendremos el género Copia esta fórmula, selecciona toda esta gama, controla, y obtendrás los resultados aquí de una manera agradable y hermosa. Todo esto es dinámico. De hecho, cuando construí algo como esto, uno de mis compañeros en casa de un cliente lo vio, y quedó súper impresionado de que tomara una copia de esto, hiciera una plantilla de hoja de cálculo en blanco para que pudiera usarla para hacer extractos de nómina cada vez que lo necesite Entonces espero que esta técnica te haya resultado útil. Ahora, no tienes que usar X Loup. También puedes usar la hep para hacer esto. No estoy explicando esta parte aquí porque ya hemos cubierto el salto y la búsqueda extensamente, pero siéntete libre de tomarlo como un desafío y escribir la fórmula básica de Vp o hacer referencia al archivo de descarga para aprender a hacerlo. Espero que esta lección le haya resultado útil. Nos vemos de nuevo en la siguiente. Adiós. 12. 8 errores y remedios de búsqueda comunes para ellos: En esta lección, voy a hablar de ocho errores de looker en las correcciones Estos errores son error faltante de valor, valor que realmente no falta error, respuesta incorrecta, error tipográfico, error de datos, error referencia, error de columna, y luego la fórmula no funcionará Vayamos a ver cuáles son estos. El error de valor que falta es, con mucho el error más común que ves cuando estás usando la fórmula de búsqueda. Entonces, por ejemplo, si estoy usando ya sea x lookup o v lookup y trato de buscar algo que no está ahí. Entonces en este caso, estoy buscando a Chandu en mi columna de nombre de personal y trato de obtener el salario de Chandu voy a conseguir el hash A, que es el error común que ves cuando falta el Entonces, ¿cómo solucionar este error? Dentro de xp, puedes usar la opción no encontrada para solucionar esto. Por ejemplo, puedo decir que no se encuentra aquí y eso va a imprimir el mensaje no encontrado. Dentro de Vp, la misma fórmula se convierte en algo así. Entonces buscamos al personal de Chando cinco caídas, y esto será un error, y podemos usar la función if error para evitar esto Cuando la ayuda tiene un error if, eso significa que es un error, entonces no nuestro empleado será el mensaje, e imprimirá ese mensaje. Para el siguiente tipo de error, imagina que tenemos un valor de bonel de ajedrez aquí, y realmente quiero buscar a esta persona Entonces usaremos miramos hacia arriba. Buscaremos el nombre en la mesa del pentagrama, y luego obtienen su género falso. Ahora bien, podíamos ver que claramente el bonel de ajedrez está ahí, pero obtenemos error Esto es algo así como valor, realmente no falta. Esto es porque pensamos que se trata de bonel de ajedrez. Pero cuando vas a editar la celda, notas que en realidad hay un espacio extra al final, lo que está arrojando ese problema. Entonces, ¿cómo arreglar esto? Bueno, la solución número uno es eliminar el espacio, para que podamos ir y eliminar esto y eso solucionará el problema. Pero si no puedes editar esto por cualquier motivo, la otra opción es usar una función como recortar en tus conjuntos de datos de entrada para que puedas eliminar cualquier espacio no deseado y obtener el resultado correcto. Esto funcionará con Luka o x. El tercer tipo de error es la respuesta incorrecta Aquí puedes ver que estoy mirando hacia arriba el bonel de pecho pero estoy consiguiendo su género como femenino Mientras que aquí en la tabla, su género dice masculino. Entonces, ¿qué está pasando? Esto se debe a que cuando usas la fórmula de búsqueda, si olvidaste el último parámetro y lo dejas fuera, Excel lo cambiará por defecto a true, lo que significa que va a buscar una coincidencia aproximada. Esto significa que irá desde arriba. Se asumirá que tu lista está ordenada por orden alfabético, por lo que va a buscar el bonel de ajedrez primera persona es B, la segunda persona es D. Excel asume que debido a que B y luego C ya no está ahí y es D ahora, te voy a dar la respuesta para B, que aquí es femenina. Para solucionar esto, es necesario especificar el último parámetro como false. Otra alternativa es usar x lookup donde ni siquiera tienes que especificar el tipo de modo de búsqueda. Siempre será una búsqueda exacta. Eso también te dará la respuesta correcta. Aquí está nuestro siguiente error de tipo de error. Siempre que cometas un error tipográfico en tu fórmula, es más probable que obtengas el error del nombre hash Entonces, cuando ves el error del nombre hash, significa que has cometido algún tipo de error de mecanografía. Aquí tengo mi fórmula Xp. Todo se ve bien, pero en una inspección más cercana, se puede ver que escribimos mal la búsqueda X como x LUK En el momento en que arregles esto, solucionará ese problema. Otro tipo común de error tipográfico que la gente hace es dar un nombre equivocado en la tabla Ya sea la tabla o la columna en sí, si escribes mal, obtendrás el error de tipo Ahora bien, este tipo de errores tipográficos son fáciles atrapar porque en el momento en que intentes presentarte, Excel dará un mensaje de advertencia diciendo que estás tratando escribir algo que no tiene sentido Ahí es cuando también resaltará que esta parte de la fórmula no tiene sentido. Esto es muy fácil de atrapar. Pero el otro tipo de errores tipográficos aquí, no son fáciles de atrapar y XL lanzará el error de nombre hash El siguiente tipo de error que cometerías es un error de datos. Digamos que tenemos un nombre de empleado como Douty Strutle aquí, y queremos ver su departamento Entonces digo X L esta persona en nombre del personal y luego departamento de personal. Y luego obtenemos el error de valor hash. ¿Qué está pasando aquí? Esto se debe a que cuando observa sus datos, nota que el valor de su departamento es en realidad un valor hash. Esto podría suceder con bastante frecuencia cuando intentas copiar datos de otro sistema u otra hoja de cálculo, y si hubo un error en uno de los cálculos, entonces eso se percolará y Ahora bien, este tipo de error es difícil de atrapar. Si pongo, por ejemplo, departamento de personal y luego si no se encuentra como error de valor. Esto aún no se mostrará. Te dará el valor hash en lugar de ese mensaje de error específico. Esto se debe a que técnicamente, sí encontramos al Doughty Strutle, casualidad de que sus propios datos están dando un error Aquí es donde podrías usar la función de error o quieres inspeccionar la calidad de los datos y corregir cualquier error allí. El tipo de error de texto es un error de referencia. Esto es cuando intentas referirte en tu fórmula a algo que ya no existe. Hagámoslo con una fórmula sencilla aquí. Buscaremos el salario de Doughty Strutle. Nombre del personal y luego sueldo del personal. Aquí sí obtenemos la respuesta. Pero en el momento en que vaya y elimine esta columna, obtendré un error hash aquí porque ya no tengo acceso a esa columna en particular, así que obtendré el error hash rah. Este tipo de error es bastante común, sobre todo si te refieres a cosas en otra hoja de cálculo y cierras ese archivo o cosas así Cuando obtenga el error de envoltura de hash, debe regresar y verificar si sus fórmulas tienen cosas que necesitan para funcionar. Séptimo error es un error de columna, y esto es muy, muy común con fórmulas p. Así que vamos a escribir una mirada aquí arriba. Tenemos a Dotty Strutle y luego vamos a decir arriba Dotty Strutle en la mesa del personal, y luego queremos su columna salarial, que es Y aquí obtenemos el resultado correcto. El salario punteado es de 41.980, y eso es lo que estamos consiguiendo aquí Pero fíjate lo que pasa si inserto una columna en el medio. No tiene que estar justo entre salario, pero puede estar en cualquier parte. Siempre y cuando el salario ya no sea la columna número cinco, ahora es la columna número seis, este valor se convierte en cero porque este arriba no tiene idea de lo que acaba de suceder en la pantalla. Todavía se está refiriendo felizmente a la columna número cinco en lugar de moverla a seis. Entonces, cuando haces este tipo de cosas, esto volverá a ser un colmeror, esto es muy difícil de detectar porque no hay indicación visual de lo que pasó, y puede ser bastante problemático, especialmente cuando estás trabajando en un entorno de equipo donde otras personas están agregando columnas o moviendo cosas Esto es otra vez, otra razón por la que deberías usar las funciones de copa XL. Voy a deshacer los pasos y mostrarte lo que pasa con X Lup X, mi valor nombre del personal, sueldo del personal, y obtendremos la misma respuesta aquí y allá. Pero si inserto una columna, esta se convierte en cero, pero esta aún te da la respuesta correcta porque esto sigue refiriéndose técnicamente a la columna de sueldos del personal, y funcionará mientras nadie vaya y renombró la columna Esta última es la fórmula ni siquiera va a funcionar. Esta es una muy complicada . Voy a escribir la fórmula y después verás que no va a funcionar. Si digo X y luego buscaremos este valor. Nombre del personal, sueldo del personal. Presentador, no pasa nada, la fórmula permanece como es como si fuera un valor de texto Esto se debe a que aquí en esta celda en particular, el formato se ha establecido en formato de texto. Ahora, normalmente, el formato de celda sería general o de número o fecha o moneda Pero esta celda, ya la he puesto a texto. O podrías hacer esto accidentalmente o alguien más podría haberlo hecho con algún otro propósito. Pero cuando tienes una celda como formato de texto, cualquier fórmula que escribas en esa celda no funcionará. Solución fácil, cambia el formato de nuevo a general, y luego edita la celda y presiona enter, y ahora la fórmula funcionará Entonces ahí tienes, ocho errores de búsqueda y correcciones fáciles para ellos. Espero que esta lección en todo este curso te haya resultado muy útil para subir de nivel tu loop y X L up juego. Muchas gracias por mirar, y te volveré a atrapar en otro lugar.