MacNOMODO

Où on cause du Mac - A consommer sans modération

 
PortailPortailAccueilRechercherRechercherFAQS'enregistrerMembresConnexion

Partagez | 
 

 Fonction en escalier dans un tableur

Voir le sujet précédent Voir le sujet suivant Aller en bas 
AuteurMessage
hr
Légende vivante
Légende vivante


Nombre de messages : 6295
Age : 62
Planète : Creuse
Matos : iMac alu en OS 10.10 - G4 en OS 10.5 - MacBook Pro (90% en Ubuntu, 10% en OS X 10.6) - PC en Ubuntu 16.04
Date d'inscription : 19/11/2006

MessageSujet: Fonction en escalier dans un tableur   5/30/2008, 09:55

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"
                           )
                        )
                     )
                  )
               )
            )
         )
      )
   )

_______________
On ne fait jamais d’erreur sans se tromper !
L’affaire est dans le sac de Prévert et Prévert
Revenir en haut Aller en bas
Pomme-I
Enkysté
Enkysté


Nombre de messages : 1993
Date d'inscription : 13/12/2006

MessageSujet: Re: Fonction en escalier dans un tableur   5/30/2008, 11:31

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, … Circonspect
Revenir en haut Aller en bas
hr
Légende vivante
Légende vivante


Nombre de messages : 6295
Age : 62
Planète : Creuse
Matos : iMac alu en OS 10.10 - G4 en OS 10.5 - MacBook Pro (90% en Ubuntu, 10% en OS X 10.6) - PC en Ubuntu 16.04
Date d'inscription : 19/11/2006

MessageSujet: Re: Fonction en escalier dans un tableur   5/30/2008, 12:10

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é.

_______________
On ne fait jamais d’erreur sans se tromper !
L’affaire est dans le sac de Prévert et Prévert
Revenir en haut Aller en bas
TG
Légende vivante
Légende vivante


Nombre de messages : 5470
Age : 53
Planète : Paradis n°2
Matos : MacBook Pro Unibody 2.54 late 2008 Mountain Lion • MacPlus • PIXMA iP4300 • Scanner Epson Photo 330
Date d'inscription : 12/11/2006

MessageSujet: Re: Fonction en escalier dans un tableur   5/30/2008, 18:38

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.

_______________
[MacJams] [RouteBuddy] [Winckler] [Panoramio] [ePhotos]
Revenir en haut Aller en bas
http://www.panoramio.com/user/616684
hr
Légende vivante
Légende vivante


Nombre de messages : 6295
Age : 62
Planète : Creuse
Matos : iMac alu en OS 10.10 - G4 en OS 10.5 - MacBook Pro (90% en Ubuntu, 10% en OS X 10.6) - PC en Ubuntu 16.04
Date d'inscription : 19/11/2006

MessageSujet: Re: Fonction en escalier dans un tableur   5/30/2008, 22:38

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.

_______________
On ne fait jamais d’erreur sans se tromper !
L’affaire est dans le sac de Prévert et Prévert
Revenir en haut Aller en bas
TG
Légende vivante
Légende vivante


Nombre de messages : 5470
Age : 53
Planète : Paradis n°2
Matos : MacBook Pro Unibody 2.54 late 2008 Mountain Lion • MacPlus • PIXMA iP4300 • Scanner Epson Photo 330
Date d'inscription : 12/11/2006

MessageSujet: Re: Fonction en escalier dans un tableur   5/30/2008, 22:50

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.

_______________
[MacJams] [RouteBuddy] [Winckler] [Panoramio] [ePhotos]
Revenir en haut Aller en bas
http://www.panoramio.com/user/616684
Pomme-I
Enkysté
Enkysté


Nombre de messages : 1993
Date d'inscription : 13/12/2006

MessageSujet: Re: Fonction en escalier dans un tableur   5/31/2008, 04:23

Ça faisait vraiment longtemps que je n'avais plus fouillé dans ces f.….s fonctions Trop content
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.… Moqueur
Revenir en haut Aller en bas
hr
Légende vivante
Légende vivante


Nombre de messages : 6295
Age : 62
Planète : Creuse
Matos : iMac alu en OS 10.10 - G4 en OS 10.5 - MacBook Pro (90% en Ubuntu, 10% en OS X 10.6) - PC en Ubuntu 16.04
Date d'inscription : 19/11/2006

MessageSujet: Re: Fonction en escalier dans un tableur   5/31/2008, 07:44

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.

_______________
On ne fait jamais d’erreur sans se tromper !
L’affaire est dans le sac de Prévert et Prévert
Revenir en haut Aller en bas
TG
Légende vivante
Légende vivante


Nombre de messages : 5470
Age : 53
Planète : Paradis n°2
Matos : MacBook Pro Unibody 2.54 late 2008 Mountain Lion • MacPlus • PIXMA iP4300 • Scanner Epson Photo 330
Date d'inscription : 12/11/2006

MessageSujet: Re: Fonction en escalier dans un tableur   5/31/2008, 09:02

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...

_______________
[MacJams] [RouteBuddy] [Winckler] [Panoramio] [ePhotos]


Dernière édition par TG le 5/31/2008, 09:29, édité 1 fois (Raison : orthographe)
Revenir en haut Aller en bas
http://www.panoramio.com/user/616684
TG
Légende vivante
Légende vivante


Nombre de messages : 5470
Age : 53
Planète : Paradis n°2
Matos : MacBook Pro Unibody 2.54 late 2008 Mountain Lion • MacPlus • PIXMA iP4300 • Scanner Epson Photo 330
Date d'inscription : 12/11/2006

MessageSujet: Re: Fonction en escalier dans un tableur   5/31/2008, 09:25

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.

_______________
[MacJams] [RouteBuddy] [Winckler] [Panoramio] [ePhotos]


Dernière édition par TG le 6/1/2008, 19:16, édité 1 fois (Raison : mot oublié)
Revenir en haut Aller en bas
http://www.panoramio.com/user/616684
hr
Légende vivante
Légende vivante


Nombre de messages : 6295
Age : 62
Planète : Creuse
Matos : iMac alu en OS 10.10 - G4 en OS 10.5 - MacBook Pro (90% en Ubuntu, 10% en OS X 10.6) - PC en Ubuntu 16.04
Date d'inscription : 19/11/2006

MessageSujet: Re: Fonction en escalier dans un tableur   5/31/2008, 09:34

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.

_______________
On ne fait jamais d’erreur sans se tromper !
L’affaire est dans le sac de Prévert et Prévert
Revenir en haut Aller en bas
hr
Légende vivante
Légende vivante


Nombre de messages : 6295
Age : 62
Planète : Creuse
Matos : iMac alu en OS 10.10 - G4 en OS 10.5 - MacBook Pro (90% en Ubuntu, 10% en OS X 10.6) - PC en Ubuntu 16.04
Date d'inscription : 19/11/2006

MessageSujet: Re: Fonction en escalier dans un tableur   6/1/2008, 20:26

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.

_______________
On ne fait jamais d’erreur sans se tromper !
L’affaire est dans le sac de Prévert et Prévert
Revenir en haut Aller en bas
Pomme-I
Enkysté
Enkysté


Nombre de messages : 1993
Date d'inscription : 13/12/2006

MessageSujet: Re: Fonction en escalier dans un tableur   6/2/2008, 03:59

hr a écrit:
Merci à vous.
Tu vouvoies TG maintenant ? Trop content
Parce que moi …. Circonspect
Revenir en haut Aller en bas
hr
Légende vivante
Légende vivante


Nombre de messages : 6295
Age : 62
Planète : Creuse
Matos : iMac alu en OS 10.10 - G4 en OS 10.5 - MacBook Pro (90% en Ubuntu, 10% en OS X 10.6) - PC en Ubuntu 16.04
Date d'inscription : 19/11/2006

MessageSujet: Re: Fonction en escalier dans un tableur   6/2/2008, 07:17

Pomme-I a écrit:
hr a écrit:
Merci à vous.
Tu vouvoies TG maintenant ? Trop content
Parce que moi …. Circonspect
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 !

_______________
On ne fait jamais d’erreur sans se tromper !
L’affaire est dans le sac de Prévert et Prévert
Revenir en haut Aller en bas
TG
Légende vivante
Légende vivante


Nombre de messages : 5470
Age : 53
Planète : Paradis n°2
Matos : MacBook Pro Unibody 2.54 late 2008 Mountain Lion • MacPlus • PIXMA iP4300 • Scanner Epson Photo 330
Date d'inscription : 12/11/2006

MessageSujet: Re: Fonction en escalier dans un tableur   6/2/2008, 17:45

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é.

_______________
[MacJams] [RouteBuddy] [Winckler] [Panoramio] [ePhotos]
Revenir en haut Aller en bas
http://www.panoramio.com/user/616684
hr
Légende vivante
Légende vivante


Nombre de messages : 6295
Age : 62
Planète : Creuse
Matos : iMac alu en OS 10.10 - G4 en OS 10.5 - MacBook Pro (90% en Ubuntu, 10% en OS X 10.6) - PC en Ubuntu 16.04
Date d'inscription : 19/11/2006

MessageSujet: Re: Fonction en escalier dans un tableur   6/2/2008, 18:54

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…

_______________
On ne fait jamais d’erreur sans se tromper !
L’affaire est dans le sac de Prévert et Prévert
Revenir en haut Aller en bas
Bernardo
Enkysté
Enkysté


Nombre de messages : 1641
Age : 57
Planète : Gliese 581c
Matos : PMG5 (H) - iMacIntel (W) - Titanium (H/W)
Date d'inscription : 04/02/2007

MessageSujet: Re: Fonction en escalier dans un tableur   6/3/2008, 06:18

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.

_______________
« Il n'y a pas d'abus de pouvoir, seulement des abus d'obéissance », Chawki Amari.
Revenir en haut Aller en bas
http://web.mac.com/blestum/iWeb/Math-Mac/Bienvenue.html
Contenu sponsorisé




MessageSujet: Re: Fonction en escalier dans un tableur   Aujourd'hui à 21:33

Revenir en haut Aller en bas
 
Fonction en escalier dans un tableur
Voir le sujet précédent Voir le sujet suivant Revenir en haut 
Page 1 sur 1

Permission de ce forum:Vous ne pouvez pas répondre aux sujets dans ce forum
MacNOMODO :: A i d e  &  d é p a n n a g e :: Aspects logiciels-
Sauter vers: