Dans ce guide pratique, vous allez apprendre comment créer, utiliser, modifier, supprimer et importer des fonctions nommées dans Google Sheets. Et ce n’est pas tout ! Vous trouverez également une liste croissante de fonctions nommées à la fin de cet article.
Pendant l’année 2022-2023, Google Sheets a bénéficié de nouvelles fonctions, de nouvelles fonctionnalités et d’une refonte de son interface utilisateur. J’aime la plupart des nouvelles fonctionnalités disponibles dans mon compte, sauf la Validation des données. À noter également que je n’ai pas la nouvelle chronologie dans mon compte. Cela étant dit, concentrons-nous sur les fonctions nommées.
Les fonctions nommées sont une nouvelle fonctionnalité ajoutée à Google Sheets pour aider les utilisateurs à créer des fonctions personnalisées sans avoir recours à Google Apps Script.
Par exemple, nous pouvons créer une fonction personnalisée appelée SEQUENCE_NB pour numéroter une colonne dans Sheets. Contrairement à la fonction intégrée SEQUENCE, cette fonction nouvellement créée numérotera uniquement les lignes où la colonne référencée a une valeur.
Astuce de fonction au fur et à mesure de la saisie :
Résultat de la formule :
Nous pouvons réutiliser cette fonction de la même manière que n’importe quelle fonction intégrée de Google Sheets, par exemple =SEQUENCE_NB(A2:A), =SEQUENCE_NB(Feuille2!I:I), etc. De plus, Google Sheets prend en charge l’importation de cette fonction dans d’autres feuilles.
Quand faut-il envisager de créer une fonction nommée dans Google Sheets ?
En bref, utilisez cette fonctionnalité lorsque vous avez une formule complexe dans Google Sheets et que vous voulez la réutiliser plus souvent dans le même classeur ou dans des classeurs différents.
Fonctions personnalisées utilisant des formules intégrées dans Excel et Sheets
Excel :
Excel pour Microsoft 365 dispose déjà d’une fonctionnalité similaire où nous convertissons d’abord notre formule en une fonction LAMBDA, puis nous la copions-collons dans le gestionnaire de noms pour créer une fonction nommée.
Google Sheets :
Dans Google Sheets, nous n’avons pas besoin d’écrire la fonction LAMBDA en premier.
Nous pouvons directement copier-coller la formule dans le menu « Fonctions nommées » (Données > Fonctions nommées). Dans cet interface (un panneau latéral), nous devons remplacer les références de cellules/plages dans la formule par des espaces réservés (du texte) pour les entrées.
Par exemple, vous pouvez remplacer A2:A dans la formule par un texte significatif comme colonne_reference.
Voilà la différence entre la création de fonctions personnalisées dans Excel et Google Sheets.
Le rôle de LAMBDA
Alors, pourquoi devrions-nous avoir la fonction LAMBDA dans Google Sheets ?
Elle a quelques fonctions auxiliaires associées (LHFs), et cela peut faire un excellent combo.
De plus, nous pouvons utiliser les LHFs pour créer une fonction nommée dans Google Sheets. Nous en discuterons dans un prochain tutoriel.
Comment créer une fonction nommée dans Google Sheets
Ci-dessous, nous allons convertir deux formules utiles en fonctions nommées.
SEQUENCE_NB (un argument)
Tout d’abord, nous avons besoin d’une formule fonctionnelle pour créer une fonction nommée dans Google Sheets.
Commençons par une formule qui saute les lignes vides dans une numérotation séquentielle.
Vous pouvez trouver un certain nombre de formules pour ce type de numérotation dans Google Sheets, en voici une :
=TableauArray(formule(si(len(B2:B9),countifs(ligne(B2:B9),"<="&ligne(B2:B9),len(B2:B9),">0"),"")))Dans cette formule Google Sheets, nous avons une référence de plage unique, à savoir B2:B9. Ainsi, la fonction personnalisée ne nécessitera qu’un seul argument (espace réservé).
Étapes :
- Copiez la formule ci-dessus.
- Allez dans Données > Fonctions nommées > Ajouter une nouvelle fonction.
- Saisissez le nom de la fonction, c’est-à-dire SEQUENCE_NB. Lorsque vous définissez un nom de fonction, évitez d’utiliser les types suivants :
- Tout nom de fonction intégrée dans Sheets.
- Les valeurs booléennes TRUE ou FALSE.
- La syntaxe A1 ou R1C1.
- Les espaces et les caractères spéciaux, à l’exception du tiret bas.
- Un nom commençant par un chiffre.
- Les noms longs, c’est-à-dire plus de 255 caractères.
- Ajoutez une description significative de la fonction. Par exemple, indiquez « Retourne la séquence de valeurs dans une colonne en sautant les cellules vides. »
- Dans les espaces réservés pour les arguments, entrez le nom de l’argument, c’est-à-dire column_ref_1.
- Par exemple, si vous consultez la syntaxe de la fonction MAX intégrée, qui est MAX(valeur1, [valeur2, …]), les espaces réservés des arguments sont valeur1 et valeur2.
- Ici aussi, les points 3, 4 et 5 du troisième point ci-dessus s’appliquent spécifiquement.
- Dans la définition de la formule, collez la formule précédemment copiée et modifiez-la en remplaçant B2:B9 par l’espace réservé column_ref_1. Elle sera alors la suivante :
=TableauArray(formule(si(len(column_ref_1),countifs(ligne(column_ref_1),"<="&ligne(column_ref_1),len(column_ref_1),">0"),"")))- Cliquez sur le bouton « Suivant » et remplissez les détails supplémentaires pour le nom de l’espace réservé (column_ref_1). Par exemple, si vous avez écrit « The sequence based on values in this range. » (La séquence basée sur les valeurs de cette plage).
- Enfin, indiquez un exemple d’argument (colum_ref_1). Ici, j’ai entré B2:B10.
- Cliquez sur le bouton « Créer ».
Vous avez créé votre première fonction nommée dans Google Sheets.
Maintenant, vous pouvez utiliser cette fonction de la même manière que n’importe quelle autre fonction intégrée, depuis n’importe quelle feuille de calcul de ce classeur.
FLIP_DATA (deux arguments)
Dans cet exemple, créons une fonction nommée FLIP_DATA.
Comme son nom l’indique, nous pourrons utiliser cette fonction pour inverser une plage de données dans Google Sheets.
La formule suivante inversera la plage A2:C5.
=ORDONNER(A2:C5,LIGNE(A2:A5)*N(A2:A5<>""),0)Cette formule comporte deux références de plage uniques.
Nous aurons donc besoin de deux arguments (espaces réservés) dans la fonction FLIP_DATA personnalisée, les voici : flip_range et first_col_range.
Étapes :
- Copiez la formule ci-dessus.
- Allez dans Données > Fonctions nommées > Ajouter une nouvelle fonction.
- Saisissez le nom de la fonction, c’est-à-dire FLIP_DATA.
- Saisissez une description significative de la fonction. Par exemple, indiquez « À partir d’une plage donnée, la fonction renvoie des données inversées verticalement. »
- Dans les espaces réservés pour les arguments, ajoutez les noms des arguments, c’est-à-dire flip_range et first_col_range.
- Dans la définition de la formule, collez la formule précédemment copiée et modifiez-la comme suit :
=ORDONNER(flip_range,LIGNE(first_col_range)*N(first_col_range<>""),0)- Cliquez sur le bouton « Suivant » et remplissez les détails supplémentaires pour le nom de l’espace réservé. Pour flip_range, indiquez « L’étendue des données à inverser verticalement », et pour first_col_range, indiquez « La référence de la première colonne dans l’étendue des données à inverser ».
- Enfin, indiquez les exemples d’arguments. Ici, j’ai entré A2:C5 et A2:A5, respectivement.
- Cliquez sur le bouton « Créer ».
Nous pouvons utiliser la fonction nommée que nous venons de créer pour inverser des données dans une plage de une ou plusieurs colonnes.
Comment supprimer ou modifier des fonctions nommées dans Google Sheets
Pour supprimer ou modifier des fonctions nommées, allez dans Données > Fonctions nommées.
Vous verrez toutes vos fonctions personnalisées.
Cliquez sur les trois points verticaux à côté d’une fonction et choisissez « Modifier » ou « Supprimer ».
C’est aussi simple que ça !
Veuillez noter qu’il n’y a pas d’option pour supprimer plusieurs fonctions nommées en une seule fois.
Importer des fonctions nommées d’un classeur à un autre
Créez toutes vos fonctions nommées requises dans une feuille, puis importez-les dans d’autres feuilles.
Supposons que vous ayez les deux fonctions personnalisées mentionnées précédemment dans le fichier « Google Sheets Named Functions ». Vous pouvez laisser ce fichier ouvert ou fermé.
Voyons comment importer ces deux fonctions nommées dans un autre classeur.
Étapes :
- Ouvrez un nouveau fichier Google Sheets.
- Allez dans Données > Fonctions nommées.
- Cliquez sur « Importer une fonction ».
- Sélectionnez le fichier dans lequel vous avez créé les deux fonctions nommées.
- Cliquez sur « Tout importer » ou sélectionnez la fonction que vous souhaitez importer, et voilà !
De cette façon, nous pouvons créer et réutiliser des fonctions nommées dans Google Sheets.
Mes fonctions nommées créées dans Google Sheets
- PUNCH_IN_OUT_SAME_ROW : Pour copier l’heure de sortie dans la ligne d’entrée.
- COPY_TO_MASTER_SHEET : Combine les données de plusieurs onglets dans Google Sheets.
- MERGE_TABLE_REMOVE_DUPLICATES : Supprime les doublons par colonne clé.
- COMPARE_ALL_COLUMNS : Compare toutes les colonnes les unes avec les autres pour trouver des doublons.
- NEXT_RENEWAL_DATE : Obtenez la prochaine date de renouvellement (mensuelle et annuelle).
- AGE_CALC : Calcul de l’âge ou de la durée.
- REPT_ROWS : Répète chaque ligne dans un tableau en variant N.
- GANTT_CHART : Une manière simple de créer des diagrammes de Gantt.
- LIST_ALL_DATES : Génère toutes les dates entre deux dates données ou plus.
- AT_EACH_CHANGE : Agrège les résultats en fonction des changements de valeur des lignes.
- CUSUM_BY_GROUP : Total cumulatif par groupe (élément, mois ou année).
- SPARKLINE_NEGATIVE_BAR : SPARKLINE pour un graphique à barres positif et négatif (formule matricielle).
- REF_SHEET_TABS : Référence à une liste de noms d’onglets dans une requête dans Google Sheets.
- _3D : Crée une référence 3D dans Google Sheets.
- SPLIT_EXPENSES : Divise les dépenses de groupe.
- NUMBERTOWORDS : Convertit les nombres en mots dans Google Sheets.
- CUSTOMTIMESLOTS (nouveau !) : Crée des séquences de créneaux horaires personnalisés.
- TOSENTENCECASE (nouveau !) : Convertit une chaîne de caractères spécifiée en majuscules.
Merci de votre lecture et profitez-en bien !
Pour plus d’astuces et d’informations sur Google Sheets, visitez Crawlan.com.
