Réduire une formule volumineuse

[pascal82]

Bonjour,

J'essaye de réduire une formule volumineuse qui me permet de rechercher le nombre d'occurrence d'un groupe de valeurs binaires (AK2:AQ5 + AN2) dans une base de données (A2:S100).

Pour cela j'utilise la formule recopiée dans les cellules V2:AH100, cette formule fonctionne mais mais alourdi considérablement le fichier de travail.

=SI(ET(D1=$AN$1;A2=$AK$2;B2=$AL$2;C2=$AM$2;D2=$AN$2;E2=$AO$2;F2=$AP$2;G2=$AQ$2;A3=$AK$3;B3=$AL$3;C3=$AM$3;D3=$AN$3;E3=$AO$3;F3=$AP$3;G3=$AQ$3;A4=$AK$4;B4=$AL$4;C4=$AM$4;D4=$AN$4;E4=$AO$4;F4=$AP$4;G4=$AQ$4;B5=$AL$5;C5=$AM$5;E5=$AO$5;F5=$AP$5;$AN$5=D5;$AK$5=A5;G5=$AQ$5);1;"")

Si quelqu'un avait une petite idée ce serait génial

Merci par avance

 

[JCGL]

Re : Réduire une formule volumineuse

Bonjour à tous,

Il y a t'il une logique dans le choix des cellules en A1:S99 ?
Je n'en vois pas donc il me semble difficile de réduire ta formule.

A + à tous

 

[pascal82]

Re : Réduire une formule volumineuse

Bonsoir,

Tu as parfaitement raison JCGL, il n'y a aucune logique dans la base de données (à ma connaissance), cependant c'est l'exercice que ma hiérarchie m' impose (Pffff …)
En fait la dimension réelle de la base de données est (A2:AT345650), donc beaucoup de formules.
Ces formules alourdissent considérablement le fichier et rallongent le temps de traitement car j'ai une petite macro qui me permet de modifier les critères de recherches

Cordialement

 

[david84]

Re : Réduire une formule volumineuse

Bonsoir, salut Jean-claude,
à tester d'après ce que j'en ai compris (validation matricielle par Ctrl, maj et entrée) :

=SI(ET(D1=$AM$1;SOMME(N(DECALER(A2;;;4;7)=DECALER($AJ$2;;;4;7)))=28);1;"")

A+
Edit : ci-joint fichier + formule actualisée (c'est toujours la même mais copiée à partir de la 1ère cellule validée, à savoir U2 )

 

[JCGL]

Re : Réduire une formule volumineuse

Bonjour à tous,
Salut David,

Tu poses bien ta "courte" formule en V2 et à droite et en bas ?

Les résultats ne sont plus identiques à ceux de notre ami... Mais si cela convient, je suis bluffé...

J'attends un peu avant de tresser les lauriers...

A++ l'ami
A + à tous

 

[david84]

Re : Réduire une formule volumineuse

Re
regarde le fichier joint rajouté dans mon message précédent.
A+ l'ami

 

[JCGL]

Re : Réduire une formule volumineuse

Bonjour à tous,

Je comprends mieux avec :

=SI(ET(D1=$AM$1;SOMME(N(DECALER(A2;;;4;7)=DECALER($AJ$2;;;4;7)))=28);1;"")

Je suis, néanmoins, bluffé...

Bravo l'ami, de la belle ouvrage...

A++ et Merci pour la leçon (il fallait voir le décalage)
A + à tous

 

[pascal82]

Re : Réduire une formule volumineuse

Bonsoir,

A première vue les résultats sont similaires. Je réalise les tests et vous réponds demain.
Nota: pas certain d'avoir tout compris dans la formule!!!

Encore un grand bravo

Cordialement

 

[pascal82]

Re : Réduire une formule volumineuse

Bonsoir,

J'ai terminé les tests selon la proposition de "david".
Points positifs:
- Résultats identiques
- Réduction très importante de la formule initiale.
ce qu'il l'est moins:
- Prise de poids du fichier de traitement voisin de 30%
- Augmentation très importante du temps de traitement
Conclusion: l'objectif lié à la question initiale (Le titre) est atteint et encore Bravo avec un grand merci, par contre je ne peux décemment pas l'utiliser.

Très cordialement

 

[david84]

Re : Réduire une formule volumineuse

Re
il ne te reste donc plus qu'à continuer à utiliser ta formule initiale ou à passer par du VBA.
A+

 

[pascal82]

Re : Réduire une formule volumineuse

Bonjour,

Désolé pour le retard.
Je tente de passer la formule de "david84" en VBA pour cela j'utilise l'instruction FormulaArray.
J'ai donc passé les instructions en anglais et remplacé les ";" par ","

Range("AR2:BD99").FormulaArray = "=IF(AND(D1=$AM$1,SUM(N(OFFSET(A2,,,4,7)=OFFSET($AJ$2,,,4,7)))=28),1,"""")"

La formule ne fonctionne pas car "D1" et "A2" sont considérées en référence absolue alors que ce n'est pas le cas.

Si quelqu'un pouvait m'expliquer

Merci par avance

 

[david84]

Re : Réduire une formule volumineuse

Bonsoir,
un essai via sub VBA dans la feuille VBA (on peut également adapter en fonction mais je n'en vois pas l'intérêt) :

Dim Plage As Range, PlComp As Range, CellComp As Range, i&, j&, test
With Worksheets("VBA")
Set Plage = .[A1].CurrentRegion
Set PlComp = .[PlageComp]
Dim tabl()
ReDim Preserve tabl(1 To Plage.Rows.Count, 1 To Plage.Columns.Count)
Set CellComp = .[CelluleComp]
For i = 1 To Plage.Rows.Count
    For j = 1 To Plage.Columns.Count
        If Plage(i, j + 3) = CellComp Then
            test = Evaluate("SUM(N(OFFSET(" & Plage(i + 1, j).Address & ",,,4,7)=" & PlComp.Address & "))")
            If test = 28 Then tabl(i + 1, j) = 1
        End If
    Next j
Next i
[U1].Resize(Plage.Rows.Count, Plage.Columns.Count).ClearContents
[U1].Resize(Plage.Rows.Count, Plage.Columns.Count) = tabl
End With
End Sub

A+

 

[pascal82]

Re : Réduire une formule volumineuse

Bonjour,

Un grand merci pour la qualité de ta réponse, du grand art.
J'ai galéré toute la matinée pour comprendre et adapter ton code sur mon fichier de travail. Puis enfin j'ai réalisé quelques tests pour comparer les résultats.
Le volume du fichier de travail est divisé par 3 et c'est déjà un exploit. Par contre le temps de traitement est un plus long probablement par maladresse de ma part sur d'autres boucles, mais je m'en contenterais.

Encore un grand merci à "david"

Cordialement