Remplacer mes sommeprod par recherche v ou index multi critères ou..(pour alléger)

dindondesbois

XLDnaute Nouveau
Bonjour à tous !

1ère fois que je poste sur ce forum, que je consulte pas mal récemment ! Très utile, merci aux experts des formules et du vba!

Je n'ai pas réussi à trouver de sujets correspondants exactement à ma demande donc je me lance !

Voilà j'ai un fichier avec 2 onglets : un onglet Bases ou l'utilisateur à la possibilité de définir des bases de chiffre d'affaire, en affichant VRAI ou FAUX pour savoir si les ventes de véhicules selon leur axe (poids lourds ou véhicules légers) et leur marque(marque 1 à ...marque 15) font partie de ces bases.

En gros dans cet onglet, je vais dire si les poids lourds appartenant à telle marque vont figurer dans la base 1 ou/et dans la base 2 etc...

Dans le 2ème onglet, "Données", j'ai de grandes listes de chiffres, et sur chacune des lignes, 3 colonnes base 1, base 2, base 3. Je veux aller chercher dans la feuille Bases si cette ligne selon ses infos d'axe et de marque, sont dans la base 1 et/ou base et / ou 3.

C'est le pitch de départ, j'arrive très bien à gérer facilement avec les sommeprod (comme dans l'exemple de fichier attaché), mais dans mon fichier réel j'ai quelques 6000 lignes et 6 bases de chiffres, et sommeprod c'est un peu lourd, j'ai tenté de faire la même chose avec des recherche v et index equiv, mais j'ai des problèmes avec la marque notamment (si une même marque fait et des poids lourds et des véh légers), ca ne me ressort pas les bonnes valeurs ou des erreurs.

=SI(SOMMEPROD((H2=Axe)*(Données!I2=Marque)*Base1)=1;VRAI)

en gros je veux la même chose sans sommeprod, quelqu'un aurait une idée? je pense que l'erreur vient du fait que j'ai plusieurs même marque pour plusieurs axe, peut être un problème qui peut être résolu avec une formule en matriciel, mais je ne vois pas j'ai essayé pas mal de trucs. :confused:

Merci d'avance ! :)

Dindon des bois
 

Pièces jointes

  • exemple.xlsx
    12.8 KB · Affichages: 87
  • exemple.xlsx
    12.8 KB · Affichages: 85
  • exemple.xlsx
    12.8 KB · Affichages: 84

Tibo

XLDnaute Barbatruc
Re : Remplacer mes sommeprod par recherche v ou index multi critères ou..(pour allége

Bonjour,

SOMMEPROD est une fonction matricielle qui ne nécessite pas de validation matricielle (CTRL + MAJ + ENTREE)

Il n'est pas sûr que remplacer cette formule par une autre formule matricielle fasse gagner du temps.

Une piste peut-être avec la fonction DECALER :

en E2 :

Code:
=INDEX(DECALER(Bases!$A$1;EQUIV(Données!$H2;Bases!$A:$A;0)-1;COLONNES($A:B);NB.SI(Bases!$A:$A;Données!$H2));EQUIV($I2;DECALER(Bases!$A$1;EQUIV(Données!$H2;Bases!$A:$A;0)-1;1;NB.SI(Bases!$A:$A;Données!$H2));0))

à recopier vers le bas et vers la droite

Je te laisse voir si ça fait gagner du temps.

@+
 

BOISGONTIER

XLDnaute Barbatruc
Repose en paix
Re : Remplacer mes sommeprod par recherche v ou index multi critères ou..(pour allége

Bonjour,

Organiser les données autrement

JB
 

Pièces jointes

  • Organis.xls
    34 KB · Affichages: 79
  • Organis2.xls
    35 KB · Affichages: 74
Dernière édition:

dindondesbois

XLDnaute Nouveau
Re : Remplacer mes sommeprod par recherche v ou index multi critères ou..(pour allége

Bonjour,

SOMMEPROD est une fonction matricielle qui ne nécessite pas de validation matricielle (CTRL + MAJ + ENTREE)

Il n'est pas sûr que remplacer cette formule par une autre formule matricielle fasse gagner du temps.

Une piste peut-être avec la fonction DECALER :

en E2 :

Code:
=INDEX(DECALER(Bases!$A$1;EQUIV(Données!$H2;Bases!$A:$A;0)-1;COLONNES($A:B);NB.SI(Bases!$A:$A;Données!$H2));EQUIV($I2;DECALER(Bases!$A$1;EQUIV(Données!$H2;Bases!$A:$A;0)-1;1;NB.SI(Bases!$A:$A;Données!$H2));0))

à recopier vers le bas et vers la droite

Je te laisse voir si ça fait gagner du temps.

@+

Merci Tibo je viens de tester, ca fonctionne effectivement bien mais malheuresement mon fichier global est plus lent avec cette formule qu'avec les sommeprod (j'en ai en gros 25000).

Je vais tester de ce pas la fonction substitue de Boisgontier sur mon fichier et je vais voir comment adapter un nb.si.ens par la suite.
 

dindondesbois

XLDnaute Nouveau
Re : Remplacer mes sommeprod par recherche v ou index multi critères ou..(pour allége

Même chose pour les plages nommées et l'index (equiv (substitue , les sommeprod restent plus rapide tant qu'eux aussi sont limités par des plages nommées.

J'ai testé avec un =SI(NB.SI.ENS(AXE;$H2;MARQUE;$I2;DEFBASE1;VRAI)=1;VRAI)
Axe = Bases A2:A28
Marque = Bases B2:B28
DEFBASE1 = Bases C2:C28
DEFBASE2 = "" avec D
...

Bref c'est quasiment comme les sommeprod niveau rapidité, si je modifie quelque chose dans mon fichier j'ai un recalcul qui fait s'afficher le % de progression du processeur (juste un affichage du chargement pendant 1 seconde, avec les sommeprod 44% avec les nb.si.ens 41%, les 2 autres méthodes prennent 2-3 secondes). Jai l'impression que jvais devoir chercher ailleurs que remplacer les sommeprod pour que j'ai plus de recalcul a chaque modif :(
 
Dernière édition:

Tibo

XLDnaute Barbatruc
Re : Remplacer mes sommeprod par recherche v ou index multi critères ou..(pour allége

re,

Tu peux, le cas échéant, passer ton classeur en mode Calcul sur ordre

Lorsque tu veux recalculer, tu fais F9. Ainsi, le recalcul ne se fait plus à chaque modif.

@+
 

BOISGONTIER

XLDnaute Barbatruc
Repose en paix
Re : Remplacer mes sommeprod par recherche v ou index multi critères ou..(pour allége

>Même chose pour les plages nommées et l'index (equiv (substitue , les sommeprod restent plus rapide tant qu'eux aussi sont limités par des plages nommées

il doit y avoir erreur!

-avec =INDEX(INDIRECT(SUBSTITUE($D2;" ";""));EQUIV($E2;INDIRECT("Marques"&SUBSTITUE($D2;" ";""));0);EQUIV(A$1;Bases;0))

Excel accède directement à l'information

Pour 15.000 lignes poids lourds+10.000 lignes véhicules légers=25.000 lignes et 2.500 lignes de formules, le temps de recalcul est instantané si on modifie une donnée dans la base.

-Avec =SI(SOMMEPROD(($H2=Axes)*($I2=marques)*base1)=1;VRAI):

Excel teste n * n * n combinaisons

Pour 25.000 lignes de BD et 300 lignes de formules, le temps de recalcul est de 8 secondes et de 14 secondes pour 500 lignes de formules.

Le gain est donc évident.

JB
 
Dernière édition:

dindondesbois

XLDnaute Nouveau
Re : Remplacer mes sommeprod par recherche v ou index multi critères ou..(pour allége

Hello !

JB,

=SI(INDEX(INDIRECT(SUBSTITUE(SUBSTITUE($K5;" ";"");"-";""));EQUIV($L5;MARQUES;0);EQUIV(A$3;Bases;0))=VRAI;A$3;"")
ou
=SI(INDEX(INDIRECT(SUBSTITUE(SUBSTITUE($K5;" ";"");"-";""));EQUIV($L5;INDIRECT("MARQUES"&SUBSTITUE(SUBSTITUE($K5;" ";"");"-";"");0));EQUIV(A$3;Bases;0))=VRAI;A$3;"")

vs

=SI(SOMMEPROD((AXE=$K5)*(MARQUE=$L5)*(DEFBASE1=VRAI))=1;A$3;"")

Pour être complet, si le résultat de la formule est vrai alors j'inscris dans la cellule en face de la ligne de données le nom de la base, et rien si le résultat est faux.

J'ai testé les 2 organisations possibles, mais le résultat est quasi identique, et je ne comprends pas non plus pourquoi sommeprod est plus rapide, peut être à cause du deuxième substitue que j'ai rajouté? je vais tester sans le 2ème cet aprem. Je me doute aussi qu'en utilisant une plage nommée (DEFBASEx = Bases C2:C28 dans l'exemple) ca doit aider le sommeprod, tandis que le index équiv subtitue cherche dans toutes les plages PoidsLourds et Véh.légers. jvais regarder aussi de ce coté là.

Je fais beaucoup de calcul derrière sur l'onglet données qui renvoient à ces colonnes, peut être que sommeprod aide mieux pour les croisements. Je sais pas !

Pour le recalcul automatique Tibo, effectivement pas le choix mais mon fichier final est à destination de débutant en excel, et le fait que ce soit du temps réel c'est plus user friendly pour eux pour tester plusieurs scénarios que de faire une modif puis f9. Mais au final je vais peut être obligé de faire ca.
 

Discussions similaires

Réponses
12
Affichages
225
Réponses
6
Affichages
256

Statistiques des forums

Discussions
311 724
Messages
2 081 936
Membres
101 844
dernier inscrit
pktla