Archives de catégorie : Excel 2007

La rechercheH

Dans un cours précédent, vous avez pu voir comment utiliser la fonction RechercheV pour la recherche verticale et nous allons maintenant voir l’intérêt et l’utilisation de la RechercheH pour la recherche horizontale.

Prenons pour exemple, un tableau de notes pour une classe de 14 élèves .

Tableau Source

 

Celui-ci pourrait comporter bien plus de colonnes et de lignes, ce qui rendrait la collecte d’information plus longue et ce que nous voulons c’est pouvoir facilement et rapidement trouver les renseignements pour un élève en particulier.

Pour commencer, j’ai créé en dessous de mon tableau principal, un nouveau petit tableau pour accéder rapidement aux informations souhaitées une fois que j’aurai saisi les formules adéquates.

Image2

 

Ce que l’on souhaite, c’est que dès que je saisi le nom d’un élève, je puisse voir immédiatement dans quelle classe il se trouve et quelles notes a-t-il obtenus pour chaque matière.

Dans la cellule B12 : « Elève », je ne rentre aucune formule car c’est ici que je vais saisir le nom de mon élève.

Par contre, en cellule B13 : « Classe », je vais utiliser la formule RechercheH pour par la suite obtenir l’information de manière automatique.

La RechercheH se décompose comme la recherveV en 4 parties :

La formule RechercheH

 

Comme toute formule, nous commençons par le signe = , suivi du nom de la formule, soit RechercheH.

Pour le premier élément, Excel nous indique de lui préciser la valeur cherchée, ce qui dans notre exemple, sera le nom de l’élève que nous allons saisir dans la cellule B12.

Valeur cherchée

Premier élément : La valeur cherchée

Sans oublier de séparer chaque élément de notre formule par les « », Excel nous demande comme second élément, notre tableau et que nous allons simplement lui désigner en sélectionnant toutes les cellules.

Image5

Second élément : La sélection du tableau

 

Le troisième élément (et on n’oublie pas nos « »), indiqué par Excel par l’intitulé « no_index_lig », représente le numéro de la ligne où Excel doit rechercher l’information dans notre tableau.

Dans notre exemple, dans la cellule où nous entrons la formule, nous voulons savoir la Classe de l’élève. Dans notre tableau, cette information se trouve en ligne 2, ce que nous lui indiquons en saississant simplement le chiffre 2.

Attention, il s’agit bien du numéro de la ligne en fonction de la sélection de votre tableau. Même si vous avez crée votre tableau plus bas dans votre feuille Excel, par exemple si vous l’avez commencé en ligne 5, les informations par rapport à la Classe, seront toujours en ligne 2 par rapport à la sélection de votre tableau.

Image6

 

Quatrième et dernier élément, Excel nous demande si il doit rechercher une correspondance approximative ou exacte. Comme nous souhaitons avoir une information et une valeur juste, nous allons simplement cliquer sur FAUX – Correspondance exacte.

Ci qui signifie que si nous entrons le nom d’un élève qui n’existe pas, Excel ne donnera bien sûr aucune information. Si nous avions sélectionné la correspondance approximative, Excel, nous aurai fourni les informations de l’élève le plus proche.

Image7

Faux - La correspondance exacte

 

On ferme la parenthèse pour signifier que notre formule est terminée et il ne nous reste plus qu’à cliquer sur Entrée.

Une fois validée, si vous voyez #N / A, ne vous inquiétez pas ; Excel vous signifie qu’il n’y a pas de valeur et c’est normal car nous n’avons pas renseigné le nom de l’élève à chercher dans la cellule B12.

#N / A

 

Si maintenant, je lui demande de me chercher la classe de l’élève13 par exemple, Excel me dit bien qu’il est en classe 1B.

Image10

 

La formule sera la même pour toutes les cellules pour lesquelles nous voulons les notes ; La seule chose qui changera est le numéro de la ligne où l’information doit être retrouvée.

Soit la ligne 3 pour la note de Français, la ligne 4 pour la note de Mathématiques, la ligne 5 pour la note d’Histoire-Géographie et ainsi de suite.

Une fois mes formules indiquées pour chaque cellule, je peux saisir le nom de n’importe quel élève et je vois immédiatement toutes les informations que je souhaitais.

 

Image11

Image12

Définir et fixer les décimales sous Excel 2007

Si vous devez saisir un nombre important de chiffres comme des valeurs comptables ou financières et qui doivent comporter un nombre égal de décimale, les fonctions du groupe Nombre d’Excel vous sera très utile.

La procédure est très simple. Imaginons un simple tableau de 2 colonnes où nous indiquons le mois de facturation et le montant facturé. Dans cet exemple, j’ai saisi mes données chiffrées en masse brute sans faire attention à la mise en forme.

Image1

Pour rendre mon tableau plus présentable et plus lisible, je souhaite que toutes mes cellules comportant mes données chiffrées, à savoir ma colonne B, soient au même format avec 2 décimales.

Sur le ruban d’Excel et dans l’onglet Accueil, nous pouvons voir le groupe Nombre.

Groupe Nombre de l'onglet Accueil du ruban Excel

Groupe Nombre de l’onglet Accueil du ruban Excel

Par défaut, le format affiché est Standard.

Pour le modifier, nous allons dans un premier temps :

  • sélectionner la colonne pour laquelle nous souhaitons modifier ce format
  • cliquer sur la petite flèche de la fenêtre du groupe Nombre pour avoir accès aux autres formats en déroulant cette liste
Menu déroulant des différents formats

Menu déroulant des différents formats

Nous pouvons maintenant voir les différents formats possibles. Nous retrouverons le format Standard mais également Nombre, Monétaire, Comptabilité, Date courte, Date longue, etc.

Ici, ce sont les formats les plus couramment utilisés mais vous pouvez avoir accès à une multitude d’autres choix en cliquant sur « Autres formats numériques ».

Dans notre exemple, nous avons plusieurs possibilités.

  • En cliquant sur le format Nombre, vous pouvez voir, ci-après, qu’automatiquement toutes mes données chiffrées se sont mises en forme avec deux chiffres après la virgule (par défaut sous Excel).

Format Nombre

  • Si nous souhaitons en plus des deux chiffres après la virgule, ajouter le sigle de l’Euro , il vous suffit de sélectionner le format Monétaire, ce qui nous donnera la mise en forme suivante :

Format Monétaire

  • Pour ce format, vous pouvez également choisir le format Comptabilité, ce qui donnera la mise en forme suivante et qui est quasi identique au format Monétaire :

Format Comptabilité

Petite astuce pour ce format : Vous pouvez le sélectionner très rapidement en cliquant sur la petite icône suivante :

Raccourci rapide pour le format avec le sigle de l'euro

Raccourci rapide pour le format avec le sigle de l’euro

Et en cliquant sur la petite flèche de cette icône, vous pouvez avoir accès au symbole représentant les Dollars $, si nécessaire.

Raccourci rapide pour afficher le format Comptabilité avec le signe des Dollars $

Raccourci rapide pour afficher le format Comptabilité avec le signe des Dollars $

Comme nous avons sélectionné la colonne entière, ce format choisi s’appliquera à toutes les cellules, même si vous entrez de nouvelles données par la suite.

Vous pouvez également définir le format de votre colonne en amont en sélectionnant votre colonne vide. Vos données chiffrées se mettrons en forme automatiquement et peu importe la façon dont vous, vous les entrerez.

2 chiffres après la virgule est le format par défaut d’Excel pour les fonctions Nombre, Monétaire ou Comptabilité.

Si vous souhaitez en retirer ou en ajouter, il vous suffit d’utiliser les deux icônes suivantes en cliquant autant de fois que vous voulez ou non de décimales.

Dans notre exemple, j’ai cliqué deux fois sur le symbole avec la flèche à droite pour retirer les 2 décimales et obtenir ainsi des chiffres entiers :

Ajouter ou supprimer des décimales

Image10

Nous pouvons observer qu’en retirant des décimales, la petite fenêtre, m’indique que nous avons défini un format Personnalisée.

Format personnalisée

 

 

Changer la couleur d’une cellule si la valeur est différente

Pour changer la couleur d’une cellule si la valeur est différente il faut créer une règle de mise en forme conditionnelle pour la cellule en question.

Exemple :

Dans la Feuil1, cellule C3 est saisit le nombre 22. Lorsque cette valeur sera modifiée par une valeur quelconque, la couleur de trame de la cellule deviendra rouge.

Mise en pratique :

Sélectionnez la cellule C3, puis dans l’onglet Accueil, cliquez sur le bouton Mise en forme conditionnel -> Nouvelle règle…

Dans Sélectionnez un type de règle, choisissez l’option : Appliquer une mise en forme uniquement aux cellules qui contiennent.

Dans Modifier la description de la règle, sélectionner à l’aide des menus déroulants les options : Texte spécifique et ne contenant pas.

Dans le champ (à droite) saisissez 22, la valeur de la cellule C3.

A présent, cliquez sur le bouton Format… Depuis l’onglet Remplissage sélectionnez une couleur (ici ce sera du rouge).

Pour finir, cliquez sur OK pour fermer les boites de dialogues et valider les paramètres.

Mise en application :

La mise en forme de la cellule C3 reste inchangée le moment. Changez sa valeur (55 par exemple). La couleur de trame de la cellule doit devenir rouge.

Nota 1 : La règle peut s’appliquer sur plusieurs cellules contenant la même valeur. Pour cella elles devront être toutes sélectionnées avant de cliquer sur Mise en forme conditionnel…

Nota 2 : La règle ne peut pas s’appliquer sur plusieurs cellules ne contenant pas la même valeur. Dans ce cas, il faudra créer autant de règle qu’il n’y a de cellules.

Répéter une ligne de titres pour l’impression sur Excel 2007

Vous avez un tableau comportant de nombreuses lignes et qui par conséquent représente plusieurs feuilles d’impression.

Nous allons voir comment nous pouvons répéter une ligne de titre afin de l’imprimer automatiquement sur chaque feuille.

Cette procédure est valable sur excel 2007, 2010 et 2013.

Pour notre exemple, je viens de créer un tableau pour le suivi de nos stagiaires qui comporte 6 colonnes et 125 inscriptions comme ci-après :

Image1

 

 

 

 

 

Je souhaite imprimer ce tableau de suivi et si je vais dans l’aperçu avant impression :

(Bouton Office – Imprimer – Aperçu avant impression)

Image2

 

 

 

 

 

Lors de mon aperçu,  je peux voir que ma première page est parfaitement présentée :

Image3

 

 

 

 

 

Sauf que pour toutes mes pages qui vont suivre, je m’aperçois que mes titres n’apparaissent plus :

Image4

 

 

 

 

 

Bien sûr, vous pourriez faire un copier-coller de la ligne comportant vos en-têtes mais cela signifie que si vous faites la moindre modification comme ajouter ou supprimer une ligne, toutes vos lignes de titres seraient elles-mêmes décalées et vous n’auriez plus qu’à tout recommencer.

Pour que notre ligne de titres s’imprime de façon automatique, nous allons aller dans l’onglet Mise en page et utiliser la fonction Imprimer les titres

Image5

 

 

 

 

 

 

En cliquant sur Imprimer les titres, une nouvelle fenêtre mise en page s’ouvre et vous pouvez voir dans la rubrique Titres à imprimer, la fonction Lignes à répéter en haut

Image6

 

 

 

 

 

 

Je clique sur le symbole qui va me permettre d’indiquer à Excel quelle ligne je souhaite qu’il répète lors de l’impression de mon tableau.

Image7

 

 

 

 

 

 

 

La fenêtre Mise en page – Lignes à répéter en haut s’ouvre et il ne me restera plus qu’à sélectionner la ou les lignes sélectionnées.

Image8

 

 

 

 

Dans mon exemple, je sélectionne de la ligne 1 à 4 car je souhaite aussi qu’il prenne le logo en compte et je valide.

Image9

 

 

 

 

 

Je retrouve ma fenêtre précédente et je valide en cliquant sur OK.

Image10

 

 

 

 

 

 

Je retourne dans mon aperçu avant impression et je vois que pour n’importe laquelle de mes pages, Excel m’imprimera bien automatiquement toutes les lignes sélectionnées.

Image11

 

 

 

 

 

 

Dupliquer un classeur excel.

excel 2013Pour les besoins d’un suivi client ou chiffre d’affaire, dupliquer un classeur excel peut s’avérer précieux.

La duplication n’est pas très difficile, mais n’a pas les mêmes méthodes  en fonction du contenu. S’il s’agit de faire la différence entre ce que vous souhaitez copier, dupliquer ou retranscrire.

L’exemple d’un classeur avec une dizaine de feuilles à l’intérieur, représentants différents clients. Pour reprendre ce classeur pour chaque mois de l’année, la duplication passera par la fonction « enregistrer sous » puis « nom.xls ».

A partir de là vous pourrez préparer votre travail pour l’année entière.

Juste un point pour les autres données à dupliquer, qui ne sont pas des classeurs.

  • Pour un cellule, ou une plage de cellule : Il faut tout simplement faire un copeir/coller.
  • Pour dupliquer une feuille :Clic droit en bas, sur le nom de la feuille, puis dupliquer.
  • Pour dupliquer un classeur :Enregistrer-sous, puis on renomme ce classeur dans le fichier de destination voulu.

 

Faire un renvoi à la ligne sur excel 2014 : Astuce de la semaine

Les passionnés comme les confirmés me diront que ce n’est pas la découverte de la semaine, mais rappelons nous la première fois que nous avons chercher à faire un renvoi à la ligne dans une cellule excel…!

Pas très facile, même plutôt casse tête pour les non-initiés.

Voilà donc la procédure sur Excel 2014 (respectivement utilisé sur la version 2010 et 2007 de la même façon) :

  • Allez dans l’onglet d’accueil
  • Sélectionner la partie « alignement » qui se trouve au milieu de ce bandeau
  • Pré-sélectionner votre cellule, ou pour appliquer la règle à toute la page, faire une « selectionnez tout »
  • Puis, il suffit de cliquez sur « Renvoyez à la ligne automatiquement« 

Voilà la capture sur Excel 2014 ci dessous, pour vous permettre de comprendre au visuel.

retour-ligne-excel

Bref, rien de compliqué, mais c’est tout de même bien pratique d’en profiter !

Somme.Si : Faire une somme avec une condition sous Excel 2007

L’intérêt de la fonction Somme.Si est de pouvoir calculer une somme seulement si une condition, un critère est respecté.

Prenons pour exemple, le tableau suivant :

Le client X a reçu le devis et souhaite savoir au fur et à mesure qu’il valide les produits, le montant réel de sa commande.

Pour cela, on va donc utiliser la fonction Somme.Si. On va demander à Excel de nous calculer la somme seulement si nous avons l’accord « Oui » dans la colonne E de notre tableau.

 

Photo 1

 

 

 

 

 

Regardons de plus près la construction de cette formule :

Comme toutes les formules Excel, nous avons le signe = qui va indiquer à Excel que nous souhaitons faire un calcul.

Nous avons ensuite SOMME.SI, ce qui va indiquer à Excel, que nous souhaitons faire la Somme de nos données seulement Si le critère que nous allons lui indiqué est respecté.

La parenthèse permet par la suite de cerner notre formule. La parenthèse servant à définir l’ordre dans lequel Excel doit effectuer les opérations.

La plage va être l’ensemble des données dans lesquelles Excel doit aller chercher l’information, ici notre critère, à savoir dans notre exemple, de la cellule E4 à la cellule E11, ce qui nous donnera la plage E4:E11

(les  : servant de référence à Excel pour prendre en compte toutes les données comprises entre ces 2 cellules).

Photo 2

 

 

 

 

 

Ne pas oublier les points virgules ; qui servent à séparer les arguments de notre fonction.

Nous devons par la suite indiquer notre critère. Dans notre exemple, nous allons utiliser le terme Oui qui serait à mettre entre guillemet comme il s’agit de texte.

Ce qui nous donne pour le moment :

Photo 3

 

 

 

Excel nous demande par la suite la somme_plage, c’est-à-dire la plage de données où il doit effectuer les calculs si le critère que nous lui avons défini est respecté, à savoir dans notre exemple, dans notre colonne « Prix total » de la cellule D4 à la cellule D11, traduit par D4:D11

Photo 4

 

 

 

 

 

 

Il ne nous reste plus qu’à valider notre formule (sans oublier de fermer notre formule par une parenthèse).

Par la suite, à partir du moment où j’indique Oui dans ma colonne Accord, Excel me calcule la somme de mes produits validés.

On peut voir ci-après qu’Excel ne m’a calculé que la somme des articles Chaussure, Gant, Pull, Veste de Pantalon car ils contiennent bien le critère Oui dans ma colonne Accord et n’a pas pris en compte les articles Écharpe, Bonnet et Manteau car le critère n’est pas respecté.

Photo 5

 

 

 

 

 

 

Comment utiliser la fonction NBval sur Excel.

Utilisation de la fonction NBVAL sous Excel 2007 ou excel 2010

Nous allons voir ici comment nous pouvons compter un nombre de cellules contenant des données que ce soit du texte ou des nombres à l’aide de la fonction NBVAL sous Excel 2007 (procédure également valable sur la version 2010 et 2014).

Prenons pour exemple, un tableau Excel où nous suivons au jour le jour nos équipes de formateurs.

excel nbval

Dans notre tableau nous voulons savoir le nombre de personnes pour chaque catégorie et pour cela nous allons donc utiliser la fonction NBVAL qui va nous permettre en fait de calculer le nombre de cellules non vides.

Nous allons donc dans un premier temps, nous positionner dans la cellule B17, cellule dans laquelle nous voulons que s’affiche notre résultat pour la catégorie « Formateurs Excel – Niveau 1 ».

Comme vous le savez, toute formule doit commencer par le signe = auquel nous ajouterons la fonction NBVAL ; Dans la barre de formule, notre formule débutera donc ainsi.

nbval

En effet, en double cliquant sur cette fonction, nous voyons qu’automatiquement la parenthèse s’est affichée et qu’Excel nous apporte des indications sur les données que nous devons entrer pour que notre formule fonctionne correctement :

detail formule nbval

Ce que nous voulons, ce sont le nombre de données contenues dans notre colonne B intitulée « Formateurs Excel – Niveau 1 » et nous allons sélectionner, à l’aide de la souris, la plage de données de la cellule B5, où débute les noms de nos formateurs, à la cellule B16, où elle se termine.

exemple formule nombre valeur

On n’oublie pas de fermer la parenthèse et on valide notre formule à l’aide de la touche Entrée de notre clavier, ce qui nous donne =NBVAL(B5 :B16) ; Notre résultat s’affiche bien dans la cellule que nous avions sélectionné, à savoir B17 dans notre exemple.

Pour rappel, les : permette à Excel de comprendre qu’il doit prendre en compte toutes les données contenues entre la cellule B5 et la cellule B16

resultat formule excel

Il ne nous reste plus qu’à l’aide de la souris à étirer notre formule vers la droite pour la recopier et nos résultats s’afficheront pour chacune des catégories.

etirement formule excel

copier coller automatique formule

A présent, vous pouvez à ajouter ou supprimer un nom dans n’importe laquelle de vos colonnes, vos résultats s’agrémenteront automatiquement.

La fonction NB.SI également possible pour calculer le nombre de cellules pleines dans la plage sélectionnée.

A titre indicatif et un peu plus compliqué, pour cet exemple qui ne contient que des données textes, vous auriez également pu utiliser la fonction NB.SI qui vous calcule le nombre de cellules si la condition est respectée.

formule nb si

On demande ici à Excel de nous calculer le nombre de cellule entre la plage de données B5 et B16 si la cellule contient du texte, représentée par * que nous mettons entre guillemets.

calcul nombre de cellules

Comme vous pouvez le constater par vous-même, la première formule en utilisant la fonction NBVAL est bien plus simple à utiliser et nous arrivons au même résultat.

Modifier le format date sous Excel 2007

Excel vous présente différents formats de dates qui vous permettent de les présenter comme bon vous semble.

Par défaut, sous Excel, si vous saisissez une date, celle-ci s’affichera sous la forme « 01-janv » alors que vous l’avez saisi à l’aide du clavier numérique « 01/01 ».

Pour que votre date  s’affiche de manière complète, vous devez modifier le format de votre cellule.

  • Pour cela, sélectionnez la plage de données contenant vos dates ou la colonne entière si cette dernière n’est destinée qu’à ce type de données.
  • Dans l’onglet « Accueil » du ruban, dans la rubrique « Nombre », vous avez un menu déroulant qui par défaut, affiche « Standard ».

Il vous suffit alors de cliquer sur la petite flèche qui déroule les différentes options et de sélectionner :

  • Soit « Date courte » pour que votre date s’affiche sous le format « 01/01/2013 »,
  • Soit « Date longue » si vous souhaitez la formulation complète « mercredi 01 janvier 2014 ».

Autre façon de changer les formats :

Une fois vos données sélectionnées, vous pouvez utilisez le clic droit de votre souris. Vous retrouverez alors l’accès au « Format de cellule » et aux options de « Date » où de nombreuses possibilités vous seront offertes.

Figer des lignes et des colonnes sous Excel 2007 et 2010

figer-volets-excelFiger certaines lignes et/ou colonnes dans sa feuille de calcul Excel peux s’avérer bien pratique et utile lorsque vous avez un important  tableau et que vous ne pouvez pas tout visualiser sur l’écran.

Si vous avez en effet de nombreuses lignes et colonnes et que vous ne pouvez pas tout afficher à l’écran, vous allez devoir utiliser la barre de défilement verticalement et horizontalement et dans ce cas, vous ne pourrez plus voir l’intitulé de vos lignes.

Sauf avoir une très bonne mémoire pour se souvenir que la colonne B et la colonne J correspondent à telles données, vous risquez de vite vous perdre et être obligé de remonter régulièrement au début de votre tableau pour vérifier les en-têtes.

En figeant vos en-têtes, vous pourrez librement naviguer dans votre fichier et avoir toujours à l’écran les titres de vos données.

Pour se faire, vous trouverez dans l’onglet «Affichage» de votre ruban, la commande «Figer les volets».

En cliquant dessus et en fonction de votre besoin, vous aurez le choix :

  • De figer la ligne supérieure si votre tableau est étendu en hauteur
  • De figer la première colonne si votre tableau est étendu en largeur
  • De figer les volets, c’est-à-dire les deux, et la ligne supérieure et la première colonne, si votre tableau est étendu en hauteur et en largeur.

Attention pour figer les deux, vous devez vous positionner dans la cellule qui se trouve en dessous de votre première ligne et à droite de votre première colonne puis utiliser la commande « Figer les volets ».

La navigation dans votre tableau en sera bien plus simple et plus efficace maintenant que vous ne pouvez plus quitter des yeux les en-têtes de vos données.

Bien sûr, si vous souhaitez revenir à l’affichage normal, il vous suffira de re-cliquer sur l’icône «Figer les volet», toujours dans l’onglet «Affichage», et de sélectionner la commande «Libérer les volets».

Toutes les lignes et les colonnes qui ont été figées précédemment seront donc déverrouillées.