Fungsi Excel ; Average If

Sudah lama nggak posting,

nggak tau kenapa hari ini suasana hati mampu menggerakkan kedua jari untuk menulis.

Beberapa waktu lalu seorang rekan di kantor bertanya ada nggak formula average tetapi dengan kriteria.

Kali ini saya akan mencoba membagikan jawabannya..

Fungsi average if digunakan untuk mengetahui nilai rata dari suatu range dengan kriteria yang telah kita tentukan

Langkahnya adalah sebagai berikut:

bukalah visual basic editor pada microsoft excel anda dan ketiklah module berikut:

Function averageif(range, kriteria, ave_range)
iconditioncolno = range.Column
inumbercolno = ave_range.Column


For lrowno = 1 To range.Rows.Count
vcellvalue = range.Parent.Cells(range.Row + lrowno - 1, inumberscolno.Value)
If IsNumeric(vcellvalue) = True Then
If (range.Parent.Cells(range.Row + lrowno - 1, iconditioncolno).Value = kriteria) Then
lmatch = lmatch + 1
sngaverage = sngaverage + range.Parent.Cells(range.Row + lrowno - 1, inumbercolno).Value
End If
End If

If sngaverage <> 0 And IsEmpty(vcellvalue) = True Then Exit For
End If
Next lrowno
averageif = sngaverage / lmatch

End Function


Save file excel dan kembalilah ke worksheet anda

anda sudah bisa menggunkan fungsi =averageif pada worksheet anda.


Selamat mencoba...

Bagaimana Membuat dan Menjalankan Macro Excel? (1)

Sebenarnya terdapat dua cara untuk membuat macro di Microsoft Excel, yang pertama adalah dengan merekam aktivitas yang telah kita jalankan pada worksheet, sedangkan yang kedua adalah dengan membuat sendiri perintah macro pada pada Visual Basic Editor yang telah disediakan pada Microsoft Excel anda. Tapi akan dijelaskan terlebih dahulu toolbar yang akan digunakan dalam menjalankan atau membuat macro.


Toolbar pada gambar diatas adalah toolbar visual basic. Toolbar ini dapat anda munculkan dengan mencentang pada kotak opsi visual basic ( Tools -> Customize -> Toolbar -> Visual Basic ).

Keterangan gambar :

1. Run Macro, untuk menjalankan macro yang telah direkam atau dibuat. Jika anda mengklik tombol ini maka akan muncul jendela listing macro.
2. Record Macro, untuk merekam aktivitas yang akan anda lakukan pada worksheet anda kedalam sebuah prosedur program atau macro.
3. Visual Basic Editor, berfungsi untuk membuka jendela Visual Basic Editor yang akan digunakan untuk melihat listing program yang telah dibuat oleh Record Macro atau untuk membuat macro dengan listing program yang akan dibuat sendiri.
4. Control Tool Box, berisi bermacam-macam Control Tool yang dapat diguanakan untuk membuat form, membuat combo box, input box, text box dan lain sebagainya.
5. Design View or Exit Design View, opsi untuk memilih apakah kita akan bekerja dengan macro atau tidak. Apabila kita telah selesai mengedit listing program pada Jendela Visual Basic Editor, maka klik Tombol Exit Design View ini untuk mulai menjalankan macro.

Merekam Macro
Cara menggunakan fitur macro yang paling sederhana adalah dengan merekam aktivitas yang kita lakukan. Perhatikan contoh kecil berikut:

1. Pertama-tama masukklah ke menu Tools -> Macro -> Record Macro. Lalu akan muncul jendela Record Macro. Pada isian Macro Name anda isikan nama macro sesuai yang anda inginkan sesuai default maka akan muncul nama Macro1. Pada isian Shortcut anda dapat isikan misalnya "t" (tanpa tanda kutip). Maksudnya shortcut untuk menjalankan Macro1 nantinya adalah CTRL + t. Lalu Tekan OK.

2. Pada worksheet anda buatlah seperti pada gambar dibawah ini:




3. Apabila anda telah selesai membuat seperti pada gambar diatas maka masuklah pada menu Tools -> Macro -> Stop Recording.

4. Sekarang hapus yang telah anda tulis tadi, sampai benar2 bersih lalu tekan tombol Run Macro maka akan terlihat list dari macro yang ada, yaitu Macro1. Lalu klik Run. Excel akan melakukan apa yang telah anda rekam sebelumnya.

Bersambung.

Apa itu Macro Excel?

Macro adalah sederetan fungsi dan perintah program yang disimpan dalam menu Visual Basic. Microsoft Excel menyediakan fitur untuk merekam aktivitas yang dilakukan oleh penggunanya melalui tools -> macro -> record new macro. Record new macro berguna untuk merekam aktivitas yang dilakukan dan merubahnya menjadi baris-baris program.

Namun macro juga dapat digunakan oleh user untuk membuat perintah atau procedure melalu bahasa VBA atau Visual Basic for Application yang cukup populer, sederhana dan mudah untuk dipelajari. Semakin besar pemahaman anda tentang bahasa VBA maka semakin banyak dan canggih pula prosedur atau perintah-perintah yang dapat anda buat untuk memudahkan perkerjaan anda dengan Microsoft Excel.

Contoh sederhana dari fungsi atau kegunaan macro adalah apabila anda melakukan pekerjaan yang banyak tapi monoton. Dari pada anda membuang-buang waktu dengan mengerjakan pekerjaan yang monoton secara manul, kenapa anda tidak merekamnya saja dengan macro kemudian melakukan sedikit modifikasi. Pekerjaan yang sebelumnya dikerjakan berjam-jam dapat selesai hanya dengan sekali klik. Contoh lain adalah apabila anda ingin membuat fungsi yang belum tersedia pada fungsi standard yang disediakan oleh excel, maka anda dapat membuat fungsi yang anda inginkan melalui Macro berupa User Defined Function (UDF).

Untuk dapat menggunakan macro, anda harus terlebih dahulu melakukan setting terhadap macro security level pada Microsoft Excel anda. Caranya adalah melalui tools -> macro -> security lalu set security level pada level Medium. Jangan gunakan low apabila anda sering menerima file dalam bentuk excel karena kemungkinan anda mendapat file excel yang mengandung virus macro menjadi sangat tinggi. Dengan level medium anda dapat menentukan apakah anda akan meng-Enable atau Disable macro yang terdapat pada file excel yang akan anda buka. Namun jika macro security level anda di set pada level Low maka anda tidak akan mendapat pemberitahuan bahwa file yang akan anda buka berisi macro atau tidak.



Pada posting berikutnya akan dijelaskan bagaimana membuat dan menjalankan macro pada File Microsoft Excel anda.

Menterjemahkan Angka Menjadi Kalimat Dengan Macro

Jika kita ingin membuat kuitansi atau bukti potong pajak, maka umumnya kita harus menuliskan angka rupiah pada line terbilang. Jika kuitansi atau bukti potong yang anda buat hanya 1 atau beberapa lembar saja per hari mungkin tidak sulit untuk membuatnya, tapi bagaimana jika anda harus membuat puluhan bahkan ratusan lembar bukti semacam ini setiap hari atau beberapa hari sekali. Tentu sangat tidak efisien bila kita menuliskan kalimat terbilang seperti ini secara manual. Hal ini juga sangat rentan terhadap kesalahan.

Ada satu trik yang sangat bagus untuk mengatasi hal ini yaitu dengan fungsi terbilang. Simak caranya sebagai berikut:

Pertama, masuklah ke menu visual basic editor pada microsoft excel anda dan ketiklah fungsi berikut (Anda dapat meng-Copy Paste dari artikel ini).

Function Terbilang(ByVal n As Currency) As String

Dim Satuan As Variant

Satuan = Array("", "Satu", "Dua", _
"Tiga","Empat", "Lima", "Enam", "Tujuh", _
"Delapan", "Sembilan", "Sepuluh", "Sebelas")

Select Case n

Case 0 To 11
Terbilang = " " + Satuan(Fix(n))

Case 12 To 19
Terbilang = Terbilang(n Mod 10) + " Belas"

Case 20 To 99
Terbilang = Terbilang(Fix(n / 10)) + _
" Puluh" + Terbilang(n Mod 10)

Case 100 To 199
Terbilang = " Seratus" + Terbilang(n - 100)

Case 200 To 999
Terbilang = Terbilang(Fix(n / 100)) + " Ratus" + _
Terbilang(n Mod 100)

Case 1000 To 1999
Terbilang = " Seribu" + Terbilang(n - 1000)

Case 2000 To 999999
Terbilang = Terbilang(Fix(n / 1000)) + " Ribu" + _
Terbilang(n Mod 1000)

Case 1000000 To 999999999
Terbilang = Terbilang(Fix(n / 1000000)) + _
" Juta" + Terbilang(n Mod 1000000)

Case Else
Terbilang = Terbilang(Fix(n / 1000000000)) + _
" Milyar" + Terbilang(n Mod 1000000000)

End Select

End Function

Lalu kemudian anda dapat mencoba fungsi yang telah anda buat ini di worksheet anda. Coba buatlah sheet seperti dibawah ini dalam worksheet yang sama dengan worksheet saat anda membuat macro sebelumnya.


pada cell B3 anda tuliskan rumus =terbilang(B2) dan anda dapat tuliskan angka yang ingin diterjemahkan pada cell B2.


hasil dari fungsi itu adalah kalimat dari angka yang anda masukkan pada cell B2.

Selamat mencoba.

Excel Tips: Fungsi Ekstrak Huruf Dengan Macro Excel

Pada posting sebelumnya sudah diulas bagaimana mengekstrak angka dari suatu teks di file excel dengan menggunakan macro. Kali ini dengan teknik yang hampir sama, akan ditunjukkan bagaimana jika huruf yang ingin kita ekstrak dari teks yang memiliki variasi angka dan huruf.

Yang anda perlu lakukan pertama-tama adalah menuliskan script VBA di bawah pada Visual Basic Editor pada Excel Anda:

Function EkstrakHuruf(teks)

For lenstr = 1 To Len(teks)

Select Case Asc(Mid(teks, lenstr, 1))

Case 65 To 90

EkstrakHuruf = EkstrakHuruf & Mid(teks, lenstr, 1)

Case 97 To 122

EkstrakHuruf = EkstrakHuruf & Mid(teks, lenstr, 1)

End Select

Next


End Function


Setelah itu anda tinggal menggunakan formula = EkstrakHuruf(Cell Teks) pada cell dimana anda ingin memperoleh hasil ekstrak huruf dari sebuah teks (seperti contoh gambar diatas).
Lalu lihat hasilnya.. ya anda telah berhasil mengekstrak huruf dari variasi angka dan huruf.

Mudah kan. Jika kita mengerjakan perkerjaan dengan excel tanpa bantuan macro dan formula (Kerja Manual) bisa berjam-jam. Tapi jika kita menguasai Macro, dan Trik-trik Excel Lainnya pekerjaan berjam-jam itu dapat kita selesaikan hanya dalam hitungan menit. Menyenangkan bukan? Selamat Mencoba

Fungsi Ekstrak Angka dengan Macro Excel

Ini salah satu fungsi VBA kesukaan saya, dan saya yakin bagi anda yang sering bergelut dengan angka fungsi ini pasti juga akan anda sukai. Dengan fungsi ini anda dapat meng-ekstrak angka dari kata yang terdiri variasi huruf dan angka. Anda cukup membuat sebuah module pada visual basic editor microsoft excel anda. Lalu anda dapat menuliskan kode dibawah ini:

Function ekstrakangka(teks)

For lenstr = 1 To Len(teks)

Select Case Asc(Mid(teks, lenstr, 1))
Case 48 To 57
ekstrakangka = ekstrakangka & Mid(teks, lenstr, 1)
End Select

Next

End Function

setelah itu anda dapat membuat formula dengan fungsi =ekstrakangka(cell)

lalu lihat hasilnya, angka sudah ter-ekstrak.

bagaimana sebaliknya jika huruf yang ingin anda ekstrak?
itu akan ada di posting berikutnya.