Contents
I nehinei i ka marathon 30 mau hana Excel i 30 mau lā ʻike mākou i nā kikoʻī o kā mākou wahi hana me ka hana INFO (INFORM) a ua ʻike ʻaʻole hiki iā ia ke kōkua hou iā mākou me nā pilikia hoʻomanaʻo. ʻAʻole kā mākou, ʻaʻole hoʻi ka hoʻomanaʻo o Excel!
Ma ka lā ʻelima o ka marathon, e aʻo mākou i ka hana koho (KOHO). Aia kēia hana i ka māhele Nā kuhikuhi a me nā hoʻonohonoho, hoʻihoʻi ia i kahi waiwai mai kahi papa inoa o nā koho hiki ke like me ka helu helu. He mea pono e hoʻomaopopo i ka nui o nā hihia ʻoi aku ka maikaʻi o ke koho ʻana i kahi hana ʻē aʻe, no ka laʻana, INDEX (INDEX) a ʻO MATCH (MORE EXPOSED) a i ʻole VLOOKUP (VPR). E uhi mākou i kēia mau hiʻohiʻona ma hope o kēia marathon.
No laila, e huli mākou i ka ʻike i loaʻa iā mākou a me nā hiʻohiʻona o ka hana koho (KOHO), e nana kakou i ka hana, a e nana hoi i na nawaliwali. Inā loaʻa iā ʻoe nā ʻōlelo aʻoaʻo a me nā hiʻohiʻona no kēia hiʻohiʻona, e ʻoluʻolu e kaʻana iā lākou i nā manaʻo.
Hana 05: KOHO
Launch koho (SELECT) e hoʻihoʻi i kahi waiwai mai kahi papa inoa, e koho ana e like me ka helu helu.
Pehea ʻoe e hoʻohana ai i ka hana CHOOSE?
Launch koho (SELECT) hiki ke hoʻihoʻi i ka mea ma ka papa inoa ma kahi helu kikoʻī, e like me kēia:
- Ma ka helu mahina, e hoʻihoʻi i ka helu hapaha kālā.
- Ma muli o ka lā hoʻomaka, e helu i ka lā o ka Pōʻakahi aʻe.
- Ma ka helu hale kūʻai, e hōʻike i ka nui o nā kūʻai.
Syntax KOHO
Launch koho (SELECT) loaʻa kēia syntax:
CHOOSE(index_num,value1,value2,…)
ВЫБОР(номер_индекса;значение1;значение2;…)
- helu_helu (index_number) pono ma waena o 1 a me 254 (a i ʻole 1 a hiki i 29 ma Excel 2003 a ma mua).
- helu_helu (index_number) hiki ke hoʻokomo ʻia i loko o kahi hana ma ke ʻano he helu, he ʻano, a i ʻole he kuhikuhi i kahi kelepona ʻē aʻe.
- helu_helu (index_number) e hoʻopuni ʻia i lalo i ka integer kokoke loa.
- manaʻo hoʻopiʻi kū'ē waiwai (waiwai) hiki ke helu, helu kelepona, inoa pae, hana, a kikokiko paha.
Nā Pahele CHOOSE (CHOICE)
Ma Excel 2003 a ma mua, ʻo ka hana koho (SELECT) i kākoʻo wale i nā manaʻo 29 wale nō waiwai (manaʻo).
ʻOi aku ka maʻalahi o ka huli ʻana ma o ka papa inoa ma ka pepa haʻawina ma mua o ka hoʻokomo ʻana i nā mea a pau i loko o kahi ʻano. Me nā hana VLOOKUP (VLOOKUP) a i ʻole ʻO MATCH (MATCH) Hiki iā ʻoe ke kuhikuhi i nā papa inoa o nā waiwai i loaʻa ma nā pepa hana Excel.
Laʻana 1: Helu hapahā ma ka mahina
Launch koho (SELECT) hana maikaʻi me nā papa inoa maʻalahi o nā helu ma ke ʻano he waiwai. No ka laʻana, inā loaʻa i ka cell B2 ka helu o ka mahina, ʻo ka hana koho (SELECT) hiki ke helu i ka hapaha kālā nona ia. Ma ka laʻana ma lalo nei, hoʻomaka ka makahiki kālā i Iulai.
Hōʻike ke kumu i nā waiwai 12 e pili ana i nā mahina 1 a 12. Hoʻomaka ka makahiki kālā i Iulai, no laila hāʻule nā mahina 7, 8, a me 9 i ka hapaha mua. Ma ka papa ma lalo nei, hiki iā ʻoe ke ʻike i ka helu hapaha kālā ma lalo o kēlā me kēia helu mahina.
I ka hana koho (SELECT) Pono e hoʻokomo i ka helu hapahā ma ke ʻano i hōʻike ʻia ma ka papa. No ka laʻana, i ka papa inoa o nā waiwai hana koho (koho) ma na kulana 7, 8 a me 9 (Iulai, ʻAukake a me Kepakemapa) pono ke helu 1.
=CHOOSE(C2,3,3,3,4,4,4,1,1,1,2,2,2)
=ВЫБОР(C2;2;3;3;3;4;4;4;1;1;1;2;2;2)
E hoʻokomo i ka helu o ka mahina ma ke kelepona C2, a me ka hana koho (SELECT) e helu i ka helu hapaha kālā ma ke kelepona C3.
Laʻana 2: E helu i ka lā o ka Pōʻakahi aʻe
Launch koho (SELECT) hiki ke hana pū me ka hana LA WEEKIKA (DAYWEEK) e helu i nā lā e hiki mai ana. No ka laʻana, inā he lālā ʻoe o kahi hui e hui ana i kēlā me kēia ahiahi Pōʻakahi, a laila ma ka ʻike ʻana i ka lā o kēia lā, hiki iā ʻoe ke helu i ka lā no ka Pōʻakahi aʻe.
Hōʻike ka kiʻi ma lalo nei i nā helu serial o kēlā me kēia lā o ka pule. Aia ma ke kolamu H no kēlā me kēia lā o ka pule ka helu o nā lā e hoʻohui i ka lā o kēia manawa e kiʻi ai i ka Pōʻakahi e hiki mai ana. No ka laʻana, pono ʻoe e hoʻohui i hoʻokahi lā wale nō i ka Lāpule. A ina he Poakahi keia, alaila, he ehiku la i koe a hiki i ka Poakahi ae.
Inā aia ka lā i kēia manawa i loko o ke kelepona C2, a laila hoʻohana ke ʻano o ka cell C3 i nā hana LA WEEKIKA (LA) a koho (koho) e helu i ka lā o ka Poakahi aʻe.
=C2+CHOOSE(WEEKDAY(C2),1,7,6,5,4,3,2)
=C2+ВЫБОР(ДЕНЬНЕД(C2);1;7;6;5;4;3;2)
Laʻana 3: Hōʻike i ka nui o nā kūʻai aku no ka hale kūʻai i koho ʻia
Hiki iā ʻoe ke hoʻohana i ka hana koho (SELECT) i hui pū me nā hana ʻē aʻe e like me ʻO SUM (SUM). Ma kēia laʻana, e loaʻa iā mākou nā huina kūʻai no kahi hale kūʻai kikoʻī ma ke kuhikuhi ʻana i kāna helu ma ka hana koho (SELECT) ma ke ʻano he hoʻopaʻapaʻa, a me ka papa inoa ʻana i nā pae ʻikepili no kēlā me kēia hale kūʻai e helu ai i nā huina.
Ma kā mākou laʻana, ua hoʻokomo ʻia ka helu hale kūʻai (101, 102, a i ʻole 103) i ke kelepona C2. No ka loaʻa ʻana o kahi waiwai kuhikuhi e like me 1, 2, a i ʻole 3 ma kahi o 101, 102, a i ʻole 103, e hoʻohana i ke ʻano: =C2-100.
Aia ka ʻikepili kūʻai no kēlā me kēia hale kūʻai ma kahi kolamu ʻokoʻa e like me ka mea i hōʻike ʻia ma lalo nei.
I loko o kahi hana ʻO SUM E hoʻokō mua ʻia ka hana (SUM). koho (SELECT), ka mea e hoʻihoʻi i ka laulima hōʻuluʻulu makemake e pili ana i ka hale kūʻai i koho ʻia.
=SUM(CHOOSE(C2-100,C7:C9,D7:D9,E7:E9))
=СУММ(ВЫБОР(C2-100;C7:C9;D7:D9;E7:E9))
He laʻana kēia o kahi kūlana i ʻoi aku ka maikaʻi o ka hoʻohana ʻana i nā hana ʻē aʻe e like me INDEX (INDEX) a ʻO MATCH (HILI). Ma hope o kā mākou marathon, e ʻike mākou i kā lākou hana.