Monsieur Excel
Pour tout savoir faire sur Excel !

30 décembre 2016

Une formule dure à comprendre…

La formule ci-dessous serait due à Laurent Longre, qui avait entre autres créé dans le temps une DLL appelée MoreFunc.DLL.

L’objectif est d’identifier le premier lundi de la semaine indiquée en B2 pour l’année en B1.

Voici la formule placée en B4 :
=somme(mod(("1/"&B1)-{0;6};{1E+99;7})*{1;-1})+B2*7-4

Pouvez-vous comprendre comment cette formule s’y prend pour obtenir le bon résultat ?


Les explications sérieuses suivantes proviennent du MVP Craig Hatmaker, qui a fourni une analyse détaillée du processus utilisé. Il a dû tirer cela de son chapeau… Les explications plus farfelues sont de votre serviteur.

La première date normale dans Excel est 1, pour le dimanche 1er janvier 1900.

La première date possible dans Excel est 0, pour le samedi précédent. Toute valeur négative est refusée comme date.

On voit bien là que Bill Gates est un peu un extraterrestre puisqu’il fait commencer Excel à une date qui n’existe pas. Steve Jobs, qui n’était pas un extraterrestre, a fait débuter Excel Mac sur le 1er janvier 1904, ce qui fait qu’il y avait 4 ans et 1 jour d’écart entre les deux calendriers. Vous pouvez le vérifier en passant par les options d’Excel, dans la section « Options avancées », bloc « Lors du calcul de ce classeur ».

"1/"&B1 donne le 1er janvier de l’année en B1.

("1/"&B1)-{0;6} donne le 1er janvier de l’année et 6 jours plus tôt.

1E+99 est un nombre si grand que n’importe quelle date divisée par ce nombre a un reste égal à la date. Si nous remplaçons ce grand nombre par 1000000, nous obtenons le même résultat…

{1er janvier;Reste} * {1 ;-1} donne {1er janvier;-Reste}

Somme({1er janvier;-Reste}) donne le vendredi précédant le lundi cherché

Quand la semaine cherchée est 1, alors 7*semaine – 4 = 3 : on cherche donc le troisième jour après le vendredi cité ci-dessus, ce qui est bien un lundi !

En général, dans ce blog, j’essaye de vous apprendre à faire simple. 

Aujourd’hui, pour changer, c’était l’inverse. Mais un peu de stimulation intellectuelle pour disperser les vapeurs de l’alcool de fin d'année, c’est peut-être bien utile…

J’espère que cela ne vous empêchera pas de bien profiter de votre réveillon…


Bonne année !

24 décembre 2016

Booléens vs. SI() imbriqués

Une particularité d’Excel est sa gestion des variables booléennes. Une variable booléenne peut prendre les valeurs VRAI ou FAUX. Quand ces valeurs sont utilisées dans des calculs, VRAI est équivalent à 1 et FAUX à 0.

Prenons un exemple tout simple où un commercial reçoit une prime de 75 € pour une commande de 1.000 € ou plus, de 125 € pour une commande de 2.000 € ou plus, et enfin de 250 € pour une commande supérieure à 5.000 €.

Une solution simple (colonne G) consiste à créer une table de recherche en B1:C4 dans laquelle on ira chercher le résultat grâce à la fonction recherche().

Supposons maintenant que l’on ne souhaite pas créer cette table de recherche. On peut alors utiliser (colonne H) trois fonctions si() imbriquées l’une dans l’autre.

Mais une troisième solution (colonne I) revient à remplacer cela par une formule avec des conditions résultant dans des valeurs booléennes.


Remarque – L’utilisation de valeurs booléennes permet d’éviter à l’utilisateur d’imbriquer des fonctions si(), ce qui n’est jamais souhaitable pour la lisibilité des modèles.

En tant que consultant, quand je construis des modèles pour mes clients, je suis souvent confronté à ce genre de dilemme. La solution booléenne est plus simple et logique que la solution avec les si(), mais elle est plus difficile à comprendre pour les utilisateurs. Quand j’ai ainsi le choix entre une solution plus directe et/ou compacte, mais moins compréhensible, je choisis en général pour mes clients la solution la plus compréhensible et auditable.

Pour la même raison, je ne mets du VBA dans mes modèles que lorsque c’est indispensable. Les clients ont en effet plus de mal à s’approprier un modèle s’il contient du code VBA…

18 décembre 2016

Création d’un « waffle chart » (c)

Pour terminer la série sur les waffle charts, voici enfin un waffle chart à quatre catégories.

Pour ceux qui prennent le train en route, sachez que nous avons simplement entré une puce « ● » dans chaque cellule de B5 à P14, en Calibri taille 36, et que c’est la macro ci-dessous, liée à la feuille, qui met tout cela en couleur.

Ce sont les couleurs des cellules B5:B8 qui sont prises comme modèles et reproduites dans le « graphe ».


Voici le code VBA, une fois encore grâce au MVP américain Matt Mickle :

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim CatVal1 As Integer: Dim CatVal2 As Integer
    Dim ColLp As Integer: Dim RowLp As Integer: Dim Cnt As Integer
    Dim Cat1Color  As Long: Dim Cat2Color As Long: Dim Cat3Color As Long
     
    If Intersect(Target, Range("B5:C7")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
   
        On Error Resume Next
        CatVal1 = 100 * Range("D5")
        CatVal2 = 100 * Range("D6") + CatVal1
        Cat1Color = Range("B5").Font.Color
        Cat2Color = Range("B6").Font.Color
        Cat3Color = Range("B7").Font.Color
   
        For RowLp = 5 To 14
            For ColLp = 7 To 16
                Cnt = Cnt + 1
                    If Cnt <= CatVal1 Then
                        Cells(RowLp, ColLp).Font.Color = Cat1Color
                    ElseIf Cnt <= CatVal2 Then
                        Cells(RowLp, ColLp).Font.Color = Cat2Color
                    Else
                        Cells(RowLp, ColLp).Font.Color = Cat3Color
                    End If
            Next ColLp
        Next RowLp
       
        'Change Font Color of TextBoxes
        With Sheets("3CategoryWaffleChart")
            .TextBoxes("TextBox 2").Font.Color = Cat1Color
            .TextBoxes("TextBox 6").Font.Color = Cat1Color
            .TextBoxes("TextBox 3").Font.Color = Cat2Color
            .TextBoxes("TextBox 7").Font.Color = Cat2Color
            .TextBoxes("TextBox 4").Font.Color = Cat3Color
            .TextBoxes("TextBox 8").Font.Color = Cat3Color
        End With
        On Error GoTo 0

End Sub

13 décembre 2016

Création d’un « waffle chart » (b)

Dans le dernier post, nous avons vu comment fonctionnait le waffle chart avec deux catégories.

Voici aujourd’hui un exemple de waffle chart à trois catégories, ainsi que le code VBA associé.

Le code original est dû au MVP américain Matt Mickle. Je l’ai seulement un peu simplifié et compacté.


Private Sub Worksheet_Change(ByVal Target As Range)

    Dim CatVal1 As Integer: Dim CatVal2 As Integer
    Dim ColLp As Integer: Dim RowLp As Integer: Dim Cnt As Integer
    Dim Cat1Color  As Long: Dim Cat2Color As Long: Dim Cat3Color As Long
     
    If Intersect(Target, Range("B5:C7")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
   
        On Error Resume Next
        CatVal1 = 100 * Range("D5")
        CatVal2 = 100 * Range("D6") + CatVal1
        Cat1Color = Range("B5").Font.Color
        Cat2Color = Range("B6").Font.Color
        Cat3Color = Range("B7").Font.Color
   
        For RowLp = 5 To 14
            For ColLp = 7 To 16
            
                Cnt = Cnt + 1
                    If Cnt <= CatVal1 Then
                        Cells(RowLp, ColLp).Font.Color = Cat1Color
                    ElseIf Cnt <= CatVal2 Then
                        Cells(RowLp, ColLp).Font.Color = Cat2Color
                    Else
                        Cells(RowLp, ColLp).Font.Color = Cat3Color
                    End If
                   
            Next ColLp
        Next RowLp
       
        'Change Font Color of TextBoxes
        With Sheets("3CategoryWaffleChart")
            .TextBoxes("TextBox 2").Font.Color = Cat1Color
            .TextBoxes("TextBox 6").Font.Color = Cat1Color
            .TextBoxes("TextBox 3").Font.Color = Cat2Color
            .TextBoxes("TextBox 7").Font.Color = Cat2Color
            .TextBoxes("TextBox 4").Font.Color = Cat3Color
            .TextBoxes("TextBox 8").Font.Color = Cat3Color
        End With
       
        On Error GoTo 0

End Sub

08 décembre 2016

Création d’un « waffle chart » (a)

Un concurrent éventuel du camembert, une référence en matière de cuisine Excel, est le « waffle chart », dont je ne connais pas – s’il existe – le nom français. La traduction du mot nous donne le choix entre Gaufre, Baratin et Remplissage… Aucun de ces trois termes ne m’excite particulièrement…

Le débat entre ceux qui préfèrent le camembert et les fanas du waffle chart est sans fin, et je ne vais donc pas m'y lancer.... Sachez simplement que vous avez aussi cette possibilité.

Le modèle ci-dessous est dû à mon collègue MVP américain Matt Mickle. La couleur de la police en B4:B5 détermine celle des billes du graphe. Dans les colonnes de P à R, il y a six zones de texte qui sont liées aux cellules des colonnes B:D.


Une macro évenementielle redessine le graphe chaque fois que l’on modifie quoi que ce soit dans l’onglet. Voici le code de cette macro :

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim CatVal1 As Integer: Dim ShpLp As Integer: Dim Cnt As Integer
    Dim Cat1Color  As Long: Dim Cat2Color As Long
   
    If Intersect(Target, Range("B5:C6")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
   
        On Error Resume Next
        CatVal1 = 100 * Range("D5")
        Cat1Color = Range("B5").Font.Color
        Cat2Color = Range("B6").Font.Color
       
        For ShpLp = 1 To 100
            With Sheets("ShapeTest").Shapes("Oval " & ShpLp)
                 Cnt = Cnt + 1
                 If Cnt <= CatVal1 Then
                    .Fill.ForeColor.RGB = RGB(Cat1Color Mod 256, _
                           ((Cat1Color \ 256) Mod 256), (Cat1Color \ 65536))
                 Else
                    .Fill.ForeColor.RGB = RGB(Cat2Color Mod 256, _
                           ((Cat2Color \ 256) Mod 256), (Cat2Color \ 65536))
                 End If
            End With
        Next ShpLp

        'Change Font Color of TextBoxes
        With Sheets("ShapeTest")
            .TextBoxes("TextBox 101").Font.Color = Cat1Color
            .TextBoxes("TextBox 103").Font.Color = Cat1Color
            .TextBoxes("TextBox 102").Font.Color = Cat2Color
            .TextBoxes("TextBox 104").Font.Color = Cat2Color
        End With
        On Error GoTo 0

    CatVal1 = Empty
    ShpLp = Empty
    Cnt = Empty
    Cat1Color = Empty
    Cat2Color = Empty
   
End Sub

03 décembre 2016

Combien de valeurs de la liste ?

Le problème auquel nous nous intéressons aujourd’hui est tout à fait particulier. Ce sera pour nous l’occasion de découvrir à la fois une formule intéressante et un format conditionnel original.

Nous avons en C1:C3 une liste de noms qui nous intéresse. Nous souhaitons afficher – en C6 – le nombre de ces noms que l’on trouve présents dans la colonne A.

La partie equiv(C1:C3;A1:A15;0) de la formule s’évalue en {#N/A;2;12}. Il se trouve que, quand on compte avec Nb(…), le nombre de valeurs présentes, les #N/A sont purement et simplement ignorés !

Notons bien que la formule de C6 est matricielle, donc validée avec [Ctrl]-[Maj]-[Entrée].


Un format conditionnel original

Nous avons défini pour A1:A15 un format conditionnel original dont le but est de mettre en relief les cellules ainsi  comptées, c’est-à-dire celles où a lieu la première apparition dans la colonne A d’un des noms de C1:C3.

Cette formule – pour A1 – a été reproduite en C1. Le principe en est tout à fait logique : les gagnants sont la première occurrence de ce nom dans la colonne A et ils sont présents dans le bloc C1:C3.

Une fois de plus, nous découvrons qu’il est possible de mettre en relief, via une formule astucieuse dans la définition du format conditionnel, des situations particulières complexes.