Pour obtenir les valeurs maximales en cours dans Google Sheets, nous pouvons utiliser deux types de formules – une formule basée sur un glisser-déposer ou une formule matricielle.
Formule basée sur le glisser-déposer
La formule basée sur le glisser-déposer est simple à coder en utilisant la fonction MAX. Prenons l’exemple de la séquence suivante de dix valeurs dans une colonne {3;5;4;6;6;8;7;9;1;1}. Les valeurs maximales en cours sont la séquence de valeurs {3;5;5;6;6;8;8;9;9;9}.
Supposons que le premier ensemble de valeurs ci-dessus se trouve dans la plage de cellules B2:B11.
Vous pouvez utiliser la formule suivante dans la cellule C2 et la faire glisser vers le bas jusqu’à la cellule C11 pour obtenir le deuxième ensemble, c’est-à-dire les valeurs maximales en cours.
=max($B$2:B2)
En passant, nous pouvons également obtenir les points hauts de la séquence de nombres dans C2:C11. Je l’expliquerai plus tard.
Formule matricielle
En ce qui concerne la formule matricielle, il existe différentes méthodes.
- DMAX (ancienne)
- SCAN (nouvelle) – Recommandée !
Formule matricielle DMAX
Nous pouvons utiliser la fonction de base de données DMAX pour coder une formule matricielle maximale en cours dans Google Sheets. Cependant, cela est relativement complexe.
Syntaxe : DMAX(base de données, champ, critères)
Nous n’avons qu’une plage de cellules (matrice) à utiliser, c’est-à-dire B2:B11. Vous pourriez donc penser que nous ne pouvons pas utiliser la fonction DMAX.
Vous avez raison dans une certaine mesure car ladite fonction nécessite une base de données ou des données structurées.
Pour résoudre ce problème, nous utiliserons d’autres fonctions pour formater la plage en une base de données virtuelle.
La formule suivante peut ne pas être nouvelle pour mes lecteurs.
C’est parce que j’ai déjà utilisé des fonctions de base de données pour obtenir des résultats de tableau similaires par ligne dans le passé.
Videz la plage C2:C11 et insérez la formule matricielle maximale en cours suivante dans la cellule C2.
Formule matricielle principale pour les valeurs maximales en cours :
=ArrayFormula(dmax(transpose({B2:B11,if(sequence(10,10)/sequence(10,10)+sequence(1,10,row(A2)-1)<=row(A2:A11),transpose(B2:B11))}),sequence(rows(B2:B11),1),{if(,,);if(,,)}))
La formule DMAX ci-dessus concerne les valeurs dans la plage fermée B2:B11. Une fois que nous l’aurons maîtrisée, nous remplacerons B11 par B (une plage ouverte).
Explication de la formule
La fonction comporte trois arguments : base de données, champ et critères.
Voici ces arguments dans la formule DMAX ci-dessus.
BASE DE DONNÉES
=ArrayFormula(transpose({B2:B11,if(sequence(10,10)/sequence(10,10)+sequence(1,10,row(A2)-1)<=row(A2:A11),transpose(B2:B11))}))CHAMP
=ArrayFormula(sequence(rows(B2:B11),1))CRITÈRES
={if(,,);if(,,)}Pour expliquer ces arguments, j’ai inséré leurs valeurs respectives dans les cellules E2, P2 et R2. Veuillez consulter l’image ci-dessous.
Remarque : Pour les utiliser de manière autonome, j’ai également utilisé la fonction ArrayFormula.
J’ai utilisé ces sorties dans une nouvelle formule DMAX dans la cellule C2 ci-dessous pour retourner la valeur maximale en cours des valeurs dans B2:B11.
=ArrayFormula(dmax(E2:N12,P2:P11,R2:R3))Il va sans dire que dans notre formule matricielle principale pour la valeur maximale en cours, nous avons remplacé E2:N12, P2:P11 et R2:R3 par les formules correspondantes, évitant ainsi les plages de colonnes d’assistance.
Explication des arguments (Champ et Critères)
J’expliquerai l’argument de la base de données plus tard. Voici les explications des deux autres arguments, à savoir le champ et les critères.
Il y a dix colonnes dans la base de données en E2:N12. Donc, le champ P2:P11 contient les chiffres de 1 à 10, qui représentent chaque colonne.
Nous n’avons pas de colonne de critères dans la base de données. Par conséquent, comme c’est la norme dans les fonctions de base de données, j’ai utilisé deux cellules vides verticales, c’est-à-dire R2:R3, pour la représenter.
Relatif : Deux façons de spécifier des cellules vides dans les formules de Google Sheets.
Passons maintenant aux détails de la base de données, qui est la partie centrale et complexe de la formule matricielle maximale en cours dans Google Sheets.
Base de données et logique
Je vais d’abord expliquer la logique pour vous permettre de comprendre ce qu’est la « base de données » utilisée dans E2:N12.
Nous pouvons obtenir la valeur maximale en cours de la manière suivante dans Google Sheets.
Voici les valeurs {3;5;4;6;6;8;7;9;1;1} dans B2:B11.
Pour obtenir le maximum en cours, nous pouvons utiliser les formules =max(3), =max(3,5), =max(3,5,4), =max(3,5,4,6), =max(3,5,4,6,6), =max(3,5,4,6,6,8), =max(3,5,4,6,6,8,7), =max(3,5,4,6,6,8,7,9), =max(3,5,4,6,6,8,7,9,1), =max(3,5,4,6,6,8,7,9,1,1) dans les cellules C2, C3, C4, C5, C6, C7, C8, C9, C10 et C11, respectivement.
La logique consiste à coder la formule matricielle maximale en cours dans Google Sheets.
Permettez-moi de l’expliquer.
Nous utiliserons une seule formule pour obtenir la plage utilisée dans les dix formules maximales ci-dessus dans dix colonnes, comme suit.
=ArrayFormula(transpose({if(sequence(10,10)/sequence(10,10)+sequence(1,10,row(A2)-1)<=row(A2:A11),transpose(B2:B11))}))Vous pouvez trouver l’explication de cette formule ici – Obtenir des motifs 1-12-123-1234 en utilisant une formule matricielle dans Google Sheets.
Maintenant, nous devons en faire une base de données pour DMAX.
En ajoutant une ligne d’en-tête, nous pouvons formater le tableau ci-dessus en base de données.
J’ai donc modifié la formule ci-dessus comme suit.
=ArrayFormula(transpose({B2:B11,if(sequence(10,10)/sequence(10,10)+sequence(1,10,row(A2)-1)<=row(A2:A11),transpose(B2:B11))}))J’ai simplement ajouté les valeurs B2:B11 en tant qu’étiquettes de champ (même une ligne vide fera l’affaire).
Dans l’image ci-dessus, veuillez consulter la plage E2:N2 pour les étiquettes de champ.
La formule ci-dessus est celle utilisée dans la cellule E2 (base de données).
Le DMAX « nouveau » dans la cellule C2 utilise la base de données ci-dessus et renvoie le maximum de chaque colonne verticalement car nous avons spécifié les champs (numéros de colonne) verticalement dans P2:P11.
Comment utiliser une plage ouverte dans cette formule ?
Lorsque nous utilisons une plage ouverte comme suit, les performances de notre feuille en question peuvent être gravement affectées.
=ArrayFormula(if(B2:B="",,dmax(transpose({B2:B,if(sequence(rows(B2:B),rows(B2:B))/sequence(rows(B2:B),rows(B2:B))+sequence(1,rows(B2:B),row(A2)-1)<=row(A2:A),transpose(B2:B))}),sequence(rows(B2:B),1),{if(,,);if(,,)})))Nous suivrons donc une solution de contournement qui garantira l’amélioration des performances.
Voici comment.
Nous utiliserons une formule pour trouver la dernière cellule non vide en ignorant les cellules vides dans la colonne B.
La formule suivante renverra 11, qui est le numéro de ligne de la dernière cellule non vide actuelle dans la colonne B.
=ArrayFormula(MATCH(2,1/(B:B<>""),1))Nous l’utiliserons (la partie en gras) pour modifier la formule matricielle maximale en cours comme indiqué dans le tableau ci-dessous.
| Actuelle | À modifier | Nombre d’occurrences |
|---|---|---|
| D2 | D2:D3 | 2 |
Je vais apporter les modifications ci-dessus et inclure la formule dans ma feuille d’exemple ci-dessous.
De manière similaire à Cummin, nous pouvons utiliser la fonction SCAN Lambda pour coder une formule matricielle maximale en cours (Cummax) dans Google Sheets.
Voici cette formule.
=scan(B2,B2:B,lambda(a,v,if(and(a>=v,v<>""),a,v)))Elle présente deux principaux avantages par rapport à la formule DMAX précédente.
- C’est un code propre.
- Fonctionne sur un ensemble de données volumineux.
La formule SCAN parcourt les valeurs de B2:B, de B2 en passant par chaque ligne, remplaçant les valeurs par la valeur maximale jusqu’à présent, pour finir par une plage où la dernière ligne contient la valeur maximale.
Dans la formule de running max array SCAN ci-dessus, a (accumulateur) est égal à B2 dans la première ligne.
En ce qui concerne v, c’est la valeur ligne par ligne à évaluer. Dans la première ligne, v sera B2, B3 dans la deuxième ligne, et ainsi de suite.
Si a>=v, la formule renvoie la valeur de l’accumulateur, sinon la valeur de la ligne. La valeur maximale renvoyée sera la valeur de l’accumulateur dans la ligne suivante.
Les hauts d’eau des valeurs actuelles dans B2:B11 sont {3;5;6;8;9}.
Pour autant que je sache, ce sont les valeurs uniques des valeurs maximales en cours. Nous devons donc simplement envelopper notre formule matricielle avec UNIQUE pour les obtenir.
Syntaxe : unique(formule maximale en cours)
Formule :
=unique(scan(B2,B2:B,lambda(a,v,if(and(a>=v,v<>""),a,v))))C’est tout. Merci de rester avec nous. Profitez-en !
Exemple_191121
