Problème combinaison fonctions SOMMEPROD et OU
Kayna.86
Messages postés
42
Statut
Membre
-
eriiic Messages postés 25847 Date d'inscription Statut Contributeur Dernière intervention -
eriiic Messages postés 25847 Date d'inscription Statut Contributeur Dernière intervention -
Bonjour à tous,
Une fois de plus, j'ai besoin de votre aide précieuse.
J'ai élaboré une formule combinant les fontions "sommeprod" et "ou" (matérialisé par le signe "+").
J'ai attribué des noms de champs aux colonnes de ma base de données correspondant à différents critères de sélection dans ma formule.
La formule est question est sensée calculer le montant des loyers pour les locations:
- dont la date de début de contrat est anétrieure OU égale à une année x OU inconnue (cellules vides),
- dont la date de fin de contrat est postérieure à cette année x OU inconnue.
- dont le champs nommé NTC est vide.
Ce qui donne:
=+SUMPRODUCT(--($A34=Site_database);--((StartContract<=B$4)+(StartContract=""));--((EndContract>B$4)+(EndContract=""));--(NTC="");Rent)
La formule me semble correcte sur le papier et fonctionne, sauf que les résultats affichés ne sont pas bons, ils sont nettement supérieurs aux résultats attendus. J'ai bien peur que la syntaxe ne soit pas assez rigoureuse, et que certaines lignes sont comptées plusieurs fois.
Quelqu'un peut-il m'aider?
Merci d'avance
KAYNA
Une fois de plus, j'ai besoin de votre aide précieuse.
J'ai élaboré une formule combinant les fontions "sommeprod" et "ou" (matérialisé par le signe "+").
J'ai attribué des noms de champs aux colonnes de ma base de données correspondant à différents critères de sélection dans ma formule.
La formule est question est sensée calculer le montant des loyers pour les locations:
- dont la date de début de contrat est anétrieure OU égale à une année x OU inconnue (cellules vides),
- dont la date de fin de contrat est postérieure à cette année x OU inconnue.
- dont le champs nommé NTC est vide.
Ce qui donne:
=+SUMPRODUCT(--($A34=Site_database);--((StartContract<=B$4)+(StartContract=""));--((EndContract>B$4)+(EndContract=""));--(NTC="");Rent)
La formule me semble correcte sur le papier et fonctionne, sauf que les résultats affichés ne sont pas bons, ils sont nettement supérieurs aux résultats attendus. J'ai bien peur que la syntaxe ne soit pas assez rigoureuse, et que certaines lignes sont comptées plusieurs fois.
Quelqu'un peut-il m'aider?
Merci d'avance
KAYNA
A voir également:
- Problème combinaison fonctions SOMMEPROD et OU
- Fonction si et - Guide
- Combien de combinaison possible avec 3 chiffres ✓ - Forum Programmation
- Combinaisons possibles avec 3 séries de 3 nombres - Forum Réseaux sociaux
- Combinaisons à 3 CHIFFRES - Forum Programmation
- Tableau de combinaison loto 5/90 - Forum Logiciels
4 réponses
Bonjour,
Déjà j'aurais écrit la formule comme ça, mais ça ne changera rien à mon avis.
=SUMPRODUCT(($A34=Site_database)*((StartContract<=B$4)+(StartContract=""))*((EndContract>B$4)+(EndContract=""))*(NTC="")*Rent)
Sans un extrait de ton fichier difficile de répondre.
Travaille en réduisant aux 5 premières lignes, tu y verras plus clair.
Sinon déposer le fichier *.xls (réduit au nécessaire et anonymisé) sur cjoint.com et coller ici le lien fourni.
eric
Déjà j'aurais écrit la formule comme ça, mais ça ne changera rien à mon avis.
=SUMPRODUCT(($A34=Site_database)*((StartContract<=B$4)+(StartContract=""))*((EndContract>B$4)+(EndContract=""))*(NTC="")*Rent)
Sans un extrait de ton fichier difficile de répondre.
Travaille en réduisant aux 5 premières lignes, tu y verras plus clair.
Sinon déposer le fichier *.xls (réduit au nécessaire et anonymisé) sur cjoint.com et coller ici le lien fourni.
eric
Bonjour,
Désolée de ma réponse tardive. Voici un extrait de mon fichier dont j'ai changé les données, biensur, et réduit.
https://www.cjoint.com/c/CAyrjxm8JWM
Merci de votre aide.
Cordialement,
Kayna
Désolée de ma réponse tardive. Voici un extrait de mon fichier dont j'ai changé les données, biensur, et réduit.
https://www.cjoint.com/c/CAyrjxm8JWM
Merci de votre aide.
Cordialement,
Kayna
Bonjour
c'est la syntaxe du OU qui est fausse
comme tu manipules des booléens avec des chiffre un test bon renvoie 1 sinon 0
d'où tes "résultats bien supérieurs"
il faut écrire: (A+B=1) qui est le OU exclusif (Xor)
Michel
c'est la syntaxe du OU qui est fausse
comme tu manipules des booléens avec des chiffre un test bon renvoie 1 sinon 0
A=0+B=0--->0 A=0+B=1 --->1 A=1+ B=0--->1 A=1 + B=1--->2
d'où tes "résultats bien supérieurs"
il faut écrire: (A+B=1) qui est le OU exclusif (Xor)
Michel
Bonjour Michel,
Merci de ta réponse.
Je pense avoir compris tes explications. En revanche, j'ai beaucoup de mal à l'appliquer à mon cas:
Si l'on reprends ma formule, en fait, au lieu d'avoir :
;--((StartContract<=B$4)+(StartContract=""));--
Je devrais avoir quelque chose qui ressemble à:
;--((StartContract=((<=B$4)+(""))
Ma solution ne fonctionne pas (problème de sytaxe surement). Et puis, je ne sais pas comment intégrer le signe "<=" dans la formule.
Pourriez-vous m'aider, s'il vous plait?
Merci d'avance
Flora
Merci de ta réponse.
Je pense avoir compris tes explications. En revanche, j'ai beaucoup de mal à l'appliquer à mon cas:
Si l'on reprends ma formule, en fait, au lieu d'avoir :
;--((StartContract<=B$4)+(StartContract=""));--
Je devrais avoir quelque chose qui ressemble à:
;--((StartContract=((<=B$4)+(""))
Ma solution ne fonctionne pas (problème de sytaxe surement). Et puis, je ne sais pas comment intégrer le signe "<=" dans la formule.
Pourriez-vous m'aider, s'il vous plait?
Merci d'avance
Flora
Bonjour Eriic,
J'ai fait le test et malheureusement, la formule de ne fonctionne pas. J'obtiens les mêmes résultats;
Voici le fichier auquel j'ai appliqué les ajustements proposés:
https://www.cjoint.com/c/CAzkR6CcV2g
Cdt,
Kayna.
J'ai fait le test et malheureusement, la formule de ne fonctionne pas. J'obtiens les mêmes résultats;
Voici le fichier auquel j'ai appliqué les ajustements proposés:
https://www.cjoint.com/c/CAzkR6CcV2g
Cdt,
Kayna.
Bonjour,
Tu ne peux pas utiliser sommeprod() sur des plages de colonnes entières.
D'une part parce qu'excel n'aime pas, et surtout parce que c'est une fonction matricielle extrèment gourmande en ressources.
Si tu mets 1000 lignes par 80 formules tu vas avoir des lenteurs insupportables.
Il faut limiter les lignes au strict nécessaire quelques dizaines, centaines maxi.
Si tu rédéfinis tes plages sur les lignes 2 à 50 tu vois que ça marche.
Le mieux est d"utiliser un nom dynamique.
Compter le nombre de lignes sur une colonne entièrement remplie, par exemple B :
=NBVAL(bdd!$B:$B)-1 => 29
Et pour chaque nom utiliser décaler() :
NTC: =DECALER(bdd!$D$2;;;NBVAL(bdd!$B:$B)-1;)
https://www.cjoint.com/c/CAzpLmuoHyh
eric
Tu ne peux pas utiliser sommeprod() sur des plages de colonnes entières.
D'une part parce qu'excel n'aime pas, et surtout parce que c'est une fonction matricielle extrèment gourmande en ressources.
Si tu mets 1000 lignes par 80 formules tu vas avoir des lenteurs insupportables.
Il faut limiter les lignes au strict nécessaire quelques dizaines, centaines maxi.
Si tu rédéfinis tes plages sur les lignes 2 à 50 tu vois que ça marche.
Le mieux est d"utiliser un nom dynamique.
Compter le nombre de lignes sur une colonne entièrement remplie, par exemple B :
=NBVAL(bdd!$B:$B)-1 => 29
Et pour chaque nom utiliser décaler() :
NTC: =DECALER(bdd!$D$2;;;NBVAL(bdd!$B:$B)-1;)
https://www.cjoint.com/c/CAzpLmuoHyh
eric
