Sommario
Per creare un conteggio parziale in una tabella Excel, è possibile utilizzare la funzione INDICE con un riferimento strutturato per creare un intervallo in espansione. Nell'esempio mostrato, la formula in F5 è:
=(@Color)&" - "&SUM(--(INDEX((Color),1):(@Color)=(@Color)))
Quando viene copiata nella colonna, questa formula restituirà un conteggio progressivo per ogni colore nella colonna Colore.
In alcune versioni di Excel, questa è una formula di matrice e deve essere inserita con CTRL + MAIUSC + INVIO.
Spiegazione
Al centro, questa formula utilizza INDICE per creare un riferimento in espansione come questo:
INDEX((Color),1):(@Color) // expanding range
Sul lato sinistro dei due punti (:), la funzione INDICE restituisce un riferimento alla prima cella nella colonna della colonna.
INDEX((Color),1) // first cell in color
Questo funziona perché la funzione INDICE restituisce un riferimento alla prima cella, non il valore effettivo. Sul lato destro dei due punti, otteniamo un riferimento alla riga corrente della colonna del colore in questo modo:
(@Color) // current row of Color
Questa è la sintassi di riferimento strutturato standard per "questa riga". Insieme ai due punti, questi due riferimenti creano un intervallo che si espande man mano che la formula viene copiata nella tabella. Quindi, scambiamo questi riferimenti nella funzione SUM, abbiamo:
SUM(--(B5:B5=(@Color))) // first row SUM(--(B5:B11=(@Color))) // last row
Ciascuna delle espressioni precedenti genera un array di valori VERO / FALSO e il doppio negativo (-) viene utilizzato per convertire questi valori in 1 e 0. Quindi, nell'ultima riga, finiamo con:
SUM((0;0;0;1;0;0;0;0;1;0;1)) // returns 3
Il resto della formula concatena semplicemente il colore dalla riga corrente al conteggio restituito da SUM:
=(@Color)&" - "&3 ="Gold"&" - "&3 ="Gold - 3"
Gamma in espansione semplice?
Perché non utilizzare una gamma in espansione semplice come questa?
SUM(--($B$5:B5=(@Color)))
Per qualche motivo, questo tipo di riferimento misto viene danneggiato in una tabella Excel quando vengono aggiunte righe. L'uso di INDICE con un riferimento strutturato risolve il problema.
