XL 2016 Index equiv sur plusieurs feuilles

La Bourgeoise · 28 Octobre 2020

Bonjour à tous,

Je rencontre un petit problème avec mon dossier Excel.

Dans un classeur j’ai créé plusieurs feuilles (31 pour chaque jour)
Elles comportent toutes le même tableau avec une partie divers pour des fournisseurs exceptionnels (qui seront rentrés de manière ponctuelle)

Dans ce classeur j’ai également une feuille Récapitulative avec le même tableau que pour les jours
Je souhaiterais que quand j’entre le nom d’un fournisseur celui-ci ce reporte dans mon tableau récap.

Pour ne pas qu’ils se cumulent sur une meme case, j’ai mis une formule pour qu’une fois entrée ils se classent par ordre alphabétique et soit donc associé à un numéro (1,2,3..):

=NB.SI('J1'!B96:B101;"<="&B96)+NB.SI('J2'!B96:B101;"<="&B96)+NB.SI('J3'!B96:B101;"<="&B96)+NB.SI('J4'!B96:B101;"<="&B96)+NB.SI('J5'!B96:B101;"<="&B96)+NB.SI('J6'!B96:B101;"<="&B96)+NB.SI('J7'!B96:B101;"<="&B96)+NB.SI('J8'!B96:B101;"<="&B96)+NB.SI('J9'!B96:B101;"<="&B96)+NB.SI('J10'!B96:B101;"<="&B96)+NB.SI('J11'!B96:B101;"<="&B96)+NB.SI('J12'!B96:B101;"<="&B96)+NB.SI('J13'!B96:B101;"<="&B96)+NB.SI('J14'!B96:B101;"<="&B96)+NB.SI('J15'!B96:B101;"<="&B96)+NB.SI('J16'!B96:B101;"<="&B96)+NB.SI('J17'!B96:B101;"<="&B96)+NB.SI('J18'!B96:B101;"<="&B96)+NB.SI('J19'!B96:B101;"<="&B96)+NB.SI('J20'!B96:B101;"<="&B96)+NB.SI('J21'!B96:B101;"<="&B96)+NB.SI('J22'!B96:B101;"<="&B96)+NB.SI('J23'!B96:B101;"<="&B96)+NB.SI('J24'!B96:B101;"<="&B96)+NB.SI('J25'!B96:B101;"<="&B96)+NB.SI('J26'!B96:B101;"<="&B96)+NB.SI('J27'!B96:B101;"<="&B96)+NB.SI('J28'!B96:B101;"<="&B96)+NB.SI('J29'!B96:B101;"<="&B96)+NB.SI('J30'!B96:B101;"<="&B96)+NB.SI('J31'!B96:B101;"<="&B96)

Dans mon tableau récapitulatif j’ai entré cette classification (soit 1,2,3...)
Pour que le nom étant associé à la valeur « 1 » par exemple, se reporte dans la case juste à côté, où j’ai entrée la formule :

=INDEX('J1'!$B$96:$F$101;EQUIV($C$85;'J1'!$C$96:$C$101;0);1)

Mais celle si ne fonctionne que pour la 1ère feuille.

Quelle formule puis-je utiliser pour récupérer
les données si elles trouvent sur une autres feuilles (sachant qui y en a 31)?

Désoler si les explications ne sont pas assez précises ...
merci d’avance pour vos réponses

chris · 28 Octobre 2020

Bonjour

Joins un fichier

La récap peut sans doute être aisément faite par PowerQuery intégré à Excel

La Bourgeoise · 28 Octobre 2020

Bonjour,

Voici le fichier
La partie qui pose un problème se trouve en bas (partie violette du tableau)
au niveau des lignes 84 à 99 (du récap)
et correspondent au ligne 96 à 101 (dans les jours)

Je ne connais pas du tout PowerQuery

En tout cas merci

chris · 28 Octobre 2020

RE

Tes formules pourraient être simplifiées en formules 3D =SOMME('J1:J31'!E77)

La conception est un peu bancale
Tes formules en B96 et suivantes sont assez bizarres...

Il parait aussi bizarre que la recap récupère Descriptif, U, Prix U sur une ligne Fournisseur unique
Sauf si le fournisseur ne vend qu'un unique produit, il faut autant de lignes que de Fournisseur+produit +prix unitaire ou bien une synthèse préalable

Une proposition PowerQuery qui nécessite de mettre la partie B76:G101 de chaque onglet sous forme de tableau structuré nommé T_Jn comme T_J1, T_J2

Je l'ai fait sur 5 onglets, je te laisse faire le reste

Actualiser par Données, Actualiser tout

419 · 28 Octobre 2020

moi
je ne vois pas le power query, il faut qqchose de particulier pour le voir.

@LaBourgeoise,
si j'ai compris, tu permets de rentrer sporadiquement des nouveaux fournisseurs sur tes onglets jour, fournisseurs qui se retrouvent sur l'onglet récap.
Mais tu définis depuis l'onglet récap, l'ordre dans lesquels ils apparaissent. En gros tu fais de la référence circulaire entre ta récap et tes onglets.
si c'est tes onglets qui peuvent générer la création de fournisseurs, chaque nouvel entrée génère un +1 via une formule et dans ta récap tu cherches dans chaque feuille si le num+1 est présent pour classer tes nouveaux fournisseurs.
mais ils seront dans l'ordre d'enregistrement et vl'a la formule SI + index equiv imbriquée à se palucher. quitte à rajouter une formule si pour classer dans l'ordre alphabétique.
le plus simple est d'apprendre le VBA et le gérer via macro.

chris · 28 Octobre 2020

419 à dit:
moi
je ne vois pas le power query, il faut qqchose de particulier pour le voir.

Il faut Excel 2010 ou + mais comme tu n'as pas indiqué ta version dans ton profil...
(a priori 2013 d'après un de tes posts) donc en add on (intégré totalement à partir de 2016)

419 à dit:
le plus simple est d'apprendre le VBA et le gérer via macro.

Non c'est bien plus simple en PowerQuery qu'en VBA
(J'enseigne les deux donc je parle en connaissance de cause)

La Bourgeoise · 29 Octobre 2020

Bonjour,

Merci pour vos réponses,
Je vous avoue que je débute sur Excel alors les notion de PowerQuery et de VBA, je n’ai pas vraiment la notion de se que ça peut être.

@chris
Je vais essayer de simplifier mes formules comme indiqué et essayer de reproduire ton tableau sur le reste des pages.

Pour chaque fournisseur il y a une case global car je me suis trompé ce n’est pas « Prix U » mais plutôt « Prix H.T » c’est donc un prix global a la journée que je n’ai pas besoin de plus détailler.

@419
C’est exactement ce que je souhaite faire.
J’aimerais pouvoir entrée de nouveau fournisseurs de manière ponctuelle, sur mes onglets « jours »
Puis qu’ils se reportent sur mon recap en se classant pas ordre alphabétique.

J’ai trouvé une formule qui fonctionne mais celle ci ne cherche que sur un onglet unique et je souhaiterais « l’étendre » à tout les autres
Or je ne sais vraiment comment je peux faire

De plus je ne comprends pas vraiment se que vous voulez dire par :

mais ils seront dans l'ordre d'enregistrement et vl'a la formule SI + index equiv imbriquée à se palucher. quitte à rajouter une formule si pour classer dans l'ordre alphabétique.

chris · 29 Octobre 2020

Bonjour

La Bourgeoise à dit:
Merci pour vos réponses,
Je vous avoue que je débute sur Excel alors les notion de PowerQuery et de VBA, je n’ai pas vraiment la notion de se que ça peut être.

Je vais essayer de simplifier mes formules comme indiqué et essayer de reproduire ton tableau sur le reste des pages.

Il suffit, dans chaque onglet de J6 à J31 de sélectionner la plage indiquée, B76:G101, puis Accueil, Mettre sous forme de tableau : confirmer qu'il y a des en-tête de colonnes
Sans déselectionner la plage, tu vois un nouvel onglet plus haut sur le ruban : Outils de Tableau croisés dynamique
Complètement à gauche, tu dois voir Tableau1 ou autre chiffre
Tu tapes par dessus T_J suivi du nombre correspondant au jour et tu valides

Une fois cela fat tu n'as plus qu'à actualiser comme indiqué dans mon post

On verra ensuite s'il faut ajouter une colonne à la requête...

XL 2016 Index equiv sur plusieurs feuilles

La Bourgeoise

XLDnaute Nouveau

chris

XLDnaute Barbatruc

La Bourgeoise

XLDnaute Nouveau

Pièces jointes

chris

XLDnaute Barbatruc

Pièces jointes

419

XLDnaute Nouveau

chris

XLDnaute Barbatruc

La Bourgeoise

XLDnaute Nouveau

chris

XLDnaute Barbatruc

Discussions similaires