Cette partie suppose que vous êtes familiarisé avec les premiers pas dans Excel. Mise en forme; écriture d'une formule simple, accès aux fonction intégrées. Il s'agit là néanmoins d'un rappel des fonctions simples du tableur ainsi que des fonctions logiques simples ( SI )
LES FONCTIONS SIMPLES
A partir d'un tableau répertoriant les salaires, charges afférentes, et services auxquels appartiennent les salariés listés, nous pouvons appeler un certains nombre de fonctions simples pour
afficher des informations.
Rappel :
les plages de cellules peuvent être définies de différentes façons
- PLAGE DE CELLULE : =fonction(E2:E11; ... ; ...)
- PLAGE NOMMEE : = fonction(nom_de_la_plage ; ... ; ...)
FONCTIONS MAX() ET MIN()
Elles renvoient respectivement la valeur maximale et minimale dans une liste de valeurs.
- syntaxe : = MAX(PLAGE DE CELLULE)
- syntaxe : = MIN(PLAGE DE CELLULE)
FONCTION MOYENNE()
Elle envoie la moyenne d'une liste de valeur.
NB: une valeur numérique à 0 est prise en compte alors qu'une valeur non numérique ou une cellule vide sera ignorée.
- syntaxe : = MOYENNE(PLAGE DE CELLULE)
FONCTIONS NB et NBVAL
Ces fonctions sont de type compteur et renvoient le nombre de valeurs contenues dans une liste
NB = renvoie le nombre de valeurs numériques contenue dans une liste
NBVAL = renvoie le nombre de cellules non-vides dans une liste. Explixcitement NBVAL pourra être utilisée pour renvoyer le nombre de cellules contenant notamment du texte.
- syntaxe : = NBVAL(plage de cellule)
- syntaxe : = NBVAL(plage de cellule)
LES FONCTIONS CONDITIONNELLES
La plus utilisée des fonctions conditionnelles utilise le mot clé SI qui invite à entrer un critère qui devra
être strictement respecté pour qu'une action se fasse.
sa syntaxe est la suivante
= SI (évaluation du critère ; SI critère avéré action ; si critère non avéré action )
L'action effectuée peut être : un calcul; une fonction; le renvoi d'une chaîne de caractère.
Le troisième argument indique l'action si la condition n'est pas respectée.
Dans le tableau ci-dessus évaluons si le premier salarié fait partie du service Compta
nous renverrons la chaîne oui si le critère est avéré et non sinon
Dans une cellule vide nous entrons
=SI (D2="compta";"oui";"non")
La fonction renvoie "non"
accordons une augmentation de salaire de 10% pour les salaires inférieurs ou égaux à 1500 euros
dans une nouvelle colonne augmentation insérons la formule
=SI(E2 <= 1500;E2+E2*10%;E2)
La recopie vers le bas de la formule (référence relative) nous renvoie les nouvelles valeurs correspondant aux critère et action définies
Le tableau ci-dessous affiche les résultats effectués par 3 commerciaux sur les 4 trimestres de l'année
Le tableau II vous indique les différentes fonctions SI pouvant être utilisées afin de leur attribuer des primes conditionelles
La première condition accorde une prime de 1% au commercial ayant effectué un C.A supérieur à 4000 euros
Si la condition est avérée; la prime est de 1% du C.A
Sinon on insérère la chaîne "pas de prime dans la cellule où la fonction est insérée
=SI(G33>4000;$G$36*1%;"pas de prime")
La fonction est recopiée vers le bas pour les autres commerciaux pour tous ces exemples
La deuxième condition accorde une prime de 2% au commercial ayant effectué un C.A supérieur à la moyenne des C.A. ici nous insérons un fonction "moyenne()" en critère de la fonction SI()
=SI(G33>MOYENNE($G$33:$G$35);$G$36*2%;"pas de prime")
La troisième fonction conditionnelle accorde une prime de 2% au commercial ayant effectué un C.A supérieur à à la moyenne.Dans le cas où cette condition n'est pas remplie une prime de 1% si le chiffre d'affaire est supérieur à 4000.sans qu'il soit encore question de la fonction logique ET() que nous verrons plus tard, cette fonction insère une deuxième fonction SI dans le troisième argument. Il restera une alternative, la condition un n'est pas avérée, la condition 2 non plus, il reste donc à définir l'action pour le troisième argument de la deuxième condition.
=SI(G33>MOYENNE($G$33:$G$35);$G$36*2%;SI(G33>4000;$G$36*1%;"pas de prime"))
Il existe d'autres fonctions logiques OU, ET, VRAI, FAUX qui constituent avec SI les fonctions logiques que l'on retrouve dans tous les programmes informatique. Il est important de se rappeler que ces fonction dites BOOLEENNE, du nom de leur inventeur sont à la base de la logique informatique.La fonction ET() évalue des arguments et renvoie VRAI si les arguments sont tous strictement avérés. Cette Fonction peut être représentée par la table de vérité de Boole
Dans notre quatième exemple nous affichons la progression du commercial lièe à la progression de ses ventes de trimestre en trimestre. En l'occurence SI sont 2ème trimestre est meilleur que le premier ET si sont 4ème est meilleur que le 3ème.
=SI(ET(D33>C33;F33>E33);"bonne progression";"mauvaise progression")
il faut donc pour que la progression soit bonne que les deux conditions soient remplies.
La fonction OU() évalue des arguments et renvoie VRAI si l'un des deux est vrai (ou si ils le sont tous éventuellement).
EX : dans une feuille de calcul, insérons la valeur 11 en A1 et 9 en A2
Nous nous asssurons maintenant que l'une de ces deux valeurs est supérieure à 10
En A3 : = OU(A1>10;A2>10) Excel renvoie la valeur VRAI en A3
Ces fonctions logiques simples, que toute personnes utilisant Excel doit maîtriser, trouvent leurs limites lorsque de nombreux arguments sont à évaluer. Il devient difficile de construire une syntaxe claire et
les imbrications de fonction ont leur limite en nombre (7 sauf erreur).
Excel propose de nombreuses autres fonctions permettant d'effectuer des calculs soumis à condition et c'est le but de la suite de mon propos.
LA FONCTION SOMME.SI()
cette fonction permet de notifier un critère dans une plage de cellules et d'additionner des montants correspondant à ces critères.
L'évaluation se fait sur la plage A1:A8 et la somme sur la plage adjacente B1:B8
IL EN IRA DE MEME POUR LA FONCTION SOMMEPROD() que nous allons voir, ainsi que pour les fonctions matricielles que je vous propose d'aborder rapidement ici
Avant cela je vous propose un petit topo expliquant comment Excel calcul logiquement ces fonctions conditionnelles.
Nous souhaitons évaluer dans le tableau ci-dessous le nombre de garçons (sexe M) ayant une note inférieur à 10.
Pour cela nous attribuons avec une condition simple SI(), la valeur 1 pour les cellules de la plage A8:A8 contenant la valeur "M"
de la même manière nous testons la plage B2:B8 en attribuant 1 pour les cellules contenant une note inférieur à 10.
Nous considérons que la valeur 1 correspond à VRAI et 0 à FAUX
Pour tester les plages dans lesquelles les deux conditions sont vraies (plage E2:E8), nous appliquons le produit des plages C et D, mêmes lignes. En fait nous simulons une addition logique au sens ou nous l'avons définie en présentant les tables de vérité
il nous reste ensuite à faire la somme (E9) de la plage E2:E8 pour avoir la réponse à notre intérrogation
Bien sûr ce procédé est un peu lourd mais il est présenté uniquement pour montrer comment les fonctions que nous allons voir procèdent pour effectuer ce type de calcul
LA FONCTION SOMMEPROD()
cette fonction travaille sur des matrices et permet d'effectuer des calculs ligne par ligne et d'insérer des critères
=SOMMEPROD((A2:A8="m")*(B2:B8 <10))
La fonction fait la synthèse parfaite de ce que nous venont de disséquer, en attribuant une valeur logique VRAI(1) quand le critère est avéré et FAUX (0) sinon. Elle fait le produit ligne par ligne et additionne ensuite le résultat.
Nous aurions pu de même utiliser une formule matricielle (bien qu'un peu lourde ici)
{=SOMME((A2:A8="m")*(B2:B8 <10))}
ces formules effectuent des calculs sur des matrices. Elles sont caractérisées par les deux accolades {...} qui les dinstinguent des formules "simples"
un autre exemple de la fontion SOMMEPROD()
Nous permet d'afficher le nombres de lignes impaires contenant la chaîne "test"
Pour rappel, la fonction mod renvoie le modulo (reste de la division) et la fonction ligne renvoie le numéro de la ligne pour chaque cellule d'une liste.
LES FONCTIONS DE RECHERCHE
Avec ces premiers exemples nous allons aborder des fonctions qui permettent de faire des recherches dans des matrices de manière plus ou moins élaborées.
Le tableau ci dessous liste les motifs d'arrêt de travail ainsi que leur nombre sur une période donnée.
Dans un premier temps, nous évaluerons le nombre d'absences par motif avec une fonction que nous avons déjà abordée.
Dans un deuxième temps nous aborderons deux fonction complémentaires qui permettent de faire des recherches dans une matrice :
La fonction INDEX()
la fonction EQUIV()
Les tableaux suivant liste le nombre d'absences par motif et par salariés
|
Les statistiques concernant le nombre d'absence par motif et par salariés sont calculées avec la fonction NB.SI en prenant comme critère la liste des salariès et celle des motifs d'absences. Pour les absences par exemple la fonction est : =NB.SI(A1:C25;A31) recopiée vers le bas |
=RANG(cellule;plage;0 ou omis croissant; 1 décroissant)
Nous souhaitons trouver, à partir du tableau listant les absences par salariès, celui qui a leplus d'absence. Nous souhaiton voir afficher son nom. Il nous faut donc utiliser une formule qui recherchera dans la deuxième colonne une ligne (celle qui a la plus grande valeur) et dans la première colonne, la valeur de cellule correspondant:
INDEX() est la première formule que nous utiliserons
cette fonction renvoie la valeur contenue à l'intersection d'un eligne et d'une colonne dans une matrice.
Elle accepte trois arguments =INDEX (matrice;n°ligne;n°colonne)
En ce qui nous concerne nous connaissont deux de ces arguments la matrice et le n° de colonne (1) qui correspond au nom du salariés
Il nous manque le n° de la ligne correspondant à la plus grande valeur dans la liste des nombres d'abscences
EQUIV() est la fonction que nous utlisiseront pour trouver le n° de la ligne. Equiv recherche une valeur approchée ou fixe dans un liste et renvoie son N° de ligne Elle accepte 3 arguments : =equiv (valeur à trouver;liste de valeurs;type()-1;0;1)
Si le type est 0 on recherche une valeur fixe (connue) sinon Equiv renvoie un message d'erreur
Si le type est 1 equiv renvoie la plus grande valeur inférieure ou égale à la valeur recherchée. Il faut impérativement que la liste soit triée en ordre croissant
Si le type est -1 equiv renvoie la plus petite valeur supérieure ou égale à la valeur recherchée. Il faut impérativement que la liste soit triée en ordre décroissant
Dans notre cas nous ne connaissons pas la valeur cherchée (nb d'absence) mais nous savons que nous recherchons la valeur maximale et nous connaissons la fonction qui renvoie la valeur maximale d'une liste max()
EQUIV() se présentera donc ainsi =EQUIV(max(liste);liste;0)
Il nous suffit d'imbriquer ces fonctions dans la fonction INDEX pour trouver le N° de ligne
=INDEX ( A39:B46;EQUIV(max(liste);liste;0);1)
