|
| |
04 -
UTILISER LES FONCTIONS
INTEGREES D’EXCEL
1. Affecter un nom à une
cellule, à une plage de cellules
è
Nommer une cellule ou une plage de cellules
- Sélectionnez la cellule, la plage de cellules
ou la sélection multiple que vous voulez nommer.
- Choisissez le menu « Insertion ».
- Choisissez
la commande « Nom ».
- Choisissez
la sous-commande « Définir ».
- Dans la zone « Nom », tapez le nom à donner
aux cellules.
|
 |
|
Excel propose un nom
basé sur le texte le plus proche. |
- Vérifiez que dans la zone « Fait référence à : »
la référence est correcte, sinon retapez-la
(elle doit commencer par le signe = comme une formule).
- Choisissez OK.
|
|
Désormais, dans les formules, vous pourrez saisir le
nom des cellules à la place des références. |
è
Coller un nom dans une formule
- Choisissez le menu « Insertion ».
- Choisissez la commande « Nom ».
- Choisissez la sous-commande « Coller ».
- Sélectionnez dans la liste, le nom que vous voulez
coller.
- Choisissez OK.
|
 |
è
Modifier la référence correspondant à un nom
- Choisissez le menu « Insertion ».
- Dans la commande « Nom ».
- Choisissez la sous-commande « Définir ».
- Sélectionnez le nom que vous voulez modifier.
- Dans la zone « Fait référence à : »,
entrez une nouvelle référence.
- Choisissez OK.
|
è
Supprimer un nom qui n’est plus utilisé
- Choisissez le menu « Insertion ».
- Choisissez la commande « Nom ».
- Choisissez la sous-commande « Définir ».
- Sélectionnez le nom que vous voulez supprimer.
- Cliquez sur le bouton « Supprimer ».
- Cliquez sur le bouton « Fermer ».
|
| |
|
Attention !!! Il est impossible d’annuler la
suppression d’un nom.
Aussi, avant de supprimer un nom, vérifiez bien qu’aucune formule ne
l’utilise. |
è
Afficher la liste des noms
- Placez-vous dans une zone vierge de la feuille de
calcul.
- Choisissez le menu « Insertion ».
- Choisissez la commande « Nom ».
- Choisissez la sous-commande « Coller ».
- Cliquez sur le bouton « Coller une liste ».
|
 |
| |
|
Excel affiche la liste des
noms définis avec la référence des cellules correspondantes. |
2. Utiliser les expressions
conditionnelles
è
La Fonction Si
|
La syntaxe utilisée est la suivante : |
|
=SI(condition à évaluer ;
valeur si vrai ; valeur si fausse) |
|
Le résultat de la formule peut être une valeur
numérique, une formule de calcul ou du texte. |
|
Dans les conditions à évaluer, vous pouvez utiliser les
opérateurs suivants : |
|
Egal =
Supérieur à >
Inférieur à <
Supérieur ou égal à >=
Inférieur ou égal à <=
Différent de <> |
|
Exemples :
|
|
Si la moyenne de l’élève est supérieure
ou égale à 10, il passe dans la classe suivante, sinon il redouble.
La formule sera :
=SI(D4>=10 ; « Passage » ; « Redoublement »)
D4 représente la cellule qui contient la moyenne.
Le texte se met entre guillemets. |
 |
|
Si le montant de la facture est supérieur à 1 000, vous avez une remise de 3
% sur le montant
de la facture, sinon vous n’avez pas de remise.
|
|
La formule de calcul du montant de la remise sera :
=SI(E2>1000 ;E2*3% ;0)
E2 représente la cellule qui contient le montant
de la facture. |
 |
è
SI imbriqués
|
Il s’agit d’imbriquer des conditions selon la syntaxe
suivante : |
|
=SI(condition 1 ; valeur si vraie ; si(condition 2 ; valeur si vraie ;
valeur si fausse))
|
|
=SI(condition 1 ;si(condition 2 ; valeur si vraie ; valeur si fausse) ;
valeur si fausse)
|
|
Exemple :
Calcul d’une remise en fonction du montant total de la
facture :
Montant >= 1 000 F 5% de remise sur le total
Montant entre 500 F et 1 000 F 3 % de remise sur le total
Montant < 500 F pas de remise |
| |
|
La formule qui permet d’obtenir le montant
de la remise sera la suivante :
=SI(E2>=1000 ;E2*5% ;SI(E2<500 ;0 ;E2*3%))
E2 représente la cellule qui contient le montant total
de la facture. |
 |
|
Attention !!! Ici, il y a trois cas possibles, donc nous aurons une
formule
avec deux fonctions SI imbriquées.
|
è
Utilisation des fonctions ET et OU
|
La syntaxe à
retenir est : |
ET(condition 1 ;condition 2 ;condition 3 ;…)
OU(condition 1 ;condition 2 ;condition 3 ;…) |
|
Exemple :
Pour passer dans la classe supérieure,
il faut une moyenne supérieure
ou égale à 10
et une note de mathématiques supérieure ou égale à 11.
|
 |
|
La formule sera :
=SI(ET(A4>=10 ;G5>=11) ; « Passage » ;
« Redoublement »)
A4 représente la cellule qui contient la moyenne, G5
représente la cellule qui contient la note
de mathématiques. |
|
Attention !!! Le nombre de parenthèses ouvertes doit être égal au nombre
de parenthèses fermées.
|
3. Utiliser les fonctions de
recherche
|
Une matrice est une plage
de cellules qui contient du texte, des nombres
ou des valeurs logiques. |
| |
| La fonction
RECHERCHEH : |
cherche
une valeur sur la première ligne d’une matrice et se déplace vers le bas de
la colonne pour renvoyer la valeur d’une cellule. |
| |
|
|
La fonction
RECHERCHEV : |
cherche une valeur dans la première colonne d’une matrice et se déplace
latéralement pour renvoyer la valeur d’une cellule. |
| |
|
La syntaxe à retenir est la
suivante : |
|
RECHERCHEH(valeur_cherchée ;table_matrice ;n°_index_ligne)
|
|
Valeur_cherchée est la
valeur à trouver dans la première ligne de la table.
Table_matrice est une
table de données dans laquelle est exécutée la recherche de la valeur.
N°_index_ligne
est le numéro de la ligne de Table_matrice dont la valeur correspondante
est renvoyée. |
| |
|
RECHERCHEV(valeur_cherchée ;table_matrice ;n°_index_col)
|
|
Valeur_cherchée est la
valeur à trouver dans la première colonne de la table.
Table_matrice est une
table de données dans laquelle est exécutée la recherche de la valeur.
N°_index_col est
le numéro de la colonne de Table_matrice dont la valeur correspondante
est renvoyée. |
| |
|
Exemple :
Calculer les notes des
élèves en fonction de leur classe et des coefficients de chaque matière.
Vous disposez du tableau des coefficients en Feuil1 du
classeur. |
- Nommez
la zone A3 :E7 : Table.
Cette table représente la
Table_matrice.
|
 |
- En Feuil2 du classeur, construisez le tableau des
résultats.
La formule en cellule D2 sera :
=RECHERCHEH(B2 ;TABLE ;4)
|
 |
|
On choisit la fonction
RECHERCHEH car la valeur cherchée est dans la première ligne
de la Table_matrice.
|
|
La valeur_cherchée
est B2 : Classe de l’élève.
|
|
La Table_matrice est
TABLE : Matrice préalablement nommée.
|
|
Le N°_index_ligne est
4 : Excel revoie la valeur de la quatrième ligne de Table_matrice.
C’est sur cette ligne que se situe les coefficients de français.
|
|
Attention !!! Les
valeurs cherchées dans la Table_matrice doivent être placées en ordre
croissant, sinon RECHERCHE peut donner une valeur incorrecte.
Dans le cas contraire, vous
devez ajouter un quatrième argument qui aura la valeur FAUX : =RechercheH(B2 ;table ;4 ;Faux). |
4.
Réaliser des calculs financiers
Excel dispose de nombreuses
fonctions qui permettent de faire des calculs financiers courants.
è
Calcul d’amortissement
|
La fonction AMORLIN(Coût ;Valeur_rés ;Durée)
calcule l’amortissement linéaire
d’un bien pour une période donnée.
|
|
Coût |
Coût initial du bien. |
|
Valeur_rés |
Valeur du bien au terme de
l’amortissement (valeur résiduelle du bien). |
|
Durée |
Nombre de périodes au cours
desquelles le bien est amorti
(vie utile du bien). |
è
Calcul d’annuités
|
Une annuité est une série
de remboursements constants pendant une période ininterrompue. Par exemple,
un emprunt pour une voiture ou un emprunt hypothécaire est une annuité.
|
|
La fonction VPM(Taux ;Npm ;Va)
calcule la valeur des paiements périodiques
d’une annuité à remboursements et taux d’intérêt constants. |
|
Taux |
Taux d’intérêt par période. |
|
Npm |
Nombre total de périodes de
paiement d’une annuité. |
|
Va |
Valeur actuelle. |
|
La fonction TAUX(Npm ;Vpm ;Va)
calcule le taux d’intérêt par période
d’un investissement donné pour une annuité. |
|
Npm |
Nombre total de périodes de
paiement d’une annuité. |
|
Vpm |
Montant de chaque
remboursement périodique. |
|
Va |
Valeur actuelle. |
|
La fonction NPM(Taux,Vpm ;Va)
calcule le nombre de remboursements périodiques
et constants d’un investissement à taux d’intérêt constant. |
|
Taux |
Taux d’intérêt par période. |
|
Vpm |
Remboursement pour chaque
période. |
|
Va |
Valeur actuelle. |
|
La fonction VA(Taux ;Npm ;Vpm)
calcule la valeur actuelle d’un investissement |
|
Taux |
Taux d’intérêt par période. |
|
Npm |
Nombre total de périodes de
remboursement au cours d’une annuité. |
|
Vpm |
Montant du remboursement
pour chaque période. |
|
