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


2/48

2/48

Rappelset complments

sur Excel


3/48

3/48

Quest-ce quune formule ?

Nom de la cellule Formule de la cellule

Rsultat / valuation de la formule


4/48

4/48

Syntaxe dune formule (1)

Oprateur daffectation

Nepas

oublie

r!!


5/48

5/48

Syntaxe dune formule (2)

Rfrences aux cellules

Oprandes (donnes traiter)

Constantes

Plages cellules


6/48


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/48

8/48

Relative versus absolue (1)

Problme ?


9/48

9/48


Formule en rfrence relative


10/48

10/48


On tend la cellule sur les autres cellules.


11/48

11/48

Erreur !


La rfrence concernant la taxe est aussi

descendue de deux cellules.


12/48

12/48


tape 1 : On renomme la cellule pourla rendre absolue

Unesolu

tion


13/48

13/48


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


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


15/48

15/48

Oprateurs (2)

de texte :& Concatnation


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 !


17/48

17/48

Formules avances


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


19/48

19/48


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


20/48

20/48


=RECHERCHEV( B1 ; D3:F6 ; 3 ; VRAI)

Colonne1 Colonne3

Si le tableau est tri


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.


22/48

22/48

Fonctions Conditionnelles

Fonction SISI(test ; valeur_si_vrai ; valeur_si_faux)


23/48

23/48

Les filtres automatiques


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)


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


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


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


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


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


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


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.


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 :


33/48

33/48

Les critres des filtres labors


34/48

34/48

Les tableaux croiss

dynamiques


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


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


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


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 ( )


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 ( )


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 ( )


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 ( )


42/48

42/48


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 ( )


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)


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


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)


46/48

46/48


Si on prend le tableau suivant :

On obtient alors :

Plus de dtails


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


48/48

A la semaine prochaine !

Documents

Cours Excel Avance