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

Sulap Excel : Bermain-main dengan Worksheet_Events

SULAP? Ya... mengapa tidak.


Performance: 
1. Insert sebuah atau beberapa gambar ke dalam Sheet.
2. Tuliskan kata "Hilang" di sel mana saja, lalu tekan Enter. maka Simsalabim.... Gambarnya akan hilang.
3. Tuliskan kata "Muncul" di sel mana saja, lalu tekan Enter. Maka Simsalabim.... Gambarnya muncul lagi.


The Secret:
1. Klik kanan nama Sheet, pilih View Code
2. Tuliskan Kode berikut:

VBA

Posted by excell in VBA

Sulap Excel : Bermain-main dengan Worksheet_Events

Teknik Menyembunyikan Banyak Sheet Sekaligus dan Menampilkannya

Setelah Contoh Aplikasi Raport yang Sudah Jadi dirilis, saya menerima banyak email dari pembaca yang bertanya bagaimana caranya agar hanya satu sheet yang tampil di layar, seperti yang terlihat pada tampilan file raport tersebut. Untuk mempermudah penjelasannya, posting kali ini akan membahas cara mengatur tampilan sheet di Excel menggunakan VBA.

📚 Baca Juga:

• Tutorial Membuat Arsip Online Surat dengan Token
• Bikin Live Score Pakai Google Sheets API & Google Sites
• Excel Custom Number Format: Biar Tampilan Angka Makin Estetik dan Fungsional!

Jenis "Penampakan" (Visibility) Sheet dalam VBA

Dalam VBA, "penampakan" (visibility) suatu sheet memiliki tiga jenis:

1. xlSheetVisible
   Sheet tampil seperti biasa. Kode VBA: -1
2. xlSheetHidden
   Sheet disembunyikan biasa. Bisa dimunculkan kembali melalui klik kanan > Unhide. Kode VBA: 0
3. xlSheetVeryHidden
   Sheet disembunyikan khusus dan tidak bisa dimunculkan lewat Unhide. Harus melalui VBA Editor. Kode VBA: 2

Contoh Kode VBA untuk Mengatur Penampakan Sheet

Menyembunyikan Sheet1 dengan metode Very Hidden:

Sheets("Sheet1").Visible = 2
' atau:
Sheets("Sheet1").Visible = xlSheetVeryHidden

Menampilkannya kembali:

Sheets("Sheet1").Visible = -1
' atau:
Sheets("Sheet1").Visible = xlSheetVisible

Bagaimana Cara Menyembunyikan Banyak Sheet Sekaligus?

Untuk menyembunyikan beberapa sheet dalam satu klik saja, ikuti langkah-langkah berikut:

1. Tampilkan terlebih dahulu sheet yang akan ditampilkan.
2. Semua sheet selain yang ditampilkan disembunyikan menggunakan prosedur looping.

Misalnya, hanya ingin menampilkan "Sheet2" dari total 20 sheet:

Sub TampilkanSheet2()
    Dim sh As Worksheet
    Sheets("Sheet2").Visible = -1
    For Each sh In ThisWorkbook.Worksheets
        If sh.Name <> "Sheet2" Then
            sh.Visible = xlSheetVeryHidden
        End If
    Next sh
End Sub

Bagaimana Jika Nama Sheet yang Ditampilkan Berubah-ubah?

Buat prosedur VBA yang menerima nama sheet sebagai parameter:

Private Sub SembunyikanSemuaSheetKecualiAku(NamaSheet As String)
    Dim sh As Worksheet
    Sheets(NamaSheet).Visible = -1
    For Each sh In ThisWorkbook.Worksheets
        If sh.Name <> NamaSheet Then
            sh.Visible = xlSheetVeryHidden
        End If
    Next sh
End Sub

Untuk menampilkan hanya Sheet2:

Call SembunyikanSemuaSheetKecualiAku("Sheet2")

Bagaimana Cara Menampilkan Semua Sheet Sekaligus?

Gunakan makro berikut:

Sub TampilkanSemuaSheet()
    Dim sh As Worksheet
    For Each sh In ThisWorkbook.Worksheets
        sh.Visible = xlSheetVisible
    Next sh
End Sub

Kode ini akan menampilkan semua sheet, termasuk yang disembunyikan dengan metode Very Hidden.

Download Contoh File

File contoh bisa diunduh di sini

VBA

Posted by excell in VBA

Teknik Menyembunyikan Banyak Sheet Sekaligus dan Menampilkannya

Cara Mencegah Save As pada Ms Excel

Terkadang, kita tidak ingin user memanfaatkan penggunaan Save As pada workbook yang kita buat. Hal ini dilakukan jika workbook digunakan oleh beberapa orang, dan kita tidak ingin terjadi duplikasi file yang isinya sama, namun nama filenya berbeda.
Untuk mencegah user menggunakan fasilitas Save As pada suatu workbook, maka langkahnya adalah sbb:
1. Buka workbook 
2. Tekan Alt + F11
3. Klik kanan objek ThisWorkbook, pilih View Code
4. Tuliskan code berikut:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim lReply As Long




  If SaveAsUI = True Then




lReply = MsgBox("Maaf, Anda tidak diperkenankan menyimpan workbook ini dengan nama lain. Anda ingin menyimpan workbook ini?", vbQuestion + vbOKCancel)


     Cancel = (lReply = vbCancel)


   If Cancel = False Then Me.Save


     Cancel = True


  End If


End Sub

5. Tekan Alt + Q untuk kembali ke Excel

6. Selesai

Selanjutnya, jika user mencoba mengakses Save As, maka akan muncul peringatan seperti ini:

File contoh bisa didownload di sini atau pada boxnet di samping

VBA

Posted by excell in VBA

Cara Mencegah Save As pada Ms Excel

Tips Merapikan Kode Makro Hasil Recording (Rekaman)

Record Macro adalah salah satu fitur yang sangat berguna bagi pemakain Excel baik user pemula maupun user yang sudah berpengalaman. Namun, kode macro yang dihasilkan dari proses perekaman Makro tersebut kadang butuh sentuhan lanjutan agar lebih rapi dan berjalan lebih cepat.
Beberapa tips berikut bisa dijadikan pedoman.

trik VBA

Posted by excell in trik, VBA

Tips Merapikan Kode Makro Hasil Recording (Rekaman)

Cara Memodifikasi Menu Ribbon Ms Excel 2007 / 2010

Kalau kita lihat gambar di atas, kita mendapati bahwa menu ribbon yang biasa kita temui seperti Home, Insert, PageLayout, Formula, dll menjadi hilang bak ditelan bulan. Pada kemana menu-menu tersebut?

Pertanyaan selanjutnya, bisakah menu Ribbon dimodifikasi (diubah, ditambah atau dikurangi?). Jawabnya BISA. lalu bagaimana caranya?

Perlu dipahami, bahwa ada dua jenis modifikasi menu Ribbon.

1. Hasil modifikasi hanya berlaku untuk satu komputer saja, artinya jika dibuka di komputer lain, hasil modifikasi tidak berpengaruh, akan kembali normal seperti biasa.
2. Hasil modifikasi hanya berlaku untuk satu file yang dimodifikasi saja, namun berlaku untuk semua komputer. Artinya jika file tersebut dibuka di komputer lain, maka modifikasi yang dibuat masih tetap berlaku. 

Untuk yang pertama, bisa dilakukan dengan klik kanan di ruang kosong pada tollbar, pilih Customize Ribbon. Pada bagian Main Tabs, pilih New Tab. Lakukan modifikasi sesuai kebutuhan.

Namun, perlu dipahami tindakan ini berpengaruh untuk semua file Excel di komputer. Artinya, ketika kita membuka Ms Excel, maka menu-menu tambahan akan muncul.


Cara yang kedua, adalah dengan memodifikasi satu file saja sehingga walauoun dibuka di komputer lain, kostumasi yang dibuat tetap ada. Untuk cara yang kedua ada beberapa hal yang perlu dipahami.


Memahami lebih jauh tentang format XLSX.
Berbeda dengan format XLS (office 2003 dan sebelumnya), Excel 2007 hadir dengan eksistensi XLSX. Dengan format ini satu file Excel sebenarnya merupakan sebuah file kompresi dari beberapa file. Buktinya, file Excel dengan eksistensi XLSX bisa dibuka dengan aplikasi seperti WINRAR seperti berikut:

Untuk memodifikasi menu Ribbon, kita harus menambah sebuah folder baru bernama CustomUI seperti tampilan berikut:

Ada sebuah aplikasi gratis yang bisa membantu kita memodifikasi menu Ribbon melalui pembuatan folder CustomUI tersebut, namanya adalah OfficeCustomUIEditor. Dengan bantuan program ini kita bisa mensetting sendiri menu-menu yang akan ditampilkan di UserInterface satu file Ms Excel.


Karena untuk menuliskan kode-kode pada aplikasi tersebut ada aturan khusus, link berikut bisa dijadikan rujukan beserta contoh penerapannya:
1. http://www.rondebruin.nl/ribbon.htm
2. http://msdn.microsoft.com/en-us/library/aa338202.aspx
3. http://www.oaltd.co.uk/Excel2007ProgRef/Default.htm


Demikian, semoga bermanfaat

Ribbon

Posted by excell in Ribbon

Cara Memodifikasi Menu Ribbon Ms Excel 2007 / 2010