ʻO nā kaʻina hoʻomaʻemaʻe ʻikepili maʻamau i Excel

excel kau

No nā makahiki, ua hoʻohana wau i ka paʻi ma ke ʻano he kumuwaiwai e wehewehe ʻole pehea e hana ai i nā mea, akā e mālama pū i kahi moʻolelo naʻu e nānā aʻe ma hope. I kēia lā, ua loaʻa iā mākou kahi mea kūʻai aku i hāʻawi mai iā mākou i kahi faila ʻikepili mea kūʻai mai kahi pōʻino. Ua hana hewa ʻole ʻia kēlā me kēia kula a; ʻo ka hopena, ʻaʻole hiki iā mākou ke lawe mai i ka ʻikepili. ʻOiai aia kekahi mau mea hoʻohui maikaʻi loa no Excel e hana i ka hoʻomaʻemaʻe me ka hoʻohana ʻana iā Visual Basic, holo mākou i Office for Mac ʻaʻole e kākoʻo i nā kōmi ʻōuli. Akā, nānā mākou i nā formula pololei e kōkua. Ua manaʻo wau e kaʻana like wau i kekahi o kēlā mau mea i hiki i nā poʻe ʻē aʻe ke hoʻohana iā lākou.

Wehe i nā huapalapala helu ʻole

Koi pinepine nā ʻōnaehana i nā helu kelepona e hoʻokomo ʻia i loko o kahi kikoʻī, huahelu 11 huahelu me ka pāʻālua ʻāina a ʻaʻohe kahakaha. Eia nō naʻe, hoʻokomo pinepine nā poʻe i kēia ʻikepili me nā kuʻi a me nā manawa ma kahi. Eia kahi haʻilula maikaʻi no e hoʻoneʻe ana i nā huapalapala helu helu āpau i Excel. Loiloi ka hana i ka ʻikepili i ka pūnaewele A2:

=IF(A2="","",SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))*
ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10))

I kēia manawa hiki iā ʻoe ke kope i ke kolamu hopena a hoʻohana Hoʻoponopono> Nā helu kāpili e kākau ma luna o ka ʻikepili me ka hopena hoʻonohonoho pono.

Loiloi i nā kahua he nui me ka OR

Hoʻomaʻemaʻe pinepine mākou i nā moʻolelo ʻaʻole hoʻopiha. ʻAʻole ʻike nā mea hoʻohana ʻaʻole pono ʻoe e kākau i nā hana hierarchical paʻakikī a hiki iā ʻoe ke kākau i kahi ʻōlelo OR. I kēia hiʻohiʻona ma lalo, makemake wau e nānā iā A2, B2, C2, D2, a i ʻole E2 no nā ʻikepili i nalo. Inā nalowale kekahi ʻikepili, e hoʻihoʻi wau i kahi 0, a i ʻole a 1. E ʻae iaʻu e hoʻonohonoho i ke ʻikepili i nā ʻikepili a kāpae i nā moʻolelo i piha ʻole.

=IF(OR(A2="",B2="",C2="",D2="",E2=""),0,1)

ʻĀpana ʻoki a concatenate

Inā loaʻa i kāu ʻikepili kahi kahua mua a me ka inoa hope, akā he kahua inoa piha kāu e lawe mai ai, hiki iā ʻoe ke concatenate i nā māla me ka hoʻohana pono ʻana i ka mea i kūkulu ʻia ma Excel Function Concatenate, akā e hoʻohana pono iā TRIM e kāpae i nā wahi hakahaka ma mua a ma hope paha o ka huaʻōlelo. Hoʻopili mākou i ka māla holoʻokoʻa me TRIM i ka hanana ʻaʻohe ʻikepili o kekahi o nā kahua.

=TRIM(CONCATENATE(TRIM(A1)," ",TRIM(B1)))

E hōʻoia no ka leka uila maikaʻi

ʻO kahi kumumanaʻo maʻalahi maʻalahi e nānā nei no ka ʻelua a me ka. i kahi leka uila:

=AND(FIND(“@”,A2),FIND(“.”,A2),ISERROR(FIND(” “,A2)))

Unuhi i nā inoa mua a hope

I kekahi manawa, ʻokoʻa ka pilikia. He piha inoa piha kāu ʻikepili akā pono ʻoe e hoʻokaʻawale i nā inoa mua a me hope. Nānā kēia mau formulate i ka hakahaka ma waena o ka inoa mua a me ka hope a hoʻopaʻa i nā huaʻōlelo inā pono. Mālama pū ʻo IT inā ʻaʻohe inoa hope a i ʻole kahi komo hakahaka i A2.

=IFERROR(IF(SEARCH(" ",A2,1),LEFT(A2, SEARCH(" ",A2,1)),A2),IF(LEN(A2)>0,A2,""))

A ʻo ka inoa hope:

=IFERROR(IF(SEARCH(" ",A2,1),RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1)),A2),"")

Palena i ka helu o nā hāmeʻe a hoʻohui…

Makemake ʻoe e hoʻomaʻemaʻe i kāu wehewehe meta? Inā makemake ʻoe e huki i ka ʻike i loko o Excel a ʻokiʻoki i ka ʻike no ka hoʻohana ʻana i kahi kahua wehewehe Meta (150 a 160 mau huapalapala), hiki iā ʻoe ke hana i kēlā me ka hoʻohana ʻana i kēia kumuhana mai Kaʻu Spot. Maʻemaʻe e wāwahi i ka wehewehe ma kahi a laila hoʻohui i ka…:

=IF(LEN(A1)>155,LEFT(A1,FIND("*",SUBSTITUTE(A1," ","*",LEN(LEFT(A1,154))-LEN(SUBSTITUTE(LEFT(A1,154)," ",""))))) & IF(LEN(A1)>FIND("*",SUBSTITUTE(A1," ","*",LEN(LEFT(A1,154))-LEN(SUBSTITUTE(LEFT(A1,154)," ","")))),"…",""),A1)

ʻOiaʻiʻo, ʻaʻole pono kēia mau mea e laulā ... ʻo kekahi mau hana wikiwiki e kōkua iā ʻoe e hoʻomaka i kahi lele. He aha nā ʻano hana ʻē aʻe āu e ʻike ai e hoʻohana nei? Hoʻohui iā lākou i nā manaʻo a hāʻawi wau iā ʻoe i ka hōʻaiʻē ke hoʻomaikaʻi wau i kēia ʻatikala.

Pehea kou manaʻo?

Ke hoʻohana nei kēia pūnaewele i ka Akismet e ho'ēmi i ka spam. E aʻo pehea e hanaʻia ai kāuʻikeʻikepili.