Excel: SI y BUSCAR - ocw.uc3m.esocw.uc3m.es/.../practicas-1/practicas-excel/excel2.pdf · 4 Soluci ón: B úsqueda en tablas de datos Parte II.-Búsquedas, tablas y Bases de Datos

1

Excel: Excel: SI y BUSCARSI y BUSCAR

22

¿¿Por quPor quéé??

Parte II.Parte II.-- BBúúsquedas, tablas y Bases de Datossquedas, tablas y Bases de Datos 1.1.-- BBúúsquedas en tablas de datossquedas en tablas de datos

•• La funciLa funcióón SIn SI

•• SI (expres_lSI (expres_lóógica; valor_A; valor_B) = SI(Condicigica; valor_A; valor_B) = SI(Condicióón; valor_si_verdad; n; valor_si_verdad; valor_si_falso)valor_si_falso)

•• Ejemplo: Calcular el porcentaje de retenciEjemplo: Calcular el porcentaje de retencióón a aplicar en funcin a aplicar en funcióón del valor de los n del valor de los ingresos que se encuentran en C9.ingresos que se encuentran en C9.

Recorre las Recorre las condiciones condiciones una a una y una a una y cuando se cuando se pasa, se pasa, se queda con queda con la anteriorla anterior

=SI(=SI(C9<=A3;B2;SI(C9<=A4;B3;SI(C9<=A5;B4;SI(C9<=A6;B5;B6))))C9<=A3;B2;SI(C9<=A4;B3;SI(C9<=A5;B4;SI(C9<=A6;B5;B6))))

33

¿¿Por quPor quéé??


•• ¿¿Cuando no vale? SegCuando no vale? Segúún va aumentando el taman va aumentando el tamañño de la tabla o de la tabla ⇒⇒ se hace impracticablese hace impracticable

=SI(=SI(C10<=A3;B2;SI(C10<=A4;B3;SI(C10<=A5;B4;SI(C10<=A6;B5;SI(C10<=A7;C10<=A3;B2;SI(C10<=A4;B3;SI(C10<=A5;B4;SI(C10<=A6;B5;SI(C10<=A7;B6;B7))))B6;B7))))

44

SoluciSolucióón: Bn: Búúsqueda en tablas de datossqueda en tablas de datos


¿¿y si la tabla tiene 500 filas de datos? y si la tabla tiene 500 filas de datos? ⇒⇒ hay funciones para resolver el problemahay funciones para resolver el problema

•• BuscarBuscar

•• BuscarVBuscarV

•• BuscarHBuscarH

•• ÍÍndicendice

•• CoincidirCoincidir

55

SoluciSolucióón: n: BBúúsquedasqueda en tablas de datosen tablas de datos


•• Buscar:Buscar: sirve para encontrar un valor en una tabla, sin importar las disirve para encontrar un valor en una tabla, sin importar las dimensiones de la mensiones de la mismamisma

Devuelve un Devuelve un úúnico valor. Hay que decirle en qunico valor. Hay que decirle en quéé rango realizar la comparacirango realizar la comparacióón y en n y en ququéé rango estrango estáá el valor.el valor.

Buscar(CondiciBuscar(Condicióón; rango_comparacin; rango_comparacióón;rango_valor)n;rango_valor)

Buscar(C10;A2:A8;B2:B8)Buscar(C10;A2:A8;B2:B8)

66

SoluciSolucióónn: B: Búúsqueda en tablas de datossqueda en tablas de datos


•• Buscar:Buscar: tambitambiéén sirve para datos textualesn sirve para datos textuales

Buscar(CondiciBuscar(Condicióón; rango_comparacin; rango_comparacióón;rango_valor)n;rango_valor)

Buscar (Buscar ("Ana "Ana ÁÁlvarez";lvarez"; A2:A6;A2:A6; B2:B6B2:B6))

Buscar (Buscar ("Sergio Zurdo";"Sergio Zurdo"; AA22:A:A66;; BB22:B:B6)6)

•• ¿¿CuCuáándo falla? ndo falla? ⇒⇒ si datos textuales, deben estar ordenadossi datos textuales, deben estar ordenados

HabrHabráá que ordenarlosque ordenarlos

77

SoluciSolucióón: Bn: Búúsqueda en tablas de datossqueda en tablas de datos


•• BuscarV y BuscarH: sirven para realizar bBuscarV y BuscarH: sirven para realizar búúsquedas en grandes tablas, sin ordenar, squedas en grandes tablas, sin ordenar, normalmente empleando una clavenormalmente empleando una clave

BuscarV (Clave; rango_tabla; nBuscarV (Clave; rango_tabla; núúmero_ col _valor; ordenado)mero_ col _valor; ordenado)

BuscarH (Clave; rango_tabla; nBuscarH (Clave; rango_tabla; núúmero_fila_valor; ordenado)mero_fila_valor; ordenado)

•• Clave es el valor por el que se identifica a los datos de la tabClave es el valor por el que se identifica a los datos de la tabla.la.

•• Rango_tabla es el rango de celdas que contiene la tabla de datosRango_tabla es el rango de celdas que contiene la tabla de datos..

•• NNúúmero_fila/col_valor indica el orden que ocupa en la tabla la filmero_fila/col_valor indica el orden que ocupa en la tabla la fila/col que a/col que contiene el valor que se busca.contiene el valor que se busca.

•• Ordenado es una variable lOrdenado es una variable lóógica (0 o 1) que permite elegir si la coincidencia del gica (0 o 1) que permite elegir si la coincidencia del valor buscado ha de ser parcial o total. Para que sea total 0, avalor buscado ha de ser parcial o total. Para que sea total 0, aproximado 1.proximado 1.

88


•• EJERCICIO 1:EJERCICIO 1: En el departamento de ventas de la empresa RUMOROSA, se discuteEn el departamento de ventas de la empresa RUMOROSA, se discute la polla políítica de comisiones a pagar tica de comisiones a pagar a sus comerciales. Calcular cual sera sus comerciales. Calcular cual seríía la comisia la comisióón que percibirn que percibiráá cada vendedor en los siguientes casos:cada vendedor en los siguientes casos:

•• Caso A: Caso A:

1.1. Si el importe de la venta es inferior a 30.050,61Si el importe de la venta es inferior a 30.050,61€€. Percibir. Percibiráá un 10% sobre ventasun 10% sobre ventas

2.2. Si el importe de la venta es igual o superior a 30.050,61Si el importe de la venta es igual o superior a 30.050,61€€. Percibir. Percibiráá un 15% sobre ventasun 15% sobre ventas

•• Caso B: Caso B:

1.1. En las zonas Norte y Sur se pagarEn las zonas Norte y Sur se pagaráá una comisiuna comisióón del 8%n del 8%

2.2. En las zonas Este y Oeste se pagarEn las zonas Este y Oeste se pagaráá una comisiuna comisióón del 14%n del 14%

•• Caso C: Caso C:

1.1. En la zona Sur se pagarEn la zona Sur se pagaráá una comisiuna comisióón del 8%n del 8%

2.2. En la zona Norte se pagarEn la zona Norte se pagaráá una comisiuna comisióón del 12%n del 12%

3.3. En las zonas Este y Oeste se pagarEn las zonas Este y Oeste se pagaráá una comisiuna comisióón del 15%n del 15%

•• Caso D: Caso D:

1.1. Si la venta realizada es inferior a 18.030,36Si la venta realizada es inferior a 18.030,36€€ se percibirse percibiráá una comisiuna comisióón del 7%n del 7%

2.2. Si la venta realizada estSi la venta realizada estáá entre 18.030,36entre 18.030,36€€ y 36.060,73y 36.060,73€€ se percibirse percibiráá una comisiuna comisióón del 10%n del 10%

3.3. Si la venta realizada es superior a 36.060,73Si la venta realizada es superior a 36.060,73€€ se percibirse percibiráá una comisiuna comisióón del 15%n del 15%

La hoja Excel en la que se realizarLa hoja Excel en la que se realizaráá el ejercicio serel ejercicio seráá de la forma:de la forma:

99


La hoja Excel en la que se realizarLa hoja Excel en la que se realizaráá el ejercicio serel ejercicio seráá de la forma:de la forma:

1010


•• Caso E: Caso E:

En funciEn funcióón del intervalo en el que se encuentre la venta, se podrn del intervalo en el que se encuentre la venta, se podráán percibir distintas comisiones. Los n percibir distintas comisiones. Los intervalos y las comisiones asociadas se encuentran en la tabla intervalos y las comisiones asociadas se encuentran en la tabla de comisionesde comisiones

INTERVALO VENTAS COMISIÓN

18.030,36€ o menos 5%

Entre 18.030,36€ y 27.045,54 € 7%

Entre 27.045,54 € y 36.060,73€ 9%

Entre 36.060,73€ y 45075,91€ 11%

Entre 45075,91€ y 54.091,09€ 13%

Más de 54.091,09€ 15%

•• Caso F: Caso F:

Los comerciales percibirLos comerciales percibiráán una comisin una comisióón en funcin en funcióón de la venta realizada (tabla anterior) y ademn de la venta realizada (tabla anterior) y ademáás cobrars cobraráán n tambitambiéén una prima extra en funcin una prima extra en funcióón de dicha venta. Utilizar la tabla de primas.n de dicha venta. Utilizar la tabla de primas.

INTERVALO VENTAS PRIMAS

18.030,36€ o menos 150.25 €

Entre 18.030,36€ y 27.045,54 € 210.35 €

Entre 27.045,54 € y 36.060,73€ 270.46 €

Entre 36.060,73€ y 45075,91€ 330.56 €

Entre 45075,91€ y 54.091,09€ 390.66 €

Más de 54.091,09€ 450.76 €

•• Caso G: Caso G:

Si la venta es inferior o igual a 36.060,73 Si la venta es inferior o igual a 36.060,73 €€ se percibirse percibiráá un 10% sobre las ventasun 10% sobre las ventas

En caso contrario usar la tabla de comisiones del caso E.En caso contrario usar la tabla de comisiones del caso E.

1111


SOLUCISOLUCIÓÓN del EJERCICIO 1:N del EJERCICIO 1:

1212


SOLUCISOLUCIÓÓN del EJERCICIO 1. FN del EJERCICIO 1. FÓÓRMULAS:RMULAS:

1313

BBúúsqueda en tablas de datos. Ejemplo con datos textualessqueda en tablas de datos. Ejemplo con datos textuales


•• EJERCICIO 2: Dada una tabla de calificaciones de alumnos, buscarEJERCICIO 2: Dada una tabla de calificaciones de alumnos, buscar a los alumnos que se a los alumnos que se pide: pide:

Decir quDecir quéé nota ha sacado el alumno Abanda Hernnota ha sacado el alumno Abanda Hernáándezndez

Indicar a quIndicar a quéé grupo pertenece el alumno Aceituno Gutigrupo pertenece el alumno Aceituno Gutiéérrezrrez

1414


•• SOLUCISOLUCIÓÓN N EJERCICIOEJERCICIO 2:2:

Decir quDecir quéé nota ha sacado el alumno Abanda Hernnota ha sacado el alumno Abanda Hernáándezndez

Indicar a quIndicar a quéé grupo pertenece el alumno Aceituno Gutigrupo pertenece el alumno Aceituno Gutiéérrezrrez

=BUSCARV(A16;$A$2:$E$11;5;0)=BUSCARV(A16;$A$2:$E$11;5;0)

=INDICE(E3:E11;COINCIDIR(A16;A3:A11;0))=INDICE(E3:E11;COINCIDIR(A16;A3:A11;0))

=BUSCAR(A16;A3:A11;E3:E11)=BUSCAR(A16;A3:A11;E3:E11)

=BUSCARV(A17;A3:F11;6;0)=BUSCARV(A17;A3:F11;6;0)

=BUSCAR(A3:A11;A3:E11;1)=BUSCAR(A3:A11;A3:E11;1)

1515

••EJERCICIO 3:EJERCICIO 3: Sea el siguiente catSea el siguiente catáálogo de productos informlogo de productos informááticos, con sus mticos, con sus máárgenes correspondientes. Rellenar el rgenes correspondientes. Rellenar el siguiente albarsiguiente albaráán buscando en el catn buscando en el catáálogo el P.V.D. Y calcular el P.V.P. aplicando los correspondientlogo el P.V.D. Y calcular el P.V.P. aplicando los correspondientes mes máárgenes. rgenes. Calcular el IVA a aplicar y el precio final. Calcular el IVA a aplicar y el precio final.

1616

=SI(C4="";"";C4*(F4+E4))=SI(C4="";"";C4*(F4+E4))

=SI(A4="";"";BUSCARV(A4;'EJER =SI(A4="";"";BUSCARV(A4;'EJER 33(CONT)'!$A$4:$D$16;3))(CONT)'!$A$4:$D$16;3))

=SI(E4="";"";REDONDEAR(E4*$D$21;2))=SI(E4="";"";REDONDEAR(E4*$D$21;2))

•• SOLUCISOLUCIÓÓN EJERCICIO 3.N EJERCICIO 3.

=SI(A4="";"";D4*BUSCARV(A4;'EJER =SI(A4="";"";D4*BUSCARV(A4;'EJER 33(CONT)'!$A$4:$D$16;4)+D4)(CONT)'!$A$4:$D$16;4)+D4)

1717


•• ÍÍNDICE y COINCIDIR:NDICE y COINCIDIR:

Son funciones para realizar bSon funciones para realizar búúsquedas en tablas de texto en las que no es necesario que los dasquedas en tablas de texto en las que no es necesario que los datos tos textuales esttextuales estéén ordenados. n ordenados. ÍÍndice: utiliza un ndice: utiliza un ííndice para acceder a un valorndice para acceder a un valor a a partir de una referencia o partir de una referencia o matrizmatriz. Coincidir: . Coincidir: Busca los valores en una referencia o matrizBusca los valores en una referencia o matriz

•• INDICE (INDICE (matriz;matriz; nnúúm_fila;m_fila; nnúúm_columna) m_columna)

devuelve el valor de una celda o de una matriz de celdas determidevuelve el valor de una celda o de una matriz de celdas determinada dentro del argumento ref.nada dentro del argumento ref.

•• COINCIDIR (COINCIDIR (valor_buscado;matriz_buscada;valor_buscado;matriz_buscada; tipo_de_coincidencia)tipo_de_coincidencia)

Valor_buscadoValor_buscado:: es el valor que se usa para encontrar el valor deseado en la taes el valor que se usa para encontrar el valor deseado en la tabla.bla. Valor_buscado es el valor que Valor_buscado es el valor que

desea hacer coincidir en la matriz_buscada. Por ejemplo, cuando desea hacer coincidir en la matriz_buscada. Por ejemplo, cuando busque algbusque algúún nn núúmero en la gumero en la guííaa teleftelefóónica, estarnica, estaráá

usando el nombre de la persona como valor de busando el nombre de la persona como valor de búúsqueda, pero el valor que realmente desea es el nsqueda, pero el valor que realmente desea es el núúmero de telmero de telééfono.fono.

PPuede ser un valor (nuede ser un valor (núúmero, texto o valor lmero, texto o valor lóógico) o una referencia de celda a un ngico) o una referencia de celda a un núúmero, a un texto o a un valor mero, a un texto o a un valor

llóógicogico. .

Matriz_buscadaMatriz_buscada:: es un rango mes un rango múúltiple de celdas que contienen posibles valores a buscar. Matrizltiple de celdas que contienen posibles valores a buscar. Matriz_buscada puede ser _buscada puede ser

una matriz o una referencia matricial.una matriz o una referencia matricial.

Tipo_de_coincidenciaTipo_de_coincidencia:: es el nes el núúmero mero --1, 0 1, 0 óó 1 y especifica c1 y especifica cóómo hace coincidir Microsoft Excel el valor_buscado con mo hace coincidir Microsoft Excel el valor_buscado con

los valores de matriz_buscada. los valores de matriz_buscada. 1: busca el mayor valor inferior o igual al buscado.1: busca el mayor valor inferior o igual al buscado. 00:: encuentra el primer valor que encuentra el primer valor que

es exactamente igual al valor_buscado. Los valores en matriz_buses exactamente igual al valor_buscado. Los valores en matriz_buscada pueden estar en cualquier orden.cada pueden estar en cualquier orden.

1818


•• ÍÍNDICE y COINCIDIRNDICE y COINCIDIR::

Se suelen emplear combinadas: INDICE(Se suelen emplear combinadas: INDICE(matrizmatriz;COINCIDIR(;COINCIDIR(valor_buscado;matriz_buscada;valor_buscado;matriz_buscada;

tipo_de_coincidencia)tipo_de_coincidencia)))

1919


• EJEMPLILLO:

=BUSCAR("Ana =BUSCAR("Ana ÁÁlvarez";A2:A6;B2:B6)lvarez";A2:A6;B2:B6)

=BUSCAR("Sergio =BUSCAR("Sergio Zurdo";A4:A8;B4:B8)Zurdo";A4:A8;B4:B8)

=BUSCARV("Carlos =BUSCARV("Carlos Segura";A2:D6;3)Segura";A2:D6;3)

=BUSCARV("Ana =BUSCARV("Ana ÁÁlvarez";A2:D6;4)lvarez";A2:D6;4)

=INDICE(A2:A6;COINCIDIR("Jefe =INDICE(A2:A6;COINCIDIR("Jefe secciseccióón";C2:C6;0))n";C2:C6;0))

Documents

Excel: SI y BUSCAR - ocw.uc3m.esocw.uc3m.es/.../practicas-1/practicas-excel/excel2.pdf · 4 Soluci ón: B úsqueda en tablas de datos Parte II.-Búsquedas, tablas y Bases de Datos