La mayoría de las veces, encontrar una celda en una hoja de cálculo de Excel es bastante fácil; si no puede escanear las filas y las columnas, puede usar CTRL + F para buscarlo. Pero si estás trabajando con una hoja de cálculo muy grande Cómo dividir una gran hoja de cálculo CSV Excel en archivos separados Cómo dividir una gran hoja de cálculo CSV en Excel en archivos separados Una de las deficiencias de Microsoft Excel es el tamaño limitado de una hoja de cálculo. Si necesita hacer que su archivo de Excel sea más pequeño o dividir un archivo CSV grande, ¡siga leyendo! Lea más, puede ahorrar mucho tiempo utilizar una de estas cuatro funciones de búsqueda. No importa el tamaño de su documento de Microsoft Excel, serán mucho más eficientes.
La función VLOOKUP
Esta función le permite especificar una columna y un valor, y devolverá un valor de la fila correspondiente de una columna diferente (si eso no tiene sentido, se aclarará en un momento). Dos ejemplos donde puede hacer esto son buscar el apellido de un empleado por su número de empleado o encontrar un número de teléfono especificando un apellido. Aquí está la sintaxis de la función:
= VLOOKUP ([lookup_value], [table_array], [col_index_num], [range_lookup])
El [lookup_value] es la información que ya tiene; por ejemplo, si necesita saber en qué estado se encuentra una ciudad, sería el nombre de la ciudad. [table_array] le permite especificar las celdas en las que la función buscará los valores de búsqueda y retorno. Al seleccionar su rango, asegúrese de que la primera columna incluida en su matriz sea la que incluya su valor de búsqueda. Esto es crucial. [col_index_num] es el número de la columna que contiene el valor de retorno.
[range_lookup] es un argumento opcional, y toma 1 o 0. Si ingresa 1 u omite este argumento, la función busca el valor que ingresó o el siguiente número más bajo. De modo que en la imagen siguiente, una VLOOKUP que busque un puntaje SAT de 652 devolverá 646, ya que es el número más cercano en la lista que es menos de 652, y [range_lookup] está predeterminado en 1.
Echemos un vistazo a cómo podemos usar esto. Como tengo en mis artículos sobre operadores booleanos en Microsoft Excel Tutorial de Mini Excel: Usar lógica booleana para procesar datos complejos Tutorial de Mini Excel: Usar lógica booleana para procesar datos complejos Operadores lógicos SI, NO, Y y O, pueden ayudarlo a obtener Novato de Excel para usuarios avanzados. Explicamos los conceptos básicos de cada función y demostramos cómo puede usarlos para obtener los máximos resultados. Leer más y conteo avanzado y agregar Tutorial de Mini Excel: Usar conteo avanzado y agregar funciones en Excel Tutorial de Mini Excel: Usar conteo avanzado y agregar funciones en Excel Contar y agregar fórmulas puede parecer mundano en comparación con fórmulas de Excel más avanzadas. Pero pueden ayudarlo a ahorrar mucho tiempo cuando necesite recopilar información sobre los datos en su hoja de cálculo. Leer más, utilizaré una hoja de cálculo que generé con generatedata.com. Contiene números de identificación, nombre y apellidos, ciudad, estado y puntajes de SAT. Digamos que quiero encontrar el puntaje SAT de una persona con el apellido "Winters". VLOOKUP lo hace fácil. Aquí está la fórmula que usaríamos:
= VLOOKUP ("Inviernos", C2: F101, 4, 0) Debido a que los puntajes del SAT son la cuarta columna de la última columna, he usado 4 para el argumento del índice de la columna. Tenga en cuenta que cuando busca texto, establecer [range_lookup] en 0 es una buena idea; sin eso, puedes obtener malos resultados. Esto es lo que Microsoft Excel nos ofrece:
Devolvió 651, el puntaje SAT perteneciente al estudiante llamado Kennedy Winters, que se encuentra en la fila 92 (se muestra en el recuadro anterior). ¡Hubiera tomado más tiempo desplazarse buscando el nombre que escribir rápidamente la sintaxis!
VLOOKUP también puede ser bastante útil si está usando Microsoft Excel para hacer sus impuestos ¿Cómo hacer sus impuestos? 5 fórmulas de Excel que debes saber para hacer tus impuestos 5 Fórmulas de Excel que debes conocer Pasan dos días antes de que vencen los impuestos y no quieres pagar otra tarifa de presentación tardía. Este es el momento de aprovechar el poder de Excel para poner todo en orden. Lee mas .
Notas sobre VLOOKUP
Algunas cosas son buenas para recordar cuando estás usando VLOOKUP. Primero, como mencioné anteriormente, asegúrese de que la primera columna en su rango sea la que incluya su valor de búsqueda. Si no está en la primera columna, la función arrojará resultados incorrectos. Si sus columnas están bien organizadas, esto no debería ser un problema.
Lo segundo a tener en cuenta es que VLOOKUP solo devolverá un valor. Si hubiéramos usado "Georgia" como valor de búsqueda, habría devuelto el puntaje del primer estudiante de Georgia, y no se da ninguna indicación de que haya, de hecho, dos estudiantes de Georgia.
La función HLOOKUP
Donde VLOOKUP encuentra los valores correspondientes en otra columna, HLOOKUP encuentra los valores correspondientes en una fila diferente. Como normalmente es más fácil escanear los encabezados de las columnas hasta que encuentre el correcto y usar un filtro para encontrar lo que está buscando, HLOOKUP se utiliza mejor cuando tiene hojas de cálculo realmente grandes o si está trabajando con valores organizados por tiempo. . Aquí está la sintaxis:
= HLOOKUP ([lookup_value], [table_array], [row_index_num], [range_lookup])
[lookup_value], de nuevo, es el valor que conoce y para el que desea encontrar un valor correspondiente. [table_array] es las celdas en las que desea buscar. [row_index_num] especifica la fila de donde vendrá el valor de retorno. Y [range_lookup] es el mismo que el anterior; déjelo en blanco para obtener el valor más cercano cuando sea posible, o ingrese 0 para buscar coincidencias exactas.
Para este ejemplo, he creado una nueva hoja de cálculo con generatedata.com. Éste contiene una fila para cada estado, junto con un puntaje SAT (diremos que es el puntaje promedio del estado) en los años 2000-2014. Usaremos HLOOKUP para encontrar el puntaje promedio en Minnesota en 2013. Así es como lo haremos:
= HLOOKUP (2013, A1: P51, 24)
(Tenga en cuenta que 2013 no está entre comillas porque es un número, y no una cadena, también, el 24 proviene de Minnesota en la fila 24). Como puede ver en la imagen a continuación, se devuelve el puntaje:
Minnesotans promedió un puntaje de 1014 en 2013.
Notas sobre HLOOKUP
Al igual que con VLOOKUP, el valor de búsqueda debe estar en la primera fila de su matriz de tablas; esto rara vez es un problema con HLOOKUP, ya que generalmente usará un título de columna para un valor de búsqueda. HLOOKUP también solo devuelve un valor único.
Las funciones INDEX y MATCH
INDEX y MATCH son dos funciones diferentes, pero cuando se usan juntas pueden hacer que la búsqueda de una gran hoja de cálculo sea mucho más rápida. Ambas funciones tienen inconvenientes, pero al combinarlas desarrollaremos las fortalezas de ambos. Primero, sin embargo, la sintaxis:
= INDICE ([matriz], [número de fila], [número_columna]) = PARTIDO ([valor_buscado], [conjunto_buscado], [tipo_cuerpo])
En INDEX, [matriz] es la matriz en la que buscará. [row_number] y [column_number] se pueden usar para restringir su búsqueda; Echaremos un vistazo a eso en un momento.
El [lookup_value] de MATCH es un término de búsqueda que puede ser una cadena o un número; [lookup_array] es la matriz en la que Microsoft Excel buscará el término de búsqueda. [match_type] es un argumento opcional que puede ser 1, 0 o -1; 1 devolverá el valor más grande que sea menor o igual que su término de búsqueda; 0 solo devolverá su término exacto; y -1 devolverá el valor más pequeño que sea mayor o igual que su término de búsqueda.
Puede que no esté claro cómo vamos a usar estas dos funciones juntas, así que lo expondré aquí. MATCH toma un término de búsqueda y devuelve una referencia de celda. En la imagen a continuación, puede ver que en una búsqueda del valor 646 en la columna F, MATCH devuelve 4.
ÍNDICE, por otro lado, hace lo contrario: toma una referencia de celda y devuelve el valor en ella. Puede ver aquí que, cuando se le indica que devuelva la sexta celda de la columna Ciudad, INDEX devuelve "Anchorage", el valor de la fila 6.
Lo que vamos a hacer es combinar los dos para que MATCH devuelva una referencia de celda e INDEX use esa referencia para buscar el valor en una celda. Digamos que recuerda que había un estudiante cuyo apellido era Waters, y quiere ver cuál era el puntaje de este estudiante. Aquí está la fórmula que usaremos:
= ÍNDICE (F: F, MATCH ("Aguas", C: C, 0)) Notarás que el tipo de concordancia está configurado a 0 aquí; cuando estás buscando una cuerda, eso es lo que querrás usar. Esto es lo que obtenemos cuando ejecutamos esa función:
Como puede ver en el recuadro, Owen Waters obtuvo 1720, el número que aparece cuando ejecutamos la función. Esto puede no parecer tan útil cuando solo puede mirar algunas columnas, pero imagine cuánto tiempo ahorraría si tuviera que hacerlo 50 veces en una gran hoja de cálculo de base de datos Excel vs. Acceso: ¿puede una hoja de cálculo reemplazar una base de datos? Excel vs. Acceso: ¿puede una hoja de cálculo reemplazar una base de datos? ¿Qué herramienta deberías usar para administrar los datos? Access y Excel incluyen filtrado de datos, intercalación y consultas. Le mostraremos cuál es el más adecuado para sus necesidades. ¡Leer más que contenía varios cientos de columnas!
Deje que la búsqueda comience
Microsoft Excel tiene muchas funciones extremadamente potentes 3 Fórmulas locas de Excel que hacen cosas increíbles 3 Fórmulas locas de Excel que hacen cosas increíbles El poder de Microsoft Excel radica en sus fórmulas. Déjame mostrarte las maravillas que puedes hacer con las fórmulas y el formato condicional en tres ejemplos útiles. Lea más, y los cuatro enumerados arriba simplemente rascan la superficie. Sin embargo, incluso con esta visión general rápida, usted debería poder ahorrar mucho tiempo cuando trabaje con hojas de cálculo grandes. Si desea ver cuánto más puede hacer con INDEX, consulte "The Imposing Index" en Microsoft Excel Hero.
¿Cómo ejecutas búsquedas en grandes hojas de cálculo? ¿Cómo ha encontrado que estas funciones de búsqueda son útiles? ¡Comparte tus pensamientos y experiencias a continuación!
Créditos de las imágenes: lupa en una hoja de cálculo Via Shutterstock