XL 2019 Lister les résultats de Nb.si.ens

[lotislogan]

Bonjour,

Je ne sais pas si le sujet a déjà été traité sur le forum, mais je ne l'ai pas trouvé en tous cas.

Du coup je demande. J'ai un problème que je pensais simple, mais qui dépasse mes connaissances, de toutes évidences. Dans un classeur, à partir d'un tableau "OriginalTitle" j'ai un onglet "Akas" avec des formules de recherches selon différents critères, qui peuvent être partiels, depuis l'onglet "Base de données". Comme il peut y avoir plusieurs occurences selon les résultats, j'ai donc une formule Nb.si.ens pour me dire combien il y en a. Maintenant, ce que je souhaite faire, et c'est sur quoi je coince, c'est lister les résultats sur autre onglet "Occurences multiples". L'idée que quand un titre me renvoie à plusieurs résultats, je puisse avoir la liste des différents films, afin de déterminer quel est celui que je cherche.

Je joins un fichier allégé (le classeur d'origine pèse 90Mo et l'onglet "base de donnée" comporte pus de 960 000 lignes^^), afin que vous puissiez visualiser ce que j'essaie d'expliquer. D'ailleurs si vous avez des suggestions de formules pour simplifier l'ensemble, je suis preneur. Je ne fais que bidouiller avec ce que je trouve sur le net...

Bien entendu je n'ai aucune connaissance du VBA. Je ne sais même pas ouvrir une macro, à part me servir de la mini caméra, ce qui m'est rarement utile^^

D'avance, Je remercie tous les contributeurs du forum qui voudront se pencher sur mon cas.

 

[djidji59430]

Bonjour à tous,

Une proposition.
J'espere que tu as un bon processeur ! 1000000 de matricielles, ça va ramer.
C'a serait beaucoup mieux en VBA (pas par moi !)

 

[lotislogan]

Bonjour à tous,

Une proposition.
J'espere que tu as un bon processeur ! 1000000 de matricielles, ça va ramer.
C'a serait beaucoup mieux en VBA (pas par moi !)

Super djidji59430, merci énormément. J'avais bien essayé avec Petite.Valeur et Ligne, mais il me manquait Cherche et LigneS ^^
Oui, j'ai une belle bécane à la maison. J'ai testé avec 250 lignes (multi critères) d'un coup et il mouline une minute 6 secondes chrono en main. Ce qui est très raisonnable comparé au 2h30 que ça me prenait pour faire les mêmes recherches sur internet.

J'imagine bien qu'en VBA ce serait plus simple. Il faudrait que je j'envisage de m'y mettre un jour.

En tout cas, un grand merci. Très bonnes fêtes de fin d'année.

 

[lotislogan]

Bonjour à tous,

Une proposition.
J'espere que tu as un bon processeur ! 1000000 de matricielles, ça va ramer.
C'a serait beaucoup mieux en VBA (pas par moi !)

Ah je me suis un peu emballé. Je suis désolé, j'ai du mal exprimer précisémment ce que je cherche a obtenir. En fait, dans la formule que vous écrit, j'obtiens toutes les occurences correspondantes au seul critère contenu dans la colonne A2 de la feuille "OriginalTitle". Ce que je cherche à obtenir c'est la même liste, mais concernant le nombre d'occurences selon différents critères.

Par exemple, pour le titre "les dents de la mer", il y a 8 résultats. Mais si je rajoute le critère de l'année dans l'onglet "OriginalTitle", cellule B, par exemple 1978, je n'ai plus que 4 résultats. Dans la liste de l'onglet "Occurences multiples", je souhaite alors qu'il n'y ait plus que ces occurences qui ressortent. Et ainsi de suite selon les renseignements contenus dans les cellules C2 et D2 si nécéssaires. Et en pluis selon des critères partiels.

Je sais obtenir le nombre d'ocurences (cellule G2 de l'onglet Akas), mais ce qu'il me faudrait, si cela est même possible, c'est d'en obtenir la liste qui équivaut ce résultat. Je pense que c'est une formule proche de celle que vous m'avez proposé, mais je ne maîtrise pas du tout les fonctions Ligne et Petite.Valeur.

Je vous joint le fichier avec l'exemple des dents de la mer - 1978 pour tenter d'être plus clair.

Désolé pour la confusion.

 

[djidji59430]

Une approche

 

[lotislogan]

Bonjour djidji59430, cette nouvelle version s’approche un peu plus de ce que je cherche à obtenir. Mais je vais continuer à t'embêter, car il y a quelques soucis que je n’arrive pas régler. D’abord, selon ce que j’essaie de modifier dans la formule, excel me renvoie un message « Vous ne pouvez pas modifier une partie de matrice » que je ne connaissais pas.

C’est vrai que j’ai également remarqué que lorsque je pouvais apporter une modification, cela modifiait également les autres cellules. Je ne sais pas si c’est souhaitable.

Egalement, si je rentre seulement « les dents de la mer » comme critère, je n’ai que 4 résultats affichés (les quatre différents), alors qu’il y a 8 résultats, même quand j’incrémente les cellules pour avoir la formule sur 8 lignes. Comme si c’était figé.

Aussi, si en ligne 2 de l’onglet « OriginalTitle », il y a des champs renseignés, la formule ne prend pas en compte le critère de date. Si une année figure dans la cellule B2, lorsque je sélectionne le champ de la cellule A3 dans la cellule C2 de l’onglet « Occurrences multiples », rien n’apparait.

Dans la cellule B2, si je renseigne une année renvoyant à une seule occurrence (1975 ou 1983 pour « Les dents de la mer », par exemple), le tableau de l’onglet « Occurrences multiples » va afficher le même titre dans les 4 lignes. Si, pour le même film, le critère de l’année renvoie à deux résultats (1987), alors les deux premières lignes sont juste et les deux suivantes affiche l’erreur #N/A. Par contre, si le critère de l’année renvoie 4 résultats (1987), j’ai les bonnes propositions.

J’ai essayé de comprendre, mais je ne maîtrise pas les fonctions employées et je ne connaissais pas la possibilité des faire formules « liées ».

L’objectif de ce classeur est de pouvoir effectuer une recherche en fonction des éléments à ma disposition, pour plusieurs centaines de titre en même temps. J’ai testé avec 250 films différents et ça prend qu’une minute, là où ça me prend près de trois heures avec mes petites mains musclées. J’ai en effet plusieurs dizaines de classeurs répertoriant des dizaines de milliers de titres de films utilisés dans d’autres pays. Par exemple « les dents la mer » a eu des titres différents en Allemagne, en Italie ou en Espagne etc.

Le problème que je rencontre est qu’il y a souvent des résultats multiples (un critère unique comme « Dracula » dans la cellule du titre peut me renvoyer jusqu’à 587 occurrences) et plutôt que de chercher par moi-même, j’avais pensé, par le biais du menu déroulant de sélectionner les résultats multiples et obtenir directement toutes les données liées afin de sélectionner le bon film. C’est pourquoi, il faut que les résultats qu’il affichera soient en fonctions des critères renseignés dans l’onglet « Originaltitle ». Et ce, pour n’importe film contenu dans cet onglet. En effet, si je mentionne « Copolla » en plus de Dracula, je n’ai plus que 10 résultats et qui renvoient tous au même film.

Je te renvoie mon fichier avec l’exemple d’un autre film avec un date en B2.

Dans tous les cas, je te remercie de nouveau pour ton aide.

 

[djidji59430]

re
On continue en MP

Crdlmt

 

[Hasco]

Bonjour,

Voici une proposition de recherche multi-critères par powerquery. J'y ai passé l'après-midi mais suis assez content du résultat.

Il est quand même dommage que vous fassiez la suite en MP.

Cordialement

 

[djidji59430]

Hasco
Tu as raison, d'autant plus qu'a mon avis, c'est mieux par PQ
J'y avais pensé", mais je ne sais pas trier avec des valeurs "extérieures" et je vais de ce pas me lancer dans l'examen des requêtes.
Par contre, si on veut modifier, il faut certaine connaissance de la chose ! Quand on voit que tu y as passé l'après midi, ça me laisse songeur ......

 

[Hasco]

Quand on voit que tu y as passé l'après midi, ça me laisse songeur ......

Re,

C'est à dire que j'ai fait plusieurs versions.
Celle-ci me semble jouable mais perfectible.
Si notre ami, a des fichiers externes à explorer, effectivement, power query serait une réponse.

Bonne soirée

 

[lotislogan]

Bonsoir et merci à vous deux.

J'avoue que je viens de découvrir le nom de Power Query à l'instant. J'ai googuelé et ça à l'air effectivement plein de potentiel. Mais en l'état je ne suis pas sûr de savoir l'utiliser. Surtout si par la suite je dois apporter des modifications. L'idée étant aussi de comprendre afin que je puisse être autonome sur mon classeur.

@djidji59430, j'ai téléchargé le fichier et je vais regarder ça de près. Si je ne reviens pas vers vous ces deux prochains jours, ne m'en voulez pas, je risque d'être un peu pris ou fatigué

Je vous souhaite une bonne soirée et par avance un bon réveillon demain soir.

 

[Hasco]

Bonjour,

Voici une version quelque peu amélioré.
Il peut y avoir plusieurs lignes de critères par champ.
Le critère 'liste contient certains' a été rajouté.
Les noms des champs peuvent être choisis.
Amélioration de la requête 'Conditions'.
Si aucun critères ne correspond, toute la base est retournée (à voir si c'est pertinent)

Si vous voyez quelques corrections et/ou amélioration...dites le moi

Cordialement

 

[lotislogan]

Bonjour Hasco,

Je vous prie de m'excuser pour ce retour tardif. Tout d'abord, je vous souhaite une bonne année et vous remercie encore pour votre aide.

J'ai regardé le fichier et c’est vachement bien. Mais du coup, ne connaissant pas du tout, avant de me lancer dedans, j’aurais quelques questions si vous permettez.

Déjà, on se trouve PQ dans excel ? J’ai bien vu Power Pivot et Power Map dans les compléments, mais pas de Power Query.
Si j’ai compris l’idée générale, ça fonctionne un peu comme une combinaison de filtres avancés et de TCD. Mais, alors est-ce que cela prendrait une base de données de près d’un million de lignes ?

Si oui, comment j’intègre ladite base ?

Et s’il y a plusieurs dizaines de résultats, le tableau vert les affichera toutes ou je dois incrémenter ce tableau ?

Concernant la casse, y-aurait-il un moyen de contourner le fait que PQ en tienne compte ?

Enfin, je n’arrive pas à me servir des modes « Liste contient certains » et « Liste contient tous ». Je les sépare par une virgule et un espace, mais il ne me renvoie aucun résultat dans le tableau vert. Même en modifiant la casse.

Cordialement.

 

[Hasco]

Bonjour,

Lorsque vous sélectionnez une cellule du tableau vert de résultats, un nouvel onglet 'Requête' s'ouvre dans le ruban excel. Dans cet onglet de ruban, à gauche vous avez un bouton : 'Modifier', cliquer sur ce bouton et l'éditeur Power Query, faisant partie de votre version excel, s'ouvrira sur la requête.

Sinon l'onglet 'Données' bouton 'Requêtes et connexion', vous ouvrira un panneau à droite de votre fenêtre avec la liste des requêtes, Click-droit sur le nom 'Datas' puis 'Modifier' (voir image plus bas)

Dans l'éditeur de power query, à droite vous avez un panneau avec les noms des étapes de la requête, cliquez sur l'étape nommée 'Source', c'est là (regarder la barre de formule de PQ en haut du panneau central) qu'il faut intégrer votre source de données. Par contre, si elle n'est pas dans le classeur, il faudra écrire différemment.

Pour ce qui concerne la casse, on peut demander que les comparaison se fasse en ignorant la casse.

Le tableau vert s'ajustera au nombre de lignes retournées par la requête.

Power query est fait pour interroger les grandes bases de données.

Pour Liste contient certains par exemple, voyez le fichier joint ci-dessous :

La demande :
Stars | Liste contient certains | Lorraine Gary, Yvonne Furneaux, Murray Hamilton, Louis Jourdan

Retourne bien les films qui dont la liste des stars contien un ou des noms de la liste de valeurs.
vous verrez des films qui ont Lorraine Gary et/ou Murray Hamilton dans leur liste de stars et d'autres qui ont
Lorraine Gary et/ou Yvonne Furneaux dans leur liste de stars.

Normalement demain je ne serai pas disponible.

[Edition 19h02] changer le fichier pour une version ignorant la casse.

Cordialement

 

[lotislogan]

Bonjour,

Lorsque vous sélectionnez une cellule du tableau vert de résultats, un nouvel onglet 'Requête' s'ouvre dans le ruban excel. Dans cet onglet de ruban, à gauche vous avez un bouton : 'Modifier', cliquer sur ce bouton et l'éditeur Power Query, faisant partie de votre version excel, s'ouvrira sur la requête.

Sinon l'onglet 'Données' bouton 'Requêtes et connexion', vous ouvrira un panneau à droite de votre fenêtre avec la liste des requêtes, Click-droit sur le nom 'Datas' puis 'Modifier' (voir image plus bas)

Dans l'éditeur de power query, à droite vous avez un panneau avec les noms des étapes de la requête, cliquez sur l'étape nommée 'Source', c'est là (regarder la barre de formule de PQ en haut du panneau central) qu'il faut intégrer votre source de données. Par contre, si elle n'est pas dans le classeur, il faudra écrire différemment.

Pour ce qui concerne la casse, on peut demander que les comparaison se fasse en ignorant la casse.

Le tableau vert s'ajustera au nombre de lignes retournées par la requête.

Power query est fait pour interroger les grandes bases de données.

Pour Liste contient certains par exemple, voyez le fichier joint ci-dessous :

La demande :
Stars | Liste contient certains | Lorraine Gary, Yvonne Furneaux, Murray Hamilton, Louis Jourdan

Retourne bien les films qui dont la liste des stars contien un ou des noms de la liste de valeurs.
vous verrez des films qui ont Lorraine Gary et/ou Murray Hamilton dans leur liste de stars et d'autres qui ont
Lorraine Gary et/ou Yvonne Furneaux dans leur liste de stars.

Normalement demain je ne serai pas disponible.

[Edition 19h02] changer le fichier pour une version ignorant la casse.

Cordialement
Regarde la pièce jointe 1126400

Bonsoir Hasco,

C'est super. Franchement, je vais me lancer à la découverte de Power Query. Je ne connaissais pas, mais sa souplesse et ses possiblités vont beaucoup m'aider, je pense.

Encore merci à vous deux pour votre aide.

Cordialement