Optimisation - BDSOMME vs Somme.si.ens

[Shiriu]

Bonjour à tous,

1 - Vous trouverez ci-joint un extrait de fichier opérationnel utilisant la méthode Somme.si.ens.

2 - Mon objectif est d'optimiser le code pour le rendre le plus léger possible tous en conservant le même résultat.
Dans une autre discussion avec Tototiti2008 il a été précisé que BDSOMME était la fonction la plus fiable et la plus légère.

Ne maitrisant pas réellement cette fonction pourriez vous m'orienter sur la bonne voie.

3 - Dans le fichier :
Onglets : IRF. (Il s'agit de la base de donnée récupéré) On ne touche pas à l'onglet.

Onglets : Bal :
L'objectif de ce fichier est de combiner Période (2014/04), Numéro (1641000), Rubrique (AN,OD,CR,...), Valeur Dt, Valeur Ct.

En jaune (L1) Saisir un compte trouver en IRF. (En ligne)
En marron soit de N à AO = 1 mois (En colonne)
En rose soit de AP à XX = 2 mois etc etc

Les formules 'Somme.si.ens' se trouvent en cellule 'N1' et 'O1' et sont répété sur les cellules suivantes.

4 - Vous l'avez compris le fichier sera au final juste énorme. Et il ne s'agit que d'un onglet.
Pour faire simple uniquement dans cette onglet j'aurai.
Numéro = 700
Rubrique = 30
Période = 36
Valeur = 2

Soit un total de plus de 756 000 formules.

Shiriu

 

[gosselien]

Re : Optimisation - BDSOMME vs Somme.si.ens

Bonjour,

de fait 756 0000 formules faudra changer de PC
Tu ne peux pas te tourner vers un tableau croisé dynamique ou une macro ?

P.

 

[Shiriu]

Re : Optimisation - BDSOMME vs Somme.si.ens

Bonjour Gosselien;

Les tableaux croisé dynamique ne me conviennent pas.
Changer de PC malheureusement non.

Un macro est déjà prévu derrière mais elle ne concerne pas le traitement des données.
Il faut que le fichier reste dynamique je ne vois donc pas de macro possible.

Cordialement.

Shiriu.

 

[tototiti2008]

Re : Optimisation - BDSOMME vs Somme.si.ens

Bonjour Shiriu, Bonjour gosselien,

Pour appliquer BDSOMME il faut une base de données valide, c'est à dire un titre au-dessus de chaque colonne de la feuille IRF, et pas de colonnes vides au milieu
Chaque formule demandera de créer une zone de critère (petit tableau), ça fera beaucoup de zones de critères^^
Oui plus de 700 000 formules ça semble un peu délirant
un exemple de bdsomme dans le fichier

 

[gosselien]

Re : Optimisation - BDSOMME vs Somme.si.ens

Bonjour Gosselien;

Les tableaux croisé dynamique ne me conviennent pas.
Changer de PC malheureusement non.

Un macro est déjà prévu derrière mais elle ne concerne pas le traitement des données.
Il faut que le fichier reste dynamique je ne vois donc pas de macro possible.

Cordialement.

Shiriu.

Je n'en suis pas capable mais une macro bien écrite fera le travail c'est sur

 

[tototiti2008]

Re : Optimisation - BDSOMME vs Somme.si.ens

Re,

Je suppose que la liste en feuille IRF sera un peu plus longue également ? sa taille va beaucoup compter pour évaluer le temps de calcul
Quel humain est capable d'analyser 700 000 résultats numériques sur une feuille en même temps ?

Edit : 7000 fonctions BDSOMME sur une liste de 100 000 lignes = environ 2 minutes sur mon core i5

 

[BOISGONTIER]

Re : Optimisation - BDSOMME vs Somme.si.ens

Bonjour,

Comparaison de Sommeprod() et fonction perso matricielles

Si on modifie ligne 40.000 , les temps de recalcul sont de 11s (Sommeprod) et 0,8s (fonction perso matricielle) .

Objet dictionary
Formules Matricielles


JB

 

[tototiti2008]

Re : Optimisation - BDSOMME vs Somme.si.ens

Bonjour à tous,

Pour compléter les tests, des fichiers avec les fonctions de bases de données et les fonctions SI.ENS sur le même modèle que fourni par Boisgontier
La fonction DECALER semble légèrement déprécier les résultats de BDSOMME
Boisgontier, ta fonction matricielle est très rapide, mais ce qui m'étonne c'est qu'elle renvoie déjà les bons résultats si le mode de calcul est manuel (comme si elle se calculait avant, ce qui compliquerait la comparaison)

Edit : après différents tests, c'est bon les temps affichés pour la fonction de Boisgontier sont corrects
chez moi ça divise par 5 (environ) le temps de calcul de bdsomme ou somme.si.ens, impressionnant mais à tester sur 700 000 formules

 

[BOISGONTIER]

Re : Optimisation - BDSOMME vs Somme.si.ens

En doublant le nombre de formules: 2x200=400 --> 2x400=800

Sommeprod: 8 sec -> 12 sec
Fonction Perso: 0,8 sec -> 0,95 sec

JB

 

[Shiriu]

Re : Optimisation - BDSOMME vs Somme.si.ens

Bonjour à tous,

Que des bonnes solutions .
Je suis bluffé. Par vos programmes. Cela vas beaucoup m'aider.
En passant sur du 'SOMMEPROD' je gagne déjà 5 fois le temps de calcul.
En passant par une fonction matricielle je gagne encore dix fois sur le 'SOMMEPROD'.

Tototiti2008 : Cette feuille n'est pas réellement une feuille à analyser. Il s'agit juste d'un retraitement centralisé, ce sera donc la colonne vertébrale du fichier. Les informations voulues sera piocher dans cette feuille et le résultat apparaitra dans un autre onglet selon les besoins de l'utilisateur et les paramètres sélectionnés.

BOISGONTIER : Je n'aurai pas imaginé qu'une fonction matricielle soit aussi rapide. Je n'ai personnellement découvert l'usage de ce mode de calcul que cette année. Mais du coût tu piques ma curiosité sur ce type de calcul.

Question :
Le fichier est prévu pour perduré dans le temps. Les fonctions matricielles existe depuis quel version ?
Y a t-il un risque de voir une modification dans le temps de ce type de fonctionnalité, je pense notamment à certain code VBA qui ne sont plus utilisés ?
Il faut aussi que je vois la souplesse du changement et la mise en œuvre sur du grand volume.
La première ligne me servirait de référentiel de formule. Une macro viendrait reproduire le calcul, colonne par colonne selon le nombre de ligne renseigné.

Un grand merci à vous tous.
Il faut que je test vos solutions et je n'aurai malheureusement pas le temps aujourd'hui. Aïe je me fais déjà rappelé à l'ordre

Je m'y colle Lundi et je vous tiens au courant, j'aurais certainement quelques questions complémentaires.

Bien à vous.

Shiriu

 

[tototiti2008]

Re : Optimisation - BDSOMME vs Somme.si.ens

Bonjour Shiriu, Bonjour Boisgontier, Bonjour gosselien,

Le fichier est prévu pour perduré dans le temps. Les fonctions matricielles existe depuis quel version ?

Euh, très longtemps, mais depuis quand exactement...

Attention, les formules matricielles classiques (utilisant les fonctions natives d'Excel) sont toujours plus lentes que les formules non matricielles, SOMMEPROD est une sorte de formule matricielle masquée, tu as pu constater qu'elle a des performances moindres par rapport aux autres, mais les possibilités sont plus grandes qu'avec les fonctions classiques pour des calculs tordus.

Par contre sur une fonction programmée en VBA et utilisant des dictionnaires, on voit que ça peut changer, grâce à Boisgontier.

Y a t-il un risque de voir une modification dans le temps de ce type de fonctionnalité, je pense notamment à certain code VBA qui ne sont plus utilisés ?

Pas facile à dire... pour l'instant les dictionnaires semblent suivre, même chez Microsoft ils n'ont surement pas la réponse à cette question

Cette feuille n'est pas réellement une feuille à analyser. Il s'agit juste d'un retraitement centralisé, ce sera donc la colonne vertébrale du fichier.

Alors fait simple, des informations en ligne et colonne collées, pas de présentation élaborée

 

[tototiti2008]

Re : Optimisation - BDSOMME vs Somme.si.ens

Re,

Cette feuille n'est pas réellement une feuille à analyser. Il s'agit juste d'un retraitement centralisé, ce sera donc la colonne vertébrale du fichier. Les informations voulues sera piocher dans cette feuille et le résultat apparaitra dans un autre onglet selon les besoins de l'utilisateur et les paramètres sélectionnés.

J'ajouterais que si on ne sait pas de quel résultat de formule on aura besoin sur 700 000 résultats, alors il ne sert à rien de stocker les 700 000 résultats en même temps quelque part, il vaudra mieux calculer ceux dont on a besoin à la demande, beaucoup moins exigeant en terme de calcul.

 

[Shiriu]

Re : Optimisation - BDSOMME vs Somme.si.ens

Bonjour à tous,

Bon je crois que je suis un peu perdu. Je comprends la logique, je comprends la mécanique mais je ne parviens à aucun résultat.

Ci-joint le fichier de test.
J'ai créer une formule matricielle en cellule bleue (O19) onglet 'Bal'. Il n'y a aucun résultat.
Étant donnée que le résultat de la cellule dépend de 4 critères comment adapté la fonction matricielle...
J'ai déclarer les colonnes de données. Mais quel que soit le mode utilisé rien ne se produit.
Je n'arrive pas à inclure les variables Périodes, Numéros, Rubriques, champs de valeur.
Je m'y prend mal la logique doit être différente...

Dans le fichier de BOISGONTIER la fonction matricielle totalise les colonnes 'Service', 'Qualif', 'Salaire', 'Zone1' et 'Zone2'.
Je ne comprends pas comment insérer des éléments de saisie.

Tototiti2008 :

Attention, les formules matricielles classiques (utilisant les fonctions natives d'Excel) sont toujours plus lentes que les formules non matricielles, SOMMEPROD est une sorte de formule matricielle masquée, tu as pu constater qu'elle a des performances moindres par rapport aux autres, mais les possibilités sont plus grandes qu'avec les fonctions classiques pour des calculs tordus.

.

Tototiti2008 : Pourrais-tu m'apporter un complément d'information.
Qu'est ce qu'une fonction matricielle classique ?
Qu'elles sont les autres formules matricielles ? Comment distinguer l'une de l'autre ?
Et laquelle est conseillé ?

J'ai une macros VBA qui copie la formule en ligne 1 jusqu'à la dernière ligne de la colonne compte.
Quel la code VBA à utiliser pour que la formule devienne matricielle sur toute les cellules copiées jusqu'à la fin ?

Bonne réception.

Cordialement.

Shiriu.

 

[tototiti2008]

Re : Optimisation - BDSOMME vs Somme.si.ens

Bonjour Shiriu,

Qu'est ce qu'une fonction matricielle classique ?

Ce lien n'existe plus

Qu'elles sont les autres formules matricielles ?

ici, les 2 proposées par Boisgontier dans son classeur MatricielPersoCalcul
Elles ne sont pas "classiques" car programmées en VBA

Et laquelle est conseillé ?

en terme de performance, les matricielles "classiques" c'est SOMMEPROD, sinon tu as les fonctions personnalisées de Boisgontier
c'est toi qui voit

 

[Shiriu]

Re : Optimisation - BDSOMME vs Somme.si.ens

Bonjour à tous,

Tototiti2008 :
Merci pour ce retour rapide. Article très intéressant sur l'utilisation des formules matricielles.
Bon en conclusion les formules matricielles ne sont pas aussi rapide. Elles sont surtout l'avantages d'obtenir un résultat immédiatement sans passer par des étapes intermédiaires.
Je garde en tête cette fonctionnalité je ferai d'autres tests avec les conditions et les matricielles.

Tu peux me faire un petit retour sur l'approche de la fonction matricielle écrite en cellule bleue.

Concernant la fonction BDSOMME, je comprends mieux son usage.
Effectivement cela peut être plus simple en terme de traitement mais j'ai l'impression que cela limite et borde le traitement.
Pour faire des regroupements d'informations cela devrait très bien fonctionner. Étant donné qu'il faut toujours une entête de colonne.
Je vais approfondir les possibilités du BDSOMME.

Dans le cas de mon fichier mon entête est juste un titre d'information. Les données en dessous sont individuelles.
Je ne vois donc pas d'optimisation possible sur la formule 'Somme.si.ens'.
Je vais trouver un moyen pour réduire les informations en colonne, réduction que je ne pas faire en ligne.
Malheureusement il faut que je stocke mes 70 000 lignes c'est le 1er contrôle d'anomalie.

Bien à vous.

Shiriu.