Leta värden i listor och returnera

[minismood]

Hej!

 

Har en massa datum i en lista som står intill personnamn. Det står flera datum intill ett och samma personnamn. Dessa datum vill jag returnera i kolumner. Listan är uppdelad på rader i två kolumner. En med namn och en med datum. Vad jag vill göra är att i en kolumn någonannanstans i excelbladet vill jag ha det första värdet som står intill en person och i nästa kolumn någonannanstans i bladet vill jag ha det andra värdet osv.

 

Hur kan jag lösa detta på bästa sätt?

 

Mkt tacksam för hjälp!

 

[MH2]

Borde gå med pivotabell, men jag orkar inte tänka så långt.

 

Exempel på hur du kan använda funktionen STÖRSTA

 

1. Du måste plocka ut en lista med de unika namnen. Föreslår att du använder avancerat filter

Markera namndelen av din lista, Data, filter, avancerat filter: Kryssa i "kopiera till annan plats" och "Enbart unika poster". I exemplet har jag valt cell E2 som "kopiera till".

 

Nu skall du ha en lista I e-kolumnen med alla namn (en gång).

Klistra in en formel i den här stilen i cell F2

=PRODUKTSUMMA(STÖRSTA(($A$2:$A$25=$E2)*($B$2:$B$25);ANTAL.OM($A$2:$A$25;$E2)-(KOLUMN()-6)))

eller mata in den som matrisformel (CTRL+shift+enter)

 

{=STÖRSTA(($A$2:$A$25=$E2)*($B$2:$B$25);ANTAL.OM($A$2:$A$25;$E2)-(KOLUMN()-6))}

 

och kopiera den nedåt och åt höger

 

STÖRSTA(Data;n)

Letar efter det n:te största talet i databasen

 

($A$2:$A$25=$E2)*($B$2:$B$25) Plockar helt enkelt ut de rader där namnet=E2. Om Det står något annat namn returneras 0

 

n får man fram med hjälp av

ANTAL.OM($A$2:$A$25;$E2)-(KOLUMN()-6))

Dvs man Räknar hur många gånger namnet i cell E2 förekommer i "databasen" - Kolumnnumret (det är för formeln skall returnera "nästa" datum när du kopierar den till efterföljande kolumn.

 

 

Hmm, eventuellt skulle kunna skriva

=OM(ANTAL.OM($A$2:$A$25;$E2)>(KOLUMN()-6);PRODUKTSUMMA(STÖRSTA(($A$2:$A$25=$E2)*($B$2:$B$25);ANTAL.OM($A$2:$A$25;$E2)-(KOLUMN()-6)));"")

Så slipper du felvärdena

 

 

[minismood]

Tack för ditt svar!

 

Men titta gärna på nedanstående:

 

1: Svårt att förstå. :-)

2: Sen att jag tyckte att Excel borde ha löst detta på ett betydligt enklare sätt då formeln inte skiljer så värst mycket åt från LETA-formlerna bara det att LETA-formeln endast returnerar det första värdet. T ex finns det ju LETARAD där man anger kolumnindex och på så sätt kan söka sig vidare till nästa värde om man har datan i kolumner men jag har dem i rader.

 

Kan man på något sätt ändra datan från att gå i rader till kolumner? Då blir formeln mycket enklare.

 

3: När jag använde den sistnämnda formeln får jag bara det minsta värdet och inte alla...vad gör jag för fel?

 

[PerboMan]

Bara för att det var kul:)

Följande makro stegar igenom en lista som finns i kolumnerna A och B, med start på rad 1 (går att redigera).

Efter att makrot körts finns det bara en rad per namn, med övriga värden i kolumnerna efter namnet.

Tänk på att makrot "förstör" den urspungliga listan, och att det inte finns någon kontroll på överskrivingar av värden i kolumnerna C och bortåt.

Det finns inte heller någon spärr för om något namn skulle ha så många värden att kolumnerna inte räcker till.

 

[color="#0000ff"]Sub[/color] Kolumnisera()
 r = 1 [color="#006400"]'Startrad[/color]
 [color="#0000ff"]Do[/color]
  leta_efter = Cells(r, 1)  [color="#006400"]' Hämtar namn att leta efter i kolumn A (B=2...)[/color]
  sk = 3  [color="#006400"]'Initialt första tomma kolumn[/color]
  lr = 1  [color="#006400"]'Börjar leta på raden efter aktuell rad[/color]
  [color="#0000ff"]Do[/color]
   [color="#0000ff"]If[/color] Cells(r + lr, 1) = leta_efter [color="#0000ff"]Then[/color]  [color="#006400"]'Om namnet hittas[/color]
    Cells(r, sk) = Cells(r + lr, 2)  [color="#006400"]'Kopiera värdet till första tomma kolumn[/color]
    sk = sk + 1  [color="#006400"]'Räkna upp tomkolumnindex[/color]
    Cells(r + lr, 1).EntireRow.Delete  [color="#006400"]'Ta bort den hittade raden[/color]
   [color="#0000ff"]Else[/color]  [color="#006400"]'Om namnet inte hittas[/color]
    lr = lr + 1  [color="#006400"]'Räkna upp för att kolla nästa rad[/color]
   [color="#0000ff"]End[/color] [color="#0000ff"]If[/color]
  [color="#0000ff"]Loop[/color] [color="#0000ff"]While[/color] Cells(r + lr, 1) <> [GRÅ]""[/GRÅ]  [color="#006400"]'Sluta leta om listan tagit slut[/color]
  r = r + 1  [color="#006400"]'Stega fram en rad till nästa namn[/color]
  sk = 3  [color="#006400"]'Återställ tomkolumnindex[/color]
 [color="#0000ff"]Loop[/color] [color="#0000ff"]While[/color] Cells(r, 1) <> [GRÅ]""[/GRÅ]  [color="#006400"]'Avsluta om listan tagit slut[/color]
[color="#0000ff"]End[/color] [color="#0000ff"]Sub[/color]

 

/Pär B

 

 

[inlägget ändrat 2006-09-20 15:19:22 av PerboMan]

[MH2]

Snyggt, tror jag i alla fall. Skulle nog rekommendera en säkerhetskopia

 

[minismood]

Va sjuttsingen! :-)

 

Går det inte att undvika VBA?

 

Det finns ju till exempel denna matrisformel =LETAUPP({"Calle Larsson"};B37:B100;C37:C100) som letar upp det sista värdet och denna som letar upp =OM(ANTAL.OM(B37:B100;B2);PRODUKTSUMMA(STÖRSTA((B37:B100=B2)*(C37:C100);ANTAL.OM(B37:B100;B2)))) det minsta värdet. Och vidare finns det formler för att leta upp det största värdet osv.

 

Finns det verkligen ingen formel som fångar upp de mellanliggande värdena??

 

[PerboMan]

Nix!

Det skulle onekligen vara mycket praktiskt ifall DHÄMTA skulle kunna hämta alla värden som motsvarar ett villkor, men tyvärr...

 

Min lilla VBA övning borde gå att bygga om till en egendefinierad funktion som hämtar ett valfritt värde, typ LETARAD med tillägget att den skall hitta n:te raden som passar på villkoret.

 

Jag skall grunna på saken och se om jag kan tota ihop något.

 

/Pär B

 

[MH2]

Men varför är det så svårt att acceptera KOLUMN() för att plocka ut det n:te största värdet?

Är det anständigare om man skriver det manuellt istället? (om din utsorterade lista ligger i kolumn E)

 

=PRODUKTSUMMA(STÖRSTA(($B$37:$B$100=$E2)*($C$37:$C$100);1)

=PRODUKTSUMMA(STÖRSTA(($B$37:$B$100=$E2)*($C$37:$C$100);2)

=PRODUKTSUMMA(STÖRSTA(($B$37:$B$100=$E2)*($C$37:$C$100);3)

 

Eller om du lägger 1,2,3 i översta raden?

=PRODUKTSUMMA(STÖRSTA(($B$37:$B$100=$E2)*($C$37:$C$100);F$2)

 

Tycker att Kolumn är minst lika enkelt.

=PRODUKTSUMMA(STÖRSTA(($B$37:$B$100=$E2)*($C$37:$C$100);KOLUMN()-6))

 

Det går säkert att lösa med någon passa- och indirekt kombination men jag ger upp nu.

 

***Tog bort ett feltänk***

[inlägget ändrat 2006-09-20 16:32:09 av MH2]

[PerboMan]

OK i all hast:

Function LetaRadNummer(LetaVärde, Område, Kolumn, VärdeNummer)

Dim rCell As Range

lkolmin = 0

For Each rCell In Område

If lkolmin = 0 Then

lkolmin = rCell.Column

Exit For

End If

Next

hittar = 1

flagga = 0

For Each rCell In Område

If flagga = 0 Then

If rCell.Column = lkolmin Then

If rCell.Value = LetaVärde And hittar = VärdeNummer Then

flagga = 1

ElseIf rCell.Value = LetaVärde And hittar < VärdeNummer Then

hittar = hittar + 1

End If

End If

Else

If rCell.Column = lkolmin + Kolumn - 1 Then

LetaRadNummer = rCell.Value

Exit For

End If

End If

Next

End Function

 

För funktion se bild.

 

Jag kan kommentera imorgon, och kanske hitta en bättre lösning för att hitta vänstar kolumnen (lkolmin).

 

/Pär B

 

[bild bifogad 2006-09-20 16:38:43 av PerboMan]

[inlägget ändrat 2006-09-20 16:39:37 av PerboMan]

[minismood]

Men herregud människa tror att du har löst det!!!!!!! :-)

 

=PRODUKTSUMMA(STÖRSTA(($B$37:$B$100=$B2)*($C$37:$C$100);4)

)

 

Använde ovanstående och ändrade siffra för varje ny post jag ville söka efter!

 

TACK som fan!

 

[minismood]

Följdfråga:

 

Det går inte göra på samma sätt när när man vill ha över text eller tal som ser ut så här: 1998:2255

 

[PerboMan]

Den kod jag skickade in förut klarar vilka värden som helst.

 

/Pär B

 

Ps.

 

Med formatering och kommentarer:

 

[color="#0000ff"]Function[/color] LetaRadNummer(LetaVärde, Område, Kolumn, VärdeNummer)
[color="#0000ff"]Dim[/color] rCell [color="#0000ff"]As Range[/color]
lkolmin = 0  [color="#006400"]'Variabel för att hålla koll på vänstra kolumnen[/color]
[color="#0000ff"]For Each[/color] rCell [color="#0000ff"]In[/color] Område
 [color="#0000ff"]If[/color] lkolmin = 0 [color="#0000ff"]Then[/color]
  lkolmin = rCell.[color="#0000ff"]Column
  Exit For
 End If
Next[/color]
hittar = 1  [color="#006400"]'Hittils hittade värden[/color]
flagga = 0  [color="#006400"]'Flaggan är noll tills n:te raden hittats[/color]

[color="#0000ff"]For Each[/color] rCell [color="#0000ff"]In[/color] Område
 [color="#0000ff"]If[/color] flagga = 0 [color="#0000ff"]Then[/color]  [color="#006400"]'Leta bara efter LetaVärde[/color]

  [color="#0000ff"]If[/color] rCell.[color="#0000ff"]Column[/color] = lkolmin [color="#0000ff"]Then
   If[/color] rCell.[color="#0000ff"]Value[/color] = LetaVärde [color="#0000ff"]And[/color] hittar = VärdeNummer [color="#0000ff"]Then[/color]
    flagga = 1
   [color="#0000ff"]ElseIf[/color] rCell.[color="#0000ff"]Value[/color] = LetaVärde [color="#0000ff"]And[/color] hittar < VärdeNummer [color="#0000ff"]Then[/color]
    hittar = hittar + 1
   [color="#0000ff"]End If
  End If
 Else[/color]  [color="#006400"]'Hämta från rätt kolumn[/color]

  [color="#0000ff"]If[/color] rCell.[color="#0000ff"]Column[/color] = lkolmin + Kolumn - 1 [color="#0000ff"]Then[/color]
   LetaRadNummer = rCell.[color="#0000ff"]Value
   Exit For
  End If
 End If
Next
End Function[/color]

 

Ds.

 

[MH2]

Det går inte göra på samma sätt när när man vill ha över text eller tal som ser ut så här: 1998:2255

 

Näpp, inte med den här grundidén i alla fall. Det bygger på att man kan rangorda själva data-kolumnen, och det går bara med tal (du skrev datum i första posten, så jag utgick från det). Vill du mixa med text så blir det ett annat tänk. Borde iofs gå med hjälp av RAD() och någon slags Index- eller förskjutningsfunktion

 

Hmmm, du skulle ju kunna hitta radnumren för den 4:e "nisse" från slutet med hjälp av

=PRODUKTSUMMA(STÖRSTA(($B$37:$B$100="nisse")*RAD($B$37:$B$100);4))

Sedan skapar du en adress utifrån det. Typ

 

 

=INDIREKT("c"&radnummret)

 

eller, om man slår ihop det

=INDIREKT("C"&PRODUKTSUMMA(STÖRSTA(($B$37:$B$100=$B2)*RAD($B$37:$B$100);4)))

Där den sista fyran är din "manuella" kontroll

 

Eftersom det är Radnummret som bestämmer så kommer data att komma i den ording de står i din ursprungstabell. Inte i storleksording

 

Om man accepterar de förhatliga kolumnerna så kan man göra så här också...

=INDIREKT("C"&PRODUKTSUMMA(STÖRSTA(($B$37:$B$100=$B2)*RAD($B$37:$B$100);KOLUMN()-2)))

(dvs om dina formler börjar i kolumn c)

 

Men jag tycker nog att Pärs lösning känns mer och mer motiverad. Det här är nog ett typexempel när ett makro faktiskt är bättre.

 

 

[MH2]

Har lite svårt att släppa den här tråden:-)

Du kan ju faktiskt skapa en ny variabel för att göra det enklare:

 

Antar att namnen står i kolumn A och Data i kolumn B

1. Sortera grunddata efter namn

2. Skapa en ny variabel i kolumnC Med hjälp av följande formel

=OM(A2<>A1;1;C1+1)

 

Nu kommer du att ha en "ordningssiffra" i kolumn C

Nu har du 3 kolumner med data me rubriker i stil med:

Namn_____Data_____Ordning

 

En ganska enkel variant om dina värden alltid är siffror:

***********Pivotabell för Tal-data********

Om dina värden alltid är tal så kan du göra en VÄLDIGT enkel variant

skapa en pivotabell (Ställ dig någonstans i data tabellen> Menyn Data->(rapport för) pivotabeller.

 

"Namn" som Radfält

"ordning" som Kolumnfält

"Data" som Dataelemen

Om du ser till att det blir "summa av data" så får du ut din tabell precis som du vill

/***********Pivotabell för Tal-data********

 

 

***********Icke-numeriska data********

Skapa din lista med unika namn, antingen med filtret, eller en formel i den här stilen:

=INDIREKT("b"&PRODUKTSUMMA(STÖRSTA(($C$2:$C$43=1)*RAD($C$2:$C$43);RAD())))

(borde gå med PASSA, men jag orkar inte)

 

Antag att dina unika namn står i kolumn G, med början i G1

Då kan du hämta ut dina data med hjälp av PASSA och INDEX. Eller Indirekt, eftersom jag är lat...

=INDIREKT("B"&PRODUKTSUMMA(($A$2:$A$43=$G1)*($C$2:$C$43=1)*(RAD($A$2:$A$43))))

=INDIREKT("B"&PRODUKTSUMMA(($A$2:$A$43=$G1)*($C$2:$C$43=2)*(RAD($A$2:$A$43))))

=INDIREKT("B"&PRODUKTSUMMA(($A$2:$A$43=$G1)*($C$2:$C$43=3)*(RAD($A$2:$A$43))))

 

OSV

 

Eller med de hatade kolumnerna

=INDIREKT("B"&PRODUKTSUMMA(($A$2:$A$43=$G1)*($C$2:$C$43=KOLUMN()-7)*(RAD($A$2:$A$43))))

 

[minismood]

Tjena! Grymt svar!

 

Körde med pivottabell och ordningsnr. Funkar jättebra, har bara ett litet problem. Det är nämligen så att vi har en fjärde kolumn där det står ett ärendenr som hör till som hör till uppgifterna i Kolumn B.

 

Kolumn A = Namn

Kolumn B = Dagar

Kolumn C = Ordningsnr

Kolumn D = Ärendenr

 

Går det att skapa en till rad för varje namn i pivottabellen där ärendenumret står intill dagarna?

 

[MH2]

Ja, om Ärendenr är ett tal så lägger du bara in det som ett nytt dataelement (summavariabel). Det är bara att släpa dit det.

 

Men då kommer dagar och ärendenummer att hamna bredvid varandra, på samma rad. Men du vill ha det nedanför?

 

Om du vill ha det i samma kolumn och på olika rader så får du "dra" i det gråa fältet "DATA" så att det hamnar bredvid "NAMN" istället för Ordningsnummer. Väldigt lätt att göra, men svårt att förklara :-( ähh, se bild för ett före/efter

 

Om du vill slippa texten "summa av dagar" så högerklickar du och går in i fältinställningar. Du får inte döpa fältet till dagar, men du kan tex skriva dagar+mellanslag.

 

 

[bild bifogad 2006-09-25 13:34:52 av MH2]

[minismood]

Shoot! Tack som fan för hjälpen(!!!) men tyvärr står ärendenumret ex. så här: "8111:6544", vilket inte är ett tal. Det går inte att lösa på något annat sätt? Om man tar bort : så blir det dock lite halvt oläsligt...Några idéer?

 

[MH2]

Beror på.

Om ditt artikelnummer har ett helt regelbundet format typ

4siffror:4siffror

så kan du omvandla ditt artikelnummer till siffror och göra ett anpassat visningsformat (format, celler, anpassat; I Pivotabellen: Högerklicka, fältinställningar, tal, anpassat )

####":"####

 

 

Men det där är svårt om formatet är lite friare.

Du skulle ju kunna köra en "sök och ersätt" på dina artikelnummer och ersätta ":" med ","

Då får i alla fall något slags struktur (vänsterdelen som heltal och högerdelen som decimaltal). Men det kanske är att be om problem?

,":"#####

 

Fungerar inte det här så får du släppa pivotabellerna och gå tillbaks till någon av de jobbiga metoderna :-(.

 

I det här exemplet finns grunddata på blad 1 (A2:D51). Du har redan gjort pivotabellen för "dagar". Den ligger i blad 5, Rubrikraden finns i rad 4 och namnen i Kolumn A. Någonstans nedanför skapar du en formel i stil med:

=B5&TECKENKOD(10)&

INDIREKT("Blad1!D"&PRODUKTSUMMA((Blad1!$A$2:$A$51=Blad5!$A5)*(Blad1!$C$2:$C$51=Blad5!B$4)*(RAD(Blad1!$A$2:$A$51))))

Dvs hämta Datum, infoga en radbrytning och hämta Artikelnummret. Namnen får du hämta separat. (Kräver att du krysssar i radbrytning under Formatera celler, justering).

 

[minismood]

att jag inte tänkte på ,-tecknet...:-)

 

Jag tror jag kör på det tills vidare, pivottabellen är så bekväm.

 

Men du, tack som fan för hjälpen! Mycket, mycket tacksam! :-)

 

Ps. Säg att jag inte orkar köra "Sök och ersätt" varje gång jag fyller på med data. Hur ser en formel ut som jag exempelvis gör i en kolumn bredvid ärendenumrena som automatiskt tar bort : och ersätter med ,?

 

[MH2]

Det får bli en 2-stegsraket

=BYT.UT(A1;":";",";1)

sköter sjäva utbytet, sen omger du den av en "TEXTNUM" för att det skall Tolkas som tal.

 

=TEXTNUM(BYT.UT(A1;":";",";1))

 

[Solik]

Hej! Hoppas att jag hittade rätt. Jag har en liknande fråga.

 

Jag har en tabell med data på ca 350 rader. Kolumn A har ca 20 unika värden, i kolumner B och C finns det bara unika värden (nr. och namn) och andra kolumner består av siffror.

Vad jag vill...

Till ett annat blad vill jag hämta automatiskt alla värden från kolumn B, C och en av de andra med siffror som motsvarar en av de unika värdena i kolumn A. Alltså vill jag får en minitabell som resultat.

(Vilket är det unika värdet som måste matchas bestämmer ett värde i en cell på det nya bladet eller om det går mer advancerad så är det ett värde från en listruta. Eftersom det går att länka listrutan till en cell misstänker jag att varianten med cellen är bra nog.)

 

Jag har inte hittat någon formel i excel som kunde göra det för mig och har just börjat leka med makron så jag hoppas att ni har någon bra förslag.

 

Ursäkta om det finns för mycket fel i min text, jag är inte klar med att lära mig svenska än.

 

[inlägget ändrat 2006-10-09 22:33:42 av Solik]

[inlägget ändrat 2006-10-09 22:35:11 av Solik]

[Monshi]

Två saker - ny fråga - ny tråd är att föredra. Skapa en gärna ny tråd med din fråga och komplettera den lite.

 

För andra detaljen är jag inte helt säker på att jag följer ditt resonemang.

 

Stämmer mitt antagande:

1: Du har ett sorteringsvärde i A, namn och nyckel (unika värden) i B och C samt data i övriga kolumner.

2: Du vill kunna hämta data givet en viss kombination av värden i B och C, dvs användaren ska genom ex.v. en lista välja vilka data som ska visas?

 

Kanske ett bildexempel, en skärmdump med ursprungsdata och vad du önskar ta fram?

 

/T

Moderator Excel & övriga kalkylprogram

 

[Solik]

Hej! Tack för ditt svar och ursäkta mig. Jag kommer att skapa en ny tråd senare ikväll om jag har tid. (Borde också läsa regler här mer noggrann).

Jag har redan hittat en tillfällig lösning. Den är inte den smartaste, men vi får se om jag har tid att förbättra det med er hjälp. (Har mycket annat att göra, den här grejen är bara optimeringsgrej för beräkningar som före eller senare kommer att bytas ut mot något annat (något som en riktig programmerare gör )... så jag har ingen lust att lägga in för mycket dock är det intressant att leka med.

Vi kanske ses igen!