DIP, Universit Stendhal, Grenoble III *Cours Tableur 6

Fonctions plus complexes dans ExcelDpartement Informatique PdagogiqueUniversit Stendhal, Grenoble III

Sandra Michelet, Sandra.Michelet@imag.fr

DIP, Universit Stendhal, Grenoble III *Partie 1Fonctions Si(), Et(), Ou() et fonctions imbriques

Sandra Michelet, Sandra.Michelet@imag.fr

DIP, Universit Stendhal, Grenoble III *I.1. Introduction Parmi les fonctions dj dfinies dans Excel, certaines sont trs intressantes car on peut demander Excel de ragir diffremment en fonction du rsultat dun calcul demand. Nous allons voir plus en dtail le cas de 3 fonctions: si(), et(), ou()

DIP, Universit Stendhal, Grenoble III *I.2. Oprateurs logiquesI.2.1. Dfinition Les oprateurs logiques (ou oprateurs de comparaisons) permettent de comparer deux valeurs et gnrent la valeur logique VRAI ou FAUX.

Oprateurs logiquesSignification=galStrictement suprieur =Suprieur ou gal Diffrent de

DIP, Universit Stendhal, Grenoble III *I.2. Oprateurs logiquesI.2.2. ExempleOn considre le tableau suivant

Voici la valeur logique retourne pour chacune des formules saisies dans une cellule autre que celles intgres dans les formules

Oprateurs logiquesExempleSignificationValeur logique retourne==(A3=B3)Le contenu de la cellule A3 est-il gal celui de B3?VRAIA2)Le contenu de la cellule A1 est-il strictement suprieur celui de A2?FAUX=B3) Le contenu de la cellule A3 est-il suprieur ou gal celui de B3?VRAI=(A3B3) Le contenu de la cellule A3 est-il diffrent de celui de B3?FAUX

DIP, Universit Stendhal, Grenoble III *I.3. Fonction SI()Principe :La fonction SI() permet d'excuter une opration si la condition est remplie et une autre si cette condition n'est pas remplie.

Exemple :SI la moyenne annuelle dun lve en 1re anne est suprieure 10 ALORS il passe en 2me anne, SINON il redouble.

Syntaxe :

La condition est toujours soit vraie, soit fausseLes ; sparent les diffrents arguments de la fonction=SI(Condition; Opration effectuer si VRAI; Opration effectuer si FAUX)

DIP, Universit Stendhal, Grenoble III *I.3. Fonction SI()Exemples :

Remarque: si vous utilisez du texte dans les formules, le mettre entre guillemets " "

ExempleSignification=Si(A1>10;"bon rsultat"; "chec") SI le contenu de la cellule A1 est suprieur 10 ALORS crire bon rsultat, SINON crire chec=Si(B2="russite";"lve admis"; "lve recal") SI le contenu de la cellule B2 est gal au texte russite ALORS crire lve admis, SINON crire lve recal

DIP, Universit Stendhal, Grenoble III *I.4. Fonctions ET() , OU()Principe :La rponse dExcel aux fonctions ET() et OU() est VRAI ou FAUX

Syntaxe :

Renvoi :

=ET(Condition1; Condition2;)=OU(Condition1; Condition2;)La fonction ET() renvoie VRAI si toutes les conditions sont vraiesLa fonction OU() renvoie VRAI si au moins une condition est vraie

DIP, Universit Stendhal, Grenoble III *I.4. Fonctions ET() , OU()Tables de vrit de ET et OU :Soient A et B deux conditions valant vrai ou faux, on a les tables de vrit suivantes :

Exemples :

ABA et BVraiVraiVrai VraiFauxFaux FauxVraiFaux FauxFauxFaux

ABA ou BVraiVraiVrai VraiFauxVraiFauxVraiVraiFauxFauxFaux

ExempleCe que lon testeValeur logique retourne=ET(A1=44; A2=B2)Le contenu de la cellule A1 est gal 4 ET le contenu de la cellule A2 est gal celui de la cellule B2FAUX=OU(A3= "OUI";B3 =2)Le contenu de la cellule A3 est gal au texte "OUI" OU le contenu de la cellule B3 est gal 2VRAI

DIP, Universit Stendhal, Grenoble III *I.5. Fonctions imbriquesIl se peut que votre condition de la fonction SI comporte des ET ou des OU, ou alors dautre SI.

On parle de fonction imbrique si lintrieure dune fonction, vous utilisez une autre fonction.

Les formules suivantes vous donnent un aperu de fonction imbrique (formules saisies dans une cellule autre que celles intgres dans les formules) :

=SI(A1=A2; B3; Moyenne(A1:A5))=SI(C2="bonjour"; SI(C3="madame"; "bonjour madame"; "bonjour monsieur"); "au revoir")=SI(ET(A1=4; A2=B2); B1;C2)=SI(ET(D2="Oui"; E2="Oui"); C2+7; SI(OU(D2="Oui"; E2="Oui"); C2+5; C2))

DIP, Universit Stendhal, Grenoble III *Partie 2Fonctions Somme.Si() et Nb.Si()

Sandra Michelet, Sandra.Michelet@imag.fr

DIP, Universit Stendhal, Grenoble III *II.1. Fonction SOMME.SI()Principe :La fonction Somme.Si() permet dadditionner des contenus de cellules spcifies si elles rpondent un critre donn

Syntaxe :

Les contenus de cellules de la plage somme_plage seront additionnes si et seulement si les contenus des cellules correspondantes situes dans plage rpondent au critreExemple : =Somme.Si(plage de cellules; critre; somme_plage)=Somme.Si(A2:A5; " >160 000 " ; B2:B5) signifie : On somme les contenus des cellules B2 B5 si et seulement si les contenus des cellules A2 A5 sont suprieurs 160 000Ici cela revient sommer le contenu des cellules B3,B4 et B5

DIP, Universit Stendhal, Grenoble III *II.2. Fonction NB.SI()Principe :La fonction Nb.Si() permet de dterminer le nombre de cellules dont le contenu nest pas vide, rpondant au critre

Syntaxe :

Exemple : On dispose dun classeur contenant les rponses de 100 personnes interroges un sondage. On a reprsent par 1 le fait que la personne sonde soit une femme et par 0 si cest un homme. Ces donnes se trouvent dans la plage de cellule allant de A1 A100.Nombre de femmes ayant particip lenqute :=Nb.Si(A1:A100; "1" ) Nombre dhommes ayant particip lenqute := Nb.Si(A1:A100; "0" ) =Nb.Si(plage de cellules; critre)

DIP, Universit Stendhal, Grenoble III *Partie 3Fonctions =RechercheV() et =RechercheH()

Sandra Michelet, Sandra.Michelet@imag.fr

DIP, Universit Stendhal, Grenoble III *III.1. PrincipeLes fonctions =RechercheV() (V pour Verticale, cest--dire base sur les colonnes) et =RechercheH() (H pour Horizontale, cest--dire base sur les lignes) vous permettent de:chercher un lment du tableaupiocher des lments dun autre tableau pour les intgrer au vtre

DIP, Universit Stendhal, Grenoble III *III.2. SyntaxeLes fonctions =RechercheV() et =RechercheH() ont la mme syntaxe

O:Argument1 est llment de la 1re colonne (ligne pour RechercheH() ) connueArgument2 est la slection de la plage de donnes qui sera balaye pour trouver linformation recherche partir de la colonne 1Argument3 est le numro de la colonne (de la ligne dans le cas de =RechercheH())du rsultat cherchArgument4 est lautorisation de valeur approche. Argument4 a pour valeur oui (=VRAI) ou non (=FAUX)=RechercheV(Argument1;Argument2;Argument3;Argument4)

DIP, Universit Stendhal, Grenoble III *III.3. Attention aux arguments ! (1/3) Concernant la fonction =RechercheV(), le 1re colonne du tableau balay doit tre tri par ordre croissant. Cette remarque ne sapplique pas dans le cadre de la fonction =RechercheH()

Argument2 - Argument3 :Avec Argument2 vous dfinissez le tableau sur lequel va porter la recherche. La 1re colonne de ce tableau aura pour numro 1. De ce fait, Argument3 est la colonne de ce nouveau tableau et pas du tableau initial.

DIP, Universit Stendhal, Grenoble III *III.3. Attention aux arguments ! (2/3)Exemple: =RECHERCHEV(G2 ;B1:E4; 4 ; VRAI)

Dcortiquons la formule :On recherche la valeur 20 (valeur de G2) dans la plage de cellules B1:E4. Le rsultat sera pris dans la 4me colonne de cette plage (soit dans la colonne E et non pas dans la colonne D). Comme on veut une valeur approche, la valeur la plus proche de 20 est 15. Ainsi, le rsultat de la formule renvoie Terre (car le rsultat est la valeur dune cellule appartenant la colonne E)

DIP, Universit Stendhal, Grenoble III *III.3. Attention aux arguments ! (3/3) Argument4 :Par dfaut, cest--dire si vous ncrivez rien, cet argument est cod avec la valeur VRAI. Cet argument demande si on souhaite une valeur approche (dans ce cas VRAI) ou non (FAUX).

Si on considre lexemple prcdent, dans la formule =RECHERCHEV(G2 ;B1:E4; 4 ; VRAI), on accept de prendre une valeur approch de la valeur de G2 (la valeur la plus proche). Maintenant, si on crit la place =RECHERCHEV(G2 ;B1:E4; 4 ; FAUX), Excel va rechercher dans le tableau B1:E4, la valeur 20. Comme elle nexiste pas, Excel ne pourra pas faire la recherche et va renvoyer le message: #N/A