Transcript Excel 2003

Le tableur
TIC – S2
Organisation et traitement
donné
données (tableaux)
! Automatisation des calculs
! Outil d’
d’analyse et simulation
! Logiciels :
!
1
TC :: IUT Montpellier
Nancy Rodriguez
!
OO Calc
!
Excel
de
NRD - 2010
Microsoft Excel
2
Le classeur
Barre des formules
Colonnes
!
!
!
Fichier (*.xls)
Il peut contenir plusieurs feuilles de
calcul ( classification)
Il est possible d’
d’y organiser diffé
différents
types d’
d’information
Cellules
Lignes
NRD - 2010
3
NRD - 2010
4
1
Gestion des feuilles
Les cellules
!
!
Clic droit sur
l ’onglet de la feuille
concernée
!
!
NRD - 2010
Cellule active
Peut contenir une valeur
ou une formule plus un
format
Format : hauteur,
largeur, couleur,
bordure, mise en forme
du texte,…
Le format peut aussi
dépendre du contenu
NRD - 2010
5
Sélection
Insertion / Suppression
D’une ligne ou colonne
! D’un ensemble de lignes ou
colonnes
! D’une cellule
! D’un ensemble de cellules
(consé
(consécutives ou non)
!
!
NRD - 2010
!
7
6
Sélectionner les lignes ou colonnes à
supprimer
Menu contextuel ou menu Insertion
NRD - 2010
8
2
Mise en forme des cellules - 1
Mise en forme de cellules - 2
Barre
d’outils
Format
ou
NRD - 2010
NRD - 2010
9
Mise en forme de cellules - 3
10
Mise en forme de cellules - 4
!
Format > Mise en forme conditionnelle
Mettre en
forme la
feuille
« Mise en
forme »
NRD - 2010
11
NRD - 2010
12
3
Figer / libérer les volets
!
Modes
Fenêtre > figer (libé
(libérer) les volets
sélection
recopie
déplacement
NRD - 2010
NRD - 2010
13
Les séries - 1
Les séries - 2
Glisser la poignée de recopie vers le bas
Ctrl + Glisser la poignée de recopie vers le
bas
NRD - 2010
15
NRD - 2010
14
16
4
Les séries - 3
Les séries - 4
Sélectionner les deux premiers
éléments et glisser la poigné
poignée de
recopie vers le bas
NRD - 2010
!
17
Les tris
!
!
!
Édition > Remplissage >Sé
>Série
!
Série : en colonnes
!
Type : linéaire
!
Valeur du pas : 3
!
Dernière valeur : 45
Compléter la
feuille « Listes »
NRD - 2010
18
Les filtres automatiques - 1
Copier les valeurs de
la feuille Mise en forme
dans la feuille Tri
Sélectionnez TOUTES
les cellules
Menu Donné
Données > Trier
NRD - 2010
Donné
Données > Filtres > Filtre Automatique
19
NRD - 2010
20
5
Les filtres automatiques - 2
!
Les filtres automatiques - 3
Afficher uniquement les hommes
!
!
NRD - 2010
NRD - 2010
21
Les filtres automatiques - 4
!
22
Le Publipostage
Afficher uniquement les personnes
dont le nom commence par M
!
!
NRD - 2010
Afficher uniquement les personnes qui
habitent à Montpellier
Afficher les personnes qui habitent à
Montpellier ou à Toulouse
23
Combiner un document source (Word
ou Publisher) avec une liste de
destinataires (Excel, Access)
Générer automatiquement autant de
documents que de destinataires
NRD - 2010
24
6
Le Publipostage
!
Le Publipostage : Publisher - 1
Documents à fusionner :
!
Invitation Publisher (invitation.pub)
!
Liste de destinataires sur Excel
(feuille destinataires.xls)
NRD - 2010
!
!
25
NRD - 2010
26
Le Publipostage : Publisher - 3
Le Publipostage : Publisher - 2
NRD - 2010
A partir du document Publisher
Outils / Fusion de catalogue et
publipostage / Assistant Fusion de
catalogue et publipostage
27
NRD - 2010
28
7
Le Publipostage : Publisher - 4
Le Publipostage : Publisher - 5
!
!
Vérifiez le ré
résultat
Cliquez sur Suivante : Fin de la fusion
!
NRD - 2010
29
NRD - 2010
Sauvegardez
la nouvelle
composition
30
A vos claviers !
!
TP Publipostage
NRD - 2010
31
8
Les références
TIC – S2
!
2
!
TC :: IUT Montpellier
Coordonnées d’une cellule dans la
feuille de calcul B4
La référence d’une plage de cellules :
!
Nancy Rodriguez
!
B4 : C5
Essentielles pour la copie
!
!
!
Référence relative : E2
Référence absolue : $E$2
Référence semi-relative : $E2 ou E$2
NRD - 2010
Les formules
Les formules 1
!
Trouver une valeur en réalisant des calculs
!
Constituée de :
!
!
2
•
Symbole d’affectation =
Opérandes : valeurs constantes, références de
cellules, noms de cellules
!
Opérateurs : de comparaison (= > >= < <= <>)
arithmétiques (+ - * / ^)
!
De fonctions
NRD - 2010
•
•
Cliquez sur la cellule dans laquelle
vous souhaitez voir apparaître le
résultat
Dans la barre de formule cliquez sur =
Écrivez la formule
•
3
Pour faire référence à une cellule, cliquez sur
celle-ci ou saisissez sa référence
NRD - 2010
4
1
Les formules 2
Les formules 3
!
Pour répéter la formule, glissez la
poignée de recopie
CTRL
CTRL ++ ""
NRD - 2010
NRD - 2010
5
Les fonctions
6
Formules arithmétiques
!
Copier
Série + Collage
Spécial >
Transposé
Compléter
Compléter
Formules
Formules
NRD - 2010
Feuille TableMultiplication
7
NRD - 2010
8
2
Formules et références - 1
!
Formules et références - 2
Trouver la bonne formule!
!
!
!
Copie
!
Références absolues?
Références mixtes !
Nous souhaitons :
« Une ligne fixe sur la même colonne
* une colonne fixe sur la même ligne »
« Quatre cellules en haut sur la même
colonne * Deux cellules à gauche sur
la même ligne »
NRD - 2010
"
☺
NRD - 2010
9
Les fonctions
10
Formules logiques - 1
!
Feuille Fonctions logiques
!
SI(condition;opération à effectuer si VRAI;
Opération à effectuer si FAUX)
Compléter
Compléter
la
la feuille
feuille
NRD - 2010
11
NRD - 2010
12
3
!
Graphiques
Personnaliser le graphique - 1
Feuille Données graphiques
!
NRD - 2010
14
A vos claviers !
Série 1 Étiquettes de données puis
bouton Format
NRD - 2010
NRD - 2010
13
Personnaliser le graphique - 2
!
Affichage > Barre d’outils > Graphique
!
15
TP Excel 2.pdf
NRD - 2010
16
4
Au TP Précédent…
TIC – S2
3
TC :: IUT Montpellier
Nancy Rodriguez
NRD - 2010
Formules et fonctions
Format conditionnel
NRD - 2010
2
3
NRD - 2010
4
1
Graphiques - 1
!
Graphiques - 2
!
Feuille Bulletin
NRD - 2010
Fonction OU
VRAI si toutes les conditions sont rempliées
OU(condition1;condition2;...) :
!
!
6
ET(condition1;condition2;...) :
!
!
NRD - 2010
5
Formules logiques
!
Affichage > Barre d’outils > Graphique
VRAI si au moins une condition est remplie
Imbrication de fonctions :
!
Une fonction est utilisée comme paramètre
d’une autre fonction
NRD - 2010
7
NRD - 2010
8
2
Fonctions SI et OU
Regroupement des données
!
Fonction NB.SI
!
!
Fonction SOMME.SI
!
!
Additionner les cellules qui répondent à un
certain critère
Les sous-totaux
!
NRD - 2010
Compter les cellules qui répondent à un certain
critère
Regrouper les cellules et réaliser des calculs sur
les groupes
NRD - 2010
9
Fonction NB.SI
10
Fonction NB.SI
NB.SI(plage_de_cellules;critère)
NRD - 2010
11
NRD - 2010
12
3
Fonction SOMME.SI
Résultat
SOMMME.SI(plage_de_cellules;critère;
somme_plage)
B6)
NRD - 2010
Les sous-totaux
!
!
!
NRD - 2010
13
14
Les sous-totaux
Grouper les données et réaliser
ensuite de calculs
Première étape : Trier les données
!
Trier le tableau statVentesPays par pays
!
Menu Données > Trier
Seconde étape : Réaliser les soustotaux
!
Menu Données > Sous-totaux
NRD - 2010
15
NRD - 2010
16
4
Les sous-totaux
A vous !
!
NRD - 2010
17
TP Excel 3.pdf
(classeur
exercicesTP3.xls)
NRD - 2010
18
5
Précédemment…
TIC – S2
!
4
!
TC :: IUT Montpellier
Nancy Rodriguez
!
Stocker tous les valeurs nécessaires
à un calcul dans des cellules
Une formule s’écrit une fois puis se
recopie par étirement …
Le caractère « $ » bloque la
coordonnée (ligne et/ou colonne)
devant lequel il est placé
NRD - 2010
2
Les fonctions
Précédemment…
!
NRD - 2010
Les fonctions
NRD - 2010
3
4
1
Précédemment…
!
Fonctions mathématiques
!
!
somme, moyenne, max, min, nb
si, ou, et
Fonctions avancées
!
!
Quelle formule faut-il pour trouver
les points obtenus par Jacques lors
de la troisième partie ? Ou par Lucile
dans la première partie ?
Fonctions logiques
!
!
Un nouveau problème
somme.si, nb.si
Imbrication de fonctions
NRD - 2010
NRD - 2010
5
Fonctions de recherche
!
!
Fonction RECHERCHEV
Limitation de la fonction SI
!
6
RECHERCHEV(valeur_cherchée;table_
matrice;no_index_col;valeur_proche)
Formules longues et compliquées lorsque
plusieurs SI sont imbriquées
!Utilisée
par Excel s'il ne trouve pas la
valeur exacte
Solution : Utiliser les fonctions de
recherche pour retrouver
l’information dans un tableau
!Deux
NRD - 2010
possibilités pour valeur_proche :
!
VRAI (1) : prendre la valeur inférieure la plus
proche (intervalle)
!
FAUX (0) : message d’erreur (#N/A)
NRD - 2010
7
8
2
Solution
RECHERCHEV : Exercice 1
Quelle formule faut-il insérer pour calculer le
CA TTC et les commissions des vendeurs ?
exercices4.xls
Copyright : Gaetan Mourmant - Tous droits réservés
NRD - 2010
NRD - 2010
9
Analyse
10
Solution
=C5*(1+RECHERCHEV(B5;Source;2;FAUX))
FAUX
•Valeur à chercher
•Tableau de
données
•Colonne à
récupérer
2
NRD - 2010
NRD - 2010
11
12
3
RECHERCHEV : Exercice 2
Solution
Appartenance à un intervalle :
!Le paramètre valeur_proche prend la
valeur VRAI
=RECHERCHEV(B16;A20:B27;2;VRAI)
NRD - 2010
NRD - 2010
13
RECHERCHEH
!
!
!
14
Exemple RECHERCHEH
H pour horizontal
La valeur à chercher se trouve sur
une ligne du tableau de données
L’index colonne est remplacé par un
index ligne
NRD - 2010
NRD - 2010
15
16
4
A vous !
!
TP Excel 4.pdf
NRD - 2010
17
5
Point évaluation du jour
TIC – S2
!
5
TC :: IUT Montpellier
!
Nancy Rodriguez
!
!
Pratique : deux notes Excel
(individuel et binômes) plus une note
HTML (construction site Web)
Écrit : QCM
Séances TP noté : 8 et 15 mars
Tirage au sort le 8 mars pour définir
le travail individuel/en binôme pour
les deux séances
NRD - 2010
A savoir
!
!
2
Les outils d’analyse
Travail sur les comptes exam, travail à rendre sur
le lecteur boîte-aux-lettres-partiel, le lecteur
ressources reste accessible
!
!
Lecteur BALP avec uniquement des droits
d’écriture
!
La valeur cible
Le solveur
Les tableaux dynamiques
Lecteur BALP :
Coller le dossier
Lecteur exam :
Sélectionner et
copier votre
dossier
NRD - 2010
3
NRD - 2010
4
1
La valeur cible – Le solveur
La valeur cible
• Classeur exercicesTP5.xls
http://www.misfu.com/valeur-cible-solveur-excel.html
NRD - 2010
http://www.misfu.com/valeur-cible-solveur-excel.html
NRD - 2010
5
La valeur cible
6
La valeur cible
Outils >
valeur cible
NRD - 2010
7
NRD - 2010
8
2
Le solveur
!
Le solveur – une seule variable
!
Déterminer le nouveau prix de vente
permettant de réaliser un bénéfice de
350 000€
NRD - 2010
(pour l’installer : Outils > Macro complémentaire)
9
Le solveur - résultat
NRD - 2010
Outils > Solveur
NRD - 2010
10
Le solveur – deux variables
11
NRD - 2010
12
3
CA par mois et par région pour
chaque appellation
Les tableaux dynamiques
!
!
!
!
Analyse rapide d’une grande quantité de
données
Visualiser sous forme synthétique le
contenu d’un tableau
Possibilité de faire pivoter les lignes et les
colonnes
Feuille vins
!
!
!
CA par mois et par région pour chaque
appellation
CA par appellation et par région
NRD - 2010
13
CA par mois et par région pour
chaque appellation
!
Données > Rapport de tableau
croisé dynamique :
NRD - 2010
14
CA par mois et par région pour
chaque appellation
Sélectionnez (vérifiez) les données à
traiter
NRD - 2010
15
NRD - 2010
16
4
Grouper par région
Création du tableau dynamique 1
NRD - 2010
17
Moyenne de ventes par région
NRD - 2010
NRD - 2010
18
Moyenne de ventes par région
19
NRD - 2010
20
5
Mise en forme
!
Mise en forme
Barre d’outils Tableau croisé
dynamique
NRD - 2010
21
NRD - 2010
22
A vous !
!
TP Excel 5.pdf
NRD - 2010
23
6