TL;DR
Stok barang gudang butuh lima tabel terpisah: Master Barang, Master Lokasi, Barang Masuk, Barang Keluar, dan Mutasi antar gudang. Saldo per gudang dihitung dengan SUMIFS yang nyaring berdasarkan kode barang sekaligus kode gudang, lalu ditambah mutasi masuk dan dikurangi mutasi keluar. Struktur ini bikin selisih bisa ditelusuri sampai ke lokasi rak, bukan cuma sampai ke jenis barang.
Stok barang gudang butuh lima tabel terpisah, bukan satu tabel besar: Master Barang, Master Lokasi, Barang Masuk, Barang Keluar, dan Mutasi antar gudang.
Bedanya sama stok toko ada di tiga hal: banyak lokasi penyimpanan, perpindahan barang antar gudang, dan barang yang punya masa berlaku.
Tiga hal itu yang bikin rancangan satu tabel langsung ambruk. Di bawah ini struktur yang tahan, rumus saldonya, dan cara nyusun sheet stock opname.
Karena satu kode barang bisa ada di beberapa tempat sekaligus. Barang yang sama bisa nyimpen 40 unit di gudang utama dan 15 unit di gudang cabang, dengan riwayat masuk yang berbeda. Satu tabel dengan satu kolom saldo nggak bisa nyimpen dua kenyataan itu tanpa kehilangan jejak.
Masalah kedua ada di perpindahan. Barang yang pindah dari gudang utama ke cabang itu bukan pembelian dan bukan penjualan.
Kalau dicatat sebagai masuk dan keluar biasa, angka total pembelian bulan itu langsung menggelembung. Laporan pembelian jadi ngasih angka yang lebih besar dari nota supplier.
Buat toko kecil dengan satu tempat penyimpanan, struktur tiga sheet di aplikasi stok barang di Excel udah cukup. Artikel ini buat yang udah punya lebih dari satu lokasi.
Lima tabel, masing-masing satu peran. Master Barang nyimpen identitas SKU, Master Lokasi nyimpen daftar gudang dan rak, sementara tiga sisanya nyimpen kejadian: penerimaan dari luar, pengeluaran ke luar, dan perpindahan internal. Saldo nggak disimpan di mana pun, dia selalu hasil hitungan.
| Sheet | Isi | Frekuensi berubah |
|---|---|---|
| Master | kode, nama, satuan, harga_beli, stok_minimum, punya_kedaluwarsa | Jarang |
| Lokasi | kode_gudang, nama_gudang, kode_rak, kapasitas | Jarang |
| Masuk | tanggal, kode, gudang, rak, qty, batch, kedaluwarsa, supplier, no_nota | Harian |
| Keluar | tanggal, kode, gudang, rak, qty, batch, tujuan, no_bukti | Harian |
| Mutasi | tanggal, kode, gudang_asal, gudang_tujuan, qty, batch, no_surat_jalan | Mingguan |
Ubah kelimanya jadi Excel Table lewat Ctrl+T, lalu kasih nama tblMaster, tblLokasi, tblMasuk, tblKeluar, dan tblMutasi.
Satu aturan yang bikin seluruh rancangan ini jalan: satu baris mutasi cuma boleh punya satu gudang asal dan satu gudang tujuan. Kalau barang dipecah ke dua gudang, tulis dua baris.
Enam kolom wajib di dua-duanya: tanggal, kode barang, kode gudang, jumlah, nomor bukti, dan pelaku transaksi. Kolom batch dan kedaluwarsa jadi wajib kalau barangnya punya masa berlaku. Kolom rak sifatnya operasional, dipakai buat nyari barang, bukan buat hitungan saldo.
Dua kolom yang paling sering dilupain dan paling mahal akibatnya:
Kunci kolom kode barang dan kode gudang pakai Data Validation supaya nggak ada yang ngetik bebas. Sumber daftarnya ambil dari dua sheet master.
=INDIRECT("tblMaster[kode]")
=INDIRECT("tblLokasi[kode_gudang]")Saldo per gudang dihitung dengan SUMIFS yang nyaring dua syarat sekaligus: kode barang dan kode gudang. Hasilnya berupa penerimaan dikurangi pengeluaran, lalu ditambah mutasi yang masuk ke gudang itu dan dikurangi mutasi yang keluar darinya. Empat bagian itu dijumlahin dalam satu rumus.
Bikin sheet baru bernama Saldo. Taruh kode barang di kolom A mulai baris 2, dan kode gudang di baris 1 mulai kolom B.
Di sel B2, tulis:
=SUMIFS(tblMasuk[qty],tblMasuk[kode],$A2,tblMasuk[gudang],B$1)
-SUMIFS(tblKeluar[qty],tblKeluar[kode],$A2,tblKeluar[gudang],B$1)
+SUMIFS(tblMutasi[qty],tblMutasi[kode],$A2,tblMutasi[gudang_tujuan],B$1)
-SUMIFS(tblMutasi[qty],tblMutasi[kode],$A2,tblMutasi[gudang_asal],B$1)Perhatikan posisi tanda dolar. $A2 ngunci kolomnya, B$1 ngunci barisnya.
Berkat dua kunci itu, satu rumus bisa ditarik ke kanan dan ke bawah buat ngisi seluruh tabel silang. Cara kerja penguncian ini dibahas di absolute reference Excel, dan argumen fungsinya di fungsi SUMIFS.
Buat pengguna Excel dengan pengaturan regional Indonesia, koma pemisah argumen ganti jadi titik koma.
Kolom total di paling kanan cukup pakai penjumlahan biasa. Angkanya harus sama persis dengan hitungan tanpa penyaringan gudang, dan itu cara cepat ngecek rumusmu bener.
=SUM(B2:D2)Saldo dihitung per pasangan kode barang dan nomor batch, bukan per kode barang saja. Sheet Masuk nyimpen tanggal kedaluwarsa tiap batch, dan sheet Keluar wajib nyebut batch mana yang diambil. Dengan begitu kamu bisa tau umur tiap tumpukan, bukan cuma jumlah totalnya.
Tambah satu syarat batch ke rumus saldo:
=SUMIFS(tblMasuk[qty],tblMasuk[kode],$A2,tblMasuk[batch],$B2)
-SUMIFS(tblKeluar[qty],tblKeluar[kode],$A2,tblKeluar[batch],$B2)Buat peringatan masa berlaku, hitung selisih hari dari tanggal hari ini:
=IF([@kedaluwarsa]-TODAY()<=0,"KEDALUWARSA",IF([@kedaluwarsa]-TODAY()<=30,"SEGERA KELUARKAN","AMAN"))Warnai kolom ini pakai Conditional Formatting. Merah buat yang lewat, kuning buat yang tinggal sebulan.
Aturan pengeluarannya ikut masa berlaku, bukan urutan datang. Batch dengan tanggal kedaluwarsa paling dekat keluar duluan, walaupun dia datang belakangan.
Fungsi tanggal yang dipakai di atas dijelasin di fungsi TODAY.
Sheet Opname isinya empat kolom: kode barang, gudang, saldo sistem, dan hasil hitung fisik. Kolom saldo sistem ditarik dari sheet Saldo pakai rumus, sementara kolom fisik diisi manual waktu penghitungan. Kolom kelima berisi selisih, yaitu fisik dikurangi sistem.
Urutkan barisnya berdasarkan kode rak, bukan berdasarkan kode barang. Petugas gudang jalan mengikuti rak, jadi daftar yang urut rak bikin mereka nggak bolak-balik.
=[@fisik]-[@sistem]Tambah kolom nilai selisih dalam rupiah biar prioritas penelusuran jelas:
=[@selisih]*VLOOKUP([@kode],tblMaster[[kode]:[harga_beli]],4,FALSE)Selisih 40 unit paku senilai Rp 60.000 nggak sepenting selisih 2 unit barang senilai Rp 1,4 juta. Kolom rupiah yang bikin urutan itu kelihatan.
Setelah selesai, jangan langsung ubah angka sistem. Catat penyesuaian sebagai baris baru di sheet Masuk atau Keluar dengan keterangan "penyesuaian opname" dan nomor bukti berita acara.
Dataset latihan toko_berkah di ngulikdata punya 42 SKU yang tersebar di dua lokasi: Gudang Utama di belakang toko, dan Gudang Cabang di ruko sebelah.
Selama Juli 2025 ada 6 baris mutasi antar gudang. Total nilai persediaan akhir bulan Rp 37,2 juta.
| Kode | Nama | G. Utama | G. Cabang | Total | Umur stok cabang |
|---|---|---|---|---|---|
| BRG-014 | Minyak Goreng 2L | 88 | 27 | 115 | 12 hari |
| BRG-007 | Beras Premium 5kg | 45 | 18 | 63 | 9 hari |
| BRG-019 | Sirup Botol Besar | 6 | 64 | 70 | 117 hari |
| BRG-026 | Kecap Manis 600ml | 11 | 48 | 59 | 102 hari |
Dua baris terakhir yang bikin kolom umur stok kepakai. Sirup dan kecap numpuk di cabang lebih dari 100 hari.
Total ada 5 SKU dengan umur stok di atas 90 hari di Gudang Cabang. Nilainya Rp 4,1 juta, atau 11 persen dari seluruh nilai persediaan.
Angka 11 persen itu uang yang duduk diam di rak sebelah, sementara gudang utama sempat kehabisan gula pasir di minggu yang sama.
Akar masalahnya kelihatan waktu sheet Mutasi ditelusuri. Lima dari enam mutasi arahnya sama, dari utama ke cabang, dan nggak ada satu pun yang balik.
Cabang dipakai sebagai tempat naruh barang yang nggak muat, bukan sebagai titik jual. Sekali barang pindah ke sana, dia berhenti bergerak.
Perbaikannya nggak butuh sistem baru. Cukup satu aturan: barang boleh pindah ke cabang cuma kalau penjualan di cabang bulan lalu lebih besar dari nol.
Tanpa kolom gudang di tiap transaksi, pola ini nggak akan pernah kelihatan. Angka total 70 botol sirup kedengeran normal sampai kamu tau 64 di antaranya nyangkut di ruko sebelah.
1. Nyatet mutasi sebagai masuk dan keluar biasa. Total pembelian jadi menggelembung dan laporan ke supplier nggak cocok. Mutasi wajib punya sheet sendiri.
2. Nyimpen kolom saldo sebagai angka yang diketik. Saldo harus selalu hasil rumus. Angka yang diketik nggak bisa dicocokin balik ke transaksinya.
3. Satu kolom gudang buat semua sheet, tapi kodenya beda-beda. Gudang Utama, GU, dan G-Utama bakal dihitung sebagai tiga gudang oleh SUMIFS. Pakai satu sumber kode dari Master Lokasi.
4. Ngabaikan batch buat barang yang punya masa berlaku. Tanpa batch, kamu tau ada 70 botol, tapi nggak tau 20 di antaranya lewat tanggal bulan depan.
5. Nyusun daftar opname berdasarkan kode barang. Petugas jadi bolak-balik antar rak dan waktu hitungnya bisa dua kali lipat. Urutkan berdasarkan lokasi.
6. Ngubah angka sistem langsung setelah opname. Penyesuaian wajib jadi baris transaksi baru dengan nomor berita acara. Kalau ditimpa, jejak selisihnya hilang.
7. Nggak pernah ngecek umur stok. Barang yang nggak bergerak nggak bakal ngasih tanda sendiri. Tambah kolom selisih hari dari penerimaan terakhir dan cek tiap bulan.
Karena satu perpindahan itu dua kejadian sekaligus: keluar dari gudang asal dan masuk ke gudang tujuan. Kalau dicatat di sheet Masuk dan sheet Keluar, total masuk dan total keluar jadi menggelembung dan angka pembelian nggak bisa dibaca lagi. Sheet Mutasi misahin perpindahan internal dari transaksi sama pihak luar.
Tambah kolom nomor batch dan tanggal kedaluwarsa di sheet Masuk, lalu wajibkan sheet Keluar nyebut batch mana yang diambil. Saldo dihitung per pasangan kode barang dan batch, bukan per kode barang doang. Buat peringatan, pakai rumus selisih antara tanggal kedaluwarsa dan tanggal hari ini, lalu warnai yang tersisa di bawah 30 hari.
Batas baris Excel jauh di atas sejuta, tapi batas nyamannya lebih rendah. Dengan rumus SUMIFS di ribuan baris, file mulai terasa berat di sekitar 50 ribu baris transaksi. Kalau udah nyampe situ, pindahin rumus ke Power Query atau Power Pivot, atau arsipkan transaksi tahun lama ke file terpisah.
Perlu kalau nyari barang di gudang itu makan waktu lebih dari dua menit. Kolom lokasi bukan buat hitungan saldo, tapi buat operasional. Waktu stock opname, petugas bisa jalan urut per rak dan nggak bolak-balik. Kalau gudangmu cuma satu ruangan kecil dengan 20 jenis barang, kolom ini boleh dilewat.
Jangan hapus transaksi lama. Ganti rumus SUMIFS jadi versi yang punya syarat rentang tanggal, lalu simpan hasil hitung fisik akhir bulan sebagai stok awal periode berikutnya di sheet Opname. Saldo sistem bulan baru dihitung dari stok awal itu ditambah transaksi bulan berjalan. Riwayat lama tetap ada buat penelusuran.
Lima tabel, satu rumus SUMIFS bertingkat, dan aturan bahwa saldo nggak pernah diketik. Itu kerangka yang bikin stok gudang bisa ditelusuri sampai ke rak.
Kolom yang paling sering dilewat tapi paling banyak ngasih jawaban: kode gudang dan umur stok. Dua kolom itu yang nunjukin barang mana yang cuma numpang duduk.
Referensi resmi fungsi yang dipakai di atas ada di dokumentasi Microsoft Excel.
Buka file stokmu sekarang dan cek apakah tiap baris transaksi punya kolom gudang. Kalau belum ada, itu satu kolom yang paling cepat balik modal. Buat pencatatan per barang yang lebih rinci, lanjut ke kartu stok barang.
Mau praktek langsung? Mulai latihan SQL gratis
Latihan interaktif, langsung di browser.
Punya ratusan angka dan mau tau sebarannya masuk ke rentang mana aja? FREQUENCY ngitung distribusi frekuensi sekali jalan, tanpa nulis COUNTIFS berkali-kali.
SUMIFS ribet kalau kriterianya banyak dan sering ganti. Rumus database Excel kayak DSUM baca kriteria dari sel terpisah, jadi kamu tinggal ubah tabel kecil tanpa nyentuh rumus.
DATEDIF ngasih umur bulat, tapi kadang kamu butuh angka desimal kayak 27,6 tahun buat hitung masa kerja atau bunga. YEARFRAC ngasih pecahan tahun dengan presisi.