Formule matricielle CTRL + MAJ + ENTREE gros challenge

pasquetp

XLDnaute Occasionnel
bonjour tout le monde


voila je bloque sur une formule matricielle

Elle marche bien mais elle est très lente pour une raison simple:

=SOMME(SI(data!A4:A1048576="";"";1/NB.SI(data!A4:A1048576;data!A4:A1048576)))

j'y ai mis des extresmes car la base de données varie et je sais pas du tout comment remplacer ces valeurs extremes par des variables.

Quelqu'un aurait une idée?

Je pense que le vba sera necessaire.

Merci de votre aide
 

R@chid

XLDnaute Barbatruc
Re : Formule matricielle CTRL + MAJ + ENTREE gros challenge

Bonjour,
On ne sait pas si la plage A1:A3 contient des valeurs ou non, faut de fichier exemple..

Dans le gestionnaire de noms, créer un champ dynamique nommé "MaPlage" :
Code:
=DECALER(data!$A$4;;;NBVAL(data!$A:$A))

Essayer d'éviter SOMME(1/NB.SI()), elle est trop lente avec des grandes plages, faut passer avec FREQUENCE() c'est plus rapide :
Code:
=NB(1/(EQUIV(MaPlage;MaPlage;0)=LIGNE(MaPlage)-3))
@ valider par Ctrl+Shift+Enter

@ + +
 

Efgé

XLDnaute Barbatruc
Re : Formule matricielle CTRL + MAJ + ENTREE gros challenge

Bonjour pasquetp, Re Patrick, Bonjour Rachid :)

Puisque la question est préfixée 2013, autant utiliser les outils de feuilles.
Mettre les données sous forme de tableau et utiliser la colonne (automatiquement délimitée) dans la formule.
Cordialement
 

R@chid

XLDnaute Barbatruc
Re : Formule matricielle CTRL + MAJ + ENTREE gros challenge

Bonjour les amis,
@ Patrick,
Je sais que FREQUENCE() ne gère pas le texte, voici la formule dont je parle :
Code:
=NB(1/FREQUENCE(SI(MaPlage<>"";EQUIV(Maplage;MaPlage;0));LIGNE(MaPlage)-3))

pour la formule,
Code:
=SOMMEPROD(1/NB.SI(MaPlage;MaPlage))
Elle est trop lente sur une plage avec des milliers de cellules, vous pouvez faire le test sur 50.000 lignes pour faire la comparaison..

@
 

Victor21

XLDnaute Barbatruc
Re : Formule matricielle CTRL + MAJ + ENTREE gros challenge

Re,

Une fonction de Michel_M à copier dans un module:

VB:
Function compter_uniques(MaPlage As Range) As Long
Set dico = CreateObject("scripting.dictionary")
For Each cellule In MaPlage
     ref = cellule.Value
     If Not dico.exists(ref) Then
          dico.Add ref, ref
     End If
Next
compter_uniques = dico.Count
End Function

10 secondes pour une zone de 400 000 nombres aléatoires
 

fosters

XLDnaute Occasionnel
Re : Formule matricielle CTRL + MAJ + ENTREE gros challenge

Bonsoir a tous,

Je me permets de m incruster dans la discussion, j’ai le même soucis que pasquetp avec des formule matricielle qui sont tres lente.
Je suis preneur de formule « plus » rapide.

Code:
=SOMME((BD!$E$2:$E$14500=Cout!$A8)*(DROITE(BD!$U$2:$U$14500;13)="Restaurant")*(BD!$G$2:$G$14500))

Code:
=MOYENNE(SI((BD!$E$2:$E$14500=$A8)*(GAUCHE(BD!$V$2:$V$14500;13)="Tarif");BD!$K$2:$K$14500;""))

Merci pour vous réponses
Bonne fin de journée
 

Misange

XLDnaute Barbatruc
Re : Formule matricielle CTRL + MAJ + ENTREE gros challenge

Bonsoir a tous,

Je me permets de m incruster dans la discussion, j’ai le même soucis que pasquetp avec des formule matricielle qui sont tres lente.
Je suis preneur de formule « plus » rapide.

Code:
=SOMME((BD!$E$2:$E$14500=Cout!$A8)*(DROITE(BD!$U$2:$U$14500;13)="Restaurant")*(BD!$G$2:$G$14500))

Code:
=MOYENNE(SI((BD!$E$2:$E$14500=$A8)*(GAUCHE(BD!$V$2:$V$14500;13)="Tarif");BD!$K$2:$K$14500;""))

Merci pour vous réponses
Bonne fin de journée
Bonjour

merci de commencer une discussion avec votre question plutot que de "squatter" la question d'un autre. Profitez en pour joindre un extrait anonymisé de votre base de donnée, de préciser votre version d'excel.
@vous lire
 

pasquetp

XLDnaute Occasionnel
Re : Formule matricielle CTRL + MAJ + ENTREE gros challenge

Bonjour tout le monde

Merci avant tout de vos réponses rapides. :)

suite au message de R@chid, je vous mets un fichier joint.

je l'ai commenté aussi clairement que possible

Merci a tous :)
 

Pièces jointes

  • Classeur1.xlsx
    28.8 KB · Affichages: 54
  • Classeur1.xlsx
    28.8 KB · Affichages: 62
  • Classeur1.xlsx
    28.8 KB · Affichages: 67
Dernière édition:

Misange

XLDnaute Barbatruc
Re : Formule matricielle CTRL + MAJ + ENTREE gros challenge

bonjour tout le monde


voila je bloque sur une formule matricielle

Elle marche bien mais elle est très lente pour une raison simple:

=SOMME(SI(data!A4:A1048576="";"";1/NB.SI(data!A4:A1048576;data!A4:A1048576)))

j'y ai mis des extresmes car la base de données varie et je sais pas du tout comment remplacer ces valeurs extremes par des variables.

Quelqu'un aurait une idée?

Je pense que le vba sera necessaire.

Merci de votre aide

Bonjour
1) mettez vos données sous forme de tableau excel (onglet accueil/style/mettre sous forme de tableau). Toutes les données que vous ajouterez sous ce tableau s'y intègreront et votre formule en tiendra compte. Inutile donc de prévoir des lignes vides ni de définir un nom dynamique avec la fonction decaler, les tableaux font ça tout seul.
2) plutot que de dire ce qui ne va pas, ce serait mieux de dire ce que tu veux faire et de joindre un extrait anonymisé de ton fichier qu'on puisse t'aider efficacement.
3) combien de lignes en pratique contiendra ta feuille (disons dans l'année à venir :) )
 

pasquetp

XLDnaute Occasionnel
Re : Formule matricielle CTRL + MAJ + ENTREE gros challenge

Cher Misange

Merci de vos idées

en effet il vaut largement mieux que je donne l'idée: grace au fichier que je viens de mettre vous pourrez tres probablement trouver une idee auquel je n'ai pas eu recours

Merci de votre aide toujours autant appreciée
 

Efgé

XLDnaute Barbatruc
Re : Formule matricielle CTRL + MAJ + ENTREE gros challenge

Bonjour à tous, Salut aux amies et Amis :)

@ fosters
Il est préférable d'utiliser les noms de tableaux... ...Misange te le montreras :D

@ Patrick:
Il n'est pas nécessaire, dans ce contexte, de mettre une condition, de plus il me semble que l'utilisation de la feuille ralentie la boucle :
VB:
Function compter_uniques(MaPlage As Range) As Long
   Dim Dico As Object, T As Variant
    Set Dico = CreateObject("scripting.dictionary")
T = MaPlage


For i = Lboun(T, 1) To UBound(T, 1)
     Dico(T(i, 1).Value) = ""
Next


compter_uniques = Dico.Count
End Function

Cordialement
 
Dernière édition:

pasquetp

XLDnaute Occasionnel
Re : Formule matricielle CTRL + MAJ + ENTREE gros challenge

le nombre de ligne peut aller de quelques dizaines a des centaines voir plusieurs milliers. impossible a prevoir

la base de donnée appartient a des clients different donc on ne peut pas prevoir a l'avance le nombre de ligne qu'il y aura
 

pasquetp

XLDnaute Occasionnel
Re : Formule matricielle CTRL + MAJ + ENTREE gros challenge

Pour résumer les commentaires du fichier:

objectif de la formule: avoir le ratio de référence en erreurs sur le nombre de référence total

j'ai deja le nombre de reference en total mais mon probleme est d'avoir le nombre de référence en erreurs

Merci a tous
 

Efgé

XLDnaute Barbatruc
Re : Formule matricielle CTRL + MAJ + ENTREE gros challenge

Re
le nombre de ligne peut aller de quelques dizaines a des centaines voir plusieurs milliers. impossible a prevoir

la base de donnée appartient a des clients different donc on ne peut pas prevoir a l'avance le nombre de ligne qu'il y aura


Encore une fois, si le demandeur disait tout sur le problème, on gagnerai du temps....



Cordialement
 
Dernière édition:

Discussions similaires

Statistiques des forums

Discussions
311 725
Messages
2 081 948
Membres
101 850
dernier inscrit
Danigra