Discussion :

[plonsqua63]

Bonjour à tous

J'ai bien cherché et n'ai pas trouvé, il me semble, réponse à mes questions liées au fichier joint dans le forum
Pour information, dans le fichier d'origine (celui joint est un extrait) les valeurs "Taux de respect..." viennent d'une autre feuille du tableur. Je ne fais donc que rajouter une colonne à chaque mois.
Le diagramme d'origine est crée en utilisant la fonction décaler (sélection des 12 derniers mois) donc à chaque rajout d'une colonne le graphe se met à jour.

Mes questions :

La première concerne la ligne "moyenne" -> j'aimerais une formule qui me calcule la moyenne des 12 dernières valeurs de taux (cases non vides) qui se décale à chaque rajout de colonne-> si besoin voir les formules dans cases.

La deuxième concerne le diagramme -> est-il possible de différencier les mois où il n'y a pas eu de mesure "case vide" (texte "pas de mesure ce mois" ?) de ceux ou la mesure est 0 en automatique (paramétrage ou autre)

Merci à tous ceux qui prendront un peu de temps pour regarder mon problème

Christophe

[Daniel.C]

Bonjour,

1. en U3, formule matricielle (valider avec Ctrl+Maj+Entrée) et à tirer vers la droite :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=MOYENNE(INDIRECT(ADRESSE(2;GRANDE.VALEUR(SI($B$2:U2<>"";COLONNE($B$2:U2));1))&":"&ADRESSE(2;GRANDE.VALEUR(SI($B$2:U2<>"";COLONNE($B$2:U2));12))))

2. je regarde.

[Daniel.C]

1. Ajoute une plage de cellules (en ligne 5 dans mon exemple) avec la formule en B5, à tirer vers la droite :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(ESTVIDE(B2);20;0)

2. Dans le graphique, ajoute une série avec les valeurs U5:AF5.
3. Ajoute des étiquettes à cette série et formate-les.
3.A Dans l'onglet "Nombre, choisis "personnalisé, dans la zone "code de format", mets : "pas de mesure ce mois";;
ce qui signifie que si le nombre est plus grand que 0, le texte apparaîtra.
3.B Dans l'onglet "Alignement", dans la liste déroulante "Orientation du texte", choisis "Faire pivoter tout le texte de 270°"
3.C dans le même onglet, dans la liste déroulante "Alignement horizontal", choisis "Milieu centre". Clique sur "Fermer.
4. Clic droit sur la série, option "Mettre en forme une série de données", onglet "Couleur du trait", cocher "Aucun trait".

Pièce jointe : plonsqua63 question pour forum.xlsx

[plonsqua63]

Bonsoir et merci tout marche.

Pour le 1 j'ai trouvé aussi (sur le net) la formule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMMEPROD(($B2:V2)*(COLONNE($B2:V2)>EQUIV(GRANDE.VALEUR(($A2:V2<>"")*($A2:V2<>"")*COLONNE($A2:V2);12);($A2:V2<>"")*($A2:V2<>"")*COLONNE($A2:V2);0)-1))/12

Les 2 formules me conviennent, je ne sais pas la différence mais bon...d'ailleurs un conseil sur un ouvrage pour apprendre les fonctions matricielles et autres ?

Pour le 2 j'ai mis la formule =SI(ESTVIDE(U2);2;-1) car il semblerait que pour le 3.A le texte apparait si le nombre est supérieur ou égal à 0 ce qui ne me convenait pas.

Ci-joint le format définitif.

Merci encore

Christophe

[Daniel.C]

Bonsoir,

d'ailleurs un conseil sur un ouvrage pour apprendre les fonctions matricielles et autres ?

Je t'avais répondu en te mettant un lien sur un très bon tuto vers un site externe. Ca n'a pas l'air de plaire soit au modérateur ou alors, il y a un surveillance par mots clé, car mon message a été supprimé. Comme je n'aime pas la censure, je t'envoie ce message sans lien pour que tu soies informé et dans le message suivant, je mettrai le lien avec un espace entre chaque lettre. Si tu reçois ce second message, enlève les espaces pour reconstituer le lien vers le tuto.

[Daniel.C]

w w w . e x c e l a b o . n e t / e x c e l / m a t r i c i e l l e s

[plonsqua63]

Bonjour

Je reviens car cela ne marche pas pour l'ensemble de mon besoin.
En effet, manque de précision de ma part , des fois il n'y a pas de case vide (fonction des équipes mesurées, certaines ont des cases vides, d'autres pas ou peu, d'autres jamais) dans la série et là la formule ne marche pas.
J'ai rajouté un jeu de données dans le fichier avec la moyenne calculé "à la main" et le résultat que donne la formule matricielle.

Merci

Christophe

[Daniel.C]

Bonjour,

En L3, tu n'obtiens pas la moyenne mais la somme des 10 valeurs divisée par 12.

La moyenne (matricielle) est pour L6 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=MOYENNE(INDIRECT(ADRESSE(2;SI(COLONNE(L1)=2;2;GRANDE.VALEUR(SI($B$2:L2<>"";COLONNE($B$2:L2));1)))&":"&ADRESSE(2;SIERREUR(GRANDE.VALEUR(SI($B$2:L2<>"";COLONNE($B$2:L2));12);2))))

Si tu veux le 12e de la somme :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMME(INDIRECT(ADRESSE(2;SI(COLONNE(L1)=2;2;GRANDE.VALEUR(SI($B$2:L2<>"";COLONNE($B$2:L2));1)))&":"&ADRESSE(2;SIERREUR(GRANDE.VALEUR(SI($B$2:L2<>"";COLONNE($B$2:L2));12);2))))/12

En C6, la formule renvooie une erreur. Mets alors :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SIERREUR(MOYENNE(INDIRECT(ADRESSE(2;SI(COLONNE(B1)=2;2;GRANDE.VALEUR(SI($B$2:B2<>"";COLONNE($B$2:B2));1)))&":"&ADRESSE(2;SIERREUR(GRANDE.VALEUR(SI($B$2:B2<>"";COLONNE($B$2:B2));12);2))));"")

[plonsqua63]

En L3, tu n'obtiens pas la moyenne mais la somme des 10 valeurs divisée par 12.

En fait c'est une erreur c'est que je n'avais que 10 valeurs en début de tableau, la bonne formule commence à partir de la case O3. C'est effectivement la somme des 12 dernières (les plus proches du mois sélectionner) non vides divisée par 12.
Donc La lligne 6 avec la formule matricielle marche bien.

Je me suis rendu compte par contre que quand je n'ai pas de vide (ligne 8) les résultats ne sont plus concordants (ligne 9 et 10).
Comme il n'y a pas de constance dans le fait qu'il y ai ou non des cases vides, il me faut une formule qui marche dans tous les cas.

En C6, la formule renvoie une erreur. Mets alors :

C'est pas grave car c'est le début du tableau et je n'affiche que les 12 derniers mois (mon tableau commence réellement en 2012 mais l’indicateur a été mis en place il y a 6 mois. J'ai pu récupérer un historiques de données)

Merci

[Daniel.C]

Les formules ne peuvent pas se recopier telles quelles de la ligne 6 à la ligne 10

En C10, la formule devient :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=MOYENNE(INDIRECT(ADRESSE(8;SI(COLONNE(C1)=2;2;GRANDE.VALEUR(SI($B$8:C8<>"";COLONNE($B$8:C8));1)))&":"&ADRESSE(8;SIERREUR(GRANDE.VALEUR(SI($B$8:C8<>"";COLONNE($B$8:C8));12);2))))

ou plutôt, utilise celle-ci en C10 qui, elle, peut se recopier sur la ligne 6 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=MOYENNE(INDIRECT(ADRESSE(LIGNE(A8);SI(COLONNE(C1)=2;2;GRANDE.VALEUR(SI($B$8:C8<>"";COLONNE($B$8:C8));1)))&":"&ADRESSE(LIGNE(A8);SIERREUR(GRANDE.VALEUR(SI($B$8:C8<>"";COLONNE($B$8:C8));12);2))))

[plonsqua63]

Re et merci cette fois cela à l'air d'être bon.

Pour que la formule marche quelque soit la ligne (j'ai 14 équipes à mesurer avec 2 taux de respect différent) il faut bien que j'adapte les caractères que j'ai mis en rouge ci-dessous dans ta formule ?

=MOYENNE(INDIRECT(ADRESSE(LIGNE(A 8 );SI(C OLONNE(C1)=2;2;GRANDE.VALEUR(SI($B$ 8 :C 8 <>"";COLONNE( $B$ 8 :C8 ));1)))&":"& ADRESSE(LIGNE(A8);SIERREUR(GRANDE.VALEUR(SI($B$ 8 :C8<>"";COLONNE($B$ 8 :C8));12);2) )))

Quelle est la fonction (succincte des bornes que j'ai mis en bleu dans la formule) car :
- la borne évolue quand je décale à droite
- mes tableaux originaux ne commencent pas colonne A

Encore merci

Christophe

[Daniel.C]

Le problème est l'écart variable entre la ligne contenant les formules et la lignes contenant les valeurs à traiter. Sinon, "8" représente le numéro de ligne des valeurs, "C la colonne contenant la formule et B la colonne initiale.

[plonsqua63]

Encore merci.

Je suis en congés j'essiae lundi sur le tableau complet et reviens si besoin.

Cdlt