การสร้างรายการแบบดรอปดาวน์

รายการแบบดรอปดาวน์จะเพิ่มลูกศรเล็ก ๆ ให้เซลล์ เมื่อคลิกลูกศรจะเห็นเมนูตัวเลือกที่อนุญาต ผู้ใช้จึงเลือกแทนการพิมพ์

นี่คือรูปแบบการตรวจสอบความถูกต้องของข้อมูลที่ใช้งานง่ายที่สุด:

• ไม่มีการพิมพ์ผิด — ตัวเลือกผ่านการอนุมัติไว้ล่วงหน้า
• การสะกดสอดคล้องกันตลอดทั้งคอลัมน์
• ป้อนข้อมูลที่ใช้ซ้ำ เช่น สถานะหรือภูมิภาค ได้เร็วขึ้น

เบื้องหลังแล้ว รายการแบบดรอปดาวน์ก็คือกฎการตรวจสอบความถูกต้องประเภท รายการ

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

Yes,No,Maybe

คั่นแต่ละรายการด้วยเครื่องหมายจุลภาค ใน Google Sheets ให้เลือก รายการแบบดรอปดาวน์ แล้วป้อนแต่ละตัวเลือก

• เหมาะสำหรับรายการขนาดเล็กที่เปลี่ยนแปลงไม่บ่อย
• ข้อเสียคือ หากต้องการแก้ไขรายการ คุณต้องเปิดกฎขึ้นมาอีกครั้ง

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

=$F$2:$F$6

เมื่อแก้ไขเซลล์ในคอลัมน์ F รายการแบบดรอปดาวน์จะอัปเดตทันที

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

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

=RegionList

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

• ทำงานเหมือนกันทั้งใน Excel และ Google Sheets
• ชื่อและรายการแบบดรอปดาวน์จะสอดคล้องกันเสมอ

ลองนึกถึงเครื่องมือติดตามงาน ในแท็บช่วยเหลือ ให้แสดงสถานะไว้ใน A1:A4: เปิด, กำลังดำเนินการ, ถูกบล็อก, เสร็จสิ้น จากนั้นตั้งชื่อช่วงดังกล่าวว่า StatusList

เลือกคอลัมน์สถานะของคุณ เปิดการตรวจสอบความถูกต้องของข้อมูล เลือก รายการ และกำหนดแหล่งข้อมูลเป็น =StatusList

ตอนนี้เซลล์สถานะทุกเซลล์จะแสดงตัวเลือกที่เป็นระเบียบเหมือนกันทั้งสี่รายการ รายงานและสูตร COUNTIF ที่นับแต่ละสถานะจะไม่พลาดรายการที่สะกดผิด

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

• แปลงแหล่งข้อมูลเป็น ตารางของ Excel และตั้งชื่อคอลัมน์ ตารางจะขยายโดยอัตโนมัติ
• หรือกำหนดช่วงที่มีชื่อแบบไดนามิกโดยใช้ฟังก์ชันกระจายผลลัพธ์ เช่น =A2# ใน Excel รุ่นใหม่

ใน Google Sheets การกำหนดแหล่งข้อมูลเป็นทั้งคอลัมน์ เช่น F2:F จะรวมรายการที่เพิ่มในอนาคตด้วย

รายการแบบดรอปดาวน์ที่ขึ้นต่อกันจะแสดงตัวเลือกที่แตกต่างกันตามเซลล์อีกเซลล์หนึ่ง เมื่อเลือกประเทศในเซลล์หนึ่ง รายการเมืองจะแสดงเฉพาะเมืองของประเทศนั้น

เทคนิคคลาสสิกของ Excel คือ ตั้งชื่อรายการย่อยแต่ละรายการให้ตรงกับหมวดหมู่ จากนั้นใช้ INDIRECT ในแหล่งข้อมูล:

=INDIRECT(A2)

หาก A2 มีค่าเป็น France และช่วงที่มีชื่อว่า France แสดงรายชื่อเมือง รายการแบบดรอปดาวน์ก็จะปรับตาม วิธีนี้เป็นเทคนิคขั้นสูงแต่มีประสิทธิภาพ

ตามค่าเริ่มต้น กฎรายการยังอนุญาตให้ผู้ใช้พิมพ์ค่าเองได้ คุณสามารถควบคุมเรื่องนี้ได้ดังนี้:

• ใน Excel ให้ตั้งค่า การแจ้งเตือนข้อผิดพลาดเป็น หยุด เพื่อปฏิเสธทุกค่าที่ไม่อยู่ในรายการ
• หากอนุญาตให้ใช้ คำเตือน ผู้ใช้ก็อาจเลือกใช้ค่าอื่นแทนรายการแบบดรอปดาวน์ได้
• ใน Google Sheets ให้เลือก ปฏิเสธข้อมูลที่ป้อน เพื่อบังคับใช้รายการอย่างเคร่งครัด

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

ลูกศรเล็ก ๆ จะปรากฏเฉพาะเมื่อเลือกเซลล์ และจะปรากฏก็ต่อเมื่อเลือก รายการแบบดรอปดาวน์ในเซลล์ (Excel) หรือเปิดใช้รูปแบบรายการแบบดรอปดาวน์ (Sheets) เท่านั้น

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

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

เนื่องจากรายการแบบดรอปดาวน์ขับเคลื่อนด้วย =RegionList การดูแลรักษาจึงทำได้ง่าย:

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

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

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

=XLOOKUP(A2,RegionList,SalesList)

เนื่องจากรายการแบบดรอปดาวน์รับรองว่า A2 จะมีภูมิภาคที่ถูกต้องเสมอ การค้นหาจึงไม่ล้มเหลวเพราะการพิมพ์ผิด

• รายการแบบดรอปดาวน์ควบคุมข้อมูลที่ป้อน
• การค้นหาจะตอบสนองต่อตัวเลือกนั้น

การทำงานร่วมกันนี้เป็นหัวใจสำคัญของรายงานแบบโต้ตอบที่ขับเคลื่อนด้วยสูตร

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

คุณได้เรียนรู้การสร้าง รายการแบบดรอปดาวน์ ที่ช่วยให้ข้อมูลสะอาดและสม่ำเสมอ:

• ใช้กฎการตรวจสอบแบบ รายการกับรายการที่พิมพ์ไว้ ช่วงเซลล์ หรือ ช่วงที่มีชื่อ
• ควบคุมรายการแบบดรอปดาวน์ด้วย =RegionList เพื่อให้อ่านเข้าใจง่ายและดูแลรักษาได้สะดวก
• ทำให้รายการขยายได้ด้วยตารางหรือแหล่งข้อมูลที่อ้างอิงทั้งคอลัมน์
• ใช้การแจ้งเตือนข้อผิดพลาดแบบ หยุด เพื่อบังคับให้รับเฉพาะค่าที่อยู่ในรายการ

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