Selecciona un módulo y comienza a practicar. Cada ejercicio incluye contexto, datos, solución paso a paso, el porqué y atajos.
0 ejercicios explorados
Módulo 1 · Fundamentos de Excel
Dominar referencias y formato es el 50% de la eficiencia como analista.
Ejercicio 1.1 — Referencias absolutas vs. relativas
Básico
Contexto: Tienes una lista de 5 préstamos y el banco aplica una tasa de interés mensual única del 1.5% (celda G1). Necesitas calcular el interés mensual de cada uno sin que al arrastrar la fórmula se rompa la referencia a la tasa.
Celda
A
B
C
Fila 1
Cliente
Capital
Interés mensual
2
Juan Pérez
10,000
?
3
Ana Ruiz
25,000
?
4
Luis Díaz
7,500
?
5
María Soto
40,000
?
6
Pedro Gil
15,000
?
G1 = 1.5% (tasa mensual del banco)
¿Qué fórmula pondrías en C2 para arrastrar hacia abajo y calcular el interés mensual de cada cliente?
Fórmula
=B2*$G$1
Paso a paso
Posiciónate en la celda C2.
Escribe =B2*G1.
Selecciona G1 dentro de la fórmula y presiona F4 una vez para convertirla en $G$1.
Presiona Enter.
Selecciona C2 y haz doble clic en el cuadro de llenado (esquina inferior derecha) para copiar hacia abajo.
¿Por qué?
B2 es referencia relativa: al copiar baja a B3, B4, etc. Queremos eso porque cada fila tiene un capital diferente.
$G$1 es referencia absoluta: los signos $ "anclan" la celda. Al copiar, siempre apunta a G1 (la tasa común). Sin los $, al arrastrar se movería a G2, G3, etc. — celdas vacías, dando cero o error.
F4 cicla entre los 4 modos:G1 → $G$1 (ambos fijos) → G$1 (fila fija) → $G1 (columna fija) → G1.
Atajos relacionados
F4 Alternar referencia absolutaCtrl+Shift+↓ Seleccionar hasta el finalCtrl+D Rellenar hacia abajo
Ejercicio 1.2 — Formato de moneda y porcentaje
Básico
Contexto: Recibiste una base con valores en bruto (números sin formato). Tu gerente te pide presentar Capital en moneda (2 decimales) y Tasa en porcentaje (2 decimales) para la revisión de comité.
¿Cómo formateas el rango B2:B100 como moneda y el rango C2:C100 como porcentaje usando solo el teclado?
Paso a paso
Selecciona B2 y presiona Ctrl+Shift+↓ para llegar a B100.
Presiona Ctrl+Shift+4 (o $) → formato moneda.
Selecciona C2 y repite la selección.
Presiona Ctrl+Shift+5 (o %) → formato porcentaje.
Para ajustar decimales: Alt+H, luego 0 (aumentar) o 9 (reducir).
¿Por qué?
Formato ≠ valor: Excel guarda el número real (ej. 0.015) y lo muestra como 1.50%. Nunca multipliques por 100 manualmente: rompe fórmulas financieras como PAGO o TASA.
Si ves ##### en la celda, la columna es muy angosta. Doble clic en el borde del encabezado de columna para autoajustar, o Alt+H+O+I.
Atajos relacionados
Ctrl+Shift+1 Número (2 decimales)Ctrl+Shift+4 MonedaCtrl+Shift+5 PorcentajeCtrl+Shift+3 FechaCtrl+1 Abrir diálogo de formato
Ejercicio 1.3 — Filtros y orden por monto en mora
Básico
Contexto: Tienes una base de 500 clientes con columnas: Cliente, Producto, Saldo, Días mora. Necesitas mostrar solo los clientes con mora > 30 días, ordenados por saldo descendente, para priorizar gestión.
¿Cómo aplicas filtro y ordenamiento en menos de 30 segundos?
Paso a paso
Haz clic en cualquier celda del rango de datos.
Presiona Ctrl+Shift+L para activar filtros.
Clic en la flecha del encabezado Días mora → Filtros de número → Mayor que... → escribe 30.
Clic en la flecha del encabezado Saldo → Ordenar de mayor a menor.
Para exportar solo los visibles: copia el rango filtrado con Ctrl+C (Excel copia solo celdas visibles).
¿Por qué?
El filtro no borra datos, solo los oculta. Puedes quitarlo con Ctrl+Shift+L de nuevo. Ordenar con filtro activo solo reordena los visibles, por eso es la forma más segura cuando trabajas sobre una base compartida.
Al copiar un rango filtrado, Excel por defecto copia solo filas visibles. Si copiaste antes de filtrar y quieres replicar, usa Alt+; para seleccionar solo visibles explícitamente.
Atajos relacionados
Ctrl+Shift+L Activar/desactivar filtroAlt+↓ Abrir menú de filtroAlt+; Seleccionar solo visibles
Ejercicio 1.4 — Inmovilizar paneles y navegación en base grande
Básico
Contexto: Trabajas con una base de 2,000 clientes y 15 columnas. Al hacer scroll pierdes de vista los encabezados y la columna de Cliente.
¿Cómo fijas la fila 1 y la columna A para que siempre se vean al hacer scroll?
Paso a paso
Selecciona la celda B2 (una fila debajo y una columna a la derecha del punto de congelación).
Ve a pestaña Vista → Inmovilizar paneles → Inmovilizar paneles.
O con teclado: Alt+W+F+F.
Para navegar rápido: Ctrl+↓ salta al último dato; Ctrl+Inicio regresa a A1.
¿Por qué?
Excel congela todo lo que esté arriba y a la izquierda de la celda activa. Por eso seleccionas B2: fija fila 1 (arriba) y columna A (a la izquierda). Es el truco estándar.
Atajos de navegación
Ctrl+Inicio Ir a A1Ctrl+Fin Última celda con datosCtrl+↓↑→← Saltar al bordeCtrl+G Ir a... (celda específica)Ctrl+Re Pág / Av Pág Cambiar hoja
Módulo 2 · Funciones Básicas
Estas 5 funciones resuelven el 70% de los análisis diarios.
Ejercicio 2.1 — Totalizar cartera con SUMA
Básico
Contexto: Tu gerente quiere saber el saldo total de la cartera de créditos personales. Tienes los saldos en D2:D150.
¿Fórmula para totalizar el saldo y forma más rápida de insertarla?
Fórmula
=SUMA(D2:D150)
Paso a paso (atajo)
Selecciona la celda D151 (debajo del último dato).
Alt+= detecta el rango contiguo automáticamente. Es 5× más rápido que escribir la fórmula. Excel también muestra en la barra de estado (esquina inferior derecha) suma, promedio y conteo del rango seleccionado sin tener que escribir nada.
Selecciona D2:D150 y mira abajo a la derecha de Excel: verás Suma, Promedio, Recuento. Útil para validaciones rápidas sin insertar fórmula.
Alt+= AutosumaCtrl+Shift+↓ Extender selección
Ejercicio 2.2 — CONTAR.SI: clientes en mora
Básico
Contexto: En E2:E500 tienes los días de mora de cada crédito. El gerente pregunta: "¿Cuántos clientes tienen más de 60 días de mora?" (crítico para provisiones).
Fórmula para contar clientes con mora superior a 60 días.
Fórmula
=CONTAR.SI(E2:E500, ">60")
¿Por qué CONTAR.SI y no CONTAR?
CONTAR cuenta celdas con cualquier número. CONTARA cuenta celdas no vacías (texto o número). CONTAR.SI cuenta solo las que cumplen una condición. El criterio siempre va entre comillas cuando es comparación: ">60", "<=30", "=0", "Juan*".
Comodines:* = cualquier texto, ? = un solo carácter. Ej: =CONTAR.SI(A:A,"Juan*") cuenta todos los que empiezan con "Juan".
Variantes útiles
=CONTAR.SI(E2:E500, ">=90") // cartera vencida (provisión 100%)
=CONTAR.SI(F2:F500, "Aprobado") // estados exactos
=CONTAR.SI.CONJUNTO(E2:E500, ">30", F2:F500, "Personal") // dos condiciones
Ejercicio 2.3 — SUMAR.SI: exposición por producto
Intermedio
Contexto: Columna B = tipo de producto (Personal, Vehicular, Hipotecario). Columna D = saldo. Necesitas la exposición total en créditos Hipotecarios.
El rango del criterio y el rango a sumar deben tener el mismo tamaño. Si solo pasas un rango SUMAR.SI(D2:D500, ">10000"), Excel suma los valores del mismo rango que cumplen la condición.
Para múltiples criterios usa SUMAR.SI.CONJUNTO (cambia el orden: primero el rango a sumar).
Versión con 2 criterios
=SUMAR.SI.CONJUNTO(D2:D500, B2:B500, "Hipotecario", E2:E500, ">30")
// Exposición hipotecaria con mora > 30 días
A diferencia de SUMAR.SI, en la versión "CONJUNTO" el primer argumento es el rango a promediar, y después van pares (rango, criterio). Mismo orden que SUMAR.SI.CONJUNTO.
Si quieres el ticket promedio excluyendo castigados, agrega otro par: , F2:F500, "<>Castigado".
Ejercicio 2.5 — MAX, MIN y K.ESIMO.MAYOR
Intermedio
Contexto: Reporte ejecutivo: mostrar el crédito más grande, el más pequeño y el 3° más grande de la cartera (los TOP ayudan a ver concentración).
Tres fórmulas, una por métrica.
Fórmulas
=MAX(D2:D500) // crédito mayor
=MIN(D2:D500) // crédito menor
=K.ESIMO.MAYOR(D2:D500, 3) // 3° más grande (TOP 3)
=K.ESIMO.MENOR(D2:D500, 5) // 5° más pequeño
¿Por qué K.ESIMO.MAYOR?
Para análisis de concentración de cartera (ej. los 10 créditos más grandes representan el X% del total), K.ESIMO.MAYOR es insustituible. Combínalo con SUMA para el índice de concentración de los TOP N.
Concentración TOP 10:=SUMAPRODUCTO(K.ESIMO.MAYOR(D2:D500, FILA(INDIRECTO("1:10"))))/SUMA(D2:D500) te da qué % del total representan los 10 créditos más grandes.
En SI anidado, Excel evalúa las condiciones en orden. Si empiezas por C2>=600, un score de 800 entraría ahí (porque 800 ≥ 600) y nunca llegaría a la "A". Siempre evalúa de más estricto a menos estricto.
Cuando tengas más de 3 niveles, usa BUSCARV con tabla de rangos (verás en módulo 4). Es mucho más mantenible que SI anidados.
Ejercicio 3.3 — Y / O: política de aprobación multivariable
Intermedio
Contexto: Política: se aprueba si el cliente cumple TODAS estas condiciones:
Y(...) = todas las condiciones deben ser verdaderas (AND).
O(...) = al menos una (OR).
Ejemplo con O: rechazar si alguna condición de riesgo se activa:
=SI(O(C2<500, F2>90, D2<1000), "Rechazar", "Evaluar")
Contexto: Calculas ratio Deuda/Ingreso: =D2/E2. Pero algunos clientes no tienen ingreso registrado (E2 = 0 o vacío) y aparece #¡DIV/0!. Quieres que muestre "Sin datos" en esos casos.
Fórmula blindada.
Fórmula
=SI.ERROR(D2/E2, "Sin datos")
¿Por qué?
SI.ERROR captura cualquier error: #DIV/0!, #N/A, #VALOR!, #REF!, etc. Es la forma limpia de manejar datos sucios.
¡Ojo! Úsalo con criterio. Si ocultas todos los errores, puedes esconder bugs reales en tu modelo. Úsalo para casos esperados (BUSCARV sin coincidencia, divisiones por cero).
Combinado con BUSCARV: =SI.ERROR(BUSCARV(A2, Base, 3, FALSO), "No encontrado").
Módulo 4 · Búsqueda y Referencia
Cruzar bases es pan de cada día. Aquí las armas esenciales.
Ejercicio 4.1 — BUSCARV: traer datos del maestro de clientes
Intermedio
Contexto: Tienes dos hojas:
Clientes: A=DNI, B=Nombre, C=Ingreso, D=Score.
Solicitudes: quieres traer el score del cliente a partir del DNI.
Fórmula en Solicitudes!C2 sabiendo que A2 tiene el DNI.
matriz: dónde buscar. La columna de búsqueda debe ser la primera.
columna: índice de la columna a devolver (1 = A, 2 = B, 3 = C, 4 = D → Score).
FALSO: coincidencia exacta. Siempre usar FALSO salvo casos especiales.
Error común #N/A: significa que el valor no existe, o hay espacios/tipos diferentes (DNI como texto vs número). Solución: =SI.ERROR(BUSCARV(...), "No encontrado") o limpiar con ESPACIOS().
Atajos
F4 Fijar la matriz: Clientes!$A:$DShift+F3 Asistente de función
Ejercicio 4.2 — BUSCARV aproximado: asignar categoría por score
Intermedio
Contexto: Tienes una tabla en H2:I6 con los cortes:
Score mínimo (H)
Categoría (I)
0
D
600
C
700
B
750
A
Fórmula para asignar categoría a partir del score en C2, sin SI anidado.
Fórmula
=BUSCARV(C2, $H$2:$I$6, 2, VERDADERO)
¿Por qué VERDADERO aquí?
Con VERDADERO (coincidencia aproximada), BUSCARV busca el valor más cercano menor o igual. Requisitos:
La primera columna debe estar ordenada de menor a mayor.
Se usa para rangos (comisiones escalonadas, tasas por monto, categorías por score).
Un score de 720 encuentra 700 como el "más cercano menor o igual" y devuelve B. Limpio, mantenible, sin SI anidados.
Ejercicio 4.3 — BUSCARX: el reemplazo moderno
Intermedio
Contexto: Misma búsqueda del 4.1 (traer Score por DNI), pero usando BUSCARX (Excel 365 / 2021). Más flexible: busca en cualquier dirección y devuelve "No encontrado" sin SI.ERROR.
Versión en BUSCARX.
Fórmula
=BUSCARX(A2, Clientes!A:A, Clientes!D:D, "No encontrado")
Ventajas sobre BUSCARV
No necesitas contar columnas (pasas el rango a devolver directamente).
Puede buscar a la izquierda (BUSCARV no podía).
Manejo de "no encontrado" integrado (4° argumento).
Para devolver varias columnas a la vez: =BUSCARX(A2, Clientes!A:A, Clientes!B:D) devuelve Nombre, Ingreso y Score de un solo golpe (con derrame/spill).
Contexto: Versión sin BUSCARX. Necesitas traer el Ingreso del cliente cuya columna clave no es la primera (la columna DNI está a la derecha del Nombre).
COINCIDIR(A2, B:B, 0) devuelve la posición (número de fila) donde se encontró A2 en la columna B. El 0 es coincidencia exacta.
ÍNDICE(C:C, posición) devuelve el valor de la columna C en esa fila.
Flexible: funciona en cualquier dirección, con filas o columnas. Desde Excel 365, BUSCARX lo reemplaza, pero ÍNDICE+COINCIDIR sigue siendo universal.
Búsqueda por fila Y columna: =INDICE(tabla, COINCIDIR(cliente, filas, 0), COINCIDIR(campo, columnas, 0))
Ejercicio 4.5 — Eliminar duplicados y clientes únicos
Intermedio
Contexto: Base con operaciones repetidas por cliente (un cliente puede tener varios créditos). Necesitas una lista única de DNIs para enviar un reporte.
Dos formas: con función y con menú.
Opción 1 — UNICOS (Excel 365/2021)
=UNICOS(A2:A500)
Opción 2 — Menú (cualquier versión)
Selecciona el rango con duplicados.
Datos → Quitar duplicados.
Marca las columnas que definen el duplicado (solo DNI, por ejemplo).
Aceptar.
Para contar cuántos clientes únicos hay: =CONTARA(UNICOS(A2:A500)) o en versiones antiguas =SUMAPRODUCTO(1/CONTAR.SI(A2:A500, A2:A500)).
Módulo 5 · Funciones Financieras
El núcleo técnico del analista: calcular cuotas, valor presente, rendimientos.
Ejercicio 5.1 — PAGO: cuota mensual de un crédito
Intermedio
Contexto: El cliente solicita $20,000 a 24 meses con una tasa efectiva anual (TEA) del 18%. Calcula la cuota fija mensual.
La TEA (18%) es anual. Para cuota mensual necesitas la tasa efectiva mensual (TEM):
TEM = (1 + TEA)^(1/12) − 1
¡No divides entre 12! Eso sería una tasa nominal, no efectiva. Usar 18%/12 = 1.5% daría una cuota diferente (menor).
Estructura PAGO
=PAGO(tasa_por_periodo, nro_periodos, valor_presente, [valor_futuro], [tipo])
// Signo: capital como negativo → la cuota sale positiva (convención de flujo)
Si el dato ya viene como TEM directa (ej. 1.39%), usa: =PAGO(1.39%, 24, -20000). Ojo con si la tasa es nominal o efectiva (lee el contrato).
Ejercicio 5.2 — PAGOINT y PAGOPRIN: separar interés y capital
Avanzado
Contexto: Necesitas saber cuánto de la cuota del mes 1 y del mes 12 corresponde a interés vs amortización (capital). Mismo crédito del 5.1.
Fórmulas para cualquier cuota n.
Fórmulas
=PAGOINT(tem, n, nper, -capital) // interés del periodo n
=PAGOPRIN(tem, n, nper, -capital) // amortización del periodo n// Ejemplo con TEM ya calculada en F1
=PAGOINT($F$1, 1, 24, -20000) → interés mes 1
=PAGOPRIN($F$1, 1, 24, -20000) → capital mes 1
¿Por qué importa?
En créditos de cuota fija, el total es constante pero la composición cambia:
Al inicio: mucho interés, poco capital.
Al final: poco interés, mucho capital.
Esto es clave para explicarle al cliente por qué "aunque pago hace un año, mi deuda casi no baja". Y es la base para construir la tabla de amortización (ejercicio 6.2).
Validación:PAGO = PAGOINT + PAGOPRIN en todos los periodos.
Ejercicio 5.3 — VA: ¿cuánto puedo prestar?
Intermedio
Contexto: El cliente dice: "Puedo pagar hasta $500 al mes durante 36 meses". A una TEA del 20%, ¿cuál es el monto máximo que le puedes otorgar?
Fórmula de valor presente.
Fórmula
=VA((1+20%)^(1/12)-1, 36, -500)
// ≈ 13,682 pesos
VA = Valor Actual/Presente
VA responde: "¿Qué capital inicial equivale a estas cuotas futuras, descontadas a cierta tasa?" Es la operación inversa de PAGO.
Uso típico: cálculo de capacidad de endeudamiento basado en la cuota máxima que el cliente puede asumir.
Puedes obtener lo mismo despejando PAGO, pero VA es directo y más limpio. Úsalo cuando el dato fijo es la cuota (no el capital).
Ejercicio 5.4 — NPER: ¿en cuánto tiempo se paga?
Intermedio
Contexto: Cliente con saldo de $15,000 quiere reducir su cuota a $400. TEA 22%. ¿Cuántos meses tardaría en cancelar?
Fórmula de número de periodos.
Fórmula
=NPER((1+22%)^(1/12)-1, -400, 15000)
// ≈ 49.7 meses → redondear a 50
Signos y convención
En funciones financieras, los signos representan flujos de caja:
Positivo: dinero que recibes.
Negativo: dinero que pagas.
Para el banco que presta: capital +15000 (entra al modelo del cliente), cuotas -400 (salen). Si ambos son del mismo signo, obtendrás #¡NUM!.
Ejercicio 5.5 — TASA: encontrar la tasa oculta
Avanzado
Contexto: Un cliente te muestra otra entidad que le presta $10,000 con una cuota mensual de $520 a 24 meses. No declara tasa. ¿Cuál es la TEA implícita?
Fórmula para encontrar la tasa.
Fórmula
// TEM (tasa mensual)
=TASA(24, -520, 10000)
// ≈ 1.95%// Convertir a TEA
=(1+TASA(24,-520,10000))^12 - 1
// ≈ 26.05%
¿Por qué es útil?
En análisis comparativo, los clientes suelen traer propuestas con "cuota X" sin especificar tasa. TASA te permite desencriptar la tasa real y comparar con tu oferta en igualdad de condiciones.
También útil para validar contratos: si la entidad declara una tasa, pero recalculas con TASA y da distinto, hay algo raro (comisiones ocultas, capitalización distinta).
Si Excel devuelve #¡NUM!, agrega una tasa estimada como 4° argumento: =TASA(24, -520, 10000, 0, 0, 0.02).
Ejercicio 5.6 — VNA y TIR: evaluar rentabilidad de un portafolio
Avanzado
Contexto: Evaluar un proyecto de desembolso masivo: inversión inicial -100,000 (desembolsos) y recuperaciones mensuales durante 12 meses en celdas B2:B13. Tasa de descuento (costo de fondos) = 12% anual efectiva.
Calcular VAN (valor actual neto) y TIR.
Fórmulas
// VAN (descuenta solo los flujos futuros y suma inversión inicial)
= -100000 + VNA((1+12%)^(1/12)-1, B2:B13)
// TIR (mensual) — agrega inversión inicial al rango
=TIR(A1:B13) // A1 = -100000, B2:B13 = recuperaciones// TIR anual efectiva
=(1+TIR(A1:B13))^12 - 1
VNA vs. TIR
VNA > 0: el proyecto genera valor (rentabilidad > costo de fondos). Viable.
TIR: la tasa que hace VNA = 0. Si TIR > costo de fondos, proyecto viable.
VNA no incluye el periodo 0: la inversión inicial se suma aparte. Es un error clásico ponerla dentro del rango.
Para flujos irregulares en fechas (no mensuales exactos), usa VNA.NO.PER y TIR.NO.PER con columna de fechas.
Módulo 6 · Análisis de Crédito Aplicado
Casos integradores: scoring, ratios, tabla de amortización, capacidad de pago.
Ejercicio 6.1 — Ratio Deuda/Ingreso (DTI)
Intermedio
Contexto: Cliente con ingreso mensual D2 = 4,500 y deudas mensuales actuales (suma de cuotas de todos los créditos) E2 = 1,200. Política interna: aprobar si DTI < 40%.
Calcula el DTI y emite el veredicto.
Fórmulas
// DTI
=E2/D2
// Formatear como porcentaje (Ctrl+Shift+5)// Veredicto
=SI(E2/D2<40%, "Aprobar", "Rechazar")
// Con manejo de ingreso en cero
=SI.ERROR(SI(E2/D2<40%,"Aprobar","Rechazar"), "Sin ingreso")
Cuidado con el DTI "antes/después"
El DTI tradicional mide las cuotas actuales. Pero para aprobar un nuevo crédito, debes usar DTI proyectado:
Contexto: Construir el cronograma completo para un crédito: $10,000, 12 cuotas, TEA 24%. Necesitas columnas: Mes, Cuota, Interés, Amortización, Saldo.
Fórmulas para cada columna.
Setup en celdas fijas
B1: 10000 // Capital
B2: 24% // TEA
B3: 12 // Nro cuotas
B4: =(1+B2)^(1/12)-1 // TEM
B5: =PAGO(B4, B3, -B1) // Cuota fija
Estructura de la tabla (fila 8 en adelante)
A (Mes)
B (Cuota)
C (Interés)
D (Amort.)
E (Saldo)
0
—
—
—
=B1
1
=$B$5
=E8*$B$4
=B9-C9
=E8-D9
2
=$B$5
=E9*$B$4
=B10-C10
=E9-D10
...
arrastrar hasta mes 12
La lógica
Saldo mes 0 = capital.
Interés del mes = saldo anterior × TEM.
Cuota es fija (la calculamos con PAGO).
Amortización = cuota − interés (lo que realmente reduce deuda).
Saldo nuevo = saldo anterior − amortización.
Validación final: el saldo al final del mes 12 debe ser 0 (o un decimal mínimo por redondeo).
Alternativa con PAGOINT/PAGOPRIN:
Interés = =PAGOINT($B$4, A9, $B$3, -$B$1)
Amortización = =PAGOPRIN($B$4, A9, $B$3, -$B$1)
Más limpio, aunque menos educativo para entender los flujos.
Ejercicio 6.3 — Scoring simple multi-factor
Avanzado
Contexto: Construir un mini-score con 4 factores ponderados:
Antigüedad laboral (40%): 1 punto por cada año, máximo 10.
DTI (30%): 10 si <20%, 7 si <30%, 4 si <40%, 0 si ≥40%.
Historial de mora (20%): 10 si nunca, 5 si ≤30 días, 0 si >30.
Edad (10%): 10 si entre 25-55, 5 si no.
Puntaje final de 0 a 10. Aprobar si ≥ 7.
Fórmula del score total.
Setup por factor (celdas auxiliares)
// Antigüedad laboral en C2 (años)
F2: =MIN(C2, 10)
// DTI en D2
G2: =SI(D2<20%,10, SI(D2<30%,7, SI(D2<40%,4,0)))
// Historial mora en E2 (días máx histórico)
H2: =SI(E2=0,10, SI(E2<=30,5,0))
// Edad en B2
I2: =SI(Y(B2>=25, B2<=55), 10, 5)
// Score final (ponderado)
J2: =F2*0.4 + G2*0.3 + H2*0.2 + I2*0.1
// Veredicto
K2: =SI(J2>=7, "APROBAR", "RECHAZAR")
¿Por qué celdas auxiliares?
Separar cada factor en su propia celda tiene 3 ventajas:
Fácil depurar (si el score está raro, ves qué factor falla).
Fácil auditar para comité.
Puedes ajustar pesos sin tocar la lógica (poner los pesos en celdas y usar referencias).
Intentar meter todo en una sola fórmula con SI anidados gigantes es ilegible e imposible de mantener.
Ejercicio 6.4 — Provisión por días de mora
Intermedio
Contexto: Regla de provisión por clasificación:
0 días: Normal → 1%
1-30: Con problemas potenciales → 5%
31-60: Deficiente → 25%
61-120: Dudoso → 60%
>120: Pérdida → 100%
Mora en C2, saldo en D2.
Calcular provisión en pesos.
Opción 1 — BUSCARV con tabla de cortes (recomendado)
// Tabla de cortes en H2:I6// 0 1%// 1 5%// 31 25%// 61 60%// 121 100%
=D2 * BUSCARV(C2, $H$2:$I$6, 2, VERDADERO)
Ambos funcionan, pero BUSCARV con tabla es mejor porque:
Si la regulación cambia los %, editas la tabla, no la fórmula.
Más fácil de auditar por terceros (los porcentajes están visibles).
Escalable a más tramos sin tocar fórmula.
Ejercicio 6.5 — Edad en años desde fecha de nacimiento
Básico
Contexto: Formato de expediente: calcular la edad del cliente a partir de su fecha de nacimiento (B2). Política: no otorgar créditos si al terminar el plazo el cliente tendrá más de 75 años.
Edad actual y edad al final del crédito (36 meses).
Edad actual
=SIFECHA(B2, HOY(), "Y")
// o alternativa moderna
=ENTERO((HOY()-B2)/365.25)
Edad al final del crédito (+ 36 meses)
=SIFECHA(B2, FECHA.MES(HOY(), 36), "Y")
Validación de política
=SI(SIFECHA(B2, FECHA.MES(HOY(), 36), "Y") > 75, "RECHAZAR por edad", "OK")
SIFECHA es una función "oculta" (no aparece en autocompletar en algunas versiones). Segundo argumento: "Y" años, "M" meses, "D" días. HOY() se actualiza cada vez que abres el libro — útil pero cuidado si el expediente es histórico (usa fecha fija).
Módulo 7 · Tablas Dinámicas
Analizar la cartera por producto, región, segmento en segundos.
Ejercicio 7.1 — Cartera total por producto y región
Intermedio
Contexto: Base con 500 créditos. Columnas: Cliente, Producto, Región, Saldo, Días Mora. Generar un reporte que muestre saldo total cruzado por Producto (filas) × Región (columnas).
Crea la tabla dinámica y dale formato.
Paso a paso
Clic en cualquier celda de la base.
Atajo: Alt+N+V (o pestaña Insertar → Tabla dinámica).
Confirma el rango y elige "Nueva hoja de cálculo" → Aceptar.
En el panel de campos arrastra:
Producto → Filas
Región → Columnas
Saldo → Valores (asegúrate que sea Suma, no Conteo)
Clic derecho en un valor → Formato de número → Moneda.
Tips críticos
Si Excel interpreta un número como "Conteo", es porque hay celdas vacías o texto en la columna. Limpia primero.
Convierte la fuente en Tabla (Ctrl+T) antes de crear la dinámica: así al agregar filas nuevas se incluyen automáticamente sin redefinir el rango.
Para actualizar tras cambios: Alt+F5.
Segmentadores: pestaña Análisis de TD → Insertar segmentación. Te dan botones visuales para filtrar por Región, Segmento, etc. Perfecto para dashboards de comité.
Ejercicio 7.2 — % de cartera vencida por producto
Avanzado
Contexto: Mostrar para cada producto: saldo total, saldo vencido (mora > 30), y índice de morosidad (%).
Usando tabla dinámica con campo calculado.
Preparación
Agrega una columna auxiliar en la base: Saldo Vencido = =SI(E2>30, D2, 0).
Actualiza la tabla dinámica (Alt+F5) para que reconozca la nueva columna.
En filas → Producto; en valores → Saldo (Suma) y Saldo Vencido (Suma).
Agregar campo calculado: Análisis de TD → Campos, elementos → Campo calculado.
Nombre: Morosidad % Fórmula: ='Saldo Vencido' / Saldo
Formatea el nuevo campo como porcentaje.
¿Por qué campo calculado y no fórmula externa?
Un campo calculado se queda dentro de la tabla dinámica: sobrevive a actualizaciones, cambios de filas, reestructuraciones. Una fórmula externa junto a la TD se puede romper al cambiar filas.
Ejercicio 7.3 — Top 10 clientes por saldo
Intermedio
Contexto: Reporte de concentración: los 10 clientes con mayor exposición.
Usando tabla dinámica con filtro TOP N.
Paso a paso
TD con Cliente → Filas, Saldo → Valores (Suma).
Clic derecho sobre Cliente → Filtros → Diez mejores.
Selecciona "Superior" – "10" – "Elementos" – por "Suma de Saldo".
Ordena de mayor a menor: clic derecho en un valor → Ordenar → Mayor a menor.
Análisis rápido con Ctrl+Q: al seleccionar un rango, abre herramientas rápidas (totales, gráficos mini, formato condicional). Útil para exploración inicial.
Módulo 8 · Formato Condicional & Validación
Convertir tu hoja en una herramienta visual y a prueba de errores.
Ejercicio 8.1 — Semáforo de mora
Intermedio
Contexto: Columna E tiene días de mora. Quieres pintar automáticamente:
Verde si 0 días
Amarillo si 1-30
Naranja si 31-90
Rojo si > 90
Configura el formato condicional.
Paso a paso
Selecciona E2:E500.
Inicio → Formato condicional → Nueva regla → "Aplicar formato a las celdas que contengan".
Regla 1: "Es igual a" 0 → Formato verde.
Nueva regla: "Entre" 1 y 30 → Formato amarillo.
Nueva regla: "Entre" 31 y 90 → Formato naranja.
Nueva regla: "Mayor que" 90 → Formato rojo.
Verifica el orden en: Formato condicional → Administrar reglas.
Orden importa
Excel evalúa las reglas en orden. Si una regla se cumple, por defecto sigue evaluando las siguientes; si marcas "Detener si es verdad", se detiene. Cuando los rangos no se solapan (como aquí) no hay problema, pero en reglas complejas es clave.
Para pintar toda la fila (no solo la celda), usa "Usar una fórmula" con =$E2>90 y aplica al rango A2:F500. Los $ fijan la columna.
Ejercicio 8.2 — Validación de datos: lista desplegable
Básico
Contexto: Columna "Producto" debe aceptar solo: Personal, Vehicular, Hipotecario, Pyme. Quieres evitar que alguien escriba "Hipot." o "personal" en minúscula.
Configura la validación.
Paso a paso
Selecciona el rango que aceptará productos (ej. B2:B1000).
Datos → Validación de datos → Permitir: Lista.
Origen: Personal,Vehicular,Hipotecario,Pyme (separados por coma o punto y coma según la configuración regional).
En la pestaña Mensaje de error: Estilo Detener, título "Producto inválido", mensaje "Selecciona de la lista".
Lista dinámica: si la lista de productos cambiará, ponla en otra hoja como rango nombrado (Ctrl+F3) y en Origen escribe =ProductosValidos. Así puedes agregar/quitar sin tocar la validación.
Ejercicio 8.3 — Resaltar duplicados en DNIs
Intermedio
Contexto: Al consolidar expedientes, quieres detectar si un DNI aparece dos veces (posible duplicación de solicitud).
Elige el color (rojo claro por ejemplo) → Aceptar.
Versión con fórmula (más control)
// Resaltar solo a partir de la 2ª aparición
=CONTAR.SI($A$2:A2, A2)>1
// Contar cuántos duplicados hay
=SUMAPRODUCTO((CONTAR.SI(A2:A500, A2:A500)>1)*1) / 2
El truco $A$2:A2 (fila inferior relativa) permite marcar solo desde la segunda aparición en adelante, manteniendo la primera en blanco. Útil si quieres conservar la primera como "original".