Exceller

Somme des colonnes et des lignes correspondantes

Sum Matching Columns

Formule Excel : additionner les colonnes et les lignes correspondantesFormule générique |_+_| Sommaire

Pour additionner les valeurs dans les colonnes et les lignes correspondantes, vous pouvez utiliser le Fonction SOMMEPROD . Dans l'exemple illustré, la formule de J6 est :



= SUMPRODUCT (data*(range1=criteria1)*(range2=criteria2))

Les données (C5:G14), jours (B5:B14), et codes (C4:G4) sont plages nommées .

Explication

La fonction SOMMEPROD peut gérer tableaux nativement, sans nécessiter d'entrée de décalage de contrôle.





Dans ce cas, nous multiplions toutes les valeurs des données de plage nommées par deux expressions qui filtrent les valeurs sans intérêt. La première expression applique un filtre basé sur des codes :

excel pourcentage de cellules avec valeur
 
= SUMPRODUCT (data*(codes=J4)*(days=J5))

Étant donné que J4 contient 'A002', l'expression crée un tableau de valeurs VRAI FAUX comme ceci :



 
(codes=J4)

La deuxième expression filtre le jour :

comment espacer dans Excel
 
{FALSE,TRUE,FALSE,FALSE,FALSE}

Étant donné que J4 contient 'Wed', l'expression crée un tableau de valeurs VRAI FAUX comme ceci :

 
(days=J5)

Dans Excel, les valeurs VRAI FAUX sont automatiquement forcées aux valeurs 1 et 0 par toute opération mathématique, de sorte que l'opération de multiplication force les tableaux ci-dessus à des uns et des zéros et crée un tableau 2D avec les mêmes dimensions que les données d'origine. Le processus peut être visualisé comme indiqué ci-dessous :

Multiplication de tableau dans SUMPRODUCT

Enfin, SUMPRODUCT renvoie la somme de tous les éléments du tableau final, 9.

Compter au lieu de sommer

Si vous souhaitez compter les valeurs correspondantes au lieu de les additionner, vous pouvez raccourcir la formule à :

formule de remboursement des prêts pour les prêts à versement
 
{FALSEFALSETRUEFALSEFALSEFALSEFALSETRUEFALSEFALSE}

Notez que ce nombre inclura les cellules vides.

Remarques

  1. Bien que l'exemple ne montre qu'une seule colonne correspondante, cette formule additionnera correctement plusieurs colonnes correspondantes.
  2. Si vous avez seulement besoin de faire correspondre des colonnes (pas des lignes), vous pouvez utiliser un formule comme ça .
  3. Pour ne faire correspondre que les lignes, vous pouvez utiliser le Fonction COUNTIFS .
Auteur Dave Bruns


^