Membuat Formula Kompleks

Dalam pengolahan data dengan menggunakan Microsoft Microsoft Excel, seringkali kita menghadapi pembuatan rumus yang panjang sehingga menyulitkan untuk membuatnya. Untuk mengatasi hal ini, kita dapat Membuat sebuah formula yang terdiri dari beberapa formula antara.

Langkah untuk membuat formula rumit yang disarankan adalah 1:

  1. Tentukan tujuan untuk membuat formula
  2. Buat atau pahami algoritmanya
  3. Buat formula antara sederhana untuk menghasilkan tujuan antara sampai hasil akhir (tujuan pembuatan formula) di sheet lain dengan posisi teks original yang sama
  4. Gabungkan formula di tempat yang seharusnya.
  5. Selesai

Untuk menggambarkan situasinya, kita akan membuat formula yang menghasilkan teks yang terakhir muncul setelah karakter tertentu. Sebagai contoh, berikut sebuah teks dari alamat website:

validasi-double

Microsoft Excel tidak memiliki fungsi yang langsung dapat mengekstrak karakter diawali dengan karakter / (garis miring terbalik) dalam hal ini kata “validasi-double”) dari sebuah teks. Untuk mengekstrak diperlukan formula tambahan yang menghitung posisi garis miring tersebut. Pada Gambar 2 ditunjukkan langkah demi langkah untuk mengekstrak teks tersebut. Teks asli ada pada sel A1. Formula di A3:A7 digunakan untuk menghasilkan teks yang diinginkan. Formula yang digunakan ditampilkan pada kolom B.

Gambar 2. Formula Untuk Mengekstrak Teks Setelah Karakter Tertentu

 

Penjelasan Formula antara, yang pada akhirnya digabung pada formula yang digunakan di sel A9. Dengan hasil validasi-double

  1. Hitung jumlah karakter “/” (Sel A3).

    Formula yang terdapat di sel A3 menghasilkan jumlah karakter “/” yang ada dalam teks A1. Formula =LEN(A1)-LEN(SUBSTITUTE(A1,”/”,””)) menghasilkan nilai 5,yang berarti terdapat (5) lima karakter “/” dalam teks yang ada di A1.

  2. Ganti karakter “/” terakhir dengan karakter apa-saja (Sel A4)

    Formula di sel A4 menggunakan fungsi SUBSTITUTE untuk mengganti “/” terakhir (yang dihitung di sel A3) dengan karakter lain. Dalam hal ini kita memilih CHAR(1) karena sangat kecil sekali karakter ini ada dalam teks. Hasilnya adalah “http://excel.aurino.com/2013/04validasi-double

  3. Cari posisi karakter baru (sel A5)

    Formula di sel A5 menggunakan fungsi FIND untuk menentukan posisi karakter baru. Hasilnya adalah pada posisi 32

  4. Hitung jumlah karakter setelah setelah karakter baru (sel A6)

    Formula di sel A5 menggunakan operasi pengurangan yaitu jumlah karakter teks asli dikurangi dengan posisi karakter baru. Dan hasilnya adalah panjang karakter setelah karakter baru.

  5. Ambil teks setelah karate baru (Sel A7)

    Formula di sel A5 menggunakan fungsi RIGHT untuk mengekstrak teks yang dimaksud.

  6. Menggabungkan lima formula menjadi satu (Sel A9)

    Lima formula dari langkah satu sampai dengan 5 digabungkan menjadi satu formula.

    1. Pilih sel yang akan berisi hasil akhir dalam hal ini adalah sel A9. Hasil akhirnya akan sama dengan isi sel A7.

      =RIGHT(A1,A6)

    2. Pilih sel A6, dan tekan [F2] dan pilih rumus yang adalah dalam formula bar (tidak termasuk tanda sama dengannya), dan tekan [Ctrl+C] untuk mengcopy rumusnya, lalu tekan [Esc].
    3. Pilih sel A9 dan tempelkan rumus (paste) dengan menekan [Ctrl-V] pada formula bar untuk mengganti referensi sel A6. Formula di A9 sekarang adalah:

      =RIGHT(A1, LEN(A1)-A5)

    4. Formula berisi referensi ke sel A5, maka pilih sel A5 dan copy formula sebagai teks. Kemudian ganti referensi ke sel A5 dengan teks formula yang telah dicopy. Formula sekarang menjadi:

      =RIGHT(A1,LEN(A1)-FIND(CHAR(1),A4))

    5. Ganti referensi ke sel A4 dengan teks formula dari Sel A4. Formula sekarang menjadi:

      =RIGHT(A1,LEN(A1)-FIND(CHAR(1),SUBSTITUTE(A1,”/”,CHAR(1),A2)))

    6. Ganti referensi ke sel A3 dengan teks formula dari Sel A3. Formula sekarang menjadi:

      =RIGHT(A1,LEN(A1)-FIND(CHAR(1),SUBSTITUTE(A1,”/”,CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,”/”,””)))))

 

Hasil akhirnya semua referensi dalam formula di sel A9 sekarang adalah sel A1, dan formula antara sudah tidak diperlukan lagi. Satu formula dalam sel A9 telah mengganti fungsi dari lima formula di atasnya..

Teknik ini dapat diterapkan untuk keadaan yang hasil akhirnya memerlukan beberapa rumus antara.

Leave a Reply

Your email address will not be published. Required fields are marked *