page 56 reproduction interdite

4 LES FORMULES - LES FONCTIONS

Les formules ou les fonctions sont des types de données particulières. Elles prennent le plus souvent en compte les valeurs enregistrées dans d'autres cellules de la feuille de travail. La position de ces cellules est indiquée par leurs références.

a) LES FORMULES

Une formule permet d'effectuer des calculs et d'afficher le résultat dans une cellule et ce, à partir de nombres et/ou d'autres cellules.

ü les cellules intervenant dans le calcul sont définies par leurs références

ü des opérateurs les mettent en relation : + (plus), - (moins), * (multiplié), / (divisé).

ü Les formules peuvent être arithmétiques, logiques ou texte selon les opérateurs utilisés.

PROCEDURE

SAISIE DE LA FORMULE :

<CLIC G> DANS LA CELLULE OU DOIT S'AFFICHER LE RESULTAT DU CALCUL TAPER = (SIGNE EGAL) - l'indicateur "entrer" s'inscrit dans la barre d'état <CLIC G> SUR LA PREMIERE CELLULE SAISIR L'OPERATEUR <CLIC G> SUR LA SECONDE CELLULE VALIDER PAR OU <ENTREE>

AVANCE

Dans les formules plus complexes, il peut être nécessaire d'utiliser des parenthéses ()

CONSEIL

Lors de la saisie, utiliser le pavé numérique à droite du clavier sur lequel vous avez les chiffres de 1 à 9, les opérateurs et une touche <ENTREE> l'idéal est d'utiliser la main gauche sur le pavé numérique pour taper les opérateurs et la souris dans la main droite pour désigner les cellules ou les plages de cellule

COMPOSITION D'UNE FORMULE

opérande opérateurs

séparateurcolonne ligne

références de la cellule

SYNTAXE

Ä OPERANDE données ou référence des cellules contenant les données faisant l'objet du calcul

Ä OPERATEURS symboles indiquant l'opération à effectuer

Ä SEPARATEURS symboles permettant de combiner les différentes opérations (parenthèses)

IOS département formation page 57

ATTENTION

NE PAS OUBLIER DE FAIRE PRECEDER LES REFERENCES DE LA CELLULE DE = (EGAL)

PROCEDURE

LA FORMULE ARITHMETIQUE :

+A5+B5 additionne les cellules a5 et b5 +A5-1 enlève 1 à la valeur de la cellule a5 +A5*3 multiplie la valeur de la cellule a5 par 3 +A5/2 divise la valeur de la cellule a5 par 2

EXERCICE

DANS LE CLASSEUR COMMANDE FAIRE LE TOTAL DES QUANTITES CALCULER LA VALEUR POUR CHAQUE TYPE DE FLEUR EN MULTIPLIANT QUANTITE PAR PRIX

PROCEDURE

LA FORMULE LOGIQUE :

+A5=10 donne vrai si a5 est égal à 10 et faux autrement +A5>1 donne vrai si a5 est supérieur à 1 et faux autrement

EXERCICE

REPRODUIRE LES 2 EXEMPLE CI-DESSUS

CONSEIL

La formule logique, associée à un format conditionnel est un excellent moyen d'introduire une formule de vérification dans un tableau (voir " ='[Exercice fini.xls]TOTAL FRANCE'!$G$9"

PROCEDURE

LA FORMULE TEXTE :

+"TIC"&"TAC" affiche TICTAC

EXERCICE

REPRODUIRE L 'EXEMPLE CI-DESSUS

AVANCE

La formule matricielle est un type particulier de formule à employer lorsque la même formule est répétée dans une plage de cellule adjacentes. Elle permet de ne saisir qu'une seule fois la formule pour l'ensemble des cellules, cette dernière faisant référence à l'ensemble des cellules concernées

NOTES

page 58 reproduction interdite

PROCEDURE

LA FORMULE MATRICIELLE - SAISIE : SELECTIONNER LA PLAGE DE CELLULES SAISIR LA FORMULE EN INCLUANT TOUTES LES PLAGES DE REFERENCE (ne pas mettre d'accolades) <CTRL> <MAJ> <ENTREE>

LA FORMULE MATRICIELLE - MODIFICATION : SELECTIONNER UNE CELLULE DE LA PLAGE MODIFIER LA FORMULE (ne pas mettre d'accolades) <CTRL> <MAJ> <ENTREE>

ATTENTION

EXCEL convertit automatiquement les lettres minuscules d'une formule (ou d'une fonction) en majuscules S'il ne le fait pas, c'est que celle-ci n'est pas valide ou n'est pas reconnue comme telle. Vérifier la saisie. Si la formule est reconnue comme valeur mais n'est pas acceptée, "ERR" s'affiche - Vérifier la syntaxe.

LISTE DES OPERATEURS

PROCEDURE

ARITHMETIQUES :

+ PLUS

- MOINS

* MULTIPLIE

/ DIVISE

% POURCENTAGE

^ PUISSANCE

LOGIQUES : = EGAL < INFERIEUR A > SUPERIEUR A <> DIFFERENT <= INFERIEUR OU EGAL >= SUPERIEUR OU EGAL & CONCATENATION #non# NON LOGIQUE #et# ET LOGIQUE #ou# OU LOGIQUE

EXERCICE

SAISIR LE TABLEAU "BILAN" SUIVANT EN EFFECTUANT LES CALCULS "RESULTAT BRUT" ET "RESULTAT NET" SUR LA FEUILLE 3

NOTES

IOS département formation page 59

b) LES FONCTIONS

Les fonctions sont des formules intégrées et prédéfinies suivies d'un ou plusieurs arguments et obéissant à une syntaxe précise. Dans un tableau, elles s'utilisent souvent en combinaison avec des formules et sont précédées du signe = (égal).

Une fonction s'exprime toujours sous la forme :

=FONCTION(ARG1;ARG2;"ARG3";ARGN)

ü = EGAL indique à EXCEL qu'une fonction suit

ü (...) PARENTHESES les parenthèses encadrent les arguments de la fonction

ü ARG1 ARG2..ARGN définissent les différentes variables dont la valeur est à renseigner (4 types : emplacement, valeur, chaîne, condition)

ü ; POINT VIRGULE caractère séparateur d'arguments indispensable

ü " " GUILLEMETS encadrent un argument texte

PROCEDURE

LA FONCTION SOMME :

<CLIC G> SUR LA CELLULE OU DOIT S'INSCRIRE LA SOMME

<CLIC G> SUR si le calcul porte sur la somme en ligne ou en colonne de cellules contiguës, la formule est correcte (cas le plus fréquent) sinon modifier les références erronées

ATTENTION

Si vous désirez que Excel fasse des totaux de ligne mais que les 2 cellules immédiatement au-dessus de la cellule sélectionnée comportent des valeurs, Excel fera un total vertical et non horizontal - le rectifier en faisant glisser la souris sur la plage adéquate.

Lorsque le tableau que l'on crée est un tableau carré (somme des lignes = somme des colonnes), la somme du tableau peut être effectuée en un clic de souris.

PROCEDURE

SOMME D'UN TABLEAU CARRE :

SELECTIONNER LES DONNEES PLUS 1 LIGNE ET UNE COLONNE VIDES POUR LES TOTAUX

<CLIC G> SUR

NOTES

page 60 reproduction interdite

AVANCE

La fonction somme sait gérer la notion de sous-totaux ; dans une sélection ou figurent des valeurs numériques et des sous-totaux, elle ne prend en compte que les sous-totaux

EXERCICE

SAISIR LE TABLEAU SUIVANT

FAIRE LES TOTAUX EN UNE OPERATION ENREGISTRER LE CLASSEUR SOUS LE NOM "TRANSPORT"

EXERCICE

OUVRIR LE DOCUMENT "SOMME " FAIRE LES TOTAUX DES TABLEAUX EN UN MINIMUM D 'OPERATIONS EFFACER CES TOTAUX EN UNE SEULE OPERATION EFFECTUER LE TOTAL GLOBAL DES LIGNES DES TABLEAUX (SOMME DE TOUTES LES ACTIVITES DE TOUTES LES REGIONS) FERMER LE TABLEAU SANS L 'ENREGISTRER

L'assistant fonction permet d'utiliser des fonctions plus complexes.

PROCEDURE

ASSISTANT FONCTIONS :

<CLIC G> DANS LA CELLULE DE SAISIE (UNE SEULE CELLULE)

<CLIC G> SUR DANS LA BARRE D'OUTILS STANDARD ou

<CLIC G> SUR DE LA BARRE DE FORMULE SELECTIONNER LA FONCTION L'ASSITANT FONCTION RENTRE EN ACTION SELECTIONNER LA FONCTION DEFINIR LES ARGUMENTS

POUR VALIDER

NOTES

IOS département formation page 61

EXERCICE

DANS LE CLASSEUR "COMMANDE" EFFACER LES DONNEES DE LA LIGNE TOTAL REMPLACER LES PAR LA MOYENNE DE CHAQUE COLONNE

c) LE CALCUL AUTOMATIQUE

La barre d'état de EXCEL affiche désormais une zone dans laquelle figure le résultat de

l'application de la fonction en cours à la sélection. Ex :

PROCEDURE

CALCUL AUTOMATIQUE :

EFFECTUER LA SELECTION CONSULTER LA BARRE D 'ETAT

INFO

les procédures standard de sélection (monozone ou multizones) peuvent être appliquées : <FAIRE GLISSER> <CTRL> pour ajouter <MAJ> pour étendre

PROCEDURE

CHANGER LA FONCTION :

AVANT OU APRES LA SELECTION <CLIC D> SUR LA ZONE DE LA BARRE D 'ETAT AFFICHANT LE RESULTAT

<CLIC G> SUR LA FONCTION A UTILISER

EXERCICE

DANS LE CLASSEUR "COMMANDE" CHOISIR LA FONCTION "SOMME" EFFECTUER UNE SELECTION DE VALEUR IDEM AVEC LA FONCTION "MOYENNE" TESTER AUSSI LES AUTRES FONCTIONS

NOTES