ป้อนข้อมูลโดยใช้รายการแบบเลื่อนลง สร้างรายการแบบเลื่อนลง การเลือกหลายค่าจากรายการแบบเลื่อนลงของ Excel

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

เรามาเริ่มตัวอย่างง่ายๆ กันด้วยว่าคุณสามารถสร้างรายการดรอปดาวน์ที่เชื่อมโยง (หรือขึ้นอยู่กับ) ใน Excel ได้อย่างไร ในเซลล์ B1เราจะเลือกประเทศและในเซลล์ บี2– เมืองที่เป็นของตน ดังตัวอย่าง:

ก่อนอื่นคุณต้องสร้างฐานข้อมูล ในแผ่นงานที่สอง ฉันป้อนรายชื่อประเทศที่ฉันต้องการให้ผู้ใช้เลือกในรายการแบบเลื่อนลงรายการแรก และในคอลัมน์ที่อยู่ติดกัน ฉันระบุดัชนีตัวเลขที่สอดคล้องกับหนึ่งในรายชื่อเมือง รายชื่อเมืองจะอยู่ทางด้านขวาในคอลัมน์ ดี, เอฟและ ชม. ตัวอย่างเช่นถัดจาก ฝรั่งเศสคือดัชนี 2 ซึ่งสอดคล้องกับรายชื่อเมือง 2 . ต่อมาคุณจะเห็นว่าดัชนีนี้จะถูกนำไปใช้อย่างไร

หากคุณทำงานใน Excel 2010 คุณสามารถสร้างแผ่นงานต้นฉบับในสมุดงานแยกต่างหากได้ หากคุณมี Excel เวอร์ชัน 2003 และวางแผนที่จะใช้ช่วงที่มีชื่อ ค่าควรอยู่ในสมุดงานเดียวกันหรือในแผ่นงานอื่น

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

คลิกปุ่ม ใหม่(ใหม่) เพื่อเพิ่มช่วงที่มีชื่อใหม่ กล่องโต้ตอบจะเปิดขึ้น ชื่อใหม่(การสร้างชื่อ).

ในสนาม ชื่อ(ชื่อ) ป้อนชื่อ ประเทศสำหรับช่วงที่ตั้งชื่อครั้งแรกของเราและในสนาม อ้างถึง(ช่วง) เลือกรายการประเทศที่เก็บไว้:

Sheet3!$A$3:$A$5

ช่วงที่มีเมืองสามารถตั้งชื่อได้ในลักษณะเดียวกันทุกประการ

ตอนนี้เราสามารถสร้างรายการแบบหล่นลงในเซลล์ที่เราวางแผนจะเลือกข้อมูลได้ เลือกเซลล์ B1(ในนั้นเราจะเลือกประเทศ) เปิดแท็บ ข้อมูล(ข้อมูล) คลิก การตรวจสอบข้อมูล(การตรวจสอบข้อมูล) จากนั้นเลือกจากเมนูแบบเลื่อนลง การตรวจสอบข้อมูล(การตรวจสอบข้อมูล)

กล่องโต้ตอบจะเปิดขึ้น การตรวจสอบข้อมูล(การตรวจสอบค่าที่ป้อน)

เราต้องการให้รายการตัวเลือกแก่ผู้ใช้ในฟิลด์นี้ อนุญาต(ชนิดข้อมูล) เลือก รายการ(รายการ). นี่จะเปิดใช้งานฟิลด์ แหล่งที่มา(ที่มา) โดยคุณจะต้องระบุชื่อช่วงกับประเทศ ป้อน “=ประเทศ” ในช่องนี้แล้วคลิก ตกลง. ตอนนี้เราจำเป็นต้องสร้างเมนูแบบเลื่อนลงที่สองเพื่อให้ผู้ใช้สามารถเลือกเมืองได้ เราจะวางรายการแบบเลื่อนลงในเซลล์ บี2. และตอนนี้ให้ความสนใจ - โฟกัส! เราจำเป็นต้องตรวจสอบเนื้อหาของเซลล์ชื่อประเทศ (เซลล์ B1) เพื่อให้ได้ดัชนีที่สอดคล้องกับฐานข้อมูลเมือง หากผู้ใช้เลือก โปรตุเกสจากนั้นเราจะต้องเข้าถึงฐานด้วยดัชนี 3 ซึ่งเก็บชื่อเมืองในประเทศโปรตุเกส เราจะใช้ฟังก์ชัน วีลุคอัพ(VLOOKUP) เพื่อค้นหาค่าจากเซลล์ B1ในตารางที่มีชื่อประเทศ เมื่อทราบดัชนีแล้ว เราจะเลือกรายการที่จะกลายเป็นแหล่งข้อมูลสำหรับรายการดรอปดาวน์ที่สองของเรา เมื่อต้องการทำเช่นนี้ เราเขียนสูตรต่อไปนี้:

เลือก(VLOOKUP(B1,Sheet3!$A$3:$B$5,2,FALSE),อังกฤษ,ฝรั่งเศส,โปรตุเกส)
=SELECT(VLOOKUP(B1,Sheet3!$A$3:$B$5,2,FALSE),อังกฤษ,ฝรั่งเศส,โปรตุเกส)

สูตรนี้ช่วยอะไร? มันค้นหาค่าจากเซลล์ B1ในรายชื่อประเทศและส่งกลับดัชนีที่เกี่ยวข้องซึ่งฟังก์ชันจะใช้ เลือก(SELECT) เพื่อเลือกช่วงที่ตั้งชื่อเป็นที่ 1, 2 หรือ 3

นี่คือลักษณะรายการแบบเลื่อนลงที่สองของเราจะมีลักษณะดังนี้:

รายการแบบหล่นลงใน Microsoft Excel เป็นสิ่งที่ขาดไม่ได้เมื่อสร้างตารางขนาดใหญ่และทำงานกับฐานข้อมูล ความสะดวกของเครื่องมือนี้คืออะไร?

เหตุใดจึงต้องมีรายการดังกล่าว?

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

ในบทความฉันจะบอกคุณ วิธีสร้างรายการแบบหล่นลงในเซลล์ใน Excel e และวิธีการทำงานกับมัน

การก่อตัวของรายการแบบหล่นลง

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

ผลิตภัณฑ์ทั้งหมดที่อยู่ในตารางจะต้องจัดอยู่ในหมวดหมู่ "เสื้อผ้า" หากต้องการสร้างรายการแบบเลื่อนลงสำหรับรายการนี้ คุณจะต้องดำเนินการดังต่อไปนี้:

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

วิธีเพิ่มค่าให้กับรายการ

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

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

ในบทความนี้ เราจะดูวิธีสร้างรายการแบบเลื่อนลงประเภทต่างๆ ในตาราง Excel

สร้างรายการแบบเลื่อนลงอย่างง่าย

ในการทำเช่นนี้ในเซลล์ A1:A7 เราป้อนข้อมูลที่จะแสดงในรายการ ตอนนี้เรามาเลือกเซลล์ที่เราจะสร้างรายการแบบเลื่อนลง - B2

ไปที่แท็บ "ข้อมูล" และคลิกที่ปุ่ม "ตรวจสอบข้อมูล"

บนแท็บ "พารามิเตอร์" ในช่อง "ประเภทข้อมูล" เลือก "รายการ" คุณสามารถป้อนค่าในช่องแหล่งที่มาได้หลายวิธี:

1 – ป้อนค่าสำหรับรายการด้วยตนเอง คั่นด้วยเครื่องหมายอัฒภาค

2 – ระบุช่วงของเซลล์ที่ป้อนข้อมูลสำหรับรายการดรอปดาวน์

3 – เลือกเซลล์ที่มีชื่อ คลิกขวาที่เซลล์เหล่านั้นแล้วเลือก “กำหนดชื่อ” จากเมนู

เลือกเซลล์ B2 แล้วใส่ “=” ในช่อง “แหล่งที่มา” จากนั้นเขียนชื่อที่สร้างขึ้น

ดังนั้นเราจึงสร้างรายการดรอปดาวน์ง่ายๆ ใน Excel

หากคุณมีส่วนหัวของคอลัมน์ และคุณต้องเติมค่าแต่ละแถว ให้เลือกไม่ใช่เซลล์เดียว แต่เลือกช่วงของเซลล์ - B2:B9 จากนั้นคุณสามารถเลือกค่าที่ต้องการในแต่ละเซลล์จากรายการแบบเลื่อนลง

การเพิ่มค่าให้กับรายการดรอปดาวน์ - รายการไดนามิก

ในกรณีนี้ เราจะเพิ่มค่าในช่วงที่ต้องการ และค่าเหล่านั้นจะถูกเพิ่มลงในรายการแบบเลื่อนลงโดยอัตโนมัติ

เลือกช่วงของเซลล์ - D1:D8 จากนั้นบนแท็บ "หน้าแรก" คลิก "จัดรูปแบบเป็นตาราง" และเลือกสไตล์ใดก็ได้

ยืนยันตำแหน่งของข้อมูลและทำเครื่องหมายที่ช่อง "ตารางพร้อมส่วนหัว"

ที่ด้านบนเราเขียนชื่อของตาราง - "พนักงาน" และกรอกข้อมูลลงในนั้น

เลือกเซลล์ที่จะแสดงรายการแบบเลื่อนลงและคลิกที่ปุ่ม "ตรวจสอบข้อมูล" ในหน้าต่างถัดไป ในช่อง "แหล่งที่มา" ให้เขียนดังนี้: =INDIRECT("Table1") ฉันมีตารางหนึ่งตารางบนแผ่นงาน ดังนั้นฉันจึงเขียน "Table1" ถ้ามีตารางที่สอง - "Table2" และอื่นๆ

ตอนนี้เรามาเพิ่มชื่อพนักงานใหม่ในรายการของเรา: Ira ปรากฏในรายการแบบเลื่อนลง หากเราลบชื่อใดๆ ออกจากตาราง ชื่อนั้นก็จะถูกลบออกจากรายการด้วย

รายการแบบเลื่อนลงพร้อมค่าจากชีตอื่น

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

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

ไปที่แผ่นงาน 1 วางเคอร์เซอร์ในช่อง "แหล่งที่มา" และเลือกช่วงของเซลล์ที่ต้องการ

ตอนนี้คุณสามารถเพิ่มชื่อในแผ่นงาน 1 ได้ โดยชื่อเหล่านั้นจะถูกเพิ่มลงในรายการแบบเลื่อนลงในแผ่นงาน 2

การสร้างรายการดรอปดาวน์ที่ขึ้นต่อกัน

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

เราเรียกชื่อแรกว่า "ชื่อ" คนที่สอง - "นามสกุล" คนที่สาม - "พ่อ"

เรามาสร้างช่วงอื่นที่จะเขียนชื่อที่กำหนด เรียกมันว่า "พนักงาน"

เราสร้างรายการแบบเลื่อนลงรายการแรกซึ่งจะประกอบด้วยชื่อของช่วง เลือกเซลล์ E1 และบนแท็บ "ข้อมูล" เลือก "การตรวจสอบข้อมูล"

ในช่อง "ประเภทข้อมูล" ให้เลือก "รายการ" ส่วนในช่องแหล่งที่มา ให้ป้อน "=พนักงาน" หรือเลือกช่วงของเซลล์ที่ได้รับการกำหนดชื่อ

รายการแบบเลื่อนลงรายการแรกได้ถูกสร้างขึ้นแล้ว ตอนนี้ในเซลล์ F2 เราจะสร้างรายการที่สอง ซึ่งจะขึ้นอยู่กับรายการแรก ถ้าเราเลือก “ชื่อ” ในอันแรก รายชื่อจะแสดงในอันที่สอง ถ้าเราเลือก “นามสกุล” รายชื่อนามสกุลก็จะปรากฏขึ้น

เลือกเซลล์และคลิกที่ปุ่ม "ตรวจสอบข้อมูล" ในช่อง "ประเภทข้อมูล" ให้เลือก "รายการ" โดยในช่องแหล่งที่มา ให้ป้อนข้อมูลต่อไปนี้: =INDIRECT($E$1) ที่นี่ E1 คือเซลล์ที่มีรายการดรอปดาวน์แรก

การใช้หลักการนี้ คุณสามารถสร้างรายการดรอปดาวน์ที่ขึ้นต่อกันได้

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

ตอนนี้คุณรู้วิธีสร้างรายการแบบหล่นลงใน Excel แล้ว

วิธีสร้างรายการแบบเลื่อนลงที่ประกอบด้วยหลายเซลล์พร้อมกัน (เช่น เพื่อให้ชื่อมีค่าใช้จ่าย)

ขอบคุณ ทุกอย่างทำงานได้ดี

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

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

ตัวอย่างการสร้างรายการแบบหล่นลงในเซลล์ Excel

ตัวอย่างของการใช้รายการแบบเลื่อนลงที่ขึ้นต่อกันเพื่อสร้างแบบฟอร์มที่สะดวกสำหรับการกรอกเอกสารที่ผู้ขายสั่งซื้อสินค้า จากการเลือกสรรทั้งหมด พวกเขาต้องเลือกผลิตภัณฑ์ที่พวกเขาจะขาย

ผู้ขายแต่ละรายจะระบุกลุ่มผลิตภัณฑ์ก่อน จากนั้นจึงระบุผลิตภัณฑ์เฉพาะจากกลุ่มนี้ แบบฟอร์มจะต้องมีชื่อเต็มของกลุ่มและดัชนีผลิตภัณฑ์เฉพาะ เนื่องจากการพิมพ์ด้วยมืออาจใช้เวลานานเกินไป (และน่ารำคาญ) ฉันจึงคิดวิธีแก้ปัญหาที่ง่ายและรวดเร็วขึ้นมา - ดรอปดาวน์ 2 รายการ

รายการแรกคือรายการหมวดหมู่ผลิตภัณฑ์ทั้งหมด รายการที่สองคือรายการผลิตภัณฑ์ทั้งหมดในหมวดหมู่ที่เลือก ดังนั้นฉันจึงสร้างรายการแบบเลื่อนลงโดยขึ้นอยู่กับการเลือกในรายการก่อนหน้า (ที่นี่คุณจะพบเนื้อหาเกี่ยวกับวิธีสร้างรายการแบบเลื่อนลงที่ขึ้นต่อกันสองรายการ)

ผู้ใช้เทมเพลตงบประมาณบ้านต้องการได้รับผลลัพธ์เดียวกันกับที่พวกเขาต้องการหมวดหมู่และหมวดหมู่ย่อยของค่าใช้จ่าย ตัวอย่างของข้อมูลอยู่ในภาพด้านล่าง:

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

รายการหมวดหมู่และหมวดหมู่ย่อยในรายการแบบเลื่อนลงที่ขึ้นอยู่กับ Excel

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

และผลลัพธ์สุดท้ายจะเป็นดังนี้:

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

เพื่อให้บรรลุเป้าหมายนี้ เราจำเป็นต้องสร้างตารางข้อมูลที่แตกต่างจากการสร้างรายการแบบเลื่อนลงรายการเดียว ตารางควรมีลักษณะเช่นนี้ (ช่วง G2:H15):

สเปรดชีตแหล่งที่มาของ Excel ที่ใช้งานได้

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

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

โอเคถ้าอย่างนั้น. ตอนนี้ ผมจะอธิบายขั้นตอนในการสร้างรายการดรอปดาวน์ที่ขึ้นต่อกันทีละรายการ

1. ชื่อช่วงเซลล์

นี่เป็นขั้นตอนเสริม หากไม่มีขั้นตอนนี้ เราก็สามารถจัดการได้โดยไม่มีปัญหาใดๆ อย่างไรก็ตาม ฉันชอบใช้ชื่อเพราะทำให้ทั้งเขียนและอ่านสูตรได้ง่ายขึ้นมาก

มากำหนดชื่อให้กับทั้งสองช่วงกัน รายการหมวดหมู่ทั้งหมดและรายการการทำงานของหมวดหมู่ ช่วงเหล่านี้จะเป็น A3:A5 (รายการหมวดหมู่ในแผ่นงานสีเขียวในภาพแรก) และ G3:G15 (รายการหมวดหมู่ที่ซ้ำกันในแผ่นงานสีม่วง)

ในการตั้งชื่อรายการหมวดหมู่:

เลือกช่วง A3:A5
ในกล่องชื่อ (กล่องทางด้านซ้ายของแถบสูตร) ให้ป้อนชื่อ "ประเภท"
ยืนยันด้วยปุ่ม Enter

ดำเนินการแบบเดียวกันสำหรับช่วงรายการงานหมวดหมู่ G3:G15 ซึ่งคุณสามารถเรียกว่า "Work_List" เราจะใช้ช่วงนี้ในสูตร

2. สร้างรายการแบบเลื่อนลงสำหรับหมวดหมู่

มันจะง่าย:

เลือกเซลล์ที่คุณต้องการวางรายการ ในกรณีของฉันมันคือ A12
จากเมนู DATA ให้เลือกเครื่องมือตรวจสอบข้อมูล หน้าต่าง "ตรวจสอบค่าอินพุต" จะปรากฏขึ้น
เลือกรายการเป็นชนิดข้อมูล
เป็นแหล่งที่มา ให้ป้อน: =Category (รูปภาพด้านล่าง)
ยืนยันด้วยตกลง

ผลลัพธ์จะเป็นดังนี้:

รายการแบบเลื่อนลงสำหรับหมวดหมู่

3. สร้างรายการดรอปดาวน์ที่ขึ้นต่อกันสำหรับหมวดหมู่ย่อย

ตอนนี้มันคงจะสนุก เรารู้วิธีสร้างรายการ - เราเพิ่งสร้างสำหรับหมวดหมู่หนึ่งๆ มีคำถามเดียว: “ฉันจะบอก Excel ให้เลือกเฉพาะค่าที่มีไว้สำหรับหมวดหมู่เฉพาะได้อย่างไร” ดังที่คุณคงเดาได้ ฉันจะใช้แผ่นงานที่นี่และแน่นอนว่าต้องใช้สูตรด้วย

เริ่มจากสิ่งที่เรารู้อยู่แล้วนั่นคือโดยการสร้างรายการแบบหล่นลงในเซลล์ B12 ให้เลือกเซลล์นั้นแล้วคลิกการตรวจสอบข้อมูล/ข้อมูล จากนั้นประเภทข้อมูลคือรายการ

ในแหล่งที่มาของรายการ ให้ป้อนสูตรต่อไปนี้:

มุมมองของหน้าต่าง "การตรวจสอบค่าอินพุต":

การตรวจสอบความถูกต้องของค่าอินพุตสำหรับหมวดหมู่ย่อยในรายการแบบเลื่อนลงที่ขึ้นต่อกัน

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

ในตัวอย่างของเรา ช่วงจะย้ายไปตามคอลัมน์ประเภทย่อยในเวิร์กชีต (G2:H15) เราจะเริ่มย้ายจากเซลล์ H2 ซึ่งเป็นอาร์กิวเมนต์แรกของฟังก์ชันของเราด้วย ในสูตร เซลล์ H2 เขียนเป็นการอ้างอิงแบบสัมบูรณ์เนื่องจากฉันคิดว่าเราจะใช้รายการแบบเลื่อนลงในหลายเซลล์

เนื่องจากแผ่นงานถูกจัดเรียงตามประเภท ช่วงที่ควรเป็นแหล่งที่มาของรายการดรอปดาวน์จะเริ่มต้นจากตำแหน่งที่หมวดหมู่ที่เลือกปรากฏเป็นอันดับแรก ตัวอย่างเช่น สำหรับหมวดหมู่อาหาร เราต้องการแสดงช่วง H6:H11 สำหรับการขนส่ง - ช่วง H12:H15 เป็นต้น โปรดสังเกตว่าเรากำลังเคลื่อนไปตามคอลัมน์ H ตลอดเวลา และสิ่งเดียวที่เปลี่ยนแปลงคือจุดเริ่มต้น ของช่วงและความสูง (นั่นคือจำนวนองค์ประกอบในรายการ)

จุดเริ่มต้นของช่วงจะถูกย้ายโดยสัมพันธ์กับเซลล์ H2 โดยจำนวนเซลล์ที่อยู่ด้านล่าง (ในจำนวน) เท่ากับหมายเลขตำแหน่งของหมวดหมู่ที่เกิดขึ้นครั้งแรกในคอลัมน์หมวดหมู่ จะเข้าใจได้ง่ายขึ้นด้วยตัวอย่าง: ช่วงสำหรับหมวดหมู่อาหารถูกย้ายลงไป 4 เซลล์โดยสัมพันธ์กับเซลล์ H2 (เริ่มจาก 4 เซลล์จาก H2) ในเซลล์ที่ 4 ของคอลัมน์หมวดหมู่ย่อย (ไม่รวมส่วนหัว เนื่องจากเรากำลังพูดถึงช่วงที่เรียกว่า Work_List) จะมีคำว่าโภชนาการ (ปรากฏครั้งแรก) เราใช้ข้อเท็จจริงนี้เพื่อกำหนดจุดเริ่มต้นของช่วงจริงๆ ฟังก์ชัน MATCH (แนะนำเป็นอาร์กิวเมนต์ที่สองของฟังก์ชัน OFFSET) จะให้บริการเราเพื่อจุดประสงค์นี้:

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

แน่นอนว่าทั้งสองฟังก์ชันรวมอยู่ในฟังก์ชัน OFFSET ที่อธิบายไว้ข้างต้นแล้ว นอกจากนี้ โปรดสังเกตว่าในฟังก์ชันทั้ง MATCH และ COUNTIF มีการอ้างอิงถึงช่วงที่เรียกว่า WorkList ดังที่ฉันได้กล่าวไว้ก่อนหน้านี้ คุณไม่จำเป็นต้องใช้ชื่อช่วง คุณสามารถป้อน $H3:$H15 ได้เลย อย่างไรก็ตาม การใช้ชื่อช่วงในสูตรจะทำให้อ่านง่ายและสะดวกยิ่งขึ้น

นั่นคือทั้งหมด:

ดาวน์โหลดตัวอย่างของรายการดรอปดาวน์ที่เกี่ยวข้องใน Excel

สูตรเดียวที่ไม่ง่ายนัก แต่ทำให้งานง่ายขึ้นและป้องกันข้อผิดพลาดเมื่อป้อนข้อมูล!

เพื่อไม่ให้พิมพ์ตัวอักษรและตัวเลขข้อความที่พิมพ์ก่อนหน้านี้และค่าตัวเลขของเซลล์เพื่อเร่งกระบวนการกรอกเซลล์ของแผ่นงาน MS Excel พร้อมข้อมูลและเพื่อลดข้อผิดพลาดรวมถึงการพิมพ์ผิดและการสะกดคำผิด สะดวกในการใช้รายการแบบเลื่อนลง

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

โปรแกรม MS Excel มีอินเทอร์เฟซที่เป็นมิตรต่อผู้ใช้ เสนอทางเลือกต่างๆ มากมายแก่ผู้ใช้เพื่อขอความช่วยเหลือเมื่อป้อนข้อมูลซ้ำลงในเซลล์แผ่นงาน

สมมติว่าเราเก็บรักษาฐานข้อมูลใบเสร็จรับเงินแบบม้วนไว้ที่คลังสินค้า ในคอลัมน์แรกเราจะระบุประเภทของโปรไฟล์แบบรีด

ตัวเลือก # 0 - "ประถมศึกษา"

เมื่อสร้างรายการถัดไปในเซลล์ A9 เมื่อพิมพ์อักษรตัวแรกของชื่อโปรไฟล์ เช่น "Ш" Excel แนะนำให้เติมคำว่า "Channel" ในเซลล์ หลังจากพิมพ์ "Ш" เพียงกดปุ่ม "Enter" บนแป้นพิมพ์ - และคำนั้นจะถูกป้อนลงในเซลล์

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

มาดูตัวเลือกสำหรับการสร้างรายการแบบเลื่อนลงโดยตรง

ตัวเลือก # 1 - "ง่ายที่สุด"

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

ในเวอร์ชันนี้ เซลล์ที่ใช้งานอยู่จะต้องอยู่ติดกับด้านล่างสุดของช่วงของค่า และช่วงนั้นจะต้องไม่มีเซลล์ว่าง!

ตัวเลือก # 2 - "ง่าย"

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

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

1. เราสร้างรายการค่าที่เป็นไปได้โดยการเขียนลงในคอลัมน์หนึ่งค่าต่อเซลล์ สมมติว่านี่คือรายการในเซลล์ A2...A8

2. เราเปิดใช้งานเซลล์ที่เราต้องการวางรายการแบบเลื่อนลงโดยวางเคอร์เซอร์ไว้ ให้นี่เป็นเซลล์เดียวกัน A9

3. ในเมนูหลัก เลือกปุ่ม "ข้อมูล" – "ตรวจสอบ..."

4. ในหน้าต่าง "การตรวจสอบค่าที่ป้อน" ที่ปรากฏขึ้น ให้เลือกแท็บ "พารามิเตอร์"

5. ในฟิลด์ "ประเภทข้อมูล:" จากรายการแบบเลื่อนลง (คล้ายกับรายการที่เรากำลังสร้าง) ให้เลือกค่า "รายการ"

6. ในช่อง "แหล่งที่มา:" ที่ปรากฏขึ้น ให้ระบุช่วงที่มีรายการค่าที่เป็นไปได้

7. ทำเครื่องหมาย (หากไม่ได้ตรวจสอบโดยค่าเริ่มต้น) ช่องทำเครื่องหมาย "รายการค่าที่ถูกต้อง" แล้วคลิกปุ่ม "ตกลง"

รายการแบบเลื่อนลงพร้อมแล้ว สามารถคัดลอกเป็นสูตรลงในเซลล์จำนวนเท่าใดก็ได้!

ตัวเลือก # 3 - "ซับซ้อน"

ตัวเลือกสำหรับการสร้างรายการแบบเลื่อนลงนี้ แม้จะมีชื่อว่า "ซับซ้อน" แต่ก็ไม่ใช่อย่างนั้น หากต้องการสร้างรายการแบบเลื่อนลง จะใช้รายการ Combo Box บนแถบเครื่องมือแบบฟอร์ม

มาสร้างรายการแบบเลื่อนลงโดยใช้วิธีนี้

1. เราสร้างรายการอ้างอิงในเซลล์ A2...A8

2. ในเมนูหลัก เลือกปุ่ม “ดู” – “แถบเครื่องมือ” – “แบบฟอร์ม”

3. ในแผง "แบบฟอร์ม" ที่ปรากฏขึ้น ให้เลือก "Combo Box" แล้ววาดในเซลล์ A9 เป็นต้น

องค์ประกอบ “Combo Box” ไม่ได้ถูกวางไว้ในเซลล์ แต่เหมือนเดิม อยู่เหนือมัน!!! องค์ประกอบอาจมีขนาดใหญ่และอยู่เหนือเซลล์หลายเซลล์

4. คลิกขวาที่องค์ประกอบที่วาดแล้วเลือก "จัดรูปแบบวัตถุ" ในเมนูบริบทที่ปรากฏขึ้น

5. ในหน้าต่าง "การจัดรูปแบบวัตถุ" ที่ปรากฏขึ้น บนแท็บ "การควบคุม" ให้กรอกข้อมูลในช่องต่างๆ ตามรูปด้านล่าง แล้วคลิก "ตกลง"

6. รายการแบบเลื่อนลงพร้อมแล้ว โดยจะแสดงหมายเลขลำดับของรายการในเซลล์ B9 ที่เกี่ยวข้อง (คุณสามารถกำหนดเซลล์ใดก็ได้ที่สะดวกสำหรับคุณ ไม่จำเป็นต้องเป็น B9!)

หากต้องการแสดงค่าจากรายการอ้างอิงในเซลล์ใดๆ ให้ใช้ฟังก์ชัน INDEX สมมติว่าเราต้องแสดงค่าในเซลล์ A9 ซึ่งอยู่ใต้องค์ประกอบ "Combo Box"

เมื่อต้องการทำเช่นนี้ ให้เขียนสูตรในเซลล์ A9: =INDEX(A2:A8,B9)

ตัวอย่างที่ชัดเจนมีการกล่าวถึงโดยละเอียดในบทความ “” คุณสามารถไปตามลิงค์และตรวจสอบได้

รายการแบบเลื่อนลงที่สร้างขึ้นในลักษณะนี้บวกกับการใช้ฟังก์ชัน INDEX และ/หรือ VLOOKUP ให้ความเป็นไปได้ที่ไร้ขีดจำกัดสำหรับผู้ใช้เมื่อดึงข้อมูลจากตารางการค้นหาพื้นฐานต่างๆ

ตัวเลือกหมายเลข 4 - "ยากที่สุด"

ในการสร้างรายการแบบเลื่อนลงในกรณีนี้จะใช้องค์ประกอบ "Combo Box" เช่นกัน แต่จะใช้แถบเครื่องมือ "การควบคุม" (ใน MS Excel 2003) สิ่งเหล่านี้เรียกว่าตัวควบคุม ActiveX ที่นี่ทุกอย่างมีลักษณะคล้ายกับตัวเลือกหมายเลข 3 มาก แต่ความเป็นไปได้ในการปรับแต่งและการจัดรูปแบบองค์ประกอบนั้นกว้างกว่ามาก

1. ในเมนูหลัก เลือกปุ่ม “ดู” – “แถบเครื่องมือ” – “การควบคุม”

2. ในแผง "การควบคุม" ที่ปรากฏขึ้น ให้เลือก "Combo Box" แล้ววาดลงในเซลล์ A9 องค์ประกอบActiveX“กล่องคำสั่งผสม” ไม่ได้ถูกวางไว้ในเซลล์ แต่อยู่ด้านบนเพื่อปกปิด!!!

3. คลิกปุ่ม "คุณสมบัติ" บนแผง "การควบคุม" และในหน้าต่าง "คุณสมบัติ" ที่ปรากฏขึ้นป้อนช่วงของแหล่งข้อมูลด้วยตนเองที่อยู่ของเซลล์ที่เกี่ยวข้อง (เซลล์ที่จะป้อนค่าที่เลือก) และ จำนวนแถวที่แสดง

4. จากนั้นหากต้องการคุณสามารถเปลี่ยนแบบอักษรสีสีพื้นหลังและพารามิเตอร์อื่น ๆ... ไม่มีอะไรซับซ้อนในการใช้ตัวเลือก "ยากที่สุด" - ดูด้วยตัวคุณเอง ทุกอย่างใช้งานง่ายแม้ว่าความรู้พื้นฐานภาษาอังกฤษจะไม่เจ็บก็ตาม!

5. กดปุ่ม "ออกจากโหมดการออกแบบ" บนแผง "การควบคุม" และตรวจสอบการทำงานของรายการแบบเลื่อนลง ทุกอย่างทำงานได้! ค่าที่เลือกจะเขียนในเซลล์ A9 ในตัวอย่างของเรา - ใต้องค์ประกอบ "Combo Box" โดยทั่วไป เซลล์ที่เชื่อมโยงสามารถเป็นเซลล์ใดก็ได้ยกเว้นเซลล์ที่มีรายการฐานอยู่

ผลลัพธ์.

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

ในทางปฏิบัติฉันมักจะสร้างรายการแบบเลื่อนลงใน Excel โดยใช้ตัวเลือกหมายเลข 1 และหมายเลข 3 บ่อยครั้งน้อยกว่า - ตัวเลือกหมายเลข 2 และน้อยมาก - ตัวเลือกหมายเลข 4 แม้ว่าจะมีความยืดหยุ่นมากที่สุดและมีให้ ความเป็นไปได้ที่กว้างที่สุด

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

สมัครติดตามประกาศบทความในหน้าต่างที่อยู่ท้ายบทความแต่ละบทความหรือในหน้าต่างที่ด้านบนของหน้าและ อย่าลืมยืนยันสมัครสมาชิกโดยคลิกที่ลิงค์ในจดหมายที่จะส่งถึงคุณตามที่อยู่อีเมลที่ระบุ (อาจมาถึงโฟลเดอร์สแปม - ทั้งหมดขึ้นอยู่กับการตั้งค่าอีเมลของคุณ)!!!

บทความนี้มีประโยชน์หรือไม่สำหรับคุณผู้อ่านที่รัก? เขียนเกี่ยวกับมันในความคิดเห็น

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

รายการดรอปดาวน์ประเภททั่วไปบางประเภทที่คุณสามารถสร้างใน Excel ได้ ได้แก่:

ด้วยฟังก์ชันเลือกหลายรายการ
ด้วยการกรอก;
ด้วยการเพิ่มองค์ประกอบใหม่
พร้อมรูปถ่ายแบบเลื่อนลง
ประเภทอื่นๆ.

สร้างรายการใน Excel ด้วยการเลือกหลายรายการ

คุณสามารถสร้างรายการในเซลล์โปรแกรมโดยใช้เครื่องมือในตัวของแผงควบคุม

มาดูประเภทหลักและประเภททั่วไปทั้งหมดอย่างละเอียดยิ่งขึ้นและกระบวนการสร้างมันขึ้นมาในทางปฏิบัติ

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

การเลือกหลายรายการเป็นสิ่งจำเป็นเมื่อคุณต้องการตั้งค่าของเซลล์เอกสารหลายเซลล์โดยใช้รายการเดียว

หากต้องการสร้างบัญชี ให้ทำตามคำแนะนำ:

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

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