Formula Excel

Jika anda menemukan data dengan angka yang berformat teks, tentunya ada tidak dapat menjumlahkan atau mengolah angka-angka tersebut untuk dijumlahkan, di-sumif, ataupun dengan rumus lainnya karena formatnya yang masih berupa teks. Berikut gambaran perbedaan format number dan format teks:

Jika Anda perhatikan di Formula Bar pada B1, formatnya adalah format number. Dengan format number ini anda dapat mengolah angka tersebut. Sedang pada A1, formatnya adalah format teks karena terdapat karakter koma dan titik. Angka dengan format seperti ini tidak dapat anda olah. Untuk dapat mengolah angka tersebut, anda harus menghilangkan karakter koma dan titik tersebut. Namun hati-hati, jika anda salah langkah dalam menghapus koma dan titik tersebut, maka hasilnya akan berbeda dengan angka awalnya. Berikut langkah-langkahnya:

• 1. Cek regional setting anda di control panel. Jika regional setting anda Indonesia, maka karakter pemisah ribuan adalah "." (titik) dan karakter pemisah desimal adalah "," (koma). Sedang jika regional setting anda adalah English (United States), maka karakter pemisah ribuan adalah "," (koma) dan karakter pemisah desimal adalah "." (titik).
• 2. Blok A1:A17 lalu pilih Edit > Replace (Ctrl + H). Karena komputer saya regional settingnya adalah Indonesia, maka ketik pada kolom Find what: dengan "," (koma) dan kosongkan pada kolom Replace with: lalu klik Replace All. Anda dapat melihat bahwa karakter "," (koma) sudah hilang. Jangan tekan tombol Close dulu.

• 3. Masih di kotak dialog Find and Replace, sekarang ganti pada kolom Find what: dengan "." (titik) dan pada kolom Replace with: dengan "," (koma), lalu tekan tombol Replace All.

Dengan begitu, anda sudah bisa mengolah angka tersebut untuk dijumlahkan atau yang lainnya.

Catatan: Jika regional setting anda adalah English (United States), maka ada cukup mengganti karakter koma dengan titik, dan titik dengan koma sesuai dengan urutan langkah-langkah di atas.

Jika Anda bekerja dengan banyak data yang menggunakan nama orang, suatu saat Anda mungkin ingin memisahkan nama-nama tersebut menjadi dua bagian, yaitu nama dengan dan nama belakang. Dengan menggunkan rumus ini, pekerjaan tersebut sangat mudah dilakukan:

Pada kolom A terdapat nama lengkap, dan pada kolom B anda ingin mengambil nama depannya saja, sedang kolom C anda ingin mengambil nama belakangnya. Maka cukup tuliskan rumus:

• LEFT(A1 artinya anda akan mengambil nama dari kolom A1 yang dimulai dari paling kiri.
• FIND(" ";$A$1:$A$16;1)-1 akan mencari karakter spasi " ". Misal pada nama "Aan Arifin" karakter spasi ditemukan pada karakter ke-4, maka nilai yang dihasilkan adalah 4. Rumus -1 artinya angka 4 tadi dikurangi dengan -1 agar menjadi 3, sesuai dengan nama "Aan" yang hanya ada 3 karakter.

• =MID(A1; artinya anda akan mengambil nama dari kolom A1 yang dimulai dari tengah.
• FIND(" ";$A$1:$A$16;1)+1, sama seperti rumus FIND di atas, untuk mencari karakter spasi " ", namun kali ini +1 untuk mencari nama belakang. Rumus FIND(" ";$A$1:$A$16;1)+1 akan menghasilkan angka 5, di mana nama "Arifin" dimulai dengan karakter ke-5. Rumus ;100) artinya karakter yang diambil untuk nama belakang diasumsikan 100 karakter.

Terkadang kita dihadapkan dengan banyak data yang bisa berjumlah ratusan atau bahkan ribuan. Dan masalahnya data tersebut mungkin saja bisa terjadi duplikasi data atau data lebih dari satu. Nah, kalau data yang terduplikasi tersebut menjadi reference atau sumber dari perhitungan tertentu, hal ini tentunya akan menjadi masalah bagi anda dalam mencari selisihnya.

Biar lebih mudah, berikut saya berikan gambarannya:

Kolom A dan B adalah sumber datanya, jumlah totalnya adalah 28. Jika Anda rekap di kolom C dan D dengan menggunakan rumus SUMIF dan terdapat duplikasi data, maka hasilnya akan berbeda dengan yang seharusnya. Kalau anda teliti pada data rekap, terjadi duplikasi pada item Avanza pada sel C21 dan C30.

Untuk memudahkan Anda mencari mana data yang terduplikasi, anda bisa membuat rumus COUNTIF dengan mudah. Berikut gambarannya:


Anda bisa membuat rumus sementara misalnya pada kolom E hanya untuk mencari duplikasi data. Penjelasan rumusnya sbb:

• Hasil dari rumus COUNTIF($C$19:$C$32;C19) adalah jumlah duplikasi data. Dalam hal ini type Avanza ada 2 duplikasi data, sedang yang lain hanya ada 1 data.
• Jika hasil dari perhitungan COUNTIF lebih dari 1, maka teks yang dimunculkan adalah "Duplikasi data", sedang jika tidak lebih dari 1, maka isi selnya kosong "".

Rumus IF dibuat hanya untuk memudahkan mata anda dalam mencari duplikasi data. Karena jika Anda hanya menuliskan rumus COUNTIF($C$19:$C$32;C19), maka hasilnya akan bervariasi tergantung jumlah duplikasi data.

Catatan: Selain menggunakan rumus ini, anda dapat dengan mudah mencari duplikasi data dengan menggunakan JMT Excel Utilities pada menu Worksheet Tools > Show Selection Tools... > Search for = Duplicate Values.