สอนทำไฟล์ Excel บริหาร Stock สินค้าคงคลัง : Version 3 ระบุช่วงเวลา

สอนทำไฟล์ Excel บริหาร Stock สินค้าคงคลัง : Version 3 ระบุช่วงเวลา 1
รายชื่อบริษัทลูกค้าที่อบรมกับเทพเอ็กเซล
บทนำ : ทำไมต้องเรียนรู้ Power Query?

บทความนี้ก็จะเป็นการสอนใช้ Excel บริหาร Stock เป็นตอนที่ 3 แล้ว (ซึ่งเราค่อยๆ พัฒนามันขึ้นมาเรื่อยๆ) และในตอนนี้เราจะทำให้มันสามารถดูข้อมูลเฉพาะมนช่วงเวลาที่เราสนใจได้ครับ

ในไฟล์ version ก่อนหน้านี้เราได้มีการแยกตารางซื้อขายออกมาเป็นคนละตาราง เพื่อเพิ่มความยืดหยุ่นให้มากขึ้นไปแล้ว มาวันนี้เราจะสามารถกำหนดช่วงเวลาเพื่อที่จะสามารถดูข้อมูลเฉพาะในนช่วงที่ระบุได้ด้วย

สรุปข้อมูลแต่ละตาราง

ก่อนจะไปเขียนสูตรเพิ่ม ให้แก้ตัวเลขเล็กน้อย ในชีทตารางซื้อ (TableBuy) ดังนี้

สอนทำไฟล์ Excel บริหาร Stock สินค้าคงคลัง : Version 3 ระบุช่วงเวลา 2

ตารางขาย เดิมชื่อ TableSale ผมขอเปลี่ยนชื่อเป็น TableSell (เพราะมันจะได้เข้ากับ TableBuy หน่อย 555) แต่ข้อมูลยังเหมือนเดิม คือ

สอนทำไฟล์ Excel บริหาร Stock สินค้าคงคลัง : Version 3 ระบุช่วงเวลา 3

กลับมาในชีทตารางสรุป ให้เตรียมตารางดังนี้ครับ

สอนทำไฟล์ Excel บริหาร Stock สินค้าคงคลัง : Version 3 ระบุช่วงเวลา 4

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

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

โดยเขียนว่า =MAX(TableBuy[วันที่ซื้อ],TableSell[วันที่ขาย])

สูตรในตาราง Summary ของ Excel บริหาร Stock

มาดูใสนตารางบ้าง ในแต่ละคอลัมน์ หลักการจะคล้ายเดิมที่เราใช้ SUMIFS แต่จะมีการเพิ่มเงื่อนไขเรื่องวันที่เข้ามา โดยเราจะมีการใช้เครื่องหมายเปรียบเทียบมาช่วยด้วย (ใส่ในรูปแบบที่เป็น Text) เช่น หากต้องการยอดที่เกิดก่อนวันที่กำหนด เราก็จะใส่ Criteria Range เป็นช่วงวันที่ ส่วน Criteria จะเป็น “<“&วันที่ที่กำหนด เป็นต้น

ตั้งต้นงวด : เอายอดซื้อก่อนต้นงวด-ยอดขายก่อนต้นงวด

=SUMIFS(TableBuy[จำนวนซื้อ],TableBuy[Product],[@Product],TableBuy[วันที่ซื้อ],”<“&Summary!$B$1)-SUMIFS(TableSell[จำนวนขาย],TableSell[Product],[@Product],TableSell[วันที่ขาย],”<“&Summary!$B$1)

ซื้อเพิ่มในงวด : สูตรเหมือนซื้อเพิ่มในบทความก่อน แต่เพิ่มเงื่นไขวันที่ว่าต้อง >=วันที่ต้นงวด และ <=วันที่ปลายงวด

=SUMIFS(TableBuy[จำนวนซื้อ],TableBuy[Product],[@Product],TableBuy[วันที่ซื้อ],”>=”&Summary!$B$1,TableBuy[วันที่ซื้อ],”<=”&Summary!$D$1)

ขายออกในงวด : สูตรเหมือนซื้อเพิ่มในบทความก่อน แต่เพิ่มเงื่นไขวันที่ว่าต้อง >=วันที่ต้นงวด และ <=วันที่ปลายงวด

=SUMIFS(TableSale[จำนวนขาย],TableSale[Product],[@Product],TableSell[วันที่ขาย],”>=”&Summary!$B$1,TableSell[วันที่ขาย],”<=”&Summary!$D$1)

คงเหลือสิ้นงวด : =[@ตั้งต้นงวด]+[@ซื้อเพิ่มในงวด]-[@ขายออกในงวด]

คงเหลือล่าสุด : เอาข้อมูลซื้อลบขายทั้งหมดแบบไม่สนใจวันที่

=SUMIFS(TableBuy[จำนวนซื้อ],TableBuy[Product],[@Product])-SUMIFS(TableSell[จำนวนขาย],TableSell[Product],[@Product])

ต้องสั่งเพิ่ม : =[@คงเหลือล่าสุด]<[@จุดสั่งของเพิ่ม]

เมื่อเขียนสูตรครบหมดแล้ว จะได้ดังนี้

สอนทำไฟล์ Excel บริหาร Stock สินค้าคงคลัง : Version 3 ระบุช่วงเวลา 5

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

........

ใครที่ชื่นชอบบทความนี้และเห็นว่ามีประโยชน์ ช่วยบอกต่อเพื่อนๆ ของคุณด้วยนะครับ
ยิ่งเกิดประโยชน์กับคนเยอะๆ ผู้เขียนก็ยิ่งดีใจครับ คนแชร์ก็ได้ร่วมสร้างประโยชน์ด้วยนะ ^^

อย่าลืมกดติดตามเทพเอ็กเซลได้ที่ Facebook.com/ThepExcel
และสามารถติดตามคลิปวีดีโอเจ๋งๆ ได้ที่ YouTube Channel ของ Thep Excel ครับ


รายชื่อบริษัทลูกค้าที่อบรมกับเทพเอ็กเซล
บทนำ : ทำไมต้องเรียนรู้ Power Query?
  • 329
  •  
  •  
  •  
  •  

Posted on: October 1, 2019
Tags: ,

Leave a Reply

Your email address will not be published. Required fields are marked *