กฏกติกาของฟอรัม : แจ้งชื่อและนามสกุลจริงในการสมัครสมาชิก--ตั้งชื่อกระทู้ให้ตรงกับปัญหาที่ถาม--เขียนอธิบายคำถามทั้งในตัวกระทู้และในแฟ้มแนบให้ละเอียด--ห้ามใช้คำว่า อ่ะ คับ หรือคำผวน หรือคำที่ไม่มีในพจนานุกรมไทย--โปรดตั้งชื่อแฟ้มให้สื่อถึงปัญหาที่ถาม--อย่าระบุชื่อให้คนใดคนหนึ่งเป็นผู้ตอบ--งดให้ความร่วมมือหรือช่วยตอบคำถาม กระทู้ที่ละเมิดกฏกติกาของฟอรัม
Results 1 to 12 of 12

Thread: ต้องการตัดสต็อกสินค้าแบบFIFO สร้างสูตรอย่างไรได้ครับ

  1. #1
    Excel Expert Volunteer pichartyapan's Avatar
    Join Date
    May 2007
    Location
    Boots Retail, สำนักงานใหญ่, Bkk.
    Job
    Retail Excellence Manager job func: Budget planning and allocation for each branch. Incentive scheme planning and evaluation for incentive payment. Routine and Ad-hoc sales analysis.
    Version
    Excel 2003-2007/Window vista & 2007
    Posts
    1,631

    Default ต้องการตัดสต็อกสินค้าแบบFIFO สร้างสูตรอย่างไรได้ครับ

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

    โจทย์ 1 สต็อก ที่มีเหลืออยู่ตาม InvNo ต่าง ๆ กันไป (คอลัมน์สีเหลือง)2 ยอดขายที่ได้มาเป็นยอดขายรวมของ Barcode นั้น ๆ (คอลัมน์สีแดง) (ไม่แจกลงตาม Inv No.)สิ่งที่ต้องการคือต้องการลงจำนวนเพื่อตัดสต็อกสินค้าตามจำนวนในแต่ละ Inv (คอลัมน์สีเขียว) แบบ FIFO ครับ


    โดยมีเงื่อนไข1 จำนวนใน QtySoldByInvNo ต้องไม่มากกว่า จำนวนในคอลัมน์ Stock2 เริ่มตัดสต็อกโดยเรียงแบบ FIFO (InvNo ใดมาก่อนให้เริ่มตัดก่อน)3 หากไม่เหลือสต็อกให้ตัดแล้ว ให้ลงจำนวน SoldQty ที่เหลือได้เลย

  2. #2
    Forum Admin สมเกียรติ's Avatar
    Join Date
    May 2007
    Location
    Excel Expert Training
    Job
    MS Excel Instructor
    Version
    Excel 2010/Windows 7
    Posts
    6,946
    Blog Entries
    5

    Default

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

    ได้ลองตัวอย่างที่นี่หรือยังครับ (ไม่เกี่ยวกับคำถามนี้)
    http://www.excelexperttraining.com/f...ead.php?t=3345

  3. #3
    Excel Expert Volunteer pichartyapan's Avatar
    Join Date
    May 2007
    Location
    Boots Retail, สำนักงานใหญ่, Bkk.
    Job
    Retail Excellence Manager job func: Budget planning and allocation for each branch. Incentive scheme planning and evaluation for incentive payment. Routine and Ad-hoc sales analysis.
    Version
    Excel 2003-2007/Window vista & 2007
    Posts
    1,631

    Default

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

    ขอบคุณที่อาจารย์ได้แนะนำสูตรมาให้ครับ แต่ในความเป็นจริงข้อมูลของผมมีประมาณ 30,000 Rows (records) ครับ ผมคงไม่สามารถพิจารณาข้อมูลทีละชุด ๆ ได้แน่ ๆ ครับ ผมจึงยังคงพยายามต่ออยู่ครับ...เป็นงานที่เร่งทีเดี่ยวครับ

    ผมได้ดู Link ที่อาจารย์แนบมาให้แล้วครับ แต่มันไม่ตรงความต้องการครับ :???:

  4. #4
    Forum Admin สมเกียรติ's Avatar
    Join Date
    May 2007
    Location
    Excel Expert Training
    Job
    MS Excel Instructor
    Version
    Excel 2010/Windows 7
    Posts
    6,946
    Blog Entries
    5

    Default

    ลองใหม่ครับ ใช้สูตรเดียว

    =MIN(D6,INDEX(H$6:H6,MATCH(99999999,$H$6:H6))-IF(H6<>0,0,SUM(OFFSET($G$5,MATCH(99999999,$H$6:H6),0,ROW()-ROW(INDEX(H$6:H6,MATCH(99999999,$H$6:H6))),1))))

    โดย
    =MATCH(99999999,$H$6:H6)
    ใช้หาตำแหน่งของเลข SoldQTY รายการสุดท้ายแค่ในชุดที่กำลังหายอดอยู่ ใช้สำหรับกำหนดตำแหน่ง Reference ให้กับสูตร Offset แต่ห้ามใส่เลข 0 ลงไปใน SoldQTY นะครับ

    =ROW(INDEX(H$6:H6,MATCH(99999999,$H$6:H6)))
    หาเลขที่ row ของเลขรายการสุดท้ายข้างต้น

    =ROW()-ROW(INDEX(H$6:H6,MATCH(99999999,$H$6:H6)))
    หาความสูงของช่วงที่ต้องการหายอดรวมสะสมของยอดที่หักไปแล้ว

    =IF(H6<>0,0,SUM(OFFSET($G$5,MATCH(99999999,$H$6:H6),0,ROW()-ROW(INDEX(H$6:H6,MATCH(99999999,$H$6:H6))),1)))
    ถ้ามียอดหัก ก็ไม่ต้องหักอีก
    แต่ถ้าไม่มียอดหัก ให้หักรวมเท่ากับยอดมีที่หักไปแล้ว โดยใช้สูตร Offset ขยายความสูงเท่ากับจำนวนยอดหักที่หักไปแล้ว

  5. #5
    Excel Expert Volunteer pichartyapan's Avatar
    Join Date
    May 2007
    Location
    Boots Retail, สำนักงานใหญ่, Bkk.
    Job
    Retail Excellence Manager job func: Budget planning and allocation for each branch. Incentive scheme planning and evaluation for incentive payment. Routine and Ad-hoc sales analysis.
    Version
    Excel 2003-2007/Window vista & 2007
    Posts
    1,631

    Default

    เรียนอาจารย์สมเกียรติครับ
    ขอบคุณครับที่อาจารย์ช่วย ผมเข้าใจว่ามันคงใช้เวลาพอสมควรทีเดี่ยว ผมลองใช้ดูมันยังไม่สามารถเอาค่ายอดขายที่เหลือหลังจากหักสต็อกจนถึงตัวสุดท้ายแล้วบันทึกเข้าไปได้ อย่างที่ผมบอกครับผมไม่สามารถทิ้งยอดขายชิ้นใด ๆ ได้เลย บางรายการราคาถึง 2000 - 5000 บาททีเดี่ยวครับ
    ผมได้พยายามทำสูตรขึ้น ดังที่ปรากฎใน file แนบครับ หากอาจารย์ยังมีเวลา ไม่ยุ่งเกินไป จะช่วยพิจารณา และแนะนำเพื่่อให้สุตรกระชับเข้า และมีความสมบูรณ์มากขึ้นจะขอบคุณมาก ๆ ครับ ผมกลัวว่ามันจะมีช่องโหว่แล้วผมมองไม่ครบครับ (ต้องอาศัยผู้มีประสบการณ์มาก ๆ ครับ) ผมได้ลองคำนวณกับ File จริงที่มีขนาด 30000 records ครับ ยอดที่ match แล้วไม่มี discrepancy ครับ จำนวนที่หยอดตรงกับจำนวนขายทั้งหมด
    แต่มันยังมีปัญหาอยู่ที่ว่า แค่เปิดไฟล์ก็ใช้เวลาถึง 5 นาทีแล้ว ยิ่งเวลาสั่งให้ apply สูตร กับ 30000 records แล้ว มันใช้เวลา calculate ถึง 25-30 นาทีเลยครับ (เครื่องที่ผมใช้เป็น note book, CPU = Intel centrino core 2 duo, ram 2 G. ผมกลัวว่าถ้าต้องเอาไปใช้จริงที่ บริษัท ที่Spect ต่ำกว่านี้ มีหวัง hang แน่ ๆ ครับ)
    ช่วยชี้แนะด้วยนะครับ

    พิชาติ :???:

  6. #6
    Excel Expert Volunteer vajra's Avatar
    Join Date
    May 2007
    Location
    Nakorn si thammarat
    Job
    freelance
    Version
    Excel 2007 Windows 7
    Posts
    865
    Blog Entries
    16

    Icon5

    ตอนแรกคิดว่าเป็นการตัด Stock ธรรมดา แต่ลองดูที่อธิบายประกอบไฟล์แนบแล้ว งง
    ครับ การทำตารางไม่สอดคล้องกับคำอธิบาย
    Stock (Columnสีเหลือง) เป็น Stock in หรือ Stock out หรือ Balance Stock
    สัมพันธ์กับ Invoice No หรือไม่ อย่างไร
    Last edited by vajra; 27 Oct 2008 at 11:45.

  7. #7
    Forum Admin สมเกียรติ's Avatar
    Join Date
    May 2007
    Location
    Excel Expert Training
    Job
    MS Excel Instructor
    Version
    Excel 2010/Windows 7
    Posts
    6,946
    Blog Entries
    5

    Default

    Quote Originally Posted by pichartyapan View Post
    ผมกลัวว่ามันจะมีช่องโหว่แล้วผมมองไม่ครบครับ :???:
    แทนที่จะหาสูตร น่าลองเปลี่ยนวิธีออกแบบโครงสร้างตารางหรือปรับวิธีตรวจสอบค่าของมนุษย์ จะได้ใช้วิธีที่ง่ายขึ้นก็เป็นได้

    แต่ถ้ายืนยันว่า ไม่ต้องการปรับโครงสร้างตารางหรือวิธีทำงาน ก็ขอให้คิดถึงข้อดีข้อเสียว่าวิธีใดคุ้มค่าที่สุด

    ปัญหานี้ คิดไม่ไหวครับ ยอมแพ้แล้ว

  8. #8
    Excel Expert Volunteer pichartyapan's Avatar
    Join Date
    May 2007
    Location
    Boots Retail, สำนักงานใหญ่, Bkk.
    Job
    Retail Excellence Manager job func: Budget planning and allocation for each branch. Incentive scheme planning and evaluation for incentive payment. Routine and Ad-hoc sales analysis.
    Version
    Excel 2003-2007/Window vista & 2007
    Posts
    1,631

    Default

    ขอบคุณอาจารย์ด้วยใจจริงครับ สัญญาครับว่าเมื่อผ่านมรสุมงานช่วงนี้ที่มีมากจริง ๆ แล้วจะมาลองใช้ link ที่อาจารย์แนะนำครับ (บริษัท freeze พนักงานครับ ใครลาออกไม่มีการรับเพิ่ม...ทีมผมมี 6 คน ตอนนี้เหลือกันอยู่แค่ 3 ครับ)

    ขอบคุณด้วยใจจริงครับ

    พิชาติ : )

  9. #9
    Excel Expert Volunteer vajra's Avatar
    Join Date
    May 2007
    Location
    Nakorn si thammarat
    Job
    freelance
    Version
    Excel 2007 Windows 7
    Posts
    865
    Blog Entries
    16

    Default

    ลองดูที่ Attached File บางทีอาจจะช่วยได้ครับ

  10. #10
    Excel Expert Volunteer pichartyapan's Avatar
    Join Date
    May 2007
    Location
    Boots Retail, สำนักงานใหญ่, Bkk.
    Job
    Retail Excellence Manager job func: Budget planning and allocation for each branch. Incentive scheme planning and evaluation for incentive payment. Routine and Ad-hoc sales analysis.
    Version
    Excel 2003-2007/Window vista & 2007
    Posts
    1,631

    Default

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

    พิชาติ

  11. #11
    Excel Expert Volunteer vajra's Avatar
    Join Date
    May 2007
    Location
    Nakorn si thammarat
    Job
    freelance
    Version
    Excel 2007 Windows 7
    Posts
    865
    Blog Entries
    16

    Default

    ขอแยกตอบดังนี้
    พิชาติ : ขอบคุณคุณVajraครับ น่าจะมีประโยชน์จริง ๆ
    Vajra : ด้วยความยินดีครับ ที่ Post ของผมจะช่วยให้คุณแก้ปัญหาได้

    พิชาติ :แต่ผมเองยังมีความรู้เรื่องของ VBA ไม่มากนัก
    Vajra : ผมก็เช่นเดียวกันครับ

    พิชาติ : สงสัยครับว่าไฟล์นี้ทำงานอย่างไรครับ
    Vajra : เขียน Code เลียนแบบการทำงานของคน (User) ถ้าคุณพิชาติ
    สามารถป้อนข้อมูลลงไปได้ถูกต้องตามเงื่อนไขโดยไม่ใช้สูตร การทำงาน
    ของ Code ที่เขียนก็เป็นเช่นนั้นเหมือนกัน

    พิชาติ :มีการซ่อนโค้ดไว้ที่ไหนครับ กด alt F11 เห็นแต่ form ในชีทก็เพียงแต่มีโค้ดเรียกฟอร์มตอนเปิดไฟล์เท่านั้น
    Vajra :ไม่มีการซ่อนหรอกครับ ลอง Dubble Click ที่ CommandButton1 บน Form ใน
    Project Explorer Window จะเห็น Codeทั้งหมดที่ Code Window

    ลองแกะ Code ดู ไม่ยากหรอกครับ
    ติดขัดตรงไหนก็ถามได้ครับ ยินดีตอบครับ ทาง E-Mail ก็ได้ครับ

    ศึกษาเพิ่มเติมที่ :
    http://www.excel-vba.com/vba-prog-1-1-editor.htm
    http://www.xlpert.com/toc.htm
    Last edited by vajra; 28 Oct 2008 at 22:42.

  12. #12
    Excel Expert Volunteer pichartyapan's Avatar
    Join Date
    May 2007
    Location
    Boots Retail, สำนักงานใหญ่, Bkk.
    Job
    Retail Excellence Manager job func: Budget planning and allocation for each branch. Incentive scheme planning and evaluation for incentive payment. Routine and Ad-hoc sales analysis.
    Version
    Excel 2003-2007/Window vista & 2007
    Posts
    1,631

    Default

    ขอบคุณครับสำหรับคำแนะนำ และ link ดี ๆ ที่แนะนำให้ครับ จะพยายามฝึกฝนครับ

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •