Unlimited WordPress themes, graphics, videos & courses! Unlimited asset downloads! From $16.50/m
Advertisement
  1. Computer Skills
  2. Office

Cómo extraer datos de una Hoja de Cálculo usando VLOOKUP, MATCH e INDEX

by
Difficulty:IntermediateLength:LongLanguages:
This post is part of a series called Spreadsheets for Finance.
Spreadsheets for Finance: Calculating Internal Rate of Return
Getting Started With Numbers on OS X

Spanish (Español) translation by Rodney Martinez (you can also view the original English article)

Cuando necesites encontrar y extraer una columna de datos de una tabla y colocarla en otra, usa la función VLOOKUP. Esta función está presente en cualquier versión de Excel en Windows y Mac, así como en Google Sheets. Te permite encontrar datos en una tabla usando algún tipo de identificador que tenga en común con otra. Las dos tablas pueden estar en diferentes hoja o incluso en diferentes libros. Hay también una función HLOOKUP, que hace lo mismo pero con datos distribuídos de forma horizontal, en filas.

Las funciones MATCH e INDEX son buenas para usar cuando usted se está conectando con la ubicación de un dato específico tales como la columna o la fila que contiene el nombre de una persona.

Opciones Premium

Antes de que comencemos a examinar las funciones de Excel, ¿sabía que Envato Market tiene una variedad de scripts y plugins para Excel que le permiten realizar funciones avanzadas?

Por ejemplo, usted puede:

Excel scripts and plugins on Envato Market
Excel, scripts y plugins en Envato Market

Presentación digital

Si quiere seguir todo el tutorial usando su propio archivo Excel, entonces hágalo. O si lo prefiere, descargue el archivo zip incluido para este tutorial, el cual contiene una muestra de un libro de trabajo llamado vlookup example.xlsx.

Usando VLOOKUP

Cuando VLOOKUP encuentra el identificador que usted especifica en la fuente de datos, entonces puede encontrar cualquier celda  en esa fila y devolverle la información a usted. Note que en el fuente de dato, el identificador debe estar en la primera columna de la tabla.

unique identifiers for vlookup
Un identificador único debería ser como un número de serie, donde dos no son iguales en la misma tabla.

Sintaxis

La sintaxis de la función VLOOKUP es:

=VLOOKUP(lookup value, table range, column number, [true/false])

Aquí está lo que estos argumentos significan:

  • Valor Lookup. La celda que tiene el único identificador.
  • Rango de la tabla. El rango de las celdas que tiene el identificador en la primera columna, seguido por el resto de los datos en las otras columnas.
  • Número de columnas. El número de la columna que tiene el dato que está buscando. No se confunda con las letras de la columna. En la ilustración de arriba, la condición está en 4 columnas.
  • True/False.  Este argumento es opcional. True significa que la correspondencia aproximada es aceptable, y False significa que una coincidencia exacta es aceptable.

Queremos encontrar la cantidad de ventas desde la tabla en la ilustración arriba, así que usamos tres argumentos:

syntax of vlookup
Sintaxis de la función VLOOKUP

Defina un Nombre de Rango para Crear una Referencia Absoluta

En Vlookup example.xlsx, mire la hoja de calculo Sales Amounts. Introduciremos la fórmula en B5, luego usamos la característica AutoFIll para copiar la fórmula abajo en la hoja. Eso significa que el rango de la tabla en la fórmula tiene que ser una referencia absoluta. Una buena manera de hacer eso es definir el nombre para el rango de la tabla.

Definiendo un Nombre de Rango en Excel

  1. Antes de introducir la fórmula, vaya a la fuente de datos de la hoja de cálculo.
  2. Seleccione todas las celdas, desde la A4 (el encabezado para el Orden # columna) hacia abajo hasta H203. Una manera rápida de hacer esto es pulsando A4, luego presionar Ctrl+Shift+End (Command+Shift+End en la Mac).
  3. Haga clic dentro del Name Box arriba de la columna A (ahora Name Box muestra A4).
  4. Escriba data, después presione Enter.
  5. Ahora puede usar el nombre data en la formula en vez de $A$4:$H$203.
Name box displaying a range name
El name box, por lo general, muestra la dirección de las celdas actuales. Haga clic dentro de él y escriba un nombre para definir un rango.

Definiendo un nombre de rango en Google Sheets.

En Google Sheets, definir un nombre es un poco diferente.

  1. Haga clic en el encabezado de la primer columna de su fuente de datos, luego presione Ctrl+Shift+Flecha derecha (Command+Shift+Flecha derecha en la Mac). Eso seleccione la fila de los encabezados de columnas.
  2. Presione Ctrl-Shift-Flecha abajo (Command-Shift-Flecha abajo en la Mac). Eso seleccione el dato actual.
  3. Haga clic en el menú Data, luego seleccione Named and protected ranges.
  4. En Named and protected ranges a la derecha, escriba data, luego pulse Done.
Data menu in Google Sheets
Definiendo un nombre de rango en Google Sheets.

Introduciendo la Formula

Para introducir la formula, vaya a la hoja de cálculo Sales Amount y haga clic en B5.

Introduzca la formula:

=VLOOKUP(A5,data,8,FALSE)

Presione Enter.

entering the vlookup function
Introduzca la función VLOOKUP

El resultado debería de ser 40. Para rellenar en los valores inferiores de la columna, haga clic de vuelta sobre B5, si es necesario. Ponga el puntero del sobre el punto AutoFill en celda de la esquina inferior derecha, para que el puntero ser convierta en una cruz.

mouse pointer on the autofill dot
Cuando coloque el puntero sobre el punto en la esquina inferior derecha de una celda, ésta se convierte en una cruz AutoFill.

Doble clic para rellenar los valores de la columna.

worksheet with data after using autofill
Doble-clic con la cruz AutoFill para copiar la formula de la columna.

Si lo desea, puede ejecutar la función VLOOKUP en las siguientes columnas para extraer otros campos, como el nombre o el estado.

Usando MATCH

La función MATCH no le devuelve el valor de dato a usted, usted provee el valor que está buscando, y la función devuelve la posición de ese valor. Es como preguntar donde está la Calle Main #135, y conseguir la respuesta; está en el 4to. edificio por la calle.

Sintaxis

La sintaxis de la función MATCH es:

=MATCH(lookup value, table range, [match type])

Los argumentos son:

  • Valor Lookup. La celda que tiene el identificador único.
  • Rango de tabla. El rango de las celdas que está buscando.
  • Tipo de Coincidencia. Opcional. Es cómo usted específica cuán cerca de una coincidencia usted quiere, así como sigue:

Después el valor más alto

-1 

Los valores deben ser en orden descendente.

Valor Target

0

Estos valores pueden ser en cualquier orden.

Los siguientes valores más bajos

1

Tipo por defecto. Los valores deben estar en orden ascendente.

Al igual como con la función VLOOKUP, usted probablemente encontrará que la función MATCH es más fácil de usar si aplica un nombre de rango. Vaya a la Fuente de Datos de la hoja, seleccione desde B4 (la columna encabezado para orden #) hasta el fondo, clic en Name box arriba de la columna A, y llámela order_number. Note que los valores están en orden ascendente.

name box with one column defined
Un nombre de rango puede ser sólo una columna, una fila o incluso; una celda.

Vaya a la pestaña Match de la hoja de cálculo. En B5, introduzca la función MATCH:

=MATCH(A5,order_number,1)

entering the match function
Introduciendo la función MATHC

Si no ha definido un nombre de rango, entonces ha escrito una función así:

=MATCH(A5,'Source Data'!A5:A203,0)

De cualquier manera, puede ver que ésta está en la posición 14 (haciéndola en el orden 13).

completed match function
Resultado de la función MATCH.

Usando INDEX

La función INDEX es lo opuesto a la función MATHC y es similar a la función VLOOKUP. Usted le dice a la función cual es la fila y columna de datos que quiere y le dirá el valor de lo que está en la celda.

Sintaxis

La sintaxis de la función INDEX es:

=INDEX(data range, row number, [column number])

Los argumentos son:

  • Rango Data. Al igual que en las otras dos funciones, esta es la tabla de datos.
  • Número de Filas. El número de filas de los datos, el cual no es necesariamente la fila de la hoja de cálculo. Si el rango de la tabla empieza en la fila 10 de la hoja, entonces esa fila es #1.
  • Número de columna. El número de la columna del rango de datos. Si el rango empieza sobre la columna E, esa columna es #1.

La documentación de Excel le dirá que el argumento; número de columna, es opcional, pero el número de la fila es de tipo opcional, también. Si el rango de la tabla tiene sólo una fila o una columna, entonces no tiene que usar otro argumento.

Vaya a la hoja Index del libro de trabajo y haga clic en C6. Primero, queremos encontrar lo que está contenido en la fila 9, columna 3 de la tabla. En la formula, usaremos el nombre del rango que creamos anteriormente.

Introduzca la formula:

=INDEX(data,A6,B6)

entering the index function
introduciendo la función index

Esta devuelve el último nombre del cliente: Strevell. Cambie los valores de A6 y B6, y el resultado en C6 mostrará diferentes resultados (observe que muchas filas tiene el mismo estado y nombres de productos).

En conclusión

La habilidad de una hoja de cálculo para ver en otra hoja de cálculo y extraer datos es una herramienta magnífica. De esta manera, usted puede tener una hoja que contiene todos los datos que usted necesita para muchos propósitos, luego extraer lo que usted necesita para instancias específicas.

Advertisement
Advertisement
Advertisement
Advertisement
Looking for something to help kick start your next project?
Envato Market has a range of items for sale to help get you started.