Formule générique
=SUMPRODUCT(data*(headers=A1))
Sommaire
Pour additionner les valeurs dans les colonnes en faisant correspondre les en-têtes de colonne correspondants, vous pouvez utiliser une formule basée sur la fonction SUMPRODUCT. Dans l'exemple illustré, la formule en J5 est:
=SUMPRODUCT(data*(LEFT(headers)=J4))
où "data" est la plage nommée B5: G14 et "headers" est la plage nommée B4: G4.
La formule additionne les colonnes dont les en-têtes commencent par "a" et renvoie 201.
Explication
À la base, cette formule repose sur la fonction SOMMEPROD pour additionner les valeurs dans les colonnes correspondantes dans la plage nommée «données» C5: G14. Si toutes les données étaient fournies à SUMPRODUCT dans une seule plage, le résultat serait la somme de toutes les valeurs de la plage:
=SUMPRODUCT(data) // all data, returns 387
Pour appliquer un filtre en faisant correspondre les en-têtes de colonne - colonnes dont les en-têtes commencent par "A" - nous utilisons la fonction GAUCHE comme ceci:
LEFT(headers)=J4) // must begin with "a"
Cette expression renvoie TRUE si un en-tête de colonne commence par "a", et FALSE sinon. Le résultat est un tableau:
(TRUE,TRUE,FALSE,FALSE,TRUE,FALSE)
Vous pouvez voir que les valeurs 1, 2 et 5 correspondent à des colonnes commençant par "a".
Dans SUMPRODUCT, ce tableau est multiplié par "data". En raison de la diffusion, le résultat est un tableau à deux dimensions comme celui-ci:
(8,10,0,0,7,0;9,10,0,0,10,0;8,6,0,0,6,0;7,6,0,0,6,0;8,6,0,0,6,0;10,11,0,0,7,0;7,8,0,0,8,0;2,3,0,0,3,0;3,4,0,0,4,0;7,7,0,0,4,0)
Si nous visualisons ce tableau dans une table, il est facile de voir que seules les valeurs des colonnes commençant par "a" ont survécu à l'opération, toutes les autres colonnes sont nulles. En d'autres termes, le filtre conserve les valeurs d'intérêt et «annule» le reste:
| A001 | A002 | B001 | B002 | A003 | B003 |
|---|---|---|---|---|---|
| 8 | dix | 0 | 0 | sept | 0 |
| 9 | dix | 0 | 0 | dix | 0 |
| 8 | 6 | 0 | 0 | 6 | 0 |
| sept | 6 | 0 | 0 | 6 | 0 |
| 8 | 6 | 0 | 0 | 6 | 0 |
| dix | 11 | 0 | 0 | sept | 0 |
| sept | 8 | 0 | 0 | 8 | 0 |
| 2 | 3 | 0 | 0 | 3 | 0 |
| 3 | 4 | 0 | 0 | 4 | 0 |
| sept | sept | 0 | 0 | 4 | 0 |
Avec un seul tableau à traiter, SUMPRODUCT renvoie la somme de toutes les valeurs, 201.
Somme par correspondance exacte
L'exemple ci-dessus montre comment additionner les colonnes commençant par un ou plusieurs caractères spécifiques. Pour additionner la colonne en fonction d'une correspondance exacte, vous pouvez utiliser une formule plus simple comme celle-ci:
=SUMPRODUCT(data*(headers=J4))
