Formula Excel Untuk Pembersihan Data Biasa

Rumusan Pembersihan Data Excel

Selama bertahun-tahun, saya menggunakan penerbitan itu sebagai sumber untuk tidak hanya menerangkan bagaimana melakukan sesuatu, tetapi juga menyimpan rekod agar saya dapat melihatnya nanti! Hari ini, kami mempunyai pelanggan yang menyerahkan fail data pelanggan yang menjadi bencana kepada kami. Hampir setiap bidang salah format dan; hasilnya, kami tidak dapat mengimport data. Walaupun terdapat beberapa tambahan untuk Excel untuk melakukan pembersihan menggunakan Visual Basic, kami menjalankan Office untuk Mac yang tidak akan menyokong makro. Sebaliknya, kami mencari formula lurus untuk membantu. Saya fikir saya akan berkongsi beberapa yang ada di sini agar orang lain dapat menggunakannya.

Buang Karakter Bukan Numerik

Sistem sering memerlukan nombor telefon dimasukkan dalam formula 11 digit yang khusus dengan kod negara dan tanpa tanda baca. Walau bagaimanapun, orang sering memasukkan data ini dengan tanda sempang dan noktah. Inilah formula yang bagus untuk membuang semua aksara bukan angka dalam Excel. Formula mengkaji data dalam 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))

Sekarang anda boleh menyalin lajur yang dihasilkan dan menggunakan Edit> Tampal Nilai untuk menulis data dengan hasil yang diformat dengan betul.

Nilai pelbagai medan dengan OR

Kami sering membersihkan rekod yang tidak lengkap dari import. Pengguna tidak menyedari bahawa anda tidak selalu perlu menulis formula hierarki yang kompleks dan sebaliknya anda boleh menulis pernyataan ATAU. Dalam contoh ini di bawah, saya ingin memeriksa A2, B2, C2, D2, atau E2 untuk kehilangan data. Sekiranya ada data yang hilang, saya akan mengembalikan 0, sebaliknya 1. Itu akan membolehkan saya menyusun pesanan data dan menghapus rekod yang tidak lengkap.

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

Medan Potong dan Bersambung

Sekiranya data anda mempunyai medan Nama Pertama dan Akhir, tetapi import anda mempunyai medan nama penuh, anda boleh menggabungkan medan tersebut dengan kemas menggunakan fungsi gabungan dalam Excel Function, tetapi pastikan anda menggunakan TRIM untuk membuang ruang kosong sebelum atau sesudah teks. Kami membungkus seluruh bidang dengan TRIM sekiranya salah satu bidang tidak memiliki data:

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

Periksa Alamat E-mel yang Sah

Formula yang cukup mudah untuk kedua-dua @ dan. dalam alamat e-mel:

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

Ekstrak Nama Pertama dan Akhir

Kadang kala, masalahnya adalah sebaliknya. Data anda mempunyai bidang nama penuh tetapi anda perlu menguraikan nama depan dan belakang. Rumus ini mencari ruang antara nama depan dan nama belakang dan ambil teks jika perlu. IT juga menangani sekiranya tidak ada nama belakang atau ada entri kosong di A2.

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

Dan nama belakang:

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

Hadkan Bilangan Watak dan Tambah…

Adakah anda pernah mahu membersihkan penerangan meta anda? Sekiranya anda ingin menarik kandungan ke dalam Excel dan kemudian memangkas kandungan untuk digunakan dalam medan Penerangan Meta (150 hingga 160 aksara), anda boleh melakukannya menggunakan formula ini dari Tempat Saya. Ia dengan jelas memecahkan keterangan di ruang dan kemudian menambahkan…:

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

Sudah tentu, ini tidak bermaksud menyeluruh ... hanya beberapa formula cepat untuk membantu anda memulakannya! Apakah formula lain yang anda gunakan sendiri? Tambahkan mereka dalam komen dan saya akan memberi anda penghargaan semasa saya mengemas kini artikel ini.

Apa yang anda fikir?

Laman web ini menggunakan Akismet untuk mengurangkan spam. Ketahui bagaimana data komen anda diproses.