Snasui Excel Forum

ขออนุญาต admin สอบถามการใช้งาน excel ดังนี้
1) ต้องการตัดสินข้อมูลตามเกณฑ์มาตรฐานที่กำหนดไว้ ว่าตัวอย่างที่ได้รับการวิเคราะห์ ผ่านหรือไม่ผ่าน
2) ถ้าไม่ผ่านเกณฑ์มาตรฐาน 1 พารามิเตอร์ ถือว่าตัวอย่างนั้นไม่ผ่าน
3) เมื่อกรอกตัวเลขแล้ว ข้อมูลตัวเลขที่ไม่ผ่านเกณฑ์ ให้แสดงเป็นสีแดง
4) ผลตรวจที่ไม่ผ่าน ให้ใส่แถบสี (ใช้ฟังก์ชั่น conditional Formatting ได้ไหมครับ)
5) ข้อมูลที่กรอกแล้วผมดึงจากฐานข้อมูลครับ จะอยู่ใน sheet เดียวกันทั้งหมด (ผมไม่อยากแยกข้อมูล)
6) จากข้อ 1-5 ผมต้องใช้คำสั่งหรือฟังก์ชั่นอะไรครับ ขอแนวทางการแก้ไขปัญหานี้ด้วยครับ
มีรายละเอียดตามรูปที่แนบนะครับ
ขอบคุณมากครับ

กรุณาแนบเป็นไฟล์ Excel จะได้สะดวกในการตอบของเพื่อนสมาชิกครับ

snasui wrote: Sat Sep 04, 2021 11:01 pm กรุณาแนบเป็นไฟล์ Excel จะได้สะดวกในการตอบของเพื่อนสมาชิกครับ

แนบไฟล์ให้แล้วครับ ขอบคุณมากครับ

ตัวอย่างการกำหนด Conditional Formatting โดยใช้สูตรครับ

การเข้าเมนู Conditional Formatting คือ เข้าเมนู Home > Conditional Formatting > New Rule > Use a formula for determine which cells to format > ตรง Format values where this formula is true: กำหนดสูตรให้แต่ละพื้นที่และกำหนดรูปแบบตามต้องการดังนี้

1. คลุม C12:C16 > เข้าเมนูตามด้านบน > ใช้สูตร =C12>--RIGHT(FILTER(C$3:C$8,($A$3:$A$8=$A12)*($B$3:$B$8=$B12)),2) > คลิกปุ่ม Format เพื่อกำหนดรูปแบบสี Font ตามต้องการ > OK
2. คลุม D12:E16 > เข้าเมนูตามด้านบน > ใช้สูตร =D12<=--RIGHT(FILTER(D$3:D$8,($A$3:$A$8=$A12)*($B$3:$B$8=$B12)),2) > คลิกปุ่ม Format เพื่อกำหนดรูปแบบสี Font ตามต้องการ > OK
3. คลุม F12:F16 > เข้าเมนูตามด้านบน > ใช้สูตร =OR(({-1,1,1}*($C12:$E12-RIGHT(FILTER(C$3:E$8,($A$3:$A$8=$A12)*($B$3:$B$8=$B12)),2))<0)) > คลิกปุ่ม Format เพื่อกำหนดรูปแบบสีพื้นหลังตามต้องการ > OK

ขอขอบคุณ admin มากนะครับที่ช่วยแก้ไขปัญหา ลองใช้แล้วพบปัญหาดังนี้ครับ

ข้อ 3 คลุม F12:F16 > เข้าเมนูตามด้านบน > ใช้สูตร =OR(({-1,1,1}*($C12:$E12-RIGHT(FILTER(C$3:E$8,($A$3:$A$8=$A12)*($B$3:$B$8=$B12)),2))<0)) > คลิกปุ่ม Format เพื่อกำหนดรูปแบบสีพื้นหลังตามต้องการ > OK

ขึ้นกล่องข้อความ You may not use reference operators (such as unions, intersection, and rangers), array constants, or LAMBDA function for Conditional Formatting criteria.

แต่ถ้าวางสูตรข้อ 3 ลงเซลล์แล้วลากลงจาก F12:F16 มันจะขึ้นข้อความ True กับ False ก็ใช้ได้เหมือนกันครับ เดี๋ยวไปใส่สี แทนทีข้อความเอาก็ได้ครับ แต่อยากรู้ปัญหาของกล่องข้อความที่ขึ้นมาครับผม

CF

C12:E16
=COUNTIFS(C12,SUBSTITUTE(SUBSTITUTE(INDEX(C$3:C$8,MATCH($A12&$B12,$A$3:$A$8&$B$3:$B$8,)),"ไม่เกิน",">"),"มากกว่า","<="))

F12:F16
=F12="ไม่ผ่าน"

สูตร F12:F16
=IF(SUM(--TEXT(C12:E12,"["&SUBSTITUTE(SUBSTITUTE(INDEX(C$3:E$8,MATCH($A12&$B12,$A$3:$A$8&$B$3:$B$8),),"ไม่เกิน",">"),"มากกว่า","<=")&"]0;\0")),"ไม่",)&"ผ่าน"

ัyong wrote: Sun Sep 05, 2021 10:40 pm ขึ้นกล่องข้อความ You may not use reference operators (such as unions, intersection, and rangers), array constants, or LAMBDA function for Conditional Formatting criteria.

เกิดจากใน Conditional Formatting ไม่ยอมรับค่าคงที่ Array ในสูตร (ส่วนประกอบที่เป็น {-1,1,1})

กรณีนี้เข้าใจว่าต้องการให้แสดงคำว่า "ผ่าน" หรือ "ไม่ผ่าน" ลงในเซลล์ F2 เป็นต้นไปด้วย สามารถนำสูตรนั้นมาใช้ใน F2 ได้ครับ เป็น

=IF(OR(({-1,1,1}*($C12:$E12-RIGHT(FILTER(C$3:E$8,($A$3:$A$8=$A12)*($B$3:$B$8=$B12)),2))<0)),"ไม่ผ่าน","ผ่าน")

จากนั้นกำหนดเงื่อนไขให้แสดงสีเฉพาะคำว่า "ไม่ผ่าน" แทนโดย คลุม F12:F16 > เข้าเมนู Home > Conditional Formatting > New Rule > Format cells that contain > ตรงช่อง Between (ช่องที่ 2 ของ Format my cells with:) เลือก equtl to > ช่องถัดมาคีย์คำว่า ไม่ผ่าน > คลิกปุ่ม Format เพื่อกำหนดการระบายสี

Bo_ry wrote: Mon Sep 06, 2021 12:00 am CF

C12:E16
=COUNTIFS(C12,SUBSTITUTE(SUBSTITUTE(INDEX(C$3:C$8,MATCH($A12&$B12,$A$3:$A$8&$B$3:$B$8,)),"ไม่เกิน",">"),"มากกว่า","<="))

F12:F16
=F12="ไม่ผ่าน"

สูตร F12:F16
=IF(SUM(--TEXT(C12:E12,"["&SUBSTITUTE(SUBSTITUTE(INDEX(C$3:E$8,MATCH($A12&$B12,$A$3:$A$8&$B$3:$B$8),),"ไม่เกิน",">"),"มากกว่า","<=")&"]0;\0")),"ไม่",)&"ผ่าน"

ขอบคุณมากครับสำหรับการช่วยเหลือ

snasui wrote: Mon Sep 06, 2021 6:39 am

ัyong wrote: Sun Sep 05, 2021 10:40 pm ขึ้นกล่องข้อความ You may not use reference operators (such as unions, intersection, and rangers), array constants, or LAMBDA function for Conditional Formatting criteria.

เกิดจากใน Conditional Formatting ไม่ยอมรับค่าคงที่ Array ในสูตร (ส่วนประกอบที่เป็น {-1,1,1})

กรณีนี้เข้าใจว่าต้องการให้แสดงคำว่า "ผ่าน" หรือ "ไม่ผ่าน" ลงในเซลล์ F2 เป็นต้นไปด้วย สามารถนำสูตรนั้นมาใช้ใน F2 ได้ครับ เป็น

=IF(OR(({-1,1,1}*($C12:$E12-RIGHT(FILTER(C$3:E$8,($A$3:$A$8=$A12)*($B$3:$B$8=$B12)),2))<0)),"ไม่ผ่าน","ผ่าน")

จากนั้นกำหนดเงื่อนไขให้แสดงสีเฉพาะคำว่า "ไม่ผ่าน" แทนโดย คลุม F12:F16 > เข้าเมนู Home > Conditional Formatting > New Rule > Format cells that contain > ตรงช่อง Between (ช่องที่ 2 ของ Format my cells with:) เลือก equtl to > ช่องถัดมาคีย์คำว่า ไม่ผ่าน > คลิกปุ่ม Format เพื่อกำหนดการระบายสี

ขอบคุณมากครับสำหรับการช่วยเหลือ

ลองปรับแก้ไขข้อมูลดูแล้วครับ ยังทำให้แสดง ผ่าน/ไม่ผ่านในคอลัมน์ N ไม่ได้ครับ หาผู้รู้ช่วยชี้แนะด้วยครับ ขอบคุณครับ

ช่วยอธิบายว่าไฟล์ที่แนบมานี้ได้ทำอะไรไปอย่างไรแล้วบ้าง ติดขัดปัญหาใด จะได้ตอบต่อไปจากนั้นครับ

ัyong wrote: Tue Sep 28, 2021 10:34 am ลองปรับแก้ไขข้อมูลดูแล้วครับ ยังทำให้แสดง ผ่าน/ไม่ผ่านในคอลัมน์ N ไม่ได้ครับ หาผู้รู้ช่วยชี้แนะด้วยครับ ขอบคุณครับ

เมนู Conditional Formatting > New Rule > Use a formula for determine which cells to format > ตรง Format values where this formula is true: กำหนดสูตรดังนี้

คอลัมน์ j คำสั่ง =J3>--RIGHT(FILTER(C$5:C$55,($A$5:$A$55=$H3)*($B$5:$B$55=$I3)),2)

คอลัมน์ K คำสั่ง =K3<--RIGHT(FILTER(D$5:D$55,($A$5:$A$55=$H3)*($B$5:$B$55=$I3)),2)

คอลัมน์ L คำสั่ง =L3>--RIGHT(FILTER(E$5:E$55,($A$5:$A$55=$H3)*($B$5:$B$55=$I3)),2)

คอลัมน์ M คำสั่ง =M3<--RIGHT(FILTER(F$5:F$55,($A$5:$A$55=$H3)*($B$5:$B$55=$I3)),2)

ถ้าตัวเลขแต่ละคอลัมน์ (J K L และ M) ไม่เป็นไปตามเกณฑ์ที่กำหนด (A5:F55) ให้เปลี่ยนอักษรเป็นสีชมพู

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

=AND(J3<=--RIGHT(FILTER(C$5:C$55,($A$5:$A$55=$H3)*($B$5:$B$55=$I3)),2),K3>=--RIGHT(FILTER(D$5:D$55,($A$5:$A$55=$H3)*($B$5:$B$55=$I3)),2),L3<=--RIGHT(FILTER(E$5:E$55,($A$5:$A$55=$H3)*($B$5:$B$55=$I3)),2),M3>=--RIGHT(FILTER(F$5:F$55,($A$5:$A$55=$H3)*($B$5:$B$55=$I3)),2))

CF ที่ J3:Mxx

=COUNTIFS(J3,SUBSTITUTE(SUBSTITUTE(INDEX(C$5:C$55,MATCH($H3&$I3,$A$5:$A$55&$B$5:$B$55,)),"ไม่เกิน",">"),"ไม่น้อยกว่า","<"))

N3
=IF(SUM(--TEXT(J3:M3,"["&SUBSTITUTE(SUBSTITUTE(INDEX($C$5:$F$56,MATCH(H3&I3,$A$5:$A$55&$B$5:$B$55,),),"ไม่เกิน",">"),"ไม่น้อยกว่า","<")&"]1;\0")),"ไม่",)&"ผ่าน"

snasui wrote: Tue Sep 28, 2021 6:57 pm ตัวอย่างสูตรที่ N3 ครับ

=AND(J3<=--RIGHT(FILTER(C$5:C$55,($A$5:$A$55=$H3)*($B$5:$B$55=$I3)),2),K3>=--RIGHT(FILTER(D$5:D$55,($A$5:$A$55=$H3)*($B$5:$B$55=$I3)),2),L3<=--RIGHT(FILTER(E$5:E$55,($A$5:$A$55=$H3)*($B$5:$B$55=$I3)),2),M3>=--RIGHT(FILTER(F$5:F$55,($A$5:$A$55=$H3)*($B$5:$B$55=$I3)),2))

ขอบคุณมากครับ ทำได้แล้วครับ

Bo_ry wrote: Tue Sep 28, 2021 9:11 pm CF ที่ J3:Mxx

=COUNTIFS(J3,SUBSTITUTE(SUBSTITUTE(INDEX(C$5:C$55,MATCH($H3&$I3,$A$5:$A$55&$B$5:$B$55,)),"ไม่เกิน",">"),"ไม่น้อยกว่า","<"))

N3
=IF(SUM(--TEXT(J3:M3,"["&SUBSTITUTE(SUBSTITUTE(INDEX($C$5:$F$56,MATCH(H3&I3,$A$5:$A$55&$B$5:$B$55,),),"ไม่เกิน",">"),"ไม่น้อยกว่า","<")&"]1;\0")),"ไม่",)&"ผ่าน"

ขอบคุณมากครับ ทำได้แล้วครับ