Belajar Microsoft Excel: Mengunci Dan Memproteksi Sel Berisi Formula

Ketika mendesain suatu template laporan berformat excel , mungkin kita mengharapkan user cuma sanggup melengkapi data pada sel yang diputuskan saja pada template laporan yang kita buat. Selain itu , kesibukan user mesti dibatasi mudah-mudahan tidak sanggup mengerjakan penyesuaian formula yang sanggup memunculkan kesalahan kalkulasi data. Untuk itu maka kita sanggup mengendalikan penguncian (lock) dan perlindungan sel.

Metode termudah dan paling lazim untuk mengamankan template spreadsheet dari user yang akan coba-coba dengan formula merupakan dengan cara memproteksi worksheet. Sayangnya , perlindungan sheet saja tidak cuma menangkal user mengerjakan penyesuaian kepada rumus , tetapi juga memberhentikan user dari memasukan apapun semuanya ke dalam worksheet. Terkadang anda tidak menginginkannya. Untuk itu ada 3 penyelesaian yaitu:

1. Mengunci Sel Berisi Formula Saja
2. Gunakan data validation
3. Auto Toggle perlindungan sheet dengan VBA

Dalam praktekanya dari ketiga cara di atas tidak ada satu pun yang sungguh-sungguh kebal kepada gangguan. Namun tidak ada salahnya bagi kita untuk mengerti dan memanfaatkannya.

Mengunci Sel Berisi Formula Saja

Secara garis besar , tahapannya selaku berikut:

1. Setting semua cell pada posisi unlocked
2. Setting cell locked pada range yang dikehendaki saja (sel berisi formula / rumus)
3. Setting perlindungan sheet.

Secara default , semua sel dalam worksheet ter-setting dalam posisi terkunci (Locked). Seting Locked tidak kokoh apa-apa kecuali perlindungan sheet sudah diaktifkan. Berikut merupakan cara gampang untuk menerapkan perlindungan worksheet sehingga cuma sel berisi formula saja yang dikunci dan di-protect:

  • Seleksi keseluruhan sel dalam worksheet (misalnya dengan cara tekan Ctrl + A) ,.
  • Kemudian klik kanan ➜ Format Cells ➜ masuk ke tab Protection
  • Hilangkan centang pada check box locked
  • Klik OK

Untuk mengunci sel yang berisi formula , ikuti langkah selanjutnya:

  • Excel 2017 atau yang lebih baru: Tab Home ➜ Find Select ➜ Go To Special.
  • Excel 2003 : santapan Edit ➜ Go To ➜ Special
  • Bisa juga dengan cara menekan shortcut (berlaku untuk semua Versi) : Tekan tombol short cut F5 atau Ctr + G
  • Maka akan timbul pembicaraan box Go To Special menyerupai terlihat dalam gambar di bawah ini.
Baca Juga  Belajar Microsoft Excel: Menjumlah Jumlah Sel Dengan Standar Warna

Menyeleksi Sel Berisi Rumus
  • Dalam pembicaraan box Go To Special , pilih pilihan Formulas , kalau diharapkan centang pilihan jenis formula di bawahnya (Numbers , Text , Logicals , Error , Jika anda ingin mengunci semua jenis formula. Biarkan seluruhnya tercentang). Lalu klik OK.

Setelah proses di atas , maka semua sel berisi formula akan terseleksi.

Klik kanan pada range terseleksi ➜ Format Cells ➜ masuk ke tab Protection – centang checkbox Locked. Ini akan memunculkan semua range yang berisi formula saja yang di lock.

Setting locked cell tersebut tidak akan kokoh apa-apa jika  perlindungan worksheet tidak diaktifkan.

Selanjutnya langkah perlindungan worksheet.

Excel 2007 ke atas: dari tab Review ➜ klik Protect Sheet ➜ sehingga timbul jendela protect sheet:

How To Protect Worksheet

Lakukan pengaturan umpamanya menghasilkan pasword , dan hal-hal yang diizinkan atau tidak untuk user. Lalu klik OK.

Metode di atas utamanya sanggup meminimalisir banyak waktu serta mengeliminasi kemungkinan error alasannya kesalahan edit formula oleh user. Sayangnya penerapan tata cara ini juga memunculkan user kehilangan peluang untuk mempergunakan fitur-fitur yang lain yang sungguh mempunyai fungsi , menyerupai sorting , formating dan sebagainya bahkan kepada sel yang tidak di-lock sekalipun.

Data Validation

Penggunaan data validation tidak tergantung pada perlindungan worksheet sama sekali , tetapi sanggup dengan gampang menangkal edit formula.

Sayangnya data validation masih jauh dari kata tahan banting untuk menangkal user memasukan data tidak tervalidasi ke sel. User memang tidak sanggup mengetik data tidak valid pada sel yang divalidasi. Namun user masih sanggup copy paste data tidak valid pada sel yang divalidasi. Bahkan validasi sel sanggup terhapus atau berubah sesuai sel yang dicopy paste.

Untuk mengerti apa yang dimaksudkan silahkan jalankan langkah – langkah berikut dimulai dengan memilih sel berisi formula yang mesti diamankan.

Baca Juga  Fungsi MySQL RANK

Seleksi satu sel sembarang , kemdian tekan F5 atau Ctr+G untuk masuk ke jendelal Go To ,

  • Klik Special
  • Tick Formulas
  • Klik OK

Setelah langkah tersebut diatas , maka semua sel berisi formula akan terseleksi.

Langkah berikutnya jalankan setting data validation

  • Excel 2003:  santapan Data ➜ Validation
  • Excel 2007 atau yang lebih baru: Masuk ke Tab Data ➜ Data validation 
  • Sehingga timbul jendela Data Validation
  • Masuk ke tab setting ,  pada kotak allow pilih custom dan pada kotak formula , masukan rumus =”” menyerupai sanggup dilihat dalam gambar di bawah ini:
Melindungi Formula dengan Data Validation
  • Kemudian klik OK

Hasil dari tata cara tersebut akan menangkal kesalahan pengetikan yang tidak sengaja pada cell tervalidasi. Sekali lagi , ini bukan tata cara yang sempurna. Tehnik ini cuma sanggup menangkal salah ketik yang tidak sengaja. Tetapi kalau anda mengerjakan copy paste dari sel lain ke sel tervalidasi tersebut , maka setting yang kita buat akan tertimpa oleh sel yang di copy paste. Keunggulan dari cara ini merupakan kita sanggup menjaga fungsi fitur fitur excel yang lain ketimbang cara protect sheet.

Auto – Toggle Worksheet Protection

Cara ke-3 ini menggunakan instruksi VBA untuk perlindungan worksheet secara otomatis saat user sel aktif berisi formula. Dengan tata cara ini , user masih sanggup menggunakan fitur-fitur excel saat sel aktive sedang berada di sel yang tidak di-lock.

Caranya ikuti langkah berikut:

  • Seleksi semua cell dengan menekan shortcut Ctr + A , lalu klik kanan ➜ format cells ➜ tab protection ➜ hilangkan centang Locked
  • Seleksi range formula yang akan di proteksi.  Jika posisi formula tersebar maka jalankan langkah berikut: Tekan Ctrl + G , atau tekan F5 untuk membuka jendela Go To , lalu klik Special ➜ centang Formula ➜ klik Ok
  • Kemudian tekan Ctrl + 1 atau Klik kanan ➜ format cells ➜ Protection ➜ centang Locked.  Hal ini akan menerapkan setting locked pada range sel yang dipilih (range sel berisi formula)
  • Selanjutnya Klik kanan tab sheet lalu klik view Code
How To Show VBA Code
  • Kemudian masukan instruksi berikut kedalam modul worksheet:
Baca Juga  8 cara optimasi judul artikel untuk SEO

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Locked = True Then
Me.Protect
Else
Me.Unprotect
End If
End Sub

Auto Toggle Sheet Protection With VBA

Jika diharapkan , anda juga sanggup menyertakan pasword pada instruksi VBA. Misalnya paswordnya =
“abc” (tanpa tanda kutip) , maka code VBA menjadi selaku berikut:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Locked = True Then
Me.Protect pasword:="abc"
Else
Me.Unprotect pasword:="abc"
End If
End Sub

Setelah code terpasang maka secara otomatis worksheet akan terproteksi saat sel aktive berada pada sel yang berisi formula (setting locked). Tentu saja dengan syarat setting macro enabled.
Jika menggunakan tenhik ke-3 ini , kalau anda menggunakan model excel 2007 atau yang lebih gres maka file mesti disimpan dengan extensi .xlsm atau .xlsb

Meskipun melakukan pekerjaan cukup baik , cara ini tidak lepas dari kelemahan. Target dalam instruksi , mereferensikan sel aktif. Jika kita memilih sejumlah range dan sel aktif nya merupakan sel yang tidak di lock maka kita sanggup meniadakan semua data dalam range yang dipilih walaupun dalam range tersebut ada sel yang di lock.

Sampai disini mudah-mudahan uraian perihal beberapa cara untuk perlindungan formula atau rumus ini sanggup gampang difahami. Mau pilih cara yang mana? Silahkan pilih mana saja sesuai preferensi anda. Semoga bermanfaat.

Dapatkan kiat excel yang sungguh berguna:

Referensi:
David Raina & Hawley 2007 , Excel Hack , Tips & Tools for Streamlining Your Spreadsheet , 2nd edition. O’Reilly Media , Inc

Login