Paano Kumuha ng Data sa Spreadsheet gamit ang VLOOKUP, MATCH at INDEX

() translation by (you can also view the original English article)

Kapag kailangan mong maghanap at kumuha ng isang hanay ng data sa isang table atilagay ito sa ibang table, gamitin ang VLOOKUP na funciton. Ang function na ito ay gumagana sa kahit anong bersyon ng Excel sa Windows at Mac, at pati na rin sa Google Sheets. Pinapahintulotan ka nitong mahanap ang data sa isang table gamit ang isang katangian na kaparehas nito sa kabilang table. Ang dalawang table ay maaaring nasa magkaibang sheets o kahit nasa magkaibang workbooks. Mayroon ding HLOOKUP function, na parehas din ang ginagawa, ngunit and data ay nakaayos pahalang, sa magkabilaang hilera.

Ang MATCH at INDEX na function ay mabuting gamitin kapag ang iyong ukol ayang kinalalagyan ng isang partikular na data, gaya ng hanay o hilera na naglalaman ngpangalan ng tao.

Premium na Pagpipilian

Bago tayo mapunta sa mga function ng Excel, alam mo ba na ang Envato Marketay mayroong saklaw ng mga Excel scripts at plugins na nagpapahintulot sa iyo na gumawa ng mga advance na function?

Halimbawa, maaaring kang:

Excel scripts and plugins on Envato Market — Excel Scripts at plugins sa Envato Market

Screencast

Kung gusto mong sumunod sa tutorial na ito, maaari mong gamitin ang sarili mong Excelfile. O kung gusto mo, maaari mong i-download ang zip file na kasama sa tutorial na ito, na may lamang isang halimbawang workbook na may pamagat na vlookup example.xlsx.

Paggamit ng VLOOKUP

Kapag ang VLOOKUP ay nahanap ang pagkakakilanlan na tinutukoy mo sa pinagmulan ng data, maaari nitong mahanap ang kahit anumang cell sa isang hilera at ibalik ang inpormasyon sa iyo. Tandaan na sa pinagmulan ng data, ang pagkakakilanlan ay dapat nasa unang hanay ng table.

unique identifiers for vlookup — Ang isang kakaibang pagkakakilanlan ay dapat kaparehas ng isang serial number, na kung saan ay wala itong kaparehas sa parehong table.

Syntax

Ang syntax ng VLOOKUP na function ay:

=VLOOKUP(lookup value, table range, column number, [truefalse])

Heto ang ibig sabihin ng mga argumentong ito:

Lookup value. Ang cell na naglalaman ng kakaibang pagkakakilanlan.
Table range. Ang sakop ng cells na mayroong pagkakakilanlan sa unang hanay, kasunod ng lahat ng natirang data sa mga kabilang hanay.
Column number. Ang numero ng isang hanay na nandoon ang data na iyong hinahanap. Huwag maguluhan sa letra ng hanay. Sa larawan na nasa itaas, ang states ay nasa ika-4 na hanay.
True/False. Ang argumentong ito ay pagpipilian. Ang True ay nangangahulugang humigit-kumulang na kapantay ay tinatanggap, at False ay nangangahulugang na tanging tumpak na kapantay lamang ang tinatanggap.

Gusto nating hanapin ang sales amounts galing sa table sa larawan na nasa itaas,kaya gamitin natin ang argumentong:

syntax of vlookup — Syntax ng VLOOK na function

Pagtakda ng Range Name upang Makalikha ng Tiyak na Reperensiya

Sa Vlookup example.xlsx, tignan ang Sales Amounts worksheet. Ilagay natin ang pormula sa B5, at gamitin ang AutoFill na feature para kopyahin angpormula hanggang sa pinakadulo ng sheet. Ibig sabihin na ang hanay ng table na nasa formula ay kailangangmaging isang tiyak na reperensiya. Ang mainam na paraan upang gawin ito ay ang pagtakda ng isang pangalan parasa sakop ng table.

Pagtakda ng Range Name sa Excel

Bago ilagay ang pormula, pumunta sa source data worksheet.
Piliin ang lahat ng cells sa A4 (ang header para sa Order # na hanay) pababa hanggang H203. Isang mabilis na paraan upang magawa ito ay pindutin ang A4, pagkatapos ay pindutin ang Ctrl-Shift-End (Command-Shift-End sa Mac).
Pumindot sa loob ng Name Box na nasa taas ng hanay A (ang Name Box ngayon ay pinapakita ang A4).
Ilagay ang data, pagkatapos ay pindutin ang Enter.
Maaari mo ng magamit ang name data na nasa pormula imbes na ang $A$4:$H$203.

Name box displaying a range name — Ang name box ay karaniwang naglalahad ng kasalukuyang kinalalagyan ng cell. Pindutin ang loob nito at ilagay ang pangalan upang matakda ang sakop nito.

Pagtakda ng Range name sa Google Sheets

Sa google sheets, ang pagtakda ng pangalan ay may kaunting kaibahan.

Pindutin ang unang hanay na header ng iyong source data, pagkatapos ay pindutin ang Ctrl-Shift-Right Arrow (Command-Shift-Right Arrow sa Mac). Tapos ay piliin ang hilera ng hanay na headers.
Pindutin ang Ctrl-Shift-Down Arrow (Command-Shift-Down Arrow sa Mac). Pipiliin nito ang aktwal na data.
Pindutin ang Data menu, tapos ay piliin ang Named at protected ranges.
Sa Name at protected ranges na kahon sa kanan, ilagay ang data, tapos ay pindutin ang Done.

Data menu in Google Sheets — Pagtakda ng Range name sa Google Sheets

Paglagay ng Pormula

Para malagay ang pormula, pumunta sa Sales Amounts worksheet at pindutin ang B5.

Ilagay ang pormula:

=VLOOKUP(A5,data,8,FALSE)

Pindutin ang Enter.

entering the vlookup function — Paglagay ng VLOOKUP na function

Ang resulta ay dapat 40. Para punuin ang values sa hanay, pindutinulit ang B5, kung kinakailangan. Ilagay ang mouse pointer sa AutoFill na tuldok sababa na kanang bahagi ng cell, para ang mouse pointer ay maging cross hair.

mouse pointer on the autofill dot — Kapag nilagay mo ang mouse pointer sa tuldok sa babang-kanang bahagi ngisang cell, ito ay magiging isang AutoFill cross hair.

Pindutin ng dalawang beses upang mapuno ang mga values hanggang sa pinakadulo ng hanay.

worksheet with data after using autofill — Pindutin ng dalawang beses ang AutoFill cross hair upang ma kopya ang pormula hanggang sa dulo ng hanay

Kung gusto mo, pwede mong gamitin ang VLOOKUP na function sa mga susunod na mga hanay upangmapalabas ang ibang fields, gaya ng last name o state.

Paggamit ng MATCH

Ang MATCH na function ay hindi binabalik ang value sa iyo; Ikawang magbibigay ng value na iyong hinahanap, at ang function ay ibabalik angposisyon ng value na iyon. Para kang nagtatanong kung nasaan ang #135 Main Street, atmakukuha mo ang sagot na ito ay nasa ika-4 na building sa dulo ng daan.

Syntax

Ang syntax ng MATCH na function ay:

=MATCH(lookup value, table range, [match type])

Ang mga argumento ay:

Lookup value. Ang cell na naglalaman ng kakaibang pagkakakilanlan.
Table range. Ang sakop ng mga cell na iyong hinahanap.
Match type. Pagpipilian. Ito ay kung paano mo tutukuyin kung gaano ka tumpak ang resulta na gusto mo, sa sumusunod:

Kasunod na may pinakamataas na value	-1	Ang mga value ay dapat nakaayos na magkasunod-sunod pababa.
Target value	0	Ang mga value ay pwede kahit anumang ayos.
Kasunod na may pinakamababang value	1	Default type. Ang mga values ay nakaayos na magkasunod-sunod pataas.

Kung ikukumpara sa VLOOKUP na function, siguro ay makikita mo na ang MATCH na functionay mas madaling gamitin kung gagamitin ang isang range name. Pumunta sa pinagmulan ng Data sheet, pumili sa B4 (hanay na header para sa order #) hanggang sa dulo, pindutin ang Name box na nasa taas ng hanay A, at tawagin itong order_number. Tandaan na ang mga values ay nasa magkasunod-sunod na pataas na ayos.

name box with one column defined — Ang named range ay pwedeng nasa isang hanay, nasa isang hanay lamang, o kahit pa nga nasa isang cell

Pumunta sa Match tab ng worksheet. Sa B5, ilagay ang MATCH na function:

=MATCH(A5,order_number,1)

entering the match function — Paglagay ng MATCH na function

Kung hindi mo itinakda ang isang range name, isusulat mo ang function bilang:

=MATCH(A5,'Source Data'!A5:A203,0)

Kahit sa anong paraan, makikita mo na andoon ito sa ika 14th na posisyon (at ginawa itong nasaika 13^th na ayos).

completed match function — Resulta ng MATCH na function

Paggamit ng INDEX

Ang INDEX na function ay salungat ng MATCH na function at may hawig ito sa VLOOKUP. Sasabihan mo ang function kung anong hilera at hanay ng data ang gusto mo, at sasabihan ka nito kung ano ang value na laman ng cell.

Syntax

Ang syntax ng INDEX na function ay:

=INDEX(data range, row number, [column number])

Ang mga argumento ay:

Data range. Kagaya ng dalawang mga function, ito ay ang table ng data.
Row number. Ang numero ng hilera ng data, na hindi ibig sabihin na hilera ng worksheet. Kung ang hanay ng table ay nagsisimula sa ika 10 na hilera ng sheet, kung gayon 'yon ang hilera #1.
Column number. Ang numero ng hanay ng mga sakop ng data. Kung ang hanay ay nagsisimula sa hanay E, 'yon ang hanay #1.

Ang dokyumentasyon ng Excel ay magsasaad sa iyo na ang argumento ng numero ng hanay aypwedeng pagpipilian, ngunit ang numero ng hilera ay maaaring pagpilian din. Kung ang hanay ng table mayroon lamang isang hilera o isang hanay, hindi mo na kailangang gamitin ang ibangargumento.

Pumunta sa Index sheet ng workbook at pindutin ang C6. Gusto natin na unang mahanap kung ano ang nilalaman ng ika-9 na hilera, ika-3 hanay ng table. Sa pormula, gagamitin natin ang range name na nilikha natin kamakailan lang.

Ilagay ang pormula:

=INDEX(data,A6,B6)

entering the index function — paglagay ng index na function

Binabalik nito ang apleyido ng mamimili: Strevell. Palitan ang mga value ng A6 at B6, at ang resulta sa C6 ay magpapakita ng ibang resulta (tandaan na maraming hilera na may parehong istado at pangalan ng mga produkto).

Konklusyon

Ang abilidad ng isang worksheet na maghanap sa ibang worksheet at kumuha ng data ay isang dakilang kasangkapan. Sa pamamaraang ito, maaari kang magkaroon ng isang sheet na naglalaman ng lahat ngdata na iyong kailangan para sa maraming layunin, at pagkatapos ay kukunin ang anumang kailangan para sa partikular na mga pagkakataon.