[2016 et +] Lister les lignes des cellules vides dans une seule cellule

Téléchargez le fichier ici
Une réponse possible pour 2016+, à valider avec CTRL+MAJ+Entrée :
=CONCAT(SI(Tableau1[Plage à tester]="";LIGNE(Tableau1[Plage à tester])&" ";""))
ou encore :
=JOINDRE.TEXTE("-";VRAI;SI(Tableau1[Plage à tester]="";LIGNE(Tableau1[Plage à tester]);""))


En cherchant un moyen de renvoyer toutes les cellules vides d'une plage, je suis tombé sur cette formule qui permet de renvoyer la première ligne :

A valider par CTRL+MAJ+Entrée
=MIN(SI(Tableau1[Plage à tester]="";LIGNE(Tableau1[Plage à tester]);""))
Et si on décompose :
=MIN(SI({VRAI;FAUX;VRAI;FAUX;VRAI;FAUX};{2;3;4;5;6;7});""))
D'où :
=MIN({2;"";4;"";6;""})
et donc :
2

De là, me dis-je, on devrait pouvoir arriver à concaténer l'ensemble de :
SI(Tableau1[Plage à tester]="";LIGNE(Tableau1[Plage à tester]))
Ce qui nous donnerait la solution.

Malheureusement, CONCATENER ne semble pas fonctionner comme formule matricielle, MAIS, la nouvelle fonction CONCAT ou JOINDRE.TEXT peut le faire 🙂

D'où
A valider avec CTRL+MAJ+Entrée
=CONCAT(SI(Tableau1[Plage à tester]="";LIGNE(Tableau1[Plage à tester])))
qui renvoit donc :
246

Et si on veut séparer les lignes avec un espace (CTRL+MAJ+Entrée) :
=CONCAT(SI(Tableau1[Plage à tester]="";LIGNE(Tableau1[Plage à tester])&" ";""))

Ou encore avec JOINDRE.TEXTE
=JOINDRE.TEXTE("-";VRAI;SI(Tableau1[Plage à tester]="";LIGNE(Tableau1[Plage à tester]);""))

Et si on veut récupérer l'adresse exacte (CTRL+MAJ+Entrée)
=CONCAT(SI(Tableau1[Plage à tester]="";ADRESSE(LIGNE(Tableau1[Plage à tester]);COLONNE(Tableau1[Plage à tester]))&" ";""))
qui renvoit :
$A$2 $A$4 $A$6

En espérant que cela vous ouvre pleins d'horizons !

Je n'ai pas trop cherché, mais si vous connaissez la solution pour les versions antérieures à 2016, n'hésitez-pas.

A bientôt

Gaetan

Clarifications sur les noms de plages et les noms de tables

Suite à la question d'un lecteur, voici une petite clarification sur l'utilisation des noms, et notamment la différence entre un nom créé sur une table et un nom créé sur une plage. En deux minutes et en vidéo :

A bientôt

Gaetan