[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

PS : Ne manquez aucune nouvelle astuce sur Excel, abonnez-vous à notre newsletter gratuite !

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

PS : Ne manquez aucune nouvelle astuce sur Excel, abonnez-vous à notre newsletter gratuite !

Format des nouvelles lignes dans un tableau

Bonjour,

Voici une petite exploration dans le monde merveilleux des tableaux créés par "Mettre sous forme de tableau".

En général, lorsque l'on ajoute une ligne dans un tableau, la mise en forme de la ligne précédente suit automatiquement, mais pas toujours.
N'ayant pas trouvé la documentation appropriée (si vous l'avez, merci de l'indiquer dans les commentaires), voici le résultat de quelques expériences :
1/ si vous sélectionnez la colonne au complet et que vous ajoutez la mise en forme, celle-ci est conservée lors de l'ajout de nouvelles lignes au tableau.
2/ Si vous ne sélectionnez qu'une partie de la colonne, ou si vous faites la mise en forme en plusieurs fois, la mise en forme ne sera pas conservée lors de l'ajout de nouvelles lignes.

Voici l'explication en vidéo :


PS : Ne manquez aucune nouvelle astuce sur Excel, abonnez-vous à notre newsletter gratuite !

Mettre sous forme de tableau – 4 aspects à connaître

Avant de commencer la liste, clarifions au préalable que j'utilise les tableaux extrêmement souvent et que je les adore. Pour créer des listes dynamiques, pour assurer que le tableau croisé dynamique est bien connecté à toutes les données, ou encore pour conserver les validations et formules, c'est un outil fantastique.

Donc, voici les 4 dangers des tableaux sous Excel :

1/ Copier un groupe de feuilles vers un autre classeur

Si dans votre groupe de feuilles, vous avez des tableaux, il sera alors impossible de copier les feuilles sur un autre classeur. Vous aurez le message suivant:

Si le nombre de feuilles n'est pas trop important, on peut copier toutes les feuilles sans tableaux. Ensuite, on copie une à une les feuilles ayant un tableau. En général, j'essaie d'éviter au maximum ce genre de manipulations, les liens entre feuilles et les noms sont parfois assez sioux à gérer.

Une solution possible en VBA (Anglais):
https://blogs.office.com/2009/08/31/copying-worksheets-with-a-list-or-table/

2/ Ajouter une vue personnalisée (onglet affichage) ne va pas fonctionner si vous avez un tableau :

3/ La case "Mon tableau comporte des en-têtes n'a pas été cochée"

Ce qui nous donne le problème suivant :

Si vous avez fait cette erreur, la solution est très simple, il suffit d'annuler :
CTRL + Z

La solution extrême s'il est trop tard pour annuler, consiste à convertir le tableau en plage, puis supprimer la ligne de titre ajoutée et finalement recréer le tableau.

4/ Etendre le tableau sur une feuille protégée

Lorsqu'on ouvre les propriétés de la cellule pour déverrouiller une plage, puis que l'on protège la feuille, le tableau ne s'étend plus automatiquement. Ainsi, la touche TAB n'ajoute plus de nouvelles lignes, et si l'on ajoute une nouvelle entrée en dessous du tableau, le tableau ne s'étend pas automatiquement. A priori, en dehors d'une solution en VBA, il semble ne pas y avoir d'autes possibilités...

Il y a encore un problème que je n'ai pas abordé et qui concerne les mises en formes conditionnelles, mais ça sera pour une prochaine fois 🙂

A bientôt.

Source et autres discussions à ce sujet:
http://www.xlerateur.com/vba-excel/2017/03/14/aide-memoire-les-options-dexcel-dont-on-ne-se-rappelle-jamais-5790/

http://blog.contextures.com/archives/2015/04/30/excel-table-doesnt-expand-for-new-data/

PS : Ne manquez aucune nouvelle astuce sur Excel, abonnez-vous à notre newsletter gratuite !