Resumen
Para crear un recuento continuo en una tabla de Excel, puede usar la función INDICE con una referencia estructurada para crear un rango en expansión. En el ejemplo que se muestra, la fórmula en F5 es:
=(@Color)&" - "&SUM(--(INDEX((Color),1):(@Color)=(@Color)))
Cuando se copia en la columna, esta fórmula devolverá un recuento continuo para cada color en la columna Color.
En algunas versiones de Excel, esta es una fórmula de matriz y debe ingresarse con control + shift + enter.
Explicación
En el núcleo, esta fórmula utiliza INDICE para crear una referencia en expansión como esta:
INDEX((Color),1):(@Color) // expanding range
En el lado izquierdo de los dos puntos (:), la función INDICE devuelve una referencia a la primera celda de la columna de la columna.
INDEX((Color),1) // first cell in color
Esto funciona porque la función INDICE devuelve una referencia a la primera celda, no al valor real. En el lado derecho de los dos puntos, obtenemos una referencia a la fila actual de la columna de color como esta:
(@Color) // current row of Color
Esta es la sintaxis de referencia estructurada estándar para "esta fila". Junto con los dos puntos, estas dos referencias crean un rango que se expande a medida que la fórmula se copia en la tabla. Entonces, intercambiamos estas referencias en la función SUM, tenemos:
SUM(--(B5:B5=(@Color))) // first row SUM(--(B5:B11=(@Color))) // last row
Cada una de las expresiones anteriores genera una matriz de valores VERDADERO / FALSO, y el doble negativo (-) se usa para convertir estos valores en 1 y 0. Entonces, en la última fila, terminamos con:
SUM((0;0;0;1;0;0;0;0;1;0;1)) // returns 3
El resto de la fórmula simplemente concatena el color de la fila actual con el recuento devuelto por SUM:
=(@Color)&" - "&3 ="Gold"&" - "&3 ="Gold - 3"
¿Rango de expansión simple?
¿Por qué no utilizar un rango de expansión simple como este?
SUM(--($B$5:B5=(@Color)))
Por alguna razón, este tipo de referencia mixta se corrompe en una tabla de Excel a medida que se agregan filas. Usar INDEX con una referencia estructurada resuelve el problema.
