Calcul matriciel sur plusieurs feuilles (3D)

[Choops]

Bonjour à tous!

J'aurais une question sur les calculs matriciels en 3D. J'aimerais faire une moyenne conditionnelle d'une plage (D23:J23) sur plusieurs feuilles (sem1:sem6!).

Mon but est de calculer le taux de productivité de Janvier uniquement et donc de ne pas prendre en compte les valeurs pour les autres mois. Or les semaines se chevauchent et des jours de Décembre et Février sont dans les mêmes semaines que ceux de Janvier.

Comment se fait-il que ces 2 formules fonctionnent parfaitement :
=MOYENNE(sem1:sem4!D23:J23))
(moyenne pour les mêmes cellules d’un groupe de feuilles)

et

{=MOYENNE(SI(sem1!D85:J85="janvier";sem1!D23:J23))}
(moyenne conditionnelle pour une seule feuille)

alors que celle-ci me donne #REF en guise de résultat:
{=MOYENNE(SI(sem1:sem4!D85:J85="janvier";sem1:sem4 !D23:J23))}
(moyenne conditionnelle pour les mêmes cellules d’un groupe de feuilles)

Merci d’avance!!

 

[Choops]

Re : Calcul matriciel sur plusieurs feuilles (3D)

J'ai effectué quelques tests mais je n'ai pas pu trouver de solution pour l'instant.
Merci d'avance!

 

[JHA]

Re : Calcul matriciel sur plusieurs feuilles (3D)

Bonjour Choops, le forum,

Regarde la formule en B5 de l'onglet "Controle", J'ai nommé les plages de chaque semaines et de chaque mois pour plus de clarté mais cela restera une formule assez longue sur 52 semaines.
{=(MOYENNE(SI(MoisSem1="janvier";sem1))+MOYENNE(SI(MoisSem2="janvier";Sem2))+MOYENNE(SI(Moissem3="janvier";Sem3))+MOYENNE(SI(MoisSem4="janvier";Sem4)))/4}


JHA

 

[Choops]

Re : Calcul matriciel sur plusieurs feuilles (3D)

Hello JHA! Bon, on s'est planté tous les 2 dans les formules des moyennes car on ne peut pas faire une moyenne de moyennes si ces moyennes ont des bases différentes.

Pour l'instant je ne vois pas comment faire autrement que de recopier toutes les données (date, mois et tx de productivité) des différentes feuilles (sem1:sem9!) sur la feuille "controle" et donc avoir un tableau de 366 lignes et 3 colonnes. ça me permettrait ensuite d'y appliquer la formule de la moyenne conditionnelle sans problème.

Ceci dit j'aimerais avoir recours à cette méthode seulement en dernier ressort donc si tu as une idée, ou un autre forumeur, vous êtes les bienvenus ! 🙂

Merci!

 

[wilfried_42]

Re : Calcul matriciel sur plusieurs feuilles (3D)

Bonjour

Voici une autre solution, avec 14 colonnes masquées qui recuperent les date et les chiffres des 53 semaines
(pour le moment, il y as 20 semaines de faites, il suffit de glisser les formules jusqu'à la ligne 53)

dans le tableau : p3 à p5, (semaine) recap moyenne par semaine, en p8 : calcul selon le mois en utilisant les colonnes masquées

 

[Choops]

Re : Calcul matriciel sur plusieurs feuilles (3D)

Hello! merci je vais regarder ça de manière approfondie car je ne connais pas certaines fonctions comme decaler, indirect et non.

En attendant, merci pour ta contribution!

 

[wilfried_42]

Re : Calcul matriciel sur plusieurs feuilles (3D)

re:

INDIRECT : Permet de transformer une reference definie en texte en reference cellule

"Sem" & ligne() & "!A1" (Sur la ligne 1) donnera "Sem1!A1" avec indirect, il va rechercher la cellule A1 de la feuile Sem1!

DECALER : Permet de prendre une cellule de reference et de la decaler de x lignes, y colonne. permet aussi de definir des plages

DECALER(Cellule;lignes;colonnes;nblignes;nbcolonnes)
exemple : Decaler(A1;2;3;10;7)
Decaler A1 de 2 lignes ---> A3, de 3 Colonnes ----> D3
ensuite les 2 derniers pour definir une plage
D3 ---> nbligne 10 ---> D3😀12, nbcolonne 7 ----> D3:J12

NON() permet d'inverser la valeur VRAI ou FAUX

ESTERREUR(15/0) ' 15/0 va provoquer une erreur #DIV0 ESTERREUR va renvoyer VRAI
Avec NON on RENVOIE FAUX

 

[Monique]

Re : Calcul matriciel sur plusieurs feuilles (3D)

Bonjour,

Ce que l'on peut faire sur plusieurs feuilles avec
SOMMEPROD(SOMME.SI(INDIRECT(etc
SOMMEPROD(NB.SI(INDIRECT(etc
Pour la moyenne, il y a trop de critères
Donc tableau de 12 lignes sur 53 colonnes

Source : Ce lien n'existe plus
avec plus d’explications et un lien vers une macro.

Sinon, une formule donne, dans chaque feuille hebdo, la date du 1er lundi selon le nom de l'onglet

 

[Choops]

Re : Calcul matriciel sur plusieurs feuilles (3D)

Merci à toi wilfried pour tes explications!! Tu me maches le travail !!

Monique, merci aussi pour ton coup de main! En plus tu réponds à une autre question que je me posais qui est celle de la prise en compte de décembre 2007 avec décembre 2008 et Janvier 2008 avec Janvier 2009.

Je vais regarder dans le courant de la semaine si tout cela fonctionne et comment je pourrai organiser ma feuille "controle" en fonction de ces calculs.