Réunir les données de plusieurs colonnes par calcul Excel

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


Sujets que vous pourriez aussi aimer :


Réunir les données de plusieurs colonnes

L'objectif de cette formation est de démontrer la technique permettant de réunir les données de plusieurs colonnes dans une seule et même rangée. Le but est évident. Il consiste à réorganiser l'information correctement pour mieux pouvoir la présenter et la manipuler.



Dans l'exemple illustré par la capture, nous travaillons sur une liste de personnes, identifiées par leurs noms et prénoms. Ces personnes sont énumérées sur un certain nombre de lignes et sur un certain nombre de colonnes. Le tableau situé sur la droite n'est composé que de deux rangées. Il regroupe et consolide toutes ces informations dans l'ordre. A chaque fin de colonne, c'est la suivante qui est ajoutée à la file. Et pour réaliser cette prouesse, nous allons bâtir une formule unique.

Source et présentation
Pour aboutir cette solution, nous suggérons de récupérer une source présentant les données ainsi fractionnées.

• Télécharger le classeur regrouper-donnees-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é,

Un tableau situé entre les colonnes B et G énumère des personnes avec noms et prénoms, à raison d'une colonne sur deux. La hauteur de ce tableau est considérée comme variable. De nouvelles personnes peuvent être insérées à la suite à tout moment. La formule que nous devons bâtir doit s'en accommoder pour reconstruire une liste linéaire des noms et prénoms dans le tableau situé entre les colonnes I et J.

Consolider les colonnes
Le calcul à bâtir doit exploiter la fonction Excel Decaler. Elle doit considérer le premier nom comme point de départ de la plage. Elle doit récupérer tous ceux qui suivent jusqu'à la fin de la colonne. Mais lorsque cette dernière est atteinte, elle doit naturellement porter son analyse deux colonnes plus loin, pour réunir les noms suivants et ainsi de suite. Pour cela, au fil de sa réplication sur les lignes du tableau consolidé, elle doit opérer des décalages en ligne et en colonne, fonctions de la hauteur du tableau.

• Sélectionner le premier nom à consolider, soit la cellule I6,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire la fonction d'ajustement suivie d'une parenthèse, soit : Decaler(,
• Cliquer sur la case du premier nom, soit la cellule B6,
• Enfoncer la touche F4 du clavier pour la figer, ce qui donne : $B$6,

Ce premier nom est le point de départ. C'est par rapport à cette donnée de référence que nous allons devoir observer les décalages ajustés en ligne et en colonne. Cette cellule ne doit donc pas bouger.

• Taper un point-virgule (;) pour passer dans l'argument du décalage en ligne,
• Inscrire la fonction Mod suivie d'une parenthèse, soit : Mod(,

Cette fonction permet de calculer le reste d'une division. Cette division doit consister à ramener la ligne en cours d'étude à l'échelle du nombre de valeurs contenues dans la colonne.

• Inscrire la fonction Ligne sans argument, soit : Ligne(),

De cette manière, cette fonction va renseigner sur l'indice de ligne en cours d'analyse, au fil de la réplication sur les lignes du dessous.

• Retrancher six unités à ce résultat, soit : -6,

En effet, cinq lignes sont placées au-dessus des informations à traiter. Pour récupérer tout d'abord le premier nom, le premier décalage doit être nul (5+1 soit 6-6=0).

• Taper un point-virgule (;) pour passer dans l'argument du diviseur de la fonction Mod,
• Inscrire la fonction comptant les cellules non vides, suivie d'une parenthèse, soit : NbVal(,
• Désigner la colonne B en tapant ses coordonnées, soit : B:B,
• Enfoncer la touche F4 du clavier pour la figer, ce qui donne : $B:$B,
• Fermer la parenthèse de la fonction NbVal,
• Puis, retrancher trois unités à ce décompte, soit : -3,
• Fermer alors la parenthèse de la fonction Mod,

Au-dessus du tableau, trois cellules de titre sont en effet renseignées. Elles ne doivent pas être incluses dans le décompte. Il ne s'agit pas de noms à considérer. De cette manière, tant que la valeur du diviseur n'est pas atteinte, soit le nombre de noms dans la colonne, le reste est incrémenté d'une unité. Ainsi, au fil de la réplication de la formule, nous prélèverons bien le nom suivant jusqu'à la fin variable de la colonne. Désormais, nous devons opérer le décalage à l'horizontale, précisément lorsque la fin de la colonne est détectée, pour poursuivre la consolidation des noms à partir du premier pour la rangée située deux colonnes plus loin.

• Taper un point-virgule (;) pour passer dans l'argument du décalage en colonne,
• Inscrire la fonction pour la partie entière, suivie d'une parenthèse, soit : Ent(,
• Ouvrir une nouvelle parenthèse pour la factorisation du calcul à suivre,
• Inscrire la fonction Ligne sans argument, soit : Ligne(),
• Comme toute à l'heure, lui retrancher six unités, soit : -6,
• Fermer la parenthèse de la factorisation,
• Puis, inscrire le symbole slash (/) pour annoncer la division à suivre,
• Ouvrir une nouvelle parenthèse,
• Inscrire la fonction pour compter les cellules non vides suivie d'une parenthèse, soit : NbVal(,
• Saisir les coordonnées de la colonne B comme précédemment, soit : B:B,
• Figer cette plage en enfonçant la touche F4 du clavier, ce qui donne : $B:$B,
• Fermer la parenthèse de la fonction NbVal,
• Comme précédemment, lui retrancher trois unités, soit : -3,
• Fermer la parenthèse de ce calcul,
• Fermer la parenthèse de la fonction Ent,
• Taper le symbole de l'étoile (*) pour annoncer la multiplication à venir,
• Saisir le chiffre 2,

Grâce à ce calcul, lorsque la ligne dépasse la valeur du diviseur, la fonction Ent conserve la partie entière de l'unité pour le décalage. En la multipliant par deux, nous repoussons l'étude deux colonnes plus loin, soit dans la colonne suivante pour les noms.

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

Comme vous le savez, cette astuce Excel permet de conserver active la cellule du résultat. De cette manière, nous allons pouvoir l'exploiter dans l'enchaînement.

• Cliquer et glisser la poignée de la cellule jusqu'en ligne 64 par exemple,

De cette manière, nous prévoyons quelques lignes supplémentaires en cas d'ajout de personnes à la suite du tableau.



Comme vous pouvez le voir, les informations sont parfaitement réorganisées sur une seule et même colonne. Et lorsque la fin de la rangée des noms est atteinte, la formule permet effectivement de poursuivre l'énumération à partir de la colonne suivante. De plus, si vous ajoutez de nouvelles personnes à la suite du tableau, vous constatez qu'elles sont automatiquement intégrées dans l'énumération reconstruite. Et pour cela, notre formule retarde le saut de colonne. La syntaxe complète du calcul que nous avons construit est la suivante :

=DECALER($B$6; MOD(LIGNE()-6; NBVAL($B:$B)-3); ENT((LIGNE()-6)/(NBVAL($B:$B)-3))*2)

Maintenant, pour réorganiser les prénoms en regard des noms, il suffit de répliquer la même syntaxe en adaptant le premier argument de la fonction Decaler. Il s'agit cette fois de débuter l'étude à partir du premier prénom.

=SI(I6<>0; DECALER($C$6; MOD(LIGNE()-6; NBVAL($B:$B)-3); ENT((LIGNE()-6)/(NBVAL($B:$B)-3))*2); '')

Dans cette syntaxe, nous avons néanmoins intégré une gestion conditionnelle. Elle permet de stopper l'énumération sur le dernier nom retranscrit.

De plus, il peut paraître judicieux de construire une règle de mise en forme conditionnelle sur le tableau recomposé : =EQUIV($I6;$B$6:$G$6;0). Celle-ci permet de surligner chaque personne issue de la première ligne de chaque colonne consolidée.

Sachez enfin que le cheminement inverse est tout à fait possible. Il s'agit de décomposer les informations d'une colonne dans plusieurs rangées.

=DECALER($I$6; LIGNE()+10*(ENT(COLONNE()/2)-6)-6;)



Dans cet exemple, nous recomposons des listes de 10 noms par rangée, à partir des cellules L6, N6 et P6. Nous retranchons des unités pour corriger les décalages en ligne et colonne, selon les mêmes explications que celles que nous avons fournies précédemment.