Guide de formation avec exercices et cas pratiues

��

��

Patrick Morié, Bernard-Philippe Boyer ��

© Tsoft et Groupe Eyrolles, 2004, ISBN : 2-212-11417-6

4 - CRÉER UNE FORMULE

4

CRÉATION DE FORMULE 1 - SOMME AUTOMATIQUE Cette fonction vous permet d’obtenir rapidement la somme d’une ligne ou d’une colonne. • Placez le curseur sous la colonne ou à la fin de la ligne dont il faut calculer la somme Cliquez sur ce bouton dans la barre d’outils Standard, ou appuyez sur

-=.

• La plage de données qu’Excel va additionner apparaît entourée de pointillés : si cette sélection est incorrecte, sélectionner la bonne plage pour confirmer • Appuyez sur Remarque : si vous cliquez sur la flèche associée au bouton précédent, un menu vous permet de choisir un autre calcul qu’une somme : moyenne, nombre d’éléments, etc.

2 - CRÉER UNE FORMULE • Cliquez dans la cellule devant afficher le résultat du calcul • Tapez = pour indiquer que vous allez créer une formule • Construisez la formule et appuyez sur

Pour terminer, vous pouvez aussi cliquer sur l’une de ces icônes dans la barre de formule : Abandonne la formule.

Valide la formule.

Le résultat est alors affiché dans la cellule, mais c’est la formule qui apparaît dans la barre de formule. La formule est dynamique : si vous changez des valeurs dans la feuille de calcul, la formule est automatiquement recalculée. Construire une formule La formule est composée de références aux cellules (ex : A1, B10) et d’opérateurs : + : addition - : soustraction * : multiplication / : division % : pourcentage ^ : puissance (ex : 2^3=23) − Pour indiquer une cellule : cliquez dedans et ses références s’inscrivent automatiquement. − Pour indiquer un opérateur : tapez son signe. − Pour indiquer une valeur : tapez le chiffre. Priorité des opérateurs Les formules respectent la priorité des opérateurs : la multiplication et la division sont prioritaires sur l’addition et la soustraction. On peut utiliser des parenthèses pour obtenir le résultat souhaité. Par exemple, 10*2+5 donne 25, alors que 10*(2+5) donne 70.

3 - RÉVISER UNE FORMULE • Double-clic dans la cellule, ou cliquez sur la cellule, puis dans la barre de formule Les cellules utilisées par la formule sont mises en évidence par un contour en couleur. • Révisez la formule directement dans la cellule ou dans la barre de formule ou cliquez sur l’icône de coche ( ) pour terminer • Appuyez sur

4 - CALCULS TEMPORAIRES Pour information, Excel affiche en permanence dans la barre d’état la somme du contenu de la sélection. Si vous préférez par un autre type calcul : clic-droit sur la zone affichant la somme dans la barre d’état, puis sélectionnez une autre fonction dans le menu contextuel qui apparaît.

58

© Tsoft/Eyrolles – Excel 2003 Initiation – Formation avec exercices et cas pratiques

RÉFÉRENCE AUX CELLULES 1 - LES RÉFÉRENCES DANS EXCEL Référence à une cellule − Ex : C5 Cellule se trouvant à l’intersection de la colonne C et de la ligne 5. Référence à une plage de cellules − Ex : C5:G15 Plage de cellules allant de la cellule C5 à la cellule G15. Références de type L1C1 Vous pouvez utiliser des références du type L(-2)C(1), caractérisant par exemple la cellule se trouvant deux lignes au-dessus de la cellule active et une colonne à droite ou L(5)C, caractérisant la cellule se trouvant cinq lignes au-dessous de la cellule active mais dans la même colonne. Pour adopter ce type de références, passez la commande Outils/Options, cliquez sur l’onglet Général, puis cocher ⌧Style de référence L1C1.

2 - RÉFÉRENCES RELATIVES Les formules créées par Excel sont par défaut relatives : la formule est définie par rapport à la position de la cellule qui la contient. Cela veut dire qu’une formule qui utilise par exemple le contenu des deux cellules qui sont au-dessus d’elle, continuera à utiliser les deux cellules au-dessus d’elle, même si elle est recopiée ailleurs dans la feuille de calcul. Exemple :

(a)

(b)

La formule en (a) fait le produit des deux cellules qui sont au-dessus d’elle (5 et 7). Si on la recopie en (b), elle fait encore le produit des deux cellules qui sont au-dessus d’elle (7,5 et 9). Les références B2 et B3 contenues dans la formule initiale sont relatives car en recopiant la formule ailleurs, elles se modifient et deviennent dans cet exemple D2 et D3.

3 - RÉFÉRENCES ABSOLUES Parfois, il est indispensable quand on recopie une formule que les copies fassent toujours référence à une cellule particulière : il est alors nécessaire de référencer cette cellule de manière absolue. Transformer une référence relative en référence absolue En cours de création de formule : • Cliquez dans la cellule pour faire apparaître sa référence relative pour la transformer en référence absolue • Appuyez sur Pour une formule existante : • Double-clic dans la cellule contenant la formule à modifier • Insérez le curseur derrière la référence à rendre absolue © Tsoft/Eyrolles – Excel 2003 Initiation – Formation avec exercices et cas pratiques

59

RÉFÉRENCE AUX CELLULES • Appuyez sur Des signes $ apparaissent alors avant la lettre et le nombre dans la référence de la cellule. Ainsi, A1 devient $A$1. Si vous utilisez des références du type L1C1, L(-5)C(2) devient L7C3. • Appuyez sur pour terminer

4 - RÉFÉRENCES MIXTES Il est possible d’avoir dans une formule une référence dont l’une des coordonnées (la ligne ou la colonne) est relative, et l’autre absolue. En cas de recopie, le numéro de la ligne s’adaptera à la nouvelle position de la formule, mais pas la lettre de la colonne, ou l’inverse.

Exemples de références : − C5 Référence relative. − $C$5 Référence absolue. − $C5 Référence colonne absolue et ligne relative. − C$5 Référence colonne relative et ligne absolue. Pour obtenir ces références mixtes, procédez comme pour créer une référence absolue, deux ou trois fois au lieu d’une seule fois. mais appuyez sur

5 - RÉFÉRENCES EXTERNES Lorsqu’une formule fait référence à une plage de cellules appartenant à une autre feuille de calcul, dans le même ou dans un autre classeur, la référence est dite externe et prend cet aspect : − Même classeur : =Feuil1!H3 − Autre classeur : =’[CLASSEUR.XLS]Feuil1’!$A$1 Si la feuille appartient à un classeur qui n’est pas enregistré dans le même dossier que le classeur actif, Excel fera précéder cette référence par son chemin. Par exemple : =‘C:\EXCEL\GESTION\[CLASSEUR.XLS]Feuil1’!$H$3

6 - RÉFÉRENCES 3D Ce type de formule vous permet de faire référence à une même plage sur plusieurs feuilles successives. Par exemple : =SOMME(Feuil1:Feuil5!A1:H3). Vous pouvez utiliser des références 3D pour faire référence à des cellules situées sur d’autres feuilles, pour définir des noms et créer des formules à l’aide des fonctions suivantes : − SOMME 60

− MOYENNE

− AVERAGEA


RÉFÉRENCE AUX CELLULES − NBVAL − ECARTYPE − MINA − VARPA − MIN

− MAX − STDEVPA − VAR − ECARTYPEP − VAR.P

− STDEVA − PRODUIT − NB − MAXA − VARA

7 - EN CAS D’ERREUR DANS UNE FORMULE En cas d’erreur dans la composition d’une formule, la cellule qui la contient affichera l’un des messages suivants : − #####

La cellule n’est pas assez large pour afficher le nombre dans le format choisi. − #DIV/0! Division par zéro. − #NOM? La formule utilise un nom inexistant ou il y a une erreur dans le nom d’une fonction. − #N/A! La formule fait référence à une valeur non disponible. − #NUL! Référence invalide (hors feuille par exemple). − #NOMBRE! Argument invalide dans une fonction. − #REF! Référence à quelque chose qui n’existe pas ou qui a été effacé. − #VALEUR! Arguments incompatibles dans une fonction. Suite à la création d’une formule erronée ou dans le cas de la sélection d’une cellule contenant une erreur potentielle (cellules avec un triangle vert dans leur coin supérieur gauche), une balise s’affiche : Cliquez sur la balise signalant l’erreur.

Ce menu vous propose diverses commandes pour vous aider à identifier et à corriger l’erreur, ou pour l’ignorer.

Les erreurs devant être signalées par cette balise sont à préciser dans le dialogue amené par la commande Outils/Options, sous l’onglet Vérification des erreurs :


61

LIAISON ENTRE FEUILLES La liaison permet de récupérer un ou plusieurs résultats provenant d’une ou de plusieurs autres feuilles différentes de la feuille de calcul active, dans le cas où elles appartiennent toutes deux au même classeur. Les cellules étant liées, si les données sources de la liaison changent, les modifications sont automatiquement répercutées dans les cellules liées. Il s’agit d’une référence externe : il est ainsi possible de récupérer le contenu d’une cellule ou d’une plage de cellules. Il est également possible de construire des formules impliquant des cellules appartenant à des feuilles distinctes.

1 - RÉFÉRENCE À UNE CELLULE OU À UNE PLAGE D’UNE AUTRE FEUILLE

Liaisons avec des cellules appartenant à d’autres feuilles

Méthode 1 • Ouvrez le ou les classeurs contenant les feuilles • Activez la feuille d’origine • Sélectionnez la plage de cellules dont on veut récupérer le contenu Cliquez sur ce bouton dans la barre d’outils Standard, ou Edition/Copier, ou appuyez sur -C. • Activez le classeur et la feuille de destination • Placez le curseur à la position où vous voulez récupérer la plage de cellules Cliquez sur ce bouton dans la barre d’outils Standard, ou Edition/Coller, ou appuyez -V. sur Cliquez sur la balise qui s’affiche automatiquement à proximité de la zone de collage. • Dans le menu qui s’affiche, cochez

Lier les cellules

On crée ainsi une référence externe dont la formule est du type : − Si les feuilles appartiennent au même classeur : =Feuil1!B5 − Si elles appartiennent à des classeurs différents : =’[Classeur.xls]Feuil1’!$B$5 Méthode 2 • Ouvrez le ou les classeurs contenant les feuilles, et activez la feuille de destination • Sélectionnez une plage de la même taille que la plage à récupérer • Pour créer une formule, tapez =

62


LIAISON ENTRE FEUILLES • Activez la feuille d’origine et sélectionnez la plage à récupérer - pour confirmer • Appuyez sur Vous pouvez créer ainsi une référence externe. La formule est du type : {= Feuil1!A1:C5} ou {=’[Classeur1.xls]Feuil12’!$A$1:$C$5}. Il s’agit d’une matrice.

2 - FORMULE UTILISANT DES RÉFÉRENCES EXTERNES Il s’agit de créer une formule qui utilise le contenu de cellules appartenant à d’autres feuilles de calcul ou à d’autres classeurs. La formule sera mise à jour en cas de modification dans les feuilles sources.

Avant de créer la formule, enregistrer le/les classeurs contenant les feuilles sources. • Ouvrez le ou les classeurs contenant les feuilles sources et la feuille de synthèse • Placez le curseur dans la feuille de synthèse (celle où apparaîtra la formule) • Tapez = • Activez la première feuille source, puis cliquez dans la donnée à utiliser dans la formule • Tapez un opérateur (+, -, / ou *) • Activez la seconde feuille source et cliquez dans la donnée à utiliser dans la formule • Et ainsi de suite ... pour achever la formule • Tapez On obtient par exemple, =Paris IDF!E10+Régions!E15 si les feuilles appartiennent au même classeur, ou =’[Classeur 1.xls] Paris IDF’!$E$10+’[Classeur 2.xls]Régions’!$E$15 si les feuilles appartiennent à des classeurs distincts.

3 - MISE A JOUR DES RÉFÉRENCES EXTERNES A l’ouverture d’un classeur contenant des formules qui font référence à d’autres classeurs : − Si ceux-ci sont ouverts, la mise à jour des formules est automatique. − Si ceux-ci ne sont pas ouverts, Excel affiche un message : dans ce dialogue, cliquez sur «Mettre à jour» pour mettre à jour les liaisons. En cours de session : • Edition/Liaison • Sélectionnez la description de la liaison à mettre à jour • Cliquez sur «Mettre à jour les valeurs» © Tsoft/Eyrolles – Excel 2003 Initiation – Formation avec exercices et cas pratiques

63

ÉTIQUETTES Les tableaux comportent généralement des libellés (Excel parle d’étiquettes), placés en haut de chaque colonne et à gauche de chaque ligne, et qui décrivent les séries du tableau. Vous pouvez utiliser ces étiquettes dans les formules lorsque vous voulez vous référer aux données en question. Il faut toutefois vérifier que la case ⌧Accepter les étiquettes dans les formules est cochée dans le dialogue amené par la commande Outils/Options, onglet Calcul. La différence entre les étiquettes et les noms (voir pages suivantes) est que les noms doivent être créés par l’utilisateur, et qu’ils sont utilisables dans la totalité du classeur alors que les étiquettes sont automatiquement identifiées par Excel, mais ne peuvent être utilisées que dans la feuille en cours. Si l’on modifie les étiquettes d’un tableau, leur nom est automatiquement mis à jour dans les formules qui les utilisent.

1 - UTILISER LES ÉTIQUETTES DANS LES FORMULES Par exemple, si un tableau contient les montants des objectifs dans une colonne étiquetée Objectifs et une ligne pour une zone géographique étiquetée Europe, on peut calculer les objectifs pour l’Europe en tapant la formule =Europe Objectifs. L’espace entre les étiquettes est l’opérateur d’intersection, qui commande à la formule de renvoyer la valeur de la cellule située à l’intersection de la ligne Europe et de la colonne Objectifs.

Par exemple, dans ce tableau nous avons tapé les formules suivantes : − En C37 =SOMME(Objectifs) − En E33 =Europe Objectifs-Europe Réalisations − En E37 =Total Objectifs-Total Réalisations

2 - DÉFINIR LES ÉTIQUETTES Si les étiquettes ne sont pas dans la première ligne ou la première colonne du tableau, il faut signaler leur position à Excel. • Sélectionnez les étiquettes en ligne ou en colonne • Insertion/Nom/Étiquette

(a)

• Précisez en (a) s’il s’agit d’étiquettes de lignes ou de colonnes • Cliquez sur «Ajouter», puis sur «OK» 64


NOMS DE CELLULES ET DE PLAGES Si les données d’un tableau ne possèdent aucune étiquette, ou si l’on désire utiliser des données enregistrées dans une autre feuille de calcul du même classeur, on peut créer un nom qui désignera la cellule ou la plage. Il devient ensuite possible d’utiliser ce nom dans les formules pour faire référence à cette cellule ou à cette plage nommée.

1 - NOMMER UNE PLAGE DE CELLULES Avec la barre de formule • Sélectionnez la plage de cellules • Cliquez dans la zone de saisie qui se trouve à l’extrémité gauche de la barre de formule ici

• Tapez un nom (sans espace) et appuyez sur Avec un dialogue • Sélectionnez la plage • Insertion/Nom/Définir, ou appuyez sur

-

• Tapez un nom (sans espaces) • Cliquez sur «Ajouter», puis sur «OK»

2 - RÉCUPÉRER LA LISTE DES NOMS UTILISÉS Pour insérer dans une partie de la feuille de calcul la liste des noms définis et les références des plages associées. • Placez le curseur dans la zone vide de la feuille • Insertion/Nom/Coller, ou appuyez sur • Cliquez sur «Coller une liste»

3 - UTILISER UN NOM DANS UNE FORMULE Lors de la création d’une formule, plutôt que de cliquer dans une cellule ou de sélectionner une plage de cellules pour en faire apparaître les références, il est possible de saisir ou récupérer le nom de la cellule ou de la plage. La formule devient beaucoup plus lisible. Le curseur étant dans la barre de formule, à la position où l’on souhaite insérer le nom, • Tapez le nom Ou • Insertion/Nom/Coller, ou appuyez sur • Sélectionnez dans la liste le nom à insérer • Cliquez sur «OK» © Tsoft/Eyrolles – Excel 2003 Initiation – Formation avec exercices et cas pratiques

65

NOMS DE CELLULES ET DE PLAGES 4 - SUPPRIMER UN NOM • • • •

Insertion/Nom/Définir, ou appuyez sur Sélectionnez le nom dans la liste Cliquez sur «Supprimer» Cliquez sur «OK»

-

5 - GÉNÉRER AUTOMATIQUEMENT LES NOMS EN UTILISANT LES LIBELLÉS Si un tableau contient des libellés, Excel peut automatiquement nommer les lignes et les colonnes du tableau en utilisant ces libellés comme noms. • Sélectionnez le tableau - • Insertion/Nom/Créer, ou appuyez sur • Indiquez dans le dialogue qui s’affiche où se trouvent les libellés du tableau • Cliquez sur «OK»

6 - REMPLACER LES RÉFÉRENCES PAR LES NOMS DANS LES FORMULES Si vos formules n’utilisent pas de noms, vous pouvez créer les noms après coup et demander à Excel de remplacer dans les formules les références par les noms existants. • Sélectionnez le tableau, ou ne sélectionnez rien pour un remplacement dans toute la feuille • Insertion/Nom/Appliquer • Cliquez sur «Options»

(a) (b) (c) (d)

(a) Remplace des références par des noms quel que soit le type de référence. Si vous désactivez cette case, les références absolues ne sont remplacées que par des noms absolus, et les références relatives par des noms relatifs. (b) Cette option permet d’utiliser le nom des plages de lignes et de colonnes contenant les cellules auxquelles il est fait référence si les noms des cellules exactes référencées ne peuvent être trouvés. (c) Si la cellule référencée se trouve dans la même colonne que la formule et à l’intérieur d’une plage orientée en ligne nommée, permet de remplacer la référence par le nom orienté en ligne. (d) Si la cellule référencée se trouve dans la même ligne que la formule et à l’intérieur d’une plage orientée en ligne nommée, cette option remplace la référence par le nom orienté en colonne. • Si nécessaire, sélectionnez les noms devant être utilisés lors du remplacement • Cliquez sur «OK»

66


CALCUL MATRICIEL Une matrice est une plage de cellules repérée comme une entité unique. Au sein d’une formule, il vous sera possible d’utiliser une matrice plutôt qu’une référence à une plage de cellules. L’intérêt est que vous pourrez remplacer une formule et ses copies par une seule formule matricielle. On gagne du temps et la taille de la feuille de calcul est réduite.

1 - CRÉER UNE FORMULE MATRICIELLE Avec la méthode habituelle Exemple :

(a)

• Créez la formule en (a) : =H32*I32 • Recopiez la formule vers le bas En utilisant le calcul matriciel • Sélectionnez la zone de calcul (J32 à J36)

• • • • •

Tapez = Sélectionnez la plage H32 à H36 Tapez * Sélectionnez la plage I32 à I36 - - pour valider la formule matricielle Appuyez sur

Pour toute la zone calculée, c’est la même formule qui apparaît. On reconnaît qu’elle est matricielle car elle est placée entre accolades : {=H32:H36*I32:I36}.

2 - RÉVISER UNE FORMULE MATRICIELLE • Double-clic dans l’une des cellules contenant la formule • Modifiez la formule - - pour valider la formule • Appuyez sur

3 - SUPPRIMER UNE MATRICE Il n’est pas possible d’effacer une partie de la matrice : elle doit être effacée dans sa totalité. • Cliquez sur une cellule de la matrice -T • Edition/Atteindre, ou appuyez sur © Tsoft/Eyrolles – Excel 2003 Initiation – Formation avec exercices et cas pratiques

67

CALCUL MATRICIEL • Cliquez sur «Cellules»

• Cochez Matrice en cours • Cliquez sur «OK» • Appuyez sur pour supprimer la matrice Ou • Sélectionnez l’intégralité de la matrice pour la supprimer • Appuyez sur

68
