Médiaforma

All posts in excel-pratique

Le taux des devises change en permanence. Si vous utilisez une formule statique, comme pour la conversion de Fahrenheit en degrés Celsius, le résultat sera forcément erroné, à moins que vous ne mettiez à jour les taux au jour le jour. Cette section va vous montrer comment utiliser un site Web pour avoir des taux toujours à jour. Ici, nous convertirons des euros en dollars.

Allez sur le site https://www.boursorama.com/bourse/devises/convertisseur-devises/ (ou un autre site de conversion de devises), puis copiez l’URL de cette page dans le presse-papiers :

Basculez sur l’onglet Données. Dans le groupe Récupérer et transformer, cliquez sur Nouvelle requête, pointez A partir d’autres sources, puis cliquez sur A partir du Web. Entrez l’adresse https://www.boursorama.com/bourse/devises/convertisseur-devises/ et validez en cliquant sur OK :

Si une boîte de dialogue vous invite à vous connecter, cliquez sur Se connecter. Quelques instants plus tard, lorsque la connexion avec le serveur a été établie, la liste des tables de conversion du site est accessible :

Cliquez sur Table 1 (si vous choisissez un autre site Web ou si vous convertissez d’autres devises, les taux de changes se trouveront certainement dans une autre table) :

Cliquez sur Charger. Quelques instants plus tard, la table de conversion est chargée dans une nouvelle feuille de votre classeur :

Pour convertir en dollars un montant exprimé en euros, vous allez vous servir du taux de change de la cellule B2. Ici, le tableau de conversion a été stocké dans la feuille Feuil3.

Si vous essayez d’utiliser la formule =A5*Feuil3!B2, vous obtenez une erreur :

En effet, la cellule B2 de la feuille Feuil3 contient la chaîne 1.14165 USD. Impossible de multiplier cette chaîne par une valeur numérique !

Vous devez supprimer le suffixe USD et remplacer le point décimal par une virgule. Pour cela, vous utiliserez cette formule :

=A5*SUBSTITUE(GAUCHE(Feuil3!B2;CHERCHE(" ";Feuil3!B2)-1);".";",")

La fonction GAUCHE() retourne la partie numérique de la cellule B2 de la feuille Feuil3. Cette fonction demande deux paramètres :

  • La cellule sur laquelle travailler. Ici, la cellule B2 de la feuille Feuil3.
  • Le nombre de caractères à extraire. Ici, on utilise la fonction CHERCHE() pour trouver la position de l’espace dans la cellule B2. En enlevant 1, on obtient le nombre de caractères à extraire.

La fonction SUBSTITUE() remplace le caractère “.” par le caractère “,“.

Le résultat est bien celui qui était attendu :

Pour que le taux de change soit mis à jour automatiquement, sélectionnez la feuille qui contient le tableau de change, basculez sur l’onglet Données, puis cliquez sur l’icône Propriétés dans le groupe Connexions. La boîte de dialogue Propriétés des données externes s’affiche. Cliquez sur l’icône Paramètres de connexion :

La boîte de dialogue Propriétés de connexion s’affiche. Cochez les cases Actualiser toutes les et/ou Actualiser les données lors de l’ouverture du fichier pour déterminer le mode d’actualisation des données :

Refermez toutes les boîte de dialogue ouvertes. Avec ce réglage, le taux de change sera automatiquement mis à jour à l’ouverture du classeur et toutes les 60 minutes.


Avec Excel, il est très simple de convertir des unités de mesure en utilisant la fonction CONVERT(). A titre d’exemple, nous allons convertir des Fahrenheit en Degrés et des Degrés en Fahrenheit.

Cliquez sur la cellule où doit se faire la conversion. Tapez =CONVERT(. Désignez :

  • La cellule à convertir.
  • L’unité d’origine en choisissant une entrée dans la liste ou en la saisissant directement entre guillemets.
  • L’unité finale en choisissant une entrée dans la liste ou en la saisissant directement entre guillemets.

Fermez la parenthèse et appuyez sur la touche Entrée du clavier pour obtenir le résultat :

La conversion inverse ne pose aucun problème. Cliquez dans la cellule où doit se faire la conversion. Tapez =CONVERT(. Désignez :

  • La cellule à convertir.
  • L’unité d’origine en choisissant une entrée dans la liste ou en la saisissant directement entre guillemets.
  • L’unité finale en choisissant une entrée dans la liste ou en la saisissant directement entre guillemets.

Fermez la parenthèse et appuyez sur la touche Entrée du clavier pour obtenir le résultat :


Cette section va vous montrer comment simuler le remboursement d’un emprunt immobilier sur la base de remboursements et d’un taux d’intérêt constants. Trois scénarios seront analysés, dans lesquels vous pourrez choisir le taux d’emprunt, le nombre de mensualités et le montant à rembourser.

Nous allons partir d’un emprunt à 1% et créer des scénarios pour le cas le moins favorable, le plus favorable et le plus probable. Voici les données de départ :

La cellule G6 calcule les mensualités du remboursement à l’aide de la fonction VPN() :

=VPN(taux; npm; va)

Où :

  • taux est le taux d’intérêt de l’emprunt.
  • npm est le nombre de remboursements de l’emprunt.
  • va est le montant à rembourser.

Sélectionnez la plage de cellules qui doit être ajustée : G3:G5.

Basculer sur l’onglet Données du ruban. Cliquer sur l’icône Analyse scénarios dans le groupe Prévision et sélectionnez Gestionnaire de scénarios. La boîte de dialogue Gestionnaire de scénarios s’affiche.

Cliquez sur Ajouter, définissez le scénario Meilleur :

Cliquez sur OK et définissez le meilleur taux de crédit, le nombre de mensualités et le montant à rembourser. Ici, le taux est de 0,9%, le nombre de mensualités est égal à 120 et le montant de l’emprunt est égal à 180 000 € :

Validez en cliquant sur OK. De retour dans la boîte de dialogue Gestionnaire de scénarios, définissez un nouveau scénario. Appelez-le Pire. Choisisse le taux, le nombre de mensualités et le montant à rembourser. Ici, le taux est de 1,2%, le nombre de mensualités est égal à 100 et le montant de l’emprunt est égal à 180 000 €:

Validez en cliquant sur OK. De retour dans la boîte de dialogue Gestionnaire de scénarios, définissez un nouveau scénario. Appelez-le Probable. Choisissez le taux, le nombre de mensualités et le montant à rembourser. Ici, le taux est de 1,0%, le nombre de mensualités est égal à 120 et le montant de l’emprunt est égal à 180 000 € :

Validez en cliquant sur OK. Vous pouvez choisir un des scénarios et cliquer sur Afficher pour afficher la simulation du remboursement dans la feuille de calcul :

Vous pouvez également cliquer sur Synthèse. Indiquez si nécessaire la cellule qui contient le montant à retenir dans la synthèse. Ici, la cellule G7 :

Cliquez sur OK pour afficher la synthèse :

Vous voudriez également faire apparaître le taux mensuel des remboursements dans la synthèse ? Affichez à nouveau la boîte de dialogue Gestionnaire des scénarios, puis cliquez sur Synthèse. La boîte de dialogue Synthèse de scénarios s’affiche. Supprimez le contenu de la zone de texte Cellules résultantes, maintenez la touche Contrôle du clavier enfoncée puis cliquez successivement sur les cellules G6 et G7 :

Cliquez enfin sur OK pour afficher la synthèse :

Cette fois-ci, vous avez tous les éléments nécessaires pour avoir une idée précise de ce qui vous attend dans les années à venir…


La fonction NB.SI() permet de trouver le nombre de cellules qui répondent à un critère. Voici sa syntaxe :

=NB.SI(plage; critère)

plage est la plage de cellules examinée et critère est le critère de sélection.

Le critère peut être un nombre, une comparaison numérique, une chaîne sans ou avec caractères génériques ou encore la référence à une cellule.

Pour bien comprendre comment utiliser cette fonction, nous allons raisonner sur plusieurs exemples.

Recherche d’un nombre

Pour compter le nombre de cellules de la plage C5:E15 égales à 33 dans la cellule I5, vous utiliserez cette formule :

=NB.SI(C5:E15;33)

Comparaison numérique

Pour compter le nombre de cellules de la plage C5:E15 inférieures à 100 dans la cellule I6, vous utiliserez cette formule :

=NB.SI(C5:E15;"<100")

Chaîne sans caractères générique

Pour compter le nombre de cellules de la plage B5:B15 qui contiennent la chaîne Elise, vous utiliserez cette formule :

=NB.SI(B5:B15;"Elise")

Chaine avec caractères génériques

Pour compter le nombre de cellules de la plage B5:B15 qui commencent par la lettre P, vous utiliserez cette formule :

=NB.SI(B5:B15;"P*")

Référence à une cellule

Enfin, pour compter le nombre de cellules de la plage C5:E15 qui contiennent la valeur de la cellule H3, vous utiliserez cette formule :

=NB.SI(C5:E15;H3)

Le format personnalisé utilisé dans la section précédente ajoute un espace et le mot ventes à chaque cellule. Supposons qu’une cellule contienne la valeur 0 ou 1. Il faudrait ajouter le mot vente au singulier et non au pluriel. Pour cela, vous utiliserez une formule légèrement plus complexe :

[<2]Standard" vente"; Standard" ventes"

Si la valeur est inférieure à 2, le mot vente précédé d’un espace est ajouté à la cellule. Dans le cas contraire, le mot ventes précédé d’un espace est ajouté à la cellule :


Il est très simple d’ajouter une unité dans une ou plusieurs cellules. Commencez par sélectionner les cellules concernées. Basculez sur l’onglet Accueil du ruban, puis cliquez sur l’icône Format de nombre dans le groupe Nombre. Vous pouvez utiliser le raccourci Contrôle + Majuscule + 1 pour parvenir au même résultat. La boîte de dialogue Format de cellule s’affiche.

Sous l’onglet Nombre, sélectionnez Personnalisé dans la zone de liste Catégorie et ajoutez l’unité dans la zone de texte Type en vous aidant de la zone de prévisualisation :

Voici le résultat :


Cet article va vous montrer comment extraire des données dans une plage en fonction de la valeur d’une cellule. Pour cela, vous utiliserez les fonctions INDEX() et EQUIV().

La fonction INDEX()

La fonction INDEX() retourne la valeur d’une cellule dans une plage en fonction de ses coordonnées ligne et colonne. Voici sa syntaxe :

=INDEX(plage; ligne; colonne)

Où :

  • plage est la plage de cellules concernée.
  • ligne et colonne sont la ligne et la colonne de la cellule dont on veut connaître la valeur.

Voici un exemple d’utilisation :

La formule utilisée pour remplir la cellule C12 est :

=INDEX(A1:E10;4;5)

La plage de cellules examinée est A1:E10. La valeur à l’intersection de la quatrième ligne et de la cinquième colonne dans cette plage est 827 952.

Remarque

  • Si la plage contient une seule ligne ou une seule colonne, la ligne ou la colonne est facultative.
  • Si la plage comporte plusieurs lignes et plusieurs colonnes et que la ligne ou la colonne est omise, la fonction INDEX() retourne une plage qui correspond à la ligne ou à la colonne de la plage.

La fonction EQUIV()

La fonction EQUIV() retourne la position d’une valeur dans une plage de cellules. Voici sa syntaxe :

=EQUIV(recherche; plage; type de recherche)

Où :

  • recherche est la valeur recherchée.
  • plage est la plage de cellules examinée.
  • type de recherche peut prendre l’une des valeurs suivantes :
    • 0 pour rechercher la première correspondance exacte. Si aucune correspondance exacte n’est trouvée, la position de la valeur supérieure la plus proche est retournée.
    • 1 pour rechercher la valeur inférieure ou égale la plus proche de recherche. Les valeurs de la plage doivent être placées en ordre croissant, sans quoi cette fonction produira une erreur.
    • -1 pour rechercher la valeur supérieure ou égale la plus proche de recherche. Les valeurs de la plage doivent être placées en ordre décroissant, sans quoi cette fonction produira une erreur.

Voici un exemple d’utilisation :

La formule utilisée pour remplir la cellule D12 est :

=EQUIV(2014;A1:A10;0)

La valeur 2014 est recherchée de façon exacte (0 en troisième paramètre) dans la plage A1:A10. La septième cellule contient la valeur recherchée. C’est donc le résultat retourné par la fonction EQUIV().