การสร้าง dependent drop down list เป็นแนวปฏิบัติทั่วไปในหมู่ผู้ใช้ Excel อย่างไรก็ตาม การสร้าง dependent drop down list ที่มีช่องว่าง (space) ใน Excel ต้องการการจัดการช่องว่างระหว่างคำ
สมมติว่าเรามี 3 Product Lists ที่มีชื่อ 2 คำ ทำให้เกิดช่องว่างระหว่างคำ เราต้องสร้าง drop down list ที่อนุญาตให้มีช่องว่างภายในชื่อที่ขึ้นกันได้ ฟังก์ชัน INDEX, MATCH และ INDIRECT ของ Excel เป็นสูตรที่อนุญาตให้ช่องว่างมีอยู่ในชื่อที่อ้างอิง
ในบทความนี้ เราจะสาธิต 2 วิธีง่ายๆ ในการสร้าง Excel dependent drop down list ที่มีช่องว่าง
ดาวน์โหลดสมุดงาน Excel
2 วิธีง่าย สร้าง Excel Dependent Drop Down List ที่มีช่องว่าง
ในแท็บ Data ของ Excel มีคุณสมบัติ Data Validation ภายในส่วน Data Tools ผู้ใช้สามารถแทรก drop down list ได้ง่ายด้วยคุณสมบัติ Data Validation แต่ขณะสร้าง dependent drop down list ช่องว่างที่มีอยู่ภายในรายการที่ขึ้นกันได้จะทำให้เกิดข้อผิดพลาดในกระบวนการ
เพื่อแก้ไขปัญหานี้ เราใช้วิธีต่อไปนี้
วิธีที่ 1: Dependent Drop Down List ที่มีช่องว่าง ด้วยฟังก์ชัน Excel รวม
ฟังก์ชัน INDEX และ MATCH รวมกันจะไม่สนใจช่องว่างภายในชื่อหรือหัวข้อที่ขึ้นกันได้ ไวยากรณ์ของฟังก์ชัน INDEX คือ
=INDEX (array, row_num, [col_num], [area_num])array; ช่วงเซลล์หรืออาร์เรย์
row_num; ตำแหน่งแถวภายในอาร์เรย์
col_num; ตำแหน่งคอลัมน์ภายในอาร์เรย์ [optional]
area_num; ช่วงที่ใช้ในอาร์เรย์ [optional]
อีกครั้ง ไวยากรณ์ของฟังก์ชัน MATCH คือ
=MATCH (lookup_value, lookup_array, [match_type])lookup_value; ค่าที่ต้องการค้นหาใน lookup_array
lookup_array; อาร์เรย์หรือการอ้างอิงเพื่อค้นหา lookup_value
match_type; โดยค่าเริ่มต้น ตรงหรือค่าที่น้อยกว่าที่ใกล้ที่สุด=1, ตรงเป๊ะ=0, และ ตรงหรือค่าที่มากกว่าที่ใกล้ที่สุด=-1 [optional]
🔁 Drop Down List
ขั้นตอนที่ 1: วางเคอร์เซอร์ที่เซลล์ว่างใดๆ (เช่น F4) แล้วไปที่ Data > เลือก Data Validation (จากส่วน Data Tools)
ขั้นตอนที่ 2: หน้าต่าง Data Validation เปิดขึ้น ในส่วน Data Validation Setting,
- เลือก Validation Criteria Allow เป็น List
- เลือก B4:D4 เป็น Source
- คลิก OK
➤ ตอนนี้ ในแผ่นงาน คลิกที่ไอคอนลูกศรลงในเซลล์ F4 คุณจะเห็นเซลล์ทั้งหมดที่กำหนดเป็น Source ในหน้าต่าง Data Validation ปรากฏเป็น drop down list
🔁 Dependent Drop Down List
ขั้นตอนที่ 3: ทำซ้ำ ขั้นตอนที่ 1 ถึง 2 สำหรับเซลล์ F5 พิมพ์สูตรต่อไปนี้เป็น Source ในหน้าต่าง Data Validation
=INDEX(B5:D13,,MATCH($F$4,$B$4:$D$4,0))เปรียบเทียบสูตรกับไวยากรณ์ที่อธิบายไว้ก่อนหน้า ส่วนสูตร MATCH($F$4,$B$4:$D$4,0) ส่ง col_num ให้ฟังก์ชัน INDEX B5:D13 = array ของฟังก์ชัน INDEX และไม่รับ row_num
ส่วน MATCH รับ F4 เป็น lookup_value, B4:D4 เป็น lookup_array และ 0 หมายถึงประเภท exact match
คลิก OK
➤ การคลิก OK จะนำคุณกลับสู่แผ่นงาน ในแผ่นงาน ที่เซลล์ F5 คลิกที่ไอคอนลูกศรลง และคุณจะเห็นว่าช่องว่างที่มีอยู่ไม่ทำให้เกิดปัญหาในการสร้าง dependent drop down list
อ่านเพิ่มเติม: วิธีสร้าง Drop-Down List ตามสูตรใน Excel (4 วิธี)
บทความคล้ายกัน
- Conditional Drop Down List ใน Excel (สร้าง เรียงลำดับ และใช้)
- วิธีเชื่อมโยงค่าเซลล์กับ Drop Down List ใน Excel (5 วิธี)
- Excel Drop Down List Depending on Selection
- สร้าง Searchable Drop Down List ใน Excel (2 วิธี)
- วิธีสร้าง Excel Drop Down List พร้อมสี (2 วิธี)
วิธีที่ 2: ใช้ Define Name และฟังก์ชัน INDIRECT
ก่อนหน้านี้ เราสาธิตว่า ฟังก์ชัน INDIRECT ไม่สามารถสร้าง drop down list ได้ในขณะที่มีช่องว่างในชื่อหรือการอ้างอิงที่ขึ้นกันได้ เพื่อให้ ฟังก์ชัน INDIRECT อนุญาตช่องว่างภายในรายการที่ขึ้นกันได้ เราต้องปรับสูตรเล็กน้อยโดยใช้ฟังก์ชัน SUBSTITUTE ไวยากรณ์ของ ฟังก์ชัน INDIRECT คือ
=INDIRECT (ref_text, [a1])ref_text; การอ้างอิงในรูปแบบ Text
a1; การบ่งชี้บูลีนของ A1 โดยค่าเริ่มต้น TRUE = A1 style [Optional]
🔁 การ Define Name
ขั้นตอนที่ 1: เลือกหัวคอลัมน์ (เช่น B4:D4) แล้วไปที่ Formulas > เลือก Define Names (จากส่วน Define Names)
ขั้นตอนที่ 2: หน้าต่าง New Name ปรากฏ ในหน้าต่าง New Name,
- กำหนด Name สำหรับเซลล์ (เช่น List)
- Excel จะดึงการอ้างอิงเซลล์อัตโนมัติสำหรับกล่อง Refers to
- คลิก OK
ขั้นตอนที่ 3: ทำซ้ำ ขั้นตอนที่ 1 และ 2 ของวิธีนี้ เพื่อกำหนดชื่อและช่วงของรายการต่างๆ
➤ คุณสามารถตรวจสอบชื่อที่กำหนดทั้งหมดได้จาก Formulas > Name Manager (จากส่วน Define Name)
🔁 Drop Down List
ขั้นตอนที่ 4: ตอนนี้ วางเคอร์เซอร์ที่เซลล์ F4 ไปที่ Data > เลือก Data Validation (จากส่วน Data Tools)
ขั้นตอนที่ 5: กล่องโต้ตอบ Data Validation ปรากฏ ในกล่องโต้ตอบ,
- ภายใต้ Validation Criteria เลือก List ในกล่อง Allow
- พิมพ์ =List (List คือชื่อที่กำหนดสำหรับหัวคอลัมน์) ในกล่องโต้ตอบ Source
- คลิก OK
➤ หากคุณคลิกที่ไอคอนลูกศรลงในเซลล์ F4 คุณจะเห็นหัวคอลัมน์เป็น drop down list ตามภาพด้านล่าง
🔁 Dependent Drop Down List
ขั้นตอนที่ 6: ดำเนินการ ขั้นตอนที่ 4 และ 5 เพื่อแสดงหน้าต่าง Data Validation ในหน้าต่าง,
- วางสูตรต่อไปนี้เป็น Source
=INDIRECT(SUBSTITUTE($F$4," ","_"))
ในสูตร ฟังก์ชัน SUBSTITUTE แทนที่ช่องว่างในการอ้างอิง F4 ด้วย underscore (_) คุณสามารถใช้ตัวอักษรอื่นแทนช่องว่างใน defined names และในสูตร INDIRECT เช่นกัน ดังนั้น INDIRECT จะแปลงรายการของ F4 ให้คล้ายกับ defined names ขึ้นอยู่กับค่า F4 ฟังก์ชัน INDIRECT จะแสดงรายการสินค้า
➤ หลังจากคลิก OK จะได้ dependent list แสดงขึ้น ตามภาพด้านล่าง
คุณสามารถเปลี่ยนรายการชื่อที่ขึ้นกันได้จาก drop down list แรก ได้ ดังผล รายการใน drop down list ที่สองจะเปลี่ยนอัตโนมัติ
อ่านเพิ่มเติม: วิธีสร้าง Drop Down List ใน Excel (อิสระและขึ้นกันได้)
สรุป
ในบทความนี้ เราสาธิตสูตรสำหรับแทรก Excel dependent drop down list ที่มีช่องว่าง สูตร INDEX และ MATCH รวมกันรับช่องว่างภายในชื่อหรือหัวข้อที่ขึ้นกันได้โดยอัตโนมัติ อย่างไรก็ตาม เราต้องปรับ ฟังก์ชัน INDIRECT ให้อนุญาตช่องว่างภายในรายการที่ขึ้นกันได้ หวังว่าวิธีที่กล่าวมาจะตอบสนองความต้องการของคุณ คอมเมนต์ได้หากมีคำถามเพิ่มเติมหรือมีสิ่งเพิ่มเติม
บทความเกี่ยวข้อง
- VLOOKUP with Drop Down List in Excel
- วิธีแก้ไข Drop-Down List ใน Excel (4 วิธีพื้นฐาน)
- สร้าง Drop Down List จากแผ่นงานอื่นใน Excel (2 วิธี)
- วิธิลบ Drop-Down List ใน Excel
- วิธีใช้ IF Statement สร้าง Drop-Down List ใน Excel
- Excel Drop Down List Not Working (8 ปัญหาและวิธีแก้)
