Formule dynamique : Diviser une table en plusieurs tables dans Google Sheets

Avec l’aide d’une formule dynamique, il est possible de diviser les plages de résultats de tableau ou de formule en plusieurs tables dans Google Sheets. L’utilisateur doit spécifier la taille des lignes des tables.

Par exemple, si vous spécifiez une taille de ligne de 15 et que le tableau contient 50 lignes, vous obtiendrez 4 tables, les trois premières contenant chacune 15 lignes et la dernière contenant 5 lignes.

Nous utiliserons la fonction lambda REDUCE pour cette opération. Cependant, il s’agit d’une fonction gourmande en ressources. Si vous rencontrez des problèmes, je peux vous proposer une autre option plus simple, bien qu’elle nécessite l’utilisation de plusieurs formules – une formule par table.

Diviser un tableau en plusieurs tables : pourquoi est-ce utile?

Lorsque vous divisez un tableau en plusieurs tables, la formule positionne les tables côte à côte en laissant une colonne vide entre elles.

Cela est avantageux lors de l’impression de tableaux, car cela réduit le nombre de pages nécessaires pour l’ensemble du tableau. Cependant, cela peut ne pas être applicable si votre tableau comporte de nombreuses colonnes.

De plus, cela facilite la navigation. La division des tableaux en sections plus petites simplifie le processus de recherche de données spécifiques et permet de se concentrer sur les informations pertinentes.

Prérequis

La formule qui divise dynamiquement un tableau en plusieurs tables nécessite une plage fermée lors de l’utilisation de données physiques pour la division, et non une plage ouverte.

Par exemple, vous pouvez spécifier A2:C100, et non seulement A2:C, dans la formule. Si vous avez un tableau en expansion, envisagez d’utiliser la plage comme suit: FILTER(A2:C, A2:A<> » »), car cela permettra d’accommoder les plages futures.

Excluez la ligne d’en-tête du tableau. Si votre tableau se trouve dans A1:F50 et que A1:F1 contient des étiquettes de champ, utilisez la plage A2:F50 ou FILTER(A2:F, A2:A<> » »), en excluant la ligne d’en-tête.

Comment diviser un tableau en plusieurs tables dans Google Sheets

Voici une formule dynamique pour diviser un tableau en plusieurs tables dans Google Sheets.

=LET(data, plage, n, lignes, taille, LIGNES(CHOOSECOLS(data, 1)), base, SEQUENCE(1, ROUNDUP(taille/n), 1, n), en-tête, HSTACK("étiquette1", "étiquette2, ..."), REDUCE(TOROW(, 1), base, LAMBDA(a, v, IFERROR( HSTACK(a, VSTACK( en-tête, FILTER(data, ISBETWEEN(SEQUENCE(taille), v, v+n, TRUE, FALSE)) ), ) ))) )

Apportez les ajustements suivants à la formule pour l’adapter à votre tableau:

1. Remplacez « plage » par la plage réelle du tableau, par exemple, A2:C100 ou FILTER(A2:C, A2:A<> » »).
2. Remplacez « lignes » par le nombre souhaité de lignes pour chaque tableau divisé, par exemple, 10.
3. Modifiez également les en-têtes dans la fonction HSTACK en fonction de la structure de votre tableau. Si votre tableau comporte quatre colonnes, ajustez-le comme HSTACK(« étiquette1 », « étiquette2 », « étiquette3 », « étiquette4 »). Remplacez les textes de substitution par les noms de champ réels.

Exemple de division d’un tableau en plusieurs tables à l’aide d’une formule dynamique

Nous avons une formule dynamique et des instructions pour diviser un tableau en n tables dans Google Sheets. Testons-la avec un petit jeu de données d’exemple contenant trois colonnes et 17 lignes. La plage du tableau est A2:C18.

Nous allons diviser ce tableau en plusieurs tables, chaque table contenant 8 lignes. Par conséquent, il y aura trois tables, les deux premières contenant chacune 8 lignes et la dernière contenant 1 ligne. De plus, ces tables auront une ligne d’en-tête supplémentaire.

Formule:

=LET(data, A2:C18, n, 8, taille, LIGNES(CHOOSECOLS(data, 1)), base, SEQUENCE(1, ROUNDUP(taille/n), 1, n), en-tête, HSTACK("Date", "W.B. No.", "Qty."), REDUCE(TOROW(, 1), base, LAMBDA(a, v, IFERROR( HSTACK(a, VSTACK( en-tête, FILTER(data, ISBETWEEN(SEQUENCE(taille), v, v+n, TRUE, FALSE)) ), ) ))) )

Notes:

1. Si votre source contient une colonne de date ou d’horodatage, la table résultante peut contenir des valeurs de date dans les colonnes correspondantes. Pour résoudre ce problème, sélectionnez ces colonnes et appliquez le format de date. Pour ce faire, une fois sélectionnées, cliquez sur Format > Nombre > Date ou Date et heure.
2. Toutes les modifications apportées au tableau source se refléteront dans les résultats.
3. Vous avez la possibilité de remplacer les plages physiques par des sorties de formules, telles que QUERY, FILTER, SORT, etc. Pour cela, remplacez la plage par la formule elle-même. Dans l’exemple ci-dessus, vous pouvez remplacer A2:C18 par une QUERY ou d’autres formules qui renvoient un résultat de tableau. Assurez-vous que les résultats de la formule ne contiennent pas d’en-tête.

Explication de la formule

Il n’est pas nécessaire de comprendre en détail la formule pour l’utiliser pour diviser un tableau en plusieurs tables de taille égale. La formule implique une fonction lambda, ce qui peut être un peu difficile à comprendre pour les nouveaux utilisateurs.

Cependant, si vous avez quelques connaissances de base sur les fonctions lambda, en particulier REDUCE, vous trouverez probablement intéressante l’explication détaillée de la formule ci-dessous.

La fonction LET est utilisée pour coder la formule, en attribuant des noms aux expressions de valeur et en renvoyant le résultat de l’expression de la formule.

Syntaxe:

LET(nom1, expression_valeur1, [nom2, …], [expression_valeur2, ...], expression_formule)

Expressions de valeur et noms:
Il y a cinq expressions de valeur avec des noms attribués dans la formule. Les voici :

1. A2:C18 : Représente le tableau source, et « data » est le nom attribué.

2. 8 : Représente le nombre de lignes dans chaque table, et « n » est le nom attribué.

3. LIGNES(CHOOSECOLS(data, 1)) : Renvoie le nombre total de lignes dans le tableau source, avec le nom attribué « taille ».

4. SEQUENCE(1, ROUNDUP(taille/n), 1, n) : Génère un tableau, auquel est attribué le nom « base ». Il joue un rôle clé dans l’expression de la formule. Permettez-moi d’expliquer ce qu’il renvoie et à quoi il sert.

   • Dans l’exemple donné, avec des valeurs réelles, « base » sera SEQUENCE(1, ROUNDUP(17/8), 1, 8), ce qui donne le tableau {1, 9, 17}. Ces nombres représentent les lignes correspondantes dans le tableau source, servant de premières lignes dans les nouvelles tables.
   • ROUNDUP(17/8) équivaut à 3, ce qui indique qu’il y aura trois tableaux après la division, où 17 est le nombre total de lignes dans le tableau source et 8 est le nombre de lignes requis dans chaque tableau divisé.
   • La fonction SEQUENCE génère 3 nombres à partir de 1, avec une étape de 8. Ainsi, le résultat sera {1, 9, 17}.

5. HSTACK(« Date », « W.B. No. », « Qty. ») : Représente les en-têtes de la table divisée, et « en-tête » est le nom attribué.

Expression de formule:

REDUCE(TOROW(, 1), base, LAMBDA(a, v, IFERROR( HSTACK(a, VSTACK( en-tête, FILTER(data, ISBETWEEN(SEQUENCE(taille), v, v+n, TRUE, FALSE)) ), ) )))

Explication:

• La fonction REDUCE fonctionne avec une valeur initiale et un tableau, en appliquant une fonction lambda à chaque élément et en accumulant les résultats.
• La valeur initiale est définie comme TOROW(, 1), ce qui est équivalent à TOROW(A1, 1) en considérant que A1 est vide. Utiliser 1 dans le deuxième argument de la fonction TOROW assure la compatibilité avec les cellules vides potentielles, empêchant le retour de blancs.
• Le tableau spécifié est « base », contenant {1, 9, 17}. Ce tableau détermine les valeurs sur lesquelles la fonction REDUCE itère, en appliquant la fonction lambda spécifiée à chaque élément de « base ».
• La fonction lambda prend deux paramètres : « a » représentant l’accumulateur, initialement défini comme TOROW(, 1), et « v » représentant l’élément actuel dans le tableau.
• Le cœur de la formule réside dans la fonction lambda, où les fonctions HSTACK et VSTACK sont utilisées pour empiler horizontalement et verticalement des tableaux, respectivement.
• La formule FILTER, c’est-à-dire FILTER(data, ISBETWEEN(SEQUENCE(taille), v, v+n, TRUE, FALSE)), filtre les données source en fonction des numéros de ligne entre « v » (inclus) et « v+n » (exclus), comme décrit dans la référence à la fonction ISBETWEEN.
• Par exemple, lorsque « v » est 1 et « v+n » est 9, la formule filtre les enregistrements dans le tableau des lignes 1 à 8. Ces données filtrées sont ensuite empilées verticalement avec la ligne d’en-tête à l’aide de VSTACK.
• La fonction HSTACK combine la valeur actuelle de l’accumulateur « a » avec les données empilées et introduit une cellule vide :

  HSTACK(a, VSTACK(en-tête, FILTER(data, ISBETWEEN(SEQUENCE(taille), v, v+n, TRUE, FALSE))), )

• La fonction REDUCE itère à travers le tableau « base », appliquant la fonction lambda à chaque fois. Le résultat final est la concaténation des tableaux filtrés et empilés, avec les erreurs #N/A supprimées à l’aide de IFERROR.
• Par exemple, avec ‘base’ {1, 9, 17}, il filtre les données de la rangée 1 à 8, puis de la rangée 9 à 16, et enfin de la rangée 17 à 24. Ces tableaux filtrés sont empilés horizontalement avec des cellules vides séparant chaque tableau divisé. La fonction IFERROR garantit que toute erreur #N/A qui pourrait survenir lors du processus d’empilement est supprimée du résultat final.

Si vous rencontrez des problèmes avec la formule dynamique en raison d’un volume important de données pour la division des colonnes, envisagez d’utiliser l’option de formule QUERY suivante. Il s’agit du moyen le plus simple de diviser un tableau en plusieurs tables dans Google Sheets.

Supposons que la plage du tableau soit A1:C18, avec A1:C1 contenant des étiquettes de champ, et que vous souhaitiez 8 lignes par tableau. Utilisez la formule suivante dans la cellule E1 :

=QUERY($A$1:$C, "SELECT * WHERE Col1 IS NOT NULL LIMIT 8 OFFSET 0", 1)

Contrairement à la formule dynamique, vous pouvez utiliser une plage ouverte, A1:C, il n’est pas nécessaire de spécifier une plage fermée comme A1:C18. De plus, vous pouvez inclure les en-têtes dans la plage, contrairement à la formule dynamique où nous avons utilisé A2:C18.

La formule QUERY sélectionne toutes les colonnes où la colonne 1 n’est pas nulle, excluant les lignes vides basées sur la colonne 1, et limite le résultat à 8 lignes. Il n’y a aucun décalage à partir du début du tableau.

Copiez cette formule et collez-la dans la cellule I1, en remplaçant OFFSET 0 par OFFSET 8. Puis collez la formule dans la cellule M1, en remplaçant OFFSET 0 par OFFSET 16. C’est aussi simple que ça.

Cet article explore deux approches distinctes pour diviser un tableau en plusieurs tables de taille égale côte à côte dans Google Sheets. Je préfère l’approche dynamique car elle gère automatiquement la création de nouvelles tables.

Lors de l’utilisation de la formule dynamique, envisagez d’utiliser FILTER avec la plage, par exemple, en remplaçant A2:C18 par FILTER(A2:C, A2:A<> » »). Cela offre l’avantage que lorsque votre tableau source s’étend, la formule créera automatiquement de nouvelles tables.

Bien que QUERY soit également une option viable dans Google Sheets, elle n’est pas dynamique et nécessite une interaction de l’utilisateur.

Avant de conclure, une dernière chose à noter : si vous êtes intéressé par la fusion de tableaux plutôt que par leur division, j’ai déjà fourni des tutoriels à ce sujet. Veuillez vous référer aux ressources suivantes :

• Comment fusionner deux tableaux à gauche dans Google Sheets
• Comment fusionner deux tableaux à droite dans Google Sheets
• Comment fusionner deux tableaux intérieurs dans Google Sheets
• Comment fusionner deux tableaux complets dans Google Sheets
• Joindre des tables dans Google Sheets (gauche, droite, intérieur, complet) – Résolution des ID en double