Excel avancé - LAAS

1 DEUST-MF. UPS. Excel avanc . +. Qu'est-ce qu'une formule ? Nom de la cellule Formule de la cellule R sultat / valuation de la formule +. Syntaxe d'une formule (1). Op rateur d'affectation +. Syntaxe d'une formule (2). Op randes (donn es traiter). R f rences aux cellules Plages cellules Constantes +. Syntaxe d'une formule (3). Op rateurs (instructions). Op rateurs de calcul Fonctions +. Nommage d'une cellule La cellule A3 est maintenant la cellule note_globale . Nouveau nom Attention : Pas d'espace ! (Vous pouvez remplacer les espaces par _').

2 +. Relative versus absolue (1). Probl me ? +. Relative versus absolue (2). Formule en r f rence relative +. Relative versus absolue (3). On tend la cellule sur les autres cellules. 10/4. + 8. Relative versus absolue (4). Erreur ! La r f rence concernant la taxe est aussi descendue de deux cellules. 11/4. + 8. Relative versus absolue (5). tape 1 : On renomme la cellule pour la rendre absolue . +. Relative versus absolue (6). = B2 + B2 * Taxe tape 2 : On tape la formule correspondante au calcul. tape 3 : On tend la cellule sur les autres cellules.

3 Note : On aurait put utiliser la r f rence absolue $C$6. +. Op rateurs classiques (1). n arithm tiques : + 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 ^ B7. n de comparaison : < et > Inf rieur et Sup rieur <= et >= Inf rieur ou gal et Sup rieur ou gal = et <> gal et Diff rent (non gal). Exemple: A3 >= 100. +. Op rateurs (2). n de texte : & Concat nation +. Les erreurs de calcul n ##### : Entrer une date sous forme de XX mois XXXX et r duire la largeur de la colonne !

4 N #DIV/0 : Diviser une valeur par la valeur d'une cellule vide (ou par 0) ! n #NOM : Faire Somme(<r f rence d'une cellule> : xxxx) ! n #NOMBRE : Faire 1000 1000 ! (d passement de capacit ). n #VALEUR: Essayer de calculer la valeur absolue d'un argument de type texte ! +. Formules avanc es 16/48. +. Fonctions de Recherche (1). n Renvoie une valeur provenant d'une plage n Recherche d'une ligne : RECHERCHEV. n Recherche d'une colonne : RECHERCHEH. n Syntaxe : RECHERCHEV(valeur;plage;num_colonne). " valeur : Valeur chercher (R f rence ou constante).

5 " plage : la plage o s'effectue la recherche " Num_colonne : le num ro de la colonne dont la valeur doit tre renvoy e 18/4. + 8. Fonctions de Recherche (2). n Renvoie d'une valeur provenant d'une plage Dans la plage A1:B6 : -Colonne 1 : nom -Colonne 2 : note Recherche la ligne de Benoit dans cette plage et donne la note. = RECHERCHEV ( ''Benoit'' ; A1:B6 ; 2 ; FAUX). Fonctions de Recherche (3) 19/4. 8. Si le tableau est tri . =RECHERCHEV( B1 ; D3:F6 ; 3 ; VRAI). Colonne1 Colonne3. +. Autres recherches n Index(tableau;no_lig;no_col).

6 N La fonction Index renvoie la donn e situ e l'intersection de la ligne et de la colonne du tableau (plage) de recherche. n Exemple Index(A1:D8;3;2) retourne la valeur de la cellule de troisi me ligne et deuxi me colonne c'est- -dire B3. n EQUIV(valeur_cherch e;tableau_recherche;type) Renvoie la position relative de la valeur_cherch e dans le tableau o on effectue la recherche. Type est le nombre -1, 0 ou 1 qui indique comment Excel doit proc der pour comparer l'argument valeur_cherch e aux valeurs de l'argument tableau_recherche : n Si la valeur de l'argument type est 1, la fonction EQUIV trouve la valeur la plus lev e qui est inf rieure ou gale celle de l'argument valeur_cherch e.

7 N Si la valeur de l'argument type est 0, la fonction EQUIV trouve la premi re valeur exactement quivalente celle de l'argument valeur_cherch e. n Si la valeur de l'argument type est -1, la fonction EQUIV trouve la plus petite valeur qui est sup rieure ou gale celle de l'argument valeur_cherch e. Fonctions Conditionnelles 21/4. 8. n Fonction SI. SI(test ; valeur_si_vrai ; valeur_si_faux). +. Les filtres automatiques 22/48. +. Excel en tant que mini-SGBD. n Nous allons voir ici, comment utiliser Excel en tant que mini-SGBD : utiliser les feuilles en tant que table (relation).

8 N Nous allons donc pouvoir fusionner des donn es, les s lectionner, etc. (nous avions d j vue comment trier des donn es). n Vous remarquerez imm diatement les limites ; avec Access (le SGBD d'Office), ces limites seront d pass s (notamment par les requ tes SQL). Nous verrons cela bient t mais avec un peut de temps car cela n'est pas si vident que cela (faut bien terminer par le plus dure ). +. Filtres automatiques (1). n Dans une feuille (ou une plage de donn es) en tant que table, on peut s lectionner (et ne pas faire appara tre les autres) certaines donn es.

9 N Exemple d'une table en Excel : Noms des colonnes n Cela correspond la table Enseignant(Nom,Pr nom,Statue). + Filtres automatiques (2). n Pour chaque colonne, on peut s lectionner quelles sont les lignes qui nous int resse n Comme, on peut moduler ces choix, on parle alors de tableau dynamique (en SQL, nous verrons que cela correspondra la clause WHERE ). n Exemple, s lectionner que les MCF : n On clique dans le menu Donn es Filtre Filtre Automatique n On obtient : n Puis, on clique sur la colonne Statue, et on choisit MCF.

10 On obtient alors +. Filtres automatiques (4). n On peut aussi choisir des filtres plus labor s n Pour cela, on clique sur une des colonnes puis on choix personnalis : n Il existe plusieurs variantes comme commence par , se termine par . etc . +. Sous-totaux (1). n Comme on peut s lectionner des lignes du tableux (filtre), il est possible de faire des calculs sur les sous parties de ce m me tableau n Prenons cet exemple : Nous souhaitons les ventes totales Pour chaque entreprise . n Notez qu'il faut que les donn es soient tri es n Puis faisons les totaux des ventes de chaque entreprise.