ในการดึงข้อมูลเฉพาะตามค่าที่ระบุ เราอาจต้องใช้รายการแบบเลื่อนลง ยิ่งกว่านั้น เราจำเป็นต้องเชื่อมโยง รายการแบบเลื่อนลงที่ขึ้นต่อกันสองรายการขึ้นไปร่วมกัน . ในบทความนี้ เราจะแสดงวิธีเปลี่ยนรายการดรอปดาวน์ตามค่าของเซลล์ใน Excel
2 วิธีที่เหมาะสมในการเปลี่ยนรายการแบบหล่นลงตามค่าของเซลล์ใน Excel
ในส่วนด้านล่าง เราจะเน้นที่ 2 วิธีที่เหมาะสมที่สุดในการเปลี่ยนรายการดรอปดาวน์ ก่อนอื่น เราจะใช้ OFFSET และ MATCH ทำงานในรายการดรอปดาวน์เพื่อทำการเปลี่ยนแปลงตามค่าของเซลล์ นอกจากนี้ เราจะใช้ XLOOKUP ฟังก์ชันเด่นใน Microsoft Excel 365 ที่จะทำเช่นเดียวกัน ในภาพด้านล่าง เราได้จัดเตรียมชุดข้อมูลตัวอย่างเพื่อให้งานสำเร็จ
1. รวมฟังก์ชัน OFFSET และ MATCH เพื่อเปลี่ยนรายการแบบหล่นลงตามค่าของเซลล์ใน Excel
ในชุดข้อมูลต่อไปนี้ เรามีพนักงานขาย 3 คนพร้อมผลิตภัณฑ์ที่ขาย ตอนนี้ เราต้องการค้นหาผลิตภัณฑ์สำหรับพนักงานขายรายใดรายหนึ่ง โดยทำตามขั้นตอนด้านล่าง
ขั้นตอนที่ 1:สร้างรายการตรวจสอบข้อมูล
- ไป ไปยัง ข้อมูล
- คลิก ใน การตรวจสอบความถูกต้องของข้อมูล .
ขั้นตอนที่ 2:เลือกแหล่งที่มาสำหรับรายการ
- จาก อนุญาต ตัวเลือก เลือก รายการ
- ใน ที่มา กล่อง เลือก ช่วงต้นทาง E4:G4 สำหรับชื่อพนักงานขาย
- กด Enter .
- ดังนั้น เมนูแบบเลื่อนลงจะปรากฏในเซลล์ B5 .
ขั้นตอนที่ 3:ใช้ฟังก์ชัน OFFSET
- พิมพ์สูตรต่อไปนี้สำหรับ OFFSET ฟังก์ชัน
=OFFSET($E$4) - นี่ E4 เป็น ข้อมูลอ้างอิง เซลล์ในรูปแบบสัมบูรณ์
- ใน แถว อาร์กิวเมนต์ใส่ 1 เป็นค่าที่จะนับ 1 แถวลงจากเซลล์อ้างอิง E4 .
=OFFSET($E$4,1
ขั้นตอนที่ 4:ใช้ฟังก์ชัน MATCH เพื่อกำหนดคอลัมน์ฟังก์ชัน OFFSET
- ใน cols อาร์กิวเมนต์ ในการเลือกคอลัมน์ให้ใช้ MATCH ทำงานตามสูตรต่อไปนี้
=OFFSET($E$4,1,MATCH($B$5 - นี่ B5 คือค่าเซลล์ที่เลือกในรายการแบบเลื่อนลง
- ในการเลือก lookup_array อาร์กิวเมนต์สำหรับ MATCH ฟังก์ชั่นเพิ่ม E4:G4 เป็นช่วงในรูปแบบสัมบูรณ์ด้วยสูตรต่อไปนี้
=OFFSET($E$4,1,MATCH($B$5,$E$4:$G$4
- พิมพ์ 0 สำหรับ แน่นอน ประเภทการแข่งขัน สูตรต่อไปนี้จะส่งกลับ 3 สำหรับ MATCH
MATCH($B$5,$E$4:$G$4,0)
- เขียนลบ 1 (-1 ) จาก MATCH ฟังก์ชัน เนื่องจาก OFFSET ฟังก์ชั่นนับ คอลัมน์แรก เป็น ศูนย์ (0 )
MATCH($B$5,$E$4:$G$4,0)-1
ขั้นตอนที่ 5:ป้อนความสูงของคอลัมน์
- สำหรับการเลือก 1 ใน ความสูง อาร์กิวเมนต์จะนับว่าแต่ละคอลัมน์มีค่าหนึ่งค่า
=OFFSET($E$4,1,MATCH($B$5,$E$4:$G$4,0)-1,1
ขั้นตอนที่ 6:ป้อนค่าความกว้าง
- สำหรับ ความกว้าง อาร์กิวเมนต์ พิมพ์ 1 .
=OFFSET($E$4,1,MATCH($B$5,$E$4:$G$4,0)-1,1,1)
- ดังนั้น คุณจะเห็นว่าเมื่อเราเลือก Jacob ใน B5 , ก็จะส่งผลให้ ช็อกโกแลต เป็นองค์ประกอบแรกสำหรับ Jacob .
ขั้นตอนที่ 7:นับองค์ประกอบของแต่ละคอลัมน์
- ในการนับจำนวนองค์ประกอบในคอลัมน์ เราจะใช้ COUNTA ทำงานในเซลล์ C13 ด้วยสูตรดังต่อไปนี้
=COUNTA(OFFSET($E$4,1,MATCH($B$5,$E$4:$G$4,0)-1,10))
- สิ่งนี้จะนับองค์ประกอบ/ผลิตภัณฑ์ เบอร์สำหรับพนักงานขายคนใดคนหนึ่ง (จาค็อบ )
ขั้นตอนที่ 8:ป้อนค่าเซลล์ความสูงที่นับเป็นอาร์กิวเมนต์ความสูงในฟังก์ชัน OFFSET
- เขียนสูตรต่อไปนี้เพื่อเพิ่ม ความสูง
=OFFSET($E$4,1,MATCH($B$5,$E$4:$G$4,0)-1,C13,1)
ขั้นตอนที่ 9:คัดลอกสูตร
- กด Ctrl + ค เพื่อคัดลอกสูตร
=OFFSET($E$4,1,MATCH($B$5,$E$4:$G$4,0)-1,C13,1)
ขั้นตอนที่ 10:วางสูตร
- วางสูตรใน การตรวจสอบข้อมูล ที่มา
=OFFSET($E$4,1,MATCH($B$5,$E$4:$G$4,0)-1,C13,1)
- สุดท้าย กด Enter เพื่อดูการเปลี่ยนแปลง
- ด้วยเหตุนี้ ค่ารายการแบบหล่นลงของคุณจะเปลี่ยนไปตามค่าของเซลล์อื่น
- ทำการเปลี่ยนแปลงค่าของเซลล์ ไบรอัน ถึง จูเลียน่า และได้รับชื่อผลิตภัณฑ์จำหน่ายโดย Juliana .
อ่านเพิ่มเติม: วิธีสร้างรายการจากช่วงใน Excel (3 วิธี)
การอ่านที่คล้ายกัน
- วิธีการสร้างรายการดรอปดาวน์ขึ้นกับหลายคำใน Excel
- การสร้างตัวกรองแบบเลื่อนลงเพื่อดึงข้อมูลตามการเลือกใน Excel
- วิธีการดึงข้อมูลตามการเลือกรายการดรอปดาวน์ใน Excel
- สร้างตัวกรอง Excel โดยใช้รายการแบบเลื่อนลงตามค่าของเซลล์
- วิธีการเพิ่มรายการลงในรายการดรอปดาวน์ใน Excel (5 วิธี)
2. ใช้ฟังก์ชัน XLOOKUP เพื่อเปลี่ยนรายการแบบหล่นลงตามค่าของเซลล์ใน Excel
หากคุณได้รับพรจาก Microsoft 365 คุณสามารถทำได้ด้วย XLOOKUP . เพียงสูตรเดียว การทำงาน. ทำตามขั้นตอนด้านล่างเพื่อดำเนินการ
ขั้นตอนที่ 1:สร้างรายการตรวจสอบข้อมูล
- จาก การตรวจสอบความถูกต้องของข้อมูล ตัวเลือก เลือก รายการ
ขั้นตอนที่ 2:พิมพ์ช่วงแหล่งที่มา
- เลือก ช่วงต้นทาง E4:G4 ในช่องต้นทาง
- จากนั้น กด Enter .
- ดังนั้น การตรวจสอบข้อมูล รายการจะปรากฏขึ้น
ขั้นตอนที่ 3:แทรกฟังก์ชัน XLOOKUP
- เลือก B5 เซลล์เป็น look_up.
=XLOOKUP(B5)
ขั้นตอนที่ 4:เลือก lookup_array
- เขียน ช่วง E4:G4 เป็น look_array .
=XLOOKUP(B5, E4:G4)
อ่านเพิ่มเติม: วิธีแก้ไขรายการดรอปดาวน์ใน Excel (แนวทางพื้นฐาน 4 วิธี)
ขั้นตอนที่ 5:ใส่ return_array
- พิมพ์ ช่วงสำหรับ ผลตอบแทน ค่า E5:G11 .
- ดังนั้น ผลิตภัณฑ์ จะกลับมาตามพนักงานขายโดยเฉพาะ .
- ตอนนี้ เลือกชื่อใดก็ได้จากรายการแบบเลื่อนลงและรับชื่อผลิตภัณฑ์
หมายเหตุ ดูให้ดีนะครับว่าในรูปข้างบน ศูนย์ แสดงเป็นช่วงที่เซลล์ว่าง . นั่นเป็นสาเหตุที่ถือว่า ศูนย์ . หากต้องการลบ ศูนย์ ทำตามขั้นตอนด้านล่าง
อ่านเพิ่มเติม: วิธีเพิ่มตัวเลือกว่างเพื่อแสดงรายการแบบเลื่อนลงใน Excel (2 วิธี)
ขั้นตอนที่ 6:ใช้ฟังก์ชัน UNIQUE
- พิมพ์สูตรต่อไปนี้ซ้อนกับ UNIQUE
=UNIQUE(XLOOKUP(B5,E4:G4,E5:G11),,TRUE)
- สุดท้าย คุณจะได้ผลลัพธ์ที่คุณต้องการ
อ่านเพิ่มเติม: ค่าที่ไม่ซ้ำในรายการดรอปดาวน์ด้วย VBA ใน Excel (คู่มือฉบับสมบูรณ์)
บทสรุป
สุดท้ายนี้ ฉันหวังว่าคุณจะเข้าใจวิธีอัปเดตรายการแบบเลื่อนลงใน Excel ขึ้นอยู่กับค่าของเซลล์ กลยุทธ์ทั้งหมดเหล่านี้ควรดำเนินการเมื่อข้อมูลของคุณได้รับการศึกษาและฝึกฝน ตรวจสอบหนังสือฝึกหัดและประยุกต์ใช้สิ่งที่คุณได้เรียนรู้ เรามุ่งมั่นที่จะนำเสนอโปรแกรมเช่นนี้ต่อไปเนื่องจากการสนับสนุนที่เอื้อเฟื้อจากคุณ
หากคุณมีคำถามใด ๆ โปรดอย่าลังเลที่จะติดต่อเรา โปรดแบ่งปันความคิดเห็นของคุณในส่วนความคิดเห็นด้านล่าง
ความเป็นเลิศ เจ้าหน้าที่จะติดต่อกลับโดยเร็วที่สุด
อยู่กับเราและเรียนรู้ต่อไป
บทความที่เกี่ยวข้อง
- วิธีการคัดลอกรายการดรอปดาวน์ตัวกรองใน Excel (5 วิธี)
- VBA เพื่อเลือกค่าจากรายการดรอปดาวน์ใน Excel (2 วิธี)
- วิธีสร้างรายการดรอปดาวน์ที่มีค่าที่ไม่ซ้ำกันใน Excel (4 วิธี)
- รายการแบบหล่นลงของ Excel ไม่ทำงาน (8 ปัญหาและวิธีแก้ไข)
- Excel Drop Down List ขึ้นอยู่กับการเลือก
- วิธีการสร้างรายการแบบหล่นลงใน Excel (อิสระและขึ้นอยู่กับ)
- อัปเดตรายการแบบเลื่อนลงอัตโนมัติใน Excel (3 วิธี)
