Super Sale WeekClaude Skills — DISKON 20%
Tips

Cara Membuat Pelacak Inventaris di Excel: Langkah demi Langkah

Powerdrill Bloom·
Cara Membuat Pelacak Inventaris di Excel: Langkah demi Langkah

Untuk membuat pelacak inventaris di Excel, buatlah dua tabel. Tabel pertama mencantumkan item Anda, dengan kolom untuk ID item, nama, biaya per unit, stok awal, dan titik pemesanan ulang. Tabel kedua mencatat setiap pergerakan, dengan tanggal, ID item, tipe (Masuk atau Keluar), dan jumlah. Kemudian gunakan SUMIFS untuk menjumlahkan stok masuk dan stok keluar, lalu kurangi keduanya untuk mendapatkan stok yang tersedia.

Tambahkan kolom status yang menandai item pada atau di bawah titik pemesanan ulang, dan sorot baris tersebut dengan pemformatan bersyarat. Ringkasan singkat di bagian atas menunjukkan total nilai stok dan berapa banyak item yang perlu dipesan ulang.

Panduan ini mencakup setiap langkah, mulai dari struktur tabel hingga rumus dan peringatan. Panduan ini juga membahas contoh lengkap, ruang stok perlengkapan kantor kecil, dan cara yang lebih cepat untuk membangun pelacak dari hasil ekspor. Langkah-langkah ini bersumber dari halaman dukungan Microsoft, dibaca pada 18 November 2026.

Apa fungsi pelacak inventaris

Pelacak inventaris menjawab tiga pertanyaan pada saat kapan pun:

  • Berapa banyak jumlah setiap item yang kita miliki? Ini adalah stok yang tersedia.
  • Berapa nilainya? Ini adalah nilai stok, berdasarkan biaya per unit.
  • Apa yang perlu kita pesan? Ini adalah item yang berada pada atau di bawah titik pemesanan ulang mereka.

Pelacak yang baik juga menyimpan riwayat. Ketika hitungan terlihat salah, Anda dapat menggulir kembali melalui log pergerakan dan menemukan kapan stok masuk atau keluar.

Pelacak inventaris di Excel berfungsi dengan baik untuk inventaris kecil dan menengah. Ini cocok untuk ruang stok, toko kecil, perlengkapan kantor, suku cadang, dan peralatan acara. Begitu Anda melacak ribuan item di beberapa lokasi, sistem inventaris khusus biasanya lebih cocok.

Rencanakan pelacak: dua tabel

Sebelum Anda mengetik rumus apa pun, tentukan bagaimana struktur pelacak inventaris nantinya. Banyak pelacak sederhana menggunakan satu tabel dengan kolom jumlah tunggal yang ditimpa oleh orang-orang. Itu akan cepat rusak. Tidak ada yang tahu kapan sebuah angka berubah atau mengapa.

Desain yang lebih baik memisahkan daftar item dari pergerakan:

Tabel Satu baris per Kolom utama
Item Produk atau bagian ID Item, Nama, Kategori, Biaya per unit, Stok awal, Titik pemesanan ulang
Pergerakan Perubahan stok Tanggal, ID Item, Tipe (Masuk atau Keluar), Jumlah, Catatan

Tabel Item menyimpan fakta yang jarang berubah. Tabel Pergerakan bertambah setiap hari. Rumus di tabel Item membaca tabel Pergerakan dan menghitung stok saat ini.

Desain ini memiliki tiga manfaat. Anda tidak pernah menimpa angka. Setiap perubahan memiliki tanggal. Dan Anda dapat menjawab pertanyaan di kemudian hari, seperti berapa banyak stok yang keluar pada bulan Maret.

Ini juga membuat pelacak inventaris lebih aman untuk dibagikan. Staf hanya menambahkan baris ke tabel Pergerakan. Tabel Item, dengan rumus-rumusnya, dapat tetap terkunci.

Letakkan setiap tabel pada lembar kerjanya sendiri. Beri nama lembar tersebut "Items" dan "Movements" agar rumus mudah dibaca.

Bangun daftar item sebagai tabel

Mulailah dengan lembar Items. Ketik header di baris 1: ID Item, Nama, Kategori, Biaya per unit, Stok awal, dan Titik pemesanan ulang. Kemudian masukkan satu baris per item.

Selanjutnya, ubah rentang tersebut menjadi tabel Excel. Microsoft menjelaskan alasannya: "Untuk mempermudah pengelolaan dan analisis sekelompok data terkait, Anda dapat mengubah rentang sel menjadi tabel Excel." Untuk membuatnya:

  1. Pilih sel mana pun dalam data.
  2. Pilih Beranda > Format sebagai Tabel.
  3. Pilih gaya tabel.
  4. Pastikan kotak untuk header dicentang, lalu pilih OK.

Beri nama tabel tersebut "Items" pada tab Desain Tabel. Lakukan hal yang sama untuk log pergerakan di lembar lainnya, dan beri nama "Movements."

Tabel membantu dalam dua cara. Mereka bertambah secara otomatis saat Anda menambahkan baris. Dan rumus dalam kolom tabel akan terisi ke bawah dengan sendirinya. Microsoft mengatakan rumus yang dimasukkan dalam satu sel "secara instan diterapkan ke semua sel lain di kolom tabel tersebut."

Jaga agar ID item tetap singkat dan unik, seperti A-101. Gunakan ID tersebut di mana saja alih-alih nama, karena nama sering kali dieja secara berbeda.

Tambahkan drop-down ke log pergerakan

Kesalahan pengetikan adalah alasan utama mengapa hitungan inventaris meleset. Daftar drop-down menghentikan sebagian besar kesalahan tersebut.

Microsoft menjelaskan tujuannya dalam satu baris. "Gunakan validasi data untuk membatasi jenis data atau nilai yang dimasukkan pengguna ke dalam sel, seperti daftar dropdown." Untuk kolom Tipe:

  1. Pilih kolom Tipe di tabel Movements.
  2. Pilih Data > Validasi Data.
  3. Pada tab Pengaturan, di kotak Izinkan, pilih Daftar.
  4. Di kotak Sumber, ketik In,Out.
  5. Pastikan Dropdown dalam sel dicentang, lalu pilih OK.

Lakukan hal yang sama untuk kolom ID Item, tetapi arahkan sumbernya ke kolom ID Item di tabel Items. Sekarang orang hanya dapat memilih item yang memang ada.

Drop-down juga mempercepat entri. Seorang petugas ruang stok dapat mencatat pengiriman dalam beberapa detik dengan memilih item dan tipenya, lalu mengetik jumlahnya.

Anda juga dapat membatasi kolom Jumlah. Di kotak Izinkan, pilih Bilangan Bulat dan tetapkan minimum 1. Itu akan memblokir entri kosong dan negatif.

Hitung stok yang tersedia dengan SUMIFS

Sekarang tambahkan kolom kalkulasi ke tabel Items. SUMIFS menjumlahkan angka yang memenuhi lebih dari satu kondisi. Sintaks Microsoft adalah:

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Tambahkan kolom bernama "Stock in." Di sel pertamanya, masukkan:

=SUMIFS(Movements[Quantity],Movements[Item ID],[@[Item ID]],Movements[Type],"In")

Ini menjumlahkan setiap jumlah di tabel Movements yang ID itemnya cocok dengan baris tersebut dan tipenya adalah In. Tabel akan mengisi rumus tersebut ke bawah untuk setiap item.

Tambahkan kolom "Stock out" dengan rumus yang sama, tetapi ubah "In" menjadi "Out."

Kemudian tambahkan "On hand":

=[@[Opening stock]]+[@[Stock in]]-[@[Stock out]]

Terakhir, tambahkan "Stock value":

=[@[On hand]]*[@[Unit cost]]

Periksa satu item secara manual setelah Anda membuat rumus. Filter tabel Movements untuk item tersebut, jumlahkan baris In dan Out, lalu bandingkan hasilnya dengan kolom On hand.

Tandai item untuk dipesan ulang

Pelacak inventaris paling berguna ketika ia memberi tahu Anda apa yang harus dilakukan. Tambahkan kolom "Status" dengan rumus ini:

=IF([@[On hand]]<=[@[Reorder point]],"Reorder","OK")

Sekarang setiap baris akan bertuliskan Reorder atau OK. Tetapkan titik pemesanan ulang berdasarkan berapa lama pesanan sampai dan berapa banyak yang Anda gunakan dalam waktu tersebut.

Buat baris Reorder menonjol dengan pemformatan bersyarat:

  1. Pilih kolom Status.
  2. Pilih Beranda > Pemformatan Bersyarat > Aturan Penyorotan Sel > Teks yang Berisi.
  3. Ketik Reorder dan pilih warna isian, seperti merah muda.
  4. Pilih OK.

Microsoft mencatat bahwa ketika kotak dialog Teks yang Berisi muncul, Anda "memasukkan opsi pemformatan yang ingin Anda terapkan dan klik OK." Sorotan akan diperbarui setiap kali stok berubah.

Anda juga dapat mengurutkan atau memfilter kolom Status untuk melihat hanya baris Reorder sebelum Anda melakukan pemesanan.

Cari detail item dengan XLOOKUP

Log pergerakan menunjukkan ID item, yang sulit dibaca. Kolom pencarian menambahkan nama item di samping setiap pergerakan.

Microsoft mendeskripsikan XLOOKUP sebagai cara "untuk menemukan berbagai hal dalam tabel atau rentang berdasarkan baris." Sintaksnya adalah:

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Tambahkan kolom "Name" ke tabel Movements dan masukkan:

=XLOOKUP([@[Item ID]],Items[Item ID],Items[Name],"Unknown ID")

Jika seseorang memasukkan ID yang tidak ada, sel akan menunjukkan "Unknown ID." Itu membuat kesalahan mudah dikenali.

Microsoft mencatat bahwa "XLOOKUP tidak tersedia di Excel 2016 dan Excel 2019." Pada versi tersebut, gunakan INDEX dan MATCH, atau VLOOKUP dengan ID item di kolom pertama.

Tambahkan ringkasan di bagian atas

Bagian terakhir dari pelacak inventaris adalah ringkasan. Ringkasan singkat membantu siapa pun melihat status stok tanpa perlu menggulir. Tambahkan beberapa sel di atas tabel Items, atau pada lembar "Summary" terpisah.

Gunakan rumus-rumus ini:

  • Total nilai stok: =SUM(Items[Stock value])
  • Item yang harus dipesan ulang: =COUNTIF(Items[Status],"Reorder")
  • Unit yang keluar bulan ini: =SUMIFS(Movements[Quantity],Movements[Type],"Out",Movements[Date],">="&DATE(2026,11,1))

COUNTIF mengikuti pola yang sederhana. Dalam kata-kata Microsoft, ia menanyakan "Di mana Anda ingin mencari?" dan "Apa yang ingin Anda cari?"

Anda juga dapat menambahkan bagan batang nilai stok berdasarkan kategori. Pilih rentang ringkasan dan pilih Sisipkan > Bagan. Jaga agar ringkasan tetap kecil, dengan tiga hingga lima angka yang penting.

Contoh pengerjaan: ruang stok perlengkapan kantor

Berikut adalah contoh lengkap pelacak inventaris yang sedang digunakan. Sebuah kantor kecil melacak lima item perlengkapan. Tabel Items terlihat seperti ini sebelum November:

ID Item Nama Biaya per unit Stok awal Titik pemesanan ulang
A-101 Buku Catatan A5 $2.50 120 50
A-102 Pulpen (kotak) $6.00 40 20
B-201 Kertas printer (rim) $4.20 80 30
B-202 Kartrid toner $45.00 12 5
C-301 Kotak pengiriman (sedang) $0.80 300 100

Selama bulan November, tabel Movements mencatat perubahan ini. Buku catatan: 85 keluar dan 50 masuk. Pulpen: 25 keluar. Kertas: 60 keluar dan 40 masuk. Toner: 9 keluar. Kotak: 180 keluar.

Setelah rumus dijalankan, kolom kalkulasi menunjukkan:

ID Item Stok masuk Stok keluar Tersedia Nilai stok Status
A-101 50 85 85 $212.50 OK
A-102 0 25 15 $90.00 Reorder
B-201 40 60 60 $252.00 OK
B-202 0 9 3 $135.00 Reorder
C-301 0 180 120 $96.00 OK

Contoh ini juga menunjukkan mengapa log pergerakan itu penting. Kertas terlihat baik-baik saja pada 60 rim, tetapi log menunjukkan 60 keluar dalam satu bulan. Jika kecepatan itu berlanjut, kertas akan mencapai titik pemesanan ulangnya dalam hitungan minggu. Kolom jumlah tunggal akan menyembunyikan tren tersebut.

Periksa satu baris secara manual. Buku catatan dimulai pada 120, bertambah 50, dan berkurang 85, jadi 120 + 50 − 85 = 85. Itu cocok dengan kolom On hand.

Ringkasan menunjukkan total nilai stok sebesar $785.50 dan 2 item yang harus dipesan ulang. Pulpen dan toner keduanya berada di bawah titik pemesanan ulang mereka, jadi mereka masuk dalam pesanan minggu ini.

Buat pelacak inventaris di Excel dengan AI

Membangun pelacak membutuhkan waktu satu atau dua jam secara manual. Bagian yang lambat sering kali adalah datanya: membersihkan hasil ekspor dari sistem point-of-sale, mencocokkan nama item, dan memperbaiki ID duplikat.

Alat AI dapat melakukan pembersihan dan analisis tersebut untuk Anda. Berikut adalah metodenya dalam tiga langkah, menggunakan Powerdrill Bloom.

Langkah 1: Unggah file Excel, CSV, atau PDF Anda

Unggah hasil ekspor stok Anda, daftar pemasok, atau lembar hitungan bulan lalu. Excel AI Assistant bekerja dengan file spreadsheet secara langsung.

Sertakan daftar item dan pergerakan jika Anda memilikinya, meskipun dalam file terpisah.

Mengunggah ekspor stok untuk membuat pelacak inventaris di Excel dengan Powerdrill Bloom

Langkah 2: Minta pelacak dalam bahasa alami

Jelaskan apa yang Anda butuhkan. Sebagai contoh: "Buat pelacak inventaris dengan stok tersedia, nilai stok, dan tanda pemesanan ulang untuk item pada atau di bawah 20 unit." Mintalah untuk mencantumkan item yang perlu dipesan minggu ini.

Anda juga dapat mengajukan pertanyaan lanjutan, seperti kategori mana yang paling banyak menyerap uang tunai.

Langkah 3: Tinjau hasilnya dan ekspor

Periksa angka stok tersedia untuk beberapa item terhadap hitungan terakhir Anda. Kemudian ekspor tabel yang sudah dibersihkan kembali ke Excel, atau ubah temuan tersebut menjadi laporan singkat atau slide untuk tim Anda.

Ini berguna ketika pelacak inventaris sudah tertinggal. Ekspor bulanan dari sistem penjualan Anda dapat menunjukkan apa yang benar-benar keluar, yang dapat Anda bandingkan dengan log.

Pertahankan desain dua tabel untuk pembaruan harian. Gunakan langkah AI saat Anda membutuhkan pembersihan cepat atau tinjauan bulanan.

Meninjau stok tersedia sebelum mengekspor pelacak inventaris

Kesalahan umum yang harus dihindari

Sebagian besar masalah dengan pelacak inventaris berasal dari cara orang memasukkan data, bukan dari rumusnya. Ini adalah masalah yang paling sering muncul dengan pelacak inventaris di Excel.

  • Menimpa jumlah. Catat setiap perubahan sebagai pergerakan baru.
  • Nama alih-alih ID. "Pulpen" dan "Kotak pulpen" menjadi dua item. Gunakan ID unik.
  • Tidak ada stok awal. Tanpa hitungan awal, setiap angka stok tersedia akan salah.
  • Unit campuran. Lacak kotak atau satuan tunggal, jangan keduanya, untuk item yang sama.
  • Tidak ada hitungan rutin. Bandingkan pelacak dengan hitungan fisik setiap bulan atau kuartal.
  • Titik pemesanan ulang tidak pernah ditinjau. Perbarui saat penggunaan atau waktu tunggu pemasok berubah.
  • Baris log yang dihapus. Jangan pernah menghapus pergerakan untuk memperbaiki kesalahan. Tambahkan entri koreksi dengan catatan sebagai gantinya.
  • Rumus yang ditimpa ketikan. Lindungi lembar Items agar tidak ada yang mengganti rumus dengan angka.

Untuk menemukan item yang terlalu lama berada di rak, lihat panduan kami tentang cara mengenali inventaris yang lambat bergerak. Untuk meramalkan permintaan, lihat daftar alat AI untuk inventaris dan peramalan permintaan kami.

Ketika data stok Anda berada dalam hasil ekspor yang berantakan, coba Powerdrill Bloom untuk membersihkannya menjadi pelacak yang siap pakai.

Pertanyaan yang sering diajukan

Bagaimana cara membuat pelacak inventaris sederhana di Excel?

Buat tabel Items dengan ID item, nama, biaya per unit, stok awal, dan titik pemesanan ulang. Tambahkan tabel Movements yang mencatat setiap perubahan. Kemudian gunakan SUMIFS untuk menghitung stok masuk, stok keluar, dan stok tersedia.

Rumus apa yang menghitung stok tersedia di Excel?

Stok tersedia sama dengan stok awal ditambah stok masuk dikurangi stok keluar. Gunakan SUMIFS untuk menjumlahkan kuantitas Masuk dan Keluar untuk setiap item dari log pergerakan.

Bagaimana cara membuat Excel menandai stok rendah?

Tambahkan kolom Status dengan rumus IF yang mengembalikan "Reorder" ketika stok tersedia berada pada atau di bawah titik pemesanan ulang. Kemudian sorot sel tersebut dengan pemformatan bersyarat.

Seberapa sering saya harus memperbarui pelacak inventaris?

Catat setiap pergerakan saat itu terjadi, atau setidaknya sekali sehari. Kemudian bandingkan pelacak dengan hitungan fisik setiap bulan atau kuartal, dan perbaiki celah apa pun dengan entri penyesuaian.

Dapatkah saya melacak inventaris di Excel untuk beberapa lokasi?

Ya. Tambahkan kolom Lokasi ke tabel Movements, dan tambahkan Lokasi sebagai kondisi kedua dalam SUMIFS. Setiap item kemudian mendapatkan angka stok tersedia per lokasi.

Sumber: Dukungan Microsoft, Gambaran umum tabel Excel · Dukungan Microsoft, Menerapkan validasi data ke sel · Dukungan Microsoft, fungsi SUMIFS · Dukungan Microsoft, fungsi XLOOKUP · Dukungan Microsoft, Menggunakan pemformatan bersyarat untuk menyoroti informasi di Excel · Dukungan Microsoft, Menggunakan fungsi COUNTIF di Microsoft Excel.