Et voici un petit tour d’horizon, non exaustif, des fonctions d’Excel, pour voir ce qu’il est possible de faire. Des fonctions de texte pour les coller ou les séparer, des fonctions de dates et heures pour calculer avec, des fonctions financières…
Sommaire
Les fonctions composées avec SI
La fonction SOMME.SI
=SOMME.SI(Plage où se trouve la contition;Condition;Plage dans laquelle se trouve la donnée à additionner)
Les cellules de la plage de données à additionner sur les lignes correspondantes au critère rempli dans la plage où se trouve la condition seront additionnées. Nous allons nous servir de l’exercice en exemple.
> Exercice : Plateforme/Exercices/Tableur perfectionnement/04_sommesi_nb_si/somme_si.xls
Nous souhaitons additionner le montant perçu par les femmes et celui perçu par les hommes. Donc pour les femmes :
=SOMME.SI(Sexe; »Madame »;Montant collecté)
A vous la suite.
La fonction NB.SI
=NB.SI(plage;critère)
La fonction SIERREUR
La fonction SIERREUR teste une expression et, si cette expression aboutit à une erreur, affiche à sa place le résultat que vous voulez. Sa syntaxe sera :
=SIERREUR(valeur;valeur_si_erreur)
Si on ne veux rien afficher il suffit de mettre des guillemets vides « ».
La fonction NB.SI.ENS
La fonction NB.SI permet de dénombrer combien de fois se trouve un élément dans une liste. Mais quand vous souhaitez faire un dénombrement sur plusieurs critères, la fonction NB.SI montre ses limites.
Voilà pourquoi Microsoft à intégré depuis la version 2007 la fonction NB.SI.ENS (ENS pour ensemble). Le nombre maximal de couple critères et plages est de 127.
Sa syntaxe :
=NB.SI.ENS(plage critère 1;critère 1;plage critère 2; critère 2…)
La fonction SOMME.SI.ENS
=SOMME.SI.ENS(plage de calcul;plage critère 1;critère 1;plage critère 2; critère 2…)
Avant on pouvait utiliser les fonction BD pour cela : elle resterons d’actualité pour les moyennes min et max, mais moins utilisées car elles nécessite la création d’une plage de critère annexe. A tester il semblerait que ces nouvelles fonction tolèrent les colonnes entières contrairement aux BD.
La fonction SOMMEPROD
Les fonctions de recherche
La fonction RECHERCHEV
La fonction RECHERCHEV cherche une valeur dans la première colonne d’un tableau puis elle affiche le contenu de la cellule qui se situe sur la même ligne que la valeur recherchée. La syntaxe :
=RECHERCHEV(valeur cherchée;table matrice;numéro colonne;valeur proche)
Valeur cherchée est la valeur qui servira de référence, dans la première colonne de la table matrice. Il s’agit parfois de la clé primaire.
Table matrice est la plage que nous définirons où se trouvent les données utiles.
Numéro colonne est le numéro de la colonne où sera la donnée à afficher, en correspondance avec la ligne de la valeur cherchée.
Valeur proche doit être replie en FAUX pour que la valeur cherchée soit exact, en VRAI ou non remplie pour chercher un approximation.
Prenons pour exemple l’exercice suivant, feuille recherchev.
Nous voulons afficher le prénom d’un candidat en fonction de son numéro. Ces données se trouvent sur la feuille salaire étudiant. Nous allons choisir et reseigner manuellement la cellule de mois et le numéro du candidat.
Puis nous vous utiliser la formule Recherche V pour compléter automatiquement le reste du tableau.
Par exemple, pour nom :
=RECHERCHEV(numéro du candidat sur ce tableau;plages etudiants;numéro de la colonne nom dans la plage;FAUX)
à vous de jouer pour la suite !
> Exercice : Plateforme/Exercices/Tableur perfectionnement/08_recherchev_h/Etudiants_RechercheV.xls
La fonction RECHERCHEH
Les fonctions de date
La fonction AUJOURDHUI
Cette fonction vous permet d’afficher automatiquement la date du système dans une cellule. Sa syntaxe sera :
=AUJOURDHUI()
Il n’y a rien à écrire entre les parenthèses
Exemple : Vous avez saisi une date d’échéance dans la cellule A1 (par exemple 12/06/04). En B1 vous désirez connaître le nombre de jours restant entre cette date d’échéance et la date du jour. En B1 vous écrivez :
=A1-AUJOURDHUI()
La fonction JOUR
Cette fonction vous permet d’extraire le numéro du jour à partir d’une date saisie dans une cellule (ou dans la formule elle même). Le numéro extrait peut ainsi être utilisé pour effectuer des calculs ou des test dans le cadre d’une fonction logique. Cette fonction prend tout son intérêt dans le cadre de la fonction =DATE() décrite ci-après. Sa syntaxe sera :
=JOUR()
Exemple : Vous avez saisi une date de paiement dans la cellule A1 (par exemple 12/06/04). En B1 vous désirez connaître le jour où la facture devra être payée compte tenu d’un délai de paiement de 8 jours. En B1 vous écrivez :
=JOUR(A1)+8
Le résultat est : 23/06/04 (ou 23 si vous modifiez le format des nombres).
La fonction MOIS
Cette fonction vous permet d’extraire le numéro du mois à partir d’une date saisie dans une cellule de la même façon que JOUR. Sa syntaxe sera :
=MOIS()
La fonction ANNEE
=ANNEE()
La fonction JOURSEM
=JOURSEM(date;codification des jours)
1 : dimanche = 1 et samedi = 7 ou
2 : lundi = 1 et dimanche = 7 ou
3 : lundi = 0 et dimanche = 1
=JOURSEM(A1;2)
Le résultat est : 3 (ou mercredi si vous modifiez le format des nombres)
La fonction NO.SEMAINE
=NO.SEMAINE(date;codification des jours)
La codification des jours est :
1 : dimanche est le premier jour de la semaine ou
2 : lundi est le premier jour de la semaine
Exemple : Vous avez saisi une date de paiement dans la cellule A1 (par exemple 9/06/07). En B1 vous désirez connaître le numéro de la semaine correspondant. En B1 vous écrivez :
=JOURSEM(A1;2)
Le résultat est : 23.
La fonction DATE
Exemple : Vous avez saisi une date de paiement dans la cellule A1 (par exemple 12/06/04). En B1 vous désirez connaître le jour où la facture devra être payée compte tenu d’un délai de paiement à 30 jours. En B1 vous écrivez :
=DATE(ANNEE(A1);MOIS(A1)+1;JOUR(A1))
Le résultat est : 12/07/04
Exemple 2 : Vous voulez calculer un amortissement et vous avez besoin d’obtenir automatiquement la date du dernier jour de l’exercice comptable. Vous avez saisi la date d’achat de matériel dans la cellule A1 (par exemple 15/06/04). En B1 vous désirez obtenir da date de fin d’exercice (à savoir le 31 décembre de l’année). En B1 vous écrivez :
=DATE(ANNEE(A1);12;31)
Le résultat est : 31/12/04
La fonction DATEDIF
La fonction DATEDIF est intégrée depuis Excel 2007. Elle ne fonctionne pas dans Openoffice. Elle sert à faire la différence entre deux dates. Sa syntaxe :
=DATEDIF(date ancienne;date récente; »code »)
Le code peut être :
D comme day pour connaître le nombre de jours entre les 2 dates
M comme month pour connaître le nombre de mois entre les 2 dates
Y comme year pour connaître le nombre d’années entre les 2 dates
Je vous recommande vivement de faire référence externes aux dates au lieu de les taper dans la formule ce qui pose souvent des soucis de reconnaissance de format.
Les fonctions de texte et Flash fill
La fonction STXT
La fonction STXT sert à extraire des caractères. Sa syntaxe :
=STXT(texte;départ;nb lettres)
Le texte est à mettre entre guillemet ou on pet faire référence à une cellule.
Départ est le numéro de la lettre à partir duquel les caractères sont extraits.
Nb lettre est le nombre de lettres extraites.
Sa limite est bien sur que la position des lettre à extraire doit toujours être la même.
La fonction GAUCHE
La fonction GAUCHE sert à extraire un certain nombre de caractères depuis la gauche. Sa syntaxe :
=GAUCHE(texte;nb lettre)
Nb de lettre est facultatif : si vous ne mettez rien il ne prendra qu’un caractère. Très pratique pour extraire un numéro de département d’un code postal ou un indicateur téléphonique pour faire un filtre.
La fonction DROITE
La fonction DROITE sert à extraire un certain nombre de caractères depuis la droite. Sa syntaxe :
=DROITE(texte;nb lettre)
Nb de lettre est également facultatif. Par exemple pour les terminaisons des verbes ou des conjugaisons.
La fonction CONCATENER
La fonction CONCATENER sert à réunir plusieurs textes. Sa syntaxe :
=CONCATENER(texte1;texte2)
On peut ajouter jusqu’à 255 textes entres guillemets ou références séparés par des points vigules.
Si on veut insérer un espace il suffit de créer un argument » « . Idem avec le point ou le tiret.
La fonction INDIRECT
Les fonctions financières
VC
La fonction VC calcule la valeur capitalisée, c’est-à-dire un montant payé ou reçu à un nombre exact de périodes à partir d’un moment donnée. La syntaxe :
taux Taux d’intérêt (annuel)
npm Nombre total des versements
vpm Montant du remboursement de chaque période (ex. mensualités)
va Valeur actuelle (montant du placement en négatif ou montant d’un prêt en positif)
type Valeur facultative pour la date d’échéance des paiements (début ou fin de mois). Les emprunts sont généralement remboursable fin de mois. Certains crédits hypothécaires sont remboursables début de mois. Au début de la période = 1. A la fin de la période = 0.
Exemple
TAUX
La fonction TAUX calcule le taux d’intérêt annuel par rapport à un placement ou un prêt. Il est calculé pour l’année afin de calculer un taux d’intérêt trimestriel vous devez diviser le taux par 4 (ex: 5%/4). Sa syntaxe :
npm Nombre total des versements
va Valeur actuelle (montant du placement ou montant d’un prêt)
vc Valeur capitalisée, c’est à dire la valeur future (si omise la valeur est = 0)
type Valeur facultative pour la date d’échéance des paiements. Au début de la période = 1. A la fin de la période = 0.
estimation Valeur facultative estimé du taux (si omise =0, ex 0,1 = 10%)
Exemple
NPM
La fonction NPM calcule le nombre de paiements d’un investissement ou prêt à versements réguliers et taux d’intérêts constants. Cette fonction permet donc aussi de calculer combien d’années il faut pour atteindre un certain montant à partir d’un capital de départ. Sa syntaxe :
taux Taux d’intérêt (annuel)
vpm Montant du remboursement de chaque période (ex. mensualités)
va Valeur actuelle (montant du placement ou montant d’un prêt)
vc Valeur capitalisée, c’est à dire la valeur future (si omise la valeur est = 0)
type Valeur facultative pour la date d’échéance des paiements. Au début de la période = 1. A la fin de la période = 0.
Exemple
Dans notre exemple l’argument vpm = 0 (pas de paiements réguliers), et le va est négative car il s’agit d’un placement.
VA
La fonction VA calcule la valeur actuelle, c’est-à-dire un montant payé ou reçu désigné comme montant principal. Sa syntaxe :
taux Taux d’intérêt (annuel)
npm Nombre total des versements
vpm Montant du remboursement de chaque période (ex. mensualités)
vc Valeur capitalisé ou valeur future (facultative) le montant qu’on aimerait atteindre à la fin de la période
type Valeur facultative pour la date d’échéance des paiements. Au début de la période = 1. A la fin de la période = 0.
Exemple
VPM
La fonction VPM calcule le montants pour chaque échéance de paiement (ex mensualités). Sa syntaxe :
taux Taux d’intérêt (annuel)
npm Montant du remboursement de chaque période (ex. mensualités)
va Valeur actuelle (montant du placement ou montant d’un prêt)
vc Valeur capitalisée facultative, c’est à dire la valeur future (si omise la valeur est = 0)
type Valeur facultative pour la date d’échéance des paiements. Au début de la période = 1. A la fin de la période = 0.
Exemple
Vu qu’il s’agit de remboursements mensuel le taux à été divisé par 12.
INTPER
La fonction INTPER calcule les intérêts par période d’un investissement ou d’un prêt sur la base de remboursements réguliers et fixes et un taux d’intérêts constant. Sa syntaxe :
taux Taux d’intérêt (annuel)
période Période pour laquelle on veux calculer les intérêts.
npm Montant du remboursement de chaque période (ex. mensualités)
va Valeur actuelle (montant du placement ou montant d’un prêt)
vc Valeur capitalisée facultative, c’est à dire la valeur future (si omise la valeur est = 0)
type Valeur facultative pour la date d’échéance des paiements. Au début de la période = 1. A la fin de la période = 0.
Exemple
Dans cet exemple nous avons calculé le montant des intérêts pour la première période, notez que le taux à été divisé par 12 pour tenir compte du remboursement mensuel.
PRINCPER
La fonction PRINCPER calcule le capital remboursé à chaque période pour un prêt à remboursement et à taux constants. Sa syntaxe :
taux Taux d’intérêt (annuel)
période Période pour laquelle on veux calculer les intérêts.
npm Montant du remboursement de chaque période (ex. mensualités)
va Valeur actuelle (montant du placement ou montant d’un prêt)
vc Valeur capitalisée facultative, c’est à dire la valeur future (si omise la valeur est = 0)
Exemple
Dans cet exemple nous avons calculé le montant du capital remboursé pour la première période, notez que le taux à été divisé par 12 pour tenir compte du remboursement mensuel.
Conclusion
Tableur, le sujet est vaste. La suite sera les macro, mais cela sera l’objet d’un autre cours ! À bientôt !
