XL 2019 Formule liée à une base de donnée pour un suivi de cadence

[Samulo]

Bonjour,

Je suis à la recherche d'une formule, qui en fonction de la base de donnée qui sera alimenté régulièrement calculera une moyenne par ligne de production.

Dans le fichier ci-joint :

La feuille "Suivi" se trouve les codes produits avec les cadences théorique par machine (en jaune) et les données réelles extraite de la base de données (en bleu).

Je voudrai que si il y a au moins 14 valeurs pour un code produit et une machine (dans la feuille BDD) on fasse la moyenne des 10 valeurs en enlevant les 2 MAX et les 2 MIN des données. Si moins de 14 valeurs alors on affiche vide. Si il y à 20 valeurs et + on enlève alors les 5 MAX et 5 MIN et si il y a 30 valeurs et + on enlève les 10 MAX et 10 MIN

La feuille "BDD" est la base de données qui sera alimenté régulièrement.

J'espère avoir été clair !

Merci d'avance !

Samuel

 

[mapomme]

Bonsoir @Samulo ,

Par formule, cela risque d'être un peu alambiqué. Je passe donc par une fonction VBA.
=MoyBaseNombreMinimum(Base As Range, Nombre&, Minimum&, Ligne As String, Code&)

Avec:
Base : la base de donnée
Nombre : le nombre de valeurs à moyenner (10 dans votre exemple)
Minimum : le nombre minimum de valeurs à considérer (14 dans votre exemple)
Ligne : le critère ligne
Code : le critère code produit

Ce qui donne la formule suivante en H3 à copier vers la droite et vers le bas:

=MoyBaseNombreMinimum(BDD!$A$1:$C$50000;10; 14;H$2;$A3)

Les intitulés de la ligne 2 on été modifiés pour simplification de la formule.

Le code est dans module1 (notez le commentaire en fin de code) :

Function MoyBaseNombreMinimum(Base As Range, Nombre&, Minimum&, Ligne As String, Code&)
Dim t, nmax&, i&, j&, i1&, i2&, ech As Boolean, aux, combien&, som

   t = Base.Value      'lecture des données de la base dans le tableau t
 
   ' combien de lignes répondent aux deux critères Ligne et Code => nmax
   ' on déplace les valeurs de la 3ème colonne vers le haut du tableau t
   For i = 1 To UBound(t)
      If t(i, 1) = Ligne And t(i, 2) = Code Then
         nmax = nmax + 1
         t(nmax, 3) = t(i, 3)
      End If
   Next i
 
   ' si le minimum n'est pas atteint on quitte
   If nmax < Minimum Then MoyBaseNombreMinimum = "": Exit Function
 
   ' tri de la 3ème colonne de t du plus petit au plus grand de la ligne 1 à nmax
   Do
      ech = False
      For i = 1 To nmax - 1
         'si la valeur de la ligne i+1 est <= à celle de la ligne i, on inverse les lignes i et i+1
         If t(i + 1, 3) < t(i, 3) Then ech = True: aux = t(i, 3): t(i, 3) = t(i + 1, 3): t(i + 1, 3) = aux
      Next i
   Loop Until Not ech   'et cela jusqu'à ce qu'aucun échange ne ligne ne se produise plus
                        'sinon on repart pour un tour
 
   ' ne garder que les Nombre médians
   ' i1 est la ligne inférieure des Nombre valeurs à moyenner
   ' i2 est la ligne supérieure des Nombre valeurs à moyenner
   i1 = 1: i2 = nmax: combien = nmax
   ' on va retirer alternativement une petite valeur (i1=i1+1) puis une grande valeur (i2=i2-1)
   Do
      i1 = i1 + 1: combien = combien - 1
      If combien = Nombre Then Exit Do
      i2 = i2 - 1: combien = combien - 1
      If combien = Nombre Then Exit Do
   Loop
 
   'calcul de la somme des Nombre valeurs médianes
   For i = i1 To i2: som = som + t(i, 3): Next
   'calcul de leur moyenne
   MoyBaseNombreMinimum = som / Nombre
End Function

' c'est la version optimiste - on commence par retirer le plus petit, puis le plus grand,
' puis le plus petit, puis... jusqu'à ce qu'il ne reste que Nombre valeurs.

'  Pour la version pessimiste remplacer:
'      i1 = i1 + 1: combien = combien - 1
'      If combien = Nombre Then Exit Do
'      i2 = i2 - 1: combien = combien - 1
'      If combien = Nombre Then Exit Do

'  par:
'      i2 = i2 - 1: combien = combien - 1
'      If combien = Nombre Then Exit Do
'      i1 = i1 + 1: combien = combien - 1
'      If combien = Nombre Then Exit Do

 

[mapomme]

Re,

Je me suis trompé de fichier... ,
J'ai remis le bon fichier avec le bon code dans le message ci-dessus (version v2)

 

[Samulo]

Salut @mapomme !

Merci beaucoup pour ta réponse ! J'ai quelques questions, là ça effectue une moyenne sur 14 valeurs en enlevant les 2 MIN et MAX de la série donc 10 valeurs MAIS si j'ai 18 valeurs ça fait la moyenne sur 10 valeurs aussi ? L'idéal serait que ça fasse la moyenne sur les 14 valeurs en enlevant les 2 MIN et 2 MAX que le système s'adapte.. Et si j'ai 30 valeurs et + j'aimerai que ça enlève les 5 MAX et 5 MIN et fasse la moyenne sur le reste des valeurs du coup.. Comme j'avais expliqué dans l'énoncé.. Je ne sais pas si c'est possible ?

 

[mapomme]

Salut @mapomme

Merci beaucoup pour ta réponse ! J'ai quelques questions, là ça effectue une moyenne sur 14 valeurs en enlevant les 2 MIN et MAX de la série donc 10 valeurs MAIS si j'ai 18 valeurs ça fait la moyenne sur 10 valeurs aussi ? L'idéal serait que ça fasse la moyenne sur les 14 valeurs en enlevant les 2 MIN et 2 MAX que le système s'adapte.. Et si j'ai 30 valeurs et + j'aimerai que ça enlève les 5 MAX et 5 MIN et fasse la moyenne sur le reste des valeurs du coup.. Comme j'avais expliqué dans l'énoncé.. Je ne sais pas si c'est possible ?

Bonjour
Oui c'est possible mais pas cette après-midi

 

[Samulo]

Bonjour
Oui c'est possible mais pas cette après-midi

Salut,

D'accord, pas de soucis j'attends avec impatience la solution alors !

Mon souhait :

Entre 14 et 20 valeurs enlever 2 MIN / 2 MAX et faire la moyenne sur le reste

Entre 20 et 30 valeurs enlever 5 MIN / 5 MAX et faire la moyenne sur le reste

Entre 30 valeurs et + enlever 10 MIN et 10 MAX et faire la moyenne sur le reste

En dessous de 14 valeurs aucun calcul car pas assez de valeur !

Merci beaucoup !

 

[mapomme]

Re,

Un essai dans le fichier joint.

Le nom de la fonction a changé: MoyBaseCritere(...) ainsi que le nombre d'argument qui est maintenant réduit à 3.

 

[Samulo]

Re,

Un essai dans le fichier joint.

Le nom de la fonction a changé: MoyBaseCritere(...) ainsi que le nombre d'argument qui est maintenant réduit à 3.

C'est parfait, je te remercie infiniment !

Bon week-end !

Samulo