Monsieur Excel
Pour tout savoir faire sur Excel !

28 août 2010

Quelques remarques sur les tables

Il est important de connaître les caractéristiques des tables d’hypothèses, dont nous venons de voir un nouvel exemple dans l'article présenté il y a quatre jours.

Voici quelques remarques qui vous permettront de mieux les connaître et donc de mieux les utiliser…

Lectures préliminaires

Avant d’aller plus loin, vous pouvez (re)lire les articles « Table de données à une entrée » du 17 octobre 2006 et « Table de données à deux entrées » du 21 octobre 2006. Cela vous donnera une première vision d’ensemble du mode de fonctionnement des tables et de leur potentiel.

Protection automatique des tables

Une table est par défaut protégée et donc indestructible (en partie tout au moins). Si vous reprenez l’exemple précédent, en sélectionnant toute partie de la table qui ne couvre pas au moins l’ensemble du bloc B3:I13 et en utilisant la touche [Del] ou [Eff] pour effacer le contenu sélectionné, cela ne marche pas et vous êtes bloqué avec le message « Impossible de modifier une partie d’une table de données ».

Mode de calcul des tables

Les tables ne se calculent automatiquement que si l’on se trouve en mode de calcul « Automatique ». Le mode de calcul « Automatique sauf les tables » est bien utile car une table ralentit énormément les calculs. Prenons l’exemple de remboursement de prêt présenté il y a quatre jours : cette table contient 88 cellules de résultat. Si cette table se trouvait dans un gros modèle Excel qui mettrait normalement 1 minute à se recalculer, le temps de calcul – à chaque changement de quoi que ce soit dans le classeur – serait de 88 minutes si l’on était en mode de calcul automatique.

Pire encore, si votre classeur comporte une table de 50 cellules de résultat et une autre avec 40 cellules de résultat, le temps de calcul est multiplié par 2.000 car la seconde table est calculée entièrement pour chaque cellule de la première !

Modification de la référence de la table

Une table à deux entrées, comme la table de remboursement de prêt, décline la formule placée dans le coin supérieur gauche de la table. Si nous remplacions la formule =C18 par la formule =C16+10*C17, elle calculerait le résultat obtenu en ajoutant 10 fois le taux mensuel au nombre d’années…

Paramétrisation des paramètres

Rien n’empêche d’utiliser des formules pour les paramètres d’une table. Dans notre exemple de prêt, nous pourrions avoir le taux initial en L1 et l’écart de taux en L2. La formule en A3 serait alors =L1 et la formule en A4 – recopiée ensuite vers le bas – serait =A3+L$2.

Tables à une ou à deux entrées

Une table à deux entrées permet de faire varier deux paramètres et d’en voir l’impact sur une formule, celle qui est placée dans le coin supérieur gauche de la table.

Une table à une entrée permet de faire varier autant de formules que l’on veut – placées dans une ligne ou dans une colonne – mais ce en fonction d’un seul paramètre.

Si l’on voulait analyser la façon dont trois paramètres ou plus influeraient sur un ou plusieurs résultats, on pourrait par exemple faire de la simulation probabiliste, à l’aide d’un outil tel que Crystal Ball. J’ai pour ma part, en tant que consultant, développé des modèles Crystal Ball pour une vingtaine de grandes entreprises.

24 août 2010

Remboursement d’un prêt

Nous avons déjà publié le 9 juin 2008 l'article « Calcul du remboursement d’un prêt » dans lequel nous montrions le fonctionnement des fonctions VPM(), VA(), Taux() et NPM() dans le calcul du remboursement d’un prêt.

Le tableau ci-dessus montre comment l’on peut construire une table pour calculer le remboursement mensuel en fonction de deux paramètres, la durée (en ligne 2) et le taux (en colonne A).

En C18, nous avons entré la formule illustrée dans le commentaire et, en A2, nous avons simplement saisi la formule =C18. Ensuite, nous avons sélectionné le bloc A2:I13.

Nous avons alors créé une table :
● avec Excel 2003 ou XP, en utilisant la commande « Table… » du menu Données ;
● avec Excel 2007 ou 2010, en utilisant la commande « Table de données… » du bouton « Analyse de scénarios » du bloc « Outils de données » du ruban « Données ».

Nous avons sélectionné C16 comme « Cellule d’entrée en ligne » et C17 comme « Cellule d’entrée en colonne », puis nous avons validé.

Le résultat est la table ci-dessus. Si les bonnes valeurs n’apparaissent pas, c’est que vous êtes soit en mode de « calcul manuel », soit dans le mode « automatique sauf les tables ». Dans les deux cas, [F9] lance les calculs…

20 août 2010

Commande « Formulaire » en 2007

Dans le ruban « Données » d’Excel 2007, il y a une absence criante, celle de la commande « Formulaire… » de toutes les versions antérieures d’Excel, et dont nous venons de présenter le mode d’emploi dans les deux articles précédents.

Peut-on récupérer cette commande avec Excel ? Ceux qui me connaissent se doutent bien que, si je pose la question, c’est que j’ai la réponse ! Ils n’auront pas tort…

En fait, il suffit une fois de plus – nous l’avons déjà fait auparavant pour d’autres commandes disparues » - de passer par la barre d’accès rapide.

Cliquons sur le tout dernier bouton, « Personnaliser la barre d’outils Accès rapide », puis sélectionnons les options « Autres commandes » puis « Toutes les commandes ». Nous trouvons la commande « Formulaire… » dans la liste et nous cliquons dans le bouton « Ajouter >> »…

Et voilà ! Nous avons à présent, à la fin de la barre d’accès rapide, la commande « Formulaire… », clairement identifiée par une icône représentant justement un formulaire. Et elle fonctionne sous Excel 2007 exactement comme sous Excel 2003 ou XP.

Si quelqu’un peut nous expliquer pourquoi Microsoft a décidé de ne pas l’inclure dans le ruban « Données », cela m’intéresserait beaucoup d’en connaître la raison ! Un commentaire à ce sujet serait le bienvenu…

15 août 2010

Utilisation du bouton « Critères »

Nous prolongeons le dernier article, sur la commande « Formulaire… » du menu Données. Nous nous intéressons aujourd’hui au bouton « Critères » du dialogue de cette commande.

Quand on clique sur ce bouton, le dialogue apparaît avec tous les champs vides. On peut alors entrer un ou plusieurs critères, et utiliser les boutons « Suivant » et « Précédent » pour se promener dans la partie de la base qui correspond aux critères.

Entrons par exemple « f » dans la case « Sexe » puis cliquons dans le bouton
« Suivant ». Voici le résultat que nous obtenons alors :

Remarque 1 – On peut regretter, comme pour la consultation, mais encore plus dans ce cas des critères de sélection, qu’il n’y ait pas de menu déroulant pour, par exemple, choisir un nom ou un pays dans une liste.

Remarque 2 – On peut entrer des critères relativement élaborés tels que « f* » dans le pays pour retenir toutes les personnes dont le nom du pays débute par « f » ou « F », ou « <>27 » dans l’âge pour exclure uniquement les personnes âgées de 27 ans, ou encore « ????? » dans le prénom pour ne retenir que les personnes dont le prénom comporte exactement cinq caractères.

10 août 2010

La commande « Formulaire... »

Nous n’avions pas encore eu l’occasion de parler d’une commande relativement peu connue du menu « Données » des versions 2003 et XPet antérieures… – d’Excel.

Quand le curseur se trouve dans une cellule de la base – comme c’est le cas ci-dessous en B11 – cette commande fait apparaître un formulaire pour la première fiche de la base.

On utilise l’ascenseur au milieu du dialogue pour se « promener » dans la base.

Remarque 1 – La largeur du formulaire dépend directement de la largeur de la colonne la plus large de la base.

Remarque 2 – Quand on utilise le bouton « Nouvelle » pour ajouter une fiche, cela ne marche que s’il y a de la place sous la base. Sinon, Excel avertit l’utilisateur de l’impossibilité de la tâche.

Remarque 3 – Quand on crée une fiche, Excel ne propose hélas pas les modalités antérieures. Ainsi, pour le champ « Sexe », il ne propose pas par défaut « M » ou « F »… Cela aurait été bien pratique dans le cas des pays, pour éviter des saisies inutiles quand le pays est déjà présent…

06 août 2010

Date initiale et date finale

Nous avons des dates en colonne B auxquelles des opérations ont été réalisées pour les départements listés en colonne A.

Nous souhaitons obtenir en D1:F5, pour chaque département, les dates de la première et de la dernière opération.

Vous pouvez voir dans le bas de la copie d’écran ci-dessus, les formules utilisées dans les colonnes E et F. Pour la colonne E, c’est simple, il s’agit d’un index() d’equiv() comme nous en avons déjà souvent vus.

Le plus délicat est d’identifier la dernière date. Pour cela nous avons utilisé trois formules différentes en D2, D3 et D4. Notez que les deux premières doivent être saisies comme des formules matricielles, donc validées avec [Ctrl]-[Alt]-[Entrée].

Pour des explications complémentaires, vous pouvez lire l’article « Où est la dernière occurrence ? » du 28 mai 2007.

02 août 2010

Deux livres sur Excel 2010

« VBA Excel 2010 – Créez des applications professionnelles » est écrit par Henri Laugié et publié aux Editions ENI, 348 pages, 29,90 €. Un classeur d’exercices peut être téléchargé à partir du site ENI.

Liste des chapitres

1. Procédures
2. Variables – Constantes – Types de données
3. Opérateurs
4. Structures de contrôle
5. Tableaux
6. Introduction à la programmation objet
7. Classeurs
8. Feuilles de calcul
9. Cellules et plages
10. Graphiques
11. Echange de fonctions entre Excel et VBA
12. Boîtes de dialogue standard
13. Formulaires
14. Contrôles
15. Contrôles Objets liés et incorporés
16. Collaboration avec les applications Microsoft
17. Programmation Web - Windows
18. Matrice et tableau croisé dynamique

Chaque chapitre comporte des QCM (168 au total) et des exercices, tous corrigés dans la seconde partie de l’ouvrage. Au total, selon l’auteur, près de 35 heures de mise en pratique.

Microsoft Excel 2010 – Référence bureautique – Editions ENI
485 pages – 17,90 €

Ce livre se présente étrangement sans auteur…

C’est un livre assez complet pour ceux qui veulent un ouvrage de référence. On y trouve de tout, sous la forme « Comment faire ci ? Comment faire ça ? La table des matières, en 11 pages, permet de trouver en général ce que l’on y cherche.

Pour ma part, je trouve qu’il manque à cet ouvrage – comme d’ailleurs à tous les autres ouvrages du même type, un chapitre listant toutes les nouveautés de cette version par rapport à la précédente, Excel 2007.

En effet, je ne dois pas être le seul à avoir besoin avant tout de savoir ce qui est nouveau dans une nouvelle version d’Excel : je préfèrerai de loin avoir 25 pages à lire que 485 à fouiller…

Mais cela demanderait sans doute trop de travail aux auteurs…

Pour savoir ce qu'il y a de neuf dans Excel 2010, vous trouverez de nombreux articles et même des vidéos. Voici deux exemples de liens :

http://www.addictivetips.com/windows-tips/microsoft-office-excel-2010-whats-new/
http://spreadsheetpage.com/index.php/blog/whats_new_in_excel_2010/