GESTION D'UNE BASE DE DONNEES

 GESTION D'UNE BASE DE DONNÉES 

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.

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.

    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 mais souhaitez un date avec le format suivant "jj mmmm yyyy" soit 14 octobre 2018 rien de plus simple.
Ajouter dans la programmation la ligne suivante en dessou 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

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

Exemple N° 4 :  Votre colonne comporte uniquement des formules. Pour convertir les formules en valeur, appliquez cette macro et toutes les données des cellules de la colonne ne seront plus que des valeurs absolues.

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

D'autres macros vont suivre......