การสร้าง Drop Down List เพื่อควบคุมการแสดงข้อมูล (Data) แบบตาราง (Table)

จากบทความก่อนเรื่อง "การสร้าง Drop Down List ทั้งแบบง่ายๆ และแบบมีเงื่อนไข" เราให้คุณทำความรู้จักกับวิธีการสร้าง Drop down list ไป 3 แบบ ไม่ทราบว่าคุณๆ ยังคิดว่ายุ่งยาก ซับซ้อนเกินไปไหม ในบทความนี้ เราจะนำเสนอวิธีการสร้าง Drop down list ในวิธีการอื่นๆ เพื่อให้คุณได้วิธีการที่ชื่นชอบมากที่สุด

การแสดงข้อมูล จากวิธีเลือก Drop Down Filte 

รูปที่ 1 ตารางข้อมูลที่ต้องการให้แสดงข้อมูลตาม DropDownList 

จากรูปที่ 1 เป็นตารางข้อมูลเดิม ที่เราจะมากำหนดให้แสดงข้อมูลตาม DropDownList ซึ่งคุณๆ 

ต้องเพิ่มคอลัมน์ที่ 1 เพื่อเป็นคอลัมน์แสดงลำดับที่ คอลัมน์ที่ 2 เพื่อเป็นคอลัมน์ที่แสดงข้อมูลตาม DropDownList และ คอลัมน์ที่ 3 เพื่อแสดงตัวเลขทั้งหมดในคอลัมน์ที่ 2

ต้องสร้างตารางที่จะใช้เป็นเงื่อนไขให้กับ Drop Down List ดังรูปที่ 2

รูปที่ 2 ตารางที่เพิ่ม Column 1-3 และสร้าง DropDownList

ขั้นตอนการสร้าง
1. สร้าง List ที่ไม่มีข้อมูลซ้ำกัน เพื่อนำข้อมูลใน List ไปสร้าง DropDownList อีกที 
     1.1. คัดลอกข้อมูลในคอลัมน์ Module มาวางไว้ในส่วนอื่นของ Worksheet ตามรูปที่ 2 วางไว้ที่คอลัมน์ G
     1.2. เลือกเมนู Data >> Remove Duplicates โปรแกรม Excel จะตัดข้อความที่ซ้ำกันทิ้งไป ซึ่งเราจะนำข้อมูลนี้ มาสร้าง Drop down list กัน 
2. การสร้าง Drop down list คุณสามารถกลับไปทบทวนการสร้าง ตามตัวอย่างที่ 1 ในบทความ "การสร้าง Drop Down List ทั้งแบบง่ายๆ และแบบมีเงื่อนไข" นี้
3. สร้างคอลัมน์อีก 2 คอลัมน์ โดย
    3.1. คอลัมน์ที่ 1 ให้แสดงเลข runing number 
    3.2. คอลัมน์ที่ 2 ให้เขียนสูตรฟังก์ชั่น =IF(A2=$I$1,D2,"") ในเซลล์ E2 ซึ่งฟังก์ชั่นนี้ จะทำการตรวจสอบเซลล์ E2 ว่ามีข้อมูล A2 มีค่าเท่ากับคอลัมน์ I1 หรือไม่ ถ้ามีค่าเท่ากันให้แสดงข้อมูลเหมือนเซลล์ D2 แต่ถ้าไม่เหมือนกันก็แสดงค่าว่าง 
    3.3. คอลัมที่ 3 ให้นำเลขในคอลัมน์ที่ 2 มาแสดง โดยเขียนสูตรฟังก์ชั่น =IFERROR(SMALL($E$2:$E$27,$D2),"") ในเซลล์ F2 ซึ่งฟังก์ชั่นนี้ SMALL จะทำการตรวจสอบค่าในเซลล์ D2 ภายในช่วง E2 ถึง E27 ถ้าในเซลล์ D2 มีค่าให้แสดงตัวเลข แต่ถ้าค่าไม่ตรงกัน ฟังก์ชันจะคืนค่า #NUM! ซึ่งถ้าเราเขียนเฉพาะฟังก์ชั่น SMALL เพียงฟังก์ชั่นเดียว ตารางเราคงจะดูไม่ดีเท่าไร ดังนั้น เราจะเอาฟังก์ชั่น IFERROR มาควบคุม เมื่อเจอ Error ต่างๆ เราจะให้แสดงเป็นค่าว่าง ดังรูปที่ 2
4.  ขั้นตอนสุดท้าย คือ การเขียนสูตรทีละคอลัมน์ 
=IFERROR(INDEX($A$2:$A$27,$F2,COLUMNS($J$2:$J$27)),"")
=IFERROR(INDEX($B$2:$B$27,$F2,COLUMNS($K$2:$K$27)),"")
=IFERROR(INDEX($C$2:$C$27,$F2,COLUMNS($L$2:$L$27)),"")
เพื่อสร้างตารางที่เปลี่ยนข้อมูลตามที่เลือก DropDownList ดังรูปที่ 3

รูปที่ 3 แสดงตารางที่เขียนจากสูตร เพื่อให้แสดงข้อมูลที่เปลี่ยนไปตาม DropDownList

เพียงเท่านี้คุณจะได้ตารางที่แสดงข้อมูลตาม DropDownList จ้า

Read More

การสร้าง Drop Down List ทั้งแบบง่ายๆ และแบบมีเงื่อนไข

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

รูปที่ 1 ตัวอย่างการสร้าง Drop-Down List ด้วย Data Validation แบบช่วงเซลล์ข้อมูล (Range)

เพียงแค่นี้คุณจะได้ 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 แสดงการสร้าง Drop-Down List 

จากรูปที่ 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 

รูปที่ 3 แสดงวิธีการสร้าง Drop-Down List ด้วยสูตร OFFSET

ข้อดีของการใช้สูตร 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

รูปที่ 4 ตัวอย่างการสร้าง Drop-Down List แบบมีเงื่อนไข

ข้อ 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 ตัวอย่างการสร้าง Drop-Down List ที่ 2

จากรูปที่ 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 แบบ เพื่อให้คุณๆ นำไปประยุกต์ใช้กับลักษณะงานของแต่ละท่านได้อย่างเหมาะสม 

Read More