Comment utiliser les fonctions TOCOL et TOROW dans Microsoft Excel

Le remodelage d'un tableau bidimensionnel de données en une seule colonne ou ligne dans Microsoft Excel rationalise la disposition de vos chiffres et permet un tri et un filtrage plus efficaces. Pour ces raisons, vous devriez savoir comment fonctionnent les fonctions TOCOL et TOROW d'Excel.

Les fonctions TOCOL et TOROW sont disponibles pour celles utilisant Excel 2024 ou version ultérieure, Excel pour Microsoft 365, Excel pour le Web et les applications Excel Mobile et Tablet.

La fonction TOCOL: transformant un tableau bidimensionnel en une seule colonne

Comme son nom l'indique, la fonction TOCOL d'Excel transforme un tableau de plusieurs colonnes et lignes en une seule colonne de données unidimensionnelle.

La syntaxe de cette fonction est la suivante:

=TOCOL(a,(b),(c))

où

• un (requis) est le tableau à retourner en colonne,
• b (facultatif) définit si le résultat doit ignorer certains types de valeurs, et
• c (Facultatif) Définit si le tableau doit être scanné par colonne ou ligne.

Si vous omettez l'argument bla formule revient tous Les valeurs du tableau source dans la colonne résultante. Cependant, 1 dit à Excel d'ignorer les blancs, 2 dit à Excel d'ignorer les erreurs, et 3 dit à Excel d'ignorer les blancs et les erreurs.

Omettre l'argument c scanne le tableau par ligne de gauche à droite, en commençant dans la rangée supérieure. D'un autre côté, la valeur booléenne VRAI Indique à Excel de scanner le tableau par colonne de haut en bas, à partir de la colonne la plus à gauche.

Imaginez que vous êtes le PDG d'une entreprise de vente au détail, et vous avez une feuille de calcul contenant des types de magasins dans la colonne A, les pays de la ligne 1 et le nom d'un manager dans chaque cellule qui se croise.

Votre objectif est de transformer le tableau des noms de gestionnaire dans la cellule B2 vers la cellule F17 en une seule colonne, que vous utiliserez comme point de départ d'un nouvel ensemble de données.

Examinons d'abord TOCOL dans sa forme la plus simple, en utilisant uniquement l'argument requis. Dans une cellule vierge, Type:

=TOCOL(

puis sélectionnez les cellules contenant les valeurs que vous souhaitez énumérer dans une seule colonne.

Ensuite, fermez les parenthèses à la fin de la formule et appuyez sur Entrée.

=TOCOL(B2:F17)

La colonne de noms 1D résultante apparaît comme un tableau dynamique qui se déverse de la cellule où vous avez tapé la formule.

En raison du résultat dynamique, il adoptera toute modification des données d'origine. Cependant, gardez à l'esprit que les tableaux dynamiques sont incompatibles avec les tables Excel. Cela signifie que si vous souhaitez formater la liste 1D en tant que table Excel, vous devrez d'abord sélectionner toutes les cellules du résultat, appuyez sur Ctrl + C pour les copier et appuyez sur Ctrl + Shift + V pour les coller sous forme de valeurs. Cependant, à ce stade, la liste résultante n'est plus liée aux données d'origine, donc elle ne reflétera aucune modification ultérieure apportée.

Parce que vous avez omis l'argument b (valeurs à ignorer), le résultat renvoie toutes les valeurs, y compris les blancs (illustrés sous forme de zéros) et les erreurs. Aussi, parce que vous avez omis l'argument c (Direction de balayage), le résultat répertorie les valeurs telles qu'elles sont apparues dans la source de gauche à droite sur la première ligne (tous les gestionnaires de supermarchés), puis la deuxième ligne (tous les gestionnaires de news), etc.

Toutes les valeurs répétées dans le tableau source seront également répétées dans le résultat. Voir la dernière section de ce guide pour voir comment retourner uniquement des valeurs uniques.

Bien que laisser des zéros et des erreurs dans le résultat puisse être utile dans certains contextes, comme si vous souhaitez analyser leur prévalence dans les données source – la plupart du temps, les données seront plus propres si vous forcez Excel à les cacher. Pour ce faire, utilisez la même formule, mais tapez 3 pour argument b:

=TOCOL(B2:F17,3)

et appuyez sur Entrée.

Enfin, plutôt que de numériser le tableau d'origine par ligne, disons que vous souhaitez le scanner par colonne. C'est-à-dire que vous voulez que le résultat répertorie les valeurs telles qu'elles sont apparues dans la source de haut en bas dans la première colonne (tous les managers américains), puis la deuxième colonne (tous les managers basés au Royaume-Uni), etc.

Pour ce faire, vous devez taper VRAI pour argument c:

=TOCOL(B2:F17,3,TRUE)

La fonction Torow: transformant un tableau bidimensionnel en une seule ligne

La fonction Torow de Microsoft Excel transforme un tableau de plusieurs colonnes et lignes en une seule ligne de données unidimensionnelle.

La syntaxe fonctionne de la même manière que la fonction TOCOL:

=TOROW(a,(b),(c))

où

• un (requis) est le tableau à retourner en ligne,
• b (facultatif) définit si le résultat doit ignorer certains types de valeurs, et
• c (Facultatif) Définit si le tableau doit être scanné par colonne ou ligne.

Omettre l'argument b rendements tous Les valeurs du tableau source, y compris les blancs et les erreurs, dans la ligne résultante. Cependant, 1 dit à Excel d'ignorer les blancs, 2 dit à Excel d'ignorer les erreurs, et 3 dit à Excel d'ignorer les blancs et les erreurs.

Si vous omettez l'argument facultatif cLa fonction scanne le tableau par ligne de gauche à droite, à partir de la première ligne. Si, au lieu de cela, vous voulez qu'il scanne le tableau par colonne de haut en bas, à partir de la première colonne, vous devez ajouter la valeur booléenne VRAI.

En utilisant le même ensemble de données que dans l'exemple précédent, cette fois, vous souhaitez répertorier tous les noms des managers en une seule ligne en tant qu'en-têtes de colonne, sous laquelle vous taperez les noms de leurs employés. Pour ce faire, dans une cellule vierge, type:

=TOROW(

et sélectionnez les cellules du tableau.

Ensuite, fermez les parenthèses et appuyez sur Entrée:

=TOROW(B2:F17)

Le résultat affiche tous les noms en une seule ligne, avec des blancs affichés sous forme de zéros et d'erreurs incluses.

Pour forcer la fonction à ignorer les blancs et les erreurs, tapez 3 pour argument b. Aussi, pour scanner les données d'origine par colonne plutôt que par ligne, tapez VRAI pour argument C:

=TOROW(B2:F17,3,TRUE)

Pour transformer le résultat des valeurs dynamiques en statiques, sélectionnez toutes les cellules, appuyez sur Ctrl + C pour les copier et appuyez sur Ctrl + Shift + V pour les coller sous forme de valeurs.

Fusion de plusieurs tableaux en une seule colonne ou ligne

Utilisé isolément, TOCOL et TOROW ne peuvent transformer qu'un seul tableau en colonne ou ligne de données. Cependant, lorsqu'ils sont utilisés aux côtés de Vstack et HSTACK, ils peuvent fusionner plusieurs tableaux dans une colonne ou une ligne de données.

Dans cette section, je vais expliquer comment fusionner plusieurs tableaux en une seule colonne en utilisant TOCOL. Cependant, vous pouvez appliquer les mêmes étapes avec Torow pour fusionner plusieurs tableaux dans une seule ligne.

Ici, vous avez deux ensembles de données – le premier contient le directeur de chaque magasin par pays européen, et le second contient les mêmes détails pour l'Asie. Votre objectif est de reproduire tous les noms des managers des deux tableaux dans une seule colonne.

Vstack empile plusieurs tableaux les uns sur les autres. Vous pouvez le voir en action dans la capture d'écran ci-dessous.

=VSTACK(B2:F17,B20:F35)

En conséquence, si vous nid Vstack à l'intérieur d'une formule TOCOL, les noms de tous les managers européens seront répertoriés par Row en premier, suivis par tous les managers asiatiques par Row.

=TOCOL(VSTACK(B2:F17,B20:F35),3)

D'un autre côté, HSTACK empile plusieurs tableaux les uns à côté des autres.

=HSTACK(B2:F17,B20:F35)

Donc, si vous nid hstack à l'intérieur de TOCOL, la colonne résultante répertorie d'abord tous les gestionnaires de supermarchés (Europe, puis Asie), alors tous les gestionnaires de news (Europe, puis en Asie), etc.

=TOCOL(HSTACK(B2:F17,B20:F35),3)

N'oubliez pas que vous pouvez également inclure le troisième argument des fonctions TOCOL et TOROW pour redéfinir davantage l'ordre du résultat.

Transformer un tableau bidimensionnel en une seule colonne de valeurs uniques et triées

Les fonctions TOCOL et TOROW d'Excel peuvent être imbriquées dans une multitude de fonctions d'organisation des données utiles. Deux des compagnons les plus utiles de Tocol et Torow sont tri et uniques.

Disons que vous êtes un directeur d'athlétisme et que vous avez une gamme Excel de médailles d'or dans divers événements au cours des cinq dernières années.

Votre objectif est de convertir tous les noms en une seule colonne. En utilisant juste TOCOL, vous tapez:

=TOCOL(B2:F17)

Cependant, certaines personnes apparaissent plus d'une fois dans la liste car elles ont remporté plusieurs médailles d'or. Ainsi, pour éviter la répétition, vous devez nicher toute la formule TOCOL à l'intérieur unique:

=UNIQUE(TOCOL(B2:F17))

Enfin, vous souhaitez trier ces valeurs uniques dans l'ordre alphabétique, qui est l'ordre par défaut adopté par la fonction de tri. Ainsi, vous pouvez envelopper l'ensemble de la formule unique-tocol en quelque sorte:

=SORT(UNIQUE(TOCOL(B2:F17)))

Pour trier le résultat dans l'ordre inverse, tapez -1 pour troisième Argument de la partie de la formule:

=SORT(UNIQUE(TOCOL(B2:F17)),,-1)

Les arguments par défaut des fonctions de tri et uniques fonctionnent mieux avec les tableaux basés sur des colonnes (plutôt que les tableaux basés sur des lignes). Pour utiliser ces deux fonctions aux côtés de Torow, vous devez taper VRAI pour deuxième argument de la partie unique de la formule, et VRAI pour quatrième Argument de la partie de la formule:

=SORT(UNIQUE(TOROW(B2:F17),TRUE),,,TRUE)

---

Les fonctions TOCOL et TOROW ne sont que deux façons de réorganiser les données dans Microsoft Excel. Par exemple, vous pouvez retourner les lignes et les colonnes à l'aide de la fonction Transsose, transformer de grands ensembles de données à l'aide de pivottables ou combiner des feuilles de calcul à l'aide de la requête de puissance.