Contents
I nehinei i ka marathon 30 mau hana Excel i 30 mau lā Ua ʻike mākou i nā ʻano hewa me ka hoʻohana ʻana i ka hana ERROR.TYPE (ERROR TYPE) a hōʻoia i hiki ke hoʻohana pono no ka hoʻoponopono ʻana i nā hewa ma Excel.
Ma ka lā 18 o ka marathon, e hāʻawi mākou i ke aʻo ʻana i ka hana SEARCH (HILI). ʻImi ia i kahi ʻano (a i ʻole nā huaʻōlelo) i loko o kahi kaula kikokikona a hōʻike i kahi i loaʻa ai. E nānā pū mākou pehea e hana ai i nā kūlana kahi e hoʻolei ai kēia hana i kahi hewa.
No laila, e nānā pono kākou i ke kumumanaʻo a me nā hiʻohiʻona pono o ka hana SEARCH (HILI). Inā loaʻa iā ʻoe kekahi mau hoʻopunipuni a i ʻole nā hiʻohiʻona o ka hana ʻana me kēia hana, e ʻoluʻolu e kaʻana iā lākou i nā manaʻo.
Hana 18: HULI
Launch SEARCH Huli (SEARCH) i kahi kaula kikokikona i loko o kekahi kaula kikokikona, a inā loaʻa, hōʻike i kona kūlana.
Pehea e hiki ai iaʻu ke hoʻohana i ka hana SEARCH?
Launch SEARCH (SEARCH) ʻimi i kahi kaula kikokikona i loko o kekahi kaula kikokikona. Hiki iā ia ke:
- E ʻimi i kahi kaula kikokikona i loko o kekahi kaula kikokikona (case insensitive).
- E hoʻohana i nā huapalapala wildcard i kāu hulina.
- E hoʻoholo i ke kūlana hoʻomaka ma ka kikokikona i nānā ʻia.
Hulina Huli
Launch SEARCH (SEARCH) loaʻa kēia syntax:
SEARCH(find_text,within_text,[start_num])
ПОИСК(искомый_текст;текст_для_поиска;[нач_позиция])
- loaʻa_ kikokikona (search_text) ʻo ia ka kikokikona āu e ʻimi nei.
- loko_ kikokikona (text_for_search) – he kaula kikokikona kahi e huli ai.
- helu_hoʻomaka (start_position) - inā ʻaʻole i kuhikuhi ʻia, e hoʻomaka ka ʻimi mai ka ʻano mua.
Huli Huli (HILI)
Launch SEARCH (SEARCH) e hoʻihoʻi i ke kūlana o ke kaula pili mua, ʻaʻohe hihia. Inā makemake ʻoe i kahi hulina hihia, hiki iā ʻoe ke hoʻohana i ka hana E huli i (FIND), a mākou e hālāwai ai ma hope ma ka marathon 30 mau hana Excel i 30 mau lā.
Laʻana 1: ʻImi ʻana i ke kikokikona ma kahi kaula
E hoʻohana i ka hana SEARCH (SEARCH) no ka huli ʻana i kekahi kikokikona i loko o kahi kaula kikokikona. Ma kēia hiʻohiʻona, e ʻimi ana mākou i kahi ʻano hoʻokahi (i paʻi ʻia ma ke kelepona B5) i loko o ke kaula kikokikona i loaʻa ma ka cell B2.
=SEARCH(B5,B2)
=ПОИСК(B5;B2)
Inā loaʻa ka kikokikona, ʻo ka hana SEARCH (SEARCH) e hoʻihoʻi i ka helu kūlana o kona ʻano mua ma ke kaula kikokikona. Inā ʻaʻole i loaʻa, ʻo ka hopena he memo hewa #VALUE! (#SO).
Inā he hewa ka hopena, hiki iā ʻoe ke hoʻohana i ka hana IFERROR (IFERROR) no laila ma kahi o ka hoʻokō ʻana i ka hana SEARCH (SEARCH) hōʻike i ka memo pili. Hana IFERROR (IFERROR) ua hoʻokomo ʻia ma Excel e hoʻomaka ana i ka mana 2007. Ma nā mana mua, hiki ke loaʻa ka hopena like me ka hoʻohana ʻana. IF (IF) me ISERROR (EOSHIBKA).
=IFERROR(SEARCH(B5,B2),"Not Found")
=ЕСЛИОШИБКА(ПОИСК(B5;B2);"Not Found")
Laʻana 2: Ke hoʻohana ʻana i nā kāleka ʻāhiu me SEARCH
ʻO kekahi ala e nānā i ka hopena i hoʻihoʻi ʻia SEARCH (SEARCH), no ka hewa - e hoʻohana i ka hana ISNUMBER (ISNUMBER). Inā loaʻa ke kaula, ʻo ka hopena SEARCH (SEARCH) he helu, ʻo ia hoʻi he hana ISNUMBER (ISNUMBER) e hoʻi mai TRUE. Inā ʻaʻole ʻike ʻia ka kikokikona, a laila SEARCH (SEARCH) e hōʻike i kahi hewa, a ISNUMBER (ISNUMBER) e hoʻi mai FALSE.
Ma ka waiwai o ka hoopaapaa loaʻa_ kikokikona (search_text) hiki iā ʻoe ke hoʻohana i nā huapalapala wildcard. hōʻailona * (asterisk) pani i kekahi helu o na huapalapala a i ole, a ? (hōʻailona nīnau) pani i kekahi ʻano hoʻokahi.
I kā mākou laʻana, hoʻohana ʻia ke ʻano wildcard *, no laila e ʻike ʻia nā huaʻōlelo CENTRAL, CENTER, a me CENTER ma nā inoa alanui.
=ISNUMBER(SEARCH($E$2,B3))
=ЕЧИСЛО(ПОИСК($E$2;B3))
Laʻana 3: E hoʻoholo ana i ke kūlana hoʻomaka no SEARCH (SEARCH)
Inā mākou e kākau i ʻelua mau hōʻailona hōʻemi (palua negation) i mua o ka hana ISNUMBER (ISNUMBER), e hoʻihoʻi i nā waiwai 1/0 ma kahi o TRUE/FALSE (TRUE/FALSE). A laila, ka hana ʻO SUM (SUM) ma ke kelepona E2 e helu i ka huina o nā moʻolelo i loaʻa ai ka kikokikona hulina.
Ma kēia laʻana, hōʻike ke kolamu B:
inoa kūlanakauhale | ʻoihana
ʻO kā mākou hana ʻo ka ʻimi ʻana i nā ʻoihana i loaʻa ke kaula kikokikona i hoʻokomo ʻia i loko o ka cell E1. ʻO ke kumu ma ka cell C2 penei:
=--ISNUMBER(SEARCH($E$1,B2))
=--ЕЧИСЛО(ПОИСК($E$1;B2))
Ua loaʻa i kēia ʻano nā lālani i loaʻa ka huaʻōlelo "bank", akā i loko o kekahi o lākou ʻaʻole i loaʻa kēia huaʻōlelo ma ka inoa o ka ʻoihana, akā ma ka inoa o ke kūlanakauhale. ʻAʻole kūpono kēia iā mākou!
Hoʻopili ʻia kēlā me kēia inoa kūlanakauhale me kahi hōʻailona | (vertical bar), no laila mākou, e hoʻohana ana i ka hana SEARCH (SEARCH), hiki iā mākou ke ʻike i ke kūlana o kēia ʻano. Hiki ke kuhikuhi ʻia kona kūlana ma ke ʻano he waiwai o ka hoʻopaʻapaʻa helu_hoʻomaka (start_position) i ka hana "main". SEARCH (HILI). ʻO ka hopena, e nānā ʻole ʻia nā inoa kūlanakauhale e ka ʻimi.
I kēia manawa, e helu ʻia nā laina i hoʻopaʻa ʻia i ka huaʻōlelo "bank" ma ka inoa o ka ʻoihana:
=--ISNUMBER(SEARCH($E$1,B2,SEARCH("|",B2)))
=--ЕЧИСЛО(ПОИСК($E$1;B2;ПОИСК("|";B2)))