Optimisation fonction SOMME PROD

Bonjour à tous,

nouveau sur ce forum, je m'adresse à vous afin d'obtenir conseil concernant une formule que j'exploite sous Excel 2010 FR, celle-ci est basée sur la fonction SOMME PROD afin de réaliser une opération de calcul sur du multicritères.

L'objectif de la formule est de déterminer le nombre d'emplois par commune et par codes NCE (variante NAF), les critères sont basés sur les codes NAF issus de Pole Emploi, pour cela je réalise le calcul suivant :

En bleu : le premier critère de calcul qui concerne les codes communes

en noir : les critères de codes NAF (il s'agit d'une plage de critères) sous la forme 1051Z ; 1112A; 1080Z etc..... (mélange de chiffres et lettres)

en vert : le calcul du nombre d'emplois

=SOMMEPROD(('données PE brut communal'!C$2:C$100000=Q3804)*((('données PE brut communal'!E$2:E$100000='données NCE NAF industrie'!G$2)+('données PE brut communal'!E$2:E$100000='données NCE NAF industrie'!G$3)+('données PE brut communal'!E$2:E$100000='données NCE NAF industrie'!G$4)+('données PE brut communal'!E$2:E$100000='données NCE NAF industrie'!G$5)+('données PE brut communal'!E$2:E$100000='données NCE NAF industrie'!G$6)+('données PE brut communal'!E$2:E$100000='données NCE NAF industrie'!G$7)+('données PE brut communal'!E$2:E$100000='données NCE NAF industrie'!G$8)+('données PE brut communal'!E$2:E$100000='données NCE NAF industrie'!G$9)+('données PE brut communal'!E$2:E$100000='données NCE NAF industrie'!G$10)+('données PE brut communal'!E$2:E$100000='données NCE NAF industrie'!G$11)+('données PE brut communal'!E$2:E$100000='données NCE NAF industrie'!G$12)+('données PE brut communal'!E$2:E$100000='données NCE NAF industrie'!G$13)+('données PE brut communal'!E$2:E$100000='données NCE NAF industrie'!G$14)+('données PE brut communal'!E$2:E$100000='données NCE NAF industrie'!G$15)+('données PE brut communal'!E$2:E$100000='données NCE NAF industrie'!G$16)+('données PE brut communal'!E$2:E$100000='données NCE NAF industrie'!G$17)+('données PE brut communal'!E$2:E$100000='données NCE NAF industrie'!G$18)+('données PE brut communal'!E$2:E$100000='données NCE NAF industrie'!G$19))*('données PE brut communal'!G$2:G$100000)))

Cette formule est un peu "énorme" et requiert beaucoup de puissance processeur pour être calculée. Je souhaiterais si cela est possible éviter la répétition de formules affichée ici en noir, ici la plage va de G2 à G19 dans une même colonne excel, mais je n'ai rien trouvé permettant de réduire les 19 répétitions à une simple formule.

En vous remerciant par avance pour l'aide que vous pourriez m'apporter.

Bonjour,

Un petit bout de fichier exemple aurait le bienvenu pour bien comprendre et tester la formule.

Avec un peu de chance, peut-être ceci :

=SOMMEPROD(('données PE brut communal'!C$2:C$100000=Q3804)*(ESTNUM(EQUIV('données PE brut communal'!$E$2:$E$100000;'données NCE NAF industrie'!$G$2:$G$19;0))*'données PE brut communal'!$G$2:$G$100000))

Sans garantie.

Quand au temps de calcul, sur une telle plage de cellules, il est clair qu'il ne faut pas s'attendre à des miracles.

Les 100.000 lignes sont elles toutes remplies ? Sinon, on peut envisager de nommer de façon dynamique les plages de données avec la fonction DECALER.

@+

Merci beaucoup pour ce code, il fonctionne parfaitement !

en fait je dispose d'un fichier pole emploi avec les codes communes d'Ile de France (1300 communes environ), avec les codes NAF (732 codes NAF), le croisement de ces 2 bases me donne environ 75 000 lignes. Je place le calcul à 100 000 afin d'anticiper une augmentation des emplois et/ou codes NAF à l'avenir, car le fichier de calcul me servira de template afin de calculer automatiquement des valeurs à partir des fichiers brut pole emploi, sans avoir à refaire des extraction et évaluations chaque années.

voici tout de meme un bout de fichier qui devrait vous éclairer sur les calculs.

re,

en nommant de façon dynamique avec la fonction DECALER, ça offre des avantages :

  • meilleure lisibilité et compréhension de la formule du fait de l'utilisation du nom
  • meilleur temps de calcul.

La formule en E2 devient :

=SOMMEPROD((Code_commune=C2)*(ESTNUM(EQUIV(Code_Activité;'données NCE NAF industrie'!$G$2:$G$19;0)))*Total_Emplois)

Voir le fichier joint où tu pourras regarder comment ont été nommés les différentes plages.

@+

84petar91.xlsx (185.99 Ko)

Merci pour ces astuces, cependant pour la fonction DECALER, attribuer un nom à une plage de données en utilisant la "zone nom" revient-il au même ?

Bonjour,

L'intérêt d'utiliser la fonction DECALER est d'avoir des plages de cellules nommées adaptées en taille quelle que soit le nombre de données.

Si tu nommes en "dur" et que le nombre de données excède la taille définie, tu ne t'en apercevras pas.

Autre avantage, c'est en terme de temps de calcul : la taille de la plage est limitée au nécessaire, pas plus pas moins.

@+

Bonjour,

je re-up ce topic, j'ai appliqué les recommandations qui ont été citées dans ce fil de discussion. Cependant une fois prêt à engager le calcul du classeur excel, vu le nombre de lignes et de critères à considérer, le calcul prend plusieurs heures.

Savez vous s'il existe un moyen d'optimiser encore ma requête Somme prod, voir à changer de formule de calcul si nécessaire afin de raccourcir le temps de calcul ?

Merci par avance pour les réponses que vous pourriez m'apporter.

Bonjour,

Il ne faut pas attendre de miracle. Traiter des bases de données de 100.000 lignes sur Excel n'est pas chose fréquente et il faut peut-être voir du côté d'Access.

En ajoutant une colonne dans l'onglet données PE brut communal en E2 :

=NB.SI('données NCE NAF industrie'!$G$2:$G$19;'données PE brut communal'!$C2)

à recopier vers le bas

J'ai défini un nouveau nom qui porte sur cette colonne :

Code_test

Fait référence à :

=DECALER('données PE brut communal'!$E$1;1;;NBVAL('données PE brut communal'!$A:$A)-1)

Ensuite, sur l'onglet Calcul emploi par communes, la formule devient :

=SOMMEPROD((Code_commune=C2)*Code_test*Total_Emplois)

à recopier vers le bas

Il restera à voir pour gérer les différentes catégories de codes NAF.

Voir le fichier joint.

Sans garantie toutefois pour le temps de calcul.

@+

32petar91-2.zip (149.48 Ko)
Rechercher des sujets similaires à "optimisation fonction somme prod"