วิธีสร้างรายการดรอปดาวน์ใน Excel

รายการดรอปดาวน์เป็นเครื่องมือที่มีประโยชน์อย่างเหลือเชื่อที่ช่วยให้การทำงานกับข้อมูลสะดวกสบายยิ่งขึ้น ทำให้มีหลายค่าในเซลล์พร้อมกันได้ ซึ่งคุณสามารถทำงานได้เหมือนกับค่าอื่นๆ ในการเลือกรายการที่คุณต้องการเพียงคลิกที่ไอคอนลูกศรหลังจากนั้นรายการค่า uXNUMXbuXNUMXbis จะปรากฏขึ้น หลังจากเลือกเซลล์ใดเซลล์หนึ่งแล้ว เซลล์จะถูกเติมโดยอัตโนมัติ และสูตรจะถูกคำนวณใหม่โดยยึดตามเซลล์นั้น

Excel มีวิธีการต่างๆ มากมายในการสร้างเมนูแบบเลื่อนลง และนอกจากนี้ ยังช่วยให้คุณปรับแต่งรายการเหล่านั้นได้อย่างยืดหยุ่น มาวิเคราะห์วิธีการเหล่านี้ในรายละเอียดเพิ่มเติมกัน

กระบวนการสร้างรายการ

ในการสร้างเมนูป๊อปอัป ให้คลิกที่รายการเมนูตามเส้นทาง “ข้อมูล” – “การตรวจสอบข้อมูล” กล่องโต้ตอบจะเปิดขึ้นซึ่งคุณต้องค้นหาแท็บ "พารามิเตอร์" และคลิกที่แท็บหากยังไม่เคยเปิดมาก่อน มีการตั้งค่ามากมาย แต่รายการ "ประเภทข้อมูล" มีความสำคัญต่อเรา จากความหมายทั้งหมด "รายการ" เป็นความหมายที่ถูกต้อง

จำนวนวิธีการป้อนข้อมูลลงในรายการแบบผุดขึ้นค่อนข้างมาก

1. การบ่งชี้อิสระขององค์ประกอบรายการโดยคั่นด้วยเครื่องหมายอัฒภาคในฟิลด์ "แหล่งที่มา" ที่อยู่ในแท็บเดียวกันของกล่องโต้ตอบเดียวกัน
   

   

2. การระบุค่าเบื้องต้น ฟิลด์แหล่งที่มาประกอบด้วยช่วงที่มีข้อมูลที่จำเป็น
   

   

3. การระบุช่วงที่มีชื่อ วิธีการที่ซ้ำกับวิธีก่อนหน้า แต่จำเป็นต้องตั้งชื่อช่วงเบื้องต้นเท่านั้น
   

   

วิธีใดวิธีหนึ่งเหล่านี้จะให้ผลลัพธ์ที่ต้องการ มาดูวิธีการสร้างรายการดรอปดาวน์ในสถานการณ์จริงกัน

ตามข้อมูลจากรายการ

สมมติว่าเรามีตารางอธิบายชนิดของผลไม้ต่างๆ

ในการสร้างรายการในเมนูแบบเลื่อนลงตามชุดข้อมูลนี้ คุณต้องดำเนินการดังต่อไปนี้:

1. เลือกเซลล์ที่สงวนไว้สำหรับรายการในอนาคต
2. ค้นหาแท็บข้อมูลบนริบบิ้น ที่นั่นเราคลิกที่ "ตรวจสอบข้อมูล"
   

   

3. ค้นหารายการ "ประเภทข้อมูล" และเปลี่ยนค่าเป็น "รายการ"
   

   

4. ในฟิลด์ที่แสดงถึงตัวเลือก "แหล่งที่มา" ให้ป้อนช่วงที่ต้องการ โปรดทราบว่าต้องระบุการอ้างอิงแบบสัมบูรณ์เพื่อไม่ให้มีการคัดลอกรายการเมื่อคัดลอกรายการ
   

นอกจากนี้ยังมีฟังก์ชันสำหรับสร้างรายการพร้อมกันในเซลล์มากกว่าหนึ่งเซลล์ เพื่อให้บรรลุเป้าหมายนี้ คุณควรเลือกทั้งหมด และทำตามขั้นตอนเดียวกับที่อธิบายไว้ก่อนหน้านี้ อีกครั้ง คุณต้องแน่ใจว่ามีการเขียนการอ้างอิงแบบสัมบูรณ์ หากที่อยู่ไม่มีเครื่องหมายดอลลาร์ถัดจากชื่อคอลัมน์และแถว คุณต้องเพิ่มโดยกดปุ่ม F4 จนกว่าเครื่องหมาย $ จะอยู่ถัดจากชื่อคอลัมน์และแถว

ด้วยการบันทึกข้อมูลด้วยตนเอง

ในสถานการณ์ข้างต้น รายการถูกเขียนโดยเน้นช่วงที่ต้องการ นี่เป็นวิธีที่สะดวก แต่บางครั้งจำเป็นต้องบันทึกข้อมูลด้วยตนเอง ซึ่งจะทำให้สามารถหลีกเลี่ยงความซ้ำซ้อนของข้อมูลในสมุดงานได้

สมมติว่าเรากำลังเผชิญกับงานสร้างรายการที่มีสองทางเลือกที่เป็นไปได้: ใช่และไม่ใช่ เพื่อให้บรรลุภารกิจจำเป็นต้อง:

1. คลิกที่เซลล์สำหรับรายการ
2. เปิด "ข้อมูล" และพบส่วน "การตรวจสอบข้อมูล" ที่เราคุ้นเคย
   

   

3. เลือกประเภท "รายการ" อีกครั้ง
   

   

4. ที่นี่คุณต้องป้อน "ใช่; ไม่มี” เป็นแหล่งที่มา เราเห็นว่ามีการป้อนข้อมูลด้วยตนเองโดยใช้เครื่องหมายอัฒภาคสำหรับการแจงนับ

หลังจากคลิกตกลงเราได้ผลลัพธ์ดังต่อไปนี้

ถัดไป โปรแกรมจะสร้างเมนูแบบเลื่อนลงในเซลล์ที่เหมาะสมโดยอัตโนมัติ ข้อมูลทั้งหมดที่ผู้ใช้ระบุเป็นรายการในรายการป๊อปอัป กฎสำหรับการสร้างรายการในหลายเซลล์จะคล้ายกับกฎก่อนหน้า โดยมีข้อยกเว้นเพียงอย่างเดียวคือคุณต้องระบุข้อมูลด้วยตนเองโดยใช้เครื่องหมายอัฒภาค

การสร้างรายการดรอปดาวน์โดยใช้ฟังก์ชัน OFFSET

นอกจากวิธีการแบบคลาสสิกแล้ว ยังใช้ฟังก์ชันได้อีกด้วย การกำจัดเพื่อสร้างเมนูแบบเลื่อนลง

มาเปิดแผ่นกันเถอะ

ในการใช้ฟังก์ชันสำหรับรายการดรอปดาวน์ คุณต้องดำเนินการดังต่อไปนี้:

1. เลือกเซลล์ที่น่าสนใจที่คุณต้องการวางรายการในอนาคต
2. เปิดแท็บ "ข้อมูล" และหน้าต่าง "การตรวจสอบข้อมูล" ตามลำดับ
   

   

3. ตั้งค่า "รายการ" สิ่งนี้ทำในลักษณะเดียวกับตัวอย่างก่อนหน้า ในที่สุดใช้สูตรต่อไปนี้: =ออฟเซ็ต(A$2$;0;0;5). เราป้อนโดยระบุเซลล์ที่จะใช้เป็นอาร์กิวเมนต์

จากนั้นโปรแกรมจะสร้างเมนูพร้อมรายการผลไม้

ไวยากรณ์สำหรับสิ่งนี้คือ:

=OFFSET(อ้างอิง,line_offset,column_offset,[height],[width])

เราจะเห็นว่าฟังก์ชันนี้มี 5 อาร์กิวเมนต์ ขั้นแรกให้ระบุที่อยู่เซลล์แรกที่จะออฟเซ็ต อาร์กิวเมนต์สองอันถัดไปจะระบุจำนวนแถวและคอลัมน์ที่จะออฟเซ็ต เมื่อพูดถึงเรา อาร์กิวเมนต์ความสูงคือ 5 เพราะแสดงถึงความสูงของรายการ 

รายการแบบเลื่อนลงใน Excel พร้อมการแทนที่ข้อมูล (+ โดยใช้ฟังก์ชัน OFFSET)

ในกรณีที่กำหนด การกำจัด อนุญาตให้สร้างเมนูป๊อปอัปที่อยู่ในช่วงที่กำหนด ข้อเสียของวิธีนี้คือหลังจากเพิ่มรายการแล้ว คุณจะต้องแก้ไขสูตรด้วยตัวเอง

ในการสร้างรายการแบบไดนามิกที่รองรับการป้อนข้อมูลใหม่ คุณต้อง:

1. เลือกเซลล์ที่สนใจ
2. ขยายแท็บ "ข้อมูล" และคลิกที่ "การตรวจสอบข้อมูล"
3. ในหน้าต่างที่เปิดขึ้น ให้เลือกรายการ "รายการ" อีกครั้งและระบุสูตรต่อไปนี้เป็นแหล่งข้อมูล: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. คลิก OK

ประกอบด้วยฟังก์ชัน COUNTIFเพื่อกำหนดจำนวนเซลล์ที่เติมได้ทันที (แม้ว่าจะมีจำนวนการใช้งานที่มากกว่ามาก แต่เราเพียงแค่เขียนไว้ที่นี่เพื่อวัตถุประสงค์เฉพาะ)

เพื่อให้สูตรทำงานได้ตามปกติ จำเป็นต้องติดตามว่ามีเซลล์ว่างบนเส้นทางของสูตรหรือไม่ พวกเขาไม่ควรจะเป็น

รายการดรอปดาวน์พร้อมข้อมูลจากชีตอื่นหรือไฟล์ Excel

วิธีการแบบคลาสสิกจะไม่ทำงานหากคุณต้องการรับข้อมูลจากเอกสารอื่นหรือแม้แต่แผ่นงานที่อยู่ในไฟล์เดียวกัน สำหรับสิ่งนี้ ฟังก์ชันนี้ถูกใช้ ทางอ้อมซึ่งช่วยให้คุณสามารถป้อนลิงก์ไปยังเซลล์ที่อยู่ในชีตอื่นหรือโดยทั่วไปในรูปแบบที่ถูกต้อง นั่นคือไฟล์ คุณต้องทำสิ่งต่อไปนี้:

1. เปิดใช้งานเซลล์ที่เราวางรายการ
2. เราเปิดหน้าต่างที่เรารู้อยู่แล้ว ในตำแหน่งเดียวกับที่เราระบุแหล่งที่มาสำหรับช่วงอื่นๆ ก่อนหน้านี้ สูตรจะถูกระบุในรูปแบบ =ทางอ้อม(“[List1.xlsx]Sheet1!$A$1:$A$9”). โดยปกติ แทนที่จะใส่รายชื่อ 1 และชีต 1 คุณสามารถแทรกชื่อหนังสือและชีตตามลำดับได้ 

ความสนใจ! ชื่อไฟล์อยู่ในวงเล็บเหลี่ยม ในกรณีนี้ Excel จะไม่สามารถใช้ไฟล์ที่ปิดอยู่เป็นแหล่งข้อมูลได้

ควรสังเกตด้วยว่าชื่อไฟล์นั้นเหมาะสมก็ต่อเมื่อเอกสารที่จำเป็นนั้นอยู่ในโฟลเดอร์เดียวกับที่จะแทรกรายการ ถ้าไม่เช่นนั้นคุณต้องระบุที่อยู่ของเอกสารนี้ให้ครบถ้วน

การสร้างรายการแบบหล่นลงที่ขึ้นต่อกัน

รายการที่ขึ้นต่อกันคือรายการที่มีเนื้อหาได้รับผลกระทบจากตัวเลือกของผู้ใช้ในรายการอื่น สมมติว่ามีตารางเปิดอยู่ข้างหน้าเราซึ่งมีสามช่วง ซึ่งแต่ละช่วงได้รับชื่อแล้ว

คุณต้องทำตามขั้นตอนเหล่านี้เพื่อสร้างรายการที่ได้รับผลกระทบจากตัวเลือกที่เลือกในรายการอื่น

1. สร้างรายการที่ 1 ด้วยชื่อช่วง
   

   

2. ที่จุดเริ่มต้นแหล่งที่มา ตัวบ่งชี้ที่จำเป็นจะถูกเน้นทีละตัว
   

   

3. สร้างรายการที่ 2 ขึ้นอยู่กับชนิดของพืชที่บุคคลเลือก หรือหากคุณระบุต้นไม้ในรายการแรก ข้อมูลในรายการที่สองจะเป็น "ต้นโอ๊ค ฮอร์นบีม เกาลัด" และอื่นๆ จำเป็นต้องเขียนสูตรในตำแหน่งอินพุตของแหล่งข้อมูล =ทางอ้อม(E3). E3 – เซลล์ที่มีชื่อของช่วง 1.=INDIRECT(E3) E3 – เซลล์ที่มีชื่อของรายการ 1

ตอนนี้ทุกอย่างพร้อมแล้ว

วิธีการเลือกหลายค่าจากรายการแบบหล่นลง?

บางครั้งอาจเลือกค่าเพียงค่าเดียวไม่ได้ ดังนั้นต้องเลือกมากกว่าหนึ่งค่า จากนั้น คุณต้องเพิ่มมาโครลงในโค้ดของเพจ การใช้คีย์ผสม Alt + F11 จะเป็นการเปิด Visual Basic Editor และใส่รหัสที่นั่น

แผ่นงานย่อยส่วนตัว_เปลี่ยน (ByVal Target As Range)

    เกี่ยวกับข้อผิดพลาดต่อไป

    ถ้าไม่ตัดกัน(เป้าหมาย, ช่วง(«Е2:Е9»)) ก็ไม่มีอะไรและ Target.Cells.Count = 1 แล้ว

        Application.EnableEvents = เท็จ

        ถ้าเลน (Target.Offset (0, 1)) = 0 แล้ว

            Target.Offset (0, 1) = เป้าหมาย

        อื่น

            Target.End (xlToRight) .Offset (0, 1) = เป้าหมาย

        End If

        Target.ClearContents

        Application.EnableEvents = จริง

    End If

ย่อยสิ้นสุด 

เพื่อให้เนื้อหาของเซลล์แสดงด้านล่าง เราแทรกโค้ดต่อไปนี้ลงในตัวแก้ไข

แผ่นงานย่อยส่วนตัว_เปลี่ยน (ByVal Target As Range)

    เกี่ยวกับข้อผิดพลาดต่อไป

    ถ้าไม่ตัดกัน(เป้าหมาย ช่วง(«Н2:К2»)) ก็ไม่มีอะไรและ Target.Cells.Count = 1 แล้ว

        Application.EnableEvents = เท็จ

        ถ้าเลน (Target.Offset (1, 0)) = 0 แล้ว

            Target.Offset (1, 0) = เป้าหมาย

        อื่น

            Target.End (xlDown) .Offset (1, 0) = เป้าหมาย

        End If

        Target.ClearContents

        Application.EnableEvents = จริง

    End If

ย่อยสิ้นสุด

และสุดท้าย โค้ดนี้ใช้เพื่อเขียนในเซลล์เดียว

แผ่นงานย่อยส่วนตัว_เปลี่ยน (ByVal Target As Range)

    เกี่ยวกับข้อผิดพลาดต่อไป

    ถ้าไม่ตัดกัน(เป้าหมาย ช่วง(«C2:C5»)) ก็ไม่มีอะไรและ Target.Cells.Count = 1 แล้ว

        Application.EnableEvents = เท็จ

        newVal = เป้าหมาย

        แอปพลิเคชันเลิกทำ

        oldval = เป้าหมาย

        ถ้า Len (oldval) <> 0 และ oldval <> newVal แล้ว

            เป้าหมาย = เป้าหมาย & «,» & newVal

        อื่น

            เป้าหมาย = ใหม่Val

        End If

        ถ้า Len (newVal) = 0 แล้ว Target.ClearContents

        Application.EnableEvents = จริง

    End If

ย่อยสิ้นสุด

ช่วงสามารถแก้ไขได้

จะสร้างรายการดรอปดาวน์ด้วยการค้นหาได้อย่างไร?

ในกรณีนี้ คุณต้องใช้รายการประเภทอื่นก่อน แท็บ "นักพัฒนาซอฟต์แวร์" จะเปิดขึ้น หลังจากนั้นคุณต้องคลิกหรือแตะ (หากสัมผัสหน้าจอ) บนองค์ประกอบ "แทรก" - "ActiveX" มีกล่องคอมโบ คุณจะได้รับแจ้งให้วาดรายการนี้ หลังจากนั้นจะถูกเพิ่มลงในเอกสาร

นอกจากนี้ยังมีการกำหนดค่าผ่านคุณสมบัติ โดยจะมีการระบุช่วงในตัวเลือก ListFillRange เซลล์ที่แสดงค่าที่ผู้ใช้กำหนดได้รับการกำหนดค่าโดยใช้ตัวเลือก LinkedCell ถัดไป คุณเพียงแค่ต้องจดอักขระตัวแรก เนื่องจากโปรแกรมจะแนะนำค่าที่เป็นไปได้โดยอัตโนมัติ

รายการแบบเลื่อนลงพร้อมการแทนที่ข้อมูลอัตโนมัติ

นอกจากนี้ยังมีฟังก์ชันที่ข้อมูลจะถูกแทนที่โดยอัตโนมัติหลังจากเพิ่มลงในช่วงแล้ว การทำเช่นนี้ทำได้ง่าย:

1. สร้างชุดเซลล์สำหรับรายการในอนาคต ในกรณีของเรา นี่คือชุดสี เราเลือกมัน
   

   

2. ต่อไปจะต้องจัดรูปแบบเป็นตาราง คุณต้องคลิกปุ่มชื่อเดียวกันและเลือกสไตล์ตาราง
   

   

   

ถัดไป คุณต้องยืนยันช่วงนี้โดยกดปุ่ม "ตกลง"

เราเลือกตารางผลลัพธ์และตั้งชื่อผ่านช่องป้อนข้อมูลที่ด้านบนของคอลัมน์ A

เพียงเท่านี้ มีตาราง และสามารถใช้เป็นพื้นฐานสำหรับรายการแบบหล่นลง ซึ่งคุณต้องการ:

1. เลือกเซลล์ที่มีรายการอยู่
2. เปิดกล่องโต้ตอบการตรวจสอบข้อมูล
   

   

3. เราตั้งค่าประเภทข้อมูลเป็น "รายการ" และในฐานะค่าที่เราให้ชื่อตารางผ่านเครื่องหมาย =
   

   

   

ทุกอย่าง เซลล์พร้อมแล้ว และชื่อของสีก็แสดงอยู่ในนั้น ตามที่เราต้องการในตอนแรก ตอนนี้คุณสามารถเพิ่มตำแหน่งใหม่ได้ง่ายๆ โดยเขียนลงในเซลล์ที่อยู่ด้านล่างเล็กน้อยทันทีหลังจากตำแหน่งสุดท้าย

นี่คือข้อดีของตาราง ซึ่งช่วงจะเพิ่มขึ้นโดยอัตโนมัติเมื่อมีการเพิ่มข้อมูลใหม่ ดังนั้น นี่เป็นวิธีที่สะดวกที่สุดในการเพิ่มรายการ

จะคัดลอกรายการแบบหล่นลงได้อย่างไร

ในการคัดลอก ก็เพียงพอแล้วที่จะใช้คีย์ผสม Ctrl + C และ Ctrl + V ดังนั้นรายการแบบหล่นลงจะถูกคัดลอกพร้อมกับการจัดรูปแบบ หากต้องการลบการจัดรูปแบบ คุณต้องใช้การวางแบบพิเศษ (ในเมนูบริบท ตัวเลือกนี้จะปรากฏขึ้นหลังจากคัดลอกรายการ) ซึ่งจะมีการตั้งค่าตัวเลือก "เงื่อนไขเกี่ยวกับค่า"

เลือกเซลล์ทั้งหมดที่มีรายการแบบหล่นลง

เพื่อให้งานนี้สำเร็จ คุณต้องใช้ฟังก์ชัน "เลือกกลุ่มเซลล์" ในกลุ่ม "ค้นหาและเลือก"

หลังจากนั้น กล่องโต้ตอบจะเปิดขึ้น ซึ่งคุณควรเลือกรายการ "ทั้งหมด" และ "สิ่งเดียวกัน" ในเมนู "การตรวจสอบข้อมูล" รายการแรกจะเลือกรายการทั้งหมด และรายการที่สองจะเลือกเฉพาะรายการที่คล้ายกับบางรายการเท่านั้น