4 façons de créer un tableau !

Supposons que vous ayez un tableau tout simple – en A1:D4 – dans lequel vous souhaitez calculer, pour les cellules vides, le total du « titre » de ligne et du « titre » de colonne. Il y a quatre façons de réaliser cela, et nous l’avons fait dans les quatre blocs ci-dessous :

1. Formules avec coordonnées mixtes

Dans le premier bloc, A1:D4, nous avons utilisé la solution la plus naturelle, avec en B2 la formule =B$1+$A2, ensuite recopiée dans le reste du bloc. Ceci dit, pour bien écrire cette formule, il faut maîtriser l’utilisation des « $ ».

2. Formules utilisant des noms

Pour le second bloc, F1:I4, nous avons commencé par donner le nom « Lig » à G1:H1 et le nom « Col » à F2:F4. Puis nous avons entré en G2 la formule =lig+col, ensuite recopiée dans le reste du bloc. Cette formule est plus lisible ; en outre, on se passe totalement des « $ » !

3. Formule matricielle

Pour le bloc A6:D9, nous avons sélectionné le bloc B7:D9 puis entré la formule =A7:A9+B6:D6 que nous avons validée en « formule matricielle » avec [Ctrl]-[Maj]-[Entrée].

4. Table à deux entrées

Pour le dernier bloc, nous avons entré en F6 la formule =E6+E7, qui n’affiche rien de visible ici car nous avons choisi une police blanche pour le résultat ! Ensuite, nous avons sélectionné le bloc F6:I9 puis défini une table à deux entrées avec E6 en paramètre de ligne et E7 en paramètre de colonne. Une façon détournée d’utiliser les tables, certes, mais ça marche !

Voici les formules de ce modèle :

Utilisation astucieuse des tables

Prenons une liste (ex-base de données) d’Excel, avec ici 25 personnes et six champs par personne. Supposons que nous souhaitions calculer le salaire moyen par pays. Nous pourrions certes constituer à cet effet un tableau croisé dynamique, mais il y a probablement mieux à faire avec une table.

Créons en colonne H (cf. ci-dessous) la liste des pays, entrons dans la cellule I1 la formule =bdmoyenne(A1:F26;E1;K1:K2), où K1:K2 est une zone de critères dans laquelle la seconde cellule est vide – ce qui nous donnera donc en I1 le salaire moyen global – sélectionnons le bloc H1:I20, appelons la commande
« Table » du menu Données et remplissons le dialogue comme ci-dessous :

Dès que nous validons par « OK », nous obtenons les salaires moyens par pays.

Remarque 1 – L’avantage de cette solution par rapport au résultat que nous aurions obtenu avec un tableau croisé dynamique est que cette table est réellement dynamique : dès que l’on modifie un salaire dans la base, cela impacte immédiatement les valeurs de la table.

Remarque 2 – Il suffit de modifier la formule en I1 pour décliner d’autres résultats par pays, par exemple en entrant la formule =bdecartype(A1:F26;E1;K1:K2) ou =bdmax(A1:F26;E1;K1:K2).

Table de données à deux entrées

Il y a quatre jours, nous avons appris à créer une table de données à une entrée. Dans une telle table, on fait varier un paramètre et l’on calcule ce que cela donne pour une ou plusieurs formules.

Aujourd’hui, nous faisons varier deux paramètres – le prix de vente initial et l’élasticité – et nous nous intéresserons à ce que cela donne pour la marge bénéficiaire :

En D6, nous faisons référence à la cellule B17, celle de la marge bénéficiaire. Pour créer la table à deux entrées, nous sélectionnons le bloc D6:I17, nous appelons la commande « Table » du menu Données, nous le remplissons comme ci-dessus – B6 pour l'entrée en ligne et B3 pour l'entrée en colonne – puis nous validons par OK.

Aussitôt – à condition bien entendu que l’on se trouve en mode de calcul automatique – nous obtenons le résultat ci-dessous :

Remarque 1 – Si nous modifions par exemple la définition de D6 pour y entrer la formule =B15, c’est à présent le chiffre d’affaires qui sera décliné par la table.

Remarque 2 – Le moyen mnémotechnique pour ne pas se tromper lors de la définition de la table est de noter que la cellule d’entrée en ligne (resp. colonne) est celle où l’on souhaite que les valeurs du paramètre horizontal (resp. vertical) aillent successivement se loger.

Remarque 3 – Cette table comporte 55 éléments et calcule par conséquent 55 fois le classeur. Si vous aviez un classeur conséquent dont le temps de calcul normal serait d’une minute, son temps de calcul pourrait prendre jusqu’à 55 minutes avec une telle table ! C’est pourquoi Excel vous propose un mode de calcul « Automatique sauf les tables ».

Remarque 4 – Si l’on voulait analyser les conséquences de la variation de trois paramètres ou plus, il faudrait utiliser un add-in de simulation probabiliste tel que Crystal Ball.

Table de données à une entrée

Excel dispose d’une fonctionnalité à la fois puissante et très mal documentée dans l’aide – encore plus que le reste, ce qui n’est pas peu dire ! – la table de données.

Aujourd’hui, nous en verrons la forme la plus simple, la table de données à une entrée. Nous le faisons à partir d’un modèle simple de calcul de marge où nous vendons un produit. Il s’en vend 5.000 exemplaires si le prix unitaire est de 150 €, et – pour chaque € en moins du prix de vente – il s’en vend 80 exemplaires de plus. Nous voyons ci-dessous le modèle résultant.

Nous souhaitons voir comment la marge bénéficiaire évolue en fonction du prix de vente. Pour cela, nous avons constitué en D6:F17 une table pour différentes valeurs de ce prix en colonne D. La cellule E6 fait référence à B13, et la cellule F6 à B17, comme le montrent les flèches d’audit.

Nous appelons alors la commande « Table » du menu Données puis indiquons (cf. ci-dessus) que la « cellule d’entrée en colonne » est la cellule B3, en cliquant dans cette cellule. Il suffit alors de valider par « OK » pour obtenir la table désirée (nous en avons ôté les flèches d’audit) :

Remarque 1 – Une table est naturellement protégée. Si par exemple vous sélectionnez les lignes 12:16 et que vous utilisez la commande « Supprimer » du menu Edition, Excel refuse en déclarant : « Il est impossible de modifier une partie d’une table ».

Remarque 2 – Si la table ne s'est pas calculée, il faut soit forcer un calcul via la touche [F9], soit – de façon plus permanente – passer par la commande
« Options » du menu Outils, onglet « Calcul », puis demander le mode de calcul « Automatique ».

Mes prochaines formations

Avez-vous encore envie de progresser ?

Voici quelques détails sur le contenu des prochaines formations :

· La modélisation du risque (Crystal Ball), le mardi 19 décembre 2006 : découvrir la modélisation du risque à l’aide d’Excel et de l’add-in Crystal Ball.

· Modélisation avec Excel, mercredi 13 et mercredi 20 décembre 2006 : en deux jours, passez du niveau d'utilisateur lambda (ou même évolué) d'Excel à celui d'expert Excel.

· Word et Powerpoint efficaces, le mercredi 6 décembre 2006 : vous savez comment je parviens à tirer parti d'Excel. Vous pourrez de la même façon apprendre à tirer le maximum de Word et de Powerpoint !

Quelques références d'entreprises dans lesquelles ces formations ont été assurées en intra, avec toujours un taux de satisfaction très élevé :
Aéroports de Paris, Arianespace, Artémis, ASF, Bouygues, CNES, CNET, EADS, EdF, Ernst & Young, Finacor, France Telecom, Johnson & Johnson, Lilly France, MBDA, Sanofi-Aventis, Smithkline Beecham, Total.

Remarque 1 - Pour toutes ces formations, nous nous engageons à ce qu'il y ait un ordinateur par personne et au maximum huit personnes.

Remarque 2 - Pour voir et télécharger le programme détaillé des formations, cliquez sur les liens dans la partie droite de ce blog.

Création de sous-totaux

Vous disposez d’une table avec les ventes de certaines quantités de produits, avec des codes utilisés pour représenter à chaque fois le type de client, ou le secteur géographique concerné. Vous souhaitez obtenir les sous-totaux par valeur du code.

Pour cela, vous placez le curseur n’importe où dans la table, vous appelez la commande « Sous-totaux » du menu Données et vous sélectionnez les options ci-dessus – ou d’autres si vous préférez. Dès que vous avez validé par « OK », vous obtenez le résultat attendu :

Excel a donc fait tout ce que vous lui aviez demandé ; en prime, il a aussi créé des groupes vous permettant d’ouvrir ou de fermer les blocs intérieurs ou même l’ensemble des données initiales.

Remarque 1 – Il n’a cependant pas pensé à redimensionner la colonne B qui cache encore une partie de l’information. Après correction, on obtient :

Remarque 2 – Vous pouvez même – à tout moment – revenir rapidement à la situation antérieure : pour cela, mettez le curseur n’importe où dans la table, appelez de nouveau la commande « Sous-totaux» et cliquez sur le bouton
« Supprimer tout » !

Un add-in pour vos « bases »

Quand on utilise une base de données Excel – une « liste » donc, puisque notre brave Excel est capable de plein de choses, mais certainement pas de gérer une véritable base de données – il arrive que l’on souhaite réaliser des extractions en série selon certains critères.

Par exemple, vous avez une liste de salariés, avec plein de renseignements les concernant et vous souhaitez – à partir de cette liste – créer une feuille par département. Vous n’avez pas vraiment envie de faire cela avec une série d’extractions manuelles, suivies de copier/coller dans de nouvelles feuilles, étant donné qu’Excel est hélas incapable naturellement de faire une extraction vers une feuille autre que celle ou se trouve la « base ».

La solution que vous attendez est alors l’add-in « Data Explosion ». Cet add-in est une création de Mike Alexander, que nous tenons à féliciter pour son ouvrage.

Prenons la « base » exemple ci-dessous. Nous invoquons la commande
« Excel-Explosion » qui apparaît dans un menu ajouté à la barre de menus d’Excel par l'add-in. Il suffit alors de remplir le dialogue comme suit puis de cliquer sur le bouton « Explode Now » pour que l’add-in ajoute dans le classeur une feuille pour chaque âge avec – dans cette feuille – l’extraction de toutes les fiches des personnes ayant cet âge.

Remarque 1 – Notons la présence de la commande « First Column is a compound key » qui permet de créer par formule un critère d’éclatement personnalisé.

Remarque 2 – L’add-in est livré avec un tutorial sous la forme de vidéo. C’est rare et tout à fait appréciable, surtout si vous êtes anglophone :)

Remarque 3 – Tous les abonnés au blog recevront gratuitement cet add-in dans leur prochaine livraison.

Le blog "Monsieur Excel" : un an !

J’ai créé le blog « Monsieur Excel » il y a un an exactement, après avoir publié durant 15 ans – d’octobre 1990 à août 2005 – la lettre d’Excel, qui était éditée tous les deux mois avec, accompagnant chaque numéro, une disquette qui fut sur la fin remplacée par l’envoi des fichiers par e-mail.

Le nombre de lecteurs du blog est en croissance régulière, comme en témoigne le graphe ci-dessous, avec en moyenne 150 visites par jour ouvrable ce dernier mois. En un an, il y a eu quasiment 30.000 visites.

Je propose un abonnement au blog à 150 euros TTC (cf. le message à la droite du blog). Il y a aussi un abonnement « entreprise » grâce auquel l’entreprise abonnée acquiert le droit de mettre sur son réseau local tous les fichiers du blog. Il y a actuellement une vingtaine de personnes et plusieurs entreprises abonnées.

Les abonnés reçoivent tous les deux mois un document Acrobat reprenant – avec une table des matières comportant des liens hypertexte – tous les articles publiés dans le blog, un classeur Excel dont les feuilles illustrent la mise en œuvre de tous les conseils du blog, avec fréquemment des explications ou exemples en supplément, ainsi qu’un classeur Excel permettant de retrouver rapidement tel ou tel conseil. Des goodies (modèle Excel, macro, add-in) sont fournis avec chaque livraison bimestrielle.

Pour en terminer avec cet article « promotionnel », sachez que je suis disponible pour :
- la réalisation de modèles Excel performants
- l’audit et l’amélioration de modèles existants
- la formation à l’utilisation avancée d’Excel
- toute étude de modélisation du risque

En ce qui concerne l’audit et l’amélioration de modèles existants, j’ai ramené – dans deux entreprises – des modèles de respectivement 40 et 50 Mo à moins de 10 Mo chacun. J’ai aussi augmenté de façon extrêmement sensible la convivialité et la sécurité de nombreux modèles existants dans de nombreuses entreprises. Au total, j'ai développé des centaines de modèles pour plus de 75 entreprises dans une dizaine de pays.

Enfin, n'hésitez pas à attacher des commentaires à mes messages : d'une part, cela peut me donner l'occasion de vous répondre ; d'autre part, cela peut me donner des idées pour d'autres articles.