Perkongsian kemahiran Excel Praktikal: bijak menggunakan fungsi untuk mencipta jadual pembelian, jualan dan inventori statistik automatik

Dalam artikel sebelumnya " Perkongsian Kemahiran Praktikal Excel: Beberapa Operasi Praktikal "Alat Isih Lajur" ", kami mempelajari operasi praktikal beberapa alat isihan. Hari ini kita akan mempelajari empat fungsi dengan mahir, kita boleh mencipta "Jadual Statistik Belian, Jualan, Inventori dan Masuk dan Keluar" yang boleh mengira inventori secara automatik. Sebaik sahaja anda mempelajari operasi ini, anda boleh dibebaskan daripada kerja yang membosankan dan nikmati puisi dan kejauhan.

Bagaimana untuk membuat jadual pembelian, penjualan dan inventori yang secara automatik boleh mengira inventori masa nyata dan kuantiti masuk dan keluar Malah, ia tidak memerlukan teknologi yang sangat sukar? anda hanya perlu menguasai empat fungsi dan Anda boleh membuatnya sendiri jika anda mempunyai beberapa kemahiran asas menyunting dan menaip Excel.

Empat fungsi ini ialah: vlookup, iferror, sumif dan if. Seterusnya, biarkan veteran membawa anda langkah demi langkah untuk mencipta jadual masuk dan keluar gudang ini.

Fungsi Perihalan "Pembelian, Jualan, Inventori dan Jadual Statistik Masuk dan Keluar":

Fungsi statistik masa nyata: Anda hanya perlu merekodkan keluar dan masuk jadual aliran dalam format yang ditetapkan Secara automatik melaksanakan statistik masa nyata pada inventori terkini dan kuantiti masuk dan keluar.

Fungsi peringatan pintar: Apabila inventori item lebih rendah daripada kuantiti stok keselamatan, ia akan ditanda secara automatik untuk mencapai kesan amaran.

Komposisi "Pembelian, Jualan, Inventori dan Jadual Statistik Masuk dan Keluar":

Mengikut keperluan paling asas, ia biasanya mengambil tiga Bahagian: asas jadual data (juga dipanggil jadual maklumat asas), jadual rekod masuk dan keluar (juga dipanggil jadual butiran aliran), jadual statistik inventori (juga dipanggil jadual pertanyaan hasil). Berikut menerangkan kaedah ketiga-tiga bahagian ini masing-masing.

1. Jadual data asas

Reka bentuk mengikut keperluan sebenar syarikat, fahami prinsip asas, jadual harus dapat mencerminkan semua atribut item, dan setiap atribut disimpan dalam lajur yang berasingan. Jadual tidak perlu cantik, dan sel yang digabungkan tidak boleh muncul.

Sebagai contoh, angka berikut ialah jadual data asas yang agak standard:

Nota: Nombor siri tidak diperlukan, hanya untuk carian mudah; biasanya Gunakan kod produk sebagai satu-satunya asas Jika secara kebetulan produk syarikat anda tidak mempunyai kod, nombor siri boleh digunakan sebagai kod.

Untuk memastikan ketepatan data statistik, apabila terdapat produk baru, rekod perlu ditambah ke dalam jadual Jika terdapat produk usang, tidak perlu memadam rekod asal.

2. Jadual rekod masuk dan keluar

Biasanya keluar dan masuk disimpan dalam dua helaian, atau ia boleh digabungkan bersama Storan, untuk kemudahan, kami menggabungkannya sebagai contoh.

Lajur data dalam jadual perlu mengandungi maklumat produk asas serta tarikh dan kuantiti gudang masuk dan keluar Formatnya secara kasar:

Dalam jadual aliran di atas, hanya lajur biru A, E, F dan G perlu direkodkan mengikut masa. Maklumat asas seperti klasifikasi, nama dan lajur unit dijana secara automatik melalui formula Anda mesti meneka, sudah tiba masanya untuk vlookup muncul di atas pentas!

Ya, ini adalah apabila vlookup mula dimainkan Daripada gambar di bawah, anda boleh melihat bahawa tiga lajur selepas pengekodan semuanya diperoleh menggunakan fungsi vlookup. Formula dalam sel B2 ialah:

=VLOOKUP($A2,基础数据表!$B:$E,COLUMN(B1),0)

Tafsiran formula: vlookup memerlukan sejumlah empat parameter dan format asas ialah

=vlookup(查找值，查找区域，列数，精确查找)

• Parameter pertama

  mewakili kandungan yang anda ingin cari Ambil perhatian bahawa kerana formula perlu ditarik ke bawah, tambahkan $A2 di hadapan A untuk mengunci lajur. untuk mengelakkan ralat semasa menarik ke kanan pengekodan sebagai lajur pertama, kerana pengekodan berada dalam data asas Lajur B jadual, jadi kawasan itu juga bermula dari lajur B bukannya lajur A. Ini mesti diingat kerana ramai orang baru membuat kesilapan di tempat ini apabila menggunakan vlookup $

• Parameter ketiga menunjukkan lajur kawasan carian kandungan yang dikembalikan Kerana formula perlu ditarik ke kanan, kami menggunakan

  sebagai bilangan lajur yang dikembalikan. Fungsi 基础数据表!$B:$E

  lajur ialah untuk mendapatkan nombor lajur parameter. Kami ingin mengembalikan lajur C dalam
, iaitu lajur 2. Nombor lajur sel B1 dalam keseluruhan jadual data asas parameter ialah 2, jadi
• digunakan di sini untuk menunjukkan bilangan lajur yang akan dikembalikan. Apabila formula ditarik ke kanan, B1 akan menjadi C1, dan nombor lajur akan berubah daripada 2 kepada 3, mencapai tujuan menarik ke kanan formula untuk melengkapkan rujukan berbilang lajur.

  column(B1)

  Parameter terakhir 0 bermaksud carian tepat. 基础数据表$B:$E

Tiga lajur terakhir jadual, tarikh dan kuantiti masuk dan keluar, boleh direkodkan mengikut situasi sebenar. Dalam keadaan biasa, carta alir ini dilengkapkan, tetapi untuk menggunakannya dengan lebih bijak, bahagian vlookup juga boleh dioptimumkan.

Apabila kami memasukkan kod produk, mungkin terdapat ralat dalam entri (atau kod baharu yang tiada dalam jadual data asas), dan kemudian kami akan mendapat beberapa kod bercelaru:

Kesannya tidak kelihatan sangat cantik, jadi anda perlu menggunakan fungsi lain iferror untuk bekerjasama dengan vlookup untuk menyelesaikan masalah ini. Formula diubahsuai kepada: =IFERROR(VLOOKUP($A2,基础数据表!$B:$E,COLUMN(B1),0),"编码有误请核查！")

Sesetengah rakan mungkin telah melihat fungsi iferror buat kali pertama:

=iferror(公式，公式结果错误时显示的内容) Formula hanya memerlukan dua parameter ialah a formula, dan parameter kedua ialah parameter semasa Apa yang perlu dipaparkan apabila hasil formula salah. Dalam contoh ini, parameter pertama ialah vlookup Apabila hasil vlookup adalah betul, iferror tidak mempunyai kesan Tetapi apabila hasil vlookup adalah salah, kandungan yang diperlukan akan dipaparkan dalam contoh ini. Pengekodan mempunyai Sila semak secara tidak sengaja! Nota: Jika kandungan yang akan dipaparkan adalah teks, ia mesti disertakan dalam tanda petikan.

3. Jadual statistik inventori

Fungsi jadual statistik inventori ini adalah untuk memaparkan status inventori semua produk dalam masa nyata adalah lebih kurang seperti berikut Maklumat: kuantiti keluar terkumpul, kuantiti masuk terkumpul, kuantiti inventori semasa diperlukan, kuantiti stok keselamatan dan sama ada ia kehabisan stok juga diperlukan;

Jadual statistik ini tidak perlu dibuat secara berasingan Anda hanya perlu menambah kandungan yang disenaraikan selepas jadual data asas >

Seperti yang anda lihat, enam lajur telah ditambahkan ke bahagian belakang jadual data asas Hanya inventori awal dan kuantiti stok keselamatan yang perlu dimasukkan Kuantiti keluar terkumpul, kuantiti masuk terkumpul dan sama ada ia kehabisan Stok semua disertakan. Ini dicapai melalui formula Berikut adalah penjelasan ringkas tentang medan ini:

Inventori awal: Ia juga boleh dipanggil inventori inventori direkodkan apabila jadual statistik masuk dan keluar didayakan.

Kuantiti keluar terkumpul (lajur G): Statistik yang diperoleh menggunakan formula

:

=SUMIF(出入库记录表!A:A,B2,出入库记录表!F:F)

Analisis formula: fungsi sumif memerlukan tiga parameter, struktur asas Untuk

=SUMIF(条件区域, 条件, 求和区域) parameter pertama

mewakili lajur keadaan; syarat yang harus dipenuhi (bersamaan dengan kod item dalam baris);

• 出入库记录表!A:ADengan cara yang sama, gantikan parameter ketiga

  dengan
untuk mendapatkan kuantiti pergudangan terkumpul (lajur H):

• B2

Kuantiti inventori semasa: Gunakan inventori awal - kuantiti keluar kumulatif untuk mengumpul kuantiti masuk;

• 出入库记录表!F:F

Kuantiti stok keselamatan: Dalam contoh ini, 50 ditetapkan. Ini boleh ditentukan mengikut kes demi kes bagi setiap produk. Item ini perlu dimasukkan secara manual.

Sama ada ia kehabisan stok: Fungsi IF digunakan di sini, formulanya ialah: 出入库记录表!$F:$F出入库记录表!$G:$G

Format asas fungsi If ialah

;

Dalam contoh ini, syaratnya ialah , yang bermaksud apabila kuantiti inventori semasa lebih besar daripada kuantiti stok keselamatan, anda akan mendapat kosong "kehabisan stok".

Pada masa yang sama, tetapkan pemformatan bersyarat untuk lajur ini Apabila stok habis, gunakan warna untuk mendapatkan kesan yang menarik perhatian.

Kaedah tetapan adalah untuk memilih lajur k, klik [Pemformatan Bersyarat] → [Serlahkan Peraturan Sel] → [Sama]: =IF(I2>J2,"","缺货")

di sebelah kiri Enter perkataan "kehabisan stok" dalam kotak, pilih kesan yang diingini di sebelah kanan, dan sahkan.

Pada ketika ini, jadual kemasukan dan keluar statistik automatik boleh dilaksanakan dengan mudah! Dengan alat ini, anda tidak perlu lagi bimbang tentang salah mengira inventori gudang beribu-ribu item Setelah anda mendapati bahawa terdapat kekurangan, anda boleh memberitahu pemerolehan untuk membelinya, dan kecekapan juga telah bertambah baik!

Akhir sekali, saya ingin menyatakan bahawa idea reka bentuk untuk jadual statistik masuk dan keluar yang serupa adalah lebih kurang sama Beberapa pengoptimuman boleh dibuat berdasarkan aplikasi sebenar. ia boleh dilakukan dalam jadual data asas, dan kemudian gunakan kuantiti * harga seunit untuk mendapatkan jumlah.

Adalah idea yang baik untuk menggunakan kesahihan data untuk menyeragamkan kemasukan data Contohnya, jika pengekodan perlu unik, anda boleh menetapkan kesahihan untuk menghalang kemasukan berulang (anda boleh meninggalkan mesej jika anda tidak. tidak tahu bagaimana untuk melakukan ini lagi).

Sediakan perlindungan formula untuk mengelakkan salah operasi daripada memusnahkan formula dan menjejaskan ketepatan data, dsb...

Cadangan pembelajaran berkaitan: tutorial cemerlang

Atas ialah kandungan terperinci Perkongsian kemahiran Excel Praktikal: bijak menggunakan fungsi untuk mencipta jadual pembelian, jualan dan inventori statistik automatik. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!