Ka'idodin Tsabtace Bayanai na Kowa a cikin Excel

mafi kyau dabaru

Na tsawon shekaru, Na yi amfani da bugawa azaman hanya don ba kawai bayanin yadda ake abubuwa ba, amma don adana wa kaina kaina don duba baya! A yau, muna da abokin ciniki wanda ya ba mu fayil ɗin bayanan abokin ciniki wanda ya kasance bala'i. Kusan kowane fanni bai dace ba kuma; sakamakon haka, mun kasa shigo da bayanan. Duk da yake akwai wasu manyan add-ons don Excel don yin tsabtace ta amfani da Kayayyakin Kayayyaki, muna gudanar da Office don Mac wanda ba zai goyi bayan macros ba. Madadin haka, muna neman madaidaiciyar dabara don taimakawa. Ina tsammanin zan raba wasu daga waɗanda ke nan kawai don wasu su iya amfani da su.

Cire harafin Mara lamba

Tsarin tsari yawanci suna buƙatar saka lambobin waya a cikin takamaiman, dabara mai lamba 11 tare da lambar ƙasa kuma babu alamar rubutu. Koyaya, masu goyon baya sukan shigar da wannan bayanan tare da dashes da lokaci maimakon. Ga babban tsari don cire duk ba haruffa ba lamba a cikin Excel. Tsarin yana nazarin bayanan a cikin sel 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))

Yanzu zaku iya kwafin sakamakon da aka samu kuma kuyi amfani da shi Shirya> Manna Dabi'u don yin rubutu akan bayanan tare da kyakkyawan tsarin da aka tsara.

Kimanta Filaye da yawa tare da OR

Sau da yawa muna tsabtace bayanan da basu cika ba daga shigo da kaya. Masu amfani ba su gane cewa ba koyaushe ke rubuta ƙa'idodin tsarin tsari ba kuma za ku iya rubuta bayanin OR a maimakon haka. A cikin wannan misalin da ke ƙasa, Ina so in bincika A2, B2, C2, D2, ko E2 don ɓacewar bayanai. Idan wani bayanan ya ɓace, zan dawo da 0, in ba haka ba 1. Hakan zai ba ni damar tsara bayanan da kuma share bayanan da ba su cika ba.

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

Gyara da atididdigar atungiyoyin

Idan bayananku suna da filaye na Farko da na Lastarshe, amma shigo da ku yana da filin suna cikakke, zaku iya haɗa filayen tare da kyau ta amfani da ginannen Excel Function Concatenate, amma tabbatar da amfani da TRIM don cire kowane fanko a gaban ko bayan rubutu. Muna nade dukkan filin da TRIM a yayin da daya daga cikin filayen bashi da bayanai:

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

Duba adireshin Imel mai inganci

Kyakkyawan tsari mai sauƙi wanda yake neman duka @ da. a cikin adireshin imel:

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

Cire Sunayen farko dana karshe

Wani lokaci, matsalar akasin haka ne. Bayananku suna da cikakken suna amma kuna buƙatar yin lafazin sunayen farko da na ƙarshe. Waɗannan dabarun suna neman sarari tsakanin sunan farko da na ƙarshe kuma suna karɓar rubutu a inda ya cancanta. Hakanan IT suna aiki idan babu suna na ƙarshe ko kuma akwai ɓoyayyen shiga a cikin A2.

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

Kuma sunan karshe:

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

Iyakance yawan haruffa da…

Shin kun taɓa so ku tsabtace bayanan kwatancenku? Idan kana so ka ja abun ciki zuwa cikin Excel sannan ka datsa abun ciki don amfani a cikin Meta Description filin (haruffa 150 zuwa 160), zaka iya yin hakan ta amfani da wannan dabara daga Tauraruwata. Yana tsabtace bayanin a sarari sannan kuma yana ƙara…:

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

Tabbas, waɗannan ba ana nufin su zama cikakke ba… kawai wasu dabarun sauri ne don taimaka muku fara farawa! Waɗanne hanyoyi ne kuke samun kanku amfani da su? Sanya su a cikin bayanan kuma zan baku daraja yayin da nake sabunta wannan labarin.

Me kuke tunani?

Wannan shafin yana amfani da Akismet don rage spam. Koyi yadda aka sarrafa bayanan bayaninka.