All posts in excel-pratique

La fonction DATEDIF() permet de calculer le nombre de jours, de mois ou d’années entre deux dates :

=DATEDIF(« date début »; « date fin »; « unité »)

Où :

• date début correspond à la date de début de la période.
• date fin correspond à la date de fin de la période.
• unité correspond à l’unité retournée par la fonction.

Voici les différentes unités utilisables :

Voici exemples d’utilisation :

Cette section va vous montrer comment calculer tous les jours fériés d’une année. Voici les formules utilisées, en supposant que l’année concernée est définie dans la cellule B1 :

Le jour de l’An, la fête du travail, la Victoire de 1945, la Fête Nationale, l’Assomption, la Toussaint, l’Armistice de 1918 et le jour de Noël sont faciles à calculer car il s’agit de jours fixes dans l’année. Par exemple, la fête nationale a lieu le 14 Juillet, ou encore, Noël a lieu le 25 Décembre. La fonction DATE() est dont toute indiquée pour calculer les dates correspondantes.

La seule date difficile à calculer est Pâques. Le Lundi de Pâques, l’Ascension, Pentecôte et le Lundi de Pentecôte sont calculés à partir du jour de Pâques.

Il est parfois utile d’exécuter une macro à l’ouverture d’un classeur. Pour cela, il vous suffit d’enregistrer la macro Auto_Open.

A titre d’exemple, nous allons trier les données de la colonne A par ordre croissant à l’ouverture du classeur :

Dans un premier temps, vérifiez que l’onglet Développeur est bien affiché dans le ruban. Si ce n’est pas le cas, cliquez du bouton droit dans le ruban et sélectionnez Personnaliser le ruban dans le menu contextuel. La boîte de dialogue Options Excel s’affiche, onglet Personnaliser le ruban sélectionné. Dans la zone de liste de droite, cochez la case Développeur et validez en cliquant sur OK :

L’onglet Développeur fait maintenant partie du ruban. Basculez sur cet onglet, puis cliquez sur l’icône Enregistrer une macro dans le groupe Code. La boîte de dialogue Enregistrer une macro s’affiche. Tapez Auto_Open dans la zone de texte Nom de la macro et cliquez sur OK :

Basculez sur l’onglet Accueil du ruban. Sélectionnez la plage à trier A2:E10. Cliquez sur l’icône Trier et filtrer du groupe Edition puis sur Trier du plus petit au plus grand. Cliquez sur la cellule A1 pour désélectionner la plage.

Basculez sur l’onglet Développeur du ruban et cliquez sur l’icône Arrêter l’enregistrement du groupe Code.

Pour vérifier que la macro fonctionne, triez les données dans le sens inverse, sauvegardez le classeur au format XLSM, puis fermez-le. Rouvrez le classeur. Les données devraient être classées par années de naissance croissantes.

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)

Où 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)