recherchev matricielle

[heriss]

Bonsoir à toutes et à tous,


Le but est de calculer la somme des valeurs qui appartiennent à une catégorie donnée (qui contient elle-même plusieurs sous-catégories).
Les enregistrements sont sur une feuille qui n'est pas modifiable (on ne peut y ajouter de formule) et la clef identifiant est du type:
typ + numérique + quelquechose de variable (pour les repérer et faire des sommes par type j'utilise une formule trouve (ou cherche) dans une sommeprod cf C3).


une possibilité consiste à faire la somme des somme prod (soit schématiquement)
Total catégorie 1 = sommeprod((typ1 + typ2 + typ3) * vecteur valeurs)

mais cela ne me plaît pas car je ne sais pas à l'avance combien de sous catégories je vais avoir au sein d'une même catégorie.
Auriez-vous d'autres idées?? (si possible autres que le VBA)

Merci d'avance
Heriss [file name=test_somme_categorie.zip size=2770]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/test_somme_categorie.zip[/file]

 

[andré]

Salut,

Je ne suis peut-être pas encore bien réveillé, mais je n'ai rien compris à ta question.
Ne pourrais-tu donner un mot d'explication supplémentaire ?

Â+

 

[heriss]

Salut André,

A la relecture ce n'est pas plus clair pour moi... j'aurai mieux fait d'aller me coucher hier soir.

J'attache un nouvel exemple qui sera cette fois çi je l'espère plus clair.


Merci d'avance,
Heriss



P.S: le titre de mon message est VRAIMENT incompréhensible :-| [file name=test_somme_categorie2.zip size=5455]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/test_somme_categorie2.zip[/file]

 

[Gael]

Bonjour Heriss, Bonjour André,

J'ai bien vu ton dernier fichier mais dans ce type de problème, il faut que la question soit vraiment précise. Comme André, je vois à peu près ce que tu veux mais c'est un peu flou.

Dans ton fichier, il n'y a aucune formule, on ne sait donc pas comment ça fonctionne actuellement.

Je ne comprends pas non plus comment faire le lien entre les données et les paramètres (comment savoir à quelle marque appartient un produit donné).

Merci d'apporter encore quelques précisions.

@+

Gael

PS: En analysant la formule en C8, j'ai mieux compris la demande mais je n'ai pas encore trouvé de solution.

Gael

Message édité par: Gael, à: 03/01/2006 12:50

 

[heriss]

Bonjour Gael, Bonjour André,

Encore désolé du manque d'explications mais à bosser dessus en continu, les choses semblent être évidentes.

Pour obtenir toutes les valeurs d'une marque en me basant sur le libellé de la transaction je peux faire:

=SOMMEPROD(ESTNUM(CHERCHE(nom de la marque;Data_ref))*1;Data_value)

et donc si j'ai n marques au sein d'un groupe/catégorie j'aurai:

=SOMMEPROD(ESTNUM(CHERCHE(Marque1;Data_ref))*1+ESTNUM(Marque2(B4;Data_ref))*1+ (ainsi de suite) +ESTNUM(CHERCHE(nième_Marque;Data_ref))*1;Data_valeurs)

La formule est lourde et demande de savoir exactement combien de marques l'on a au sein d'un groupe/catégorie (le nombre est rarement fixe d'une catégorie à l'autre). Je souhaite donc trouver une simplification en utilisant une formule excel mais je cale.

Si seulement je pouvais trouver un moyen de faire tourner une fonction recherchev, ou cherche/trouve sur une liste de valeurs et non sur une valeur simple, je serai sauvé!!!

Merci d'avance pour vos suggestions et remarques,

Cordialement,
Heriss

 

[Gael]

Bonjour Heriss, bonjour André,

Pour simplifier, je te propose un tableau récapitulatif qui permet de 'utiliser que des formules simples et d'avoir tous les résultats à jour.

J'ai créé une feuille 'Tableau'.

@+

Gael [file name=somme_categorie_V1.zip size=5408]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/somme_categorie_V1.zip[/file]

 

[heriss]

Bonjour Gael,

Merci pour la proposition, malheureusement ce n'est pas possible pour moi car j'ai des impératifs strictes en terme de présentation des chiffres.

Du coup je continue à chercher et je dois avouer que je me suis résolu à faire appel au VBA sous la forme d'une fonction pour résoudre ce problème.

Mon but est de créer une fonction qui ferai un test pour identifier si les libellés contiennent le nom d'une série de marques et donnerai un vecteur résultat d'une seule colonne (contenant des 0 et des 1) pour pouvoir être imbriquée dans une formule de sommeprod.

du coup ça me donne quelque chose du type:

=SOMMEPROD(multicherche(Brand;Data);Data_value)

Brand (sélection des marques de la catégorie)
Data (libellés des transactions)

avec la fonction multicherche définit comme il suit:

Function multicherche(cherche, donnee As Variant)
Dim vecteur_cherche(), vecteur_donnee(), vecteur_resultat() As String
Dim i, j, max_i, max_j, valeur As Integer
Dim cel As Range

'intialise les variables
i = 0
j = 0
max_i = 0
max_j = 0
valeur = 0

'compte le nombre de valeurs à chercher
For Each cel In cherche.Cells
max_i = max_i + 1
Next cel

'donne la dimension de la zone de données
For Each cel In donnee.Cells
max_j = max_j + 1
Next cel

'on dimensionne les tableaux
ReDim vecteur_cherche(max_i)
ReDim vecteur_donnee(max_j)
ReDim vecteur_resultat(max_j)

'on remplit le tableau vecteur_cherche avec les valeurs de la zone cherche
For i = 1 To max_i
vecteur_cherche(i) = cherche.cel.Offset(i - 1)
Next i

'on remplit le tableau vecteur_données avec les valeurs de la zone donnée
For j = 1 To max_j
vecteur_donnee(j) = donnee.cel.Offset(j - 1)
Next j

'teste si la valeur cherchée est dans la zone de données (alors +1) et attribue la valeur au vecteur résultat
For j = 1 To max_j
For i = 1 To max_i
If InStr(vecteur_donnee(j), vecteur_cherche(i)) > 0 Then
valeur = 1
'teste si le texte de la ligne i du vecteur recherche est contenu dans le texte de la ligne j du vecteur résultat
Next i
vecteur_resultat(j) = valeur
'réinitialise le compteur et passe à la ligne suivante du vecteur résultat
valeur = 0
Next j


multicherche = vecteur_resultat

End Function


Bien sûr je ne suis pas bon en VBA et du coup ça plante. Si vous pouviez me dire ou je me trompe et m'aider à finir vous seriez bien sympa.

Merci d'avance et bonne soirée,
Heriss

 

[JeanMarie]

Bonsoir

Attention avant d'utiliser la formule ci-dessous, tu dois déjà adapter ton fichier, toutes les valeurs de la plage Param!B$2:E$8 doivent être entourées de *, (exemple en B3 *Buitoni*). de plus les cellules vides doivent contenir impérativement une valeur, tu peux mettre en espace dans la cellule (sans les *). Et regarde si cette formule à mettre en H4 te convient
=BDSOMME(Données!A$1:B$14;2;INDEX(Param!B$2:E$8;0;EQUIV(G4;Param!B$1:E$1;0)))

PS : J'ai oublié de te précisr qu'il faut évidement que les valeurs des cellules H3:H7 correspondent aux valeurs des cellules Param!B$1:E$1. Pour avoir le mot Total dans la cellule, j'ai utilisé un format personnalisé 'Total '@

@+Jean-Marie

Message édité par: JeanMarie, à: 05/01/2006 07:43

 

[heriss]

Bonjour à tous,

Merci beaucoup Jean-Marie pour l'idée de la formule BDSomme. Jusqu'à présent je ne voyais pas l'utilité de la série des formules BDmachin... maintenant c'est chose faite.

Néanmoins la mise en place d'une telle formule n'est pas compatible en l'état avec les impératifs de présentation (je sais c'est contraignant mais je n'y peux rien, c'est le pb des états financiers).

La définition des critères est contraignante surtout que dois ensuite jongler avec plusieurs autres critères (le type de compte, les dates, les sociétés à consolider...) et que j'ai plusieurs types de format différents à consolider.

(je joins un fichier d'exemple pour montrer sur quoi je travaille)

Je continue donc sur le projet en VBA mais ne trouve pas d'expication à mes messages d'erreur alors si vous pouvez m'aider, merci d'avance.

Heriss

 

[heriss]

Désolé voila le fichier

 

[heriss]

Mais c'est pas vrai ce matin!!!!

 

[heriss]

heriss écrit:

Mais c'est pas vrai ce matin!!!!

Je laisse tomber... :-[
bonne journée
Heriss

 

[papin]

Bonjour,

Je réveille ce post sur lequel je suis tombé par hasard.

Pas besoin de macros, ni de feuille spéciale pour calculs intermédiaires.

En prime, comment créer une liste déroulante qui dépend d'une autre...

Comment résoudre un problème avec Excel ? Une fois qu'on a compris ce qu'on veut, il suffit de voir s'il existe une fonction qui permet d'obtenir le résultat. Une fois qu'on a compris comment alimenter la fonction avec les bons arguments, ça devient simple. Si la fonction est BDSOMME, alors on doit se demander où sont les données à lire, indiquer le champ à calculer et fournir une liste de critères. C'est ce que j'ai fait.

Prérequis : maîtrise des fonctions DECALER et BDSOMME, et maîtrise des listes déroulantes dans des cellules (validation de données).
Je ne répondrai pas aux questions relatives à ces prérequis.