EXCEL

Notes de cours Excel

CPGE Omar Al Khayyam

Année Scolaire : 2014/2015

I.                   INTRODUCTION

Excel est un outil de gestion communément appelé tableur nom donné aux logiciels servant à calculer et à visionner des tableaux traitant des chiffres. Ce logiciel sert à construire des budgets, préparer des bilans, réaliser des prévisions de vente, élaborer des tableaux d'amortissements, réaliser des calculs automatiques, d'évaluer des remboursements d'emprunt. Etc.

Ces puissantes fonctions de calcul s'assortissent de commandes multiples de mise en forme; le programme, de fait est équipé d'outils de présentation très performants autorisant l'édition des résultats. Mais les prouesses d'Excel ne s'arrêtent pas là.

En effet, bien que sa fonction majeure soit le calcul électronique (grâce au module tableur), le programme Microsoft est capable de présenter graphiquement les données (grâce au module grapheur), de manipuler les informations (grâce au module de Base de données intégré au tableur) et même de mémoriser des séquences complexes d'instructions (grâce au module macro ).

Aujourd'hui il y a une multitude de tableurs dans le domaine informatique à savoir :

Supercale, Multi-Plan, Lotus 1 -2 -3, Quattro -pro, Excel, VP Planner, calc Star sans oublier ceux qui constituent ce que nous appelons les logiciels intégrés comme (Framework, Works) qui possèdent des tableurs assez opérationnels. Grâce à ce logiciel, la manipulation des données, leur gestion et exploitation pour diverses opérations comptables, économiques et statistiques …etc, s'est trouvé ainsi facilitée.

II.               Présentation générale des onglets d'Excel 2007

La version 2007 de Word a fait disparaître la barre de menu Fichier, Edition ... Celle-ci a été remplacée par un ruban composé d'onglets. Ces onglets sont au nombre de 7:

·         L'onglet Accueil :

Dans cet onglet, vous allez trouver les éléments de base pour mettre en forme les cellules d'Excel.

·         L'onglet Insertion :

Dans cet onglet, vous allez trouver tous les outils nécessaires pour insérer un graphique, un lien hypertexte ... dans votre feuille de calcul Excel.

·         L'onglet Mise en Page :

Dans cet onglet, vous allez trouver tous les outils nécessaires à la mise en page de votre feuille Excel.

·         L'onglet Formules :

Dans cet onglet, vous trouverez tous les outils pour réaliser des formules de calculs dans votre feuille Excel 2007.

·         L'onglet Données :

Dans cet onglet, vous allez trouver tous les outils pour par exemple importer des données ... dans votre feuille de calcul Excel 2007

·         L'onglet Révision :

Cet onglet vous permet de corriger les fautes d'orthographe, ajouter des commentaires ... à votre feuille de calcul Excel 2007

·         L'onglet Affichage :

Cet onglet vous permet de changer l'affichage de votre feuille Excel 2007.

III.           Les bases d'Excel

Le logiciel Excel 2007 est un logiciel tableur. Un tableur est un programme informatique capable de manipuler des feuilles de calcul. À l'origine destinés au traitement automatisé des données financières, les logiciels tableurs sont maintenant utilisés pour effectuer des tâches variées, de la gestion de bases de données simples à la production de graphiques, en passant par diverses analyses statistiques.

III.1 Les éléments de base du tableur Excel 2007

Le tableur Excel 2007 se compose de plusieurs feuilles . On parle alors de classeur. Dans chaque feuille Excel, vous allez trouver une succession de ligne et de colonne. Les lignes sont représentées par des chiffres allant de 1 à 65536 et les colonnes sont représentées par des lettres allant de A jusqu'à ZZ.

Cellule C3

Plage de cellules       D5 : E12

L'intersection d'une ligne et d'une colonne se nomme une cellule et chaque cellule possède un nom composé du numéro de ligne et de la lettre de la colonne. Par exemple sur l'exemple précédent, la cellule A1 est sélectionnée cela signifie que tous ce que vous allez écrire va se situer dans cette cellule.

Dans une cellule, vous pouvez saisir des caractères numériques et alphanumériques.

III.2 Le déplacement et la validation d'une cellule

Pour vous déplacer entre les cellules, il suffit de cliquer sur la cellule de destination. Vous pouvez aussi vous déplacer avec les 4 flèches de direction.

Valider une cellule signifie sortir de la cellule en cours pour vos déplacer sur une autre cellule. Pour valider une cellule, utilisez une des 4 flèches de direction. Lorsque vous ferez un calcul dans une cellule, pour valider cette cellule, il faudra appuyer sur la touche ENTREE du clavier.

IV.           Les fonctions de base du logiciel Excel 2007

Excel permet de faire des calculs simples et des calculs plus ou moins complexes. Vous trouverez de nombreux outils dans l'onglet Formules qui vous permettront de réaliser ces calculs :

1.      La fonction Moyenne : Vous trouverez cette fonction en déroulant le menu suivant:

Cette fonction calcule la moyenne des cellules sélectionnées comme le montre l'image suivante:

2.      la  fonction somme : Cette fonction calcule la somme des cellules sélectionnées comme le montre la figure suivante:

3.      les fonctions MAX et MIN : MAX retourne la valeur maximale d’une plage de cellules et MIN la valeur minimale :

              

4.      La fonction SI :  La fonction SI effectue un test logique qui va retourner 2 valeurs (ET UNIQUEMENT 2) : VRAI ou FAUX

        La fonction SI se décompose en trois parties

ü  Le test

ü  Le résultat si le test est vrai

ü  Le résultat si le test est faux

=SI(test; Si test est VRAI; Si test est faux)

La condition (ou test) se fait toujours en comparant 2 choses (2 cellules entre elles, le contenu d'une cellule avec un nombre ou un texte, 2 calculs, etc...) Au niveau des conditions plusieurs opérateurs sont utilisables :

=	égal à
>	supérieur à
<	inférieur à
<>	différent de
>=	supérieur ou égal à
<=	inférieur ou égal à

La réponse à la question posée dans le test ne pourra avoir que 2 résultats : VRAI ou FAUX. Dans les 2 cas on va écrire dans la formule le résultat que l'on voudra voir apparaître dans la cellule.

Syntaxe :
Si vous souhaitez afficher dans une cellule du texte, il faut alors écrire vos conditions vrai et fausse entre guillemet.

=SI(test;"Accepté";"Refusé")

C'est également en utilisant des guillemets que vous pouvez écrire un résultat vide. En effet si vous omettez le paramètre FAUX dans votre formule, Excel écrira le terme FAUX dans la cellule (dans le cas où le résultat de votre test est faux). Donc pour éviter cela, il est nécessaire d'écrire "" plutôt que de laisser le paramètre vide.

=SI(test;"Accepté";"")

Pour les chiffres ou pour les formules, vous écrivez vos paramètres VRAI ou FAUX sans guillemet comme par exemple:

=SI(test;A1-B1;A1-C1)

La fonction SI() n'autorise que 2 réponses possibles. Si le problème à résoudre comporte 3 réponses possibles, il faudra procéder par élimination en utilisant, successivement, 2 fonctions SI(). Les 2 fonctions seront imbriquées.

=SI(Test1;vrai test 1; SI(test2;vrai test2;faux test2))

la 2ème fonction SI() s'exécutera si (et seulement si) la réponse au TEST1 est "FAUX"

Exemple :

Vous décidez d’élaborer une facture. Vous souhaitez faire bénéficier à votre client d’une remise sur le prix de vente. Cette remise sera de 5% du total hors taxes de la facture si ce total inférieur à 1000 €. Si le total hors taxes est supérieur ou égal à 1000 €, le taux de la remise sera de 10% sur la totalité du montant.

Le document Excel se présentera ainsi : 

 

La formule en D6, compare tout d’abord le résultat obtenu en D5 au nombre 1000.

Si le montant en D5 est plus petit que 1000, alors (symbolisé par le point virgule) le résultat à afficher en D6 sera celui du calcul : D5 multiplié par 5%.

Sinon (symbolisé par le deuxième point virgule) c’est à dire si le montant en D5 est égal ou plus grand que le nombre 1000, le résultat à afficher en D6 sera celui du calcul : D5 multiplié par 10 %.

5.     La fonction ET()

Les conditions posées dans le test de la fonction SI() seront multiples et pour que la réponse aux conditions soit "VRAI", il faudra quelles se vérifient toutes.

=SI(ET(Cond1;Cond2;...;CondN); action à réaliser si les N conditions sont satisfaites; action à réaliser si au moins une des conditions n'est pas satisfaite) 

Exemple : On désire attribuer une ristourne de fin d'année de 2% aux clients qui remplissent les 2 conditions suivantes : être grossiste ET avoir acheté plus de 100 000 € de marchandises chez nous.

Nos clients sont répertoriés dans le tableau ci-dessous.

	A	B	C
1	Nom du client	Type de client	Achats
2	EMELINE	Grossiste	67 000 €
3	ACQUIN	Détaillant	138 000 €
4	HENDOL	Grossiste	213 000 €
5	JUNEZ	Détaillant	59 500 €

On crée une colonne ristourne et on saisi dans cette colonne la formule suivante :

=SI(ET(type de client="grossiste";achats>100000);achats*2%;0) 

Remarque : Cette formule peut être recopiée sur les lignes suivantes. On remarque que l'on utilise les noms des en-têtes de colonnes. Cette technique fonctionne indépendamment des fonctions SI(), ET() et OU().

	A	B	C	D
1	Nom du client	Type de client	Achats	Ristourne
2	EMELINE	Grossiste	67 000 €	0 €
3	ACQUIN	Détaillant	138 000 €	0 €
4	HENDOL	Grossiste	213 000 €	4 260 €
5	JUNEZ	Détaillant	59 500 €	0 €

6.     La fonction OU()

Les conditions posées seront multiples et pour que la réponse aux conditions soit "VRAI", il faudra que l'une au moins se vérifie.

=SI(OU(Cond1;Cond2;...;CondN);action à réaliser si au moins une des conditions est satisfaite;action à réaliser si aucune des conditions n'est satisfaite)

 Exemple : Une entreprise souhaite verser une prime de fin d'année à ses représentants s'ils remplissent l'une OU l'autre des conditions suivantes :

• Avoir plus de 5 ans d'ancienneté
• Avoir réalisé plus d' 1 Million d'euros de chiffre d'affaires.

Les représentants sont répertoriés dans le tableau suivant :

	A	B	C
1	Nom du Représentant	Années d'ancienneté	Chiffre d'affaires
2	ARMAND	3	1 213 000
3	FLORA	6	997 000
4	NINIAN	7	1 016 000
5	SEBATI	1	757 000

 

On crée une colonne PRIME et on y construit la formule suivante :

Colonne C

Colonne B

=SI(OU(années d'ancienneté>5;chiffre d'affaires>1000000);1000;0)

Remarque : Cette formule peut être recopiée sur les lignes suivantes. On remarque que l'on utilise les noms des en-têtes de colonnes. Cette technique fonctionne indépendamment des fonctions SI(), ET() et OU().

	A	B	C	D
1	Nom du Représentant	Années d'ancienneté	Chiffre d'affaires	Prime
2	ARMAND	3	1 213 000	0 €
3	FLORA	6	997 000	0 €
4	NINIAN	7	1 016 000	4 260 €
5	SEBATI	1	757 000	0        €

 

V.           Référence absolue – relative

La maîtrise des références relatives et absolues s'avère vite indispensable sous Excel dès lors que l'on réalise des tableaux un peu complexes.

1.      Référence relative

 Ce sont celles que vous utilisez, sans le savoir, lorsque vous recopiez par défaut une formule de calcul.
Les références relatives ont la particularité d'ajuster automatiquement les coordonnées des cellules concernées par la formule de calcul.
Ainsi, dans le tableau ci-dessous, lorsque vous recopiez le montant de la première ligne vers la deuxième ligne -et les suivantes-, les références des cellules s'ajustent automatiquement : C2*B2 devient C3*B3 sur la ligne suivante puis C4*B4 et ainsi de suite.

2.      Référence Absolue

Si on ne veut pas que Excel ajuste les références lorsqu'on copie une formule dans une autre cellule, il faut utiliser une référence absolue. Par exemple, si la formule multiplie la cellule A5 par la cellule C1 (=A5*C1) et qu'on la copie dans une autre cellule, Excel ajuste les deux références. On peut créer une référence absolue en plaçant le signe $ avant les éléments de la référence qui ne doivent pas être modifiés. Par exemple, pour créer une référence absolue à la cellule C1, on ajoute les signes $ à la formule comme indiqué ci-contre : =A5*$C$1.

Exemple :

On ajoute une colonne au tableau précédent pour calculer le montant TVA et une cellule TVA. Dans la première cellule( E5) on écrit la formule =D5*B2, si  on généralise la formule pour les autres cellules, on aura le résultat suivant :

Que constatez-vous ?
Le résultat renvoie des messages d'erreurs (#VALEUR!)  Pourquoi ?  Observez de près la formule : elle est devenue =D7*B4. Or la cellule B4 ne contenant pas de TVA. Il aurait fallu ancrer notre formule initiale sur la cellule B2 avant de la recopier vers le bas.  C'est là qu'interviennent les références absolues.

Les références absolues permettent d'ancrer une formule sur une cellule déterminée. Lorsque la formule est recopiée, la référence à la cellule absolue ne change pas.

Comment faire ?

Tout d'abord, commençons par un rappel élémentaire mais essentiel : les références d'une cellule sont composées de deux arguments : un nom de colonne (ex : B) et un numéro de ligne (ex 2) pour la cellule B2.

Pour transformer une référence relative en référence absolue, il suffit d'intercaler le symbole $

devant le numéro de ligne et/ou le numéro de colonne.          
On peut soit insérer manuellement ce symbole, soit éditer le contenu de la cellule, puis se positionner sur la partie de la formule à modifier (dans notre exemple, il suffit de positionner le curseur soit entre B et 2 soit derrière B2).

1.      Appuyez ensuite une première fois sur la touche F4 : votre référence devient totalement (ligne et colonne) absolue ($B$2). Lors d'une recopie, la référence reste donc toujours $B$2.

2.      Une autre pression sur la touche F4 et seule la ligne devient absolue (B$2). Lors de recopie, la référence à la colonne varie : C$2 puis D$2...

3.      Encore une pression et cette fois, la colonne seule devient absolue ($B2). Lors de recopie, seul le numéro de colonne demeure inchangé : $B2, puis $B3, etc.

4.      Une quatrième pression sur la touche F4 et votre formule redevient relative B2.  

Résumé :

1)      $ devant la lettre de la cellule (colonne) : permet de fixer le nom de la colonne si vous recopiez la fonction d'une colonne à l'autre. 2)      $ devant le numéro de la ligne : permet de figer le numéro de la ligne lorsque vous recopiez la fonction vers le haut ou vers le bas. 3)      $ devant la lettre de la colonne et le numéro de la ligne : ne modifie ni le nom de la colonne ni le numéro de la ligne quel que soit le sens pour recopier la fonction.

Dans notre exemple ci-dessus, pour pouvoir recopier notre formule vers le bas, nous devons donc la transformer de façon à obtenir =D5*$B$2.
Ainsi, chaque montant sera bien multiplié par le contenu de la cellule B2.

VI.          Fonctions de recherche

Dans certains cas il est utile de pouvoir faire apparaître automatiquement des données (constantes; taux; texte) dans un formulaire, sans devoir les saisir manuellement. Par exemple en remplissant une facture il serait utile de faire apparaître automatiquement les données relatives au produit acheté (Prix, description du produit etc.). De même vous aimeriez faire apparaître les données relatives aux clients simplement en saisissant son No de client.

Dans ce cas les fonctions RECHERCHEV ou RECHERCHEH pourront faire l'affaire. Ces fonctions permettent de rapatrier automatiquement des données se trouvant dans une autre feuille ou classeur dans la feuille sur laquelle on travaille. 

Pour utiliser ces fonctions nous avons besoin d'une base de données (p. ex. une liste d’Articles) et d'une feuille contenant un formulaire (p. ex. une facture). Dans le formulaire nous allons sélectionner la cellule dans laquelle devra être rapatriée (affichée) la première donnée (p. ex. la désignation). Afin qu'Excel puisse trouver le bon article il faut l'identifier par un code (p. ex. Code article). Nous avons désormais les données nécessaires pour utiliser la fonction Recherche. Notre liste d’articles étant ordonnée verticalement nous allons utiliser la fonction RECHERCHEV.

Cette fonction requiert 4 arguments et se présente ainsi :

=RECHERCHEV(valeur_cherchée;table_matrice;no_index_col;valeur_proche)

La fonction va rechercher, dans une table_matrice, la valeur_cherchée que l’on va lui indiquer. Quand elle aura trouvé cette valeur (nombre ou texte), elle va renvoyer le contenu de la cellule se trouvant sur la même ligne que la valeur trouvée et dans la colonne désignée par no_index_col.

Si la valeur_cherchée n’existe pas dans la table_matrice, la fonction affichera :

• un message d’erreur, si on choisi le nombre 0 pour l’argument valeur_proche
• le contenu de la colonne, déterminée par no_index_col, correspondant à la valeur précédente la plus proche de la valeur_cherchée, si on choisi le nombre 1 pour l’argument valeur_proche

 Dans notre exemple :

Pour élaborer une facture, nous allons utiliser la fonction =RECHERCHEV().

Tout d’abord, nous construisons 2 tableaux, un sur chaque feuille du classeur, un modèle de facture et un tableau des articles (nous nommerons la zone " articles " pour la réutiliser dans les formules).

Facture

 

La première formule à créer permettra d’afficher automatiquement la désignation de l’article (dans la colonne " désignation ") dont le code aura été saisi en A22 (colonne " code article). Nous allons construire cette formule dans la colonne " Désignation " (en B22)

La formule, que l’on va construire dans la cellule B22, sera donc la suivante :

=RECHERCHEV(A22;articles;2;0)

• La valeur_cherchée sera le code de l’article, préalablement saisi en A22.
• La table_matrice est la plage de cellules que l’on a préalablement nommé " articles ".
• no_index_col sera ici le chiffre 2 qui correspond à la deuxième colonne de notre zone " articles ", et qui contient le nom des articles.
• valeur_proche sera ici le chiffre 0 qui permettra d’afficher un message d’erreur si le code article saisi n’existe pas.

Le résultat sera le suivant :

La fonction construite en B22 renvoie la valeur " Disque dur ".

En effet :

• la fonction recherche la valeur 1, saisie en A22, dans la zone " articles " en se déplaçant VERTICALEMENT dans la première colonne de la zone.
• une fois la valeur_cherchée trouvée, la fonction lit la ligne correspondante dans la zone jusqu’à la colonne choisie dans no_index_col.
• la fonction renvoie la valeur de cette cellule.

Pour obtenir le prix de l’article, la fonction est la même que la précédente, mis à part le no_index_col qui est ici le chiffre 3 (troisième colonne de la zone " articles "). La fonction construite en D22 est la suivante : =RECHERCHEV(A22;articles;3;0)  

Remarque :

Il en va de même avec la fonction RECHERCHEH, à la différence près qu'elle recherche la donnée horizontalement, ce qui implique que les données doivent être disposées horizontalement.