Uttrekk fra tekst i Excel

Arkivert som Tips&Triks av i 2013 0 kommentarer

Det finnes flere funksjoner for uttrekk av deler av tekst i Excel.  Dette er funksjoner som kan brukes til å systematisere data i tabeller, der det unike feltet for data er komplekse tekstkoder.  Det er for eksempel tilfelle med Nasdaq OMX sine koder for strømpriser.  Nasdaq har ulike produktserier der alle kodene representerer følgende (en systempris-kontrakt, en CfD-kontrakt):

KodeProduktgruppeProduktPeriodePeriode-IDÅr
ENOQ3-14ENO=SystemprisQ=Kvartal3=3.kvartal14=2014
SYHELYR-14SY=CfDHEL=HelsinkiYR=År14=2014

Ved å bruke tekst-funksjoner i Excel kan det relevante trekkes ut fra kodene (Kode = ENOQ3-14 ligger i celle A1 i et regneark):

FormelSyntaksResultat
LEFT()
Antall tegn fra venstre
Celle;Antall tegn
=LEFT(A1;3)
ENO
RIGHT()
Antall tegn fra høyre
Celle;Antall tegn
=RIGHT(A1;2)
14
MID()
Antall tegn midt i
Celle;Antall tegn fra venstre;Antall tegn
=MID(A1;4;2)
Q3
LEN()
Antall tegn i en tekstreng
Celle
=LEN(A1)
8

Problemet med disse kodene i systematisering av data, for eksempel langs en tidsakse, er at de verken er like lange eller systematisert på samme måte.  Løsningen på dette er en kombinasjon av if-setninger og uttrekk av deler av teksten.

Det som er ønskelig å få ut av kodene er følgende (Data i kolonne A, uttrekk i kolonne B-E):

  • Hvilken produktgruppe er det?
  • Hvor lang er perioden?
  • Hvilken periode er det?
  • Hvilket år er det?
 FormelKommentar
ProduktgruppeIF(LEFT(A4;3)="ENO";"System";IF(LEFT(A4;2)="SY";"CfD";0))Forteller hvorvidt dette er en Systempris-kontrakt eller en CfD-kontrakt
PeriodelengdeIF(B4="System";MID(A4;4;1);IF(B4="CfD";IF(OR(MID(A4;6;1)="Q";MID(A4;6;1)="Y");MID(A4;6;1);"M")))Forteller hvilken periodelengde kontrakten har,.
Merk at systematikken er ulik for System- og CfD-kontrakter og dermed må det gjøres en IF-test på hvorvidt dette er den ene eller den andre kontraktstypen.
PeriodeIF(B4="System";IF(C4="M";MID(A4;5;3);MID(A4;4;2));IF(B4="CfD";IF(C4="M";MID(A4;6;3);MID(A4;6;2))))Forteller hvorvidt dette er en måned-, kvartal- eller årskontrakt.
Igjen er systematikken ulik basert på om dette er en system- eller DfD-kontrakt
ÅrRIGHT(A4;2)+2000Forteller hvilket år dette er.
2000 er lagt til ettersom perioden angis med 2 tegn i koden

Resultatet av en lang rekke lignende koder blir som følger:

ContractGroupLenghtPeriodYear# char
ENOMJUN-13SystemMJUN201310
ENOQ1-14SystemQQ120148
ENOQ2-14SystemQQ220148
ENOQ3-13SystemQQ320138
ENOQ4-14SystemQQ420148
ENOYR-14SystemYYR20148
SYOSLMAY-13CfDMMAY201311
SYOSLQ3-13CfDQQ3201310
SYOSLYR-14CfDYYR201410

Dermed har du en tabell der kodene kan sorteres langs en tidsakse.

Emner: , ,

Legg igjen en kommentar