Catégorie : Excel

Boostez votre productivité avec Power Query : le couteau suisse d’Excel

Boostez votre productivité avec Power Query : le couteau suisse d’Excel

Cela fait maintenant quelques années que Power Query est inclus dans Excel. Il reste toutefois peu exploité. Il faut dire que pour les habitués d’Excel, l’interface de Power Query paraît trop simpliste. Deuxième raison d’une adoption plus limitée : il faut apprendre un nouveau langage pour prendre le contrôle de Power Query. Il s’agit du langage M.

Déjà que maîtriser toutes les formules d’Excel n’est pas chose aisée, pourquoi donc faudrait-il s’intéresser à ce module complémentaire d’Excel qu’est Power Query ? C’est ce que je vais développer dans cet article. Vous y trouverez notamment quelques tutoriels (ou tutorial ?) en français.

Excel et Power Query : Vous ne verrez plus jamais ça !

Avez-vous déjà dû ouvrir un fichier .csv dans Excel et rencontré l’écran ci-dessous ?

Source du fichier utilisé : https://www.data.gouv.fr/fr/datasets/r/bf82e99f-cb74-48e6-b49f-9a0da726d5dc

Vous savez, c’est quand on ouvre un fichier csv ?

Désormais, Microsoft a remplacé l’ancien utilitaire d’importation de fichiers par Power Query justement ! Vous l’avez peut-être déjà utilisé à votre insu en pensant que les bugs étaient corrigés.

Vous devrez importer votre fichier, et non juste double-cliquer dessus (même si le logo d’Excel apparaît sur votre fichier csv…)

Désormais, vous aurez l’interface suivante lors de vos imports de fichiers :

Fichier CSV - Importer - Interface Power Query

Alors, ce serait uniquement cela Power Query ? Un nouvel utilitaire d’import ? Heureusement que non ! Comme je vais vous le présenter dans cet article, Power Query est bien plus puissant que cela. L’import des données est effectivement LE point d’entrée de l’outil. Comme vous allez le voir dans les paragraphes suivants, Excel n’est qu’une source parmi tant d’autres…

Qu’est-ce que véritablement Power Query ?

En résumé

Power Query est une usine de transformation de vos données. Telle une chaîne industrielle, Power Query va opérer une suite d’étapes que vous allez lui demander. Et à chaque fois que vous actualiserez les données dans Excel (ce sera identique avec Power BI), la chaîne que vous avez mise en place va s’exécuter.

En détail

Certains diront qu’il existe déjà les macros pour cela… et ils auront raison et un peu tort quand même. J’ai déjà développé mon point de vue sur le sujet des macros dans Excel et sur les blocages qui sont là et vont continuer sur la sécurité des macros en entreprise. Raison supplémentaire de passer à Power Query et les autres produits de la gamme Power (Power Apps, Power Automate, Power BI notamment).

Là où Power Query se distinguera des macros, c’est que c’est un moteur uniquement fait pour transformer vos données vers un état souhaité. Alors, je comprends que dit ainsi, cela paraît bien abstrait !

Dans les grandes lignes, les possibilités offertes par Power Query, une fois les données importées, seront :

  • Sélectionner les colonnes à conserver ou à supprimer
  • Réordonnancer les colonnes conservées
  • Filtrer les lignes à conserver / à supprimer (en particulier, les doublons, les lignes contenant des erreurs, etc.)
  • Nettoyer les données (les séparer, les convertir, les mutualiser, etc.)
  • Combiner plusieurs sources entre elles (c’est l’un des gros morceaux qu’un simple point ne peut résumer !). Le mot combiner est à prendre dans le sens commun ici. Car au sein de Power Query, le terme Combiner correspond à une action bien spécifique.

Paré pour la suite

Au final, quand vous avez combiné toutes les possibilités, vous disposez d’une machine ultra puissante de préparation de vos données (c’est la première étape clé pour la mise en place des tableaux de bord…).

Avec Power Query, vous pouvez voir plus grand et plus loin que votre seul classeur. Importer des données issues de plusieurs sources est d’une facilité déconcertante !

Vous pourrez disposer d’un fichier de pilotage qui va, à proprement parler, consommer les données dont vous avez besoin et produire de beaux rapports ! Ces données peuvent bien être où elles veulent, tant que vous y avez accès, vous pourrez les importer. Il n’y a qu’à regarder la liste des connecteurs à disposition (nativement !).

Par où commencer et comment apprivoiser l’interface de Power Query

L’import des données est obligatoirement la première étape de tous les traitements. La première fois que vous ouvrirez l’interface, vous aurez une envie irrésistible de saisir vos données ou de les modifier cellule par cellule (comme un bon vieux Excel…). Ne pouvant y arriver, je sais qu’une pointe de frustration et à la limite de rejet peut émerger en vous. Résistez ! Tout ressemble et rien n’est identique à votre interface habituelle d’Excel.

Interface Power Query

Vous verrez des tableaux de données, des menus déroulants avec des filtres à disposition, la possibilité de déplacer vos colonnes, et à ce stade vous ne mesurez pas les conséquences de vos actes.

Chaque action que vous réaliserez dans cette interface représentera une étape qui sera enregistrée automatiquement. Par la suite, elle sera reproduite à chaque rafraîchissement de vos sources de données.

Exemple n°1 : Utilisez Power Query pour importer des données

La première action qui pourra vous interpeler est de devoir importer dans Power Query les données qui se trouvent déjà dans votre classeur Excel. Je comprends bien que cela vous surprenne.

On ouvre l’interface de Power Query depuis Excel et pourtant le premier « ne voit pas » les données du second. Comme nous le verrons dans les paragraphes suivants, Power Query peut importer des données de nombreuses sources différentes. Et hormis la phase d’apprentissage initiale, vous verrez rapidement que la plupart du temps, les données dont vous avez besoin proviendront de sources externes plus fréquemment que vous ne l’imaginez aujourd’hui.

Et tout comme je vous l’ai déjà indiqué, il sera bien plus performant et efficace à termes que les données NE PROVIENNENT PAS du classeur où vos traitements Power Query seront configurés.

Toutefois, pour débuter, ce sera plus simple d’avoir les données et les traitement dans le même classeur. (Pourquoi tant de personnes recherchent « Power Query pour les nuls » ?? On a le droit d’être un débutant sans être qualifié de nul !)

Importer des données qui sont déjà dans votre classeur

Cette option est à privilégier pour débuter son apprentissage de Power Query. Deuxième cas de figure, vous n’allez utiliser Power Query que pour quelques traitements simples et rapides.

Je vous avertis tout de même : on commence toujours petit et avant de s’en être rendu compte, on a construit une usine à gaz. Et ensuite, changer les modalités d’import peut être cavalier pour un débutant. Aussi, je me dois de vous prévenir que c’est faisable, à condition d’en mesurer les conséquences et disposer du savoir-faire.

Étape n°1 : Onglet « Données » puis « À partir de Tableau ou d’une Plage »

Power Query - Importer des données déjà présentes dans le classeur
Power Query – Importer des données déjà présentes dans le classeur

Le dernier encadré est très important à bien lire. Un tableau Excel est un objet bien particulier qui diffère des simples cellules mises en forme pour avoir l’apparence d’un tableau.

Un objet tableau dans Excel dispose d’un nom réutilisable dans des formules, les formules faisant références aux données du tableau n’utiliseront plus les n° de lignes ou nom de colonne « A, B, C… » mais le nom de la colonne. Et si des cellules sont fusionnées, elles seront « défusionnées ».

Étape n°2 : Création d’un tableau à partir de vos données et ouverture de Power Query Editor

Power Query - Importer depuis une plage - Créer un tableau
Power Query – Importer depuis une plage – Créer un tableau

À ce stade, Excel vous propose de créer un tableau comme indiqué dans le titre en (1) (seulement si vous n’êtes pas déjà dans un tableau).

Veillez bien à la seconde partie (2) : Mon tableau comporte des en-têtes.

Cela pourrait ne pas être le cas ! Certains fichiers de données brutes ne disposent pas de noms de colonnes. Si vous êtes dans cette situation et que vous maintenez cette case cochée, vous perdrez les données de la première ligne. Celles-ci devenant de facto l’en-tête du nouveau tableau.

Si vous décochez cette case, Excel vous créera une ligne d’en-tête avec des noms d’une créativité incroyable : Colonne1, Colonne2, etc. Mais au moins, vous ne perdrez aucune donnée !

Étape n°3 : Apprivoiser l’interface de Power Query Editor

Power Query - Interface édition
Power Query – L’interface d’édition

Ici, vous croyez retrouver un pseudo Excel… et c’est tout sauf la même chose !

Décomposons l’interface
  1. Les onglets et le ruban
  2. Les données dans l’état de l’étape sélectionnée (vous comprendrez par la suite…)
  3. Les requêtes Power Query disponibles dans ce classeur
  4. Les étapes de transformation

Que s’est-il passé jusqu’à présent ? Vous avez créé une requête d’import des données auxquelles vous avez déjà accès dans le classeur. Si par mégarde, vous cliquez sur le bouton « Fermer et charger », vous aurez la chance d’avoir un nouveau onglet nommé « Tableau1 » avec la copie de toutes vos données.

Normal, vous n’aurez pas utilisé une seule des possibilités offertes par Power Query ! Il faudra maintenant ajouter des étapes de traitement de vos données pour que cela en vaille la peine.

Par exemple, vous pourriez « Trier » vos données par total des ventes de manière décroissante et ne conserver que les 5 premières.

Voyons ce que cela donne
Power Query - Trier et filtrer les lignes
Power Query – Trier et filtrer les lignes

On voit qu’à droite, deux nouvelles étapes se sont ajoutées à la liste : « Lignes triées » et « Conserver les premières lignes ». J’avais au préalable sélectionné la colonne « Sales » pour réaliser ces opérations.

Sauf que maintenant, si je clique sur « Fermer et charger », seules 5 lignes vont être importées dans le nouvel onglet d’Excel. Et par n’importe lesquelles, celles des 5 ventes les plus importantes.

Pas de formule, pas de fonctions avancées, jusque 4 clics et l’affaire est dans le sac !

Et que se passera-t-il si vous ajoutez des données à votre tableau initial ? Vous n’avez qu’à faire un essai, ajoutez de nouvelles lignes, cliquez sur « Actualiser tout » dans l’onglet « Données » et voyez le résultat !

Importer des données depuis des fichiers (CSV, txt)

Idéalement, il est préférable d’avoir un fichier qui fait office de tableau de bord qui soit séparé des sources de données. C’est pourquoi, je vous recommande de ne plus ouvrir les fichiers contenant les données, mais de les importer à l’aide de Power Query.

Ainsi, lorsque vous aurez de nouvelles données à analyser, vous n’aurez qu’à actualiser votre import et tous vos rapports seront à jour et actualisés.

Étape n°1 : Onglet « Données » puis « À partir d’un fichier texte/CSV »

Power Query - Importer depuis un fichier csv
Power Query – Importer depuis un fichier csv

Rendez-vous dans l’onglet « Données » puis cliquez sur « À partir d’un fichier texte/CSV » et enfin, sélectionnez votre fichier à importer.

Étape n°2 : Contrôlez les modalités de l’import et la qualité de la prévisualisation

Au moment de sélectionner votre fichier CSV à importer, l’écran suivant apparaît. Étudions-le attentivement :

Power Query - Interface import
Power Query – Interface import
1. Contrôler l’encodage (Origine du fichier)

Ce qui fait qu’un caractère accentué comme « é » apparaissent comme suit : « Ã© », c’est ce qu’on nomme son encodage. C’est à dire, la langue avec laquelle le fichier a été enregistré sur le disque de l’ordinateur. Et lorsqu’on n’indique rien, c’est un peu le jeu de la chance, soit c’est dans la langue la plus parlée (UTF-8), soit c’est celle de Windows (ANSI). Et les deux langues ne sont pas identiques.

Ici, Power Query présence en (1) ce qu’il nomme « Origine du fichier », et on voit qu’il a reconnu UTF-8. Quelle que soit la proposition, vous pouvez la changer tout de suite ou plus tard. Par principe, le plus tôt sera le mieux !

2. Contrôler le délimiteur

Là, Power Query a fait bien des progrès par rapport au module d’import d’Excel. En général, il ne se trompe pas. Toutefois, selon vos données, cela pourrait être une virgule, des tabulations ou comme dans l’illustration, un point-virgule.

Vous avez aussi la possibilité d’indiquer un autre séparateur et de voir ce que cela donnerait dans la prévisualisation.

3. Plage de contrôle des types de données

Les types de données sont importants pour Power Query. Si vous avez besoin de déterminer une année à partir d’une des colonnes, celle-ci doit obligatoirement être une date, sinon, vous aurez un message d’erreur. Comme le chargement et l’analyse de l’ensemble des données importées pourrait être chronophage, ici, Power Query vous propose de n’analyser que les 200 premières lignes pour déterminer le type de chaque colonne. (le choix se situe principalement entre Texte, Nombre entier, Nombre décimal, Date, Booléen, Pourcentage).

In fine, pas d’inquiétude, tout sera modifiable et corrigeable si besoin par la suite. Disons que tant qu’à travailler, autant bien commencer !

Importer depuis un dossier

Pour le cas de l’import depuis un dossier, en réalité, il s’agira de fusionner au moins deux fichiers de même nature et structurée de la même manière.

Et pour cela, l’article sur comment fusionner plusieurs fichiers Excel vous explique tout !

Power Query va plus loin ! Il peut importer des données depuis un fichier PDF ou depuis une image

Eh oui ! Power Query peut enfin faire ce que vous avez tenté des dizaines de fois avec l’espoir que « Cette fois, ça va fonctionner ! » à savoir copier un tableau depuis un fichier PDF et le coller dans Excel.

Power Query peut importer dans Excel les données tabulaires de votre fichier PDF avec une grande facilité. Il faut que votre fichier PDF soit bien un PDF Texte. C’est à dire qu’il n’est pas le fruit d’une numérisation. Sinon, autant faire l’import d’une image !

Oui, vous avez bien lu, on peut faire de la reconnaissance de caractère depuis Excel, en particulier si vos données sont dans un tableau. La performance de la reconnaissance dépendra de la qualité de votre image.

Comment fait-on tout cela ?

Tout est dans la vidéo ci-dessous :

Exemple n°2 : Traitez de plus grandes quantités de données à l’aide de Power Query qu’avec Excel

Non, 1’048’576 ne représente pas la limite du nombre de lignes qu’Excel peut traiter. À l’aide de Power Query, Excel pourra analyser plusieurs millions de lignes de données !

Attention, traiter ne signifie pas importer ! Alors, à quoi cela peut-il bien servir me direz-vous ?

Eh bien, entre nous, lorsqu’on a besoin d’analyser plus d’un million de lignes de données, c’est rarement pour les étudier une par une. Il s’agira ici nécessairement de créer des tableaux croisés dynamiques (voir des tableaux de bords).

Nous avons vu ensemble dans le paragraphe précédent qu’il était possible d’importer et de fusionner toutes les données se trouvant dans les fichiers d’un dossier.

Et même avec un unique fichier de plusieurs millions de lignes, l’erreur à ne pas commettre, serait de vouloir visualiser les données à analyser. Vous n’avez réellement besoin que de les analyser. En cas de nécessité, vous pourrez voir le détail.

Exemple avec un fichier CSV volumineux

Si vous tentez d’ouvrir un fichier disposant de plus d’un million de lignes, vous aurez droit au message suivant :

Fichier CSV trop volumineux - Message avertissement / erreur
Fichier CSV trop volumineux – Message avertissement / erreur

Pour vous le prouver, voici le même fichier ouvert à l’aide de Notepad++ :

Fichier CSV volumineux - Illustration dans Notepad++
Fichier CSV volumineux – Illustration dans Notepad++

Comment traiter ce fichier volumineux seul ou suite à la compilation des fichiers dans un dossier à l’aide de Power Query ?

Au moment d’importer vos millions de lignes de données, vous devrez bien ouvrir vos yeux ! Une petite option cachée de prime abord est là pour vous aider !

Il s’agit du menu déroulant présent à côté du bouton « Charger » :

Power Query - Menu charger dans

Regardez bien de plus près :

Menu charger dans - Zoom

Et ce petit menu va vous donner l’accès au menu suivant :

Importer les données directement dans un TCD
Importer les données directement dans un TCD

Comme indiqué, vous pourrez importer les données dans un tableau croisé dynamique directement !

Et vous verrez ensuite ce menu apparaître sur la droite :

Power Query - Importer fichier de plusieurs millions de lignes
Power Query – Importer fichier de plusieurs millions de lignes

Pendant l’import, le nombre de lignes importées va croître et dépasser allègrement le million de lignes permis dans une feuille de calcul Excel.

Merci Power Query !

Exemple n°3 : Réduisez les erreurs dans vos données à l’aide de Power Query (en améliorant leur qualité avant de les traiter !)

Avec les exemples précédents, nous avons vu que Power Query est votre super-assistant d’accès aux données. Et ce, même si vous avez plusieurs millions de lignes de données à traiter.

À partir d’ici, je vais vous initier aux autres possibilités de cet outil. Vous allez désormais pouvoir automatiser les traitements que vous faisiez manuellement.

Qu’est-ce qu’une donnée de mauvaise qualité ?

La qualité de vos données est essentielle pour débuter leurs analyses. Si vous avez déjà rencontré l’une des situations suivantes, alors, vous allez adorer ce qui suivra…

1. Des caractères espaces inutiles au début ou à la fin dans une cellule.

Cela empêche en plus Excel de reconnaître le type de données et d’adapter la mise en forme convenablement. Par exemple en nombre décimal ou en date.

2. Des sauts de lignes d’origine inconnue.

Au moment où vous analysez vos données à l’aide d’un tableau croisé dynamique, vous trouvez qu’il y a quelque chose qui cloche… jusqu’à ce que vous vous rendiez compte qu’à cause des lignes vides, toutes les données n’ont pas été incluses dans l’analyse !

3. Des cellules qui contiennent deux données au lieu d’une !

C’est parfois ce qui arrive quand on fait un export de données depuis un ERP. Vous devez encore retraitez cette cellule, le plus souvent avec des formules, pour séparer toute une colonne en deux colonnes.

4. Des noms de colonnes très techniques et peu compréhensibles pour les néophytes.

Vous devez à chaque fois, renommer vos colonnes pour les rendre compréhensibles dans le rapport.

5. Le pire de tous : les lignes incomplètes

Parfois, les outils qui exportent les données ne remplissent pas toutes les valeurs s’il n’y a pas de changement d’une ligne à l’autre. On peut avoir par exemple, la date d’une vente qui n’est présente en première colonne que lorsqu’on change de jour de l’écriture. Il vous faut faire de nombreuses manipulation pour compléter chaque ligne…

6. D’autres idées ?

D’autres cas de mauvaise qualité des données existent (d’ailleurs, vous pouvez me les proposer en commentaire de cet article) et vous font perdre un temps précieux à chaque fois.

Eh bien, tous ces cas de figure sont automatisables en quelques clics à l’aide de Power Query !

Mais le plus beau dans l’histoire, c’est qu’une fois que vous avez construit vos étapes de nettoyage. Vous n’avez plus jamais à vous en soucier !

Améliorer la qualité de vos données à l’aide des premières fonctions de Power Query

Je vous propose l’illustration ci-dessous avec la même numérotation que les points soulevés précédemment.

En choisissant dans le ruban, l’onglet « Transformer« , vous aurez accès aux fonctionnalités ci-dessous :

Power Query - Fonctionnalités simples
  1. En sélectionnant la ou les colonnes de votre choix et en cliquant sur « Format » puis « Supprimer les espaces« . Vous aurez déjà réglé le premier point.
  2. Dans cette interface, le filtre d’une colonne n’agit pas exactement comme dans Excel. Ici, quand vous filtrez quelque chose (par exemple en supprimant les lignes vides), vous ajoutez une étape automatisée de suppression des lignes vides ! Eh oui, c’est tout !
  3. « Fractionner la colonne » va automatiser le fait de séparer une colonne avec des données en autant de colonnes que d’occurrence du séparateur choisi. Parfois ce sera un caractère tabulation, parfois ce sera un caractère dièse, ou ce que vous souhaitez ! Et voilà !
  4. Ici, si vous double-cliquez sur un nom de colonne et changez son nom, vous n’avez pas simplement changé de nom. Vous avez automatisé le renommage depuis votre fichier source. Ce dernier n’a pas bougé. En revanche, lors de l’utilisation dans Excel, seul le nom que vous aurez choisi apparaîtra !
  5. Last but not least ! Vous pouvez compléter toutes les cellules qui seraient vides à partir de la cellule la plus proche qui a des données. En clair, si vous reprenez l’exemple avec la date (au paragraphe précédent), toutes les cellules vides peuvent être complétées en un clic !

Exemple n°4 : Simplifiez vous la vie avec toutes les données de type date et heure à l’aide de Power Query

Mise en situation des problèmes potentielles avec les dates et les heures

Les données de type date sont certainement celles qui ont donné et qui continueront de donner de nombreux maux de têtes à ceux qui les manipulent…

Entre les formats américains et européens, les séparateurs entre les jours, les mois et les années des plus exotiques ou encore des formats étranges, il vous faut souvent bien de la patience pour les rendre utilisables.

Et je ne vous parle pas des configurations régionales de votre ordinateur ! Car oui, selon la configuration de votre système d’exploitation, Excel va traiter différemment les données que vous allez lui fournir !

En résumé, les sources de difficultés peuvent être :

  • L’ordre dans lequel une date vous est transmises (12.31.2025 ou 31.12.2025). Dans cette situation, c’est encore facile à identifier, mais 8.9.2025, c’est le 8 septembre ou bien le 9 août ?
  • Le choix du séparateur de date entre celui fourni et celui de votre système… Dans l’exemple précédent, j’ai choisi le symbole « . » et non « / » comme c’est le cas en France habituellement. Comment Excel va réagir ?
  • Parfois, les dates viennent au format année puis mois puis jour : 2025.12.31. C’est plus clair pour identifier les composantes de la date, mais comment Excel va l’interpréter ?
  • Il y a également les dates « techniques » qui inclut l’heure et le décalage horaire par rapport à l’heure universel UTC : 20251231T12:42-0500

Comment Power Query peut vous simplifier la vie ?

Avant que vous découvriez Power Query pour traiter vos données de type date, vous pouviez tenter d’écrire des formules complexes afin d’identifier dans quel cas vous vous trouviez.

Maintenant, quelques clics suffisent !

Il suffit de regarder les différences entre import via Power Query ou ouverture direct du même fichier CSV (Lien vers le fichier utilisé dans l’illustration) :

Power Query - Différences traitement dates avec Excel

Et ce n’est pas tout ! Grâce aux fonctions intégrées à Power Query, vous pourrez extraire l’année, le mois, le jour d’une date en deux clics. En à peine plus de clics, vous pourrez intégrer des dates aux formats régionaux exotiques à votre format. Et enfin, faire la différence entre deux dates ou entre deux heures ! (même si je vous explique en détail comment ajouter des heures avec Excel ou comment calculer un âge avec Excel, avec Power Query, ce sera plus rapide !).

Exemple n°5 : Ajoutez, Fusionner, Étendez vos données à l’aide de Power Query au lieu de vos formules Excel

À l’aide de ces autres fonctionnalités, vous allez goûter au véritable potentiel de Power Query ! Je dois d’abord redéfinir certains termes. En effet, entre la vie de tous les jours, Excel et Power Query, certains termes ne signifient pas la même chose…

Ajouter des données avec Power Query

Lorsqu’on vient du monde Excel, « ajouter » signifie faire une addition. Dans Power Query, pas du tout… « Ajouter des données » signifie « empiler des données » dans un tableau unique.

Power Query - Ajouter des données

Cette opération sert à mutualiser dans une table unique des données issues de sources souvent différentes.

Cas d’usage type : Sortir une liste unique de fournisseurs (ou de clients) en mutualisant des données issues de deux logiciels différents.

Il faudra souvent ajouter une étape de suppression des doublons pour obtenir la liste mutualisée. À l’inverse, vous pourriez souhaiter identifier les doublons, et pour cela, vous sélectionneriez conserver les doublons.

Fusionner des données avec Power Query

Dans Excel, nous n’avons pas l’habitude de fusionner les données… plutôt des cellules. En revanche, on a souvent besoin de compléter nos données à partir d’un identifiant. En clair, lorsqu’une commande contient un code fournisseur, parfois, on souhaite récupérer son nom et son adresse par exemple.

Dans Excel, c’est la fameuse fonction RECHERCHEV() qui réalise cette opération. En passant, je vous recommande d’utiliser la nouvelle fonction RECHERCHEX() (si vous disposez d’une licence Excel 2021 ou Excel 365). Deuxième info en passant, voici comment faire une RECHERCHEV avec plusieurs critères.

Avec Power Query, si vous souhaitez faire cette opération, alors vous devrez fusionner vos données.

Power Query - Fusionner données
Power Query – Fusionner données

Je ne rentre pas ici dans les détails de toutes les options. La fusion entre deux tables offre des options avancées qui mériteraient des explications plus détaillées… Le choix du symbole multiplier n’est pas anodin.

Power Query - Fusionner des données - Type de jointure

Étendez vos données avec Power Query

On vient de voir précédemment qu’il est possible de fusionner ses données entre elles. D’une certaine manière, vous venez en quelques clics d’enrichir vos données. Et que se passerait-il si vous aviez accès à d’autres données ? Que vous pouviez les connecter avec celles que vous possédez déjà ?

Connaissez-vous les API ? Les « Application Programming Interface » ? Dans la langue des informaticiens, cela veut dire : accéder à un logiciel et à ses fonctionnalités à l’aide de la programmation. Si vous remettez dans le contexte, ici, il ne s’agit pas de faire ses courses en lignes, mais plutôt d’accéder à des données.

Une mine de données se trouvent par exemple sur le site https://data.gouv.fr. De là, vous pouvez connecter directement votre fichier Excel aux données actualisées du site officiel !

Conclusions sur Power Query

Vous avez vu tout au long de cet article, des illustrations, des exemples et une courte présentation de l’interface. Power Query est véritablement une plateforme à maîtriser (et si vous EXCELLER avec, c’est mieux !).

Je n’ai pas la possibilité dans un seul article de développer toutes les possibilités. Et l’une mériterait pourtant un développement, Power Query est le préparateur caché de Power BI !

Ainsi, si vous souhaitez rentrer dans l’univers de Power BI, vous aurez un passage obligé par Power Query ! Et si vous êtes prêt à découvrir Power BI, il ne reste qu’à vous inscrire à la liste d’attente ci-dessous :

Prenez

le prochain départ vers

un nouveau monde

Tous vos commentaires sont les bienvenus pour enrichir l’article, partager vos expériences !

RechercheV avec plusieurs critères, sur un plateau

RechercheV avec plusieurs critères, sur un plateau

La fonction RECHERCHEV() est utilisée pour récupérer les données complémentaires qui se trouvent dans un second tableau, sur la base d’un unique critère (une référence ou un code d’identification). Et si vous devez vous baser sur plusieurs critères ? Comment réaliser cette opération avec RECHERCHEV() ? Est-ce la meilleure fonction pour réaliser cette opération ?

Bref rappel du fonctionnement de la RECHERCHEV() avec un seul critère

Les conditions de départ sont les suivantes pour utiliser la fonction RECHERCHEV() :

  1. Vous devez disposer d’un tableau « de référence » (vos articles, vos fournisseurs, etc.).
  2. Vos données (articles, fournisseurs, etc.) contiennent un identifiant unique. Avec cette information, n’importe qui saurait de quelle ligne de données vous parlez.
  3. Votre identifiant est positionné dans les colonnes PRÉCÉDANT les données détaillées.
  4. Vous disposez d’un second tableau où cet identifiant est utilisé.

Exemple : votre objectif est de récupérer la description détaillée d’un article à partir de son code.

Tableau 1

Code articleDescription
1Chaise
2Table
3Bureau
Table de référence

Tableau 2

N°FactureCode articleDescription
11
12
22
33
Table où vous devez utiliser RECHERCHEV()

Ici, vous souhaitez compléter automatiquement le texte de la description à l’aide de la fonction RECHERCHEV().

Celle-ci s’utilise ainsi :

RECHERCHEV(
valeur recherchée – ici, c’est le code article, à partir du tableau des factures ;
table où rechercher – attention, bien fixer la plage de recherche avec les $ ou utiliser les tableaux nommés ;
numéro de la colonne à retourner – cela veut dire que le tableau où rechercher doit inclure la donnée à retourner !! ;
acceptez-vous les valeurs approximatives – par défaut, la valeur est à VRAI !, donc si vous ne renseignez pas FAUX, vous aurez des surprises…).

Comme vous pouvez le voir, cette fonction ne semble pas laisser de place à l’utilisation de plusieurs critères…

Pour une RECHERCHEV() avec plusieurs critères, une seule solution : Abattre les limites de la RECHERCHV() !

RechercheV vers la gauche

Mon commentaire est peut-être passé inaperçu dans mon rappel de l’utilisation de la RECHERCHEV(), il est pourtant essentiel. La valeur recherchée doit se trouver dans les colonnes PRÉCÉDANT celles du tableau de recherche. Sinon, cela ne fonctionne pas…

Ainsi, si votre tableau de référence ressemble à ceci, les conditions d’utilisation de RECHERCHEV() ne sont pas respectées :

Tableau 1

DescriptionCode article
Chaise1
Table2
Bureau3
Tableau de référence

Heureusement, d’autres fonctions existent pour cela !

RECHERCHEV à l’envers pour Excel 365

Microsoft a ajouté une nouvelle fonction bien plus puissante : RECHERCHEX(). Cette fonction regroupe à elle seule RECHERCEV(), RECHERCHEH() et l’ancien RECHERCHE()… en mieux !

Je ne m’attarderai pas sur toutes les possibilités de cette nouvelle fonction ici, mais uniquement sur celle qui fait que la recherche est souvent nommée « à l’envers » car elle peut aller vers la gauche !

Au lieu de renseigner le numéro de la colonne à retourner, vous renseignez ici directement la colonne à retourner ! Où qu’elle soit…

=RECHERCHEX (
valeur recherchée – Ici, rien ne change ;
tableau où rechercher – Plus besoin de tout le tableau, uniquement la colonne où se trouve la valeur recherchée ;
tableau à retourner – Et au lieu du n° de la colonne, vous indiquez la colonne entière où se trouve la donnée à retourner. Et elle peut se trouver avant, après c’est vous qui voyez !).

Cette nouvelle fonction résout donc le problème de la recherche vers la gauche. Mais elle ne permet toujours pas d’avoir une RechercheV avec plusieurs critères…

C’est pourquoi, vous devez continuer la lecture de cet article !

RECHERCHEV à l’envers pour toutes les versions d’Excel

Ce n’est pas une fonction que vous aurez à utiliser ici, mais deux ! Et chacune d’entre elles prise indépendamment a l’apparence d’une fonction presque simpliste… alors que leur potentiel va bien plus loin et leur combinaison en fait un outil très puissant !

Ces deux fonctions sont INDEX() et EQUIV() – pour les versions anglaises, elles portent le nom de INDEX() et MATCH().

INDEX() sert à retourner le contenu de la cellule d’un tableau dont vous avez fourni les coordonnées.

INDEX(
Tableau – En général, c’est une colonne, mais cela peut être un tableau ;
numéro de ligne – Vous fournissez le numéro de la ligne où se trouve votre cellule ;
numéro de colonne – Cette valeur est optionnelle et fixée à 1 par défaut car on l’utilise souvent pour une seule colonne de données.

A priori, rien de transcendant… vous donnez les coordonnées de la cellule et Excel vous la retourne. Bon…

La deuxième fonction est EQUIV(). Celle-ci renvoie le numéro de la ligne où se trouve une valeur que vous indiquez.

EQUIV(
Valeur recherchée;
Tableau où se trouve la valeur recherchée – En général, c’est une colonne d’un tableau;
Type de recherche – Ici, on recherche l’égalité, ce sera donc 0.

Et lorsque l’on combine les deux, on peut alors faire de très belles choses ! Voyons de plus près :

INDEX renvoie le contenu d’une cellule à partir d’un tableau (en général une colonne de valeurs) et EQUIV renvoie le numéro de ligne à partir d’une colonne de valeurs (NOTA – je simplifie volontairement…).

De plus, chacune de ces deux fonctions possède SON TABLEAU dans les critères. Ainsi, EQUIV va donner un rang dans une colonne et INDEX va retourner ce rang d’une AUTRE colonne (puisque nous avons le choix des paramètres !).

Exemple d’utilisation de INDEX et EQUIV en vidéo

RechercheV avec plusieurs critères : L’équation impossible ?

Pour atteindre cet objectif, une autre étape intermédiaire doit être expliquée : les calculs matriciels (une introduction seulement…).

C’est ainsi… Exceller avec Excel passe par une meilleure compréhension de certains calculs mathématiques. En particulier, certaines notions de calculs matriciels.

Initiation aux opérations matricielles dans Excel

L’objectif n’est pas de devenir mathématicien. Juste manier Excel !

Si vous êtes l’heureux(-se) possesseur(-e) d’une licence Excel 365, vous pouvez directement tester ces fonctions dans Excel, pour les autres, vous devrez saisir ces mêmes fonctions en pressant « Ctrl + Shift + Enter »…

RechercheV Plusieurs criteres - Initiation calcul matriciel
RechercheV plusieurs critères – Initiation calcul matriciel

Au calcul =(B2:B4=F2), Excel va construire en mémoire « le tableau / la matrice » résultat.

Petite astuce ici… en ajoutant deux fois le symbole « – » devant, Excel va inverser deux fois le résultat et convertir l’affichage en valeur numérique :

RechercheV Plusieurs criteres - Calcul matriciel - Obtenir des uns et zeros
RechercheV Plusieurs critères – Calcul matriciel – Obtenir des 1 et des 0

Ce résultat indique que la valeur recherchée se trouve à la première position de notre tableau.

Produit matriciel : La lumière au bout du tunnel de notre RechercheV avec plusieurs critères

Maintenant, passons à l’étape avec deux critères sur deux colonnes différentes. J’ai ajouté une notion de collection, et notre objectif est d’identifier quel prix doit s’appliquer dans le tableau des factures.

De la même manière que précédemment, les matrices d’égalité (–(B2:B7=G2)) et (–(C2:C7=I2)) permettent d’identifier où se trouvent les cellules avec la bonne valeur.

L’astuce consiste à multiplier ces deux « matrices/tableau » entre elles. Excel va alors multiplier le premier élément avec le premier, le second avec le second, etc…

Finalement, une seule cellule du tableau résultat contient la valeur 1. Cela signifie que c’est la première ligne qui répond aux deux critères !

RechercheV Plusieurs criteres - Produit matriciel
RechercheV Plusieurs critères – Produit matriciel

Assemblage des pièces du puzzle : La RechercheV avec plusieurs critères se révèle à travers INDEX et EQUIV + les produits matriciels !

Rappelez-vous précédemment : nous avons vu que INDEX renvoie le contenu d’une cellule, lorsqu’on lui dit laquelle. Et EQUIV nous indique où se trouve une valeur dans un tableau. Enfin, le produit matriciel des « égalités » indique à l’aide de la valeur 1 à quelle ligne se trouve la cellule qui répond à tous les critères.

En combinant le tout, cela donne :

=INDEX(
$D$2:$D$7; – La colonne contenant les prix.
EQUIV(
1; – Comme vu précédemment, c’est la ligne où se trouve 1 qui donnera le résultat
(–($B$2:$B$7=G2))*(–($C$2:$C$7=I2)))) – Le fameux produit matriciel.

L’article en vidéo

Pour développer cet article, je vous propose un autre exemple illustré à l’aide de cette vidéo.

Votre avis sur cet article

Avez-vous réussi à mettre en pratique cette « RECHERCHEV avec plusieurs critères » ? Avez-vous des zones d’ombres ou des difficultés à la mettre en pratique ?

Votre avis compte et votre feedback m’est très utile ! Utilisez la zone de commentaires pour cela !

La fonction LAMBDA : La révolution est en marche !

La fonction LAMBDA : La révolution est en marche !

En introduisant la fonction LAMBDA, Microsoft initie un véritable changement de paradigme dans l’utilisation d’Excel. Je vous l’annonce depuis un moment maintenant, le VBA n’est pas l’avenir d’Excel et ma position sur les macros. Comment parvenir à changer les esprits ? En offrant une alternative pertinente à chacune des motivations d’utilisation du VBA.

Un nouveau pas vient d’être franchi : Vous permettre de développer vos propres fonctions SANS VBA ! Oui, la fonction LAMBDA est une fonction permettant d’en écrire d’autres !

Bienvenue dans le nouveau monde d’Excel : un monde où les limites tombent les unes après les autres !

À ce jour, seuls les détenteurs d’un abonnement à Excel 365 ont accès à la fonction LAMBDA (Ce n’est même accessible que pour les membres du programme Office Insider qui permet d’accéder aux fonctions en avant-première, avec les risques de bugs qui vont avec…).

La fonction LAMBDA : Tout sauf quelconque

Son nom pourrait laisser penser qu’elle est quelconque… en réalité, elle est véritablement révolutionnaire dans le monde d’Excel ! Vous pensez que j’exagère ? Regardons de plus près !

Votre première fonction LAMBDA

Cette fonction est d’un nouveau genre. Elle permet de créer d’autres fonctions qui pourront être appelées par d’autres formules.

Fonction LAMBDA - Decouvrir
Fonction LAMBDA – Premiers pas

Hmmm… C’est intéressant comme idée : une fonction pour écrire des fonctions. Mais comment cela fonctionne-t-il ? Comment l’utilise-t-on ? Comment définit-on les paramètres de cette fonction ? De combien de paramètres peut-on disposer au maximum ?

Les paramètres de votre fonction LAMBDA

Lorsqu’on pense aux fonctions Excel, on pense à SI(), RECHERCHEV(), SOMME(), etc. Chacune d’entre elles possède des paramètres d’appel et vous savez également quel type de résultat vous aurez en retour.

Par exemple, la fonction SOMME() prend jusqu’à 255 paramètres et chacun de ces paramètres peut être un nombre, une référence à une cellule ou une référence à une plage de cellules.

Alors, de combien de paramètres avez-vous besoin pour votre fonction LAMBDA ?

Prenons un exemple : Vous êtes un étudiant ou un enseignant et souhaitez calculer la moyenne des notes pondérées par des coefficients.

Fonction LAMBDA - Moyenne des notes avec coefficients
Moyenne des notes avec coefficients

Ici, la formule cible est

=SOMMEPROD( [Coefficient]; [Note] ) / SOMME( [Coefficient] )

Il n’y a donc que deux paramètres : la colonne des coefficients et la colonne des notes. Avec ces deux informations, il est possible d’écrire la formule.

Pour ceux qui ne connaissent pas la fonction SOMMEPROD( Plage1; Plage2), elle permet de réaliser le calcul :

(1er élément de la Plage1 x 1er élément de la plage 2) + (2e Plage1 x 2e Plage2) + etc

Écrire une fonction LAMBDA

Par nature, Excel ne peut deviner le nombre de paramètres dont vous aurez besoin dans votre future fonction… Il va donc TOUJOURS considérer que le dernier paramètre d’appel (de la fonction LAMBDA) correspondra à votre formule de calcul. Les premiers éléments étant les variables dont vous aurez besoin pour votre formule…

Ce n’est pas très clair ? Voyons de plus près avec le calcul de la moyenne pondérée des notes.

Commençons par un retour sur les bancs de l’école avec un bel énoncé :

Soit c l’ensemble de mes coefficients et n l’ensemble de mes notes pour calculer la moyenne pondérée. Veuillez trouver la formule pour calculer la moyenne pondérée. Vous avez 2 minutes !

En même temps, je vous ai donné la réponse quelques lignes plus haut. Il « suffit » de placer des variables à la place des plages.

La formule devient donc « SOMMEPROD(c;n)/SOMME(c)« .

Est-ce terminé ? Pas tout de suite… encore un peu de patience.

Une fonction LAMBDA s’écrit de la manière suivante :

=LAMBDA(ma_première_variable; ma_seconde_variables; …, ma_nième_variable; mon_calcul_qui_utilise_les_variables)

Afin de vous y retrouver, je vous recommande d’utiliser des noms de variables qui vous parlent. Les plus mathématiciens d’entre vous utiliseront peut-être les fameux x, y, z comme variables. Ici, je choisis d’utiliser les premières lettres de « Coefficient » et « Notes » pour nommer les variables dont nous avons besoin.

La fonction LAMBDA définitive s’écrit donc ainsi :

=LAMBDA(c; n; SOMMEPROD(c;n)/SOMME(c) )

Comment invoquer une fonction LAMBDA ? Doit-on simplement écrire cette fonction dans une cellule ?

Malheureusement, si vous inscrivez cette fonction juste ainsi, vous recevrez un joli message d’erreur de la part d’Excel : #CALC!

En effet, vous venez d’écrire une fonction … que vous n’avez pas appelée ! C’est comme si vous écriviez « =SI() ». Excel vous retournerait la même erreur !

Pour que cela fonctionne dans une cellule, il faut renseigner les valeurs à attribuer aux paramètres d’appel, en l’occurrence, les deux paramètres c et n avec les valeurs des deux plages de valeurs.

=LAMBDA(c; n; SOMMEPROD(c;n)/SOMME(c))(Notes[Coefficient]; Notes[Note])

À ce stade, je sens naître en vous une pointe de scepticisme… Vous auriez pu écrire la fonction directement au lieu de passer par l’appel à la fonction LAMBDA…

Fonction LAMBDA - Comparaison ecriture fonction
Fonction LAMBDA – Comparaison écriture fonction

Sauf que ce n’est pas terminé… cette étape essentielle permet de vérifier que votre fonction est bien écrite et retourne le résultat attendu. Maintenant, vous allez pouvoir inscrire votre fonction LAMBDA dans votre classeur Excel pour pouvoir être réutilisée.

Donner un NOM à votre fonction

Finalement, lorsqu’on y pense, le meilleur endroit pour donner un nom à votre fonction LAMBDA, c’est justement dans le gestionnaire de noms !

Fonction LAMBDA - Gestionnaire de noms
Fonction LAMBDA – Gestionnaire de noms

Ensuite, vous aurez à donner un nom à votre fonction :

Fonction LAMBDA - Nommer sa fonction
Fonction LAMBDA – Nommer sa fonction

Ici, chacun des points de l’illustration a son importance.

  1. Le nom de votre fonction
    C’est celle qui sera présentée dans l’assistance à la saisie. Je vous invite à nommer de manière explicite votre fonction afin d’immédiatement identifier son objectif .
  2. Le commentaire
    Ce que vous écrirez ici apparaîtra dans l’infobulle de votre fonction. Ici, pas de mise en forme possible (retour chariot, gras, italique, etc.). Si vous disposez de nombreux paramètres de saisie, ma recommandation : Explicitez chacun d’entre eux de manière concise.
  3. Fait référence à
    C’est ici que vous saisissez votre fonction LAMBDA, et uniquement la définition de la fonction, sans aucune référence à des cellules ou des plages de votre classeur.

Et voilà ! Votre première fonction LAMBDA est écrite !

Utiliser votre fonction LAMBDA nouvellement créée

Dès le moment où vous avez cliqué sur OK sur la fenêtre précédente, Excel a désormais connaissance de la nouvelle fonction. Voyez par vous-même :

Fonction LAMBDA - Utiliser sa fonction
Fonction LAMBDA – Utiliser sa fonction

Vous retrouvez ici le texte saisi précédemment dans le champ « Commentaire ». Vous comprendrez qu’une description trop longue rendrait la lecture difficile.

Toutefois, contrairement aux fonctions Excel, ici, pas d’assistance contextuelle pour chacun des paramètres de la fonction.

Assistance paramètres
Assistance paramètres

Alors que normalement, vous disposez d’une assistance pour la liste des paramètres obligatoires ou optionnels (les paramètres optionnels se présentent entre crochets) :

Assistance parametres - RechercheV
Assistance paramètres – RechercheV

Vous devrez donc connaître (ou bien décrire dans le commentaire) la liste des paramètres à renseigner.

Désormais, vous pouvez utiliser cette formule pour calculer la moyenne pondérée :

Fonction LAMBDA - Utiliser sa fonction
Fonction LAMBDA – Utiliser sa fonction

L’accès aux fonctions de niveau supérieur : les fonctions LAMBDA récursives

Je dois vous avertir : Vous entrez en zone dangereuse. L’utilisation de la récursivité est une notion qui n’est pas destinée aux débutants. Cette notion est habituellement enseignée à celles et ceux qui apprennent la programmation.

En effet, une fonction récursive est une fonction qui s’appelle elle-même dans la formule qu’elle représente. Quand on y pense, cela peut dans un premier temps être très troublant.

Dans l’utilisation courante d’Excel, cette forme d’utilisation vous rappelle peut-être l’erreur dite de « référence circulaire » ?

Reference circulaire
Référence circulaire

Le plus simple, c’est de comprendre à l’aide d’un l’exemple.

Votre première fonction LAMBDA récursive

Les fonctions récursives sont utiles lorsqu’on doit répéter une opération en boucle jusqu’à ce qu’une certaine condition soit vraie. Cela reste encore trop théorique ? C’est normal.

Prenez le tableau de valeurs suivant :

RelevésValeurs
Relevé 11
Relevé 243
Relevé 35
Relevé 465
Relevé 576
Relevé 6678
Relevé 7789
Relevé 843

Maintenant, imaginez que vous souhaitiez ajouter une colonne présentant la somme cumulée des valeurs des relevés précédents.

Cela représenterait les calculs suivants :

RelevésSomme cumulée
Relevé 1Valeur 1
Relevé 2Valeur 1 + Valeur 2
Relevé 3Valeur 1 + Valeur 2 + Valeur 3
Relevé 8Valeur 1 + Valeur 2 + Valeur 3 + Valeur 4 + Valeur 5 + Valeur 6 +Valeur 7 + Valeur 8

Un des raisonnements que l’on peut tenir est :

Je souhaite additionner la valeur du relevé actuel avec les valeurs précédentes jusqu’à arriver à la ligne des en-tête.

C’est exactement de cette manière que notre fonction LAMBDA sera construite.

Utiliser un nom de fonction qui n’existe pas encore… Étrange n’est-ce pas ?

Comme nous l’avons vu précédemment avec votre première fonction LAMBDA, elle n’existera que lorsque vous aurez créé un NOM avec le gestionnaire de nom.

Pour les fonctions récursives, vous aurez la même procédure à suivre. Toutefois, vous devrez connaître le nom de votre fonction avant qu’elle n’existe puisque vous allez l’utiliser dans votre formule.

Vous aurez également à déterminer le critère de sortie de la récursivité. Sinon… vous resterez enfermé à jamais dans la fonction ! :).

Déterminer les paramètres de votre fonction

Ici, j’ai identifié 3 paramètres :

  1. L’identifiant du relevé actuel
  2. La valeur du relevé actuel
  3. La valeur de l’en-tête des relevés du tableau

Pour construire ma fonction récursive, je raisonne donc ainsi :

Ajouter les valeurs du relevé actuel au total précédent jusqu’à ce que la valeur de la colonne « Relevés » soit celle de l’en-tête de mon tableau.

Écrire une fonction récursive pas à pas

Maintenant que les paramètres sont identifiés, il faut nommer à l’aide de variables (une chaîne de caractères quelconque qui représentera votre paramètre dans la formule finale).

Dans mon cas, je vais utiliser les variables suivantes :

ParamètreNom de la variable
Identifiant du relevé actuelr
Valeur du relevé actuelv
Valeur de l’en-têtearret

J’ai choisi ensuite de nommer ma fonction « SommeCumulee ».

L’idée maintenant est de procéder comme suit :

  1. Tester si la ligne courante est celle de l’en-tête, si oui, on renvoie la valeur sinon on renvoie zéro. C’est notre critère de sortie de la récursivité.
  2. Ensuite, on décale la ligne de référence à la ligne précédente puis on appelle la fonction SommeCumulee avec les nouveaux paramètres actualisée. La récursivité est maintenant démarrée.
  3. C’est tout !

Cela donne la fonction LAMBDA suivante (j’ai ajouté des espaces et sauts de lignes pour plus de lisibilité) :

=LAMBDA(r; v; arret;
SI(r=arret ; 0 ; SommeCumulee( DECALER(r; -1; 0); DECALER(v; -1; 0); arret) + v)

La fonction DECALER() permet de se déplacer d’une cellule à l’autre. Ici, le deuxième paramètre étant le nombre de déplacements en ligne à réaliser. La valeur -1 indique qu’on se dirige vers la ligne juste au-dessus.

Il faut bien évidemment déplacer également la cellule contenant les valeurs, d’où l’utilisation de DECALER() à deux reprises.

Ainsi, la fonction va être appelée en boucle jusqu’à ce que la cellule corresponde à la cellule d’arrêt.

On se rend alors dans le gestion des noms et complétons ainsi :

Fonction LAMBDA - Fonction recursive
Fonction LAMBDA – Fonction récursive

Utiliser la fonction récursive dans un tableau

Maintenant que votre fonction est écrite, il ne reste qu’à l’utiliser !

Comme précédemment, vous n’aurez que l’assistance décrite dans la zone « Commentaire » de votre NOM.

Grâce à cette fonction, vous pourriez même imaginer l’utiliser pour cumuler les valeurs à partir du relevé 2 ou 4 ! Eh oui, ce n’est qu’un paramètre à changer !

Attention à deux choses tout de même :

  1. Si vous ne fixez pas le 3e paramètre, il sera adapté par Excel à chaque ligne.
  2. La formule va s’appliquer à toutes les cellules de la 3e colonne. Sauf que pour les cellules qui sont avant la cellule d’arrêt… il n’y a plus rien pour les arrêter qu’une erreur (l’appel à la fonction DECALER vers la ligne 0 par exemple).

A ces deux réserves près, vous pourrez faire des sommes cumulées à votre goût !

Fonction LAMBDA - Utiliser sa fonction recursive
Fonction LAMBDA – Utiliser sa fonction récursive

Vous n’aurez alors qu’à effacer la formule dans les cellules où la formule n’est pas applicable.

Un exemple en vidéo : La fonction Fibonacci

Quoi de mieux qu’un exemple en vidéo ? Je vous propose de découvrir la fonction LAMBDA à travers une vidéo.

Dans un premier temps, je vous présente une simple fonction avec trois paramètres (a, b, c) et ensuite, une fonction récursive, et pas des moindre puisqu’il s’agit de la fonction Fibonacci !

Je vous rappelle que la suite de Fibonacci se définit ainsi :

F0 = 0 / F1 = 1 / Fn = Fn-1 + Fn-2

Les fonctions LAMBDA ont-elles des limites ? Et ensuite ?

Actuellement, comme de nombreuses fonctions EXCEL, la fonction LAMBDA ne peut recevoir « que » 255 paramètres. Donc, 254 variables + 1 pour la fonction à définir.

Comme je l’ai mentionné à plusieurs reprises, dans la version actuellement à disposition, il n’y a pas d’assistance à l’utilisation de la fonction comme les autres fonctions d’Excel. Vous devrez donc bien expliquer dans le champ Commentaire la liste des paramètres et vous en souvenir lors de son utilisation.

Pourquoi est-ce une révolution ?

Jusqu’à présent, vous n’aviez pas de choix pour créer vos fonctions personnalisées : vous deviez apprendre le VBA et créer des macros.

Le détournement des macros réalisé par les pirates informatiques pour transmettre des virus à l’aide des fichiers Excel, Word ou PowerPoint fait maintenant trop de dégâts.

Microsoft le sait mais n’avait rien d’autre à proposer aux utilisateurs avancés d’Excel pour la création de fonction personnalisée. C’est maintenant chose faite !

Lorsqu’on compile les dernières avancées majeures d’Excel, on note Power Query, la possibilité d’interagir avec Excel avec du code JavaScript et maintenant les fonctions LAMBDA ! Le mouvement est très clair (à mes yeux) : offrir toutes les options pour sortir des macros !

Vous n’êtes pas d’accord ? Vous souhaitez en discuter ? Cet article vous a plu (ou pas !), faites-le moi savoir !

La zone de commentaires est à vous ! A tout de suite !

Apprendre Power Query : Pourquoi Excel est limité !

Apprendre Power Query : Pourquoi Excel est limité !

Apprendre Power Query devient une nécessité pour aller plus loin avec Excel. Ce composant a fait son apparition avec la version 2013 d’Excel et depuis 2016 est intégré nativement à Excel. Il remplace peu à peu certaines fonctionnalités historiques d’Excel.

Qu’est-ce que Power Query ? Pourquoi faut-il l’apprendre ? En quoi est-ce qu’Excel est limité ? Et pourquoi Power Query va vous permettre d’aller plus loin ? Par où commencer ? Et après ? Toutes ces questions seront traitées dans cet article. Il ne tient qu’à vous de le lire et d’aller plus loin avec les outils à votre disposition !

Cet article se veut une introduction au monde de Power Query. Je vais donc utiliser des analogies et non les termes techniques exacts que vous aurez le temps d’apprendre le moment venu.

Qu’est-ce que Power Query ?

Power Query permet de réaliser des opérations en masse sur vos données. Vous allez pouvoir intégrer plusieurs sources de données et les manipuler à souhait. Voilà.

Si ma description de Power Query s’arrêtait là, on pourrait penser à Excel. Ce n’est pas suffisant pour vous donner envie de l’apprendre ? C’est effectivement un peu léger comme description…

Power Query offre la possibilité de manier les données comme un cuisinier manie ses ingrédients. Vous pourrez découper, séparer, réunir, ajouter des données supplémentaires et cuisiner vos données pour préparer vos rapports.

Cela ressemble à Excel d’une certaine manière, et en même temps, c’est très différent. Véritablement différent.

En plus de réaliser ces opérations en masse, vous pourrez créer un modèle de données à partir d’un simple tableau. Afin que cela ait un peu de sens, vous devez disposer de grands tableaux (en nombre de lignes et en nombre de colonnes). Ainsi, vous pourrez commencer à réellement extraire le nectar que représente Power Query et vous aurez bientôt envie de l’apprendre !

Qu’est-ce qu’une opération en masse ?

Partons d’Excel que vous connaissez pour distinguer les opérations en masse des opérations que je nommerai unitaires.

Lorsque vous insérez une fonction dans une cellule d’Excel aussi simple qu’une addition « =A1+B1 », vous vous attendez à ce qu’en tout temps, la cellule contenant cette opération vous donne le résultat de l’addition.

Hormis la situation d’exception décrite un peu plus bas, c’est effectivement toujours le cas dans Excel.

Lors d’une opération en masse, ceci n’est plus vrai.

Considérez Power Query comme une chaîne automatisée d’une usine. Vous placez la matière première (ici les données) au début de la chaîne. L’usine va réaliser tous les traitements et vous donner le produit fini.

Si vous changez une pièce après que le produit soit sorti de la chaîne, cela ne change pas le résultat. Il faut jeter le produit et relancer la machine avec toutes les matières premières.

Cela ressemble énormément à ce que les macros permettent de faire : automatiser certains traitements. C’est vrai que cela y ressemble. Mais la programmation avec les macros est la voie du passé. Les risques de sécurité liés à l’utilisation des macros deviennent trop grands. Les macros seront bannies dans les prochaines années. Alors ne soyez pas en retard, tournez-vous vers les manières contemporaines de traiter les données : apprenez Power Query, Power BI et les fonctions Lambda !

Exception pour la MAJ des données automatiques dans Excel

Je fais une aparté ici concernant la mise à jour automatique des données dans Excel. Combien de fois ai-je failli devenir fou devant Excel lorsque le résultat ne correspondait pas à la formule… J’avais oublié que l’on pouvait désactiver l’actualisation automatique des calculs !

Apprendre Power Query - Option de calcul - Manuel
Option de calcul – Manuel

Cette option est très utile lorsque le temps d’actualisation de votre classeur est très long. C’est le cas lorsque vous avez beaucoup de formules ou qu’elles ne sont pas optimisées.

Quels types d’opérations peut-on réaliser avec Power Query ?

À partir de maintenant, vous devez penser à vos données comme un ensemble. Les opérations s’appliquent à l’ensemble uniquement.

Ainsi, l’opération « Ajouter » ne correspond à aucune addition. Si vous pensez « ensemble de données », « Ajouter » signifiera « Mettre plus de données dans mon ensemble ». Dans l’article, comment fusionner plusieurs fichiers Excel, je le précise déjà dans le scénario d’utilisation de Power Query.

Les opérations principales de Power Query

  • Obtenir des données – Intégrer des données venant d’Excel ou d’ailleurs
  • Ajouter des données – Mettre à la suite des ensembles de données de même structure (Pour les connaisseurs : UNION en SQL)
  • Fusionner des données : Enrichir les données d’un ensemble avec les données d’un autre ensemble (Pour les connaisseurs JOIN en SQL)
  • Ajouter / Supprimer des colonnes – Différentes modalités d’ajout existent (colonne calculée, colonne d’index automatique de ligne par exemple)
  • Supprimer des lignes selon vos critères (suppression des doublons, suppression de toutes les lignes sauf les doublons par exemple)
  • Transposer vos données – Une opération qui existe sous forme de fonction dans Excel 365 désormais
  • Pivoter une ou plusieurs colonnes – Cette opération ne devrait pas beaucoup vous parler à ce stade (et rien à voir avec les Pivot Table (soit le terme anglais de Tableau Croisé Dynamique)).

De nouveau, à ce stade, vous vous demandez encore quel est l’intérêt d’apprendre Power Query ? Excel sait fait tout cela… C’est vrai en partie seulement (je vous mets au défi de pivoter plusieurs colonnes sans programmation…).

Mais, car il y a un mais, Excel ne sait pas automatiser (sans programmation) l’exécution de ces opérations ! Quand est-ce cela est nécessaire ? Lorsque vous recevez des fichiers de vos fournisseurs, collègues ou clients à analyser.

Illustrons ces transformations à partir d’un fichier fourni par l’Université Johns Hopkins sur le COVID-19. Mais juste avant, encore un mot sur la place que prend Power Query au sein d’Excel.

L’obtention des données externes s’appuie déjà sur Power Query

Lorsque vous utilisez les fonctionnalités présentes dans l’onglet Données > Obtenir des données, Excel utilise Power Query.

Apprendre Power Query - Obtenir des données
Apprendre Power Query – Obtenir des données

Vous pourrez par exemple importer les données qui se trouvent dans un tableau d’un fichier PDF !

La combinaison des requêtes – Ajouter ou fusionner – utilise Power Query aussi

Dans l’article sur comment fusionner plusieurs fichiers Excel en un seul, je vous ai déjà présenté cette possibilité de mise en commun des données de plusieurs tableaux de données (voire de fichiers). C’est encore Power Query qui est utilisé ici.

Apprendre Power Query - Combiner des requêtes
Apprendre Power Query – Combiner des requêtes

L’import de données CSV dans Excel – Power Query a chassé l’ancien assistant d’importation de texte

L’ancien composant permettant d’importer les données des fichiers textes ou CSV disparaît au profit de Power Query une fois de plus.

Vous devrez ajouter manuellement la commande dans le ruban (encore présente mais masquée) pour retrouver cet assistant :

Apprendre Power Query - Assistant importation de texte
Apprendre Power Query – Assistant importation de texte

Désormais, vous aurez l’interface de Power Query :

Apprendre Power Query - Assistant importation de fichier CSV
Apprendre Power Query – Assistant importation de fichier CSV

L’importation des données depuis le web – Power Query est partout !

Les utilisateurs les plus avancés connaissaient cette fonction d’import des données web (on appelle cela le web scraping). Là encore, terminé la vieille interface :

Apprendre Power Query - Assistant importation depuis web - Ancienne version
Apprendre Power Query – Assistant importation depuis web – Ancienne version

Power Query est venu également remplacer ce composant, voyez par vous-même la nouvelle interface :

Apprendre Power Query - Assistant importation depuis web
Apprendre Power Query – Assistant importation depuis web

Power Query est partout où vous aurez à interagir avec Excel et l’extérieur d’Excel. Mais ceci n’est que la partie émergée de l’iceberg. La puissance de Power Query est bien ailleurs.

En quoi est-ce qu’Excel est limité ? Et pourquoi apprendre Power Query va vous permettre d’aller plus loin ?

La finalité d’Excel et ses limites

Excel peut être utilisé selon deux principes opposés :

  1. Excel est l’application de saisie de vos données, puis vous utilisez son potentiel pour les analyser (tableau croisé dynamique, graphiques, fonctions, etc.)
  2. Vous utilisez Excel uniquement pour ses capacité d’analyse de vos données qui ont été exportées depuis d’autres applications

Dans les deux cas, c’est dans la partie analyse que vous risquez d’atteindre les limites d’Excel.

Excel, comme n’importe quel logiciel, possède des limites techniques. Celles-ci sont d’ailleurs documentées par Microsoft.

Depuis que Microsoft a étendu le nombre de lignes et de colonnes d’Excel, ces limites techniques sont rarement atteintes.

Les autres limites sont celles que vous allez introduire dans des formules complexes, le nombre de tableaux croisés dynamiques à actualiser. Toutes ces formules à appliquer et ces TCD à rafraîchir vont mettre de plus en plus de temps.

Je vous avertis, pour côtoyer les limites d’Excel, il faut avoir soit beaucoup de données à traiter, soit beaucoup d’analyses différentes à réaliser.

Avertissement sur Excel en mode application de gestion

A titre personnel, je ne vous recommanderai jamais d’utiliser Excel pour en faire une application de gestion (gestion des stocks, gestion de la facturation, etc.).

De nombreuses raisons motivent cette prise de position :

  1. Toutes vos données sont stockées dans un simple fichier. En une manipulation, vos données peuvent être altérées ou détruites.
  2. Malgré la capacité de coédition, travailler à plusieurs sur un fichier Excel « Gestion du stock » vous posera des difficultés ou des erreurs.
  3. Pour ajouter des fonctionnalités, vous devez travailler sur le fichier contenant les dernières données… au risque de les altérer.
  4. Aucune gestion des droits d’accès ou de modification possible (eh non, la fonctionnalité de protection des feuilles/classeurs ne sécurise rien. Il est très simple de la supprimer sans connaître le mot de passe de protection).

Votre fichier Excel devient très lent et met du temps à rafraîchir les données ?

Vous êtes devenu un expert en RECHERCHEV() – entre nous, si vous êtes devenu cet expert, vous êtes passé soit à RECHERCHEX(), soit à INDEX() + EQUIVX() – et en avez mis partout ? Et vous êtes encore étonné que ce soit lent ?

Par exemple, vous avez un premier tableau rempli de références produits et dans un second tableau les caractéristiques détaillées de ces derniers. À l’aide de plusieurs RECHERCHEV(), vous avez ajouté des colonnes « Détails produits ».

Autre exemple, vous adorez les TCD (c’est mon cas…), et en avez insérés 5, 6, 10 ? Lors de la mise à jour des données, votre feuille met beaucoup de temps à vous redonner la main…

Alors comment optimiser vos fichiers et en tirer toutes les analyses souhaitées ?

Power Query pourrait-il vous y aider ?

Par où commencer pour apprendre Power Query ? Et après ?

Apprendre Power Query par l’exemple : Analyse des données COVID-19

Il est temps de prendre les rênes de Power Query pour commencer à l’apprendre. Comme indiqué plus haut, je vous propose d’utiliser les données de l’Université Johns Hopkins sur la COVID-19.

Les données sont publiées dans le repository GitHub de l’Université. Voici le lien vers le fichier CSV que je vais utiliser (pensez à faire un clic-droit puis « Enregistrer sous… »).

Option 2 : Vous pouvez directement référencer l’adresse web du fichier CSV qui est mis à jour quotidiennement. Ainsi, vous n’aurez qu’à rafraîchir les données (et disposer d’une connexion à Internet) pour avoir les données actualisées.

Étape n°1 : Importer votre fichier CSV à l’aide Power Query – À l’aide du fichier CSV

Une fois enregistrée, rendez-vous sur le ruban « Données » > « À partir d’un fichier texte/CSV » > Sélectionnez le fichier CSV depuis votre dossier > Cliquez sur Importer

Importation CSV Donnees COVID-19
Importation CSV Données COVID-19

Étape n°1 bis : Importer votre fichier CSV directement depuis sa source web

Cette option est intéressante pour avoir les données actualisées quotidiennement depuis votre fichier Excel. En effet, l’adresse du fichier ne change pas d’un jour à l’autre. Cela rend son importation web très simple et très efficace.

Petit avertissement tout de même : dans la mesure où Excel va réaliser des requêtes web, vous aurez un avertissement de sécurité à chaque ouverture du fichier. Pour vous qui avez conçu le fichier et défini la source de données, pas de soucis. En revanche, si vous envoyez le fichier à une autre personne, celle-ci pourrait (devrait !) légitimement se méfier.

Rendez-vous sur le ruban « Données » puis « À partir du web » et coller l’adresse complète du fichier CSV : https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_confirmed_global.csv

Importation CSV Donnees COVID-19 Web
Importation CSV Données COVID-19 Web

Étape n°1 (ter) : Se connecter au contenu web

Une étape supplémentaire pour l’option connexion au contenu web directement, l’authentification. Dans le cas présent, rien à configurer, l’accès est libre et anonyme (au sens d’authentification).

Ici, vous n’avez qu’à vérifier que c’est bien « Anonyme » qui est sélectionné à gauche puis à cliquer sur « Se connecter ».

Importation CSV Donnees COVID-19 Web - Se connecter
Importation CSV Données COVID-19 Web – Se connecter

Étape n°2 : Transformer les données dans Power Query

Cette fois vous y êtes presque ! Après cette étape, vous vous trouverez dans l’interface de Power Query et pourrez commencer à apprendre à l’utiliser !

Ici, vous allez cliquer sur « Transformer les données » (ceci est valable dans les deux scénarios : import depuis un fichier CSV ou à partir du web) :

Importation CSV Donnees COVID-19 Web - Transformer les donnees
Importation CSV Données COVID-19 Web – Transformer les données

Étape n°3 : Découvrir l’interface de Power Query

Pour commencer à apprendre Power Query, il vous sera nécessaire de comprendre la nouvelle interface qui se présente à vous.

Bien évidemment, tous les boutons et menus ont leur utilité, toutefois, il faut bien commencer par quelque part.

Bienvenue dans la cuisine des données ! L’analogie culinaire est intéressante à plusieurs titres :

  1. Les requêtes (1) : C’est l’équivalent du menu, c’est ce que vous pourrez consommer depuis Excel. A ce stade, vous n’avez qu’une seule requête, c’est celle de l’import du fichier CSV. Vous pourrez toutefois la renommer pour lui donner un nom compréhensible.
  2. Les opérations sur les colonnes et les lignes (2) ou les opérations de transformation (3) : Ce sont vos ustensiles de cuisine pour préparer vos données.
  3. Les étapes appliquées (4) : C’est votre recette. Elles seront appliquées à chaque appel de rafraîchissement des données.
Apprendre Power Query - Decouvrir interface
Apprendre Power Query – Découvrir l’interface

Étape n°4 : Avoir une idée de la transformation que vous souhaitez réaliser

Pour continuer l’analogie avec la cuisine, vous ne commencez pas à préparer vos ingrédients sans savoir quel plat vous préparez ! Ici, c’est la même chose.

Pour réussir votre premier tableau croisé dynamique, je vous avais conseillé de dessiner votre tableau de synthèse avant de manipuler Excel. Je vais ici réitérer ce conseil.

Ayez une idée de ce que vous recherchez avant de manipuler Power Query.

Dans le cas présent, l’Université Johns Hopkins place une nouvelle colonne pour chaque jour. En général, les relevés de données se présentent en ligne. D’ailleurs, Excel possède plus de lignes que de colonnes pour cette même raison.

Je vous propose donc un premier scénario d’analyse : pivoter les données de l’Université.

À ce stade, les explications à base de captures d’écran devenant trop compliquées, j’ai réaliser un enregistrement vidéo explicatif.

Et après ?

Power Query est un tremplin vers Power BI (regardez comment importer l’historique des actions du CAC 40 à l’aide d’Excel et de Power BI). Vous aurez exactement la même interface et les mêmes fonctionnalités. Car une fois que vous aurez appris à utiliser le langage M (et aussi un peu de DAX), vous pourrez faire le saut de l’exploration des données.

Tel un tremplin à plusieurs étages, vous accéderez d’Excel à Power Query puis à Power BI.

Est-ce que cette introduction et cet exemple vous ont intéressé ? Faites-le moi savoir dans les commentaires de cet article, dans les commentaires de la vidéo YouTube ou simplement par mail via la page contact !

En attendant, si vous êtes intéressé par une formation sur Power Query, je vous propose de vous pré-inscrire sur ma page formation.

Apprendre le VBA Excel : Summum de la maîtrise d’Excel ?

Apprendre le VBA Excel : Summum de la maîtrise d’Excel ?

Par où commencer pour apprendre le VBA Excel ? Doit-on apprendre le VBA d’Excel ? Que vais-je savoir faire avec cet apprentissage ? Comment choisir la meilleure formation pour apprendre le VBA d’Excel ? Peut-on faire autrement qu’apprendre le VBA Excel ?

Apprendre le VBA d’Excel vous semble être le summum de l’utilisation d’Excel et vous vous posez de nombreuses questions sur les moyens de parvenir à ce sommet.

À travers cet article, je vous propose de répondre à toutes les questions, et d’autres encore, que votre désir d’apprendre le VBA avec Excel a fait émerger.

Qu’est-ce que le VBA ?

Le terme signifie Visual Basic for Application. C’est un langage de programmation extrait de son parent : le Visual Basic.

Apprendre le VBA Excel - Logo VBA
Logo VBA

Un peu d’histoire pour commencer

Le Visual Basic (VB pour les intimes) est un langage de programmation qui a été inventé par Microsoft pour faciliter la création de programmes pour Windows. Ce qui peut paraître naturel et évident de nos jours, ne l’était pas dans les débuts de l’Ordinateur Personnel (mieux connu sous le nom anglais Personnal Comupter = PC).

Les besoins étaient pourtant déjà les mêmes : pouvoir échanger des informations entre les applications, intégrer le meilleur de chaque logiciel dans un autre (le traitement d’une image au sein d’un tableur par exemple).

C’est ici que les fameux acronymes que nous avons souvent entendus sont apparus :

  • OLE : Object Linking and Embedding ou en bon français « Liens et inclusion des objets » – Permet d’intégrer des morceaux d’une application au sein d’une autre.
  • COM : Component Object Model soit « Modèle objet des composants » – Décrit les objets utilisables ainsi que les modalités d’utilisation.
  • DCOM : Distributed COM ou encore « Modèle objet des composants distribués » – Identique à COM mais à travers un réseau informatique.

Tout ceci est-il véritablement utile pour apprendre le VBA avec Excel ? Personnellement, je le pense. À moins que vous ne soyez un génie qui réussit tout du premier coup, vous verrez des messages d’erreurs apparaître lors de vos débuts. Et je trouve que rien n’est plus frustrant que de ne pas savoir à quoi cela fait référence.

VBA - Erreur OLE
VBA – Erreur OLE

Et le VBA Excel alors ? Pourquoi ce « A » en plus ?

VBA ou VB, les deux permettent de programmer les mêmes choses. Ce n’est pas au niveau des possibilités que se situent les différences. Il faut aller les chercher ailleurs. Il y en a 4.

Le contexte d’exécution

Le VBA est du code qui nécessite une application telle qu’Excel, Word ou PowerPoint (ou d’autres) pour s’exécuter. Le code VB permet de créer des programmes qui se lance directement à partir de Windows.

La compatibilité de votre VBA Excel entre Windows et Mac

Eh oui ! C’est une chose qui peut surprendre, mais il possible d’exécuter du code écrit en VBA depuis Windows sur un Mac ! Comme le contexte d’exécution est Excel, Microsoft se charge de faire les adaptations nécessaires avec le système sous-jacent.

En revanche, votre programme en VB qui a été compilé pour un environnement Windows ne pourra pas s’exécuter sur un Mac.

La licence d’utilisation

Créer des applications pour Windows n’est pas gratuit ! Il faut payer pour disposer de l’environnement de développement, Visual Studio pour le citer. (Même s’il faut l’avouer, ces derniers temps le modèle économique de Microsoft évolue vers un modèle plus ouvert pour le développement des applications.)

Pour le VBA Excel, vous avez déjà payé car vous possédez déjà une licence d’Excel.

La performance du code

Le code VBA Excel s’exécute au sein d’Excel. Par principe, il reste moins de ressources utilisables car Excel est en cours d’exécution. De plus, lorsqu’on développe une application, on fait ce qu’on nomme une compilation du code.

Cette dernière action traduit le code écrit dans un langage machine, l’Intermediate Language (IL, pour Microsoft, il s’appelle le MSIL). Ainsi, en étant compilé, un programme développé en VB sera bien plus performant que le même code en VBA.

Ce tableau extrait de cet article de blog montre des différences de vitesse de traitement :

Execution Time (Seconds)
Excel VBA114
Visual Studio 2010 (w/o Parallel Programming)58
Visual Studio 2010 (with Parallel Programming)23

Par où commencer pour apprendre le VBA Excel ?

Point de départ

Depuis le début de cet article, vous n’avez pas encore commencé à apprendre le VBA Excel… En êtes-vous certain ?

Vous savez déjà que :

  1. Il n’y a pas de limitation en termes de capacité de programmation par rapport à un langage comme le VB.
  2. Le code sera moins performant qu’une application développée à part entière.
  3. Votre VBA sera compatible entre Windows et Mac (si vous restez dans les objets Excel).
  4. Et vous savez surtout que vous avez affaire à un langage de programmation !

Apprendre le VBA Excel : En voiture s’il vous plaît !

Je commencerai par mon dernier point ci-dessus. Apprendre le VBA Excel, c’est avant tout apprendre à faire de la programmation comme un développeur en informatique !

Avec tout le respect que je dois à tous mes confrères de la branche, devenir développeur nécessite un véritable investissement pour se former.

Une langue

Comme tous les métiers, celui-ci possède sa propre langue. String, char, bouton radio ne veulent pas dire la même chose dans ce monde… 🙂

Prévoyez ainsi d’apprendre à parler DEUX langues ! Celle du langage de programmation à proprement parler (le VBA) ET celle des développeurs.

Une manière de penser

Le VBA est un langage orienté objet (c’est une langue… rappelez-vous). Là où vous verrez les données de votre métier, un développeur verra un objet avec des attributs. Il va s’abstraire de ce qu’il voit pour le transformer en objets qui interagissent.

Afin de commencer à programmer, vous devrez vous aussi passer par cette étape d’abstraction de votre problème.

Et cela va sans dire, mais c’est mieux en le disant, il faut apprendre la syntaxe du VBA, comment créer des procédures, des fonctions, des objets, des variables, etc.

Une manière de voir le monde

Le monde vu par un développeur n’est qu’un monde dans lequel il y a des entrées et des sorties.

Voir le monde autrement
Voir le monde autrement

Avec la même entrée, vous aurez la même sortie.

Le programme est une usine que vous alimentez avec des données qui va réaliser une suite d’actions automatiquement, toujours dans le même ordre, sans se poser de questions.

Le programme ne connaît que ce qu’on lui donne. Le développeur doit donc s’assurer que le VBA qu’il est en train d’écrire possède toutes les informations nécessaires pour fonctionner dans toutes les situations !

Vu le nombre de correctifs que nous avons à appliquer sur tous nos équipements, tout n’a pas été prévu et construit comme ça…!

D’ailleurs, cela devrait vous interpeler à ce stade que tant de professionnels formés à programmer écrivent tant de programmes avec tant de bugs…

Apprendre le VBA Excel : Et après ?

Maintenant que vous savez comment penser votre programme, il faut mettre en pratique ces connaissances.

Une fois qu’on sait programmer et que l’on connaît un langage, on peut commencer ?

A vrai dire… pas tout à fait.

L’API Culture…

Qu’est-ce que cela encore ?

Rappelez-vous, vous allez apprendre le VBA Excel, pas celui de Word ou PowerPoint. Il est clair qu’un objet cellule d’un tableau Excel n’a pas les mêmes attributs qu’une cellule d’un tableau sur Word…

L’API c’est l’Application Programming Interface (ou Interface de programmation de l’application).

Apprendre-le-VBA-Excel-API

Même si Microsoft est en train de s’ouvrir au monde open source, il ne va pas tout de suite vous permettre d’accéder au code source d’Excel.

A la place, il a mis en place un ensemble de fonctions et objets accessibles par nous. Cet ensemble, c’est l’API.

Une API est structurée et liste tous les objets et fonctions que vous pouvez utiliser.

Ainsi, comme vous comptez manipuler vos feuilles, cellules et autres informations contenues dans votre classeur Excel, vous aurez à apprendre à vous en servir.

Microsoft vous fournit d’ailleurs une documentation complète sur son API d’Excel.

Mais je veux manipuler des objets qui sont hors d’Excel…

Ah… il fallait bien que cela arrive…

Vous souhaitez en plus écrire des fichiers (vous interagissez avec l’API de Windows). Si en plus vous pouviez automatiquement intégrer les données de votre application de compta dans votre classeur…

Aucun problème.

C’est autant d’API que vous aurez à utiliser !

Pour l’exemple, voici l’API de SAP.

Ci-gît la perte de la compatibilité avec l’environnement Mac.

Charles Exceller

Tant que vous étiez dans le cocon d’Excel à n’utiliser que les objets internes, pas de problèmes. Dès que vous sortez de votre bac à sable, vous ne pouvez plus être compatible avec tout le monde.

Créer un fichier sur Windows s’écrit d’une manière, le même sur un Mac se fait différemment.

En plus de manipuler les données, je veux aussi interagir avec l’utilisateur. Comment faire ?

Autre point de passage quand on veut apprendre le VBA Excel : les formulaires, les menus, les listes, etc.

Sur le principe décrit jusqu’ici, c’est totalement en accord. Toutefois, vous aurez une dimension supplémentaire à considérer : les interactions avec l’utilisateur. En termes techniques, on parle d’UI ou de GUI (User Interface ou Graphical User Interface – on parle aussi d’IHM, Interface Homme-Machine).

D’un point de vue programmation, c’est la même chose.

Toutefois, les considérations suivantes s’ajoutent :

  • Les événements
  • La modalité
  • La notion « synchrone / asynchrone« 

Les événements sont toutes ces interactions que vous aurez avec les objets présentés à l’écran et qui enverront des signaux à votre code. Ce sera à vous de déterminer ceux sur lesquels vous ferez ou non quelque chose.

Par exemple, le clic sur un bouton ou le simple passage de la souris sur le bouton sont deux événements que le système va déclencher. Ce sera à vous de déterminer si vous souhaitez réagir ou non à celui-ci.

Et chaque objet de votre interface graphique peut (et parfois pas) réagir à certains événements.

Apprendre-le-VBA-Excel-Evenements-Bouton
Évènements disponibles pour un bouton

La modalité déterminera si une fenêtre qui s’ouvre au premier plan conserve « la main » tant qu’elle est ouverte ou bien si vous pouvez continuer à interagir avec les autres fenêtres ouvertes.

Enfin, la notion de synchronicité est essentielle à comprendre et maîtriser pour gérer les délais d’attentes lors d’un appel à une API externe par exemple (et si votre application comptable n’était plus joignable au moment précis où vous l’appeler, votre écran va probablement « freezer »).

Que vais-je savoir faire avec cet apprentissage du VBA Excel ?

Arrivé jusqu’ici, vous avez déjà appris beaucoup de choses.

Vous êtes presque devenu un développeur. Le presque, c’est par respect pour ceux qui mettent plusieurs années d’études et d’années d’expérience pour se dire développeur.

Vous DEVREZ devenir un développeur pour bien faire les choses. Sinon, des erreurs, des dysfonctionnements, des erreurs inattendues seront sur votre route en permanence.

N’a-t-on pas juste oublié une chose ? Nous sommes partis d’Excel et vous devenez développeur…

Pourtant, le terme est très clair sur Excel, le ruban qui vous donne accès au VBA s’appelle « Développeur ».

Avez-vous pris la bonne voie ? Fallait-il apprendre le VBA Excel ?

Apprendre-le-VBA-Excel-Question
Véritable question à se poser !

Tout ça pour ça ?! Tout cet article pour laisser entendre qu’il ne fallait pas le faire ?

Je crois que c’était nécessaire oui. Car oui, je vous le dis, n’apprenez pas le VBA ! Vous faites partie du passé avec cette technologie.

C’est un peu le problème avec les applications qui ont une histoire. Et quelle histoire, Excel 1.0 est sorti en 1985. Cela fait donc 35 ans ! Et en informatique, avoir 35 ans, c’est faire partie de la préhistoire…

Une génération entière a appris Excel à cette époque. La deuxième génération (à partir de 2005), a commencé avec la version Excel 2007 qui a profondément changé le format interne des fichiers. Les fameux .xlsx ont plus de 13 ans, et pourtant les dangereux fichiers Excel au format .xls sont partout !

À la première génération, j’aurais dit de faire avec ce qui était à la pointe du moment, mais aujourd’hui, VBA n’est plus à la pointe !

Quelle est la bonne manière de faire alors ? Que dois-je apprendre si je NE DOIS PAS apprendre le VBA Excel ?

Tout d’abord, pour les développements un peu plus sérieux, Microsoft vous permet de passer à un langage un peu plus sérieux : Le .Net.

Attention, là, vous devenez véritablement un informaticien. Vous quittez presque le bateau Excel pour changer de quai.

Alors que le VBA est un environnement de développement embarqué dans Excel, là, vous partez d’un environnement de développement pour aller vers Excel. Il s’agit de VSTA : Visual Studio Tools for Application.

Certains s’y sont mis tout de suite à la sortie (j’imagine avec un peu de sponsoring de la part de Microsoft… 🙂 ), et pas des moindres. En effet, il s’agit de Dassault Systems qui adopte VSTA en 2005 !!!

Cette voie n’est pas pour tout le monde

Je ne crois pas que ceux qui veulent véritablement progresser dans leur utilisation d’Excel doivent aller vers un langage de développement. Le développement est un métier différent.

Il est évident que, comme tous les logiciels au monde, Excel fournit des fonctionnalités et que vous finirez par en trouver une qui vous manque.

Si vous êtes déjà un développeur et que vous devez fournir une application qui s’intègre avec Excel, alors oui, allez vers VSTA !

Les erreurs les plus communément commises avec le VBA

Une fois que le chemin d’apprendre à coder et à développer avec VBA ou VSTA est parcouru, on a oublié plusieurs choses en route.

  1. Est-ce qu’Excel ne possède pas une fonction pour mon besoin ?
  2. Est-ce qu’on ne pourrait pas écrire une formule Excel pour mon besoin ?
  3. Pourquoi ai-je choisi Excel si je dois faire des développements ? Allez relire mes conseils pour bien démarrer votre apprentissage d’Excel, en particulier, en apprenant ce qu’Excel n’est pas !
  4. Programmer des boucles itératives alors qu’Excel sait faire du calcul matriciel
  5. Ne pas avoir un assez bon niveau en Mathématiques
  6. Ne pas connaître Power Query et son grand cousin Power BI
  7. Négliger l’option Access !

Malheureusement pour vous, les francs-tireurs du code à tout va foisonnent sur les forums Excel sur Internet. Et cela donne l’impression que pour être bon avec Excel, il faut apprendre le VBA Excel…

Ils sont généreux de prendre du temps à aider les autres. Mais les aident-ils véritablement ?

Ouvrez-vous aux autres options que le développement VBA

Option 1 : Découvrez Power Query

Depuis 2013, Excel intègre un nouveau moteur : Power Query. Power Query est un ETL : Extract Transform Load.

Ce sont habituellement de gros systèmes dans les entreprises qui permettent de transférer les données d’un système vers un autre. Par exemple, de votre système de paie vers le système de paiement bancaire.

Intégrer des données externes à Excel

Cette technologie est faite pour manipuler de grandes masses de données. D’abord à l’aide du « E : Extract » de ETL, Power Query permet d’intégrer des données externes à Excel.

Apprendre le VBA Excel Decouvrir Power Query
Découvrir Power Query

En regardant de plus près, cela facilite toute la couche de développement d’intégration de données de systèmes tiers.

Adapter et compléter les données

Nous arrivons au « T : Transform » d’ETL.

Lorsque vous avez sélectionné votre source, vous allez pouvoir ouvrir le nouveau capot, Power Query Editor.

Apprendre le VBA Excel Découvrir Power Query Transformer les données
Apprendre le VBA Excel – Découvrir Power Query – Transformer les données

Et à partir de là, un autre monde de traitement des données s’offre à vous. Il dispose également de plusieurs niveaux de langage à apprendre :

Ces langages vont permettre de manipuler vos données en masse et de manière performante.

Utiliser les données préparées dans Excel

Une fois terminée, vous pourrez alors fermer et charger vos données dans Excel sous la forme d’une ou de plusieurs tables.

Il devient désormais possible d’ouvrir Excel, le connecter à des systèmes externes pour importer des données, les modifier à souhait pour les mettre dans un format qui vous convient sans écrire une seule ligne de code !

Votre fichier restera un fichier sans macro et avec l’extension .xlsx (pourquoi je tiens à ce point à cette extension).

Option 2 : Apprenez les fonctions d’Excel

Lorsque Microsoft met à jour Excel, on pense généralement que c’est juste pour vendre une nouvelle licence, que rien ne change vraiment.

Maintenant, je vous mets au défi de programmer la fonction UNIQUE() présente dans la dernière version d’Excel 365 avec la même performance.

Ensuite, découvrez les tableaux dynamiques (rien à voir avec les tableaux croisés dynamiques).

Et si vous voulez réellement progresser dans votre utilisation d’Excel, apprenez à manier les formules matricielles.

Vous pouvez également aller télécharger mon livre PDF qui vous récapitule les 12 fonctions Excel à maîtriser absolument.

Lorsque vous aurez fait véritablement le tour de toutes ces possibilités, alors vous pourrez vous poser la question de développer… quoique… Vous êtes-vous posé la question d’utiliser Access ?

Option 3 : (Re-)Découvrez Access

Finalement, la véritable plateforme que Microsoft vous a mise à disposition pour créer des applications, des formulaires, des règles de saisies, etc. c’est Access.

Apprendre le VBA Excel - Option 3 Access
Option 3 Access

C’est écrit juste ici : Suivi des biens, Base de données de gestion de projets, Gestion des tâches.

Hélas, combien de fois trouverez-vous des demandes d’assistance sur les forums pour recréer ce type d’application de gestion ?

N’apprenez pas le VBA, apprenez Access ! Tout y est :

  1. Formulaires de saisie
  2. Contraintes d’unicité d’un identifiant
  3. Incrément automatique d’un identifiant
  4. Rapports récapitulatifs (on appelle cela un « état » dans Access)

Pourquoi vouloir tout réinventer ? C’est entre vos mains. Au lieu de vous démener à apprendre le VBA Excel, apprenez Access et vous deviendrez un génie qui résout tous les problèmes !

Option 4 : Découvrez ScriptLab et la gestion d’Excel avec du JavaScript

Le JavaScript (JS) est un langage de programmation initialement conçu pour le web et les navigateurs web. Toutefois, sans entrer dans les considérations techniques, il est désormais employé à peu près partout. Excel ne fait pas exception.

Le véritable langage moderne est aujourd’hui JavaScript.

Comment passer à JS sur Excel ?

Première étape : Installer ScriptLab depuis l’AppSource

Apprendre le VBA Excel - Découvrir ScriptLab
Découvrir ScriptLab

Un nouveau ruban fait alors son apparition, c’est « ScriptLab ».

Deuxième étape : Commencer par utiliser les exemples de scripts fournis

Afin de vous aider à apprendre ScriptLab, vous trouverez de nombreux exemples embarqués dans le composant.

Pour y accéder, vous aurez à suivre les étapes suivantes :

Apprendre le VBA Excel - Premiers pas avec ScriptLab
Premiers pas avec ScriptLab

Au passage, pensez à agrandir le panneau qui s’ouvre sur la droite pour avoir une meilleur lisibilité du contenu.

Troisième étape : Suivez le guide de Microsoft

Je ne vais pas réécrire ici la prise en main déjà documentée par Microsoft. Aussi, je vous invite à consulter la documentation fournie sur le site de l’éditeur.

Mon conseil pour apprendre le VBA Excel

Depuis plusieurs années, Microsoft enrichit de manière très importantes les capacités d’Excel. En particulier sa capacité à intégrer des données provenant de systèmes externes.

Pourquoi toutes ces possibilités ? Mon avis est qu’il souhaite détourner les utilisateurs du VBA pour utiliser des fonctionnalités intégrées.

Pourquoi détourner du VBA ? Parce que la programmation est une source majeure de risques pour la sécurité de votre poste de travail et de tout ce qui est connecté au même réseau.

Pour faire une analogie avec le monde qui nous entoure, utiliser les fonctionnalités c’est conduire avec un code de la route. Faire du VBA, c’est conduire sans code de la route. Tout peut très bien se passer, mais les pirates ont bien compris qu’un monde sans contraintes était disponible et ils l’exploitent. (ici, ici ou encore ici)

Apprendre le VBA Excel pour se rendre compte que les fichiers avec macros seront bloqués en envoi et en réception dans quelques temps serait bien dommage, non ? Microsoft lui-même le restreint fortement par défaut dans son environnement Microsoft 365.

Finalement, si vous souhaitez dépasser les limites qu’Excel vous pose, apprenez à développer une application à partir de zéro !

Mettez en place un environnement de développement avec sa base de données et toutes les fonctionnalités dont vous avez besoin. Vous pourrez décider de faire une app pour votre smartphone ou une application web, mais au moins ce sera clair !

Et après ?

Vous souhaitez en savoir plus sur les fonctions Excel ? Restez informé sur l’ouverture des sessions de formations.

Vous avez un problème à résoudre et ne savez pas comment l’aborder ? Contactez-moi via la page de contact.

Vous avec un avis sur cet article ? Utilisez la partie des commentaires et parlons-en !

Additionner des heures avec Excel – Tout comprendre, tout maîtriser !

Additionner des heures avec Excel – Tout comprendre, tout maîtriser !

Comme mentionné dans mon article expliquant comment calculer un âge avec Excel, la gestion du temps sur cette application peut sembler peu compréhensible. Or, une fois qu’on SAIT, additionner et soustraire des heures ou des minutes avec Excel devient un jeu d’enfants !

Excel ne sait pas additionner les heures !

Si vous êtes ici, c’est que vous en avez vous-même fait l’expérience, et dans le doute, faites le test suivant :

  1. Saisissez 12:00 dans une cellule
  2. Saisissez 12:00 dans une autre cellule
  3. Réalisez l’addition des deux cellules dans une troisième
  4. Observez le résultat : 00:00 !
Additionner heures Excel - Resultat étrange
Additionner heures Excel – Résultat étrange

Premier pas pour résoudre cette étrangeté : je vous invite à changer le format de présentation des données de votre cellule.

  1. Dans le ruban Accueil, bloc Nombre, sélectionnez le menu déroulant « Autres formats numériques… »
  2. Sur l’onglet « Nombre », sélectionnez « Personnalisée »
  3. Dans la zone de saisie « Type » saisissez [h]:mm
Additionner heures Excel - Changer format cellule
Additionner heures Excel – Changer format cellule – Étape 1
Changer format cellule
Changer format cellule – Étape 2
Changer format cellule
Changer format cellule – Étape 3

Avec cette configuration, on retrouve le résultat attendu soit : 24:00.

Sur le principe, on pourrait s’arrêter là, mais je ne trouve pas cela très convaincant… Et vous ?

Comment Excel gère les heures ? Comprendre pour additionner, soustraire des heures, des minutes à souhait !

Je vous l’ai déjà dit et j’insiste, Excel ne comprend pas les heures ! Tout est nombre ou texte pour Excel. Le reste n’est que mise en forme pour vos yeux.

D’ailleurs, vous allez découvrir que le 0 janvier 1900 existe chez Microsoft !

Je vous propose de commencer par un petit exercice pratique.

Comprendre et apprendre en pratiquant – Ajouter des heures sur Excel

Préparer votre classeur

  1. Ouvrez un nouveau classeur vide
  2. Saisissez dans la cellule de votre choix (D2 dans mon cas) une heure, 13:22 par exemple
  3. Dans la cellule de droite, saisissez =votre_référence (=D2 dans mon cas)
  4. Dans la cellule encore à droite, répétez la même chose avec la même référence (=D2 dans mon cas)
  5. Sélectionnez ces trois cellules et étendez-les sur trois cellules vers le bas (cf. Le curseur Excel : Sous toutes ses formes et fonctions)

Vous devriez vous retrouver avec cet affichage :

Additionner heures Excel - Cas pratique
Additionner heures Excel – Cas pratique

Ensuite, dans la cellule sous chaque colonne, calculons la somme des lignes du dessus.

=SOMME(D2:D5) pour la première colonne

Il ne vous reste qu’à étendre cette formule sur la droite pour que celle-ci s’adapte aux colonnes d’à côté. Regardez cette partie de l’article pour apprendre à étendre une fonction avec le curseur.

Étudier les données

Je vous propose maintenant de changer le format des données de la 2e colonne (Colonne E pour moi) ainsi que de celui de la 3e colonne (Colonne F pour moi).

Additionner heures Excel - Changer format cellule
Additionner heures Excel – Changer format cellule

Vous vous souvenez, je vous ai proposé de changer le format de la cellule au début de cet article. Vous allez faire la même opération, mais cette fois, voici le format que vous allez saisir pour la 2e colonne :

jj.mm.aaaa hh:mm:ss

Et pour la dernière colonne, mettez simplement « Standard » (c’est le premier de la liste).

Vous devriez avoir l’affichage suivant :

Additionner heures Excel - Déchiffrer les formats
Additionner heures Excel – Déchiffrer les formats

Avez-vous remarqué la dernière ligne, celle des totaux ?

11:2802.01.1900 11:28:002,47777778
Ligne de la somme des valeurs

La même donnée sous trois formes différentes !

En effet, Excel ne fait que présenter les données sous forme de date ou d’heure. Lorsque vous avez saisi votre première valeur 13:22, vous n’avez pas renseigné de date particulière, celle-ci a été fixée automatiquement au « 0 janvier 1900 ».

Vous avez bien lu : Le jour zéro existe pour Excel et c’est le 0 janvier 1900 !

Comprendre pour maîtriser la manipulation des heures, des minutes ou des dates avec Excel

Le véritable zéro a été inventé par les indiens vers le IIIe siècle après J.-C.

https://www.lumni.fr/article/mathematiques-histoire-du-zero

Ici, Microsoft a fait le choix technique d’implémenter les dates de cette manière. Je ne me permettrai pas de commentaires tant ce choix a des impacts que je ne peux évaluer.

Une date n’est finalement qu’une apparence visuelle. Au passage, je vous informe que dans le monde Excel, les dates, en plus d’avoir un début, ont une fin !

Eh oui, la fin du monde datée se situe au 31 décembre 9999 à 23:59:59.

Maintenant, que faut-il savoir pour VOUS et MAINTENANT pour vos opérations avec les heures ?

Même si vous ne saisissez qu’une donnée sous la forme d’une heure, Excel va considérer cette information comme étant une date. Et, en plus, Excel traitera cette information comme étant une valeur numérique.

Les nombres entiers correspondent aux jours.

Une heure vaut numériquement 1 divisé par 24.

Une minute vaut 1 divisé par 24 divisé par 60. Pour la seconde, il faudra diviser encore par 60 soit 1 divisé par (24*60*60).

Lorsque vous faites n’importe quelle opération (additionner des heures, soustraire des minutes, etc.), Excel réalise l’opération sur les valeurs numériques puis convertit le résultat en la date correspondante.

Comme Excel va respecter au maximum le format des cellules qui composent votre opération, vous aurez des résultats comme l’exemple du début :

Additionner heures Excel - Comprendre l'affichage
Additionner heures Excel – Comprendre l’affichage

La somme des différentes heures donne le résultat de 11:28 ! Vous l’avez peut-être déjà repéré… Lorsque vous adaptez le format de la cellule du total au format jj.mm.aaaa hh:mm:ss vous obtenez : 02.01.1900 11:32.

C’est à dire, 2×24 + 11:32 = 59:32.

Pour obtenir cet affichage, il faut savoir gérer les options de mise en forme des cellules. Le meilleur format pour le cas présent est [h]:mm.

Quelques dernières précautions pour vos additions ou soustraction d’heures avec Excel

Vous y êtes presque, vous avez déjà fait un grand pas dans votre connaissance d’Excel. Maintenant, c’est la dernière ligne droite, c’est à partir d’ICI que vous allez plus loin. Restez bien jusqu’au bout !

Gérer les erreurs d’affichage

Excel va tant qu’il peut essayer d’afficher les données dans le format de votre choix. Or, si les données ne sont pas affichables, Excel remplira la cellule avec des #.

Cela ne veut pas dire que le calcul ne sera pas fait, ni même que cette cellule ne peut pas être utilisée comme référence pour un autre calcul…

Voyons cela à l’aide d’une illustration !

Repartons de notre heure de 13:22 en D2.

Si en C2, vous saisissez =D2-1, savez-vous quelle opération vous venez de faire ?

Vous ne venez pas de soustraire une heure à 13:22, mais un jour ! Soit 24h !

Ainsi, arrive ce qui devait arriver :

Soustraire heures Excel - Erreur
Soustraire heures Excel – Erreur

13:22 est interprété par Excel comme étant le 0 janvier 1900, et maintenant vous essayer de soustraire un jour à cette date ! Excel ne sait pas afficher cette information sous forme de date… Il faudrait normalement soustraire (1/24) pour que cela représente une heure.

Cependant, Excel a en mémoire la valeur numérique de 13:22 (allez voir en F2 de votre tableau) et peut heureusement encore soustraire 1 à cette valeur.

En B2, vous pouvez maintenant saisir =C2+2. Arrivez-vous à deviner le résultat affiché ?

Cellule en erreur affichage - Valeur prise en compte
Cellule en erreur affichage – Valeur prise en compte

Malgré l’erreur d’affichage, Excel a bien la valeur en mémoire. En ajoutant 2, vous revenez dans une plage de valeur qu’Excel sait afficher sous forme de date. Et comme vous n’avez pas affiché le jour, chaque unité que vous ajouterez fera avancer le jour, mais pas l’heure !

Astuce pour réaliser sereinement toutes vos opérations mathématiques avec les heures

Êtes-vous condamné à ajouter ou soustraire des multiples de 1/24 dans tous vos calculs ? Eh bien non ! Vous pouvez tout simplement utiliser l’écriture AVEC LES GUILLEMETS « 1:00 » dans vos calculs.

= « 1:00 » * 5

Faites l’essai par vous-même !

Excel comprend alors que vous manipulez des heures et adaptera la valeur numérique correspondante !

Opérations heures Excel - Astuce
Opérations heures Excel – Astuce

Il ne vous restera qu’à adapter le format d’affichage de la cellule en heure ou date selon votre préférence.

Évidemment, cela fonctionne également pour les additions ou les soustractions directement dans les formules.

Et les minutes dans tout cela ?

Maintenant que vous connaissez l’astuce pour ne jamais vous tromper dans les opérations avec les heures, sachez que vous pouvez tout à fait l’étendre pour les minutes ou les secondes !

Dans vos calculs, vous pouvez maintenant saisir = »00:30:15″ * 8 et obtenir exactement le résultat escompté ! Le seul point d’attention à conserver en tête est le format d’affichage de votre cellule, soit « Heure », soit « Personnalisé » avec le type [h]:mm:ss.

Sinon, il faut saisir 1/(24*60) dans toutes vos formules, et pour les secondes 1/(24*60*60). C’est plus lourd et plus compliqué que « 00:01:00 » ou « 00:00:01 ».

Pour aller plus loin, avec les dates cette fois-ci !

Excel est plein de secrets, l’un d’entre eux est cette fonction cachée, oui CACHÉE, qui se nomme DATEDIF(). Cette fonction permet de calculer la différence entre deux dates en nombre de jours, mois ou années.

Le plus simple est d’aller voir par vous-même sur comment calculer un âge avec Excel, vous en apprendrez beaucoup sur la gestion des dates sur Excel.

Vous reste-t-il des questions pour additionner ou soustraire des heures avec Excel ? Avez-vous apprécié cet article ?

Dans tous les cas, utilisez la partie commentaire de cet article pour tous vos échanges avec moi. Votre question et ma réponse pourront servir les autres visiteurs !

Et si vous avez apprécié cet article, alors c’est moi que cela servira de savoir qu’il vous a plu !

Je compte sur vous !

>