XL 2013 [RESOLU] OU dans un SOMME.SI.ENS

miloo

XLDnaute Junior
Bonjour,

Je cherche à faire un OU dans un SOMME.SI.ENS.
En recherchant sur le web, j'ai vu que la fonction somme.si.ens ne le permettait pas directement. J'ai aussi vu des solutions pour détourner avec sommeprod mais je n'arrive pas à l'appliquer sur mon cas.
J'ai joint un descriptif exact de mon besoin dans mon fichier joint, ce sera plus clair qu'avec des mots je pense (j'espère!).
J'ajoute que l'ajout de colonnes est possible pour calculs intermédiaires mais si possible, pas d'ajout de ligne (j'ai réussi à le faire en ajoutant une ligne).

PS, je voulais ajouter une image de mon fichier dans cette discussion pour que vous n'ayez pas à l'ouvrir pour comprendre, mais je n'y arrive pas via l'éditeur... (image stockée en local)

Merci!
 

Pièces jointes

  • essai HC 2.xlsx
    12.9 KB · Affichages: 39

miloo

XLDnaute Junior
Merci à vous 2.

Chris24, malheureusement, il semble que ta formule ne fonctionne pas. L'as-tu testé dans mon fichier? trouves-tu 8:00? ce n'est pas mon cas (56:00). J'ai aussi du mal à comprendre ta formule, du coup je ne sais pas la corriger ou l'adapter. Je vais continuer à essayer de la comprendre.

chris, merci, cela fonctionne. J'avais vu aussi des solutions matricielles sur internet (sans malheureusement les comprendre ni réussir à les appliquer), mais je voulais les éviter car je ne connais pas bien ce fonctionnement. Peux-tu m'éclairer: la validation par "CTRL shift entrée" se fait une fois pour toute, il n'y a pas de "reset" sous certaines conditions de l'utilisation du fichier? Car c'est seulement une petite partie de mon fichier sur lequel il y a des interventions de macros, etc tout au long de son utilisation et j'ai peur qu'il ne faille relancer cette opération "manuelle" "CTRL shift entrée" qu'un utilisateur lamba ne pourra pas faire.
 

miloo

XLDnaute Junior
merci encore à tous.

chris, il ne devrait pas y avoir à toucher à cette formule pendant l'utilisation du fichier (cette cellule sera verrouillée), donc le matriciel devrait aller. Merci encore!

eric, ta formule fonctionne aussi, merci, et sans le "risque" du matriciel (même si a priori comme expliqué ci-dessous ça devrait le faire).
Par contre je ne comprends pas la formule, je vais m'y pencher (le je dois partir, j'y regarderai plus tard). Enfin, si tu peux me l'expliquer sans que j'aie à trop chercher, ce serait nickel! (je connais les différentes formules individuellement mais je ne comprends pas leur combinaison).

Merci encore à tous.

Je passerai en résolu quand j'aurai compris, pour être sûr que cela fonctionne dans tous les cas (en tous cas je viens de faire plusieurs tests, cela semble fonctionner dans tous les cas).
 

eriiic

XLDnaute Barbatruc
Pour comprendre une formule il faut l'évaluer pas à pas.
Tu as 'Formules / Audit de formules / Evaluation de formule' pour ça.
Rapidement illisible si les plages sont grandes (pas le cas ici)
Dans ce cas réduire les plages, ou bien évaluer partiellement la formule.

Par exemple dans =SOMMEPROD((B5:H5)*(((GAUCHE(B2:H2;1)="C")+(GAUCHE(B3:H3;1)="C"))>0)) sélectionne :
=(GAUCHE(B2:H2;1)="C") et fait F9, tu obtiens {FAUX\FAUX\FAUX\VRAI\FAUX\VRAI\FAUX}
Fait pareil sur :
(GAUCHE(B3:H3;1)="C"), tu obtiens {FAUX\FAUX\FAUX\VRAI\VRAI\FAUX\FAUX}
Tu vois que tu as les positions des C des lignes 2 et 3, plus qu'à faire un OU mais avec le + (algèbre de Boole), les opérateurs ET, OU ayant leur limite dans excel...
Maintenant F9 sur :
{FAUX\FAUX\FAUX\VRAI\FAUX\VRAI\FAUX}+{FAUX\FAUX\FAUX\VRAI\VRAI\FAUX\FAUX}, tu obtiens {0\0\0\2\1\1\0}
au passage tu vois l'explication de ton 11:00 = 2*3:00+5:00+0:00
d'où le test nécessaire ({0\0\0\2\1\1\0})>0 qui donne {FAUX\FAUX\FAUX\VRAI\VRAI\VRAI\FAUX} que tu n'as plus qu'à multiplier par les temps de B5:H5.

Quand tu fais des évaluations avec F9 il faut quitter par Echap. Sinon tu valides l'évaluation et tu perds le bout de formule (Ctrl+Z dans ce cas)
eric
 

miloo

XLDnaute Junior
Merci beaucoup Eric pour t'être donné la peine de m'expliquer. Je ne connaissais pas la fonction "Evaluation de formule", c'est vrai que cela aide bien. Je n'avais également pas saisi le + dans le sens booléen, n'ayant pas fait attention qu'il ne s'agissait pas d'une addition, mais de conditions (je ne suis pas habitué ce type de formules et des tests, mais plus à du calcul "pur").

Je viens d'essayer la formule sur mon fichier complet (le fichier joint n'est qu'un extrait), et j'ai omis de préciser que dans certains cas, dans la ligne a additionner, il y a du texte (qu'il faut donc "sauter"). Cela semble perturber la formule, voir nouveau fichier joint. Est-ce possible de les omettre en adaptant la formule? ou en adaptant le format de cellule?
Sinon, ce n'est pas grave, j'ai trouvé une solution en ajoutant des colonnes intermédiaires en faisant des sous totaux (car ces cellules avec du texte interviennent à intervalles réguliers), mais si je peux éviter, c'est encore mieux! En regardant la formule matricielle de chris je me dis qu'il y apeut être une solution avec la formule ESTNUM, mais je n'arrive pas à l'implémenter.
Sinon la formule matricielle de chris semble fonctionner même avec ce texte, sauf que dans mon fichier réel, je veux placer la formule dans une cellule fusionnée, ce qui n'est, semble-t-il, pas possible...
 

Pièces jointes

  • essai HC 3.xlsx
    11.6 KB · Affichages: 27
Dernière édition:

job75

XLDnaute Barbatruc
Bonjour miloo, Chris24, chris, eriiiic, le forum,

Formule en I5 du fichier joint :
Code:
=SOMMEPROD(SIGNE((GAUCHE(B2:H2)="C")+(GAUCHE(B3:H3)="C"));B5:H5)
Le point-virgule fait que les textes dans B5:H5 sont ignorés.

Edit : SIGNE() prend moins d'octets en mémoire que ()>0 (3 au lieu de 5).

Et I5 est fusionnée puisque vous en parlez.

Bonne journée.
 

Pièces jointes

  • essai HC 3(1).xlsx
    17.5 KB · Affichages: 36
Dernière édition:

miloo

XLDnaute Junior
Bonjour job75, cela fonctionne, c'est parfait!
Merci à tous pour vos contributions!

Cependant, il y a un "détail" que je n'avais pas vu (désolé du coup pour tous ces allers-retours). La formule prend en compte toutes les cellules commençant par "C". Je viens de réaliser qu'il peut y avoir des cellules "CA" dont il ne faut pas tenir compte (normalement, avec CA, il ne devrait pas y avoir d'heure sup dans la ligne 5, mais je préfère pallier toute éventualité). Donc il faudrait tenir compte seulement de C2, C4 et C7. Est-ce possible, et comment? Ou plus génériquement de toute cellule avec C suivi d'un chiffre, ça serait le top!
Sinon tant pis, ce cas ne devant normalement pas se produire.
 

miloo

XLDnaute Junior
job75, impeccable. Maintenant cela fonctionnera dans tous les cas. Non, il n'y aura pas plus de 2 caractères. Je passe en résolu.
Merci à tous: Chris24, chris, eriiiic et job75 ainsi qu'à toute la communauté Excel-downloads!
Bonne fin de week end à ceux qui ne travaillent pas aujourd'hui!
 

Discussions similaires

Statistiques des forums

Discussions
311 740
Messages
2 082 047
Membres
101 880
dernier inscrit
Anton_2024