Fonctions spécialisées pour l\'examen C2I PDF

Preview

Summary

Des fiches afin de s'exercer à l'examen de C2I....

Description

LES FONCTIONS SPCIALISÉES Ouvrez le document Conditions2 Dans ce tableau, vous avez 3 primes à calculer, indépendantes les unes des autres et faisant appel à des opérateurs logiques différents. (ET, OU)

Le ET logique Calcul de la première prime : on accorde une prime de 76 € aux personnes qui ont plus de 2 enfants et qui gagnent moins de 1 500 €. La syntaxe du ET est la suivante : ET(condition1;condition2;…;condition30) Une fonction ET peut contenir jusqu'à 30 conditions maximum La fonction ET sera intégrée dans la fonction SI selon la syntaxe suivante : =SI(ET(condition1;condition2;…;condition30);Action 1;Action 2) Pour que l'action 1 soit exécutée, toutes les conditions doivent être vraies. Définissons les différents éléments de la prime 1 CONDITION : avoir plus de 2 enfants et moins de 1500 € de salaire Affichage si VRAI (la condition est vérifiée) : 76 € Affichage si FAUX (la condition n’est pas vérifiée) : rien ("")

•

Cliquez en E4

•

Saisissez =SI(ET(D4>2;B430;C43

3

Il faut remplir l'une des 2 conditions pour avoir la prime. La balle est dans votre camp, à vous de jouer !!! •

Calculez la prime 3.

Pour vous aider : Il faudra de toute évidence associer les deux opérateurs ET et OU dans la formule, mais dans quel ordre ? Il faudra soit incorporer le ET à l’intérieur du OU, soit incorporer le OU à l’intérieur du ET. Pensez que le problème principal est : l’âge OU le nombre d’enfants.

Compter ou additionner si… Vous savez additionner les nombres avec la fonction SOMME, les compter avec la fonction NB, tester avec la fonction SI. Il peut être fort utile d’associer ces différentes fonctions : Ouvrez le fichier calculs_si

Vous avez ici une liste de personnes, avec leurs numéros de sécurité sociale et leurs chiffres de ventes. Supposons que nous souhaitions compter les hommes ou les femmes… Il faudrait utiliser la fonction NB, mais on compterait dans ce cas tout le monde sans distinction de sexe… Du coup, il serait nécessaire de passer par deux phases : ajouter pour commencer une colonne dans laquelle on testerait sur chaque ligne (à partir du numéro de sécurité sociale) si le vendeur est un homme, une autre colonne pour tester si le vendeur est une femme, et en bas de chaque colonne, on pourrait enfin compter avec NB… lourd, non ? NB, SI… justement, le tableur propose une fonction NB.SI ! Voyons cela de plus près. Pour connaître le nombre d’hommes, nous souhaitons compter (NB) les cellules si (SI) le numéro de sécurité sociale commence par 1.

Cliquez sur la cellule C21 •

Appelez l’assistant fonctions (fx )

•

Connaissant le nom de la fonction (NB.SI), il suffit de le saisir dans la zone de recherche et de cliquer sur le bouton OK (en haut à droite de la boîte)

•

Cliquez sur le bouton OK du bas pour passer à l’écriture de la formule

Deux paramètres sont proposés : Plage permet de définir la zone à compter et à tester Critère permet de préciser la condition à remplir •

En vérifiant que vous êtes bien sur la zone Plage, sélectionnez les numéros de sécurité sociale, A2 :A19

•

Cliquez sur la zone Critère, et saisissez 1*

NOTA : L’astérisque * permet de remplacer une série de caractères. Ainsi, en tapant 1*, on sélectionne tous les numéros de sécurité sociale commençant par 1 (donc les hommes) •

Validez

La formule obtenue est : =NB.SI(A2:A19;"1*") où A2:A19 est la zone à compter, et "1*" est la condition à remplir. Remarques : La fonction NB.SI correspond à NBVAL (qui compte toutes les cellules non vides) et non pas à NB (qui compte les nombres seulement)

4

L’action est saisie entre guillemets : "1*". Si l’action est un nombre seul, on le tapera avec ou sans guillemet, si c’est un texte ou une comparaison, on tapera entre guillemets. Exemples : 50 "50" ">20" "voiture" •

De la même façon, comptez en C22 le nombre de femmes

On peut compter, mais aussi additionner… •

Cliquez sur la cellule C24

•

Avec la fonction SOMME.SI, calculez le total des ventes supérieures à 2000

=SOMME.SI(C2:C19;">2000") Le résultat doit être 13 450 1. Utilisez l’assistant fonction pour en savoir plus sur la fonction SOMME.SI Vous remarquerez qu’il est possible d’utiliser un troisième paramètre : somme_plage. Il permet, si on l’utilise, de différencier la zone à totaliser de la zone à tester. Ainsi, si vous souhaitez connaître le total des ventes des hommes, la zone à totaliser est la colonne des ventes, tandis que la zone à tester est la colonne des numéros de sécurité sociale. La syntaxe peut donc se résumer à : SOMME.SI ( zone à tester ; condition ; zone à additionner ) •

Utilisez la fonction SOMME.SI pour totaliser les ventes des hommes en C25 et le total des ventes des femmes en C26

Mais encore… Le SI, c’est bien… avec le OU et le ET, c’est encore mieux… mais parfois, ça ne suffit pas ! Notes Décision BARTHOD

2,5

DESHAYES

12

DURRENBERGER

19

FREMOND

5

GONET

18

GUILLOU

9

LEDO

8

PORQUIER

13

En effet, jusqu’à présent, nous avons pu prendre en compte une condition unique avec le SI, plusieurs conditions grâce aux opérateurs OU et ET, mais nous n’avons pu à chaque fois effectuer que deux actions, alors que trois actions ou plus peuvent s’avérer nécessaires. Dans le tableau ci-dessus, on souhaite ajouter la décision du jury en fonction de la note : Admis pour les notes égales ou supérieures à 10, Oral pour les notes comprises entre 8 et 10, et Refusé pour les notes en dessous de 8… De toute évidence, notre SI ne suffit plus, puisque trois actions sont envisagées : Admis, Oral, Refusé. Pour nous en sortir, il sera nécessaire d’utiliser plusieurs SI imbriqués, mais c’est une autre histoire… En attendant, place à la synthèse !

5

Les SI imbriqués Ici, vous avez trois actions à afficher, mais ne pouvez utiliser ni le ET ni le OU

Imbriquer les fonctions SI

Voici la syntaxe de la fonction SI :

=SI(CONDITION ; ACTION 1 ; ACTION 2) Possibilité d'imbrication des fonctions SI A chaque fois qu'il y a action, on peut imbriquer une nouvelle fonction SI

=SI(CONDITION1 ; ACTION1 ;

SI(CONDITION2;ACTION2;ACTION3) ) Calcul exécuté si condition 1 fausse

Exemple :

=SI(CONDITION1 ; ACTION1 ; =SI(B4>=10 ; "Admis" ;

SI(CONDITION2 ; ACTION2 ; ACTION3) )

SI(B4>=8 ; "Oral" ; "Refusé"))

Exercice : •

Ouvrez le classeur anciennete

La feuil1 de ce classeur contient une liste de salariés et leur ancienneté. •

Affichez le montant de la prime d'ancienneté en tenant compte des paramètres suivants : 

Moins de 5 ans d'ancienneté : pas de prime



De 5 à 9 ans : 150 € de prime



De 10 à 13 ans : 225 € de prime



Plus de 13 ans d'ancienneté : 300 € de prime

On va voir si vous êtes capable de trouver ! Petit conseil du jour : Le nombre de fonctions SI à imbriquer correspond au nombre d'actions -1 Exemple : Dans ce cas, il y a 4 actions (rien, 150, 225, 300). Le nombre de tests imbriqués est donc de 3.

6

Exercice 1: Vous devez créer un tableau qui affiche les capitales européennes d’après l’indication du pays : •

Créez un nouveau classeur

•

Saisissez les données ci-contre :

•

Trouvez la formule en B2 permettant d’afficher la capitale lorsqu’un pays est saisi en B1.

Pour vous aider : Les capitales de quelques pays !

Pays Capitales Allemagne - Berlin Belgique Bruxelles Danemark - Copenhague Espagne - Madrid France - Paris Grèce - Athènes Italie - Rome Luxembourg - Luxembourg Pays-bas - Amsterdam Portugal - Lisbonne Royaume-Uni - Londres Il y a 11 actions – Le nombre de tests imbriqués est donc de

??.

Avez-vous trouvé la solution ?

Exercice 2: Combien vont-nous coûter les vacances du mois d’août ? •

Ouvrez le fichier vacances

•

En fonction du tableau de paramètres fourni ci-dessous, complétez la feuille de calcul pour les colonnes CATEGORIE, INDEMNITE, ASSURANCE, ACTIVITES, puis faites le total par personne et pour l'ensemble du groupe.

Age

Catégorie

Indemnité

Assurance

Activités

[8-11]

P

13

15

110

[12-15]

M

20

25

110

[16-18]

G

25

30

150

Total = Indemnités X nb jours du mois + Assurances + Activité

7

Fonctions RECHERCHE, RECHERCHEV Dans certaines situations, il est impossible d'utiliser une fonction conditionnelle (trop grand nombre de conditions, situation inadaptée…exemple des capitales). Les fonctions RECHERCHE, RECHERCHEV permettent de résoudre de nombreux problèmes à choix multiples. En effet, lorsque le nombre d'actions possibles est important dans une fonction conditionnelle, la syntaxe devient trop complexe.

Fonction RECHERCHE Exemple : •

Ouvrez le classeur capitaleseuropeennes.

La fonction SI n’a pas pu répondre à notre problème :  Afficher la capitale en fonction du pays indiqué. La fonction RECHERCHE est tout à fait adaptée à cette situation. La résolution du problème va s'effectuer en deux étapes : 1. Création d'une table de référence contenant la liste des pays et des capitales correspondantes. 2. Création de la formule qui va permettre de trouver la capitale correspondant à chaque pays dans la table de référence

Syntaxe générale de la fonction RECHERCHE : =RECHERCHE(valeur cherchée ; table de référence) D'après la syntaxe, dans notre exemple, la valeur cherchée est le pays, la table de référence est la zone grisée •

Saisissez en B1 : Italie

•

Placez vous en B2

•

Saisissez la formule suivante :

=RECHERCHE(B1;D2:E26) •

Validez

En résumé : =RECHERCHE(

B1

;

D2:E26) Valeur

Table

cherchée

de recherche

• Testez avec d ’autres pays • Fermez capitaleseuropeennes.

8

Deux remarques importantes : 1 - La colonne de recherche de la table de référence (première colonne) doit TOUJOURS être classée par ordre croissant. Elle peut contenir du texte ou des nombres. 2 - Pour créer la table de référence, on utilise les limites inférieures des tranches (0,5,8,10,12,15,18) et non les limites supérieures. Exemple : [0-5[ : Mauvais

0

Mauvais

[5-8[ : Pas terrible

5

Pas terrible

[8-10[ : Moyen

8

Moyen

[10-12[ : Passable

10

Passable

[12-15[ : Bien

12

Bien

[15-18[ : Très Bien

15

Très Bien

[18-20] : Tricheur

18

Tricheur

Exercice 1 : •

Ouvrez le classeur recherches, feuille "relevé de notes"

La formule que vous allez saisir dans la colonne Appréciations va permettre d'abord de situer la note dans la table, puis de renvoyer le contenu de la deuxième colonne. Remarque : Attention aux références absolues Exercice 2 : Vous disposez d'une liste de véhicules et de leur puissance fiscale sur la feuille "Vignettes" du Classeur recherches Transformez ce tableau en une TABLE DE RECHERCHE (voir exemple des notes)

Puissance

Prix

Jusqu'à 4 CV

53,66

5-7 CV

81,41

8-9 CV

108,54

10-11 CV

154,28

12-14 CV

238,28

15-16 CV

343,01

17-18 CV

792,73

19-20 CV

1 189,10

21-22 CV

1 710,48

23 et plus

2 213,56

Exercice 3 : Le calcul du prix de location d'un véhicule. Une société loue des véhicules selon le tarif dégressif. •

Renseignez la feuille "location voiture" du classeur Recherches

Le prix à payer se calcule de la façon suivante : (Prix journée –(Prix journée*Taux remise))*Nbre jours

9

Fonction RECHERCHEV Vous souhaitez que s’affiche automatiquement les coordonnées de vos clients après saisie de leur code. Cette situation est tout à fait envisageable pour une facturation. Comment faire ?

La fonction Recherche permettra de renvoyer le nom (un élément de la seconde colonne) mais ne permet pas d’afficher directement l’adresse, le code postal et la ville du client. Pour cet exemple, ouvrez le classeur clients. La zone grisée est donc la table de référence dans laquelle va s'effectuer la recherche. La formule que vous allez saisir dans la cellule E20 va permettre d’afficher le nom du client

Syntaxe générale de la fonction RECHERCHEV =RECHERCHEV(valeur cherchée;table de référence;no_colonne) D'après la syntaxe, dans notre exemple, la valeur cherchée est le nom du client, la table de référence est la zone grisée, le n° colonne est 2 (2ème colonne de la table de référence) •

Saisissez en B20 : FRA009

•

Placez vous en E20

•

Saisissez la formule suivante :

=RECHERCHEV(B20;A2:E17;2) •

Validez

En résumé :

=RECHERCHE( B20 ; Valeur

A2:E17 ;

2)

Table de

Numéro de

cherchée recherche

colonne

Adresse du client : Placez-vous en E21

•

Saisissez la formule suivante :

=RECHERCHEV(B20;A2:E17;3) •

Validez

•

Saisissez les formules pour le Code Postal et la ville

10

Exercice 1: Vous travaillez dans une bibliothèque et vous voulez récompenser vos « bons lecteurs ». En effet, vous décidez d’offrir un mois d’abonnement gratuit aux personnes inscrites depuis au moins trois ans et un ouvrage aux autres. •

•

Ouvrez le classeur bibliotheque

La recherche s’effectuera sur le code des abonnés, triez donc cette colonne

En cellule D16 entrez la formule sachant qu’elle doit vous permettre de faire ressortir l’année d’inscription de la personne : La fonction ANNEE est nouvelle pour vous. Elle permet de faire ressortir l’année par rapport à une date. Exemple : =ANNEE(15/06/1993) = 1993 la fonction AUJOURDHUI() permet d’obtenir la date du jour.

•

Traitez le cas de Mr VIEUBOUQUIN et de Mme GERLITOUT.

Exercice 2 : •

Ouvrez le classeur fichesecu

•

Sur la feuille renseignements, à partir des éléments du numéro de sécurité sociale, créez des formules permettant d’afficher :  « Féminin » ou « Masculin »  Le mois de naissance en toutes lettres  Le département de naissance parmi les départements du grand ouest :

Eure 27 Seine Maritime 76 Calvados 14 Orne 61 Manche 50 Ille et vilaine 35 Morbihan 56 Côtes d’Armor 22 Finistère 29 Sarthe 72 •

Ajoutez votre département de naissance s’il n’est pas dans la liste

11

Exercice 3 : La SARL AtoutLoc loue à ses abonnés des DVD moyennant une participation financière. Par gain de temps, vous souhaitez automatiser votre facturation. Vous décidez de terminer votre maquette intitulée facturelocation. Attention, vous accordez un tarif préférentiel aux abonnés :  Dans le cas d’une location de trois DVD ou de deux DVD d’Action, une remise de 5% est accordée.  A partir de 4 DVD loués, la réduction passe à 10%. Par soucis d’esthétique, rien ne doit figurer sur les lignes inoccupées.

Quelques fonctions supplémentaires et utiles Excel est riche d’environ 400 fonctions de toutes sortes (mathématiques, trigonométriques, financières, logiques, dates…), vous en connaissez déjà quelques unes. Nous allons ici, voir de façon non-exhaustive certaines de ces fonctions sous forme de catalogue. Elles seront réutilisées par la suite dans des exemples et problèmes concrets.

Fonction RANG Renvoie le rang d'un nombre dans une liste d'éléments. Le rang d'un nombre est donné par sa taille comparée aux autres valeurs de la liste. (Si vous triez la liste, le rang d'un nombre serait sa position).

•

Créez le tableau ci-contre

Saisissez la formule suivante en B2: =RANG(B2;B2:B8)

Syntaxe générale :

=RANG(élément cherché;liste) Le résultat affiché (3) signifie que cette valeur (B2) est la 3ème plus importante de la liste (B2:B8) Attention à la recopie vers le bas !! Remarque : La fonction RANG peut contenir un 3ème paramètre qui est l’ordre de classement. La syntaxe habituelle (2 paramètres) est =RANG(élément cherché;liste) elle renvoie un classement du plus grand au plus petit. Pour inverser cet ordre, il suffit d’ajouter un 3ème paramètre, ce qui donne =RANG(élément cherché;liste;1)

12

Fonction INDEX La fonction INDEX permet la recherche d’une valeur dans un tableau en fonction de ses coordonnées. 

Indiquez dans « Matrice » la plage de cellules dans laquelle la fonction effectuera la recherche.



Dans « No_lig » indiquez le n° de la ligne et dans « No_col » le n° de la colonne. Ces numéros doivent correspondre aux colonnes et lignes de la plage de cellules « Matrice ».

Fonction EQUIV La fonction EQUIV recherche la position d’une valeur dans une matrice. 

Dans « Valeur_cherchée » entrez la valeur dont vous souhaitez obtenir la position.



Dans « Tableau_recherche » entrez la plage de cellules dans laquelle la fonction recherchera la position de « Valeur_cherchée ».



Dans « Type » entrez 0 pour trouver la valeur exacte, 1 pour la valeur la plus élevée qui est inférieure ou égale à « Valeur_cherchée », -1 pour la plus petite valeur qui est supérieure ou égale à « Valeur_cherchée ».

13

Fonctions de traitement des chaînes de caractères Pour certains traitements, il peut être nécessaire d’extraire des caractères des cellules contenant du texte. Par exemple pour constituer des numéros de client automatiquement, des codes de livres, de factures etc. Deux fonctions essentielles permettent d’extraire des caractères à gauche ou à droite :

La fonction GAUCHE : Permet d’extraire des caractères à gauche d’un texte. Exemple : •

Saisissez « bonjour » dans la cellule A1

•

Placez vous en B1 et entrez la formule suivante : =GAUCHE(B1;3)

•

puis validez

vous obtenez « bon » Vous venez d’extraire les 3 lettres à gauche du mot « bonjour » situé en A1

•

Ecrivez votre nom en A2 et essayez d’afficher les 4 premiers caractères…Dommage si votre nom ne fait que 3 caractères ;o)

•

La fonction DROITE : Permet d’extraire des caractères à droite d’un texte. Cette fonction a la même syntaxe que la fonction gauche, mais elle extrait les caractères à droite du texte que vous spécifiez. Essayez, en C1, d’extraire les 3 caractères de droite de votre nom. Bon, maintenant, ça va se compliquer !

La fonction STXT : On voudrait extraire des caractères à l’intérieur d’un texte. Placez le texte « UNIV Licence SESS PE1 » dans la cellule A1 Nou...