Renseigner des cellules vides dans liste de chiffres

Chris57 · 17 Mai 2010

Bonjour à tous,

je récupère actuellement des données météo dans un fichier Excel en raison d'une valeur par 1/4 d'heure.
Mais il arrive que pendant quelques heures je perde la source de données. J'ai donc un trou dans mes relevés.

Y a t'il un moyen de calculer ces valeurs par estimation ?

Par exemple j'ai ceci :

Code:

01:15	 - 	16,4 °C
02:15	 - 	16,2 °C
03:15	 - 	16,7 °C
04:15	 - 	16,5 °C
05:15	 - 	
06:15	 - 	
07:15	 - 	
08:15	 - 	
09:15	 - 	
10:15	 - 	
11:15	 - 	
12:15	 - 	
13:15	 - 	
14:15	 - 	
15:15	 - 	
16:15	 - 	
17:15	 - 	
18:15	 - 	
19:15	 - 	11,2 °C
20:15	 - 	11,1 °C
21:15	 - 	10,9 °C
22:15	 - 	10,6 °C
23:15	 - 	10,4 °C

Dans cette exemple entre 5h15 et 18h15 je n'ai plus eu de données. Par calcul on pourrait estimer les valeur manquantes, ce qui donnerai ceci :

Code:

01:15	 - 	16,4 °C
02:15	 - 	16,2 °C
03:15	 - 	16,7 °C
04:15	 - 	16,5 °C
05:15	 - 	[B]16,1 °C[/B]
06:15	 - 	[B]15,7 °C[/B]
07:15	 - 	[B]15,4 °C[/B]
08:15	 - 	[B]15,0 °C[/B]
09:15	 - 	[B]14,6 °C[/B]
10:15	 - 	[B]14,2 °C[/B]
11:15	 - 	[B]13,9 °C[/B]
12:15	 - 	[B]13,5 °C[/B]
13:15	 - 	[B]13,1 °C[/B]
14:15	 - 	[B]12,7 °C[/B]
15:15	 - 	[B]12,3 °C[/B]
16:15	 - 	[B]12,0 °C[/B]
17:15	 - 	[B]11,6 °C[/B]
18:15	 - 	[B]11,2 °C[/B]
19:15	 - 	11,2 °C
20:15	 - 	11,1 °C
21:15	 - 	10,9 °C
22:15	 - 	10,6 °C
23:15	 - 	10,4 °C

Le calcul est simple : 14 valeurs manquantes, entre le 1er relevé manquant et celui de 19h15 il y a 5.3°C.
5,3 / 14 = 0,378571429 °C par 1/4 d'heure.

Peut-on traduire ça avec une macro qui rechercherai les plages vides et calculerai les valeurs manquantes ?

camarchepas · 17 Mai 2010

Re : Renseigner des cellules vides dans liste de chiffres

Bonjour M. Météo,

Les tranches de ton exemple sont toutes les heures .

Ca ne change rien au principe .

Voici une solution :

job75 · 17 Mai 2010

Re : Renseigner des cellules vides dans liste de chiffres

Bonjour Chris57, camarchepas.

Voici une solution qui fait 2 types d'interpolations des valeurs :

- linéaire avec la fonction TENDANCE (touche F1 pour l'aide Excel)

- exponentielle avec la fonction CROISSANCE.

Les résultats sont un peu différents.

La macro dans le Module1 :

Code:

Sub Interpolation()
Dim tablo, u As Long, i As Long, Xc(), Yc(), n As Long
tablo = Range("A2:B" & Range("A65536").End(xlUp).Row)
u = UBound(tablo)
'---Détermine les valeurs connues Xc et Yc---
For i = 1 To u
  If tablo(i, 2) <> "" Then
    ReDim Preserve Xc(n): Xc(n) = tablo(i, 1)
    ReDim Preserve Yc(n): Yc(n) = tablo(i, 2)
    n = n + 1
  End If
Next
'---Création des noms correspondants dans la feuille---
ThisWorkbook.Names.Add "Xc", Xc
ThisWorkbook.Names.Add "Yc", Yc
'---Création du tableau colonnes C:D---
Application.ScreenUpdating = False
Range("C2:D65536").ClearContents
For i = 1 To u
  Cells(i + 1, "C").Resize(, 2) = tablo(i, 2)
  If tablo(i, 2) = "" Then
    Cells(i + 1, "C").FormulaLocal = "=TENDANCE(Yc;Xc;" & tablo(i, 1) & ")"
    Cells(i + 1, "D").FormulaLocal = "=CROISSANCE(Yc;Xc;" & tablo(i, 1) & ")"
  End If
Next
Range("C2:D2").Resize(u) = Range("C2:D2").Resize(u).Value 'suppression des formules
End Sub

Fichier joint.

Edit : j'ai essayé de calculer les valeurs en VBA avec Application.Trend et Application.Growth.

Mais ça ne veut pas marcher

A+

job75 · 17 Mai 2010

Re : Renseigner des cellules vides dans liste de chiffres

Re,

Il peut être intéressant de voir ce que donnent les calculs de régression pour toutes les valeurs (même quand elles sont mesurées).

Fichier joint.

A+

job75 · 17 Mai 2010

Re : Renseigner des cellules vides dans liste de chiffres

Re,

Une solution nettement plus simple pour la 2ème version :

Code:

Sub Interpolation()
Dim tablo, u As Long, i As Long, Xc(), Yc(), n As Long
tablo = Range("A2:B" & Range("A65536").End(xlUp).Row)
u = UBound(tablo)
'---Détermine les valeurs connues Xc et Yc---
For i = 1 To u
  If tablo(i, 2) <> "" Then
    ReDim Preserve Xc(n): Xc(n) = tablo(i, 1)
    ReDim Preserve Yc(n): Yc(n) = tablo(i, 2)
    n = n + 1
  End If
Next
'---Création des noms correspondants dans la feuille---
ThisWorkbook.Names.Add "Xc", Xc
ThisWorkbook.Names.Add "Yc", Yc
'---Création du tableau colonnes C:D---
Application.ScreenUpdating = False
Range("C2:D65536").ClearContents
[COLOR="Red"]Range("C2").Resize(u).FormulaR1C1 = "=TREND(Yc,Xc,RC1)"
Range("D2").Resize(u).FormulaR1C1 = "=GROWTH(Yc,Xc,RC1)"[/COLOR]
Range("C2:D2").Resize(u) = Range("C2:D2").Resize(u).Value 'suppression des formules
End Sub

A+

Chris57 · 23 Mai 2010

Re : Renseigner des cellules vides dans liste de chiffres

Merci à tous les 2 pour vos aides !!
Je répond un peu en retard car j'ai pas mal de boulot en ce moment et je suis un peu dépassé par les évènements.

Par contre job75, je ne comprends pas ton idée de calcul sur toutes les valeurs !
Quel en est l'objectif ??

Sinon pour clarifier, je relève 24 valeurs toutes les 15 min. J'ai déjà 4000 relevés depuis le 17 avril 2010 soit 96000 valeurs !
Je pense faire un fichier par an, c'est à dire plus de 35000 relevés c'est à dire 840000 valeurs !
J'espère que le PC ne va pas explosé.

Mes relevés ressemblent au fichier joint (ce n'est qu'un tout petit extrait, mais j'ai choisi une période où il y a des relevés manquants).

Je vais tenter d'adapter vos macro à mon tableau.

job75 · 23 Mai 2010

Re : Renseigner des cellules vides dans liste de chiffres

Bonsoir Chris57, le fil, le forum,

Chris57 à dit:
Par contre job75, je ne comprends pas ton idée de calcul sur toutes les valeurs !
Quel en est l'objectif ??

Vous avez certainement compris que les fonctions TENDANCE ou CROISSANCE permettent de déterminer la droite ou la courbe de régression.

Les valeurs calculées complètent les valeurs mesurées : on peut avec elles tracer la droite ou la courbe de régression...

A+

job75 · 23 Mai 2010

Re : Renseigner des cellules vides dans liste de chiffres

Re,

Chris57 à dit:
Sinon pour clarifier, je relève 24 valeurs toutes les 15 min. J'ai déjà 4000 relevés depuis le 17 avril 2010 soit 96000 valeurs !
Je pense faire un fichier par an, c'est à dire plus de 35000 relevés c'est à dire 840000 valeurs !
J'espère que le PC ne va pas explosé.

Aucun problème pour Excel.

Par contre, sur un an, il est clair que vous ne pouvez pas utiliser les fonctions TENDANCE ou CROISSANCE (même si la planète se réchauffe... ou se refroidit).

Il faudrait coupichonner en tranches, correspondant aux valeurs manquantes, sur des périodes courtes.

Ce sera alors bien laborieux...

Une solution, c'est de faire un graphique. Vous remplacerez les valeurs manquantes par #N/A, et vous cocherez l'option lissage.

A+

ROGER2327 · 24 Mai 2010

Re : Renseigner des cellules vides dans liste de chiffres

Bonjour à tous

Ce n'est pas exactement la réponse attendue, c'est un bricolage que j'ai fait à un autre propos...

ROGER2327
#3522

Samedi 7 Merdre 137 (Nativité de Monsieur Plume, propriétaire, SQ)
5 Prairial An CCXVIII
2010-W21-1T00:05:07Z

job75 · 24 Mai 2010

Re : Renseigner des cellules vides dans liste de chiffres

Bonjour le fil, salut Roger

Voici une solution qui fonctionne quels que soient le nombre de plages vides et leurs positions.

La macro interpole à partir des 2 valeurs qui bordent chaque plage vide :

Code:

Sub Interpolation()
Dim tablo, u As Long, i As Long, j As Long, k As Long, p As Double
tablo = Application.Transpose(Range("B2:B" & Range("A65536").End(xlUp).Row))
u = UBound(tablo)
For i = 1 To u - 2
  If tablo(i) <> "" And tablo(i + 1) = "" Then
    For j = i + 2 To u
      If tablo(j) <> "" Then GoTo 1
    Next
    Exit For
1   p = (tablo(j) - tablo(i)) / (j - i)
    For k = 1 To j - i - 1
      tablo(i + k) = tablo(i) + k * p 'interpolation
    Next
    [COLOR="Red"]i = j - 1[/COLOR]
  End If
Next
'---Restitution en colonne C (on pourrait restituer directement en colonne B)---
Range("C2:C65536").ClearContents
Range("C2").Resize(u) = Application.Transpose(tablo)
End Sub

Edit : ajouté i = j - 1 qui permet d'accélérer la macro.

A+

ROGER2327 · 24 Mai 2010

Re : Renseigner des cellules vides dans liste de chiffres

Bonsoir à tous

La version VB de ma contribution précédente.

ROGER2327
#3537

Samedi 7 Merdre 137 (Nativité de Monsieur Plume, propriétaire, SQ)
5 Prairial An CCXVIII
2010-W21-1T21:56:33Z

Chris57 · 25 Mai 2010

Re : Renseigner des cellules vides dans liste de chiffres

Excellent. J'ai testé les 2 possibilités et elle marchent à merveille !!

Par contre j'ai une question pour job75.
Dans ta macro pourquoi tu fais un Range("C2:C65536").ClearContents avant de transposer les résultats ?

job75 · 25 Mai 2010

Re : Renseigner des cellules vides dans liste de chiffres

Bonsoir Chris57, le fil, le forum,

Chris57 à dit:
Par contre j'ai une question pour job75.
Dans ta macro pourquoi tu fais un Range("C2:C65536").ClearContents avant de transposer les résultats ?

C'est juste une précaution, pas forcément indispensable : si le nombre de lignes du tableau A:B est diminué, on peut oublier de "nettoyer" la colonne C (où il pourrait rester des valeurs en dehors de la plage de restitution).

Pour en terminer en ce qui me concerne, vous trouverez ci-joint le fichier de votre post #6, avec la macro adaptée pour traiter les colonnes C à U.

Bien noter que les formats personnalisés des colonnes ont été revus, mais j'ai conservé le format (très spécial) de la colonne D, ce qui nécessite un traitement particulier de cette colonne.

Noter aussi que le bouton a une double fonction...

A+

Chris57 · 25 Mai 2010

Re : Renseigner des cellules vides dans liste de chiffres

Merci à tous pour votre aide !!

C'est vraiment du grand art, un niveau que je ne maîtrise pas en VBA ! BRAVO !

Renseigner des cellules vides dans liste de chiffres

XLDnaute Occasionnel

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Occasionnel

Pièces jointes

XLDnaute Barbatruc

XLDnaute Barbatruc

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Occasionnel

XLDnaute Barbatruc

Pièces jointes

XLDnaute Occasionnel

Discussions similaires

Partager cette page

Statistiques des forums