Astuces pour devenir un pro dans Excel : Tableaux et Automatisation

Excel est un outil puissant pour gérer des données et automatiser des tâches. Voici des astuces avancées pour maîtriser les tableaux et l’automatisation dans Excel.

1. Maîtriser les Tableaux Structurés

Pourquoi utiliser les tableaux structurés ?

Les tableaux structurés rendent les données dynamiques et simplifient les formules, la mise en forme et la gestion des plages.

Astuces :

Créer un tableau structuré :
- Sélectionnez vos données > Insertion > Tableau.
- Cela transforme votre plage en un tableau structuré.
Ajouter automatiquement des formules :
- Les formules ajoutées dans une colonne du tableau s’appliquent automatiquement à toute la colonne.
Utiliser les noms des colonnes dans les formules :
- Exemple : =SOMME(Table1[Montant]) au lieu de =SOMME(B2:B100).
Étendre le tableau automatiquement :
- Ajouter des données juste sous le tableau l’inclut automatiquement.
Filtres dynamiques :
- Utilisez les outils de filtre et de tri intégrés directement au tableau.

2. Dynamiser les Tableaux avec les Formules Avancées

Fonctions indispensables pour les tableaux :

INDEX + EQUIV :
- Rechercher des données sans utiliser RECHERCHEV : =INDEX(Table1[Montant], EQUIV(100, Table1[ID], 0))
SOMME.SI.ENS :
- Effectuer des calculs conditionnels : =SOMME.SI.ENS(Table1[Montant], Table1[Catégorie], "Ventes")
DECALER :
- Créer des plages dynamiques pour des graphiques ou des calculs.
TABLEAU.CROISE.DYNAMIQUE :
- Synthétisez vos données en tableaux croisés dynamiques à partir de vos tableaux structurés.

3. Automatiser avec les Macros

Créer une macro simple :

Activer l’onglet Développeur :
- Allez dans Fichier > Options > Personnaliser le ruban et cochez Développeur.
Enregistrer une macro :
- Allez dans Développeur > Enregistrer une macro.
- Effectuez une série d’actions (ex. mise en forme, insertion d’une formule).
- Cliquez sur Arrêter l’enregistrement.
Exécuter la macro :
- Assignez la macro à un raccourci ou un bouton pour l’utiliser rapidement.

Exemple de macro VBA :

Sub FormaterTableau()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Feuil1")
    
    ' Appliquer un tableau structuré à une plage
    ws.ListObjects.Add(xlSrcRange, ws.Range("A1:C10"), , xlYes).Name = "Table1"
    
    ' Appliquer une mise en forme
    ws.ListObjects("Table1").TableStyle = "TableStyleMedium9"
End Sub

4. Automatiser les Tâches Répétitives avec VBA

Tâches courantes à automatiser :

Nettoyage de données :
- Supprimez les espaces inutiles avec VBA : Sub NettoyerEspaces() Columns("A:A").Replace What:=" ", Replacement:="", LookAt:=xlPart End Sub
Création automatique de graphiques :
- Générer un graphique basé sur un tableau dynamique.
Envoi de rapports par email :
- Automatiser l’envoi de vos tableaux ou graphiques via Outlook.

5. Utiliser les Slicers et Graphiques Dynamiques

Filtres interactifs (Slicers) :

Appliquez des segments (Slicers) aux tableaux structurés ou aux tableaux croisés dynamiques.
Vous pouvez filtrer les données visuellement en un clic.

Graphiques dynamiques avec plages nommées :

Créez une plage dynamique avec DECALER ou une table structurée.
Utilisez cette plage dans un graphique.
Ajoutez des segments pour filtrer vos graphiques en temps réel.

6. Utiliser les Outils Intégrés pour l’Automatisation

Power Query :

Chargez, transformez et combinez des données issues de plusieurs sources (Excel, CSV, bases de données).
Nettoyez vos données automatiquement.

Power Pivot :

Créez des relations entre plusieurs tables.
Analysez de grandes quantités de données avec des modèles de données avancés.

7. Conseils Pratiques pour Travailler Comme un Pro

Utilisez des raccourcis clavier :
- Ctrl + T : Convertir une plage en tableau.
- Ctrl + ; : Insérer la date actuelle.
- Alt + F8 : Exécuter une macro.
Utilisez les validations de données :
- Créez des listes déroulantes dynamiques ou des listes conditionnelles.
Standardisez vos mises en page :
- Ajoutez des styles cohérents aux tableaux et graphiques pour une présentation professionnelle.
Documentez vos formules :
- Insérez des commentaires dans vos cellules ou macros pour expliquer vos calculs.
Utilisez le Mode Audit :
- Vérifiez vos formules avec l’outil d’audit de formules pour repérer rapidement les erreurs.

Exemple d’automatisation dans Excel

Voici les étapes détaillées de la création de l’exemple d’automatisation dans Excel :

1. Création d’une Feuille avec des Données Source

Objectif : Ajouter des données brutes dans une feuille nommée Données Source.

Étapes :

Créer une feuille nommée Données Source.
Ajouter des colonnes :
- ID : Numéros d’identification uniques.
- Catégorie : Catégories des transactions (par ex., Ventes, Achats).
- Montant : Montants associés à chaque transaction.
Remplir des exemples de données : ID Catégorie Montant 1 Ventes 1000 2 Ventes 2000 3 Achats 1500 4 Achats 3000 5 Ventes 2500 6 Achats 4000 7 Ventes 3000
Appliquer des styles :
- Les en-têtes (ID, Catégorie, Montant) sont stylisés :
  - Texte en gras.
  - Couleur d’arrière-plan bleue (4F81BD).
  - Texte en blanc.

2. Création d’une Feuille Résumé Automatisée

Objectif : Résumer les données pour chaque catégorie (Ventes, Achats) en utilisant des formules dynamiques.

Étapes :

Créer une nouvelle feuille nommée Résumé Automatisé.
Ajouter des en-têtes :
- Catégorie : Contient les catégories (Ventes, Achats).
- Total Montant : Contient les sommes des montants pour chaque catégorie.
Ajouter les formules dynamiques pour chaque catégorie :
- Pour Ventes : =SUMIF('Données Source'!B2:B8, A2, 'Données Source'!C2:C8)
- Pour Achats : =SUMIF('Données Source'!B2:B8, A3, 'Données Source'!C2:C8)
Remplir les données dans la colonne Catégorie :
- Ligne 2 : Ventes.
- Ligne 3 : Achats.
Styliser les en-têtes :
- Texte en gras.
- Couleur d’arrière-plan bleue (4F81BD).
- Texte en blanc.

3. Automatiser avec des Formules Dynamiques

Objectif : Mettre à jour automatiquement les totaux dans la feuille Résumé lorsque les données de la feuille Données Source changent.

Étapes :

Utiliser la fonction SUMIF :
- Elle permet de calculer les totaux en fonction d’une condition (ex. : Catégorie = “Ventes”).
Lier les plages de la feuille Données Source à la feuille Résumé Automatisé :
- Colonne B (Catégorie) et C (Montant).

4. Ajustement Automatique des Colonnes

Objectif : Ajuster la largeur des colonnes pour améliorer la lisibilité.

Étapes :

Parcourir chaque colonne dans les deux feuilles :
- Données Source.
- Résumé Automatisé.
Déterminer la longueur maximale de texte dans chaque colonne.
Ajuster automatiquement la largeur des colonnes en fonction de cette longueur.

5. Sauvegarde du Fichier

Objectif : Enregistrer le fichier Excel contenant toutes les données et formules.

Étapes :

Enregistrer le fichier au format .xlsx.
Nommer le fichier Exemple_Automatisation_Excel.xlsx.

Résultat Final

Une feuille Données Source contenant des données brutes stylisées.
Une feuille Résumé Automatisé avec des totaux calculés automatiquement en fonction des données source.
Une automatisation dynamique : toute modification dans la feuille Données Source se reflète automatiquement dans la feuille Résumé Automatisé grâce aux formules.

Créer une macro Excel automatisée permet d’exécuter une série de tâches répétitives en un clic. Voici un exemple de macro qui automatise la création d’un tableau structuré, applique des formules et génère un résumé dans une nouvelle feuille.

Objectif de la Macro :

Insérer des données source.
Transformer les données en tableau structuré.
Ajouter des formules automatiques.
Générer un résumé des totaux par catégorie dans une nouvelle feuille.

Code VBA :

Voici le code pour une macro automatisée :

Sub AutomatiserExcel()

    Dim ws As Worksheet
    Dim summarySheet As Worksheet
    Dim tbl As ListObject
    Dim lastRow As Long

    ' Création de la feuille pour les données source
    Set ws = ThisWorkbook.Sheets.Add
    ws.Name = "Données Source"

    ' Ajouter des en-têtes
    ws.Range("A1").Value = "ID"
    ws.Range("B1").Value = "Catégorie"
    ws.Range("C1").Value = "Montant"

    ' Ajouter des données
    ws.Range("A2").Value = 1
    ws.Range("A3").Value = 2
    ws.Range("A4").Value = 3
    ws.Range("A5").Value = 4
    ws.Range("A6").Value = 5

    ws.Range("B2:B6").Value = Application.Transpose(Array("Ventes", "Achats", "Ventes", "Achats", "Ventes"))
    ws.Range("C2:C6").Value = Application.Transpose(Array(1000, 2000, 1500, 3000, 2500))

    ' Appliquer une mise en forme en tableau structuré
    Set tbl = ws.ListObjects.Add(xlSrcRange, ws.Range("A1:C6"), , xlYes)
    tbl.Name = "TableDonnées"
    tbl.TableStyle = "TableStyleMedium9"

    ' Création de la feuille pour le résumé
    Set summarySheet = ThisWorkbook.Sheets.Add
    summarySheet.Name = "Résumé Automatisé"

    ' Ajouter des en-têtes dans le résumé
    summarySheet.Range("A1").Value = "Catégorie"
    summarySheet.Range("B1").Value = "Total Montant"
    summarySheet.Range("A2").Value = "Ventes"
    summarySheet.Range("A3").Value = "Achats"

    ' Ajouter des formules de totaux dynamiques
    summarySheet.Range("B2").Formula = "=SUMIF(Données Source!B2:B6, A2, Données Source!C2:C6)"
    summarySheet.Range("B3").Formula = "=SUMIF(Données Source!B2:B6, A3, Données Source!C2:C6)"

    ' Ajuster la largeur des colonnes
    ws.Columns("A:C").AutoFit
    summarySheet.Columns("A:B").AutoFit

    ' Message de confirmation
    MsgBox "La macro a terminé avec succès !", vbInformation
End Sub

Comment Ajouter et Exécuter cette Macro :

Étape 1 : Activer l’onglet Développeur

Allez dans Fichier > Options > Personnaliser le ruban.
Cochez Développeur et cliquez sur OK.

Étape 2 : Ajouter le Code VBA

Appuyez sur Alt + F11 pour ouvrir l’éditeur VBA.
Allez dans Insertion > Module.
Collez le code ci-dessus dans le module.

Étape 3 : Enregistrer le Fichier

Enregistrez votre fichier au format Classeur Excel prenant en charge les macros (.xlsm).

Étape 4 : Exécuter la Macro

Retournez dans Excel.
Allez dans Développeur > Macros.
Sélectionnez AutomatiserExcel et cliquez sur Exécuter.

Résultat :

Une feuille nommée Données Source est créée, contenant des données formatées en tableau structuré.
Une feuille nommée Résumé Automatisé est générée, avec des totaux par catégorie calculés automatiquement.
La largeur des colonnes est ajustée pour une meilleure lisibilité.

Améliorations possibles :

Ajouter une boîte de dialogue pour permettre à l’utilisateur de personnaliser les données.
Étendre les formules pour des plages dynamiques.
Ajouter des graphiques à partir des données résumées.

Exemple_Macro_Automatisation Télécharger