Convertir IF con referencia a celda anterior en fórmula array con SCAN

Anita plantea un reto técnico interesante: tiene la fórmula =SI(B4=B3; D3+1; 1) que incrementa un contador cuando la celda actual coincide con la anterior, y lo reinicia a 1 cuando cambia. Funciona arrastrando, pero quiere una versión array que se desborde sola. La condición: sin LAMBDA.

El problema de fondo es que la fórmula original es auto-referencial (D3 depende de D2, que depende de D1...), y eso no se puede resolver con arrays simples.

Con SCAN + VSTACK + DROP (Leo): la solución más completa, aunque técnicamente usa LAMBDA (ya que SCAN la requiere). El truco está en construir un array booleano que compare cada elemento con el anterior usando VSTACK + DROP para desplazar:

``
=SCAN(1; B4# = VSTACK(""; DROP(B4#; -1)); LAMBDA(a; b; SI(b; a + 1; 1)))
`

VSTACK(""; DROP(B4#; -1)) crea una copia del array desplazada una posición hacia abajo (con un vacío al inicio), permitiendo la comparación "actual vs anterior" sin auto-referencia.

Con DESREF (enfoque no-array) (Nacho): usa DESREF para crear rangos dinámicos que se expandan con los datos, evitando la necesidad de LAMBDA:

`
=SI(B4# = B3:DESREF(B3; CONTARA(B:B) - 2; 0);
C3:DESREF(C4; CONTARA(C:C) - 2; 0) + 1; 1)
`

Anita bromea llamándoles "lambda-boys" y al final decide quedarse con la fórmula clásica arrastrada para mantener compatibilidad con versiones anteriores de Excel. Leo reconoce que su solución con SCAN "hace trampas" al incluir LAMBDA` indirectamente.

El problema: un contador que mira a la celda de arriba

Anita llega con una fórmula que funciona perfectamente arrastrada:

=SI(B4=B3; D3+1; 1)

Es un contador con reinicio: si el valor de esta fila coincide con el de la anterior, suma uno al contador previo; si cambia, vuelve a empezar en 1. Sirve para numerar repeticiones dentro de cada grupo, y aparece en mil sitios.

Lo que quiere Anita es la versión moderna: una sola fórmula que se desborde sola por toda la columna, sin arrastrar. Y pone una condición: sin LAMBDA.

Por qué no sale a la primera

El obstáculo no es de sintaxis, es conceptual. Esa fórmula es auto-referencial: el valor de D4 depende de D3, que depende de D2, que depende de D1. Cada resultado necesita el anterior ya calculado.

Las fórmulas matriciales normales no funcionan así. Calculan todas las celdas a la vez, no en cadena, así que no hay forma de que una celda del resultado consulte otra celda del mismo resultado.

Ese es el muro. Y hay dos maneras de rodearlo: o usas una función que sí sepa acumular en cadena, o eliminas la auto-referencia del planteamiento.

Solución 1: SCAN comparando cada fila con la anterior

Leo aporta la versión completa:

=SCAN(1; B4# = APILARV(""; TOMAR(B4#; FILAS(B4#) - 1)); LAMBDA(a; b; SI(b; a + 1; 1)))

Aquí hay dos ideas y ambas valen la pena.

La primera es cómo comparar cada elemento con el anterior sin auto-referencia. El truco está en el segundo argumento: se construye una copia del array desplazada una posición hacia abajo. Se coge el array quitándole el último elemento y se le pone un vacío por delante con APILARV. Al comparar el array original con esa copia desplazada, cada posición queda enfrentada a la que la precede.

El resultado es una columna de VERDADERO y FALSO que dice, para cada fila, si repite el valor de la anterior. Eso ya no es auto-referencial: es un cálculo normal entre dos matrices.

En el hilo original esa parte se escribe con EXCLUIR y un desplazamiento negativo, que quita elementos por el final. TOMAR con el total de filas menos una hace exactamente lo mismo y se lee igual de bien.

La segunda idea es SCAN. Es la función pensada precisamente para acumular en cadena: arranca con un valor inicial — aquí el 1 — y recorre el array llevándose el resultado anterior en la variable a y el elemento actual en b. La lógica de dentro es literalmente la fórmula original de Anita: si repite, suma uno; si no, reinicia a 1.

Leo reconoce en el hilo que su solución "hace trampas", porque SCAN exige una LAMBDA y la condición era no usarla. Es verdad, y aun así es la solución correcta: SCAN es la respuesta natural a cualquier cálculo que dependa del resultado anterior.

Solución 2: rangos que crecen solos con DESREF

Nacho ofrece la alternativa que sí respeta la restricción, sin LAMBDA por ninguna parte:

=SI(B4# = B3:DESREF(B3; CONTARA(B:B) - 2; 0);
    C3:DESREF(C4; CONTARA(C:C) - 2; 0) + 1; 1)

La idea es distinta: en vez de desplazar el array, se construyen rangos que empiezan una fila más arriba. DESREF calcula el final del rango a partir de cuántas celdas hay con datos, así que el rango se estira solo cuando llegan filas nuevas.

Es la versión que funciona en Excel antiguo, donde no hay SCAN ni LAMBDA. A cambio, DESREF es una función volátil: se recalcula cada vez que cambia cualquier cosa del libro, no solo lo que le afecta. En una hoja pequeña da igual; en un libro grande y lleno de fórmulas volátiles, se nota.

El desenlace, que también enseña

Anita bromea llamando "lambda-boys" a los que le proponen matrices dinámicas, y al final se queda con la fórmula clásica arrastrada para mantener la compatibilidad con versiones anteriores de Excel.

Y es una decisión perfectamente razonable. La fórmula original es corta, la entiende cualquiera que abra el fichero y funciona en cualquier versión. Si el libro lo va a usar gente con Excel antiguo, la solución elegante que solo corre en las versiones nuevas no es una solución, es un problema aplazado.

Conviene tenerlo presente: el mejor Excel no siempre es el más moderno, es el que va a poder abrir quien lo reciba.

Funciones clave

  • SCAN — acumula a lo largo de un array llevándose el resultado anterior. La herramienta correcta para cualquier cálculo en cadena.
  • APILARV — apila matrices en vertical. Aquí, para meter un vacío al principio y provocar el desplazamiento.
  • TOMAR y EXCLUIR — se quedan con, o descartan, elementos de los bordes de una matriz. Con ellas se construye la copia desplazada.
  • El operador de derrame — referencia el resultado dinámico completo sin fijar su tamaño.
  • DESREF — construye rangos calculados. Funciona en cualquier versión, pero es volátil.
  • CONTARA — cuenta celdas no vacías. Es lo que hace crecer el rango de DESREF.

Conclusión

Este caso deja un patrón muy reutilizable: para comparar cada elemento con el anterior, construye una copia del array desplazada una posición y compara las dos. Se resuelve así el problema sin tener que recurrir a la auto-referencia, y sirve para detectar cambios de grupo, calcular diferencias o marcar repeticiones.

Y deja la regla de decisión: si el cálculo necesita el resultado anterior, SCAN; si además necesita correr en Excel antiguo, la fórmula arrastrada de toda la vida sigue siendo la respuesta correcta.

Discusiones como esta, donde la solución más brillante no es la que acaba en el fichero, son de las más honestas de la comunidad de InflueXcel.

Más casos con estas funciones

Más contenido de Excel en InflueXcel