> Tous les forums > Forum Bureautique
 Créer une liste dynamique en fonction d'un résultatSujet résolu
Ajouter un message à la discussion
Page : [1] 
Page 1 sur 1
Labougie
  Posté le 27/06/2017 @ 00:50 
Aller en bas de la page 
Groupe Sécurité

Bonsoir,

Je cherche à créer une liste (pas déroulante).

Cette formule doit récupérer une information dans la colonne A si et seulement si en colonne y l'on trouve le mot "attention". (sur la même ligne).

Ensuite après avoir collecté l'info, dresser une liste dans la colonne d. Cette liste sera donc dynamique, à savoir qu'elle est dépendante du mot ''Attention" trouvé en y.

si une fois le mot alors une ligne, si 10x le mot alors 10 lignes etc...

Je suis parti de ceci

=INDEX(a:a;EQUIV("attention";y:y;0);1)

le 0 est pour la valeur excate.

Si j'étire ma formule vers le bas, je ne trouve que le 1er résultat contenant "attention" .

Merci pour votre lecture.

Labougie



Modifié par Labougie le 27/06/2017 09:25
Publicité
ferrand
 Posté le 27/06/2017 à 02:50 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

Bonsoir,

En D1 : =SIERREUR(INDEX(A:A;PETITE.VALEUR(SI(Y:Y="attention";LIGNE(Y:Y);"");LIGNE(1:1)));"")

Formule matricielle (à valider par Ctrl+Maj+Entrée). Et tirer vers le bas...

Cordialement.

Labougie
 Posté le 27/06/2017 à 10:46 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Groupe Sécurité

Bonjour Ferrand,

Merci pour ta solution.

Ma colonne "a" contient 200 lignes, et en colonne d, je ne souhaite obtenir que les informations utiles. (je ne devrais pas avoir à recopier vers le bas + de 15 lignes). c'est cela ma problèmatique.

Debrief m'a aidé il y a quelques semaines sur cette formule, qui fonctionne à merveille. .

=SIERREUR(INDEX(INDIRECT("$C$4:$C$" & 4 - 1 + NBVAL($A$3:$A$65534));EQUIV(0;INDEX(NB.SI($G$3:G3;INDIRECT("$C$4:$C$" & 4 - 1 + NBVAL($A$3:$A$65534)));0;0);0)); "")

J'ai pourtant bien remplacé par les bonnes colonnes et en faisant attention aux $, mais rien n'y fait, (rien ne ressort).

Le equiv(0 a bien été remplacé par equiv("attention"

voici ma formule

=SIERREUR(INDEX(INDIRECT("'Suivi Entretien'!$a4:$a$"&4-1+NBVAL('Suivi Entretien'!$Y$3:$Y$65534));EQUIV("attention";INDEX(NB.SI($M$3:M3;INDIRECT("'Suivi Entretien'!$a$4:$a$"&4-1+NBVAL('Suivi Entretien'!$Y$3:$Y$65534)));0;0);0));"")

critère de recherche texte à extraire sur ===> Suivi Entretien'!$a4:$a$

colonne dans laquelle je dois trouver le mot "attention" ===> 'Suivi Entretien'!$Y$3:$Y$65534

Colonne dans laquelle le résultat sera prononcée ===> INDEX(NB.SI($M$3:M3 cette colonne est un "résumé"

Mon objectif est d'extraire des immatriculations "colonne a" seulement et seulement si en "colonne y" il y "attention" sur la même ligne que l'immatriculation.

j'espère que mon charabia est compréhensible .

labougie

ferrand
 Posté le 27/06/2017 à 13:52 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

Bonjour,

Mon objectif est d'extraire des immatriculations "colonne a" seulement et seulement si en "colonne y" il y "attention" sur la même ligne que l'immatriculation.

C'est bien ce que j'avais compris. As-tu essayé la formule que j'ai fournie... ?

Cordialement.

Debrief
 Posté le 28/06/2017 à 07:21 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Astucien

(je ne devrais pas avoir à recopier vers le bas + de 15 lignes). c'est cela ma problèmatique

Salut à toutesetatous

A mon avis, si tu ne recopies vers la bas que 15 lignes (sous-entendu de formules ?), tu n'auras que 15 résultats au maximum, il n'y a pas de miracle. Sinon faut passer par une Macron

Cordialement,
D.

Gaby-Jo
 Posté le 28/06/2017 à 08:58 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petit astucien

Bonjour Labougie,

J'ai traité un problème semblable de la façon suivante :

1) une formule en E1 qui indique les données à remonter :

=NB.SI($Listing.$D$2:$D$9999;"anniversaire")

et une liste déroulante en J2 qui contient :

SI($Listing.$D$2:$Listing.$D$9999="anniversaire";$Listing.$A$2:$Listing.$A$9999)

Voir le fichier exemple joint.

http://www.cjoint.com/c/GFCg1iQmsP2

Tu peux je pense adapter facilement ma modeste contribution à ton cas.

Bonne journée, à te lire.

Gaby



Modifié par Gaby-Jo le 28/06/2017 09:01
Labougie
 Posté le 28/06/2017 à 10:16 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Groupe Sécurité

Ferrand ,

Oui, j'ai bien essayé ta formule, et elle ne fonctionnait pas hier. (suis parfois un peu boulet ).

Ce matin, au premier jet elle roule parfaitement bien .

Dans la condition de recherche, il y a ici le mot "Attention", si je reproduis la formule mais que la condition de recherche est "1" ou un ensemble de mot concaténés, je n'obtiens aucun résultat.

La formule contenant la concaténation est :

  • SI(DATEDIF(E3;$A$2;"d")>300;"1er Entretien "&DATEDIF(E3;$A$2;"d")&" j, /!\ max 300 j";

J'ai tenté "*" en début et fin. mais rien ne ressort.

=SIERREUR(INDEX(A:A;PETITE.VALEUR(SI(Y:Y="* entretien *";LIGNE(Y:Y);"");LIGNE(1:1)));"")

As tu une idée?

Labougie

Labougie
 Posté le 28/06/2017 à 10:18 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Groupe Sécurité

Debrief ,

Oui, je comprends parfaitement ta remarque, je suis repartie sur l'idée des catégories "onglet véhicules"

Cette idée est franchement parfaite .

Labougie



Modifié par Labougie le 28/06/2017 10:40
Labougie
 Posté le 28/06/2017 à 10:42 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Groupe Sécurité

Gaby-jo,

Je ne peux pas accéder à ton fichier.

La formule produite par Ferrand répond très bien à ma demande.

Regarde comment elle est construite. Elle est redoutable .

labougie

Publicité
Debrief
 Posté le 28/06/2017 à 10:44 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Astucien

Ah oui, j'y suis, j'ai encore la V4.1 de ce gros Excel avec les Stats et les graphiques. Il a dû évoluer pas mal car honnêtement je ne comprends pas trop ce que tu veux faire mais tu es dans de bonnes main avec Ferrand :)

Labougie
 Posté le 28/06/2017 à 11:04 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Groupe Sécurité

Debrief a écrit :

Ah oui, j'y suis, j'ai encore la V4.1 de ce gros Excel avec les Stats et les graphiques. Il a dû évoluer pas mal car honnêtement je ne comprends pas trop ce que tu veux faire mais tu es dans de bonnes main avec Ferrand :)

Tu as extrait les catégories dans l'onglet "véhicules". Et en partant de cette idée, j'ai créer un onglet entretien avec un report global dans une page "Main board".

Du coup, au lieu d'avoir un nombre m'indiquant la quantité de véhicules en entretien, je souhaite obtenir l'immatriculation (gain de temps) ceci evite de fouiller dans l'onglet concerné .

La formule de Ferrand fonctionne à merveille, mais reste à solutionner le différent avec les mots à rechercher quand ils sont concaténer.

J'ai bien tenter des jokers, mais rien n'y fait.

Version 3.7 actuellement avec des boutons pour sélectionner des pages plus rapidement, car avec une multitude d'onglet cela devenait compliquer y compris pour moi. .

Labougie

Debrief
 Posté le 28/06/2017 à 11:20 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Astucien

Tu veux dire que les cellules où tu cherches un mot ("entretien" ?) ne contiennent pas que ce mot mais d'autres ("ce véhicule est en entretien chez Gégé") ?

Ou c'est autre chose ?

Labougie
 Posté le 28/06/2017 à 12:06 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Groupe Sécurité

Effectivement le texte est different pour chaque véhicules car il comprend un décompte en fonction de la date d'arrivée

SI(DATEDIF(E3;$A$2;"d")>300;"1er Entretien "&DATEDIF(E3;$A$2;"d")&" j, /!\ max 300 j"; (ce n'est qu'une partie de la formule)

  • 1er entretien 345 j, /!\ max 300 j

Le nombre 345 est par conséquent différent et change touts les jours.

Je sais qu'avec un nb.si l'on peut traquer de cette façon

  • =NB.SI('Suivi Entretien'!M:M;"*entretien*")

j'ai tenté les joker comme dis précédemment mais hélas, rien ne passe, ou alors je m'y prend mal.

Aussi, tant que j'y pense, dans la formule de Ferrand

  • =SIERREUR(INDEX(A:A;PETITE.VALEUR(SI(Y:Y="* entretien *";LIGNE(Y:Y);"");LIGNE(1:1)));"")

LIGNE(1:1) 1:1 correspondent à ligne et colonne c'est bien cela?

Labougie

Debrief
 Posté le 28/06/2017 à 12:38 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Astucien

Je laisse Ferrand, notre maître à tous, tenter de résoudre ce problème.

En dernier ressort, j'y jetterai un œil et s'il n'y a pas de solution en formule, reste la possibilité d'un petite macro appelée à l'activation de la feuille.

Labougie
 Posté le 28/06/2017 à 12:46 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Groupe Sécurité

ferrand a écrit :

Bonsoir,

En D1 : =SIERREUR(INDEX(A:A;PETITE.VALEUR(SI(Y:Y="attention";LIGNE(Y:Y);"");LIGNE(1:1)));"")

Formule matricielle (à valider par Ctrl+Maj+Entrée). Et tirer vers le bas...

Cordialement.

Ferrand,

1/ décalage d'un ligne si plusieurs lignes trouvées

Le premier résultat est juste, ensuite le scd est décalé d'une ligne.

Voici comment j'ai adapté ta formule.

  • =SIERREUR(INDEX('Suivi Entretien'!A1:A300;PETITE.VALEUR(SI('Suivi Entretien'!Y1:Y300="attention vidange";LIGNE('Suivi Entretien'!Y1:Y300);"");LIGNE('Suivi Entretien'!1:1)));"")

Plage de référence 'Suivi Entretien'!A1:A300 car si a:a j'obtiens une erreur excel

j'ai donc appliqué le même raisonnement sur le suite des tailles.

La premier immatriculation qui tombe dans la formule est juste, cela dit, quand une seconde est trouvée, l'immatriculation est décalée d'une ligne vers le bas.

En ligne 1 j'ai l'entête des colonne

En ligne 2 des sous.totaux

En ligne 3 commence donc mon tableaux.

2/ Comment palier la recherche sur "attention vidange"

J'ai un critère de recherche avec des noms variables donc une base est commune à toutes les lignes "entretien"

Merci à toi.

Labougie

PS

Debrief,

Merci pour ton suivi.

Debrief
 Posté le 28/06/2017 à 14:45 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Astucien

Je ne sais rien de ta feuille "Suivi Entretien" mais en faisant référence à la feuille "Véhicules" et la formule pour la liste des catégories, dans la partie de formule:
SIERREUR(INDEX(INDIRECT("$B$3:$B$" & NBVAL($A:$A));EQUIV(1;INDEX((1-NB.SI($J$2:J2;INDIRECT("$B$3:$B$" & NBVAL($A:$A))))*(NB.SI($J$2;INDIRECT("$C$3:$C$" & NBVAL($A:$A))));0;0);0)); "")

cela revient à remplacer
NB.SI($J$2;INDIRECT("$C$3:$C$" & NBVAL($A:$A)))
par
ESTNUM(CHERCHE($J$2;INDIRECT("$C$3:$C$" & NBVAL($A:$A))))

$J$2 étant la valeur à tester ("entretien") qui peut être mise en string direct "entretien" dans le CHERCHE mais pas dans le NB.SI qui doit faire référence à une cellule.

A tout hasard je joins un petit Excel conservé l'occasion d'une question similaire (de moi-même ?) où j'avais récolté les solutions des contributeurs -> http://www.cjoint.com/c/GFCmh0uqM1K
En colonne H, j'ai ajouté la liste sans doublon avec condition <colonne B contient "X"> au lieu <colonne B = "X">

D.



Modifié par Debrief le 28/06/2017 14:51
Debrief
 Posté le 28/06/2017 à 16:50 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Astucien

Remarque complémentaire:

Si tu n'as pas à créer une liste sans doublon (et à priori ce n'est pas nécessaire dans ce cas ?) mieux vaut utiliser la formule de Ferrand adaptée comme suit:
=SIERREUR(INDEX(A:A;PETITE.VALEUR(SI(Y:Y="attention";LIGNE(Y:Y);"");LIGNE(1:1)));"") -> =SIERREUR(INDEX(A:A;PETITE.VALEUR(SI(ESTNUM(CHERCHE("attention";Y:Y));LIGNE(Y:Y);"");LIGNE(1:1)));"")

Bien que je ne la comprenne pas vraiment (pas analysée) cela fonctionne. LIGNE(1:1) doit s'appliquer même si tu saisis ta formule en ligne 3 (en supposant que les titres n'interfèrent pas).
Sinon tu peux exclure les 2 lignes de titres avec =SIERREUR(INDEX(A$3:A$65000;PETITE.VALEUR(SI(ESTNUM(CHERCHE("attention";Y$3:Y$65000));LIGNE(Y$3:Y$65000);"");LIGNE(1:1)) - 2);"")

P.S.
Il faut bien évaluer l'impact sur le temps de saisie car la ré-évaluation, même sur un petit tableau, prend un certain temps.



Modifié par Debrief le 28/06/2017 17:01
Publicité
ferrand
 Posté le 28/06/2017 à 19:34 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

Ouf ! Beaucoup de choses à lire depuis mon dernier passage !

LIGNE(1:1) [dans la première formule qu'on étendra vers le bas] est destiné à renvoyer 1. En référence relative, recopié sur les lignes suivantes, on aura successivement 2, 3... On a donc tous les rangs de PETITE.VALEUR à partir de 1... (et SIERREUR interrompt l'affichage quand plus de valeur à afficher).

Remplacer les colonnes entières par une plage plus réduites dans les formules, est une bonne chose. Certaines fonctions rament un peu avec les lignes entières...

Cordialement.

Debrief
 Posté le 28/06/2017 à 20:54 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Astucien

Absolument, et dans cette perspective, pour limiter la hauteur de colonne à ce qui est présent, en admettant que les valeurs de la colonne A sont déterminantes pour cette hauteur, tu peux remplacer

Comme on l'avait fait dans le l'Excel initial:
$A$3:A$65000 par INDIRECT("$A$3:$A$" & NBVAL($A$2:$A$65000) + 2)
$Y$3:Y$65000 par INDIRECT("$Y$3:$Y$" & NBVAL($A$2:$A$65000) + 2)

ou encore mieux pour l'adaptabilité de la formule en cas d'insertion / suppression de colonnes / lignes:(*)
$A$3:A$65000 par DECALER($A$3:$A$65000;0;0;NBVAL($A$3:$A$65000))
$Y$3:Y$65000 par DECALER($Y$3:$Y$65000;0;0;NBVAL($A$3:$A$65000))

A condition évidemment qu'il n'y ait pas de "trou" (cellule vide) dans la liste des valeurs de la colonne A, sinon NBVAL rendra un nombre inférieur au nombre de lignes à prendre en compte.

Cordialement,
D.

(*) En effet, car en cas d'insertion / suppression de colonnes / lignes, les parties de ranges entre cotes de l'INDIRECT ne seront pas automatiquement ajustées par Excel qui n'y voit que des strings, il faudra le faire manuellement, alors que la mention explicite des ranges dans le DECALER permettra l'adaptation éventuelle automatique par Excel.



Modifié par Debrief le 29/06/2017 09:21
Labougie
 Posté le 30/06/2017 à 13:28 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Groupe Sécurité

Ferrand, Debrief,

Merci tous les 2 pour votre aide.

J'ai utilisé cette formule de Debrief

=SIERREUR(INDEX(A$3:A$65000;PETITE.VALEUR(SI(ESTNUM(CHERCHE("attention";Y$3:Y$65000));LIGNE(Y$3:Y$65000);"");LIGNE(1:1)) - 2);"")

Qui donne entière satisfaction .

Je garde également les autres afin de faire dans le temps un upgrade en cas d'insertion de colonne.

-

Debrief, ton classeur mérite une étude approfondie et beaucoup d'attention afin que je puisse intégrer toutes les possibilités.

Excel permet vraiment de faire plein de trucs.

Voici à quoi ressemble désormais la page de garde


Labougie



Modifié par Labougie le 30/06/2017 13:37
Debrief
 Posté le 01/07/2017 à 06:35 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Astucien

Ah, oui ! C'est devenu un belle et grande application. Bien joué Labougie !

Cordialement,
D.

Page : [1] 
Page 1 sur 1

Vous devez être connecté pour poster des messages. Cliquez ici pour vous identifier.

Vous n'avez pas de compte ? Créez-en un gratuitement !


Sujets relatifs
créer liste déroulante longue sous word
Pas compris pour créer un liste de contact outlook 2013
Créer une liste ou un index _ Word 2010
créer une liste avec site interdit sous windows 7
Access resultat sur liste deroulante
code résultat sur liste deroulante
Comment créer une liste aléatoire sur excel ?
Outlook créer un contact avec une liste de contact
créer une liste de choix avec excel 2007
macro creer par enregistrement Resultat bizarre
Plus de sujets relatifs à Créer une liste dynamique en fonction d''un résultat
 > Tous les forums > Forum Bureautique