Macro Excel - supp colonnes - moyenne & Co

Question

Bonjour,

Je suis en train de monter une macro Excel un peu complexe, tout du moins pour moi et j'aimerais savoir comment en VBA on peut réaliser l'action suivante :
J'ai 4 colonnes, une date d'action, une date de validation, une identifiant, une montant

Partie 1 - Suppression des lignes inutiles
Je dois d'abord  supprimer toutes les lignes qui ne comprendraient pas l'identifiant XXXXXX dans la troisième colonne. On aura préalablement renseigné cet identifiant en le rentrant dans la case "O1" par exemple.
Attention la première ligne qui comprend les intitulés des champs ne doit pas être prise en compte dans cette suppression. En effet elle contient les intitulés des colonnes et l'identifiant qui sera à comparer en "O1".

Partie 2 - Soustraction entre deux date pour obtenir une durée
Création d'une colonne 5 intitulé durée qui fera la soustraction "colonne 1" moins "colonne2" ou plus exactement date d'action - date de validation si on parle en intitulé de colonne.

Partie 3 - moyenne
il me faudrait faire la moyenne de la colonne 4 "montant" qui est en euros puis la moyenne de la colonne 5 "durée" qui est en jours. A savoir que nous ne savons pas exactement combien nous avons de ligne au total dans le document et dans quelle case mettre le résultat. Les cellules F2 et F4 par exemple.

Partie 4 - Enfin la colonne 5 concerne un ensemble de durée, il me faudrait savoir le pourcentage (le nombre de résultat de la colonne inférieur à x divisé par le nombre total de durée et le tout multiplié par 100) ou X est inférieur à 2 jours puis 5 jours puis 10 jours et enfin 15 jours.

Je réalise une étude comportementale sur Internet et j'ai beaucoup de fichiers à traiter. Si quelqu'un peut m'aider à réaliser cette macro, il me ferait gagner une bonne dizaine de jours.

Merci par avance,

Julien

eriiic · Answer

Bonjour,

Pour te faire patienter... ;-)
Pourrais tu préparer un petit fichier tel que tu l'imagines avec qcq données dedans, les zones où tu attends les résultats. Le déposer sur cijoint.fr et coller ici le lien fourni.

Pour les résultats à ajouter veux-tu une formule ou le résultat brut dans les cellules ?
supprimer toutes les lignes qui ne comprendraient pas l'identifiant XXXXXX dans la troisième colonne. On aura préalablement renseigné cet identifiant en le rentrant dans la case "O1" 
O1 est une partie de l'identifiant ?
Saisi dans une cellule, ou dans une boite de dialogue ? 
Un seul identifiant ou ça pourrait être une liste sur une plage et il faut dupliquer les feuilles ?
Pour la partie 4 sans doute d'autres questions et met met un exemple sur le classeur.

Pour moi ça ne sera pas avant ce we mais d'autres seront peut-être plus dispo

eric

milouck2 · Answer

avec plaisir :-)
je peux meme t'envoyer une explication si ca t'intéresse, c'est lié à la publicité sur internet.
A+ tard et merci par avance,
julien
ci-dessous un exemple de ficher, tout du moins une toute petite partie d'un fichier.
Les espaces représentent les séparations de colonnes
Date action	 DateVal	 Comid	Montant
31/08/2008	16/08/08	104399	44,82 €
31/08/2008	24/07/08	104400	26,00 €
31/08/2008	31/08/08	102986	82,48 €
31/08/2008	30/08/08	102986	57,69 €
31/08/2008	31/08/08	102985	28,09 €
31/08/2008	13/08/08	104400	40,38 €
31/08/2008	09/08/08	104400	51,46 €
31/08/2008	31/08/08	102985	30,35 €
31/08/2008	31/08/08	102985	44,82 €
31/08/2008	31/08/08	102985	58,07 €
31/08/2008	12/08/08	104400	23,68 €
30/08/2008	30/07/08	102985	54,04 €
30/08/2008	03/07/08	104399	29,52 €
30/08/2008	29/08/08	104400	54,57 €
30/08/2008	29/08/08	104400	26,92 €
30/08/2008	30/08/08	102985	114,05 €
30/08/2008	02/08/08	104400	61,45 €
30/08/2008	30/08/08	102985	63,49 €
30/08/2008	18/07/08	104399	46,66 €
30/08/2008	29/08/08	102985	30,41 €
30/08/2008	27/08/08	104400	24,08 €
30/08/2008	28/08/08	104400	31,18 €
30/08/2008	28/08/08	104400	37,21 €
30/08/2008	11/08/08	104400	30,94 €
30/08/2008	13/08/08	104400	27,68 €
29/08/2008	26/08/08	104400	78,26 €
29/08/2008	28/08/08	104400	115,64 €
29/08/2008	25/08/08	104399	28,18 €
29/08/2008	21/08/08	104400	32,02 €

milouck2 · Answer

je vois que le fichier passe pas bien en copier /coller, je peux envoyer un fichier par mail si nécessaire
A+

wilfried_42 · Answer

Bonjour à tous

le copier coller, c'est bien, mais si on doit reprendre tes données brute, il va falloir les traiter
tu vas sur https://www.cjoint.com/ et tu y place un fichier exemple avec des exemples qui correspondent aux questions

voir question 1 identifiant vide

@ te lire

milouck2 · Answer

Ok voici le lien d'un fichier 
https://www.cjoint.com/?jtkoZzPcRy
Merci

wilfried_42 · Answer

re:

ton fichier en retour, avec un bouton et une macro (Alt+F11 pour la voir)

voici la macro liée : 
Sub Go_macro()
    Dim c As Range
    'destruction des ligne ne comprenant pas d'identifiant
    Set c = Range("C2:C" & Range("C65536").End(xlUp).Row).Cells.SpecialCells(xlCellTypeBlanks)
    If Not c Is Nothing Then c.EntireRow.Delete ' controle s'il y a des cellules vides et detruit
    ' definition du calcul colonne B - Colonne C
    Range("E2").Formula = "=A2-B2"
    ' recopie de la formule
    Range("E2:E" & Range("C65536").End(xlUp).Row).FillDown
    ' faire les moyennes
    Range("F1") = "Moyenne des montants : "
    Range("F2").Formula = "=AVERAGE(D2:D" & Range("C65536").End(xlUp).Row & ")"
    Range("G1") = "Moyenne des jours"
    Range("G2").Formula = "=INT(AVERAGE(E2:E" & Range("C65536").End(xlUp).Row & "))"
End Sub


https://www.cjoint.com/?jtk1LoCV6Q

par contre pour le 4eme point, je n'ai rien compris

milouck2 · Answer

Déjà merci pour tous tes efforts
Alors je comprends pas exactement ce que génère la macro que tu as mises en PJ. Je vais essayer de t'expliquer un peu mieux en partant de ton document.
On a sur la 5eme colonne qui a été créée par ta macro des résultats avec des virgules. C 'est normalement une durée en nombre de jours car comme tu peux le voir sur la première colonne et la deuxième on à des dates. La première colonne étant toujours plus ancienne que la colonne deux sur la même ligne.

Ensuite sur la suppression des lignes (étape 1), on doit rentrer dans une cellule X ou Y du document un "Comid" (intitulé colonne 3) et par exemple dans ce document si l'on rentre "104400" puis que l'on clique sur le bouton lancement macro, toutes les lignes qui ont ce "comid" restent et toutes les autres lignes sont supprimées.
C'est à partir des lignes restantes que l'on calcule la durée (colonne 5, générée) et que l'on passe à l'étape 3.

Est ce que là je suis plus clair, je n'en suis pas sur....mais en tout cas merci pour ce coup de main de folie ;-)
A+ tard

wilfried_42 · Answer

re:

desolé, mais je ne vais pas modifier les 1419 lignes te ta base de données, car si tu regardes bien, dans la colonne B (meme si l'affichage montre une date), tu as une date et une heure etant donné que la date, est un nombre entier et qu'une heure est un nombre decimal, la date avec leur donne JJJJJJJ,hhhhhhhh

donc le resultat donné par la formule est juste et cela en fonction des données qu'on lui donne à manger
maintenant, ce n'est pas complique à modifier

2eme je te cite
Partie 1 - Suppression des lignes inutiles 
Je dois d'abord supprimer toutes les lignes qui ne comprendraient pas l'identifiant XXXXXX dans la troisième colonne. On aura préalablement renseigné cet identifiant en le rentrant dans la case "O1" par exemple. 
Attention la première ligne qui comprend les intitulés des champs ne doit pas être prise en compte dans cette suppression. En effet elle contient les intitulés des colonnes et l'identifiant qui sera à comparer en "O1".

tu parles de suppression donc personnellement j'ai pensé à des valeur non remplies, c'est un filtre est pas une suppression

voici donc la nouvelle procedure
Sub Go_macro()
    If Range("O1") = "" Then Range("A1").AutoFilter: Exit Sub
    ' definition du calcul colonne B - Colonne C
    Range("E2").Formula = "=int(A2)-int(B2)"
    ' recopie de la formule
    Range("E2:E" & Range("C65536").End(xlUp).Row).FillDown
    ' faire les moyennes
    Range("F1") = "Moyenne des montants : "
    Range("G1").Formula = "=subtotal(1,D2:D" & Range("C65536").End(xlUp).Row & ")"
    Range("H1") = "Moyenne des jours"
    Range("I1").Formula = "=subtotal(1,E2:E" & Range("C65536").End(xlUp).Row & ")"
    Range("A1").AutoFilter field:=3, Criteria1:=Range("O1")
End Sub


remplit O1, les filtres apparaissent, supprime la valeur, les donnes reapparaissent

milouck2 · Answer

T'as assuré un max. 
Bon je vais essayer d'abuser encore un tout petit peu pour que, n'ayons pas peur de le dire, tu fasses jusqu'au bout la macro car j'ai pas trop participé mise à part te faire comprendre ce que je voulais.

"La Partie 4" consiste à savoir dans la colonne 5 (intitulé  "durée" et que tu as générée grâce à la macro) il y a de valeurs inférieures à 2 jours (en pourcentage), combien de valeurs inférieurs à 3 jours, 5 jours et 10 jours.
Il faut d'abord connaitre combien on a de valeurs au total sur la colonne 5 (nombre de ligne moins la première) puis compter le nombre inférieur et égal à 2. Prendre cette valeur divisé par le total et le tout multiplié par 100 pour obtenir un pourcentage.
Faire la même chose pour 3, 5 et 10 en mettant tous les résultats sur la même page

Si tu as encore la réponse merci par avance,

Julien

wilfried_42 · Answer

re:

en fonction de ce que j'ai compris : un macro, une fonction

Sub Go_macro()
    Dim c As Range
    If Range("O1") = "" Then Range("A1").AutoFilter: Exit Sub
    ' definition du calcul colonne B - Colonne C
    Range("E2").Formula = "=int(A2)-int(B2)"
    ' recopie de la formule
    Range("E2:E" & Range("C65536").End(xlUp).Row).FillDown
    ' faire les moyennes
    Range("F1") = "Moyenne des montants : "
    Range("G1").Formula = "=subtotal(1,D2:D" & Range("C65536").End(xlUp).Row & ")"
    Range("H1") = "Moyenne des jours"
    Range("I1").Formula = "=subtotal(1,E2:E" & Range("C65536").End(xlUp).Row & ")"
    Range("A1").AutoFilter field:=3, Criteria1:=Range("O1")
    Set c = Range("E2:E" & Range("E65536").End(xlUp).Row).Cells.SpecialCells(xlCellTypeVisible)
    Range("F2:H65536").ClearContents
    Range("F1").Select
    SendKeys "{DOWN}": DoEvents
    Selection.Value = "Nombre total : "
    Selection.Offset(0, 1) = c.Cells.Count
    SendKeys "{DOWN}": DoEvents
    Selection = "Moins de 2 jours"
    Selection.Offset(0, 1) = nb_si(c, -999999, 1)
    Selection.Offset(0, 2) = Selection.Offset(0, 1) / c.Cells.Count * 100
    SendKeys "{DOWN}": DoEvents
    Selection.Value = "moins de 2 à 3 jours"
    Selection.Offset(0, 1) = nb_si(c, 2, 3)
    Selection.Offset(0, 2) = Selection.Offset(0, 1) / c.Cells.Count * 100
    SendKeys "{DOWN}": DoEvents
    Selection.Value = "moins de 4 à 5 jours"
    Selection.Offset(0, 1) = nb_si(c, 4, 5)
    Selection.Offset(0, 2) = Selection.Offset(0, 1) / c.Cells.Count * 100
    SendKeys "{DOWN}": DoEvents
    Selection.Value = "moins de 6 à 10 jours"
    Selection.Offset(0, 1) = nb_si(c, 6, 10)
    Selection.Offset(0, 2) = Selection.Offset(0, 1) / c.Cells.Count * 100
    SendKeys "{DOWN}": DoEvents
    Selection.Value = "plus de 10 jours"
    Selection.Offset(0, 1) = nb_si(c, 10, 999999)
    Selection.Offset(0, 2) = Selection.Offset(0, 1) / c.Cells.Count * 100
End Sub
Function nb_si(target As Range, deb As Long, fin As Long) As Long
    Dim c As Range
    nb_si = 0
    For Each c In target
        If c >= deb And c <= fin Then nb_si = nb_si + 1
    Next
End Function

milouck2 · Answer

Merci merci merci c'est super sympa.
C'est pas tout à fait ca mais je pense que je vais me débrouiller.
Tu m'as fait :
Moins de 2 jours
moins de 2 à 3 jours
moins de 4 à 5 jours
moins de 6 à 10 jours
plus de 10 jours

En fait g besoin de
Moins de 2 jours
moins de 3 jours (soit moins de 2 jours + de 2 à 3 jours)
moins de 5 jours
moins de 6 jours
moins 10 jours

Mais je vais essayer de modifier la macro tout seul comme un grand histoire de mettre une petite pierre à l'édifice :-)

Encore merci

Julien

wilfried_42 · Answer

re:

un indice c'est ici 
    Selection.Value = "moins de 2 à 3 jours" ' le titre
    Selection.Offset(0, 1) = nb_si(c, 2, 3) ' 2 borne inferieure, 3 borne superieure
    Selection.Offset(0, 2) = Selection.Offset(0, 1) / c.Cells.Count * 100

tu vois j'ai lu une : entre ce que je pense, ce que j'explique et ce que l'autre crois comprendre.......

finalement je n'avais pas tout compris

milouck2 · Answer

G tout réussi comme il faut, comme tu m'as dit. En même temps c'était super simple après tout le boulot que t'avais fait.
Maintenant j'ai un petit problème où je rentre le chiffre dans la case "O1" (début de la macro) et qui permet de supprimer toute les lignes qui ne possèdent pas ce ComID. 
En fait je me suis rendu compte que je devais prendre en compte plusieurs comID (intitulé colonne) qui seront pris en compte par copier/coller d'un autre fichier et qui apparaitront avant lancement de la macro en O1, O2, O3, .. En fait potentiellement dans toute la colonne même si la plupart du temps les 30 premières cases de la colonne O seront les seules à servir.
Au lieu de prendre en compte seulement le ComID rentré en O1, il faudrait que je puisse prendre en compte tous les Comid que je rentre dans la colonne.
J'ai testé en remplaçant dan la macro ("O1") par ("O1", "O2", "O3") et ("O1, O2, O3") mais le débogeur de macro me dit que le nombre de "range" est trop important ou alors ca plante
As tu une solution miracle pour me sortir encore une fois de la m.... :-)

merci par avance et désolé car c'est chiant de se remettre dans une macro une semaine après.
A+

Macro Excel - supp colonnes - moyenne & Co

13 réponses

Discussions similaires

Newsletters