Formula Excel

Kepada para pengunjung blog Formula Excel, sebelumnya saya mohon maaf atas keterlambatan update Billing Formula v1.0 dan Tunas Gadget v1.0 yang telah expire ini. Tanggal expire sudah saya ganti menjadi tanggal 31 Desember 2011. File update bisa didownload di menu DOWNLOAD. Untuk Tunas Gadget Anda cukup mengganti (replace) file tersebut di folder yang sama.

Saya juga mohon maaf karena kesibukan saya, saya belum sempat lagi menambahkan artikel-artikel yang mungkin berguna bagi Anda semua. Saya berterima kasih kepada teman-teman sekalian yang telah merespon blog saya ini melalui email saya. Bagi yang telah saya bantu solusi atas pertanyaan-pertanyaan, saya harap semoga bisa banyak membantu dan mempermudah pekerjaan Anda. Namun bagi yang belum sempat atau tidak bisa saya jawab pertanyaannya (karena tidak berhubungan dengan Excel) saya mohon maaf karena tidak bisa banyak membantu.

Terima kasih telah mengunjungi blog saya ini.

Jika anda menemukan data dengan angka yang berformat teks, tentunya ada tidak dapat menjumlahkan atau mengolah angka-angka tersebut untuk dijumlahkan, di-sumif, ataupun dengan rumus lainnya karena formatnya yang masih berupa teks. Berikut gambaran perbedaan format number dan format teks:

• 1. Cek regional setting anda di control panel. Jika regional setting anda Indonesia, maka karakter pemisah ribuan adalah "." (titik) dan karakter pemisah desimal adalah "," (koma). Sedang jika regional setting anda adalah English (United States), maka karakter pemisah ribuan adalah "," (koma) dan karakter pemisah desimal adalah "." (titik).
• 2. Blok A1:A17 lalu pilih Edit > Replace (Ctrl + H). Karena komputer saya regional settingnya adalah Indonesia, maka ketik pada kolom Find what: dengan "," (koma) dan kosongkan pada kolom Replace with: lalu klik Replace All. Anda dapat melihat bahwa karakter "," (koma) sudah hilang. Jangan tekan tombol Close dulu.

• 3. Masih di kotak dialog Find and Replace, sekarang ganti pada kolom Find what: dengan "." (titik) dan pada kolom Replace with: dengan "," (koma), lalu tekan tombol Replace All.

Jika Anda bekerja dengan banyak data yang menggunakan nama orang, suatu saat Anda mungkin ingin memisahkan nama-nama tersebut menjadi dua bagian, yaitu nama dengan dan nama belakang. Dengan menggunkan rumus ini, pekerjaan tersebut sangat mudah dilakukan:

• LEFT(A1 artinya anda akan mengambil nama dari kolom A1 yang dimulai dari paling kiri.
• FIND(" ";$A$1:$A$16;1)-1 akan mencari karakter spasi " ". Misal pada nama "Aan Arifin" karakter spasi ditemukan pada karakter ke-4, maka nilai yang dihasilkan adalah 4. Rumus -1 artinya angka 4 tadi dikurangi dengan -1 agar menjadi 3, sesuai dengan nama "Aan" yang hanya ada 3 karakter.

• =MID(A1; artinya anda akan mengambil nama dari kolom A1 yang dimulai dari tengah.
• FIND(" ";$A$1:$A$16;1)+1, sama seperti rumus FIND di atas, untuk mencari karakter spasi " ", namun kali ini +1 untuk mencari nama belakang. Rumus FIND(" ";$A$1:$A$16;1)+1 akan menghasilkan angka 5, di mana nama "Arifin" dimulai dengan karakter ke-5. Rumus ;100) artinya karakter yang diambil untuk nama belakang diasumsikan 100 karakter.

Terkadang kita dihadapkan dengan banyak data yang bisa berjumlah ratusan atau bahkan ribuan. Dan masalahnya data tersebut mungkin saja bisa terjadi duplikasi data atau data lebih dari satu. Nah, kalau data yang terduplikasi tersebut menjadi reference atau sumber dari perhitungan tertentu, hal ini tentunya akan menjadi masalah bagi anda dalam mencari selisihnya.

Biar lebih mudah, berikut saya berikan gambarannya:

• Hasil dari rumus COUNTIF($C$19:$C$32;C19) adalah jumlah duplikasi data. Dalam hal ini type Avanza ada 2 duplikasi data, sedang yang lain hanya ada 1 data.
• Jika hasil dari perhitungan COUNTIF lebih dari 1, maka teks yang dimunculkan adalah "Duplikasi data", sedang jika tidak lebih dari 1, maka isi selnya kosong "".

Berikut adalah langkah-langkah untuk menjalankan Billing Formula:

A. TRANSAKSI RENTAL 
1. Masukkan Username dan Password lalu klik Login. Gunakan username: sa password: sa. Secara otomatis Anda akan menuju sheet sesuai tanggal hari ini. Misal hari ini adalah tanggal 16 Mei 2010, maka Anda akan langsung menuju ke sheet 16.

 Berikut adalah screenshot Billing Formula v1.0:

Melihat respon yang cukup baik dari pembaca artikel saya di IlmuKomputer.Com yang berjudul "Tips Membuat Billing Warnet dengan Excel" dan banyaknya dari mereka yang minta dikirimkan file yang sudah jadi, membuat saya untuk mengembangkan file tersebut menjadi Billing Excel yang lebih canggih. Billing yang saya tujukan untuk warnet dan rental komputer yang saya namakan "Billing Formula" ini diperkuat dengan rumus macro Visual Basic Application (VBA) Excel dengan form-form yang akan mempermudah navigasi untuk operator dalam menjalankan operasional warnet atau rental komputernya.

 Beberapa kelebihan Billing Formula ini adalah:

•  Sistem login dengan password masing-masing. Disediakan 1 login untuk Administrator dan 5 login untuk user/operator.
• Menu Hak Akses User untuk menentukan menu apa saja yang bisa diakses oleh User. Misalnya, operator tidak boleh membuka menu Ubah Harga.
• Sistem Navigasi disediakan untuk 10 dan 20 client/komputer sehingga memudahkan operator mana saja client yang kosong dan mana saja client yang sudah terisi.
• Untuk mementukan harga, disediakan 12 setingan harga yang dipisahkan menjadi 6 Harga Reguler dan 6 Harga Khusus sehingga Anda bisa dengan fleksibel dalam menentukan harga.
• Sistem Membership dengan deposit sehingga Anda bisa mengontrol aktivitas member Anda seperti sisa deposit, tanggal terdaftar, login terakhir, dan status membership.
• Pembayaran dengan sistem cash atau memotong sisa deposit member.
• Disediakan 10 jenis transaksi yang bisa Anda tentukan sendiri, misalnya Print, Scan, Minuman, dll.
• Menu untuk tambah, ubah, dan hapus transaksi.
• Menu untuk tambah, ubah, hapus member, dan tambah deposit member.
• Menu Statistik untuk melihat statistik hari ini.
• Menu Log Off jika akan berganti Operator.

• Navigasi
  Floating menu yang terdapat tombol MULAI dan SELESAI. Dilengkapi juga dengan status 10 atau 20 client, jika berwana abu-abu maka status client adalah STANDBY, sedang jika berwarna hijau maka status client adalah AKTIF. Status ini untuk membantu operator dalam mengontrol posisi di mana user yang sedang aktif.
• Sheet Transaksi Hari Ini
  Jika Anda memilih menu ini maka Anda akan langsung menuju sheet transaksi hari ini. Misal jika hari ini adalah tanggal 15 Mei 2010, maka Anda akan langsung menuju sheet 15. Ini untuk memudahkan Anda memilih sheet transaksi hari ini mengingat jumlah sheet adalah 33 sheet.
• User dan Password
  Menu ini untuk mengatur username Administrator dan 5 user lainnya dan tipe usernya masing-masing. Pada tab Password memungkinkan Anda untuk me-reset password user-user tersebut.
• Hak Akses User Menu untuk menentukan hak akses masing-masing user. Misalnya jika Anda ingin User5 tidak boleh akses ke menu Ubah Harga, maka cukup kosongkan tickmark pada checkbox Ubah Harga.
• Ganti Password
  Menu ini untuk mengganti password user yang sedang login.
• Identitas Usaha
  Menu ini untuk menentukan Nama Usaha, Alamat dan Nomor Telepon Usaha, serta Nama Pemilik Usaha. Pada bagian Serialisasi terdapat keterangan serialisasi file Billing Formula seperti Terdaftar Kepada, Nomor Serialisasi, Versi, dan Masa Evaluasi Hingga. Jika masa evaluasi sudah berakhir maka file ini sudah tidak bisa digunakan lagi. Artinya Anda hanya bisa membuka file ini, tapi menu bar Billing Formula tidak akan muncul sehingga Anda tidak bisa lagi melakukan penambahan, perubahan, ataupun penghapusan data. Silahkan kunjungi blog ini lagi untuk mendapatkan versi yang terbaru.
• Ubah Harga
  Menu ini untuk menentukan harga menit pertama dan menit selanjutnya. Saya telah buatkan sefleksibel mungkin yaitu dengan menyediakan 12 jenis harga yang dibagi menjadi 6 harga reguler dan 6 harga khusus. Hindari untuk menggunakan kode yang sama dari keduabelas jenis harga tersebut.
• Pengaturan
  Menu ini untuk mengatur banyaknya jumlah PC atau client yang Anda miliki. Hanya terdapat pilihan 10 dan 20 client. Anda juga bisa mengatur masa waktu member yang aktif. Pada sheet Transaksi Anda dapat menentukan 10 nama transaksi di luar warnet atau rental komputer.
• Input Transaksi
  Menu ini untuk menginput transaksi-transaksi lain seperti print, scan, dll. Silahkan masukkan angka berupa total per transaksi yang harus dibayar oleh pelanggan. Misal jika pelanggan print sebanyak 20 lembar dengan harga @ Rp. 500,- maka masukkan angka 10000.
• Ubah Transaksi
  Menu ini untuk merubah transaksi client. Data yang bisa diubah adalah Kode Client, Jam Mulai, Jam Selesai, 10 transaksi, dan Penerimaan.
• Hapus Transaksi
  Menu ini untuk menghapus semua transaksi client yang dipilih. Bukan hanya 10 transaksi, tapi semua data transaksi client di baris yang dipilih akan dihapus.
• Pembayaran
  Secara otomatis pada saat Anda menekan tombol "SELESAI" dari menu Navigasi, kotak Pembayaran akan muncul. Namun menu ini disediakan jika client sudah selesai namun tidak langsung membayar. Menu pembayaran bisa Anda pilih jika pelanggan sudah siap untuk membayar. Pada menu ini terdapat informasi client dan pembayaran. Silahkan input di kolom "Dibayar" untuk pembayaran dengan cash. Namun jika pembayaran dilakukan dengan memotong deposit maka silahkan tekan tombol "Bayar dengan Deposit..." sehingga muncul kotak dialog Deposit Member.Pada Deposit Member akan muncul informasi sisa deposit yang dimiliki oleh member tersebut. Jika sisa deposit tidak cukup untuk membayar "total yang harus dibayar" oleh member, maka secara otomatis akan ditolak dan member diminta untuk menambah jumlah deposit memeber terlebih dahulu.
• Pindah Client
  Menu ini untuk mengakomodir jika client ingin pindah PC ke client yang lain. Secara otomatis status pada menu Navigasi akan berpidah ke client yang dituju.
• Daftar Member Baru
  Menu ini untuk mendaftarkan member baru. Saya sediakan juga kolom deposit jika member ingin melakukan pembayaran dengan deposit.
• Ubah Data MemberMenu ini untuk merubah data member termasuk login terakhir dan jumlah deposit awal dan deposit yang sudah terpakai.
• Hapus Data Member
  Menu ini untuk menghapus data member yang dipilih.
• Tambah Deposit Member
  MEnu ini untuk menambahkan deposit member. Setelah tombol Ok ditekan, maka nilai pada kolom Saldo Deposit akan menjadi Deposit Awal dan Pemakaian akan menjadi nol kembali.
• Statistik
  Menu ini untuk menunjukkan statistik transaksi hari ini. Terdapat informasi Jumlah Client, Penerimaan, Durasi rental yang digunakan, dan Rincian Transaksi. Statistik ini untuk mempermudah Anda dalam membaca operasional warnet atau rental komputer Anda hari ini.
• Log Off
  Menu ini untuk mengakomodir jika terjadi penggantian operator yang menggunakan sistem shift.
• Tentang Billing Formula
  Informasi tentang file Billing Formula yang Anda miliki.

Sebelum direlease untuk umum, saat ini saya sedang melakukan Trial and Error Billing Formula v1.0 kepada ketiga rekan saya yang bersedia membantu saya memberikan feedback jika masih terdapat bugs dalam Billing Formula v1.0. Ketiga rekan saya yang bersedia untuk berpartisipasi dalam Trial and Error tersebut adalah:

• Rezzas Razzes Al Fabari
• Suwanto Nico
• Dul Fuad

 

 

 

Mengurutkan data berdasarkan angka atau huruf mungkin sudah biasa, tapi dapatkah Anda mengurutkan data berdasarkan warna? Dengan bantuan 2 fitur dari JMT Excel Utilities hal ini sangat mudah dilakukan yaitu dengan Show Selection Tools dan Show Range Tools. Silahkan klik gambar di bawah ini untuk melihat animasi ilustrasinya:

Sama seperti fitur di JMT Excel Utilities yaitu Show Fill and Unfill Tools, dengan sedikit rumus kita bisa melakukan hal yang sama. Perhatikan gambar berikut:

Fitur ini bertujuan untuk memudahkan Anda dalam membuat penanda tanggal dan waktu sehingga Anda tidak perlu repot lagi dalam membuat tanggal dan waktu saat ini dan memberikan format yang Anda inginkan.

Sama seperti fitur Hide Rows with Criteria, fitur ini bertujuan untuk menyembunyikan baris-baris yang mengandung kriteria tertentu. Kriteria yang dimaksud bisa berupa angka nol, sel kosong, atau sel yang mengandung kata tertentu.

• 0 (Zero Value): Kriteria dengan sel yang mengandung nilai 0 (nol). Criteria of cell that contains the 0 value (zero).
• Empty cells: Kriteria dengan sel yang kosong. Criteria of empty cells.
• Text: Kriteria sel yang mengandung kata tertentu. Input teks yang akan dicari pada kolom yang disediakan. Criteria of cells that contain certain words. Input text to be searched in the field provided.

Fitur ini bertujuan untuk menyembunyikan baris-baris yang mengandung kriteria tertentu. Kriteria yang dimaksud bisa berupa angka nol, sel kosong, atau sel yang mengandung kata tertentu.

• 0 (Zero Value): Kriteria dengan sel yang mengandung nilai 0 (nol). Criteria of cell that contains the 0 value (zero).
• Empty cells: Kriteria dengan sel yang kosong. Criteria of empty cells.
• Text: Kriteria sel yang mengandung kata tertentu. Input teks yang akan dicari pada kolom yang disediakan. Criteria of cells that contain certain words. Input text to be searched in the field provided.

Fitur ini bertujuan untuk mencari data yang ganda (lebih dari satu). Setelah ditemukan, data ganda tersebut bisa diberi format tebal dan diberi warna, hapus seluruh baris, atau hapus isinya.

• Format as Bold and colored: data duplikasi akan diberi format huruf tebal dan akan diberi warna sesuai dengan pilihan warna di dropdown menu. Duplicate data will be given a bold format and will be assigned a color according to color choice in the dropdown menu.
• Delete Entire Row: data duplikasi akan dihapus seluruh baris. Duplicate data will be deleted the entire rows
• Clear Contents: data duplikasi akan dihapus dari sel. Duplicate data will be cleared from the cell.

Sama seperti JMT Excel Utilities, Tunas Gadget adalah sekumpulan rumus macro yang digabung dalam bentuk Add-Ins. Tujuannya tidak lain adalah untuk membuat pekerjaan Anda menjadi lebih mudah. Ada beberapa fitur di Tunas Gadget yang mirip dengan fitur di JMT Excel Utilities.

• Serial Number: 1011D31DOD02
• Registered to: Formula Excel - http://formula-excel.blogspot.com
• Expiry Date: 12/31/2011

Fitur Text and Comment ini berfungsi untuk mengkonversi Comment ke Teks, Teks ke Comment, atau membuat daftar semua comment yang ada dalam sheet aktif.

1. Pada Reference Cell, pilih dengan sel sebagai referensinya. Jika Anda ingin memilih Comment to Text artinya pilih sel yang mengandung Comment. Jika Anda ingin memilih Text to Comment artinya pilih sel yang mengandung teks yang akan dijadikan comment. In the Reference Cell, select a cell as the reference. If you want to select the Comment to Text means you have to select the cell that contains a comment. If you want to choose Text to Comment means you have to select a cell that contains text to be used as comment.
2. Pada Output Cell pilih sel sebagai sel outputnya. In the Output Cell select the cell as the cell output.
3. Pilih fungsinya: Select function:

• Comment to Text: mengkonversi Comment menjadi Teks. Converting Comment into Text.
• Text to Comment: mengkonversi Teks menjadi Comment. Converting Text to Comments.
• List all comments in active sheet: membuat sheet baru dan membuat daftar comment pada sheet yang aktif. Create a new sheet and make a list of comment on active sheet

Fitur ini sangat cocok untuk Kasir. Fitur ini menerjemahkan angka menjadi teks terbilang yang seperti pada kwitansi. Contoh: angka 10.000 menjadi Sepuluh Ribu Rupiah dan juga tersedia dalam bahasa Inggris menjadi IDR Ten Thousand.

Fitur ini berfungsi sama seperti fungsi Change Case di Microsoft Word. Simple namun cukup bisa membantu jika Anda punya banyak data di mana Anda ingin merubah format penulisannya.

• UPPER CASE: format huruf besar
• lower case: format huruf kecil
• Sentence case: format huruf kalimat
• Title Case: format huruf judul

Bagi Anda yang muslim dan yang pekerjaan sehari-harinya menggunakan Excel, tentu fitur ini akan sangat membantu Anda dalam mengingatkan Anda waktu sholat. Waktu sholat ini bisa Anda seting sesuai dengan keinginan Anda. Waktu sholat ini juga ada pilihan untuk Sholat Duha dan Ngabuburit untuk bulan Ramadhan. Fitur Snooze juga disediakan dan Anda bisa mengisi interval waktu sesuai keinginan Anda.

With this feature you can easily delete things.

• Selected Range
• Active Sheet
• Active Book

Dengan fitur ini Anda dapat dengan sangat mudah menghapus banyak hal.

• Selected Range: range yang terseleksi
• Active Sheet: sheet yang sedang aktif
• Active Book: workbook yang sedang aktif

Is your Excel is additional Add-Ins that require you to select manual calculation? With this feature you can select the calculation you want. Is it only a range, entire sheet, a workbook, or even entire workbook?

Apakah Anda menggunakan Add-Ins tambahan yang mengharuskan Anda untuk memilih kalkulasi manual (Manual Calculation)? Dengan perintah ini Anda bisa memilih kalkulasi yang ingin Anda lakukan. Apakah hanya sebatas range, seluruh sheet, satu workbook, atau bahkan seluruh workbook?

This feature helps you when you want to save the active sheet as a separate workbook. It's easier than doing the command Move or Copy ... > (New book)> File Save As. You will be asked to specify the directory and file name.

Fitur ini bisa Anda gunakan saat Anda ingin menyimpan sheet yang sedang aktif menjadi file workbook yang terpisah. Lebih mudah daripada melakukan perintah Move or Copy... > (new book) > File Save As. Anda akan diminta untuk menentukan direktori dan nama filenya.

Do you want to reopen your workbook without saving it instantly? With this feature you can do it instantly without having to close it and open the file again through the Open button or through Windows Explorer and search for the file.

Do you ever feel want to change the current's workbook file name you are opening? Of course, without this feature it can not be done instantly. You certainly have to close the first file, open Windows Explorer, find the directory and then change its name.

Anda ingin merubah nama file yang sedang Anda buka? Tentunya tanpa fitur ini hal tersebut tidak dapat dilakukan. Anda tentunya harus menutup file tersebut terlebih dahulu, membuka Windows Explorer dan mencari direktorinya dan kemudian merubah namanya.

Pernahkan Anda merasa untuk perlu menutup file yang sedang Anda buka tanpa ingin menyimpannya dan membuka kembali file tersebut? Dengan fitur ini Anda dapat melakukan hal tersebut tanpa harus membuka lagi melalui tombol Open atau melalui Windows Explorer dan mencari-cari file yang dimaksud.

You certainly know that when you copy cell/range that contains no absolute formulas (no $ sign), the result of the formula must be different because the formula will automatically change. But how if we want to copy the formula to other cell with the same result without having to change the formula, although the cell reference is not made absolute? With the Copy and Paste Exact Formulas feature you can do it easily. The result will be identically same.

The picture is as follows:

Anda semua pasti sudah tau bahwa saat Anda mencopy sel/range yang berisi formula tidak absolut (tidak ada tanda $) tentunya saat Anda pasti di sel lain, rumus akan secara otomotis berubah. Namun bagaimanakah jika kita ingin mencopy rumus-rumus tersebut ke sel lain tanpa harus merubah referensi sel walaupun rumus tidak dibuat absolut? Dengan fitur Copy and Paste Exact Formulas Anda dapat melakukannya dengan mudah. Anda akan mengcopy-paste rumus yang identik sama.

Gambarannya sebagai berikut:

The following display Sheets Utilities dialog box:

1. Merge: Combine several cells into one cell
2. AutoFit: Make a cell changed its size to adjust the width / height the cell contents
3. Resize: Change the column width or row height to the standard size
4. Unhide: Displays the column / row that is hidden
5. Flip Range: Flip contents in range
6. Randomize: Randomize cell contents.
7. Set to Grid: Make all rows and columns into smaller boxes.

Bagi Anda yang sering “bermain” dengan comment, tentunya fitur ini akan sangat membantu Anda.

Tab 1. General Settings

• Pilih area yang akan efek pada perintah yang akan dijalakan apakah hanya:
     => Selection (range)
     => Active Sheet (sheet yang sedang aktif)
     => Active Book (workbook yang sedang aktif)
• Kemudian pilih perintah yang akan dijalankan.
  
  
  

Still in Selection, this feature is used to move, enlarge, or shrink, or expand range.

• Select Adjacent Cells
• Expand Range
• Shrink Range
• Select to edge of Used Range
• Select to edge of sheet

This feature is very useful for you who want to quickly fills the empty cells referring the previous cell's value. See figure below:

• Fill Cells in Rows: fill the empty cells in rows
• Fill Cells in Columns: fill the empty cells in column
• Unfill Cells in Rows: delete the contents of the cell in rows
• Unfill Cells in Columns: delete the contents of the cell in column

Masih dalam rangka seleksi, fitur ini bertujuan untuk memindahkan, memperbesar, atau memperkecil range.

• Select Adjacent Cells (memindahkan range)
• Expand Range (memperbesar range)
• Shrink Range (memperkecil range)
• Select to edge of Used Range (memperbesar range hingga sel (yang berisi nilai) paling ujung
• Select to edge of sheet (memperbesar range hingga ujung sheet

Fitur ini sangat berguna bagi Anda yang ingin mengisi dengan cepat sel-sel yang kosong dengan nilai sel yang berada di atasnya ataupun sebaliknya. Berikut gambarannya:

• Fill Cells in Rows: mengisi sel baris
• Fill Cells in Columns: mengisi sel kolom
• Unfill Cells in Rows: menghapus isi sel baris (menyisakan isi sel paling atas)
• Unfill Cells in Columns: menghapus isi sel kolom (menyisakan isi sel paling kiri)

If you want to quickly add rows or columns with a certain interval, then this feature can help you do this quickly.

• Rows to insert: Enter the number of rows you want to add
• Every Nth row: Enter the row interval value 
• Columns to insert: Enter the number of columns you want to add
• Every Nth column: Enter the column interval value

Jika Anda ingin secara cepat menambahkan baris atau kolom dengan interval tertentu, maka fitur ini dapat membantu Anda dengan cepat melakukannya.

• Rows to insert: Masukkan jumlah baris yang ingin ditambahkan
• Every nth row: Masukkan nilai interval barisnya
• Columns to insert: Masukkan jumlah kolom yang ingin ditambahkan
• Every nth column: Masukkan nilai interval kolomnya