Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

XL 2010 aide pour formule si et arrondi

Martin sav

XLDnaute Nouveau
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
 

Pièces jointes

  • AIDE FORMULE.xlsx
    8.8 KB · Affichages: 9

eddy1975

XLDnaute Occasionnel
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

XLDnaute Accro
Supporter XLD
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
 

Pièces jointes

  • aide pour formule si et arrondi.xlsx
    9.9 KB · Affichages: 12

AtTheOne

XLDnaute Accro
Supporter XLD
Bonne nuit

Pour faciliter la saisie de formule, j'ai créé deux noms définis :
Nom définiDé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
 

Pièces jointes

  • aide pour formule si et arrondi.xlsx
    10.3 KB · Affichages: 4
Dernière édition:

Martin sav

XLDnaute Nouveau
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
 

Martin sav

XLDnaute Nouveau
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

XLDnaute Nouveau
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
 

Pièces jointes

  • adide formule 22.xlsx
    23.5 KB · Affichages: 3

AtTheOne

XLDnaute Accro
Supporter XLD
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,913,510,510,538,54100
874119102
Bref il faut que je revois ma copie, et je penche plutôt pour une fonction VBA.

Amicalement
Alain
 

AtTheOne

XLDnaute Accro
Supporter XLD
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
 

Pièces jointes

  • Fonction pour Arrondis Rectifiées (4 cols).xlsm
    20.7 KB · Affichages: 3
  • Fonction pour Arrondis Rectifiés Germination (5 cols).xlsm
    29 KB · Affichages: 3
Dernière édition:

Martin sav

XLDnaute Nouveau
Bonsoir
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

XLDnaute Accro
Supporter XLD
Bonsoir à toutes & à tous, bonsoir @Martin sav
Oui Martin, il y avait un petit bug, j'utilisais la fonction Round de VBA or Microsoft dit :
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
 

Pièces jointes

  • Fonction pour Arrondis Rectifiées (Exemple 5 cols).xlsm
    22.4 KB · Affichages: 6
Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…