XD blog

excel

2015-05-18 Ordonner les onglets sous Excel

On me demandait récemment comment ordonner les onglets sous Excel. Mon premier réflexe de chercher sur un moteur de recherche ordonner les onglets sous Excel. Je recopie ici le code trouver sur le site de Microsoft : Comment trier les onglets d'un classeur

Pour s'en servir, il suffit de recopier ce code dans l'éditeur VBA qui apparaît dès qu'on utilise la combinaison ALT+F11. Il faut le recopier dans la fenêtre associée à l'intitulé ThisWorkbook.

more...

2014-06-20 Python et Excel

On m'a posé récemment la question des divers moyens de travailler avec Python et Excel. J'en connais trois :

lecture, écriture offline de fichiers Excel
communiquer avec Excel avec une API COM
communiquer avec Excel en utilisant C# et .Net

more...

2014-03-03 Styles conditionnels en VBA sous Excel

J'avais besoin de créer un tableau avec une ligne de couleur différente pour chaque jour de la semaine. J'ai d'abord essayé de me passer de programmation en VBA car je me suis dit que c'est le genre de choses que je pourrais trouver sans un menu quelconque. J'ai commencé à chercher dans les styles personnalisés, puis sur Internet mais j'ai finalement abandonné. Je suis passé au VBA et ça m'a pris une dizaine de minutes. J'ai utilisé la première ligne pour les couleurs que je souhaitais (j'ai fait ça très vite, ce n'est pas du meilleur goût) puis j'ai composé le tableau, une première colonne pour les dates, une seconde qui contient le jour de la semaine (obtenu par formule). C'est ce numéro qui indiquera la couleur.

Puis j'ai écrit la macro suivante :

more...

2014-02-08 Résoudre un sudoku avec Excel et VBA

Le programme que vous trouverez plus bas résoud les sudoku. Je ne suis pas sûr qu'il faille rappeler les régles de ce jeu mais comme elles sont simples :

Chaque case contient un chiffre entre 1 et 9.
Dans chaque ligne et colonne, il ne peut y avoir deux chiffres égaux.
Dans chaque petit carré, il ne peut y deux chiffres égaux.

Et le programme Excel que j'ai fait se présente sous la forme :

more...

2014-01-30 Equation différentielle stochastique sous Excel avec VBA

C'est un TD que je donnais à l'ENSAE il y a quelques années. Mais le VBA n'a pas trop changé depuis cette époque, tout du moins comparé à d'autres langages. L'équation différentielle stochastique de Black et Scholes apparaît souvent lorsqu'on parle de finance :

$\frac{dY_t}{Y_t} = r dt + \sigma dW_t$

Avec :

: est le prix à l'instant t d'une action
: est le taux d'intérêt sans risque
$\sigma$ : est la volatilité du prix de l'action
t : représente le temps
: un petit intervalle de temps
: est un mouvement brownien
: est une variable aléatoire normale de moyenne nulle et de variance dt

On cherche à résoudre numériquement cette équation avec Excel. C'est un peu le début de l'implémentation d'un pricer sous Excel.

more...

2013-11-21 Fusionner deux tableaux

L'exercice est le suivant : on récupère deux tableaux depuis le site http://www.data.gouv.fr/. On récupère les deux fichiers suivants :

Ils contiennent des effectifs de la fonction publique ventilés par ministères :

Ministère    Libellé    Catégorie d'emploi    Emploi    ETPPLF
1    Affaires étrangères    1101    Titulaires et CDI en administration centrale    3 059
1    Affaires étrangères    1102    Titulaires et CDI dans le réseau    2 895
1    Affaires étrangères    1103    CDD et volontaires internationaux    2 877
1    Affaires étrangères    1104    Militaires    712
1    Affaires étrangères    1105    Agents de droit local    4 962
...

On cherche à mesurer l'évolution des effectifs entre ces deux années même si la liste des ministères et des catégories évolue.

more...

2013-09-08 Convertir un tableau Excel au format HTML

Le code suivant convertit un tableau Excel au format HTML puis stocke le résultat dans le presse-papier (il suffit donc de faire CTRC+V pour le coller où on veut). La macro convertit la zone sélectionnée au format HTML. Voici ce qu'elle donne :

objectif Python VBA

test if / else if .. Then / else / end if

boucle for .. in .. for .. = .. to .. / next

fonction def fonction / end function

Le code de la fonction doit être copié/collé dans un module (ALT+F11 pour voir le code VBA associé à la feuille). Je n'ai pas implémenté beaucoup de styles mais la fonction est facilement modifiable.

' code à copier dans la feuille Excel
Function ColorToHtml(ByVal value As String) As String
    Dim scolor As String
    scolor = Trim(Hex(value))
    While Len(scolor) < 6
        scolor = "0" + scolor
    Wend
    scolor = Mid(scolor, 3, 4) + Mid(scolor, 1, 2)
    ColorToHtml = "#" + scolor
End Function

Sub range_html_to_cliboard()
    Set rge = Selection
    Dim res, line, ce As String
    res = "<table>" + Chr(10)
    
    For i = 1 To rge.Rows.Count
        line = "<tr>"
        For j = 1 To rge.Columns.Count
            ce = "<td style="""
            If rge(i, j).Interior.color Then
                ce = ce + "background-color:" + ColorToHtml(rge(i, j).Interior.color) + ";"
            End If
            If rge(i, j).Font.color Then
                ce = ce + "color:" + ColorToHtml(rge(i, j).Font.color) + ";"
            End If
            If rge(i, j).Font.Bold Then
                ce = ce + "font-weight:bold;"
            End If
            ce = ce + """>"
            ce = ce + rge(i, j).Text + "</td>"
            line = line + ce
        Next
        line = line + "</tr>"
        res = res + line + Chr(10)
    Next
    
    res = res + "</table>" + Chr(10)
    
    ClipBoard_SetData (res)
End Sub

La fonction ne marche pas tel quel sur les tableaux créés avec des styles, ni même avec une liste filtrée. Pour cela, il faut copier la zone désirée puis faire deux collages spéciaux, le formatting et les valeurs, au même endroit. On sélectionne ensuite la zone collée puis on appelle la macro décrite ci-dessus.

more...

2013-03-04 Easy Pivot

I realize that wherever I go, I always do the same operation with figures. Before using any machine learning, the most basic task consists in aggregate figures using a pivot. Excel is already providing a visual tool. No matter what I do, I always find some limitations or I feel stuck because I do not know this function or this other one. Translating a matrix in Excel is still a pain. Plus, depending on the machine, I'm using, I need to switch from English function to French function which is really annoying. 10 years ago, I used to work with Lotus Improve. Basically, it was a cube in n dimension and you could write formulas quite easily. The sheet displayed on the screen was a pivot by itself and we could move a dimension from one axis to the others or choose a value and bring another dimension for the view. The concept was interesting. I never met anything similar except a programming language.

Any scripting language now allows to implement its own cube quite easily. The initial format is a big table containing the extended cube. Each row follows the same format: n coordinates, value or list of values. When I need to create a view of this cube, I need to aggregate using a kind of SQL logic. It is fine to write a GROUP BY query when the number of variable is small but when it is high, I would appreciate to have a tool which would do that for me. The convention I chose is to have meaningful name. Like many convention, the name of a column should indicate the numeric type and the aggregation type you want (sum, avg, len, key, none, max, min).

key_name	sum_a	len_b	avg_c
A	1.0	2.0	3.0
A	1.1	2.1	3.1
B	3.0	4.0	5.0

To aggregate on name, I just write this:

gr = table.groupby_implicit ( lambda v : v ["key_name"] )

I do not have to specify the rest. Usually, I use sum most of the time. I'll probably add others aggregator later such as argmax. I added a module computing some basics statistics, the code is still here:

Maybe one day, somebody will find something easy.

2013-01-28 Répéter les mêmes modifications sur une table

Excel est très pratique pour tracer des graphiques, écrire des formules dans une table. La seule contrainte vient parfois du fait qu'on se retrouve à faire la même chose plusieurs fois de suite. On doit produire les mêmes statistiques sur les mêmes données ou presque les mêmes : la matrice a deux colonnes en plus et trois lignes en moins. Ce n'est pas toujours évident d'adapter ses feuilles Excel. Aujourd'hui, je devais répéter la même formule sur dix colonnes différentes. J'ai donc décider de faire ça en Python. Je voulais écrire quelque chose comme ça :

table.add_column ( "has_A" + k, lambda v : 1 if "prenom" in v["name"] else 0 )

L'avantage est que je peux maintenant écrire quelque chose comme :

for name in selection_colonnes :
    table.add_column ( "has_" + name, lambda v : 1 if "mot clé" in v[name] else 0 )

ou encore

group = table.groupby ( lambda v: v["name"],
                        [ lambda v: v["d_a"], 
                          lambda v: v["d_b"] ],
                        [ "name", "sum_d_a", "sum_d_b"] )

innerjoin = table.innerjoin (group, lambda v : v["name"], 
                                    lambda v : v["name"], "group" )

Il ne me reste plus qu'à récupérer le tout sous Excel pour faire des graphiques ou faire de la mise en page. J'ai dû le coder plusieurs fois sous différentes formes. Voici la dernière.

2013-01-26 Compter le nombre de cellules d'une couleur sous Excel

On cherche à compter le nombre de cellules d'une couleur spécifique sous Excel dans une plage de données. On peut considérer l'exemple suivant qui fait apparaître un rectangle contenant des cases de couleurs différentes. La première ligne contient toutes les couleurs une seule fois, la seconde ligne contient le nombre de cellules dans le rectangle de la même couleur que la case du dessus.

On veut pouvoir écrire une formule du type :

=NbColorSameAs($B$6:$D$11;B1)

Il n'est pas possible de s'en sortir sans programmer soit même cette fonction. Pour cela, il faut :

créer une document Excel qui accepte les macros,
ouvrir l'éditeur Visual Basic (ALT + F11),
ajouter un module (clic droit de la souris)
copier/coller le code qui suit

Function NbColor(ByRef Plage As Range, Couleur As Byte) As Long
    Dim c As Range
    Dim nb As Long
    nb = 0
    For Each c In Plage
        If c.Interior.ColorIndex = Couleur Then
            nb = nb + 1
        End If
    Next c
    NbColor = nb
End Function

Function NbColorSameAs(ByRef Plage As Range, ByRef Cellule As Range) As Long
    NbColorSameAs = NbColor(Plage, Cellule.Interior.ColorIndex)
End Function

Le document Excel est maintenant prêt, il suffit d'insérer la première formule insérée plus haut dans ce blog (vous pourrez trouver un exemple ici).

more...

Xavier Dupré

objectif	Python	VBA
test	if / else	if .. Then / else / end if
boucle	for .. in ..	for .. = .. to .. / next
fonction	def	fonction / end function