XL 2013 Recherche conditionnelle dans une liste

[Guardi_Guedj]

Bonjour,

Tout d'abord désolé pour ce titre peu explicite, mais je ne sais trop comment résumer le sujet.
Ayant jusqu'à maintenant utilisé excel pour des choses très basiques, je n'arrive pas à voir par quel bout prendre mon problème.

Voici son exposé que je simplifie au maximum.
Je dispose d'une table contenant 4 colonnes :

- timestamp, horodatage associé à chacun des événements de ma liste
- ID, qui identifie de manière unique différents objets (que je nomme A, B, C...)
- Action : au cours du temps, chaque objet ne peut faire que 3 actions : disons MOVE, TURN, STOP
- Value : aux actions MOVE et TURN est associée une mesure d'une grandeur, qui va par exemple de 1 à 10

Pour l'action STOP, aucune mesure n'est effectuée par l'objet et la colonne Value est donc vide.

Mon but est très simple : j'aimerais pouvoir, pour chaque ligne correspondant à une action de type STOP, écrire dans le champ Value la dernière valeur vue dans la liste (donc mesurée) pour l'objet en question (recherche vers le haut basée sur l'ID donc).

En clair dans mon exemple ci dessous, je cherche à compléter les cellules D7 et D10 :
-> D7 : dernière valeur mesurée pour A = 2
-> D10 : dernière valeur mesurée pour B = 8

Le but étant ensuite de ne filtrer que les actions de type STOP puis de faire des statistiques sur les dernières valeurs mesurées à chaque fois... Je travaille sur une base de données d'environ 100000 lignes dont environ 10% de lignes de type STOP (donc de recherches à effectuer).

Y'a t'il un moyen simple de s'en sortir uniquement avec des fonctions excel ?
Merci par avance pour votre aide.

 

[CISCO]

Bonsoir

Oui, il y a moyen, mais avec des formules, cela risque de ramer... Je te fais ça demain.

@ plus

 

[Guardi_Guedj]

Bonjour Cisco,

Merci pour ta réponse.
Oui effectivement, quand bien même ça serait possible par de "simples" formules, je m'interroge sur les performances compte-tenu du volume à traiter. Néanmoins je suis curieux de savoir comment on s'en sort avec les fonctions classiques.

Sinon j'imagine qu'il faut passez au VBA ?

Bonne journée

 

[JHA]

Bonjour à tous,

Un essai par formule matricielle

JHA

 

[Jocelyn]

Bonjour le Forum,
Bonjour Guardi_Guedj, CISCO,

Pour le fun un essai par formule matricielle

Cordialement

EDIT : Bonjour l'ami JHA , arf grillé de 2 minutes

 

[Guardi_Guedj]

Bonsoir JHA et Jocelyn !


Tout d’abord un très grand MERCI pour votre aide, ça semble tellement simple pour vous ! J’aurais aimé pouvoir répondre plus tôt mais je manque de temps.

Effectivement cela marche très bien sur mon petit exemple, mais malheureusement je n’arrive pas à reproduire ces solutions sur mes vraies données L.


Dans mon cas les IDs sont en réalité des nombres sur plusieurs digits, et la colonne Value contient des valeurs alphanumériques et non une simple valeur entre 0 et 10. Mais cela ne devrait rien changer à l’algorithme non ?

Quoi qu’il en soit j’aimerais persévérer par moi-même sur la base de vos propositions.


Je me suis surtout pour l’instant concentré sur la première solution proposée par JHA.


Si je comprends bien le principe est d’utiliser la fonction INDEX sur la colonne Value afin de récupérer la valeur associée à la dernière position de l’ID.

Donc "en gros" INDEX (colonne D ; last position of ID)


Je comprends ensuite que le deuxième argument de cette fonction (mon last position) se calcule en utilisant une fonction GRANDE.VALEUR qui va me renvoyer « 1-ème » plus grande valeur d’une liste.


C’est à l’intérieur de cette fonction GRANDE.VALEUR que je suis complètement largué.

Sur le principe, je pensais que cette dernière allait servir à retourner le numéro de ligne où le dernier évènement a été vu pour l’ID en question.

Par exemple en D7, nous donner la valeur 4 pour l’ID A puisque sa dernière activité correspond à la 4ème ligne de la liste globale.


Or lorsque j’exécute cette fonction GRANDE.VALEUR isolément, je m’aperçois que ça n’a pas l’air tout à fait cela.

Je m’excuse de mon faible niveau, mais j’ai vraiment du mal à décrypter le premier argument de cette fonction GRANDE.VALEUR.


Si jamais vous avez le temps de détailler un peu, ça serait top pour que je puisse progresser !

Merci et bonne soirée !!

 

[Guardi_Guedj]

Ok après avoir repris ça à tête reposée, je pense avoir compris.
En fait avec l'expression suivante :

SI(($B$2:$B9=$B9)*($C$2:$C9<>"Stop");LIGNE($D$2:$D9)-1

On recrée artificiellement une sorte de tableau à une seule colonne qui ne contient que les numéros de lignes (en partant de 0) pour lequel on voit l'ID en B9 (dans cet exemple) et un événement != STOP.
C'est bien cela ?

C'est comme un genre de boucle for que l'on ferait en filtrant sur l'ID ?
Si c'est bien cela je ne savais pas qu'on pouvait faire cela comem ça !

Merci encore

 

[CISCO]

Bonsoir

Pour voir les calculs intermédiaires :
* Sélectionner la cellule
* Onglet Formules
* Évaluation de formule.

@plus

 

[JHA]

Bonjour à tous,

Tu as compris la formule mais je récapitule la demande.
Mon but est très simple : j'aimerais pouvoir, pour chaque ligne correspondant à une action de type STOP, écrire dans le champ Value la dernière valeur vue dans la liste (donc mesurée) pour l'objet en question (recherche vers le haut basée sur l'ID donc).

par exemple pour la ligne"7":

=INDEX($D$2:$D7;GRANDE.VALEUR(SI(($B$2:$B7=$B7)*($C$2:$C7<>"Stop");LIGNE($D$2:$D7)-1);1))

Cette formule se décompose comme suit:
GRANDE.VALEUR(SI(($B$2:$B7=$B7)*($C$2:$C7<>"Stop");LIGNE($D$2:$D7)-1);1)
on détermine le plus grand numéro de ligne en cherchant la plus grande valeur du dessus ("GRANDE.VALEUR("critères";1)") qui appartient au critère "ID" de la cellule "B7" et qui n'est pas noté "Stop" dans la colonne "Action" ("($B$2:$B7=$B7)*($C$2:$C7<>"Stop")").

Pourquoi LIGNE($D$2:$D7)-1, comme la plage débute à la seconde ligne, on aurait ("{2;3;4;5;6;7}") avec le "-1" on a ("{1;2;3;4;5;6}").
INDEX($D$2:$D7;N° de ligne)
Une fois la ligne trouvée, on recherche dans la colonne "Value" la valeur de la ligne 4.

JHA

 

[Guardi_Guedj]

Bonjour,


Super, merci bien Cisco pour la fonctionnalité « Evaluer la formule », c’est effectivement très pratique ! (Oui, je pars de loin ^^)

Et encore un grand merci JHA, je comprends donc maintenant très bien chaque élément de la fonction.


Mais il doit y avoir encore qqchose qui m’échappe car comme je l’expliquais hier, je n’arrive pas à reproduire cette formule sur mes données.

Ci-joint un petit échantillon.


En fait le problème est que dès la première évaluation de la fonction, le test booléen Si (A and B) n’est pas réalisé correctement.

En effet je constate que ça ne prend pas du tout les vecteurs ID et Action mais uniquement les valeurs à la ligne où je me trouve.



Puis-je abuser de votre aide et vous demander pourquoi ça fait cela dans mon fichier (ci-joint) ?

D’avance un grand merci.

 

[CISCO]

Bonsoir

Cela buggue pour deux raisons :
1) Il faut valider en matriciel la formule (Sélectionner la formule. Cliquer dans la barre de formule. valider avec les 3 touches Ctrl+maj+entrer).
2) Dans B28, tu as 1720271361508120 et on ne trouve pas cette valeur dans la plage B2:B27. Conclusion : Que faut-il écrire dans D28 puisqu'on ne trouve pas la valeur repère au dessus dans la colonne B ? Comme la formule ne trouve pas ce n° de ligne, elle te renvoie #NOMBRE.

@ plus

 

[Guardi_Guedj]

Bonsoir Cisco,

Ok !!!! Et bien j'ignorais cette façon matricielle de valider !
Du coup effectivement ça marche beaucoup mieux

Un grand merci à vous trois pour votre aide précieuse, j'ai appris des choses grâce à vous !
Je vous souhaite une excellente soirée, je devrais vous laisser tranquille maintenant !