sous.total & formule matricielle

[krystof_ii]

Bonjour, est-il possible d'effectuer d'utiliser la fonction sous.total avec une formule matricielle ?
j'utilise actuellement la fonction NB.SI(colonne_a_sommer;condition) que je souhaiterais transformer en sous.total. Ceci me permettra d'utiliser les filtres dynamiques.

Merci de m'avoir lu (j'espère avoir été clair),
Bonnes fêtes à tous.

Christophe

 

[JeanMarie]

Re : sous.total & formule matricielle

Bonjour

Oui, c'est possible. Que veux-tu faire avec les filtres dynamiques et la fonction SOUS.TOTAL ?. Donne nous des détails.

@+Jean-Marie

 

[krystof_ii]

Re : sous.total & formule matricielle

Plus qu'un détail, j'ai mis en pièce jointe un exemple de fichier :
Feuill1 -> le registre
Feuill2 -> les stats

Je désires remplacer les calculs NB.SI dans la feuill2 par un sous.total avec calcul conditionnel (matriciel) ce qui me permet ensuite d'effectuer les stats sur des extraits de mon registre (utilisation du filtre dynamique).

J'espere que cela sera utile pour trouver la solution,
et vous remercie de nouveau,

Christophe

 

[JeanMarie]

Re : sous.total & formule matricielle

Re...

Pour répondre à ton fichier exemple :

=SOMMEPROD((Feuil1!D$2:D$51=A6)*SOUS.TOTAL(2;DECALER(Feuil1!A$1;LIGNE($1:$50);)))

formule à glisser vers le bas.

Le problème avec les fonctions de sommation, c'est quelles prennent la plage entière. En utilisant la fonction DECALER, pour la plage de la fonction SOUS.TOTAL, on va décomposer la plage ligne par ligne.

Obligation :
- Feuil1!A$1 correspond à la cellule titre d'entête du tableau.
- LIGNE($1:$50) doit commencer à la ligne 1 de la feuille et comporter autant de lignes que la plage D$2😀$51.

@+Jean-Marie

 

[krystof_ii]

Re : sous.total & formule matricielle

Trop fort !!!

Ca marche à merveille !

Merci bcp.

 

[krystof_ii]

Re : sous.total & formule matricielle

La solution proposée par Jean-Marie fonctionne à merveille.
Seul problème : lorsque je rajoute des lignes, il faut que je modifie toutes les formules (notamment pour "ligne($1:$50)")

Ceci me gene bcp car je travaille sur un registre.

Y-a-t-il un moyen de résoudre ce pb (voire en affectant un nom à une plage variable des données) ?

En espérant avoir reponse ...
... Merci de m'avoir lu

 

[Excel-lent]

Re : sous.total & formule matricielle

Oui, donner un nom à ta plage serait une solution

Ou tout simplement enlever les signes dollars

Dans les deux cas, ne pas rajouter de ligne juste en dessous de ta sélection.

Dans ton cas précis, la ligne la plus basse à sélectionner pour faire "insérer une ligne" est la ligne 50.

Si tu sélectionne la ligne 51 (ou plus), tu aura le même soucis qu'actuellement.

 

[krystof_ii]

Re : sous.total & formule matricielle

Effectivement, en rajoutant en haut ou au milieu cela fonctionne si j'enleve les $ dans la formule concernant la fin du tableau.

=SOMMEPROD((Feuil1!D$2:D51=A6)*SOUS.TOTAL(2;DECALER(Feuil1!A$1;LIGNE($1:50);)))

Par contre, pour bien comprendre le fonctionnement de la fonction LIGNE(), j'aurais aimé savoir s'il était possible de lui associer une plage variable (en lui donnant un nom) pour soit tte la fonction decaler soit la fonction ligne.
Et si oui : comment ?

Pour la plage de condition, aucun pb :

Test_col_D=DECALER(Feuil1!$C$2;0;0;NBVAL(Feuil1!$D:$D)-1;1)

La formule donnera donc ensuite :

=SOMMEPROD((Test_col_D=A6)*SOUS.TOTAL(2;DECALER(Feuil1!A$1;LIGNE($1:50);)))

Mais faire de-meme pour la deuxieme condition (qui si j'ai bien compris test l'affichage de la ligne) : je ne vois pas comment !?

En vous remerciant une nouvelle fois de m'avoir lu,
Et espérant avoir réponse,

Amicalement.

PS : Bonne Année à Tous (Santé, Bonheur & réussite personnelle et professionnelle)

 

[JeanMarie]

Re : sous.total & formule matricielle

Bonjour

Je voulais répondre ce matin, mais pas le temps, Krystof tu peux garder les symboles d'absolue dans la formule, l'insertion d'une ligne dans la plage ne pose aucun problème.

Règle sur l'insertion de ligne. Pour que la plage définie dans la formule puisse évoluer avec les mouvements d'insertion de lignes, il faut que l'insertion soit effectuée dans la plage entre la deuxième ligne et la dernière, étant donner que la nouvelle ligne est placée avant la référence active.

Excellent, c'est bigrement une erreur de croire, qu'une plage désignée en absolue ne puisse évoluer.

En ce qui concerne ta question sur une plage variable, regarde le fichier modifié. J'ai ajouter deux plages dans la boîte de dialogue d'insertion de nom. Tu verras une formule qui permet de faire varier la plage automatiquement à chaque rajout d'une donnée dans ton tableau. Dans ce cas, il est nullement besoin d'insertion de ligne. (rajout edit).

@+Jean-Marie

 

[Excel-lent]

Re : sous.total & formule matricielle

Salut JeanMarie,

Excellent, c'est bigrement une erreur de croire, qu'une plage désignée en absolue ne puisse évoluer.

En effet, une ancienne rancoeur contre ce signe qui remonte à la surface au mauvais moment! 😀