Monsieur Excel
Pour tout savoir faire sur Excel !

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.

27 novembre 2016

Opérations sur cellules colorées

On regrette parfois dans Excel de ne pas disposer de formules réalisant des opérations en fonction de la couleur de fond de la cellule, ou de la couleur de la police.

Ce problème est résolu aujourd’hui grâce à une fonction personnalisée (UDF en anglais pour User Defined Function) créée par Philip Treacy, le mari de Mynda, la MVP qui tient avec lui l’excellent blog MyOnlineTrainingHub.

Nous voyons dans le tableau ci-dessous les résultats obtenus avec cette fonction, dont nous avons illustré la formule de chaque colonne via trois commentaires.


Remarque 1 – Je n’ai francisé ni le nom de la fonction ni celui de ses arguments afin de rester compatible avec des modèles provenant d’autres personnes utilisant cette fonction personnalisée.

Voici le code de la fonction. Bien entendu, si vous voulez la rendre accessible à tous vos modèles Excel, je vous conseille de la loger dans votre classeur de macros personnelles.

' Auteur : Philip Treacy
' http://www.myonlinetraininghub.com/count-sum-and-average-colored-cells
' Fonction personnalisée traduite et simplifiée par Hervé Thiriez
'
Function ColorMath(InputRange As Range, ReferenceCell As Range, Optional Action As String = "S")
   
    Application.Volatile
   
    ' Action : S pour somme, A (Avg) pour moyenne, C pour compter
    ' Sans le troisième argument, c'est la somme qui est effectuée
   
    Dim ReferenceColor As Long
    Dim CellCount As Long
    Dim Result As Variant
    Dim Cell As Range
    
    Action = UCase(Action)
    ReferenceColor = ReferenceCell.Interior.Color
       
    If Action = "S" Or Action = "A" Then
        For Each Cell In InputRange
            If Cell.Interior.Color = ReferenceColor Then
                Result = Result + Cell.Value
                CellCount = CellCount + 1
            End If
        Next Cell
    End If
           
    If Action = "C" Then
        For Each Cell In InputRange
            If Cell.Interior.Color = ReferenceColor Then Result = Result + 1
        Next Cell
    End If
           
    If Action = "A" Then Result = Result / CellCount
    ColorMath = Result

End Function

Remarque 2 – Cette fonction personnalisée fonctionne quand les couleurs de fond sont « naturelles », mais pas quand elles sont obtenues par des formats personnalisés !

En effet, selon le VBA, la commande suivante permet d’obtenir la couleur de fond d’une cellule :
CellColor = ActiveCell.DisplayFormat.Interior.Color

Sauf que, dans une fonction personnalisée, cette commande renvoie l’erreur  #VALUE !
Encore un mystère de Microsoft !

Opérations sur cellules colorées

On regrette parfois dans Excel de ne pas disposer de formules réalisant des opérations en fonction de la couleur de fond de la cellule, ou de la couleur de la police.

Ce problème est résolu aujourd’hui grâce à une fonction personnalisée (UDF en anglais pour User Defined Function) créée par Philip Treacy, le mari de Mynda, la MVP australienne qui tient avec lui l’excellent blog MyOnlineTrainingHub.

Nous voyons dans le tableau ci-dessous les résultats obtenus avec cette fonction, dont nous avons illustré la formule de chaque colonne via trois commentaires.


Remarque 1 – Je n’ai francisé ni le nom de la fonction ni celui de ses arguments afin de rester compatible avec des modèles provenant d’autres personnes utilisant cette fonction personnalisée.

Voici le code de la fonction. Bien entendu, si vous voulez la rendre accessible à tous vos modèles Excel, je vous conseille de la loger dans votre classeur de macros personnelles.

' Auteur : Philip Treacy
' http://www.myonlinetraininghub.com/count-sum-and-average-colored-cells
' Fonction personnalisée traduite et simplifiée par Hervé Thiriez
'
Function ColorMath(InputRange As Range, ReferenceCell As Range, Optional Action As String = "S")
   
    Application.Volatile
   
    ' Action : S pour somme, A (Avg) pour moyenne, C pour compter
    ' Sans le troisième argument, c'est la somme qui est effectuée
   
    Dim ReferenceColor As Long
    Dim CellCount As Long
    Dim Result As Variant
    Dim Cell As Range
    
    Action = UCase(Action)
    ReferenceColor = ReferenceCell.Interior.Color
       
    If Action = "S" Or Action = "A" Then
        For Each Cell In InputRange
            If Cell.Interior.Color = ReferenceColor Then
                Result = Result + Cell.Value
                CellCount = CellCount + 1
            End If
        Next Cell
    End If
           
    If Action = "C" Then
        For Each Cell In InputRange
            If Cell.Interior.Color = ReferenceColor Then Result = Result + 1
        Next Cell
    End If
           
    If Action = "A" Then Result = Result / CellCount
    ColorMath = Result

End Function

Remarque 2 – Cette fonction personnalisée fonctionne quand les couleurs de fond sont « naturelles », mais pas quand elles sont obtenues par des formats personnalisés !

En effet, selon le VBA, la commande suivante permet d’obtenir la couleur de fond d’une cellule :
CellColor = ActiveCell.DisplayFormat.Interior.Color

Sauf que, dans une fonction personnalisée, cette commande renvoie l’erreur  #VALUE !
Encore un mystère de Microsoft !

22 novembre 2016

Un graphique simple de PERCO

Aujourd’hui, nous nous attaquons – pour changer ! – à un problème simple de représentation graphique. Mais ce sera quand même l’occasion de voir quelques astuces qui ne sont pas connues de tous les utilisateurs Excel.

Nous avons un tableau indiquant, pour un salarié ayant souscrit à un plan PERCO d’épargne salariale. Une fois par an, en général, il effectue un versement pour lequel son employeur ajoute un abondement qui est grosso modo trois fois plus important.

Pour créer le graphique, j’ai sélectionné le bloc A1:A31 puis – en maintenant la touche [Ctrl] enfoncée – le bloc E1:F31. J’ai alors inséré un graphe en lignes. Ensuite, j’ai déplacé la légende en haut à gauche, là où elle ne risque pas de gêner les séries, et modifié les polices, tailles et couleurs des divers éléments du graphe pour obtenir le résultat que vous voyez ci-dessous, le titre « Analyse de PERCO » exclu.


Pour ce titre, plutôt que de passer par la commande associée, j’ai sélectionné l’objet graphique dans son ensemble (le rectangle entourant tous les éléments du graphe), puis utilisé la commande InsertionZone de texte. Cette commande insère une zone de texte qui est solidaire de l’objet graphique et borné par ses limites. J’ai entré le texte voulu et décoré cette zone de texte de la même façon que la légende.

Dans le graphe, nous constatons que – pour les premières valeurs – la courbe « Montant brut » occulte la courbe « Cumul apport ». Supposons que nous souhaitions mettre au premier plan le cumul de l’apport. La solution la plus simple est de cliquer dans un point de cette série, ce qui affiche dans la barre de formule, la formule de cette série : =serie('Salarié n°1'!$F$1;'Salarié n°1'!$A$2:$A$31;'Salarié n°1'!$F$2:$F$31;2). Il suffit de remplacer le dernier argument par un 2 et de valider : cette série devient alors la seconde et se logera donc « dessus » la nouvelle première série.

Remarque – Il y a ainsi de nombreux réglages que l’on peut effectuer pour les graphes directement, sans jamais passer par les menus Création, Disposition et Mise en forme qui apparaissent quand un objet graphique est actif.

En conséquence, je n’utilise pour ma part presque jamais ces trois menus.

Et ce pour un gain notable en rapidité et en efficacité…

16 novembre 2016

Un TCD classique en direct

Dans l’article précédent, nous avons vu que la présentation classique des TCDs (tableaux croisés dynamiques) avait un certain nombre d’avantages sur la présentation imposée par défaut depuis Excel 2007.

On pourrait alors se demander s’il n’est pas possible de régler quelque part un paramètre qui nous donne un TCD classique par défaut. Il semble hélas que cela ne soit pas possible :(

En revanche, vous pouvez loger dans votre classeur de macros personnelles – afin d’y avoir un accès direct depuis n’importe quel classeur – la macro suivante, à laquelle vous pourrez même associer le raccourci de votre choix :

Sub TCD_classique()
    With Selection.PivotTable
        .InGridDropZones = True
        .RowAxisLayout xlTabularRow
    End With
End Sub


Remarque – Si le TCD a déjà été construit, la macro fonctionne toujours, et l’on observe avec plaisir les affreux titres « Etiquettes des … » des lignes et des colonnes récupérer enfin les noms des champs concernés !

10 novembre 2016

La présentation des TCDs

Jusqu’à Excel 2003, la présentation des tableaux croisés dynamiques était toujours la même.

Voici ce que donnait un TCD au moment de sa création :


Avec Excel 2007, cela a changé complètement. Voici ce que donne au moment de sa création un TCD :


Pour ma part – et je ne suis manifestement pas le seul –, je trouve la présentation originale bien plus claire et efficace : on voit nettement qu’il y a quatre zones utilisables, et il est tout à fait naturel de glisser chaque champ concerné dans le bloc voulu.

Heureusement, on peut aujourd’hui encore revenir à la présentation classique du TCD. Pour cela, il suffit de faire un clic droit n’importe où dans le TCD, de choisir la commande « Options du tableau croisé dynamique », puis l’onglet « Affichage » et enfin de cocher l’option « Disposition classique du tableau croisé dynamique ». C’est ce que j’ai fait pour le tableau en haut de cet article.

Un autre avantage !

Comme le montre l’image ci-dessous, un autre avantage de la présentation classique est que les noms des champs de ligne et de colonne sont intelligemment récupérés alors que, depuis Excel 2007, il sont sottement nommés « Etiquettes de lignes » et « Etiquettes de colonnes ».

On ne comprend d’ailleurs pas pourquoi Microsoft n’a pas résolu ce problème depuis dans la mesure où cette simple correction n’aurait provoqué aucun problème de compatibilité ascendante :(


05 novembre 2016

Copie depuis une sélection multiple

Vous avez déjà probablement constaté que, dans Excel, on ne peut pas opérer une sélection multiple, puis la copier pour aller la coller ailleurs. La macro que nous vous proposons aujourd’hui, due à Kevin Jones, résout ce problème, au moins en partie. Pour illustrer l’utilisation de cette macro, nous allons reprendre l’exercice présenté dans l’article précédent, mais nous aurions pu prendre n’importe quel autre tableau Excel.


Comme nous pouvons le voir dans la copie d’écran, nous avons sélectionné le bloc E1:E6 puis, en maintenant la touche [Ctrl] enfoncée, le bloc A2:B3. Nous avons alors exécuté la macro PressePapiers puis cliqué en A8. Nous avons enfin utilisé [Ctrl]-v pour coller le résultat en A8.

Cette macro permet donc de copier une sélection multiple !

Remarque – Nous notons une originalité dans le résultat. Les données provenant du bloc A2:B3 sont recollées en une seule colonne. Cela peut intéresser les personnes souhaitant ramener en une colonne unique des données provenant de plusieurs blocs, chacun pouvant être bidimensionnel.

En revanche, on ne peut hélas pas recoller tout cela en respectant les positions relatives des blocs originaux ! A moins qu’un lecteur particulièrement créatif de ce blog ne trouve la solution…