Snasui Excel Forum

สวัสดีคะ ตอนนี้กำลังทำฐานข้อมูลสำหรับสินค้าคงคลังนะคะ ตอนนี้ปัญหาอย่างแรกที่เจอคือ
1.คือเวลามีการเคลื่อนไหวของคลังสินค้า(stock_movement) เช่น สินค้าเข้า หรือ ออกจะมีการบันทึกข้อมูลไว้ตลอดเวลา แต่ที่ต้องการเพิ่มคือถ้ามีของเข้าซึ่งเป็นสินค้าใหม่ ไม่มีข้อมูลในฐานข้อมูลมาก่อน เลยต้องการให้ข้อมูลเพิ่มลงในฐานข้อมูลสินค้าคงคลังที่อยู่อีกชีท (product) อัตโนมัตินะคะ
2.เนื่องจากเวลาที่เราบันทึกความเคลื่อนไหวของสินค้า(stock_movement) เราจะบันทึกทั้งสินค้าเข้าและออกในชีทเดียวกัน ดังนั้นในชีทฐานข้อมูลสินค้าคงคลังเราต้องการให้เพิ่มเฉพาะข้อมูลสินค้าใหม่ที่รับเข้ามาอย่างเดียว เราควรตั้งสูตรแยกอย่างไรคะ ว่าจะเพิ่มเฉพาะที่รับเข้ามานะ
3.เวลาทำการตัดสต็อกสินค้าแบบอัตโนมัติ(stock_bal) ซึ่งเราอ้างอิงมาจากการบันทึกความเคลื่อนไหวของสินค้าคงคลัง กรณีมีของเข้า-ออก จะตั้งสูตรให้รันอัตโนมัติ โดยอ้างอิงจาก รหัสสินค้า แต่สำหรับสินค้าบางชิ้น นอกจากจะมีรหัสสินค้าหลักแล้ว ยังมีรหัสย่อยที่ใช้ในการแบ่งอีก เราจะตั้งสูตรให้คำนวณค่าที่มีทั้งรหัสสินค้าหลัก และรหัสสินค้าย่อยตรงกันได้อย่างไรคะ


ขอบคุณคะ

wasinee35 wrote:1.คือเวลามีการเคลื่อนไหวของคลังสินค้า(stock_movement) เช่น สินค้าเข้า หรือ ออกจะมีการบันทึกข้อมูลไว้ตลอดเวลา แต่ที่ต้องการเพิ่มคือถ้ามีของเข้าซึ่งเป็นสินค้าใหม่ ไม่มีข้อมูลในฐานข้อมูลมาก่อน เลยต้องการให้ข้อมูลเพิ่มลงในฐานข้อมูลสินค้าคงคลังที่อยู่อีกชีท (product) อัตโนมัตินะคะ

ปกติบันทึกอย่างไร โปรแกรมจะทราบได้อย่างไรว่าเคยมีในฐานข้อมูลหรือไม่เคยมีในฐานข้อมูลครับ

ข้อมูลนำเข้ามาจากไหนมีการคีย์เองหรือว่าใช้โปรแกรมในการบันทึกข้อมูล ช่วยเล่ารายละเอียดในการทำงานว่ามีขั้นตอนอย่างไรมาด้วยครับ

wasinee35 wrote:2.เนื่องจากเวลาที่เราบันทึกความเคลื่อนไหวของสินค้า(stock_movement) เราจะบันทึกทั้งสินค้าเข้าและออกในชีทเดียวกัน ดังนั้นในชีทฐานข้อมูลสินค้าคงคลังเราต้องการให้เพิ่มเฉพาะข้อมูลสินค้าใหม่ที่รับเข้ามาอย่างเดียว เราควรตั้งสูตรแยกอย่างไรคะ ว่าจะเพิ่มเฉพาะที่รับเข้ามานะ

ข้อนี้ก็เช่นเดียวกันกับข้อแรก ข้อมูลที่ว่านั้นนำเข้าด้วยวิธีใด ปกติหากต้องการจะ List รายการใหม่แบบไม่ซ้ำสามารถใช้ Remove Duplicate หรือ Advanced Filter มาช่วยได้ ซึ่งจะเป็นเหมือนการ Refresh เพื่อวางรายการ Stock ทั้งหมดว่ามีอะไรบ้าง

wasinee35 wrote:3.เวลาทำการตัดสต็อกสินค้าแบบอัตโนมัติ(stock_bal) ซึ่งเราอ้างอิงมาจากการบันทึกความเคลื่อนไหวของสินค้าคงคลัง กรณีมีของเข้า-ออก จะตั้งสูตรให้รันอัตโนมัติ โดยอ้างอิงจาก รหัสสินค้า แต่สำหรับสินค้าบางชิ้น นอกจากจะมีรหัสสินค้าหลักแล้ว ยังมีรหัสย่อยที่ใช้ในการแบ่งอีก เราจะตั้งสูตรให้คำนวณค่าที่มีทั้งรหัสสินค้าหลัก และรหัสสินค้าย่อยตรงกันได้อย่างไรคะ

ช่วยยกตัวอย่างให้เห็นว่าข้อมูลเป็นอย่างไร ต้องการคำตอบเป็นอย่างไร แสดงตัวอย่างคำตอบที่ต้องการมาด้วยสัก 2-3 ตัวอย่าง จะได้สะดวกในการทำความเข้าใจและเขียนสูตรในการหาคำตอบครับ

จะนำเข้าข้อมูลด้วยการพิมพ์เองคะ จะกรอกข้อมูลใน sheet stock_movement นะคะ

สำหรับข้อ 3 นะคะ
คือ ใน ชีท stock_bal ช่อง stock นะคะ อย่างเช่นในส่วน item 1 อยากให้คำนวณผลรวมของจำนวนของเข้าซึ่งอ้างอิงจาก ชีท stock_movement เช่น item 1 รหัสสินค้าหลัก (part no.) 65-52822-7 , รหัสสินค้าย่อย (serial no) D01314 มีของเข้าจำนวนเท่าไหร่ รวมจาก ช่อง qty_in ของชีท stock_movement คะ ซึ่งจะต้องกำหนดให้ช่องนี้คำนวณจากรหัสอ้างอิงสินค้าตัวเดียวกันนะคะ

แหะๆ พอจะเข้าใจไหมอ่า คือ อยากคำนวณผลรวมโดยอ้างอิงจากรหัสสินค้าตัวเดียวกัน เพื่อให้ในการตัดสต็อกนะคะ

ใช้ Sumifs ได้ครับ

ตัวอย่างสูตรที่ชีท STOCK_BAL เซลล์ E1 คีย์

=SUMIFS(Table2[QTY_IN],Table2[PART NO.],[@[PART NO.]],Table2[SERIAL NO.],[@[SERIAL NO.]])

Enter

คำว่า "อ่า", "อ่ะ" หรืออื่น ๆ ที่เป็นภาษาแชทนั้นผิดกฎนะครับ อ่านกฎการใช้บอร์ดทุกข้อด้านบนประกอบด้วยครับ

เข้าใจแล้วคะ ขอบคุณมากนะคะอาจารย์

สำหรับข้อ 1 และ ข้อ 2 นะคะ
ขั้นตอนแรกเมื่อมีการเข้า-ออกของสินค้า เราจะทำการบันทึกลงใน ชีท stock_movement คะ ถ้าสินค้าเข้าเราจะใช้คีย์ว่า IN แต่ถ้า สินค้าออกเราจะใช้คีย์ว่า OUT ซึ่งจะใช้ฐานข้อมูลหลักจากชีท part นะคะ ดังนั้นเวลามีของเข้ามาใหม่เราก็จะกรอกข้อมูลด้วยตัวเองคะ แต่อยากจะให้ลิ้งข้อมูลไปอัพเดทที่ชีท part ด้วยนะคะ และจากชีทนี้ก็ต้องการให้เชื่อมไปยังชีท stock_bal เพื่อใช้ในการตัดสต็อกต่อไปด้วยคะ

ทำตัวอย่างมาด้วยครับ

ตัดข้อมูลมาเพียงเพื่อเป็นตัวอย่าง ชี้ให้เห็นว่าข้อมูลต้นทางเป็นอย่างไร จะได้เข้าใจตรงกันว่าต้องการให้ Update ข้อมูลใดบ้าง หลัง Update แล้วมีลักษณะเป็นอย่างไร

ขั้นตอนแรก : สมมติว่าในฐานข้อมูลสินค้าคงคลังเราว่างเปล่าเลยนะคะ เมื่อมีการรับสินค้าเข้ามาเราจึงต้องป้อนข้อมูลลงใน stock_movement อันนี้ก่อนมามีสินค้าอะไรเข้ามาบ้าง โดยกรอกข้อมูลรหัสสินค้า รายละเอียดต่าง ๆ พร้อมกับจำนวนสินค้าที่เข้ามาคะ

ขั้นตอนที่สอง : อ้างอิงจากข้อมูลของเข้าจากชีท stock_movement นะคะ โดยเราต้องการให้ระบบสามารถอัพเดทข้อมูลสินค้าที่เข้ามาอัตโนมัติคะในชีท part คะ เช่น เริ่มต้นสินค้าที่เข้ามาตัวแรกอ้างอิงจากชีท stock_movement ในขั้นตอนแรก คือ Part no. 65-62822-7 , Serial no. D01314 รายละเอียดสินค้าคือ stall warning comp นะคะ เราต้องการให้ข้อมูลเหล่านี้อัพเดทไปที่ ชีท part ด้วยคะ โดยต้องการเฉพาะข้อมูลหลัก 3 ตัวนี้นะคะ ส่วนรายละเอียดอื่น ๆ ของสินค้าเราจะอัพเดทเองคะ

ขั้นตอนที่สาม : จากที่แจ้งไปในตอนแรกว่าในชีท stock_movement ของเราไม่ได้อัพเดทเฉพาะข้อมูลสินค้าเข้าเท่านั้น แต่จะมีการอัพเดทข้อมูลสินค้าออกด้วย รวมถึงในบางครั้งจะมีการนำเข้าสินค้าชนิดเดิมที่เคยนำเข้ามาแล้วด้วย ดังนั้นในชีท part จึงต้องการให้ดึงมาเฉพาะข้อมูลสินค้าเข้า และต้องไม่มีข้อมูลสินค้าที่ซ้ำกันคะ โดยข้อมูลสินค้าที่จะใช้เป็นคีย์หลักว่าไม่ซ้ำกันจะดูจาก Part no. และ serial no. ในชีท stock_movement คะ

ขั้นตอนที่สี่ : จากการที่เราสามารถดึงเฉพาะข้อมูลสินค้านำเข้าที่ไม่ซ้ำกันจากชีท stock_movement มาอัพเดทไว้ในชีท part เพื่อเป็นฐานข้อมูลของเราได้แล้ว เราจำเป็นต้องดึงข้อมูลการอัพเดทสินค้าใหม่นี้เข้าไปไว้ในชีท stock_bal ด้วยคะ โดยข้อมูลที่ต้องการให้ดึงจะเหมือนกับที่ดึงจาก stock_movement มาชีท part นะคะ เพื่อที่เราจะได้มีฐานข้อมูลของ สินค้าในคงคลังตรงกันทั้ง 3 ชีทคะ

แนบไฟล์ตัวอย่างประกอบแต่ละข้อตามอธิบายมาด้วยจะได้เขียนสูตรต่อไปจากนั้นครับ

คำว่าตัวอย่างผมหมายถึงต้องประกอบด้วยไฟล์ต้วอย่างเสมอ ไม่ใช่เฉพาะคำอธิบายครับ

รบกวนอาจารย์ดูจากรายละเอียดจากเอกสารแนบนะคะ ไม่แน่ใจว่าพอจะอธิบายได้ชัดเจนไหมนะคะ
ขอบคุณคะ

ต้องขออภัยที่เขียนแจ้งเรื่องไฟล์ตัวอย่างไปไม่ชัดเจน

คำว่าไฟล์ตัวอย่าง ให้แนบเป็นไฟล์ Excel และต้องเป็นเช่นนี้เสมอสำหรับการถามตอบในฟอรัมในหมวด Excel นี้ครับ

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

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

แบบนี้พอจะได้ไหมคะอาจารย์

ทำตามนี้ครับ

1. ชีท Part
   1. เซลล์ A2 คีย์สูตร
      =IFERROR(INDEX(Table1[PART NO.],AGGREGATE(15,6,ISNA(MATCH(Table1[PART NO.],A$1:A1,0))*(ROW(Table1[PART NO.])-MIN(ROW(Table1[PART NO.]))+1),ROWS(A$2:A2))),"")
      Enter > Copy ไป C2 แล้ว Copy ลงด้านล่าง
   2. ที่ D2 ลองเขียนสูตรหายอดคงเหลือมาเองก่อน ติดแล้วค่อยถามกันต่อ สามารถประยุกต์ใช้สูตร Sumifs มาช่วยได้ครับ
2. ชีท Stock_bal
   1. เซลล์ B2 คีย์สูตร
      =IFERROR(INDEX(Table1[PART NO.],AGGREGATE(15,6,ISNA(MATCH(Table1[PART NO.],B$1:B1,0))*(ROW(Table1[PART NO.])-MIN(ROW(Table1[PART NO.]))+1),ROWS(B$2:B2))),"")
      Enter > Copy ไปถึง D2 แล้ว Copy ลงด้านล่าง
3. ชีท stock_movement ทำการระบายสีบรรทัดที่ซ้ำ
   1. คลุม A2:F8
   2. เข้าเมนู Home > Conditional formatting > New Rule
   3. ตรง Select Rule Type: เลือก User a formula to determine which cells to format
   4. ตรงช่องว่างใต้บรรทัด Format values where this formula is true: คีย์สูตร
      =COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2,$D$2:$D2,$D2)>1
   5. คลิกปุ่ม Format แล้วกำหนดค่าตามต้องการ > OK > OK

อาจารย์คะ หลังจากใส่สูตรลงไปแล้ว ทดลองเพิ่มข้อมูลใน ชีท stock_movement ดู ปรากฎว่ารายการสินค้าใหม่ไม่ได้ถูกเพิ่มเข้าในชีท part และ stock_bal ด้วยนะคะ แต่ปรากฎว่าเป็นสูตร error แทนนะคะ

ชีท Part D2 ปรับสูตรเป็นด้านล่างครับ

=SUMIFS(Table5[REMAIN],Table5[PART NO.],[@[PART NO.]],Table5[SERIAL NO.],[@[SERIAL NO.]])

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

เมื่อได้ขยาย Table แล้วค่อย Copy สูตรเดิมลงมาใหม่ครับ

อาจารย์คะพอลากสูตรลงมาแล้วข้อมูลสินค้าที่ได้จะซ้ำกับสินค้าเก่านะคะ

ปรับสูตรใหม่เป็นด้านล่างครับ

ชีท STOCK_BAL

1. เซลล์ B2 คีย์สูตร
   =IFERROR(INDEX(Table2[PART NO.]&"",SMALL(IF(FREQUENCY(MATCH("~"&Table2[PART NO.],Table2[PART NO.]&"",0),ROW(Table2[PART NO.])-MIN(ROW(Table2[PART NO.]))+1),ROW(Table2[PART NO.])-MIN(ROW(Table2[PART NO.]))+1),ROWS(B$2:B2))),"")
   Ctrl+Shift+Enter > Copy ลงด้านล่าง
2. เซลล์ C2 คีย์สูตร
   =IFERROR(INDEX(Table2[SERIAL NO.]&"",SMALL(IF(FREQUENCY(MATCH("~"&Table2[PART NO.],Table2[PART NO.]&"",0),ROW(Table2[PART NO.])-MIN(ROW(Table2[PART NO.]))+1),ROW(Table2[PART NO.])-MIN(ROW(Table2[PART NO.]))+1),ROWS(C$2:C2))),"")
   Ctrl+Shift+Enter > Copy ลงด้านล่าง
3. เซลล์ D2 คีย์สูตร
   =IFERROR(INDEX(Table2[DESCRIPTION]&"",SMALL(IF(FREQUENCY(MATCH("~"&Table2[PART NO.],Table2[PART NO.]&"",0),ROW(Table2[PART NO.])-MIN(ROW(Table2[PART NO.]))+1),ROW(Table2[PART NO.])-MIN(ROW(Table2[PART NO.]))+1),ROWS(D$2:D2))),"")
   Ctrl+Shift+Enter > Copy ลงด้านล่าง

ชีท Part

1. เซลล์ A2 คีย์สูตร
   =IFERROR(INDEX(Table2[PART NO.]&"",SMALL(IF(FREQUENCY(MATCH("~"&Table2[PART NO.],Table2[PART NO.]&"",0),ROW(Table2[PART NO.])-MIN(ROW(Table2[PART NO.]))+1),ROW(Table2[PART NO.])-MIN(ROW(Table2[PART NO.]))+1),ROWS(A$2:A2))),"")
   Ctrl+Shift+Enter > Copy ลงด้านล่าง
2. เซลล์ B2 คีย์สูตร
   =IFERROR(INDEX(Table2[PART NO.]&"",SMALL(IF(FREQUENCY(MATCH("~"&Table2[PART NO.],Table2[PART NO.]&"",0),ROW(Table2[PART NO.])-MIN(ROW(Table2[PART NO.]))+1),ROW(Table2[PART NO.])-MIN(ROW(Table2[PART NO.]))+1),ROWS(A$2:A2))),"")
   Ctrl+Shift+Enter > Copy ลงด้านล่าง
3. เซลล์ C2 คีย์สูตร
   =IFERROR(INDEX(Table2[DESCRIPTION]&"",SMALL(IF(FREQUENCY(MATCH("~"&Table2[PART NO.],Table2[PART NO.]&"",0),ROW(Table2[PART NO.])-MIN(ROW(Table2[PART NO.]))+1),ROW(Table2[PART NO.])-MIN(ROW(Table2[PART NO.]))+1),ROWS(C$2:C2))),"")
   Ctrl+Shift+Enter > Copy ลงด้านล่าง

!

Note: Ctrl+Shift+Enter หมายถึง กรณีคีย์สูตรเอง เมื่อคีย์สูตรแล้ว แทนที่จะกด Enter ให้กดแป้น Ctrl+Shift ค้างไว้แล้วกด Enter เพื่อสร้างเป็นสูตร Array กรณี Copy สูตรไปวางให้กดแป้น F2 เพื่อทำการ Edit Cell นั้นก่อน จากนั้นกดแป้น Ctrl+Shift ค้างไว้แล้วกด Enter เพื่อสร้างเป็นสูตร Array หากกดแป้นถูกต้องสูตรนั้นจะมีเครื่องหมายปีกกาครอบ เช่น {=YourFormulas(...)} ปีกกานี้คีย์เข้าไปเองไม่ได้ ถ้ายังไม่เห็นปีกกาครอบสูตรแสดงว่ากดแป้นให้รับสูตรไม่ถูกต้อง การแก้ไขเปลี่ยนแปลงสูตร Array จะต้องกดแป้นให้รับสูตรด้วย Ctrl+Shift+Enter ทุกครั้ง

ได้แล้วคะ
ขอบคุณมากคะอาจารย์ ยังไงรบกวนอาจารย์อธิบายสูตรให้ฟังหน่อยได้ไหมคะ คือหนูดูแล้วยังไม่ค่อยเข้าใจนะคะ คือ
สูตร iferror เป็นตัวคลุมใหญ่เพื่อให้แสดงค่าที่ไม่ซ้ำกับตัวก่อนใช่ไหมคะ
สูตร index นี้ใช้ในการอ้างอิงเซลล์ที่เราต้องการ
สูตร SMALL(IF(FREQUENCY(MATCH >>> อันนี้คือไม่เข้าใจจริง ๆ คะว่าแนวคิดในการใช้เป็นอย่างไร คือใช้ match ในการอ้างอิงข้อมูลที่เราใช้เป็นคีย์ในการดูใช่ไหมคะ แล้วเวลาใช้รวมกันนี้ต้องเป็นเงื่อนไขแบบไหนหรอคะ

ลองศึกษาจากกระทู้นี้ครับ

viewtopic.php?t=2875#p19137
viewtopic.php?t=7871#p49598

สูตรพวกนี้ยากมาก ต้องเข้าใจ Array เป็นอย่างดีมาก่อนครับ