[XL-2003] Somme conditionnelle avec condition sur une partie de cellule + RechercheV [Résolu]

[Benoit Schwob]

Bonjour,

Un souci de syntaxe sur un enchevêtrement de fonctions...

J'ai deux tableaux :
- un bête tableau à deux colonnes :
… l'une porte un grand libellé client de longueur fixe (plein d'infos en vrac, dont le code postal qui est toujours sur les positions 59 à 63)
… et l'autre est un volume de ventes ;
- la table de correspondance département x région.

Ce que je veux faire : un tableau-récap de ventes par région.

Alors évidemment, je pourrais ajouter des colonnes à mon tableau de départ, pour sortir le code postal avec un STXT puis la région avec une RECHERCHEV ; ensuite un SOMME.SI fait le reste, ok.

Mais je veux le faire d'un seul coup - notamment parce que mon tableau de base aura un nombre variable de lignes et que je ne peux pas jouer à ajuster les lignes.

Qui a la clé ?
Merci de votre aide !!!

Benoît

[mercatog]

Bonjour
En F1 tu as le code postal, en G1 la formule suivante

=SOMMEPROD((STXT($A$1:$A$100;59;5)=TEXTE(F1;"00000"))*($B$1:$B$100))

[Benoit Schwob]

Bonsoir mercatog,

OK ça marche, merci de ton aide !
(Je fais même sauter la fonction TEXTE, en entrant mon code postal au format Texte.)

Maintenant, il me reste la correspondance "département > région" à caser : à côté j'ai une table qui relie 75-77-78-92-93-94-95 à "ILE DE FRANCE", 67-68 à ALSACE, etc.

J'imagine qu'il faut passer le résultat de STXT (sur 2 caractères) à une RECHERCHEV vers la table correspondance ? Mais je ne trouve pas la bonne syntaxe...

???

A plus,

Benoît

[corona]

Bonjour,
Je ne sais pas si ta table se trouve sur la même feuille mais voilà une syntaxe possible si le n° de département se trouve en colonne A et le nom de la région en B et le n° de département à chercher en F1
Formule à placer par exemple en G1

Code :

=RECHERCHEV(F1;$A$2:$B$100;2;FAUX)

Un très bon didacticiel de Pierre Fauconnier sur ce sujet

[mercatog]

Bonjour

Pour simplifier la lisibilité de la formule, j'ai nommé les plages comme ceci:

PlCode : Plage concernant les libellés clients
PlVal: Plage concernant les volumes de ventes
MatReg: Plage contenant le nom de la région en première colonne et les codes des départements y afférents (séparés d'un tiret ou autre), Exemple

ILE DE FRANCE.......|.......75-77-78-92-93-94-95
ALSACE.................|.......67-68

Si on cherche le cumul des ventes du département X contenu dans la cellule F21, Ci-joint formule (après tâtonnement)

Edit: Sans parenthèses et 59 à la place du 5 fait pour le test

=SOMMEPROD(NON(ESTERREUR(TROUVE(STXT(PlCode;59;2);RECHERCHEV(F21;MatReg;2;FAUX))))*PlVal)

[hben1961]

Bonjour à tous,

En supposant que les départements et Régions sont saisis en colonnes A et B respectivement, la valeur cherchée du département (deux chiffres) est en colonne C (on peut l'avoir par la fonction STXT à partir du code), la formule sera peut être:

Code :

=INDEX($B:$B;EQUIV(1;1*(ESTNUM(CHERCHE(C2;$A$1:$A$1000)));0))

Au cas où le code Département est introuvable, on peut écrire la formule:

Code :

=SI(ESTERREUR(EQUIV(1;1*ESTNUM(CHERCHE(C2;$A$1:$A$1000));0));"";INDEX($B:$B;EQUIV(1;1*(ESTNUM(CHERCHE(C2;$A$1:$A$1000)));0)))

ou (en 2007 et plus)

Code :

=SIERREUR(INDEX($B:$B;EQUIV(1;1*(ESTNUM(CHERCHE(C2;$A$1:$A$1000)));0));"")

Cordialement

[Benoit Schwob]

Bonjour,

Pour une RECHERCHEV nue ça va aller, merci Philippe pour la formule, hum...
(Au fait, le tuto en question de Pierre Fauconnier est bon parce qu'il est visuel. Je suis persuadé que la RechercheV, qui perturbe tant de néophytes, est fondamentalement un mécanisme visuel.)

Mercatog, merci de la piste avec la SOMMEPROD sur la RECHERCHEV, je crois que c'est la bonne direction. Mon souci, c'est que j'arrive pas à dépatouiller cette piste…

L'usage de TROUVE est dispensable puisque ma table de correspondance est normalisée.
J'ai fait un truc malin : un classeur-exemple, ci-joint ! (Désolé de pas l'avoir fait avant.)

J'en suis à quelque chose comme ça :

Code :

=SOMMEPROD((RECHERCHEV(PlgClients;TblCorresp;2;FAUX)=G3)*(PlgValeurs))

avec les noms :
- PlgClients pour la plage des clients (sur 1 colonne)
- TblCorresp pour celle de la table de correspondance département x région (sur 2 colonnes)
- PlgValeurs pour celle des valeurs à côté des clients (sur 1 colonne donc)
et G3 pour la région sur laquelle je veux faire la somme.

Mais ça ne fonctionne pas.
Vous voyez pourquoi ?!?

A plus,

Benoît

[Benoit Schwob]

(Et cinq minutes plus tard...)
J'avais zappé le STXT, n'importe quoi !

Bon, j'en suis là :

Code :

=SOMMEPROD((RECHERCHEV(STXT(PlgClients;59;2);TblCorresp;2;FAUX)=G2)*(PlgValeurs))

avec la région en G2.

Ce qui commence à fonctionner, mais renvoie soit zéro, soit le total général, et je ne vois pas pourquoi !
Classeur joint à jour...

Benoît

[mercatog]

Bonjour
Ce que j'avais compris dans ma proposition est démontré dans le fichier ci-joint
(la partie correspondance Région-Département est mise dans une feuille cachée)

[Benoit Schwob]

Joli !

C'est marrant, on travaillait la RechercheV dans des sens opposés.
- Tu raisonnais "chercher la région de réf dans une table de correspondance => ramener sa chaîne de départements => tester si le département est dedans". Et ça marche.
- J'essayais de faire "chercher les département dans une (autre) table de correspondance => ramener la région => tester avec la région de réf". Ce qui ne marche pas (encore).

Au fait, du coup on peut même basculer ta formule dans la feuille "Codification" et s'affranchir de la RechercheV !
(C'est ce que j'ai fait dans le classeur ci-joint, à partir du tiens : )

Code :

=SOMMEPROD(NON(ESTERREUR(TROUVE(STXT(PlgClients;59;2);B2)))*PlgValeurs)

Cinq fonctions, cinq paramètres, moins de 100 caractères...
.
Merci d'avoir poussé le truc au bout !
(Et merci d'avoir pris la peine de retourner ma table de codif.)

Je continue (un peu) de pousser mon raisonnement pour voir (à coups de EQUIV…) mais je suis prêt à passer le truc en résolu…

Benoît

[mercatog]

J'ai revu ta formule et j'ai 2 remarques:
1. SommeProd prend en considération dans les critères des plages
2. RechercheV donne la première occurrence trouvée


Il fallait intervertir la première condition de SommeProd.
Donc, disons que ta formule devrait être comme ceci:

=SOMMEPROD((STXT(PlgClients;59;2)=RECHERCHEV(G2;TblCorresp;2;FAUX))*PlgValeurs)

en inverssant l'ordre des 2 colonnes TblCoressp (Région en Colonne 1 et N° Département en Colonne 2)

Mais, hélas! aussi ne donnera pas le résultat escompté parce que encore une fois rechercheV donne seulement la première valeur trouvée.
En effet, pour l'Alsace on aura seulement la donnée du département 67 (le département 68 ne sera jamais pris en compte puisqu'il n'est pas la première valeur trouvée de RechercheV).


Edit

Dans ta dernière formule

=SOMMEPROD(NON(ESTERREUR(TROUVE(STXT(PlgClients;59;2);B2)))*PlgValeurs)

Le B2 de feuille codification, correspond au

RECHERCHEV(G2;TblCorresp;2;FAUX)

de la formule finale

=SOMMEPROD(NON(ESTERREUR(TROUVE(STXT(PlgClients;59;2);RECHERCHEV(G2;TblCorresp;2;FAUX))))*PlgValeurs)

[hben1961]

Bonjour à tous,

Avec un petit changement sur la table de correspondance (Départements/Régions) (départements pour chaque région sont concaténés en une seule cellule en colonne G séparés par un "-" ou autres) et la formule à utiliser pour répondre à ta question saisie en I2 pour la première région est :

Code :

=SOMMEPROD(ESTNUM(CHERCHE(STXT($A$2:$A$1000;59;2);G2))*($B$2:$B$1000))

cette formule sera copiée pour le reste des régions...


Cordialement

[mercatog]

Bonjour
En effet, on gagne une fonction

ESTNUM(TROUVE(...

et

NON(ESTERREUR(TROUVE(...

font la même chose.
L'important sur la logique de la fonction SommeProd.

[Benoit Schwob]

Bonsoir,

Mercatog, c'est ta remarque sur les formules qui m'a fait comprendre un truc :

J'ai revu ta formule et j'ai 2 remarques:
1. SommeProd prend en considération dans les critères des plages
2. RechercheV donne la première occurrence trouvée

C'est que je peux uniquement passer une cellule comme premier argument à la fonction RECHERCHEV. Et pas une plage ; or c'est ce que j'essayais désespérément de faire avec mon
RECHERCHEV(STXT(PlgClients;59;2);TblCorresp;2;FAUX)

Dans la foulée, je note cette même règle sur les arguments passés à INDEX et EQUIV notamment (parce que bon, j'ai essayé aussi...)

Bref ta solution n'est pas la meilleure : c'est la seule...

hben : j'ai l'impression que tu étais parti sur la même démarche que moi au début (département => région), avant de basculer aussi sur la vision "région => liste jointe de départements" de mercatog...

Merci encore de votre hyper-réactivité samedi dernier.
(Je bosse par intermittence sur la question et donc vos minutes sont des jours chez moi, hum...)

Benoît