Extraction conditionnelle un peu compliquée

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

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

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 !

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

Voilà le fichier de base

Bon courage... Ma journée commence, mais ne te couche pas trop tard...

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

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 !!!

Re

Sans colonne intermédiaire :

Fichier

Amicalement

Nad

Ok, ça marche parfaitement !

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% !

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

Bonjour

Avec l'aide de Dan, j'ai converti les formules dans des macros.

Peux-tu tester si c'est suffisant ?

Tu entres une valeur en G1 et tu valides.

Fichier

Amicalement

Nad

Bonjour la France ! Quel temps fait-il à Souston ? (Souston... ah... mon enfance !)

Mon fichier complet original fait 220 000 lignes ! Il m'a fallu laisser l'ordi tourner toute la nuit pour faire les calculs pour les intervalles de 4h. Ce soir, je lance les 2H... Demain, les 1H ! On verra si j'ai besoin de 0,5 heures et de 3H !!!

En tout cas, je me répète peut-être, mais merci de ton soutien !

Amicalement,

Anthax

Bonjour à tous,

Bonjour Nad,

https://www.excel-pratique.com/~files/doc2/Extrait_Pair.xls

En reprenant le fichier du début, je filtre les heures pair,

si çà peut déjà simplifier les formules et alléger ?

on peut ajouter d'autres critères de filtre.

Amicalement

Claude.

Bonsoir Claude

Merci de ton aide.

Peux-tu essayer de remplacer dans la macro le SUMPRODUCT ?

Je t'explique la demande d'Anthax :

1 - On vérifie que les cellules de la colonne D2:D dernière ligne sont divisible par G1 (partie MOD de la macro qui fonctionne très bien) - Si réponse = NON, alors la cellule colonne G prend valeur 0

2 - Si réponse = OUI, on calcule :

Plage colonne A=A2 ET (Plage colonne B+Plage colonne C (concatenation de la date et de l'heure)= B2+C2+la valeur en heure de G1) (exemple, valeur de G1 égal 2 doit retourner "02:00")

........................................................................................................................................................OU B2+C2-la valeur en heure de G1)

Si la réponse est VRAI, alors la cellule colonne G prend valeur 1 - Autrement, valeur 0

Autre façon d'expliquer la partie SOMMEPROD :

Si pour le même numéro en colonne A, l'intervalle Date+Heure est égal à G1 alors 1

L'intervalle vaut pour avant et après la Date+Heure recherchée.

Voilà, je pense que si on peut éviter le SOMMEPROD on gagnerait en rapidité d'exécution.

Si tu veux bien essayer ... Bien sûr cela vaut pour tous nos amis VBAïste

Amicalement

Nad

re,

Nad, je suis un peu largué, d'autant que je n'ai pas tout suivi, peux-tu écrire la formule,

j'essayerais de la mettre dans la macro.

C'est ce que tu veux ?

à+.....Claude.

Re

Non Claude, la formule est déjà dans la macro (voir le dernier fichier que j'ai posté - Sub Divisible())), mais j'ai peur qu'elle ne ralentisse le code.

Ce qu'il faudrait, c'est justement la transformer en un code qui serait moins gourmand

Amicalement

Nad.

re,

Tu peux aussi mettre le résultat en dur (plus de formules dans le fichier)

en ajoutant cette ligne de code sous la formule :

        Else: .Offset(0, 3).FormulaR1C1 = "=IF(SUMPRODUCT((etc...
            .Offset(0, 3) = .Offset(0, 3) * 1
        End If

Claude.

Merci Claude

Anthax, tu n'as plus qu'à essayer ce

Nouveau Fichier

et nous tenir au courant du temps de réponse.

Amicalement

Nad, Dan et Claude

Bon, je dois avouer que même moi qui suis à l'origine de ce post, j'ai du mal à suivre ! Lol !

Non, sérieusement : NAD fait un gros effort pour m'aider, et je lui en suis très reconnaissant...

Malheureusement, dans le dernier fichier que tu as posté, ça ne marche plus : la formule sort TOUS les points pour lesquels il existe une localisation X heures avant ou après... Or moi je ne veux que celles toutes les X heures à partir de minuit...

Donc l'avant-dernier fichier que tu as posté avec le commentaire "sans colonne intermédiaire" était mieux (sauf que la colonne intermédiaire me convenait bien !). Ainsi, on aurait :

  • une colonne qui définit si l'horaire de la loc pourrait rentrer dans un planning "X heures" à partir de minuit ; (=If(MOD(...)=0,1,0)
  • une colonne qui détermine TOUTES les localisations pour lesquels il existe une autre localisation X heures avant OU après ;
  • et enfin une 3e colonne qui croise les données des deux premières colonnes (juste une multiplication) pour me sortir mes points.

Selon moi, c'est la meilleure façon de procéder... car ensuite selon les besoins de mon étude, je pourrais avoir à utiliser les valeur de la 2e colonne... peut-être !

Donc la formule qui est très lourde, c'est celle de la 2e colonne qui comporte plusieurs SUMPRODUCT et qui regarde dans 3 plages différentes (DeerUID, Date & Time) sur plus de 220 000 entrées !

Y'a un moyen de vous envoyer des photos afin de vous montrer les chevreuils, les loups et les pumas que je capture pour mon étude, histoire de vous montrer l'aspect génial qu'il peut y avoir derrière ces tableaux de données et ces formule indigestes ? 8)

Zut ! Le temps que j'écrive mon message, y'a eu d'autre posts !

Bon, je lis ça et je vous dis...

image1
Rechercher des sujets similaires à "extraction conditionnelle peu compliquee"