Le SUMPRODUCT tout-puissant pour apprendre les fonctions Excel !

Dans l'article précédent « L'AGRÉGAT magique de l'apprentissage des fonctions Excel, un peut en réalité valoir 19 ! 》, nous avons découvert une puissante fonction statistique. Aujourd'hui, nous allons parler de la fonction SUMPRODUCT. La fonction sumproduct peut obtenir la somme des produits de deux colonnes de données. Cependant, il y a beaucoup de choses auxquelles il faut prêter attention lors de l'utilisation de la fonction, surtout après avoir utilisé des valeurs logiques. ​​et les tableaux. Faisons-le ensemble.

1. L'essence de SUMPRODUCT

La fonction SUMPRODUCT est vraiment très simple. Elle consiste à obtenir la somme des produits de deux colonnes de données. de la fonction :

L'image ci-dessus est un tableau très simple Pour calculer le prix total, vous calculez généralement le prix unitaire * la quantité puis le résumez. Le résultat est affiché en D8. Si la fonction SUMPRODUCT est utilisée, le prix total peut être calculé directement en utilisant le prix unitaire et la quantité. La formule 1 est : =SUMPRODUCT(B2:B7,C2:C7), et le résultat est affiché dans D9.

Dans cette formule, deux paramètres sont utilisés, à savoir la zone de prix unitaire (B2:B7) et la zone de quantité (C2:C7). La fonction est de combiner le premier paramètre (prix unitaire) avec. le deuxième paramètre (Les données dans la quantité) sont multipliés ensemble puis additionnés.

2. Causes de la plupart des erreurs

De nombreux amis obtiennent souvent des valeurs d'erreur lors de l'utilisation de cette fonction. La plupart d'entre elles sont dues à une sélection de taille de zone incohérente. Par exemple, dans le cas suivant, le premier paramètre est. 7 cellules et le deuxième paramètre n'a que 6 cellules :

Lorsque vous utilisez la fonction SUMPRODUCT, vous devez vous assurer que la taille de la zone de chaque paramètre est la même, mais de nombreux amis ne l'ont pas remarqué.

3. Une autre façon d'écrire courante, la virgule se transforme en signe de multiplication (*)

Pour cet exemple, il existe une autre façon d'écrire qui est plus courante. La formule est la suivante : =SUMPRODUCT(B2 : B7*C2 :C7)

Vous pouvez voir que le résultat du calcul est cohérent avec =SUMPRODUCT(B2:B7,C2:C7).

Les résultats cohérents ont conduit à une question qui laisse de nombreux amis intrigués : quelle est la différence entre les deux ?

4. La différence entre la virgule et le signe de multiplication (*)

Bien que la virgule dans la première formule ne soit transformée qu'en signe de multiplication (*), la signification de la formule a changé. La première formule (SUMPRODUCT(B2:B7,C2:C7)) a deux paramètres, tandis que la deuxième formule (B2:B7*C2:C7) a un paramètre. (Pour déterminer le nombre de paramètres, vous devez voir s'ils sont séparés par des virgules.) Dans la première formule, l'étape de multiplication de deux aires est complétée par une fonction. La fonction fait deux choses. Tout d'abord, laissez les deux aires. Les données correspondantes sont multipliées, puis les produits sont ajoutés. Dans la deuxième formule, la multiplication de deux aires se fait par calcul matriciel, et la fonction ne fait qu'une seule chose, c'est-à-dire additionner les valeurs des produits.

Quel impact a le changement de sens ?

Regardons cela à travers un exemple :

Dans la formule ci-dessus, une virgule (,) est utilisée, et il y a deux paramètres indépendants. La fonction SUMPRODUCT multiplie d'abord les deux ensembles de données de manière correspondante. Lors de la multiplication, elle vérifie les données et traite les données non numériques comme 0, puis ajoute les produits. Par conséquent, B1 « Prix unitaire » et C1 « Quantité » seront traités comme 0 et la formule pourra obtenir des résultats corrects.

Quand on remplace la virgule par un *, le résultat de la formule est faux. Pourquoi? La fonction SUMPRODUCT est uniquement responsable de l'ajout des produits à ce moment-là. Les paramètres B2:B7*C2:C7 sont des opérations de multiplication de tableau. Étant donné que la zone de calcul contient du texte (le texte ne peut pas être multiplié), il existe une valeur d'erreur dans le résultat du calcul de ce tableau. Sélectionnez "B2:B7*C2:C7" dans la formule et appuyez sur F9 pour afficher le résultat de l'opération de B2:B7*C2:C7 :

Vous pouvez voir que le premier résultat de l'opération (prix unitaire * quantité) est la valeur d'erreur. Ensuite, SUMPRODUCT additionne les données contenant des valeurs d'erreur et le résultat doit être une erreur.

5. Points clés de l'utilisation du signe de multiplication (*) dans SUMPRODUCT

Le contenu ci-dessus a deux significations :

Premièrement, l'utilisation de virgules et l'utilisation de * ont parfois les mêmes résultats, mais les significations sont complètement différentes. J'espère que tout le monde pourra comprendre.

Deuxièmement, lorsque vous utilisez le signe de multiplication (*) dans la fonction SUMPRODUCT, vous devez faire attention à deux points : premièrement, il ne peut pas y avoir de contenu non calculé, tel que du texte ; deuxièmement, si deux ou plusieurs ensembles de tableaux sont multipliés, le taille de la zone de données cohérente. Lorsque vous utilisez des virgules, il vous suffit de vous assurer que la taille de la zone de données est cohérente.

6. En fait, le signe de multiplication apporte également de plus grands avantages

Nous remplaçons la virgule de la fonction SUMPRODUCT et les données avant et après le signe de multiplication par A et B, exprimés par SUMPRODUCT (A, B) et SOMMEPRODUIT (A *B). Lorsqu'il s'agit d'une virgule, A et B doivent être tous deux des valeurs numériques ou des tableaux en même temps. L'un ne peut pas être une valeur numérique et l'autre est un tableau. Lorsqu'il s'agit d'un signe de multiplication, A et B peuvent être tous deux numériques. des valeurs ou des tableaux en même temps, ou l'un peut être une valeur numérique et l'autre un tableau.

C'est-à-dire :

virgule,		signe multiplicateur*
SUMPRODUCT (C1, B1)	√	SUMPRODUCT(C1*B1)	√
SUMPRODUCT(C1:C9,D1:D9)	√	SUMPRODUCT（C1:C9*D1:D9）	√
SUMPRODUCT（C1:D 9,F1:G9）	√	SUMPRODUCT（C1:D9*F1:G9）	√
SUMPRODUCT （C1:C9,D1）	×	SUMPRODUCT（C1:C9*D1）	√
SUMPRODUCT（C1:D9,F1）	×	SUMPRODUCT（C1:D9*F1 ）	√

Par conséquent, l'application de la fonction SUMPRODUCT est étendue avec le signe de multiplication. Ce que vous êtes sur le point de voir ci-dessous, ce sont des applications de la fonction SUMPRODUCT utilisant le signe de multiplication.

7. Comprenez-vous ces formules SUMPRODUCT ?

Si vous comprenez le contenu ci-dessus, cela signifie que l'utilisation de base de cette fonction ne pose aucun problème. Mais il est très probable que vous ne compreniez toujours pas de nombreuses formules SUMPRODUCT, comme celle-ci :

Il s'agit en fait d'un problème de comptage conditionnel. Regardez à nouveau celle-ci :

C'est un multi-. problème de sommation de conditions. Et ceci :

Dans ce tableau, presque toutes sortes de problèmes statistiques peuvent être résolus à l'aide de la fonction SUMPRODUCT, je ne donnerai donc pas d'exemples un par un. Il n’y a peut-être pas beaucoup d’amis capables de comprendre uniquement les trois formules énumérées ci-dessus.

Pourquoi est-ce que je comprends comment utiliser la fonction SUMPRODUCT mais je n'arrive toujours pas à comprendre ces formules, et encore moins à les utiliser pour résoudre des problèmes moi-même ? La raison est que vous ne comprenez pas ces deux points de connaissance : les valeurs logiques et les tableaux.

8. Apprenez-en un peu sur les valeurs logiques et les tableaux

Parlons d'abord des valeurs logiques. Il n'y a que deux valeurs logiques, VRAI et FAUX. Lorsque nous effectuons une sorte de comparaison ou de jugement dans une formule, une valeur logique sera générée. Prenons SUMPRODUCT((I2:I22="Zhang San")*1) comme exemple, où (I2:I22="Zhang San" ) ) est un jugement. Normalement, nous faisons des jugements basés sur les cellules. I2 = "Zhang San" signifie juger si le contenu de la cellule I2 est "Zhang San". Si c'est le cas, cela deviendra VRAI, sinon cela deviendra FAUX. Lorsque nous utilisons une zone pour juger, nous obtenons un ensemble de données, qui est en fait un tableau. Par exemple, I2:I22="Zhang San" obtiendra un ensemble de valeurs logiques. Vous pouvez utiliser F9 pour voir les résultats du calcul :

Si vous êtes intéressé, vous pouvez comparer le vendeur et la valeur du résultat un par un. un, et vous constaterez que tous les correspondants de Zhang San sont VRAIS.

Étant donné que les valeurs logiques ne peuvent pas être additionnées directement, elles doivent être converties en nombres. La méthode de conversion consiste à effectuer toute opération telle que l'addition, la soustraction, la multiplication ou la division sur les valeurs logiques. Dans cette formule, *1 a cet effet. Vous pouvez voir l'effet :

Après l'opération *1, VRAI devient 1 et FAUX devient 0. Ne me demandez pas pourquoi, c'est ainsi qu'Excel le stipule, et c'est la relation correspondante entre les valeurs logiques et les nombres.

D'accord, nous obtenons d'abord un ensemble de valeurs logiques, puis utilisons *1 pour les transformer en un ensemble de nombres, puis les résumons pour atteindre l'objectif de compter en fonction de conditions. Comprenez-vous la formule =SUMPRODUCT((I2:I22="Zhang San")*1) maintenant ?

Maintenant, nous comprenons les valeurs logiques et comprenons le premier principe des opérations sur les tableaux : lorsqu'un groupe de nombres est calculé avec un nombre, chaque donnée du groupe de nombres est calculée avec ce nombre. C'est ainsi qu'il est calculé dans la formule à l'instant.

9. Peu importe la complexité de la formule SUMPRODUCT, vous pouvez tout comprendre !

Jetons un coup d'œil à la deuxième formule : =SUMPRODUCT((MONTH(F2:F22)=3)*(H2:H22="二分店")*(G2:G22="衬衫")*J2:J22).

Ne regardez pas la longueur de cette formule, c'est en fait très simple de la déchiffrer en utilisant les connaissances que nous venons d'apprendre. La formule n'a toujours qu'un seul paramètre, mais ce paramètre est composé de quatre tableaux dont trois sont des valeurs logiques, à savoir (MONTH(F2:F22)=3), (H2:H22="Second Branch") et (G2: G22="chemise"). Ces trois ensembles de valeurs logiques complètent trois jugements et correspondent à trois conditions : mois = 3, magasin = deuxième succursale et nom = chemise. Le contenu spécifique est comme indiqué sur l'image :

semble densément emballé, mais après multiplication, il devient un tas de 1 et de 0. Le résultat est comme ceci :

Le résultat du produit n'est que de deux 1. , en fait, correspond aux deux données des chemises de la deuxième branche en mars. La formule à ce moment devient un ensemble de valeurs logiques (déjà 0 et 1) multipliées par un ensemble de données (quantité), puis la fonction complète la somme.

Dans cet exemple, vous devez faire attention au deuxième principe du calcul de tableau : lors du calcul de plusieurs (dont deux) tableaux (dans ce cas, quatre tableaux), les données à la position correspondante dans le tableau sont calculées, et le le tableau doit contenir Le nombre de données est le même.

Maintenant, nous avons compris les deux principes de calcul d'un tableau. Donnons un exemple simple pour illustrer :

Lorsqu'un tableau (A1:A9) est multiplié par une donnée (B1), quelle est la différence entre ce groupe de des nombres ? Multiplier ces données ;

Lorsque deux tableaux (A1:A9 et B1:B9) sont multipliés, les données aux positions correspondantes du premier groupe de nombres et du deuxième groupe de nombres sont multipliées.

Maintenant, revenez sur cette formule =SUMPRODUCT((MONTH(F2:F22)=3)*(H2:H22="Second Branch")*(G2:G22="Shirt")*J2:J22) , cela devrait être moins un casse-tête. =SUMPRODUCT((MONTH(F2:F22)=3)*(H2:H22="二分店")*(G2:G22="衬衫")*J2:J22)，应该没那么头疼了吧。

其实数组的计算原则还有更加复杂的情况，有兴趣的朋友可以看看相关的资料，这里了解简单原理即可。再来看看第三个公式：=SUMPRODUCT((H2:H22=H2)*(G2:G22={"毛衣","衬衫"})*L2:L22)。这个公式看上去和前面两个有点区别，(G2:G22={"毛衣","衬衫"})这部分用到了常量数组，其实这个公式本来应该是这样写的：=SUMPRODUCT((H2:H22=H2)*(G2:G22="毛衣")*L2:L22)+SUMPRODUCT((H2:H22=H2)*(G2:G22="衬衫")*L2:L22)

En fait, les principes de calcul des tableaux sont plus compliqués. Les amis intéressés peuvent consulter les informations pertinentes ici. Jetons un coup d'œil à la troisième formule : =SUMPRODUCT((H2:H22=H2)*(G2:G22={"sweater", "shirt"})*L2:L22). Cette formule est un peu différente des deux précédentes. (G2:G22={"Sweater", "Shirt"}) Cette partie utilise un tableau constant. En fait, cette formule devrait être écrite comme ceci. : =SUMPRODUCT((H2:H22=H2)*(G2:G22="Pull")*L2:L22)+SUMPRODUCT((H2:H22=H2)*(G2:G22="Chemise")* L2 :L22).

Cette formule utilise deux fonctions SUMPRODUCT La première consiste à calculer les ventes de pulls dans le magasin principal et la seconde consiste à calculer les ventes de chemises dans le magasin principal. Vous devriez être capable de comprendre chacune d'elles. séparément. Les deux fonctions SUMPRODUCT sont exactement les mêmes sauf pour "pull" et "chemise". Dans ce cas, vous pouvez utiliser un tableau constant pour rassembler les deux contenus afin de simplifier la formule.

Grâce à l'explication d'aujourd'hui, nous avons compris la formule commune SUMPRODUCT, résolu la confusion liée à l'utilisation du signe de multiplication de la fonction SUMPRODUCT et maîtrisé davantage d'applications de la fonction SUMPRODUCT. Nous avons également appris quelques notions de base sur les valeurs logiques et les tableaux, ce qui nous aidera à comprendre d'autres formules complexes.

Recommandations d'apprentissage associées : Tutoriel Excel

🎜

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!