XL 2010 Comptage Complexe

patricktoulon à dit:

re
en fait c’était bon il manquait la colone "Ad" dans la listbox
tu m’étonne que ça matchait pas
si on ajoute la colonne "Ad" ca match
maintenant si tu veux l'enlever tu le dit

VB:

Option Explicit

Function GetlisteCountProp()
    'patricktoulon
    'Version 2
    Dim i As Long, d As Object, k, tb
    Dim y&, esP$, Cd&, Fa&, Ad&, Ch&, Rt&, Adoptable&, Non_Adoptable&, Dc&
    Set d = CreateObject("scripting.dictionary")
    tb = ThisWorkbook.Sheets("BD").Range("table").Value
    For i = 1 To UBound(tb)
        '//////////////////////////////////////////////////////////////////////////////////////////
        'selon les valeaur  dans les colonnes corespondante on met 1 ou 0 dans les variable du même non que le header
        y = Year(tb(i, 1))
        esP = Trim(tb(i, 4))
        Cd = Abs(Trim(tb(i, 3)) = "Cd")
        Fa = Abs(Trim(tb(i, 3)) = "Fa")
        Ad = Abs(Trim(tb(i, 3)) = "Ad")
        Ch = Abs(Trim(tb(i, 3)) = "Ch")
        Rt = Abs(Trim(tb(i, 3)) = "Rt")
        Adoptable = Abs(Trim(tb(i, 5)) = "Adoptable")
        Non_Adoptable = Abs(Trim(tb(i, 5)) = "Non Adoptable")
        Dc = Abs(Trim(tb(i, 6)) <> "")
        'MsgBox Join(Array(y, esP, cd, fa, Ad, Ch, Rt, Adoptable, Non_Adoptable, Dc))
        '//////////////////////////////////////////////////////////////////////////////////////////

        'Maintenant on va alimenter le dico selon si la clé année et bestiole existe ou pas
        If Not d.exists(y & "_" & esP) Then
            ' donc si il n'existe pas on ajoute un array avec les valeur précédemment obtenue
            d(y & "_" & esP) = Array(y, esP, Cd, Fa, Ad, Ch, Rt, Adoptable, Non_Adoptable, Dc)
        Else
            'sinon on reprend l'array de la clé
            'et on additionne les items de l'array avec les valeurs obtenue Dim arr
            Dim arr
            arr = d(y & "_" & esP)
            arr(2) = arr(2) + Cd
            arr(3) = arr(3) + Fa
            arr(4) = arr(4) + Ad
            arr(5) = arr(5) + Ch
            arr(6) = arr(6) + Rt
            arr(7) = arr(7) + Adoptable
            arr(8) = arr(8) + Non_Adoptable
            arr(9) = arr(9) + Dc
            d(y & "_" & esP) = arr
        End If
    Next
    'k = d.keys
    'MsgBox Join(d(k(1)), ";")
    '//////////////////////////////////////////////////////////////////////////////////////////
    ' voila maintenant on a un dictionnaire rempli avec   des clés et un array a chaque clé
    ' exemple
    ' clé = "2024_chien"
    'et les valeaurs de l'array = [2024;Chien;4;4;4;0;0;12;0;0]
    '
    'il nous reste plus a retranscrire tout ca dans une variable tableau
    i = 0
    Dim xr, c&
    ReDim tbl(d.Count, 9) 'on dimentionne une variable tableau comme le dictionnaire avec 9 colonnes(EN BASE 0!!!!)
    For Each k In d.keys
        xr = d(k)
        For c = 0 To UBound(xr)
            tbl(i, c) = xr(c)
        Next
        i = i + 1
    Next
    GetlisteCountProp = tbl 'la fonction retourn le tableau
End Function

userform

VB:

Option Explicit

Private Sub UserForm_Activate()
ListTitre.Column = Array("Année", "Espèce", "Cd", "Fa", "Ad", "Ch", "Rt", "Adoptable", "Non Adoptable", "Décès")
ListTitre.ColumnCount = 11
ListTitre.ColumnWidths = "50;50;50;50;50;50;50;60;50;50"
ListCompt.ColumnCount = 11
ListCompt.ColumnWidths = "50;50;50;50;50;50;50;60;50;50"
ListCompt.List = GetlisteCountProp
End Sub

Regarde la pièce jointe 1202461

pour le filtre année tu t'en sortira pou pas ?

Cliquez pour agrandir...

patricktoulon à dit:

il n'y a pas de doublons possible
chez moi les résultat sont exacts avec l'exemple que tu nous a donné
pour vérifier j'ai testé avec l'autofilter
si ca te convient pas alors c'est la conception de ton tableau qui est a revoir
sachant par exemple que l'on compile par exemple année espece et décès
davec ton tableau il sera tout simplement impossible de faire une règle de doublons puisque l'on est sensés compiler les doublons
regarde ton tableau filtré les deux décès sont bien en 2020
comment veux tu que j'en zape un ? c'est absurde
mainenant si tu me dit qu'il faut dedoublonner la colonnes B alors là oui mais le resultat ne sera pas celui que tu présente dans ta capture c'est évident
Regarde la pièce jointe 1202468

Cliquez pour agrandir...

dysorthographie à dit:

dis moi ce qui ne va pasnstp

Cliquez pour agrandir...

patricktoulon à dit:

re
je ne dis pas que ton tableau est faux
je dis que si tu veux faire une listebox avec le comptage tu ne peux pas car
il faudrait enlever les doublons de n° dossier et là le resulta que tu souhaite dans ta capture est faux
tu ne peux pas cumuler Cd ,Fa,Ch,Rt etc.. et pas cumuler la colonne Dc ça n'a pas de sens
est ce que tu comprends mieux comme ça ?

Cliquez pour agrandir...

SouSql = "Select * From [BD$]" & IIf(Where = "", "", " Where " & Where)

• Sql : Initialise la requête SQL qui va résumer les données. Elle commence par sélectionner l'année (Années), l'espèce (Espece), et les sommes pour différentes catégories (Ad, Cd, etc.).
• FROM (" & vbCrLf : Le vbCrLf ajoute une nouvelle ligne pour rendre le code plus lisible. La requête sélectionne les données à partir d'une sous-requête qui va être construite ensuite.

Union des Différentes Catégories​

• SouSql : Une variable qui stocke une requête SQL de base pour sélectionner toutes les colonnes de la feuille Excel nommée BD.
• IIf(Where = "", "", " Where " & Where) : IIf est une fonction conditionnelle en VBA. Si la variable Where est vide, elle retourne une chaîne vide, sinon elle retourne une clause WHERE avec la condition spécifiée dans Where. Cela permet de filtrer les enregistrements si nécessaire.

Initialisation de l'Objet Connection ADO​

Déclaration et Construction de la Requête SQL​

With CreateObject("Adodb.connection")
  .Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=YES;"""

• CreateObject("Adodb.connection") : Crée une nouvelle connexion ADO pour interagir avec la source de données, dans ce cas, le fichier Excel.
• .Open : Ouvre la connexion en utilisant le fournisseur OLEDB pour accéder aux données dans le fichier Excel (ThisWorkbook.FullName), avec des propriétés étendues qui spécifient qu'il s'agit d'un fichier Excel au format 12.0 (Excel 2007 ou plus récent) et que la première ligne contient les en-têtes de colonnes (HDR=YES).

Construction de la Requête SQL Principale​

Sql = "SELECT Années, Espece, sum(Ad), sum(Cd), sum(Fa), sum(Ch), sum(Rt), sum(Adoptable), sum(NAdoptable), sum(Décès) FROM (" & vbCrLf

• Sql : Initialise la requête SQL qui va résumer les données. Elle commence par sélectionner l'année (Années), l'espèce (Espece), et les sommes pour différentes catégories (Ad, Cd, etc.).
• FROM (" & vbCrLf : Le vbCrLf ajoute une nouvelle ligne pour rendre le code plus lisible. La requête sélectionne les données à partir d'une sous-requête qui va être construite ensuite.

Union des Différentes Catégories​

Sql = Sql & "SELECT DISTINCT NoDossier, FORMAT([Date],'yyyy') AS Années, [Espece],1 as Ad,0 as Cd,0 as Fa,0 as Ch,0 as Rt,0 as Adoptable,0 as  NAdoptable, 0 as Décès FROM (" & SouSql & ") WHERE UCASE([Cat#]) = 'AD'" & vbCrLf

• SELECT DISTINCT : Sélectionne des enregistrements distincts pour éviter les doublons.
• FORMAT([Date],'yyyy') AS Années : Formate la colonne Date en année (yyyy) pour regrouper les résultats par année.
• UCASE([Cat#]) = 'AD' : Filtre les enregistrements où la colonne Cat# (convertie en majuscules) est égale à AD.
• 1 as Ad, 0 as Cd, ... : Attribue la valeur 1 à la colonne correspondante (ici Ad) et 0 aux autres colonnes. Cela permet de compter le nombre d'occurrences pour chaque catégorie lorsqu'on fera la somme.

Agrégation et Tri Final​

Sql = Sql & ") AS SubQuery GROUP BY Années, Espece Order by Années DESC"

• SubQuery : La sous-requête qui regroupe toutes les UNION ALL précédentes.
• GROUP BY Années, Espece : Regroupe les résultats par année et par espèce.
• ORDER BY Années DESC : Trie les résultats par année, en ordre décroissant (de la plus récente à la plus ancienne).

Exécution de la Requête​

With .Execute(Sql)
    If .EOF Then LST = Split(Space(10)) Else LST = .getrows
End With

• .Execute(Sql) : Exécute la requête SQL construite précédemment.
• If .EOF : Vérifie si la requête retourne des résultats (.EOF signifie "End Of File").
• LST = .getrows : Si des résultats sont retournés, ils sont stockés dans LST sous forme de tableau.

Résumé​

1. SQL (Structured Query Language)​

• Description: SQL est un langage standardisé utilisé pour interagir avec des bases de données relationnelles. Il permet de créer, lire, mettre à jour et supprimer des données.

2. Requête (Query)​

• Description: Une requête est une instruction écrite en SQL qui permet de récupérer ou de manipuler des données dans une base de données. Les requêtes peuvent être simples (sélectionner des données) ou complexes (inclure des calculs, des filtres, etc.).

3. SELECT​

• Description: Le mot-clé SELECT est utilisé pour récupérer des données dans une base de données. C'est la commande la plus courante en SQL.
• Exemple: SELECT * FROM Clients; — Cela sélectionne toutes les colonnes de la table Clients.

4. FROM​

• Description: Le mot-clé FROM indique la table d'où proviennent les données que l'on souhaite récupérer.
• Exemple: SELECT Nom FROM Clients; — Cela sélectionne uniquement la colonne Nom de la table Clients.

5. WHERE​

• Description: La clause WHERE est utilisée pour filtrer les résultats d'une requête en fonction de certaines conditions.
• Exemple: SELECT * FROM Clients WHERE Age > 30; — Cela sélectionne les clients âgés de plus de 30 ans.

6. GROUP BY​

• Description: GROUP BY permet de regrouper les lignes qui ont les mêmes valeurs dans des colonnes spécifiées, souvent en combinaison avec des fonctions d'agrégation comme SUM, COUNT, AVG.
• Exemple: SELECT Departement, COUNT(*) FROM Employes GROUP BY Departement; — Cela compte le nombre d'employés dans chaque département.

7. ORDER BY​

• Description: ORDER BY est utilisé pour trier les résultats d'une requête en fonction d'une ou plusieurs colonnes.
• Exemple: SELECT Nom FROM Clients ORDER BY Age DESC; — Cela sélectionne les noms des clients en les triant par âge décroissant.

8. JOIN​

• Description: Les JOIN permettent de combiner des lignes de deux ou plusieurs tables en fonction d'une colonne commune. Il existe plusieurs types de JOIN :
  • INNER JOIN : Récupère les enregistrements qui ont des valeurs correspondantes dans les deux tables.
  • LEFT JOIN : Récupère tous les enregistrements de la table de gauche, et les correspondances de la table de droite. Si aucune correspondance n'est trouvée, les résultats contiendront NULL pour les colonnes de la table de droite.
• Exemple: SELECT Employes.Nom, Departements.Nom FROM Employes INNER JOIN Departements ON Employes.DepartementID = Departements.ID;

9. UNION​

• Description: UNION est utilisé pour combiner les résultats de deux ou plusieurs requêtes SELECT. Les colonnes sélectionnées doivent être du même type dans toutes les requêtes.
• Exemple: SELECT Nom FROM Clients UNION SELECT Nom FROM Fournisseurs; — Cela récupère tous les noms des clients et des fournisseurs, sans doublons.

10. INSERT INTO​

• Description: INSERT INTO est utilisé pour ajouter de nouvelles lignes dans une table.
• Exemple: INSERT INTO Clients (Nom, Age) VALUES ('Jean', 32); — Cela ajoute un nouveau client nommé Jean, âgé de 32 ans.

11. UPDATE​

• Description: UPDATE permet de modifier les données existantes dans une table.
• Exemple: UPDATE Clients SET Age = 33 WHERE Nom = 'Jean'; — Cela met à jour l'âge de Jean à 33 ans.

12. DELETE​

• Description: DELETE est utilisé pour supprimer des enregistrements d'une table.
• Exemple: DELETE FROM Clients WHERE Age < 18; — Cela supprime tous les clients âgés de moins de 18 ans.

13. Subquery (Sous-requête)​

• Description: Une sous-requête est une requête imbriquée dans une autre requête SQL. Elle est utilisée pour effectuer des calculs ou des sélections supplémentaires.
• Exemple: SELECT Nom FROM Employes WHERE Salaire > (SELECT AVG(Salaire) FROM Employes); — Cela sélectionne les employés dont le salaire est supérieur à la moyenne.

14. Alias​

• Description: Un alias est un nom temporaire attribué à une table ou une colonne dans une requête pour en faciliter la lecture ou la gestion.
• Exemple: SELECT Nom AS ClientName FROM Clients; — Cela renomme temporairement la colonne Nom en ClientName.

15. NULL​

• Description: NULL représente une valeur manquante ou inconnue dans une base de données. C'est différent de zéro ou d'une chaîne vide.
• Exemple: SELECT * FROM Clients WHERE Adresse IS NULL; — Cela sélectionne les clients qui n'ont pas d'adresse renseignée.

16. Aggregate Functions (Fonctions d'Agrégation)​

• Description: Ce sont des fonctions utilisées pour effectuer des calculs sur un ensemble de valeurs et retourner un seul résultat.
  • SUM : Calcule la somme des valeurs.
  • COUNT : Compte le nombre de valeurs (ou de lignes).
  • AVG : Calcule la moyenne des valeurs.
  • MAX : Retourne la valeur maximale.
  • MIN : Retourne la valeur minimale.
• Exemple: SELECT AVG(Salaire) FROM Employes; — Cela calcule le salaire moyen des employés.

17. DISTINCT​

• Description: DISTINCT est utilisé pour retourner uniquement des valeurs uniques, en éliminant les doublons.
• Exemple: SELECT DISTINCT Departement FROM Employes; — Cela retourne la liste des départements sans répétition.

Je m'excuse. J'ai fait une erreur dans mon userform. Il manquait la catégorie "Ad" en l'ajoutant et en corrigeant le nombre de colonnes 10 au lieu de 9. les résultats sont décalés. Mais ils semblent bons.

Cliquez pour agrandir...