Computer Knowledge: Data-Management

แสดงบทความที่มีป้ายกำกับ Data-Management แสดงบทความทั้งหมด

การใส่สีให้ Drop Down List ใน Microsoft Excel อย่างง่าย

BeHappyมกราคม 19, 2562สร้าง dropdownlist, ใส่สีให้ dropdownlist, เอ็กเซล, Conditional Formatting, Data-Management, Drop down list, excel, highlight in dropdownlist ไม่มีความคิดเห็น

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

การใส่สีให้ Drop Down List อย่างง่าย

เมื่อคุณๆ คิดอยากใส่สี HighLight เปลี่ยนสีตัวหนังสือ หรือใส่ Icon เป็นต้น มาถึงตรงนี้ถ้าใครที่ชำนาญ Excel สักหน่อย คงจะคิดถึง Conditional Formatting เป็นแน่ ถูกต้องแล้วค่ะ ไม่พูดพร่ำต่อละ เข้าเรื่องเลยละกันค่ะ

1. คุณต้องสร้าง Drop Down List ก่อน แต่ถ้ายังไม่รู้จะสร้างอย่างไร สามารถศึกษาวิธีการสร้างจากบทความเก่า เช่น การสร้าง Drop Down List แบบง่ายๆ หรือ การเพิ่มรายการภายใน Drop Down List ภายหลังโดยไม่ต้องสร้างใหม่

กรณีต้องการใส่สี HighLight ใน Drop Down List เพียง Cell เดียว

2. กำหนดให้คอลัมน์ B เป็น Drop Down List แล้ว (โดย Drop Down List เราเป็นคำที่แสดง Status ต่างๆ เช่น Complete Pending Inprocess Queue และ Cancel) เราจะแสดงตัวอย่างวิธีการกำหนดให้เมื่อผู้ใช้คลิกเลือกสถานะต่างๆ Hightlight จะขึ้นตามที่กำหนด ตามรูปที่ 1

รูปที่ 1 แสดงตัวอย่าง DropDownList ให้แสดง Hightlight

2.1 ให้คุณๆ คลิก Cell ที่เป็น Drop Down List ซึ่งตามรูปที่ 1 คือ B1 โดยให้ไปเลือก Home Ribbon และเลือก Conditional Formatting ตามรูปที่ 2

รูปที่ 2 การเลือก Conditional Formatting

2.2 เลือก Highlight Cells Rules และจะขึ้นเมนูย่อยให้คุณเลือกว่าจะกำหนดเงื่อนไขยังไงให้แสดง Highlight แบบต่างๆ ตามตัวอย่างข้อ 2 DropDownList ของเราเป็นตัวหนังสือ ดังนั้น เราจะเลือกเป็น Equal To... ตามรูปที่ 3

รูปที่ 3 ขึ้น Equal To PopUp ให้กำหนดค่าที่จะเท่ากัน

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

รูปที่ 4 กำหนดว่าถ้า DropDownList มีค่าเท่ากับ Complete จะแสดง Highlight

กรณีต้องการใส่สี HighLight ใน Drop Down List หลาย Cells

3. จากข้อ 2.1 แทนที่จะเลือกแค่ B1 ให้คุณเลือกทั้งคอลัมน์ B และทำตามข้อ 2.2 โดยพิมพ์คำที่ต้องการไปที่ละคำ (Pending Inprocess Queue และ Cancel) และเลือกรูปแบบเป็นอย่างอืน ตามรูปที่ 5

รูปที่ 5 กำหนด DropDownList หลาย Cells ให้แสดง Highlight ตามต้องการ

ตามรูปที่ 5 กำหนดคำ Pending ให้แสดง Hightlight เป็นสีเทา ซึ่งใน Equal To PopUp ไม่มีค่าไว้ให้คุณเลือก Custom Format... ตามรูปที่ 6

รูปที่ 6 กำหนดพื้นของ DropDownList

ที่เมนู Format Cell คุณต้องเลือก Fill Tab ซึ่งคำ Pending เลือกพื้นเป็นสีเทา และคลิกปุ่ม Ok

ตามรูปที่ 1 ด้านบนคลิก DrowDownList ที่ B2 เลือก Pending จะแสดง Hightlight ตามที่กำหนด ดังรูปที่ 7

รูปที่ 7 แสดงตัวอย่าง DropDownList ที่แสดง HighLight

ส่วนคำ Inprocess Queue และ Cancel ให้ทำตามข้อ 3 และเลือก Highlight ตามต้องการ ซึ่งกรณีที่ต้องการแก้ไขที่หลังสามารถเข้าไปที่ Conditional Formatting เลือก Manage Rule ตามรูปที่ 8

รูปที่ 8 แสดง Conditional Formatting Rules Manager

คุณๆ สามารถแก้ไข Cell Value ต่างๆ โดยเลือกค่านั้น และคลิกที่ปุ่ท Edit Rule.. หรือ Double Click เข้าไป ตามรูปที่ 9

รูปที่ 9 Edit Formatting Rule

ที่ Select a Rule Type เลือกประเภทกฏที่ต้องการ ในรูปที่ 9 เลือกการจัดรูปแบบเซลที่มีเท่านั้น

ส่วน Edit the Rule Description คือ ส่วนที่คุณเข้าไปแก้ไขกฏที่จะให้ Excel ตรวจสอบได้

ส่วน Preview ให้คุณคลิกที่ปุ่ม Format เพื่อเปลี่ยนรูปแบบ HighLight ตามที่คุณต้องการได้เอง

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

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

3 ขั้นตอนในการสร้าง Drop Down List ให้สามารถเพิ่มรายการได้ภายหลังโดยไม่ต้องแก้ไข

BeHappyธันวาคม 16, 2561เพิ่มรายการใน Data Validation, สร้าง dropdownlist, เอ็กเซล, Data-Management, data validation, Drop down list, excel, excel data validate, Name Manager ไม่มีความคิดเห็น

การสร้าง Drop Down List ใน Excel สามารถสร้างได้จากหลายวิธี ซึ่งก่อนหน้านี้ เราได้นำเสนอ 3 วิธีการใน การสร้าง Drop Down List ทั้งแบบง่ายและแบบมีเงื่อนไข หรือ การสร้าง Drop Down List เพื่อควบคุมการแสดงข้อมูลแบบตาราง แต่ในบทความนี้ เราจะนำเสนอเทคนิคในการใช้ Data validation feature ร่วมกับ Table of Item or List เพื่อให้คุณๆ สามารถเพิ่มรายการใน Drop Down List โดยไม่ต้องไปแก้ไข Drop Down list เดิม

การสร้าง Drop Down List ที่คุณๆ สามารถเพิ่มรายการได้ในภายหลังโดยไม่ต้องแก้ไข

ใครบ้างที่เคยเจอปัญหาการสร้าง Drop Down List แล้ว ภายหลังที่ต้องการเพิ่มรายการใน Drop Down List กลับพบว่าต้องเริ่มต้นสร้าง Drop Down List ทั้งหมดใหม่

ถ้าคุณเจอปัญหาดังกล่าว ลองทำตามขั้นตอน 3 ขั้นตอนข้างล่างนี้

1. สร้าง Table of Item หรือ List

เป็นขั้นตอนแรกในคุณต้องสร้าง Table of Item หรือ List ที่คุณต้องการให้เป็นรายการ (Item of Drop Down List) หรือข้อมูลภายใน Drop Down List นั้นเอง
ตัวอย่างต้องการสร้าง Table of Item ของตัวเลข ตามรูปที่ 1

รูปที่ 1 ตารางรายการที่ต้องการจะนำไปสร้าง Drop Down List

จากนั้นให้วางเมาส์ที่ส่วนใดส่วนหนึ่งของตาราง และเปิด Ribbon's Insert tab >> คลิกเลืิอก Table หรือ Short Cut : Ctrl+T ดังรูปที่ 2

รูปที่ 2 เลือก Ribbon's Insert tab และเลือก Table

จากนั้นระบบจะแสดง Popup : Create Table ในช่อง Where is the data for your table? ให้คุณระบุเซลล์ที่เป็นข้อมูลหรือรายการใน Drop Down List และเลือกช่อง My table has headers จากนั้นคลิกปุ่ม OK ดังรูปที่ 3

รูปที่ 3 สร้างตารางสำหรับใส่รายการใน Drop Down List

ก็จะได้ตารางภายในโปรแกรม Excel ซึ่งมันจะชื่อว่า Table1

คุณสามารถตรวจสอบว่าใน Excel นี้มีตารางที่ช่อง Name Box ดังรูปที่ 4

รูปที่ 4 เลือก Name Box เพื่อตรวจสอบตารางที่สร้าง

สำหรับขั้นที่ 1 ที่เตรียมข้อมูลหรือรายการที่จะอยู่ใน Drop Down List อยู่ในตารางก็เสร็จแล้ว

2. สร้าง Name the list

ขัั้นตอนที่ 2 เป็นขั้นตอน named range ซึ่งมันจะทำให้ตัวตารางที่เราสร้างไว้ในขั้นตอนที่ 1 เป็นแบบ dynamic โดยให้เข้าไปที่ Ribbon's Formulas tab >> เลือก Define Name ดังรูปที่ 5

รูปที่ 5 สร้าง Named range

จะแสดง Pop up: New Name ให้คุณๆ ระบุชื่อที่คุณต้องการในช่อง Name และกดพิมพ์ชื่อหัวตารางในวงเล็บ ที่ช่อง Refers to ดังรูปที่ 6

รูปที่ 6 New Name

ในรูปที่ 6 นี้ ชื่อเราตั้งเป็น NumberName และช่อง Refers to เราให้ตารางที่ 1 (ในขั้นตอนที่ 1) ไปอ่านชื่อหัวตาราง

3. สร้าง Drop Down List

ขั้นตอนนี้จะเหมือนการสร้าง Drop Down List เหมือนในบทความ การสร้าง Drop Down List ทั้งแบบง่ายและแบบมีเงื่อนไข ดังนี้
3.1. เลือกเซลล์ที่ต้องการให้เป็น Drop Down List
3.2. เลือก Ribbon's Data tab 3.3. เลืิอก Data Validation 3.4. แสดง Pop Up: Data Validation3.5. ช่อง Allow: เลือก List 3.6. ช่อง Source: เอาเมาส์วางที่ช่องและกดปุ่ม F3 ดังรูปที่ 7

รูปที่ 7 สร้าง Drop Down List ครั้งเดียวได้หลายเซลล์

3.7. ปรากฏ Pop Up: Paste Name ให้เลือกชื่อ Name ที่เราสร้างไว้ (ตามขั้นตอนที่ 2) และคลิก OK ดังรูปที่ 8

รูปที่ 8 เลือก Name ที่เราสร้างไว้ เพื่อระบุว่า Data Validation ที่เราสร้างจะเอารายการจากไหนมาแสดง

3.8. คลิก OK ใน Pop Up: Data Validation

โดยบทความนี้ จะใช้ตัวอย่างให้ Matching คำภาษาอังกฤษ (รายการใน Drop Down List) ให้ตรงกับตัวเลข ตามรูปที่ 9

รูปที่ 9 แสดงตัวอย่างการสร้าง Data Validation ครั้งเดียวได้หลายเซลล์

เมื่อทำตามกระบวนการทั้ง 3 ขั้นตอนแล้ว หากภายหลังต้องการเพิ่มรายการในตาราง (Table1) ตามขั้นตอนที่ 1 ก็สามารถเพิ่มได้ทันที ไม่กระทบกับ Drop Down List ที่สร้างไปแล้วแต่อย่างใด และรายการที่คุณไปเพิ่มใหม่ ก็จะปรากฎใน Drop Down List ที่สร้างไปแล้วด้วย ดังรูปที่ 10

รูปที่ 10 แสดงตัวอย่าง Data Validation ที่สามารถเพิ่มรายการ Data Validation ได้

เพียงเท่านี้การสร้าง Drop Down List ก็เป็นเรื่องง่าย ถ้าอนาคตต้องมีรายการเพิ่มขึ้นมาใหม่ หวังว่าคงจะไม่ยากเกินไปนะคะ หากมีคำถามสามารถส่งมาสอบถามที่ e-mail ได้ค่ะ

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

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

BeHappyมีนาคม 10, 2561สร้าง drop down list, เอ็กเซล, Computer knowledge, Data-Management, Drop down list, excel 2 ความคิดเห็น

จากบทความก่อนเรื่อง "การสร้าง 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 จ้า

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

BeHappyมกราคม 26, 2561สร้าง drop down list, เอ็กเซล, Data-Management, Drop down list, excel data validate 6 ความคิดเห็น

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

4 ข้อในการสร้างแผนภูมิ Excel ให้อ่านง่ายและสวยงาม

BeHappyมกราคม 14, 2561กราฟ แผนภูมิ Excel, สร้าง กราฟ เอ็กเซล, chart excel คือ, Data-Management 2 ความคิดเห็น

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

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

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

5 ข้อเลือกแผนภูมิให้เหมาะกับข้อมูล
1. คุณต้องการเปรียบเทียบค่า (Value) หรือไม่
แผนภูมิที่เหมาะสมกับการเปรียบเทียบค่าตั้งแต่ 1 ค่าไปจนหลายๆ ค่า เช่น แผนภูมิคอลัมน์ (Column Chart) แผนภูมิแทง (ฺBar Chart) แผนภูมิพื้นที่ (Circular Area) แผนภูมิเส้น (Line Chart) แผนภูมิจุด/กระจาย (Scatter Chart) แผนภูมิผสม (Bullet Chart) เป็นต้น

2. คุณต้องการให้แสดงส่วนประกอบอะไรบ้าง
เช่น แผนภูมิวงกลม (Pie chart) แผนภูมิพื้นที่ (Area Chart) เป็นต้น

3. คุณต้องการดูการกระจายตัวของข้อมูลหรือไม่
แผนภูมิที่แสดงการกระจายตัวของข้อมูลจะทำให้คุณสังเกตเห็นข้อมูลที่มีความผิดปกติ แนวโน้มปกติ และช่วงของข้อมูล เช่น แผนภูมิจุด/กระจาย (Scatter Chart) แผนภูมิแทง (ฺBar Chart) แผนภูมิเส้น (Line Chart) แผนภูมิคอลัมน์ (Column Chart) เป็นต้น

4. คุณต้องการวิเคราะห์แนวโน้มของข้อมูลหรือไม่
แผนภูมิที่สามารถให้ข้อมูลของชุดข้อมูล (data set) ต่อช่วงเวลา เช่น แผนภูมิเส้น (Line Chart) แผนภูมิคอลัมน์ (Column Chart) เป็นต้น

5. คุณต้องการดูความสัมพันธ์ระหว่างกลุ่มข้อมูลหรือไม่
เช่น แผนภูมิเส้น (Line Chart) แผนภูมิจุด/กระจาย (Bubble/Scatter Chart) เป็นต้น

ตัวอย่าง แผนภูมิแท่ง กับ แผนภูมิโดนัท ใช้เปรียบเทียบข้อมูลที่เป็นหมวดหมู่ได้เหมือนกัน แต่ลักษณะของแผนภูมิแท่ง ทำให้อ่านข้อมูลแผนภูมิได้อย่างง่ายและเห็นแนวโน้มของข้อมูลได้ มากกว่าแผนภูมิโดนัท ที่เหมาะกับลักษณะข้อมูลที่มีหมวดหมู่หนึ่งใหญ่กว่าหมวดหมู่อื่นๆ มากและไม่ควรมีหมวดหมู่มากเกินกว่า 7 หมวด ดังรูปที่ 1-2

รูปที่ 1 แสดงตัวอย่างแผนภูมิโดนัท

รูปที่ 2 แสดงตัวอย่างแผนภูมิแท่ง

รูปที่ 3 ตัวอย่างเลือกแผนภูมิให้ผู้อ่านเห็นข้อมูลได้ง่าย

2. จัดเรียงข้อมูลเพื่อให้ดูข้อมูลได้ง่ายขึ้น

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

รูปที่ 4 แสดงแผนภูมิที่ยังไม่ได้จัดเรียงข้อมูล

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

รูปที่ 5 แสดงตัวอย่างแผนภูมิที่จัดเรียงข้อมูลก่อน

3. ควรปรับแต่งแกน X,Y ให้สั้น กระชับ และทำความเข้าใจได้เร็ว
จากรูปที่ 5 จะเห็นว่า เราสามารถทำให้แกน Y ไม่ยาวเกินไปด้วยการคลิกขวาที่แกน Y ของแผนภูมิ >> เลือก "Format Axis" >> เลือก "Custom" >> พิมพ์ [>999999] #,,"M";#,"K" ที่ช่อง Format Code ดังรูปที่ 6

รูปที่ 6 ตัวอย่างการกำหนดแกน y ให้แสดงตัวเลขไม่ยาว

จากรูปที่ 6 เป็นผลการกำหนดให้แกน y แสดงตัวเลขไม่ยาวเหมือนรูปที่ 5

นอกจากนี้ เรายังสามารถเปลี่ยนรูปแบบของแกน x (X axes) ที่เป็นแกนใช้แสดงประเภทข้อมูล (Category) ของแผนภูมิ เราสามารถปรับเปลี่ยนรูปแบบเพื่อให้ผู้อ่านทำความเข้าใจได้ง่ายมากขึ้น

ตัวอย่างที่ 1 การใช้ตัวย่อแทนชื่อเต็ม แบบนี้จะทำได้กับประเภทข้อมูลที่เป็น วัน เดือน ปี หรือชื่อเฉพาะต่างๆ

รูปที่ 7 ตัวอย่างการย่อชื่อประเภทข้อมูล

ตัวอย่างที่ 2 การจัดเรียงข้อความให้อ่านได้ง่ายขึ้น

รูปที่ 8 ตัวอย่างการจัดเรียงประเภทข้อมูลให้เอียง 45 องศา

รูปที่ 9 ตัวอย่างจัดเรียงประเภทข้อมูลให้เอียง 90 องศา

หรือเลือกแผนภูมิให้ผู้อ่านเห็นรายละเอียดของประเภทข้อมูลได้ง่ายขึ้น ดังรูปที่ 3 ด้านบน

4. การใส่รายละเอียดของแผนภูมิให้เหมาะสม
การที่จะนำแผนภูมิไปนำเสนอกับคนอื่นๆ เราควรใส่รายละเอียดที่จำเป็นของแผนภูมิ เช่น ชื่อแผนภูมิ (Chart Title) ชื่อแกน x ชื่อแกน y (Axis Title) เป็นต้น

2 วิธีใช้สร้างผลรวมย่อย (Subtotal) ในโปรแกรม Excel

BeHappyตุลาคม 13, 2560การจัดการข้อมูลในเอ็กเซล, การใช้งาน Subtotal outline excel, Data-Management ไม่มีความคิดเห็น

ฟังก์ชั่นในโปรแกรม Excel หนึ่งที่หลายคนรู้จักคงจะไม่พ้นฟังก์ชั่นที่เกี่ยวกับการหาผลรวมแน่นอน ซึ่งก่อนหน้านี้ เราได้อธิบายอย่าง การหาผลรวมด้วย SUM() / SUMIF() / SUMIFS ใน Excel หรือ SUBPRODUCT() ใช้ในการหาผลรวมของคอลัมน์ตั้งแต่ 2 คอลัมน์คูณกัน

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

SUBTOTAL Function
หมายถึง ฟังก์ชั่นที่ช่วยคำนวณเซลล์ย่อย ๆ ของผลรวมในตาราง โดยมีการทำอยู่ 2 แบบ ดังนี้

ไวยากรณ์

=Subtotal(function_num, ref1, [ref2], ...)

หลักการ

function_num คือ คุณต้องระบุตัวเลข ซึ่งมี 2 แบบ คือ 1-11 และ 101-111 เพื่อคืนค่าตาม Function ที่ต้องการ โดยเงื่อนไขในการกำหนดอยู่ในตารางที่ 1
ref1, [ref2], ... คือ คุณระบุเลข 1-254 การอ้างอิงเพื่อให้ฟังก์ชั่นหาผลรวมย่อยของข้อมูล

ตารางที่ 1 แสดงความหมายของตัวเลขใน Function_num

Function	คำนวณค่าที่ซ่อน	ไม่คำนวณค่าที่ซ่อน
AVERAGE	1	101
COUNT	2	102
COUNTA	3	103
MAX	4	104
MIN	5	105
PRODUCT	6	106
STDEV	7	107
STDEVP	8	108
SUM	9	109
VAR	10	110
VARP	11	111

หมายเหตุ

ในตาราง Function แต่ละเลข (เช่น 1 กับ 101 / 2 กับ 102 / ...) จะถูกจับคู่ไว้อยู่แล้ว เมื่อคุณเลือก
Function_num เป็นค่าระหว่าง 1-11 ฟังก์ชั่น SUBTOTAL จะคำนวณตัวเลขทุกตัว แม้ว่าตัวเลขจะถูกซ่อนไว้
Function_num เป็นค่าระหว่าง 101-111 ฟังก์ชั่น SUBTOTAL จะ ไม่ นำตัวเลขที่ถูกซ่อนไว้มาคำนวณ
แต่ถ้าคุณทำการกรองข้อมูลด้วย Autofilter แล้วฟังก์ชั่น SUBTOTAL จะไม่สนใจว่าคุณกำหนด Function_Num เป็นอะไร มันจะ ไม่ นำค่าในตารางที่คุณกรองข้อมูลนั้นมาคำนวณเด็ดขาด
ฟังก์ชั่น SUBTOTAL จะสามารถทำงานตามที่บอกไว้กับข้อมูลแนวตั้ง แต่ถ้าข้อมูลเป็นลักษณะแนวนอน (Horizontal) ฟังก์ชั่น SUBTOTAL จะนำค่าที่ซ่อนไว้มารวมด้วย

ตัวอย่างที่ 1 เขียนฟังก์ชั่นแบบ Command ดังนี้

รูปที่ 1 แสดงตัวอย่าง Subtotal

จากรูปที่ 1 ถ้าเขียน =SUBTOTAL(9,B1:B3) หรือ =SUBTOTAL(109,B1:B3) จะได้ผลลัพธ์เป็น 9 ทั้ง 2 ฟังก์ชั่น และมีความหมายว่าให้ทำการบวกค่าระหว่าง B1 ถึง B3 แต่ถ้าคุณซ่อน Row 2 ไป ผลลัพธ์ที่ได้จะเป็น 9 และ 5 ตามลำดับ

แบบที่ 2 ใช้ Subtotal แบบ Outline ดังนี้

รูปที่ 2 แสดงการทำ Subtotal แบบ Outline

ในการทำ Subtotal แบบ Outline จะช่วยให้คุณไม่ต้องมาเขียนสูตรที่ละส่วน แต่การจะใช้เครื่องมือตัวนี้ เราต้องมารู้ก่อนว่ามันมีคุณลักษณะการทำงานอย่างไรบ้าง

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

รูปที่ 3 แสดงการเตรียมข้อมูลให้เหมาะสมกับการทำ Subtotal แบบ Outline

ตามรูปที่ 3 เราได้ทำการเติมข้อมูลให้เต็มและจัดเรียงข้อมูล ในคอลัมน์ปี (Year) นั้นเอง

2. เลือกเซลล์ส่วนใดส่วนหนึ่งบนตาราง และ เลือกที่ Data >> Subtotal โปรแกรมจะแสดง PopUp ดังรูปที่ 4

รูปที่ 4 แสดง Subtotal outline

แต่ละส่วนของ Subtotal Popup มีดังนี้

At each change in: เป็นการเลือกคอลัมน์คุม ตารางที่ทำ Subtotal
Use Function: เป็นการเลือกว่าจะใช้ Function ใดในการทำ Subtotal
Add subtotal to : เป็นการเลือกคอลัมน์ที่ต้องการให้คำนวณ ตาม Function ที่เลือกไว้
Replace current subtotal เป็นการกำหนดให้สามารถเพิ่มผลรวมย่อยได้ โดยจะถูกใช้เมื่อคุณทำ Subtotal ไปก่อนหน้านี้แล้ว แต่ต้องการจะเพิ่ม Subtotal กับคอลัมน์อื่นเพิ่ม โดยไม่ให้กระทบกับข้อมูลที่ทำ Subtotal ไว้ก่อนหน้านี้นั้นเอง
Page break between groups เป็นการกำหนดให้แบ่งเป็นหน้า
Summary below data เป็นการกำหนดให้แสดงผลสรุปไว้ท้ายของตาราง

โดยในรูปที่ 4 คือการเลือกคอลัมน์ปี (Year) เป็นคอลัมน์คุมการแสดงผลรวมย่อย (Subtotal) ซึ่งจากการเติมข้อมูลให้เต็มทั้งตารางและจัดเรียงข้อมูลไว้ก่อนหน้านี้แล้ว คุณจะได้ข้อมูลผลรวมย่อยที่นำไปวิเคราะห์ได้ ดังรูปที่ 5

รูปที่ 5 แสดงผลลัพธ์การทำผลรวมย่อย (Subtotal - Outline)

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

นอกจากนี้ คุณยังสามารถทำการเพิ่ม Subtotal ที่ 2 ในตารางได้ โดยคลิกที่ Replace current subtotal แล้วเลือกคอลัมน์ที่ต้องการจะทำ Subtotal ที่ 2 ในส่วน Add subtotal to ดังรูปที่ 6

รูปที่ 6 แสดงตัวอย่างการทำผลรวมย่อยที่2 (Subtotal แบบ outline)

โดยในรูปที่ 6 จะทำการคลิกที่ Replace current subtotal และเลือกส่วนของ Add subtotal to: Product ผลลัพธ์ที่ได้เพิ่ม Subtotal ที่ 2 จะแสดงดังรูปที่ 7

รูปที่ 7 แสดงผลลัพธ์ของการทำผลรวมย่อยที่ 2 (Subtotal แบบ Outline)

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