Monsieur Excel
Pour tout savoir faire sur Excel !

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 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…


29 octobre 2016

Trouver un mot depuis une liste

Le problème que nous avons aujourd’hui est assez particulier. Nous avons en colonne A une liste de textes et, en colonne E, une liste de mots recherchés. Nous voulons indiquer en colone B s’il est vrai que le texte à gauche contient au moins un mot de la liste et, en colonne C, quel est le premier mot de la liste trouvé dans ce texte.

Remarque 1 – La liste actuelle ne contient que des mots isolés, mais rien n’empêche d’y mettre des groupes de mots ou même des phrases entières.


La formule de B2 est :
=sommeprod(1*estnum(cherche(Couleur;A2)))>0

Dans cette formule :
cherche(Couleur;A2) donne {7;#VALEUR!;#VALEUR!;#VALEUR!;#VALEUR!}
estnum(cherche(Couleur;A2)) donne {VRAI;FAUX;FAUX;FAUX;FAUX}

La formule de C2 est matricielle, donc validée avec [Ctrl]-[Maj]-[Entrée].
La voici : =sierreur(index(Couleur;equiv(vrai;estnum(cherche(Couleur;A2));0));"")

En A6, deux couleurs de la liste sont présentes, mais c’est bien celle placée la plus haut dans la liste qui apparaît en C6.

Remarque 2 – Une fois de plus, nous constatons que nous parvenons à des résultats surprenants en utilisant des formules mettant en œuvre des vecteurs ! Il fallait penser à chercher un vecteur entier dans une cellule…

23 octobre 2016

Le ruban d’un add-in disparaît !

Vous avez peut-être découvert que le ruban d’un de vos add-ins, on dit compléments en français, a disparu depuis une mise à jour d’Excel en juillet dernier.

C’est un problème que vous pouvez avoir avec les versions 2010, 2013 et 2016 d’Excel. L’add-in, quand vous l’installez, se charge normalement. Mais Excel bloque, soit-disant pour des raisons de sécurité, des fichiers en provenance d’ailleurs. Quand on relance l’add-in, Excel bloque le chargement de ces fichiers.

Il y a deux façons de résoudre ce problème, une solution à long terme que je présenterai en premier car elle est à long terme, et une solution à court terme.

Solution à long terme

Il faut mettre le classeur de l’add-in dans le
C:\Utilisateurs\nom d’utilisateur\AppData\Roaming\Microsoft\AddIns\

Ou, avec le Mac :
 %appdata%\Microsoft\AddIns\

Tout add-in placé dans ce dossier apparaît dans le dialogue d’activation des add-ins sans que l’on ait besoin de l’installer. En outre, ce dossier peut être transformé en « Trusted Location » en suivant les instructions détaillées par mon collègue MVP Jon Peltier à l’adresse suivante :

C’est lui qui a identifié ce problème et publié l’article que j’ai traduit ici partiellement.

Solution à court terme

La solution ci-dessous vous permet d’installer l’add-in depuis n’importe quel dossier. Mais, si vous recevez un classeur de mise à jour, vous devrez le débloquer avant de l’ouvrir.
  • Trouvez l’add-in par l’Explorateur de fichiers.
  • Faites un clic droit sur le nom du fichier et sélectionnez les propriétés.
  • Si un message du type « Le fichier vient d’un autre ordinateur et peut être bloqué pour protéger cer ordinateur » apparaît, cliquez dans le bouton pour débloquer le fichier.
  • Validez par OK et redémarrez Excel.