หลายคนได้สอบถามมาที่ผมว่า พอจะมีวิธีใช้ Excel เป็นตัวจัดการเรื่องข้อมูล Stock สินค้าหรือไม่?

โดยปัญหาที่มักถามมาก็มีหลากหลายประเด็น เช่น

  • มีรับสินค้าเข้า จ่ายสินค้าออก อยากรู้ว่าสินค้าคงเหลือเป็นเท่าไหร่?
  • อยากให้มีการขึ้นสีเตือนเมื่อสินค้าเหลือน้อยกว่าที่กำหนด (จะได้ Re-Order สั่งของเพิ่ม)
  • อยากทำรายงานเป็น Dashboard เพื่อให้เห็นภาพรวมของสินค้าที่กำลังบริหารอยู่
  • อยากให้พนักงานกรอกข้อมูลที่เดียว แล้วทุกอย่างวิ่งไปออกเป็นรายงานเอง
  • อยากให้มีการคำนวณต้นทุนตามหลักการบัญชี เช่น แบบ FIFO (First-In-First-Out)

และอื่นๆ อีกมากมาย

ซึ่งหลายคนคิดว่าการทำข้อมูลแบบนี้ ต้องเขียนสูตรหรือใช้ VBA ยุ่งยากมากมาย แต่จริงๆ แล้วมันไม่ได้ยากมากมายอย่างที่คิดครับ ซึ่งวันนี้ผมจะขอมาแนะนำ Concept และวิธีการทำคร่าวๆ เพื่อให้สามารถนำไปประยุกต์กันต่อได้ครับ (แน่นอนว่าคุณควรจะต้องมีพื้นฐาน Excel ระดับกลางๆ ถึงจะอ่านเข้าใจนะครับ)

Concept สำคัญ

Concept สำคัญมีอยู่ 3 เรื่องด้วยกัน มีทั้งเรื่องเกี่ยวกับการทำงานใน Excel /  เรื่องความรู้ด้านการจัดการสินค้า / เรื่อง Concept ความคิด เรามาดูแต่ละอันกันครับ

  1. การทำงานใน Excel ควรจะแยก Input และ Output ให้ออกจากกันอย่างชัดเจน :
    • เราไม่ควรให้มีการคีย์ข้อมูลดิบลงไปในรายงานสุดท้าย พูดง่ายๆ คือ หลายคนเอารายงานมาเป็นแบบฟอร์มกรอกข้อมูลซะเลย ซึ่งก่อให้เกิดปัญหาหลายอย่าง เช่น ไม่สามารถพลิกแพลงไปดูมุมมองอื่นๆ ได้ หรือ อาจมีการคีย์ข้อมูลผิด รวมถึงคีย์ข้อมูลลำบาก เพราะอาจต้องหาก่อนว่าตัวเองควรจะคีย์ข้อมูลตรงไหนดี (เพราะรายงานอาจมีหลายหน้ามาก)
    • จะให้ดีมากๆ ก็ให้แยกส่วนที่เป็น Input / Calculate (มีการคำนวณเพิ่ม) / Output ออกจากกันอย่างชัดเจนเลยก็ได้ครับ
  2. เราควรรู้จักความสัมพันธ์ : หรือสมการที่ใช้ในการจัดการ Stock  เช่น
    • สินค้าต้นงวด + สินค้าที่เปลี่ยนแปลง = สินค้าปลายงวด หรือ
      สินค้าต้นงวด + (สินค้าเข้า – สินค้าออก) = สินค้าปลายงวด
    • สินค้าต้นงวดวันนี้ = สินค้าปลายงวดเมื่อวาน
  3. ฺBegin with the End in Mind : เวลาเราคิดหรือออกแบบ Excel เราควรเริ่มจากหน้าตารายงานสุดท้ายที่อยากได้ก่อน เพื่อที่จะคิดย้อนกลับว่าเราจะต้องมีการกรอกข้อมูลอะไรบ้าง ถึงจะทำรายงานแบบนั้นๆ ได้
    เช่น ผมอาจอยากได้แบบนี้ (คิดคร่าวๆ ก่อน สามารถปรับตอนหลังได้)
    draft4

เริ่มออกแบบฐานข้อมูล

จากหน้าตาคร่าวๆ ที่คิดไว้

สิ่งที่เราควรต้องมีในฐานข้อมูลที่เป็น Input คือ

  • วันที่ทำรายการ
  • ชื่อสินค้า
  • ประเภทรายการ ว่า ทำให้สินค้าเข้า หรือ สินค้าออก
  • จำนวนสินค้าที่เกิดจากรายการนั้นๆ
  • เลขอ้างอิงรายการ (ไม่จำเป็น แต่ถ้ามีได้จะดีกว่า)

สิ่งที่เราควรจะต้องคำนวนเพิ่ม คือ

  • สินค้าต้นงวด
  • สินค้าปลายงวด

ข้อตกลงการกรอกข้อมูล

  • ใน Input 1 บรรทัด คือ 1 รายการ ซึ่งเพื่อความง่าย ตอนนี้จะออกแบบให้มีสินค้าได้แค่ 1 ประเภท/1 รายการ ก่อนนะครับ
  • กรอกตามลำดับเหตุการณ์ จากบนลงล่าง

ดังนั้นฐานข้อมูลเบื้องต้นจะมีหน้าตาคล้ายๆ แบบนี้

database-01

ลองกรอกข้อมูลดูก็จะเป็นประมาณนี้

  • บรรทัด 5 กับ 6 เกิดจาก Bill ตัวเดียวกัน แต่เราเขียนแยกบรรทัดกันเพราะเป็นสินค้าคนละตัว
  • บรรทัด 5,6,7 เกิดในวันเดียวกัน แต่เกิดจาก Bill 2 ใบ

database-03

เริ่มทำรายงาน

ต่อไปก็ถึงเวลาที่จะคำนวณสินค้าเข้า/ออก/คงเหลือ ของแต่ละวันแล้ว
ซึ่งจะใช้วิธีเขียนสูตร หรือจะทำ PivotTable ก็ได้ครับ ตามสะดวกเลย

สมมติว่าในบทความนี้ผมใช้ PivotTable แบบง่ายๆ ก่อนละกัน ลองจัด Layout ให้ได้ดังรูป

pivot-02

ใครนึกไม่ออกว่าทำไง? ดูเฉลยได้ตรงนี้

pivot-08

ทีนี้ผมจะสร้าง calculated item เป็นปริมาณสินค้าเปลี่ยนแปลง เกิดจาก สินค้าเข้า-สินค้าออก (วิธีนี้มีข้อจำกัดเยอะ แต่ก็พอจะตอบโจทย์แบบ basic ได้ครับ)

pivot-03

pivot-04

จากนั้นให้เปลี่ยนรูปแบบการแสดงผลเป็น Running Total in (base… วันที่ทำรายการ) แทน เพื่อทำการสะสมค่าสินค้าเปลี่ยนแปลงของแต่ละวัน

pivot-05

จากนั้นให้ Filter เอา สินค้าเข้า, สินค้าออก ออกไปจากรายงาน (เพราะผมไม่สนใจที่จะนับสะสม)
เพื่อให้เหลือแต่สินค้าเปลี่ยนแปลง (แบบสะสม)

pivot-06

จากนั้นผมทำการเปลี่ยนชื่อ Label คำว่า “สินค้าเปลี่ยนแปลง” โดยการคลิ๊กแล้วพิมพ์ชื่อใหม่เลย จะได้ไม่งง

เพียงเท่านี้ก็จะได้ข้อมูลสินค้าคงเหลือแต่ละวันแล้วครับ (เวลาใช้ PivotTable อย่าลืมเรื่อง Data Source / กด refresh ด้วยนะครับ)

pivot-07

ใครอ่านแล้วยังงงๆ ก็ถามได้นะครับ แต่ถ้าให้ดีผมแนะนำให้ไปฝึกการใช้ PivotTable เยอะๆ เลย

ในบทความต่อไปจะอธิบายวิธีการอื่นๆบ้าง ซึ่งจะซับซ้อนมากขึ้นครับ