INTRODUCCION A LAS FUNCIONES LÓGICAS Y DE BUSQUEDA Y REFERENCIA   Excel permite para crear fórmulas condicionales que analicen datos y devuelvan un valor basado en los resultados de dicho análisis.

FUNCION CONTAR.SI

Cuenta las celdas en el rango que cumplan con la condición estipulada, pero que no estén en blanco

=CONTAR.SI(rango;criterio) Ejemplo: =CONTAR.SI(B2:B45;”>=3,5”)

=CONTAR.SI(A2:A45;"Ruiz")

TALLER USO DE CONDICIONALES COMO TRABAJAR CON CONTAR.SI Diseñe una hoja de trabajo con los rótulos APELLIDO, NOMBRE, NOTA1, NOTA2 Y NOTA3, para 15 estudiantes. 1.Los rótulos ubíquelos en la fila 3 a partir de la celda A3. 2. Ingrese las notas correspondientes en un rango de 1 a 5, con un formato de 1 decimal. Debe tener en cuenta que el curso tiene estudiantes con un alto rendimiento, así como alumnos con bajos rendimientos. 3. Ordene la tabla por Apellido 4. Enumere los estudiantes, adicionando una columna. La numeración debe quedar centrada 5. Una las celdas B20 y C20 y coloque el rotulo PROMEDIO 6. Halle el promedio de cada una de las notas 7. Una las celdas B21 y C21 y coloque el rotulo MAYORES DE 3,0 8. Halle por nota el número de estudiantes cuya nota es mayor de 3,0. Para ello utilice la función CONTAR.SI =CONTAR.SI(rango;criterio) 9. Coloque el rótulo DEFINITIVA y hállela 10.Haciendo uso del condicional SI, en la columna H diseñe una formula en donde establezca si la nota es mayor de 3, debe aparecer el texto PROMOVER, notas inferiores a 3 debe reportar un REPETIR ASIGNATURA

Una de las funciones más potentes que se utilizan en Excel es la función SI. FUNCION SI

=SI(condición;verdadero;falso) Donde condición es una situación a evaluar que puede Si se cumple, se ejecutará verdadero, o en caso contrario, se ejecutará falso.

 

Ejemplo: =SI(A3>B12;”Correcto”;”Incorrecto”) Si la celda A3 es mayor que la celda B12, aparecerá la palabra Correcto, en caso contrario, aparecerá la palabra Incorrecto. =SI(A1=”Bajo mínimos”;”Quiebra”;”Normal”) Si la celda A1 contiene la palabra Bajo mínimos, en la celda actual aparecerá la palabra Quiebra, en caso contrario, aparecerá la palabra Normal. =SI(O(A1=B1;C1=D1);”Bien”;”Mal”) Aquí ha de cumplirse una de las dos condiciones. Nótese la utilización del operador O, es decir, que se tiene que cumplir una de las dos condiciones. =SI(y(A1=B1;C1=D1);”Bien”;”Mal”) Deben cumplirse las dos condiciones. Nótese la utilización del operador Y. COMO TRABAJAR CON SI Diseñe una hoja de trabajo para la nueva compañía ABC de Colombia que tiene una nómina de 15 empleados. Debe incluir los siguientes rótulos 1. Inserte una imagen para su hoja de trabajo en el extremo superior izquierdo de su hoja de trabajo. Para ello, utilice de la barra de herramientas/Insertar/imagen 2. Combine las celdas C2 : I2 y coloque como título ART BISC COLOMBIA 3. A partir de la celda A6, escriba los siguientes rótulos NOMBRE, IDENTIFICACION, EDAD, CARGO, SUELDO BÁSICO, DESCUENTO AHORRO, AUXILIO TRANSPORTE, SUELDO A PAGAR 4. Los nombres, cargos e identificaciones son los siguientes, complete la información faltante CABRALES MARTINEZ JORGE GUZMAN JUAN JOSE AVILA GOMEZ ANA MARIA LOPEZ LOPEZ ROSA ESTHER RODRIGUEZ RUIZ CLAUDIA

 

43 32 28 43 31

56789345 67456234 52563894 68459632 52653636

Auxiliar Gerente Secretaria Jefe de Nomina Secretaria

BENITEZ LUNA MARIA CLARA SANCHEZ SANCHEZ JESUS MARIA TORRES PEREZ JUAN BECERRA RUIZ PEDRO TRUJILLO BAENA MARIA JESUS PALLARES TELLEZ JUAN PABLO TORRES BERMUDEZ FABIOLA POVEDA ALEJANDRA GARRIDO MARLEN GOMEZ JAIRO

38 27 30 36 35 33 32 45 58 36

45872566 96235689 54784512 56214587 28564569 25658956 56235689 56457854 45785236 58986956

Servicios Generales Auxiliar Subgerente Mensajero Jefe de Planta Asistente Servicios Generales Secretaria Jefe de Bodega Auxiliar

5. Asigne los sueldos teniendo en cuenta que: Mensajería y Servicios Generales gana el sueldo mínimo Secretarias sueldos menores a 600000 Auxiliares tienen sueldos entre 800000 y 1000000 Jefes y Asistentes sueldos entre 1200000 y 1800000 Gerencia y Subgerencia sueldos entre 2000000 y 4000000 6. Ubique la información de Cargo después de Nombre y ordene la tabla por Cargo 7. Para el fondo de ahorros manejado por la empresa, tenga en cuenta que: Sueldos mayores o iguales a 1.500.000 se efectuará un descuento es del 8% Sueldos menores, el descuento es del 4%. (utilice la función Si) 8. Para sueldos inferiores o iguales a 700000 asigne un auxilio de transporte de 60000, sueldos superiores no tendrán este auxilio (utilice la función Si) 9. Halle los sueldos a cancelar en el mes, teniendo en cuenta los descuentos y auxilios 10. Inserte una columna en A coloque el rótulo Numeración y enumere los empleados, para ello genere una serie lineal de la siguiente manera: Coloque el número 1 para el primer empleado y presione enter Ubíquese nuevamente en la celda Edición/Rellenar/Series Del cuadro de dialogo elija Columna, determine el incremento (1) y establezca el límite en 15. Presione Enter y observe 11. A partir de la celda A18 Halle: (a26)El mayor sueldo cancelado (a27)El menor sueldo cancelado en el mes Esta última información enciérrela dentro de un recuadro y resáltela utilizando colores, bordes y opciones de formato

 

En  algunas  ocasiones,  se  utiliza  el  condicional  Si  combinados  dos  o  tres  veces  para  dar  una  solución.    A  veces es más fácil, utilizar la función BUSCARV   FUNCIÓN BUSCARV Buscarv(valor_buscado;matriz_buscar_en;indicador_columnas;ordenado) Buscar: Busca en la primera columna de una matriz y se mueve en horizontal por la fila para devolver el valor de una celda V -> Hace referencia a la búsqueda vertical Valor_buscado: Valor que se va a buscar en la primera columna de la matriz de la tabla. Matriz_buscar_en: Dos o más columnas de datos. Los valores de la primera columna de matriz_buscar_en son los valores que busca valor_buscado, los cuales pueden ser texto, números o valores lógicos. Indicador_columnas Corresponde al número de columna desde la cual debe devolverse el valor que coincide. Ej: si el indicador_columnas es igual a 2, devuelve el valor de la segunda columna de matriz_buscar_en 1.Cargue el Excel y digite la siguiente información: A

B

1 2 NOMBRE 3 Rodriguez Jaime 4 Beltran Angela 5 Torres Andres 6 Cifuentes Hernando 7 Marin Martha 8 Delgado Alejandro 8 Gomez Alejandro 9 Uribe Sandra 10 Hernandez Carolina 11 Herrera Andrea 12 Vargas Carlos Andres 13 Zuluaga Victor

C INGRESO 1999-06-01 1998-01-01 2003-02-01 1997-01-01 2003-01-01 2004-06-01 2005-06-01 2005-01-01 2006-01-01 2002-06-01 2005-06-01 1996-01-01

D

ANTIGUEDAD 01/01/20010

E

F

AUMENTO

SUELDO 1.357.600 2.300.000 1.500.000 2.300.000 1.100.000 900.000 750.000 750.000 515.000 1.500.000 515.000 3.800.000

En la misma hoja de trabajo, pero en la parte inferior digite la tabla anexa TABLA DE AUMENTOS BONO ANTIGÜEDAD % DE AUMENTO ANTIGUEDAD 0 0% 1 0% 2 2% 3 2% 4 2% 5 5% 50000 6 5% 50000 7 6% 50000 8 7% 50000 9 7% 50000 10 10% 80000 11 10% 80000 12 10% 80000

 

Calcule los años de antigüedad entre las 2 fechas, fecha de ingreso y el año actual para (01/01/2010), dato que se encuentra ubicado en la celda C2, la cual debe fijarse como celda absoluta. Una vez haya diseñado la fórmula, cópiela para todas las celdas y observe el resultado UTILICE LA FUNCION AÑO De formato al rango D3:D14 en Porcentaje con 0 posiciones decimales HALLE EL SUELDO BASICO PARA EL 2010, TENIENDO EN CUENTA EL PORCENTAJE DE AUMENTO DETERMINADO Y EL BONO DE ANTIGÜEDAD ESPECIFICADO EN LA TABLA . Utilice la función Buscarv para asignar a cada empleado el porcentaje de aumento teniendo en cuenta la tabla de porcentajes determinada por la empresa Buscarv(valor_buscado;matriz_buscar_en;indicador_columnas;ordenado) =Buscarv(C3;$D$19:$F$32;2;FALSO) Determine el auxilio de transporte para los empleados que ganan menos de 2 salarios mínimos Determine los porcentajes de descuento por salud y pensión (4%) Halle el sueldo a pagar

TALLER EXCEL Ejercicio BUSCARV Veamos usos de la opción BUSCARV cuando los datos se encuentran en otra hoja de trabajo 1. Cree un nuevo archivo en Excel y etiquete la hoja de trabajo como DATOS 2. Digite la siguiente información iniciando en la celda A1 hasta la c6

 

A 1 2 3 4 5 6 7 8 9 10

B

C

Zapatos Medias Blusas Tenis Pantalones Camisas Corbata Jeans Top Camiseta

67000 12000 45000 120000 98000 64000 27800 72000 23000 45600

El numero de la columna (A) lo vamos a relacionar con la referencia de la entrada. En otras palabras, por ejemplo: el numero 3 identifica a Blusas (es la referencia de blusas) 3. 4. 5. 6. 7. 8. 9.

En la Hoja 2 vamos a realizar una pequeña FACTURA, para ello: Combine las celdas A3 hasta F3 En la celda A4 coloque el rotulo REFERENCIA En B4 digite ITEM para C4 asigne el rotulo VALOR UNITARIO En D4 CANTIDAD y E4 TOTAL En la celda B5 digite la siguiente formula

=BUSCARV($A5;datos!$A$1:$C$6;2)             

Referencia  que  debe buscar 

REFERENCIA     

ITEM  #N/A 

Ubicaciond  e  los  datos‐ Matriz_buscar_en 

VALOR U 

Indicador  columna 

CANTIDAD 

de 

TOTAL 

Error de valor no  disponible 

Digite cualquier referencia en A5 y observe como inmediatamente busca en la tabla ubicada en Datos la referencia correspondiente y devuelve el indicador de columna 2 Como solucionar este inconveniente? .............. una alternativa es combinar las funciones Si y busqueda 10. Ingrese a la celda b5 y presione la tecla F2, modifique la fórmula: =SI($A5"";BUSCARV($A5;datos!$A$1:$C$6;2);"") 11. Copie la formula al rango B6:B15 12. En la celda C5 diseñe la fórmula, que permita traer el dato correspondiente al valor unitario de la referencia 13. copie la fórmula en el rango C6:C15 14. seleccione el rango A1:F15 y coloque fondo de color 15. Ubíquese en la celda A5 y digite un numero de referencia cualquiera y observe 16. Genere las cantidades de compra

 

17. Recuerde que el total corresponde a CANTIDAD por el VALOR UNITARIO 18. Ubique en D16 el rótulo SUBTOTAL y hállelo 19. Rotule la celda D17 como IVA-16% , diseñe la fórmula pertinente en la celda E17 20. Rotule la celda D18 como TOTAL, diseñe la fórmula que le permita hallarlo, de formato a la celda (control-1/numero/moneda/español(Colombia) 21. Ahora elimine las referencias entradas y la cantidad. Observe lo que sucede en la factura ¿DE QUE MANERA ES POSIBLE SOLUCIONAR LA SITUACION QUE SE PRESENTA?

FORMATO CONDICIONAL La función Si puede crear formatos que dependen de ciertas condiciones, permitiendo la combinación de valores y formatos basados en una combinación lógica 1.- Seleccionar las celdas a las que se quiere dar formato. 2.- Elegir Formato/ Formato condicional. Aparecerá la siguiente ventana:

3.- Definir la condición se puede efectuar de 2 formas: a.- Valor de la celda. Se utilizan los valores de las celdas seleccionadas como el criterio de formato, elegimos la frase de comparación, que por defecto es entre, se introduce un valor en el cuadro correspondiente. Puede introducirse un valor constante o una fórmula; debe incluirse un signo igual (=) delante de la misma. b.- Fórmula. Para evaluar datos o una condición que no sean los valores de las celdas seleccionadas. En este caso la figura que aparece es la siguiente 4.- Es posible adicionar mas condiciones, seleccionando el botón Agregar y repitiendo los pasos anteriores.

Pueden especificarse tres condiciones como máximo. Si se especifican varias condiciones y más de una de ellas es verdadera, solamente se aplicará los formatos de la primera condición que sea verdadera. Si ninguna de las condiciones que se han especificado es verdadera, las

 

celdas conservarán los formatos existentes. identificar una cuarta condición.

Pueden utilizarse los formatos existentes para

COMO BUSCAR CELDAS QUE TENGAN FORMATOS CONDICONALES En el menú Edición, seleccionar la opción Ir a y pulsamos el botón Especial... (o hacer uso del comando CONTROL-I)

 

Ejercicio Elabore una tabla para 6 alumnos cuya nota definitiva dependerá de los siguientes porcentajes Nota1 25% Nota2 35% Nota3 40% Rótulos en la fila 3 (nombre, nota1,nota2, nota3, def, comentario) Para la definitiva, debe usar el formato condicional que tenga en cuenta Si la nota es mayor a 3,5 color verde (elija bordes para la celda de acuerdo a su criterio) Notas entre 3 y 3,5 color amarillo (bordes de su elección) Notas inferiores a 3 de color rojo (bordes de su elección)

Para comentario tenga en cuenta que debe utilizar la función SI Si la nota es mayor a 3,5 “Excelente trabajo” Notas entre 3 y 3,5, “Puede esforzarse más” Notas inferiores a 3,0 “Requiere acompañamiento”