Lorsque vous souhaitez filtrer l’enregistrement ou la ligne de valeur minimale ou maximale de chaque groupe dans Google Sheets, vous pouvez utiliser une formule ou le menu DONNÉES > CRÉER UN FILTRE.
Nous pouvons regrouper les données en fonction d’une colonne ou de deux colonnes (plusieurs colonnes).
Regardez l’image ci-dessous. J’ai séparé chaque groupe par une ligne en gras.
Quelle méthode dois-je suivre – la formule ou la commande du menu ?
Formule: Lorsque nous voulons filtrer les lignes/enregistrements minimum ou maximum vers une nouvelle zone/plage, nous pouvons utiliser la fonction QUERY.
Menu Filtrer: Lorsque nous voulons filtrer les données dans la plage existante. Dans ce cas, nous devons utiliser une colonne auxiliaire.
J’ai trié les lignes en fonction des articles dans le Tableau 1 (A2: B10).
Dans le Tableau 2 (A14:C22), j’ai trié les lignes en fonction des articles et des dates.
Est-ce nécessaire ?
Non ! Pour filtrer la valeur minimale ou maximale de chaque groupe dans Google Sheets, nous pouvons également utiliser des tableaux non triés.
Comment filtrer la valeur minimale ou maximale de chaque groupe dans Google Sheets ?
Approche basée sur la formule
Nous avons deux tableaux.
Le Tableau 1 contient les reçus d’articles (A2:B10).
Le Tableau 2 contient les reçus d’articles par date (A14:C22).
Avant d’aller dans le menu Filtrer, écrivons d’abord les formules QUERY.
Formule pour filtrer la valeur minimale dans un groupe à une ou deux colonnes
Formule min 1 (E2): =query({A2:B10},"Select Col1,min(Col2) group by Col1",1)
Formule min 2 (E14): =query({A14:C22},"Select Col1,Col2,min(Col3) group by Col1,Col2",1)
Dans les formules ci-dessus, en plus de la fonction d’agrégation MIN, nous avons utilisé deux clauses QUERY. Ce sont SELECT et GROUP BY.
Vous pourriez aimer : Comment faire la somme, la moyenne, le compte, le maximum et le minimum dans Google Sheets Query.
Dans la clause SELECT, nous devons sélectionner les colonnes pour regrouper les données.
Les mêmes colonnes doivent être utilisées dans la clause GROUP BY.
Formule pour filtrer la valeur maximale dans un groupe à une ou deux colonnes
Remplacez « min » par « max ». C’est ce que nous voulons faire ici.
-
Formule max 1 (E2):
=query({A2:B10},"Select Col1,max(Col2) group by Col1",1) -
Formule max 2 (E14):
=query({A14:C22},"Select Col1,Col2,max(Col3) group by Col1,Col2",1)
Personnalisation des étiquettes de champ
La sortie ci-dessus contient des étiquettes de champ que vous pouvez supprimer entièrement ou personnaliser.
Remplacez la formule dans E2 par celle-ci:
=query({A2:B10},"Select Col1,max(Col2) group by Col1 label Col1'',max(Col2)''",1)
En ce qui concerne la requête E14, utilisez celle-ci :
=query({A14:C22},"Select Col1,Col2,max(Col3) group by Col1,Col2 label Col1'',Col2'',max(Col3)''",1)
Les formules ci-dessus suppriment les étiquettes de champ dans les résultats de la requête. Pour ajouter une étiquette personnalisée, vous devez les placer entre guillemets simples.
Je veux dire remplacer Col1 » par Col1’Nom_de_l’article’ pour obtenir « Nom_de_l’article » comme étiquette de champ de la première colonne.
Lié : Comprendre la clause Label dans Google Sheets Query.
Nous avons appris comment trouver la valeur minimale ou maximale de chaque groupe dans Google Sheets.
Menu Filtrer pour filtrer la valeur minimale ou maximale de chaque groupe dans Google Sheets
Dans les exemples ci-dessus, nous avons utilisé deux formules QUERY – une dans E2 et l’autre dans E14.
La formule E2 renvoie deux colonnes, tandis que E14 renvoie trois colonnes.
Nous allons reformater la formule E2 pour renvoyer une seule colonne de valeurs booléennes VRAI ou FAUX dans C2:C10, comme indiqué sur l’image ci-dessous.
Nous ferons de même avec la formule E14 (D14:D22).
Ensuite, filtrez les valeurs VRAI dans C2:C10 ou D14:D22.
Pour ce faire, nous pouvons utiliser l’une des deux commandes du menu FILTRER – CRÉER UN FILTRE ou VUES FILTRÉES.
Comment ?
- Sélectionnez C2:C10.
- Allez dans Données > Créer un filtre.
- Cliquez sur la flèche dans C2.
- Décochez FAUX et cliquez sur OK.
De cette façon, nous pouvons utiliser la commande du menu pour filtrer la valeur minimale ou maximale de chaque groupe dans Google Sheets.
Voici comment procéder étape par étape. Je veux dire reformuler les formules E2 et E14.
Note : Nous utiliserons les deux formules de requête ci-dessus qui contiennent la clause LABEL.
Étapes
Je vais commencer par la formule E2.
-
Transposer la formule E2.
=transpose(query({A2:B10},"Select Col1,max(Col2) group by Col1 label Col1'',max(Col2)''",1)) -
Ensuite, enveloppez-la avec Query pour combiner les valeurs.
=query(transpose(query({A2:B10},"Select Col1,max(Col2) group by Col1 label Col1'',max(Col2)''",1)),,9^9)
Lié : La formule de tableau flexible pour joindre des colonnes dans Google Sheets.
-
Appliquer Textjoin pour placer un délimiteur de pipe entre les valeurs.
=textjoin("|",true,query(transpose(query({A2:B10},"Select Col1,max(Col2) group by Col1 label Col1'',max(Col2)''",1)),,9^9)) -
Utilisez l’étape 3 ci-dessus comme une regular_expression dans Regexmatch.
Syntaxe : REGEXMATCH(texte, regular_expression)
Qu’en est-il de l’argument texte dans Regexmatch ?
Il s’agit de la plage combinée A3:A10 et B3:B10.
={"Max";ArrayFormula(regexmatch(A3:A10&" "&B3:B10, textjoin("|",true,query(transpose(query({A2:B10},"Select Col1,max(Col2) group by Col1 label Col1'',max(Col2)''",1)),,9^9))))}
Nous pouvons utiliser la formule ci-dessus dans C2 pour filtrer les valeurs maximales de chaque groupe dans Google Sheets.
Et la formule E14 ?
Les mêmes étapes s’appliquent également ici. La seule différence réside dans la partie texte de Regexmatch.
Dans l’exemple ci-dessus, il s’agit de A3:A10 & » « &B3:B10. Ici, ce n’est pas A15:A22 & » « &B15:B22 & » « &C15:C22, mais text(A15:A22, »dd/mm/aa ») & » « &B15:B22 & » « &C15:C22.
La plage A15:A22 contient des dates.
Lorsque nous concaténons cette plage de dates avec une plage de texte, elle perd le formatage. La fonction Text le conserve.
Formule D14 : ={"Max";ArrayFormula(regexmatch(text(A15:A22,"dd/mm/aa")&" "&B15:B22&" "&C15:C22, textjoin("|",true,query(transpose(query({A14:C22},"Select Col1,Col2,max(Col3) group by Col1,Col2 label Col1'',Col2'',max(Col3)''",1)),,9^9))))}
Remplacez « Max » par « Min » pour filtrer les valeurs minimales de chaque groupe dans Google Sheets.
C’est tout. Merci de votre attention. Amusez-vous bien !
Exemple de feuille 131021
