Excel Forum

norkaz wrote: Tue Apr 27, 2021 5:02 pm ..

A2
=IFERROR(INDEX(data!A:A,AGGREGATE(15,6,ROW($2:$99)/(data!$B$2:$B$99="Yes")/(data!$C$2:$C$99=1),ROW(1:1))),"")


Norkaz

norkaz wrote: Wed Apr 28, 2021 6:48 pm ..

ฟังก์ชั่นหลักๆ ประกอบด้วย ROW,AGGREGATE,INDEX

ผลลัพธ์ A1 มาจาก =INDEX(data!A:A,2)
ผลลัพธ์ A4 มาจาก=INDEX(data!A:A,5)
ผลลัพธ์ A5 มาจาก=INDEX(data!A:A,6)

ถ้าหา เลข 2,5,6 ได้ ก็ได้คำตอบที่ต้องการ

=============

A2
=IFERROR(INDEX(data!A:A,AGGREGATE(15,6,ROW($2:$99)/(data!$B$2:$B$99="Yes")/(data!$C$2:$C$99=1),ROW(1:1))),"")

=============

a) =(data!$B$2:$B$99="Yes")

ผลลัพธ์ที่ได้
TRUE;FALSE;TRUE;TRUE;TRUE;…......ที่เหลือเป็น FALSE ทั้งหมด ไม่สนใจ สนใจเฉพาะ 5 รายการแรกตามโจทย์

=============

b) =(data!$C$2:$C$99=1)

ผลลัพธ์ที่ได้
TRUE;FALSE;FALSE;TRUE;TRUE;…........ที่เหลือเป็น FALSE ทั้งหมด ไม่สนใจ สนใจเฉพาะ 5 รายการแรกตามโจทย์

=============

c) (data!$B$2:$B$99="Yes")/(data!$C$2:$C$99=1)

ผลลัพธ์ที่ได้
1;#DIV/0!;#DIV/0!;1;1;….........ที่เหลือเป็น #DIV/0! ทั้งหมด ไม่สนใจ สนใจเฉพาะ 5 รายการแรกตามโจทย์

=============

d) ROW($2:$99) หมายถึง เลข 2,3,4,5,6…...........99

=============

e) ROW($2:$99)/ผลลัพธ์ที่ได้จาก c

กลายมาเป็น.......
ROW($2:$99)/(data!$B$2:$B$99="Yes")/(data!$C$2:$C$99=1)

ซึ่งคือด้านล่างนี้
(2;3;4;5;6…............99) / (1;#DIV/0!;#DIV/0!;1;1;….........)

ผลลัพธ์ที่ได้
2;#DIV/0!;#DIV/0!;5;6;.....ที่เหลือเป็น #DIV/0! ทั้งหมด ไม่สนใจ

=============

f) =AGGREGATE(15,6,…..ชุดข้อมูล........,ตามลำดับที่)

AGGREGATE(15 หมายถึงให้ไล่ค่าจากน้อยไปมาก
6 หมายถึง ไม่สนใจค่า ERROR ทุกชนิด ในชุดข้อมูล

ROW(1:1) =1
ROW(2:2) =2
ROW(3:3) =3

ไปเรื่อยๆ.......

=AGGREGATE(15,6,.... ผลลัพธ์ที่ได้จาก e,ROW(1:1))

=AGGREGATE(15,6,ROW($2:$99)/(data!$B$2:$B$99="Yes")/(data!$C$2:$C$99=1),ROW(1:1))

กลายมาเป็น.......
=AGGREGATE(15,6,2;#DIV/0!;#DIV/0!;5;6;.....ที่เหลือเป็น #DIV/0! ทั้งหมด ไม่สนใจ,ROW(1:1))

ผลลัพธ์ที่ได้ คือเลข 2

เมื่อ Copy ลงไป ROW จะวิ่งตามไป จาก 1 เป็น 2 เป็น 3..................

=AGGREGATE(15,6,2;#DIV/0!;#DIV/0!;5;6;.....ที่เหลือเป็น #DIV/0! ทั้งหมด ไม่สนใจ,ROW(2:2))

ผลลัพธ์ที่ได้ คือเลข 5

เมื่อ Copy ลงไป

=AGGREGATE(15,6,2;#DIV/0!;#DIV/0!;5;6;.....ที่เหลือเป็น #DIV/0! ทั้งหมด ไม่สนใจ,ROW(3:3))

ผลลัพธ์ที่ได้ คือเลข 6

=============

g) ใช้ INDEX ไปครอบ
=INDEX(data!A:A,AGGREGATE(15,6,ROW($2:$99)/(data!$B$2:$B$99="Yes")/(data!$C$2:$C$99=1),ROW(1:1)))

=INDEX(data!A:A,2)
=INDEX(data!A:A,5)
=INDEX(data!A:A,6)

=============

h) ใช้ IFERROR ไปครอบบังคับให้ค่า ERROR เป็นค่าว่าง

=IFERROR(INDEX(data!A:A,AGGREGATE(15,6,ROW($2:$99)/(data!$B$2:$B$99="Yes")/(data!$C$2:$C$99=1),ROW(1:1))),"")

Norkaz