Leta upp referens och återge

[Slim_Jim]

Hej alla duktiga Excel-are!

 

Jag har ett excel register över utförda arbeten som jag lägger in information i vartefter arbeten blir utförda. I slutet av varje år så gör jag en planering för nästkommande år och det sker idag helt manuellt vilket är jobbigt och tidskrävande.Jag har lyckats göra en automatisk preliminär planering MEN det finns ett aber som jag inte lyckas lösa.

 

All min service information samlas i ett kalkylblad som heter Uppslag för planering och på ett annat blad som jag har döpt till Preliminär planering har jag skapat en kalender som har nästa års dagar. I cellen under alla datum har jag skrivit in en formel som delvis hittar det jag söker. (Se bilfoagd bild)

 

Mitt problem är att det finns flera arbeten som har utförs samma datum. Jag vet inte riktigt hur jag ska få min formel att hämta olika poster som har samma datum och presentera dem i samma cell...

 

Är det någon som har en idé?

 

/Slim_Jim

 

[bild bifogad 2008-10-02 12:04:19 av Slim_Jim]

[inlägget ändrat 2008-10-02 12:20:04 av Slim_Jim]

[Monshi]

Oh, det där var en elak formel

 

Det du önskar går men kräver lite magi och några extra celler. Grundförutsättning är att du vet det maximala antalet poster som kan behöva eftersökas. Ska se om jag lyckas återge det på bra sätt.

 

Börjar med att definiera att jag har tabeller som jag exempel-söker i på samma blad för enkelhetens skull, säg i A1:A100. I G1 har jag värdet jag vill söka efter, datum exempelvis.

G2 lämnas tom (eller skriv noll där)

i G3 skriver jag

=PASSA($G$1;FÖRSKJUTNING($A$1;G2;0;ANTALV(A:A)-G2;1);0)+G2

och sedan kopierar/fyller ned den så många rader som behövs.

Sedan för att hämta värdena som hittats, skriv något i stil med

=OM(ÄRFEL(G3);"";INDEX(B1:B100;G3))

 

 

ja, för att spara lite prestanda kan man ändra PASSA-satsen till

=OM(ÄRFEL(G2);SAKNAS();PASSA(...som ovan))

för man vet ju att om föregående passa inte hittat något ska inte heller nästa PASSA göra det.

 

Vad gör dessa formler? Nyckeln är FÖRSKJUTNING-formeln som skapar en referens till det området PASSA ska söka i baserat på vilken rad värdet hittats på tidigare.

 

/T

 

Even when we know we´ll never find the answers, we have to keep on asking questions.

 

[Slim_Jim]

Hej igen,

 

Jag har försökt att projecera din lösning i mitt blad men inte lyckats.

Jag har tagit en screen dump till för att visa en del av bladet som skall undersökas. (Se bild)

Den förra screen dumpen var på bladet som kalendern och min mupp formel finns på.

 

Formeln skall alltså kontrollera om datumet finns i undersökningsbladets L-kolumn och finns datumet så ska den hämta värdet i B, G och I kolumnen på den raden. Min extremt fula formel som jag visade i förra inlägget hittar värdet om det finns men finns det fler rader med samma datum så återger den bara en av dem.

Jag har försökt att omsätta ditt förslag in i mitt blad men jag får det inte riktigt att fungera.

 

Jag kan infoga hur många kolumner som helst och skriva mina formler i men inga rader.

 

På bilden finns värdet i L1 bara en gång medan värdet i L6 (2009-09-12) även förekommer i L7.

 

Jag funderar på om jag kanske på detta blad måste sammanfoga värdena (B, G och I kolumnens värde) för alla de celler som innehar samma datum och sedan hämta upp den sammanfogade cellen till min kalender.

 

Suck vilka mupp projekt man ger sig på ibland!

 

[bild bifogad 2008-10-02 14:37:27 av Slim_Jim]

[Monshi]

Vilken del av min lösning fungerar icke? PASSA-satsen, returnerar den inte rätt rad?

 

Om datumvärdena är korrekta datum på båda bladen ska det bara vara att sätta referenserna rätt, dvs att den söker på L-kolumnen på ditt datablad.

 

INDEX går sedan inte att misslyckas med

 

Ja, passa ska väl se ut som:

PASSA($A$1;FÖRSKJUTNING('Uppslag för planering'!$L$1;G2;0;ANTALV('Uppslag för planering'!$L:$L)-G2;1);0)+G2

gärna omsluten med en OM-sats

 

Detta får du alltså göra i "stöd"-celler till cellen du vill presentera data i. Någonstans utanför din kalender.

 

Alternativt är att ta till en VBA-lösning, antingen en egen formel som i praktiken gör allt i ett steg (bättre då att ha det på bladet) eller kod som stoppar in värdena på rätt plats i din kalender. Men vanligtvis är min rekommendation att hålla sig borde från VBA när inte VBA är ett måste för att lösa problemet.

 

Fördelen med en egenfunktion är att den blir dynamisk, du behöver inte veta hur många träffar som kan fås.

 

/T

 

Even when we know we´ll never find the answers, we have to keep on asking questions.

 

[Monshi]

En UDF, en egenfunktion som dock mycket väl kan vara långsammare än motsvarande formler men fungerar på i princip samma sätta:

Function MyMultiFind(toFind As Variant, toSearch As Range, dataRange As Range, delim As String) As String
   Dim arRW() As Integer
   ReDim arRW(toSearch.Cells.Count)
   Dim temp As Variant
   On Error GoTo errHandle
   temp = Application.WorksheetFunction.Match(toFind, toSearch, 0)
   While Not IsError(temp)
       If MyMultiFind <> "" Then
           MyMultiFind = MyMultiFind & delim
       End If
       MyMultiFind = MyMultiFind & dataRange.Cells(toSearch.Row + temp - 1, 1)

       temp = Application.WorksheetFunction.Match(toFind, toSearch.Offset(temp, 0).Resize(toSearch.Rows.Count - temp, 1), 0) + temp
   Wend
errHandle:
End Function

 

toFind - värdet du ska söka efter

toSearch - kolumn du ska söka i

dataRange - kolumn data ska hämtas ifrån

delim - det som ska skrivas mellan funna värden

Klistra in koden i en modul och använd som vilken annan funktion som helst.

 

 

/T

 

Even when we know we´ll never find the answers, we have to keep on asking questions.

 

[Slim_Jim]

Jag ska inte säga att din formel inte fungerar för det gör den bergis.

 

Det som jag förmodligen inte får till rätt är följande:

 

Säg att jag söker efter 2009-09-01 och det i min tabell återfinns på fem olika rader får jag formeln endast att hitta det fösta värdet och inte de 4 andra. Det beror nog på att jag inte riktigt vet hur jag ska använda formeln.

 

Sedan vet jag inte alltid hur många gånger ett datum förekommer heller för den delen...

 

:-S

 

[Monshi]

Skapa en enkel tabell med datum enligt min uppställning och skriv in formeln.

Set att den fungerar, förstå hur den fungerar. Anpassa den sedan till dina förutsättningar. Enda du behöver veta för formelalternativet är maximala antalet förekomster. Så många PASSA-satser behöver du, så många INDEX-satser behöver du.

 

 

...eller ta den genväg jag skapat åt dig...(ed)...som faktiskt verkar fungera ganska snabbt och bra.

 

 

/T

 

Even when we know we´ll never find the answers, we have to keep on asking questions.

 

 

[inlägget ändrat 2008-10-02 15:31:55 av Monshi]

 

[inlägget ändrat 2008-10-02 15:42:24 av Monshi]

[Slim_Jim]

Din UDF var ytterst vacker och fungerar kalas!

Om jag nu vill hämta värdet ifrån 3 olika kolmner, går det att lösa med den formeln?

 

[Slim_Jim]

Om jag bara kunde få till att den skriver ut tre kolumners värde (B, G och I) enligt tidigare så fungerar den nästan bättre än förväntat!

 

Du är riktigt grym!!!!

 

TACK!

 

[inlägget ändrat 2008-10-02 16:00:41 av Slim_Jim]

[inlägget ändrat 2008-10-02 16:08:18 av Slim_Jim]

[Monshi]

Bara att lägga till två till referenser och hämta värden från dem... Testa om nu lyckas fixa det själv.

 

Ska installera om en dator nu så...

 

 

/T

 

Even when we know we´ll never find the answers, we have to keep on asking questions.

 

[Monshi]

Okej, nu får du en bättre från en bättre dator

 

Function MyMultiFind(toFind As Variant, dataRange As Range, searchColID, delim As String, ParamArray dataColId()) As String
   Dim va As Variant
   Dim temp As Variant

   Dim iSize As Long
   iSize = dataRange.Rows.Count
   Dim t As Long
   Dim searchCol As Range
   Set searchCol = dataRange.Offset(0, searchColID - 1).Cells(1, 1).Resize(iSize, 1)
   temp = Application.WorksheetFunction.Match(toFind, searchCol, 0)
   While Not IsError(temp)
       On Error Resume Next
       For Each va In dataColId

           t = -1
           t = CInt(va)
           If t > 0 Then
               MyMultiFind = MyMultiFind & dataRange.Cells(temp, t) & delim
           End If
       Next va
       On Error GoTo errHandle
       temp = Application.WorksheetFunction.Match(toFind, searchCol.Offset(temp).Resize(iSize - temp, 1)) + temp
   Wend
errHandle:
   If MyMultiFind <> "" Then
       MyMultiFind = Left(MyMultiFind, Len(MyMultiFind) - Len(delim))
   End If
End Function

här är anropen:

toFind - värdet du söker efter

dataRange - din tabell, inklusive sök och datakolumner

searchColID - relativt kolumnindex för sökkolumnen. 1 eller större

delim - skiljetecknen mellan posterna

dataColId - ett godtyckligt antal kolumner att hämta data ifrån.

 

Dvs ett anrop kan se ut som

=MyMultiFind(I2;A1:E28;1;", ";2;3;4;5)

där då sökningen sker i första kolumnen (A) och data hämtas från kolumn 2, 3, 4 och 5, eller B:E.

 

Och ja, jag tror denna kanske är 5-10% långsammare än formler på bladet men ack så mycket smidigare att använda...borde det inte finnas en sådan här funktion i Excel från början egentligen?

 

 

/T

 

Even when we know we´ll never find the answers, we have to keep on asking questions.