Union de tables/matrices
Fonctions ASSEMB.V/ASSEMB.H
Rôle : Consolider/combiner/empiler des tables/matrices verticalement ou horizontalement.
Disponibilité
- Windows : 2208 (build 15601)
- Mac : 16.65 (build 220911)
- Web : introduit le 15 septembre 2022
- iOS : 2.65 (build 220905)
- Android : 16.0.15629
Syntaxe
=ASSEMB.V(Matrice1 [; Matrice2][;...Matrice254])
=ASSEMB.H(Matrice1 [; Matrice2][;...Matrice254])Matrice1 à 254 : Tables / matrices à associées (Acceptent les références 3D), valeurs scalaires (Numérique, alphanumérique, booléen).
La matrice résultante correspondra à la matrice de plus grandes dimensions pour la dimension non empilée (pour que toutes les valeurs « tiennent »). Les éléments manquants des matrices plus petites seront remplacés par #N/A.
Exemple 1
=ASSEMB.H("aa";10;VRAI;C10;SEQUENCE(;3);B4:C4)
Exemples 2 & 3
Formule 2 :
- La matrice la plus large est celle à 3 colonnes (matrice bleu).
- On va empiler avec elle 2 matrices verticalement, la matrice verte de 2 colonnes et une constante matricielle de 2 colonnes).
- Excel complète les éléments manquants de ces 2 dernières matrices avec des #N/A.
Formule 3 :
- On masque les #N/A avec la fonction SIERREUR.
Exemple 4
Objectif : On veut consolider les résultats de deux filtres (Sexe="F") appliqués sur deux bases de données (bleu et verte).
=ASSEMB.V(FILTRE(A2:C5;C2:C5="F");FILTRE(E2:G4;G2:G4="F"))Pourquoi ne pas simplement positionner les fonctions FILTRE l'une au-dessus de l'autre ?
Si le nombre de réponse du 1er filtre (du haut) change, on aura alors des lignes vides (s’il y a moins de réponse) ou l’erreur #PROPAGATION ! (s’il y a plus de réponse car butant sur la 2eme fonction FILTRE).
Solution de remplacement pour ASSEMB.H
=CHOISIR({1.2};B2:B4;D2:D4) <=> =ASSEMB.H(B2:B4;D2:D4)Utilisation en VBA
N'est pas présent dans WorksheetFunction (pour l'instant). On utilise Evaluate.
Application.Evaluate("VSTACK(pA,pB)") ' Tableau 2D
Application.Evaluate("HSTACK(pA,pB)") ' Tableau 2DTransformation de la table/matrice
Passage d'une table/matrice 2D en 1D (DANSLIGNE/DANSCOL)
Objectif : Passer une table/matrice ayant une dimension Ligne et une dimension colonne (2D) en une Table/matrice n’ayant plus qu’une dimension soit ligne ou soit colonne (1D).
Disponibilité
- Windows : 2208 (build 15601)
- Mac : 16.65 (build 220911)
- Web : introduit le 15 septembre 2022
- iOS : 2.65 (build 220905)
- Android : 16.0.15629
Syntaxe
=DANSLIGNE (Array [; Ignore] [; Scan_by_column])
=DANSCOL (Array [; Ignore] [; Scan_by_column])Array : La table / matrice (Accepte les références 3D).
Ignore : Choix des valeurs à conserver ou ignorer
- [0] => On garde les valeurs vides (remplacer par 0) et les valeurs d’erreurs.
- 1 => Les valeurs vides sont ignorées (non récupérées).
- 2 => Les valeurs d’erreurs sont ignorées (non récupérées).
- 3 => Les valeurs vides et d’erreurs sont ignorées (non récupérées).
Scan_by_column : Au lieu de d’extraire les valeurs ligne par ligne, le faire colonne par colonne.
Remarque :
Superbe traduction de Microsoft de TOROW/TOCOL qui devient DANSLIGNE*DANSCOL.
Perso j’aurais pris ENLIGNE/ENCOL 😊.
Exemples 1, 2 & 3
Exemple 4
Objectif : Dépivoter un tableau pour l’exploiter comme base de données d’un TCD par exemple (Cas classique).
Tableau à dépivoter
Résultat
=ASSEMB.V({"Produits"."Mois"."Qt"};ASSEMB.H(INDEX(A2:A4;DANSCOL(SEQUENCE(3)*1/(B2:D4<>"");2));INDEX(B1:D1;DANSCOL(SEQUENCE(;3)*1/(B2:D4<>"");2));DANSCOL(B2:D4;1)))Le résultat est constitué de 4 matrices empilées :
- Les titres de colonnes :
{"Produits"."Mois"."Qt"}
- La liste des produits :
INDEX(A2:A4;DANSCOL(SEQUENCE(3)*1/(B2:D4<>"");2))
>>>Matrice des positions des lettres produits correspondantes :
SEQUENCE(3)*1/(B2:D4<>"")
- La liste des mois :
INDEX(A2:A4;DANSCOL(SEQUENCE(;3)*1/(B2:D4<>"");2))
>>>Matrice des positions des noms des mois correspondantes :
SEQUENCE(;3)*1/(B2:D4<>"")
Pourquoi le 1/ ?
INDEX(A2:A4;DANSCOL(SEQUENCE(3)*(B2:D4<>"");2)) renvoie 0 pour les cellules vides et INDEX renvoie alors la première valeur de la liste ! On a utilisé l'inversion 1/ pour transformer les FAUX (0) de B2:D4 <> "" en #DIV/0 ! ignorés via l'argument Ignore de DANSCOL.
- La liste des Qt : DANSCOL(B2:D4;1)
Utilisation en VBA
N'est pas présent dans WorksheetFunction (pour l'instant). On utilise Evaluate.
Application.Evaluate("TOCOL(pA)") ' Tableau 2D
Application.Evaluate("TOROW(pA)") ' Tableau 2DPassage d'une table/matrice 1D en 2D (ORGA.LIGNES/ORGA.COLS)
Rôle : Faire l’inverse des précédentes fonctions DANSLIGNE/DANSCOL.
>Avec ORGA.LIGNES, on remplit la nouvelle matrice ligne/ligne.
>Avec ORGA.COLS, on remplit la nouvelle matrice colonne par colonne.
Disponibilité
- Windows : 2208 (build 15601)
- Mac : 16.65 (build 220911)
- Web : introduit le 15-Sep-2022
- iOS : 2.65 (build 220905)
- Android : 16.0.15629
Syntaxe
=ORGA.LIGNES (vector ; Wrap_count [; Pad_with])
=ORGA.COLS (vector ; Wrap_count [; Pad_with])Vector : Vecteur en français, une matrice n’ayant qu’une dimension (une ligne de valeurs ou une colonne de valeurs) sinon on lève une erreur.
Wrap_count : Nombre de valeurs pour la nouvelle dimension (pour ORGA.LIGNES = Nbre de colonne, pour ORGA.COLS = Nbre de lignes).
Pad_with : Avec quoi remplir les éléments vides supplémentaires de la matrice à la place du #N/A par défaut.
Exemple 1
- Les valeurs vides sont converties en 0.
- Les 6 valeurs laissent 2 éléments vides (en vert) dans la matrice 4×2.
Exemple 2
Objectif : Distribuer uniformément une valeur de départ (Cellule verte E1) sur une semaine entre 2 groupes A et B par paquet de 15.
=ORGA.LIGNES(ASSEMB.V(SEQUENCE(ENT(E1)/15;;;0)*15;ARRONDI.SUP(MOD(E1;15)/2;0);ARRONDI.INF(MOD(E1;15)/2;0));2;"")On groupe 3 matrices.
- Matrice 1 : Les paquets entiers de 15.
L’argument 0 (pas) de SEQUENCE permet d’obtenir une matrice de 1 transformée en une matrice de 15 avec *15. - Matrice 2 et 3 : On répartit le reste entre les 2 groupes A et B.
Les fonctions d’arrondis sont là pour obtenir des nombres entiers et gérer le cas où le reste est une valeur impaire.
Pourquoi faire 2 fonctions et non une seule détectant le sens de l'argument Vector ?
Pour permettre de choisir le sens de remplissage des données.
Exemple : D’une matrice verticale, obtenir une matrice 2 lignes x 3 colonnes.
Matrice de départ verticale
ORGA.COLS : Wrap_count = 2 (lignes)
ORGA.LIGNES : Wrap_count = 3 (colonnes)
Utilisation en VBA
N'est pas présent dans WorksheetFunction (pour l'instant). On utilise Evaluate.
Application.Evaluate("WRAPCOLS(pA,2,0)") ' Tableau 2D
Application.Evaluate("WRAPROWS(pA,3,0)") ' Tableau 2DDiminuer la taille de la matrice
Fonctions PRENDRE et EXCLURE
Rôle : Renvoyer une matrice à partir d’une autre matrice dont on a sélectionné (PRENDRE)/ignoré (EXCLURE) les n premières/dernières lignes/colonnes.
Disponibilité
- Windows : 2208 (build 15601)
- Mac : 16.65 (build 220911)
- Web : introduit le 15 septembre 2022
- iOS : 2.65 (build 220905)
- Android : 16.0.15629
Syntaxe
=PRENDRE(array ; [rows] [; columns])
=EXCLURE(array ; [rows] [; columns])Array : Matrice de départ.
Rows : Nombre de lignes à prendre/exclure (Positif : A partir du début, Négatif : A partir de la fin, Non renseigné : toutes).
Columns : Nombre de colonnes à prendre/exclure (Positif : A partir du début, Négatif : A partir de la fin, Non renseigné : toutes).
Si Rows/Columns sont supérieurs au nombre de lignes/colonnes de la matrice de départ, c’est toutes les lignes/colonnes de la matrice de départ qui sont utilisées.
Exemple
Afficher le Top3 des étudiants (sans tenir compte des ex aequo).
=PRENDRE(TRIER(FILTRE(B2:C6;C2:C6>=GRANDE.VALEUR(C2:C6;3));2;-1);3)La fonction FILTRE renvoie 4 réponses du fait de l’égalité entre Luc et Chantal (8).
PRENDRE ne gardera que les 3 premières réponses (le tri décroissant sur Notes est obligatoire car sinon on risquerait de supprimer Marie ou Paul avec le PRENDRE).
Utilisation en VBA
N'est pas présent dans WorksheetFunction (pour l'instant). On utilise Evaluate.
Application.Evaluate("TAKE(pA,2)") ' Tableau 2D
Application.Evaluate("DROP(pA,1)") ' Tableau 2DFonctions CHOISIRLIGNES et CHOISIRCOLS
Rôle : Renvoyer une matrice correspondant à une sélection de lignes/colonnes d’une matrice à partir de leurs positions.
Disponibilité
- Windows : 2208 (build 15601)
- Mac : 16.65 (build 220911)
- Web : introduit le 15-Sep-2022
- iOS : 2.65 (build 220905)
- Android : 16.0.15629
Syntaxe
CHOISIRLIGNES (array ; row_num1 [; row_num2]…[; row_num253])
CHOISIRCOLS (array ; row_num1 [; row_num2]…[; row_num253])Array : Matrice de départ.
Row_num : Postions des éléments à prendre.
Exemple
=CHOISIRLIGNES(B1:D10;{1;3};5;1)De la plage/matrice B1:B10, on sélectionne les lignes 1, 3, 5 et à nouveau 1.
- On peut utiliser une constante matricielle.
- On obtient une matrice de 4 lignes (et 3 colonnes) avec la ligne 1 en double en 1er et 4ème position.
On obtient #VALUE si la valeur absolue d’un des arguments row_num est nulle ou dépasse le nombre de lignes/colonnes de Array.
Utilisation en VBA
N'est pas présent dans WorksheetFunction (pour l'instant). On utilise Evaluate.
Application.Evaluate("CHOOSECOLS(F37:H38,1,3)") ' Tableau 2D
Application.Evaluate("CHOOSEROWS(pA,2,4)") ' Tableau 2DAugmenter la taille de la matrice
Fonction ETENDRE
Rôle : Agrandir une matrice sur une ou deux dimensions (ligne/colonne).
Disponibilité
- Windows : 2208 (build 15601)
- Mac : 16.65 (build 220911)
- Web : introduit le 15 septembre 2022
- iOS : 2.65 (build 220905)
- Android : 16.0.15629
Syntaxe
=ETENDRE(array ; [rows] [ ; columns] [ ; pad_with])Array : Matrice à étendre.
Rows : Nombre de lignes de la matrice finale (>= à celle de départ, si manquante correspondra à la valeur correspondante de Array.
Columns : Nombre de colonne de la matrice finale (>= à celle de départ, si manquante correspondra à la valeur correspondante de Array.
Pad_with : Valeur unique de remplissage pour les nouvelles lignes/colonnes.
Exemple
Objectif : Consolider/combiner/empiler 2 tables/matrices verticalement n’ayant pas la même taille.
=ASSEMB.V(B2:C5;ETENDRE(B8:B9;;2;"Manquante"))ASSEMB.V (tout comme ASSEMB.H) va se baser sur la plus grande matrice et ajouter des #N/A aux valeurs manquantes. Pour contrôler cela vu qu’elle ne possède pas d’argument Pad_with, on utilise celui de la fonction ETENDRE.
Utilisation en VBA
N'est pas présent dans WorksheetFunction (pour l'instant). On utilise Evaluate.
Application.Evaluate("EXPAND(B2:B4,4,,100)") ' Tableau 2DMerci pour votre attention bienveillante.
