Digamos que necesita encontrar la extensión de teléfono de un empleado usando su número y también estimar correctamente el índice de comisión por el monto de las ventas. Usted busca datos para encontrar rápida y eficientemente datos específicos en una lista y verificar automáticamente que los datos se estén utilizando correctamente. Después de ver los datos, puede realizar cálculos y mostrar los resultados, indicando los valores de retorno. Hay varias formas de buscar valores en una lista de datos y mostrar los resultados.

En este articulo

Encuentra valores en una lista verticalmente por coincidencia exacta

Puede utilizar la función BUSCARV o una combinación de las funciones ÍNDICE y COINCIDIR para realizar esta tarea.

Ejemplos de uso de la función BUSCARV

Función BUSCARV.

Ejemplos de ÍNDICES y PARTIDOS

Qué significa:

=ÍNDICE(debe devolver el valor de C2:C10, que coincidirá con COINCIDIR(el primer valor "Repollo" en la matriz B2:B10))

La fórmula busca el primer valor en la celda C2:C10 correspondiente a repollo(en B7) y devuelve el valor en C7 ( 100 ) - el primer valor correspondiente repollo.

Para obtener más información, consulte Función ÍNDICE y Función COINCIDIR.

Encuentra valores en una lista verticalmente por coincidencia aproximada

Para hacer esto, use la función BUSCARV.

Importante:

En el ejemplo anterior, la función BUSCARV busca el nombre de un estudiante que tiene 6 tardanzas en el rango A2:B7. No hay ninguna entrada en la tabla para 6 tardanzas, por lo que la función BUSCARV busca la siguiente coincidencia más alta debajo de 6 y encuentra el valor 5 asociado con el nombre dave, y por lo tanto regresa dave.

Para obtener más información, consulte la función BUSCARV.

Encontrar valores verticales en una lista de tamaño desconocido con coincidencia exacta

Para realizar esta tarea, utilice las funciones COMPENSACIÓN y COINCIDIR.

Nota: Este enfoque se utiliza si los datos se encuentran en un rango de datos externo que actualiza todos los días. Sabe que la columna B tiene Precio, pero no sabe cuántas filas de datos devuelve el servidor y la primera columna no está ordenada alfabéticamente.

C1 es la celda superior izquierda del rango (también llamada celda inicial).

Coincidencia("naranjas"; C2: C7; 0) busca el color naranja en el rango C2:C7. No debes incluir la celda inicial en el rango.

1 - el número de columnas a la derecha de la celda inicial para las que se debe devolver el valor de retorno. En nuestro ejemplo, el valor de retorno está en la columna D, Ventas.

Encuentra valores en una lista horizontalmente por coincidencia exacta

Para realizar esta tarea se utiliza la función BUSCAR. A continuación se muestra un ejemplo.

La función BUSCAR busca una columna Ventas y devuelve el valor de la línea 5 en el rango especificado.

Para obtener más información, consulte Funciones de BÚSQUEDA.

Encontrar valores en una lista horizontalmente usando una coincidencia aproximada

Para realizar esta tarea se utiliza la función BUSCAR.

Importante: Asegúrese de que los valores de la primera fila estén ordenados en orden ascendente.

En el ejemplo anterior, la función BUSCAR busca el valor 11000 en la fila 3 dentro del rango especificado. No encuentra 11000 y, por lo tanto, busca el siguiente valor más grande que sea menor que 1100 y devuelve el número 10543.

Para obtener más información, consulte Funciones de BÚSQUEDA.

Cree una fórmula de búsqueda utilizando el Asistente de búsqueda (solo Excel 2007)

Nota: El complemento Asistente de búsqueda se ha descontinuado en Excel 2010. Esta funcionalidad ha sido reemplazada por el Asistente de funciones y la funcionalidad de búsqueda y referencia (referencia) disponible.

En Excel 2007, el Asistente de búsqueda crea una fórmula de búsqueda basada en datos de la hoja de cálculo que tiene encabezados de fila y columna. El Asistente de búsqueda le ayuda a encontrar otros valores en una fila cuando conoce el valor de una columna y viceversa. El Asistente de búsqueda utiliza índice y COINCIDIR en las fórmulas que crea.

Hola amigos. ¿Con qué frecuencia hay que buscar una coincidencia en una hoja de cálculo de Excel para un valor? Por ejemplo, necesita encontrar la dirección de una persona en un directorio o el precio de un producto en una lista de precios. Si ocurren tales tareas, ¡esta publicación es solo para ti!

Realizo procedimientos similares todos los días y sin las funciones que se describen a continuación lo pasaría realmente mal. ¡Toma nota y aplícalos en tu trabajo!

Buscar en tabla de Excel, funciones BUSCARV y BUSCAR

Es difícil sobreestimar el papel de estas funciones en la vida de un usuario normal. Ahora puede encontrar fácilmente una entrada adecuada en la tabla de datos y devolver el valor correspondiente.

La sintaxis de la función BUSCARV es: =BUSCARV( valor de búsqueda; tabla de búsqueda; número_columna_salida; [tipo_mapeo]). Veamos los argumentos:

• Valor de búsqueda– el valor que buscaremos. Este es un argumento requerido;
• Tabla de búsqueda– el conjunto de celdas en las que se realizará la búsqueda. La columna con los valores buscados debe ser la primera de esta matriz. Éste también es un argumento obligatorio;
• Número de columna a mostrar– el número de serie de la columna (comenzando desde la primera en la matriz), desde la cual la función mostrará datos si los valores requeridos coinciden. Argumento requerido;
• tipo coincidente– seleccione “1” (o “VERDADERO”) para una coincidencia débil, “0” (“FALSO”) para una coincidencia completa. El argumento es opcional; si se omite, se realizará una búsqueda. partido no estricto.

Encontrar una coincidencia exacta usando BUSCARV

Veamos un ejemplo de cómo funciona la función BUSCARV cuando el tipo de coincidencia es FALSO, buscando una coincidencia exacta. La matriz B5:E10 indica los activos fijos de una determinada empresa, su valor contable, número de inventario y ubicación. La celda B2 contiene el nombre para el cual necesita encontrar el número de inventario en la tabla y colocarlo en la celda C2.

Función BUSCARV en Excel

Escribamos la fórmula: =BUSCARV(B2,B5:E10,3,FALSO) .

Aquí el primer argumento indica que en la tabla debe buscar el valor de la celda B2, es decir la palabra "Fax". El segundo argumento dice que la tabla a buscar está en el rango B5:E10, y hay que buscar la palabra “Fax” en la primera columna, es decir en la matriz B5:B10. El tercer argumento le dice al programa que el resultado del cálculo está contenido en la tercera columna de la matriz, es decir D5:D10. El cuarto argumento es FALSO, es decir. Se requiere una coincidencia completa.

Y así, la función recibirá la cadena “Fax” de la celda B2 y la buscará en el array B5:B10 de arriba a abajo. Una vez que se encuentra una coincidencia (línea 8), la función devolverá el valor correspondiente de la columna D, es decir Contenido D8. Esto es exactamente lo que necesitábamos, el problema está resuelto.

Si no se encuentra el valor buscado, la función devolverá .

Encontrar una coincidencia imprecisa usando BUSCARV

Gracias a esta opción en BUSCARV, podemos evitar fórmulas complejas para encontrar el resultado deseado.

La matriz B5:C12 muestra las tasas de interés de los préstamos según el monto del préstamo. En la celda B2 indicamos el monto del préstamo y queremos recibir la tasa por dicha transacción en C2. La tarea es difícil porque la cantidad puede ser cualquier cantidad y es poco probable que coincida con las especificadas en la matriz; buscar una coincidencia exacta no es adecuado:

Luego escribimos la fórmula para una búsqueda no estricta: =BUSCARV(B2,B5:C12,2,VERDADERO). Ahora, de todos los datos presentados en la columna B, el programa buscará el más pequeño más cercano. Es decir, para la cantidad de 8.000 se seleccionará el valor de 5.000 y se mostrará el porcentaje correspondiente.

Búsqueda BUSCARV no estricta en Excel

Para que la función funcione correctamente, debe ordenar la primera columna de la tabla en orden ascendente. De lo contrario, puede dar un resultado erróneo.

La función BUSCAR tiene la misma sintaxis que BUSCARV, pero busca el resultado en filas en lugar de columnas. Es decir, escanea las tablas no de arriba a abajo, sino de izquierda a derecha y muestra el número de fila especificado, no la columna.

Búsqueda de datos mediante la función VER

La función BUSCAR funciona de manera similar a BUSCARV, pero tiene una sintaxis diferente. Lo uso cuando la tabla de datos contiene varias docenas de columnas y para usar BUSCARV necesitas calcular adicionalmente el número de la columna de salida. En tales casos, la función VER facilita la tarea. Y así, la sintaxis: =VER( valor de búsqueda; matriz_para_buscar; matriz_para_mostrar) :

• Valor de búsqueda– datos o un enlace a los datos que se van a buscar;
• Matriz para buscar– una fila o columna en la que buscamos un valor similar. Debemos ordenar esta matriz en orden ascendente;
• Matriz para mostrar– un rango que contiene datos para mostrar resultados. Naturalmente, debe tener el mismo tamaño que la matriz de búsqueda.

Al escribir de esta manera, está dando una referencia no relativa a la matriz de resultados. Y lo señalas directamente, es decir. no es necesario calcular primero el número de la columna de salida. Usamos la función VER en el primer ejemplo para la función BUSCARV (activos fijos, números de inventario): =VER(B2,B5:B10,D5:D10). ¡El problema se ha resuelto con éxito!

Función VER en Microsoft Excel

Búsqueda por coordenadas relativas. Funciones COINCIDIR e ÍNDICE

Otra forma de buscar datos es combinar las funciones COINCIDIR e ÍNDICE.

El primero de ellos se utiliza para buscar un valor en un array y obtener su número de serie: BÚSQUEDA( valor de búsqueda; matriz_vista; [tipo coincidente] ). Argumentos de función:

• Valor de búsqueda– argumento requerido
• Matriz para ser vista– una fila o columna en la que buscamos una coincidencia. Argumento requerido
• tipo coincidente– especifique “0” para buscar una coincidencia exacta, “1” para la coincidencia más pequeña más cercana, “-1” para la coincidencia más grande más cercana. Dado que la función busca desde el principio de la lista hasta el final, cuando busque la más pequeña más cercana, ordene la columna de búsqueda en orden descendente. Y cuando busque más, ordénelo en orden ascendente.

Se ha encontrado la posición del valor requerido, ahora puede mostrarlo en la pantalla usando la función ÍNDICE( Formación; número de línea; [Número_columna]) :

• Formación– el argumento especifica de qué matriz de celdas se debe seleccionar el valor
• Número de línea– indique el número de serie de la línea (comenzando desde la primera celda de la matriz) que desea mostrar. Aquí puede escribir el valor manualmente o utilizar el resultado de otro cálculo de función. Por ejemplo, BUSCAR.
• Número de columna– argumento opcional, especificado si la matriz consta de varias columnas. Si se omite un argumento, la fórmula utiliza la primera columna de la tabla.

Ahora combinemos estas funciones para obtener el resultado:

Funciones COINCIDIR e ÍNDICE en Excel

Estos son los métodos para buscar y mostrar datos que existen en Excel. Además, puede utilizarlos en cálculos, utilizarlos en presentaciones, realizar operaciones con ellos, especificarlos como argumentos para otras funciones, etc.

¿Sientes cómo tus conocimientos y habilidades crecen y se fortalecen? Entonces no pares, ¡sigue leyendo! En el próximo post lo consideraremos: ¡será difícil e interesante!

Fecha: 3 de enero de 2016 Categoría:

La búsqueda de caracteres en una cadena es la tarea más importante que le permite automatizar el trabajo con texto. Por ejemplo, lo hace más fácil cuando se desconoce la posición de la línea objetivo.

Mencioné la herramienta de búsqueda de Excel en , pero aquí veremos funciones que buscan texto en una cadena y devuelven su posición inicial.

Hay dos funciones para buscar texto en Microsoft Excel:

1. ENCONTRAR() . Esta función tiene 2 argumentos obligatorios y uno opcional:
   • Buscar texto– la secuencia de caracteres que hay que encontrar. Este es un argumento requerido
   • Texto original– la cadena en la que se realiza la búsqueda. Argumento requerido
   • Posición inicial– el número de serie del carácter del texto fuente a partir del cual comienza la búsqueda. Argumento opcional. Si no se especifica, la búsqueda comienza desde el primer carácter.

función ENCONTRAR toma en cuenta Y no admite el uso de comodines(lea más sobre ellos).

Función ENCONTRAR en Excel

1. BUSCAR(Buscar texto; Texto original; [Posición de salida]) tiene una sintaxis y argumentos similares. Se diferencia en que no distingue entre mayúsculas y minúsculas Y admite el uso de comodines.

Función de BÚSQUEDA en Excel

Como puede ver, esta función dio un resultado diferente porque la búsqueda no distingue entre mayúsculas y minúsculas.

¿Qué son los comodines, preguntas? Estos son caracteres especiales que le permiten omitir una parte desconocida del texto para realizar la búsqueda:

• ? – cualquier símbolo
• * – cualquier secuencia de caracteres

Si el signo de interrogación o el asterisco forman parte de la cadena de búsqueda y no son un carácter comodín, precedido por una tilde (~).

Anotemos la siguiente fórmula: =BUSCAR(“??chn”,A1,1). La función buscará una cadena de dos caracteres cualesquiera seguidos de las letras "chn". La búsqueda coincide con parte de la palabra "tabular" en el texto de ejemplo, es decir combinación de personajes "personales". El resultado de la función será el valor "4".

Comodines en Excel

Ahora escribamos la fórmula =BUSCAR(“p*s”,A2,1) . La función busca la combinación “p” + cualquier número de caracteres + “s”. En el ejemplo, esto es parte de la palabra "procesador". El resultado de la función es 11.

Comodín de asterisco en Excel

Tener las funciones de búsqueda le facilita rastrear la posición del texto deseado, por lo que es más fácil reemplazarlo. Asegúrate de practicar su uso. Mi post-tutorial sobre funciones de texto te ayudará con esto.

Y el próximo artículo estará dedicado a ello. ¡Un tema importante que definitivamente necesita ser estudiado!

¡Haz preguntas en los comentarios y suscríbete a las actualizaciones para no perderte nada!

Disponemos de una tabla que registra los volúmenes de ventas de determinados productos en diferentes meses. Necesita encontrar datos en la tabla y el criterio de búsqueda serán los encabezados de filas y columnas. Pero la búsqueda debe realizarse por separado por rango de filas o columnas. Es decir, sólo se utilizará uno de los criterios. Por lo tanto, la función ÍNDICE no se puede utilizar aquí, sino que se necesita una fórmula especial.

Encontrar valores en una tabla de Excel

Para resolver este problema, ilustramos un ejemplo en una tabla esquemática que corresponde a las condiciones descritas anteriormente.

Hoja con tabla para buscar valores vertical y horizontalmente:

Encima de la tabla hay una línea con los resultados. En la celda B1 ingresamos el criterio para la consulta de búsqueda, es decir, el encabezado de la columna o el nombre de la fila. Y en la celda D1, la fórmula de búsqueda debería devolver el resultado del cálculo del valor correspondiente. Posteriormente, la segunda fórmula funcionará en la celda F1, que ya utilizará los valores de las celdas B1 y D1 como criterio para buscar el mes correspondiente.

Encontrar un valor en una cadena de Excel

Ahora averiguamos cuál fue el volumen máximo y en qué mes fue la venta máxima del Producto 4.

Para buscar por columnas:

Encontrado en qué mes y cuál fue la mayor venta del Producto 4 durante dos trimestres.

El principio de la fórmula para encontrar un valor en una fila de Excel:

El primer argumento de la función BUSCARV (Vista vertical) especifica un enlace a la celda donde se encuentra el criterio de búsqueda. El segundo argumento especifica el rango de celdas que se verán durante la búsqueda. El tercer argumento de la función BUSCARV debe indicar el número de la columna de la cual se debe tomar el valor de la fila con el nombre Producto 4. Pero como no conocemos este número de antemano, usamos la función COLUMNA para crear una matriz. de números de columna para el rango B4:G15.

Esto permite que la función BUSCARV recopile una matriz completa de valores. Como resultado, todos los valores correspondientes para cada columna de la fila Producto 4 se almacenan en la memoria (a saber: 360; 958; 201; 605; 462; 832). Después de eso, la función MAX solo puede tomar el número máximo de esta matriz y devolverlo como valor para la celda D1, como resultado del cálculo de la fórmula.

Como puedes ver, el diseño de la fórmula es simple y conciso. En base a él, puede encontrar otros indicadores para un producto específico de manera similar. Por ejemplo, el valor mínimo o promedio del volumen de ventas usando las funciones MIN o PROMEDIO. Nada le impide aplicar esta fórmula básica utilizando funciones más complejas para implementar el análisis más cómodo del informe de ventas.

¿Cómo obtener encabezados de columna basados ​​en el comienzo de una celda?

Por ejemplo, con qué eficacia mostramos el mes en el que hubo una venta máxima utilizando la segunda fórmula. No es difícil notar que en la segunda fórmula usamos el esqueleto de la primera fórmula sin la función MAX. La estructura principal de la fórmula es: BUSCARV(B1,A5:G14,COLUMNA(B5:G14),0). Hemos sustituido la función MAX por MATCH, que utiliza como primer argumento el valor obtenido por la fórmula anterior. Ahora actúa como criterio para buscar el mes. Y como resultado, la función BÚSQUEDA nos devuelve el número de la columna 2, donde se ubica el valor máximo del volumen de ventas del producto 4. Luego de lo cual se incluye en el trabajo la función ÍNDICE, que devuelve el valor por fecha y número de columna. del rango especificado en sus argumentos. Como tenemos un número de columna 2, y el número de fila en el rango donde se almacenan los nombres de los meses en cualquier caso será 1, entonces solo nos queda usar la función ÍNDICE para obtener el valor correspondiente del rango B4: G4 - febrero (segundo mes).



Encontrar un valor en una columna de Excel

La segunda versión de la tarea sería buscar en la tabla utilizando el nombre del mes como criterio. En tales casos, debemos cambiar el esqueleto de nuestra fórmula: reemplazar la función BUSCARV con BUSCARH y reemplazar la función COLUMNA con FILA.

Esto nos permitirá saber qué volumen y qué producto fueron las ventas máximas en un mes determinado.

Para saber qué producto tuvo el volumen máximo de ventas en un mes determinado, debes:

El principio de la fórmula para encontrar un valor en una columna de Excel:

En el primer argumento de la función HLOO (Vista Horizontal) indicamos un enlace a la celda con el criterio de búsqueda. El segundo argumento especifica una referencia al rango de la tabla que se está viendo. El tercer argumento lo genera la función FILA, que crea una matriz de 10 elementos de números de línea en la memoria. Ya que tenemos 10 filas en la parte tabular.

A continuación, la función BUSCAR, utilizando cada número de línea por turno, crea una matriz de valores de ventas correspondientes de la tabla para un mes específico (junio). A continuación, la función MAX solo necesita seleccionar el valor máximo de esta matriz.

¡ATENCIÓN! Cuando utilice la fórmula esquelética para otros problemas, preste siempre atención al segundo y tercer argumento de la función de búsqueda BUSCAR. El número de filas cubiertas en el rango especificado en el argumento debe coincidir con el número de filas de la tabla. ¡Y además la numeración debe comenzar desde la segunda línea!

Entre miles de filas y decenas de columnas de datos, es casi imposible encontrar algo manualmente en una tabla de Excel. La única opción es utilizar algún tipo de función de búsqueda y a continuación veremos cómo buscar en una tabla de Excel.

Para buscar datos en una tabla de Excel, debe utilizar el elemento de menú "Buscar y seleccionar" en la pestaña "Hogar", en el que debes seleccionar una opción "Encontrar" o utilice la combinación de teclas para llamar "Control + F".

Por ejemplo, intentemos encontrar el número requerido entre los datos de nuestra tabla, ya que es al buscar números que debemos tener en cuenta algunas sutilezas de la búsqueda. Busquemos un número en la tabla de Excel. "10".

Después de seleccionar el elemento de menú requerido, ingrese el valor deseado en el cuadro de búsqueda que aparece. Tenemos dos opciones para buscar valores en una tabla de Excel, esta es encontrar todas las coincidencias a la vez haciendo clic en el botón "Encuentra todos" o ver inmediatamente cada celda encontrada presionando el botón cada vez "Buscar siguiente". Al usar el botón "Buscar siguiente" también debes tener en cuenta la ubicación actual de la celda activa, ya que la búsqueda comenzará desde esta posición.

Intentemos encontrar todos los valores a la vez y todo lo encontrado aparecerá en la ventana debajo de la configuración de búsqueda. Si dejamos todas las configuraciones predeterminadas, el resultado de la búsqueda no será exactamente el que esperábamos.

Para buscar correctamente datos en una tabla de Excel, haga clic en el botón "Opciones" y configurar el área de búsqueda. Ahora se busca el valor deseado incluso en las fórmulas utilizadas en las celdas para los cálculos. Necesitamos especificar la búsqueda solo en valores y, si lo deseamos, también podemos especificar el formato de los datos buscados.

A la hora de buscar palabras en una tabla de Excel, también debes tener en cuenta todas estas sutilezas y, por ejemplo, incluso puedes tener en cuenta el caso de las letras.

Y finalmente, veamos cómo buscar datos en Excel solo en el área requerida de la hoja. Como puede verse en nuestro ejemplo, el valor deseado "10" ocurre en todas las columnas de datos a la vez. Si necesita encontrar este valor, digamos, solo en la primera columna, debe seleccionar esta columna o cualquier área de valores en la que necesite buscar y luego comenzar la búsqueda.