somme si et sous total

[GHISLAIN]

bonjour le forum ,

je viens vers vous a la recherche d une formule sous total et somme si

ce que je trouve sur le forum prenne en compte les filtres automatique ce que je n'ai pas dans mon fichier final
je cherche donc une formule adaptable a mon cas

j ai testé =SOUS.TOTAL(109;(SOMME.SI(A99:B516;A7;B99:B516)))

mais bon sans grand succès

merci a tous de votre aide

cordialement

Ghislain

 

[R@chid]

Re : somme si et sous total

Bonsoir @ tous,
Salut GHISLAIN,
Voir PJ
Avec colonne Intermediaire et LIRE.CELLULE, SOUS.TOTAL() ne fonctionne pas dans ton cas.
Voir PJ
Amicalement

 

[mapomme]

Re : somme si et sous total

Bonjour GHISLAIN,R@chid,

Un essai avec une formule matricielle à placer dans la cellule B6, à valider par Ctrl+Maj+Entrée puis à copier vers la droite et vers le bas.
Formule matricielle:

=SOMMEPROD(($A$14:$A$34=$A6)*SOUS.TOTAL(103;DECALER($A$14;LIGNE(INDIRECT("1:" & LIGNES($A$14:$A$34)))-1;0))*B$14:B$34)

Formule matricielle: Elle doit être validée par la combinaison des touches Ctrl+Maj+Entrée au lieu de la seule touche Entrée comme une formule classique.
Si la validation matricielle est correcte, alors Excel entoure la formule d'accolades {=.......}.
Chaque fois que cette formule sera modifiée, la validation devra se faire par Ctrl+Maj+Entrée. Les accolades apparaissent à la validation et ne doivent pas être saisies au clavier.

 

[R@chid]

Re : somme si et sous total

Bonjour @ tous,
Salut mapomme 🙂,
Je n'ai pas Excel2003 sous les mains, mais je ne suis pas sur de la disponibilité des arguments 101 @ 109 qui ignorent les valeurs masquées dans cette version...
Amicalement

 

[R@chid]

Re : somme si et sous total

Bonjour @ tous,
Oui je viens d'installer l'Office2003 et ça fonctionne, les arguments 101 @ 109 sont prises en charge par cette version...
@ GHISLAIN : tu n'as pas besoin maintenant de la Colonne Intermédiaire...
Amicalement

 

[mapomme]

Re : somme si et sous total

Bonjour GHISLAIN et le forum,

R@chid (que je salue amicalement) me signale que ma formule du message #3 se contente d'une validation ordinaire. Merci 🙂
Merci aussi d'avoir vérifié que la formule fonctionne en Excel 2003.

@+

 

[GHISLAIN]

Re : somme si et sous total

bonjour Rachid et mapomme,

je vous remercie tout deux d etre passé sur mon fil et de me faire des propositions fonctionnelles , ça laisse le choix ....
une pensée pour Rachid , c est bon j'ai pigé les colonnes intermédiaire , j'ai acquis ton cours ;-))

merci a tout deux

Amicalement

Ghislain

 

[GHISLAIN]

Re : somme si et sous total

re tous , voila j ai mis en place vos propositions , j'ai opter pour la solution de Rachid en hommage a sa patience et pour mise en application de son cours sur un autre poste des colonnes intermédiaires que je ne connaissais pas
je garde sous le coude l'option de ma pomme qui me sera utile ultérieurement .

je souhaiterais finaliser ce fichier non plus en ajoutant mais en récupérant des données par rapport au nom en colonne a
sans doublon

ci joint le fichier plus explicite j en suis sur

merci de votre patience et aide

Amicalement

GHISLAIN

 

[mapomme]

Re : somme si et sous total

Bonsoir GHISLAIN,

Je ne suis pas certain d'avoir bien compris. Ce que j'en ai compris:

• pour chaque ligne du premier tableau:
• on examine les lignes du second tableau correspondant au nom de la ligne du premier tableau
• parmi ces lignes, on ne retient que les lignes visibles
• pour ces lignes visibles, on va chercher les mots à partir de la colonne Q
• on retient les 5 premiers mots différents
• on inscrit ces mots dans les colonnes M à Q sur la ligne du 1ier tableau
• on trie ces mots par ordre alphabétique

Voir le fichier joint pour un essai de solution en VBA (cliquer sur "afficher ligne" ou "masquer ligne")

 

[R@chid]

Re : somme si et sous total

Bonjour/Bonsoir @ tous,
Je t'ai fait ton fichier,
j'ai préféré le faire avec SOUS.TOTAL() pour ne pas te déranger avec la Colonne Intermédiaire...
et car j'avais un problème de niveaux d'imbrications, j’étais obliger de nommer la formule avec SOUS.TOTAL() ==> FORMULE_SOUSTOTAL, et utiliser la fonction SIERREUR() et l'enregistrer sous *.XLSM,
Voir si répond d'abord @ ton besoin,
et je vais voir comment démerder le problème d'imbrication avec Excel2003, sinon je voie que je dois passer par un tableau intermédiaire...
Amicalement

Edit : Salut mapomme

 

[GHISLAIN]

Re : somme si et sous total

re ,

un grand merci a vous deux , vous avez parfaitement cernée ma demande

donc pour répondre a mapomme , c est effectivement les 5 premier mots (il n'y en aura jamais plus a récupérer) la macro effectue ma demande mais très lente dans sa fonction , je vais tenter de l utiliser en désactivant le calcul auto , mais je ne sais pas si ce sera compatible avec les formules de Rachid, qui sont intégrés dans mon fichier finale.

Rachid tu as également cerné ma demande et sortie des formules de ouf
comme tu le fait remarquer , j'utilise 2003 et en l 'enregistrant dans ce format j ai un mess d excel me signalant qu il recalculera les formules en version 2003

je l ai ré ouvert et apparemment tes formules sont prises en compte par excel 2003

j essaie d intégrer tout ça et je vous tient quoi qu il en soit au courant

en attendant merci de votre énorme talent en la matière en encore une fois merci de le partager

Amicalement aux exceliens
GHISLAIN

 

[R@chid]

Re : somme si et sous total

Bonsoir @ tous,

je l ai ré ouvert et apparemment tes formules sont prises en compte par excel 2003

je ne sais pas quoi dire, normalement SIERREUR() n'existe que depuis 2007...
Amicalement

 

[R@chid]

Re : somme si et sous total

ReBonsoir @ tous,
ça ne fonctionne pas chez moi sur Excel2003, je ne sais comment il fonctionne sur ton PC
Amicalement

 

[GHISLAIN]

Re : somme si et sous total

bonjour Rachid,

j'ai Excel 2007 , mais j'enregistre la version sous excel 97-2003 .xls , car au taf j'utilise Excel2003, peut être que le faite de le ré ouvrir même dans le format enregistré 97-2003 la fonction est prise en compte

Amicalement

GHISLAIN

 

[GHISLAIN]

Re : somme si et sous total

Re Rachid,

je viens d’intégrer ta proposition sur mon fichier final
le problème rencontrer est la rapidité d’exécution , je boss sur 516 lignes

ma question est : dans la formule est il possible de prendre en compte la colonne de récupération des noms de A7 a A13 le fait que si vide cellule M,N,O,P et Q vide
si non alors la formule matricielle ce met en route

donc du style :

si($A6 ="";""; =SOMME(SI(($A$99:$A$516=$A7)*(Col_Intermed>0);B$99:B$516))

etc pour toute les formules

ce qui permettrait de supprimer un calcul auto sur des valeurs vident

Dit moi ce que tu en pense

Amicalement

GHISLAIN