microfo

Microsoft Excel

Belajar Dasar-Dasar Microsoft Excel

Belajar Dasar-Dasar Microsoft Excel. Microsoft excel atau microsoft office excel adalah sebuah program aplikasi lembar kerja spreadsheet(Lembar Kerja Elektronik. Aplikasi ini menampilkan lembaran tabel yang dibuat dan didistribusikan oleh Microsoft Corporation untuk sistem operasi Windows dan Mac OS. Cara menggunakannya pun mudah sehingga Microsoft Excel menjadi salah satu program komputer yang populer digunakan oleh pengguna komputer sampai saat ini.

Hal- hal yang harus kita pelajari dari Microsoft Excel adalah dasar-dasarnya terlebih dahulu. Dan hal yang pertama adalah mengenal rumus-rumus yang digunakan di microsoft excel 2007. Berikut ini adalah rumus sederhana tentang cara menggunakan fungsi penambahan, pengurangan, kali dan bagi. Cara ini juga bisa digunakan di microsoft office Excel 2003. Untuk lebih jelasnya. Ikuti langkah-langkah dibawah ini.

    Belajar Dasar-Dasar Microsoft Excel

·                     Buka Microsoft Excel. Start > All Programs > Microsoft Office > Microsoft Office Excel. 

·                     Ikuti langkah-langkah dibawah ini. Biar lebih mudah disamakan saja.

·                     Pertama kita buat Rumus Tambah. klik kotak 6B dan tulis rumus=SUM(3B+4B) di Fx lalu Enter

4

·                     Rumus =SUM(3B+4B) itu maksudnya Colom No 3 Row B ditambah Dengan Colom No 4 Row B. 6B itu Kotak Hasil 1+ 5 = 6 . Hasil bisa kita tempatkan dikotak mana saja, asal kotaknya masih kosong belum ada rumusnya.

·                     Untuk Rumus kurang, klik Kotak 6C. 

1.            Masukan Rumus Kurang di Fx=SUM(3C-4C)

2.            Rumus Bagi di Kotak 6D =SUM(3D/4D)

3.            Rumus Kali di Kotak 6E =SUM(3E*4E)

·                     Maka Hasil keseluruhan akan seperti gambar dibawah ini.

·                     Jika pada saat Enter Rumus ada Tulisan Seperi dibawah ini. Klik ok, tulisan itu keluar karena rumus yang digunakan adalah rumus manual yang berbeda dengan rumus asal dari excel tapi fungsinya sama untuk +X-:

5

Itulah sedikit cara tentang belajar dasar-dasar microsoft excel 2007.

TUTORIAL RUMUS EXCEL VLOOKUP UNTUK PEMULA

Untuk Anda yang ingin mengenali lebih dalam Excel dan penggunaan rumusnya, maka Anda sedang membaca artikel yang tepat. Di dalam artikel ini akan dijelaskan salah satu rumus atau fungsi Excel yang paling berguna, yakni rumus VLOOKUP. Pada saat yang sama, itu salah satu fungsi yang paling rumit dan sedikit orang yang memahami secara detail tentang fungsi ini.

Dalam tutorial VLOOKUP ini, kita akan mencoba memahami dan menjelaskan dasar-dasar dalam penggunaan bahasa pemograman yang sangat sederhana untuk membuat kurva pembelajaran bagi para pengguna semudah mungkin. Di sini juga akan di ulas beberapa contoh formula yang sering digunakan dalam fungsi VLOOKUP Excel.

FUNGSI EXCEL VLOOKUP – DESKRIPSI DAN PENULISAN

Jadi apa yang dimaksud dengan Excel VLOOKUP? untuk mendeskripsikan secara sederhana, ini adalah fungsi dari Excel. Lalu apa gunanya? Fungsi ini akan mencari nilai yang Anda tentukan dan mengembalikan nilai yang cocok dari kolom lain. Lebih teknis, fungsi VLOOKUP adalah mencari nilai dalam kolom pertama dari kisaran tertentu dari sel, dan mengembalikan nilai dalam baris yang sama dari kolom lain.

Dalam penggunaan umum, Excel VLOOKUP melakukan pencarian melalui daftar data Anda berdasarkan pengenal yang unik dan membawakan kepada Anda sepotong informasi yang terkait dengan identifikasi yang unik. Huruf “V” di VLOOKUP singkatan dari “vertikal”. Hal ini digunakan untuk membedakan VLOOKUP dari fungsi HLOOKUP yang mencari nilai di baris atas array (H singkatan dari “horisontal”).

Fungsi VLOOKUP tersedia dalam semua versi Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP dan Excel 2000.

6

Rumus Excel VLOOKUP

Rumus untuk fungsi VLOOKUP adalah sebagai berikut:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Seperti yang Anda lihat, fungsi Excel VLOOKUP memiliki 4 parameter, atau argumen. Tiga parameter pertama adalah wajib di tuliskan, sedangkan yang terakhir adalah opsional.

1. lookup_value: Nilai untuk dicari.
Ini dapat berupa nilai (nomor, tanggal atau teks) atau referensi sel (referensi ke sel yang berisi nilai lookup), atau nilai yang dikembalikan oleh beberapa fungsi Excel lainnya. Sebagai contoh, rumus =VLOOKUP (40, A2: B15,2) akan mencari nilai 40.

2. table_array: dua atau lebih kolom data.
Ingat, fungsi VLOOKUP selalu mencari nilai lookup di kolom pertama table_array. Table_array Anda mungkin berisi berbagai nilai seperti teks, tanggal, angka, atau nilai-nilai logis. Nilai di sini bersifat sensitif, yang berarti bahwa teks huruf besar dan huruf kecil harus diperhatikan sebagai nilai identik.

Jadi, contoh rumus yang kita gunakan di sini adalah: =VLOOKUP (40, A2: B15,2) akan mencari “40″ dalam sel A2 sampai A15 karena A adalah kolom pertama dari A2 table_array: B15.

3. col_index_num: nomor kolom di table_array di mana nilai dalam baris yang sesuai harus dikembalikan. Sedangkan tabel yang paling kiri dari table_array ditentukan dengan 1, kolom kedua adalah 2, kolom ketiga adalah 3, dan seterusnya.

Sekarang, Anda dapat membaca seluruh rumus yang telah Anda susun di atas, yakni: =VLOOKUP (40, A2: B15,2). Pencarian formula untuk “40″ di sel A2 sampai A15 dan mengembalikan nilai yang cocok dari kolom B (karena B adalah kolom ke-2 dalam table_array A2:B15).

Dari contoh rumus di atas, Anda akan mendapatkan hasil seperti berikut:

7

Rumus VLOOKup Excel dan contohnya

4. range_lookup: menentukan apakah Anda sedang mencari nilai yang sama (FALSE) atau perkiraan pencocokan (TRUE atau diabaikan). Parameter akhir ini adalah opsional, tetapi sangat penting.

Aturan yang Harus Diperhatikan

Ada beberapa peraturan yang harus diingat tentang penggunaan fungsi ini, antara lain adalah:

Peraturan 1: Kolom kiri harus mengandung nilai-nilai yang direferensikan. Dengan kata lain, kita tidak bisa mengisi kolom paling kiri dengan nilai yang lain berbeda.

Peraturan 2: Anda tidak dapat memiliki nilai ganda dalam kolom paling kiri dari rentang pencarian. Atau Anda tidak bisa memiliki dua entri dengan nilai yang sama. Jika memaksa melakukan hal ini, maka Excel tidak memungkinkan untuk menjalankan fungsi.

Peraturan 3: Ketika mengacu pada tabel pencarian, Anda tidak menginginkan jika referensi sel berubah ketika Anda melakukan drag dan mengisi sel-sel lain dengan fungsi VLOOKUP. Sebagai contoh, jika Anda ingin menggunakan fungsi yang sama pada sel D3 melalui D7, dan Anda tidak ingin referensi lookup menggeser setiap kali pindah ke sel berikutnya, maka Anda membutuhkan nilai referensi yang sama. Setelah Anda mendefinisikan jangkauan Anda, Anda bisa menekan F4 yang akan melalui referensi absolut dan relatif.

8

Contoh Excel VLOOKUP

Diharapkan setelah membaca tutorial di atas, Anda akan sedikit lebih akrab dengan VLOOKUP. Sekarang mari kita telusuri beberapa contoh yang menggunakan rumus VLOOKUP pada data yang sesungguhnya.

Dalam prakteknya, formula VLOOKUP jarang digunakan untuk mencari data di lembar kerja yang sama. Paling sering Anda harus melihat data dari lembar kerja lain untuk melakukan pencocokan.

Untuk melakukan VLOOKUP dari lembar Excel yang berbeda, Anda harus memasukkan nama worksheet dan tAnda seru dalam argumen table_array sebelum kisaran sel, misalnya = VLOOKUP (40, Sheet2 A2: B15,2). Rumus ini menunjukkan bahwa kisaran pencarian terdapat pada A2: B15 yang terletak di Sheet2.

Tentu saja, Anda tidak perlu mengetik nama lembar secara manual. Cukup mulai mengetik rumus ini dan ketika datang kepada argumen table_array maka Anda bisa beralih ke worksheet lookup dan pilih kisaran menggunakan mouse.

Yang Harus Diingat dalam Penggunaan Excel VLOOKUP

1. Fungsi Excel VLOOKUP tidak dapat membaca dari kiri, namun fungsi ini selalu mencari nilai lookup di kolom paling kiri dari rentang lookup (table_array).

2. Dalam rumus VLOOKUP, semua nilai adalah nilai sensitif, yang berarti bahwa karakter huruf besar dan huruf kecil diperhatikan secara detail.

3. Jika nilai lookup lebih kecil dari nilai terkecil di kolom pertama dari jangkauan pencarian Anda, maka fungsi VLOOKUP akan menghasilkan hasil #N/A error.

4. Jika parameter ke tiga (col_index_num) kurang dari 1, rumus VLOOKUP akan menampilkan hasil #VALUE! eror. Sebaliknya, jika dalam pengerjaan bahwa ini lebih besar dari jumlah kolom dalam rentang pencarian Anda (table_array), rumus akan menampilkan #REF! error.

5. Gunakan referensi sel absolut dalam rumus table_array VLOOKUP Anda untuk mendapatkan rentang lookup yang tepat ketika menghadapi rumus. Pertimbangkan untuk menggunakan rentang lookup dengan nama atau tabel Excel sebagai alternatif.

9

6. Saat mencari dengan menggunakan metode pencocokan perkiraan (range_lookup set ke TRUE atau diabaikan), selalu miliki data pada kolom pertama dalam rentang pencarian yang diurutkan dalam urutan yang menarik.

7. Dan terakhir, ingat tentang pentingnya parameter akhir. Penyediaan TRUE atau FALSE di saat yang tepat akan menghindarkan Anda dari banyak kebingungan.

VLOOKUP adalah fungsi dan rumus Excel yang kuat yang dapat memanfaatkan data Anda bahkan dari lembar kerja yang lain. Ada banyak cara Anda bisa mendapatkan keuntungan dari fungsi ini. Semakin Anda mengeksplorasi dan memperdalam fungsi ini, maka Anda akan semakin ahli untuk mengerjakan data dalam Excel Anda.

CONTOH VBA EXCEL UNTUK MEMBUAT TANGGAL DAN WAKTU

Contoh VBA Excel kali ini akan sedikit mengajak kita bersenang-senang, karena pada ulasan kali ini kita akan belajar bagaimana memasukkan tanggal dan waktu menggunakan script di VBA. VBScript tentang tanggal dan waktu ini membantu para pengembang untuk mengubah tanggal dan waktu dari satu format ke format yang lain atau untuk mengungkapkan tanggal atau waktu yang sesuai dengan kondisi tertentu. Penasaran bagaimana cara membuatnya? ikuti prosedur berikut.

Masukkan barisan kode di bawah ini pada worksheet Anda dan eksekusi kodenya. Untuk mengeksekusi baris kode, klik tombol command button pada sheet Anda. Makro berikut ini akan memberikan tahun dan tanggal. Untuk mendeklarasikan tanggal, gunakan pernyataan Dim. Untuk menginisialisasi tanggal, gunakan fungsi datevalue.

Dim exampleDate As Date

exampleDate = DateValue(“Jun 19, 2010″)

MsgBox Year(exampleDate)

10

Hasil yang akan Anda dapatkan:

Untuk menambahkan hari dan tanggal, gunakan fungsi DateAdd. Fungsi DateAdd memiliki tiga argumen. Isi “d” untuk menambahkan hari. Isi 3 untuk argumen kedua untuk menambah 3 hari. Argumen ketiga merupakan tanggal dimana jumlah hari akan ditambah.

Dim firstDate As Date, secondDate As Date

firstDate = DateValue(“Jun 19, 2010″)

secondDate = DateAdd(“d”, 3, firstDate)

MsgBox secondDate

Ubah “d” untuk “m” untuk menambahkan bulan ke tanggal. Tempatkan kursor pada DateAdd di Visual Basic Editor dan klik F1 untuk bantuan pada penentu interval lain. Anda bisa menggunakan format tanggal sesuai dengan keinginan Anda, apakah ingin menggunakan format Indonesia, Amerika, atau Jepang. Jenis format tergantung pada jendela Anda pengaturan waktu regional padadesktop Anda.

Untuk mendapatkan tanggal dan waktu, gunakan fungsi Now.

MsgBox Now

Hasil contoh VBA excel yang akan Anda dapatkan

11

Untuk mendapatkan waktu, gunakan fungsi Hour.

MsgBox Hour(Now)

Hasil yang akan Anda dapatkan

Fungsi TimeValue akan mengkonversi string ke nomor seri waktu. Nomor seri waktu adalah angka antara 0 dan 1. Sebagai contoh, siang (tengah hari menuju sore) akan direpresentasikan sebagai 0,5.

MsgBox TimeValue(“9:20:01 am”)

Hasil yang akan Anda dapatkan:

Sekarang, untuk melihat dengan jelas bahwa Excel menangani waktu secara internal sebagai angka antara 0 dan 1, tambahkan baris kode berikut:

12

Dim y As Double

y = TimeValue(“09:20:01″)

MENGENAL DEBUGGING PADA VBA MACRO EXCEL PROGRAMMING

VBA macro excel programming juga merupakan salah satu tutorial yang akan dibahas pada ulasan kali ini. Pemrograman sangat penting untuk menguasai excel secara keseluruhan. Ketika Anda mengenal sedikit pemrograman pada excel, maka Anda bisa menguasai semua seluk beluk dari program Microsoft Excel ini. Alhasil semua pekerjaan Anda akan sangat mudah untuk dikerjakan, karena ini akan menghemat waktu Anda. Salah satu hal yang paling penting untuk dikenal di dalam bahasa pemrograman adalah debugging. Dengan menekan F8, Anda dapat dengan mudah  melangkah melalui kode Anda. Ini sangat berguna karena memungkinkan Anda untuk melihat efek dari setiap baris kode pada lembar kerja Anda. Untuk belajar melakukannya, tuliskan kode di bawah ini:

Dim i As Integer, j As Integer

For i = 1 To 2

    For j = 1 To 5

        Cells(i, j).Value = WorksheetFunction.RandBetween(20, 100)

    Next j

Next i

Hasil yang akan Anda dapatkan ketika Anda mengklik command button pada sheet.

1.       Kosongkan range A1:E2.

2.       Buka Visual Basic Editor dan kurangi ukuran jendela hingga setengah layar Anda sehingga Anda bisa dengan jelas melihat Visual Basic Editor dan worksheet pada waktu yang bersamaan.

3.       Dalam Visual Basic Editor, tempatkan kursor sebelum Private dan tekan F8. Maka Anda akan mendapatkan baris pertama VBA macro excel programming akan berubah menjadi kuning.

13

4.       Tekan F8 empat kali. Untuk i = 1 dan j = 1, Excel VBA akan memasukkan nomor acak antara 20 dan 100 ke dalam cell pada persimpangan baris 1 dan kolom 1. Dengan mengarahkan kursor pada variabel, Anda dapat melihat nilai variabel itu sendiri.

5.       Tekan F8 dua kali. For i = 1 dan j = 2, Excel akan memasukkan nomor acak antara 20 dan 100 ke dalam cell pada persimpangan baris 1 dan kolom 2.

14

Ini adalah serangkaian cara bagaimana Excel memasuki nomor lain. Ini adalah cara lain yang terbaik untuk belajar bagaimana loop itu sendiri bekerja. Jika Anda ingin menghentikan program, klik tombol Reset (Stop) pada VBA macro excel programming.

MENGENAL LEBIH DEKAT PENGATURAN KONTROL VBA PADA MICROSOFT EXCEL

Microsoft Excel merupakan salah satu produk yang paling canggih yang pernah diproduksi oleh pabrikan perangkat lunak terbesar di dunia Microsoft, bersanding dengan perangkat lunak tulisnya yang bertajuk Microsoft Word. Untuk dunia kerja, produk Microsoft ini memang sudah tak bisa dipisahkan lagi perannya. Program ini sudah sangat identik dengan program administrasi dan program untuk pembukuan. Tidak hanya dapat membantu Anda dalam melakukan pembukuan dan administrasi saja, namun di dalam Excel ini Anda diberikan semua otoritas untuk mengatur data Anda dengan cara sedemikian mudahnya.

Salah satu cara yang paling terkenal adalah excel macro. Di dalam alat yang dikenal macro ini Anda bisa melakukan trik ‘sulap’ yang mana hanya dengan beberapa klik di sana sini, Anda bisa meringkas semua pekerjaan yang membutuhkan waktu berjam-jam ketika dikerjakan secara manual. Alat macro ini dibantu

15

dengan alat lain yang bernama VBA atau Visual Basic Application. Sebelum melangkah lebih jauh lagi, ada

baiknya kita mengenal lebih dekat VBA. Masing-masing dan kontrol setiap VBA pada Microsoft Excel memiliki pengaturan bawaannya sendiri, seperti Control, height, width, Caption, color, background, font Color dan lain-lain. Dalam Excel selain Semua kontrol VBA seperti Command Button, Button Radio, Textbox, WorkSheet Anda juga memiliki kontrol sendiri.

Jadi mari kita lihat bagaimana kita dapat melihat dan mengubah kontrol yang ditambahkan pada WorkSheet Anda.

1.       Di bawah WorkSheet Excel ini kita memiliki 2 kontrol, 1 Command Button dan Satu Satu Combobox.

Tekan Klik kanan pada Control yang ingin Anda lihat atau ubah sifat dari kontrol itu.

2.       Klik Properties

16

3.       Jendela Properti terlihat sekarang. Di atas Anda dapat melihat semua Control yang ditambahkan ke dalam WorkSheet Anda bersama dengan Sheet Name. Secara default, kendali kontrol yang diberikan kepada Anda akan jatuh di tempat di mana Anda telah mengklik kanan dan memilih “Properties”. Dari sini Anda dapat memilih salah satu kontrol yang ingin Anda lihat di Property.

17

4.       Dalam Gambar di atas Anda dapat melihat properti dari CommandButton1 yang telah ditampilkan.

18

Di Atas Anda dapat melihat ada properti yang disebut “Name”. Ini adalah nama dari Control itu. Nama ini harus selalu unik dalam Workbook Excel sebagai penanda dari kontrol yang telah Anda ubah.

Menjadi kebiasaan yang baik untuk mengubah nama kontrol Anda berdasarkan fungsinya. Sehingga ketika Anda memiliki banyak kontrol, maka sangat mudah nantinya mengingat nama-nama kontrol saat menulis kode di dalam Microsoft Excel.

MENENTUKAN FPB DAN KPK MATEMATIKA DENGAN EXCEL

Dalam pelajaran matematika, khususnya tentang aritmetika, memiliki istilah mengenai faktor persekutuan terbesar dan juga terkecil. Dan di dalam tutorial kali ini, akan diberikan cara menghitung faktor persekutuan terbesar dan terkecil di Ms Excel.

19

Tutorial pertama akan dibahas mengenai Faktor Persekutuan Terbesar (FPB) yang terdiri dari 2 bilangan bulat positif terbesar yang bisa habis dibagi kedua bilangan tersebut. Excel sendiri memiliki fungsi Greatest Common Divisor atau GCD. Salah satu penerapan GCD di Ms Excel ini guna mencari bilangan prima. Berikut detail dan penjelasannya.

Menentukan nilai Faktor Persekutuan Terbesar (FPB) dari angka bilangan 10 serta 20

Faktor bilangan 10 = 1, 2, 5 dan 10
Faktor bilangan 20 = 1, 2, 4, 5, 10 dan 20

Jadi FPB dari bilangan 10 dan 20 yang bisa dibagi habis oleh keduanya adalah 10

CARA MENENTUKAN PERSEKUTUAN TERBESAR DAN TERKECIL DENGAN MICROSOFT EXCEL

Membuat tabel seperti gambar di bawah (kolom berwarna kuning berisi data. Sementara kolom berwarna hijau akan berisi rumus atau formula).

Menentukan bilangan FPB Matematika dengan excel

Kolom B2 dan C2 akan berisi data angka yang bisa dicari kelipatannya, yaitu:

Di kolom C3 bisa dimasukkan rumus =GCD(B2,C2)
Di kolom C4 masukkan rumus =GCD(10,20)

Sedangkan di kolom B7, C7 dan D7, isi data yang akan dicari kelipatannya

20

Di kolom C8 ketik =GCD(B7,C7,D7)
Lalu ketik =GCD(10,15,20)

Maka dengan cara menggunakan rumus excel seperti diatas akan didapat hasil pada gambar berikut

Hasil perhitungan bilangan FPB dengan Excel

Sementara untuk kelipatan persekutuan terkecil atau KPK, dari 2 bilangan adalah bilangan bulat positif terkecil yang bisa dibagi habis oleh kedua bilangan tersebut. Kebalikan dari persekutuan terbesar. Kelipatan persekutuan terkecil bisa dicari di Ms Excel.

Berikut contoh perhitungan persekutuan terkecil yang menggunakan bilangan 2 dan 10. Perhitungan manualnya akan didapatkan berikut

Kelipatan 2 adalah 2,4,6,8,10,12,14,16 dst
Kelipatan 10 adalah 10,20, 30,40, dst
Maka KPK dari bilangan 2 dan 10 adalah 10

Sementara jika menggunakan Excel, anda bisa menggunakan cara berikut
Buat tabel seperti gambar di bawah ini. (kolom berwarna kuning berisi data. Sementara kolom berwarna hijau akan berisi rumus atau formula).

21

Menentukan bilangan KPK matematika dengan Excel

Kolom B2 dan C2 sendiri merupakan angka yang bisa anda cari kelipatannya

Kolom C3 diisi rumus =LCM(B2,C2)
Kolom C4 diisi rumus =LCM(2,10)
Kolom C8 diisi rumus =LCM(B7,C7,D7)
Kolom C9 diisi rumus =LCM(2,10,15)

Dengan cara rumus microsoft Excel sperti diatas, hasilnya akan didapat sebagai berikut

Hasil perhitungan rumus Excel untuk bilangan KPK matematika

22

Untuk mencoba apakah rumus tersebut berhasil atau tidak, anda bisa mengganti angka yang terdapat di dalam kolom yang berwarna kuning. Setelah itu anda bisa melihat perubahan nilai dari bilangan KPK secara otomatis yang terdapat di kolom C3 dan C8. Demikian tutorial singkat dan sederhana mengenai persekutuan terbesar dan terkecil. 

BELAJAR VBA EXCEL UNTUK MEMBUAT INPUT BOX

Belajar VBA Excel kali ini akan memperkenalkan Anda kepada Input Box. Lalu apa sebenarnya Input Box itu sendiri? Fungsi InputBox membantu pengguna untuk mendapatkan nilai-nilai. Setelah memasukkan nilai, jika pengguna mengklik tombol OK atau menekan ENTER, fungsi InputBox akan mengembalikan teks dalam kotak teks. Jika pengguna mengklik tombol Cancel , fungsi akan mengembalikan string kosong (“”).

Contoh syntax dari fungsi Input Box:

InputBox(prompt[,title][,default][,xpos][,ypos][,helpfile,context])

§  Prompt: Sebuah parameter yang diperlukan. Sebuah String yang ditampilkan sebagai pesan dalam kotak dialog. Panjang maksimum dari prompt ini adalah sekitar 1024 karakter. Jika pesan pesan meluas ke lebih dari satu baris, maka kita dapat memisahkan baris menggunakan karakter carriage return (Chr (13)) atau karakter linefeed (Chr (10)) antara setiap baris.

§  Title: Sebuah Parameter Opsional. Ekspresi String yang ditampilkan di bar judul kotak dialog. Jika judul dibiarkan kosong, nama aplikasi ditempatkan dalam judul bar.

§  Default: Sebuah Parameter Opsional. Sebuah teks default di kotak teks yang ingin ditampilkan oleh user.

§  XPos: Sebuah Parameter Opsional. Posisi sumbu X yang mewakili jarak yang dikonfirmasi dari sisi kiri layar horizontal. Jika dibiarkan kosong, kotak input akan secara horizontal terpusat.

§  Ypos: Sebuah Parameter Opsional. Posisi Y axis yang mewakili jarak yang dikonfirmasi dari sisi kiri layar vertikal. Jika dibiarkan kosong, kotak input akan secara vertikal terpusat.

§  helpfile: Sebuah Parameter Opsional. Ekspresi String yang mengidentifikasi file Help yang digunakan untuk memberikan bantuan untuk menunjukkan konteks yang sensitif.

§  context: Sebuah Parameter Opsional. Sebuah ekspresi numerik yang mengidentifikasi nomor yang diberikan oleh Help. Jika konteks disediakan, helpfile juga harus disediakan.

Berikut adalah contohnya untuk Belajar VBA Excel. Kita akan menghitung luas persegi panjang dengan memasukkan nilai-nilai dari pengguna pada waktu yang bersamaan dengan bantuan dua kotak input (satu untuk panjang dan satu untuk lebar).

Function findArea()

  Dim Length As Double

23

 Dim Width As Double

  Length = InputBox(“Enter Length “, “Enter a Number”)

  Width = InputBox(“Enter Width”, “Enter a Number”)

  findArea = Length * Width

End Function

untuk mengeksekusinya, kita perlu memanggil menggunakan nama fungsi dan tekan Enter dan akan menunjukkan hasil seperti gambar di bawah.

Setelah Eksekusi, Kotak input Pertama (Panjang) ditampilkan dan pengguna harus memasukkan nilai ke dalam kotak masukan.

24

Setelah memasukkan nilai pertama, kotak input kedua (lebar) ditampilkan kepada pengguna.

Setelah memasukkan nomor kedua dan mengklik tombol OK, maka area ditampilkan kepada pengguna seperti yang ditunjukkan di bawah ini.

Masukkan nilai lain jika Anda ingin untuk Belajar VBA Excel.

 

 

MENGGUNAKAN MACRO VBA EXCEL UNTUK MEMBUAT CHART

Macro VBA Excel selain dapat digunakan untuk menghasilkan beberapa keajaiban dasar untuk menyelesaikan semua pekerjaan Anda, juga dapat berguna untuk membuat grafik. Pada tutorial ini, Anda akan belajar bagaimana untuk memanipulasi grafik dengan VBA. Ada dua jenis grafik yang dapat Anda manipulasi menggunakan VBA. Yang pertama adalah grafik lembaran, dan yang kedua adalah grafik tabel. Grafik bentuk lembaran adalah lembaran terpisah di buku kerja Anda, dengan tab tersendiri yang Anda klik di bagian bawah Excel. Sedangkan sebuah grafik tabel adalah salah satu yang dimasukkan ke lembar kerja. Kedua jenis grafik ini menggunakan kode yang sedikit berbeda. Kami akan berkonsentrasi pada grafik tabel. Untuk membuat grafik lembaran, mulai dengan menggunakan spreadsheet baru. Masukkan beberapa data dalam sel A1 ke B11. Sesuatu seperti berikut:

25

Klik Developer > Visual Basic untuk mendapatkan jendela coding. Buat Sub baru dalam Sheet 1. Beri nama ChartSheetExample. Tambahkan baris berikut:

Dim ChartSheet1 As Chart

Kode Anda akan terlihat seperti ini:

Jadi kita sekarang tidak menggunakan jenis variabel Integer atau string jenis variabel, kita sekarang memiliki tipe Chart. Untuk menambahkan lembar grafik, yang Anda butuhkan adalah panggilan ke metode penjumlahan. Namun, kita akan mengatur variabel ChartSheet1 sebagai objek, sehingga kita dapat mengakses berbagai properti dan metode. Tambahkan baris berikut ke kode Macro VBA Excel Anda:

Set ChartSheet1 = Charts.Add

Sekarang tambahkan statemen With … End With

With ChartSheet1

End With

Hal pertama yang bisa kita lakukan adalah menambahkan data untuk grafik. Untuk melakukan itu, Anda perlu metode SetSourceData. Metode ini mengambil parameter yang disebut Source. Parameter Source membutuhkan data dari cell untuk mengambil data. Berikut kode yang bisa ditambahkan.

26

With ChartSheet1

.SetSourceData Source:=Sheets(“Sheet1″).Range(“B1:B11″)

End With

Setelah Source:= kita memiliki kode ini Sheets(“Sheet1″).Range(“B1:B11″). Kode ini akan mendapatkan referensi ke lembar kerja yang disebut  “Sheet1″. Range cell yang kita inginkan adalah sel B1 ke B11. Jendela coding Anda akan  terlihat seperti ini:

Anda dapat menjalankan kode Anda pada tahap ini. Tekan F5 pada keyboard Anda, atau klik Run> Run Sub/User Form dari menu di bagian atas jendela VBA. Anda akan menemukan bahwa lembar baru terbuka di Macro VBA Excel seperti ini:

27

 

 

BELAJAR VBA EXCEL MENGGUNAKAN IF STATEMENTS

Belajar VBA Excel kali ini akan membahas Conditional Logic. Conditional Logic sendiri adalah semua tentang kata IF. Ini tentang mengatakan apa yang harus terjadi jika kondisi tertentu terpenuhi, atau apa yang harus terjadi jika tidak terpenuhi.

IF Statements

Anda terkadang menggunakan kondisi IF ini di kehidupan Anda sehari-hari. Seperti Anda akan mengatakan hal-hal seperti berikut: Jika saya membeli barang ini, saya akan bahagia, jika saya belajar menggunakan Macro Excel, saya mungkin akan makin mudah mengerjakan semua tugas saya. Pemrograman dalam bahasa apapun sangat bergantung pada Conditional Logic seperti seperti Pernyataan IF. Hal ini memungkinkan Anda untuk turun ke jalan yang berbeda, tergantung pada kondisi awal.

Struktur Pernyataan IF pada VBA akan terlihat seperti ini:

If Condition_To_Test Then

28

‘CODE HERE

End If

Anda mulai dengan kata If dengan huruf I besar dan f kecil. Setelah spasi, Anda memiliki kondisi yang ingin Anda uji. Kondisi ini  adalah sesuatu yang dapat menjadi TRUE atau FALSE. Setelah Anda memasukkan kondisi Anda, Anda harus memberikan spasi diikuti oleh kata Then dengan huruf T besar. Dan sebuah pernyataan If akan diakhiri dengan kata-kata End If.

Di antara If dan End If, adalah di mana Anda mengetik kode Anda. Tapi kode ini hanya akan dieksekusi jika kondisi If Anda TRUE. Jika itu FALSE maka VBA akan melompat melewati End If dan meneruskan eksekusi kodenya. Mari kita Belajar VBA Excel dengan mengetikkan beberapa contoh coding sederhana. Anda dapat memulai spreadsheet baru untuk ini. Ketika Anda menyimpan file baru, jangan lupa untuk menyimpannya sebagai Excel Macro- Enable Workbook. File akan tersimpan dengan format XLSM.

Klik View Code akan membuka VBA Editor. Jika coding jendela untuk Sheet1 belum terbuka, klik dua kali Sheet1 pada Project Explorer di sebelah kiri. Buat Sub baru di jendela coding Anda, dan beri nama If_Test_1. Tambahkan kode berikut untuk Sub Anda:

Dim MyNummber As Integer

MyNumber = 10

If MyNumber = 10 Then

MsgBox “Number = 10″

End If

29

Maka akan terlihat seperti ini:

Tekan tombol F5 pada keyboard Anda Anda untuk menjalankannya. Anda akan melihat tampilan pesan Box seperti ini:

Mudah bukan Belajar VBA Excel? Begitula cara belajar rumus excel kita kali ini.

 

MENAMBAHKAN TOMBOL PADA MACRO VBA EXCEL

Macro VBA Excel menyimpan banyak kemudahan. Setelah Anda mengetahui bagaimana membuat Sub dengan sederhana, pada tutorial kali ini Anda akan mengaktifkan sebuah tombol pada spreasheet excel. Caranya pun tergolong mudah.

Di bagian atas VBA Editor, cari ikon Excel, tepat di bawah menu File.

Klik ikon ini untuk kembali ke spreadsheet Anda. Kita sekarang akan menempatkan tombol kontrol pada spreadsheet. Cari Controls panel pada toolbar Developer, dan kemudian klik item Insert. Dari menu Insert, klik item pertama, yang merupakan tombol:

30

Sekarang pindahkan mouse Anda ke spreadsheet Anda. Tahan tombol kiri mouse Anda di suatu tempat pada kolom F (biasanya akan jatuh pada F3). Tetap tahan dan tarik maka akan tercipta sebuah tombol persegi panjang. Lepaskan tombol kiri mouse saat kursor Anda di H4

Segera setelah Anda melepaskan tombol kiri mouse Anda akan melihat kotak dialog Assign Macro muncul pada Macro VBA Excel;

31

Pilih Macro Anda dari daftar dan klik OK. Tombol pada spreadsheet Anda akan terlihat seperti ini:

Anda dapat mengedit teks pada tombol dengan cukup mudah. Klik tombol kanan untuk memunculkan sebuah menu. Dari menu, pilih Edit Text:

Bila Anda pilih Edit Text, kursor akan muncul pada awal teks. Gunakan tombol panah pada keyboard untuk memindahkan kursor ke akhir baris. Hapus Button 1 dan ketik Range(“A1:D6″).

Pindahkan kursor keluar dari tombol tersebut, Anda akan melihat garis pengaturan ukuran tombol akan menghilang. Sekarang Anda dapat menguji tombol. Klik tombol tersebut, lalu Anda akan melihat cell A1 ke D6 disorot:

32

Selamat! Sekarang Anda telah menulis sebuah kode VBA Excel untuk memilih kisaran cell pada spreadsheet. Dan semua hanya dengan sebuah klik tombol. Sekarang kembali ke Visual Basic Editor (Dari toolbar Developer, klik Visual Basic pada panel Code.) Beri tanda kutip pada awal kode Range Anda, dan ini akan berubah menjadi hijau.

Mudah bukan membuat tombol pada Macro VBA Excel. Begitulah kehebatan rumus excel dalam membantu kita.

 

MENGOPERASIKAN VBA MACRO EXCEL DENGAN ANGKA VARIABEL

VBA macro excel sebelumnya telah mengulas tentang bagaimana mengoperasikan VBA macro di dalam Microsoft Excel. Kali ini di dalam tutorial ini Anda akan diajak untuk memahami variabel, dengan membuat spreadsheet kosong yang baru. Untuk memulainya klik pada pita Developer pada bagian atas Excel. Pada panel Control klik View Code. Ini akan membuka Visual Basic Editor dengan jendela Sheet1 coding yang sudah siap Anda gunakan. Siapkan Sub baru dan beri nama Variable_Practice. Tambahkan dua baris berikut ini ke dalam Sub.

            Dim MyNumber As Integer

MyNumber = 10

Jendela coding Anda akan terlihat seperti ini:

33

Jadi kode di atas hanya akan membuat sebuah variabel yang disebut MyNumber. Lalu kemudian kita menambahkan nilai 10 ke variabel ini. Hal penting dalam pemrograman adalah apapun yang Anda tulis di sisi kanan tanda adalah menggambarkan apa yang akan Anda simpan. Sedangkan apa yang Anda simpan di sisi kiri sebuah tanda merupakan tempat di mana Anda akan menampilkannya.

Anda telah mengerti tentang Range pada ulasan lalu. Salah satu properti dari Range adalah Value. Nilai atau value dari properti bisa mengatur sebuah cell atau kelompok dari cell. Untuk mengatur sebuah Value, Range harus ditempatkan sebelum sama dengan, seperti contoh berikut:

            Worksheets(1).Range(“A1″).Value =

Di sini, kita mengakses terlebih dahulu objek dari Worksheet1 di VBA macro excel. Pada baris kode tersebut kita menekankan pada Range A1. Setelah titik, kita kemudian ketik properti Value. Hal ini diikuti oleh sama dengan. Setelah sama dengan, Anda dapat mengisikan angka yang Anda inginkan.

            Worksheets(1).Range(“A1″).Value = 10

Sekarang, nilai atau Value untuk Range A1 di dalam Worksheet 1 diatur dari angka 1 ke 10. Daripada harus mengetik nomor, Anda dapat mengetik nama variabel. Karena kita telah menyimpan angka 10 dalam variabel, jadi kita hanya perlu menggunakan ini:

Worksheets(1).Range(“A1″).Value = MyNumber

VBA akan melihat variabel yang disebut MyNumber dan kemudian akan menjemput nilai apa pun yang di simpan di dalamnya. Nilai yang dimaksud adalah nilai yang Anda simpan di baris sebelah kiri. Tambahkan baris tersebut ke kode Anda, persis di bawah dua yang lainnya. Maka jendela coding Anda akan tampil seperti berikut:

34

Sekarang saatnya mencoba. Kembali ke Spreedsheet dan tambahkan sebuah tombol. Ketika dialog Assign Macro muncul, pilih Variable Practice Sub dari daftar. Ubah teks pada tombol menjadi  Variable Practice. Hapus centang tombol Anda. Sekarang klik lagi untuk mencobanya. Anda akan menemukan bahwa nomor 10 muncul dalam sel A1 pada spreadsheet VBA macro excel Anda:

Sekian tutorial rumus excel tentang penggunaan macro kita kali ini. Semoga bermanfaat dalam membantu pekerjaan sehari-hari kita.

MACRO EXCEL TUTORIAL: CARA MEMBUAT EXCEL FORM VBA

Macro excel tutorial kali ini akan dijelaskan setiap aspek dari membuat form dari VBA. Bentuk dari form atau yang sering kita sebut dengan formulir di dalam Excel benar-benar sangat bagus untuk dilihat. Hal ini terlihat sangat menarik saat Anda bekerja untuk membuat User Forms. Anda dapat membangun sebuah UI yang sangat bagus (User Interface) dengan menggunakan Excel Form VBA. Dalam tutorial ini Anda akan belajar tentang semua hal tentang VBA User Form.

Cara membuat User Form dari VBA sendiri sangat mudah dan menarik. Untuk merancang sebuah form sendiri tidak perlu dibutuhkan coding sama sekali. Kode hanya diperlukan untuk membuat fungsi Form sesuai kebutuhan Anda. Untuk membuat Form, ikuti langkah-langkah sederhana darimacro excel tutorial di bawah ini:

1.       Buka lembar kerja Anda dan Tekan Alt + F11

2.       VB Code Editor akan terbuka seperti di bawah ini

35

3.       Klik kanan pada Microsoft Excel Object

4.       Pergi ke Insert à User Form

5.       Di Sisi Kanan Anda dapat melihat satu Default UserForm1 telah dibuat. Anda dapat melihat Toolbox juga dengan semua Kontrol yang tersedia untuk User Form.

6.       Menggunakan Tool Box dari User Form dapat Anda lakukan dengan cara Drag and drop atas semua Kontrol yang Anda inginkan pada formulir Anda. Lihatlah contoh dari formulir yang sudah jadi di bawah ini. Anda bisa menciptakan formulir Anda sendiri bagaimana pun bentuknya

36

Untuk membuat formulir pada rumus excel tersebut bisa bekerja, kita harus menuliskan beberapa baris kode. Kode ini akan dapat mengeksekusi perintah Anda kepada formulir tersebut, sehingga formulir Anda akan benar-benar berjalan sebagaimana mestinya. Di sini akan diberikan contoh seperti apa kode yang akan Anda masukkan:

Private Sub UserForm_Initialize()

UserForm1.ComboBox1.Clear

 With UserForm1.ComboBox1

 .AddItem “Junior Engineer”

 .AddItem “Engineer”

 .AddItem “Team Lead”

 .AddItem “Technical Lead”

 .AddItem “Functional Consultant”

 .AddItem “Technical Consultant”

 .AddItem “Project Manager”

 .AddItem “Delivery Manager”

 .AddItem “Group Delivery Manager”

 .AddItem “Unit Head”

37

.AddItem “Regional Head”

 End With

End Sub

Pelajari dengan teliti bagaimana memasukkan kode seperti di atas. Cara mengaktifkan kode tersebut di atas dengan metode hiding dan unloading akan dibahas kembali pada macro excel tutorial berikutnya.

MENGENAL LEBIH DEKAT PENGATURAN KONTROL VBA PADA MICROSOFT EXCEL

Microsoft Excel merupakan salah satu produk yang paling canggih yang pernah diproduksi oleh pabrikan perangkat lunak terbesar di dunia Microsoft, bersanding dengan perangkat lunak tulisnya yang bertajuk Microsoft Word. Untuk dunia kerja, produk Microsoft ini memang sudah tak bisa dipisahkan lagi perannya. Program ini sudah sangat identik dengan program administrasi dan program untuk pembukuan. Tidak hanya dapat membantu Anda dalam melakukan pembukuan dan administrasi saja, namun di dalam Excel ini Anda diberikan semua otoritas untuk mengatur data Anda dengan cara sedemikian mudahnya.

Salah satu cara yang paling terkenal adalah excel macro. Di dalam alat yang dikenal macro ini Anda bisa melakukan trik ‘sulap’ yang mana hanya dengan beberapa klik di sana sini, Anda bisa meringkas semua pekerjaan yang membutuhkan waktu berjam-jam ketika dikerjakan secara manual. Alat macro ini dibantu dengan alat lain yang bernama VBA atau Visual Basic Application. Sebelum melangkah lebih jauh lagi, ada baiknya kita mengenal lebih dekat VBA. Masing-masing dan kontrol setiap VBA pada Microsoft Excel memiliki pengaturan bawaannya sendiri, seperti Control, height, width, Caption, color, background, font Color dan lain-lain. Dalam Excel selain Semua kontrol VBA seperti Command Button, Button Radio, Textbox, WorkSheet Anda juga memiliki kontrol sendiri.

38

Jadi mari kita lihat bagaimana kita dapat melihat dan mengubah kontrol yang ditambahkan pada WorkSheet Anda.

1.       Di bawah WorkSheet Excel ini kita memiliki 2 kontrol, 1 Command Button dan Satu Satu Combobox.

Tekan Klik kanan pada Control yang ingin Anda lihat atau ubah sifat dari kontrol itu.

2.       Klik Properties

3.       Jendela Properti terlihat sekarang. Di atas Anda dapat melihat semua Control yang ditambahkan ke dalam WorkSheet Anda bersama dengan Sheet Name. Secara default, kendali kontrol yang diberikan kepada Anda akan jatuh di tempat di mana Anda telah mengklik kanan dan memilih “Properties”. Dari sini Anda dapat memilih salah satu kontrol yang ingin Anda lihat di Property.

39

4.       Dalam Gambar di atas Anda dapat melihat properti dari CommandButton1 yang telah ditampilkan.

40

Di Atas Anda dapat melihat ada properti yang disebut “Name”. Ini adalah nama dari Control itu. Nama ini harus selalu unik dalam Workbook Excel sebagai penanda dari kontrol yang telah Anda ubah.

Menjadi kebiasaan yang baik untuk mengubah nama kontrol Anda berdasarkan fungsinya. Sehingga ketika Anda memiliki banyak kontrol, maka sangat mudah nantinya mengingat nama-nama kontrol saat menulis kode di dalam Microsoft Excel.

TUTORIAL EXCEL MACRO: BAGAIMANA MENULIS MACRO EXCEL PERTAMA ANDA

Tutorial excel macro kali ini akan membawa Anda untuk menulis macro excel lebih jauh lagi tidak hanya sekedar menulis “Hello” saja sebagai percobaan. Kali ini Anda akan dibimbing untuk benar-benar menggunakan angka sebagai contoh yang mungkin akan sangat Anda butuhkan di tempat kerja Anda. Dalam bagian tutorial ini, Anda akan belajar bagaimana untuk membuka VBE Code Window di Visual Basic Editor Workbook dan mulai menulis macro pertama Anda.

41

Anda sudah mendapat semua konsep dasar tentang Excel Macro. Jadi sekarang saya akan menunjukkan kepada Anda, bagaimana Anda dapat mulai menulis Excel Macro. Contoh: Anda akan menulis Excel Macro pertama Anda. Dengan menjalankan Excel Macro Anda akan menambahkan Nilai Cell A1 dan A2 dan kalikan Sum dengan nilai dari Cell A3. Simpan Hasil ini dalam sel A4. Dan inilah tutorial excel macro yang harus Anda lakukan.

Ikuti langkah-langkah di bawah ini, untuk menulis Excel Macro pertama Anda untuk menyelesaikan permasalahan di atas.

1.       Pertama pergi ke Visual Basic Editor di WorkSheet Anda.

2.       Double Klik pada nama sheet di bawah VBA Project di sisi kanan dari Visual Basic Editor.

3.       Copy dan Paste kode di bawah ini ke Code Window

Sub My_First_Macro()

Range(“A4″).Value = (Range(“A1″).Value + Range(“A2″).Value) * Range(“A3″).Value

End Sub

4.       Dalam A1, A2 dan A3, Anda telah memasukkan masing-masing angka 25, 30 dan 4.

42

5.       Sekarang setelah menjalankan macro di atas, Sum A1 dan A2 harus dikalikan dengan A3 dan hasilnya harus disimpan dalam A4.

6.       Sekarang Klik pada tombol Run untuk menjalankan macro

7.       Klik Run

43

8.       Setelah menjalankan Macro, lihat hasilnya

Selamat! Anda telah menjalankan dan menguji macro pertama Anda

9.       Sekarang pergi ke sheet Anda dan masukkan beberapa nilai Lain sebagai input dan tekan Alt + F8

44

10.   Sekarang hasil telah berubah setelah Anda menjalankan macro.

Setelah Anda mengetahui bagaimana menggunakan rumus excel pada macro untuk tugas rumit seperti di atas, maka Anda bisa mencoba dengan angka input Anda sendiri untuk mempraktekkantutorial excel macro.

 

45

KETAHUI PERBEDAAN DARI RANGE() DAN CELLS() PADA VBA EXCEL 2007

VBA Excel 2007 merupakan kode yang menarik untuk dipelajari untuk Anda yang tertarik untuk memahami excel macro. Jika Anda pernah mencoba untuk membaca dan memahami kode VBA, Anda akan melihat dua cara yang sering digunakan pada metode excel macro, yakni RANGE() dan CELLS(). Keduanya ini memiliki perbedaan. Contohnya ketika Anda merujuk suatu nilai pada cell D4, maka Anda bisa menggunakan metode Range(“B4″).Value. Sedangkan untuk fungsi CELLS() bisa digunakan misalnya untuk mengetahui nilai yang sama dari cell B4, seperti Cells(2, 4).Value di mana 2 adalah cell dan 4 adalah kolom.

Meskipun dari contoh di atas sepertinya keduanya sama karena mereka digunakan untuk merujuk sebuah cell di sheet excel, tetapi pada kenyataannya RANGE() dan CELLS() tidaklah sama. CELLS adalah properti dari RANGE atau WorkSheet atau Application Objects sementara RANGE sendiri merupakan Obyek. CELLS mengembalikan Object Range. CELLS dapat digunakan tanpa parameter atau satu parameter yang melewati di dalamnya. Karena CELLS sendiri bisa menjadi milik WorkSheet atau objek Range, karena itu CELLS memberikan hasil berdasarkan apa yang dilihat oleh objek. Berikut di bawah ini adalah beberapa contoh penggunaan kode RANGE dan CELLS padaVBA Excel 2007.

Range(“C3:F10″).Cells: Ini akan mengembalikan semua cell dari Range (C3: F10) dari C3 ke F10.

Range(“C3:F10″).Cells(1, 2): Ini akan mengembalikan cell di First Row (1) dan kolom kedua (2) dari Range (“C3: F10″). Oleh karena itu (“C3:F10″).Cells(1, 2) akan kembali ke Range(“D3″). 3

Range(“C3:F10″).Cells(3): Dalam cell ketika hanya ada satu parameter maka secara default dianggap sebagai cell pertama dan parameter yang lolos adalah kolom. Oleh karena itu Range(“C3:F10″).Cells(3) sama dengan Range(“C3:F10″).Cells(1, 3).

Seperti yang Anda tahu objek dari RANGE mengacu pada cell atau cell range dengan urutan baris dan nama kolom cell yang paling berguna ketika Anda harus merujuk cell / Range di dalam loop. Dalam hal ini maka cell sangat berguna, karena terdapat dua parameter yang numerik di sini dan Anda dapat menggunakannya untuk loop .Untuk itu, Anda bisa mempraktekkan baris kode di bawah ini:

Sub Cell_Traverse()

Dim iRow

Dim iCol

For iRow = 1 To 5 ‘traverse across rows

46

For iCol = 1 To 5 – traverese across columns in a Row

      Cells(iRow, iCol).Value = iRow & ” , ” & iCol

   Next

Next

End Sub

Kode di atas akan menampilkan tampilan seperti berikut di VBA Excel 2007.

Begitulah pelajaran macro pada rumus excel kali ini. Pada awal mengenalnya, memang terasa sangat sulit, namun ketika sudah dikuasai, VBA dalam excel ini benar benar sangat berguna.

 

MEMBUAT ISIAN DATA DI EXCEL MENGGUNAKAN FUNGSI VBA

Pada kesempatan ini, saya akan membahas tentang bagaimana cara membuat isian data di excel dengan menggunakan fungsi VBA. Seperti yang diketahui, VBA merupakan sebuah halaman padaMicrosoft Excel yang digunakan untuk menuliskan code visual basic, dengan code tersebut kita dapat membuat berbagai form yang dibutuhkan. Dengan VBA tersebut kita juga dapat memungkinkan membuat sebuah aplikasi di Microsoft Excel.

Tanpa panjang lebar, mari kita langsung saja untuk membuat isian data menggunakan fungsi VBA. Yang mana form isian data ini dapat memudahkan kita menginputkan data dengan cepat dan lebih aman. Berikut langkah-langkahnya.

1. Langkah pertama, siapkan terlebih dulu data tabel di lembar kerja Excel seperti berikut.

47

2.  Selanjutnya membuat tombol tambah, caranya arahkan kursor ke Tab Developer > Insert > CommandButton (ActiveX Control).

3.  Lalu posisikan tombol tersebut sesuai kebutuhan, ganti nama tombol dengan tulisan Tambah, caranya klik kanan pada tombol lalu pilih CommandButton Object >> Edit. Hasilnya seperti berikut.

4.  Kemudian klik kanan pada tombol Tambah, pilih View Code, maka akan tampil halaman Visual Basic for Application, kemudian isikan code berikut ini.

Private Sub CommandButton1_Click()

UserForm1.Show

End Sub

48

Seperti pada tampilan berikut. (catatan, simpan halaman dengan nama IsianData.xlsb)

5.  Kemudian klik kanan pada tulisan “VBAProject (IsianData.xlsb)”, lalu pilih insert >> UserForm, seperti tampilan berikut.

6.  Selanjutnya buat tiga buah TextBox dengan satu tombol CommandButton, contohnya seperti tampilan berikut.

7.  Kemudian double klik pada button Simpan, setelah itu masukan code seperti berikut.

Private Sub CommandButton1_Click()

Set wsDtbsBrg = Sheets(“Sheet1″)

RecordAkhir = wsDtbsBrg.Cells(wsDtbsBrg.Rows.Count, “B”). _

49

End(xlUp).Offset(0, 0).Row

With wsDtbsBrg

.Cells(RecordAkhir + 1, 1).Value = TextBox1.Value

.Cells(RecordAkhir + 1, 2).Value = TextBox2.Value

.Cells(RecordAkhir + 1, 3).Value = TextBox3.Value

End With

Unload Me

End Sub

Private Sub Textbox3_KeyPress(ByVal KeyAscii _

As MSForms.ReturnInteger)

Select Case KeyAscii

Case Asc(“0″) To Asc(“9″)

Case Else

KeyAscii = 0

End Select

End Sub

Seperti pada tampilan berikut. (catatan, setelah code di atas di tuliskan atau Anda bisa meng-copy pastenya, lalu save halaman?

50

8.  Setelah itu Anda kembali lagi ke lembar kerja Excel, untuk mengaktifkan tombol “Tambah”, Anda bisa arahkan kursor ke tab Developer >> Design Mode.

9.  Sekarang Anda coba klik tombol “Tambah”, jika tombol tersebut berfungsi maka akan muncul form dialog isian, dan coba Anda isi bagian Nama, Alamat dan No telepon, kemudian Klik Simpan, seperti berikut.

10.  Maka pada tabel data akan terisi sesuai dengan data yang di inputkan pada form isian data. Jika Anda ingin menambahkan data lebih banyak lagi, cukup klik tombol “Tambah” dan lakukan pengisian data seperti pada no 9 diatas.

51

Dengan demikian, jika Anda ingin mengisi data tabel hanya tinggal klik tombol “Tambah” saja, maka akan tampil form isian data.

RUMUS EXCEL UNTUK MENGHITUNG GAJI PEGAWAI

Materi pengnganti kursus microsoft excel kali ini adalah menghitung gaji pegawai berdasarkan jam kerja dengan rumus Excel. Meamang bisa? Mari kita lihat. Seperti yang sudah kita ketahui, formula atau rumus pada Excel itu sangat beragam. Aplikasi sakti ini mempunyai banyak manfaat dan kegunaan, salah satunya adalah digunakan untuk menghitung. Untuk Anda yang bekerja pada bagian keuangan atau administrasi kehadiran Microsoft Excel sangat membatu dalam menyelesaikan tugas Anda dengan cepat dan efisien.

Baik, pada pertemuan kali ini, akan mencoba membahas tentang penggunaan Excel yang berkaitan dengan menghitung. Apakah yang akan dihitung? Menghitung gaji pegawai yang bekerja pada suatu perusahaan atau usaha rumahan dengan upah kerja yang diterima berdasarkan jam kerja.

Misalnya : Rina adalah seorang bendahara keuangan yang tugasnya selain memanej keuangan, ia juga ditugaskan untuk menghitung gaji pegawai, pada perusahaan tempat Rina bekerja menerapkan sistem upah berdasarkan jam kerja, jika diketahui upah para karyawan per jam sebesar Rp 15.000. Berapakah jumlah gaji para pegawai yang harus di bayar?

Langkahnya, buat 2 buah tabel yaitu Data Pegawai dan Data Gaji Pegawai seperti berikut.

Pada tabel Data Pegawai digunakan sebagai referensi data dari para pegawai, sedangkan pada tabel Data Gaji Pegawai digunakan sebagai penghitungan gaji para pegawai.

52

Mari kita eksekusi, pada tabel Data Gaji Pegawai bagian No Pegawai (sel E6) cara mengisinya kita gunakan fungsi Combo Box. isi dengan no pegawai secara acak (misal P02 ada di sel E6). Dan lakukan hal yang sama pada sel E7 sampai E11.

Kemudian untuk mengisi Nama Pegawai (sel F6) masukan rumus berikut.

=IF(ISNA(VLOOKUP(E6,Data_Pegawai,2,FALSE)),”",VLOOKUP(E6,Data_Pegawai,2,FALSE))

Untuk mengisi seluruh Nama Pegawai kita gunakan fungsi Auto Fill, Hasilnya sebagai berikut.

Selanjutnya isi pada kolom Jam (Masuk dan keluar) secara manual.

Untuk mengisi Lama Kerja (sel I6) gunakan rumus =(H6-G6)*24, agar sel I6 sampai sel I11 dapat terisi secara otomatis gunakan fungsi Auto Fill.

Setelah Lama Kerja di dapat, maka selanjutnya mengisi Total bayar, dengan rumus berikut.

1.       Total Bayar (sel I6), =I6*F14

2.       Total Bayar (sel I6), =I7*F14

3.       Total Bayar (sel I6), =I8*F14

4.       Total Bayar (sel I6), =I9*F14

5.       Total Bayar (sel I6), =I10*F14

53

6.       Total Bayar (sel I6), =I11*F14

Keterangan : F14 adalah sel yang berisi nilai Upah yaitu sebesar Rp 15.000

Maka Rina harus mengeluarkan sejumlah uang untuk membayar gaji para pegawai berdasarkan jam kerja pegawai sebesar yang terdapat pada Total Bayar.

Kesimpulan, menghitung gaji pegawai berdasarkan jam kerja pada excel tidaklah sulit, hanya dengan memasukan beberapa rumus excel kita dapat bekerja lebih cepat dan efisien dibanding menghitung dengan manual atau menggunakan kalkulator.

Semoga bermanfaat, dan tentunya kita jangan lupa untuk terus belajar, belajar, dan belajar mengikuti kursus online microsoft excel ini hehe.  tetap semangat untuk meraih tujuan yang ingin dicapai.

FUNGSI IF (AND), IF (OR) DALAM EXCEL

 Tes Logika dalam Microsoft Excel yang menggunakan fungsi IF dapat dikembangkan dengan tambahan salah satu fungsi AND, OR atau NOT. Fungsi-fungsi tersebut merupakan tambahan untuk mengembangkan tes kondisi dalam suatu data tertentu.

Adapun dalam penerapannya fungsi AND, dan OR menggunakan maksimal 30 logika, sedang fungsi NOT hanya mempunyai satu argumen logika saja.

Berikut sintaksnya :

§  =IF(AND(logika 1, logika 2, logika 3,……..,logika 30)

Contoh : =IF(AND(A1<10,B1=20) artinya apabila semua nilai logika benar maka hasilnya pasti benar, bila ada satu atau semua bernilai salah maka hasilnya juga salah.

§  =IF(OR(logika 1, logika 2, logika 3,……..,logika 30)

Contoh : =IF(OR(A1<10,B1=20) artinya apabila salah satu atau semua dari nilai logika benar maka hasilnya benar, bila semua salah maka hasilnya salah.

§  =IF(NOT(logika)

Contoh : =IF(NOT(A1<10) artinya apabila nilai dari A1<10 itu benar maka hasilnya salah tapi bila bernilai salah maka hasilnya benar (logika terbalik)

54

Untuk lebih jelasnya kita lihat contoh penggunaan di bawah ini:

Ketentuan soal:

1. Untuk mencari Harga Jual Per unit:

Jika jenis pembelian = K (Kredit), maka harga jual = 1.25 x harga satuan harga

Jika jenis pembelian =T (Tunai), maka harga jual = HArga Satuan Normal

2. Untuk mencari Total Harga = Jumlah Beli x Harga Jual per Unit

3. Untuk Mencari Diskon:

Jika Pembelian dilakukan secara Tunai dan jumlah beli di atas 15, maka mendapat diskon sebesar 10% dari Total Harga.

4. Untuk mencari Voucer:

Jika membeli computer merek Axioo atau Acer, maka mendapat voucer 50.000

Jika membeli computer merek Toshiba, maka mendapat voucer 100.000

5. Untuk mencari total Bayar = Jumlah Beli x Harga Jual per Unit – Diskon.

55

Untuk menjawab soal di atas rumus yang mesti di tulis adalah sebagai berikut:

1. Rumus untuk mencari Harga Jual per Unit menggunakan fungsi IF Tunggal dan perkalian, yaitu Posisi kursor cell di G5, =IF(F5=”K”,1.25*D5,D5)

2. Rumus untuk mencari Total Harga di Cell H5 menggunakan operator matematika Perkalian yaitu=E5*G5

3. Rumus untuk mencari Diskon di Cell I5 menggunakan fungsi IF dengan tambahan Fungsi AND yaitu, =IF(AND(F5=”T”,E5>15),10%*H5,0)

4. Rumus untuk mencari Voucer di cell J5 menggunakan Fungsi IF Ganda dengan tambahan Fungsi OR yaitu,

=IF(OR(C5=”Axioo”,C5=”Acer”),50000,IF(C5=”Toshiba”,100000,0)) Tutup kurung yang terakhir ada 2 karena ada 2 IF yang di tulis.

5. Rumus untuk mencari Total Bayar di Cell K5 menggunakan operator metematika Perkalian dan Pengurangan yaitu, =(E5*G5)-I5  atau langsung saja  =(H5-I5)

Untuk rumus baris ke 6 sampai 9 cukup di copy dari baris 5 atau menggunakan Fill Handle.

Hasil dari penulisan rumus-rumus di atas adalah:

Catatan : Jika hasil rumus yang mucul tidak ada tanda koma, maka gunakan ikon Comma Styleyang ada di Group Number. Caranya select/blok semua data kemudian klik Comma Style.

§  Untuk menambahkan atau menyisipkan symbol mata uang (Rupiah/Rp), Blok data kemudian klik accounting number format yang ada di group number, pilih symbol Rp Indonesian dan klik, maka simbol Rp akan muncul. Jika tidak ada lanjutkan dengan meng-klik more accounting formats, pada sub tab symbol cari dan klik Rp Indonesian kemudian OK.

Demikian pembahasan Fungsi IF dengan tambahan OR dan AND, jika ingin mebaca lebih lanjut penggunaan fungsi rumus if dalam excel ini, lihat artkel berikut:

56

Penggunaan Fungsi Rumus IF dalam Microsoft Excel

MEMBUAT MACRO DI EXCEL 2010 DAN CARA PENGGUNAANNYA

Membuat macro di Excel 2010 adalah salah satu hal yang berikutnya akan kita pelajari. Ini adalah pembahasan yang sudah pernah dijanjikan untuk dibahas pada ulasan pengenalan fungsi macro pada excel 2010. Pada pembahasan yang lalu, kita sudah mengenal apa itu macro. Namun tak ada salahnya untuk menyegarkan kembali ingatan kita tentang apa itu macro. Macro benar-benar alat yang hebat. Hal ini memungkinkan Anda untuk melakukan beberapa operasi hanya dengan mengklik tombol saja. Hal ini memungkinkan Anda untuk bekerja dengan cara yang cerdas dan efisien. Terutama untuk hal produktivitas, cara kerja macro ini dapat mengurangi banyak pekerjaan manual.

Macro adalah perangkat lunak yang sangat populer yang tersedia di sebagian besar aplikasi Windows. Banyak aplikasi Windows menyediakan program Macro secara built-in. Salah satunya adalah di dalam rumus Excel. Tanpa menunda lebih banyak waktu lagi, berikut ini adalah bagaimana cara Anda untuk membuat macro di Excel 2010.

Cara pertama yang harus Anda lakukan adalah seperti di ulasan sebelumnya, yakni mengaktifkan Developer Tab seperti berikut:

1.       Klik tab File dan pilih Options. Kotak dialog Options Excel akan muncul.

2.       Klik Customize Ribbon di sisi kiri kotak dialog.

3.       Centang kotak Developer dan klik OK.

57

Sekarang Anda dapat mengklik tab Developer yang telah ditempatkan di samping tab View.

1.       Klik Insert.

2.       Klik Command Button di bagian ActiveX Controls.

58

3.       Sekarang Anda dapat menyeret kursor pada lembar kerja Anda

Sekarang saatnya untuk membuat kode macro dan melihat hasilnya.

1.       Klik kanan pada CommandButton1

2.       Klik View Code.

3.       Visual Basic Editor muncul. Tempatkan kursor Anda antara ‘Private Sub CommandButton1_Click ()’ dan ‘End Sub’.

4.       Misalnya, tambahkan baris kode berikut:

Range(“A1″).Value = “Hello”

59

Kode baris tersebut akan menempatkan kata Hello ke dalam baris A1.

5.       Tutup Visual Basic Editor

6.       Sebelum Anda mengklik tombol perintah pada sheet, pastikan Design Mode dalam keadaan tidak terpilih. Anda dapat melakukan ini dengan mengklik Design Mode lagi.

Hasil yang akan Anda dapatkan ketika Anda mengklik tombol perintah pada sheet:

Selamat! Anda sudah bisa membuat macro di Excel 2010. Jangan lupa ikuti terus tutorial sederhana rumus excel khusus macro ini. 

 

CARA UNTUK MEMPROTEKSI WORKBOOK

Protect Workbook Anda, jika Anda bingung untuk memproteksi pekerjaan Anda, dan jika Anda takut hasil pekerjaan Anda akan dicopas atau pun diambil alih oleh tangan yang salah, maka Anda dapat melakukan perlindungan kepada workbook Anda, dan jika Anda melakukan perlindungan terhadap workbook Anda, maka Anda melindunginya dari keamanan untuk tidak bisa sembarangan delete, rename, move, copy, hide ataupun unhide bagi user lain. Dan langkah – langkahnya akan dibahas dibawah ini:

Open a workbook

Bukalah 1 buah workbook seperti excel di windows Anda.

60

Di tab review, klik protect workbook

Klik pada Protect workbook dan jika sudah meng kliknya biasanya akan muncul sebuah tampilan yang akan membuat Anda harus memasukkan password ( password yang dimaksud bukan lah password computer Anda ) dan password yang dimaksud adalah password dari file yang Anda buat tersebut.

Check Structure, masukkan password dan kemudian OK

Jangan lupa untuk mengecek tanda structurenya, dan kemudian Anda akan memasukkan password do bagian Password ( optional )

Masukkan kembali password dan kemudian klik OK

61

Dan ini Anda akan disuruh untuk memasukkan kembali password yang telah Anda ketik tadi, dan jangan memasukkan password yang baru, dan sesuai pesan diatas, jika Anda kehilangan atau lupa password, ini tidak bisa di recoverd, dan ini akan sangat disarankan kepada Anda untuk membuat sebuah list password dan buatlah koresponding dari password yang telah Anda buat tadi, dan nama – nama dari sheetnya itu di tempat yang benar – benar aman, dan perlu diingat bahwa passwords yang dimasukkan kesini case – sensitive. Jadi untuk huruf besar maupun kecil perlu diperhatikan.

User yang lain tidak bisa melakukan insert, edit, move, copy, hide, atau unhide di worksheets tersebut tanpa mengunlock password di worksheet tersebut.

Untuk unprotect workbook nya, click untuk masukkan passwordnya, dan sesudah memasukkan passwordnya, maka file tersebut sudah bisa dieksekusi, atau diedit, dan di insert maupun selainnya.

MENGATUR NOMOR URUT YANG TIDAK BERATURAN DENGAN RUMUS EXCEL

Di dalam salah satu postingan kami terdapat bahasan tentang mengatur nomor urut dengan rumus microsoft excel yang penerapannya sesuai dengan data yang ada. Kali ini kami akan melanjutkan pembahasannya mengenai pengaturan penomoran yang tidak berurutan, maksudnya nomor urut itu akan muncul dengan tidak berurutan sesuai data, kalau datanya urut nomor itu akan urut tetapi jika datanya tidak urut maka nomor yang muncul juga tidak urut.

Coba lihat data di bawah ini;

62

Data di atas sengaja kami tulis tidak runtut (loncat-loncat) dan untuk menampilkan nomor urut yang belum ada (yang diberi tanda tanya), maka diperlukan rumus/fungsi excel untuk memunculkannya.

Untuk kasus data di atas, yang akan kita munculkan adalah nomor urut yang tidak berurutan/runtut

Mari kita masukkan dulu fungsi yang dibutuhkan dan selanjutnya lihat hasilnya.

Tulis rumus pada cell A4

=IF(B4=”",”",ROWS($B$5:B5))

Dan lihat hasilnya sebagai berikut;

63

Sekali lagi nomor urut akan muncul jika ada datanya, jika datanya tidak urut maka nomor urut juga tidak urut sebagaimana gambar di atas.

Bandingkan dengan materi Mengatur Nomor Urut dengan Rumus Excel sebagai salah satu contoh penggunaan rumus if sebelumnya.

Demikian pengaturan nomor urut semoga bermanfaat, dan mempermudah kita dalam mengerjakan tugas atau pekerjaan kita sehari-hari.

CARA MENGHITUNG BIAYA SEWA DVD/CD RENTAL DI EXCEL

Pada kesempatan ini, saya akan mencoba membahas meneganai cara menghitung biaya sewa DVD/CD pada Microsoft Excel. Pembahan ini mungkin bisa sedikit membantu bagi para pemiliki rentalan untuk menghitung berapa jumlah uang yang harus dibayarkan oleh para penyewa. Pada prinsinya, untuk menghitunga biaya sewa ini menggunakan fungsi kombinasi seperti fungsi IF dan Date. Untuk mengetahui mengenai cara penghitungan ini, berikut ulasannya.
Misalnya, sebuah rentalan menyewakan DVD dengan harga Rp 5000 per hari dan CD dengan harga Rp 2500 per hari. Dengan biaya denda keterlambatan pengembalian untuk DVD seharga Rp 2000 per hari dan CD seharga Rp 1000 per hari.
1. Langkah pertama, buat data tabelnya sebagai berikut.

2. Pada kolom Batas Sewa cell C3 ketikan rumus =IF(B3>3,3,IF(B3>2,2,1)), untuk mengisi cell C4 sampai cell C7 gunakan fungsi Auto Fill

64

3. Selanjutnya untuk mengisi kolom Lama sewa pada cell F3 ketikan rumus =E3-D3, dan pada kolom Status Sewa pada cell G3 ketikan rumus =IF(F3>C3,”denda”,”tidak”).

4. Selanjutnya untuk mengisi kolom Biaya Denda pada bagian cell H3 ketikan rumus =IF(LEFT(A3,1)=”C”,(F3-C3)*1000*B3,(F3-C3)*2000*B3), gunakan fungsi Auto Fill untuk mengisi cell H4 sampai cell H7.

5. Selanjutnya untuk mengisi kolom Biaya Sewa pada bagian cell I3 ketikan rumus ini =IF(LEFT(A3,1)=”C”,B3*2500,B3*5000), gunakan fungsi Auto Fill untuk mengisi cell I4 sampai cell I7.

6. Selanjutnya untuk mengisi kolom Total bayar pada cell J3 ketikan rumus =H3+I3, gunakan fungsi Auto Fill untuk mengisi cell J4 sampai cell J7. Maka hasilnya akan seperti berikut.

Maka didapatlah total bayar dari penyewaan DVD/CD, jika para penyewa meminjam DVD/CD lebih dari batas sewa yang ditentukan, maka akan mendapat denda dengan biaya yang telah ditentukan per harinya.

RUMUS EXCEL MENGHITUNG JAM KERJA BERDASARKAN SHIFT

Kembali kita belajr rumus-rumus excel untuk membantu meringankan pekerjaan sehari hari. Kalau kita belom sempat ikut kursus komputer terutama program excel, jangan kuwatir, materi-materi disini juga cukup lumayan bagus. Kali ini kita belajar rumus Excel untuk menghitung jam kerja berdasarkan shift.

Bagi sebagian perusahaan jam kerja merupakan salah satu peran penting dalam menentukan lamanya kerja seorang pegawai, Jam kerjanya pun ada yang memakai sistem Shift atau pembagian waktu kerja agar jam kerja pegawai dapat termenej dengan baik. Maka dari itu, pada kesempatan ini akan mencoba menghitung Jam Kerja Berdasarkan Shift dengan bantuan Microsoft Excel.

Terlebih dahulu siapkan lembar kerja Excel dan buat tabel datanya sebagai berikut.

65

Pada tabel jam kerja terdiri dari jam masuk dan jam keluar berdasarkan Shift I dan shift II, lalu tabel Istirahat I (Shift I) dan istirahat II (Shift II) masing-masing diberikan waktu setengah jam saja, Sekarang akan berfokus pada tabel jumlah jam kerja berdasarkan shift, disana telah tersedia data nama, shift kerja, jam masuk, dan jam keluar.

Mengisi kolom Masuk bagian status sel E11 atau khusus shift I. caranya masukan rumus berikut.

=IF(C11=”I”,IF(D11>C4,”Telat”,”Ok”),IF(D11>D4,”Telat”,”Ok”))

Dan untuk mengisi status khusus Shift II sel E14, rumusnya sebagai berikut.

=IF(C14=”II”,IF(D14>C5,”Telat”,”Ok”),IF(D14>D5,”Telat”,”Ok”))

Selanjutnya untuk mengisi kolom Keluar bagian status pada sel G11, masukan rumus berikut.

=IF(C11=”I”,IF(F11>C4,”Pulang Awal”,”Ok”),IF(F11<D4,”Pulang Awal”,”Ok”))

Dan untuk status keluar khusus Shift II sel G14, masukan rumus berikut.

=IF(C11=”II”,IF(F11>C5,”Pulang Awal”,”Ok”),IF(F11<D5,”Pulang Awal”,”Ok”))

Setelah rumus diatas dimasukan semua ke kolom yang kosong, maka hasilnya sebagai berikut.

66

Dari keterangan status diatas, pada jam masuk jika pegawai masuk tepat jam 8:00 maka statusnya OK atau tepat waktu, tetapi jika pegawai masuk melebihi jam 8:00 maka statusnya telat. Begitu pun berlaku untuk status di jam keluar.

Selanjutnya untuk mengisi kolom Jumlah Jam pada sel H11, masukan rumus =F11-D11-I4 khusus shift I dan  untuk shift II pada sel H14 masukan rumus =F14-D14-I5.

Maka jumlah jam pegawai sudah di dapat, tentunya dengan adanya tabel penghitungan jumlah jam kerja dapat membantu menentukan prestasi pegawai, jika pegawai bekerja dengan tepat waktu, kemungkinan pegawai akan menyandang predikat pegawai teladan.

CARA MENGHITUNG PAJAK PPH 21 DENGAN EXCEL

Materi kursus online microsoft excel kali ini berusaha menjawab pertanyaan – Bagaimana cara menghitung Pajak Penghasilan Pasal 21 atau PPh 21 dengan Excel? Ini merupakan suatu pertanyaan yang akan ditanyakan Wajib Pajak atau orang yang terkena pajak, jika mendapat rekomendasi untuk menghitung PPh 21 terutang dengan bantuan Excel.

Caranya tentu sangat mudah, dengan menggunakan beberapa fungsi dan rumus penghitungan PPh 21 dengan bantuan Excel ini bisa diselesaikan dengan cepat dan tepat. Mari jangan berlama-lama, kita langsung saja kepada prakteknya menghitung PPh 21 terutang.

Langkah pertama, siapkan 2 (dua) Worksheet atau lembar kerja pada Excel.

1. Worksheet pertama (Hitung PPh 21), berisikan data penghitungan PPh 21. Misalnya Wajib Pajak memperoleh penghasilan per bulan Rp 2.500.00 maka penghasilan disetahunkan Rp 30.000.000 sebagai berikut.

67

2. Worksheet kedua (Data Tarif), berisikan data Tarif PPh 21 sebagai berikut.

68

Selanjutnya untuk menentukan Status tanggungan pada Worksheet pertama, gunakan fungsi Combo Box, data dari Status tanggungan tersebut didapat dari Worksheet kedua. Misal status K/1 (Menikah dengan 1 tanggungan anak) adalah Rp 28.350.000

Selanjutnya mengisi penghasilan bruto, dengan masukan =E6 pada sel E11. Kemudian untuk menghitung biaya jabatan tempatkan kursor pada sel E13 dan isikan =IF((E11*0.06)>6000000,6000000,(E11*0.06)). Mka hasilnya sebesar Rp 1.800.000, ini didapat dari 15 % x 30.000.000.

Selanjutnya untuk menghitung penghasilan neto adalah =E11-E13 atau penghasilan bruto dikurangi biaya jabatan hasilnya Rp 28.200.000, untuk menentukan besar PTKP hasilnya akan sama dengan status tanggungan Rp 28.350.000.

69

 kemudian untuk jumlah PKP yaitu =E14-E15 atau jumlah penghasilan neto dikurangi PTKP hasilnya Rp 150.000.

Setelah jumlah PTKP dan PKP didapat, maka selanjutnya menghitung PPh 21 terutang, caranya jumlah PKP di kali tarif pasal 17 ayat 1 huruf a UU PPh, yaitu Rp 150.000 x 5% atau pada sel E18 isikan =D18*B18 maka hasilnya adalah sebesar Rp 7.500. Dan Jumlah PPh 21 terutang yang harus dibayarkan kepada kantor pajak adalah sebesar Rp Rp 7.500 per bulan.

Kesimpulan, Menghitung PPh 21 dengan Excel ini sangat membantu untuk Wajib Pajak, karena seperti yang diketahui, dalam melakukan pembayaran PPh 21 terutang, Wajib Pajak diberi tanggung jawab untuk dapat menghitung sendiri jumlah PPh 21 terutang tersebut. Maka dengan adanya fungsi dan rumus PPh 21 Excel ini semoga dapat membantu Wajib Pajak dan untuk lebih lengkap pembahasan pajak penghasilan ini lihat:

Tarif Pajak Penghasilan PPh 21 dan Contoh Perhitungannya

 

70

MEMBUAT INVOICE TAGIHAN PEMBAYARAN DENGAN EXCEL

Pembuatan invoice tagihan memanfaatkan microsoft excel adalah materi kursus excel kita kali ini. Istilah invoice mungkin sudah tidak asing lagi bagi para pengusaha yang bergelut dibidang penjualan barang atau jasa, invoice disebut juga sebagai surat bukti tagihan pembayaran yang berkaitan dengan transaksi antara penjual dan pembeli.

Pada pertemuan ini, akan membahas mengenai pembuatan invoice untuk tagihan pembayaran atas penjualan barang pada PT.Sumber Rejeki kepada pihak Toko Sahira Permata selaku pembeli. Data yang harus disiapkan adalah data Surat tagihan (invoice), Daftar Customer, dan Harga Barang, data tersebut masing-masing di buat pada 3 buah worksheet. Seperti pada tampilan berikut.

1. Pada worksheet pertama, kita buat bentuk surat tagihan pada excel yang didalamnya terdapat data customer yang terdiri dari nama, alamat, kota, dan telepon. Kemudian tabel data berupa Nama Barang, Jumlah, Harga, dan Total, serta subtotal, diskon dan Total bayar.

2. Pada worksheet kedua, buat tabel Daftar Customer, yang terdiri dari Nama Toko, Alamat, Kota, dan No.Telp, serti tampilan dibawah ini.

3. Pada worksheet ketiga, buat tabel Harga Barang, yang terdiri dari Nama Barang, dan Harga satuan.

71

Setelah data-data dibuat, selanjutnya mengisi data pada worksheet pertama, kita memanfaatkan fungsi Combo Box untuk mengisi data pada bagian customer dan Nama Barang. Untuk fungsi Combo Box sendiri sebelumnya telah dibahas, jadi disini tidak perlu di bahas lagi. Pada kolom Jumlah bisa di isi secara manual.

Selanjutnya pada bagian kolom Harga, masukan rumus berikut :

1. Rumus Harga sel F12,

=IF(ISNA(VLOOKUP(C12,Master_Harga,2,FALSE)),”",VLOOKUP(C12,Master_Harga,2,FALSE))

2. Rumus Harga sel F13,

=IF(ISNA(VLOOKUP(C13,Master_Harga,2,FALSE)),”",VLOOKUP(C13,Master_Harga,2,FALSE))

3. Rumus Harga sel F14,

=IF(ISNA(VLOOKUP(C14,Master_Harga,2,FALSE)),”",VLOOKUP(C14,Master_Harga,2,FALSE))

4. Rumus Harga sel F15,

=IF(ISNA(VLOOKUP(C15,Master_Harga,2,FALSE)),”",VLOOKUP(C15,Master_Harga,2,FALSE))

5. Rumus Harga sel F16,

=IF(ISNA(VLOOKUP(C16,Master_Harga,2,FALSE)),”",VLOOKUP(C16,Master_Harga,2,FALSE))

6. Rumus Harga sel F17,

72

=IF(ISNA(VLOOKUP(C17,Master_Harga,2,FALSE)),”",VLOOKUP(C17,Master_Harga,2,FALSE))

7. Rumus Harga sel F18,

=IF(ISNA(VLOOKUP(C18,Master_Harga,2,FALSE)),”",VLOOKUP(C18,Master_Harga,2,FALSE))

Catatan : Pada rumus Harga, kode “Master_Harga” di dapat dari worksheet ketiga pada tabel Harga Barang. Caranya blok tabel Harga Barang, klik kanan pilih define name, isikan name dengan Master_Harga.

Hasil pada worksheet pertama akan seperti berikut.

Selanjutnya mengisi kolom Total pada sel G12, dengan rumus =IF(ISERR(F12*E12),”",F12*E12)

Untuk mengisi keseluruhan data pada kolom total dapat memanfaatkan fungsi Auto Fill, maka hasilnya seperti berikut.

73

Selanjutnya mengisi kolom Total pada sel G12, dengan rumus =IF(ISERR(F12*E12),”",F12*E12)

Untuk mengisi keseluruhan data pada kolom total dapat memanfaatkan fungsi Auto Fill, maka hasilnya seperti berikut.

Jadi tagihan pembayaran untuk toko Sahira Permata sebesar Rp 9.641.250 yang harus dibayarkan kepada PT.Sumber Rejeki.

Kesimpulan, pada dasarnya  pertemuan kali ini membahas tentang penghitungan dengan metode penggabungan pada Excel, yaitu menggabungkan rumus IF dengan Vlookup. Namun di presentasikan dalam bentuk Invoice (Tagihan Pembayaran).

MEMBUAT STATISTIK DATABASE GOLONGAN PEGAWAI DENGAN EXCEL

Anggap saja ini adalah pengganti mengikuti kursus microsoft excel dan materi kita kali ini adala cara membuat statistik Database Golongan Pegawai dengan Excel. Sebelum memulai percobaanrumus excel gabungan ini, Alangkah baiknya berkenalan terlebih dahulu dengan Fungsi Statistik Database. Menurut pengertiannya, bahwa fungsi ini antara lain digunakan untuk menghitung, menjumlah atau mencari nilai tertentu pada suatu database dengan menggunakan syarat-syarat atau kriteria tertentu.

Rumus fungsi Statistik Database itu terdiri dari fungsi DSUM, DCOUNT, DMAX, DMIN, DAVERAGE, SUMIF dan Fungsi COUNTIF. Diantara fungsi-fungsi tersebut sedianya telah di bahas beberapa waktu lalu, Nahh…Sekarang kita akan mencoba menggabungkan fungsi-fungsi tersebut untuk menentukan statistik data pegawai berdasarkan pada kriteria jenis golongan gaji yang diterima.

Mari kita coba praktekan, pertama yang harus disiapkan adalah membuat data pegawai menurut golongan dan gaji, serta membuat tabel statistik yang akan di tentukan berdasarkan kriteria golongan

74

Keterangan: B5:E15 adalah daerah database yaitu semua data termasuk judul. Dan E23:E24 adalah daerah kriteria atau syarat berupa golongan III.

Selanjutnya kita akan melakukan pengisian data pada sel E17 sampai dengan E21 dengan rumus berikut.

a.  RUMUS E17  : =DSUM(B5:E15,4,E23:E24)

b.  RUMUS E18  : =DCOUNT(B5:E15,4,E23:E24)

c.  RUMUS E19   : =DAVERAGE(B5:E15,4,E23:E24)

d.  RUMUS E20  : =DMIN(B5:E15,4,E23:E24)

e.  RUMUS E21  : =DMAX(B5:E15,4,E23:E24)

Dari rumus diatas didapatkan hasil sebagai berikut

75

Data statistik gaji pegawai berdasarkan golongan III adalah berupa jumlah gaji sebesar Rp 9.500.00, jumlah pegawai sebanyak 3 orang, dengan memiliki rata-rata gaji sebesar Rp 3.166.667, berdasarkan golongan III gaji terendah sebesar Rp 3.000.000, dan gaji tertinggi sebesar Rp 3.300.000

Begitulah contoh penggunaan microsoft program excel untuk fungsi statistik database golongan pegawai. Kita explor kesaktiannya si excel ini agar bisa membantu pekerjaan kita sehari hari. Terlalu muluk muluk memang kalau tulisan sederhana begini ingin dianggap sebagai sebuah materi kursus online tentang menjalankan aplikasi microsoft excel, tapi setidaknya dengan blog sederhana ini bisa sedikit membantu pemakai program spreadsheet microsoft,

MENGHITUNG KORELASI PENDAPATAN DAN PENGELUARAN KEUANGAN PADA EXCEL

Korelasi biasanya dipakai untuk memeriksa hubungan antara data yang satu dengan data lainnya, misalnya memeriksa data keuangan antara pengeluaran dan pendapatan, apakah memiliki korelasi yang seimbang atau justru memiliki nilai korelasi yang kurang kuat atau menurun? Saya yakin anda sudah tahu rumus statistiknya untuk menghitung korelasi tersebut, Jika anda belum tahu maka perhatikan rumus statistik untuk menghitung korelasi dibawah ini.

Nilai korelasi itu berada diantara -1 sampai dengan 1. Jika nilainya positif maka bisa dikatakan kedua variabel tersebut mempunyai hubungan yang positif atau searah maksudnya kenaikan nilai variabel x juga akan diikuiti oleh kenaikan nilai variabel y begitu pula sebaliknya, namun jika nilainya negatif maka bisa dikatakan kedua variabel tersebut mempunyai hubungan yang negatif atau berlawanan arah maksudnya kenaikan nilai variabel x akan diikuti dengan penurunan nilai variabel y.

76

Adapun fungsi untuk menghitung korelasi variabel x dan y pada Microsoft Excel, yaitu fungsi CORREL dengan rumus =CORREL(array1, array2) dimana :

§  array1 = rentang sel yang berisi nilai variabel x

§  array2 = rentang sel yang berisi nilai variabel y

Untuk lebih jelasnya bagaimana menghitung korelasi yang diterapkan pada data keuangan antara pendapatan dan pengeluaran, bisa Anda simak ulasannya berikut ini.

Buat data keuangan sederhana seperti dibawah ini.

Dari data diatas akan dihitung nilai korelasi antara pendapatan dan pengeluaran selama periode 8 bulan (Januari – Agustus). Caranya letakan kursor pada cell D12, lalu ketikan rumus =CORREL(C3:C10,D3:D10), tekan enter. Maka hasilnya akan seperti berikut.

77

Maka akan diperoleh nilai korelasinya sebesr 0,35. Hal ini menunjukan bahwa korelasi antara pendapatan dan pengeluaran keuangan tidak terlalu kuat. Karena jika dilihat dari data tabel diatas pengeluaran lebih besar dari pendapatan. Sehingga dengan demikian, perlu untuk menekan angka pengeluaran agar dapat seimbang atau tidak jauh nilai korelasinya dengan pendapatan yang diperoleh selama bekerja atau selama menjual produk atau jasa. 

 

MEMBUAT LAPORAN PEMESANAN MAKANAN DENGAN FUNGSI VLOOKUP PADA EXCEL

Dalam membuat suatu laporan kita kadang memasukan data yang akan dibuat laporan tersebut dilakukan secara manual baik menggunakan program Ms.Excel maupun Ms.Word. Hal tersebut hanya akan memakan waktu saja, Kini tidak usah khawatir lagi, dengan menggunakan fungsi Vlookup pada Ms.Excel kita dapat membuat laporan secara otomatis hanya dengan beberapa perintah saja.

Sebagai contoh, membuat suatu laporan pemesanan makanan tentunya setiap hari data pemesanan makanan tersebut akan terus bertambah. Kemudian pada tabel laporan Anda hanya perlu mengisikan data tanggal, nama pembeli serta kode makanan saja. Sementara data Nama Makanan dan Harga akan terisi secara otomatis. Pengisian data secara otomatis ini bisa dicapai dengan melihat tabel referensi pada tabel Referensi Menu Makanan dan tentunya laporan tersebut akan berjalan secara otomatis dengan menggunakan fungsi Vlookup.

Untuk lebih jelasnya. Mari kita mencobanya bersama dengan langkah-langkah sebagai berikut.

1.  Terlebih dahulu membuat tabel Referensi Menu Makanan, lalu simpan dengan nama Rumus Excel Vlookup. Tampilannya sebagai berikut.

Pada lembar kerja tabel Referensi Menu Makanan di atas diberi nama Referensi. Seperti terlihat pada tampilan pojok kiri bawah dari layar PC atau Laptop Anda.

Kemudian blok sel B6 sampai sel D10, klik kanan pilih Define Name, isikan Makanan pada bagian Name, lalu klik OK. seperti tampilan berikut.

78

Setelah itu lihat gambar berikut

Tujuannya untuk memudahkan ketika nanti dibuatkan laporan, data pada lembar kerja Referensi ini bisa terbaca pada lembar kerja Laporan.

2. Selanjutnya membuat tabel Laporan Pemesanan Makanan. Tabel ini dibuat pada lembar kerja berikutnya atau berada disamping dari lembar kerja Referensi (Masih dalam satu dokumen Rumus Excel Vlookup)

3. Salnjutnya pada lembar kerja laporan, terlihat Nama Makanan dan Harga masih kosong, maka sekarang kita akan mengisinya secara otomatis dengan fungsi Vlookup. Ketikan rumus kode berikut pada sel E6.  =VLOOKUP(D6,Makanan,2,FALSE).

79

Setelah itu lihat hasil tabel diatas menjadi dibawah ini

Untuk mengisi data pada sel selanjutnya yaitu sel E7 dan E8 dapat menggunakan Auto Fill

4. Selanjutnya untuk mengisi sel F6 berupa Harga, caranya sama seperti pada mengisi nama makanan dengan menggunakan fungsi VLOOKUP, namun pada bagian col_index_num diisi dengan angka 3 agar sesuai dengan data harga pada referensi, rumusnya seperti ini =VLOOKUP(D6,Makanan,3,FALSE)

Maka hasilnya sebagai berikut akan seperti dibawah ini.

Untuk mengisi data pada sel selanjutnya yaitu sel F7 dan F8 dapat menggunakan Auto Fill excel

Pada dasarnya kita selalu ingin dihadapkan pada sesuatu hasil yang serba instant, tujuannya agar lebih praktis, Maka rumus fungsi Vlookup untuk membuat laporan penjualan makanan ini bisa menjawabnya saat kita membutuhkan solusi yang cepat dalam menyelesaikan pekerjaan.

 

80

MEMBUAT BILLING WARNET SEDERHANA DENGAN EXCEL

Warnet, siapa yang tidak kenal dengan salah satu bentuk usaha yang satu ini, yang senantiasa menyediakan layanan berupa koneksi internet. Warnet ini tentunya berhubungan dengan transaksi yang dikendalikan oleh billing, Billing warnet ini dapat mencatat semua kegatan transaksi dari jasa penggunaan internet.

Billing ini berupa aplikasi yang di install pada PC (Personal Komputer), Namun aplikasi billing ini tentunya tidak gratis, harga software billing warnet yang ditawarkan begitu beragam dan cukup mahal. Dengan kesaktian Excel, sekarang kita dapat membuat sendiri billing warnet dengan mudah. Berikut ini akan dibahas bagaimana membuat billing warnet dengan mudah dengan bantuan Microsoft Excel.

Langkah pertama, siapkan data-data seperti pada tampilan Excel berikut ini.

Langsung saja, ceritanya ada seorang client menggunakan warnet dengan waktu mulai dan waktu selesai yang telah kita ditentukan seperti berikut.

Kemudian stelah diketahui waktu mulai dan waktu selesai, kita akan mengisi durasi waktu yang dipakai saat menggunakan internet. Caranya pada sel G5 kita masukan rumus =TEXT(F5-E5,”hh:mm”) dan untuk mengisi sel G6 sampai G11 kita gunakan Auto Fill.

Durasi waktu sudah kita dapatkan, terasa sangat mudah kan? Tidak terasa sekarang kita harus mengisi Total Bayar dari durasi waktu yang telah ada.

81

Mari kita isikan Sel H5 dengan rumus =D5*ROUND((G5-INT(G5))*24,1), sama seperti pada durasi waktu gunakan auto fill untuk mengisi Sel H6 sampai H11.

Maka akhirnya kita bisa dapatkan Total Bayar dari para client yang menggunakan jasa warnet tersebut, jika kita bahas sedikit, nama client Hidayat, ia mulai menggunakan internet pada pukul 9:00, lalu beberapa saat kemudian ia selesai pada pukul 10:30, ia hanya memakai internet dengan durasi 1 setengah jam sajam, kemudian ia harus membayar Total Bayar Rp 4.500. Kok, bisa begitu?

Jika diuraikan dari rumus Total Bayar, kita akan mengetahui triknya, rumus pada Sel H5 merupakan rumus untuk mengubah waktu ke dalam bentuk desimal. Sehingga dapat diolah (dikalkulasi) dengan angka desimal. Sedangkan D5 sendiri merupakan Cell yang berisi harga penyewaan internet per jam. Jadi Totalnya adalah BIAYA SEWA x WAKTU SEWA (sel D5 x G5).

Kemudian kita juga bisa mengisi kembali Waktu Mulai dan Waktu selesai dengan cepat, hanya dengan menekan tombol pada keybord (CTRL + : ).

Kesimpulan, Microsoft Excel memang aplikasi yang terlihat sederhana, namun apabila kita mengerti bagaimana menggunakannya, ada banyak hal yang dapat kita buat dengan aplikasi ini.

MENGUBAH NILAI ANGKA MENJADI HURUF DALAM PENULISAN NILAI RAPORT

Rumus excel lengkap – Perkembangan tekhnologi mengharuskan kita untuk selalu terus mengembangkan pengetahuan dan ketrampilan, terutama dalam hal ini di bidang komputerisasi, why? Komputer sudah menjadi kebutuhan kita sehari-hari, baik dalam urusan perkantoran, perusahaan, sekolah, bahkan dalam hal penulisan Raport pun sekarang sudah membutuhkan komputer.

Jadi penting bagi kita untuk terus menggali perkembangan dan fasilitas yang ada di komputer sehingga pekerjaan kita bisa menjadi lebih cepat selesai, efesien dan akurat. Bagi seorang guru, sekarang di tuntut untuk menguasai beberapa program aplikasi terutama dalam hal ini  Microsoft Excel karena guru sering berhubungan dengan nilai atau angka yang harus di kelolah. Apalagi sekarang dalam pembuatan Hasil Belajar (Raport) sudah menggunakan komputer terutama program Ms. Excel.

82

Dalam postingan kali ini penulis ingin berbagi pengetahuan dalam hal penulisan rumus untuk merubah nilai Angka menjadi Huruf dalam penulisan Raport Siswa. Topik ini sebenarnya menjadi materi dasar pada kebanyakan kursus-kursus  microsoft excel. Meski begitu beberapa dari kita kadang masih sedikit kebingungan. Kaidah penulisannya tergantung dari masing-masing kebijakan sekolah, ada yang menggunakan nilai satuan dengan dua angka di belakang koma; contoh 7,65, dan ada yang menggunakan nilai puluhan dengan satu angka di belakang koma; contoh 76,5.

Adapun fungsi atau rumus yang di gunakan merupakan gabungan atau kombinasi dari beberapa rumus sehingga menghasilkan tujuan yang dimaksud.

Langkahnya adalah, terlebih dahulu kita harus membuat tabel data angka beserta huruf, sebagaimana contoh di bawah ini

Tabel ini nantinya akan digunakan sebagai dasar dalam penulisan format huruf menggunakan bantuan sebuah formula sederhana

Kemudian buatlah format Raport yang akan di isi, misalnya

83

Setelah ada data tabel dan format raportnya sekarang kita masukkan rumusnya mengubah angka menjadi huruf sebagai berikut;

Melihat data di atas berarti kita memasukkan rumus pada sel D8, (merubah nilai Angka menjadi Huruf).

Rumus yang digunakan adalah:

=VLOOKUP(LEFT(C8,1)*1,$F$3:$G$12,2)&” Koma “&VLOOKUP(MID(C8,3,1)*1,$F$3:$G$12,2)&” “&VLOOKUP(RIGHT(FIXED(C8),1)*1,$F$3:$G$12,2)

84

Untuk lebih jelas lihat hasil perumusan di bawah ini.

Catatan :
jika komputer anda menggunakan setting Bahasa Indonesia maka gunakan tanda koma (,) sebagai pemisah angka desimal, sedangkan jika komputer anda menggunakan setting bahasa Inggris maka gunakan tanda titik (.)
Kesalahan penulisan tanda baca akan menghasilkan pesan error #VALUE! untuk hasil akhirnya. Begitu juga dengan bentuk penulisan rumus berbeda dengan format angka, akan menghasilkan #VALUE! (lihat data di atas yang berwarna merah)

Untuk penulisan rumus pada format yang puluhan sebagaimana pada jumlah adalah:

=VLOOKUP(LEFT(C18,1)*1,$F$3:$G$12,2)&”  “&VLOOKUP(MID(C18,2,1)*1,$F$3:$G$12,2)&” Koma “&VLOOKUP(MID(C18,4,1)*1,$F$3:$G$12,2)& “  “&VLOOKUP(RIGHT(FIXED(C18),1)*1,$F$3:$G$12,2)

Begitula materi kursus excel online tentang cara membuat raport sederhana dengan memanfaatkan rumus excel dari microsoft office ini.

85