Sommeprod : Raccourcir une formule

[cibleo]

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]

Re : Sommeprod : Raccourcir une formule

Bonjour Forum, cibleo !

Moi je verrais bien une petite formule matricielle du genre :

=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]

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)))

[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]

Re : Sommeprod : Raccourcir une formule

Bonjour à tous, cibleo !

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

@++

 

[cibleo]

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]

Re : Sommeprod : Raccourcir une formule

Bonjour à tous,

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

 

[cibleo]

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]

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]

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