Snasui Excel Forum

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

ข้อมูลใน Sheet 4 จะมีเพิ่มขึ้นมาเรื่อยๆได้อีกไม่แน่นอนในแต่ละเดือน
จึงต้องการทราบว่าในรายการ และ รหัส ใน Sheet 4 มีรายการ และ รหัส ไหนเพิ่มขึ้นมา
โดยข้อมูลใน Sheet4 ไม่มีข้อมูลอยู่ใน Sheet1 , Sheet2 , Sheee3 อะไรบ้าง
จากตัวอย่างมี 2 รายการที่ไม่มีในSheet1 , Sheet2 , Sheee3
ให้แสดงรายการ และ รหัส ไว้ใน H3 , I3 , H4 , I4 เป็นต้น
ขอบคุณครับ

ตัวอย่างสูตรครับ

เซลล์ H2 คีย์

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$3:$A$33)/(1-MMULT(--(COUNTIF(INDIRECT("'Sheet"&{1,2,3}&"'!A2:A100"),$A$3:$A$33)>0),{1;1;1})),ROWS(H$4:H4))),"")

Enter > Copy ไป I2 และ Copy ลงด้านล่าง

ผลลัพธ์ใน H3 = ABCDFFG , I3 = 123456 ถูกต้องครับ
ผลลัพธ์ใน H4 = qqq , I4 = 21 ไม่ถูกต้องครับ
ที่ถูกต้องคือ H4 = ZZZZZZZ , I4 = 999999 แถว2 ยังไม่ได้ครับ

qqq ไม่มีใน Sheet1, Sheet2 และ Sheet3 ครับ

ผมขออธิบายเพิ่มเติมครับ
ใน Sheet1 มีประเภทสินค้า A1 และ ประเภทสินค้า B1
Sheet2 มีประเภทสินค้า C1 และ ประเภทสินค้า D1
Sheet3 มีประเภทสินค้า E1 และ ประเภทสินค้า F1
มีรายการ และ รหัส ใน column A , B , E , F ทั้ง 3 Sheets ครอบคลุมไปตั้งแต่ column A ถึง F
ซึ่งรายการ Sheets1,2,3 จะมีอยู่ทั้งหมดใน Sheet4 ประเภทสินค้าทุกชนิด รวมอยู่ใน column A , B
เป็นแถวเดียวกัน แต่ใน Sheet4 เป็นข้อมูลจะมีรายการ รหัส เพิ่มขึ้นมาเพราะม่ีการอัพเดทข้อมูลครับ

ตัวอย่างการปรับสูตรครับ

H3 คีย์

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$3:$A$33)/(0=MMULT(--(COUNTIF(INDIRECT("'Sheet"&{1,2,3}&"'!A2:F100"),$A$3:$A$33)>0),{1;1;1})),ROWS(H$3:H3))),"")

Enter > Copy ไป I3 > Copy ลงด้านล่าง

ขอบคุณครับ ได้ตามที่ต้องการครับ

เรียน อาจารย์
สูตรที่ให้มาที่ INDEX(A:A จะสามารถกำหนดช่วงที่ต้องการได้หรือไม่ครับ เพราะลองกำหนดช่วงที่ต้องการ เช่น INDEX(A2:A100 จะทำให้ผลลัพธ์ไม่ตรงเคลื่อนไป ไม้ได้ตาม INDEX(A:A ครับ หรือว่ากับช่วงที่ต้องการไม่ได้ครับ ขอบคุณครับ

กำหนดได้ครับ เช่น

=IFERROR(INDEX(A$3:A$33,AGGREGATE(15,6,(ROW($A$3:$A$33)-Row($A$3)+1)/(0=MMULT(--(COUNTIF(INDIRECT("'Sheet"&{1,2,3}&"'!A2:F100"),$A$3:$A$33)>0),{1;1;1})),ROWS(H$3:H3))),"")

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

ขอบคุณครับ