Croiser des données avec comme synthèse des données alphanumériques
Croiser des données en fonction de deux critères placés en lignes et en colonnes revient à utiliser la fonction INDEX qui renvoie la valeur se trouvant à l'intersection Ligne/Colonne d'un tableau.
Scénario
Nous avons une liste de données avec des personnes (Pierre, Paul et Virginie) inscrites à une ou plusieurs formations (Access, Excel) et nous aimerons faire un tableau de synthèse pour savoir si une personne s'est inscrite à une ou plusieurs formations.
La difficulté dans notre exemple est de savoir de quelle ligne il s'agit puisque nous avons plusieurs données identiques en lignes (Pierre peut s'inscrire soit à la formation Excel soit à celle d'Access soit aux deux.
C'est le rôle que va jouer la formule SOMMEPROD en la combinant à la formule LIGNE.
Les données
La liste des données se trouve sur la plage A1:C6
En colonne A : Les formations(A2:A7)
En colonne B : Les noms (B2:B7)
En colonne C : Les données à croiser (C2:C7)
La lignes 1 contenant les étiquettes de colonnes
Pour la facilité de la compréhension, nous allons nommés les plages
planning est la plage des données (A1:C7)
planning_Training est la plage contenant les formations (A1:A7)
planning_Name est la plage contenant les noms (B1:B7)
Le tableau récapitulatif
Plage F3:H6
En ligne : Les noms (F4:F6)
En colonne: Les formations (G3:H3)
La plage des synthèses : (G4:H6) où l'on place la formule
La formule
Placée en $G$4:$H$6
=INDEX(planning ; SOMMEPROD( ($F4=planning_Name) * (G$3=planning_Training) * LIGNE(planning) ) ; 3)
La formule SOMMEPROD calcule la ligne (2ème argument de INDEX) qui répond au critères (pour la cellule G4) Pierre et Access et ce grâce à la combinaison avec LIGNE
SOMMEPROD( ($F4=planning_Name) * (G$3=planning_Training) * LIGNE(planning) )
Cette formule placée en G4 renverra 5 (N° de la ligne répondant aux critères Pierre et Access) ce qui revient à obtenir :
renvoie la matrice {1;2;3;4;5;6;7} ce qui nous donnera le numéro de la ligne contenant les deux critères (par exemple Pierre et Access)
La formule SOMMEPROD étape par étape
Les matrices de départ
=SOMMEPROD( ("Pierre"={"Nom";"Pierre";"Paul";"Virginie";"Pierre";"Paul";"Virginie"}) * ("Access"={"Formation";"Excel";"Excel";"Excel";"Access";"Access";"Access"}) * LIGNE( {1;2;3;4;5;6;7} ) )
Soit avec comme résultat
SOMMEPROD( ({FAUX;VRAI;FAUX;FAUX;VRAI;FAUX;FAUX}) * ({FAUX;FAUX;FAUX;FAUX;VRAI;VRAI;VRAI}) * {1;2;3;4;5;6;7} )
Donc résultat final VRAI * VRAI * 5 soit 1 * 1 * 5
Rendre la formule dynamique[B]
Idéalement, les plages nommées ne devraient pas contenir les étiquettes de colonnes et nous devrions donc rectifier le numéro de ligne trouvée en défalquant 1 (la formule SOMMEPROD avec LIGNE renvoyant le numéro de ligne de la feuille Excel et pas le numéro de ligne du tableau référencé dans la formule INDEX et de même si nous déplaçons la listes de données.
Nous allons donc ajouter une formule qui va nous permettre de rectifier la formule dynamiquement.
Cette formule est à ajouter derrière SOMMEPROD et fait donc partie du deuxième argument de la fonction INDIRECT
Cette formule renvoie le numéro de la première ligne de la plage (planning) soit 4 dans l'illustration ci-dessous
Ce qui signifie que si la formule SOMMEPROD renvoie la valeur 6 qui correspond à la ligne de la feuille Excel où se trouve la donnée cherchée (dans notre exemple Virgine et Access nous aurons 6 - 4 + 1 soit 3
Et enfin pour éviter un message d'erreur si une des personnes ne s'inscrit à rien nous ajouterons la formule SIERREUR (valable uniquement sur les versions 2007 et suivantes). Il y a lieu d'utiliser un autre test pour les versions antérieures à 2007.
=SIERREUR( INDEX(planning; SOMMEPROD( ($F4=planning_Name) * (G$3=planning_Training) * LIGNE(planning) ) - LIGNE(planning) + 1; 3);"")
L'image ci-dessous illustre les données déplacées de deux lignes et une colonne.
Croiser des données avec comme synthèse des données alphanumériques
Répondre avec citation
Partager