Fonction Excel Torow pour transformer la plage ou la table

Un moyen rapide de transformer une gamme de cellules en une seule ligne à l'aide de la fonction Torow.

Microsoft Excel 365 a introduit plusieurs nouvelles fonctions pour effectuer diverses manipulations avec des tableaux. Avec Torow, vous pouvez effectuer des transformations de plage à rang en un rien de temps. Voici une liste de tâches que cette nouvelle fonction peut accomplir:

• Fonction Excel Torow
• Formule de base de Torow
• Transformer la plage en rangée en ignorant les blancs et les erreurs
• Scanes de numérisation verticalement ou horizontalement
• Rejoignez plusieurs gammes en une seule ligne
• Extraire des valeurs uniques de la plage en ligne
• Alternatives Torow pour Excel 365 - 2010
• La fonction Torow ne fonctionne pas

Fonction Excel Torow

La fonction Torow dans Excel est utilisée pour convertir un tableau ou une gamme de cellules en une seule ligne.

La fonction prend un total de trois arguments, dont seul le premier est requis.

Torow (array, [ignorer], [scan_by_column])

Où:

Tableau (requis) - un tableau ou une plage pour se transformer en une seule ligne.

Ignore (facultatif) - Détermine s'il faut ignorer les blancs ou / et les erreurs. Peut prendre une de ces valeurs:

• 0 ou omis (par défaut) - Gardez toutes les valeurs
• 1 - Ignorer les blancs
• 2 - Ignorer les erreurs
• 3 - ignorer les blancs et les erreurs

Scan_by_column (facultatif) - Définit comment scanner le tableau:

• Faux ou omis (par défaut) - Scannez le tableau horizontalement par ligne.
• Vrai - Scannez le tableau verticalement par colonne.

Conseils:

• Pour transformer un tableau en une seule colonne, utilisez la fonction TOCOL.
• Pour préformer la transformation inverse de la ligne à la liste, utilisez la fonction WrapCols pour s'envelopper en colonnes ou la fonction Wraprows pour envelopper le tableau en lignes.
• Pour transformer les lignes en colonnes, utilisez la fonction de transposition.

Torow Disponibilité

Torow est une nouvelle fonction, qui est prise en charge uniquement dans Excel pour Microsoft 365 (pour Windows et Mac) et Excel pour le Web.

Formule de base de Torow à Excel

Pour faire une simple transformation de plage à rang, utilisez la formule Torow sous sa forme de base. Pour cela, vous devez définir uniquement le premier argument ( tableau ).

Par exemple, pour transformer un tableau bidimensionnel composé de 3 colonnes et 3 lignes en une seule ligne, la formule est:

=TOROW(A3:C6)

Vous entrez la formule en une seule cellule (A10 dans notre cas), et il se déverse automatiquement dans autant de cellules que nécessaire pour conserver tous les résultats. En termes Excel, la plage de sortie entourée d'une bordure bleue mince est appelée gamme de déversement.

Comment fonctionne cette formule:

Premièrement, une gamme fournie de cellules est transformée en un réseau bidimensionnel. Veuillez remarquer les colonnes délimitées par des virgules et les lignes séparées des demi-colonies:

{"Apple","Banana","Cherry";1,2,3;4,5,6;7,8,9}

Ensuite, la fonction Torow lit le tableau de gauche à droite et le convertit en un réseau horizontal unidimensionnel:

{"Apple","Banana","Cherry",1,2,3,4,5,6,7,8,9}

Le résultat va à la cellule A10, à partir de laquelle il se répand dans la cellule voisine à droite.

Transformer la plage en rangée en ignorant les blancs et les erreurs

Par défaut, la fonction Torow conserve toutes les valeurs du tableau source, y compris les cellules et les erreurs vides. Dans la sortie, les valeurs zéro apparaissent à la place des cellules vierges, ce qui peut être assez déroutant.

Pour exclure les blancs , définissez l'argument Ignore sur 1:

=TOROW(A3:C5, 1)

Pour ignorer les erreurs , définissez l'argument ignoré sur 2:

=TOROW(A3:C5, 2)

Pour sauter les deux, les blancs et les erreurs , utilisez 3 pour l'argument Ignore :

=TOROW(A3:C5, 3)

L'image ci-dessous montre les trois scénarios en action:

Lisez le tableau horizontalement ou verticalement

Avec le comportement par défaut, la fonction Torow traite le tableau horizontalement de gauche à droite. Pour scanner les valeurs par colonne de haut en bas, vous définissez l'argument 3 ^RD ( scan_by_column ) sur True ou 1.

Par exemple, pour lire la plage source par ligne, la formule en E3 est:

=TOROW(A3:C5)

Pour scanner la plage par colonne, la formule en E8 est:

=TOROW(A3:C5, ,TRUE)

Dans les deux cas, les tableaux résultants sont de la même taille, mais les valeurs sont organisées dans un ordre différent.

Fusionner plusieurs gammes en une seule rangée

Pour combiner plusieurs plages non adjacentes en une seule ligne, vous les empilez d'abord horizontalement ou verticalement en un seul tableau à l'aide de HSTack ou Vstack, respectivement, puis utilisez la fonction Torow pour convertir le tableau combiné en une ligne.

Selon votre logique commerciale, l'une des formules suivantes effectuera la tâche.

Empiler les tableaux horizontalement et convertir par ligne

Avec la première plage dans A3: C4 et la deuxième plage dans A8: C9, la formule ci-dessous empilera les deux plages horizontalement en un seul tableau, puis la transformera en une ligne en lisant les valeurs de gauche à droite. Le résultat est en E3 dans l'image ci-dessous.

=TOROW(HSTACK(A3:C4, A8:C9))

Les tableaux de pile horizontalement et convertissent par colonne

Pour lire le tableau empilé verticalement de haut en bas, vous définissez l'argument 3 ^e de Torow à TRUE comme indiqué dans E5 dans l'image ci-dessous:

=TOROW(HSTACK(A3:C4, A8:C9), ,TRUE)

Empiler les tableaux verticalement et convertir par ligne

Pour ajouter chaque tableau suivant au bas du tableau précédent et lire le réseau combiné horizontalement, la formule en E12 est:

=TOROW(VSTACK(A3:C4, A8:C9))

Empiler les tableaux verticalement et convertir par colonne

Pour ajouter chaque tableau suivant au bas de la précédente et scanner le tableau combiné verticalement, la formule est:

=TOROW(VSTACK(A3:C4, A8:C9), ,TRUE)

Pour mieux comprendre la logique, observez l'ordre des valeurs différentes dans les tableaux résultants:

Extraire des valeurs uniques d'une plage à une ligne

En commençant par Microsoft Excel 2016, nous avons une fonction merveilleuse, nommée unique, qui peut facilement obtenir des valeurs uniques d'une seule colonne ou d'une seule ligne. Cependant, il ne peut pas gérer les tableaux multi-colonnes. Pour surmonter cette limitation, utilisez les fonctions uniques et Torow ensemble.

Par exemple, pour extraire toutes les différentes valeurs (distinctes) de la plage A2: C7 et placez les résultats en une ligne, la formule est:

=UNIQUE(TOROW(A2:C7), TRUE)

Alors que Torow renvoie un tableau horizontal unidimensionnel, nous définissons l'argument 2ème ( BY_COL ) de TRUE Unique pour comparer les colonnes les unes contre les autres.

Dans le cas où vous souhaitez les résultats disposés en ordre alphabétique, enveloppez la formule ci-dessus dans la fonction de tri:

=SORT(UNIQUE(TOROW(A2:C7), TRUE), , ,TRUE )

Comme pour unique, l'argument BY_COL est également défini sur true.

Alternative Torow pour Excel 365 - 2010

Dans les versions Excel où la fonction Torow n'est pas disponible, vous pouvez transformer une plage en une seule ligne en utilisant une combinaison de quelques fonctions différentes qui fonctionnent dans des versions plus anciennes. Ces solutions sont plus complexes, mais elles fonctionnent.

Pour scanner la plage horizontalement, la formule générique est:

Index ( plage , quotient (colonne (A1) -1, colonnes ( plage )) 1, mod (colonne (a1) -1, colonnes ( plage )) 1)

Pour scanner la plage verticalement, la formule générique est:

Index ( plage , mod (colonne (a1) -1, colonnes ( plage )) 1, quotient (colonne (a1) -1, colonnes ( plage )) 1)

Pour notre exemple de données dans A3: C5, les formules prennent cette forme:

Pour scanner la plage par ligne:

=INDEX($A$3:$C$5, QUOTIENT(COLUMN(A1)-1, COLUMNS($A$3:$C$5)) 1, MOD(COLUMN(A1)-1, COLUMNS($A$3:$C$5)) 1)

Cette formule est une alternative à la fonction Torow avec l'argument de 3 ^points défini sur faux ou omis:

=TOROW(A3:C5)

Pour scanner la plage par colonne:

=INDEX($A$3:$C$5, MOD(COLUMN(A1)-1, COLUMNS($A$3:$C$5)) 1, QUOTIENT(COLUMN(A1)-1, COLUMNS($A$3:$C$5)) 1)

Cette formule est équivalente à la fonction Torow avec l'argument de 3 ^points défini sur true:

=TOROW(A3:C5, ,TRUE)

Veuillez noter que contrairement à la fonction Torow Dynamic Array, ces formules traditionnelles doivent être entrées dans chaque cellule où vous souhaitez que les résultats apparaissent. Dans notre cas, la première formule (par ligne) va à E3 et est copiée via M3. La deuxième formule (par colonne) atterrit dans E8 et est traînée à travers M8.

Pour que les formules soient copies correctement, nous verrouillons la plage à l'aide de références absolues ($ a 3 $: $ C 5 $). Une gamme nommée fera également l'affaire.

Si vous avez copié les formules à plus de cellules que nécessaire, un #ref! L'erreur apparaîtra dans les cellules "supplémentaires". Pour résoudre ce problème, enveloppez votre formule dans la fonction IFERROR comme ceci:

=IFERROR(INDEX($A$3:$C$5, QUOTIENT(COLUMN(A1)-1, COLUMNS($A$3:$C$5)) 1, MOD(COLUMN(A1)-1, COLUMNS($A$3:$C$5)) 1), "")

Comment ces formules fonctionnent

Vous trouverez ci-dessous une rupture détaillée de la première formule qui organise les valeurs par ligne:

=INDEX($A$3:$C$5, QUOTIENT(COLUMN(A1)-1, COLUMNS($A$3:$C$5)) 1, MOD(COLUMN(A1)-1, COLUMNS($A$3:$C$5)) 1)

Au cœur de la formule, nous utilisons la fonction d'index pour obtenir la valeur d'une cellule en fonction de sa position relative dans la plage.

Le numéro de ligne est calculé par cette formule:

QUOTIENT(COLUMN(A1)-1, COLUMNS($A$3:$C$5)) 1

L'idée est de produire une séquence de nombres répétitive telle que 1,1,1,2,2,2,3,3,3,… où chaque nombre répète autant de fois que des colonnes dans la plage de source. Et voici comment nous faisons ceci:

Quotient renvoie la partie entière d'une division.

Pour le numérateur , nous utilisons la colonne (A1) -1, qui renvoie un numéro de série à partir de 0 dans la première cellule où la formule est entrée à n (nombre total de valeurs dans la plage moins 1) dans la dernière cellule où la formule en entrée. Dans cet exemple, nous avons 0 en E2 et 8 en M3.

Pour le dénominateur , nous utilisons des colonnes ($ A 3: $ C 5 $)). Cela renvoie un nombre constant égal au nombre de colonnes dans votre plage (3 dans notre cas).

En conséquence, la fonction de quotient renvoie 0 dans les 3 premières cellules (E3: G3), à laquelle nous ajoutons 1, donc le numéro de ligne est 1.

Pour les 3 cellules suivantes (H3: J3), le quotient renvoie 1 et 1 donne la ligne numéro 2. Et ainsi de suite.

Pour calculer le numéro de colonne , vous créez une séquence de nombres appropriée à l'aide de la fonction MOD:

MOD(COLUMN(A1)-1, COLUMNS($A$3:$C$5)) 1

Comme il y a 3 colonnes dans notre gamme, la séquence doit ressembler: 1,2,3,1,2,3,…

La fonction MOD renvoie le reste après la division.

Dans E3, MOD (colonne (A1) -1, colonnes ($ a 3 $: $ c 5 $)) 1

devient

Mod (1-1, 3) 1)

et retourne 1.

En F3, mod (colonne (b1) -1, colonnes ($ a 3 $: $ c 5 $)) 1

devient

Mod (2-1, 3) 1)

et retourne 2.

Une fois les numéros de ligne et de colonne établies, l'index obtient facilement la valeur à l'intersection de cette ligne et colonne.

Dans E3, Index ($ a 3: $ C 5, 1, 1) renvoie une valeur de la ligne 1 ^ST et de la colonne 1 ^st de la plage référencée, c'est-à-dire de la cellule A3.

Dans F3, l'index ($ a 3 $: $ C 5, 1, 2) renvoie une valeur de la ligne 1 ^st et de la 2 ⁿ colonne, c'est-à-dire de la cellule B3.

Et ainsi de suite.

La deuxième formule qui scanne la gamme par colonne, fonctionne de la même manière. La différence est que nous utilisons MOD pour calculer le numéro de ligne et le quotient pour déterminer le numéro de colonne.

La fonction Torow ne fonctionne pas

Si la fonction Torow entraîne une erreur, il est très probable que ce soit l'une de ces raisons:

#NOM? erreur

Avec la plupart des fonctions Excel, un #Name? L'erreur est une indication claire que le nom de la fonction est mal orthographié. Avec Torow, cela peut également signifier que la fonction n'est pas disponible dans votre Excel. Si votre version Excel dans autre que 365, essayez d'utiliser une alternative Torow.

#NUM ERREUR

Un #num! L'erreur indique que le tableau renvoyé ne peut pas tenir dans une ligne. Le plus souvent, cela se produit lorsque vous référez des colonnes et / ou des lignes entières au lieu d'une gamme plus petite.

Erreur #spill

Dans la plupart des cas, une erreur #spill suggère que la ligne où vous avez entré la formule n'a pas assez de cellules vierges pour déborder les résultats. Si les cellules voisines sont visuellement vides, assurez-vous qu'il n'y a pas d'espaces ou d'autres caractères non imprimants. Pour plus d'informations, voir ce que l'erreur #spill signifie dans Excel.

C'est ainsi que vous utilisez la fonction Torow dans Excel pour convertir un tableau ou une plage bidimensionnel en une seule ligne. Je vous remercie d'avoir lu et j'espère vous voir sur notre blog la semaine prochaine!

Classeur de pratique pour télécharger

Fonction Excel Torow - Exemples de formule (fichier .xlsx)

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!