Extraction conditionnelle un peu compliquée

Y compris Power BI, Power Query et toute autre question en lien avec Excel
N
Nad-Dan
Passionné d'Excel
Passionné d'Excel
Messages : 7'305
Inscrit le : 27 avril 2007
Version d'Excel : 2003 FR-2007 FR-MAC 2004 FR

Message par Nad-Dan » 2 juin 2009, 22:49

Re

J'ai simplifié la formule pour que tu puisses mieux la comprendre (ce qui a pour effet que tu as des #NA quand la réponse n'est pas trouvée) et je t'ai mis des explications.

Fichier

Amicalement
Nad
A
Anthax
Jeune membre
Jeune membre
Messages : 45
Inscrit le : 1 juin 2009
Version d'Excel : 2007 EN

Message par Anthax » 4 juin 2009, 07:13

En effet, je comprends mieux, non pas grâce à la formule simplifiée, mais plutôt grâce aux explications : ce que je ne pigeais pas, c'est que tu avais nommé des plages... moi je pensais que les références étaient le titre de mes colonnes !

Merci pour ces détails, mais le processus est trop lourd, et comme ma feuille Excel comporte une vingtaine de colonne et environ 200 000 lignes, c'est trop lourd à gérer.
Donc je passe à Access pour cette partie.

Mais je reviens sur ma formule qui me permet d'identifier avec des 1 les lignes correspondant à des localisations faites toutes les 4 heures (par ex.)... Non seulement elle est compliquée, j'ai beaucoup de mal à en expliquer la logique à mes collègues, mais en plus, dans le cas de "toutes les 4 heures" justement, il y a parfois des erreurs qui se glissent... (voir en M68) Alors je réfléchis à une meilleure façon de procéder, plus simple et logique... et j'ai une idée plus claire ! Mais je ne parviens pas à la mettre en "formule" !
J'ai juste un début que je présente dans le fichier ci-dessous et que détaille ensuite, mais elle présent tout de même un problème majeur que je ne parviens pas à résoudre...

Nouvelle Méthode


L'idée, la voici :

- pour éliminer le problème des dates et des changements de jours, j'ai créé un point de référence complètement arbitraire, à l'instant T : le 1er janvier 2006 à minuit (en J1 et K1). Puis dans la colonne J, je calcule le nombre d'heures qui séparent une localisation donnée de cet instant T Zéro...
Jusque là, ça va ?

- Ensuite, la demande que je fais dans la colonne K, c'est : "pour chaque localisation i à l'instant ti, regarder dans la colonne J pour savoir s'il existe dans mon tableau une localisation faite à ti+4 ou à ti-4 ?" Si oui, alors on met de côté (en affichant le nombre de localisations faites à l'instant ti+4 ou ti-4)... Sinon, on jette (0).
(NB : Quand je dis +4 ou -4, je parle en fait de la valeur X entrée en I1)

- Enfin, dans la colonne L en multipliant le résultat obtenu en K par celui obtenu en I (qui je le rapelle nous disait si l'heure de la localisation pouvait entrer dans un planning "X heures"), j'obtiens alors des 1 uniquement pour les localisations qui m'intéressent : celles pour lesquelles il existe une localisation X heures avant OU après.

Donc jusque là, ça marche bien...
A
Anthax
Jeune membre
Jeune membre
Messages : 45
Inscrit le : 1 juin 2009
Version d'Excel : 2007 EN

Message par Anthax » 4 juin 2009, 07:39

Le problème apparait lorsque dans mon fichier global, plusieurs chevreuil différents ont un collier qui enregistre leur localisation le même jour au même instant...

Voir le fichier avec le problème

Du coup, les instants ti des localisations ne sont pas uniques et ma formule en K trouve les valeurs ti+4 ou ti-4 pour d'autres chevreuil, me renvoie alors des chiffres supérieurs à 1, ou des 1 quand il ne devrait pas y en avoir pour ce chevreuil, et cela me fausse tout en L.

Il faudrait que je parvienne à améliorer cette idée, mais indiquer avec la formule en K de ne chercher le temps ti+4 et ti-4 que dans la zone de la plage K qui correspond à un même chevreuil ! Ainsi, on ne cherche ti+4 et ti-4 QUE pour ce chevreuil ! Et ils seraient alors uniques.

Et je me suis creusé la tête, et je n'ai pas réussi à faire ça !

J'espère que toute cette confusion est clairement expliquée ! :D
N
Nad-Dan
Passionné d'Excel
Passionné d'Excel
Messages : 7'305
Inscrit le : 27 avril 2007
Version d'Excel : 2003 FR-2007 FR-MAC 2004 FR

Message par Nad-Dan » 4 juin 2009, 14:31

Bonjour

J'avoue que c'est de plus en plus confus.

Pourrais-tu m'envoyer un fichier avec uniquement les données de base. Tu ajoutes 2 colonnes : 1 avec les résultats attendus pour 2h d'intervalles et une autre pour 4h. (Manuellement bien sûr)

Ensuite, je reprendrai tout à tête reposée.

Amicalement
Nad
A
Anthax
Jeune membre
Jeune membre
Messages : 45
Inscrit le : 1 juin 2009
Version d'Excel : 2007 EN

Message par Anthax » 4 juin 2009, 16:13

Voilà le fichier de base

Bon courage... Ma journée commence, mais ne te couche pas trop tard... :lol:
N
Nad-Dan
Passionné d'Excel
Passionné d'Excel
Messages : 7'305
Inscrit le : 27 avril 2007
Version d'Excel : 2003 FR-2007 FR-MAC 2004 FR

Message par Nad-Dan » 4 juin 2009, 20:33

Re

Je suis repartie sur ma 1ère idée et j'ai trouvé (du moins il me semple)
J'ai testé avec 2h00 et 4h00 et je n'ai pas eu d'erreur.

Peux-tu faire d'autres tests et me dire si c'est bon ?

Fichier

Amicalement
Nad
A
Anthax
Jeune membre
Jeune membre
Messages : 45
Inscrit le : 1 juin 2009
Version d'Excel : 2007 EN

Message par Anthax » 4 juin 2009, 20:47

WOW!
Sacré boulot, merci énormément pour le temps et les neurones que tu te grilles pour moi !
A première vue, ça a l'air d'être ça...
En anglais, la formule qui apparait sur mon écran est "SUMPRODUCT", et je pense que c'est ce que je cherchais à faire en fait avec mon "NB.SI"
Je ne savais pas qu'on pouvait additionner en une cellule une date et une heure ! C'est pratique.

Bon, bah ça va être testé sur plusieurs centaines de milliers de cas, alors je saurais si ça marche dans quelques heures... Je te tiens au courant !!!
N
Nad-Dan
Passionné d'Excel
Passionné d'Excel
Messages : 7'305
Inscrit le : 27 avril 2007
Version d'Excel : 2003 FR-2007 FR-MAC 2004 FR

Message par Nad-Dan » 4 juin 2009, 21:21

Re

Sans colonne intermédiaire :

Fichier

Amicalement
Nad
A
Anthax
Jeune membre
Jeune membre
Messages : 45
Inscrit le : 1 juin 2009
Version d'Excel : 2007 EN

Message par Anthax » 5 juin 2009, 00:03

Ok, ça marche parfaitement ! :D

Le hic, c'est que la formule est si compliquée, et les plages à parcourir sont si grandes, et l'opération répétée 200 000 fois, ça va prendre des heures et des heures avant de pouvoir me faire tous les calculs !
Je l'ai lancé il y a 15 minutes, et il vient à peine de passer à 2% ! :cry:
N
Nad-Dan
Passionné d'Excel
Passionné d'Excel
Messages : 7'305
Inscrit le : 27 avril 2007
Version d'Excel : 2003 FR-2007 FR-MAC 2004 FR

Message par Nad-Dan » 5 juin 2009, 06:56

Bonjour

Ton fichier original comporte combien de lignes ?

Peut-être qu'un membre du forum peut faire une macro à la place des formules ?

J'en appelle aux VBAïstes !

amicalement
Nad
Répondre Sujet précédentSujet suivant
  • Sujets similaires
    Réponses
    Vues
    Dernier message