Discussion :

[bricko]

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

[bricko]

Une première solution serait de faire ainsi:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

[Philippe Tulliez]

Bonjour,
Une solution parmi d'autres

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[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.

[parmi]

Bonjour,

Cherche-tu une fonction Excel ou une macro pourrait faire l'affaire ?

[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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

[Daniel.C]

Bonsoir,

Essaie :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[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.

[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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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é.

[Patrice740]

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

[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.

[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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

et ensuite la tirer vers le bas

non?

[Daniel.C]

Bonjour,

Bricko :

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[Philippe Tulliez]

Bonjour,

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

=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

[mayekeul]

Bonjour (plein de soleil ici )

Oups, j'avais zappé cette partie

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[Daniel.C]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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