Transcripciones
1. Funciones de búsqueda de entrada: Una de las funciones más utilizadas
en Excel o funciones de búsqueda, lo que llevó a extraer
datos y cotejar datos. Tiene diferentes
variaciones como VLooup, HLooup, x lookup,
una coincidencia de índice. Si quieres ponerte
en marcha rápidamente con estas funciones y además ver casos de uso más avanzados que este entrenamiento es para ti. Hola a todos. Soy parcial. Soy formador y consultor para axial Power BI y Tableau, dirijo mi propia capacitación de
datos de la empresa. Y también soy YouTuber. He construido esta completa capacitación real
en línea para ayudarte a dominar el eje y de la
manera más rápida sin perder tiempos para aprender cosas que
quieres usar en la práctica. Hay entrenamientos por cinco del
campo de entrenamiento real donde
aprenderás sobre todo alrededor las diferentes funciones de búsqueda.
2. VLOOKUP | Lo básico: En el apartado anterior,
hemos hablado de todos
los fundamentos sobre trabajar
con funciones en Excel. Ahora es el momento de empezar a explorar diferentes
tipos de funciones. Una categoría muy importante
son las funciones de búsqueda. Ahora aquí tenemos diferentes
tipos de funciones de búsqueda. Tenemos búsqueda de honorarios,
HLookup, tenemos búsqueda AX, tenemos coincidencia de índice,
y vamos a hablar de todo
eso en esta sección. Entonces comencemos y abramos el cuaderno de trabajo, todo
sobre las búsquedas. Ahora, en primer lugar, ¿
qué funciones busco y por
qué quieres usarlas? No para mostrarte eso. Vamos a ir a
la primera hoja, 01 búsquedas donde tenemos
un conjunto de datos muy pequeño sobre diferentes bebidas
que podemos pedir en un bar. De acuerdo, bueno,
digamos que queremos saber el precio de un té verde de
tamaño mediano. Y quiero hacer esto sin usar ninguna fórmula o función. ¿ Cómo abordarías esto? Pero lo que haría,
iría a su columna de bebidas, bajaría por la lista hasta
encontrar té verde por ahí. Y luego voy a
celdas a la derecha, porque ahí tenemos
los precios medianos. Y así llego
al precio a 45. Ahora porque este es un conjunto de datos
muy pequeño, podrías hacer esto, por supuesto
muy fácilmente de forma manual, pero imagínate que tendría cientos de miles de filas, entonces esto tomaría
bastante tiempo. ¿ Y si quieres saber ahora
el precio de un espresso? Bueno, tendrías que
bajar la lista una y otra vez, buscarla
manualmente, y después decir qué tamaño quieres
y extraer el precio. Ahora, en lugar de
hacerlo manualmente, podemos por supuesto, hacer esto
con la función de búsqueda. Y la
función de búsqueda más común es una VLookup. Comencemos con la función
VLookup. Por aquí. Vamos a
buscar el precio para, digamos que el té verde. Y el tamaño que
vamos a buscar es mediano. Y queremos buscar
el precio, ¿de acuerdo? Ahora, aquí por el precio, vamos a escribir un VLookup. Entonces escribo un signo igual VL y aparece la
función VLookup. Eso busca un valor en la columna
más a la izquierda de una tabla. Y eso es importante,
la columna más a la izquierda de una mesa, ¿de acuerdo? Y luego devuelve un valor en la misma fila de una columna
que especificó. Y por defecto, la tabla debe ordenarse en orden
ascendente. Repasemos esto paso a paso. Aquí. Voy a
seleccionarlo presionando Tab. Por lo que no escribo
la función completa y el soporte abierto,
acabo de presionar hacia arriba. O si lo prefieres, con el mercado también puedes
simplemente dar clic en él. Ahora, tenemos cuatro argumentos
diferentes que el primero
es el valor de búsqueda. Queremos buscar té verde. Ahora bien, podríamos hacer
esto
codificándolo duro así, té verde. Y porque es impuesto,
necesitamos las comillas. O alternativamente, también podemos referirnos a la celda que
contiene el valor de búsqueda, que en nuestro caso
es un té verde 60. De acuerdo, coma para ir
al siguiente argumento. Ahora aquí necesitamos una mesa. Array no necesariamente
tiene que ser una tabla. También puede ser sólo
un conjunto de datos, ¿verdad? Entonces por aquí
tenemos nuestro conjunto de datos. No es una tabla o
no está formateada como tabla. ¿ De acuerdo? Y lo importante es que empecemos con el color Lookup. Entonces no empezamos de nuevo
aquí en la columna
a. vamos a empezar en
la columna B y seleccionamos todo
hasta el final. Ahora, el colon del que
extraemos la información también necesita estar en esta
matriz de tabla dentro del rango. Entonces si solo nos
interesan los precios medios, bueno, entonces no necesariamente necesito seleccionar la última
columna, sin embargo, para ser un poco más
flexible más adelante, también
incluyo la
última columna también. ¿ De acuerdo? Ahora antes de hacer cualquier otra cosa, voy a introducir una coma
para ir al tercer argumento, que es el número de
índice de columna, no el número de índice de columna del que queremos extraer
la información. Entonces eso significa con la información que
tenemos actualmente, Se buscará té verde en la primera
columna de la gama, lo
encuentra por aquí. Y luego hay que decir, ¿
de qué columna
desea extraer la información? Bueno, si queremos saber el precio de un té verde de
tamaño mediano, bueno entonces tenemos que ir
a la columna número uno también. Tres. Empiezas a contar desde la columna más a la izquierda en el
rango que especifiques. Entonces no de la columna a. ahora empiezas a contar
aquí desde la columna B. columna B es una, C es dos, y d, eso es libre. Entonces por eso nos sumergimos en F3, F4. El último argumento que
tenemos que decir, ¿queremos tener
un coincidente exacto o queremos tener
un partido aproximado? Ahora, la mayoría de las veces
probablemente quieras tener una coincidencia exacta. Ahora, volveremos
a esto un poco más tarde para explicar exactamente
cuál es la diferencia. Pero si no estás seguro, vamos por una coincidencia
exacta. De acuerdo. Ahora cerremos los
corchetes, presionemos Enter, y nos da 245, que de hecho es por aquí
el precio de un té verde. Perfecto, De acuerdo, y ahora qué pasa
si nos interesa el precio de un
espresso de tamaño mediano, Bueno, entonces solo tenemos que
cambiar el valor aquí en nombre de la
bebida por espresso. Pulse Intro. Y ahora tenemos 375, que es un espresso de tamaño mediano. ¿ Y si tengo
una e minúscula? ¿ Todavía lo encuentra? Sí, acerías. Entonces no es sensible a mayúsculas y minúsculas. Y qué pasa si no quiero
tomar un espresso de tamaño mediano, pero tal vez solo uno pequeño. Pues pues sólo volvemos
aquí a la fórmula. Y en lugar de un índice
de columna de
tres pies del cual queremos
extraer información, y tendríamos que cambiarlo a E2 porque los precios pequeños. Están en la columna número dos del rango que
especificamos, ¿de acuerdo? Y luego tenemos 315, que efectivamente es el precio
de un pequeño nespresso. Ahora repasemos los pasos una vez más en la siguiente hoja. Entonces vayamos aquí
al ejercicio Z11. Y aquí tenemos un tipo
diferente de
conjunto de datos con información
financiera
para diferentes cuentas. Ahora, quiero extraer información para una cuenta
específica, y esa es la cuenta 6 mil 805. Entonces si hiciéramos
esto de manera manual, tomaríamos el número, pasaríamos a esa columna con
los números de cuenta, y luego
bajarían por la lista hasta
encontrar 6 mil 805, que está por aquí. Y digamos que queremos
saber ese valor por adeudo. Después íbamos
al dios justo al lado y
luego regresaríamos 2132. Ahora, todo eso quiero hacer, tenemos una función VLookup. Así que vamos aquí a D5 y empezar a escribir nuestra
función VLookup de nuevo para seleccionarlo, presione Tab en el valor de búsqueda
está por aquí en D4 coma, luego la matriz de la tabla. Entonces solo la gama. Bueno, esta gama necesita
comenzar con el color Lookup, ok, Ahora, eso
es muy importante y mucha gente
a menudo se equivoca. Entonces no estamos iniciando un
rango aquí y tenemos suficiente. Estamos empezando y variamos con
la columna de número de cuenta. ¿ De acuerdo? Ahora queremos devolver el valor en
la columna de débito. Entonces al menos necesitamos
ir hasta esa columna. No obstante, si queremos un
poco más de flexibilidad para poder devolver también
valores en la columna de crédito, entonces tenemos que incluir también
el juego de golf. ¿ De acuerdo? Entonces con eso seleccionado, presiono coma para ir
al tercer argumento. Ahora, ¿cuál es el número de índice de
columna? Bueno, ese es el colon del que queremos
extraer la información contando de la columna
más izquierda dentro de ese rango
que acabamos de especificar. Por lo que la columna de débito está en
la columna número 12 de ese rango. Entonces lo escribimos también. Entonces. ¿ Queremos una
coincidencia aproximada o una coincidencia exacta? Queríamos tener
una coincidencia exacta. Entonces tecleamos falso. Nunca dejes fuera este
último argumento. Es opcional, pero
si no especifica, no coincide aproximada y podría darte
los resultados equivocados. Entonces cuidado, siempre
escribe falso. Data, nos da 2132. A ver si eso es correcto. Bueno, por aquí tenemos 6,805 y tenemos el monto de
adeudo de 2132. Perfecto, Entonces un VLookup,
está funcionando. Ahora. También es muy
importante que sepas, cuándo usar una v lookup y a veces la gente se equivoca ahí. Entonces tengo otro ejemplo en la siguiente hoja, 01 duplicados. Ahora aquí tenemos un conjunto de datos
diferente, también con diferentes cuentas, diferentes años,
descripción de cuenta y monto. Ahora lo que quiero
hacer es buscar de nuevo el número de cuenta y aquí
la primera columna. Y una vez que encuentre eso, así que aquí está 600857, entonces me gustaría devolver
la cantidad correspondiente, en este caso, 7,730. De acuerdo, ahora, se podría pensar, vale, podemos hacer esto
con un VLookup. Sí, ahora podemos escribirlo
como igual a V lookup. Esa es la función que necesito. Quiero buscar la
información y D4, voy a buscar
por aquí,
empezando por la primera
columna, Control Shift down,
Control shift a la derecha para
seleccionarlos, conjunto de datos Diana. Y antes de subir, entro
una coma para que cuando subo, no cambie el rango
seleccionado. De acuerdo, así que por aquí para
el número de índice de columna, tengo que presionar retroceso. Y luego para el número de índice de
columna, y podemos escribir cuatro
porque queremos
extraer el valor en la
columna número 1234, ¿de acuerdo? Y queremos tener
una coincidencia exacta. Entonces escribe falso. Cierra tus corchetes. Menos de 7,730, exactamente la cantidad que
no esperábamos. No obstante, si
miras más de cerca, verás que
tenemos esa misma cuenta varias veces en este conjunto de datos. Aquí lo tenemos. Y si bajamos un
poco más, ya ven que lo tenemos
por aquí también. Entonces si vuelvo a bajar
un poco más, aquí también lo
tenemos. Ya ven, lo tenemos tres
veces porque tenemos datos
de
información de varios años para las mismas cuentas. Ahora lo que pasa es
que sólo devuelve la primera entrada para ese número de cuenta,
no los demás valores. Y lo que podrías estar
esperando es que obtengas suma de los tres
de estos valores, el valor para 2019202021. No obstante, entonces estamos usando la función equivocada porque ¿cómo podríamos llegar a ese número? Bueno,
ya lo hemos visto en el apartado anterior
porque entonces querrías hacer una suma F. Así que te
gustaría luego
sumar todos estos montos si
el número de cuenta es de 6,857, por lo que no lo harás utilícelo V lookup. Así que en lugar de una función
VLookup, podríamos escribir como suma F son algunas de las funciones Fs que el rango de suma que se
hace la columna cantidad. Y luego tenemos el rango que sería
el número de goma dos puntos, y luego la propia cuadrícula, que sería el número de
cuenta que llenamos un d cuatro. Muy bien. Y ahora me da 18,941, que es la suma de éste, aquel, y de aquel. ¿ De acuerdo? Entonces aquí
no usarías el VLookup porque no solo queremos extraer la información de uno, solo
queremos sumar la cantidad
para una cuenta específica. Entonces VLookup, no usas
cuando quieres sumar, creo que el promedio cuenta en
diferentes valores para ese valor de búsqueda
correspondiente. Aquí, ese
sería el número de chicle. En lugar de eso,
usarías las funciones condicionales como SUMIFS, AVERAGEIFS,
e innumerables. De acuerdo, Así que ahora ya sabes
lo básico de VLookup, pero también está HLookup y eso es lo que vamos a
explorar en la siguiente parte.
3. HLOOKUP | La misma lógica, al revés: Acabamos de ver cómo
usar la función VLookup, que es probablemente la función de
búsqueda más utilizada que está
disponible en Acts. Sin embargo, también hay HLookup, que solo significa búsqueda
horizontal en lugar de v, búsqueda vertical. Entonces vamos a ver cómo funciona. Y para eso, vamos a
la hoja 0 a HLookup. Ahora aquí tenemos un pequeño
conjunto de datos donde queremos buscar los valores
para diferentes métricas, como costo por clic,
número de clickers, número de reservas
para un mes determinado. Ahora el mes
que tenemos en la parte superior, y quiero escribir en cierto mes y
buscarlo en esa primera fila. Y una vez que lo encuentre, quiero bajar unas filas para luego devolver el valor
para esa métrica. Por ejemplo, costo por clic
para octubre. Eso significaría,
miro hacia arriba octubre por aquí en la primera
fila, lo encuentro por allá. Y luego si quiero ir
al costo-por-clic, entonces sólo voy una fila hacia abajo, y ese es entonces
el valor que
devuelvo para octubre,
coste-por-clic. Ahora hagamos esto
entonces con HLookup porque así es exactamente
como funciona esa función. Ahora, pasamos por aquí a la celda E6 y tecleamos signo igual. Y luego aparece la función de búsqueda
H L , H, seleccionada presionando Tab. Y entonces cuál es el
valor de búsqueda mientras que el valor de búsqueda que
tenemos por allá,
octubre, ahora, Goma. ¿ Dónde quiero buscarlo? ¿ En qué rango? ¿ Ahora? Aquí, en lugar de la
primera columna del rango que especificamos, se va a buscar
el valor de búsqueda en la primera fila del
rango que especificamos. De acuerdo, así que si seleccionamos todo
el conjunto de datos aquí, incluyendo los encabezados,
entonces va a buscar octubre en
adultos por aquí. ¿ De acuerdo? Ahora, otra coma. Una vez que encuentre el valor de búsqueda, ¿
a qué fila desea
ir a extraer la información? Ahora, queremos tener
aquí el costo por clic. Entonces contando desde
la primera fila, queremos tener la
información en la fila número dos. Entonces el último argumento también
aquí, siempre llénalo. Queremos tener una coincidencia exacta. Cierra los corchetes, presiona
Enter, y ahí vas. Tenemos $0.48. ¿ Y si también queremos obtener el número de
ingresos de clickers de las cuentas? Bueno, nos gustaría simplemente tomar esa fórmula y arrastrarla
hacia abajo a las otras dos, y nos da un error. Ahora bien, ¿por qué hay un error? Echemos un vistazo más de cerca. Voy a pasar por aquí a siete y luego dar clic en
la barra de fórmulas AC, ese rango que especificamos. Bueno, también se ha desplazado hacia abajo, y el valor de búsqueda ya
no es octubre, sino el sonido debajo de él. Entonces, si solo queremos simplemente arrastrar una copia o una fórmula
a una celda diferente, entonces tenemos
que asegurarnos de que arreglamos nuestras referencias. Ahora, ¿cómo hacer eso? Volvamos a la
fórmula original donde empezaste. Ahora quiero arreglar por lo menos
el número de fila aquí para e4, porque cuando lo arrastre hacia abajo, no
quiero que esta
referencia a octubre vaya
a la siguiente celda debajo de ella. Vale, entonces entonces el rango que especificamos aquí para
el segundo argumento, bueno, eso no debería moverlo hacia arriba. Entonces voy a usar F4 para candado de tu d 11
y para bloquear B6. Y luego el tercer argumento, mientras todavía queremos
tener aquí, coste-por-click. Entonces no voy a
cambiar eso y presente. Ahora podemos arrastrarlo hacia abajo. Ya veo, bueno tenemos el
mismo valor en todas partes. Sin embargo, para el
número de clics, podemos volver atrás y cambiar los dos a tres para obtener la
siguiente fila en el conjunto de datos. Y aquí por los
ingresos de los clickers, que es el último, vamos a la fila seis. Entonces tengo que cambiar
los dos en un seis. De acuerdo, entonces nuestra
búsqueda de edad está funcionando, sin embargo, vamos a practicarlo de
nuevo en diferentes conjuntos de datos. Entonces vamos aquí a 0 para
ejercitar a donde
tenemos ese mismo conjunto de datos que
teníamos al principio para
la función VLookup. Pero ahora vamos a
hacer un HLookup aquí. Ahora, digamos que la
bebida por la que queremos extraer el precio va
a ser de nuevo, el té verde. Y entonces el tamaño que
teníamos antes era mediano. Y queremos
extraer el precio. Entonces, ¿cómo podríamos hacer
eso ahora con HLookup? ¿ Y en qué se diferencia de ese VLookup que teníamos antes? Ahora, voy a escribir
un H lo busca. Lo seleccionaré presionando Tab. Entonces el
valor de búsqueda que tenemos. Bueno, aquí vamos a mirar
hacia arriba horizontalmente. Entonces ahora no estamos
mirando la bebida. No, en cambio vamos
a buscar el tamaño. Por lo que el énfasis está ahora en el lado derecho no está
tanto en la bebida. Entonces ser 16 es el valor de búsqueda. Ahora, si quieres,
puedes arreglarlo. No es que lo vayamos a
copiar en otro lugar, pero presionemos F4 para arreglar y referenciar a Goma que al rango, el rango donde
queremos buscarlo. Bueno, tiene que empezar con la primera fila porque esa va a ser
la fila de búsqueda. De acuerdo, Así que aquí, si incluyes o no la
columna de bebidas en realidad no importa. Las columnas que
realmente necesitas son estas aquí y luego
el número de índice de fila. Ahora bien, ¿dónde está la bebida té
verde, en qué papel? Mientras que el té verde está por aquí. Entonces esa es la fila número 11. Voy a escribir 11. Entonces, ¿queremos
tener una coincidencia exacta? Sí, lo hacemos. Entonces buceando falso para el último argumento
y luego Enter 245, que efectivamente es el precio correcto. Pero se ve por allá,
y si queremos encontrar no el de tamaño mediano sino
el precio de los sitios grandes. Bueno, entonces sólo
voy aquí a tamaño y cambio medio, mediano a grande. Y ahora tenemos 265. ¿ Y si queremos tener el precio de
diferentes bebidas, por
ejemplo, brebaje de oro? Bueno, nada cambia cuando
cambié el
cuello de la bebida porque aquí no tenemos referencia a la celda donde tecleamos
el nombre de la bebida. Así que si quieres buscar el precio de diferentes bebidas, tendría que volver
a la fórmula y cambiar el 11 aquí a, bueno, digamos que quiero
buscar brebaje frío, que es una fila número nueve, entonces tengo
que cambiar eso a un nueve. Entonces tenemos 325. Bonito que correcto. No brebaje frío está por
aquí, es de gran tamaño. Por lo que efectivamente es correcto. Déjame deshacerme de este
color y ya está. Nuestra búsqueda de edad funciona. Ahora se ve la diferencia
con el VLookup de antes donde hicimos una búsqueda vertical en
la columna de bebidas. Ahora con el HLookup, estamos buscando el tamaño
horizontalmente en la fila de encabezado. Entonces una vez que encontramos el tamaño, pasamos a cierta fila. Entonces la fila está un
poco más arreglada aquí. Por lo que ahora hemos visto y practicamos un poco con la
búsqueda de edad VLookup. También hay actos lookup, que les voy a
mostrar en la siguiente parte.
4. XLOOKUP | Versión nueva y mejorada: La más nueva de todas las funciones de
búsqueda es x lookup. Y en realidad, si no
eres x buscar, realmente no
necesitas
saber VLookup, HLookup. Entonces vamos a verlo en
acción. No por esto. Vamos a volver
a la primera hoja del cuaderno 01 look-ups. Ahora, por aquí
estábamos buscando el precio del espresso de
tamaño mediano. De acuerdo, ahora voy a borrar
lo que alguna vez escribiste aquí en C6 y ahora escribir una búsqueda x. Ahora veamos cómo
es diferente. Voy a escribir
un signo igual. Ahora escribimos en Excel, no una función de búsqueda x absorber. Aquí tenemos la descripción de x lookup y es casi lo mismo. No obstante, hay una
sutil diferencia. Busca un rango o
una matriz para una coincidencia y devuelve el
elemento correspondiente a partir de una segunda furia. Entonces aquí tenemos que
configurar dos rangos. Una rabia en la que
vas a hacer la búsqueda, otro rango del
que
quieres extraer la información. De acuerdo, ahora vamos a
seleccionarlo y ver cómo
funciona en acción. Valor de búsqueda, que
OVN A16 coma, luego la matriz de búsqueda. Bueno ese va a
ser el rango de búsqueda, por lo que la columna de bebidas, y
realmente no importa si se incluye la fila de encabezado o no. Gama. Y la gran
diferencia es que no incluimos las columnas de
precios. Y antes sí incluimos
viene de donde queremos
extraer la información. Sin embargo, con la búsqueda de hacha, solo se especifica el
rango de extractos en el tercer argumento. Entonces si queremos tener el precio de un Espresso de
tamaño mediano, entonces vamos a
seleccionar la columna D allí. Y el número de
celdas que tenemos en ese segundo rango
necesita corresponder
al número de celdas
que seleccionamos allí para el primer rango, la rama de búsqueda. ¿De acuerdo? Ahora estos tres argumentos los
necesitas especificar como mínimo. Y luego hay un montón
de argumentos opcionales, que echaremos un
vistazo en un segundo, ¿de acuerdo? Ahora, por defecto siempre
hace una coincidencia exacta. No hace falta que
te molestes con eso. Por aquí, veo que
tenemos un precio de 375, que efectivamente es el precio
de un espresso de tamaño mediano. Pero ahora extraemos
información usando un HLOOKUP. Ahora vamos a verificar
si funciona. Cambiémonos
aquí a Londres. Y ya ves que tenemos aquí para 25. Y qué pasa si
lo escribo
minúsculas, minúsculas i, entonces todavía
funciona. Entonces también aquí,
no es sensible a mayúsculas y minúsculas. De acuerdo, así que repasemos
estos pasos una
vez más para 01 ejercicio. Aquí quisimos
extraer el monto en la columna de débito para un
determinado número de cuenta. Esta vez lo vamos a
escribir usando un hacha mirar hacia arriba. ¿ Cuál es el valor que
queremos buscar? Ese es el número de cuenta
y d para la matriz de búsqueda. Entonces el rango de búsqueda, que va a ser
el número de cuenta. Ahora, antes no
incluíamos eso como pitch. Si lo deseas, puedes
incluir eso también. Entonces una coma, ¿cuál
es la matriz de retorno? Bueno, queremos devolver
el monto adeudo, por lo que ese es el rango
justo al lado. Y porque incluí
añadido antes, tengo que incluirlo por aquí. Ahora hecho, podemos cerrar los
corchetes, presionar Enter. Veo 2132, que efectivamente es por aquí el
monto correspondiente a las cuentas 6 mil 805. Ahora uno de los grandes beneficios de la búsqueda de Actos es que
para mucha gente, se siente un
poco más intuitivo. Y también puedes hacer
una búsqueda horizontal. Por lo que reemplaza tanto a VLookup
como a HLookup. Entonces si vamos aquí a 0 a h lookup y nos deshacemos de todas estas fórmulas que ahí
escribimos antes. Y ahora podemos usar
una búsqueda X en su lugar. Ahora, voy a ir
aquí a Sx es igual a sine x alpha x lookup,
no al valor de búsqueda. Eso es octubre, el mes aquí. Entonces, ¿dónde
queremos buscarlo? Ahora vamos a
tener una h horizontal Ahora esa horizontal
oscila por aquí. Entonces todos los meses, Gama, ¿qué devuelve
área que para la primera métrica,
costo por clic, es
decir, bueno, fila número
dos, fila por aquí. Y seleccionamos tantas celdas como tengamos para el
primer rango. Y después podemos simplemente presionar Enter o cerrar los
corchetes y presionar Enter. Y ahí tenemos los $0.48, que es el valor para octubre
cada cambio el mes, digamos de octubre a julio. Entonces ya ves tenemos 60 cuartos. Y también con la búsqueda de hacha, si queremos copiar nuestra
fórmula a la derecha o hacia abajo, necesitamos arreglar nuestras referencias. Entonces el valor de búsqueda
para esa referencia, bueno, necesitamos
signos de dólar, ¿no? Así que al menos el
signo de dólar delante
del número de fila para
la matriz de búsqueda, también
estamos necesita ser arreglado. Así que voy a poner signos de dólar aquí y aquí para
la matriz de retorno. Bueno, podríamos
arreglarlo y no pudimos. Si no lo arreglamos,
entonces se va a bajar. Lo que significa que toma la siguiente fila, que es el número de clickers, que está bien para
la segunda métrica. Y si la siguiente
sería número de reservas, bueno entonces no
arreglaría la referencia a
la matriz de retorno. No obstante, queremos tener aquí, los ingresos de Click out. Eso significa que si lo
arrastrara hasta aquí, ves tendríamos número de reservas en lugar de
ingresos de clickers. Entonces necesitaría volver atrás y hacer un ajuste y decir aquí, no
quiero tener la fila 14, Pero no me gustaría
tener la fila 1616 por ahí. Y ahora tenemos 2829, que de hecho es el valor
para los ingresos de julio de Click. De acuerdo, ahora tal vez te
estés preguntando, ¿por qué nos enseñaste VLookup y HLookup si hay acts lookup, que básicamente es
una mejor versión y reemplaza a ambos. Y estoy de acuerdo con ustedes. No obstante, hay
mucha gente que no sabe x buscar
y trabajar con VLookup. Y es por eso que todavía necesitas
conocer VLookup también. Sin embargo, si necesita extraer la información
del conjunto de datos, solo use x lookup. Es mucho más flexible
y sustituye a la auditoría.
5. Coincidir datos con funciones de búsqueda: Mira las funciones no sólo
se utilizan para extraer información
del conjunto de datos, también
se utilizan
a menudo para hacer coincidir dos o más conjuntos de datos juntos. Para que tengas una
tabla grande con todos los datos, que es algo que necesitas para las tablas dinámicas normales si
quieres usar los campos
de diferentes datasets. Ahora, veamos cómo
funciona eso en la práctica. Ahora para eso, tengo
una hoja aparte. Entonces tenemos por aquí 03 datos coincidentes y aquí
tenemos dos conjuntos de datos. El principal, estás del
lado izquierdo, donde tenemos información sobre los ingresos
para diferentes modelos. Y tenemos un segundo
conjunto de datos que contiene
información descriptiva sobre estos hoteles. Entonces aquí tenemos la identificación del hotel Aquí tenemos la identificación del hotel
y los nombres de los hoteles. De acuerdo. Lo que me gustaría
hacer es que me gustaría
buscar una identificación de hotel en
ese segundo conjunto de datos. Una vez que lo encuentre, extraiga la información justo
al lado para que tengamos el nombre del hotel en el primer conjunto de datos
que está a la izquierda. De acuerdo, Ahora, ¿cómo funcionaría eso? Bueno, nos vendría bien el VLookup. Siguiente Luca, primero intentemos. Por lo que voy a escribir de nuevo
un signo igual para VLook. Cuál es el valor de búsqueda
o no me gusta D justo al
lado, G5, Gama. No ¿Dónde está mi rango? ¿ Aquí mismo? Entonces voy a seleccionar toda
la gama. Entonces otra coma, una vez
que encuentre el id del hotel, así que el valor de búsqueda, luego quiero ir a esa segunda columna en el rango que se especifica, número dos. Y luego el último argumento, quiero tener una coincidencia exacta. Entonces escribe falso. Ahora cerremos los corchetes, presionemos Enter y veamos si
funciona para el primero. No obstante, en cuanto
copiamos la fórmula hacia abajo, entonces ves en algún
momento obtenemos flechas. Y eso es porque, bueno, el rango que
especificamos aquí para un segundo argumento
también se mueven hacia abajo. Entonces si queremos que coincidan los datos, tenemos que ir un paso atrás y tenemos que arreglar la referencia al rango de búsqueda y al rango para
el que extraemos información porque
ese no debe moverse. Entonces aquí necesitamos al
menos un signo de dólar
frente a los cinco, frente a los 40. De acuerdo. Entonces podemos presionar Enter. De acuerdo. ¿Por qué no necesitamos un letrero de
dólar frente al G5? Porque esa referencia
necesita moverse hacia abajo. De acuerdo, entonces ahí no usamos signos de
dólar, vale, ahora, podemos simplemente arrastrar este hacia abajo o simplemente haces doble clic en la esquina inferior derecha y lo copia hacia abajo para todo
el conjunto de datos. Perfecto. Y ya ves ahora sí funciona. Por ejemplo aquí
para el hotel ID seven, eso es Ramadán limitado. Por lo que esta es la forma en que se pueden
administrar los datos usando VLook. Ahora por supuesto, esto también
habría funcionado con una búsqueda de hacha. De acuerdo, así que aquí déjame
borrar lo que
acabamos y luego volver de
nuevo a H5, ¿no? X L para x lookup. Queremos mirar hacia arriba por aquí, el hotel id ¿verdad? Siguiente diapositiva, luego
la matriz de búsqueda. Ahora bien, ¿dónde
queremos buscarlo? En qué rango
queremos buscarlo, o aquí en el segundo
conjunto de datos, primera columna coma. Entonces no arreglamos a partir de qué objetivos
queremos devolver valores. Esa es la segunda columna
justo al lado. Porque vamos a
copiar esta fórmula hacia abajo. Vamos a arreglar estos rangos. Así que el segundo argumento, la matriz de búsqueda,
tenemos que arreglar. Entonces utilizo la tecla F4
para arreglar ese rango. Y lo mismo para la matriz de
retorno, bien, por lo que se utiliza permitirse el lujo de arreglar
esas referencias, bien, que cerrar los corchetes, pulse Enter, y luego copiarlo
hacia abajo para toda la columna. Y ahí vas.
Tenemosexactamente lo mismo que
lo que teníamos antes
con el VLook, pero ahora solo levanta actos lookup. Aquí tenemos dos variaciones. Ahora lo que hacemos todo
esto un poco más fácil es si
usaríamos tablas. Ahora voy a ir de
nuevo un paso atrás. Y voy a
crear una tabla a partir
del primer conjunto de datos
de la izquierda ahora donde tenemos básicamente todos los datos
de ingresos. Por lo que voy a
seleccionar el conjunto de datos, ir a Insertar y elegir tabla. La tabla tiene
encabezados. Haga clic en Ok. Ahora podemos cambiar el nombre
aquí en el diseño de la mesa. diseño de la tabla sólo
se muestra cuando se tiene una
celda en el diseño de la tabla seleccionada
hasta el lado izquierdo. Y podemos llamar a
éste ingresos. De acuerdo, así que aquí tenemos
todos los datos de ingresos. Después vamos a ese
segundo conjunto de datos, creamos una tabla a partir de él. Por lo que insertar tabla,
los datos tienen encabezados. Entonces vamos a escribir de nuevo
que los últimos actos de búsqueda. Así que ahora podemos ver
es igual a entonces x alfa x búsqueda
seleccionada pulsando Tab valor de búsqueda está
justo al lado de él. Ahora ya ves porque
estamos usando una mesa, tenemos que agregar signo hotel id solo significa tomar el
id del hotel de la misma fila. Ahora la matriz de búsqueda, ahora queremos
buscarla aquí en la columna ID del hotel
del segundo conjunto de datos. Ahora ya ves tenemos info del hotel, ID del
hotel, vale, entonces info del hotel
es el nombre de la mesa. ID del hotel es el
nombre de la columna. A continuación, la matriz de retorno. Bueno, podemos o bien
seleccionar eso todo. No organizamos la columna de nombre del
hotel ni. Si lo prefieres, también puedes
simplemente escribir el nombre de la mesa, corchete abierto. Entonces vemos todo
dentro de la mesa, todos los campos que queremos tener como no
vamos en el nombre del hotel. Y luego podemos cerrar los
corchetes y luego pilas
cerradas mirar
hacia arriba y ver que se llena automáticamente y ver que se llena automáticamentepara toda la columna. Y esto lo hace
especialmente fácil si tus conjuntos de datos están en hojas
diferentes. Ahora, echemos un vistazo a
otro ejemplo. Ahora para este ejercicio de práctica, tenemos tres hojas,
tenemos financieros. Estamos aquí con datos financieros. Por lo que costo de ingresos para
diferentes países, diferentes departamentos,
diferentes fechas. Entonces tenemos
información geográfica en la siguiente hoja, y tenemos información
sobre diferentes departamentos. ¿ De acuerdo? Ahora ven que podemos
igualar los datos, por ejemplo, para los departamentos
bajo el peso de la llave, porque aquí tenemos
la clave del perdón. Y si nos fijamos en el conjunto de datos
financieros, también
tenemos la
clave de partición y por lo tanto la geografía. Ya has visto que tenemos por aquí Clave
del país, hoja de geografía. También contamos con Country key. Por lo que podemos usar esos
identificadores únicos para que coincidan con los datos. ¿ De acuerdo? Ahora bien, ¿cómo podemos hacer eso? Bueno, para que sea un
poco más fácil para nosotros, aquí
vamos a trabajar
con mesas. Entonces voy a ir a finanzas y luego seleccionar una celda
dentro de un conjunto de datos. Y podemos hacer Control T o insertar y luego dar clic
en la tabla tiene encabezados. De acuerdo,
démosle un buen nombre. Llamémoslo a éste financiero. Entonces vamos a la siguiente, geografía, hacemos lo mismo. Así que selecciona una celda
en tu conjunto de datos, Controla D, presiona Enter, y luego cámbiale el nombre. Y a éste lo podemos
llamar Geografía. Entonces el último, departamentos.
Vamosallá también. Controla T y
llámenlo este departamentos. De acuerdo, Entonces ahora que
tenemos nuestras tablas, podemos igualar los datos. Por ejemplo, digamos que desde la tabla de geografía
queremos tener el nombre completo del país en lugar de tener aquí el
país, conseguir eso. ¿ Cómo podemos hacer eso? Bueno, con nuestras funciones de búsqueda. Entonces déjame hacer esta columna
un poco más ancha. Y aquí vamos a
tener el nombre de su país. Ahora vamos a sumergirnos en
nuestra función de búsqueda. Para este ejemplo, voy
a ir por una taza extra. Ahora bien, ¿cuál es el valor de búsqueda? Lo que vamos a
buscar la clave del país. Por lo tanto, llave de pórtico
significa que toma la
tecla de contador de la misma fila. Después añadimos una coma. Ahora, ¿cuál va a
ser la matriz de búsqueda? Ahora aquí, quiero
buscarlo en la tabla de geografía. Ahora podríamos ir allí
y simplemente haciendo clic en la geografía naranja y luego seleccionando sobre control A2 desplazar hacia abajo para seleccionar toda
la columna. Después coma, vuelva
a las finanzas. Podríamos hacerlo así. No obstante, si estamos en la hoja, financieras son yo lo
haría es yo
sólo mecanografiaría el
nombre de esa tabla, geografía, corchete abierto. Entonces vemos todo
dentro de esa mesa. Nos gustaría tener
la tecla de control, seleccionarla, y luego cerrar el
corchete. Ahora, la matriz
de retorno, ¿de qué columna
queremos devolver el valor? Nuevamente, geografía,
corchete abierto. Y queremos tener
el nombre del país o el país Dan
corchete cerrar de nuevo. Y entonces podemos cerrar. En realidad mira hacia arriba, presiona Enter. Y como hay una mesa, automáticamente
la llena hacia abajo. Es perfecto. Y eso es todo. Tenemos el nombre del país. ¿ Y si también quisiéramos
tener la región? Bueno, entonces sólo podemos agregar otra y decir que
esta es la columna de región. Ahora sólo podemos apoderarnos de
ti esa primera celda, arrastrarla a la
derecha y veamos qué pasa ahora me
da un error. ¿ Por qué? Porque se necesita llave de
departamento ahora, la siguiente y tenemos que
cambiar eso de nuevo a contra-clave. Entonces va a ser
complicado y luego lo
va a buscar
en la columna país y tabla de geografía. Ahora, no queremos
tener un galón contador, pero la columna
clave del país, lo siento, cambió de país a país
k Y
queremos tener la información
de la lectura obtenida. Eso es correcto. Entonces eso es que Santa y el automáticamente se llena y
tenemos las regiones correspondientes. Perfecto. Y si necesitamos información
de la carga no es, entonces podemos hacer exactamente lo mismo. Entonces voy a tener aquí,
digamos el nombre del departamento. Entonces podemos escribir nuestra búsqueda x. ¿ Qué queremos mirar hacia arriba? ¿ Cuál es el departamento donde
queremos buscarlo? Lo vamos a buscar en los departamentos estables,
corchete abierto. Y luego se ve todo
dentro de esa mesa. Ahora queremos igualarlo sobre
la base de la ganga K, Muy bien, corchete cerrar
y luego regresan array. Entonces nuevamente nos referimos al corchete estéril
Barton abierto y queremos tener
el valor del nombre del departamento. Entonces las gangas eres tú, ¿de acuerdo? Después corchete cuadrado, cerrar, cerrar los corchetes para
x Luca, presentador. Y aquí tenemos todos los nombres de los departamentos
telefónicos. Entonces ya ves, trabajar con
Damon's hace que esto sea mucho más fácil para que
no tengas que saltar
de un lado a otro entre
las diferentes hojas. Por lo que ahora ya has
visto cómo podemos usar las diferentes funciones de búsqueda no solo para extraer información, sino también para hacer coincidir datos de diferentes tablas
o diferentes conjuntos de datos. No obstante, de lo que aún no
hablamos es ¿por qué necesitamos realmente una coincidencia exacta y qué es una coincidencia
aproximada? Porque creo que
podrías sorprenderte.
6. Aproximación frente al partido exacto: Una coincidencia exacta frente a
una coincidencia aproximada. ¿ Qué
significa exactamente y por qué
elegimos hasta ahora solo coincidencia exacta? Ahora lo que mucha gente
piensa que hace es que toma el valor de
búsqueda y lo busca en el rango de búsqueda y busca una coincidencia
aproximada. No obstante, esto
no es exactamente cierto. Ahora aquí tengo un par
de ejemplos en la hoja 0 para aproximar que
como primer ejemplo, vamos a buscar cierta cantidad en la columna de
cantidad de aquí. Y una vez
que lo encontremos, vemos, ok, cuál es el porcentaje de
descuento correspondiente, ok, entonces los
pedidos más acostumbrados, el high-end, el
porcentaje de descuento, bien, ahora digamos que la
cantidad es una mil. Ahora entonces podemos escribir
V lookup un logo X. Ahora aquí, voy
a usar por ahora, primero VLook, V lookup
valor de búsqueda está aquí en D4 coma. Entonces lo vamos a
buscar por aquí. Así que voy a seleccionar todo
el conjunto de datos, no la primera columna
y columna de búsqueda, y luego otra coma. Ahora, una vez que
encontremos la cantidad, queremos ir al
valor justo al lado, que es la columna dos. Y queremos tener
una coincidencia exacta. Por lo tanto falso. Cerrar el corchete por
debajo del seis por ciento, lo cual es cierto, que es el
porcentaje de descuento correspondiente para esa cantidad. Pero imagínense que
no tendríamos miles, pero tal vez 750, no quiero 750 no está dentro
de esa primera columna, por lo que no lo encuentra. No obstante, seguirías obteniendo un descuento si
pides 750 productos. Ahora, ¿cuánto descuento? Bueno, probablemente el cuatro por
ciento que tienes aquí, justo al lado del 500
porque estás en el corchete de 502 mil. Y ahí es donde entra en juego
aproximado. Ahora si volvemos a una fórmula
y cambiamos false a true, ahora vamos a hacer
un partido aproximado. ver, ahora devuelve ese 4%
por aquí, justo al lado de 500. Ahora, ¿por qué devuelve
el cuatro por ciento? Porque cuando un partido
aproximado
lo hace, toma el salmón y un 50, si no lo encuentra, va al valor más grande. Y el NIF, el
valor de búsqueda, que es 500, y luego va a la segunda columna y
extrae la información. Ahora lo que también es
realmente importante para que esto funcione es que los valores que tenemos en esa columna de búsqueda estén
ordenados en orden ascendente. Si no, entonces podría
devolverte el resultado equivocado. Y es por eso que
también puede ser tan peligroso no especificar que quiere tener una
coincidencia exacta porque entonces por defecto un VLook siempre
hace una coincidencia aproximada. Mi acaba de devolverte
un resultado aleatorio, así que nunca lo dejes fuera. ¿ De acuerdo? Pero ahora
echemos un vistazo primero a algunos otros ejemplos donde
podemos usar este partido aproximado. Ahora si vas un
poco más abajo, tenemos por aquí un segundo conjunto de datos con
diferentes tramos fiscales. Ahora aquí me gustaría
bucear en ingresos. Entonces digamos que tenemos un
ingreso de 50 mil, entonces ¿cuál es la tasa impositiva
correspondiente? Ahora, para esto, podemos usar de nuevo un
valor de búsqueda VLook por aquí. Y D 13 coma el rango
donde queremos buscarlo. Bueno, queremos verlo en la primera columna de
esta gama de aquí. Y luego
extraemos la información
del tercer objetivo. ¿ De acuerdo? Ahora empecemos de nuevo con coincidencia
exacta. Cierre los corchetes. Ahora no devuelve
nada porque 50 mil no está en
esa primera columna. No obstante, sí sabemos que
estamos en el corchete nueve mil
setecientos cuarenta
y cuatro y cincuenta y siete mil
novecientos dieciocho. Por lo que una tasa impositiva debería estar en
algún lugar del
rango del 40 al 42%. Ahora bien, ¿cómo podemos asegurarnos de que devolvamos la tasa impositiva
correspondiente? Bueno, aquí es de nuevo donde tenemos
partido aproximado para ello. Entonces volvemos y
cambiamos de falso a verdadero. Ahora tenemos ese
valor, 14 a 42. ¿ De acuerdo? Ahora bien, ¿y si tenemos
cincuenta y siete mil
novecientos setenta? Nada cambia. No salta a la siguiente, pesar de que 57,918 está
cerca y luego 9,744. Ahora bien, ¿eso es bueno? Sí,
Es bueno porque bueno, seguimos en el corchete. Nueve mil
setecientos cuarenta cuatro cincuenta y siete mil
novecientos ochenta. Ahora permite ejemplo donde este partido aproximado podría
ser muy útil es,
bueno, de nuevo, datos coincidentes. Por ejemplo, si tienes ciertas cuentas
aquí que quieres emparejar con una categoría. Ahora, digamos que
para cierta categoría, siempre
has arreglado de
diferentes números de cuenta. Entonces por ejemplo, para un d
es de 0 a mil, de personas mil a 2009 para Oficio dos mil,
tres mil. De acuerdo. Entonces podemos hacer coincidir
los datos usando un VLookup. Ahora, el valor de búsqueda está por aquí. Entonces donde queremos
buscarlo en la primera columna
de esta gama aquí. Entonces queremos tener que
como número de índice de columna. Y entonces vamos a tener
ahora un partido aproximado. Ahora vuelve de ello. ¿ Por qué devuelve oficina? Porque 2499 no está aquí
en la primera columna. No obstante, va al valor
más grande debajo de ella, que es de 2 mil. Después pasa a la segunda columna donde tenemos oficina
como categoría, ese es el valor
que devuelve. Vale, ¿y ahora esto es
posible con HLookup? Funciona exactamente de la misma manera. ¿ Qué pasa con la búsqueda AKS? Bueno, claro, también
es posible. Ahora, echemos un vistazo cómo funcionaría
esto con los hechos. Mira eso. Voy a volver
al primer ejemplo. Y voy a escribir un valor de búsqueda
x lookup. ¿ Son los 750 los que tenemos ahí? Lo vamos a buscar aquí arriba, la columna de cantidad coma. Y luego queremos devolver
el valor a fin de año, la tasa de descuento, ¿de acuerdo? Cierra los corchetes,
presiona Enter y
verás que devuelve un error. ¿De acuerdo? Ahora, ¿por qué? Porque por defecto y excelente Dios
hace una coincidencia exacta. Ahora bien, si esto
hubiera sido un VLook, búsqueda en
V por defecto hace
una coincidencia aproximada. No obstante, pensaste que
la mayoría de las veces la gente
quiere tener una coincidencia exacta. Por lo tanto, la coincidencia exacta es ahora el valor predeterminado
para la búsqueda de AKS. Y si quieres hacer
un partido aproximado, entonces tenemos otro
argumento aquí. Si no se encuentra,
modo de coincidencia y búsqueda. Ahora, f nada encontró que
pudiera ser perjudicial porque
podría una
manera elegante devolver mensaje. Entonces yo, su valor no encontrado. ¿ De acuerdo? Entonces ya ves, en lugar de un error, me sale ahora el impuesto que se especifica en la
fórmula, ¿de acuerdo? Y luego hay otro
argumento. Después de eso. Podría especificar coma, coma para ir a la siguiente. Y aquí podemos decir, vale, ¿qué tipo de
partido aproximado queremos? Podemos decir
coincidencia exacta, eso es 0. Podemos exacta coincidencia o
siguiente elemento más pequeño, coincidencia
exacta, o siguiente elemento más grande o coincidencia de caracteres comodín. Ahora, por aquí, los que nos
interesan son estos dos. Queremos tener aquí
una coincidencia exacta o siguiente artículo pequeño queremos ir
al que está debajo de él. Y así el valor más grande debajo del valor de búsqueda
básicamente, ok, ahora seleccionado presionando Tab, y luego podemos
cerrar los corchetes, presionar Enter, y
devuelve el 4%. Ahora a veces podría
haber un caso de uso también de uno, que luego devolvería el seis por ciento que está justo por
encima del valor de búsqueda. Entonces se iría a los miles y
luego a la derecha. De acuerdo, Así que
aquí tienes un
poco más de flexibilidad en comparación con el VLook. Voy a regresar
por aquí el 4%. Ahora, ¿cómo funcionaría eso
para los demás? Exactamente de la misma manera. Ahora que sabes qué coincidencia
aproximada hace, probablemente
estés un poco decepcionado porque
a menudo quieres
buscar el valor de búsqueda
en un rango y encontrar el valor que
casi coincide . Y eso no es lo que hace un partido
aproximado. En lugar de eso,
podemos resolver ese problema con caracteres comodín
y hacer un contiene mucho. Ahora, esa va a
ser la siguiente parte.
7. Tarjetas comodines para más flexibilidad: Acabamos de ver
cómo funciona la
coincidencia aproximada y cuando
quieres usarla, sin embargo a menudo también solo quieres tomar el valor de búsqueda,
búscalo dentro de la columna de búsqueda y f
Hay casi lo mismo ahí, luego coincide con los datos que no
se quiere un partido
aproximado hace. Entonces, ¿cómo resolverlo? Bueno, con carácter comodín, eso es exactamente lo que
vamos a mirar ahora mismo. Ahora, sígueme a la
hoja 05 contiene. Ahora, aquí tenemos un conjunto de datos muy
pequeño y lo que
queremos hacer es tomar
el nombre de la empresa, buscarlo aquí en
el colon de la empresa. Y luego una vez que encontremos el valor, entonces extraemos el monto
en la columna de débito. ¿ De acuerdo? Ahora ya ves
que Wayne no está aquí exactamente en
el colon de la empresa. Sin embargo, tenemos
Wayne Enterprises, Inc., que probablemente
debería ser igualado. De acuerdo, así que vamos a ver
cómo podemos hacer eso con nuestras funciones de búsqueda
ahora, también aquí, podemos hacerlo, pero si
miras hacia arriba o una copa real, Primero
hagámoslo
con un VLook. Voy a ir aquí a D6, ingresa un signo igual V lookup. Queremos buscar el valor
en los cinco cuando tengamos a Wayne. Lo vamos a buscar a
lo largo de años, empezando por la columna D. Y luego queremos ir por lo menos hasta
la columna de débito coma. Entonces una vez que encontremos a Wayne
Enterprises ahí, que queremos extraer
información de la columna dos. Y por ahora queremos
tener una coincidencia exacta. Cierra tus corchetes y
no encuentra a Wayne, y por lo tanto devuelve
un error como se esperaba. Ahora mucha gente piensa, sabes qué,
voy a arreglar esto. Quiero tener un partido
aproximado. Entonces cambia esto a verdadero. Y sí me da un valor. Pero esta es la parte peligrosa porque te da
el valor equivocado, el valor para, bueno, Stark y esto no es para
no para el que
probablemente estabas pensando donde estaría
el partido,
EmpresasWayne. Debido a que convierte tu valor, podrías pensar que todo está
bien. Continúas. Ahora, sólo porque aquí el
conjunto de datos es muy pequeño, es fácil detectar el error. Sin embargo, si su
conjunto de datos es muy grande, entonces podría no
ser tan obvio. De acuerdo, ahora, ¿cómo podemos arreglar eso? Bueno, volvamos
a nuestra fórmula. Voy a deshacer, rellenar color, volver
a la fórmula. Y aquí vamos
a deshacernos de los cinco. Y por ahora, simplemente sumérgete en peso entre
comillas porque está gravado. Y aquí no queremos
tener un partido aproximado, pero queremos tener
un cotejo exacto. Ahora por el momento todavía
devuelve un error. No obstante, si ahora
vuelves atrás y escuchas que tocaste un
signo de asterisco justo después. Bueno, ese es un carácter
comodín, lo que significa que cualquier cosa
puede seguir después. De acuerdo, entonces si presiono Enter, ya ven ahora obtenemos 63, que efectivamente es el valor
que esperábamos. ¿ De acuerdo? ¿ Y si también hay
algo en frente de camino? Por ejemplo, aquí
tenemos un número y luego ya no funciona. Pero entonces también podemos colocar un signo de asterisco
justo delante de él. Y eso significa que ahora estamos
viendo el valor que simplemente contiene lo que esté
delante o después de
que no importa. Ahora probemos esto también
para diferentes empresas. Digamos Holly, De acuerdo, ahora aquí mi fórmula no se actualiza porque un Wayne
codificado. Entonces ahora voy
a hardcode totalmente dentro de la fórmula
y funciona. Entonces tenemos el seminal 50. Pero a veces
necesitas un poco más, tiene que ser un poco más exacto. De acuerdo, entonces ahora
sólo decimos que en
algún momento debería contener, claro, podemos o bien colocarlo en
ambos lados o simplemente uno de los lados o
aún no son alternativos. También podemos decir que
estamos mirando temprano y luego cinco
personajes, ¿de acuerdo? Y ese es el segundo carácter
comodín,
el signo de interrogación. De acuerdo, Entonces, ¿cuál es el signo de interrogación? Ahora estamos diciendo que necesitamos encontrar
a Holly y bueno, cinco personajes siguientes y espacios también son un personaje. Entonces si sé de un año que debería tener cinco
caracteres siguiendo sólo, entonces cuestionar cinco signos de
interrogación. De acuerdo, veo que funciona. No obstante, si tendríamos
un carácter extra, entonces devolvemos un error. Entonces este, el signo de interrogación es un
poco más restrictivo. Ahora, por el momento, estamos
codificando duro el valor de búsqueda, que por supuesto no es tan bueno. Entonces, ¿cómo podemos hacer
esto para que
no codificemos duro el valor
sino que solo nos referimos a D5? Bueno, aquí volvemos. Y luego en lugar de valor de
codificación dura, vamos a
referirnos solo ahora, cómo obtenemos los
caracteres comodín mientras
queremos tener un
signo de asterisco antes y después de él. Entonces vas justo antes de
la marca D5 Dacia, comilla
astérix ahora necesita estar entre
comillas, lo contrario,
cosas reales que vas a hacer. Multiplicación de pozos.
Deacuerdo, y ahora queremos combinar texto, básicamente un carácter comodín. pilas hacen el valor que está
dentro de los cinco, que podemos hacer con él
y signo signo signo sinaí
antes y después de
la referencia de celda. Y después de ello,
queremos tener otro carácter comodín,
comillas. Y eso es todo. Presiona Enter, ya ves, vuelve a funcionar y
se encuentra santo ahí. ¿ De acuerdo? Y si lo cambiamos ahora a cuando lo veas devuelve el 63. Perfecto. Entonces ahora no estamos
codificando duro el valor de búsqueda, sino solo haciendo referencia a una celda que contiene
el aspecto de eso. Nuevamente, haces esto
también con la búsqueda de AKS. Por supuesto otra vez. Ahora volvamos y cambiemos
el VLookup a una X look up. Ahora, no voy a escribir
Donnelly desde cero. Yo sólo me voy a quedar con
esa primera parte. Y así el valor de búsqueda,
eso no va a cambiar, no la
matriz de búsqueda está por aquí. Y entonces no lo hacemos
array está por allá. Voy a cerrar mis
corchetes, presiona Enter. Bueno, ¿por qué funcionó
con el VLookup? Otra búsqueda de hechos. Bueno, porque la búsqueda x, específicamente
tienes que decir que estás usando caracteres
comodín, lo cual puedes hacer haciendo uso
de los argumentos opcionales. Entonces coma, coma. Entonces aquí en segundos argumentos
opcionales, entonces podemos decir que estamos usando caracteres
comodín para
el presentador del partido. Ya veo ahora funciona de nuevo.
8. ÍNDICE MATCH | Cómo funciona: Ahora en este punto
has visto todas
las cosas más importantes que necesitas saber
para extraer información de un
conjunto de datos o dos
conjuntos de datos coincidentes juntos usando
diferentes funciones de búsqueda. Sin embargo, hay
situaciones en las que necesita aún
más flexibilidad, más flexibilidad que lo que las diferentes
funciones de búsqueda son para usted. Y ahí es donde el index
match puede ayudarte. Ahora, básicamente es
la combinación de dos funciones, index y match. Y si pones las dos juntas, entonces tienes aún
más posibilidades. Ahora, primero hagamos una
búsqueda normal usando la coincidencia de índice. Ahora para eso,
sígueme 206 y próximo partido. Ahora aquí tenemos
ese mismo
conjunto de datos que lo hemos visto antes con los diferentes precios para diferentes bebidas y tamaños. ¿ De acuerdo? Ahora, digamos que volvemos a
mirar hacia arriba, creando t. Entonces para la talla mediana,
¿cuál es el precio? Ahora, aquí vamos a usar
ahora index y match, porque estas son dos funciones. Hagámoslos paso a paso. Entonces en lugar de
hacerlo todo en una celda, lo
voy a
dividir en dos celdas. Ahora vamos primero por el
índice y después por el partido, y después juntamos
los dos. Entonces aquí tenemos índice, ahí, tenemos partido aquí. Déjame crear algunos marcadores de posición. Permítanme hacer esta
columna un poco más ancha y comenzar con
la función de índice. Aquí, solo sumérgete en índice. Ahora la función de índice
simplemente le da el valor que está en la
intersección de columna y fila. Así que primero empieza con la gama. Entonces aquí tenemos un conjunto de datos
y esta vez incluye la dirección que es importante para más adelante porque
hay que ser consistente. Entonces incluyo eso es, y me gustaría tener
el valor para su propia fila, bien por aquí para el
té verde, esa es la fila 11. Entonces digamos que queremos
tener de tamaño mediano. Entonces esa es la columna número
1234 en ese rango, y luego nos da 245 por aquí. Ahora por supuesto, no
queremos
buscarlo manualmente y averiguar cuáles son los números de
fila y columna. Ahí es exactamente donde entra en
juego la función de coincidencia. Ahora con la función match, podemos buscar,
buscar valor y color, igual que las funciones de búsqueda que
hemos visto antes. Por lo que la función
de partido entonces le dirá cuál es la posesión relativa por lo que el papel en el que esa
mirada del IRS. De acuerdo. Entonces voy a usar una función de match
para el segundo. Por lo tanto, la coincidencia de tiempo
seleccionada presionando el valor de búsqueda
Tab
va a ser té verde. Y luego vamos a buscarlo. Vamos. Vamos a
buscarlo por aquí en la columna B. Y ya ves incluyo
eso, Eso es importante,
eso es consistente con
la función de índice y también incluyó
la sumadora, vale, así que solo sé consistente, ya sea incluidos en la
función de índice e incluir en la función de coincidencia o excluidos
en la función de coincidencia o excluidos en la función de
índice, ¿de acuerdo? Entonces queremos tener
una coincidencia exacta, así que 0. Y entonces podemos cerrar
la función de partido. Y ya ves que me da
la fila en la que
rasga verde y ese valor que
luego podemos usar dentro de
la función de índice. Entonces, en lugar de codificación dura, el no amoroso podría usar el resultado de la
función match de esta manera. De acuerdo. Por lo que acabo de referirme a la celda que contiene el
resultado de la función de partido. No hay curso extra que me
da entonces a 45, el valor correcto, sin embargo
necesitábamos células. Entonces, ¿puedes juntarlo todo? Sí. De acuerdo, así que si
combinamos los dos, entonces podemos simplemente agarrar por aquí la función match sin el signo igual para que el
control C copie. Y luego vuelves
a la función de índice. Entonces por aquí, en lugar de referirme a la celda que contiene la
función de partido a los 17, voy a sacar eso. Y luego para el número de
fila basado en la función de coincidencia,
presione Entrar. Y ahí vas. Tenemos 245. De acuerdo, esa función de partido de aquí. Ya no
necesitamos. Eso se puede borrar. Entonces aquí, esta combinación de
coincidencia de índice hace lo mismo que un
VLookup no son como una búsqueda de hacha. De acuerdo, ahora,
tal vez te estés preguntando, cuál es exactamente el
beneficio de hacer así parece
mucho más complejo. Y eso es cierto. Si puedes hacer lo mismo
con BUSCARV y BUSCARH, solo tienes que ir por el
VLookup actúa localmente. No obstante, te daré uno o dos ejemplos donde índice coincide con
el único camino a seguir. Ahora veamos
un primer ejemplo donde la coincidencia de índice puede hacer
algo que VLookup. Ahora, para eso, sólo voy a reajustar un poco esto
aquí. Ahora digamos que no nos
interesa el precio, pero sí nos interesa
devolver categoría D,
la categoría de producto en el 31 conseguido
que se ve por allá. Ahora bien, si hiciéramos
esto con VLookup, podríamos decir, si buscas, valor de
búsqueda, esa es
la bebida. Y luego
lo miramos por aquí. Y queremos extraer
lo que queremos
extraer la categoría que
está fuera del rango. Entonces ahí es donde está el problema. Entonces si me sumerjo aquí, ahora, ¿cuál es el índice de columna,
menos uno o menos? No se puede salir
del rango. Entonces por aquí puedo terminarlo, pero verán
que devuelve un error. No puede salir
de la matriz. Así que podrías pensar, está bien, pero entonces sólo podemos
ajustar por aquí el rango para que empiece por
aquí al principio. No obstante, entonces se
va a buscar té
verde en la primera
columna no lo encontrará, y además entonces
obtendrá un error. De acuerdo, entonces esto no lo está resolviendo. Entonces lo que mucha
gente a veces hace es tomar esa columna de categoría de
producto y luego simplemente arrastrarla aquí
al lado derecho que
puedas devolver
el valor a la derecha. No obstante, esto es un poco chapucero. Ahora bien, ¿cómo se puede resolver esto? Bueno, antes que nada, debes saber que
podrías haberlo resuelto
también dentro de la búsqueda de AKS. Para que podamos hacer un excelente trabajo. Busca té verde,
búscalo por aquí y columna de
bebidas y luego devuelve el valor aquí a
la izquierda de la misma. Pulse Intro. Veo que está en categoría D. De acuerdo, entonces esta es probablemente
la forma más fácil de resolverlo, pero ahora quiero
hacerlo con un partido de índice antes de que vamos a
mirar un ejemplo más complejo
con match de índice. De acuerdo, Entonces, ¿cómo funcionaría eso? Voy a empezar
con el signo igual. Ahora aquí podemos
usar de nuevo en siguiente. Ahora, ¿dónde está mi rango? Rangos por aquí. Entonces todo el conjunto de datos, incluidos los datos, está bien.
Despuésel número de fila. Para el número de fila, puedo usar la función de coincidencia. valor de búsqueda de coincidencia está por aquí. La bebida. ¿ Dónde queremos
buscarlo en columna B, incluyendo los encabezados. Entonces queremos tener
una coincidencia exacta a 0. Cierre la función de coincidencia.
Porlo que la función de coincidencia va a averiguar
el número de fila coma. Después de que los consiguieron hechos. Queremos extraer
la información. Bueno, por aquí podemos extraer la información de la
primera columna del rango. Entonces uno, cierra los
corchetes, presiona Enter. Veo también obras. Así que probablemente ahora piensas, sí, pero la búsqueda real
seguía siendo más fácil. Estoy de acuerdo. No obstante, en los
siguientes dos ejemplos, vamos a
ver dos casos. Sólo podemos resolverlo
con index match. Entonces echemos un vistazo.
9. Búsqueda de filas y columnas: Simplemente agregas una
primera introducción al índice y a
la función match y cómo puedes
usarlos para conseguir que básicamente haga
lo mismo que un logotipo real. Entonces, ¿cuál es el punto? Y esta parte,
vamos a echar un vistazo a cómo se puede hacer un valor de búsqueda doble
en una columna y una fila. Ahora vayamos
a la hoja 07, búsqueda de
fila y columna. Ahora aquí quiero
buscar una cuenta en esa primera columna
con los números de cuenta. Y me gustaría
buscar un
año determinado porque
aquí tenemos valores para el año 20202122, y ese es el valor
que quiero devolver. Entonces, en este caso, están aquí 6 mil 805. Quiero devolver
el valor de 2020, que está ahí, que entonces
debería ser 562. Importante es que tanto
el número
de cuenta como el año deben
ser flexibles. Entonces quiero que el usuario final ponga un valor aquí y aquí para luego obtener el valor por
aquí sin
tener que jugar con
la fórmula misma. Entonces, ¿cómo podemos hacer eso? Bueno, este caso sólo se puede
resolver con la coincidencia de índices. Ahora, al igual que antes, podemos dividirlo en
múltiples celdas. Así que vamos a tener una función de índice y
vamos a tener una función de coincidencia. Pero ahora por la fila
y la dorada, bien, así que vamos a
tener que igualar funciones. Y por aquí podemos empezar
con la función de índice. Y podemos decir gay, el conjunto de datos con el que estamos
trabajando está por aquí. Y aquí o bien incluir
o excluir los encabezados, pero sean consistentes. ¿De acuerdo? Entonces voy a
incluir a los demás. Entonces. Apenas por ahora, sólo
voy a decir, Ok, número de
cuenta 6,805
está en la fila número cuatro y luego
la columna número 1234. De acuerdo, Así que para, para algunos valores
hardcoding, y eso me da que el 562. Pero por supuesto esto
tiene que ser dinámico. Por lo que hay que averiguar en qué fila 6,805 sus calendarios. Podemos usar la función de coincidencia. Voy a teclear en partido. Ahora, ¿cuál es mi
valor de búsqueda? El 6,805. ¿ Dónde queremos buscarlo? Y luego muy primera columna, e incluimos los
encabezados o los datos de expertos, solo sé consistente y
ves que me da un cuatro. Entonces lo mismo
para un jardín. Entonces por aquí podemos usar
otra función de coincidencia. El valor de búsqueda es el año que tenemos justo debajo
del número de cuenta. ¿ Y dónde
queremos buscarlo? Queremos
buscarlo en la fila. De acuerdo, ahora, aquí es importante que empieces por el
principio, ¿no? Porque aquí, en nuestro
caso necesita devolver 1234 columna para el
tipo de partido tiene que ser exacto. Entonces 0 Entra, veo
que nos da un cuatro. Entonces ahora tenemos los
tres componentes y sólo hay que
ponerlos juntos. Tenemos por aquí
la función de índice. Y para el número
de fila es que lo codifica duro, podemos referirnos a la
celda que contiene el resultado del
partido para la fila. Y luego para la columna, tenemos por aquí una referencia
entonces al partido por Dios. ¿ De acuerdo? Sigue siendo el mismo. No obstante, necesitamos resultados. Queremos tenerlo todo
en una fórmula. Entonces en lugar de solo
referirme a esa celda, voy a copiar esa función de
coincidencia Control C. Volver a la función de índice. Y luego aquí vamos a este segundo argumento
para el número de fila D9. Voy a borrar basado
N aquí, el primer partido. Todavía funciona. Entonces ves que
uno que no necesitamos más de lo que hacemos lo
mismo para el colon. Así que copia sobre la
función match por aquí, dos en ese índice. Entonces están entrando en acerías. Entonces ahora esa segunda
función de partido tampoco necesitamos. Y lo que nos queda
con esta fórmula. Bueno, esa es nuestra fórmula de
doble búsqueda. Entonces voy a copiar ese. Y luego básicamente
aquí para D6, Ese es el resultado final. De acuerdo, Ahora ves bastantes funciones y podría ser un poco
abrumador al principio. No obstante, solo asegúrate dividirlo
y en algún momento, una vez que lo hayas hecho en
ellos, varias veces, podrás hacerlo de una sola vez. Ahora lo que también podría
hacerlo un poco más fácil es dividirlo
en múltiples filas. Entonces, si hacemos la barra de fórmulas un poco más grande que aquí, podemos usar Alt Enter para colocar la función de índice
en la siguiente línea. Lo mismo que
haces entonces por todos
los diferentes argumentos para
que no pierdas el descuido. ¿ Qué está haciendo, qué y dónde están los corchetes de apertura y cierre
para qué función? Entonces aquí, la parte media, los argumentos medios
para la función de índice. Probablemente terminaría
y un poco para que se vuelva un
poco más legible. No me gustó. Pulse Intro, verá espacios y colocar una nueva línea no afecta
la funcionalidad. Y lo que también es agradable cuando combinas esto con los drop-downs. Entonces si hago la barra de
fórmula y vivo en Lazada y voy aquí
al número de cuenta. Entonces podemos hacer uso
de la validación de datos. Si acudes a Data. Y luego aquí, validación de datos, entonces se puede decir lo que se debe permitir que el usuario
ponga en esa celda. Ahora, sólo queremos
tener los valores que
están en la lista. Entonces para la fuente, aquí tenemos valores únicos y
la columna de número de cuenta. Pulse Intro, haga clic en. De acuerdo. Ahora tenemos un desplegable para que el usuario pueda elegir
qué cuenta, para qué cuenta
desea extraer la información. Por ejemplo, 6,022. Desde hace años, podemos hacer lo mismo. Seleccione la validación de datos de
datos ESL. Y también aquí
queremos tener una lista con los valores que están por aquí en el
encabezado de fila. Haga clic en Ok. Ahora tenemos dos desplegables y puede cambiar a
lo largo de un año, el año, así como sus tripas. Entonces si yo elegí, elige por aquí, 6,818, que es por aquí. Año 21, que está por allá, tenemos 8 mil 038, lo cual es correcto. Perfecto. Por lo que en próximo partido está funcionando. Ahora estamos haciendo
una búsqueda doble, que no se puede hacer con una
búsqueda VLook x o un trabajo.
10. Búsqueda de varias columnas: Veamos otro ejemplo
donde necesitas Index Match. No puedo usar una u
otra función de búsqueda. Y es que si quieres
hacer una búsqueda de múltiples dos puntos, ahora sígueme a la hoja número ocho, búsqueda de múltiples columnas. Y aquí en este conjunto de datos, tengo por encima
el valor de búsqueda. Por lo que ven tenemos
el año, el mes, la identificación del granero, y
la productividad. Y básicamente no hay
un valor único en el conjunto de datos solo cuando combino
múltiples columnas. Por lo que las columnas para el
año, mes, negociador, y ID de producto sólo cuando los
combino en él hecho, obtenemos un valor único. ¿ De acuerdo? Ahora primero escribamos
una fórmula y luego pasemos por ella paso a paso. Porque creo que es
un poco más fácil cuando ves el resultado final. De acuerdo, ahora vamos a
buscar el costo por clic
para el producto uno, ID de
socio, un mes,
enero año 2010. De acuerdo, entonces, ¿cómo hacer eso? Bueno, el valor que
estoy esperando aún está en $0.72 en esa primera fila. De acuerdo, ahora, vamos
a darle una oportunidad a esto. Vayamos a los nueve años. Y aquí podemos escribir función de
índice. Ahora. Aquí, en primer lugar, ¿dónde está la tabla de que quiero extraer
la información después? Bueno, tenemos nuestro
conjunto de datos por aquí. Y también aquí. Sólo sé consistente si
ejecutas a los demás. Selecciona todo el puente, ¿de acuerdo? Ahora, ¿cuál es mi número de fila
en lugar de mi referencia a H7? Vamos a poner el
número de columna que se pone en luego seis. Y entonces podemos cerrar
la función de índice. Presentador $0.72. De acuerdo, fácil. Pero ahora ¿qué sigue? Ahora tenemos que
averiguar en qué fila, la combinación de estos cuatro valores de
búsqueda, S, ¿verdad? Pero luego de una manera dinámica, en lugar de codificar eso también. Entonces para eso podemos
usar una función de coincidencia. Aquí es donde se vuelve un
poco más complejo porque ahora el valor de búsqueda
consiste en ese valor de
allá y ese valor de allá, y el valor de
allá y allá. De acuerdo, entonces
vamos a combinar el impuesto que hay en
estas cuatro celdas. Muy bien, coma, luego
la matriz de búsqueda, queremos buscarla. Bien combinando, bien de
nuevo para el colapso. Y aquí, porque esencia, incluimos las flechas por ahora. Entonces hago clic aquí en D
o Control Shift hacia abajo, y luego el signo de ampersand
para que podamos combinarlo. Tenemos la siguiente columna
justo al lado, ok, que es el mes. Y luego recombine ese con el ID de socio por aquí, control shift down,
no un signo de ampersand. Y luego la última columna que
tenemos por aquí, Vamos a la parte superior Control
Shift Down para tomar esa. ¿ De acuerdo? Entonces esa es la matriz de búsqueda, la combinación de
los valores que están dentro de estas
cuatro columnas, ¿de acuerdo? Entonces el tipo de coincidencia, lo que queremos tener
una coincidencia exacta 0, entonces podemos cerrar
esa función de coincidencia. Bueno, ya veo, tengo,
se equivocan. Puedo deshacerme de eso
y presionar Enter. Ya ves que tenemos $0.72. ¿ Y si tenemos producto? Vamos a sumergirnos aquí. Productos, ¿ven $0.64? De acuerdo, entonces está funcionando, pero ¿por qué está funcionando? Ahora, repasemos los
pasos paso a paso. Así que vayamos aquí al
lado derecho de nuestro conjunto de datos. Ahora, ¿cómo empezamos? Empezamos diciendo, vale, no tenemos
un valor único, sino solo cuando combinamos
el aire con el mes, con el regateo
con el producto. De acuerdo, entonces combinamos estas cuatro encuestas lo que nos
da un valor. Entonces, ¿dónde
queríamos buscarlo? Bueno, no hay una columna
donde encontremos eso. Sin embargo, podemos combinar los valores que están en la columna del
año por aquí. Entonces el mes que el
socio, que los productos. Los combinamos y
luego lo arrastramos hacia abajo para todo el conjunto de datos. Una vez que haya hecho eso, entonces sí tenemos
un valor único, que luego podemos encontrar dónde
está utilizando una función de coincidencia. Entonces coincide, este es el valor, el valor de búsqueda,
¿dónde quieres que coincida? Pero en esa columna
que contiene todos estos valores combinados
de estas columnas. ¿ De acuerdo? Entonces también aquí queremos
tener una coincidencia exacta 0. Y eso nos da una herramienta, y eso sí es correcto. Tenemos por aquí producto para seleccionar que si este
va a ser producto uno, entonces volvería
por allá, el uno. De acuerdo, así es como podemos
hacer búsquedas de múltiples columnas. No obstante, ahora solo
tenemos el número de fila. Necesitamos tener
el valor real, los $0.72 por ahí. Saber cómo obtener
el valor real. Bueno, tenemos que hacer un índice, vale, entonces podemos decir índice. Ahora estamos, tenemos
ese viejo conjunto de datos. Entonces nos encargamos aquí de todo
ese conjunto de datos. Entonces tenemos el número de fila. Bueno, el número de fila que
acabamos de averiguar aquí. Y la columna de que queremos extraer
la información, costo por clic es un sexo tranquilo, y luego cerrar el corchete
que nos da los 72 conjuntos. Así es como
funciona paso a paso. De acuerdo, así que volviendo a nuestra fórmula donde
tenemos todo el asunto, déjame hacer esto un
poco más grande y luego ponerlo de nuevo en líneas separadas para que sea un poco más legible. Entonces decimos índice, no la parte de rango,
esa es la parte fácil. Ahora, ¿de qué papel? Ahí es donde se pone complicado, mucho más un poco más difícil. Y por aquí, es decir, la última parte es sólo la columna de la que extraemos
la información. De acuerdo, Así que aquí la
parte media es la parte complicada. Así que combinamos las columnas de recombinar del
valor de búsqueda que usamos para una
búsqueda por aquí. Y queremos tener una coincidencia
exacta, y ya está. De acuerdo, entonces este
fue otro ejemplo de cómo puedes usar
index match para hacer algo que no puedes hacer con vLookUps son x look-ups. Ahora claro que es muy
específico, sin embargo, podría ser muy
útil recordar esto una vez que te encuentres
con una situación similar. Entonces eso es todo. Ahora deberías poder abordar cualquier situación para la que
necesites hacer una búsqueda.
Bas Dohmen, Founder + YouTuber
