Discussion :

[dariyoosh]

Bonjour à tous,



Je voudrais poser une question concernant les forumules Excel qui peuvent être mises à jour
automatiquement quand le contenu de la plages des cellules desquelles elles dépendent change.

Voici ma situation. Pour l'instant j'ai un fichier Excel avec deux onglets "stat" et "données".
Dans l'onglet "stat" comme le nom indique j'utilise des formules prédéfinies dans Excel du genre
MAX, MIN, SOMME, MOYEN, etc. afin de faire des statistiques sur les données de l'onglet "données".

L'onglet "données" contient des données d'utilisateurs qui se connectent à un système SAP et les
statistiques que je fait consistent principalement à des informations liées aux connexions, par
exemple combien de fois un utilisateur s'est connecté au système, quelles étaient les transactions
qu'il/elle a exécutées et combien de fois, durée de chaque connexion, etc. Actuellement j'arrive à me débrouiller
avec des formules Excel pour effectuer mes calculs.

Maintenant le problème c'est que le contenu de l'onglet "Données" est rafraîchi au début de chaque
semaine et l'administrateur du système m'envoie un nouveau fichier d'extraction SAP selon lequel
je dois à nouveau faire un reporting dans l'onglet "stat". Évidemmment chaque semaine, je n'ai pas
nécessairement le même nombre de lignes (dans l'onglet "Données") que la semaine précédente car
le contenu varie selon la charge de travail des utilisateurs qui peut très bien être différente d'une
semaine à l'autre. Voici un example:

Supposons que la semaine dernière dans l'onglet "données" j'avais 23000 lignes et
dans l'onglet stat j'avais la formule suivante dans la cellule A2.

Code :

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

1
2
 
A2 = SOMME(donnees!$A$1;donnees!$A$23000)

Maintenant si cette semaine, j'ai 25000 lignes au lieu de 23000 lignes, cela revient à dire que
je dois aller manuellement changer la formule

Code :

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

1
2
 
A2 = SOMME(donnees!$A$1;donnees!$A$25000)

Vu le fait, que j'ai un nombre important de formules, cela prend beaucoup du temps. Je crois
que mon approche n'est pas bonne et je voulais vous demander de me donner votre avis pour savoir
comment automatiser le changement de mes formules en fonction du nombre des lignes dans l'onglet
données.

J'ai même défini une fonction qui renvoie le nombre des lignes dans l'onglet "données"

Code :

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

1
2
3
4
5
6
 
public Function nbrLignes()
 
	nbrLignes = Worksheets.Item("donnes").UsedRange.Rows.Count
 
End Function

Mais j'ai constaté qu'il n'était pas possible d'inclure l'appel de la fonction dans les expressions
du genre $A$nbrLignes()


Quelqu'un a une solution?



Merci d'avance,

Dariyoosh

[MarcelG]

Bonsoir dariyoosh,

Tu peux gérer un nom dont la référence serait variable

Code :

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

1
2
3
4
5
Sub Macro3()
    Dim colonne As Range
    Set colonne = Range("A1", Range("A" & Rows.Count).End(xlUp))
    ActiveWorkbook.Names.Add Name:="MACOLONNE", RefersToR1C1:=colonne
End Sub

Et dans la cellule somme sur ta feuille entrer

Code :

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

=SOMME(MACOLONNE)

Cordialement.

Marcel

[BenjGe]

sans macro à l'aide d'une utilisation astucieuse d'excel vous pouvez largement y parvenir. il faut utiliser suivant les versions la fonction DECALER, les références nommées ou les références structurées, les tableaux ou les listes.

l'idée est d'obtenir un modèle de rapport avec une feuille donnée sur laquelle il suffit de fai eun copier coller du nouveau listing. Les formules se mettent alors à jour.

Quelle est votre version d'excel? Et celle des utilisateurs qui emploieront ce classeur?

[dariyoosh]

Bonsoir dariyoosh,

Tu peux gérer un nom dont la référence serait variable

Code :

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

1
2
3
4
5
Sub Macro3()
    Dim colonne As Range
    Set colonne = Range("A1", Range("A" & Rows.Count).End(xlUp))
    ActiveWorkbook.Names.Add Name:="MACOLONNE", RefersToR1C1:=colonne
End Sub

Et dans la cellule somme sur ta feuille entrer

Code :

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

=SOMME(MACOLONNE)

Cordialement.

Marcel

Bonsoir Marcel,


Je te remercie pour cette belle solution. Je viens de faire quelques petits tests et apparemment ça a l'air bon. Voici la procédure que j'ai développé

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
 
Public Sub update()
 
    Dim colonne As Range
    Dim wksh As Worksheet
 
    Set wksh = Worksheets.Item("donnees")
 
    Set colonne = wksh.Range("A1", "A" & wksh.UsedRange.Rows.Count)
    ActiveWorkbook.Names.Add Name:="MACOLONNE", RefersToR1C1:=colonne
 
    Set wksh = Nothing
    Set colonne = Nothing
 
End Sub

Quand j'appelle cette procédure (par exemple en cliquant sur un bouton qui appel cette procédure) la mise à jour est faite automatiquement. Demain je vais continuer pour faire un test plus général et j'écrirai ici le résultat.

Merci beaucoup pour ton aide et ton attention à l'égard de mon problème.

Bonne soirée.

Cordialement,
Dariyoosh

[dariyoosh]

sans macro à l'aide d'une utilisation astucieuse d'excel vous pouvez largement y parvenir. il faut utiliser suivant les versions la fonction DECALER, les références nommées ou les références structurées, les tableaux ou les listes.

l'idée est d'obtenir un modèle de rapport avec une feuille donnée sur laquelle il suffit de fai eun copier coller du nouveau listing. Les formules se mettent alors à jour.

Quelle est votre version d'excel? Et celle des utilisateurs qui emploieront ce classeur?

Bonsoir,

Merci pour votre réponse, la version d'Excel que j'utilise est 2003.

Cordialement,
Dariyoosh