Cómo usar BUSCARV, ÍNDICE, ... en Excel debes saber

Las funciones BUSCARV, ÍNDICE, COINCIDIR, ... son funciones bastante complejas pero efectivas en la minería de datos en Excel . En este artículo, lo guiaré en detalle sobre cómo usar VLOOKUP y funciones relacionadas de manera efectiva.

1. Función VLOOKUP

Uso : busca un valor especificado en la primera columna y extrae datos coincidentes de la misma fila en otra columna. Esta es una de las funciones más populares y proporciona un gran soporte para tareas de oficina complejas.

Fórmula : = VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

En el cual :

+ Lookup_value : el valor para buscar.

+ Table_array : dos o más columnas de datos.

+ Col_index_num : Número de columnas para extraer datos.

+ Range_lookup : determina si la búsqueda es completamente precisa (FALSO) o simplemente relativamente precisa (VERDADERO u omitido).

Ejemplo : usamos la función BUSCARV para encontrar el Bono de cada empleado. El valor a buscar es Posición de celda C2; La matriz a detectar es la matriz F1: G3. Al ingresar la fórmula = VLOOKUP (C2, $ F $ 1: $ G $ 3,2,0) (1) el resultado es STAFF 2000 y ROOMER 5000 (2).

2. Función INDICE

Uso : devuelve una referencia a una celda dentro de una matriz en función del número de filas y columnas que especifique.

Fórmula : = INDICE (matriz, núm_fila, [núm_columna])

Del cual : Matriz (obligatorio): rango de celdas o constante de matriz.

 

 

Ejemplo : cuando necesitamos obtener el valor de la primera fila, la segunda columna de la tabla, usamos la función INDICE . Array es el arreglo que necesitamos detectar es B2: C5; Row_num es la línea 1; Column_num es la columna 2. Al ingresar la fórmula INDICE (B2: C5,1,2,1) (1) el resultado es "JEFE DE SALA" (2).

3. Función MATCH

Uso : busca un valor específico en un rango de celdas y muestra la posición relativa de ese valor.

Fórmula : = COINCIDIR (lookup_value, lookup_array, [match_type])

En el cual :

+ lookup_value (obligatorio) : el valor que desea hacer coincidir en la matriz de búsqueda. Cambiar el valor de búsqueda número puede ser un valor (número, texto o valor lógico) o una referencia de celda a un número, texto o valor lógico.

+ lookup_array (obligatorio) : el rango de celdas que se buscarán.

+ match_type (opcional) : Número -1, 0 o 1. El argumento del tipo de coincidencia especifica cómo Excel hace coincidir el valor de búsqueda con los valores de la matriz de búsqueda. El valor predeterminado para este argumento es 1.

Por ejemplo : cuando necesitamos saber en qué posición está CLOTH, usamos la función COINCIDIR . Al ingresar la fórmula = COINCIDIR (FF2 & "*", "B2: B7", 0) (1), el resultado es 3 (2).

3. Función INDIRECTA

Uso : devuelve una referencia de celda o rango especificado por una cadena de texto. Utilice la función INDIRECTO cuando desee cambiar la referencia a una celda en una fórmula sin cambiar la fórmula en sí.

Fórmula : = INDIRECTO (ref_text, [a1])

En el cual :

+ Ref_text (obligatorio) : referencia a una celda que contiene el estilo de referencia A1, el estilo de referencia R1C1 o una referencia a la celda como una cadena de texto.

+ Si el texto de referencia no es una referencia de celda válida, INDIRECTO devuelve el valor de error # ¡REF! .

+ Si ref_text hace referencia a otro libro de trabajo (una referencia externa), ese libro de trabajo debe estar abierto. Si el libro de trabajo de origen no está abierto, INDIRECTO devuelve el valor de error # ¡REF! .

 

 

Por ejemplo : Cuando solo necesitas calcular la comisión y quieres arrastrar, soltar o mover el cuadro de resultados a otra posición sin cambiar la fórmula del dinero, usamos INDIRECTO . Al ingresar la fórmula

= INDIRECTO (“B2”, VERDADERO) * INDIRECTO (“C2”, VERDADERO) (1) dará el resultado 700 * 100 = 70000 (2).

4. Función OFFSET

Uso : devuelve la referencia a un rango mediante un número especificado de filas y un número especificado de columnas de una celda o rango de celdas. La referencia devuelta puede ser una sola celda o un rango de celdas. Puede especificar el número de filas y columnas para devolver.

Fórmula : = DESPLAZAMIENTO (referencia, filas, columnas, [alto], [ancho])

En el cual :

+ Referencia (obligatorio) : el área de referencia en la que desea basar la distancia de referencia. El rango de referencia debe referirse a una celda o un rango de celdas adyacentes; de lo contrario, OFFSET devuelve el valor de error # ¡VALOR! .

+ Filas (obligatorio) : el número de filas, hacia arriba o hacia abajo, a las que desea que se refiera la celda superior izquierda. Las filas pueden ser positivas (lo que significa debajo de la referencia inicial) o negativas (lo que significa por encima de la referencia inicial).

+ Cols (obligatorio) : número de columnas, a la izquierda o derecha, a las que desea que se refiera la celda de la esquina superior izquierda del resultado. Las columnas pueden ser positivas o negativas.

+ Alto (opcional) : El alto, en número de filas, que desea tener para la referencia devuelta. La altura debe ser un número positivo.

+ Ancho (opcional) : El ancho, en el número de columnas, que desea tener para la referencia devuelta. El ancho debe ser un número positivo.

Ejemplo : usamos OFFSET para localizar valores y luego usamos la función SUM para calcular la suma. La celda a la que nos referimos es A1; en comparación con A1, las Filas del rango de referencia están por debajo de 1 celda, por lo que Filas es 1; en comparación con A1, Cols de la izquierda es 1 celda, por lo que Cols es 1; Altura tomamos 3 líneas; Ancho obtenemos 2 columnas. Entonces, el resultado al sumar todos los valores en la región es 2025.

5. Función TRANSPONER

Uso : convierte un rango horizontal de celdas en un rango vertical y viceversa, es decir, convierte filas en columnas y columnas en filas.

Fórmula : = TRANSPONER (matriz)

Haciendo

+ Paso 1 : Seleccione celdas en blanco.

+ Paso 2 : Ingrese = TRANSPONER (B2: C4).

+ Paso 3 : Ingrese el rango de celdas originales.

+ Paso 4 : Finalmente, presione CTRL + MAYÚS + ENTRAR.

Ejemplo : cuando necesitamos convertir toda la lista de precios del producto de vertical a horizontal, usamos la función TRANSPONER con la matriz de valores B2 a C4. Al ingresar la fórmula = TRANSPONER (B2: C4) (1) dará el resultado como se muestra (2).

6. Función HIPERVÍNCULO

Usos : crea un hipervínculo a un documento almacenado en la intranet o en Internet.

Fórmula : = HIPERVÍNCULO (ubicación_vínculo, [nombre_ descriptivo]).

En el cual :

Link_location puede hacer referencia a una ubicación en un documento, como una celda específica o un rango con nombre en una hoja de trabajo o en un libro de Excel, o un marcador en un documento de Microsoft Word.

La ruta puede ser a un archivo almacenado en el disco duro. La ruta también puede ser una ruta de convención de nomenclatura universal (UNC) en el servidor (en Microsoft Excel para Windows).

 

 

 

 

Ejemplo : Los enlaces que son largos y confusos resultarán molestos para el usuario. Por lo tanto, utilice HYPERLINK para facilitar el uso de la interfaz. Si ingresa la fórmula = HIPERVÍNCULO (A1, “ENLACE GOOGLE”) (1) dará como resultado ENLACE GOOGLE (2).

El artículo anterior guió VLOOKUP y funciones relacionadas. Espero que el artículo anterior te sea útil.