Data Validation ใน Excel ให้ความสะดวกในการตั้งช่วงการตรวจสอบข้อมูลที่ช่วยได้หลายอย่าง เช่น ข้อมูลไหนเลือกได้/ไม่ได้ หรือสร้างรายการแบบเลื่อนลงที่ประหยัดเวลา ขณะใช้ฟังก์ชัน VLOOKUP เราต้องตั้งค่า lookup value และ Data Validation สามารถทำให้ง่ายขึ้น บทความนี้จะให้ 2 วิธีที่มีประโยชน์ในการใช้สูตร VLOOKUP แบบกำหนดเอง (Custom) ใน Data Validation ของ Excel
คุณสามารถดาวน์โหลดเทมเพลต Excel ฟรีจากที่นี่และฝึกปฏิบัติด้วยตัวเอง
2 วิธีใช้สูตร VLOOKUP แบบกำหนดเองใน Excel Data Validation
มาทำความรู้จักชุดข้อมูลของเราก่อน มันแสดงถึงยอดขายของพนักงานขายบางคนในภูมิภาคต่างๆ
1. ใช้รายการแบบเลื่อนลงของ Data Validation ร่วมกับฟังก์ชัน VLOOKUP ใน Excel
ในวิธีนี้ เราจะใช้ฟีเจอร์ Drop-Down list ของ Data Validation สำหรับ ฟังก์ชัน VLOOKUP เพื่อที่เราจะหาข้อมูลสำหรับ lookup value ได้ง่าย ขั้นแรก เราจะดูวิธีสร้าง drop-down list สำหรับ เซลล์ D11 จากนั้นเราจะใช้มันสำหรับ ฟังก์ชัน VLOOKUP
ขั้นตอน:
- เลือก เซลล์ D11
- จากนั้นคลิกตามลำดับ: Data > Data Tools > Data Validation > Data Validation
และทันที กล่องโต้ตอบจะเปิดขึ้น
- จากส่วน Settings (การตั้งค่า) เลือก List (รายการ) จากกล่องแบบเลื่อนลง Allow (อนุญาต)
- ต่อไป คลิก ที่ไอคอน Open (เปิด) จากกล่อง Source (แหล่งที่มา)
มันจะนำคุณไปตั้งค่าช่วงข้อมูล
- เลือก (Select) ชื่อพนักงานขาย (salespersons' names) โดยลากเมาส์
- จากนั้นกดปุ่ม Enter และมันจะนำคุณกลับไปที่กล่องโต้ตอบก่อนหน้า
- ช่วงถูกเลือกสำเร็จ แค่กด OK
จากนั้นคุณจะได้ไอคอนแบบเลื่อนลงข้างขวาเซลล์ที่เลือก คลิกมันคุณจะได้รายการ มาเลือก Sam และใช้ ฟังก์ชัน VLOOKUP ตอนนี้ เราจะใช้ VLOOKUP โดยใช้รายการนี้เพื่อหายอดขายและภูมิภาคแยกกันแตพร้อมกัน
- เพื่อได้ชื่อภูมิภาค ให้เขียนสูตรต่อไปนี้ใน เซลล์ D12–
=VLOOKUP(D11,B5:D9,2,0)
- จากนั้นกดปุ่ม Enter เพื่อได้ผลลัพธ์
ตอนนี้เราจะหายอดขายใน เซลล์ D13
- พิมพ์สูตรต่อไปนี้ในมัน-
=VLOOKUP(D11,B5:D9,3,0)
- ต่อไป กดปุ่ม Enter เพื่อได้ผลลัพธ์
ตอนนี้ถ้าคุณต้องการได้ภูมิภาคและยอดขายสำหรับพนักงานขายคนอื่น แค่เลือกชื่อจาก drop-down list และคุณจะได้ผลลัพธ์ที่ตรงกันทันที
อ่านเพิ่มเติม: วิธีใช้ Named Range สำหรับ Data Validation List กับ VBA ใน Excel
บทความคล้ายกัน:
- [Fixed] Data Validation Not Working for Copy Paste in Excel (with Solution)
- วิธีลบเซลล์ว่างจาก Data Validation List ใน Excel (5 วิธี)
- Default Value in Data Validation List with Excel VBA (Macro and UserForm)
- วิธีสร้าง Data Validation List จากตารางใน Excel (3 วิธี)
- Excel Data Validation Drop Down List with Filter (2 Methods)
2. ใช้ Data Validation แบบไดนามิกกับสูตร VLOOKUP คู่ (Double VLOOKUP)
ที่นี่ เราจะตรวจสอบความถูกต้องของข้อมูลสำหรับเกณฑ์ที่กำหนดโดยใช้เครื่องมือ Data Validation และฟังก์ชัน VLOOKUP คู่ (double VLOOKUP) หากตรงตามเกณฑ์ Excel จะแสดง TRUE มิฉะนั้น FALSE สำหรับนั้น ผมใช้ชุดข้อมูลใหม่ที่แสดงราคาของอุปกรณ์บางอย่าง ผมเลือกช่วงราคาต่ำสุดและสูงสุดสำหรับแต่ละรายการ ตอนนี้ผมจะตรวจสอบราคาที่กำหนดว่าตรงตามเกณฑ์หรือไม่โดยใช้ฟังก์ชัน VLOOKUP คู่
ขั้นตอน:
- ในเซลล์ D11 เขียนสูตรต่อไปนี้-
=AND(C11>=VLOOKUP(B11,B5:D8,2,0),C11<=VLOOKUP(B11,B5:D8,3,0))
- ต่อไป กดปุ่ม Enter สำหรับผลลัพธ์ และมันบอกว่าเป็น TRUE
- สุดท้าย แค่ลาก Fill Handle ลงเพื่อได้ผลลัพธ์อื่นๆ
นี่คือผลลัพธ์ทั้งหมด-
💭 การแยกส่วนสูตร:
➥ C11<=VLOOKUP(B11,B5:D8,3,0)
ที่นี่ ฟังก์ชัน VLOOKUP จะหาช่วงบนสำหรับค่าของ เซลล์ B11 และ Excel จะตรวจสอบค่าของ เซลล์ C11 ว่ามันน้อยกว่าหรือเท่ากับผลลัพธ์ของฟังก์ชัน VLOOKUP หรือไม่ ดังนั้นมันจะคืนค่าเป็น-
TRUE
➥ C11>=VLOOKUP(B11,B5:D8,2,0)
ฟังก์ชัน VLOOKUP นี้จะหาช่วงล่างสำหรับค่าของ เซลล์ B11 และ Excel จะตรวจสอบค่าของ เซลล์ C11 ว่ามันมากกว่าหรือเท่ากับผลลัพธ์ของฟังก์ชัน VLOOKUP หรือไม่ ดังนั้นมันคืนค่า-
TRUE
➥ AND(C11>=VLOOKUP(B11,B5:D8,2,0),C11<=VLOOKUP(B11,B5:D8,3,0))
สุดท้าย ฟังก์ชัน AND จะรวมผลลัพธ์ทั้งสอง หากทั้งสองคืน TRUE มันจะคืน TRUE หากผลลัพธ์ใดผลลัพธ์หนึ่งคืน FALSE มันจะคืน FALSE ดังนั้นผลลัพธ์สุดท้ายจะคืนค่าเป็น-
TRUE
อ่านเพิ่มเติม: วิธีใช้ Multiple Data Validation ในเซลล์เดียวใน Excel (3 ตัวอย่าง)
สรุป
ผมหวังว่าขั้นตอนที่อธิบายไว้ข้างต้นจะเพียงพอสำหรับการใช้ Excel Data Validation กับสูตร VLOOKUP แบบกำหนดเอง โปรดถามคำถามใดๆ ในส่วนความคิดเห็นและให้คำติชมด้วยนะครับ
บทความที่เกี่ยวข้อง
- ใช้ Data Validation แบบกำหนดเองสำหรับเกณฑ์หลายอย่างใน Excel (4 ตัวอย่าง)
- วิธีใช้คำสั่ง IF ในสูตร Data Validation ใน Excel (6 วิธี)
- ใช้ Data Validation ใน Excel พร้อมสี (4 วิธี)
- วิธีใช้ Data Validation List จากแผ่นงานอื่น (6 วิธี)
- Excel VBA สร้าง Data Validation List จาก Array
