EXCEL - FORMULES DE CALCUL : AIDE-MMOIRE DES FONCTIONS, DES POINTEURS ET DES OPRATEURS

Pour bien connatre Excel, il ne suffit pas de connatre toutes ses commandes, il faut aussi connatre ses principales fonctions de calcul ! Vous trouverez ici un choix des meilleures fonctions dExcel, celles que vous utiliserez tous, un jour ou lautre Pour matriser vos formules, vous trouverez galement ici un mmo des oprateurs ainsi quun rcapitulatif des pointeurs de la souris quil est essentiel de bien comprendre. Cliquez simplement sur un mot-clef (dans le sommaire gauche) pour y accder instantanment Pour revenir la page prcdente, comme dhabitude, utilisez la touche CORRECTION-ARRIRE (Backspace) ou ALT+GAUCHE. Vous pouvez rduire ou largir le volet gauche (sommaire) en glissant sur le bord droit de l'ascenseur (c'est difficile attraper!... Visez bien avec le bi du bout de la pointe de la souris sur le bord droit de lascenseur...)

I-

FONCTIONS CHOISIES : le top 50RETOUR EN HAUT

Les fonctions dExcel sont des mots rservs que lon peut taper dans une formule pour obtenir facilement un rsultat labor. Sont ici dtailles les 50 fonctions les plus importantes. Toutes les fonctions dExcel utilisent des parenthses. Entre ces parenthses, on prcise les contraintes du calcul : Excel appelle arguments ces informations entre parenthses. Les arguments sont spars par le signe point-virgule ; Certaines fonctions nont pas besoin darguments : on tape alors 2 parenthses colles. Dautres nont besoin que dun seul argument : on na pas besoin de point-virgule dans ce cas. Jai souvent mis des espaces autour des points-virgules et des parenthses pour une bonne lisibilit, mais attention : il ne faut JAMAIS taper despace dans les formules !

Objectif
Pour additionner de nombreuses cellules, sans avoir les citer toutes dans la formule, il faut utiliser la fonction SOMME, et dcrire la plage de cellules sommer. Le signe deux-points : dit jusqu , le signe point-virgule ; dit et .

Fonction, syntaxe et exemples

Syntaxe : SOMME ( plage ) Exemples : SOMME ( B2 : B30 ) SOMME ( MontantHT ) SOMME ( TotalSem1 ; TotalSem2 )

Avantages de la fonction SOMME sur loprateur + : Ignorer les cellules contenant du texte. Avec +, on a un message derreur si une des cellules sommes contient un
texte !.

Astuce - Conseil
Vous allez tre tent de placer vos sommes en dessous des donnes de base, mme si vos donnes de base sont rgulirement augmentes de saisies nouvelles. Ceci va vous obliger insrer des lignes chaque fois que vous aurez des saisies oprer. Il est beaucoup plus malin de placer les sommes en tte, au-dessusdes donnes de base, et en visant une

Objectif
Dcrire rapidement un grand nombre de cellules.
Avec +, la saisie est longue et fastidieuse.

Fonction, syntaxe et exemples

plage beaucoup plus vaste que la zone des donnes actuellement saisies (voir exemple ci-dessus) : toute la colonne par exemple (sans prendre la formule ellemme, attention !!!). Vous aimerez srement voir immdiatement vos donnes de synthse, en haut des colonnes, ds que vous entrez dans le classeur, et dans le volet haut, mme pendant vos dplacements et corrections des donnes de base ! Essayez ! vous verrez

Dcrire la plage de faon fiable, sans oubli.

Avec loprateur +, le risque derreur est grand : oubli, doublon, erreur de pointage.

Permettre des insertions futures au cur de la plage somme.

Avec +, les futures insertions ne seront pas concernes par laddition.

Pour classer vos donnes, en plus de la commande Donnes Trier qui agit ponctuellement, sur demande, on peut dterminer le rang dune cellule dans une plage de cellules avec la fonction RANG. On calculera le rang de la premire cellule, puis on recopiera cette formule pour calculer le rang des autres. Mais il faudra faire attention figer la plage de rfrence (avec des dollars, touche F4) pour quelle reste la mme dans toutes les copies.

Vous observez sur cet exemple que la fonction RANG est souvent utilement complte par la commande Format Mise en forme conditionnelle qui permet, ici, dafficher automatiquement les 10 premiers en fond bleu, et les derniers (aprs le rang 20) en rouge Les autres restant sur fond blanc

Objectif

Fonction, syntaxe et exemples

Syntaxe :

RANG ( cellule ; plage )

Exemple ci-dessus

Dans le mme ordre dides (classement), on voquera des fonctions Syntaxe : simples qui permettent, partir dune plage de cellules, dextraire la plus grande, la plus petite, le nombre de valeurs, la moyenne Fonctions MIN, MAX, NB, NBVAL, MOYENNE

En jouant dastuce, on peut dcupler les performances dune fonction : par exemple, pour plafonner un calcul, je vais utiliser Exemples : MIN ( calcul ; plafond ). MAX ( B2 : B120 ) sort le plus grand nombre. De mme, pour ne pas dpasser un plancher, je vais utiliser MAX ( MOYENNE ( Notes ) calcule la moyenne. calcul ; plancher ). NB ( C5 : C37 ) calcule le nombre de valeurs Exemples : numriques en ignorant les MIN ( base * taux ; PlafondScu ) textes ventuels. pour plafonner un calcul de cotisation sociale.

MIN ( plage ) MAX ( plage ) MOYENNE ( plage ) NB ( plage ) NBVAL ( plage )

Objectif
MAX ( CA * 0,10 ; Fixe ) pour ne jamais payer un vendeur moins que le minimum fixe prvu (plancher).

Fonction, syntaxe et exemples

NBVAL ( C5 : C37 ) calcule le nombre de
cellules non vides, en comptant les textes et les chiffres.
Mme teint, lordinateur mmorise la date et lheure (avec une
pile)

On peut donc rcuprer la date-sytme et lheure-systme :

=AUJOURDHUI ( )
Pour saisir et manipuler des dates et des dlais, il faut connatre les

rend la date sytme (pas lheure), donc un nombre entier. rend la date et lheure systme, donc un nombre dcimal.

=MAINTENANT ( )

fonctions date :

Excel utilise un calendrier o chaque jour est un rangpar rapport au correspondant la date dcrite. Par 1er janvier 1900. exemple,DATE(01;07;09) rend 37 081. Avec cette fonction, on peut crer une date de toute Cest ainsi que le 9 juillet 2001 est le chiffre 37 081 mis pices, morceau par morceau au format j mmmm aaaa Par exemple : =DATE ( AnneAchat + 1 Par ailleurs, les heures sont naturellement des fractions de jour : 12h (midi) ; MoisAchat ; 1 ) est 0,5, 18h est 0,75, 6h du matin est 0,25, etc. permet de calculer la premire date Donc, le lundi 9 juillet 2001 6h se dit 37 081,25 au anniversaire dun achat, au 1er du mois. format jjjj j mmmm aaaa " " h"h" =JOUR ( date ) , MOIS ( date ) et ANNEE ( date On utilise les sparateurs / ou (slash ou tiret) poursaisir les dates. On utilise ) le sparateur : (deux points) pour saisir les heures. permettent dextraire une partie de la date, pour effectuer des tests ou recomposer une autre Exemple : il suffit de saisir 9/7 pour le lundi 9 juillet 2001, car Excel date. devine que sans anne spcifie, cest lanne en cours quil faut prendre. Les formats personnaliss connatre sont : Autre exemple : pour saisir 6h, il faut taper 6:0 (cest le :0 qui signale Excel quil
sagit dune heure). Code j jj jjj jjjj m mm mmm mmmm aa aaaa h hh m mm Format Exempl 9 09 lun lundi 7 07 juil juillet 01 2001 6 06 0 00

=DATE ( anne ; mois ; jour ) rend le nombre

Soustraire deux dates donne un dlai. Additionner ou soustraire une date et un dlai donne une date Additionner deux dates est absurde. Multiplier oudiviser 2 dates galement.
Pour dcouvrir seul lintgralit des fonctions offertes par Excel, appelez lAide en ligne , pas avec la touche F1 : le trombonne est totalement incomptent, il ne comprend rien aux questions : utilisez plutt la commande ? (point dinterrogation) Sommaire et Index, droite du menu, et cherchez le mot fonctions, de date

e jour sur 1 chiffre jour sur 2 chiffres jour sur 3 lettres jour en toutes lettres mois sur 1 chiffre mois sur 2 chiffres mois sur 3 ou 4 lettres mois en toutes lettres anne sur 2 chiffres anne sur 4 chiffres heure sur 1 chiffre heure sur 2 chiffres minutes sur un chiffre minutes sur 2 chiffres

Objectif

Fonction, syntaxe et exemples

Vous noterez que ET ( cond1 ; cond2 ; cond3) et OU ( cond1 ; cond2 ; cond3) sont des fonctions utiliser lintrieur du premier argument des fonctions SI. Un bloc "ET" ou un bloc "OU" gnre VRAI ou FAUX. Le SI sen sert ensuite pour agir

Objectif

Fonction, syntaxe et exemples

La fonction DATEDIF (pour les calculs d'ge) (voir le site de Laurent Longre sur [Link] DATEDIF fait partie des fonctions masques d'Excel. Elle n'est pas rfrence par l'aide en ligne. Elle est pourtant bien commode DATEDIF permet de calculer la diffrence entre deux dates en annes, mois et jours. Syntaxe : =DATEDIF ( Date1 ; Date2 ; Intervalle ) Cette fonction renvoie la diffrence entre Date1 et Date2 (Date2 >= Date1) selon l'argument Intervalle, qui peut prendre les valeurs suivantes : "y" : diffrence en annes "m" : diffrence en mois "d" : diffrence en jours "ym" : diffrence en mois, une fois les annes soustraites "yd" : diffrence en jours, une fois les annes soustraites "md" : diffrence en jours, une fois les annes et les mois soustraits =DATEDIF ( "5/4/1990" ; "15/8/99" ; Intervalle ) renvoie les valeurs suivantes selon la valeur de l'argument Intervalle : "y" : 9 (ans) "m" : 112 (mois) "d" : 3419 (jours) "ym" : 4 (mois restants, une fois les 9 ans soustraits) "yd" : 132 (jours restants, une fois les 9 ans soustraits) "md" : 10 (jours restants, une fois les 112 mois soustraits) La fonction DATEDIF peut tre en particulier utilise pour calculer des ges. Par exemple, si la cellule A1 contient une date de naissance et la cellule B1 la date du jour : - ge en annes simples : =DATEDIF(A1;B1;"y") & SI(DATEDIF(A1;B1;"y")>1;" ans";" an") - ge en annes et mois : =DATEDIF(A1;B1;"y") & SI(DATEDIF(A1;B1;"y")>1;" ans, ";" an, ") & DATEDIF(A1;B1;"ym") & " mois" - ge en annes, mois et jours : =DATEDIF(A1;B1;"y") & SI(DATEDIF(A1;B1;"y")>1;" ans, ";" an, ") & DATEDIF(A1;B1;"ym") & " mois, " & DATEDIF(A1;B1;"md") & SI(DATEDIF(A1;B1;"md")>1;" jours";" jour") Exemple :

Objectif

Fonction, syntaxe et exemples

On peut utiliser quelques autres fonctions de date trs pratiques, mais pas disponibles tout de suite Il faut dabord installer des modules additionnels : lancer Outils Macros complmentaires, et ajouter Utilitaire danalyse, sil nest pas dj en place [Link] ( date_dpart ; mois ) gnre une nouvelle date, corrige (en plus ou en moins) du nombre de mois spcifi Cette fonction est prcieuse pour calculer une date dchance ! Exemples :
[Link] ( DATEVAL("15/01/93" ; 1 ) gale 34015 ou 15/02/93 [Link] ( DATEVAL("31/03/93" ; - 1 ) gale 34028 ou 28/02/93

[Link] ( date_dpart ; date_fin ; jours_fris ) Renvoie le nombre de jours ouvrs entiers compris entre date_dpart et date_fin. Les jours ouvrs excluent les fins de semaine et toutes les dates identifies comme tant des jours fris. Utilisez [Link] pour calculer les charges salariales au prorata du nombre de jours travaills. jours_fris reprsente une plage facultative d'une ou plusieurs dates exclure du calendrier des jours de travail, comme les jours fris ou d'autres jours contractuellement chms. Exemple : [Link] ( DATEVAL("01/10/93") ; DATEVAL("01/12/93") ; DATEVAL("11/11/93")) gale 43 [Link](date_dpart;mois) Renvoie le numro de srie de la date du dernier jour du mois prcdant ou suivant date_dpart du nombre de mois indiqu. Utilisez [Link] pour calculer des dates d'chance tombant le dernier jour du mois. mois reprsente le nombre de mois avant ou aprs date_dpart. Une valeur de mois positive donne une date future, tandis qu'une valeur ngative donne une date passe. Exemples : [Link](DATEVAL("01/01/93"); 1) gale 34028 ou 28/2/93 [Link](DATEVAL("01/01/93"); -1) gale 33969 ou 31/12/92

Pour prvoir une formule conditionnelle, il faut utiliser la fonction SI

Objectif
Premire syntaxe et exemple

Fonction, syntaxe et exemples

(on peut ne pas prvoir de cas non ) :

SI (condition ; expression ) exemple : =SI ( B23 < 0 ; "Attention !" ) pour faire de la cellule un drapeau (flag) qui signale fortement un problme important, sans rien faire sinon SI (condition ; expression si la condition est vraie ; expression si cond. fausse )
Ici, pour viter la division par zro qui gnre un message derreur, on prvoit deux cas : une constante (zro) en guise de calcul, ou une formule (calcul de pourcentage).

Deuxime syntaxe Exemple :

(souvent, on prvoit un cas oui et un cas non ) :

=SI ( CATotal = 0 ; 0 ; B23 / CATotal )

Troisime syntaxe

(quelquefois, on est oblig dimbriquer les SI les uns dans les autres) :

Exemple : une entreprise consent une ristourne de fin danne ses meilleurs clients en fonction du CA cumul de lanne : si le CA est infrieur 10 000 F, pas de ristourne. Si le CA est compris entre 10 000 (inclus) et 20 000 (non compris), ristourne de 5% du CA. Si le CA gale ou dpasse 20 000 sans atteindre 30 000 F, ristourne de 8%. Si le CA est gal ou suprieur 30 000 sans galer 40 000 F, ristourne de 10%, ristourne de 15% dans les autres cas.

=SI(CA<10000;0;SI(CA<20000;0,05;SI(CA<30000;0,08;SI(CA<40000;0,1;0,15))))
Ici, on imbrique les SI : un SI complet est plac dans le cas non dun autre SI. Pour simplifier la rflexion pralable, il est fortement recommand de dessiner un petit graphe (segment de droite), voire mme un organigramme (cest vite fait et a montre bien les enchanements) :

Un autre schma, classique et assez clair, est lorganigramme : comparateur = <> > >= < <= inverse <> = <= < >= >

Objectif

Fonction, syntaxe et exemples

EXTRAIRE UNE VALEUR DUNE TABLE

Une des fonctions les plus importantes dans un tableur est la fonction RECHERCHEV, qui permet de rcuprer dans une

table une information stable, partir dune information saisie.

Excel propose 2 syntaxes pour 2 besoins trs diffrents :

RECHERCHEV ( valeur connue ; Table ; ColRendre ; FAUX ) pour les tables-fichiers (clients, fournisseurs, produits, agences, voyages, etc.)
Le 4
me

argument (FAUX) refuse les approximations : aucune diffrence ne doit tre accepte entre la valeur connue et ce qui est trouv dans la re 1 colonne de la table.

RECHERCHEV ( valeur connue ; Table ; ColRendre ; VRAI ) pour les tables-seuils et taux (taux de remises, de commissions, de primes, de TVA, etc.)
Le 4
me

argument (VRAI) accepte les approximations : il est tout fait possible (et mme probable) que la valeur connue ne corresponde pas re exactement un des seuils de la 1 colonne de la table.
Jai mis des espaces autour des points-virgules pour la lisibilit, mais en fait, il ne faut JAMAIS taper despace dans les formules !

Voyons dabord les tables-fichiers : je veux saisir un code et rcuprer les infos correspondant exactement ce code dans une table

Soit une table de rfrence, mmorise dans une feuille part, et nomme TableClients : La table (colonnes entires ! et non pas qqs lignes) est nomme. Un cas zro est prvu, pour viter des tests inutiles dans la formule de recherche.

Soit une autre feuille (ci-contre), dans laquelle je dois seulement saisir le numro du client (15 par exemple) :

Objectif
Excel est capable de rcuprer automatiquement toutes les infos relatives au client n15 ! Si je me trompe (en tapant 10 ou 13 qui sont des codes inexistants dans ma table Clients), Excel me signale que les valeurs sont Non Accessiblespar un message #NA. Tant que rien nest saisi en F4, Excel cherche zro (contenu de F4),le trouve (en dbut de table) et affiche les donnes correspondantes (des zros ou des espaces pour garder un affichage propre). Mon"cas zro" dans ma table me permet
dconomiser de nombreux SI pour garder des lignes vides tant que rien nest saisi !

Fonction, syntaxe et exemples

En tapant 15 en F4 (F4 est nomme CodeClientSaisi), D6 affiche toute seule Ets Durand Cest magique !!! Une autre formule du mme tonneau permet dafficher galement 1 cours Lieutaud en dessous

Voyons maintenant les tables-seuils et taux : je veux saisir un nombre et rcuprer dans une table la valeur correspondant la tranche laquelle appartient le nombre.

Objectif

Fonction, syntaxe et exemples

Soit une table de rfrence, nomme TableTauxRistournes qui contient les rgles dattribution des ristournes mes meilleurs clients : la formule exploitera cette table

Inutile ici de nommer des colonnes entires, car les perspectives de croissance de la table sont faibles En tapant 32 400 en B6 (B6 est nomme CA), B7 affiche toute seule 0,1 Cest Tant que rien nest saisi en B6, Excel cherche zro (contenu de B6), le simple !!! trouve (en dbut de table, D7) et affiche la valeur correspondante (un zro, ce Loutil Pourcentage multipliera ce rsultat qui naffiche rien si jai dsactiv laffichage des zros avec Outils Options). par 100 et lui ajoutera le signe % Avec
loutil Ajout de dcimale , on termine la mise en forme.

Objectif

Fonction, syntaxe et exemples

Avantages formidables de la fonction RECHERCHEV par rapport une imbrication de fonctions SI :

Grande simplicit de mise en uvre : la fonction RECHERCHEV, une fois apprivoise, est beaucoup plus simple taper et dbuguer, pluslisible, quun enchevtrement de SI De cette simplicit dcoulent rapidit et fiabilit !!! Il ny a pas photo ! On ne peut pas imbriquer les SI sans limites ! Alors que RECHERCHEV est sans limites ! Par ailleurs, la formule qui utilise RECHERCHEV peut tre protge sans gner lutilisateur novice (puisquon ne la modifiera jamais : seule la table fait lobjet des MAJ ventuelles), alors quune formule avec des SI imbriqus pose le problme de la dprotection ncessaire, en plus de la complexit de la correction, ce qui rend, en fait, les MAJ inaccessibles un novice ! Ensuite, la mise jour ventuelle des rgles (nouveaux seuils, nouveaux taux) est trs simple (donc rapide et fiable) directement,clairement, et une seule fois !, dans la table (sans toucher aux formules), alors quelle est complexe (donc lente et risque, ncessitantdprotection, rflexion, correction dans le fatras de la formule, recopie sans oubli sur les autres formules, reprotection, tests) avec des fonctions SI ! Il faut tre compltement masochiste pour imbriquer des SI dans les cas simples. Seul cas dans lequel il faut choisir dimbriquer des SI : taux appliqus cumulativement : quand on applique un 1er taux la 1re tranche PLUS un 2metaux la 2me tranche, PLUS un 3me taux la 3me tranche, etc.. Dans ce cas, il faudra se rsoudre imbriquer les SI ou bien construire une astucieuse imbrication de tables et de RECHERCH EV, mais on perdra une partie des qualits de simplicit lies la fonction (les qualits de simplicit de MAJ restant intactes).

Les applications de loutil RECHERCHEV sont infinies : tous les tableaux du monde () offrent un terrain dapplication de cette indispensable fonction (jexagre peine). Tous les utilisateurs dExcel devraient connatre cette fonction sur le bout des doigts Il faut tre capable de monter une fonction Recherche tout moment, sans mode opratoire, aussi simplement quon fait intellectuellement une addition, sans calculette Ceci est ESSENTIEL. Si on ne matrise pas RECHERCHEV, on est dbutant sous Excel.
Syntaxe reformule : RECHERCHEV (quoi chercher ; o chercher ; quoi rendre ; accepter ou pas les approximations) ou RECHERCHEV (valeur unique ; table de recherche ; n colonne ; faux ou vrai)
En franais, on demande Excel : Cherche cette valeur que je connais dans la premire colonne de la table dsigne, et rends-moi ce que tu trouves dans la colonne x de la table, sans accepter de diffrence entre ce que je cherche et ce que tu trouves dans la 1re colonne de la table .

Objectif

Fonction, syntaxe et exemples

On a vu que la fonction RECHERCHEV, utilise dans une table-fichier, peut ne pas trouver dans la premire colonne de la table ce qui a t saisi par loprateur (ceci va arriver lors de chaque erreur de saisie, par exemple). RECHERCHEV, dans ce cas, affiche un message #NA qui signale le problme #NA pour dire Non Accessible Mais ce message est bien obscur pour un oprateur peu expriment. Si vous voulez aider un oprateur novice, si ce nest pas vous qui allez utiliser le tableau, vous avez intrt intercepter cette erreur et ordonner laffichage dun message plus clair : vous allez utiliser la fonction ESTNA (prononcez AINA), qui sutilise uniquement en association avec la fonction SI : Syntaxe : Exemple : =SI ( ESTNA ( RECHERCHEV ( CodeClientSaisi ; TableClients ; 2 ; FAUX ) ) ; "Client inexistant" ; RECHERCHEV ( CodeClientSaisi ; TableClients ; 2 ; FAUX ) ) En franais, on dit Excel : si mon bloc-recherche rend #NA, alors affiche un message clair, sinon affiche le rsultat du bloc-recherche.
Dans cette formule assez longue, vous remarquerez que le bloc-Recherche est tap 2 fois Pas moyen de faire autrement, mais la saisie de la formule est quand mme trs simple si on connat bien son presse-papier et ses raccourcis claviers : je tape le dbut de la formule (toute la premire ligne de lexemple ci-dessus), et quand cest le moment de taper mon deuxime bloc-Recherche, je slectionne le premier avec la souris, je tape CTRL + C pour le mettre au presse-papier, je me replace en fin de formule avec la touche Fin, puis je tape CTRL + V pour vider le presse-papier Cest beaucoup plus long crire qu faire Essayez, vous serez vite convaincu de lefficacit de cette manipulation La cerise sur le gteau de lergonomie (cest--dire du confort) de mon tableau, cest dassocier un format conditionnel mon message derreur : si, en plus dafficher un message clair et lisible, ma formule passe en blanc gras sur fond rouge ds quelle affiche 'Client inexistant", cest spectaculaire et trs soign On va vous fliciter, cest sr ! Toujours en complment de la fonction RechercheV, il faut connatre INDEX et EQUIV qui prennent en charge les cas tordus dans lesquels Recherchev ne fonctionne pas En gros, EQUIV retrouve une valeur connue dans un vecteur (ligne ou colonne) et rend un rang (un numro dans la srie de cellules alignes) alors que INDEX est capable dutiliser ce rang pour trouver la cellule quivalente (de mme er rang) dans un autre vecteur (ligne ou colonne organise de la mme faon que le 1 vecteur). Cette combinaison permet de chercher dans une feuille et de trouver dans une autre feuille, mme dans un autre classeur si on veut ! On na plus la contrainte de la table unique, avec sa re 1 colonne qui est la seule colonne de recherche possible ! Syntaxe : EQUIV ( valeur_cherche ; zone_de_recherche ; type ) permet de localiser (par un rang) une valeur dans une ligne ou une colonne. Si type=0, EQUIV sarrte sur la premire occurrence trouve : le vecteur peut tre en dsordre. Si type = 1, il faut que le vecteur soit en ordre croissant. (Type = -1, vecteur tri dcroissant). INDEX ( zone_dextraction ; indice_Ligne ; indice_Colonne ) permet de piocher dans un tableau grce 2 indices numriques (un seul si la zone na quune dimension) INDEX ( vecteur_valeur__rendre ;EQUIV ( valeur_connue ; vecteur_de_recherche ) ) Exemple : INDEX ( SalairesNets ; EQUIV ( NomSalariSaisi ; NomsSalaris ; 0 ) )
On cherche et on trouve dans des colonnes places o lon veut : pas de contrainte Table et 1re colonne )

ESTNA ( expression ) rend VRAI ou FAUX, (ce qui fait que cette fonction na dintrt qu lintrieur dune condition de fonction SI)

Objectif

Fonction, syntaxe et exemples ARRONDI ( expression ; nbdcimales )

expression dsigne une formule ou une adresse ou autre moyen de gnrer une valeur
Si nbdcimales est suprieur 0 (zro), expression est arrondie au nombre de dcimales indiqu (au plus proche).

Certains calculs gnrent des

micro-dcimales :

Si nbdcimales est gal 0, expression est arrondie au nombre entier le plus proche. Si nbdcimales est infrieur 0, expression est arrondie gauche de la virgule ( la dizaine la + proche, la centaine, etc.)

Multiplier un prix deux dcimales par un taux de TVA ( 3 dcimales) donne un rsultat 5 dcimales. Seules les 2 1res dcimales sont significatives. Les 3 autres sont des microdcimales liminer. Mais il faut les liminer en profondeur avec une fonction ARRONDI, et pas seulement en apparence avec une commande Format ! Sinon, vous allez constater une diffrence entre la somme affiche par Excel (qui compte lesmicrodcimales) et la somme effectue avec unecalculette (qui ne les compte pas) !!! et vos partenaires ne vont pas aimer cette diffrence de centimes qui fait natre la suspicion !!!

Attention ! dans la ralit, bien sr, on ne tapera pas des chiffres en dur comme dans les exemples : on tapera une adresse ou une formule de calcul

Exemples ARRONDI(2,15; 1) gale 2,2 ARRONDI(2,149; 1) gale 2,1 ARRONDI(-1,475; 2) gale -1,48 ARRONDI(24,45; -1) gale 20

On peut forcer larrondi au suprieur ou linfrieur :

Les rgles sont les mmes quavec ARRONDI : on peut arrondir lentier en spcifiant zro en 2me argument. On peut aussi arrondir la dizaine, la centaine, etc.

[Link] (expression; nbdcimales ) [Link] (expression; nbdcimales )

ENT ( expression )
Arrondit un nombre l'entier immdiatement infrieur.

Pour extraire la partie entire dun nombre, on peut utiliser la fonction ENT ou la fonction TRONQUE
Ces deux fonctions se ressemblent, mais ont desractions diffrentes en dessous de zro.

Exemples :

ENT(8,9) gale 8 ENT(-8,9) gale 9

TRONQUE ( expression ; no_chiffres ) Tronque

un nombre en supprimant la partie dcimalede ce nombre de sorte que la valeur renvoye par dfaut soit un nombre entier.

Exemples :

TRONQUE(8,9) gale 8 TRONQUE(-8,9) gale -8

MOD ( expression ; diviseur )

Pour calculer le reste dune division, il faut utiliser la fonction MOD
Renvoie le reste de la division de l'argument expression par l'argument diviseur. (Le rsultat est du mme signe que diviseur.)

Exemple :

MOD(3 ; 2) gale 1

Objectif
Pour faire la somme seulement de certaines cellules dune plage, il faut absolument connatre la trs prcieuse fonction [Link]
nomme !

Fonction, syntaxe et exemples

Syntaxe :
Attention au test : guillemets, sauf si simple galit une zone

[Link] ( zone tester ; test ; zone sommer )

Exemple : [Link] ( Catgorie ; "cadres " ; Salaires)
va dgager la masse salariale des cadres de la colonne de tous les salaires, pourvu quune colonne voisine contienne de quoi faire la distinction (ici, cest une colonne qui affiche le texte de la catgorie en clair, mais ce pourrait tre nimporte quel autre indice cod, numrique ou autre)

Le troisime argument est facultatif : on ne le remplit pas quand la zone tester est la mme que la zone sommer.

La fonction [Link] est galement souvent trs utile ! Elle permet de dnombrer les cellules dune colonne qui rpondent une condition (simple).
Cette fonction na que 2 arguments car la zone tester est toujours la mme que la zone dnombrer.

Exemple : [Link] ( MontantVente ; ">=10000" )

va dgager de la colonne des montants individuels de chaque vente, le CA global des grosses ventes (>10 000F)

Exemple : [Link] ( TxTVATVA ; Montant )

TxTVA1 ;

va sommer les seuls montants pour lesquels le taux de TVA est gal au taux contenu dans la cellule nomme TxTVA1.

Syntaxe : [Link] ( zone tester et dnombrer ; test ) Exemples : =[Link] ( notes ; ">=10" ) compte les
bonnes notes de la dernire interrogation crite Autre exemple : =[Link] ( flag ; 1 ) compte le nombre de cellules de la colonne "flag" (drapeau) qui signalent un problme (en affichant le
chiffre 1) Cette astucepermet de dnombrer des lignes avec des conditions complexes (plusieurs SI imbriqus qui dbouchent sur la valeur 1 si le problme est constat), ce que ne permet pas [Link] seule.

DROITE ( texte ; nbcar ) : Renvoie les x derniers Pour manier les chanes de caractres, il faut connatre les fonctions texte. On a souvent besoin de les utiliser ensemble, par des combinaisons astucieuses
caractres droite d'une chane de texte.

Exemple : DROITE ( ) gale "vente"

"Prix

de

vente";

GAUCHE ( texte ; nbcar ) : idem lenvers NBCAR ( texte ) : Longueur (nb de car) d'une
chane.

Exemple : NBCAR ( "Prix de vente" ) gale 13 On noubliera pas que : loprateur de concatnation est le et commercial ) STXT ( texte ; numdpart ; nbcar )

&

(on dit perluette ou

Renvoie une sous-chane de n caractres extraits d'un texte partir de la position que vous avez spcifie.

Exemple : STXT ("Cours moyen"; 1; 5 ) gale

"Cours" STXT ("Cours moyen"; 7; 20 ) gale "moyen"

Objectif

Fonction, syntaxe et exemples

Dans les exemples ci-dessus, on connat le point de dpart et la longueur de la chane voulue, or ces infos sont souvent variables La fonction suivante est donc essentielle ! Elle sert localiser un caractre donn dans la chane : CHERCHE ( texte_cherch ; texte ; no_dpart )
Renvoie le numro du caractre au niveau duquel un texte cherch est initialement reconnu lors d'une lecture de gauche droite. Vous pouvez utiliser les caractres gnriques (jokers), le point d'interrogation (?) et ltoile (*) dans l'argument texte_cherch. Le point d'interrogation correspond un caractre unique quelconque et ltoile correspond une squence de caractres quelconque. Si vous voulez trouver rellement un point d'interrogation ou un astrisque, tapez un tilde (~) devant ce caractre.

Exemple :

Si

la

cellule B17 contient le mot profit et CHERCHE ( $B$17 ; $A$14 ) gale 11

la

cellule A14

l'expression

Pertes

et

profits :

Utilisez la fonction CHERCHE pour trouver la position d'un caractre ou d'une chane de texte dans une autre chane de texte de faon pouvoirutiliser ensuite l'une des fonctions STXT (ou SUBSTITUE) pour modifier le texte.

TROUVE ( texte_cherch ; texte ; no_dpart ) la fonction TROUVE, la diffrence de la fonction CHERCHE, respecte les majuscules et les minuscules et n'admet pas de caractre gnrique. MINUSCULE ( texte ), MAJUSCULE ( texte ), NOMPROPRE ( texte ) sont faciles comprendre seul SUBSTITUE ( texte ; ancien_texte ; nouveau_texte ; no_position )
Exemples : SUBSTITUE("Donnes Ventes"; "Ventes"; "Cot") gale "Donnes Cot" SUBSTITUE("Trimestre 1 - 1991"; "1"; "2"; 1) gale "Trimestre 2 - 1991" SUBSTITUE("Trimestre 1 - 1991"; "1"; "2"; 3) gale "Trimestre 1 - 1992"

Objectif

Fonction, syntaxe et exemples

Exemple de combinaison : Je viens de rcuprer chez un ami un classeur dans lequel la mme colonne contient la fois les noms et les prnoms des gens, et dans le mauvais ordre Or je veux rcuprer ces infos (sans les resaisir, bien sr), en mettant le prnom avant le nom (lui-mme en majuscules) :

Bien sr, le dcoupage de la formule en 7 colonnes est l pour expliquer progressivement les possibilits On pourrait aussi bien arriver au mme rsultat en une seule formule (un peu moins digeste, il est vrai) : =STXT(A3;1;CHERCHE(" ";A3))&" "&DROITE(A3;NBCAR(A3)-CHERCHE(" ";A3;CHERCHE(" ";A3)+1))&" " &MAJUSCULE(STXT(A3;CHERCHE(" ";A3);CHERCHE(" ";A3;CHERCHE(" ";A3)+1)-CHERCHE(" ";A3)))

Objectif
Excel les offre des outils nombreux pour

Fonction, syntaxe et exemples

Combien priode ? faudra-t-il rembourser chaque

calculs financiers.
Toutes les situations demprunt se dfinissent avec 5 variables :

Nous nous limiterons aux fonctions simples qui tournent autour des simulations demprunts et dpargne.

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

Exemple : cette formule renvoie la valeur du remboursement mensuel d'un emprunt de10 000 F au taux annuel de 8 % remboursable en 10 mois : =VPM ( 8%/12 ; 10 ; 10000 ) gale 1 037,03 F

VA = valeur actuelle
Ce que reprsente aujourdhui lensemble des remboursements futurs. Par exemple, le montant de lemprunt pour le prteur.

Combien faut-il rembourser ?

prvoir

de

temps

pour

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

Vc reprsente la valeur atteindre aprs le dernier versement : 0 si cest un emprunt, un nombre si cest une pargne. Exemple : la formule suivante renvoie le nombre de mensualits pour le remboursement mensuel d'un emprunt de 10 000 F au taux annuel de 12 % raison de 1000 F par mois :

VC = valeur future
Valeur capitalise, avec les intrts

VPM = montant des remboursements

Valeur des montants priodiques (par an, par mois)

NPM = nombre des remboursements

Nombre des priodes pour des remboursements constants, un taux dintrt constant. Si on rembourse tous les mois un emprunt sur 4 ans, NPM = 48 (4 ans x 12 mois par an).

TAUX = taux de lemprunt

Taux dintrt par priode (anne, mois) Le taux doit tre en rapport avec les priodes : Si on rembourse tous les mois, un taux annuel devra tre divis par 12 pour donner Excel un taux mensuel.

=NPM(12%/12; 1) gale 11 (11 mensualits)

-1000;

10000;

0;

Combien vaudra mon pargne ? VC ( taux ; npm ; vpm ; va ; type )

Renvoie la valeur future d'un investissement remboursements priodiques et constants, et un taux d'intrt constant. Exemple : Supposons que vous vouliez conomiser de l'argent pour financer un projet spcifique qui sera mis en oeuvre dans un an. Vous dposez 1 000 F sur un compte d'pargne qui vous rapporte 6 % d'intrts par an, capitaliss mensuellement, ce qui reprsente un intrt mensuel de 6%/12, soit 0,5%. Vous envisagez de dposer 100 Francs au dbut de chaque mois pendant les 12 mois venir. Quel sera le montant de votre pargne au bout des 12 mois ? =VC( 0,5% ; 12 ; -100 ; -1000 ; 1) gale 2 301,40 F

Il faut connatre 4 variables pour calculer la cinquime, linconnue Excel propose donc 5 fonctions : une fonction par cas de figure, une fonction par variable inconnue. Remarques gnrales (pour toutes les fonctions) :

Les

sommes reues sont positives, alors que les sommes verses sont ngatives. On peut se placer du point de vue de lemprunteur ou au contraire du prteur il suffit dadapter les signes

Largument Type = 0 sert dire que les remboursements ont lieu en fin de priode. Type = 1 => dbut de priode.

Quel est le taux dun emprunt ?

TAUX ( npm ; vpm ; ; estimation )

va

; vc ;

type

La formule suivante donne le taux d'intrt d'un emprunt de 80 000 F sur 4 ans (48 = 4 x 12 mois) avec des remboursements mensuels de 2 000 F :

=TAUX ( 48 ; -2000 ; 80000 ) gale 0,77 %

Quelle est la investissement ?

valeur

actuelle

dun

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

Exemple : une compagnie financire vous propose un investissement qui vous rapportera 5 000 F la fin de chaque mois pendant les vingt prochaines annes. Le montant de l'investissement est de600 000 F et le taux

Objectif

Fonction, syntaxe et exemples

II - OPRATEURS
Opration Oprateur

RETOUR EN HAUT Exemples et remarques

Addition Soustraction Multiplication Division

+ * / ^ : ; :

=B4+B$5

=TotalHT+FraisPort

On peut utiliser ladressage de type bataille navale (ligne/colonne) (avec ou sans dollars $), mais il est souvent plus lisible dutiliser les noms de cellules

=B4-B$5 =B4*B$5 =B4/B$5

=TotalHT-Remise =TotalHT*TauxTVA*1,01 =Total/B3

Les 4 oprateurs arithmtiques sont en double sur le clavier : pav alpha et pav numrique.

Puissance Changement de signe Domaines de cellules

Le signe ^ est saisi en tapant AltGr 9 (sur le pav alpha) Exemple : 10 scrit 10^2 sous Excel. En plaant un moins devant une expression, on en change le signe.
On peut dsigner des groupes de cellules :

Le signe deux-points ( : ) dit jusqu . Par exemple, A1:C4 dit toutes les cellules de A1 jusqu C4 . Le signe point-virgule ( ; ) signifie et . Par exemple, A1;C4 dit les cellules A1 et C4 . Quand on travaille sur une seule feuille, on parle de 2D (lignes/colonnes). Quand on ajoute des liens vers dautres feuilles, on utilise une 3 me dimension. Les formules qui joue de cette possibilit sont des formules "3D". Par exemple, on peut additionner toutes les cellules C3 des feuilles Janvier Dcembre d ans la cellule C3 dune 13me feuille : on crira : =SOMME('Janvier : Dcembre' ! C3) Sans parenthses, Excel effectue les oprations dans lordre suivant : Ngation (comme dans 1) % Pourcentage ^ Exposant * et / Multiplication et division + et Addition et soustraction & Concatnation de deux chanes de texte = < > <= >= <> Comparaison

Formules 3D

Ordre de calcul

Ordre forc Comparaisons

( )
> >=

Avec les parenthses, on impose lordre des oprations. Lopration =3*2+10 donne 16 (XL fait dabord la multiplication), alors que =3*(2+10) donne 36 (on force XL commencer par laddition) Ceci est essentiel. Les fonctions SI principalement, mais aussi quelques commandes et autres outils, utilisent un desoprateurs de comparaison pour exprimer une condition : ci-contre, on lit :

< <= = <>

suprieur > suprieur ou gal >= infrieur < infrieur ou gal <= gal = diffrent <>. Lors dune recopie de formule, Excel transforme tout seul la formule pour quelle fonctionne encore sa nouvelle place Par exemple, si on recopie vers le bas, Excel change la partie ligne des adresses de cellules dans la formule, pour que la formule fonctionne en sadaptant sa nouvelle ligne. Quand une partie de la formule pointe vers une cellule de rfrence qui doit tre la mme dans toutes les formules (TauxTVA par exemple), il faut bloquer le mcanisme de transformationautomatique de ladresse quexcel excute lors de la recopie : en plaant le signe dollar $ devant un numro de colonne ou un numro de ligne, on fige ce numro : Par exemple, $A$1 est une adresse compltement fige : on peut la recopier dans tous les sens, elle restera $A$1 Autre exemple : Dans A$1, seule la ligne (1) est fige, pas la colonne (pas le A) : si on recopie vers le bas, le 1 reste 1, mais si on recopie vers la droite, le A devient B, C, D, etc. La touche utile est F4 ! Cest prcieux pour ne crer quune seule formule pour tout un tableau : par exemple, pour calculer des pourcentages ci-dessous, on ne cre quune seule formule, utilisant astucieusement le dollar $. Puis on la recopie dabord la cellule vers le bas, puis toute la colonne vers la droite :

Figer une adresse pour la prochaine recopie

Prcieux oprateur qui permet d additionner des textes : Si je veux quune cellule agrge, compile, rassemble le prnom et le nom contenus dans des cellules spares, je cre la formule : =Prnom&Nom

Concatnation

&

Pour ajouter des constantes (par exemple, des espaces), on les encadre de guillemets " ". Exemple : =B3&" "&A3 permet de ne pas coller le nom et le prnom.

III IV - POINTEURS
Nous allons voir ici : A. La croix blanche pour slectionner B. La flche blanche pour dplacer ou copier C. La croix noire pour copier en incrmentant D. Le i noir pour placer le point dinsertion E. Le double trait - double flche pour les rglages de largeurs /hauteurs F. Les pointeurs pour les objets graphiques : a. Le pointeur 4 points cardinaux pour dplacer ou copier b. Les pointeurs double flche noire pour redimensionner
RETOUR EN HAUT

A - CROIX BLANCHE POUR SLECTIONNER

La grosse croix blanche apparat quand on vise le corps des cellules. Elle signale quExcel est prt slectionner des cellules (les mettre en surbrillance) ds quon va cliquer ou glisser avec la souris :

Notez bien : slectionner nest pas agir ! Si vous avez mal slectionn, il ne faut pas essayer dannuler une slection avec Ctrl Z (vous annuleriez la commande prcdente, ce qui na rien a voir). Non, en cas derreur de slection, il suffit de slectionner nouveau : clic ou glissade avec la grosse croix blanche, et une slection chasse lautre

Pour bien comprendre quel moment le pointeur change dapparence, il faut intellectuellement rduire la croix son centre. Imaginez que la croix blanche a un centre rouge lumineux Il ny a que cette lumire rouge qui compte

B - FLCHE BLANCHE POUR DPLACER OU COPIER

La flche blanche apparat quand on vise le bord noir dune slection. Elle signale quExcel est prt dplacer/copier des cellules ds quon va glisser avec la souris (avec ou sans la touche Ctrl) :

Intellectuellement, il faut rduire la flche sa pointe. Imaginez que la pointe a un bout rouge lumineux Il ny a que cette pointe lumineuse qui compte Vous comprendrez mieux quel moment le pointeur change dapparence.

C - CROIX NOIRE POUR COPIER EN INCRMENTANT

La croix noire apparat quand on vise le coin bas droit dune slection. Elle signale quExcel est prt copier la slection en incrmentant ds quon va glisser avec la souris :

Attention : Cet outil de recopie crase tout sur son passage : je lappelle recopie rouleau compresseur Par ailleurs, Excel nincrmente que quand il le peut (sinon il recopie mais nincrmente pas) : il connat lundi, mardi janvier, fvrier il est capable de prolonger la suite Rgion 1 il est mme capable dutiliser vos suites personnelles(cres avec la commande Outils Options,
onglet Listes pers.)

Mais quelquefois, il faut slectionner 2 cellules de dpart pour quExcel puisse deviner la suite :

Intellectuellement, il faut rduire la croix son centre. Imaginez que la croix noire a un centre rouge lumineux Il ny a que cette lumire rouge qui compte Vous comprendrez mieux quel moment le pointeur change dapparence.

D - i NOIR POUR PLACER LE POINT DINSERTION

Le i majuscule noir apparat quand on vise une zone de saisie de texte (la barre de formule, souvent).

Il signale quExcel est prt placer le point dinsertion l o on va cliquer avec la souris :

Intellectuellement, il faut rduire le i noir son centre. Imaginez que le i a un centre rouge lumineux Il ny a que ce point lumineux qui compte Vous comprendrez mieux quel moment le pointeur change dapparence.

E - DOUBLE TRAIT - DOUBLE FLCHE POUR LES RGLAGES DE LARGEURS /HAUTEURS

Le pointeur double trait-double flche apparat quand on vise une zone de rglage de volet (entre deux zones gomtrie variable ). Il signale quExcel est prt dplacer le trait de sparation partir de l o on va glisser avec la souris :
Largeur et hauteur des lignes et des colonnes

Cration, rglage et suppression dun volet

Tailles respectives de la zone des onglets et de celle des ascenseurs

Comme dhabitude, pour comprendre les transformations de votre pointeur, rduisez-le intellectuellement son centre de gravit (son centre, souvent)

A - POINTEURS SUR OBJETS GRAPHIQUES

Enfin, sur tous les objets dessins (les formes automatiques, les graphiques, les images importes, les photos, les organigrammes, les zones de texte, les WordArts, etc.), le pointeur peut prendre deux apparences, suivant que lon vise le corps de lobjet ou ses poignes (8 carrs placs autour de lui aprs avoir cliqu dessus) :

Soit cest le pointeur 4 points cardinaux qui permet de dplacer/copier lobjet graphique (pour copier, il faut glisser en gardant Ctrl enfonce) :

Soit cest le pointeur double flche noire qui permet de redimensionner lobjet :