การใช้สูตรเพื่อนำค่าตามเงื่อนไข จากทุกๆชีทมาแสดงอยู่ในชีทเดียว

[RS.TOP]

สวัสดีครับ อาจารย์และสมาชิกทุกท่าน,
ขอความกรุณาสอบถามและการชี้แจ้งตามความต้องการด้านล่างครับ

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

หาสูตรที่นำข้อมูลจากชีท 'MMA', 'MTA', 'Telescope PC', 'MMA Kitting', 'MTA Kitting' มาแสดงในตารางหน้าชีท 'Missing items' โดยมีเงื่อนไข

• ถ้า Part no. ไหน มี QTY เป็น "0" ให้นำค่าในคอลัมน์ A (Kit name)ในแถวนั้นๆ มาแสดงใน คอลัมน์ A (Kit name) ของชีท 'Missing items'

• จากเงื่อนไขข้อแรก ให้นำคอลัมน์ B (Part No.) มาแสดงในคอลัมน์ B (Part No.) ของชีท 'Missing items'

• จากเงื่อนไขข้อแรก ให้นำคอลัมน์ E (Description) มาแสดงในคอลัมน์ C (Description) ของชีท 'Missing items'

• จากเงื่อนไขข้อแรก ให้นำคอลัมน์ E (Description) มาแสดงในคอลัมน์ C (Description) ของชีท 'Missing items'

• จากเงื่อนไขข้อแรก ให้นำคอลัมน์ D (QTY) มาแสดงในคอลัมน์ D (QTY) ของชีท 'Missing items'

• โดยอยากให้ Part No. ที่มีค่า QTY เท่ากับ "0" ของทุกชีทมาแสดงที่ชีท 'Missing items' ทั้งหมดครับ

**ซึ่งบาง Part No. จะมีค่าที่ซ้ำกัน แต่แตกต่างกันตรง Kit name

Excel Test question.xlsx

ขอขอบพระคุณล่วงหน้าสำหรับคำตอบครับ

[snasui]

ตัวอย่างสูตรตามด้านล่างครับ

1. ที่ F2:F6 คีย์ชื่อชีตทั้งหมดที่ต้องการดึงข้อมูลมาแสดง
2. ที่ G1 คีย์
   =SUMPRODUCT(COUNTIF(INDIRECT("'"&F2:F6&"'!g2:g1000"),0))
   Enter > Copy ลงด้านล่าง
3. ที่ G2 คีย์
   =SUMPRODUCT(COUNTIF(INDIRECT("'"&F$2:F2&"'!g2:g1000"),0))-COUNTIF(INDIRECT("'"&F2&"'!g2:g1000"),0)+1
   Enter > Copy ลงด้านล่างถึง G6
4. ที่ E2 คีย์
   =IF(ROWS(E$2:E2)>$G$1,"",LOOKUP(ROWS(E$2:E2),$G$2:$G$6,$F$2:$F$6))
   Enter > Copy ลงด้านล่าง
5. ที่ A2 คีย์
   =IF(ROWS(A$2:A2)>$G$1,"",OFFSET(INDIRECT("'"&$E2&"'!A2"),AGGREGATE(15,6,(ROW(INDIRECT("'"&$E2&"'!A2:A1000"))-ROW($E$2))/(--INDIRECT("'"&$E2&"'!G2:G1000")=0),COUNTIF($E$2:$E2,$E2)),MATCH(A$1,MMA!$A$1:$G$1,0)-1))
   Enter > Copy ไปด้านขวาถึง D2 > Copy ลงด้านล่าง

[RS.TOP]

ขอบพระคุณมากครับอาจารย์
มีสูตรหลายๆสูตรเลยที่ต้องศึกษาต่อ เพราะเป็นความรู้ใหม่
เช่น =SUMPRODUCT(COUNTIF(INDIRECT

ขอรบกวนอีกนิดนึงได้มั้ยครับ เกี่ยวกับสูตรที่อาจารย์ชี้แนะครับ
ในเซลล์ G2 คือการหาค่าอะไรครับ ?? (=SUMPRODUCT(COUNTIF(INDIRECT("'"&F$2:F2&"'!g2:g1000"),0))-COUNTIF(INDIRECT("'"&F2&"'!g2:g1000"),0)+1)

[snasui]

=SUMPRODUCT(COUNTIF(INDIRECT("'"&F$2:F2&"'!g2:g1000"),0))-COUNTIF(INDIRECT("'"&F2&"'!g2:g1000"),0)+1)

เป็นการหาตำแหน่งบรรทัดเริ่มต้นของข้อมูลต้นทางแต่ละชุดหลังจากนำมาวางยังปลายทางครับ

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

[RS.TOP]

ขอขอบพระคุณมากครับ อาจารย์
พอดีผมมีอีก เงื่อนไขนึงครับ โดยจากชีทเดิม ที่นำรายการที่มี QTY เป็น 0 มาแสดง
ที่นี้ผมอยากจะให้เอาค่าที่ ต่ำกว่า 10 มาแสดง ต้องแก้สูตรตรงไหนบ้างครับ

ขอขอบพระคุณล่างหน้าครับ

[snasui]

จากคำตอบในโพสต์ #2 ปรับสูตรดังนี้ครับ

1. สูตรในข้อ 1 และ 2 เปลี่ยนส่วนประกอบใน Countif ส่วนที่เป็น 0 เปลี่ยนเป็น "<0" นั่นคือเปลี่ยนจาก

   COUNTIF(...,0)

   เป็น
   
   COUNTIF(...,"<0")
2. เปลี่ยนส่วนประกอบของสูตรในข้อ 5 ตามด้านล่าง

   .../(--INDIRECT("'"&$E2&"'!G2:G1000")=0)

   เป็น
   
   .../(--INDIRECT("'"&$E2&"'!G2:G1000")<0)

เครื่องหมาย ... หมายถึงค่าเดิมเป็นเช่นไรก็ให้ใช้ตามนั้น

[RS.TOP]

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

รบกวน อาจารย์ช่วยดูที่สูตรให้อีกทีนะครับ

ขอขอบพระคุณล่วงหน้าครับ

[snasui]

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

[RS.TOP]

ขออภัยครับ

ไฟล์แนบตามด้านล่าง หล้งจากอัพเดทสูตรครับ
*ผมใส่เลขเป็น 10 นะครับซึ่งไม่ตรงกับที่อาจารย์แนะนำไว้ ที่ให้ใส่ 0 ไม่แน่ใจว่าถูกต้องรึเปล่าครับ

Excel Test question.xlsx

[snasui]

สูตรถูกแล้วไม่ทราบว่าคำตอบตรงกับที่ต้องหรือไม่ ติดปัญหาตรงไหนกรุณาแจ้งมาด้วยครับ

[RS.TOP]

ครับอาจารย์ ผลลัพธ์ที่ได้ มันน้อยกว่าค่าที่ควรได้ครับ
สูตรแรกที่หาค่าที่เท่ากับ 0 มี 48 ผลลัพธ์
แต่พอเปลี่ยนสูตรมาเป็น หาค่าที่น้อยกว่า 10 ปรากฏว่าได้ผลลัพธ์แค่ 30 ผลลัพธ์ซึ่งน้อยกว่าผลลัพธ์ที่มีค่าเท่ากับ 0 ครับ
ซึ่งจริงๆแล้วผลลัพธ์ควรจะได้ 78 ผลลัพธ์ (มี QTY ในคอลัมน์ G ของทุกชีท <10 ซึ่งรวม 0 ด้วย)

รบกวนอาจารย์อีกทีนะครับ ให้ช่วยดูสูตรให้อีกทีครับ

[snasui]

เนื่องจากคอลัมน์ G ในแต่ละชีต เช่นชีต MMA Kitting ค่าที่เป็น 0 นั้นไม่ใช่ตัวเลข แต่เป็นตัวอักษรครับ

ตรวจสอบง่าย ๆ ด้วยการคีย์สูตรที่ G2 เป็น

=ISNUMBER(G2)

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

สำหรับค่าตัวอักษรที่เป็น 0 จะมีค่ามากกว่าตัวเลขเสมอไม่ว่าค่าตัวเลขนั้นจะมากเท่าไรก็ตาม ทดสอบง่าย ๆ ได้โดยที่เซลล์ใด ๆ คีย์

="0">100000

Enter

คำตอบจะต้องได้ True หรือหมายถึง "0" มากกว่า 100000 เป็นจริง

การแก้ไขด้วยวิธีง่าย ๆ ให้แก้ไขค่า 0 ของทุกชีตให้กลับมาเป็นเป็นตัวเลขเสียก่อนครับ