XL 2013 Récupérer la valeur de la première cellule non vide juste au-dessus d'une cellule

Mooga · 12 Mai 2021

Bonjour à toutes et à tous,

Je suis bien embêté sur un petit problème Excel. Je cherche à réaliser une opération simple : une soustraction entre deux valeurs.
Le problème est que l'endroit d'une de ces valeurs à soustraire est variable.

J'ai trouvé une formule matricielle qui permet de récupérer la première cellule non vide d'une colonne :
=INDEX($C$4:$C$14;EQUIV(VRAI;$C$4:$C$14<>"";0))

mais je ne souhaite pas piocher la cette valeur située tout en haut de ma colonne mais plutôt celle située juste au-dessus de ma ligne du moment.
Le tableur en PJ sera beaucoup plus clair.

Merci beaucoup beaucoup à celles et ceux qui auront le temps de se pencher sur la question.
Patrick.

Jocelyn · 12 Mai 2021

Bonjour le Forum,
Bonjour Mooga,

un essai en fichier joint

Cordialement

soan · 12 Mai 2021

Bonjour Mooga, Jocelyn,

ton fichier en retour.

* tu peux voir que C4: D14 est vide (en D4: D14, y'a aucune formule)

* en C4, saisis 82 ➯ en D4 : 0,0 cm

* en C6, saisis 70 ➯ en D6 : 12,0 cm

* en C10, saisis 45 ➯ en D10 : 25,0 cm

* en C12, saisis 10 ➯ en D12 : 35,0 cm

VB:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim lig&, v1%, v2%
  With Target
    If .CountLarge > 1 Then Exit Sub
    If .Column <> 3 Then Exit Sub
    lig = .Row: If lig < 4 Then Exit Sub
    v2 = .Value
    If lig = 4 Then v1 = v2 Else v1 = Val(Replace$(.End(3), ",", "."))
    If v2 = 0 Then .Offset(, 1).ClearContents Else .Offset(, 1) = v1 - v2
  End With
End Sub

edit : il y a un bug dans mon code VBA ; ne pas utiliser le fichier joint ; j'ai corrigé le bug, et le bon fichier est dans mon post #15 (avec les explications sur la cause du bug).

soan

Mooga · 12 Mai 2021

C'est formidable merci à tous les deux pour votre réactivité.

Je vais garder celle de Jocelyn car pas de vba et dans le fichier de Soan, il y a des cas où le calcul est incorrect malheureusement. Par exemple :

Niveau d'eau (en cm)	Marnage (en cm)


80,00	0,0 cm
75,00	5,0 cm
70,00	10,0 cm

Ce n'est pas un besoin Jocelyn mais lorsque je tire toutes les colonnes vers la bas pour augmenter le nombre d'incrémentation, la formule de fonctionne mais c'est un pur détail subsidiaire. Pas besoin de se prendre la tête dessus.

Un grand merci encore à vous deux. C'est formidable.

Mooga · 12 Mai 2021

Rhaa zut désolé de déranger encore mais sur le fichier de Jocelyn, tout fonctionne sauf la dernière ligne du tableau. Si je rentre 8 et 1 cm pour 135 et 150s, j'obtiens ceci : 9 cm calculé au lieu de 7 cm.

Temps (en s)	Temps (en min)	Niveau d'eau (en cm)	Marnage (en cm)
Increment.
15 s
0	00:00	82,00	0,0 cm
15	00:15
30	00:30	70,00	12,0 cm
45	00:45
60	01:00
75	01:15
90	01:30	45,00	25,0 cm
105	01:45
120	02:00	10,00	35,0 cm
135	02:15	8,00	2,0 cm
150	02:30	1,00	9,0 cm

soan · 12 Mai 2021

@Mooga

pour ton post #4 :

80 ; 80 - 80 = 0 : ok

75 ; 80 - 75 = 5 ; ok

70 ; 75 -70 = 5 de nouveau, pas 10 !

exemple :

soan

Mooga · 12 Mai 2021

soan à dit:
@Mooga

pour ton post #4 :

80 ; 80 - 80 = 0 : ok

75 ; 80 - 75 = 5 ; ok

70 ; 75 -70 = 5 de nouveau, pas 10 !

soan

Merci de réagir Soan, dans ton fichier, si je rentre par exemple dans la colonne "niveau d'eau" dans l'ordre les valeurs suivantes j'obtiens ceci :
80 --> 0 cm
75 --> 5 cm
70 --> 10 cm (au lieu de 5 cm, 75-70)

soan · 12 Mai 2021

j'comprends pas : regarde l'image de mon post #6 précédent ;
en D10, j'ai bien 5,0 cm et pas 10,0 cm.

j'vois pas c'que t'as pu faire pour avoir 10 cm au lieu de 5 cm.

soan

Mooga · 12 Mai 2021

soan à dit:
j'comprends pas : regarde l'image de mon post #6 précédent ;
en D10, j'ai bien 5,0 cm et pas 10,0 cm.

j'vois pas c'que t'as pu faire pour avoir 10 cm au lieu de 5 cm.

soan

J'ai simplement commencé à saisir des valeurs au hasard à partir du haut du tableau pour 0s puis 15s puis 30s, etc.
Dans la colonne "niveau d'eau" et le calcul qui se fait est le suivant :

Mooga · 12 Mai 2021

Sinon @Jocelyn, un mystère que je ne parviens pas à résoudre est qu'il m'est impossible d'appliquer votre formule dans mon vrai fichier que je mets en PJ (cf. colonnes en jaune). Alors ça c'est curieux dis donc.

Jocelyn · 12 Mai 2021

re,
Bonjour soan,

concernant mon fichier c'est juste un soucis de $

j'avais écrit en D4

VB:

=SI(C4="";"";INDEX($C$4:C4;MAX(SI((ESTNUM($C$4:$C$12))*(LIGNE($C$4:C$12)<LIGNE());LIGNE($C$4:C$12)-3)))-C4)

en Bloquant la $C$12 car j'avais commencé a modifier en ligne12

donc en D4 après modif il faut

Code:

=SI(C4="";"";INDEX($C$4:C4;MAX(SI((ESTNUM($C$4:$C4))*(LIGNE($C$4:C4)<LIGNE());LIGNE($C$4:C4)-3)))-C4)

voir fichier

Mooga · 12 Mai 2021

Jocelyn à dit:
re,
Bonjour soan,

concernant mon fichier c'est juste un soucis de $

j'avais écrit en D4

VB:

=SI(C4="";"";INDEX($C$4:C4;MAX(SI((ESTNUM($C$4:$C$12))*(LIGNE($C$4:C$12)<LIGNE());LIGNE($C$4:C$12)-3)))-C4)

en Bloquant la $C$12 car j'avais commencé a modifier en ligne12

donc en D4 après modif il faut

Code:

=SI(C4="";"";INDEX($C$4:C4;MAX(SI((ESTNUM($C$4:$C4))*(LIGNE($C$4:C4)<LIGNE());LIGNE($C$4:C4)-3)))-C4)

voir fichier

Merci beaucoup Jocelyn, je me suis perdu également dans les $,
Mes excuses mais serait-il possible de m'aider à comprendre pourquoi je n'arrive pas intégrer votre formule dans mon fichier en PJ s'il-vous-plait ?

Jocelyn · 12 Mai 2021

re,

re voila le fichier corrigé l'erreur venait de cette partie de la formule LIGNE(G$31:G31)-30

la fonction max renvoie un numéro de ligne pour l'index or si nous avons besoin de la ligne 31 elle correspond au numéro 1 de l'index G$31:G31 donc pour avoir ce numéro 1 on recherche les numéro de ligne en on enleve 30

dans le fichier exemple on avait LIGNE(G$31:G31)-1 puisque l'index commencait sur la ligne 2 et tu avais laisser le -1

Mooga · 12 Mai 2021

Excellent, j'en sors grandi merci beaucoup.

soan · 12 Mai 2021

@Mooga, Jocelyn,

j'ai été occupé sur d'autres exos, c'est pourquoi je reviens seulement maint'nant sur celui-ci.

ton post #9 m'a aidé à comprendre le bug de mon code VBA : ça se produit quand il n'y a pas au moins une cellule vide au-dessus du nombre que tu saisis : c'est le .End(3) = .End(xlUp) qui fait que ça ne marche plus quand on saisi un nombre juste en dessous du précédent ; ça faisait 80 (de C4) - 70 (de C6) = 10 ; j'ai donc utilisé une autre méthode qui est sans .End(xlUp) ; cette fois c'est ok :

VB:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim lg1&, lg2&, v1%, v2%
  With Target
    If .CountLarge > 1 Then Exit Sub
    If .Column <> 3 Then Exit Sub
    lg2 = .Row: If lg2 < 4 Then Exit Sub
    v2 = .Value: If v2 = 0 Then .Offset(, 1).ClearContents: Exit Sub
    If lg2 = 4 Then
      v1 = v2
    Else
      lg1 = lg2: Do While Cells(lg1 - 1, 3) = 0: lg1 = lg1 - 1: Loop
      v1 = Val(Replace$(Cells(lg1 - 1, 3), ",", "."))
    End If
    If v2 = 0 Then .Offset(, 1) = "" Else .Offset(, 1) = v1 - v2
  End With
End Sub

soan

XL 2013 Récupérer la valeur de la première cellule non vide juste au-dessus d'une cellule

Mooga

XLDnaute Nouveau

Pièces jointes

Jocelyn

XLDnaute Barbatruc

Pièces jointes

soan

XLDnaute Barbatruc

Pièces jointes

Mooga

XLDnaute Nouveau

Mooga

XLDnaute Nouveau

soan

XLDnaute Barbatruc

Mooga

XLDnaute Nouveau

soan

XLDnaute Barbatruc

Mooga

XLDnaute Nouveau

Mooga

XLDnaute Nouveau

Pièces jointes

Jocelyn

XLDnaute Barbatruc

Pièces jointes

Mooga

XLDnaute Nouveau

Pièces jointes

Jocelyn

XLDnaute Barbatruc

Pièces jointes

Mooga

XLDnaute Nouveau

soan

XLDnaute Barbatruc

Pièces jointes

Discussions similaires