Monsieur Excel
Pour tout savoir faire sur Excel !

30 avril 2016

La fonction Indirect (a)

Il y a longtemps que nous n’avons pas parlé de la fonction indirect(), qui est pourtant une fonction particulièrement originale, et importante, d’Excel.

Supposez qu’en B1, vous ayez récupéré (cf. formule reproduite en B8) un montant de dépense provenant d’un autre classeur. Vous souhaitez rendre cette recherche paramétrable, en vous gardant la possibilité de modifier le nom du classeur, celui de la feuille, ou l’adresse de la cellule concernée.

Cela se fait très bien en utilisant la solution proposée en B2 (cf. formule en B9), grâce à la fonction indirect(). L’argument de la fonction sert uniquement à reconstituer, caractère par caractère, la formule que nous avions en B1 (cf. formule en B8).


Remarque 1 – Si le classeur référencé dans la formule n’est pas ouvert, le résultat de la formule est le message d’erreur #REF !

Remarque 2 – Dans le cas ci-dessus, qu’il y ait $B$129 ou B129 dans la cellule B6 revient strictement au même.

Syntaxe de la fonction

La syntaxe de la fonction est =indirect(référence[;A1]).
Les crochets dans la syntaxe indiquent que cet argument est facultatif.

Remarque 3 – Notez que, contrairement à Microsoft, nous mettons bien  dans nos syntaxes  le ";" à l'intérieur des crochets. Dans l'aide d'Excel, Microsoft les met toujours, à tort, en dehors...

Si l’argument facultatif A1 est FAUX, la référence doit être de type L1C1.

S’il est VRAI ou absent, la référence doit être de type A1, c’est-à-dire une ou plusieurs lettres pour la colonne et un nombre pour la ligne.

24 avril 2016

Découverte de la fonction N()

Une des fonctions les moins connues d’Excel est la fonction N(), qui est censée assurée la conversion en nombre de son argument.

En fait, dans une formule, sans rien vous demander, Excel assure lui-même la conversion chaque fois que nécessaire. Ainsi, quand A1 contient le texte « ’23 », et que l’on entre en B1 la formule « =10*A1 » , le résultat affiché est bien 230.

Historiquement, cette fonction a plutôt été conçue pour assurer une meilleure communication avec d’autres logiciels.


Le tableau ci-dessous indique les résultats obtenus avec divers arguments. J’ai fait exprès de choisir en B2:B10 des arguments cités en exemple dans l’aide de Microsoft.

On tombe sur un os avec B10, où j’ai repris l’exemple de l’aide de Microsoft qui annonce que l’on obtient alors la valeur de la date, comme cela avait été le cas en C3...

Le traducteur de l’aide n’a pas réalisé que les dates en français ne s’écrivaient pas comme en B10. C’est franchement désolant, et cela fait de multiples fois que je cite des exemples d’erreurs de ce genre dans l’aide d’Excel : Microsoft n’a hélas jamais voulu investir quoi que ce soit pour faire relire toute l’aide d’Excel par quelqu’un qui maîtrise le logiciel.

La fonction N() en guise de commentaire

Une application originale de la fonction N() est illustrée en B12. Comme on le constate en lisant la formule dans la barre de formule, on peut utiliser cette fonction – puisqu’elle évalue un texte à 0 ! – pour mettre un commentaire dans la formule elle-même !

Cela vous évite d’avoir à passer par la création de commentaires, d’avoir à supporter les triangles rouges, et de devoir gérer leur affichage ou non ! Mais cela ne fait hélas pas de la programmation très propre :(

Essayez de gagner un drone, votez pour Alzohis !

Dans le post précédent, je vous ai demandé de voter pour le projet Alzohis de diagnostic Alzheimer à partir dune prise de sang. Il faut absolument que cette start-up arrive en premier pour gagner ce concours. Elle a besoin de chaque vote possible ! Cela vous prendra moins d'une minute et vous ferez une bonne action !

http://entreprendre.bforbank.com/32/alzohis

Si vous voulez être plus sympa encore, demandez à vos amis - via Facebook ou autres - d'en faire autant. La date limite est le 25 avril au soir ! Il ne faut pus tarder...




19 avril 2016

Vous pouvez gagner un drone !

Il y a un peu plus d’un mois, le 16 mars, je vous avais présenté la société Alzohis, à laquelle je participe, qui fait du diagnostic Alzheimer à partir d’une simple prise de sang.

Comme promis, le site – qui n’était au départ qu’en anglais – est à présent disponible aussi en français :

Essayez de gagner un drone !

Alzohis participe, dans le cadre du réseau Entreprendre dont elle est lauréate, à un concours de start-ups animé par B for Bank. Vous pouvez participer en votant pour Alzohis dans la liste des 18 projets retenus pour ce concours :
http://entreprendre.bforbank.com/32/alzohis

Je vous encourage à voter pour trois raisons. 

La première est que cela vous donne une chance de gagner un drone ! 

La seconde est que si, comme moi, vous croyez dans ce projet, il faut tout faire pour lui permettre d’aboutir. 

La troisième est que cela représente une façon de me remercier pour tous les conseils sur Excel que je vous donne depuis octobre 2009 sur ce blog.

Attention ! La date limite pour les votes est le 25 avril !

13 avril 2016

Mes formations en mai-juin

Vous trouverez ci-dessous la liste des formations que je propose pour la fin mai et le début juin. Chaque formation est limitée à huit participants.

Création de tableau de bord sous Excel : le mardi 31 mai.
Modélisation avec Excel (2 jours) : les lundis 23 et 30 mai .
Découverte de Visual Basic (2 jours) : jeudi 2 et vendredi 3 juin.

Les autres séminaires habituels ne sont pas proposés en inter lors de cette session. Toutes les formations proposées sont organisées en association avec la société EuroDécision (www.eurodecision.com). Elles auront lieu à Paris, au 14 avenue de l’Opéra.

La formation « Création de tableau de bord sous Excel » vous permettra, en une seule journée, de totalement maîtriser la création d’un tableau de bord personnalisé, comme vous pouvez le voir dans l’article « Notre premier tableau de bord » du 22 mai 2013.

La formation « Modélisation avec Excel » est mon best-seller intégral : c’est une formation que j’ai animée plus de 100 fois, en inter ou en intra, avec un taux de satisfaction proche de 100%. Avec cette formation, n’importe quel utilisateur d’Excel parvient en deux jours à atteindre le niveau du top 5% des utilisateurs d’Excel.

Avec la formation « Découverte de Visual Basic », vous ne saurez pas tout sur le VBA, ce qui n'est pas possible en si peu de temps. Mais vous en saurez assez pour développer vos propres macros et savoir où trouver ce que vous ne savez pas encore.

Toutes mes formations (cf. catalogue sur ce blog) peuvent être animées en intra dans votre entreprise et – le cas échéant – personnalisées grâce à l’analyse et à l’amélioration des modèles propres à votre entreprise.

Ne loupez pas cette occasion de découvrir tout cela de la bouche même de l’auteur de ce blog, qui partagera avec vous l’expérience qu'il acquise en développant plus de 1.000 modèles dans plus de 100 entreprises en plus de 10 pays.

Quelques-unes de mes références de formation intra : Aéroports de Paris, Aérospatiale, Arianespace, Bouygues, Caisse des dépôts, CASE-Poclain, CCIP, Cegelec, CNES, CNET, EADS, EdF, Elf, Ernst & Young, Euroconsult, Finacor, France Telecom, Gaz de France, GIAT, IFP, Isochem, Lafarge, La Poste, Lilly France, Marsh, RTE, Sanofi, SIRIS, Texas Instruments, Tir Groupé, Total, Wabco, Walt Disney.

08 avril 2016

Résolution de problème récursif (d)

Comme nous l’avons dit au début de l’article précédent, il y a deux façons de trouver  la réponse à notre problème : soit l’énumération – par une série de boucles – des 65.536 solutions possibles, soit le tirage d’un grand nombre de solutions – par exemple 100.000 – pour être quasiment sûr de trouver la solution optimale.

Nous avons choisi la seconde solution, ce qui évite au moins de devoir créer 8 boucles imbriquées… Voici le code VBA correspondant :

Sub Solution()
Dim NbMax As Integer
Dim I As Long
Dim Nb1 As Integer
Dim Nb2 As Integer
    Range("Sol").ClearContents
    Range("Sol").Offset(0, 1).ClearContents
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    For I = 1 To 500000
        Application.StatusBar = "Itération n°" & _
           Application.Text(I, "# ##0") & _
           ". Meilleure solution : " & NbMax
        Calculate
        If Range("Good").Value >= NbMax Then
            Application.ScreenUpdating = True
            NbMax = Range("Good")
            Range("Sol") = Range("Answers").Value
            Range("Sol").Offset(0, 1) = Range("Eval").Value
            Application.ScreenUpdating = False
        End If
    Next I
    Application.StatusBar = False
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    MsgBox "Solution optimale : " & NbMax & ""
End Sub

Remarque 1 – Notez l’utilisation de la barre de statut pour permettre à l’utilisateur de suivre la progression des travaux. J’utilise cette barre chaque fois qu’un traitement prend un certain temps et que je souhaite rassurer l’utilisateur sur la progression du traitement.

Remarque 2 – Quand j’ai résolu le problème la première fois, j’ait trouvé deux réponses optimales, chacune avec 7. J’ai envoyé un mail à l’auteur en lui disant que j’avais bien résolu le problème mais qu’il y avait « hélas » deux solutions.

Il m’a répondu que c’était faux et que la solution était unique. J’ai repris mon travail et j’ai constaté que j’avais en effet saisi (5;6;7;8) en H8:K8. Je n’avais effectivement pas remarqué, en créant mon modèle Excel, que dans cette série – et elle seule – les réponses n’étaient pas en ordre croissant.

J’ai donc corrigé cette ligne et, comme par hasard, il n’y avait alors plus qu’une seule solution ! Cela m’a permis de lui renvoyer un mail pour lui dire que j’avais fait cette distraction puis compris pourquoi il avait « truandé » la dernière ligne afin d’obtenir une réponse unique !


31 mars 2016

Résolution de problème récursif (c)

Pour résoudre notre problème, trouver la solution avec le plus grand nombre possible de réponses vraies, il y a grosso modo deux approches via le VBA : soit faire une série de boucles avec toutes les réponses possibles pour toutes les questions, soit tirer les réponses au hasard et simuler cela assez de fois pour être quasiment sûr de trouver la meilleure réponse.

Il faut tout d’abord calculer le nombre de réponses possibles, ce que nous avons calculé en A19 avec la formule : ="Il y a "&texte(4^8;"# ##0")&" combinaisons possibles !". Nous avons donc 65.536 réponses possibles, ce qui signifie qu’avec 100.000 itérations nous avons de fortes chances de trouver la solution la meilleure. A ce jour, je l'ai toujours trouvée avant la fin des 100.000 itérations !


J’ai donc ajouté une colonne B pour tirer les réponses au hasard, avec la formule =H1+ent(4*alea()) en B1, reproduite ensuite vers le bas. En C1, la formule =index(val;equiv(B1;H1:K1;0)), elle aussi tirée vers le bas, calcule la réponse correspondante, Val étant le nom du bloc H9:K9.

Tout cela fonctionne très bien et l’on découvre la solution optimale, qui est de 7.

Dans mon prochain blog, je vous livrerai le code de la macro...

26 mars 2016

Résolution de problème récursif (b)

Dans l’article précédent, nous avons construit le tableau de départ et nous vous demandions quelles formules utiliser pour évaluer la qualité des réponses aux questions.

C’est ce que nous avons fait avec le tableau reproduit ci-dessous, dans lequel nous avons légèrement modifié la place des colonnes utilisées. 


Vos réponses sont entrées en colonne C et la réponse exacte est calculée en colonne D. Dans la partie inférieure de la copie d’écran, nous avons listé les formules des colonnes D et L.

Les formules de D3, D6, L5 et L7 sont des formules matricielles, à valider avec [Ctrl]-[Maj]-[Entrée].

La formule de F2, reproduite vers le bas, est : =si(estna(E1);faux;C1=E1). Je n’ai pas utilisé la fonction sierreur() pour que cette formule fonctionne avec toutes les versions d’Excel.

La formule de F9 est =nb.si(F1:F8;vrai()).

Il ne vous reste plus, d’ici mon prochain post, qu’à trouver comment vous ferez, à partir de ce modèle, pour trouver la solution optimale…