Cours sur les fonctions et les formules de Microsoft Excel 2013
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
Opérandes (données à traiter) Références aux cellules Plages cellules
Opérateurs (instructions)
Opérateurs de calcul Fonctions
Nommage d’une cellule
La cellule « A3 » est maintenant la cellule « note_globale »
Attention : Pas d’espace !
(Vous pouvez remplacer les espaces par ‘_’)
Problème ?
Formule en référence relative
On étend la cellule sur les autres cellules.
8 |
10/4
La référence concernant la taxe est aussi descendue de deux cellules.
11/4
|
Étape 1 : On renomme la cellule pour la rendre « absolue »
Étape 2 : On tape la formule correspondante au calcul.
Étape 3 : On étend la cellule sur les autres cellules.
Note : On aurait put utiliser la référence absolue $C$6
Opérateurs classiques (1)
nnarithmé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) ouA1 ^ B7
nnde 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)
nnde texte :
& Concaténation
Les erreurs de calcul
nn##### : Entrer une date sous forme de XX mois XXXX et réduire la largeur de la colonne !
nn#DIV/0 : Diviser une valeur par la valeur d’une cellule vide (ou par 0) ! nn#NOM : Faire Somme( : xxxx) ! nn#NOMBRE : Faire 1000 1000 ! (dépassement de capacité)
nn#VALEUR : Essayer de calculer la valeur absolue d’un argument de type texte !
|
16/48
+
Fonctions de Recherche (1)
nnRenvoie une valeur provenant d'une plage
nnRecherche d’une ligne : RECHERCHEV nnRecherche d’une colonne : RECHERCHEH nnSyntaxe :
RECHERCHEV(valeur;plage;num_colonne)
" valeur : Valeur à chercher (Référence ou constante)
" 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
Fonctions de Recherche (2)
nnRenvoie 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
+
Autres recherches
nn Index(tableau;no_lig;no_col)
nnLa fonction Index renvoie la donnée située à l'intersection de la ligne et de la colonne du tableau (plage) de recherche. nnExemple Index(A1:D8;3;2) retourne la valeur de la cellule de troisième ligne et deuxième colonne c’est-à-dire B3
nnEQUIV(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 :
nnSi 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. nnSi 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. nnSi 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
nnFonction SI
SI(test ; valeur_si_vrai ; valeur_si_faux)
|
Les filtres automatiques
22/48
Excel en tant que mini-SGBD
nnNous allons voir ici, comment utiliser Excel en tant que
mini-SGBD : utiliser les feuilles en tant que table (relation) nnNous allons donc pouvoir fusionner des données, les sélectionner, etc. (nous avions déjà vue comment trier des données). nnVous 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)
nnDans 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.
nnExemple d’une table en Excel:
Filtres automatiques (2)
nnPour chaque colonne, on peut sélectionner quelles sont les lignes qui nous intéresse nnComme, on peut moduler ces choix, on parle alors
de tableau dynamique (en SQL, nous verrons que cela correspondra la clause WHERE…) nnExemple, sélectionner que les MCF :
nnOn clique dans le menu DonnéesèèFiltreèèFiltre
Automatique nnOn obtient :
nnPuis, on clique sur la colonne Statue, et on choisit MCF. On
obtient alors
Filtres automatiques (4)
nnOn peut aussi choisir des filtres plus élaborés nnPour cela, on clique sur une des colonnes puis on choix « personnalisé » :
nnIl existe plusieurs variantes comme « commence par », « se termine par »
etc…
Sous-totaux (1)
nnComme 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 nnPrenons cet exemple :
Nous souhaitons les ventes totales Pour chaque entreprise… nnNotez qu’il faut que les données soient triées nnPuis faisons les totaux des ventes de chaque entreprise.
nnPour cela, il faut cliquez dans le menu « Donnée » puis sélectionner « Soustotaux »
Sous-totaux (2)
nnNous pouvons alors choisir :
nnLe champ « A chaque changement de : » on sélectionne sur quel champs nous souhaitons un sous total. Ici par entreprise. nn« Utiliser la fonction » : plusieurs fonctions sont à
notre disposition mais celle que nous allons utiliser est bien entendu « Somme »
nn« Ajouter un sous-total à : » ici, il nous faudra
cocher « Vente » pour totaliser les ventes par entreprise
nn« Remplacer les sous-totaux existants : il est préférable de laisser cette case cochée. Si vous avez déjà utilisé la fonction Sous-totaux pour obtenir d'autres résultats, ceux-ci seront donc effacés au bénéfice des nouveaux calculs. nnSaut de page entre les groupes : cette option
permet d'obtenir, automatiquement, une page par entreprise. Soyez prudents dans son utilisation. Ici, nous n'avons que 9 entreprise (donc au minimum 9 pages), mais si nous en avions eu 150, cela n'aurait pas été sans incidence ! nnSynthèse sous les données : les résultats sont groupés à chaque changement d'entreprise
Filtres élaborés (1)
nnla grande différence entre un filtre automatique et un filtre élaboré est que ce dernier doit être saisi manuellement ; Pour autant, pas de panique : ce n'est pas franchement compliqué.
nnActivez la commande « Données - Filtre élaboré » nnOn peut activez « Copier vers un autre emplacement » si on désire conserver intact les données d'origine s nndans le champ Plages, sélectionnez vos données nndans le champ Zone de critères, sélectionnez votre zone de critère. Attention : ne sélectionnez que les lignes non vides de votre zone de critères. Ceci est extrêmement important : si vous sélectionnez une ligne de critères vierge, Excel considérera que vous souhaitez sélectionner l'intégralité de votre base ; nncochez Extraction sans doublon si vous ne souhaitez pas avoir plusieurs fois le même enregistrement.
Filtres élaborés (2)
nnLes filtres « manuels » sont des cellules contenant le filtre. On les appels « zones de critères » nnIl est souvent conseiller d’avoir une zone de critères (cellules content les filtres) puis une zone de données (extraction)
nnPlusieurs critères sur une même ligne : ET nnPlusieurs critères sur des lignes différentes : OU nnExemple :
|
Les tableaux croisés dynamiques
32/48
Objectif
nnBut par l’exemple : nnA partir d'un tableau recensant par exemple toutes les commandes de l'année, les tableaux croisés dynamiques permettent d'obtenir,des tableaux statistiques. nnVoici, à titre d'illustration, quelques exemples de résultats qu'il est possible d'obtenir
nnle nombre ou le montant des commandes pour chaque client ou type de produits (en valeur absolue ou bien même en %) ;
nnle montant ou le nombre de commandes traité par chaque employé ; nnle montant généré par chaque produit vendu, soit dans l'année, soit par mois ou par trimestre.
nnIl ne faudra pas confondre les tableaux croisés et les SGBD…
Comparaison
nnA le différence des SGBD, les données proviennent
d’une seule et même table (feuille) ; nnDans les SGBD, les données peuvent, pour des
raisons d’efficacité et de non redondance des informations, provenir de différentes tables et même être sélectionnés en chaque table (nous verrons ces possibilités dans les prochains cours avec les requêtes SQL) nnPar contre, les données seront données de manière
brute…c’est-à-dire sous la forme d’une table. Avec les tableaux dynamiques, les données seront présentés sous la forme de tableaux Excel (avec donc toute l’esthétisme qui peut allé avec)
nnOn a donc
nnAccess pour retrouver/sélectionner ces données nnExcel pour les traiter et les visualiser correctement
|
|
Création d’un tableau croisé
nnPour débuter, vous devez donc disposer d'un fichier de données. Exemple :
nnEnsuite, on sélection le menu « Données », « rapport de tableaux croisés
dynamiques » nnNous passons à la création du croisement (forme de d’auto-jointure)
Création d’un tableau croisé (1)
nnla 1ère étape vous invite à sélectionner le fichier source à exploiter. Il peut s'agir nnd'une liste ou base de données Excel (notre cas)
nnsource de données externes ; cette option permet de récupérer des informations dans des fichiers non Excel comme par exemple des résultats de requêtes SQL en Access
nndes plages de feuilles de calcul avec étiquettes (valable si vous avez utilisé la fonction Définir un nom),
nnou un autre rapport de tableau ou de graphique croisé dynamique. nnLa seconde zone d'option vous permet d'élaborer soit un tableau, soit un graphique dynamique. Notre choix s'est porté sur un tableau. nnCliquez sur Suivant
Création d’un tableau croisé (2)
nnIl vous faut sélectionner votre fichier de données. Si la zone contenu dans le champ Plage n'est pas la bonne, effacez son contenu et activez votre feuille de calcul. Sélectionnez ensuite l'intégralité de votre tableau ; nncliquez sur Suivant
Création d’un tableau croisé (3)
nnChoisissez, un emplacement pour votre tableau croisé dynamique. Il est conseillé d'opter pour une Nouvelle feuille. nnBien qu'il soit possible de cliquer directement sur le bouton Terminer, puis de procéder ensuite à l'aménagement de vos données, il est recommandé de prendre l'habitude de cliquer sur le bouton Disposition
Création d’un tableau croisé
nnExemple : nom des sociétés dans « lignes » et « PrixTotal »
dans données nnPar défaut nous avons « Somme » des « PrixTotal » mais nous
pouvons aussi avoir :
nnMOYENNE nnNOMBRE nnMIN ou MAX
Création d’un tableau nncroisé (5)
nnDans notre cas :
Puis on accepte la création du tableau et on obtient :
Création d’un tableau
nnSi on double-clic sur le champs croisé (6) « NomProduit » : nnEt on peut faire la même chose pour chacun des autres champs…
nnAttention, ne pas
oublier de cliquer sur le bouton ! si on modifie les données
(actualiser…)
Création d’un tableau croisé (7)
nnSi on prend le tableau suivant : nnOn obtient alors :