Supposez que vous souhaitiez trouver le nombre total de lignes d’un élément. Je veux dire le nombre de lignes qu’un élément occupe dans une colonne.
Vous pouvez utiliser la fonction COUNTIF à cet effet.
Exemple:
=countif(B3:B14,"Pomme")
Cela peut ne pas être idéal dans certains cas. Vous voudrez peut-être d’abord trouver la première et la dernière ligne d’un élément pour obtenir le nombre de lignes.
La formule que vous allez apprendre dans ce tutoriel vous aidera à obtenir les numéros de la première et de la dernière ligne des éléments dans chaque groupe dans Google Sheets.
En soustrayant les numéros de la première ligne des numéros de la dernière ligne, vous obtiendrez le nombre total de lignes pour chaque élément.
Scénario 1:
Dans l’exemple ci-dessus, les éléments se trouvent dans la colonne B.
Scénario 2:
Si les éléments se répètent comme dans l’exemple ci-dessous, vous devrez peut-être utiliser une formule différente pour trouver les numéros de la première et de la dernière ligne des éléments dans Google Sheets.
Note: Le COUNTIF que j’ai donné en exemple ci-dessus s’applique à cet ensemble de données.
J’ai quelques formules dans les cellules C3, D3 et E3. Vous allez obtenir ces formules et les explications ci-dessous.
Trouver les numéros de la première ligne des éléments dans chaque groupe
Les scénarios 1 et 2 utilisent deux méthodes différentes (formules) pour trouver les numéros de la première ligne des éléments.
Pour trouver les numéros de la dernière ligne, dans les deux scénarios, les formules sont identiques.
Dans le premier scénario, nous pouvons utiliser la formule de tableau suivante dans la cellule C3 pour obtenir les numéros de la première ligne des éléments dans B3:B14.
=ArrayFormula( if( B3:B14<>"", row(B3:B14), ) )
La formule renvoie les numéros de ligne, là où les valeurs ne sont pas vides dans B3:B14.
Mais dans le deuxième scénario, nous devons utiliser une formule différente, et voici celle-ci.
=ArrayFormula( IFNA( if( {" ";B3:B14}<>{B3:B14;" "}, row(B3:B14), ) ) )
Les changements sont minimes, mais la logique est complètement différente. Quelle est la différence?
Selon le scénario 1, ici nous ne pouvons pas tester si B3:B14 est vide ou non car cela n’a aucun sens avec les données en main.
Ici, à la place, la formule teste deux colonnes virtuelles et elles sont {" ";B3:B14} et {B3:B14;" "}.
Pour vous aider à comprendre, laissez-moi remplir les deux tableaux mentionnés dans les colonnes C et D en utilisant les deux formules de colonne virtuelle ci-dessus.
J’espère que l’explication de la capture d’écran ci-dessus a du sens.
Trouver les numéros de la dernière ligne des éléments dans chaque groupe
Pour trouver les numéros de la dernière ligne des éléments dans chaque groupe, nous pouvons utiliser la formule de tableau suivante dans la cellule D3. Cette formule est la même pour les scénarios 1 et 2.
=ArrayFormula( ifna( VLOOKUP( C3:C14, {lookup(row(C3:C14),if(len(C3:C14),row(C3:C14)),C3:C14),ROW(C3:C14)}, 2, 1 ) ) )
Je sais que vous voudrez peut-être des explications détaillées sur la formule ci-dessus, qui renvoie les numéros de la dernière ligne des éléments dans Google Sheets. Alors, voici!
Explication de la formule
Pour trouver les numéros de la dernière ligne des éléments dans Google Sheets, j’ai utilisé une formule Vlookup.
Syntaxe Vlookup: VLOOKUP(search_key, range, index, [is_sorted])
Dans ma formule, les arguments Vlookup sont les suivants.
search_key: C3:C14 (les numéros de la première ligne des éléments)range:{lookup(row(C3:C14),if(len(C3:C14),row(C3:C14)),C3:C14),ROW(C3:C14)}index: 2is_sorted: 1
Ici, la plage Vlookup nécessite une mention spéciale. Je saisis donc la partie plage de la formule dans la cellule G3 pour démontrer son rôle.
Le Vlookup 'range' est le résultat retourné par une formule Lookup dans une colonne (G3:G14) et les numéros de ligne de 3 à 14 (H3:H14) dans une autre colonne.
Le Vlookup recherche la clé de recherche, c’est-à-dire C3:C14, dans la première colonne de la plage et renvoie la valeur maximale de la deuxième colonne dans la plage.
Pourquoi la formule renvoie-t-elle des valeurs maximales?
Parce que le dernier argument (is_sorted) dans Vlookup vaut 1 et cela signifie que nous utilisons une plage triée.
Dans une plage triée, la formule renvoie la correspondance la plus proche (inférieure ou égale à la clé de recherche).
Dans notre cas, il y a plusieurs occurrences de clés de recherche dans la première colonne de la plage. La formule choisit les dernières occurrences et renvoie les valeurs des lignes correspondantes de la deuxième colonne.
Explication de la plage de recherche de Vlookup
lookup(row(C3:C14),if(len(C3:C14),row(C3:C14)),C3:C14)
La formule Lookup renvoie le résultat dans G3:H14, qui peut être divisé comme suit.
Syntaxe: LOOKUP(search_key, search_range|search_result_array, [result_range])
search_key:row(C3:C14)search_range:if(len(C3:C14),row(C3:C14))– Si les cellules C3:C14 contiennent des valeurs, renvoie les numéros de ligne correspondants, sinon la valeur booléenne FAUX.result_range:C3:C14
Le Lookup recherche les numéros de ligne de 3 à 14 dans la plage de recherche ci-dessus et renvoie les valeurs de la plage résultat, qui sont les numéros de la première ligne de chaque élément du groupe.
Si la clé de recherche n’est pas trouvée dans la plage de recherche, l’élément utilisé dans Lookup sera la valeur qui est immédiatement inférieure dans la plage fournie.
Par exemple, la clé de recherche 4 n’est pas dans la plage de recherche. La valeur immédiatement inférieure, qui est 3, sera considérée et la valeur correspondante de la plage résultat sera renvoyée.
Voir G2:G14 dans l’image 4 pour le résultat de Lookup.
Comment obtenir le total des lignes dans chaque groupe dans Google Sheets
Nous avons appris à obtenir les numéros de la première et de la dernière ligne des éléments dans chaque groupe dans Google Sheets.
Maintenant, pour obtenir le total des lignes dans chaque groupe, soustrayez le numéro de la première ligne du numéro de la dernière ligne et ajoutez 1.
Vous pouvez utiliser la formule suivante dans la cellule E3 à cette fin, et je pense qu’elle ne nécessite aucune explication.
=ArrayFormula( if( len(D3:D14), D3:D14-C3:C14+1, ) )
Conclusion
Le retour des numéros de la première et de la dernière ligne des éléments sera utile lors du regroupement des données. Voir les images 1 et 2.
Vous pouvez voir que les éléments sont regroupés en utilisant la commande de menu Données « Grouper ».
Lorsque vous réduisez toutes les lignes à l’aide de mes formules, vous pouvez comprendre le nombre de lignes dans chaque groupe ainsi que les numéros de la première et de la dernière ligne des éléments.
Cela peut être utile dans certains cas spécifiques.
Si vous souhaitez en savoir plus ou copier le modèle de feuille de calcul, cliquez ici.
Merci de votre attention et profitez-en bien! Pour plus d’informations, visitez Crawlan.com.
