Stok barang di Excel cukup tiga sheet. Master menyimpan daftar barang, stok awal, dan batas minimum. Masuk-Keluar mencatat tiap transaksi. Ringkasan menghitung stok akhir dengan SUMIFS dan menampilkan “Restock” saat stok sama dengan atau di bawah batas minimum. Rumusnya sama di Excel dan Google Sheets.
Kalau Excel Anda memakai pengaturan regional Indonesia, pemisah argumen rumus biasanya titik koma (;), bukan koma. Rumus di artikel ditulis dengan koma.
Contoh kasus
Toko alat tulis kecil mencatat empat barang selama Oktober 2026. Nama barang dan angka stok di bawah hanya contoh.
Sheet 1: Master
Satu baris untuk satu barang. Kode barang dibuat unik dan tidak diubah setelah dipakai.
| Kolom 1 | A (Kode) | B (Nama) | C (Satuan) | D (Stok awal) | E (Stok minimum) |
|---|---|---|---|---|---|
| 1 | Kode | Nama | Satuan | Stok awal | Stok minimum |
| 2 | BK-01 | Buku tulis 40 lembar | pak | 50 | 20 |
| 3 | PN-01 | Pulpen hitam | pcs | 120 | 50 |
| 4 | PS-01 | Pensil 2B | pcs | 80 | 30 |
| 5 | PH-01 | Penghapus | pcs | 40 | 15 |
Stok minimum adalah angka yang Anda tentukan sendiri, misalnya kebutuhan penjualan untuk waktu tunggu pemasok. Tidak ada angka baku.
Sheet 2: Masuk-Keluar
Satu baris untuk satu kejadian. Kolom C hanya berisi dua kata: Masuk atau Keluar. Di artikel ini sheet tersebut diberi nama Transaksi. Nama berisi tanda hubung seperti Masuk-Keluar harus dibungkus tanda kutip tunggal di rumus, jadi satu kata lebih ringkas.
| Kolom 1 | A (Tanggal) | B (Kode) | C (Jenis) | D (Jumlah) | E (Nama barang) |
|---|---|---|---|---|---|
| 1 | Tanggal | Kode | Jenis | Jumlah | Nama barang |
| 2 | 01/10/2026 | BK-01 | Masuk | 30 | Buku tulis 40 lembar |
| 3 | 02/10/2026 | PN-01 | Keluar | 45 | Pulpen hitam |
| 4 | 04/10/2026 | PS-01 | Keluar | 25 | Pensil 2B |
| 5 | 06/10/2026 | BK-01 | Keluar | 40 | Buku tulis 40 lembar |
| 6 | 08/10/2026 | PN-01 | Masuk | 60 | Pulpen hitam |
| 7 | 12/10/2026 | PN-01 | Keluar | 100 | Pulpen hitam |
| 8 | 15/10/2026 | PH-01 | Keluar | 25 | Penghapus |
| 9 | 20/10/2026 | PS-01 | Keluar | 30 | Pensil 2B |
Kolom E diisi otomatis agar Anda bisa memastikan kodenya benar:
=IFERROR(VLOOKUP(B2,Master!$A$2:$B$100,2,FALSE),"Kode tidak ada")
Hasil di E2: Buku tulis 40 lembar. Kalau kode salah ketik, kolom E menampilkan “Kode tidak ada” dan Anda tahu ada yang keliru sebelum stok jadi salah. Fungsi yang dipakai: VLOOKUP dan IFERROR. Pilihan lain ada di XLOOKUP vs INDEX-MATCH.
Dropdown untuk kode dan jenis
Dropdown membuat kolom B dan C selalu berisi nilai yang sah.
| Aplikasi | Langkah |
|---|---|
| Excel | Pilih B2:B500, tab Data > Data Validation > Allow List, Source =Master!$A$2:$A$100. Untuk kolom C, Source diketik Masuk,Keluar. (Microsoft) |
| Google Sheets | Pilih B2:B500, Data > Data validation > Add rule > Dropdown from a range, pilih Master!A2:A100. Untuk kolom C pilih Dropdown, isi dua item: Masuk dan Keluar. (Google) |
Langkah Excel di atas dijalankan di Excel for the web pada 28 Oktober 2026, memakai buku kerja berlocale Indonesia dan antarmuka berbahasa Inggris. Source berisi tiga item yang dipisah koma diterima, dan panah dropdown muncul dengan tiga pilihan. Contoh di tangkapan layar memakai nama minuman, bukan kode barang di artikel ini.
Dialog Data Validation di Excel for the web (antarmuka bahasa Inggris): Allow List, Source berisi item dipisah koma.
Hasilnya: sel dengan panah dropdown dan tiga pilihan (Excel for the web, antarmuka bahasa Inggris).
Pembahasan umum dropdown ada di membuat dropdown list. Bila Anda mengetik Masuk,Keluar di Excel pada regional Indonesia, pemisahnya bisa titik koma: Masuk;Keluar. Periksa pemisah di komputer Anda, karena itu tidak saya uji di Excel desktop.
Sheet 3: Ringkasan
Satu baris per barang. Kolom A sampai C menyalin dari Master. Kolom D sampai H berisi rumus.
| Kolom 1 | A (Kode) | B (Nama) | C (Awal) | D (Masuk) | E (Keluar) | F (Stok akhir) | G (Status) | H (Saran pesan) |
|---|---|---|---|---|---|---|---|---|
| 2 | BK-01 | Buku tulis 40 lembar | 50 | 30 | 40 | 40 | Aman | 0 |
| 3 | PN-01 | Pulpen hitam | 120 | 60 | 145 | 35 | Restock | 65 |
| 4 | PS-01 | Pensil 2B | 80 | 0 | 55 | 25 | Restock | 35 |
| 5 | PH-01 | Penghapus | 40 | 0 | 25 | 15 | Restock | 15 |
Rumus baris 2, disalin ke bawah:
| Sel | Rumus | Hasil | Fungsinya |
|---|---|---|---|
| A2 | =Master!A2 | BK-01 | Kode dari Master |
| B2 | =Master!B2 | Buku tulis 40 lembar | Nama dari Master |
| C2 | =Master!D2 | 50 | Stok awal |
| D2 | =SUMIFS(Transaksi!$D$2:$D$500,Transaksi!$B$2:$B$500,A2,Transaksi!$C$2:$C$500,"Masuk") | 30 | Total masuk untuk kode ini |
| E2 | =SUMIFS(Transaksi!$D$2:$D$500,Transaksi!$B$2:$B$500,A2,Transaksi!$C$2:$C$500,"Keluar") | 40 | Total keluar untuk kode ini |
| F2 | =C2+D2-E2 | 40 | Stok akhir |
| G2 | =IF(F2<0,"Cek data",IF(F2<=Master!E2,"Restock","Aman")) | Aman | Status |
| H2 | =IF(F2<=Master!E2,Master!E2*2-F2,0) | 0 | Contoh saran: isi lagi sampai dua kali stok minimum |
Cara membaca SUMIFS di D2: jumlahkan kolom D sheet Transaksi, tetapi hanya baris yang kodenya sama dengan A2 dan jenisnya “Masuk”. Untuk BK-01 hanya baris 2 yang cocok, yaitu 30. Penjelasan argumen ada di SUMIFS, dan versi satu syarat di SUMIF.
Rentang dikunci dengan $ supaya saat rumus disalin ke bawah, rentang transaksi tidak ikut bergeser.
Coba di lembar berikut. Catatan: demo menulis rentang tanpa tanda $ (misalnya C2:C9), karena itu setiap baris punya rumus yang sama persis. Di Excel dan Sheets tetap pakai $ seperti tabel di atas.
Stok akhir dan peringatan restock
Kiri: catatan masuk dan keluar. Kanan: ringkasan per barang dengan SUMIFS.
| A | B | C | D | E | F | G | H | I | J | K | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Kode | Jenis | Jumlah | Barang | Awal | Minimum | Masuk | Keluar | Stok akhir | Status | Sisa di atas minimum |
| 2 | BK-01 | Masuk | 30 | BK-01 | 50 | 20 | 30 | 40 | 40 | Aman | 20 |
| 3 | PN-01 | Keluar | 45 | PN-01 | 120 | 50 | 60 | 145 | 35 | Restock | -15 |
| 4 | PS-01 | Keluar | 25 | PS-01 | 80 | 30 | 0 | 55 | 25 | Restock | -5 |
| 5 | BK-01 | Keluar | 40 | PH-01 | 40 | 15 | 0 | 25 | 15 | Restock | 0 |
| 6 | PN-01 | Masuk | 60 | ||||||||
| 7 | PN-01 | Keluar | 100 | ||||||||
| 8 | PH-01 | Keluar | 25 | ||||||||
| 9 | PS-01 | Keluar | 30 |
Sel biru bisa diubah: jumlah transaksi (kolom C) dan stok minimum (kolom F). Coba turunkan minimum penghapus jadi 14, statusnya berubah dari Restock ke Aman. Kolom K menunjukkan selisih stok akhir dari minimum; nilai 0 atau negatif berarti Restock.
Panah, Enter, dan Tab memindah sel. Esc mengembalikan sel ke isi awal.
Pelajaran
=SUMIFS(C2:C9,A2:A9,D2,B2:B9,"Masuk")
Jumlahkan kolom C hanya untuk baris yang kodenya sama dan jenisnya Masuk.
=IF(I2<0,"Cek data",IF(I2<=F2,"Restock","Aman"))
Stok sama dengan minimum sudah dihitung Restock karena memakai <=.
Cara membaca hasilnya
PN-01 (pulpen): 120 awal + 60 masuk − 145 keluar = 35. Batas minimum 50, jadi statusnya Restock. Saran pesan: 50 × 2 − 35 = 65.
PH-01 (penghapus) tepat bernilai 15, sama dengan minimumnya. Rumus memakai <= bukan <, jadi tetap muncul Restock. Anda yang menentukan: kalau batas minimum berarti “boleh sampai angka ini”, ganti ke <. Operatornya dijelaskan di operator perbandingan.
Rumus saran pesan di H hanya contoh aturan: “isi sampai dua kali minimum”. Ganti sesuai kebiasaan belanja Anda, atau hapus kolomnya.
Jumlah barang yang perlu dipesan ulang bisa dihitung dengan COUNTIF. Di contoh ini hasilnya 3:
=COUNTIF(G2:G5,"Restock")
Warna merah otomatis untuk Restock
Hanya membaca kata “Restock” mudah terlewat. Beri warna dengan Conditional Formatting.
| Aplikasi | Langkah |
|---|---|
| Excel | Pilih A2:H5, Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, isi =$G2="Restock", pilih warna isi merah muda, OK. (Microsoft, halaman untuk Mac dengan langkah yang sama) |
| Google Sheets | Pilih A2:H5, Format > Conditional formatting, Format cells if > Custom formula is, isi =$G2="Restock", pilih warna, Done. (Google) |
Tanda $ di depan G mengunci kolom, jadi seluruh baris ikut berwarna. Pola yang sama dibahas di menandai data duplikat dengan Conditional Formatting.
Beda Excel dan Google Sheets
| Hal | Excel | Google Sheets |
|---|---|---|
| SUMIFS, IF, COUNTIF, VLOOKUP | Sama | Sama |
| Referensi antar-sheet | Master!A2 | Master!A2 |
| Letak menu dropdown dan warna kondisi | Data > Data Validation, Home > Conditional Formatting | Data > Data validation, Format > Conditional formatting |
| Rentang tumbuh otomatis | Ubah daftar jadi Table (Ctrl+T), rumus ikut meluas | Artikel ini memakai rentang panjang seperti $D$2:$D$500 supaya rumusnya sama di kedua aplikasi |
| Dipakai bersama banyak orang | Perlu berbagi berkas lewat layanan seperti OneDrive | Berbagi lewat tombol Share di berkas yang sama |
Jika beberapa orang mencatat stok bersamaan, Sheets biasanya lebih praktis. Untuk satu komputer kasir, Excel cukup.
Kesalahan yang sering terjadi
- Kode barang berbeda antara Master dan Transaksi. “BK-01” dan “BK-01 " (ada spasi) dianggap berbeda. Dropdown mencegahnya.
- Jenis diketik “masuk”, “Masuk “, atau “Barang masuk”. SUMIFS tidak membedakan huruf besar kecil tetapi spasi dan kata lain membuat baris tidak cocok. Pakai dropdown.
- Rentang SUMIFS kurang panjang. Rentang artikel ini sampai baris 500. Perpanjang bila transaksi lebih banyak.
- Stok awal diganti tiap bulan. Ini membuat total lama hilang. Biarkan stok awal tetap dan buat sheet transaksi baru per periode, atau pindahkan sisa stok sebagai stok awal baru saat Anda mengarsipkan.
- Stok negatif tidak terlihat. Rumus G menampilkan “Cek data” jika F kurang dari 0. Itu tanda ada barang keluar yang belum tercatat masuk, atau salah ketik jumlah.
- Retur dan barang rusak tidak dicatat. Tambahkan jenis baru, misalnya “Retur”, lalu tambahkan satu SUMIFS dan ubah rumus stok akhir.
- Angka diketik dengan satuan. “50 pak” adalah teks dan tidak ikut dijumlahkan. Ketik 50, satuannya di Master.
Cara angka di artikel ini diperiksa
Semua angka di tabel Ringkasan (masuk, keluar, stok akhir, status, saran pesan, dan jumlah Restock = 3) dihitung dengan skrip Node memakai hot-formula-parser. Parser itu tidak mendukung VLOOKUP, IFERROR, dan SUMIFS dengan benar, jadi untuk fungsi tersebut skripnya memakai penggantian tulisan sendiri. Saya tidak menjalankannya di Excel atau Google Sheets. Langkah menu mengutip bantuan resmi Microsoft dan Google yang ditautkan di atas.
Template stok sedang direncanakan. Daftar yang tersedia nanti ada di halaman template.
Pola penjumlahan bersyarat serupa dipakai di rencana anggaran perjalanan. Dasar-dasarnya ada di 50 rumus dasar.