Membuat fungsi kustom di Excel

Catatan: Kami ingin secepatnya menyediakan konten bantuan terbaru dalam bahasa Anda. Halaman ini diterjemahkan menggunakan mesin dan mungkin terdapat kesalahan tata bahasa atau masalah keakuratan. Kami bertujuan menyediakan konten yang bermanfaat untuk Anda. Dapatkah Anda memberi tahu kami apakah informasi ini bermanfaat untuk Anda di bagian bawah halaman ini? Berikut artikel dalam bahasa Inggris untuk referensi.

Meskipun Excel menyertakan banyak fungsi lembar kerja bawaan, kemungkinan yang tidak memiliki fungsi untuk setiap tipe perhitungan yang Anda lakukan. Desainer Excel mungkin tidak dapat mengantisipasi setiap pengguna perhitungan kebutuhan. Sebagai gantinya, Excel memberi Anda kemampuan untuk membuat fungsi kustom, yang dijelaskan dalam artikel ini.

Apakah Anda mencari informasi tentang cara membuat fungsi kustom Javascript yang bisa Anda jalankan di Excel untuk Windows, Excel untuk Mac, atau Excel Online? Jika Anda, lihat artikel fungsi kustom Excel gambaran umum.

Fungsi kustom, seperti makro, menggunakan Visual Basic for Applications (VBA) bahasa pemrograman. Mereka berbeda dari makro dalam dua cara yang signifikan. Pertama, mereka menggunakan fungsi prosedur bukan Sub prosedur. Yaitu, program ini memulai dengan pernyataan fungsi dan bukan pernyataan Sub dan end dengan Fungsi akhir bukan End Sub. Kedua, mereka melakukan perhitungan daripada mengambil tindakan. Jenis pernyataan, seperti pernyataan yang memilih dan memformat rentang, dikecualikan dari fungsi kustom tertentu. Di artikel ini, Anda akan mempelajari cara membuat dan menggunakan fungsi kustom. Untuk membuat makro dan fungsi, Anda bekerja dengan Visual Basic Editor (VBE), yang terbuka di jendela baru terpisah dari Excel.

Anggap perusahaan Anda menawarkan diskon kuantitas 10 persen di penjualan produk, disediakan urutan adalah untuk lebih dari 100 unit. Di paragraf berikut, kami akan menunjukkan fungsi untuk menghitung diskon ini.

Contoh di bawah ini memperlihatkan formulir pesanan yang mencantumkan setiap item, jumlah, harga, diskon (jika ada), dan harga diperpanjang dihasilkan.

Contoh formulir pesanan tanpa fungsi kustom

Untuk membuat fungsi kustom diskon di buku kerja ini, ikuti langkah-langkah ini:

  1. Tekan Alt + F11 untuk membuka Visual Basic Editor (di Mac, tekan FN + ALT + F11), lalu klik sisipkan > modul. Jendela modul baru akan muncul di sisi kanan dari Visual Basic Editor.

  2. Salin dan tempelkan kode berikut ke modul baru.

    Function DISCOUNT(quantity, price)
       If quantity >=100 Then
         DISCOUNT = quantity * price * 0.1
       Else
         DISCOUNT = 0
       End If
     
     DISCOUNT = Application.Round(Discount, 2)
    End Function
    

Catatan: Untuk membuat kode Anda lebih mudah dibaca, Anda bisa menggunakan tombol Tab untuk mengindentasi baris. Indentasi untuk manfaat Anda hanya, dan bersifat opsional, seperti kode akan dijalankan dengan atau tanpa itu. Setelah Anda mengetikkan baris yang terindentasi, Editor Visual Basic menganggap baris berikutnya Anda akan demikian pula berindentasi. Untuk berpindah (yaitu, ke kiri) satu tab karakter, tekan Shift + Tab.

Sekarang Anda sudah siap untuk menggunakan fungsi baru ini diskon. Tutup Visual Basic Editor, pilih sel G7 dan ketik yang berikut ini:

=DISCOUNT(D7,E7)

Excel menghitung 10 persen diskon pada 200 unit di $47,50 per unit dan mengembalikan $950.00.

Di baris pertama kode VBA, fungsi DISCOUNT(quantity, price), yang ditunjukkan dalam fungsi diskon memerlukan dua argumen, kuantitas dan harga. Saat Anda memanggil fungsi di dalam sel lembar kerja, Anda harus menyertakan dua argumen tersebut. Dalam rumus = DISCOUNT(D7,E7), D7 argumen kuantitas , dan E7 argumen harga . Sekarang Anda bisa menyalin rumus diskon ke G8:G13 untuk mendapatkan hasil yang diperlihatkan di bawah ini.

Mari kita lihat cara Excel menginterpretasikan prosedur fungsi ini. Saat Anda menekan Enter, Excel akan mencari nama diskon dalam buku kerja saat ini dan menemukan bahwa itu adalah fungsi kustom dalam modul VBA. Nama argumen yang dimasukkan dalam tanda kurung, kuantitas dan harga, adalah tempat penampung untuk nilai yang didasarkan perhitungan diskon.

Contoh formulir pesanan dengan fungsi kustom

Jika pernyataan dalam blok berikut kode meneliti argumen kuantitas dan menentukan apakah jumlah item terjual lebih besar dari atau sama dengan 100:

If quantity >= 100 Then
 DISCOUNT = quantity * price * 0.1
Else
 DISCOUNT = 0
End If

Jika jumlah item terjual lebih besar dari atau sama dengan 100, VBA menjalankan pernyataan berikut, yang mengalikan nilai jumlah nilai harga dan lalu mengalikan hasilnya dengan 0,1:

Diskon = Jumlah * harga * 0,1

Hasil disimpan sebagai variabel diskon. Pernyataan VBA yang menyimpan nilai dalam variabel disebut pernyataan tugas , karena mengevaluasi ekspresi di sisi kanan tanda sama dengan dan menetapkan hasilnya ke nama variabel di sebelah kiri. Karena variabel diskon memiliki nama yang sama sebagai prosedur fungsi, nilai yang disimpan dalam variabel dikembalikan ke rumus lembar kerja yang disebut fungsi diskon.

Jika kuantitas kurang dari 100, VBA menjalankan pernyataan berikut ini:

Diskon = 0

Akhirnya, pernyataan berikut membulatkan nilai yang ditetapkan ke variabel diskon ke dua tempat desimal:

Diskon = Application.Round (diskon, 2)

VBA memiliki tidak ada fungsi ROUND, namun tidak Excel. Oleh karena itu, untuk menggunakan ROUND dalam pernyataan ini, Anda mengetahui VBA untuk mencari metode Round (fungsi) di objek Application (Excel). Anda lakukan dengan menambahkan kata aplikasi sebelum kata tersebut Round. Gunakan sintaks ini kapan pun Anda memerlukannya untuk mengakses fungsi Excel dari modul VBA.

Fungsi kustom harus mulai dengan pernyataan fungsi dan diakhiri dengan pernyataan fungsi akhir. Selain nama fungsi, pernyataan fungsi biasanya menentukan satu atau beberapa argumen. Namun, Anda dapat membuat fungsi dengan argumen tidak ada. Excel menyertakan beberapa fungsi bawaan — RAND dan saat ini, misalnya — yang tidak menggunakan argumen.

Mengikuti Pernyataan fungsi, prosedur fungsi menyertakan satu atau beberapa pernyataan VBA yang membuat keputusan dan melakukan perhitungan menggunakan argumen yang diberikan ke fungsi. Akhirnya, tempat di prosedur fungsi, Anda harus menyertakan pernyataan yang menetapkan nilai variabel dengan nama yang sama sebagai fungsi. Nilai ini akan dikembalikan ke rumus yang panggilan fungsi.

Jumlah kata kunci VBA Anda bisa menggunakan fungsi kustom lebih kecil dari angka yang bisa Anda gunakan di makro. Fungsi kustom tidak diperbolehkan untuk melakukan apa pun selain return nilai dengan rumus dalam lembar kerja, atau untuk ekspresi yang digunakan dalam makro VBA atau fungsi yang lain. Misalnya, fungsi kustom tidak bisa mengubah ukuran, mengedit rumus di sel atau mengubah font, warna, atau pola opsi untuk teks dalam sel. Jika Anda menyertakan "tindakan" kode semacam ini dalam prosedur fungsi, fungsi akan mengembalikan #VALUE! .

Tindakan satu prosedur fungsi bisa lakukan (dari melakukan penghitungan) menampilkan kotak dialog. Anda bisa menggunakan pernyataan InputBox dalam fungsi kustom untuk mendapatkan input dari pengguna mengeksekusi fungsi. Anda bisa menggunakan pernyataan MsgBox sebagai berarti satunya informasi kepada pengguna. Anda juga dapat menggunakan kotak dialog kustom, atau UserForms, tapi itu adalah subjek luar cakupan pengenalan ini.

Bahkan sederhana makro dan fungsi kustom sulit dibaca. Anda dapat membuatnya lebih mudah dipahami dengan mengetik teks penjelasan di formulir komentar. Anda menambahkan komentar dengan cara mengawali teks penjelasan dengan tanda kutip. Misalnya, contoh berikut ini memperlihatkan fungsi diskon dengan komentar. Menambahkan komentar seperti ini memudahkan Anda atau orang lain untuk mempertahankan kode VBA Anda berjalannya waktu. Jika Anda perlu membuat perubahan ke bagian kode di masa mendatang, Anda akan memiliki waktu yang lebih mudah memahami apa yang Anda lakukan pada awalnya.

Contoh fungsi VBA dengan komentar

Tanda kutip memberitahu Excel untuk abaikan semua untuk kanan pada baris yang sama, sehingga Anda bisa membuat komentar baik di baris dengan konstanta itu sendiri atau pada sisi kanan baris yang berisi VBA kode. Anda mungkin mulai blok relatif panjang kode dengan komentar yang menjelaskan tujuan keseluruhan dan lalu menggunakan komentar sebaris ke dokumen individual pernyataan.

Cara lain untuk dokumen makro dan fungsi kustom Anda adalah untuk memberi nama deskriptif. Misalnya, bukan nama makro label, Anda bisa Namai MonthLabels untuk menjelaskan lebih khusus tujuan makro menyajikan. Menggunakan nama yang deskriptif untuk makro dan fungsi kustom berguna khususnya saat Anda telah membuat banyak prosedur, terutama jika Anda membuat prosedur yang memiliki tujuan yang serupa tapi tidak sama.

Bagaimana Anda dokumen makro dan fungsi kustom adalah masalah preferensi pribadi. Apa itu penting adalah mengadopsi beberapa metode dokumentasi, dan menggunakannya secara konsisten.

Untuk menggunakan fungsi kustom, buku kerja yang berisi modul yang Anda buat dalam fungsi harus terbuka. Jika buku kerja tersebut tidak terbuka, Anda mendapatkan #NAME? kesalahan ketika Anda mencoba menggunakan fungsi. Jika Anda referensi fungsi di buku kerja berbeda, Anda harus Awali nama fungsi dengan nama buku kerja di mana fungsi berada. Misalnya, jika Anda membuat fungsi disebut diskon dalam buku kerja yang disebut Personal.xlsb dan Anda melakukan panggilan fungsi tersebut dari buku kerja yang lain, Anda harus mengetik =personal.xlsb!discount(), bukan sekadar =discount().

Anda bisa menyimpan sendiri beberapa tekanan tombol (dan kemungkinan kesalahan mengetik) dengan memilih fungsi kustom Anda dari kotak dialog sisipkan fungsi. Fungsi kustom Anda muncul dalam kategori ditentukan pengguna:

kotak dialog sisipkan fungsi

Cara yang lebih mudah untuk membuat fungsi kustom Anda tersedia di semua waktu untuk menyimpannya di buku kerja terpisah dan lalu menyimpan buku kerja tersebut sebagai add-in. Anda dapat lalu membuat add-in tersedia setiap kali Anda menjalankan Excel. Berikut adalah cara untuk melakukan hal ini:

  1. Setelah Anda membuat fungsi yang Anda perlukan, klik File > Simpan sebagai.

    Di Excel 2007, klik Tombol Microsoft Office, dan klik Simpan sebagai

  2. Dalam kotak dialog Simpan sebagai , buka daftar turun bawah Simpan sebagai tipe , dan pilih Excel Add-In. Menyimpan buku kerja di bawah nama dikenali, seperti MyFunctions, di folder Add-in . Kotak dialog Simpan sebagai akan mengusulkan folder tersebut, jadi Anda perlu melakukan menerima lokasi default.

  3. Setelah menyimpan buku kerja, klik File > Opsi Excel.

    Di Excel 2007, klik Tombol Microsoft Office, dan klik Opsi Excel.

  4. Dalam kotak dialog Opsi Excel , klik kategori Add-in .

  5. Di daftar turun bawah Kelola , pilih Add-in Excel. Lalu klik tombol buka .

  6. Dalam kotak dialog Add-in , pilih kotak centang di samping nama yang Anda gunakan untuk menyimpan buku kerja Anda, seperti yang diperlihatkan di bawah ini.

    kotak dialog add-in

  1. Setelah Anda membuat fungsi yang Anda perlukan, klik File > Simpan sebagai.

  2. Dalam kotak dialog Simpan sebagai , buka daftar turun bawah Simpan sebagai tipe , dan pilih Excel Add-In. Menyimpan buku kerja di bawah nama dikenali, seperti MyFunctions.

  3. Setelah menyimpan buku kerja, klik alat > Add-in Excel.

  4. Dalam kotak dialog Add-in , pilih tombol Telusuri untuk menemukan add-in, klik buka, lalu centang kotak di samping Add-In dalam kotak Add-in tersedia .

Setelah Anda mengikuti langkah-langkah ini, fungsi kustom Anda akan tersedia setiap kali Anda menjalankan Excel. Jika Anda ingin menambahkan ke pustaka fungsi Anda, kembali ke Visual Basic Editor. Jika Anda melihat di penjelajah proyek Visual Basic Editor di bawah judul VBAProject, Anda akan melihat modul yang bernama setelah Anda menambahkan dalam file. Add-in akan memiliki ekstensi .xlam.

modul bernama di vbe

Mengklik ganda modul di penjelajah proyek menyebabkan Editor Visual Basic untuk menampilkan kode fungsi Anda. Untuk menambahkan fungsi baru, letakkan titik penyisipan Anda setelah pernyataan akhir fungsi yang berakhir, fungsi terakhir di jendela kode, dan mulailah mengetik. Anda dapat membuat seperti banyak fungsi saat Anda perlu menggunakan cara pemuatan ini, dan mereka akan selalu tersedia dalam kategori ditentukan pengguna dalam kotak dialog Sisipkan fungsi .

Konten ini awalnya dibuat oleh Mark Dodge dan Craig Stinson sebagai bagian dari buku mereka Microsoft Office Excel 2007 dalam dan luar. Itu karena telah diperbarui untuk menerapkan ke versi yang lebih baru Excel juga.

Perlu bantuan lainnya?

Anda selalu dapat bertanya kepada pakar di Komunitas Teknologi Excel, mendapatkan dukungan di Komunitas Jawaban, atau menyarankan fitur maupun fitur baru di Suara Pengguna Excel.

Kembangkan keterampilan Office Anda
Jelajahi pelatihan
Dapatkan fitur baru terlebih dahulu
Gabung ke Office Insiders

Apakah informasi ini bermanfaat?

Terima kasih atas umpan balik Anda!

Terima kasih atas umpan balik Anda! Sepertinya menghubungkan Anda ke salah satu agen dukungan Office kami akan sangat membantu.

×