Utilisation de SOMME(SOMME.SI.ENS)

Bonjour.

J'ai une tâche à faire sur Excel et je sollicite votre aide.

Je dispose d'un tableau qui contient des colonnes fruits, quantité, lots et d'autres paramètres, que je n'ai pas tous mis dans le fichier d'exemple joint à ce message.

Je souhaite faire la somme de quantités de fruits, et ce par lot.

Par contre, pour un même lot, les produits peuvent se répéter, il n'y a pas qu'une seule occurrence de "pommes" par lot par exemple.

Je souhaite que la somme des quantités de fruits se fasse pour chaque lot, que la mention "fruits" apparaisse dans la cellule précédent celle où se trouvera le résultat de la somme et que le contenu des autres colonnes, par exemple le numéro de lot, soit transféré également dans ce nouveau tableau de résultats.

Il y a des exceptions :

par exemple certains fruits, les fruits tropicaux, doivent être traités à part des fruits normaux, et leur somme devra s'afficher séparément et avec la mention "fruits tropicaux" dans la cellule précédente et les autres paramètres devront aussi être présents.

Une autre exception concerne les fruits à prix réduits, qui doivent aussi être comptés à part mais ils doivent être nommés "fruits" comme les fruits normaux.

Dans le fichier joint à ce message, les tableaux de droite vous expliquent les différentes situations.

Mon deuxième problème est que je dois ensuite copier ce tableau pour le coller dans un autre fichier Excel qui contient des formules qui permettront de traiter ces données.

J'ai donc tenté des choses : je me suis intéressé aux fonctions SOMME et SOMME.SI.ENS.

Quand je rentre la formule suivante directement sur la feuille Excel :

=SOMME(SOMME.SI.ENS(B:B; A:A; {"pommes";"oranges";"poires"}))

j'obtiens la somme de tous les fruits de tous les lots.

Avec cette formule :

=SOMME(SOMME.SI.ENS(B:B;A:A;{"pommes";"oranges";"poires"};C:C;C2))

j'obtiens la somme de tous les fruits du lot 1. C'est ce que je cherche, il faut donc que je fasse une boucle sur le numéro de lot avec cette formule, je pense.

(Deux remarques : si j'ai mis explicitement les noms de fruits dans la formule, c'est car ils sont en nombre réduit, par plus de 10, donc je peux me permettre de le faire. Et ensuite, la cellule C2 est celle où se trouve le premier numéro de lot. Cependant, il n'y a pas un nombre fixe de lignes par lot, donc je ne vois pas comment faire une boucle. De plus, le numéro de lot ne commencera pas toujours par 1, ça pourra être 25, mais par contre dans presque tous les cas, le numéro de lot suivant le premier sera incrémenté de +1. Je ne sais pas s'il existe en VBA un moyen pour dire de faire une somme sur telles lignes puis de passer à une nouvelle somme quand le numéro de lot change.)

Je suis obligé de faire une "concaténation" de fonctions, de les avoir imbriquées, sinon je n'obtient pas le bon résultat.

Mais quand j'utilise ces fonctions sur dans l'éditeur VBA, les lignes deviennent rouges et quand je veux lancer la macro, j'ai une erreur de syntaxe.

WorksheetFunction.Sum(WorksheetFunction.Sumifs(B:B; A:A; {"Pommes";"Oranges"}; C:C;E13))

Bonjour

au lieu de A:A ->> Range("A:A") et idem pour les autres

Avec deux colonnes supplémentaires et un simple tableau croisé dynamique, on obtient ce que tu cherches, je crois.

Bonjour à tous

Je partage l'avis de Valky68 (qui semble allergique au mot bonjour si j'en crois l'absence répétée de formule de politesse dans ses posts)

Par ailleurs travailler avec un tableau structuré et utiliser des formules de plage sur des colonnes entières n'as pas de sens...

Bonjour.

Merci à tous de vos réponses.

Tout d'abord, 78chris, pourrais-tu expliquer pourquoi "travailler avec un tableau structuré et utiliser des formules de plage sur des colonnes entières n'a pas de sens" ?

Concernant les tableaux croisés dynamiques, est-ce reproductible automatiquement ou il faut à chaque fois refaire les mêmes actions ? Car j'ai des dizaines de tableaux de ce type à traiter, c'est pour cela que je pensais qu'il était plus commode d'avoir une macro.

Valky68, je n'obtiens pas ce que je cherche avec ton tableau. En fait, je dispose d'un tableau avec différents produits dont des fruits, et je souhaite sommer uniquement les quantités des fruits. J'ai l'impression que tu as mis "fruits" partout sans différencier les produits "fruits" des produits "non fruits". Et je suis sensé obtenir les mêmes valeurs de quantités que celles contenues dans mes tableaux "faits à la main" présent sur le fichier de test.

Par exemple, pour le premier lot, je dois obtenir un total de 21 fruits. (5 pommes + 7 oranges + 5 pommes + 4 poires)

Et la formule utilisée pour cela dans la feuille est :

=SOMME(SOMME.SI.ENS(B:B; A:A; {"apples";"oranges";"pears"}; C:C; C2))

Et elle fonctionne très bien et me donne le bon résultat.

Cependant, dans l'éditeur VBA, la formule suivante ne fonctionne pas :

Range("L2").Value = Application.WorksheetFunction.Sum(WorksheetFunction.SumIfs(Range("B:B") ; Range("A:A") ; {"Apples";"Oranges";"Pears"} ; Range("C:C") ; C2))

J'obtiens l'erreur suivante :

"Erreur de compilation Attendu : Séparateur de liste ou )"

Et le curseur se place au niveau du premier point-virgule, après Range("B:B"). C'est, je suppose, l'endroit de l'erreur. Mais il me semblait que les espaces ne comptaient pas et que le séparateur était bien le point-virgule.

J'ai bien rajouté Range comme on me l'avait précisé. Et j'ai également vérifié le nombre de parenthèses, il est correct.

Vous savez ce qui se passe ?

Concernant le critère L, je souhaitais le "transférer" à côté de la cellule qui contient la somme des quantités des fruits. Comme pour le numéro de lot en fait.

Je vous remercie d'avance.

Bonjour le forum, Max35,

Je n'y connais pas grand chose en VBA mais il me semble qu'il faut mettre des , au lieu des ;

A savoir:

Range("L2").Value = Application.WorksheetFunction.Sum(WorksheetFunction.SumIfs(Range("B:B") , Range("A:A") , {"Apples";"Oranges";"Pears"},  Range("C:C") , C2))

Cordialement.

Merci, je n'ai plus l'erreur à présent.

Par contre, j'ai d'autres erreurs : le { est décrit comme étant un caractère interdit.

Y a-t-il alors un moyen pour grouper tous les critères (pommes, oranges et poires) sans répéter Range("A:A") devant pour chaque critère ?

Re Max35,

A tester peut-être comme ceci:

Range("L2").Value = Application.WorksheetFunction.Sum(WorksheetFunction.SumIfs(Range("B:B"), Range("A:A"), "{""Apples"";""Oranges"";""Pears""}", Range("C:C"), C2))

Cdlt

J'ai essayé

Range("L2").Value = Application.WorksheetFunction.Sum(WorksheetFunction.SumIfs(Range("B:B"), Range("A:A"), "{""Apples"";""Oranges"";""Pears""}", Range("C:C"), C2))

mais le résultat est 0.

Avec

Range("L2").Value = Application.WorksheetFunction.Sum(WorksheetFunction.SumIfs(Range("B:B"), Range("A:A"), ("Apples"), Range("A:A"), ("Oranges"), Range("A:A"), ("Pears"), Range("C:C"), C2))

j'obtiens aussi 0.

Mais directement sur la feuille Excel, sans passer par l'éditeur VBA,

=SOMME(SOMME.SI.ENS(B:B; A:A; {"apples";"oranges";"pears"}; C:C; C2))

me donne bien 21, qui est le bon résultat.

Bonjour à tous

Max35 a écrit :

Bonjour.

Tout d'abord, 78chris, pourrais-tu expliquer pourquoi "travailler avec un tableau structuré et utiliser des formules de plage sur des colonnes entières n'a pas de sens" ?

Parce qu'Excel connait la taille du tableau à tout moment et que faire le calcul sur 1 million de lignes ne peut que ralentir inutilement le calcul

Max35 a écrit :

Concernant les tableaux croisés dynamiques, est-ce reproductible automatiquement ou il faut à chaque fois refaire les mêmes actions ? Car j'ai des dizaines de tableaux de ce type à traiter, c'est pour cela que je pensais qu'il était plus commode d'avoir une macro.

De façon générale on évite de mélanger détail et synthèse dans un même tableau car c'est peu lisible...

SOMME.SI n'est efficace que si tu se réfères aux cellules pour le critère, pas en saisissant chaque texte

Pour ma part je n'ai pas compris ton exemple avec 4 tableaux qui ne permet pas d'établir un lien si toutefois il y en a un...

Valky68 de même, puisqu'il a voulu retrouvé la notion correspondant à Catégorie et Reduced en ajoutant ces colonnes au tableau source

Si on n'ajoute pas les colonnes à la source il faut à minima une table de correspondance pour ranger les articles dans Fruits ou Tropical ou les ignorer

Ceci fait, il est facile de travailler avec un TCD d'autant qu'on peut en définir la source par VBA... ce qui permet donc de traiter tout tableau de même structure...

@Max35,

Je retente ma chance, mais j'avoue ne pas tout avoir compris sur la demande.

With Range("L2:L" & Range("A" & Rows.Count).End(xlUp).Row)
    .FormulaR1C1 = _
    "=SUM(SUMIFS(Tableau1[QUANTITY],Tableau1[PRODUCTS], {""apples"";""oranges"";""pears""},Tableau1[BATCH], RC3))"
    .Value = .Value
End With

Cdlt.

Rebonjour,

je vais tester ce que tu as envoyé et je reviens vers toi.

En attendant, je vais expliquer à nouveau pour la demande :

j'ai un tableau.

Il contient en première colonne une liste de produits.

En deuxième colonne, la quantité des produits.

En troisième colonne, le numéro de lot auquel appartiennent les produits de la ligne.

Et en quatrième colonne un marqueur/critère appelé L et qui est affecté aux produits de la ligne.

Les produits peuvent se répéter. Par exemple dans un lot, on peut avoir 5 pommes, puis 3 ordinateurs puis, 4 pommes. Dans les données brutes, il n'y a pas de regroupement par produit qui se fait au préalable, il peut y avoir des doublons.

De plus, le nombre de lignes (c'est-à-dire de types de produits, en comptant les doublons que je viens juste d'expliquer) n'est pas le même dans chaque lot.

Ceci étant posé : je souhaite récupérer la quantité des produits qui sont des fruits et ce pour chaque lot.

Par exemple, dans le premier lot, il y a 5 pommes, 7 oranges, 5 pommes et 4 poires. Le résultat est 21 fruits. J'aimerais pouvoir automatiquement créer (d'où le VBA) un tableau de résultats dans lequel on a le nom générique "fruits" (sans entrer dans les détails de leur type) et puis dans la cellule à coté, la quantité, puis le numéro de lot et le critère L associés.

Puis continuer : pour le lot 2 : faire la somme des quantités des fruits uniquement, mettre cette valeurs dans le tableau précédée de la mention "fruits", rajouter le numéro de lot dans la cellule adjacente ainsi que le critère L.

Et ainsi de suite pour chaque lot du fichier.

Ensuite, j'aurai d'autres fichiers avec d'autres lots, c'est pour ça que je me suis dit qu'il valait mieux faire une macro.

Pour obtenir la quantité des fruits faisant partie d'un lot, j'applique sur la feuille Excel la formule suivante, qui fonctionne :

=SOMME(SOMME.SI.ENS(B:B; A:A; {"apples";"oranges";"pears"}; C:C; C2))

Ce que j'aimerais savoir, c'est comment faire pour réaliser cela grâce à une macro VBA, car d'une part, je dois sortir le tableau des résultats pour tous les lots dans ce premier fichier, donc je dois faire une boucle sur le numéro de lot, et je ne sais pas comment faire car il n'y a pas le même nombre de lignes par lots (donc je ne peux pas dire de tourner toutes les 10 lignes par exemple) et d'autre part je n'aurai pas un unique fichier de ce type à traiter mais des centaines qui eux-mêmes peuvent contenir une centaine de lots.

Ensuite, il peut y avoir des exceptions : par exemple certains fruits sont tropicaux. Ceux-là il ne faut pas les compter dans la catégorie fruits, mais juste les sommer à part, et dans le tableau des résultats, rajouter une ligne avec 4 cellules "fruits tropicaux, 5, 1, 2" sur le modèle "nom, quantité sommée de tous les représentants de ce produit dans le lot, numéro de lot et valeur du critère L".

Une autre exception concerne les fruits à prix réduit : de la même manière, je dois les compter à part, et tout faire comme expliqué plus haut, la seule différence est que le nom qui leur sera attribué dans le tableau des résultats sera tout simplement fruits.

Donc si vous avez 3 pommes, 2 oranges, 3 oranges à prix réduit et 4 mangues dans le lot 1 avec un L=2, le tableau des résultats aura la tête suivante :

Fruits 5 1 2

Fruits tropicaux 4 1 2

Fruits 3 1 2

C'est ce tableau que je veux obtenir.

Je suis obligé d'utiliser ces nomenclatures car je dois ensuite utiliser transférer ce tableau des résultats vers un autre fichier Excel avec des formules toutes faites qui requièrent cette écriture.

RE

Ce pourquoi il est nécessaire d'avoir une table de correspondance entre Produit et Catégorie mais mon post n'a pas été lu ou compris...

Bonjour à tous,

@Chris : voilà pourquoi on me voit moins depuis un moment ;)

Bon courage

Bonjour 21Formatic

Oui il y a des jours ou je sature aussi : les multipost, l'absence de politesse, l'impression de crier dans le désert, des classeurs parfois tellement foutoir ou semblant dater d'Excel 4, que je les referme aussitôt pour les laisser aux anthropologues...

Mais j'aime bien te voir passer, ça redonne du courage...

Re,

Quand la personne n'attend pas une application clé en main, gratuitement, pour hier...

Pour ce qui est du plaisir de me voir passer, c'est sympa, et plus que reciproque. Je lis toujours avec intérêt tes interventions. Mais je commence déjà à faire partie du passé... Je suis en train de louper le train Power Query...

Bien que persuadé par l'utilité et le potentiel de l'outil, je n'arrive pas vraiment à franchir le cap. Je ne sais pas ce qui bloque. Je pense qu'il me faudrait un mentor 😉.

RE

Max nous pardonnera de squatter son fil...

Au début on échangeait souvent avec Jean-Eric sur des questions PQ

Il est plus avancé que moi mais j'essaye de rester sur ses talons...

Mais il est déjà loin devant pour les formules 365

Il y a aussi un léger lien avec les autres aficionados de PQ

Sur DEVELOPPEZ, Stéphane est loin devant mais il répond gentiment et simplement à mes questions quant une de ses réponses m'interpelle.

C'est sûr que ce serait sympa de monter un groupe pour pédaler ensemble : on avance effectivement plus vite en se soutenant...

Rebonjour.

J'ai créé ma table de correspondance entre les différents types de produits et la catégorie dans laquelle il faut les regrouper.

Une fois cela fait, pourriez-vous m'expliquer la syntaxe à suivre pour lier le tableau de données à la table de correspondance ?

@mdo100 Merci, votre code fonctionne, il y a un léger souci de mise en forme (par exemple le résultat de la somme pour le premier lot est 21, le code affiche 21 comme prévu, mais il l'affiche autant de fois qu'il y a de lignes dans le lot, au lieu de l'afficher une fois) mais je vais le régler. Merci beaucoup.

Bonjour

Et le classeur avec la table de correspondance est où ?

Merci infiniment mdo100 et désolé du retard, le code suivant fonctionne parfaitement.

With Range("L2:L" & Range("A" & Rows.Count).End(xlUp).Row)
    .FormulaR1C1 = _
    "=SUM(SUMIFS(Tableau1[QUANTITY],Tableau1[PRODUCTS], {""apples"";""oranges"";""pears""},Tableau1[BATCH], RC3))"
    .Value = .Value
End With

Cela m'affiche bien les sommes des produits demandés. Ca me l'affiche par contre autant de fois qu'il y a de lignes dans le tableau de données originales, c'est-à-dire qu'il y a des doublons, mais c'est pas grave, je me suis débrouillé avec un autre code pour les supprimer.

J'avais cependant deux petites questions : si je veux afficher un nom par exemple "fruits" dans la cellule qui est juste devant la cellule d'une somme et ce pour toutes les sommes, comment je peux faire en utilisant cette structure ?

Je m'explique : j'ai par exemple la somme des produits du premier lot qui vaut 20 disons, et la somme des produits du deuxième lot qui vaut 38.

Disons qu'ils y a 3 lignes de produits pour le premier lot, et 4 pour le second.

Ton code m'affichera donc :

20

20

20

20

38

38

38

Comment faire, en utilisant ta structure, pour que le mot "fruits" soit affiché dans la cellule qui se trouve devant chacune des cellules contenant les 20 et 38 ?

Ce n'est pas grave si tu me dis comment faire pour cette situation là, c'est-à-dire pour les afficher en doublon, j'enlèverai les doublons en utilisant :

Sub SupressionDoublons()

    Dim r As Range
    [x2] = "=countifs(c$2:c2,c2,N$2:N2,N2)>1"
    With Range("a1").CurrentRegion.Resize(, 14)
        .AdvancedFilter 1, [x1:x2]
        If .Columns(1).SpecialCells(12).Count > 1 Then
            Set r = .Offset(1).Columns(14).SpecialCells(12)
        End If
        If .Parent.FilterMode Then .Parent.ShowAllData
    End With
    If Not r Is Nothing Then r.Delete xlShiftUp
    [x2] = ""
End Sub

Les sommes se trouvant dans la colonne N et les numéros de lot dans la colonne C.

Et deuxième question : si je veux garder ton code pour avoir les sommes de fruits, mais que j'ai d'autres produits que je veux additionner par lot également. Par exemple, j'ai 3 lots avec des fruits et des voitures, et je veux pour chaque lot obtenir la somme des quantités de fruits puis la somme des quantités de voitures.

Dois-je recopier le code existant, le coller en dessous et changer uniquement le nom des produits ?

Est-ce que les sommes vont s'afficher dans la même colonne et sans erreur ? Ou bien le fait que les fruits et dans le cas présent les voitures soient entremêlés dans le tableau des données créera un soucis ?

Pour plus de clarté, j'ai refais le tableau et je le joins ici.