MOYENNE.SI en 3D

[GMeunier]

Bonjour!
J'ai un cahier Excel 2013 avec 4 feuilles identiques (une par pays) contenant des taux d'exécution de tâches prévus et réalisés (0=>100%) - 1 ligne pour chaque.

Dans une feuille récapitulative, je souhaite faire une moyenne du taux prévu pour les pays où le taux d'exécution prévu est supérieur à 0 (sinon la moyenne récapitulative n'aurait pas de sens) ainsi qu'une moyenne des taux réalisés pour les pays où le taux prévu est <>0 ou taux réalisé <>0 (car certains pays peuvent commencer l'exécution plus tôt que prévu). J'ai attaché un petit fichier de démonstration.

J'ai essayé MOYENNE.SI, la formule me renvoie#Valeur!. J'ai essayé d'autres formules dont matricielle (je ne suis pas trop habitué!) Rien ne fonctionne.

J'aimerai savoir pourquoi MOYENNE.SI ne fonctionne pas dans ce cas en qu'elle est la bonne formule.

Je reste à l'écoute et merci d'avance!

 

[renyeu]

Re : MOYENNE.SI en 3D

Salut
Quelques explications supplémentaires?
Comme par exemple: dans la feuille "Total" que signifie 1er cas, 2ème cas, etc...
A suivre
Cdlt
Renyeu

 

[Guest]

Re : MOYENNE.SI en 3D

Bonjour,

Peut-être ai-je mal compris mais: =SI(NB(Pays01ays03!H5)>0;SOMME(Pays01ays03!H5)/NB(Pays01ays03!H5);"")
en H5 de Total, à tirer vers la droite et le bas, semble fonctionner.

A+

 

[GMeunier]

Re : MOYENNE.SI en 3D

Bonjour,
1° cas, 2° cas etc. est un petit tableau additionnel explicatif (pas suffisamment!) qui illustre les combinaisons entre les valeurs des taux en "Prévu" et en "Réalisé" .
0, 0 rien est prévu et rien est réalisé, 0,5 rien est prévu mais 5% est réalisé, 5,0 5% est prévu mais rien est réalisé, etc.
Merci d'avance!

 

[GMeunier]

Re : MOYENNE.SI en 3D

Bonjour,
Merci pour la proposition mais elle ne fonctionne pas car cette formule ne tient pas compte des 0. Ainsi si j'ai 0% en février pour le Pays01, comme j'ai 10% pour le Pays02 et 10% pour le Pays03 ma moyenne régionale pour février devrait être 10% or j'ai 6,67%, qui est 20/3! Voir attaché.

Il faut que la formule de moyenne du prévu ne prenne en compte que les valeurs prévus >0 au dénominateur. Je continue de mon coté mais votre aide est bienvenu.
Cordialement

 

[Guest]

Re : MOYENNE.SI en 3D

Bonjour,

En sachant que tes noms de feuille vont de Pays01 à PaysNN:

=SOMMEPROD(MOYENNE.SI(INDIRECT("Pays"&TEXTE(LIGNES($1:1);"00")&"!" & ADRESSE(LIGNE();COLONNE();4));">0";INDIRECT("Pays"&TEXTE(LIGNES($1:1);"00")&"!"&ADRESSE(LIGNE();COLONNE();4))))

A+

 

[GMeunier]

Re : MOYENNE.SI en 3D

Hasco, bonjour!

Merci pour la formule (compliquée pour moi mais je vais l'étudier attentivement). Sur un plan pratique, elle ne fonctionne pas correctement.
* En fait, j'ai 4 feuilles "Pays" intitulées: RCA, RC, RDC et Gabon avec une feuille de consolidation "Région" où est située la formule.
* Pour chaque activité, j'ai trois lignes: la 1° avec le taux d'activité mensuel "Prévu", une 2° ligne avec le taux d'activité mensuel "Réalisé" et enfin une 3° ligne pour le calcul du taux relatif (j'ai la formule pour cette 3° ligne).
* Effectivement, je dois pouvoir recopier la formule en ligne (suivant les 12 mois) mais aussi en colonne pour prendre en compte les différentes activités.
* Je veux une moyenne "Région" des taux d'exécution "Prévu" d'une activité des différents Pays (qui varient de 0% à 100%) en excluant de la moyenne les Pays dont le taux "Prévu" est 0% ( en effet si une activité n'est prévue que dans un seul pays la moyenne du taux d'exécution prévu ne doit porter que sur ce seul pays).
* La formule proposée ne fonctionne pas. En fait c'est le taux "Prévu" du premier pays qui est retenu comme moyenne. Elle ne fait pas la bonne moyenne. Ex: 5% en Pays01, 15% en Pays02 et 10% en Pays03 en "Prévu" devrait donner une moyenne de taux d'exécution "Prévu" du programme régional de 10% (les pays ne sont, volontairement,pas pondérés) or on obtient 5%.
* Une fois cette formule résolue, il y a une seconde formule à établir pour la ligne: la moyenne des taux d'exécution "Réel". Cette formule ne doit s'appliquer que si le taux d'exécution "Réel" est <>0% OU si le taux "Prévu" est <>0% (Pour prendre le cas où l'exécution est en avance sur le prévu (Prévu 0% mais Réel = 5%).
* Utilisateur convaincu d'Excel, j'aimerai aussi comprendre pourquoi je ne peux pas utiliser MOYENNE.SI en 3D et ensuite faut il utiliser sommeprod ou bien une formule matricielle?
Merci d'avance pour votre aide!
GDM

 

[BOISGONTIER]

Re : MOYENNE.SI en 3D

Bonjour,

Cf Moyenne 3D conditionnelle

JB

 

[GMeunier]

Re : MOYENNE.SI en 3D

M. BoisGontier, bonjour,

Je vous ai déjà "croisé" sur le web avec intérêt! J'ai testé les 3 formules proposées. Elles ont 2 limites pour ce que je cherche à obtenir.
1) elle renvoie #DIV0! si les taux de tous les pays sont à 0 (ce qui est très possible pour un mois donné),
2) Elles ne sont pas recopiable vers la droite ou vers le bas car les références des cellules sont absolues.
Avez vous une solution pour ces 2 questions (surtout la 1° car la seconde peut être réglée manuellement.

 

[BOISGONTIER]

Re : MOYENNE.SI en 3D

Voir exemple en PJ

=SI(SOMMEPROD(N(DECALER(INDIRECT(nf&"!B5");LIGNES($1:1)-1;COLONNES($A:A)-1)))>0;
MOYENNE(SI(N(DECALER(INDIRECT("'"&nf&"'!B5");LIGNES($1:1)-1;COLONNES($A:A)-1))>0;N(DECALER(INDIRECT("'"&nf&"'!B5");LIGNES($1:1)-1;COLONNES($A:A)-1))));"")

JB

 

[GMeunier]

Re : MOYENNE.SI en 3D

Merci pour cette formule sophistiquée! Elle fonctionne très bien. Je vais essayer de comprendre et de la reproduire. Très cordialement et à une prochaine fois. GDM