Posts filed under ‘Tips’

Filter Excel Tingkat Lanjut

1. Mempersiapkan data sebagai database

  1. Baris pertama (A1:D1) adalah judul data.
  2. Baris-baris berikutnya berisi data.
  3. Tidak boleh ada baris yang kosong diantara data yg ada dalam database.
  4. Baris kosong pada akhir data dan kolom paling kanan.


2. Mempersiapkan Range untuk Kriteria

Dalam range yang kita tetapkan untuk kriteria, kita memasukkan aturan data yang akan ditampilkan apabila kita mem-filter data. Kita dapat menggunakan satu atau lebih kriteria.

  1. Dalam contoh ini, sel F1:F2 adalah range untuk kriteria.
  2. Judul kita letakkan pada sel F1.
  3. Dan sel F2 berisi kriterianya. Tanda (operator) > (lebih besar dari) dipergunakan bersama-sama dengan angka 500.

Setelah diaplikasikan, maka hanya pesanan yang memiliki total penjualan lebih besar dari Rp 500 akan ditampilkan.

Operator-operator lainnya adalah:
< lebih kecil dari
<= lebih kecil dan sama dengan
>= Lebih besar dan sama dengan
<> tidak sama dengan


3. Mempersiapkan Range untuk hasil pengaplikasian filter (ekstrak data)

Jika anda ingin mengcopykan data ke lokasi lainnya, maka anda harus menetapkan kolom-kolom yang akan anda ekstrak. Jika anda ingin seluruh kolom, maka anda harus mengosongkan range tersebut..

  1. Pilih sel di bagian kiri atas range untuk data yang akan diambil.
  2. Ketik judul untuk kolom yang ingin Anda ekstrak. Harus sesuai dengan kolom judul yang sama, dalam ejaan dan tanda baca. Urutan Kolom bolehberbeda, dan salah satu atau semua kolom dapat diikutsertakan.

4. Mengaplikasikan Filter

  1. Pilih sebuah sel dalam database.
  2. Dari Menu Data menu, pilih Filter, Advanced Filter. (Dalam Excel 2007, klik tab Data pada Ribbon, kemudian klik Advanced Filter.)
  3. Anda boleh mem filter daftar di data asli atau copy hasilnya ke lokasi lain.
  4. Excel seharusnya otomatis mendeteksi range data. Jika tidak, anda dapat memilih sel-sel yang akan diikutkan dari worksheet.
  5. Pilih range kriteria pada worksheet
  6. Jika anda mengcopykan ke lokasi lain, maka pilih sel awal untuk menempatkan data copy anda
    Catatan: Jika anda memilih copy ke lokasi lain (copy to another location), maka seluruh sel dibawa range yang diextrak akan dihapus jika menggunakan Advanced Filter.
  7. klik OK

October 19, 2008 at 2:20 pm 8 comments

Mengubah Angka Ke Kata – English

Untuk mengubah angka ke kata dalam bahasa Inggris menggunakan fungsi dengan VBA
Yang menggunakan fungsi dapat mendownload file numstowords_vba.zip. yang punya kemampuan sbb:

  • Fungsi NumsToWords(), dengan parameter yang dapat diubah mata uangnya dan format.
  • Contohnya apat dilihat dalam worksheet yang anda download
  • PLUS…Jika anda alergi terhadap VBA..Jangan Takut be!…Click tombolnya dan fungsi tersebut akan otomatis diexport ke workbook yang sedan terbuka atau anda buka tanpa harus mengubah-ubah programnya.
  • Fimgsi ini dibuat oleh Ron Coderre

123.45

Diubah menjadi: One Hundred Twenty Three Dollars and Forty Five Cents

Sintaks nya

=NumsToWords(NumSource, MajorCurrency, MinorCurrency, MajorMinorLink)

Version 1.0.0

Where:
NumSource: The number, or cell reference containing the number, to be converted to text
MajorCurrency: The primary currency name (Optional: Default is “Dollar”)
MinorCurrency: The secondary currency name (Optional: Default is “Cent”)
MajorMinorLink: The word connecting MajorCurrency and MinorCurrency (Optional: Default is “and”)
SkipMinor: True/False flag to ignore the MinorCurrency (Optional: Default is FALSE)

Contoh Penggunaan lainnya:

Usage Umum =NumsToWords(C2)
One Hundred Twenty Three Dollars and Forty Five Cents
Altered Currencies: =NumsToWords(C2,”Goat”,”Kid”)
One Hundred Twenty Three Goats and Forty Five Kids
No MajorMinorLink: =NumsToWords(C2,,,””)
One Hundred Twenty Three Dollars Forty Five Cents
Skip MinorCurrency: =NumsToWords(C2,,,,TRUE)
One Hundred Twenty Three Dollars
Altered MinorCurrency: =NumsToWords(C2,,”Basket”)
One Hundred Twenty Three Dollars and Forty Five Baskets
Altered Major/Minor and Link =NumsToWords(C2,””,”Knot”,”Point”)
One Hundred Twenty Three Point Forty Five Knots

October 19, 2008 at 1:50 pm 2 comments

Fungsi Indirect Excel

Dalam Excel, jika anda ingin menggunakan isi sel sebagai referensi (reference) untuk formula di sel lainnya maka fungsi Indirect adalah jawabannya. Fungsi ini menghasilkan referensi sel berdasarkan pada isi sel yang berbentuk string.

Sintaks Indirect adalah sebagi berikut:

Indirect( string_reference, ref_style )

string_reference adalah referensi sel dalam bentuk teks, misalnya B2, A25, IV23 atau R1C2.

ref_style adalah pilihan bentuk referensi yang berisi nilai TRUE atau FALSE. TRUE jika string_reference akan diintepretasikan sebagai bentuk
referensi
A1. FALSE menunjukkan bahwa string_reference dalam bentuk referensi R1C1. Jika pilihan ini dikosongkan maka, fungsi
Indirect akan menganggap bahwan string_reference sebagai bentuk A1.

Contoh berikut berlaku untuk Excel 2007, Excel 2003, Excel XP, Excel 2000

Let’s take a look at an example:


Dengan menggunakan spreadsheet Excel di atas, maka:

Formula Hasil
=Indirect(“$B$4″) menghasilkan angka 3
=Indirect(“A5″) menghasilkan 10570
=Indirect(“A5″, TRUE) menghasilkan 10570
=Indirect(“R2C3″, FALSE) Menghasilkan $3.50

Contoh penerapan fungsi INDIRECT lebih lanjut adalah dengan menggabungkan dengan fungsi-fungsi lainnya. Tentunya hal ini dapat diperoleh dengan nilai string yang dapat dibuat dengan menggunakan konstanta string dan nilai sel lainnya dalam sebuah formula, dan digabungkan dengan menggunakan operator penyambungan (concatenate) teks (&).   Sebagai contoh, misal anda memiliki formula sederhana sbb:

=SUM(A5:A10)

Yang menjumlahkan nilai dalam range A5:A10.  Akan tetapi, misalnya anda ingin dapat menentukan range baris yang dijumlahkan segera tanpa mengubah formula,  maka dengan fungsi INDIRECT anda dapat melakukan hal ini.  Misalnya anda meletakkan awal baris pada sel B1, dan akhir baris di C1.  Maka, Formula anda menjadi

=SUM(INDIRECT(“A”&B1&”:A”&C1))

Argumen dalam fungsi INDIRECT anda adalah

“A”&B1&”:A”&C1

Jika B1 berisi nilai 5 dan C1 berisi nilai 10, maka argumen tersebut di atas this akan menghasilkanstring “A5:A10″.  Fungsi INDIRECT mengubah string tersebut menjadi range reference sebenarnya, yang selanjutnya akan dimasukkan sebagai referensi bagi fungsi SUM.

Fitur yang sangat berguna lainnya dari fungsi INDIRECT disebabkan oleh perlakuan fungsi ini yang menggunakan argumen string, sehingga anda dapat menggunakan dengan referensi sel yang tidak berubah jika anda ingin menginsert dan mendelete baris.   Biasanya, Excel akan mengubah referensi sel (cell references) pada saat anda meng- Insert atau Men- Delete baris atau kolom, bahkan jika anda menggunakan Referensi Absolut. Jika anda memiliki formula =SUM($A$1:$A$10), kemudian anda meng-Insert baris pada baris ke 5, maka Excel akan mengubah formulanya menjadi =SUM($A$1:$A$11). Hal ini tidak akan terjadi jika anda menggunakan fungsi INDIRECT function sebagai referensi sel/range:

=SUM(INDIRECT(“A1:A10″))

Karena Excel menganggap bahwa “A1:A10″ sebagai text string bukannya sebagai range reference, maka referensi ini tidak akan berubah jika baris atau kolom di-delete atau di-insert.

Fitur ini juga sangat penting jika kita menggunakannya dengan array formulas. Seringkali, formula array menggunakan fungsi ROW(), yaitu fungsi yang menghasilkan deret (array) angka-angka.  Sebagai contoh, formula berikut akan menghasilkan rata-rata dari 10 angka terbesar dalam sebuah range A1:A60 :

=AVERAGE(LARGE(A1:A60,ROW(1:10)))

Akan tetapi. Jika anda menambahkan baris dengan meng-insert diantara baris 1 dan 10, maka Excel akan mengubah formula menjadi

=AVERAGE(LARGE(A1:A60,ROW(1:11)))

Tentunya hasil formula ini menjadi salah karena akan menghasilkan rata-rata 11 angka terbesar. Jika kita menggunakan fungsi ROW() dengan argumen string, maka Excel tidak akan mengubah referensi tersebut, jadi formula akan tetap menghasilkan perhitungan yang benar, walaupun ada baris yang di-insert atau di-delete.

=AVERAGE(LARGE(A1:A60,ROW(INDIRECT(“1:10″))))

Anda dapat pula menggunakan fungsi INDIRECT bersama dengan fungsi ADDRESS. Fungsi ADDRESS menggunakan angka baris dan kolom untuk membuat alamat sel sebagai string. Sebagai contoh, formula =ADDRESS(5,6) menghasilkan $F$5, karena $F$5 adalah baris ke 5 dari kolom ke 6. Anda dapat memasukkan formula ini ke INDIRECT untuk mendapatkan nilai F5. Sebagai contoh, =INDIRECT(ADDRESS(5,6)) .

Memang kelihatannya fungsi-fungsi diatas biasa-biasa saja, namun dalam kenyataannya fungsi diatas akan sangat membantu dalam pengembangan formula yang lebih rumit.

September 21, 2008 at 3:21 pm 2 comments

Extools User Guide

Extools  – Free Excel spreadsheet editing tools user guide

Below is a list of tools and utilities, built with Excel macros (Excel VBA), available in Extools and Extools RX free add-in software for Microsoft Excel (Excel 97, 2000, 2002, 2003). You can even treat this page as a tutorial for Extools and Extools RX Excel add-in.

You will find the tools provided by Extools and Extools RX free Excel spreadsheet add-in software useful for project management, scheduling, budget, accounting – well, just about any simple or complicated spreadsheet task can benefit from the features.

Introduction

ExTools is a free add-in software program for Microsoft Excel (Windows). It is designed to make your work with Excel more productive as it provides many features that make spreadsheet editing much easier.
(more…)

September 16, 2008 at 1:46 pm 1 comment

Add Ins ExTools

Add-Ins Excel Extools adalah program/Add Ins yang dapat membantu kita sehingga bisa bekerja lebih produktif di Excel. Program ini disediakan gratis di website http://www.excel-extool atau download file untuk Excel 97-2003 -extools20 atau Excel 2007 – extools_rxUntuk user guide klik berikut Petunjuk Extools English

(more…)

September 16, 2008 at 1:41 pm Leave a comment

Hitung Data dengan Kriteria Banyak – DCOUNTA

Menghitung jumlah data yang memenuhi dua atau lebih criteria (lihat data) adalah dengan menggunakan fungsi DCOUNTA. Fungsi ini menyediakan parameter yang memungkinkan anda untuk mendefinisikan criteria, dan menggunakan criteria tersebut sebagai dasar menganalisis daftar data. Seperti fungsi-fungsi data dalam Excel, maka DCOUNTA tergantung pada tiga parameters: data range, kolom yang akan digunakan untuk membandingkancolumn, dab criteria range. Untuk menggunakan fungsi ini, set up table criteria pada area worksheet yang tidak anda gunakan. Sebagai contoh, kita dapat mensetup di H1:I2

 

Kemudian, Anda memasukkan rumus di tempat yang anda kehendaki DCOUNTA, dengan rumus sebagai berikut:

=DCOUNTA(A1:E6,1,H1:I2)

Hasilnya adalah jumlah data yang memenuhi criteria sesuai dengan yang ada di H1:I2, dalam hal ini hasilnya adalah 2 data. Catatan: criteria dalam kolom criteria di atas adalah nilai matematika “>50″ dan Nilai English “>50″, kalau ingin lebih besar dan sama dengan maka tanda diganti dengan “>=” dst..

September 6, 2008 at 2:58 pm 2 comments

Menghitung Data yang Cocok Dengan Beberapa Kriteria

Menghitung jumlah data dalam sebuah table yang memenuhi satu syarat cukup mudah; akan tetapi jika anda ingin menghitung jumlah data yang memenuhi dua atau lebih criteria, maka perlu cara yang agak sulit. Dalam tip ini kita mencoba lima cara untuk menghitung jumlah data yang memenuhi beberapa criteria. (untuk Ms Excel 97, Excel 2000, Excel 2002, Excel 2003, Excel 2007.)

Memang tidak umum bagi berbagai pengguna Excel untuk menggunakannya sebagai perhitungan database. Sebagai contoh: misalnya kita memilihi database tentang daftar nilai mahasiswa, atau daftar pelanggan yang dimiliki oleh perusahaan. Dengan contoh ini, kita dapat menghitung misalnya berapa jumlah mahasiswa yang lulus untuk mata pelajaran matematika dan bahasa inggris, atau jumlah pelanggan yang pembeliannya diatas 1 jt dan membeli produk tertentu (criteria yang digunakan lebih besar sama dengan 2)

Excel memeliki sejumlah fungsi yang cukup baikdalam menentukan jumlah data (record) dalam sebuah daftar (table). Akan tetapi, bagaimana cara menggunakannya untuk menyelesaikan soal yang memiliki dua criteria atau lebih tidaklah begitu jelas. Misalnya kita mencoba melihat berapa mahasiswa yang lulus Matematika dan English dari daftar di bawah ini dengan syarat nilai kelulusan adalah 50 keatas untuk matematika dan 50 ke atas untuk English.

Hasilnya adalah 2 karena yang memiliki nilai matematika > 50 adalah 2 orang dan sedangkan English >50 3 orang, tapi yang memenuhi syarat Matematika >50 dan English >50 adalah 2 orang.

Caranya adalah (klik untuk melihat caranya):

  1. Menggunakan DCOUNTA
  2. Menggunakanan Rumus Deret
  3. Menggunakan SUMPRODUCT
  4. Menggunakan Autofilter dan SUBTOTAL
  5. Menggunakan Conditional Sum Wizard

September 6, 2008 at 2:55 pm 4 comments

Older Posts Newer Posts


Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 14 other followers

Blog Stats

  • 77,459 hits

Twitter Updates

  • Reply to @KAI121 tempat membatalkan tiket dimana? 1 month ago
  • @KAI121 kalau sudah reservasi dan membayar bagaimana caranya mengubah orang yang akan berangkat? 2 months ago
  • Ya Allah, jangan Engkau biarkan orang-orang berbuat anarki. Jadikan semua pihak bersabar dan menahan diri #pilpress2014 2 months ago
  • Ya Allah, saya hanya beribadah kepada-Mu, dan hanya memohon pertolongan kepada-Mu. Jadikanlah sejuk damai di Indonesia setelah Pilpres ini. 2 months ago

Iklan


Follow

Get every new post delivered to your Inbox.