แปลงสูตรจาก FILTER -> INDEX

[Xcelvba]

ผมพยายามแปลงสูตร Filter ใน คอลัมน์ D เป็น INDEX ในคอลัมน์ I ครับ

D=FILTER(ROW(INDIRECT(A1&":"&A12)),(1-ISNUMBER(MATCH(ROW(INDIRECT(A1&":"&A12)),A1:A12,0))))

แต่ไม่ได้ ขึ้น #REF! ครับ

คอลัมน์
G ผมใช้สูตร =AGGREGATE(15,6,(ROW(INDIRECT($A$1&":"&$A$12)))/(1-ISNUMBER(MATCH(ROW(INDIRECT($A$1&":"&$A$12)),$A$1:$A$12,0))),ROWS(G$2:G2))

ผลลัพธ์ออกมาปกติครับ แต่พอผมมาเอารวมกับ INDEX ค่าไม่ออกมาเพราะเหตุใดครับ

I=INDEX(ROW(INDIRECT(A1&":"&A12)),AGGREGATE(15,6,(ROW(INDIRECT($A$1&":"&$A$12)))/(1-ISNUMBER(MATCH(ROW(INDIRECT($A$1&":"&$A$12)),$A$1:$A$12,0))),ROWS(G$2:G2)))

[snasui]

ต้องการใช้ร่วมกับ Index เพื่อหาค่าแบบไหน อย่างไร กรุณาอธิบายมาอย่างละเอียดครับ

[Xcelvba]

ต้องการหาวันที่ที่ขาดหายไปใน คอลัมน์ A ครับ ซึ่ง ผมเข้าใจว่า INDEX คือ ตาราง ARRAY ทั้งหมดที่ต้องการเลือกข้อมูลในแถวนั้นๆ ครับ
ส่วนใช้ AGGREGATE เพื่อหาแถวของ ARRAY ที่ตรงตามเงื่อนไข ผมเลยใช้ INDEX(ROW(INDIRECT... เพื่อเป็นการคุมวันที่ทั้งหมดในช่วง แล้วใช้ AGGREGATE จำตำแหน่งแถวครับ

[snasui]

INDEX คือ ตาราง ARRAY ทั้งหมดที่ต้องการเลือกข้อมูลในแถวนั้นๆ ครับ

ประเด็นนี้เข้าใจถูกแล้ว

แล้วใช้ AGGREGATE จำตำแหน่งแถวครับ

Aggregate ตามสูตรที่ใช้กับงานนี้ไม่ได้หาตำแหน่งแถว แต่เป็นการให้ผลลัพธ์ออกมาเลย จึงไม่สามารถนำมาใช้ประกอบกันได้

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

[Xcelvba]

ขอบคุณครับ อาจารย์ จะลองนำไปศึกษาเพิ่มเติมครับ

[snasui]

ใช้ AGGREGATE จำตำแหน่งแถว

กรณีใช้ Aggregate ให้แสดงผลลัพธ์เป็นตำแหน่งบรรทัดสามารถเขียนได้เป็นด้านล่างครับ

=INDEX(ROW(INDIRECT($A$1&":"&$A$12)),AGGREGATE(15,6,(ROW(INDIRECT($A$1&":"&$A$12))-MIN(ROW(INDIRECT($A$1&":"&$A$12)))+1)/(1-ISNUMBER(MATCH(ROW(INDIRECT($A$1&":"&$A$12)),$A$1:$A$12,0))),ROWS(G$2:G2)))

จะเห็นว่าสูตรมีโอกาสยาวออกไป ใช้เวลาคำนวณมากกว่าเดิม

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

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

[Xcelvba]

=INDEX(ROW(INDIRECT($A$1&":"&$A$12)),AGGREGATE(15,6,(ROW(INDIRECT($A$1&":"&$A$12))-MIN(ROW(INDIRECT($A$1&":"&$A$12)))+1)/(1-ISNUMBER(MATCH(ROW(INDIRECT($A$1&":"&$A$12)),$A$1:$A$12,0))),ROWS(G$2:G2)))

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