Fonctions complexes l`assistant fonction

COURS EXCEL 2ème PARTIE
Fonctions complexes
l'assistant fonction
est représenté dans la barre d'outils standard par l'icône
(Coller une fonction)
Il affiche la liste des fonctions ainsi que leurs formats, permettant ainsi de définir les valeurs des
arguments.
Se positionner dans la cellule de résultat
Cliquer sur
Choisir la catégorie adéquate
Choisir la fonction voulue
Cliquer sur "OK"
Cours excel 2ème partie
Page 1
(L’exemple choisi est la fonction financière VPM)
Renseigner les différents arguments
(réduire la boîte de dialogue pour sélectionner les cellules concernées )
(restaurer la boîte par la suite
Cliquer sur "OK"
Les fonctions "simples"
Définition
Les fonctions dites "simples" sont celles pour lesquelles il ne faut renseigner qu'un seul argument
dans la syntaxe.
Les plus utilisées sont :
MOYENNE (pour calculer une moyenne)
MIN (pour calculer un minimum)
MAX (pour calculer un maximum)
SOMME (pour calculer une somme)
Cours excel 2ème partie
Page 2
PRODUIT (pour calculer un produit)
Quelle que soit la fonction demandée, la syntaxe sera toujours la même :
=Nom_de_la_fonction (cellules concernées)
Procédure
Se positionner dans la cellule de résultat
Saisir "=" suivi du nom de la fonction et d'une parenthèse ouverte
Sélectionner les cellules concernées (utiliser la touche Ctrl pour des cellules discontinues)
Valider (Excel se chargera de fermer la parenthèse)
Lors de la sélection des cellules, apparition des séparateurs de listes
":" jusqu'à
";" et puis
Par l'assistant fonction
-Se positionner dans la cellule de résultat
-Cliquer sur
-Choisir la catégorie adéquate
-Choisir la fonction voulue
-Cliquer sur "OK"
-Renseigner les différents arguments
(réduire la boîte de dialogue pour sélectionner les cellules concernées)
-Cliquer sur "OK"
Les fonctions "complexes"
Définition
On appelle fonction "complexe" une fonction qui comporte au moins deux arguments dans sa
syntaxe.
Quelle que soit la fonction demandée, la syntaxe sera la suivante :
=Nom_de_la_fonction (Arg1 ; Arg2 ; ... ; ArgN)
Pour faciliter l'utilisation des différentes fonctions mises à disposition dans Excel, il est possible
d'avoir recours à l'assistant représenté par dans la barre d'outils standard.
Procédure
Cours excel 2ème partie
Page 3
Se positionner dans la cellule de résultat
2 possibilités :
Avec l'assistant fonction:
Cliquer sur
Choisir la catégorie voulue
Choisir la fonction voulue
Cliquer sur "OK"
Renseigner les différents arguments de la fonction (au moins ceux obligatoires)
Cliquer sur "OK"
Pour plus de compréhension vis à vis d'une fonction donnée, à la deuxième étape de l'assistant
cliquer sur "?" (Compagnon office : déf. de la fonction, exemple et/ou démo).
Saisie directe :
Saisir "=" suivi du nom de la fonction et d'une parenthèse ouverte
Indiquer les différents arguments en les séparant les uns des autres par un ";"
Une fois tous les arguments notés, fermer la parenthèse
Valider
Pour avoir recours à la saisie directe, la syntaxe des différentes fonctions utilisées doit être
parfaitement maîtrisée.
Fonctions conditionnées simples
Définition
Permet d'afficher un résultat suivant une condition donnée.
Procédure
Se positionner dans la cellule de résultat
Cliquer sur
Choisir, dans la catégorie "Logique", la fonction "SI"
Cliquer sur "OK"
Renseigner les différents arguments :
Test_logique : Condition à vérifier
Cours excel 2ème partie
Page 4
- Sélectionner la cellule sur laquelle doit se porter la condition
- Saisir l'opérateur de comparaison (voir ci-dessous)
- Saisir la valeur de comparaison : Cette dernière peut être une donnée, un
calcul ou une cellule
Valeur_si_vrai : indiquer le résultat à afficher si la condition est vérifiée
Valeur_si_faux : Indiquer le résultat à afficher si la condition n'est pas vérifiée
Cliquer sur "OK"
Les différents opérateurs de comparaison sont les suivants :
Math
Excel
Math
=
=
>=
>
>
<=
<
<
<>
Excel
Si les arguments "Valeur_si_vrai" et "Valeur_si_faux" ne sont pas renseignés, le résultat affiché
par défaut sera soit "Vrai" soit "Faux".
Saisie directe :
la syntaxe de la fonction est :
=SI(Test_logique;Valeur_si_vrai;Valeur_si_faux)
Fonctions conditionnées combinées
Définition
Permet d'afficher un résultat suivant une condition multiple donnée.
La fonction "SI" peut être combinée - au niveau du test logique - avec :
ET : pour vérifier tous les critères énoncés
=ET(Arg1; Arg2; ...;ArgN)
OU : pour vérifier au moins un des critères énoncés
=OU(Arg1; Arg2; ...;ArgN)
NON : pour vérifier le contraire du critère énoncé
=NON(Arg1)
Cours excel 2ème partie
Page 5
Ces 3 fonctions peuvent être utilisées seules. Le résultat sera "Vrai" ou "Faux".
Elles peuvent être aussi combinées entre elles.
Procédure
Se positionner dans la cellule de résultat
Cliquer sur
Choisir, dans la catégorie "Logique", la fonction "SI"
Cliquer sur "OK"
Renseigner les différents arguments :
Test_logique
- Saisir l'élément de combinaison (ET - OU - NON)
- Ouvrir la parenthèse
- Indiquer la/les conditions
- Fermer la parenthèse
Valeur_si_vrai : indiquer le résultat à afficher si la condition est vérifiée
Valeur_si_faux : Indiquer le résultat à afficher si la condition n'est pas vérifiée
Cliquer sur "OK"
Pour procéder en mode de saisie directe, la syntaxe de la fonction est :
=SI(Combinaison(Arg1; Arg2; ...;ArgN);Valeur_si_vrai;Valeur_si_faux)
Fonctions conditionnées imbriquées
Définition
Permet d'indiquer plusieurs conditions ayant chacune leur résultat.
Procédure
Se positionner dans la cellule de résultat
Cliquer sur
Choisir la catégorie "Logique"
Choisir la fonction "SI"
Cliquer sur "OK"
Renseigner les différents arguments :
Cours excel 2ème partie
Page 6
Test_logique
- Saisir la 1ère condition à vérifier (celle-ci pouvant être combinée)
Valeur_si_vrai : indiquer le résultat à afficher si la première condition est vérifiée
Valeur_si_faux :
Pour indiquer une nouvelle condition, cliquer dans la zone Nom de la barre de formule
Choisir la fonction "SI"
Renseigner les différents arguments :
Répéter l'action * autant de fois que nécessaire
Une fois la dernière condition et sa "Valeur_si_vrai" indiquée :
- Cliquer dans la zone "Valeur_si_faux"
- Y indiquer le résultat à afficher si aucune des conditions n'est vérifiée
Cliquer sur "OK
Nota :
Les conditions portant sur des nombres doivent être indiquées dans l'ordre décroissant
En saisie directe, respecter la procédure suivante :
Se positionner dans la cellule de résultat
Saisir "=SI" suivi d'une parenthèse ouverte
Indiquer le premier "Test_logique" suivi d'un point-virgule
Saisir la "Valeur_si_vrai" rattachée à ce premier "Test_logique" suivi d'un point virgule
Indiquer qu'il y a d'autres conditions :
Saisir "SI" suivi d'une parenthèse ouverte
Indiquer le prochain "Test_logique" suivi d'un point-virgule
Saisir la "Valeur_si_vrai" rattachée à ce "Test_logique" suivi d'un point virgule
Répéter l'opération autant de fois que nécessaire
Une fois toutes les conditions indiquées :
Saisir le résultat à afficher si aucune d'entre elles n'est vérifiée (Valeur_si_faux)
Fermer la parenthèse autant de fois qu'il y a de "SI" dans la formule
Exemple de syntaxe pour 3 conditions :
=SI(Test1;Valeur_si_vrai1;
SI(Test2;Valeur_si_vrai2;
Cours excel 2ème partie
Page 7
SI(Test3;Valeur_si_vrai3;Valeur_si_faux3))
es fonctions de calculs conditionnels
La fonction "Somme.Si"
La fonction "Somme.Si" permet de calculer la somme de données répondant à une condition
Se positionner dans la cellule de résultat
Cliquer sur
Choisir, dans la catégorie "Math & Trigo", la fonction "SOMME.SI"
Cliquer sur "OK"
Renseigner les différents arguments :
Plage : Sélectionner les cellules dans lesquelles le critère doit être vérifié
Critère : Indiquer la condition à vérifier
Somme_plage : Sélectionner les cellules contenant les données à additionner
Cliquer sur "OK"
Pour procéder en saisie directe, la syntaxe est :
=SOMME.SI(Plage;Critère;Somme_plage)
La fonction "NB.SI"
La fonction NB.SI permet de calculer le nombre de cellules répondant à un critère
Se positionner dans la cellule de résultat
Cliquer sur
Choisir la catégorie "Statistique"
Choisir la fonction "NB.SI"
Cliquer sur "OK"
Renseigner les différents arguments :
Plage : Sélectionner les cellules dans lesquelles le critère doit être vérifié
Critère : Indiquer la condition à vérifier
Cliquer sur "OK"
Pour procéder en saisie directe, la syntaxe est :
=NB.SI(Plage; "Critère")
Cours excel 2ème partie
Page 8
La fonction "NB.SI.ENS"
La fonction NB.SI.ENS permet de calculer le nombre de cellules répondant à un ensemble de
critères
Se positionner dans la cellule de résultat
Cliquer sur
Choisir la catégorie "Statistique"
Choisir la fonction "NB.SI.ENS"
Cliquer sur "OK"
Renseigner les différents arguments :
Plage1 : Sélectionner les cellules dans lesquelles le critère doit être vérifié
Critère1 : Indiquer la condition à vérifier
Plage2 : Sélectionner les cellules dans lesquelles le critère doit être vérifié
Critère2 : Indiquer la condition à vérifier
Cliquer sur "OK"
Pour procéder en saisie directe, la syntaxe est :
=NB.SI.ENS(Plage1; "Critère1";Plage2 ; "Critère2")
Comment Alimenter Une feuille Excel à partir des
Données venant d’une autre feuille
Selectionner la cellule ici B7 de la feuille ‘Histoire-geo’
Cours excel 2ème partie
Page 9
Ensuite allez dans la feuille destination ici (Moyenne) se placer dans la cellule d’acceuil ici B6 et saisir :
=’Histoire-Geo’ !B7 et faire entrée
Exercice sur plusieurs feuilles
Ouvrir le document « Exercice feuille de base »
Cours excel 2ème partie
Page 10
Déplacement
Options du Bouton droit
Double clic pour renommer
Cours excel 2ème partie
Page 11
Formule multi feuille : =, Onglet Janvier, B2, +, Onglet Février, B2, +…
Autre méthode (plus rapide, si l’on a de nombreuses feuilles) :
Se mettre sur la feuille Total, sur cellule B2,
cliquer sur le bouton
(Onglet Formule) cliquer sur l’onglet Janvier, puis sur Majuscule, puis
B2, puis sur l’onglet Juin, valider, puis lacher la majuscule…
Exercice achevé
Gestion des onglets
Gestion des couleurs d’onglets (clic droit sur l’Onglet)
Filtres
Ouvrir « Exercice fichier clients … »
Cours excel 2ème partie
Page 12
Sur un tableau avec des entêtes de colonnes,
Exercice : mettre en oeuvre le filtre automatique
Ouvrir le document « Exercice fichier client »
Mettre en oeuvre les filtres
Recherche et remplace
Cours excel 2ème partie
Page 13
Onglet Accueil, Rechercher et sélectionner, Rechercher…
Indiquer « r » et onglet Remplacer indiquer « rue »
Cliquer sur le Bouton Remplacer tout…
Exercice :
Ouvrir le document « Exercice fichier client » (même document qu’exercice précédent)
Remplacer « r » par « rue »
Remarque : il faut mettre un blanc, puis la lettre « r » puis un autre blanc. Ceci pour ne convertir
que les textes contenant « r » et non pas ceux qui se terminent ou qui commencent par « r ».
On peut aussi remplacer
« rte » par « route »
« , » par RIEN
Cours excel 2ème partie
Page 14
Trier
Sur un tableau avec des entêtes de colonnes,
Ne pas sélectionner de colonnes,
Onglet Données, Trier
.
Exercice : mettre en oeuvre les tris
Ouvrir le document Exercice fichier client (même document qu’exercice précédent)
Mettre en oeuvre un tri sur le nom.
Même exercice avec une colonne sélectionnée
Attention : choisir étendre la sélection…
Cours excel 2ème partie
Page 15
Définir des graphiques
Sélectionner les zones utiles puis cliquer sur le bouton de définition de graphique :
Autre exemple : secteurs
Voir la touche CTRL pour effectuer des sélections multiples.
Avec pourcentage :
Sur la zone graphique, Clic droit, Ajouter des étiquettes,
Cours excel 2ème partie
Page 16
Puis sur la zone graphique, Clic droit, Modifier les étiquettes,
Cours excel 2ème partie
Page 17
Modifications :
Clic droit sur le graphique
3 - Autre exemple : Ca cumulé
Exercice : tracer un graphique
Ouvrir le document « Exercice feuille de base »
Tracer un graphique.
Définir un Graphique dans une cellule différente :
Se mettre dans la feuille choisie :
Cours excel 2ème partie
Page 18
Cliquer sur insertion
Cliquer sur graphique et double cliquer sur le type de grahique
Positionner le graphique à l’endroit désiré
Clic droit sur la souris
Dans le menu contextuel affiché cliquer sur sélectionner des données comme ci-dessous :
Cours excel 2ème partie
Page 19
Cliquer sur ajouter
Dans le menu contextuel saisir comme suit :
Nom de la série : =’feuille’!cellule
Valeurs de la série : =’feuille’!cellule
Puis OK
Ensuite recommencer si cela est nécessaire
Cours excel 2ème partie
Page 20