Normal 0 21

bonjour,

J'ai des tableaux Excel (Excel 2000) comportant une suite de lignes horodatées:

la première colonne comporte la date, la deuxième colonne comporte l'heure (de 5 en 5 minutes).

les autres colonnes sont des mesures météo (correspondant à chaque période de 5 minutes)

Chaque fichier (tableau) fais un mois de mesures (donc environ 9000 lignes)

Il s'agit de voir ce qui se passe aux changements d'heure été-hiver, sachant que l'heure (en heure et minutes) dans les tableaux, est toujours continue lors de ces changements: il n'y a pas de saut 01:55 – 03:00 par exemple.

Je voudrait donc détecter (autrement que visuellement !) s'il existe dans un tableau:

1/

une répétition parfaitement identique des mesures deux heures de suite ?

C'est à dire est-ce que, à un moment donné (et pas obligatoirement au moment du changement d'heure), il y a (exactement) les mêmes mesures qui reviennent l'heure suivante.

2/

les mêmes mesures (exactement) répétées (ou absentes) pendant une heure de suite ?

Suis-je assez clair ?

Bonjour,

Un modèle aiderait (peut-être !) Mais il faut surtout que tu précises : la nature des mesures, les critères pour apprécier qu'une mesure est la "même" ou qu'elle est "différente", les plages à comparer.

Pour le moment, je ne vois pas très bien ce dont il retourne.

Bonjour ,

Idem ferrand.

Tu parles de changement d'heure été/hiver. Quand on avance d'une heure, pas de pb (encore que), mais quand on recule, tu as des heures qui se chevauchent.

En météo, comme en beaucoup de domaines, on utilise le Temps Universel, ce qui permet de synchroniser les observations dans le monde entier.

@ +

La discussion sur:

"comment représenter sur un graphique des mesures horodatées lorsqu'il n'y a plus de concordance entre l'heure qu'il est et le temps qui passe (cas d'une journée avec changement d'heure par exemple)"

est passionnante, mais très difficile intellectuellement !

J'ai, en cours, cette discussion sur un autre forum et je n'en parlerai donc surtout pas ici !

Ma question était précise, merci de vous y tenir.

(mes excuses pour cet impératif)

A priori les réponses à tes (vos) questions sont toutes dans mon premier post:

- nature des mesures = mesures météo (température, vent, pression, pluie, etc) mais quelle importance ?

- les critères = j'ai dis: "parfaitement identique" c'est à dire, à part la date et l'heure, exactement la même ligne.

- les plages à comparer = une heure exactement (soit 12 lignes puisqu'il y a une ligne par cinq minutes)

Je pourrais préciser si vos questions sont plus précises…

Je vais faire un modèle, mais vous avez tous les éléments pour le faire si vous avez compris la question.

merci,

Bonjour

voir ce tuto

http://fr.wikihow.com/comparer-des-donn%C3%A9es-dans-Excel

Je comprends pas .

Mais une comparaison est une comparaison et la ligne 14 contient les mesures correspondant à 1 heure après celles de la ligne 2.

Donc, sur la ligne 14, colonne ? => =C14=C2

qui renverra VRAI si la mesure en col. C est identique, FAUX dans le cas contraire !

S'agissant de mesures, sans connaître l'incertitude affectant les mesures, je doute qu'on puisse obtenir un constat significatif , si tant est qu'une telle comparaison soit en elle-même significative de quoi que ce soit dans un modèle donné .

Voilà, vous allez (mieux) comprendre!

Le lien ci-dessous (en bas de ce message) est un tableau Excel de 9000 lignes environ (environ 3Mo)

Ce n'est pas une image et il s'ouvrira chez vous sous Excel (si vous avez Excel sur votre PC)

(attention: peut-être un peu long à télécharger)

Mais vous pourrez le manipuler comme n'importe quel tableau Excel.

Si vous observez attentivement ce tableau vous constaterez que les 12 lignes:

- du 24 mars à 19h20(ligne 6859)

- au 24 mars à 20h15(ligne 6870)

se répètent exactement à l'identique:

- du 24 mars à 20h20 (ligne 6871)

- au 24 mars à 21h15 (ligne 6882) (je dis bien "exactement": il n'y a aucune incertitude, vous pouvez vérifier !)

Bien entendu, je suis ironique !

car il est absolument impossible de détecter cette "anomalie" par simple observation du tableau !

(c'est un peu plus facile si on trace le graphique (*))

De plus ça ne se produit pas forcément le jour du changement d'heure

(dans le cas de ce tableau le changement d'heure était le 25 mars)

Et des tableaux comme ça j'en ai 23 à faire !...

La question que je vous pose est donc de trouver une façon de détecter automatiquement cette anomalie (répétition exacte de 12 lignes)

(j'admets bien entendu, si besoin, de copier-coller une formule dans une (plusieurs) colonnes complètes)

Je soupçonne également une répétition de 12 lignes (une heure) avec 12 fois de suite la même ligne (exactement identique)

qu'il faudrait détecter également.

ainsi que 12 fois de suite tous les paramètres à zéro (c'est un peu plus facile à voir à l'œil, mais tant qu'à faire autant que ce soit automatique !)

Remarque:

ces trois détections peuvent se faire séparément, c'est à dire qu'il n'est pas obligatoire de trouver une seule formule qui fasse les trois détections en même temps.

Je peux très bien, si besoin:

entrer une formule pour la première détection,

puis une autre formule, etc.

ou même utiliser une copie du tableau chaque fois…

Alors, c'est clair ?

http://michel.mo.pagesperso-orange.fr/meteo/mars2012.xls

(*) mais pas facile s'il y a peu de variation dans les paramètres météo,

alors qu'il doit être plus facile de détecter dans le tableau s'il n'y a aucune variation.

ferrand a écrit :

Je comprends pas .

Mais une comparaison est une comparaison et la ligne 14 contient les mesures correspondant à 1 heure après celles de la ligne 2.

Donc, sur la ligne 14, colonne ? => =C14=C2

qui renverra VRAI si la mesure en col. C est identique, FAUX dans le cas contraire !

peut-être, effectivement, que ce n'est pas plus compliqué que ça ! (je n'ai jamais dit que c'était difficile !)

mais, s'il te plait, propose-moi quelque chose de plus aboutis...

(en faisant toi-même des essais avec le tableau que j'ai fourni)

MERCI

ferrand a écrit :

S'agissant de mesures, sans connaître l'incertitude affectant les mesures, je doute qu'on puisse obtenir un constat significatif

............................

Il n'y a aucune incertitude à connaître !

dans le premier cas il s'agit de détecter un bloc de 12 ligne qui se reproduirait exactement pareil les 12 lignes suivantes;

il est déjà extrèmement peu probable que les 9 paramètres météo (les 9 colonnes après la date et l'heure) se retrouvent exactement les mêmes deux lignes de suite.

(2 ou 3 paramètres c'est possible, mais 9, j'en doute; et de toute façon s'il y a quelques litiges je les traiterais manuellement; je ne cherche pas un résultat sans aucune erreur)

Alors si on trouve 12 lignes qui se reproduisent exactement pareil que les 12 lignes suivantes ce sera vraiment le défaut que je cherche.

et encore une fois s'il y a quelques erreurs ce n'est vraiment pas grave, ça m'aura bien aidé par rapport à une recherche visuelle (23 fois 9000 lignes !!...)

Si vous observez attentivement ce tableau vous constaterez que les 12 lignes:

- du 24 mars à 19h20(ligne 6859)

- au 24 mars à 20h15(ligne 6870)

se répètent exactement à l'identique:

- du 24 mars à 20h20 (ligne 6871)

- au 24 mars à 21h15 (ligne 6882) (je dis bien "exactement"

Pas exact !

Donc ?

Bonjour,

Comme toutes les cellules de la ligne ne sont pas identiques

donc j'ai pris comme critères cinq cellules de C à G

lancer la macro avec Alt + F8 puis choisir "test" et cliquer sur executer

une fois lancé attendre l'apparition de la boite de dialogue "terminé"

http://cjoint.com/?ECfqN2SUk72

si pas de problème les cellules recherchées sont colorées en jaune

modifs : nouveau fichier

SOMMEPROD permet facilement de quantifier le nombre de répétitions par blocs de 108 cellules (12*9).

Sur la feuille échantillon, le maximum est 106 et le nombre immédiatement inférieur 99. Le minimum est 12. Il est probable qu'en recensant la répartition du nombre de répétitions, on aura une répartition approchable à une loi Normale, mais ce n'est qu'une répartition statistique, sans signification particulière tant qu'on n' aucun modèle causal explicatif...

A+

Normal 0 21

gilbert_rgi,

J'ai essayé et ça marche très bien ! (pour le premier cas: les 12 lignes qui se répètent).

Je te remercie et je suis un peu gêné de te dire que ce n'est pas vraiment ce que je cherche.

Je ne veux pas passer par une macro, et surtout je cherche quelque chose qui ne soit pas complètement transparent pour moi (et hors de mes compétences) et donc que je puisse comprendre et modifier moi-même.

N'est-il pas possible de trouver seulement une formule que l'on copierait dans une colonne et qui comparerait (à chaque ligne) les 12 dernières lignes avec les douze précédentes (hors date et heure) ?

et suivant le résultat on affiche (à chaque ligne) un signe quelconque (un zéro par exemple), ou rien, dans une autre colonne.

(c'est peut-être un peu ce dont parlait ferrand dans son message du 4/3 à 12h58 ?)

un truc du genre:

à la ligne 860 par exemple:

Si c860 = c(860-12), et d860 = d(860-12), et e860 = e(860-12), etc..(jusqu'à k860)

alors L860 = 0, sinon L860=vide

Ceci aurait le gros avantage que je pourrai modifier moi-même la formule en fonction d'autres critères répétitifs (12 fois la même ligne par exemple, ou autres).

par exemple:

Si c860 = c859=c858=c857…..et d860 = d859=d858=d857…et e860=e859….

alors M860 = 0, sinon M860 = vide

Remarque:

la comparaisons doit se faire sur les N° de ligne et non sur l'heure car j'ai des cas où il y a des saut d'heure (contrairement à ce que j'ai dit dans mon premier post)

merci pour votre aide.

Bonjour,

J'ai essayé et ça marche très bien ! (pour le premier cas: les 12 lignes qui se répètent).

Je te remercie et je suis un peu gêné de te dire que ce n'est pas vraiment ce que je cherche.

pas grave mais dommage beaucoup plus difficile à réaliser avec des formules

Je ne veux pas passer par une macro

ce n'était pas stipulé

je cherche quelque chose qui ne soit pas complètement transparent pour moi

à vous de voir qui n'avance pas recule

voir les fonctions sommeprod , index et equiv

J'ai donné la réponse dans mon post du 5 à 15h10.

Elle permet un calcul du nombre de répétitions des valeurs mesurées au cours d'une heure donnée par rapport aux mesures de l'heure qui précède, calcul effectué de 5 minutes en 5 minutes.

Il ne peut y avoir de réponse binaire, éventuellement, qu'après analyse de ce résultat brut.

Ça doit être mon jour de bonté : http://cjoint.com/?EChm06kMZxi

Les calculs faits sur la 2e feuille apportent une vague information supplémentaire sur la répartition...

Cependant : la courbe étant dissymétrique, il n'est pas établi que l'approximation par une loi Normale qui y est faite soit valide, il faudrait d'autres tests pour savoir quel modèle statistique permet la meilleure approche. En outre, l'absence de biais n'est pas garantie, ce qui pourrait affecter sensiblement les valeurs constatées (et on reste dans une statistique abstraite, en l'absence d'une pré-étude qualitative).

Normal 0 21

Toutes mes excuses, ferrand, mais je ne comprends pas pourquoi tu traites cette question par statistique ?

Il ne s'agit pas de déterminer une probabilité pour que des mesures météo se reproduisent plus ou moins identiques, mais bien de détecter une reproduction des valeurs affichées parfaitement identiques (due à un problème informatique et non aux mesures météo elles-même).

Mais j'ai trouvé moi-même une partie de la solution:

concernant la détection d'un bloc de 12 lignes qui se répète, j'utilise (colonne L) la formule:

=ET(C13=C1;D13=D1;E13=E1;F13=F1;G13=G1;H13=H1;I13=I1;J13=J1)

(ne pas utiliser la colonne K, celle qui a en titre un "?")

qui me répond VRAI ouFAUX

et ensuite j'utilise la fonction "Edition / Rechercher" (par "colonne", dans "valeurs") de Excel pour trouver les lignes contenant "VRAI"

En procédant ainsi, dans ce tableau, on trouve bien "VRAI" dans les lignes 6871 à 6882

C'est exactement ce que je cherchai !

Par contre, et là vous pouvez sans doute m'aider encore,

je n'arrive pas à trouver la bonne formule (dans le même esprit que la précédente) me permettant de détecter 12 lignes identiques qui se suivent. (12 lignes ou plus) (*)

Pourtant il me semble que ça devrait être plus simple ?

Voir le nouveau tableau avec la formule utilisée (colonne L)

et la formule à trouver (colonne M)

c'est ici: http://michel.mo.pagesperso-orange.fr/meteo/mars2012.xls

merci pour votre contribution, votre patience et votre pédagogie…

(*) dans ce tableau ce cas n'existe peut-être pas ?

donc pour tester la formule il faudra peut être créer artificiellement 12 lignes identiques dans ce tableau.

J'accepte tes excuses . Je déteste travailler en aveugle, sans avoir une idée de la finalité de ce que je fais ... Ton problème n'a rien à voir avec les mesures, il était bon de le préciser au départ. Tu réduis le nombre de colonne à tester à 8. Cela ne change rien à la solution que j'ai donné : SOMMEPROD est la fonction la plus adéquate pour ce type de dénombrements. Lorsqu'on regarde s'il y a égalité entre 2 'blocs' de 12 lignes sur 8 colonnes, le dénombrement des égalités de cellule à cellule occupant la même place dans chacun donnera 96 (=12*8) si l'égalité est totale.

On compare la plage jaune à la plage verte : en les faisant glisser tout au long du tableau, c'est le seul endroit où le résultat maximal recherché est atteint. L'égalité des deux plages (dans la formule) renvoie une matrice de 96 VRAI ou FAUX, selon qu'il y a égalité ou non entre chaque cellule correspondante, l'opérateur "--" transforme ces valeurs booléennes en 1 ou 0, que SOMMEPROD additionne.

Sachant que le résultat que tu cherches est 96, tu peux évidemment mettre la formule sous condition pour qu'elle affiche ce que tu souhaites...

Le problème est un peu moins simple pour rechercher une répétition de 12 lignes identiques.

(Voir ci-après)

Détection de 12 lignes identiques :

On peut de la même façon que précédemment, comparer chaque ligne à la suivante :

Le résultat sera 8 s'il y a identité [cette formule est équivalente à ta recherche avec ET...]. En la tirant, si l'on obtient une succession de 11 fois le résultat 8, cela signifie que les 12 lignes en partant du premier 8 sont identiques. Mais cela oblige à refaire un calcul sur le résultat de premier.

Autre possibilité de formule : on dénombre avec NB.SI la répétition des valeurs de la première ligne d'une matrice de 12 lignes x 8 colonnes qu'on fait additionner avec SOMMEPROD : le résultat doit donc être 96 si l'on a répétition sur 12 lignes.

Parfait donc ! Mais il y a un petit "hic" comme le montre l'exemple ci-dessous :

NB.SI dénombre la répétition de chaque valeur de la première ligne sur l'ensemble de la matrice, et lorsqu'une valeur se trouve identique sur 2 colonnes ou plus le résultat se trouve gonflé. Le cas se produit dans le tableau. Il faut donc corriger le résultat de la formule pour obtenir une valeur significative identique dans tous les cas.

Si les 8 valeurs de la ligne sont différentes, la somme de dénombrement des répétitions sur la lignes sera 8 (résultat de la formule = 96). Si 2 des valeurs sont identiques chaque valeur répétée comptera pour 2 et le total de ce dénombrement donnera 10 (résultat de la formule =120). Si 3 identiques, le dénombrement de la ligne sera 14 (résultat formule = 168)... En divisant le résultat brut de la formule par le dénombrement de répétitions sur la première ligne, on ramène le résultat initial au nombre de ligne identique, soit 12.

NB- A noter que les valeurs 0.000 et 0.0 ne sont pas identiques car les valeurs de ce tableau sont des valeurs texte.

On aboutit donc à la formule finale :

On aboutit donc à la formule finale :

Si 12 lignes sont identiques, le résultat de la formule sera 12. A mettre éventuellement sous condition selon le résultat que l'on veut afficher.

Ainsi je comprends beaucoup mieux !

MERCI