Implémenter des techniques avancées de transformation de données, Excel VBA

Implémenter des techniques avancées de transformation de données, Excel VBA

Voici une explication détaillée et un exemple de code VBA pour implémenter des techniques avancées de transformation de données dans Excel. Nous allons vous guider à travers des techniques courantes telles que le pivotement des données, le dé-pivotement des données, le nettoyage des données et l’application de filtres complexes.
Techniques avancées de transformation de données avec Excel VBA
Scénario
Imaginons que vous ayez un ensemble de données avec plusieurs colonnes, et que vous souhaitiez le transformer dans un format plus utile. Par exemple, vous pourriez avoir besoin de :
1. Pivoter une table de données (transformer des lignes en colonnes).
2. Dé-pivoter des données (transformer des colonnes en lignes).
3. Nettoyer des données en supprimant des caractères indésirables ou en traitant des valeurs manquantes.
4. Appliquer des filtres complexes ou transformer les données en fonction de critères spécifiques.
Je vais détailler ces techniques et fournir un exemple de code VBA pour chaque cas.
1. Pivoter des données (Transformer des lignes en colonnes)
Problème : Vous avez une liste de données de ventes pour plusieurs commerciaux sur plusieurs mois, mais les données sont en lignes et vous voulez les transformer pour que chaque mois devienne une colonne.
Exemple de données :

Sortie souhaitée :

Code VBA pour pivoter les données :

Sub PivotData()
    Dim ws As Worksheet
    Dim lastRow As Long, lastCol As Long
    Dim dataRange As Range
    Dim pivotTable As PivotTable
    Dim pivotCache As PivotCache
    ' Référence de la feuille et de la plage de données
    Set ws = ThisWorkbook.Sheets("Feuil1")
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    Set dataRange = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))
    ' Créer un cache de pivot
    Set pivotCache = ThisWorkbook.PivotTableWizard(dataRange)
    ' Créer la table pivot sur une nouvelle feuille
    Set wsPivot = ThisWorkbook.Sheets.Add
    Set pivotTable = wsPivot.PivotTableWizard(pivotCache, _
                                              ws.Cells(1, 1), _
                                              ws.Cells(1, 2), _
                                              ws.Cells(1, 3))
    ' Organiser les champs de la table pivot
    pivotTable.PivotFields("Commercial").Orientation = xlRowField
    pivotTable.PivotFields("Mois").Orientation = xlColumnField
    pivotTable.PivotFields("Montant des ventes").Orientation = xlDataField
    pivotTable.PivotFields("Montant des ventes").Function = xlSum
End Sub

Explication :
1. Nous définissons la plage de données contenant l’ensemble de données.
2. Créez un cache de pivot et utilisez la méthode PivotTableWizard pour créer une nouvelle table pivot dans une feuille séparée.
3. Définissez l’orientation des champs pour les lignes (Commercial), les colonnes (Mois) et les données (Montant des ventes) pour afficher la somme des ventes.
2. Dé-pivoter des données (Transformer des colonnes en lignes)
Problème : Vous avez un jeu de données large et vous souhaitez le transformer en un format plus long (dé-pivoter les données).
Exemple de données :

Sortie souhaitée :

Code VBA pour dé-pivoter les données :

Sub UnpivotData()
    Dim ws As Worksheet
    Dim lastRow As Long, lastCol As Long
    Dim i As Long, j As Long
    Dim targetRow As Long
    Dim monthName As String
    Dim salesAmount As Double
    ' Référence de la feuille
    Set ws = ThisWorkbook.Sheets("Feuil1")
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    ' Commencer à remplir les données dé-pivotées en dessous des données existantes
    targetRow = lastRow + 2
    ' Écrire les en-têtes pour les données dé-pivotées
    ws.Cells(targetRow, 1).Value = "Commercial"
    ws.Cells(targetRow, 2).Value = "Mois"
    ws.Cells(targetRow, 3).Value = "Montant des ventes"
    targetRow = targetRow + 1
    ' Boucle à travers les données pour dé-pivoter
    For i = 2 To lastRow
        For j = 2 To lastCol
            monthName = ws.Cells(1, j).Value
            salesAmount = ws.Cells(i, j).Value
            ws.Cells(targetRow, 1).Value = ws.Cells(i, 1).Value ' Commercial
            ws.Cells(targetRow, 2).Value = monthName ' Mois
            ws.Cells(targetRow, 3).Value = salesAmount ' Montant des ventes
            targetRow = targetRow + 1
        Next j
    Next i
End Sub

Explication :
1. Nous parcourons chaque ligne et colonne de l’ensemble de données original.
2. Pour chaque combinaison de Commercial et Mois, nous créons une nouvelle ligne dans le tableau de sortie avec le mois et le montant des ventes correspondants.
3. Les données sont maintenant dans un format long, ce qui est souvent plus adapté pour l’analyse ou d’autres transformations.
3. Nettoyage des données (Suppression de caractères indésirables)
Problème : Votre ensemble de données contient des espaces ou des caractères spéciaux indésirables, et vous souhaitez les nettoyer.
Exemple de données :

Code VBA pour nettoyer les données :

Sub CleanData()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim cell As Range
    ' Référence de la feuille
    Set ws = ThisWorkbook.Sheets("Feuil1")
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    ' Boucle à travers chaque ligne pour nettoyer les données
    For i = 2 To lastRow
        ' Nettoyer le Nom - supprimer les caractères spéciaux et les espaces superflus
        Set cell = ws.Cells(i, 1)
        cell.Value = Trim(Replace(cell.Value, "@", ""))
        cell.Value = Trim(Replace(cell.Value, "!", ""))
        ' Nettoyer l'Adresse - supprimer les caractères spéciaux
        Set cell = ws.Cells(i, 3)
        cell.Value = Trim(Replace(cell.Value, "#", ""))
    Next i
End Sub

Explication :
1. Nous parcourons les lignes et nettoyons les caractères indésirables (comme @, !, #, etc.) et les espaces supplémentaires dans les colonnes Nom et Adresse.
2. La fonction Trim() supprime les espaces en début et en fin de chaîne, et la fonction Replace() est utilisée pour remplacer les caractères indésirables.
4. Filtres complexes (Application de plusieurs critères)
Problème : Vous devez filtrer un ensemble de données en fonction de plusieurs critères (par exemple, des ventes supérieures à une certaine valeur et provenant d’une région spécifique).
Exemple de données :

Code VBA pour appliquer des filtres complexes :

Sub FilterData()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim salesAmount As Double
    Dim region As String
    ' Référence de la feuille
    Set ws = ThisWorkbook.Sheets("Feuil1")
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    ' Boucle à travers chaque ligne pour appliquer les critères de filtre
    For i = 2 To lastRow
        salesAmount = ws.Cells(i, 3).Value
        region = ws.Cells(i, 2).Value
        ' Conserver uniquement les lignes où le Montant des ventes > 200 et la Région = "Nord"
        If salesAmount > 200 And region = "Nord" Then
            ws.Rows(i).Hidden = False
        Else
            ws.Rows(i).Hidden = True
        End If
    Next i
End Sub

Explication :
1. Nous parcourons l’ensemble des données et appliquons un filtre où le Montant des ventes est supérieur à 200, et la Région est « Nord ».
2. Les lignes qui ne respectent pas ces critères sont masquées.
Conclusion
Voici quelques-unes des techniques avancées de transformation de données que vous pouvez implémenter avec VBA dans Excel. Ces méthodes vous permettent de pivoter et dé-pivoter vos données, de les nettoyer et d’appliquer des filtres complexes pour rendre votre ensemble de données plus utile pour l’analyse. Grâce à VBA, vous pouvez automatiser ces tâches, économisant ainsi du temps et garantissant la cohérence de vos transformations.