Calcul avec SOUSTOTAL, et sommeprod ?

[l.dolmaire]

Bonjour à tous, je sais que cette question à été vu plusieurs fois concernant la combinaison de SOUSTOTAL et de SOMMEPROD mais je remarque que personne n'a exactement la répons à mon problème et comme je suis pas un expert d'excel...

Je m'explique :

je dois analyser les chiffres de produits finis mois par mois, il me faudrait un calcul permettant de calculer la quantité de mouvement de stocks de grande et petite palettes dans l'entreprise ( P = petite , G = grande ).
Je dois calculer cela pour une entités à chaque fois ( en D1) exemple : pour les CARDOC, CARPRO,CARIC,...

je ne demande pas une macro mais un calcul qui fait intervenir obligatoirement SOUSTOTAL ( puisque je dois filtrer pour chaque entités en D1) et SOMMEPROD .

Pour résumé j'aimerai un calcul qui me détermine le nombre de P et G pour chaque entités ( en tenant compte des filtres donc)

par exemple pour l'entité CARPRO j'aimerai avoir un résultat du genre 200 P et 83 G. Ce qui si signifie 200 petites palettes et 83 grandes palettes pour CARPRO sur ce mois.

Chaque ligne vaut 1, je veux juste calculer le nombre de P et de G que j'ai déja effectué en ajoutant une fonction recherche et concatainer.

Le mieux serait de proposé une solution directement adaptable au fichier puisque ce dernier est assez compliqué et volumineux je vous le joins donc :

http://cjoint.com/?3DklXQykvSk

Le fichier contient 2 feuilles, il ne faut travailler que sur la feuille "analyse", la feuille "base" sert uniquement à savoir si c'est une grande ou petite palette en fonction des références.

Merci d'avance
PS : n'hésitez pas à me demander plus de renseignement

 

[l.dolmaire]

Re : Calcul avec SOUSTOTAL, et sommeprod ?

Un ptit UP

 

[hoerwind]

Re : Calcul avec SOUSTOTAL, et sommeprod ?

Bonjour et bienvenu sur le forum,

Peux-tu joindre un fichier compressé ZIP (au lieu de RAR) ?

 

[l.dolmaire]

Re : Calcul avec SOUSTOTAL, et sommeprod ?

Bonjour hoerwind et merci.
voici le fichier compressé sous la forme ZIP : http://cjoint.com/?3DkoY3Rpk19

 

[Misange]

Re : Calcul avec SOUSTOTAL, et sommeprod ?

Bonjour
comme on le dit souvent, on n'a nullement besoin d'un fichier de plus de 5MO pour mettre au point une formule excel !
mieux vaut un petit extrait du fichier récapitulant exactement ce qui est souhaité, et avec juste ce qu'il faut de lignes pour que les différents cas de figure soient représentés.
Ici on a un truc très lourd mais pas d'exemple de ce qui est attendu comme résultat en sortie !

en l’occurrence un simple tcd avec en colonne la cause, en lignes estna (pourquoi c'est estna qui contient le résultat alors que la colonne p ou G ne contient que des estna ... la logique m'échappe) et en valeurs nombre de estna et en 3 clics c'est fait sans formule compliquée.

Vu le poids du fichier je ne joins pas d'exemple. PAr ailleurs cijoint ne garde plus les messages donc tout l'intérêt de la question est perdu au bout de quelque temps pour ceux qui se poseraient un pb similaire . Dommage

 

[l.dolmaire]

Re : Calcul avec SOUSTOTAL, et sommeprod ?

Bonjour Misange, je vais veillé à héberger le fichier plus longtemps, la colonne estna comme tu dis est là pour la seule raison que dans la colonne P ou G , comme tu le vois j'ai des #N/A et je n'arrive pas à supprimer cette erreur c'est pour ela que j'ai rajouté une colonne.

Si tu peux m'aider pour ne pas rajouter la colonne estna, alors je prend .
Je vais retrécir mon fichier et je le réheberge

 

[l.dolmaire]

Re : Calcul avec SOUSTOTAL, et sommeprod ?

Voici le fichier fortement allegé 500 ko, j'ai réussi a supprimer estna et j'ai ajouté un petit texte afin que vous puissiez mieux comprendre le problème.

Voici : http://cjoint.com/?BDkpJBcSSf5

 

[Misange]

Re : Calcul avec SOUSTOTAL, et sommeprod ?

Essaie d'abord de faire un TCD toi même , c'est un super tableau pour apprendre à faire cela et ça t'ouvrira des perspectives géantes.
pour ne pas avoir deux colonnes :
si(estna(ta superformule);la formule de la colonne estna;tasuper formule)

 

[l.dolmaire]

Re : Calcul avec SOUSTOTAL, et sommeprod ?

Merci mais mon objectif n'est pas de faire un TCD, en effet, je débute avec excel et j'aimerai qu'on m'aide uniquemeent sur la formule, cela me permettrait par la suite d'analyser 2 années de travail ans mon entreprise et de faire des macros automatiques pour répertorié ces chiffres et les mettres sous forme graphique

 

[Misange]

Re : Calcul avec SOUSTOTAL, et sommeprod ?

Il y a un truc bizarre dans ton classeur qui est anormalement lourd.
J'ai copié tes valeurs (sans la 2° feuille qui ne sert à rien ici) dans un nouveau classeur et il fait ... 44k
regarde comment fonctionne le TCD, tu peux mettre autant de conditions que tu veux,
tu peux aussi mettre un champ en champ de page ce qui te permet de filtrer comme tu veux.
pas une seule formule sur la feuille = risque d'erreur minimisé !

 

[david84]

Re : Calcul avec SOUSTOTAL, et sommeprod ?

Bonjour tout le monde,
une proposition par formules puisque tu sembles y tenir (mais la solution par TCD est la meilleure).
A+

 

[l.dolmaire]

Re : Calcul avec SOUSTOTAL, et sommeprod ?

Bonjour david , merci pour ta contribution mais il me faut une formule qui compte le nombre de palette différente, ( tout en tenant compte des filtres)

A droite du tableau, il y a une colonne P ou G. En réponse tu as mis un chiffre pour chaque entités, hors il me faudrait 2 chiffres par entités:

Exemple : SCAORD 10 G et 2 P

EDIT : LE TCD est intéressant, je vais creuser le sujet mais en attendant il me faudrait la formule

 

[Misange]

Re : Calcul avec SOUSTOTAL, et sommeprod ?

Il te suffit de 3 clics mais tu penses aller plus vite en évitant de lire simplement une petite page. Tu fais comme tu veux mais c'est un peu désespérant parfois.

 

[JBOBO]

Re : Calcul avec SOUSTOTAL, et sommeprod ?

Bonjour à tous,

Sur la base de la formule de David84, en I28 :

=SI(F28<>"";SOMMEPROD((D$2:D$25=F28)*(SOUS.TOTAL(3;INDIRECT(ADRESSE(LIGNE(D$2:D$25);COLONNE(D$2:D$25)))))*($S$2:$S$25="G"));"")&" G et "&SI(F28<>"";SOMMEPROD((D$2:D$25=F28)*(SOUS.TOTAL(3;INDIRECT(ADRESSE(LIGNE(D$2:D$25);COLONNE(D$2:D$25)))))*($S$2:$S$25="P"));"")&" P"

 

[david84]

Re : Calcul avec SOUSTOTAL, et sommeprod ?

Re

...mais en attendant il me faudrait la formule

Le but de ce forum n'est pas de te donner "une" formule mais de t'aider à progresser dans la compréhension des outils qu'Excel met à notre disposition. Le TCD est parfaitement adapté à ta problématique, plus sûr, plus souple et bien plus rapide que des formules matricielles.

Il te suffit de 3 clics mais tu penses aller plus vite en évitant de lire simplement une petite page. Tu fais comme tu veux mais c'est un peu désespérant parfois.

D'accord avec Misange...
Qu'est-ce qui ne te convient pas dans le TCD ?
Si tu as lu le lien proposé par Misange et que la solution ne te séduit pas, ce serait bien que tu nous dises pourquoi.
Je te conseille d'utiliser les possibilités "natives" offertes par la version 2007 (table plutôt que plages, possibilité d'insérer une ligne de sous-totaux dans laquelle tu peux même inclure tes propres formules, utilisation des filtres et tris, insertion de graphiques voire même de graphiques croisés dynamiques, etc.) avant de te focaliser sur une formule matricielle "lourde".
A+