IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Macros et VBA Excel Discussion :

SumIf dans un Tableau


Sujet :

Macros et VBA Excel

  1. #1
    Membre régulier
    Femme Profil pro
    Inscrit en
    juin 2012
    Messages
    226
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Localisation : France

    Informations forums :
    Inscription : juin 2012
    Messages : 226
    Points : 84
    Points
    84
    Par défaut SumIf dans un Tableau
    Bonjour,
    J'ai un tableau de données (issu d'un Query).
    Je voudrais faire un somme.si sur ces données. (Somme des vendus par date)
    Colonnes : Date - Vendus
    Ce code me retourne 0 (je n'ai pas réussi non plus avec un somme.si dans excel)
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
     
             For Each r In Range("T_Données").ListObject.ListRows
                Sheets("a").Cells(Lg, 4) = Application.WorksheetFunction.SumIf(r.Range(r.Parent.ListColumns("vendus").Index), r.Range(r.Parent.ListColumns("date").Index), Sheets("b").Cells(Lg, 1))
             Next r
    Comment faire ?
    Merci

  2. #2
    Community Manager

    Avatar de Malick
    Homme Profil pro
    Community Manager
    Inscrit en
    juillet 2012
    Messages
    8 514
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Sénégal

    Informations professionnelles :
    Activité : Community Manager
    Secteur : Conseil

    Informations forums :
    Inscription : juillet 2012
    Messages : 8 514
    Points : 79 073
    Points
    79 073
    Billets dans le blog
    15
    Par défaut
    Salut,

    Je pense qu'un TCD devrait faire l'affaire sans lignes de code.
    Vous avez envie de contribuer au sein du Club Developpez.com ? Contactez-nous maintenant !
    Vous êtes passionné, vous souhaitez partager vos connaissances en informatique, vous souhaitez faire partie de la rédaction.
    Il suffit de vous porter volontaire et de nous faire part de vos envies de contributions :
    Rédaction d'articles/cours/tutoriels, Traduction, Contribution dans la FAQ, Rédaction de news, interviews et témoignages, Organisation de défis, de débats et de sondages, Relecture technique, Modération, Correction orthographique, etc.
    Vous avez d'autres propositions de contributions à nous faire ? Vous souhaitez en savoir davantage ? N'hésitez pas à nous approcher.

  3. #3
    Membre régulier
    Femme Profil pro
    Inscrit en
    juin 2012
    Messages
    226
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Localisation : France

    Informations forums :
    Inscription : juin 2012
    Messages : 226
    Points : 84
    Points
    84
    Par défaut
    Je ne souhaite pas passer par un TCD, je fais des tests sur les données et je ne veux faire cette action que sur une partie des données qui répondent à certains critères, je fais autre chose selon les cas.

  4. #4
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    novembre 2003
    Messages
    17 809
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 54
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : novembre 2003
    Messages : 17 809
    Points : 51 206
    Points
    51 206
    Billets dans le blog
    94
    Par défaut
    Salut

    Ta variable lg n'est pas incrémentée dans la boucle => tu écris toujours ton résultat dans la même cellule. A la fin de la boucle, tu as donc dans range(lg,4) la valeur du sumif calculé sur la dernière ligne du tableau.

    Dans la mesure où tu passes sur toutes les lignes avec la même fonction, tu pourrais:
    • soit placer la formule dans le "tableau" de résultat de la feuille A;
    • soit utiliser un TCD comme le propose Malick, car malgré ce que tu dis, c'est exactement le même traitement sur toutes les lignes.



    Du coup, il serait probablement intéressant de nous dire ce que tu veux réaliser en français (sans jargon Excel ou VBA), avec une copie d'écran dépersonnalisée du tableau de départ et de la feuille d'arrivée


    En règle générale, SUMIF (SOMME.SI en Excel) est obsolète et remplacé dans les versions actuelles par SUMIFS (SOMME.SI.ENS en Excel) qui accepte 127 conditions. L'ordre des arguments n'étant pas identique entre les deux fonctions, il conviendrait selon moi d'utiliser la version actuelle de la fonction
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  5. #5
    Membre régulier
    Femme Profil pro
    Inscrit en
    juin 2012
    Messages
    226
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Localisation : France

    Informations forums :
    Inscription : juin 2012
    Messages : 226
    Points : 84
    Points
    84
    Par défaut
    Bonjour, j'incrémente lg dans mon code, j'ai juste mis la ligne avec le calcul.

    Dans mon tableau, j'ai des lignes avec date, famille, lot, quantité vendue ou quantité fabriquée.
    Date - Famille - Lot - Qté vendue - qté fabriquée
    01/05 - 1 - 150 - 2
    01/05 - 3 - 152 - 50
    01/05 - 1 - 150 - - 250
    02/05 - 3 - 153 - - 100
    02/05 - 4 - 150 - 10

    Ma clé est famille-lot.
    Lorsque pour la même famille et le même lot, la quantité fabriquée est supérieure à la quantité vendue, je veux créer une fiche de stock avec les quantités vendues et/ou fabriquées par jour
    01/05 - famille 1 - lot 150 - qté fabriquée 300 - qté vendue 200 - stock (calculé) 100
    02/05 - famille 1 - lot 150 - qté vendue 50 - stock (calculé) 50

    Lorsque pour la même famille et le même lot, la quantité fabriquée est inférieure à la quantité vendue, je veux mettre dans un onglet Erreur
    Famille 1 - lot 150 - qté fabriquée 300 - qté vendue 400
    Famille 1 - lot 152 - qté fabriquée 200 - qté vendue 201


    Je ne fais rien si la quantité fabriquée correspond à la quantité vendue

    Si besoin de captures d'écran, je pourrais le faire demain.

    Merci

  6. #6
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    novembre 2003
    Messages
    17 809
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 54
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : novembre 2003
    Messages : 17 809
    Points : 51 206
    Points
    51 206
    Billets dans le blog
    94
    Par défaut
    Tu incrémentes lg dans ton code, mais pas dans la boucle que tu nous montres et que je reproduis ci-dessous. Donc, lg est toujours le même pour chaque ligne du tableau => c'est la valeur calculée pour la dernière ligne (le dernier r de la boucle) qui se trouvera dans Sheets("a").Cells(Lg, 4)


    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
             For Each r In Range("T_Données").ListObject.ListRows
                Sheets("a").Cells(Lg, 4) = Application.WorksheetFunction.SumIf(r.Range(r.Parent.ListColumns("vendus").Index), r.Range(r.Parent.ListColumns("date").Index), Sheets("b").Cells(Lg, 1))
             Next r
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  7. #7
    Membre régulier
    Femme Profil pro
    Inscrit en
    juin 2012
    Messages
    226
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Localisation : France

    Informations forums :
    Inscription : juin 2012
    Messages : 226
    Points : 84
    Points
    84
    Par défaut
    sumif me renvoie la valeur 0 à chaque ligne.
    Lorsque je fais somme.si dans une cellule, j'ai 0 également comme résultat.

  8. #8
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    novembre 2003
    Messages
    17 809
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 54
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : novembre 2003
    Messages : 17 809
    Points : 51 206
    Points
    51 206
    Billets dans le blog
    94
    Par défaut
    A mon avis, tu as des valeurs qui sont en texte d'un côté et en numérique de l'autre. Vérifie déjà cela. Cela ne change rien au problème d'incrémentation de Lg...

    Cela dit, j'ai une solution via Power Query sans VBA:
    • On importe le tableau dans Power Query;
    • On regroupe sur la date, la famille et le lot;
    • On ajoute une colonne pour calculer la différence Fabriquée - Vendue;
    • On crée deux références à cette requête, l'une pour filtrer les valeurs positives, l'autre filtrer les valeurs négatives.



    Ici, j'ai mis les tableaux les uns en dessous des autres pour la copie d'écran, mais ce n'est bien sûr à ne pas faire et l'on préfèrera renvoyer les tableaux sur des feuilles disctinces.

    Nom : 2021-05-19_212025.png
Affichages : 90
Taille : 18,7 Ko


    Requête de calcul du stock:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    let
        Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
        TypesAdaptés = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Famille", Int64.Type}, {"Lot", Int64.Type}, {"Qté vendue", Int64.Type}, {"Qté fabriquée", Int64.Type}}),
        LignesGroupées = Table.Group(TypesAdaptés, {"Date", "Famille", "Lot"}, {{"Vendu", each List.Sum([Qté vendue]), type nullable number}, {"Fabriqué", each List.Sum([Qté fabriquée]), type nullable number}}),
        StockCalculé = Table.AddColumn(LignesGroupées, "Stock", each List.Sum({[Fabriqué],-[Vendu]}))
    in
        StockCalculé
    Fichiers attachés Fichiers attachés
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  9. #9
    Membre régulier
    Femme Profil pro
    Inscrit en
    juin 2012
    Messages
    226
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Localisation : France

    Informations forums :
    Inscription : juin 2012
    Messages : 226
    Points : 84
    Points
    84
    Par défaut
    Bonjour,
    J'ai essayé mais j'ai encore des erreurs.

    Il faut respecter la casse ? (j'avais lot au lieu de Lot et un message d'erreur).

    Nom : Capture5.PNG
Affichages : 84
Taille : 22,7 Ko

    Voici des captures d'écran

    Mes données de base

    Nom : Capture3.PNG
Affichages : 83
Taille : 15,1 Ko

    Le query
    Nom : Capture.PNG
Affichages : 84
Taille : 80,3 Ko

    Les données de la colonne Qté fabriquée
    Nom : Capture2.PNG
Affichages : 85
Taille : 31,1 Ko

    Le résultat
    Nom : Capture4.PNG
Affichages : 85
Taille : 10,4 Ko
    Nom : Capture6.PNG
Affichages : 80
Taille : 7,5 Ko

    Merci pour votre aide

  10. #10
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    novembre 2003
    Messages
    17 809
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 54
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : novembre 2003
    Messages : 17 809
    Points : 51 206
    Points
    51 206
    Billets dans le blog
    94
    Par défaut
    Oui, Power Query est "case sensitive" et c'est "un peu" perturbant lorsque l'on vient du monde Excel et VBA. On s'y fait vite.

    Autre différence avec Excel, Power Query ne mélange pas les types de données, et il est donc impossible d'additionner des valeurs dont certaines sont Null. On peut éviter cela soit avec des IF, soit en passant par List.Sum qui permet les Null et n'en tient tout simplement pas compte.

    Il est préférable de renommer les étapes d'une requête et d'éviter les espaces. Ca alourdit la syntaxe car chaque étape reprend le nom de la précédente et les noms avec espace sont exprimés de façon verbeuse: Je préfère TypesAdaptés à #"Types Adaptés", perso. Dans le fichier que j'ai donné précédemment, j'ai renommé systématiquement les requêtes pour alléger le code M.


    Dans ce que tu as montré, il n'y a pas de qtés fabriquées => il est malaisé de tester. Dans "mon" fichier, j'avais mis quelques qtés fabriquées pour pouvoir créer un jeu de test correct.


    Tu peux visualiser tout le script d'une requête via l'outil"Editeur avancé" que tu trouves sur les onglets Accueil et Affichage. Tu peux ainsi voir que pour la première requête, la plus importante puisque les deux tableaux de résultat en découlent, que le code M est assez léger en fait, et reprend bien les étapes que j'ai développées dans la réponse précédente.

    Code Power query : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    let
        Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
        TypesAdaptés = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Famille", Int64.Type}, {"Lot", Int64.Type}, {"Qté vendue", Int64.Type}, {"Qté fabriquée", Int64.Type}}),
        LignesGroupées = Table.Group(TypesAdaptés, {"Date", "Famille", "Lot"}, {{"Vendu", each List.Sum([Qté vendue]), type nullable number}, {"Fabriqué", each List.Sum([Qté fabriquée]), type nullable number}}),
        StockCalculé = Table.AddColumn(LignesGroupées, "Stock", each List.Sum({[Fabriqué],-[Vendu]}))
    in
        StockCalculé

    A toi d'adapter et d'essayer sur tes propres données, c'est ainsi que l'on apprend le mieux
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  11. #11
    Membre régulier
    Femme Profil pro
    Inscrit en
    juin 2012
    Messages
    226
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Localisation : France

    Informations forums :
    Inscription : juin 2012
    Messages : 226
    Points : 84
    Points
    84
    Par défaut
    Citation Envoyé par Pierre Fauconnier Voir le message
    Dans ce que tu as montré, il n'y a pas de qtés fabriquées => il est malaisé de tester. Dans "mon" fichier, j'avais mis quelques qtés fabriquées pour pouvoir créer un jeu de test correct.
    Nom : Capture.PNG
Affichages : 84
Taille : 25,4 Ko


    Citation Envoyé par Pierre Fauconnier Voir le message
    Autre différence avec Excel, Power Query ne mélange pas les types de données, et il est donc impossible d'additionner des valeurs dont certaines sont Null. On peut éviter cela soit avec des IF, soit en passant par List.Sum qui permet les Null et n'en tient tout simplement pas compte.

    Il est préférable de renommer les étapes d'une requête et d'éviter les espaces. Ca alourdit la syntaxe car chaque étape reprend le nom de la précédente et les noms avec espace sont exprimés de façon verbeuse: Je préfère TypesAdaptés à #"Types Adaptés", perso. Dans le fichier que j'ai donné précédemment, j'ai renommé systématiquement les requêtes pour alléger le code M.
    Les Null ne sont pas dans mes données de départ, peut-on les éviter à la source ?
    Je vais chercher des infos sur l'utilisation des Query, c'est tout nouveau pour moi.
    Merci

  12. #12
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    novembre 2003
    Messages
    17 809
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 54
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : novembre 2003
    Messages : 17 809
    Points : 51 206
    Points
    51 206
    Billets dans le blog
    94
    Par défaut
    Null veut dire "cellule sans info" pour Power Query, car Power Query ne connait pas le vide. Lorsque l'on importe une table dans Power Query, "il remplit toute la grille" et place la valeur Null pour les cellules vides.

    Normalement, tu devrais t'en tirer avec ce que j'ai donné comme exemple et comme explications. Si tu coinces, donne le script de la requête pour que l'on puisse voir ce que tu as fait, car voir l'interface avec juste les noms des étapes ne nous aide pas beaucoup.
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  13. #13
    Membre régulier
    Femme Profil pro
    Inscrit en
    juin 2012
    Messages
    226
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Localisation : France

    Informations forums :
    Inscription : juin 2012
    Messages : 226
    Points : 84
    Points
    84
    Par défaut
    C'est bon, j'ai des résultats, j'avais des lignes où la colonne "Famille" n'était pas renseignée, d'où les erreurs depuis ce matin je suppose.


    Par contre, la requête ne fait pas tout à fait ce que je veux. J'obtiens ceci
    Nom : Capture.PNG
Affichages : 77
Taille : 5,8 Ko

    J'aimerais que le stock du 05/01 soit de 415, le 08/01 : 410 et de 0 le 11/01
    Nom : Capture.PNG
Affichages : 79
Taille : 5,5 Ko

Discussions similaires

  1. [XL-2016] SumIfs dans un tableau de Variants
    Par symbiioz dans le forum Macros et VBA Excel
    Réponses: 2
    Dernier message: 04/12/2019, 10h36
  2. alterner les couleurs dans un tableau avec xsl
    Par Eithelgul dans le forum XSL/XSLT/XPATH
    Réponses: 14
    Dernier message: 03/05/2015, 23h29
  3. Réponses: 2
    Dernier message: 23/11/2003, 18h44
  4. Réponses: 4
    Dernier message: 10/10/2003, 18h04
  5. verification de doublons dans un tableau
    Par bohemianvirtual dans le forum C
    Réponses: 11
    Dernier message: 25/05/2002, 12h21

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo