TUTO EXCELLONS : CAPTURONS et TRANSFORMONS les données du WEB

De plus de plus de données sont accessibles à partir de pages WEB.

Grâce à ce tutoriel capturons et transformons les données du WEB avec un minimum d’effort.

(Les copies-écrans du présent document sont issues d’EXCEL version 365)

QUELLES DONNEES WEB SONT RECUPERABLES DANS EXCEL ?

Avant de nous lancer dans la récupération de données capturées sur le WEB, nous devons nous assurer que ces données sont récupérables dans EXCEL. D’une façon générale nous pourrons récupérer les données d’une page WEB se présentant sous la forme d’un tableau. Pour nous en assurer, il suffit de nous rendre sur la page WEB visée, de copier le tableau de données voulu (ctrl+C) et le coller dans une feuille EXCEL vierge (ctrl+V). Si, après cette opération, nous obtenons les données en colonnes avec entêtes dans EXCEL nous avons de bonnes chances de pouvoir utiliser avec succès les fonctions de récupération de données.
Site cours Crypto-monnaies
Pour ce tutoriel, nous nous proposons de récupérer les cours des crypto-monnaies (Bitcoin et autres…) à une fréquence quotidienne. Pour ce faire, nous utiliserons le site courscryptomonnaies.com  pour récupérer les données qu’il expose. Pour simplifier, le site WEB utilisé se trouve en accès libre (accès anonyme). Il est tout à fait possible de récupérer également des données sur des pages WEB réclamant une connexion explicite « Code Utilisateur/Mot de passe ». Ce tutoriel ne détaille pas les étapes à suivre pour ce type de connexion (si besoin, se reporter à la page https://learn.microsoft.com/fr-fr/power-query/connector-authentication)

CREONS UN LIEN AVEC LE SITE WEB

Ouvrons un nouveau classeur EXCEL et dans l’onglet « Données » du ruban, cliquons sur l’icône ‘A partir du WEB’ :
Etablir avec lien avec le site WEB

Dans la fenêtre qui s’ouvre, indiquons l’adresse (l’URL) du site WEB désiré :

URL Site Cours Crypto

Cliquons sur OK.

Le formulaire « Navigateur » s’ouvre. Sélectionnons « Table » dans sa partie gauche :

Navigateur Site WEB

Dans sa partie droite du formulaire « Navigateur » affiche un aperçu des données voulues puis, dans le bas du formulaire, sélectionnons « Charger dans… » de la liste déroulante :

Charger Dans

Dans le fenêtre « Importation des données », choisissons « Insérer les données dans » la feuille existante en $A$1 puis cliquons ur OK :

Importation des Données

Après le temps nécessaire pour le chargement des données, nous obtenons le tableau suivant dans le feuille EXCEL :

Resultat Importation

PROGRAMMONS L’ ACTUALISATION DES DONNEES

  Dans le ruban, après avoir sélectionné une cellule quelconque du tableau obtenu, cliquons la liste déroulante ‘Actualiser tout’ et choisissons ‘Propriétés de connexion… ‘ :
Proprietes de la Connexion

Dans le formulaire « Propriétés de la requête » sélectionnons :

  • Activer l’actualisation en arrière-plan
  • Actualiser toutes les 360 minutes (6 heures) ou indiquer une autre périodicité
  • Actualiser les données lors de l’ouverture du fichier.
Mise à jour des Proprietes

PERSONNALISONS LES DONNÉES RECUPÉRÉES

Maintenant que nos données sont récupérées, nous aimerions bien améliorer leur présentation :

  • Nous voulons supprimer la première colonne (« Rang ») et la dernière colonne (« Détail ») sans intérêt pour nous.
  • Nous voulons trier les cours sur la colonne « Variation (24h) » en ordre décroissant pour obtenir le hit-parade des crypto-monnaies relativement à leur variation d’un jour à l’autre.

Après avoir sélectionné une cellule quelconque de notre tableau, cliquons sur « Requêtes et connexions » dans l’onglet « Données » du ruban, puis sur « Table 0 » dans la partie droite ouverte dans le classeur :

Activer l'Editeur Power Query

L’ Editeur Power Query EXCEL s’ouvre .

    • Commençons par supprimer les données indésirables : sélectionnons les colonnes « Rang » et « Détails » et cliquons sur l’icône « Supprimer les colonnes ».
Editeur Power Query

Dans le cas où nous voudrions trier sur la colonne ‘Variation (24h)’ les choses seront plus complexes : cliquons sur l’entête ‘Variation (24h)’ et sélectionnons ‘tri décroissant’. Le résultat obtenu n’est pas celui attendu. Cela tient au fait que les données de cette colonne sont stockées sous format ‘Texte’ et non sous format ‘Numérique’.

Il nous faut alors avoir recours à la transformations de données et procéder en plusieurs étapes. 

  1. Effaçons le signe « % » :
    • Sélectionnons la commande « Remplacer les valeurs » :
Remplacer Valeurs

Dans la fenêtre qui s’ouvre, saisissons le caractère ‘%’ dans le champ ‘Valeur à rechercher’ et laisson vide le champ ‘Remplacer par’ et cliquons sur ‘OK’ :

Remplacer Valeurs

2. Remplaçons le séparateur décimal point ‘.’ par une virgule ‘, ‘

    • Sélectionnons une nouvelle fois la commande « Remplacer les valeurs » :
Remplacer Valeurs

3. Transformons les données de la colonne numérique décimal.

    • Sélectionnons l’onglet ‘Transformer’ puis, dans la liste déroulante ‘Type de données’ choisissons ‘Nombre décimal’ :
Transformer Decimal

4. Divisons les données par 100

    • Dans l’onglet « Transformer», sélectionnons « Standard » puis dans la liste déroulante sur « Diviser ».
Activer Diviser

 

    • Dans la fenêtre qui s’ouvre, indiquons la valeur ‘100’ et cliquons sur OK :
Diviser colonne Par 100

5. Sélectionnons l’onglet « Accueil » puis « Fermer et charger»:

Résultat Transformations

Dans le feuille EXCEL, les données transformées se présentent ainsi :

Résultats Excel

Pour finaliser la présentation, cliquons sur la colonne ‘Variation (24h)’ puis, dans le volet « Accueil », partie   « Nombre », choisissons « Pourcentage » :

Mise en forme Pourcentage

Enfin, trions sur la colonne ‘Variation (24h)’ avec le chois ‘Trier du plus grand au plus petit‘ :

Trier Descendant

A l’issue des diverses transformations, nous obtenons le tableau suivant :

Resultat Final

OFFICE SCRIPTS – Une Introduction

 

OFFICE SCRIPTS c’est quoi ?

Globalement,  Office Scripts est pour Excel mode online l’équivalent de ce que l’enregistreur de macros et de l’éditeur VBE est pour EXCEL mode bureau.

Nous écrivons les scripts d’OFFICE SCRIPTS en langage TypeScript lui-même hérité du langage JavaScript (ce qui rapproche ces scripts de ceux écrits en Google Apps Script pour les GOOGLE SHEETS).

Usage basique d’OFFICE SCRIPTS

Sur le même principe que l’enregistreur de macros d’EXCEL mode bureau, l’enregistreur d’actions nous permet d’enregistrer une série d’actions dans EXCEL online, de donner un nom à cette série puis de la reproduire en l’exécutant.

Usage avancé d’OFFICE SCRIPTS

Sur le même principe que l’éditeur VBE d’EXCEL mode bureau, l‘éditeur de code intégré nous permet de créer des scripts à partir de rien ou de modifier les scripts générés par l’enregistreur d’actions. Avant de nous lancer dans l’écriture de code, nous devons posséder un minima de connaissance du langage TypeScript. L’investissement nécessaire à cet apprentissage dépend grandement de notre connaissance préalable de la programmation (Si nous maîtrisons le langage Javascript, cet investissement devrait être relativement léger).

Ce qu’il nous faut pour bénéficier d’OFFICE SCRIPTS

Pour pouvoir bénéficier d’OFFICE SCRIPTS, nous devons disposer d’un des abonnements Microsoft 365 listés ici.

Dans sa version de mars 2022, OFFICE SCRIPTS n’est pas disponible dans EXCEL mode bureau.

Lorsque nous disposons d’un abonnement Microsoft 365 éligible à OFFICE SCRIPTS, l’onglet Automatiser est ajouté au ruban EXCEL mode online :

Menu_Debogage_Compiler

Trouvons de l’aide sur OFFICE SCRIPTS

En mars 2022 le produit OFFICE SCRIPTS est encore récent et, de ce fait, l’offre d’aide est encore limitée.

La documentation en ligne d’OFFICE SCRIPTS de Microsoft est accessible ici.

Nous disposons de vidéos de présentation d’Office Scripts. En premier lieu celles réalisées par Microsoft ou encore celles réalisées par d’autres contributeurs : celle de Kevin Stratvert (en anglais) ou celle de Leila Gharani (en anglais) par exemple.

Au moment de l’écriture de cet article, aucune formation dédiée à Office Scripts n’a pu être trouvée. A défaut, nous pouvons, si besoin, nous former à JavaScript. La plateforme de formations Udemy en propose plusieurs dont certaines réalisées par des formateurs de langue française.

Nous pouvons soumettre nos problèmes et questions sur les forums d’échanges en français tel que celui d’EXCEL-PRATIQUE auquel nous contribuons.

Voyons les différences essentielles entre OFFICE SCRIPTS et VBA

(Constat de mars 2022)

Portée d’Office Scripts versus portée du VBA

  • Alors que le VBA n’est utilisable dans EXCEL qu’en mode bureau, a contrario, OFFICE SCRIPTS n’est (actuellement) disponible qu’en mode online.
  • Cependant, il semble que Microsoft ait la volonté d’étendre l’usage OFFICE SCRIPTS à l’avenir. Pour autant, compte-tenu du nombre considérable de solutions VBA actuellement en œuvre, il est quasi certain que la fin du VBA n’est pas pour demain.

Stockage des Scripts

  • Avec le VBA les macros sont stockées dans le même fichier que le classeur alors que les fichiers de scripts OFFICE SCRIPTS (d’extension .osts) sont stockés dans le dossier “Scripts Office” de notre espace OneDrive. Cela offre l’avantage de pouvoir aisément et à notre convenance partager nos scripts entre nos divers classeurs et d’en faire bénéficier d’autres utilisateurs de notre organisation.
Menu_Debogage_Compiler

Déclaration des objets manipulés

  • Alors que VBA tolère la déclaration implicite des objets manipulés dans un classeur, OFFICE SCRIPTS impose une déclaration explicite de tout objet manipulé. Ainsi par exemple pour l’objet ‘worksheet’, VBA (à défaut d’une déclaration explicite) considère la feuille sélectionnée dans le classeur comme étant la feuille manipulée, alors que pour OFFICE SCRIPTS la déclaration explicite de la feuille manipulée est impérative. Nous réalisons cette déclaration dans le code TypeScript avec une instruction let :
Menu_Debogage_Compiler

Référencement par indice

  • Une différence essentielle : le référencement des cellules par indice.
    Dans VBA le référencement des cellules par indice a pour base 1 alors que dans OFFICE SCRIPTS le référencement des cellules par indice a pour base 0.
    Ainsi, pour référencer par indice la cellule “A1” de la feuille active, dans VBA nous écrivons :
    set oCell = Activesheet.Cells(1,1)
    alors que dans un script OFFICE SCRIPTS nous écrivons :
    let oCell = SelectedSheet.getCells(0,0)

Déclenchement des scripts

  • Dans la version de mars 2022, OFFICE SCRIPTS est capable de déclencher l’exécution de script au moyen d’un bouton déposé sur la feuille du classeur.
    Contrairement aux macros VBA qui peuvent être déclenchées sur un évènement (par exemple lorsque la valeur d’une certaine cellule change) OFFICE SCRIPTS n’en a pas la capacité dans sa version actuelle (gageons que ces limitations évolueront dans un futur proche, restons vigilants…).
  • Au demeurant, l’application POWER AUTOMATE (incluse dans les abonnements Microsoft 365) nous permet de déclencher les scripts OFFICE SCRIPTS sur certains évènements comme : l’arrivée d’un mail dans OUTLOOK 365 ou la survenue d’une heure ou d’une date déterminée.

Interaction avec les utilisateurs

  • Encore une différence essentielle : dans la version mars 2022, OFFICE SCRIPTS ne nous permet pas d’interagir avec les utilisateurs.
    Avec VBA nous interagissons avec les utilisateurs au moyen de formulaires “userforms” ou avec l’instruction “Input” ou bien encore avec l’instruction “Msgbox”. Toutes ces techniques sont actuellement indisponibles dans OFFICE SCRIPTS. L’application POWER AUTOMATE (incluse dans les abonnements Microsoft 365) pourrait nous offrir une alternative.

Gestion des erreurs et exceptions

  • Une bonne nouvelle : OFFICE SCRIPTS comporte une instruction ‘try…catch…finally’.
    Grâce à l’instruction ‘try…catch…finally’ OFFICE SCRIPTS nous permet de gérer les erreurs et les exceptions de façon structurée.
    (Un ouf de soulagement est poussé par les tenants de la programmation structurée, dont nous sommes, horrifiés par les instructions “On Error…” parfois incontournables en VBA).

 Ajout automatique de commentaires par Office Scripts

  • L’enregistreur d’actions OFFICE SCRIPTS ajoute automatiquement des lignes de commentaires (en anglais actuellement) commençant par ‘//’ dans le code généré. Il ajoute également un commentaire lorsqu’il n’a pas pu générer le code sur une action particulière (ceci est bien utile pour avoir connaisance de ces actions non automatisables).
  • Dans l’image suivante, les lignes de commentaires générées automatiquement sont surlignées en jaune :
Menu_Debogage_Compiler

Illustrons OFFICE SCRIPTS par un exemple

A titre d’exemple, reprenons l’application EXCELLONS ‘Jours fériés en France’ téléchargeable ici.

Application EXCEL "Jours fériés en France"

Lorsque nous ouvrons ce classeur dans EXCEL en mode bureau, si nous modifions le territoire ou l’année, le tableau des jours fériés est automatiquement mis à jour, de même que le nombre de jours ouvrés.

Par contre, lorsque nous ouvrons ce classeur dans EXCEL en mode online, si nous modifions le territoire ou l’année… OUPS! Rien ne se passe…

En effet, EXCEL mode online ne prend pas en charge ni les évènements du classeur ni les macros VBA (comme vu dans le chapitre précédent). Dans le cas où nous voudrions transposer les fonctions réalisées par VBA, il nous faut réaliser un script OFFICE SCRIPTS en TypeScript.

Une fois mis au point, ce script sera exécuté dans EXCEL mode online de la façon suivante:

Application EXCEL "Jours fériés en France"

A noter que les Office scripts se comportent de façon identique sur plateforme WINDOWS et MAC. A contrario, le code VBA de ce classeur ne peut s’exécuter sur MAC (du fait de l’absence de l’objet ‘MSXML2.XMLHTTP’ dans cette environnement).

Ci-dessous, nous fournissons l’intégralité du code Office Scripts permettant de réaliser, en environnement EXCEL Online, la mise à jour du tableau de résultats en fonction du territoire et de l’année choisis :

//OfficeScript de récupération des jours fériés en France pour un territoire donné et une année donnée.
async function main(workbook: ExcelScript.Workbook){
//On vérifie que le classeur est celui attendu
const cWorkbookName = 'JoursFeries.xslm';
let sWorkbookName: string = workbook.getName();
if (sWorkbookName = cWorkbookName) {
    try {
        //On affecte la feuille et on vérifie que la feuille active est celle attendue
        let oActiveSheet: ExcelScript.Worksheet = workbook.getWorksheet('Accueil');
        try {
            //On affecte toutes les zones nommées et on vérifie leur présence
            let sChoixZone = oActiveSheet.getRange('txtZone').getValue();
            let sChoixAnnee = oActiveSheet.getRange('txtAnnee').getValue().toString();
            let oCellStatut = oActiveSheet.getRange('txtStatut');
            let oTableau: ExcelScript.Table = oActiveSheet.getTable('tblResult');
            let oCellMsg = oActiveSheet.getRange('txtMessage');

            //On efface le message et le statut
            oCellMsg.clear(ExcelScript.ClearApplyTo.all);
            oCellStatut.clear(ExcelScript.ClearApplyTo.all);

            //On efface le contenu du tableau de résultats
            oTableau.getRangeBetweenHeaderAndTotal().clear(ExcelScript.ClearApplyTo.all)

            //On initialise la cellule statut
            oCellStatut.getFormat().getFill().setColor('orange');
            oCellStatut.setValue('En cours...');

            //On déclare l'URL racine de l'API
            const cUrl = 'https://calendrier.api.gouv.fr/jours-feries/';
            //On constitue la requête pour l'API
            const sRequestString = cUrl + sChoixZone + '/' + sChoixAnnee + '.json';
            //On constitue la formule à copier dans la colonne 'Jour de la semaine' pour obtenir le jour de la semaine
            const sFormula = "=SI(ESTNUM([@[Jour férié]]);TEXTE([@[Jour férié]];\"jjjj\");\"\")";

            //On affecte l'objet Request API
            const oRequest = await fetch(sRequestString);            
            try {
                //On teste le statut de la réponse de l'API
                if (oRequest.status === 200) {
                    //On récupère la réponse de l'API
                    const oResponseJSON: string = await oRequest.json();
                   
                    //On parse la réponse de l'API
                    const sResponse = JSON.stringify(oResponseJSON);
                    let lLen = sResponse.length;
                    let sUsageData = sResponse.substring(1, lLen - 1);
                    const aUsageData = sUsageData.split(',');

                    //On traite chaque ligne de la réponse parsée
                    for (let i = 0; i < aUsageData.length; i++) {
                        let aUsageText = aUsageData[i].split(':');

                        //On remplit la première colonne du tableau avec les dates reçues
                        let sText: string  = aUsageText[0].trim();
                        lLen = sText.length;                    
                        sText = sText.substring(1, lLen - 1);
                        oTableau.getRangeBetweenHeaderAndTotal().getCell(i, 0).setValue(sText);
                        oTableau.getRangeBetweenHeaderAndTotal().getCell(i, 0).getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.center);

                        //On remplit la deuxième colonne du tableau avec le libellé reçu
                        let sText2: string = aUsageText[1].trim();
                        lLen = sText2.length;
                        sText2 = sText2.substring(1, lLen - 1);
                        oTableau.getRangeBetweenHeaderAndTotal().getCell(i, 1).setValue(sText2);

                        //On remplit la troisième colonne du tableau avec la formule donnant le jour de la semaine
                        oTableau.getRangeBetweenHeaderAndTotal().getCell(i, 2).setFormulaLocal(sFormula);                        
                    }
                    //On met en forme le message de résultat et le statut à OK
                    oCellStatut.getFormat().getFill().setColor('green');
                    oActiveSheet.getRange('E5').getFormat().getFont().setColor("FFFFFF");
                    oCellStatut.getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.center);
                    oCellStatut.setValue('OK');
                    oCellMsg.setValue("L'API a renvoyé " + aUsageData.length + " jours fériés en " + sChoixAnnee + " pour le territoire '" + sChoixZone + "'." );
                    oCellMsg.getFormat().getFont().setColor('green');

                    //On fait pointer le surseur sur la ligne de titre
                    let oRange = oActiveSheet.getRange("B2:D2");
                    oRange.select();
                }
                else {
                    //Dans le cas où l'on obtient une erreur de l'API
                    oCellStatut.getFormat().getFill().setColor('red');
                    oCellStatut.setValue('Erreur');
                    oCellStatut.getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.center);
                    //On ajoute un commentaire à la cellule contenant le message 
                    let oComment = oActiveSheet.addComment(oCellMsg.getAddress(), "L'API ne peut répondre à la requête pour le territoire et l'année indiquée !");
                    
                }
            }
            catch {
                //Dans le cas où l'on ne peut atteindre l'API
                oCellStatut.getFormat().getFill().setColor('orange');
                oCellStatut.setValue('Indisponibilité');
                oCellStatut.getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.center);
                oCellMsg.setValue("Le service interrogé est momentanément indisponible. Attendre quelques minutes puis retenter l'exécution de ce script.");
                console.log(oRequest.status);
            }
        }
        catch{																											//Dans le cas où une zone nommée n'est pas présente
            console.log("Le classeur actif ne semble pas correspondre à ce traitement. Traitement stoppé.")
        }
    }
    catch {																												//Dans le cas où la feuille attendue n'est pas présente
        console.log("La feuille voulue n'est pas présente dans le classeur actif. Traitement stoppé.")
    }
}
else {
    //Dans le cas où le classeur n'est pas celui attendu
    console.log(sWorkbookName);
}
}

L’IA connait Office Script !

Nous sommes tous friants des nouveauté technologiques et impatients, à leur émergence, d’en découvrir les possibilités et limites.

C’est donc curiodité et gourmansie que nous avons posé le prompt suivant à Copiltop Nous avons 

    Interaction avec les utilisateurs

    • Encore une différence essentielle : dans la version mars 2022, OFFICE SCRIPTS ne nous permet pas d’interagir avec les utilisateurs.
      Avec VBA nous interagissons avec les utilisateurs au moyen de formulaires “userforms” ou avec l’instruction “Input” ou bien encore avec l’instruction “Msgbox”. Toutes ces techniques sont actuellement indisponibles dans OFFICE SCRIPTS. L’application POWER AUTOMATE (incluse dans les abonnements Microsoft 365) pourrait nous offrir une alternative.

    TUTO : TABLEAUX STRUCTURES – Une Introduction

     

    UN TABLEAU STRUCTURÉ c’est quoi ?

    Nous pouvons légitimement nous demander ce que sont les tableaux structurés dans un classeur EXCEL lui-même étant ‘un tableau’.

    De fait , un tableau structuré est un object d’EXCEL à part entière (le terme anglais le désignant est ‘Listobject’). Cet objet expose un ensemble riche de propriétés et méthodes.

    Le but de ce tutoriel est de monter par l’exemple ce que nous apporte le « Tableau structuré » par rapport au « Tableau classique » et montrer quelques unes de ses principales propriétés bien utiles au quotidien. Tellement utiles et faciles à mettre en oeuvre qu’une fois découvert, nous ne pourrons nous passer des « Tableaux structurés ».

    Ce tutoriel ne couvre pas l’intégralité de ce qu’il est possible de faire avec les « Tableaux structurés ». Son objectif est d’éveiller l’intérêt pour cet objet et fournir les bases de son utilisation. De nombreux cours et tutoriels complets sur le sujet sont disponibles sur le WEB (cf. Notre sélection de sites consacrés à EXCEL).

    CREONS UN TABLEAU STRUCTURÉ

    Dans une feuille d’un classeur, créons tout d’abord le tableau « classique » suivant :

    (cliquer ici pour télécharger l’EXCEL)

    Menu_Debogage_Compiler

    Pour transformer un tableau « classique » en tableau « structuré », rien de plus simple :

    Sélectionnons l’une des cellules du tableau et cliquons sur le bouton « Tableau » de l’onglet « Insertion » présent dans le ruban EXCEL (ou Ctrl+L) :

    La boite de dialogue qui s’ouvre permet d’indiquer l’adresse de la plage de cellules à reprendre du tableau classique et si le tableau classique comporte des en-têtes :

    (Dans notre exemple, Excel indique de lui-même la plage de cellules du tableau classique et déduit qu’il possède des entêtes. On pueut bien entendu modifierces valeurs si besoin.)

    Une fois validée la boîte de dialogue, nous obtenons le tableau structuré suivant :

    Qu’a fait EXCEL ?

    • Il a automatiquement ajouté des filtres sur chaque colonne de l’en-tête.
    • Il a mis en forme la présentation du tableau, en particulier en ajoutant le surlignage d’une ligne sur deux.

    De plus, il a automatiquement donné un nom au tableau, par défaut « Tableau1 »

    Recommandation : modifier le nom du tableau pour le rendre son utilisation plus aisée. Pour réaliser cette modification, plusieurs possibilités. Par exemple, passons par le gestionnaire de noms : Dans l’onglet « Formules » du ruban, faisons le choix « Gestionnaire de noms » :

    En sélectionnant « Tableau1 », cliquons sur le bouton « Modifier ». Indiquons, par exemple « ts_ApproFruits » en tant que nom.

    Désormais, dans la liste déroulante des plages nommées, notre nom de tableau est présent. Ceci permettra de le sélectionner rapidement par la suite :

    UTILISONS L’OUTIL ‘SEGMENT’

    Lorsque nous sélectionnons un tableau stucturé, EXCEL ajoute un nouvel onglet ‘Outils de tableau’ au ruban :

    Cet onglet contient un ensemble d’outils et de fonctions bien pratiques. Explorons quelques-unes d’entre-elles :

    Cliquons sur l’outil « Insérer un segment » :

     

    La liste des colonnes de notre tableau structuré est présentée par EXCEL dans une boite de choix à cocher. Cliquons par exemple, devant « Produits » et « Provenance » puis « OK » :

    La feuille EXCEL affiche 2 boîtes de cases à cocher dénommées sélecteurs (on peut les disposer à notre convenance sur la feuille) :

    Dans le sélecteur ‘Produit’, en cochant un produit particulier et dans le sélecteur ‘Provenance’ un pays particulier,  les données du tableau structuré sont filtrées relativement aux choix opérés.

    Par exemple, sélectionnons ‘Fraise’ dans le sélecteur ‘Produit’ et ‘Espagne’ dans le sélecteur ‘Provenance’. Le tableau structuré se présente alors ainsi :

    Remarque :

    Il est possible de réaliser des sélections mutiples.  Par exemple, Fraise et Pomme en provenance d’Espagne ou de Belgique…

    AJOUTONS UNE COLONNE DE CALCUL

    Dans le cas ou nous voulons, par exemple, ajouter une colonne ‘Montant’ dont la valeur contiendra le produit du ‘Nombre d’unités achetées’ par le ‘Prix par unité’. 

    Sélectionnons l’une des cellules de la colonne « Prix par unité » et faisons un clic droit sur la souris. Dans le menu contextuel qui s’affiche, choisissons « Insérer » puis « Colonne de tableau à droite » :

    Modifions l’entête de la nouvelle colonne par ‘Montant’ . Dans la première cellule de cette colonne, entrons la formule de calcul en sélectionnant avec la souris successivement la cellule de la première ligne contenant le ‘Nombre d’unités achetées’, le signe ‘*’ puis  la cellule de la première ligne contenant le ‘Prix par unité’ :

    Une fois la formule validée, EXCEL propage cette formule dans chaque ligne du tableau structuré :

    AJOUTONS UNE TOTALISATION

    Le tableau structuré permet d’ajouter aisément des totalisations sur ses lignes et ses colonnes. Par exemple, pour ajouter une totalisation sur la colonne ‘Montant’, dans l’onglet ‘Outils de tableau‘ cocher ‘ Ligne Total’ :

    Une ligne intitule ‘Total’ s’ajoute en bas du tableau stucturé :

    APP EXCELLONS GRATUITE : SMART_TROMBINOSCOPE

    Application réalisée avec la version EXCEL 365 Windows.

    Cette App EXCELLONS vous permet d’obtenir avec un minimum d’effort un trombinoscope regroupant les photos des membres d’une association, d’une assemblée, d’une société…

    Avec cette App Excellons entièrement gratuite et sans publicité, vous produirez les plus beaux trombinoscopes comme celui-ci :

    Trombinoscope généré par APP EXCELLONS 'Smart_Excellons'

    Télécharger l’App EXCELLONS ‘Smart Trombinoscope’ version VBA. (Nécessite l’activation des macros dans les options EXCEL)

    TUTO EXCEL : Les tableaux structurés

    Les tableaux structurés rendent l’utilisation d’EXCEL au quotidien encore plus souple, pratique et fiable.

    On peut regretter que Microsoft ait choisi d’utiliser en français le terme de « Tableau ». En effet, ce terme prête à confusion. Nous préférons lui adjoindre le qualificatif ‘structuré’ afin de le distinguer des tableaux au sens classique du terme.

    Ce tutoriel montre par l’exemple ce que les « Tableaux structurés » nous apportent dans notre pratique d’EXCEL et ceci avec un minimum d’efforts.

    Ce tutoriel n’a pas vocation à couvrir l’intégralité du sujet. Son objectif est d’éveiller l’intérêt pour cette riche fonctionnalité et fournir les bases de son utilisation.
    Pour un approfondissement du sujet, nous nous reporterons utilement vers l’article de Pierre Fauconnier Excel tableaux structurés.

      
     (Tutoriel réalisé avec version 2016 d’EXCEL)

    Créons un tableau structuré

    Commençons par créer le tableau ‘classique’ suivant :
    TableauClassique

    Pour transformer un tableau « classique » en tableau « structuré », rien de plus simple :

    Sélectionnons l’une des cellules du tableau et cliquons sur le bouton « Tableau » de l’onglet « Insertion » présent dans le ruban EXCEL : 

    InsertionTableau

    Un boîte de dialoque s’ouvre nous permettant d’indiquer :

    • la plage de cellules composant le tableau stucturé
    • si la première ligne contient les entêtes des colonnes

    Remarquons qu’EXCEL est suffisamment intelligent pour prendre l’intégralité des cellules de notre tableau classique et comprendre tout seul les entêtes se trouvent dans la 1ère ligne :

    CréerTableau

    Après avoir cliqué sur ‘OK’, EXCEL présente la transformation de notre tableau ‘classique’ en tableau structuré :

    RésultatTableauStructuré

    Qu’a fait EXCEL ?

    • Il a automatiquement ajouté des filtres sur chaque colonne de l’en-tête.
    • Il a mis en forme la présentation du tableau, en particulier en ajoutant le surlignage d’une ligne sur deux.

    De plus, il a automatiquement donné un nom au tableau, par défaut « Tableau1 ». Il est recommandé de modifier le nom du tableau pour le rendre plus explicite et ainsi plus facilement repérable.

    Pour réaliser cette modification, plusieurs possibilités. Passons par le gestionnaire de noms :

    Dans l’onglet « Formules » du ruban, faisons le choix « Gestionnaire de noms » :

    GestionnaireNoms

    En sélectionnant  ‘Tableau1’, cliquons sur le bouton ‘Modifier’. Pour notre exemple utilisons le nom ‘ts_ApproFruits‘.

    Désormais, dans la liste déroulante des plages nommées, notre nom de tableau est présent. Ceci permettra de rapidement le sélectionner :

    TableauStructuréNommé

    CUSTOMISONS UN TABLEAU STRUCTURE

    Utilisons l’outil « Segment »

     

    Sélectionnons notre tableau structuré et remarquons qu’alors, EXCEL ajoute un nouvel onglet au ruban : « Outils de tableau ». Sélectionnons cet onglet :

    RubanOutilsTableau

    Nous y trouvons un ensemble d’outils et de fonctions bien pratiques. Explorons quelques-unes d’entre-elles :

    Cliquons sur l’outil « Insérer un segment » :

    InsérerSegment

    EXCEL présente la liste des colonnes de notre tableau. Sélectionnons, par exemple, « Produits » et « Provenance » et cliquons sur « OK » :

    TableSegments

    Notre feuille EXCEL s’est enrichie de 2 sélecteurs (on peut les disposer à notre convenance sur la feuille au moyen de la souris par exemple) :

    DeuxSelecteurs

    Dans ces sélecteurs, en cliquant sur un produit particulier et une provenance particulière, nous filtrons le tableau à notre guise.

    Par exemple, sélectionnons les fraises en provenance d’Espagne :

    DoubleSélection

    Les sélections multiples sont possibles. Par exemple : Fraise et Pomme en provenance d’Espagne et de Belgique. Pour mettre en place des choix multiples, il suffit de cliquer sur l’icône ‘Sélection multiple (Alt+S)’ dans l’entête du sélecteur :

    DoubleSélection

    AJOUTONS UNE COLONNE DE CALCUL

    Dans le cas où nous désirons ajouter une colonne de calcul (par exemple le calcul du montant = nb d’unités achetées * Prix par unité) :
    • On sélectionne l’une des cellules de la colonne ‘Prix par unité’.
    • On clique droit.
    • Dans le menu contextuel, on choisis « Insérer » puis « Colonne de tableau à droite » :
    DoubleSélection

    Intitulons la nouvelle colonne « Montant » et dans la première cellule, entrons la formule de calcul soit « =C2*D2 » :

    CalculMontant

    Après la validation de la formule, EXCEL la propage sur toutes les lignes :

    ColonneMontantCalculée

    AJOUTONS UNE TOTALISATION

    Nous pouvons ajouter une totalisation par exemple sur la colonne « Montant ». Pour ce faire, il nous suffit de revenir sur l’onglet « Outils de tableau » et de cocher « ligne des totaux » :
    AjoutLigneTotalisation

    EXCEL ajoute alors une ligne « Total » en bas du tableau :

    Totalisation

    La totalisation est dynamique : elle évolue en fonction des sélections faites.

    Par exemple, si la sélection porte sur Fraise+Pomme et Belgique+Espagne, le total obtenu correspond à la somme des montants de cette sélection :

    TotalisationDynamique