Invent

Membuat Data Pemasukan dan Pengeluaran Harian di Excel




Pertanyaan dari Mas Mulyono

pakar excel yang baik. kalau ingin memasukkan data otomatis terekam pada sheet lain bagaiman bos rumus. Terima kasih

Yap, pertanyaan menarik. Untuk menjawabnya, saya akan memberikan contoh satu aplikasi sederhana untuk menyimpan transaksi masukan dan pengeluaran yang bisa anda kembangkan sendiri. Aplikasi ini sederhana. Saya akan menggunakan Excel untuk memasukkan data Pemasukan dan pengeluaran harian, yang terdiri dari :

  1. Tanggal Transaksi
  2. Jenis Transaksi (Input/Output)
  3. Uraian
  4. Jumlah
  5. Terbilang, hanya sebagai tambahan saja, untuk mengecek apakah nilai yang kita masukkan sudah benar.

Caranya:

1. Buka Excel, Namai sheet1 dengan INPUT dan Sheet2 dengan DATA. Nantinya data akan disimpan dalam Sheet DATA

3. Buat antar muka sederhana yang terdiri dari 5 point diatas. Tambah 1 lagi yaitu ID transaksi. Lihat gambar.

transaksi harian

4. Supaya mudah, namai sel C3, yaitu untuk kotak No Transaksi dengan id dan berikutnya:

  • C3 dinamai id
  • C4 dinamai tgl
  • C5 dinamai jenis
  • C6 dinamai uraian
  • C7 dinamai jumlah

Untuk menamai sel dengan mudah, caranya dibawah ini:

Misalkan anda ingin menamai sel G12 (gambar diatas) dengan nama terbilang, maka klik sel G12, lalu klik kotak yang berisi alamat G12 (disebelah kiri fx – lihat gambar diatas), klik, dan tulis Terbilang, tekan Enter

5. Sekarang klik Sheet Data. Anda perlu membuat tabel data yang berisi ID, Tanggal, Jenis, Uraian dan Jumlah. Saya mulai dari A5 menulis ID, Tanggal, Jenis, Uraian dan Jumlah. A1 sampai dengan A4 nanti akan digunakan, maka kosongi saja

  • Sheet DATA. A4, tulis CountID. A5 tulis rumus =COUNT(A6:A10006)
  • Saya asumsi kita nantinya punya 10.000 data. CountID akan menghitung jumlah data yang sudah ada di tabel DATA.
  • Isi tabel data dengan isi sebagai berikut
    • ID = 1
    • Tgl = 25/4/12
    • Jenis = Output
    • Uraian= Pembelian Bolpoin
    • Jumlah = 24000
  • Format masing-masing sel, sesuai dengan isinya. Misalkan format Tgl dengan tanggal, Jumlah dengan tanda koma, dan seterusnya
  • di A1, tulis rumus : =id
  • B1 tulis rumus INDEX($A$6:$E$10006;MATCH($a$1;$A$6:$A$10006;0);2)
  • copy rumus di B1, tarik sampai E1. Ganti rumus yang bertanda merah, diganti dengan 2, 3, 4, dan 5. Jadi
  • B3 rumusnya =INDEX($A$6:$E$10006;MATCH($A$1;$A$6:$A$10006;0);3) dst

transaksi harian data

5. Nah sekarang balik lagi ke Sheet INPUT. Kita akan buat Tombol Pemasukan Data, Data Baru, dan Tombol Lihat Record Ke kiri dan ke kanan.

Klik Developer, kemudian klik Insert/ActiveX Control, CommandButton

Buat 4 Commandbutton, atur seperti gambar. Namai masing-masing dengan BtnEnter, BtnNew, BtnRight dan BtnLeft, Caption seperti Gambar. Nantinya button ini digunakan untuk navigasi Record Kita.

btnNavigasi

Selanjutnya Isi Sheet INPUT dengan data –data yang telah kita punya dari sheet DATA

  • Id dengan =Data!A1
  • tgl dengan =Data!B1
  • Jenis dengan cara seperti dibawah ini
  • Uraian dengan =Data!D1
  • Jumlah dengan =Data!E1
  • Terbilang dengan =terbilang(jumlah)

Aktifkan fungsi terbilang. Caranya lihat http://harisaryono.com/2011/07/membuat-fungsi-terbilang-di-excel/ download fungsinya dari 4shared .

Untuk Jenis, karena terdiri dari 2 pilihan saja, yaitu Input dan Output, maka kita bisa membuat Drop Down Menu supaya pemasukan datanya selalu tepat. Caranya seperti yang telah dijelaskan pada membuat drop down menu. Atau supaya cepat, caranya begini:

  • Klik C5 atau Jenis
  • Data/Data Validation pilih Data Validation
  • Pada Setting/Allow pilih List.
  • Pada Source tulis Input,Output
  • Tekan OK

6. Saatnya berpikir.

Kita punya 4 tombol yaitu Left, Right, New dan Enter. Fungsinya sebagai berikut:

  • Enter ditekan, maka Kita harus mengkopi data yang ada di sel Input ke dalam sel Data. Lokasi pengkopian tentu saja sesuai dengan nomor ID
  • New ditekan, maka ID akan ditambah dengan 1 dari jumlah record yang ada, sel yang lain dikosongi semua
  • << ditekan, maka ID dikurangi 1. tampilkan record yang sudah ada sesuai dengan ID yang ditunjuk di kolom ID.  Bila ID = 1, jangan kurangi lagi
  • >> ditekan, maka ID ditambah 1. Tampilkan record sesuai dengan ID yang ditunjuk. Bila ID sudah maksimum, jangan ditambah lagi

Ok, kita sekarang akan memberi kode pada tombol-tombol tersebut. Klik Developer/Design Mode lalu klik tombol Enter, klik kanan, pilih View Code. Isi Kode berikut ini

Private Sub BtnEnter_Click()
RecordNow = Sheets("Input").Range("id").Value
    With Sheets("Data")
       .Cells(RecordNow + 5, 1).Value = Sheets("Input").Range("ID").Value
       .Cells(RecordNow + 5, 2).Value = Sheets("Input").Range("tgl").Value
       .Cells(RecordNow + 5, 3).Value = Sheets("Input").Range("jenis").Value
       .Cells(RecordNow + 5, 4).Value = Sheets("Input").Range("uraian").Value
       .Cells(RecordNow + 5, 5).Value = Sheets("Input").Range("jumlah").Value
    End With
    MsgBox ("Data Sudah Dimasukkan")
End Sub

Private Sub BtnLeft_Click()
    With Sheets("Input")
       RecordNow = Sheets("Input").Cells(3, 3).Value
       If RecordNow = 1 Then
          If .Cells(4, 3).Formula = "=Data!B1" Then
          Else
             Call FillFormula
          End If
       Else
          .Cells(3, 3).Formula = RecordNow – 1
          If .Cells(4, 3).Formula = "=Data!B1" Then
          Else
             Call FillFormula
          End If
       End If
    End With
End Sub

Private Sub BtnNew_Click()
   ‘MaxRecord = Sheets("Data").Range("B4").Value
   With Sheets("Input")
      .Cells(3, 3).Formula = "=Data!b4+1"
      .Cells(4, 3).Formula = ""
      .Cells(5, 3).Formula = ""
      .Cells(6, 3).Formula = ""
      .Cells(7, 3).Formula = ""
   End With
End Sub

Sub FillFormula()
      With Sheets("Input")
      .Cells(4, 3).Formula = "=Data!B1"
      .Cells(5, 3).Formula = "=Data!C1"
      .Cells(6, 3).Formula = "=Data!D1"
      .Cells(7, 3).Formula = "=Data!E1"
   End With
End Sub
Private Sub BtnRight_Click()
    With Sheets("Input")
       MaxRecord = Sheets("Data").Cells(4, 2).Value
       RecordNow = Sheets("Input").Cells(3, 3).Value
       If RecordNow = MaxRecord Then
          If .Cells(4, 3).Formula = "=Data!B1" Then
          Else
             Call FillFormula
          End If
       Else
          .Cells(3, 3).Formula = RecordNow + 1
          If .Cells(4, 3).Formula = "=Data!B1" Then
          Else
             Call FillFormula
          End If
       End If
    End With
End Sub

catatan: maaf, gak sempat menjelaskan arti rumus tersebut. Tapi coba diamat-amati sendiri. Fungsi tersebut sangat mudah kok

7. Selesai. Sekarang simpan file dalam bentuk XLSM, supaya makronya juga tersimpan.

Percobaan

Mari Kita coba hasil kerja kita.

  1. Klik Sheet Input. Klik New
  2. Isi Dengan Data Sembarang
  3. Klik Enter
  4. Klik tombol <<
  5. Klik tombol >>

enter data

Download

Download file contoh disini

Komentar anda sangat kami tunggu…

Istilah Pencarian:

Klik untuk melihat isi list

Istilah Pencarian:

Klik untuk melihat isi list

28 Responses to Membuat Data Pemasukan dan Pengeluaran Harian di Excel

  1. By Randy, 6 April 2012 at 8:59 pm

    mas.,ada nomer yang bisa dihubungi gak??mau minta ajarin .. :DTutorial Excelnya menarik sekali..

    • By Admin, 7 April 2012 at 6:31 am

      @randy: pake form comment ini saja, insy selalu saya jawab kok. Biar yang lain juga pada ngerti. Ini sesuatu yang gampang, dan didistribusikan lewat gnu, copyleft. heheehee….

  2. By Randy, 13 April 2012 at 8:48 am

    mas coba tolong ke Link Ini ya.,Aq bertanya ke Forum Kaskus.

    Soalnya harus aq kash screenshot.,

    nanti balasnya disini aja.,terima kash sebelumnya mas 😀

    • By Admin, 14 April 2012 at 12:00 am

      @randy: gak bisa lihat, soalnya livebeta.kaskus.us hanya yang sudah diundang (invited) yang bisa lihat threadnya.
      coba gambarnya diupload ke free image hosting khan banyak

  3. By Randy, 14 April 2012 at 10:54 pm

    *

    Coba kesini mas

    http://img405.imageshack.us/img405/856/newpicturew.png

    yang aq tanyain.,Jika saya ketik “1” Di C4..,nanti yang saya inginkan di “C10″ Muncul Tulisan Yang Dari “B4″  .Bagaimana ya mas..?trims

  4. By Admin, 15 April 2012 at 8:28 am

    masih bingung: jika diketik “1” di “C4″, di “C10″ muncul tulisan dari “B4″? maksudnya muncul tulisan dari “C4″?. Kalau itu sih gampang. tulis rumus di “C10″ yaitu “=C4″. 

    Kalau dari gambar, saya mengira adik menulis “Hallo Kaskus” di “B4″, dan di “C4″, tulisan tersebut ingin adik copy. Begitu?

    Kalau ya, berarti tulis rumus di “C4″ —> =B4, lalu copy rumusnya sampai C9

  5. By Randy, 17 April 2012 at 6:42 pm

    *Sudah bisa mas.,Trims

    Terus Bisa Dijelaskan Tidak Formula Index Itu Yang Ada Sheet “Data” ?
    Apa bisa Menyimpan Data yang ada di Sheet “input” Tanpa Macro?Misal Menggunakan Formula.?Dengan Menggunakan “Jika Meng input Angka “1”.,Data Akan Tersimpan.

    Trims

  6. By Admin, 18 April 2012 at 4:29 am

    @randy: menyimpan data di sheet lain tanpa macro, TIDAK BISA. (Bisa sih dengan suatu cara, tapi tidak efektif). Kenapa? Karena sheet DATA tersebut, rumusnya =INPUT!A5 misalnya, karenanya apa yang ada di sheet INPUT sel A5 akan dicopy ke sheet DATA tsb. Jadi kalau di sheet INPUT sel A5 nilainya diganti, sheet DATA nilainya akan berganti juga

    untuk formula INDEX-MATCH , nanti akan saya buat postingan tersendiri. Intinya, sama dengan VLOOKUP atau HLOOKUP, tetapi INDEX-MATCH lebih fleksibel. 

  7. By hermawan daliz, 29 April 2012 at 6:21 am

    *mas,, saya baru download dan lg mencoba buat memahami.. pertama saya download tombol nya gak aktif..developernya dimana ya?masuk macro gitu? dijelasin dong caara memasukan kodenya,trims :-)

  8. By hermawan daliz, 29 April 2012 at 9:13 am

    kalau mau tutor bisa gak ya belajar logika ini aja dulu,,#minat serius pak

  9. By hermawan daliz, 4 May 2012 at 8:07 am

    pagi pak,, terima kasih atas respon yg cepat dan tepat dari bapak..sy ingin mengajukan pertanyaan lagi krn sya masih newbie soal VB.. kalau seandainya saya ingin menambah kategori pada sheet inputan menjadi lebih banyak lg sehingga isi pada sheet data lebih banyak lagi..bagaimana caranya pak?

    contoh saya ingin menambahkan kategori NAMA,Tgl Lahir dan Jenis kelamin

    terima kasih sebelumnya..

    • By Admin, 4 May 2012 at 8:43 am

      @hermawan: untuk memasukkan data baru, silahkan tambah kotak masukan, kemudian edit BtnEnter_Click(). sebenarnya cara memasukkan data ke dalam baris dan kolom menggunakan fungsi sheets(“Data”).cells(baris,kolom).

      contoh: .Cells(RecordNow + 5, 5).Value = Sheets(“Input”).Range(“jumlah”).Value

      RecordNow adalah jumlah record terakhir. Plus 5, karena record dimulai dari baris ke-6. 5 adalah kolom ke-5.
      Nilainya diambil dari Sheet(“Input”).Range(“jumlah”).value

      Jadi kalau mau tambah masukan Nama, buat kotak input, namai dengan “Nama” (tanpa tanda petik), lalu rumus diatas, yang berubah hanya kolomnya. Mungkin anda buat di kolom ke 7 dst…

  10. By Ali Mustofa, 4 July 2012 at 10:30 am

    Mas..
    aku lagi penasaran nich…
    pengalaman tahun 1995 aku penasaran sama aplikasi macros di lotus. sampai 3 kali install.

    melihat penjelasan diatas saya lebih interest. sayangnya mau download ga bisa.
    bisa kasih tau atau send e-mail mas.

    Matur nuwun….

  11. By Lian, 23 November 2012 at 11:17 am

    pak saya baru coba dowload file contoh, saya coba oprasiin belum bisa ya pak (tobol new,enter dll)?? mohon dibantu penjelasannya bagaimana cara pengoprasiannya

    thx

    • By Admin, 23 November 2012 at 12:51 pm

      Kalau tombol tidak aktif, mungkin karena makronya tidak diaktifkan. Coba cek di tab Developer/Macro Security. Pada Macro Setting, set Enable all Macro….Tekan OK, lalu tutup excel dan buka lagi

  12. By aldrin, 29 January 2013 at 10:44 am

    pak, saya sudah mencoba membuat dan memang agak rumit, dan jujur saja saya pusing hehehe, boleh ga apa bila file yang saya buat saya kirim ke email bapak, minta tolong di cek, klo memang bapak berkenan, saya akan kirim via email

    terima kasih banyak

    jawab: kirim ke hari.saryono@gmail.com

  13. By Ariance Fallo, 5 February 2013 at 3:12 pm

    Ada tempat untuk kursus kah?? kalau ada bisa di infokan ya…Thanks

  14. By desman fiktor, 22 February 2013 at 4:58 pm

    pak,,,,saya pemula di excel ne dan saya di suruh membuat general report perusahaan saya. saya kurang mengerti cara-caranya. kalo bisa di beritahukan pak.

  15. By willy, 6 March 2013 at 4:59 pm

    mas numpang nanya..yang point NO 5 itu nyari Developer klik di bagian mana yah mas ? mohon petunjuknya trimakasih banyak mas ilmu ke pakai di tempat saya

  16. By Killua Zoldyk, 8 March 2013 at 7:46 pm

    Thank’s yaa gan info.a,,,
    klo ada yg bru n lbih mudah kontak ane yaa gan diaz_gokil005@rocketmail.com
    skali lgi than’s gan,,,

  17. By patricia, 12 March 2013 at 9:51 pm

    Says ingin membuat laporan penjualan… saya Bergerak dibidang distributor obat2n… tolong bantuanya don.k..

  18. By faris, 15 March 2013 at 11:33 am

    Maaf Pak Admin rumus untuk menampilkan data dengan kriteria yang sama apa ya rumus excelnya? contoh kita punya data disheet 1 nama orang dan jenis kelamin terus menampilkan di sheet 2 dengan kriteria nama2 yang berjenis kelamin laki/perempuan saja.
    File nya ada di http://www.4shared.com/file/O3qYuPXH/SORT_OTOMATIS.html atau di http://www.mediafire.com/view/?czalaxgcc4gc4k1.
    Terima Kasih Banyak

  19. By igun, 2 July 2013 at 5:10 pm

    Mas kenapaa ya saya masukkan rumus yg mas kasi di sheet DATA kolom B1 (=INDEX)malah error.

  20. By zak, 26 April 2014 at 3:01 pm

    Kok gak jalan di ms office 2013 rumusnya mas

  21. By Fathur Rohman, 1 July 2014 at 3:58 pm

    Pa klo si input nya menyamping apa kode ny berbeda? itu kan kebawah trims

    • By Admin, 5 July 2014 at 7:19 am

      sebenarnya sama saja, cuman yang diganti adalah alamat sel =cells(x,y).value nya aja

Leave a Reply

Archives

Kategori

Who's Online

8 visitors online now
2 guests, 6 bots, 0 members
Map of Visitors

Langganan Blog Lewat Email

Masukkan email anda untuk mendapatkan update website ini secara otomatis di email anda.
Bergabunglah bersama 1862 Subscriber yang lain

Visit also our social profiles:

Scroll to top