Posts tagged ‘Excel Function’

Custom Function untuk Excel

Berikut adalah contoh custom function untuk excel.

untuk melihat semua fungsi dan contoh pemakaiannya  download file excel custom function di sini :

Fungsi terdiri dari :

(more…)

March 29, 2009 at 4:27 pm Leave a comment

FUNGSI EXCEL HITUNG KATA

Kadang kita perlu mengetahui berapa jumlah kata yang kita tulis dalam Workbooks atau Worksheet Ms Excel. Dalam Excel tidak ada fungsi khusus yang dapat digunakan untuk menghitung jumlah kata. Oleh karena itu perlu dibuat fungsi untuk menghitung kata dengan menggunakan VBA. However, you can count the number of words with a simple VBA macro.

Sub Procedure di bawah ini akan menghitung berapa jumlah kata yang ada di dalam ActiveSheet (sheet yang aktif) dan menampilkannya dalam Kotak Pesan (Message Box). Contoh file dapat di download adalah (Download file : hitung-kata.zip)

(more…)

January 17, 2009 at 4:53 am 2 comments

Keputusan Jumlah Investasi dengan fungsi PV Excel

Misal Anda harus memutuskan jumlah uang yang akan investasikan. Untuk memecahkan masalah ini, Anda dapat menggunakan fungsi PV dalam Excel, yang akan menghasilkan nilai investasi. Yaiut jumlah total dari  serangkaian pembayaran yang terjadi beberapa kali dengan nilai sekarang (present value). Berikut adalah sintaks yang digunakan untuk Fungsi PV .
PV (rate, NPER, PMT, FV, type)
Rate
: Suku bunga per periode.
NPER: Jumlah total pembayaran dalam periode tahunan.
PMT: Pembayaran dilakukan setiap periode, yang merupakan nilai konstan.
FV: Nilai Masa depan (Future Value) adalah jumlah yang Anda inginkan setelah pembayaran terakhir dilakukan.
type: anaka yang menunjukkan bahwa kapanpembayaran yang jatuh tempo. 0 atau diabaikan atau menunjukkan akhir periode, dan 1 menunjukkan awal periode.

Untuk menentukan berapa banyak untuk berinvestasi:

1. Dalam sel C1 masukkan return perkiraan per tahun.
2. Dalam sel C2 masukkan jumlah periode dalam tahun.
3. Masukkan suku bunga di sel C3.
4. Hitung jumlah maksimum investasi dalam sel C4 dengan rumus sebagai berikut: = PV (C3, C2, C1).
5. Tekan Enter.

Excel PV-function.xls

October 23, 2008 at 3:53 am 2 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

Bermain dengan SUMIF

Dengan fungsi SUMIF kita dapat menghasilkan penjumlahan yang memenuhi kriteria tertentu.

Sintaks : SUMIF(range,criteria,sum_range)

Sebagai contoh:

  1. Anda ingin menjumlahkan semua data yang ada di sel A1:A20 dengan kriteria bahwa data harus lebih besar dan sama dengan 10 (>=10), maka anda dapat menggunakan =SUMIF(A1:A20,”>=10″) .
  2. Atau jika anda ingin menjumlahkan data tersebut diatas dengan kriteria nilainya berada di antara 5 dan 10. Maka kriterianya menjadi jumlahkan data yang lebih besar dan sama dengan 5 (>=5) dikurangi jumlah seluruh data yang lebih besar dari 10 ( > 10) dengan rumus sebagai berikut =SUMIF(A1:A20,”>=5″)-SUMIF(A1:A20,”>10″)

Catatan: Cara lain untuk item 2 adalah : =SUMPRODUCT((A1:A20>=5)*(A1:A20<=10)*A1:A20)

Okey dua dulu ya contohnya… yang lainnya menyusul … kalau ada yang mau nyumbang silahkan.

October 12, 2008 at 3:48 pm 6 comments

Menjumlahkan 5 Angka terbesar

Untuk menjumlahkan 5 angka terbesar sebuah data yang terletak dikolom tertentu maka kita dapat menggunakan gabungan antara fungsi SUM dengan fungsi LARGE. Sebagai contoh lihat tabel dibawah berikut ini. Rumusyang digunakan adalah

=SUM(LARGE(A:A,{1,2,3,4,5}))

Yang artinya jumlahkan data di kolom A hanya 5 angka terbesar saja.


October 12, 2008 at 3:25 pm Leave a comment

Cara membulatkan ke angka .5 dan .25

Kadang kala anda perlu untuk membulatkan suatu hasil tidak mengikuti ketentuan matematika umum. Dengan fungsi pembulatan Round(angka,desimal) maka hasil yang didapatkan adalah seperti dalam contoh baris 3 s/d 4. Jika anda ingin mendapatkan pembulatan lainnya maka contoh paa baris 6 s/d 14 dapat dipergunakan sebagai acuan. Untuk pembulatan dengan angka 5 maka anda perlu membagi angka asli dengan angka 5 kemudian dibulatkan ke 0 desimal dan dikalikan lagi dengan angka 5. Demikian pula jika anda ingin mendapatkan pembulatan ke angka 0.5 maupun ke angka 0.25.

October 12, 2008 at 3:08 pm 6 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

Menuliskan kalimat dengan rumus di Excel

Kadang kita mau menuliskan hasil perhitungan ms excel yang kita buat dalam kalimat.

Sebagai contoh kita menghitung rata-rata waktu pelayanan pelanggan dan simpangan bakunya dengan data sebagai berikut:

Untuk menampilkan dalam kalimat misalnya “

“Rata-rata waktu pelayanan pelanggan adalah 39.6 dengan Simpangan Baku/Standar Deviasi sebesar 6.77″

Maka kita dapat menggunakan rumus sebagai berikut:

=”Rata-rata waktu pelayanan pelanggan adalah “&B12&” dengan Simpangan Baku/Standar Deviasi sebesar ” &TEXT(B13,”#.##”)

  • Artinya sel D2 sama dengan “Rata-rata waktu pelayanan pelanggan adalah ” + sel B12 + ” dengan Simpangan Baku/Standar Deviasi sebesar ” + jadikan teks sel B13 dengan format angka dan dua decimal.

Atau langsung dihitung dalam kalimat:

=”Rata-rata waktu pelayanan pelanggan adalah “&AVERAGE(B2:B11)&” dengan Simpangan Baku/Standar Deviasi sebesar ” &TEXT(STDEV(B2:B11),”#.##”)

  • Artinya sel D2 sama dengan “Rata-rata waktu pelayanan pelanggan adalah ” + rata-rata sel B2 s/d B11 + ” dengan Simpangan Baku/Standar Deviasi sebesar ” + jadikan teks hasil perhitungan Simpangan Baku sel B2 s/d B11 dengan format angka dan dua decimal.

Selamat mencoba !!!

August 24, 2008 at 3:16 pm 3 comments


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

Join 13 other followers

Blog Stats

  • 76,008 hits

Twitter Updates

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

Iklan


Follow

Get every new post delivered to your Inbox.