---

Janvier 2018

Volume 33, numéro 1

Cet article a fait l'objet d'une traduction automatique.

Office - analyse de données, apprentissage automatique et Microsoft Excel

Par Michael Saunders | Janvier 2018

Prenez réfléchir à Excel dans son ensemble, il s’agit d’un outil très répandue, puissant et flexible pour analyser les données de ma feuille de calcul de liste des tâches peu pratique, tout en massive classeurs de modèle financier de 100 Mo sur les banques supérieur au monde. Et en dessous de toutes les fonctionnalités sophistiquées derrière les graphiques efficace et la mise en forme, la valeur réelle est un calcul, appelé « Calc » en interne. Calcul est ce qui vous permet de créer des relations entre les cellules : Express de modèles complexes en écrivant des formules simples pour décrire les arborescences de dépendances entre les valeurs (consultez Figure 1). Et ensuite, dès que vous apportez une modification, Calc met à jour les valeurs dépendantes, en fonction de ces formules.

Figure 1 Calc vous permet de créer des relations entre les cellules

Excel fournit tonnes de fonctions utiles à utiliser dans les formules, à partir de la fonction AVERAGE essentielle (bit.ly/1Tjynwl), aux fonctions d’analyse de la chaîne comme une recherche pour trouver les sous-chaînes (bit.ly/2jhcEuV), pour des calculs plus complexes telles que la fonction statistique T.TEST (bit.ly/2ipowKE). Au travail et à l’école apprendre les fonctions à utiliser dans les formules Excel, et ils interagissent avec les naturellement à leurs besoins quotidiens de calcul. Si vous souhaitez fournir une fonctionnalité puissante Excel n’a pas encore, vous devez donc créer une fonction Excel à cet effet. Consultez Figure 2 pour obtenir un exemple : Les cryptographes Contoso Corp. souhaite libérer une solution qui permet d’analyser les données dans Excel pour décoder les messages sleuths. Et un des outils utiles pour rupture du code consiste à identifier les nombres premiers rapidement, Contoso aimerions avoir une fonction ISPRIME des millions de détectives qui utilisent déjà Excel. Plus loin dans cet article, vous verrez comment Contoso génère cette fonction et les autres fonctionnalités que nécessaires à son complément.

Figure 2 à la fonction de Contoso pour identifier les nombres premiers

Que puis-je générer ?

Avant de commencer la génération, considérez que pas de toutes les extensions pour les fonctionnalités Excel doivent utiliser des formules Excel. Pour décider si vous devez, posez-vous la question : « Ma fonction doit-elle change rien à l’exception de la valeur d’Excel qui est retournée ? » En d’autres termes, la fonction n’a pas les effets secondaires ? Dans Excel, formules de modifications l’exception de la cellule dans laquelle ils sont entrés ne prévoyez pas personnes. Cette modification peut déclencher d’autres modifications, mais celles serait également les résultats de formules autonomes sans effets secondaires. Lorsque vous tapez « = SUM() » dans la cellule A1, vous ne prévoyez pas sembler quelque part sur la feuille ou une nouvelle ligne à ajouter en dessous ou un nombre doivent être modifiées dans une base de données financière sur Internet, vous pouvez contrôler tous les comportements dans le cadre d’une solution que vous générez un graphique , qui peut contenir des fonctions et autres fonctionnalités.

Le type de solution que doit être généré pour créer votre fonction Excel dépend de vos compétences et vos objectifs. Si vous êtes un développeur professionnel, de vente comme externe Contoso cryptographes ou création de personnes de votre organisation, un complément est l’outil approprié pour les solutions d’analytique de données dans Excel, comme expliqué dans les fonctions JavaScript personnalisé » dans Section compléments Excel. » Si vous êtes un spécialiste des données afin de générer des modèles intelligents pour les analystes de données à utiliser dans votre entreprise ou un développeur de AI, consultez la section « Azure Machine Learning fonctions » plus loin dans cet article. Et, si vous n’avez aucun de ces compétences, Microsoft, des solutions pour vous dans le futur !

Les fonctions JavaScript personnalisé dans des compléments Excel

Compléments Excel sont des outils que les développeurs professionnels peuvent créer pour étendre Excel et d’interagir avec les données dans les classeurs (bit.ly/2AUOsZk). Des milliers de modules complémentaires sont déjà disponibles et elles s’exécutent toutes entre les plateformes d’Excel, notamment Windows, Mac, Excel Online et même iOS. Toute personne familiarisé avec les technologies Web recherche des compléments faciles à générer, car elles sont écrites comme des pages Web : Exécutent le code HTML, JavaScript et CSS et les compléments ont la possibilité d’appeler des serveurs Web externes. De plus, les compléments n’avez pas besoin des modifications du code pour s’exécuter sur différentes plateformes. (Excel prend également en charge autres types de modules complémentaires, comme décrit dans bit.ly/2qsPfLe, mais ils ne peuvent pas s’exécuter sur plusieurs plateformes ou être déployés dans le magasin.) Figure 3 illustre les éléments clés d’un complément. Le principal est le fichier manifest.xml, qui spécifie l’emplacement Web du reste de la macro complémentaire et d’autres métadonnées. Le reste du code est déployé par le développeur, comme avec toute autre application Web.

Figure 3 les éléments clés d’un complément

Les compléments fournissent un grand nombre de façons utile d’étendre Excel (consultez la documentation relative à bit.ly/2AV47rw). Ils peuvent ajouter des éléments d’interface utilisateur, telles que les boutons de ruban, options de menu contextuel et les volets de tâches HTML et les boîtes de dialogue. Ils peuvent interagir avec des classeurs via des milliers d’API, telles que la possibilité d’ajouter et supprimer des lignes, insérer et modifier des graphiques et même appliquer la mise en forme aux cellules. Et maintenant, avec la nouvelle version préliminaire de fonctions personnalisées de JavaScript, il est possible d’étendre les formules Excel.

Des fonctions personnalisées permettent aux développeurs d’ajouter n’importe quelle fonction JavaScript vers Excel à l’aide un complément (bit.ly/2AYtNUW). Les utilisateurs peuvent accéder ensuite les fonctions personnalisées, comme toute autre fonction native d’Excel (par exemple, en moyenne). Jetons un œil plus près à la fonction Contoso cryptographes ISPRIME pour voir comment il est écrit (il est également possible de vérifier primality à l’aide uniquement les fonctions Excel intégrées, mais qui est beaucoup plus difficile pour la personne qui demande le calcul.) :  

function isPrime(n) {
  var root = Math.sqrt(n);
  if (n < 2) return false;
  for (var divisor = 2; divisor <= root; divisor++){
    if(n % divisor == 0) return false;
  }
  return true;
}

La fonction vérifie simplement tous les diviseurs possible jusqu'à la racine carrée pour déterminer si l’entrée est prime : quelques lignes de JavaScript. Toute la logique réelle est déjà faite. Mais il n’est plus à écrire que la fonction elle-même. Pour être un point d’extension efficace, des fonctions personnalisées doivent apparence comme ceux natif. Et les fonctions natives ont un ensemble d’informations personnalisés. Examinez Figure 4 pour obtenir un exemple avec la fonction Contoso Crypographers ISPRIME : En plus du nom de la fonction elle-même, Excel affiche une description utile pour clarifier l’objectif de la fonction.        

Figure 4 des fonctions personnalisées et des fonctions natives complète automatiquement en cours de frappe

Vous pouvez voir un autre exemple dans Figure 5. Après avoir CONTOSO. ISPRIME est sélectionné, Excel affiche le nom du paramètre (dans ce cas, n) pour faciliter l’utilisation récupérer les entrées de droite.

Figure 5 la fonction ISPRIME de Contoso

Excel.Script.CustomFunctions["CONTOSO"]["ISPRIME"] = {
  call: isPrime,
  description: "Determines whether the input is prime",
  helpUrl: "https://example.com/help.html",
  result: {
    resultType: Excel.CustomFunctionValueType.boolean,
    resultDimensionality: Excel.CustomFunctionDimensionality.scalar,
  },
  parameters: [{
    name: "n",
    description: "the number to be evaluated",
    valueType: Excel.CustomFunctionValueType.number,
    valueDimensionality: Excel.CustomFunctionDimensionality.scalar,
  }],
  options: { batched: false, streaming: false }
};

À la fois de ces éléments d’information et bien plus d’informations sont fournies par le développeur dans la définition de JavaScript de métadonnées de la fonction, comme vous pouvez le voir dans Figure 5.

Vous pouvez voir que la description est spécifiée en tant que chaîne, de sorte qu’est le nom de chaque paramètre. Aborder ne sont pas toutes les métadonnées ici, mais vous pouvez consulter la documentation pour plus d’informations. Si vous êtes familiarisé avec le modèle de complément, vous vous demandez peut-être pourquoi ces informations sont fournies en JavaScript plutôt que statiquement quelque part, codé en dur comme dans le fichier manifest.xml. La raison est la flexibilité. Dans les cas de Contoso, les fonctions de chiffrement sont définies et bien connus au préalable. Mais vous pouvez parfois la possibilité d’activer des fonctions différentes dans différentes situations.

Contoso cryptographes connaissent la simplicité de leur fonction ISPRIME, mais leur objectif suivant est un peu plus difficile : Il souhaite créer une fonction pour générer des nombres aléatoires de chiffrement du texte. Excel possède une fonction RAND excellente, mais le problème est que RAND n’est pas idéale pour le chiffrement, car ses valeurs sont pseudo-aléatoire, généré de façon procédurale. En revanche, les numéros générés par random.org sont sécurisés à utiliser à cet effet, elles sont générées à partir de bruit atmosphérique. Bien entendu, il n’est aucune utilité pour coder des nombres aléatoires ; au lieu de cela, Contoso doit concevoir une fonction qui peut appeler random.org pour extraire des nombres aléatoires via une demande HTTP. Heureusement, des fonctions personnalisées permettent de facilement inclure les requêtes Web. Fonction RANDOM asynchrone de Contoso ressemble à ceci :

function getRandom(min, max) {
  return new OfficeExtension.Promise(function(setResult, setError){
    sendRandomOrgHTTP(min, max, function(result){
      if(result.number) setResult(number);
      else setError(result.error);
    });
  });
}

La principale différence entre cette fonction et les ISPRIME est aléatoire asynchrone : au lieu de retourner une valeur à Excel, il immédiatement retourne une promesse de JavaScript, puis effectue une requête XMLHttpRequest (non visible dans la fonction, il se trouve dans le sendRandomOrgHTTP méthode) pour le service random.org. Ensuite, une fois que le service Web a répondu, Contoso résout la promesse avec le nombre de random.org à écrire dans la cellule. Importation de données Web dans Excel est une des principales raisons sociétés souhaitent étendre les fonctions Excel pour Microsoft attend des fonctions asynchrones populaire.

Un aspect commune de toutes les fonctions asynchrones est qu’ils prennent une certaine quantité de temps à retourner son résultat, afin que Excel affiche un message ## CHARGEMENT_DONNEES dans la cellule en attendant que la fonction à résoudre (consultez Figure 6).

Figure 6 en attente pour une fonction asynchrone à retourner

Dans ce cas, la requête HTTP de procéder rapidement, dans environ un dixième de seconde. Mais, si nécessaire, la fonction peut apporter de nombreux appels et prendre plus de temps que celle à évaluer. Pour les fonctions personnalisées de JavaScript, la résolution de la valeur en JavaScript provoque qu’il s’affiche immédiatement dans la cellule. Figure 7 montre une vue Excel de la même fonction aléatoire une fois que la valeur est retournée.

Figure 7 une vue Excel de la fonction aléatoire après la valeur est retournée.

Bien entendu, un complément à l’aide des fonctions personnalisées peut avoir plusieurs autres fonctionnalités, ainsi : Contoso cryptographes souhaiterez peut-être créer un volet de tâches et d’un onglet de ruban pour permettre à leurs clients un accès facile aux fonctions personnalisées ; paramètres pour leur comportement ; et comment les utiliser. Peut-être l’API JavaScript peut même être utilisé pour permettre aux clients de chiffrer une feuille de calcul entière de données avec un clic sur un bouton. Quelle que soit ils décident, ils peuvent être certain que le complément entière s’exécutera sur plusieurs plateformes Excel sans modification du code.

Il existe deux façons de déployer un complément qui contient ces fonctions personnalisées de JavaScript, en fonction de l’audience. La banque publique Office (bit.ly/2A70L5o) est disponible pour toute personne disposant d’Excel, il existe même un bouton pour parcourir le Windows Store dans le ruban Excel. La banque prend en charge des compléments gratuites et payantes et nécessite l’envoi et la validation via Microsoft. Toutefois, cryptographes de Contoso Corp. déploie directement à ses clients PME afin qu’ils ne puissent cliquer sur quoi que ce soit pour l’installer. Il fournit à la place, chaque client avec le fichier de manifeste. Un administrateur informatique peut choisir les utilisateurs accéder via le centre d’administration Office 365 (voir l’interface dans Figure 8). Les utilisateurs d’obtenir le complément installé automatiquement.

Figure 8 déploiement d’une fonction personnalisée à l’organisation

Nous espérons que vous avez été inspirés par la pleine de ressources cryptographes Contoso pour essayer d’écrire vos propres fonctions personnalisées (utiliser le guide et les exemples sur aka.ms/customfunctions). Ensuite, je vais aborder l’autres nouvelles permettent d’étendre les formules Excel.

Fonctions d’Azure Machine Learning

Le deuxième type de fonction extensible, que Microsoft a annoncé est fonctions d’Azure Machine Learning (abrégé Azure ML). Contrairement aux fonctions personnalisées de JavaScript, les fonctions Azure ML sont créées par des développeurs AI (souvent des chercheurs de données et d’autres experts) pour les analystes de leur organisation à utiliser. Par conséquent, il n’est pas nécessaire de créer un package de compléments pour déployer une fonction d’Azure ML.

La fonction d’Azure ML elle-même est basée sur un service qui calcule ou qui prévoit les valeurs selon un modèle d’apprentissage (ML). Une fois le modèle est généré, les créateurs activez-le pour toute personne de que leur choix. Ensuite, chaque fois qu’un utilisateur souhaite exécuter la fonction, ils simplement taper dans une cellule, comme pour toute autre fonction d’Excel. La fonction appelle un service Web en direct sur un abonnement Azure de la société et retourne le résultat de façon asynchrone.

Par exemple, un analyste marketing sur un site de vente souhaiterez prédire la demande pour les nouveaux produits dans des zones géographiques différentes. L’analyste possède des données sur ce produit et les marchés cible dans Excel. L’objectif est d’obtenir une fonction simple qui permet à l’analyste de prévision de la demande sur chaque marché sans jamais quitter Excel. Figure 9 affiche une feuille de calcul avec ce type de données.

Figure 9 exemple produit et les données de marché qui seront analysées avec une fonction d’Azure ML

Un développeur d’AI à cette organisation utilise des services Azure ML (bit.ly/2nwa0WP) pour créer le modèle : Elle commence par préparer les données d’apprentissage, généralement à partir d’une base de données externe pour créer une expérience ML en fonction des ventes historique pour les nouveaux produits. Azure banc d’essai Machine Learning est un outil qui simplifie la préparation des données et le chercheur de données vous permet d’écrire du code Python pour effectuer l’apprentissage et évaluer des modèles. La figure 10 est la vue de tableau de bord s’exécuter dans Azure ML banc d’essai, affichant des statistiques sur une expérience.

La figure 10 Azure Machine Learning banc d’essai

Une fois que le modèle est prêt, il peut être déployé comme un service Web dans un abonnement Azure de l’organisation, avec des autorisations pour l’ensemble approprié d’utilisateurs à y accéder. Au lieu de définir les métadonnées dans le code JavaScript qui s’exécute localement, les métadonnées sont définies dans un format de Swagger (API RESTful) est créé automatiquement en même temps que le service déployé. Tout ce qui a requis pour le service d’apparaître comme une fonction dans Excel est que l’analyste dans Excel a l’autorisation d’y accéder. Figure 11 affiche les fonctions déployées qui apparaissent pour les analystes de données dans Excel. Les analystes puis, exécutez la fonction normalement. Tout comme avec les fonctions personnalisées JavaScript asynchrones, la cellule affiche un message ## CHARGEMENT_DONNEES pendant que le service calcule le résultat.

Figure 11 les fonctions déployées dans Excel

L’avenir de fonctions Extensible dans Excel

J’espère que vous allez essayer les aperçus de technologie pour les fonctions extensibles dans Excel, des fonctions JavaScript personnalisées et les compléments si vous êtes un logiciel ou fournisseur de services et Azure ML fonctionne si vous êtes un spécialiste des données ou développeur AI. Microsoft est à l’écoute avec soin pour tous les commentaires dans la version préliminaire (valider des idées à la page de UserVoice à bit.ly/2jRJQsu), en plus de l’obtention d’un point de départ, vous aurez également la possibilité de mettre en forme la version finale du produit . Il existe de nombreuses améliorations plus entrantes sur les deux de ces types de fonction et entièrement nouveaux, restent validées pour les annonces futures !

---

Michael Saundersest responsable de programme de l’équipe Office, où il crée des fonctionnalités d’Excel pour les développeurs. Il est à l’origine à partir de Toronto, Canada, et il étudié Materials Science Engineering au niveau de l’Université de Pennsylvanie et Wharton et gestion. Pendant son temps libre, Saunders chante avec le Esoterics de Seattle et développe les compléments.

Merci aux experts techniques Microsoft suivants d'avoir relu cet article : Yina Ashvini Sharma, l’utilisation, Sandhya Vankamamidi
L’utilisation Yina est responsable de programme principal chez Microsoft et responsable de Microsoft Graph. Elle prend Office et Microsoft APIs des technologies existantes et disjoints à un nouveau monde API unifié.
Ashvini Sharma est responsable de programme de l’équipe Excel responsable de la feuille de route et la vision d’Analytique d’Excel.
Sandhya Vankamamidi est un responsable Responsable de programme dans l’équipe d’informations et d’expériences de consommation de contenu. Sandhya est responsable de la conception et la fourniture des expériences réalistes sur toute la gamme d’applications de Bing.

---

Discussion sur cet article sur le forum MSDN Magazine