Cours Excel Avance
-
Upload
saadani-riadh -
Category
Documents
-
view
255 -
download
3
Transcript of Cours Excel Avance
-
8/14/2019 Cours Excel Avance
1/48
Excel avanc
Frdric Gava (MCF)[email protected]
LACL, btiment P2 du CMC, bureau 221Universit de Paris XII Val-de-Marne
61 avenue du Gnral de Gaulle94010 Crteil cedex
-
8/14/2019 Cours Excel Avance
2/48
2/48
Rappelset complments
sur Excel
-
8/14/2019 Cours Excel Avance
3/48
3/48
Quest-ce quune formule ?
Nom de la cellule Formule de la cellule
Rsultat / valuation de la formule
-
8/14/2019 Cours Excel Avance
4/48
4/48
Syntaxe dune formule (1)
Oprateur daffectation
Nepas
oublie
r!!
-
8/14/2019 Cours Excel Avance
5/48
5/48
Syntaxe dune formule (2)
Rfrences aux cellules
Oprandes (donnes traiter)
Constantes
Plages cellules
-
8/14/2019 Cours Excel Avance
6/48
-
8/14/2019 Cours Excel Avance
7/48
7/48
Nommage dune cellule
La cellule A3 est maintenant la cellule note_globale
Nouveau nom
Attention: Pas despace !(Vous pouvez remplacer les espaces par _)
-
8/14/2019 Cours Excel Avance
8/48
8/48
Relative versus absolue (1)
Problme ?
-
8/14/2019 Cours Excel Avance
9/48
9/48
Relative versus absolue (2)
Formule en rfrence relative
-
8/14/2019 Cours Excel Avance
10/48
10/48
Relative versus absolue (3)
On tend la cellule sur les autres cellules.
-
8/14/2019 Cours Excel Avance
11/48
11/48
Erreur !
Relative versus absolue (4)
La rfrence concernant la taxe est aussi
descendue de deux cellules.
-
8/14/2019 Cours Excel Avance
12/48
12/48
Relative versus absolue (5)
tape 1 : On renomme la cellule pourla rendre absolue
Unesolu
tion
-
8/14/2019 Cours Excel Avance
13/48
13/48
Relative versus absolue (6)
tape 2 : On tape la formule correspondanteau calcul.
tape 3 : On tend la cellule sur les autres cellules.
Note : On aurait put utiliser la rfrence absolue $C$6
= B2 + B2 * Taxe
-
8/14/2019 Cours Excel Avance
14/48
14/48
Oprateurs classiques (1)
arithmtiques :+ Addition 3 + 4 ou A3 + B5- Soustraction 2 1 ou A4 B7* Multiplication 3 * 4 ou A5 * B8
/ Division 10/5 ou A1/B7
^ Puissance 2 ^ 3 (2*2*2) ou A1 ^ B7de comparaison :< et > Infrieur et Suprieur= Infrieur ou gal et Suprieur ou gal
= et gal et Diffrent (non gal)Exemple: A3 >= 100
-
8/14/2019 Cours Excel Avance
15/48
15/48
Oprateurs (2)
de texte :& Concatnation
-
8/14/2019 Cours Excel Avance
16/48
16/48
Les erreurs de calcul
##### : Entrer une date sous forme de XX moisXXXX et rduire la largeur de la colonne !
#DIV/0 : Diviser une valeur par la valeur dune
cellule vide (ou par 0) !#NOM : Faire Somme( : xxxx) !
#NOMBRE : Faire 1000 1000 ! (dpassement de capacit)
#VALEUR : Essayer de calculer la valeur absolue dun argumentde type texte !
-
8/14/2019 Cours Excel Avance
17/48
17/48
Formules avances
-
8/14/2019 Cours Excel Avance
18/48
18/48
Fonctions de Recherche (1)
Renvoie une valeur provenant d'une plageRecherche dune ligne : RECHERCHEVRecherche dune colonne : RECHERCHEH
Syntaxe :RECHERCHEV(valeur;plage;num_colonne)
valeur : Valeur chercher (Rfrence ou constante)
plage : la plage o seffectue la recherche
Num_colonne : le numro de la colonne dont la valeurdoit tre renvoye
-
8/14/2019 Cours Excel Avance
19/48
19/48
Fonctions de Recherche (2)
Renvoie dune valeur provenant d'une plage
Recherche la ligne de Benoit dans cette plage et donne la note.
= RECHERCHEV ( Benoit ; A1:B6 ; 2 ; FAUX)
Dans la plage A1:B6 :-Colonne 1 : nom
-Colonne 2 : note
-
8/14/2019 Cours Excel Avance
20/48
20/48
Fonctions de Recherche (3)
=RECHERCHEV( B1 ; D3:F6 ; 3 ; VRAI)
Colonne1 Colonne3
Si le tableau est tri
-
8/14/2019 Cours Excel Avance
21/48
21/48
Autres recherchesIndex(tableau;no_lig;no_col)
La fonction Index renvoie la donne situe l'intersection de la ligne et de lacolonne du tableau (plage) de recherche.
Exemple Index(A1:D8;3;2) retourne la valeur de la cellule de troisime ligne etdeuxime colonne cest--dire B3
EQUIV(valeur_cherche;tableau_recherche;type) Renvoie laposition relative de la valeur_cherche dans le tableau o on effectue
la recherche. Type est le nombre -1, 0 ou 1 qui indique commentExcel doit procder pour comparer l'argument valeur_cherche auxvaleurs de l'argument tableau_recherche :
Si la valeur de l'argument type est 1, la fonction EQUIV trouve la valeur la plus
leve qui est infrieure ou gale celle de l'argument valeur_cherche.Si la valeur de l'argument type est 0, la fonction EQUIV trouve la premirevaleur exactement quivalente celle de l'argument valeur_cherche.Si la valeur de l'argument type est -1, la fonction EQUIV trouve la plus petitevaleur qui est suprieure ou gale celle de l'argument valeur_cherche.
-
8/14/2019 Cours Excel Avance
22/48
22/48
Fonctions Conditionnelles
Fonction SISI(test ; valeur_si_vrai ; valeur_si_faux)
-
8/14/2019 Cours Excel Avance
23/48
23/48
Les filtres automatiques
-
8/14/2019 Cours Excel Avance
24/48
24/48
Excel en tant que mini-SGBD
Nous allons voir ici, comment utiliser Excel en tant quemini-SGBD : utiliser les feuilles en tant que table (relation)Nous allons donc pouvoir fusionner des donnes, lesslectionner, etc. (nous avions dj vue comment trier desdonnes).Vous remarquerez immdiatement les limites ; avec Access(le SGBD dOffice), ces limites seront dpasss (notamment
par les requtes SQL). Nous verrons cela bientt mais avecun peut de temps car cela nest pas si vident que cela (fautbien terminer par le plus dure)
-
8/14/2019 Cours Excel Avance
25/48
25/48
Filtres automatiques (1)Dans une feuille (ou une plage de donnes) en tant
que table, on peut slectionner (et ne pas faire
apparatre les autres) certaines donnes.Exemple dune table en Excel:
Cela correspond la table
Enseignant(Nom,Prnom,Statue)
Noms des colonnes
-
8/14/2019 Cours Excel Avance
26/48
26/48
Filtres automatiques (2)Pour chaque colonne, on peut slectionner quelles sont leslignes qui nous intresse
Comme, on peut moduler ces choix, on parle alors detableau dynamique (en SQL, nous verrons que celacorrespondra la clause WHERE)
Exemple, slectionner que les MCF :On clique dans le menu DonnesFiltreFiltre Automatique
On obtient :
Puis, on clique sur la colonne Statue, et on choisit MCF. Onobtient alors
-
8/14/2019 Cours Excel Avance
27/48
27/48
Filtres automatiques (3)
Dmo Flash trouve sur http://top-assistante.com
Reproduction copyrightDouble-clic sur licne pour avoir la dmo
Filtre1.swf
-
8/14/2019 Cours Excel Avance
28/48
28/48
Filtres automatiques (4)On peut aussi choisir des filtres plus labors
Pour cela, on clique sur une des colonnes puis onchoix personnalis :
Il existe plusieurs variantes comme commencepar , se termine par etc
-
8/14/2019 Cours Excel Avance
29/48
29/48
Sous-totaux (1)Comme on peut slectionner des lignes du tableux
(filtre), il est possible de faire des calculs sur les
sous parties de ce mme tableauPrenons cet exemple :
Nous souhaitons les ventes totalesPour chaque entreprise
Notez quil faut que les donnes soient tries
Puis faisons les totaux des ventes de chaqueentreprise.Pour cela, il faut cliquez dans le menu Donne
puis slectionner Sous-totaux
-
8/14/2019 Cours Excel Avance
30/48
30/48
Sous-totaux (2)Nous pouvons alors choisir :
Le champ A chaque changement de : onslectionne sur quel champs nous souhaitons un soustotal. Ici par entreprise. Utiliser la fonction : plusieurs fonctions sont notre disposition mais celle que nous allons utiliserest bien entendu Somme Ajouter un sous-total : ici, il nous faudra cocher Vente pour totaliser les ventes par entreprise Remplacer les sous-totaux existants : il estprfrable de laisser cette case coche. Si vous avezdj utilis la fonction Sous-totaux pour obtenird'autres rsultats, ceux-ci seront donc effacs aubnfice des nouveaux calculs.Saut de page entre les groupes : cette option permet
d'obtenir, automatiquement, une page par entreprise.Soyez prudents dans son utilisation. Ici, nous n'avonsque 9 entreprise (donc au minimum 9 pages), mais sinous en avions eu 150, cela n'aurait pas t sansincidence !
Synthse sous les donnes : les rsultats sont groups chaque changement d'entreprise
-
8/14/2019 Cours Excel Avance
31/48
31/48
Filtres labors (1)
la grande diffrence entre un filtre automatique et un filtre labor estque ce dernier doit tre saisi manuellement ; Pour autant, pas depanique : ce n'est pas franchement compliqu.Activez la commande Donnes - Filtre labor
On peut activez Copier vers un autre emplacement si on dsireconserver intact les donnes d'origine sdans le champ Plages, slectionnez vos donnesdans le champ Zone de critres, slectionnez votre zone de critre.
Attention : ne slectionnez que les lignes non vides de votre zone decritres. Ceci est extrmement important : si vous slectionnez uneligne de critres vierge, Excel considrera que vous souhaitezslectionner l'intgralitde votre base ;
cochez Extraction sans doublon si vous ne souhaitez pas avoirplusieurs fois le mme enregistrement.
-
8/14/2019 Cours Excel Avance
32/48
32/48
Filtres labors (2)Les filtres manuels sont des cellules contenant
le filtre. On les appels zones de critres Il est souvent conseiller davoir une zone de critres
(cellules content les filtres) puis une zone dedonnes (extraction)Plusieurs critres sur une mme ligne : ETPlusieurs critres sur des lignes diffrentes : OUExemple :
-
8/14/2019 Cours Excel Avance
33/48
33/48
Les critres des filtres labors
-
8/14/2019 Cours Excel Avance
34/48
34/48
Les tableaux croiss
dynamiques
-
8/14/2019 Cours Excel Avance
35/48
35/48
ObjectifBut par lexemple :
A partir d'un tableau recensant par exemple toutes lescommandes de l'anne, les tableaux croiss dynamiquespermettent d'obtenir, des tableaux statistiques.Voici, titre d'illustration, quelques exemples dersultats qu'il est possible d'obtenir
le nombre ou le montant des commandes pour chaque client outype de produits (en valeur absolue ou bien mme en %) ;le montant ou le nombre de commandes trait par chaque
employ ;le montant gnr par chaque produit vendu, soit dans l'anne,soit par mois ou par trimestre.
Il ne faudra pas confondre les tableaux croiss et lesSGBD
-
8/14/2019 Cours Excel Avance
36/48
36/48
ComparaisonA le diffrence des SGBD, les donnes proviennent duneseule et mme table (feuille) ;Dans les SGBD, les donnes peuvent, pour des raisons
defficacit et de non redondance des informations, provenirde diffrentes tables et mme tre slectionns en chaquetable (nous verrons ces possibilits dans les prochains coursavec les requtes SQL)
Par contre, les donnes seront donnes de manirebrutecest--dire sous la forme dune table. Avec lestableaux dynamiques, les donnes seront prsents sous laforme de tableaux Excel (avec donc toute lesthtisme quipeut all avec)On a donc
Access pour retrouver/slectionner ces donnes
Excel pour les traiter et les visualiser correctement
l
-
8/14/2019 Cours Excel Avance
37/48
37/48
But gnral
Modlisationen Merise
Tablesen Access
Tableauxen Excel
Feuilles de calculEn Excel
E/ARelations Requtes SQL Tableaux
dynamiques
Formules etmacros ExcelEntrer les
donnes
C i d bl i
-
8/14/2019 Cours Excel Avance
38/48
38/48
Cration dun tableau croisPour dbuter, vous devez donc disposer d'un fichier
de donnes. Exemple :
Ensuite, on slection le menu Donnes , rapport de tableaux croiss dynamiques Nous passons la cration du croisement (forme de
dauto-jointure)
i d bl i ( )
-
8/14/2019 Cours Excel Avance
39/48
39/48
Cration dun tableau crois (1)la 1re tape vous invite slectionner le fichier source exploiter.Il peut s'agir
d'une liste ou base de donnes Excel (notre cas)source de donnes externes ; cette option permet de rcuprer des informationsdans des fichiers non Excel comme par exemple des rsultats de requtes SQL
en Accessdes plages de feuilles de calcul avec tiquettes (valable si vous avez utilis lafonction Dfinir un nom),ou un autre rapport de tableau ou de graphique crois dynamique.
La seconde zone d'option vous permet d'laborer soit un tableau, soitun graphique dynamique. Notre choix s'est port sur un tableau.Cliquez sur Suivant
C i d bl i ( )
-
8/14/2019 Cours Excel Avance
40/48
40/48
Cration dun tableau crois (2)
Il vous faut slectionner votre fichier de donnes. Si
la zone contenu dans le champ Plage n'est pas labonne, effacez son contenu et activez votre feuillede calcul. Slectionnez ensuite l'intgralit de votre
tableau ;cliquez sur Suivant
C i d bl i ( )
-
8/14/2019 Cours Excel Avance
41/48
41/48
Cration dun tableau crois (3)Choisissez, un emplacement pour votre tableau
crois dynamique. Il est conseill d'opter pour uneNouvelle feuille.Bien qu'il soit possible de cliquer directement sur le
bouton Terminer, puis de procder ensuite
l'amnagement de vos donnes, il est recommandde prendre l'habitude de cliquer sur le boutonDisposition...
C ti d t bl i ( )
-
8/14/2019 Cours Excel Avance
42/48
42/48
Cration dun tableau crois (4)
Exemple : nom des socits dans lignes et PrixTotal dans
donnesPar dfaut nous avons Somme des PrixTotal mais nouspouvons aussi avoir :
MOYENNENOMBREMIN ou MAX
C ti d t bl i ( )
-
8/14/2019 Cours Excel Avance
43/48
43/48
Cration dun tableau crois (5)Dans notre cas :
Puis on accepte la cration du tableau et on obtient :
C ti d t bl i (6)
-
8/14/2019 Cours Excel Avance
44/48
44/48
Cration dun tableau crois (6)Si on double-clicsur le champs NomProduit :
Et on peut faire lamme chose pourchacun des autreschamps
Attention, ne pasoublier de cliquersur le bouton ! si on
modifie les donnes(actualiser)
A i ti
-
8/14/2019 Cours Excel Avance
45/48
45/48
Animation
Tcd1.swf
Dmo Flash trouve sur http://top-assistante.com
Reproduction copyrightDouble-clic sur licne pour avoir la dmo
C ti d t bl is (7)
-
8/14/2019 Cours Excel Avance
46/48
46/48
Cration dun tableau crois (7)
Si on prend le tableau suivant :
On obtient alors :
Plus de dtails
-
8/14/2019 Cours Excel Avance
47/48
47/48
Plus de dtails
Vous trouverez plus de dtails et doptionssupplmentaires surregrouper les donnes par dates
amliorer la prsentation dun tableau croisetc
ladresse suivante :http://www.top-assistante.com/
PECA
-
8/14/2019 Cours Excel Avance
48/48
A la semaine prochaine !