Resumo
Para criar uma contagem contínua em uma tabela do Excel, você pode usar a função INDEX com uma referência estruturada para criar um intervalo de expansão. No exemplo mostrado, a fórmula em F5 é:
=(@Color)&" - "&SUM(--(INDEX((Color),1):(@Color)=(@Color)))
Quando copiada para baixo na coluna, esta fórmula retornará uma contagem contínua para cada cor na coluna Cor.
Em algumas versões do Excel, esta é uma fórmula de matriz e deve ser inserida com control + shift + enter.
Explicação
Basicamente, esta fórmula usa INDEX para criar uma referência de expansão como esta:
INDEX((Color),1):(@Color) // expanding range
No lado esquerdo dos dois pontos (:), a função INDEX retorna uma referência à primeira célula na coluna da coluna.
INDEX((Color),1) // first cell in color
Isso funciona porque a função INDEX retorna uma referência à primeira célula, não o valor real. No lado direito dos dois pontos, obtemos uma referência à linha atual da coluna de cores como esta:
(@Color) // current row of Color
Esta é a sintaxe de referência estruturada padrão para "esta linha". Juntas com os dois pontos, essas duas referências criam um intervalo que se expande conforme a fórmula é copiada para baixo na tabela. Então, trocamos essas referências na função SUM, temos:
SUM(--(B5:B5=(@Color))) // first row SUM(--(B5:B11=(@Color))) // last row
Cada uma das expressões acima gera uma matriz de valores VERDADEIRO / FALSO, e o negativo duplo (-) é usado para converter esses valores em 1s e 0s. Então, na última linha, terminamos com:
SUM((0;0;0;1;0;0;0;0;1;0;1)) // returns 3
O resto da fórmula simplesmente concatena a cor da linha atual para a contagem retornada por SUM:
=(@Color)&" - "&3 ="Gold"&" - "&3 ="Gold - 3"
Faixa de expansão simples?
Por que não usar um intervalo de expansão simples como este?
SUM(--($B$5:B5=(@Color)))
Por alguma razão, esse tipo de referência mista é corrompido em uma Tabela do Excel à medida que as linhas são adicionadas. Usar INDEX com uma referência estruturada resolve o problema.
