Tableur 6 : fonctions à foison

Tableur 6 : fonctions à foison

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…

tableur

Les fonctions composées avec SI

La fonction SOMME.SI

La fonction SOMME.SI permet d’effectuer une somme en tenant compte d’un critère. La syntaxe est :
=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

Elle compte le nombre de cellules à l’intérieur d’une plage qui répondent à un critère donné. La syntaxe :

=NB.SI(plage;critère)

Prenons l’exercice suivant : pour celui ci nous compterons le nombre de type de fruit en provenance de France. Il faudra donc appliquer :
=NB.SI(pays d’origine;France)
Nous pouvons aussi calculer le stock disponible avec la fonction SOMME.SI. Faisons de même pour la Suisse.
> Exercice : Plateforme/Exercices/Tableur perfectionnement/04_sommesi_nb_si/Somme.SI_Nb.Si.xls

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

De la même façon, SOMME.SI.ENS additionne les résultats alphanumériques d’une plage en fonction de plusieurs critères. Sa syntaxe :

=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

La fonction SOMMEPROD sert à synthétiser plusieurs autres. Elle permet entre autre de proposer des OU et des ET logiques entre les critères en une seule fonction. Sa syntaxe :
=SOMMEPROD((plage1=critère1)+(plage2=critère2)*(plage3=critère3);(plage de somme))
On peut ajouter de nombreuses paires plages et critères, les paires séparées par * sont en ET logique et les paires séparées par + sont en OU logique.
Exemple :
Pour faire connaître la somme des quantités d’oranges en europe ou de pomme
ORANGES ET EUROPE OU POMMES
Avec les fonctions si :
=SOMME.SI.ENS(plage de quantité;plage de fruits; »Oranges »;plage de continent; »Europe »)+SOMME.SI(plage de fruits; »Pommes »;plage de quantité)
Avec la fonction SOMMEPROD
=SOMMEPROD((plage de fruits= »Oranges »)*(plage de continent= »Europe »)+(plage de fruits= »Pommes »);plage de quantité)

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

La fonction RECHERCHEH fonctionne exactement de la même façon mais lorsque votre tableau est à l’envers (ce que je vous déconseille). Les champs (types de données) se trouvent sur les lignes et les éléments (individu ou produits) se trouvent sur les colonnes. Aussi au lieu du numéro de colonne ce sera le numéro de ligne cette fois.

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

De la même façon que les deux précédentes, cette fonction vous permet d’extraire le numéro de l’année à partir d’une date saisie dans une cellule. Sa syntaxe sera :

=ANNEE()

La fonction JOURSEM

Cette fonction vous permet de connaître le numéro du jour dans la semaine à partir d’une date saisie dans une cellule. Sa syntaxe sera :

=JOURSEM(date;codification des jours)

La codification des jours est :

1 : dimanche = 1 et samedi = 7 ou
2 : lundi = 1 et dimanche = 7 ou
3 : lundi = 0 et dimanche = 1

Exemple : Vous avez saisi une date de paiement dans la cellule A1 (par exemple 9/06/04). En B1 vous désirez connaître le jour de la semaine correspondant. En B1 vous écrivez :

=JOURSEM(A1;2)

Le résultat est : 3 (ou mercredi si vous modifiez le format des nombres)

La fonction NO.SEMAINE

Cette fonction vous permet de connaître le numéro de la semaine à partir d’une date saisie dans une cellule. Sa syntaxe sera :

=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

Cette fonction vous permet de calculer une date à partir d’une autre et est intéressante à combiner avec les précédentes. Sa syntaxe sera :
=DATE(annee;mois;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 à 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

Voici les fonctions natives de date dans Excel. Certaines autres sont disponible en activant des macros, comme DATEDIF ou NB.JOURS.OUVRES, selon les versions.

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

La fonction INDIRECT vous permet de concevoir des liaisons entre les feuilles d’un classeur en réutilisant des données présentes. Sa syntaxe :
=INDIRECT(cellule)
Exemple
Si nous voulons récupérer le niveau de TVA de différents pays listés en colonne A dont les noms sont les mêmes que celui des feuilles, nous aurions jusque là pointé la cellule sur chaque feuille, manuellement.
La fonction INDIRECT peut remplacer le nom de la feuille par le contenu de la colonne A de la façon suivante :
=INDIRECT(A2& »!B7″)
Entre guillemet se trouve ce qui ne varie pas. Le symbole & permet de relier des éléments distincts dans une formule pour n’en faire plus qu’un seul. Attention si le nom de la feuille contient un espace il sera entouré d’apostrophes dans la référence manuelle comme dans :
=’Etats unis’!B7
Alors ces apostrophes devront être entourée elles aussi de guillemets :
=INDIRECT(« ‘ »&A2& »‘!B7 »)

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 :

=VC(taux;npm;vpm;va;type)

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

Quel sera le montant d’un placement de 10000 euros sur 4 ans à un taux fixe de 2.5% par an?

Va est ici négatif car il s’agit d’un placement.

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 :

=TAUX(npm;vpm;va;vc;type;estimation)

npm Nombre total des versements

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.
estimation Valeur facultative estimé du taux (si omise =0, ex 0,1 = 10%)

Exemple

Quel est le taux d’intérêt annuel si pour un montant placé de 1000 euros pendant 8 ans on reçoit 2000 euros ?

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 :

=NPM(taux;vpm;va;vc;type;estimation)

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

Combien d’années faut-il pour en obtenir 100000 euros à partir d’un capital de 10000 euros placé à 8.5% ?

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 :

=VA(taux;npm;vpm;vc;type)

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

Un placement a atteint le montant de 15000 euros sur une période de 2 ans à un taux de 5%. Quel était le montant initial du placement ?

VPM

La fonction VPM calcule le montants pour chaque échéance de paiement (ex mensualités). Sa syntaxe :

=VPM(taux;npm;va;vc;type)

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

Quel est le montant des mensualités pour un prêt de 10000 euros remboursable en 12 mois avec un taux d’intérêts annuel de 6.75% ?


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 :

=INTPER(taux;période;npm;va;vc;type)

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

Quel est le montant des mensualités pour un prêt de 10000 euros remboursable en 12 mois avec un taux d’intérêts annuel de 6.75% ?

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 :

=PRINCPER(taux;période;npm;va;vc)

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

Quel est le montant du capital remboursé (amortissement) par période pour un prêt de 10000 euros remboursable en 12 mois avec un taux d’intérêts annuel de 6.75% ?

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.

> Exercice : Plateforme/Exercices/Tableur perfectionnement/05_mise_en_forme_cond/immobilier.xls

Conclusion

Tableur, le sujet est vaste. La suite sera les macro, mais cela sera l’objet d’un autre cours ! À bientôt !

prev-tableur5

Laisser un commentaire