Archives de catégorie : Excel Niveau Avancé

La valeur cible avec Excel 2007

La fonction valeur cible a pour utilité de rechercher une valeur par rapport à un objectif défini: Voici comment l’utiliser.

La valeur cible sur Excel est un système de calcul qui permet de partir d’un résultat pour obtenir l’une des parties de notre calcul. Bien pratique pour des crédits ou des soldes par exemple : Voici une exemple de la vie pratique qui vous permettra de bien comprendre la notion de valeur cible.

Imaginons que nous devions changer de canapé. Nous souhaitons l’acquérir à crédit et nous nous sommes fixés au départ un budget de 1800 euros.

Nous souhaitons un remboursement sur un an en 12 mensualités et le magasin où nous souhaitons l’acheter propose un taux de remboursement de 3,5 %.

Nous avons donc toutes les informations nécessaires pour saisir nos données.

Image1

Dans un premier temps, je dois calculer le montant de mon remboursement et pour cela, je vais utiliser une fonction financière, nommée VPM qui me permettra ce calcul.

Profitons-en pour voir cette formule en détail :

Image2

Mon premier argument sera mon taux d’intérêt ramené à ma périodicité ; Dans notre exemple, 3,5%/12

Mon second argument sera le nombre total de remboursement soit dans notre exemple, 12 (ma périodicité)*1 (ma durée)

Mon troisième argument sera le prix de mon canapé.

Attention, pour obtenir un résultat positif, je dois mettre un devant ; C’est une question de somme algébrique. Ce qui donnera dans mon exemple, -1800

Ce qui me donne la formule suivante (sans oublier de séparer chacun de mes arguments par des ; )

Image3

Je valide et j’obtiens le montant de mon remboursement pour chaque période, soit mensuellement comme j’ai choisi un remboursement sur 12 mois :

Image4

Je dois donc rembourser 152,86 euros par mois pour cet achat.

Le problème est que j’avais prévu dans mon budget de ne rembourser que 120 euros par mois au maximum.

La question que je me pose donc est à quel prix dois-je acheter mon canapé pour avoir un remboursement égal ou inférieur à 120 euros par mois dans ces mêmes conditions ?

Pour cela, nous allons donc utiliser la valeur cible et pour trouver cette fonction, je vais aller dans l’onglet Données où je vais trouver l’icône Analyse de Scénarios qui se trouve dans le groupe Outils de données.

Image5

Je clique dessus pour ouvrir les différentes options et je découvre la fonction Valeur cible

Image6

Je clique sur Valeur cible et une nouvelle boite de dialogue fait son apparition

Image7

Nous avons 3 paramètres à renseigner :

  • La cellule à définir, c’est-à-dire l’objectif que je me suis défini.

Comme j’étais positionné dessus, Excel m’indique bien la cellule B7 soit le montant de mon remboursement.

Attention, cette cellule doit obligatoirement comporter une formule au sens large du terme. Elle peut contenir aussi bien une fonction d’Excel qu’une formule simple comme une valeur multipliée par une autre.

 

  • La valeur à atteindre, c’est-à-dire l’objectif que je me suis fixée. Je ne souhaite pas un remboursement supérieur à 120 euros et je vais donc entrer cette valeur.

 

  • La cellule à définir, c’est-à-direla cellule à modifier en fonction de mon objectif ; Dans mon exemple, ce sera la cellule B2 soit le prix de mon canapé.

 

Ce qui nous donne :

Image8

Je valide en cliquant sur Ok et Excel m’indique qu’il a trouvé un résultat.

Image9

Je valide à nouveau et je sais maintenant que je ne peux pas dépasser 1 413,07 euros pour l’achat de mon canapé en ayant les mêmes conditions.

Image10

Dans mon exemple, ce qui m’intéressait c’était de connaître le prix d’achat maximum de mon canapé en ayant un remboursement de 120 euros sur 1 an en 12 mensualités avec un taux d’intérêt de 3,5 % mais j’aurai pu également garder mon prix d’achat initial et avec les mêmes conditions, savoir sur quelle durée, je devrais emprunter.

Dans ce cas, la cellule à renseigner aurait été B3, soit ma durée et je peux ainsi rapidement savoir que pour un achat de 1800 euros avec un remboursement de 120 euros par mois, mon remboursement serait de 1 an et 3 mois.

Image11

Image12

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

Fonction recherchev d’Excel 2003 – 2013

La fonction recherchev fonctionne sur toutes les versions d’Excel. Pour voir comment elle fonction, voici une liste de cinq étudiants avec leurs notes globales. L’objectif de cet exercice est d’utiliser la fonction Recherchev afin qu’Excel leurs attribue automatiquement un grade suivant d’un commentaire.

fonction recherchevDans le premier tableau, se trouve la liste des étudiants (A2 à A6) avec leur notes (B2 à B6). C’est dans les colonnes C et D que figureront respectivement les Grades et les Commentaires.

Dans le second tableau, se trouve la liste des grades et la liste des commentaires qui s’afficheront dans les colonnes C et D du premier tableau.

Important : Dans le deuxième tableau, il est impératif que les valeurs de la colonne A soient dans l’ordre croissant.

Pour commencer, allez dans la cellule C2, puis tapez la formule suivante :
=recherchev(B2;A9:C13;2)

Appuyez sur Entrer pour valider.

La fonction recherchev à besoin de trois arguments pour fonctionner :

  • B2 correspond à la valeur cherchée. C’est cette valeur qui va permettre à Excel de déterminer le grade.
  • A9:C13, correspond à la plage de valeur de comparaison.
  • Le chiffre 2, fait référence à la colonne B du deuxième tableau. En effet, dans la plage A9:C13, la colone 2 correspond bien aux valeurs B9 à B13.

Pour obtenir les commentaires le principe est le même. La formule sera la suivante :
=recherchev(B2;A9:C13:3)

Ici, seul la dernière valeur a été changé. Pour les commentaires, les valeurs se trouvent dans la colonne numéro 3, autrement dit de C9 à C13).

Pour les étudiant de 2 à 5 il serait intéressant de tirer les cellules en utilisant la poignée de recopie. Toute fois il y a un piège. En faisant cela, la valeur A9:C13 se voie remplacé par A10:C13, ce qui décale la plage de valeur d’une ligne.

Pour remédier à ce problème, il va falloir modifier les formules afin de figer ces valeurs.
Allez sur la cellule C2, puis ajoutez-y le symbole dollar comme dans l’exemple ci-dessous :
=recherchev(B2;A$9:C$13;2)

Faites de même avec la formule se trouvant dans la cellule D2
=recherchev(B2;A$9:C$13;3)

Il est à présent possible d’utiliser la poignée de recopie pour les étudiants 2 à 5

fonction recherchev

Comment mettre en forme un fichier CSV sous Excel 2010

Pour mettre en forme un fichier CSV sous forme de tableau, Excel possède les outils adéquates.
Pour rappel les fichiers CSV (Comma-separated values) est un format de fichier ouvert représentant des données tabulaires sous forme de valeurs séparées par des virgules. Ce format est très largement employé par les logiciels pour l’exportation de leur base de données.

Les fichiers CSV peuvent être ouverts avec Excel. Toutes fois leur présentation n’est pas des plus lisibles, comme l’illustre la capture d’écran ci-dessous.

fichier csv

Les données sont présentées sur plusieurs lignes mais sur une seule colonne. La colonne A en l’occurrence. Heureusement il n’est pas exclu ne remédier à ce problème, et de scinder la colonne A avec autant de colonne que nécessaire.

Sélectionnez la colonne A. Les fichiers CSV étant souvent très gros, il est conseillé de cliquer directement sur la lettre A. Ainsi, l’intégralité de la colonne (de A1 jusqu’à l’infinie) sera sélectionné.

Dans l’onglet Données cliquez sur Convertir. La boite de dialogue suivante apparait :

fichier csvDans Type de données d’origine sélectionner : Délimité, puis Suivant >.

fichier csv

Dans cet exemple, les valeurs sont séparées par des virgules. Ici, dans la partie Séparateurs, Tabulation sera décoché au profit de Virgule. Il est possible que votre fichier ne soit pas séparé par des virgules, voir même séparé par plusieurs séparateurs. Faites les sélections en fonction de la situation. La partie Aperçu de données propose un aperçu en temps réel en fonction des paramètres activé. Cliquez sur Suivant > pour passer à l’étape suivante.

fichier csv

Troisième est dernière étape. Il est possible de formater toutes les cellules du tableau (Format des données en colonnes), de placer le tableau à un endroit bien précis dans le classeur Excel (Destination), avec comme précédemment un Aperçu de données. Cliquez sur Terminer pour valider les paramètres.
A présent les valeurs sont présentées sous forme de tableau. Sa mise en forme est encore un peu brute, mais les colonnes sont bien délimitées et tous les outils de mise en forme sont disponibles.

Gestion des thèmes sous Excel 2007 et 2010

Lorsque l’on créé un document sous Excel, que ce soit des tableaux ou des graphiques, il est conseillé de commencer par créer le document dans sa globalité avant de ce soucier de son apparence. Bien entendu l’apparence joue un rôle capital dans la lisibilité du document. Le choix des couleurs dépend de la pertinence de l’information mais aussi et surtout des goûts de l’auteur.

C’est pourquoi, certaines entreprises, et plus particulièrement les multinationales impose un code couleur, afin contenir l’imagination d’débordante de ses employés mais aussi et surtout pour garder une certaine continuité dans la mise en forme des documents de l’entreprise.

Excel ne manque d’outils pour personnaliser de façon spontané ses documents, et ainsi répondre à une charte graphique précise. Mais qu’en est-il lorsqu’il faut répéter cette charte graphique fréquemment ? Comment peut-on créer un thème graphique complet sous Excel ? Est-il possible de l’exporter sur d’autres ordinateurs ?

Autant de questions qui vont être le centre d’intérêt de cet article.

Dans l’élaboration d’un thème trois paramètres peuvent être pris en compte : Les couleurs, les polices et les effets. Les réglages figurent dans l’onglet Mise en page.

  1. Pour personnaliser les couleurs, cliquez sur le bouton Couleurs, puis sur Nouvelles couleurs du thème… La boîte de dialogue ci-dessous s’affiche.
    Les modifications se font dans la partie de gauche Couleur du thème. Cliquez sur les boutons de votre choix pour faire apparaitre le nuancier et ainsi remplacer la couleur par défaut, par une couleur personnalisée. Une fois les modifications terminé, cliquez sur le bouton Enregistrer.
  2. Pour personnaliser les Polices, la procédure est la même que pour les couleurs. Cliquez sur Polices, puis sur Nouvelles polices du thème… Là encore une boîte de dialogue s’affiche pour effectuer les réglages.
  3. Pour les effets, il n’est pas possible de les personnaliser. Choisissez un effet dans la liste déroulante. Cet effet sera appliqué aux formes personnalisé et certaines parties des graphiques.

Votre thème est à présent terminé, et donc utilisable. L’ensemble des réglages on était sauvegardé dans trois fichiers distincts. Pour s’en assurer il suffit de se rendre dans le dossier suivant:

C:\Users\votre_nom_d’utilisateur\AppData\Roaming\Microsoft\Templates\Document Themes

Le moins que l’on puisse dire, c’est que le chemin vers ces fichiers n’est pas simple. D’autant plus que le dossier AppData est un dossier caché, ce qui ne rend vraiment pas les choses faciles, surtout si l’on envisage de partager le thème avec d’autres personnes.

Pour remédier à ce problème, retournez dans Excel, toujours dans l’onglet Mise en page, cliquez sur le bouton Thèmes, puis sur Enregistrer le thème actif… Votre thème sera enregistré dans un seul est unique fichier avec l’extension .thmx. Indiquez le dossier dans lequel le fichier. thmx devra être enregistrer puis donnez-lui un nom.

Votre thème est à présent créé et sauvegardé, il est à présent possible de l’importer sur d’autres ordinateurs (équipés d’Excel bien sûr). Sur l’ordinateur hôte, importer le fichier. thmx, par le biais d’une clé USB par exemple, puis ouvrez Excel. Dans l’onglet Mise en page, bouton Thème, allez cette fois-ci sur Rechercher les thèmes… et sélectionnez votre fichier.thmx

Dorénavant votre thème sera toujours disponible dans la liste des thèmes disponible (Mise en page, Thèmes).