Ang lahat ng mga lihim ng Excel VLOOKUP function para sa paghahanap ng data sa isang talahanayan at pagkuha nito sa isa pa

2024 May -akda: Malcolm Clapton | [email protected]. Huling binago: 2023-12-17 04:13

Pagkatapos basahin ang artikulo, hindi mo lamang matututunan kung paano maghanap ng data sa isang spreadsheet ng Excel at i-extract ito sa isa pa, ngunit pati na rin ang mga diskarte na maaaring magamit kasabay ng VLOOKUP function.

Kapag nagtatrabaho sa Excel, madalas na kailangang maghanap ng data sa isang talahanayan at i-extract ito sa isa pa. Kung hindi mo pa rin alam kung paano gawin ito, pagkatapos ay pagkatapos basahin ang artikulo, hindi mo lamang matutunan kung paano gawin ito, ngunit malalaman din sa ilalim ng kung anong mga kondisyon ang maaari mong pisilin ang maximum na pagganap sa labas ng system. Karamihan sa mga napakaepektibong pamamaraan na dapat gamitin kasabay ng VLOOKUP function ay isinasaalang-alang.

Kahit na ginagamit mo ang VLOOKUP function sa loob ng maraming taon, pagkatapos ay may mataas na antas ng posibilidad na ang artikulong ito ay magiging kapaki-pakinabang sa iyo at hindi ka iiwan na walang malasakit. Halimbawa, bilang isang espesyalista sa IT, at pagkatapos ay isang pinuno sa IT, gumagamit ako ng VLOOKUP sa loob ng 15 taon, ngunit ngayon ko lang nagawang harapin ang lahat ng mga nuances, nang magsimula akong magturo sa mga tao ng Excel sa isang propesyonal na batayan.

VLOOKUP ay abbreviation para sa vpatayo NSinspeksyon. Gayundin, VLOOKUP - Vertical LOOKUP. Ang mismong pangalan ng function ay nagpapahiwatig sa amin na naghahanap ito sa mga hilera ng talahanayan (patayo - umuulit sa mga hilera at nag-aayos ng column), at hindi sa mga column (pahalang - umuulit sa mga column at nag-aayos ng row). Dapat pansinin na ang VLOOKUP ay may kapatid na babae - isang pangit na sisiw na hindi kailanman magiging isang sisne - ito ang HLOOKUP function. Ang HLOOKUP, bilang kabaligtaran sa VLOOKUP, ay nagsasagawa ng mga pahalang na paghahanap, ngunit ang konsepto ng Excel (at sa katunayan ang konsepto ng organisasyon ng data) ay nagpapahiwatig na ang iyong mga talahanayan ay may mas kaunting mga column at marami pang row. Iyon ang dahilan kung bakit kailangan nating maghanap ayon sa mga hilera nang maraming beses nang mas madalas kaysa sa mga hanay. Kung masyadong madalas mong ginagamit ang function ng HLO sa Excel, malamang na hindi mo naiintindihan ang isang bagay sa buhay na ito.

Syntax

Ang VLOOKUP function ay may apat na parameter:

= VLOOKUP (;; [;]), dito:

- ang nais na halaga (bihira) o isang sanggunian sa isang cell na naglalaman ng nais na halaga (ang karamihan sa mga kaso);

- reference sa isang hanay ng mga cell (two-dimensional array), sa FIRST (!) column kung saan hahanapin ang value ng parameter;

- numero ng column sa hanay kung saan ibabalik ang halaga;

- ito ay isang napakahalagang parameter na sumasagot sa tanong kung ang unang column ng hanay ay pinagsunod-sunod sa pataas na pagkakasunud-sunod. Kung pinagbukud-bukod ang array, tinutukoy namin ang value na TRUE o 1, kung hindi - FALSE o 0. Kung aalisin ang parameter na ito, magde-default ito sa 1.

Pustahan ako na marami sa mga nakakaalam ng function ng VLOOKUP bilang patumpik-tumpik, pagkatapos basahin ang paglalarawan ng ika-apat na parameter, maaaring hindi sila komportable, dahil sanay silang makita ito sa isang bahagyang naiibang anyo: kadalasan ay pinag-uusapan nila ang eksaktong tugma kung kailan paghahanap (FALSE o 0) o isang range scan (TRUE o 1).

Ngayon ay kailangan mong pilitin at basahin ang susunod na talata nang maraming beses hanggang sa maramdaman mo ang kahulugan ng sinabi hanggang sa wakas. Bawat salita ay mahalaga doon. Ang mga halimbawa ay makakatulong sa iyo na malaman ito.

Paano eksaktong gumagana ang VLOOKUP formula?

• Uri ng formula I. Kung ang huling parameter ay tinanggal o tinukoy na katumbas ng 1, ipinapalagay ng VLOOKUP na ang unang column ay pinagsunod-sunod sa pataas na pagkakasunud-sunod, kaya huminto ang paghahanap sa row na nauuna kaagad sa linyang naglalaman ng halagang mas malaki kaysa sa nais … Kung walang nakitang ganoong string, ibabalik ang huling row ng range.

  

• Formula II. Kung ang huling parameter ay tinukoy bilang 0, pagkatapos ay ini-scan ng VLOOKUP ang unang column ng array nang sunud-sunod at agad na ihihinto ang paghahanap kapag natagpuan ang unang eksaktong tugma sa parameter, kung hindi man ang # N / A (# N / A) na error code ay ibinalik.

  

Mga formula ng daloy ng trabaho

Uri ng VPR I

Uri ng VPR II

Corollaries para sa mga formula ng form I

1. Maaaring gamitin ang mga formula upang ipamahagi ang mga halaga sa mga saklaw.
2. Kung ang unang hanay ay naglalaman ng mga duplicate na halaga at maayos na pinagsunod-sunod, ang huling mga hilera na may mga dobleng halaga ay ibabalik.
3. Kung maghahanap ka ng value na halatang mas malaki kaysa sa maaaring nilalaman ng unang column, madali mong mahahanap ang huling row ng talahanayan, na maaaring maging lubos na mahalaga.
4. Ibabalik lang ng view na ito ang # N / A error kung hindi ito makakita ng value na mas mababa sa o katumbas ng ninanais.
5. Sa halip mahirap maunawaan na ang formula ay nagbabalik ng mga maling halaga kung ang iyong array ay hindi pinagsunod-sunod.

Corollaries para sa mga formula ng uri II

Kung maraming beses na naganap ang gustong value sa unang column ng array, pipiliin ng formula ang unang row para sa kasunod na pagkuha ng data.

Pagganap ng VLOOKUP

Naabot mo na ang kasukdulan ng artikulo. Mukhang, mabuti, ano ang pagkakaiba kung tutukuyin ko ang zero o isa bilang huling parameter? Karaniwan, ang lahat ay nagpapahiwatig, siyempre, zero, dahil ito ay medyo praktikal: hindi mo kailangang mag-alala tungkol sa pag-uuri ng unang hanay ng array, maaari mong agad na makita kung ang halaga ay natagpuan o hindi. Ngunit kung mayroon kang ilang libong VLOOKUP formula sa iyong sheet, mapapansin mong mabagal ang VLOOKUP II. Kasabay nito, kadalasan ang lahat ay nagsisimulang mag-isip:

• Kailangan ko ng mas malakas na computer;
• Kailangan ko ng mas mabilis na formula, halimbawa, maraming tao ang nakakaalam tungkol sa INDEX + MATCH, na diumano'y mas mabilis ng isang measly 5-10%.

At ilang mga tao ang nag-iisip na sa sandaling simulan mo ang paggamit ng VLOOKUP ng uri I at matiyak na ang unang hanay ay pinagsunod-sunod sa anumang paraan, ang bilis ng VLOOKUP ay tataas ng 57 beses. Sumulat ako sa mga salita - fifty-seven TIMES! Hindi 57%, ngunit 5,700%. Sinuri ko ang katotohanang ito nang lubos.

Ang sikreto ng ganoong mabilis na trabaho ay nakasalalay sa katotohanan na ang isang napakahusay na algorithm sa paghahanap ay maaaring mailapat sa isang pinagsunod-sunod na hanay, na tinatawag na binary search (paraan ng paghahati, pamamaraan ng dichotomy). Kaya't ang VLOOKUP ng uri ko ay inilalapat ito, at ang VLOOKUP ng uri ng II ay naghahanap nang walang anumang pag-optimize. Ang parehong ay totoo para sa MATCH function, na may kasamang katulad na parameter, at ang LOOKUP function, na gumagana lamang sa mga pinagsunod-sunod na array at kasama sa Excel para sa compatibility sa Lotus 1-2-3.

Mga disadvantages ng formula

Ang mga kawalan ng VLOOKUP ay halata: una, naghahanap lamang ito sa unang column ng tinukoy na array, at pangalawa, sa kanan lang ng column na ito. At gaya ng naiintindihan mo, maaaring mangyari na ang column na naglalaman ng kinakailangang impormasyon ay nasa kaliwa ng column kung saan natin titingnan. Ang nabanggit na kumbinasyon ng mga formula na INDEX + MATCH ay wala sa disbentaha na ito, na ginagawa itong pinaka-flexible na solusyon para sa pagkuha ng data mula sa mga talahanayan kumpara sa VLOOKUP (VLOOKUP).

Ilang aspeto ng paglalapat ng formula sa totoong buhay

Hanay ng paghahanap

Ang isang klasikong paglalarawan ng isang hanay ng paghahanap ay ang gawain ng pagtukoy ng diskwento ayon sa laki ng order.

Maghanap ng mga string ng teksto

Siyempre, ang VLOOKUP ay naghahanap hindi lamang para sa mga numero, kundi pati na rin para sa teksto. Dapat itong isipin na ang formula ay hindi nakikilala sa pagitan ng kaso ng mga character. Kung gumagamit ka ng mga wildcard, maaari mong ayusin ang isang malabo na paghahanap. Mayroong dalawang wildcard: "?" - pinapalitan ang anumang isang character sa isang text string, "*" - pinapalitan ang anumang bilang ng anumang mga character.

Mga puwang sa pakikipaglaban

Ang tanong ay madalas na itinaas kung paano lutasin ang problema ng mga karagdagang espasyo kapag naghahanap. Kung ang lookup table ay maaari pa ring i-clear sa kanila, ang unang parameter ng VLOOKUP formula ay hindi palaging nasa iyo. Samakatuwid, kung ang panganib ng pagbara sa mga cell na may mga karagdagang espasyo ay naroroon, maaari mong gamitin ang TRIM function upang i-clear ito.

Iba't ibang format ng data

Kung ang unang parameter ng VLOOKUP function ay tumutukoy sa isang cell na naglalaman ng isang numero, ngunit naka-imbak sa cell bilang teksto, at ang unang column ng array ay naglalaman ng mga numero sa tamang format, kung gayon ang paghahanap ay mabibigo. Posible rin ang kabaligtaran na sitwasyon. Ang problema ay madaling malutas sa pamamagitan ng pagsasalin ng parameter 1 sa kinakailangang format:

= VLOOKUP (−− D7; Mga Produkto! $ A $ 2: $ C $ 5; 3; 0) - kung ang D7 ay naglalaman ng teksto at ang talahanayan ay naglalaman ng mga numero;

= VLOOKUP (D7 at ""); Mga Produkto! $ A $ 2: $ C $ 5; 3; 0) - at kabaliktaran.

Sa pamamagitan ng paraan, maaari mong isalin ang teksto sa isang numero sa maraming paraan nang sabay-sabay, piliin ang:

• Dobleng negasyon -D7.
• Pagpaparami ng isang D7 * 1.
• Zero na karagdagan D7 + 0.
• Pagtaas sa unang kapangyarihan D7 ^ 1.

Ang pag-convert ng numero sa text ay ginagawa sa pamamagitan ng concatenation na may walang laman na string, na pumipilit sa Excel na i-convert ang uri ng data.

Paano sugpuin ang # N / A

Ito ay napaka-maginhawang gawin sa IFERROR function.

Halimbawa: = IFERROR (VLOOKUP (D7; Mga Produkto! $ A $ 2: $ C $ 5; 3; 0); "").

Kung ibabalik ng VLOOKUP ang error code # N / A, haharangin ito ng IFERROR at papalitan ang parameter 2 (sa kasong ito, isang walang laman na string), at kung walang nangyaring error, ang function na ito ay magpapanggap na wala ito, ngunit mayroon lamang VLOOKUP na nagbalik ng normal na resulta.

Array

Kadalasan ay nakakalimutan nilang gawing ganap ang sanggunian ng array, at kapag lumalawak ang array ay "lumulutang". Tandaan na gumamit ng $ A $ 2: $ C $ 5 sa halip na A2: C5.

Magandang ideya na ilagay ang reference array sa isang hiwalay na sheet ng workbook. Hindi ito natatapakan, at ito ay magiging mas ligtas.

Ang isang mas mahusay na ideya ay ang ideklara ang array na ito bilang isang pinangalanang hanay.

Maraming user, kapag tumutukoy ng array, ang gumagamit ng construction tulad ng A: C, na tumutukoy sa buong column. Ang diskarte na ito ay may karapatang umiral, dahil nailigtas ka mula sa kinakailangang subaybayan ang katotohanan na kasama sa iyong array ang lahat ng kinakailangang string. Kung magdaragdag ka ng mga row sa sheet na may orihinal na array, hindi kailangang isaayos ang range na tinukoy bilang A: C. Siyempre, pinipilit ng syntactic construct na ito ang Excel na gumawa ng kaunti pang trabaho kaysa sa eksaktong pagtukoy sa hanay, ngunit ang overhead na ito ay maaaring mapabayaan. Pinag-uusapan natin ang tungkol sa daan-daang segundo.

Well, sa gilid ng henyo - upang ayusin ang array sa form.

Gamit ang COLUMN function para tukuyin ang column na i-extract

Kung ang talahanayan kung saan ka kumukuha ng data gamit ang VLOOKUP ay may parehong istraktura tulad ng lookup table, ngunit naglalaman lamang ng mas kaunting mga row, maaari mong gamitin ang COLUMN () function sa VLOOKUP upang awtomatikong kalkulahin ang mga numero ng mga column na kukunin. Sa kasong ito, ang lahat ng mga formula ng VLOOKUP ay magiging pareho (isinasaayos para sa unang parameter, na awtomatikong nagbabago)! Tandaan na ang unang parameter ay may ganap na column coordinate.

Paglikha ng composite key na may & "|" at

Kung ito ay magiging kinakailangan upang maghanap sa pamamagitan ng ilang mga haligi sa parehong oras, pagkatapos ito ay kinakailangan upang gumawa ng isang pinagsama-samang key para sa paghahanap. Kung ang return value ay hindi textual (tulad ng kaso sa field na "Code"), ngunit numeric, ang mas maginhawang formula ng SUMIFS ay magiging angkop para dito at ang composite column key ay hindi na kakailanganin.

Ito ang aking unang artikulo para sa isang Lifehacker. Kung nagustuhan mo, inaanyayahan kita na bisitahin, at masayang basahin sa mga komento tungkol sa iyong mga lihim ng paggamit ng VLOOKUP function at iba pa. Salamat.:)