Snasui Excel Forum

ตัวอย่าง ข้อมูลที่คีย์ อยู่ในช่วง a1:a10 format cell แบบข้อความ ป้องกันคีย์ข้อมูลผิด validation แบบรายการ
รายการที่อ้างอิงอยู่ในช่วง c1:c10 format cell แบบข้อความ คือ 001 ถึง 010
ทีนี้ พอคีย์ข้อมูลในช่วง a1:a10 คีย์ผิดมีเครื่องหมาย * ติดเข้าไปด้วย เช่น *10, 0**, **1, ***
ปรากฏว่าคีย์ผ่าน ไม่ติดล็อค ไม่ทราบว่าพอมีวิธีไหนแก้ไขได้บ้างครับ ไม่เอาวิธีที่สร้างรายการอ้างอิง
ในเมนู validaion นะครับ เพราะรายการเยอะ

สามารถใช้ Validation แบบกำหนดสูตรเข้าไปช่วยได้ครับ

ยกตัวอย่างเช่น A1:A10 คือเซลล์ที่ต้องการจะคีย์โดยไม่ต้องการให้มีเครื่องหมายดอกจันเข้าไปด้วย

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

1. ลากเมาส์คลุม A1:A10
2. เข้าเมนู Data > Validation
3. ช่อง Allow เลือก Custom
4. ช่อง Formula คีย์สูตร =1-ISNUMBER(FIND("*",A1)) แล้วคลิกปุ่ม OK
5. ทดสอบคีย์ค่าใน A1:A10 ตามต้องการ สังเกตดูผล

ลองดูแล้วครับ คุณ คนควน มันล็อคได้เฉพาะเครื่องหมาย * ส่วนเครื่องหมายอื่น เช่น + , - , / มันไม่ล็อค แล้วผมต้องการให้คีย์
ได้เฉพาะข้อมูลที่อยู่ในรายการ c1:c10 คือ 001 002 003 004 005 006 007 008 009 010 เท่านัั้น เราสามารถเลือก validation ได้ 2 choice คือ แบบ list และ custom ในช่วงข้อมูลเดียวกันได้มั้ย ครับ

ส่งไฟล์มาน่าจะง่ายกว่าครับ

อีกอย่างสูตรที่อาจารย์แนะนำไปนั้น เราาสามารถเพิ่มเงื่อนไขเข้าไปได้เองเลย ดังนี้

ลากเมาส์คลุม A1:A10
เข้าเมนู Data > Validation
ช่อง Allow เลือก Custom
ช่อง Formula คีย์สูตร =1-ISNUMBER(FIND("*",A1))&1-ISNUMBER(FIND("+",A1))&1-ISNUMBER(FIND("-",A1))&1-ISNUMBER(FIND("/",A1))
แล้วคลิกปุ่ม OK
ทดสอบคีย์ค่าใน A1:A10 ตามต้องการ สังเกตดูผล

a1:a10 คือช่วงข้อมูลทึ่ต้องการคีย์ format cell แบบข้อความ และป้องกันการคีย์
ข้อมูลผิดแบบ รายการ ช่วงรายการทีเลือก คือ =$c$1:$c$10

c1:c10 คือ ช่วงรายการข้อมูล 001 002 ...ถึง 010 ทึ่จะกำหนดให้คีย์ได้ใน a1:a10 format cell แบบข้อความ เช่นกัน

คือจะคีย์อะไรได้ ใน a1:a10 คีย์ได้เฉพาะ 001 002 003 004 005 006 007 008 009 010 เท่านััน ปัญหาตอนนี้คือเครื่องหมายอะไรมันก๋็ล็อคได้หมด +, - , / แต่เครื่องหมาย * กลับคีย์ผ่านแต่ต้องขึ้นต้นด้วย * หรือ 0 เท่านั้น เช่น **1, 01* ,00*, 0*6 ถ้าขึ้นต้นด้วยตัวอื่นเช่น 1*0 , 2**, *11 ก็คีย์ไม่ผ่านเข้าใจว่าเครื่องหมาย * แทนสัญลักษณ์ข้อความ แต่จะให้เปลี่ยน format cell เป็นแแบบอื่นก็ไม่ได้ ไม่ทราบว่ามีวิธีไหนที่แก้ปัญหาได้บ้างครับ

ผมไม่ทราบว่าคุฯเข้าใจที่ผมอธิบายไปไหม และทดลองสูตรที่ผมให้ไปหรือยัง

a1:a10 คือช่วงข้อมูลทึ่ต้องการคีย์ format cell แบบข้อความ และป้องกันการคีย์
ข้อมูลผิดแบบ รายการ ช่วงรายการทีเลือก คือ =$c$1:$c$10

ให้เปลี่ยนเป็นตั้งช่วงชื่อ โดยไปที่ Fomulas > RangeName คลิ๊กสร้างชื่อใหม่ โดยให้ชื่อว่า ID ในแถบสูตรใส่ =Sheet1!$C$1:$C$10 คลิ๊ก ตกลง

คลิ๊กที่เซลล์ A1 ไปที่ DataValidation ให้เลือกแสดงเป็นรายการ ในแถบสูตรคีย์ =ID แล้วกดตกลง จากนั้นคัดลอกรูปแบบเซลล์จาก A1 ลงมาถึง A10
ทดสอบคีย์รหัสที่ไม่ถูกต้อง เช่น 111 โปรแกรมจะฟ้อง

คือจะคีย์อะไรได้ ใน a1:a10 คีย์ได้เฉพาะ 001 002 003 004 005 006 007 008 009 010 เท่านััน ปัญหาตอนนี้คือเครื่องหมายอะไรมันก๋็ล็อคได้หมด +, - , / แต่เครื่องหมาย * กลับคีย์ผ่านแต่ต้องขึ้นต้นด้วย * หรือ 0 เท่านั้น เช่น **1, 01* ,00*, 0*6 ถ้าขึ้นต้นด้วยตัวอื่นเช่น 1*0 , 2**, *11 ก็คีย์ไม่ผ่านเข้าใจว่าเครื่องหมาย * แทนสัญลักษณ์ข้อความ แต่จะให้เปลี่ยน format cell เป็นแแบบอื่นก็ไม่ได้ ไม่ทราบว่ามีวิธีไหนที่แก้ปัญหาได้บ้างครับ

การบังคับไม่ให้คีย์พวกเครื่องหมายเหล่านี้ลงไปด้วย (+)(-)(*)(/) ให้คุณคลุมเซลล์ B1:B10 แล้วก็เข้าไปที่ เข้าเมนู Data > Validationท ช่อง Allow เลือก Custom
ช่อง Formula คีย์สูตร =1-ISNUMBER(FIND("*",B1))&1-ISNUMBER(FIND("+",B1))&1-ISNUMBER(FIND("-",B1))&1-ISNUMBER(FIND("/",B1))
แล้วคลิกปุ่ม OK
ทดสอบคีย์ค่าใน B1:B10 ตามต้องการ สังเกตดูผล

คุณ bank 9597 เข้าใจผิดหรือเปล่าครับ ผมไม่ได้อ้างถึง b1:b10 คือต้องการล็อคข้อมูลที่จะคีย์เข้า a1:a10 เท่านั้น
คือ เครื่องหมาย * และ คีย์ได้เฉพาะ 001 002 ...ถึง 010 ที่อ้างอิงไว้ในช่วง c1:c10

ตอนนี้สร้าง validation ในช่วง a1:a10 เลือกแบบรายการ แหล่งข้อมูล $c$1:$c$10 หรือจะให้เปลี่ยนแบบกำหนดชื่อช่วงก็ได้
แต่ติดข้อสงสัย เราสามารถกำหนด validation 2 เงื่อนไข คือ แบบรายการ และ custom ในช่วง a1:a10 ได้หรือไม่
ขอบคุณครับ

คืออย่างนี่ครับ ในไฟล์แนบ

คุณ bank 9597 เข้าใจผิดหรือเปล่าครับ ผมไม่ได้อ้างถึง b1:b10 คือต้องการล็อคข้อมูลที่จะคีย์เข้า a1:a10 เท่านั้น
คือ เครื่องหมาย * และ คีย์ได้เฉพาะ 001 002 ...ถึง 010 ที่อ้างอิงไว้ในช่วง c1:c10

ที่ B1:B10 ผมทำไว้เป็นตัวอย่าง โดยกำหนดแบบ Custom ซึ่งป้องการคีย์เครื่องหมายต่างๆที่่เราไม่พึงประสงค์ เช่น + - * / นั่นคือในส่วนของ B1:B10

ส่วน A1:A10 นั้นผมได้เปลี่ยนจากการอ้างอิงค่าจาก $C$1:$C$10 มาเป็นการตั้งช่วงชื่อว่า ID ซึ่งเราจะสามารถล๊อคการคีย์ข้อมูลโดยการเลือกการรายการที่เรากำหนดไว้ใน $C$1:$C$10 อย่างเดียว หากคีย์ข้อมูลอื่นลงไปแล้วไม่ถูกต้อง ระบบก็จะแจ้งเตือนทันที

ตอนนี้สร้าง validation ในช่วง a1:a10 เลือกแบบรายการ แหล่งข้อมูล $c$1:$c$10 หรือจะให้เปลี่ยนแบบกำหนดชื่อช่วงก็ได้
แต่ติดข้อสงสัย เราสามารถกำหนด validation 2 เงื่อนไข คือ แบบรายการ และ custom ในช่วง a1:a10 ได้หรือไม่
ขอบคุณครับ

ส่วนการกำหนด 2 เงื่อนในที่เดียวกันนั้น เห็นทีจะไม่ได้ครับ ผมเลยแยกออกเป็น 2 กรณี กรณีที่1 คือ A1:A10 กรณีที่2 คือ B1:B10 ครับ แล้วแต่ว่าจะเลือกแบบไหนครับ

กรณีไม่ต้องการมีอักขระอื่นใดนอกเหนือจาก ตัวเลขและต้องมี 3 หลัก สามารถเปลี่ยนสูตรใน Validation ที่ผมให้ไปเป็น

=And(Isnumber(A1+0),Len(A1)=3)

ส่วนที่ถามว่า

Chaval wrote: เราสามารถเลือก validation ได้ 2 choice คือ แบบ list และ custom ในช่วงข้อมูลเดียวกันได้มั้ย ครับ

ใน Validation หนึ่ง ๆ เราสามารถเลือกได้อย่างใดอย่างหนึ่งเท่านั้นครับ

ขอบคุณครับ คุณ คนควน

ผมลองจากสูตรที่ให้แล้ว มันล็อคไว้ให้คีย์ได้เฉพาะ 000 ถึง 999
แต่ถ้าข้อมูลไม่ถึง 999 มันจะมีวิธีให้ล็อคได้มั้ยครับ เพราะทำ 2 เงื่อนไขไม่ได้

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

ผมสมมุติต่อว่ามีมากที่สุดแค่ 010 สามารถปรับสูตรที่ผมให้ไปได้เป็นด้านล่างครับ

=And(Isnumber(A1+0),Len(A1)=3,A1+0<=10)

ขอบพระคุณครับ คุณคนควน

เทพจริงๆ โอกาสหน้าถ้าติดปัญหาอะไรขอรบกวนอีกนะครับ