Ma'lumotlarni umumiy tozalash uchun Excel formulalari

Excel ma'lumotlarni tozalash formulalari

Bir necha yillar davomida men nashrni nafaqat qanday ishlashni tavsiflash uchun emas, balki keyinchalik qarash uchun o'zim uchun yozuvlarni saqlash uchun manba sifatida ishlatib kelaman! Bugun bizda mijozga falokat bo'lgan ma'lumotlarning faylini topshirgan mijozimiz bor edi. Deyarli har bir soha noto'g'ri formatlangan va; Natijada biz ma'lumotlarni import qila olmadik. Visual Basic yordamida tozalashni amalga oshirish uchun Excel uchun bir nechta ajoyib qo'shimchalar mavjud bo'lsa-da, biz makroslarni qo'llab-quvvatlamaydigan Office for Mac dasturini ishga tushiramiz. Buning o'rniga, biz yordam beradigan to'g'ri formulalarni qidiramiz. Boshqalar ulardan foydalanishi uchun shunchaki ba'zi birlarini bu erda baham ko'rmoqchiman.

Raqamli bo'lmagan belgilarni olib tashlang

Tizimlar ko'pincha telefon raqamlarini mamlakat kodi va tinish belgilarisiz 11 raqamli formulaga kiritishni talab qiladi. Biroq, odamlar bu ma'lumotlarni ko'pincha chiziqcha va nuqta bilan kiritishadi. Mana buning ajoyib formulasi barcha raqamli bo'lmagan belgilarni olib tashlash Excelda. Formulada A2 katakchadagi ma'lumotlar ko'rib chiqiladi:

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

Endi siz olingan ustunni nusxalashingiz va ishlatishingiz mumkin Tartibga solish> Qiymatlarni joylashtirish to'g'ri formatlangan natija bilan ma'lumotlar ustiga yozish.

OR bilan bir nechta maydonlarni baholang

Biz ko'pincha to'liq bo'lmagan yozuvlarni importdan tozalaymiz. Foydalanuvchilar har doim ham murakkab ierarxik formulalarni yozishingiz shart emasligini va uning o'rniga OR buyrug'ini yozishingiz mumkinligini anglamaydilar. Quyidagi ushbu misolda men A2, B2, C2, D2 yoki E2 ma'lumotlarini etishmayotganligini tekshirmoqchiman. Agar biron bir ma'lumot etishmayotgan bo'lsa, men 0 ni qaytaraman, aks holda 1. Bu menga ma'lumotlarning tartibini tartiblash va to'liq bo'lmagan yozuvlarni o'chirish imkonini beradi.

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

Maydonlarni qirqish va birlashtirish

Agar sizning ma'lumotlaringizda "Familiya va familiya" maydonlari mavjud bo'lsa, lekin sizning importingiz to'liq ism maydoniga ega bo'lsa, siz Excel funktsiyasini birlashtirgan holda maydonlarni yaxshilab birlashtira olasiz, ammo TRIM-dan oldin yoki undan keyin bo'sh joylarni olib tashlash uchun foydalaning. matn. Maydonlardan birida ma'lumot bo'lmasa, biz butun maydonni TRIM bilan o'rab olamiz:

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

Haqiqiy elektron pochta manzilini tekshiring

@ Va ni ham qidiradigan juda oddiy formula. elektron pochta manzilida:

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

Ism va familiyalarni ajratib oling

Ba'zan, muammo aksincha bo'ladi. Ma'lumotlaringiz to'liq ism maydoniga ega, ammo ism va familiyalarni tahlil qilishingiz kerak. Ushbu formulalar ism va familiya orasidagi bo'shliqni qidiradi va kerak bo'lganda matnni oladi. Shuningdek, A2 da familiya bo'lmasa yoki bo'sh yozuv mavjud bo'lsa, IT ularni boshqaradi.

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

Va familiyasi:

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

Belgilar sonini cheklash va qo'shish…

Meta tavsiflaringizni tozalashni xohlaganmisiz? Agar siz tarkibni Excelga qo'shib, keyin Meta Description maydonida (150 dan 160 tagacha) foydalanish uchun tarkibni kesmoqchi bo'lsangiz, ushbu formuladan foydalanishingiz mumkin. Mening joyim. U bo'sh joydagi tavsifni toza tarzda buzadi va keyin quyidagilarni qo'shadi ...:

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

Albatta, bular keng qamrovli bo'lishi kerak emas ... shunchaki sakrashni boshlashga yordam beradigan tezkor formulalar! Siz yana qanday formulalardan foydalanmoqdasiz? Ularni sharhlarga qo'shing, men ushbu maqolani yangilaganimda sizga kredit beraman.

Siz nima deb o'ylaysiz?

Ushbu sayt spamni kamaytirish uchun Akismet-dan foydalanadi. Fikringiz ma'lumotlarining qanday ishlashini bilib oling.