Dernière ligne et offset depuis la dernière ligne

Le 28/02/2023 à 21:18

Bonjour à tous,

J'essai de faire du post traitement de données scientifiques.

J'aimerais sélectionner les 10000 dernières valeurs d'une colonne "E" sur 45000 valeurs pour effectuer une moyenne.

Du coup dans le principe je peux utiliser End(xldown) pour aller à la dernière valeur de la colonne = OK

Ca doit ressemble à Range("E:E").End(xldown) = Mais cette opération sert à quoi à sélectionner une cellule ?

Une fois la cellule sélectionner j'aimerais qu'il me donne par exemple que c'est la ligne 45000 ou E45000 pour que je puisse l'utiliser afin de sélectionner les 10000 valeurs plus haut

Exemple : sélection entre E35000 et E45000 pour pouvoir calculer la moyenne.

J'espère être clair, j'ai pour le moment pas coder grand chose tant que j'ai pas quelques outils pour démarrer.

En vous remerciant pour votre aide

Le 28/02/2023 à 22:25

Hello,

Alors tu peux utiliser ceci qui te permettra de récupérer le numéro de la dernière ligne renseignée en colonne E (DerLigne).

LigneAvant, récupère la 10000ème ligne avant la dernière ligne

moyenne = moyenne de la plage de cellules

Sub moyenne()

DerLigne = Range("E" & Rows.Count).End(xlup).Row
LigneAvant = DerLigne - 10000

moyenne = Application.WorksheetFunction.Average(Range("E" & LigneAvant & ":E" & DerLigne))

End sub

@+

Le 01/03/2023 à 00:12

re,

avec cela,la moyenne sera de 10.001 lignes !

LigneAvant = worksheetfunction.max(1;DerLigne - 9999)

Le 01/03/2023 à 00:40

Ah bien vu j'ai été un peu trop rapide sur l'écriture

Le 01/03/2023 à 23:04

Merci beaucoup à vous !!

Je regarde çà ASAP :) :)

Le 03/03/2023 à 13:03

Bonjour a vous

J'ai 4 petites questions supplémentaires.

est ce vous pourriez me dire ce que fait la fonction rows.count dans range ?

quand on met des & dans range ( arg1 & arg2) cela signifie quoi ?

que signifie ":E" ?

et si je veux rajouter une condition dans derligne ? Possible j'avais pas vu mais il y a plein de 0 en dessous ce que je pensais être la dernière ligne .... Et du coup a juste titre la moyenne qu'il me sort est 0 :-)

en vous remerciant !

Le 03/03/2023 à 13:15

Hello

- est ce vous pourriez me dire ce que fait la fonction rows.count dans range ?

Rows.Count : Rows (Lignes en anglais) et count (compte en anglais) est une instruction qui renvoie le nombre totale de lignes d'une feuille. Ce nombre dépend uniquement de la version de Excel (65 536 sur les versions avant Excel 2007, 1 048 576 pour les versions ultérieures). (Merci à Pedro22 pour cette explication trouvée sur le forum)

- quand on met des & dans range ( arg1 & arg2) cela signifie quoi ?

Ca permet de concatener. Exemple si ta dernière ligne est variable et que tu veux la dernière cellule de ta colonne A tu vas écrire Range("A" & DerLigne). Vu que DerLigne est dynamique ça te reprendra modifiera bien ta dernière cellule à chaque fois.

- que signifie ":E" ?

Quand tu fais une moyenne dans Excel tu fais : =MOYENNE(E1:E6)

Ici c'est la même chose J'ai concaténé E avec l'équivalent du 1 puis avec ":E" puis avec le 6

moyenne = Application.WorksheetFunction.Average(Range("E" & LigneAvant & ":E" & DerLigne))

- et si je veux rajouter une condition dans derligne ? Possible j'avais pas vu mais il y a plein de 0 en dessous ce que je pensais être la dernière ligne .... Et du coup a juste titre la moyenne qu'il me sort est 0 :-)

Quelle est cette condition ?

@+

Le 03/03/2023 à 14:12

super tes réponses

merci beaucoup

la condition c'est prendre la dernière ligne dans laquelle la valeur est différente de "0"

Le 03/03/2023 à 14:51

Hello,

Imaginons en colonne E tu as : E15002 qui est différent de 0, et après tu as que des 0 jusqu'à E 16000.

Toi ton besoin ce serait de parcourir E16000 jusqu'à E15002 et faire le test : si c'est 0 tu passes à la cellule du dessus sinon tu gardes le numéro de ligne.

Il faut que tu dises que tu vas te décaler à la dernière ligne puis remonter tant que tu n'es pas à une cellule différente de 0 :

Ce code à tester

Sub moyenne()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

DerLigne = Range("E" & Rows.Count).End(xlup).Row

Do While Range("E" & Derligne) = 0
    DerLigne = DerLigne -1
Loop While

LigneAvant = worksheetfunction.max(1;DerLigne - 9999) 'Merci à BsAlv

moyenne = Application.WorksheetFunction.Average(Range("E" & LigneAvant & ":E" & DerLigne))

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End sub

@+

Le 03/03/2023 à 15:08

re,

ceci est peut-être pas pour quelqu'un novice 

Sub MisterDuc()
     Dim Derligne As Long, MyMax As Variant

     Derligne = Range("E" & Rows.Count).End(xlUp).row
     MyMax = Application.Max(Evaluate(Replace("if((E1:E#<>"""")*(E1:E#<>0),row(E1:E#),""-"")", "#", Derligne)))     'dernière ligne non-vide et <>0
     If IsNumeric(MyMax) Then
          MsgBox MyMax & vbLf & Range("E" & Application.Max(1, MyMax - 9999) & ":E" & MyMax).Address, vbInformation, "okay" 'ce msgbox n'est pas nécessaire
          moyenne = WorksheetFunction.Average(Range("E" & Application.Max(1, MyMax - 9999) & ":E" & MyMax))
     Else
          MsgBox "problème"
     End If
End Sub
Le 03/03/2023 à 15:43

Je vais le prendre le temps de regarder tout ça en tout cas merci beaucoup a vous 2 !

J'apprends beaucoup grâce à vous

Le 07/03/2023 à 18:13

Bonjour

J'ai pris le temps de regarder :)

J'avoue que la proposition de BslAv pfiou ... pas facile facile :)

2 questions pour Baroute78 : pourquoi utilise t'on la fonction max pour ligne avant ? pourquoi on se met en calcul manuel ?

Merci beaucoup :)

Le 07/03/2023 à 18:18

Hello,

On utilise Max entre 1 et DerLigne - 9999 car si on 9999 lignes ou moins dans le fichier et bien on passerait en dessous de 1 en terme de ligne. Ce qui n'est pas possible dans un classeur Excel :) Merci à BsAlv pour cette précision, je n'y avais pas pensé à la base

On se met en calcul manuel pour optimiser la vitesse de traitement de la macro :)

@+

Le 12/03/2023 à 18:01

Bonjour BsAlv

Merci pour ta proposition.

Ca fait un moment que j'essai de comprendre peux tu m'expliquer juste cette ligne STP ?

MyMax = Application.Max(Evaluate(Replace("if((E1:E#<>"""")*(E1:E#<>0),row(E1:E#),""-"")", "#", Derligne))) 'dernière ligne non-vide et <>0

Replace : j'ai compris que c'était remplacer

"""" = ? Cellules vides dans E1:E# ?

* : Et ?

""-"" : je ne comprend pas

En te remerciant,

Le 12/03/2023 à 18:49

re,

avec evaluate, on calcule quelque chose en VBA qu'on fait normallement dans une feuille. Vous pouvez par exemple mettre cette formule dans une feuille (la première formule est pour dans la feuille elle-même, la 2ième pour une autre feuille).

=MAX(SI((E1:E100<>"")*(E1:E100<>0);LIGNE(E1:E100))) 
=MAX(ALS((Feuil1!E2:E101<>"")*(Feuil1!E2:E101<>0);RIJ(Feuil1!E2:E101)))

Mais comme VBA est anglais, il faut traduire cela en

=MAX(IF((Feuil1!E2:E101<>"")*(Feuil1!E2:E101<>0),ROW(Feuil1!E2:E101)))

Mais maintenant vous voulez que ce "100" soit un variable

Sub test()

     s1 = "MAX(IF((Feuil1!E2:E101<>"""")*(Feuil1!E2:E101<>0),ROW(Feuil1!E2:E101)))"   '>>>> attention ce double """"
     s2 = Replace(s1, "101", "#")
     r = Application.InputBox("un numéro svp", , 1000, , , , 2)
     s3 = Replace(s2, "#", r)

     MsgBox "ma formule en anglais, avec virgule au lieu de point virgule" & vbLf & "et le "" doublé est " & vbLf & s1 & vbLf & vbLf & _
            "ma formule avec ""#"" au lieu de ""100   """ & s2 & vbLf & vbLf & _
            "ma formule avec au lieu de ""#"" un numéro variable :  ligne " & r & "  : " & s3 & vbLf & vbLf

     x = Evaluate(s3)
     MsgBox "résultat : " & x

End Sub

Donc, si vous traduisez votre formule en anglais ( ; devient , ) (https://fr.excel-translator.de/translator/) et si vous avez un " vous le doublez, mettez le resultat dans un string et puis vous n'avez qu'à evaluer ce string

Si vous me donnez une formule que vous avez maintenant quelque part dans votre feuille et vous voulez calculer cela en VBA, envoyez votre fichier avec votre formule, pour mieux vous expliquer ...

Le 12/03/2023 à 21:17

Merci beaucoup

Je pense que j'ai compris qqchose de très intéressant :) merci pour ce temps pris pour m'expliquer.

Je vais prendre le temps de relire.

Le 12/03/2023 à 22:05

Salut …

Autre proposition (VBA), dans la page de codes de la feuille concernée*

‘sélection par double clic de la cellule H2 dans celle-ci

Private Sub Worksheet_BeforeDoubleClick(ByVal R As Range, Cancel As Boolean)
  If R.Address <> [H2].Address Then Exit Sub
  Cancel = 1: UsF.Show
End Sub

Et pour le formulaire (Usf) où T1 contiendra la ligne de départ et T2 celle d’arrivée (à saisir ),une sélection de Lm donnera la moyenne en H

Private Sub Lm_Click() 
  If Val(T2) < Val(T1) Then T1 = "": T2 = "": Exit Sub
  [H2].FormulaLocal = "=MOYENNE(E" & Val(T1) & ":E" & Val(T2) & ")"
  [H2] = [H2].Value 'plus de formule (peut être inutile) !
  Unload Me
End Sub

*voir fichier joint (Formulaire pour faciliter les choix de lignes)

22moyenne-si.zip (512.70 Ko)
Le 13/03/2023 à 00:02

Bonsoir,
@Bart,
Une modification !?
Cdlt.

Sub RevisedMisterDuc()
Dim lastrow As Long, rngData As Range
Const N As Long = 10
    lastrow = Application.Evaluate("MATCH(2,IF(E:E<>0,1))")
    Set rngData = Cells(lastrow - N, 5).Resize(N + 1)
    MsgBox WorksheetFunction.Average(rngData)
End Sub
Le 14/03/2023 à 20:12

Bonjour à tous

Merci beaucoup pour vos propositions je vais regarde tout çà :) :)

Rechercher des sujets similaires à "derniere ligne offset"