TL;DR
Tabel stok barang sebaiknya dipecah jadi dua: master barang yang nyimpan identitas dan harga tiap barang satu baris sekali, serta tabel transaksi yang nyatet tiap pergerakan masuk dan keluar. Keduanya disambung lewat kode barang pakai VLOOKUP atau XLOOKUP, dan input di tabel transaksi dikunci pakai data validation. Struktur ini bikin perubahan harga cukup diedit sekali dan nama barang nggak pernah punya banyak versi ejaan.
Tabel stok barang sebaiknya dipecah jadi dua: master barang buat identitas tiap barang, dan tabel transaksi buat tiap pergerakan masuk dan keluar.
Satu sheet gemuk berisi semua kolom kelihatan praktis di bulan pertama. Bulan keenam, kamu punya empat versi ejaan buat barang yang sama dan rekap yang mecah ke mana-mana.
Di bawah ini isi tiap tabel, cara nyambunginnya pakai kode barang, dan contoh file toko yang punya 27 versi ejaan cuma buat 9 barang.
Master barang adalah daftar semua barang yang kamu jual, satu barang satu baris, berisi hal yang jarang berubah. Tabel transaksi adalah catatan tiap pergerakan barang, satu kejadian satu baris, dan isinya terus bertambah tiap hari.
Bedanya gampang diingat lewat pertanyaan yang dijawab masing-masing. Master jawab pertanyaan "barang apa". Transaksi jawab pertanyaan "apa yang terjadi dan kapan".
| Aspek | Master barang | Tabel transaksi |
|---|---|---|
| Jumlah baris | Sebanyak jenis barang | Terus bertambah tiap hari |
| Frekuensi berubah | Jarang | Setiap hari |
| Contoh isi | Nama, satuan, harga beli | Tanggal, jumlah, jenis |
| Kunci utama | Kode barang, unik | Kode barang, boleh berulang |
Tiga alasan praktis. Perubahan harga cukup diedit di satu baris, nama barang nggak punya banyak versi ejaan, dan file kamu tetap ringan walau transaksinya udah ribuan baris.
Perubahan harga jadi satu tempat. Di sheet gemuk, harga beli ditulis ulang di tiap baris transaksi. Waktu supplier naikin harga, kamu perlu mikir baris mana yang harus diubah dan mana yang tetap.
Nama barang jadi konsisten. Nama yang diketik berulang selalu berakhir punya varian. Beras 5 kg, Beras 5kg, dan beras 5 Kg dianggap tiga barang berbeda sama rumus rekap.
File tetap ringan. Tabel transaksi cuma nyimpan angka dan kode pendek, bukan teks panjang yang diulang ribuan kali.
Ada satu keuntungan yang baru kerasa nanti. Waktu kamu pindah dari spreadsheet ke aplikasi atau database, struktur dua tabel ini udah siap dipindahkan hampir tanpa ubahan.
Enam sampai delapan kolom udah cukup buat UMKM. Semuanya hal yang jarang berubah, dan satu barang cuma boleh punya satu baris.
| Kolom | Isi | Contoh |
|---|---|---|
| Kode barang | Identitas unik, jangan pernah dipakai ulang | BRG-014 |
| Nama barang | Nama lengkap yang gampang dikenali | Beras Pandan Wangi 5 kg |
| Satuan | Satuan tetap buat semua transaksi | Sak |
| Kategori | Buat rekap dan analisis | Sembako |
| Harga beli | Harga terakhir dari pemasok | 62.000 |
| Batas minimum | Ambang buat alert restock | 20 |
| Pemasok | Siapa yang biasa nyuplai | UD Sumber Tani |
Satu kolom yang sering keliru ditaruh di sini: saldo stok. Saldo itu hasil hitungan dari tabel transaksi, bukan data yang diketik. Kalau ditaruh di master dan diisi manual, kamu balik lagi ke masalah lama.
Satuan juga perlu disiplin. Kalau satuannya sak, semua transaksi barang itu harus dalam sak. Nyampur sak dan kilogram di barang yang sama bikin saldonya nggak berarti.
Enam kolom inti. Semuanya soal kejadian, bukan soal identitas barang.
| Kolom | Isi | Contoh |
|---|---|---|
| Tanggal | Kapan transaksi terjadi | 2025-08-09 |
| No referensi | Nomor nota atau surat jalan | NT-2508-231 |
| Kode barang | Diambil dari master, bukan diketik | BRG-014 |
| Jenis | Masuk, keluar, atau penyesuaian | Keluar |
| Jumlah | Selalu angka positif | 3 |
| Petugas | Siapa yang mencatat | Rahmat |
Jumlah sebaiknya selalu positif, dengan arah pergerakan ditentukan lewat kolom jenis. Nulis angka negatif buat barang keluar kelihatan hemat kolom, tapi bikin rumus rekap dan filter jadi lebih ribet.
Nama barang, harga, dan kategori jangan masuk sini. Semuanya bisa ditarik dari master waktu kamu butuh.
Pakai kode barang sebagai penghubung, lalu tarik data master pakai XLOOKUP atau VLOOKUP. Buat ngitung saldo, pakai SUMIFS yang baca tabel transaksi dengan kriteria kode barang dan jenis transaksi.
Di sheet rekap dengan kode barang di kolom A, tarik nama dan harga:
Nama barang:
=XLOOKUP($A2, Master!$A:$A, Master!$B:$B)
Harga beli:
=XLOOKUP($A2, Master!$A:$A, Master!$E:$E)Kalau XLOOKUP belum ada di aplikasimu, VLOOKUP masih jalan dengan pencarian persis:
=VLOOKUP($A2, Master!$A:$G, 2, FALSE)Buat saldo, anggap tabel transaksi punya kode barang di C, jenis di D, dan jumlah di E:
=SUMIFS(Transaksi!$E:$E, Transaksi!$C:$C, $A2, Transaksi!$D:$D, "Masuk")
-SUMIFS(Transaksi!$E:$E, Transaksi!$C:$C, $A2, Transaksi!$D:$D, "Keluar")Dua jenis rumus itu yang bikin dua tabel terasa seperti satu, tanpa nyalin satu pun data. Detail cara kerjanya ada di panduan fungsi VLOOKUP dan panduan fungsi SUMIFS.
Pakai data validation dengan daftar diambil dari kolom kode di master barang. Setelah dipasang, kolom kode di tabel transaksi berubah jadi menu pilihan, dan kode yang nggak terdaftar ditolak otomatis.
Ini pengaman paling murah di seluruh file stok. Satu kesalahan ketik kode bikin transaksinya nggak kehitung di rekap mana pun, dan biasanya baru ketahuan waktu stok opname.
Buat mastiin nggak ada kode nyasar yang lolos dari sebelum data validation dipasang, jalanin pengecekan sekali:
=IF(COUNTIF(Master!$A:$A, C2)=0, "KODE ASING", "")Aku pegang file stok toko_berkah dari dataset ngulikdata, warung sembako di Semarang. Awalnya semuanya di satu sheet: 4.100 baris transaksi dengan nama barang, harga, dan kategori diketik ulang di tiap baris.
Waktu aku hitung jumlah nama barang unik, angkanya 169. Padahal barang yang beneran dijual cuma 142 jenis.
| Temuan | Jumlah | Akibatnya |
|---|---|---|
| Varian ejaan buat barang sama | 27 varian buat 9 barang | Rekap penjualan mecah |
| Harga beli beda di baris berbeda | 14 barang | Nilai persediaan meleset |
| Kategori kosong | 318 baris | Analisis per kategori nggak lengkap |
Barang yang paling parah ternyata yang paling laku. Satu merek minyak goreng punya 5 versi ejaan, dan di rekap mingguan dia kelihatan seperti lima produk kecil, bukan satu produk terlaris.
Akibatnya nyata di keputusan belanja. Barang itu nggak pernah masuk daftar prioritas restock, padahal seharusnya nomor satu.
Setelah dipecah jadi master 142 baris dan tabel transaksi 4.100 baris dengan kode barang, tiga masalah itu hilang sekaligus. Waktu nyusun rekap mingguan turun dari sekitar 90 menit jadi sekitar 15 menit, dan itu tanpa nambah satu pun rumus baru di luar lookup dan SUMIFS.
Bikin sheet ketiga bernama Rekap, isi kolom pertama dengan daftar kode barang dari master, lalu tarik semua sisanya pakai rumus. Sheet ini nggak boleh ada satu pun angka yang diketik tangan.
| Kolom rekap | Sumbernya |
|---|---|
| Kode barang | Salin dari master |
| Nama barang | Lookup ke master |
| Total masuk | SUMIFS ke transaksi |
| Total keluar | SUMIFS ke transaksi |
| Saldo | Selisih dua kolom sebelumnya |
| Batas minimum | Lookup ke master |
| Status | IF membandingkan saldo dan batas |
| Nilai persediaan | Saldo dikali harga beli dari master |
Rumus lengkap tiap kolomnya aku kupas satu per satu di 9 rumus Excel stok barang. Kalau kamu lebih nyaman pakai kombinasi lain buat lookup, ada juga INDEX MATCH yang lebih fleksibel.
Naruh kolom saldo di master barang. Saldo itu hasil hitungan, bukan data. Begitu diketik manual, dia bakal beda dari kenyataan dalam hitungan minggu.
Ngetik nama barang di tabel transaksi. Ini penyebab utama rekap yang mecah. Tulis kodenya aja, tarik namanya waktu butuh.
Pakai ulang kode barang yang udah pensiun. Barang lama yang nggak dijual lagi tetap perlu kodenya sendiri, soalnya data historisnya masih nyantol ke sana.
Nyampur satuan di barang yang sama. Sak dan kilogram di satu kode bikin saldonya nggak berarti. Tentukan satu satuan per barang, tulis di master.
Nggak pasang data validation. Kode salah ketik nggak nongol di rekap mana pun, dan biasanya baru ketahuan waktu stok opname berbulan-bulan kemudian.
Master barang nyimpan hal yang jarang berubah seperti kode, nama, satuan, kategori, harga beli, dan batas minimum, dengan satu barang satu baris. Tabel transaksi nyimpan tiap pergerakan barang: tanggal, kode, jenis, jumlah, dan petugas. Master jawab pertanyaan barang apa, transaksi jawab pertanyaan apa yang terjadi dan kapan.
Karena nama yang diketik berulang bakal punya banyak versi ejaan, dan rekapmu jadi mecah. Beras 5 kg dan Beras 5kg dianggap dua barang berbeda oleh rumus. Simpan nama sekali di master, tulis kodenya aja di transaksi, lalu kunci input itu pakai data validation.
Pakai kode barang sebagai penghubung. Tarik nama dan harga dari master pakai XLOOKUP atau VLOOKUP dengan kode sebagai kunci pencarian, lalu hitung saldo pakai SUMIFS yang membaca tabel transaksi dengan kriteria kode barang dan jenis transaksi. Dua rumus itu udah cukup tanpa menyalin data apa pun.
Enam sampai delapan kolom udah cukup buat UMKM: kode, nama, satuan, kategori, harga beli, batas minimum, plus opsional lokasi dan pemasok. Jangan taruh kolom saldo di master, soalnya saldo itu hasil hitungan dari tabel transaksi dan bakal jadi sumber selisih kalau diisi manual.
Jalan, dan caranya sama persis. Buat dua tab terpisah lalu pakai rumus lookup dan SUMIFS antar tab. Data validation ada di menu Data lalu Data validation, dengan sumber daftar diambil dari kolom kode di tab Master. Kelebihannya, beberapa orang bisa input bareng dari HP tanpa saling menimpa.
Tiga hal yang perlu kamu bawa:
Buka file stokmu sekarang, bikin tab baru bernama Master, lalu isi daftar barang unik beserta kodenya. Itu langkah pertama, dan biasanya selesai dalam satu jam buat toko dengan seratusan barang.
Kalau struktur dasarnya belum ada sama sekali, mulai dari template stok barang Excel. Buat referensi resmi rumus pencarian, cek dokumentasi fungsi VLOOKUP dari Microsoft.
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.