Sökfunktion i excel

[MattiasLu]

Har ett problem. Jag behöver utforma en slags sökfunktion i excel.

Bäst illustruerat med följande exempel.

 

 

Kol A innehåller Modell.

Kol B innehåller Serienummer.

Kol C datum då maskienen levererades.

 

Jag skulle vilja lista alla maskiner som levererades ett givet datum i ett nytt blad. Jag kan inte använda mig av sortering stigande eller fallande eftersom informationen finns i flera excel blad. Pivot tabell går heller inte att använda eftersom excel bladet där delat mellan många användare.

 

Jag har suttit och funderat men kan inte komma på någon bra lösning, finns det någon som har någon bra idé.

 

[Monshi]

Dina data är ordnade över flera blad inom ett ark och du vill sortera ut de poster som har en notering ett visst datum? Låter nästan som om ett makro är vägen att gå men exakt hur är jag inte säker på. Jag antar att källan förändras med tiden, att det kan komma till och tas bort blad med data?

 

Man kan utforma det med användning av Excels sökfunktion, Find i VBA, eller kanske kanske via formler på bladet. Men det senare tror jag blir otympligt.

 

Kanske ett smart nyttjande av Avancerat filter i VBA kan användas även...

 

Har jag förstått upplägget rätt? Något jag ska jobba vidare med?

 

 

/T

 

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

 

[MH2]

1. Varför kan du inte använda en pivotabell? Förstår inte problemet.

2. Det absolut smidigaste alternativet är Data->avancerat filter->till annan plats.

 

Hursomhellst. Här är ett knep du använda om du absolut vill ha formler:

 

Antar

1 Det eftersökta datumet är inskrivet i cell F1 på "målbladet"

2. Källbladet heter [bok1]Blad1! (orginellt....)

 

För att få reda på vilka rader som innehåller använder ditt datum så kan du använda den här formeln:

 

=PRODUKTSUMMA(STÖRSTA(RAD([bok1]Blad1!$C$1:$C$100)*([bok1]Blad1!$C$1:$C$100=$F$1);RAD()))

 

 

Om du skriver in den på rad1 och kopierar ner den så kommer den att returnera radnumret för de C-celler som innehåller det datum du vill ha (fallande ordning).

 

För att returnera maskinnamnet i A-kolumnen så kan du skriva in följande formel (en rad):

=INDIREKT("[bok1]Blad1!A"&PRODUKTSUMMA(STÖRSTA(RAD([bok1]Blad1!$C$1:$C$100)*([bok1]Blad1!$C$1:$C$100=$F$1);RAD())))

 

Eller, för att slippa felvärden:

=OM(RAD()<=ANTAL.OM([bok1]Blad1!$C$1:$C$100;$F$1);INDIREKT("[bok1]Blad1!A"&PRODUKTSUMMA(STÖRSTA(RAD([bok1]Blad1!$C$1:$C$100)*([bok1]Blad1!$C$1:$C$100=$F$1);RAD())));"")

 

För att returnera serienumret så kan du helt enkelt byta ut A mot B i Indirekt formeln ("[bok1]Blad1!A"&). Ähhh, det vill säga

=OM(RAD()<=ANTAL.OM([bok1]Blad1!$C$1:$C$100;$F$1);INDIREKT("[bok1]Blad1!B"&PRODUKTSUMMA(STÖRSTA(RAD([bok1]Blad1!$C$1:$C$100)*([bok1]Blad1!$C$1:$C$100=$F$1);RAD())));"")

 

Du skriver alltså in formeln på rad 1 och kopierar/drar ner den.

 

PS,

Monshi, svarade inte vi på den här frågan alldeles nyss?

 

[inlägget ändrat 2007-04-02 18:48:58 av MH2]

[Monshi]

Monshi, svarade inte vi på den här frågan alldeles nyss?

Kanske det...Känner igen det du skrivit men minns inte när det var. FAST - det du gjort söker väl på ETT blad. Fast det är iofs inget problem att i praktiken skapa x-antal varianter av formeln som söker på varsitt blad.

 

Nackdelen med ditt förslag kan vara att om det är många träffar som sökes, stora mängder data som genomsökes, kan funktionerna du skrivit dra ned allmänna prestandan på bladet. Då kan det vara intressant att titta på en VBA-lösning.

 

Jag undrar om inte min lösning med PASSA är lite snabbare?

=PASSA($F$1;FÖRSKJUTNING([bok1]Blad1!$C1;G1;0;ANTALV([bok1]Blad1!$C:$C))-G1))

där då förutsättningarna lika som i din formel men formeln skrivs in i cell G2. Den behöver en tom cell, ett nollvärde, ovan sig för att fungera. Kopiera sedan ned den så långt som det antal värden du vill hämta. Returnerar #SAKNAS när ingen passande post hittas.

Sedan använder vi parhästen INDEX för att hämta värdena, exemplet datum:

=INDEX([bok1]Blad1!$A:$C);G2;3)

som kopieras ned utmed PASSA-formeln.

 

Då slipper vi massor med matrismultiplikationer. Men även här måste vis skapa en variant per blad och, om stora mängder data, kan även dessa påverka prestanda.

 

 

/T

 

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

 

[MattiasLu]

Jag tackar och bockar, det fungerar aldeles utmärkt. Dock så har jag ett listet spinn off problem. Källdatan finns i ca 15 olika excell blad.

Hur gör jag för att söka av dem alla?

 

[Monshi]

Vems funktioner tog du? Som sagt, tror mina är snabbare...

 

Men för att utföra det på ytterligare blad måste vi skapa en version av funktionen för varje blad. Vi kan lösa det lite generellt genom att skriva en funktion som hämtar bladnamnet från en cell eller så skriver du in bladnamnet för hand..

 

Först dock - varför kunde du inte använda Pivot-tabellen? Vore så mycket enklare!! På vilket sätt hindrar delningen av dokumentet användningen av Pivot?

 

Nåja, jag utgår från den funktion jag ställt upp:

Givet: Sökord/datum i F2

Bladnamn, som det ska sökas på givet i G2

Cell G3 tom eller med ett nollvärde

Cell G4 skrivs formeln in:

=PASSA($F$1;FÖRSKJUTNING(INDIREKT([GRÅ]"'[bok1]"[/GRÅ] & G$2 & [GRÅ]"'!C1"[/GRÅ]);G3;0;ANTALV(INDIREKT([GRÅ]"'[bok1]"[/GRÅ] &G$2 & [GRÅ]"'!A:C"[/GRÅ]))-G3;1);0)+G3

 

Dvs en INDIREKT sats tar bladnamnet från cellen och skapar en referens.

Om dina blad heter något numerärt räkningbart, som Blad1, Blad2 osv, så kan man skapa en formeln likt:

INDIREKT([GRÅ]"'[bok1]Blad"[/GRÅ] &KOLUMN()-KOLUMN($F$3) & [GRÅ]"'!a:c"[/GRÅ])

som räknar upp bladnumret när formeln kopieras åt höger.

 

Samma metod går givetvis att använda med MHs formler. Det är INDIREKT-satsen som är nyckeln.

 

Testa och se hur det fungerar. Räcker detta? Eller behövs det kanske ytterligare en formel som snyggar till det som formeln ovan hämtat?

 

HOPPSAN!

Min formeln igår var visst inte helt komplett ser jag nu, formlerna ovan är dock uppdaterade. För att visa hur det skulle sett ut igår:

=PASSA($F$1;FÖRSKJUTNING([bok1]Blad1!$C1;G1;0;ANTALV([bok1]B
lad1!$C:$C)-G1;1);0)+G1

 

Hoppas det stämmer nu, lätt att det blir lite fel med formler av denna art.

 

Edit:

Notera att INDIREKT fungerar enbart om den andra boken är öppen!

/T

 

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

 

 

[inlägget ändrat 2007-04-03 11:04:01 av Monshi]

 

[inlägget ändrat 2007-04-03 11:10:44 av Monshi]

[MattiasLu]

Hej.

 

Jag fick det att fungera med

=INDIREKT("[bok1]Blad1!A"&PRODUKTSUMMA(STÖRSTA(RAD([bok1]Bla
d1!$C$1:$C$100)*([bok1]Blad1!$C$1:$C$100=$F$1);RAD())))

 

Ang. Pivot tabell, så fungerar inte den funktionen när man har ett delat arbetsdokument. Annars hade det varit den självklara lösningen.

 

Funktionen ovan fungerar men eftersom det är ganska många blad som ska sökas vill jag helst inte ha resultaten i olika listor. En lista hade varit bäst.

 

Dock får jag inte din kod att fungera

=PASSA($F$1;FÖRSKJUTNING(INDIREKT("'[bok1]" & G$2 & "'!C1");G3;0;ANTALV(INDIREKT("'[bok1]" &G$2 & "'!A:C"))-G3;1);0)+G3

 

[Monshi]

Notera att min funktion ger dig radnumret för träffen. Den måste kompletteras med en INDEX-funktion. Man kan även "optimera" MHs funktion så man en gång räknar ut radnumret och sedan även där använder INDEX för att hämta värdena. Det är lönt att använda INDEX om det är en komplex uppslagning och man ska hämta mer än ett värde från varje rad.

 

INDEX blir i detta fall denna inte så vackra funktion:

=INDEX(INDIREKT("'[bok1]" & G$2 & "'!$A:$C");G4;1)

 

Funktionen ovan fungerar men eftersom det är ganska många blad som ska sökas vill jag helst inte ha resultaten i olika listor. En lista hade varit bäst.

Då får vi ta till en ytterligare funktion som ser till att smala upp dina data i en lista. Det går nog att ordna på något sätt, dvs formlerna ovan hämtar dina data till en plats och en annan formel, i sin tur, tittar på de resultaten och hämtar därifrån.

 

Ska titta på det. Tillsvidare - se mitt bildexempel, det fungerar visst!

 

 

/T

 

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

 

[bild bifogad 2007-04-03 11:33:44 av Monshi]

[Monshi]

Okej, här kommer en hög med formler

 

Ska se om jag kan strukturera det bra

..asch, titta på bifogad bild...

I A skriver vi:

Talet 1 i A2

(behövs ett startvärde för A och B-kolumnens formler)

I A3 skriver vi

=OM(ÄRFEL(FÖRSKJUTNING($H$4;B2;A2));A2+1;A2)

och kopierar ned så lång vi bara önskar.

I B2 skriver vi talet 0

I B3 skriver vi

=OM(A2=A3;B2+1;1)

I C3 skriver vi:

=OM(B3="";"";INDEX($I$2:$J$2;1;A3))

D3:

=OM(B3="";"";INDEX($I$4:$J$9;B3;A3))

och till sist i E3

=OM(B3="";"";INDEX(INDIREKT("'[bok1]" & C3 & "'!$A:$C");D3;1))

så hämtar vi det värde du vill ha ( i detta fall det i kolumn A på det blad som ett värde hittats på)

Förklaringar? Nej, ställ upp och se hur det fungerar.

 

Det formlerna INTE klarar av är om inget hittas på ett blad. Händer det? mmh, då har jag en liten nöt att knäcka....Om en tom rad i resultaten godtas så är det enkelt...

 

Edit:

Formlerna i I:K är de jag givit dig tidigare, bara flyttade en bit för att ge plats åt det nya.

 

/T

 

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

 

[bild bifogad 2007-04-03 11:57:34 av Monshi]

 

[inlägget ändrat 2007-04-03 11:58:52 av Monshi]

[MattiasLu]

Hmm, får det fortfarande inte till att fungera.

Något som jag har sett är att i mitt excell dokument så finns inte informationen i kol A till C, utan är utspritt i tre olika kollumner som inte är jämte varandra. Tror att det är därför jag inte får din funktion att fungera.

 

Det formlerna INTE klarar av är om inget hittas på ett blad. Händer det? mmh, då har jag en liten nöt att knäcka....Om en tom rad i resultaten godtas så är det enkelt...

 

Jepp, ibland finns det ingen match på ett blad.

 

Och sedan fungerar inte

=INDIREKT("Kund!C"&PRODUKTSUMMA(STÖRSTA(RAD(´Kund!$N$7:$N$1000)*(Kund!$N$7:$N$1000=$P$3);RAD())))

så bra som jag trodde. Finns det 20 rader som har rätt datum i blad "Kund" så blir resultat så får jag bara ut 13st i listan.

För att förklara lite mer, på blad "Kund" i kolumnen C finns maskin modell som jag vill ha ut i listan, kol N finns datum som jag söker och matchar emot.

 

[Monshi]

Jepp, ibland finns det ingen match på ett blad.

 

ett steg i taget då, vi ser till att få set ett att fungera först.

Något som jag har sett är att i mitt excell dokument så finns inte informationen i kol A till C, utan är utspritt i tre olika kollumner som inte är jämte varandra. Tror att det är därför jag inte får din funktion att fungera.

Bara att anpassa min för det då? Ska den söka i N? Ändra då referensen.

Men det ska sägas att det blir lite krångligare om inte alla blad ser lika ut. Då kan det vara bra att plocka ut den informationen, likt bladnamnet, ut formeln.

 

Ändrar sig dokumentet med tiden? Kommer nya blad till medan andra faller ifrån och kan sökområdena på de nya vara andra än på de gamla?

 

Det börjar då som om lite mer formler kan behövas, formler som letar reda på vilken kolumn som det ska sökas i! För rubriker har du väl på kolumnerna och dessa är väl desamma mellan bladen?

 

 

Mao, jag skulle då tänka mig att för varje blad sker en sökning på var dina data finns, vilka kolumner. Sedan använder man FÖRSKJUTNING tillsammans med INDIREKT, eller kanske bara INDIREKT, för att bygga upp referenser till dessa kolumner.

 

ELLER - så skriver vi ett generellt makro som fixar det åt dig. Fast även där blir det mycket mycket enklare om dina blad är lika formaterade. Att de inte är lika formaterade diskvalificerar förövrigt troligtvis Pivottabelln.

 

 

Vad får du förresten för fel med min funktion? Begriper du vad våra funktioner gör förresten?

 

/T

 

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

 

[MH2]

Och sedan fungerar inte ..... ..

Kräver som sagt var att formlerna "börjar" i rad1. Om du startar från rad7 så kommer den att ignorera de 7 "största" maskinerna. Vill du börja på rad sju så får du göra ett tillägg:

=INDIREKT("Kund!C"&PRODUKTSUMMA(STÖRSTA(RAD(´Kund!$N$7:$N$1000)*(Kund!$N$7:$N$1000=$P$3);RAD()-6)))

 

Intressant, Inlägget ändras innan det skrivits

[inlägget ändrat 2007-04-03 14:28:42 av MH2]