Publisert av: pertm | 13.09.09

Hente data fra ikke regulær liste i Excel

Jeg hadde besøk av en venn i går og fikk et spørsmål om å hente ut data fra Excel som har ikke fast linje avstand. Problemet han hadde var med 80 000 linjer så jeg vil ikke ha noe så stort her. Her er en litt mindre versjon av slik jeg tolket det. Problemet er at dataene ikke er med noe form for fat mellomrom. Faktisk er det ikke så mye mer komplisert enn om  man har fast mellomrom.

Rad 1:
Rad 2: Eple 5
Rad 3:
Rad 4:
Rad 5: Pære 6
Rad 6:
Rad 7:
Rad 8:
Rad 9: Banan 8
Rad 10:
Rad 11:
Rad 12:
Rad 13:
Rad 14: Ananas 12
Rad 15:
Rad 16:
Rad 17:
Rad 18:
Rad 19: Paprika 12
Rad 20:
Rad 21: Kokos 54
Rad 22:
Rad 23:
Rad 24: Potet 5
Rad 25:
Rad 26:
Rad 27:
Rad 28:
Rad 29:
Rad 30: Appelsin 12
Rad 31:
Rad 32:
Rad 33:
Rad 34:
Rad 35:
Rad 36:
Rad 37:
Rad 38:
Rad 39: Gulerot 342
Rad 40:
Rad 41:
Rad 42:
Rad 43:
Rad 44:
Rad 45:
Rad 46: Vannmelon 5
Rad 47:
Rad 48: Druer 5
Rad 49:
Rad 50:

Slik ser det ut i Ark1, vel hvordan man vil ha det forandret er til dette:

Rad 1: Eple 5
Rad 2: Pære 6
Rad 3: Banan 8
Rad 4: Ananas 12
Rad 5: Paprika 12
Rad 6: Kokos 54
Rad 7: Potet 5
Rad 8: Appelsin 12
Rad 9: Gulerot 342
Rad 10: Vannmelon 5
Rad 11: Druer 5

Uten å ødelegge originalen, slik at dette blir i Ark2. Jeg fant to løsninger på det, en for Office 2007 og en som passer for Office 2003 og tidligere versjoner. Hvorfor to løsninger lurer du kanskje på, vel jeg fant først den til 2007 versjonen og den bruker formler som ikke finnes i tidligere versjoner.

Løsning for 2007

I Ark1 setter jeg inn en kolonne slik at det blir A kolonne og alt blir flyttet til høyre en plass. I A1 skriver jeg inn denne formelen:

=HVIS(B1<>»»;RAD(B1);»»)

Den blir så kopiert ned hele veien de 50 radene. Hvis du skal gjøre dette og ikke starter i Rad1(hvis en har noe overskrift) så må du trekke fra slik at en får 1 i cellen du starer

I Ark2 så bruker denne formelen i A1:

=N.MINST(‘Ark1’!A$1:A$50;RAD(B1))

Den vil finne det n minste verid i A kolonnen til Ark1 som er verdien som skal slåes opp. I B2 vil denne formelen da finne riktig verdi

=INDEKS(‘Ark1’!B$1:B$50;$A1;1)

Denne kopieres til C kolonne og så langt utover det er data og nedover så mange rader det er data

Løsning for 2003 (og tidligere)

Denne løsningen fungerer på tidligere versjoner av Excel, jeg vet ikke helt hvor mange versjoner bakover den ikke vil fungere.

I Ark1 må vi nå legge til 2 kolonner, i det minste slik jeg har løst det.

I A1 legges dette til

=HVIS(B1<>»»;ANTALL(B$1:B1);»»)

Mens i B1 legges dette til

=HVIS(C1<>»»;RAD(A1);»»)

Begge kontrollerer om det er noe datai denne raden. I A kolonna så får man tallene 1,2,3 der det er data, mens i B kolonna blir det rad nummeret til. Når dette kopieres.

I Ark2 så trenger en formel i A1

=SUMMERHVIS(Ark1!A$1:A$50;RAD(B1);Ark1!B$1:B$50)

Denne er ikke så enekel som den i 2007 versjonen og tar når og summerer alle tall som i Ark1 sin A kolonne er lik rad nummeret. Selvsagt skal hvert tall bare komme en gang. I B2 nesten lik 2007 versjonen

=INDEKS(Ark1!C$1:C$50;$A1;1)

Denne formelen kan kopieres nedover slik at en får med resten. Har man flere kolonner er det bare å kopiere den bortover også

Få verdier

Om en ikke vil at verdiene i Ark2 skal oppdateres når en forandrer noe i Ark1 så er man ikke helt ferdig. Da er det best å merkere alle dataene man har laget i Ark2, da  trenger man ikke ta med verdier for å hente riktig data. Så kan man kopiere dem og ikke lime inn vanlig men bruke Lim inn utvalg og så velge verdier Da vil dataene være uavhengig av hva som skjer med Ark1.

Sannsynligvis er kriteriene for hva som skal brukes anderledes enn det jeg har her og det må fikses til å passe problemmet man har. Min venn hadde en rekke med 80 000 om jeg fikk med meg riktig, formelene må også tilpasses til å ta det antall man har poster. Det med 80 000 fikk meg litt til å stuse siden om det er i et regneark så trodde jeg det var 2003 og den går bare til 65 536, I 2007 er ikke dette noe problem siden den går til 1 million og noe.

Rad 1:
Rad 2: Eple 5
Rad 3:
Rad 4:
Rad 5: Pære 6
Rad 6:
Rad 7:
Rad 8:
Rad 9: Banan 8
Rad 10:
Rad 11:
Rad 12:
Rad 13:
Rad 14: Ananas 12
Rad 15:
Rad 16:
Rad 17:
Rad 18:
Rad 19: Paprika 12
Rad 20:
Rad 21: Kokos 54
Rad 22:
Rad 23:
Rad 24: Potet 5
Rad 25:
Rad 26:
Rad 27:
Rad 28:
Rad 29:
Rad 30: Appelsin 12
Rad 31:
Rad 32:
Rad 33:
Rad 34:
Rad 35:
Rad 36:
Rad 37:
Rad 38:
Rad 39: gulrot 342
Rad 40:
Rad 41:
Rad 42:
Rad 43:
Rad 44:
Rad 45:
Rad 46: vannmelon 5
Rad 47:
Rad 48: druer 5
Rad 49:
Rad 50:

Legg igjen en kommentar

Fyll inn i feltene under, eller klikk på et ikon for å logge inn:

WordPress.com-logo

Du kommenterer med bruk av din WordPress.com konto. Logg ut / Endre )

Twitter picture

Du kommenterer med bruk av din Twitter konto. Logg ut / Endre )

Facebookbilde

Du kommenterer med bruk av din Facebook konto. Logg ut / Endre )

Google+ photo

Du kommenterer med bruk av din Google+ konto. Logg ut / Endre )

Kobler til %s

Kategorier

%d bloggers like this: