Faire des additions au pieds des colonnes... c'est bien, mais Excel dispose d'outils incroyablement plus puissants : les Fonctions (ou Formules).
Excel dispose de 100aines de fonctions... Chaque fonction est 1 solution à 1 besoin ou 1 problème de l'utilisateur...
Chaque Fonction possède son propre "petit mode d'emploi"... (certaines sont simples à utiliser, d'autres moins)...
il n'est pas nécessaire de retenir comment utiliser chaque Fonction..., l'essentiel c'est de mémoriser leur nom & de savoir quel 'problème' elle traite... (et d'aller 'réviser' son mode d'emploi avant de la mettre en pratique :-)
Nous proposons ici de découvrir les bases de l'utilisation des FONCTIONS, et de prendre en main une petite sélection des plus utiles, simples et pratiques (dont la surpuissante =RECHERCHEV ()
ATTENTION : Pour une meilleure lisibilité des fonctions, nous avons inclus des espaces dans les exemples de cette page... MAIS en conditions réelles, il est recommandé de NE PAS utiliser d'espace :-)
Découvrez les Fonctions progressivement, petit à petit, et mettez-les en pratique avec succès avant d'en attaquer une autre
L'utilisation des Fonctions réclame parfois une logique proche de la programmation... Alors soyez rigoureux et décomposer vos problèmes en 'petits morceaux' pour n'être confronté qu'a de petits problèmes :-)
Dans XL, on distingue :
les 'cellules utilisateurs' : les cellules qui 'appartiennent' aux utilisateurs... celles où il a saisi des textes et/ou des nombres...
les' cellules XL' : les celles qui commencent par le signe ' = '... celles donc XL prend en charge l'affichage en fonctions d'instructions qu'il a reçu...
Les cellules gérées par XL sont de 2 types : les Formules et les Fonctions...
FORMULES :
des instructions de calculs 'simples' : comme = A2 + B2 + C2 qui affichera le résultat de l'addition des nombres contenu dans les 3 cellules. (voir Calculer avec XL)
avec une Formule : c'est l'Utilisateur qui impose une méthode à XL...
FONCTIONS :
des Instructions ou opérations plus complexes comme = SOMME ( A2 : A2500 ) qui affichera le résultat de l'addition de tous les nombres contenus entre la cellule A2 et la cellule A2500... (soit 2499 nombres additionnés !)
une FONCTION se reconnait car le signe ' = ' est suivi d'un NOM en MAJUSCULE puis d'instructions entre parenthèses... (comme =SOMME(A2:A10)... ou = CONCATENER(A15;B15) ...
avec une Fonction : c'est XL qui impose un fonctionnement à l'utilisateur pour arriver à ses fins :-)
En PRATIQUE, tout le monde utilise indifféremment les 2 termes... Nous aussi dans ce support :-)
Une FONCTION Excel est un petit outil piloté par XL qui permet d'obtenir 1 résultat spécifique... Chaque fonction effectue 1 tâche précise...
L'utilisateur doit souvent découper son problème ou besoin en "petits morceaux", puis trouver et utiliser la Fonction qui solutionnera chacun de ses "petit morceau" :-)
il existe des fonctions pour tous les usages ou métiers :
pour réaliser des calculs un peu costauds...
pour rechercher des informations dans des documents complexes
pour travailler et manipuler des cellules existantes...
pour la Finance (calculs d'intérêts, amortissement, etc...)
pour la Recherche scientifique
etc...
TOUTES les fonctions sont consultables ICI (mais attention, ça fait peur...)
Chaque Fonction dispose de son propre mode d'emploi... Certaines sont simples à mettre en oeuvre... d'autres moins... On apprend les fonctions au fur et à mesure des besoins rencontrés :-)
Sur une feuille de calculs, XL affiche les saisies de l'utilisateurs & les résultats des fonctions/Formules... 2 moyens sont à votre disposition pour voir/comprendre les fonctions :
cliquer sur 1 cellule et lire la barre de formule
Utiliser le raccourci clavier : Ctrl+" pour visualiser TOUTES les formules d'une feuille (ou ruban Formules, cliquer sur le bouton Afficher les formules)
Il y a 3 parties dans une fonction XL... :
= : une fonction commence toujours par le signe Egale ( '= ')
NOMFONCTION : le NOM (en majuscule) de la fonction suit immédiatement le signe ' = '... (c'est lui qu'il faut tenter de mémoriser)
(arguments1;...) : le nom de la fonction est immédiatement suivi d'Arguments entre parenthèses. (selon les fonctions, 1, 2 ou + arguments sont nécessaires) séparés par un ' ; ' (point-virgule). Chaque Argument' est 1 information à fournir à XL pour qu'il puisse mener à bien sa fonction (il s'agit souvent de références de cellules, mais pas que...) . Un argument peut être soit :
du Texte (entre guillemets),
une Référence simple (ex. : A5),
Plage de cellules (ex. : A5:C20),
une Fonction (SANS signe ' = ') :-)
Exemples de fonctions...
= SOMME ( argument1 )
= SOMME ( A2:A2500 ) => 1 seul argument qui est une plage de cellules (un tableau qui va de la cellule A2 jusqu'à la cellule A2500....)
= NB.SI ( argument1 ; argument2 )
= NB.SI (A2:H20 ; "Cat A") => compte le nombre de cellules qui contiennent "Cat A" (2) dans un tableau (1) (le tableau occupe les cellules A2 à H20)
= SI ( argument1 ; argument2 ; argument3 )
= SI (D10="Cat A" ; "l'agent est encadrant " ; "l'agent n'est PAS encadrant") => Si la cellule D10 contient "Cat A" (1), si oui on affiche un texte (2), si non on affiche un autre texte (3)
= RECHERCHEV ( argument1 ; argument2 ; argument3 ; argument4 )
= RECHERCHEV ("paul" ; A5:J200 ; 5 ; FAUX) => on recherche un "paul"(1) dans un tableau B (2), et si trouvé, on rapatrie la colonne 5 (3)... et une option XL 'Faux' (4)
Si XL ne parvient pas à trouver un résultat, il affiche un code d'erreur à la place du résultat...
Ces codes d'erreur permettent de diagnostiquer le problème qu'a rencontré la fonction :
#DIV/0 : XL vous dit "on ne divise par le chiffre 0 !"
#NOM? : XL vous dit "je ne connais pas ce nom de cellule ou de tableau"
#REF! XL vous dit "le cellule ou tableau n'existe plus ! / j'ai pas trouvé !"
#N/A : XL vous dit "je ne trouve pas ce que je devrais trouver ! "
#VALEUR! XL vous dit "tu m'a pas donné la bonne info !"
#NUL! XL vous dit "je comprends pas l'information que tu m'as donnée... prob de syntaxe ??"
#NOMBRE! XL vous dit "j'ai un problème avec ton nombre... vérifie le "
########## XL vous dit "j'ai bien trouvé un nombre, mais je peux pas l'afficher dans cette largeur de colonne""
1 seule chose à faire : Vérifier et Corriger !
Est-ce que toutes les infos demandées par XL sont bien présentes ?
Est-ce que tous les arguments sont là ?
Est-ce que la syntaxe est correcte & complète (y'a t'il le ' ; ' ? les ' ( ' ouvertes ont-elles bien été fermées ??)
etc...
Voir sur Excel-Pratique : cet excellent site vous aidera à comprendre (et corriger) l'erreur :-)
RAPPEL : En cas de copie des formules ou fonctions (par copier-coller, ou avec la poignée de copie incrémentée), XL adapte les références utilisées en fonction de la nouvelle position... Ce fonctionnement est parfois gênant.
Retenez simplement ceci :
utiliser des $ pour FIGER une référence et interdire toute adaptation de lors des copies... Par exemple un tableau qui occupe la plage de cellules A2:C10 deviendra $A$2:$C$10 , ainsi équipée $, XL respectera toujours cette référence sans jamais l'adapter ou la modifier...
le $ rend XL têtu :-)
Une référence saisie 'simplement' s'adaptera quand on la tire ou colle... on parle de Référence RELATIVE
ici B2 utilise une formule qui lit A2...
si on 'tire' la formule vers le bas... en B3, la référence sera adaptée en A3...
...en B4, elle s'adaptera en A4
etc...
Une référence saisie AVEC le signe $ signifie ne s'adapte PAS quand on la tire ou colle...on parle de Référence ABSOLUE
ici B2 utilise une formule qui lit toujours la colonne A et toujours la ligne 2... (A2 devient $A$2)
si on "tire la formule vers le bas... en B3, la référence sera toujours en colonne A, toujours en ligne 2... encore $A$2
...en B4, toujours $A$2
etc...
$ signifie "toujours" pour XL...
$ signifie "absolument" pour XL...
= AUJOURDHUI ()
la plus simple de toutes, car la seule qui n'a pas besoin d'arguments :-)
Attention : veillez à ne pas écrire l'apostrophe par réflexe:-)=AUJOURDHUI() affiche automatiquement la date du jour... (chaque jour la date s'actualise toute seule :-)
= SI ( test_logique ; valeur_si_vrai ; valeur_si_faux)
= SI ( question ; réponse_si_vrai ; réponse_si_faux )
=SI () permet d'afficher un résultat en fonction d'une question... C'est à dire qu'XL effectuera un test (poser une question) et il affichera 1 résultat si la réponse à la question est VRAI , et 1 autre résultat si la réponse à la question est FAUX.
Les cellules utilisant cette fonction sont ainsi plus intelligentes et capables d'adapter le résultat à des situations spécifiques :-) (exemple : si 1 score est plus grand que 10, XL affichera un "Bravo" pour féliciter l'utilisateur et un "Recommence" si le score est inférieur à 10)
Le test Logique (la question) se pose avec les opérateurs de comparaison suivants :
' = ' (égale)
' > ' (plus grand que) ; ' < ' (plus petit que)
' >= ' ( supérieur ou égal à) ; ' <= ' (inférieur ou égal à)
' <> ' (différent de)
Le résultat à afficher (qu'il soit vrai ou faux !) peut être :
soit du texte entre guillemets (voir exemple ci-dessous)
soit un calcul
soit une autre cellule
soit une autre fonction
Dans cette exemple, 1 cellule calcule un budget restant OU informe l'utilisateur d'un dépassement du budget...
La cellule en C10 contient la formule suivante :
= SI ( question ; réponse_si_vrai ; réponse_si_faux )
= SI ( B8>C3 ; "ACHAT IMPOSSIBLE" ; C3-B8 )
argument question : "EST-CE-QUE la cellule B8 est plus grande que la cellule C3 ?"
argument réponse_si_VRAI : SI C'EST VRAI que B8 est plus grand que C3 : Excel écrit un texte "ACHAT IMPOSSIBLE"
argument réponse_si_FAUX : SI C'EST FAUX : Excel calcule le budget - la dépense avec le formule C3-B8
= RECHERCHEV ( valeur_cherchée ; tableau_où_chercher ; numero_colonne ; FAUX )
Nous pouvons même "humaniser" cette fonction pour encore mieux la comprendre...
= RECHERCHEV ( aiguille ; meule_de_foin ; rapatrier_colonne_n° ; FAUX )
=RECHERCHEV () permet de chercher 1 information dans un autre tableau... puis de rapatrier 1 information qui se trouve dans une autre colonne. (C'est un peu chercher une aiguille dans une meule de foin, mais en mieux, car ça ramène une information :-)
Pour bien fonctionner, il faut absolument respecter les conditions suivantes :
l'information recherchée doit être unique dans le tableau où chercher
l'information recherchée doit se trouver dans la 1ère colonne du tableau où chercher
la colonne à rapatrier est identifiée par son numéro (exemple : 3 pour la 3ème colonne)
Imaginons que la direction Etat civil possède 1 tableau VERT avec l'adresse des agents, mais PAS leur catégorie... ET imaginons que les Ressources Humaines lui communique un tableau ORANGE avec l'info désirée...
=RECHERCHEV() va permettre de rapatrier l'information du tableau ORANGE dans le tableau VERT :-) (L'état civil pourra ainsi écrire à une catégorie d'agents seulement :-)
Grace à =RECHERCHEV(), le tableau VERT sera enrichi des informations qui se trouvent du tableau ORANGE (3ème colonne...)
la formule utilisée est la suivante (exemple sur la ligne rouge) :
= RECHERCHEV ( valeur_cherchée ; tableau_où_chercher ; numero_colonne ; FAUX )
= RECHERCHEV ( B14 ; $I$5:$M$23 ; 3 ; FAUX )
B14 = valeur_cherchée : Ce que nous recherchons dans le second tableau, c'est le matricule agent (qui est unique !). Dans notre tableau vert le matricule est en B14... n'importe quelle information peut être choisie A CONDITION qu'elle soit dans la 1ère colonne du tableau recherché (ORANGE)
I$5:$M$23 = tableau_où_chercher : Le tableau ORANGE occupe les cellules I5 à M23... On utilisera des $ pour que ces références ne changent pas : I$5:$M$23
3 = numero_colonne : La colonne qui sera rapatriée sera la 3ème colonne...
FAUX : Ce paramètre est plus difficile à appréhender (nous le précisons plus bas), mais FAUX est à utiliser systématiquement quand la 1ère colonne contient des informations uniques (jamais en double dans la colonne)
Le visuel ci-dessous visualise le raisonnement...
NOTE : pour la mise en place, on créée la formule sur la 1ère ligne du tableau Vert (en G6), en utilisant bien sûr le matricule en B6... La formule sera ensuite tirée... (seule la référence B6 sera modifiée de ligne en ligne, puisque saisie simplement SANS le signe $ :-)
= GAUCHE ( Référence ; Nombre_caractères )
= DROITE ( Référence ; Nombre_caractères )
=GAUCHE() permet d'extraire 1 nombre de caractère à partir de la gauche (à partir du début)
=DROITE() permet d'extraire 1 nombre de caractère à partir de la droite (à partir de la fin)
Ces fonctions sont utiles pour créer des références...
Une direction souhaite créer une référence pour chaque nouvel arrivant... Elle utilisera la date d'arrivée de l'agent et les 2 premières lettres du NOM + la 1ère lettre du prénom...
Nous allons simplement préparer les colonnes (pour coller les 3 éléments ensemble, elle utilisera la fonction =CONCATENER() ci-dessous)
La Fonction utilisée en F3 extrait les 2 premiers caractères.
= GAUCHE ( Référence ; Nombre_caractères )
= GAUCHE ( D3 ; 2 )
D3 = Référence : on utilise le texte de la colonne NOM en D3
2 = Nombre : on exploite les 2 premiers caractères en partant de la gauche (début)
En G3, on utilise la même fonction avec 1 seul caractère... : = GAUCHE( E3 ; 1 )
= CONCATENER ( élément_1 ; élément_2 ; etc... )
Cette fonction peut contenir 2, 3, 4 ou bien plus d'arguments :-)
=CONCATENER () permet coller ensemble plusieurs éléments. Elle est utile pour regrouper plusieurs cellules séparées dans une seule et même cellule OU Créer une nouvelle colonne automatiquement à partir d'éléments existants. On peut l'utiliser pour créer une colonne qui sera utilisée dans un publipostage, ou bien créer une référence unique (référence, matricule...)
Le service informatique souhaite créer automatiquement les adresses emails de ses nouveaux agents... Elle récupère le tableau des Ressources Humaines des nouveaux arrivants...
=CONCATENER(() lui permettra de créer automatiquement des adresses de type "jean.dujardin@ville-saint-denis.fr" : soir le prénom + 1 point + le nom + @ville-saint-denis.fr
La fonction utilisée et J3 est la suivante :
= CONCATENER ( élément_1 ; élément_2 ; etc... )
= CONCATENER ( E3 ; "." ; D3 ; "@ville-saint-denis.fr" )
4 éléments seront nécessaires : Prenom + 1 point + Nom + @ville-saint-denis.fr
E3 = élément_1 : la cellule ou se trouve le prénom...
"." = élément_2 : ...à laquelle on ajoute 1 point (indiqué entre guillemets)
D3 = élément_3 : ...l ...à laquelle on colle le Nom* (voir note pour les Majuscules/ Minuscules)
"@ville-saint-denis.fr" = élément_4 : ... et le texte final !
Les adresses email étant toujours en minuscule, la fonction peut être améliorée en contrôlant les MAJUSCULES / MINUSCULES du NOM...
Il faudrait que " AAA ' devienne ' aaa '... et comme pour chaque besoin il existe une fonction, 2 fonctions simples sont à notre disposition :
=MAJUSCULE( référence )
=MINUSCULE (référence )
L'astuce consiste donc à 'équiper' la référence ' D3 ' de la fonction =MINUSCULE()
= CONCATENER ( E3 ; "." ; D3 ; "@ville-saint-denis.fr" )
' MINUSCULE(D3) ' vient donc remplacer ' D3 '... la Fonction devient donc...
= CONCATENER ( E3 ; "." ; MINUSCULE ( D3 ) ; "@ville-saint-denis.fr" )
Dans XL, l'utilisation d'une fonction à l'intérieur d'une autre s'appelle une Fonction Imbriquée. La fonction la 'plus à l'intérieur est toujours réalisée en 1er dans XL (Il est possible d'imbriquer une fonction à l'intérieur d'une autre, elle-même à l'intérieur d'une autre !!! mais l'exercice est parfois bien ardu :-)
Ci-dessus nous avons envisagé la création de référence avec =GAUCHE()... en préparant des colonnes préparatoires...
L'objectif était d'utiliser : "la date d'arrivée de l'agent et les 2 premières lettres du NOM + la 1ère lettre du prénom... ". Il y a donc au moins 3 éléments à coller..., nous ajouterons un tiret entre la date et les initiales :-)
La date d'arrivée sera affichée en format natif XL, c'est à dire que la date deviendra un code à 5 chiffres (19/03/2020 devient 43909, car le 43 909ème jour depuis la 1/1/1900...)
Voici l'objectif :
= CONCATENER ( date_arrivée ; tiret ; 2_premiers_caractères_nom ; 1_caractère_prenom ) )
= CONCATENER ( B3 ; "-" ; GAUCHE ( D3 ; 2 ) ; GAUCHE ( E3 ; 1 ) )
= FILTRE
Notes de mémo (demander un accompagnement à olivier
= FILTRE ( table ; critère(s) )
Agit comme les filtres de tables, mais avec des possibilités ETENDUES de filtres multiples
Il est possible de mettre des critères multiples utilisant les ET et les OU
Exemple Simple :
= FILTRE ( mes_agents ; [date] = A1)
Affiche tous les enregistrement d'un tableau nomme "'mes_agents" pour lesquels la date correspond à une valeur saisie dans la cellule A1 (par exemple 28/10/1970)
ET (*)
= FILTRE ( table ; (crit 1) * (crit 2) )
= FILTRE ( table ; ([date] = A1 )* ([cat] = "C") )
donne les enregistrement d'un tableau qui répondent au critère :
colonne [date] = la date saisie en A1
ET colonne [cat] = "C"
OU (+)
= FILTRE ( table ; (crit 1) + (crit 2) )
= FILTRE ( table ; ([date] = A1 )* ([cat] = "C") )
donne les enregistrement d'un tableau qui répondent au critère :
colonne [date] = date saisie en A1
ET colonne [cat] = "C"
Multiple (ET & OU)
= FILTRE ( table ; (crit 1) * ( (crit 2) + (crit3) )
= FILTRE ( table ;
( [date] = A1 ) * ( ( [cat] = "C" ) + ( [cat] = "A" ) )
)
donne les enregistrement d'un tableau qui répondent au critère :
colonne [date] = date saisie en A1
ET colonne [cat] = "C" OU "A"
Note : Pour jongler avec les critères multiples, utiliser des parenthèses (comme en math) pour "grouper" les conditions...