วิธีแก้อันนึงที่ผมนำเสนอไปแล้วคือ การทำ Drop Down หลายชั้น แต่หากเราสามารถที่จะทำให้คนใช้งานพิมพ์ข้อความบางส่วนแล้วแสดงรายการที่มีคำนั้นๆ ได้เลยจะดีขนาดไหน?

หลายคนคิดว่าเรื่องแบบนี้ต้องใช้ VBA ทำเท่านั้น แต่ในความเป็นจริง เราก็สามารถเขียนสูตรเพื่อสร้าง “Drop down List แบบค้นหาได้” โดยจะใช้ Excel Version ไหนก็สามารถทำได้ครับ สุดยอดไปเลยมั้ยล่ะ!!

สารบัญ

คลิปวีดีโอ

ในคลิปนี้ผมมีการอธิบายโดยละเอียดถึงวิธีทำตั้งแต่ต้นจนจบ ลองดูได้เลยครับ

ไฟล์ประกอบการทำตาม

ไฟล์เริ่มต้น :

https://drive.google.com/open?id=1qiWOJjoB9dGyPDt9HjByaCLUwky8_jqX

ไฟล์จบ :

https://drive.google.com/open?id=17QXXR5xVtHqxPgU1ay5biyg4Vcd1ypod

หลักการทำงานของ Drop Down List แบบค้นหาได้

• ใช้ ISNUMBER + SEARCH เพื่อเช็คว่ารายการไหน มีคำค้นหาอยู่บ้าง
• พยายามเอารายการที่เจอ มากองรวมกันข้างบนด้วย INDEX
  • ในนี้ผมมีใช้ฟังก์ชัน ROW กับ RANK เพื่อช่วยจัดอันดับตัวที่เจออันดับ 1,2,3 ด้วยครับ
• จากนั้นใช้ OFFSET เพื่อเลือก Range ของรายการที่เจอทั้งหมด
• เอาสูตร OFFSET ที่ได้ไปตั้งชื่อ
• เอาชื่อที่ตั้งไปใส่ใน Data Validation List
• เลือก Option ว่าไม่ต้องแสดง Error Alert
• ใช้ IF ดักว่าถ้าหาไม่เจอ ให้แสดงคำว่า “ไม่เจอข้อมูล”
  

สรุปสูตร

IsFound =ISNUMBER(SEARCH($A$2,[Province])) GetRowNum =IF([@IsFound],ROW([@Province]),"") Rank =RANK.EQ([@GetRowNum],[GetRowNum],1) Rearrange =INDEX([Province],MATCH(ROW([@Province])-1,[Rank],0)) count=--NOT(ISERROR([@Rearrange])) =IF(SUM(Table1[count])=0,$K$4,OFFSET(Table1[[

Headers],[Rearrange]],1,0,SUM(Table1[count]),1))

ใน A2 คือช่องที่จะทำ Dropdown ใน K4 มีคำว่า ไม่เจอข้อมูล

อยากใช้ Drop Down หลายที่ทำไง?

ถ้าอยากให้ Dropdown List ที่ทำมาสามารถ Copy ไปใช้ได้หลายๆ ที่ ต้องเปลี่ยนสูตรตรง IsFound เล็กน้อยนะครับ

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

ตัวอย่างที่ 1 การสร้าง Drop-Down List แบบง่ายๆ ด้วยตารางหรือช่วง (Table/Range) ใน sheet 1. ให้คุณเตรียมข้อมูลที่ต้องการสร้าง Drop-Down List 2. ทำการสร้าง Drop-Down List ใน Cell ที่คุณต้องการ ดังนี้ \>> ไปที่ Tab: Data \>> มองหาส่วนของ Data Tool \>> เลือกปุ่ม Data Validation \>> จะมี PopUp: Data Validation แสดงขึ้นมา \>> ใน Tab: Settings ให้เลือก List \>> ในช่อง Source Field: [ระบุชื่อเซลล์ที่จะมาทำ Drop-Down List] \>> คลิกปุ่ม OK ดังรูปที่ 1

เพียงแค่นี้คุณจะได้ Drop-down list ที่มีข้อมูลตั้งแต่ A20 ถึง A24

แต่ถ้ามี Item ไม่มาก สามารถระบุข้อมูลไปที่ Source Field ได้โดยตรง จากรูปที่ 1 ให้ระบุ Item ที่ Source field เช่น Mark,John,Natty,Anny,Woody

3. การแปลง Cell ธรรมดาใน Excel ให้กลายเป็น Drop-Down List ที่มีข้อมูลในข้อ 1 เรียบร้อย ดังรูปที่ 2

จากรูปที่ 2 เราได้สร้าง Cell ด้านขวาของ "Employees" ให้เป็น Drop-Down List นั้นเอง

กรณีต้องการสร้าง Drop Down List ทั้งคอลัมน์ คุณสามารถคลิกที่หัวคอลัมน์นั้นๆ และทำตามตัวอย่างที่ 1 ได้เลย

ตัวอย่างที่ 2 การสร้าง Drop-Down List แบบใช้สูตร Offset ข้อ 1 และ ข้อ 2 ให้ทำเหมือนตัวอย่างที่ 1 แต่ ในช่อง Source Field: ให้พิมพ์สูตร OFFSET Function ในการสร้าง Drop-Down List โดยไวยากรณ์ของสูตร คือ

\=OFFSET(reference name, rows, cols, [height], [width])

ดังรูปที่ 3

ข้อดีของการใช้สูตร OFFSET ยังสามารถสร้าง Drop-down list ด้วยการขยายเซลล์ เพื่อเพิ่มรายการใน Drop-down list ได้อัตโนมัติ ซึ่งคุณสามารถนำสูตรข้างล่างนี้ ไปประยุกต์ใช้ได้ ดังนี้

\=OFFSET($A$2,0,0,COUNTIF($A$2:$A$10,”<>”))

จากสูตรนี้ CountIF จะแสดงรายการใน Drop-Down List เพียง 5 รายการ โดย CountIF จะใช้นับเซลล์ที่ว่าง (non-blank cells) และรวมเซลล์ว่างเพื่อไม่ให้แสดงรายการว่างใน Drop-Down List นั้นเองดังนั้น เมื่อไรที่คุณเพิ่มรายการ (Item) ในเซลล์ตั้งแต่ A7 ถึง A10 ช่องที่เราสร้าง Drop-Down List จะเพิ่มรายการอัตโนมัติทันที

ความแตกต่างระหว่างตัวอย่างที่ 1 และตัวอย่างที่ 2 คุณจะพบว่า หาสร้าง Drop Down List ตามตัวอย่างที่ 2 จะทำให้การเพิ่มหัวข้อภายใน Drop Down List สามารถทำได้ง่ายกว่า และไม่ต้องกลับไปแก้ไข Data Validation เดิมที่ทำไว้ด้วย แต่ถ้าคุณทำตามตัวอย่างที่ 1 คุณต้องมั่นใจว่า Drop Down List ที่คุณกำลังสร้าง จะไม่มีการเพิิ่มหัวข้อ Drop Down List ในภายหลังอีก

ตัวอย่างที่ 3 การสร้าง Drop-Down List แบบมีเงื่อนไขหรือแสดงข้อมูลตาม Drop-Down List ก่อนหน้า หลายครั้งคุณต้องสร้าง Drop-Down List หลายตัว โดย Drop-Down List ที่ 2 จะขึ้นกับ Drop-Down List ที่ 1 จะเรียกว่า "Dependent drop-down lists" หรือ "Conditional drop-down lists" ข้อ 1. เตรียมข้อมูลสำหรับสร้าง Dependent drop-down lists หรือ Conditional drop-down lists โดยเลือกเซลล์ที่คุณต้องให้เป็น Drop-down list1

ข้อ 2. การสร้าง Drop-Down List ที่ 1 ใน Cell ที่คุณต้องการ \>> ไปที่ Data Tab \>> มองหาส่วนของ Data Tool \>> เลือกปุ่ม Data Validation \>> จะมี PopUp: Data Validation แสดงขึ้นมา \>> ใน Tab: Settings ให้เลือก List \>> ในช่อง Source Field: [ระบุชื่อเซลล์ที่จะมาทำ Drop-Down List ที่ 1] \>> คลิกปุ่ม OK ดังรูปที่ 4

ข้อ 3. การตั้งชื่อตารางที่จะสร้าง Drop-down List ที่ 2 ด้วยการเลิือกเซลล์ A1 ถึง B6 หลังจากนั้น \>> เลือก Formulas Tab \>> มองหาส่วนของ Defined Names \>> เลือกปุ่ม Create from Selection \>> จะมี PopUp: Create Named from Selection แสดงขึ้นมา \>> ให้เลือก Top row option จะได้ชื่อช่วงเซลล์ (names ranges) เป็น Employee และ Department โดย Employee จะอ้างชื่อช่วงเซลล์ (names ranges) Employee \>> คลิกปุ่ม OK

ข้อ 4. การสร้าง Drop-Down List ที่ 2 ใน Cell ที่คุณต้องการ \>> ไปที่ Data Tab \>> มองหาส่วนของ Data Tool \>> เลือกปุ่ม Data Validation \>> จะมี PopUp: Data Validation แสดงขึ้นมา \>> ใน Tab: Settings ให้เลือก List \>> ในช่อง Source Field: [ระบุสูตรที่สามารถสร้าง Drop-Down List ที่ 2] \>> คลิกปุ่ม OK ดังรูปที่ 5

จากรูปที่ 5 สูตร INDIRECT(ชื่อเซลล์ที่เป็น Drop-Down List ที่ 1) เพื่อกำหนดให้ Drop-down list ที่ 2 แสดงผลตาม Items ตาม Item D2 นั้นเอง (Drop-down list 1 เป็น Department ส่งผลให้ Drop-down list 2 แสดง Items ของ Department)

ในบทความนี้ เราให้ตัวอย่างไป 3 แบบ เพื่อให้คุณๆ นำไปประยุกต์ใช้กับลักษณะงานของแต่ละท่านได้อย่างเหมาะสม

Drop Down List ใน Excel ทำไง

วางเม้าส์ ในตำแหน่ง Cell ที่ต้องการสร้าง List..

เมนู Data -> Data Validation..

จาก Any Values - > List..

พิมพ์ List ที่เราต้องการเข้าไปได้เลย เสร็จแล้ว คลิก OK ก็จะได้ List ในช่องที่ เอาเม้าส์ วางไว้.

การสร้าง Dropdown List ควรจะใช้เครื่องมืออะไร

1. ทำการสร้างรายการใน Excel Table โดยคลิกที่ cell ที่ต้องการสร้าง Excel Table แล้วกด Ctrl+T เลือกตัวเลือก My table has headers (ตารางของฉันมีส่วนหัวของตาราง) แล้วกดปุ่ม OK. 2. พิมพ์หัวคอลัมภ์และพิมพ์รายการที่ต้องการให้แสดงใน drop down list ดังตัวอย่างด่านล่าง

ฟั่งชั่นใดเป็นเครืองมือในการสร้าง Dropdown List

ขั้นตอนการทำกล่องตัวเลือก drop down list ใน Excel ด้วยเครื่องมือ Data Validation (การตรวจสอบความถูกต้องของข้อมูล) - เลือกเซลที่ต้องการทำ - แถบเครื่องมือ ข้อมูล (Data) - เลือก การตรวจสอบความถูกต้องของข้อมูล (Data Validation) - ได้หน้าต่างใหม่ขึ้นมา เลือกเป็น รายการ (list) - ที่แหล่งข้อมูล สามารถพิมพ์รายการตัวเลือกเว้น ...

คำสั่งในข้อใดใช้สำหรับการสร้างดรอปดาวน์ลิสต์

วิธีทำ Drop down list ในโปรแกรม 1. คลิกที่ cell หรือ range cell ที่ต้องการ Drop down list. 2. ไปที่แท็บ Data (ข้อมูล), คลิกกลุ่มคำสั่ง Data Tolls group, คลิกที่คำสั่ง Data Validation (การตรวจสอบความถูกต้องของข้อมูล) ดังภาพด้านล่าง