somme des valeurs d'une colonne dynamique

[favre_p]

Bonjour,

J'ai une colonne qui débute à la ligne B2 et selon le nombre que j'indique dans la cellule A1, des lignes vont s'ajouter. Ex : j'indique dans la cellule A1 le nombre "3". Du coup ma colonne s'étendra de la cellule B2 à la cellule B5. Jusqu'ici c'est bon, ca marche.
Ensuite j'aimerais qu'il apparaisse dans la cellule C2, la somme totale des chiffres indiqués dans la colonne. Donc si j'indique "3" dans la cellule "A1", la somme indiquée dans la cellule C1, doit être la somme des valeurs des cellules B2, B3, B4, B5. Autrement dit, la somme de la colonne débute toujours avec la cellule B2 et se termine à la cellule B2 + la valeur indiquée dans la cellule A1.

Le code VBA que j'ai actuellement me permet uniquement de faire une somme de la cellule B2 à une cellule prédéterminée :

Sub sum()
Range("D" & 4) = "=Sum(B2:B5)"
End Sub

Il faudrait quelque chose comme (mais je ne connais pas le format):

Sub sum()
Range("D" & 4) = "=Sum(B2:[B & 2 + value de "A1")"
End Sub

N'hésitez pas à me demander de préciser mon problème si celui-ci n'est actuellement pas clair.

Et pour faire une somme d'une ligne, c'est le même système ?

Je vous remercie d'ores et déjà pour votre aide.

Pascal

 

[JNP]

Re : somme des valeurs d'une colonne dynamique

Bonjour Favre_p et bienvenue 🙂
Ça serait plutôt

Range("D" & 4) = "=Sum(B2:B" & 2 + Range("A1") & ")"

Bonne soirée 😎

 

[JNP]

Re : somme des valeurs d'une colonne dynamique

Re 🙂,
Pour les colonnes, tu peux passer par une conversion de chiffre en lettre du style

Sub sum()
Range("D" & 4) = "=Sum(B2:" & ConversionXC(2 + Range("A1")) & "2)"
End Sub
Function ConversionXC(X)
' Conversion colonnes Excel de chiffres en lettres
    If X < 27 Then
        ConversionXC = Chr$(X + 64)
    Else
        ConversionXC = Chr$(X \ 26 + 64) & Chr$(X Mod 26 + 64)
    End If
End Function
Function ConversionCX(C)
' Conversion colonnes Excel de lettres en chiffres
    If Len(C) = 1 Then
        ConversionCX = Asc(C) - 64
    Else
        ConversionCX = (Asc(Left(C, 1)) - 64) * 26 + Asc(Right(C, 1)) - 64
    End If
End Function

par contre, attention, valable jusqu'à 2003, en 2007, il peux y avoir 3 lettres et il faudrait corriger la fonction.
Bonne soirée 😎

 

[favre_p]

Re : somme des valeurs d'une colonne dynamique

Salut JNP!

Un grand merci pour ta réponse rapide. Effectivement ta proposition marche. Pour ce qui est de ta 2ème réponse, je n'y aurai pas encore recours. Si j'ai un problème avec la solution actuelle, je l'étudierai avec un peu plus d'attention.

Merci
bonne soirée

 

[Gael]

Re : somme des valeurs d'une colonne dynamique

Bonjour Pascal, bonjour JNP,

2 autres possibilités:

En formule, avec la fonction décaler:

=SOMME(DECALER($B$2;;;$A$1))

et la même en VBA, mais dans ce cas, le VBA ne me paraît pas utile.

Range("C1").FormulaR1C1 = "=SUM(OFFSET(R2C2,,,R1C1))"

Dans cet exemple, une fois la formule saisie ou générée dans la cellule C1, la somme sera toujours fonction de la valeur en A1.

Pour une ligne, c'est le même principe, on ajoute un ";" de plus dans la formule pour définir une largeur et non plus une hauteur, ce qui donne:

=SOMME(DECALER($E$1;;;;$A$1))

ou en VBA:

Range("C2").FormulaR1C1 = "=SUM(OFFSET(R1C5,,,,R1C1))"

@+

Gael

 

[manumh@hotmail.com]

Re : somme des valeurs d'une colonne dynamique

Bonjour,
et si maintenant j'avais besoin de me déplacer dans cette colonne !
Je veux dire que, à la place de partir de $b$2, je parte de b2+K2 jusque bn+kn ?

svp, çà va faire presqu' un moi que j'y travail. ;-)

A++.

 

[Gael]

Re : somme des valeurs d'une colonne dynamique

Bonjour Manu, bonjour à tous,

Peux-tu préciser ta demande?

Le n° de ligne sera-t-il indiqué dans une autre cellule?
Doit-on faire la somme de la ligne considéré ou de toute la plage de B2 à Kn?

Envoie un exemple si possible. A priori pas de problème pour le réaliser avec une définition de plage variable.

@+

Gael

 

[favre_p]

Re : somme des valeurs d'une colonne dynamique

Merci Gael pour ton complement d'information.

A présent j'essaie de faire imprimer le document. Je rappelle que les 7 premières colonnes de ce document apparaissent par défaut. En plus de ces colonnes par défaut, un certain nombre de colonnes vont se rajouter en fonction du nombre d'exercices qu'il y a à l'examen. Exemple : Si dans la feuille "Menu", j'indique qu'il y a 10 exercices, mon tableau final se composera de 7 + 10 colonnes = 14 colonnes. Je désire imprimer ces 14 colonnes. Le nombre de colonnes à imprimer est donc dépendant du nombre d'exercices. Autrement dit, la zone à imprimer doit être flexible.

Vous remarquez qu'une colonne n'apparait pas à l'imperssion.
Le nombre de ligne va également varier sur mon tableau, mais elles doivent toutes etre imprimées et il me semble qu'il n'y a pas besoin de spécifier ceci dans la formule.

JNP, j'ai tenté d'employer ta formule pour modifier les lettres des colonnes en chiffre...pas sur que ca marche dans ce cas!!

Merci pour votre participation!


Voici la formule

Sub Imprimer()
Application.ScreenUpdating = False
Columns("A:C").Hidden = False
Columns("D").Hidden = True
Columns("E:G").Hidden = False
Columns("H:" & ConversionCX(8 + Worksheets("Menu").Range("C17")) & "8)").Hidden = False
Application.ScreenUpdating = True

End Sub

Function ConversionXC(X)
' Conversion colonnes Excel de chiffres en lettres
If X < 27 Then
ConversionXC = Chr$(X + 64)
Else
ConversionXC = Chr$(X \ 26 + 64) & Chr$(X Mod 26 + 64)
End If
End Function

Function ConversionCX(C)
' Conversion colonnes Excel de lettres en chiffres
If Len(C) = 1 Then
ConversionCX = Asc(C) - 64
Else
ConversionCX = (Asc(Left(C, 1)) - 64) * 26 + Asc(Right(C, 1)) - 64
End If

End Function

 

[favre_p]

Re : somme des valeurs d'une colonne dynamique

Au fait, c'est cette ligne qui ne marche pas! Le reste me semble ok

A savoir que Worksheets("Menu").Range("C17") indique le nombre d'exercice, donc dans cette exemple, en C17, il y a le nombre 10

Columns("H:" & ConversionCX(8 + Worksheets("Menu").Range("C17")) & "8)").Hidden = False

 

[favre_p]

Re : somme des valeurs d'une colonne dynamique

Et bien sur, j'ajoute encore ca avant le end sub :
ActiveWindow.SelectedSheets.PrintOut Copies:=1

'affichage
Application.ScreenUpdating = False
Columns("D").Hidden = False
Application.ScreenUpdating = True

 

[JNP]

Re : somme des valeurs d'une colonne dynamique

Re 🙂,

Au fait, c'est cette ligne qui ne marche pas! Le reste me semble ok

A savoir que Worksheets("Menu").Range("C17") indique le nombre d'exercice, donc dans cette exemple, en C17, il y a le nombre 10

Columns("H:" & ConversionCX(8 + Worksheets("Menu").Range("C17")) & "8)").Hidden = False

C'est un peu normal que ça marche pas, ta concaténation donne H:M8...
Bon WE 😎

 

[favre_p]

Re : somme des valeurs d'une colonne dynamique

Bonjour!

Je rencontre 2 problèmes avec mon programme pour calculer ces notes d'examen. Je vous mets le fichier en lien.

1. Dans la feuille résultat, dans la cellule E6, il y a actuellement une formule excel qui fonctionne. Elle fonctionne actuellement lorsqu'il n'y a que 5 exercices à l'examen. Elle n'est donc pas dynamique. Il faudrait donc transformer la formule excel SOMMEPROD(F6:J6;$F$4:$J$4)/$D$4) en formule VBA et dynamique. Par dynamique, je veux dire que la formule doit être débuter à Sommeprod(F6 : F + nombre d'exercie;$F$4:$J$4)/$D$4)
Dans le même genre que: Worksheets("Résultat").Range("E" & 6) = "=Sum(F6:" & ConversionXC(6 + Worksheets("Menu").Range("C17") - 1) & "6)" mais là, il s'agit d'une simple somme et ce n'est pas ce que je veux. [converstionXC transforme les lettres des colonnes en no]

De plus, il faudrait que cette formule s'inscrive dans la cellule E6 de la feuille résulat lorsqu'il y a 1 élèves, également dans la cellule E7 lorsqu'il y a 2 élèves et ainsi de suite (le "2" élèves se précise dans le feuille Menu. Il peut y avoir jusqu'à 500 élèves). La formule devrait être pour le 2ème élève SOMMEPROD(F7:J7;$F$4:$J$4)/$D$4)


2. Comment transformer la formule excel NB.SI(Résultat!$C$6:$C$1006;E10) en forumle vba ? >> $C$6 : $C$6 + nombre d'élèves qui est indiqué dans la cellule : Worksheets("Menu").Range("C15") c'est surtout NB.Si que je ne sais pas transformer en VBA.

Un grand merci
Pascal

 

[JNP]

Re : somme des valeurs d'une colonne dynamique

Salut Pascal 🙂,
Pour le point 1), il y a un petit fichier qui existe sur ton disque dur qui s'appelle VBALIST.XLS (une petite recherche, et hop 😛). Sur l'onglet Fonctions de feuille de calcul, tu auras la correspondance entre la fonction en français et en anglais. D'où SOMMEPROD devient SUMPRODUCT. Une autre solution (mais qui ne fonctionne que sur une version française d'Excel, c'est pourquoi j'évite de l'utiliser...) est d'utiliser Range("E" & 6).FormulaLocal qui va prendre la formule en langage local, donc en français. De même, pour simplifier tes concaténation, tu peux utiliser Range("E" & 6).FormulaR1C1où les référence seront prisent en fonction des décalages de colonnes et de lignes.
Pour localiser la dernière cellule, tu peux utiliser le très célèbre Range("A65536").End(xlUp).Row qui, en partant de la dernière ligne, remonte jusqu'à la dernière ligne utilisée en colonne A et .Row donne la valeur de la ligne. De même, Range("IV1").End(xlToLeft).Column te donnera la dernière colonne en ligne 1. Les seuls défauts de ces formules est sur 2007 car il y a bien plus que 65536 lignes (mais si ton prof a plus de 65000 élèves, bonjour l'angoisse 😛...) et que 255 colonnes... et d'autre part, j'ai quelquefois noté un bug (il me semble que d'autre l'on noté aussi) sur 2007 quand un effacement de cellule avait été fait, il reste parfois des trace qui arrêtent le End sur des cellules vides...
Une autre solution est d'utiliser une boucle While du type

Dim I%
I = 1
While Cells(I, 1) <> ""
I = I + 1
Wend

qui te donnera la valeur de la première case vide en I.
Pour le 2), ben, toujours VBALIST.XLS donc NB.SI devient COUNTIF.
Avec tout cela, tu as toutes les armes pour t'en sortir 😉.
Bon courage 😎

 

[favre_p]

Re : somme des valeurs d'une colonne dynamique

Hello!

J'arrive au bout là. Le programme marche, mais j'ai remarqué, que parfois, il y a une ligne de la formule qui cause problème. Avez-vous une idée qu'est-ce qu'il se passe et comment il faudrait l'améliorer ?Voici le code entier (En rouge la ligne qui cause parfois problème!) :

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'Addition des points totaux par élève (colonne G de la feuille Résulat)
Worksheets("Résultat").Range("G" & 7) = "=Sum(H7:" & ConversionXC(7 + Worksheets("Menu").Range("C17")) & "7)"

'Nombre de lignes selon le nombre d'élèves
With Worksheets("Résultat")
.Rows("7:507").EntireRow.Delete 'pour max 500 élèves
For ix = 2 To Worksheets("Menu").Range("C15").Value
.Range("6:6").Copy _
Destination:=.Cells(.Cells(.Rows.Count, 1).End(xlUp).Row + 1, 1)
.Cells(.Cells(.Rows.Count, 1).End(xlUp).Row, 1) = ix
Next ix

.Range(.Cells(3, 7), .Cells(506, 55)).Clear 'pour max 500 élèves et max 50 exercices
For ix = 2 To Worksheets("Menu").Range("C17").Value
.Range("F3:F" & .Cells(.Rows.Count, 1).End(xlUp).Row).Copy _
Destination:=.Cells(3, 5 + ix)
Next ix
End With

'Nombre d'exercices affichés sur la page Menu
Range(Cells(9, 6), Cells(59, 9)).Clear 'pour max 50 exercices
For ix = 2 To Range("C17").Value
Range(Cells(8, 6), Cells(8, 8)).Copy _
Destination:=Cells(Cells(Rows.Count, 6).End(xlUp).Row + 1, 6)
Cells(Cells(Rows.Count, 6).End(xlUp).Row, 6) = ix
Next ix

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


'Total des coefficients à l'examen dans la feuille Menu
Range("H" & 9 + Range("C17") - 1) = "=Sum(H8:H" & 8 + Range("C17") - 1 & ")"
Range("G" & 9 + Range("C17") - 1) = "Total coefficient"

'Total des coefficients à l'examen dans la cellule D4 de la feuille Résultat
Worksheets("Résultat").Range("D4") = "=Sum(Menu!H8:H" & 8 + Range("C17") - 1 & ")"


'Total des points par élève. La formule excel (non dynamique pour 5 exercice serait : =SOMMEPROD(F6:J6;$F$4:$J$4)/$D$4
For ir = 1 To Worksheets("Menu").Range("C15").Value
nbexer = Worksheets("Menu").Range("C17").Value
Worksheets("Résultat").Range("E" & 5 + ir) = "=SOMMEPROD(F"& 5+ir & " : " F" + nbexer & 5 + ir; $"F"$4:$"F"+nbexer$4)/$"D"$4"

Next ir

' Histogramme NB.SI(Résultat!$C$6:$C$6;E11)
'Worksheets("Barème").Range("H" & 10) = "=countif(Résulat!C6:C" & 6 + Range("C15") - 1 & " ; E11)"End Sub


Function ConversionXC(X)
' Conversion colonnes Excel de chiffres en lettres
If X < 27 Then
ConversionXC = Chr$(X + 64)
Else
ConversionXC = Chr$(X \ 26 + 64) & Chr$(X Mod 26 + 64)
End If
End Function

Function ConversionCX(C)
' Conversion colonnes Excel de lettres en chiffres
If Len(C) = 1 Then
ConversionCX = Asc(C) - 64
Else
ConversionCX = (Asc(Left(C, 1)) - 64) * 26 + Asc(Right(C, 1)) - 64
End If


End Function

 

[favre_p]

Re : somme des valeurs d'une colonne dynamique

Dans le code, il n'y a pas de problème de changement de ligne. C'est juste en collant le code sur ce forum, que certaines lignes sont passées à la ligne suivante.

Salutations,
Pascal