Sans dépendre d’une colonne d’aide, nous pouvons calculer la plus longue série de victoires et de défaites dans Google Sheets.
Nous utiliserons la combinaison des fonctions Ligne, Long, Si, Fréquence et Sortn sous forme de tableau pour cela.
Prenons d’abord un exemple avec l’approche de la colonne d’aide, qui sera facile à comprendre pour les utilisateurs novices de Google Sheets.
Supposons que deux joueurs aient participé à un championnat d’échecs et joué dix parties d’affilée.
Les résultats du joueur 1 sont les suivants, où 0 = perte, 0.5 = égalité, et 1 = victoire (veuillez vous référer à B2:B11 sur l’image#1 ci-dessous).
Pour obtenir la plus longue série de victoires, utilisez le test logique suivant dans la cellule C2 et copiez-collez-le dans C3:C11.
=si(B2=1;1+C1;0)
Ensuite, utilisez le MAX ci-dessous dans n’importe quelle cellule vide et voilà !
=max(C2:C11)
Pour obtenir la plus longue série de défaites, remplacez le test logique dans la cellule C2 par le suivant et copiez-collez-le dans C3:C11.
=si(B2=0;1+C1;0)
Aucun changement n’est nécessaire dans le MAX. Et pour l’égalité ?
Utilisez =si(B2=0.5;1+C1;0) dans C2 pour créer la colonne d’aide. Bien sûr, vous devez faire glisser cette formule jusqu’à la cellule C11.
Toutes les formules ci-dessus nécessitent une cellule vide C1.
Voilà pour l’approche de la colonne d’aide non-structurée. Passons maintenant aux formules de tableau.
Les plus longues séries de victoires, de matchs nuls ou de défaites en tant que formules de tableau dans Google Sheets
Je suis partisan de l’utilisation de formules de tableau pour ce calcul. Ainsi, nous pouvons éviter d’occuper des cellules supplémentaires (colonnes d’aide).
Ignorez les formules de la colonne C. Il suffit d’entrer la formule de tableau suivante dans la cellule E2.
Victoire (E2) :
=sortn(fréquence(si((B2:B=1)*long(B2:B);ligne(B2:B));si((B2:B=1)*long(B2:B);;ligne(B2:B))),1;0;1;0)
La formule ci-dessus calcule les valeurs dans la plage B2:B et renvoie la plus longue série de victoires dans la cellule E2 de Google Sheets.
Il vous suffit de faire deux modifications pour obtenir les plus longues séries de défaites.
Défaite (E3) :
=sortn(fréquence(si((B2:B=0)*long(B2:B);ligne(B2:B));si((B2:B=0)*long(B2:B);;ligne(B2:B))),1;0;1;0)
Match nul (E4) :
=sortn(fréquence(si((B2:B=0.5)*long(B2:B);ligne(B2:B));si((B2:B=0.5)*long(B2:B);;ligne(B2:B))),1;0;1;0)
Oui ! B2:B=1 dans la formule de victoire devient B2:B=0 dans la formule de défaite et B2:B=0.5 dans la formule de match nul.
Explication de la formule
Commençons par la formule générique E2/E3/E4 qui est la suivante.
Formule Générique : SORTN(FREQUENCY(data, classes))
Nous avons utilisé des tests logiques pour renvoyer les données et les classes. Dans une partie ultérieure de ce tutoriel, nous entrerons dans les détails.
Comme je l’ai mentionné, toutes les formules de tableau ci-dessus pour la série de victoires/égalité/défaites sont identiques.
Ce sont les combinaisons des fonctions Ligne, Long, Si, Fréquence et Sortn.
Nous commencerons l’explication par la dernière fonction, c’est-à-dire Sortn.
1. Partie Sortn
Sortn(Fréquence(data, classes))
Son rôle est de trier virtuellement la sortie de Fréquence par ordre décroissant et de renvoyer la valeur de la ligne la plus élevée.
En d’autres termes, pour renvoyer la valeur maximale.
La partie centrale de la formule de la plus longue série de victoires/défaites/égalité dans Google Sheets est la fonction Fréquence.
2. Partie Fréquence – La formule clé dans le calcul de la plus longue série de victoires/égalité/défaites
Sortn(Fréquence(data, classes))
Une compréhension de base de la fonction ci-dessus est essentielle pour apprendre la plus longue série de victoires dans Google Sheets.
Par exemple, nous avons inséré la formule SÉQUENCE ci-dessous dans la cellule G1 pour renvoyer les nombres de 1 à 10 dans G1:G10.
=séquence(10)
Ensuite, utilisez =FRÉQUENCE(G1:G10;{3;8}) dans H1, ce qui renverra 3, 5 et 2 dans H1, H2 et H3, respectivement.
les données – G1:G10
les classes – {3,8}
Cela signifie que le nombre d’entrées dans G1:G10 inférieur à 3 est de 3, entre 4 et 8 est de 5, et supérieur à 8 est de 2.
Revenons à notre exemple de championnat d’échecs dans Google Sheets.
Dans notre formule de la plus longue série de victoires en E2, les données et les classes dans la Fréquence sont les suivantes (en lettres grasses).
les données – =ArrayFormula(si((B2:B=1)*long(B2:B);ligne(B2:B)))
les classes – =ArrayFormula(si((B2:B=1)*long(B2:B);;ligne(B2:B)))
Je les ai entrés dans les cellules D2 et E2 pour expliquer, et voici cette capture d’écran.
Si nous utilisons une formule de Fréquence, c’est-à-dire =ArrayFormula(FREQUENCY(D2:D11;E2:E11)), cela renverra {1;0;0;0;4;0}.
Ce qui signifie :
Le nombre d’entrées inférieur à 3 est de 1, entre 3 et 4 est de 0, entre 4 et 5 est de 0, entre 5 et 6 est de 0, entre 6 et 11 est de 4 et supérieur à 11 est de 0.
La valeur maximale est de 4, ce qui correspond à la série de victoires.
Nous pouvons utiliser Sortn pour le renvoyer, et c’est ce que j’ai fait dans nos formules principales E2, E3 et E4.
Dans les formules de la plus longue série de défaites et de matchs nuls, les données et les classes sont légèrement différentes. J’y reviendrai dans la partie logique ci-dessous.
3. Partie Logique (SI, LIGNE et LONG)
Sortn(Fréquence(data, classes))
La partie logique SI renvoie les données et les classes pour la Fréquence. Elle joue donc également un rôle pivot dans le calcul de la plus longue série de victoires/défaites/matchs nuls dans Google Sheets.
Le rôle de LONG est de sauter les cellules vides dans les lignes.
Dans la formule de la plus longue série de victoires :
Dans les données, le test logique renvoie les numéros de ligne des lignes qui contiennent 1 (veuillez vous référer à l’image#2 D2:D11).
En ce qui concerne les classes, le test logique renvoie les numéros de ligne des lignes qui ne contiennent pas 1 (veuillez vous référer à l’image#2 E2:E11).
Et pour la formule de la plus longue série de défaites ?
les données = numéros de ligne des cellules contenant 0.
la classe = numéros de ligne des cellules ne contenant pas 0.
Pour la série d’égalité :
les données = numéros de ligne des cellules contenant 0.5
la classe = numéros de ligne des cellules ne contenant pas 0.5
C’est tout. Merci de votre attention. Profitez-en bien !
Visit Crawlan.com for more Google Sheets tips and tricks.
