Microsoft 365 Lister les onglets selon condition

MACx

XLDnaute Occasionnel
Bonjour les XLDnautes !

Voici une demande qui j'espère dont j'espère vous saurez m'apporter une aide précieuse.

Je possède un fichier Excel avec X onglets.
Chaque onglet est un contrat, et en cellule I2 il y a une date de fin de contrat (sauf quand ce dernier est toujours en cours)

Je voudrais tout simplement lister en deux colonnes (sur un autre onglet nommé "Listes")
- les contrats en cours (I2 = vide)
- les contrats clôturés (I2 = nonVide)

Ces deux listes devront se mettre à jour à chaque ouverture du fichier (ou par le biais d'un bouton "Actualiser"

Merci d'avance pour votre aide !

MACx
 

Hasco

XLDnaute Barbatruc
Repose en paix
Re,

Comme je n'ai pas envie que ça dure des jours, je reprends.
Dans la dernière étape nous avons sélectionné les feuilles contrats sur leur nom.
Maintenant Power Query nous présente un tableau dont une colonne se nomme 'Data'.
Cette colonne présente à la droite de son nom, une icône avec deux flèches (gauche et droite ou ouest et est)
Cliquer sur cette icône, la liste des colonnes reconnues par PQ dans les items de la colonnes, se développe.
Comme nous savons que la date de fin de contrat (celle qui nous intéresse) est dans la deuxième colonne occupée reconnue par PQ, la sélectionner :
1656089032036.png

Décochez la case 'Utiliser le nom de la colonne d'origine comme préfixe' pour ne pas avoir de nom de colonne comme Data.Column2

Nous obtenons dans la barre de formule :
= Table.ExpandTableColumn(#"Lignes filtrées", "Data", {"Column2"}, {"Column2"})
Remplacer le deuxième "Column2" par "Statut". Cela évitera d'avoir à créer une étape ultérieure pour renommer la colonne. La Formule devient :
= Table.ExpandTableColumn(#"Lignes filtrées", "Data", {"Column2"}, {"Statut"})
Dans notre colonne nous avons des lignes avec "Date fin", filtrer ces lignes par l'interface (flèche à droite de la colonne 'Statut'), ce qui produira comme formule :
= Table.SelectRows(#"Data développé", each ([Statut] <> "Date Fin"))
Maintenant nous allons transformer les dates et null de la colonne "Statut" en "Clôturé" et "En cours"
Pour cela cliquer sur le Fx à gauche de la barre de formule :
1656089673526.png

Ce qui crée une étape nommée 'Personnalisée1' dont la formule dans la barre de formule est :
= #"Lignes filtrées1"
remplacer cette formule par :
= Table.TransformColumns(#"Lignes filtrées1", {{"Statut", each if _ = null then "En cours" else "Clôturé"}})
Table.TransformColumns est une fonction PQ qui permet d'appliquer une transformation à une ou plusieurs colonnes.
La première paires de crochets contient la liste des paires {nom colonne,fonction de transformation} entre crochets également.
{"Statut", each if _ = null then "En cours" else "Clôturé"}
each* est un élément permettant d'appliquer en boucle ce qui le suit sur les items de la colonne "Statut"
Chacun des items lorsqu'il est parcouru se retrouve grâce à each dans une variable implicite dont le nom est '_' (trait de soulignement ou underscore).

donc pour chaque item de la colonne Statut si sa valeur est nulle alors on a affaire à un contrat en cours sinon on a affaire à un contrat Clôturé.

*dans d'autres fonctions que "Table.TransformColumns" l'underscore contient l'enregistrement complet de la ligne parcourue

On a presque fini.
En double-cliquant sur l'étiquette de la colonne 'Name' on peut renommer cette colonne en 'Contrat'

Et pour finir on peut sélectionner les colonnes à retourner (Onglet 'Accueil' bouton 'Choisir les colonnes')
Cela peut se faire également en cliquant sur l'entête de la colonne 'Contrat' puis avec la touche maj enfoncée, sur l'entête de la colonne 'Etat' et enfin sur 'Supprimer les colonnes/Supprimer les autres colonnes').

Voilà c'est fini.

La difficulté pour vous dans la réalité sera l'étape de sélection de la bonne colonne de date de fin. Car je suppose que vos feuilles ne contiennent pas uniquement 4 cellules occupées.

Faites déjà ce qui précède sur le dernier fichier exemple donné, et pour le reste nous verrons plus tard.


La formule suivante fait la même chose mais n'est valide que pour les version 365 et + de excel :
=LET(
ToutesFeuilles;STXT(NomsFeuilles;TROUVE("]";NomsFeuilles)+1;9^9);
FeuillesContrats;FILTRE(ToutesFeuilles;EXACT(ToutesFeuilles;MAJUSCULE(ToutesFeuilles)));
TestsClôtures;N(N(INDIRECT(CONCATENER("'";FeuillesContrats;"'!i2")))>0);
Résultat;SI(ESTVIDE('Contrats'!$E$10);FeuillesContrats;FILTRE(FeuillesContrats;TestsClôtures= N('Contrats'!$E$10="Clôturé")));
Résultat)
Où 'NomsFeuilles' est un nom contenant la fonction xlmacro4 LIRE.CLASSEUR(1) (=TRANSPOSE(LIRE.CLASSEUR(1))) Etant une fonction xlmacro4 elle oblige à enregistrer le classeur avec l'extension .xlsm même s'il ne contient aucune macro vba.

Si vous voulez en voir les variables intermédiaires, remplacez le dernier 'Résultat' (juste avant la parenthèse finale) par le nom de la variable voulue (ToutesFeuilles, FeuillesContrats,TestsClôtures)

Le nom 'NomFeuilles' quant à lui renvoie :

[ListeContrats2022.06.23.xlsm]Contrats
[ListeContrats2022.06.23.xlsm]QERHY
[ListeContrats2022.06.23.xlsm]AEBYT
[ListeContrats2022.06.23.xlsm]NHSDTHN
[ListeContrats2022.06.23.xlsm]KLFJ-HGTH
[ListeContrats2022.06.23.xlsm]BGRECZ
[ListeContrats2022.06.23.xlsm]CGYJT
[ListeContrats2022.06.23.xlsm]KLJFS
[ListeContrats2022.06.23.xlsm]QAEFV
[ListeContrats2022.06.23.xlsm]DFRYZE
[ListeContrats2022.06.23.xlsm]Listes
[ListeContrats2022.06.23.xlsm]AddNew

Toutes les feuilles du classeurs

Bon week-end
 

Pièces jointes

  • ListeContrats2022.06.23.xlsm
    42.1 KB · Affichages: 3
Dernière édition:

Discussions similaires

Statistiques des forums

Discussions
311 725
Messages
2 081 944
Membres
101 849
dernier inscrit
florentMIG