4 Excel-sökfunktioner för att söka kalkylblad effektivt

• Produktivitet

Annons

För det mesta är det ganska enkelt att söka i ett Microsoft Excel-kalkylblad. Om du inte bara kan skanna igenom raderna och kolumnerna kan du använda Ctrl + F för att söka efter det. Om du arbetar med ett riktigt stort kalkylblad kan det spara mycket tid att använda en av dessa fyra uppslagningsfunktioner.

Lås upp fuskbladet "Essential Excel Formulas" nu!

Detta kommer att registrera dig för vårt nyhetsbrev

När du väl vet hur du söker i Excel med uppslag, spelar det ingen roll hur stort dina kalkylblad blir, kommer du alltid kunna hitta något i Excel!

1. VLOOKUP-funktionen

Med den här funktionen kan du ange en kolumn och ett värde och returnerar ett värde från motsvarande rad i en annan kolumn (om det inte är vettigt kommer det att bli klart på ett ögonblick). Två exempel där du kan göra detta är att leta upp en anställds efternamn efter deras anställds nummer eller hitta ett telefonnummer genom att ange ett efternamn.

Här är syntaxen för funktionen:

 =VLOOKUP([lookup_value], [table_array], [col_index_num], [range_lookup]) 

• [lookup_value] är den information du redan har. Om du till exempel behöver veta vilken stat en stad är i skulle det vara stadens namn.
• [table_array] låter dig ange cellerna där funktionen ska leta efter uppslagnings- och retursvärden. När du väljer ditt intervall, se till att den första kolumnen som ingår i din matris är den som kommer att inkludera ditt sökvärde!
• [col_index_num] är numret på kolumnen som innehåller returvärdet.
• [range_lookup] är ett valfritt argument och tar 1 eller 0. Om du anger 1 eller utelämnar detta argument letar funktionen efter det värde du angav eller det näst lägsta siffran. Så på bilden nedan kommer en VLOOKUP som letar efter en SAT-poäng på 652 att returnera 646, eftersom det är det närmaste numret i listan som är mindre än 652, och [range_lookup] är standardvärdet till 1.

Låt oss ta en titt på hur du kan använda detta. Detta kalkylblad innehåller ID-nummer, för- och efternamn, stad-, delstat- och SAT-poäng. Låt oss säga att du vill hitta SAT-poäng för en person med efternamnet “Winters.” VLOOKUP gör det enkelt. Här är formeln du skulle använda:

 =VLOOKUP("Winters", C2:F101, 4, 0) 

Eftersom SAT-poängen är den fjärde kolumnen från kolumnen för efternamn är 4 kolumnindexargumentet. Observera att när du letar efter text är det en bra idé att ställa [range_lookup] till 0. Utan det kan du få dåliga resultat.

Här är resultatet:

Den returnerade 651, SAT-poäng tillhörde studenten vid namn Kennedy Winters, som ligger i rad 92 (visas i insatsen ovan). Det skulle ha tagit mycket längre tid att bläddra igenom leta efter namnet än det gjorde för att snabbt skriva ut syntaxen!

Anteckningar om VLOOKUP

Några saker är bra att komma ihåg när du använder VLOOKUP. Se till att den första kolumnen i ditt intervall är den som innehåller ditt sökvärde. Om det inte finns i den första kolumnen kommer funktionen att ge felaktiga resultat. Om dina kolumner är välorganiserade bör det inte vara ett problem.

Tänk också på att VLOOKUP bara någonsin kommer att returnera ett värde. Om du hade använt "Georgia" som uppslagningsvärde, skulle det ha återgivit poängen för den första studenten från Georgien och inte visat att det i själva verket finns två studenter från Georgien.

2. HLOOKUP-funktionen

Där VLOOKUP hittar motsvarande värden i en annan kolumn, hittar HLOOKUP motsvarande värden i en annan rad. Eftersom det vanligtvis är enklast att söka igenom kolumnrubriker tills du hittar rätt och använder ett filter för att hitta det du letar efter, används HLOOKUP bäst när du har riktigt stora kalkylblad eller om du arbetar med värden som är organiserade av tiden .

Här är syntaxen för funktionen:

 =HLOOKUP([lookup_value], [table_array], [row_index_num], [range_lookup]) 

• [lookup_value] är det värde du känner till och vill hitta ett motsvarande värde för.
• [table_array] är cellerna där du vill söka.
• [row_index_num] anger den rad som returvärdet kommer från.
• [range_lookup] är samma som i VLOOKUP, lämna det tomt för att få det närmaste värdet när det är möjligt, eller ange 0 för att bara leta efter exakta matchningar.

Detta kalkylblad innehåller en rad för varje tillstånd, tillsammans med en SAT-poäng under åren 2000–2014. Du kan använda HLOOKUP för att hitta den genomsnittliga poängen i Minnesota 2013. Så gör vi det:

 =HLOOKUP(2013, A1:P51, 24) 

Som ni ser på bilden nedan returneras poängen:

Minnesotans var i genomsnitt en poäng på 1014 2013. Observera att 2013 inte finns i citat eftersom det är ett nummer och inte en sträng. Dessutom kommer de 24 från Minnesota på 24: e raden.

Anteckningar om HLOOKUP

Liksom med VLOOKUP måste uppslagningsvärdet vara i den första raden i din tabellgrupp. Detta är sällan ett problem med HLOOKUP, eftersom du vanligtvis använder en kolumntitel för ett sökvärde. HLOOKUP returnerar bara ett enda värde.

3-4. INDEX- och MATCH-funktionerna

INDEX och MATCH är två olika funktioner, men när de används tillsammans kan de göra att söka i ett stort kalkylblad mycket snabbare. Båda funktionerna har nackdelar, men genom att kombinera dem bygger vi på styrkorna hos båda.

Först men syntaxen för båda funktionerna:

 =INDEX([array], [row_number], [column_number]) 

• [array] är matrisen där du söker.
• [rad_nummer] och [kolumnnummer] kan användas för att begränsa din sökning (vi tittar på det på ett ögonblick.)

 =MATCH([lookup_value], [lookup_array], [match_type]) 

• [lookup_value] är ett sökord som kan vara en sträng eller ett nummer.
• [lookup_array] är den matris där Microsoft Excel letar efter söktermen.
• [match_type] är ett valfritt argument som kan vara 1, 0 eller -1. 1 kommer att returnera det största värdet som är mindre än eller lika med ditt sökord. 0 kommer bara att returnera din exakta term, och -1 kommer att returnera det minsta värdet som är större än eller lika med ditt sökord.

Det kanske inte är klart hur vi ska använda dessa två funktioner tillsammans, så jag lägger upp det här. MATCH tar en sökterm och returnerar en cellreferens. I bilden nedan kan du se att i en sökning efter värdet 646 i kolumn F returnerar MATCH 4.

INDEX gör å andra sidan det motsatta: det tar en cellreferens och returnerar värdet i den. Här kan du se att när INDEX får besked om att returnera den sjätte cellen i City-kolumnen returnerar "Anchorage" värdet från rad 6.

Det vi ska göra är att kombinera de två så att MATCH returnerar en cellreferens och INDEX använder den referensen för att slå upp värdet i en cell. Låt oss säga att du kommer ihåg att det fanns en student vars efternamn var Waters, och du vill se vad den här studentens poäng var. Här är formeln vi använder:

 =INDEX(F:F, MATCH("Waters", C:C, 0)) 

Du kommer att märka att matchningstypen är inställd på 0 här. När du letar efter en sträng är det vad du vill använda. Här är vad vi får när vi kör den funktionen:

Som ni kan se från insatsen fick Owen Waters 1720, antalet som visas när vi kör funktionen. Detta kanske inte verkar så användbart när du bara kan titta över några kolumner, men föreställ dig hur mycket tid du skulle spara om du var tvungen att göra det 50 gånger i ett stort databas-kalkylblad Excel Vs. Tillgång - Kan ett kalkylblad ersätta en databas? Excel Vs. Tillgång - Kan ett kalkylblad ersätta en databas? Vilket verktyg ska du använda för att hantera data? Access och Excel har båda datafiltrering, sortering och frågeställningar. Vi visar dig vilken som passar bäst för dina behov. Läs mer som innehöll flera hundra kolumner!

Låt Excel-sökningarna börja

Microsoft Excel har många extremt kraftfulla funktioner för att manipulera data, och de fyra som listas ovan skrapar bara på ytan. Att lära sig använda dem kommer att göra ditt liv mycket enklare.

Om du verkligen vill behärska Microsoft Excel kan du verkligen dra nytta av att behålla Essential Excel Cheat Sheet De väsentliga Microsoft Excel-formlerna och -funktionerna Cheat Sheet De väsentliga Microsoft Excel-formlerna och -funktionerna Cheat Sheet Ladda ner Excel-formlerna fuskark för att ta en genväg genom världens favorit kalkylprogram. Läs mer nära till hands!

Bildkredit: Cico / Shutterstock

Utforska mer om: Microsoft Excel, söktrick, kalkylblad.