Fonctionnant avec les fonctions VBA qui retournent une matrice dans Excel 2016 - les formules matricielles

Les formules matricielles sont l'une des fonctionnalités les plus puissantes d'Excel. Si vous êtes familier avec les formules matricielles, vous serez heureux de savoir que vous pouvez créer des fonctions VBA qui retournent un tableau.

Renvoyer un tableau de noms de mois

Commençons par un exemple simple. La fonction MonthNames renvoie un tableau de 12 éléments - vous l'avez deviné - des noms de mois.

Fonction MonthNames () MonthNames = Tableau ("Janvier", "Février", "Mars", _ "Avril", "Mai", "Juin", "Juillet", "Août", _ "Septembre", " October "," November "," December ") Fonction de fin

Pour utiliser la fonction MonthNames dans une feuille de calcul, vous devez l'entrer comme une formule de tableau à 12 cellules. Par exemple, sélectionnez la plage A2: L2 et entrez = MonthNames () . Ensuite, appuyez sur Ctrl + Maj + Entrée pour entrer la formule matricielle dans les 12 cellules sélectionnées. Découvrez le résultat.

Utiliser la fonction MonthNames pour retourner un tableau de 12 éléments.

Si vous souhaitez afficher les noms des mois dans une colonne, sélectionnez 12 cellules dans une colonne et utilisez cette formule matricielle. (N'oubliez pas de l'entrer en appuyant sur Ctrl + Maj + Entrée.)

= TRANSPOSE (MonthNames ())

Vous pouvez également sélectionner un mois dans le tableau. Voici une formule (pas une formule matricielle) qui affiche le quatrième élément du tableau: April.

= INDEX (MonthNames (), 4)

Renvoyer une liste triée

Supposons que vous ayez une liste de noms que vous souhaitez afficher dans un ordre différent dans une autre plage de cellules. Ne serait-il pas agréable d'avoir une fonction de feuille de calcul pour vous?

Cette fonction personnalisée ne fait que cela: elle prend comme argument une plage de cellules à une seule colonne, puis renvoie un tableau de ces cellules triées. Plage A2: A13 contient certains noms. Plage C2: C13 contient cette formule de matrice multicellulaire. (N'oubliez pas que vous devez entrer la formule en appuyant sur Ctrl + Maj + Entrée.)

Utilisation d'une fonction personnalisée pour renvoyer une plage triée.

= Trié (A2: A13)

Voici le code de la fonction Sorted:

Fonction triée (Rng As Range) Dim SortedData () As Variant Dim Cellule As Plage Dim Temp As Variant, i As Long, j Comme long Dim NonEmpty As Long 'Transférer des données à SortedData pour chaque cellule en Rng si n'est pas IsEmpty (Cell) Then NonEmpty = NonEmpty + 1 ReDim Preserve SortedData (1 à NonEmpty) SortedData (NonEmpty) = Cell. Fin de la valeur si la cellule suivante 'Trie le tableau Pour i = 1 Pour NonEmpty Pour j = i + 1 Pour NonEmpty Si SortedData (i)> SortedData (j) Puis Temp = SortedData (j) SortedData (j) = SortedData (i) SortedData (i) = Temp End Si Next j Next i 'Transpose le tableau et le renvoie Sorted = Application.Transpose (SortedData) End Function

La fonction Sorted démarre en créant un tableau nommé SortedData. Ce tableau contient toutes les valeurs non vides dans la plage d'arguments. Ensuite, le tableau SortedData est trié, en utilisant un algorithme de tri par bulles. Comme le tableau est un tableau horizontal, il doit être transposé avant d'être renvoyé par la fonction.

La fonction triée fonctionne avec une plage de n'importe quelle taille, tant qu'elle se trouve dans une seule colonne ou rangée. Si les données non triées sont dans une rangée, votre formule doit utiliser la fonction TRANSPOSE d'Excel pour afficher les données triées horizontalement. Par exemple:

= TRANSPOSE (trié (A16: L16))