cumuler des feuilles avec INDIRECT

[papy39]

Determiner sur plusieurs feuilles pour chaque cellule le NOMBRE de cellules de même rang
contenant des valeurs numériques non nulles.
J'ai écrit d'abord une formule explicite mais pas pratique pour un nombre de feuilles plus important:
=SOMME(SI('imp20126'!F6;1;0)+SI('imp20127'!F6;1;0)+SI('imp20128'!F6;1;0))
Résultat: 2 juste 2 cellules à contenu non nul
J'ai utilisé la fonction INDIRECT pour généraliser la formule; j'ai beaucoup galèré, avec des propositions
de modification d'Excel qui menaient à des erreurs ! J'ai trouvé une formule acceptée:
=SOMME(SI(INDIRECT("imp2012"&LIGNE(INDIRECT("6:8"))&"!"&ADRESSE(LIGNE();COLONNE()));1;0))
Résultat: 0 les 2 formules dans même cellule F6 feuille "Cumul" (Excel 2010)
Je ne vois pas ce qui est faux Help ! Merci
feuille

 

[Grand Chaman Excel]

Re : cumuler des feuilles avec INDIRECT

Bonjour papy39,

Ce n'est pas facile de travailler dans le vide...
Peux-tu joindre un fichier avec quelques données et un exemple de résultat souhaité?

 

[BOISGONTIER]

Re : cumuler des feuilles avec INDIRECT

Bonjour,

=SOMMEPROD(NB.SI(INDIRECT(Nf&"!F6");">0"))

Pour formule copiable

=SOMMEPROD(NB.SI(DECALER(INDIRECT(Nf&"!F6");LIGNE()-2;COLONNE()-1);">0"))

Formules Matricielles 3D



JB

 

[papy39]

Re : cumuler des feuilles avec INDIRECT

Merci de te soucier de mon problème
Je prépare un nouveau classeur "épuré" du reste
avec des explications complémentaires
prévu feuilles résultats souhaités et obtenus
A bientôt

Bonjour papy39,

Ce n'est pas facile de travailler dans le vide...
Peux-tu joindre un fichier avec quelques données et un exemple de résultat souhaité?

 

[papy39]

Re : cumuler des feuilles avec INDIRECT

Grand Shaman, je suis de retour
Ci-joint classeur concerné
As-tu Excel 2010 ? Sinon je le repasserai en 2007
Bon courage

 

[Grand Chaman Excel]

Re : cumuler des feuilles avec INDIRECT

Bonsoir papy39,

As-tu vu la réponse de Boisgontier... tout y est!
En fait, tu y étais presque toi aussi. Il te suffisait d'ajouter SOMMEPROD en début et de valider cette formule matricielle avec CTRL + SHIFT + ENTER.

Essaie cette formule matricielle:

=SOMMEPROD(NB.SI(INDIRECT("imp2012"&LIGNE(INDIRECT("6:8"))&"!"&ADRESSE(LIGNE(),COLONNE())),"<>0"))

Voir dans l'onglet Cumul(3)

A+

 

[BOISGONTIER]

Re : cumuler des feuilles avec INDIRECT

Bonjour,

-Compte les cellules>0 pour toutes les feuilles qui commencent par imp2012
-On peut donc ajouter des feuilles Imp2012xxxx sans modifier les formules.

Créer un nom de champ nf
=STXT(LIRE.CLASSEUR(1);TROUVE("]";LIRE.CLASSEUR(1))+1;99)&INDIRECT("iv65000")

=SOMMEPROD((GAUCHE(nf;7)="IMP2012")*((N(DECALER(INDIRECT("'"&nf&"'!B3");LIGNE()-4;COLONNE()-2))>0)))

Cette formule ne doit pas être sur la ligne 3

JB

 

[papy39]

Re : cumuler des feuilles avec INDIRECT

MERCI MERCI pour Cumul (3) depuis le temps que je cherche tout seul !
Je suis "passé sur" la solution Boisgontier mais je n'ai pas encore étudié SOMMEPROD
A Bientôt

Bonsoir papy39,

As-tu vu la réponse de Boisgontier... tout y est!
En fait, tu y étais presque toi aussi. Il te suffisait d'ajouter SOMMEPROD en début et de valider cette formule matricielle avec CTRL + SHIFT + ENTER.

Essaie cette formule matricielle:

=SOMMEPROD(NB.SI(INDIRECT("imp2012"&LIGNE(INDIRECT("6:8"))&"!"&ADRESSE(LIGNE(),COLONNE())),"<>0"))

Voir dans l'onglet Cumul(3)

A+

 

[papy39]

Re : cumuler des feuilles avec INDIRECT

J'étais passé sur ta formulation sans comprendre la fonction SOMMEPROD
C'est Grand Chaman qui m'a confirmé la qualité de ta solution
Par rapport à ce que j'avais trouvé, j'ai eu seulement à rajouter SOMMEPROD
et les parenthèses matricielles
Merci Boisgontier Je suis très impressionné par ton expertise en Excel

 

[papy39]

Re : cumuler des feuilles avec INDIRECT

Mes salutations du matin à l''expert de l'Excel 3D
J'essaie de comprendre le fonctionnement de SOMMEPROD dans le classeur ci-joint
Pour moi, un élément de la matrice produit de 2 matrices est la somme des produits
des éléments d'une ligne de la matrice 1 par ceux d'une colonne de la matrice 2
A quoi correspondent ces matrices dans l'exemple
Merci Cordialement
feuille_Cumul: formule explicite_Cumul (2): formule erronée_Cumul (3): ta formule

 

[Grand Chaman Excel]

Re : cumuler des feuilles avec INDIRECT

Bonsoir papy39,

Clique sur une cellule (exemple D3 de la feuille Cumul(3) et va dans Formules | Évaluation de formule. Clique ensuite sur le bouton "Évaluer" pour voir comment la cellule est calculée.
Dans ce cas-ci, tu verras que tu n'as en fait qu'une matrice qui est le résultat de ta formule NB.SI.
Tu regarde dans chaque feuille si la valeur de D3 est <> 0.
Tu te retrouves donc avec la matrice suivante : {1;0;1} car il y a une valeur <> 0 dans la cellule D3 de tes feuilles 1 et 3.
Comme tu n'as qu'une seule matrice, SOMMEPROD fait la somme des éléments.

Note: J'ai fait une erreur dans mes messages précédents. Si tu utilises SOMMEPROD, tu n'est pas obligé de l'utiliser en matriciel (CTRL + MAJ + ENTER), bien que ça marche quand même.
Tu pourrais aussi remplacer SOMMEPROD par SOMME mais dans ce cas, tu dois obligatoirement valider avec CTRL + MAJ + ENTER.

En espérant que ce soit un peu plus clair...

A+

 

[papy39]

Re : cumuler des feuilles avec INDIRECT

Merci beaucoup pour tes explications claires pour moi