Fonction Excel adequate

Version imprimable

12/08/2013, 16h17
bricko

Fonction Excel adequate

Bonjour à la communauté;
Je suis à la recherche d'une fonction adéquate pour mon problème.
En effet, j'ai 3 colonnes.
La première contient une série de 0 et de 1.
La deuxième contient une série de valeurs en zlotis.
La 3ème colonne est dédiée à accueillir mon calcul.

J'aimerais faire une fonction qui quand elle rencontre un 1 dans la 1ère colonne, fasse la somme des précédents nombre se trouvant dans la 2èmes colonne, elle inscrit cette dernière dans la 3ème colonne. Si il y a un 1 dans la 1ère colonne et qu'il n' ya aucune valeur dans la 2ème colonne, elle inscrit "not in contract" dans la 3ème colonne .

Si elle rencontre un 0, elle laisse un blanc dans la 3ème colonne, même si il ya une valeur dans la 2ème colonne.

La difficulté de cette exercice réside dans le fait de faire la somme des nombres et d'inscrire la somme dans la 3ème colonne.

Merci pour votre aide.

En pièce jointe vous trouverez un fichier pour illustrer mon propos.
Par exemple, je dois retrouver 417.52 zlotis pour la ligne 6 de la colonne C
12/08/2013, 16h43
bricko
Une première solution serait de faire ainsi:
Code:

1 2 =si(A2=0;"";si(B2="";Not in contract";B2))
Mais là je fais en quelque sorte une copie de la colonne B. J'aimerais faire la somme des nombres précédents.

En clair, à chaque fois que je rencontre un 1; je recopie le nombre correspondant plus la somme des nombres précédents.
Quand je rencontre un 1 et que dans la colonne B , il y a aucun nombre, je laisse la chaîne de caractère Not in contract.
Quand je rencontre un 0, je laisse un blanc.

Merci pour votre aide.
12/08/2013, 17h30
Philippe Tulliez

Bonjour,
Une solution parmi d'autres

Code:

=SI(ET(A2=1;B2=0);"Not in contract";A2*SOMME($B$2:B2))

Si 1 en A et valeur en B fait la somme des cellules qui précèdent.
Si 1 en A et valeur 0 en B - Affiche "Not in contract"
Si 0 en A et valeur en B - Affiche 0
Pour faire en sorte que la valeur nulle ne s'affiche pas, il y a trois solutions.
1 - Modifier l'option d'excel pour ne pas afficher les valeurs nulles dans la feuille.
2 - Appliquer une mise forme conditionnelle sur les cellules de la colonne C.
3 - Placer un format personnalisé sur les cellules de la colonne C.
12/08/2013, 18h15
bricko

Merci Philippe, pour votre réponse.
Mais je voudrais en fait que la somme s'arrête au niveau du premier 1 et reprenne par après.

Je m'explique si pour la cellule 6 de la colonne C , j'ai 417,52 zlotis;
pour la cellule 20, j'ai 153,56 zlotis.

En fait la somme n'est pas linéaire. Une fois que j'ai fais une somme, le compteur est remis à zéro, et je reprends la somme quand je rencontre un autre 1 qui a bien évidement une valeur dans la colonne B.

Je ne sais pas si je me suis bien fais comprendre.
12/08/2013, 19h41
parmi

Bonjour,

Cherche-tu une fonction Excel ou une macro pourrait faire l'affaire ?
12/08/2013, 20h48
bricko
Je cherche plutot une macro j'ai peur qu'une fonction Excel ne fasse pas l'affaire.
ça pourrait être un si monstrueux

Un internaute m'a déjà suggéré cette formule
Code:

1 2 SI(ET(A2=1;B2=0);"Not in contract";A2*SOMME($B$2:B2))
Sauf que je vais que le calcul se reinitialise à chaque fois que je rencontre un 1.
Bien évidement le calcul se déclenche quand il y a un nombre dans la cellule B correspondante.

Merci de votre aide

Bonsoir à la communauté;
Je suis un peu à la masse, je cherche à automatiser mon calcul dans une colonne donnée.

Quelqu'un pourrait m'aider je suis un peu perdu.

Bonsoir,

Essaie :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Function SansNom() As Variant
    Dim Ligne, Col, Ctr As Byte
    Application.Volatile
    Ligne = Evaluate("Row()")
    Col = Evaluate("column()")
    If Cells(Ligne(1), Col(1) - 2) = 1 And _
        Cells(Ligne(1), Col(1) - 1) = "" Then
        SansNom = "not in contract"
    ElseIf Cells(Ligne(1), Col(1) - 2) = 1 Then
 
        Set c = Cells(Ligne(1), Col(1) - 2)
        Ctr = 0
        For i = Ligne(1) To 1 Step -1
            If Cells(i, 1) = 1 Then Ctr = Ctr + 1
            If Ctr = 2 Or i = 1 Then
                Exit For
            End If
            SansNom = SansNom + Cells(i, 2)
        Next i
    End If
End Function

12/08/2013, 21h45
bricko

Je vous remercie beaucoup pour le coup de main.
Mais tout cela semble compliqué sans des petits commentaires.
Je vais essayer et je reviens vers vous.

Bonjour,
Je viens d'essayer la macro dans un module standard mais il l'air de ne pas bien marcher.
J'ai une erreur récurrent comme quoi la variable c n'est pas définie.

J'ai Option Explicit par défaut dans mon éditeur de macro.
12/08/2013, 22h23
Daniel.C
Bon, tu définis c, c'est pas un problème. Ca ne peux pas fonctionner en tant que macro dans un module standard. Pourquoi est-ce que tu testes le code dans une macro ?
Code:

1 2 Ligne = Evaluate("Row()") Col = Evaluate("column()")
ces deux variables récupèrent l'adresse de la cellule contenant la fonction, alors, dans un module standard, ça correspond à rien. On te donne une fonction, pourquoi est-ce que tu n'essaies pas la fonction ? Ca fonctionne ou ça ne fonctionne pas. Tu le voies tout de suite.

Fais comme tu veux, mais, personnellement, je ne mets l'option Explicit que lorsque je passe en prod. Tant que c'est en test, je veux être libre de modifier mes variables sans être enquiquiné.
12/08/2013, 22h24
Patrice740

1 pièce(s) jointe(s)

Bonsoir,

Un ersatz de solution sans macro qui donne le résultat visuellement (les valeurs de la colonne C en face d'un 0 en colonne A sont masquées par une MFC).

Patrice
12/08/2013, 22h43
bricko

Bonjour à tous,
Les solutions proposées semblent toutes bien marchées. Je vous remercie pour votre sollicitude.

juste une dernière question la macro je dois la mettre dans la feuille test alors Daniel

Bonsoir ,
en fait ce que je n'arrive pas à comprendre c'est comment tester votre macro sans la mettre dans un module.
13/08/2013, 09h23
mayekeul
bonjour,

Si j'ai bien tout compris.
Et pour reprendre l'idée de la formule.
On pourrais simplement inscrire dans la cellule C2
Code:

1 2 =IF(A2;IF(ISBLANK(B2);"Not in Contract";SUM($B$2:B2));"")
et ensuite la tirer vers le bas

non?
13/08/2013, 09h45
Daniel.C

Bonjour,

Bricko :

Tu mets la fonction dans le module standard et, dans la feuille Excel, tu écris :

Code:

=SansNom()

en colonne C
PS. Désolé pour le nom de la fonction, je n'ai rien trouvé de causant. Tu peux le changer dans le code.
13/08/2013, 10h08
Philippe Tulliez

Bonjour,

Citation:

En fait la somme n'est pas linéaire. Une fois que j'ai fais une somme, le compteur est remis à zéro, et je reprends la somme quand je rencontre un autre 1 qui a bien évidement une valeur dans la colonne B.

Pour compléter ma première proposition, à placer à partir de la ligne 3 (si la première ligne des données est en ligne 2).

Code:

=SI(ET(A3=1;B3=0);"Not in contract";A3*SOMME($B$2:B3))-(A3*SOMME($C$2:C2))

Il y a sûrement une meilleure solution qui pourrait débuter en ligne 2
14/08/2013, 08h27
mayekeul

Bonjour (plein de soleil ici :) )

Oups, j'avais zappé cette partie

Citation:

En fait la somme n'est pas linéaire. Une fois que j'ai fais une somme, le compteur est remis à zéro, et je reprends la somme quand je rencontre un autre 1 qui a bien évidement une valeur dans la colonne B.

alors effectivement, en partant de la deuxieme ligne , on pourrait faire quelque chose ressemblant à ceci je pense

Code:

=IF(A2;IF(ISBLANK(B2);"Not in Contract";SUM($B$2:B2)-SUM($C$1:C1));"")

Ta fonction fonctionne bien. Seulement pour la recopier sur les 24000 lignes, dans mon environnement, cela prend 6m 30 sec (contre 3,50 sec avec la fonction VBA. Par contre, c'est plus rapide si on passe en calcul manuel le temps de la recopie Je reposte la mienne, modifiée pour tenir compte du calcul quand A=1 et B="" :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Function SansNom() As Variant
    Dim Ligne, Col, Ctr As Byte
    Application.Volatile
    Ligne = Evaluate("Row()")
    Col = Evaluate("column()")
    If Cells(Ligne(1), Col(1) - 2) = 1 And _
        Cells(Ligne(1), Col(1) - 1) = "" Then
        SansNom = "not in contract"
    ElseIf Cells(Ligne(1), Col(1) - 2) = 1 Then
        Set c = Cells(Ligne(1), Col(1) - 2)
        Ctr = 0
        For i = Ligne(1) To 1 Step -1
            If (Cells(i, Col(1) - 2) = 1 And Cells(i, Col(1) - 1) <> "" And Ligne(1) <> i) Or i = 1 Then
                Exit For
            End If
            SansNom = SansNom + Cells(i, Col(1) - 1)
        Next i
    End If
    If SansNom = 0 Then SansNom = ""
End Function

Voici le code ayant servi à tester :

Code:

1
2
3
4
5
6
7
8
Sub test2()
    deb = Timer
    'Application.Calculation = xlCalculationManual
    Set plage = Range([A2], Cells(Rows.Count, 1).End(xlUp)).Offset(, 2)
        [C2].AutoFill plage
    'Application.Calculation = xlCalculationAutomatic
    MsgBox Timer - deb
End Sub