VLOOKUP. Paggamit ng VLOOKUP. Excel - CDF

Ang application na programa Excel ay popular dahil sa kanyang affordability at pagiging simple, dahil ito ay hindi nangangailangan ng anumang espesyal na kaalaman at kasanayan. Table view ng mga kuru-kuro ng impormasyon sa anumang mga gumagamit, at isang malawak na hanay ng mga kasangkapan, kabilang ang "Master function na" nagbibigay-daan sa anumang pagmamanipula ng at kalkulasyon gamit ang ibinigay na data.

Isa sa mga kilalang Excel formula ay isang vertical view. Paggamit ng VLOOKUP sa unang tingin ay tila masyadong kumplikado, ngunit ito ay lamang sa simula.

Paano gumagana ang Excel VLOOKUP

Kapag nagtatrabaho sa CDF formula ay dapat na kumuha sa account na ito ay sa paghahanap para sa ninanais na halaga lamang sa mga haligi sa halip na mga hilera. Upang mag-apply ang pag-andar ay nangangailangan ng isang minimum na bilang ng mga haligi - dalawa, ang maximum na magagamit.

CDF function na paghahanap sa isang paunang natukoy na criterion na maaaring maging ng anumang format (teksto, numero, pera, petsa at oras, t. D.) Sa talahanayan. Sa kaso ng pag-record ito outputs isang pasiya (insert) ang halaga na naitala sa parehong hilera, ngunit may pamagat ng haligi, ibig sabihin na naaayon sa isang naibigay na criterion. Kung ang halaga ay hindi natagpuan, at pagkatapos ng isang error na maibigay # N / A (sa Ingles na bersyon # N / A).

ang pangangailangan na gamitin ang

VLOOKUP pagdating sa aid ng operator, kung kailan mo nais upang mabilis na mahanap at gamitin sa karagdagang mga kalkulasyon, pag-aaral o hula ng isang tiyak na halaga ng mas malaking table. Ang pangunahing bagay kapag ginamit mo ang formula - tiyakin na ang tinukoy na saklaw ng paghahanap ay tama napili. Dapat itong isama ang lahat ng mga talaan, iyon ay, mula sa unang sa huling.

Ang pinaka-madalas na paggamit ng CDF (Excel function na) - ang paghahambing at pagdaragdag ng data sa dalawang mga talahanayan, ang paggamit ng mga tiyak na pamantayan. Dagdag pa rito, ang saklaw ng paghahanap ay maaaring maging malaki at naglalaman ng libu-libong mga patlang, na inilagay sa iba't ibang mga sheet o mga aklat.

Ipinapakita ang mga pag-andar ng CDF, kung paano gamitin ito, kung paano upang magsagawa ng mga kalkulasyon, bilang isang halimbawa sa figure sa itaas. Narito isaalang-alang namin ang table retails mga sukat depende sa rehiyon at ang manager. Search criterion ay ang tiyak na manager (ang kanyang buong pangalan), at ang nais na halaga ay ang kabuuan ng kanyang benta.

Bilang isang resulta ng CDF function (VLOOKUP), isang bagong mesa kung saan tiyak na nais na manager mabilis na pinarehang kanyang kabuuang benta.

Pagpuno algorithm formula

Matatagpuan formula CDF sa "Ang function Wizard" at ang "Mga sanggunian at mga array." Dialog na option ay ang mga sumusunod:

sa formula sa mga argumento ay ipinasok sa isang queue:

• Ang nais na halaga - iyon ay upang mahanap ang isang function, at ang mga variant na kung saan ay ang halaga ng mga cell, address nito, ang pangalang ibinigay sa ito sa pamamagitan ng operator. Sa aming kaso - ito ay ang pangalan at ang pangalan ng manager.
• Table - isang hanay ng mga hanay at haligi, kung saan ang criterion ay hinahangad.
• Haligi numero - nito serial number, na kung saan ay ang kabuuan ng mga benta, iyon ay ang resulta ng formula.
• Pagitan view. Mayroon siyang isang halaga ng alinman sa totoo o totoo. At FALSE nagbabalik lamang ng isang eksaktong tugma, TRUE - ay nagbibigay-daan sa paghahanap tinatayang halaga.

Halimbawa ng pag-andar

VLOOKUP halimbawa ng paggamit ay maaaring maging tulad ng sumusunod: kapag ang pakikitungo sa kalakalan enterprise sa Excel spreadsheet sa column Isang nakasulat na pangalan ng produkto, at sa hanay B - ang katumbas na presyo. Para sa pagguhit ng up ng mga panukala sa haligi C na kailangan mo upang mahanap ang halaga ng isang partikular na produkto na nais mong ipakita sa hanay D.

Ang formula, na nakasulat sa D, magmumukhang ganito: = VLOOKUP (C1, A1: B5, 2, 0), ibig sabihin, = VLOOKUP (lookup halaga, talahanayan ng data na saklaw, ang mga serial number ng haligi; 0). Bilang isang fourth argument ay maaaring gamitin sa halip ng 0 ay FALSE.

Upang punan ang formula ay nag-aalok ng isang talahanayan, dapat mong kopyahin ang buong hanay D.

Secure data lugar ng pagtatrabaho hanay ay posible sa pamamagitan ng absolute reference. Upang gawin ito nang manu-mano tabulated $ tanda sa harap ng pang-abakada at de-numerong halaga sa pamamagitan ng ang matinding kaliwa at kanang mga cell ng talahanayan. Sa kasong ito ang formula ay magiging: = CDF (C1; $ A $ 1: $ B $ 5, 2, 0).

gamit error

CDF function na ay hindi gumagana, at pagkatapos ay ang isang mensahe ay lilitaw sa haligi ng output resulta ng isang error (# N / A o # N / A). Nangyayari ito sa naturang kaso:

1. Ang formula ay nagpasimula, at ang ninanais na pamantayan ng hanay ay walang laman (sa kasong ito, haligi C).
2. Sa haligi C na ginawa ng isang halaga na ay absent sa column A (hanay ng data sa paghahanap). Upang suriin ang mga kinakailangang mga halaga ay dapat na inilalaan at ang haligi pamantayan sa menu ng tab na "I-edit" - "Hanapin" upang ipasok ang rekord na ito, simulan ang paghahanap. Kung ang program ay hindi mahanap ito, pagkatapos ito ay hindi umiiral.
3. Haligi ng Mga Format ng mga cell A at C (ang mga kinakailangang mga pamantayan) ay naiiba, halimbawa, isa - ang teksto, at ang iba pang mga - number. Baguhin ang format ng cell ay maaaring maging, kung pumunta kami sa cell pag-edit (F2). Ang ganitong problema ay karaniwang mangyayari kapag nag-import ka ng data mula sa ibang mga application. Upang maiwasan ang naturang mga pagkakamali sa formula CDF ay posible upang isama ang mga sumusunod na tampok: VALUE o TEXT. Pagpapatupad ng mga algorithm awtomatikong i-convert ang format ng mga cell.
4. Ang function na code ay naglalaman non-napi-print na mga character o puwang. Pagkatapos ay dapat mong maingat na suriin ang mga formula sa presensya ng error input.
5. I-set ang isang tinatayang search, iyon ay ang ika-apat na argumento VLOOKUP ay 1 o TRUE, ngunit ang mga talahanayan ay hindi pinagsunod-sunod sa pamamagitan ng tumataas na halaga. Sa kasong ito, ang mga kinakailangang mga pamantayan para sa hanay na gusto mong ayusin sa pataas na pagkakasunod-sunod.

At kapag ang isang bagong talahanayan ng buod ng organisasyon ay ang mga kinakailangang mga pamantayan ay maaaring sa anumang pagkakasunod-sunod at pagkakasunod-sunod at hindi kinakailangan na matanggap kumpletong listahan ng (bahagyang pagpili).

Mga Tampok ng paggamit bilang ang pagitan ng pagtingin 1 o TRUE

Error sa ilalim №5 ay medyo pangkaraniwan at malinaw na ipinapakita sa figure sa ibaba.

Sa halimbawang ito, ang pangalan listahan ay pinagsunod-sunod ayon sa paglalagay ng bilang ay hindi sa pataas na pagkakasunod-sunod, at sa pagbagsak ng halaga. Dagdag pa rito, pati na ang pagitan ginamit sa view TRUE criterion (1), na agad na interrupts ang paghahanap kapag nakita nito ang isang halaga na mas mataas kaysa sa kinakailangan, kaya ng isang error na maibigay ito.

Sa application ng 1 o tunay na sa ikaapat na argumento ng ang pangangailangan upang matiyak na ang mga hanay na may ninanais na pamantayan ay pinagsunod-sunod sa pataas na pagkakasunod-sunod. Kapag gumagamit ng 0 o LIE, ito ay hindi na isang pangangailangan, ngunit din nawawala pagkatapos ay ang posibilidad ng isang pagitan view.

Basta ilagay sa isip na interval ay partikular na mahalaga upang ayusin ang mesa. Kung hindi man VLOOKUP ay magpapakita ng maling data sa isang cell.

Iba pang mga nuances ng nagtatrabaho sa VLOOKUP

Para sa kaginhawahan, tulad ng isang formula ay maaaring maging karapat-dapat hanay ng talahanayan na kung saan ay naghanap (ikalawang argument), tulad ng ipinapakita sa Fig.

Sa kasong ito, ang mga benta na lugar ng talahanayan na pinamagatang. Sa pagtatapos na ito, ito ay nakatayo sa isang table, na may pagbubukod sa mga heading ng hanay, at sa field ng pangalan (sa ilalim ng tab na bar sa kaliwa) ay naitalaga nang isang pangalan.

Ang isa pang pagpipilian - headline - ay nangangahulugan na ang paglalaan ng hanay ng data, at pagkatapos ay lumipat sa menu ng "Insert" - "Pangalan" - "Magtalaga".

Upang magamit ang data na inilagay sa isa pang sheet ng workbook, gamit ang PPS function, ito ay kinakailangan sa ikalawang argument ng formula rehistro hanay data ng lokasyon. Halimbawa, CDF = (A1; Sheet 2 $ A $ 1: $ B $ 5; 2; 0), kung saan Sheet2! - isang reference sa ang hiniling na listahan ng libro, at $ A $ 1: $ B $ 5 - address ng saklaw ng data sa paghahanap.

Ang isang halimbawa ng proseso ng pang-edukasyon na may CDF

Lubos na maginhawa sa Excel VLOOKUP function na upang ilapat hindi lamang sa mga kumpanya ay nakikibahagi sa kalakalan, ngunit din pang-edukasyon na institusyon upang i-optimize ang mga alagad proseso ng pagtutugma (mag-aaral) sa kanilang mga pagtatantya. Mga halimbawa ng mga gawaing ito ay ipinapakita sa figure sa ibaba.

Mayroong dalawang mga listahan ng mga mag-aaral ng table. Isa sa kanilang mga pagtatantya, at ang pangalawa ay nagpapahiwatig edad. Ito ay kinakailangan upang ihambing ang dalawang mga talahanayan sa gayon ay sa isang par sa ang edad ng mga mag-aaral na ipinapakita ang kanilang mga pagtatasa, iyon ay upang magdagdag ng isang karagdagang hanay sa pangalawang listahan.

VLOOKUP copes sa gawaing ito. Sa "marks" G haligi na may pamagat na naitala kaukulang formula: = CDF (E4, B3: C13, 2, 0). Kailangan Niya upang kopyahin ang buong hanay ng table.

Bilang resulta ng ang pag-andar VLOOKUP ay tasahin nakuha sa pamamagitan ng ilang mga mag-aaral.

Halimbawa ng isang search engine na may CDF

Ang isa pang halimbawa ng application ng CDF function na - isang organisasyon ng mga search engine kapag nasa isang database ayon sa tinukoy na pamantayan ay dapat na upang mahanap ang katumbas na halaga. Kaya, ang mga figure ay nagpapakita ng isang listahan ng mga palayaw ng mga hayop at ang kanilang mga pag-aari sa isang tiyak na uri.

Sa tulong ng CDF, isang bagong talahanayan na madaling mahanap, palayaw na hayop ang kanyang anyo. May-katuturang mga search engine tulad ng kapag nagtatrabaho na may malaking mga listahan. Upang mano-manong suriin ang lahat ng mga entry, maaari mong mabilis na gamitin ang search at makuha ang ninanais na resulta.