EXCEL
Table des matières
EXCEL L'écran d'Excel________________________
Excel Découverte d'Excel_____________________
Excel Barres d'outils________________________
EXCEL Découverte du tableur___________________
EXCEL Premier tableau_______________________
EXCEL Les cellules__________________________
EXCEL Saisie des données_____________________
EXCEL Affichage des données__________________
EXCEL Les astuces__________________________
EXCEL Découverte des fonctions_________________
EXCEL Mise en place des formules_______________
EXCEL Les premières fonctions__________________
EXCEL Les opérateurs________________________
EXCEL Tableau T°__________________________
EXCEL Les Formats de nombres 1/2_______________
EXCEL Les Formats de nombres 2/2_______________
EXCEL L’impression_________________________
EXCEL Mise en page_________________________
EXCEL En-tête/pied de page (infobulles)____________
EXCEL Contrôles et vérification 1/2________________
EXCEL Contrôles et vérification 2/2________________
EXCEL Date et Heure________________________
EXCEL Calcul Date et Heure____________________
EXCEL Les Feuilles de calcul___________________
EXCEL Copie, Déplacement & Insertion_____________
EXCEL Utilisation d’une feuille de calcul____________
EXCEL Manipuler Feuilles & Fenêtres______________
EXCEL Protection___________________________
EXCEL Les fichiers__________________________
EXCEL Touches de raccourcis utiles_______________
EXCEL Optimisation_________________________
EXCEL Conseils et dépannage__________________
EXCEL Les séries___________________________
EXCEL SI (formule de condition)_________________
EXCEL TEST 1 - Tableau de notes 2/2_____________
EXCEL Les références absolues 1/2_______________
EXCEL Les références absolues_________________
EXCEL Les réf. relatives et absolues 2/2____________
EXCEL Ex réf. absolues : Budget pub______________
EXCEL Les références nommées_________________
EXCEL Tableau mensuel d'exploitation_____________
EXCEL Création d’un plan 1/2___________________
EXCEL Création d’un plan 2/2___________________
EXCEL Graphiques 1/3_______________________
EXCEL Graphiques 2/3_______________________
EXCEL Graphiques 3/3_______________________
EXCEL La Base de données 1/2_________________
EXCEL La Base de données 2/2_________________
EXCEL Formules plus 1/2______________________
EXCEL Les Formules plus 2/2__________________
EXCEL Formats conditionnels___________________
EXCEL Le collage "Photo"_____________________
EXCEL Collage entre applications________________
EXCEL Tableaux croisés 1/2___________________
EXCEL Tableaux croisés 2/2___________________
EXCEL Valeur cible et commentaires______________
EXCEL Le collage spécial 1/2___________________
EXCEL Le collage spécial 2/2___________________
EXCEL Formules plus Fréquence_______________
EXCEL Utiliser une liste déroulante_______________
EXCEL Publipostage avec WORD________________
EXCEL Publipostage (impression & requêtes)_________
EXCEL Publipostage - Application________________
EXCEL Exercice : Facture avec TVA_______________
EXCEL Table de recherche. 1/3__________________
EXCEL Table de recherche. 2/3__________________
EXCEL Table de recherche. 3/3__________________
EXCEL Application Recherche/facture______________
EXCEL Application Recherche/fact/remises__________
EXCEL Condition et concaténation 1/2_____________
EXCEL Condition et concaténation 2/2_____________
EXCEL Macro-commandes 1/2__________________
EXCEL Macro-commandes 2/2__________________
EXCEL Formules plus________________________
EXCEL Outils contrôles de formulaire 1/2____________
EXCEL Outils contrôles de formulaire 2/2____________
EXCEL TEST 2 - Bulletin de notes 1/2______________
EXCEL TEST 2 - Bulletin de notes 2/2______________
EXCEL Validation des données__________________
Saintonge Informatique Formation
SAINTONGE INFORMATIQUE FORMATION 1
? Cliquez
? Cherchez le groupe où se trouve Excel (Logiciels, Microsoft, Applications).
LA BARRE D'OUTILS
LA BARRE D'ACCES RAPIDE
LES ONGLETS ET LES PANNEAUX
? Pour ouvrir une boite de dialogue, cliquez sur le lanceur ?
1 Saisissez = (observez la barre de formule au fur et à mesure de la saisie)
2 Cliquez dans C3
3 Saisissez +
4 Cliquez dans C4
5 Saisissez -
6 Cliquez dans C5
7 Cliquez l’icône Valider devant la barre de formule.
1 saisissez =
2 Cliquez dans C7, saisissez * 365
3 Tapez la touche Entrée
1 Saisissez 24
2 Cliquez dans F9, saisissez =
3 Cliquez dans C9, saisissez *
4 Cliquez dans D9
5 Cliquez Valider
A RETENIR :
Le signe = permet de démarrer une formule de calcul.
Les cellules concernées par le calcul peuvent être appelées avec la souris ou leurs coordonnées saisies au clavier.
Vous pouvez inclure dans les formules tous les signes opérateurs tels que (+;-;*;/;etc.)
Dans le même classeur que le tableau précédent, dans la feuille 2, créez le tableau suivant :
? Saisissez les FORMULES DE CALCUL adéquates dans les cellules marquées =?
| ACTIVITÉ | AVRIL | MAI | JUIN | TOTAL |
| VENTES | 1000 | 1200 | 2000 | =? |
| COÛTS | 700 | 1200 | 2100 | =? |
| MARGE | =? | =? | =? | =? |
Rappel : Tapez la touche Entrée ou E cliquez sur pour VALIDER LASAISIE
NB : Tapez la touche Échap ou X ou cliquez sur la case ANNULATION pour ARRÊTEREN COURS DESAISIE et RÉTABLIR le contenu précédent de la cellule.
Cliquez l’icône pour ANNULER la dernière ACTIONeffectuée
Pour sélectionner une cellule :
Pour sélectionner plusieurs cellules adjacentes :
Pour sélectionner plusieurs cellules non adjacentes :
Pour sélectionner une colonne ou une ligne :
Pour sélectionner plusieurs lignes ou plusieurs colonnes :
Pour sélectionnertoutela feuille de calcul :
Ou tapez Ca
Pour masquer une colonne (ou une ligne, ou une feuille) :
Pour retrouver une colonne (ou une ligne) masquée :
? Le principe est identique pour agir sur une ligne.
Pour saisir des données ou des formules dans une cellule :
Pour modifier des données ou une formule dans une cellule :
Pour saisir des données ou des formules dans une plage de cellules :
Pour saisir la même donnée ou formule dans toute une plage :
Pour faire un retour à la ligne dans une cellule sans agrandir la colonne
Ou Faire AE
Insertions automatiques :
Les données que vous saisissez ou que vous obtenez à partir de formules peuvent être affichées avec des paramètres différents et personnalisés.
On parle à cette occasion d’un FORMAT D’AFFICHAGE
Pour appliquer un format :
? POLICE permet de définir une police de caractères
? ALIGNEMENT permet de positionner les données dans la cellule
? NOMBRE permet de préciser l’affichage des nombres
? STYLE permet de faire une mise en forme automatique
? CELLULES permet de modifier la taille des cellules
? EDITION permet de choisir des outils de tri, de recopie…
Pour centrer du texte sur plusieurs colonnes :
Pour centrer du texte par rapport à la ligne :
Pour taper du texte vertical ou oblique:
Pour modifier la hauteur ou la largeur d'une cellule, d’une ligne ou d'une colonne :
Vous pouvez : Modifier la Hauteur.
Masquer la ligne.
Réafficher la ligne
Ajuster la hauteur à la taille des caractères
Vous pouvez : Modifier la Largeur.
Masquer la colonne.
Réafficher la colonne
Ajuster la largeur à la taille des caractères
Raccourcicliquez 2 fois sur le séparateur de colonne
Vous pouvez aussi vous mettre entre 2 lettres de colonnes ou entre 2 chiffres de lignes et ajuster avec la souris.
Pour annuler une commande :
Pour répéter une commande :
Pour recopier une formule ou des données :
Pour obtenir le menu contextuel :
? Dans une cellule texte, vous obtiendrez un menu concernant les textes.
? Dans un graphique, vous obtiendrez un menu concernant les graphiques.
Comment personnaliser la barre d'outils Accès rapide :
Pour personnaliser votre feuille de calcul :
ASTUCES
1.Saisissez les nombres suivants à partir de la cellule C3 :
180
242
364
92
41
137
288
401
322
402
MOYENNE :________________
TOTAL :________________
MAXI :________________
MINI :________________
NB :________________
DATE :________________
2.Vous saisirez les formules nécessaires à l’obtention des résultats en consultant les pages suivantes.
OBJECTIF: Apprendre la procédure pour mettre en place une formule.
Exercice: Calculer la moyenne de la liste des nombres saisis dans le tableau de la page précédente.
=MOYENNE(C3:C12)
LISTE DES FONCTIONS LES PLUS COURAMMENT UTILISÉES :
=MOYENNE(LISTE) Calcule la moyenne d'une liste.
Ex. =MOYENNE(C2:C10)
=SOMME(LISTE) Additionne tous les nombres d'une liste.
Ex. =SOMME(D4:D22)
=MAX(LISTE) Donne la valeur MAXIMUM d'une liste.
Ex. =MAX(A2;B5;E4;F10)
=MIN(LISTE) Donne la valeur MINIMUM d'une liste.
Ex. =MIN(A2:A10;B7;B10;B12)
=NB(LISTE) Donne le NOMBRE d’éléments dans une liste.
Ex. =NB(A2:A15)
=MAINTENANT( ) Donne la date et heure (choisir un format d’affichage).
Ex.=MAINTENANT()
Récapitulatif :
COMMENT DEFINIR UN DOMAINE ?
= égal <> différent
> supérieur à < inférieur à
>= supérieur ou égal à <= inférieur ou égal à
^ élévation à la puissance =5^2 * multiplication
+ et – addition et soustraction / division
( ) L’expression entre parenthèses est calculée en priorité :
=(2+4)*2 donne 12
alors que =2+4*2 donne 10
& Opérateur de CONCATÉNATION (assemblage) :
Exemple:
la cellule A1 contient 2005,
dans B2 tapez la formule suivante : ="VENTES pour "&A1
? résultat affiché dans B2 : VENTES pour 2005
Vocabulaire:
FONCTION il s’agit d’une expression de calcul contenue dans une formule.
(SOMME - NB - MAX - MIN - SI - PRODUIT – etc.)
Elles permettent de réaliser des calculs en simplifiant les formules.
Elles s’utilisent avec des arguments saisis entre parenthèses.
Exemple : =SOMME(liste de cellules)
LISTE c'est un ensemble de cellules (en lignes / en colonnes / indépendantes)
PLAGE c'est un ensemble de cellules (en lignes et en colonnes)
RÉFÉRENCES c'est l’adresse de la cellule (n° de ligne et lettre de colonne, ex : A1, J4, etc.)
LES OPÉRATEURS DE RÉFÉRENCES:
DEUX POINTS (:) est utilisé pour décrire une liste ou plage de cellules adjacentes
exemple : (A1:A6) ou (B12:C17)
POINT-VIRGULE (;) est utilisé pour décrire une liste de cellules non adjacentes
exemple : (A1;A7;B4;D5) ou (B2:C10;E1:G3)
OBJECTIFS : Créer un format personnalisé (°C ; positif en bleu, négatif en rouge)
Utiliser les fonctions (MOYENNE - MAX - MIN - MAINTENANT) pour afficher les résultats dans la 2ème partie du tableau
LES CLIMATS FRANÇAIS
| OCEANIQUE | MONTAGNE | TROPICAL | |
| BREST | CHAMONIX | LA REUNION | |
| POSITION: | 48.5° Nord | 46 ° Nord | 23 ° Sud |
| ALTITUDE: | 0 m | 1044 m | 0 m |
| JANVIER FEVRIER MARS AVRIL MAI JUIN JUILLET AOUT SEPTEMBRE OCTOBRE NOVEMBRE DECEMBRE | 6.0 °C 6.5 °C 9.0 °C 12.0 °C 13.5 °C 16.5 °C 17.0 °C 14.0 °C 12.0 °C 12.0 °C 8.5 °C 5.0 °C | -6.0 °C -4.0 °C 0.0 °C 5.0 °C 10.0 °C 13.5 °C 16.0 °C 14.0 °C 11.0 °C 4.5 °C -0.5 °C -5.0 °C | 25.5 °C 25.0 °C 22.5 °C 22.0 °C 21.0 °C 20.5 °C 20.0 °C 20.5 °C 21.0 °C 21.5 °C 23.0 °C 26.5 °C |
| MOY/ANNUELLE T° MAXI T° MINI MOY/HIVER MOY/ETE TABLEAU EDITE LE | sur 12;01:03 sur 06:08 JOUR | HEURE |
PRINCIPE : Transformer l'affichage d'un nombre standard en nombre monétaire, en pourcentage, en kilomètres, etc
Si vous tapez "2 kg" dans une cellule, Excel ne pourra pas effectuer de calculs à partir de cette cellule : dès qu’une cellule contient une lettre (excepté l’€ et le signe %), elle devient une cellule de texte, alors que pour calculer il faut des nombres.
Ainsi, il ne sera pas nécessaire de saisir dans les cellules les « € » pour des euros ou les « °C » pour une liste de températures.
Pour utiliser un format existant :
Pour créer un format personnalisé :
? Principe de construction
? Tapez un 0 pour afficher le chiffre
? Tapez le nombre de décimales que vous souhaitez, ex : 0.0
? Tapez le texte du format entre guillemets « »
Exemple : 0.0« °C », les espaces doivent être à l’intérieur des guillemets
? Validez
? Le bon fonctionnement de ces formats dépend des paramètres définis dans Windows ( panneau de configuration, options régionales)
? Certains formats réclament des saisies spécifiques ( cf. catégorie « spéciale »)
00000 pour un code postal ; 00-00-00-00-00 pour un numéro de téléphone
Règle principale: Installer un zéro au moins pour afficher vos chiffres, tapez le texte entre guillemets
Pour différencier les chiffes positifs des chiffres négatifs
Un format peut contenir 4 affichages différents qui agissent dans l’ordre suivant :
Les chiffres positifs, les chiffres négatifs, les chiffres nuls et le texte
Chaque section d'affichage est séparée par un point virgule
Exemple : [rouge]0.0« °C » ;[bleu]-0.0« °C » ;;
Pour appliquer une couleur à un format
Rajouter un des codes couleur dans le format ; voici les codes disponibles :
[NOIR] - [VERT] - [BLEU] - [CYAN] - [MAGENTA] - [ROUGE] - [BLANC] - [JAUNE]
Pour appliquer un format par quantité
Exemples de formats personnalisés (à créer):
Pour réaliser un aperçu avant impression :
Différents boutons permettent:
Pour définir le sens de l'impression :
Pour centrer votre tableau :
Pour ajuster votre tableau :
Pour insérer un en-tête ou un pied de page :
Pour imprimer le quadrillage ou les têtes de lignes ou de colonnes :
Pour imprimer une zone d’une feuille de calcul:
Pour créer une zone d'impression :
Pour annuler une zone d'impression :
Pour insérer un saut de page manuel :
sur une ligne (sens horizontal)
sur une colonne (sens vertical)
Pour supprimer un saut de page manuel :
Pour imprimer des documents longs :
Si vous voulez que les titres en colonne et/ou les titres en ligne d'un tableau soient imprimés sur chaque page, quelque soit la taille de votre tableau :
Pour VÉRIFIER RAPIDEMENT LES CALCULS
POUR AFFICHER ET IMPRIMER LES FORMULES
Avant tout, pensez à enregistrer votre classeur pour sauvegarder la version définitive de votre tableau, puis :
?POUR CELA, PLUSIEURS SOLUTIONS:
POUR RÉTABLIR LE TABLEAU DANS SA VERSION PRÉCÉDENTE
?FERMER le classeur SANS ENREGISTRER LES MODIFICATIONS.
Vous pourrez le rouvrir tel qu'il était au moment du dernier enregistrement
POUR REPÉRER LES ANTÉCÉDENTS ET LES DÉPENDANTS
IMPRIMER L'AUDIT
Pour supprimer les flèches d'audit:
Pour rechercher une information
POUR REMPLACER UNE INFORMATION
POUR ATTEINDRE UNE ZONE OU UNE CELLULE
FORMATS DE DATE ET D'HEURE:
En plus des formats prédéfinis, vous pouvez créer des formats personnalisés:
Dans une cellule, saisissez 5/9/07 8:30 et testez les formats suivants :
COMMENT SAISIR LA DATE ET L'HEURE COURANTES?
Une formule de calcul : =MAINTENANT() affiche la date et l’heure courante
Deux raccourcis-clavier : C+ ?date courante C+ :?heure courante
COMMENT EXCEL CALCULE-T-IL AVEC LES DATES ET LES HEURES?
Excel transforme les dates en nombres classiques : il dispose d'un calendrier qui débute au 1er janvier 1900, et cette date équivaut au nombre 1.
De la même manière, le nombre 36526 correspond au 1er janvier 2000, soit le 36526ème jour depuis le début du calendrier d'Excel.
Si une cellule qui contenait une date affiche un nombre, c'est que vous avez enlevé sans le vouloir le format de date.
Tapez par exemple C+j pour remettre un format de date.
De la même manière, 24 heures correspondent au nombre 1, midi à 0.5, 18h à 0.75.
QUELQUES FORMULES DE CALCUL POUR LES DATES:
=JOURS360(cellule1;cellule2) Donne le nombre de jours entre 2 dates
=JOUR(cellule) Donne le jour
=MOIS(cellule) Donne le mois
=ANNEE(cellule) Donne l’année.
=JOURSEM(cellule) Donne le numéro du jour de la semaine (1=dimanche)
=HEURE(cellule) Donne l'heure
=MINUTE(cellule) Donne les minutes
= (cellule1)-(cellule2) donne le nombre de jours entre ces 2 dates
utiliser le format aa "ans" mm "mois" jj "jours"
vous obtenez par exemple 2 ans 10 mois 14 jours
COMMENT ADDITIONNER DES DATES RÉGULIÈREMENT?
+ 1 an ?=DATE(ANNEE(cellule)+1;MOIS(cellule);JOUR(cellule))
+ 1 mois ?=DATE(ANNEE(cellule);MOIS(cellule)+1;JOUR(cellule))
+ 1 jour ?=cellule+1
OU ?=DATE(ANNEE(cellule);MOIS(cellule);JOUR(cellule)+1)
+ 1 an, 1 mois, 1jour
?=DATE(ANNEE(cellule)+1;MOIS(cellule)+1;JOUR(cellule)+1)
CONVERTIR UN NOMBRE EN HEURES
=TEXTE(cellule contenant le chiffre/24;«hh:mm»)
PourINSERERune nouvelle feuille de calcul :
PourRENOMMERune feuille de calcul :
Ou
Pour SUPPRIMER, DÉPLACER ou COPIER une feuille :
? Pour effectuer une copie, n’oubliez pas de cocher la case Créer une copie.
? Vous pouvez déplacer ou copier une feuille vers un nouveau classeur, ou vers un classeur existant, à condition que celui-ci soit déjà ouvert.
Groupe de travail: Lorsque plusieurs feuilles de calcul sont sélectionnées, tout ce qui vous réalisez dans une feuille est reproduit automatiquement dans les autres feuilles.
POUR REPRODUIRE RAPIDEMENT UNE FEUILLE DE CALCUL :
OBJECTIF : Reproduire autant de fois que nécessaire une feuille de calcul type.
OBJECTIF : déplacer, copier, insérer, supprimer, intervertir des cellules, des lignes ou des colonnes dans un tableau.
Pour DÉPLACER ou COPIER des lignes, colonnes, cellules ou plages de cellules :
Attention à ne pas écraser des cellules lors de vos manipulations!!!
? Pour ces opérations, vous pouvez utiliser les raccourcis clavier ou les icônes :
? Pour un déplacement, vous pouvez aussi faire glisser la sélection avec la souris :
? Pour une copie, faites glisser la sélection en maintenant la touche C enfoncée.
Pour INSÉRER une ou plusieurs cellules, lignes ou colonnes vides :
? Vous pouvez aussi tirer la poignée de recopie en maintenant la touche Maj.enfoncée
Pour SUPPRIMER une ou plusieurs cellules, plages de cellules, lignes ou colonnes :
Pour INSERER ou INTERVERTIR une ou plusieurs cellules, lignes ou colonnes :
? Vous pouvez tirer la sélection en maintenant la touche Maj. enfoncée
? Maintenez aussi la touche C enfoncée si vous voulez en même temps copier
POUR FIGER LES VOLETS D’UNE FEUILLE DE CALCUL :
OBJECTIF : Sans manipulation particulières, les têtes de lignes et de colonnes de votre tableau disparaissent au fur et à mesure que vous déplacez votre feuille.
Les volets pour permettront de continuer à visualiser les informations principales (têtes de lignes et de têtes de colonnes) d'un tableau dont la taille est supérieure à l’écran.
Vous pouvez aussi faire apparaître ces volets en les « tirant » avec la souris : ils se trouvent respectivement à droite de la barre de défilement horizontale et en haut de la barre de défilement vertical.
Pour SÉLECTIONNER plusieurs feuilles NON CONSÉCUTIVES :
Pour ajuster cette sélection :
? Tout en maintenant la touche C, cliquez sur un onglet pour ajouter ou supprimer une feuille de la sélection.
Pour SÉLECTIONNER plusieurs feuilles de calcul CONSÉCUTIVES :
COMMENT AFFICHER DEUX FENÊTRES EN MOSAÏQUE:
?les deux fenêtres apparaissent côte à côte
?on peut alors choisir d'afficher une feuille dans la première fenêtre et une autre feuille dans la seconde fenêtre
COMMENT REVENIR À UNE SEULE FENÊTRE:
COMMENT FAIRE DEFILER 2 FENËTRES EN SEMBLE:
COMMENT MASQUER OU AFFICHER UNE FENÊTRE:
?la fenêtre reste ouverte mais on ne peut pas la consulter
?la feuille est à nouveau affichée
OBJECTIF : Protéger les cellules contre des modifications non souhaitées (éviter que vos formules soient effacées par exemple).
Lorsque votre FEUILLE DE CALCUL est terminée, vous pouvez la protéger contre toute modification ou permettre une modification partielle :
Lorsque la protection de la feuille est activée, SEULES LES CELLULES VERROUILLÉES SONT PROTÉGÉES, tandis que les cellules déverrouillées restent modifiables.
?n'enlevez pas le verrouillage par défaut des cellules contenant les formules, ni celles contenant des valeurs constantes (exemple : les titres de colonnes ou de lignes)
1 Sélectionnez la cellule ou les cellules concernées
2 Cliquez l'onglet Accueil?panneau cellule?format
3 Cliquez Verrouillerla cellule pour éteindre l'icône
Seules les cellules DÉVERROUILLÉES sont alors modifiables mais les menus de manipulation sont interdits (police, nombre, etc.)
1 Cliquez l'onglet Accueil?panneau cellule?format
? Ôter la protection.
EXCEL propose les fichiers sous forme de classeurs qui peuvent contenir plusieurs feuilles de calcul. Cela permet de regrouper les feuilles de calcul traitant d’un même thème. Les classeurs d’EXCEL portent l’extension .XLS.
Pour créer un nouveau classeur :
Pour enregistrer pour la première fois un nouveau classeur :
Pour ouvrir un classeur existant :
Pour enregistrer un classeur existant :
Pour enregistrer un classeur sous un nouveau nom ou dans un autre dossier :
Pour enregistrer un classeur comme Modèle :
Pour utilisez un fichier Modèle :
SAINTONGE INFORMATIQUE FORMATION1
Touches de déplacements rapides:
Aller à la dernière ligne de la feuille CY
Aller à la dernière colonne de la feuille CR
Revenir à la première cellule de la feuille C
Aller à la dernière cellule du tableau C FIN
Consultez Les Menus:
(de nombreux raccourcis y sont affichés, en face de la commande correspondante)
? ex. :Cn pour Fichier>Nouveau
Voici les plus courants :
Cc COPIER Cx COUPER
Cv COLLER Ca SÉLECTIONNER TOUT
Cz ANNULER C DATE DU JOUR
Cr FORMAT STANDARD Cm FORMAT MONÉTAIRE
Cj FORMAT DATE Cq FORMAT HORAIRE
Certaines touches de fonction sont utiles:
@ ouvre la barre de formule
# affiche la liste des noms définis dans le classeur
$ répète la dernière action effectuée (pose de bordures, application d'un format, etc.) ; $ permet également, si vous êtes dans une formule, de FIGER une référence.
Comment paramétrer le signe Décimal
(le point au lieu de la virgule)
Corriger les erreurs d'arrondis
Même si vous avez demandé un format avec 2 décimales, Excel calcule avec tous les chiffres obtenues lors de ces calculs.
Aller en bas dans la zone calcul dans ce classeur
cocher (définir le calcul avec la précision au format affiché)
Formule pour préciser la manière d’arrondir les résultats
Calculer une somme avec le clavier
ou cliquez l'icône ? dans la barre d'outils
Excel va sélectionner automatiquement les chiffres les plus proches ( à contrôler)
Sélectionnez d'abord les chiffres et faîtes A = (égal) ou cliquez l'icône ?
OBJECTIF : Être plus efficace dans l’élaboration d’un tableau.
Exploiter les messages d’erreur pour apporter la correction appropriée.
CONSTRUCTION D’UNE FEUILLE DE CALCUL
ERREURS POSSIBLES
Ne saisissez pas de chiffres accompagnés de texte, aucun calcul ne sera
possible.. Utilisez un format personnalisé.
MESSAGES D’ERREUR
OBJECTIF: Créer à l’aide d’EXCEL une succession de valeurs (texte, date, nombre) automatique, dont la séquence peut être modifiée.
Pour créer une liste de texte ou de dates automatique :
Vous pouvez aussi inventer des listes :
Pour créer une série numérique :
Pour créer une série numérique dont le pas est différent de 1:
? Exemple : à partir d’une saisie de 2, vous obtiendrez :
Pour créer une série avec un pas différent de 1 avec le bouton gauche de la souris :
Vous pouvez aussi utiliser la poignée de recopie avec le bouton GAUCHE de la souris pour créer rapidement une série. Il vous faut dans ce cas :
? Saisir 2 valeurs pour déterminer le pas de la série.
? Sélectionner les 2 cellules avant de tirer la poignée de recopie.
Exemple : à partir d'une saisie de 3 et 5 vous obtiendrez 7, 9, 11, 13, 15, etc.
Une série recopiée vers la gauche ou vers le haut sera décroissante.
La formule de condition permet de faire exécuter à Excel de nombreuses manipulations.
=si(condition;VRAI;FAUX)
Cette formule affiche VRAI si la condition est respectée ; sinon, si la condition n'est pas respectée la formule affiche FAUX
Exemple :
=Si(A1=10;"10";"pas 10")
| Condition | vrai | faux |
? Si A1 est égal à 10; a2 affichera "10" sinon "pas 10".
=Si(A5>15;5%;0)
| Condition | vrai | faux |
| A | B | C | |
| 1 | ÉLÈVES | Notes | Appréciations |
| 2 | MUSSET | 16 | =si(B2<10;"insuffisant";si(B2<=14;"bien";"très bien")) |
| 3 | HUGO | 8 | Recopiez la formule vers la bas |
| 4 | CORNEILLE | 14 | Modifiez les notes pour vérifier votre formule de condition |
| 5 | RIMBAUD | 12 |
? Si la valeur de B2 est inférieure à 10, la formule en C2 affiche "insuffisant";
si la valeur de B2 est inférieure ou égale à 14, la formule en C2 affiche "bien";
sinon, la valeur de B2 est supérieure à 14 et la formule en C2 affiche "très bien".
Condition sur des dates
=si(c6<date(2009;4;30);«en retard»; «bon»)
FORMULES & MISE EN FORME
| NOM | Prénom | maths | français | histoire | géographie | anglais | biologie | physique |
| PERIGORD | Sarah | 20.00 | 16.00 | 14.00 |
12.00 | 11.00 | 15.00 | 20.00 |
| MAZOUIN | Éric | 1.00 | 20.00 | 0 | 2.00 | 15.00 | 17.00 | 20.00 |
| MAZOUIN | Yann | 10 | 10 | 6 | 8 | 9 | 6 | 1.00 |
| GARNIER | Loïc | abs | 3.00 | 2.00 | 0.10 | abs | 3.00 | 1.50 |
| POURAJEAUD | Claude | abs | 13.50 | 13.00 | 20.00 | 18.00 | 20.00 | 17.00 |
| FETIS | Béatrice | 8.00 | 15.00 | 9.00 | 17.00 | 19.50 | 20.00 | 3.00 |
| SEGUIN | Michael | 20.00 | 19.00 | 1.00 | 0.10 | 1.01 | 0.02 | 0.50 |
| DAVAUD | Sébastien | 3.00 | 1.00 | 16.00 | 12.00 | 13.00 | 14.00 | 17.00 |
| A | B | C | |
| BUDGET D'UNE FAMILLE | taux de l'euro | 6.55957 F | |
| EUROPÉENNE | En Francs | En Euros | |
| Une minute de téléphone | 0.67 F | 0.10 e | |
| Une baguette de pain | 3.70 F | 0.56 e | |
| Un quotidien | 4.80 F | 0.73 e | |
| Une douzaine d'huîtres | 16.00 F | 2.44 e | |
| Un bifteck de 300 gr | 30.00 F | 4.57 e | |
| Une revue | 35.00 F | 5.34 e | |
| Une place de cinéma | 45.00 F | 6.86 e | |
| Un disque audio | 129.00 F | 19.67 e | |
| Un livre | 159.00 F | 24.24 e | |
| Un plein d'essence en super 50 l | 290.00 F | 44.21 e | |
| La redevance TV | 690.00 F | 105.19 e | |
| Un caddie chez Leclerc | 850.00 F | 129.58 e | |
| La taxe d'habitation | 1800.00 F | 274.41 e | |
| Un poste de télévision | 2990.00 F | 455.82 e | |
| Un salaire | 5500.00 F | 838.47 e | |
| Un salaire | 8000.00 F | 1219.59 e | |
| Un salaire | 12000.00 F | 1829.39 e | |
| Une voiture 5p 6cv | 86900.00 F | 13247.82 e | |
| Une maison | 400000.00 F | 60979.61 e | |
Pour calculer le prix en euro d’une minute de téléphone, il faut diviser le prix en Francs par le taux de conversion.
Puis il faut recopier cette formule vers le bas pour les autres prix.
Le problème est le suivant : lorsqu’on recopie une formule vers le bas ou la droite les références des cellules s’incrémentent en fonction du sens de la recopie.
C’est normal, ce sont des références relatives.
Or pour obtenir des résultats justes, il faut toujours diviser par le taux de l’euro qui se trouve dans C2.
Lors de la construction de la formule =B4/C2 il faut FIGER C2
?La RÉFÉRENCE RELATIVE (C2) est devenue une RÉFÉRENCE ABSOLUE ($C$2)
Comment déterminer les adresses à figer
Construisez 3 ou 4 formules manuellement-
Vous allez découvrir que certains éléments se répètent
Ce sont ces éléments qu’il vous faut figer avec le signe $
| A | B | C | |
| 1 | Totaux | Janvier | |
| 2 | Brochures | =B2/B7 | =C2/C7 |
| 3 | Envois | =B3/B7 | =C3/C7 |
| 4 | Annonces | =B4/B7 | =C4/C7 |
| 5 | Dépliants | =B5/B$7 | =C5/C$7 |
| 6 | Totaux | =B6/B$7 | =C6/C$7 |
Saisissez la première formule et avec le bouton recopie,
recopiez vers la droite puis vers le bas.
| A | B | C | |
| 1 | Totaux | Janvier | |
|
2 | Brochures | =B2/B$7 | |
| 3 | Envois | ||
| 4 | Annonces | ||
| 5 | Dépliants | ||
| 6 | Totaux |
Votre tableau est rempli
OBJECTIF : Pour faciliter la recopie d'une formule de calcul, on modifie les références des cellules utilisées dans cette formule.
Il existe 4 types de références:
Exemple de formule : =B4
Les références relatives évoluent lorsque l'on recopie la formule :
Les références absolues sont fixes par rapport à la feuille de calcul : elles restent inchangée lors de la recopie des formules : la formule =$B$4 restera inchangée qu'on la recopie, à droite, en bas ,à gauche ou en haut.
L'adresse de la cellule est ½ absolue, ½ relative : seule la référence relative évolue lors de la recopie de la formule, tandis que la référence absolue reste fixe
Exemple de formule: =$B4?lacolonneestabsolue, lalignerelative
De la même manière, la formule =B$4 (colonnerelative,ligneabsolue) n'évolue que si elle est recopié à gauche =A$4, ou à droite =C$4.
Les noms se comportent exactement comme des références absolues.
Pour transformer une référence relative en référence absolue (et vice versa):
? Saisissez un $ devant la référence à modifier (lettre de colonne et/ou N° de ligne)
OU
? Utilisez $pour faire défiler les différents types de référence : absolue, mixte, relative.
ThÈme:Tableau automatique.
À partir des données saisies dans le premier tableau, il faudra réaliser les calculs qui s’afficheront dans le deuxième.
OBJECTIF:Utiliser les références relatives et absolues.
Rappel: utilisez la touche $ pour figer l’adresse d'une cellule.
OBJECTIF: Utiliser des noms dans les formules de calcul au lieu des habituelles références de cellules. EXCEL offre la possibilité de nommer une ou plusieurs cellules.
Utiliser des noms dans les formules rend plus aisée la compréhension desdites formules et facilite la recopie de ces formules car les noms se comportent comme des références absolues.
POUR ATTRIBUER UN NOM À UNE CELLULE OU À UNE PLAGE DE CELLULE :
#NOM? ce message apparaît si votre formule contient un nom qui n’a pas été défini, ou qui est mal orthographié, ou un nom de fonction qui n'existe pas (ex. maxi au lieu de max…).
POUR INCLURE UN NOM SANS RISQUE D'ERREUR DANS UNE FORMULE:
POUR CRÉER PLUSIEURS NOMS EN MÊME TEMPS :
? Construisez le tableau suivant sans les formules de calcul :
| A | B | C | D | E | |
| 1 | JANVIER | FÉVRIER | MARS | ||
| 2 | VENTES | 100 | 100 | 100 | =somme(VENTES) |
| 3 | COÛTS | 60 | 60 | 60 | =somme(COÛTS) |
| 4 | PROFITS | =VENTES-COÛTS | =VENTES-COÛTS | =VENTES-COÛTS | =somme(PROFITS) |
Cliquez onglet Formules panneau noms définis – gestionnaires de noms
Cliquez modifier et resélectionner les bonnes cellules.
TABLEAU MENSUEL D’EXPLOITATION
| VENTES | CHARGES | SALAIRES | TOTAL CHARGES | RÉSULTAT | RÉSULTAT CUMULÉ | |
| JANVIER FÉVRIER MARS | =? = ? = ? | =? = ? = ? | =? =? = ? | |||
| 1er TRIM | =? | = ? | = ? | = ? | = ? | |
| AVRIL MAI JUIN | = ? = ? = ? | = ? = ? = ? | = ? = ? = ? | |||
| 2ème TRIM | = ? | = ? | = ? | = ? | = ? | |
| 1er SEMESTRE | =? | = ? | = ? | = ? | = ? | |
| JUILLET AOÛT SEPTEMBRE | = ? = ? = ? | = ? = ? = ? | = ? = ? = ? | |||
| 3ème TRIM | = ? | = ? | = ? | = ? | = ? | |
| OCTOBRE NOVEMBRE DÉCEMBRE | = ? = ? = ? | = ? = ? = ? | = ? = ? = ? | |||
| 4ème TRIM | = ? | = ? | = ? | = ? | = ? | |
| 2ème SEMESTRE | = ? | = ? | = ? | = ? | = ? | |
| TOTAL | =? | = ? | = ? | = ? | = ? |
OBJECTIF : Préparer la visualisation ou l’impression de certaines parties d’un tableau.
Dans une feuille de calcul complexe, la création d’un plan permet d’afficher seulement une partie de cette feuille, par exemple les lignes ou les colonnes de synthèse, et de masquer les autres informations.
Pour créer un plan automatique:
?des niveaux se créent automatiquement selon les formules de calcul du tableau.
?des boutons de manipulation s'affichent dans les marges
Pour réduire un niveau :
? Cliquez sur le bouton - dans la marge
?Seule la ligne ou la colonne de synthèse sera affichée.
Pour développer un niveau :
? Cliquez sur le bouton + dans la marge
?Les lignes ou les colonnes de détail correspondant aux lignes ou aux colonnes de synthèse seront affichées à nouveau.
Pour afficher un niveau spécifique :
? Cliquez sur les boutons de niveau de lignes ou de colonnes : 1 2 3 etc.
Pour effacer le plan :
Pour créer un plan personnalisé:
2.1. Sélectionnez UNIQUEMENT les lignes ou les colonnes (têtes de lignes ou de colonnes) qui devront êtremasquées dans le niveau 1
2.2. Cliquez l'onglet Données?panneau plan?Grouper
2.3. Vérifiez que votre niveau est correctement créé en cliquant sur + ou -
?lorsque le niveau est réduit, seules les lignes qui n'ont pas été groupées doivent rester affichées
2.4. En cas d'erreur, supprimez le niveau en cliquant sur le bouton Dissocier
Vous pouvez créer un plan de niveau N-1 à l’intérieur d’un plan de niveau N..
Exemple : vous avez déjà créé le niveau 1er semestre, vous devez maintenant créer un niveau pour le 1er trimestre :
| Janvier |
|
| Février | |
| Mars | |
| 1er TRIM |
Commentcréerun graphique
Commentsélectionnerun graphique : cliquez 1 fois dans la zone de graphique
Commentmodifierla taille d’un graphique
Commentdéplacerun graphique
Comment sélectionner lesdifférents élémentsd’un graphique
Cliquez sur l’élément choisi
Commentmodifierlesdifférents élémentsd’un graphique
Couleurs, motifs, polices, tailles, bordures, nombres, position des étiquettes, des axes…
Commentajouterun élément dans le graphique
Comment modifier le texte de la légende
Dans la zone Nom, tapez le titre de la série ou cliquez sur la cellule correspondante dans le tableau d'origine (utilisez le bouton pour masquer provisoirement la boîte de dialogue)
Comment modifier letyped’ungraphique
Comment modifier lesensd’un graphique
Comment modifier letyped’unesérie
Comment tracer une série sur un axe secondaire
QUEL GRAPHIQUE CHOISIR SELON LE TYPE DE DONNÉES?
| ? | graphique en courbes ou histogrammes |
| ? | graphique en barres |
| ? | graphique à secteurs |
| ? | graphique radar |
Comment modifier l’ordre de traçage
Comment créer un graphique à combinaisons
Comment ajouter une série à un graphique
Comment inverser l’ordre de traçage
Comment modifier la présentation d'un graphique 3D
Comment réaliser un graphique à images
Cliquez remplissage dans la mise en forme d'une série de données
Choisissez remplissage image ou clipart
DEFINITION :
Une Base de données est un outil permettant d’organiser, de gérer, de retrouver et d’extraire des informations.
VOCABULAIRE :
Pour créer une Base de données :
METTRE EN PLACE L'outil formulaire
Puis ?Autres commandes
Utilisation DU FORMULAIRE
Pour ajouter une nouvelle fiche
Cette fiche est ajoutée à la Base et une nouvelle fiche apparaît.
Pour rechercher une fiche :
Pour trier les informations (classer)
(une clé est une préférence, lorsque vous utilisez plusieurs clés, vous déterminez des préférences prioritaires).
POUR EXTRAIRE DES INFORMATIONS
POUR REALISER DES STATISTIQUES
LES FORMULES DE CONDITIONS
| NOM | Montant |
| Beaujolais | 5000 hl |
| Bordeaux | 12000 hl |
| Côtes du Rhône | 8500 hl |
| Beaujolais | 10000 hl |
| Bordeaux | 7500 hl |
| Bordeaux | 9200 hl |
Pour obtenir une somme à partir d'une condition
| =(colonne des noms;"beaujolais";liste des montants | 15000 hl |
| =(colonne des noms;"bordeaux";liste des montants | 28700 hl |
Quand la formule rencontre le nom "beaujolais", le chiffre figurant dans la colonne montant est additionné.
Pour obtenir le nombre d'informations sous condition
| =(liste des noms;"beaujolais") | 2 |
| =(liste des noms;"bordeaux") | 3 |
La formule compte à chaque fois que le nom "beaujolais" est mentionné dans la colonne des noms.
Pourconvertir des données "texte" en données "Excel"
CONVERTIR
Exemple : des données saisies sous Word et séparées par des virgules ou point-virgules
Monsieur, Vanille, Desbois, 56, place des Halles, 31000, Toulouse
Monsieur, Yvan, Leterrible, 58, bd Robespierre, 75018, PARIS
Certaines formules fonctionnent avec des valeurs « TEXTE » d'autres avec des valeurs « CHIFFRES »
Les formules les mieux adaptées au « TEXTE »
Pour obtenir le nombre de données "texte" dans une liste
• =NBVAL(liste)
Pour assembler deux informations
•=CONCATENER(texte 1 ;" ";texte2)
| Nom | Prénom | =Concaténer(celluîe 1 ;" ";cellule2) |
| Durand | Jacques | Durand Jacques |
| Bertrand | Joël | Bertrand Joël |
| N° Fournisseur | Famille article | Code article | Concaténer (cell;cel2;cel3) |
| B | 10 | 545 | B10545 |
Pour extraire la partie gauche d'une information
• =GAUCHE(cellule;Nbre de Caractères)
| Code article | =gauche(cellule ;1) |
| B10545 | B |
Pour extraire la partie droite d'une information
• =DROITE(cellule;Nbre de caractères)
| Code article | =Droite(cellule;3) |
| B10545 | 545 |
Pour extraire une partie d'une information
• =STXT(cellule;rang;nbre)
| Code article | =Stxt(cellule;2;2) |
| B 10545 | 10 |
Pour comparer 2 informations
• =EXACT(celluiel;cellule2)
cette formule permet de vérifier le contenu de 2 cellules le résultat affiché est ; Vrai ou Faux
Pour obtenir le nombre de données différentes
=sommeprod((liste de cellules;même liste))
Objectif : obtenir une mise en forme automatique selon le contenu de la cellule
Exemple : afficher une trame de fond de couleur différente selon le texte saisi dans la cellule
Une autre méthode pour lier des éléments d'Excel est d'utiliser son "appareil photo".
Mise en place de l'appareil
Utilisation de l'appareil
(OLE – DDE)
DEFINITON
Le collage spécial permet de reproduire un document réalisé par une autre application en respectant la mise en forme.
Le collage spécial peut être réalisé avec liaison afin de permettre, lors de l'ouverture du fichier, les mises à jour qui ont été effectuées dans le document d'origine.
Une liaison OLE permet d'activer l'application source
Une liaison DDE met à jour les informations en provenance de la source.
Exemples :
? Un tableur et un graphique installé sur EXCEL peuvent être incorporés dans un rapport réalisé sous Word.
Procédure :
? vous obtiendrez une image du graphique ou du tableau d’EXCEL
? Vous obtiendrez un tableau sous le format de Word
? Les modifications apportées dans la feuille d'Excel seront reportées dans le document de Word.
? Un double clic sur la copie permettra l'accès à EXCEL.
OBJECTIF : Utiliser et présenter les informations d’une Base de données sous forme statistiques
Un tableau croisé vous permet à partir d’informations stockées dans une Base de données de créer un tableau dans lequel il est possible de regrouper et de présenter les informations selon un objectif précis.
Il est indispensable de disposer d’une plage de Base de données avant de créer un tableau croisé et de placer le curseur dans cette Base.
Pour créer un tableau croisé :
?Un assistant vous guide dans la construction du tableau
faîtes glisser vers les zonesvaleurs,colonne,ligneoufiltre
les champs que vous voulez y voir figurer
Il faut choisir des champs qui regrouperont les informations :
? Zone ligne (c’est à dire à la gauche du tableau, par ligne)
? Zone colonne (c’est à dire en haut du tableau, par colonne)
? Zone valeurs C’est la zone de statistiques
? Zone filtre (Créé une liste déroulante à partir du champ choisi)
Pour sélectionner un tableau croisé
Pour créer un nouveau tableau croisé (partir de la même Base de données) :
Pour modifier un tableau croisé :
Pour personnaliser un tableau croisé
Pour modifier les calculs dans un tableau croisé :
Dans la zone valeurs, faites un -clic droit sur le CHAMP à modifier
Exemple:
Pour déplacer les éléments :
Pour enlever les sous totaux
Pour modifier la source de votre tableau croisé
Pour ajouter une formule
COMMENT SIMULER UNE SITUATION A L’AIDE D’UNE VALEUR CIBLE
Vous pouvez simuler différentes situations en utilisant la commande VALEUR CIBLE.
PROCEDURE
Problème : Pour un emprunt de 60000 F à un taux de 8%, quelle va être la durée du prêt pour obtenir des remboursements mensuels de 2000 F ?
COMMENT AJOUTER UN COMMENTAIRE
En fonction du résultat que l'on souhaite obtenir, les manipulations classiques copier-coller ne sont pas toujours adaptées.
La manipulation copier-coller classique recopie le contenu d'une cellule (formule, texte ou nombre) ainsi que sa mise en forme (police, bordures, trame, formats de nombre…).
Le collage spécial permet de varier les circonstance de collage et d'aboutir au bon résultat.
?choisissez ce que vous voulez coller:
Tout dépend du résultat souhaité.
1EREXEMPLE: les totaux du tableau de SAISIE HEBDOMADAIRE doivent être reportés dans le tableau archives ; un copier coller classique ne fonctionne pas :
| SAISIE HEBDOMADAIRE | |||
| Nbre | PU | Totaux | |
| Lundi | 2 | 500 | 1000 |
| Mardi | 2 | 250 | 500 |
| Mercredi | 2 | 400 | 800 |
| Jeudi | 2 | 700 | 1400 |
| Vendredi | 2 | 600 | 1200 |
? IL FAUTCOLLER LES VALEURS
| ARCHIVES | |||||
| Lundi | Mardi | Mercredi | Jeudi | Vendredi | |
| Semaine 1 | 1000 | 500 | 800 | 1400 | 1200 |
2ÈMEEXEMPLE: les valeurs du tableau ARCHIVES doivent être additionnés dans le tableau cumul, toujours dans la même cellule ; un copier coller classique ne fonctionne pas :
? IL FAUTCOLLER AVEC ADDITION :
| Tableau cumul | ? | Tableau cumul | ||||||
| Semaine 1 | 1000 | ? | Semaine 1 | 4900 | ||||
3ÈMEEXEMPLE: les stocks de différents articles doivent être reportés dans l'inventaire ; un copier coller classique ne fonctionne pas :
? IL FAUTCOLLER AVEC LIAISON:
| Fiche de stock Article 1 | Fiche de stock Article 2 | Fiche de stock Article 3 | ||||
| Mouvements | Entrées | 200 | Entrées | 450 | Entrées | 500 |
| Sorties | 50 | Sorties | 200 | Sorties | 150 | |
| Stock final | 150 | 250 | 350 | |||
| INVENTAIRE | |
| Article 1 | 150 |
| Article 2 | 250 |
| Article 3 | 350 |
4èmeexemple: comment inverser sans manipulation complexe la présentation d’un tableau ?
| 1er semestre | 2ème semestre | ? | Recettes | Dépenses | ||
| Recettes | 1500 | 2000 | ? | 1er semestre | 1500 | 1400 |
| Dépenses | 1400 | 1700 | ? | 2ème semestre | 2000 | 1700 |
Thème : Evaluer des données par intervalle
Exemple : Obtenir par tranche d’âge le nombre de personnes
Obtenir par tranche de prix, le nombre d’articles.
Dans votre base de données « population ou tarif », saisissez dans la 1ère colonne les tranches ou intervalles que vous souhaitez.
| 20 | |
| 25 | |
| 30 | |
| 35 | |
| 40 | |
| 45 | |
| 50 | |
| 55 | |
| 60 | |
| 65 | |
| 70 | |
| 75 | |
| … |
Sélectionnerla 2éme colonne à coté de vos intervalles
Saisissez la formule suivante :
la formule doit ressembler à celle-ci:
{=fréquence(zone à comparer; zone d’intervalles)}
il s’agit d’une formule matricielle, il faut valider par <entrée>
Vous obtenez à coté de votre zone d’intervalles, le nombre de personnes ou d’articles correspondant aux tranches.
Comment mettre en place une liste déroulante?
Principe:
.1 Dans WORD,
.2 Dans un nouveau fichier, cliquez panneau publipostage…
.3 Cliquez démarrer la fusion puis choisissez Lettres
.4 Cliquez Sélection des destinataires
.5 choisissez utiliser la liste existante
.6 Cliquerparcourir
.7allez dans votre dossier chercher votre fichier excel
.8Saisissez le texte de la lettre et cliquez dans le panneaupublipostage pour insérez les champs là où c’est nécessaire.
.9Enregistrez régulièrement toutes les modifications.
.10 Cliquez Aperçu des résultats, pour vérifier la fusion
.11 Cliquez ou pour faire défiler les différents enregistrements.
.12 Cliquez Aperçu des résultats, pour affichez à nouveau les noms des champs
.13 Cliquez pour imprimer la lettre type avec les noms de champ
A-POUR IMPRIMERTOUTESLES LETTRES:
B-POUR IMPRIMERUNE SEULELETTRE:
C-POURSÉLECTIONNERLES ENREGISTREMENTS:
| Exemple : | Champ | element de comparaison | information a comparer |
| NOM | est égal à | DUPONT |
?La fusion concernera uniquement les enregistrements dont le champ NOM contient l’information «DUPONT».
Utiliser le tableau des notes comme source de données pour le publipostage suivant :
THÈME : FACTURE (tableau automatique).
OBJECTIFS :
Dans cette facture qui doit réclamer le minimum de saisie, on doit obtenir le maximum de résultats grâce à la mise en place de formules.
Utiliser la fonction « SI » pour ventiler les montants HT selon le code TVA dans deux colonnes et masquer ces colonnes pour éviter leur impression.
Pour obtenir le prix hors taxes :
Le prix unitaire après la remise se calcule comme ceci : PU‑(PU*Remise/100), formule que l'on simplifie ainsi : PU*(1‑Remise/100), soit pour une remise de 10%, un coefficient multiplicateur de 1‑10%=0.90.
Si vous avez nommé correctement vos cellules, votre formule pour le calcul du prix HT peut s'écrire ainsi :
=(QTE*PU)*(1‑R%)
Pour calculer le montant total soumis à chaque taux de TVA :
Le choix du taux applicable (5.5 % ou 20.6 %) s'effectue par un code saisi dans la colonne TVA, à savoir A pour 5,5 % et B pour 20,6 %.
Le montant HT de chaque article devra être ventilé dans 2 colonnes supplémentaires, à droite de la facture.
Une formule de condition, =SI(CONDITION;résultat si VRAI;résultat si FAUX), sera utilisée pour placer le HT dans la première colonne si le code TVA est égal à A, ou dans la deuxième colonne si le code TVA est égal à B.
La somme de ces 2 colonnes permettra obtenir le total des bases TVA 5.5% et TVA 20.6%.
Pour rendre ces 2 colonnes non imprimables :
Bien qu’invisibles, les formules à l’intérieur de ces 2 colonnes continuent de fonctionner.
OBJECTIF : Extraire des informations à partir d’une table contenant des données.
Une table de recherche est une plage de cellule, contenant des informations que l’on peut extraire à partir de la formule RECHERCHE.
La création de cette table de recherche obéit à certaines règles :
Elle peut se construire en colonne (dans le sens vertical)
Elle peut se construire en ligne (dans le sens horizontal).
La première ligne ou la première colonne de la table contient les valeurs de comparaison.
Ces valeurs, qui constituent la base de la table, doivent être classées dans un ordre croissant.
Le reste de la table contient les valeurs à extraire.
| COMPARAISONS | COLONNE 2 | COLONNE 3 |
| Numéro du modèle | Nom du modèle | Prix du modèle |
| 0 | ||
| 1 | CLIO | 50000 |
| 2 | FIESTA | 60000 |
| 3 | PEUGEOT 306 | 70000 |
| 4 | XANTIA | 80000 |
| 5 | SAFRANE | 110000 |
Les 3 formules pour extraire les informations se présentent ainsi :
- recherchev(valeur recherchée;table de recherche;colonne à extraire)
- rechercheh(valeur recherchée;table de recherche;ligne à extraire)
- recherche(valeur recherchée;colonne de comparaison;colonne à extraire)
Exemples :
? RECHERCHEV(3;TABLE;2) donnera la valeur de la ligne 3 et de la colonne 2 soit PEUGEOT 306.
? RECHERCHEV(4;TABLE;3) donnera la valeur de la ligne 4 et de la colonne 3 soit 80000.
Objectif : Faire apparaître automatiquement sur un bon de commande les informations du véhicule que l'on souhaite
Exercice :
| BON DE COMANDE | |||
| Numéro du véhicule choisie : | |||
| Désignation du véhicule : | |||
| Prix du véhicule : | |||
| Adresse de la cellule où est saisi le code de recherche | Nom de la table de recherche, (ou adresse de ses cellules si aucun nom n'a été défini) | Numéro de la colonne de la table où se trouve la donnée à extraire (le nom du modèle est dans la 2ème colonne de la table nommée Véhicules…) |
| Les "arguments" de la fonctionRechercheV recherchev(critère de recherche;table de recherche;numéro de colonne) | ||
| ADRESSE DE LA CELLULE où est saisi le code de recherche (il doit correspondre aux codes qui se trouve dans la première colonne de la table de recherche) | PLAGE DE CELLULES comprenant la colonne de comparaison + la (ou les) colonne(s) des données à extraire (si cette plage est nommée, utiliser le nom…) | NOMBRE qui indique le rang de la colonne ou de la ligne de la table de recherche où se trouve la donnée à extraire |
?La formule recherchev s’utilise lorsque la table de recherche est construite en colonne (h signifie horizontal, v = vertical)
?La formule rechercheh s’utilise lorsque la table de recherche est construite en ligne (h signifie horizontal, v = vertical)
?La formule recherche s’utilise lorsque la colonne de comparaison et la colonne des données à extraire ne forment pas un tableau, la longueur de chaque colonne doit être identique, (elles peuvent même être dissociées sur des feuilles de calcul différentes, ou même des classeurs différents…).
OBJECTIF : A partir d’un fichier « Clients » et d’un fichier « Articles », remplir de manière
automatique, la facture en saisissant uniquement le numéro du compte client et
le numéro du code article.
La première ligne des données doit être vide, elle permettra de mettre à blanc la facture.
Rendez votre facture plus efficace.
* Les remises seront indiquées dans la feuille « articles » en fonction du client inscrit dans la facture.
ObjectifS : Utiliser la condition (fonction si) et la concaténation (fonction concatener) pour extraire dans le tableau Coût au km le nom du mois où les dépenses ont été les plus importantes.
1ÈREPHASE :
Dans une colonne supplémentaire à la droite de la colonne "total mois", il faut afficher le nom du mois sous condition : le montant dépensé ce mois-là doit être identique au montant maximum précédemment calculé en bas du tableau. Si la condition n'est pas respectée, il ne faut rien afficher.
Vous pouvez définir des noms, ou utiliser les références relatives… Il faudra de toutes façons pouvoir recopier la formule de condition pour chacun des 12 mois de l'année.
Plusieurs formules sont possibles :
? avec des noms : =si(total_mois=mois_maxi,mois;"")
? avec des référence de colonnes =si(E:E=mois_maxi;A:A;"")
? etc.
2ÈMEPHASE :
Le nom du mois ainsi affiché dans la colonne F doit être reporté à droite de la cellule contenant le montant du mois maximum.
La CONCATÉNATION consiste à mettre bout à bout dans une seule cellule le contenu de plusieurs cellules.
Comme pour l'addition, qui s'effectue avec une fonction (somme) ou un opérateur (+), ou la multiplication (fonction produit et opérateur *) la concaténation peut s'effectuer avec une fonction (concatener) ou un opérateur (&) :
? =CONCATENER(1ère cellule;….;dernière cellule)
? =1ère cellule&….&dernière cellule
| A | B | C | D | E | F | G | |
| 1 | MOIS | Kilomètres | Coût essence | Coût garage | Total mois | ||
| 2 | Janvier | 1400 F | =SI( E2=mois_maxi;A2;"") | ||||
| … | … | … | Recopier la formule jusqu'à décembre | ||||
| 13 | Décembre | 860 F | |||||
| … | Mois maxi | 2 250 F | =CONCATENER(F2;F3;…;F13) | ||||
| … | Mois mini | 772 F | |||||
4ÈMEPHASE :
De la même manière, extrayez dans la colonne G le nom du mois où les dépenses ont été les plus faible et reportez-le dans la cellule à droite du montant du mois mini.
5ÈMEPHASE :
Rendez ces colonnes non imprimables en les masquant :
COMMENT MASQUER UNE COLONNE
Ou bien
?De la même manière, essayez de masquer une ou des lignes
?On peut aussi masquer des feuilles entières
COMMENT RETROUVER UNE COLONNE (OU UNE LIGNE) MASQUÉE
Ou bien
?toutesles colonnes et les lignes masquées seront à nouveau affichées.
Rendez ces colonnes non imprimables en créant une zone d'impression:
Une macro-commande vous permet d’automatiser n’importe quelle action, par exemple, passer de paysage à portrait rapidement, choisir des commandes, sélectionner des options dans les boîtes de dialogue, saisir des données dans les feuilles de calcul, etc…
Vous pouvez aussi créer des fonctions personnalisées qui exécutent des calculs spécialisés.
EXEMPLE: vous allez créer une macro pour afficher les formules d’un tableau:
1.LANCER L’ENREGISTREMENT D’UNE MACRO:
Vous êtes en position ENREGISTREMENT (affiché dans la barre d’état )
Toute action est désormais enregistrée.
Vous devez alors effectuer uniquement les opérations qui doivent être enregistrées dans la macro-commande: si vous faites une fausse manœuvre, elle sera enregistrée dans la macro et vous devrez supprimer la macro et tout recommencer…
Pour notre exemple:
2.ARRÊTER L’ENREGISTREMENT:
? Si vous avez fait des erreurs pendant l’enregistrement de votre macro :
3.VÉRIFIER QUE VOTRE MACRO FONCTIONNE:
? Si vous avez oublié de saisir un raccourci, vous pouvez toujours lancer votre macro en cliquant Outils>Macro>Macros, sélectionner la macro puis Exécuter
? Créez unemacro inverse de la précédente :affiche_calculs
(raccourci CTRL-MAJ-C)
? Créez unemacro qui centre une sélection quelconque sur plusieurs colonnes.
4.AFFECTER UNE MACRO À UN BOUTON:
? Vous pouvez maintenant exécuter la macro en cliquant sur le bouton
? Insérez des images et affectez-leur vos macros.
5.POUR STOPPER L’EXÉCUTION D’UNE MACRO:
Si vous avez enregistré dans une macro une action gênante ou absurde, la macro fait n’importe quoi, ou pire elle ne s’arrête plus !
exemple : le N° de client le plus cité dans la liste des commandes
?cellule contenant le rang dans la liste =choisir(A1; Paris ; Lyon ; Marseille
s'il y a 2 dans A1 vous obtenez Lyon
=INDEX(liste;NBVAL(liste);1)
Pour compter des cellules vides
(plage) Compte les cellules vides
(plage)*3 compte les cellules vides et
multiplie le résultat par 3
(plage;critère)*7 multiplie le nombre de cellules
répondant au critère par 7
(plage;« P »)*7 le nombre de cellules contenant
l’information « p » sera multiplié
par 7
Pour compter le nombre de données différentes
=sommeprod((liste;liste identique))
=sommeprod((a1:a10;a1:a10))
Objectif:Utiliser les outils de contrôle de formulaire pour afficher facilement des informations.
Le choix d'une information permet d'obtenir un chiffre qui peut être stocké dans une cellule et servir à des formules.
1) Cliquez la barre développeur
2) Dans le panneau contrôles cliquez insérer
3) Cliquez l’outil toupie et dessinez dans la feuille
4) Cliquez avec le bouton droit sur cet objet
1) Cliquez Format d'objet onglet contrôle
2) Renseigner les éléments suivants
3) Valeur courante (celle qui sera affichée par défaut)
4) Valeur mini
5) Valeur maxi
6) Changement de Pas (valeur de variation lorsque l'on clique sur les flèches ?? du compteur).
7) Cellule liée (Affiche la valeur choisie)
1) Dans le panneau contrôles cliquez insérer
2) Cliquez l’outil Zone de liste déroulante et dessinez dans la feuille
3) Cliquez avec le bouton droit sur cet objet
1) Cliquez Format d'objet onglet contrôle
2) Cliquez dans Plage d'entrée
3) Sélectionnez dans la feuille "clients" la plage de cellules contenant les noms des entreprises
4) Cliquez dans Cellule liée
5) Sélectionnez dans la feuille une cellule vide (c'est dans cette cellule que sera stocké le chiffre correspondant à l'information choisie.
SAINTONGE INFORMATIQUE FORMATION EXCEL5&2.DOC 1
1) Cliquez OK
2) Cliquez une cellule quelconque pour enlever la sélection
3) Cliquez sur la flèche de la zone de liste, choisissez une information et observez:
1) A partir du numéro de cette cellule et avec une formule de recherche, vous pouvez faire apparaître toutes les informations de votre fichier
2) Exemple : Dans votre facture, saisissez dans la zone N° de Compte client, une formule de Recherche qui permettra avec le N° obtenu par la liste d'afficher le N° du Client.
3) Choisissez dans la zone de liste une autre valeur et Observez
1) Sélectionnez l'outil
2) Cliquez Bouton droit sur l'outil
3) Cliquez menu Format d'objet
4) Cliquez l'onglet Propriétés
EXCEL
VÉRIFICATION DES ACQUIS
OBJECTIFS
? MODIFIER LA BASE DE DONNÉES POUR QU'ELLE PUISSE SERVIR DE TABLE DE RECHERCHE
? INSTALLER DANS LE BULLETIN INDIVIDUEL LES FORMULES DE RECHERCHE NÉCESSAIRES : EN FONCTION DU CODE DE L'ÉLÈVE, VOUS OBTIENDREZ SON NOM, SON PRÉNOM, ET SES NOTES RESPECTIVES DANS LES DIFFÉRENTES MATIÈRES
THÈME: Construire un bulletin de notes individuel automatique à partir des données du tableau de notes créé lors du premier test.
Vous imprimerez le tableau en précisant :
Vous disposez d'une seule feuille pour l'impression, aussi n'hésitez pas à vérifier très souvent l'aperçu avant impression
TEMPS IMPARTI: 1 heure 30
INDICATIONS
? Afficher la barre d'outils Formulaires
? Cliquer sur l'icône Compteur et dessiner le compteur dans la feuille de calcul en faisant un cliqué-glissé
? Modifier le format de contrôle du compteur :
- cellule liée=cellule contenant le code de l'élève ;
- valeur maxi=nombre total d'élèves.
Exemple 1 Exemple 2
Objectif: interdire la saisie de certaines valeurs dans un tableau
Exemple: on gère une bibliothèque de prêt multimédia ; si on ne dispose que de 100 livres à prêter, le nombre cumulé des différents emprunts ne doit pas pouvoir dépasser ce nombre.
1 mars 11
