Discussion :

[bettercallbob]

Bonjour à tous,

Je dispose d'une base de donnée contenant quelques centaines de lignes et sur chacune on retrouve plusieurs informations relatives à un objet (nom, date de création, origine etc) et son prix
Mon objectif est d'écrire une fonction qui en utilisant 2 critères de recherche parmi les caractéristiques des objets me donne la valeur totale des objets répondant aux critères en question par exemple les jouets produits en afrique du nord.
Au départ j'utilisais une fonction BDsomme pour faire ce calcul (et ça fonctionnait très bien en détaillant correctement les critères) le soucis est que le premier critère est en fait une liste de référence à rechercher (par exemple les pays où le taux d'utilisation de l'objet en question est supérieur à N), liste qui peut être amenée à évoluer. Mon but est donc de faire une fonction qui s'adapte à l'évolution de la liste de critère, ce que ne permet pas (à ma connaissance) la fonction BDsomme. Je précise que la fonction n'a pas besoin d'intégrer la partie "calculatoire" du premier critère qui ne sert que dans l'exemple ci dessus.
En nommant de manière dynamique (avec la fonction "décaler") certaines plages de données j'étais arrivé à une fonction qui marchait et me renvoyait bien tout ce que je souhaitais, son seul problème est qu'elle ralentit terriblement tout mon document - les quelques macros extrêmement simple que j'utilisais, et qui ne prenaient que 2-3 secondes à s'appliquer prennent maintenant une vingtaine de secondes, sûrement du fait que la fonction en question est appelée 15 fois par onglet dans 12 onglets en recherchant des valeurs différentes.
Avez vous une idée de comment optimiser cette fonction de manière à ce qu'elle ne fasse pas ramer mon classeur à ce point ? Ou alors d'autres pistes qui pourraient expliquer le ralentissement de mon document ?
Je vous mets le code de ma fonction tel qu'il est pour l'instant :
(on recherche les éléments de "listecritère1" dans "matricecrit1" et critère2 dans "matricecrit2, chacune de ces dimension appelle un nom que j'ai paramétré dans excel avec la fonction "decaler", de même que la dimension "matricevaleur" qui contient le prix de chaque objet)

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
Function sommebd(listecritère1, matricecrit1, matricevaleur, matricecrit2, critère2  As String)
 
Dim k, i As Long
For k = 1 To matricecrit1.Cells.Count
 
For i = 1 To listecritère1.Cells.Count
 
If listecritère1(i) = matricecrit1(k) Then
If matricecrit2 (k) = critère2  Then
sommebd = sommebd + matricevaleur(k)
Else
sommebd = sommebd
End If
End If
 
Next i
Next k
 
End Function

Merci d'avance pour votre aide
Bob

[Slooby]

Bonjour,

Le plus simple ne serait pas de faire un filtre sur les champs de ton choix, puis de compter les lignes visibles ?

Tu trouveras toutes tes réponses sur le lien suivant: http://excel.developpez.com/faq/?page=Filtre

Slooby

[bettercallbob]

C'est une possibilité à laquelle je n'avais pas pensé en effet, je vais jeter un oeil la dessus.

Merci beaucoup pour cette réponse.

[Promethee25]

Bonjour,

J'ai l'impression que BDSOMME ou SOMME.SI.ENS suffiraient largement à ton besoin; es-tu sûr d'avoir besoin de VBA?

Ton critère dans BDSOMME peut très bien être dynamique si tu le construis avec une formule qui pourrait pointer sur une autre case où tu stockerais N. Ex:

Code :

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

=">" & B1

Peux-tu détailler un peu plus la structure de tes données et tes critères?
Pourquoi utilises-tu la fonction DECALER? Il faut savoir que cette fonction est volatile donc chaque formule qui utilise un DECALER est recalculée à chaque modification dans Excel d'où peut-être ton temps de calcul très long.

[bettercallbob]

Merci pour cette réponse. Je ne suis en effet pas à 100% certain d'avoir besoin de VBA, toutefois l'utilisation d'un critère muni d'un comparateur comme vous le proposez risque de ne pas fonctionner dans mon cas car (contrairement à ce que mon exemple laissait entendre, je ne l'ai pas choisi au mieux j'en suis désolé) les données à considérer dans la zone de critère ne sont pas forcément liées par une relation mathématique, il s'agit en fait le plus souvent de noms a priori sans rapport les un avec les autres (ils en ont en fait un dans la logique du document mais qui ne se traduit pas par une connexion logique).
J'édite un onglet spécial contenant les différentes listes de critères, et ces dernières peuvent être amenées à évoluer, au quel cas je modifie les listes à la main, c'est la raison pour laquelle j'utilise des "décaler" pour définir les zones de critères, qui permettent que lorsque je modifie les listes la fonction s'adapte et considère bien tous les critères, y compris les nouveaux.

[Promethee25]

Il est possible d'utiliser un tableau (menu Insertion -> Tableau) plutôt que DECALER et d'utiliser un NB.SI pointant sur ce tableau dans le critère.

BDSOMME reste indiqué à mon avis (ou sinon SOMME.SI.ENS ou une formule matricielle mais si tu connais BDSOMME autant l'utiliser):

BDSOMME_exemple.xlsx

[bettercallbob]

Ah oui en effet on n'est pas loin du tout avec cette combinaison tableau/nb.si/BDsomme, je vais essayer de l'adapter à mon cas.
Merci beaucoup encore

[clementmarcotte]

Bonjour,

Merci pour cette réponse. Je ne suis en effet pas à 100% certain d'avoir besoin de VBA.

Ce dont tu as besoin ce sont de vrais outils de gestion de données à la place d'une vieille picouille à trois pattes de longueur inégales : Access et de judicieuses requêtes SQL.

[Philippe Tulliez]

Bonjour,
Dans quelle mesure as-tu besoin de la fonction DECALER.
Est-ce simplement pour rendre dynamique une plage de données pour connaître par exemple le nombre de lignes ?
Si c'est le cas, il est préférable de convertir la plage de données en tableau.
La fonction DECALER est une fonction volatile et est donc gourmande en ressource CPU (Evidemment pas si tu l'utilises dans une seule cellule). Car elle est recalculée à chaque modification d'une valeur dans une cellule quelconque, de n'importe quelle feuille de n'importe quel classeur ouvert.