Conditional Format

[kmb]

จากไฟล์ที่แนบมา ผมใส่ Conditional format ว่าถ้าค่าในคอลัมน์ B มากกว่า 10 ให้ cell เป็นสีเหลือง ก็ทำได้ไม่มีปัญหาครับ
แต่ต้องการใส่ขอบเขตไว้สำหรับในกรณีที่มีจำนวนบรรทัดมากขึ้น โดยเปลียนจาก applied to : =$A$1:$B$12 เป็น
=OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1)) โดยตรง หรือใช้วิธี define name แล้วก็ใส่สูตร OFFSET ไปที่ NAME แทน แล้วแทนค่า
มันก็ทำงานนะครับ แต่มันก็เปลี่ยนค่าจาก OFFSET หรือ NAME นั้น ๆ กลับไปเป็น =$A$1:$B$12 ทุกครั้ง

ไม่ทราบว่าพอจะมีวิธีทำให้สามารถเปลี่ยนขอบเขตลักษณะนี้ได้ไหมครับ

[snasui]

ดูแล้วน่าจะเป็นความฉลาดของโปรแกรมครับ แม้ว่าจะให้ชื่อไว้กับช่วงข้อมูลแล้วนำมาใช้ในช่อง Applied to แต่โปรแกรมก็แปลงกลับมาเป็นช่วงข้อมูล แต่การกำหนด Conditional Formatting นั้นสามารถที่จะเพิ่มลดตามตามปริมาณข้อมูลที่เราคีย์เพิ่มได้ ไม่ใช่ว่าโปรแกรมแปลงกลับมาเป็นช่วงข้อมูลแล้วจะจำกัด Conditional Formatting อยู่เพียงแต่ช่วงเซลล์นั้น

[kmb]

แต่พอผมลองคีย์ข้อมูลเพิ่ม range มันไม่เปลี่ยนตามน่ะครับ
format จะตามมาด้วยในกรณีที่ copy จาก range เดิมเท่านั้น
หรือไม่ก็ต้อง copy คำสั่ง OFFSET หรือ NAME ไปใ่ส่ในช่อง applied to ใหม่ มันถึงจะเปลี่ยนเป็น range ใหม่ให้

[snasui]

ดูภาพการทดสอบสด ๆ จากไฟล์ที่คุณ kmb แนบมาครับ

[kmb]

ถ้าเป็นการเพิ่มทีละบรรทัดดังในรูป ไม่มีปัญหาครับ
แต่ตอนที่ใช้งาน ผมทำจาก 2 วิธีครับ คือ
1. ใช้ Autofill
2. ใช้ copy --> paste
ซึ่งจะเป็นการใส่ข้อมูลมากกว่า 1 บรรทัดต่อครั้ง
Range มันจะคลุมเพิ่มแค่บรรทัดแรกบรรทัดเดียวเท่านั้นน่ะครับ
ขอโทษทีครับ ทำเป็นภาพให้เห็นอย่างข้างบนไม่เป็น

[snasui]

การ Copy แล้ววางให้วางแบบ Special ครับคือ

หลังจาก Copy เซลล์ต้นทาง > คลิกขวาที่เซลล์เป้าหมาย > Paste Special > Value

หากวางตรง ๆ จะทำให้ Conditional Formatting ที่กำหนดไว้ถูกทับด้วย Format ของเซลล์ที่วางครับ

[kmb]

การ Copy แล้ววางให้วางแบบ Special ครับคือ

หลังจาก Copy เซลล์ต้นทาง > คลิกขวาที่เซลล์เป้าหมาย > Paste Special > Value

หากวางตรง ๆ จะทำให้ Conditional Formatting ที่กำหนดไว้ถูกทับด้วย Format ของเซลล์ที่วางครับ

มันเป็นแบบนี้น่ะครับ เวลา paste ถึงแม้ว่าจะ paste value ก็ตาม

cf.gif

[snasui]

ไม่เป็นไรครับ เราไปปรับช่วงข้อมูลเผื่อไว้ก่อนครับ เช่นจากเดิม Applied to: =$A$1:$B$12 ให้เราแก้เป็น =$A$1:$B$1200 ครับ

[kmb]

ไม่เป็นไรครับ เราไปปรับช่วงข้อมูลเผื่อไว้ก่อนครับ เช่นจากเดิม Applied to: =$A$1:$B$12 ให้เราแก้เป็น =$A$1:$B$1200 ครับ

ขอบคุณครับ จริง ๆ แล้วถ้ามีข้อมูลใน Column A & B ทั้งหมดเพื่อจะตรวจสอบตามเงื่อนไขเหมือนกันหมด
ก็คงใส่เป็น Applied to: =$A:$B ไปเลยน่ะครับ
แต่ที่จะพยายามเขียนสูตรให้เปลี่ยน range ได้เผื่อในกรณีที่ worksheet นั้น มีการแบ่งข้อมูลออกเป็นช่วง ๆ สำหรับกรณีที่ต่างกันน่ะครับ
จะลองหาวิธีประยุกต์ดูครับ

[kmb]

ถ้าในกรณีที่มีหลาย worksheet แล้วต้องการจะ copy conditional format จาก sheet นึง ไปยัง sheet อื่น ๆ สามารถทำได้ไหมครับ

[snasui]

หากกำหนด Conditional Format ด้วยการดูค่าในเซลล์สามารถ Copy Format ไปได้ครับ แม้ว่าจะข้ามไฟล์กันก็ตาม แต่การจะให้แสดงผลเหมือนกัน สภาพแวดล้อมของข้อมูลที่นำ Format ไปวางก็ต้องมีลักษณะเดียวกันครับ

หากกำหนด Condition Format เป็นสูตรก็คงต้องดูเป็นกรณี ๆ ไปครับ

[kmb]

ตามตัวอย่างไฟล์ที่แนบมา ถ้าต้องการ copy conditional format จาก sheet 1 ไปยัง sheet 2 มีวิธีการไหมครับ

[snasui]

ลองตามนี้ครับ

1. ที่ Sheet1 คลุม B2:O34 > คลิกขวา > Copy
2. ไปที่ Sheet2 คลิกขวาเซลล์ B2 > Paste Special > Format

[kmb]

ถ้าใช้วิธีเลือกที่ Conditional Formatting > Manage Rules ...
เลือก New Rule ... แล้วเปลี่ยนค่าในสูตร กับช่อง Applied to โดยให้อ้างอิงค่าด้วยการใส่ชื่อ Worksheet นั้น ๆ ได้ไหมครับ
แต่ผมลองแล้ว ทำไม่ได้ ไม่รู้ว่าเป็นเพราะทำไม่ถูกหรือเปล่า
ตอนนี้ถ้าจะทำได้ต้องไปเลือก Worksheet นั้น ๆ ก่อนแล้วค่อยเลือก New Rule ... เพื่อเปลี่ยนค่าในช่อง Applied to ทีหลัง
เหตุผลที่ไม่ใช้วิธี Paste Special > Format เพราะว่าแต่ละ Worksheet มีการ Format อื่น ๆ ซึ่งจะไม่เหมือนกัน เช่นการตีเส้น เป็นเส้นเดี่ยว เส้นคู่ หรือตัวหนังสือเป็นตัวหนาในบางบรรทัด ประมาณนั้นน่ะครับ

[snasui]

ในการเขียนสูตร Conditional Formatting เราจะอ้างอย่างไรก็ได้ครับ แต่ในช่อง Applies to ถูกบังคับให้อ้าง Sheet ปัจจุบันเท่านั้น ดังนั้น การเขียน Conditional Formatting เพื่อไป Applies to กับชีทอื่นจึงไม่น่าจะทำได้ด้วยวิธีนี้

แต่เราสามารถอ้างอิงเงื่อนไขจากชีทอื่นเพื่อมาใช้กับชีทปัจจุบันได้ครับ ดูตัวอย่างการเขียนสูตรตามไฟล์แนบครับ

[kmb]

ขอบคุณครับ ถ้างั้นในกรณีผม คงต้องทำไปทีละ worksheet

[snasui]

โดยลักษณะการทำงานของ Conditional Formatting จะกำหนดให้ทำที่ชีทใครชีทมันครับ ถ้ามีข้อจำกัดว่าไม่สามารถจะ Copy Format ได้ ก็จำเป็นต้องทำทีละชีทครับ

[kmb]

ไม่ทราบว่าสามารถประยุกต์โดยใช้ define name แทนการใส่สูตรโดยระบุชื่อ worksheet ได้ไหมครับ

[snasui]

สามารถทำได้ครับ