XL 2016 SOMME.SI.ENS + Application d'une formule à une plage

[AlexMancho]

Bonjour à tous,

Dans une base de donnée contenant des dates et des montants, je cherche à cumuler les dates par mois & année.

Situation actuelle : Ce qui fonctionne (je ne suis donc pas bloqué):
Ma base de donnée contient les champs "tva" ainsi que "date facture" déclinée en deux colonnes "mois" & "année"
Mon tableau de synthèse contient une date et j'additionne les valeurs "TVA" des lignes pour lesquelles "mois" et "année" correspondent à ma date de référence
formule :
=SOMME.SI.ENS(Tableau1[TVA];Tableau1[mois];MOIS(C14);Tableau1[année];ANNEE(C14);Tableau1[catégorie];"RECETTE*")
cela fonctionne mais suppose d'avoir les colonnes "mois" et "année" dans le tableau source.

Amélioration souhaitée : dans un soucis d'optimisation je cherche à supprimer les colonnes "mois" et "année" de ma base de donnée en intégrant directement leur calcul dans le tableau de synthèse.
formule testée :
=SOMME.SI.ENS(Tableau1[TVA];MOIS(Tableau1[date facture]);MOIS(C14);ANNEE(Tableau1[date facture]);ANNEE(C14);Tableau1[catégorie];"RECETTE*")
Dans l'éditeur de formule chaque champ est bien pris en compte, par exemple MOIS(Tableau1[datefacture]) renvoi bien les mêmes valeurs que renvoyait Tableau1[mois]
Cependant excel me rapporte une erreur au moment de valider la formule (l'erreur basique "Êtes-vous en train de taper une formule?")

J'aimerai donc votre avis sur cette dernière, est-ce que j'ai une faute basique sous les yeux que je ne verrai pas ? ou le problème serait il qu'on ne peut pas appliquer la formule MOIS ou ANNEE dans un champ qui demande une plage (mais il est étrange que dans l'éditeur de formule ce soit bien pris en compte) ?

Bonne journée

PS : un TCD permet de réaliser cela plus facilement avec un groupement par mois et année mais je ne souhaite pas ici passer par cet outil.
(Office pro plus 2016 sur Windows 10)

 

[ALS35]

Bonjour,

Si tu supprimes les colonnes Mois et Année tu dois passer en formule matricielle SOMMEPROD pour extraire ces informations, avec en plus le caractère * non reconnu

=SOMMEPROD((Tableau1[TVA])*(MOIS(Tableau1[date facture])=MOIS(C14))*(ANNEE(Tableau1[date facture])=ANNEE(C14))*(GAUCHE(Tableau1[catégorie];7)="RECETTE"))

Cordialement

 

[Hasco]

Bonjour,

Avec somme.si.Ens ce pourrait être :

=SOMME.SI.ENS(Tableau1[TVA];Tableau1[date facture];">" & fin.MOIS(C14;-1);Tableau1[date facture];"<=" & FIN.MOIS(C14;0);Tableau1[catégorie];"RECETTE*")

Sur un ficher personnel, cette formule fonctionne pour avoir le total des montants du mois en cours :

=SOMME.SI.ENS(Journal[Montant];Journal[Date];">" & FIN.MOIS(AUJOURDHUI();-1);Journal[Date];"<=" & FIN.MOIS(AUJOURDHUI();0))

Question : C14 ou $C$14 ?

Mais difficile de vérifier sans données.
Joignez un fichier exemple synthétique et expurgé de toute donnée confidentielle.

Cordialement

 

[AlexMancho]

Merci ALS35,
Bien vu, ça fonctionne.

(Tableau1[TVA]) => renvoi la valeur
(MOIS(Tableau1[date facture])=MOIS(C14)) => renvoi 1 si correspondance du mois (ou 0 sinon)
(ANNEE(Tableau1[date facture])=ANNEE(C14)) => renvoi 1 si correspondance de l'année (ou 0 sinon)
(GAUCHE(Tableau1[catégorie];7)="RECETTE") => renvoi 1 si le texte correspond (ou 0 sinon)
et SOMMEPROD fait la somme de chacun des produits.

Cependant, pourquoi SI(Tableau1[catégorie]="RECETTE*";1;0) ou Tableau1[catégorie]="RECETTE*" ne fonctionnent pas en lieu et place de GAUCHE(Tableau1[catégorie];7)="RECETTE" dans cette formule ?

PS : merci Hasco d'avoir pris le temps d'y regarder. Je me contente de la solution précédente qui correspond de manière simple au besoin.

 

[ALS35]

Re,

Parce que le caractère générique * n'est reconnu par la fonction SOMMEPROD et donc il faut prendre les 7 premiers caractères de ton texte(Recette) pour avoir l'équivalent. Par contre ce caractère * est reconnu dans la fonction SOMME.SI.ENS, et la formule de Hasco, salutations, est également adaptée à ton cas.

Cordialement

 

[Hasco]

Re,

Oui la fonction somme.si.ens prend en compte les caratères génériques. De plus elle a l'avantage d'avoir été optimisée pour les grands tableaux, elle est réputée être plus véloce que sommeprod.

Cordialement

 

[AlexMancho]

Merci pour vos réponses et explications.