XL 2010 aide pour formule si et arrondi

=ARRONDI(B1:F1;0)+CHOISIR(SOMMEPROD(ARRONDI(B1:F1;0))-98;
((COLONNE(B1:F1)-COLONNE(B1)+1)=EQUIV(MAX(SI(MOD(B1:F1;1)<0,5;MOD(B1:F1;1);0));MOD(B1:F1;1);0));
0;
-((COLONNE(B1:F1)-COLONNE(B1)+1)=EQUIV(MIN(SI(MOD(B1:F1;1)>=0,5;MOD(B1:F1;1);1));MOD(B1:F1;1);0))
)

• =99 d'augmenter le premier arrondi qui contenait la plus grande partie décimale <0,5
• =101 de diminuer le premier arrondi qui contenait la plus petite partie décimale >=0,5

• _Ds fait référence à un bloc de 5 cellules de la ligne du dessus commençant en colonne A
• _Arrondi contient la formule qui rectifie les arrondis pour arriver à 100.

AtTheOne à dit:

Bonne nuit

Pour faciliter la saisie de formule, j'ai créé deux noms définis :

Nom défini	Définition (cellule active sur la ligne 3)
_Ds	=DECALER(Feuil1!$A2;-1;0;1;5)
_Arrondi	=ARRONDI(_Ds;0) SOMMEPROD(ARRONDI(_Ds;0))-98 ((COLONNE(_Ds)--COLONNE(INDEX(_Ds;1))+1)=EQUIV(MAX(SI(MOD(_Ds;1)<0,5;MOD(_Ds;1);0));MOD(_Ds;1);0)) 0 -((COLONNE()-COLONNE(INDEX(_Ds;1))+1)=EQUIV(MIN(SI(MOD(_Ds;1)>=0,5;MOD(_Ds;1);1));MOD(_Ds;1);0))

• _Ds fait référence à un bloc de 5 cellules de la ligne du dessus commençant en colonne A
• _Arrondi contient la formule qui rectifie les arrondis pour arriver à 100.

Si tes plages de données sont plus longues ou ne commence plus en colonne A, il n'y a qu'à modifier le nom _DS.
Par exemple si la colonne de début est H, est le nombre de colonne = 4, redéfinir _Ds pour faire référence à =DECALER(Feuil1!$H2;-1;0;1;4) (la cellule active étant sur la ligne 3)

Sous la 1ère cellule qui contient la valeur avec décimales, tu n'as qu'à écrire la formule =_Arrondi, valider par ENTRÉE et tirer cette formule vers la droite.
Plus besoin de valider par CTRL+MAJ+ENTRÉE car les formules contenue dans les noms sont interprétées comme des formules matricielles.

Voir l'exemple en pièce jointe
Amicalement
Alain

Cliquez pour agrandir...

Martin sav à dit:

l'arrondi commence toujours a la cellule A d'abord et lui il est arrondi selon l'arrondi normal de 0,5

Cliquez pour agrandir...

AtTheOne à dit:

Bonjour à toutes et à tous, bonjour @Martin sav

Est-ce que tu veux dire que l'on ne rectifie que les arrondis des colonnes B à E et qu'on laisse l'arrondi de la colonne A tel quel ?
Parce-que c'est faisable avec une petite adaptation ...
Amicalement
Alain

Cliquez pour agrandir...

AtTheOne à dit:

Bonjour à toutes & à tous, bonjour @Martin sav

J'ai crée une fonction "Arrondi_Rectifié()" sans argument pour ramener ton total d'arrondi à 100%.
La configuration du fichier joint au post #8 est différente de celle du post #1 (5 colonnes dont une vide au lieu de 4 contigües)
Dans ton (tes) fichiers(s) cible(s), il te faudra créer le nom défini "_Moy" qui se réfère aux quatre ou cinq cellules situées au-dessus de la ligne courante et commençant à la 1ère colonne concernée par la rectification.
Par exemple si la première colonne à rectifiée est la colonne la colonne J que la ligne courante est la ligne 13 et que le nombre de colonnes à prendre en compte est 5 :
_Moy se réfère à =DECALER(!$J13;-1;0;1;5) (Bien noter !$J13)
Regarde la pièce jointe 1134188
Regarde la pièce jointe 1134189

Le code se trouve dans le Module "M0_Fonction" :

Enrichi (BBcode):

Option Base 1
'Rectification des arrondis sur la plage "_Moy" pour ramener le total des pourcentages à 100
'Rectifications faites dans l'ordre des priorités indiqué dans le tableau Idx
Function Arrondi_Rectifié() As Variant
    Application.Volatile True
  
    Dim Idx, C As Range, Col As Long, i, j As Byte, Somme As Byte
    Dim TbV, NbV As Byte, TbA(), TbDp(), DbDg()
    Dim P1 As Double, G1 As Double, Pi As Byte, Gi As Byte
  
    Idx = Array(1, 4, 3, 5, 2) 'à adapter pour le nb de col et l'ordre de priorité des rectifications à respecter
  
    Set C = Application.Caller: Col = C.Column - C.Parent.[_Moy].Column + 1: TbV = C.Parent.[_Moy]
    Somme = Round(C.Parent.[_Moy].Offset(0, -1).Resize(1, 1), 0)
    NbV = UBound(TbV, 2)
    ReDim TbA(1 To NbV): ReDim TbDp(1 To NbV):: ReDim tbDg(1 To NbV)
    j = 0
    Set C = Nothing
    For Each i In Idx
        j = j + 1
        TbA(j) = Round(TbV(1, i), 0): Somme = Somme + TbA(j)
        'Parties décimales inférieures à 0.5 pour rectification par augmentation
        TbDp(j) = IIf(Round(TbV(1, i) - Int(TbV(1, i)), 2) < 0.5, Round(TbV(1, i) - Int(TbV(1, i)), 2), 0)
        'Parties décimales supérieures ou égales à 0.5 pour rectification par diminution
        tbDg(j) = IIf(Round(TbV(1, i) - Int(TbV(1, i)), 2) >= 0.5, Round(TbV(1, i) - Int(TbV(1, i)), 2), 1)
    Next
    Erase TbV
  
    With WorksheetFunction
        P1 = .Large(TbDp, 1)        '1ère plus grande décimale <.5
        Pi = .Match(P1, TbDp, 0)    'N° de la colonne
        TbDp(Pi) = 0                'On ignore la valeur retenue ci-dessus
        P1 = .Large(TbDp, 1)        '2ème plus grande décimale <.5
        Pj = .Match(P1, TbDp, 0)    'N° de la colonne
        G1 = .Small(tbDg, 1)        '1ère plus petite décimale >= 0.5
        Gi = .Match(G1, tbDg, 0)    'N° de la colonne
        tbDg(Gi) = 1                'On ignore la valeur retenue ci-dessus
        G1 = .Small(tbDg, 1)        '2ème plus petite décimale >= 0.5
        gj = .Match(G1, tbDg, 0)    'N° de la colonne
        Col = .Match(Col, Idx, 0)   'N° de la colonne à prendre en compte dans l'ordre des priorité
    End With
  
    Select Case Somme               'Rectification en fonction de la somme des arrondis
        Case 98
            TbA(Pi) = TbA(Pi) + 1
            TbA(Pj) = TbA(Pj) + 1
        Case 99
            TbA(Pi) = TbA(Pi) + 1
        Case 101
            TbA(Gi) = TbA(Gi) - 1
        Case 102
            TbA(Gi) = TbA(Gi) - 1
            TbA(gj) = TbA(gj) - 1
        Case Else
            'pas de rectification
    End Select
  
    Arrondi_Rectifié = TbA(Col)       'Renvoie de la colonne concérnée
  
End Function

Voilà, bon courage
Amicalement
Alain

Cliquez pour agrandir...

AtTheOne à dit:

Bonjour à toutes & à tous, bonjour @Martin sav

J'ai crée une fonction "Arrondi_Rectifié()" sans argument pour ramener ton total d'arrondi à 100%.
La configuration du fichier joint au post #8 est différente de celle du post #1 (5 colonnes dont une vide au lieu de 4 contigües)
Dans ton (tes) fichiers(s) cible(s), il te faudra créer le nom défini "_Moy" qui se réfère aux quatre ou cinq cellules situées au-dessus de la ligne courante et commençant à la 1ère colonne concernée par la rectification.
Par exemple si la première colonne à rectifiée est la colonne la colonne J que la ligne courante est la ligne 13 et que le nombre de colonnes à prendre en compte est 5 :
_Moy se réfère à =DECALER(!$J13;-1;0;1;5) (Bien noter !$J13)
Regarde la pièce jointe 1134188
Regarde la pièce jointe 1134189

Le code se trouve dans le Module "M0_Fonction" :

Enrichi (BBcode):

Option Base 1
'Rectification des arrondis sur la plage "_Moy" pour ramener le total des pourcentages à 100
'Rectifications faites dans l'ordre des priorités indiqué dans le tableau Idx
Function Arrondi_Rectifié() As Variant
    Application.Volatile True
  
    Dim Idx, C As Range, Col As Long, i, j As Byte, Somme As Byte
    Dim TbV, NbV As Byte, TbA(), TbDp(), DbDg()
    Dim P1 As Double, G1 As Double, Pi As Byte, Gi As Byte
  
    Idx = Array(1, 4, 3, 5, 2) 'à adapter pour le nb de col et l'ordre de priorité des rectifications à respecter
  
    Set C = Application.Caller: Col = C.Column - C.Parent.[_Moy].Column + 1: TbV = C.Parent.[_Moy]
    Somme = Round(C.Parent.[_Moy].Offset(0, -1).Resize(1, 1), 0)
    NbV = UBound(TbV, 2)
    ReDim TbA(1 To NbV): ReDim TbDp(1 To NbV):: ReDim tbDg(1 To NbV)
    j = 0
    Set C = Nothing
    For Each i In Idx
        j = j + 1
        TbA(j) = Round(TbV(1, i), 0): Somme = Somme + TbA(j)
        'Parties décimales inférieures à 0.5 pour rectification par augmentation
        TbDp(j) = IIf(Round(TbV(1, i) - Int(TbV(1, i)), 2) < 0.5, Round(TbV(1, i) - Int(TbV(1, i)), 2), 0)
        'Parties décimales supérieures ou égales à 0.5 pour rectification par diminution
        tbDg(j) = IIf(Round(TbV(1, i) - Int(TbV(1, i)), 2) >= 0.5, Round(TbV(1, i) - Int(TbV(1, i)), 2), 1)
    Next
    Erase TbV
  
    With WorksheetFunction
        P1 = .Large(TbDp, 1)        '1ère plus grande décimale <.5
        Pi = .Match(P1, TbDp, 0)    'N° de la colonne
        TbDp(Pi) = 0                'On ignore la valeur retenue ci-dessus
        P1 = .Large(TbDp, 1)        '2ème plus grande décimale <.5
        Pj = .Match(P1, TbDp, 0)    'N° de la colonne
        G1 = .Small(tbDg, 1)        '1ère plus petite décimale >= 0.5
        Gi = .Match(G1, tbDg, 0)    'N° de la colonne
        tbDg(Gi) = 1                'On ignore la valeur retenue ci-dessus
        G1 = .Small(tbDg, 1)        '2ème plus petite décimale >= 0.5
        gj = .Match(G1, tbDg, 0)    'N° de la colonne
        Col = .Match(Col, Idx, 0)   'N° de la colonne à prendre en compte dans l'ordre des priorité
    End With
  
    Select Case Somme               'Rectification en fonction de la somme des arrondis
        Case 98
            TbA(Pi) = TbA(Pi) + 1
            TbA(Pj) = TbA(Pj) + 1
        Case 99
            TbA(Pi) = TbA(Pi) + 1
        Case 101
            TbA(Gi) = TbA(Gi) - 1
        Case 102
            TbA(Gi) = TbA(Gi) - 1
            TbA(gj) = TbA(gj) - 1
        Case Else
            'pas de rectification
    End Select
  
    Arrondi_Rectifié = TbA(Col)       'Renvoie de la colonne concérnée
  
End Function

Voilà, bon courage
Amicalement
Alain

Cliquez pour agrandir...

Notes
Cette fonction VBA renvoie un élément communément appelé la constitution d’arrondi. Soyez donc prudent avant d’utiliser cette fonction. Pour des résultats plus prévisibles, utilisez les fonctions feuille de calcul arrondi dans Excel VBA.

Cliquez pour agrandir...