Recherchev renvoie une erreur

Logiciel & apps Logiciel Général
1

Bonjour à tous,

Y aurait-il des spécialistes d’Excel pour me venir en aide ?

J’utilise une formule apparemment assez commune pour remplir un tableau avec des données à l’aide de la formule RECHERCHEV(). Malheureusement, au bout de trois lignes, les résultats sont faux. J’ai fait l’essai sur plusieurs exemples et je ne trouve aucune solution.

Le problème est tout bête : j’ai une liste de valeurs associées à des dates et heures. Toute les heures ne sont pas représentées. Seulement celles où il y existe une valeur. Donc, le tableau n’a pas de cases vides mais il manque les dates et heures de toutes les cellules “vides”. Or, j’ai besoin d’avoir une liste avec toutes les heures, qu’il y ait ou non des valeurs associées. Le tableau se présente sous la forme d’une matrice de deux colonnes et d’une dizaine de milliers de lignes. Dans la première colonne on trouve les dates sous la forme “jj/mm/aaa hh:mm”, dans la deuxième colonne, j’ai des nombres ordinaires.

J’ai créé une autre table, avec une colonne présentant toutes les dates, sans exception et une autre contenant la formule RECHERCHEV. Les cases contiennent donc : “=RECHERCHEV(D2;$A$2:$B$16567;2;0)”. La formule est censée rechercher la valeur de la ligne courante (ici 2) de la colonne D dans la plage de cellules A2:B16567. Si la valeur exacte est trouvée, elle renvoie la valeur de la colonne 2 de la plage de cellules, sinon, elle renvoie N/A.

J’ai vérifié que les premières lignes correspondaient (les valeurs présentes dans les premières lignes de la plage sont exactement les mêmes que celles de ma liste de dates). Or, RECHERCHEV ne fonctionne bien que sur les deux premières lignes. A la troisième, j’obtiens un N/A alors que la valeur existe bien. Il y a bien une lacune à la dixième ligne de ma plage de cellules. Donc, il devrait y avoir une réponse N/A à la dixième ligne, précédée de neuf valeurs correctes, récupérées sur la plage de données.


42614.000000000000	14
42614.041666666700	14
42614.083333333300	#N/A
42614.125000000000	#N/A
42614.166666666700	#N/A
42614.208333333300	#N/A
42614.250000000000	#N/A

Impossible de trouver d’où vient l’erreur, pourquoi elle se produit et comment l’éliminer. La plage de données est fixe (avec des $), les valeurs recherchées sont là, exactes, le tableau comporte des lacunes (mais c’est normal : c’est la raison pour laquelle on cherche à appliquer la formule), il n’y pas de texte, pas de caractères étranges ou caractères de contrôle.

J’ai tenté de faire la même chose avec EQUIV et Index, sans plus de résultat.

S’il y a une meilleure solution, je suis preneur !!

2

Bonjour,

Peux-tu nous fournir un fichier (par exemple en passant par cjoint.com) ?
D’après ce que tu dis tout est normal mais ça ne marche pas, donc difficile de dire vu d’ici d’où peut venir le problème. :wink:

Tu es vraiment sûr que tous tes nombres sont bien des nombres et non du texte ?

Je pense que le souci a de fortes chances de venir d’un manque de précision pour trouver la valeur exacte.
En effet :

  • pour minuit c’est 0,0
  • pour midi c’est 0,5
  • mais pour 8 heures du matin c’est 1/3 et ça… c’est une valeur qui a un nombre infini de chiffres après la virgule, ce qui veut dire que ce n’est pas égal à 0,33333 ni même 0,333333333333333333333333333 donc pas facile de tomber sur la valeur exacte sauf éventuellement à utiliser également des valeurs au format “heure”, et encore… ça risque de ne pas marcher à tous les coups. :frowning:

Essaye d’utiliser les arrondis.
Je pense que 10 chiffres après la virgule devraient largement suffire. :wink:
Edité le 26/06/2017 à 23:20

3

Bonjour,

les valeurs que tu cherches et celles de la table sont différentes., surtout si il y en a qui proviennent de calculs.
Pour t’en rendre compte, par exemple, fait un copier/coller de la valeur cherché a la place de celle de la plage de la recherche(v) - 3 ème ligne-. Tu verras le résultat.
Ou bien passe tes cellules de date en standard, et en cliquant sur les cellules, compare leurs valeurs respectives, tu verras une différence.
Il te faut passer par la fonction arrondi().
Crdmt

4

Je confirme : le problème vient bien de la transformation d’une date en nombre. Cette valeur qui est retranscrite en date à l’affichage est assez approximative. Même pour les valeurs “rondes” ! Pour éliminer ce problème, j’avais formaté toutes les dates de la même manière au caractère près. Puis tout transformé en nombre avec la commande CNUM. Hélas, en vain. Excel ne donne pas des résultats constants quand il découpe une journée en 24 heures (ou en une période plus courte).

Je m’en suis rendu compte quand j’ai pris le problème d’un autre angle : ne pas classer les données en dates mais en numéro de période. Dans mon cas, il s’agissait de compter les heures à partir du début des enregistrements. La première date est donc un 0, la seconde 1 etc. Dès les premières lignes, j’ai constaté que les nombres d’heures n’étaient pas des entiers et que les arrondis posaient problème. Parfois légèrement au-dessus, parfois légèrement au-dessous et même les valeurs entières (minuit) n’étaient pas non plus vraiment entières.

Je n’ai pas le fichier sous la main en ce moment.

5

A l’origine, il y a une série de dates (une par heure) de référence, qui comprend toutes les dates entre le début et la fin de l’expérience. Et à côté un tableau montrant les dates et heures des mesures, sans mentionner les moments où la mesure est absente. Quand on teste l’équivalence des premières dates, Excel nous renvoie un VRAI tant qu’aucune lacune n’apparaît. Ce qui laisse penser que les valeurs des dates sont bien les mêmes. Le fait que ces dates soient écrites d’abord sous forme de texte puis transformées en nombre devrait en principe nous donner des résultat identiques. Eh bien non.

Je ne sais pas si c’est moi qui ne maîtrise pas bien les arrondis ou si Excel continue ses facéties, toujours est-il que les résultats, même arrondis ne donnent pas toujours des résultats comparables : il suffit d’un rien pour que l’heure obtenue ne soit pas l’heure véritable. C’est bien un entier, mais pas le bon.

Il aura fallu faire des arrondis à plusieurs chiffres après la virgule puis ensuite de retrancher la partie décimale pour obtenir quelque chose d’utilisable.

Le problème est donc provisoirement contourné à défaut d’avoir été réglé.

6

Attention de ne pas confondre la valeur contenue dans la cellule et la valeur affichée ! :wink:

Est-ce que ça veut dire que, comme je te l’avais conseillé, tu as testé avec l’arrondi et que ça fonctionne ?
Mais dans ce cas, que veux-tu dire par “provisoirement contourné mais non réglé” ? Ca marche ou pas ???
Edité le 30/06/2017 à 15:50

7

Avec l’arrondi simple, je passais d’une heure à une autre, mais pas forcément celle que l’on souhaitait.

Pour résumer, je me suis retrouvé avec des valeurs proches de l’entier, à quelques chiffres après la virgule près. Par exemple 1,9999 au lieu de 2 ou 0,99999 au lieu de 1. Hélas, il y avait aussi des valeurs telles que 1,00001.

Normalement, avec un arrondi à 0 chiffres après la virgule, tout aurait dû rentrer dans l’ordre. L’arrondi aurait dû aller dans le sens de la valeur la plus proche. Pourtant, avec l’arrondi, l’arrondi supérieur, l’arrondi inférieur et autres variantes (je crois qu’il y en a quatre, je les ai toutes testées), il y avait toujours des valeurs qui n’allaient pas. Je n’ai pas la feuille de calcul sous les yeux (elle est assez loin en ce moment), mais j’ai trouvé une mini-astuce qui, dans la sélection de nombres que je devais traiter finissait enfin par donner un résultat correct. J’ai dû faire un arrondi à un chiffre après la virgule et retrancher la partie décimale. Ou vice-versa. Je vérifierai. Sur les quelques milliers de dates, toutes ont sont bien sorties après transformation. Et le test RECHERCHEV a bien fonctionné.

J’ai indiqué que le problème avait été contourné parce que ce n’est pas une réelle solution. J’ignore toujours pourquoi 3/24 ne donne pas toujours la même valeur (pourquoi cela change d’une case à l’autre ?), j’ignore pourquoi l’arrondi n’a pas donné le résultat escompté et je ne me rappelle plus trop comment j’en suis parvenu à retrancher arbitrairement une partie décimale pour obtenir les valeur entières que j’aurais dû avoir dès le début.

Et, surtout, je n’ai eu aucun souci avec une précédente série de données ayant pourtant la même présentation et des valeurs de même type.

8

Non, je ne crois pas.
Si je ne me trompe, les chiffres avant la virgule ce sont les jours, et les chiffres après la virgules ce sont donc les heures, minutes, secondes et millièmes de seconde.
Si tu arrondis à 0 chiffres après la virgule, c’est comme si tu ne tenais compte que des jours.
C’est pourquoi je disais d’aller éventuellement jusqu’à 10 chiffres après la virgule selon la précision de tes “dates” (précision à la seconde ou à la minute).

C’est hélas un problème classique sous Excel. :frowning:

Étonnant. Mais peut-être avais-tu des données dans un format différent, ou effectuais-tu une comparaison avec des valeurs d’un autre type, ou… etc.
Les dates (et surtout les heures) sont très capricieuses sous Excel. :frowning:

Vu que ton problème est résolu, je suppose qu’on ne verra pas le fichier. :wink:
Mais le principal est que tout soit rentré dans l’ordre pour toi. :super:

@+
:hello:

9

Je viens de faire quelques essais rapidement, et un arrondi à 4 chiffres après la virgule semble fonctionner pour une précision à la minute.

Exemple :
00:00:59 = 00:01:00 = 00:01:01

10

En fait, même avec toutes les précautions, on finit toujours par avoir un décalage, surtout sur des séries longues (plusieurs années avec des mesures toutes les 5 minutes). Et les arrondis finissent également pas poser problème car il suffit d’un rien pour que la valeur arrondie ne cadre plus avec l’heure initiale.

J’ai fini par trouver la solution. Pas du tout élégante, mais efficace : j’ai laissé tomber les dates et je les ai codées sous la forme de nombres dans lesquels les unités, dizaines, centaines etc. expriment les dates complètes, heures, minutes et secondes, en juxtaposant les valeurs de chaque partie.

01/05/2011 13:55:00 => 20110501135500

15/08/2015 17:25:00 => 20150805172500

La conversion n’est pas excessivement longue et le volume de données ne pose pas plus de problèmes qu’avec les dates. De plus, les tests d’équivalence et la fonction RECHERCHEV fonctionnent correctement. Plus de souci ou, en cas d’absence de message d’erreur, de doute sur la validité du résultat.

J’aurais dû y penser beaucoup plus tôt.
Edité le 20/09/2017 à 17:09