XL 2016 Optimisation SOMMEPROD (résolu)

[Julien_B]

Bonjour à tous,

J'ai créé un plan de charge pour mes projets qui fonctionne sur deux onglets:

- 1 onglet de planification en heures/jours => Onglet "Hrs hebdo"
- 1 onglet qui me déduit une charge par mois => Onglet "Plan de charge"

Dans l'onglet "Plan de charge" je cherche à récupérer mes heures mensuelles par projet (voir lignes 10 à 48). J'arrive parfaitement à le faire via une SOMMEPROD mais le temps de calcul est lourd car je fais marcher la fonction sur environ 500 lignes (potentiellement plus par la suite puisque je n'ai pas encore entré mes projets...).

Est-il possible d’optimiser la formule en passant par une autre fonction?

Actuellement, je vais récupérer à la fois le nom du projet en colonne B (qu'il faudra saisir manuellement) et le mois concerné (ligne 1) pour faire une somme mensuelle (voir exemple sur cellule C10 de l'onglet "Plan de charge"). Je suis obligé de rechercher le nom du projet car le nombre de ligne sera variable en fonction du nombre de ressources utilisées sur chaque projet.

=SOMMEPROD(('Hrs hebdo'!$B$64:$B$500=$B10)*('Hrs hebdo'!$D$1:$NK$1=C$1);'Hrs hebdo'!$D$64:$NK$500)

Pour les absences, comme il s'agit d'une plage fixe, j'utilise un SOMME.SI, qui marche très bien mais dans le cas des projets, comme je me base sur plusieurs critères, je passe par le SOMMEPROD et c'est la le début des problèmes, car le fichier est déjà lent alors qu'il est encore vide.

Je vous joins mon fichier pour plus de clarté.

Merci par avance pour votre aide.

Julien

 

[tbft]

bonjour
sans avoir vu votre fichier...
avez-vous essayer d'utiliser la formule
=somme(si((liste1=condition1)*(liste2=condition2)*(liste3=condition3);Liste_a_sommer))
Je l'utilise très souvent.

 

[Julien_B]

Bonjour, j’ai effectivement tenté le coup, mais je n’y suis pas parvenu. Je me suis dit que cela venait du fait qu’un des critères était vérifié sur une colonne et l’autre sur une ligne, mais peut-être ai-je mal utilisé la fonction…

 

[tbft]

aie
et en transposant la ligne en colonne avec la fonction transpose ???
il faut qu'elle est le même nombre d'élément et la même orientation...

 

[Julien_B]

Au niveau des colonnes, le fichier fonctionne de novembre 2023 a decembre 2024, et au niveau des lignes, cela va varier (nombre de projets x nombre de ressources par projet) du coup pas certain que cela fonctionne

 

[tbft]

ça devient très intéressant.
mais, je ne peux pas me libérer avant demain après midi pour regarder le fichier...

 

[Julien_B]

D'accord je comprends, je regarde de mon côté en attendant

Merci!

Julien

 

[Julien_B]

Je viens de trouver une solution qui consiste à faire la manipulation en deux fois en passant par un onglet qui sera masqué. Sur cet onglet masqué, j'opère une recherche par projet mais toujours sur une base journalière. Ensuite, sur mon onglet plan de charge, j'opère un SOMME.SI qui va regrouper mes données sur base mensuelle, du coup, plus de formule matricielle .

Si toutefois tu trouve une solution plus directe, je suis preneur!

Julien

 

[tbft]

Pas de soucis

 

[tbft]

Un essai en c10
=SOMME(SI(('Hrs hebdo'!$B$64:$B$500=$B10)*('Hrs hebdo'!$D$1:$NK$1=C$1);'Hrs hebdo'!$D$64:$NK$500))

 

[Julien_B]

Hello, j’ai déjà testé et cela fonctionne à condition de valider via ctrl+maj+ entrée, ce qui revient à faire du matriciel et c’est tout aussi lent.
On va dire que la solution que j’ai noté plus haut me convient bien en passant par un onglet intermédiaire, le calcul est instantané .

 

[tbft]

Aie
Je n'avais pas regardé la version.
La version office 365 gère les formules matricielles sans avoir à les valider avec la combinaison de touche.
Je vous conseil de changer de version pour la gestion des formules trop lourdes, cela sera plus simple et surtout plus efficace.
Je peux te donner un exemple pour faire la synthèse de mes pointages, j'utilisais un tableau croisé dynamique.
Maintenant j'utilise plus que des formules matricielles (3 ou 4).
Bon par contre les formules sortent en grande partie de se forum.