Dans ce tutoriel, je vais vous montrer comment mettre plusieurs valeurs dans une seule cellule Excel, sans les transformer en un simple bloc de texte.
Pour cela, nous allons utiliser une fonctionnalité incroyable d’Excel qui va par exemple nous permettre de stocker plusieurs collaborateurs dans une cellule, puis filtrer notre tableau sur une personne précise, vérifier si un collaborateur appartient à une équipe ou encore compter et additionner plusieurs nombres contenus dans une seule cellule.
Jusqu’à présent, lorsque nous saisissions une liste dans une cellule, Excel se contentait de considérer l’ensemble comme un texte.
Avec les nouvelles listes introduites dans la version béta D’Excel, chaque élément séparé par une virgule devient une valeur totalement indépendante, même si elle reste dans la même cellule.
Téléchargement
Tutoriel Vidéo
1. Présentation
Pour illustrer ce tutoriel, nous allons pouvoir utiliser le tableau suivant dans lequel nous retrouvons cinq projets, les collaborateurs affectés à chacun d’eux et leurs dépenses mensuelles.
Dans la cellule B7, par exemple, nous voyons trois noms séparés par des points-virgules : Alexandre, Julie et Sophie.
Visuellement, nous pourrions déjà considérer qu’il s’agit d’une liste. Mais pour Excel, ce n’est absolument pas le cas. Pour le moment, tout le contenu de B7 correspond à une seule chaîne de caractères.
Nous pouvons facilement le vérifier en saisissant la formule suivante dans une cellule vide :
=NBVAL(B7)
Nous validons avec [Entrée] et obtenons 1.
Même si nous voyons trois noms, Excel ne voit donc qu’une seule valeur. Le point-virgule est simplement un caractère comme un autre à l’intérieur du texte.
Et c’est justement ce que la nouvelle fonctionnalité de listes vient changer.
Pour transformer le contenu de B7 en véritable liste, nous sélectionnons la cellule, puis nous nous rendons dans le menu « Insertion » pour cliquer sur le nouveau bouton « Liste ».
Attention toutefois : au moment où je réalise ce tutoriel, cette fonctionnalité n’est pas encore disponible dans toutes les versions d’Excel. Elle est proposée en préversion aux utilisateurs Microsoft 365 qui utilisent le canal Bêta du programme Microsoft 365 Insider.
Microsoft 365 Insider permet tout simplement de recevoir certaines nouveautés d’Office avant leur diffusion auprès de l’ensemble des utilisateurs.
Plusieurs canaux existent selon le niveau d’anticipation souhaité. Ici, nous parlons du canal Bêta, qui permet de tester les fonctionnalités les plus récentes, mais également les moins finalisées.
Si vous souhaitez en savoir plus sur le programme Insider, les différents canaux disponibles et la manière de passer votre version d’Excel sur le canal Bêta, n’hésitez pas à me le faire savoir dans les commentaires pour que je puisse vous préparer un tutoriel spécifique sur le sujet.
Sinon, il n’y a absolument rien à faire : il suffit d’attendre que Microsoft déploie progressivement cette fonctionnalité dans les versions classiques d’Excel 365.
Attention également, étant donné qu’il s’agit pour le moment d’une fonctionnalité en cours de développement, son fonctionnement peut encore évoluer. Le nom des commandes, les raccourcis clavier, les fonctions disponibles ou certaines limitations que nous allons voir dans ce tutoriel pourront donc être légèrement différents lorsque la version définitive sera déployée.
D’ailleurs, nous pouvons déjà constater une petite incohérence : l’infobulle du bouton « Liste » indique le raccourci [Ctrl]+[J], mais sur la version française d’Excel, celui-ci ne fonctionne pas et applique à la place un format de date à la cellule.
Pour le moment, nous passerons donc directement par le bouton « Liste ».
Revenons maintenant à notre cellule B7.
Lorsque nous cliquons sur « Liste », Excel utilise le point-virgule pour identifier les différents éléments et transforme notre texte en véritable liste.
Une petite icône apparaît alors dans la cellule pour nous indiquer que celle-ci contient désormais une liste. Lorsque nous cliquons dessus, nous retrouvons séparément Alexandre, Julie et Sophie.
Et c’est là toute la différence.
Ces trois noms restent physiquement stockés dans une seule cellule, B7, mais Excel peut maintenant les traiter individuellement, presque comme s’ils étaient répartis dans une plage de plusieurs cellules.
Nous pouvons par exemple sélectionner un élément de la liste, en ajouter un nouveau, en supprimer un ou modifier sa position sans avoir à manipuler manuellement notre chaîne de caractères et ses séparateurs.
Faisons maintenant la même chose avec B8, qui contient Julie et Thomas.
Nous sélectionnons B8 puis utilisons [Ctrl]+[J]. Excel crée immédiatement une nouvelle liste contenant cette fois deux éléments.
Nous pourrions évidemment poursuivre avec B9, B10 et B11, mais avant cela, revenons sur notre formule située en D7 :
=NBVAL(B7)
Cette fois, le résultat change.
Excel ne considère plus B7 comme une seule chaîne de caractères : NBVAL peut identifier les différents éléments contenus dans la liste.
Nous pouvons donc utiliser des fonctions Excel classiques directement sur le contenu de la cellule.
D’ailleurs, notre fonction NBVAL() retourne maintenant la valeur « 3 » :
Et cela ne fonctionne pas uniquement avec du texte.
Regardons maintenant C7. Cette cellule contient les valeurs 12, 15, 14 et 18 séparées par des points-virgules.
Pour le moment, nous avons une nouvelle fois un simple texte.
Nous sélectionnons C7 et cliquons à nouveau sur le bouton « Liste » :
Excel transforme les quatre valeurs en une liste, mais il reconnaît cette fois qu’il s’agit de nombres.
Nous pouvons donc immédiatement effectuer des calculs dessus.
Dans une cellule vide, saisissons :
=SOMME(C7)
Nous validons avec [Entrée] et obtenons 59, ce qui correspond bien à 12 + 15 + 14 + 18.
C’est probablement le point le plus important à comprendre avant de poursuivre : nous n’avons pas quatre nombres écrits sous forme de texte dans C7. Nous avons réellement quatre valeurs numériques contenues dans une seule cellule.
Excel peut donc les compter, les additionner, calculer leur moyenne ou encore rechercher une valeur précise sans que nous ayons besoin de commencer par les répartir dans quatre cellules différentes.
Et nous allons justement voir maintenant que cette distinction devient encore plus intéressante lorsque nous voulons filtrer notre tableau en fonction des personnes présentes dans chaque équipe.
2. Filtrer les valeurs contenues dans une cellule
Voyons maintenant ce qui rend les listes beaucoup plus intéressantes qu’un simple texte séparé par des points-virgules.
Nous sélectionnons une cellule de notre tableau et utilisons [Ctrl]+[L] pour le transformer en tableau structuré.
Nous ouvrons le filtre de la colonne « Équipe ».
Excel est capable de reconnaître individuellement Alexandre, Emma, Julie, Sophie et Thomas.
Nous cochons uniquement « Julie ».
Excel conserve les projets « Refonte site » et « Application mobile », même si Julie partage ces cellules avec d’autres personnes.
Avec du texte classique, nous aurions dû utiliser un filtre « Contient ».
Le problème, c’est que ce type de recherche travaille sur les caractères et non sur les éléments. Avec des valeurs comme « Ann » et « Anna », une recherche contenant « Ann » pourrait donc retourner les deux.
Une liste évite cette ambiguïté.
Autre détail intéressant : si nous cochons « Alexandre » et « Emma » dans le même filtre, Excel applique une logique « OU ». Nous récupérons les lignes contenant Alexandre ou Emma.
En revanche, lorsque nous combinons ce filtre avec un filtre placé sur une autre colonne, Excel applique toujours une logique « ET ».
Ce fonctionnement devient très pratique avec des compétences, des catégories, plusieurs responsables d’un dossier ou plusieurs zones géographiques associées au même enregistrement.
Nous conservons surtout une seule ligne par projet.
Sans cette fonctionnalité, nous aurions parfois été tentés de créer trois colonnes « Collaborateur 1 », « Collaborateur 2 » et « Collaborateur 3 », ce qui devient rapidement difficile à exploiter.
La liste correspond mieux à la réalité de notre donnée : un projet possède une équipe, et cette équipe peut contenir un nombre variable de personnes.
3. Rechercher avec HAS, HASANY et HASALL
Cette fonctionnalité n’est pas arrivée seule, elle est en effet accompagnée de trois nouvelles fonctions Excel.
Étant donné qu’il s’agit encore d’une version béta, ces fonctions restent pour le moment encore en anglais.
Découvrons la première de ces fonctions, la fonction HAS().
En D6, saisissons « Alexandre ? ».
Dans D7 :
=HAS(B7;"Alexandre")
HAS recherche une valeur à l’intérieur d’un tableau ou d’une liste.
Le premier argument, B7, correspond donc à notre équipe. Le second, « Alexandre », correspond à la valeur recherchée.
Nous obtenons VRAI sur tous les projets auxquels Alexandre participe.
Passons maintenant à la fonction HASANY() en saisissant en E6 « Alexandre ou Emma ? ».
En E7 :
=HASANY(B7;{"Alexandre";"Emma"})
HASANY vérifie si au moins une des valeurs recherchées est présente.
Nous appliquons donc une logique « OU ».
Comme vous pouvez le remarquerez nous avons placé les deux valeurs recherchées entre accolades.
Ces accolades permettent de créer directement ce que nous appelons une constante matricielle, c’est-à-dire un petit tableau de valeurs directement à l’intérieur de notre formule.
Ici, nous ne transmettons donc pas à HASANY une seule valeur à rechercher, mais un tableau contenant deux valeurs : « Alexandre » et « Emma ».
Le point-virgule permet de séparer les différents éléments de ce tableau. Nous obtenons donc en quelque sorte un petit tableau vertical composé de deux lignes.
D’ailleurs, j’ai consacré une vidéo complète aux accolades dans Excel il y a seulement quelques semaines, sans me douter qu’elles allaient nous être à nouveau utiles aussi rapidement !
Si vous ne connaissez pas encore leur fonctionnement, je vous invite à regarder cette vidéo, car nous pouvons utiliser les accolades dans de nombreuses formules pour créer directement des tableaux de valeurs sans avoir à les saisir dans des cellules.
Pour appliquer une logique « ET » et véfifier si plusieurs valeurs sont bien présentes, nous utilisons « HASALL ».
En F6, saisissons « Alexandre et Thomas ? », puis en F7 :
=HASALL(B7;{"Alexandre";"Thomas"})
Cette fois, les deux noms doivent être présents pour obtenir VRAI.
HAS, HASANY et HASALL couvrent donc trois besoins très courants : rechercher une valeur, rechercher au moins une valeur parmi plusieurs, ou vérifier que toutes les valeurs demandées sont présentes.
Dans un fichier réel, nous pouvons bien entendu placer nos critères dans des cellules afin que l’utilisateur puisse modifier sa recherche sans toucher à la formule.
4. Extraire nos listes avec APLATIR
Terminons avec l’opération inverse.
Nos collaborateurs sont regroupés dans cinq cellules. Nous voulons maintenant obtenir la liste de toutes les personnes présentes dans notre tableau.
En H6, saisissons « Collaborateurs uniques ».
Puis en H7 :
=APLATIR(B7)
APLATIR aplatit les listes contenues dans B7, c’est-à-dire qu’Excel déverse leurs éléments dans la feuille.
Pour obtenir la liste de tous les noms, nous pouvons utiliser toute la colonne :
=APLATIR(Tableau1[Équipe])
Mais dans ce cas-là, nous aurons évidemment des doublons.
Modifions donc la formule en l’intercalant comment argument de la fonction UNIQUE() :
=UNIQUE(Tableau1[Équipe]))
UNIQUE supprime les répétitions.
Enfin, pour les trier par ordre alphabétique, nous pouvons encore utiliser la fonction TRIER() :
=TRIER(UNIQUE(Tableau1[Équipe])))
Nous obtenons automatiquement la liste alphabétique des collaborateurs.
Attention toutefois : contrairement aux valeurs contenues dans B7, le résultat affiché à partir de H7 est ici un tableau dynamique classique. Nous devons donc laisser suffisamment de cellules vides sous H7 pour permettre son déversement.
Et nous arrivons justement à la limite que je voulais atteindre dans cette première vidéo.
Nous avons vu comment une cellule pouvait contenir plusieurs valeurs. Mais Excel peut maintenant aller beaucoup plus loin : une seule cellule peut contenir un véritable tableau composé de plusieurs lignes et plusieurs colonnes.
Cela nous permettra ensuite d’effectuer des recherches dans ce tableau, extraire certaines colonnes ou calculer directement sur son contenu.
Je vais m’arrêter ici pour ne pas mélanger les deux notions, mais si vous voulez que je réalise une deuxième vidéo pour vous montrer comment stocker et exploiter un tableau entier dans une seule cellule Excel, dites-le-moi dans les commentaires de la vidéo Youtube.
