Excel - verdi fra dato og søkekriterie

[Zlatzman]

Hei, 

Jeg sliter litt med å finne en måte å løse denne raringen på, uten å måtte bearbeide rådataene mine inn i flere kolonner. Dette er selvsagt en mulighet hvis det ikke finnes en mer elegant måte å gjøre det på.

På ark 1 har jeg en ordreverdi sammen med en dato og en valuta for denne verdien. Jeg ønsker å finne verdi i norske kroner.
På ark 2 har jeg valutaoversikt med en fra-dato og en gjeldende kurs. 

Jeg har to hovedutfordringer:
1) Benytte både valuta og dato i oppslaget.
2) Sikre at jeg for datoer etter nyeste dato (altså etter 2022-04-01 i eksemplet under) benytter riktig verdi, og ikke hopper til feil valuta eller noe sånt.

Noen tips til hvordan dette kan løses?

[ilpostino]

Er noe på denne siden til hjelp?

edit: siden går igjennom flere måter å løse det på.

Endret 8. april 2022 av ilpostino

[Zlatzman]

ilpostino skrev (1 time siden):

Er noe på denne siden til hjelp?

edit: siden går igjennom flere måter å løse det på.

Takk for tips.

Jeg prøvde å se igjennom nå, og finner ikke noe der om historiske kurser. Dersom jeg benytter én kurs for alle transaksjoner i f.eks. Euro så er jo det greit, men jeg har data som strekker seg over et par år. Jeg ønsker benytte historiske kurser slik at jeg fanger opp korrekt kroneverdi for transaksjonen.

Her er metoden jeg benytter nå, men det krever manuell bearbeiding av kursfilene for å få dem i en struktur med kursene i hver enkelt kolonne.

Sheet 1:

Sheet 2:
 

[ilpostino]

Jeg har sett litt på dette innimellom og du kan få Excel til å hente handelskurs automatisk ved å bruke funksjonen Aksjehistorikk i Excel. Det kreves Excel 365 for å kunne bruke funksjonen men den virker veldig sexy. 

Eksempelet under henter kursen for EUR/NOK hver handelsdag mellom 01.01.21 og 31.01.21. Det er tallet 0 på slutten som angir at det skal hentes kurs for hver dag. Sjekk lenken over for å få mer beskrivelse av funksjonen.

Det skal gå enkelt å hente kursene for spesifikke dager ved å ha oppdaterte tabeller med kursene. 

=AKSJEHISTORIKK("EUR/NOK";"01.01.21";"31.01.21";0)

edit1: legger ved eksempel på hvordan tabellen med veksklingsoversikten blir. 
edit2: her er formel som søker i tabellen  under: =FINN.RAD(CELLE_MED_DATO];CELLEREFERANSE_TIL_TABELLEN;2;USANN)

Endret 8. april 2022 av ilpostino

[ilpostino]

Fant du ut av det @Zlatzman?

[Zlatzman]

Nei, her ble det manuell løsning. Kjører eldre Excel, så får ikke brukt funksjonalitet fra Office 365. Tenker jeg prøver å løse dette på motsatt side i stedet for ved å modifisere csv-filene før jeg importerer dem til Excel.

[ilpostino]

Inspirert av denne tråden lagde jeg et regneark tidligere i sommer for utlegg på en utenlandstur. Regnearket er vedlagt slik at Noen André i fremtiden kan bruke det hvis det trengs. Ark #1 har forklaringer, regneark #2 har oversikt over utlegg (kolonnen til høyre /summering oppdateres automatisk) mens regneark #3 henter vekslingskurs fra verdensveven.

 

Utlegg på tur.xlsx

[toiota]

Jeg fikk ikke til søk på to søkekriterier, så istedenfor slo jeg sammen kolonnene "Valuta" og "Dato" til en hjelpekolonne "Valua-Dato". Så her er et løsningsforslag med en ekstra hjelpekolonne i ordre- og oppslagstabellen, samt bruk av VLOOKUP-funksjonen.

Programvare: Calc, LibreOffice_24.2.1_Win_x86-64.msi

Inndata: kolonnenavn med grønn bakgrunnsfarge.
Utdata:  kolonnenavn med gulorange bakgrunnsfarge.

 

Formler i ordretabellen:

D4

=CONCAT(A4; " "; B4)

E4

=VLOOKUP($D4;$I$4:$L$100;4;1)

F4

=ROUND(C4/E4;2)

Formler i oppslagstabellen:

I4

=CONCAT(J4;" ";K4)

Kommentar til D4 og I4: 
Ved å slå sammen valuta og dato behøver man bare et søkekriterie. Tallverdien for dato er antall dager siden dag 0 (= 30. des. 1899, default).

Kommentar til kolonne "I":
NB! Kolonne "I", Valuta-Dato", i oppslagstabellen må være stigende sortert når man bruker "approximate match", dvs. siste parameter i VLOOKUP lik 1.

ordreverdi.ods

Endret 25. mars av toiota
Skrivefeil.