Simplifier SOMME.SI.ENS

[yannlion]

Bonjour,

Suite à de nombreuses recherches je ne trouve pas la solution à mon problème.
Après avoir changé mes formules Sommeprod (trop lent) en Somme.si.ens, je souhaiterais automatiser ces formules dans une macro car les suppressions/ajouts de ligne dans la base de donnée (qui peut monter jusqu'à 13000 lignes) transforment les formules.

Les formules que je souhaiteraient automatiser sont celles situées en colonne R, T, V, X, Z, etc jusqu'à AN puis de AP à BL de la même manière (colonnes Real).

Bon courage et merci d'avance à celui qui pourra me faire avancer dans ma réflexion

Yannlion

 

[chris]

Re : Simplifier SOMME.SI.ENS

Bonjour

Si déjà ta liste de données comportait des titres et était mise sous forme de tableau, tu n'aurais pas ce problème car, en te référant à une colonne du tableau, la hauteur serait automatiquement prise en compte.

Il serait à mon avis plus simple d'exploiter le réalisé par TCD et d'en récupérer les valeurs pour comparer au prévisionnel.

Puisque tu as 2010, on peut sans doute également utiliser Powerpivot pour réaliser toute l'analyse par TCD ce qui serait plus souple et plus lisible qu'un tableau aussi large.

 

[Misange]

Re : Simplifier SOMME.SI.ENS

Bonjour

tout à fait d'accord avec Chris, d'abord sur l'utlisation de tableaux, et sur un éventuel TCD.
J'ajouterai : une fois que le mois de janvier est terminé, si il n'y a plus à attendre de modifs sur ce mois, il faut faire un copier/coller spécial valeur, ce qui évitera à excel de recalculer des données qui ne changent pas.
L'autre solution c'est de remplir ces tableaux en VBA. Ca revient un peu au même que la solution préconisée ci-dessus : les valeurs sont en dur dans la feuille.
Question de structure de tableau, je trouve toujours dangereux d'avoir des tableaux avec deux colonnes janvier suivies de 2 février ... ca empêche d'avoir des formules qu'on tire aisément (donc bien plus complexe si on veut/doit modifier la formule...) et on mélange à la lecture les torchons et les serviettes. MAis ce n'est qu'un avis . Quand je gère ce genre de tableau, j'en fais un pour le prévu, un pour le réalisé, et un pour les écarts (plus un tableau qui reprend le réalisé pour les mois passés et le prévu pour les mois à venir).

Au fait : penser que somme.si.ens est bien plus rapide que sommeprod est une illusion dans 99.99% des cas. Ca ne se voit que sur des tableaux vraiment très très grands. Dans les deux cas on travaille sur des matrices et tous les calculs matriciels sont nécessairement très longs. Il est essentiel de travailler avec les plus petites plages possibles, donc de ne jamais réserver des lignes vides "pour plus tard", la fonctionnalité tableau étant justement là pour gérer ce pour plus tard.

 

[joss56]

Re : Simplifier SOMME.SI.ENS

Très instructif, tout ça. Moi, j'aurais tendance à préconiser palo (dont j'ai déjà parlé sur ce forum) pour ce type de problématique Outre le fait qu'il est gratuit, ce qui ne gâche rien, il permet vraiment de faire du contrôle budgétaire en affranchissant Excel de la lourde responsabilité de stocker les données et de faire les calculs sur des volumes importants voire très importants. Excel comme outil de restitution avec ses fonctionnalités graphiques intéressantes et la possibilité d'y intégrer un peu de VBA pour automatiser et personnaliser les interfaces utilisateurs et on obtient un outil professionnel.

 

[chris]

Re : Simplifier SOMME.SI.ENS

Bonjour

joss : PowerPivot est un peu dans la même logique (BI) et tout aussi gratuit. Si on a la mémoire qui va bien, on peut y traiter des millions de lignes...

Concernant le stockage on peut directement stocker dans powerpivot une extraction au format texte sans charger un onglet.

La seule question, et non des moindres, est 2013 : les consultants qui ont misé sur Powerpivot version 2010, découvrent avec effarement que Microsoft le lie aux versions pro plus pour 2013.
Vu le ramdam que cela génère, nous attendons de voir si Microsoft va modifier cette politique absurde...

 

[Misange]

Re : Simplifier SOMME.SI.ENS

Il y a même eu une manifestation de MVP au MVP summit qui vient de se tenir à Seattle !
Il y a des photos qui valent leur pesant de cahuettes : une bande 20 MVP tous avec un T shirt orange marqué "PIVOT" dessus (Bill Manville a raflé les soldes de microsoft sur ce T shirt, j'imagine qu'ils ne s'attendaient pas à cela, c'est franchement assez drôle on dirait une bande de repris de justice (la tenue des prisonniers américains étant de ce même orange).
J'espère aussi que MS changera d'attitude, c'est vraiment complètement ahurissant.

 

[joss56]

Re : Simplifier SOMME.SI.ENS

Merci pour ces infos. Effectivement PowerPivot est très intéressant aussi et très puissant. L'avantage de PALO consiste dans le fait que les utilisateurs peuvent également écrire dans les cubes de données accessibles en réseau local et/ou distant directement à partir d'Excel

 

[chris]

Re : Simplifier SOMME.SI.ENS

Bonjour

Il y a même eu une manifestation de MVP au MVP summit qui vient de se tenir à Seattle !
Il y a des photos qui valent leur pesant de cahuettes : une bande 20 MVP tous avec un T shirt orange marqué "PIVOT" dessus (Bill Manville a raflé les soldes de microsoft sur ce T shirt, j'imagine qu'ils ne s'attendaient pas à cela, c'est franchement assez drôle on dirait une bande de repris de justice (la tenue des prisonniers américains étant de ce même orange).
J'espère aussi que MS changera d'attitude, c'est vraiment complètement ahurissant.

J'avais mis le lien vers l'article de Zdnet dans ce post https://www.excel-downloads.com/threads/office-2013-les-premiers-tests.200431/.

Postez, postez, postez vos remarques sur ce fil de POWERPIVOTPRO : plus nombreux on sera...

 

[yannlion]

Re : Simplifier SOMME.SI.ENS

Bonsoir,

Tout d'abord merci pour les pistes déjà énoncées.
En fait j'ai déjà un autre onglet avec un TCD qui synthétise les donnes par mois, par poste analytique et par compte.
Le soucis est que les données de l'onglet EBP (extraction d'un logiciel comptable) ne sont pas au même format que les données prévisionnel et je les rentre donc dans un tableau général afin de les exploiter.

Mais le réel soucis est le temps d'exécution des formules qui grandit jusqu'à la fin de l'année.