Funciones Combinadas 5-11-08

Función > BUSCARV

Categoría > Búsqueda y Refer

La función BUSCARV busca un valor en la primera columna de la izquierda de una tabla y luego devuelve un misma fila desde una columna especificada. De forma predeterminada, la tabla se ordena de forma ascenden

Ejemplo:

En el rango D17:E24 se tiene una relación de alumnos con sus respectivos códigos de Escuela . En el rango F desea colocar el nombre de la escuela según corresponda. Los nombres de las escuelas la obtendremos de la I17:J19. C 15 16 17 18 19 20 21 22 23 24 25

D Alumno Luis Garcia Antonio Solis Carmen Farías Sonia Heredia Ricardo Beltrán Rosa Quispe Olga Agurto Jorque Mejía

E Cod 01 03 02 01 01 03 02 01

F

G

Especialidad Contabilidad Educación Economía Contabilidad Contabilidad Educación Economía Contabilidad

H Mínima

I

J

K

Cod Especialidad 01 Contabilidad 02 Economía 03 Educación

L

1 2

=BUSCARV (E17;$I$17:$J$19;2) Buscamos el valor de la celda E17 en la primera columna de tabla I17:J19 y cuando lo hallamos devolvemos el valor de la columna

Sintaxis

= BUSCARV (valor_buscado ; matriz_buscar_en ; indicador_columnas ) valor_buscado: es el valor buscado en la primera columna de la tabla y puede ser un valor, referencia o una c texto. matriz_buscar_en: es una tabla de texto, números o valores lógicos en los cuales se recuperan datos. Matriz puede ser una referencia a un rango o un nombre de rango. indicador_columnas: es el número de columna de matriz_buscar_en desde la cual debe devolverse el valor coincida. La primera columna de valores en la tabla es la columna 1.

Ejercicio

Usando la función BUSCARV colocar la categoría del trabajador F46:F53 de acuerdo a su categoría, la cual d buscada en la tabla I46:J48.

C 44 45 46 47 48 49 50 51 52 53 54

D Alumno Luis Garcia Antonio Solis Carmen Farías Sonia Heredia Ricardo Beltrán Rosa Quispe Olga Agurto Jorque Mejía

E

F

Cod C02 C02 C03 C01 C03 C02 C01 C03

Categoría CONTADORES CONTADORES INGENIEROS ABOGADOS INGENIEROS CONTADORES ABOGADOS INGENIEROS

G

H

I

J

K

L

Cod CATEGORIA C01 ABOGADOS C02 CONTADORES C03 INGENIEROS

Solución celda F46 Empleado =BUSCARV (E46;$I$46:$J$48;2)

a > Búsqueda y Referencia

abla y luego devuelve un valor en la rdena de forma ascendente.

de Escuela . En el rango F17:F24 se uelas la obtendremos de la tabla

M

N

O

P

Q

R

2) 17 en la primera columna de la os devolvemos el valor de la 2da

un valor, referencia o una cadena de

e recuperan datos. Matriz_buscar_en

al debe devolverse el valor que

o a su categoría, la cual deberá ser

M

46;$I$46:$J$48;2)

N

O

P

Q

R

Nombres Angel Juan Kathy Gregory Luigi Ivan Silvana Cesinho Jimmy Paulo César

Apellido Martínez González Montaner Pezoa Katz Leeroy Fernández Núñez Vivanco Cruz

Ejecutivo

email

%

Total de Venta por Situación

Venta máxima Venta Mínima

Número Bajo Notable

INDICACIONES

1. Renombrar la Hoja1 por EJECUTIVOS. 2. Completar la columna Ejecutivos con los datos de las columnas Apellidos y Nombres, el primer registro quedara asi: 3. Completar el Email de los Ejecutivos, sabiendo que esta formado por:la segunda letra de su nombre seguido de la segu el primer registro quedaria asi: [email protected] 3. Hallar las Ventas que es igual al promedio del Trimestre1 , Trimestre2 y Trimestre 3. Usar función: Promedio. 4. En la celda B13 hallar la venta máxima. Usar función MAX. 5. En la celda B14 hallar la venta minimas. Usar función MIN. 6. En la celda H17 indicar el numero de ejecutivos con lso que ceunta la compañía.Usar función CONTARA. 7. En la Celda H18 insertar la fecha actual, utilizando la funcion HOY() 8. Completar la columna Situación, teniendo en cuenta las consideraciones siguientes: Usar función SI. Si el promedio es a lo mucho 5,1 la situación del ejecutivo será "Bajo" Si el promedio es mayor o igual que 5,3, la situación es "Notable" 9. En la celda B20 hallar el número de empleados cuya situación es igual a Bajo. Usar función CONTAR.SI. 10. En la celda B21 hallar el número de empleados cuya situación es igual a Notable. Usar función CONTAR.SI. 11.En la celda C20 digitar =B20/$F$17. Así se está dividiendo el número de Bajos por el total de ejecutivos (notar que se u Después arrastrar hacia abajo este resultado para obtener los demás porcentajes. Debe elegir Formato-celdas-número-po 12. En la celda D20. Hallar el Total de Venta de cada ejecutivo de acuerdo a la Situación a la que pertenesca. 13. Calcular el Tiempo de Servicio para cada trabajador conociendo su Fecha de Ingreso (considerar 365 días por año y to

F.Ingreso Trimestre1 Trimestre2 Trimestre3 1/13/1999 6.0 2.5 2.3 7/11/2001 6.5 4.8 4.0 7/16/2004 3.4 6.0 5.8 11/23/2002 7.0 4.0 5.0 2/12/2000 4.7 5.5 6.0 4/28/2003 6.2 6.5 7.0 9/3/2005 5.9 4.4 6.4 6/8/1999 5.3 7.0 4.9 9/7/2001 2.5 6.0 2.3 6/14/2003 7.0 6.7 6.4

Venta

Situación

Tiempo Servicio

N° Ejecutivos Fecha de Evaluación

INDICACIONES

res, el primer registro quedara asi: Martínez, Angel. Usar Funcion CONCATENAR() tra de su nombre seguido de la segunda, tercera y cuarta letra de sus Apellidos,luego el dominio de la compañía:@kvrz.com

3. Usar función: Promedio.

sar función CONTARA.

s: Usar función SI.

r función CONTAR.SI. Usar función CONTAR.SI. r el total de ejecutivos (notar que se usa referencia absoluta a la celda F17 que contiene el total de ejecutivos). ebe elegir Formato-celdas-número-porcentaje para que los valores se muestren en %. ción a la que pertenesca. eso (considerar 365 días por año y tomar solo la parte entera)

pañía:@kvrz.com

PL DATOS PERSONALES Ejecutivo

E.Civil

Tiempo de Servicio

Situación

C C S S C C S S S C Totales

Realizar los siguientes cálculos : 1 Rellenar el campo Ejecutivo Y Tiempo de Servicio, copiandolos de la Hoja EJECUTIVOS 2 Copiar el campo Situación usando celdas de referencia de la Hoja EJECUTIVOS 3. Sueldo Básico Situación Bajo Notable

4. Bonif1 Sueldo S/. 1,300.00 S/. 1,100.00

E.Civil C S

5. Bonif2

Tiempo de Servicio 7 % del S.Básico 0…2 5% del S.Básico 3…5 6… Bonif.

6. Total Bonif = Suma de las bonificaciones 7. Sueldo Bruto = Sueldo Básico + Total Bonificaciones 8. AFP = 11% del Sueldo Básico 9. Impuesto Renta = 15% del Sueldo Básico ( aplicado a Sueldo Bruto > 1500 ) 10. Total Descuentos = AFP + Imp.Renta 11. Sueldo Neto = Sueldo Bruto - Total Descuentos. Mostrar los resultados en formato de Moneda (soles S/. Un 12. Hallar Totales. Usando fucnión adecuada.

PLANILLA DE SUELDOS

Situación

Sueldo Básico

5. Bonif2 Bonif. S/. 12.00 por cada año de servicio S/. 18.00 por cada año de servicio S/. 25.00 por cada año de servicio

en formato de Moneda (soles S/. Un decimal)

BONIFICACIONES Bonif1

Bonif2

Total Bonificaciones

Descuentos Sueldo Bruto

AFP

Imp.Renta

Total Descuentos

Sueldo Neto

PRACTICA DIRIGIDA EXCEL

Transporte Aéreo "CHASQUI TOURS" SALIDA :

Arequipa

Piloto Destinos Lima Chiclayo Tumbes Piura Trujillo Cajamarca Chiclayo Tarapoto Chiclayo Trujillo Tumbes Iquitos Piura Chiclayo Trujillo Tumbes Tarapoto Piura

Ruta Arequipa - Lima Arequipa - Chiclayo Arequipa - Tumbes Arequipa - Piura Arequipa - Trujillo Arequipa - Cajamarca Arequipa - Chiclayo Arequipa - Tarapoto Arequipa - Chiclayo Arequipa - Trujillo Arequipa - Tumbes Arequipa - Iquitos Arequipa - Piura Arequipa - Chiclayo Arequipa - Trujillo Arequipa - Tumbes Arequipa - Tarapoto Arequipa - Piura

Apell. Pat. Castro Reyes Pérez Ayvar Gonzales Arauco Bazan Arce Mellado Risso Torres Vilca Osorio Suarez Ayala Rodriguez Vilchez Sipan

Apell. Mat. Quispe Ismodes Cárdenas Carmona Toro Loayza Castillo Poma Leyva Castro Gonzales Arauco Bazan Orihuela Bohórquez Fonseca Chirinos Soto

nombres Ernesto Augusto Carlos Jhon Ivan Nolberto Renatto Rodrigo Jhonatan Leonardo Roberto Erick Daniel Percy Johan Omar Alex Mauro

RESUMEN Destino

Precio $

Desino

Cajamarca

$160.00

Chiclayo

$150.00

Lima

Iquitos

$220.00

Chiclayo

Lima

$80.00

Tumbes

Piura

$180.00

Piura

Tarapoto

$200.00

Trujillo

$125.00

Tumbes

$200.00

PREGUNTAS: 1.- Utilizando la Función Concatenar complete la columna Ruta sabiendo que esta dado por la ciudad de Salida + la ciudad

2.- Complete los Apellidos y Nombres del Piloto utilizando la función correspondiente. Ej. CASTRO QUISPE, Ernesto. (con 3.- Complete la columna Precio $ del pasaje utilizando la función Buscar V, tome en cuenta la tabla ubicada en el rango de 4.- Complete las fechas de vuelo sabiendo que los vuelos salen 2 días despues del anterior. Considere sabados y doming 5.- Complete el Nro. De pasajes vendidos para las dos ultimas ciudades, sabiendo que, para estas ciudades se vendió el 7 6.- Halle el Nro. De Boleto que esta dado por lo siguiente: primera letra de la ciudad de salida, seguido de las dos primeras 7.- Halle el Ingreso en S/. Por vuelo. Considere el precio $ y la cantidad de pasajes vendidos. 8.- Genere el correo electronico del piloto sabiendo que esta compuesto por la primera letra de su nombre, seguido de su 9.- Halle la Condición del piloto que esta dado por el Nr. De Horas de vuelo, sabiendo que si las horas de vuelo esta por de 9.- Complete la tabla resumen según la información solicitada.

eo "CHASQUI TOURS" Tipo de Cambio

3.12

Piloto Apellidos y Nombres Castro Quispe, Ernesto Reyes Ismodes, Augusto Pérez Cárdenas, Carlos Ayvar Carmona, Jhon Gonzales Toro, Ivan Arauco Loayza, Nolberto Bazan Castillo, Renatto Arce Poma, Rodrigo Mellado Leyva, Jhonatan Risso Castro, Leonardo Torres Gonzales, Roberto Vilca Arauco, Erick Osorio Bazan, Daniel Suarez Orihuela, Percy Ayala Bohórquez, Johan Rodriguez Fonseca, Omar Vilchez Chirinos, Alex Sipan Soto, Mauro

Total de Pasajes Vendidos

Fecha de Vuelo

Precio $ Pasaje

11/10/2008 11/12/2008 11/14/2008 11/16/2008 11/18/2008 11/20/2008 11/22/2008 11/24/2008 11/26/2008 11/28/2008 11/30/2008 12/2/2008 12/4/2008 12/6/2008 12/8/2008 12/10/2008 12/12/2008 12/14/2008

80 150 200 180 125 160 150 200 150 125 200 220 180 150 125 200

Nro. De pasajes vendidos

Nro de Boleto

Ingreso S/. Por Vuelo

70 65 90 120 75 110 58 75 95 137 70 77 150 80 100 80 128 233

ALi8 Ch1 Tu2 RPi1 ATr1 ACa1 ACh1 ATa2 ACh1 ATr1 ATu2 AIq2 APi1 ACh1 ATr1 ATu2

17472 30420 56160 67392 29250 54912 27144 46800 44460 53430 43680 52852.8 84240 37440 39000 49920

Total de IngresoS/. Por Destino 70

5600

65

9750

90

18000

120

21600

e esta dado por la ciudad de Salida + la ciudad de Destino. Ej. Arequipa - Lima. (tome en cuenta los signos y el espacio).

ondiente. Ej. CASTRO QUISPE, Ernesto. (considere las mayusculas y signos). ome en cuenta la tabla ubicada en el rango de celda A31:B39 es del anterior. Considere sabados y domingos. iendo que, para estas ciudades se vendió el 70% más de los que se vendió en la ciudad de Trujillo. Aplique Redondeo. ciudad de salida, seguido de las dos primeras letras de la ciudad de destino, seguido del primer nro. De los pasajes vendidos y todo en asajes vendidos. a primera letra de su nombre, seguido de su apellido materno, seguido de la primera letra de su apell. Paterno, seguido de @chasquito sabiendo que si las horas de vuelo esta por debajo de las 100 horas es "Poca Experiencia", si esta entre 100 y 350 horas es "Con Expe

os y el espacio).

E-mail Piloto

Nro. De Horas de Vuelo

150 200 80 560 364 457 650 458 231 500 300 146 250 75 160 80 360 500

Condición

que Redondeo. idos.

aterno, seguido de @chasquitours.com.pe 100 y 350 horas es "Con Experiencia", si tiene mas de 500 horas de vuelo es un piloto con "Mucha Experiencia".