4 функции за търсене в Excel за ефективно търсене на електронни таблици

4 функции за търсене в Excel за ефективно търсене на електронни таблици

През повечето време търсене на a Microsoft Excel електронната таблица е доста лесна. Ако не можете просто да прегледате редовете и колоните за него, можете да използвате Ctrl + F, за да го потърсите. Ако работите с наистина голяма електронна таблица, това може да спести много време за използване на една от тези четири функции за търсене.





След като знаете как да търсите в Excel с помощта на търсене, няма значение колко големи ще бъдат вашите електронни таблици, винаги ще можете да намерите нещо в Excel!



1. Функцията VLOOKUP

Тази функция ви позволява да посочите колона и стойност и ще върне стойност от съответния ред на различна колона (ако това няма смисъл, ще стане ясно след малко). Два примера, в които можете да направите това, са да потърсите фамилията на служител по неговия номер на служител или да намерите телефонен номер, като посочите фамилия.





Ето синтаксиса на функцията:

=VLOOKUP([lookup_value], [table_array], [col_index_num], [range_lookup])
  • [lookup_value] е информацията, която вече имате. Например, ако трябва да знаете в какво състояние се намира един град, това би било името на града.
  • [таблица_масив] ви позволява да посочите клетките, в които функцията ще търси стойностите за търсене и връщане. Когато избирате диапазона си, уверете се, че първата колона, включена във вашия масив, е тази, която ще включва вашата стойност за търсене!
  • [col_index_num] е номерът на колоната, която съдържа връщаната стойност.
  • [range_lookup] е незадължителен аргумент и приема 1 или 0. Ако въведете 1 или пропуснете този аргумент, функцията търси въведената от вас стойност или следващото най-ниско число. Така че на изображението по -долу VLOOKUP, който търси SAT резултат от 652, ще върне 646, тъй като това е най -близкото число в списъка, което е по -малко от 652, а [range_lookup] по подразбиране е 1.

Нека да разгледаме как можете да използвате това. Тази електронна таблица съдържа идентификационни номера, име и фамилия, град, щат и резултати от SAT. Да предположим, че искате да намерите SAT резултата на човек с фамилно име „Уинтърс“. VLOOKUP го прави лесно. Ето формулата, която бихте използвали:



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

Тъй като резултатите от SAT са четвъртата колона от колоната с фамилното име, 4 е аргументът за индекса на колоната. Имайте предвид, че когато търсите текст, задаването на [range_lookup] на 0 е добра идея. Без него можете да получите лоши резултати.

Ето резултата:



Той върна 651, резултатът от SAT, принадлежащ на ученика на име Кенеди Уинтерс, който е в ред 92 (показан в вмъкването по -горе). Щеше да отнеме много повече време, за да превъртите в търсене на името, отколкото за бързо въвеждане на синтаксиса!

Бележки за VLOOKUP

Няколко неща е добре да запомните, когато използвате VLOOKUP. Уверете се, че първата колона във вашия диапазон е тази, която включва вашата стойност за търсене. Ако не е в първата колона, функцията ще върне неправилни резултати. Ако колоните ви са добре организирани, това не би трябвало да е проблем.



Също така, имайте предвид, че VLOOKUP винаги ще връща само една стойност. Ако бяхте използвали „Джорджия“ като стойност за търсене, това щеше да върне резултата на първия студент от Джорджия и без да има индикации, че всъщност има двама студенти от Джорджия.

2. Функцията HLOOKUP

Когато VLOOKUP намира съответните стойности в друга колона, HLOOKUP намира съответните стойности в различен ред. Тъй като обикновено е най -лесно да сканирате през заглавията на колони, докато не намерите правилния и да използвате филтър, за да намерите това, което търсите, HLOOKUP е най -добре да се използва, когато имате наистина големи електронни таблици или работите със стойности, организирани по време .

Ето синтаксиса на функцията:

=HLOOKUP([lookup_value], [table_array], [row_index_num], [range_lookup])
  • [lookup_value] е стойността, за която знаете и искате да намерите съответната стойност.
  • [таблица_масив] е клетките, в които искате да търсите.
  • [ред_индекс_ номер] посочва реда, от който ще дойде връщаната стойност.
  • [range_lookup] е същото като във VLOOKUP, оставете го празно, за да получите най -близката стойност, когато е възможно, или въведете 0, за да търсите само точни съвпадения.

Тази електронна таблица съдържа ред за всеки щат, заедно със SAT резултат през 2000–2014 г. Можете да използвате HLOOKUP, за да намерите средния резултат в Минесота през 2013 г. Ето как ще го направим:

=HLOOKUP(2013, A1:P51, 24)

Както можете да видите на изображението по -долу, резултатът се връща:

Минесотанс средна оценка от 1014 през 2013 г. Имайте предвид, че 2013 г. не е в кавички, защото е число, а не низ. Също така, 24 идва от Минесота, който е на 24 -ия ред.

Ето тук как да се изчисли среднопретеглената стойност в Excel .

Бележки относно HLOOKUP

Както при VLOOKUP, стойността за търсене трябва да бъде в първия ред на вашия масив от таблици. Това рядко е проблем с HLOOKUP, тъй като обикновено ще използвате заглавие на колона за стойност за търсене. HLOOKUP също връща само единична стойност.

3-4. Функциите INDEX и MATCH

INDEX и MATCH са две различни функции, но когато се използват заедно, те могат да направят търсенето на голяма електронна таблица много по -бързо. И двете функции имат недостатъци, но като ги комбинираме, ще надграждаме силните страни на двете.

Първо обаче синтаксисът на двете функции:

=INDEX([array], [row_number], [column_number])
  • [масив] е масивът, в който ще търсите.
  • [ред_номер] и [номер на колона] може да се използва за стесняване на търсенето (ще го разгледаме след малко.)
=MATCH([lookup_value], [lookup_array], [match_type])
  • [lookup_value] е термин за търсене, който може да бъде низ или число.
  • [lookup_array] е масивът, в който Microsoft Excel ще търси думата за търсене.
  • [match_type] е незадължителен аргумент, който може да бъде 1, 0 или -1. 1 ще върне най -голямата стойност, която е по -малка или равна на думата Ви за търсене. 0 ще върне само точния ви термин, а -1 ще върне най -малката стойност, която е по -голяма или равна на вашата дума за търсене.

Може да не е ясно как ще използваме тези две функции заедно, затова ще го изложа тук. MATCH приема термин за търсене и връща препратка към клетка. На изображението по -долу можете да видите, че при търсене на стойността 646 в колона F MATCH връща 4.

INDEX, от друга страна, прави обратното: взема препратка към клетка и връща стойността в нея. Тук можете да видите, че когато бъде казано да върне шестата клетка от колоната City, INDEX връща „Anchorage“, стойността от ред 6.

Това, което ще направим, е да комбинираме двете, така че MATCH връща препратка към клетка, а INDEX използва тази препратка, за да търси стойността в клетка. Да речем, че си спомняте, че имаше студент с фамилия Уотърс и искате да видите какъв е резултатът на този ученик. Ето формулата, която ще използваме:

как да си направите нов акаунт в playstation
=INDEX(F:F, MATCH('Waters', C:C, 0))

Ще забележите, че типът на съвпадението е зададен на 0 тук. Когато търсите низ, това ще искате да използвате. Ето какво получаваме, когато изпълняваме тази функция:

Както можете да видите от вмъкването, Оуен Уотърс отбеляза 1720, числото, което се появява, когато изпълняваме функцията. Това може да не изглежда толкова полезно, когато можете просто да прегледате няколко колони, но си представете колко време бихте спестили, ако трябва да го направите 50 пъти на голяма електронна таблица на база данни който съдържаше няколкостотин колони!

Нека търсенията в Excel започнат

Microsoft Excel има много изключително мощни функции за манипулиране на данни, а четирите изброени по -горе просто надраскват повърхността. Научаването как да ги използвате ще направи живота ви много по -лесен.

Ако наистина искате да овладеете Microsoft Excel, наистина бихте могли да се възползвате от поддържането на Essential Excel Cheat Sheet близо до вас!

Кредит на изображението: Cico/ Shutterstock

Дял Дял Туит електронна поща Canon срещу Nikon: Коя марка фотоапарат е по -добра?

Canon и Nikon са двете най -големи имена в камерата. Но коя марка предлага по -добрата гама от фотоапарати и обективи?

Прочетете Напред Свързани теми
  • Производителност
  • Електронна таблица
  • Microsoft Excel
  • Трикове за търсене
За автора Иън Бъкли(216 статии са публикувани)

Иън Бъкли е журналист на свободна практика, музикант, изпълнител и видео продуцент, живеещ в Берлин, Германия. Когато не пише или е на сцената, той се занимава с „направи си сам“ електроника или код с надеждата да стане луд учен.

Още от Иън Бъкли

Абонирайте се за нашия бюлетин

Присъединете се към нашия бюлетин за технически съвети, рецензии, безплатни електронни книги и изключителни оферти!

Щракнете тук, за да се абонирате