XL 2016 INDEX(PETITE.VALEUR) depuis plusieurs feuilles

[Juu1033]

Bonjour la communauté ,

Tout nouveau dans le forum, je fais appel à votre expertise en vue de réaliser un INDEX(PETITE.VALEUR) depuis plusieurs feuilles.

Détails sur la structure :
Je possède 8 tableaux dynamiques possédant les mêmes colonnes, depuis lesquels j'aimerais matcher une liste de numéros d'items en fonction de 3 critères (Contents, Status, Date). Je souhaiterais que ces différents matchs soient incrémentés sur un même tableau dynamique SOMMAIRE. J'aurais donc dans cette liste en 1er la liste d'items du tableau1 (SH1_LIST), puis la liste d'items du tableau 2 (SH2_LIST) et ainsi de suite.

Détails sur la formule :
J'utilise une formule INDEX(PETITE.VALEUR) pour trouver ma liste d'items du plus petit au plus grand (=INDEX(SH1_LIST[Items '#];PETITE.VALEUR(SI(SH1_LIST[Contents]="OWNER";SI(SH1_LIST[Status]<>"APPROVED";SI(SH1_LIST[Date]<AUJOURDHUI();LIGNE(SH1_LIST[Contents])-LIGNE(INDEX(SH1_LIST[Contents];1))+1)));LIGNE(1:1)));"")).
Je décide donc d'imbriquer cette formule dans un SIERREUR(INDEX(PETITE.VALEUR sur SH1_LIST);SIERREUR(INDEX(PETITE.VALEUR sur SH2_LIST);....;"") et validé en matriciel pour être sûr que je puisse tirer ma formule vers le bas. Le but étant que le système soit dynamique, donc que si la date d'un item est à jour, cela se mette à jour dans le tableau SOMMAIRE (Voir qu'un item s'ajoute ou se supprime en fonction des critères définis).

C'est le caractère k (LIGNE(1:1)) de PETITE.VALEUR qui joue le rôle de compteur pour chaque Tableau Source. Cependant, écrit comme il l'est dans cette formule, je n'arrive qu'a récupérer les items du 1er tableau source et non des autres tableaux. Avez-vous une idée pour résoudre ce problème ?

Je vous joint un fichier Mock avec 3 tableaux sources définis.

Merci de votre temps.

 

[WTF]

Et pourquoi ne pas passer par PowerQuery ?
Exemple en PJ

 

[Juu1033]

Bonjour WTF,

merci de ta réponse. As-tu envoyé le bon fichier ? Je ne vois rien de nouveau à part le Sheet6 et Sheet7 vides.

PowerQuery, solution à envisager, même si je ne connais pas le fonctionnement. Je cherche une solution simple de display pour tout utilisateur. Dans le cas de PowerQuery doit-on refaire à chaque consultation du classeur, le travail de requête ?

Pas de solution en formule Excel les experts ?

Merci

 

[WTF]

En effet, ca n'était pas le bon fichier
Désolé

 

[CISCO]

Bonjour

Tu dois pouvoir trouver ton bonheur sur le site de J. Boisgontier, sur la partie 3D. Pas certain, mais bon...

@ plus

 

[CISCO]

Bonjour

Avec ce fichier, par ex, trouvé sur le site de J. Boisgontier, dans la partie nommée Equiv/Index 3D avec réponses multiples Recherche3D.

@ plus

 

[Juu1033]

Merci Cisco, pour ton aide.

Effectivement, la Recherche3D pourrait correspondre à mon besoin et surtout en utlisant le regroupement des feuilles. Néanmoins, en testant ton fichier joint, je me rend compte qu'il ne retourne qu'une seul réponse par feuille
ex : En ajoutant une nouvelle ligne avec le critère "aaa" en feuille 41, seul le 1er résultat est retourné. Je continue à plancher sur ces formules et revient vers vous si(/dès que) j'ai une solution

Bonne journée.

 

[CISCO]

Bonsoir

Et avec ça, toujours puisé dans le site de J. Boisgontier, est-ce que c'est mieux ?

@ plus

 

[chris]

Bonjour à tous

Tu sembles confondre Tableaux structurés et tableaux croisés dynamiques.

Une autre solution PowerQuery avec un tableau de choix, un peu comme un filtre avancé.

J'ai pris en plus dates<date du jour

Tu peux ajouter autant d'onglets que tu veux : il faut juste que les ableaux structurés aient un nom commençant par SH

Actualiser par Données. Actualiser Tout

 

[Juu1033]

Chris,

Merci pour ton support. Solution efficace effectivement, même si la MAJ doit se faire manuellement.
Pour ce qui sont des termes, je parle de tableaux structurés lorsque je fais appel à un tableau comportant un nom et des colonnes nommées. J'utilise donc des références structurées, c'est le cas dans mes exemples ci-dessus.
J'entends par tableau dynamique (et non tableau croisé dynamique), un tableau dont ma 1ère ligne de données comporte des formules de type RECHERCHEV ou INDEX(EQUIV ou PETITE.VALEUR). En tirant ma ligne vers le bas, j'applique ces formules aux cellules inférieures et génère une liste de résulats (depuis un seul tableau source). Voir mon exemple de Mercredi (Feuille SUMMARY). Si je supprime ou change la date dans mon tableau source, je n'ai pas de réactualisation manuelle à faire dans ce cas.

Avec les éléments dont je dispose grâce à vous, ce que je recherche (Retourner plusieurs occurrences de résultats pour chaque feuille sources, en utilisant les formules INDEX(EQUIV) ou INDEX(PETITE.VALEUR)) devrait exister (Hors VBA, je travaille sous Sharepoint).

En attentant de trouver ce que je cherche, PowerQuery est une excellente solution que je vais tester en environnement de travail. Deux questions à ce sujet :
- PowerQuery est il compatible avec un environnement Sharepoint ? Pas de problèmes lors de l'actualisation des données ?
- Propositions de Chris/Cisco : Avez vous une solution pour faire apparaître une colonne personnalisée comportant le nom de la feuille source pour chaque ligne de données ? J'ai commencer à chercher sur différents forums mais sans grand succès.

Merci de vos retours.

 

[chris]

Bonjour

A l'origine PowerQuery vient de SharePoint il me semble.

J'avais supprimé l'info feuille source car ton exemple ne la mentionnait pas

Je reposte avec la correction

On peut ajouter une ligne de code pour la MAJ se fasse quant on arrive sur l'onglet SUMMARY.

Si tu as beaucoup de données le temps de calcul de formules matricielles est supérieur à l'actualisation de la requête et en plus avec le tableau de choix tu peux intégrer des variantes...

 

[Juu1033]

J'avais supprimé l'info feuille source car ton exemple ne la mentionnait pas
Je reposte avec la correction

Effectivement, dès que tu as le temps de m'envoyer ton fichier avec cette MAJ, ce serait top. Je pourrais l'appliquer à mes classeurs.

Si tu as beaucoup de données le temps de calcul de formules matricielles est supérieur à l'actualisation de la requête et en plus avec le tableau de choix tu peux intégrer des variantes...

Mon vrai classeur, possède 8 tableaux sources donc effectivement Power Query devrait être plus rapide et fiable.

Merci de ton aide et bonne continuation.

 

[chris]

Oups

Oubli réparé

 

[Juu1033]

Salut Chris,

J'ai encore quelques questions sur ton fichier :
- J'ai bien compris la logique de la requête "TOUT" qui est la combinaison des 3 tableaux SH.
- A quoi servent les 3 requêtes que tu as créées ? Celle "TOUT" ne suffirait pas ?
- Comment arrives tu as cette 1ère étape dans ta requête STATUS ?

- Tu as fait un filtre sur les noms de tableaux commençant par les lettres "SH" dans ce cas, pour les ajouter a la requête "TOUT". Qu'en est-il si mes tableaux n'ont pas de lettres communes ? Dans mon classeur de travail, ces tableaux finiraient plutôt par "_LIST" par exemple.
- Ma demande est sûrement contraignante, mais pourrais tu prendre le temps de détailler toutes les étapes qui t'ont permis d'arriver au résultat final ?

Navré, pour apprendre, j'ai besoin de refaire la manip dans sa totalité.

Merci , Ju

 

[chris]

RE

Tout prend tous les tableaux structurés du classeur.

Il faut donc avoir un moyen de Filtrer afin de de pas intégrer le tableau Tout et le Tableau Choix.
Éventuellement différent de Choix et différent de Tout peut faire l'affaire si les autres ont des noms variables.

Tout et Status prennent l'intégralité du classeur via la ligne Source :
= Excel.CurrentWorkbook()
(On part d'une requête vide et on tape ceci dans la barre de formule ce qui crée l'étape Source)

Status sert à lister les status autres que ceux que tu a choisi d'éliminer en les extrayant de l'existant.

Si tous les choix sont fixes tu peux supprimer Choix et Status et simplifier Tout