Les Fonctions de Base Excel Niveau 2 [PDF]

Zitiervorschau

I. Quelles sont les fonctions de calcul Excel ? Comment utiliser les fonctions de calcul. Comment est organisée la bibliothèque de fonctions Excel. Quelles sont les principales catégories. Les fonctions de calcul Excel sont classées par catégories : statistiques, mathématiques, logique, finance…. Cliquez sur une catégorie pour parcourir ses fonctions. Ou appuyez sur Ctrl+F pour rechercher une fonction en tapant les premières lettres ou un mot descriptif. Astuce : pour obtenir des informations détaillées sur une fonction, cliquez sur son nom dans la première colonne. Pour utiliser une fonction prédéfinie, il suffit d’écrire la formule : = nom de la fonction () et de préciser, entre les parenthèses, à quelles données doit s’appliquer le calcul en sélectionnant ou en écrivant les références des cellules à utiliser. Voici, à titre d’exemple un extrait des fonctions disponibles dans Excel : a) les dernières utilisées :

b) dans la catégorie : mathématiques :

c) dans la catégorie statistique :

d) dans la catégorie logique :

Astuce : l’aide en ligne, disponible dans Excel, donne une information complète sur les conditions d’usage des fonctions prédéfinies.

II. Comment gérer les plages de cellules Excel Pour opérer sur de grandes quantités de données, les fonctions de calcul (somme, moyenne, maximum…) s’appliquent sur des plages de cellules. On appelle plage de cellules, un ensemble rectangulaire de cellules contiguës, délimité par la cellule en haut à gauche et la cellule en bas à droite. On note habituellement une plage par les références aux 2 coins séparés par le symbole « : ». Par exemple : • •

A1:A6 désigne les 6 premières cellules de la colonne A. B2:H2 désigne les cellules de la ligne 2 comprises entre les colonnes B et H • A1:D3 désigne les cellules contenues dans le rectangle entre les coins A1 et D3 soit les cellules A1, A2, A3, B1, B2, B3, C1, C2, C3, D1, D2 et D3. • Dans le tableau suivant comportant les ventes de Mars de produits, on saisit dans la cellule C13 la formule « =SOMME(C3:C12) » en saisissant la formule « =somme()« , puis en sélectionnant la plage de données par un clic sur C3 maintenu jusqu’en C12.

Attention : On peut aussi, dans une opération généralisée, énumérer une liste de cellules ou de plages en les séparant par des « ; » par exemple : A1; A2; A3; A4; A5.

III. Comment marche les références relatives ou absolues dans Excel Comment sont référencées les cellules Excel. Comprendre les références relatives et absolues. Utiliser le caractère $. Qu’est-ce qu’une ligne relative ou une ligne absolue. Rappel : une cellule est désignée par son numéro de colonne et de ligne, appelée aussi la référence ; ainsi, A1 est la référence de la cellule A1 ! Pour utiliser efficacement les formules de calcul, il est important de bien comprendre comment sont référencées les cellules. Il existe 4 types de références : • • • •

Référence relative (ex : A1) Référence absolue (ex : $A$1) Ligne absolue (ex : A$1) Colonne absolue (ex : $A1)

Nous allons détailler chacun de ces types de référence. Je vais volontairement expliquer dans le détail le fonctionnement du référencement des cellules car cette base est indispensable pour utiliser efficacement Excel.

1- Comment utiliser une référence relative Excel ? La référence de la cellule est relative. Si l’on fait un copier/coller d’une formule contenant cette référence (Par exemple A1 ) alors la référence de la formule s’ajustera en fonction du nouvel emplacement.

EXEMPLE 1. Entrer dans la cellule A1 le texte « les chanteurs » comme indiquer ci-dessous :

2. Utiliser dans la cellule A2 la formule MAJUSCULE qui permet de transformer un texte minuscule (ici le contenu de la cellule A1 : les chanteurs) en texte en majuscule.

La référence (relative) utilisée dans la formule majuscule doit être “A1“ Ensuite appuyez sur entrée. Le résultat obtenu doit être le suivant :

LES CHANTEURS est affiché en majuscule dans la cellule A2. 3. Inscrivez dans la cellule A2 le texte suivant ‘les danseurs‘ : 4.Sélectionner et copier la cellule B1 5.Coller la formule dans la cellule B2 Le résultat affiché dans la cellule B2 est le suivant : ‘LES DANSEURS” et non pas ‘LES CHANTEURS‘ Quelles remarques pouvons-nous faire ? En faisant un copier/coller nous n’obtenons pas dans la cellule B2 le même contenu que celui de la cellule B1. Pourquoi ? –> Car seule la formule (=MAJUSCULE) a été copié telle quelle. La référence dans la formule, elle, a changé. On peut le constater en double cliquant dans la cellule B2, alors on s’apercevra que la référence (relative) n’est plus A1 mais A2.

2- Comment utiliser une référence absolue Excel ?

Si l’on fait un copier/coller d’une formule contenant une référence absolue (Par exemple $A$1 ) alors la référence de la formule ne changera pas. Le symbole dollars « $ » permet de transformer une référence relative en référence absolue.

Ainsi si on effectue un copier/coller dans la cellule B2 de la formule =MAJUSCULE($A1$) contenue en B1 comme dans l’exemple ci-dessus alors le résultat sera le suivant : On remarque que la référence $A$1 est la même dans la cellule B2 et B1. Dans les deux cas la formule « =MAJUSCULE($A$1) » utilise la référence “$A$1“ Astuce : Lorsque vous avez saisi le code suivant : « =MAJUSCULE(A1), en appuyant sur la touche F4 vous obtiendrez automatiquement le résultat suivant “=MAJUSCULE($A$1)“

3- Comment définir une ligne absolue dans Excel ? Dans ce cas, la référence est partiellement absolue, seule la ligne est figée. La colonne reste relative au nouvel emplacement. Une référence avec ligne absolue s’écrit de la façon suivante “A$1“

4- Comment définir une colonne absolue dans Excel Dans ce cas la référence est partiellement absolue, seule la colonne est figée. La ligne reste relative au nouvel emplacement. Une référence avec colonne absolue s’écrit de la façon suivante “$A1“ Note : Vous remarquerez que le signe dollars $ se place juste en avant ce qui doit rester figé. Avant le ‘1’ si l’on veut que la référence reste figée sur la ligne ‘1‘. Juste avant le ‘A‘ si l’on veut que la référence reste figée sur la colonne ‘A‘. Et devant la référence de ligne et de colonne si l’on veut qu’il y soit une référence absolue sur une cellule.

IV. Insérer des colonnes ou lignes Insérer une colonne dans une feuille Excel Pour insérer une seule colonne, 1. Sélectionnez une colonne ou une cellule dans la colonne située immédiatement à droite de l’endroit où vous voulez insérer la nouvelle colonne 2. Faîtes alors un clic droit afin d’afficher le menu contextuel puis cliquez sur Insertion Par exemple, pour insérer une colonne à gauche de la colonne B, cliquez sur une cellule de la colonne B. Pour sélectionner une ligne ou une colonne, cliquez sur l’entête de colonne (2) : 2. En-tête de colonne

Insérer une ligne dans une feuille Excel Pour insérer une ligne unique, 1. Sélectionnez une ligne entière ou une cellule dans la ligne située au-dessus de laquelle vous souhaitez insérer la nouvelle ligne. 2. Faîtes alors un clic droit afin d’afficher le menu contextuel puis cliquez sur Insertion Par exemple, pour insérer une nouvelle ligne au-dessus de la ligne 5, cliquez sur une cellule de la ligne 5. Pour sélectionner une ligne, cliquez sur l’en-tête de ligne (1) : 2. Entête de ligne

V. comment trier ou filtrer les données Comment trier ou filtrer ses données Excel pour les rendre synthétique. Comment définir les critères de tri ou de filtre.

1- Trier les données Trier des données consiste à choisir une zone de données puis à en trier les lignes (ou les colonnes) en fonction de l’ordre sur un ou plusieurs critères – en ordre croissant ou décroissant selon la valeur d’une ou plusieurs colonnes (ou lignes, si on trie des colonnes de données). Trier des lignes de données permet de regrouper celles qui partagent la même valeur dans une colonne particulière, si on utilise cette colonne comme indice de tri. Concernant l’exemple suivi, on a utilisé les critères de tri suivant :

Pour obtenir le fichier contenant au début les données 1986 (tri croissant par année), par Vignoble (tri croissant alphabétique), et par Appellation (tri croissant alphabétique).

2- Filtrer les données Filtrer des données consiste à sélectionner par un ou plusieurs critères – les valeurs des colonnes sur lesquelles portent le filtre – les lignes qui devront être visualisées et celles qui seront cachées. Filtrer permet de choisir de ne voir que telle partie des données, ce qui peut s’avérer très utile pour commencer l’analyse d’un fichier comportant beaucoup de données. Sur l’exemple du fichier ‘cave à vin’, sélectionner la plage des cellules A3:H3 contenant les entêtes de colonnes puis activer les filtres en cliquant sur Données> Trier et Filtrer> Filtrer a) on a utilisé successivement un filtre N°1 sur le niveau couleur pour ne retenir que le blanc :

On notera en indice de colonnes que les numéros ne sont plus consécutifs car seule une petite partie des lignes est alors affichée à l’utilisateur, celles qui respectent les critères choisis. b) puis on a utilisé un filtre N°2 sur l’âge pour ne retenir que les plus de 15 ans d’âge.

c) enfin, on a utilisé un filtre N°3 sur le nom pour ne retenir que les château :

VI. Donner un nom aux cellules Comment donner un nom aux cellules Excel. L’utilisation de nom pour identifier une cellule rend plus lisible les formules de calcul. Excel offre la possibilité de nommer une cellule ou une plage de cellules L’utilisation de noms rendra plus lisible vos formules de calcul, ainsi : =B2*C2*H35 n’est pas très parlant =Prix * Quantité* Taux TVA est bien plus lisible. Un autre avantage des noms est qu’ils ne sont pas modifier/incrémenter lors des copies incrémentales.

Comment nommer vos cellules 1. Sélectionner la cellule à nommer : 2. taper directement le nom dans la zone nom. (Liste déroulante à gauche de la barre formule) => Une autre solution consiste à cliquer sur le bouton “Définir un nom“ de l’onglet “Formule" : Indiquer le nom que vous souhaitez attribuer à la cellule “B2“ Le nom “résultat“ pourra ensuite avantageusement être utilisé dans le contexte suivant : Cela équivaut à la formule suivante, avec l’avantage d’être beaucoup plus explicite. Dans les 2 cas le résultat est le suivant :

VII. Ajouter des commentaires Ajouter un commentaire à une cellule Excel • •

Sélectionnez la cellule à laquelle vous souhaitez ajouter un commentaire. Sous l’onglet Révision, dans le groupe Commentaires, cliquez sur Nouveau commentaire. Raccourci clavier vous pouvez également appuyer sur MAJ + F2.Un nouveau commentaire est créé. Le pointeur se place dans le commentaire. Un indicateur apparaît dans l’angle de la cellule. Par défaut, le nouveau commentaire est marqué par un nom. Pour utiliser un autre nom, vous pouvez sélectionnez le nom dans le commentaire, puis tapez un nouveau nom. Ou bien, vous pouvez supprimer le nom, sélectionnez le nom dans le commentaire, en appuyant sur SUPPR.

• •

Dans la zone de texte du commentaire, tapez le texte associé. Cliquez en dehors de la zone du commentaire. La zone de texte du commentaire disparaît, mais son indicateur reste affiché. Pour que le commentaire soit toujours visible, procédez comme suit : a. Sélectionnez la cellule. b. Sous l’onglet Révision, dans le groupe Commentaires, cliquez sur Afficher/Masquer les commentaires.

Conseil : Vous pouvez également cliquer avec le bouton droit dans la cellule qui contient le commentaire, puis cliquer sur Afficher/Masquer les commentaires.

VIII. Créer un graphique Quel type de graphique choisir ? Excel vous permet de créer facilement un graphique à partir d’une série de données. Mais le choix du type de graphique à utiliser : histogramme, courbe, camembert ou radar, ne doit pas être limité à de simples considérations esthétiques. La conception d’un graphique pertinent passe d’abord par le choix des données à visualiser sans oublier les entêtes de lignes et colonnes. En effet la zone de données sélectionnée déterminera l’ensemble de ce qui pourra être affiché dans le graphique. Les graphiques de type : courbes et histogrammes mettent bien en évidence les tendances dans le temps (année, mois …). Le camembert (secteur) est bien adapté pour montrer les proportions d’un tout (ex : répartition de données exprimées en pourcentage). Le radar met bien en évidence la répartition des données.

Comment créer un graphique avec Excel ? Pour créer un graphique : 1. Sélectionnez les cellules qui contiennent les données à utiliser pour le graphique

2. Cliquez sur l’onglet Insertion, puis dans le groupe Graphiques : cliquez sur le type de graphique souhaité : 1) histogramme, 2) courbe, 3) camembert, 4) radar …

3. Puis cliquez sur le sous-type de graphique à utiliser.

4. Par défaut, le graphique est placé dans la feuille de calcul sous la forme d’un graphique incorporé. Vous pouvez le déplacer en le faisant glisser avec la souris. Dans le tableau de production par mois, c’est l’évolution de la production qui est pertinente, la visualisation peut se faire par une courbe ou un histogramme, en fonction du mois. Pour le tableau de composition d’un produit alimentaire, les masses absolues en gramme n’ont pas de signification. C’est leur grandeur relative qui permet d’apprécier la composition du produit. Le graphique de type camembert remplit bien cette fonction.

IX. Mettre en page une feuille Excel Définir la zone d’impression Si vous imprimez fréquemment une section spécifique de votre feuille de calcul, vous pouvez définir une zone d’impression pour celle-ci. Ainsi, lorsque vous imprimez votre feuille de calcul, seule cette section s’imprime.

a. Comment définir une zone d’impression 1. Sélectionnez la plage des cellules que vous souhaitez imprimer (ici : A1:F15) et qui constituera la zone d’impression.

2. Cliquez sur l’onglet Mise en page puis cliquez sur l’icône ZoneImpr

3. Cliquez sur l’icône Définir La plage de cellules sélectionnées prend alors le nom : Zone d’impression

b. Définir les marges de la feuille Réaliser les opérations suivantes : 1. 2. 3.

Cliquez sur l’onglet Mise en page Cliquez sur l’icône Marges Choisissez Normales ou Larges ou Etroites

c. Définir l’orientation portrait ou paysage d’une feuille Vous pouvez opter pour une orientation Portrait (verticale) ou Paysage (horizontale) pour votre feuille Excel.

d. Modifier l’orientation Réaliser les opérations suivantes : 1. Cliquez sur l’onglet Mise en page 2. Cliquez sur l’icône Orientation 3. Choisissez Portrait ou Paysage

X. Imprimer une feuille Excel Imprimer et afficher un aperçu de la feuille Pour imprimer une feuille Excel, cliquez sur l’onglet Fichier et l’écran suivant apparait.

Cliquez sur l’image pour l’agrandir. Effectuez les actions suivantes : 1. 2. 3. 4. 5.

Cliquez sur Imprimer dans le bandeau vert de gauche Sélectionner les feuilles à imprimer : les feuilles actives ou tout le document Sélectionner le nombre de copies que vous souhaitez imprimer Sélectionner l‘imprimante sur laquelle sera faite l’impression Lancer l’impression en appuyant sur le bouton Imprimer