Moyenne.si

[GDCANDF]

Bonjour,

J'utilise les bases INSEE pour usage professionnel.


J'utilise un SOMME.SI pour faire des sous totaux par zone géographique (-> mes critères, au nombre de 70).
J'ai besoin à certains moments de faire des MOYENNE.SI mais je ne dispose que d'EXCEL 2003.
Mes SOMME.SI fonctionnent parfaitement bien et se présentent ainsi.
=SOMME.SI('BASE 6A'!$C:$C;Présentation!$C$20;'BASE 6A'!K:K)

Dans la colonne C de BASE 6A, mes zones géographiques
Dans la cellule C20 de Présentation, mon choix de zone géographique (critère)
Dans la colonne K EF) de BASE 6A, mes résultats.

Je précise que la colonne K présente des cellules vides et que je voudrais évidemment les ignorer.

J'ai essayé pas mal de choses, des matricielles MOYENNE(SI...), des SOMMEPROD, mais je n'y comprends rien. Les résultats sont aberrants ou affichent des messages d'erreur.

Que faire ? Merci d'avance pour vos réponses.

 

[Jocelyn]

Re : Moyenne.si

Bonjour le Forum,
Bonjour GDCANDF,

Lorsque l'on travail avec une formule Matricielle il ne faut pas mettre de colonne entières de type 'BASE 6A'!M:M.

De plus lorsque tu regardes le critère correspondant a la cellule C20 de la feuille présentation qui est numérique du le compare aux valeur de la colonne C de la feuille BASE 6A qui contient des valeur alpha cela ne peut pas marcher on peux corriger de cette façon ('BASE 6A'!$C2:$C1000*1=Présentation!$C$20) en multipliant les valeurs de la colonne C par 1 dans la formule

Cela donnerait une formule complète du genre

=MOYENNE(SI(('BASE 6A'!$C2:$C1000*1=Présentation!$C$20)*('BASE 6A'!M2:M1000<>"");'BASE 6A'!M2:M1000))

formule matricielle bien entendu

voir fichier joint

Cordialement

 

[GDCANDF]

Re : Moyenne.si

Merci Jocelyn pour cette réponse rapide !

Je me disais bien que la colonne C en valeur alpha posait problème. Mais les somme.si n'étant pas affectées je me suis dit que la raison était ailleurs... et non. De plus je ne savais pas qu'on ne pouvait pas utiliser les formules matricielles sur des colonnes entières.

La formule est très bien, un grand merci. Je vérifie que cela fonctionne et reviens ici pour confirmer.

Merci de partager ainsi tes connaissances.

 

[GDCANDF]

Re : Moyenne.si

Mon problème est en voie de résolution mais un problème subsiste...

La formule de Jocelyn semble tout à fait correcte. Et pourtant, les résultats sont (légèrement !) erronés...

Voir fichier joint. 3e feuille, à partir d'I1.

Au dessus ce que j'obtiens avec la formule de Jocelyn, et en dessous ce que j'obtiens en appliquant un filtre sur ma zone géo dans BASE 6 et en calculant tout bêtement la moyenne dans cette feuille.

Je n'ai laissé pour l'exemple qu'une seule zone géographique (0010, ARRAS)

J'ai beaucoup de mal à comprendre pourquoi on trouve deux moyennes différentes...

Merci encore pour votre aide.

 

[Jocelyn]

Re : Moyenne.si

re,

Si j'ai bien compris tu as en feuille BASE 6A ligne 1547 fait la moyenne manuellement pour le code 10 et tu ne tombe pas juste avec la moyenne calculée par la formule.

Humhum, , cela me parait logique puisque tes donnée vont jusqu'en ligne 1546 et que dans la formule les plages de données s'arrete en ligne 1000

si tu remplaces

=MOYENNE(SI(('BASE 6A'!$C2:$C1000*1=Présentation!$C$20)*('BASE 6A'!M2:M1000<>"");'BASE 6A'!M2:M1000))

par

=MOYENNE(SI(('BASE 6A'!$C2:$C2000*1=Présentation!$C$20)*('BASE 6A'!M2:M2000<>"");'BASE 6A'!M2:M2000))

le résultat est identique

regardes et dis nous

 

[Legolas]

Re : Moyenne.si

Bonjour à tous,

Tes formules s'arretent à la ligne 1000.
Il faut juste agrandir ta plage jusqu'à la dernière cellule non vide (j'ai testé en mettant 2000 et ça marche).

A+

 

[GDCANDF]

Re : Moyenne.si

Eh oui, ça marche ! Bravo et merci Jocelyn ! (EDIT et Legolas)
Il faudrait une formule pour me sortir de ma léthargie intellectuelle, je n'ai clairement pas assez réfléchi...

Merci beaucoup d'avoir partagé votre savoir-faire avec moi.
Vous avez résolu mon problème.

Bonne journée et bonne continuation.

 

[Jocelyn]

Re : Moyenne.si

re,
Bonjour legolas,

je te fait passer un autre fichier ou pour ne pas etre ennuyé avec la longueur des plages la formule fonctionne avec des plages nommées dynamique la hauteur des plages se calcul suivant le nombre de valeur de la colonne C de la feuille BASE 6A attention il ne faut pas qu'il y est de cellule vides intercalées dans cette colonne

dans le fichier j'ai donc créer 2 plages nommée une pour les codes et une pour la colonne de calcul pour les voir menu insertion nom définir

dans le fichier si tu ajoutes d&ans la feuille BASE 6A une ligne en code 129 sur la ligne 652 et que tu mets sur cette meme ligne un grosse valeur en colonne M tu verras que la formule tiendras compte de cette nouvelle ligne sans rien avoir a faire

regardes et dis nous

 

[GDCANDF]

Re : Moyenne.si

Wow, alors là bravo. Je ne m'attendais pas à ce que cela soit possible.

J'ai fait le test indiqué avec une grosse valeur dans la zone 0129 utilisée.
La modif est prise instantanément. Je vais essayer de bien comprendre la formule car là je suis perdu, mais je vais l'utiliser car elle me permettra plus de souplesse dans le fichier.

Tu as rendu mon tableur plus intelligent, merci. C'est parfait.