[Excel] Europeese brandstofkosten overzicht

[NielsB]

Goedeavond,

Ik ben al een poos bezig om een goed overzicht te maken van gemaakte brandstofkosten in europa. Nu wil ik graag berekenen wat het verschil is in liter prijs ten opzichte van de laatste keer dat ik in het desbetreffende land heb getankt.

Hierbij moet ook rekening gehouden worden met het betaalmiddel omdat dit de literprijs in mijn geval kan beinvloeden.

Als voorbeeld:
A | B | C | D
1| 10-05-2022 | Nederland | Total | €1,80
2| 21-05-2022 | Belgie | Total | €1,67
3| 26-05-2022 | Nederland | AS24 | €1,79
4| 01-06-2022 | Nederland | Total | €1,85

Stel rij 1,2,3 zijn in de loop der tijd ingevuld en ik voer op 1-6-2022 de nieuwe rij in dan wil ik dat in kolom E automatisch het verschil in literprijs komt ten opzichte van de laatste keer dat ik in Nederland met de Total betaalpas heb betaald. Volgens het voorbeeld zou er dan €0,05 in E4 moeten komen.

Mijn vraag is, is dit uberhaupt mogelijk en zo ja welke formule kan ik hier voor gebruiken?

Mvg Niels

[g0tanks]

Welkom op Tweakers!

Ik zou eerst een kolom toevoegen die land en tankstation combineert (bijv. Nederland-Total). Vervolgens heb je een formule nodig die van die combinatie de laatste literprijs vindt. Dat kan op een aantal manieren. Hierbij een voorzet: https://www.exceldemy.com...e-of-a-value-in-a-column/

De rest mag je eerst zelf proberen uit te vogelen onder het mom van eigen inzet.

[dix-neuf]

Een oplossing is mogelijk met index en vergelijken, met daarbij vermelding van de voorwaarden waaraan de kolommen A, B, en C moeten voldoen t/m de laatst ingevoerde rij-1. Dat kan door in kolom D te zoeken naar een groot bedrag, zo groot, dat je zeker weet dat dat bedrag niet voorkomt (meer dan 100 euro voor 1 liter bv. zal wel niet voorkomen). Als je dan met vergelijken als criteriumtype_getal 1 gebruikt (of dat getal weglaat), dan wordt in kolom D het laatste bedrag gevonden (van rij 1 t/m de laatste rij-1) dat aan de voorwaarden voldoet. Je moet dan alleen nog dat gevonden bedrag aftrekken van het laatst ingevoerde bedrag.

[ Voor 5% gewijzigd door dix-neuf op 10-06-2022 12:02 ]

[NielsB]

Bedankt voor jullie antwoorden. Het is mij niet gelukt om 1 van deze oplossingen werkend te krijgen zoals het zou moeten worden.

Heb er wel heel erg bruikbare info uit gekregen en daar ben ik verder mee gaan puzzelen. Zodoende heb ik nu een formule gekregen die precies doet wat er moet gebeuren.

Als voorbeeld geef ik de formule die in D4 staat.

=ALS.FOUT(VERT.ZOEKEN(MAX.ALS.VOORWAARDEN($A$1:A4;$B$1:B3;B4;$C$1:C3;C4);$A$1:D4;4;ONWAAR);"")

Hierbij wordt eerst gecontroleerd of er een overeenkomst is in de voorgaande cellen. Dus in dit geval of de waarde in B4 voor komt in B1 tot B3 en of de waarde in C4 voor komt in C1 tot C3. Vervolgens wordt de MAX van alle matchende criteria gezocht. Door VERT.ZOEKEN toe te passen kan ik als retour waarde de 4e kolom vragen en zodoende dus de laatst voor komende literprijs uit hetzelfde land bij hetzelde station. Ik heb de formule later nog aangepast door er ALS.FOUT voor te zetten om te voorkomen dat er een foutmelding wordt weergegeven op het moment dat er waarden worden ingegeven die niet voorkomen in de lijst.

Door steeds de eerste cel van een zoekbereik te blokkeren met het $ teken kan ik de formule zonder problemen oneindig doortrekken naar beneden.

Ik heb onvoldoende verstand van excel om te weten of dit de juiste oplossing is, maar het werkt en dat was de bedoelling .
Verder was het weer een leuke zoektocht en geeft het wel weer een heel goed gevoel als je er eindelijk uit krijgt wat je zoekt.

[dix-neuf]

Ik werk met Excel 2010 en die kent de functie ALS.VOORWAARDEN nog niet. Daarom had ik onderstaande formule in gedachten, die je moet invoeren in E2 en daarna naar beneden kopiëren. Let er op dat je de formule (als je ze wil gebruiken) invoert via Ctrl-Shift-Enter (d.w.z: de Ctrl- en Shifttoets ingedrukt houden terwijl je op Enter drukt). Want doe je dat niet, dan kun je verkeerde resultaten krijgen. Met de gegevens uit je voorbeeld komt er dan (als je de formule in E2 hebt ingevoerd en naar beneden gekopieerd) in E4 0,05 te staan.

code:

=ALS.FOUT(D2-INDEX(D$1:D$100;VERGELIJKEN(100;ALS(A$1:A$100<A2;ALS(B$1:B$100=B2;ALS(C$1:C$100=C2;D$1:D$100)))));"")

[ Voor 12% gewijzigd door dix-neuf op 12-06-2022 09:29 ]

[vinpower]

Net Excel 2010 heb je volgens mij ook de beschikking tot powerpivot. Daarin kan je de functie 'lookupvalue' gebruiken. Op basis van datum en land kan je de voorgaande tankbeurt vinden.

LOOKUPVALUE ( <Result_ColumnName>, <Search_ColumnName>, <Search_Value> [, <Search_ColumnName>, <Search_Value> )

Result column name wordt je prijs. eerste search columnname land met daarna criteria land. Vervolgens tweede zoekkolom wordt datum met daarna criteria voorgaande datum met de functie previousday
https://docs.microsoft.co.../previousday-function-dax