Discussion :

[crissud]

Bonsoir


j'ai un petit souci et je n'arrive pas à le résoudre seul.
Je voudrais utiliser l'en-tête de colonne dans ma formule SOMMEPROD
car ma base sur laquelle je veux travailler n'aura pas systématiquement les colonnes aux mêmes coordonnées.

dans mon fichier que je joins,
- j'ai une formule (Orange) qui utilise les coordonnées, elle fonction bien.
- En (vert) la même formule mais en voulant utiliser l'en-tête de colonne

Si quelqu'un peut m'aider

Merci d'avance
Christian

[alsimbad]

salut
en fait ça marche très bien avec l'entete de colonne, mais il y a une condition a sommeprod c'est que toutes les données de la colonne doivent etre des chiffres, et donc c'est l'entete de la colonne qui perturbe
essaye sans
je t'ai aussi simplifié le formule, la parenthese avec a1:g1 = "montant ne sert a rien pour le calcul.
sinon, le mieux est de nommer tes colonnes avec une formule "décaler". ainsi, tu peux garder tes entetes, et la formule étant dynamique, elle n'utilise que la hauteur de colonne réelle

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
en E11:
=SOMMEPROD((Extract!F:F-Extract!E:E<=D11)*(Extract!F:F-Extract!E:E<D12)*(Extract!D:D))

bon dimanche

[crissud]

Bonjour

Merci alsimbad de ta réponse.

Je viens de m'apercevoir que c'est les caractères alphabétiques qui génère une erreur dans ma formule.

l'erreur renvoyé n'est pas à cause de l'entête car cela marche si il y avait pas d'alphabétique dans les données.
L'entête je veux la garder, car ma base à analyser est issue d'un autre programme que j'extrais par copier/coller, donc je n'aurais pas nécessairement les colonnes toujours au même coordonnées sur Excel.

Je ne sais pas si c'est possible dans les critères de SOMMEPROD, exclure une colonne ayant du alphabétique, j'ai essayé avec ESTNUM() mais depuis tout à l'heure je cherche sans trouver.

voici un nouveau fichier retraçant cela.

Faut voir avec decaler, mais c'est aussi une découverte pour moi


Cordialement
Christian de montpellier

[alsimbad]

Je viens de m'apercevoir que c'est les caractères alphabétiques qui génère une erreur dans ma formule.

l'erreur renvoyé n'est pas à cause de l'entête

hé ben si
ce que j'appelle l'entete, ce sont les caracteres de texte qui sont en tete de tes colonne montant, date ect. si tu avais du texte a un endroit quelquonque de ta colonne, cela genererait une erreur aussi.
dans le deuxieme fichier que tu as joint, je ne vois pas que ça marche
si tu ne veux pas enlever cet entete, tu n'as pas d'autre solution que de t'atteler a la formule décaler
si tu sais nommer une plage, c'est le mieux.
je te joins ton fichier avec les formules, tu dis quoi

[crissud]

Re

Merci d'avoir passé un peu de temps et d'avoir essayé, mais ce n'est pas là où je bloque.

Je ne connais pas à l'avance où se trouve mes colonnes, [Paiement] [Date] [Montant], sur ma feuille Extract!.

C'est pour cela que dans ma formule je cherche l'entête Paiement, Date et Montant qui sont les seuls informations que je dispose pour la syntaxe de ma formule.


Cordialement
Christian de montpellier

[alsimbad]

là, je crois que je peux passer la main au premier volontaire. je suppose que tu peux le faire avec un equiv, puis un décaler, mais j'ai pas la competence pour ce genre de formule. j'y reflechirais néamoins.
est ce que la casse et le format sont toujour les mêmes ?

[Pierre Fauconnier]

Salut

Comme le suggère Alsimbad, une solution est de passer par DECALER...

Pour simplifier un peu la formule, je crée d'abord une plage nommée avec DECALER via le gestionnaire de noms... J'appelle cette plage Donnees et en référence, lors de la création du nom, j'utilise ceci

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=DECALER(Feuil1!$A$1;0;0;NBVAL(Feuil1!$A:$A);NBVAL(Feuil1!$1:$1))

Ainsi, la plage s'étend au fur et à mesure des données et des champs qui sont ajoutés/supprimés. Pour cela, je considère que tes données démarrent en A1 avec une ligne d'entête, et qu'aucune ligne n'est vide en A entre la ligne 1 et la fin de ton tableau. Je considère également qu'aucune colonne n'est vide en 1 entre la colonne A et la dernière colonne de ton tableau. En clair, il n'y a sur la feuille QUE tes données (ta feuille sert de table de données)

Si, parmi plusieurs colonnes, tu as effectivement des colonnes dont les intitulés sont Montant, Date et Paiement, retrouver par exemple la somme des montants payés en octobre (date en octobre et PAIEMENT à "Oui"), tu peux utiliser cette charmante formule

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMMEPROD((DECALER(Donnees;1;EQUIV("Paiement";DECALER(Donnees;0;0;1);0)-1;LIGNES(Donnees)-1;1)="Oui")*(MOIS(DECALER(Donnees;1;EQUIV("Date";DECALER(Donnees;0;0;1);0)-1;LIGNES(Donnees)-1;1))=10)*(DECALER(Donnees;1;EQUIV("Montant";DECALER(Donnees;0;0;1);0)-1;LIGNES(Donnees)-1;1)))

Tu peux simplifier la formule en créant trois plages nommées avec DECALER (une pour les montants, une pour les dates, une pour la colonne PAIEMENT), ce qui allègera évidemment la formule ci-dessus. Dans ce cas, tu crées les trois plages nommées Dates, Montants et Paiements sur ce modèle

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
=DECALER(Feuil1!$A$1;1;EQUIV("Date";Feuil1!$1:$1;0)-1;NBVAL(Feuil1!$A:$A)-1;1)
=DECALER(Feuil1!$A$1;1;EQUIV("Montant";Feuil1!$1:$1;0)-1;NBVAL(Feuil1!$A:$A)-1;1)
=DECALER(Feuil1!$A$1;1;EQUIV("Paiement";Feuil1!$1:$1;0)-1;NBVAL(Feuil1!$A:$A)-1;1)

Et la formule avec SOMMEPROD devient simple et lisible

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMMEPROD((Paiements="Oui")*(MOIS(Dates)=10)*Montants)

Si tu dois réaliser plusieurs SOMMEPROD, tu pourrais peut-être t'orienter vers un tableau croisé dynamique

[crissud]

Re

Merci Pierre et alsimbad.

Avec et grâce à votre aide, on arrive au résultat que je souhaitais
je ne serais pas arrivé seul.

A présent quelques soit la position de la colonne, dans mon onglet où se trouve mes données, me renvoie le résultat de ma formule

Je joins le résultat, qui pourrait servir à d'autre

Bon après midi
Cordialement
Christian