La bible de Excel version 2010 ou superieur pdf


Télécharger La bible de Excel version 2010 ou superieur pdf

Formation Excel en ligne par vidéo

205 leçons vidéos + 20 Livres PDF + 20 TP + Sous supervision + Certificat de réussite à la fin du cours



★★★★★★★★★★3.5 étoiles sur 5 basé sur 1 votes.
Votez ce document:

Télécharger aussi :


La bible de Excel version 2010 ou superieur pdf

...

1.1. Connaître les principes de conception

Voyons d’abord comment saisir une formule simple puis comment la modifier.

Saisir une formule

Une formule de calcul débute impérativement par le caractère = (égal). Ce dernier indique à Excel qu’il doit considérer les éléments qui vont être saisis comme une formule de calcul, et non comme une simple entrée numérique ou alphanumérique.

Prenons un exemple :

1.   Créez un nouveau classeur.

2.   En A1, saisissez =19+71.

3.   Appuyez sur [Ä].

Le résultat du calcul apparaît en A1, soit 90.

La formule, quant à elle, apparaît dans la barre de formule.

Figure 1.1 : Une formule simple

1.1. Connaître les principes de conception

Utilisation du pavé numérique

Pour saisir des formules simples, comme celle que nous venons de voir, utilisez de préférence le pavé numérique : vous gagnerez du temps. Mais sur ce dernier, le signe = n’est pas présent. La solution est simple : au lieu de saisir =, entrez le caractère + (plus) si le premier nombre est positif ou ? (moins) s’il est négatif. Ainsi, si vous saisissez +19+71, Excel convertit en =19+71.

Modifier une formule

La saisie de formule se révèle d’une grande simplicité. Mais personne n’est à l’abri d’une erreur. C’est pourquoi, il est important de pouvoir modifier une formule saisie.

Reprenons l’exemple précédent en supposant que la formule correcte soit =(19+71)/5 :

1.   Double-cliquez sur la cellule A1.

2.   À l’aide de la souris ou en utilisant les touches [î] et [ï], déplacez le curseur après le caractère = et saisissez la parenthèse

ouvrante.

3.   Déplacez le curseur à la fin de la formule, à l’aide de la touche [Fin], et saisissez la fin de la modification.

4.   Appuyez sur [Ä].

Le nouveau résultat s’affiche immédiatement (18).

Autres méthodes

Il existe deux autres façons de modifier une formule. Le résultat est bien sûr identique quelle que soit la méthode utilisée.

La première variante consiste à sélectionner la cellule contenant la formule à modifier et à appuyer sur la touche [F2].

Au moment où vous avez saisi la parenthèse fermante, les deux parenthèses sont apparues fugitivement en gras. Excel signifie de cette façon qu’il a « compris » que la parenthèse fermante était associée à la parenthèse ouvrante. Cela permet de se repérer plus facilement lorsqu’il existe plusieurs niveaux de parenthèses dans une formule.

La seconde variante consiste à sélectionner la cellule et à cliquer dans la barre de formule.

Choisissez la méthode qui convient le mieux à vos habitudes de travail.

1.2.       Utiliser des opérateurs

Nous allons décrire les différents opérateurs pris en charge par Excel, en commençant bien sûr par les plus classiques : les opérateurs mathématiques. Mais nous verrons également un opérateur qui permet de traiter les chaînes de caractères.

Utiliser des opérateurs mathématiques
Connaître les règles de priorité des opérateurs

Pour concevoir vos formules, vous disposez des opérateurs mathématiques courants : j ^ : la puissance ; j * : la multiplication ; j / : la division ; j + : l’addition ; j ? : la soustraction.

Les règles de priorité des opérateurs sont évidemment respectées, c’est-à-dire que les expressions utilisant l’opérateur puissance sont évaluées en premier, puis viennent, au même niveau, la multiplication et la division, et enfin l’addition et la soustraction (même niveau).

Voici quelques exemples de formules mettant en jeu les opérateurs mathématiques :

1.2. Utiliser des opérateurs

Tableau 1.1 : Quelques exemples de formules de calcul

Formule
    

Résultat

=4+5*3
    

= 4+15 = 19

=2?3+10/2
    

= 2-3+5 = 4

=5*6/2?18/3
    

= 15-6 = 9

=2^2*5+3?2
    

= 4*5+3-2 = 20+3–2 = 21
Utiliser des parenthèses

Les parenthèses permettent d’influer sur les règles de priorité des opérateurs mathématiques. En effet, toute expression placée entre parenthèses est évaluée de façon prioritaire. Il est évidemment possible d’imbriquer des parenthèses.

À titre d’exemple, vous allez calculer le prix TTC d’un ensemble de deux articles dont les prix HT sont 75 euros et 100 euros, sur lesquels une remise respective de 10 % et 5 % a été préalablement appliquée.

Pour obtenir un résultat correct, il faut utiliser des parenthèses. En effet, il s’agit d’abord d’évaluer le prix total HT, compte tenu de la remise, puis de calculer le prix TTC :

1.   Sélectionnez A3.

2.   Saisissez =(75*(1?10%)+100*(1?5%))*(1+19,6%).

3.   Appuyez sur [Ä].

Voici comment Excel évalue cette formule : j =(75*0,9+100*(1?5%))*(1+19,6%) : étape 1 ; j =(67,5+100*(1?5%))*(1+19,6%) : étape 2 ; j =(67,5+100*0,95)*(1+19,6%) : étape 3 ; j =(67,5+95)*(1+19,6%) : étape 4 ; j =162,5*(1+19,6%) : étape 5 ; j =162,5*1,196 : étape 6 ; j =194,35 : étape 7.

Lors de la saisie, chaque niveau de parenthèse possède une couleur. Cela permet de mieux visualiser la hiérarchie des parenthèses. De plus, lorsque vous refermez une parenthèse, la paire (ouvrante et fermante) est mise brièvement en gras.

Utiliser l’opérateur de concaténation

L’opérateur & permet de concaténer des chaînes de caractères. Jusqu’à présent, nous avons évoqué des formules dont les opérandes étaient numériques. Or, il peut être nécessaire de manipuler des opérandes alphanumériques avec lesquels les opérateurs mathématiques n’ont aucun sens (essayez de diviser « Bonjour » par « Au revoir » !).

1.   Sélectionnez A2.

2.   Saisissez =" Micro "& " "& "Application ".

3.   Appuyez sur [Ä].

Figure 1.2 : Une formule alphanumérique

Il est possible de concaténer des expressions numériques avec des expressions alphanumériques.

1.3.     Maîtriser les références relatives, absolues et mixtes

Si les possibilités en matière de calcul en restaient là, Excel ne serait qu’une super-calculatrice. Or cette application est beaucoup plus que cela. Sa puissance supplémentaire provient, entre autres, de la possibilité de faire référence à d’autres cellules dans une formule. Cette faculté autorise la conception de formules complexes et puissantes. Nous aborderons progressivement l’utilisation des différents types de références dans la conception des formules de calcul.

Découvrir les références de cellules

Donner la référence d’une cellule en indiquant la ligne et la colonne où elle se trouve permet de localiser ladite cellule dans un classeur. Les colonnes sont identifiées par des lettres de (de A à XFD, soit 16384 colonnes) et les lignes par des chiffres (de 1 à 1 048 576). Par exemple, A1 localise la cellule située à l’intersection de la première colonne et de la première ligne, B10 la cellule située à l’intersection de la deuxième colonne et de la dixième



ligne.

Lorsque vous saisissez la référence d’une cellule dans une formule, vous utilisez le contenu de la cellule correspondante dans votre formule.
Utiliser des références de cellules

Voici l’exemple le plus simple d’utilisation d’une référence de cellule dans une formule :

1.   Sélectionnez A4.

2.   Saisissez =A3.

3.   Appuyez sur [Ä].

Figure 1.3 : Utilisation d’une référence

Le contenu de la cellule A4 est maintenant égal au contenu de la cellule A3. Si ce dernier varie, celui de A4 varie également.

Pour mesurer l’intérêt de ce mécanisme, reprenons l’exemple de calcul du prix TTC des deux articles. En cas de changements de tarif ou de taux de remise, il faut modifier la formule contenue en A3, ce qui n’est pas très aisé. Vous utiliserez plutôt des références pour vous faciliter la tâche :

1.   En A6, saisissez 75.

2.   En B6, saisissez 10%.

3.   En A7, saisissez 75.

4.   En B7, saisissez 10%.

5.   En A8, saisissez 19,6%.

6.   En A10, saisissez =((A6*(1?B6)+A7*(1?B7))*(1+A8)).

Au fur et à mesure de l’écriture de la formule, les références des cellules qu’elle intègre sont affichées avec des couleurs différentes. Ces couleurs sont reprises au niveau de la bordure des cellules correspondantes. Vous avez ainsi une vision synthétique des cellules impliquées dans une formule (à condition, bien sûr, qu’elles soient toutes visibles à l’écran).

Figure 1.4 : Utilisation de références dans un calcul

7.   Validez par [Ä].

Le résultat contenu dans la cellule A10 est égal à celui contenu dans A3 mais il est beaucoup plus facile d’intégrer d’éventuelles modifications de tarif, de remise ou de taux de TVA en utilisant des références comme en A10.

Figure 1.5 : Résultat de la formule

Saisir une formule sous forme de texte ?

Dans certains cas, il peut se révéler intéressant d’afficher une formule et non son résultat (pour légender une feuille de calcul par exemple).

Pour cela, faites-la précéder d’une apostrophe.
Saisir des références de cellules avec la souris

La dernière formule que vous avez créée compte cinq références de cellules. Leur saisie au clavier ne pose pas de problème car elles sont peu nombreuses et toutes visibles à l’écran en même temps. Mais dans la plupart des cas, il est préférable de sélectionner les cellules correspondantes afin d’insérer leur référence dans la formule en cours.

1.   Sélectionnez A47.

2.   Saisissez =(.

3.   Faites défiler, à l’aide de la barre de défilement verticale, la feuille de calcul jusqu’à ce que la cellule A6 soit visible.

4.   Sélectionnez la cellule A6 à l’aide de la souris. En réaction, Excel l’entoure de pointillés et d’une bordure de couleur. De plus, sa référence apparaît dans la barre de formule, qui affiche le contenu de la cellule active (A47).

Figure 1.6 : Saisie de référence à l’aide de la souris

5.   Continuez la définition de la formule en saisissant les opérateurs et les parenthèses au clavier et en sélectionnant les références avec la souris.

6.   Validez à l’aide de la touche [Ä].
Distinguer les différents types de références : relatives absolues et mixtes

Jusqu’à présent, vous avez utilisé des références relatives : lorsque vous avez saisi =A3 dans la cellule A4, vous avez fait une référence, non pas à la cellule A3 en tant que telle, mais à la cellule se trouvant une ligne au-dessus de la cellule en cours (en l’occurrence A4). Ainsi, lorsque vous copiez le contenu de la cellule A4 et le collez en B10, B10 contient =B9, et non =A3.

Pour faire référence à la cellule A3, il faut utiliser une référence absolue. Elle se présente sous la forme suivante : $A$3. Si vous saisissez =$A$3 en A4, puis copiez le contenu de la cellule A4 et le collez en B10, B10 contient =$A$3. Le caractère $ indique que c’est bien à la colonne A et à la ligne 3 auxquelles il est fait référence.

Il est possible de combiner des références absolues à des colonnes avec des références relatives à des lignes, et vice versa. Il s’agit alors de références mixtes.

Lors de la saisie d’une formule, vous pouvez facilement passer d’un mode de référence à l’autre à l’aide de la touche [F4] :

1.   Double-cliquez sur A10.

2.   Positionnez le curseur à côté de la référence A6 (après le 6, par exemple).

3.   Appuyez sur [F4]. La référence devient $A$6.

4.   Appuyez une deuxième fois sur [F4]. La référence devient A$6.

5.   Appuyez une troisième fois sur [F4]. La référence devient $A6.

6.   Appuyez encore une fois sur [F4]. La référence redevient A6.
Utiliser des références relatives et absolues

Dans un premier temps, vous allez mettre en pratique les références absolues et relatives. En ce sens, vous allez créer une feuille de calcul pour déterminer les tarifs de différents produits, compte tenu d’un taux de remise et d’un taux de TVA.

1.   En A1, saisissez TARIF.

2.   En E3, saisissez Taux TVA.

3.   En F3, saisissez 19,6%.

4.   En A5, B5, C5, D5, E5, F5, saisissez respectivement Référence, Libellé, P.U. H.T., Remise, P.U. net H.T., P.U. T.T.C..

5.   Saisissez les différentes lignes d’exemple :

Tableau 1.2 : Lignes d’exemple

Référence
    

Libellé
    

P.U. H.T.
    

Remise

ABC1
    

Bloc notes
    

0,93
    

15 %

ABC2
    

Enveloppes (500)
    

11,07
    

12 %

ABC3
    

Stylo
    

0,66
    

25 %

ABC4
    

Gomme
    

0,76
    

12 %

ABC5
    

Marqueur
    

1,65
    



14 %

ABC6
    

Agrafeuse
    

9,95
    

20 %

ABC7
    

Classeur
    

2,57
    

33 %

ABC8
    

Surligneur
    

0,66
    

25 %

6.   Sélectionnez A1:F1 et cliquez sur Fusionner et centrer (onglet Accueil, groupe Alignement). Appliquez une taille de police de 16 et mettez le texte en gras.

7.   Sélectionnez E3:F3 et appliquez un contour de type quadrillage. Mettez E3 en gras.

8.   Sélectionnez A5:F5, centrez le texte et mettez-le en gras.

Sélection de la zone courante

Dans Excel, une zone est une plage de cellules séparées des autres par des cellules vides. Ainsi, dans la feuille que vous êtes en train d’élaborer se trouvent trois zones : A1:F1, E3:F3 et A5:F13. Pour sélectionner rapidement la zone à laquelle appartient la cellule active, appuyez sur [Ctrl]+[*]. Ici, sélectionnez A5 (par exemple) et appuyez sur [Ctrl]+[*] pour sélectionner A5:F13.

9.   Sélectionnez A5:F13 et appliquez un contour de type qua-

drillage.

10.Sélectionnez C6:C13, ainsi que E6:F13 et appliquez le format Monétaire.

Figure 1.7 : La feuille de calcul initiale

Cette feuille présente plusieurs articles avec, pour chacun d’eux, son prix HT ainsi qu’un taux de remise. Le but est de calculer, pour chaque article, le prix net HT (c’est-à-dire compte tenu de la remise) et le prix TTC.

Pour calculer le prix net, il faut à chaque ligne appliquer le taux de remise :

1.   En E6, saisissez =C6*(1?D6).

2.   Étendez le contenu, à l’aide de la poignée de recopie, jusqu’en E13. Pour cela, sélectionnez la cellule E6 et approchez le pointeur de la souris de la poignée de recopie de la cellule. La poignée de recopie est le petit carré noir situé dans l’angle inférieur droit. Le pointeur change d’apparence : il se transforme en une petite croix noire.

          Figure 1.8 : Le pointeur change d’apparence

Extension rapide d’une formule dans une colonne

Pour étendre encore plus rapidement une formule dans une colonne, sélectionnez la cellule qui contient la formule à étendre et double-cliquez sur la poignée de recopie de cette cellule. La formule est alors étendue

3.   Cliquez à présent sur la poignée de recopie et déplacez le pointeur jusqu’en E13. La plage E6:E13 est entouré d’un contour gris. Relâchez le bouton de la souris. La formule de calcul de la cellule E6 a été « étendue » aux autres cellules de la plage.

jusqu’à la ligne correspondant à la dernière cellule non vide des colonnes immédiatement adjacentes. Ainsi, si vous double-cliquez sur la poignée de recopie de la cellule E6, sa formule est étendue jusqu’en E13, puisque la colonne D contient des valeurs jusqu’en D13.

Vous avez ainsi mis à profit les propriétés des références relatives : elles localisent les cellules par rapport à la cellule active. En étendant le contenu de la cellule active à celles situées dans la même colonne, vous avez « fait suivre » les références relatives de la formule.

Il reste maintenant à calculer les prix TTC. Pour cela, il faut utiliser le taux de TVA situé dans la cellule F3 :

1.   En F6, saisissez =E6*(1+F3).

2.   Étendez le contenu, à l’aide de la poignée de recopie, jusqu’en F13.

Figure 1.9 : Première tentative de calcul du prix TTC

Le résultat n’est pas très probant. Vous avez sans doute déjà identifié les causes du problème : la référence à F3 doit être absolue, puisque ce taux doit être utilisé de la même façon à chaque ligne.

1.   Double-cliquez sur F6.

2.   Positionnez le curseur à côté de la référence F3 (après le 3, par exemple).

3.   Appuyez sur [F4]. La référence devient $F$3.

4.   Validez par [Ä].

5.   Étendez le contenu, à l’aide de la poignée de recopie, jusqu’en F13.

Figure 1.10 : Deuxième tentative de calcul du prix TTC

Cette deuxième tentative est sans doute plus conforme à vos attentes.

L’emploi de références relatives ou absolues est principalement conditionné par le comportement attendu de la formule lorsqu’elle sera copiée. En effet, une formule est rarement « isolée », elle fait souvent partie de lignes ou de colonnes présentant des formules semblables, obtenues par recopie d’une formule

initiale.

Utiliser des références mixtes
Pour mettre en pratique les références mixtes, vous allez construire une feuille de calcul qui permet de déterminer la capacité de production d’un atelier, en fonction de la capacité horaire de chaque machine et de la durée d’ouverture journalière de l’atelier.

1.   En A1, saisissez Capacités de production – Atelier XXXX.

2.   En C3, saisissez Lundi et étendez le contenu de la cellule jusqu’en I3.

3.   En B4, saisissez Durée du travail.

4.   En C4, D4, E4, F4, G4, H4, I4, saisissez respectivement 8, 10, 10, 10, 8, 6, 0.

5.   En A6, saisissez Machine.

6.   En B6, saisissez Capacité.

7.   En C6, saisissez Lundi et étendez le contenu de la cellule jusqu’en I6.

8.   En J6, saisissez Total.

9.   En A7, saisissez Machine 1 et étendez le contenu de la cellule jusqu’en A12.

10.En A13, saisissez Total / jour.

11.En B7, B8, B9, B10, B11, B12, saisissez respectivement 100, 150, 75, 98, 102, 123.

12.Sélectionnez A1:J1 et cliquez sur Fusionner et centrer (onglet Accueil, groupe Alignement). Appliquez une taille de police de 16 et mettez le texte en gras.

13.Sélectionnez C3:I3 et appliquez un contour de type quadrillage. Mettez le texte en gras.

14.Sélectionnez B4:I4 et appliquez un contour de type quadrillage. Mettez B4 en gras.



142