Excel Stok Barang: 9 Rumus Kunci untuk Saldo dan Peringatan Minimum

File stok barang yang bagus cuma butuh sembilan rumus. Sisanya tinggal disiplin nyatet.

Yang bikin file stok berantakan biasanya bukan kurang canggih, tapi ada kolom yang diisi manual padahal harusnya dihitung rumus. Satu kolom saldo yang diketik tangan cukup buat bikin seluruh laporan meleset.

Di bawah ini sembilan rumus kunci beserta contoh penulisannya, urut dari yang paling dasar sampai reorder point, plus contoh angka toko sembako dengan 142 jenis barang.

Rumus apa aja yang wajib ada di file stok barang?

Sembilan rumus ini nutup hampir semua kebutuhan stok UMKM: hitung saldo, rekap dari log transaksi, kasih peringatan minimum, narik data master, nahan error, hitung jumlah barang bermasalah, hitung nilai persediaan, dan tentuin kapan harus pesan lagi.

No	Kebutuhan	Rumus intinya
1	Saldo akhir sederhana	Penjumlahan langsung
2	Rekap dari log transaksi	SUMIFS
3	Alert stok minimum	IF
4	Alert tiga tingkat	IFS
5	Tarik nama dan harga	XLOOKUP atau VLOOKUP
6	Nahan error lookup	IFERROR
7	Hitung barang perlu restock	COUNTIF
8	Nilai persediaan	SUMPRODUCT
9	Titik pesan ulang	Perkalian dan penjumlahan

Aku bahas satu per satu di bawah, lengkap dengan penulisan yang bisa langsung kamu salin.

1. Gimana rumus saldo stok akhir?

Stok awal ditambah barang masuk dikurangi barang keluar. Kalau stok awal di C, masuk di D, dan keluar di E, tulis di kolom saldo:

=C2+D2-E2

Tarik ke bawah buat semua baris. Sekarang tiap kamu ubah angka masuk atau keluar, saldonya ikut update.

Kalau kamu mau saldo nggak pernah tampil minus (biasanya buat laporan yang dilihat orang lain), bungkus pakai MAX:

=MAX(0, C2+D2-E2)

Tapi hati-hati sama trik ini. Saldo minus itu sinyal ada data yang salah, dan menyembunyikannya bikin kamu kehilangan alarm. Aku lebih suka biarkan minus tampil di file kerja.

2. Gimana hitung saldo kalau datanya berbentuk log transaksi?

Pakai dua SUMIFS, satu buat baris masuk dan satu buat baris keluar, lalu kurangkan. Cara ini yang dipakai kalau kamu nyatet tiap transaksi di baris terpisah, bukan ngedit satu baris per barang.

Anggap sheet Log punya kode barang di B, jenis transaksi di C, dan jumlah di D:

=SUMIFS(Log!$D:$D, Log!$B:$B, $A2, Log!$C:$C, "Masuk")
 -SUMIFS(Log!$D:$D, Log!$B:$B, $A2, Log!$C:$C, "Keluar")

Bentuk log lebih repot di awal, tapi jauh lebih jujur. Kamu punya jejak siapa nyatet apa dan kapan, dan itu yang nolong waktu ada selisih.

Penjelasan kriteria bertingkatnya ada di panduan fungsi SUMIFS.

3. Gimana bikin peringatan stok minimum?

Pakai IF yang bandingin saldo dengan batas minimum tiap barang. Kolom F saldo, kolom G batas minimum:

=IF(F2<=G2,"RESTOCK","Aman")

Batas minimum jangan diisi angka yang sama buat semua barang. Beras yang laku 20 karung seminggu butuh batas jauh lebih tinggi dari sabun cuci yang laku 3 botol.

Setelah kolom status jadi, pasang conditional formatting supaya sel bertulisan RESTOCK berubah merah. Mata kamu bakal langsung ketarik ke sana tiap buka file.

4. Gimana bikin peringatan tiga tingkat?

Pakai IFS kalau kamu mau bedain kondisi kritis, waspada, dan aman. Rumusnya dibaca dari kiri, dan kondisi pertama yang benar yang dipakai.

=IFS(F2<=G2*0.5,"KRITIS", F2<=G2,"RESTOCK", TRUE,"Aman")

Barang yang saldonya di bawah separuh batas minimum masuk kategori kritis. Itu barang yang perlu kamu urus hari ini juga, bukan minggu depan.

Kalau versi Excel kamu belum punya IFS, pakai IF bertingkat. Cara nulisnya aku bahas di rumus IF bertingkat.

5. Gimana narik nama dan harga dari tabel master?

Pakai XLOOKUP dengan kode barang sebagai kunci pencarian. Data nama, kategori, dan harga beli disimpan sekali di tabel master, lalu ditarik ke mana pun butuh.

=XLOOKUP($A2, Master!$A:$A, Master!$B:$B)

Kalau XLOOKUP belum tersedia di aplikasimu, VLOOKUP masih jalan:

=VLOOKUP($A2, Master!$A:$D, 2, FALSE)

Angka FALSE di akhir wajib. Tanpa itu, Excel nyari kecocokan terdekat dan bisa ngasih nama barang yang salah. Detailnya ada di panduan fungsi VLOOKUP.

6. Gimana nahan error waktu kode barang belum terdaftar?

Bungkus rumus lookup pakai IFERROR. Tanpa ini, satu kode yang belum ada di master bikin kolomnya penuh tulisan error, dan rumus penjumlahan di bawahnya ikut gagal.

=IFERROR(XLOOKUP($A2, Master!$A:$A, Master!$B:$B), "BELUM TERDAFTAR")

Tulisan penggantinya jangan dibikin kosong. Teks yang jelas kayak BELUM TERDAFTAR bikin kamu tahu ada barang yang perlu ditambahkan ke master, bukan cuma sel kosong yang nggak dilirik siapa-siapa.

7. Gimana ngitung berapa barang yang perlu restock?

Pakai COUNTIF di kolom status. Angka ini yang kamu lihat pertama tiap pagi sebelum mutusin mau belanja atau nggak.

=COUNTIF($I$2:$I$150, "RESTOCK")

Buat ngitung yang kritis, ganti kriterianya. Buat ngitung barang di satu kategori yang perlu restock, naikkan ke COUNTIFS dengan dua kriteria:

=COUNTIFS($C$2:$C$150, "Sembako", $I$2:$I$150, "RESTOCK")

Taruh angka-angka ini di baris paling atas sheet, bukan di bawah tabel. Yang di bawah jarang kelihatan.

8. Gimana ngitung nilai persediaan?

Pakai SUMPRODUCT antara kolom saldo dan kolom harga beli. Rumus ini ngalikan tiap baris lalu menjumlahkan semuanya sekaligus, jadi kamu nggak perlu kolom bantu.

=SUMPRODUCT($F$2:$F$150, $H$2:$H$150)

Pastikan yang dipakai harga beli, bukan harga jual. Nilai persediaan pakai harga beli supaya nyambung dengan laporan keuangan dan perhitungan perputaran persediaan.

Cara kerja SUMPRODUCT lebih dalam ada di panduan SUMPRODUCT.

9. Gimana ngitung reorder point?

Kalikan rata-rata pemakaian harian dengan lead time pemasok, lalu tambah safety stock. Angka hasilnya jadi batas minimum yang lebih masuk akal ketimbang tebakan.

=B2*C2+D2

B2 pemakaian harian, C2 lead time dalam hari, D2 safety stock. Pemakaian hariannya bisa kamu ambil dari total keluar dibagi jumlah hari periode:

=SUMIFS(Log!$D:$D, Log!$B:$B, $A2, Log!$C:$C, "Keluar")/30

Hitung ulang angka ini tiap bulan. Pola jualan berubah, dan reorder point yang dihitung enam bulan lalu sering udah nggak nyambung.

Contoh kasus: 142 barang toko_berkah

Aku pasang sembilan rumus ini di file stok toko_berkah dari dataset ngulikdata, warung sembako dengan 142 jenis barang di Semarang. Sebelumnya, saldo diisi manual tiap sore.

Ukuran	Sebelum	Sesudah
Waktu rekap harian	Sekitar 40 menit	Sekitar 8 menit
Barang berstatus RESTOCK ketahuan	Waktu udah kosong	19 barang, sebelum kosong
Nilai persediaan	Nggak pernah dihitung	Rp 194 juta
Selisih saldo vs hitungan fisik	2,4 persen	0,7 persen

Angka yang paling penting ada di baris kedua. Dari 19 barang berstatus RESTOCK, 6 di antaranya nyumbang sekitar 41 persen omzet minggu itu.

Enam barang itu yang kalau kosong bikin pembeli pindah ke warung sebelah, dan sering nggak balik. Satu kolom rumus IF nyelametin omzet yang biasanya bocor tanpa ketahuan.

Kesalahan umum waktu pakai rumus stok

Ngisi kolom saldo manual. Ini sumber salah hitung nomor satu, terutama di file yang dipegang lebih dari satu orang. Biarkan rumus yang ngitung.

Pakai batas minimum yang sama buat semua barang. Barang cepat laku butuh batas jauh lebih tinggi. Hitung dari pemakaian harian, jangan dari perasaan.

Lupa kunci referensi pakai tanda dolar. Waktu rumus ditarik ke bawah, rentang datanya ikut geser dan hasilnya berantakan. Pakai $ di rentang tetap.

Nyimpan nama dan harga barang berulang di tiap baris transaksi. Waktu harga naik, kamu perlu ngedit ratusan baris. Simpan sekali di master, tarik pakai lookup.

Nyembunyiin saldo minus tanpa nyari penyebabnya. Angka minus itu alarm, bukan gangguan tampilan. Telusuri dulu, baru rapikan.

FAQ

Apa rumus stok akhir di Excel?

Stok awal ditambah barang masuk dikurangi barang keluar, misalnya =C2+D2-E2. Kalau catatanmu berbentuk log transaksi, pakai dua SUMIFS untuk menjumlahkan baris masuk dan baris keluar, lalu kurangkan. Jangan isi kolom saldo manual, soalnya itu sumber salah hitung paling sering di file yang dipakai banyak orang.

Gimana bikin peringatan stok minimum otomatis?

Pakai IF yang bandingin saldo dengan batas minimum tiap barang, misalnya =IF(F2<=G2,"RESTOCK","Aman"). Kalau butuh tiga tingkat peringatan, pakai IFS dengan ambang kritis dan waspada. Tambahkan conditional formatting supaya sel berstatus RESTOCK berubah merah dan gampang kelihatan.

Gimana cara hitung nilai persediaan di Excel?

Pakai SUMPRODUCT antara kolom saldo dan kolom harga beli, misalnya =SUMPRODUCT(F2:F150,H2:H150). Rumus ini ngalikan tiap baris lalu menjumlahkan semuanya sekaligus. Pastikan yang dipakai harga beli, bukan harga jual, supaya nyambung dengan laporan keuangan dan perhitungan perputaran persediaan.

Rumus mana yang dipakai buat reorder point?

Rata-rata pemakaian harian dikali lead time pemasok, ditambah safety stock, atau =B2*C2+D2 di Excel. Pemakaian hariannya bisa diambil dari total keluar dibagi jumlah hari periode. Hitung ulang angka ini tiap bulan biar ngikutin pola jualan yang berubah.

Rumus ini jalan di Google Sheets juga?

Semuanya jalan tanpa perubahan, kecuali XLOOKUP yang di sebagian akun Sheets belum tersedia. Kalau begitu, ganti pakai INDEX dan MATCH atau VLOOKUP dengan pencarian persis. Sisanya punya perilaku yang sama, jadi file stokmu bisa pindah antar dua aplikasi tanpa dibetulin ulang.

Pasang tiga dulu, sisanya nyusul

Tiga hal yang perlu kamu bawa:

• Saldo selalu hasil rumus, nggak pernah diketik tangan.
• Batas minimum dihitung dari pemakaian harian, bukan ditebak.
• Nilai persediaan pakai harga beli supaya nyambung ke laporan lain.

Buka file stokmu sekarang dan pasang tiga rumus pertama: saldo, status RESTOCK, dan jumlah barang yang perlu diisi ulang. Tiga itu aja udah ngubah cara kamu belanja minggu depan.

Buat struktur kolom yang bikin sembilan rumus ini gampang dipasang, cek template stok barang Excel. Referensi resminya ada di dokumentasi fungsi SUMIFS dari Microsoft.