TAI038 Expliquer les fonctions d’un tableur
TAI038 Expliquer les fonctions d’un tableur
EXCEL : TABLE DES MATIERES
1 PRESENTATION GENERALE DU LOGICIEL 3
2 LE TABLEUR 3
2.1 Configuration du tableur 3
2.2 Utiliser la souris dans la feuille 3
3 LES SAISIES 4
3.1 Saisir un texte dans une cellule 4
3.2 Saisir des nombres 4
3.3 Saisir des nombres dans des colonnes contiguës d'un tableau 4
3.4 Modifier le contenu d'une cellule 4
3.5 Effacer une cellule 4
3.6 Saisir la même donnée dans plusieurs cellules : 4
3.7 Saisie de formules de calcul 4
3.8 Recopie de formules de calcul 5
4 REPERAGE DES CELLULES 6
4.1 Mode A1 et L1C1 6
4.2 Référence absolue 6
4.3 Référence relative 6
4.4 Exemple d’utilisation des références relatives et références absolues 7
5 CREATION DE TABLEAU 8
5.1 Lignes et colonnes 8
5.2 Zones saisies, zones calculées 9
5.3 Les formules de calcul 9
6 LES FORMATS 10
6.1 Formats généraux 10
6.2 Formats personnalisés 10
6.3 Formats de lignes et de colonnes 11
7 COPIE DE CELLULES 12
7.1 Avec la souris 12
7.2 Commande recopier 12
7.3 Commande Edition Copier 12
7.4 Commande Edition Coller 13
7.5 Collage spécial (valeurs, formules, formats...) 13
7.6 Collage spécial addition ou autres opérations 13
7.7 Collage spécial transposé 13
8 LES FONCTIONS 15
8.1 Les fonctions de date et heure 15
8.2 Les fonctions de conversion 16
9 NOMMER LES CELLULES 17
9.1 Nommer une cellule 17
9.2 Nommer des plages de cellule repérées par des titres de colonnes 17
10 GROUPE DE TRAVAIL 19
10.1 Créer un groupe de travail dans un nouveau classeur : 19
10.2 Créer les tableaux en groupe de travail 19
10.3 Dissocier les feuilles 19
10.4 Utiliser les tableaux créés 19
11 CONSOLIDATION 20
12 FONCTIONS LOGIQUES : SI, ET, OU 21
12.1 La fonction SI 21
12.2 La fonction ET 22
12.3 La fonction OU 23
12.4 Les opérateurs logique 23
12.5 Les résultats 24
13 FONCTION MATHEMATIQUE : SOMME.SI 25
13.1 Sans la fonction SOMME.SI 25
13.2 Avec la fonction SOMME.SI 25
14 FONCTION DE RECHERCHE VECTORIELLE 26
15 FONCTIONS FINANCIERES 27
16 PROTECTION D’UNE FEUILLE DE CALCUL 29
17 LES BASES DE DONNEES 30
17.1 Principe 30
17.2 Création d'une base de données 31
17.3 Ajout de fiches 31
17.4 Tri d'une base de données 31
17.5 Extraction de données en fonction d'un critères 32
17.6 Création d'une zone de critère simple 32
17.7 Lancement de l'extraction 32
17.8 Extraction sur plusieurs critères combinés 33
Ce support ne correspond peut-être pas à votre version d’EXCEL, mais, il est
facilement transposable à une autre version.
1 PRESENTATION GENERALE DU LOGICIEL
Les fonctionnalités d'EXCEL
Tableur
Grapheur
Base de données
Modules Visual Basic d’Application
Le tableur : il permet la création de tableaux de toute nature, comprenant des textes, des valeurs saisies et des valeurs calculées, liaisons, recopie...
Le grapheur permet la réalisation rapide de graphiques à partir de tableaux, mise en forme, modification...
Les fonctions de base de données permettent la création de fichiers à partir desquels on peut extraire des renseignements en fonction de critères divers, réaliser des statistiques...
Le Visual Basic d’application permet l’automatisation de manipulations, de traitements...
EXCEL est de plus doté d'outils permettant de réaliser de très belles présentations.
2 LE TABLEUR
2.1 Configuration du tableur
Lorsque nous ouvrons EXCEL, nous ouvrons un classeur qui prend par défaut le nom de Classeur1.
Le classeur contient plusieurs feuilles repérées par des onglets en bas de l’écran, sur lesquelles nous allons pouvoir travailler en groupe de travail ou de façon individuelle sur chacune d’entre elle.
Une feuille comprend :
65536 lignes
256 colonnes
L’intersection d'une ligne et d'une colonne se nomme une cellule
65536 lignes * 256 colonnes = 16 777 216 cellules
Lorsque nous enregistrerons le travail réalisé, c’est le classeur complet qui sera enregistré et constituera le fichier qui prendra l’extension .XLS.
Un classeur a un nombre de feuilles par défaut défini par OUTILS/option/général
2.2 Utiliser la souris dans la feuille
pointer une cellule : positionner le pointeur de souris sur la cellule
cliquer sur une cellule pointée : sélectionne la cellule active
cliquer-glisser : pointer un élément, puis maintenir enfoncé le bouton gauche de la souris tout en déplaçant le pointeur.
sélectionner une plage de cellules : délimiter une plage de cellules, en déplaçant la souris avec une pression sur la touche gauche
3 LES SAISIES
3.1 Saisir un texte dans une cellule
pointer puis cliquer sur la cellule
taper le texte : il s'inscrit dans la barre de formule, et en même temps dans la cellule
valider en fin de saisie par la touche entrée ou en utilisant les flèches
3.2 Saisir des nombres
pointer puis cliquer sur la cellule
saisir le nombre
valider
3.3 Saisir des nombres dans des colonnes contiguës d'un tableau
Sélectionner la zone de saisie,
Saisir les nombres avec le clavier numérique et valider chaque saisie avec la touche entrée
En fin de colonne la cellule active passe automatiquement à la première cellule de la colonne suivante de la zone sélectionnée, sans avoir à se positionner sur celle-ci.
3.4 Modifier le contenu d'une cellule
cliquer sur la cellule
positionner le pointeur après le caractère à modifier, effacer avec Retour arrière et saisir
3.5 Effacer une cellule
cliquer sur la cellule
appuyer sur Suppr et valider ou EDITION/Effacer
3.6 Saisir la même donnée dans plusieurs cellules :
sélectionner la plage de cellules
saisir et valider par CTRL Entrée
3.7 Saisie de formules de calcul
Une formule est une expression commençant par le signe =, et permettant de réaliser des calculs à partir de différentes cellules.
cliquer sur la cellule dans laquelle vous voulez saisir la formule
taper =
cliquer sur la cellule contenant le premier élément de l'opération
taper le signe opératoire * / - + ou %
cliquer sur le deuxième élément du calcul
recommencer les deux dernières opérations autant de fois que vous avez d'éléments à intégrer dans le calcul
valider
3.8 Recopie de formules de calcul
sélectionner la cellule à recopier
positionner le pointeur sur la poignée de recopie
maintenir la touche gauche enfoncée tout en balayant les cellules dans lesquelles la formule doit être recopiée.
4 REPERAGE DES CELLULES
Une cellule est repérée par ls référence de la ligne et de la colonne sur laquelle elle se trouve. La façon dont ce repérage est effectué, peut être différente selon les besoins
4.1 Mode A1 et L1C1
Il existe 2 modes de repérage des cellules : mode A1 et mode L1CI. Pour passer de l'un à l'autre il suffit d'aller dans le menu : OUTILS/options/général. Cocher ou décocher Style de référence L1C1.
En mode A1, la lettre de la colonne dans laquelle se trouve la cellule est placée d'abord, ensuite vient le numéro de la ligne.
En mode L1C1, on a d'abord le numéro de colonne, puis le numéro de la ligne.
Dans la suite du document, nous allons travailler en mode L1C1.
4.2 Référence absolue
C'est l'adresse de la cellule, elle correspond à ses coordonnées.
Exemple :
L4C8 est la référence absolue de la cellule qui se trouve à l'intersection de la Ligne 4 et de la Colonne 8.
4.3 Référence relative
C'est la position d'une cellule par rapport à une autre cellule.
Exemple :
L-(2)C(-6) correspond à la cellule qui se trouve 2 lignes avant et 6 colonnes avant la cellule de base.
Si la cellule de base est L9C10, la cellule ci-dessus aura pour adresse :
Ligne 9 - 2 Lignes = Ligne 7
Colonnes 10 - 6 colonnes = Colonne 4
soit la référence absolue L7C4
Si la cellule de base change et devient par exemple L9C11, L(-2)C(-6) par rapport à L9C11 est L9C5
On utilise les références relatives pour effectuer des calculs en ligne ou en colonnes qui sont basés sur le même nombre d'éléments. Il suffit d'entrer la formule dans la première cellule de la ligne ou de la colonne et puis de la recopier dans les cellules suivantes.
Par contre, si l'un des éléments du calcul est situé dans une cellule fixe et est utilisé pour tous les calculs, cette cellule devra être identifiée sous forme absolue.
Pour transformer une référence relative en référence absolue, il suffit, une fois que la formule a été générée, de se positionner dans la barre de formule, sélectionner la référence à transformer, et appuyer sur la touche F4. Faire la même manœuvre pour passer de l'absolu au relatif.
A noter : en mode A1, les références absolues sont notées $A$1, A1 constituant une référence relative.
4.4 Exemple d’utilisation des références relatives et références absolues
1 2 3
1 Note 1 Note2 Total
2 12 15 =LC(-1)+LC(-2)
3 15 13 =LC(-1)+LC(-2)
4 14 17 =LC(-1)+LC(-2)
5 16 12 =LC(-1)+LC(-2)
6 8 15 =LC(-1)+LC(-2)
7 19 14 =LC(-1)+LC(-2)
8 12 15 =LC(-1)+LC(-2)
9 11 18 =LC(-1)+LC(-2)
10 10 11 =LC(-1)+LC(-2)
11 7 15 =LC(-1)+LC(-2)
12 Coefficient Note sur 20 Note finale
13 4 12 =LC(-1)*LC(-2)
14 15 =LC(-1)*L13C1
15 14 =LC(-1)*L13C1
16 16 =LC(-1)*L13C1
17 8 =LC(-1)*L13C1
18 19 =LC(-1)*L13C1
19 12 =LC(-1)*L13C1
20 11 =LC(-1)*L13C1
21 10 =LC(-1)*L13C1
5 CREATION DE TABLEAU
La création d’un tableau nécessite une préparation préalable en ce qui concerne son organisation sur les points suivants :
Le nombre de lignes et de colonnes nécessaires
Les textes : titre général du tableau, en-têtes de lignes et de colonnes à saisir une fois pour toutes
Les valeurs numériques monétaires ou autres, saisies, et qui doivent pouvoir être modifiées selon les besoins
Les calculs qui sont automatisés et basées sur les valeurs numériques saisies
Les liens avec d’autres tableaux…
Exemple : nous voulons réaliser un tableau sur 3 mois et en totalité sur le trimestre, présentant les éléments suivants : Chiffre d’affaires, Achats de marchandises, Marge commerciale
5.1 Lignes et colonnes
5 colonnes et 5 lignes sont nécessaires pour réaliser ce tableau :
Colonne
en-têtes lignes mois 1 mois 2 mois 3 trimestre
1 2 3 4 5
1 EVOLUTION SUR LE TRIMESTRE
2 MOIS 1 MOIS 2 MOIS 3 TOTAL
3 Chiffre d'affaires
4 Achats de marchandises
5 Marge commerciale
Nous aurons :
Une colonne pour les titres de ligne
Une colonne pour chacun des mois
Une colonne pour le total en ligne
Une ligne pour le titre
Une ligne pour les titres de colonne
Une ligne par information
Pour aérer le tableau, il n'est pas nécessaire de laisser des lignes blanches : il suffit simplement d'augmenter leur hauteur et de centrer le texte verticalement par rapport à celle-ci.
5.2 Zones saisies, zones calculées
Une cellule peut contenir du texte, des chiffres ou des formules de calculs.
Les zones saisies en texte sont fixes. Les zones contenant des valeurs saisies sont dites variables. Les zones contenant des formules varient lorsque les valeurs de base des calculs varient.
Textes saisis à la création du tableau
1 2 3 4 5
1 EVOLUTION SUR LE TRIMESTRE
2 MOIS 1 MOIS 2 MOIS 3 TOTAL
3 Chiffre d'affaires
4 Achats de marchandises
5 Marge commerciale
Valeurs numériques Formules de calcul
saisies
5.3 Les formules de calcul
Elles concernent :
pour chaque mois en L5C2, L5C3, L5C4 : la marge commerciale : chiffre d'affaires du mois - achats du mois. Il suffit d’entrer la formule en L5C2 la formule suivante
==L(-2)C-L(-1)C
puis la recopier vers la droite sur les 2 cellules adjacentes (références relatives).
pour le trimestre :
le total des chiffres d'affaires mensuels en L3C5 : =SOMME(LC(-3):LC(-1))
le total des achats mensuels en L4C5 : même formule que la précédente
le total des marges mensuelles en L5C5 : même formule que la précédente
La formule entrée en L3C5 peut-être recopiée sur les 2 cellules au-dessous.
A noter : la fonction SOMME peut être générée à l’aide du bouton SOMME AUTOMATIQUE se trouvant dans la barre d’outils , et en balayant la somme à totaliser.
6 LES FORMATS
Les cellules peuvent prendre des formats particuliers. Pour les affecter, il suffit de sélectionner les cellules concernées, puis de cliquer sur le menu FORMAT/Cellules
6.1 Formats généraux
Les différents onglets proposés dans cette commande permettent d’agir sur les formats suivants :
Nombres : ils sont classés par catégorie
Alignement : cela concerne l’alignement horizontal, vertical et le contrôle du texte
Police
Bordure
Motif
Protection : afin de paramétrer la protection de certaines cellules.
6.2 Formats personnalisés
Créer un format personnalisé permet d'afficher à coté d'un nombre une unité quelconque.
Exemple : 300 tonnes.
Pour cela, à partir de la cellule contenant le nombre, cliquer sur FORMAT/Cellules,
Cliquer sur personnalisé
Saisir dans la zone de saisie taper #" tonnes"
6.3
Formats de lignes et de colonnes
Lignes et colonnes peuvent être modifiés en largeur, hauteur, encadrement…
Modification de la largeur et de la hauteur
Pour modifier uniformément toutes les lignes et colonnes de la feuille:
sélectionner le carré grisé en haut à gauche de la feuille : toute la feuille est alors sélectionnée
faire un cliquer/glisser sur le trait séparateur entre 2 colonnes ou lignes pour élargir ou rétrécir.
Pour modifier une seule ligne ou colonne, faire la même chose sans sélectionner la totalité de la feuille.
7 COPIE DE CELLULES
La simple recopie de cellule peut être effectuée à l’aide de la souris ou bien des commandes du menu EDITION Recopier.
7.1 Avec la souris
Sélectionner la ou les cellules à Recopier. Positionner le pointeur sur la poignée de recopie, Etirer vers le bas ou la droite selon le cas.
7.2 Commande recopier
Permet de recopier le contenu d'une cellule vers d'autres cellules, vers le bas, le haut, à gauche ou à droite en fonction de l'orientation des cellules sélectionnées. La première cellule sélectionnée doit toujours être la cellule contenant les données à reproduire. Cliquer sur :
EDITION/Recopier/ Vers le bas, (le haut, à droite ou à gauche)
Permet de générer des séries, c'est-à-dire des listes de valeurs séparées par un écart, ou pas. Il suffit de saisir les deux premières valeurs, et de sélectionner les cellules suivantes en ligne ou en colonne. Cliquer alors sur :
EDITION/Recopier/Série
Les séries peuvent être :
en progression arithmétique (linéaire) : le pas est alors ajouté à chaque valeur pour donner la valeur suivante. Cela équivaut à la recopie incrémentée que l'on pratique avec la souris après avoir sélectionné les deux premières valeurs et les cellules sur lesquelles on veut recopier.
en progression géométrique : le pas est alors multiplié par chaque valeur pour donner la valeur suivante.
en progression chronologique journalière, mensuelle, annuelle... Le pas est alors ajouté à chaque valeur pour obtenir la suivante
Série linéaire (pas de 9) Série géométrique (pas de 9) Série chronologique (pas = 1 jour) Série chronologique (pas = 1 mois) Série chronologique (pas = 1 an)
1 1 11/11/2002 11/01/2002 11/01/1995
10 9 12/11/2002 11/02/2002 11/01/1996
19 81 13/11/2002 11/03/2002 11/01/1997
28 729 14/11/2002 11/04/2002 11/01/1998
37 6561 15/11/2002 11/05/2002 11/01/1999
46 59049 16/11/2002 11/06/2002 11/01/2000
55 531441 17/11/2002 11/07/2002 11/01/2001
64 4782969 18/11/2002 11/08/2002 11/01/2002
73 43046721 19/11/2002 11/09/2002 11/01/2003
7.3 Commande Edition Copier
Permet de stocker les données sélectionnées dans le presse papier.
7.4 Commande Edition Coller
Colle les données sélectionnées dans leur format d'origine. Equivaut à la commande Collage spécial/Tout
Si avant de copier une feuille de calcul, on sélectionne la feuille entière 5S2LEC le format des lignes et colonnes est aussi pris en compte.
7.5 Collage spécial (valeurs, formules, formats...)
Permet de choisir ce que l'on veut coller.
7.6 Collage spécial addition ou autres opérations
Permet d'additionner (multiplier, diviser...) la cellule copiée avec les cellules sélectionnées.
Prévisions par région, 1996, 1997 (montants en miliers de FF)
1998 Trim 1 Trim 2 Trim 3 Trim 4 Total
Amér. du Nord 200 240 235 200 875
Amér. du Sud 205 175 175 190 745
Extrême-Orient 235 245 260 265 1 005
Somme Ventes 640 660 670 655 2 625
1999 Trim 1 Trim 2 Trim 3 Trim 4 Total
Amér. du Nord 250 300 293.75 250 1 094 1.25
Amér. du Sud 256.25 218.75 218.75 237.5 931
Extrême-Orient 293.75 306.25 325 331.25 1 256
Somme Ventes 800 825 838 819 3 281
7.7 Collage spécial transposé
Permet de changer l'orientation des cellules copiées : les lignes deviennent des colonnes et les colonnes des lignes.
Pelles Pioches Râteaux Total
janvier 125 145 215 485
février 215 216 236 667
mars 325 216 245 786
Total 665 577 696 1938
janvier février mars
Pelles 125 215 325
Pioches 145 216 216
Râteaux 215 236 245
8 LES FONCTIONS
Les fonctions sont des formules prédéfinies qui effectuent des calculs en utilisant des valeurs particulières appelées arguments, dans un certain ordre (ou structure). Par exemple, la fonction SOMME additionne des valeurs ou des plages de cellules tandis que la fonction VPM calcule les remboursements de prêt en fonction du taux d'intérêt, de la durée du prêt et de son montant principal.
Arguments Les arguments peuvent être des nombres, du texte et des valeurs logiques telles que VRAI ou FAUX, des matrices, des valeurs d'erreur telles que #N/A ou des références de cellules. L'argument choisi doit produire une valeur valide. Les arguments peuvent aussi être des constantes, des formules ou d'autres fonctions
Structure La structure d'une fonction débute par le nom de la fonction suivi d'une parenthèse ouvrante puis des arguments de la fonction séparés par des virgules et se termine par une parenthèse fermante. Si la fonction débute une formule, tapez un signe égal (=) avant le nom de la fonction. Dès que vous créez une formule contenant une fonction, la Palette de formules vous aide
Signe égale (si la fonction est en début de formule)
Nom de la fonction
Arguments délimités par des parenthèses
=SOMME(L1C1:L10C1)
Les : signifient qu’il s’agit d’une plage de cellules
8.1 Les fonctions de date et heure
Une fonction peut être utilisée seule, imbriquée avec d’autres fonctions, devenir argument d’une fonction…
Exemple :
=DATE(ANNEE(A10);MOIS(B12);JOUR(B18))
La fonction DATE nécessite 3 arguments séparés par des ;
L’année que l’on peut saisir dans la fonction ou récupérer dans une autre date
Le mois que l’on peut saisir dans la fonction ou récupérer dans une autre date
Le jour que l’on peut saisir dans la fonction ou récupérer dans une autre date
La fonction ANNEE extrait l’année correspondant à une date saisie dans une cellule (ici A10)
La fonction MOIS extrait le mois correspondant à une date saisie dans une cellule (ici B12)
La fonction JOUR extrait le jour correspondant à une date saisie dans une cellule (ici B18)
Il existe différents types de fonctions que l’on peut consulter par le menu Insertion :Fonction ou par un clic sur l’icône .
8.2 Les fonctions de conversion
Elles permettent de passer de la numération en base 10, à la numération en binaire, en hexadécimal… et inversement, ce qui est très utile en micro informatique.
Ces fonctions de conversion peuvent être utilisées à condition d'avoir auparavant, chargé les macros complémentaires : utilitaires d'analyse et Utilitaire d'analyse VBA (Outils macros complémentaires).
=BINDEC(D3) : convertit le contenu en binaire de la cellule D3 en une valeur décimale
=DECBIN(D4;8) convertit le contenu en décimal de la cellule D4 en une valeur binaire sur 8 positions
=DECHEX(D6) : convertit le contenu en décimal de la cellule D6 en une valeur hexadécimale
=HEXDEC(D7) : convertit le contenu en hexadécimal de la cellule D7 en une valeur décimale
=BINHEX(D9) : convertit le contenu en binaire de la cellule D9 en une valeur hexadécimale
=HEXBIN(D10;8) : convertit le contenu en hexadécimal de la cellule D10 en une valeur binaire sur 8 positions
9 NOMMER LES CELLULES
Cela consiste à repérer des cellules ou plages de cellules par un Nom qui pourra être utilisé dans les formules à la place des références
9.1 Nommer une cellule
Sélectionner la cellule à nommer.
Cliquer sur INSERTION/Nom/définir et taper le nom à donner à la cellule ou conserver celui qui est proposé
Valider
Ce nom peut être maintenant utilisé dans des formules pour faire référence à la cellule nommée.
18,60% Cellule nommée Taux
Montant TVA
15000 2790,00 =Montant*Taux
12000 2232,00 =Montant*Taux
15800 2938,80 =Montant*Taux
Plage nommée Montant
9.2 Nommer des plages de cellule repérées par des titres de colonnes
Il est possible d’utiliser des titres de colonnes pour nommer les plages qui se trouvent immédiatement au-dessous.
Exemple :
1 2 3 4 5
1
2 Mois\Produits Pelles Pioches Râteau Total
3 Janvier 4545 7878 999 13422
4 février 4545 454 777 5776
5 mars 545 545 545 1635
6 Total =SOMME(pelles) =SOMME(Pioches) =SOMME(Râteau) 20833
Ces plages de cellules ont été nommées après avoir sélectionné les cellules L3C2 à L5C4 en cliquant sur INSERTION/Nom/créer, et en cliquant sur Ligne du haut
On pourra aussi nommer des plages en ligne à l’aide des titres de lignes.
10 GROUPE DE TRAVAIL
Un groupe de travail est constitué de plusieurs feuilles sur lesquelles on va pouvoir effectuer des saisies et mises en forme en même temps.
Cela sera utilisé pour créer des tableaux identiques : hebdomadaires, mensuels…
La structure de l’ensemble des tableaux sera alors créée en une seule fois. Les saisies sur chacun d’entre eux seront ensuite effectuées indépendamment.
10.1 Créer un groupe de travail dans un nouveau classeur :
Sélectionner autant de feuilles que de tableaux à créer. Pour cela :
Cliquer sur la première feuille
Maintenir appuyée la touche SHIFT
Cliquer sur la dernière feuille. Les feuilles sont lors sélectionnées, leur nom est en surbrillance. En haut dans la barre de titres apparaît Groupe de travail.
10.2 Créer les tableaux en groupe de travail
A partir de là, créer la structure des tableaux en incluant tous les éléments standardS :
Titres généraux, de lignes, de colonnes
Mises en forme de cellules, nombres…
Formules de calcul
Encadrements de cellules…
10.3 Dissocier les feuilles
Une fois la structure créée, dissocier les feuilles en effectuant un clic droit sur le nom d’une des feuilles et en cliquant sur dissocier.
Chaque feuille devient alors indépendante.
10.4 Utiliser les tableaux créés
Nommer chaque feuille en double cliquant sur le nom de la feuille et en saisissant un nom explicite.
Saisir les données de chacun des tableaux
11 CONSOLIDATION
On peut consolider par addition (ou autre opération) des tableaux représentant des données identiques sur des périodes successives.
Exemples :
On consolidera des tableaux mensuels sur l’année. On aura donc un tableau par mois et un tableau récapitulatif annuel qui cumule les données mensuelles.
On consolidera des tableaux par succursale en un seul tableau qui représentera l’ensemble des chiffres de l’entreprise.
Pour cela :
Il faut d’abord que les données aient été saisies dans les différents tableaux situés sur des feuilles différentes d’un même classeur.
Dans une feuille récapitulative, on lancera alors la consolidation à partir de la première cellule de la feuille, par Données/ Consolider.
La consolidation des 3 feuilles ci-dessus se fera suivant ce schéma :
Les références des données à consolider de chaque feuille ont été sélectionnées et ajoutées les unes après les autres.
On a ensuite coché Ligne du haut et Colonne de Gauche dans la rubrique Etiquettes, pour indiquer que la première ligne et la première colonne de la zone sélectionnée contiennent du texte et non pas des chiffres à ajouter.
On a coché aussi Lier aux données source de façon à créer un lien entre les 3 tableaux de base et le récapitulatif. Celui-ci se mettra ainsi à jour si les données source sont modifiées.
12 FONCTIONS LOGIQUES : SI, ET, OU
12.1 La fonction SI
La fonction SI permet de soumettre le résultat à obtenir à partir d'une formule de calcul à une condition.
Si la condition est remplie, tel résultat s’affichera, si elle n’est pas remplie, ce sera tel autre résultat.
Syntaxe de la fonction :
=SI(Condition;alors;sinon)
ou =SI(test logique;valeur si vrai;valeur si faux)
Exemple :
Nous voulons obtenir le tableau suivant :
Si l'élève a plus de 10 de moyenne, doit figurer le mot Encouragement dans la colonne appréciation, sinon, ce sera Des efforts à faire.
Moyenne des élèves
Elèves Moyenne Appréciation
Paul 12 Encouragement
Pierre 10 Des efforts à faire
Jacques 9 Des efforts à faire
Julie 15 Encouragement
(Les cellules contenant les moyennes ont été nommées Moyenne)
La formule à positionner dans la première cellule de la colonne 3 sera la suivante :
Moyenne des élèves
Elèves Moyenne Appréciation
Paul 12 =SI(Moyenne>10;"Encouragement";"Des efforts à faire")
Pierre 10
Jacques 9
Julie 15
Elle sera ensuite recopiée vers le bas.
=SI (Moyenne>10; "Encouragement"; "Des efforts à faire")
=SI (Condition; Alors; Sinon)
12.2 La fonction ET
Combinée avec la fonction SI, elle permet de soumettre le résultat à obtenir à partir d'une formule de calcul, à plusieurs conditions simultanées.
Syntaxe de la fonction :
=SI(ET(Condition1;Condition2;...);alors;sinon)
ou =SI(ET(test logique1;test logique2;...);valeur si vrai;valeur si faux)
Exemple :
Nous voulons obtenir le tableau suivant :
Si l'élève a une moyenne 1 supérieure à 10 et une moyenne 2 supérieure ou égale à 10, le mot Encouragement doit figurer dans la colonne appréciation. Si les deux conditions ne sont pas remplies simultanément, le message sera Des efforts à faire.
Moyenne des élèves
Elèves Moyenne1 Moyenne2 Appréciation
Paul 12 8 Des efforts à faire
Pierre 10 12 Des efforts à faire
Jacques 9 11 Des efforts à faire
Julie 15 16 Encouragement
Formule :
=SI(ET(Moyenne1>10;Moyenne2>=10);"Encouragement";"Des efforts à faire")
Moyenne des élèves
Elèves Moyenne1 Moyenne2 Appréciation
Paul 12 8
Pierre 10 12
Jacques 9 11
Julie 15 16
=SI( ET( Moyenne1>10; Moyenne2>=10); "Encouragement"; "Des efforts à faire")
=SI( ET( Condition1; Condition2); Alors; Sinon)
12.3 La fonction OU
Combinée avec la fonction SI, elle permet de soumettre le résultat à obtenir à partir d'une formule de calcul, à plusieurs conditions alternatives.
Syntaxe de la fonction :
=SI(OU(Condition1;Condition2;...);alors;sinon)
ou =SI(OU(test logique1;test logique2;...);valeur si vrai;valeur si faux)
Exemple :
Nous voulons obtenir le résultat suivant :
Si l'élève a une moyenne 1 supérieure à 10 ou une moyenne 2 supérieure ou égale à 10, le mot Encouragement doit figurer dans la colonne appréciation. Si l'une au moins des deux conditions n'est pas remplie, le message sera Des efforts à faire.
Moyenne des élèves
Elèves Moyenne1 Moyenne2 Appréciation
Paul 12 8 Encouragement
Pierre 10 12 Encouragement
Jacques 9 11 Encouragement
Julie 15 16 Encouragement
Formule :
=SI(OU(Moyenne1>10;Moyenne2>=10);"Encouragement";"Des efforts à faire")
Moyenne des élèves
Elèves Moyenne1 Moyenne2 Appréciation
Paul 12 8
Pierre 10 12
Jacques 9 11
Julie 15 16
=SI( OU( Moyenne1>10; Moyenne2>=10); "Encouragement"; "Des efforts à faire")
=SI( OU( Condition1; Condition2); Alors; Sinon)
12.4 Les opérateurs logique
Ils peuvent être :
=,>, <, >=,<=,
Le test peut porter sur des valeurs numériques intégrées ou non dans une formule de calcul, ou des valeurs alphabétique. Dans le cas où il porte sur des valeurs alpha, celles-ci doivent figurer entre guillemets ("aaaaa"), dans la formule.
Exemple : =SI(L2C6="Bien";...............;................)
12.5 Les résultats
Ils peuvent être :
des valeurs numériques
Exemple : SI(L2C6>10;100%;50%)
des valeurs alpha (doivent alors figurer entre guillemets)
Exemple : SI(L2C10="Bien";"RAS";"Voir les parents")
des formules de calcul comportant des opérateurs (+, -, *, /, %)
Exemple : SI(L2C6>1000000;L2C6*10%;L2C6*5%)
des formules comportant des fonctions (SOMME, MOYENNE, MAX, MIN, DATE, MOIS.....)
des tests avec utilisation de la fonction SI
13 FONCTION MATHEMATIQUE : SOMME.SI
Cette fonction permet d’effectuer la somme de cellules en fonction d’un critère se trouvant dans des cellules adjacentes.
Exemple :
Nous souhaitons ici effectuer la somme des règlements de la colonne Montant de ce tableau en fonction du mode de paiement.
Si nous n’utilisons pas la fonction SOMME.SI, il est nécessaire de créer 2 colonnes pour extraire le règlement en fonction du mode.
13.1 Sans la fonction SOMME.SI
Règlement Montant Espèces Chèques
Esp 10 000 F 10 000 F 0 F
Chèq 15 000 F 0 F 15 000 F
Chèq 12 500 F 0 F 12 500 F
Esp 14 569 F 14 569 F 0 F
Esp 2 498 F 2 498 F 0 F
Total des règlements 54 567 F 27 067 F 27 500 F
Formule Colonne Espèces
=SI(Règlement="Esp";LC(-1);0)
Formule Colonne Chèques
=SI(Règlement="Chèq";LC(-2);0)
Formule Cellules Total des règlements
=SOMME(Espèces)
=SOMME(Chèques)
13.2 Avec la fonction SOMME.SI
Nous n’avons plus besoin des 2 colonnes annexes. 2 cellules suffisent alors pour effectuer les calculs.
Règlement Montant
Esp 10 000 E
Chèq 15 000 E
Chèq 12 500 E
Esp 14 569 E
Esp 2 498 E
Total des règlements 54 567 E Dont 27 500 E par chèque
27 067 E en espèces
Vérification 54 567 E
Calcul des règlements par chèque :
=SOMME.SI(Règlement;"Chèq";Montant)
Calcul des règlements en espèces :
=SOMME.SI(Règlement;"Esp";Montant)
14 FONCTION DE RECHERCHE VECTORIELLE
Cette fonction permet de rechercher une valeur dans une table à partir d’une valeur saisie et la faire apparaître dans une cellule.
Exemple :
On dispose de la table des salariés de l’entreprise. Chaque colonne est repérée par un nom qui correspond au titre de la colonne concernée. On dispose d’une fiche individuelle salarié et on souhaite qu’à partir de la saisie du code du salarié, les références de celui-ci apparaissent.
Feuille Salariés
1 2 3
1 Codsal Nomsal Prenomsal
2 10 PAULOT Jean
3 11 DURAND Pierre
4 12 MARTIN Paul
5 13 MICHEL Jacques
6 14 JULIEN Jules
7 15 JENTOT Louis
FICHE INDIVIDUELLE SALARIE : Feuille Fichesal
1 2
1 Code
2 Nom
3 Prénom
4
Les cellules contenant le nom et le prénom du salarié seront automatiquement renseignées, lors de la saisie du code dans la cellule nommée Code.
Pour cela il faut positionner une formule contenant la fonction RECHERCHE( ), dans ces deux cellules :
Syntaxe de la fonction Recherche Vectorielle:
=RECHERCHE(valeur_cherchée;vecteur_recherche;vecteur_résultat)
Arguments :
valeur_cherchée valeur saisie, c'est-à-dire le code du salarié
vecteur_recherche colonne contenant les codes des salariés dans la feuille Salariés
vecteur_résultat colonne contenant le résultat à obtenir dans la feuille Salariés : colonne 2 pour le nom colonne 3 pour le prénom
Formule à positionner dans la cellule dans laquelle on désire faire apparaître le nom du salarié L2C2:
= RECHERCHE(Code;Salariés!Codsal;Salariés!Nomsal)
Formule à positionner dans la cellule dans laquelle on désire faire apparaître le prénom du salarié L3C2 :
= RECHERCHE(Code;Salariés!Codsal;Salariés!Prénomsal)
15 FONCTIONS FINANCIERES
Ce sont des fonctions qui permettent d’effectuer des calculs financiers de type remboursement d’emprunt, des calculs d’amortissements…
Exemples de fonctions financières :
VPM
PRINCPER
INTPER
AMORLIN
Calcule le remboursement d'un emprunt sur la base de remboursements et d'un taux d'intérêt constants.
Syntaxe de la fonction VPM
VPM(taux;npm;va;vc;type)
taux : représente le taux d'intérêt de l'emprunt (attention le taux est annuel, s’il y a plusieurs remboursements annuels, il faut diviser le taux par le nombre de remboursements)
npm : représente le nombre total de remboursements pour l'emprunt (Nombre d’années multipliée par le nombre de remboursements par an)
va : représente la valeur actuelle ou la valeur que représente à la date d'aujourd'hui une série de remboursements futurs ; il s'agit du principal de l'emprunt.
vc : représente la valeur future (valeur capitalisée), c'est-à-dire le montant que vous souhaitez obtenir après le dernier paiement. Si vc est omis, la valeur par défaut est 0 (zéro), c'est-à-dire que la valeur future d'un emprunt est égale à 0. (Argument facultatif)
type : représente le nombre 0 (zéro) ou 1 et indique quand les paiements doivent être effectués. . (Argument facultatif)
Exemple de tableau de remboursement d’emprunt
Les éléments de l’emprunt sont les suivants :
Montant 30 000 F
Taux 11%
Durée (en années) 4
Nombre remboursements par an 3
Remboursement 3 135,05 F
Les cellules contenant les valeurs de base ont été nommées :
Montant
Taux
Durée
Nb
Le remboursement annuel sera donc égal à :
=VPM ( Taux / Nb ; Nb * Durée ; -Montant )
Le tableau de remboursement constant sera le suivant :
Echéancier de remboursement
Période Capital Intérêt Remboursement Reste dû
1 2 035,05 E 1 100,00 E 3 135,05 E 27 964,95 E
2 2 109,67 E 1 025,38 E 3 135,05 E 25 855,29 E
3 2 187,02 E 948,03 E 3 135,05 E 23 668,27 E
4 2 267,21 E 867,84 E 3 135,05 E 21 401,06 E
5 2 350,34 E 784,71 E 3 135,05 E 19 050,72 E
6 2 436,52 E 698,53 E 3 135,05 E 16 614,19 E
7 2 525,86 E 609,19 E 3 135,05 E 14 088,34 E
8 2 618,47 E 516,57 E 3 135,05 E 11 469,86 E
9 2 714,49 E 420,56 E 3 135,05 E 8 755,37 E
10 2 814,02 E 321,03 E 3 135,05 E 5 941,36 E
11 2 917,20 E 217,85 E 3 135,05 E 3 024,16 E
12 3 024,16 E 110,89 E 3 135,05 E
Formule de calcul du capital remboursé à chaque période :
= PRINCPER(Taux/Nb;Période;Nb*Durée;-Montant)
Formule de calcul de l’intérêt correspondant à chaque paiement :
= INTPER(Taux/Nb;Période;Nb*Durée;-Montant)
16 PROTECTION D’UNE FEUILLE DE CALCUL
Ouvrir le classeur et la feuille à protéger.
Déverrouiller les cellules devant recevoir une saisie
Sélectionner de la cellule et la déprotéger par
FORMAT/Cellule/protection
Décocher Verrouillé
Dans le menu Outils, cliquer sur Protection, puis sur Protéger la feuille.
Pour empêcher la modification des cellules de feuille de calcul ou des éléments de graphique, activer la case à cocher Contenu.
Pour empêcher la modification des objets graphiques, activez la case à cocher Objets.
Pour empêcher d'autres utilisateurs de supprimer la protection de la feuille, tapez un mot de passe (à déconseiller) et cliquez sur OK, puis tapez à nouveau ce mot de passe dans la boîte de dialogue Confirmer le mot de passe.
Remarque
Pour supprimer la protection, cliquez sur Protection (menu Outils), puis sur Oter la protection de la feuille.
Si vous avez défini un mot de passe en protégeant la feuille, vous devez le taper pour supprimer la protection.
17 LES BASES DE DONNEES
17.1 Principe
Une base de données est un fichier qui permet d'organiser, de gérer, de retrouver et d'extraire des informations. Une base de données est créée sur une feuille de calcul, et doit être structurée d'une certaine façon.
Vocabulaire :
Plage de base de données : Plage de cellules rectangulaire, définie en tant que base de données. La première ligne de la plage contient les noms de champs. Les lignes suivantes contiennent les fiches ou enregistrements;
Fiche : une fiche correspond à une ligne de la base de données. Toutes le fiches sont structurées de la même façon, selon l'ordre des champs défini sur la première ligne.
Champ : Chaque colonne de la plage de base de données correspond à un champ distinct. Chaque cellule d'une colonne constitue un champ.
Nom de champ : Nom permettant d'identifier les données stockées dans un champ. Ils sont définis sur la première ligne de la base de données. A chaque nom de champ correspond un renseignement.
Champ calculé : Un champ calculé contient des formules de calcul.
Grille : Ecran de saisie d’une fiche
Exemple de base de données :
1 2 3 4 5 6 7
1 Facture Date Numsoc Société Montant TVA Total
2 11560 22/10/1999 101 Bénat SA 15 600,00 2 901,60 18 501,60
3 11561 20/11/1999 85 Répar 3 860,00 717,96 4 577,96
4 11562 22/11/1999 101 Bénat SA 12 500,00 2 325,00 14 825,00
5 11563 24/11/1999 88 Blatec SA 46 980,00 8 738,28 55 718,28
6 11564 25/11/1999 54 Vélo service 672,00 124,99 796,99
7 11565 25/11/1999 12 Céramique SA 1470,00 273,42 1743,42
8 11566 26/11/1999 85 Répar 540,00 100,44 640,44
9 11567 26/11/1999 54 Vélo service 18504,00 3441,74 21945,74
La première ligne contient les noms de champ.
Les lignes suivantes contiennent les fiches
La plage de base de données est constituée par les cellules L1C1 à L9C7.
Les champs Facture, Date, Numsoc, Société et Montant sont des champs saisis.
Les champs TVA et Total sont des champs calculés.
17.2 Création d'une base de données
Sur la première ligne, saisir les noms de champs.
Sur la deuxième ligne saisir la première fiche en respectant l'ordre des champs.
Les données saisies sont maintenant considérées comme une base de données par EXCEL.
17.3 Ajout de fiches
Cliquer sur DONNEES/Grille ou Formulaire. La grille suivante apparaît :
Cliquer sur Nouvelle pour ajouter une fiche.
Saisir le premier renseignement dans le premier champ.
Tabuler pour passer au champ suivant.
Recommencer ces opérations autant de fois que nécessaire.
Valider après la saisie du dernier champ. La validation permet d'enregistrer la fiche et de passer à la saisie de la fiche suivante.
L'utilisation de la grille a plusieurs avantages :
Les champs calculés sont recopiés automatiquement (Noter que dans la grille, il n'y a pas de possibilité d'accés à ces champs)
La plage de base de données est renommée lors de la saisie de chacune des fiches.
17.4 Tri d'une base de données
Le menu DONNEES/Trier permet de réorganiser les données d'une base dans l'ordre alphabétique ou numérique en fonction d'une ou plusieurs clés de tri. Une clé correspond à un champ.
Sélectionner la base de données sans prendre en compte les noms de champs.
Cliquer sur DONNEES/Trier
Dans la zone première clé, entrer la référence de la première cellule du champ sur lequel vous désirez trier.
Dans les zones correspondant aux deuxième et troisième clés, entrer la référence de la première cellule des champs sur lesquels vous désirez effectuer un deuxième classement.
Valider.
17.5 Extraction de données en fonction d'un critères
EXCEL permet d'extraire des listes de données correspondant à un ou plusieurs critères simples, combinés ou calculés. Il faut pour cela créer une zone de critère et une zone d'extraction.
17.6 Création d'une zone de critère simple
Positionner le pointeur de cellules à droite de la base de données.
Saisir le nom du champ sur lequel doit porter le critère
Saisir le critère lui-même dans la cellule au-dessous, sans espace.
17.7 Lancement de l'extraction
Cliquer sur DONNEES/ Filtre/ Filtre élaboré
Dans les zones prévues à cet effet dans la boite de dialogue :
Entrer la plage de base de données en la sélectionnant ou en tapant son nom si elle a été nommée,
Entrer ensuite les références de la zone de critère et la première cellule de la zone de destination.
Cocher Copier vers un autre emplacement de façon à ce que la base ne soit pas filtrée sur place.
17.8
Extraction sur plusieurs critères combinés
Il est possible de lancer une extraction à partir de critères combinés ET ou alternatifs OU.
Créer une zone de critères comprenant sur sa première ligne tous les noms de champs de la base.
Renseigner les champs correspondant aux critères que vous voulez retenir : sur la deuxième ligne si les critères doivent être simultanés, en décalage à partir de la deuxième ligne et sur les lignes suivantes s'ils doivent être alternatifs.
Zone de critères combinés (ET)
Facture Date Numsoc Société Montant TVA Total
>24/11/1999 >1000
On extraira les factures dont la date est postérieure au 24/11/99 et le total supérieur à 1000.
Zone de critères combinés OU
Facture Date Numsoc Société Montant TVA Total
101
85
On extraira les factures correspondant au numéro de société 101 ou au numéro 85
EXCEL : TABLE DES MATIERES
1 PRESENTATION GENERALE DU LOGICIEL 3
2 LE TABLEUR 3
2.1 Configuration du tableur 3
2.2 Utiliser la souris dans la feuille 3
3 LES SAISIES 4
3.1 Saisir un texte dans une cellule 4
3.2 Saisir des nombres 4
3.3 Saisir des nombres dans des colonnes contiguës d'un tableau 4
3.4 Modifier le contenu d'une cellule 4
3.5 Effacer une cellule 4
3.6 Saisir la même donnée dans plusieurs cellules : 4
3.7 Saisie de formules de calcul 4
3.8 Recopie de formules de calcul 5
4 REPERAGE DES CELLULES 6
4.1 Mode A1 et L1C1 6
4.2 Référence absolue 6
4.3 Référence relative 6
4.4 Exemple d’utilisation des références relatives et références absolues 7
5 CREATION DE TABLEAU 8
5.1 Lignes et colonnes 8
5.2 Zones saisies, zones calculées 9
5.3 Les formules de calcul 9
6 LES FORMATS 10
6.1 Formats généraux 10
6.2 Formats personnalisés 10
6.3 Formats de lignes et de colonnes 11
7 COPIE DE CELLULES 12
7.1 Avec la souris 12
7.2 Commande recopier 12
7.3 Commande Edition Copier 12
7.4 Commande Edition Coller 13
7.5 Collage spécial (valeurs, formules, formats...) 13
7.6 Collage spécial addition ou autres opérations 13
7.7 Collage spécial transposé 13
8 LES FONCTIONS 15
8.1 Les fonctions de date et heure 15
8.2 Les fonctions de conversion 16
9 NOMMER LES CELLULES 17
9.1 Nommer une cellule 17
9.2 Nommer des plages de cellule repérées par des titres de colonnes 17
10 GROUPE DE TRAVAIL 19
10.1 Créer un groupe de travail dans un nouveau classeur : 19
10.2 Créer les tableaux en groupe de travail 19
10.3 Dissocier les feuilles 19
10.4 Utiliser les tableaux créés 19
11 CONSOLIDATION 20
12 FONCTIONS LOGIQUES : SI, ET, OU 21
12.1 La fonction SI 21
12.2 La fonction ET 22
12.3 La fonction OU 23
12.4 Les opérateurs logique 23
12.5 Les résultats 24
13 FONCTION MATHEMATIQUE : SOMME.SI 25
13.1 Sans la fonction SOMME.SI 25
13.2 Avec la fonction SOMME.SI 25
14 FONCTION DE RECHERCHE VECTORIELLE 26
15 FONCTIONS FINANCIERES 27
16 PROTECTION D’UNE FEUILLE DE CALCUL 29
17 LES BASES DE DONNEES 30
17.1 Principe 30
17.2 Création d'une base de données 31
17.3 Ajout de fiches 31
17.4 Tri d'une base de données 31
17.5 Extraction de données en fonction d'un critères 32
17.6 Création d'une zone de critère simple 32
17.7 Lancement de l'extraction 32
17.8 Extraction sur plusieurs critères combinés 33
Ce support ne correspond peut-être pas à votre version d’EXCEL, mais, il est
facilement transposable à une autre version.
1 PRESENTATION GENERALE DU LOGICIEL
Les fonctionnalités d'EXCEL
Tableur
Grapheur
Base de données
Modules Visual Basic d’Application
Le tableur : il permet la création de tableaux de toute nature, comprenant des textes, des valeurs saisies et des valeurs calculées, liaisons, recopie...
Le grapheur permet la réalisation rapide de graphiques à partir de tableaux, mise en forme, modification...
Les fonctions de base de données permettent la création de fichiers à partir desquels on peut extraire des renseignements en fonction de critères divers, réaliser des statistiques...
Le Visual Basic d’application permet l’automatisation de manipulations, de traitements...
EXCEL est de plus doté d'outils permettant de réaliser de très belles présentations.
2 LE TABLEUR
2.1 Configuration du tableur
Lorsque nous ouvrons EXCEL, nous ouvrons un classeur qui prend par défaut le nom de Classeur1.
Le classeur contient plusieurs feuilles repérées par des onglets en bas de l’écran, sur lesquelles nous allons pouvoir travailler en groupe de travail ou de façon individuelle sur chacune d’entre elle.
Une feuille comprend :
65536 lignes
256 colonnes
L’intersection d'une ligne et d'une colonne se nomme une cellule
65536 lignes * 256 colonnes = 16 777 216 cellules
Lorsque nous enregistrerons le travail réalisé, c’est le classeur complet qui sera enregistré et constituera le fichier qui prendra l’extension .XLS.
Un classeur a un nombre de feuilles par défaut défini par OUTILS/option/général
2.2 Utiliser la souris dans la feuille
pointer une cellule : positionner le pointeur de souris sur la cellule
cliquer sur une cellule pointée : sélectionne la cellule active
cliquer-glisser : pointer un élément, puis maintenir enfoncé le bouton gauche de la souris tout en déplaçant le pointeur.
sélectionner une plage de cellules : délimiter une plage de cellules, en déplaçant la souris avec une pression sur la touche gauche
3 LES SAISIES
3.1 Saisir un texte dans une cellule
pointer puis cliquer sur la cellule
taper le texte : il s'inscrit dans la barre de formule, et en même temps dans la cellule
valider en fin de saisie par la touche entrée ou en utilisant les flèches
3.2 Saisir des nombres
pointer puis cliquer sur la cellule
saisir le nombre
valider
3.3 Saisir des nombres dans des colonnes contiguës d'un tableau
Sélectionner la zone de saisie,
Saisir les nombres avec le clavier numérique et valider chaque saisie avec la touche entrée
En fin de colonne la cellule active passe automatiquement à la première cellule de la colonne suivante de la zone sélectionnée, sans avoir à se positionner sur celle-ci.
3.4 Modifier le contenu d'une cellule
cliquer sur la cellule
positionner le pointeur après le caractère à modifier, effacer avec Retour arrière et saisir
3.5 Effacer une cellule
cliquer sur la cellule
appuyer sur Suppr et valider ou EDITION/Effacer
3.6 Saisir la même donnée dans plusieurs cellules :
sélectionner la plage de cellules
saisir et valider par CTRL Entrée
3.7 Saisie de formules de calcul
Une formule est une expression commençant par le signe =, et permettant de réaliser des calculs à partir de différentes cellules.
cliquer sur la cellule dans laquelle vous voulez saisir la formule
taper =
cliquer sur la cellule contenant le premier élément de l'opération
taper le signe opératoire * / - + ou %
cliquer sur le deuxième élément du calcul
recommencer les deux dernières opérations autant de fois que vous avez d'éléments à intégrer dans le calcul
valider
3.8 Recopie de formules de calcul
sélectionner la cellule à recopier
positionner le pointeur sur la poignée de recopie
maintenir la touche gauche enfoncée tout en balayant les cellules dans lesquelles la formule doit être recopiée.
4 REPERAGE DES CELLULES
Une cellule est repérée par ls référence de la ligne et de la colonne sur laquelle elle se trouve. La façon dont ce repérage est effectué, peut être différente selon les besoins
4.1 Mode A1 et L1C1
Il existe 2 modes de repérage des cellules : mode A1 et mode L1CI. Pour passer de l'un à l'autre il suffit d'aller dans le menu : OUTILS/options/général. Cocher ou décocher Style de référence L1C1.
En mode A1, la lettre de la colonne dans laquelle se trouve la cellule est placée d'abord, ensuite vient le numéro de la ligne.
En mode L1C1, on a d'abord le numéro de colonne, puis le numéro de la ligne.
Dans la suite du document, nous allons travailler en mode L1C1.
4.2 Référence absolue
C'est l'adresse de la cellule, elle correspond à ses coordonnées.
Exemple :
L4C8 est la référence absolue de la cellule qui se trouve à l'intersection de la Ligne 4 et de la Colonne 8.
4.3 Référence relative
C'est la position d'une cellule par rapport à une autre cellule.
Exemple :
L-(2)C(-6) correspond à la cellule qui se trouve 2 lignes avant et 6 colonnes avant la cellule de base.
Si la cellule de base est L9C10, la cellule ci-dessus aura pour adresse :
Ligne 9 - 2 Lignes = Ligne 7
Colonnes 10 - 6 colonnes = Colonne 4
soit la référence absolue L7C4
Si la cellule de base change et devient par exemple L9C11, L(-2)C(-6) par rapport à L9C11 est L9C5
On utilise les références relatives pour effectuer des calculs en ligne ou en colonnes qui sont basés sur le même nombre d'éléments. Il suffit d'entrer la formule dans la première cellule de la ligne ou de la colonne et puis de la recopier dans les cellules suivantes.
Par contre, si l'un des éléments du calcul est situé dans une cellule fixe et est utilisé pour tous les calculs, cette cellule devra être identifiée sous forme absolue.
Pour transformer une référence relative en référence absolue, il suffit, une fois que la formule a été générée, de se positionner dans la barre de formule, sélectionner la référence à transformer, et appuyer sur la touche F4. Faire la même manœuvre pour passer de l'absolu au relatif.
A noter : en mode A1, les références absolues sont notées $A$1, A1 constituant une référence relative.
4.4 Exemple d’utilisation des références relatives et références absolues
1 2 3
1 Note 1 Note2 Total
2 12 15 =LC(-1)+LC(-2)
3 15 13 =LC(-1)+LC(-2)
4 14 17 =LC(-1)+LC(-2)
5 16 12 =LC(-1)+LC(-2)
6 8 15 =LC(-1)+LC(-2)
7 19 14 =LC(-1)+LC(-2)
8 12 15 =LC(-1)+LC(-2)
9 11 18 =LC(-1)+LC(-2)
10 10 11 =LC(-1)+LC(-2)
11 7 15 =LC(-1)+LC(-2)
12 Coefficient Note sur 20 Note finale
13 4 12 =LC(-1)*LC(-2)
14 15 =LC(-1)*L13C1
15 14 =LC(-1)*L13C1
16 16 =LC(-1)*L13C1
17 8 =LC(-1)*L13C1
18 19 =LC(-1)*L13C1
19 12 =LC(-1)*L13C1
20 11 =LC(-1)*L13C1
21 10 =LC(-1)*L13C1
5 CREATION DE TABLEAU
La création d’un tableau nécessite une préparation préalable en ce qui concerne son organisation sur les points suivants :
Le nombre de lignes et de colonnes nécessaires
Les textes : titre général du tableau, en-têtes de lignes et de colonnes à saisir une fois pour toutes
Les valeurs numériques monétaires ou autres, saisies, et qui doivent pouvoir être modifiées selon les besoins
Les calculs qui sont automatisés et basées sur les valeurs numériques saisies
Les liens avec d’autres tableaux…
Exemple : nous voulons réaliser un tableau sur 3 mois et en totalité sur le trimestre, présentant les éléments suivants : Chiffre d’affaires, Achats de marchandises, Marge commerciale
5.1 Lignes et colonnes
5 colonnes et 5 lignes sont nécessaires pour réaliser ce tableau :
Colonne
en-têtes lignes mois 1 mois 2 mois 3 trimestre
1 2 3 4 5
1 EVOLUTION SUR LE TRIMESTRE
2 MOIS 1 MOIS 2 MOIS 3 TOTAL
3 Chiffre d'affaires
4 Achats de marchandises
5 Marge commerciale
Nous aurons :
Une colonne pour les titres de ligne
Une colonne pour chacun des mois
Une colonne pour le total en ligne
Une ligne pour le titre
Une ligne pour les titres de colonne
Une ligne par information
Pour aérer le tableau, il n'est pas nécessaire de laisser des lignes blanches : il suffit simplement d'augmenter leur hauteur et de centrer le texte verticalement par rapport à celle-ci.
5.2 Zones saisies, zones calculées
Une cellule peut contenir du texte, des chiffres ou des formules de calculs.
Les zones saisies en texte sont fixes. Les zones contenant des valeurs saisies sont dites variables. Les zones contenant des formules varient lorsque les valeurs de base des calculs varient.
Textes saisis à la création du tableau
1 2 3 4 5
1 EVOLUTION SUR LE TRIMESTRE
2 MOIS 1 MOIS 2 MOIS 3 TOTAL
3 Chiffre d'affaires
4 Achats de marchandises
5 Marge commerciale
Valeurs numériques Formules de calcul
saisies
5.3 Les formules de calcul
Elles concernent :
pour chaque mois en L5C2, L5C3, L5C4 : la marge commerciale : chiffre d'affaires du mois - achats du mois. Il suffit d’entrer la formule en L5C2 la formule suivante
==L(-2)C-L(-1)C
puis la recopier vers la droite sur les 2 cellules adjacentes (références relatives).
pour le trimestre :
le total des chiffres d'affaires mensuels en L3C5 : =SOMME(LC(-3):LC(-1))
le total des achats mensuels en L4C5 : même formule que la précédente
le total des marges mensuelles en L5C5 : même formule que la précédente
La formule entrée en L3C5 peut-être recopiée sur les 2 cellules au-dessous.
A noter : la fonction SOMME peut être générée à l’aide du bouton SOMME AUTOMATIQUE se trouvant dans la barre d’outils , et en balayant la somme à totaliser.
6 LES FORMATS
Les cellules peuvent prendre des formats particuliers. Pour les affecter, il suffit de sélectionner les cellules concernées, puis de cliquer sur le menu FORMAT/Cellules
6.1 Formats généraux
Les différents onglets proposés dans cette commande permettent d’agir sur les formats suivants :
Nombres : ils sont classés par catégorie
Alignement : cela concerne l’alignement horizontal, vertical et le contrôle du texte
Police
Bordure
Motif
Protection : afin de paramétrer la protection de certaines cellules.
6.2 Formats personnalisés
Créer un format personnalisé permet d'afficher à coté d'un nombre une unité quelconque.
Exemple : 300 tonnes.
Pour cela, à partir de la cellule contenant le nombre, cliquer sur FORMAT/Cellules,
Cliquer sur personnalisé
Saisir dans la zone de saisie taper #" tonnes"
6.3
Formats de lignes et de colonnes
Lignes et colonnes peuvent être modifiés en largeur, hauteur, encadrement…
Modification de la largeur et de la hauteur
Pour modifier uniformément toutes les lignes et colonnes de la feuille:
sélectionner le carré grisé en haut à gauche de la feuille : toute la feuille est alors sélectionnée
faire un cliquer/glisser sur le trait séparateur entre 2 colonnes ou lignes pour élargir ou rétrécir.
Pour modifier une seule ligne ou colonne, faire la même chose sans sélectionner la totalité de la feuille.
7 COPIE DE CELLULES
La simple recopie de cellule peut être effectuée à l’aide de la souris ou bien des commandes du menu EDITION Recopier.
7.1 Avec la souris
Sélectionner la ou les cellules à Recopier. Positionner le pointeur sur la poignée de recopie, Etirer vers le bas ou la droite selon le cas.
7.2 Commande recopier
Permet de recopier le contenu d'une cellule vers d'autres cellules, vers le bas, le haut, à gauche ou à droite en fonction de l'orientation des cellules sélectionnées. La première cellule sélectionnée doit toujours être la cellule contenant les données à reproduire. Cliquer sur :
EDITION/Recopier/ Vers le bas, (le haut, à droite ou à gauche)
Permet de générer des séries, c'est-à-dire des listes de valeurs séparées par un écart, ou pas. Il suffit de saisir les deux premières valeurs, et de sélectionner les cellules suivantes en ligne ou en colonne. Cliquer alors sur :
EDITION/Recopier/Série
Les séries peuvent être :
en progression arithmétique (linéaire) : le pas est alors ajouté à chaque valeur pour donner la valeur suivante. Cela équivaut à la recopie incrémentée que l'on pratique avec la souris après avoir sélectionné les deux premières valeurs et les cellules sur lesquelles on veut recopier.
en progression géométrique : le pas est alors multiplié par chaque valeur pour donner la valeur suivante.
en progression chronologique journalière, mensuelle, annuelle... Le pas est alors ajouté à chaque valeur pour obtenir la suivante
Série linéaire (pas de 9) Série géométrique (pas de 9) Série chronologique (pas = 1 jour) Série chronologique (pas = 1 mois) Série chronologique (pas = 1 an)
1 1 11/11/2002 11/01/2002 11/01/1995
10 9 12/11/2002 11/02/2002 11/01/1996
19 81 13/11/2002 11/03/2002 11/01/1997
28 729 14/11/2002 11/04/2002 11/01/1998
37 6561 15/11/2002 11/05/2002 11/01/1999
46 59049 16/11/2002 11/06/2002 11/01/2000
55 531441 17/11/2002 11/07/2002 11/01/2001
64 4782969 18/11/2002 11/08/2002 11/01/2002
73 43046721 19/11/2002 11/09/2002 11/01/2003
7.3 Commande Edition Copier
Permet de stocker les données sélectionnées dans le presse papier.
7.4 Commande Edition Coller
Colle les données sélectionnées dans leur format d'origine. Equivaut à la commande Collage spécial/Tout
Si avant de copier une feuille de calcul, on sélectionne la feuille entière 5S2LEC le format des lignes et colonnes est aussi pris en compte.
7.5 Collage spécial (valeurs, formules, formats...)
Permet de choisir ce que l'on veut coller.
7.6 Collage spécial addition ou autres opérations
Permet d'additionner (multiplier, diviser...) la cellule copiée avec les cellules sélectionnées.
Prévisions par région, 1996, 1997 (montants en miliers de FF)
1998 Trim 1 Trim 2 Trim 3 Trim 4 Total
Amér. du Nord 200 240 235 200 875
Amér. du Sud 205 175 175 190 745
Extrême-Orient 235 245 260 265 1 005
Somme Ventes 640 660 670 655 2 625
1999 Trim 1 Trim 2 Trim 3 Trim 4 Total
Amér. du Nord 250 300 293.75 250 1 094 1.25
Amér. du Sud 256.25 218.75 218.75 237.5 931
Extrême-Orient 293.75 306.25 325 331.25 1 256
Somme Ventes 800 825 838 819 3 281
7.7 Collage spécial transposé
Permet de changer l'orientation des cellules copiées : les lignes deviennent des colonnes et les colonnes des lignes.
Pelles Pioches Râteaux Total
janvier 125 145 215 485
février 215 216 236 667
mars 325 216 245 786
Total 665 577 696 1938
janvier février mars
Pelles 125 215 325
Pioches 145 216 216
Râteaux 215 236 245
8 LES FONCTIONS
Les fonctions sont des formules prédéfinies qui effectuent des calculs en utilisant des valeurs particulières appelées arguments, dans un certain ordre (ou structure). Par exemple, la fonction SOMME additionne des valeurs ou des plages de cellules tandis que la fonction VPM calcule les remboursements de prêt en fonction du taux d'intérêt, de la durée du prêt et de son montant principal.
Arguments Les arguments peuvent être des nombres, du texte et des valeurs logiques telles que VRAI ou FAUX, des matrices, des valeurs d'erreur telles que #N/A ou des références de cellules. L'argument choisi doit produire une valeur valide. Les arguments peuvent aussi être des constantes, des formules ou d'autres fonctions
Structure La structure d'une fonction débute par le nom de la fonction suivi d'une parenthèse ouvrante puis des arguments de la fonction séparés par des virgules et se termine par une parenthèse fermante. Si la fonction débute une formule, tapez un signe égal (=) avant le nom de la fonction. Dès que vous créez une formule contenant une fonction, la Palette de formules vous aide
Signe égale (si la fonction est en début de formule)
Nom de la fonction
Arguments délimités par des parenthèses
=SOMME(L1C1:L10C1)
Les : signifient qu’il s’agit d’une plage de cellules
8.1 Les fonctions de date et heure
Une fonction peut être utilisée seule, imbriquée avec d’autres fonctions, devenir argument d’une fonction…
Exemple :
=DATE(ANNEE(A10);MOIS(B12);JOUR(B18))
La fonction DATE nécessite 3 arguments séparés par des ;
L’année que l’on peut saisir dans la fonction ou récupérer dans une autre date
Le mois que l’on peut saisir dans la fonction ou récupérer dans une autre date
Le jour que l’on peut saisir dans la fonction ou récupérer dans une autre date
La fonction ANNEE extrait l’année correspondant à une date saisie dans une cellule (ici A10)
La fonction MOIS extrait le mois correspondant à une date saisie dans une cellule (ici B12)
La fonction JOUR extrait le jour correspondant à une date saisie dans une cellule (ici B18)
Il existe différents types de fonctions que l’on peut consulter par le menu Insertion :Fonction ou par un clic sur l’icône .
8.2 Les fonctions de conversion
Elles permettent de passer de la numération en base 10, à la numération en binaire, en hexadécimal… et inversement, ce qui est très utile en micro informatique.
Ces fonctions de conversion peuvent être utilisées à condition d'avoir auparavant, chargé les macros complémentaires : utilitaires d'analyse et Utilitaire d'analyse VBA (Outils macros complémentaires).
=BINDEC(D3) : convertit le contenu en binaire de la cellule D3 en une valeur décimale
=DECBIN(D4;8) convertit le contenu en décimal de la cellule D4 en une valeur binaire sur 8 positions
=DECHEX(D6) : convertit le contenu en décimal de la cellule D6 en une valeur hexadécimale
=HEXDEC(D7) : convertit le contenu en hexadécimal de la cellule D7 en une valeur décimale
=BINHEX(D9) : convertit le contenu en binaire de la cellule D9 en une valeur hexadécimale
=HEXBIN(D10;8) : convertit le contenu en hexadécimal de la cellule D10 en une valeur binaire sur 8 positions
9 NOMMER LES CELLULES
Cela consiste à repérer des cellules ou plages de cellules par un Nom qui pourra être utilisé dans les formules à la place des références
9.1 Nommer une cellule
Sélectionner la cellule à nommer.
Cliquer sur INSERTION/Nom/définir et taper le nom à donner à la cellule ou conserver celui qui est proposé
Valider
Ce nom peut être maintenant utilisé dans des formules pour faire référence à la cellule nommée.
18,60% Cellule nommée Taux
Montant TVA
15000 2790,00 =Montant*Taux
12000 2232,00 =Montant*Taux
15800 2938,80 =Montant*Taux
Plage nommée Montant
9.2 Nommer des plages de cellule repérées par des titres de colonnes
Il est possible d’utiliser des titres de colonnes pour nommer les plages qui se trouvent immédiatement au-dessous.
Exemple :
1 2 3 4 5
1
2 Mois\Produits Pelles Pioches Râteau Total
3 Janvier 4545 7878 999 13422
4 février 4545 454 777 5776
5 mars 545 545 545 1635
6 Total =SOMME(pelles) =SOMME(Pioches) =SOMME(Râteau) 20833
Ces plages de cellules ont été nommées après avoir sélectionné les cellules L3C2 à L5C4 en cliquant sur INSERTION/Nom/créer, et en cliquant sur Ligne du haut
On pourra aussi nommer des plages en ligne à l’aide des titres de lignes.
10 GROUPE DE TRAVAIL
Un groupe de travail est constitué de plusieurs feuilles sur lesquelles on va pouvoir effectuer des saisies et mises en forme en même temps.
Cela sera utilisé pour créer des tableaux identiques : hebdomadaires, mensuels…
La structure de l’ensemble des tableaux sera alors créée en une seule fois. Les saisies sur chacun d’entre eux seront ensuite effectuées indépendamment.
10.1 Créer un groupe de travail dans un nouveau classeur :
Sélectionner autant de feuilles que de tableaux à créer. Pour cela :
Cliquer sur la première feuille
Maintenir appuyée la touche SHIFT
Cliquer sur la dernière feuille. Les feuilles sont lors sélectionnées, leur nom est en surbrillance. En haut dans la barre de titres apparaît Groupe de travail.
10.2 Créer les tableaux en groupe de travail
A partir de là, créer la structure des tableaux en incluant tous les éléments standardS :
Titres généraux, de lignes, de colonnes
Mises en forme de cellules, nombres…
Formules de calcul
Encadrements de cellules…
10.3 Dissocier les feuilles
Une fois la structure créée, dissocier les feuilles en effectuant un clic droit sur le nom d’une des feuilles et en cliquant sur dissocier.
Chaque feuille devient alors indépendante.
10.4 Utiliser les tableaux créés
Nommer chaque feuille en double cliquant sur le nom de la feuille et en saisissant un nom explicite.
Saisir les données de chacun des tableaux
11 CONSOLIDATION
On peut consolider par addition (ou autre opération) des tableaux représentant des données identiques sur des périodes successives.
Exemples :
On consolidera des tableaux mensuels sur l’année. On aura donc un tableau par mois et un tableau récapitulatif annuel qui cumule les données mensuelles.
On consolidera des tableaux par succursale en un seul tableau qui représentera l’ensemble des chiffres de l’entreprise.
Pour cela :
Il faut d’abord que les données aient été saisies dans les différents tableaux situés sur des feuilles différentes d’un même classeur.
Dans une feuille récapitulative, on lancera alors la consolidation à partir de la première cellule de la feuille, par Données/ Consolider.
La consolidation des 3 feuilles ci-dessus se fera suivant ce schéma :
Les références des données à consolider de chaque feuille ont été sélectionnées et ajoutées les unes après les autres.
On a ensuite coché Ligne du haut et Colonne de Gauche dans la rubrique Etiquettes, pour indiquer que la première ligne et la première colonne de la zone sélectionnée contiennent du texte et non pas des chiffres à ajouter.
On a coché aussi Lier aux données source de façon à créer un lien entre les 3 tableaux de base et le récapitulatif. Celui-ci se mettra ainsi à jour si les données source sont modifiées.
12 FONCTIONS LOGIQUES : SI, ET, OU
12.1 La fonction SI
La fonction SI permet de soumettre le résultat à obtenir à partir d'une formule de calcul à une condition.
Si la condition est remplie, tel résultat s’affichera, si elle n’est pas remplie, ce sera tel autre résultat.
Syntaxe de la fonction :
=SI(Condition;alors;sinon)
ou =SI(test logique;valeur si vrai;valeur si faux)
Exemple :
Nous voulons obtenir le tableau suivant :
Si l'élève a plus de 10 de moyenne, doit figurer le mot Encouragement dans la colonne appréciation, sinon, ce sera Des efforts à faire.
Moyenne des élèves
Elèves Moyenne Appréciation
Paul 12 Encouragement
Pierre 10 Des efforts à faire
Jacques 9 Des efforts à faire
Julie 15 Encouragement
(Les cellules contenant les moyennes ont été nommées Moyenne)
La formule à positionner dans la première cellule de la colonne 3 sera la suivante :
Moyenne des élèves
Elèves Moyenne Appréciation
Paul 12 =SI(Moyenne>10;"Encouragement";"Des efforts à faire")
Pierre 10
Jacques 9
Julie 15
Elle sera ensuite recopiée vers le bas.
=SI (Moyenne>10; "Encouragement"; "Des efforts à faire")
=SI (Condition; Alors; Sinon)
12.2 La fonction ET
Combinée avec la fonction SI, elle permet de soumettre le résultat à obtenir à partir d'une formule de calcul, à plusieurs conditions simultanées.
Syntaxe de la fonction :
=SI(ET(Condition1;Condition2;...);alors;sinon)
ou =SI(ET(test logique1;test logique2;...);valeur si vrai;valeur si faux)
Exemple :
Nous voulons obtenir le tableau suivant :
Si l'élève a une moyenne 1 supérieure à 10 et une moyenne 2 supérieure ou égale à 10, le mot Encouragement doit figurer dans la colonne appréciation. Si les deux conditions ne sont pas remplies simultanément, le message sera Des efforts à faire.
Moyenne des élèves
Elèves Moyenne1 Moyenne2 Appréciation
Paul 12 8 Des efforts à faire
Pierre 10 12 Des efforts à faire
Jacques 9 11 Des efforts à faire
Julie 15 16 Encouragement
Formule :
=SI(ET(Moyenne1>10;Moyenne2>=10);"Encouragement";"Des efforts à faire")
Moyenne des élèves
Elèves Moyenne1 Moyenne2 Appréciation
Paul 12 8
Pierre 10 12
Jacques 9 11
Julie 15 16
=SI( ET( Moyenne1>10; Moyenne2>=10); "Encouragement"; "Des efforts à faire")
=SI( ET( Condition1; Condition2); Alors; Sinon)
12.3 La fonction OU
Combinée avec la fonction SI, elle permet de soumettre le résultat à obtenir à partir d'une formule de calcul, à plusieurs conditions alternatives.
Syntaxe de la fonction :
=SI(OU(Condition1;Condition2;...);alors;sinon)
ou =SI(OU(test logique1;test logique2;...);valeur si vrai;valeur si faux)
Exemple :
Nous voulons obtenir le résultat suivant :
Si l'élève a une moyenne 1 supérieure à 10 ou une moyenne 2 supérieure ou égale à 10, le mot Encouragement doit figurer dans la colonne appréciation. Si l'une au moins des deux conditions n'est pas remplie, le message sera Des efforts à faire.
Moyenne des élèves
Elèves Moyenne1 Moyenne2 Appréciation
Paul 12 8 Encouragement
Pierre 10 12 Encouragement
Jacques 9 11 Encouragement
Julie 15 16 Encouragement
Formule :
=SI(OU(Moyenne1>10;Moyenne2>=10);"Encouragement";"Des efforts à faire")
Moyenne des élèves
Elèves Moyenne1 Moyenne2 Appréciation
Paul 12 8
Pierre 10 12
Jacques 9 11
Julie 15 16
=SI( OU( Moyenne1>10; Moyenne2>=10); "Encouragement"; "Des efforts à faire")
=SI( OU( Condition1; Condition2); Alors; Sinon)
12.4 Les opérateurs logique
Ils peuvent être :
=,>, <, >=,<=,
Le test peut porter sur des valeurs numériques intégrées ou non dans une formule de calcul, ou des valeurs alphabétique. Dans le cas où il porte sur des valeurs alpha, celles-ci doivent figurer entre guillemets ("aaaaa"), dans la formule.
Exemple : =SI(L2C6="Bien";...............;................)
12.5 Les résultats
Ils peuvent être :
des valeurs numériques
Exemple : SI(L2C6>10;100%;50%)
des valeurs alpha (doivent alors figurer entre guillemets)
Exemple : SI(L2C10="Bien";"RAS";"Voir les parents")
des formules de calcul comportant des opérateurs (+, -, *, /, %)
Exemple : SI(L2C6>1000000;L2C6*10%;L2C6*5%)
des formules comportant des fonctions (SOMME, MOYENNE, MAX, MIN, DATE, MOIS.....)
des tests avec utilisation de la fonction SI
13 FONCTION MATHEMATIQUE : SOMME.SI
Cette fonction permet d’effectuer la somme de cellules en fonction d’un critère se trouvant dans des cellules adjacentes.
Exemple :
Nous souhaitons ici effectuer la somme des règlements de la colonne Montant de ce tableau en fonction du mode de paiement.
Si nous n’utilisons pas la fonction SOMME.SI, il est nécessaire de créer 2 colonnes pour extraire le règlement en fonction du mode.
13.1 Sans la fonction SOMME.SI
Règlement Montant Espèces Chèques
Esp 10 000 F 10 000 F 0 F
Chèq 15 000 F 0 F 15 000 F
Chèq 12 500 F 0 F 12 500 F
Esp 14 569 F 14 569 F 0 F
Esp 2 498 F 2 498 F 0 F
Total des règlements 54 567 F 27 067 F 27 500 F
Formule Colonne Espèces
=SI(Règlement="Esp";LC(-1);0)
Formule Colonne Chèques
=SI(Règlement="Chèq";LC(-2);0)
Formule Cellules Total des règlements
=SOMME(Espèces)
=SOMME(Chèques)
13.2 Avec la fonction SOMME.SI
Nous n’avons plus besoin des 2 colonnes annexes. 2 cellules suffisent alors pour effectuer les calculs.
Règlement Montant
Esp 10 000 E
Chèq 15 000 E
Chèq 12 500 E
Esp 14 569 E
Esp 2 498 E
Total des règlements 54 567 E Dont 27 500 E par chèque
27 067 E en espèces
Vérification 54 567 E
Calcul des règlements par chèque :
=SOMME.SI(Règlement;"Chèq";Montant)
Calcul des règlements en espèces :
=SOMME.SI(Règlement;"Esp";Montant)
14 FONCTION DE RECHERCHE VECTORIELLE
Cette fonction permet de rechercher une valeur dans une table à partir d’une valeur saisie et la faire apparaître dans une cellule.
Exemple :
On dispose de la table des salariés de l’entreprise. Chaque colonne est repérée par un nom qui correspond au titre de la colonne concernée. On dispose d’une fiche individuelle salarié et on souhaite qu’à partir de la saisie du code du salarié, les références de celui-ci apparaissent.
Feuille Salariés
1 2 3
1 Codsal Nomsal Prenomsal
2 10 PAULOT Jean
3 11 DURAND Pierre
4 12 MARTIN Paul
5 13 MICHEL Jacques
6 14 JULIEN Jules
7 15 JENTOT Louis
FICHE INDIVIDUELLE SALARIE : Feuille Fichesal
1 2
1 Code
2 Nom
3 Prénom
4
Les cellules contenant le nom et le prénom du salarié seront automatiquement renseignées, lors de la saisie du code dans la cellule nommée Code.
Pour cela il faut positionner une formule contenant la fonction RECHERCHE( ), dans ces deux cellules :
Syntaxe de la fonction Recherche Vectorielle:
=RECHERCHE(valeur_cherchée;vecteur_recherche;vecteur_résultat)
Arguments :
valeur_cherchée valeur saisie, c'est-à-dire le code du salarié
vecteur_recherche colonne contenant les codes des salariés dans la feuille Salariés
vecteur_résultat colonne contenant le résultat à obtenir dans la feuille Salariés : colonne 2 pour le nom colonne 3 pour le prénom
Formule à positionner dans la cellule dans laquelle on désire faire apparaître le nom du salarié L2C2:
= RECHERCHE(Code;Salariés!Codsal;Salariés!Nomsal)
Formule à positionner dans la cellule dans laquelle on désire faire apparaître le prénom du salarié L3C2 :
= RECHERCHE(Code;Salariés!Codsal;Salariés!Prénomsal)
15 FONCTIONS FINANCIERES
Ce sont des fonctions qui permettent d’effectuer des calculs financiers de type remboursement d’emprunt, des calculs d’amortissements…
Exemples de fonctions financières :
VPM
PRINCPER
INTPER
AMORLIN
Calcule le remboursement d'un emprunt sur la base de remboursements et d'un taux d'intérêt constants.
Syntaxe de la fonction VPM
VPM(taux;npm;va;vc;type)
taux : représente le taux d'intérêt de l'emprunt (attention le taux est annuel, s’il y a plusieurs remboursements annuels, il faut diviser le taux par le nombre de remboursements)
npm : représente le nombre total de remboursements pour l'emprunt (Nombre d’années multipliée par le nombre de remboursements par an)
va : représente la valeur actuelle ou la valeur que représente à la date d'aujourd'hui une série de remboursements futurs ; il s'agit du principal de l'emprunt.
vc : représente la valeur future (valeur capitalisée), c'est-à-dire le montant que vous souhaitez obtenir après le dernier paiement. Si vc est omis, la valeur par défaut est 0 (zéro), c'est-à-dire que la valeur future d'un emprunt est égale à 0. (Argument facultatif)
type : représente le nombre 0 (zéro) ou 1 et indique quand les paiements doivent être effectués. . (Argument facultatif)
Exemple de tableau de remboursement d’emprunt
Les éléments de l’emprunt sont les suivants :
Montant 30 000 F
Taux 11%
Durée (en années) 4
Nombre remboursements par an 3
Remboursement 3 135,05 F
Les cellules contenant les valeurs de base ont été nommées :
Montant
Taux
Durée
Nb
Le remboursement annuel sera donc égal à :
=VPM ( Taux / Nb ; Nb * Durée ; -Montant )
Le tableau de remboursement constant sera le suivant :
Echéancier de remboursement
Période Capital Intérêt Remboursement Reste dû
1 2 035,05 E 1 100,00 E 3 135,05 E 27 964,95 E
2 2 109,67 E 1 025,38 E 3 135,05 E 25 855,29 E
3 2 187,02 E 948,03 E 3 135,05 E 23 668,27 E
4 2 267,21 E 867,84 E 3 135,05 E 21 401,06 E
5 2 350,34 E 784,71 E 3 135,05 E 19 050,72 E
6 2 436,52 E 698,53 E 3 135,05 E 16 614,19 E
7 2 525,86 E 609,19 E 3 135,05 E 14 088,34 E
8 2 618,47 E 516,57 E 3 135,05 E 11 469,86 E
9 2 714,49 E 420,56 E 3 135,05 E 8 755,37 E
10 2 814,02 E 321,03 E 3 135,05 E 5 941,36 E
11 2 917,20 E 217,85 E 3 135,05 E 3 024,16 E
12 3 024,16 E 110,89 E 3 135,05 E
Formule de calcul du capital remboursé à chaque période :
= PRINCPER(Taux/Nb;Période;Nb*Durée;-Montant)
Formule de calcul de l’intérêt correspondant à chaque paiement :
= INTPER(Taux/Nb;Période;Nb*Durée;-Montant)
16 PROTECTION D’UNE FEUILLE DE CALCUL
Ouvrir le classeur et la feuille à protéger.
Déverrouiller les cellules devant recevoir une saisie
Sélectionner de la cellule et la déprotéger par
FORMAT/Cellule/protection
Décocher Verrouillé
Dans le menu Outils, cliquer sur Protection, puis sur Protéger la feuille.
Pour empêcher la modification des cellules de feuille de calcul ou des éléments de graphique, activer la case à cocher Contenu.
Pour empêcher la modification des objets graphiques, activez la case à cocher Objets.
Pour empêcher d'autres utilisateurs de supprimer la protection de la feuille, tapez un mot de passe (à déconseiller) et cliquez sur OK, puis tapez à nouveau ce mot de passe dans la boîte de dialogue Confirmer le mot de passe.
Remarque
Pour supprimer la protection, cliquez sur Protection (menu Outils), puis sur Oter la protection de la feuille.
Si vous avez défini un mot de passe en protégeant la feuille, vous devez le taper pour supprimer la protection.
17 LES BASES DE DONNEES
17.1 Principe
Une base de données est un fichier qui permet d'organiser, de gérer, de retrouver et d'extraire des informations. Une base de données est créée sur une feuille de calcul, et doit être structurée d'une certaine façon.
Vocabulaire :
Plage de base de données : Plage de cellules rectangulaire, définie en tant que base de données. La première ligne de la plage contient les noms de champs. Les lignes suivantes contiennent les fiches ou enregistrements;
Fiche : une fiche correspond à une ligne de la base de données. Toutes le fiches sont structurées de la même façon, selon l'ordre des champs défini sur la première ligne.
Champ : Chaque colonne de la plage de base de données correspond à un champ distinct. Chaque cellule d'une colonne constitue un champ.
Nom de champ : Nom permettant d'identifier les données stockées dans un champ. Ils sont définis sur la première ligne de la base de données. A chaque nom de champ correspond un renseignement.
Champ calculé : Un champ calculé contient des formules de calcul.
Grille : Ecran de saisie d’une fiche
Exemple de base de données :
1 2 3 4 5 6 7
1 Facture Date Numsoc Société Montant TVA Total
2 11560 22/10/1999 101 Bénat SA 15 600,00 2 901,60 18 501,60
3 11561 20/11/1999 85 Répar 3 860,00 717,96 4 577,96
4 11562 22/11/1999 101 Bénat SA 12 500,00 2 325,00 14 825,00
5 11563 24/11/1999 88 Blatec SA 46 980,00 8 738,28 55 718,28
6 11564 25/11/1999 54 Vélo service 672,00 124,99 796,99
7 11565 25/11/1999 12 Céramique SA 1470,00 273,42 1743,42
8 11566 26/11/1999 85 Répar 540,00 100,44 640,44
9 11567 26/11/1999 54 Vélo service 18504,00 3441,74 21945,74
La première ligne contient les noms de champ.
Les lignes suivantes contiennent les fiches
La plage de base de données est constituée par les cellules L1C1 à L9C7.
Les champs Facture, Date, Numsoc, Société et Montant sont des champs saisis.
Les champs TVA et Total sont des champs calculés.
17.2 Création d'une base de données
Sur la première ligne, saisir les noms de champs.
Sur la deuxième ligne saisir la première fiche en respectant l'ordre des champs.
Les données saisies sont maintenant considérées comme une base de données par EXCEL.
17.3 Ajout de fiches
Cliquer sur DONNEES/Grille ou Formulaire. La grille suivante apparaît :
Cliquer sur Nouvelle pour ajouter une fiche.
Saisir le premier renseignement dans le premier champ.
Tabuler pour passer au champ suivant.
Recommencer ces opérations autant de fois que nécessaire.
Valider après la saisie du dernier champ. La validation permet d'enregistrer la fiche et de passer à la saisie de la fiche suivante.
L'utilisation de la grille a plusieurs avantages :
Les champs calculés sont recopiés automatiquement (Noter que dans la grille, il n'y a pas de possibilité d'accés à ces champs)
La plage de base de données est renommée lors de la saisie de chacune des fiches.
17.4 Tri d'une base de données
Le menu DONNEES/Trier permet de réorganiser les données d'une base dans l'ordre alphabétique ou numérique en fonction d'une ou plusieurs clés de tri. Une clé correspond à un champ.
Sélectionner la base de données sans prendre en compte les noms de champs.
Cliquer sur DONNEES/Trier
Dans la zone première clé, entrer la référence de la première cellule du champ sur lequel vous désirez trier.
Dans les zones correspondant aux deuxième et troisième clés, entrer la référence de la première cellule des champs sur lesquels vous désirez effectuer un deuxième classement.
Valider.
17.5 Extraction de données en fonction d'un critères
EXCEL permet d'extraire des listes de données correspondant à un ou plusieurs critères simples, combinés ou calculés. Il faut pour cela créer une zone de critère et une zone d'extraction.
17.6 Création d'une zone de critère simple
Positionner le pointeur de cellules à droite de la base de données.
Saisir le nom du champ sur lequel doit porter le critère
Saisir le critère lui-même dans la cellule au-dessous, sans espace.
17.7 Lancement de l'extraction
Cliquer sur DONNEES/ Filtre/ Filtre élaboré
Dans les zones prévues à cet effet dans la boite de dialogue :
Entrer la plage de base de données en la sélectionnant ou en tapant son nom si elle a été nommée,
Entrer ensuite les références de la zone de critère et la première cellule de la zone de destination.
Cocher Copier vers un autre emplacement de façon à ce que la base ne soit pas filtrée sur place.
17.8
Extraction sur plusieurs critères combinés
Il est possible de lancer une extraction à partir de critères combinés ET ou alternatifs OU.
Créer une zone de critères comprenant sur sa première ligne tous les noms de champs de la base.
Renseigner les champs correspondant aux critères que vous voulez retenir : sur la deuxième ligne si les critères doivent être simultanés, en décalage à partir de la deuxième ligne et sur les lignes suivantes s'ils doivent être alternatifs.
Zone de critères combinés (ET)
Facture Date Numsoc Société Montant TVA Total
>24/11/1999 >1000
On extraira les factures dont la date est postérieure au 24/11/99 et le total supérieur à 1000.
Zone de critères combinés OU
Facture Date Numsoc Société Montant TVA Total
101
85
On extraira les factures correspondant au numéro de société 101 ou au numéro 85
Aucun commentaire:
Enregistrer un commentaire