Comment utiliser les fonctions Vstack et HSTACK dans Microsoft Excel

La combinaison de deux tableaux ou plus dans un classeur Excel était autrefois une tâche compliquée et fastidieuse. Cependant, l'introduction des fonctions VSTACK et HSTACK fin 2022 a changé la donne qui a rendu ce processus beaucoup plus facile.

Les fonctions VSTACK et HSTACK sont disponibles pour ceux qui utilisent Excel pour Microsoft 365, Excel pour le Web et les applications mobiles et tablettes Excel.

Les syntaxes Vstack et Hstack

La fonction VStack ajoute des tableaux verticalement, en utilisant la syntaxe suivante:

=VSTACK(a,(b)...)

Où l'argument un est le tableau qui apparaîtra d'abord en haut de la pile et l'argument b est le premier de 253 tableaux supplémentaires à ajouter immédiatement ci-dessous.

D'un autre côté, la fonction HSTACK Ajoute des tableaux horizontalement:

=HSTACK(a,(b)...)

Où l'argument un est le tableau le plus à gauche dans le résultat annexé et l'argument b est le premier de 253 tableaux supplémentaires à ajouter immédiatement à droite.

Si vous essayez d'empiler plus de 254 tableaux dans l'ensemble, vous verrez un message lisant « Vous avez entré trop d'arguments pour cette fonction », et vous devrez réduire le nombre d'arguments avant de pouvoir commettre la formule. Vous pouvez contourner cela en nilitant des combinaisons VSTack et HSTACK supplémentaires, mais sachez que cela peut entraver considérablement les performances de votre classeur.

Vstack et HSTack sont des fonctions de tableau dynamique, ce qui signifie que le résultat se déverse de la cellule dans laquelle vous tapez la formule, et toute modification des données dans les tableaux de source se reflète dans les tableaux empilés. Cependant, les tableaux dynamiques sont incompatibles avec les tables Excel, vous devez donc taper la formule dans une cellule ordinaire.

Vstack et HSTACK utilisés: Exemples

Vous pouvez utiliser les fonctions VStack et HSTACK d'Excel pour ajouter des données dans les gammes de cellules régulières, les tables Excel ou les gammes nommées.

Ici, tapant:

=VSTACK(A2:C6,A9:C13,A16:C20)

Dans la cellule E1, les trois tableaux dans les gammes cellulaires A2 à C6, A9 à C13 et A16 à C20 verticalement.

Après avoir tapé le signe égal, le nom de la fonction et l'ouverture des parenthèses, plutôt que de taper manuellement les plages, utilisez votre souris pour les sélectionner.

De la même manière, tapant:

=HSTACK(A2:C6,G2:G6)

Empile les données dans les cellules A2 à C6 et G2 à G6 horizontalement.

D'un autre côté, après avoir nommé les gammes LGA, LGBet LGCTyping:

=VSTACK(LgA,LgB,LgC)

atteint le même résultat que lors de l'utilisation de références de cellules directes.

Cependant, l'avantage d'utiliser des plages nommées au lieu de références cellulaires est qu'elles sont plus faciles à comprendre et à analyser, et vous pouvez rapidement ajouter des gammes nommées aux formules sans toucher votre souris. Vous pouvez appliquer exactement le même principe à la fonction HSTACK.

Dans tous les exemples ci-dessus, si plus de lignes ou de colonnes de données sont ajoutées à l'une des gammes source, Vstack et HSTack ne les ramasseront pas. Cependant, si vos données source sont formatées sous forme de tables Excel, toutes les lignes ou colonnes supplémentaires seront ajoutées au résultat. En effet

=VSTACK(T_LgA,T_LgB,T_LgC)

Les tableaux sont également utiles lors de l'utilisation de VStack et HSTACK pour ajouter les données de plusieurs feuilles de calcul dans un classeur. Si vos ensembles de données sont stockés dans des gammes régulières dans des feuilles de calcul distinctes, vous devez sauter entre les onglets pour sélectionner les gammes manuellement, ou utiliser le référencement 3D si chaque feuille de calcul est structurée identique. Cependant, comme toutes les tables d'un classeur Excel doivent être nommées de manière unique, vous n'avez pas besoin de dire à Excel quelle feuille il se trouve – vous pouvez simplement taper la première lettre, utiliser les touches de flèche pour sauter dans la table pertinente et appuyer sur l'onglet pour confirmer.

Encore une fois, la même notion s'applique à la fonction HSTACK.

Combinant vstack et hstack

Parfois, vous devrez peut-être utiliser Vstack et HSTACK ensemble dans une seule formule pour ajouter des parties de tableaux.

Dans cet exemple, disons que vous souhaitez créer un nouveau tableau avec la ligue dans la première colonne, l'équipe dans la deuxième, le score dans le troisième et le bonus du quatrième.

Une façon de le faire est de nid vstack à l'intérieur de hstack:

=HSTACK(VSTACK(A2:C6,A9:C13,A16:C20),VSTACK(G2:G6,G9:G13,G16:G20))

Premièrement, la formule empile verticalement les données dans les cellules A2 à C6, A9 à C13 et A16 à C20. Deuxièmement, il fait de même avec les données dans les cellules G2 à G6, G9 à G13 et G16 à G20. Enfin, il faut ces deux tableaux VStack et les ajoute horizontalement à l'aide de HSTACK.

Vous pouvez également nicher hstack à l'intérieur de Vstack:

=VSTACK(HSTACK(A2:C6,G2:G6),HSTACK(A9:C13,G9:G13),HSTACK(A16:C20,G16:G20))

Cette fois, la formule utilise d'abord HSTACK pour ajouter les données dans les cellules A2 à C6 avec les données dans les cellules G2 à G6, puis il fait de même avec les deux autres ligues. Enfin, il faut ces trois tableaux HSTACK et les ajoute verticalement à l'aide de VStack.

Ainsi, lequel des deux fonctions que vous nichez à l'intérieur de l'autre dépend si vous devez effectuer des piles plus verticales ou horizontales. Dans l'exemple ci-dessus, car il y a trois tableaux à empiler verticalement et seulement deux horizontalement, la formule est plus courte lorsque Vstack est imbriqué à l'intérieur de Hstack. Cependant, si vous avez besoin d'effectuer plus de piles horizontales que celles verticales, il est préférable de nid hstack à l'intérieur de Vstack.

Ajouter des tableaux de différentes tailles à l'aide de Vstack et HSTACK

Lorsque vous ajoutez des tableaux verticalement à l'aide de Vstack, le nombre total de lignes est le nombre combiné de toutes les lignes dans les tableaux sélectionnés. Cependant, le nombre de colonnes occupent le résultat est le même que le tableau le plus large de la sélection.

Donc, si vous utilisez VSTACK pour ajouter les tableaux dans la capture d'écran ci-dessous, le résultat est de 15 lignes de haut (cinq lignes dans chaque tableau en annexe) et quatre colonnes à travers (la largeur du tableau les plus larges). Cependant, parce que les tableaux plus petits ont été élargis pour correspondre à la taille du plus grand, # n / a est renvoyé à la place des valeurs manquantes.

=VSTACK(A2:D6,A9:C13,A16:C20)

Une façon de ranger le tableau Vstack résultant est d'envelopper la formule entière à l'intérieur de l'IFNA et d'utiliser des citations doubles pour remplacer les chaînes vides par des cellules vierges:

=IFNA(VSTACK(A2:D6,A9:C13,A16:C20),"")

De même, lorsque vous utilisez HSTACK pour ajouter les tableaux horizontalement, le nombre total de colonnes est déterminé par le nombre de colonnes dans les données source, mais le nombre de lignes est le même que le tableau sélectionné le plus haut. Ainsi, dans cet exemple, où il y a des hauteurs de tableau variables, la formule renvoie l'erreur # n / a où les réseaux plus petits ont été élargis pour correspondre à la plus grande.

Encore une fois, pour résoudre ce problème, enveloppez la formule HSTACK à l'intérieur de l'IFNA, avec des citations doubles remplaçant les erreurs:

=IFNA(HSTACK(A2:C7,G2:G6),"")

Traitant avec des cellules vierges et des zéros dans Vstack et Hstack

Par défaut, VStack et HSTACK renvoient à la fois Blanks et Zeros dans les données source en tant que Zeros dans le résultat. Bien que cela puisse être idéal dans certains scénarios, cela pourrait devenir déroutant lorsque les cellules vierges et les valeurs nulles signifient des choses différentes dans vos données.

Dans cet exemple, les coyotes ont été disqualifiés, donc leur score de zéro devrait se refléter dans le résultat d'une formule VStack. Cependant, le score des Léopards n'a pas encore été saisi, il faut donc rester vide. Cependant, lorsque la fonction VSTACK est utilisée pour ajouter les données de chaque ligue verticalement, les deux affichent des zéros.

Pour résoudre ce problème, nidine la fonction VSTACK ou HSTACK dans une formule de substitution et remplacez les chaînes vides par des chaînes vides:

=SUBSTITUTE(VSTACK(A2:C6,A9:C13,A16:C20),"","")

Même s'il semble étrange que l'ancien texte et les nouveaux arguments de texte de la fonction de substitution soient des chaînes vides (représentées par des citations doubles), cette solution fonctionne bien. En effet, maintenant, le score des Coyotoes s'affiche correctement comme zéro, tandis que le score des Léopards est correctement affiché comme vide.

Ajout d'en-têtes de colonne aux sorties VStack

Lorsque vous utilisez VStack dans Excel, vous devez toujours ajouter des données sans en-têtes de colonne car, sinon, les en-têtes seront répétés dans le résultat et seront comptés dans le cadre des données pendant le tri et le filtrage.

Cependant, un problème avec l'omission des en-têtes de colonne est que le tableau annexé pourrait être clair. Pour surmonter cela, utilisez une formule pour dupliquer l'en-tête à partir des données source. Ici, tapant:

=A1:C1

Dans la cellule E1, directement au-dessus du résultat d'une formule VStack, reproduit dynamiquement la ligne d'en-tête au-dessus du premier tableau, mettant à jour automatiquement si les en-têtes d'origine sont modifiés.

Vous pouvez utiliser la même approche pour les en-têtes de lignes lorsque vous utilisez HSTACK. Cependant, les ensembles de données bien structurés ont tendance à avoir des champs en tant que colonnes et enregistrements en tant que lignes, donc si vous avez besoin d'en-têtes de ligne, envisagez de transposer vos données et d'utiliser des en-têtes de colonne à la place.

Le même principe s'applique aux tableaux, mais cette fois, vous pouvez utiliser une référence structurée à la ligne d'en-tête plutôt que des références de cellules directes:

=T_LgA(#Headers)

L'avantage de référencer les en-têtes de colonne de table est que si plus de colonnes sont ajoutées, ils sont automatiquement ramassés par la formule contenant une référence structurée à l'en-tête de colonne et la formule VStack. De plus, parce que le résultat de la fonction VSTACK est aussi large que le tableau référencé le plus large, il est prêt à étendre les tables restantes et à saisir vos nouvelles données.

Tri des tableaux vstack annexés

L'un des avantages de la fonction VSTACK est qu'il appelle des données à type pour les types qui étaient auparavant dans des gammes distinctes en un tableau continu, ce qui signifie que vous pouvez effectuer des analyses comparatives plus efficacement.

Mais que se passe-t-il si vous voulez trier le réseau nouvellement annexe? Par exemple, ce résultat serait beaucoup plus précieux si la colonne de score était organisée par ordre décroissant.

Dans Excel, vous ne pouvez pas réorganiser les tableaux dynamiques à l'aide du bouton Filtre, car seule la cellule où vous avez entré la formule est considérée comme active. Toutes les autres cellules ne servent qu'à s'adapter au déversement de la cellule active, de sorte qu'elles n'ont techniquement aucune valeur qui peut être triée.

Au lieu de cela, nidine toute la formule Vstack à l'intérieur de la fonction de tri:

=SORT(VSTACK(T_LgA,T_LgB,T_LgC),3,-1)

où 3 (Le deuxième argument de la fonction de tri) dit à Excel de trier le tableau par la troisième colonne, et -1 (le troisième argument de la fonction de tri) dit à Excel de trier cette colonne dans l'ordre descendant.

Si vous utilisez le tri avec hstack, le deuxième argument de la fonction de tri représente la ligne par laquelle vous souhaitez trier le résultat, le troisième argument est 1 pour l'ordre croissant ou -1 pour l'ordre descendant, et le quatrième argument doit être la valeur booléenne VRAI Alors qu'Excel sait que vous voulez trier horizontalement, pas verticalement.

---

Vstack et Hstack ne sont pas les seules fonctions pour remodeler les données dans Microsoft Excel. Par exemple, les fonctions TOCOL et TOROW vous permettent de convertir un tableau en une seule colonne ou une ligne, les fonctions WrapCols et Wraprows transforment une colonne ou une ligne unidimensionnelle en un tableau bidimensionnel, et la fonction Pivotby vous permet de regrouper et d'agréger vos chiffres sans le besoin d'un pivottable.