Page mise à jour : mai 2024
Cette page va vous guider dans la gestion de vos données collectées ou ou non collectées.
Excel, à l'origine, n'était pas voué aux stockage de données mais plutôt à la gestion comptable, calcul de données etc.
Excel, à l'origine, n'était pas voué aux stockage de données mais plutôt à la gestion comptable, calcul de données etc.
Aujourd'hui dans notre cadre de travail nous réceptionnons ou transmettons différents types de tableau contenant une multitude de données.
Et ces données nous devons les formater pour qu'elles puissent être facilement utilisables pour tous les intervenants sur les fichiers.
Ces fichiers peuvent être important suivant les informations stockés à savoir une multitude de colonnes et de lignes
Il faut savoir que les versions récentes peuvent contenir 1 048 576 lignes et 16 384 colonnes. Vertigineux me direz vous !
Donc il est important de structurer ces données dans votre fichier.
Je vais vous guider pour que ces fichiers de données soit plus lisibles et la plus EXPLOITABLES.
Pour une lecture plus simple dans le descriptif ci-après nous allons appelé le fichiers de données => BDD comme Base de données
Tout dépend d'abord si vous créer cette BDD ou si vous faites une extraction d'un support source.
Dans le premier cas c'est plus simple car c'est vous qui êtes à l'initiative de la BDD car il vous simplement de créer la structure et de l'alimenter selon vos choix.
Dans le deuxième cas, c'est à vous de structurer ces données issues d'une extraction avec les problèmes d'export.
Dans tous les cas la structure est importante donc les principes de base sont :
1) Toutes les données doivent être réunies dans un seul et unique
onglet.
2) La première ligne est uniquement réservée au titre des colonnes.
Important :
* Pas de cellules vides dans les titres de colonne.
* Pas de cellules fusionnées.
* Pas de doublons dans les titres de colonne.
3) La première colonne doit être la clé primaire de la BDD
Important :
* Pas de cellules vides dans cette colonne.
4) Dans la BDD
* Pas de ligne vide ni de colonne vide.
* Pas de Totaux ou de Sous-Totaux, ni de calculs intermédiaires.
6) N'utilisez qu'une seule formule par colonne
Donc normalement si vous respectez ces six principes de base vous avez déjà une BDD bien structurée.
* Pas de Totaux ou de Sous-Totaux, ni de calculs intermédiaires.
6) N'utilisez qu'une seule formule par colonne
Donc normalement si vous respectez ces six principes de base vous avez déjà une BDD bien structurée.
A ce stade vous pouvez gérer, modifier ou supprimer des données soit manuellement soit via un formulaire que vous pouvez créer pour la gestion des données.... Consultez la page CREER SON FORMULAIRE dans le présent blog.
Gestion d'un fichier de données issue d'une extraction de données d'une base de données.
L'ensemble des données de ce genre de fichier sont pratiquement le reflet de la source à savoir avec des paramétrages qui leur sont propres.
A titre d'exemple les dates peuvent apparaitre sous différents formats :
* Format américain
* Avec des points ou des tirets ou autres.
* Format Texte.
Donc si vous avez quelques milliers de ligne c'est extrêmement compliqué de corriger ces informations auxquelles s'ajoutent des informations du même type dans les autres colonnes du genre les NOMS en majuscule, minuscule et etc.
Mais il y a toujours des solutions et les Macros d'Excel permettent d'y remédier très rapidement et de gagner un temps fou pour que votre BDD soit exploitable.
Je vais vous mettre quelques macros très simple qui vont vous ravir de part leur simplicité d'utilisation.
Toutes macros à suivre sont exploitables en se positionnement dans la colonne de votre choix et peux importe la ligne car elles effectuent les modifications à partir de la deuxième ligne de la colonne jusqu'à la dernière ligne vide d'où l'intérêt d'avoir respecter les principes de mise forme de base expliqués ci-dessus.
Exemple N° 1 : Toutes les dates de la colonne sont au format 14.10.2018 donc non reconnues par Excel comme des dates.
Appliquez cette macro et toutes les données des cellules de la colonne seront modifiées pour être format DATE avec des Slash soit 14/10/2018
Option Explicit
Sub DATE_CHGT_POINT_PAR_SLASH()
Dim CEL As Range
Dim plg As Range
Dim J As Byte
Dim m As Byte
Dim A As Integer
Dim D As Date
Dim Col
Col = Left$(ActiveCell.Address(0, 0), (ActiveCell.Column < 27) + 2)
With ActiveSheet
Set plg = .Range(Col & "2:" & Col & .Range(Col & .Rows.Count).End(xlUp).Row)
End With
For Each CEL In plg
On Error Resume Next
J = Split(CEL.Value, ".")(0)
If Err <> 0 Then GoTo suite
m = Split(CEL.Value, ".")(1)
A = Split(CEL.Value, ".")(2)
D = CDate(A & "-" & m & "-" & J)
CEL.Value = D
suite:
On Error GoTo 0
Next CEL
End Sub
Le résultat de 14/10/2018 ne vous convient pas mais souhaitez une date avec le format suivant "jj mmmm yyyy" soit 14 octobre 2018 rien de plus simple.
Ajoutez dans la programmation la ligne suivante en dessous de CEL.Value = D
CEL.NumberFormat = "dd mmmm yyyy"
Donc avec NumberFormat vous pouvez définir le format que vous souhaitez à la date.
CEL.NumberFormat = "ddd dd mmmm yyyy" => Dimanche 14 octobre 2018
CEL.NumberFormat = "mmmm yyyy" => octobre 2018
CEL.NumberFormat = "dd mmm yyyy" => 14 oct 2018
et etc.
Exemple N° 2 : Si votre colonne comporte des dates avec des heures, exemple 14/10/2018 10:00, appliquez la macro suivante pour voir le résultat 14/10/2018.
Option Explicit
Sub SUPPRIMER_HEURE_DE_LA_DATE()
Dim CEL As Range
Dim plg As Range
Dim Col
Application.ScreenUpdating = False
Col = Left$(ActiveCell.Address(0, 0), (ActiveCell.Column < 27) + 2)
With ActiveSheet
Set plg = .Range(Col & "2:" & Col & .Range(Col & .Rows.Count).End(xlUp).Row)
End With
For Each CEL In plg
If IsDate(CEL) = True Then
CEL.Value = VBA.Int(CEL.Value)
CEL.NumberFormat = "dd/mm/yyyy"
End If
Next CEL
End Sub
Exemple N° 3 : Si votre colonne comporte des dates au format "Anglais", exemple 2023/06/10, appliquez la macro suivante pour voir le résultat 06/10/2023.
Option Explicit
Option Explicit
Sub ConvertirDateAnglaisFrançais()
Dim CEL As Range
Dim plg As Range
Dim Col
Application.ScreenUpdating = False
Col = Left$(ActiveCell.Address(0, 0), (ActiveCell.Column < 27) + 2)
With ActiveSheet
Set plg = .Range(Col & "2:" & Col & .Range(Col & .Rows.Count).End(xlUp).Row)
End With
For Each CEL In plg
If IsDate(CEL.Value) Then
CEL.Value = Format(CEL.Value, "dd/mm/yyyy")
End If
Next CEL
End Sub
Option Explicit
Sub CONVERTIR_LES_FORMULES_EN_VALEURS()
Dim CEL As Range
Dim plg As Range
Dim Col
Application.ScreenUpdating = False
Col = Left$(ActiveCell.Address(0, 0), (ActiveCell.Column < 27) + 2)
With ActiveSheet
Set plg = .Range(Col & "2:" & Col & .Range(Col & .Rows.Count).End(xlUp).Row)
End With
For Each CEL In plg
If CEL.HasFormula Then
CEL.Formula = CEL.Value
End If
Next CEL
End Sub
Exemple N°5 : Dans de nombreuses extractions, il se peut que les données de vos cellules comportent des ESPACES avant et/ou après les données qui y sont intégrées. Appliquez la macro suivante pour supprimer tous les espaces avant et/ou après.
Option Explicit
Sub SUPPRIME_ESPACE()
Dim CEL As Range
Dim plg As Range
Dim Col
Application.ScreenUpdating = False
Col = Left$(ActiveCell.Address(0, 0), (ActiveCell.Column < 27) + 2)
With ActiveSheet
Set plg = .Range(Col & "2:" & Col & .Range(Col & .Rows.Count).End(xlUp).Row)
End With
For Each CEL In plg
CEL.Value = RTrim(CEL.Value)
CEL.Value = LTrim(CEL.Value)
Next CEL
Application.ScreenUpdating = True
End Sub
Exemple N° 6 : Dans vos colonne de chiffres vous pouvez avoir des chiffres négatifs qui apparaissent de la manière suivante 10,5- ce qui est une valeur non prise en compte par Excel. Appliquez la macro suivante pour avoir des vrais chiffres négatif => -10,50.
Option Explicit
Sub INVERSER_CHIFFRES_NEGATIFS()
Dim CEL As Range
Dim plg As Range
Dim Col
Application.ScreenUpdating = False
Col = Left$(ActiveCell.Address(0, 0), (ActiveCell.Column < 27) + 2)
With ActiveSheet
Set plg = .Range(Col & "2:" & Col & .Range(Col & .Rows.Count).End(xlUp).Row)
End With
For Each CEL In plg
CEL = Replace(CEL, ".", "")
CEL.Value = CDec(CEL.Value)
Next CEL
For Each CEL In plg
'CEL = Replace(CEL, ",", ".")
If Right(CEL, 1) = "-" Then CEL.Value = Val("-" & Left(CEL, Len(CEL) - 1))
CEL.NumberFormat = "#,##0.00 €"
Next CEL
Application.ScreenUpdating = True
End Sub
Dans cette macro le format du chiffres restitués est = -10,50 €.
Changer la ligne suivante pour obtenir le format souhaité :
CEL.NumberFormat = "#,##0.00 €"
Exemple N° 7 : Dans votre colonne vous souhaitez changer un mot par un autre du genre changer le mot "banane" par "pomme", appliquez la macro suivante. Elle vous proposera une boite de dialogue pour le mot à changer et une autre boite de dialogue pour définir le mot de remplacement.
Option Explicit
Sub REMPLACER_UN_MOT_PAR_UN_AUTRE()
Dim Mot As Variant
Dim Replace As Variant
Dim CEL As Range
Dim plg As Range
Dim Col
Application.ScreenUpdating = False
Col = Left$(ActiveCell.Address(0, 0), (ActiveCell.Column < 27) + 2)
With ActiveSheet
Set plg = .Range(Col & "2:" & Col & .Range(Col & .Rows.Count).End(xlUp).Row)
End With
Mot = InputBox("Quel mot recherchez-vous ?", Title:="Recherche un mot")
Replace = InputBox("Par quel mot voulez vous remplacer ?", Title:="Remplacer le mot trouver")
If Mot = "" Then Exit Sub
For Each CEL In plg
CEL.Replace What:=Mot, Replacement:=Replace
Next CEL
Application.ScreenUpdating = True
End Sub
Exemple N° 8 : Dans votre colonne vous souhaitez mettre la première lettre des mots, intégrés dans les cellules, en Majuscule.
Sub PREMIERE_LETTRE_des_mots_en_MAJUSCULE()
Dim CEL As Range
Dim PLG As Range
Dim COL
Application.ScreenUpdating = False
COL = Left$(ActiveCell.Address(0, 0), (ActiveCell.Column < 27) + 2)
With ActiveSheet
Set PLG = .Range(COL & "2:" & COL & .Range(COL & .Rows.Count).End(xlUp).Row)
End With
For Each CEL In PLG
If CEL.HasFormula = False Then
CEL = Application.WorksheetFunction.Proper(CEL)
End If
Next CEL
Application.ScreenUpdating = True
End Sub
Exemple N° 9 : Dans votre colonne vous souhaitez mettre la première lettre des phrases, intégrés dans les cellules, en Majuscule.
Sub MAJUSCULE_en_début_de_phrase()
Dim CEL As Range
Dim PLG As Range
Dim COL
Application.ScreenUpdating = False
COL = Left$(ActiveCell.Address(0, 0), (ActiveCell.Column < 27) + 2)
With ActiveSheet
Set PLG = .Range(COL & "2:" & COL & .Range(COL & .Rows.Count).End(xlUp).Row)
End With
For Each CEL In PLG
If CEL.HasFormula = False Then
CEL = UCase(Mid(CEL, 1, 1)) & LCase(Mid(CEL, 2))
End If
Next CEL
Application.ScreenUpdating = True
End Sub
Exemple N° 10 : Dans votre colonne vous souhaitez mettre tous les mots en Majuscule.
Sub Tous_les_mots_en_MAJUSCULE()
Dim CEL As Range
Dim PLG As Range
Dim COL
Application.ScreenUpdating = False
COL = Left$(ActiveCell.Address(0, 0), (ActiveCell.Column < 27) + 2)
With ActiveSheet
Set PLG = .Range(COL & "2:" & COL & .Range(COL & .Rows.Count).End(xlUp).Row)
End With
For Each CEL In PLG
If CEL.HasFormula = False Then
CEL = UCase(CEL)
End If
Next CEL
Application.ScreenUpdating = True
End Sub
Exemple N° 11 : Dans votre colonne vous souhaitez mettre tous les mots en Minuscule.
Sub Tous_les_mots_en_MINUSCULE()
Dim CEL As Range
Dim PLG As Range
Dim COL
Application.ScreenUpdating = False
COL = Left$(ActiveCell.Address(0, 0), (ActiveCell.Column < 27) + 2)
With ActiveSheet
Set PLG = .Range(COL & "2:" & COL & .Range(COL & .Rows.Count).End(xlUp).Row)
End With
For Each CEL In PLG
If CEL.HasFormula = False Then
CEL = LCase(CEL)
End If
Next CEL
Application.ScreenUpdating = True
End Sub
Exemple N° 12 :
Sub INVERSER_la_CASSE()
Dim CEL As Range
Dim PLG As Range
Dim i As Long
Dim T As String
Dim IsCharUpper
Dim COL
Application.ScreenUpdating = False
COL = Left$(ActiveCell.Address(0, 0), (ActiveCell.Column < 27) + 2)
With ActiveSheet
Set PLG = .Range(COL & "2:" & COL & .Range(COL & .Rows.Count).End(xlUp).Row)
End With
For Each CEL In PLG
T = (CEL.Value = UCase(CEL))
CEL.Value = IIf(T, LCase(CEL), UCase(CEL))
Next CEL
Application.ScreenUpdating = True
End Sub
Exemple N° 13 : Dans votre colonne vous souhaitez supprimer les apostrophes devant les nombres.
Sub Supprimer_Apostrophe_devant_un_nombre()
Dim CEL As Range
Dim PLG As Range
Dim COL
Application.ScreenUpdating = False
COL = Left$(ActiveCell.Address(0, 0), (ActiveCell.Column < 27) + 2)
With ActiveSheet
Set PLG = .Range(COL & "2:" & COL & .Range(COL & .Rows.Count).End(xlUp).Row)
End With
For Each CEL In PLG
CEL.Value = CEL.Value
Next CEL
Application.ScreenUpdating = True
End Sub
Exemple N° 14 : Dans votre colonne vous souhaitez intégrer un chiffre (exemple zéro) dans les cellules vides.
Sub Remplacer_les_cellules_vides_par_des_zéros()
Dim CEL As Range
Dim PLG As Range
Dim COL
Application.ScreenUpdating = False
COL = Left$(ActiveCell.Address(0, 0), (ActiveCell.Column < 27) + 2)
With ActiveSheet
Set PLG = .Range(COL & "2:" & COL & .Range(COL & .Rows.Count).End(xlUp).Row)
End With
For Each CEL In PLG
If CEL = "" Or CEL = " " Then
CEL.Value = "0"
End If
Next CEL
End Sub
Exemple N° 15 : Dans votre colonne vous souhaitez convertir les dates en chiffre du jour.
Sub Convertir_la_date_en_CHIFFRE_du_jour()
Dim CEL As Range
Dim PLG As Range
Dim COL
Application.ScreenUpdating = False
COL = Left$(ActiveCell.Address(0, 0), (ActiveCell.Column < 27) + 2)
With ActiveSheet
Set PLG = .Range(COL & "2:" & COL & .Range(COL & .Rows.Count).End(xlUp).Row)
End With
For Each CEL In PLG
If IsDate(CEL) = True Then
With CEL
.Value = Day(CEL)
.NumberFormat = "0"
End With
End If
Next CEL
End Sub
Exemple N° 16 : Dans votre colonne vous souhaitez convertir les dates en années.
Sub Convertir_la_date_en_année()
Dim CEL As Range
Dim PLG As Range
Dim COL
Application.ScreenUpdating = False
COL = Left$(ActiveCell.Address(0, 0), (ActiveCell.Column < 27) + 2)
With ActiveSheet
Set PLG = .Range(COL & "2:" & COL & .Range(COL & .Rows.Count).End(xlUp).Row)
End With
For Each CEL In PLG
If IsDate(CEL) = True Then
With CEL
.Value = Year(CEL)
.NumberFormat = "0"
End With
End If
Next CEL
End Sub
Exemple N° 17 : Dans votre colonne vous souhaitez convertir les dates en mois format lettre.
Sub Convertir_la_dATE_en_MOIS_LETTRE()
Dim CEL As Range
Dim PLG As Range
Dim COL
Application.ScreenUpdating = False
COL = Left$(ActiveCell.Address(0, 0), (ActiveCell.Column < 27) + 2)
With ActiveSheet
Set PLG = .Range(COL & "2:" & COL & .Range(COL & .Rows.Count).End(xlUp).Row)
End With
For Each CEL In PLG
If IsDate(CEL) = True Then
With CEL
CEL = Format(CEL, "mmm")
End With
End If
Next CEL
End Sub
Exemple N° 18 : Dans votre colonne vous souhaitez convertir les dates en mois format chiffre.
Sub Convertir_la_dATE_en_MOIS_CHIFFRE()
Dim CEL As Range
Dim PLG As Range
Dim COL
Application.ScreenUpdating = False
COL = Left$(ActiveCell.Address(0, 0), (ActiveCell.Column < 27) + 2)
With ActiveSheet
Set PLG = .Range(COL & "2:" & COL & .Range(COL & .Rows.Count).End(xlUp).Row)
End With
For Each CEL In PLG
If IsDate(CEL) = True Then
With CEL
.Value = Month(CEL)
.NumberFormat = "0"
End With
End If
Next CEL
End Sub
Exemple N° 19 : Dans votre fichier vous souhaitez créer une plage nommée suivant la dernière ligne non vide de la colonne A.
Sub CreerPlageNomme()
D'autres macros vont suivre......
Dim WS As Worksheet
Dim DerniereLigne As Long
Dim Plage As Range
Dim MaPlage As String
Set WS = ActiveSheet
' Trouver la dernière ligne non vide dans la colonne A
DerniereLigne = WS.Cells(WS.Rows.Count, "A").End(xlUp).Row
' Définir la plage à partir de B2 jusqu'à la dernière ligne non vide de la colonne A
Set Plage = WS.Range("B2:B" & DerniereLigne)
MaPlage = InputBox("Mentionnez le nom de la plage : ")
' Créer une plage nommée
ThisWorkbook.Names.Add Name:=MaPlage, RefersTo:=Plage
End Sub
Exemple N° 20 : Dans votre fichier vous souhaitez créer une plage nommée suivant la dernière ligne non vide de la colonne A et suivant la colonne où une cellule est sélectionnée.
Sub CreerPlageNommeVersionCelluleColonne()
Dim WS As Worksheet
Dim DerniereLigne As Long
Dim Plage As Range
Dim MaPlage As String
Dim Colonne As String
Set WS = ActiveSheet
' Obtenir la colonne de la cellule sélectionnée
Colonne = ActiveCell.Column
' Trouver la dernière ligne non vide dans la colonne A
DerniereLigne = WS.Cells(WS.Rows.Count, "A").End(xlUp).Row
' Définir la plage à partir de la cellule de la 2ème ligne de la colonne sélectionnée jusqu'à la dernière ligne non vide de la colonne A
Set Plage = WS.Range(Cells(2, Colonne), Cells(DerniereLigne, Colonne))
MaPlage = InputBox("Mentionnez le nom de la plage : ")
' Créer une plage nommée
ThisWorkbook.Names.Add Name:=MaPlage, RefersTo:=Plage
End Sub
Exemple N° 21 : Dans votre fichier vous souhaitez mettre à jour toutes les plages données du fichier.
Sub ActualiserPlagesNommees()
Dim WS As Worksheet
Dim DerniereLigne As Long
Dim Plage As Range
Dim Nom As Name
Set WS = ActiveSheet
' Trouver la dernière ligne non vide dans la colonne A
DerniereLigne = WS.Cells(WS.Rows.Count, "A").End(xlUp).Row
' Parcourir toutes les plages nommées dans le classeur
For Each Nom In ThisWorkbook.Names
' Vérifier si la plage nommée est sur la feuille active
If Nom.RefersToRange.Parent.Name = WS.Name Then
' Déterminer la colonne de la plage nommée
Dim Colonne As String
Colonne = Split(Nom.RefersToRange.Address, "$")(1)
' Définir la plage à partir de la cellule de la 2ème ligne de la colonne jusqu'à la dernière ligne non vide de la colonne A
Set Plage = WS.Range(Colonne & "2:" & Colonne & DerniereLigne)
' Mettre à jour la référence de la plage nommée
Nom.RefersTo = Plage
End If
Next Nom
End Sub
Exemple N° 22 : Dans votre fichier vous souhaitez récupérer et lister les catégories et les sous catétorie.
Sub ub OrganiserLesDonnees()
Dim WsBDD As Worksheet, WsTest As Worksheet
Dim DerniereLigne As Long, i As Long, j As Integer
Dim Categorie As String, SousCategorie As String
' Définir les feuilles de calcul
Set WsBDD = Sheets("BDD")
Set WsTest = Sheets("TEST")
' Obtenir la dernière ligne de la feuille BDD
DerniereLigne = WsBDD.Cells(WsBDD.Rows.Count, "A").End(xlUp).Row
' Parcourir les lignes de la feuille BDD
For i = 2 To DerniereLigne
' Obtenir la catégorie et la sous-catégorie
Categorie = WsBDD.Cells(i, "A").value
SousCategorie = WsBDD.Cells(i, "Y").value
' Trouver la colonne pour la catégorie dans la feuille TEST
j = 1
While WsTest.Cells(1, j).value <> "" And WsTest.Cells(1, j).value <> Categorie
j = j + 1
Wend
' Ajouter la catégorie à la ligne 1 si elle n'est pas déjà là
If WsTest.Cells(1, j).value = "" Then
WsTest.Cells(1, j).value = Categorie
End If
' Ajouter la sous-catégorie à la colonne appropriée
WsTest.Cells(WsTest.Rows.Count, j).End(xlUp).Offset(1, 0).value = SousCategorie
Next i
Dim DerniereColonne As Long
' Définir la feuille de calcul
'Set WsTest = Sheets("TEST")
' Obtenir la dernière colonne de la feuille TEST
DerniereColonne = WsTest.Cells(1, WsTest.Columns.Count).End(xlToLeft).Column
' Parcourir les colonnes de la feuille TEST
For i = 1 To DerniereColonne
' Supprimer les doublons dans la colonne
WsTest.Columns(i).RemoveDuplicates Columns:=1, Header:=xlYes
Next i
End Sub
Comment arranger une base de donnée pour faire des estimations , analyse statistique
RépondreSupprimerBonjour,
RépondreSupprimerC'est un choix qui vous est propre car cela dépend des données que vous avez dans votre fichier Excel même s'il s'agit d'un export sur base de données (généralement exporté au format .CSV).
vous base de données est importante je vous suggère de gérer vos estimation ou analyse statistique via des plages nommées.
Pour vos statistiques vous pouvez utiliser les Tableaux Croisés Dynamiques (TCD) ou créer vos propres statistiques dans des feuilles avec des formules spécifiques.
Pour informations j'ai déjà traité ce genre de base de données avec des milliers de lignes et une centaine de colonnes, et je n'ai pas eu de contraintes particulières mais c'est une organisation plus ou moins complexe car les formules alourdissent la gestion et la manipulations du fichier mais on peut contourner cette problématique.
Cdt
Bonjour
RépondreSupprimerPour information si vous avez, à titre professionnel, des projets de développement ou de gestion de données, de statistiques ou si votre objectif est de gagner du temps et d'automatiser vos tâches avec Excel, nul besoin de perdre du temps à connaître toutes les fonctionnalités de cette application, n’hésitez pas à me contacter (Adresse mail dans l'onglet contact du présent blog)
Détaillez-moi succinctement votre projet et je vous apporterai, rapidement, une réponse sur la faisabilité de celui-ci.
Merci pour votre tutoriel
RépondreSupprimer