Sommaire
Pour créer un décompte en cours dans un tableau Excel, vous pouvez utiliser la fonction INDEX avec une référence structurée pour créer une plage en expansion. Dans l'exemple illustré, la formule en F5 est:
=(@Color)&" - "&SUM(--(INDEX((Color),1):(@Color)=(@Color)))
Une fois copiée dans la colonne, cette formule renverra un décompte en cours pour chaque couleur de la colonne Couleur.
Dans certaines versions d'Excel, il s'agit d'une formule matricielle et doit être saisie avec Ctrl + Maj + Entrée.
Explication
Au fond, cette formule utilise INDEX pour créer une référence en expansion comme celle-ci:
INDEX((Color),1):(@Color) // expanding range
Sur le côté gauche des deux-points (:), la fonction INDEX renvoie une référence à la première cellule de la colonne de colonne.
INDEX((Color),1) // first cell in color
Cela fonctionne car la fonction INDEX renvoie une référence à la première cellule, pas la valeur réelle. Sur le côté droit des deux points, nous obtenons une référence à la ligne actuelle de la colonne de couleur comme ceci:
(@Color) // current row of Color
Il s'agit de la syntaxe de référence structurée standard pour «cette ligne». Jointes par deux points, ces deux références créent une plage qui se développe au fur et à mesure que la formule est copiée dans le tableau. Donc, nous échangeons ces références dans la fonction SOMME, nous avons:
SUM(--(B5:B5=(@Color))) // first row SUM(--(B5:B11=(@Color))) // last row
Chacune des expressions ci-dessus génère un tableau de valeurs TRUE / FALSE, et le double négatif (-) est utilisé pour convertir ces valeurs en 1 et 0. Donc, dans la dernière rangée, nous nous retrouvons avec:
SUM((0;0;0;1;0;0;0;0;1;0;1)) // returns 3
Le reste de la formule concatène simplement la couleur de la ligne actuelle au nombre retourné par SUM:
=(@Color)&" - "&3 ="Gold"&" - "&3 ="Gold - 3"
Gamme en expansion simple?
Pourquoi ne pas utiliser une gamme extensible simple comme celle-ci?
SUM(--($B$5:B5=(@Color)))
Pour une raison quelconque, ce type de référence mixte est corrompu dans un tableau Excel lorsque des lignes sont ajoutées. L'utilisation d'INDEX avec une référence structurée résout le problème.
