renvoyer valeur et lister produits par date en automatique

VBA_DEAD

XLDnaute Occasionnel
bonjour,
j`espere que vous allez bien.

Je bloque sur 2 formules


la 1ere : j`essaie de renvoyer une valeur selon 3 conditions en passant par indirect/match. je ne veux pas sumproduct (somme.produit) car les calculs sont trop lents. Je sais sortir le resultat avec somme.produit mais avec 300000+ lignes, ca n`avance pas.
j`ai fait 2 exemples ds le fichier joint. si quelqu`un a 5 minutes pour adjuster ma formula? ca marche a 2 valeurs mais a 3 parametres, je plante.

2eme question
j`ai des listes de produits par mois et j`essaie de faire un rapport automatique ou je choisis le mois et aussitot j`ai les ventes pas produits. le hic est que mon nombre de produits change chaque mois. Je sais que par excel (match / indirect et offset) on peut faire en sorte qu`excel liste les produits selon le mois souhaite (je l`ai deja vu) mais jene sais pas le faire. est ce que cela vous parle?
la donnee important est de trier les donnees par date et par formula identifier l`adresse du debut et fin de periode et excel reporte les donnees entre les 2 points. c`est le concept de memoire mais je ne sais pas comment faire.
Je ne veux pas de macro VBA...ds ce cas tres précis.

merci pour votre aide.


je vous souhaite une bonne soiree a tous
VBA_DEAD
 

Pièces jointes

  • formale indirect&match.xlsx
    12.2 KB · Affichages: 64

job75

XLDnaute Barbatruc
Re : renvoyer valeur et lister produits par date en automatique

Bonjour VBA_DEAD, le forum,

Voyez le fichier joint.

Les 2 tableaux sont définis dynamiquement par les noms P et T.

Formules matricielles en K6 K14 H26 H32 à valider par Ctrl+Maj+Entrée et tirer vers le bas.

A+
 

Pièces jointes

  • Formules matricielles(1).xls
    52 KB · Affichages: 33

job75

XLDnaute Barbatruc
Re : renvoyer valeur et lister produits par date en automatique

Re,

J'ai oublié de dire qu'il n'est absolument pas nécessaire que les tableaux P et T soient triés.

Il peut même y avoir des "trous" dans les colonnes des dates.

Par ailleurs si par exemple on tire les formules trop bas il y aura des valeurs d'erreur.

On peut facilement les éviter en utilisant la fonction SIERREUR (versions Excel 2007 et suivantes).

IFERROR en anglais.

A+
 

job75

XLDnaute Barbatruc
Re : renvoyer valeur et lister produits par date en automatique

Re,

S'il y a plusieurs années, il faut bien sûr tester l'année en H26 et H32, fichier (2).

A+
 

Pièces jointes

  • Formules matricielles(2).xls
    55 KB · Affichages: 35

Chris401

XLDnaute Accro
Re : renvoyer valeur et lister produits par date en automatique

Bonjour

@job75

Je planche également sur cette demande mais il n'existe apparemment qu'une formule matricielle.
Le problème de lenteur sera donc identique à SOMMEPROD

Cordialement
Chris
 

job75

XLDnaute Barbatruc
Re : renvoyer valeur et lister produits par date en automatique

Re, hello Chris, JM,

Si les dates sont toujours des fins de mois c'est évidemment bien plus simple, fichier (3).

A+
 

Pièces jointes

  • Formules matricielles(3).xls
    54.5 KB · Affichages: 46

job75

XLDnaute Barbatruc
Re : renvoyer valeur et lister produits par date en automatique

Re,

Le problème de lenteur sera donc identique à SOMMEPROD

Pour les formules avec EQUIV/MATCH je ne crois pas, cette fonction est très rapide et elle s'arrête à la ligne trouvée.

Pour les formules avec PETITE.VALEUR/SMALL c'est à voir, mais elle aussi devrait être plus rapide que SOMMEPROD/SUMPRODUCT.

A+
 

job75

XLDnaute Barbatruc
Re : renvoyer valeur et lister produits par date en automatique

Re,

J'ai testé les formules sur un très grand tableau :

- je suis allé sur mon portable avec Win 7 - Excel 2010

- j'ai créé dans le fichier (3) cette macro :

Code:
Sub Durée()
Dim t
t = Timer
Calculate
MsgBox Timer - t
End Sub
- j'ai enregistré le fichier en .xlsm, fermé et rouvert le fichier

- je n'ai conservé que les valeurs du tableau P

- j'ai recopié le tableau P jusqu'à la ligne 360003

- j'ai neutralisé toutes les formules matricielles sauf celle en K6

- j'ai exécuté la macro => durée d'exécution 0,10 seconde

- j'ai neutralisé la formule en K6 et activé la formule en H26

- j'ai exécuté la macro => durée d'exécution 0,11 seconde

S'il n'y a pas trop de cellules avec formules en colonnes K ou H ces durées sont acceptables.

Pas sûr que VBA puisse faire mieux.

A+
 
Dernière édition:

Staple1600

XLDnaute Barbatruc
Re : renvoyer valeur et lister produits par date en automatique

Bonjour job75 ;)


J'évoquais VBA, oui mais en pensant plutôt à la création d'une fonction VBA personnalisée.
Mais comme VBA_Dead ne veut que des formules.
J'en resterai à la simple suggestion ;)
 

job75

XLDnaute Barbatruc
Re : renvoyer valeur et lister produits par date en automatique

Re,

En réfléchissant un peu mieux, je me rends compte que le tableau réel est très grand parce qu'il y a un grand nombre de produits chaque mois, sans doute plusieurs milliers.

Dans ce cas il faudra plusieurs milliers de cellules comme H26 et toute modification du fichier prendra donc plusieurs centaines de secondes...

La solution la plus simple sera alors de mettre le fichier en mode de calcul manuel.

On appuira sur F9 quand on voudra recalculer les formules.

A+
 

VBA_DEAD

XLDnaute Occasionnel
Re : renvoyer valeur et lister produits par date en automatique

Bonsoir a tous,

desole pour me retard, je profitais du soleil!!
je conseille a tous 3 heures dehors pour prendre l`air !!
ca fait du bien!

Merci pour votre aide.
j`ai environ 50-70 produits par mois et les mois sont des fins de mois (pour le moment - ca peut changer ds quelques mois).

Je vais tester cela des demain au boulot avec les donnees que j`ai pour quelques mois.

Sinon, si c`est trop lent, je passrai au VBA_ALIVE ;)
J`ai un macro qui devrait me permettre d`extraire rapidement les infos donc au pire, je passé par cela.
J`essai de rester en excel car c`est un fichier que je partage et les gens ont parfois peur de VBA car ils aiment voir la formule. C`est psychologique. Perso j`ai rien contre VBA!!
c`est tres pratique tres souvent!!

Je vais tester cela. En fait les donnees sont sur une feuille DATA et je vais devoir lister les produits ds differentes tables donc je vais adapter la formule. Si je bloque, je vous contacterai demain.

en tout cas merci de m`avoir aide.

je pensais que vous alliez utiliser decaler pour identifier les produits c`est pour cela que j`avais compte les nombres de produits et ordonne les donnees par date et produit. Bon apparemment c`est pas la peine de trier les donnees. tant mieux!

merci a vous tous. Bonne fin de journee

vba_dead
 

VBA_DEAD

XLDnaute Occasionnel
Re : renvoyer valeur et lister produits par date en automatique

Bonsoir Job75,

j`ai 50-70 produits (le nombre est 73 maintenant et 33 ds le passé il y a 7 ans) mais sur chaque produit j`ai des sous-rubriques.
Par exemple j`ai un produit (une gamme disons comme le vin rouge pour prendre un exemple sympa) et ensuite j`ai mes sous-rubriques (bordelaise, chilie...).

Ds ma base j`ai plus de 350 000 lignes et avec somme produit, je ne m`en sortais pas. Ca plante ou je dois laisser tourner la nuit.


Merci pour votre aide. Je vais tester demain sur 100000 lignes pour voir si tout marche.
Si le calcul prend 5 min, c`est vraiment pas un souci.
si ca prends 10 min, alors je passerai peut etre par du VBA.

bonne soiree

VBA_DEAD
 

Discussions similaires