? Initiation a l’Excel – Introduction Le logiciel Excel est un tableur : il permet de gerer des tableaux de donnees. Lancer Excel par Demarrer Programmes ou par son icone. Les cases de la grille sont appelees cellule. L’unique cellule entouree de la grille est la cellule courante ou cellule active. Chaque cellule est a l’intersection d’une colonne et d’une ligne. Il y a deux notations standards pour reperer lignes et colonnes : Les colonnes sont reperees par des lettres et les lignes par des numeros. Ainsi A1 est l’adresse de la cellule en haut a gauche, elle se trouve a l’intersection de la colonne A et de la ligne 1.
Les colonnes sont reperees par C et les ligne par L : L1C1 au lieu de A1. Pour passer d’une reference a une autre : cocher A1 dans le menu Outils, item Options, onglet General, cadre Reference. Exercice : Activer differentes cellules a l’aide des touches de direction ou en cliquant avec la souris, observer l’adresse de la cellule active en haut a gauche. Passer d’une reference a une autre comme indiquee ci-dessus. Observez les differentes adresses. La petite zone de texte au dessus de la grille est la zone de saisie des cellules. Une cellule
I ) Les valeurs Placez vous sur la cellule C2 et saisissez le mot bouteille. Ce texte s’affiche dans la cellule et aussi dans la zone de saisie. Terminez la saisie en tapant un retour chariot. La saisie est validee et Excel passe a la cellule en dessous. Placez vous sur la cellule B2 et saisissez le nombre 28. A B C D E F G H I J 1 2 28 bouteille 3 11 4 3 5 II ) Les formules La force d’un tableur vient de sa capacite a faire des calculs automatiquement mis a jour en fonction de l’evolution des valeurs. Pour faire un calcul il faut donner une formule. Une formule commence toujours par =.
Dans la cellule D3 saisissez le nombre 11 et dans la cellule D4 le nombre 3. Puis positionnez-vous dans la cellule D5 et saisissez la formule =D3*D4. Validez. Qu’observez-vous ? Modifiez maintenant la valeur dans la cellule D3, qu’observez vous ? Que se passe t’il si vous saisissez du texte dans la cellule D3 ? III ) Recopie ou glissement Exercice : Activez la cellule D5. Deplacer legerement la souris vers la droite sans cliquer. Vous devez voir une petite croix noire. Cliquer en gardant le doigt appuyer sur le bouton gauche de la souris et deplacer la souris pour arriver jusqu’a la cellule E5 : Vous avez fait un glissement.
Regarder le contenu de E5 : Excel a recopie la formule en modifiant automatiquement le numero des lignes concernees. Autres formules : Exercice : Remplir les cellules comme suit : A B C D 1 Jan Fev Mars 2 Ventes 45 21 39 3 Achats 12 8 15 4 Benef 5 6 Placements 13 10 14 7 Total En B4, saisir = B2-B3 (utiliser la souris comme dans l’exercice 2). Recopier a droite. En B7, saisir = somme( selectionner avec la souris les cellules B4 a B6, taper ) et valider. Vous devez voir apparaitre en B7, la formule = somme(B4:B6). Que signifie t’elle ? Recopier a droite. En E7 cliquer le bouton ? et valider. Que se passe t’il ? Utilisation du $
Exercice : On veut convertir des sommes en euros en francs. Saisir le tableau suivant : A B C D E 1 € FF 2 1 6,55957 3 10 4 15 5 20 6 25 Dans la cellule B3, ecrire la formule : =A3*B2 Recopier ou glisser cette formule en bas. Les resultats obtenus ne semblent pas corrects. Observer les differentes formules dans les cellules B4 a B5. Reference relative En fait, dans la formule precedente, les references des cellules sont relatives : Quand on recopie la formule A3*B2 juste en dessous, en B4, elle devient A4*B3 : les numeros de lignes augmentent de 1, les numeros de colonnes ne changent pas car on a recopie dans la meme colonne.
Que devient la formule de B3 quand elle a ete recopiee en B5 ? Que serait-elle devenue si on l’avait recopie en C3 ? en D4 ? en E6 ? On veut dans le cas precedent que la reference B2 ne change pas pendant la recopie car c’est dans cette cellule que se trouve le taux de conversion (qui est le meme pour toutes les lignes). Reference absolue Ecrire en B3 la formule =A3*$B$2. Recopier vers le bas. Verifier que les resultas sont corrects. Observer les formules situees en B4, B5 et B6. Remarquer ce qui change et ce qui ne change pas dans les formules. Quand on met le signe dollar devant une reference, celle-ci ne change pas en recopiant.
On a deux signes dollars : le premier fixe le B, le deuxieme fixe la ligne 3. Exercice : Saisir le tableau suivant A B C D E F 1 Ja Fe Ma Total % 2 Jean 55 31 39 3 Louis 72 67 27 4 Paul 29 48 38 5 Total 6 % Ecrire les formules pour trouver tous les totaux en utilisant le bouton ? et la recopie. Calculer les pourcentages par rapport au total situe en E5 en utilisant $E$5 : on rappelle qu’un pourcentage est simplement une division. Utiliser Format cellules pourcentage ou bien le bouton % pour avoir une bonne presentation des resultas. Reference semi-absolue On veut convertir des sommes en euros en plusieurs devises.
Saisir le tableau suivant : A B C D E 1 € FF ? $ 2 1 6,55957 0,54 0,87 3 10 4 15 5 20 6 25 On voudrait ecrire une seule formule et recopier sur tout le tableau. Les references absolues ne conviennent pas pour toutes les colonnes : Dans la cellule B3, ecrire la formule =A3*$B$2. Recopier en bas, puis a droite. Verifier que les resultats ne sont pas ceux attendus. En fait, on veut que le taux depende de la devise on ne peut pas laisser fixe le B dans $B$2. Par contre c’est toujours a la ligne 2 que se trouve le taux. Ecrire en B3 la formule =A3*B$2. Recopier ou glisser cette formule en bas, puis a droite.
Les resultats semblent t’ils corrects. Observer les formules. Expliquer. Exercice : Il s’agit de construire la table de multiplication suivante : A B C D E F G H I J 1 1 2 3 4 5 6 7 8 9 2 1 1 2 3 4 5 6 7 8 9 3 2 2 4 6 8 10 12 14 16 18 4 3 3 6 9 12 15 18 21 24 27 5 4 4 8 12 16 20 24 28 32 36 6 5 5 10 15 20 25 30 35 40 45 7 6 6 12 18 24 30 36 42 48 54 8 7 7 14 21 28 35 42 49 56 63 9 8 8 16 24 32 40 48 56 64 72 10 9 9 18 27 36 45 54 63 72 81 Taper la ligne 1 et la colonne A. Dans la cellule B2, on inscrit la formule =A2*B1 Faites glisser cette formule de C2 a K2, expliquez le resultat. Revenez dans la cellule B2, nscrivez la formule =$A2*B1 Refaites le glissement, quelle est l’utilite du $ ? Placez un autre $ dans le formule de la cellule B2 de maniere a pouvoir remplir tout le tableau en deux glissements un horizontal et un vertical. Exercice : Soit le tableau suivant sous Excel : A B C D 1 1 2 2 3 4 3 4 Repondre aux questions puis verifier vos reponses avec Excel : 1) Dans la cellule A3, la formule =SOMME(A1 :A2) est ecrite. Quelle sera le resultat affiche ? 2) La formule precedente (=SOMME(A1 :A2)) est recopiee de la case A3 a B3. Donner la formule de la case B3. Quel sera le resultat affiche ? ) Dans la cellule A4, la formule =SOMME($A1 :A2) est ecrite. Quelle sera le resultat affiche ? 4) La formule precedente (=SOMME($A1 :A2)) est recopiee de la case A4 a B4. Donner la formule de la case B4. Quel sera le resultat affiche ? 5) Dans la cellule C1, la formule =SOMME($A1 :B1) est ecrite. Quelle sera le resultat affiche ? 6) La formule precedente (=SOMME($A1 :B1)) est recopiee de la case C1 a C2. Donner la formule de la case C2. Quel sera le resultat affiche ? IV ) Graphiques Reproduire le tableau suivant : A B C D E 1 2 3 EDF 4 Janvier 2 675,19 5 Fevrier 2 716,83 6 Mars 2 557,98 7 Avril 2 484,62 8 Mai 2 596,07 9 Juin 372,45 10 Juillet 2 230,86 11 Aout 2 223,01 12 Septembre 2 364,04 13 Octobre 2 780,40 14 Novembre 2 586,62 15 Decembre 2 896,66 Selectionnez la zone de B3 a C15 puis cliquez sur le bouton de l’assistant graphique, choisir l’icone histogramme et appuyer sur le bouton Suivant, continuez jusqu’au dernier cadre et faire Fin. Vous obtenez le diagramme suivant : En cliquant avec le bouton droit de la souris dans la zone du graphique, on fait apparaitre un menu contenant Type de Graphique. Cliquez dessus et modifiez le graphique avec des secteurs. Graphiques Reproduire la colonne A ainsi que la ligne 2, puis ecrire en B3 la formule : ($A3-B$2)*($A3+B$2), faire un double glissement pour obtenir le tableau. A B C D E F G H I J H K L M N O P Q R T U V 1 2 -1 -0,9 -0,8 -0,7 -0,6 -0,5 -0,4 -0,3 -0,2 -0,1 0 0,1 0,2 0,3 0,4 0,5 0,6 0,7 0,8 0,9 1 3 -1 0,0 0,2 0,4 0,5 0,6 0,8 0,8 0,9 1,0 1,0 1,0 1,0 1,0 0,9 0,8 0,8 0,6 0,5 0,4 0,2 0,0 4 -0,9 -0,2 0,0 0,2 0,3 0,5 0,6 0,7 0,7 0,8 0,8 0,8 0,8 0,8 0,7 0,7 0,6 0,5 0,3 0,2 0,0 -0,2 5 -0,8 -0,4 -0,2 0,0 0,2 0,3 0,4 0,5 0,6 0,6 0,6 0,6 0,6 0,6 0,6 0,5 0,4 0,3 0,2 0,0 -0,2 -0,4 6 -0,7 -0,5 -0,3 -0,2 0,0 0,1 0,2 0,3 0,4 0,5 0,5 0,5 0,5 0,5 0,4 0,3 0,2 0,1 0,0 -0,2 -0,3 -0,5 7 -0,6 -0,6 -0,5 -0,3 -0,1 0,0 0,1 0,2 ,3 0,3 0,4 0,4 0,4 0,3 0,3 0,2 0,1 0,0 -0,1 -0,3 -0,5 -0,6 8 -0,5 -0,8 -0,6 -0,4 -0,2 -0,1 0,0 0,1 0,2 0,2 0,2 0,3 0,2 0,2 0,2 0,1 0,0 -0,1 -0,2 -0,4 -0,6 -0,8 9 -0,4 -0,8 -0,7 -0,5 -0,3 -0,2 -0,1 0,0 0,1 0,1 0,2 0,2 0,2 0,1 0,1 0,0 -0,1 -0,2 -0,3 -0,5 -0,7 -0,8 10 -0,3 -0,9 -0,7 -0,6 -0,4 -0,3 -0,2 -0,1 0,0 0,1 0,1 0,1 0,1 0,1 0,0 -0,1 -0,2 -0,3 -0,4 -0,6 -0,7 -0,9 11 -0,2 -1,0 -0,8 -0,6 -0,5 -0,3 -0,2 -0,1 -0,1 0,0 0,0 0,0 0,0 0,0 -0,1 -0,1 -0,2 -0,3 -0,5 -0,6 -0,8 -1,0 12 -0,1 -1,0 -0,8 -0,6 -0,5 -0,4 -0,2 -0,2 -0,1 0,0 0,0 0,0 0,0 0,0 -0,1 -0,2 -0,2 -0,4 -0,5 -0,6 -0,8 -1,0 13 0 -1,0 -0,8 -0,6 -0,5 -0,4 -0,3 -0,2 0,1 0,0 0,0 0,0 0,0 0,0 -0,1 -0,2 -0,3 -0,4 -0,5 -0,6 -0,8 -1,0 14 0,1 -1,0 -0,8 -0,6 -0,5 -0,4 -0,2 -0,2 -0,1 0,0 0,0 0,0 0,0 0,0 -0,1 -0,2 -0,2 -0,4 -0,5 -0,6 -0,8 -1,0 15 0,2 -1,0 -0,8 -0,6 -0,5 -0,3 -0,2 -0,1 -0,1 0,0 0,0 0,0 0,0 0,0 -0,1 -0,1 -0,2 -0,3 -0,5 -0,6 -0,8 -1,0 16 0,3 -0,9 -0,7 -0,6 -0,4 -0,3 -0,2 -0,1 0,0 0,1 0,1 0,1 0,1 0,1 0,0 -0,1 -0,2 -0,3 -0,4 -0,6 -0,7 -0,9 17 0,4 -0,8 -0,7 -0,5 -0,3 -0,2 -0,1 0,0 0,1 0,1 0,2 0,2 0,2 0,1 0,1 0,0 -0,1 -0,2 -0,3 -0,5 -0,7 -0,8 18 0,5 -0,8 -0,6 -0,4 -0,2 -0,1 0,0 0,1 0,2 0,2 0,2 0,3 0,2 0,2 0,2 0,1 0,0 -0,1 -0,2 -0,4 -0,6 -0,8 19 0,6 -0,6 -0,5 -0,3 -0,1 0,0 0,1 0,2 ,3 0,3 0,4 0,4 0,4 0,3 0,3 0,2 0,1 0,0 -0,1 -0,3 -0,5 -0,6 20 0,7 -0,5 -0,3 -0,2 0,0 0,1 0,2 0,3 0,4 0,5 0,5 0,5 0,5 0,5 0,4 0,3 0,2 0,1 0,0 -0,2 -0,3 -0,5 21 0,8 -0,4 -0,2 0,0 0,2 0,3 0,4 0,5 0,6 0,6 0,6 0,6 0,6 0,6 0,6 0,5 0,4 0,3 0,2 0,0 -0,2 -0,4 22 0,9 -0,2 0,0 0,2 0,3 0,5 0,6 0,7 0,7 0,8 0,8 0,8 0,8 0,8 0,7 0,7 0,6 0,5 0,3 0,2 0,0 -0,2 23 1 0,0 0,2 0,4 0,5 0,6 0,8 0,8 0,9 1,0 1,0 1,0 1,0 1,0 0,9 0,8 0,8 0,6 0,5 0,4 0,2 0,0 Selectionnez la zone de A2 a V23 puis cliquez sur le bouton de l’assistant graphique, choisir l’icone surface et appuyer sur le bouton Suivant, continuez jusqu’au dernier cadre et faire Fin.
V ) Les fonctions Somme et Moyenne Reproduire le tableau de donnees ci-dessous : A B C D E 1 T. V. A. 2 20,6% 3 TELECOM EDF LOYER 4 Janvier 167,67 2 675,19 3 200,70 5 Fevrier 146,86 2 716,83 3 200,70 6 Mars 134,00 2 557,98 3 200,70 7 Avril 127,11 2 484,62 3 200,70 8 Mai 125,00 2 596,07 3 200,70 9 Juin 126,91 2 372,45 3 200,70 10 Juillet 132,33 2 230,86 3 200,70 11 Aout 140,92 2 223,01 3 200,70 12 Septembre 152,43 2 364,04 3 275,80 13 Octobre 166,67 2 780,40 3 275,80 14 Novembre 183,50 2 586,62 3 275,80 15 Decembre 202,82 2 896,66 3 275,80 16 Total 1 806,22 F 30 484,73 F 38 708,80 F 17 T. V. A. 372,08 F 6279,85 F 7974,01 F 8 T. T. C. 2 178,30 F 36 764,58 F 46 682,81 F 19 Moyenne HT 150,52 F 2 540,39 F 3 225,73 F Pour calculer automatiquement le total des charges, la TVA, le total taxes comprises et la moyenne hors taxes, les formules sont les suivantes : dans la cellule C16, =SOMME(C4 :C15), dans la cellule C17, =C16*A2, dans la cellule C18, =C16+C17, dans la cellule C19, =MOYENNE(C4 :C15). SOMME et MOYENNE sont des fonctions que l’on obtient dans le menu Insertion/Fonction. Les fonctions sont rangees par rubrique. Au lieu de refaire le meme travail dans les colonnes D et E, effectuer un glissement apres avoir remplace A2 par $A$2.
Que se passe t-il sinon ? Modifier la TVA a 18,6%. Qu’observez-vous ? Reprendre le calcul des charges en creant un nom (tva) pour la cellule qui contient la valeur de la TVA. Pour cela placer le curseur en A2, aller dans Insertion/Nom/Definir, taper tva et valider. Remplacer alors dans les formules de TVA, l’adresse A2 par tva. VI ) Autres fonctions : SI, MOYENNE , MIN, MAX Un automobiliste desire calculer la consommation aux 100km de son vehicule. Pour cela, a chaque arret a une station service il note le kilometrage et le plein effectue. Reproduire le tableau de donnees ci-dessous sans ecrire les unites km ou L.
Ne remplir que les deux premieres colonnes. Pour rajouter ces unites, aller dans Format/Cellule/Nombre/Personnalise et dans la barre type ecrire le format que vous desirez. Dans ce TP, vous aurez a utilisez les formats suivants : # ##0’’ km’’ ; 0,00’’ L’’ ; 0,00’’ L/100km’’. Calculer automatiquement les kilometres parcourus entre deux arrets, et la consommation au 100km sur ce trajet : En C3 : =A3-A2 et en D3 : =B3/C3*100. Si la consommation est inferieure a 9L aux 100km, utiliser la fonction SI dans la rubrique logique pour ecrire automatiquement le commentaire correct, sinon le commentaire abusif. En E3 : =SI(D3
