Det meste af tiden er at finde en celle i et Excel-regneark temmelig let; hvis du ikke bare kan scanne gennem rækker og kolonner for det, kan du bruge CTRL + F til at søge efter det. Men hvis du arbejder med et rigtigt stort regneark Sådan splitses et stort CSV Excel-regneark i separate filer Sådan splitses et stort CSV Excel-regneark i separate filer En af Microsoft Excel's mangler er den begrænsede størrelse af et regneark. Hvis du skal gøre din Excel-fil mindre eller opdele en stor CSV-fil, læs videre! Læs mere, det kan spare meget tid for at bruge en af disse fire opslagsfunktioner. Uanset størrelsen af dit Microsoft Excel-dokument, bliver de meget effektivere.
VLOOKUP-funktionen
Denne funktion giver dig mulighed for at angive en kolonne og en værdi, og returnerer en værdi fra den tilsvarende række i en anden kolonne (hvis det ikke giver mening, bliver det klart om et øjeblik). To eksempler, hvor du måske gør dette, kigger efter medarbejderens efternavn ved deres medarbejdernummer eller finder et telefonnummer ved at angive et efternavn. Her er funktionens syntaks:
= VLOOKUP ([lookup_value], [table_array], [col_index_num], [range_lookup])
[Lookup_value] er det informationsstykke, du allerede har; for eksempel, hvis du har brug for at vide, hvilken stat en by er i, ville det være navnet på byen. [table_array] lader dig angive de celler, hvor funktionen vil søge efter opkalds- og returværdier. Når du vælger dit interval, skal du være sikker på at den første kolonne, der er inkluderet i din matrix, er den, der vil indeholde din opslagsværdi! Dette er afgørende. [col_index_num] er nummeret på kolonnen, der indeholder returværdien.
[range_lookup] er et valgfrit argument, og tager 1 eller 0. Hvis du indtaster 1 eller udelad dette argument, søger funktionen efter den indtastede værdi eller det næstbedste nummer. Så i billedet nedenfor vil en VLOOKUP, der leder efter en SAT-score på 652, returnere 646, da det er det nærmeste nummer i listen, der er mindre end 652, og [range_lookup] som standard er 1.
Lad os se på, hvordan vi kan bruge dette. Som jeg har i mine artikler om boolese operatører i Microsoft Excel Mini Excel-vejledning: Brug Boolean Logic til at behandle komplekse data Mini Excel-vejledning: Brug Boolean Logic til at behandle komplekse datalogiske operatører, hvis ikke, og og og eller kan hjælpe dig med at komme fra Excel nybegynder til strømbruger. Vi forklarer de grundlæggende funktioner i hver funktion og demonstrerer, hvordan du kan bruge dem til maksimale resultater. Læs mere og avanceret tælling og tilføjelse af Mini Excel-vejledning: Brug af avanceret tælling og tilføjelse af funktioner i Excel Mini Excel-vejledning: Brug af avanceret tælling og tilføjelse af funktioner i Excel Tælle og tilføje formler kan forekomme jordiske i forhold til mere avancerede Excel-formler. Men de kan hjælpe dig med at spare meget tid, når du skal indsamle oplysninger om dataene i dit regneark. Læs mere, jeg bruger et regneark, som jeg genererede med generatedata.com. Den indeholder ID-numre, for- og efternavne, by, stat og SAT-score. Lad os sige, at jeg vil finde SAT-score for en person med efternavnet "Winters." VLOOKUP gør det nemt. Her er den formel, vi ville bruge:
= VLOOKUP ("Winters", C2: F101, 4, 0) Fordi SAT-scorerne er den fjerde søjle over fra efternavnet kolonnen, har jeg brugt 4 for kolonneindekset argumentet. Bemærk, at når du leder efter tekst, er indstilling af [range_lookup] til 0 en god ide; uden det kan du få dårlige resultater. Her er hvad Microsoft Excel giver os:
Det vendte tilbage til 651, SAT-score tilhørende den studerende, der hedder Kennedy Winters, som er i række 92 (vist i indsatsen ovenfor). Det ville have taget meget længere tid at rulle gennem kigge efter navnet end det gjorde for hurtigt at skrive syntaksen!
VLOOKUP kan også være ganske nyttigt, hvis du bruger Microsoft Excel til at gøre dine skatter Gør dine skatter? 5 Excel-formler, du skal vide, gør dine skatter? 5 Excel-formler Du skal vide Det er to dage før dine skatter forfalder, og du ønsker ikke at betale et andet forsinkelsesgebyr. Dette er tid til at udnytte magten i Excel for at få alt i orden. Læs mere .
Noter om VLOOKUP
Et par ting er gode at huske, når du bruger VLOOKUP. For det første skal du sørge for, at den første kolonne i dit interval er den, der indeholder din opslagsværdi, som jeg nævnte tidligere. Hvis den ikke er i den første kolonne, returnerer funktionen forkerte resultater. Hvis dine kolonner er velorganiserede, bør det ikke være et problem.
Den anden ting at huske på er, at VLOOKUP kun vil returnere en værdi. Hvis vi havde brugt "Georgia" som opslagsværdi, ville det have returneret scoringen fra den første studerende fra Georgien og givet ingen indikation for, at der faktisk er to studerende fra Georgien.
HLOOKUP-funktionen
Hvor VLOOKUP finder tilsvarende værdier i en anden kolonne, finder HLOOKUP tilsvarende værdier i en anden række. Fordi det normalt er lettere at scanne gennem kolonneoverskrifter, indtil du finder den rigtige og bruge et filter til at finde det, du leder efter, er HLOOKUP bedst brugt, når du har rigtige store regneark, eller du arbejder med værdier, der er organiseret efter tid . Her er syntaxen:
= HLOOKUP ([lookup_value], [table_array], [row_index_num], [range_lookup])
[lookup_value], igen, er den værdi, du ved, og du vil finde en tilsvarende værdi for. [table_array] er de celler, du vil søge i. [row_index_num] angiver rækken, som returværdien kommer fra. Og [range_lookup] er det samme som ovenfor; lad det være tomt for at få den nærmeste værdi, når det er muligt, eller indtast 0 for kun at søge efter nøjagtige kampe.
I dette eksempel har jeg oprettet et nyt regneark med generatedata.com. Denne indeholder en række for hver stat, sammen med en SAT-score (vi siger, at det er den gennemsnitlige score for staten) i årene 2000-2014. Vi bruger HLOOKUP til at finde den gennemsnitlige score i Minnesota i 2013. Sådan gør vi det:
= HLOOKUP (2013, A1: P51, 24)
(Bemærk at 2013 ikke er indeholdt i citater, fordi det er et tal og ikke en streng, også de 24 kommer fra Minnesota i den 24. række.) Som du kan se i billedet nedenfor, returneres scoren:
Minnesotans i gennemsnit en score på 1014 i 2013.
Noter om HLOOKUP
Som med VLOOKUP skal opslagsværdien være i den første række af din tabel array; Dette er sjældent et problem med HLOOKUP, da du normalt bruger en kolonne titel for en opslagsværdi. HLOOKUP returnerer også kun en enkelt værdi.
INDEX- og MATCH-funktionerne
INDEX og MATCH er to forskellige funktioner, men når de bruges sammen, kan de gøre det meget hurtigere at søge et stort regneark. Begge funktioner har ulemper, men ved at kombinere dem bygger vi på begge sider. For det første, syntaxen:
= INDEX ([array], [row_number], [column_number]) = MATCH ([lookup_value], [lookup_array], [match_type])
I INDEX er [array] det array, hvor du søger. [row_number] og [column_number] kan bruges til at indsnævre din søgning; vi tager et kig på det om et øjeblik.
MATCHs [lookup_value] er et søgeord, der kan være en streng eller et tal; [lookup_array] er det array, hvor Microsoft Excel vil søge efter søgeordet. [match_type] er et valgfrit argument, der kan være 1, 0 eller -1; 1 returnerer den største værdi, der er mindre end eller lig med dit søgeord 0 vil kun returnere dit nøjagtige udtryk og -1 returnerer den mindste værdi, der er større end eller lig med dit søgeord.
Det kan ikke være klart, hvordan vi skal bruge disse to funktioner sammen, så jeg lægger det ud her. MATCH tager et søgeord og returnerer en cellehenvisning. I billedet nedenfor kan du se, at i en søgning efter værdien 646 i kolonne F returneres MATCH 4.
INDEX på den anden side gør det modsatte: det tager en cellereference og returnerer værdien i den. Du kan se her at INDEX returnerer "Forankring", når værdien er angivet fra række 6, når den bliver bedt om at vende tilbage til kolonnen Byens kolonne.
Hvad vi skal gøre er at kombinere de to, så MATCH returnerer en cellehenvisning, og INDEX bruger denne henvisning til at opsøge værdien i en celle. Lad os sige, at du husker, at der var en elev, hvis efternavn var Waters, og du vil se, hvad denne studerendes score var. Her er den formel, vi vil bruge:
= INDEX (F: F, MATCH ("Waters", C: C, 0)) Du vil bemærke, at kamptypen er indstillet til 0 her; når du leder efter en streng, så er det du vil bruge. Her er hvad vi får, når vi kører den funktion:
Som du kan se fra indsatsen, scorede Owen Waters 1720, nummeret der vises, når vi kører funktionen. Det kan ikke virke som det er nyttigt, når du bare kan se et par kolonner over, men forestil dig, hvor meget tid du vil spare, hvis du skulle gøre det 50 gange på en stor database regneark Excel Vs. Adgang - Kan et regneark erstatte en database? Excel Vs. Adgang - Kan et regneark erstatte en database? Hvilket værktøj skal du bruge til at administrere data? Adgang og Excel har begge data filtrering, sortering og forespørgsel. Vi viser dig hvilken som passer bedst til dine behov. Læs mere, der indeholdt flere hundrede søjler!
Lad søgningen begynde
Microsoft Excel har mange ekstremt kraftfulde funktioner 3 Crazy Excel-formler, der gør fantastiske ting 3 Crazy Excel-formler, der gør fantastiske ting Microsoft Excel's kraft ligger i formlerne. Lad mig vise dig de vidundere, du kan gøre med formler og betinget formatering i tre nyttige eksempler. Læs mere, og de fire ovenfor nævnte skal kun ridse overfladen. Selv med dette overskuelige overblik bør du dog spare meget tid, når du arbejder med store regneark. Hvis du vil se, hvor meget mere du kan gøre med INDEX, skal du tjekke "The Imposing Index" på Microsoft Excel Hero.
Hvordan kører du søgninger i store regneark? Hvordan har du fundet disse opslagsfunktioner til at være nyttige? Del dine tanker og oplevelser nedenfor!
Billedkreditter: forstørrelsesglas på et regneark via shutterstock