Kata-kata yang salah eja, spasi tertinggal yang membandel, awalan yang tidak diinginkan, huruf besar yang tidak tepat, dan karakter noncetak memberikan kesan pertama yang buruk. Dan itu bahkan bukan daftar lengkap cara data Anda bisa menjadi kotor. Singsingkan lengan baju Anda. Inilah saatnya untuk melakukan pembersihan besar-besaran lembar kerja Anda dengan Microsoft Excel.
Dasar-dasar pembersihan data Anda
Anda tidak selalu memiliki kontrol atas format dan tipe data yang Anda impor dari sumber data eksternal, seperti database, file teks, atau halaman Web. Sebelum Anda dapat menganalisis data, Anda sering perlu membersihkannya. Untungnya, Excel memiliki banyak fitur untuk membantu Anda mendapatkan data dalam format yang tepat seperti yang Anda inginkan. Terkadang, tugasnya mudah dan ada fitur tertentu yang melakukan pekerjaan untuk Anda. Misalnya, Anda dapat dengan mudah menggunakan Pemeriksa Ejaan untuk membersihkan kata-kata yang salah eja di kolom yang berisi komentar atau deskripsi. Atau, jika ingin menghapus baris duplikat, Anda dapat melakukannya dengan cepat menggunakan kotak dialog Hapus Duplikat .
Di lain waktu, Anda mungkin perlu memanipulasi satu atau beberapa kolom menggunakan rumus untuk mengonversi nilai yang diimpor menjadi nilai baru. Misalnya, jika ingin menghapus spasi akhir, Anda dapat membuat kolom baru untuk membersihkan data menggunakan rumus, mengisi kolom baru, mengonversi rumus kolom baru tersebut menjadi nilai, lalu menghapus kolom asli.
Langkah-langkah dasar untuk membersihkan data adalah sebagai berikut:
Impor data dari sumber data eksternal.
Buat salinan cadangan data asli di buku kerja terpisah.
Pastikan data berada dalam format tabel baris dan kolom dengan: data yang serupa di setiap kolom, semua kolom dan baris terlihat, dan tidak ada baris kosong dalam rentang tersebut. Untuk hasil terbaik, gunakan tabel Excel.
Lakukan tugas yang tidak memerlukan manipulasi kolom terlebih dahulu, seperti memeriksa ejaan atau menggunakan kotak dialog Temukan dan Ganti .
Berikutnya, lakukan tugas yang memerlukan manipulasi kolom. Langkah-langkah umum untuk memanipulasi kolom adalah:
- Sisipkan kolom baru (B) di samping kolom asli (A) yang perlu dibersihkan.
- Tambahkan rumus yang akan mengubah data di bagian atas kolom baru (B).
- Isi rumus di kolom baru (B). Dalam tabel Excel, kolom terhitung dibuat secara otomatis dengan nilai yang diisi.
- Pilih kolom baru (B), salin, lalu tempel sebagai nilai ke dalam kolom baru (B).
- Hapus kolom asli (A), yang mengonversi kolom baru dari B menjadi A.
Untuk membersihkan sumber data yang sama secara berkala, pertimbangkan untuk merekam makro atau menulis kode untuk mengotomatiskan seluruh proses. Terdapat juga sejumlah add-in eksternal yang ditulis oleh vendor pihak ketiga, yang tercantum di bagian Penyedia pihak ketiga , yang dapat Anda pertimbangkan untuk digunakan jika Anda tidak memiliki waktu atau sumber daya untuk mengotomatiskan proses sendiri.
| Informasi selengkapnya | Deskripsi |
|---|---|
| Mengisi data secara otomatis dalam sel lembar kerja | Memperlihatkan cara menggunakan perintah Isi. |
|
Membuat dan memformat tabel Mengubah ukuran tabel dengan menambahkan atau menghapus baris dan kolom Menggunakan kolom terhitung dalam tabel Excel |
Tunjukkan cara membuat tabel Excel dan menambahkan atau menghapus kolom atau kolom terhitung. |
| Membuat makro | Memperlihatkan beberapa cara untuk mengotomatiskan tugas berulang menggunakan makro. |
Pemeriksaan ejaan
Anda dapat menggunakan pemeriksa ejaan untuk tidak hanya menemukan kata yang salah eja, tetapi juga untuk menemukan nilai yang tidak digunakan secara konsisten, seperti nama produk atau perusahaan, dengan menambahkan nilai tersebut ke kamus kustom.
| Informasi selengkapnya | Deskripsi |
|---|---|
| Memeriksa ejaan dan tata bahasa | Menunjukkan cara mengoreksi kata yang salah eja di lembar kerja. |
| Gunakan kamus kustom untuk menambahkan kata ke pemeriksa ejaan | Menjelaskan cara menggunakan kamus kustom. |
Menghapus baris duplikat
Baris duplikat adalah masalah umum saat Anda mengimpor data. Sebaiknya filter untuk mendapatkan nilai unik terlebih dahulu untuk memastikan bahwa hasilnya sesuai dengan keinginan Anda sebelum menghapus nilai duplikat.
| Informasi selengkapnya | Deskripsi |
|---|---|
| Memfilter untuk mendapatkan nilai unik atau menghapus nilai duplikat | Memperlihatkan dua prosedur yang terkait erat: cara memfilter baris unik dan cara menghapus baris duplikat. |
Menemukan dan mengganti teks
Anda mungkin ingin menghapus string utama umum, seperti label yang diikuti dengan titik dua dan spasi, atau akhiran, seperti frasa tanda kurung di akhir string yang sudah usang atau tidak diperlukan. Anda dapat melakukannya dengan menemukan contoh teks tersebut lalu menggantinya tanpa teks atau teks lainnya.
| Informasi selengkapnya | Deskripsi |
|---|---|
|
Periksa apakah sel berisi teks (tidak peka huruf besar/kecil) Periksa apakah sel berisi teks (peka huruf besar/kecil) |
Tunjukkan cara menggunakan perintah Temukan dan beberapa fungsi untuk menemukan teks. |
| Menghapus karakter dari teks | Memperlihatkan cara menggunakan perintah Ganti dan beberapa fungsi untuk menghapus teks. |
| Menemukan atau mengganti teks dan angka di lembar kerja | Tunjukkan cara menggunakan kotak dialog Temukan dan Ganti . |
|
FIND, FINDB SEARCH, SEARCHB REPLACE, REPLACEB SUBSTITUTE LEFT, LEFTB RIGHT, RIGHTB LEN, LENB MID, MIDB |
Berikut adalah fungsi yang dapat Anda gunakan untuk melakukan berbagai tugas manipulasi string, seperti menemukan dan mengganti substring di dalam string, mengekstrak bagian string, atau menentukan panjang string. |
Mengubah huruf besar/kecil teks
Terkadang teks datang dalam tas campuran, terutama ketika menyangkut kasus teks. Dengan menggunakan satu atau beberapa dari tiga fungsi Huruf kecil, Anda dapat mengonversi teks menjadi huruf kecil, seperti alamat email, huruf besar, seperti kode produk, atau huruf besar, seperti nama atau judul buku.
| Informasi selengkapnya | Deskripsi |
|---|---|
| Mengubah kapitalisasi huruf teks | Menunjukkan cara menggunakan tiga fungsi Case. |
| LOWER | Mengonversi semua huruf besar dalam string teks menjadi huruf kecil. |
| PROPER | Menjadikan huruf besar untuk huruf pertama dalam string teks dan huruf-huruf lain dalam teks yang mengikuti karakter selain huruf. Mengonversi semua huruf lain menjadi huruf kecil. |
| UPPER | Mengonversi teks menjadi huruf besar. |
Menghapus spasi dan karakter noncetak dari teks
Terkadang nilai teks berisi karakter awal, akhir, atau beberapa karakter spasi yang disematkan (nilai rangkaian karakter Unicode 32 dan 160), atau karakter noncetak (nilai rangkaian karakter Unicode 0 hingga 31, 127, 129, 141, 143, 144, dan 157). Karakter ini terkadang dapat menyebabkan hasil yang tidak terduga saat Anda mengurutkan, memfilter, atau mencari. Misalnya, dalam sumber data eksternal, pengguna mungkin membuat kesalahan ketik dengan tidak sengaja menambahkan karakter spasi tambahan, atau data teks yang diimpor dari sumber eksternal mungkin berisi karakter noncetak yang disematkan dalam teks. Karena karakter-karakter ini tidak mudah diperhatikan, hasil yang tidak terduga mungkin sulit dipahami. Untuk menghapus karakter yang tidak diinginkan ini, Anda dapat menggunakan kombinasi fungsi TRIM, CLEAN, dan SUBSTITUTE.
| Informasi selengkapnya | Deskripsi |
|---|---|
| CODE | Mengembalikan kode numerik untuk karakter pertama dalam string teks. |
| CLEAN | Menghapus 32 karakter noncetak pertama dalam kode ASCII 7-bit (nilai 0 hingga 31) dari teks. |
| TRIM | Menghapus karakter spasi ASCII 7-bit (nilai 32) dari teks. |
| SUBSTITUTE | Anda dapat menggunakan fungsi SUBSTITUTE untuk mengganti karakter Unicode yang bernilai lebih tinggi (nilai 127, 129, 141, 143, 144, 157, dan 160) dengan karakter ASCII 7-bit yang dirancang untuk fungsi TRIM dan CLEAN. |
Memperbaiki angka dan tanda angka
Ada dua masalah utama dengan angka yang mungkin mengharuskan Anda membersihkan data: angka secara tidak sengaja diimpor sebagai teks, dan tanda negatif perlu diubah ke standar untuk organisasi Anda.
| Informasi selengkapnya | Deskripsi |
|---|---|
| Mengonversi angka yang disimpan sebagai teks menjadi angka | Menunjukkan cara mengonversi angka yang diformat dan disimpan dalam sel sebagai teks, yang dapat menyebabkan masalah penghitungan atau menghasilkan urutan pengurutan yang membingungkan, ke format angka. |
| DOLLAR | Mengonversi angka menjadi format teks dan menerapkan simbol mata uang. |
| TEKS | Mengonversi nilai menjadi teks dalam format angka tertentu. |
| DIPERBAIKI | Membulatkan angka menjadi jumlah desimal yang ditentukan, memformat angka dalam format desimal dengan menggunakan tanda titik dan koma, dan mengembalikan hasilnya sebagai teks. |
| NILAI | Mengonversi string teks yang menyatakan angka menjadi angka. |
Memperbaiki tanggal dan waktu
Karena ada begitu banyak format tanggal yang berbeda, dan karena format ini mungkin dikacaukan dengan kode bagian bernomor atau string lain yang berisi tanda miring atau tanda hubung, tanggal dan waktu sering perlu dikonversi dan diformat ulang.
| Informasi selengkapnya | Deskripsi |
|---|---|
| Mengubah sistem tanggal, format, atau interpretasi tahun dua digit | Menjelaskan cara kerja sistem penanggalan di Office Excel. |
| Mengonversi waktu | Menunjukkan cara mengonversi di antara satuan waktu yang berbeda. |
| Mengonversi tanggal yang disimpan sebagai teks menjadi tanggal | Menunjukkan cara mengonversi tanggal yang diformat dan disimpan dalam sel sebagai teks, yang dapat menyebabkan masalah dengan penghitungan atau menghasilkan urutan pengurutan yang membingungkan, ke format tanggal. |
| TANGGAL | Mengembalikan nomor seri berurutan yang mewakili tanggal tertentu. Jika format sel adalah Umum sebelum fungsi dimasukkan, hasil diformat sebagai tanggal. |
| DATEVALUE | Mengonversi tanggal yang direpresentasikan oleh teks menjadi nomor seri. |
| TIME | Mengembalikan angka desimal untuk waktu tertentu. Jika format sel adalah Umum sebelum fungsi dimasukkan, hasil diformat sebagai tanggal. |
| TIMEVALUE | Mengembalikan angka desimal dari waktu yang dinyatakan oleh string teks. Angka desimal adalah nilai mulai dari 0 (nol) hingga 0,99999999, yang mewakili waktu dari 0:00:00 (12:00:00 AM) hingga 23:59:59 (23:59:59 PM). |
Menggabungkan dan memisahkan kolom
Tugas umum setelah mengimpor data dari sumber data eksternal adalah menggabungkan dua kolom atau lebih menjadi satu, atau membagi satu kolom menjadi dua kolom atau lebih. Misalnya, Anda mungkin ingin membagi kolom yang berisi nama lengkap menjadi nama depan dan belakang. Atau, Anda mungkin ingin membagi kolom yang berisi bidang alamat menjadi kolom jalan, kota, kawasan, dan kode pos yang terpisah. Kebalikannya mungkin juga benar. Anda mungkin ingin menggabungkan kolom Nama Depan dan Nama Belakang menjadi kolom Nama Lengkap, atau menggabungkan kolom alamat terpisah menjadi satu kolom. Nilai umum tambahan yang mungkin memerlukan penggabungan menjadi satu kolom atau pemisahan menjadi beberapa kolom termasuk kode produk, jalur file, dan alamat Protokol Internet (IP).
| Informasi selengkapnya | Deskripsi |
|---|---|
|
Menggabungkan nama depan dan belakang Menggabungkan teks dan angka Menggabungkan teks dengan tanggal atau waktu Menggabungkan dua kolom atau lebih menggunakan satu fungsi |
Tampilkan contoh umum penggabungan nilai dari dua kolom atau lebih. |
| Membagi teks ke dalam kolom yang berbeda dengan Panduan Konversi Teks ke Kolom | Memperlihatkan cara menggunakan wizard ini untuk memisahkan kolom berdasarkan berbagai pemisah umum. |
| Membagi teks ke dalam kolom yang berbeda dengan fungsi | Menunjukkan cara menggunakan fungsi LEFT, MID, RIGHT, SEARCH, dan LEN untuk membagi kolom nama menjadi dua kolom atau lebih. |
| Menggabungkan atau memisahkan konten sel | Menunjukkan cara menggunakan fungsi CONCATENATE, operator & (ampersand), dan Panduan Konversi Teks ke Kolom. |
| Menggabungkan sel atau memisahkan sel yang digabungkan | Menunjukkan cara menggunakan perintah Gabungkan Sel, Gabungkan Selatan, dan Gabungkan dan Pusatkan. |
| CONCATENATE | Menggabungkan dua string teks atau lebih ke dalam satu string teks. |
Mentransformasi dan menata ulang kolom dan baris
Sebagian besar fitur analisis dan pemformatan di Office Excel mengasumsikan bahwa data ada dalam satu tabel dua dimensi datar. Terkadang Anda mungkin ingin membuat baris menjadi kolom, dan kolom menjadi baris. Di lain waktu, data bahkan tidak terstruktur dalam format tabel, dan Anda memerlukan cara untuk mengubah data dari format nontabel ke format tabel.
| Informasi selengkapnya | Deskripsi |
|---|---|
| TRANSPOSE | Mengembalikan rentang vertikal sel sebagai rentang horizontal, atau sebaliknya. |
Rekonsiliasi data tabel dengan menggabungkan atau mencocokkan
Terkadang, administrator database menggunakan Office Excel untuk menemukan dan memperbaiki kesalahan pencocokan ketika dua tabel atau lebih digabungkan. Hal ini mungkin melibatkan rekonsiliasi dua tabel dari lembar kerja yang berbeda, misalnya, untuk melihat semua catatan di kedua tabel atau untuk membandingkan tabel dan menemukan baris yang tidak cocok.
| Informasi selengkapnya | Deskripsi |
|---|---|
| Mencari nilai dalam daftar data | Memperlihatkan cara umum untuk mencari data menggunakan fungsi pencarian. |
| LOOKUP | Mengembalikan nilai dari rentang satu baris atau satu kolom atau dari larik. Fungsi LOOKUP memiliki dua bentuk sintaks: formulir vektor dan formulir array. |
| HLOOKUP | Mencari nilai di baris atas tabel atau larik nilai, lalu mengembalikan nilai dalam kolom yang sama dari baris yang Anda tentukan dalam tabel atau larik. |
| VLOOKUP | Mencari nilai dalam kolom pertama larik tabel dan mengembalikan nilai dalam baris yang sama dari kolom lain dalam array tabel. |
| INDEKS | Mengembalikan nilai atau referensi ke sebuah nilai dari dalam tabel atau rentang. Ada dua bentuk fungsi INDEX: formulir array dan formulir referensi. |
| MATCH | Mengembalikan posisi relatif item dalam array yang cocok dengan nilai tertentu dalam urutan tertentu. Gunakan MATCH ketimbang salah satu dari fungsi LOOKUP ketika Anda membutuhkan posisi sebuah item dalam rentang dan bukannya item itu sendiri. |
| OFFSET | Mengembalikan referensi ke rentang yang merupakan jumlah baris dan kolom tertentu dari sel atau rentang sel. Referensi yang dikembalikan dapat berupa sel tunggal atau rentang sel. Anda dapat menentukan jumlah baris dan jumlah kolom yang dikembalikan. |
Penyedia pihak ketiga
Berikut ini adalah sebagian daftar penyedia pihak ketiga yang memiliki produk yang digunakan untuk membersihkan data dengan berbagai cara.
Catatan
Microsoft tidak menyediakan dukungan untuk produk pihak ketiga.