Regrouper les colonnes séparées, par formule Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Regrouper les colonnes séparées

C'est une nouvelle technique matricielle qui va nous apprendre à réunir les colonnes à partir d'un tableau source présentant des trous verticaux. Et c'est une extraction avec regroupement que nous proposons de réaliser, afin de ne pas altérer la présentation du tableau d'origine.



Sur l'exemple illustré par la capture, ce sont effectivement seulement les cellules renseignées qui sont réunies dans un second tableau situé sur la droite du premier. Et pour atteindre ce résultat, nous allons construire une formule d'extraction très simple, exploitant des matrices virtuelles.

Classeur Excel à télécharger
Pour la démonstration de la technique, nous suggérons d'appuyer l'étude sur un classeur Excel offrant ce tableau avec des colonnes vides à éliminer.

• Télécharger le classeur reunir-colonnes.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous découvrons effectivement le tableau à trous accompagné d'un tableau de trois colonnes vides sur la droite. C'est lui qui attend l'unique formule matricielle à répliquer pour réunir les informations.

• En haut à gauche de la fenêtre Excel, déployer la zon Nom,

Comme vous pouvez le voir, le tableau à trous est reconnu sous l'intitulé tab. Nous exploiterons ce nom pour simplifier la construction de la formule matricielle.

Initialiser l'extraction
L'une des fonctions dédiées pour réaliser des extractions puissantes est la fonction Excel Index. Nous devons premièrement la faire agir sur le tableau à trous.

• Sélectionner toutes les cellules du tableau d'extraction, soit la plage I5:K16,
• Taper le symbole égal (=) pour initier la syntaxe de la formule matricielle,
• Inscrire la fonction d'extraction suivie d'une parenthèse, soit : Index(,
• Désigner le tableau à trous par son nom, soit : tab,
• Puis, taper un point-virgule (;) pour passer dans l'argument de l'indice de ligne,

Désigner toutes les lignes du tableau
Ce n'est pas une seule information que nous souhaitons extraire, mais toutes celles qui sont renseignées. Elles sont présentes sur toutes les lignes du tableau. Ce n'est donc pas un simple indice de ligne que nous devons fournir mais l'ensemble des indices. Et pour cela dans ce raisonnement matriciel, nous proposons de construire une matrice virtuelle représentant toutes les lignes. Et comme vous le savez, une matrice virtuelle doit être interprétée pour être exploitée.

• Inscrire la fonction donnant la ligne d'une cellule, suivie d'une parenthèse, soit : ligne(,

Nous l'avons dit, nous ne souhaitons pas travailler sur une seule ligne mais sur toutes.

• Inscrire la fonction d'interprétation suivie d'une parenthèse, soit : Indirect(,
• Entre guillemets, taper le chiffre 1 suivi du symbole deux points, soit : "1:",

De cette manière, nous donnons le point de départ du tableau, soit à partir de la première ligne. Le symbole deux points (:) annonce la borne inférieure à suivre. Elle doit englober toutes les cellules restantes. La fonction renseignant sur le nombre de lignes d'un tableau se nomme Lignes. Elle est donc au pluriel par rapport à son homologue que nous venons d'employer.

• Taper le symbole de concaténation (&) pour l'assemblage dynamique à faire,
• Inscrire la fonction comptant les lignes, suivie d'une parenthèse, soit : Lignes(,
• Désigner le tableau par son nom, soit : tab,
• Fermer la parenthèse de la fonction Lignes,
• Fermer la parenthèse de la fonction Indirect,
• Puis, fermer la parenthèse de la fonction Ligne,

De fait, nous sommes de retour dans les bornes de la fonction d'extraction Index.

• Taper un point-virgule (;) pour passer dans l'argument de l'indice de colonne,

Pointer sur toutes les colonnes à réunir
Pour consolider toutes les colonnes renseignées à extraire, nous devons cette fois construire une matrice virtuelle fixe et horizontale. Comme vous le savez, dans le cas d'une matrice verticale, ce sont des points-virgules qui doivent être utilisés pour séparer les indices de lignes. Dans le cas d'une matrice horizontale, ce sont des points qui doivent être employés pour séparer les indices de colonnes.

• Entre accolades, construire la matrice horizontale suivante : {1.3.6},

Nous désignons ainsi la première, la troisième et la sixième colonne du tableau source. Il s'agit effectivement des trois colonnes renseignées tandis que toutes les autres sont vides.

• Fermer la parenthèse de la fonction Index,
• Puis, valider la formule matricielle par le raccourci clavier CTRL + MAJ + Entrée,

Rappelons-le, depuis la version 2019, ce raccourci n'est plus nécessaire. Excel comprend naturellement qu'il s'agit d'une formule matricielle qu'il répand automatiquement en tant que telle.

Quoiqu'il en soit, grâce à cette matrice horizontale donnée dans le troisième argument de la fonction Index, vous remarquez que seules les colonnes renseignées sont effectivement importées. Bref, nous avons réussi à les réunir en ignorant les colonnes vides, grâce à une syntaxe relativement simple :

{=INDEX(tab;LIGNE(INDIRECT("1:" & LIGNES(tab))); {1.3.6})}