Étiquette : Apprendre Excel

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 !

Comment fusionner plusieurs fichiers Excel en un seul ?

Comment fusionner plusieurs fichiers Excel en un seul ?

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 :

  1. Cas n°1 : Fusionner les feuilles de plusieurs classeurs Excel dans un seul classeur.
    L’objectif ici est d’avoir toutes les feuilles de plusieurs classeurs réunies dans un seul classeur.
  2. Cas n°2 : Fusionner les feuilles d’un même classeur dans une seule feuille.
    L’objectif est de n’avoir qu’un seul tableau de valeurs pour les analyser, à la place d’un tableau disposant d’une structure identique mais dont les données sont réparties sur plusieurs feuilles.
  3. 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
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
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
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
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
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
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
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
Cas 1 – Feuilles Excel fusionnées

Et si j’ai de nombreux fichiers Excel à fusionner ?

Je vous l’ai déjà écrit, je ne suis pas pour l’utilisation des macros à tout va dans Excel ou n’importe quel outil de la suite Microsoft 365 (nouveau nom de la suite Office depuis avril 2020). Mais là, je dois reconnaître qu’il sera nécessaire de mettre en place une macro pour automatiser la fusion des feuilles de plusieurs classeurs Excel vers un unique classeur.

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.

Avant cette version d’Excel, PowerQuery n’était pas intégré dans l’installation initiale d’Excel, vous deviez aller télécharger le composant sur le site de Microsoft.

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
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
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
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 !
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'
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
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 - Etat final - Données Excel Mutualisées
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
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
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
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
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
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
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
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

Cas 3 – Combiner les fichiers

PowerQuery va alors vous présenter le premier des fichiers qu’il prendra comme exemple.

  1. Vous pouvez éventuellement sélectionner un autre fichier pour le prendre en exemple.
  2. 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.
  3. 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
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
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
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.

Dernière étape

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.

J’attends de vos nouvelles ! A bientôt !

Curseur Excel : Sous toutes ses formes et toutes ses significations

Curseur Excel : Sous toutes ses formes et toutes ses significations

Le curseur d’Excel prend de multiples formes : croix blanche épaisse, croix noire fine, flèche noire vers le bas ou vers la droite, etc. Chacune de ces formes a une signification bien précise.

Apprenez à positionner judicieusement votre curseur, et vous manipulerez vos données, vos lignes et vos colonnes avec bien plus d’efficacité !

J’ai répertorié pas moins de 13 situations différentes du curseur d’Excel ! Je n’exclus pas d’en avoir oubliées, mais si tel était le cas, signalez-le moi dans les commentaires !

Après les premiers pas avec Excel, c’est un pas de plus vers la maîtrise d’Excel que faites en apprenant toutes les formes du curseur d’Excel.

Et je vous propose à chaque fois que c’est possible, les fonctions avancées pour chaque curseur !

Pour tout le reste de l’article : les fonctions sont activables lorsque le curseur possède la forme indiquée et que vous cliquez sur le bouton gauche de votre souris.

Pour les fonctions avancées, j’indiquerai les opérations complémentaires à réaliser.

Curseur Excel : Croix blanche

Fonction de base : La sélection de cellules

Curseur Excel - Croix blanche
Curseur Excel – Croix blanche

Cette forme de curseur permet de sélectionner une ou plusieurs cellules. Si vous maintenez la touche “Ctrl” appuyée pendant que le curseur a cette forme, vous pourrez sélectionnez autant de cellules que vous le souhaitez.

Fonction avancée : Sélectionner les mêmes cellules sur plusieurs feuilles

Je vous mets en situation pour bien comprendre l’utilité de cette fonction.

  1. Vous disposez d’un fichier récapitulant vos comptes
  2. Vous avez créé une feuille pour chaque mois de l’année
  3. Ainsi, vous savez qu’à la cellule B2, vous avez le nom du mois
  4. Vous souhaitez changer la mise en forme de cette cellule

Il vous suffit de :

  1. Sélectionner la cellule sur la feuille de votre choix
  2. Presser la touche Ctrl et la maintenir appuyée
  3. Cliquer sur chacune des feuilles où vous souhaitez opérer votre changement
  4. Une fois toutes les feuilles sélectionnées, relâchez la touche Ctrl
  5. Réalisez votre changement

Ce changement peut être :

  • Mettre en forme la ou les cellules sélectionnées (bordure, police, etc.)
  • Modifier le contenu de la cellule (texte ou formule !)

Pour ce dernier cas, la formule saisie sera strictement la même dans chacune des feuilles.

Curseur Excel : Croix noire

Curseur Excel - Croix noire
Curseur Excel – Croix noire

Fonction de base : “Remplir et étendre”

Cette fonction est très utile, complète et complexe à la fois. Elle fait tellement d’opérations d’une manière intégrée qu’il me semble utile de les décomposer.

Lorsque vous cliquez et déplacez le curseur vers d’autres cellules, Excel va :

  • Copier la mise en forme de votre cellule initiale. La copie inclut les mises en formes conditionnelles qui existent sur la cellule initiale.
  • Copier le contenu de la cellule initiale.

Cela correspond à Remplir. Passons maintenant à Étendre.

Excel va réaliser les actions suivantes selon le cas de figure.

Votre cellule contient un élément d’une liste personnalisée

Par défaut, Excel connaît la liste des jours de la semaine et la liste des mois de l’année. Si votre cellule initiale contient l’un de ces deux éléments, au lieu de simplement copier le contenu, il placera le prochain élément de la liste.

Votre cellule initiale contient une formule

Toutes les références relatives aux formules de la cellule initiale se mettent à jour en fonction de la direction vers laquelle vous avez “tiré” la cellule.

Si votre cellule contient =A1+B1 et que vous “tirez” vers la droite, la nouvelle cellule contiendra =B1+C1. Excel adapte les références relatives aux colonnes.

Si vous “tirez” cette même cellule vers le bas, la nouvelle cellule contiendra =A2+B2. Ce sont les références relatives aux lignes qui sont adaptées.

Votre sélection initiale contient plusieurs cellules

Excel va chercher à trouver une séquence logique à votre sélection pour l’étendre. Si vous aviez sélectionné une cellule contenant “1” et une deuxième contenant “2” et que vous étendez dans la même direction, Excel inscrira 3 puis 4 et ainsi de suite.

Selon la logique qu’Excel trouvera, il essaiera de ne pas juste copier mais étendre dans la mesure du possible.

Fonction avancée : Copier sans étendre

Et si vous ne souhaitiez pas qu’Excel étende ? Que vous souhaitiez que toutes les cellules contiennent “lundi” ou “janvier” ? Il suffira de cliquer sur la touche “Ctrl”

Curseur Excel - Étendre avancée
Curseur Excel – Étendre avancée

Curseur Excel : Double flèche

Fonction de base : Déplacer une cellule

Curseur Excel - Double flèches
Curseur Excel – Double flèches

Lorsque votre curseur prend cette forme, cela indique que vous allez déplacer la cellule ou le groupe de cellules.

Le déplacement de la cellule va emporter avec lui la totalité des informations ET des mises en forme appliquées à cette cellule.

Qu’advient-il des formules qui utilisaient la référence de la cellule avant son déplacement ?

Toutes les formules seront automatiquement mises à jour par Excel. Cela est également valable pour les éventuelles formules utilisées dans les mises en forme conditionnelles.

Fonctions avancées : Dupliquer la cellule ou insérer

Si vous pressez sur la touche “Ctrl” en même temps que vous déplacez la cellule, vous obtiendrez la fonction “Dupliquer”. Exactement l’équivalent d’un “Copier-Coller”.

Vous verrez un petit + apparaître à côté de votre curseur pour confirmer la copie.

Curseur Excel - Double flèches - Ctrl
Curseur Excel – Double flèches – Ctrl

Deuxième option : Pressez la touche “Shift” (dite aussi “MAJ”) pendant que vous déplacez la cellule.

À ce moment, c’est une insertion qui sera réalisée. Vous verrez une nouvelle forme apparaître. Celle-ci vous indiquera si ce sera une insertion dans une colonne ou dans une ligne que vous ferez.

Curseur Excel - Double flèches - Shift
Curseur Excel – Double flèches – Shift

Et si vous le souhaitez, vous pouvez combiner les deux touches “Ctrl+Shift” et ainsi réaliser une insertion d’une copie de vos cellules sélectionnées initialement.

Curseur Excel : Flèche noire horizontale / verticale / diagonale

Fonction de base : Sélectionner une ligne ou une colonne

Curseur Excel - Flèche noire sur colonne
Curseur Excel – Flèche noire sur colonne

Avec cette forme, Excel vous propose de sélectionner toute une colonne ou toute une ligne si vous placez le curseur sur un n° de ligne.

Bref aparté : avec l’utilisation de la fonction ci-dessous, votre tableau de valeurs va être transformé en tableau.

Mettre sous forme de tableau

Excel va alors vous permettre de sélectionner uniquement le contenu du tableau.

Fonction avancée : Sélectionner le contenu d’un tableau, avec ou sans l’en-tête

Curseur Excel - Flèche noire sur colonne table
Curseur Excel – Flèche noire sur colonne table

Souvent, les tableaux sont créés à la première ligne d’un classeur. Il n’est pas facile de distinguer si la flèche noire de sélection se trouve sur la colonne de la feuille (A, B, C, …) ou sur la colonne de la table (Colonne1, Colonne2 ou Colonne3 dans l’exemple ci-dessus).

Ainsi, dans une table, le premier clic permet de sélectionner le contenu de la colonne, sans le nom de la colonne. Au deuxième clic, le nom de la colonne est inclus dans la sélection.

Peu de gens le savent, mais en positionnant son curseur à gauche de la première colonne, celui-ci se met en diagonale comme ci-dessous :

Curseur Excel - Flèche noire diagonale sur première colonne
Curseur Excel – Flèche noire diagonale sur première colonne

Au premier clic, seul le contenu du tableau est sélectionné, sans les noms de colonnes. Au deuxième clic, tout le tableau est alors sélectionné.

Curseur Excel : Double flèche et barre verticale (ou horizontale)

Fonction de base : Changer la largeur ou la hauteur des cellules

Curseur Excel - Double flèches et barre verticale
Curseur Excel – Double flèches et barre verticale

Ce curseur apparaît lorsque vous le positionnez entre deux colonnes ou deux lignes de votre feuille Excel.

Avec cette forme de curseur, Excel vous permet de changer manuellement la largeur (ou la hauteur) de l’ensemble de la colonne (ou de la ligne) se trouvant à gauche (ou au-dessus).

Si vous sélectionnez plusieurs colonnes (grâce à la fonction du paragraphe précédent), ce sont alors l’ensemble des colonnes qui seront redimensionnées.

Fonction avancée : Ajuster automatiquement la largeur ou la hauteur

Lorsque vous avez sélectionné vos lignes ou colonnes, vous pouvez alors double-cliquer. Dans cette situation, Excel va ajuster la largeur (si vous aviez sélectionné des colonnes) ou la hauteur (si vous aviez sélectionné des lignes) au contenu de vos cellules.

Voici un exemple ci-dessous :

Dans l’exemple ci-dessus, les colonnes ont toutes la même largeur. Certains titres ne sont pas totalement lisibles, pendant que d’autres ont de l’espace.

Étape n°1 : Sélectionner les colonnes à ajuster

Curseur Excel - Sélection des colonnes à ajuster
Curseur Excel – Sélection des colonnes à ajuster

Étape n°2 : Positionner le curseur et double-cliquer

Vous positionnez alors votre curseur en haut à droite jusqu’à ce qu’il ait la bonne forme. Vous pouvez double-cliquer.

Curseur Excel : Après ajustement
Curseur Excel : Après ajustement

Vous observerez que les colonnes ont toutes été ajustées en fonction de leur contenu respectif. Par ailleurs, Excel a tenu compte de la présence de la flèche de tri/filtre sans mordre sur le texte.

Curseur Excel : Double flèche noire en diagonale

Fonction de base : Étendre votre tableau

Curseur Excel - Flèche noire diagonale tableau
Curseur Excel – Flèche noire diagonale tableau

Ce curseur ne peut apparaître que lorsque vous utilisez les tableaux (comme vu précédemment).

Vous pourrez étendre votre tableau automatiquement sur autant de lignes ou de colonnes souhaité. Mais pas les deux en même temps !

Si votre tableau possède une ligne ou une colonne de total, les formules seront automatiquement mises à jour.

Curseur Excel : Double flèche blanche verticale

Fonction de base : Redimensionner la barre de formule

Curseur Excel -Double flèches blanches verticales
Curseur Excel -Double flèches blanches verticales

Il n’y a pas que dans la zone où il y a des cellules que le curseur d’Excel change de forme. C’est également le cas pour la barre de formule. Cette dernière forme vous indique que vous pouvez agrandir l’affichage du contenu de la barre de formule.

Celle-ci peut parfois contenir un long texte (une longue formule ou juste du texte).

Curseur Excel - Barre de formule agrandie
Curseur Excel – Barre de formule agrandie

Vous pourrez ainsi visualiser et modifier les contenus de vos cellules plus facilement.

D’autres curseurs ?

Avez-vous trouvé une autre utilisation du curseur qui ne serait pas mentionnée dans cet article ? Avez-vous apprécié cet article ?

Je compte sur vos commentaires et vos partages de cet article autour de vous !

Apprendre Excel : Des premiers pas aux premiers résultats

Apprendre Excel : Des premiers pas aux premiers résultats

Comment apprendre Excel ? Vaste programme ! Par où débuter son apprentissage d’Excel ? Apprendre Excel veut dire bien des choses différentes pour chacun d’entre nous : est-ce apprendre toutes les fonctions d’Excel ? Est-ce concevoir un classeur complet ? Est-ce savoir utiliser et faire évoluer des fichiers Excel existants ? Bref, apprendre Excel est à la fois un objectif et tout un parcours (Personnellement, j’en apprends encore après tant d’années d’utilisation !)

Pour bien apprendre Excel, commençons par comprendre les fondamentaux

Excel est un tableur. Mais qu’est-ce qu’un tableur ? Prenons un extrait de la définition sur Wikipédia :

Une feuille de calcul est une table (ou grille) d’informations de toutes natures (données industrielles, scientifiques, commerciales, comptables, financières, statistiques, données de métiers, etc.). D’une manière générale, toute problématique, pouvant être disposée en lignes et colonnes, éventuellement sur plusieurs plans, impliquant ou n’impliquant pas des calculs, rend le tableur pertinent.

Wikipedia

Une partie de la deuxième phrase est essentielle “toute problématique pouvant être disposée en ligne et colonnes”.

Pour apprendre Excel, il faut penser le monde en lignes et en colonnes. Vous devez apprendre à quadriller, classer, structurer dans des tableaux vos informations / données.

Un petit avertissement à ce stade : si vous pouvez penser votre problème en lignes et en colonnes, Excel est un candidat, mais pas nécessairement LA solution. Access est un autre candidat… D’autres critères sont à explorer pour bien sélectionner le bon outil de travail.

Vous pouvez ainsi consulter les 4,5 apprentissages clés d’un cours Excel et en particulier le premier !

Apprendre Excel c’est apprendre à automatiser

Automatiser, oui, c’est le mot approprié. On ne parle pas encore à ce stade de programmation, mais il s’agit de faire travailler Excel à notre place pour des tâches répétitives.

La première des tâches répétitives est la réalisation d’opérations mathématiques simples pour plusieurs lignes ou plusieurs colonnes.

Afin d’automatiser, il faut pouvoir donner des instructions du type “Prends la valeur de la cellule en 2e ligne et 2e colonne, ajoute la valeur de la cellule en 3e ligne et 2e colonne et donne-moi le résultat”.

Vous comprendrez que c’est un peu long à écrire…

Pour simplifier cette instruction, chaque cellule possède une adresse. On parle également de référence d’une cellule.

Cette référence de cellule s’écrit avec des lettres et un nombre.

Dans Excel 365, les cellules vont de A1 à XFD1048576.

Apprendre Excel : les références

A quoi servent les lettres et les nombres d’une référence ?

La lettre détermine le rang de la colonne.

“A” = 1ère lettre de l’alphabet = 1ère colonne

“Z” = 26e lettre de l’alphabet = 26e colonne

Et après le “Z”, on recommence l’alphabet, mais avec deux lettres : “AA”, “AB”, “AC”, …, et ainsi de suite.

La colonne ZZ se trouve donc à la 26*26 + 26 = 702e colonne. (Nous avons 26 fois l’alphabet avec deux lettres + 1 fois l’alphabet avec une seule lettre)

Finalement, on ne cherchera pas vraiment à savoir à quel rang se trouvent les colonnes. Ce qui compte, c’est d’avoir compris le principe.

La colonne XFD se situe à la 16’384e colonne (je vous passe le calcul). Il s’agit d’une puissance de 2.

16’384 = 214

Eh oui, les informaticiens aiment bien les puissances de 2 !

Les nombres déterminent le rang de la ligne

Là, c’est bien plus simple à comprendre.

A12 sera la cellule située à la 12e ligne de la colonne A. B28, sera la cellule située à la 28e ligne de la colonne B.

Depuis quelques versions d’Excel, le nombre de lignes disponibles est de 1’048’576. Devinez quoi ? Encore une puissance de 2 !

1’048’576 = 220

Pour Exceller, vous devez savoir ceci : Excel peut utiliser une autre manière d’écrire les références !

Je dois vous avertir, le monde entier a décidé que les références s’utilisaient comme je vous l’ai décrit ci-dessus. Toutefois, une simple case à cocher dans les options d’Excel et vous entrez dans un autre monde. Celles des références ultra lisibles !

Au lieu d’écrire ZZ28, vous pourriez écrire L28C702.

En quoi est-ce plus simple ? Cette référence indique “L” (comme Lignes) suivi du numéro de la ligne puis “C” (comme Colonnes) suivi du numéro de la colonne.

C’est immédiatement plus clair.

Si vous souhaitez activer (ou désactiver) cette option, procédez comme suit :

Changer l'affichage des références - Étape 1 - Menu fichier - Option
Menu fichier – Options

Ensuite, cliquez sur le menu “Formules” et activez (ou désactivez) la case “Style de référence L1C1”.

Activer le style de référence L1C1 dans le menu Formules
Menu Formules – Style de référence L1C1

Votre feuille de calcul prendre alors la forme suivante :

Affichage d'une feuille Excel avec le style L1C1
Feuille de calcul – Style L1C1

Rassurez-vous tout de suite, toutes les formules de votre feuille de calcul sont automatiquement converties dans le bon format. C’est un simple remplacement d’une lettre par son rang.

En revanche, pour les plus habitués d’entre vous, cela peut sembler difficile de s’y retrouver.

Pour la suite, je n’utiliserai que la manière la plus répandue d’utiliser les références, à savoir avec les lettres et les nombres.

Apprendre à automatiser les opérations mathématiques simples avec Excel

Maintenant que vous avez bien compris la notion de référence d’une cellule, le véritable usage d’Excel va pouvoir commencer.

Nous disposons de cellules dans lesquelles nous allons inscrire des valeurs, ou des opérations.

Lorsque, dans une cellule, vous souhaitez faire un calcul utilisant la valeur de la cellule A1, vous allez indiquer à Excel, non pas la valeur elle-même, mais le fait qu’Excel doive récupérer la valeur contenue dans A1.

Ainsi, dès que vous modifierez la valeur de la cellule A1, toutes les cellules qui font référence à A1 se mettront automatiquement à jour.

Cependant, Excel ne peut pas savoir si vous écrivez A1 en référence à la cellule A1 ou juste parce que vous souhaitez écrire le texte “A1″…

C’est pourquoi il faut l’indiquer ! Ceci se fait en commençant par écrire le simple symbole “égal” : ‘=’.

Pour dire à Excel que vous souhaitez référencer le contenu de la cellule A1, il faut commencer par écrire le symbole égal ‘=’

Exemple 1 : L’addition et la multiplication

Prenons un exemple avec une facture extrêmement simplifiée.

Vous disposez d’un petit tableau avec les colonnes suivantes :

  • L’intitulé d’un article
  • Le prix unitaire de l’article
  • La quantité
  • Le prix total par article acheté
  • Le prix total de la facture
Exemple opération simple pour apprendre Excel

L’objectif à atteindre est de pouvoir modifier les valeurs du prix unitaire ou de la quantité et d’avoir automatiquement les résultats qui se mettent à jour.

Au lieu de raisonner avec les valeurs (1×299, 6×49, puis la somme des valeurs obtenues, il faut raisonner avec les références des cellules.

Référence celluleRésultat attenduFormule correspondante
D2La multiplication de B2 par C2=B2*C2
D3La multiplication de B3 par C3=B3*C3
D5La somme de D2 et de D3=D2+D3

Vous pouvez également observer que pendant que vous écrivez les formules, le texte de votre formule change de couleur. Les couleurs correspondent aux cellules auxquelles elles font références.

Apprendre Excel - Illustration d'une formule Excel avec la coloration des références d'une formule

Si vous avez bien retranscrit mon exemple, vous devriez avoir le résultat ci-dessous. J’aimerais d’ailleurs en profiter pour attirer votre attention sur 3 éléments :

  1. Premièrement, c’est la position actuelle de mon curseur. Il se trouve en D5, sur le total de la facture. C’est la zone de nom.
  2. Deuxièmement, on peut voir le contenu de cette cellule. Et c’est une formule que l’on voit, et non le résultat ! C’est la barre de formule.
  3. Troisièmement, sur la feuille de calcul, on voit le résultat.

A partir de maintenant, si vous modifiez le prix ou la quantité, vous verrez les résultats se mettre à jour automatiquement.

Exemple 2 : Utiliser votre première fonction – SOMME()

Dans mon exemple, c’était facile, il n’y avait que 2 lignes à ajouter pour obtenir le total de la facture. Si vous en aviez 30, ce serait un peu plus pénible de saisir =D2+D3+D4+…+D31.

C’est pour cela qu’Excel possède de nombreuses fonctions prédéfinies.

Je vous propose de découvrir la fonction SOMME().

Avant d’aller plus loin, je dois maintenant vous présenter la notion de PLAGE (sans le soleil et les serviettes… 🙂 ).

Apprendre Excel : Les PLAGES

Une PLAGE correspond à un ensemble de cellules. Ce sera une manière simplifiée d’indiquer à Excel que nous avons besoin de l’ensemble des cellules contenues dans un rectangle en particulier.

Regardez la sélection ci-dessous :

Apprendre Excel : Les plages

J’ai volontairement vidé tout le contenu des cellules pour mettre l’accent sur la zone sélectionnée. Et c’est également volontairement un rectangle composée de plusieurs colonnes et plusieurs lignes.

Nous aurons besoin des cellules situées en haut à gauche (ici B2) et en bas à droite (ici D5). Ce sont elles qui détermineront notre plage.

L’écriture à utiliser sera : B2:D5.

Excel comprendra qu’il faut prendre en compte toutes les cellules contenues dans ce rectangle.

Ce rectangle peut posséder uniquement une colonne : B2:B5 (en rouge). Ou encore, ne posséder qu’une seule ligne B2:D2 (en bleue).

Utiliser la fonction SOMME()

Vous l’avez deviné, nous allons pouvoir fournir une plage à la fonction SOMME() pour terminer notre exemple.

Avez-vous trouvé comment décrire cette plage ?

Les articles vont de la ligne 2 à la ligne 31, et les cellules à additionner se trouvent dans la colonne D. La plage se décrira ainsi : D2:D31.

Excel pourra alors interpréter cette plage et exécuter la fonction SOMME() sur celle-ci. Comme son nom l’indique, la fonction SOMME additionnera toutes les cellules et vous présentera le résultat.

Petite astuce pour avoir tout lu !

Pour vous remercier d’avoir tout lu, je vous présente une fonction que même les assez bons ne connaissent pas !

Vous avez pu voir sur cette capture qu’à moins d’être sur la cellule, rien n’indique que c’est le résultat d’une opération avec d’autres cellules.

Une fonctionnalité d’Excel permet de montrer immédiatement toutes les formules présentes dans toutes les cellules du classeur.

Ainsi, au lieu de voir le résultat du calcul, vous verrez les formules !

  1. Menu Formules
  2. Afficher les formules (à désactiver ensuite)

Et à ce moment, toutes les formules se révèlent ! En en cliquant sur les cellules (comme en 3), vous verrez immédiatement les cellules concernées par la formule.

N’oubliez pas de cliquer à nouveau sur cette option pour que le résultat revienne.

Pour une raison encore inconnue, les largeurs des cellules augmentent soudainement. Pas d’inquiétude, lorsque l’affichage des formules est désactivé, les largeurs reviennent à leur état originel.

Avez-vous appris à mieux utiliser Excel ?

Faites-le moi savoir dans les commentaires, partagez cet article sur vos réseaux, mettez-vous en contact avec moi sur LinkedIn ou Facebook.

Merci !

[Tuto] Jusqu’où peut-on aller avec les mises en forme conditionnelles sur Excel ?

[Tuto] Jusqu’où peut-on aller avec les mises en forme conditionnelles sur Excel ?

La mise en forme conditionnelle est à Excel ce que les épices sont à la cuisine ! Sans aucune mise en forme, cela peut être bon, mais sans saveur. En trop grande dose, cela peut gâcher tout votre travail. Dans cet article, je souhaite vous présenter quelques fonctionnalités de base :  mise en forme conditionnelle des textes, mise en forme conditionnelle des lignes. Je souhaite surtout vous présenter les fonctionnalités avancées des mises en forme conditionnelles. Enfin, je vous présenterai quelques astuces avancées pour réellement exceller avec les mises en forme conditionnelles !

Introduction

Excel permet de faire des calculs, présenter les résultats dans un graphique et également mettre en forme des données, c’est-à-dire changer le format du nombre, le style de la police, la présence et le style de bordure, et enfin le remplissage (la couleur du fond de la cellule).

La fonctionnalité de mise en forme conditionnelle dans Excel permet d’adapter ces attributs de manière automatique en fonction des critères de vos choix ! Cela peut paraître théorique lorsque c’est écrit ainsi, mais avec quelques cas pratiques, ce sera plus clair.

Cas pratique 0 : Prise en main des mises en forme conditionnelles

Créer une nouvelle règle

La fonction de mise en forme conditionnelle a été placée dans le bandeau “Accueil”, c’est vous dire si elle est fréquemment utilisée !

Comme le menu le présente, plusieurs fonctions pré-configurées existent. Celles-ci seraient d’ailleurs difficiles à mettre en place “sans code”. Elles sont donc utiles, mais ne sont pas extensibles…

La fonction que vous utiliserez le plus souvent est bien la “Nouvelle règle…”. Celle-ci vous correspondra et fera ce que vous lui demanderez.

Excel - Menu mise en forme conditionnelle

Une fois cette action réalisée, la fenêtre suivante apparaît.

Excel - Menu mise en forme conditionnelle - Type reglesDans la liste des options proposées, sur la même idée que précédemment, pour les règles de mise en forme conditionnelle “simples”, les 5 premiers choix pourront être utiles. Pour tous les autres cas, ce sera la dernière option “Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué”.

La formule qui permettra de déclencher la mise en forme sera à saisir dans la barre de formule en 2.

Et en 3, surtout, ne l’oubliez pas ! C’est la mise en forme à adopter si la formule saisie renvoie VRAI.

Combien de fois ai-je oublié de configurer la mise en forme… ? Je ne le compte plus. J’étais pourtant certain de ma formule conditionnelle… et rien ne se passait.

Au bout d’un quart d’heure à vérifier et revérifier, je me rends compte que je n’ai pas configuré de mise en forme spéciale… donc le champ ne se mettait pas en évidence !

Gérer les règles de mise en forme conditionnelleExcel - Gerer les mises en forme conditionnelle

Dans le menu initial des mises en forme conditionnelles, se trouve également une option essentielle : “Gérer les règles…”

Cette option permet non seulement de retrouver l’ensemble des règles qui s’appliquent sur une feuille de calcul, mais elle permet aussit de mettre en pratique une super astuce que je vous dévoile plus loin dans cet article !

L’écran qui apparaît alors est EXTRÊMEMENT important !

Excel - Menu mise en forme conditionnelle - Gerer regles

  1. Après l’oubli de configurer une mise en forme, c’est la deuxième cause de perte de temps ! Les règles affichées ne concernent que la portée affichée. Vous pouvez avoir comme choix : le tableau où se trouve votre souris, la sélection ou la feuille entière. Toutes les règles qui s’appliquent ne sont pas systématiquement affichées !
  2. Le format permet d’identifier d’un coup d’œil la règle recherchée.
  3. La zone “S’applique à” est ESSENTIELLE ! Elle va être la clé pour étendre une règle que vous aurez paramétrée sur une unique cellule.
  4. Last but not least, la case “Interrompre si Vrai”. Eh oui ! Il est possible d’interrompre l’application des règles de mise en forme dès qu’une règle est appliquée. Ainsi, l’ordre des règles va également revêtir une importance.

Avec toutes ces informations en main, vous êtes prêt à mettre en pratique les cas que je vous présente ci-dessous.

Cas pratique 1 : Mise en forme conditionnelle d’un texte

Besoin = Détecter les doublons

Le cas d’utilisation que je vois le plus fréquemment avec Excel, c’est de l’utiliser comme un fichier de commandes ou de factures (voire les deux en même temps !). Je vous invite à lire et à relire mon article décrivant les 4,5 apprentissages qu’un cours Excel devrait vous enseigner, et en particulier le premier point ! Cela étant dit, vous avez un problème à résoudre, c’est celui de ne pas donner deux fois le même numéro de commande ou de facture sur des lignes différentes.

Excel n’étant pas une application de gestion, il n’intègre pas de fonctionnalité d’incrément de numéro ou de création d’un identifiant unique.

Vous devez saisir les numéros de commandes qui contiennent en plus des lettres pour diverses raisons, vous n’avez pas fabriqué une formule de calcul automatique. Vous devez seulement vérifier qu’il n’y a pas eu d’erreur de saisie (et surtout de doublons).

Excel - Mise en forme conditionnelle - Texte

Étape n° 1 : Identifier la formule qui permet de détecter le doublon

Déterminons comment détecter un doublon pour une cellule donnée.

La réponse se trouve dans la question, détecter un doublon, c’est trouver au moins deux fois la même valeur. La formule qui sera utilisée est donc : NB().

Dans la mesure où il s’agira de tester si le nombre d’occurrences une valeur est strictement supérieure à 1, nous nous orienterons vers NB.SI(plage de recherche ; valeur recherchée).

En langage Excel, cela donnera : NB.SI($A$2:$A$19;$A$2)>1.

Cette formule signifie “Compte le nombre de fois que tu trouves la valeur de $A$2 dans la plage de valeur allant de $A$2 à $A$19 et dis-moi si ce nombre est supérieur à 1”.

Étape n° 2 : Tester cette formule sur un seul champ

Pour tester cette formule, il ne reste qu’à ajouter une formule contenant une condition, “Si – Y’a-t-il un doublon” – alors VRAI sinon FAUX.

Cela nous amène à la formule complète : =SI(NB.SI($A$2:$A$19;$A$2)>1;VRAI;FAUX).

Étape n° 3 : Généraliser sa formule sur une plage de champs

Excel - Plage mise en forme conditionnellePour appliquer une règle sur un ensemble de champs, il sera nécessaire de relire et analyser à nouveau la formule qui avait été trouvée.

Il faut analyser les références fixes et réfléchir à celles qui doivent devenir relatives.

Rappel sur les références et l’utilisation du symbole $

Une référence à une cellule avec des symboles $ indique que la référence ne doit pas s’adapter au contexte, l’absence du symbole indique qu’il faut s’adapter au contexte.

Deuxième rappel, $A2 signifie que la colonne ne doit pas s’adapter, mais la ligne oui (ce sera toujours la colonne A). A$2 indique que la colonne doit s’adapter et la ligne non (ce sera toujours la ligne “2”).

Notre formule précédente =SI(NB.SI($A$2:$A$19;$A$2)>1;VRAI;FAUX) possède deux références. L’une correspond à toutes les cellules parmi lesquelles nous recherchons des doublons ($A$2:$A$19), l’autre correspond à la cellule dont nous cherchons les doublons ($A$2).

Comme nous allons étendre la zone sur laquelle nous recherchons des doublons, si la cellule recherchée reste avec les symboles $, la mise en forme changera uniquement si la cellule A2 est en doublon ! Il est donc essentiel de changer manuellement cette référence de $A$2 en A2. Ainsi, la règle adaptera AUTOMATIQUEMENT au contexte où elle s’applique.

Il ne vous reste qu’à étendre la plage d’application de la règle.

Dans le menu “Gérer les règles de mise en forme”, vous devriez avoir cet écran :

Excel - Plage mise en forme conditionnelle - 1

Ainsi, seule la première cellule est vérifiée. Vous allez pouvoir étendre la zone sur laquelle cette règle s’applique avec la petite flèche vers le haut et en sélectionnant toute la plage de vérification.

Excel - Plage mise en forme conditionnelle - 2

Maintenant, c’est tout bon !

Attention : Si vous retiriez les $ de la plage de recherche ($A$2:$A$19), celle-ci changerait également automatiquement. Elle deviendrait A3:A20 pour le test de la 2e ligne, A4:A21 pour la 3e, et ainsi de suite. Au fur et à mesure, les valeurs des premières cellules du tableau ne seraient plus dans la zone de recherche d’un doublon !

Autres cas

A ce stade, vous l’aurez certainement compris, la mise en forme conditionnelle d’une date ou la mise en forme conditionnelle d’une somme n’est pas plus difficile. Il vous faut déterminer la formule qui vous correspond pour détecter quand il faut changer la mise en forme.

Ne pas oublier que la mise en forme conditionnelle peut également adapter le format des nombres. J’ai d’ailleurs récemment découvert la mise à jour des monnaies disponibles dans Excel avec la présence du Bitcoin !

Excel - Menu mise en forme conditionnelle - Format nombre

Cas pratique 2 : Mise en forme conditionnelle d’une autre cellule

Initialement, nous avons tendance à penser que la mise en forme doit s’appliquer sur la cellule contenant les valeurs. Que nenni ! Les règles partent de la cellule à mettre en forme, peu importe où elle se trouve.

Ce sont les règles de mise en forme conditionnelle qui vont déterminer les conditions de changement de la mise en forme.

Ainsi, je peux configurer une cellule que j’appellerais “Garant de la conformité”. Je peux par exemple conditionner sa mise en forme au respect de toutes les règles de gestion de ma feuille Excel.

  • S’il y a un doublon, je passe en rouge.
  • S’il manque une valeur dans une ligne, je passe en bleu.
  • Si le total est négatif, je passe en orange.

D’un coup d’œil, il est alors possible de connaître l’état de cohérence de la feuille / du classeur Excel.

Cas pratique 3 : Mise en forme conditionnelle d’une ligne entière

La mise en forme conditionnelle d’une ligne entière n’est pas plus compliquée que celle d’une cellule unique. Le véritable défi se situe dans la configuration des références ($A$1 ≠ $A1 ≠ A$1 ≠ A1).

La situation que j’utilise régulièrement, c’est de mettre en forme toutes les cellules concernées par un calcul qui ne respecte pas une condition. Par exemple, je vais répartir un travail entre des équipes à l’aide de pourcentage. Bien sûr le total doit faire 100, et je peux le calculer et présenter le résultat.

Excel -MFC - Ligne entiere

L’enjeu reste toujours de gérer les références fixes et relatives. Dans l’exemple présenté, le test est assez simple : =SI($K$2<>1;VRAI;FAUX).

Attention, 100% = 1 et pas 100… Sinon, votre test retournera VRAI tant que le total ne sera pas de 10’000 %.

La plage où appliquer cette règle sera l’ensemble du tableau : $C$2:$K$11.

Hélas, cela ne fonctionnera que pour la première ligne. Dès la deuxième, cela posera un problème ! Avez-vous trouvé pourquoi ?

Il n’y a aucune référence qui est relative dans le test “SI”. Ce sera toujours la cellule K2 qui sera testée. Comme vous souhaitez rendre le test relatif à la ligne où vous vous trouvez, il faudra saisir $K2 au lieu de $K$2.

La bonne réponse est donc : =SI($K2<>1;VRAI;FAUX).

Bonus pour devenir un expert

Dès que possible, j’utilise les tables dans Excel. Cela a un avantage important dans le nommage des cellules dans les formules.

Ainsi, la somme des pourcentages (issue de l’exemple précédent) n’est pas SOMME(C2:J2) mais SOMME(TChargesRepartition[@[Equipe 1]:[Equipe 8]]).Excel - MFC - Table

Cela rend la formule lisible et compréhensible.

Comment faire pour rendre les formules de mise en forme conditionnelle également lisibles ?

Pas de chance ! Les références structurées ne sont pas supportées par cette fonction ! (pas encore… j’espère que cela arrivera un jour !).

Mais, j’ai tout de même une petite astuce pour cela ! Cela s’appelle les “noms”. Oui, oui, les “noms”.

Excel - MFC - Les noms

Cela permet de donner un nom à une plage ou à une cellule. Ainsi, au lieu de désigner les cellules selon leur adresse (A1, K2, …), il est possible de donner un nom à une cellule et d’y faire référence dans les formules.

Reprenons le premier exemple de cet article avec la mise en évidence des doublons.

Excel - Mise en forme conditionnelle - TexteLa formule était : =SI(NB.SI($A$2:$A$19;$A$2)>1;VRAI;FAUX).

Vous pouvez sélectionner toutes les cellules contenant les n° de commandes (de A2 à A19) et leur donner un nom dans la zone nom directement.

Excel - MFC - Donner un nom

La zone nom correspond à cette zone où se trouve habituellement le nom d’une cellule. Ici, j’ai choisi le nom “NoCommandes”.

Ensuite, vous allez pouvoir changer votre formule dans la condition de mise en forme en :

=SI(NB.SI(NoCommandes;A2)>1;VRAI;FAUX)

Cela peut paraître anodin comme changement, mais je vous assure que lorsque vous avez des fichiers Excel complexes avec de très nombreuses mises en forme conditionnelles en place, identifier le périmètre concerné par une formule fait gagner beaucoup de temps !

Dernière action

Avez-vous trouvé la réponse à votre question ? En avez-vous d’autres ? Votre feedback compte pour moi. Un petit commentaire en dessous de l’article me confirmerait que vous avez lu jusqu’ici ! Merci !