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)Transformation 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)
Passage 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)
Diminuer 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).
Fonctions 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.
Augmenter 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.
Merci pour votre attention bienveillante.
