Editions ENI Livres | Vidéos | e-Formations
Les outils à maîtriser avant de commencer
Introduction
Afin d’aborder plus confortablement les différents projets, nous vous proposons de voir dans ce chapitre les commandes et fonctions indispensables pour travailler efficacement avec Excel.
Les références relatives, absolues et mixtes
Le point fort d’Excel est de nous éviter la saisie répétitive d’une même formule, grâce à l’utilisation d’une recopie "intelligente". En effet, le simple fait de recopier une formule dans la verticale (changement de lignes) ou dans l’horizontale (changement de colonnes) vers une autre cellule entraîne la modification des références utilisées dans le calcul : elles s’adaptent automatiquement.
Pour une meilleure compréhension, nous vous proposons de reproduire les différents exemples qui vont illustrer nos propos.
Références relatives
Nous parlons de références relatives lorsque, suite à la recopie d’une formule vers d’autres cellules, les références qui la composent changent automatiquement pour s’adapter au lieu où elles sont copiées.
Voici un exemple d’utilisation :
Nous avons mis en place en D2 la formule de calcul pour obtenir le total. Nous allons la valider et la recopier de D3 à D5 en utilisant la poignée de recopie.
Vous pouvez également utiliser le copier-coller ou l’outil Recopier en bas du groupe Édition de l’onglet Accueil.
La formule est recopiée et les références sont automatiquement modifiées afin qu’elles correspondent...
Nommer une cellule
Il n’est pas toujours nécessaire de bloquer une référence en utilisant le caractère $, il est aussi possible de nommer les cellules.
Le fait de nommer une cellule est particulièrement intéressant lorsque les formules sont complexes. Mais cela peut aussi avoir un intérêt dans des calculs simples par le fait qu’un nom est souvent plus parlant qu’une référence.
Créer un nom de manière rapide
Nous allons le faire à partir d’un exemple :
Pour nommer la cellule B1 qui contient le taux de TVA, cliquez dans la zone Nom de la barre de formule puis saisissez TVA.
Validez par Entrée.
Cliquez en B4 puis saisissez =A4*TVA.
Vous pouvez également sélectionner la cellule nommée pour que son nom soit automatiquement reporté dans la formule.
La cellule B1 en se nommant TVA devient absolue. En recopiant votre formule vers le bas, le nom TVA sera répété à chaque ligne.
Gestionnaire de noms
Pour modifier ou supprimer un nom, vous passerez par le Gestionnaire de noms :
Onglet Formules - groupe Noms définis - bouton Gestionnaire de noms
Vous trouverez dans la boîte de dialogue tous les noms créés dans le classeur avec des informations telles que les références, la valeur, etc.
Dès que vous sélectionnez un nom, les boutons Supprimer...
La fonction SI
Une question, deux réponses possibles
Fonction incontournable qui rend bien des services. Elle permet de choisir la réponse à obtenir selon une condition.
Sa structure est la suivante :
=SI(question ; valeur si réponse positive ; valeur si réponse négative) Elle pourrait se traduire par :
SI question vérifiée ALORS renvoyer valeur SINON renvoyer autre valeur.
Question : la question consiste à comparer le contenu d’une cellule à un élément en utilisant les signes égal =, supérieur >, inférieur <, supérieur ou égal >=, inférieur ou égal <=, différent <>. Cette question est de type logique, c’est-à-dire entraînant la réponse oui ou non (vrai ou faux).
Cet élément peut être une valeur numérique (A1>1000), une valeur texte à écrire entre guillemets (A1="bonjour"), une référence de cellule (A1=B1), une formule (A1>somme(B1:C1)).
Valeur si réponse positive : correspond à la valeur à renvoyer si la réponse à la question est positive (vrai). Cette valeur peut être numérique (100), texte ("bravo") ou le résultat d’un calcul (A1*10%).
Valeur si réponse négative : correspond à la valeur à renvoyer si la réponse à la question est négative (faux) ; elle peut être numérique, texte ou le résultat d’un calcul.
Voici un exemple simple :
À traduire par : SI en A2 est inscrite la valeur textuelle soleil, ALORS renvoyer le texte parasol SINON renvoyer le texte parapluie.
Résultat après recopie de la formule en B3 :
Une question, plusieurs réponses possibles
Deux réponses c’est déjà bien, mais c’est parfois insuffisant. Nous pouvons avoir besoin de 3, 4 réponses ou plus. Nous pouvons aller jusqu’à 64 fonctions SI soit 65 réponses depuis la version 2007 d’Excel....
Évaluation de formule
Lorsque nous entreprenons des formules de calcul complexes, nous obtenons parfois des réponses inattendues. Nous avons beau lire et relire notre formule, l’erreur ne nous saute pas aux yeux. Il est intéressant alors d’utiliser l’outil Évaluation de formule.
Voyons son fonctionnement avec un cas simple :
Le code produit est composé de deux parties séparées par un tiret : une référence et un prix.
Nous avons besoin de calculer en colonne C la valeur en stock pour les produits dont le prix est supérieur à 6. Pour ce faire, nous allons saisir en C2 la formule suivante :
=SI(DROITE(A2;5)>6;DROITE(A2;5)*B2;"")
La fonction DROITE renvoie le nombre de caractères spécifiés (5) d’une cellule (A2) en partant, comme son nom l’indique, de la droite. Ce qui donne 05,60, la virgule comptant pour un caractère.
Une fois votre formule validée, vous obtenez 67.2.
Pourtant, 05.60 est inférieur à 6. La fonction ne devait rien nous renvoyer.
C’est là qu’entre en jeu l’outil Évaluation de formule.
Sélectionnez la cellule C2 qui contient la formule.
Onglet Formules - groupe Vérification des formules - bouton Évaluer la formule
Cet outil va évaluer, au fur et à mesure, votre formule en soulignant à chaque fois l’élément...
Onglet Développeur
Nous aurons à créer des macros et de ce fait, nous avons besoin de l’onglet Développeur qui, par défaut, n’est pas présent dans le Ruban.
Comment l’afficher ?
Onglet Fichier - Options
Dans la boîte de dialogue Options Excel, cliquez sur la catégorie Personnaliser le Ruban.
Dans le volet Onglets principaux, cochez Développeur puis validez par le bouton OK.
Macros et fichiers
Enregistrement
Depuis la version 2007, les fichiers contenant des macros doivent obligatoirement être enregistrés sous le type Classeur Excel (prenant en charge les macros).
L’extension de ce type de fichier est .xlsm.
Paramétrage des macros
À partir des options Excel, vous pouvez paramétrer votre logiciel pour choisir le comportement qu’il doit avoir lors de l’ouverture d’un fichier contenant des macros.
Onglet Fichier - Options
Dans la boîte de dialogue Options Excel, cliquez sur la catégorie Centre de gestion de la confidentialité.
Cliquez sur le bouton Paramètres du Centre de gestion de la confidentialité.
Dans la fenêtre qui s’affiche, cliquez sur la catégorie Paramètres des macros.
Activez l’option Désactiver toutes les macros avec notification.
Cliquez à deux reprises sur OK.
En activant cette option, dès l’ouverture d’un fichier contenant des macros, vous aurez le message suivant :
Si vous voulez juste consulter le fichier et éventuellement lire le code, il ne sera pas nécessaire d’activer le contenu.
Mais si vous voulez exécuter les macros, vous devrez, avant toute chose, cliquer sur le bouton Activer le contenu.
