Excel Forum

สวัสดีครับ

สืบเนื่องจากที่ทางผมได้สอบถามสูตรการดึงข้อมูลและการเลือกข้อมูลไว้ก่อนหน้านี้

เพื่อที่จะนำมาศึกษา และปรับใช้งาน แต่สุดท้ายก็ยังไม่สำเร็จ T_T อาจจะเป็นเพราะยังไม่เข้าใจการทำงานของ Function ที่ดีพอ

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

เงื่อนไข

1. ให้ทำการตรวจสอบ Series no. ที่ Column A ใน Sheet Data ว่า Series no. ไหนมีผลเป็น FAIL ที่ Column G เกิดขึ้นถึง 3 ครั้งหรือไม่
ถ้ามีให้นำข้อมูล Item (ที่ Column B ถึง Column F) FAIL ครั้งที่ 3 ของ Series no นั้นมาแสดงที่ Sheet Result ตามลำดับ

2. โดยที่ข้อมูล Item และ Value (ที่ Column B ถึง Column F) ที่นำมาแสดงนั้น ให้นำมาแสดงเฉพาะหัวข้อ Item และ Value ที่ไม่ผ่าน Limit
โดยกำหนดค่า Limit Max ไว้ที่ Row 3 และ Limit Min ไว้ที่ Row 4

ขอคำแนะนำด้วยครับ

...

A2

=IFERROR(INDEX(Data!$A$5:$A$27,AGGREGATE(15,6,ROW(Data!$A$5:$A$27)-ROW(Data!$A$5)+1/(MATCH(ROW(Data!$A$1:$A$27)/((Data!$B$5:$F$27<Data!$B$4:$F$4)+(Data!$B$5:$F$27>Data!$B$3:$F$3)),AGGREGATE(15,6,LOOKUP(ROW(Data!$A$5:$A$27)/((ROW(Data!$A$5:$A$27)/(COUNTIFS(Data!$A$5:$A$27,Data!$A$5:$A$27,Data!$G$5:$G$27,"FAIL")>=3/(Data!$G$5:$G$27="FAIL"))-ROW(Data!$A$5)+1)=MATCH(Data!$A$5:$A$27,Data!$A$5:$A$27,0))-ROW(Data!$A$5)+1,MATCH(Data!$A$5:$A$27,Data!$A$5:$A$27,0)/(Data!$G$5:$G$27="FAIL"),ROW(Data!$A$5:$A$27)-ROW(Data!$A$5)+1),ROW(Data!$A$1:$A$27)),0)>0),ROWS($A$2:A2))),"")

Ctrl + Shift+ Enter

B2

=IFERROR(INDEX(Data!$B$2:$F$2,AGGREGATE(15,6,INDEX(COLUMN(Data!$B$5:$F$5)/((Data!$B$5:$F$27<Data!$B$4:$F$4)+(Data!$B$5:$F$27>Data!$B$3:$F$3))-COLUMN(Data!$B$5)+1,AGGREGATE(15,6,ROW(Data!$A$5:$A$27)/(COUNTIFS(Data!$A$5:$A$27,Data!$A$5:$A$27,Data!$G$5:$G$27,"FAIL")>=3)/($A2=Data!$A$5:$A$27),3)-ROW(Data!$A$5)+1,0),COUNTIF($A$2:A2,A2))),"")

C2

=IFERROR(INDEX(Data!$B$5:$F$27,AGGREGATE(15,6,ROW(Data!$A$5:$A$27)/(COUNTIFS(Data!$A$5:$A$27,Data!$A$5:$A$27,Data!$G$5:$G$27,"FAIL")>=3)/($A2=Data!$A$5:$A$27),3)-ROW(Data!$A$5)+1,AGGREGATE(15,6,INDEX(COLUMN(Data!$B$5:$F$5)/((Data!$B$5:$F$27<Data!$B$4:$F$4)+(Data!$B$5:$F$27>Data!$B$3:$F$3))-COLUMN(Data!$B$5)+1,AGGREGATE(15,6,ROW(Data!$A$5:$A$27)/(COUNTIFS(Data!$A$5:$A$27,Data!$A$5:$A$27,Data!$G$5:$G$27,"FAIL")>=3)/($A2=Data!$A$5:$A$27),3)-ROW(Data!$A$5)+1,0),COUNTIF($A$2:A2,A2))),"")

Norkaz

...

อีกแบบใช้คอลัมน์ไปช่วย

ชีท Data

Helper-1
H5

=IF((COUNTIFS($A$5:A5,A5,$G$5:G5,"FAIL")*(G5="FAIL"))=3,ROWS($H$5:H5),"")

Helper-2

I5:Mxx
=IFERROR(IF($H5="","",AGGREGATE(15,6,$H5/(($B5:$F5<$B$4:$F$4)+($B5:$F5>$B$3:$F$3)),COLUMNS($I5:I5))),"")

Helper-3

N5:Rxx
=IF($H5="","",IFERROR(AGGREGATE(15,6,COLUMN($B5:$F5)/(($B5:$F5<$B$4:$F$4)+($B5:$F5>$B$3:$F$3)),COLUMNS($N5:N5))-COLUMN($B5)+1,""))


ชีท Result
A2
=IFERROR(INDEX(Data!$A$5:$A$27,AGGREGATE(15,6,Data!$I$5:$M$27,ROWS(A$2:A2))),"")


B2

=IF(A2="","",INDEX(Data!$B$2:$F$2,INDEX(Data!$N$5:$R$27,AGGREGATE(15,6,Data!$I$5:$M$27,ROWS(B$2:B2)),COUNTIF($A$2:A2,A2))))


C2

=IF(A2="","",INDEX(Data!$B$5:$F$27,AGGREGATE(15,6,Data!$I$5:$M$27,ROWS(C$2:C2)),INDEX(Data!$N$5:$R$27,AGGREGATE(15,6,Data!$I$5:$M$27,ROWS(D$2:D2)),COUNTIF($A$2:A2,A2))))


Norkaz

A2
=IFERROR(INDEX(Data!$A$1:$A$27,AGGREGATE(15,6,ROW(Data!$B$5:$F$27)/((Data!$B$5:$F$27>Data!$B$3:$F$3)+(Data!$B$5:$F$27<Data!$B$4:$F$4)),ROWS(A$2:A2))),"")

B2
=IFERROR(INDEX(Data!$A$2:$G$2,MOD(AGGREGATE(15,6,ROW(Data!$B$5:$F$27)*10^6+COLUMN(Data!$B$2:$F$2)/((Data!$B$5:$F$27>Data!$B$3:$F$3)+(Data!$B$5:$F$27<Data!$B$4:$F$4)),ROWS(A$2:A2)),10^6)),"")

C2
=IFERROR(INDEX(Data!$B$1:$F$27,AGGREGATE(15,6,ROW(Data!$B$5:$F$27)/((Data!$B$5:$F$27>Data!$B$3:$F$3)+(Data!$B$5:$F$27<Data!$B$4:$F$4)),ROWS(A$2:A2)),MATCH(B2,Data!$B$2:$F$2,)),"")

norkaz wrote: Tue Oct 26, 2021 9:58 pm ...

อีกแบบใช้คอลัมน์ไปช่วย

ชีท Data

Helper-1
H5

=IF((COUNTIFS($A$5:A5,A5,$G$5:G5,"FAIL")*(G5="FAIL"))=3,ROWS($H$5:H5),"")

Helper-2

I5:Mxx
=IFERROR(IF($H5="","",AGGREGATE(15,6,$H5/(($B5:$F5<$B$4:$F$4)+($B5:$F5>$B$3:$F$3)),COLUMNS($I5:I5))),"")

Helper-3

N5:Rxx
=IF($H5="","",IFERROR(AGGREGATE(15,6,COLUMN($B5:$F5)/(($B5:$F5<$B$4:$F$4)+($B5:$F5>$B$3:$F$3)),COLUMNS($N5:N5))-COLUMN($B5)+1,""))


ชีท Result
A2
=IFERROR(INDEX(Data!$A$5:$A$27,AGGREGATE(15,6,Data!$I$5:$M$27,ROWS(A$2:A2))),"")


B2

=IF(A2="","",INDEX(Data!$B$2:$F$2,INDEX(Data!$N$5:$R$27,AGGREGATE(15,6,Data!$I$5:$M$27,ROWS(B$2:B2)),COUNTIF($A$2:A2,A2))))


C2

=IF(A2="","",INDEX(Data!$B$5:$F$27,AGGREGATE(15,6,Data!$I$5:$M$27,ROWS(C$2:C2)),INDEX(Data!$N$5:$R$27,AGGREGATE(15,6,Data!$I$5:$M$27,ROWS(D$2:D2)),COUNTIF($A$2:A2,A2))))


Norkaz

ขอขอบคุณคุณ Norkaz มากเลยครับ ที่แนะนำ มาให้ 2 แบบ เด๋วจะนำไปศึกษาและปรับใช้งานครับ

Bo_ry wrote: Wed Oct 27, 2021 12:21 am A2
=IFERROR(INDEX(Data!$A$1:$A$27,AGGREGATE(15,6,ROW(Data!$B$5:$F$27)/((Data!$B$5:$F$27>Data!$B$3:$F$3)+(Data!$B$5:$F$27<Data!$B$4:$F$4)),ROWS(A$2:A2))),"")

B2
=IFERROR(INDEX(Data!$A$2:$G$2,MOD(AGGREGATE(15,6,ROW(Data!$B$5:$F$27)*10^6+COLUMN(Data!$B$2:$F$2)/((Data!$B$5:$F$27>Data!$B$3:$F$3)+(Data!$B$5:$F$27<Data!$B$4:$F$4)),ROWS(A$2:A2)),10^6)),"")

C2
=IFERROR(INDEX(Data!$B$1:$F$27,AGGREGATE(15,6,ROW(Data!$B$5:$F$27)/((Data!$B$5:$F$27>Data!$B$3:$F$3)+(Data!$B$5:$F$27<Data!$B$4:$F$4)),ROWS(A$2:A2)),MATCH(B2,Data!$B$2:$F$2,)),"")

ขอขอบคุณ คุณ Bo_ry มากครับที่แนะนำ เด๋วจะนำไปศึกษาและปรับใช้งานครับ

non2_hab wrote: Wed Oct 27, 2021 7:56 am ขอขอบคุณ คุณ Bo_ry มากครับที่แนะนำ เด๋วจะนำไปศึกษาและปรับใช้งานครับ

non2_hab wrote: Wed Oct 27, 2021 7:55 am ขอขอบคุณคุณ Norkaz มากเลยครับ ที่แนะนำ มาให้ 2 แบบ เด๋วจะนำไปศึกษาและปรับใช้งานครับ

คำว่า "เด๋ว" ผิดกฎการใช้บอร์ดข้อ 1 ด้านบน กรุณาระมัดระวังด้วยครับ

snasui wrote: Wed Oct 27, 2021 12:51 pm

non2_hab wrote: Wed Oct 27, 2021 7:56 am ขอขอบคุณ คุณ Bo_ry มากครับที่แนะนำ เด๋วจะนำไปศึกษาและปรับใช้งานครับ

non2_hab wrote: Wed Oct 27, 2021 7:55 am ขอขอบคุณคุณ Norkaz มากเลยครับ ที่แนะนำ มาให้ 2 แบบ เด๋วจะนำไปศึกษาและปรับใช้งานครับ

คำว่า "เด๋ว" ผิดกฎการใช้บอร์ดข้อ 1 ด้านบน กรุณาระมัดระวังด้วยครับ

ขอโทษด้วยครับ ครั้งถัดไปจะดูกฎให้รอบคอบครับ