สร้างรายการแบบหล่นลงแบบไดนามิกใน Excel (ทีละขั้นตอน)
ในบทช่วยสอนนี้ เราจะแนะนำวิธีการสร้างรายการดรอปดาวน์ที่ขึ้นอยู่กับค่าที่เลือกในรายการดรอปดาวน์รายการแรกทีละขั้นตอน กล่าวอีกนัยหนึ่ง เราจะสร้างรายการตรวจสอบความถูกต้องของข้อมูล Excel ตามค่าของรายการอื่น
สร้างรายการแบบเลื่อนลงที่ขึ้นอยู่กับไดนามิก
10 วินาทีเพื่อสร้างรายการแบบเลื่อนลงที่ขึ้นอยู่กับเครื่องมือที่มีประโยชน์
สร้างรายการดรอปดาวน์ที่ขึ้นอยู่กับไดนามิกใน Excel 2021 หรือ Excel 365
คำถามที่คุณอาจถามเกี่ยวกับบทช่วยสอนนี้
วิดีโอ: สร้างรายการแบบหล่นลงที่ขึ้นกับ Excel
สร้างรายการแบบเลื่อนลงที่ขึ้นอยู่กับไดนามิก
ขั้นตอนที่ 1: พิมพ์รายการสำหรับรายการแบบหล่นลง
1. ประการแรก พิมพ์รายการที่คุณต้องการให้ปรากฏในรายการแบบเลื่อนลง แต่ละรายการในคอลัมน์แยกกัน.
แจ้งให้ทราบ ว่ารายการในคอลัมน์แรก (ผลิตภัณฑ์) จะเป็นชื่อ Excel สำหรับรายการที่ต้องพึ่งพาในภายหลัง ตัวอย่างเช่น ที่นี่ ผักและผลไม้จะเป็นชื่อสำหรับคอลัมน์ B2:B5 และ C2:C6 แยกกัน
ดูภาพหน้าจอ:
2. จากนั้นสร้างตารางสำหรับแต่ละรายการข้อมูล
เลือกช่วงคอลัมน์ A1:A3 คลิก สิ่งที่ใส่เข้าไป > ตารางจากนั้นในกล่องโต้ตอบสร้างตาราง ให้ทำเครื่องหมาย ตารางของฉันมีส่วนหัว ช่องทำเครื่องหมาย คลิก OK.
จากนั้นทำซ้ำขั้นตอนนี้เพื่อสร้างตารางสำหรับอีกสองรายการ
คุณสามารถดูตารางทั้งหมดและการอ้างอิงถึงช่วงต่างๆ ในตัวจัดการชื่อ (กด Ctrl + F3 เพื่อเปิด)
ขั้นตอนที่ 2: สร้างชื่อช่วง
ในขั้นตอนนี้ คุณต้องสร้าง ชื่อ สำหรับรายการหลักและรายการอ้างอิงแต่ละรายการ
1. เลือกรายการที่ปรากฏในรายการหลัก (A2: A3).
2. จากนั้นไปที่ไฟล์ กล่องชื่อ ซึ่งอยู่ข้างๆ แถบสูตร.
3. พิมพ์ชื่อลงไป ในที่นี้ ให้ตั้งชื่อเป็น ผลิตภัณฑ์.
4 กด เข้าสู่ คีย์ให้เสร็จสมบูรณ์
จากนั้นทำซ้ำขั้นตอนข้างต้นเพื่อสร้างชื่อสำหรับแต่ละรายการที่ขึ้นต่อกันแยกกัน
ที่นี่ตั้งชื่อคอลัมน์ที่สอง (B2:B5) เป็นผลไม้ และคอลัมน์ที่สาม (C2:C6) เป็นผัก
คุณสามารถดูชื่อช่วงทั้งหมดใน Name Manager (กด Ctrl + F3 เพื่อเปิด)
ขั้นตอนที่ 3: เพิ่มรายการดรอปดาวน์หลัก
ถัดไป เพิ่มรายการดรอปดาวน์หลัก (ผลิตภัณฑ์) ซึ่งเป็นรายการดรอปดาวน์การตรวจสอบข้อมูลปกติ ไม่ใช่รายการดรอปดาวน์ที่ขึ้นต่อกัน
1. ประการแรก สร้างตาราง
เลือกเซลล์ (E1) แล้วพิมพ์ส่วนหัวของคอลัมน์แรก (ผลิตภัณฑ์) และย้ายไปยังเซลล์คอลัมน์ถัดไป (F1) พิมพ์ส่วนหัวของคอลัมน์ที่สอง (ชิ้น). คุณจะเพิ่มรายการแบบเลื่อนลงในตารางนี้
จากนั้นเลือกส่วนหัวทั้งสองนี้ (E1 และ F1) คลิก สิ่งที่ใส่เข้าไป และเลือก ตาราง ในกลุ่มตาราง
ในกล่องโต้ตอบสร้างตาราง ให้ทำเครื่องหมาย ตารางของฉันมีส่วนหัว กล่องและคลิก OK.
2. เลือกเซลล์ E2 ที่คุณต้องการแทรกรายการดรอปดาวน์หลัก ให้คลิก ข้อมูล และไปที่ เครื่องมือข้อมูล กลุ่มที่จะคลิก การตรวจสอบข้อมูล > การตรวจสอบข้อมูล.
3. ในกล่องโต้ตอบการตรวจสอบความถูกต้องของข้อมูล
- Choose รายการ ใน อนุญาต มาตรา,
- พิมพ์สูตรด้านล่างลงไป แหล่ง แถบสินค้าคือชื่อรายการหลัก
- คลิก OK.
=Product
คุณสามารถดูรายการแบบหล่นลงหลักที่ถูกสร้างขึ้น
ขั้นตอนที่ 4: เพิ่มรายการแบบหล่นลงขึ้นอยู่กับ
1. เลือกเซลล์ F2 ที่คุณต้องการเพิ่มรายการดรอปดาวน์ที่ขึ้นต่อกัน ให้คลิก ข้อมูล แท็บ และไปที่กลุ่มเครื่องมือข้อมูลเพื่อคลิก การตรวจสอบข้อมูล > การตรวจสอบข้อมูล.
2. ในกล่องโต้ตอบการตรวจสอบความถูกต้องของข้อมูล
- Choose รายการ ใน อนุญาต มาตรา,
- พิมพ์สูตรด้านล่างลงไป แหล่ง แถบ E2 คือเซลล์ที่มีรายการดรอปดาวน์หลัก
- คลิก OK.
=INDIRECT(SUBSTITUTE(E2," ","_"))
หาก E2 ว่างเปล่า (คุณไม่ได้เลือกรายการใดรายการหนึ่งในรายการดรอปดาวน์หลัก) คุณจะเห็นข้อความปรากฏขึ้นด้านล่าง ให้คลิก ใช่ เพื่อดำเนินการต่อ
ขณะนี้มีการสร้างรายการแบบเลื่อนลงที่ขึ้นต่อกันแล้ว
ขั้นตอนที่ 5: ทดสอบรายการแบบเลื่อนลงที่ขึ้นต่อกัน
1 เลือก ผลไม้ ในรายการดรอปดาวน์หลัก (E2) จากนั้นไปที่รายการดรอปดาวน์ที่ขึ้นต่อกัน (F2) เพื่อคลิกไอคอนลูกศร ดูว่าผลไม้อยู่ในรายการหรือไม่ จากนั้นเลือกหนึ่งรายการจากรายการดรอปดาวน์ที่ขึ้นต่อกัน
2 กด แถบ คีย์เพื่อเริ่มแถวใหม่ในตารางการป้อนข้อมูล เลือก ผักและย้ายไปยังเซลล์ถัดไปทางขวา ดูว่ารายการผักอยู่ในรายการหรือไม่ จากนั้นเลือกหนึ่งรายการจากรายการดรอปดาวน์ที่ขึ้นต่อกัน
- หากไม่มีรายการที่เลือกในรายการดรอปดาวน์หลัก (คอลัมน์ผลิตภัณฑ์) รายการดรอปดาวน์ที่ขึ้นต่อกัน (คอลัมน์รายการ) จะไม่ทำงาน
- หากคุณต้องการรีเซ็ตหรือล้างเนื้อหาของรายการแบบเลื่อนลงที่อ้างอิงหลังจากเลือกการเปลี่ยนแปลง โปรดไปที่บทความนี้ วิธีการล้างเซลล์รายการแบบหล่นลงที่ขึ้นอยู่กับการเลือกเปลี่ยนแปลงใน Excelจะแนะนำรหัส VBA เพื่อช่วยคุณ
- หากคุณต้องการสร้างรายการแบบเลื่อนลง 3 ระดับ บทความนี้ วิธีสร้างรายการแบบหล่นลงขึ้นอยู่กับหลายระดับใน Excel จะช่วยคุณ.
10 วินาทีเพื่อสร้างรายการแบบเลื่อนลงที่ขึ้นอยู่กับเครื่องมือที่มีประโยชน์
Kutools สำหรับ Excel มีเครื่องมือที่ทรงพลังในการสร้างรายการแบบเลื่อนลงที่ขึ้นต่อกันได้ง่ายและรวดเร็วขึ้น มาดูกัน:
ก่อนทำตามขั้นตอนด้านล่างนี้ คลิกเพื่อดาวน์โหลด Kutools for Excel สำหรับทดลองใช้ฟรี 30 วัน ประการแรก
ขั้นตอนที่ 1: พิมพ์รายการสำหรับรายการแบบหล่นลง
ประการแรก จัดเรียงข้อมูลของคุณตามภาพหน้าจอด้านล่าง:
ขั้นตอนที่ 2: การใช้เครื่องมือ Kutools
1. เลือกข้อมูลที่คุณสร้าง คลิก Kutools แล้วคลิก รายการแบบหล่นลง เพื่อแสดงเมนูย่อย คลิก รายการแบบเลื่อนลงแบบไดนามิก.
2. ในรายการแบบหล่นลงขึ้นอยู่กับ
- ตรวจสอบ โหมด B ที่ตรงกับโหมดข้อมูลของคุณ
- เลือก ช่วงเอาท์พุทคอลัมน์ช่วงเอาต์พุตต้องเท่ากับคอลัมน์ช่วงข้อมูล
- คลิก Ok.
ตอนนี้สร้างรายการแบบเลื่อนลงที่ขึ้นต่อกันแล้ว
- โหมด B รองรับการสร้างรายการดรอปดาวน์ระดับที่สามขึ้นไป:
- หากข้อมูลของคุณถูกจัดเรียงตามที่แสดงภาพหน้าจอด้านล่าง คุณต้องใช้โหมด A โหมด A รองรับเฉพาะเพื่อสร้างรายการแบบเลื่อนลงที่ขึ้นต่อกัน 2 ระดับ
- รายละเอียดเพิ่มเติมเกี่ยวกับวิธีใช้ Kutools เพื่อสร้างรายการแบบเลื่อนลงที่ขึ้นต่อกัน โปรดไปที่ บทช่วยสอนนี้ .
สร้างรายการดรอปดาวน์ที่ขึ้นอยู่กับไดนามิกใน Excel 2021 หรือ Excel 365
หากคุณอยู่ใน Excel 2021 หรือ Excel 365 มีวิธีอื่นที่สามารถสร้างรายการดรอปดาวน์ที่ขึ้นอยู่กับไดนามิกได้อย่างรวดเร็วโดยใช้ฟังก์ชันใหม่ ที่ไม่ซ้ำกัน และ FILTER.
หากข้อมูลต้นฉบับของคุณถูกจัดเรียงเป็นภาพหน้าจอ โปรดปฏิบัติตามขั้นตอนด้านล่างเพื่อสร้างรายการแบบเลื่อนลงแบบไดนามิก
ขั้นตอนที่ 1: ใช้สูตรเพื่อรับรายการสำหรับรายการแบบหล่นลงหลัก
เลือกเซลล์ ตัวอย่างเช่น เซลล์ G3 และใช้ฟังก์ชัน UNIQUE และ FILTER เพื่อแยกค่าที่ไม่ซ้ำออกจาก ผลิตภัณฑ์ รายการซึ่งจะเป็นที่มาของรายการดรอปดาวน์หลัก และกด เข้าสู่ กุญแจ
=UNIQUE(FILTER(A3:A20, A3:A20<>""))
ขั้นตอนที่ 2: สร้างรายการแบบหล่นลงหลัก
1. เลือกเซลล์ที่คุณต้องการวางรายการดรอปดาวน์หลัก เช่น เซลล์ D3คลิก ข้อมูล และไปที่ เครื่องมือข้อมูล กลุ่มที่จะคลิก การตรวจสอบข้อมูล > การตรวจสอบข้อมูล.
2. ในกล่องโต้ตอบการตรวจสอบความถูกต้องของข้อมูล
- Choose รายการ ใน อนุญาต มาตรา,
- พิมพ์สูตรด้านล่างลงไป แหล่ง บาร์,
- คลิก OK.
=$G$3#
ตอนนี้รายการแบบเลื่อนลงหลักถูกสร้างขึ้น
ขั้นตอนที่ 3: ใช้สูตรเพื่อรับรายการสำหรับรายการแบบเลื่อนลงที่ขึ้นต่อกัน
เลือกเซลล์ เช่น เซลล์ H3 โดยใช้ฟังก์ชัน FILTER เพื่อกรองรายการตามค่าในเซลล์ D3 (รายการที่เลือกในรายการดรอปดาวน์หลัก) กด เข้าสู่ กุญแจ
=FILTER(B3:B20, A3:A20=D3)
ขั้นตอนที่ 4: สร้างรายการแบบหล่นลงที่ขึ้นต่อกัน
1. เลือกเซลล์ที่จะวางรายการแบบหล่นลงที่ขึ้นต่อกัน ตัวอย่างเช่น เซลล์ E3คลิก ข้อมูล และไปที่ เครื่องมือข้อมูล กลุ่มที่จะคลิก การตรวจสอบข้อมูล > การตรวจสอบข้อมูล.
2. ในกล่องโต้ตอบการตรวจสอบความถูกต้องของข้อมูล
- Choose รายการ ใน อนุญาต มาตรา,
- พิมพ์สูตรด้านล่างลงไป แหล่ง บาร์,
- คลิก OK.
=$H$3#
ตอนนี้สร้างรายการแบบหล่นลงที่ขึ้นต่อกันเรียบร้อยแล้ว
เมื่อคุณเพิ่มรายการใหม่หรือทำการเปลี่ยนแปลงบางอย่างใน A3:A20 รายการดรอปดาวน์จะได้รับการอัปเดตโดยอัตโนมัติ
จัดเรียงรายการแบบเลื่อนลงตามตัวอักษร
หากคุณต้องการจัดเรียงรายการในรายการแบบเลื่อนลงตามตัวอักษร คุณสามารถใช้สูตรด้านล่างกับตารางการเตรียมการได้สำหรับเมนูแบบเลื่อนลงหลัก (สูตรในเซลล์ G3):
=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")))
สำหรับเมนูแบบเลื่อนลง (สูตรในเซลล์ H3):
=SORT(FILTER(B3:B20, A3:A20=D3))
ตอนนี้รายการแบบเลื่อนลงทั้งสองจะเรียงตามตัวอักษร A ถึง Z
สำหรับการจัดเรียงตามตัวอักษร Z ถึง A โปรดใช้สูตรด้านล่าง:
สำหรับเมนูแบบเลื่อนลงหลัก (สูตรในเซลล์ G3):
=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")), 1, -1)
สำหรับเมนูแบบเลื่อนลง (สูตรในเซลล์ H3):
=SORT(FILTER(B3:B20, A3:A20=D3), 1, -1)
คำถามที่คุณอาจถาม:
1. ทำไมต้องใส่ตารางสำหรับแต่ละรายการข้อมูล?
การแทรกตารางสำหรับรายการข้อมูลจะช่วยให้คุณอัปเดตรายการแบบหล่นลงโดยอัตโนมัติตามการเปลี่ยนแปลงในรายการข้อมูล ตัวอย่างเช่น การเพิ่ม 'อื่นๆ' ในรายการข้อมูลแรก จากนั้นรายการแบบเลื่อนลงหลักจะถูกเพิ่มโดยอัตโนมัติด้วย 'อื่นๆ'
2. เหตุใดจึงต้องใช้ตารางเพื่อวางรายการแบบเลื่อนลง
เมื่อคุณกดแป้น Tab เพื่อเพิ่มบรรทัดใหม่ในตาราง รายการแบบหล่นลงจะถูกเพิ่มโดยอัตโนมัติในบรรทัดใหม่เช่นกัน
3. ฟังก์ชัน INDIRECT ทำงานอย่างไร
ทางอ้อม ฟังก์ชันใช้ในการแปลงสตริงข้อความเป็นข้อมูลอ้างอิงที่ถูกต้อง
4. สูตร INDIRECT(SUBSTITUTE(E2&F2," ","")) ทำงานอย่างไร
ประการแรก ทดแทน ฟังก์ชันแทนที่ข้อความด้วยข้อความอื่น ที่นี่ใช้เพื่อลบช่องว่างออกจากชื่อที่รวมกัน (E2 และ F2) แล้ว ทางอ้อม ฟังก์ชันแปลงสตริงข้อความ (เนื้อหาที่รวมกันโดย E2 และ F2) เป็นการอ้างอิงที่ถูกต้อง
สุดยอดเครื่องมือเพิ่มผลผลิตในสำนักงาน
เพิ่มพูนทักษะ Excel ของคุณด้วย Kutools สำหรับ Excel และสัมผัสประสิทธิภาพอย่างที่ไม่เคยมีมาก่อน Kutools สำหรับ Excel เสนอคุณสมบัติขั้นสูงมากกว่า 300 รายการเพื่อเพิ่มประสิทธิภาพและประหยัดเวลา คลิกที่นี่เพื่อรับคุณสมบัติที่คุณต้องการมากที่สุด...
แท็บ Office นำอินเทอร์เฟซแบบแท็บมาที่ Office และทำให้งานของคุณง่ายขึ้นมาก
- เปิดใช้งานการแก้ไขและอ่านแบบแท็บใน Word, Excel, PowerPoint, ผู้จัดพิมพ์, Access, Visio และโครงการ
- เปิดและสร้างเอกสารหลายรายการในแท็บใหม่ของหน้าต่างเดียวกันแทนที่จะเป็นในหน้าต่างใหม่
- เพิ่มประสิทธิภาพการทำงานของคุณ 50% และลดการคลิกเมาส์หลายร้อยครั้งให้คุณทุกวัน!
สารบัญ
- วิดีโอ: สร้างรายการแบบหล่นลงที่ขึ้นกับ Excel
- สร้างรายการแบบเลื่อนลงที่ขึ้นอยู่กับไดนามิก
- 10 วินาทีเพื่อสร้างรายการแบบเลื่อนลงที่ขึ้นต่อกัน
- สร้างรายการดรอปดาวน์ที่ขึ้นอยู่กับไดนามิกใน Excel 2021/365
- คำถามที่พบบ่อย
- บทความที่เกี่ยวข้อง
- เครื่องมือเพิ่มประสิทธิภาพการทำงานในสำนักงานที่ดีที่สุด
- ความคิดเห็น