L'informatique à votre portée
L'informatique prêt-à-porter

Faites appel à moi pour vos formations
sur les dpts 81, 31, 82, 11 et 67 (Strasbourg)
Confiez moi vos développements Office

FacebookTwitterGoogle BookmarksLinkedin

Relative

Imprimer E-mail
12 mars 2014

Une référence relative est une référence relatant le chemin qu'il faut prendre, exemple : vous devez indiquer à une personne où vous habitez et vous lui expliquez comment y aller avec des "Tourner à droite puis au rond-point tourner à gauche". C'est la référence standard d'Excel, celle que vous utilisez jusqu'ici, elle s'écrit A1 par exemple. Elle est relative à la position de la formule, lorsque vous effectuez la recopie d'une cellule, contenant une formule se référant à une cellule relative la référence à cette cellule s'incrémente, par exemple : la formule "=A1+B1" devient "=A2+B2" si vous la recopiez vers le bas ou "=C1+D1" si vous la recopiez dans une cellule 2 colonnes à droite.

Saisie d'une formule

Imprimer E-mail
6 août 2012

Une formule doit toujours commencée par le signe égal =. Elle comprend également au moins deux de ces composants :

  • Constante : une valeur de type nombre, texte ou date saisie directement au clavier
  • Variable : la référence à une ou plusieurs cellules
  • Opérateur : voir le tableau ci-après
  • Fonction : formules prédéfinies faisant référence à des nombres, dates ou texte.

Les différents opérateurs ont leur ordre de priorité. Excel effectue les opérations dans l'ordre indiqué dans le tableau ci-dessous. Si une formule contient des opérateurs de même priorité, il traite les opérations de gauche à droite. En conséquence, si vous ne voulez pas vous tracasser, utilisez les parenthèses qui permettent d’isoler les calculs, exemple : =(4+3)*(8-5)
uille.

Pour plus d'informations sur les opérateurs, n'hésitez pas à consulter la page OPERATEUR et le site de Microsoft Excel 2010

Protégez vos formules

Imprimer E-mail
12 mars 2014

Protéger les formules et une feuille d'Excel 2010, 2007

Dès qu’un classeur contient des formules et des cellules devant être régulièrement modifiées, vous risquez de supprimer ou écraser vos formules avec une erreur de saisie. Il est alors bon de protéger ces formules en respectant les étapes suivantes :

  1. Sélectionnez les cellules devant rester modifiables après la protection
  2. Appelez le format de cellule (clic-droit)
  3. Dans l’onglet Protection de cette fenêtre, décochez l’option Verrouillée pour chacune de ces cellules.
  4. Puis sur l’onglet Révision, groupe Modifications, cliquez sur Protéger la feuille
  5. Choisissez un mot de passe et les options que vous autorisez après la protection. Vous devrez confirmer votre mot de passe.

Attention ! N’oubliez pas votre mot de passe, sous peine de ne plus pouvoir modifier votre feuille. De plus, le mot de passe n’est pas obligatoire

Vous pouvez également rendre vos formules invisibles dès qu’une feuille est protégée :

  1. Avant la protection, sélectionnez les plages avec une formule.
  2. Appelez le format de Cellule,
  3. puis dans l’onglet Protection de la fenêtre Format de Cellule, cochez l’option Masquée.
  4. Il ne reste plus qu’à protéger votre feuille

Retrouvez plus d'explications sur le site de Microsoft Office

Opérateurs

Imprimer E-mail
6 août 2012

Les différents opérateurs ont leur ordre de priorité. Excel effectue les opérations dans l'ordre indiqué dans le tableau ci-dessous. Si une formule contient des opérateurs de même priorité, il traite les opérations de gauche à droite. En conséquence, si vous ne voulez pas vous tracasser, utilisez les parenthèses qui permettent d?isoler les calculs, ex. : =(4+3)*(8-5).

Excel calcule avec précision jusqu'à la 15ème décimale. Dans le cas d?un format à 2 décimales, le résultat est affiché arrondi mais Excel calcule en réalité avec les 15 décimales. En cas de calculs successifs, vous trouverez une différence. Pour l'éviter, demandez expressément à ce que le calcul soit arrondi avec la fonction adéquate, ex. : =ARRONDI(A1*B2 ;2).

Saisie

Imprimer E-mail
12 mars 2014

Au cas où la barre de formule n'est pas affichée, je vous conseille fortement de l'afficher maintenant (Affichage/Barre de formule). Même si vous voyez ce que vous écrivez dans la cellule elle-même, vous visualiserez beaucoup mieux son contenu dans la barre de formule .

Pour mieux comprendre la saisie d'une formule, nous allons prendre l'exemple suivant :

Dans la cellule C vous pourriez bien évidemment taper la formule "=2+(3*4)" ou "=2+12" mais celle-ci ne sera pas mise à jour si vous inscrivez 4 dans A1, la formule "=A1+(B1*4)" permet de faire varier A1 et B1, 4 est alors une constante.

1ère méthode :

Action

Barre de formule

Sélectionnez la cellule C1.

Appuyez sur la touche =

=

Sélectionnez, avec la souris, la cellule A1.

=A1

Appuyez sur la touche +

=A1+

Appuyez sur la touche (

=A1+(

Sélectionnez, avec la souris, la cellule B1.

=A1+(B1

Appuyez sur la touche *

=A1+(B1*

Appuyez sur la touche 4

=A1+(B1*4

Appuyez sur la touche )

=A1+(B1*4)

Validez la cellule (Entrée ou une touche de direction ou la touche Tab)

2e méthode :

  • Sélectionnez la cellule C1

  • Cliquez dans la barre de formule

  • Écrivez votre formule en minuscule, surtout n'oubliez pas le signe égal : =a1+(b1*4)

  • Validez la cellule (Entrée ou une touche de direction ou la touche Tab)

Si vous avez bien tapé votre formule les références des cellules vont se mettre en majuscules.

Les références relatives et absolues

Imprimer E-mail
6 août 2012

Référence relative

Une référence relative est une référence relatant le chemin qu'il faut prendre, exemple : vous devez indiquer à une personne où vous habitez et vous lui expliquez comment y aller avec des "Tourner à droite puis au rond-point tourner à gauche". C'est la référence standard d'Excel, celle que vous utilisez jusqu'ici, elle s'écrit A1 par exemple. Elle est relative à la position de la formule, lorsque vous effectuez la recopie d'une cellule, contenant une formule se référant à une cellule relative la référence à cette cellule s'incrémente, par exemple : la formule "=A1+B1" devient "=A2+B2" si vous la recopiez vers le bas ou "=C1+D1" si vous la recopiez dans une cellule 2 colonnes à droite.

Référence absolue

Une référence absolue est une référence indiquant l'adresse exacte, exemple : vous devez indiquer à une personne où vous habitez et vous lui donnez votre adresse. Pour obtenir une référence absolue, il suffit dans Excel d'ajouter le signe $ dans la référence. Ainsi en recopiant la cellule, celle-ci ne sera pas incrémentée et la formule se référera toujours à la même adresse. Pour mettre ce $, vous pouvez l'ajouter vous-même en le tapant ou utiliser la touche F4 , comme dans le tableau suivant, avec l'exemple A1 :

Copier une formule

Imprimer E-mail
6 août 2012

Lorsque vous copiez, déplacez ou tirez une cellule contenant une formule, Excel 2010, et ancienne version, adapte votre formule selon le type de référence utilisée.

Prenons un exemple simple :

  • une valeur en A1, la formule =A1 dans les cellules B1 et A2.
  • Copiez ou tirez la cellule B1 sur la 1ère ligne et la cellule A2 sur la 1ère colonne.

Vous constatez que sur la ligne 1 les formules ont incrémenté la lettre de la colonne et que sur la colonne A, cette fois c’est le numéro de ligne qui est incrémenté. Ce fonctionnement est dû au type de référence : la référence relative, elle correspond non à une adresse fixe mais à un chemin de référence (même ligne, colonne -1 ou même colonne, ligne -1)

Nommer une cellule ou une plage

Imprimer E-mail
6 août 2012

Pour travailler avec une meilleure efficacité, il est souvent utile de nommer une plage de cellule, vous évitez ainsi les références absolues. Par ex : B3*Taux_TVA est plus explicite que B3*A1.
Pour utiliser ces noms dans une formule, vous n'êtes nullement obligé de les connaître par coeur. Créez votre formule, lorsque vous devez faire référence à une plage nommée, vous pouvez, soit, la sélectionner avec la souris, soit, procéder ainsi :

  • appuyez sur la touche F3.

  • Sélectionnez le nom

  • Cliquez sur Ok.

Vous pouvez également nommer une formule,

  • Insertion/Nom/Définir pour Excel 2003 et antérieur ou sur l'onglet FORMULE, cliquez sur le bouton Définir un nom dans le groupe Noms définis
  • Dans la zone Noms dans le classeur, inscrivez un nom.

  • Dans la zone Fait référence à, tapez votre formule.

  • Cliquez sur le bouton Ajouter.

  • Le bouton Supprimer supprime le nom sélectionné dans la liste.

Vous pouvez maintenant l'insérer dans une autre formule avec la touche F3.

Dépanner des formules

Imprimer E-mail
6 août 2012

Savoir relire une formule d'Excel 2010, 2007 ou 2003

Lorsque vous rencontrez une valeur d’erreur, ou un résultat incohérent, votre premier réflexe doit être la vérification des références de cellules.

  • Cliquez dans la barre de formule, ces références sont aussitôt colorées dans la formule et également dans la feuille. les bordures des plages sont de la même couleur que leurs références dans la barre de formule
    Dans l’exemple, ci-après, vous pouvez constatez que la plage B3 :B13 n’a pas la même taille que la plage A3 :A14, ce qui provoque l’erreur.

La protection de vos formules

Imprimer E-mail
6 août 2012

Protéger les formules et une feuille d'Excel 2010, 2007

Dès qu’un classeur contient des formules et des cellules devant être régulièrement modifiées, vous risquez de supprimer ou écraser vos formules avec une erreur de saisie. Il est alors bon de protéger ces formules en respectant les étapes suivantes :

  1. Sélectionnez les cellules devant rester modifiables après la protection
  2. Appelez le format de cellule (clic-droit)
  3. Dans l’onglet Protection de cette fenêtre, décochez l’option Verrouillée pour chacune de ces cellules.
  4. Puis sur l’onglet Révision, groupe Modifications, cliquez sur Protéger la feuille
  5. Choisissez un mot de passe et les options que vous autorisez après la protection. Vous devrez confirmer votre mot de passe.

Attention ! N’oubliez pas votre mot de passe, sous peine de ne plus pouvoir modifier votre feuille. De plus, le mot de passe n’est pas obligatoire

Vous pouvez également rendre vos formules invisibles dès qu’une feuille est protégée :

  1. Avant la protection, sélectionnez les plages avec une formule.
  2. Appelez le format de Cellule,
  3. puis dans l’onglet Protection de la fenêtre Format de Cellule, cochez l’option Masquée.
  4. Il ne reste plus qu’à protéger votre feuille

Opérateurs

Imprimer E-mail
12 mars 2014

Opérateur

Exemple

Signification

Opérateurs de zone

:

B3:B7

Plage simple

Espace

B3:C7 C4:F12

Intersection de plages. Ici le résultat est C4:C7 (elles sont communes aux 2 plages)

;

B3:B7;C4:F12

Union de plages

Opérateurs arithmétiques

-

-5

Négation

%

10%

Pourcentage

^

5^2

Puissance

*

5*2

Multiplication

/

5/2

Division

+

5+2

Addition

-

5-2

Soustraction

Opérateur textuel

&

B3&B7

Concaténation de chaînes. Si dans B3 vous avez « Bon » et dans B7 « jour », le résultat sera « Bonjour »

Opérateur de comparaison

=

B3=B7

Egal

<>

B3<>B7

Différent

<

B3<B7

Inférieur

<=

B3<=B7

Inférieur ou égal

>

B3>B7

Supérieur

>=

B3>=B7

Supérieur ou égal

Les différents opérateurs ont leur ordre de priorité. Excel effectue les opérations dans l'ordre indiqué dans le tableau ci-dessus. Si une formule contient des opérateurs de même priorité, il traite les opérations de gauche à droite. En conséquence, si vous ne voulez pas vous tracasser, utilisez les parenthèses qui permettent d?isoler les calculs, ex. : =(4+3)*(8-5).
Excel calcule avec précision jusqu?à la 15ème décimale. Dans le cas d?un format à 2 décimales, le résultat est affiché arrondi mais Excel calcule en réalité avec les 15 décimales. En cas de calculs successifs, vous trouverez une différence. Pour l?éviter, demandez expressément à ce que le calcul soit arrondi avec la fonction adéquate, ex. : =ARRONDI(A1*B2 ;2).

Corriger une formule contenant une ou plusieurs fonctions

Imprimer E-mail
12 mars 2014

Lorsqu’une formule contient plusieurs fonctions imbriquées il est parfois difficile de la relire et la corriger. Pour simplifier cette lecture, utilisez la commande Insertion de fonction :

  1. Cliquez dans la barre de formule sur le nom de la fonction que vous voulez vérifier
  2. Cliquez sur le bouton ƒx. La fenêtre Arguments de fonction s’affiche aussitôt.
     

 
Sans quitter cette fenêtre, cliquez sur une autre fonction. La boîte de dialogue affiche alors les arguments de cette nouvelle fonction. Vous pouvez ainsi vérifier les arguments de chacune des fonctions en cliquant sur son nom dans la barre de formule.
Sur l'exemple ci-dessous, le 1er écran affiche les arguments du SI.

Absolue

Imprimer E-mail
12 mars 2014

Une référence absolue est une référence indiquant l'adresse exacte, exemple : vous devez indiquer à une personne où vous habitez et vous lui donnez votre adresse. Pour obtenir une référence absolue, il suffit dans Excel d'ajouter le signe $ dans la référence. Ainsi en recopiant la cellule, celle-ci ne sera pas incrémentée et la formule se référera toujours à la même adresse. Pour mettre ce $, vous pouvez l'ajouter vous-même en le tapant ou utiliser la touche F4 , comme dans le tableau suivant, avec l'exemple A1 :

Nombre d'appuie sur la touche F4

Résultat

Après recopie vers la droite

Après recopie vers le bas

1

$A$1

$A$1

$A$1

2

A$1

B$1

A$1

3

$A1

$A1

$A2

4

A1

B1

A2

Pour utiliser la touche F4 :

  • Vous placez le point d'insertion, dans la barre de formule, sur l'adresse de la cellule concernée
  • Vous cliquez
  • Puis vous appuyez sur la touche F4

Corriger une formule

Imprimer E-mail
12 mars 2014

Vous pouvez également retrouver une aide complémentaire sur les pages du site Microsoft Office listée sur Correction de formules

Evaluer une formule d'Excel

Imprimer E-mail
12 mars 2014

Pour corriger vos formules, vous avez une autre possibilité : l’évaluation de formules. Cette commande permet l’évaluation en pas à pas d’une formule.

Prenons par exemple une formule qui doit calculer la TVA après l’application d’une remise. Vous pouvez avoir saisi machinalement cette formule : =HT-Remise*TxTva ce qui affiche un net à payer de 1861,14 € au lieu de 1089,90 €. Pourquoi ?

Evaluer une formule dans Excel 2010

Sélectionnez cette cellule et sur l’onglet Audit de formule, cliquez sur le bouton Evaluer la formule :

Trouver les cellules contenant une formule

Imprimer E-mail
12 mars 2014

Corriger une erreur dans une cellule, c'est bien, mais comment rapidement retrouver toutes les formules de ma feuille ?

Dans Excel 2010 et 2007 c'est tout simple :

  1. Placez vous dans une cellule de votre feuille
  2. cliquez simplement sur la commande Formule du bouton Recherchez et sélectionnez de l'onglet Accueil

Savoir relire une formule

Imprimer E-mail
12 mars 2014

Lorsque vous rencontrez une valeur d’erreur, ou un résultat incohérent, votre premier réflexe doit être la vérification des références de cellules.

  • Cliquez dans la barre de formule, ces références sont aussitôt colorées dans la formule et également dans la feuille. les bordures des plages sont de la même couleur que leurs références dans la barre de formule
    Dans l’exemple, ci-après, vous pouvez constatez que la plage B3 :B13 n’a pas la même taille que la plage A3 :A14, ce qui provoque l’erreur.

Les codes d'erreur sur les formules d'Excel 2010

Imprimer E-mail
12 mars 2014

Excel 2010 vous aide dans l’identification de la source de votre erreur en affichant un code d’erreur, comprendre ces codes est donc indispensable.

##### : La cellule n’est pas assez large pour afficher le nombre (ou la cellule contient une heure négative).

#VALEUR ! Un des arguments de la fonction ou de la formule n’est pas du bon type ou est manquant. Exemple vous essayez d’additionner une cellule contenant un nombre et une cellule contenant un texte : le texte n’est pas une valeur appropriée.

#DIV/0 ! Vous essayez de diviser par zéro ou par une cellule vide (équivalent à la valeur 0), ce qui est impossible.

Vérification

Imprimer E-mail
12 mars 2014

Lors de la conception, si votre formule contient une erreur, Excel vous propose de la corriger, acceptez toujours, il y a de grandes chances qu'il vous corrige correctement. Dans le cas contraire ou si l'erreur ne vous apparaît que plus tard, sélectionnez votre cellule, cliquez sur le signe égal de la barre de formule. La palette de formules s'ouvre et en cliquant sur chaque fonction contenue dans la cellule, cette palette va vous permettre de repérer l'erreur et de la corriger.

Fonctions imbriquées

Imprimer E-mail
12 mars 2014

Une fonction peut utiliser une autre fonction comme argument. Elle doit renvoyer le même type de valeur que celui attendu par l'argument. Si une fonction imbriquée ne renvoie pas le bon type d'argument, Microsoft Excel affiche la valeur d'erreur #VALEUR!. Une formule peut contenir jusqu'à sept niveaux d'imbrication.
Exemple : soit les fonctions A, B, C, D, E, F et G, chacune n'ayant qu'un seul argument. Vous pouvez donc les imbriquer ainsi : =A(B(C(D(E(F(G(argument de G))))))).
Pour imbriquer les fonctions vous pouvez utiliser la Palette, prenons comme exemple la formule suivante, "=ABS(SOMME(A1:A5))", dans la cellule A6 :

  • sélectionnez A6.

  • Cliquez sur le bouton Coller une fonction , de la barre d'outil Standard.

  • Dans la boîte de dialogue, vous choisissez la Catégorie Maths & Trigo, puis la fonction ABS.

  • Cliquez dans la zone du 1er argument (Nombre) de la palette de formules.

  • Cliquez sur le petit triangle déroulant la liste des fonctions.
  • Si la fonction Somme n'est pas dans la liste sélectionnez Autres fonctions.

  • La fonction s'insère donc dans la fonction ABS.

  • La palette de formules vous aide à remplir cette fonction SOMME.

Si la fonction de niveau supérieur contient plusieurs arguments, vous pouvez la retrouver dans la palette de formules, en cliquant sur son nom dans la barre des formules.
Cette méthode ne vous permet que l'insertion d'une formule dans un des arguments d'une autre formule. Vous aurez parfois besoin d'effectuer l'opération inverse, exemple, dans la cellule A6, vous mettez la formule "=MOYENNE(A1:A5)" , si aucune donnée ne se trouve dans la plage A1:A5, la cellule A6 affiche l'erreur #DIV/0!, pour vous indiquer qu'il est impossible de diviser par 0, vous voulez donc gérer cette erreur avec la fonction SI. 2 possibilités s'offre à vous : a) vous maîtrisez parfaitement la fonction, tapez la directement dans la barre de formules, b)la fonction ne vous est pas familière, suivez la méthode suivante :