CRM ndi Data PlatformZida Zamalonda

Mitundu ya Excel Yotsuka Dongosolo Lonse

Kwa zaka zambiri, ndakhala ndikugwiritsa ntchito bukuli pofotokoza mmene ndingachitire zinthu komanso kusunga mbiri yoti ndizidzayang’ana m’tsogolo! Wokasitomala adatipatsa fayilo ya data yamakasitomala yomwe inali tsoka. Pafupifupi magawo onse anali olakwika, ndipo chifukwa chake, sitinathe kulowetsa deta. Ngakhale pali zowonjezera zowonjezera za Excel kuti ziyeretsedwe pogwiritsa ntchito Visual Basic, timayendetsa Office for Mac, zomwe sizingagwirizane ndi macros. M'malo mwake, timayang'ana njira zowongoka kuti zithandizire. Ndinaganiza ndikugawana nawo ena apa kuti muthe kuwagwiritsa ntchito.

Chotsani Anthu Osakhala Nambala

Makina nthawi zambiri amafunikira manambala a foni kuti ayikidwe mumtundu wina wa manambala 11 okhala ndi khodi yadziko komanso opanda zizindikiro. Komabe, anthu nthawi zambiri amalowetsa izi ndi mizere ndi nthawi m'malo mwake. Nayi njira yabwino kwambiri 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))

Mukhoza kukopera ndime zotsatira ndi ntchito Sinthani> Sakani Makhalidwe kuti mulembe pamtunduwu ndi zotsatira zake.

Unikani Minda Yambiri ndi OR

Nthawi zambiri timachotsa zolembedwa zosakwanira kuchokera kuzinthu zomwe zachokera kunja. Ogwiritsa ntchito samazindikira kuti nthawi zonse simuyenera kulemba zolemba zovuta komanso kuti mutha kulemba OR mawu m'malo mwake. Ndikufuna kuyang'ana A2, B2, C2, D2, kapena E2 pakusowa deta mu chitsanzo pansipa. Ngati deta ikusowa, ndibwezera 0; mwinamwake, a 1. Izi zidzandilola kuti ndisanthule deta ndikuchotsa zolemba zosakwanira.

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

Chepetsa ndi Minda Yokhazikika

Ngati deta yanu ili ndi magawo a Dzina Loyamba ndi Lomaliza, koma kulowetsa kwanu kuli ndi dzina lathunthu, mutha kulumikiza magawowo mosamala pogwiritsa ntchito Excel Function Concatenate yomangidwa, koma onetsetsani kuti mukugwiritsa ntchito TRIM kuchotsa malo opanda kanthu musanayambe kapena pambuyo pake. mawu. Timakulunga gawo lonse ndi TRIM ngati gawo limodzi lilibe deta:

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

Fufuzani Adilesi Yoyenera Ya Imelo

Fomula yosavuta yomwe imayang'ana zonse @ ndi . mu imelo adilesi (osati a Mtengo wa RFC

):

=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 kutchula mayina oyamba ndi omaliza. Mafomuwa amayang'ana danga pakati pa dzina loyamba ndi lomaliza ndikugwira mawu ngati kuli kofunikira. Imagwiranso ngati palibe dzina lomaliza kapena cholembera chopanda kanthu 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 munayamba mwafuna kuyeretsa malongosoledwe anu a meta? Ngati mukufuna kukokera zomwe zili mu Excel ndikuchepetsa zomwe mugwiritse ntchito pagawo la Meta Description (zilembo 150 mpaka 160), mutha kuchita izi pogwiritsa ntchito fomula iyi. Imaphwanya malongosoledwewo pamalo amodzi 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)

Zoonadi, izi sizikutanthauza kuti zifotokoze zambiri… zina zachangu zomwe zingakuthandizeni kuti muyambe kudumpha! Ndi njira zina ziti zomwe mukupeza kuti mukugwiritsa ntchito? Onjezani mu ndemanga, ndipo ndikupatsani mbiri pamene ndikusintha nkhaniyi.

Douglas Karr

Douglas Karr ndi CMO OpenINSIGHTS ndi woyambitsa wa Martech Zone. Douglas wathandizira oyambitsa ambiri opambana a MarTech, wathandizira kulimbikira kopitilira $ 5 bil pakugula ndi kuyika kwa Martech, ndipo akupitiliza kuthandiza makampani kukhazikitsa ndikusintha njira zawo zogulitsa ndi zotsatsa. Douglas ndi katswiri wodziwika padziko lonse lapansi wosinthira digito komanso katswiri komanso wokamba nkhani wa MarTech. Douglas ndi mlembi wofalitsidwa wa kalozera wa Dummie komanso buku la utsogoleri wabizinesi.

Nkhani

Bwererani pamwamba
Close

Adblock yapezeka

Martech Zone imatha kukupatsirani izi popanda mtengo chifukwa timapanga ndalama patsamba lathu kudzera muzotsatsa, maulalo ogwirizana, komanso kuthandizira. Tingayamikire ngati mungachotse choletsa ad mukamawona tsamba lathu.