Problème de référencement plages avec calcul avec Sommeprod()

anthoYS

XLDnaute Barbatruc
Bonjour,

J'ai un classeur, avec plusieurs onglets et des plages nommées.

Dans un onglet des calculs avec Sommeprod() utilisé notamment ; Puis par incrémentation, un résultat pour chaque type fonction des mois permettant une comparaison durant l'evolution.

En fait, le problème c'est que les valeurs ne se mettent pas à jour de façon automatique.
Lorsque je rentre la ou la formule est inscrite (une des cellule) et que je valide -entrée-, cela me renvoie à selectionner le fichier et à valider par "ok", puis toutes les valeurs se mettent à jour pour chaque cellule. Mais après quand j'aurais modifié des valeurs ailleurs, pour les prendre en compte dans ce tableau synthèse comparatif, il faudra entrer dans l'une des cellules et refaire la même manipulation (selection de fichier, etc.).

Or, je souhaite que tout ceci soit automatique, car le fichier est celui en cours, donc je ne vois pas pourquoi il me demande d'aller chercher ou sont les données (càd selectionner l'emplacement du fichier dans le répertoire)...

Merci par avance voir les fichiers joint qui contiennent des exemples (captures d'écran).
 

Pièces jointes

  • 2.jpg
    2.jpg
    48.4 KB · Affichages: 100
  • 2.jpg
    2.jpg
    48.4 KB · Affichages: 101
  • 2.jpg
    2.jpg
    48.4 KB · Affichages: 98
  • 1jpg.jpg
    1jpg.jpg
    43.5 KB · Affichages: 98
Dernière édition:

anthoYS

XLDnaute Barbatruc
Re : Problème de référencement plages avec calcul avec Sommeprod()

Re,

Je ne peut joindre le fichier car trop lourd et avec des données résultant d'un long travail que je ne souhaite pas dévoiler.

La formule avec les plages référencés nommées.

On retrouve par exemple en AZ3 :
Code:
=SI($AX3="";"";AZ2+SOMMEPROD((MOIS([COLOR=Lime]date[/COLOR])=MOIS($AX3))*(ANNEE([COLOR=Lime]date[/COLOR])=ANNEE($AX3))*([COLOR=Orange]Codif2[/COLOR]=AZ$1)*[COLOR=Yellow]value[/COLOR])-SOMMEPROD((MOIS([COLOR=Magenta]dateetat[/COLOR])=MOIS($AX3))*(ANNEE([COLOR=Magenta]dateetat[/COLOR])=ANNEE($AX3))*[COLOR=Blue]montantCCb[/COLOR]))

date :
Code:
=DECALER(cptesP!$B$2;;;NBVAL([Cpte]cptesP!$A:$A)-1)

value :
Code:
=DECALER(cptesP!$K$2;;;NBVAL(cptesP!$A:$A)-1)

Codif2 :
Code:
=DECALER(cptesP!$N$2;;;NBVAL([Cpte]cptesP!$A:$A)-1)

dateetat :
Code:
=DECALER(cptesP!$DH$3;;;NBVAL(cptesP!$A:$A)-1)

montantCCb
:
Code:
=DECALER(cptesP!$DO$3;;;NBVAL(cptesP!$A:$A)-1)

L'étendue est toujours le classeur pour les plages renommées.
 

Tibo

XLDnaute Barbatruc
Re : Problème de référencement plages avec calcul avec Sommeprod()

Bonjour,

Des images + des formules hors de leur contexte ne sont pas de nature à pouvoir bien comprendre ton problème.

1ère piste :

tes plages nommées dateetat et montantCCb commencent en ligne 3 (ligne 2 pour les autres) et n'ont donc pas le même nombre de lignes.

Pour ma part, sans fichier exemple, je ne chercherais pas davantage.

@+
 

jeanpierre

Nous a quitté
Repose en paix
Re : Problème de référencement plages avec calcul avec Sommeprod()

Bonjour anthoYS, hoerxind,

"et avec des données résultant d'un long travail que je ne souhaite pas dévoiler"


Qui y-a-t'il de si secret dans quelques données chiffrées.. et quelques formules, peut-être... ????????

Bon courage alors.

Jean-Pierre
 

anthoYS

XLDnaute Barbatruc
Re : Problème de référencement plages avec calcul avec Sommeprod()

Re,

Decortiquons la formule suivante :

=SI($AX3="";"";AZ2+SOMMEPROD((MOIS(date)=MOIS($AX3))*(ANNEE(date)=ANNEE($AX3))*(Codif2=AZ$1)*value)-SOMMEPROD((MOIS(dateetat)=MOIS($AX3))*(ANNEE(dateetat)=ANNEE($AX3))*montantCCb))

En AZ2 il y à le montant d'investissement initial. Ensuite on y ajoute les + values du mois de septembre 2008 ici
Code:
SOMMEPROD((MOIS(date)=MOIS($AX3))*(ANNEE(date)=ANNEE($AX3))*(Codif2=AZ$1)*value)
, puis on enlève ce qui est viré vers le compte courant
Code:
-SOMMEPROD((MOIS(dateetat)=MOIS($AX3))*(ANNEE(dateetat)=ANNEE($AX3))*montantCCb))
.
Il reste le capital disponible qui doit être projeté.

Mercis aux intervenants ;
jeanpierre ;
Saches que se ne sont pas les données chiffrées que je souhaite cacher mais d'autres données issue d'un travail personnel qui est contenu dans les autres onglets (une vingtaine au total) (fichier lourd : 4,5 Mo en xslm)...

Voila, j'espère avoir répondu de façon plus concise cette fois.

A+ à ce qui auront envie de m'aider et qui en sont en mesure sans fichier, mais avec les précisions que j'ai livrées jusqu'alors.
 
Dernière édition:

Tibo

XLDnaute Barbatruc
Re : Problème de référencement plages avec calcul avec Sommeprod()

re,

Il n'est pas nécessaire de nous fournir le fichier complet.

Essaye de reproduire sur un fichier simplifié avec quelques lignes tes formules.

Le principe ici est quand même de nous aider à t'aider (car les boules de cristal ne sont plus ce qu'elles ont été)

A te (re)lire

@+
 

anthoYS

XLDnaute Barbatruc
Re : Problème de référencement plages avec calcul avec Sommeprod()

Re,

Encore mieux! :eek:

Je suis parvenu à le réduire en un onglet léger mais pas dans la limite que le forum requiert!
Mais là -soucis supplémentaire-, même en validant ça renvoie "#valeur#", alors qu'avec le fichier original ça les met à jour au moins.

J'espère que ce fichier va vous aidez à m'aider.

Toutefois, je tente encore de voir ou ça pèche aussi de mon côté aussi.

Lien du fichier (ci-joint)
 

Monique

Nous a quitté
Repose en paix
Re : Problème de référencement plages avec calcul avec Sommeprod()

Bonjour,

Les plages nommées de cette manière :
=DECALER(cptesP!$N$2;;;NBVAL([Cpte]cptesP!$A:$A)-1)
tu enlèves le nom du classeur [Cpte]
=DECALER(cptesP!$N$2;;;NBVAL(cptesP!$A:$A)-1)

Les plages non nommées par formule, montantCC, montantCCb, montantCCu, montantCCs et les autres,
tu les nommes par formule pour qu'elles aient la même longueur

A la fin des 2 formules sommeprod,
tu mets ; à la place de * avant la plage à sommer, comme ça :
SOMMEPROD((MOIS(date)=MOIS($AX3))*(ANNEE(date)=ANNEE($AX3))*(Codif2=AZ$1);value)

SOMMEPROD((MOIS(dateetat)=MOIS($AX3))*(ANNEE(dateetat)=ANNEE($AX3));montantCCb)

(Il y a du texte ("") dans la plage "value" et même s'il n'y avait que des valeurs numériques, c'est mieux comme ça, au cas où, justement, il y aurait du texte)

Idem en V62 pour le même motif :
=SOMMEPROD((ANNEE(DATESJEUX)=$U62)*1;PMVALUES)

Je crois qu'il n'y a pas grand-chose d'autre
 

anthoYS

XLDnaute Barbatruc
Re : Problème de référencement plages avec calcul avec Sommeprod()

Bonjour,

Merci Monique.

J'ai revu le fichier avec tes instructions ; Or il apparaît que ça me revoie en AZ3 et pour tout le reste du tableau "#NOM?".
Alors je ne comprend pas car la formule est la suivante :

=SI($AX3="";"";AZ2+SOMMEPROD((MOIS(date)=MOIS($AX3))*(ANNEE(date)=ANNEE($AX3))*(Codif2=AZ$1);value)-SOMMEPROD((MOIS(dateetat)=MOIS($AX3))*(ANNEE(datee tat)=ANNEE($AX3));montantCCb))

en format "Nombre".

Par ailleurs j'ai un autre soucis, en DK15 ; En effet, c'est la plus value de l'année dernière qui est ranvoyé surement car l'année n'est pas référencé dans la formule.

formule :
Code:
=SI(DJ15="";"";SOMMEPROD((MOIS(jour)=MOIS(DJ15))*(ValMois)))
Sommeprod()*Annee() en plus du mois(), non?
Mais comment?

Merci encore ;

J'invite tout ceux qui ont idée de résoudre mon (mes) soucis.

Fichier (cijoint)
 
Dernière édition:

anthoYS

XLDnaute Barbatruc
Re : Problème de référencement plages avec calcul avec Sommeprod()

Re à tous ce qui suivent ce sujet ;

En fait le problème venait d'une erreur subtile commise par Monique :

SOMMEPROD((MOIS(dateetat)=MOIS($AX3))*(ANNEE(datee tat)=ANNEE($AX3));montantCCb)
En effet, faute de frappe qui c'est répercuté lors du copier/coller :
Code:
[FONT=Verdana]SOMMEPROD((MOIS(dateetat)=MOIS($AX3))*(ANNEE(dat[COLOR=Lime]eeta[/COLOR]t)=ANNEE($AX3))[SIZE=3][COLOR=blue][B];[/B][/COLOR][/SIZE]montantCCb)[/FONT]
Voilà, je crois que mon problème est résolu.

Merci beaucoup Monique.


Reste juste à résoudre :

=SI(DJ15="";"";SOMMEPROD((MOIS(jour)=MOIS(DJ15))*(ValMois)))

prenant en compte aussi l'année pour qu'en M de N+1, le résultat ne soit le même.

J'ai tenter cela à la place :
Code:
=SI(DJ3="";"";SOMMEPROD((MOIS(jour)=MOIS(DJ3))[COLOR=Orange]*SOMMEPROD((ANNEE(jour)=ANNEE(DJ3))[/COLOR]*(ValMois))))
, mais le résultat est completement erroné.

rajout dans la formule


lien ci-joint du fichier
 
Dernière édition:

Tibo

XLDnaute Barbatruc
Re : Problème de référencement plages avec calcul avec Sommeprod()

Bonjour,

Souci, en colonne DJ, je ne vois que des #N/A

Donc, tout calcul sur cette colonne avec les fonctions MOIS ou ANNEE ne peut fonctionner.

Vois d'abord ce que cette colonne DJ devrait contenir

@+
 

Discussions similaires

Réponses
4
Affichages
277

Statistiques des forums

Discussions
311 725
Messages
2 081 943
Membres
101 849
dernier inscrit
florentMIG