Aide sur formule =index()

Soleil11 · 1 Décembre 2009

Bonjour le forum,

Pourriez-vous m'aider à modifier la formule ci-dessous :

{=INDEX('Report(Master)'!$M$2:$M$65001,MATCH(1,(MktValues!B10='Report(Master)'!$B$2:$B$65000)*("Inventories"='Report(Master)'!$G$2:$G$65001),0),0)}

Cette formule marche très bien mais lorsque la valeur n'a pas été trouvée, il me retourne la valeur N/A, il y a il un moyen qu'il me retourne la valeur zéro.

Cela m'aiderai si je veux additionner d'autre valeurs après.

Merci d'avance.

Soleil11😀

CB60 · 1 Décembre 2009

Re : Aide sur formule =index()

Bonsoir
peut être de cette façon:

HTML:

=si(estna(INDEX('Report(Master)'!$M$2:$M$65001,MATCH(1,(MktValues!B10='Report(Master)'!$B$2:$B$65000)*("Inventories"='Report(Master)'!$G$2:$G$65001),0),0));0;INDEX('Report(Master)'!$M$2:$M$65001,MATCH(1,(MktValues!B10='Report(Master)'!$B$2:$B$65000)*("Inventories"='Report(Master)'!$G$2:$G$65001),0),0))

il faut remplacer estna par son équivalent anglais, mais je pense que ça doit aller

edit
bonsoir JP, Bcharef et Hoerwind, nous avons les mêmes idées.

jeanpierre · 1 Décembre 2009

Re : Aide sur formule =index()

Bonsoir Soleil11,

Essaie en mettant un test devant ta formule : IF(ISNA(ta formule)="";0;ta formule)

(peut-être pas besoin du ="")

Bonne soirée.

Jean-Pierre

Edit : Bonsoir Bruno
Re edit : Bonsoir bcharef, me suis aperçu après coup de ma boulette tellement l'habitude du si="" alors 0.

bcharef · 1 Décembre 2009

Re : Aide sur formule =index()

Bonsoir Soleil11,
Bonsoir à toutes et à tous.

Un essai avec:

=SI(ESTNA(Formule);0;Formule).

Amicalement.

BCharef

Edition : Salut Jean-Pierre & Bruno

hoerwind · 1 Décembre 2009

Re : Aide sur formule =index()

Bonjour, salut les autres,

Peut-être un peu plus court :

Code:

=SI(NB.SI('Report(Master)'!$M$2:$M$65001;1);INDEX('Report(Master)'!$M$2:$M$65001;EQUIV(1;(Mk tValues!B10='Report(Master)'!$B$2:$B$65000)*("Inve ntories"='Report(Master)'!$G$2:$G$65001);0);0);"")

job75 · 1 Décembre 2009

Re : Aide sur formule =index()

Bonsoir le fil,

hoerwind, il y a 2 conditions testées il me semble.

Essayez :

Code:

=IF(MAX((MktValues!B10='Report(Master)'!$B$2:$B$65000)*("Inventories"='Report(Master)'!$G$2:$G$65000)),INDEX('Report(Master)'!$M$2:$M$65000,MATCH(1,(MktValues!B10='Report(Master)'!$B$2:$B$65000)*("Inventories"='Report(Master)'!$G$2:$G$65000),0)[SIZE="3"][COLOR="Red"],0[/COLOR][/SIZE]),0)

3 remarques :

1) Dans votre formule au post #1, il y a tantôt 65000, tantôt 65001. Ca ne peut pas marcher, j'ai mis partout 65000.

2) Dans votre formule au post #1, vous avez le nom de feuille Mk tValues (avec un espace). Comme il n'y a pas de guillemets simples, j'ai supprimé cet espace (qui a dû se mettre en copiant).

3) En fin de formule, le 2ème ,0 est inutile, je l'ai laissé, mais vous pouvez le supprimer.

Edit : par ailleurs, faire travailler les matrices sur 65000 lignes allonge beaucoup le temps de calcul.

Il faut travailler sur des plages évolutives en définissant un nom plage1 par une formule comme :

=OFFSET('Report(Master)'!$B$2,,,COUNTA('Report(Master)'!$B:$B)-1)

Attention, dans un calcul matriciel les plages doivent avoir la même dimension, donc mettre le même COUNTA pour toutes les plages.

A+

hoerwind · 1 Décembre 2009

Re : Aide sur formule =index()

Bonsoir job75,

Il ne me semble pas !

Avec un bout de fichier en pièce jointe c'eût été bien plus facile.

job75 · 1 Décembre 2009

Re : Aide sur formule =index()

Re hoerwind,

Les 2 conditions testées sont celles du produit matriciel :

Code:

(MktValues!B10='Report(Master)'!$B$2:$B$65000)*("Inventories"='Report(Master)'!$G$2:$G$65000)

MATCH recherche la valeur 1 dans cette matrice et renvoie la position du premier 1 trouvé.

INDEX renvoie la valeur de même position en colonne M.

A+

Soleil11 · 2 Décembre 2009

Re : Aide sur formule =index()

job75 à dit:
Re hoerwind,

Les 2 conditions testées sont celles du produit matriciel :

Code:

(MktValues!B10='Report(Master)'!$B$2:$B$65000)*("Inventories"='Report(Master)'!$G$2:$G$65000)

MATCH recherche la valeur 1 dans cette matrice et renvoie la position du premier 1 trouvé.

INDEX renvoie la valeur de même position en colonne M.

A+

Bonjour ,

Je vous remercie beaucoup le forum, cela m'a aider à trouver une valeur "0" au lieu de N/A. Concernant la formule il y a bien 2 conditions, j'ai deux feuilles 1 et 2 ou se trouve mon "output extraction" et dans la 3ème feuille se trouve un tableau récapitulatif qui va chercher les montant selon 2 conditions dans les feuilles 1 et 2 selon formule ci-dessous.

J'aimerais l'améliorer et utiliser la méthode évolutive comme cité auparavant par Mr. hoerwind, pourriez-vous m'aider à raccourcir cette formule. Selon vos explications si j'ai bien compris je dois utiliser des plages définies en =offset().

Pourrais-je avoir plus de détails ?

Mais dans quelle feuille dois-je mettre la formule =offset() and comment puis modifier ou reprendre ces plages définies dans la formule afin réduire le temps de calcul.

{=IF(ISNA(INDEX('Report(Amended)'!$M$1:$M$65000,MATCH(1,(MktValues!B10='Report(Amended)'!$B$1:$B$65000)*("Inventories"='Report(Amended)'!$G$1:$G$65000),0),0)),0,INDEX('Report(Amended)'!$M$1:$M$65000,MATCH(1,(MktValues!B10='Report(Amended)'!$B$1:$B$65000)*("Inventories"='Report(Amended)'!$G$1:$G$65000),0),0))}

Merci d'avance pour vos explications.

Soleil11🙂

Soleil11 · 2 Décembre 2009

Re : Aide sur formule =index() - suite

Soleil11 à dit:
Bonjour ,

Je vous remercie beaucoup le forum, cela m'a aider à trouver une valeur "0" au lieu de N/A. Concernant la formule il y a bien 2 conditions, j'ai deux feuilles 1 et 2 ou se trouve mon "output extraction" et dans la 3ème feuille se trouve un tableau récapitulatif qui va chercher les montant selon 2 conditions dans les feuilles 1 et 2 selon formule ci-dessous.

J'aimerais l'améliorer et utiliser la méthode évolutive comme cité auparavant par Mr. hoerwind, pourriez-vous m'aider à raccourcir cette formule. Selon vos explications si j'ai bien compris je dois utiliser des plages définies en =offset().

Pourrais-je avoir plus de détails ?

Mais dans quelle feuille dois-je mettre la formule =offset() and comment puis modifier ou reprendre ces plages définies dans la formule afin réduire le temps de calcul.

{=IF(ISNA(INDEX('Report(Amended)'!$M$1:$M$65000,MATCH(1,(MktValues!B10='Report(Amended)'!$B$1:$B$65000)*("Inventories"='Report(Amended)'!$G$1:$G$65000),0),0)),0,INDEX('Report(Amended)'!$M$1:$M$65000,MATCH(1,(MktValues!B10='Report(Amended)'!$B$1:$B$65000)*("Inventories"='Report(Amended)'!$G$1:$G$65000),0),0))}

Merci d'avance pour vos explications.

Soleil11🙂

Rebonjour le forum,

Toujours même sujet, concernant la formule ci-dessus est-il possible changer la deuxième condition, je voudrais extraire que les catégories "*Inventories*"='Report(Amended) par exemple : like "*inventories*". J'ai essayé de mettre deux * mais cela ne fonctionne pas.

Est-ce quelqu'un peut aussi m'aider à ce propos.

Merci de votre aide.

Soleil 11🙂

job75 · 2 Décembre 2009

Re : Aide sur formule =index()

Bonjour Soleil11, le fil,

Soleil11 à dit:
J'aimerais l'améliorer et utiliser la méthode évolutive comme cité auparavant par Mr. hoerwind,

Petite erreur, c'est moi qui l'ai proposée...

Suivre les étapes suivantes :

1ère étape : définir le nom plage1

- copier cette formule (touches Ctrl+C) :

=OFFSET('Report(Master)'!$B$2,,,COUNTA('Report(Master)'!$B:$B)-1)

- menu Insertion-Nom-Définir

- en haut de la boîte de dialogue, entrez => plage1

- en bas, clic dans la zone de texte puis touches Ctrl+V pour coller la formule précédente

- clic sur OK

2ème étape : définir le nom plage2

même chose que précédemment avec le nom plage2 et la formule :

=OFFSET('Report(Master)'!$G$2,,,COUNTA('Report(Master)'!$B:$B)-1)

3ème étape : modifier la formule que j'ai donnée comme suit :

Code:

=IF(MAX((MktValues!B10=[COLOR="Red"]plage1[/COLOR])*("Inventories"=[COLOR="red"]plage2[/COLOR])),INDEX('Report(Master)'!$M$2:$M$65000,MATCH(1,(MktValues!B10=[COLOR="red"]plage1[/COLOR])*("Inventories"=[COLOR="red"]plage2[/COLOR]),0),0),0)

Edit : dans les formules des 2 plages, il y a -1 car je suppose qu'il y a quelque chose en B1 et G1. Si pas le cas, enlever ce -1 dans la formule.

A+

job75 · 2 Décembre 2009

Re : Aide sur formule =index() - suite

Re,

Votre post #10 a croisé mon post #11.

Soleil11 à dit:
Toujours même sujet, concernant la formule ci-dessus est-il possible changer la deuxième condition, je voudrais extraire que les catégories "*Inventories*"='Report(Amended) par exemple : like "*inventories*". J'ai essayé de mettre deux * mais cela ne fonctionne pas.

Je regarde ce qu'on peut faire.

A+

job75 · 2 Décembre 2009

Re : Aide sur formule =index()

Re,

La réponse à votre post #11, avec la fonction SEARCH :

Code:

=IF(MAX((MktValues!B10=plage1)*ISNUMBER([COLOR="Red"]SEARCH[/COLOR]("Inventories",plage2))),INDEX('Report(Amended)'!$M$2:$M$65000,MATCH(1,(MktValues!B10=plage1)*ISNUMBER([COLOR="red"]SEARCH[/COLOR]("Inventories",plage2)),0),0),0)

J'ai remplacé Master par Amended, il faut le faire aussi pour les formules de plage1 et plage2.

A+

Soleil11 · 2 Décembre 2009

Re : Aide sur formule =index()

job75 à dit:
Re,

La réponse à votre post #11, avec la fonction SEARCH :

Code:

=IF(MAX((MktValues!B10=plage1)*ISNUMBER([COLOR="Red"]SEARCH[/COLOR]("Inventories",plage2))),INDEX('Report(Amended)'!$M$2:$M$65000,MATCH(1,(MktValues!B10=plage1)*ISNUMBER([COLOR="red"]SEARCH[/COLOR]("Inventories",plage2)),0),0),0)

J'ai remplacé Master par Amended, il faut le faire aussi pour les formules de plage1 et plage2.

A+

Bonjour,

=IF(MAX((B10=Companycodes)*("Inventories"=Descriptionname)),INDEX('Report(Amended)'!$M$2:$M$65000,MATCH(1,(B10=Companycodes)*("Inventories"=Descriptionname),0),0),0)

J'ai testé la formule ci-dessus et cela marche très bien, concernant ma dernière question je crois qu'on c'est mal compris je voudrais plutot effectuer un filtrer sur le mot *inventories*, *inventories* sales ou *inventories* cash, il faudrait qu'il me retourne les valeurs trouvées que si la deuxième condition est "true" pour le critère demandé:

*Inventories* sales" et *Inventories* Cash" et *Inventories* etc.

Pour être plus claire se serait faire un filtre avec le mot *inventories*.

Merci d'avance.

Soleil11

job75 · 2 Décembre 2009

Re : Aide sur formule =index()

Re,

Si vous voulez rechercher un mot avec des astérisques * il faut mettre un tilde ~ devant les *.

Comme votre demande n'est pas très claire, voici 2 solutions :

Code:

=IF(MAX((MktValues!B10=plage1)*ISNUMBER(SEARCH("~*inventories~*",plage2))),INDEX('Report(Amended)'!$M$2:$M$65000,MATCH(1,(MktValues!B10=plage1)*ISNUMBER(SEARCH("~*inventories~*",plage2)),0),0),0)

Code:

=IF(MAX([COLOR="red"]1[/COLOR]*ISNUMBER(SEARCH("~*inventories~*",plage2))),INDEX('Report(Amended)'!$M$2:$M$65000,MATCH([COLOR="Red"]TRUE[/COLOR],ISNUMBER(SEARCH("~*inventories~*",plage2)),0),0),0)

A+

Aide sur formule =index()

Soleil11

XLDnaute Occasionnel

CB60

XLDnaute Barbatruc

jeanpierre

Nous a quitté

bcharef

XLDnaute Accro

hoerwind

XLDnaute Barbatruc

job75

XLDnaute Barbatruc

hoerwind

XLDnaute Barbatruc

job75

XLDnaute Barbatruc

Soleil11

XLDnaute Occasionnel

Soleil11

XLDnaute Occasionnel

job75

XLDnaute Barbatruc

job75

XLDnaute Barbatruc

job75

XLDnaute Barbatruc

Soleil11

XLDnaute Occasionnel

job75

XLDnaute Barbatruc

Discussions similaires

Aide sur formule =index()

XLDnaute Occasionnel

XLDnaute Barbatruc

Nous a quitté

XLDnaute Accro

XLDnaute Barbatruc

XLDnaute Barbatruc

XLDnaute Barbatruc

XLDnaute Barbatruc

XLDnaute Occasionnel

XLDnaute Occasionnel

XLDnaute Barbatruc

XLDnaute Barbatruc

XLDnaute Barbatruc

XLDnaute Occasionnel

XLDnaute Barbatruc

Discussions similaires

Privacy & Transparency

Privacy & Transparency