¿cómo utilizar las funciones de búsqueda en excel?

archivos de muestra

1. Función DIRECCIÓN

La función DIRECCIÓN devuelve una referencia de celda válida basada en la dirección de columna y fila. En términos simples, puede crear la dirección de una celda usando su número de fila y número de columna.

Sintaxis

DIRECCIÓN(núm_línea,núm_columna,núm_abs,A1,texto_hoja)

Argumentos

• row_num: un número para especificar el número de fila.
• column_num: un número para especificar el número de columna.
• [abs_num]: Tipo de referencia.
• [A1]: estilo de referencia.
• [sheet_text]: un valor de texto como nombre de la hoja.

Comentarios

• De forma predeterminada, la función DIRECCIÓN devuelve una referencia absoluta en el resultado.

Ejemplo

En el siguiente ejemplo, utilizamos diferentes argumentos para obtener todo tipo de resultados.

Con estilo de referencia R1C1 :

• Referencia relativa.
• Referencia de fila relativa y referencia de columna absoluta.
• Referencia absoluta de fila y columna relativa.
• Referencia absoluta.

Con estilo de referencia A1:

• Referencia relativa.
• Referencia de fila relativa y referencia de columna absoluta.
• Referencia absoluta de fila y columna relativa.
• Referencia absoluta.

2. Función ZONAS

La función ÁREAS devuelve un número que representa el número de rangos en la referencia que especificó. En términos simples, en realidad cuenta las diferentes áreas de la hoja de trabajo a las que hizo referencia en la función.

sintaxis

CAMPOS (referencia)

Argumentos

• referencia: una referencia a una celda o rango de celdas.

Comentarios

• La referencia puede ser una celda, un rango de celdas o un rango con nombre.
• Si desea hacer referencia a varias referencias de celda, debe encerrar todas esas referencias entre varios conjuntos de paréntesis y usar comas para separar cada referencia de las demás.

Ejemplo

En el siguiente ejemplo, utilizamos la función de zonas para obtener la referencia numérica en un rango con nombre.

Como puede ver, hay tres columnas en el rango y arrojó 3 en el resultado.

3. ELIJA la función

La función SELECT devuelve un valor de la lista de valores según el número de posición especificado. En términos simples, busca un valor en una lista según su posición y lo devuelve en el resultado.

Sintaxis

SELECCIONAR (núm_índice, valor1, valor2,…)

Argumentos

• index_num: número utilizado para especificar la posición del valor en la lista.
• valor1: un rango de celdas o valor de entrada entre el que puede elegir.
• [valor2]: un rango de celdas o valor de entrada entre los que puede elegir.

Comentarios

• Puedes hacer referencia a una celda o también puedes insertar valores directamente en la función.

Ejemplo

En el siguiente ejemplo, utilizamos la función ELEGIR con una lista desplegable para calcular cuatro cosas diferentes (suma, promedio, máximo y mezcla). Entonces usamos la siguiente fórmula para calcular las cuatro cosas:

= ELEGIR (BUSCARV(K2,Q1:R4,2,FALSO),SUM(O2:O9),PROMEDIO(O2:O9),MAX(O2:O9),MIN(O2:O9))

Tenemos esta tablita con los nombres de los cuatro cálculos que queremos y un número de serie para cada uno en la celda correspondiente.

Después de eso tenemos una lista desplegable para los cuatro cálculos. Ahora, para obtener el número de índice en la función de elección de esta pequeña tabla, tenemos una fórmula de búsqueda que devolverá el número de serie según el valor seleccionado en la lista desplegable.

Y en lugar de valores, utilizamos cuatro fórmulas para 4 cálculos diferentes.

4. Función COLUMNA

La función COLUMNA devuelve el número de columna para la referencia de celda dada. Como sabes, cada referencia de celda consta de un número de columna y un número de fila. Entonces toma el número de columna y lo devuelve en el resultado.

Sintaxis

COLUMNA([referencia])

Argumentos

• referencia: una referencia de celda para la cual desea obtener el número de columna.

Comentarios

• No puede hacer referencia a varias referencias.
• Si se refiere a una matriz, la función de columna también devolverá los números de columna en una matriz.
• Si se refiere a un rango de varias celdas, devolverá el número de columna de la celda más a la izquierda. Por ejemplo, si hace referencia al rango A1:C10, devolverá el número de columna de la celda A1.
• Si omite especificar una referencia, devolverá el número de columna de la celda actual.

Ejemplo

En el siguiente ejemplo, usamos COLUMNA para obtener el número de columna de la celda A1.

Como ya mencioné, si ignora la referencia de la celda, se devolverá el número de columna de la celda actual. En el siguiente ejemplo, utilizamos COLUMNA para crear un encabezado con números de serie.

5. Función COLUMNAS

La función COLUMNAS devuelve el número de columnas a las que se hace referencia en la referencia dada. En términos simples, cuenta el número de columnas en el rango proporcionado y devuelve ese número.

Sintaxis

COLUMNAS(tabla)

Argumentos

• tabla: una tabla o rango de celdas de las que desea obtener el número de columnas.

Comentarios

• También puede utilizar un rango con nombre.
• La función COLUMNAS no se ocupa de los valores en las celdas, simplemente devolverá el número de columnas en una referencia.

Ejemplo

En el siguiente ejemplo, usamos COLUMNA para obtener el número de columnas en el rango A1:F1.

6. Función TEXTO DE FÓRMULA

La función FORMULATEXT devuelve la fórmula de la celda a la que se hace referencia. Y si no hay ninguna fórmula en la celda referenciada, un valor o un espacio en blanco, devolverá un #N/A.

Sintaxis

FÓRMULA DE TEXTO (referencia)

Argumentos

• referencia: la referencia de celda de la que desea obtener la fórmula como texto.

Comentarios

• Si hace referencia a otro libro de trabajo, ese libro de trabajo debe estar abierto; de lo contrario, no mostrará la fórmula.
• Si se refiere a un rango de más de una celda, devolverá la fórmula para la celda superior izquierda del rango dado.
• Devolverá un valor de error «#N/A» si la celda que está utilizando como referencia no contiene ninguna fórmula, tiene una fórmula con más de 8192 caracteres, una celda está protegida o un libro externo no está abierto.
• Si hace referencia a dos celdas en una referencia circular, se devolverán los resultados de ambas.

Ejemplo

En el siguiente ejemplo, hemos utilizado texto de fórmula con diferentes tipos de referencia. Cuando haces referencia a una celda que no tiene una fórmula, devolverá el valor de error «#N/A».

7. Función BÚSQUEDA H

La función BUSCARH busca un valor en la fila superior de una tabla y devuelve el valor en la misma columna del valor coincidente utilizando el número de índice. En términos simples, busca horizontalmente.

Sintaxis

BUSCARH(valor_búsqueda, matriz_tabla, núm_índice_fila, [rango_búsqueda])

Argumentos

• valor_buscado: el valor que desea buscar.
• table_array: la tabla o matriz de datos de la que desea encontrar el valor.
• row_index_num: un valor numérico que representa un número de filas hacia abajo desde la fila superior de la que desea obtener el valor. Por ejemplo, si especifica 2 y su valor de búsqueda está en A10 en la tabla de datos, devolverá el valor en la celda B10.
• [range_lookup]: un valor lógico para especificar el tipo de búsqueda. Si desea realizar una búsqueda de coincidencia exacta, utilice FALSO y si desea realizar una búsqueda de coincidencia no exacta, utilice VERDADERO (predeterminado).

Comentarios

• Puedes utilizar comodines.
• Puede hacer una coincidencia exacta y una coincidencia aproximada.
• Al realizar una coincidencia aproximada, asegúrese de ordenar los datos en orden ascendente de izquierda a derecha; si los datos no están en orden ascendente, arrojará un resultado inexacto.
• Si range_lookup es verdadero o se omite, realizará una coincidencia no exacta pero devolverá una coincidencia exacta si el valor de búsqueda existe en el rango de búsqueda.
• Si range_lookup es verdadero o se omite, y el valor de búsqueda no está en el rango de búsqueda, devolverá el valor más cercano que sea menor que el valor de búsqueda.
• Si range_lookup es falso, no es necesario ordenar el rango de datos.

Ejemplo

En el siguiente ejemplo, usamos la función BUSCARH con COINCIDIR para crear una fórmula dinámica y luego usamos una lista desplegable para cambiar el valor de búsqueda de la celda.

El nombre del campo de la celda C7 se utiliza como valor de búsqueda. Rango B1: F5 como matriz de tabla y para row_index_num usamos la función de coincidencia para obtener el número de fila.

Siempre que cambie el valor en la celda C9, devolverá el número de fila de la tabla. No es necesario que cambie su fórmula una y otra vez. Simplemente cambie los valores con el menú desplegable y obtendrá un valor.

8. Función HIPERVÍNCULO

La función HIPERVÍNCULO devuelve una cadena con un hipervínculo adjunto. En palabras simples, al igual que la opción HIPERVÍNCULO que tienes en Excel, la función HIPERVÍNCULO te ayuda a crear un hipervínculo.

Sintaxis

HIPERVÍNCULO(ubicación_enlace,[nombre_amigable])

Argumentos

• Link_Location: La ubicación para la cual desea agregar un HIPERVÍNCULO. Se puede dividir a su vez en dos términos.
  1. enlace: puede ser la dirección de una celda o un rango de celdas en la misma hoja de trabajo o en cualquier otra hoja de trabajo o libro de trabajo. También podemos vincular un marcador desde un documento de Word.
  2. Ubicación: puede ser un enlace a un disco duro, un servidor que utiliza la ruta UNC o cualquier URL de Internet o intranet. (En Excel online, sólo puedes utilizar la dirección web para la función HIPERVÍNCULO). Puede insertar un vínculo a la función insertándolo como texto entre comillas o haciendo referencia a una celda que contiene el vínculo como texto. Asegúrese de utilizar «HTTPS://» antes de una dirección web.
• [nombre_amigable]: Esta es una parte opcional de esta función. Actúa como la cara del enlace de conexión.
  1. Puedes utilizar cualquier tipo de texto, número o ambos.
  2. También te estás refiriendo a una celda que contiene el nombre_descriptivo.
  3. Si lo ignora, la función utilizará la dirección del enlace para mostrarse.
  4. Si nombre_amigos devuelve un error, la función mostrará un error.

Comentarios

• Vincular un archivo guardado en una dirección web: puede utilizar un archivo guardado en una dirección web. Esto nos ayuda a compartir el archivo de manera eficiente.
• Vincular un archivo guardado en un disco duro: también puede utilizar esta función cuando trabaje sin conexión. Puede vincular un archivo almacenado en su disco duro y acceder a él a través de su única hoja de Excel, sin necesidad de ir a cada carpeta para abrirlas.
• Vincular archivo de documento de Word: esta también es una característica increíble de la función HYPERLINK. Puede vincular un archivo de documento de Word o una ubicación específica en un archivo de documento de Word mediante un marcador.
• Vincular un archivo sin usar un nombre descriptivo: si desea mostrar el vínculo real al archivo o la ubicación al usuario. En esta situación, sólo necesita ignorar la declaración del nombre descriptivo en la función HIPERVÍNCULO.

9. Función INDIRECTA

La función INDIRECTA devuelve una referencia válida de una cadena de texto que representa una referencia de celda. En términos simples, puede hacer referencia a un rango de celdas utilizando la dirección de la celda como valor de texto.

Sintaxis

INDIRECTO(texto_ref, [a1])

Argumentos

• ref_text: texto que representa la dirección de una celda, la dirección de un rango de celdas, un rango con nombre o el nombre de una tabla. Por ejemplo, A1, B10:B20 o MiRango.
• [a1]: un número o valor booleano para representar el tipo de referencia de celda que especifica en ref_text. Por ejemplo, si desea utilizar el estilo de referencia A1, utilice VERDADERO o 1, y si desea utilizar el estilo de referencia R1C1, utilice FALSO o 0 para el estilo de referencia R1C. Y si no especifica el tipo de referencia de celda, utilizará el estilo A1 de forma predeterminada.

Comentarios

• Cuando haya hecho referencia a otro libro de trabajo, ese libro de trabajo debe abrirse.
• Si inserta una fila o columna en el rango al que hizo referencia, INDIRECT no actualizará esa referencia.
• Si desea insertar texto directamente en la función, debe encerrarlo entre comillas dobles o también puede hacer referencia a una celda que contenga el texto que desea usar como referencia.

Ejemplo

1. Referencia a otra hoja de trabajo

También puede consultar otra hoja de trabajo usando INDIRECTO y debe insertar el nombre de la hoja de trabajo allí. En el siguiente ejemplo, usamos la función indirecta para hacer referencia a otra hoja de trabajo y tener el nombre de la hoja en la celda A2 y la referencia de la celda en la celda B2.

En la celda C2, utilizamos la siguiente fórmula para combinar el texto.

=INDIRECTO(“’”&A2&”’!”&B2)

Esta combinación crea texto que utiliza la función INDIRECTA para hacer referencia a la celda A1 en la hoja1 y la mejor parte es que cuando cambia el nombre de la hoja de cálculo o la dirección de la celda, la referencia cambiará automáticamente.

La celda A1 en «Hoja1» tiene el valor «Sí» y es por eso que indirecto devuelve el valor «Sí».

2. Referencia a otro libro de trabajo

También puede consultar otro libro de trabajo, de la misma manera que lo hicimos con otra hoja de trabajo. Todo lo que necesita hacer es simplemente agregar un nombre de libro de trabajo en su texto que usará como referencia.

En el ejemplo anterior, utilizamos la siguiente fórmula para obtener el valor de la celda A1 del libro «Libro1».

=INDIRECTO(“[“&A2&”]”&B2&”!”&C2)

Como tenemos el nombre del libro de trabajo en la celda «A2», el nombre de la hoja de trabajo en la celda «B2» y el nombre de la celda en la celda «C2». Los combinamos para usarlos como texto de entrada en una función indirecta.

Nota: Al combinar una referencia de celda como texto, asegúrese de seguir la estructura de referencia correcta.

3. Uso de rangos con nombre

Sí, también puede hacer referencia a un rango con nombre utilizando la función indirecta. Es sencillo. Una vez que haya creado un rango con nombre, debe ingresar ese rango con nombre como texto en INDIRECTO.

En el ejemplo anterior, tenemos una lista desplegable en la celda E1 que contiene una lista de rangos con nombre, y en la celda E2 hemos usado ese nombre. Como el rango B2:B5 se denomina «Cantidad» y el rango C2:C5 se denomina «Cantidad».

Cuando selecciona una cantidad de la lista desplegable, la función indirecta hace referencia instantáneamente al rango nombrado. Y cuando selecciones la cantidad en el menú desplegable, tendrás la suma del rango de celdas C2:C5.

11. Función de BÚSQUEDA

La función BÚSQUEDA devuelve un valor (que está buscando) de una fila, columna o tabla . En términos simples, puede buscar un valor y BÚSQUEDA devolverá ese valor si está en esa fila, columna o tabla.

Sintaxis

BÚSQUEDA(valor, rango_búsqueda, [rango_resultado])

Hay dos tipos de funciones de BÚSQUEDA.

• forma vectorial
• Forma de tabla

Argumentos

• valor: el valor que desea buscar en una columna o fila.
• lookup_range: la columna o fila desde la que desea buscar el valor.
• [rango_resultado]: la columna o fila desde la que desea devolver un valor. Este es un argumento opcional.

Comentarios

• En lugar de utilizar la forma de matriz, es mejor utilizar BUSCARV o BUSCARH.

12.Función PARTIDO

La función COINCIDIR devuelve el número de índice de un valor de matriz. En términos simples, la función COINCIDIR busca un valor en la lista y devuelve el número de posición de ese valor en la lista.

Sintaxis

COINCIDIR (valor_buscado, matriz_buscada, [tipo_coincidencia])

Argumentos

• valor_buscado : el valor cuya posición desea obtener de una lista de valores.
• lookup_array : el rango de celdas o la matriz contiene valores.
• [match_type] : el número (-1, 0 y 1) para especificar cómo Excel busca el valor en la lista de valores.
  1. Si usa 1, devolverá el valor más grande igual o menor que el valor de búsqueda. Los valores de la lista deben ordenarse en orden ascendente.
  2. Si usa -1, devolverá el valor más pequeño igual o mayor que el valor de búsqueda. Los valores de la lista deben ordenarse en orden ascendente.
  3. Si usa 0, devolverá la coincidencia exacta de la lista.

Comentarios

• Puedes utilizar comodines .
• Si no hay ningún valor coincidente en la lista, devolverá #N/A.
• La función de coincidencia no distingue entre mayúsculas y minúsculas.

Ejemplo

En el siguiente ejemplo, utilizamos 1 como tipo de coincidencia y buscamos el valor 5.

Como ya mencioné, si usa 1 en el tipo de coincidencia, devuelve el valor más grande igual o menor que el valor de búsqueda. En toda la lista hay 3 valores menores que 5 y 4 es el más alto.

Por eso en el resultado devolvió 3 que es la posición del valor 4.

13. Función de cambio

La función DESPLAZAMIENTO devuelve una referencia a un rango que está a un número específico de filas y columnas de una celda o rango de celdas. En términos simples, puede hacer referencia a una celda o un rango de celdas usando filas y columnas a partir de una celda inicial.

Sintaxis

DESPLAZAMIENTO(referencia, filas, columnas, [alto], [ancho])

Argumentos

• referencia : La referencia desde la cual desea compensar para comenzar. Puede ser una celda o un rango de celdas adyacentes.
• filas : el número de filas que le indican a OFFSET que se mueva hacia arriba o hacia abajo desde la referencia. Para bajar se necesita un número positivo y para subir se necesita un número negativo.
• cols : el número de columnas le indica a OFFSET que se mueva hacia la izquierda o hacia la derecha desde la referencia. Para ir a la derecha necesitas un número positivo y para ir a la izquierda necesitas un número negativo.
• [altura] : un número para especificar qué líneas incluir en la referencia.
• [ancho]: Un número para especificar qué columnas incluir en la referencia.

Comentarios

• OFFSET es una función “volátil”, se recalcula cada vez que hay un cambio en una hoja de cálculo.
• Muestra el #REF! valor de error si el desplazamiento está fuera del borde de la hoja de trabajo.
• Si se omite la altura o el ancho, se utilizan la altura y el ancho de referencia.

Ejemplo

En el siguiente ejemplo, utilizamos SUM con OFFSET para crear un rango dinámico que suma los valores de todos los meses de un producto en particular.

14. Función LÍNEA

La función FILA devuelve el número de fila de la celda a la que se hace referencia. En palabras simples, con la función FILA puedes obtener el número de fila de una celda y si no estás haciendo referencia a ninguna celda, te devuelve el número de fila de la celda donde la insertas.

Sintaxis

LÍNEA([referencia])

Argumentos

• referencia: una referencia de celda o rango de celdas cuyo número de fila desea verificar.

Comentarios

• Incluirá todo tipo de hojas (hoja de gráfico, hoja de trabajo o hoja de macro).
• Puede consultar las publicaciones incluso si están visibles, ocultas o muy ocultas.
• Si no especificas ningún valor en la función, te dará el número de hoja de la hoja en la que aplicaste la función.
• Si especifica un nombre de hoja no válido, devolverá un #N/A.
• Si especifica una referencia de hoja no válida, devolverá un #REF!.

Ejemplo

En el siguiente ejemplo, hemos usado la función de fila para verificar el número de fila de la misma celda donde usamos la función.

En el siguiente ejemplo, hemos hecho referencia a otra celda para obtener el número de fila de esa celda.

Puede utilizar la función de fila para crear una lista de números de serie en su hoja de cálculo. Todo lo que necesita hacer es simplemente ingresar funciones de fila en una celda y arrastrarlas a la celda donde desea agregar números de serie.

15. Función LÍNEAS

La función FILAS devuelve el número de filas en el rango referenciado. En palabras simples, con la función FILAS, puedes contar el número de filas en el rango al que te refieres.

Sintaxis

LÍNEAS (tabla)

Argumentos

• matriz: una referencia de celda o matriz para verificar el número de filas.

Comentarios

• También puede utilizar un rango con nombre.
• No se preocupa por los valores en las celdas, simplemente devolverá el número de filas en una referencia.

Ejemplo

En el siguiente ejemplo, hicimos referencia a un rango vertical de 10 celdas y devolvió 10 en el resultado porque el rango tiene 10 filas.

16. Función TRANSPONER

La función TRANSPONER cambia la orientación de un rango. En palabras simples, usando esta función puedes cambiar datos de una fila a una columna y de una columna a una fila.

Sintaxis

TRANSPONER (tabla)

Argumentos

• matriz: una matriz o rango que desea transponer.

Comentarios

• Debe aplicar TRANSPONER como una función de matriz, utilizando la misma cantidad de celdas que tiene en su rango de origen presionando Ctrl+Shift+Enter.
• Si selecciona celdas más pequeñas que el rango de origen, los datos solo se transpondrán para esas celdas.

Ejemplo

Aquí necesitamos transponer los datos del rango B2:D4 al rango G2 a I4:

Para esto, primero debemos ir a la celda G2 y seleccionar el rango de celdas hasta I4.

Luego, ingrese (=TRANSPOSE(B2:D4)) en la celda G2 y presione Ctrl+Shift+Enter.

TRANSPOSE convertirá datos de filas a columnas, y la fórmula que aplicamos es una fórmula matricial, no se puede cambiar ni una sola celda.