Fonction Excel ChooseCols pour obtenir des colonnes du tableau ou de la gamme

Ce tutoriel vous présentera une nouvelle fonction de tableau dynamique Excel 365 nommée ChooseCols et montrera comment vous pouvez l'utiliser pour extraire toutes les colonnes spécifiques d'un tableau.

Imaginez que vous travaillez avec un ensemble de données de centaines ou de milliers de colonnes. De toute évidence, certaines colonnes sont plus importantes que d'autres, et naturellement vous voudrez peut-être lire leurs données en premier. Excel 365 offre une fonction parfaite pour le travail, qui peut récupérer instantanément des spécifiques à partir d'un tableau, vous pouvez donc vous concentrer sur les informations les plus pertinentes.

• Fonction Excel ChooseCols
• Comment utiliser la fonction ChooseCols dans Excel
• Retourner les n colonnes des dernières n parcours
• Obtenez toutes les autres colonnes
• Retourne horizontalement
• Obtenez des colonnes en fonction de la chaîne avec des numéros
• Extraire les colonnes de plusieurs tableaux
• Excel ChooseCols Fonction ne fonctionne pas

Fonction Excel ChooseCols

La fonction ChooseCols dans Excel est conçue pour renvoyer les colonnes spécifiées à partir d'un tableau ou d'une plage.

La syntaxe comprend les arguments suivants:

ChooseCols (array, col_num1, [col_num2],…)

Où:

Tableau (requis) - le tableau ou la plage source.

Col_num1 (requis) - un entier spécifiant la première colonne à retourner.

COL_NUM2 ,… (Facultatif) - Nombres d'index des colonnes supplémentaires à retourner.

Et c'est à quoi peut ressembler la fonction ChooseCols dans votre Excel:

Disponibilité de la fonction ChooseCols

Actuellement, la fonction ChooseCols est disponible dans Excel pour Microsoft 365 (Windows et Mac) et Excel pour le Web.

Conseil. Pour extraire certaines lignes d'une gamme ou d'un tableau, la fonction de chooserows peut être utile.

Comment utiliser la fonction ChooseCols dans Excel

ChooseCols est une fonction de tableau dynamique, il gère donc les tableaux nativement. La formule doit être saisie dans une seule cellule - la cellule supérieure gauche de la plage de destination - et elle se déverse automatiquement dans autant de colonnes que celle l'a spécifié, ses arguments et autant de lignes qu'il y en a dans le tableau d'origine. Le résultat est un réseau dynamique unique, qui est appelé une plage de déversement.

Pour faire une formule ChooseCols dans Excel, c'est ce que vous devez faire:

1. Pour le tableau , fournissez une gamme de cellules ou un tableau de valeurs.
2. Pour COL_NUM , fournissez un entier positif ou négatif indiquant la colonne à retourner. Un nombre positif tire une colonne correspondante du côté gauche du tableau, un nombre négatif - du côté droit du tableau. Pour obtenir plusieurs colonnes, vous pouvez définir leurs nombres dans des arguments distincts ou dans un argument sous la forme d'une constante de tableau.

Par exemple, pour obtenir des colonnes 2, 3 et 4 à partir de la plage A4: E19, la formule est:

=CHOOSECOLS(A4:E19, 2, 3, 4)

Alternativement, vous pouvez utiliser une constante de tableau horizontale telle que {2,3,4} ou une constante de tableau vertical tel que {2; 3; 4} pour spécifier les numéros de colonne:

=CHOOSECOLS(A4:E19, {2,3,4})

=CHOOSECOLS(A4:E19, {2;3;4})

Les trois formules ci-dessus fourniront le même résultat:

Dans certaines situations, vous pouvez trouver plus pratique de saisir les numéros de colonne dans certaines cellules, puis de référencer ces cellules individuellement ou de fournir une référence à une seule plage. Par exemple:

=CHOOSECOLS(A4:E19, G4, H4, I4)

=CHOOSECOLS(A4:E19, G4:I4)

Cette approche vous donne plus de flexibilité - pour extraire toute autre colonne, vous tapez simplement différents nombres dans les cellules prédéfinies sans avoir à modifier la formule elle-même.

Maintenant que vous connaissez les éléments essentiels, plongeons dans les extras et explorons des formules ChooseCols légèrement plus complexes pour gérer des scénarios spécifiques.

Obtenez les dernières colonnes de portée

Pour retourner une ou plusieurs colonnes à partir de la fin d'une plage, fournissez des nombres négatifs pour les arguments COL_NUM . Cela permettra à la fonction de commencer à compter les colonnes à partir du côté droit du tableau.

Par exemple, pour obtenir la dernière colonne de la gamme, utilisez cette formule:

=CHOOSECOLS(A4:E19, -1)

Pour extraire les deux dernières colonnes, utilisez celle-ci:

=CHOOSECOLS(A4:E19, -2, -1)

Pour retourner les 2 dernières colonnes dans l'ordre inverse, modifiez l'ordre des arguments COL_NUM comme ceci:

=CHOOSECOLS(A4:E19, -1, -2)

Obtenez toutes les autres colonnes dans Excel

Pour extraire toutes les autres colonnes à partir d'une plage donnée, vous pouvez utiliser les chooseCols avec plusieurs autres fonctions. Ci-dessous, il y a deux versions de la formule pour extraire des colonnes impaises et même.

Pour obtenir des colonnes impaires (comme 1, 3, 5, etc.), la formule est:

=CHOOSECOLS(A4:E19, SEQUENCE(ROUNDUP(COLUMNS(A4:E19)/2, 0), 1, 1, 2))

Pour retourner même les colonnes (telles que 2, 4, 6, etc.), la formule prend cette forme:

=CHOOSECOLS(A4:E19, SEQUENCE(ROUNDDOWN(COLUMNS(A4:E19)/2, 0), 1, 2, 2))

La capture d'écran ci-dessous montre la première formule en action:

Comment fonctionne cette formule:

Brève explication : La fonction ChooseCols renvoie toutes les autres colonnes basées sur un tableau de nombres ou même séquentiels produits par la fonction de séquence.

Une rupture de formule détaillée :

La première étape consiste à calculer le nombre de colonnes à retourner. Pour cela, nous utilisons l'une de ces formules:

ROUNDUP(COLUMNS(A4:E19)/2, 0)

ou

ROUNDDOWN(COLUMNS(A4:E19)/2, 0)

Les colonnes comptent le nombre total de colonnes dans la plage de source. Vous divisez ce nombre par 2, puis, selon que vous extrairez les colonnes étranges ou même, autour du quotient, vers le haut ou vers le bas vers l'entier à l'aide d'un tour out-up. Un arrondissement est nécessaire au cas où la plage de source contient un nombre impair de colonnes, ce qui laisse un reste lorsqu'il est divisé par 2.

Notre gamme de source a 5 colonnes. Ainsi, pour les colonnes impairs, le tour du tour (5/2, 0) renvoie 3, tandis que pour les colonnes égales (5/2, 0) renvoie 2.

Le numéro retourné est servi au premier argument ( lignes ) de la fonction de séquence.

Pour les colonnes étranges, nous obtenons:

SEQUENCE(3, 1, 1, 2)

Cette formule de séquence génère un tableau de nombres composés de 3 lignes et 1 colonne, à partir de 1 et incrémentée par 2, qui est {1; 3; 5}.

Pour même des colonnes, nous avons:

SEQUENCE(2, 1, 2, 2)

Dans ce cas, la séquence produit un tableau de nombres composés de 2 lignes et 1 colonne, à partir de 2 et incrémentée par 2, qui est {2; 4}.

Le tableau ci-dessus va à l'argument Col_Num1 de ChooseCols, et vous obtenez le résultat souhaité.

Retourner un tableau horizontalement dans Excel

Pour inverser l'ordre des colonnes dans un tableau de gauche à droite, vous pouvez utiliser les fonctions ChooseCols, Séquence et Colonnes de cette manière:

=CHOOSECOLS(A4:D19, SEQUENCE(COLUMNS(A4:D19)) *-1)

En conséquence, la plage d'origine est renversée horizontalement comme indiqué dans l'image ci-dessous:

Comment fonctionne cette formule:

Ici, nous utilisons la fonction de séquence pour générer un tableau contenant autant de nombres séquentiels que des colonnes dans le tableau source. Pour cela, nous nid les colonnes (A4: D13) dans l'argument des lignes :

SEQUENCE(COLUMNS(A4:D19))

Les autres arguments ( colonnes , démarrage , étape ) sont omis, donc ils par défaut 1. En conséquence, la séquence produit un tableau de nombres séquentiels tels que 1, 2, 3,…, n, où n est l'indice de la dernière colonne du tableau. Pour forcer la fonction ChooseCols à compter les colonnes de droite à gauche, nous multiplions chaque élément de la séquence générée par -1. En conséquence, nous obtenons un tableau de nombres négatifs tels que {-1; -2; -3}, qui va à l'argument Col_num de ChooseCols, en lui demandant de renvoyer les colonnes correspondantes du côté droit du tableau:

CHOOSECOLS(A4:D19, {-1;-2;-3;-4})

Extraire les colonnes basées sur la chaîne avec des nombres

Dans la situation où les numéros d'index des colonnes cibles sont fournis sous la forme d'une chaîne de texte, vous pouvez utiliser la fonction TextsPlit pour diviser la chaîne par un délimiteur donné, puis passer le tableau de nombres résultant à ChooseCols.

Disons que les numéros de colonne sont répertoriés dans la cellule H3, séparés par une virgule et un espace. Pour obtenir les colonnes d'intérêt, utilisez cette formule:

=CHOOSECOLS(A4:E19, TEXTSPLIT(H3, ", ") *1)

Comment fonctionne cette formule:

Tout d'abord, vous divisez une chaîne par un délimiteur donné (une virgule et un espace dans notre cas):

TEXTSPLIT(H3, ", ")

Un résultat intermédiaire est un tableau de valeurs de texte telles que {"1", "4", "5"}. Pour convertir le texte en nombres, multipliez les éléments du tableau par 1 ou effectuez une autre opération mathématique qui ne modifie pas les valeurs d'origine.

TEXTSPLIT(H3, ", ") *1

Cela produit un tableau de valeurs numériques {1,4,5} que la fonction ChooseCols peut traiter, et vous obtiendrez le résultat que vous recherchez:

CHOOSECOLS(A4:E19, {1,4,5})

Extraire les colonnes de plusieurs gammes

Pour obtenir des colonnes particulières à partir de plusieurs gammes non contiguës, vous fusionnez d'abord toutes les gammes en une avec l'aide de la fonction VSTACK, puis gérez la plage fusionnée avec des chooseCols.

Par exemple, pour retourner les colonnes 1 et 3 des plages A4: D8, A12: D15 et A19: D21, la formule est:

=CHOOSECOLS(VSTACK(A4:D8, A12:D15, A19:D21), 1, 3)

La fonction ChooseCols ne fonctionne pas

Si la formule ChooseCols lance une erreur, il est probablement l'un des éléments suivants.

#VALEUR! erreur

Se produit si la valeur absolue de tout argument COL_NUM est nulle ou supérieure au nombre total de colonnes dans le tableau référé.

#NOM? erreur

Se produit si le nom de la fonction est mal orthographié ou si la fonction n'est pas disponible dans votre version Excel. Actuellement, ChooseCols n'est pris en charge que dans Excel 365 et Excel pour le Web. Pour plus de détails, voyez comment corriger l'erreur #Name dans Excel.

#RÉPANDRE! erreur

Se produit lorsque quelque chose empêche la formule de renverser les résultats dans les cellules voisines. Pour le réparer, effacez simplement les cellules obstruées. Pour plus d'informations, veuillez voir comment réparer #Spill! Erreur dans Excel.

C'est comment utiliser la fonction ChooseCols dans Excel pour retourner des colonnes particulières à partir d'une plage ou d'un tableau. Merci d'avoir lu et de vous voir sur notre blog la semaine prochaine!

Classeur de pratique pour télécharger

Formule Excel ChooseCols - 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!