× Aidez la recherche contre le COVID-19 avec votre ordi ! Rejoignez l'équipe PC Astuces Folding@home
 > Tous les forums > Forum Bureautique
 Formule complexe excel
Ajouter un message à la discussion
Page : [1] 
Page 1 sur 1
Karlum
  Posté le 21/12/2006 @ 19:57 
Aller en bas de la page 
Petit astucien

Bonjour à tous et à toutes,

Voilà, je recherche une formule complexe afin de faciliter des calculs que je fais à mon boulot.

La situation: je dois calculer un cout par rapport à un tableau avec des ordonnés et abscisses.

A l'horizontal: il s'agit de tranche tranche de poids: 0 à 10 kgs, 2 eme colonne 11 à 20 kgs... et avant dernière colonne 90 à 99 kgs et 100 à 500 kgs.

pour les colonnes de 1 à 99 kgs c'est un forfait, de 100 à 500 kgs c'est un prix aux 100 kgs, j'y reviens + tard.

En ordonné: se sont les départements Français, de 1 à 95.

par exemple:

départ.\ kgs !1 - 10 ! 11 - 19 !!! 90 - 99 ! 100 - 500

-01 -------- !------! ---22 --!!!------- !----------

-02 -------- ! -----!---------!!!------- !--- 32

-03 ---------!----- !-------- !!!------- !---------

-04 ---------!----- !-------- !!!------- !---------

...

En exemple j'ai mis des valeurs en rouge.

imaginons département 01 pour 13 kgs: le montant est de 22 €

pour le déparetement 02 et 124 kgs: le montant est de 32 * 1.3 soit 41.6 €

j'ai mis 1.3 car le calcul se base pour des poids arondis à la dizaine supérieure (130) et c'est un prix à la centaine soit 1.3.

Ma question est donc, quelles formules ou type de formule pour faire:

  • en 1er, recherche à partir : d'une valeur en abscisse: (ex 13kgs) ; et d'une valeur en ordonné (ex 02)
  • un arrondi à la dizaine supérieur ( 130 pour 124)
  • prendre la valeur en ordonnée à la centaine supérieur quand > à 100 kgs (1.3 pour 130)
  • Enfin donner la valeur correspondante aux données précédentes.

Voilà, je ne sais pas si vous avez compris, n'hésitez pas à me dire ce qui n'est pas clair dans mon explication



Modifié par Karlum le 21/12/2006 20:41
Publicité
ferrand
 Posté le 22/12/2006 à 00:40 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

Bonsoir,

Utilise la fonction INDEX pour extraire la valeur qui convient de ton tableau tarifaire.

Il faudra y insérer des formules de calcul pour déterminer les arguments de cette fonction à partir des données entrées. Viser au plus simple, on ne s'en porte que mieux après…!

galopin01
 Posté le 22/12/2006 à 02:53 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

bonjour,
l'illustration suivante montre comment extraire un tarif dans un barême par tranche de poids

A+



Modifié par galopin01 le 22/12/2006 03:06
dixit
 Posté le 22/12/2006 à 10:18 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petit astucien

bonjour,

voici une approche

la formule de l'arrondi de poids au multiple n'a pas été faite : je n'ai pas la fonction Arrondi.au.multiple dans mon Excel 2000.

le traitement est basé sur INDEX et EQUIV comme les propositions précédentes. quelques explications sont données sur mon site sur ces 2 fonctions ; l'aide d'Excel est certainement à lire également.

à plus tard, si nécessaire.

dixit
 Posté le 22/12/2006 à 10:41 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petit astucien

re

j'ai modifié le fichier précédent avec un arrondi supérieur (et non au multiple : j'avais lu un peu vite ...)

le lien reste valable.

kénavo

Karlum
 Posté le 22/12/2006 à 19:45 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petit astucien

Merci les gars, Ferrand, Gallopin01 et Dixit,

Je suis épaté, entre nous cela fait des années que la question me trote dans la tête et que j'ai essayé de trouver la réponse et vous en une journée Hop vous me donner la réponse.

Merci encore.

Je vais maintenant étudier cela pour réussir à le reproduire et le mettre en forme pour une utilisation fréquente.

...

Après étude j'ai une question Dixit par rapport à ton tableau:

-J'ai ajouter une colonne 90 à 99 kgs. Et pour l'exemple j'ai ajouter un poids pour le département 03.

Quand je modifie les données: département 03 et poids 95 il m'indique 0.

Je pense que c'est parce que la case en jaune insérée n'est pas nommée "limitepoids". Comment fait-on pour nommée une case, ligne ou colonne.

Bon WE

tres bonnes fêtes de fin d'année.



Modifié par Karlum le 22/12/2006 19:57
dixit
 Posté le 22/12/2006 à 21:49 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petit astucien

bonsoir,

pour nommer une cellule ou un groupe de cellules : (sous Excel 2000)

- sélectionner la ou les cellules

- menu Insertion / Nom / Définir

Il est possible aussi de la même façon de modifier la plage concernée par un nom.

En ce qui concerne le tableau, effectivement j'ai fait une tranche 80 à 99

pour 90 à 99, il suffit d'insérer une colonne et j'ai vu que les plages relatives aux noms étaient mises à jour automatiquement.

je suis étonné que le prix ne s'affiche pas : a-t-il été indiqué à l'intersection de la tranche de poids et du département ?

j'ai mis le tableau à jour et j'essaie de le transférer sur mon site mais la connexion est difficile .... à suivre

kénavo

ps : transfert réussi ...



Modifié par dixit le 29/12/2006 20:08
Karlum
 Posté le 20/01/2007 à 08:21 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petit astucien

Re Salut Dixit,

Je viens de remarquer que je ne t'avais même pas répondu suite à ta dernière réponse: honte à moi.

Je te remercie encore pour tes explications et ton lien vers ton site clair et précis. Je pense enfin pouvoir utiliser ta formule car je vais en avoir besoin.

Merci encore et bon WE

dixit
 Posté le 20/01/2007 à 13:27 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petit astucien

bonjour,

c'est sympa d'y penser

bien sûr, comme tous ceux qui répondent, je souhaite savoir si ce que je propose donne satisfaction

kenavo

Publicité
Karlum
 Posté le 24/01/2007 à 20:00 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petit astucien

Re bonjour Dixit,

Je reviens vers toi car j'ai beau réfléchir et essayer je n'arrive toujours pas a comprendre. En effet plutôt que te demander la formule directement, j'essaye de comprendre.

En effet j'ai fait une formule pour le tableau ci-dessous en rapport avec ta dernière info et cela me sort la derniere colonne.

=SI(C3<1000;INDEX(bareme;EQUIV(A3;departements;0);EQUIV(C3;limitepoids;1));INDEX(bareme;EQUIV(A3;departements;0);EQUIV(C3;limitepoids;1))*C3/100)

C3 est le poids

A3 est le département

J'ai du mal a comprendre la formule index et Equiv (notament le type (0, 1...)

Sans vouloir trop t'embeter, peux-tu m'expliquer en qlq mots.

J'ai fait un tour sur ton site il est pas mal, d'autre info me seront sans doute utiles.

Salutations



Modifié par Karlum le 29/01/2007 19:10
dixit
 Posté le 24/01/2007 à 21:01 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petit astucien

bonsoir,

INDEX renvoie la valeur d'une cellule qui se trouve au croisement d'une ligne et d'une colonne (système bataille navale)

EQUIV renvoie la position de la valeur recherchée dans une matrice (on peut dire dans une zone)

l'argument "type" de la fonction EQUIV est à choisir par rapport aux données dont on dispose : j'ai retenu

0 pour la recherche dans les départements puisqu'on cherche une valeur exacte dans une matrice triée

1 pour la recherche dans la matrice limitepoids afin de trouver la valeur égale ou la plus proche inférieure, la matrice étant triée

voir la doc d'Excel qui explique (si on peut dire) ces subtilités et sur mon site pour consulter également des exemples

si ta formule donne des résultats anormaux, joins un classeur : sur la base du post précédent ce n'est pas facile de faire un diagnostic.

kénavo



Modifié par dixit le 24/01/2007 21:53
Karlum
 Posté le 24/01/2007 à 22:00 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petit astucien

En effet depuis tout à l'heure j'essaie de montrer la copie du fichier Excel où il y avait le tableau, j'ai réussi il y a longtemps mais pas aujourd'hui.

Je te remercie d'avoir répondu si vite, je crois voir maintenant l difference entre index et equiv (ou plutôt la complementarité).

Je vais regarder un peu mieux la formule. Si je n'y arrive pas je vais essayer de montrerle tableau.

Bonne soirée.



Modifié par Karlum le 24/01/2007 22:02
Karlum
 Posté le 29/01/2007 à 19:11 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petit astucien

Ca y est j'ai réussi.

Voici le tableau ou il y a les valeurs.

J'ai nommé les cases exactement comme toi la dernière fois.

Peux tu juste me dire ce qui cloche dans la formule suivane:

=SI(C3<1000;INDEX(bareme;EQUIV(A3;departements;0);EQUIV(C3;limitepoids;1));INDEX(bareme;EQUIV(A3;departements;0);EQUIV(C3;limitepoids;1))*C3/100)

Sachant qe C3 donne l'arrondi supéieur.

A3: le département demandé

Merci encore de ton aide.



Modifié par Karlum le 29/01/2007 19:33
dixit
 Posté le 29/01/2007 à 20:49 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petit astucien

bonsoir,

si tu me disais ce qui ne marche pas, cela m'éviterait de chercher.

une remarque : la formule avait été faite initialement pour des tranches de poids différentes de celles indiqués dans ce nouveau tableau et n'est donc pas adaptée.

Il faut modifier pour tenir compte du prix à la tonne et du forfait complet

je propose (sans avoir testé car je n'ai pas le courage de tout saisir dans ma feuille Excel) :

=SI(C3<25000;INDEX(bareme;EQUIV(A3;departements;0);EQUIV(C3;limitepoids;1))*c3/1000;INDEX(bareme;EQUIV(A3;departements;0);EQUIV(C3;limitepoids;1)))

attention : le poids saisi en C3 prix est considéré en Kg ce qui explique C3/1000 dans la formule

à tester

kénavo

Karlum
 Posté le 29/01/2007 à 22:19 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petit astucien

Je te remercie Dixit,

Excuse moi si je n'ai pas été assez précis dans ma demande et merci encore de ta réponse. En fait ce qui ne marche pas, c'est que le résultat correspond à la dernière colonne, pour le reste je ne sais pas trop comment te dire.

Je vais tester cela et te tiens au courant.

@+

dixit
 Posté le 30/01/2007 à 09:52 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petit astucien

bonjour,

voici une nouvelle version

le poids est à indiquer en C2 (en KG) et l'arrondi est en C3 (cet arrondi est certainement à revoir compte tenu des poids en tonnes)

le département est en A3

les tranches de poids sont 0,5T à < 1T etc ...

2 colonnes semblent inutiles mais pour ne pas avoir à tout refaire, elles ont été affectées aux tranches 21 à <23T et 23 à <25T

reste à tester

kénavo

Page : [1] 
Page 1 sur 1

Vous devez être connecté pour poster des messages. Cliquez ici pour vous identifier.

Vous n'avez pas de compte ? Créez-en un gratuitement !


Les bons plans du moment PC Astuces

Tous les Bons Plans
190,56 €Disque dur externe Western Digital Elements Desktop USB 3.0 10 To à 190,56 € livré
Valable jusqu'au 16 Juillet

Amazon Allemagne propose actuellement le disque dur externe Western Digital Elements Desktop USB 3.0 10 To à 184,13 € (avec la TVA ajustée). Comptez 6,43 € pour la livraison en France soit un total de 190,56 € livré. On le trouve ailleurs à partir de 229 €. Ce disque dur dispose d'un grande capacité de stockage (10 To) et d'une connectique USB 3.0 qui vous offrira des transferts rapides. Il est compatible USB 2.0. Une très bonne affaire. Notez que le disque dur n'est pas soudé et que vous pouvez le récupérer pour l'utiliser dans un ordinateur ou un NAS (il s'agit d'un disque dur CMR UltraStar DC HC 510).

Vous pouvez utiliser votre compte Amazon France sur Amazon Allemagne et il n'y a pas de douane. Si vous êtes perdu en allemand, vous pouvez traduire le site en anglais.


> Voir l'offre
GratuitJeu PC Killing Floor 2 gratuit
Valable jusqu'au 16 Juillet

Epic Game Store offre actuellement le jeu PC Killing Floor 2. Dans KILLING FLOOR 2, les joueurs découvrent une Europe continentale ravagée par une épidémie irrépressible de clones horribles et mortels, les Zeds, créés suite à des expérimentations ratées menées par des rebelles au sein de Horzine Corporation. Jouez en coopération à 6 joueurs... PEGI 18. Jeu en français.


> Voir l'offre
99,99 €Liseuse Amazon Kindle Paperwhite avec éclairage intégré, WiFi, IPX8 à 99,99 €
Valable jusqu'au 15 Juillet

Amazon fait une belle promotion sur la toute dernière version de sa liseuse Kindle Paperwhite (8ème génération) . Elle passe ainsi à 99,99 € au lieu de 129,99 € (avec offres spéciales). La livraison est gratuite. Cette liseuse a de nombreux avantages notamment son écran d'excellente qualité. Elle est maintenant résistante à l'eau (IPX8), afin que vous puissiez lire en toute tranquillité à la plage, au bord de la piscine ou dans le bain. Cette liseuse dispose du WiFi, de l'affichage encre électronique et surtout d'un rétro éclairage intégré pratique pour lire confortablement, quelle que soit les conditions d'éclairage (la nuit, en plein soleil, ...). Pratique pour emmener votre bibliothèque partout avec vous ! Formats pris en charge : Kindle Format 8 (AZW3), Kindle (AZW), TXT, PDF, MOBI non protégé, PRC natif ; HTML, DOC, DOCX, JPEG, GIF, PNG, BMP converti. Vous pouvez également les fichiers Epub avec cette astuce. La version avec offres spéciales (moins chère) affiche de la publicité pour des livres lors de la mise en veille de l'appareil (jamais pendant la lecture d'un livre). 


> Voir l'offre

Sujets relatifs
Excel 2003 - Problème avec une formule complexe
formule complexe dans excel
Formule excel 2007
formule excel
Formule excel
Report résultat formule Excel
Formule EXCEL
Excel 7 aide sur formule svp
Formule Excel 2007
Formule Excel
Plus de sujets relatifs à Formule complexe excel
 > Tous les forums > Forum Bureautique