การใช้ Condition format เทียบข้อมูลข้าม Sheet

[knickx]

สวัสดีครับ มีเรื่องให้ช่วยครับ
คือผมมีข้อมูลที่ Sheet Master จะลงข้อมูลการผลิตทุกวัน และมีข้อมูลที่แสดงว่าข้อมูลว่าสินค้าผ่านการตรวจอยู่ที่ Sheet Passed และข้อมูลที่ไม่ผ่านการตรวจ ที่ Sheet Npassed ผมอยากจะเทียบเลข Serial ที่ Sheet Master กับ Serial ที่ Sheet Passed และ Sheet Npassed โดยมีเงื่อนไขว่า ถ้า Serial Sheet Master ตรงกลับ Sheet Passed ให้ใส่สี Serial เป็นสีเขียว แล้วถ้าตรงกับ Sheet Npassed ให้ Serial เป็นสีแดง
ตามตัวอย่างแนบ พอจะมีวิธีไหมครับ

[jelelite]

ที่ AH1 คีย์ passed!A1:A200
ที่ AI1 คีย์ npassed!A1:A200

และเลือก Conditional formatting -> New Rule -->use a formula to determine which cells to format

Rule 1 คีย์ =ISBLANK(C2) และเลือก Applies to ที่ขอบเขตที่ต้องการ ในไฟล์ผมใช้ $C$2:$AG$32 และเลือก Stop if true

Rule 2 คีย์ =SUMPRODUCT(--(C2=INDIRECT($AH$1)))>0 และเลือก Applies to ที่ขอบเขตที่ต้องการ (ใช้ตรวจสอบชีท passed)

Rule 3 คีย์ =SUMPRODUCT(--(C2=INDIRECT($AI$1)))>0 และเลือก Applies to ที่ขอบเขตที่ต้องการ (ใช้ตรวจสอบชีท npassed)

Example.xlsx

[DhitiBank]

หรืออีกแบบหนึ่งครับ

กรณีผ่าน
1. ที่ชีท Master เลือกเซลล์ C2 --> ไปเมนู conditional formatting --> New rule...
2. เลือก Use a formula to determine...
3. ช่อง Format values where this formula is true: ให้คีย์
=COUNTIFS(passed!$A$2:$A$1000,C2,passed!$B$2:$B$1000,C$1)>0
4. ไปตั้งค่า Format ตามชอบ (ดูรูปครับ)

condi format.png

กรณีไม่ผ่าน
ทำเหมือนกัน แต่เปลี่ยนสูตรตรงข้อ 3 เป็น
=COUNTIFS(npassed!$A$2:$A$1000,C2,npassed!$B$2:$B$1000,C$1)>0

จากนั้น ยังอยู่ที่เซลล์ C2 ไปที่ conditional formatting --> Manage rules...
จะเห็นช่อง Applies to: ของทั้งสอง conditions ที่เพิ่งสร้างขึ้นมา ให้แก้เป็น
=$C$2:$AG$32
แล้วกด Apply ครับ

[knickx]

ขอบคุณ คุณ DhitiBank และคุณjelelite มากๆครับ ที่ช่วยเป็นประโยชน์มากครับ
รบกวนสอบถามอีกอย่างถ้าผมจะให้ตรวจสอบข้ามไฟล์ถ้าจะสามารถทำได้ไหมครับ

[DhitiBank]

คุณ knickx หมายถึงว่า ชีทที่จะเป็นตัวกำหนดว่าผ่านหรือไม่ผ่านอยู่คนละไฟล์ใช่ไหมครับ เช่นในกรณีนี้คือ ชีท passed และ ชีท npassed อยู่คนละไฟล์กับชีท Master

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

condi format~01.png

หรือไม่ก็คีย์สูตรในเซลล์ใดเซลล์หนึ่งให้เสร็จก่อน แล้วคัดลอกสูตรไปวางตอนทำ conditional format ก็ได้ครับ แล้วรอดูผลว่าได้หรือไม่

หากยังไม่เข้าใจ Countifs ลองศึกษาที่ลิ้งค์ด้านล่างครับ
CountIfs-OfficeSupport

[knickx]

ใช้ครับแยกชีท passed กับ npassed เป็นคนละไฟล์ ผมได้ลองทำ ใช้สูตร
=COUNTIFS([passed.xlsx]data!$A:$A,C2,[passed.xlsx]data!$B:$B,C$1)=1

ไม่แน่ใจว่าถูกไหม พอกด ok มันขึ้นแบบนี้ครับ

[DhitiBank]

ขึ้นเตือนแบบนั้น แสดงว่าสูตรที่ใช้ใน conditional format ไม่สามารถอ้างอิงไปไฟล์อื่นแบบตรงๆ ได้ครับ

คงต้องลิงค์ค่าจากไฟล์ passed.xlsx เอามาไว้ในชีทใดชีทหนึ่งในไฟล์เดียวกับมาสเตอร์ก่อนครับแล้วค่อยใช้ conditional format โดยอ้างอิงจากค่าที่ลิงค์มาแล้วแทน

[jelelite]

ใช้ครับแยกชีท passed กับ npassed เป็นคนละไฟล์ ผมได้ลองทำ ใช้สูตร
=COUNTIFS([passed.xlsx]data!$A:$A,C2,[passed.xlsx]data!$B:$B,C$1)=1

ไม่แน่ใจว่าถูกไหม พอกด ok มันขึ้นแบบนี้ครับ

คุณknickx ลองใช้สูตร Indirect ครอบ การอ้างอิงข้ามไฟล์ดูครับ

[knickx]

ขอบคุณ คุณ DhitiBank และคุณ jelelite
ผมลองหาข้อมูลใช้ Range Name เข้ามาช่วยทำได้ครับ แต่ถ้าเปิดไฟล์เดียวสีไม่ขึ้น ต้องเปิดไฟล์พร้อมกันหมด

[DhitiBank]

ผมลองหาข้อมูลใช้ Range Name เข้ามาช่วยทำได้ครับ แต่ถ้าเปิดไฟล์เดียวสีไม่ขึ้น ต้องเปิดไฟล์พร้อมกันหมด

ขอบคุณมากครับที่มารายงานผล