XL 2013 recherche exacte par lookup(), retourner 0 sinon

[fgehin]

Bonjour,

J'ai un fichier de suivi de compta qui va chercher les informations dans des onglets de données mensuelles et les résume dans un onglet comparatif mois à mois (cf PJ).

Pour se faire, je vais chercher les numéros de poste comptable et fais la somme des débits et crédits sur ces postes pendant le mois écoulé.

Je viens de me rendre compte que si la fonction lookup() ne trouvait pas le n° de poste comptable pendant un mois "x", excel me retournait la valeur relative au n° trouvé le plus proche (certains postes comptables sont remplis certains mois et d'autres noms).

Or je voudrais qu'en cas d'absence du poste comptable dans le mois "x", lookup() considère que la valeur correspondant au n° de poste absent est 0 et non "N/A" (ce qui arrive si je met le n° recherché entre " ").

Quelqu'un a-t-il un idée simple et peu coûteuse en temps pour résoudre ce petit problème ? Comme vous le verrez, si je doit retaper toutes les formules à la main, j'en ai pour des heures...

Je vous remercie infiniment d'avance.

Faustine

 

[Yurperqod]

Bonjour le forum

Tu peux utiliser SI et ESTNA
=SI(ESTNA(RECHERCHE(...));0,RECHERCHE(...))
Remplacer les ... par ta formule

Post-scriptum: Les données dans ton classeur ne sont pas confidentielles par hasard?
Par sécurité, mieux vaut mettre un classeur exemple avec des données bidons.

 

[JBOBO]

Bonjour,

Essai avec =sierreur(ta_formule;0)

 

[fgehin]

Merci à vous 2,

La 1ère solution me semble trop complexe à mettre en oeuvre vu la quantité de donnée brassée dans chacune des formules.

La 2e ne fonctionne pas parce que :

• soit je met le n° de compte recherché entre " " et lookup() ne le trouve jamais (pour une raison que j'ignore) donc retourne toujours "N/A et donc toutes mes formules sont =0 si j'utilise =sierreur(ta_formule;0)
• soit je met le n° de compte sans " " et lookup() ne constate pas d'erreur si la valeur est introuvable. Il fait juste comme si j'avais recherché la valeur la plus proche qu'il trouve dans la colonne. Et donc j'ai des résultats complètement faux.

 

[Yurperqod]

soit je met le n° de compte sans " " et lookup() ne constate pas d'erreur si la valeur est introuvable. Il fait juste comme si j'avais recherché la valeur la plus proche qu'il trouve dans la colonne.

Explications du phénomène trouvée dans l'aide d4EXCEL

Remarques

• Si la fonction RECHERCHE ne peut trouver l’argument valeur_cherchée, elle utilise la plus grande valeur de l’argumentvecteur_recherche qui est inférieure ou égale à celle de l’argument valeur_cherchée.
  
  
• Si la valeur de l’argument valeur_cherchée est inférieure à la plus petite valeur de l’argument vecteur_recherche, la fonction RECHERCHE renvoie la valeur d’erreur #N/A.

https://support.office.com/fr-fr/ar...fonction-446d94af-663b-451d-8251-369d5e3864cb

 

[JBOBO]

re,
En D34, en utilisant sierreur et recherchev, alors on aurait une formule ressemblant à ceci :
=SIERREUR(RECHERCHEV(641100;'0115'!A: D;4;0)-RECHERCHEV(641100;'0115'!A: D;3;0);0)+SIERREUR(RECHERCHEV(641110;'0115'!A: D;4;0)-RECHERCHEV(641110;'0115'!A: D;3;0);0)+SIERREUR(RECHERCHEV(641140;'0115'!A: D;4;0)-RECHERCHEV(641140;'0115'!A: D;3;0);0)+SIERREUR(RECHERCHEV(641150;'0115'!A: D;4;0)-RECHERCHEV(641150;'0115'!A: D;3;0);0)+SIERREUR(RECHERCHEV(641155;'0115'!A: D;4;0)-RECHERCHEV(641155;'0115'!A: D;3;0);0)+SIERREUR(RECHERCHEV(641160;'0115'!A: D;4;0)-RECHERCHEV(641160;'0115'!A: D;3;0);0)+SIERREUR(RECHERCHEV(641200;'0115'!A: D;4;0)-RECHERCHEV(641200;'0115'!A: D;3;0);0)+SIERREUR(RECHERCHEV(641220;'0115'!A: D;4;0)-RECHERCHEV(641220;'0115'!A: D;3;0);0)+SIERREUR(RECHERCHEV(641230;'0115'!A: D;4;0)-RECHERCHEV(641230;'0115'!A: D;3;0);0)+SIERREUR(RECHERCHEV(641250;'0115'!A: D;4;0)-RECHERCHEV(641250;'0115'!A: D;3;0);0)+SIERREUR(RECHERCHEV(641400;'0115'!A: D;4;0)-RECHERCHEV(641400;'0115'!A: D;3;0);0)+SIERREUR(RECHERCHEV(641410;'0115'!A: D;4;0)-RECHERCHEV(641410;'0115'!A: D;3;0);0)+SIERREUR(RECHERCHEV(641420;'0115'!A: D;4;0)-RECHERCHEV(641420;'0115'!A: D;3;0);0)+SIERREUR(RECHERCHEV(641460;'0115'!A: D;4;0)-RECHERCHEV(641460;'0115'!A: D;3;0);0)

Pas vraiment plus digeste que l'ancienne, mais qui semble fonctionner (sous réserve que j'ai reellement compris le probleme)

A tester.
PS: il faut supprimer les espaces dans la formule entre ":" & "D", que j'ai inclus volontairement, sinon il apparait des smileys dans le message à la place de la plage de cellules.

 

[fgehin]

j'ai essayé la formule en D34 et je tombe sur 0... ce qui n'est pas le bon résultat. Je m'étonne que quelque chose de si simple bute sur autant de problème. Il n'existe pas une fonction lookup() équivalente qui cherche exactement la valeur donnée, et non une approximation si cette valeur n'existe pas dans la plage indiquée ?

 

[chris]

Bonjour
Si comme expliqué par JBOBO tu enlèves les espaces après les 2 points tu obtiens -8537

=SIERREUR(RECHERCHEV(641100;'0115'!$A:$D;4;0)-RECHERCHEV(641100;'0115'!$A:$D;3;0);0)+SIERREUR(RECHERCHEV(641110;'0115'!$A:$D;4;0)-RECHERCHEV(641110;'0115'!$A:$D;3;0);0)+SIERREUR(RECHERCHEV(641140;'0115'!$A:$D;4;0)-RECHERCHEV(641140;'0115'!$A:$D;3;0);0)+SIERREUR(RECHERCHEV(641150;'0115'!$A:$D;4;0)-RECHERCHEV(641150;'0115'!$A:$D;3;0);0)+SIERREUR(RECHERCHEV(641155;'0115'!$A:$D;4;0)-RECHERCHEV(641155;'0115'!$A:$D;3;0);0)+SIERREUR(RECHERCHEV(641160;'0115'!$A:$D;4;0)-RECHERCHEV(641160;'0115'!$A:$D;3;0);0)+SIERREUR(RECHERCHEV(641200;'0115'!$A:$D;4;0)-RECHERCHEV(641200;'0115'!$A:$D;3;0);0)+SIERREUR(RECHERCHEV(641220;'0115'!$A:$D;4;0)-RECHERCHEV(641220;'0115'!$A:$D;3;0);0)+SIERREUR(RECHERCHEV(641230;'0115'!$A:$D;4;0)-RECHERCHEV(641230;'0115'!$A:$D;3;0);0)+SIERREUR(RECHERCHEV(641250;'0115'!$A:$D;4;0)-RECHERCHEV(641250;'0115'!$A:$D;3;0);0)+SIERREUR(RECHERCHEV(641400;'0115'!$A:$D;4;0)-RECHERCHEV(641400;'0115'!$A:$D;3;0);0)+SIERREUR(RECHERCHEV(641410;'0115'!$A:$D;4;0)-RECHERCHEV(641410;'0115'!$A:$D;3;0);0)+SIERREUR(RECHERCHEV(641420;'0115'!$A:$D;4;0)-RECHERCHEV(641420;'0115'!$A:$D;3;0);0)+SIERREUR(RECHERCHEV(641460;'0115'!$A:$D;4;0)-RECHERCHEV(641460;'0115'!$A:$D;3;0);0)

RECHERCHEV est VLOOKUP en anglais et correspond donc bien à ce que tu cherches à faire...

JBOBO : utilise une balise code pour éviter les smileys : icône juste avant la disquette

 

[chris]

Re

Pour simplifier et permettre la recopie sur toutes les colonnes de la ligne 34
Créer un nom OMois avec la formule

=INDIRECT(SI(COLONNE()-3<10;"0";"")&COLONNE()-3&"15!A:D")

puis utiliser la formule suivante de D34 à O34

=SIERREUR(RECHERCHEV(641100;OMois;4;0)-RECHERCHEV(641100;OMois;3;0);0)+SIERREUR(RECHERCHEV(641110;OMois;4;0)-RECHERCHEV(641110;OMois;3;0);0)+SIERREUR(RECHERCHEV(641140;OMois;4;0)-RECHERCHEV(641140;OMois;3;0);0)+SIERREUR(RECHERCHEV(641150;OMois;4;0)-RECHERCHEV(641150;OMois;3;0);0)+SIERREUR(RECHERCHEV(641155;OMois;4;0)-RECHERCHEV(641155;OMois;3;0);0)+SIERREUR(RECHERCHEV(641160;OMois;4;0)-RECHERCHEV(641160;OMois;3;0);0)+SIERREUR(RECHERCHEV(641200;OMois;4;0)-RECHERCHEV(641200;OMois;3;0);0)+SIERREUR(RECHERCHEV(641220;OMois;4;0)-RECHERCHEV(641220;OMois;3;0);0)+SIERREUR(RECHERCHEV(641230;OMois;4;0)-RECHERCHEV(641230;OMois;3;0);0)+SIERREUR(RECHERCHEV(641250;OMois;4;0)-RECHERCHEV(641250;OMois;3;0);0)+SIERREUR(RECHERCHEV(641400;OMois;4;0)-RECHERCHEV(641400;OMois;3;0);0)+SIERREUR(RECHERCHEV(641410;OMois;4;0)-RECHERCHEV(641410;OMois;3;0);0)+SIERREUR(RECHERCHEV(641420;OMois;4;0)-RECHERCHEV(641420;OMois;3;0);0)+SIERREUR(RECHERCHEV(641460;OMois;4;0)-RECHERCHEV(641460;OMois;3;0);0)

 

[fgehin]

Merci à tous, ça a l'air de fonctionner comme ça.