Avec l'introduction des fonctions matricielles dynamiques et de la fonction LET, Excel devient de plus en plus un langage de programmation matriciel (je ne sais pas si ça existe, mais ça sonne très bien 🙂 ).
LET permet de stocker l'équivalent de variables pour les réutiliser plusieurs fois dans la formule. La beauté de la fonction LET est qu'il suffit juste de changer les premiers paramètres pour adapter la fonction à n'importe quel tableau.
Tableau simple
L'objectif ici est dépivoter ce tableau qui contient une colonne Ville, et 4 colonnes de dates :
L'explication de la formule est un peu longue, mais en gros, on calcule les coordonnées lignes et colonne de chaque titre ou valeur. Puis, avec la fonction INDEX, on reconstruit les différentes colonnes. La fonction CHOISIR permet de les fusionner.
Plus de colonnes à gauche
On peut aller plus loin avec deux colonnes, par exemple la ville et le pays. La formule ci-dessous doit probablement encore pouvoir être optimisée pour sélectionner la plage au complet, plutôt que deux colonnes distinctes (Ville et Pays).
Voici un petit code qui permet d'ajouter une ou plusieurs lignes à une table de données, tout en incrémentant la première colonne contenant la clef. Ce code sera aussi discuté plus en profondeur durant ce séminaire. Notez aussi l'utilisation de Application.WorksheetFunction.Sequence qui ne fonctionne qu'avec Office 365, mais qui permet de réduire plusieurs lignes de code à juste une seule ligne.
Voici le code et plus bas la vidéo expliquant comment il fonctionne.
Il ne faut pas oublier de renommer la feuille en VBA (ALT+F11 pour ouvrir VBE, puis clic sur la feuille, puis touche F4 pour afficher les propriétés et dans la propriété (Name), inscrire wksData.
Renommez aussi le tableau dans Excel (se mettre sur le tableau, puis onglet Création de tableau et enfin, tout à fait à gauche, on inscrit le nom). Ici, il est nommé Data.
Option Explicit
Sub testAjouterNLignes()
AjouterNLignes 10
End Sub
Sub AjouterNLignes(lNbLignes As Long)
'on definit l'objet correspond a la table Data
Dim tblData As ListObject
Set tblData = wksData.ListObjects("Data")
If tblData.DataBodyRange Is Nothing Then ' cas d'un tableau vide
tblData.ListRows.Add 'on ajoute une ligne
tblData.Resize tblData.Range.Resize(tblData.Range.Rows.Count + lNbLignes - 1)
Else
tblData.Resize tblData.Range.Resize(tblData.Range.Rows.Count + lNbLignes)
End If
tblData.DataBodyRange.Offset( _
tblData.DataBodyRange.Rows.Count - lNbLignes, _
0).Resize(lNbLignes, 1) = _
Application.WorksheetFunction.Sequence( _
lNbLignes, 1, Application.WorksheetFunction.Max(tblData.ListColumns("No").Range.Value) + 1, 1)
End Sub
