Excel Forum

ขอสอบถามความรู้ครับ

ผมกำลังทำไฟล์สินค้าคงเหลือ ณ ปัจจุบันอยู่ครับ
ซึ่งจะมีชีท Brought Forward, Received และ Issued โดยแต่ละชีทหลักๆจะมีรหัสสินค้า และโลเคชั่นของสินค้าแต่ละรายการอยู่ครับ

ปัญหาของผมครับ
1. ก็คือว่าในชีท Issued ในช่อง M ผมอยากให้มันรวมผลลัพธ์ของรายการสินค้าที่เหมือนกับเท่านั้นครับ เพราะตอนนี้มันไปหักลบยอดของสินค้าโลเคชั่นอื่นด้วยครับ อยากได้เฉพาะโลเคชั่นเดียวกันครับ (ตามรายการในไฟล์แนบ "SINH" โลเคชั่น "Reserve" มียอดคงเหลือ 260 หักออก 10 จะเหลือ 250 และบรรทัดต่อไปรหัสสินค้าเดียวกันก็จริง แต่เป็นคนละโลเคชั่นให้ข้ามไป และบรรทัดต่อไป รหัสสินค้าเดียวกันโลเคชั่นเดียวกัน หักออก 10 ยอดคงเหลือจะต้องเหลือ 240 แต่ตอนนี้มันผลลัพธ์มันคือ 235 มันสูตรมันไปลบรายการก่อนหน้าด้วยครับ)

2. ในชีท Issued ในช่อง L จะเป็นโลเคชั่นของสินค้าแต่ละชนิด คือ พอจะมีวิธีการหรือสูตรอย่างไรหรือไม่? อยากให้มันโชว์เฉพาะโลเคชั่นของรายการสินค้านั้นๆ ที่มียอดคงเหลือ เพราะตอนนี้มันโชว์โลเคชั่นทั้งหมดเลย อยากให้โชว์เฉพาะโลเคชั่นที่มีสินค้า (ตามโลเคชั่นของ SINH ในแต่ละชีท จะมีโลเคชั่นทั้งหมดแค่ 4 โลเคชั่น คือ 31Le, 31Ri, 1GRi ในชีท Brought Forward และ Reserve, 1GRi ในชีท Received เท่านั้น ก็คืออยากให้ Data Validation โชว์ข้อมูลให้กดเลือกได้แค่ 4 โลเคชั่นที่ไม่ซ้ำกันแค่นั้นครับ)

3. สรุปผมอยากได้ตาราง Pivot table ของ 3 ชีทรวมกับ (Brought Forward + Received - Issued) เพื่อที่จะได้เห็นว่า ในรายการสรุปนั้นสินค้าแต่ละรายการ อยู่โลเคชั่นไหนบ้าง? พอจะมีวิธีไหมครับ

ขอรบกวนเวลาด้วยนะครับ ขอบคุณครับ

SerIZax wrote: Wed Dec 04, 2024 10:23 pm 1. ก็คือว่าในชีท Issued ในช่อง M ผมอยากให้มันรวมผลลัพธ์ของรายการสินค้าที่เหมือนกับเท่านั้นครับ เพราะตอนนี้มันไปหักลบยอดของสินค้าโลเคชั่นอื่นด้วยครับ อยากได้เฉพาะโลเคชั่นเดียวกันครับ (ตามรายการในไฟล์แนบ "SINH" โลเคชั่น "Reserve" มียอดคงเหลือ 260 หักออก 10 จะเหลือ 250 และบรรทัดต่อไปรหัสสินค้าเดียวกันก็จริง แต่เป็นคนละโลเคชั่นให้ข้ามไป และบรรทัดต่อไป รหัสสินค้าเดียวกันโลเคชั่นเดียวกัน หักออก 10 ยอดคงเหลือจะต้องเหลือ 240 แต่ตอนนี้มันผลลัพธ์มันคือ 235 มันสูตรมันไปลบรายการก่อนหน้าด้วยครับ)

ตัวอย่างการปรับสูตรในชีต Issued เซลล์ W6 ครับ

=IF(L6="",0,SUMIFS(Received!$K:$K,Received!$E:$E,$F6,Received!$L:$L,$L6)+SUMIFS('Brought Forward'!$L:$L,'Brought Forward'!$E:$E,$F6,'Brought Forward'!$A:$A,$L6)-SUMIFS(K$6:K6,F$6:F6,F6,L$6:L6,L6))

Enter > Copy ลงด้านล่าง

SerIZax wrote: Wed Dec 04, 2024 10:23 pm 2. ในชีท Issued ในช่อง L จะเป็นโลเคชั่นของสินค้าแต่ละชนิด คือ พอจะมีวิธีการหรือสูตรอย่างไรหรือไม่? อยากให้มันโชว์เฉพาะโลเคชั่นของรายการสินค้านั้นๆ ที่มียอดคงเหลือ เพราะตอนนี้มันโชว์โลเคชั่นทั้งหมดเลย อยากให้โชว์เฉพาะโลเคชั่นที่มีสินค้า (ตามโลเคชั่นของ SINH ในแต่ละชีท จะมีโลเคชั่นทั้งหมดแค่ 4 โลเคชั่น คือ 31Le, 31Ri, 1GRi ในชีท Brought Forward และ Reserve, 1GRi ในชีท Received เท่านั้น ก็คืออยากให้ Data Validation โชว์ข้อมูลให้กดเลือกได้แค่ 4 โลเคชั่นที่ไม่ซ้ำกันแค่นั้นครับ)

ลองยกตัวอย่างมาให้เห็นว่าชีต Issued เซลล์ L6 ควรมีรายการอะไรบ้าง ดูได้จากข้อมูลใด ด้วยเงื่อนไขใด เซลล์ L7 ก็ควรยกตัวอย่างมาด้วยเช่นกันจะได้เปรียบเทียบกันได้ครับ

SerIZax wrote: Wed Dec 04, 2024 10:23 pm 3. สรุปผมอยากได้ตาราง Pivot table ของ 3 ชีทรวมกับ (Brought Forward + Received - Issued) เพื่อที่จะได้เห็นว่า ในรายการสรุปนั้นสินค้าแต่ละรายการ อยู่โลเคชั่นไหนบ้าง? พอจะมีวิธีไหมครับ

การแสดงให้เห็นลักษณะนี้ข้อมูลควรอยู่่ในชีตเดียวกัน ปกติจะเป็นรายการ Movement มี 1 คอลัมน์ที่ระบุว่าเป็นประเภทรายการแบบไหน เช่น ยกมา, รับเข้า, จ่ายออก จึงจะทำเป็นรายงานที่แสดงยอดคงเหลือได้ งานแบบนี้ปกติจะใช้ VBA เข้ามาช่วยในการบันทึกรายการลงฐานข้อมูล จากนั้นเราสามารถนำข้อมูลดังกล่าวมาทำ Report ได้โดยสะดวก

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

=IF(L6="",0,SUMIFS(Received!$K:$K,Received!$E:$E,$F6,Received!$L:$L,$L6)+SUMIFS('Brought Forward'!$L:$L,'Brought Forward'!$E:$E,$F6,'Brought Forward'!$A:$A,$L6)-SUMIFS(K$6:K6,F$6:F6,F6,L$6:L6,L6))

1. สูตรถูกต้องครับอาจารย์ ขอบคุณครับ

ลองยกตัวอย่างมาให้เห็นว่าชีต Issued เซลล์ L6 ควรมีรายการอะไรบ้าง ดูได้จากข้อมูลใด ด้วยเงื่อนไขใด เซลล์ L7 ก็ควรยกตัวอย่างมาด้วยเช่นกันจะได้เปรียบเทียบกันได้ครับ

2. ยกตัวอย่างครับ
ชีท Brought Forward หากเรา Filter รายการสินค้าในช่อง E จะพบว่า มีโลเคชั่นของสินค้านั้นๆอยู่ในคอลัมน์ A ก็คือ 31Le, 31Ri และ 1GRi
ชีท Received หากเรา Filter รายการสินค้าในช่อง E จะพบว่า มีโลเคชั่นของสินค้านั้นๆอยู่ในคอลัมน์ L ก็คือ Reserve และ 1GRi
ดังนั้น จากชีท Issued ช่อง L6 หากเราเลือกรายการสินค้าในช่อง F6 เป็น SINH ช่อง L6 อยากให้โชว์รายการ Data Validation แค่ส่วนที่มีสินค้า ซึ่งในที่นี้ก็จะเหลือแค่ 4 โลเคชั่น คือ 31Le, 31Ri, 1GRi และ Reverse ครับ

และสมมัติ F7 เป็น ASAB จะมีโลเคชั่นของสินค้าอยู่เฉพาะในชีท Brought Forward เท่านั้น คือ 32Le และ 2GRi ซึ่งใน L7 ก็อยากให้โชว์แค่ 2 โลเคชั่นเท่านั้น สำหรับที่จะเลือกใช้

สำหรับโลเคชั่นทั้งหมดที่มีคือ 1GLe, 1GRi, 11Le, 11Ri, 12Le, 12Ri, 13Le, 13Ri, 14Le, 14Ri, 2GLe, 2GRi, 21Le, 21Ri, 22Le, 22Ri, 23Le, 23Ri, 24Le, 24Ri, 3GLe, 3GRi, 31Le, 31Ri, 32Le, 32Ri, 33Le, 33Ri, 34Le, 34Ri, 4GLe, 4GRi, 41Le, 41Ri, 42Le, 42Ri, 43Le, 43Ri, 44Le, 44Ri, 5GLe, 5GRi, 51Le, 51Ri, 52Le, 52Ri, 53Le, 53Ri, 54Le, 54Ri, 6GLe, 6GRi, 61Le, 61Ri, 62Le, 62Ri, 63Le, 63Ri, 64Le, 64Ri, TGL, Reserve (ในไฟล์ผมใส่ไม่หมดนะครับ แต่ถ้าอาจารย์แนะนำสูตรแบบที่ผมต้องการได้ ผมก็จะใส่ข้อมูลโลเคชั่นทั้งหมดเลยครับ)

การแสดงให้เห็นลักษณะนี้ข้อมูลควรอยู่่ในชีตเดียวกัน ปกติจะเป็นรายการ Movement มี 1 คอลัมน์ที่ระบุว่าเป็นประเภทรายการแบบไหน เช่น ยกมา, รับเข้า, จ่ายออก จึงจะทำเป็นรายงานที่แสดงยอดคงเหลือได้ งานแบบนี้ปกติจะใช้ VBA เข้ามาช่วยในการบันทึกรายการลงฐานข้อมูล จากนั้นเราสามารถนำข้อมูลดังกล่าวมาทำ Report ได้โดยสะดวก

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

3. โอเคครับ เดี๋ยวผมจะลองใช้ VBA ดูครับ

ขอบคุณสำหรับความช่วยเหลือนะครับอาจารย์

SerIZax wrote: Tue Dec 10, 2024 7:07 pm ชีท Brought Forward หากเรา Filter รายการสินค้าในช่อง E จะพบว่า มีโลเคชั่นของสินค้านั้นๆอยู่ในคอลัมน์ A ก็คือ 31Le, 31Ri และ 1GRi
ชีท Received หากเรา Filter รายการสินค้าในช่อง E จะพบว่า มีโลเคชั่นของสินค้านั้นๆอยู่ในคอลัมน์ L ก็คือ Reserve และ 1GRi
ดังนั้น จากชีท Issued ช่อง L6 หากเราเลือกรายการสินค้าในช่อง F6 เป็น SINH ช่อง L6 อยากให้โชว์รายการ Data Validation แค่ส่วนที่มีสินค้า ซึ่งในที่นี้ก็จะเหลือแค่ 4 โลเคชั่น คือ 31Le, 31Ri, 1GRi และ Reverse ครับ

ข้อมูลที่จะนำมาหาค่า Validation จะต้องเป็น Database เรียงอยู่ในคอลัมน์ใดคอลมน์หนึ่งแล้วค่อยใช้สูตรดึงมาเฉพาะค่า Unique ตามเงื่อนไขที่เกี่ยวข้องเพือนำไปแสดงในเซลล์ใด ๆ ที่สัมพันธ์กับรายการอื่นได้สะดวก

นอจากนี้ Validation ที่กล่าวถึงจะแตกต่างกันไปในแต่ละเซลล์ งานแบบนี้ผมมองว่าควรทำด้วย VBA ครับ