La fonction du SUMMESLE, ainsi que du SUMMESLES selon deux critères

1. Recherche par tags

Imaginez une table dans laquelle les noms de départements (ou comptes, ou autre chose) sont listés en rangées.

Faire la somme des cellules par critère
Il est nécessaire de calculer le montant total pour chaque département. Beaucoup le font avec un filtre et en écrivant avec des stylos dans les cellules.
Bien que cela puisse être fait facilement et simplement avec une seule fonction - SUMMESLI .
SUMMESLES (SUMIF): répertorie les cellules qui satisfont à une condition donnée (une seule condition peut être spécifiée). Cette fonction peut également être utilisée si le tableau est divisé en colonnes par périodes (mensuel, trimestriel, trois colonnes - Revenu | Dépense | Différence) et que vous devez calculer le montant total pour toutes les périodes uniquement par Revenu, Dépense et Différence.

Il y a trois arguments au total pour SUMMESLI: Range , Criterion , Range_Summing .
= SUMMESLE (A1: A20000; A1; B1: B20000)
= SUMIF (A1: A20000, A1, B1: B20000)

• Plage (A1: A20000) - indique la plage avec les critères. C'est à dire La colonne dans laquelle rechercher la valeur indiquée par l'argument Criterion .
• Le critère (A1) est la valeur (texte ou numérique, ainsi que la date) qui doit être trouvée dans la plage . Peut contenir les caractères génériques "*" et "?". C'est à dire en spécifiant le critère "* masse *" pour résumer les valeurs dans lesquelles le mot "masse" apparaît. Dans le même temps, le mot «masse» peut apparaître n'importe où dans le texte ou ne peut en contenir qu'un seul dans une cellule. Et en spécifiant "masse *", toutes les valeurs commençant par "masse" seront résumées. "?" - remplace un seul caractère, c'est-à-dire en spécifiant "mas? a", vous pouvez résumer les lignes avec la valeur "masse" et la valeur "masque", etc.
  Si le critère est écrit dans une cellule et que vous devez toujours utiliser des caractères génériques, vous pouvez créer un lien vers cette cellule en ajoutant celui qui est nécessaire. Supposons que vous deviez résumer les valeurs contenant le mot "total". Le mot "total" est inscrit dans la cellule A1, tandis que dans la colonne A, il peut y avoir différentes valeurs orthographiques contenant le mot "total": "totaux pour juin", "totaux pour juillet", "totaux pour mars". La formule devrait alors ressembler à ceci:
  = SUMMERS (A1: A20000; "*" & A1 & "*"; B1: B20000)
  "*" & A1 & "*" - le signe & (esperluette) combine plusieurs valeurs en une. C'est à dire le résultat est "* result *".
  Pour mieux comprendre le principe de fonctionnement des formules, utilisez l'outil Calculer une formule : Comment afficher les étapes pour calculer des formules
  Tous les critères textuels et les critères comportant des signes logiques et mathématiques doivent être placés entre guillemets (= SUMMESLI (A1: A20000; "total"; B1: B20000)). Si le critère est un nombre, les guillemets ne sont pas nécessaires. Si vous voulez trouver directement un point d'interrogation ou un astérisque, vous devez placer un tilde (~) devant celui-ci.
  À propos du tilde et de ses caractéristiques peuvent être trouvés dans cet article: Comment remplacer / supprimer / trouver un astérisque?
• Sum_Range (B1: B20000) (argument facultatif) - spécifie la plage de sommes ou de valeurs numériques à additionner.

Fonctionnement: la fonction recherche dans la plage la valeur spécifiée par l'argument Criterion et, lorsqu'une correspondance est trouvée, additionne les données indiquées par l'argument Range_Amount. C'est à dire si nous avons un nom de département dans la colonne A et un montant dans la colonne B, spécifier le département de développement comme critère donnera la somme de toutes les valeurs de la colonne B, à la différence du département de développement figurant dans la colonne A. En fait, SumArrangement peut ne pas avoir la même taille que l'argument Range et cela ne provoquera pas d'erreur de la fonction elle-même. Toutefois, lors de la définition des cellules à additionner, la cellule en haut à gauche de l'argument Range_Amount sera utilisée comme cellule de départ pour l'addition, puis les cellules correspondant en taille et en forme à l'argument Range seront additionnées.

Quelques fonctionnalités
Le dernier argument de la fonction (Sum_And_Band: B1: B20000) est facultatif. Cela signifie que cela ne peut pas être spécifié. Si vous ne le spécifiez pas, la fonction additionnera les valeurs spécifiées par l'argument Range . À quoi ça sert Par exemple, vous devez obtenir la somme des seuls nombres supérieurs à zéro. Dans la colonne A du montant. Ensuite, la fonction ressemblera à ceci:
= SUMMERS (A1: A20000; "> 0")

Ce qui devrait être considéré: le range_summing et le range devraient être égaux dans le nombre de lignes. Sinon, vous risquez d'obtenir un résultat erroné. De manière optimale, si cela apparaît dans les formules que j'ai données: la plage et la plage des sommations commencent à une ligne et ont le même nombre de lignes: A1: A20000; B1: B20000

Résumé sur deux critères ou plus
Mais que faire lorsque les critères de sommation 2 et plus? Supposons que vous n’ayez besoin de récapituler que les montants qui appartiennent à un département et pour une date donnée. Les utilisateurs satisfaits des versions 2007 et supérieures de Office peuvent utiliser la fonction SUMMESLIMN:
= SUMMESLIMN (2 dollars canadiens: 50 dollars canadiens; 2 dollars australiens: 50 dollars australiens; 3 dollars américains; 3 dollars canadiens, 2 dollars canadiens: 50 dollars américains, 8 dollars australiens)
$ C $ 2: $ C $ 50 - intervalle_échant. Le premier argument spécifie la plage de cellules contenant les quantités qui seront collectées en une seule.
$ A 2 $: 50 $ A, B $ 2 $: B $ 50 - Critères_écran. Spécifie la plage de cellules dans laquelle vous souhaitez rechercher une correspondance par critère.
$ I $ 3, $ H8 - critère. Ici, comme dans SUMMESLI, les caractères génériques * et ? Sont autorisés . et ils fonctionnent de la même manière.

Spécificités de la spécification des arguments: tout d'abord, la plage de critères est spécifiée (ils sont numérotés), puis la valeur (critère) est indiquée directement en point-virgule, qui doit être trouvé dans cette plage - $ A $ 2: $ A $ 50; $ I $ 3. Et rien d'autre. N'essayez pas de spécifier d'abord toutes les plages, puis les critères correspondants - la fonction générera une erreur ou ne résumera pas ce qui est nécessaire.

Toutes les conditions sont comparées selon le principe I. Cela signifie que si toutes les conditions énumérées sont remplies. Si au moins une condition n'est pas remplie, la fonction ignore la ligne et n'ajoute rien.
En ce qui concerne les SUMMERS, les plages de sommation et de critères doivent être égales en nombre de lignes.

Parce que SUMMESLIMN n'est apparu que dans les versions d'Excel, à partir de 2007, alors comment des utilisateurs mécontents de versions antérieures peuvent-ils l'être dans de tels cas? Très simple: utilisez une autre fonction - SOMMAIRE. Je ne vais pas peindre les arguments, parce que Il y en a beaucoup et ce sont tous des tableaux de valeurs. Cette fonction multiplie les tableaux indiqués par les arguments. Je vais essayer de décrire le principe général d’utilisation de cette fonction pour résumer des données sur plusieurs conditions.
Pour résoudre le problème de sommation selon plusieurs critères, la fonction ressemblera à ceci:
= SOMMAIRE ((2 dollars australiens: 50 dollars américains = 3 dollars américains) * (2 dollars canadiens: 50 dollars américains = H5); 2 dollars canadiens: 50 dollars canadiens)
2 dollars australiens: 50 dollars australiens - plage de dates. $ I $ 3 est la date du critère pour lequel il est nécessaire de faire la somme des données.
$ B $ 2: $ B $ 50 - les noms des départements. H5 - le nom du département, les données sur lesquelles doivent être additionnées.
2 $ CAN: 50 $ CAN - fourchette avec montants.

Nous analysons la logique, parce que pour beaucoup, il sera complètement obscur de regarder cette fonction. Si seulement parce que dans l'aide cette application n'est pas décrite. Pour une meilleure lisibilité, réduisez la taille des plages:
= SOMMAIRE ((2 dollars australiens: 5 dollars américains = 3 dollars américains) * (2 dollars canadiens: 5 dollars américains = 5 $); 2 $ canadiens: 5 $ canadiens)
Ainsi, les expressions ($ A $ 2: $ A $ 5 = $ I $ 3) et ($ B $ 2: $ B $ 5 = H5) sont logiques et renvoient des tableaux de valeurs logiques FALSE et TRUE. VRAI si la cellule de la plage $ A $ 2: $ A $ 5 est égale à la valeur de la cellule $ I $ 3 et que la cellule de la plage $ B $ 2: $ B $ 5 est égale à la valeur de la cellule H5. C'est à dire nous avons les éléments suivants:
= SOMMAIRE ({FALSE; VRAI; VRAI; FAUX} * {{FAUX; FAUX; VRAI; FAUX}; 2 $ CA: 50 $ CAN)
Comme vous pouvez le constater, dans le premier tableau, il y a deux correspondances pour la condition et dans le second. De plus, ces deux tableaux sont multipliés (le signe de multiplication (*) en est responsable). Lorsque la multiplication se produit, la conversion implicite des tableaux FALSE et TRUE en constantes numériques 0 et 1, respectivement ({0; 1; 1; 0} * {0; 0; 1; 0}) se produit. Comme vous le savez, multiplié par zéro, nous obtenons zéro. Et le résultat est un tableau unique:
= SOMMAIRE ({0; 0; 1; 0}; 2 $ CAN: 50 $ CAN)
Ensuite, le tableau {0; 0; 1; 0} est multiplié par un tableau de nombres compris entre $ C $ 2: $ C $ 50:
= SOMMAIRE ({0; 0; 1; 0}; {10; 20; 30; 40})
Et par conséquent, nous en avons 30. Ce dont nous avions besoin - nous obtenons uniquement le montant qui répond au critère. S'il y a plus d'une somme satisfaisant le critère, elles seront alors résumées.

Avantage de SUMMYROIZV
Si les arguments ont le signe plus à la place du signe de multiplication:
($ A $ 2: $ 5 $ = I $ 3 $) + (B $ 2 $: B $ 5 $ = H5)
alors les conditions seront comparées selon le principe du OU: i.e. les sommes totales seront résumées si au moins une condition est remplie: Soit 2 $ A: 5 $ est égal à la valeur de la cellule $ I $ 3 ou la cellule de la plage $ B $ 2: $ B $ 5 est égale à la valeur de la cellule H5.
C'est l'avantage de SUMMPRODUCT par rapport à SUMMESLIMN. SUMMESLIMN ne peut pas résumer les valeurs selon le principe OR, mais uniquement selon le principe AND (toutes les conditions doivent être remplies).

Inconvénients
SOMMAIRE ne peut pas utiliser de joker * et ?. Il est possible d'utiliser plus précisément, mais ils ne seront pas perçus comme des caractères spéciaux, mais comme un astérisque et un point d'interrogation. Je pense que c'est un inconvénient important. Et bien que cela puisse être contourné, j’utilise d’autres fonctions à l’intérieur de SOMPRODUCT. Ce serait tout de même formidable si la fonction pouvait en quelque sorte utiliser des caractères génériques.

Dans l'exemple, vous trouverez quelques exemples de fonctions permettant de mieux comprendre ce qui est écrit ci-dessus.

Téléchargez un exemple

Montant selon plusieurs critères (41.5 KiB, 10,477 Téléchargements)

Voir aussi:
Somme des cellules par couleur de remplissage
Résumé des cellules par couleur de police
Résumer des cellules par format de cellule
Calculer la quantité de cellules par couleur de remplissage
Calculer le nombre de cellules par couleur de police
Comment résumer les données de plusieurs feuilles, y compris par condition

{"Barre inférieure": {"textstyle": "static", "textpositionstatic": "bottom", "textautohide": true, "textpositionmarginstatic": 0, "textpositiondynamic": "bottomleft", "textpositionmarginleft": 24, " textpositionmarginright ": 24," textpositionmargintop ": 24," textpositionmarginbottom ": 24," texteffect ":" diapositive "," texteffecteasing ":" aisanceOutCubic "," texteffectduration ": 600," texteffectslidedirection ":" gauche "," texteffectslidedistance " : 30, "texteffectdelay": 500, "texteffectseparate": false, "texteffect1": "slide", "texteffectslidedirection1": "right", "texteffectslidedistance1": 120, "texteffecteasing1": "aisanceOutCubique", "texteffectdurant1" " "texteffectdelay1": 1000 textcss ":" display: block; padding: 12px; text-align: left; "," textbgcss ":" display: block; position: absolu; haut: 0px; gauche: 0px; largeur: 100%; hauteur: 100% ; couleur de fond: # 333333; opacité: 0,6; filtre: a lpha (opacity = 60); "," titlecss ":" display: block; position: relative; police: bold 14px "Lucida Sans Unicode", \ "Lucida Grande \", sans-serif, Arial; color: #fff; "," descriptioncss ":" display: block; position: relative; police: 12px "Lucida Sans Unicode", \ "Lucida Grande \", sans-serif, Arial; couleur: #fff; margin-top: 8px; "," buttoncss ":" display: block; position: relative; margin-top: 8px; "," texteffectresponsive ": true," texteffectresponsives ": 640," titlecssresponsive ":" taille de police: 12px; "," descriptioncssresponsive ":" display: none: important; "," buttoncssresponsive ": "", "addgooglefonts": false, "googlefonts": "", "textleftrightpercentforstatic": 40}}

Recherche par tags