Analyse marketing et reporting avec Excel


Analyse marketing et reporting avec Excel

 

excel er et reporting marketing

Analyse marketing et reporting avec Excel Ivana Taylor Bill Jelen 12251_Excel_FauxTitre 12/12/07 14:09 Page 1 01_pdt_blaess.indd 3 13/12/07 10:39:55 © Groupe Eyrolles, 2008, pour la présente édition ISBN : 978-2-212-12251-0 Entrer une formule Les données du problème Pour obtenir les valeurs des ventes futures fondées sur une certaine hypothèse de taux de croissance, vous devez utiliser une formule. Comment s?y prendre Observez la feuille de calcul présentée ci-dessous. Le montant des ventes pour l?année 2006 s?élève à 1 774 000. Ce montant apparaît en 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. 3 Formules 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 Excel CHAPITRE 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étail Il 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 touches de direction. C?est la méthode de prédilection de ceux qui ont connu 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é. Formules CHAPITRE 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érateurs Voici 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érations Le moteur de calcul d?Excel exécute les opérations dans un ordre pré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 Analyse marketing et reporting avec Excel CHAPITRE 3 44 Si vous considérez la formule =A2+B2*C2^D2, Excel commence par calculer C2^D2. Puis il multiplie B2 par le résultat qu?il vient de trouver. Et enfin, il finit par ajouter A2 avant de renvoyer le résultat final dans la cellule. Vous pouvez intervenir sur cet ordre en ajoutant des parenthèses : =(A2+B2)*C2^D2. Les fonctions préprogrammées Parallèlement aux opérateurs de base, Excel offre plus de 350 fonc- tions préprogrammées très puissantes. Certaines fonctions comme SOMME 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 de mettre en ?uvre l?addition de base pour écrire =A2+A3+A4+A5. Plus vous avez de données à additionner, plus le choix de la fonction SOMME devient crucial. Il est bien évident qu?entrer une formule comme =SOMME(A2:A999) est beaucoup plus rapide que de saisir quelque chose 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. Formules CHAPITRE 3 45 Entrer une fonction depuis le ruban Au niveau de l?onglet Formules du ruban, vous pouvez voir la lettre grecque ?. Ce bouton correspond à l?insertion automatique de la fonction SOMME. Si, au lieu de cliquer directement sur le bouton, vous utilisez la liste déroulante, vous avez un accès direct aux fonctions MOYENNE, MAX, MIN, etc. Dans la figure ci-dessous apparaissent les chiffres de vente pour 999 modèles sur 5 ans. Or, vous souhaitez obtenir en ligne 1000 les 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. Analyse marketing et reporting avec Excel CHAPITRE 3 46 Vérifiez les formules de manière à vous assurer que la référence de la plage 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?une apostrophe (invisible dans la cellule), ce qui leur confère une nature de 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 une valeur de texte ou une autre formule. En utilisant le bouton ? à partir d?une cellule unique, Excel ne valide pas automatiquement la formule. Il se contente de vous la suggérer, et attend que vous appuyez Entrée pour valider cette formule. Immédiatement à droite du bouton Somme automatique, vous trouvez une flèche. Utilisez-la pour choisir une autre fonction comme Moyenne, Max ou Min. Sélectionnez la plage B1000:F1000, cliquez sur la flèche et choisissez Moyenne afin d?entrer une formule capable de 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. Formules CHAPITRE 3 47 Découvrir de nouvelles fonctions Comment procéder pour calculer la médiane pour chacune des colonnes ? Vous pouvez soit cliquer sur le bouton fx qui se trouve à l?extrême gauche du ruban, sous l?onglet Formules, soit choisir cette fonction à partir de la liste déroulante attachée à l?un des boutons présentant ces fonctions par thème, à partir du même onglet. Les fonctions d?Excel sont proposées à travers sept catégories, dont une divisée en quatre sous-catégories. Elles apparaissent sous la forme 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. Analyse marketing et reporting avec Excel CHAPITRE 3 48 ? Plus de fonctions ? Statistiques ? Ingénierie ? Cube ? Information Vous trouvez également les deux boutons suivants qui reprennent certaines 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ée dans les fonctions Maths et trigonométrie, alors que MOYENNE se trouve dans la catégorie Statistiques qui est elle-même une sous-catégorie du bouton 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. Formules CHAPITRE 3 49 tapez Moyenne, Excel vous suggère d?utiliser non seulement la fonction MOYENNE, mais également la fonction MOYENNE.SI ou encore MOYENNE.HARMONIQUE et bien d?autres fonctions encore. En tapant Médiane dans la case de recherche, Excel renvoie une liste de fonctions comprenant MEDIANE mais également MODE, QUARTILE et bien 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 macro complé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. Analyse marketing et reporting avec Excel CHAPITRE 3 50 dès la saisie de la parenthèse d?ouverture, il vous indique le nom et l?ordre des arguments au sein d?un petit cartouche jaune qui apparaît juste sous votre saisie. Quoi qu?il en soit, vous avez toujours la possibilité d?utiliser le raccourci clavier 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 petite explication. 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. Formules CHAPITRE 3 51 Si votre modèle prévoit des remboursements mensuels, vous pouvez utiliser 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. La saisie des arguments qui apparaissent en caractères non gras est optionnelle. Une fois l?ensemble des arguments saisis, Excel vous indique le résultat et construit parallèlement la formule dans la cellule dans laquelle 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. Analyse marketing et reporting avec Excel CHAPITRE 3 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 Aide sur cette fonction. Vous disposez alors d?une page complète d?explica- tions avec quelques exemples d?utilisation. Recopier une formule Si vous entrez une somme en passant par le bouton Somme automa- tique, vous pouvez sélectionner au préalable une plage de plusieurs cellules. Votre somme sera entrée dans toutes les cellules de la plage sélectionnée. En revanche, si vous choisissez Autres fonctions à partir du menu déroulant Somme automatique, lors de la validation de votre fonction, Excel ne l?entrera que dans la première cellule de votre sélection (à moins que vous ne mainteniez la touche Ctrl appuyée pendant la validation de votre formule). Figure 3?16 Accédez à une aide complète pendant la saisie de votre formule. Formules CHAPITRE 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 pour recopier 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 du cliquer-glisser). Excel observe le comportement de la colonne de gauche et recopie la formule tant qu?il trouve des valeurs, et dès qu?il rencontre une cellule vide, il interrompt la recopie. Analyse marketing et reporting avec Excel CHAPITRE 3 54 Afficher les formules Les données du problème Imaginons qu?un collaborateur vous envoie une feuille de calcul vrai- ment confuse. Comment pouvez-vous repérer rapidement les cellules contenant une formule ? Comment s?y prendre Excel propose un mode d?affichage dans lequel on peut visualiser toutes 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 il estime qu?il faut davantage de place pour afficher les formules que pour 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. Formules CHAPITRE 3 55 2 Utilisez à nouveau le raccourci clavier Ctrl+" pour revenir au mode d?affichage normal. Plus en détail Vous pouvez aussi passer par la commande Sélectionner les cellules pour sélectionner simultanément toutes les cellules contenant une formule : 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. Analyse marketing et reporting avec Excel CHAPITRE 3 56 3 Une fois les cellules sélectionnées, vous pouvez leur appliquer une couleur 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 la suite, plus directement. Grâce à la couleur, il est plus facile de repérer les cellules contenant une formule, ce qui constitue un premier pas vers une meilleure com- préhension du modèle. Références absolues N?avez-vous jamais été plongé dans des abîmes de perplexité par des valeurs d?erreur récalcitrantes apparues à l?occasion de la recopie de formules ? 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?une cellule, et non pas le parcours à suivre pour atteindre cette cellule depuis la formule (parcours qui, par définition, désignera une autre cellule lorsque la formule sera recopiée). Figure 3?22 Appliquer une couleur de fond à la sélection. Formules CHAPITRE 3 57 Les données du problème Le tableau ci-dessous présente les chiffres de ventes pour divers modèles. Pour calculer les prévisions pour les années suivantes, entrez en C4 la formule =B4*1,05 et recopiez-la dans les autres cellules (en colonne et 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. Analyse marketing et reporting avec Excel CHAPITRE 3 58 Maintenant, au lieu d?entrer 1,05 « en dur » dans la formule, entrez plutô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 transforme en 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 la formule ? Comment s?y prendre Les 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. Formules CHAPITRE 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 la mê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 le taux 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. Dans notre exemple, il faut que la formule de la cellule C4 ressemble à =B4*$D$1. Dès lors, la recopie de la formule continue de transformer B4 en B5, B6, etc. alors que D1 demeure inchangée. Rien ne vous empêche de modifier la valeur de D1 et de vérifier l?impact de ce changement sur toutes vos prévisions. Vous n?êtes pas obligé de figer les deux dimensions d?une référence. Si vous voulez utiliser une référence comme =C$1 (seule la ligne est figé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érence comme =$C1 (seule la colonne est figée). Plus en détail Pour faciliter la transformation d?une référence relative en référence absolue, vous pouvez utiliser la touche F4. Pendant la construction de votre 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. Analyse marketing et reporting avec Excel CHAPITRE 3 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ème Vous vous retrouvez avec 5 000 enregistrements consignant à chaque fois une quantité et un prix unitaire. Vous souhaitez calculer la somme des produits de chaque quantité par chaque prix unitaire. Comment s?y prendre Vous pouvez toujours entrer la formule =C2*D2 en E2, recopier cette formule 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. Formules CHAPITRE 3 61 Mais si vous n?êtes intéressé que par le résultat final, il est inutile de faire 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 touches Ctrl+Maj enfoncées pendant que vous actionnez la touche Entrée. Au moment où vous pressez les touches Ctrl+Maj+Entrée, Excel réalise les 5 000 produits, les additionne et renvoie le résultat dans la cellule en 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. Analyse marketing et reporting avec Excel CHAPITRE 3 62 Plus en détail Mettre en ?uvre quelques formules matricielles ne pose pas de pro- blème particulier. En revanche, si vous en abusez au sein d?un même classeur, le temps nécessaire au calcul de toutes ces formules risque de vous 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.

PARTAGER SUR

Envoyer le lien par email
2407
READS
16
DOWN
7
FOLLOW
4
EMBED
DOCUMENT # TAGS
#excel  #reporting  #marketing 

licence non indiquée


DOCUMENT # INDEX
Publicite, Marketing 
img

Partagé par  mistigri

 Suivre

Auteur:Ivana Taylor Bill Jelen
Source:Non communiquée