Google Sheets offre une multitude de formules puissantes pour effectuer des calculs complexes. Parmi celles-ci, la fonction SMALL semble plutôt simple de prime abord. Cependant, saviez-vous qu’elle peut être utilisée dans des calculs plus complexes, comme pour une recherche dynamique VLOOKUP ? Dans cet article, je vais vous expliquer comment utiliser la formule SMALL pour des calculs de base ainsi que des calculs avancés dans Google Sheets.
Le but de la Formule SMALL dans Google Spreadsheets
La syntaxe de base de la formule SMALL est la suivante :
SMALL(data, n)Où « data » représente le tableau ou la plage de données à prendre en compte et « n » est le rang du plus petit au plus grand élément à retourner.
La fonction SMALL dans Google Sheets renvoie la n-ième plus petite occurrence d’un nombre dans un ensemble de données.
Exemples de Formules SMALL de Base dans Google Spreadsheets
Dans la capture d’écran ci-dessus, la deuxième formule renvoie une valeur d’erreur. Cela est dû au fait que la plage A2:A6 ne contient aucune valeur numérique. Cela signifie qu’il doit y avoir des valeurs numériques dans la plage pour que la fonction SMALL fonctionne.
Consultez les autres exemples dans la capture d’écran ci-dessus, puis passez à l’exemple de formule SMALL suivant :
=small({100,225,150,400,500},2)Cette formule renvoie la valeur 150, car c’est la deuxième valeur la plus basse dans le tableau, où la première valeur la plus basse est 100. J’espère que vous avez pu comprendre ce concept. Voyons un autre exemple.
La formule suivante permet de faire la somme des deux plus petits nombres de la plage « C2:C6 » en utilisant la combinaison des fonctions SUM et SMALL.
La fonction SMALL n’est pas seulement une « petite » fonction dans Google Sheets. L’exemple suivant est un véritable révélateur.
L’exemple suivant est très important, car nous pouvons utiliser cette méthode pour créer des formules de recherche dynamiques.
Exemples de Formules SMALL Avancées dans Google Spreadsheets
Vous pouvez utiliser la fonction VLOOKUP pour rechercher dans la première colonne d’une plage avec une clé de recherche. Si une correspondance est trouvée, la formule renvoie la valeur d’une cellule spécifique dans la même ligne où la correspondance a été trouvée.
La formule Vlookup ci-dessous renvoie le résultat 200. Il s’agit de la valeur de la deuxième colonne de la première occurrence de la clé de recherche « Mango » dans la cellule A3.
Elle se répète une deuxième fois dans la cellule A5. Mais nous ne pouvons pas l’utiliser avec la fonction VLOOKUP de manière habituelle.
Cependant, vous pouvez le faire avec une combinaison de formules où la fonction SMALL de Google Sheets peut jouer un rôle important. Comment ?
Vous allez maintenant découvrir le rôle de la fonction SMALL dans une telle recherche, mais pas comment utiliser la fonction SMALL pour la recherche elle-même.
Pensée selon laquelle le rôle normal de la fonction SMALL est de renvoyer la n-ième plus petite valeur dans une plage, nous pouvons l’utiliser pour renvoyer la n-ième occurrence d’une valeur texte ou numérique dans une plage. Vous trouverez les étapes nécessaires ci-dessous. Pour apprendre la recherche VLOOKUP DYNAMIQUE, consultez notre tutoriel dédié ICI.
Utilisation de la Fonction SMALL pour Renvoyer la n-ième Occurrence d’une Chaîne de Caractères
Regardez l’ensemble de données small ci-dessus. Vous pouvez voir que l’élément « Mango » est répété deux fois. Avec Vlookup, vous pouvez utiliser la clé de recherche « Mango » et rechercher la première occurrence de celle-ci. C’est dans la cellule A3. Il n’y a aucun moyen de rechercher la deuxième occurrence dans la cellule A5.
Qu’est-ce que la formule Google Sheets Small peut faire dans ce cas ? Elle peut renvoyer la position relative de la deuxième, troisième ou n-ième occurrence de l’élément répété. Voici un exemple de cette astuce :
=ArrayFormula(SMALL(IF("Mango"=A2:A6,ROW(A2:A6)-1),2))Nous pouvons utiliser le même petit ensemble de données ci-dessus. Cette formule, par exemple en cellule D6, renvoie la valeur 4. qui est la position relative de « Mango » dans la cellule A5, qui est la deuxième occurrence de cet élément.
Pour trouver la première occurrence, il suffit de changer le nombre 2 en 1 dans la dernière partie de la formule ci-dessus. Voici la formule modifiée :
=ArrayFormula(SMALL(IF("Mango"=A2:A6,ROW(A2:A6)-1),1))Cette formule renvoie la réponse 2, qui est la position relative de la première occurrence de l’élément « Mango ».
Voici la décomposition de la formule utilisée.
Dans cet exemple, j’ai utilisé deux formules en combinaison avec la fonction SMALL. Il s’agit des fonctions logiques IF et ROW de Google Sheets.
Partie 1 : la formule ROW
Si vous extrayez la formule ROW de l’exemple ci-dessus, cela donnerait ce qui suit :
=ArrayFormula(ROW(A2:A6)-1)Ici, « A2:A6 » est la plage. La formule ci-dessus, sans le « -1 » à la fin, renverrait les numéros de ligne des éléments de la plage.
Par exemple, 2 pour Apple, 3 pour Mango, et ainsi de suite. Mais nous avons besoin de la position relative, comme Apple 1, Mango 2, et ainsi de suite.
Pour cela, j’ai ajouté un « -1 » à la fin de la formule. Vous devez changer ce nombre en « -2 » si votre plage est A3:A7. J’espère que vous avez compris.
Partie 2 : la formule IF
Maintenant, nous pouvons utiliser la fonction IF avec cette formule ROW pour renvoyer le numéro de ligne, si l’expression logique « Mango » est trouvée, sinon renvoyer « Faux ».
=ArrayFormula(IF("Mango"=A2:A6,ROW(A2:A6)-1))Le résultat de la formule ci-dessus sera le suivant.
Dans ce résultat de formule, 2 et 4 sont les positions relatives de « Mango » dans la plage. Maintenant, regardez les deux formules ci-dessous que j’ai déjà mentionnées ci-dessus.
=ArrayFormula(SMALL(IF("Mango"=A2:A6,ROW(A2:A6)-1),1))Cette formule renvoie le nombre 2, qui est la première occurrence de l’élément « Mango ».
=ArrayFormula(SMALL(IF("Mango"=A2:A6,ROW(A2:A6)-1),2))Cette formule renvoie le nombre 4, qui est la deuxième occurrence de l’élément « Mango ».
Vous pouvez utiliser cette partie pour créer une recherche dynamique dans Google Spreadsheet.
