MENGHITUNG STOK BARANG Versi 2

BismillahiRahmaniRahim

Assalamualaikum Wr. Wb.

Artikel ini adalah lanjutan dari artikel KJ sebelumnya yang banyak dikunjungi di blog KJ excekj.blogspot.co.id, yaitu MENGHITUNG STOK BARANG.

Tetapi kali ini akan dijelaskan secara detail cara pembuatannya mulai dari awal hingga penggunaannya. dari sekian banyak yang mengunjungi artikel sebelumnya, ada beberapa masalah yang muncul, diantaranya Calendar Add-Ins Tidak muncul, itu karena di Excel sobat tidak melakukan setting di VBA pada Tools tambahan dan caranya banyak di share di google. Masalah terakhir yang muncul adalah blokir download file di dropbox error 403 yang disebabkan trafik kunjungan ke file tersebut overload karena akun dropbox KJ belum di upgrade ke berbayar. kedepannya untuk mengatasi file tidak bisa di download anda cukup email ke kami atau inbox di facebook KJ di menu kanan atas blog ini.

Baik kita langsung saja ke pembahasan kita, yaitu (Versi 2) MENGHITUNG STOK BARANG beserta tutorial pembuatannya.

Alat dan Bahan:

• Siapkan File Excel yang support macro, biasanya di save ke ekstensi .xlsm atau .xlsb kemudian isi dengan 3 sheet, yaitu Data_Barang, Data_Transaksi dan Laporan. model ini adalah model sebuah file perhitungan sederhana.
• Pada Sheet Data_Barang kita Siapkan Tabel Data barang sederhana saja yang memiliki informasi detail barang yang kita miliki seperti gambar dibawah ini, dan insert sebuah TextBox di kanan atas tabel untuk cari barang sesuai kriteria OptionButton. (ini bonus saja hehe)

• Untuk Macro Pencarian kita dapat memasukkan kode dibawah ini, tetapi jangan lupa untuk insert komponen ActiveX Control (1 TextBox dan 3 OptionButton dan Label) dan ganti Caption masing-masing seperti gambar diatas. 
  
  Catatan untuk Macro buka dengan Alt F11 kemudian Double klik pada Sheet Data_Barang dan paste kan kode dibawah ini.
  
  
  Private Sub OptionButton3_Click()

  Application.ScreenUpdating = False

      Dim rngBarang   As Range

      Dim jBrs        As Long

      Dim Kriteria    As String

  
  

      With Sheets("Data_Barang")

          Set rngBarang = .Range("A:A")

          jBrs = Application.WorksheetFunction.CountA(rngBarang)

          .Range("$A$2:$c$" & jBrs).AutoFilter Field:=3

          .Range("$A$2:$c$" & jBrs).AutoFilter Field:=2

      End With

  End Sub

  
  

  Private Sub TextBox1_Change()

  Application.ScreenUpdating = False

      Dim rngBarang   As Range

      Dim jBrs        As Long

      Dim Kriteria    As String

  
  

      With Sheets("Data_Barang")

          Set rngBarang = .Range("A:A")

          jBrs = Application.WorksheetFunction.CountA(rngBarang)

          Kriteria = .TextBox1.Value

          On Error Resume Next

          If .OptionButton1.Value = True Then

              .Range("$A$2:$c$" & jBrs).AutoFilter Field:=2

              .Range("$A$2:$c$" & jBrs).AutoFilter Field:=2, Criteria1:="=*" & Kriteria & "*", Operator:=xlAnd

          End If

          If .OptionButton2.Value = True Then

              .Range("$A$2:$c$" & jBrs).AutoFilter Field:=3

              .Range("$A$2:$c$" & jBrs).AutoFilter Field:=3, Criteria1:="=*" & Kriteria & "*", Operator:=xlAnd

          End If

      End With

  End Sub

• Untuk Sheet Data_Transaksi tampilan dapat kita lihat seperti gambar dibawah ini
  
  
  
  Hampir sama dengan Sheet Data_Barang perbedaannya adalah pada kolom Tanggal, keluar/masuk barang dan jumlah transaksi. dari gambar kita melihat ada kolom stok pada kolom paling kanan. sedikit penjelasan dan penggunaan rumus untuk Sheet ini adalah rumus yang kita gunakan di kolom Nama_Barang dan Kolom Stok. 
  Kolom Nama_barang pada Cell F4 bisa di Copy kebawah, kita memakai rumus berikut :=IFERROR(OFFSET(Data_Barang!$C$2,MATCH(D4,Data_Barang!$B$3:$B$999999,0),0),"")untuk penjelasan rumus Offset dan Match dapat kita lihat di artikel KJ lainnya, inti dari rumus diatas adalah kita menetapkan posisi Start kita di cell pada C2 sheet Data_Barang kemudian pada rumus Match kita menghitung dibaris berapa Kode Barang (D4) berada pada Range B3:B9999999 Sheet Data_barang dan 0 paling ujung menetapkan kolom Offset tidak bergerak tetap di kolom C. D4 dan seterusnya kebawah pada Sheet Data_Transaksi merupakan kunci pemanggilan untuk Nama Barang.
  
  Kolom Stok pada Cell H4 bisa di Copy kebawah, kita menggunakan rumus:=SUMIFS(E$4:E4,D$4:D4,D4,C$4:C4,"Masuk")-SUMIFS(E$4:E4,D$4:D4,D4,C$4:C4,"Keluar")Penjelasan dari rumus ini secara umum adalah menjumlahkan semua barang masuk dikurangi dengan semua barang keluar. secara detail adalah =Jumlahkan jika (Jumlahkan kolom ini, jika kolom D4 s.d D Baris rumus ini = kode_barang baris ini, jika Kolom C4 s.d C baris rumus ini berkriteria "Masuk") - SUMIFS(E$4:E4,D$4:D4,D4,C$4:C4,"Keluar") kira-kira seperti itu bunyi rumus tersebut jadi titik awal dihitung di baris 4 (kita kunci dengan tanda $) dan titik akhir berdasarkan posisi baris rumus ini (tidak dikunci/ tidak ada tanda $)
  
  Catatan : Rumus Stok itu kita bisa Cut (Ctrl X) ke Sheet data Barang tetapi dengan sedikit modifikasi pada range mengacu ke baris paling bawah Table pada Sheet Data Transaksi. dan mengganti D4 (referensi kriteria ke Kode barang Tabel Sheet Data_Barang)
  
  kode Macro untuk Sheet Data_Transaksi (ingat untuk mengganti nama masing-masing ActiveX Control sesuai nama pada macro dibawah ini) :
  

  
  Catatan untuk Macro buka dengan Alt F11 kemudian Double klik pada Sheet Data_Transaksi dan paste kan kode dibawah ini.
  
  Private Sub OptionButtonAll_Click()

  Application.ScreenUpdating = False

      Dim rngBarang  As Range

      Dim jBrs              As Long

      Dim Kriteria       As String

  
  

      With Sheets("Data_Transaksi")

          Set rngBarang = .Range("A:A")

          jBrs = Application.WorksheetFunction.CountA(rngBarang) + 1

          .Range("$A$3:$f$" & jBrs).AutoFilter Field:=4

          .Range("$A$3:$f$" & jBrs).AutoFilter Field:=6

      End With

  End Sub

  
  

  Private Sub TextBox1_Change()

  Application.ScreenUpdating = False

      Dim rngBarang   As Range

      Dim jBrs           As Long

      Dim Kriteria    As String

  
  

      With Sheets("Data_Transaksi")

          Set rngBarang = .Range("A:A")

          jBrs = Application.WorksheetFunction.CountA(rngBarang) + 1

          Kriteria = .TextBox1.Value

          On Error Resume Next

          If .OptionButtonNama.Value = True Then

              .Range("$A$3:$f$" & jBrs).AutoFilter Field:=6

              .Range("$A$3:$f$" & jBrs).AutoFilter Field:=6, Criteria1:="=*" & Kriteria & "*", Operator:=xlAnd

          End If

          If .OptionButtonKode.Value = True Then

              .Range("$A$3:$f$" & jBrs).AutoFilter Field:=4

              .Range("$A$3:$f$" & jBrs).AutoFilter Field:=4, Criteria1:="=*" & Kriteria & "*", Operator:=xlAnd

          End If

      End With

  End Sub

  
  
• Kita beralih ke Sheet Laporan,
  Tabel dapat kita lihat seperti gambar dibawah ini
  

  

  
  

  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  Pada Sheet ini kita merekapitulasi data berdasar Tanggal yang kita inginkan jadi pada kolom Tanggal kita bebas menggantinya sesuka hati dengan catatan Data akan muncul jika Tanggal tersebut ada di Data Transaksi.
  Kunci pencarian adalah tanggal dan kita memanggil No_Transaksinya agar lebih mudah memanggil data yang lain. pada Cell B5 Kolom No_Transaksi rumus yang kita gunakan adalah rumus CSE (eksekusi Ctrl Shift Enter) atau biasa disebut Array Formula.
  {=IFERROR(SMALL(IF(OFFSET(Data_Transaksi!$B$3,1,0,COUNTA(Data_Transaksi!A1:A1000007)+3,1)=$B$3,OFFSET(Data_Transaksi!$A$3,1,0,COUNTA(Data_Transaksi!A1:A1000007)+3,1)),ROW(B5)-ROW($B$4)),"")}Copy rumus ini kebawah pada kolom yang sama.
  
  Pada kolom Kode_Barang Cell C5 rumus yang digunakan adalah;
  =IFERROR(OFFSET(Data_Transaksi!$D$3,MATCH($B5,OFFSET(Data_Transaksi!$A$3,1,0,COUNTA(Data_Transaksi!$A1:$A1000007)+3,1),0),0),"")Fungsi COUNTA adalah untuk memblok titik terjauh range sesuai Jumlah baris pada Tabel Data_Transaksi.
  
  Saya sengaja tidak memasukkan Form Date Control  untuk menghindari error missing references pada Office user. tetapi jika anda memiliki silahkan gunakan dan output mengacu pada Cell B3 (Cell Tanggal).

Demikian Tutorial MENGHITUNG STOK BARANG Versi 2 dari saya, semoga bermanfaat untuk kita semua.

Catatan: untuk download contoh filenya, anda dapat menuju Halaman Download di Menu diatas dengan Kata Kunci Menghitung Stok Barang Versi 2, atau klik link tulisan yang berwarna biru.

Salam Excel

KJ

Read More