Fonction en escalier dans un tableur

Une question pour les forts en formule de feuilles de calcul en syntaxe excelico-neo-openofficique (pomme-i, au hasard ?).

J’ai une liste de tarifs postaux en fonction du poids :

Code:

      N         O
   ____________________
3   |   0g      0,00 €
4   |   20 g      0,55 €
5   |   50 g      0,88 €
6   |   100 g      1,33 €
7   |   250 g      2,18 €
8   |   500 g      2,97 €
9   |   1000 g      3,85 €
10   |   2000 g      5,07 €
11   |   3000 g      5,93 €

J’ai aussi un poids calculé en fonction des livres de la facture et, évidemment, je voudrais que le tarifs correspondant à l’expédition du total soit calculé dans une formule.
Il faudrait que je puisse pointer sur un prix si on est entre deux valeurs de poids, mais pour faire ça, je ne trouve qu’une solution vraiment lourde que je déplie là pour la rendre moins illisible (le poids est dans N15). Je fais des bêtes tests imbriqués et ça fonctionne très bien mais je me dis qu’il doit y avoir une manière plus élégante de procéder mais je sèche après avoir épluché toutes les fonctions disponibles :

Code:

=SI
   (
   N15=0;
   0;
   SI
      (
      N15<=N4;
      O4;
      SI
         (
         N15<=N5;
         O5;
         SI
            (
            N15<=N6;
            O6;
            SI
               (
               N15<=N7;
               O7;
               SI
                  (
                  N15<=N8;
                  O8;
                  SI
                     (
                     N15<=N9;
                     O9;
                     SI
                        (
                        N15<=N10;
                        O10;
                        SI
                           (
                           N15<=N11;
                           O11;
                           "Excès"
                           )
                        )
                     )
                  )
               )
            )
         )
      )
   )

Bah non, tu ne peux pas échapper à une recherche de l'intervalle où ça se trouve, ce n'est pas comme si tu avais une formule magique qui puisse te le calculer. Tu peux juste optimiser ta recherche et la faire dichotomique au lieu de linéaire (*), mais comme tu ne calcules pas ça des milliers de fois pas seconde...

* et là ça dépend de la gueule de l'histogramme de tes poids (non, pas ipods), petits poids, gros poids, …

Pomme-I a écrit:

Bah non, tu ne peux pas échapper à une recherche de l'intervalle où ça se trouve, ce n'est pas comme si tu avais une formule magique qui puisse te le calculer.

Magique je n’en demandais pas tant, mais j’imaginais un truc du genre
EST.DANS(plage de cellules de seuils croissants;plages de cellules de valeurs renvoyées) qui permettrait de ne pas toucher aux formules tout en mettant les tarifs à jour même si le nombre de seuils varie avec les humeurs du marketing de la poste. Comme c’est un problème qui doit souvent se poser, il ne m’eût pas paru stupéfiant qu’une fonction de ce genre existât. Bon, tant pis, je ferai ça à la main à chaque type de facture.

Pomme-I a écrit:

Tu peux juste optimiser ta recherche et la faire dichotomique au lieu de linéaire (*), mais comme tu ne calcules pas ça des milliers de fois pas seconde...

Non, effectivement, une fois par facture !
En tout cas merci pour cet avis éclairé.

Code:

=RECHERCHEV(N15;N3:O11;2;VRAI)

Il faut que le tableau N3:O11 soit trié de façon croissante sur sa première colonne (le poids), ce qui est ton cas.

TG a écrit:

Code:

=RECHERCHEV(N15;N3:O11;2;VRAI)

Il faut que le tableau N3:O11 soit trié de façon croissante sur sa première colonne (le poids), ce qui est ton cas.

Oui mais non… Ça, ça ne marche pas dans mon cas parce que le poids est une valeur quelconque, je ne peux donc pas placer toutes les valeurs possibles dans une colonne de matrice. Il faut que je puisse pointer d’une valeur continue vers quelques valeurs discrètes.

Par contre ta fonction va me permettre de résoudre un autre casse-tête ! Je choisis dans une cellule avec un menu déroulant un titre de livre pour une ligne de la facture et les cellules auteur, prix TTC et poids peuvent être automatiquement remplies ! hahaaaaaaa ! En fin la solution !

En résumé, me voilà avec mes deux problèmes résolus.
Merci à vous deux.

hr a écrit:

Oui mais non… Ça, ça ne marche pas dans mon cas parce

parce que tu n'as pas essayé ! Le 4ème paramètre (VRAI) n'est pas là pour rien, sieur hr !
Il te faudra d'ailleurs le mettre à FAUX pour résoudre vraiment ton autre besoin (sélection automatique et sûre du prix de l'ouvrage). Remarque : quand il est à FAUX, la liste n'a pas besoin d'être triée.

Ça faisait vraiment longtemps que je n'avais plus fouillé dans ces f.….s fonctions
Bon... je n'avais utilisé recherche qu'avec des valeurs qui figuraient dans le tableau, avec ta solution, TG, ça renvoie le tarif inférieur quand il n'y a pas égalité, alors que ça devrait renvoyer le tarif supérieur. Doit bien y avoir un moyen de passer à la valeur de la cellule du dessous mais je dois dire que ces trucs sont tellement tordus.…

TG a écrit:

parce que tu n'as pas essayé ! Le 4ème paramètre (VRAI) n'est pas là pour rien, sieur hr !

Mais si, bien sûr, j’ai essayé, mais comme le résultat ne correspond pas sauf quand la valeur était pile, à minuit et demi j’ai eu un peu de mal à interpréter ce qui se passait. C’était dû au problème qu’indique pomme et qui n’arrange pas mon affaire.

Par contre sélectionner le titre pour en tirer les autres donnée fonctionne très bien avec ça.

Bon, là on est dans le détail. Pour aller chercher le plafond (mea culpa, j'ai lu vite et je pensais qu'on voulait le plancher), on a plusieurs solutions.
La première consiste à remarquer que tout plafond est un plancher. On peut donc transformer l'un en l'autre. Il suffit de créer une colonne supplémentaire au tableau et d'y placer les formules qui vont bien. En P3 on trouvera donc :

Code:

=O4

qu'on recopiera jusqu'en P10 et en P11 on placera une valeur d'erreur (ou la valeur pour les colis dépassant les 3Kg).
Ce tableau s'automaintient lors des changements de tarif (sauf si le nombre de seuils change ; mais la plupart les méthodes qu'on pourra imaginer souffre de ce défaut).
La formule de recherche devient donc :

Code:

=RECHERCHEV(N15;N3:P11;3;VRAI)

Sauf que quand le poids tombe juste sur une valeur du tableau, ça commute immédiatement sur la valeur suivante.
On peut corriger ça de deux façons au moins.
Soit on retire au poids une valeur très petite (de plusieurs ordres inférieure à la résolution de l'appareil de mesure :

Code:

=RECHERCHEV(N15-0,0000000001;N3:P11;3;VRAI)

(ce qui retourne néanmoins #N/A si le poids est nul : une fois corrigé, il est légèrement négatif, donc hors tableau) soit, de façon plus puriste, on vérifie d'abord être dans une égalité avant de choisir la valeur :

Code:

=SI(ESTNA(RECHERCHEV(N15;N3:O11;2;FAUX));RECHERCHEV(N15;N3:P11;3;VRAI);RECHERCHEV(N15;N3:O11;2;FAUX))

La première méthode a incontestablement pour elle la compacité et la maintenabilité mais c'est du bricolage. La second montre comment tirer parti de ses erreurs...


Une autre façon de faire si on ne souhaite pas ajouter une colonne en P est de mettre à contribution la fonction RECHERCHE (ni V ni H) qui permet de travailler sur des sélections plus complexes. Ici, on va faire correspondre la colonne de gauche avec celle de droite en intégrant un décalage d'une ligne (ce qu'on faisait en P dans la méthode précédente) :

Code:

=RECHERCHE(N15;N3:N11;O4:O12)

le résultat souffre du même problème en cas d'égalité. En combinant le tout, on peut trouver que :

Code:

=SI(ESTNA(RECHERCHEV(N15;N3:O11;2;FAUX));RECHERCHE(N15;N3:N11;O4:O12);RECHERCHEV(N15;N3:O11;2;FAUX))

donne un bon résultat (sauf pour un poids nul). Par précaution, on placera une valeur d'erreur en O12 (qui fait partie de la plage de droite).

Bien entendu, la méthode qui consiste à minimiser le poids d'une valeur introuvable en pratique va simplifier la formule :

Code:

=RECHERCHE(N15-0,0000000001;N3:N11;O4:O12)

Si néanmoins on retient la méthode par minimisation minime, on peut un peu bricoler pour ne pas tomber hors tableau et rester positif (c'est pas beau comme méthode mais dans la pratique ça va marcher) :

Code:

=RECHERCHE(ABS(N15-0,0000000001);N3:N11;O4:O12)

Comme quoi l'ABS sait mettre un coup de frein aux petites erreurs !
Mais je répète que c'est du bricolage.

Si avec ça tes factures ne sont pas au top...

Pour être complet, je signale qu'il est toujours préférable pour des besoins comme celui-là de réserver des colonnes entières aux tables de correspondance. C'est à dire que dans notre exemple, on placera les poids à partir de N1 et les prix à partir de O1 et qu'on ne mettra rien d'autre en N et O que ce tableau. La valeur N15 doit donc être déplacée.
Pourquoi ?
Parce qu'en faisant ainsi, on peut indiquer aux formules de travailler sur les colonnes en tant que telles sans plus faire référence aux lignes, ce qui permet d'avoir un nombre variable de lignes dans le temps sans avoir à maintenir et surtout vérifier les formules.
Ainsi, si N15 devient M15, la formule de base devient :

Code:

=RECHERCHEV(M15;N:0;2;VRAI)

On voit qu'on ne parle plus que de N:O sans limite de lignes.
Hélas, la méthode par décalage (avec RECHERCHE) ne peut plus être utilisée. Sauf à utiliser une astuce qui relève plus du bug que de la fonctionnalité :

Code:

=SI(ESTNA(RECHERCHEV(M15;N:O;2;FAUX));RECHERCHE(M15;N:N;O2:O3);RECHERCHEV(M15;N:O;2;FAUX))

ou

Code:

=RECHERCHE(M15-0,0000000001;N:N;O2:O3)

selon la méthode qu'on préfère. Je vous laisse chercher l'astuce (Attention, si c'est un bug, il ne sera pas nécessairement portable sur OOo, sur PC et sur les autres versions d'Excel).

hr, je te laisse adapter tout ça à ton cas. Bon courage.

TG a écrit:

Le 4ème paramètre (VRAI) n'est pas là pour rien

À propos, vrai et faux ne sont pas pris dans la formule dans neo. Il remplace VRAI par 1 et FAUX par 0. C’est juste un bug d’écriture, fonctionnellement ça correspond.

Ayant tout regardé et essayé, j’ai pris un peu de tout et fait comme suit :

Code:

=SI(F35>0;RECHERCHE(F35-0,1;K7:K21;L8:L22);"poids nul")

Avec évidemment la valeur de poids en F35 En L22 se trouve le mot excès qui est affiché si on dépasse 3000g et si le poids est nul, la formule affiche « poids nul », haha.
Les plages sont définies plus loin que les valeurs utiles et les cellules supplémentaires sont vides, ce qui ne perturbe pas la formule. La mise à jour peut donc être faite avec plus ou moins de seuils sans avoir à retoucher à les formules elles-mêmes. Le petit rien à soustraire est limité à 0,1 puisque de toute manière les valeurs de poids sont toujours définies au gramme près.

Code:

7   K      L
8   0 g      0,00 €
9   20 g      0,55 €
10   50 g      0,88 €
11   100 g      1,33 €
12   250 g      2,18 €
13   1000 g      3,85 €
14   2000 g      5,07 €
15   3000 g      5,93 €
16   vide      excès
17   vide      vide
18   vide      vide
19   vide      vide
20   vide      vide
21   vide      vide
22   vide      vide

Voilà qui répond à mon cahier des charges de façon satisfaisante. Merci à vous.

hr a écrit:

Merci à vous.

Tu vouvoies TG maintenant ?
Parce que moi ….

Pomme-I a écrit:

hr a écrit:

Merci à vous.

Tu vouvoies TG maintenant ?
Parce que moi ….

Bah, tu t’y es intéressée et tu as passé un peu de ton temps à y réfléchir, ce n’est pas rien.
Quant à TG, il a fait un gros boulot impressionnant en essayant des tas de solutions au point que j’en ai un peu honte, je n’imaginais pas qu’avec ma bête question je le ferais bosser tant que ça !

Oh, t'inquiètes pas, hr. Le but est aussi de répondre de façon assez large à la question pour permettre à ceux qui ont un besoin proche d'y trouver leur compte. C'est aussi l'occasion de montrer que la bonne réponse n'existe pas. Il y a des solutions parmi lesquelles chacun fait son marché.

TG a écrit:

Il y a des solutions parmi lesquelles chacun fait son marché.

Surtout que là, la réponse à la question posée a répondu à une autre que je n’avais pas encore eu le temps de poser…

TG a écrit:

C'est aussi l'occasion de montrer que la bonne réponse n'existe pas. Il y a des solutions parmi lesquelles chacun fait son marché.

Et ça ne s'applique pas qu'au tableur ! Étant (entre autre) enseignant, j'ai pris l'habitude de dire « voilà comment on peut faire ». Et encore, je repousse ça le plus loin possible car les gens s'attendent à ce qu'on leur explique ce qu'il faut faire (ou à défaut, comment on peut faire). Alors qu'il faut s'imprégner des problèmes pour les dominer et les résoudre. C'est la seule approche qui permet d'avancer, et d'utiliser dans des domaines connexes le travail qui a été fait.