Qu'est-ce que la fonction FLATTEN ?
FLATTEN te sert quand tes données sont réparties sur plusieurs colonnes ou plusieurs plages, alors qu'une formule en aval n'accepte qu'une liste à une seule dimension. Elle t'évite de recopier ou de déplacer physiquement les cellules pour les rassembler.
C'est la base de nombreux pipelines de données dans Sheets : consolider des listes dispersées avant de les passer à UNIQUE pour dédoublonner, à FILTRE pour nettoyer, ou à QUERY pour une analyse de type SQL. Une seule formule remplace des dizaines de manipulations manuelles.
Syntaxe
Clique sur un argument pour aller à son explication.
FLATTEN n'existe pas dans Excel. Dans Excel 365, utilise =TOCOL(plage) pour obtenir le même résultat. Pour les versions antérieures, il faut une formule matricielle avec INDEX et LIGNE, ou une macro VBA.
Comprendre chaque paramètre de la fonction FLATTEN
FLATTEN ne réclame qu'une seule plage pour fonctionner : tout le reste est facultatif. Mais dès que tu en ajoutes plusieurs, l'ordre où tu les écris est l'ordre où elles s'empilent dans la colonne finale, alors range-les comme tu veux les lire.
À l'intérieur de chaque plage, le parcours est toujours le même : ligne par ligne, de gauche à droite. Tu ne choisis pas ce sens, mais tu peux le contourner en passant par TRANSPOSE.
plage1
: la première plage ou tableau que tu veux aplatirÇa peut être une plage rectangulaire comme A1:C5, des colonnes entières comme A:C, ou même une seule cellule. C'est le seul paramètre obligatoire.
FLATTEN parcourt cette plage de gauche à droite, ligne par ligne. Pour une plage A1:B3, elle lira A1, B1, puis A2, B2, puis A3, B3 : 6 valeurs dans une colonne unique. L'ordre est toujours prévisible et cohérent.
plage2, plage3, ...
: des plages supplémentaires, séparées par des points-virgules(facultatif)FLATTEN les combine dans l'ordre où tu les as spécifiées. C'est parfait pour consolider des données non adjacentes sans déplacer physiquement les cellules.
Tu peux mélanger des plages de tailles différentes : =FLATTEN(A1:B5; D1:E3; F10:G15) combine trois zones de tailles différentes dans une seule liste. Tu peux aussi référencer d'autres onglets : =FLATTEN(A1:B5; Feuille2!A1:C10).
Exemples pratiques pas à pas
Comment aplatir un tableau croisé en une colonne avec la fonction FLATTEN
Tes ventes te sont arrivées en tableau croisé, les régions en lignes et les trimestres en colonnes. C'est agréable à lire, mais inutilisable dès que tu veux un tableau croisé dynamique ou une statistique d'ensemble, qui réclament tous une valeur par ligne. La fonction FLATTEN déroule la matrice en une seule colonne, sans que tu recopies la moindre cellule.
- 1Dans une cellule, écris
=FLATTEN(. - 2En 1er argument : sélectionne la plage à aplatir,
A2:D4. Elle réunit les12cellules de montants, en-têtes exclus, et c'est le seul argument que la fonction FLATTEN réclame. - 3Ferme la parenthèse et appuie sur Entrée.
=FLATTEN(A2:D4)125 000, 138 000, 142 000, 156 000), puis la deuxième, puis la troisième. Les 12 cellules de la matrice ressortent dans une colonne unique, et aucune ne se perd en route.
Comment lire un tableau colonne par colonne avec les fonctions FLATTEN et TRANSPOSE
Tu veux comparer les trimestres entre eux, mais l'aplatissement par défaut mélange les cartes : il sort les quatre trimestres de la première région, puis ceux de la deuxième. Il te faudrait l'inverse, les trois régions d'un même trimestre à la suite, pour lire l'évolution d'un coup d'œil. En faisant pivoter la matrice avant de l'aplatir, tu obtiens exactement cet ordre.
- 1Dans une cellule, écris
=FLATTEN(. - 2En 1er argument : écris la plage à retourner avec la fonction TRANSPOSE et referme-la,
TRANSPOSE(A2:D4). On la fait pivoter avant de l'aplatir parce que la fonction FLATTEN lit toujours ligne par ligne, et c'est ce quart de tour qui regroupe enfin les régions par trimestre. - 3Ferme la parenthèse de la fonction FLATTEN et appuie sur Entrée. Celle de la fonction TRANSPOSE a déjà été refermée à l'étape précédente.
=FLATTEN(TRANSPOSE(A2:D4))
Comment fusionner plusieurs colonnes sans doublon avec les fonctions FLATTEN et UNIQUE
Tu as collecté des adresses sur trois événements, chacune dans sa colonne, et les fidèles se sont inscrits à plusieurs. Avant d'envoyer ta newsletter, tu veux une liste sans doublon, sinon ce sont justement tes meilleurs contacts qui recevront le message en double. La fonction FLATTEN rassemble les trois colonnes en une liste que la fonction UNIQUE nettoie dans la foulée.
- 1Dans une cellule, écris
=UNIQUE(. - 2En 1er argument : écris la liste à dédoublonner avec la fonction FLATTEN et referme-la,
FLATTEN(A2:C5). Tes adresses sont réparties sur trois colonnes, mais la fonction UNIQUE ne sait dédoublonner qu'une seule liste, alors la fonction FLATTEN rassemble d'abord les12cellules en une colonne exploitable. - 3Ferme la parenthèse de la fonction UNIQUE et appuie sur Entrée. Celle de la fonction FLATTEN a déjà été refermée à l'étape précédente.
=UNIQUE(FLATTEN(A2:C5))12 adresses des trois colonnes en une seule liste, puis la fonction UNIQUE la parcourt et ne retient que la première apparition de chaque valeur. alice@mail.com et bob@mail.com revenaient deux fois : il reste 10 adresses, prêtes pour l'envoi.
Les erreurs fréquentes avec la fonction FLATTEN
Avec FLATTEN, les ennuis viennent rarement de la formule elle-même : c'est souvent le contexte autour. Un #NOM? signifie tout simplement que tu es dans Excel, où FLATTEN n'existe pas. Les autres surprises tiennent à ce que FLATTEN ne nettoie rien : elle recopie les cellules vides telles quelles et garde les nombres déguisés en texte, ce qui fait planter tes SOMME.
Deux pièges plus subtils complètent le tableau : l'ordre ligne par ligne qui déroute quand on attendait du colonne par colonne, et le ralentissement quand tu répètes FLATTEN(...) deux fois dans la même formule.
Erreur #NOM? : FLATTEN non reconnue dans Excel
FLATTEN est exclusivement disponible dans Google Sheets. Si tu l'utilises dans Excel, #NOM? s'affiche car Excel ne connaît pas cette fonction.
Solution : Dans Excel 365, utilise =TOCOL(plage) pour obtenir le même résultat. Pour les versions antérieures (Excel 2019 et avant), tu devras utiliser une formule matricielle avec INDEX et LIGNE, ou créer une macro VBA.
Cellules vides incluses dans le résultat
Par défaut, FLATTEN conserve toutes les cellules vides de la plage source. Si ton tableau a des trous, tu te retrouves avec des lignes vides dans le résultat, ce qui perturbe les analyses statistiques ou les imports.
Solution : Enveloppe FLATTEN dans FILTRE pour exclure les vides : =FILTRE(FLATTEN(A1:C10); FLATTEN(A1:C10)<>""). Attention : FLATTEN apparaît deux fois car FILTRE a besoin de la plage à filtrer et de la condition sur cette même plage.
Résultat en format texte au lieu de nombres
Si tes cellules sources contiennent des nombres formatés en texte (import CSV, copier-coller depuis PDF, saisie avec apostrophe), FLATTEN les conserve en texte. Les calculs comme SOMME retourneront alors 0.
Solution : Multiplie par 1 pour forcer la conversion : =FLATTEN(A1:C10)*1. Pour une conversion plus explicite : =ARRAYFORMULA(VALEUR(FLATTEN(A1:C10))). Vérifie aussi l'alignement des cellules : les nombres sont alignés à droite, le texte à gauche.
Ordre des valeurs inattendu
FLATTEN lit ligne par ligne (de gauche à droite, puis passe à la ligne suivante). Certains utilisateurs s'attendent à une lecture colonne par colonne.
Solution : Pour lire colonne par colonne, applique d'abord TRANSPOSE : =FLATTEN(TRANSPOSE(A1:C5)). TRANSPOSE convertit les colonnes en lignes, donc FLATTEN lira ensuite dans l'ordre colonne par colonne. Pour un ordre alphabétique, enchaîne TRIER : =TRIER(FLATTEN(A1:C5)).
Ralentissement sur de grandes plages avec FLATTEN répété
Dans une formule comme =FILTRE(FLATTEN(A1:C1000); FLATTEN(A1:C1000)<>""), FLATTEN est calculée deux fois. Sur de grandes plages, cela peut provoquer des délais de recalcul importants.
Solution : Crée une cellule intermédiaire avec le résultat de =FLATTEN(A1:C1000), puis référence cette cellule dans tes autres formules. Tu évites le double calcul et les performances s'améliorent significativement.
Astuces avancées avec FLATTEN
Exclure les vides avec FILTRE pour des listes propres
Quand ton tableau source a des cellules vides dispersées, FLATTEN les inclut dans le résultat. La combinaison =FILTRE(FLATTEN(A1:C10); FLATTEN(A1:C10)<>"") te donne uniquement les valeurs non vides, dans leur ordre d'origine.
Pour les grandes plages, stocke d'abord =FLATTEN(A1:C10) dans une colonne intermédiaire, puis filtre dessus : les performances sont bien meilleures.
Compter les occurrences de chaque valeur dans un tableau multi-colonnes
Pour savoir combien de fois chaque produit ou catégorie apparaît dans un tableau réparti sur plusieurs colonnes, aplatis d'abord avec FLATTEN puis utilise NB.SI : =NB.SI(FLATTEN(A1:E10); "Produit A") te donne directement le comptage total.
Pour automatiser sur toutes les valeurs uniques, combine avec UNIQUE : =ARRAYFORMULA(NB.SI(FLATTEN(A1:E10); UNIQUE(FLATTEN(A1:E10)))) retourne le décompte de chaque valeur en une seule formule.
Créer une liste déroulante dynamique à partir d'un tableau multi-colonnes
Si tes valeurs de référence (produits, catégories, tags) sont réparties sur plusieurs colonnes, tu peux alimenter une liste déroulante dynamique : Données, Validation des données, Liste d'une plage, et entre =UNIQUE(FILTRE(FLATTEN(A1:D10); FLATTEN(A1:D10)<>"")). La liste se met à jour automatiquement quand tu modifies le tableau source.
Elle ne contiendra que des valeurs uniques et non vides, sans aucune maintenance manuelle.
FLATTEN vs TRANSPOSE vs TOCOL vs EMPILAGE.V
Le premier filtre, c'est ton outil : FLATTEN est réservée à Google Sheets, tandis que TOCOL et EMPILAGE.V n'existent que dans Excel 365. TRANSPOSE, elle, tourne dans les deux.
Ensuite, c'est une question de but. Pour fondre plusieurs plages en une seule colonne, FLATTEN gagne car elle en accepte autant que tu veux. Si tu veux juste faire pivoter un tableau sans tout aplatir, c'est TRANSPOSE. Et pour empiler des tableaux en gardant leurs colonnes intactes, vise EMPILAGE.V.
| Critère | FLATTEN | TRANSPOSE | TOCOL | EMPILAGE.V |
|---|---|---|---|---|
| Plateforme | Google Sheets uniquement | Excel + Google Sheets | Excel 365 uniquement | Excel 365 uniquement |
| Résultat | Une seule colonne | Inverse lignes et colonnes | Une seule colonne | Empile les plages (colonnes conservées) |
| Plusieurs plages | Oui, illimitées | Non, une seule | Non, une seule | Oui, plusieurs |
| Gestion des vides | Les conserve (FILTRE pour exclure) | Les conserve | Option native pour ignorer | Les conserve |
| Cas d'usage principal | Consolidation en liste | Pivoter un tableau | Aplatir une plage dans Excel | Empiler des tableaux verticalement |
Questions fréquentes
Non, FLATTEN est spécifique à Google Sheets. Dans Excel 365, l'équivalent direct est TOCOL qui aplatit un tableau en colonne. Pour les versions antérieures (Excel 2019 et avant), il faut une formule matricielle combinant INDEX et LIGNE, ou une macro VBA.
Par défaut, FLATTEN conserve les cellules vides dans le résultat. Pour les exclure, combine avec FILTRE : =FILTRE(FLATTEN(plage); FLATTEN(plage)<>""). Cela te donne une liste propre sans trous, parfaite pour les analyses ou les exports.
Pour les grandes plages, stocke le résultat de FLATTEN dans une colonne intermédiaire avant de filtrer, afin d'éviter que la formule soit calculée deux fois.
Oui, FLATTEN accepte plusieurs arguments séparés par des points-virgules : =FLATTEN(A1:B5; D1:E3; F10:G15). La fonction combine toutes les plages dans l'ordre spécifié, ce qui est parfait pour consolider des données dispersées sans les déplacer physiquement.
Tu peux même mélanger des plages de tailles différentes et référencer d'autres onglets : =FLATTEN(A1:B5; Feuille2!A1:C10).
Oui, FLATTEN lit les données ligne par ligne, de gauche à droite, puis passe à la ligne suivante. Pour un tableau 2×3, elle lira A1, B1, C1, puis A2, B2, C2. Cet ordre est prévisible et cohérent.
Si tu veux un ordre colonne par colonne (de haut en bas), applique TRANSPOSE avant : =FLATTEN(TRANSPOSE(A1:C5)). TRANSPOSE intervertit lignes et colonnes, donc FLATTEN lira ensuite dans l'ordre vertical.
FLATTEN transforme tout en une seule colonne verticale, en effaçant la structure bidimensionnelle du tableau. TRANSPOSE, elle, inverse simplement les lignes et colonnes tout en conservant la structure du tableau (les lignes deviennent des colonnes et vice-versa).
Utilise FLATTEN pour consolider des données en une liste unique, et TRANSPOSE pour pivoter un tableau. Tu peux même les combiner : =FLATTEN(TRANSPOSE(A1:C5)) aplatit le tableau en lisant colonne par colonne.
Pour aller plus loin
Continue sur ta lancée après la fonction FLATTEN : la leçon associée, un modèle prêt à l'emploi et le guide pour progresser.
Tout voir
