Data Validation ขั้นสูง — Custom Formula, Dependent Dropdown และเทคนิคมือโปร

1. คุณรู้ Data Validation มากพอแล้ว — ถึงเวลาอัปเกรด!

ในบทความ Data Validation พื้นฐานที่แล้ว คุณได้เรียนรู้วิธีสร้าง Dropdown List, จำกัดตัวเลข, จำกัดวันที่ และตั้งข้อความเตือนกันไปแล้ว — นั่นช่วยให้ข้อมูลในไฟล์ Excel ของคุณสะอาดขึ้นเยอะเลยใช่ไหมครับ?

แต่เดี๋ยวก่อน — Data Validation ยังมีอะไรอีกมากที่ซ่อนอยู่!

เพราะชีวิตจริงการทำงานไม่ได้ง่ายแค่นั้นครับ — บางครั้งคุณต้อง:

• สร้าง Dropdown ที่เปลี่ยนตามค่าอีกเซลล์หนึ่ง (เช่น เลือกจังหวัดแล้วรายการอำเภอเปลี่ยนตาม)
• กำหนดเงื่อนไขซับซ้อน เช่น ห้ามกรอกค่าซ้ำในหลายคอลัมน์พร้อมกัน
• ให้ Dropdown ขยายตัวเองเมื่อมีรายการเพิ่มในชีทอ้างอิง
• ใช้ Named Range เพื่อจัดการ Data Validation ให้เป็นระบบ
• ป้องกันการ Copy-Paste ที่ bypass Data Validation

วันนี้เราจะเจาะลึกเทคนิค Data Validation ขั้นสูง ที่มืออาชีพใช้จริงในที่ทำงานครับ — เปิดไฟล์ตัวอย่างขึ้นมา แล้วลองทำตามไปทีละข้อเลย!

2. Custom Formula Validation — เหนือกว่ากฎสำเร็จรูป

Data Validation มี Custom Formula ที่ให้คุณเขียนสูตร Excel เป็นเงื่อนไขได้ — ยืดหยุ่นสุดๆ ครับ!

2.1 ห้ามกรอกซ้ำด้วย COUNTIF (แต่ยืดหยุ่นกว่าเดิม)

จากบทความที่แล้วคุณรู้วิธี =COUNTIF($A$2:$A$100, A2)=1 — แต่ถ้าต้องการห้ามซ้ำในหลายคอลัมน์พร้อมกันล่ะ?

ตัวอย่าง: ห้ามมีพนักงานชื่อซ้ำกันในแผนกเดียวกัน

เงื่อนไข	สูตร
ห้ามชื่อซ้ำทั้งคอลัมน์	=COUNTIF($A$2:$A$100, A2)=1
ห้ามชื่อ + แผนก ซ้ำ (สองเงื่อนไข)	=COUNTIFS($A$2:$A$100, A2, $B$2:$B$100, B2)=1
ห้ามซ้ำเฉพาะคอลัมน์ที่กรอก (ข้ามแถวว่าง)	=OR(A2=””, COUNTIF($A$2:$A$100, A2)=1)

วิธีตั้ง: เลือกช่วงที่ต้องการ > Data Validation > Allow: Custom > Formula: พิมพ์สูตรตามข้างบน

สูตร COUNTIFS ช่วยให้คุณตรวจสอบว่า “ชื่อ A + แผนก B” มีอยู่แล้วหรือยัง — ถ้ามีอยู่แล้วจะกรอกซ้ำไม่ได้ เหมาะกับกรณีที่มีน้องใหม่ชื่อซ้ำกับคนเก่าแต่อยู่คนละแผนก — แบบนั้นควรกรอกได้!

2.2 ตรวจสอบสัดส่วนหรือเปอร์เซ็นต์

สมมติว่าคุณกรอกยอดขายกับเป้าหมาย — ต้องการให้ยอดขายต้อง >= 80% ของเป้าหมาย:

สูตร: =C2 >= B2 * 0.8

• B2 = เป้าหมาย
• C2 = ยอดขายจริง

2.3 เช็ครูปแบบรหัสพนักงาน (Text Pattern)

ต้องการให้รหัสพนักงานขึ้นต้นด้วย “EMP-” ตามด้วยตัวเลข 5 หลักเสมอ:

สูตร: =AND(LEFT(A2,4)="EMP-", ISNUMBER(VALUE(MID(A2,5,5))), LEN(A2)=9)

คำอธิบาย:

• LEFT(A2,4)=”EMP-” — ต้องขึ้นต้นด้วย EMP-
• ISNUMBER(VALUE(MID(A2,5,5))) — ตัวเลข 5 หลักถัดมา
• LEN(A2)=9 — ความยาวรวม 9 ตัวอักษร

เยอะไปไหม? ไว้ใจได้ครับ — ใช้จริงในระบบ HR หลายที่!

2.4 เช็คสัดส่วนยอดขายต่องาน

คุณมีตารางยอดขายรายเดือน ต้องการให้ยอดขายแต่ละเดือนไม่เกิน 30% ของยอดรวมทั้งปี (กันการกรอกผิดที่เยอะเกินจริง):

สูตรสำหรับ C2 (เดือนมกราคม): =C2 <= SUM($C$2:$N$2) * 0.3

⚠️ข้อควรระวัง: สูตรแบบนี้ต้องระวัง Circular Reference นะครับ — ถ้าใช้ SUM($C$2:$N$2) ใน C2 เองจะเกิด Loop! ทางแก้คือให้ใช้คอลัมน์อื่นเก็บยอดรวมไว้ก่อน แล้วค่อยอ้างอิงมาที่ Data Validation

3. Dependent Dropdown — Dropdown ที่เปลี่ยนตามค่าอีกเซลล์

นี่คือฟีเจอร์ที่คนใช้ Excel หลายคนอยากได้มากที่สุดครับ — Dropdown ที่ลิสรายการเปลี่ยนตามค่าที่เลือกใน Dropdown อีกอันหนึ่ง

วิธีที่ 1: ใช้ INDIRECT (วิธีคลาสสิก)

สมมติว่าคุณทำแบบฟอร์มกรอกที่อยู่:

• เลือก จังหวัด ก่อน
• จากนั้นเลือก อำเภอ — ต้องเป็นอำเภอของจังหวัดนั้นเท่านั้น

ขั้นตอน:

1. เตรียมข้อมูล: สร้างตารางในอีกชีทหนึ่ง (หรือมุมเดียวกัน) โดยตั้งชื่อคอลัมน์ให้ตรงกับชื่อจังหวัด:

• คอลัมน์ D1 = “รายการหลัก” — ใส่ชื่อจังหวัดทั้งหมด
• ถัดไป: E1 = “กรุงเทพมหานคร”, F1 = “เชียงใหม่”, G1 = “ภูเก็ต” (ชื่อตรงกับรายการจังหวัด)
• ใต้ E1:E10 ใส่อำเภอของกรุงเทพ, ใต้ F1:F10 ใส่อำเภอของเชียงใหม่, ใต้ G1:G10 ใส่อำเภอของภูเก็ต

1. สร้าง Dropdown จังหวัด (Dropdown หลัก): Data Validation > Allow: List > Source: เลือกช่วงจังหวัด
2. สร้าง Dropdown อำเภอ (Dropdown รอง):

• Data Validation > Allow: List
• Source: =INDIRECT(B2)
• B2 = เซลล์ที่เลือกจังหวัดไว้

วิธีนี้ทำงานยังไง: INDIRECT จะแปลงข้อความใน B2 (เช่น “เชียงใหม่”) เป็นช่วงอ้างอิงที่ชื่อตรงกัน — ทำให้รายการอำเภอเปลี่ยนตามทันที!

เปิดไฟล์ตัวอย่างที่ชีท “Dependent Dropdown” — ผมเตรียมข้อมูลจังหวัด-อำเภอไว้ให้แล้ว ลองเปลี่ยนจังหวัดดูครับ รายการอำเภอจะเปลี่ยนตามทันที!

วิธีที่ 2: ใช้ Named Range (วิธีมือโปร)

ถ้าคุณไม่อยากตั้งชื่อคอลัมน์ให้ตรงกับชื่อจังหวัด (เพราะชื่อจังหวัดอาจยาวหรือมีเว้นวรรค) — ใช้ Named Range ช่วยได้ครับ:

1. เลือกช่วงอำเภอของแต่ละจังหวัด > ตั้งชื่อ (Name Box หรือ Formulas > Define Name)

• ช่วง A2:A10 (อำเภอกรุงเทพ) — ตั้งชื่อ อำเภอกรุงเทพมหานคร (ต้องไม่มีเว้นวรรคนะครับ)
• ช่วง B2:B10 (อำเภอเชียงใหม่) — ตั้งชื่อ อำเภอเชียงใหม่
• ช่วง C2:C10 (อำเภอภูเก็ต) — ตั้งชื่อ อำเภอภูเก็ต

1. ใน Dropdown อำเภอ: Source: =INDIRECT("อำเภอ" & B2)

🔦ข้อดี: จัดการข้อมูลได้เป็นระบบมากขึ้น เปลี่ยนชื่อได้ง่ายกว่า และไม่ต้องกังวลว่าชื่อจังหวัดจะมีเว้นวรรคหรืออักขระพิเศษที่ Named Range รับไม่ได้

3-Level Dependent Dropdown

อยากได้ 3 ระดับ? (จังหวัด > อำเภอ > ตำบล) ก็ทำได้ครับ — ใช้หลักการเดียวกันซ้อนกันไปเรื่อยๆ:

1. Level 1 List: ใส่รายการจังหวัด
2. Level 2 List: =INDIRECT(จังหวัดที่เลือก)
3. Level 3 List: =INDIRECT(อำเภอที่เลือก)

⚠️ข้อควรระวัง: การทำ 3 ระดับต้องมีการตั้งชื่อ Named Range ให้ครบทุก combo — ถ้ามีจังหวัด 77 จังหวัด แต่ละจังหวัดมี 20 อำเภอ ก็จะต้องตั้ง Named Range มากถึง 77 + (77×20) = 1,617 ชื่อ! — หลักการคือถ้าข้อมูลเยอะมาก ให้ใช้ Power Query หรือ VBA แทนจะดีกว่าครับ ไม่งั้นเหนื่อยตั้งชื่อแน่

4. Dynamic Dropdown ที่ขยายเองได้ — ไม่ต้องแก้ Source ทุกที

ปัญหาโลกแตกของ Data Validation คือเวลาเพิ่มรายการใหม่ใน Source — Dropdown ไม่ update ตามครับ!

ทางแก้: ใช้ Named Range แบบ Dynamic ร่วมกับ OFFSET และ COUNTA

ขั้นตอน:

1. ไปที่ Formulas > Name Manager > New
2. Name: รายการแผนก
3. Refers to: =OFFSET(Sheet2!$A$1, 0, 0, COUNTA(Sheet2!$A:$A), 1)
4. OK
5. ใน Data Validation: Allow: List > Source: =รายการแผนก

หลักการ: OFFSET จะสร้างช่วงอ้างอิงที่มีขนาดเท่ากับจำนวนข้อมูลจริงในคอลัมน์ A — COUNTA นับว่ามีกี่รายการที่กรอกไว้ ถ้าคุณเพิ่มแผนกใหม่ ช่วงจะขยายโดยอัตโนมัติ!

เปิดไฟล์ตัวอย่างที่ชีท “Dynamic Dropdown” — ลองเพิ่ม “ฝ่ายไอที” ต่อท้ายรายการดูครับ Dropdown จะมีรายการใหม่ขึ้นมาเองโดยไม่ต้องตั้งค่าอะไรเพิ่ม!

5. Input Message และ Error Alert แบบมืออาชีพ

Input Message และ Error Alert ไม่ได้มีแค่ข้อความเฉยๆ — คุณสามารถใช้มันเพื่อสร้าง UX ที่ดีให้ผู้กรอกข้อมูลได้ครับ

5.1 Input Message — สร้างคำแนะนำผู้ใช้

เวลาคุณมีฟอร์มให้คนอื่นกรอก — Input Message บอกวิธีใช้คือสิ่งสำคัญที่สุด:

ฟิลด์	Title	Input Message
รหัสพนักงาน	รูปแบบรหัสพนักงาน	กรอกรหัสตามรูปแบบ EMP-XXXXX เช่น EMP-00001
วันที่ลา	ช่วงวันที่ลา	เลือกวันที่ระหว่าง 1 ม.ค. – 31 ธ.ค. 2026
แผนก	เลือกแผนก	กรุณาเลือกแผนกจาก Dropdown เท่านั้น — ห้ามพิมพ์!

5.2 Error Alert — เลือก Style ให้เหมาะกับสถานการณ์

Error Alert มี 3 Style:

Style	ลักษณะ	ใช้ตอนไหน
Stop	ห้ามกรอกเด็ดขาด — ต้องเปลี่ยนข้อมูลเท่านั้น	ข้อมูลสำคัญที่ผิดไม่ได้ เช่น รหัสพนักงาน, เลขบัตรประชาชน
Warning	เตือนแต่ยังกรอกต่อได้	ข้อมูลที่อยากให้ระวัง เช่น ยอดขายที่สูงผิดปกติ
Information	แจ้งให้ทราบ ไม่บังคับ	ข้อมูลที่บอกว่าอาจมีผลกระทบ เช่น ส่วนลดเกิน 20%

เคล็ดลับ: ใช้ Stop สำหรับ Dropdown เสมอ — เพราะถ้าผู้ใช้พิมพ์ข้อความเองทั้งที่มี Dropdown ให้เลือก ข้อมูลจะเละแน่!

5.3 สร้าง Error Alert ที่ผู้ใช้เข้าใจ

แทนที่จะเขียนแบบขู่: “ข้อมูลผิด!” — ลองเขียนแบบช่วยเหลือ:

แบบเก่า	แบบใหม่ (มาเอง)
ข้อมูลไม่ถูกต้อง	กรุณาเลือกแผนกจาก Dropdown เท่านั้นครับ
ERROR!	รหัสพนักงานต้องขึ้นต้นด้วย EMP- และตามด้วยตัวเลข 5 หลัก
Invalid!	อำเภอไม่ตรงกับจังหวัดที่เลือก — กรุณาเปลี่ยน

6. Circle Invalid Data — ตามล่าขยะในข้อมูลที่มีอยู่แล้ว

คุณอาจมีข้อมูลที่กรอกไว้ ก่อน ที่จะตั้ง Data Validation — ข้อมูลเหล่านั้นอาจผิดกฎที่คุณเพิ่งตั้ง!

วิธีตรวจสอบ:

1. ตั้ง Data Validation ตามที่ต้องการ
2. ไปที่ Data > Data Validation > Circle Invalid Data
3. Excel จะ วงกลมสีแดง รอบเซลล์ที่มีข้อมูลผิดกฎ!

การจัดการข้อมูลที่ Circle Highlight:

• ถ้าข้อมูลผิดจริงๆ — แก้ไขให้ถูกต้อง
• ถ้าข้อมูลถูกแต่กฎที่ตั้งไว้ไม่เหมาะ — ปรับกฎ Data Validation
• ถ้าต้องการเอา Circle ออก — ไปที่ Data > Data Validation > Clear Validation Circles

เปิดไฟล์ตัวอย่างที่ชีท “Custom Validation” — มีข้อมูลบางรายการที่ผิดกฎไว้แล้ว ลองใช้ Circle Invalid Data ดูครับ!

7. Data Validation ใน Excel Table — ข้อควรระวัง

Excel Table (Ctrl+T) กับ Data Validation ทำงานร่วมกันได้ครับ — แต่มีข้อควรรู้:

ข้อดี

• เมื่อเพิ่มแถวใหม่ใน Table — Data Validation จะถูก copy ลงไปให้อัตโนมัติ
• ไม่ต้องลาก Data Validation เองทุกครั้ง

ข้อควรระวัง

• Structured References ไม่ทำงานใน Data Validation — ถ้าเขียนสูตรแบบ [@ยอดขาย] ใน Custom Formula — จะ Error!
• INDIRECT กับ Table — ต้องอ้างอิงเป็น Range ปกติเท่านั้น
• Dynamic Named Range กับ Table — ใช้ Table References (=Table1[รายการ]) แทน OFFSET ได้เลย — สะดวกกว่า!

🔥ทางออก: ถ้าต้องการ Data Validation ซับซ้อนใน Table — ให้สร้างคอลัมน์ช่วย (Helper Column) ใน Table สำหรับเก็บค่ากลาง แล้วใช้ Data Validation กับคอลัมน์นั้นแทนการเขียนสูตรซับซ้อนใน Custom Formula โดยตรง

8. Copy-Paste Bypass — จุดอ่อนที่คุณต้องรู้

ข้อเสียที่ใหญ่ที่สุดของ Data Validation คือ ผู้ใช้สามารถ Copy-Paste ข้อมูลจากที่อื่นมาทับได้ — แล้ว Data Validation จะหายไปทันที!

ทำไมถึงเป็นแบบนี้?

เวลาคุณ Copy-Paste ข้อมูลจากแหล่งอื่น — Excel จะวางทั้งค่าและฟอร์แมต รวมถึง วางทับ Data Validation ที่มีอยู่ ทำให้กฎหายไป!

วิธีป้องกัน

• ใช้ Paste Special > Values (Ctrl+Shift+V) — แนะนำให้ผู้ใช้ Paste แบบนี้แทน
• Protect Sheet — ป้องกันชีทแล้ว Data Validation จะคงอยู่ (แต่ก็ยังมีวิธี bypass อยู่ดี)
• ใช้ VBA Event — ตรวจจับการเปลี่ยนแปลงของเซลล์เพื่อบังคับ Data Validation อีกครั้ง (ขั้นสูง)
• ส่งไฟล์เป็น Template — มี Data Validation พร้อม และบอกผู้ใช้ว่า “กรุณาอย่า Copy-Paste ทับเซลล์ที่มี Dropdown”

วิธีเช็คว่า Data Validation ยังอยู่ไหม

ใช้ Conditional Formatting สูตร =A2="" สีเหลืองเฉพาะเซลล์ที่ Data Validation ยังอยู่ — ถ้าสีหาย แสดงว่าถูก Copy-Paste ทับแล้ว!

9. ตัวอย่างประยุกต์จากงานจริง

สถานการณ์ที่ 1: แบบฟอร์มขออนุมัติเดินทาง

บริษัทมีแบบฟอร์มที่พนักงานต้องกรอกก่อนเดินทางไปต่างจังหวัด:

1. Dropdown จังหวัด — รายชื่อจังหวัด
2. Dropdown อำเภอ — เปลี่ยนตามจังหวัด (Dependent Dropdown)
3. Dropdown วัตถุประสงค์ — ประชุม, อบรม, ขายของ, ติดตามงาน
4. วันที่เดินทาง — ต้องเป็นวันธรรมดา (จ-ศ) เท่านั้น โดยใช้สูตร =WEEKDAY(D2,2)<6
5. งบประมาณ — ต้องไม่เกิน 5,000 บาท
6. อนุมัติโดย — Dropdown รายชื่อผู้จัดการที่อนุมัติได้

ถ้าทุกคนกรอกตามฟอร์มนี้ ฝ่ายบัญชีจะได้ข้อมูลที่สะอาด — ไม่ต้องตามไปถามทีละคนว่าจังหวัดไหน อำเภอไหน!

สถานการณ์ที่ 2: ระบบสต็อกสินค้าอย่างง่าย

ร้านค้าออนไลน์ที่ต้องบันทึกสินค้าขายออก:

1. Dropdown หมวดสินค้า — เสื้อผ้า, เครื่องสำอาง, เครื่องใช้ไฟฟ้า, อาหาร
2. **Dropdown สินค้า2. *Dropdown สินค้า* — เปลี่ยนตามหมวด (Dependent Dropdown เช่น เลือก “เสื้อผ้า” ก็เห็นเฉพาะรายชื่อเสื้อผ้า)
3. จำนวน — Whole number 1-100
4. ราคาขาย — Decimal มากกว่า 0 และต้อง <= ราคาทุน * 2 (กำไรไม่เกิน 100%) โดยใช้สูตร =E2<=D2*2

เมื่อครบ end of day — ฝ่ายคลังก็มีข้อมูลที่สะอาดพร้อมนำไปวิเคราะห์ยอดขายต่อ!

สถานการณ์ที่ 3: HR — กรอกประวัติพนักงานใหม่

ฝ่ายบุคคลมีฟอร์มกรอกข้อมูลพนักงานใหม่ที่ซับซ้อน:

1. รหัสพนักงาน — Custom Formula ตรวจสอบรูปแบบ EMP-XXXXX
2. วันที่เริ่มงาน — Date ต้องอยู่ในปี 2026 เท่านั้น
3. แผนก — Dropdown Dynamic (เพิ่มแผนกใหม่ได้โดยไม่ต้องแก้ Data Validation)
4. ตำแหน่ง — Dependent Dropdown เปลี่ยนตามแผนก (เช่น เลือก “ฝ่ายขาย” → มีตำแหน่ง: พนักงานขาย, หัวหน้าฝ่ายขาย, ผู้จัดการ)
5. เงินเดือน — Decimal ระหว่าง 15,000-200,000
6. เบอร์โทรฉุกเฉิน — Text length = 10

HR บอกเลยว่าชีวิตง่ายขึ้น 300% — ข้อมูลพนักงานใหม่ทุกคนกรอกมาตรงตาม format เดียวกัน!

10. สรุป

Data Validation ขั้นสูงไม่ใช่แค่การป้องกันการกรอกผิดเฉยๆ — มันคือการออกแบบระบบข้อมูลให้คนอื่นกรอกแล้วได้ข้อมูลที่สะอาดโดยอัตโนมัติ!

วันนี้เราได้เรียนรู้:

หัวข้อ	สรุป
Custom Formula	COUNTIFS หลายเงื่อนไข, ตรวจสอบ pattern รหัส, เช็คสัดส่วนเปอร์เซ็นต์
Dependent Dropdown	ใช้ INDIRECT หรือ Named Range — Dropdown เปลี่ยนตามค่าอีกเซลล์
Dynamic Named Range	OFFSET + COUNTA — Dropdown ขยายเองเมื่อเพิ่มรายการ
3-Level Dependency	จังหวัด > อำเภอ > ตำบล — ซ้อน INDIRECT ได้เรื่อยๆ
Input Message & Error Alert	Stop สำหรับ Dropdown, Warning สำหรับข้อมูลที่ควรระวัง
Circle Invalid Data	ตรวจสอบข้อมูลเก่าที่ผิดกฎ — Excel วงกลมแดงให้
Data Validation + Table	ใช้ได้แต่ห้ามใช้ Structured References โดยตรง
Copy-Paste Bypass	จุดอ่อนที่สำคัญ — ใช้ Paste Special > Values หรือ Protect Sheet

เคล็ดลับเด็ดจากผม:

• เริ่มจาก Dependent Dropdown ก่อนเลยครับ — เป็นอะไรที่คนใช้ Excel 90% ไม่รู้ แต่พอใช้แล้วติดใจ!
• ตั้งชื่อ Named Range ให้เป็นระเบียบ — เช่น list_จังหวัด, list_แผนก — เวลามีเยอะแล้วหาไม่เจอ
• รวม Data Validation + Conditional Formatting — ให้ CF สีเซลล์ที่มีข้อมูลผิดกฎเพิ่มอีกชั้น
• ถ้าข้อมูล Dependent Dropdown ซับซ้อนมาก (เกิน 3 ระดับ) — ลองใช้ Power Query หรือสร้างฟอร์ม Google Apps Script แทน

ไฟล์ตัวอย่างในบทความนี้มี 3 ชีท:

• ชีท “Custom Validation” — Custom Formula ตรวจสอบรหัสพนักงาน, ห้ามซ้ำหลายคอลัมน์, เช็คราคาขาย
• ชีท “Dependent Dropdown” — Dependent Dropdown จังหวัด-อำเภอ พร้อม Named Range
• ชีท “Dynamic Dropdown” — Dropdown ที่ขยายเองได้โดยใช้ OFFSET + COUNTA

เปิดไฟล์แล้วลองปรับใช้กับงานจริงของคุณดูครับ — แค่เพิ่ม Data Validation สัก 2-3 กฎก็ช่วยประหยัดเวลาทำความสะอาดข้อมูลได้เป็นชั่วโมง!

บทความหน้าผมจะพาคุณไปเจาะลึก Conditional Formatting ขั้นสูง — Formula-Based, Data Bars, Icon Sets และเทคนิคที่ทำให้ข้อมูลของคุณเด่นขึ้นมาในพริบตา เจอกันครับ! 😊