Vous connaissez peut-être les fonctions EXCEL essentielles comme la somme et le produit, mais il existe également une fonction exceptionnelle connue sous le nom de sumproduct . La fonction de sumproduct dans Excel est un outil puissant qui permet aux utilisateurs d'effectuer des calculs complexes avec facilité . Il calcule la somme des produits des éléments correspondants dans les gammes ou les tableaux. Cette fonction peut être utilisée pour déterminer le salaire moyen des employés, le score moyen sur un examen ou le prix de vente moyen de l'inventaire d'une entreprise, la valeur d'investissement totale , entre autres utilisations.

Les principaux plats à retenir:

• La fonction SUMPRODUCT dans Excel est très polyvalente et peut être intégrée à d'autres fonctions telles que ISNUMBER et SEARCH pour effectuer des recherches complexes dans les cha?nes de texte. En utilisant SUMPRODUCT avec des tableaux de conditions, les utilisateurs peuvent évaluer plusieurs critères dans une seule cellule et résumer les cas où les critères sont satisfaits.
• En combinant SUMPRODUCT avec l'opérateur double unaire (-) pour convertir des valeurs vraies / fausses en 1 et 0s, les utilisateurs Excel peuvent formuler des expressions qui comptent les occurrences de sous-cha?nes spécifiques dans une cellule. Ceci est particulièrement utile pour l'analyse des données texte et l'identification de la présence de certains mots clés ou phrases.
• Incorporer SUMPRODUCT dans la fonction IF peut améliorer la fonctionnalité et la convivialité des feuilles de calcul Excel. Les utilisateurs peuvent fournir des sorties personnalisées au lieu de simples valeurs vraies / fausses, ce qui rend les feuilles de calcul plus lisibles et adaptables à des besoins d'analyse de données spécifiques ou à des préférences des utilisateurs.

Téléchargez le classeur Excel ci-dessous pour suivre et comprendre comment utiliser Sumproduct dans Excel - Téléchargez le classeur Excel Sumproduct-in-excel.xlsx

Table des matières

Introduction au sumproduct dans Excel

La fonction de sumproduct dans Excel est un outil polyvalent qui calcule la somme totale des produits des éléments correspondants dans des gammes ou des tableaux spécifiés. La syntaxe du sumproduct dans Excel est la suivante:

= Sumproduct (array1, [array2], [array3],…)

• Array1 -Le premier argument Array dont les éléments vous souhaitez multiplier puis ajouter. Requis.
• Array2, Array3,… - Arguments de tableau supplémentaires 2 à 255 dont vous souhaitez multiplier les éléments, puis ajouter. Facultatif.

Veuillez noter - les tableaux doivent avoir les mêmes dimensions, sinon la fonction renvoie une erreur.

Examinons un exemple pour mieux comprendre.

# 1 - Exemple de base

Supposons que vous ayez un ensemble de données répertoriant les quantités de divers articles vendus et leurs prix respectifs. Vous souhaitez calculer les revenus totaux générés à partir de ces ventes. Voici où le sumproduct est utile.

étape 1: Entrez la fonction de sumproduct.

= Sumproduct

étape 2: Entrez le premier argument, c'est-à-dire Array1. Ici, c'est la plage contenant la quantité d'articles vendus (B2: B9).

= Sumproduct (b2: b9

étape 3: Entrez le deuxième argument, c'est-à-dire le tableau 2. Ici, c'est la plage contenant le prix unitaire des articles vendus (C2: C9).

= Sumproduct (b2: b9, c2: c9)

évaluons cette formule:

= Sumproduct (b2: b9, c2: c9)

= Sumproduct ({300; 250; 150; 200; 100; 50; 80; 70}, {10; 5; 20; 30; 15; 50; 25; 40})

= Sumproduct ({3000,1250,3000,6000,1500,2500,2000,2800})

= 22050

Excel multiplie chaque élément dans Array1 avec l'élément correspondant dans Array2, puis résume le résultat. La fonction de sumproduct rationalise ce qui nécessiterait autrement plusieurs étapes, ce qui en fait un outil inestimable pour des calculs simples.

# 2 - sumproduct avec d'autres opérateurs arithmétiques

Bien que la multiplication soit l'opération par défaut , l'addition, la soustraction et la division peuvent également être appliquées . Utilisez le sumproduct de la manière habituelle, mais au lieu d'utiliser des virgules pour séparer les arguments du tableau, utilisez les opérateurs arithmétiques souhaités (*, / ,, -). Par exemple, pour utiliser la division avec 2 tableaux:

= Sumproduct (array1 / array2)

Supposons que vous ayez un ensemble de données avec le montant total des ventes et les prix unitaires des différents articles vendus . Vous souhaitez déterminer le nombre total d'articles vendus en fonction des données fournies. Pour ce faire, vous devez d'abord diviser le montant total des ventes par le prix unitaire pour chaque article, puis additionner le résultat pour obtenir la quantité totale vendue.

Alternativement, vous pouvez utiliser la fonction de sumproduct en rempla?ant la virgule par le signe de la division pour obtenir le résultat:

étape 1: Entrez la fonction de sumproduct.

= Sumproduct

étape 2: Entrez le premier argument, c'est-à-dire Array1. Ici, c'est la plage contenant le montant total des ventes (B2: B9).

= Sumproduct (b2: b9

étape 3: Entrez l'opérateur de division (/).

= Sumproduct (b2: b9 /

étape 4: Entrez le deuxième argument, c'est-à-dire le tableau 2. Ici, c'est la plage contenant le prix unitaire des articles vendus (C2: C9).

= Sumproduct (b2: b9 / c2: c9)

= Sumproduct (b2: b9 / c2: c9)

= Sumproduct ({3000; 1250; 3000; 6000; 1500; 2500; 2000; 2800} / {10; 5; 20; 30; 15; 50; 25; 40})

= Sumproduct ({300,250,150,200,100,50,80,70})

= 1200

Excel calcule le nombre total d'unités vendues en divisant le montant des ventes par le prix unitaire, puis en additionnant le résultat.

# 3 - Moyennes pondérées

Le sumproduct peut également être utilisé pour calculer les moyennes pondérées dans divers scénarios.

Supposons que vous ayez investi dans un portefeuille d'actions et que vous souhaitez calculer le rendement moyen pondéré pour vos investissements. Vous avez le montant total investi et les rendements correspondants en pourcentage pour toutes les actions . à l'aide de la fonction de sumproduct, vous pouvez déterminer le rendement moyen global pondéré de votre portefeuille.

Tout d'abord, utilisez la fonction de sumproduct pour calculer le rendement total (en $) de l'investissement en multipliant le montant investi avec le% de rendement, puis en additionnant le résultat. Ensuite, divisez cela par l'investissement total fait pour obtenir le rendement moyen du portefeuille.

étape 1: Entrez la fonction de sumproduct.

= Sumproduct

étape 2: Entrez le premier argument, c'est-à-dire Array1. Ici, c'est la plage contenant le montant investi (B2: B8).

= Sumproduct (b2: b8

étape 3: Entrez le deuxième argument, c'est-à-dire le tableau 2. Ici, c'est la plage contenant le retour de chaque investissement réalisé (C2: C8).

= Sumproduct (b2: b8, c2: c8)

Maintenant que vous avez le rendement total, vous pouvez facilement calculer le rendement moyen (%) en le divisant par le montant total investi.

étape 4: Entrez l'opérateur de division (/) puis la fonction de somme.

= Sumproduct (b2: b8, c2: c8) / sum (

étape 5: Sélectionnez la plage contenant le montant investi, c'est-à-dire B2: B8.

= Sumproduct (b2: b8, c2: c8) / sum (b2: b8)

En utilisant les fonctions de sumproduct et de somme, vous pouvez facilement calculer les moyennes pondérées dans n'importe quel scénario.

Dépannage des problèmes de sumproduct commun

Résoudre le #value redouté! Erreur

Lorsque vous rencontrez le #Value! Erreur lors de l'utilisation du sumproduct, il indique souvent un mélange de texte et de données numériques dans vos gammes sélectionnées. Parfois, cette erreur survient parce qu'une cellule est incorrectement formatée sous forme de texte, ou elle peut contenir un résultat d'une autre formule qui n'est pas compatible avec les nombres. Pour résoudre ce problème, assurez-vous que toutes les cellules de la gamme avec lesquelles vous travaillez contiennent des nombres ou peuvent être converties en nombres. Vous serez surpris de la fréquence à laquelle la vérification du type de données résout votre problème!

Débogage des problèmes de syntaxe du sumproduct

La résolution des problèmes de syntaxe dans le sumproduct peut s'apparence à la résolution d'un puzzle. Tout d'abord, n'oubliez pas que vos gammes doivent être de la même taille. S'ils ne le sont pas, le sumproduct renvoie une erreur car il ne peut pas coupler les éléments correspondants. Ensuite, faites attention aux parenthèses mal placées, qui peuvent déséquilibrer votre formule. Chaque argument doit être correctement joint pour s'assurer que la fonction peut lire et traiter vos données avec précision.

Soyez également conscient des opérateurs. Si vous avez décidé d'ajouter un flair mathématique à votre formule en incorporant l'addition ou la soustraction, assurez-vous que ces opérateurs ne provoquent pas de conflits en étant égarés. Une formule claire et correctement structurée se déroule non seulement en douceur, mais est également plus facile pour quiconque regarde votre classeur pour comprendre et entretenir.

Meilleures pratiques et conseils pour une utilisation efficace

Comprendre Array1, Notation Array2

Plonger dans le sumproduct sans comprendre la notation, c'est comme essayer de lire une carte sans conna?tre les symboles. Heureusement, c'est assez simple une fois que vous avez compris. Array1 fait référence au premier groupe de valeurs que vous souhaitez multiplier ensemble. Ceci est votre point de départ, contenant l'ensemble de multiplications sur lesquelles le sumproduct fonctionnera d'abord.

Ensuite, Array2 (et tous les tableaux ultérieurs comme Array3, Array4, etc.) rejoint la fête en tant qu'invités en option. Vous pouvez les inclure lorsque vous devez multiplier les éléments correspondants entre plusieurs gammes. Considérez ces tableaux comme des couches supplémentaires de données que le sumproduct iratera, se multipliant, puis additionnant leurs produits au grand total.

En d'autres termes, Array1 est l'invité indispensable à votre événement de formule Sumproduct, et Array2 et au-delà sont plus, là-bas pour améliorer les choses lorsque les conditions l'appellent. Embrassez la notation et vous débloquerez un nouveau domaine de la capacité de calcul dans Excel.

Le garder rapide et propre avec le sumproduct

Le maintien de la vitesse et de l'efficacité dans vos classeurs Excel est crucial, en particulier lorsque vous traitez de grands ensembles de données. Avec le sumproduct, l'objectif est de garder les choses rapides et bien rangées. Vous pouvez accélérer les choses en utilisant des plages nommées pour vous référer à vos données. Cela rend non seulement vos formules plus faciles à lire, mais réduit également les chances de référencer les erreurs.

Deuxièmement, évitez d'utiliser des références de colonne entières dans la formule de votre sumproduct. Bien qu'il puisse être tentant d'utiliser simplement ?B: B? au lieu de ?B1: B100?, cela peut conduire à des temps de calcul plus lents car Excel traite des dizaines de milliers de cellules inutiles. Au lieu de cela, soyez précis avec votre gamme - une pratique qui permettron de faire fonctionner votre classeur au plus rapide.

N'oubliez pas non plus que la maintenance des formules directes et le point aide à maintenir la propreté du classeur. Si vous pouvez accomplir quelque chose de manière élégante avec une formule de sumproduct, il y a rarement le besoin d'encombrer votre classeur avec plusieurs opérations plus compliquées.

FAQS: vos questions de sumproduct ont répondu

Pourquoi utiliser le sumproduct sur d'autres fonctions de somme?

Le sumproduct est votre incontournable pour la polyvalence et la force des calculs. Contrairement à d'autres fonctions de somme qui pourraient frapper un barrage routier avec certaines conditions ou nécessiter de configurer plusieurs étapes intermédiaires, le sumproduct va au-delà de l'ajout de nombres - il multiplie des éléments correspondants entre les tableaux, puis résume ces produits. Cette capacité unique signifie que vous pouvez effectuer des formules complexes dans une seule ligne, où d'autres comme SUM ou SUMIF peuvent avoir besoin d'aide supplémentaire ou ne peuvent tout simplement pas faire face.

De plus, parce que le sumproduct ne nécessite pas de formules entravées par le tableau, vous pouvez éviter le processus de changement de contr?le lourd Entrée, rationaliser encore plus votre expérience Excel. Si vous appréciez une feuille de calcul propre et appréciez l'efficacité, le sumproduct vous a le dos.

Le sumproduct peut-il gérer ou logique avec succès?

Absolument, et c'est l'un des points forts de l'utilisation du sumproduct - c'est tout à fait le multitache lorsqu'il s'agit d'appliquer des conditions de logique! Le sumproduct peut traiter ou logique sans effort dans vos critères, ce qui le distingue de ses frères et s?urs Sumifs et Coups, qui adhèrent strictement à et la logique.

Pour travailler dans cette logique, vous utiliseriez l'opérateur plus () pour inclure plusieurs conditions. Considérez-le comme indiquant que le sumproduct: "Hé, estimez les valeurs totales si ce critère ou ce critère est rempli." La fonction suit ensuite avec diligence, résumant les valeurs lorsque l'une ou l'autre des conditions données est vraie.

Donc, si jamais vous avez besoin de résumer dans diverses conditions sans écrire une saga totale de formules distinctes, n'oubliez pas que le sumproduct peut gérer ou logique, simplifiant votre vie de feuille de calcul.

Comment adapter le sumproduct pour compter les occurrences uniques?

Compter les occurrences uniques n'est peut-être pas pour cela que le sumproduct est connu, mais avec quelques ajustements, c'est certainement à la hauteur du défi. Adaptez le sumproduct à cet effet en le combinant avec d'autres fonctions comme le countif. Essentiellement, vous metterez en place une formule qui pèse inversement des éléments répétés, donc si un élément appara?t plus d'une fois, il contribue moins au décompte final.

Une formule peut ressembler à ceci: =SUMPRODUCT(1/COUNTIF(range, range)) , ce qui garantit intelligemment que chaque élément unique est compté une fois. La partie Countif calcule combien de fois un élément appara?t dans la plage, puis le sumproduct résume les réciproques de ces dénombrements - comptant efficacement chaque élément distinct une seule fois.

N'oubliez pas que cette transformation Nifty est particulièrement utile si vous travaillez avec une version d'Excel qui ne prend pas en charge la fonction unique. L'utilisation de sumproduct de cette manière pour compter les occurrences uniques illustre à quel point il peut être un couteau à armée suisse dans votre ensemble d'outils Excel.

Le sumproduct calcule la somme des produits des éléments correspondants dans des tableaux ou des gammes donnés. Cette fonction trouve une application dans divers scénarios, y compris des calculs de base tels que les revenus totaux des ventes, des opérations complexes impliquant différents opérateurs arithmétiques et le calcul des moyennes pondérées, telles que la détermination du rendement moyen pondéré des investissements dans un portefeuille diversifié.

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!