XL 2010 Nettoyage des données

[meryach]

Bonjour,

J'ai une série de données que je souhaite nettoyer (fichier en attachement)
ce que j'aimerai faire c'est :
-- d'abord supprimer les lignes avec les dates des weekends et de jours fériés
-- si le type (colonne F) = A (par exemple), garder juste les heures entre 10:00 et 16:00 car hors ces heures les données m'intéresse pas.
est ce que c'est possible de faire ce nettoyage avec Excel 🤔?
Je vous remercie par avance pour votre aide.
Bonne journée 🥰

 

[sylvanu]

Bonjour Meryach,
Un essai en PJ.
Avec une feuille (masquée) pour le calcul des jours fériés.
( j'ai supposé que toutes les dates ne pouvaient pas être< à l'année précédente et > à l'année suivante)
La mise à jour est automatique quand on sélectionne la feuille "Resultat", avec cette macro :

Sub Worksheet_Activate()
    Dim DL, f, r
    [A:F].ClearContents                                 ' Effacement résultats précédents
    Application.ScreenUpdating = False                  ' Ecran figé
    With Sheets("Feuil1")
        DL = .[A65000].End(xlUp).Row                    ' Dernière ligne
        Range("A1:F" & DL) = .Range("A1:F" & DL).Value  ' Copier coller valeurs de Feuil1 vers résultat
    End With
    DL = [A65000].End(xlUp).Row                         ' Dernière ligne de Résultat
    f = "=SI(OU(C2<10/24;C2>16/24;NB.SI(JoursFériés;B2)>0;JOURSEM(B2;2)>5);CAR(1);0)"    ' Formule utilisée
    Set r = Range("G2:G" & DL)                          ' Plage où coller la formule
    r.FormulaLocal = f                                  ' Coller formule
    r.SpecialCells(xlCellTypeFormulas, 2).EntireRow.Delete  ' suppression des lignes concernées
    r.Clear                                             ' Effacement colonne formules
    Columns.AutoFit                                     'Ajustement largeurs colonnes
    With ActiveSheet.UsedRange: End With                'Ajustement barres de défilement
End Sub

La formule utilisée pour détecter les lignes à supprimer est :

=SI(OU(C2<10/24;C2>16/24;NB.SI(JoursFériés;B2)>0;JOURSEM(B2;2)>5);CAR(1);0)

pensez à valider les macros, à l'ouverture du fichier, si ce n'est déjà fait. 🙂

 

[meryach]

Bonjour Meryach,
Un essai en PJ.
Avec une feuille (masquée) pour le calcul des jours fériés.
( j'ai supposé que toutes les dates ne pouvaient pas être< à l'année précédente et > à l'année suivante)
La mise à jour est automatique quand on sélectionne la feuille "Resultat", avec cette macro :

Sub Worksheet_Activate()
    Dim DL, f, r
    [A:F].ClearContents                                 ' Effacement résultats précédents
    Application.ScreenUpdating = False                  ' Ecran figé
    With Sheets("Feuil1")
        DL = .[A65000].End(xlUp).Row                    ' Dernière ligne
        Range("A1:F" & DL) = .Range("A1:F" & DL).Value  ' Copier coller valeurs de Feuil1 vers résultat
    End With
    DL = [A65000].End(xlUp).Row                         ' Dernière ligne de Résultat
    f = "=SI(OU(C2<10/24;C2>16/24;NB.SI(JoursFériés;B2)>0;JOURSEM(B2;2)>5);CAR(1);0)"    ' Formule utilisée
    Set r = Range("G2:G" & DL)                          ' Plage où coller la formule
    r.FormulaLocal = f                                  ' Coller formule
    r.SpecialCells(xlCellTypeFormulas, 2).EntireRow.Delete  ' suppression des lignes concernées
    r.Clear                                             ' Effacement colonne formules
    Columns.AutoFit                                     'Ajustement largeurs colonnes
    With ActiveSheet.UsedRange: End With                'Ajustement barres de défilement
End Sub

La formule utilisée pour détecter les lignes à supprimer est :

=SI(OU(C2<10/24;C2>16/24;NB.SI(JoursFériés;B2)>0;JOURSEM(B2;2)>5);CAR(1);0)

pensez à valider les macros, à l'ouverture du fichier, si ce n'est déjà fait. 🙂

Merci Sylvanu,

en effet je ne maitrise pas les Macros et je souhaite apporté des modifications car dans le fichier j'ai mis juste le type A alors j'ai d'autres types et selon le type les heures changent.
Alors, quelle ligne je dois modifier pour apporter ces modifications.
Je te remercie beauuucoup par avance

 

[job75]

Bonjour meryach, sylvanu,

Pour sylvanu :

- il faudrait utiliser le critère "A" de la colonne F comme demandé

- pour gagner du temps faire un tri sur la colonne G afin de grouper les lignes à supprimer.

A+

 

[sylvanu]

Re, Bonjour Job,
Il suffisait de changer la formule :

=SI(OU(ET(OU(C2<10/24;C2>16/24);F2=""A"");NB.SI(JoursFériés;B2)>0;JOURSEM(B2;2)>5);CAR(1);0)

A tester avec plus de configuration, car il n'y a qu'un A dans votre tableau.

 

[meryach]

Re, Bonjour Job,
Il suffisait de changer la formule :

=SI(OU(ET(OU(C2<10/24;C2>16/24);F2=""A"");NB.SI(JoursFériés;B2)>0;JOURSEM(B2;2)>5);CAR(1);0)

A tester avec plus de configuration, car il n'y a qu'un A dans votre tableau.

Bonjour Sylvanu,
Merci pour ton aide
Pour ajouter d'autres conditions par exemple (si colonne F=B --> prendre les heures de 08 à 10), dois-je ajouter une nouvelle ligne ?
=SI(OU(ET(OU(C2<10/24;C2>16/24);F2=""A"");NB.SI(JoursFériés;B2)>0;JOURSEM(B2;2)>5);CAR(1);0)
=SI(OU(ET(OU(C2<08/24;C2>10/24);F2=""B"");NB.SI(JoursFériés;B2)>0;JOURSEM(B2;2)>5);CAR(1);0)
Ou sur la même formule (peut-etre ma formule est fausse) ?
=SI(OU(ET(OU(C2<10/24;C2>16/24);F2=""A"");(C2<8/24.C2>10;F2+""B""));NB.SI(JoursFériés;B2)>0;JOURSEM(B2;2)>5);CAR(1);0)
Merci beaucoup par avance 😊

 

[sylvanu]

Bonjour,
Essayez :

f = "=SI(OU(ET(OU(C2<8/24;C2>10/24);F2=""B"");ET(OU(C2<10/24;C2>16/24);F2=""A"");NB.SI(JoursFériés;B2)>0;JOURSEM(B2;2)>5);CAR(1);0)"    ' Formule utilisée

 

[meryach]

Bonjour,
Essayez :

f = "=SI(OU(ET(OU(C2<8/24;C2>10/24);F2=""B"");ET(OU(C2<10/24;C2>16/24);F2=""A"");NB.SI(JoursFériés;B2)>0;JOURSEM(B2;2)>5);CAR(1);0)"    ' Formule utilisée

Une remarque, sur la feuille résultat, je vois que même les heures 17:00, 18:00....23:00.... sont prises. seulement les weekends sont supprimés.
Merci

 

[sylvanu]

A tester avec plus de configuration, car il n'y a qu'un A dans votre tableau.

Comprenez que votre fichier ne peut permettre aucun test, il n'y a qu'un A.
Donc j'ai fait, mais vous auriez pu le faire, un "vrai" fichier test avec les différentes configurations.
Testez donc cette PJ ( avec la dernière formule incluant B ) et dites moi où les erreurs se trouvent.
Il y a en I1 le nombre de "A garder" et en L1 s'il y a une erreur.
Car :

je vois que même les heures 17:00, 18:00....23:00.... sont prises.

Sont "prises" quoi ? Supprimées ou conserver ?

 

[soan]

Bonjour sylvanu, meryach,

Une remarque, sur la feuille résultat, je vois que même les heures 17:00, 18:00....23:00.... sont prises.

Sont "prises" quoi ?

désolé, je ne peux pas t'aider, car moi aussi, j'en ai été littéralement pris de court ! 😄

je dois aussi refuser ton invitation à dîner ce soir vers 19:00 : je suis pris... 😜​

soan