← Volver

Excel — Ejercicios Avanzados & Complejos

Matrices Dinámicas Complejas

1) Filtrar clientes con compras mayores al promedio global

=FILTRAR(A2:D200, D2:D200 > PROMEDIO(D2:D200))

2) Crear ranking dinámico sin columnas auxiliares

=ORDENAR(
  HSTACK(A2:A200, D2:D200),
  2, FALSE
)

3) Top 5 productos usando TAKE()

=TAKE(ORDENAR(A2:D200, 4, FALSE), 5)

4) Crear una tabla resumen por categoría sin tablas dinámicas

=UNIQUE(CHOOSECOLS(A2:D500, 2))
Luego sumar: 
=MAP(
  UNIQUE(B2:B500),
  LAMBDA(c, SUMAR.SI(B2:B500, c, D2:D500))
)

Funciones Avanzadas Combinadas

1) Detectar duplicados avanzados con COUNTIFS + FILTER

=FILTRAR(A2:D200, CONTAR.SI(A2:A200, A2:A200) > 1)

2) Buscar el 2do valor más alto por categoría

=LAMBDA(c,
  K.ESIMO.MAYOR(FILTRAR(D2:D200, B2:B200=c), 2)
)

3) Buscar a la izquierda sin INDICE/COINCIDIR

=XLOOKUP(D1, C2:C200, A2:A200)

4) Unir nombre + categoría + ventas con TEXTJOIN dinámico

=TEXTJOIN(" | ", TRUE, A2, B2, C2)

LET, LAMBDA & Fórmulas Personalizadas

1) Función personalizada de margen

=LET(
  costo, B2,
  venta, C2,
  (venta - costo) / venta
)

2) LAMBDA para limpiar cadenas de texto

=LAMBDA(txt, LIMPIAR(SUSTITUIR(txt, " ", "")))

3) Crear un agregador personalizado (SUMA filtrada dinámica)

=LAMBDA(rango, criterio,
  SUMA(FILTRAR(rango, rango > criterio))
)

4) Conversión masiva de divisas con LET + MAP

=LET(
  valor, C2:C200,
  tasa, 925,
  MAP(valor, LAMBDA(x, x*tasa))
)

Finanzas y Modelos Avanzados

1) Tasa interna de retorno no lineal

=TIR.NO.LINEAL(A2:A60, B2:B60)

2) Amortización de un crédito con matriz dinámica

=LET(
  n, 360,
  tasa, 0.015/12,
  monto, 15000000,
  pago, PAGO(tasa, n, -monto),
  SEQUENCE(n,1,1,1)*0 + pago
)

3) Variación porcentual año a año sin tablas dinámicas

=MAP(D2:D200, LAMBDA(x, (x - OFFSET(x,-12,0))/OFFSET(x,-12,0)))

4) Modelo de proyección trimestral automática

=LET(
  base, D2:D200,
  growth, 1.08,
  MAP(base, LAMBDA(x, x*growth))
)

Power Query en Excel (consultas avanzadas)

1) Unir múltiples archivos automáticamente

= Folder.Files("C:\\Ventas")  
|> Table.Combine  
|> Table.TransformColumnTypes(...)

2) Unpivot + Merge en un solo paso (M Code)

= Table.NestedJoin(
    Table.UnpivotOtherColumns(Source, {"Producto"}, "Mes", "Valor"),
    "Producto",
    Productos,
    "ID",
    "Detalle",
    JoinKind.LeftOuter
)

3) Calcular columnas dinámicas con condiciones complejas

= Table.AddColumn(Source, "Estado",
    each if [Monto] > 5000 then "Premium"
         else if [Monto] > 2000 then "Normal"
         else "Bajo"
)

4) Transformación masiva de fechas: año fiscal

= Table.AddColumn(Source, "Año Fiscal",
    each if Date.Month([Fecha])>=7 
         then Date.Year([Fecha])+1 
         else Date.Year([Fecha])
)

Proyectos Complejos:

1) Dashboard de Ventas sin Tablas Dinámicas (full formulas)

Elementos usados:
- FILTRAR
- ORDENAR
- SEQUENCE
- SUMAR.SI.CONJUNTO
- TAKE / DROP
- Controladores dinámicos con VALIDACIÓN DE DATOS

2) Sistema de stock con alertas automáticas avanzadas

Acciones:
- Formato condicional con fórmula:
  =C2 <= D2
  
- Lista de reposición:
  =FILTRAR(A2:D200, C2:C200 <= D2:D200)

3) Sistema de liquidación de sueldos dinámico

=LET(
  salario, C2,
  hsExtra, D2*1.5,
  bono, SI(E2="A", 15000, 0),
  salario + hsExtra + bono
)

4) Motor de cálculo avanzado para Pricing

=LAMBDA(costo, margen,
  LET(
    precio, costo*(1+margen),
    redondeo, REDONDEAR(precio, -2),
    redondeo
  )
)
whatsapp