Contents
I nehinei i ka marathon 30 mau hana Excel i 30 mau lā leʻaleʻa mākou i ka hana REP (REPEAT) ma ka hana ʻana i nā pakuhi i loko o kahi keena a hoʻohana iā ia no ka helu maʻalahi. ʻO ka Pōʻakahi kēia, a ʻo ka manawa hou kēia e kau ai i ko mākou mau pāpale noʻonoʻo.
Ma ka lā 16 o ka marathon, e aʻo mākou i ka hana NĀNĀ I LUNE (NĀNĀ). He hoa pili keia VLOOKUP (VLOOKUP) a HLOOKUP (GPR), akā ʻokoʻa iki ka hana.
No laila, e aʻo kākou i ke kumumanaʻo a e hoʻāʻo i ka hana ma ka hana NĀNĀ I LUNE (NĀNĀ). Inā loaʻa iā ʻoe nā ʻike hou a i ʻole nā hiʻohiʻona e pili ana i ka hoʻohana ʻana i kēia hiʻohiʻona, e ʻoluʻolu e kaʻana iā lākou i nā manaʻo.
Hana 16: LOOKUP
Launch NĀNĀ I LUNE (LOOKUP) e hoʻihoʻi i kahi waiwai mai ka lālani hoʻokahi, hoʻokahi kolamu, a i ʻole mai kahi ʻano.
Pehea e hiki ai iaʻu ke hoʻohana i ka hana LOOKUP?
Launch NĀNĀ I LUNE (LOOKUP) hoʻihoʻi i ka hopena, ma muli o ka waiwai āu e ʻimi nei. Me kāna kōkua, hiki iā ʻoe ke:
- E huli i ka waiwai hope ma kahi kolamu.
- E ʻimi i ka mahina hope loa me nā kūʻai maikaʻi ʻole.
- E hoʻohuli i ka loaʻa ʻana o nā haumāna mai nā pākēneka i nā māka leka.
ʻO ka huaʻōlelo LOOKUP
Launch NĀNĀ I LUNE (LOOKUP) ʻelua ʻano syntactic - vector a me ka array. Ma ke ʻano vector, ʻimi ka hana i ka waiwai ma ke kolamu a i ʻole ka lālani i hāʻawi ʻia, a ma ke ʻano hoʻonohonoho, ʻimi ia i ka waiwai ma ka lālani mua a i ʻole ke kolamu o ka array.
Aia ka syntax o ka palapala vector:
LOOKUP(lookup_value,lookup_vector,result_vector)
ПРОСМОТР(искомое_значение;просматриваемый_вектор;вектор_результатов)
- huli_waiwai (lookup_value) – Hiki ke kikokiko, helu, boolean, inoa, a i ʻole loulou.
- huli_vector (lookup_vector) – He laulā me hoʻokahi lālani a i ʻole kolamu.
- hopena_vector (result_vector) – he laulā me hoʻokahi lālani a i ʻole kolamu.
- pae hoʻopaʻapaʻa huli_vector (lookup_vector) a hopena_vector (result_vector) pono e like ka nui.
Loaʻa i ka ʻano array ka syntax penei:
LOOKUP(lookup_value,array)
ПРОСМОТР(искомое_значение;массив)
- huli_waiwai (lookup_value) – Hiki ke kikokiko, helu, boolean, inoa, a i ʻole loulou.
- hana ʻia ka ʻimi e like me ka nui o ka laha:
- inā ʻoi aku ka nui o nā kolamu ma mua o nā lālani, a laila hele ka ʻimi ma ka lālani mua;
- inā like ka helu o nā lālani a me nā kolamu a ʻoi aku paha nā lālani, a laila e loaʻa ka huli ma ke kolamu mua.
- Hoʻihoʻi ka hana i ka waiwai hope mai ka lālani / kolamu i loaʻa.
Pahele LOOKUP (VIEW)
- I ka hana NĀNĀ I LUNE (BROWSE) ʻaʻohe koho e ʻimi i kahi pāʻani pololei, aia i loko VLOOKUP (VLOOKUP) a ma HLOOKUP (GPR). Inā ʻaʻohe waiwai huli, a laila e hoʻihoʻi ka hana i ka waiwai kiʻekiʻe ʻaʻole ma mua o ka waiwai huli.
- Pono e hoʻokaʻawale ʻia ka array a i ʻole vector e ʻimi ʻia ana ma ke kaʻina piʻi, i ʻole e hoʻihoʻi ka hana i kahi hopena pololei.
- Inā ʻoi aku ka nui o ka waiwai mua o ka array/vector e nānā ʻia ana ma mua o ka waiwai huli, a laila e hoʻopuka ka hana i kahi memo hewa. #AT (#N/A).
Laʻana 1: ʻImi i ka waiwai hope ma kahi kolamu
Ma ke ʻano o kahi hana array NĀNĀ I LUNE Hiki ke hoʻohana ʻia (LOOKUP) e ʻimi i ka waiwai hope ma kahi kolamu.
Excel kōkua puʻi waiwai waiwai 9,99999999999999E + 307 ʻo ia ka helu nui loa i hiki ke kākau ʻia i loko o ke kelepona. Ma kā mākou kumu, e hoʻonoho ʻia e like me ka waiwai i makemake ʻia. Manaʻo ʻia ʻaʻole e loaʻa ka helu nui, no laila e hoʻihoʻi ka hana i ka waiwai hope ma ka kolamu D.
Ma kēia hiʻohiʻona, ʻaʻole ʻae ʻia nā helu ma ka kolamu D ʻaʻole e hoʻokaʻawale ʻia, eia kekahi, hiki ke loaʻa nā helu kikokikona.
=LOOKUP(9.99999999999999E+307,D:D)
=ПРОСМОТР(9,99999999999999E+307;D:D)
Laʻana 2: E ʻimi i ka mahina i hala me ka waiwai ʻino
Ma kēia laʻana, e hoʻohana mākou i ke ʻano vector NĀNĀ I LUNE (NĀNĀ). Aia ma ke kolamu D nā waiwai kūʻai, a ʻo ke kolamu E nā inoa o nā mahina. I kekahi mau mahina, ʻaʻole i holo maikaʻi nā mea, a ua ʻike ʻia nā helu maikaʻi ʻole i nā cell me nā waiwai kūʻai.
No ka loaʻa ʻana o ka mahina hope me ka helu maikaʻi ʻole, ʻo ka formula me NĀNĀ I LUNE (LOOKUP) e nānā no kēlā me kēia waiwai kūʻai i emi iho 0 (ka like ole ma ke kumu). A laila, māhele mākou 1 ma ka hopena, hoʻopau mākou me kekahi 1, a i ʻole he memo hewa #DIV/0 (#SECTION/0).
No ka mea, ʻo ka waiwai i makemake ʻia 2 ʻAʻole i loaʻa, e koho ka hana i ka mea i loaʻa hope 1, a hoʻihoʻi i ka waiwai kūpono mai ke kolamu E.
=LOOKUP(2,1/(D2:D8<0),E2:E8)
=ПРОСМОТР(2;1/(D2:D8<0);E2:E8)
Hōʻike: Ma kēia ʻano, ma kahi o ka hoʻopaʻapaʻa huli_vector (lookup_vector) hōʻike i pani ʻia 1/(D2:D8<0), ka mea i hoʻokumu i kahi ʻano i loko o ka RAM o ke kamepiula, ʻo ia hoʻi 1 a me nā waiwai hewa #DIV/0 (#SECTION/0). 1 e hōʻike ana i ka pūnaewele pili i ka laulā D2:D8 i loaʻa kahi waiwai i emi iho 0, a me ka hewa #DIV/0 (#DIV/0) – ʻoi aku ka nui a i ʻole like me 0. ʻO ka hopena, ʻo kā mākou hana ke ʻimi i ka hope 1 i loko o ka papa hana virtual i hana ʻia, a ma muli o kēia, e hoʻihoʻi i ka inoa o ka mahina mai ka pae E2:E8.
Laʻana 3: Ka hoʻololi ʻana i ka holomua o nā haumāna mai nā pākēneka i nā māka leka
Ma mua, ua hoʻoponopono mākou i kahi pilikia like me ka hoʻohana ʻana i ka hana VLOOKUP (VPR). I kēia lā e hoʻohana mākou i ka hana NĀNĀ I LUNE (VIEW) ma ke ʻano vector e hoʻololi i ka loaʻa ʻana o nā haumāna mai nā pākēneka i nā māka leka. ʻAʻole like VLOOKUP (VLOOKUP) no kahi hana NĀNĀ I LUNE (NĀNĀ) ʻAʻole pili inā aia nā pākēneka ma ke kolamu mua o ka pākaukau. Hiki iā ʻoe ke koho i kēlā me kēia kolamu.
Ma kēia laʻana, aia nā helu ma ke kolamu D, i hoʻokaʻawale ʻia ma ka ʻaoʻao piʻi, a ʻo kā lākou mau leka pili i ke kolamu C, ma ka ʻaoʻao hema o ke kolamu e ʻimi ʻia nei.
=LOOKUP(C10,D4:D8,C4:C8)
=ПРОСМОТР(C10;D4:D8;C4:C8)
