Snasui Excel Forum

-Column A,B,C จะวางข้อมูลใหม่โดยลบข้อมูลเก่าออกก่อน ส่วนมากข้อมูลจะอยู่ที่ประมาณ 4800-5500 แถว
และ Column E ไม่เกิน 200 แถว

โดยสูตรที่ทำงาน

มี 3 Column (G,H,I) ที่ต้องใช้สูตรนี้ 4800 row (แล้วแต่ข้อมูล)

=IFERROR(1/(1/INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$5077)/(COUNTIF($E$2:$E$148,$A$2:$A$5077)=0),ROWS(G$3:G3)))),"")

และอีก 3 (L,M,N) Column ที่ต้องใช้สูตรนี้ ประมาณ row (แล้วแต่ข้อมูล)

=IFERROR(1/(1/INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$5077)/(COUNTIF($E$2:$E$148,$A$2:$A$5077)=1),ROWS(L$3:L3)))),"")

อยากทราบว่ามีวิธีไหนทำให้สูตรคำนวณเร็วขึ้นไหมครับ

ไฟล์ใหญ่เกินแนบครับ

ลองแนบไฟล์ Excel มาอีกรอบ ตัดมาเฉพาะที่พอเป็นต้วอย่างได้เพื่อที่ไฟล์จะไม่ใหญ่เกินไปครับ

snasui wrote: Tue May 03, 2022 9:40 pm ลองแนบไฟล์ Excel มาอีกรอบ ตัดมาเฉพาะที่พอเป็นต้วอย่างได้เพื่อที่ไฟล์จะไม่ใหญ่เกินไปครับ

แนลไฟล์แล้วครับ อ.

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

1. ที่ F3 คีย์
   =AGGREGATE(15,6,ROW($A$2:$A$5077)/(ISNA(MATCH($A$2:$A$5077,$E$2:$E$148,0))),ROWS(G$3:G3))
   Enter > Copy ลงด้านล่าง
2. ที่ G3 คีย์
   =IF(ISNUMBER($F3),INDEX(A:A,$F3),"")
   Enter > Copy ลงด้านล่าง
3. ที่ H3 คีย์
   =IF(ISNUMBER($F3),INDEX(B:B,$F3),"")
   Enter > Copy ลงด้านล่าง
4. ที่ I3 คีย์
   =IF(ISNUMBER($F3),INDEX(C:C,$F3),"")
   Enter > Copy ลงด้านล่าง
5. ที่ K3 คีย์
   =AGGREGATE(15,6,ROW($A$2:$A$5077)/(ISNUMBER(MATCH($A$2:$A$5077,$E$2:$E$148,0))),ROWS(L$3:L3))
   Enter > Copy ลงด้านล่าง
6. ที่ L3 คีย์
   =IF(ISNUMBER($K3),INDEX(A:A,$K3),"")
   Enter > Copy ลงด้านล่าง
7. ที่ M3 คีย์
   =IF(ISNUMBER($K3),INDEX(B:B,$K3),"")
   Enter > Copy ลงด้านล่าง
8. ที่ N3 คีย์
   =IF(ISNUMBER($K3),INDEX(C:C,$K3),"")
   Enter > Copy ลงด้านล่าง

snasui wrote: Wed May 04, 2022 8:10 pm ลองใช้คอลัมน์ช่วยตามตัวอย่างสูตรตามล่างครับ

1. ที่ F3 คีย์
   =AGGREGATE(15,6,ROW($A$2:$A$5077)/(ISNA(MATCH($A$2:$A$5077,$E$2:$E$148,0))),ROWS(G$3:G3))
   Enter > Copy ลงด้านล่าง
2. ที่ G3 คีย์
   =IF(ISNUMBER($F3),INDEX(A:A,$F3),"")
   Enter > Copy ลงด้านล่าง
3. ที่ H3 คีย์
   =IF(ISNUMBER($F3),INDEX(B:B,$F3),"")
   Enter > Copy ลงด้านล่าง
4. ที่ I3 คีย์
   =IF(ISNUMBER($F3),INDEX(C:C,$F3),"")
   Enter > Copy ลงด้านล่าง
5. ที่ K3 คีย์
   =AGGREGATE(15,6,ROW($A$2:$A$5077)/(ISNUMBER(MATCH($A$2:$A$5077,$E$2:$E$148,0))),ROWS(L$3:L3))
   Enter > Copy ลงด้านล่าง
6. ที่ L3 คีย์
   =IF(ISNUMBER($K3),INDEX(A:A,$K3),"")
   Enter > Copy ลงด้านล่าง
7. ที่ M3 คีย์
   =IF(ISNUMBER($K3),INDEX(B:B,$K3),"")
   Enter > Copy ลงด้านล่าง
8. ที่ N3 คีย์
   =IF(ISNUMBER($K3),INDEX(C:C,$K3),"")
   Enter > Copy ลงด้านล่าง

สอบถามเพิ่มเติมครับ ข้อไหนผิดรบกวน อ. อธิบายเพิ่มเติมครับ

1.---------------------------------------------------------------------------------------------------------------------------------
=AGGREGATE(15,6,ROW($A$2:$A$5077)/(ISNA(MATCH($A$2:$A$5077,$E$2:$E$148,0))),ROWS(G$3:G3))
Aggregate + small คือ เรียงเอาแถวที่มีค่าน้อยที่สุดขึ้นใช่ไหมครับ
Row / match ก็คือเช็คว่า ถ้า match ให้แสดงเลขแถว

***ISNA ตัวนี้มีไว้ทำไมครับ พยายามลองดูแล้วก็ไม่เข้าใจ คือ เอาค่าที่ไม่ match รึเปล่าครับ***

2.-----------------------------------------------------------------------------------------------------------------------------------
=AGGREGATE(15,6,ROW($A$2:$A$5077)/(ISNUMBER(MATCH($A$2:$A$5077,$E$2:$E$148,0))),ROWS(L$3:L3))

***ISNUMBER*** ล้อมาจากข้อที่ 1 ถ้า ISNA คือเอาค่าที่หาไม่เจอ ทำไมต้องมี ISNUMBER ทั้งๆที่ ROW / Match =มันไม่ match อยู่แล้วครับ

3.-----------------------------------------------------------------------------------------------------------------------------------
=IF(ISNUMBER($F3),INDEX(A:A,$F3),"")
เช็คว่าเป็นตัวเลขไหมถ้าใช่ให้เอาเอาแถวที่ (เลข) F3

รบกวนผู้รู้ด้วยครับ ขอบคุณครับ ^_^

Xcelvba wrote: Thu May 05, 2022 9:20 am ISNA ตัวนี้มีไว้ทำไมครับ พยายามลองดูแล้วก็ไม่เข้าใจ คือ เอาค่าที่ไม่ match รึเปล่าครับ

ใช่ครับ

Match ให้ผลลัพธ์เป็นตัวเลขลำดับที่หาก Match เจอค่านั้น ๆ ถ้าหาก Match ไม่เจอจะแสดงค่า #N/A

Isna(Match(..)) เป็นการตรวจสอบว่าผลลัพธ์จาก Match เป็นค่า #N/A ใช่หรือไม่ หากใช่จะแสดง True (มีค่าเท่ากับ 1) หากไม่ใช่จะแสดง False (มีค่าเท่ากับ 0)

ดังนั้น ROW($A$2:$A$5077)/(ISNUMBER(MATCH($A$2:$A$5077,$E$2:$E$148,0)) จะกลายเป็นเช่น ROW($A$2:$A$5077)/{True;True;True;False,...} ผลลัพธ์จึงแสดงเฉพาะค่าบรรทัดที่หารด้วย True เท่านั้น (เฉพาะผลลัพธ์ของ Match ที่เป็น #N/A)

ส่วน Isnumber(Match(...)) เป็นการตรวจสอบว่าผลลัพธ์เป็นตัวเลขใช่หรือไม่ หากใช่แสดงค่า True หากไม่ใช่แสดงค่า False นำความเข้าใจนี้ไปเทียบกับย่อหน้าแรกข้างบน จะเข้าใจมากขึ้นครับ

แม้ว่า Match จะให้ค่าลำดับที่เป็นตัวเลขอยู่แล้ว แต่เราต้องการค่าที่เป็น True หรือ False ไปเป็นตัวหารค่าบรรทัดเพื่อให้ผลลัพธ์ที่ได้ยังเป็นค่าบรรทัด ไม่ใช่ตัวเลขที่ได้จากการ Match เพราะจะผันแปรไปตามลำดับที่พบ หากนำไปใช้ตรง ๆ คำตอบที่ได้จะไม่ใช่ค่าบรรทัด

ส่วนอื่น ๆ ที่พยายามอธิบายมานั้นถือว่าเข้าใจอยู่พอสมควรแล้วครับ

snasui wrote: Thu May 05, 2022 12:25 pm

Xcelvba wrote: Thu May 05, 2022 9:20 am ISNA ตัวนี้มีไว้ทำไมครับ พยายามลองดูแล้วก็ไม่เข้าใจ คือ เอาค่าที่ไม่ match รึเปล่าครับ

ใช่ครับ

Match ให้ผลลัพธ์เป็นตัวเลขลำดับที่หาก Match เจอค่านั้น ๆ ถ้าหาก Match ไม่เจอจะแสดงค่า #N/A

Isna(Match(..)) เป็นการตรวจสอบว่าผลลัพธ์จาก Match เป็นค่า #N/A ใช่หรือไม่ หากใช่จะแสดง True (มีค่าเท่ากับ 1) หากไม่ใช่จะแสดง False (มีค่าเท่ากับ 0)

ดังนั้น ROW($A$2:$A$5077)/(ISNUMBER(MATCH($A$2:$A$5077,$E$2:$E$148,0)) จะกลายเป็นเช่น ROW($A$2:$A$5077)/{True;True;True;False,...} ผลลัพธ์จึงแสดงเฉพาะค่าบรรทัดที่หารด้วย True เท่านั้น (เฉพาะผลลัพธ์ของ Match ที่เป็น #N/A)

ส่วน Isnumber(Match(...)) เป็นการตรวจสอบว่าผลลัพธ์เป็นตัวเลขใช่หรือไม่ หากใช่แสดงค่า True หากไม่ใช่แสดงค่า False นำความเข้าใจนี้ไปเทียบกับย่อหน้าแรกข้างบน จะเข้าใจมากขึ้นครับ

แม้ว่า Match จะให้ค่าลำดับที่เป็นตัวเลขอยู่แล้ว แต่เราต้องการค่าที่เป็น True หรือ False ไปเป็นตัวหารค่าบรรทัดเพื่อให้ผลลัพธ์ที่ได้ยังเป็นค่าบรรทัด ไม่ใช่ตัวเลขที่ได้จากการ Match เพราะจะผันแปรไปตามลำดับที่พบ หากนำไปใช้ตรง ๆ คำตอบที่ได้จะไม่ใช่ค่าบรรทัด

ส่วนอื่น ๆ ที่พยายามอธิบายมานั้นถือว่าเข้าใจอยู่พอสมควรแล้วครับ

ขอบคุณมากๆครับ