Inventory Management ทำรายงานสินค้าคงคลังด้วย DAX และ Data Model ใน Excel

จากที่ผม post ถามไปในเพจว่าอยากรู้การทำงานจริงเรื่องไหนมากที่สุด เรื่องที่มีคนตอบมามากที่สุดก็คือ Inventory Management หรือการทำรายงานสินค้าคงคลังนั่นเอง

ในบทความนี้จะเป็นตัวอย่างที่จะใช้ Concept ของ DAX และ Data Model มาช่วยในการทำรายงานให้เห็นมิติใหม่ๆ โดยนำมาประยุกต์ร่วมกับการใช้สูตร SUMIFS ที่ช่วยในการแสดงข้อมูลแบบทันที และช่วยในการ Validate การกรอกข้อมูล ซึ่งเดี๋ยวเพื่อนๆ จะเห็นว่ามันก็เข้าขากันได้ดีอยู่นะ

ซึ่งคนที่จะใช้วิธีนี้ ผมแนะนำว่าควรมี Power Pivot/Power Query ด้วยเพื่อความสะดวกในหลายๆ เรื่องนะครับ ดังนั้นถ้าจะใช้ Excel ต้องมี Excel 2013 professional plus ขึ้นไปนะครับ (ถ้าไม่มีก็ใช้ Power BI ได้)

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

บทความเก่า เรื่องการจัดการ Stock (ด้วยการเขียนสูตร) มี 3 ตอน

• ตอนที่ 1: สอนทำไฟล์ Excel บริหาร Stock สินค้าคงคลัง : Version 1 ง่ายสุดๆ
• ตอนที่ 2: สอนทำไฟล์ Excel บริหาร Stock สินค้าคงคลัง : Version 2 แยกตารางซื้อขาย
• ตอนที่ 3: สอนทำไฟล์ Excel บริหาร Stock สินค้าคงคลัง : Version 3 ระบุช่วงเวลา

เพื่อไม่ให้เสียเวลาเรามาเริ่มกับวิธี DAX & Data Model กันเลย

เตรียมข้อมูล Input ที่จำเป็น

ก่อนอื่นสิ่งที่ต้องทำคือ เตรียมข้อมูลดิบทั้งหมดก่อน ซึ่งผมจะมีตารางทั้งหมด 3 ตาราง ใน Excel (ทำเป็น Table ให้หมดนะ) ดังนี้

1.ตารางรายชื่อสินค้าทั้งหมด : ชื่อว่า ProductTable (เอาจริงๆ ใส่เป็นรหัสสินค้าคู่กับชื่อสินค้าก็ได้นะ)

2. ตารางบันทึกการซื้อสินค้า : ชื่อว่า BuyTable

3. ตารางบันทึกการขายของ : ชื่อว่า SellTable

ซึ่งเจ้าตารางการขายของเนี่ย เรามักจะต้องมีการเช็คด้วยนะว่าไม่ได้ขายเกินสิ่งที่มีอยู่ ดังนั้นผมจึงเขียนสูตร SUMIFS ให้ดึงข้อมูลสินค้าที่มีในสิ้นวันก่อนหน้าขึ้นมาแสดงให้เห็นชัดๆ และมีการ Validate ด้วยว่าห้ามขายเกินที่มีนะ

ซึ่งสูตรจำนวนสินค้าที่มีในวันก่อน (ในช่องสีส้ม) เขียนดังนี้

= รวมสินค้าที่ซื้อมาทั้งหมดในช่วงเวลาก่อนหน้า - รวมสินค้าที่ขายไปทั้งหมดในช่วงเวลาก่อนหน้า

=SUMIFS(BuyTable[จำนวนซื้อ],BuyTable[Product],[@Product],BuyTable[วันที่ซื้อ],"<"&[@วันที่ขาย])-SUMIFS([จำนวนขาย],[Product],[@Product],[วันที่ขาย],"<"&[@วันที่ขาย])

จากนั้นในช่องที่ทำการขายก็ใส่ Data Validation ได้ดังนี้ เพื่อเช็คว่าจำนวนขายมันห้ามเกินช่องจำนวนที่มีสิ้นวันก่อนด้านซ้ายมือนะ

จากนั้นเราก็สร้างคอลัมน์เตือนนิดนึงนะ ว่าสินค้าเหลือเท่าไหร่ และเหลือน้อยกว่าจุดสั่งซื้อแล้วหรือไม่ (Reorder Point/Safety Stock)

จำนวนคงเหลือ

=[@[จำนวนที่มี ณ สิ้นวันก่อน]]-[@จำนวนขาย]

ต้องสั่งเพิ่มหรือไม่?

=[@จำนวนคงเหลือ]<VLOOKUP([@Product],ProductTable,2,0)

จากนั้นใส่ Conditional Format เตือนไปนิดนึงในกรณีที่เป็น TRUE

แค่นี้เราจะได้ตารางขายที่ค่อนข้างฉลาดมากขึ้นแล้ว ซึ่งจริงๆ มันก็สามารถเช็คสต๊อคได้คร่าวๆ แล้วนะ ถ้าเราพอใจเท่านี้ก็จบเลยก็ได้ มันก็จะคล้ายๆ กับบทความภาค 3 ของผมนั่นเอง แต่ในบทความนี้เราจะไปต่อกับ Concept ของ Data Model ครับ

เอาข้อมูล Inventory ที่เตรียมไว้ทั้งหมดเข้า Data Model

จะเอาตารางเข้า Data Model ได้ อย่าลืม Enable Add-in Power Pivot ก่อนนะ ซึ่งต้องมี Excel 2013 Professional Plus ขึ้นไป (ให้ Activate Com Add-in ที่ชื่อว่า Power Pivot) หรือจะใช้ Power BI ก็ได้

เอาตารางทั้ง 3 อันเข้า Data Model ให้ครบทุกตาราง จากนั้นไปที่หน้า Diagram View แล้วผูก Relationship ของแต่ละตารางซะ (ตัวเชื่อมคือ Product)

เพิ่ม DateTable

จากนั้นเราต้องเพิ่มตารางวันที่เข้าไปอีกอันนึง เพื่อให้สามารถ Filter ข้อมูลตามวันที่ได้ ซึ่งถ้ามี Power BI เราจะใช้ New Table โดยใช้ =CALENDARAUTO() ได้ง่ายๆ เลย แต่ใน Excel จะไม่สามารถเขียน DAX Table ได้ ดังนั้นเราจะใช้ Power Query ช่วยทำนะครับ

เพื่อไม่ให้เสียเวลา ผมจะใช้ Code ที่เขียนแจกไว้ในบทความนี้เลยนะครับ

ดังนั้นให้สร้าง Blank Query จากนั้นใน Advanced Editor ของ Power Query ก็ใส่ Code ไปว่า

(StartDate as date, EndDate as date) as table =>
let
displayRegion="en-GB",
//displayRegion="th-TH",
//StartDate=#date(2017, 1, 1),
//EndDate=#date(2019,12,31),
DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
#"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
#"Inserted Quarter" = Table.AddColumn(#"Inserted Year", "Quarter", each "Q"& Text.From(Date.QuarterOfYear([Date])), type text),
#"Inserted Month" = Table.AddColumn(#"Inserted Quarter", "Month", each Date.Month([Date]), Int64.Type),
#"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.ToText([Date],"MMMM",displayRegion), type text),
#"Inserted Day" = Table.AddColumn(#"Inserted Month Name", "Day", each Date.Day([Date]), Int64.Type),
#"Inserted Day of Week" = Table.AddColumn(#"Inserted Day", "Day of Week", each Date.DayOfWeek([Date],1)+1, Int64.Type),
#"Added YearMonthNum" = Table.AddColumn(#"Inserted Day of Week", "YearMonthNum", each Date.ToText([Date],"yyyyMM",displayRegion)),
#"Added YearMonthText" = Table.AddColumn(#"Added YearMonthNum", "YearMonthText", each Date.ToText([Date],"yyyy-MMM",displayRegion))
in
    #"Added YearMonthText"

จากนั้นระบุวันที่ให้เริ่มต้นปีและจบสิ้นปีของปีที่ต้องการ

จากนั้นเอา DateTable ที่ Invoke ฟังก์ชันที่เราสร้างไว้ Close & Load to แล้วเลือกดังรูปเพื่อเอาข้อมูล DateTable เข้าสู่ Data Model ซะ

จากนั้นเราผูก Relationship ของตารางดังภาพ

สังเกตว่าฝั่งตาราง Dimension คือ ProductTable กับ DateTable จะเป็นเลข 1 (แปลว่าตัวเชื่อมมีค่า unique ไม่มีค่าซ้ำ) ส่วนตาราง Fact ทั้ง BuyTable และ SellTable จะเป็นฝั่งที่เป็น * นั่นคือตัวเชื่อมมีค่าซ้ำกันได้

สิ่งสำคัญที่ต้องรู้ก็คือ การไหลของการ Filter จะไหลจากฝั่ง 1 ไป * ได้ ทำให้เราจะสามารถ Filter ตาราง BuyTable และ SellTable ได้พร้อมกัน จาก ProductTable และ DateTable เลย

จากนั้นเรากลับไปที่ Data View และทำการ Mark DateTable ของเราให้ Excel มองว่าเป็นตารางวันที่ด้วย เพื่อให้ใช้ Time Intelligent Function ได้

นอกจากนั้นอย่าลืมจัดการเรื่องการ Sort ข้อมูลในคอลัมน์ที่เป็น Text เช่น Month Name ว่าให้ Sort by Column ชื่อ Month และ YearMonthText ให้ Sort by Column ชื่อ YearMonthNum ด้วยนะ เวลาไปแสดงรายงานจะได้ไม่เรียงแบบงงๆ

เริ่มสร้าง Measure เพื่อการคำนวณ Inventory

จากนั้นสร้าง Measure ที่ต้องการ และกำหนด Number Format ให้เรียบร้อยซะ
ซึ่งจะสร้างใน Power Pivot เลยก็ได้ โดยไปที่ Data View แล้วคลิ๊กบริเวณด้านล่าง แล้วใส่ ชื่อMeasure:=สูตร ลงไป

หรือจะกด icon excel ซ้ายบน เพื่อออกมาใน Excel ปกติ แล้วสร้าง Measure ก็ได้

สร้าง Basic Measure

ซึ่ง Measure ที่ผมสร้างจะมีดังนี้

TotalBuyQty:=SUM(BuyTable[จำนวนซื้อ])

TotalSellQty:=SUM(SellTable[จำนวนขาย])

ใน Excel นั้น Table ที่อยู่ของ Measure ค่อนข้างมีความสำคัญกว่าใน Power BI เพราะ Pivot ใน Excel สามารถยอมให้เรา Double Click ที่ Measure เพื่อ Extract ข้อมูลออกมาดูได้ ถ้าเอา Measure ไปไว้ที่ Table ไหน มันก็จะ Extract Table นั้น ดังนั้นผมจะจัดดังนี้

• TotalBuyQty ผมจะเอา Home Table ไว้ที่ BuyTable
• ส่วน TotalSellQty ผมจะเอา Home Table ไว้ที่ SellTable

หากเราสร้าง Pivot จาก Data Model ที่สร้าง เราก็สามารถลากสรุปแบบ Basic ได้ดังนี้ คือเราจะรู้ว่าแต่ละเดือนซื้อขายอะไรไปกี่ชิ้น

สิ่งสำคัญคือ การลาก Field Dimension มาใช้ เช่น Product จะต้องใช้จากตารางที่จะ Filter ตัวอื่นได้ด้วยนะครับ เช่น Field Product นั้นมีอยู่ใน 3 ตาราง เราจะต้องใช้จากตาราง ProductTable เท่านั้น ไม่ใช่เอา Product จาก SellTable หรือ BuyTable นะครับ

สร้าง Measure เพื่อคำนวณสินค้าคงเหลือ

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

สินค้าคงเหลือ = สินค้าเริ่มต้น + สินค้าที่ซื้อเพิ่ม - สินค้าที่ขายออก

ซึ่งของเราไม่มีสินค้าเริ่มต้น คือ เรา assume เป็น 0 ไปเลย ดังนั้นหากเราสามารถหาสินค้าที่ซื้อเพิ่มสะสม – สินค้าที่ขายออกสะสมได้เลย

AccumBuyQty = สินค้าที่ซื้อเพิ่มสะสม = สินค้าที่ซื้อตั้งแต่เริ่มต้น-เวลานั้นๆ

ซึ่งเราจะใช้ CALCULATE มาช่วยเปลี่ยน Filter Context ของ TotalBuyQty เป็นให้ปลด Filter ของ DateTable ออกทั้งหมดแล้วเปลี่ยนเป็นวันที่ <= วันที่ล่าสุดที่มองเห็นอยู่ ดังนี้ (ใครงงเรื่อง CALCULATE ให้ไปอ่านในนี้)

AccumBuyQty:=VAR currentDate=LASTDATE(DateTable[Date])
RETURN
CALCULATE([TotalBuyQty],DateTable[Date]<=currentDate)

ในขณะเดียวกันก็สามารถสร้าง AccumSellQty ได้ดังนี้

AccumSellQty:=VAR currentDate=LASTDATE(DateTable[Date])
RETURN
CALCULATE([TotalSellQty],DateTable[Date]<=currentDate)

และสินค้าคงเหลือก็คือเอาสองตัวลบกัน ดังนี้

BalanceQty:=[AccumBuyQty]-[AccumSellQty]

สรุปถ้าลองเอามาใส่ Pivot จะได้ดังนี้

จะเห็นว่าผลลัพธ์ออกมาถูกต้องเลย

ทีนี้เราเลือกเอาเฉพาะตัว Measure ที่สนใจลงมาใน Pivot ก็ได้ เช่น TotalBuyQty, TotalSellQty, BalanceQty นอกนั้นไม่เอา

สร้าง Measure อื่นๆ เกี่ยวกับการคำนวณ Inventory

นอกจากนี้จะเพิ่ม Measure อื่นๆ เช่น ยอดซื้อและยอดขายเข้าไปก็ได้ เช่น ใช้ SUMX มาช่วยคำนวณ เนื่องจาก SUM จะอ้างอิงได้แค่คอลัมน์จริงๆ คอลัมน์เดียว แต่ SUMX สามารถ SUM Expression ที่เกิดจากแต่ละคอลัมน์คำนวณกันได้ (ใครงงเรื่อง SUMX ให้ไปอ่านที่นี่)

TotalBuyAmt:=SUMX(BuyTable,BuyTable[จำนวนซื้อ]*BuyTable[ราคาซื้อต่อชิ้น])

TotalSellAmt:=SUMX(SellTable,SellTable[จำนวนขาย]*SellTable[ราคาขายต่อชิ้น])

ข้อดีของ Pivot คือการปรับเปลี่ยนหน้าตาได้แบบสบายๆ รวมถึงสามารถใส่ Slicer หรือ Timeline ให้กดเล่นได้ด้วย เช่น ผมอาจปรับแบบนี้ก็ได้

นอกจากนี้ก็ยังใส่ Measure ที่สามารถแสดงข้อมูลได้ว่าต้องสั่งของเพิ่มหรือไม่ก็ได้ เช่น

MinimumQty:=SUM(ProductTable[จุดสั่งของเพิ่ม])

NeedtoOrder:=[BalanceQty]<[MinimumQty]

นอกจากนี้ยังสามารถใส่ Measure กลุ่ม Time Intelligent ได้อีก เช่น จำนวนซื้อของในเดือนก่อนหน้า ซึ่งเราจะใช้ DATEADD มาช่วยกับ CALCULATE เพื่อทำการเปลี่ยน Filter Context (ใครงงเรื่อง DATEADD มาอ่านอันนี้)

BuyQtyLastMonth:=CALCULATE([TotalBuyQty],DATEADD(DateTable[Date],-1,MONTH))

แค่นี้เราก็จะได้เลขมา ซึ่งสามารถเขียนสูตรเปรียบเทียบก็ได้ว่า ซื้อเพิ่มขึ้นหรือลดลงเท่าไหร่ กี่% เป็นต้น (ถ้าต้องการ) แต่ผมขี้เกียจละ 555

นอกจากนี้ เรายังสามารถแสดงผลเป็นกราฟก็ได้ถ้าต้องการ แค่เลือกที่ Pivot ที่ออกแบบไว้แล้วกด Insert Chart แล้วเลือกกราฟที่ต้องการ เราก็จะได้กราฟแบบง่ายๆ แล้ว และยังใช้ผสมผสานกับ Slicer ได้อีก กดเล่นสบายเลย ได้รายงานที่ Interactive ขึ้นอีก

สรุป

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

• เราสามารถเปลี่ยนแปลงหน้าตารายงานได้ง่าย แค่ลาก Field Pivot ไปๆ มาๆ (ความสามารถของ Pivot Table)
• สามารถแสดงผลเป็นกราฟได้ (ความสามารถของ Pivot Chart / Visual)
• สามารถกดดูสิ่งที่สนใจได้อย่างง่ายดาย (ความสามารถของ Slicer)
• สรุปข้อมูลจากหลายตารางได้ (ความสามารถของ Data Model)
• สามารถแสดงการคำนวณที่ซับซ้อนมากกว่าแค่ Sum Count Average Max Min ธรรมดาๆ ก็ได้ (ความสามารถของ DAX Measure)

และทั้งหมดนี้คุณก็เอาไปทำใน Power BI ก็ได้ ซึ่งจะมี Visual เจ๋งๆ ให้เลือกมากกว่าของ Excel อีก ลองคิดดูว่ารายงานสินค้าคงคลัง Inventory Management คุณจะเจ๋งขึ้นได้ขนาดไหน!!

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