TALLER FILTROS AVANZADOS
Lady Tatiana Santana Vasquez
DIDÁCTICA
Para saber a qué letra corresponde cada número, usamos la función CARACTER y
la estiramos en cada columna. Ahora si podemos desarrollar la sopa de letras.
Buscamos las respuestas de las 6 preguntas en la sopa de letras.
Para desbloquear la hoja de informe, usamos la palabra MEMORIA que es la 4
palabra en orden alfabético.
Para que nos muestre los datos, vamos a INICIO > FORMATO > MOSTRAR FILAS y
MOSTRAR COLUMNAS
Eliminamos los espacios vacíos, para esto seleccionamos todos los datos, y
FILTRAMOS las celdas que están VACÍAS
Seleccionamos todas las filas y le damos BORRAR FILA
Los codigos aparecen en blanco y no se ven, por tanto le ponemos color negro al
texto.
NORMALIZAR
Empezamos a arreglar la tabla. En la columna de códigos usaremos la función de
BUSCAR Y REEMPLAZAR. Buscamos las S y las reemplazamos por un 5.
En las columnas de apellido, nombre, profesión, empresa, división, estado, vivienda,
educación, tarjeta, seccional y ciudad, debemos reemplazar los 5 por S.
En las filas de ahorro y gasto debemos buscar las O y reemplazar por 0
Para los campos vacíos de la columna CARRO, vamos a llenarlos con un NO
Filtramos la columna EMPRESA y seleccionamos solo la que dice CARBOBOCOL,
corregimos esas celdas por CARBOCOL
Para la columna DIVISIÓN seleccionamos las dos primeras opciones para corregirlas
por ADMINISTRATIVA
Filtramos la columna VIVIENDA, seleccionamos APATRAMENTO y corregimos las
celdas por APARTAMENTO
Creamos una columna que se llame SUMATORIA COD y usaremos la siguiente
función:
=EXTRAE(A2;1;1)+EXTRAE(A2;2;1)+EXTRAE(A2;3;1)+EXTRAE(A2;4;1)+EXTRAE(A2;5;1)+EXT
RAE(A2;6;1)
La cual significa que de la celda A1 (que es de la columna CÓDIGO) debe
EXTRAER desde la posición 1, un solo carácter. Luego extraerá desde la posición 2,
3, 4, 5 y 6. Se sumarán todos los resultados para tener la sumatoria de los códigos.
Creamos una columna llamada CÓDIGO PARES y usamos la función =ES.PAR(S2) la
cual arrojará “VERDADERO” en caso de que el código sea PAR, y “FALSO” en caso
de que sea IMPAR.
Se crea una nueva columna llamada NOMBRE COMP y se usa la función
=CONCATENAR(C2;B2) que va a unir el nombre y el apellido
Una nueva columna llamada NOMBRE SIN ESPACIOS y usando la función
=SUSTITUIR(U2;" ";"") haremos que dentro de la celda, busque los espacios “ “ y los
sustituya por “” que sería no espacio.
Una nueva columna que diga CARACT NOM, con la función =LARGO(V2) que se
encargará de contar cuantos caracteres tiene el NOMB SIN ESPACIOS
En una nueva columna llamada NOMB IMPARES con la función =ES.IMPAR(W2) nos
dirá si el número que hay en CARACT NOMB es par (VERDADERO) o no (FALSO)
Para calcular el monto de préstamo, multiplicaremos la columna de ahorro por 3
Para calcular la cuota usaremos la función =-PAGO(2%;24;AA2;0) la cual calculará
cuánto debe pagar mensualmente la persona con una tasa de interés mensual del
2%, a 24 meses por el monto del préstamo.
PUNTO 1:
Definiremos los criterios para usar el filtro avanzado, los cuales son: que en la
columna de CÓDIGOS PARES los resultados sean VERDADERO, que extraiga los
VERDADERO de la columna CÓDIGOS IMPARES, y que además NO tengan CARRO.
Más abajo dejaremos nombrado lo que deseamos que nos arroje. En este caso será
NOMBRE, APELLIDO, CIUDAD, PROFESIÓN, PRÉSTAMO, CUOTA.
En la pestaña DATOS, usaremos la herramienta FILTRO - AVANZADAS de la sección
“ordenar y filtrar”. Nos solicita 3 criterios:
● Rango de la lista, donde seleccionaremos toda la base que tenemos en la
hoja “INFORME”
● Rango de criterios, donde seleccionamos desde A2 hasta C3 que fue donde
dejamos escritos nuestros criterios
● Copiar a, serán los parámetros que dejamos escritos para que al efectuar el
filtro, nos arroje esos datos (A7 hasta F7)
Al darle ACEPTAR, nos arrojará las personas que cumplen con los criterios.
PUNTO 2:
Creamos una nueva columna llamada NOMBRE COMPLETO con la función
=MAYUSC(B2& " " & C2) que pondrá en mayúscula el texto de B2, luego añadirá un
espacio “ “, y por último pondrá en mayúscula el texto de C2.
Creamos una nueva columna llamada SUELDO que multiplicará la columna de
CARACT NOMB multiplicado x1000000
Escribimos en una nueva hoja los criterios:
● ???e*, que significa que busque en la columna APELLIDO las celdas que en
la cuarta posición tengan la letra “e” y el asterisco indica que después de la
letra e puede haber cualquier carácter
● ????i* que determina que busque en la columna APELLIDO las celdas que
tengan la letra “i” en la quinta posición y que no importan los caracteres
restantes
El rango de la lista será toda la base de la hoja INFORME
y para copiar a, seleccionamos los resultados que esperamos: Nombre completo y
sueldo
PUNTO 3:
Se escribirá una formula:
=O(Y(INFORME!S2="CALI";INFORME!M2="CASA";INFORME!F2="FERROVIAS";INFORME!L1
="SI");Y(INFORME!S2="BELLO";INFORME!F2="CARBOCOL";INFORME!L1="NO");Y(INFORM
E!S2="BOGOTA";INFORME!M2="APARTAMENTO";INFORME!L1="SI"))
Que pondrá ciertos criterios: que la persona viva en Cali, en una casa, que trabaje
en ferrovia y que tenga carro, o que viva en Bello, no importa la vivienda, que
trabaje en carbocol y que no tenga carro, o que viva en Bogota, en un
apartamento, no importa donde trabaje y que tenga carro.
Entonces con la herramienta de FILTRO AVANZADO seleccionamos los parámetros:
● Rango de la lista: toda la base de la hoja de INFORME
● Rango de criterios: seleccionamos la celda que formulamos con los criterios
● copiar a: seleccionamos desde A9 hasta E9
PUNTO 4:
Hacemos lo mismo que en los puntos anteriores, escribimos los criterios,
seleccionamos la base de la hoja de informe y definimos donde queremos que
arroje los resultados.
Para asignar la prima vamos a insertar una tabla dinámica en la hoja de cálculo
existente
En filas ponemos medio de pago y en valores inicialmente nos aparece suma de
gasto
Filtramos las etiquetas de fila y seleccionamos solo Credito y Debito
Cambiamos la configuración del campo de valor y en vez de suma seleccionamos
PROMEDIO
Creamos una nueva columna al lado de los resultados del filtro avanzado llamada
PRIMA con la siguiente función
=ALEATORIO.ENTRE(IMPORTARDATOSDINAMICOS("GASTO";$A$22;"MEDIO
PAGO";"CREDITO");IMPORTARDATOSDINAMICOS("GASTO";$A$22;"MEDIO
PAGO";"DEBITO"))
la cual indica que se debe arrojar un número al azar entre el promedio del gasto
del credito y el promedio del gasto de debito.
PUNTO 5:
Establecemos los criterios de que revise valores en la columna de AHORRO que
estén entre 400.000 y 9000.000, con el fin de que arroje el NOMBRE COMPLETO y el
monto de AHORRO
Usamos la herramienta de FILTRO AVANZADO, seleccionamos la base de la hoja
INFORME, los criterios ya escritos y los resultados que queremos que arroje.
Escribimos los datos mínimos que nos da el ejercicio
Escribimos en la fila 9 distintos valores iniciando desde 2.000.000 hasta 30.000.000. En
la columna E escribimos valores entre 12 y 40. En ambos casos de forma
ascendente.
En la celda que intercepta la fila y la columna, usaremos la función
=-PAGO(F3;F5;F4) la cual calcula una cuota con el monto mínimo de préstamo
(2.000.000) a 12 meses que es el tiempo mínimo.
Seleccionamos la “tabla” que se formará con los datos escritos
Vamos a la pestaña de DATOS, seleccionamos ANÁLISIS DE HIPÓTESIS y por último
TABLA DE DATOS.
La celda de entrada (fila) será el monto mínimo y la celda de entrada (columna)
será el periodo mínimo. Estos serán las variables modificables dentro de la función
de PAGO
Marcamos todos los resultados que sean menores a 900.000
PUNTO 6:
En la hoja de INFORME, crearemos una nueva columna llamada DONACIÓN con la
siguiente función
=SI.CONJUNTO(DERECHA(C2;1)="a";50000;DERECHA(C2;1)="n";70000;DERECHA(C2;1)
="i";80000;IZQUIERDA(C3;1)="l";90000;VERDADERO;100000)
La cual indica que si al mirar el texto de la celda C2, en la posición 1 de derecha a
izquierda (es decir que termine) tiene la letra a, se debe arrojar 50000; en caso de
que haya una letra n, se debe arrojar 70000; en el caso de que sea una i, arrojará
80.000. La condición de IZQUIERDA indica que si en la posición 1 de izquierda a
derecha (es decir que inicie) esta la letra l, debe arrojar 90000. Si no cumple
ninguna de las condiciones anteriores aparecerá 100000.
Creamos una tabla dinámica
En filas irá el NOMBRE COMPLETO y la CIUDAD, en valores será la suma de
DONACIONES
Nos paramos sobre NOMBRE COMPLETO, click derecho -> FILTROS DE VALOR ->DIEZ
MEJORES
Pedimos que en Muestren los SUPERIORES - 3 - ELEMENTOS -SUMA DE DONACIÓN
Ahora nos aparecerán las personas de los 3 valores más altos asignados en
DONACIÓN
PUNTO 7:
Escribimos el criterio de que solo busque los INGENIEROS de la columna PROFESIÓN.
Lo que solicitamos que arroje es NOMBRE COMPLETO, PROFESIÓN, AHORRO.
Usamos la función VF (valor futuro) para calcular el monto que tendrá al final la
persona con una tasa de 1,3% mensual, en 24 meses, si deposita su nivel de ahorro y
adicionalmente cada mes consigna 100.000
PUNTO 8:
Escribimos los títulos de la tabla: PERIODO, SALDO INICIAL, INTERES, CUOTA, ABONO
A CAPITAL, SALDO FINAL.
En la pestaña INICIO seleccionamos la herramienta RELLENAR -> SERIES
Series en -> columnas porque queremos que ubique los periodos de arriba hacia
abajo.
Que incremente en 1
El límite es 60.
Para saber cual es el monto del préstamo, usaremos la función SUMA y sumará
todos los valores consignados en la hoja INFORME de la columna AHORRO.
El saldo inicial es igual al saldo final de la fila debidamente anterior.
Para el interés multiplicamos el saldo inicial de la fila por el 2% de interés (fijamos la
celda).
Para calcular la cuota usamos la fórmula =-PAGO con una tasa del 2%, a 60 meses,
con un saldo inicial del monto de ahorro (69.831.000).
Abono a capital sería la columna cuota menos la columna interés
Saldo final será la fila de saldo inicial menos el abono a capital
Ahora solo debemos estirar la fórmula en todas las filas hasta el período 60.
