Skip to content
Home » [Update] Inventory Management ทำรายงานสินค้าคงคลังด้วย DAX และ Data Model ใน Excel | รายงานสินค้าและวัตถุดิบ excel – NATAVIGUIDES

[Update] Inventory Management ทำรายงานสินค้าคงคลังด้วย DAX และ Data Model ใน Excel | รายงานสินค้าและวัตถุดิบ excel – NATAVIGUIDES

รายงานสินค้าและวัตถุดิบ 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 ส่วนตัวเยอะหน่อยครับ)

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

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

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

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

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

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

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

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

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

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

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

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

แค่นี้เราจะได้ตารางขายที่ค่อนข้างฉลาดมากขึ้นแล้ว ซึ่งจริงๆ มันก็สามารถเช็คสต๊อคได้คร่าวๆ แล้วนะ ถ้าเราพอใจเท่านี้ก็จบเลยก็ได้ มันก็จะคล้ายๆ กับบทความภาค 3 ของผมนั่นเอง แต่ในบทความนี้เราจะไปต่อกับ Concept ของ 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)

จากนั้นเราต้องเพิ่มตารางวันที่เข้าไปอีกอันนึง เพื่อให้สามารถ 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"

[Update] Inventory Management ทำรายงานสินค้าคงคลังด้วย DAX และ Data Model ใน Excel | รายงานสินค้าและวัตถุดิบ excel – NATAVIGUIDES

จากที่ผม 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 ส่วนตัวเยอะหน่อยครับ)

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

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

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

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

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

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

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

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

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

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

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

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

แค่นี้เราจะได้ตารางขายที่ค่อนข้างฉลาดมากขึ้นแล้ว ซึ่งจริงๆ มันก็สามารถเช็คสต๊อคได้คร่าวๆ แล้วนะ ถ้าเราพอใจเท่านี้ก็จบเลยก็ได้ มันก็จะคล้ายๆ กับบทความภาค 3 ของผมนั่นเอง แต่ในบทความนี้เราจะไปต่อกับ Concept ของ 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)

จากนั้นเราต้องเพิ่มตารางวันที่เข้าไปอีกอันนึง เพื่อให้สามารถ 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"


สอนทำสต๊อกสินค้าง่ายๆ ด้วย EXCEL Part 1


มาทำสต๊อกสินค้าแบบง่ายๆ ด้วย Excel
เสียงคลิปเบา แก้ไขเรียบร้อยแล้ว
สามารถดูได้ในลิงค์ด้านล่าง
คลิปใหม่เสียงดีกว่าเดิม : https://youtu.be/ty4Duhx94A4
คลิปนี้จะใช้ฟังชั่น VLOOKUP เป็นหลักเพื่อใช้ในการค้นหารายละเอียดข้อมูลของสินค้า และฟังชั่น IF เพื่อตรวจสอบข้อมูลสินค้าคงเหลือในสต๊อก MIN MAX
ขออภัยถ้าขาดตกบกพร่อง หรือข้ามๆขาดๆ ไปด้วยครับ
วิธีการโหลด
https://youtu.be/38dLD1lkipE
อยู่ในโฟล์เดอร์ตัวอย่างไฟล์
ลิงค์หลัก : http://caneddir.com/4G3r
ลิงค์สำรอง : https://drive.google.com/drive/folders/1iTjuC88HRbFOgq6bMsErjP_cXLsr06h?usp=sharing

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

สอนทำสต๊อกสินค้าง่ายๆ ด้วย EXCEL Part 1

excel ช่วยคุมสต็อกสินค้า


excel ช่วยคุมสต็อกสินค้า

excel ช่วยคุมสต็อกสินค้า

Excel tip – รายงานการลาพนักงาน (sec 1) (Thaisub)


Excel tip - รายงานการลาพนักงาน (sec 1) (Thaisub)

การจัดทำตารางแสดงการตรวจนับสินค้าคงเหลือ – excel


การจัดทำตารางแสดงการตรวจนับสินค้าคงเหลือ - excel

การทำบัญชี รายรับ รายจ่าย ง่าย ๆ ด้วย excel


ตัวอย่างการทำบัญชี เงินสด รายรับ รายจ่าย ประจำเดือน และมี การสรุป กำไร ขาดทุน ในแต่ละเดือน โดยการใช้ สูตร และฟังก์ชั่น พื้นฐาน ของโปรแกรม excel
^_^ รบกวนกดติดตามด้วยนะค่ะ ^_^
PJ Excel Channel
เว็บแนะนำ การใช้งาน excel https://sites.google.com/site/excel2workshop/

การทำบัญชี รายรับ รายจ่าย ง่าย ๆ ด้วย excel

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

ขอบคุณมากสำหรับการดูหัวข้อโพสต์ รายงานสินค้าและวัตถุดิบ excel

Leave a Reply

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