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

Ebook : Microsoft Excel Programming

The Power of Excel

VBA (Visual Basic for Application) bila dikombinasikan dengan Microsoft Excel bisa jadi merupakan sebuah piranti yang sangat canggih yang dapat membuat program yang kita buat tampil lebih elegan. Lebih dari 500 juta komputer di dunia telah terinstal Microsoft Excel lengkap dengan VBA-nya, namun hanya sebgian kecil saja yang mampu memanfaatkan kekuatan super dahsyat VBA ini. Jika kita sering menggunakan Excel dalam mengerjakan tugas, maka sangat bijaksana jika kita mengenal dan memahami VBA lebih dalam. Dengan VBA kita bisa mempercepat penyelesaian tugas-tugas secara signifikan, karena segala sesuatu yang bisa kita lakukan di interface Microsoft Excel bisa kita otomatisasikan dengan VBA hanya dengan sekali klik. Jika kita terbiasa membuat laporan harian, mingguan, bulanan, membuat grafik, mencetak dan lain-lain, saatnya kita “menyuruh” VBA melakukan pekerjaan-pekerjaan tersebut.

Ebook “Microsoft Excel Programming" adalah sebuah buku panduan yang tepat bagi kita yang mau belajar VBA dari nol. Ebook ini mengulas tuntas tentang penggunaan VBA (Visual Basic for Application) dalam Microsoft Excel dikemas secara apik dengan bahasa yang mudah dipahami disertai ilustrasi gambar dan contoh contoh mulai dari sederhana sampai yang agak rumit. Beberapa poin yang dibahas adalah pengenalan Macro dan Form Control, pengenalan konsep Excel Object Model, penanganan terhadap worksheet, workbook, file, sel, range, formula, worksheet function, dialog box, form, Chart, Pivot Table, serta cara membuat Add-Ins.

Memang, buku ini berbahasa Inggris. Mendapatkan buku VBA dalam bahasa Indonesia dengan ulasan yang aka lengkap masih agak sulit. Lagi pula, dengan kemajuan teknologi hari ini, aplikasi penterjemah bahasa sudah banyak beredar secara gratis. Selain itu, kita yang di Indonesia sudah sangat pintar Bahasa Inggris khan?. Buktinya kalau di Ujian Nasional SMP atau SMA nilai bahasa Inggris sangat tinggi. Juga, dengan membaca buku berbahasa Inggris akan membantu kita menambah wawasan tentang pemahaman bahasa Inggris secara tidak langsung.

Nah, bagi yang berminat mendapatkan ebook ini, bisa langsung membeli di ebookstore.sony.com

Ssssssssssst. Alternatif lain untuk mendapatkan buku ini, serta Excel for Teacher, silahkan request via email ke herumamuda@gmail.com. 

Cara Mencegah Input Data Kembar

Dalam membuat daftar, ada beberapa tipe data yang mengharuskan tidak ada data yang sama dalam satu kolom. Misalnya nomor Induk atau kode guru dalam pembuatan jadwal pelajaran.

Di Excel sedikitnya ada dua cara untuk mencegah user menginput data lebih dari satu kali.  Cara pertama menggunakan Conditional Formatting dan kedua menggunakan Data Validation.

Cara 1 – Conditional Formatting

Sebenarnya cara ini tidak secara langsung mencegah input data kembar, hanya memberi tanda atau peringatan bahwa telah ada data yang sama yang diinput sebelumnya.

Misal kita punya data sbb:

Pada kolom NO INDUK kita ingin agar tidak ada data kembar, maka caranya:

1. Blok B4:B15
2. Pilih menu Home > Conditional Formatting > New Rule > Format only unique or duplicate values
3. Klik tombol “Format”, Pada tab Fill , pilih warna Merah. Klik OK. OK
4.  Sekarang tampilannya sbb:

Dengan demikian, kita bisa tahu bahwa ada data kembar dalam kolom NO INDUK.

Cara ke-2 DATA VALIDATION

Untuk  cara yang kedua menggunakan fasilitas DATA VALIDATION. Prinsip kerjanya berbeda dengan cara pertama, yakni apabila ada data kembar, maka muncul kotak dialog yang peringatannya bisa disetting sendiri oleh kita. Caranya adalah:

1. Blok sel B4:B25
2. Pilih menu Data > Data Validation > Data Validation
3. Pada kotak dialog, di tab Setting, bagian Allow, pilih Custom.
4. Di bagian Formula, ketikkan rumus : =COUNTIF($B$4:$B$15,B4)=1
5. Pada tab Error Alert, bagian Error Message tuliskan “Nomor yang dimasukkan sudah ada”
6. Klik OK

Dengan cara di atas, jika kita memasukkan data yang sama, maka akan keluar “hantu” seperti berikut:

 Demikian, semoga bermanfaat…

Cara Membuat Link ke Setiap Sheet di Workbook dengan Klik Kanan

Fasilitas Hyperlink di Ms Excel memang sangat handal untuk navigasi antar sheet. Namun, jika sheet yang kita miliki banyak, maka pekerjaan membuat hyperlink ke semua sheet menjadi agak panjang juga. Apalagi jika nama sheet diubah, maka tambah lagi pekerjaan kita.
Nah, pada tulisan kali ini, ExcelHeru akan memberikan tip untuk membuat menu klik kanan yang berfungsi sebagai link ke sheet yang lain.
Jadi, di sheet manapun dan di sel manapun kita klik kanan, akan ada tambahan menu berjudul "Daftar Isi" di paling bawah

Kalau tulisan "Daftar Isi" di klik, akan muncul daftar sheet yang bisa diklik untuk menuju sheet yang dituju.

Gimana cara membuatnya?

1. Tekan Alt + F11 untuk menuju VBA Editor
2. Pada bagian panel kiri, Double klik ThisWorkbook

3. Pada bagian kanan, di tempat yang kosong,kopi pastekan kode berikut:



Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Dim cCont As CommandBarButton
    On Error Resume Next
    Application.CommandBars("Cell").Controls("Daftar Isi").Delete
    On Error GoTo 0
        Set cCont = Application.CommandBars("Cell").Controls.Add(Type:=msoControlButton, Temporary:=True)
        With cCont
            .Caption = "Daftar Isi"
            .OnAction = "IndexCode"
        End With
End Sub

4. Masih diVBA Editor, Pilih menu Insert > Module. Dan tuliskan kode berikut:

Sub IndexCode()

 Application.CommandBars("workbook Tabs").ShowPopup

End Sub

5. Tekan Alt+Q untuk kembali ke Excel

Sekarang silahkan coba klik kanan di sembarang sel pada sembarang sheet. 

File contoh bisa didownload di sini atau di boxnet di samping di folder Desember


Referensi:
Excel Hacks by David & Raina Hawley, 2004

Buku Excel : Excel for Teacher by Colleen Conmy, dkk

Sebuah buku yang sangat berharga didedikasikan oleh guru untuk guru (by Teacher for Teacher). Tanpa perlu banyak basa-basi, sebagaimana terlihat dalam gambar, beberapa hal berkaitan dengan tugas guru dikupas tuntas oleh buku ini. Bagaimana seorang guru profesional memanfaatkan teknologi wabil khusus Microsoft Excel untuk membantu tugas sebagai pendidik dijelentrehkan dengan baik disertai contoh file dalam bentuk workbook. 

Jadi buku ini tidak hanya berisi teori dan contoh dalam bentuk tulisan, namun juga disertai contoh penerapannya dalam Microsoft Excel.

So, bagi para guru dan dosen yang ingin maju dan lebih profesional, buku ini tentunya bisa menjadi referensi pokok. 

Namun, buku Excel for Teacher ini tidaklah gratis. Guru yang berminat bisa langsung membeli di Amazon .

Konversi Angka ROMAWI ke Angka ARAB dan Sebaliknya

Microsoft Excel sudah menyediakan fungsi khusus untuk mengkonversi angka biasa ke angka Romawi. Fungsi tersebut adalah : ROMAN. Jadi untuk mengubah angka 41 ke angka Romawi, cukup kita tuliskan rumus : =ROMAN(41). Sangat sangat mudah....!


Namun, untuk mengubah angka Romawi ke angka biasa, Excel belum mempunyai fungsi khusus. (Kita lapor ke om Bill Gates yuk, biar dibikinin). Sehingga kita sendiri yang harus membuat formulanya.


Dari berbagai referensi yang penulis miliki, salah satu rumus yang paling mudah adalah rumus yang dibahas di Milis Belajar Excel. Berikut rumusnya (Array Formula, yakni setelah selesai selesai ditulis, diikuti dengan upacara menekan 3 tombol sekaligus: Ctrl + Shift + Enter atau CSE):

=MATCH(A1, ROMAN(ROW($1:$3999)), 0) 

dengan asumsi bahwa angka Romawi ditulis di sel A1. 


Rumus di atas bekerja dengan cara membuat daftar  hasil konversi angka 1 sampai 3999 menjadi angka Romawi, kemudian me-LookUp angka Romawi yang ingin dicari dengan daftar tersebut dengan fungsi MATCH.


Jelasnya:
=ROW($1:$3999) : menghasilkan daftar angka biasa mulai 1 sampai 3999. Kok 3999? karena Excel memang membatasi angka Romawi hanya sampai 3999.
=ROMAN(ROW($1:$3999)) : Mengubah daftar angka biasa tadi menjadi angka Romawi
=MATCH(A1, ROMAN(ROW($1:$3999)), 0) : Mencari Posisi Angka Romawi yang ingin dicari dalam Daftar angka Romawi.


Berikut contoh dalam Sheet:

Demikian, semoga bermanfaat...!

Fungsi OFFSET sebagai Alternatif Pengganti VLOOKUP

Fungsi VLOOKUP adalah salah satu fungsi yang sangat powerfull di Excel, dan diajarkan serta ditulis dalam buku-buku Excel standar serta sering digunakan oleh para pengguna Excel dalam me-LookUp data.
Namun, sebenarnya Vlookup bukan satu-satunya fungsi untuk tujuan me-LookUp data. Karena sebenarnya masih banyak fungsi lain yang bisa kita gunakan sebagai Variasi dari VLOOKUP dan saudaranya: HLOOKUP.
Kali ini pembahasan kita adalah tentang fungsi OFFSET.
Menurut Help-nya, Excel mendefinisikan fungsi OFFSET sebagai suatu fungsi untuk : Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. (Mengembalikan referensi ke range yang ditentukan dengan jumlah tertentu dari baris dan kolom dari sel atau range sel). 
Bahasa sederhananya, Mengubah rujukan sel sesuai dengan jumlah baris dan kolom yang ditentukan.


Syntaxnya: 
=OFFSET(reference, rows, cols, [height], [width])
reference: alamat sel sebagai pedoman arah perubahan atau pergeseran.
rows: Pindahnya berapa baris?
cols : Pindahnya berapa kolom?

height: Tingginya berapa baris (bersifat optional, artinya boleh ditulis, boleh dikosongkan)

width: Lebarnya berapa kolom (bersifat optional, artinya boleh ditulis, boleh dikosongkan)

Contoh Penggunaan

Jika hanya melihat penjelasannya, memang agak sulit dipahami, sehingga kita akan langsung melihat contohnya di TKP. 

Pada gambar di atas, kita punya tabel Data di sel B3 sampai F7. Kemudian kita punya isian Nama, Alamat, dan Nomor Telepon di sel E11 sampai E13. Serta Nomor urut di sel E9. Nah tugas kita adalah mengisi isian Nama di sel E11 sesuai dengan nomor urut di sel E9.

Rumus yang digunakan menggunakan fungsi OFFSET seperti berikut:

=OFFSET(C2,E9,0)

Bahasa manusianya: Ubah atau geser data di sel C2, sebanyak nilai E9 (4) baris, dan 0 kolom (tidak bergeser kolomnya).

C2 : pedoman atau TITIK PIJAK awal pergeseran sel.

E9 : bergeser sebanyak 4 Baris

0   : bergeser sebanyak 0 kolom alias tetap.

Maka hasilnya : adalah Nama di nomor 4 yakni, Pangeran Mangkubumi.

Kalau nilai di sel E9 kita ubah menjadi 3, maka hasilnya pun akan berubah menjadi Nama di nomor urut 3.

Keterangan tentang Rows dan Cols

Jika nilai dari row POSITIF, maka pergeserannya ke bawah dari sel TITIK PIJAK. Jika negatif, maka pergeserannya ke ATAS dari TITIK PIJAK.

Jika nilai dari Col POSITIF, maka pergeserannya ke KANAN dari sel TITIK PIJAK-nya. Jika NEGATIF, maka bergeser ke KIRI.

Bagaimana dengan isian Alamat dan Nomor Telepon? 

Ayo silahkan dicoba sendiri........!

Bandingkan dengan:

Alamat : =OFFSET(C2,E9,2) atau =OFFSET(E3,E9,0) atau =OFFSET(F3,E9,-1) atau =OFFSET(D2,E9,1)

No Telp: =OFFSET(C2,E9,3) atau =OFFSET(E3,E9,1) atau =OFFSET(F3,E9,0) atau =OFFSET(D2,E9,2) 

Keuntungan menggunakan fungsi OFFSET dibandingkan dengan VLOOKUP adalah semakin kecilnya kemungkinan hasil #NA seperti yang sering terjadi pada fungsi VLOOKUP jika data yang rujuk keluar dari batas range yang ditentukan.

Untuk penggunaan lebih lanjut, Fungsi OFFSET bisa digandengkan dengan fungsi-fungsi lain seperti SUM, AVERAGE, MATCH, dan lain-lain sesuai keperluan. Contoh penggunaan OFFSET lebih lanjut bisa dilihat dalam membuat Defined Name Dinamis pada posting sebelumnya.

Demikian, semoga bermanfaat.

Cara Mencari KPK dan FPB dengan Excel

Masih ingat pelajaran Matematika ketika SD dulu? Salah satu materinya adalah mencari KPK (Kelipatan Persekutuan Terkecil) dan FPB (Faktor Persekutuan Terbesar). Jika adik atau anak kita meminta bantuan kita untuk menyelesaikan soal berikut, kita tinggal buka Excel, dan jreng...... selesai.


A. Carilah KPK dari 46 dan 48 dan 88 !
B. Carilah FPB dari 24, 16, 128 !


Cara menyelesaikannya pake Excel.
Untuk mencari KPK, fungsi yang digunakan adalah LCM (Least Common Multiple of Integer). Jadi untuk soal A rumusnya adalah =LCM(46,48,88)


Untuk mencari FPB, fungsi yang digunakan adalah GCD (Greatest Common Divisor). Jadi rumus untuk soal B adalah: =GCD(24,16,128)


Selesai,.....
Begitu mudah....!

Menuliskan Urutan Abjad Secara Otomatis

Untuk membuat angka urut mulai 1 sampe n, excel sudah menyediakan fasilitasnya. Misal kita tulis 1 di A1 dan 2 di A2, maka kalau A1 dan A2 di blok, lalu diseret ke bawah, akan memunculkan angka urut.
Bagaimana dengan Abjad? Adakah fasilitasnya? 
Ada, tapi kita sendiri yang harus membuatnya, dengan memanfaatkan fungsi CHAR. 
Fungsi CHAR digunakan untuk menampilkan karakter dari sebuah kode. Contoh: =CHAR(66) akan menghasilkan huruf B.
Selain itu, kita menggunakan fungsi ROW. Contoh =ROW(A1)  akan menghasilkan 1 (nomer baris dari sel A1. Sementara =ROW() akan menghasilkan nomor baris dari sel yang sedang aktif,


Sehingga untuk meghasilkan huruf A, di sel A1 kita tuliskan rumus: = CHAR(64+ROW())
Untuk menghasilkan huruf A di sel B10: = CHAR(55+ROW())


Kalau mau menuliskan huruf A sampai Z, di sel A1 sampai A26, kita tulis rumus di A1: = CHAR(64+ROW())


Untuk menuliskan huruf a sampai z, di sel B1 sampai B26, kita tulis : =CHAR(96+ROW())

Menjumlah Sel yang Tampak Saja (Tidak Disembuyikan)

Ditranslasikan dengan beberapa modifikasi dari buku Excel Gurus Gone Wild: Do The Impossible with Microsoft Excel karya Bill Jellen terbitan Holy Macro! Books 2009 oleh ExcelHeru

Demikian, semoga bermanfaat