• Facebook - Black Circle
  • Twitter - Black Circle
  • Google+ - Black Circle
  • YouTube - Black Circle

Contactez-nous

Tél. 06 25 87 59 94 
E-mail : nodexconsulting@gmail.com

235 rue de charenton 75012 Paris

Abonnez-vous

Suivre notre actualité

Choisir un abonnement :

© Copyright Nodex Consulting  2018

Penser autrement Innover simplement

Microsoft Excel est sans doute le logiciel le plus complexe de la suite Microsoft Office. Il est assez simple à prendre en main pour créer des tableaux. Mais des astuces existent pour profiter des très nombreuses fonctionnalités du logiciel. Voici quelques astuces pour maîtriser Excel. 

Sur Excel, les fonctions sont très nombreuses. Elles permettent d’effectuer des calculs, rechercher des données, ou obtenir des informations. Pour insérer une fonction, il suffit de cliquer sur l’icône Fx présent sous le ruban. Toutes les cellules qui contiennent une fonction commencent par le caractère =. Si vous connaissez bien certaines fonctions, vous pouvez y accéder directement en saisissant = puis le nom de la fonction au sein d’une cellule. Pour mieux cerner le potentiel des fonctions Excel.

Recherche et matrices​

  • La fonction RECHERCHE - Cette fonction permet de rechercher une valeur dans une colonne et retranscrire son équivalent dans une autre colonne. Exemple : RECHERCHE(42;A:A;B:B) va chercher la valeur 42 dans la colonne A, puis retranscrire son équivalent dans la colonne B (sur la même ligne). RECHERCHE permet également de chercher dans une matrice ou un vecteur.

  • La fonction RECHERCHEH - RECHERCHE Horizontale permet de chercher des valeurs selon la première ligne d’une matrice. Exemple : RECHERCHEH(F1;A1:D10;2;VRAI) permet de renvoyer la deuxième ligne d’une colonne dont la première ligne est égale à la valeur de la cellule F1 au sein de la matrice A1:D10.

       Valeur recherchée= F1 

       Tableau de recherche = A1:D10

       N° index = 2 (la deuxième ligne de la matrice)

       Valeur = Vrai (correspondance approximative) Faux (Correspondance exacte)

  • La fonction RECHERCHEV - Même principe que RECHERCHEH, mais pour chercher des valeurs selon la première colonne d’une matrice. Exemple : RECHERCHEV(F1;A1:D10;2;VRAI) permet de renvoyer la deuxième colonne d’une ligne dont la première colonne est F1 au sein d’un tableau.

       Valeur recherchée= F1

       Tableau de recherche = A1:D10

       N° index = 2 (la deuxième colonne de la matrice)

       Valeur = Vrai (correspondance approximative) Faux (Correspondance exacte)

  • La fonction TRANSPOSE – Permet de transposer une matrice (transforme les lignes en colonnes et vice-versa). Pensez simplement à valider votre formule en utilisant Ctrl+Maj+Entrée, puisqu’il s’agit d’un calcul matriciel.

Logique

  • ET – Permet de tester plusieurs conditions et vérifier qu’elles sont vraies. Exemple : ET(1337-42=1295;1295+42=1337) va renvoyer la valeur VRAI, car 1337-42=1295 et 1295+42=1337.

  • OU – Renvoie la valeur VRAI si l’un des critères est exact. Exemple : OU(1+1=2;1+1=3) est vrai parce que 1+1=2.

  • SI – Permet de tester une condition et renvoyer une certaine valeur si c’est vrai, une autre si c’est faux. Exemple : SI(moyenne(A1:A15) »j’ai la moyenne »; »je n’ai pas la moyenne ») permet de savoir si on a la moyenne, quand les notes sont comprises entre les cellules A1 et A15.

Date et heure

  • ANNEE – Pour obtenir l’année correspondante à une date plus précise. Exemple : ANNEE(« 09/10/2013 ») renvoie l’année 2013.

  • MOIS – Même principe, pour obtenir le mois. Exemple : JOUR(« 09/10/2013 »)renvoie la valeur 10.

  • JOUR – Même principe, pour obtenir le jour. Exemple : JOUR(« 09/10/2013 ») renvoie la valeur 9.

  • JOURSEM – Même principe, mais cette fonction permet de connaître le jour de la semaine associée à la valeur. Exemple : JOURSEM(« 09/10/2013 »;2) renvoie le chiffre 3, car le mercredi est le troisième jour de la semaine. Pour commencer la semaine le lundi, il faut utiliser le paramètre 2 (comme dans l’exemple ci-dessus).

  • HEURE – Même principe, pour obtenir l’heure. Exemple :HEURE(« 12:30:40 ») renvoie 12.

  • MINUTE – Même principe, pour obtenir les minutes : Exemple MINUTE(« 12:30:40 ») renvoie 30.

  • AUJOURDHUI – Comme son nom l’indique, cette fonction permet d’afficher la date du jour. Exemple : AUJOURDHUI() pour afficher la date du jour. Vous pouvez également afficher le lendemain en utilisant AUJOURDHUI()+1 et ainsi de suite.

  • MAINTENANT – Encore plus précise, cette fonction permet d’obtenir la date exacte (année, mois, jour, heure, minute, seconde). L’affichage dépend du format de la cellule. Exemple : MAINTENANT() pour afficher la date et l’heure exactes.

  • FIN.MOIS – Permet d’obtenir le dernier jour du mois en cours. Exemple : FIN.MOIS(« 09/10/13 »;0) retourne le 31 octobre 2013. Vous pouvez modifier le 0 en +1 pour obtenir le dernier jour du mois suivant, ou -1 pour obtenir le dernier jour du mois précédent et ainsi de suite.

  • FRACTION.ANNEE – Permet de connaître la fraction d’une année qui correspond à nombre de jours donnés. Exemple : FRACTION.ANNEE(« 01/01/98″; »01/01/99 »;1) va renvoyer 1, car cette durée correspond à une année entière. La dernière variable permet de choisir comment sont comptés les jours : 1 pour la valeur réel, 2 pour une base 360, 3 pour une base 365 etc.

  • NB.JOURS.OUVRES – Comme son nom l’indique, cette fonction permet de compter le nombre de jours ouvrés. Exemple : NB.JOURS.OUVRES(« 09/10/2013″; »31/12/2013 ») renvoie le nombre 60, car il y reste 60 jours ouvrés avant la fin de l’année. Vous pouvez ajouter des jours fériés de cette façon : NB.JOURS.OUVRES(« 09/10/2013″; »31/12/2013″; »25/12/2013 »).

  • NO.SEMAINE – Renvoie le numéro de semaine correspond à une date. Exemple : NO.SEMAINE(AUJOURDHUI();2) renvoie le nombre 41, car nous sommes à la semaine 42. Le paramètre 2 permet d’indiquer que les semaines commencent le lundi.

Comme de nombreux logiciels, quelques raccourcis bien utiles permettent d’être plus productif sur Excel. Outre les traditionnels Ctrl+S pour sauvegarder un document ou Ctrl+C pour copier un élément, ces 10 raccourcis spécifiques à Excel permettent d’aller plus vite.

  • Recalculer les données : F9

  • Insérer la date du jour : CTRL+; (point virgule)

  • Insérer l’heure : CTRL+: (deux points)

  • Atteindre une cellule ou un tableau : F5

  • Sélectionner toute une colonne active : CTRL+Espace

  • Sélectionner toute une ligne active : SHIFT+Espace

  • Sélectionner toute la feuille Excel : CTRL+A

  • Faire un collage spécial sur Excel (pour ne coller que les données etc.) : CTRL+ALT+V

  • Insérer une somme automatique : ALT+=

  • Masquer (et surtout ré-afficher) le ruban des fonctionnalités : CTRL+F1

les racourcis clavier sur ms.excel

les autes fonctions

 
 

Nommer un tableau sur Excel

Dans certains cas, il peut être utile de nommer un tableau. Sélectionnez la plage à nommer (exemple : de la cellule B2 à la cellule E7). Cliquez ensuite sur le champ situé à gauche, sous le ruban, puis inscrivez le nom de votre choix. Dans cet exemple, notre tableau se nomme Data. Une fois nommé, vous pouvez utiliser les données de votre tableau très facilement : pour connaître la somme des données présentes dans notre tableau, un simple =SOMME(Data) suffit. Vous pouvez accéder au gestionnaire de noms via l’onglet Formules du ruban.

Le copier coller des fonctions Excel

Très souvent, les fonctions d’un tableau se suivent et se ressemblent. Pour cette raison, Excel utilise certaines règles lorsque vous copiez collez une fonction pour conserver la logique de vos calculs. Dans l’exemple ci-dessous, la colonne SOMME correspond la somme des valeurs de la ligne. Il suffit de copier la fonction D2 et la coller en D3 pour obtenir la somme des données de la troisième ligne.

Créer une suite logique grâce au cliquer glisser

Au lieu de copier coller les formules, vous pouvez les dupliquer grâce au cliquer glisser. Il suffit de sélectionner la(les) cellule(s) source, puis placer son curseur en bas à droite de la cellule. Une petite croix va apparaître, permettant de copier coller facilement la formule (ou une série de formules) vers le côté ou vers le bas. Certaines valeurs, telles que des plages de cellules, seront remplacées automatiquement pour conserver la logique de vos fonctions.

Vous pouvez également créer une suite logique grâce à cette astuce, mais vous devez obligatoirement sélectionner plusieurs cellules. Exemple : si deux cellules adjacentes contiennent deux dates qui se suivent (le 16 octobre et le 17 octobre par exemple), vous pouvez les sélectionner, cliquer sur la petite croix en bas à droite de la cellule et glisser votre curseur sur plusieurs cellules pour afficher le 18 octobre, le 19 octobre, le 20 octobre et ainsi de suite. Ceci marche aussi pour les suites de nombres.

Figer la ligne, la colonne ou la cellule dans une fonction Excel

Dans certains cas, on souhaiter « figer » une ligne, une colonne ou une cellule dans une suite de fonctions. Pour toujours diviser un calcul par la même cellule par exemple, quel que soit l’emplacement de la formule. Pour ce faire, lorsque vous saisissez votre formule, il suffit d’appuyer sur la touche F4 lorsque la cellule en question est mentionnée. Par exemple, inscrivez =SOMME(D4, puis appuyez sur F4. Le début de votre fonction va se transformer en =SOMME($D$4. Le symbole dollar indique que l’élément suivant est figé (ici, la colonne D et la ligne 4 sont figés). Il suffit d’appuyer à nouveau sur F4 pour figer uniquement la ligne ou la colonne. Une astuce à connaître !

Appliquer une mise en forme selon les données d’un tableau

Pour mieux visualiser les valeurs d’un tableau, vous pouvez appliquer une mise en forme conditionnelle. Rendez-vous dans l’onglet Accueil du ruban, puis Mise en forme conditionnelle et choisissez les règles de votre choix. Vous pouvez également afficher des icônes selon les données du tableau.

Modifier l’affichage d’une cellule selon le type de données

Excel permet de modifier l’affichage d’une cellule selon le type de données. Il peut s’agir d’un texte, d’une valeur, d’une date, d’un prix… Sélectionnez la cellule (ou la plage de cellule de votre choix), faîtes un clic droit, puis choisissez « format de cellule ».

Cette astuce permet aussi de choisir le nombre de chiffres après la virgule. Il suffit de choisir la catégorie « Nombre » puis le nombre de décimales après la virgule. Pratique !

 

 

 

 

 

 

 

 

 

 

 

Les Tableaux Croisés Dynamiques sur Excel

C’est LA fonctionnalité de Microsoft Excel. Souvent copiée, jamais égalée. Elle permet de créer des tableaux complexes, en croisant plusieurs plages de données. Nous allons prendre un exemple concret pour détailler les possibilités offertes par cet outil : celles des statistiques Facebook.

Nous avons donc exporté les statistiques de notre page Facebook (publications) sur une période donnée (non significative) pour tenter d’évaluer la portée des publications selon l’heure, le jour, le type de contenu partagé. Nous avons retraité certaines données afin de mettre en évidence les heures et les jours des posts. Une fois nos données mises au propre, au sein de l’onglet Insertion, nous cliquons sur Tableau Croisé Dynamique pour en insérer un. Nous sélectionnons l’intégralité des données et créons notre TCD dans une nouvelle feuille de calcul.

Un tableau s’affiche alors, ainsi que la liste des champs du tableau croisé dynamique et quatre zones :

  • Étiquettes de colonnes : l’heure, dans notre cas,

  • Étiquettes de lignes : le jour, dans notre cas,

  • Valeurs : la portée des publications dans notre cas,

  • Filtre du rapport : le type de contenu dans notre cas.

Il suffit de cliquer déposer les champs disponibles dans les bonnes zones pour créer votre tableau croisé dynamique. En général, il est bon de vérifier les paramètres des champs de valeur (via un clic droit sur les données présentes dans la zone valeur). Dans notre cas, il fut nécessaire de choisir « Moyenne » plutôt que « Nombre » pour obtenir la moyenne des portées. Voici le tableau que nous obtenons, après avoir ajouté une mise en forme conditionnelle. Il permet de mettre en évidence la portée moyenne des publications selon l’heure et le jour de publication, filtré selon le type de post (il s’agit ici d’un exemple non-représentatif, les données ne sont pas assez nombreuses).

Modifier la hauteur d’une ligne ou la largeur d’une colonne

Pour modifier la taille d’une ligne ou d’une colonne, il suffit de faire un clic droit sur le numéro de la ligne ou la lettre de la colonne puis choisir « hauteur de ligne » ou « largeur de colonne ». Vous pouvez spécifier la largeur par défaut des colonnes grâce au bouton « Format » de l’onglet Accueil du ruban.

 

Insérer une ligne ou une colonne

Pour insérer une ligne, il suffit de faire un clic droit sur le numéro d’une ligne ; même principe pour une colonne, il suffit de faire un clic droit sur la lettre d’une colonne. Celle-ci sera toujours insérée avant. Si vous le souhaitez, vous pouvez aussi passer par le ruban (onglet Accueil) pour insérer une ligne ou une colonne. Vous pouvez également supprimer une ligne ou une colonne de cette façon.

Gérer plusieurs feuilles Excel sur un classeur

Un document Excel peut être composé de plusieurs onglets. On appelle un classeur le document complet, les onglets sont des feuilles. Vous pouvez ajouter facilement des feuilles grâce au bouton dédié en bas à gauche de la fenêtre (MAJ+F11). N’hésitez pas à nommer vos feuilles  pour plus lisibilité. Vous pouvez également donner une couleur à un onglet pour mieux le retrouver (clic droit sur l’onglet).

Toujours afficher la première ligne / la première colonne sur Excel

Sur Excel, vous pouvez figer les volets : cela permet de toujours afficher la première ligne ou la première colonne. Vous pouvez également choisir de figer plusieurs lignes ou plusieurs colonnes ! Pour ce faire, sélectionnez la première cellule non-figée. Par exemple, si vous souhaitez figer la ligne 1 et la colonne A, sélectionnez la cellule B2. Dans l’onglet Affichage, choisissez Figer les volets et le tour est joué.

Utiliser un axe secondaire sur un graphique Excel

Cette astuce Word est également valable sur Excel : pour améliorer la visibilité d’un graphique, vous pouvez ajouter un axe secondaire en ordonnées. Ceci permet de mettre en forme deux séries de données dont les valeurs sont très éloignées. Une fois votre graphique créé, faîtes un clic droit sur l’une des courbes puis cliquez sur « Mettre en forme une série de données ». Vous n’avez plus qu’à choisir « Axe secondaire » pour améliorer la visibilité de votre graphique.

Créer un graphique à partir de données Excel

Créer un graphique sur Excel, c’est très facile. Le mieux est de sélectionner vos données, puis de choisir le type de graphique de votre choix dans l’onglet Insertion. Sur Excel, vous pouvez également créer un graphique au sein d’une cellule : on appelle cela un graphique Sparkline.

Faire un calcul matriciel sur Excel (résultat sur plusieurs cellules)

Dans certains cas, le résultat d’un calcul Excel ne peut être affiché sur une seule cellule. On parle alors de calcul matriciel. C’est notamment le cas de la fonction TRANSPOSE, qui permet de transposer une matrice. Pour ces fonctions matricielles, vous ne pouvez pas taper ENTER pour valider la formule. Vous devez sélectionner la plage de sortie, saisir votre formule, puis taper CTRL+SHIFT+ENTER. De cette façon, le résultat de votre calcul matriciel sera affiché sur l’ensemble des cellules sélectionnées.

 

 

 

 

 

 

 

 

 

Ajouter des compléments à Microsoft Excel

Aucun doute n’est permis : Microsoft Excel fait partie des logiciels les plus puissants. Mais dans certains cas, les fonctionnalités ne suffisent pas ! Il faut alors ajouter des compléments à Excel. XLSTAT ou Analysis ToolPak permettent par exemple de réaliser de nombreux calculs statistiques. Pour ajouter un complément à Excel, il suffit de se rendre dans les options (onglet Fichier), puis Compléments. Cliquez sur « Atteindre » pour gérer ou ajouter des compléments au logiciel.

Trier et filtrer les données d’un tableau Excel

Trier les données sur Excel, c’est très facile : sélectionnez d’abord votre tableau (avec ou sans les étiquettes), puis cliquez sur Trier dans l’onglet Données. Une fenêtre s’ouvre, vous invitant un trier votre tableau selon telle ou telle colonne et de telle ou telle façon (du plus petit au plus grand etc.). Dans l’exemple ci-dessous, nous avons trié un tableau Excel selon les notes obtenues en Français.

 

Évidemment, dans ce cas, l’intégralité de la ligne est conservé, seul le tri des lignes est modifié. Si vous le souhaitez, vous pouvez également filtrer les données. C’est pratique pour n’afficher que les données de certains clients par exemple (en filtrant la ville etc.). Il suffit alors de sélectionner votre tableau puis cliquer sur Filtrer, dans l’onglet Données du ruban.

Personnaliser l’impression d’un tableau Excel

Les feuilles Excel sont pratiques pour agréger de nombreuses données. Mais bien souvent, l’impression brute des classeurs Excel donne des résultats peu convaincants. Pour améliorer l’impression des tableaux Excel, vous pouvez passer par l’onglet Mise en Page, puis « Imprimer les titres ». Une fenêtre vous permet alors de choisir une zone d’impression, les titres à imprimer etc. Vous pouvez aussi imprimer le quadrillage, les commentaires Excel et les erreurs générées par vos fonctions.

Restreindre les valeurs sur Excel

Sur Excel, vous pouvez spécifier des règles pour restreindre les données. Prenons l’exemple d’un tableau agrégeant les notes d’une série d’étudiants. Elles sont toutes comprises entre 0 et 20. Vous pouvez spécifier cette plage de données, afin qu’une erreur soit générée si une autre note est appliquée. Il suffit de sélectionner la plage de données de votre choix, puis choisir « Validation des données » au sein de l’onglet Données. Si une valeur incohérente est saisie, le message suivant est affiché.

Grouper les lignes sur Excel (calcul de sous-total etc.)

Grouper les lignes permet d’améliorer la lisibilité d’un tableau sur Excel. Dans l’exemple ci-dessous, nous avons par exemple groupé les lignes Grammaire et Orthographe sous la ligne Français, puisqu’il s’agit de matières liées au français. Il suffit de sélectionner les lignes secondaires, puis cliquer sur Grouper (onglet Données). Vous pouvez également dissocier les lignes ou calculer un sous-total.

Convertir un fichier Excel en PDF

L’enregistrement en PDF n’est pas réservée aux documents Word ! Vous pouvez aussi transformer un tableau Excel en document PDF. Mais tout dépend de votre version de Microsoft Excel :

Microsoft Excel 2010 et 2013 : il suffit d’utiliser la fonction Enregistrer sous. Choisissez alors PDF comme type de fichier, et le tour est joué.

Microsoft Excel 2007 : il suffit d’ajouter ce complément : Enregistrement en PDF ou XPS dans Microsoft et le tour est joué.

L’enregistrement des fichiers Excel en PDF pour les versions antérieures n’est pas possible sur Excel.

Sélectionner un tableau en entier ou atteindre la fin d’Excel

Sur Excel, deux raccourcis clavier sont particulièrement utiles : CTRL+Flèche et SHIFT+Flèche. Le premier permet d’atteindre la dernière colonne ou la dernière ligne d’une série de données. Le second permet de sélectionner la cellule suivante. CTRL+SHIFT+Flèche permet donc de sélectionner tout un tableau. Il suffit de se placer sur la première cellule d’un tableau, utiliser CTRL+SHIFT+Flèche droite, puis CTRL+SHIFT+Flèche du bas. De la même manière, vous pouvez atteindre la fin d’une feuille Excel (XFD1048576) en utilisant CTRL+Flèche droite puis CTRL+Flèche du bas.

Les macros sur Excel

Sur Excel, difficile de se passer des macros. Elles permettent d’enregistrer des actions simples ou complexes. Il faut parfois du temps pour les créer, mais après, le logiciel se charge de reproduire vos actions. Les macros Excel permettent de gagner un temps fou !

Il suffit de passer par l’onglet Affichage, puis Macros, pour enregistrer une série d’actions, afficher les macros disponibles, modifier ou exécuter une macro Excel. N’hésitez pas !

Convertir un CSV en fichier Excel

Pour convertir un CSV en fichier Excel, deux possibilités : vous pouvez faire un clic droit sur le fichier, puis Ouvrir avec… Excel. Ou ouvrir Excel, passer par l’onglet Données, et cliquer sur À partir du texte. Ensuite, laissez-vous guider par l’assistant d’importation. En général, les données sont séparées par une virgule, et les textes sont identifiés par des guillemets. Mais vous pouvez personnaliser ces paramètres grâce à l’assistant, ainsi que le format de chaque colonne importée.

FOrmation gratuite avec lecompagnon.info

Vous retrouverez toutes ces formations  sur le site internet de Patrice Leroy : www.lecompagnon.info

Haut de page