XL 2016 VBA : Copier ligne selon condition

[kamicr]

Bonjour,

Avant de commencer, oui beaucoup de sujets traitent ce problème, cependant je suis une triple buse en VBA (je pense que ca devrait être illegale )

A vrai dire, je ne comprend strictement rien au VBA et si j'ai besoins d'une aide personnalisé, c'est pour justement pouvoir apprendre (au moins des bases) pour être plus autonome sur mes prochains "défis" que me soumet ce langage.

Tout d'abord je veux essayer du VBA car j'imagine que mon fichier sera moins gros au final. (J'ai réussis à faire ce que je voulais en formule, où je me débrouille plutôt bien, cependant en ne traitant que 10% des données que j'ai et mon fichier atteint déjà 50Mb).

Mon problème est le suivant : J'ai un onglet avec environ 200 000 lignes pour trentaine de colonne de donnée bruts. Heuresement j'ai besoins de traiter que deux des ces colonnes. Dans une des colonnes que je dois extraire, il y a la fréquence et l'autre colonne la déviation par rapport à son état initiale. Mon objectif serait d'avoir 20 onglets ou chaque onglet, la colonne freq et la colonne de déviation associée soit recopiée par tranche de 20MHz. (ma planche de fréquence varie de 1 MHz à 400 MHz) donc la premiere sous bande serait 1-20MHz, la deuxieme 20-40, troisieme 40-60 ... jusqu'à 380-400. En formule ca donne ceci :

=IF(AND(IF('Donnée brut'!B2>=$F$1,1,0),IF('Donnée brut'!B2<$F$2,1,0)),'Donnée brut'!F2,#N/A)

Donnée brut'!B2 étant ma fréquence
Donnée brut'!F2 étant la déviation de la donnée
$F$2 ma fréquence max de la sous bande
$F$1 ma fréquence min de la sous bande

Cette formule est étendu sur les 200 000 lignes pour courvrir toute mes données bruts. Elle fonctionne très bien mais comme dit auparavant en ne traitant que les 3 premières sous bandes, j'ai deja un fichier de 50 Mb (ce qui n'est pas viable)

Il est à noté, que je n'ai jamais réussis a faire fonctionner le moindre code VBA.

D'avance merci de votre aide.

 

[job75]

Bonjour kamicr, bienvenue sur XLD,

Joignez votre fichier en ne conservant que 100 ou 200 lignes pour l'alléger.

D'après ce que je comprends le VBA n'apportera rien puisque les formules son très simples.

Et au lieu de 20 onglets de résultats il vaut mieux ajouter une colonne dans la feuille.

Cette colonne indiquera, par formule, la tranche de fréquences, il suffira ensuite de la filtrer.

A+

 

[job75]

Re,

Pour vous donner des idées voyez le fichier joint avec cette formule en B2, à tirer vers le bas :

=TEXTE(PLAFOND(A2;20)-20;"000")&"-"&TEXTE(PLAFOND(A2;20);"000")

Ou sur version anglaise :

=TEXT(CEILING(A2,20)-20,"000")&"-"&TEXT(CEILING(A2,20),"000")

Utilisez le filtre en colonne B pour afficher les lignes de la tranche que vous voulez.

A+

 

[job75]

Re,

Pour tester j'ai créé le fichier joint de 200 000 lignes et de 30 colonnes, il pèse 37 Mo.

Le filtrage sur la colonne B est quasi immédiat.

A+

 

[kamicr]

Bonjour,
Le problème c'est que j'ai "besoins" de ces 20 onglets, ca fait partie de mon cahier des charges, car je doit crée 20 courbes et je dois avoir la courbe et les données corespondante sur un onglet (pour les 20 sous-bandes)
Pour ce qui est des formules, vu que je dois étendre la formules sur 200 000 lignes sur 20 onglets ca me créer un fichier très lourd (j'ai tester pour voir et il fait 150Mb) et quelques minutes pour l'ouvrir.. je me disais donc qu'en VBA le fichier serait moins lourd.

Le fichier joint ne corespond pas à la réalité (dans la colone strat freq je ne devrais pas avoir que 1 étant donnée que mes test sont fait par tranche de 5MHz) mais mon fichier Xcel de 150Mb a planté et il ne répond plus pour le moment xD

EDIT : Finalement j'ai pus reprendre la main apres l'envois de ce message.. du coup je vous mais un fichier un peu plus "réaliste"

 

[job75]

Re,

Votre problème c'est le poids du fichier, il faut donc vous arranger pour tout faire sur la feuille des données.

Ne vous laissez pas impessionner par le "Cahier des charges"

Vous pouvez faire un seul graphique qui se modifiera suivant le filtrage.

Il y a des exemples sur le forum, travaillez un peu.

A+

 

[kamicr]

Quand le cahier des charges est défini par ton chef et que ces courbes doit figurer dans un rapport, si, je dois être "impressionné" par ce cahier des charges. Je continuerais à le faire manuellement comme je l'ai fait, je voulais juste gagner du temps sur la génération de mes courbes. Mais merci malgrès tous de votre aide.

Pour ce qui est du travail, merci mais j'ai déjà enormement travaillé sur ce fichier et les autres anterieurs qui m'ont déjà fait gagner de précieuses heures de travail, ce traitement aurait juste été le "dernier" petit plus sur ce type d'essais. Et j'aurais réussis si le fichier ne serait pas aussi lourd.

 

[job75]

Bonjour kamicr, le forum,

Quand le cahier des charges est défini par ton chef et que ces courbes doit figurer dans un rapport, si, je dois être "impressionné" par ce cahier des charges.

Si votre chef n'est pas stupide il devrait vous être facile de lui démontrer que créer 20 onglets n'est pas une bonne idée.

Car avec un seul graphique dans la feuille c'est l'enfance de l'art.

Il suffit de cette macro pour mettre en forme le graphique pour chaque filtrage :

Private Sub Worksheet_Change(ByVal Target As Range)
With [A1].CurrentRegion
    If .Rows.Count = 1 Then Exit Sub 'sécurité
    .Offset(1).Resize(.Rows.Count - 1).Columns(1).Name = "X"
    .Offset(1).Resize(.Rows.Count - 1).Columns(3).Name = "Y"
    .Columns(2).AutoFilter 1, IIf([AG1] = "", "*", [AG1])
End With
ChartObjects(1).Chart.ChartTitle.Text = IIf([AG1] = "", "000-400 MHz", [AG1] & " MHz")
With ChartObjects(1).Chart.Axes(xlCategory)
    If [AG1] = "" Then .MinimumScale = 0 Else .MinimumScale = Split([AG1], "-")(0)
    If [AG1] = "" Then .MaximumScale = 400 Else .MaximumScale = Split([AG1], "-")(1)
End With
ActiveCell.Activate
End Sub

Fichier .xlsm joint, j'ai réduit le nombre de lignes à 100 000 pour pouvoir le déposer ici.

Edit : le fichier pèse 24,5 Mo, avec 200 000 lignes il pèserait 49,2 Mo.

Juste une question, vous êtes venu ici kamicr pour alléger votre fichier, oui ou non ?

A+

 

[kamicr]

Bonjour, oui, je suis venu pour alléger mon fichier si je fais un remplissage automatique, sinon si je continue en manuel je n'ai pas besoins de le réduire. Pour ce qui est du format, il me faut ces 20 courbes, question de lisibilité, Je regarderai votre fichier Lundi, je n'ais pas trop le temps ce week end

 

[job75]

Re,

Bon on peut créer 20 onglets sans augmenter le poids du fichier.

Il faut cependant une petite gymnastique : copier-coller du graphique puis suppression dans ThisWorkbook :

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
With Feuil1.[A1].CurrentRegion.Offset(1)
    If .Rows.Count = 1 Then Exit Sub 'sécurité
    .Resize(.Rows.Count - 1).Columns(1).Name = "X"
    .Resize(.Rows.Count - 1).Columns(3).Name = "Y"
End With
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Not Sh.Name Like "*###-###" Then Exit Sub
Application.ScreenUpdating = False
With Feuil1
    .[A1].CurrentRegion.Columns(2).AutoFilter 1, IIf(Sh.Name = .Name, "*", Sh.Name)
    If Sh.Name <> .Name Then
        Application.Goto Sh.[A1], True 'cadrage
        .ChartObjects(1).Chart.ChartArea.Copy 'copie le graphique
        Sh.Paste
        Sh.[A1].Copy Sh.[A1] 'vide le presse-papiers
        ActiveCell.Activate
    End If
End With
Sh.ChartObjects(1).Chart.ChartTitle.Text = Sh.Name & " MHz"
With Sh.ChartObjects(1).Chart.Axes(xlCategory)
    .MinimumScale = Split(Right(Sh.Name, 7), "-")(0)
    .MaximumScale = Right(Sh.Name, 3)
End With
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
'---supprime le(s) graphique(s) pour alléger---
If Sh.Name Like "###-###" And Sh.ChartObjects.Count Then Sh.ChartObjects.Delete
End Sub

Fichier (2).

Edit : je suppose que vous savez ce qu'est un tableau Excel.

Son intérêt est que la formule en colonne B se recopie automatiquement quand on agrandit le tableau.

Le filtre doit alors être appliqué à toutes les colonnes :

With Feuil1
    .[A1].CurrentRegion.AutoFilter 2, IIf(Sh.Name = .Name, "*", Sh.Name)

Fichier (2 bis).

A+