VLOOKUP. Mā te VLOOKUP. Excel - CDF

Ko te hōtaka tono he rongonui Excel no o tona utu, me te tapatahi, no te mea e kore e rapua e te reira i tetahi mōhiotanga me ngā pūkenga motuhake. tirohanga Ripanga o te ariā o te mōhiohio ki tetahi kaiwhakamahi, me te whānui o ngā taputapu ngā, tae atu "mahi Kaiwhakaako" e āhei tetahi whawhe o me tātaitanga ki te raraunga whakaratohia.

Ko tētahi o nga tātai Excel pai-mohiotia ko te tirohanga poutū. Mā te VLOOKUP i mawhiti tuatahi te mea tino uaua, engari te reira anake te timatanga.

Nahea Excel VLOOKUP

kia tango, no te mahi ki te tātai CDF ki pūkete e ka rapu ai mo te uara e hiahiatia ana anake i roto i tīwae, kaua ki te rarangi. Hei tono i te mahi titau te maha itinga o tīwae - e rua, te mōrahi e wātea ana.

e rapu ana CDF mahi he paearu tomu'a e kia o tetahi hōputu (kuputuhi, tau, moni, te rā me te wā, t. D.) I roto i te tepu. I roto i te take o te tuhi i ngä whakaputa reira he kitenga (fakahū) te uara tuhia i roto i te rarangi kotahi, engari ki te taitara o te tīwae, i.e. e ōrite ana ki te paearu i homai. Ki te te kore i kitea te uara, ka puta he hapa te # N / A (i roto i te reo Ingarihi putanga # N / A).

te hiahia ki te whakamahi i

mai VLOOKUP ki te āwhina o te operator, ina e hiahia ana koe ki te hohoro kitea me te whakamahi i roto i ngā tātai atu, tātari matapae ranei o te tepu, he tetahi uara nui. Ko te mea matua ka whakamahi koe i tenei tātai - kia oti e kua tīpakohia tika te hōkai tohua o te rapu. kia whakauru te reira i te mau papaa parau katoa, e ko, i te tuatahi ki te whakamutunga.

Ko te whakamahi tino auau o te CDF (Excel mahi) - te whakarite me te tua o te raraunga i roto i nga papa e rua, mā te whakamahi i ngā paearu motuhake. Ano, Ka taea e te whānuitanga rapu kia nui, me te roto i mano o mara, whakanohoia ki runga ki pepa rerekē pukapuka ranei.

He whakaatu i te mahi o te CDF, me pehea ki te whakamahi i te reira, me pehea ki te kawe i roto i tātaitanga, hei tauira i roto i te ahua i runga. Tenei whakaaro tatou te tepu retails rahi i runga i te rohe me te kaiwhakahaere rānei. Rapu paearu Ko te kaiwhakahaere motuhake (tona ingoa tonu), ka ko te uara e hiahiatia ana te moni o ona hoko.

Ka rite ki te hua o te mahi CDF (VLOOKUP), he tepu hou i roto i nei kaiwhakahaere hiahiatia motuhake hohoro ki āu ana hoko katoa.

Whakakī tātai hātepe papatono

CDF tātai kei roto i te "Ruānuku Taumahi" me te "References me Arrays." kōwhiringa Kōrero ko e whai ake:

i roto i te tātai o te tautohetohe e tomo i roto i te rārangi:

• Ko te uara e hiahiatia ana - e ko ki te kitea he mahi, me rerekē e ko te uara o te pūtau, tona wāhitau, te ingoa i homai ki reira e te kaiwhakahaere. I roto i to tatou take - he reira te ingoa me te ingoa o te kaiwhakahaere.
• Ripanga - he whānui o ngā rarangi me tīwae, rapu ai te te paearu.
• tau tīwae - tona tau rangatū, i te mea te moni o te hoko, e ko te hua o te tātai.
• Tiro wā. mau e ia he uara o rānei FALSE TRUE ranei. Na hoki FALSE anake he ōritenga, TRUE - taea rapu uara āwhiwhiwhi.

Tauira o te mahi

kia VLOOKUP tauira whakamahi e whai ake: ka mahi hinonga hokohoko i roto i ripakaute Excel i tīwae te ingoa tuhituhi o te hua, me i roto i te tīwae B - te utu hāngai. Hoki te tuhi ake tono i roto i te tīwae C e hiahia ana koe ki te kitea te utu o te hua ngā e hiahia ana koe ki te whakaatu i roto i te tīwae D.

Te tātai, tuhituhia i roto i D, ka titiro rite tenei: = VLOOKUP (C1, A1: B5, 2, 0), arā, = VLOOKUP (uara tirohia, awhe tepu raraunga, te tau rangatū o te tīwae: 0). Ka rite ki kia te whakamahi i te wha o tautohe hei utu o 0 he FALSE.

Hei whakakiia tenei tātai tuku he tepu, me te tārua koe i te tīwae katoa D.

rohe raraunga Haumaru o te awhe mahi he taea i te tikanga o nga tohutoro pūmau. Ki te mahi i tenei ā ngä $ tohu i roto i te mua o uara pūrārangi me tau i tino nga pūtau maui, me te tika o te tepu. I roto i tenei take riro te tātai: = CDF (C1: $ te $ 1: $ B $ 5, 2, 0).

hapa te whakamahi i

e kore e CDF mahi mahi, a ka puta he karere i roto i te hua tīwae putanga o te hapa (# N / A ranei # N / A). Tenei tupu i roto i ngā take pēnei i:

1. Kei te whakaurua te tātai, ka he kau te tīwae paearu e hiahiatia ana (i roto i tenei take, tīwae C).
2. I roto i te tīwae hanga C he uara e he ngaro i roto i tīwae A (rapu raraunga whānui). Hei taki i te uara e hiahiatia ana kia tohaina me te paearu tīwae i roto i te tahua ripa "Whakatika" - "Kimihia" ki te kōkuhu i tenei record, tīmata te rapu. Ki te kore e kitea e te reira i te hōtaka, ka kore e tīari reira.
3. He hōputu tīwae o ngā pūtau A ko C (te paearu e hiahiatia ana) rerekē, hei tauira, kotahi - te kuputuhi, me te tahi atu - tau. Hurihia te hōputu o te pūtau e taea e, ki te haere tatou ki te whakatikatika pūtau (F2). Taua raruraru te tikanga puta ina kawemai koe raraunga i ētahi atu taupānga. Ki te karo i taua mau hape i roto i te tātai, ko te taea CDF ki whakauru i te āhuatanga e whai ake nei: Tuhinga KUPU ranei. Whakatinanatanga o enei hātepe te tahuri aunoa i te hōputu o te pūtau.
4. Kei te waehere mahi pūāhua kore-ka taea te tā mokowā ranei. Na kia āta tirohia e koe te tātai i roto i te aroaro o ngā hapa tāuru.
5. Kia anga te rapu tata, e ko te wha o nga tautohe VLOOKUP he 1 TRUE ranei, engari e kore e te te tepu kōmaka e te uara maranga. I roto i tenei take, nga paearu e hiahiatia ana mo te tīwae e hiahia ana koe ki te kōmaka i roto i te raupapa aupiki.

A ka he tepu whakarāpopoto hou o whakahaere ko te paearu e hiahiatia e taea e i roto i tetahi tikanga, me te raupapa, me te kore tika e riro te rārangi tino o (kōwhiringa wāhanga).

Āhuatanga o te whakamahi rite te wā mātakitaki 1 TRUE ranei

Ko te tino noa, me te mārama whakaaturia i roto i te ahua i raro Hapa i raro i №5.

I roto i tenei tauira, kōmaka te ingoa rārangi kei kore he rite ki te taua i roto i te raupapa aupiki, a ki runga ki te uara hinga. Ano, ka rite ki te wā e whakamahia ana i roto i te tirohanga paearu TRUE (1), e whakapōnānā tonu te rapu ka kitea te reira i te uara nui atu hiahiatia, na puta he hapa te.

I roto i te tono o te 1 pono i roto i te wha o nga tautohe o te hiahia ranei ki te whakarite kia te tīwae ki te paearu hiahiatia kua kōmaka kua i roto i te raupapa aupiki. A, no te whakamahi 0 teka ranei, he tenei kore te hiahia, engari ano hoki ngaro ka te taea o te tirohanga wā.

kia mau Just i roto i te ngakau, e te mea tino nui wā ki te kōmaka te tepu. Te kore e whakaatu VLOOKUP raraunga hē i roto i te pūtau.

Ētahi atu onäianei o te mahi ki a VLOOKUP

Hoki te haratau, e taea te tika te tātai taua tepu awhe rapu nei te (rua o tautohe), rite whakaaturia i roto i te Fig.

I roto i tenei take, te rohe o te tepu hoko tika. Ki tenei mutunga, tu te reira i te tepu, me te haunga o nga ngā upoko tīwae, me i te ingoa parae (i raro i te pae ripa ki te maui) whakaritea te te ingoa.

Ko tētahi atu kōwhiringa - kupu matua - te tikanga te tohanga o te awhe raraunga, a ka whakawhiti ki te tahua "Kōkuhu" - "Ingoa" - "Vahe ki".

I roto i te tikanga ki te whakamahi i te raraunga whakanohoia ki runga ki tetahi atu whārangi o te pukamahi, te whakamahi i te mahi PPS, he mea e tika ana i roto i te tautohe tuarua o te awhe wāhi raraunga rēhita tātai. Hei tauira, CDF = (A1: Sheet 2 $ He $ 1: $ B $ 5: 2; 0), i reira Sheet2! - he tohutoro ki te rārangi pukapuka tonoa, me $ He $ 1: $ B $ 5 - wāhitau o te awhe rapu raraunga.

He tauira o te tukanga mātauranga ki te CDF

Tino watea ki Excel VLOOKUP mahi ki te tono kore anake ki ngā umanga mahi i roto i te hokohoko, engari ano whare mātauranga ki te arotau i te akonga tukanga ōrite (ngā ākonga) ki o ratou tata. E whakaaturia tauira o enei mahi i roto i te tau i raro nei.

E rua ngā rārangi o ngā ākonga o te tepu. Ko tētahi ki o ratou whakatau tata, me te tuarua tohu tau. He mea tika ki te whakarite i te papa e rua kia i runga i whakaaturia i te par ki te tau o te ākonga ratou aromatawai, e ko te ki te tāpiri i tētahi tīwae atu i roto i te rua i te rārangi.

pakari a VLOOKUP ki tenei mahi. I roto i "tohu" G tīwae ui tuhia tātai hāngai: = CDF (E4, B3: C13, 2, 0). Me ia ki te tārua i te tīwae katoa o te tepu.

Ka rite ki te hua o te mahi a VLOOKUP e aromatawai whiwhi e etahi ākonga.

Tauira o te pūkaha rapu ki te CDF

kia waiho hei whakahaere o te pūkaha rapu ka i roto i te pātengi raraunga rite ki paearu i tohua ki te kitea te uara hāngai - tētahi atu tauira o tono o te mahi CDF. Ko te kupu, e whakaatu ana i te ahua he rārangi o tapaina a o te kararehe, me ratou no ki te tetahi momo.

Ki te awhina o CDF, he tepu hou e he ngāwari ki te kitea, ko te īngoa i te kararehe tona ahua. engines rapu hāngai rite ina mahi ki ngā rārangi nui. I roto i te tikanga ki te arotake ā-tāurunga katoa, ka taea e koe hohoro te whakamahi i te rapu me te whiwhi i te hua e hiahiatia ana.