recherche et somme

[tracor]

salut le phorum

je me bats avec mon excel pour trouver une formule me permettant de rechercher des valeurs présentes dans une feuille pour afficher ensuite la somme des valeurs identiques dans une autre feuille
ex
feuille 1
mais..........
je n'y arrive pas
j'essaie toutes les solutions proposées par Celeda et Monique dans leur fichier SOMME PROD du site sans parvenir à comprendre la syntaxe
y a t il une âme chaleureuse qui pourrait m'aider

pitié

tracor

 

[andré]

Salut tracor,

Je veux bien t'aider, mais ... encore faudrait-il que je comprenne la question.

Peux-tu donner un exemple ?

Ândré.

 

[Jean-Marie]

Bonjour Tracor, André

Je suis comme André, un peu dans le flou avec ton problème

Regarde le fichier ci-joint, dis-nous si l'exemple donné correspond à ta demande.

Les formules sont assez barbares, elles n’ont pas besoin de récupérer les valeurs dans d'autres cellules, donc tu pourrais les utiliser directement si tu adaptes les contantes liées au positionnement des formules dans la feuille (partie en gras dans les formules ci-dessous).

Exemple :
=SI(LIGNES(FREQUENCE(B:E;B:E))-SOMMEPROD((FREQUENCE(B:E;B:E)=0)*1)>=LIGNE()-3;INDEX($B$4:E$98;1+ENT((PETITE.VALEUR(SI(INDEX(FREQUENCE(B:E;B:E);LIGNE($B$1:$B$200);1)>0;LIGNE(A$1:A$200);65536);LIGNE()-3)-1)/COLONNES(B:E));1+MOD((PETITE.VALEUR(SI(INDEX(FREQUENCE(B:E;B:E);LIGNE($B$1:$B$200);1)>0;LIGNE(A$1:A$200);65536);LIGNE()-3)-1);COLONNES(B:E)));"")

=SI(LIGNES(FREQUENCE(B:E;B:E))-SOMMEPROD((FREQUENCE(B:E;B:E)=0)*1)>=LIGNE()-3;PETITE.VALEUR(INDEX($B$4:E$53;1+ENT((LIGNE($A$1:$A$200)-1)/COLONNES(B:E));1+MOD((LIGNE($A$1:$A$200)-1);COLONNES(B:E)))*(INDEX(FREQUENCE(B:E;B:E);LIGNE($A$1:$A$200);0)>0);SOMMEPROD((FREQUENCE(B:E;B:E)=0)*1)+LIGNE()-4);"")
le 4 n'est pas une erreur la matrice de fréquence à toujours une ligne en plus

=SI(LIGNES(FREQUENCE(B:E;B:E))-SOMMEPROD((FREQUENCE(B:E;B:E)=0)*1)>=LIGNE()-3;GRANDE.VALEUR(INDEX($B$4:E$53;1+ENT((LIGNE($A$1:$A$200)-1)/COLONNES(B:E));1+MOD((LIGNE($A$1:$A$200)-1);COLONNES(B:E)))*(INDEX(FREQUENCE(B:E;B:E);LIGNE($A$1:$A$200);0)>0);LIGNE()-3);"")

Si tu as une garde quantité de données, il est préférable d'utiliser une macro VBA, ce sera plus rapide. Ces formules indiquées dans le fichier, ont une limite physique, la plage ne doit pas contenir plus de 65535 cellules.

@+Jean-Marie

 

[andré]

C'est très beau Jean-Marie !

Mais qui te dis que les valeurs sont numériques ?
C'est pourquoi j'attendais un complément d'information.

Trop souvent il y a confusion entre somme et nombre, si ce n'est encore la somme des valeurs numériques de la colonne à côté !

Attendons, et espérons que tes efforts ne seront pas inutiles.

Waaaauf, ... c'est déjà l'heure de l'apéro, je l'avais presque loupé !

Ândré.

 

[tracor]

salut André et Jean Marie ainsi que tous le phorum

je vous joins un fichier afin de mieux faire comprendre mon problème

merci d'avance pour vos réponses

tracor

 

[andré]

Salut tracor,

C'est bien ce dont je me doutais, et j'ai bien fais d'attendre quelque peu avant de te répondre (lol).

Essaie en D1 la formule : =SOMMEPROD((Comptage!$A$2:$A$8=A2)*Comptage!$C$2:$C$8)
Tu peux copier celle-ci vers le bas.

Ândré.

 

[tracor]

re, Ândré

pourrez tu m'expliquer comment fonctionne cette fonction
parce que malgré les explications de Monique et Celeda dans leur fichier
sommeprod
je n'arrive pas à comprendre la syntaxe

en tout cas merci pour la formule

tracor

 

[tracor]

re re Ândré


une autre petite question
si je veux utitiser cette fontion par macro
et que je souhaite que la colonne selectionnée soit de la première cellule jusqu'à la première cellule vide
comment puis je transcrire la fonction

 

[Jean-Marie]

Re...

André sur ce coup tu as entièrement raison, j'avais un petit litige avec mon banquier de lors de 10000 €uros, alors il en a eu pour son argent. lol
Continue à boire ta duvel et ton apéro, tu es en pleine forme.

Tracor tu es pas prêt d'avoir la formule magique 10+26+7 donne 43 pas 44.
Entre Formules et Macros, pour obtenir le même résultat de la feuille Histo, tu as les TCD de Celeda.

@+Jean-Marie

 

[tracor]

salut Jean marie

je ne comprends pas bien ce que tu veux dire concernat la formule magique
en effet 43 correspond à la quantité comptée physiquement
44 correspond à la quantité théorique

je jette un oeil sur les TDC de Celeda

en tout cas merci

tracor

 

[Jean-Marie]

Re...

Tracor, si tu fais un enregistrer de macro, tu vas obtenir

ActiveCell.FormulaR1C1 = _
"=SUMPRODUCT((Comptage!R2C1:R8C1=R[-12]C[-5])*Comptage!R2C3:R8C3)"

@+Jean-Marie

 

[tracor]

re à tout le monde

le problème c'est que la macro ne met pas à jour la formule
en effet dans la feuille comptage une ou plusieurs lignes sont ajoutées à chaque lancement d'une macro précise
j'aimerai que les lignes ajoutées s'ajoutent également dans la formule sumproduct
comment puis je faire
merci de vos réponses ou suggestions

tracor

 

[andré]

Salut,

Tentative d'essai d'explication !

La fonction SOMMEPROD ne fait pas partie des fonctions dites "normales".
Elle a une syntaxe qui lui est propre.

Le ; de la syntaxe normale est à remplacer par un *
Un + est égal à ET, et un - à SAUF.

Dans la fcormule :
=SOMMEPROD((Comptage!$A$2:$A$8=A2)*Comptage!$C$2:$C$8)
le premier argument recherche (ou sélectionne ) les cellules A2:A8 de la feuille Comptage colonne A qui répondent à la condition =A2 de la feuille dans laquelle la fonction est placée (Histo), le deuxième argument somme les valeurs de la colonne C des lignes sélectionnées de la feuille. Comptage

Deux règles à respecter :
Cette fonction supporte mal des plages de colonnes entières, donc jamais A:A ou C:C, mais bien A2:A8 et C2:C8
Il faut que les plages aient la même taille, donc A2:A8 et A1:A10 renverra un massage d'erreur.

Pour des explications complémentaires : consulter Monique (lol).

Ândré.

 

[tracor]

merci ândré

cette explication est limpide

pour la question que j'ai posé concernant l'ajout d'une ligne dans la formule automatiquement par macro
j'ai une solution en nommant la colonne
A2:a65000
et
c2:c65000
ainsi la formule donne
=SOMMEPROD((article=A2)*comptage)

ainsi je suis sur que la formule prend en compte tout ajout de lignes
puisque toutes les lignes sont selectionnées dés le début

si quelqu'un a une autre solution "plus propre"
je suis preneur

 

[andré]

Resalut,

Plus "propre" non, ta formule est bonne.

Plus compliqué oui :
=SOMMEPROD((Comptage!$A$2:INDIRECT("Comptage!$A$"&NB(Comptage!C:C)+1)=A2)*Comptage!$C$2:INDIRECT("Comptage!$C$"&NB(Comptage!C:C)+1))

Les deux "+1" du fait que la plage commence en ligne 2.
Mais tout ceci uniquement pour te montrer que ta solution est la bonne.

Ândré.