> Tous les forums > Forum Bureautique
 détecter une discontinuité dans un tableau horodaté
Ajouter un message à la discussion
Page : [1] 
Page 1 sur 1
mm91
  Posté le 03/03/2015 @ 13:12 
Aller en bas de la page 
Astucien

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 ?



Modifié par mm91 le 03/03/2015 13:16
Publicité
ferrand
 Posté le 03/03/2015 à 14:11 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

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.

poussebois
 Posté le 03/03/2015 à 14:30 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Grand Maître astucien

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.

@ +

mm91
 Posté le 04/03/2015 à 10:28 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Astucien

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,

gilbert_rgi
 Posté le 04/03/2015 à 11:23 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Astucien

ferrand
 Posté le 04/03/2015 à 12:58 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

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 !

ferrand
 Posté le 04/03/2015 à 13:07 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

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é .

mm91
 Posté le 04/03/2015 à 16:24 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Astucien

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.



Modifié par mm91 le 04/03/2015 16:53
mm91
 Posté le 04/03/2015 à 17:40 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Astucien
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

Publicité
mm91
 Posté le 04/03/2015 à 17:58 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Astucien
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 !!...)

ferrand
 Posté le 04/03/2015 à 18:24 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

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 ?

gilbert_rgi
 Posté le 05/03/2015 à 14:51 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Astucien

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



Modifié par gilbert_rgi le 05/03/2015 16:30
ferrand
 Posté le 05/03/2015 à 15:10 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

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+

mm91
 Posté le 07/03/2015 à 09:55 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Astucien

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.



Modifié par mm91 le 07/03/2015 09:58
gilbert_rgi
 Posté le 07/03/2015 à 11:47 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Astucien

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

ferrand
 Posté le 07/03/2015 à 12:52 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

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).

mm91
 Posté le 08/03/2015 à 08:35 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Astucien

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.



Modifié par mm91 le 08/03/2015 08:43
Publicité
ferrand
 Posté le 08/03/2015 à 15:01 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

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)

ferrand
 Posté le 08/03/2015 à 15:50 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

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 :

ferrand
 Posté le 08/03/2015 à 15:56 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

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.

mm91
 Posté le 09/03/2015 à 13:56 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Astucien

Ainsi je comprends beaucoup mieux !

MERCI

Page : [1] 
Page 1 sur 1

Vous devez être connecté pour participer à la discussion.
Cliquez ici pour vous identifier.

Vous n'avez pas de compte ? Créez-en un gratuitement !
Recevoir PC Astuces par e-mail


La Lettre quotidienne +226 000 inscrits
Avec l'actu, des logiciels, des applis, des astuces, des bons plans, ...

Les bonnes affaires
Une fois par semaine, un récap des meilleurs offres.

Les fonds d'écran
De jolies photos pour personnaliser votre bureau. Une fois par semaine.

Les nouveaux Bons Plans
Des notifications pour ne pas rater les bons plans publiés sur le site.

Les bons plans du moment PC Astuces

Tous les Bons Plans
155,89 €Kit de 32 Go (2x16 Go) de mémoire DDR4 Corsair Vengeance RGB Pro 3600 MHz à 155,89 € livré
Valable jusqu'au 19 Septembre

Amazon Allemagne fait une promotion sur le kit de 32 Go (2x16 Go) de mémoire DDR4 Corsair Vengeance RGB Pro 3600 MT/s CL18 à 151,16 € (avec la TVA ajustée). Comptez 4,73 € pour la livraison en France soit un total de 155,89 € livré alors qu'on le trouve ailleurs autour de 190 €. Vous pourrez personnaliser la palette de couleurs directement depuis le logiciel Corsair iCU.

Vous pouvez utiliser votre compte Amazon France sur Amazon Allemagne et il n'y a pas de douane.


> Voir l'offre
-30%La semaine Dim: -30% sur une sélection de sous vêtements homme et femme
Valable jusqu'au 20 Septembre

C'est la semaine Dim chez Amazon avec jusqu'à 30% de remise immédiate sur une sélection de sous vêtements homme et femme.


> Voir l'offre
19,99 €Casque sans-fil bluetooth Philips TAH5205BK à 19,99 €
Valable jusqu'au 18 Septembre

Cdiscount fait une promotion sur le casque sans-fil bluetooth Philips TAH5205BK qui passe à 19,99 € au lieu d'une quarantaine d'euros habituellement. Ce casque offre des basses puissantes et percutantes et une bonne isolation phonique. Des commandes sur la coque vous permettent de contrôler vos appels, la musique et le volume. Le casque offre une autonomie de 29 heures et peut être plié pour prendre moins de place dans le sac. Recharge via USB-C.


> Voir l'offre

Sujets relatifs
Récupération dates dans tableau excel 2007
macro afficher masquer lignes tableau dans word 2010
Mettre une liste d'une page Word dans un tableau Excell ?
excel intergrer dans un tableau des valeurs venant de tableau
Selectionner valeur dans tableau (vba)
Comment insérer un tableau Excel de calcul dans un site?
tri/nom dans tableau open office
Insere tableau dans un userform
Multi-fusion à l'aide d'un tableau dans une page
photo en arrière-plan dans un tableau excel
Plus de sujets relatifs à détecter une discontinuité dans un tableau horodaté
 > Tous les forums > Forum Bureautique