XL 2010 aide pour formule si et arrondi

Martin sav · 17 Mars 2022

rebonsoir je vais encore vous déranger.
si F2 inferieure à 100 alors arrondir la valeur dont la partie décimale est la plus grande des cellule B2 à E2 au nombre entier supérieur afin que F2 soit égale à 100. Dans le cas de parties décimales égales, l’ordre de priorité des arrondi est le suivant : B1, D1, C1 et E1. les formule doivent être loger en cellule B2, C2, D2 et E2 afin que la somme A2 à E2 des arrondi en F2 soit toujours égale a 100
merci bien a vous

eddy1975 · 17 Mars 2022

Bonsoir Martin sav,
Tu peux simplement réduire tes cellules à 0 décimales sur la ligne 1 et l'arrondi se fera tout seul sans passer par la formule arrondi sur une deuxième ligne.
Reste à savoir si tu importes tes données ou pas

tu ne l'as pas précisé.
@+

AtTheOne · 17 Mars 2022

Bonsoir à toutes & à tous, bonsoir @Martin sav
Avec cette formule matricielle tu règles les cas où la somme des arrondis vaut 99, 100, 101.
Il s'agit d'une formule matricielle validée sur les 5 colonnes par CTRL+MAJ+ENTRÉE. :

Enrichi (BBcode):

=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))
)

j'ai choisi pour le cas

=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

Voir le fichier exemple ci-joint
Amicalement
Alain

AtTheOne · 18 Mars 2022

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

Martin sav · 18 Mars 2022

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

Bonjour Alain
merci pour le travail abattu amis l'arrondi commence toujours a la cellule A d'abord et lui il est arrondi selon l'arrondi normal de 0,5

AtTheOne · 18 Mars 2022

Bonjour à toutes et à tous, bonjour @Martin sav

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

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

Martin sav · 18 Mars 2022

bonjour Alain
je te remercie et je trouve que la formule de la ligne 3 du dernier fichier que tu m'a envoyer semble être le plus approprié mais je n'arrive pas à l'insérer dans les ligne I13 à O13 de ce fichier. merci pour ton appui

Martin sav · 18 Mars 2022

bonjour Alain
je te remercie et je trouve que la formule de la ligne 3 du dernier fichier que tu m'a envoyer semble être le plus approprié mais je n'arrive pas à l'insérer dans les ligne I13 à O13 de ce fichier. merci pour ton appui

Martin sav · 18 Mars 2022

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

Rebonjour,
oui la cellule A est un arrondi simple maintenant on arrondi les autre cellule pour que la somme donna 100

AtTheOne · 18 Mars 2022

Bonsoir @Martin sav
Pour ne rectifier que les arrondis des autres colonnes en laissant celui de la première, je crois que j'ai une solution par formule, par contre pour respecter l'ordre de priorité B D C E, c'est une autre affaire. Est-ce vraiment la seule solution pour toi ?
De plus ma formule ne prenait pas en compte deux possibilités, la somme des arrondis avec les 5 colonnes peut aussi être égale à 98 ou 102 !
par ex avec

86,91	3,51	0,51	0,53	8,54	100
87	4	1	1	9	102

Bref il faut que je revois ma copie, et je penche plutôt pour une fonction VBA.

Amicalement
Alain

AtTheOne · 19 Mars 2022

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)

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

Martin sav · 23 Mars 2022

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

Bonsoir

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

Bonsoir Alain,
vraiment merci pour les efforts que tu consent pour m'aider. je vois que sur le deuxième fichier tous ce que j'ai saisie marche mais lorsque ya un cas ou les décimales des cellules J à N sont égale l'ordre de priorité de l'arrondi n'est pas respecter car sa devait être d'abord la J12 ensuite, M 12, L12 et enfin N12. merci
cordialement
martin SAVADOGO

AtTheOne · 23 Mars 2022

Bonsoir à toutes & à tous, bonsoir @Martin sav
Oui Martin, il y avait un petit bug, j'utilisais la fonction Round de VBA or Microsoft dit :

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.

J'ai donc fait comme ils disent ... et je t'ai fait un classeur d'essai (ci-joint) sur 5 colonnes avec une mise en évidence des rectifications opérées, teste le est tiens moi au courant.
(Avec ce que j'ai testé ça à l'air de respecter les règles que j'ai noté sous les exemples :

La rectification des arrondis suit les règles suivantes :

si la somme des arrondis est < 100 on augmente de 1 les arrondis des plus grandes parties décimales < 0,5 en respectant en cas d'égalité la règle de priorité

si la somme des arrondis est > 100 on diminue de 1 les arrondis des plus petites parties décimales >= 0,5 en respectant en cas d'égalité la règle de priorité

Règle de priorité (ordre des colonnes modifiées) : 1, 4, 3, 5, 2 La colonne 2 ne contient pas de valeurs
(soit pour ta configuration J, M, L, N, K

Amicalement
Alain

Effectuez une recherche sur Excel Downloads...

XL 2010 aide pour formule si et arrondi

Martin sav

XLDnaute Nouveau

Pièces jointes

eddy1975

XLDnaute Occasionnel

AtTheOne

XLDnaute Impliqué

Pièces jointes

AtTheOne

XLDnaute Impliqué

Pièces jointes

Martin sav

XLDnaute Nouveau

AtTheOne

XLDnaute Impliqué

Martin sav

XLDnaute Nouveau

Martin sav

XLDnaute Nouveau

Pièces jointes

Martin sav

XLDnaute Nouveau

AtTheOne

XLDnaute Impliqué

AtTheOne

XLDnaute Impliqué

Pièces jointes

Martin sav

XLDnaute Nouveau

AtTheOne

XLDnaute Impliqué

Pièces jointes

Discussions similaires

Membres actuellement en ligne

Partager cette page

Tutos vidéos

Statistiques des forums