Sumifs VBA - somme multicriteres d un tableau dans un autre tableau

Version imprimable

3 pièce(s) jointe(s)

Bonjour à tous,

J'ai un tableau ("Monthly Hours") avec plusieurs informations concernant différentes machines de différents clients avec des heures machines réalisées par mois, et je voudrais être capable de faire dans un deuxième tableau ("projection") la somme des heures propres à un client et sur une période donnée (la période est définie dans "projection" en cellules C1 et E1) , par type de machines, puis par catégorie.

Tableau dont sont extraites les donnees "Monthly Hours"
Pièce jointe 411325

Tableau dans un deuxieme onglet ou sont reprises les données "Projection"
Pièce jointe 411327

Je veux effectuer ces calculs en VBA car les tableaux peuvent être ajournées régulièrement, et l'utilisation de formules dans excel n'est pas pratique pour cela.

Cependant, n'étant pas un expert en VBA, je le conçois que j'utilise peut-etre une methode archaique.

J'utilise un sumifs pour calculer les sommes aux critères que je souhaite, mais je me rends compte de deux problèmes :
1. Cela calcul la somme que pour le mois du début de la période (ici, mars 2018) et non pas sur toute la période.
2. Je n'arrive à avoir la somme des données que pour le premier critère "machine type", mais pas pour le second critère "category"

La ligne de code relative à mon sumifs se trouve en rouge dans le code ci dessous

Merci d avance pour votre aide
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 Sub test() ' ' test Macro ' ' Dim i, j, mons, monsend, years, yearend, colstart, colend, nbrcolsumif, yeartest As Integer Dim condition As Range Dim startdate, enddate As Date col = 14 colstart = 14 colend = 14 startdate = Sheets("Projection").Cells(1, 3) ' date de début de la periode voulue enddate = Sheets("Projection").Cells(1, 5) ' date de fin de la periode voulue mons = Month(startdate) years = year(startdate) monsend = Month(enddate) yearend = year(enddate) Sheets("Monthly Hours").Activate Do While (years <> year(Sheets("Monthly Hours").Cells(4, colstart).value) Or mons <> Month(Sheets("Monthly Hours").Cells(4, colstart).value)) colstart = colstart + 1 Loop While (yearend <> year(Sheets("Monthly Hours").Cells(4, colend).value) Or monsend <> Month(Sheets("Monthly Hours").Cells(4, colend).value)) colend = colend + 1 Wend i = 3 j = 2 While Not IsEmpty(Sheets("Projection").Cells(2, j)) While Not IsEmpty(Sheets("Projection").Cells(i, 1)) nbrcolsumif = colend - colstart Sheets("Projection").Cells(i, j).value = Application.WorksheetFunction.SumIfs(Sheets("Monthly Hours").Range(Cells(5, colstart), Cells(1048576, colend)), Sheets("Monthly Hours").Range(Cells(5, 4), Cells(1048576, 4 + nbrcolsumif)), Sheets("Projection").Cells(i, 1), Sheets("Monthly Hours").Range(Cells(5, 1), Cells(1048576, 1 + nbrcolsumif)), Sheets("Projection").Cells(2, j)) i = i + 1 Wend j = j + 1 i = 3 Wend End Sub

13/09/2018, 14h40
tchoops

Désolé, j'avais oublié de surligner en rouge les lignes relatives au sumifs dans le code, mais je pense que cela ne vous aura pas échappé

Citation:

Envoyé par tchoops

Code:

Sheets("Projection").Cells(i, j).value = Application.WorksheetFunction.SumIfs(Sheets("Monthly Hours").Range(Cells(5, colstart), Cells(1048576, colend)), Sheets("Monthly Hours").Range(Cells(5, 4), Cells(1048576, 4 + nbrcolsumif)), Sheets("Projection").Cells(i, 1), Sheets("Monthly Hours").Range(Cells(5, 1), Cells(1048576, 1 + nbrcolsumif)), Sheets("Projection").Cells(2, j))
13/09/2018, 15h04
78chris

1 pièce(s) jointe(s)
Bonjour

Une solution sans VBA
- Mise sous forme de tableau structuré de la source
- Décroisement du tableau par PowerQuery (intégré à 2016, en addon sur 2010 et 2013)
- 2 TCD avec segments communs pour le choix de la période (on pourrait utiliser une chronologie si 2013 et plus). On pourrait faire un seul TCD avec la période en étiquette de lignes et pliage/dépliage du détail...
Edit : à noter que "tableaux peuvent être ajournées régulièrement" ne veut rien dire, ajourner voulant dire reporter...
La solution proposée suivra l'évolution du tableau source en utilisant Données, Actualiser tout.
13/09/2018, 19h20
tchoops

Bonjour 78chris,

Tout d'abord, en effet, "ajourné" n'était pas le terme approprié. Merci pour la remarque et la correction.

Je t'avoue que l'idée est séduisante et semble assez directe. Je n'y avais pas pensé car mes connaissances au niveau tableau croisé dynamique s'arrêtent à faire la somme de données de plusieurs occurrences, sans aller bien au-delà.

Ta solution me convient, par contre :

1. je n'ai jamais utilisé power qwery.
2. je n'ai jamais fait de mise en forme structuré d'un tableau

Serait-ce trop demandé si tu pouvais faire un descriptif étape par étape pour arriver à ton résultat ou d'enregistrer en vidéo et de partager cela en ligne ?

Comme, je sais que c'est un peu "trop demandé", peut-être saurais-tu à tout hasard où je pourrais trouver des tutoriels pour des cas similaires au mien sans avoir à passer par une formation complète ?

Quoi qu'il en soit merci pour ta résolution :)
13/09/2018, 21h53
78chris
RE

Pour les tableaux structurés déjà lire cela http://www.excel-formations.fr/Trucs...Tableaux01.php
- Mise sous forme de tableau structuré de la source
  
  Il suffit de se placer dans une cellule du tableau et depuis l'onglet Accueil : Mettre sous forme de Tableau.
  Excel sélectionne automatiquement la plage. Dans ton cas, comme tu as des dates en tête de colonnes et que tu risques d'allonger la période, j'ai indiqué qu'il n'y avait pas d'en-tête puis ai désactivé l'affichage de la ligne d'en-tête supplémentaire que cela a généré. J'ai nommé le tableau Tdata et appliqué le 1er style qui ne modifie pas ta mise en forme
- Décroisement du tableau par PowerQuery (intégré à 2016, en addon sur 2010 et 2013 donc à installer avant)
  
  Se placer dans une cellule puis dans l'onglet PowerQuery si 2010 ou 2013, Données si 2016, A partir d'un Tableau
  On se retrouve dans l’interface PowerQuery avec une requête portant le nom du tableau. On y voit comme en-têtes colonne1 à colonne n (ajoutées par la manip précédente)
  Dans l'onglet Transformer, Utiliser la 1ère ligne pour les en-têtes
  sélectionner toutes les colonnes de dates, puis Onglet Transformer, Supprimer le tableau croisé dynamique (Dépivoter les colonnes sur 2016)
  Sélectionner la colonne date, Onglet Accueil, Type de données : date et heure. Puis refaire avec Date (en confirmant une action supplémentaire et non le remplacement de la 1ère)
  Sortir par le 1er bouton en haut à gauche, Fermer et Charger dans, Connexion seulement
- 2 TCD avec segments communs pour le choix de la période (on pourrait utiliser une chronologie si 2013 et plus). On pourrait faire un seul TCD avec la période en étiquette de lignes et pliage/dépliage du détail...
  Les 2 TCD sont assez simples : il suffit que tu observes ce que j'ai fait pour reproduire. Les seuls points particuliers sont
  
  partir d'une cellule vide puis Insertion, TCD, Utiliser une source de données Externes, sélectionner Tdata
  si tu as 2010, ajouter la date en étiquette de ligne puis la grouper par Année et mois puis décocher les champs Années et mois dans les champs affichés par le TCD. Sinon rien à faire de plus ici
- Si 2010, ajouter les segments Années et Date et les connecter aux deux TCD http://www.excel-formations.fr/Trucs_astuces/TCD04.php.
  Si 2013 ou 2016 ajouter une chronologie et la connecter aux 2 TCD
Comme tu le vois rien de très compliqué...
13/09/2018, 23h18
tchoops

Merci Chris pour ce temps passé sur ma requête. Je regarde cela demain à tête reposé mais en effet, cela n'a pas l'air compliqué:)