Semana 07
FUNCIONES DE BÚSQUED
Unidad de aprendizaje 2:
Al finalizar la unidad, el estudiante utiliza funciones matemáticas, estadísticas, de búsqueda y de texto para proces
ÚSQUEDA
y de texto para procesar información dentro de hojas de cálculo
Logro
Al finalizar la sesión, el estudiante aplica las funciones de búsqueda y extra
buscarv y buscarh a través de Microsoft Excel, para recuperar datos de un
de búsqueda y extracción de datos
cuperar datos de una tabla
Utilidad
¿Por qué será útil buscar y extraer datos de los registros?
Contenido de la sesión
1. Si error
2. Funciones de búsqueda para datos tipo texto
3. Funciones de búsqueda para datos tipo número
4. Funciones de búsqueda para datos tipo fecha
5. Función BuscarX
6. Evaluación
FUNCIONES DE BUSQUEDA PARA D
DA PARA DATO TIPO TEXTO
BuscarV
Busca un valor especifico en la primera columna de una matriz y devuelve el valor de una columna especi
Sintaxis: buscarV(Valor buscado; Matrix de comparación; Indicador de columnas
Descripción e los argumentos
Valor buscado. Es el valor que se busca en la primera columna de la matriz de comparación
Matriz de comparación. Es un rango de celdas
Indicador de columnas. Es el número de columna de la matriz de comparación que contiene el dato a dev
Ordenado. Es un valor lógico que indica si la busqueda será exacta o aproximada.
Si escribe: VERDADERO, la búsqueda será aproxomada
Si escribe: FALSO, la búsqueda será exacta
Si no encuentra ningún valor, devuelve el valor de error #N/A
alor de una columna especificada.
ador de columnas; ordenado)
comparación
que contiene el dato a devolver
TABLA PAGOS Escriba las fórmulas para calcular el sueldo básico y el descuento
Categ Sueldo AFP
A 5,000 10.00% Matriz buscar en
B 5,500 11.00%
C 6,000 10.00%
D 9,000 11.00%
E 12,000 12.00% Permite hacer búsquedas exactas
1 2 3
COD NOM TIPO CATEG Sueldo AFP
C001 ANA TELLO PAZESTABLE B 5500 11.00% =VLOOKUP(E11,$B$3:$D$7,2,FALSE())
C002 MARIA CONTRATADO B
5500 11.00% =VLOOKUP(E11,$B$3:$D$7,3,FALSE())
C003 JUAN LOPEZ CADENAS TEMPORAL C
6000 10.00%
C004 JOSE ESTABLE A
5000 10.00%
C005 CARLOS CONTRATADO D
9000 11.00%
C006 MARTIN TEMPORAL C
6000 10.00%
C007 ROSA ESTABLE A
5000 10.00%
C008 PATTY ESTABLE D
9000 11.00%
C009 KETTY TEMPORAL C
6000 10.00%
C010 CARMEN CONTRATADO B
5500 11.00%
C011 LUIS ESTABLE B
5500 11.00%
C012 ANDRES CONTRATADO B
5500 11.00%
C013 CESAR TEMPORAL B
5500 11.00%
C014 EYTEL ESTABLE E
12000 12.00%
C015 GINO CONTRATADO A 5000 10.00%
C016 VICTOR CONTRATADO C 6000 10.00%
C017 PAULA ESTABLE E 12000 12.00%
C018 GISELLA TEMPORAL A 5000 10.00%
C019 GERTRUDIS CONTRATADO E 12000 12.00%
C020 MARTHA CONTRATADO B 5500 11.00%
Lista de precios, descuento Tabla de fletes Calcular precio, según la lista de precios, descuento
Categoría Precio Desc del total PAÍS ORIGEN %Flete Total = Precio * Cantidad
A 500.00 100.00 Japón 2% Flete depende del país origen, utilice la tabla de fletes
B 600.00 80.00 China 15% Calcular descuento, según la lista de precios, descuento
C 750.00 60.00 Alemania 4% Venta = Total + Flete - descuento
D 900.00 50.00 Holanda 6%
1 2 3 Korea 1%
COD EQUIPO PAÍS ORIGEN CAT CANTIDAD PRECIO TOTAL FLETE DESCUENTO VENTA
X001 Sony LBT-XGRO Japón A 120 500 60000 1200 100 61100 =VLOOKUP(D10,$A$3:$C$6,2,FALSE())
X002 AIWA NSX-60 China A 60 500 30000 4500 100 34400 =VLOOKUP(C10,$G$3:$H$7,2,FALSE())*G10
X003 PIONEER XA-790 Alemania B 80 600 48000 1920 80 49840 =VLOOKUP(D10,$A$3:$C$6,3,FALSE())
X004 AIWA NSX-R30 China B 40 600 24000 3600 80 27520
X005 PHILIPS MC-110 Holanda A 100 500 50000 3000 100 52900
X006 LG F-363 Korea A 150 500 75000 750 100 75650
X007 PHILIPS FWC505 Holanda A 140 500 70000 4200 100 74100
X008 SONY MHC-RG66T Japón B 160 600 96000 1920 80 97840
X009 LG F-577 Korea A 90 500 45000 450 100 45350
X010 PIONEER XA-390 Alemania B 85 600 51000 2040 80 52960
X011 Sony LBT-XGRO Japón C 100 750 75000 1500 60 76440
X012 AIWA NSX-60 China A 95 500 47500 7125 100 54525
X013 PIONEER XA-790 Alemania B 85 600 51000 2040 80 52960
X014 AIWA NSX-R30 China A 120 500 60000 9000 100 68900
X015 PHILIPS MC-110 Holanda D 150 900 135000 8100 50 143050
X016 LG F-363 Korea A 125 500 62500 625 100 63025
X017 PHILIPS FWC505 Holanda A 130 500 65000 3900 100 68800
X018 SONY MHC-RG66T Japón B 120 600 72000 1440 80 73360
X019 LG F-577 Korea A 180 500 90000 900 100 90800
X020 PIONEER XA-390 Alemania B 120 600 72000 2880 80 74800
FUNCIONES DE BUSQUEDA PARA
TIPO fecha
A PARA DATO
DF1
Calcular bonificación según la FECHA de INGRESO de la persona
Si INGRESO Bonificación
antes 1980 900.00
Entre 1980 y 1990 700.00
Después 1990 y antes del 2001 500.00
Después del 2000 450.00
Caso contrario mostrará 0.00
NOMBRES FECHA INGRESO CATEGORIA BASICO
JUAN PEREZ
24/Mar/2000 C 5,000.00
JOHN VARGAS 24/Dec/1995 B 7,000.00
MARY SMITH 18/Oct/2000 C 5,000.00
CHRISTIAN VALDIVIA 10/Jul/1999 A 9,000.00
LADY MACHUCA 10/Feb/2001 B 7,000.00
JHONNY PAREDES 01/May/1982 D 11,000.00
GISSELLA CASAS 25/Apr/1987 A 9,000.00
ISABEL CASSANOVA 08/Aug/1965 C 5,000.00
CARLOS ACOSTA 29/Sep/1966 B 7,000.00
Página 15
DF1
TABLA DE DATOS
Fecha ingreso Bonif
1/1/1950 900 Busca fecha ingreso
1/1/1980 700 y extrae bonificación
1/1/1991 500
1/1/2001 450
1 2
Matriz buscar en
BONI1
500 =VLOOKUP(C14,$F$5:$G$8,2,TRUE())
500
500
500
450
700
700
900
900
Página 16
DF2
Calcular bonificación según la FECHA de INGRESO de la persona
Si INGRESO Mostrará
antes 1970 0.20
Entre 1970 y 1995 0.15
Después 1995 y antes del 2001 0.12
Después del 2000 0.10
Caso contrario mostrará 0.00
NOMBRES FECHA INGRESO CATEGORIA BÁSICO
JUAN PEREZ
24/Mar/2000 C 5,000.00
JOHN VARGAS 24/Dec/1995 B 7,000.00
MARY SMITH 18/Oct/2000 C 5,000.00
CHRISTIAN VALDIVIA 10/Jul/1999 A 9,000.00
LADY MACHUCA 10/Feb/2001 B 7,000.00
JHONNY PAREDES 01/May/1982 D 11,000.00
GISSELLA CASAS 25/Apr/1987 A 9,000.00
ISABEL CASSANOVA 08/Aug/1965 C 5,000.00
CARLOS ACOSTA 29/Sep/1966 B 7,000.00
Página 17
DF2
TABLA DE DATOS
Fecha ingreso %Bonif
1/1/1920 20% Busca fecha ingreso
1/1/1970 15% y extrae %bonificación y lo
1/1/1996 12% multiplica por básico
1/1/2001 10%
1 2
BONI1
600
1050
600
1080
700
1650
1350
1000
1400
Página 18
Calcular bonificación según la FECHA de INGRESO de la persona
Si INGRESO Mostrará
antes 1960 0.20
Entre 1960 y 1998 0.15
Después 1998 y antes del 2001 0.12
Después del 2000 0.10
Caso contrario mostrará 0.00
NOMBRES FECHA INGRESO CATEGORIA BÁSICO
JUAN PEREZ
24/Mar/2000 C 5,000.00
JOHN VARGAS 24/Dec/1995 B 7,000.00
MARY SMITH 18/Oct/2000 C 5,000.00
CHRISTIAN VALDIVIA 10/Jul/1999 A 9,000.00
LADY MACHUCA 10/Feb/2001 B 7,000.00
JHONNY PAREDES 01/May/1982 D 11,000.00
GISSELLA CASAS 25/Apr/1987 A 9,000.00
ISABEL CASSANOVA 08/Aug/1965 C 5,000.00
CARLOS ACOSTA 29/Sep/1966 B 7,000.00
TABLA DE DATOS
Fecha ingreso %Bonif
1/1/1910 20% Busca fecha ingreso
1/1/1960 15% y extrae %bonificación y lo
1/1/1999 12% multiplica por básico
1/1/2001 10%
1 2
BONI1
600 =VLOOKUP(C13,F5:G8,2,TRUE())*E13
1050
600
#N/A
14000
0
0
0
0
FUNCIONES DE BUSQUEDA P
DATO TIPO número
QUEDA PARA
úmero
N1
BONI1
La bonificació1, se determinará según la siguiente tabla
Si su BASICO es Mostrará
0 a 1000 900.00
1001 a 3000 700.00
3001 a 5000 650.00
5001 a más 550.00
Caso contrario mostrará 0.00
NOMBRES CATEGORIA BASICO
JUAN PEREZ
C 5,317.00
JOHN VARGAS
B 2,815.00
MARY SMITH
C 7,986.00
CHRISTIAN VALDIVIA
A 7,748.00
LADY MACHUCA
B 3,039.00
JHONNY PAREDES
D 8,759.00
GISSELLA CASAS
A 5,034.00
ISABEL CASSANOVA
C 8,425.00
CARLOS ACOSTA
B 8,691.00
Página 23
N1
TABLA DE DATOS
Básico Bonif
0 900
1001 700
3002 650
5002 550
1 2
BONI1
550
700
550
550
650
550
550
550
550
Página 24
N1
Página 25
N1
Qué hace la fórmula?
La fórmula busca la
categoría y extrae la
bonificación
Página 26
BuscarH
Busca un valor especifico en la primera fila de una matriz y devuelve el valor de una fila especificada.
Sintaxis: buscarH(Valor buscado; Matrix de comparación; Indicador de fila
Descripción e los argumentos
Valor buscado. Es el valor que se busca en la primera fila de la matriz de comparación
Matriz de comparación. Es un rango de celdas
Indicador de fila. Es el número de fila de la matriz de comparación que contiene el dato a devolver
Ordenado. Es un valor lógico que indica si la busqueda será exacta o aproximada.
Si escribe: VERDADERO, la búsqueda será aproxomada
Si escribe: FALSO, la búsqueda será exacta
Si no encuentra ningún valor, devuelve el valor de error #N/A
e una fila especificada.
ador de fila; ordenado)
e el dato a devolver
Extraer precio y descripción según código de producto
Tabla Fuente ( La tabla debe contener en su primera fila los datos a buscar)
1 Código C D E
2 Descripción Lapicero Fichero Portaminas
3 Precio S/. 10.20 S/. 19.30 S/. 15.50
Dato a buscar
Descripción
Nro Código Precio
Relacionada
1 C 10.20 Lapicero
2 D 19.30 Fichero
3 A 18.50 Papel Bond
4 C 10.20 Lapicero
5 B 8.40 Borrador
6 E 15.50 Portaminas
7 E 15.50 Portaminas
8 E 15.50 Portaminas
9 E 15.50 Portaminas
10 B 8.40 Borrador
datos a buscar)
A B
Papel Bond Borrador
S/. 18.50 S/. 8.40
Utilce: =BuscarH(Dato a buscar,Matriz buscar en,Indicador de fila, Verdadero/FALSO)
=HLOOKUP(C10,$B$4:$G$6,2,FALSE())
=HLOOKUP(C10,$B$4:$G$6,3,FALSE())
Verdadero/FALSO)
Evaluación
Calcular bonificación según la FECHA de INGRESO de la persona
Si INGRESO Mostrará TABLA DE DATOS
antes 1990 0.20 Fecha ingreso
Entre 1990 y 1995 0.15 1/1/1950
Después 1995 y antes del 2001 0.12 1/1/1990
Después del 2000 0.10 1/1/1996
Caso contrario mostrará 0.00 1/1/2001
1
NOMBRES FECHA INGRESOCATEGORIA BÁSICO BONI1
JUAN PEREZ
24/Mar/2000 C 5,000.00 600
JOHN VARGAS 24/Dec/1995 B 7,000.00 1050
MARY SMITH 18/Oct/2000 C 5,000.00 600
CHRISTIAN VALDIVIA 10/Jul/1999 A 9,000.00 1080
LADY MACHUCA 10/Feb/2001 B 7,000.00 700
JHONNY PAREDES 01/May/1982 D 11,000.00 2200
GISSELLA CASAS 25/Apr/1987 A 9,000.00 1800
ISABEL CASSANOVA 08/Aug/1965 C 5,000.00 1000
CARLOS ACOSTA 29/Sep/1966 B 7,000.00 1400
BLA DE DATOS
%Bonif
20% Busca fecha ingreso
15% y extrae %bonificación y lo
12% multiplica por básico
10%
2
=VLOOKUP(C13,$F$5:$G$8,2,TRUE())*E13
Calcular bonificación según la FECHA de INGRESO de la persona
Si INGRESO Mostrará
antes 1950 0.20
Entre 1950 y 1999 0.15
Después 1999 y antes del 2001 0.12
Después del 2000 0.10
Caso contrario mostrará 0.00
NOMBRES FECHA INGRESO CATEGORIA BÁSICO
JUAN PEREZ
24/Mar/2000 C 5,000.00
JOHN VARGAS 24/Dec/1995 B 7,000.00
MARY SMITH 18/Oct/2000 C 5,000.00
CHRISTIAN VALDIVIA 10/Jul/1999 A 9,000.00
LADY MACHUCA 10/Feb/2001 B 7,000.00
JHONNY PAREDES 01/May/1982 D 11,000.00
GISSELLA CASAS 25/Apr/1987 A 9,000.00
ISABEL CASSANOVA 08/Aug/1965 C 5,000.00
CARLOS ACOSTA 29/Sep/1966 B 7,000.00
* Las Expresiones de Comparaciones de tipo FECHA se ponen de la sgte. manera FECHA(año,mes,día)
* Las Expresiones de Comparaciones con caracteres (letras) siempre van entre comillas.
* Cuando se muestra valores numéricos nunca llevan comillas
TABLA DE DATOS
Fecha ingreso %Bonif
12/31/1899 20% Busca fecha ingreso
1/1/1950 15% y extrae %bonificación y lo
1/1/2000 12% multiplica por básico
1/1/2001 10%
1 2
BONI1
600 =VLOOKUP(C13,$F$5:$G$8,2,TRUE())*E13
1050
600
1350
700
1650
1350
750
1050
manera FECHA(año,mes,día)
entre comillas.
Evaluacion3
BONI1
Se determinará según el BASICO de la persona
Si su BASICO es Mostrará
0 a 1000 20%
1001 a 3000 15%
3001 a 5000 12%
5001 a más 10%
Caso contrario mostrará 0.00
NOMBRES CATEGORIA BASICO BONI1
JUAN PEREZ
C 5,317.00 10%
JOHN VARGAS B 2,815.00 15%
MARY SMITH C 7,986.00 10%
CHRISTIAN VALDIVIA A 7,748.00 10%
LADY MACHUCA B 3,039.00 12%
JHONNY PAREDES D 8,759.00 10%
GISSELLA CASAS A 5,034.00 10%
ISABEL CASSANOVA C 8,425.00 10%
CARLOS ACOSTA B 8,691.00 10%
* Las Expresiones de Comparaciones con caracteres (letras) siempre van entre comillas.
* Cuando se muestra valores numéricos nunca llevan comillas
Página 37
Evaluacion3
Tabla de Búsqueda
Básico %Bonif
0 20% Busca basico y extrae
1001 15% % de bonificación
3002 12%
5002 10%
1 2
van entre comillas.
Página 38
Las funciones [Link] y ESERROR
1. La función [Link]: utilidad y argumentos
2. La función ESERROR: utilidad y argumentos
1. La función [Link] comprueba si existe error en alguna celda o en una expresión (fórmula o función). Si lo hay,
introducir en su 2º argumento.
Error
8 8
=[Link](D11;"Existen err
Expresión que queremos evaluar si hay
error
CÓDIGO NOMBRE ARTÍCULO
A000 Monitor Philips 17 "
A001 Monitor AOC 17 "
A005 Monitor OKI 17 "
B001 Monitor LG 17 "
B002 Monitor SAMSUNG 17 " =[Link](BUSCARV(G24
Expresión que queremos evaluar si hay
error
2. La función ESERROR comprueba si existe error en alguna celda o en una expresión (fórmula o función) y si lo hay
FALSO. Con ESERROR anidada en una función SI, ampliamos las posibilidades a 2 posibles resultados, a diferencia d
Error
5 No hay errores
=SI(ESERROR(D36);"Existen errores y
Expresión que queremos evaluar si hay error y q
utilizado por la función SI
CÓDIGO NOMBRE ARTÍCULO
A000 Monitor Philips 17 "
A001 Monitor AOC 17 "
A005 Monitor OKI 17 "
B001 Monitor LG 17 "
B002 Monitor SAMSUNG 17 " =[Link](BUSCARV(G24
RROR
expresión (fórmula o función). Si lo hay, nos devuelve una expresión o valor que podemos
=[Link](D11;"Existen errores que deben ser depurados")
n que queremos evaluar si hay Expresión que queremos obtener si hay
error
Buscar código:
bbb El codigo bbb no existe en mi lista
=[Link](BUSCARV(G24;D22:E26;2;FALSO);"Cód. erróneo o falta introducir")
n que queremos evaluar si hay Expresión que queremos obtener si hay
error
expresión (fórmula o función) y si lo hay, devuelve VERDADERO. Si no lo hay, devuelve
es a 2 posibles resultados, a diferencia de la función SI. ERROR.
rrores
=SI(ESERROR(D36);"Existen errores y deben ser depurados";"No hay errores")
n que queremos evaluar si hay error y que devolverá VERDADERO o FALSO para que sea
por la función SI
Buscar código:
A001 Monitor AOC 17 "
=[Link](BUSCARV(G24;D22:E26;2;FALSO);"Cód. erróneo o falta introducir")
=IF(ISERROR(VLOOKUP(G50,D48:E52,2,FALSE())),"El codigo no existe",VLOOKUP(G50,D48:E52,2,FALSE()))
D48:E52,2,FALSE()))
