Moyenne de n cellule en décaler
Bonjour/Bonsoir.
Je réalise un projet sur une bdd excel avec l'utilisation des fameuses macros.
Je découvre le langage VBA, donc vous voila prévenue de mon niveau
Ma bdd contient des relevés de débits (du mois étudié) d'une station et mon objectif finale est de réaliser un récapitulatif (tableau + graphique) .
Mais pour arriver à ce final, il y a plusieurs étapes évidemment.
Mise en contexte:
- j'ai réalisé des macros en amont pour arriver au stade suivant (sur une nouvelle feuille):
- j'ai une colonne nommée "Valeur" qui est variable, c'est à dire que selon le mois étudié et le nombre de mesure par heure (mis en place par la station), le nombre de lignes peut donc être de 10 000 ou 5 000.
- j'ai une autre colonne nommée "Jour", dans laquelle il y a la date des relevés, du type "20/03/2018" (cette colonne est aussi variable)
- une autre colonne "Heure" avec donc l'heure de la réalisation du relevé.
colonne"Valeur"= 15.2
colonne"Jour"=01/03/2019
colonne "Heure"=00:15
Mon problème est le suivant:
-j'aimerais créer une nouvelle colonne qui sera variable aussi (car elle prendrait en compte les trois précédentes qui le sont).
Dans cette colonne, je voudrais mettre en place une fonction (ou autre) qui me permette de calculer la moyenne de débits par heure.
Je m'explique, si par exemple le nombre de mesure par heure est 4 (relevé effectué tous les quarts d'heure) et que le nombre de jour dans le mois est 30, ALORS je voudrais voudrais dans cette nouvelle colonne, 30(jours) multiplié par 24(heures) =, 720 lignes/cellules. Et pour chaque heure avec la moyenne.
VOILA VOILA
Pour m'aider, j'ai juger utile de mettre en place un système d’input box pour demander le nombre de mesures par heure et de jour.
- j'ai donc, dans une cellule, le nombre de mesures réalisé par heure.
- j'ai également le nombre de jour dans le mois en question, mis dans une autre cellule.
En faisant des recherches sur le net, j'ai vu que la fonction décaler était pas mal pour ce genre de problème, mais malheureusement je n'ai pas réussi à obtenir le résultat voulu.
En espérant avoir une réponse positif de votre part.
Merci d'avance.
Bonjour saphya
Si j'ai presque tout compris de ton problème dans les grandes lignes, je n'ai en revanche pas bien compris l'objectif final et en particulier
Je m'explique, si par exemple le nombre de mesure par heure est 4 (relevé effectué tous les quarts d'heure) et que le nombre de jour dans le mois est 30, ALORS je voudrais voudrais dans cette nouvelle colonne, 30(jours) multiplié par 24(heures) =, 720 lignes/cellules. Et pour chaque heure avec la moyenne.
Serait il envisageable de voir ce que tu as déjà réalisé pour que nous nous fassions une idée de ce que tu cherches à faire. Même si "le projet" n'est pas abouti, au moins une description des cas concrets envisagés lors de l'étude de ce projet et surtout quel résultat final tu veux obtenir tant dans le fond que la forme.
En aparté...
J'ai un peu de mal à comprendre pourquoi un sujet "si simple" en apparence en tout cas, nécessite l'utilisation de "autant de" VBA comme tu as l'air de le dire.
Je ne comprends pas bien non plus, la nécessité des "input-box"...
Ou alors, quelque chose doit m'échapper dans ta présentation du sujet.
Hello!
Déjà je te remercie énormément d'avoir pris le temps de lire ma requête
Alors enfaite l'objectif final de mon projet serait de faire un tableau et un graph récapitulatifs des relevés du mois étudié, MAIS ici dans ma requête le but n'est pas de réaliser le tableau et le graph.
Mon problème est l'étape en amont
J'utilise vba car les relevés sont faits tous les mois, du coup j'ai 12 fichiers différents pour une année, et donc il est plus pratique de créer une macro pour faire ces étapes répétitives (surtout qu'on m'a demandé de travailler avec, je suis stagiaire).
Je te joint le fichier, je pense que c'est plus pratique
(dans cette feuille j'ai mis dans deux cellules: le nombre de jour du mois en question et le nombre de mesure réalisé par heure, ces elements font que le nombre de relevés/cellules est variable)
Si tu as besoin davantage d'info, fait moi signe !
Merci d'avance
Ps: j'ai pensé à faire un tableau croisé dynamique ( avec donc les relevés moyens, avec en ordonnée les jours du mois, et en abscisse les heures) si tu as une piste pour réaliser une macro je suis preneuse, sinon ce n'est point grave
Bonjour,
un TCD te le calcul tout seul.
Ta macro pourra donc se limiter à regrouper les données, actualiser le TCD, exploiter éventuellement le TCD.
Tableau Croisé Dynamique (TCD) : http://www.mdf-xlpages.com/modules/smartsection/item.php?itemid=109
et
http://www.mdf-xlpages.com/modules/publisher/item.php?itemid=130
eric
Bonjour, (eriiic)
Autre solution VBA pour le calcul des moyennes
Si tu as besoin de "commenter" le code dis-le mais je pense qu'il se comprend de lui-même !
Sub CalculerMoyenne()
Dim tabMesure()
Dim somMesure
Dim nbrMesure
Dim cptMesure
Dim ligDeb, colDeb
Dim ligFin, colFin
Dim colNbr
nbrMesure = Cells(1, 2)
somMesure = 0
ligDeb = 2
colDeb = 3
ligFin = Cells(Rows.Count, colDeb).End(xlUp).Row
colFin = 6
colNbr = colFin - colDeb + 1
tabMesure = Range(Cells(ligDeb, colDeb), Cells(ligFin, colFin + 1))
For cptMesure = 1 To UBound(tabMesure, 1)
somMesure = somMesure + tabMesure(cptMesure, UBound(tabMesure, 2) - 1)
If cptMesure Mod nbrMesure = 0 Then
tabMesure(cptMesure, colNbr + 1) = somMesure / nbrMesure
somMesure = 0
End If
Next
Cells(2, 8).Resize(UBound(tabMesure, 1), UBound(tabMesure, 2)) = tabMesure
End Sub@eriiic
Je ne savais pas que tu étais "fan" de mdf-XLPages
Bonjour,
fan est un bien grand mot
Disons que quand je trouve un tuto bien clair je le met de coté.
C'est plus complet que d'expliquer en 2-3 phrases.
eric
Bonjour
@eriiic
C'est une bonne méthode, il est vrai que les tutos de Didier son assez bien conçus - Certains sur des sujets rares d'ailleurs !
Bonjour
@saphya
Merci d'avoir aimé nos messages mais l'un au moins des deux est il la résolution du problème ?
Bonjour, (eriiic)
Autre solution VBA pour le calcul des moyennes
Si tu as besoin de "commenter" le code dis-le mais je pense qu'il se comprend de lui-même !
Sub CalculerMoyenne() Dim tabMesure() Dim somMesure Dim nbrMesure Dim cptMesure Dim ligDeb, colDeb Dim ligFin, colFin Dim colNbr nbrMesure = Cells(1, 2) somMesure = 0 ligDeb = 2 colDeb = 3 ligFin = Cells(Rows.Count, colDeb).End(xlUp).Row colFin = 6 colNbr = colFin - colDeb + 1 tabMesure = Range(Cells(ligDeb, colDeb), Cells(ligFin, colFin + 1)) For cptMesure = 1 To UBound(tabMesure, 1) somMesure = somMesure + tabMesure(cptMesure, UBound(tabMesure, 2) - 1) If cptMesure Mod nbrMesure = 0 Then tabMesure(cptMesure, colNbr + 1) = somMesure / nbrMesure somMesure = 0 End If Next Cells(2, 8).Resize(UBound(tabMesure, 1), UBound(tabMesure, 2)) = tabMesure End Sub@eriiic
Je ne savais pas que tu étais "fan" de mdf-XLPages
Salut
Merciii énormément d'avoir pris le temps de réaliser une macro pour ce problème !
Mais pourrais tu m'expliquais davantage les étapes stp
Est ce que le nombre de jour inscrit dans la cellule B2 t'a été utile ? (ou bien pas la peine de le demander à l'utilisateur ?)
Aussi, pour la colonne L (ou il y a un les moyennes par heures),
- à partir de cette colonne, aurait-il un moyen de créer une nouvelle colonne en skippant les cellules vides ? (j'aimerais avoir plusieurs colonnes pour avoir toutes les étapes)
- également une autre colonne avec les dates regroupées par heure associé au relevé moyen?
J’espère avoir bien expliqué
En tt cas merci encore
Bonjour
Merciii énormément d'avoir pris le temps de réaliser une macro pour ce problème !
=>Mais pourrais tu m'expliquais davantage les étapes stp
Donc je vais t'envoyer une version commentée !Si tu as besoin de "commenter" le code dis-le mais je pense qu'il se comprend de lui-même !
Je répondrais aux autres questions après...
Bonjour à tous.
Il s'agit d'un multipost (au moins 3 forums) où j'avais ce matin fait sensiblement la même proposition qu'Eriiic
Comme dit aussi là-bas :
Un TCD se fait une fois pour toute et pas en VBA : on modifie la source et on l'actualise.
Il peut pointer sur une fichier externe qu'on écrase au fil du temps...
Bonjour
Réponse à la question "Est ce que le nombre de jour inscrit dans la cellule B2 t'a été utile ?" => Non inutile
Pour info le dernier quantième d'un mois se calcule ainsi
=Jour( Date( uneAnnee, unMois, 1) -1)
uneAnnee (est une annee au choix), unMois (est le mois suivant du mois dont on veut connaitre le nombre de jour)
le 1 représente le 1er quantième de ce mois, le -1 retranche 1 à la date trouvée
Donc nous avons le jour qui précède le 1er quantième du mois choisi
autrement dit le dernier quantième du mois, donc le nombre de quantième du mois
Voici le code commenté :
' -------------------------------------------------------
' Calculer une moyenne sur un Nombre variable de lignes
' (c)2019, GLI73
' -------------------------------------------------------
Sub CalculerMoyenne()
Dim tabMesure() ' TABleau pour lire les MESUREs ligne / ligne
Dim somMesure ' pour pouvoir faire la SOMme des MESUREs
Dim nbrMesure ' pour pouvoir adapter la taille du tableau (voir code)
Dim cptMesure ' ComPTeur des lignes des differentes MESUREs
Dim ligDeb, colDeb ' pour noter la LIGne et la COLonne de DEBut des donnees
Dim ligFin, colFin ' ... la LIGne et la COLonne de FIN des donnees
Dim colNbr ' ... le NomBRe de COLonnes
nbrMesure = Cells(1, 2) ' le nombre de mesure est supposé être en cellule 1,2 (B1)
somMesure = 0 ' pour l'intant aucune sommme n'est realisee
ligDeb = 2 ' ligne supposee de debut des donnees (a adapter au besoin)
colDeb = 3 ' idem pour la colonne
' Rechercher la derniere ligne des donnees
' ROW donne une ligne
' END(xlUp) fait revenir a la derniere cellule non vide vers le haut (xlUp) - identique a [CTRL-HAUT]
' ROWS.COUNT deplace (virtuellement) le pointeur de cellule sur la derrniere ligne d'un onglet
' => Nous avons donc remonté depuis la derniere ligne d'Excel vers le haut jusqu'à la premiere cellule non vide
ligFin = Cells(Rows.Count, colDeb).End(xlUp).Row
colFin = 6 ' Arbitrairement la derniere colonne est fixee a la 6ème (a adapter au besoin)
colNbr = colFin - colDeb + 1 ' Trouver le nombre de colonnes utiles
' Initialiser le tableau des mesures avec led donnees utiles de ligDeb,colDeb à ligFin,colFin
' !!! Ajout d'une colonne supplementaire (colFin +1) pour pouvoir enregistrer directement dans le tableau
' la moyenne au fur et a mesure du calcul
tabMesure = Range(Cells(ligDeb, colDeb), Cells(ligFin, colFin + 1))
' Maintenant le traitement commence
' Parcours du tabelau des donnees
' Pour toutes les lignes du tableau (ubound(... , 1) designe la dimension ligne du tableau)
For cptMesure = 1 To UBound(tabMesure, 1)
' Sur chaque ligne SOMMER la colonne de mesure en additionnant la valeur de precedente
somMesure = somMesure + tabMesure(cptMesure, UBound(tabMesure, 2) - 1)
' Pour savoir SI nous avons le 'bon nombre de mesures' pour faire la moyenne
' l'astuce utilisee est la suivante :
' la fonction MOD permet de connaitre le RESTE d'une division Euclidienne donc le reste de la division entiere
' => en divisant le compteur de mesure par le nombre de mesure a prendre nous savons aussitôt
' si nous avons le nombre de mesure
If cptMesure Mod nbrMesure = 0 Then
' Alors nous pouvons calculer la moyenne
' en utilisant la colonne supplementaire ajoutee plus haut
tabMesure(cptMesure, colNbr + 1) = somMesure / nbrMesure
' Une fois la moyenne calculee et stockee dans le tableau re-initialisation de la SOMme des MESUREs
' pour le prochain groupe de mesure
somMesure = 0
End If ' Fin Si 'bon nombre de mesures'
Next ' Fin du Parcours du tableau... passer à la ligne suivante
' Copier le tableau contenant les moyennes
' Resize permet de redimensionner (virtuellement) une cellule
' les parametres sont la hauteur (nombre de lignes) et la lrageur (nombre de colonnes)
' La fonction Ubound permet de connaitre les dimesions d'un tableau
' Ubound(... ,1) donne le nombre de lignes
' Ubound(..., 2) donne le nombre de colonnnes
' Dans l'exemple le tableau est donc copié dans la celliule (redimensionnee) de ligne 2, colonne 8 (H2)
Cells(2, 8).Resize(UBound(tabMesure, 1), UBound(tabMesure, 2)) = tabMesure
End Subvous pouvez répéter la question(ainsi avoir deux colonnes qui me permettent de faire un tableau simple avec les relevés moyens par jour et heure)
- à partir de cette colonne, aurait-il un moyen de créer une nouvelle colonne en skippant les cellules vides ? (j'aimerais avoir plusieurs colonnes pour avoir toutes les étapes)
- également une autre colonne avec les dates regroupées par heure associé au relevé moyen?
Bonjour
@78chris
Même si je suis de ton avis... Je pense que beaucoup de gens (parmi les novices en particulier) n'aime pas les TCD car ils n'ont pas la logique d'esprit pour les concevoir !
Et il semble que saphya soit de ceux-là !
Perso j'avoue que j'évite les TCD je les trouve mal pratique à concevoir et pire encore mal utilisés au final par l'utilisateur final justement ! Mais cela n'engage que moi
Bonjour,
oui, cela n'engage que toi
De toute façon la demandeuse elle s'en fout.
Elle passe mettre un j'aime et se casse sans même mettre un retour...
Ca et le multipostage ça fait beaucoup pour une seule personne, qui de débrouillera sans moi la prochaine fois.
eric
RE
Il est 1000 fois plus facile d'apprendre à créer et exploiter des TCD que d'apprendre à coder correctement.
La logique en est très simple.
En tant que formateur, autant sur l'un que sur l'autre, je sais de quoi je parle...
En plus les TCD offrent une souplesse que ne permet pas un code prévu pour répondre à un cahier des charges précis.
Ici on obtient le résultat en quelques clics.
Je n'ai rien contre le VBA mais pas quand on fait aussi bien sans.
En plus Avec PowerPivot et PowerQuery les TCD offrent de plus en plus de possibilités...
Si tu as des cas qui te paraissent difficile à concevoir, on peut en parler pour voir où le bas blesse...
Bonjour
@eriiic
Je ne fais pas de commentaire sur l'appréciation, cependant que j'approuve tout à fait la chose autant que je désapprouve le procédé !
Effectivement il serait "sympa" pour les autres co-forumeurs de ...
Bonjour,
un TCD te le calcul tout seul.
Ta macro pourra donc se limiter à regrouper les données, actualiser le TCD, exploiter éventuellement le TCD.
Tableau Croisé Dynamique (TCD) : http://www.mdf-xlpages.com/modules/smartsection/item.php?itemid=109
et
http://www.mdf-xlpages.com/modules/publisher/item.php?itemid=130
eric
Bonjour
Je vais essayer d'en apprendre plus sur les TCD, merci bcp pour les liens
Enfaite ce que je veux c'est réaliser une macro qui m'aide à faire un tableau récapitulatif, car la macro va devoir être utilisé pour plusieurs fichiers. Du coup voila je ne sais pas si cela à sert à quelque chose de faire un TCD au final ...
Merci encore d'avoir pris le temps de me répondre
Bye
Bonjour
Réponse à la question "Est ce que le nombre de jour inscrit dans la cellule B2 t'a été utile ?" => Non inutile
Pour info le dernier quantième d'un mois se calcule ainsi
=Jour( Date( uneAnnee, unMois, 1) -1)
uneAnnee (est une annee au choix), unMois (est le mois suivant du mois dont on veut connaitre le nombre de jour)
le 1 représente le 1er quantième de ce mois, le -1 retranche 1 à la date trouvée
Donc nous avons le jour qui précède le 1er quantième du mois choisi
autrement dit le dernier quantième du mois, donc le nombre de quantième du mois
Voici le code commenté :
' ------------------------------------------------------- ' Calculer une moyenne sur un Nombre variable de lignes ' (c)2019, GLI73 ' ------------------------------------------------------- Sub CalculerMoyenne() Dim tabMesure() ' TABleau pour lire les MESUREs ligne / ligne Dim somMesure ' pour pouvoir faire la SOMme des MESUREs Dim nbrMesure ' pour pouvoir adapter la taille du tableau (voir code) Dim cptMesure ' ComPTeur des lignes des differentes MESUREs Dim ligDeb, colDeb ' pour noter la LIGne et la COLonne de DEBut des donnees Dim ligFin, colFin ' ... la LIGne et la COLonne de FIN des donnees Dim colNbr ' ... le NomBRe de COLonnes nbrMesure = Cells(1, 2) ' le nombre de mesure est supposé être en cellule 1,2 (B1) somMesure = 0 ' pour l'intant aucune sommme n'est realisee ligDeb = 2 ' ligne supposee de debut des donnees (a adapter au besoin) colDeb = 3 ' idem pour la colonne ' Rechercher la derniere ligne des donnees ' ROW donne une ligne ' END(xlUp) fait revenir a la derniere cellule non vide vers le haut (xlUp) - identique a [CTRL-HAUT] ' ROWS.COUNT deplace (virtuellement) le pointeur de cellule sur la derrniere ligne d'un onglet ' => Nous avons donc remonté depuis la derniere ligne d'Excel vers le haut jusqu'à la premiere cellule non vide ligFin = Cells(Rows.Count, colDeb).End(xlUp).Row colFin = 6 ' Arbitrairement la derniere colonne est fixee a la 6ème (a adapter au besoin) colNbr = colFin - colDeb + 1 ' Trouver le nombre de colonnes utiles ' Initialiser le tableau des mesures avec led donnees utiles de ligDeb,colDeb à ligFin,colFin ' !!! Ajout d'une colonne supplementaire (colFin +1) pour pouvoir enregistrer directement dans le tableau ' la moyenne au fur et a mesure du calcul tabMesure = Range(Cells(ligDeb, colDeb), Cells(ligFin, colFin + 1)) ' Maintenant le traitement commence ' Parcours du tabelau des donnees ' Pour toutes les lignes du tableau (ubound(... , 1) designe la dimension ligne du tableau) For cptMesure = 1 To UBound(tabMesure, 1) ' Sur chaque ligne SOMMER la colonne de mesure en additionnant la valeur de precedente somMesure = somMesure + tabMesure(cptMesure, UBound(tabMesure, 2) - 1) ' Pour savoir SI nous avons le 'bon nombre de mesures' pour faire la moyenne ' l'astuce utilisee est la suivante : ' la fonction MOD permet de connaitre le RESTE d'une division Euclidienne donc le reste de la division entiere ' => en divisant le compteur de mesure par le nombre de mesure a prendre nous savons aussitôt ' si nous avons le nombre de mesure If cptMesure Mod nbrMesure = 0 Then ' Alors nous pouvons calculer la moyenne ' en utilisant la colonne supplementaire ajoutee plus haut tabMesure(cptMesure, colNbr + 1) = somMesure / nbrMesure ' Une fois la moyenne calculee et stockee dans le tableau re-initialisation de la SOMme des MESUREs ' pour le prochain groupe de mesure somMesure = 0 End If ' Fin Si 'bon nombre de mesures' Next ' Fin du Parcours du tableau... passer à la ligne suivante ' Copier le tableau contenant les moyennes ' Resize permet de redimensionner (virtuellement) une cellule ' les parametres sont la hauteur (nombre de lignes) et la lrageur (nombre de colonnes) ' La fonction Ubound permet de connaitre les dimesions d'un tableau ' Ubound(... ,1) donne le nombre de lignes ' Ubound(..., 2) donne le nombre de colonnnes ' Dans l'exemple le tableau est donc copié dans la celliule (redimensionnee) de ligne 2, colonne 8 (H2) Cells(2, 8).Resize(UBound(tabMesure, 1), UBound(tabMesure, 2)) = tabMesure End Subvous pouvez répéter la question(ainsi avoir deux colonnes qui me permettent de faire un tableau simple avec les relevés moyens par jour et heure)
- à partir de cette colonne, aurait-il un moyen de créer une nouvelle colonne en skippant les cellules vides ? (j'aimerais avoir plusieurs colonnes pour avoir toutes les étapes)
- également une autre colonne avec les dates regroupées par heure associé au relevé moyen?
ou faire un dessin sous Excel du résultat souhaité ?
Merciiiiiii beaucoup pour tes explications TRÈS précises , je suis davantage éclairée
Pour ma requete à la fin, je vais essayer de trouver une solution par moi même et si je n'y arrive pas je te ferais signe.
Merci encore pour ton temps