Trik Kecil untuk Menyembunyikan Formula tanpa Memprotek Sheet

Pembaca yang budiman,
Pekerjaan membuat formula dalam Excel membutuhkan keseriusan dan ketelitian. Ini bukanlah pekerjaan mudah, sehingga terkadang kita tidak ingin orang lain "meniru" formula yang telah kita buat dengan susah payah.
Salah satu cara untuk menghindari "peniruan" formula adalah dengan memproteksi Sheet dan menjadikan sel yang berisi formula tersembunyi (tidak tampak di formula bar). 
Selain cara di atas, sebenarnya ada cara lain menyembunyikan rumus dengan cara sedikit "mengecoh" user (kalau terkecoh sih), sehingga formula yang tampak di formula bar hanya sebagian saja.
Perhatikan gambar berikut:

Setidaknya ada dua ketidakbiasaan dalam formula pada gambar di atas:

1. Formula bar yang hanya menampakkan formula : =0 untuk sel H7, padahal rumus yang sebanarnya adalah =0+(G7>=E$4)
2. Hasil yang tampak di kolom H adalah teks Tuntas dan Belum Tuntas, padahal dari formula yang digunakan tidak ada tulisan Tuntas dan Belum Tuntas.

Apa yang sebenarnya terjadi?

Formula trik

Posted by excell in Formula, trik

Trik Kecil untuk Menyembunyikan Formula tanpa Memprotek Sheet

Lagi Nyoba Nampilin Excel di Blog

Halaman Excel di sini bisa di-Edit lho...!!!!!!
Coba saja kalau mau
Sayang tidak ada formula bar-nya ya...

Others

Posted by excell in Others

Lagi Nyoba Nampilin Excel di Blog

Memanfaatkan ComboBox pada Form Control dan ActiveX Control

Excel memiliki fitur untuk menampilkan objek control untuk ditempatkan di worksheet. Keuntungan menggunakan Form Control ini adalah:

• Form control memberi gaya visual yang menarik dan jelas di worksheet
• Form Control bisa digunakan untuk memaksa user memilih satu di antara beberapa pilihan (pro-active data validation)

ActiveX ataukah Form Controls?
Excel memiliki 2 fitur terkait form Control ini, yakni ActiveX Controls dan Form Controls. ActiveX Control memiliki beberapa keunggulan, yakni kita bisa mengubah beberapa propertiesnya, seperti warna background, warna tulisan, dll. ActiveX Control juga punya beberapa bulit-in events yang langsung dihubungkan dengan control tersebut. Hanya saja, kita perlu pengetahuan tentang VBA untuk memaksimalkan fungsi dari ActiveX Controls.


Sementara Form Controls, pengoperasiannya lebih sederhana karena sudah dilengkapi dengan built-in metode yang bisa langsung menempatkan nilai dari control di worksheet.Sayangnya, kita tidak bisa mengubah tampilan dari objek, baik warna, besar tulisan, dll.
Bagi pemula, disarankan menggunakan objek dari Forms Controls agar pengoperasiannya lebih mudah.
Lalu di mana kita bisa mengoerasikan Form Control?

VBA

Posted by excell in VBA

Memanfaatkan ComboBox pada Form Control dan ActiveX Control

Membuat Form Login untuk Membatasi Akses Data

Form Login, biasanya digunakan untuk membatasi hak akses bagi user untuk melihat dan berinteraksi dengan data. Hanya user yang sudah terdaftar yang bisa mengakses data-data rahasia.
Berdasarkan pengalaman penulis di sekolah, Form Login ini biasanya digunakan dalam aplikasi Pendataan Penerimaan Siswa Baru, Pembayaran Siswa, dan Aplikasi Pengolah Nilai yang digunakan oleh beberapa orang yang berbeda. 


Catatan kali ini membahas cara membuat Form Login sederhana.

VBA

Posted by excell in VBA

Membuat Form Login untuk Membatasi Akses Data

Program Pembayaran SPP Sekolah

Kali ini ExcelHeru mencoba melansir aplikasi baru yakni Administrasi Pencatatan Pembayaran SPP sederhana. Aplikasi ini "dipaksa" menggunakan UserForm, karena penulis lagi belajar penggunaan UserForm. Jadi baik tampilan maupun kode-kode macro yang dipakai betul-betul masih sederhana, tidak canggih-canggih amat. Maklumlah programmer-nya masih amatir.
Berikut tampilan utama program:

Berikut tampilan salah satu fitur:

Program ini, bersifat GRATIS. Boleh di-edit oleh siapa saja asalkan bertujuan baik. Boleh didistribusikan, Boleh ditambah, dikurangi. Dihapus juga tidak apa-apa. 
Sheet yang diprotect tidak diberi password, jadi kalau mau di-edit tinggal diUnprotect saja.
VBA-nya diprotect dengan password 123456
Proteksi pada VBA ini boleh dihapus, boleh diubah. Proteksi ini sengaja dibuat agar user yang tidak berkepentingan tidak mengakses kodenya. Kalau memang berkepentingan ya silahkan saja.


Berhubung program ini hasil coba-coba, maka jangan langsung digunakan, tapi dicoba terlebih dahulu. Bila ada error atau kekurangan harap segera melaporkan, baik melalui komentar di posting ini atau melalui email. Karena terus terang, saya masih sangat minim pengetahuan tentang bahasa pemerograman, dan tidak punya staff khusus untuk meng-ujicoba program.


Oke, file bisa di-Download di sini, atau di BoxNet pada Sidebar.

Aplikasi VBA

Posted by excell in Aplikasi, VBA

Program Pembayaran SPP Sekolah

Langkah membuat Mega Formula Terbilang untuk Raport atau SKHU Tanpa Macro

Setelah agak lama tidak posting, karena harus istirahat total, kali ini ExcelHeru akan membahas tentang cara menerjemahkan angka ke huruf yang biasa dipakai di penulisan Raport dan SKHU.
Kalau kita cari di Google dengan mengetikkan kata kunci "Terbilang", maka akan banyak didapatkan rumus Terbilang, tapi yang pakai Macro. Nah, di tulisan ini akan dibeber Fungsi Terbilang yang tanpa Macro.


Rumus yang dibahas di sini bersumber Maha Guru Excel Indonesia, Mbak Siti-Vi dan Mbak Hapsari di Milis Belajar-Excel#14674. Sehingga rumus ini bukan buatan saya, saya hanya tinggal menggunakan saja. Hehehehe.


Misalnya angka ada di sel C5, maka rumusnya adalah:
=IF(--INT(C5)=0,"Nol ",IF(--(LEFT(TEXT(INT(C5),"000")))=1,"Seratus ",IF(--(LEFT(TEXT(INT(C5),"000")))>1,INDEX(N,--(LEFT(TEXT(INT(C5),"000"))))&"Ratus ","")))&IF(--(MID(TEXT(INT(C5),"000"),2,1))=0,"",IF(--(MID(TEXT(INT(C5),"000"),2,1))=1,CHOOSE((--(RIGHT(TEXT(INT(C5),"0")))=0)*1+((--(RIGHT(TEXT(INT(C5),"0")))=1)*2)+((--(RIGHT(TEXT(INT(C5),"0")))>1)*3),"Sepuluh ","Sebelas ",INDEX(N,--(RIGHT(TEXT(INT(C5),"0"))))&"Belas "),IF(--(MID(TEXT(INT(C5),"000"),2,1))>1,INDEX(N,--(MID(TEXT(INT(C5),"000"),2,1)))&"Puluh ")))&IF(OR(--(MID(TEXT(INT(C5),"000"),2,1))=1,--(RIGHT(TEXT(INT(C5),"0")))=0),"",INDEX(N,--(RIGHT(TEXT(INT(C5),"0")))))&"Koma "&IF(--LEFT(TEXT(MOD(C5,1)*100,"00"))=0,"Nol ",INDEX(N,LEFT(TEXT(MOD(C5,1)*100,"00"))))&IF(--RIGHT(TEXT(MOD(C5,1)*100,"00"))=0,"Nol ",INDEX(N,RIGHT(TEXT(MOD(C5,1)*100,"00"))))


ckckckckckckcck.....! ehm rumus yang sangat sangat panjang (Mega Formula). Sulit lho memahami logika rumus panjang seperti di atas. Lebih sulit lagi cara menjelaskannya. Dan tentu yang paling sulit cara membuatnya untuk pertama kali. (OKI, kita harus berterima kasih kepada kedua Maha Guru Excel Indonesia yang telah memberikan kita, Para Guru, rumus yang hanya tinggal pakai saja).


Sebagai sebuah MegaFomula, maka memahami alur logika rumus mempunyai kesulitan tersendiri. Untuk itu, kita perlu memecah rumus tersebut menjadi beberapa bagian. Saya memecah menjadi 15 bagian. Lalu ke-15 bagian tersebut di-Kompress menjadi 4 bagian saja:
1. Bilangan Ratusan
2. Bilangan Puluhan dan Satuan
3. Koma
4. Bilangan Desimal.
Barulah dari 4 bagian tadi barulah digabung jadi satu rumus tunggal.
Berikut Pemecahan rumus di atas:

dikompress jadi 4 langkah saja:

Maka jadilahMega Formula seperti di atas.


Info Tambahan:
Rumus Index(N,.....). N yang dimaksud di sini adalah sebuah Name_Range yang berisi: 

={"Satu ","Dua ","Tiga ","Empat ","Lima ","Enam ","Tujuh ","Delapan ","Sembilan "}

File penjelasan langkah-langkah di atas, bisa didownload di sini atau di Boxnet pada SideBar.

Formula

Posted by excell in Formula

Langkah membuat Mega Formula Terbilang untuk Raport atau SKHU Tanpa Macro

Boolean Logic dan Rumus Kelulusan Ujian Nasional

Daftar Isi

• Pengantar
• Apa Itu Boolean Logic?
• Penerapan Boolean di Excel
• Rumus Kelulusan Ujian Nasional
• Penjelasan Detail Rumus
• Kesimpulan

Pengantar

Boolean Logic? Ehm, minuman apa lagi ini? Jangan salah, ini bukan istilah baru dari dunia kuliner. Boolean Logic justru sangat penting, bahkan bisa menentukan masa depan siswa melalui rumus kelulusan Ujian Nasional (UN). Dalam artikel ini, kita akan membahas bagaimana logika matematika diterapkan dalam Excel untuk menentukan kelulusan.

Apa Itu Boolean Logic?

Dalam pelajaran matematika, kita mengenal konsep logika yang terdiri dari dua nilai: TRUE (Benar) dan FALSE (Salah).

Nilai-nilai ini dikenal dalam dunia komputer sebagai Boolean. Di Excel, data bertipe TRUE dan FALSE disebut data boolean. Boolean Logic adalah dasar dari semua pengujian logis dalam Excel. Nilai ini bisa diolah layaknya angka:

• TRUE = 1
• FALSE = 0
• OR = Penambahan (+)
• AND = Perkalian (*)

Penerapan Boolean di Excel

Agar data boolean bisa digunakan dalam perhitungan, kita harus mengkonversinya menjadi nilai numerik. Ada dua cara utama:

1. Mengalikan dengan angka 1 (misal: =TRUE*1)
2. Memberikan tanda minus dua kali (double minus): =--TRUE

Contoh penggunaan double minus sangat berguna ketika kita mengolah data array, seperti yang akan kita gunakan nanti dalam rumus kelulusan.

Rumus Kelulusan Ujian Nasional

Kelulusan siswa dalam Ujian Nasional didasarkan pada dua kriteria:

• Rata-rata nilai semua mata pelajaran minimal 5.50
• Tidak ada satupun nilai mata pelajaran yang kurang dari 4.00

Maka kita perlu rumus yang bisa mengevaluasi kedua syarat tersebut sekaligus. Inilah rumusnya:

=IF((AVERAGE(D51:N51)>=5.5)*(SUM(--(D51:N51>=4))=11),"Lulus","Tidak Lulus")

Catatan: Ini adalah rumus array, tekan Ctrl+Shift+Enter untuk mengaktifkannya.

Penjelasan Detail Rumus

Rumus ini terdiri dari dua bagian utama:

1. AVERAGE(D51:N51)>=5.5: Menghitung rata-rata dan mengecek apakah >= 5.5
2. SUM(--(D51:N51>=4))=11: Menghitung apakah semua nilai di atas 4

Kenapa harus pakai SUM(--(D51:N51>=4))? Karena ini menghitung jumlah nilai yang memenuhi syarat (>= 4.00). Dengan bantuan double minus, kita ubah data boolean menjadi angka:

{TRUE,FALSE,TRUE,...} → {1,0,1,...}

Lalu kita jumlahkan. Jika hasilnya 11 (jumlah mata pelajaran), berarti semua nilai di atas 4. Jika ada satu saja di bawah 4, maka hasilnya kurang dari 11.

Bila kita tidak menggunakan array formula, maka rumus menjadi panjang seperti ini:

=IF(AND(AVERAGE(D51:N51)>=5.5,D51>=4,E51>=4,...,N51>=4),"Lulus","Tidak Lulus")

Sangat tidak efisien dan rentan kesalahan jika jumlah mata pelajaran banyak.

Kesimpulan

Boolean Logic sangat berguna dalam membuat rumus efisien di Excel, termasuk untuk menentukan kelulusan siswa. Dengan memahami TRUE/FALSE sebagai angka, dan menguasai teknik seperti double minus serta array formula, kita bisa membuat rumus kompleks menjadi jauh lebih sederhana dan dinamis.

Selamat mencoba dan semoga bermanfaat untuk guru, operator sekolah, dan siapa pun yang bergelut dengan data nilai di Excel! File excel bisa didownload di sini

Array Formula

Posted by excell in Array, Formula

Boolean Logic dan Rumus Kelulusan Ujian Nasional