Monsieur Excel
Pour tout savoir faire sur Excel !

27 novembre 2010

Résolution avec le solveur

Reprenons l’énigme mathématique présentée il y a quatre jours. Nous allons voir aujourd’hui comment – à l’aide du solveur d’Excel – vous pourrez résoudre ce problème facilement, même si l’algèbre n’est pas votre fort...

Le modèle ci-dessous montre les formules originales, en colonne A, et la façon dont nous les avons entrées dans le modèle en C3:F5. Les valeurs des trois variables sont en ligne 2. La formule de G3, reproduite jusqu’en ligne 5, est : =sommeprod($C$2:$F$2;C3:F3).

La formule de G6, enfin, est : =sommeprod(G3:G5;G3:G5). Cette formule calcule la somme des carrés des résidus des équations : il faut qu’elle aboutisse à un résultat de 0 pour que le problème soit résolu.

Nous avons alors utilisé le solveur d’Excel en entrant les paramètres ci-dessus. Il suffit alors de cliquer sur le bouton « Résoudre » pour obtenir en une fraction de seconde la solution finale avec en C2, C3 et C4 les valeurs 40, 30 et 10.

Remarque – Si vous ne voyez pas le solveur à droite de l’onglet « Données », c’est qu’il vous faut l’activer. Pour cela, passez par le bouton Office, cliquez dans « Options Excel », puis « Compléments », puis sélectionnez le solveur, passez par le bouton « Atteindre » et enfin cochez le solveur et validez…

23 novembre 2010

Une énigme mathématique...

Pour une fois, nous nous contenterons dans cet article de présenter une énigme algébrique, comme on en voit souvent dans les ouvrages de jeux et problèmes mathématiques. Nous avons trouvé cette énigme à l’adresse suivante : http://www.mathforu.com/sujet-10253.html.

« Jack a deux fois l'âge que Yann avait quand Jack avait l'âge de Yann.
Quand Yann aura l'âge de Jack, ils auront alors à eux deux 90 ans.
Quels âges ont-ils ? »

La première partie de la première phrase se formule algébriquement en
J = 2 * (Y-m), où les variables représentent respectivement l’âge de Jack, celui de Yann et un nombre d’années d’écart.

La seconde partie de la première phrase correspond à une formule plus simple encore : m = J – Y.

La seconde phrase peut être formulée de la façon suivante : J + Y + 2 * (J-Y) = 90.

Il n’est pas nécessaire d’être une lumière en algèbre pour trouver avec ces trois équations – pour trois inconnues, heureusement ! – la solution finale :
J = 40 et Y = 30.

Remarque 1 – Ce qui est intéressant, c’est de voir combien de temps il a fallu – avec les échanges reproduits à l’adresse ci-dessus – avant que la solution exacte ne soit découverte…

Remarque 2 – Que les habitués de ce blog se rassurent, le lien entre cette énigme et Excel apparaîtra dans le prochain article :)

19 novembre 2010

Un système expert de diagnostic

En utilisant les objets graphiques d’Excel, dont nous avons parlé dans les articles du 20 au 27 octobre, et en les combinant éventuellement avec des formats conditionnels, on peut réaliser des modèles remarquables.

J’ai ainsi développé un système expert pour le diagnostic des tuyaux dans une centrale nucléaire, pour EdF. Les tuyaux, dans une centrale nucléaire, doivent être régulièrement remplacés de manière préventive, afin d’éviter des fuites ou ruptures. Il ne faut pas non plus les remplacer trop tôt car cela coûte cher. D’où l’intérêt d’un système expert de diagnostic…

Le modèle que j’ai créé comporte une quinzaine d’onglets représentant différentes parties d’un organigramme général d’analyse dont nous voyons une partie du premier onglet dans le coin supérieur droit de l’image ci-dessous.

Compte tenu des valeurs indiquées dans la feuille de paramètres, les cellules actives de l’organigramme – grâce à un format conditionnel défini par une formule – prennent un fond jaune. Si l’on va consulter la fiche n°3, on y voit la continuation en fond jaune de la partie active de l’organigramme.

Si on entre à présent en B3 un pH de 12, c’est le bloc « Voir la fiche n°4 » qui va à présent prendre un fond jaune (ainsi que le bloc « pH > 10 »), la fiche n°3 n’aura plus de cellule en fond jaune, et la fiche n°4 comportera un cheminement en fond jaune.

Il s’agit bien là d’un système expert, car on parvient à un diagnostic et on peut remonter tout le cheminement pour comprendre comment ce diagnostic a été atteint.

Cette application d’Excel est tout à fait originale car, en dehors des formules définissant les formats conditionnels, il n’y a pratiquement pas de formule ni de valeur affichée dans la vingtaine d’onglets, en dehors de l’onglet de paramètres qui ne contient que des constantes !

Pour la petite histoire, EdF avait prévu de développer cette application en 6 mois avec un langage de programmation, pour un budget de 80 K€. J’ai développé le modèle avec Excel en 6 semaines (4 fois plus vite, sans VBA) et ce pour un budget de 23 K€ (cf. l’article « Réflexions sur la modélisation » du 28 octobre).

15 novembre 2010

Utilisation des « connecteurs »

Quand on utilise le ruban Insertion, le bloc « Illustrations » et la commande
« Formes », on obtient la liste des formes possibles présentée dans l’article
« Utilisation des formes Excel 2007 » du 20 octobre.

Le second ensemble de formes, appelé « Lignes » est en fait un bloc composite (cf. coin supérieur gauche de l'image ci-dessous). Les neuf premiers objets représentent réellement des connecteurs, qui sont bien plus que de simples lignes…

Un connecteur sert à connecter deux objets. Dans une feuille contenant trois blocs, activons par exemple le huitième connecteur (courbe à une flèche), puis passons le curseur sur l’un des trois blocs : quatre points rouges de connexion apparaissent et il suffit de cliquer sur l’un d’entre eux pour ancrer le point de départ du connecteur (étape 1).

Cliquons à présent sur le cercle lié à la flèche puis tirons-le à l'intérieur du bloc visé, celui en bas à droite : nous voyons alors ses quatre points rouges de connexion apparaître et il suffit de lâcher le bouton de la souris sur celui qui vous intéresse pour créer la connexion (étape 2).

Nous découvrons à présent l’intérêt principal de ces connecteurs : ils sont parfaitement dynamiques !

Déplaçons par exemple le bloc du bas, et nous voyons aussitôt le connecteur qui s’adapte (étape 3) !