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() :
Vous devez disposer d’un tableau « de référence » (vos articles, vos fournisseurs, etc.).
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.
Votre identifiant est positionné dans les colonnes PRÉCÉDANT les données détaillées.
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 article
Description
1
Chaise
2
Table
3
Bureau
Table de référence
Tableau 2
N°Facture
Code article
Description
1
1
1
2
2
2
3
3
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
Description
Code article
Chaise
1
Table
2
Bureau
3
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 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 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 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 !
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 – 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.
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 :
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.
À 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 é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
Ensuite, vous aurez à donner un nom à votre fonction :
Fonction LAMBDA – Nommer sa fonction
Ici, chacun des points de l’illustration a son importance.
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 .
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.
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
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
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 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
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 » ?
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és
Valeurs
Relevé 1
1
Relevé 2
43
Relevé 3
5
Relevé 4
65
Relevé 5
76
Relevé 6
678
Relevé 7
789
Relevé 8
43
Maintenant, imaginez que vous souhaitiez ajouter une colonne présentant la somme cumulée des valeurs des relevés précédents.
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 :
L’identifiant du relevé actuel
La valeur du relevé actuel
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ètre
Nom de la variable
Identifiant du relevé actuel
r
Valeur du relevé actuel
v
Valeur de l’en-tête
arret
J’ai choisi ensuite de nommer ma fonction « SommeCumulee ».
L’idée maintenant est de procéder comme suit :
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é.
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.
C’est tout !
Cela donne la fonction LAMBDA suivante (j’ai ajouté des espaces et sauts de lignes pour plus de lisibilité) :
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 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 :
Si vous ne fixez pas le 3e paramètre, il sera adapté par Excel à chaque ligne.
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 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 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.
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
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 VBA
114
Visual Studio 2010 (w/o Parallel Programming)
58
Visual Studio 2010 (with Parallel Programming)
23
Par où commencer pour apprendre le VBA Excel ?
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 :
Il n’y a pas de limitation en termes de capacité de programmation par rapport à un langage comme le VB.
Le code sera moins performant qu’une application développée à part entière.
Votre VBA sera compatible entre Windows et Mac (si vous restez dans les objets Excel).
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
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).
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.
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…
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 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.
É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 ?
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.
Est-ce qu’Excel ne possède pas une fonction pour mon besoin ?
Est-ce qu’on ne pourrait pas écrire une formule Excel pour mon besoin ?
Programmer des boucles itératives alors qu’Excel sait faire du calcul matriciel
Ne pas avoir un assez bon niveau en Mathématiques
Ne pas connaître Power Query et son grand cousin Power BI
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.
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
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 !
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.
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 :
Formulaires de saisie
Contraintes d’unicité d’un identifiant
Incrément automatique d’un identifiant
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
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 :
Premiers pas avec ScriptLab
Au passage, pensez à agrandir le panneau qui s’ouvre sur la droite pour avoir une meilleur lisibilité du contenu.
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 !
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 :
Saisissez 12:00 dans une cellule
Saisissez 12:00 dans une autre cellule
Réalisez l’addition des deux cellules dans une troisième
Observez le résultat : 00:00 !
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.
Dans le ruban Accueil, bloc Nombre, sélectionnez le menu déroulant « Autres formats numériques… »
Sur l’onglet « Nombre », sélectionnez « Personnalisée »
Dans la zone de saisie « Type » saisissez [h]:mm
Additionner heures Excel – Changer format cellule – Étape 1
Changer format cellule – Étape 2
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
Ouvrez un nouveau classeur vide
Saisissez dans la cellule de votre choix (D2 dans mon cas) une heure, 13:22 par exemple
Dans la cellule de droite, saisissez =votre_référence (=D2 dans mon cas)
Dans la cellule encore à droite, répétez la même chose avec la même référence (=D2 dans mon cas)
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
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
Avez-vous remarqué la dernière ligne, celle des totaux ?
11:28
02.01.1900 11:28:00
2,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.
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
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
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
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
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 !
Le calcul de l’âge avec Excel est très utile. On prend par exemple un tableau avec toutes les dates de naissance et on calcule l’âge et la date du prochain anniversaire. Si, en plus, on indique les noms des personnes, on peut déterminer automatiquement la prochaine personne de la liste qui fêtera son anniversaire.
En explorant la notion de calcul de l’âge avec Excel, j’ai eu l’occasion d’approfondir mes connaissances sur le traitement des dates avec Excel. Et je vais m’empresser de vous les partager !
Le premier jour d’Excel
Excel ne sait gérer les dates qu’à partir du 1er janvier 1900 et jusqu’au 31 décembre 9999. D’ailleurs, Excel considère les dates comme des nombres. Vous pouvez faire la simple expérience en saisissant le chiffre 1 et en choisissant le format date pour la cellule. Excel indiquera alors le 1er janvier 1900.
Ensuite, chaque valeur indiquera le nombre de jour écoulés après ce 1er jour. La valeur 2 représentera le 2 janvier 1900 et ainsi de suite. Comme nous sommes plus de 100 ans après cette date, le 1er janvier 2000 correspond à environ 10*365 + le nombre d’années bissextiles entre 1900 et 2000 + 1. En l’occurrence, le 1er janvier 2000 correspond à la valeur 36’526. Or, si vous vérifiez la règle qui détermine les années bissextiles (multiple de 4 + pour les centenaires, le nombre de centaines doit également être un multiple de 4), vous ne devriez pas trouver le même résultat qu’Excel, mais 1 de moins !
Un bug officiel d’Excel est qu’il considère l’année 1900 comme une année bissextile !
Pour en revenir au traitement des dates par Excel et comme déjà expliqué, si vous saisissez 1 dans une cellule et que vous changez le formatage de la cellule en date, Excel vous affichera le 1/1/1900 !
Calcul âge Excel – Format date Excel
Par conséquent, lorsque vous faites des opérations entre deux dates, Excel réalise une opération numérique entre les deux valeurs.
Calculer la différence entre deux dates
Intuitivement, on se dit qu’en soustrayant deux dates, on trouvera le nombre de jours les séparant. Or, la première fois que vous faites cela, vous vous retrouvez avec ceci :
Calcul de la différence entre deux dates – Format date
La soustraction entre deux dates vous renvoie une date ! Comportement étrange, n’est-ce pas ?
Finalement, ce n’est qu’une question de format d’affichage de la cellule. Il vous suffit de placer le format d’affichage de la cellule au format « Standard » pour que tout rentre dans l’ordre.
Calcul la différence entre deux dates – Format standard
Ce calcul pourrait être la base pour calculer l’âge de quelqu’un à l’aide d’Excel. Par exemple, vous pourriez tenter de déterminer le quotient d’une division par 365.
Toutefois, je vous propose d’explorer une fonction cachée d’Excel = DATEDIFF().
DATEDIFF() – La fonction cachée d’Excel pour calculer l’âge
Vous pouvez faire le test, si vous commencez à saisir cette fonction dans la zone de formule, Excel ne vous la proposera pas.
Une fois saisie, vous n’aurez pas plus d’assistance. À vous de vous débrouiller pour trouver les paramètres, leur signification et les options.
Pourtant, Microsoft propose bien une page détaillant cette fonction. Je vous partage la même mise en garde, en théorie, cette fonction n’est pas supportée et peut retourner des résultats incorrects.
Toutefois, pour un usage basique et avec une acceptation sur les risques d’erreurs (le cas particulier est détaillé sur l’article de Microsoft), vous pouvez tout à fait utiliser cette fonction.
La syntaxe de la fonction DATEDIF()
DATEDIF(date_début,date_fin,unité)
Pour résumé cette fonction, DATEDIF() calcule un écart entre deux dates constituant votre intervalle de temps. Cet écart peut représenter un nombre de jours, de mois ou d’années selon ce que vous saisirez dans le dernier paramètre. Quelques options supplémentaires permettent de calculer des différences « spéciales ».
Par exemple, il sera possible de calculer un écart en nombre de jours entre deux dates en ignorant les valeurs des années, un nombre de mois entre deux dates en ignorant les jours et les années, ou encore un nombre de jours entre deux dates en ignorant la valeur des années.
Détail des attributs
date_début représente le début de l’intervalle. Cet attribut doit être une valeur soit au format date via DATEVAL(jj/mm/aaaa) soit une valeur de date telle qu’expliquée précédemment (=nombre de jours écoulés depuis le 1er janvier 1900).
date_fin représente la fin de l’intervalle et les mêmes contraintes de format s’appliquent.
unité détermine le type d’écart que vous souhaitez calculer. Les options sont les suivantes (et n’ont pas été traduites comme le laisse prétendre la page d’aide de Microsoft. Il faut donc bien utiliser les valeurs ci-dessous).
Les différentes options de l’attribut unité
« Y » : Nombre d’année complètes entre les deux dates.
« M » : Nombre de mois complets entre les deux dates.
« D » : Nombre de jours complets entre les deux dates.
Avant de vous donner les autres options possibles, j’insiste sur le mot « complet » qui est mis en gras. En effet, si vous calculez l’écart entre le 1er janvier et le 31 décembre de la même année, vous trouverez un jour de moins qu’on ne pourrait l’imaginer !
Prenez l’écart entre le 1er et le 2 janvier (1er jour et 2e jour), il n’y a qu’un jour d’écart (=2-1). Ainsi, entre le 1er jour et le 365e de la même année (pour les années qui ne sont pas bissextiles), il n’y a que 364 jours d’écart (=365-1). Il faut tenir compte de cet élément pour tous les types d’écart que vous calculerez (nombre jours, nombre de mois ou nombre d’année).
Les autres options à votre disposition sont :
« MD » : Différence entre les valeurs du jour des deux dates (les mois et les années sont ignorés).
« YM » : Différence entre les valeurs de mois des deux dates (les jours et les années sont ignorés).
« Yd » : Différence en nombre de jours entre les deux dates en ignorant l’année. Ce n’est pas totalement vrai… L’année de la date de fin est considérée comme étant égale à l’année de la date de début.
Entre le 1er janvier 2020 et le 1er mars 2020 il y aura un jour de plus qu’entre 1er janvier 2021 et le 1er mars 2022.
En effet, l’année 2020 étant bissextile, le résultat sera 31 + 29 = 60, alors que dans le second cas, il sera de 31 + 28 = 59.
Calculer un âge et le nombre de jours jusqu’au prochain anniversaire avec Excel
Calculer un âge avec Excel à partir d’une date de naissance est désormais à votre portée ! Plus besoin de se soucier de créer une fonction, ou pire, une macro (j’ai un avis argumenté sur la question des macros) pour déterminer l’âge de votre collègue.
Mieux, je vous propose à la fin de cet article de récupérer un modèle de fichier où vous n’aurez qu’à saisir le nom et la date de naissance pour que tout soit automatiquement calculé !
Première partie du classeur Excel de calcul de l’âge
Les deux premières colonnes sont les seules à éditer. Vous n’avez qu’à y saisir le nom et la date de naissance et tout le reste se fera tout seul.
J’ai pour habitude de placer en italique tous les champs qui sont calculés. Ainsi, je ne me demande pas très longtemps si je dois saisir une donnée ou non.
Calcul âge Excel – Première partie
Une fois les donnée saisies, cette première partie facile se met à jour.
Deuxième partie du classeur Excel de calcul de l’âge
Ici, cela devient plus intéressant. Je vous propose un champ calculé avec une mise en forme conditionnelle qui vous indique d’un coup d’œil les anniversaires qui ont déjà été fêtés et en vert ceux qui sont à venir.
Afin de vous donner une chance de vous rattraper, je vous propose le nombre de jours depuis lesquels l’anniversaire a eu lieu. Si par chance c’était le week-end dernier…
Calcul âge Excel – Deuxième partie
Chacune de ces colonnes utilise bien sûr la fonction DATEDIF().
Troisième partie du classeur Excel de calcul de l’âge
Au cas où vous souhaitez organiser une fête particulière pour une dizaine, je vous propose également le décompte du nombre de jours de la prochaine dizaine.
Calcul âge Excel – Troisième partie
Premier bonus : Déterminer le prochain anniversaire
Cela peut paraître simple a priori.
Mais la colonne « nombre de jours restant avant le prochain anniversaire » va s’avérer indispensable. Combinée à la colonne « Déjà passé ? », il est possible de savoir si c’est la date de cette année ou celle de l’année prochaine qu’il faut prendre en compte dans les calculs.
Calcul âge Excel – Partie bonus
De plus, pour éviter d’oublier une personne qui serait née le même jour, j’ai configuré une mise en forme conditionnelle dans le tableau pour mettre en avant les personnes ayant la même date d’anniversaire.
Calcul âge Excel – Partie bonus
PS : J’ai changé la date de naissance de Jeanne pour illustrer la situation.
Deuxième bonus : Changer la date de référence du calcul de l’âge
D’abord, expliquons la signification de cette fonctionnalité. Parfois, nous faisons des hypothèses : « Combien de jours lui restera-t-il avant son anniversaire lorsque nous serons à Noël ? ».
C’est pour cette raison que j’ai paramétré la date de référence sur une cellule et non sur la fonction AUJOURD’HUI().
J’ai nommé la cellule Q1 en DATEDUJOUR pour pouvoir l’utiliser dans toutes les fonctions ou mises en forme conditionnelles.
Calculer âge Excel – Dans le futur ou le passé
Ainsi, en vous plaçant sur cette cellule, vous pouvez soit saisir une date soit simplement réaliser une addition ou soustraction du nombre de jours souhaités.
Il est fréquent de se retrouver avec le besoin de fusionner plusieurs fichiers Excel en un seul. Par exemple, vous avez préparé un fichier modèle contenant un tableau. Puis, ce fichier est transmis à plusieurs personnes afin de collecter leurs données. Lorsque vous récupérez ces fichiers, vous allez devoir les fusionner en un seul sur Excel.
Malheureusement, vous songez sérieusement à fusionner les fichiers Excel manuellement à l’aide de nombreux copier-coller. Heureusement pour vous, vous avez fait une recherche sur Internet et avez trouvé cette page qui vous fera gagner de précieuses heures de manipulation.
Vous avez peut-être un autre objectif que fusionner plusieurs classeurs Excel en un ? Vous trouverez probablement l’objectif recherché dans le paragraphe suivant.
Déterminez votre objectif de fusion de fichiers Excel
En effet, fusionner plusieurs fichiers Excel peut viser trois objectifs très différents. Et en fonction de celui-ci, les moyens de l’atteindre ne seront pas les mêmes.
Les mots ont toute leur importance. Et pour bien parler de la même chose, je vous propose un récapitulatif des termes Excel :
Une feuille Excel correspond à ce que l’on nomme également les onglets.
Le classeur représente la totalité du fichier Excel.
Les différentes objectifs de fusion peuvent ainsi être :
Cas n°3 : Fusionner une feuille en particulier de plusieurs fichiers dans une seule feuille. L’objectif ici est de regrouper toutes les valeurs au sein d’un même tableau. Il y a une certaine analogie avec le cas n°2, mais au lieu de se trouver dans des feuilles différentes, les données sont dans des classeurs différents. Il faudra évidemment que les données soient dans un tableau disposant de la même structure (même nombre de colonnes et mêmes noms de colonnes).
Dans ces deux cas, des questions essentielles sont à se poser pour définir la meilleure approche :
Est-ce une action ponctuelle ou à faire régulièrement (et à quelle fréquence) ?
Vais-je devoir intégrer des mises à jour de fichiers Excel déjà fusionnés ? Exceptionnellement ou régulièrement ?
Cas n°1 : Consolider les feuilles de plusieurs fichiers en un seul classeur Excel
Voici un exemple avec deux fichiers. Le premier fichier contient les données du 1er trimestre :
Fusionner plusieurs fichiers en un – Zoom 1er Trimestre
Puis un second avec les données du 2nd trimestre :
Fusionner plusieurs fichiers en un – Zoom 2nd Trimestre
Votre objectif est d’avoir toutes ces feuilles dans le même classeur.
Étape n°0 : Décidez de votre destination
Avant de commencer une quelconque opération, vous devez déterminer où vous souhaitez retrouver le résultat de la fusion de vos fichiers Excel. Est-ce dans l’un des fichiers que vous souhaitez consolider les autres ou bien dans un classeur à part ?
Ensuite, vous devez en premier lieu ouvrir votre fichier cible puis les fichiers à fusionner les uns après les autres. Prenez le premier d’entre eux, puis continuez avec les étapes suivantes.
Étape n°1 : Sélectionnez vos feuilles à fusionner
Pour déplacer les feuilles d’un classeur Excel vers un autre classeur (et ainsi fusionner vos deux fichiers Excel) il faut cliquer sur les différentes feuilles en maintenant la touche Ctrl pressée.
Vous verrez que les feuilles sont bien sélectionnées car les noms des feuilles sont en gras et toutes sur fond blanc comme ceci :
Cas 1 – Sélection des feuilles à fusionner
Étape n°2 : Sélectionnez « Déplacer ou copier… »
Vous pouvez maintenant relâcher la touche Ctrl puis opérer un clic droit sur n’importe quelle feuille sélectionnée :
Cas 1 – Clic droit puis Déplacer ou copier
Si vous vous rendez compte que vous avez sélectionné une feuille de trop, pressez à nouveau sur la touche Ctrl puis cliquez sur la feuille que vous souhaitez retirer. Le fond redeviendra gris.
Étape n°3 : Sélectionnez le classeur Excel cible de la fusion
La fenêtre suivante s’affiche alors et vous permet de déterminer le classeur cible.
Cas 1 – Sélection classeur Excel cible pour la fusion
En cliquant sur le menu déroulant, vous verrez apparaître la liste des classeurs ouverts. Vous aurez également une option supplémentaire « (nouveau classeur) ». Comme son nom l’indique, cette option permet de déplacer (ou copier) toutes les feuilles sélectionnées vers un nouveau classeur Excel.
Cas 1 – Liste des classeurs disponibles pour la fusion
Dans mon exemple, j’ai choisi de fusionner mes données dans le classeur Excel « 1er Trimestre.xlsx ». Comme la capture d’écran le présente, vous pouvez à cet instant viser un nouveau classeur. Dans ce cas, cochez la case « Créer une copie » de l’écran suivant.
Déterminez l’emplacement de vos feuilles dans le nouveau classeur. Veillez à bien noter les intitulés que je vous ai mis en valeur sur la capture suivante.
Vous aurez à sélectionner où vous souhaitez placer vos feuilles importées dans le classeur Excel cible.
Cas 1 – Sélection de l’emplacement des feuilles pour la fusion
Notez que si vous ne cochez pas la case « Créer une copie », les feuilles seront bien déplacées d’un classeur à l’autre.
Dès que vous aurez cliqué sur « OK », vous aurez terminé la fusion de vos deux fichiers Excel.
Cas 1 – Feuilles Excel fusionnées
Et si j’ai de nombreux fichiers Excel à fusionner ?
Je mettrai à jour cette section dans les prochaines semaines afin d’y intégrer le code VBA qui vous offrira cette fonctionnalité.
Cas n°2 : Consolider les feuilles d’un même classeur dans une seule feuille
Vous avez plusieurs manières de résoudre ce cas de figure. La première sera sans code. Ce sera toujours ma préférence personnelle.
Particularité : vous devrez utiliser un nouveau classeur comme point de départ. Vous devez même quitter le classeur où se trouvent les feuilles à fusionner.
Étape n°0 : Bienvenue dans le monde de PowerQuery
Si vous aimez Excel, vous allez adorer PowerQuery (avant de devenir passionné de PowerBI…). Cet outil est venu enrichir les fonctionnalités d’Excel depuis la version 2010.
Depuis Excel 2016, PowerQuery fait partie intégrante d’Excel. Cette plateforme offre la possibilité d’automatiser de nombreuses manipulations sur les données. Les informaticiens appellent cela un ETL : Extract Transform Load. C’est un outil qui va automatiser des instructions sans écrire de code.
Je le rappelle à qui veut l’entendre, le code (= les macros ou le VBA pour les intimes) peut aussi être utilisé pour écrire des virus informatiques. Les entreprises vont de plus en plus empêcher l’envoi et la réception des fichiers en contenant.
PowerQuery apporte une très belle réponse à de nombreuses opérations qu’il fallait programmer auparavant. Je vous propose d’en découvrir l’une d’entre elles maintenant (si vous allez jusqu’au bout de l’article, vous verrez un deuxième usage de PowerQuery).
Étape n°1 : Déterminer votre source de données
Nous partons donc d’un classeur tout neuf, sans les données. C’est à partir de celui-ci que vous vous rendrez dans l’onglet « Données » puis « Obtenir des données ». En sélectionnant « À partir d’un fichier » puis « À partir d’un classeur », vous pointerez vers le fichier Excel où se trouvent les feuilles à fusionner.
Cas 2 – Déterminer le fichier source de la fusion des feuilles
Étape n°2 : Sélectionner les feuilles Excel à agréger
Lorsque vous avez sélectionné votre fichier Excel qui contient les feuilles à fusionner, vous vous retrouvez avec cet écran :
Cas 2 – Sélectionner les feuilles à fusionner
Veillez à bien cocher la case « Sélectionner plusieurs éléments » afin que les cases à cocher apparaissent en 2. Vous pouvez alors sélectionner les feuilles de votre choix (ici toutes) qui seront ensuite fusionnées.
ATTENTION : En 3, je vous invite à bien sélectionner « Charger dans… » et non « Charger » car nous n’avons pas besoin de ces données maintenant. C’est lorsque nous aurons terminées les manœuvres de fusion que vous pourrez les charger !
Lorsque vous aurez cliqué sur « Charger dans… », la fenêtre suivante s’affichera et vous devrez sélectionner : « Ne créer que la connexion » :
Cas 2 – Charger dans… – Ne créer que la connexion
Étape n°3 : Fusionner les feuilles Excel se nomme « Ajouter »…
A ce stade, vous avez créé une connexion vers un classeur Excel. Ensuite, vous avez créé des tables basées sur les différentes feuilles qui le compose. Maintenant, il ne reste que le dernier mouvement : Fusionner vos feuilles. Hélas, la traduction française de cette fonction est « Ajouter » :
Cas 2 – Ajouter des requêtes et non fusionner !
L’option « Fusionner » permet de connecter deux tables à l’aide d’une référence commune. Ainsi, si vous disposez d’une table avec des commandes avec des références de produits et une autre table avec le détail des produits, vous pourrez avoir les commandes avec le détail des produits en fusionnant ces deux tables (basées sur la référence produit).
Lorsque vous sélectionnez l’option « Ajouter », vous aurez alors à sélectionner les requêtes à mutualiser.
Cas 2 – Sélection des feuilles à ‘Ajouter’
En premier lieu, sélectionnez « Au moins trois tables » puis sélectionnez les tables disponibles. Cliquez sur « Ajouter » puis « Ok ».
Étape n°4 : Finaliser la mutualisation des données
La fenêtre suivante apparaît alors :
Cas 2 – Finaliser la fusion des données des différentes feuilles Excel
Vous pourrez changer le nom par défaut « Ajouter1 » en un nom qui correspond mieux à la situation. En l’occurrence, je vous propose « Mutualisation ».
Il ne vous reste alors qu’à presser sur « Fermer et charger ».
Cas 2 – État final – Données Excel Mutualisées
Étape n°5 : Bilan de la situation
A ce stade, vous disposez d’un nouveau classeur. Celui-ci est connecté à votre fichier Excel où se trouvent les feuilles à fusionner. Quels sont les avantages et inconvénients de la situation ?
Avantages
Si votre fichier source est mis à jour, vous pourrez venir sur celui-ci et simplement « Actualiser les données » pour que la fusion opère à nouveau !
Alors, pas n’importe quel type de mise à jour.. Si les données des feuilles existantes sont modifiées, pas de problèmes (ajout, suppression ou modification).
Si de nouvelles feuilles sont ajoutées, vous pourrez adapter les requêtes et compléter la fusion des feuilles. Je ne le développerai pas ici, car ce serait rentrer dans trop de détails sur PowerQuery. Mais c’est possible !
Lorsque vous aurez goûté à la puissance de PowerQuery, vous aurez envie d’aller plus loin dans les transformations et l’analyse des données brutes. À travers ce cas d’utilisation, vous entrez dans ce monde par la petite porte.
Inconvénients
Comme votre fichier contenant les données fusionnées est lié à un autre fichier, celui contenant les données de départ ne doit pas être déplacé. Si cela arrivait, pas de panique, il y a quelques paramètres à modifier et la connexion peut être retrouvée. Idem, je ne développerai pas ce point dans le présent article pour ne pas rentrer dans les détails de PowerQuery.
Vous disposez d’un classeur connecté. Le deuxième corolaire de la situation est que vous ne pouvez pas modifier la structure de ce tableau (ajouter des colonnes, en retirer). Heureusement, vous pouvez toujours réaliser un bon vieux « Copier-Coller » pour récupérer les données fusionnées libérées de toute contrainte.
Cas n°3 : Fusionner une feuille en particulier de plusieurs fichiers Excel dans une seule feuille
La situation est la suivante : vous devez collecter des données provenant de plusieurs personnes. Chacune d’entre elles dispose d’un tableau modèle à compléter et vous envoie les données dès que possible.
Vous allez en plus devoir faire face aux enjeux suivants :
Vous n’allez pas recevoir toutes les données en même temps et devrez consolider les données reçues au fur et à mesure
Certains pourraient vous envoyer des données incomplètes puis des mises à jour
Vous aurez peut-être à consolider des données par région / pays ou un autre critère
Comme annoncé plus haut, PowerQuery va être votre meilleur allié.
Étape n°1 : Préparer les noms des fichiers Excel à fusionner
Le nom des fichiers que vous aurez collectés va être essentiel pour la fusion à venir. Grâce à PowerQuery, vous pourrez collecter cette information et identifier l’origine des données, même lorsqu’elles seront fusionnées.
Cas 3 – Préparez les noms de fichiers à fusionner
Comme vous pouvez le voir sur la capture ci-dessus, j’ai nommé les fichiers par pays et de manière bien structurée.
Étape n°2 : Créer un nouveau classeur pour y fusionner les données des différentes fichiers Excel
Depuis le menu « Données », vous sélectionnez « Obtenir des données » puis « À partir d’un fichier » et enfin « À partir d’un dossier » :
Cas 3 – Obtenir des données à partir d’un dossier
Excel (via PowerQuery) est capable d’aller récupérer l’ensemble des fichiers d’un dossier en particulier et les traiter.
Cas 3 – Sélection du dossier source des fichiers Excel à fusionner
Étape n°3 : Importer uniquement les fichiers Excel que vous souhaitez fusionner
Après avoir sélectionné le dossier où se trouvent vos fichiers Excel, l’écran suivant s’affiche :
Cas 3 – Filtrer la liste pour sélectionner les fichiers Excel à fusionner
À ce moment (où à un autre), vous vous rendrez compte que d’autres fichiers sont présents dans votre dossier sélectionné (en 1). Dans ce cas, vous aurez à passer par la case « Transformer les données ».
Vous vous trouverez dans une vue similaire à celle-ci. Un peu comme au sein d’Excel, vous aurez la possibilité de filtrer cette liste. Ce que fera PowerQuery, c’est enregistrer la liste des étapes suivies pour les reproduire automatiquement par la suite.
Cas 3 – Filtrer la liste pour sélectionner les fichiers Excel à fusionner – Suite
En cliquant sur le bouton de filtre, vous retrouverez l’interface familière suivante :
Cas 3 – Filtrer la liste pour sélectionner les fichiers Excel à fusionner – Suite 2
Dans mon cas, je ne souhaite fusionner que les fichiers commençant par « Financial Sample_ ». Le caractère « _ » est important ici, car je ne souhaite pas y intégrer l’un de mes fichiers (Financial Sample – Tout.xlsx).
Cas 3 – Filtrer la liste pour sélectionner les fichiers Excel à fusionner – Suite 3
Étape n°4 : Combiner vos fichiers pour fusionner les données des fichiers Excel sélectionnés
PowerQuery vous montre désormais la bonne liste de fichiers à importer. Il ne reste qu’à cliquer sur le bouton destiné à fusionner toutes les données contenues dans les fichiers.
Cas 3 – Combiner les fichiers
PowerQuery va alors vous présenter le premier des fichiers qu’il prendra comme exemple.
Vous pouvez éventuellement sélectionner un autre fichier pour le prendre en exemple.
Vous déterminez l’objet à importer. Ici, PowerQuery a détecté la présence d’un tableau. Mais si vous le souhaitez, vous pouvez importer la totalité de la feuille.
Ici, cette option va éviter le blocage de PowerQuery en cas d’erreur. D’un côté, c’est une option utile car elle évite les erreurs. D’un autre côté, si vous avez des erreurs dans certains fichiers, vous ne le saurez pas et risquez de penser que tout a été importé… A vous de peser le pour et le contre de cette option.
Cas 3 – Déterminer le contenu à fusionner
Étape n°5 : Importer les données fusionnées dans Excel
Lorsque vous aurez cliqué sur « OK », PowerQuery opérera plusieurs opérations automatiquement.
Sur la droite (1), vous trouverez la liste des instructions qui seront suivies à chaque rafraîchissement des données. À ce stade, vous n’avez rien d’autre à faire qu’à « Enregistrer et charger » (2) pour finaliser l’import dans Excel.
Cas 3 – Prêt à charger les données fusionnées des fichiers Excel
Étape n°6 : Bilan de la situation
Vous venez de mettre en place une chaîne automatique d’import des données depuis un dossier. Dans Excel, vous n’aurez qu’à rafraîchir votre tableau pour que la magie opère automatiquement.
Cas 3 – Actualiser les données importées depuis les fichiers
Lorsque vous cliquerez sur « Actualiser », Excel ira chercher les fichiers dans le dossier configuré, fera le filtre des fichiers à sélectionner (si vous avez bien suivi ce tuto !) et fusionnera les données de tous les fichiers Excel.
Toutes les données seront actualisées. Si un des fichiers a été mis à jour, ce sera pris en compte.
Tous les nouveaux fichiers seront inclus. Si un ou plusieurs fichiers ont été ajoutés (ou supprimés), le tableau final n’aura que l’agrégation des données présentes au moment de l’actualisation.
Excel pourra ainsi fusionner des dizaines de classeurs présents dans le dossier en un seul clic.
Comme dans le cas précédent, si votre dossier est déplacé ou que celui qui ouvre le fichier Excel n’a pas les droits d’accès, vous ne pourrez pas actualiser les données. Vous devrez faire un copier-coller (juste les valeurs) dans un fichier séparé pour pouvoir transmettre ces données agrégées à des personnes n’ayant pas accès au dossier source.
Cas n°4 : Pas le temps d’apprendre, je veux une solution immédiate pour regrouper mes données / tableaux / feuilles ou fichiers Excel en un seul tableau
J’ai trouvé un add-on qui pourrait faire l’affaire, et plus encore. Je peux tout à fait comprendre que vous n’avez pas le temps de suivre un tuto, et que vous n’avez pas le temps de vous former.
Pourtant, vous devez quand même combiner vos données Excel, qu’elles viennent de tableaux, de feuilles ou de plusieurs fichiers.
Voici la vidéo que je vous ai préparé afin de voir le fonctionnement de l’add-on dans un des cas : fusionner plusieurs feuilles Excel du même fichier. Il est tout à fait capable de le faire pour plusieurs feuilles de plusieurs fichiers !
Il possède un gros défaut, c’est que l’interface n’est qu’en anglais à ce jour. Je pourrai tenter de convaincre l’éditeur de traduire cet add-on si vous êtes plusieurs à le réclamer…
Par ailleurs, je tiens à une entière transparence, si vous utilisez ces liens pour acquérir cet add-on, je toucherai une petite commission. Toutefois, je n’ai pas été rémunéré pour faire de la pub. J’ai sélectionné ce produit pour son efficacité et son rapport qualité / prix totalement librement.
L’éditeur vous accorde maintenant 50% de rabais sur le prix en passant par ces liens :
Avez-vous d’autres cas de figure auxquels je n’aurai pas pensé ? Est-ce que ce que je viens d’écrire soulève de nouvelles questions ?
Avez-vous apprécié ce que vous venez de lire ? J’aimerais bien le savoir en vous lisant à mon tour, via les réseaux sociaux ou les commentaires ci-dessous.
