Unidad 7 - Funciones de Búsqueda y Referencia Ejercicios

Funciones de Bsqueda yReferencia

Estructura de contenidos:Funciones de Bsqueda y Referencias:

1. Funcin BUSCAR

2. Funcin CONSULTAV (BUSCARV)

3. Funcin CONSULTAH (BUSCARH)

4. Funcin INDICE

Estimado alumno:En esta sesin de aprendizaje te explicar sobre las tcnicas eficientes para buscar valores en un rango de datos. En MS Excel las funciones que permiten ubicar, encontrar y mostrar datos son: BUSCAR, CONSULTAV, CONSULTAH e INDICE.

Importante:Los detalles de los temas tratados en esta sesin se encuentran en la carpeta

Material Complementario. Asimismo los archivos a usar en toda esta sesin debes descargarlos desde el campus virtual hacia tu PC.

FUNCIONES DE LOCALIZACIN1. FUNCIN BUSCAR:Esta funcin de bsqueda tiene dos formas de sintaxis: matriz y vector.

Explicaremos cada una de ellas con los siguientes ejercicios.

Ejercicio:Se desea localizar el precio unitario de un artculo en la matriz Lista de Precios, usando el cdigo del mismo del artculo como dato buscado. Este ejercicio lo desarrollaremos por ambos mtodos para que t puedas comparar y elegir el que ms conveniente.

A. Por Matriz:La bsqueda se realiza en un rango de varias filas y varias columnas.Sintaxis:

= BUSCAR (valor_buscado, matrz)Dnde:

Valor_buscado: Dato a buscar para encontrar el resultado esperado.

Matriz: Rango de celdas que contiene en la primera fila o columna los valores buscados y en la ltima fila o columna los resultados a reportar.

Por lo tanto, en nuestra aplicacin tendramos lo siguiente:Debes tener en cuenta que la MATRIZ contiene

-En la primera columna o fila, los valores referencial a buscar. Ej.: CODIGO-En la ltima columna ofila, los posibles valores a reportar. Ej.: PRECIO UNITARIOSolucin:

1. Selecciona la celda C2, ya que es aqu donde se obtendr el resultado de la bsqueda.

2. Utiliza cualquier mtodo aprendido para insertar la funcin solicitada.

Ej.: En la ficha Frmulas, abre el botn Bsqueda y referencia, y luego elige la funcin BUSCAR.3. A continuacin, en la ventana Seleccionar argumentos, haz clic en la opcin: valor_buscado, matriz.

4. Finalmente, ingresa los argumentos de la funcin:

Observacin: En la matriz, se han seleccionado tambin los nombres de los artculos; ya que estn en el rango necesario para seleccionar el

precio unitario.Donde: C1: celda que tiene el valor a buscar (cdigo del artculo)

A6:C13: rango de celdas o matriz (A6:C13) aqu se buscar el Cdigo del articulo y se obtendr el precio del artculo en base a ese cdigo.

Si lo deseas est frmula tambin puedes escribirla:

= BUSCAR (C1, A6:C13)5. Comprueba la bsqueda: Ingresa un cdigo de artculo (en la celda

C1) y pulsa Enter. Se obtuvo el precio unitario de dicho artculo (en la celda C2).

Observa cmo a partir de un dato referencial (CODIGO), obtienes la informacin (PRECIO UNITARIO) desde un cuadro de datos. FACIL VERDAD, claro que s. SIGAMOS con otra

tcnica de bsqueda.B. Por VECTOR:La bsqueda se realiza en un rango de una columna y varias filas o varias columnas y una fila. Usaremos el mismo ejercicio.

Sintaxis:

=BUSCAR (Valor_buscado, Vector_de_comparacin, Vector_resultado)Dnde:

Valor buscado: dato referencial a buscar, para obtener el resultado esperado.

Vector de comparacin: Rango de celdas donde solo se buscarn los posibles valores buscados.

Vector resultado: rango de celdas donde se encuentran los posibles resultados a obtener.

Solucin:

1. Selecciona la celda C22. En la ficha Frmulas, abre el botn Bsqueda y referencia, luego ingresa a la funcin BUSCAR.3. En la ventana Seleccionar argumentos, haz clic en la opcin: valor_buscado, vector de comparacin,vector resultado.


OBSERVA: No hemos seleccionado el rango de los nombres de los artculos; ya que estamos usando vectores para buscar, y ese rango no es necesario.

Dnde: C1: celda que tiene el valor a buscar (cdigo del artculo)

A6:A13: rango de celdas donde se buscar slo el cdigo del artculo.

C6:C13: rango de celdas donde solo se encontrarn a los precios del artculo que se desea reportar.

Si deseas est frmula tambin puedes escribirla:

= BUSCAR (C1, A6:A13, C6:C13)5. Comprueba la bsqueda: Ingresa un cdigo de artculo (en la celda C1) y pulsa Enter. Se obtuvo el precio unitario de dicho artculo (en la celda C2).

Importante:El objetivo de bsqueda es el mismo que el mtodo anterior, pero aqu tanto el rango de valores a buscar estn separados del rango donde estn los resultados a

obtener. TAMBIEN ES FACIL, pero. SIGAMOS con otra tcnica de bsqueda.

2. FUNCIN CONSULTAV (BUSCARV):Esta funcin se usa para buscar un valor (dato) especfico en forma vertical en una tabla de datos. Esta funcin hace la bsqueda en la primera columna de una matriz y encuentra el resultado en la misma fila de una columna especfica de la matriz de bsqueda.

Sintaxis:

=CONSULTAV (Valor_buscado, Matriz_buscar_en, Indicador_columnas, Ordenado)Donde:

Valor_buscado: dato a buscar para encontrar el resultado.

Matriz_buscar_en: Rango de celdas donde se har la busqueda del valor y se encontrar el resultado a obtener.

Indicador de columna: Nmero de columna en la matriz del resultado a obtener.

Ordenado: Puede usar Verdadero (Coincidencia aproximada del dato a buscar) o Falso (Coincidencia exacta del dato a buscar).

Ejercicio:Se desea asignar el NIVEL a cada alumno a apartir del puntaje obtenido en un examen de clasificacin en IDIOMAS. Para ello se tiene una tabla de valoracin de puntajes, cada nivel debe ser reportado luego de buscarse el puntaje obtenido por el alumno.

Solucin:

Usaremos la funcin CONSULTAV (BuscarV), ya que el valor referencial

(Puntajes) estan distribuidos en la tabla de valoracin en forma vertical.

1. Asigna el nombre Tabla_valora al rango A4:B9. (si lo deseas puedes dar este paso)

2. Selecciona la celda F5 e inserta la funcin de bsqueda

CONSULTAV:

3. Selecciona la ficha Frmulas, el botn Bsqueda y referencia, y haz clic en CONSULTAV4. En la ventana que muestra, debes llenar los argumentos:

Donde: Valor_buscado: Se indica la celda E5 (puntaje del alumno) como valor a buscar en el Rango A4:B9. La bsqueda lo hace en la primera columna de la matriz.

Matriz_buscar_en: Escriba el nombre Tabla_valora que corresponde a la matriz donde se busca un valor y se obtendr el resultado. De lo contrario puedes seleccionar el rango.

Indicador_columnas: Escribe 2, ya que es el nmero de columna que corresponde a la ubicacin del nivel a encontrar como resultado.

Ordenado: Como los datos de la primera columna de la matriz Tabla_valora estn ordenados (ver tabla de valoracin), entonces este casillero se deja en blanco o se escribe VERDADERO para que haga la bsqueda por aproximacin.

5. Del procedimiento anterior se obtiene la frmula

=CONSULTAV(E5,Tabla_valora,2); luego est frmula debes copiar para las dems celdas del rango F5:F14.

6. El resultado ser el siguiente:

Importante:Observa tambin el objetivo de buscar un valor referencial para obtener un resultado, se cumple. Pero a diferencia de los mtodos anteriores, la matriz de

bsqueda define la localizacin del resultado con un valor que indica el N DE

COLUMNA donde ste se encuentra. AHORA veremos otra tcnica de bsqueda.

3. FUNCIN CONSULTAH (BUSCARH):Esta funcin se usa para buscar un valor (dato) especfico en forma horizontal en una tabla de datos. Esta funcin hace la bsqueda en la primera columna de una matriz y encuentra el resultado en la misma fila de una columna especfica de la matriz de bsqueda.

Sintaxis:

=CONSULTAH (Valor_buscado, Matriz_buscar_en, Indicador_filas, Ordenado)Ejercicio:Se desea asignar el SUELDO BASICO y ASIGNACION a cada empleado en base a su CATEGORIA.

Solucin:

Usaremos la funcin CONSULTAH, ya que el valor referencial (Categoras)

estan distribuidos en la tabla de valoracin en forma horizontal.

A. Calculando el Bsico:1. Asigna el nombre Categorias al rango G2:J5 (si lo deseas).

2. Selecciona la celda C4 e inserta la funcin de bsqueda CONSULTAH. Selecciona la ficha Frmulas, el botn Bsqueda y referencia, y haz clic en CONSULTAH.3. En la ventana que muestra, debes llenar los argumentos de la funcin

CONSULTAH.Donde: Valor_buscado: Se ingresa la celda B4 (Categora del docente) como valor a buscar en la matriz G2:J5. La bsqueda lo hace en la primera fila del rango.

Matriz_buscar_en: escribe el nombre de rango Categoras que corresponde a la matriz donde se busca un valor y encuentra el resultado.

Indicador_filas: Escribe 3 ya que es el que corresponde a la ubicacin de la fila donde estn los sueldos bsicos a encontrar.

Ordenado: Como lo que se busca es la categora del empleado, entonces en este casillero se escribe FALSO para que haga la bsqueda exacta.

4. Haz clic en el botn Aceptar, y habrs obtenido el resultado.

5. Ahora, podrs copiar la formula obtenida para el resto de empleados:

B. Calculando la Asignacin:1. Selecciona la celda D4 e inserta la funcin de bsqueda CONSULTAH

Selecciona la ficha Frmulas, el botn Bsqueda y referencia, y haz clic en CONSULTAH2. En la ventana que muestra como resultado el procedimiento anterior, debes llenar los argumentos de la funcin CONSULTAH.

Donde: Valor_buscado: Se escribe la celda B4 (Categora del docente) como valor a buscar en la matriz G2:J5. La bsqueda lo hace en la primera fila del rango.

Matriz_buscar_en: Escribe el nombre de rango Categoras que corresponde a la matriz donde se busca el valor y encuentra el resultado.

Indicador_filas: Escribe 4 ya que es el nmero de fila

que corresponde a la ubicacin del % Asignacin.

Ordenado: Como lo que se busca es la categora del empleado, entonces en este casillero se escribe FALSO para que haga la bsqueda exacta.

3. Haz clic en el botn Aceptar y obtendrs el resultado.4. Lo que se ha encontrado es el % Asignacin, este valor debes multiplicarlo por el BASICO, para obtener el monto de la asignacin.

Haga clic en la celda donde est la funcin que acabamos de insertar. Ej.: D4 En la barra de frmulas, multiplica el resultado obtenido por

C4 que es el BASICO y pulsa Enter.

5. Copia la formula obtenida para el resto de empleados:

Importante:Observa tambin el objetivo de buscar un valor referencial para obtener un resultado, se cumple. Pero a diferencia de la funcin CONSULTAV, la matriz de bsqueda define la localizacin del resultado con un valor que indica el N DE

FILA donde ste se encuentra. AHORA veremos la ltima tcnica de bsqueda.4. FUNCIN INDICE:Devuelve un valor o la referencia a un valor en una tabla o rango. La funcin INDICE presenta dos formas: matricial y de referencia.

Esta tcnica es la ms sencilla, ya que se basa en localizar un dato en una matriz, a partir de su ubicacin en dicha matriz: N fila y N columna, Aqu no se busca un dato referencial, solo la ubicacin del valor.

Sintaxis: =INDICE (Matriz, Num_fila, Num_col)Dnde:

Matriz: Rango de celdas donde se har la bsqueda del elemento a partir de un N Fila y N Columna determinado.

Num_Fila: N Fila donde est el elemento a encontrar.

Num_Columna: N Columna donde est el elemento a encontrar.

Ejercicio:

Se pide localizar el nombre del alumno de la columna C4:C13, a partir de su posicin en la matriz.

Solucin:

1. Haz clic en la celda F3, ya que es donde se mostrar el resultado.

2. Inserta la funcin INDICE:

Selecciona la Ficha Frmulas y en el grupo Biblioteca de funciones, haz clic en el botn Bsqueda y referencias y elige INDICE.3. Ingresa el argumento a usar:

4. Haz clic en el botn Aceptar5. Ingresa los argumentos solicitados:

6. Haz clic en el botn Aceptar7. Los resultados son:Importante:Puedes darte cuenta que en esta funcin no se busca un valor referencial, sino se localiza la ubicacin del elemento en la matriz de datos.

Bien hemos terminado la unidad de hoy, espero practiques bastante estas tcnicas de bsqueda para lograr destreza sobre ellas. NOS VEMOS.

Ejercicio 01:

PRACTICA GUIADACrear una aplicacin que genere una consulta de bsqueda, donde se ingrese el cdigo de un vendedor para reportar la ciudad en la que trabaja. Use la funcin BUSCAR por vector.

1. Selecciona la celda C172. En la ficha Frmulas, abre el botn Bsqueda y referencia, luego ingresa a la funcin BUSCAR.3. En la ventana Seleccionar argumentos, haz clic en la opcin:

valor_buscado, vector de comparacin, vector resultado.


Dnde: C17: celda que tiene el valor a buscar (cdigo del artculo)

A3:A13: rango de celdas donde se buscar slo el cdigo del artculo.

C3:C13: rango de celdas donde solo se encontrarn a los precios del artculo que se desea reportar.

Si deseas est frmula tambin puedes escribirla:

= BUSCAR (C17, A6:A13, C6:C13)5. Comprueba la bsqueda: Ingresa un cdigo del vendedor (en la celda C17) y pulsa Enter. Se obtuvo el nombre de la ciudad (en la celda C18).

Ejercicio 02:Se tiene la siguiente hoja de clculo donde se debe calcular el sueldo a partir de su cdigo; as como tambin calcular su bonificacin, descuento y sueldo neto de cada trabajador; a partir de su categora.

Procedimiento:

CALCULANDO INFORMACION SOLICITADA EN EL CUADRO DE INFORMACION: A. Calculando el Cargo del empleado segn el Cod_Cargo:1. Haga clic en la celda E102. Haga clic en la ficha Frmulas/elija el Bsqueda y Referencia/Buscar3. Seleccione el argumento: valor_buscado, matriz.

4. Haga clic en el botn Aceptar e ingrese los argumentos solicitados.

B. Calculando el Sueldo segn el Cod_Cargo:1. Haga clic en la celda F102. Inserte la funcin ndice (Ficha Frmulas/grupo Biblioteca de funciones/botn Bsqueda y referencias/INDICE.3. Ingrese los argumentos solicitados:

4. Haga clic en el Aceptar.5. Ingrese los datos solicitados:

C. Calculando la Bonificacin segn la Categora:1. Seleccionar la celda H10 e inserta la funcin de bsqueda CONSULTAH

(Ficha Frmulas, Botn Bsqueda y referencia, Clic CONSULTAH)2. En la ventana que muestra como resultado el procedimiento anterior, se debe llenar los argumentos de la funcin CONSULTAH.

-Valor_buscado Se indica la celda G10 (Categora del empleado) como valor a buscar en el Rango H3:K5. La comparacin lo hace en la primera fila del rango.

-Matriz_buscar_en matriz donde se busca un valor y retorna otro de la misma columna en la fila indicada.

- Indicador_filas Es un nmero que

corresponde a la ubicacin de la columna que devuelve un resultado.

- Ordenado Como los datos de la primera fila

de la matriz NO estn ordenados, entonces en este casillero se indica FALSO para que haga la bsqueda exacta.

D. Calculando el Descuento segn la Categora:1. Seleccionar la celda I10 e inserta la funcin de bsqueda CONSULTAH (Ficha

Frmulas, Botn Bsqueda y referencia, Clic CONSULTAH)2. En la ventana que muestra como resultado el procedimiento anterior, se debe llenar los argumentos de la funcin CONSULTAH.

-Valor_buscado Se indica la celda G10 (Categora del empleado) como valor a buscar en el Rango H3:K5. La comparacin lo hace en la primera fila del rango.

-Matriz_buscar_en matriz donde se busca un valor y retorna otro de la misma columna en la fila indicada.

- Indicador_filas Es un nmero que corresponde

a la ubicacin de la columna que devuelve un resultado.

-Ordenado Como los datos de la primera fila de la matriz NO estn ordenados, entonces en este

casillero se indica FALSO para que haga laE. Calculando el Sueldo Neto:1. Haga clic en la celda I102. Escriba la frmula: =F10+H10-I10

bsqueda exacta.

BUSCANDO DATOS DEL EMPLEADO A PARTIR DE SU CDIGO: A. Para obtener el Empleado:1. Seleccionar la celda D26 e introducir la funcin de bsqueda CONSULTAV(Ficha Frmulas, Botn Bsqueda y referencia, Clic CONSULTAV)2. En la ventana que muestra como resultado el procedimiento anterior, se debe llenar los argumentos de la funcin CONSULTAV.

3. Del procedimiento anterior se obtiene la frmula:

=CONSULTAV(D25,$B$10:$J$19,2,FALSO) B. Para obtener el Cargo:1. Haga clic en la celda D27

2. Introducir la funcin de bsqueda CONSULTAV (Ficha Frmulas, Botn

Bsqueda y referencia, Clic CONSULTAV)3. En la ventana que muestra como resultado el procedimiento anterior, se debe llenar los argumentos de la funcin CONSULTAV.

4. Del procedimiento anterior se obtiene la frmula:

=CONSULTAV(D25,$B$10:$J$19,4,FALSO) C. Para obtener el Sueldo:1. Haga clic en la celda D28



D. Para obtener Categora1. Haga clic en la celda D29



E. Para obtener Bonificacin:1. Haga clic en la celda D302. Introducir la funcin de bsqueda CONSULTAV (Ficha Frmulas, Botn


F. Para obtener Descuento:1. Haga clic en la celda D312. Introducir la funcin de bsqueda CONSULTAV (Ficha Frmulas, Botn


G. Para obtener el Sueldo Neto:1. Haga clic en la celda D322. Introducir la funcin de bsqueda CONSULTAV (Ficha Frmulas, Botn


Comprobar la aplicacin desarrollada: Ahora que las funciones estn ingresadas podemos comprobar la veracidad del proceso que desarrollaste.

Ingresa un nuevo cdigo del alumno y vers como cambia la informacinreferente a ese cdigo de empleado.

AUTOEVALUACION:Ahora demuestra lo aprendido:1. Responde V (Verdadero) o F (Falso), marca con X:V Fa. La funcin CONSULTAV, requiere una matriz de bsqueda ( ) ( ) b. La funcin INDICE, se aplica tambin a vectores de bsqueda ( ) ( ) c. La funcin BUSCAR, usa dato referencial de bsqueda ( ) ( )

2. Tiene como argumento la localizacin de la posicin en la matriz de bsqueda:a. BUSCAR-VECTOR b. BUSCAR-MATRIZ c. INDICE

d. CONSULTAV

e. CONSULTAH

3. La bsqueda ordenada de datos, se aplica cuando:a. La matriz de bsqueda, tiene ordenados todos sus datos en orden alfabtico

b. La matriz de bsqueda contiene su primera columna ordenada en rangos numricos.

c. Las 2 anteriores son ciertas. d. NA

BibliografaLas Funciones de Excel. (30 de Marzo de 2012). Obtenido de http://www.funcionesexcel.comAulaclic. (30 de 03 de 2012). Curso de informatica gratuito. Obtenido de http://www.aulaclic.es/excel2010/index.htm

!

!

!

!

!

Documents

Unidad 7 - Funciones de Búsqueda y Referencia Ejercicios