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