Excel Marketing

Analysemarketing

et reportingavec Excel

Ivana TaylorBill Jelen

12251_Excel_FauxTitre 12/12/07 14:09 Page 1

01_pdt_blaess.indd 3 13/12/07 10:39:55

Entrer une formule

Les données du problèmePour obtenir les valeurs des ventes futures fondées sur une certainehypothèse de taux de croissance, vous devez utiliser une formule.

Comment s’y prendreObservez la feuille de calcul présentée ci-dessous. Le montant desventes pour l’année 2006 s’élève à 1 774 000. Ce montant apparaîten B3, et le taux de croissance pour 2007 a été saisi en B5. En B7, la for-mule =B3*B5 fait le produit de la valeur saisie en B3 par celle saisie en B5.

3Formules

EN COULISSES Des calculs dynamiques

Dans un classeur, dès qu’un changement survient, toutes les formules sont recalculées instanta-nément.

Analyse marketing et reporting avec ExcelCHAPITRE 3

42

Si vous entrez un nouveau taux en B5, Excel recalcule immédiate-ment la cellule B7 pour adapter son résultat au nouveau taux.

Plus en détailIl y a trois façons d’entrer cette formule.• Si vous êtes un fanatique du clavier, vous pouvez directement

taper =B3*B5 dans la cellule B7.• Si vous préférez la souris, commencez par taper le signe =, puis,

utilisez la souris pour cliquer sur B3, tapez *, et enfin, utilisez ànouveau la souris pour cliquer sur B5.

• Mais la méthode la plus rapide est sans doute d’utiliser les touchesde direction. C’est la méthode de prédilection de ceux qui ontconnu la préhistoire du tableur avec Lotus 1-2-3.1. Tapez =.2. Pressez quatre fois la touche de direction vers le haut pour

atteindre la cellule B3.3. Tapez *.

Figure 3–1 Dans cette cellule, on a fait appel à une simple multiplication pour calculer le montant des ventes prévisionnelles.

Figure 3–2 Excel recalcule le montant des ventes prévisionnelles dès que le taux de croissance est modifié.

FormulesCHAPITRE 3

43

4. Pressez deux fois la touche de direction vers le haut pour attein-dre la cellule B5.

5. Pressez la touche Retour.

OpérateursVoici les principaux opérateurs :• Signe Plus (+) Addition• Signe Moins (-) Soustraction• Astérisque (*) Multiplication• Barre de division (/) Division• Accent circonflexe (^) Exposant• Esperluette (&) Concaténation• Parenthèses () Agir sur l’ordre des opérations

Voyons à présent quelques exemples d’utilisation :

Ordre des opérationsLe moteur de calcul d’Excel exécute les opérations dans un ordreprécis.• Exposants• Multiplications et divisions de la gauche vers la droite• Additions et soustractions de la gauche vers la droite

Tableau 3–1 Principaux opérateurs et leur utilisation pour construire les formules

Pour additionner deux cellules Signe + =A1+A2

Pour soustraire une cellule d’une autre Signe - =A2-A1

Pour multiplier deux cellules Astérisque =A1*A2

Pour diviser une cellule par une autre Barre de division =A2/A1

Pour élever une cellule à une certaine puissance

Accent circonflexe =A1^3

Pour calculer une racine Accent circonflexe, mais avec une puissance inférieure à 1. Cette formule calcule la racine quatrième de 256

=256^(1/4)

Pour concaténer du texte Esperluette =A1&A2


44

Si vous considérez la formule =A2+B2*C2^D2, Excel commence parcalculer C2^D2. Puis il multiplie B2 par le résultat qu’il vient detrouver. Et enfin, il finit par ajouter A2 avant de renvoyer le résultatfinal dans la cellule.

Vous pouvez intervenir sur cet ordre en ajoutant desparenthèses : =(A2+B2)*C2^D2.

Les fonctions préprogramméesParallèlement aux opérateurs de base, Excel offre plus de 350 fonc-tions préprogrammées très puissantes. Certaines fonctions commeSOMME sont d’un accès immédiat, mais la découverte des autres fonc-tions ne pose pas de réel problème.

Si vous devez faire la somme de valeurs saisies dans des cellules conti-guës, il est plus simple et plus rapide d’utiliser =SOMME(A2:A5) que demettre en œuvre l’addition de base pour écrire =A2+A3+A4+A5. Plusvous avez de données à additionner, plus le choix de la fonction SOMMEdevient crucial. Il est bien évident qu’entrer une formule comme=SOMME(A2:A999) est beaucoup plus rapide que de saisir quelquechose comme =A2+A3+A4+…+A999.

Figure 3–3 Excel ne peut même pas calculer une formule comme celle qui apparaît ici. Aussi, par pitié, n’essayez pas de saisir une chose aussi monstrueuse. Utilisez plutôt la fonction SOMME comme cela est décrit ci-dessus.

FormulesCHAPITRE 3

45

Entrer une fonction depuis le rubanAu niveau de l’onglet Formules du ruban, vous pouvez voir la lettregrecque Σ. Ce bouton correspond à l’insertion automatique de lafonction SOMME. Si, au lieu de cliquer directement sur le bouton, vousutilisez la liste déroulante, vous avez un accès direct aux fonctionsMOYENNE, MAX, MIN, etc.

Dans la figure ci-dessous apparaissent les chiffres de ventepour 999 modèles sur 5 ans. Or, vous souhaitez obtenir en ligne 1000les totaux pour chaque année. Il vous suffit de :1 Sélectionner la plage de cellules B1000:F1000.2 Cliquer sur le bouton Σ. Excel entre automatiquement la somme

dans les cinq colonnes.

Le résultat apparaît dans la figure ci-dessous.

Figure 3–4 Sélectionnez les cellules devant afficher le total des chiffres de ventes pour chaque année.

Figure 3–5 Dès que le bouton de somme automatique est pressé, Excel entre la formule dans les cinq colonnes.


46

Vérifiez les formules de manière à vous assurer que la référence de laplage de cellules entrée automatiquement par Excel correspond bienà ce que vous souhaitez.

Dans cet exemple, les années saisies en ligne 1 sont précédées d’uneapostrophe (invisible dans la cellule), ce qui leur confère une naturede texte. Sans cette précaution, la somme automatique aurait intégrécette première ligne à la référence de la plage de cellules à addi-tionner. Excel introduit dans sa référence toutes les valeurs numéri-ques qu’il rencontre sur son passage, jusqu’à ce qu’il tombe sur unevaleur de texte ou une autre formule.

En utilisant le bouton Σ à partir d’une cellule unique, Excel ne validepas automatiquement la formule. Il se contente de vous la suggérer, etattend que vous appuyez Entrée pour valider cette formule.

Immédiatement à droite du bouton Somme automatique, vous trouvezune flèche. Utilisez-la pour choisir une autre fonction commeMoyenne, Max ou Min. Sélectionnez la plage B1000:F1000, cliquezsur la flèche et choisissez Moyenne afin d’entrer une formule capablede calculer la moyenne des ventes par modèle.

Figure 3–6 Pressez la touche Entrée pour valider votre formule.

Figure 3–7 Sélection de la fonction Moyenne à partir de la flèche liée au bouton Somme automatique.

FormulesCHAPITRE 3

47

Découvrir de nouvelles fonctionsComment procéder pour calculer la médiane pour chacune descolonnes ? Vous pouvez soit cliquer sur le bouton fx qui se trouve àl’extrême gauche du ruban, sous l’onglet Formules, soit choisir cettefonction à partir de la liste déroulante attachée à l’un des boutonsprésentant ces fonctions par thème, à partir du même onglet.

Les fonctions d’Excel sont proposées à travers sept catégories, dontune divisée en quatre sous-catégories. Elles apparaissent sous laforme de sept boutons :• Financier• Logique• Texte• Date et heure• Recherche et référence• Maths et trigonométrie

Figure 3–8 Excel calcule la moyenne à partir des données de chacune des colonnes.

Figure 3–9 Utilisez le bouton fx pour partir à la recherche de nouvelles fonctions.


48

• Plus de fonctions– Statistiques– Ingénierie– Cube– Information

Vous trouvez également les deux boutons suivants qui reprennentcertaines des fonctions présentées dans les catégories ci-dessus :• Utilisées récemment• Somme automatique

Retrouver une fonction au sein de sa catégorie n’est pas nécessaire-ment une tâche facile car la fonction SOMME, par exemple, est rangéedans les fonctions Maths et trigonométrie, alors que MOYENNE se trouvedans la catégorie Statistiques qui est elle-même une sous-catégorie dubouton Plus de fonctions.

Au lieu de vous perdre dans les méandres des diverses catégories, cli-quez sur le bouton fx. À partir de la boîte de dialogue qui apparaît,tapez quelques mots dans la case de recherche et cliquez sur OK. Si vous

Figure 3–10 Déroulez la liste des fonctions appartenant à une catégorie à partir de la boîte de dialogue Insérer une fonction.

FormulesCHAPITRE 3

49

tapez Moyenne, Excel vous suggère d’utiliser non seulement la fonctionMOYENNE, mais également la fonction MOYENNE.SI ou encoreMOYENNE.HARMONIQUE et bien d’autres fonctions encore.

En tapant Médiane dans la case de recherche, Excel renvoie une listede fonctions comprenant MEDIANE mais également MODE, QUARTILE etbien d’autres encore.

Parfois vous connaissez le nom de la fonction, mais vous avez oubliécelui des arguments nécessaires à son calcul. Excel vous aide, puisque

Figure 3–11 À partir d’un mot, Excel ne vous suggère pas une fonction unique, mais il vous propose de nombreuses alternatives.

BON À SAVOIR Découvrez la macro complémentaire Analysis Toolpak

Environ un tiers des fonctions ne sont disponibles qu’après l’activation préalable de la macrocomplémentaire Analysis Toolpak. Si les fonctions que vous devez utiliser ne se limitent pas àSOMME et MOYENNE, prenez le temps d’explorer la liste des fonctions et activez la macro com-plémentaire Analysis Toolpak. Pour cela : 1. Cliquez sur le bouton Office. 2. Cliquez sur le bouton Options Excel (dans le coin inférieur droit du grand menu Office).3. Faites votre choix dans la fenêtre correspondant à la section Compléments.


50

dès la saisie de la parenthèse d’ouverture, il vous indique le nom etl’ordre des arguments au sein d’un petit cartouche jaune qui apparaîtjuste sous votre saisie.

Quoi qu’il en soit, vous avez toujours la possibilité d’utiliser le raccourciclavier Ctrl+A juste après avoir tapé la parenthèse d’ouverture. Ce rac-courci affiche la boîte de dialogue de l’Assistant fonction qui vous aideà saisir vos arguments. Pour chaque argument, elle affiche une petiteexplication. Par exemple, dans la figure suivante, Excel suggère la solu-tion à adopter pour travailler avec des versements trimestriels.

Figure 3–12 Un petit cartouche jaune apparaît automatiquement et vous indique la syntaxe correcte pour saisir votre formule.

Figure 3–13 Taper Ctrl+A immédiatement après la parenthèse d’ouverture d’une fonction affiche l’assistant fonction, au niveau de l’écran de saisie des arguments.

FormulesCHAPITRE 3

51

Si votre modèle prévoit des remboursements mensuels, vous pouvezutiliser B2/12, mais mieux vaut utiliser la formule (1+B2)^(1/12)-1.

La saisie de tous les arguments affichés en gras est obligatoire. Lasaisie des arguments qui apparaissent en caractères non gras estoptionnelle.

Une fois l’ensemble des arguments saisis, Excel vous indique lerésultat et construit parallèlement la formule dans la cellule danslaquelle vous avez commencé votre saisie.

Figure 3–14 Entrer l’argument Taux au sein de la boîte de dialogue de l’assistant fonction.

Figure 3–15 Les résultats apparaissent instantanément dans la boîte de dialogue.


52

Si l’aide dispensée au sein de cette boîte de dialogue s’avère insuffi-sante, vous avez toujours la possibilité de cliquer sur le bouton Aidesur cette fonction. Vous disposez alors d’une page complète d’explica-tions avec quelques exemples d’utilisation.

Recopier une formuleSi vous entrez une somme en passant par le bouton Somme automa-tique, vous pouvez sélectionner au préalable une plage de plusieurscellules. Votre somme sera entrée dans toutes les cellules de la plagesélectionnée. En revanche, si vous choisissez Autres fonctions à partirdu menu déroulant Somme automatique, lors de la validation de votrefonction, Excel ne l’entrera que dans la première cellule de votresélection (à moins que vous ne mainteniez la touche Ctrl appuyéependant la validation de votre formule).

Figure 3–16 Accédez à une aide complète pendant la saisie de votre formule.

FormulesCHAPITRE 3

53

Dans ce cas, vous pouvez faire appel à la poignée de recopie.1 Sélectionnez la cellule contenant la formule.

2 Cliquez sur la poignée de recopie et glissez vers la droite pourrecopier la formule dans les cellules voisines.

Excel recopie la formule dans toutes les cellules balayées par votre cliquer-glisser.

B.A.-BA La poignée de recopie

Vous remarquerez que dans le coin inférieur droit du cadre épais qui signale la cellule sélection-née, apparaît un carré noir. C’est ce que l’on appelle la poignée de recopie. Lorsque vous ame-nez le curseur de votre souris au dessus de cette poignée, il se transforme en signe plus.

Figure 3–17 Le signe plus indique que vous êtes prêt à utiliser la poignée de recopie.

Figure 3–18 Les résultats des diverses formules apparaissent au fur et à mesure du cliquer-glisser.

ASTUCE Recopie express

Si vous devez recopier une formule dans une colonne et que celle qui est située immédiatementà gauche n’est pas vide, vous pouvez tenter un double-clic sur la poignée de recopie (au lieu ducliquer-glisser). Excel observe le comportement de la colonne de gauche et recopie la formuletant qu’il trouve des valeurs, et dès qu’il rencontre une cellule vide, il interrompt la recopie.


54

Afficher les formules

Les données du problèmeImaginons qu’un collaborateur vous envoie une feuille de calcul vrai-ment confuse. Comment pouvez-vous repérer rapidement les cellulescontenant une formule ?

Comment s’y prendreExcel propose un mode d’affichage dans lequel on peut visualisertoutes les formules simultanément.1 Utilisez le raccourci clavier Ctrl+".

Ce raccourci correspond à la commande Afficher les formules,disponible à partir de l’onglet Formules du ruban.Dans ce mode d’affichage, Excel élargit toutes les colonnes car ilestime qu’il faut davantage de place pour afficher les formules quepour afficher leur résultat.

Figure 3–19 Voici l’exemple d’une feuille dans laquelle la visualisation des formules pourrait être utile pour mieux comprendre son fonctionnement.

FormulesCHAPITRE 3

55

2 Utilisez à nouveau le raccourci clavier Ctrl+" pour revenir au moded’affichage normal.

Plus en détailVous pouvez aussi passer par la commande Sélectionner les cellulespour sélectionner simultanément toutes les cellules contenant uneformule :1 À partir du ruban, activez l’onglet Accueil et choisissez Rechercher

et sélectionner > Sélectionner les cellules.2 Dans la boîte de dialogue qui apparaît, cliquez sur Formules, puis

sur OK.

Figure 3–20 Affichage des formules.

Figure 3–21 Cliquer sur l’option Formules pour sélectionner simultanément toutes les cellules contenant des formules.


56

3 Une fois les cellules sélectionnées, vous pouvez leur appliquer unecouleur de fond afin de les repérer plus facilement, ou même don-ner un nom à cette sélection, afin de pouvoir la sélectionner par lasuite, plus directement.

Grâce à la couleur, il est plus facile de repérer les cellules contenantune formule, ce qui constitue un premier pas vers une meilleure com-préhension du modèle.

Références absoluesN’avez-vous jamais été plongé dans des abîmes de perplexité par desvaleurs d’erreur récalcitrantes apparues à l’occasion de la recopie deformules ? L’expérience prouve que certaines approximations surve-nues lors de la manipulation des références en sont souvent la cause.En fait, une référence absolue désigne précisément l’adresse d’unecellule, et non pas le parcours à suivre pour atteindre cette celluledepuis la formule (parcours qui, par définition, désignera une autrecellule lorsque la formule sera recopiée).

Figure 3–22 Appliquer une couleur de fond à la sélection.

FormulesCHAPITRE 3

57

Les données du problèmeLe tableau ci-dessous présente les chiffres de ventes pour diversmodèles.

Pour calculer les prévisions pour les années suivantes, entrez en C4 laformule =B4*1,05 et recopiez-la dans les autres cellules (en colonneet en ligne).

Cela ne vous surprend peut-être pas, mais il est quand même trèsétonnant qu’Excel décline les références pendant la recopie des for-mules, et fasse par exemple passer B4 à C5 lorsque la formule est reco-piée de C4 en D5.

Figure 3–23 Un tableau présentant des chiffres de ventes.

Figure 3–24 Calculez les ventes prévisionnelles en recopiant une formule construite à partir d’une référence relative et d’une constante.


58

Maintenant, au lieu d’entrer 1,05 « en dur » dans la formule, entrezplutôt cette valeur en D1 (D1 a été formatée pour afficher un pourcen-tage), et en C4, modifiez la formule pour qu’elle devienne =B4*D1.Vous obtenez le même résultat.

Mais hélas, lorsque vous recopiez cette formule dans les autres cel-lules, ça ne va pas. Toutes les prévisions sont à zéro !

Lorsque vous recopiez la formule de C4 vers C5, B4 se transformeen B5 (ce qui est parfait), mais D1 se transforme en D2, ce qui ne cor-respond absolument pas à vos attentes !

Comment faire pour que D1 reste inchangée pendant la copie de laformule ?

Comment s’y prendreLes références comme B4 ou D1 sont appelées références relatives.Lorsque vous copiez une formule utilisant ce type de références, elles se

Figure 3–25 Calculer un chiffre de ventes prévisionnelles à l’aide d’une formule mettant en œuvre deux références relatives.

Figure 3–26 Recopier la formule précédente produit des résultats désastreux.

FormulesCHAPITRE 3

59

déclinent. Dans la plupart des situations, ce comportement vous con-vient, mais parfois, vous voulez qu’une référence désigne toujours lamême cellule. C’est le cas dans l’exemple traité ici pour lequel vous sou-haitez que votre formule pointe toujours sur la cellule D1 qui contient letaux de croissance que vous souhaitez appliquer à toutes vos prévisions.

Pour figer cette référence, il faut la transformer en référence absolue.On opère cette transformation en ajoutant le symbole dollar ($)devant la lettre de la colonne et devant le numéro de la ligne. Dansnotre exemple, il faut que la formule de la cellule C4 ressembleà =B4*$D$1. Dès lors, la recopie de la formule continue detransformer B4 en B5, B6, etc. alors que D1 demeure inchangée. Rienne vous empêche de modifier la valeur de D1 et de vérifier l’impact dece changement sur toutes vos prévisions.

Vous n’êtes pas obligé de figer les deux dimensions d’une référence. Sivous voulez utiliser une référence comme =C$1 (seule la ligne estfigée), vous utilisez une référence semi-relative. Cette semi-relativitépeut d’ailleurs être inversée, et vous pouvez utiliser une référencecomme =$C1 (seule la colonne est figée).

Plus en détailPour faciliter la transformation d’une référence relative en référenceabsolue, vous pouvez utiliser la touche F4. Pendant la construction devotre formule, appuyez la touche F4 immédiatement après avoir entréla référence à modifier. En utilisant cette touche plusieurs fois de

Figure 3–27 Utiliser des références absolues pour appliquer simultanément à plusieurs formules une nouvelle valeur de paramètre.


60

suite, vous faites passer votre référence par ses quatre états possibles(D1, $D$1, D$1 et $D1).

Formules matricielles

Les données du problèmeVous vous retrouvez avec 5 000 enregistrements consignant à chaquefois une quantité et un prix unitaire. Vous souhaitez calculer lasomme des produits de chaque quantité par chaque prix unitaire.

Comment s’y prendreVous pouvez toujours entrer la formule =C2*D2 en E2, recopier cetteformule vers le bas, et faire la somme de l’ensemble.

Tableau 3–2 Cycle des divers états d’une référence transformée par la touche F4

Presser F4 Résultat Nom de la référence

État initial =D1 Relative

Presser F4 une fois =$D$1 Absolue

Presser F4 deux fois =D$1 Semi-relative en colonne

Presser F4 trois fois =$D1 Semi-relative en ligne

Presser F4 quatre fois =D1 Relative

Figure 3–28 Un petit morceau des 5 000 enregistrements à partir desquels vous souhaitez calcu-ler la somme des produits de chaque quantité par chaque prix unitaire.

FormulesCHAPITRE 3

61

Mais si vous n’êtes intéressé que par le résultat final, il est inutile defaire tous ces produits intermédiaires.

Vous pouvez alors mettre en œuvre une formule matricielle.1 En D5001, entrez la formule =SOMME(C2:C5000*D2:D5000). 2 Cette formule peut paraître erronée, mais pour la valider, au lieu

de presser simplement la touche Entrée, maintenez les touchesCtrl+Maj enfoncées pendant que vous actionnez la touche Entrée.

Au moment où vous pressez les touches Ctrl+Maj+Entrée, Excel réaliseles 5 000 produits, les additionne et renvoie le résultat dans la celluleen encadrant la formule par des accolades.

Figure 3–29 Procédure habituelle avec une simple fonction SOMME.

Figure 3–30 Validez une formule matricielle à l’aide des touches Ctrl+Maj+Entrée. La formule, une fois validée, est encadrée d’accolades.


62

Plus en détailMettre en œuvre quelques formules matricielles ne pose pas de pro-blème particulier. En revanche, si vous en abusez au sein d’un mêmeclasseur, le temps nécessaire au calcul de toutes ces formules risque devous paraître incroyablement long. Si c’était le cas, vous pourriez tou-jours déconnecter le calcul automatique de votre classeur (en choisis-sant, à partir du ruban, l’onglet Formules > Options de calcul > Manuel).

Figure 3–31 Utilisez une formule matricielle pour additionner les 5 000 produits.

MNÉMOTECHNIQUE CME

Si vous avez quelques difficultés à vous souvenir de la séquence de touches à presser pour vali-der les formules matricielles, retenez le sigle CME pour Ctrl+Maj+Entrée.

Documents

Excel Marketing