Microsoft 365 Excel Somme plusieurs critères d'une même cellule à 2 conditions

[Lulu974]

Bonjour à tous,

Je cherche désespérément une formule qui additionnerait des chiffres en fonction de 2 conditions différentes avec des critères présents dans une seule et même cellule.

Je vous explique dans mon fichier excel j'ai 2 onglets : onglet 1 "Base de données brute" et onglet 2 "Parc par secteur - région".
Dans mon onglet 1 colonne D, je voudrais que la formulaire me donne la somme du nombre de véhicule dans mon parc par secteur colonne B et par région colonne C.
La plage de recherche de ce fameux nombre de véhicules se trouve dans l'onglet 2.

Mon problème c'est que la formule doit chercher :

• dans une seule et même cellule B2 plusieurs critères séparés par des ";" (critères dont le nombre est répertorié dans l'onglet 2) et
• en parallèle dans une autre et même cellule C2 plusieurs critères séparés également par des ";" (critères dont le nombre est également répertorié dans l'onglet 2)

Exemple :
dans l'onglet 1, première ligne, je voudrais le nombre total du parc dans la cellule D2, je dois donc prendre en compte :

• B2 => Secteur 1;Secteur 2;
  ET
• C2 => Sud-est;Rhône-Alpes;

Les données chiffrées correspondant à ces critères "Secteur 1;Secteur 2;" et "Sud-est;Rhône-Alpes;" sont répertoriés dans l'onglet 2, ce qui devrait faire au total 398 dans la cellule D2 de mon onglet 1.

J'ai essayé SOMMPROD / SOMME.SI.ENS / SUBSTITUE / RECHERCHEV / un mix de tout ça, mais j'ai toujours la même problématique c'est que les critères sont dans une seule et même cellule B2 et C2 que je ne peux pas dissoudre.

Je ne sais pas si j'ai été clair, n'hésitez pas à me demander plus de précisions si besoin.
Merci d'avance pour votre aide.
Lucie

 

[Hasco]

Bonjour,

Dans le fichier joint vous trouverez une solution par power query, qui me semble convenir à la situation.
Pour cela j'ai transformé vos tableaux en Tableaux structurés le premier nommé T_Journal l'autre T_Secteurs afin de pouvoir les interroger par PowerQuery.

Dans les Colonnes 'Régions' et 'Secteur' du premier, les point-virgules finaux sont-ils indispensables ?

Attention : Pour power query 'Sud-est' est différent de 'Sud-Est'. Aussi attention à la casse de vos données( minuscules, majuscules).

Lorsque les données changent, actualiser la requête.

cordialement

 

[Lulu974]

Bonjour,

Dans le fichier joint vous trouverez une solution par power query, qui me semble convenir à la situation.
Pour cela j'ai transformé vos tableaux en Tableaux structurés le premier nommé T_Journal l'autre T_Secteurs afin de pouvoir les interroger par PowerQuery.

Dans les Colonnes 'Régions' et 'Secteur' du premier, les point-virgules finaux sont-ils indispensables ?

Attention : Pour power query 'Sud-est' est différent de 'Sud-Est'. Aussi attention à la casse de vos données( minuscules, majuscules).

Lorsque les données changent, actualiser la requête.

cordialement

Bonjour Hasco,

Mille mercis pour votre aide vous êtes au top !!!
Je ne connais pas Power Query mais je vois qu'il fait des merveilles
Yes c'est noté pour les majuscules et minuscules.
Les ";" finaux ne sont malheureusement à garder car il s'agit d'une extraction de base de données "Forms" que je ne peux pas modifier.
Là s'agissait d'un fichier test. Du coup il faut que je reproduise cela dans mon fichier officiel. Comme je ne sais pas utiliser Power Query (et qu'il ne s'agit pas de formule) je suis un peu embêtée pour reproduire votre manip sur mon fichier officiel. Je ne peux pas mettre le fichier officiel ici.
Pouvez-vous m'aider ?
Merci d'avance
Lucie

 

[Hasco]

Re,

D'abord pour que cela fonctionne tel quel sur votre 'fichier officiel' il vous faudra transformer vos tableaux en tableaux structurés et les nommer pour l'un 'T_Journal' et pour l'autre 'T_Secteur'.
T_Journal devra obligatoirement comporter les colonnes 'Date', 'Secteur' et 'Région'
T_Secteur devra obligatoirement comporter la colonne 'Secteur' et pour les autres colonnes le nom des Régions idoines, tels qu'ils apparaissent dans le premier tableau.

Tableaux structurés Excel

Tableaux structurés Excel

fauconnier.developpez.com

Cliquez sur le bouton "Requêtes et connexions" de l'onglet de rubban 'Données'.

Dans le fichier exemple que je vous ai donné, ouvrez également 'Requêtes et connexions'
Dans le panneau qui s'affiche à droite de votre espace de travail, vous trouverez la requête correspondant à T_Journal, click-droit sur son nom -> copier.

Dans le fichier 'officiel', click-droit dans le panneau des requêtes et connexion -> coller

Comme cette requête est dépendante également de la seconde, elles seront automatiquement copier/coller simultanément.

Faites un click-droit sur la requête 'T_Journal' du fichier officiel -> 'Charger dans' remplissez la fenêtre qui s'ouvre (c'est simple) :

N'ajoutez pas (à moins d'un besoin particulier) les données au modèle de données.

Validez et.... voyez ce qui se passe.

Si cela ne fonctionne pas, click-droit sur le nom de la requête -> 'Modifier'
l'interface d'édition des requêtes s'ouvre.
Dans son panneau de droite qui montre le nom des différentes étapes de transformation, cliquez sur le premier nom (Source) puis si le panneau central vous présente en son bord supérieur une bande jaune avec un message d'erreur, venez ici avec son texte et le nom de l'étape concernée.

Dans l'image ci-dessous, après avoir créer un erreur pour démonstration sur l'étape source, j'obiens :

Cordialement

 

[Lulu974]

Re,

D'abord pour que cela fonctionne tel quel sur votre 'fichier officiel' il vous faudra transformer vos tableaux en tableaux structurés et les nommer pour l'un 'T_Journal' et pour l'autre 'T_Secteur'.
T_Journal devra obligatoirement comporter les colonnes 'Date', 'Secteur' et 'Région'
T_Secteur devra obligatoirement comporter la colonne 'Secteur' et pour les autres colonnes le nom des Régions idoines, tels qu'ils apparaissent dans le premier tableau.


Cliquez sur le bouton "Requêtes et connexions" de l'onglet de rubban 'Données'.

Dans le fichier exemple que je vous ai donné, ouvrez également 'Requêtes et connexions'
Dans le panneau qui s'affiche à droite de votre espace de travail, vous trouverez la requête correspondant à T_Journal, click-droit sur son nom -> copier.

Dans le fichier 'officiel', click-droit dans le panneau des requêtes et connexion -> coller

Comme cette requête est dépendante également de la seconde, elles seront automatiquement copier/coller simultanément.

Faites un click-droit sur la requête 'T_Journal' du fichier officiel -> 'Charger dans' remplissez la fenêtre qui s'ouvre (c'est simple) :
Regarde la pièce jointe 1127427
N'ajoutez pas (à moins d'un besoin particulier) les données au modèle de données.

Validez et.... voyez ce qui se passe.

Si cela ne fonctionne pas, click-droit sur le nom de la requête -> 'Modifier'
l'interface d'édition des requêtes s'ouvre.
Dans son panneau de droite qui montre le nom des différentes étapes de transformation, cliquez sur le premier nom (Source) puis si le panneau central vous présente en son bord supérieur une bande jaune avec un message d'erreur, venez ici avec son texte et le nom de l'étape concernée.

Dans l'image ci-dessous, après avoir créer un erreur pour démonstration sur l'étape source, j'obiens :
Regarde la pièce jointe 1127428

Cordialement

Mille mercis Hasco top
Je découvre de nouvelles choses c'est top
Je vous tiens au courant.
Bonne journée à vous

 

[AtTheOne]

Bonjour @lrevolus , Bonjour @Hasco, Bonjour à tous.
Je n'avais pas suivi l'évolution du fil et je suis passé par une fonction VBA.
Bien que cela fasse double emploi je poste quand même mes reflexions .

J'ai créé un tableau structuré sur les données de la feuille "Parc par secteur - région" nommé "_Tb_Secteur_Région"

• J'ai créé un module M0_Constantes qui contient les constantes utilisées (nom du tableau et titres)
• J'ai créé un module M01_Fonctions qui contient la fonction
• Les noms VBA des feuilles sont passés de Feuil1, Feuil2 à F_BdD_Brute, F_Répartition.

Module contenant les constantes

Public Const Tb_Sect_Rég As String = "_Tb_Secteur_Région"
Public Const Titre_Secteur As String = Tb_Sect_Rég & "[Secteur]"
Public Const Entêtes As String = Tb_Sect_Rég & "[#Headers]"

Module contenant la fonction

Function Total_Parc(Secteur As Range, Région As Range) As Long
    
     Dim I As Integer, J As Integer, C As Integer, L As Integer
     Dim Nb_Régions As Integer, Régions(), Tb_Test_Rég(), Nb_Secteurs As Integer, Secteurs(), Tb_Test_Sect(), Tb_Répartition(), Critères
    
     'Les régions dans un tableau (entête sauf 1ère colonne)
     Nb_Régions = F_Répartition.Evaluate(Tb_Sect_Rég).Columns.Count - 1
     Régions = F_Répartition.Evaluate(Entêtes).Resize(1, Nb_Régions).Offset(0, 1).Value
     For I = 1 To Nb_Régions: Régions(1, I) = UCase(Régions(1, I)): Next I     'Passage en majuscule
    
     'Les secteurs dans un tableau
     Secteurs = F_Répartition.Evaluate(Titre_Secteur).Value
'     Secteurs = F_Répartition.[_Tb_Secteur_Région[Secteur]].Value
     Nb_Secteurs = UBound(Secteurs, 1)
     For I = 1 To Nb_Secteurs: Secteurs(I, 1) = UCase(Secteurs(I, 1)): Next I  'Passage en majuscule
    
     'Les valeurs du tableau de répartion
     Tb_Répartition = F_Répartition.Evaluate(Tb_Sect_Rég).Resize(, Nb_Régions).Offset(0, 1).Value
    
     'Critères sur les régions
     '¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
     Critères = Split(Région.Value, ";")                             'Hachage de l'argument Région
     LimSup = UBound(Critères, 1)
    
     For I = 0 To LimSup: Critères(I) = UCase(Critères(I)): Next I   'Passage en majuscule
     ReDim Tb_Test_Rég(1 To Nb_Secteurs, 1 To Nb_Régions)            'Tableau d'évaluation des critères Régions
    
     For I = 1 To Nb_Régions
          For J = 0 To LimSup
               For L = 1 To Nb_Secteurs
                    'Evaluation du critère Région
                    Tb_Test_Rég(L, I) = Tb_Test_Rég(L, I) + Abs(Régions(1, I) = Critères(J))
               Next L
          Next J
     Next I
    
     'Critères sur les secteurs
     '¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
     Critères = Split(Secteur.Value, ";")                             'Hachage de l'argument Secteur
    
     LimSup = UBound(Critères, 1)
     For I = 0 To LimSup: Critères(I) = UCase(Critères(I)): Next I    'Passage en majuscule
     ReDim Tb_Test_Sect(1 To Nb_Secteurs, 1 To Nb_Régions)            'Tableau d'évaluation des critères Secteurs
    
     For I = 1 To Nb_Secteurs
          For J = 0 To LimSup
               For C = 1 To Nb_Régions
                    'Evaluation du critère Secteur
                    Tb_Test_Sect(I, C) = Tb_Test_Sect(I, C) + Abs(Secteurs(I, 1) = Critères(J))
               Next C
          Next J
     Next I
    
     'Application des critères Régions et Secteurs au tableau de répartion
     For L = 1 To Nb_Secteurs: For C = 1 To Nb_Régions
          Tb_Répartition(L, C) = Tb_Test_Sect(L, C) * Tb_Test_Rég(L, C) * Tb_Répartition(L, C)
     Next C: Next L

     'Totalisation du tableau de répartion filtré
     Total_Parc = WorksheetFunction.Sum(Tb_Répartition)


End Function

Il n'y a plus qu'à appeler la fonction Total_Parc(Secteur, Région) avec ses paramètres dans la colonne ad hoc de la feuille "Base de données brute"

Voir le fichier joint

Amicalement
Alain

 

[Lulu974]

Mille mercis Hasco top
Je découvre de nouvelles choses c'est top
Je vous tiens au courant.
Bonne journée à vous

Bonjour @Hasco , @AtTheOne

Je vous fais un petit retour.
J'ai utilisé la méthode Power Query avec des difficultés au départ sur l'une des requêtes (la dernière) car j'avais des cellules vide au niveau de la colonne date. J'ai mis 2 jours à chercher et à comprendre et j'y suis arrivée.
Je vais tenter la méthode Macro pour voir sur j'y arrive également.
Cependant la méthode Power Query fonctionne super bien. C'est top !
Mille mercis à vous pour votre aide, votre expérience, et votre patience.
Lucie

 

[Hasco]

Bonjour,

Merci beaucoup de ce retour et de vos efforts.

A bientôt

 

[AtTheOne]

Bonsoir Lucie (@lrevolus), Bonsoir à tous
Merci de ta curiosité, bon courage pour tes essais en VBA.
Si tu as besoin d'éclaircissements, n'hésite pas.
Amicalement
Alain