Fungsi INDIRECT dan Penerapannya pada Pembuatan Kartu Ujian

Pada posting sebelumnya, telah dibahas cara membuat Kartu Peserta Ujian. Di posting tersebut digunakan fungsi Indirect untuk menghasilkan data pada kartu peserta. Nah, kali ini blog ExcelHeru akan membahas tentang Fungsi INDIRECT secara lebih mendalam, sehingga bisa digunakan sebagai 'senjata' untuk menghadapi 'musuh' dalam dunia per-excel-an nantinya.

         




Pembahasan lengkap beserta contohnya bisa didownload di sini atau di Boxnet pada Sidebar di samping kanan tulisan ini

Kamus Function Excel (Lebih dari 150 Rumus)

Kali ini, saya sharing tentang sebuah file berisi Kamus fungsi atau rumus-rumus Microsoft Excel yanng cukup lengkap. Namun fungsi-fungsi baru Excel 2007 belum ada di situ. Kamus ini bukan buatan saya lho. Semua fungsi diberi penjelasan dan contohnya sekaligus.


Kepengin? Download aja langsung dari sumbernya di sini. Gratis? Pastinya... Hanya saja pake Bahasa Inggris. 


Setelah didownload, nama file Excel-nya jangan diubah, karena akan mengakibatkan tidak berfungsinya hyperlink yang ada di file tersebut.

Cara Membuat Password Sheet yang Bisa Berubah Sendiri

Salah satu cara melindungi data di worksheet adalah dengan cara memproteksi sheet dan memberi password. Tulisan kali ini tentang cara membuat password yang bisa berubah sendiri. 

Secara sederhana, password yang akan kita buat berdasarkan nama hari saat kita membuka file. Cara seperti ini saya terapkan di File Program Raport Jadi pada posting saya sebelumnya. Langkah-langkahnya adalah:

• Pertama, Kita buat Fungsi (UDF / User Defined Function) untuk menghasilkan password yang dimaksud. Langkah ini dimulai dengan memanfaatkan fungsi NOW di VBA untuk mendapatkan tanggal hari ini. Kemudian kita gunakan WorksheetFunction WEEKDAY untuk mendapatkan nomor hari. Terakhir, fungsi CHOOSE kita gunakan untuk mengkonversi Nomor Hari menjadi Nama Hari. Lengkapnya langkah pertama ini kita tuangkan dalam bahasa VBA sebagai berikut:

        Private Function PasswordKu() As String
                Dim NoHari As Integer
                      NoHari = Application.WorksheetFunction.Weekday(Now)
                      PasswordKu = Choose(NoHari, "minggu", "senin", "selasa", "rabu", "kamis", "jumat", "sabtu")
                End Function

• Kedua, kita buat Prosedur yang akan dieksekusi saat file dibuka, yakni Sub Auto_Open(). Di mana dalam prosedur ini kita memprotect semua worksheet dengan Password yang telah kita tentukan sebagaimana langkah di atas. Penulisan di VBA adalah sebagai berikut:

        Sub Auto_Open()
                Dim ws As Worksheet
                     For Each ws In ThisWorkbook.Worksheets
                            ws.Protect PasswordKu
                    Next ws
                End Sub

• Ketiga, kita buat prosedur untuk menghapus password pada saat file ditutup, yakni Sub Auto_Close(). Penulisannya di VBA adalah:

             Sub Auto_Close()
                          Dim ws As Worksheet
                                For Each ws In ThisWorkbook.Worksheets
                                   ws.Unprotect PasswordKu
                                Next ws
                          End Sub

Selesai.

Catatan: Penulisan kode makro di atas dengan cara:

1. Buka Excel

2. Tekan Alt F11

3. Insert > Module

4. Tuliskan kode macro-nya.

5. Tutup VBA Editor.

6. Simpan dengan format Excel 2003 atau .xlsm (macro-enabled)

Dengan cara di atas, maka setiap harinya, password proteksi worksheet kita akan berubah sesuai dengan nama hari (pakai huruf kecil semua, sesuai apa yang kita tulis di code).

Apa yang diungkapkan di atas hanyalah contoh sederhana saja, pengembangannya (untuk mendapatkan password yang lebih sulit, tergantung pada imajinasi dan kreatifitas kita sendiri). 

File contoh bisa didownload di Boxnet pada sidebar di samping atau di sini.

Contoh Aplikasi Raport yang Sudah Jadi

Pada posting sebelumnya, saya meng-upload Contoh Aplikasi Raport Sederhana yang masih bersifat mentah, artinya hanya data-data dan rumus-rumus penting saja yang dimasukkan. 
Kali ini saya upload sebuah file Aplikasi Raport Sederhana yang sudah bentuk jadi. Berikut 'penampakan' beberapa sheet:

Beberapa modifikasi dari Master Raport yang masih mentah:
1. Terdapat menu-menu yang memudahkan meng-akses bagian-bagian program
2. Menu cetak yang bisa disetting no urut-nya (sekali klik bisa langsung mencetak untuk semua siswa)
3. Nama Sheet yang tampil di layar hanya 1 sheet saja.
4. Password proteksi sheet BERUBAH-UBAH setiap harinya, mengikuti nama hari. Kalau file dibuka hari Senin, maka passwornya adalah 'senin'
5. Sudah dilengkapi Jumlah absensi, Kepribadian, Eskul, catatan wali kelas
6. dan lain-lain


Karena ini adalah program yang sudah jadi, maka saya memang membatasi penggunaannya untuk sekolah tempat saya mengajar saja. Penggantian nama sekolah perlu beberapa langkah. Sebab walaupun nama sekolah sudah diubah, ketika program ditutup, lalu dibuka lagi, Nama Sekolah asal akan kembali lagi. Hal ini karena ada sebuah Prosedur untuk mengganti nama sekolah begitu file dibuka. Jadi proses penggantiannya juga harus meng-edit kode macro di bagian Sub Auto_Open().


Oke, file bisa didownload di Boxnet pada sidebar di samping (Program Raport Jadi.rar) atau di sini

CATATAN TAMBAHAN:
Berhubung banyak permintaan password yang masuk ke e-mail saya, maka berikut password file tersebut:

1. Untuk semua Sheet, file-nya sama yakni mengikuti nama hari. Artinya jika dibuka hari KAMIS, passwordnya kamis.   Jika dibuka hari JUMAT, passwordnya : jumat . (Namun penggunaan password seperti ini memang menngandung kelemahan, yakni misal dibuka hari KAMIS, lalu nggak ditutup sampe hari JUMAT, maka program akan sedikit menngalami masalah, namun tetap bisa digunakan)
2. Untuk password VBA-nya adalah 123456

Oh ya, jangan lupa, untuk pencetakan secara otomatis, Default Printer harus disetting pada printer yang akan digunakan.

Kode Macro untuk Change Case Huruf di Excel

     Di Microsoft Word, terdapat fitur untuk mengubah Case Huruf yakni Change Case (Shift + F3) Bagaimana dengan Excel? Cobain aja…… dijamin gak bakalan bisa. 
Fitur standar untuk mengubah case huruf adalah dengan menggunakan fungsi : 

UPPER untuk mengubah semua huruf menjadi  kapital
LOWER untuk mengubah semua huruf menjadi huruf kecil
PROPER untuk mengubah huruf awal masing-masing kata menjadi huruf besar. 

Sayangnya, fungsi di atas harus tetap disandingkan dengan data awalnya.  


Dengan bantuan Macro, kita bisa menciptakan sendiri fasilitas untuk keperluan Change Case ini. Caranya? 

• Buka Excel, tekan Alt + F11 
• Pilih menu Insert > Module
• Kopi Pastekan kode berikut : 

Export/Convert File DBF (hasil Aplikasi UN) ke Excel

Pembaca, beberapa waktu yang lalu, saya diminta teman untuk meng-export File DBF hasil olahan aplikasi UN yang memang sudah diberikan oleh Dinas Pendidikan ke dalam bentuk File Excel. Berhari-hari saya cari cara yang paling mudah. Setalah bekerja keras, akhirnya saya mendapatkan cara mudah.


Mengapa harus di-export ke Excel?
Ya, kan eman. Kita udah susah-susah entri data di aplikasi UN tersebut, namun untuk pengolahan data kita sendiri harus nulis ulang di Excel. Lebih enak, kalau dari data di Aplikasi tersebut langsung kita export ke Excel untuk kemudian kita olah untuk Try Out, Penilaian, bahkan nanti membuat kartu peserta, dan SKHU. Sehingga nggak perlu nulis mulai awal lagi. Pekerjaan jadi nggak rumit lagi.


Oke, langsung saja caranya:
1. Kita cari terlebih dahulu hasil file DBF yang tersimpan di Folder APLIKASI UN, BIOSMA2012 (untuk SMA) / BIOSMP2012 (untuk SMP). Lalu cari folder yang berisi Kode Propinsi dan Kode Kabupaten. Kalau sekolah saya : 05-35. Lalu ke folder Biodata. Nah di folder tersebut ada file yang bernama BIO12-XXYYZZZR. Dimana XX adalah kode Propinsi, YY kode Kabupaten, ZZZ kode sekolah dan R kode untuk tingkatan.
2. Kita Download sebuah program yang bernama DBF Viewer. Gratis Kok. Downloadnya di sini
3. Setelah di-download, lalu kita instal sebagaimana biasa
4. Buka file DBF yang kita bahas di point 1.
5. Pilih Export > Excel .
6. Buka File Excel yang telah dibuatkan. Jangan kaget bila kondisinya nggak karuan.
7. Kita Save As sebagai Excel 2010 atau 2007 atau 2003
8. Selesai.

Demikian semoga bermanfaat

BerdataBase Ria dengan Excel

UPACARA PEMBUKAAN

Seseorang ingin mengolah suatu daftar menjadi informasi dengan susunan, urutan dan terfilternya data sesuai keinginan. Problem seperti ini sering muncul: Tak pelak lagi ini adalah kerjaan "ber-database-ria" yg memang didukung oleh Excel. 

Dalam hal ini, ada “dalil” bahwa hendaknya ada semacam pemisahan tahap, yaitu tahap pengorganisasian data, dan tahap penyajian informasi. Walau pembacanya sudah bosan tetapi saya tidak bosan karena suatu posting di milis itu bersifat tulisan tunggal (bukan serial) yg bisa "gak nyambung" kalau suatu komponen pemahamannya dipotong begitu saja.

Urusan retrieving data, sudah banyak dicontohkan di internet, kasus per kasusengan rumus-rumus canggih, dan kadang dengan macro jitu. Tetapi urusan penyiapan dan pengorganisasian data-nya agak jarang disentuh. 

Master Rapot Sekolah

Kegiatan rutin wali kelas adalah merekap nilai semua mata pelajaran dan menghitung nilai Raport di akhir semester. Posting kali ini berkenaan dengan pembuatan master Raport sekolah. Saya pernah membuat program rapot di sini. Nah, master Raport kali ini sangat berbeda jauh konsep pembuatannya dengan yang pertama.
Karena ini hanya master, maka saya tidak membuatnya sebagai sebuah program jadi, artinya editing dan penambahan hal-hal lain serta modifikasi tampilan masih diperlukan agar bisa digunakan. 
Master Raport sekolah bisa didownload di sini atau di BoxNet pada sidebar di samping kanan tulisan ini.


Untuk raport yang sudah jadi, bisa dilihat di posting ini

What is Array Formula?

Posting ini bersumber dari tulisan Mbak Siti Vi di milis XL-Mania.




Array Formula IS:
A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.)

Rumus Array, katanya, adalah:
Sebuah rumus yg mengerjakan multi-kalkulasi terhadap satu atau lebih dari satu himpunan nilai, dan kemudian menghasilkan hasil tunggal ataupun hasil multi. Rumus Array selalu dikurung (otomatis oleh excel) dgn sepasang tanda { } (braces/kurung kurawal), KARENA HARUS dimasukkan ke cell dengan menekan tombol CTRL+SHIFT+ENTER. "

array
A set of sequentially indexed elements having the same intrinsic data type. Each element of an array has a unique identifying index number. Changes made to one element of an array don't affect the other elements.

Array atau Larik
adalah: Sekelompok element berindex yg mempunyai typedata yg sama. Setiap elemen mempunyai nomor index yg unik. Pengubahan terhadap satu elemen tidak mempengaruhi elemen lainnya.
Dalam prakteknya di worksheet, sebuah range memenuhi syarat untuk disebut sebagai sebuah ARRAY. Apakah semua datanya = satuType atau tidak, akan mempengaruhi hasil Array Formula dimana range tsb digunakan sbg arguments-nya.


constant:
A value that is not calculated and, therefore, does not change. For example, the number 210, and the text ""Quarterly Earnings"" are constants. An expression, or a value resulting from an expression, is not a constant."

konstanta / tetapan:
sebuah nilai yg bukan suatu kalkulasi, dan oleh karena itu nilainya tetap / tidak berubah. Misalnya: bilangan 113 atau teks ""Selatan Jakarta"" adalah contoh sebuah konstanta.
Sebuah nilai yg berupa ekspresi / kalkulasi / rumus: BUKAN sebuah konstanta."

sebelum mulai bermain-main dgn Formula Array, sebaiknya kita fahami dulu beberapa istilah di bawah ini, meskipun, maafkan daku, banyak yg berupa berita-ulangan.

Formula
Formula (atau 'orang sini' bilang RUMUS), adalah persamaan yg mengkalkulasikan beberapa nilai dalam worksheet. Sebuah formula harus dimulai dengan tanda "samadengan" / equal sign (=).
Contohnya: FORMULA di bawah ini akan mengalikan 2 dgn 4 kemudian hasilnya ditambah dgn 7. Di cell Excel akan muncul hasilnya yaitu 15.
=7+2*4
Mengapa bukan 7 ditambah 2 dikerjakan lebih dahulu, itu termasuk salah satu aturan di excel yg sebaiknya juga kita ketahui.
Dalam formula tsb, selain tanda pembuka formula (=), terdapat dua macam ISI, yaitu operand (yg berupa konstanta 7, 2 dan 4) dan operator (yg berupa penambah (+) dan pengali (*). Kita tidak akan membicarakan bahwa ada banyak operator selain + dan *

Pada prinsip-dasarnya, Formula memang hanya terdiri dari Operand (yg mungkin dapat disebut pula sebagai Argument) dan Operator saja. Tetapi Operand itu dapat berupa banyak pilihan, antara lain:
Konstanta, yaitu nilai tetap yg kita tuliskan langsung
Rujukan / reference ke sebuah cell atau range/array !
Fungsi (Worksheet Functions)

Dalam pekembangannya, Rujukan ke cell / range, cell / range-nya ternyata dapat berisi sebuah FORMULA juga.
Sebuah Fungsi ternyata di dalamnya dapat berisi Fungsi-Fungsi juga (fungsi di dalam fungsi / nested functions)

"Titipan Protes & Pertanyaan dari Adek ""mBeling"":
Di atas, Formula dikatakan sebagai PERSAMAAN. Dalam bahasa matematik PERSAMAAN mengandung dua Ruas, Kiri dan Kanan, yg keduanya harus SAMA nilainya.
Sekarang wahai Mbak Iti-ku yg centil, Bagaimana ini ? Di mana Ruas Kirinya ? Hi hi hikk."
"Kamu mau Ruas Kiri Dek ? Mbak Iti tunjukkan Ruas Kirinya: yaitu Cell itu sendiri.
Cell itu minimal punya dua muka / dua tugas: (1) sebagai tempat menuliskan TEKS FORMULANYA yg dimulai dari batas ruas, berupa EqualSign (=).
Tugas kedua: dia adalah sebagai media penampakan dari Variable Memory yg menampung hasil formula tsb.
Variable=FORMULA, atau F16=FORMULA. ! Sudah kelihatan blum? Ruas Kirinya ??. Dan karena Variable diAssigned dengan Formula maka dia menjadi berisi HASIL formula itu, seperti yg kemudian ditampakkan ketika penulisan formula selesai kita enter. ('gitu aja kok repot to Dek..!
""ihh,... ngarang ! "" .... kaburrrr..)"
Function (Fungsi)
Fungsi sebetulnya juga sebuah formula, tetapi mengandung NAMA Fungsi, DAN cara penulisan (sintaks)nya sudah tertentu. Fungsi akan melakukan kalkulasi berdasarkan nilai-nilai pada argument-argument-nya secara spesifik sesuai yg telah ditentukan.
Contohnya: untuk menjumlah sekelompok bilangan di Excel sudah disediakan fungsi SUM
dgn sintaks =SUM(argument1, argument2,… dst...)
dimana argumentnya dapat berupa tiga jenis OPERAND yg sudah disebutkan di atas.
contoh: =SUM(A1:A13, B4:H4,5,(4*5/2),D21,AVERAGE(F10:F21))
SUM adalah nama fungsi, yg tugasnya hanya menjumlah nilai-nilai yg ada dalam arguments-nya.
A1:A13 adalah rujukan ke range, yg saat ini "kebetulan" menjadi Argument1 pada fungsi SUM
B4:H4 sebagai argument2, penjelasannya sama dgn argument1
5 adalah sebuah konstanta type bilangan, diisikan sebagai argument 4
4*5/2 adalah sebuah formula biasa, diisikan sebagai argument ke 5
D21 adalah juga rujukan ke range (yg kebetulan berupa range tanggal / cell), sbg argument ke 5.
Argument ke 6 berupa sebuah fungsi AVERAGE , yaitu mencari nilai rerata dari nilai-nilai yg ada di range F10:F21
, = tanda pemisah argument; ( ) tanda dimulai dan diakhirinya penulisan argument.

Bahkan di dalam fungsi SUM kita dapat memasukkan argument berupa logikal value (yg akan di "hargai" sebagai nilai 1 atau 0). Selain itu, teks bilangan yg diketikkan secara langsung juga akan dianggap sbg bilangan. =SUM("4",3,TRUE,FALSE) menghasilkan 8.
Untuk diketahui, di worksheet excel (Excel 2003) tersedia tak kurang dari 329 buah Fungsi bawaan.

Catatan Kecil, sebelum lupa: Menuliskan Operand ataupun Argument dalam sebuah Formula atau Fungsi TIDAK PERLU DIBERI TANDA +(Plus) lagi di depannya. Yg perlu diberikan hanya tanda - (minus) bila memang nilai MINUS yg dikehendaki. Ini perlu ditegaskan, karena dari worksheet contoh-contoh kasus masih ada beberapa penulisan seperti itu. Walau pun tidak merubah nilai, penulisan tanda + (plus) yang BUKAN sebagai Operator berarti melakukan sesuatu yg mabazir.

=+E14+F16/+D21 dapat di-perBETUL dgn =E14+F16/D21

Type Data
Di worksheet Excel ada pembedaan Type (jenis) data. Secara garis besar, ada dua Type Data, yaitu Type Bilangan (Numbers) dan Type Teks (String)
Bilangan adalah tyedata yg dapat di-aritmatik-kan (di tambah, kurang, bagi, kali, pangkat dst) dgn bilangan lain, dan akan menghasilkan bilangan baru. Sedangkan type String, tidak punya nilai bilangan, jadi tidak dapat di-aritmatik-kan.

Type Data Bilangan, untuk keperluan-keperluan tertentu dapat diFORMAT untuk dapat tampil sebagai type data TANGGAL / JAM dsb.
Apa pun cara tampilnya di worksheet, dia adalah tetap type bilangan. Tetapi Karakter Numerik (0 s/d 9) dapat dimasukkan ke cell sebagai Teks, secara sengaja maupun tidak sengaja. Hal seperti ini menjadikan Teks berpenampakan Bilangan. Excel 2003 mengangap data spt ini layak untuk diberi bendera hijau. (ErrorChecking: Numbers stored as Text)

Selain kedua type yg telah disebut di atas, ada data type Logikal, yaitu kondisi BENAR atau SALAH, yg di-representasikan dgn TRUE dan FALSE. Dalam kasus-kasus tertentu data type Logikal dapat dianggap (menjadi) data type bilangan, yaitu bernilai 0 atau 1.
Pada Array Formula yg sudah "tingkat lanjut", pemahaman ini sangat diperlukan.

Tatkala memasukkan data ke cell, hendaknya kita memperhatikan TYPE DATA yg kita kehendaki. Ada beberapa cara / kiat untuk memasukkan data ke cell agar oleh excel dianggap sbg type Bilangan (Numbers), terutama subType TANGGAL.
Tidak jarang kesulitan timbul dan ternyata berawal dari data kita yg kita SANGKA bilangan / Tanggal ternyata: bertype String.
Teks yg berpenampakan SEPERTI Bilangan atau SEPERTI Tanggal / Time, jika tidak disadari "ke-TYPE-an"-nya sering menjengkelkan karena menggagalkan HASIL YG DIKEHENDAKI dalam rumus.
SALAH TYPE ini sudah sering dibicarakan di milis XL-mania, harapan kita tidak diulang (JANGAN tidak disadari) oleh members lain, agar kita dapat segera meningkat ke pemikiran lain yg bukan sekedar SALAH TYPE saja.

Kapan kita perlu menulis Array Formula ?
Array Formula diperlukan: Jika dalam formula yg akan kita buat mengandung Evaluasi terhadap (minimal) SEKELOMPOK DATA (range / array) dengan syarat tertentu, dan hasil evaluasi itu (yg tentunya berupa BANYAK HASIL evaluasi) ingin kita ketahui secara individual, baik nantinya ditampilkan dalam BANYAK cell atau pun hanya ditampilkan dalam cell tunggal (setelah dirangkum dengan fungsi perangkum, misal SUM, AVERAGE, PRODUCT, MAX, MIN, SMALL, LARGE, AND, OR dsb)
NOTE: Array formula yg menghasilkan MULTI-HASIL, jika dituliskan hanya dalam satu cell akan hanya menampakkan HASIL-PERTAMA saja, atau bila MULTI HASIL tsb berupa sebuah RUJUKAN RANGE, akan menampakkan ErrorValue."
Yg siti maksud dgn Evaluasi terhadap sekelompok data, kira-kira begini (dalam kasus paling sederhana): range A1:A1000 berisi data-data bilangan. Anda ingin mengetahui Ada berapa buah bilangan yg Nilainya < 100. Maka Jawabannya hanya dapat dimunculkan bila kita TELAH MELIHAT SETIAP CELL dalam Range tsb SATU-PERSATU dan mencatat hasil tiap EVALUASI tsb.

Jika kasus yg kita hadapi minimal seperti itu, maka Array formula sudah mulai dapat dipertimbangkan penggunaannya. Tetapi dalam beberapa kasus sederhana yg sudah melibatkan array seperti itu; Excel sebetulnya SUDAH menyediakan fungsi-fungsi yg penulisannya tidak perlu di-enter dengan CSE. Misalnya Fungsi COUNTIF, SUMIF, SUMPRODUCT dan mungkin masih ada beberapa lagi.

Saya kira, minimal Ketiga fungsi ini sebaiknya kita ubek-ubek sampai kita faham "jalan ceritak"-nya. Berdasarkan pemahaman "fungsi ber-Aragument array sederhana" inilah kita punya pengalaman main array untuk masuk ke Formula Array yg lebih tinggi kompleksitasnya.

Fungsi semacam COUNTIF dan SUMIF, MATCH, hanya mampu diberi satu syarat, Jika syarat evaluasi sudah lebih dari satu, harus ada cara lain untuk mencari solusinya. Array formula adalah salah satu jawabannya.
Fungsi Fungsi lain yg argumennya dapat berupa Array, tidak harus dituliskan secara array formula (dan oleh karena itu: bukan Array Formula), misalnya fungsi SUM dan AVERAGE, MIN, MAX, INDEX dsb.."
Walau pun fungsi-fungsi semacam itu mengandung argument berupa array, misal SUM(A1:A1000), di sini tidak ada pekerjaan mengevaluasi dgn syarat tertentu terhadap setiap data di A1 s/d A1000. Yg terjadi hanyalah SEMUA nilai yg ada di Range tsb TANPA SYARAT dijumlahkan. Hasilnya pun selalu hanya Hasil Tunggal, bukan Multi Hasil. O.k.i. Fungsi SUM sederhana bukan termasuk Array Formula

Maafkan (lagi) daku, kalimat yg dimaksudkan sbg usaha menjelaskan itu kayaknya tidak mudah ditangkap maksudnya. Mungkin malah membuat hal yg ingin Anda ketahui menjadi semakin kabur. (yg menulis pun juga ndak mudeng jeee.., lha wong Cuma nulis dowang..) Oleh karena itu mempraktekkannyadalam beberapa contoh : hukumnya adalah "wajib dan halal" ! (begitu kata pak Syafril dari Seksi Kerohanian XL maniak... ya kan pak?!)

Pada wokbuk (terlampir), di beberapa sheets kita akan mencoba mempraktekkan array formula pada tingkat yg paling sederhana. (pasalnya mau membuat contoh yg tingkat yg canggih belum bisa..). Pengembangan ke tingkat yg lebih sofistiketed, kuncinya tentu ada pada diri kita masing-masing.., komponennya: kesempatan, waktu dan kemauan & ke-ngeyel-an.. Masih ingatkah Anda, bang & mpok Momods pernah bilang bahwa Array Formula dapat bekerja sebagai Looping ? Sehingga dalam kasus tertentu Array Formula adalah PseudoLooping ?

Di Help Excel, ada beberapa topik yg bagoes mengenai Array formula. Pada boks "Search For" ketikkan Array Formula, dan help akan menyajikan daftar topik terkait. Di Excel 2003, dlm kondisi OffLine, tersaji 20 results, tetapi yg benar-2 beraroma array formula hanya 5 bijik. Sikat semua aja blèh !!

.

lampiran Workbook bisa didownload di Boxnet pada sidebar di samping kanan tulisan ini atau di sini

Form Input sekaligus sebagai Form Output

Dalam mengerjakan tugas sehari-hari di sekolah, seringkali kita berhubungan dengan DATA. Akan lebih mudah bagi operator data jika data tersebut didesain secara simple. Secara umum, desain Data terdiri dari tiga bagian, yakni Input, Proses, Output.
Tulisan kali ini membahas tentang pembuatan Form Input yang sekaligus juga digunakan sebagai Form menampilkan output dari data yang sudah kita simpan sebelumnya. Form yang berfungsi ganda ini biasanya digunakan untuk: Rekap Absen, Formulir Pendaftaran Siswa, Pengisian Nilai, dan lain sebagainya.
Untuk contoh kita kali ini, disajikan Form pengisian Data Siswa per kelas.

Jadwal Pelajaran Sekolah (Maks. 15 Kelas) di Excel

Membuat dan mengatur jadwal pelajaran memang bikin pusing. Sering jadwal yang sudah diutak-atik selama seminggu lebih, masih ada saja yang bentrok. Apalagi banyak guru yang minta hari khusus. Sebbel dech...!


Kali ini saya luncurkan contoh file Jadwal Pelajaran yang bisa digunakan untuk maksimal 15 kelas. Untuk yang lebih 15 kelas, gak usah lha yaw, karena pasti sekolahnya udah maju. Jadi pembuatan jadwalnya gak serumit sekolah kecil kayak sekolah tempat saya mengajar.


Nah, pada file contoh Jadwal Pelajaran ini, terdapat fasilitas sebagai berikut:

• Terdapat form permintaan jam hari hari kosong dari masing-masing guru
• Cek jadwal bentrok
• Cek jadwal apakah sudah sesuai dengan permintaan sebagian guru
• Jadwal per kelas
• Jadwal per guru
• maksimal jumlah guru adalah 60

Kelemahan:

• belum bisa mengatur jadwal piket masing-masing guru
• belum bisa diterapkan untuk team-teaching

File ini ada beberapa sheet yang diprotect, namun tidak dipassword.
Special thanks for Bapak Muhammad Syukron yang telah banyak membantu selesainya file ini. 


File bisa didownload di Boxnet di sidebar (di samping kanan tulisan ini) atau di sini

Hisab Hari Raya dan Ramadlan Tahun 2000 sampai 2100 Versi Madzhab Al-Excel-Liyah

Mendekati Hari Raya Idul Adha 1432 H yang akan berlangsung tanggal 6 Nopember 2011 besok, dan perdebatan sengit tentang penetapan Hari Raya Idul Fitri yang sudah berlalu, terbersit pikiran dalam benak saya untuk membuat hisab sendiri yang berkiblat pada perhitungan Microsoft Excel (Al-BillGatiyah). 
Perhitungan atau hisab ini bukan untuk menambah perdebatan tentang penetapan hari raya, namun hanya sekedar buat guyonan dan belajar yang memang tidak bisa dipertanggungjawabkan keakuratannya. Hisab versi Excel ini tidak merujuk ke kitab-kitab hisab standar, tidak juga merujuk ke perhitungan astronomis yang bagi orang awam seperti saya sulit dipahami.
Apa yang saya tulis, murni menggunakan kalkulasi yang memang built-in Ms Excel. 
Sekali lagi, hasil perhitungan yang saya lampirkan dalam tulisan ini TIDAK DAPAT DIJADIKAN PEDOMAN untuk menetapkan Hari Raya dan ramadlan. Ini hanya dimaksudkan untuk menambah pengetahuan saya khususnya tentang bagaimana Excel memperlakukan Date (tanggal) termasuk tanggal hijriyah.
Oke, langsung saja ke pokok masalah.
Untuk menampilkan sebuah tanggal dalam format hijriyah, maka kita format sel yang berisi tanggal dengan "b2dd mmm yyyy".
Misalnya di sel A1, kita tulis 11/6/2011 (06 Nopember 2011), terus kita klik kanan, format cell, pilih tab Number, Custom, Tuliskan "b2dd mmm yyy". OK
Selesai, yang tampakdi layar adalah tanggal 1432/ذو الحجة/10
Lengkapnya untuk tahun 2000 sampai 2100 dapat didwnload di sini atau di BoxNet pada sidebar di samping.

Memberi Shading Setiap Baris Ganjil

Perhatikan gambar di atas. Setiap baris ganjil kita beri warna yang berbeda. Kalau data yang kita punya hanya sedikit, pekerjaan memberi warna dasar (Fill Color) tidak jadi masalah. Tapi kalau datanya sampai ratusan? Capek juga. 


Nah, tip berikut cara mudah untuk memberi warna untuk baris yang ganjil saja (sebenarnya kita bisa memilih sendiri, mau yang ganjil saja atau yang genap saja).

1. Blok semua sel yang mau diformat, baik ganjil maupun genap. Dalam hal ini, sel A2:D11
2. Pilih Home --> Conditional Formatting --> New Rule --> Use a formula to determine which cells to format
3. Ketikkan rumus berikut: =MOD(ROW(),2)=1
4. Klik Format. Pilih tab Fill. Pilih warna yang diinginkan. OK
5. Selesai.

Ket: Jika ingin baris genap yang mempunyai warna berbeda, maka rumusnya : =MOD(ROW(),2)=0


Sekian dulu. Terima Kasih

Membuat Sendiri Kamus Binatang Inggris Indonesia (Bergambar)

Gambar di atas, adalah tampilan kamus binatang bergambar yang dibuat menggunakan Microsoft Excel. File-nya bisa didownload di sini. atau di Boxnet pada sidebar

Cara Cepat Mem-Protect Semua Sel yang Mengandung Formula

Dalam membuat aplikasi penilian misalnua, di mana dalam aplikasi tersebut, kita hanya mengijinkan user untuk menginput nilai di sel yang sudah disediakan. Kita tidak ingin user meng-utak-atik, memodifikasi bahkan men-delete sel-sel yang mengandung formula tersebut agar aplikasi tetap berjalan dengan baik. Cara yang sering digunakan untuk melakukan hal tersebut adalah dengan mem-protect sheet. Namun, dengan memperotect sheet, tidak hanya mencegah user mengubah formula, tapi juga mencegah user menginput apapun di sheet tersebut.

Secara default, seluruh sel dalam worksheet sudah disetting Locked. Namun, hal ini tidak berpengaruh apa-apa sebelum kita memprotect sheet.Tips berikut adalah cara mudah untuk memprotect sel yang hanya mengandung formula saja dan tetap membiarkan sel yang lain tidak ter-Locked.

Membuat Mega Formula di Excel

Tip berikut menggambarkan cara menggabungkan beberapa formula menjadi satu formula yang panjang (Mega Formula). Jika kita sering bekerja dengan Excel, kita sering melihat beberapa rumus yang cukup panjang dan sulit untuk dipahami jalan ceritanya. Berikut adalah contoh membuat Mega Formula


Tujuan dari tip ini adalah membuat satu rumus (tanpa sel bantuan) untuk membuangnama tengah dari sebuah nama yang terdiri dari 3 kata. Tabel berikut menunjukkan beberapa nama yang diikuti dengan rumus-rumus perantara yang pada akhirnya dibuat untuk mengambil nama depan dan nama belakang saja. 

Rumus yang digunakan di baris yang kedua adalah sebagai berikut: