Njira Zomwe Anthu Amakonzera Dongosolo mu Excel

kupambana njira

Kwa zaka zambiri, ndakhala ndikugwiritsa ntchito bukuli ngati chothandiza kuti ndisangofotokoza momwe tingachitire zinthu, komanso kuti ndikhale ndi mbiri yanga yoyang'ana mtsogolo! Lero, tinali ndi kasitomala yemwe amatipatsa fayilo yamakasitomala yomwe inali tsoka. Pafupifupi gawo lililonse lidasinthidwa molakwika ndipo; Zotsatira zake, sitinathe kuitanitsa zomwe tapeza. Ngakhale pali zowonjezera zowonjezera za Excel zoyeretsa pogwiritsa ntchito Visual Basic, timayendetsa Office for Mac yomwe singagwirizane ndi macros. M'malo mwake, timayang'ana njira zowongoka kuti zitithandizire. Ndinaganiza kuti ndigawane ena pano kuti ena athe kuwagwiritsa ntchito.

Chotsani Anthu Osakhala Nambala

Machitidwe nthawi zambiri amafuna manambala a foni kuti aikidwe munthawi inayake, manambala 11 okhala ndi nambala yakudziko ndipo alibe zopumira. Komabe, anthu nthawi zambiri amalowetsa izi ndi ma dash komanso nthawi m'malo mwake. Nayi njira yabwino ya kuchotsa zilembo zonse zopanda manambala mu Excel. Njirayi imayang'ana zomwe zili mu selo 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))

Tsopano mutha kutengera zomwe zalembedwazi ndikugwiritsa ntchito Sinthani> Sakani Makhalidwe kuti mulembe pamtunduwu ndi zotsatira zake.

Unikani Minda Yambiri ndi OR

Nthawi zambiri timachotsa zolemba zosakwanira kuchokera ku katundu. Ogwiritsa ntchito samazindikira kuti simufunika kuti mulembe zolemba zovuta kwambiri nthawi zonse ndikuti mutha kulemba mawu a OR m'malo mwake. Mu chitsanzo ichi pansipa, ndikufuna kuwona A2, B2, C2, D2, kapena E2 pazosowa. Ngati pali chilichonse chomwe chikusoweka, ndibwezera 0, apo ayi 1. Izi zingandilole kuti ndisankhe ndalamazo ndikuchotsa zolemba zomwe sizili zonse.

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

Chepetsa ndi Minda Yokhazikika

Ngati deta yanu ili ndi gawo loyamba ndi lomaliza la dzina, koma kuitanitsa kwanu kuli ndi dzina lathunthu, mutha kulumikiza minda yonse moyenera pogwiritsa ntchito yomangidwa mu Excel Function Concatenate, koma onetsetsani kuti mukugwiritsa ntchito TRIM kuchotsa malo opanda kanthu kale kapena pambuyo pake mawu. Timakulunga gawo lonse ndi TRIM ngati m'munda umodzi mulibe deta:

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

Fufuzani Adilesi Yoyenera Ya Imelo

Fomula yosavuta yomwe imayang'ana pa @ ndi. mu imelo:

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

Chotsani Mayina Oyamba ndi Omaliza

Nthawi zina, vuto limakhala losiyana. Deta yanu ili ndi dzina lathunthu koma muyenera kufotokoza mayina oyamba ndi omaliza. Mitunduyi imayang'ana danga pakati pa dzina loyambirira ndi lomaliza ndikulemba mawu pomwe kuli kofunikira. Ikugwiranso ntchito ngati kulibe dzina lomaliza kapena palibe cholowa mu A2.

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

Ndipo dzina lomaliza:

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

Malire Chiwerengero cha Otchulidwa ndikuwonjezera ...

Kodi mudafunako kutsuka mafotokozedwe anu a meta? Ngati mukufuna kukopera zomwe zili mu Excel ndikuchepetsa zomwe zingagwiritsidwe ntchito pamunda wa Meta (zilembo 150 mpaka 160), mutha kuchita izi pogwiritsa ntchito fomuyi Malo Anga. Imasokoneza malongosoledwe ake mlengalenga ndikuwonjezera…:

=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)

Zachidziwikire, izi sizitanthauza kuti zikhale zokwanira ... njira zina zachangu zokuthandizani kuti muyambe kulumpha! Ndi njira zina ziti zomwe mumapezeka kuti mukugwiritsa ntchito? Awonjezereni mu ndemanga ndipo ndikupatsani mbiri yabwino pamene ndikusintha nkhaniyi.

Mukuganiza chiyani?

Tsambali likugwiritsa ntchito Akismet kuchepetsa spam. Phunzirani momwe deta yanu ikufotokozera.