Sommeprod : Raccourcir une formule

cibleo · 30 Octobre 2008

Bonjour le forum,

La fin de l'année arrivant, j'en profite pour reprendre certains de mes fichiers déjà créés pour y effectuer un dépoussiérage notamment dans mes formules.

Ainsi la formule ci-dessous me permet de compter le nombre de lignes où figurent les mots suivants "repas" ou "boulangerie" ou "menu" ou "sandwich" dans la plage (B9:B71) en fonction de la plage (C9:C71) où figurent les initiales "st".

=SOMMEPROD((ESTNUM(CHERCHE("repas";'Caisse Janv08'!$B$9:$B$71))+(ESTNUM(CHERCHE("boulangerie";'Caisse Janv08'!$B$9:$B$71))+(ESTNUM(CHERCHE("menu";'Caisse Janv08'!$B$9:$B$71))+(ESTNUM(CHERCHE("sandwich";'Caisse Janv08'!$B$9:$B$71)))*1))*(ESTNUM(CHERCHE("st";'Caisse Janv08'!$C$9:$C$71)))*1))

Or j'aimerais savoir s'il était possible d'une part de raccourcir cette formule et d'autre part de la modifier car j'ai un autre petite souci.

Souvent en colonne B apparait ce type de libellé :

Frais Sandwich Boulangerie Grignon Cachan

Comme vous le voyez apparaît le mot "sandwich" et le mot "boulangerie".
Avec la formule ci-dessus, cela me compte la ligne 2 fois, quelle modification faudrait-il y apporter pour éviter que cela se produise ?
Vous allez me dire "Supprime 1 des 2 mots" c'est plus simple, mais je ne le souhaite pas 🙄

Pouvez-vous m'aider ?

Merci Cibleo

i0raek · 30 Octobre 2008

Re : Sommeprod : Raccourcir une formule

Bonjour Forum, cibleo !

Moi je verrais bien une petite formule matricielle du genre :

Code:

=Somme(1*(ou(B9:B71=ESTNUM(CHERCHE("repas";'Caisse Janv08'!$B$9:$B$71);
B9:B71=ESTNUM(CHERCHE("boulangerie";'Caisse Janv08'!$B$9:$B$71))

en validant la formule par Crtl + Maj + Enter
ce qui ne comptera la ligne qu'une fois ! 😉

cibleo · 30 Octobre 2008

Re : Sommeprod : Raccourcir une formule

Re bonjour à tous, iOraek,

Merci pour ta réponse mais j'ai du mal à poursuivre ta formule matricielle.

En colonnne B et C, j'ai les lignes décrites plus bas, j'aimerais obtenir = 3 avec ta formule matricielle déclinée comme ceci, j'obtiens 1, j'ai dû me planter quelque part 🙄

Question raccourcir c'est loin d'être gagné 😀

=SOMME(1*(OU(B9:B71=ESTNUM(CHERCHE("sandwich";'Caisse Janv08'!$B$9:$B$71));B9:B71=ESTNUM(CHERCHE("boulangerie";'Caisse Janv08'!$B$9:$B$71))));B9:B71=ESTNUM(CHERCHE("repas";'Caisse Janv08'!$B$9:$B$71));B9:B71=ESTNUM(CHERCHE("menu";'Caisse Janv08'!$B$9:$B$71));B9:B71=ESTNUM(CHERCHE("st";'Caisse Janv08'!$C$9:$C$71)))

Code:

[FONT=Tahoma][SIZE=2]Frais [COLOR=sienna][B]Sandwich[/B][/COLOR] Relay H Villejuif Hôpital             [/SIZE][/FONT][FONT=Tahoma][SIZE=2]St[/SIZE][/FONT]
[FONT=Tahoma][SIZE=2]Frais [COLOR=sienna][B]Sandwich[/B][/COLOR] [COLOR=sienna][B]Boulangerie[/B][/COLOR] Grignon Cachan   [/SIZE][/FONT][FONT=Tahoma][SIZE=2]St[/SIZE][/FONT]
[FONT=Tahoma][SIZE=2]Frais [B][COLOR=#a0522d]Repas[/COLOR][/B] Chatenay Malabry                         [/SIZE][/FONT][FONT=Tahoma][SIZE=2]St[/SIZE][/FONT]

Merci à tous Cibleo

Ps : je me suis peut-être aussi planté là. (en vert ci-dessous) mais en rectifiant j'obtiens 1 quand même au lieu de 3 🙁

B9:B71=ESTNUM(CHERCHE("st";'Caisse Janv08'!$C$9:$C$71)))

i0raek · 31 Octobre 2008

Re : Sommeprod : Raccourcir une formule

Bonjour à tous, cibleo !

est ce que tu pourrais envoyer un petit fichier d'exemple... se serait plus simple...

@++

cibleo · 31 Octobre 2008

Re : Sommeprod : Raccourcir une formule

Bonjour i0raek et le forum,

Pour faire simple, en G6 (feuille "Etat") j'ai ma formule initiale (Sommeprod) que j'aimerais raccourcir d'une part et modifier d'autre part.

Dans le cas présent, j'obtiens 4 alors que je souhaiterais obtenir 3 ---> Voir feuille "Caisse Janv08" colonne B.

En B25 ("Caisse Janv08"), il y a, à la fois, le mot "sandwich" et "boulangerie" d'où compté double avec ma formule et 4 au lieu de 3 au total.

En H6, même problème, le résultat est faussé avec la présence des 2 mots dans le libellé de B25.

En G1, j'ai mis ta formule matricielle et j'obtiens 1, chercher l'erreur 😎

Ci-joint le fichier.

Cibleo

tototiti2008 · 31 Octobre 2008

Re : Sommeprod : Raccourcir une formule

Bonjour à tous,

je n'ai pas vu ton fichier mais peut-être une piste dans celui-ci :

cibleo · 31 Octobre 2008

Re : Sommeprod : Raccourcir une formule

Bonjour tototiti2008,

Je viens d'apprendre quelque chose, je n'avais jamais utiliser BDBNVAL jusqu'à présent.

J'ai l'impresssion qu'il faut que je nomme mais en-têtes de colonnes qui servent de référence.
Vu la structure de mon tableau, je ne sais pas si cela sera pratique ; à explorer quand même.

Merci du tuyau tototiti2008.

Amicalement Cibleo

curteta · 3 Novembre 2008

Re : Sommeprod : Raccourcir une formule

Bonjour

Je vais vous présenter mon pb EXCEL qui doit se résumer à une simplification de formule

J’ai 12 onglets : JANVIER, FEVRIER, …. Jusqu’à DECEMBRE

Dans chacun de ces onglets, je gère l’activité de collaborateurs par typologie (CONTRATS, ETUDES JURIDIQUES, …), par Société refacturée et par temps associés (Nb d’heure)

J’ai un onglet supplémentaire où je fais classiquement des synthèses :

Ainsi avec la formule suivante je connais le Nb de dossiers total traités pour une société donnée

=SOMMEPROD(NB.SI(INDIRECT($B$3:$M$3&"!B4:B1000");$A7))

en ($B$3:$M$3) je fais référence à Janvier, février, ….

B4 :B1000 est la colonne concernée par la saisie de la société concernée

A7 est le nom d’une société donnée

Cette formule marche parfaitement bien

Ainsi avec la formule suivante je connais le Nb d’heures total passés à traiter tous les dossiers pour une société donnée

=SOMMEPROD(SOMME.SI(INDIRECT($B$3:$M$3&"!NomSociete");$A7;INDIRECT($B$3:$M$3&"!Temps")))

Par contre, ca se complique quand je veux faire une analyse par type de dossier :

=SOMMEPROD((Decembre!NomSociete=$A7)*(Decembre!TypeInterv=E$6))+SOMMEPROD((Novembre!NomSociete=$A7)*(Novembre!TypeInterv=E$6))+SOMMEPROD((Octobre!NomSociete=$A7)*(Octobre!TypeInterv=E$6))+SOMMEPROD((Septembre!NomSociete=$A7)*(Septembre!TypeInterv=E$6))+SOMMEPROD((Aout!NomSociete=$A7)*(Aout!TypeInterv=E$6))+SOMMEPROD((Juillet!NomSociete=$A7)*(Juillet!TypeInterv=E$6))+SOMMEPROD((Juin!NomSociete=$A7)*(Juin!TypeInterv=E$6))+SOMMEPROD((Mai!NomSociete=$A7)*(Mai!TypeInterv=E$6))+SOMMEPROD((Avril!NomSociete=$A7)*(Avril!TypeInterv=E$6))+SOMMEPROD((Mars!NomSociete=$A7)*(Mars!TypeInterv=E$6))+SOMMEPROD((Fevrier!NomSociete=$A7)*(Fevrier!TypeInterv=E$6))+SOMMEPROD((Janvier!NomSociete=$A7)*(Janvier!TypeInterv=E$6))

J’ai pas trouver plus simple que cette formule qui ne me satisfait pas du tout intellectuellement

Or, je me dis qu’en associant des SOMMEPROD, INDIRECT, DECALER … et autres fonctions je dois pouvoir en simplifier l’écriture

Qu’en pensez vous ? merci de votre aide

Monique · 3 Novembre 2008

Re : Sommeprod : Raccourcir une formule

Bonjour,

On peut essayer comme ça :
SOMMEPROD(SOMME.SI(INDIRECT(ListMs&"!NomSociete");$A7;DECALER(INDIRECT(ListMs&"!C4:C1000");0;C$6)))

C'est le Equiv qui ne passe pas dans la formule complète.
On peut le calculer en prenant référence sur une feuille mensuelle

Il y a plusieurs essais dans le fichier joint

Sommeprod : Raccourcir une formule

cibleo

XLDnaute Impliqué

i0raek

XLDnaute Occasionnel

cibleo

XLDnaute Impliqué

i0raek

XLDnaute Occasionnel

cibleo

XLDnaute Impliqué

Pièces jointes

tototiti2008

XLDnaute Barbatruc

Pièces jointes

cibleo

XLDnaute Impliqué

curteta

XLDnaute Nouveau

Monique

Nous a quitté

Pièces jointes

Discussions similaires

Sommeprod : Raccourcir une formule

XLDnaute Impliqué

XLDnaute Occasionnel

XLDnaute Impliqué

XLDnaute Occasionnel

XLDnaute Impliqué

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Impliqué

XLDnaute Nouveau

Nous a quitté

Pièces jointes

Discussions similaires

Privacy & Transparency

Privacy & Transparency