Snasui Excel Forum

รบกวนช่วยหาสูตรการทำ Validation ของคอล์ลัมน์ B โดยคีย์ได้ไม่เกิน 3 อักษร โดยหลักที่ 1 จะคีย์ได้เฉพาะตัวเลข หลักที่ 2 จะคีย์ได้เฉพาะตัวเลขและตัวอักษร แต่หลักที่ 3 จะต้องเป็นอักษรเท่านั้น และถ้าคีย์ 2 หลัก จะต้องเป็นตัวเลขและตัวอักษรเท่านั้น ซึ่งผมทำสูตรดังนี้
=AND(LEN(B1)<=3,ISNUMBER(LEFT(B1)+0),TEXT(RIGHT(B1)+0),B1>="000",C1<="999") แต่เมื่อคีย์ตัวเลขและตามข้อความมันก็ฟ้องข้อความเตือนไม่รู้ผมเขียนสูตรผิดหรือเปล่า ส่วนสูตรนี้ได้จากการประยุกต์จากสูตรอาจารย์คนควนครับ

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

ที่ Validation คีย์สูตร

=AND(LEN(B1)<=3,OR(AND(ISNUMBER(LEFT(B1)+0),NOT(ISNUMBER(MID(B1,2,1)+0))),AND(ISNUMBER(LEFT(B1)+0),NOT(ISNUMBER(MID(B1,2,1)+0)),NOT(ISNUMBER(RIGHT(B1)+0)))))

อาจารย์ครับเวลาว่างสูตรใน Validation มันฟ้องเตือนว่า
The Formula you typed contains an error ครับ

ผมคีย์วงเล็บปิดตกไปตัวนึงครับ แก้ไขให้แล้วตามด้านบนครับ

ขอบคุณครับอาจารย์ ทำได้แล้ว แต่ติดนิดหนึ่งครับ ตรงเมื่อเราคีย์ 1h มันจะฟ้อง ซึ่งผมไม่้ต้องการให้ฟ้อง แต่ถ้าคีย์ตัวเลขอย่างเดียวโดยไม่มีตัวอักษรให้มันฟ้อง ซึ่งก็ตรงตามที่ต้องการ ติดที่เมื่อเราคีย์ 2 หลัก กล่าวคือ หลักแรกต้องเป็นตัวเลข หลักที่สองต้องเป็นตัวอักษร (ถ้ามีสองหลัก) แต่ถ้ามี 3 หลัก 2 หลักแรกต้องเป็นตัวเลขเท่านั้น หลักที่สามต้องเป็นตัวอักษรเท่านั้น รบกวนอาจารย์อีกรอบครับ และรอบกวนอาจารย์ช่วยอธิบายสูตรดังกล่าวหน่อยครับ ขอบคุณอาจารย์มาก ๆ ครับ

เปลี่ยนสูตรใน Validation เป็นตามด้านล่างครับ

=OR(AND(LEN(B1)=2,ISNUMBER(LEFT(B1)+0),NOT(ISNUMBER(MID(B1,2,1)+0))),AND(LEN(B1)=3,ISNUMBER(LEFT(B1)+0),ISNUMBER(MID(B1,2,1)+0),NOT(ISNUMBER(RIGHT(B1)+0))))

คำอธิบายส่วนประกอบหลัก

1. จากสูตรข้างบนหมายถึง AND(LEN(B1)=2,ISNUMBER(LEFT(B1)+0),NOT(ISNUMBER(MID(B1,2,1)+0))) หรือ AND(LEN(B1)=3,ISNUMBER(LEFT(B1)+0),ISNUMBER(MID(B1,2,1)+0),NOT(ISNUMBER(RIGHT(B1)+0))) เป็นจริงหรือไม่ ถ้าเป็นจริงอย่างใดอย่างใดอย่างหนึ่งไม่ว่าชุดหน้าหรือชุดหลังก็ถือว่าเป็นจริง คือสามารถคีย์ได้
2. สำหรับชุดแรกคือ AND(LEN(B1)=2,ISNUMBER(LEFT(B1)+0),NOT(ISNUMBER(MID(B1,2,1)+0))) หมายถึง LEN(B1)=2 และ ISNUMBER(LEFT(B1)+0) และ NOT(ISNUMBER(MID(B1,2,1)+0)) เป็นจริงหรือไม่ ซึ่งต้องเป็นจริงทั้งสามเงื่อนไขถึงจะถือว่าเป็นจริง ถ้าเป็นเท็จแค่เงื่อนไขเดียวก็จะถือว่าเป็นเท็จ
3. สำหรับชุดที่สองคือ AND(LEN(B1)=3,ISNUMBER(LEFT(B1)+0),ISNUMBER(MID(B1,2,1)+0),NOT(ISNUMBER(RIGHT(B1)+0))) หมายความว่า LEN(B1)=3 และ ISNUMBER(LEFT(B1)+0) และ ISNUMBER(MID(B1,2,1)+0) และ NOT(ISNUMBER(RIGHT(B1)+0)) เป็นจริงหรือไม่ ซึ่งจะต้องเป็นจริงทั้ง 4 เงื่อนไขจึงจะถือว่าเป็นจริง ถ้าเป็นเท็จแค่เงื่อนไขเดียวจะถือว่าเป็นเท็จ

ขอบคุณอาจารย์มากครับ ได้ตรงตามที่ต้องการ ผมคิดว่าจะต้องใช้ VBA ทำซะอีก ขอบคุณอาจารย์เป็นอย่างสูง ที่แนะนำและให้ความรู้แก่กระผม

อาจารย์ครับ ผมรบกวนอีกครั้งครับ พอดีพึ่งเจอปัญหาครับ จากสูตรที่อาจารย์เคยแนะนำ
=AND(LEN(F3)=3,ISNUMBER(LEFT(F3)+0),ISNUMBER(RIGHT(F3)+0),F3>="000",F3<="999") (จริง ๆ อาจารย์แนะนำแค่การคีย์เพียง 2 หลัก แต่ผมได้ปรับเปลี่ยนเป็น 3 หลักเองครับ)
ซึ่งมันมีปัญหาคือ เมื่อเราคีย์ 1/3 หรือ 1*4 โปรแกรมมันจะไม่เตือน แต่ถ้าคีย์ *14 โปรแกรมมันจะเตือน กล่าวคือ หากคีย์หลักที่ 2 ซึ่งไม่ได้เป็นตัวเลขมันจะไม่ฟ้อง ซึ่งผมไม่รู้จะใช้สูตรใดดีในการแก้ปัญหาตรงค่าหลักที่ 2 หากใช้สูตร MID ซึ่งเป็นสูตรตัดตัวอักษรใช่หรือเปล่าครับอาจารย์ ซึ่งผมไม่รู้จะใช้ิสูตรใดดีครับ รบกวนท่านอาจารย์ช่วยด้วยครับ

แสดงว่าเงื่อนไขคือต้องเป็นตัวเลขทั้ง 3 ตัวห้ามเป็นอักขระอื่นใช่หรือไม่ครับ

ใช่ครับอาจารย์ ต้องเป็นตัวเลขทั้ง 3 หลักครับ รบกวนอาจารย์ช่วยแนะนำสูตรด้วยครับ

กรณีต้องเป็นตัวเลขทั้ง 3 ตัวสามารถเปลี่ยนสูตรเป็นด้านล่างครับ

=AND(LEN(F3)=3,F3+0>=0,F3+0<=999)

อาจารย์ครับสูตรใช้ได้ผลดีครับ แต่ติดตรงเมื่อเราคีย์ค่า .11 ค่า 1.1 และค่า 11. มันจะไม่ฟ้องเตือนครับ รบกวนอาจารย์ช่วยแนะนำอีกครั้งครับ และต้องของพระคุณอาจารย์อย่างสูงครับที่กรุณาแนะนำสูตรต่าง ๆ ทีดีแก่ผม

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

snasui wrote: แสดงว่าเงื่อนไขคือต้องเป็นตัวเลขทั้ง 3 ตัวห้ามเป็นอักขระอื่นใช่หรือไม่ครับ

wisit wrote:ใช่ครับอาจารย์ ต้องเป็นตัวเลขทั้ง 3 หลักครับ รบกวนอาจารย์ช่วยแนะนำสูตรด้วยครับ

ครับอาจารย์เดี๋ยวผมจะลองทำดูครับ ขอบคุณอาจารย์มากครับ

ต้องขออภัยคุณ wisit ครับ ผมเข้าใจคลาดเคลื่อนเอง เงื่อนไขชัดเจนแล้วครับ คือเป็นตัวเลขทั้งสามหลัก

ลองเปลี่ยนสูตรเป็นตามด้านล่างครับ

=And(len(F3)=3,Isnumber(Mid(F3,1,1)+0),Isnumber(Mid(F3,2,1)+0),Isnumber(Mid(F3,3,1)+0))

เย้ ใช้ได้ตรงตามต้องการเลยครับ ขอบพระคุณอาจารย์มากครับ สุดยอดจริง ๆ ครับ แต่ผมขอรบกวนอาจารย์ช่วยอธิบายความหมายและวิธีใช้ เพราะผมยังไม่ค่อยเข้าใจในสูตรดังกล่าวครับ
Isnumber(Mid(F3,1,1)+0) มีความหมายอย่างไร (+ 0 เป็นการเปลี่ยนจากตัวอักษรให้เป็นตัวเลขใช่หรือเปล่าครับ)
Isnumber(Mid(F3,2,1)+0) มีความหมายอย่างไร
Isnumber(Mid(F3,3,1)+0) มีความหมายอย่างไร

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

ฟังก์ชั่น Mid เป็นการตัดข้อความตามตำแหน่งต่าง ๆ โดยระบุว่าต้องการตัดมาจำนวนกี่อักขระ เช่น Mid(A1,1,2) ความหมายคือ ให้ตัดข้อความใน A1 โดยเริ่มจากอักขระที่ 1 มาจำนวน 2 อักขระ
ฟังก์ชั่น Isnumber เป็นการตรวจสอบว่าค่านั้น ๆ มีรูปแบบเป็นตัวเลขหรือไม่ หากเป็นตัวเลขจะแสดงค่า True หากไม่ใช่จะแสดงค่า False

ขอบคุณครับอาจารย์ที่อธิบายจนพอที่จะเข้าใจขึ้นมาบ้าง แต่สูตร (Mid(F3,3,1)+0) ค่า + 0 เป็นการเปลี่ยนจากตัวอักษรให้เป็นตัวเลขใช่หรือเปล่าครับ ไม่มั่นใจเลยถามเพื่อความชัวร์คร้บ

เข้าใจถูกต้องแล้วครับ

ขอบพระคุณอาจารย์มากครับ