Convertir la colonne / ligne en tableau dans Excel: Fonctions WrapCols & Wraprows

Le moyen le plus rapide de transformer une colonne ou une ligne de valeurs en un tableau bidimensionnel consiste à utiliser la fonction WrapCols ou Wraprows.

Depuis les premiers jours d'Excel, il a été très bon pour calculer et analyser les nombres. Mais la manipulation des tableaux a été traditionnellement un défi. L'introduction de tableaux dynamiques a rendu l'utilisation des formules de tableau beaucoup plus facile. Et maintenant, Microsoft publie un ensemble de nouvelles fonctions de tableau dynamique pour manipuler et rétablir les tableaux. Ce tutoriel vous apprendra à utiliser deux fonctions de ce type, WrapCols et Wraprows, pour transformer une colonne ou une ligne en un tableau 2D en un rien de temps.

• Fonction Excel WrapCols
• Fonction excel wraprows
• Exemples de formule pour convertir la colonne / ligne en tableau
  • Comptage enveloppant par colonne ou ligne
  • Pad Valeurs manquantes
  • Fusionner plusieurs lignes dans la gamme 2D
  • Combiner plusieurs colonnes dans une plage 2D
  • Envelopper et trier à la fois
• Alternative à wrapcols
• Alternative Wraprows
• Wrapcols et wraprows ne fonctionnent pas

Fonction Excel WrapCols

La fonction WrapCols dans Excel transforme une ligne ou une colonne de valeurs en un tableau bidimensionnel basé sur le nombre spécifié de valeurs par ligne.

La syntaxe a les arguments suivants:

Wrapcols (vector, wrap_count, [pad_with])

Où:

• Vector (requis) - le tableau ou la plage de la source unidimensionnelle.
• wrap_count (requis) - Le nombre maximum de valeurs par colonne.
• PAD_WITH (Facultatif) - La valeur à remplir avec la dernière colonne en cas d'éléments insuffisants pour le remplir. S'il est omis, les valeurs manquantes seront rembourrées avec # n / a (par défaut).

Par exemple, pour modifier la plage B5: B24 en un tableau bidimensionnel avec 5 valeurs par colonne, la formule est:

=WRAPROWS(B5:B24, 5)

Vous entrez la formule dans n'importe quelle cellule unique et il se déverse automatiquement dans autant de cellules que nécessaire. Dans la sortie WrapCols, les valeurs sont disposées verticalement, de haut en bas, en fonction de la valeur wrap_count . Une fois le décompte atteint, une nouvelle colonne a commencé.

Fonction excel wraprows

La fonction Wraprows dans Excel convertit une ligne ou une colonne de valeurs en un tableau bidimensionnel basé sur le nombre de valeurs par ligne que vous spécifiez.

La syntaxe est la suivante:

Wraprows (vector, wrap_count, [pad_with])

Où:

• Vector (requis) - le tableau ou la plage de la source unidimensionnelle.
• wrap_count (requis) - le nombre maximum de valeurs par ligne.
• pad_with (facultatif) - la valeur à remplir avec la dernière ligne si les éléments sont insuffisants pour le remplir. La valeur par défaut est # n / a.

Par exemple, pour transformer la plage B5: B24 en un tableau 2D ayant 5 valeurs dans chaque ligne, la formule est:

=WRAPROWS(B5:B24, 5)

Vous entrez dans la formule dans la cellule supérieure gauche de la plage de déversement, et il remplit automatiquement toutes les autres cellules. La fonction Wraprows organise les valeurs horizontalement, de gauche à droite, en fonction de la valeur wrap_count . Après avoir atteint le décompte, il commence une nouvelle ligne.

Disponibilité de WrapCols et Wraprows

Les deux fonctions ne sont disponibles que dans Excel pour Microsoft 365 (Windows et Mac) et Excel pour le Web.

Dans les versions antérieures, vous pouvez utiliser des formules traditionnelles plus complexes pour effectuer des transformations de colonne et de ligne à la ligne. Plus loin dans ce tutoriel, nous discuterons en détail des solutions alternatives.

Conseil. Pour effectuer une opération inverse, c'est-à-dire changer un tableau 2D en une seule colonne ou une ligne, utilisez respectivement la fonction TOCOL ou TOROW.

Comment convertir la colonne / la ligne dans la plage d'Excel - Exemples

Maintenant que vous avez la compréhension de l'utilisation de base, examinons de plus près quelques cas plus spécifiques.

Définissez le nombre maximum de valeurs par colonne ou ligne

Selon la structure de vos données d'origine, vous pouvez trouver que cela peut être réorganisé en colonnes (wrapcols) ou en lignes (wraprows). Quelle que soit la fonction que vous utilisez, c'est l'argument Wrap_Count qui détermine le nombre maximum de valeurs dans chaque colonne / ligne.

Par exemple, pour transformer la plage B4: B23 en un tableau 2D, afin que chaque colonne ait un maximum de 10 valeurs, utilisez cette formule:

=WRAPCOLS(B4:B23, 10)

Pour réorganiser la même plage par ligne, de sorte que chaque ligne a un maximum de 4 valeurs, la formule est:

=WRAPROWS(B4:B23, 4)

L'image ci-dessous montre à quoi cela ressemble:

Pad les valeurs manquantes dans le tableau résultant

Dans le cas où il y a des valeurs insuffisantes pour remplir toutes les colonnes / lignes de la plage résultante, les wraprows et wrapcols renverront # n / a des erreurs pour maintenir la structure du tableau 2D.

Pour modifier le comportement par défaut, vous pouvez fournir une valeur personnalisée pour l'argument PAD_WITH en option.

Par exemple, pour transformer la plage B4: B21 en un tableau 2D avec des valeurs maximales de 5 larges et remplissez la dernière ligne avec des tirets s'il n'y a pas suffisamment de données pour les remplir, utilisez cette formule:

=WRAPROWS(B4:B21, 5, "-")

Pour remplacer les valeurs manquantes par des chaînes de longueur zéro (blanc), la formule est:

=WRAPROWS(B4:B21, 5, "")

Veuillez comparer les résultats avec le comportement par défaut (formule en d5) où PAD_With est omis:

Fusionner plusieurs lignes dans la gamme 2D

Pour combiner quelques lignes séparées en un seul tableau 2D, vous empilez d'abord les lignes horizontalement à l'aide de la fonction HSTACK, puis enveloppez les valeurs à l'aide de Wraprows ou WrapCols.

Par exemple, pour fusionner les valeurs de 3 lignes (B5: J5, B7: G7 et B9: F9) et envelopper dans des colonnes, chacune contenant 10 valeurs, la formule est:

=WRAPCOLS(HSTACK(B5:J5, B7:G7, B9:F9), 10)

Pour combiner les valeurs de plusieurs lignes dans une plage 2D où chaque ligne contient 5 valeurs, la formule prend cette forme:

=WRAPROWS(HSTACK(B5:J5, B7:G7, B9:F9), 5)

Combiner plusieurs colonnes en tableau 2D

Pour fusionner plusieurs colonnes dans une plage 2D, vous les empilez d'abord verticalement à l'aide de la fonction VSTACK, puis enveloppez les valeurs en lignes (wraprows) ou colonnes (wrapcols).

Par exemple, pour combiner les valeurs de 3 colonnes (B5: J5, B7: G7 et B9: F9) dans une plage 2D où chaque colonne contient 10 valeurs, la formule est:

=WRAPCOLS(HSTACK(B5:J5, B7:G7, B9:F9), 10)

Pour combiner les mêmes colonnes dans une plage 2D où chaque ligne contient 5 valeurs, utilisez cette formule:

=WRAPROWS(HSTACK(B5:J5, B7:G7, B9:F9), 5)

Enveloppez et triez le tableau

Dans la situation où la plage de source a des valeurs dans un ordre aléatoire pendant que vous souhaitez que la sortie soit triée, procédez de cette manière:

1. Triez le tableau initial comme vous le souhaitez en utilisant la fonction de tri.
2. Fournir le tableau trié à WrapCols ou Wraprows.

Par exemple, pour envelopper la plage B4: B23 en lignes, 4 valeurs dans chacune et trier la plage résultante de A à Z, construisez une formule comme celle-ci:

=WRAPROWS(SORT(B4:B23), 4)

Pour envelopper la même plage en colonnes, 10 valeurs dans chacune et trier la sortie par ordre alphabétique, la formule est:

=WRAPCOLS(SORT(B4:B23), 10)

Les résultats semblent comme suit:

Conseil. Pour organiser les valeurs dans le tableau résultant dans l'ordre descendant , définissez le troisième argument ( SORT_ORD ) de la fonction de tri à -1.

Alternative enveloppante pour Excel 365 - 2010

Dans les versions Excel plus anciennes où la fonction WrapCols n'est pas prise en charge, vous pouvez créer votre propre formule pour envelopper les valeurs d'un tableau unidimensionnel en colonnes. Cela peut être fait en utilisant 5 fonctions différentes ensemble.

Alternative enveloppante pour convertir une ligne en gamme 2D:

IFerror (if (row (a1)> n , "", index ( row_range ,, row (a1) (colonne (a1) -1) * n )), "")

Alternative enveloppante pour convertir une colonne en gamme 2D:

IFrorror (if (row (a1)> n , "", index ( colonnel_range , row (a1) (colonne (a1) -1) * n )), "")

Où n est le nombre maximum de valeurs par colonne.

Dans l'image ci-dessous, nous utilisons la formule suivante pour transformer une plage d'une rangée (D4: J4) en un tableau à trois rangées.

=IFERROR(IF(ROW(A1)>3, "", INDEX($D$4:$J$4, , ROW(A1) (COLUMN(A1)-1)*3)), "")

Et cette formule transforme une plage d'une colonne (B4: B20) en un tableau à cinq rangées:

=IFERROR(IF(ROW(A1)>5, "", INDEX($B$4:$B$20, ROW(A1) (COLUMN(A1)-1)*5)), "")

Les solutions ci-dessus émulent les formules de wrapcols analogues et produisent les mêmes résultats:

=WRAPCOLS(D4:J4, 3, "")

et

=WRAPCOLS(B4:B20, 5, "")

Veuillez garder à l'esprit que, contrairement à la fonction Dynamic Array WrapCols, les formules traditionnelles suivent l'approche à une forme à une cellule. Ainsi, notre première formule est entrée en D8 et a copié 3 lignes vers le bas et 3 colonnes à droite. La deuxième formule est entrée en D14 et a copié 5 lignes vers le bas et 4 colonnes à droite.

Comment ces formules fonctionnent

Au cœur des deux formules, nous utilisons la fonction d'index qui renvoie une valeur du tableau fourni en fonction d'une ligne et d'un numéro de colonne:

Index (array, row_num, [colonnel_num])

Comme nous avons affaire à un tableau unique, nous pouvons omettre l'argument ROW_NUM , donc il est par défaut 1. L'astuce consiste à faire calculer COL_NUM automatiquement pour chaque cellule où la formule est copiée. Et voici comment nous faisons ceci:

ROW(A1) (COLUMN(A1)-1)*3)

La fonction de ligne renvoie le numéro de ligne de la référence A1, qui est 1.

La fonction de colonne renvoie le numéro de colonne de la référence A1, qui est également 1. La soustraction 1 le transforme en zéro. Et la multiplication de 0 par 3 donne 0.

Ensuite, vous additionnez 1 renvoyé par ligne et 0 retourné par colonne et obtenez 1 en conséquence.

De cette façon, la formule d'index dans la cellule supérieure gauche de la plage de destination (D8) subit cette transformation:

INDEX($D$4:$J$4, ,ROW(A1) (COLUMN(A1)-1)*3))

modification de

INDEX($D$4:$J$4, ,1)

et renvoie la valeur de la 1ère colonne du tableau spécifié, qui est "pommes" en D4.

Lorsque la formule est copiée sur la cellule D9, les références de cellules relatives changent sur la base d'une position relative des lignes et des colonnes tandis que la référence de plage absolue reste inchangée:

INDEX($D$4:$J$4,, ROW(A2) (COLUMN(A2)-1)*3))

se transforme en:

INDEX($D$4:$J$4,, 2 (1-1)*3))

devient:

INDEX($D$4:$J$4,, 2))

et renvoie la valeur de la 2ème colonne du tableau spécifié, qui est "abricots" dans E4.

La fonction IF vérifie le numéro de ligne et si elle est supérieure au nombre de lignes que vous avez spécifiées (3 dans notre cas) renvoie une chaîne vide (""), sinon le résultat de la fonction d'index:

IF(ROW(A1)>3, "", INDEX(…))

Enfin, la fonction IFERROR fixe un #Ref! Erreur qui se produit lorsque la formule est copiée dans plus de cellules que nécessaire.

La deuxième formule qui convertit une colonne en gamme 2D fonctionne avec la même logique. La différence est que vous utilisez la combinaison de colonnes de ligne pour déterminer l'argument ROW_NUM pour l'index. Le paramètre COL_NUM n'est pas nécessaire dans ce cas car il n'y a qu'une seule colonne dans le tableau source.

Alternative en wraprows pour Excel 365 - 2010

Pour envelopper les valeurs d'un tableau unidimensionnel en lignes dans Excel 2019 et plus tôt, vous pouvez utiliser les alternatives suivantes à la fonction Wraprows.

Transformez une ligne en gamme 2D:

IFrorror (if (colonne (a1)> n , "", index ( row_range ,, colonne (a1) (ligne (a1) -1) * n )), "")

Modifier une colonne en gamme 2D:

IFrorror (if (colonne (a1)> n , "", index ( colonnel_range , colonne (a1) (ligne (a1) -1) * n )), "")

Où n est le nombre maximum de valeurs par ligne.

Dans notre échantillon de données, nous utilisons la formule suivante pour convertir une plage d'une rangée (D4: J4) en une plage à trois colonnes. La formule atterrit dans la cellule D8, puis est copiée sur 3 colonnes et 3 rangées.

=IFERROR(IF(COLUMN(A1)>3, "", INDEX($D$4:$J$4, , COLUMN(A1) (ROW(A1)-1)*3)), "")

Pour remodeler une plage à 1 colonne (B4: B20) dans une plage de 5 colonnes, entrez la formule ci-dessous en D14 et faites-la glisser sur 5 colonnes et 4 lignes.

=IFERROR(IF(COLUMN(A1)>5, "", INDEX($B$4:$B$20, COLUMN(A1) (ROW(A1)-1)*5)), "")

Dans Excel 365, les mêmes résultats peuvent être obtenus avec les formules WrapCols équivalentes:

=WRAPROWS(D4:J4, 3, "")

et

=WRAPROWS(B4:B20, 5, "")

Comment ces formules fonctionnent

Essentiellement, ces formules fonctionnent comme dans l'exemple précédent. La différence réside dans la façon dont vous déterminez les coordonnées ROW_NUM et COL_NUM pour la fonction d'index:

INDEX($D$4:$J$4,, COLUMN(A1) (ROW(A1)-1)*3))

Pour obtenir le numéro de colonne pour la cellule supérieure gauche dans la plage de destination (D8), vous utilisez cette expression:

COLUMN(A1) (ROW(A1)-1)*3)

Cela change à:

1 (1-1) * 3

et donne 1.

En conséquence, la formule ci-dessous renvoie la valeur de la première colonne du tableau spécifié, qui est "pommes":

INDEX($D$4:$J$4,, 1)

Jusqu'à présent, le résultat est le même que dans l'exemple précédent. Mais voyons ce qui se passe dans d'autres cellules…

Dans la cellule D9, les références de cellules relatives changent comme suit:

INDEX($D$4:$J$4,, COLUMN(A2) (ROW(A2)-1)*3))

Ainsi, la formule se transforme en:

INDEX($D$4:$J$4,, 1 (2-1)*3))

devient:

INDEX($D$4:$J$4,, 4))

et renvoie la valeur de la 4ème colonne du tableau spécifié, qui est "cerises" dans G4.

La fonction IF vérifie le numéro de colonne et s'il est supérieur au nombre de colonnes que vous avez spécifiées, renvoie une chaîne vide (""), sinon le résultat de la fonction d'index:

IF(COLUMN(A1)>3, "", INDEX(…))

En tant que touche finale, Iferror empêche #Ref! Erreurs en apparaissant dans des cellules "supplémentaires" si vous copiez la formule à plus de cellules que nécessaire.

La fonction de wrapcols ou wraprows ne fonctionne pas

Si les fonctions "enveloppement" ne sont pas disponibles dans votre Excel ou entraînent une erreur, il est très susceptible d'être l'une des raisons ci-dessous.

#NOM? erreur

Dans Excel 365, un #Name? Une erreur peut se produire car vous avez mal orthographié le nom de la fonction. Dans d'autres versions, cela indique que les fonctions ne sont pas prises en charge. En tant que solution de contournement, vous pouvez utiliser une alternative Alternative ou Wraprows Wraprows.

#VALEUR! erreur

Une erreur #Value se produit si l'argument vectoriel n'est pas un tableau unidimensionnel.

#Num! erreur

Une erreur #NUM se produit si la valeur wrap_count est de 0 ou un nombre négatif.

#RÉPANDRE! erreur

Le plus souvent, une erreur #spill indique qu'il n'y a pas assez de cellules vierges pour déborder les résultats. Éliminez les cellules voisines et elle aura disparu. Si l'erreur persiste, consultez ce que #Spill signifie dans Excel et comment le réparer.

C'est comment utiliser les fonctions WrapCols et Wraprows pour convertir une plage unidimensionnelle en un tableau bidimensionnel dans Excel. 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

Fonctions WrapCols et Wraprows - Exemples (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!