Bonnes pratiques pour les fonctions personnalisées dans Excel

Cet article contient des conseils, des bonnes pratiques et des informations sur l’écosystème des compléments Office pour les nouveaux développeurs de compléments de fonctions personnalisées.

Le diagramme suivant illustre l’interaction entre une fonction personnalisée et les deux composants main impliqués dans les compléments de fonction personnalisée : Excel et les services externes.

Excel vous permet d’intégrer vos propres fonctions personnalisées dans l’application et de les exécuter comme des fonctions intégrées.

Le complément fonctions personnalisées définit la logique de vos fonctions et la façon dont elles interagissent avec les API JavaScript Excel et Office. Pour savoir comment créer un complément de fonctions personnalisées, consultez le tutoriel fonctions personnalisées.

Un service externe est facultatif. Il peut donner à votre complément des fonctionnalités telles que l’importation de données à partir de l’extérieur du classeur. Le complément fonctions personnalisées spécifie la façon dont les données externes sont incorporées dans le classeur. Pour plus d’informations, consultez Recevoir et gérer des données avec des fonctions personnalisées.

Optimiser l’efficacité du recalcul des fonctions personnalisées

En général, le recalcul des fonctions personnalisées suit le modèle de recalcul établi dans Excel. Lorsque le recalcul est déclenché, Excel entre dans un processus en trois étapes : construire une arborescence de dépendances, construire une chaîne de calcul, puis recalculer les cellules. Pour optimiser l’efficacité du recalcul dans votre complément, tenez compte du niveau d’imbrication dans vos fonctions personnalisées, du mode de calcul Excel et des limitations des fonctions volatiles.

Imbrication dans des fonctions personnalisées

Une fonction personnalisée peut accepter une autre fonction personnalisée comme argument, ce qui en fait une fonction personnalisée imbriquée. Le recalcul de la fonction personnalisée externe dépend du résultat de la fonction imbriquée, ce qui entraîne une augmentation de la consommation de temps avec chaque fonction imbriquée supplémentaire. Réduisez le nombre de niveaux imbriqués dans vos fonctions personnalisées pour améliorer l’efficacité du recalcul. Les extraits de code suivants illustrent deux approches pour ajouter des valeurs dans le classeur qui produisent des sorties similaires. L’option 1 utilise un tableau pour appeler des valeurs en tant que paramètre unique, tandis que l’option 2 appelle chaque valeur en tant que paramètre distinct, de sorte que l’option 1 est plus efficace.

Option 1 : Augmenter l’efficacité avec l’imbrication limitée

Remarque

Il s’agit de l’approche recommandée. Il utilise un tableau pour appeler des valeurs en tant que paramètre unique et éviter l’imbrication inutile. Il est donc plus efficace que l’option 2.

    /**
    * Returns the sum of input numbers.
    * @customfunction
    */
    function Add(args: number[]): number {
      let total = 0;
      args.forEach(value => {
        total += value;
      });
     
      return total;
    }

Option 2 : Une plus grande imbrication est inefficace

Remarque

Cette approche n’est pas recommandée. Les options 1 et 2 produisent des sorties similaires, mais l’option 2 utilise davantage de paramètres et est moins efficace.

    /**
    * Returns the sum of two numbers.
    * @customfunction
    */
    function Add(arg1: number, arg2: number): number {
      return arg1 + arg2;
    }

Modes de calcul Excel

Excel propose trois modes de calcul : Automatique, Automatique sauf les tables et Manuel. Pour déterminer le mode de calcul qui convient le mieux à votre conception de fonction personnalisée, reportez-vous à la section Modes de calcul, Commandes, Recalcul sélectif et Table de données dans l’article Recalcul main Excel.

Définissez le mode de calcul de votre complément avec l’énumération Excel.CalculationMode en fonction de votre scénario. Notez que automatic le mode de calcul peut déclencher souvent un recalcul et réduire l’efficacité de votre complément.

Limitations des fonctions volatiles

Les fonctions personnalisées vous permettent de créer vos propres fonctions volatiles, comme les NOW fonctions et TODAY dans Excel. Pendant le recalcul, Excel évalue les cellules qui contiennent des fonctions volatiles et toutes leurs cellules dépendantes. Par conséquent, l’utilisation de nombreuses fonctions volatiles peut ralentir le temps de recalcul. Par conséquent, limitez le nombre de fonctions volatiles dans votre complément pour optimiser l’efficacité. Pour plus d’informations, consultez Fonctions volatiles et non volatiles.

Concevoir des approches pour améliorer l’efficacité

Les compléments de fonctions personnalisées permettent des conceptions flexibles, ce qui signifie que différentes conceptions de complément peuvent produire la même sortie pour vos utilisateurs finaux.

Résultats multiples

Vous pouvez retourner plusieurs résultats à partir de votre fonction personnalisée avec plusieurs fonctions ou avec une seule fonction.

Pour retourner plusieurs résultats avec une seule fonction, utilisez un tableau dynamique. Il s’agit généralement de l’approche recommandée, car les tableaux dynamiques nécessitent uniquement la mise à jour d’une seule cellule pour déclencher le recalcul de tous les résultats.

Gardez à l’esprit que l’utilisation de tableaux dynamiques devient moins efficace plus votre jeu de données est volumineux, car chaque recalcul traite plus de données. Pour en savoir plus sur les tableaux dynamiques dans les fonctions personnalisées, consultez Retourner plusieurs résultats à partir de votre fonction personnalisée.

Une autre façon de retourner plusieurs résultats consiste à utiliser plusieurs fonctions et à retourner un seul résultat pour chaque fonction. L’un des avantages de l’utilisation de plusieurs fonctions est que votre utilisateur final peut décider avec précision de la formule qu’il souhaite mettre à jour, puis déclencher uniquement le recalcul de cette formule. Cela est particulièrement utile lorsque vous vous appuyez sur des services externes qui peuvent répondre lentement.

Structures de données complexes

Les types de données sont le meilleur moyen de gérer des structures de données complexes dans les compléments de fonctions personnalisées. Les types de données prennent en charge les erreurs Excel et les nombres mis en forme en double. Les types de données permettent également de concevoir des cartes de valeur d’entité, en étendant les données Excel au-delà de la grille à 2 dimensions.

Améliorer l’expérience utilisateur des appels à des services externes

Les fonctions personnalisées peuvent extraire des données à partir d’emplacements distants au-delà du classeur, comme le web ou un serveur. Pour plus d’informations sur la récupération de données à partir d’un service externe, consultez Recevoir et gérer des données avec des fonctions personnalisées. Pour maintenir l’efficacité lors de l’appel de services externes, envisagez de traiter par lot les appels externes, de réduire la durée d’aller-retour pour chaque appel et d’inclure des messages dans votre complément pour communiquer les retards à votre utilisateur final.

Appels distants de fonction personnalisée par lot

Si vos fonctions personnalisées appellent un service distant, utilisez un modèle de traitement par lots pour réduire le nombre d’appels réseau au service distant. Pour plus d’informations, consultez Traitement par lot des appels de fonction personnalisée pour un service distant.

Réduire la durée des allers-retours

Les connexions de service distant peuvent avoir un impact important sur les performances des fonctions personnalisées. Pour réduire cet impact, utilisez les stratégies suivantes :

• Le traitement à forte charge du serveur doit être géré efficacement sur le serveur distant afin de raccourcir la latence de bout en bout pour une fonction personnalisée. Par exemple, l’informatique parallèle est conçue sur le serveur. Si votre service est déployé sur Azure, envisagez l’optimisation à l’aide du calcul hautes performances sur Azure.
• Réduisez le nombre d’appels de service en optimisant le flux de complément. Par exemple, envoyez uniquement les appels nécessaires à un service distant.

Améliorer les performances perçues par l’utilisateur grâce à l’expérience utilisateur du complément

Lorsqu’une fonction personnalisée appelle un service externe, la cellule avec la fonction personnalisée affiche l’erreur #BUSY ! . Si un retard lors de l’appel d’un service externe est inévitable, envisagez de fournir des messages via le volet Office du complément pour expliquer le délai à vos utilisateurs finaux. Ces informations permettent de gérer leurs attentes. L’image suivante montre un exemple.

Pour plus d’informations sur le partage de données entre une fonction personnalisée et un volet Office, voir Partager des données et des événements entre des fonctions personnalisées Excel et le volet Office.

Pour afficher un message dans le volet Office du complément qui avertit les utilisateurs d’un délai, apportez les modifications suivantes après vous être assuré que votre complément utilise un runtime partagé.

1. Dans taskpane.js ajoutez une fonction qui appelle la notification.

   export function showNotification(message){
     const label = document.getElementById("item-subject");
     label.innerHTML = message;
   }
   

2. Dans function.js, importez la showNotification fonction .

   export function showNotification(message){
     const label = document.getElementById("item-subject");
     label.innerHTML = message;
   }
   

3. Dans function.js, appelez showNotification lors de l’exécution du calcul qui peut inclure un délai.

   export async function longCalculation(param) {
     await Office.addin.showAsTaskpane();
     showNotification("It may take some time as we prepare the data.");
     // Perform long operation
     // ...
     // ...
     return answer;
   }
   

Voir aussi

• Recevoir et gérer des données à l’aide de fonctions personnalisées
• Appels de fonction personnalisée par lot pour un service distant
• Tutoriel Créer des fonctions personnalisées dans Excel