Levertijd bepalen adh van een postcode formule?

[Henradius]

Goedemorgen Allen,

Voor mijn werk ben ik bezig met het bouwen van een berekeningsmodel voor transportkosten. Het berekenen van de kosten op basis van het volume of gewicht was niet zo'n probleem maar nu loop ik toch echt vast.

Onze klant kan in mijn voltooide model de pakketgegevens invullen(LxBxH en gewicht) en de postcode, waar dan een bedrag en een levertijd naar voren komt. Het bedrag is gelukt met behulp van VERT.ZOEKEN, zie bijgevoegd. Nu wil ik ongeveer hetzelfde gebruiken voor de postcode en levertijd maar dit krijg ik niet voor elkaar. Welke formule kan ik hiervoor gebruiken?

Er zijn 3 kolommen met 3 verschillende levertijden(1,2 en 3). Er staan alleen ranges in en niet alle postcodes maar dat is van latere zorg.

Hopelijk is het een beetje duidelijk. Alvast bedankt voor de moeite!

Bekijk bijlage TX France v2.xlsx

 

[AlexCEL]

Hierbij een voorbeeld van hoe het zou kunnen. Ik heb de postcodelijst wel een beetje verbouwd (alles onder elkaar gezet). Bekijk maar eens.

Bekijk bijlage TX France v3.xlsx

 

[Henradius]

Bedankt AlexCEL! Het werkt perfect.

Nu is er een volgende kwestie. Voor een andere transporteur zijn er verschillende tarieven bij verschillende postcode gebieden. Ik denk dat er een voorwaardelijke formule voor de formule VERT.ZOEKEN moet worden geplaatst bij het tarief. Daarnaast weet ik niet of ik de formule voor de levertijd nu kan kopiëren nu er meerdere kolommen zijn.

Bijgevoegd het bestand. Zoals je ziet AlexCEL ben ik al een stuk verder. Maar ik ben er nog niet.

Bekijk bijlage TX France v4.xlsx

 

[AlexCEL]

De postcodelijst heb ik opgesplitst in een laagste en hoogste waarde, m.b.v. de functie LINKS en RECHTS. Hier kun je delen van teksten verkrijgen in excel. De functie WAARDE converteert (bijvoorbeeld) de tekst "5" naar getal 5, zodat je ermee kunt rekenen. De lijst heb ik nodig om de ingevoerde postcode mee te vergelijken.

Met behulp van de functie SOMMEN.ALS kun je rijen optellen die aan bepaalde voorwaarden voldoen. In dit geval zijn er 2 voorwaarden: de ingevoerde postcode moet groter/gelijk zijn dan de laagste waarde van de postcoderange, en lager/gelijk aan de hoogste waarde. Bij deze functie geeft je eerst aan welke kolom opgeteld moet worden (het optelbereik), dan de 1e kolom (het "criteriumbereik"), vervolgens het criterium waarop gecontroleerd moet worden, dan de 2e kolom met criterium etc. Als aan alle voorwaarden voldaan wordt telt de functie de rij met het bepalen van de som. Omdat bij postcodes maar 1 rij van toepassing is, geeft dit meteen het goede resultaat.

De ALS functie heb ik erin gezet zodat als de optelling 0 is, en er dus geen postcode c.q. levertijd gevonden is, een soort van foutmelding "N(iet)/B(ekend)" komt.

De celeigenschappen van de postcode-velden heb ik via rechtermuisknop > celeigenschappen > getal > aangepast op "00000" gezet, zodat ook de voorloopnul van de postcode zichtbaar blijft.

Ik zou de helpfuncties van de verschillende genoemde functies ook eens doorlezen ter verduidelijking.

Succes.

PS Als je tevreden bent, zet je de vraag dan even op opgelost?

 

[Henradius]

Het was opgelost maar nu zit ik weer vast. Zie mijn post hierboven. Bedankt voor je uitleg, ik ga het stap voor stap nalopen en er voor zorgen dat ik dit in het vervolg kan herhalen.

 

[AlexCEL]

Allereerst: pas niet een eerder bericht aan met het antwoord of opmerking of op een latere post. Dit maakt het erg lastig de draad van deze vraag te volgen. Plaats gewoon een nieuwe reactie...

Vraag: waar komt die tarievenlijst bij "DHL Economy" vandaan... Die is niet handig opgezet, niet eenduidig want er zijn meerdere ranges mogelijk bij 1 postcode. Bijvoorbeeld postcode 08001 valt in vele bereiken, onder andere:
08000-09007
08000-09008
08000-09009
etc.

De SOMMEN.ALS functie zal dus de levertijden voor al deze bereiken bij elkaar optellen... Is niet de bedoeling.

 

[Henradius]

Je hebt gelijk ik heb het verkeerd gedaan. Nu klopt het. Weet je hoe ik het probleem met de verschillende postcode gebieden en verschillende tarieven kan oplossen?

Bekijk bijlage TX France v4.xlsx

 

[AlexCEL]

Bekijk deze eens. Soms is de oplossing om je tabel iets anders te schrijven...

Bekijk bijlage TX France v5.xlsx

 

[Henradius]

Bedankt!