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

[Mooga]

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]

Bonjour le Forum,
Bonjour Mooga,

un essai en fichier joint

Cordialement

 

[soan]

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

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]

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]

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]

@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]

@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]

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]

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]

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]

re,
Bonjour soan,

concernant mon fichier c'est juste un soucis de $

j'avais écrit en D4

=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

=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]

re,
Bonjour soan,

concernant mon fichier c'est juste un soucis de $

j'avais écrit en D4

=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

=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]

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]

Excellent, j'en sors grandi merci beaucoup.

 

[soan]

@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 :​

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