Ma'lumotlarni umumiy tozalash uchun Excel formulalari
Ko'p yillar davomida men nashrdan narsalarni qanday qilish kerakligini tasvirlash uchun manba sifatida foydalanardim va keyinroq qidirish uchun o'zim uchun rekord saqladim! Bir mijoz bizga falokat bo'lgan mijoz ma'lumotlari faylini berdi. Deyarli har bir maydon noto'g'ri formatlangan va natijada biz ma'lumotlarni import qila olmadik. Visual Basic yordamida tozalashni amalga oshirish uchun Excel uchun ajoyib qo'shimchalar mavjud bo'lsa-da, biz Mac uchun Office dasturini ishga tushiramiz, bu esa makrolarni qo'llab-quvvatlamaydi. Buning o'rniga, biz yordam berish uchun to'g'ridan-to'g'ri formulalarni qidiramiz. Siz ulardan foydalanishingiz uchun ulardan ba'zilarini shu yerda baham ko'raman deb o'yladim.
Raqamli bo'lmagan belgilarni olib tashlang
Tizimlar ko'pincha telefon raqamlarini ma'lum bir 11 xonali formulaga mamlakat kodi bilan kiritilishini va tinish belgilarini talab qilmaydi. Biroq, odamlar ko'pincha bu ma'lumotlarni o'rniga tire va nuqtalar bilan kiritadilar. Buning uchun ajoyib formula 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))
Olingan ustunni nusxalashingiz va foydalanishingiz 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 buning oโrniga OR iborasini yozishingiz mumkinligini tushunishmaydi. Quyidagi misolda etishmayotgan ma'lumotlar uchun A2, B2, C2, D2 yoki E2 ni tekshirmoqchiman. Agar biron bir ma'lumot etishmayotgan bo'lsa, men 0 ni qaytaraman; aks holda, a 1. Bu menga ma'lumotlarni saralash va to'liq bo'lmagan yozuvlarni o'chirish imkonini beradi.
=IF(OR(A2="",B2="",C2="",D2="",E2=""),0,1)
Maydonlarni qirqish va birlashtirish
Agar maสผlumotlaringizda ism va familiya maydonlari mavjud boสปlsa, lekin importingizda toสปliq nom maydoni boสปlsa, oสปrnatilgan Excel funksiyasini birlashtirish yordamida maydonlarni bir-biriga bogสปlashingiz mumkin, lekin avval yoki keyin boสปsh joylarni olib tashlash uchun TRIM-dan foydalanganingizga ishonch hosil qiling. matn. Agar maydonlardan birida ma'lumotlar bo'lmasa, biz butun maydonni TRIM bilan o'rab olamiz:
=TRIM(CONCATENATE(TRIM(A1)," ",TRIM(B1)))
Haqiqiy elektron pochta manzilini tekshiring
@ va ni qidiradigan juda oddiy formula. elektron pochta manzilida (emas RFC standarti
):=AND(FIND(โ@โ,A2),FIND(โ.โ,A2),ISERROR(FIND(โ โ,A2)))
Ism va familiyalarni ajratib oling
Ba'zida muammo aksincha bo'ladi. Sizning ma'lumotlaringizda to'liq ism maydoni mavjud, ammo siz ism va familiyalarni tahlil qilishingiz kerak. Ushbu formulalar ism va familiya orasidagi bo'shliqni qidiradi va kerak bo'lganda matnni oladi. Shuningdek, u A2 formatida familiya yoki bo'sh yozuv bo'lmasa ham ishlaydi.
=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โฆ
Hech qachon meta tavsiflaringizni tozalashni xohlaganmisiz? Agar siz tarkibni Excelga tortmoqchi bo'lsangiz va undan so'ng Meta tavsifi maydonida foydalanish uchun tarkibni qisqartirmoqchi bo'lsangiz (150 dan 160 gacha), buni ushbu formuladan foydalanib qilishingiz mumkin. U bo'sh joydagi tavsifni toza buzadi va keyin ... 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 uchun mo'ljallanmaganโฆ faqat bir necha tezkor formulalar sizga tezroq boshlashingizga yordam beradi! Yana qanday formulalardan foydalanasiz? Izohlarda ularni qo'shing va men ushbu maqolani yangilaganim uchun sizga kredit beraman.