Découvrez comment regrouper et additionner facilement des données séparées par des lignes vides dans Google Sheets, ce qui améliorera votre efficacité d’analyse des données.
Nous identifions généralement les groupes dans un ensemble de données en utilisant une colonne de catégorie. La sommation de ces données est beaucoup plus simple à l’aide de fonctions telles que SOMME.SI, SOMME.SI.ENS ou REQUÊTE dans Google Sheets.
Cependant, que faire si vous devez grouper et additionner des données séparées par une ou plusieurs lignes vides dans Google Sheets ?
Pour l’agrégation des données, nous nous appuyons généralement sur les fonctions mentionnées précédemment, mais elles nécessitent une colonne de catégorie pour appliquer des critères, un élément manquant lorsque l’on manipule des groupes séparés par des lignes vides.
Pour remédier à cela, nous allons attribuer automatiquement des identifiants uniques à chaque ensemble de données séparés par des lignes vides dans Google Sheets. Cela peut être réalisé à l’aide d’une formule à l’intérieur même de Google Sheets, éliminant ainsi le besoin de scripts.
J’ai développé une formule matricielle pour Google Sheets qui attribue un numéro unique à chaque groupe, même si un nombre inégal de lignes vides les sépare.
En fin de compte, nous utiliserons cette colonne attribuée comme colonne de catégorie pour regrouper et additionner efficacement les données dans Google Sheets.
Données d’exemple et problème à résoudre
Dans les données d’exemple fournies (voir la capture d’écran ci-dessus), la taille du personnel, le département et les notes sont enregistrés dans les colonnes A, B et C respectivement.
Les lignes 6, 9 et 13 sont intentionnellement laissées vides. L’objectif est de calculer la taille totale du personnel séparément pour les groupes suivants : lignes 2 à 5, 7 et 8, 10 à 12 et 14 et 15.
De plus, il est important de noter que lors de l’identification des lignes vides, la ligne entière dans la plage spécifiée doit être vide ; une seule cellule vide n’est pas considérée comme suffisante pour cette analyse.
Explorons maintenant comment regrouper et additionner des ensembles de données dans Google Sheets, où les groupes sont séparés par des lignes vides.
Formule pour regrouper les données séparées par des lignes vides dans Google Sheets
Étant donné notre plage de données d’exemple dans A2:C, vous pouvez appliquer la formule suivante à la cellule D2, la première ligne de la prochaine colonne disponible :
=ARRAYFORMULA(LET(data, A2:C, empty, IF(LEN(TRIM(TRANSPOSE(QUERY(TRANSPOSE(data),,9^9))))>0,1,), rc, COUNTIFS(empty, "<>"&empty, SEQUENCE(ROWS(data)), "<="&SEQUENCE(ROWS(data))), test, SCAN(1, rc, LAMBDA(a, v, IF(v=0, a, v))), IF(LEN(empty), test, )))Cette formule génère des numéros uniques pour faciliter le regroupement des données séparées par des lignes vides, produisant des numéros tels que 1, 4, 6 et 9 pour chaque groupe.
En utilisant la colonne D ou la formule dans la cellule D2 comme colonne de catégorie, vous pouvez numérot, regrouper et additionner efficacement des données séparées par des lignes vides dans Google Sheets.
Avant de plonger dans les applications pratiques, analysons les composants de la formule, fournissant des informations précieuses aux utilisateurs souhaitant perfectionner leurs compétences dans Google Sheets.
Explication de la formule
La formule qui regroupe les données séparées par des lignes vides est essentiellement une fonction LET qui attribue un ‘nom’ à l »expression de valeur’ et renvoie le résultat final de l »expression de formule’.
Syntaxe :
LET(nom1, expression_de_valeur1, [nom2, ...], [expression_de_valeur2, ...], expression_de_formule)Où :
- name1 = data
- value_expression1 = A2:C
- name2 est empty
- value_expression2 est IF(LEN(TRIM(TRANSPOSE(QUERY(TRANSPOSE(data),,9^9))))>0,1,)
- name3 = rc
- value_expression3 = COUNTIFS(empty, « <> »&empty, SEQUENCE(ROWS(data)), « <= »&SEQUENCE(ROWS(data)))
- name4 = test
- value_expression4 = SCAN(1, rc, LAMBDA(a, v, IF(v=0, a, v)))
- expression_de_formule est IF(LEN(empty), test, )
Pour comprendre la formule, vous devez vous familiariser avec value_expression2, value_expression3, value_expression4 et expression_de_formule.
1. Retour de 1 ou Null en fonction du vide des lignes (Value_Expression2)
Formule :
IF(LEN(TRIM(TRANSPOSE(QUERY(TRANSPOSE(data),,9^9))))>0,1,)Où :
- data = A2:C
Certains d’entre vous connaissent peut-être déjà cette formule, qui était présentée dans mon tutoriel intitulé « Filtrer si la ligne entière est vide dans Google Sheets ».
- TRANSPOSE(data) : La fonction TRANSPOSE réorganise les données de lignes en colonnes.
- QUERY(TRANSPOSE(data),,9^9) : QUERY combine les valeurs de chaque colonne dans la première ligne.
- TRANSPOSE(QUERY(TRANSPOSE(data),,9^9)) : En la transposant à nouveau, nous fusionnons toutes les lignes dans chaque ligne. Pour une explication plus détaillée, veuillez vous référer à « The Flexible Array Formula to Join Columns in Google Sheets ».
- La fonction TRIM supprime les espaces blancs ajoutés et la fonction LEN renvoie les valeurs de longueur dans chaque ligne. Le test logique IF vérifie que la longueur est supérieure à 0, retournant 1 pour vrai et vide pour faux.
L’objectif de la formule ci-dessus est de renvoyer 1 dans les lignes non vides et vide dans les lignes vides.
2. Comptage cumulatif des cellules non vides dans les lignes vides (Value_Expression3)
Formule :
COUNTIFS(empty, "<>"&empty, SEQUENCE(ROWS(data)), "<="&SEQUENCE(ROWS(data)))Où :
- empty = value_expression2 (voir colonne D dans la capture d’écran ci-dessus)
- data = A2:C
Cette formule COUNTIFS renvoie le comptage cumulatif de 1 (valeurs de la colonne D) dans les lignes vides.
Syntaxe de la fonction COUNTIFS :
COUNTIFS(plage_critères1, critère1, [plage_critères2, ...], [critère2, ...])Si nous n’utilisons que plage_critères1 et critère1, la formule renverra le comptage cumulatif dans chaque ligne vide, qui sera de 11 (le total des 1 dans la colonne D).
Ceci est la formule clé pour grouper les données séparées par des lignes vides, car nous avons maintenant des numéros uniques dans les lignes vides. À l’étape suivante, nous utiliserons cela pour créer des catégories uniques.
3. Remplacement de 0 par la valeur supérieure à 0 de la cellule ci-dessus (Value_Expression4)
Formule :
SCAN(1, rc, LAMBDA(a, v, IF(v=0, a, v)))Où :
- rc = value_expression3
Syntaxe de la fonction SCAN :
SCAN(valeur_initiale, tableau_ou_plage, lambda)La fonction prend deux arguments : un accumulateur (valeur_initiale) et un tableau (value_expression3).
La valeur initiale dans l’accumulateur est 1. La formule applique une fonction lambda à chaque ligne et stocke la valeur intermédiaire dans l’accumulateur. La fonction lambda renvoie la valeur dans l’accumulateur si la valeur de la ligne est 0 ; sinon, elle renvoie la valeur elle-même.
Ce processus est similaire au remplissage des cellules vides avec les valeurs de la cellule précédente, à la différence que les cellules contenant des 0 sont remplies au lieu des cellules vides.
Regardez les valeurs dans la colonne F. Vous pouvez observer que nous avons déjà regroupé les données séparées par des lignes vides.
À l’étape suivante, qui est l’expression de formule, nous affinerons le résultat en supprimant les valeurs dans les lignes vides.
4. Regroupement des données séparées par des lignes vides (Expression_de_formule)
Formule :
IF(LEN(empty), test, )Où :
- empty = value_expression2 qui identifie les lignes vides
- test = le résultat de la formule SCAN
La formule renvoie le résultat du test si la ligne n’est pas vide ; sinon, elle renvoie vide.
Cette approche nous permet de créer une colonne de catégorie pour regrouper et additionner des données séparées par des lignes vides dans Google Sheets.
Passons maintenant à quelques applications pratiques des catégories créées.
Regroupement et agrégation de données séparées par des lignes vides
Nous utiliserons SOMME.SI et REQUÊTE à des fins d’exemple.
Comment obtenir la taille totale du personnel dans chaque groupe séparé par des lignes vides dans notre exemple ci-dessus ?
Insérez la formule de catégorisation dans la cellule D2. Veuillez remonter et voir la formule juste en dessous du titre « Formula for Grouping Data Separated by Blank Rows in Google Sheets ». Ne copiez pas les formules utilisées dans la partie explication.
Ensuite, utilisez la REQUÊTE suivante :
=QUERY(A2:D, "select sum(A) where D is not null group by D label sum(A)''")Comment obtenir le total uniquement dans le troisième groupe ?
Nous pouvons utiliser la même formule REQUÊTE et inclure les clauses OFFSET et LIMIT.
=QUERY(A2:D, "select sum(A) where D is not null group by D limit 1 offset 2 label sum(A)''")L’alternative SOMME.SI à la formule ci-dessus est :
=SUMIF(D2:D, INDEX(UNIQUE(TOCOL(D2:D, 1)), 3), A2:A)Ici, TOCOL supprime les lignes vides dans la colonne de catégorie, UNIQUE renvoie les valeurs uniques et INDEX renvoie la troisième valeur unique, qui est le critère dans SOMME.SI.
Comment remplacer les numéros de catégorie par des textes de catégorie
Dans ce tutoriel, l’accent est mis sur la création d’une colonne de catégorie lorsqu’il s’agit de données séparées par des lignes vides qui déterminent les catégories. La formule initiale génère des numéros uniques, mais si vous préférez des textes de catégorie tels que « Catégorie A », « Catégorie B », etc., suivez la solution de contournement ci-dessous.
Utilisons notre exemple :
- Créez les catégories dans la colonne D.
- Dans la cellule G2, entrez la formule suivante pour obtenir les catégories uniques (les numéros 1, 4, 6 et 9) :
=UNIQUE(TOCOL(D2:D, 1)) - Dans les cellules H2 à H5, entrez les catégories « Catégorie A », « Catégorie B », « Catégorie C » et « Catégorie D ».
- Dans la cellule E2, utilisez la formule XLOOKUP suivante :
=ArrayFormula(XLOOKUP(D2:D, G2:G, H2:H, ))
Cette formule attribuera les catégories de texte, offrant une représentation plus claire de vos données regroupées et additionnées séparées par des lignes vides dans Google Sheets. Consultez la feuille d’exemple ci-dessous à titre de référence.
Feuille d’exemple : [lien vers la feuille d’exemple]
Conclusion
J’ai expliqué comment regrouper des données séparées par des lignes vides dans Google Sheets. De plus, j’ai fourni 2 à 3 exemples illustrant comment agréger les données regroupées.
Une fois que vous créez des catégories pour les données séparées par des lignes vides, vous pouvez exploiter diverses fonctions où les catégories deviennent une partie intégrante.
Vous pouvez même basculer de telles données à l’aide d’un tableau croisé dynamique ou d’une REQUÊTE. Si vous avez des doutes, n’hésitez pas à les poser dans les commentaires ci-dessous.
