Conditional Formatting การจัดรูปแบบข้อมูลด้วยการ Hilghtlight Cells ตอนที่ 1

ในบทความนี้ เรามาเรียนรู้ เทคนิคในการจัดการรูปแบบข้อมูลภายใน Excel ด้วย Conditional Formatting 

การจัดการรูปแบบของข้อมูลด้วย Conditional Formatting คือ ทำให้ข้อมูลที่เราสนใจ เป็นจุดที่น่าสนใจกว่าข้อมูลอื่นๆ ใน Sheet ไม่ว่าจะด้วยการ Hi-light เซลล์, การเน้นค่าสูงสุด-ค่าต่ำสุด, การใช้แถบสีเทียบกับข้อมูลมาก-น้อยตามลำดับ, การใส่สัญลักษณ์ต่างๆ ตามค่าข้อมูล เป็นต้น 

วิธีการใช้ Conditional Formatting

ไปที่หน้า Home Ribbon >> คลิกปุ่ม Conditional Formatting ดังรูปที่ 1

รูปที่ 1 แสดงวิธีการเลือกทำ Conditional Formatting ใน Excel

 เมื่อคลิกที่ปุ่ม Conditional Formatting แล้ว โปรแกรมจะแสดง Pop Up โดยจะมี Functions ให้ใช้ในการจัดการข้อมูล เพื่อให้เราสังเกตได้ง่ายๆ ดังรูปที่ 2

รูปที่ 2 แสดง Conditional Formatting Menu

จากตัวอย่างในบทความ "การกรองข้อมูล (Filter) ใน Excel" หรือ "การเรียงข้อมูล (Sort) ใน Excel" จะเห็นว่ามีการแบ่งสีให้กับประเภทวัตถุดิบต่างๆ 

ดังนั้น เราจะใช้ตัวอย่างนี้มาอธิบายถึงวิธีการเน้นข้อความด้วยวิธีการต่างๆ 

ตัวอย่างที่ 1 จะทำการจัดรูปแบบข้อความจาก "ตัวอักษร" เป็นเงื่อนไข
โจทย์ต้องการให้ Highlight เซลล์ เมนูอาหารโดยแยกสีตามประเภทวัตถุดิบ (เมนูที่มีวัตถุดิบหมู-ใช้พื้นหลังสีชมพู / ปู-สีส้ม / ไก่-สีฟ้า / กุ้ง-สีเขียว) ดังรูปที่ 3

รูปที่ 3 แสดง Conditional Formatting แบบ HightLight Cell ด้วยตัวอักษรมาเป็นเงื่อนไข 

วิธีการทำตัวอย่างที่ 1
1. เลือกเซลล์ทั้งหมดที่ต้องจัดรูปแบบ (ในที่นี้เลือก Cell: B2-B16) แล้วไปหน้า Home Ribbon >> คลิกปุ่ม Conditional Formatting
2. เลือก Hightlight Cells Rules >> More Rules.. จะแสดงหน้าจอ ดังรูปที่ 4

รูปที่ 4 แสดงหน้าจอ New Formatting Rule

3. โดยในส่วน Select a Rule Type: (ประเภทเงื่อนไข โดยในตัวอย่างที่ 1 นี้ให้เลือก Format only cells that contain)
4. ส่วน Edit the Rule Description: (การกำหนดเงื่อนไขในการแสดง) โดยที่ Drop Down List ด้านซ้ายให้เลือกเป็น Specific Text / Drop Down List ตรงกลางให้เลือกเป็น Greater than และ Drop Down List ด้านขวาให้เขียนคำที่ต้องการให้โปรแกรมเน้นข้อความ 
5. ส่วน Preview ให้คลิกปุ่ม Format เพื่อกำหนดรูปแบบของข้อความที่คุณต้องการ 
6. คลิก OK ดังรูปที่ 5

รูปที่ 5 แสดงตัวอย่างการกำหนดรูปแบบข้อความในเซลล์ที่มีคำว่า ปู อยู่ในชื่อ

ให้ทำแต่ละสีไล่ไปจนครบ หากต้องการปรับแก้ไขภายหลัง สามารถเข้าไปจัดการด้วยการคลิกที่ Manage Rules (ตามรูปที่ 2)

โปรแกรมจะแสดงหน้าจอเพื่อให้คุณแก้ไขรูปแบบต่างๆ ได้ ดังรูปที่ 6

รูปที่ 6 แสดงหน้าจอสำหรับแก้ไขรูปแบบของเงื่อนไขที่กำหนดไว้เดิม

☺☺☺☺

ตัวอย่างที่ 2 จะทำการจัดการรูปแบบข้อความด้วย "ตัวเลข" เป็นเงื่อนไข

โจทย์ต้องการ Highlight เซลล์ เมนูอาหารโดยแยกสีตามจำนวนยอดการสั่งต่อวัน ที่มากกว่า 3 ดังรูปที่ 7

รูปที่ 7 แสดง Conditional Formatting แบบ HightLight Cells โดยใช้ตัวเลขมาเป็นเงื่อนไข

วิธีการทำตัวอย่างที่ 2

1. เลือกเซลล์ทั้งหมดที่ต้องจัดรูปแบบ (ในที่นี้เลือก Cell: D2-D16) แล้วไปหน้า Home Ribbon >> คลิกปุ่ม Conditional Formatting

2. เลือก Hightlight Cells Rules >> Greater Than.. ดังรูปที่ 8

รูปที่ 8 แสดงช่องทางการทำตามตัวอย่างที่ 2

ให้ระบุตัวเลขที่ต้องการมาสร้างเงื่อนไข และระบุว่าต้องการให้ Format เป็นแบบใด ดังรูปที่ 9

รูปที่ 9 แสดง Greater Than 

เมื่อคลิก OK ก็จะได้ตามรูปที่ 7 ทันที

ตัวอย่างที่ 3 จะทำการจัดรูปแบบข้อมูลด้วย "วันที่" เป็นเงื่อนไข
โจทย์ต้องการ Hightlight วันที่ของสัปดาห์ถัดไปของวันปัจจุบัน (วันปัจจุบัน คือ 18/9/2017) ดังรูปที่ 10

รูปที่ 10 แสดงตัวอย่างการ Hightling ด้วยวัน

วิธีการทำตัวอย่างที่ 3
1. เลือกเซลล์ทั้งหมดที่ต้องจัดรูปแบบ (ในที่นี้เลือก Cell: D2-D16) แล้วไปหน้า Home Ribbon >> คลิกปุ่ม Conditional Formatting
2. เลือก Hightlight Cells Rules >> A Date Occurring.. 
3. โปรแกรมจะแสดงหน้าจอ A Date Occurring โดยในโจทย์ให้กำหนดเป็นเงื่อนไข สัปดาห์ถัดไปในวันปัจจุบัน ดังรูป 11

รูปที่ 11 แสดงหน้าจอ A Date Occurring เพื่อกำหนดวัดใน Conditional Formatting

4. คลิกปุ่ม OK ก็จะได้ตามรูปที่ 10 

นี้เป็นการจัดรูปแบบข้อมูลใน Excel แบบ HightLight Cells ตามเงื่อนไขแบบต่างๆ เช่น ตัวอักษร ตัวเลข วันที่ เป็นต้น แต่ Conditional Formatting ยังมีวิธีการจัดการรูปแบบข้อมูลอีกมาก ซึ่งเราจะนำเสนอในบทความถัดไป

💁💁💁

Read More

การกรองข้อมูลปะทะการซ่อน (Filter vs Hide) ในเอ็กเซล

คุณๆที่อ่านบทความ "การกรองข้อมูล (Filter) ใน Excel ด้วย Ribbon Data" คงพอจะนำวิธีการกรองข้อมูลแบบต่างๆ ไปประยุกต์ใช้กับงานได้บ้างนะคะ

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

ผลลัพธ์ที่เห็นอย่างชัดเจนของการทำงานทั้ง 2 ตัวนี้ คือ 

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

การซ่อน คือ การทำให้ข้อมูลถูกมองไม่เห็นนั้นเอง 

ความแตกต่างของทั้ง Filter และ Hide มี 2 เรื่อง ดังนี้ 

1. การคัดลอก (Copy) ข้อมูลที่ Filter/Hide ออกมา Paste ที่อื่น 

ผลที่ได้เมื่อ Copy ข้อมูลส่วนที่ Filter ไป ข้อมูลส่วนที่ถูกซ่อนไว้นั้นจะไม่ถูก Copy ไปด้วยนั้นเอง ซึ่งแตกต่างกับข้อมูลที่ Hide ไว้ จะถูก Copy ข้อมูลที่ซ่่อนไปอยู่ดี 

สรุป Copy ข้อมูลที่ Filter จะไม่สามารถ Copy ข้อมูลที่ถูกซ่อนได้ 
แต่ถ้าทำการ Copy ข้อมูลที่ Hide จะได้ข้อมูลที่ถูกซ่อนไปด้วย

2.  การใส่ค่า (Input) ไปที่ข้อความที่ซ่อนไว้

ผลที่ได้เมื่อทำการใส่ค่า (Input) ข้อมูลลง ตารางที่ Filter โปรแกรมจะข้ามบรรทัดข้อมูลที่ถูก Filter ไปบันทัดถัดไปทันที่ ซึ่งแตกต่างกับตารางที่ Hide ไว้ โปรแกรมจะให้เราใส่ค่า (Input) ข้อมูลนั้นลงตารางที่ Hide ทันที

จะเห็นว่าแม้ผลสัพธืที่ได้จะดูคล้ายกัน แต่ว่ากระบวนการทั้ง Copy และ Input ที่ได้จาก Filter/Hide กลับต่างกัน 

Read More

การกรองข้อมูล (Filter) ใน Excel

การกรองข้อมูลตามเงื่อนไข (Filter)

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

วิธีการ 

เข้าไปที่ Ribbon Data >> คลิก Filter ดังรูปที่ 1

รูปที่ 1 แสดงช่องทางการทำ Filter

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

การกรองด้วย Filter มีอยู่ 2 รูปแบบ คือ

แบบที่ 1 : การกรองค่าแบบอัตโนมัติ (Auto Filter) โดยมีวิธีกรองสามารถกรองได้จากทั้ง สี (Filter by Color) และ ข้อความ (Text Filter)

แบบที่ 2 : การกรองค่าแบบขั้นสูง (Advance Filter) จะขึ้นกับประเภทของข้อมูลในคอลัมน์นั้น ว่าเป็น Text Filters หรือ Number Filters 

แบบที่ 1 : การกรองค่าแบบอัตโนมัติ (Auto Filter)

แบบนี้เป็นวิธีการกรองแบบง่าย ไม่ยุ่งยาก ด้วยการคลิกที่เมนู Filter ดังรูปที่ 1 หรือ Short Cut : Ctrl + Shift + L 

ในบทความนี้ เราจะใช้ตัวอย่างจาก บทความการเรียงข้อมูล (Sort) ใน Excel ด้วย Ribbon Data ตารางรายการเมนูอาหาร (รูปที่ 2 ตารางด้านซ้าย) มาใช้อธิบาย 😉😉😊😊

ที่แถวบนสุดของคอลัมน์จะขึ้นรูปกรวย สัญลักษณ์การกรองข้อมูลอันตโนมัติ ดังรูปที่ 2 

รูปที่ 2 แสดงวิธีการ Auto Filter by Color

จากรูปที่ 2 ข้างบนนี้ โปรแกรมจะมอง Column รายการอาหารที่เลือกทำ Filter แล้วแสดง Cell Color ทั้งหมดจากคอลัมน์ทันที เพื่อให้คุณสามารถเลือกเรียงข้อมูลตาม Cell Color ได้

ตัวอย่างที่ 1 ต้องการกรองข้อมูล เฉพาะสีชมพู ที่มีราคา 30 บาท คุณจะกรองข้อมูลด้วยวิธีใด
ตอบ คลิก Ribbon Data >> เลือก Filter >> เลือกปุ่มสามเหลี่ยมที่คอลัมน์รายการอาหาร >> เลือก Filter by Color >> สีชมพู ดังรูปที่ 3

รูปที่ 3 แสดงตัวอย่างการเลือกรายการอาหารที่มี Filter สีชมพู

โปรแกรมจะแสดงข้อมูลเฉพาะสีที่คุณกรองมาแสดง ดังรูปที่ 4

รูปที่ 4 แสดงผลของการกรองด้วยสี

นอกจาก Auto Filter by Color ยังสามารถทำ Auto Filter by Text ได้ด้วยเช่นกัน ดังรูปที่ 5

รูปที่ 5 แสดงวิธีการ Auto Filter by Text

จากรูปที่ 5 โปรแกรมจะดู Column รายการอาหารที่เลือกทำ Filter แล้ว ให้คุณเลือกว่าจะกรองข้อความในคอลัมน์เป็นแบบไหน

ตัวอย่างที่ 2 ต้องการกรองข้อมูลรายการอาหาร โดยมีคำขึ้นต้นว่า "ข้าว" และ คำปิดท้ายว่า "ไก่" คุณจะกรองข้อมูลด้วยวิธีใด
ตอบ คลิก Ribbon Data >> เลือก Filter >> เลือกปุ่มสามเหลี่ยมที่คอลัมน์รายการอาหาร >> เลือก Text Filter >> เลือก Begins With.. และ Ends With...  ดังรูปที่ 6

รูปที่ 6 แสดง Custom AutoFilter PopUp

โปรแกรมจะแสดงข้อมูลเฉพาะสีที่คุณกรองมาแสดง ดังรูปที่ 7

รูปที่ 7 แสดงผลของการกรองด้วยตัวอักษร

แบบที่ 2 : การกรองค่าแบบขั้นสูง (Advance Filter)

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

วิธีการ   

1. Data >> Sort&Filter >> Advanced 

2. จะแสดง Advance Filter Pop Up ดังรูปที่่ 8

รูปที่ 8 แสดง Advance Filter Pop Up

โดยวิธีการกรองแบบขั้นสูงนี้ สามารถทำได้ออกมาเป็น 2 ลักษณะ 

การกรองขั้นสูงแบบ 1. กรองข้อมูล แสดงบนเซลล์เดิม โดยสามารถทำตาม ดังนี้

• Action เลือกเป็น Filter the list, in-place  
• List range เลือกเซลล์ที่ต้องการ Filter ข้อมูล
• Criteria range เลือกเซลล์ที่มีเงื่อนไข

การกรองขั้นสูงแบบ 2. กรองข้อมูล แสดงบนเซลล์ใหม่ โดยสามารถทำตาม ดังนี้

• Action เลือกเป็น Copy to another location 
• List range เลือกเซลล์ที่ต้องการ Filter ข้อมูล
• Criteria range เลือกเซลล์ที่มีเงื่อนไข
• Copy to เลือกเซลล์ที่จ้องการให้ข้อมูลที่ Filter ไปแสดงในเซลล์ใหม่

ตัวอย่างที่ 3 ต้องการกรองข้อมูลรายการอาหารลำดับที่ 1,12 กับ อาหารที่มีราคาอาหาร 30 บาท 
ตอบ สร้างตารางเงื่อนไขที่จะใช้กรองข้อมูล ในส่วนของ Criteria range ก่อน ดังรูปที่ 9

รูปที่ 9 แสดงตัวอย่างตารางเงื่อนไขที่จะใช้ในการกรองข้อมูล (Criteria range)

คลิก Ribbon Data >> ไปที่ Sort&Filter >> คลิก Advance >> จะมี Pop Up : Advance Filter ขึ้นมา >> ให้ทำตามการกรองขั้นสูงแบบ 1 ซึ่งใน Criteria range คุณไปเลือกตาราง ดังรูปที่ 9 ลงไป >> คลิก OK 


ผลลัพธ์ โปรแกรมจะกรองข้อมูลตามเงื่อนไข Criteria range และกรองข้อมูลทับตาราพื้นที่ตารางเดิม (เลือกมาเฉพาะข้อมูลรายงานอาหารลำดับที่ 1, 12 กับราคาอาหาร 30 บาท) ดังรูปที่ 10

รูปที่ 10 แสดงผลลัพธ์การกรองข้อมูลแบบ Advance Filter ตามตัวอย่างที่ 3

ตัวอย่างที่ 4 ต้องการกรองข้อมูล ดังนี้ 

• ราคาอาหาร มากกว่าหรือเท่ากับ 40 บาท กับ จำนวนยอดสั่งต้องมากกว่าหรือเท่ากับ 4 จานต่อวัน
• ลำดับที่ 1 ที่จำนวนยอดสั่งต้องมากกว่า 3 จานต่อวัน
• รายการอาหารเป็น ข้าวผัดผักสามสหาย ที่มียอดสั่งต้องมากกว่าหรือเท่ากับ 4 จานต่อวัน
• ลำดับที่ 13 

ตอบ สร้างตารางเงื่อนไขที่จะใช้กรองข้อมูล ในส่วนของ Criteria range ก่อน ดังรูปที่ 11

รูปที่ 11 แสดงตัวอย่างตารางเงื่อนไขที่จะใช้ในการกรองข้อมูล (Criteria range)

คลิก Ribbon Data >> ไปที่ Sort&Filter >> คลิก Advance >> จะมี Pop Up : Advance Filter ขึ้นมา >> ให้ทำตามการกรองขั้นสูงแบบ 2 ซึ่งใน Criteria range คุณเลือกตาราง (รูปที่ 11) >> คลิก OK ดังรูปที่ 12 

รูปที่ 12 แสดงตัวอย่างการกรองข้อมูลใน Pop Up : Advance Filter ตามตัวอย่างที่ 4

ผลลัพธ์ โปรแกรมจะกรองข้อมูลตามเงื่อนไข Criteria range และกรองข้อมูลในพื้นที่ใหม่ ดังรูปที่ 13

รูปที่ 13 แสดงผลลัพธ์การกรองข้อมูลแบบ Advance Filter ตามตัวอย่างที่ 4

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

Read More

การเรียงข้อมูล (Sort) ใน Excel

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


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

การเรียงข้อมูล (Sort)

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

การเตรียมข้อมูลให้อยู่ในลักษณะฐานข้อมูล (Database) แบบเบื้องต้น ต้องเป็นอย่างไร 
1. บรรทัดบนสุดของข้อมูลต้องเป็นชื่อหัวตาราง 
2. ต้องไม่มีข้อมูลที่ไม่เกี่ยวข้องกับหัวข้อคอลัมน์ เช่น คอลัมน์เงินเดือน ก็ไม่ควรจะมีข้อมูลเป็นตัวหนังสือในคอลัมน์เงินเดือน เป็นต้น 

การเรียงข้อมูล (Sort) มี 2 ประเภทใหญ่

แบบที่ 1. การเรียงแบบคอลัมน์เดียว 

แบบที่ 2. การเรียงแบบหลายชั้น ซ้อนกัน เช่น ต้องการเรียงคอลัมน์ด้วย รหัสพนักงาน กับ ชื่อพนักงาน เป็นต้น

วิธีการเรียงข้อมูลแบบที่ 1 : การเรียงแบบคอลัมน์เดียว

เข้าไปที่ Ribbon Data >> คลิกที่คอลัมน์ที่คุณต้องการให้เรียงข้อมูล >> คลิก AZ กรณีต้องการเรียงจากน้อยไปมาก หรือ คลิก ZA กรณีต้องการเรียงจากมากไปน้อย ดังรูปที่ 1

รูปที่ 1 แสดงเมนู เพื่อการเรียงข้อมูล ทั้ง 2 แบบ

ตัวอย่างที่ 1 ต้องการเรียงข้อมูลตารางด้านซ้าย ให้แสดงการเรียงข้อมูลเมนูอาหารที่มียอดการสั่งต่อวันมากที่สุด ซึ่งก็จะแสดงแบบตารางด้านขวา ดังรูปที่ 2

รูปที่ 2 แสดงผลการเรียงข้อมูลแบบที่ 1 

วิธีการเรียงข้อมูลแบบที่ 2  : การเรียงแบบหลายชั้น ซ้อนกัน

1. เข้าไปที่ Ribber Data >> คลิก Sort  ตามรูปที่ 1 (ด้านบน)

2. โปรแกรมจะแสดง Sort Dialog Box เพื่อให้คุณระบุคอลัมน์ที่จะใช้เป็นเงื่อนไขในการเรียงข้อมูล ดังรูปที่ 3

รูปที่ 3 แสดงหน้าจอของ Sort Dialog Box 

ให้คุณสร้างเงื่่อนไขที่คุณต้องการจะให้เรียง 

ตัวอย่างที่ 2 ต้องการเรียงข้อมูลรายการอาหาร (จากตารางด้านซ้ายมือ) ด้วยเงื่อนไขแรก คือ คอลัมนราคาให้เรียงจากจากราคาน้อยไปมาก และเงื่อนไขสอง คือ จำนวนยอดการสั่งต่อวัน ให้เรียงจาก จำนวนสั่งมากไปน้อย ก็จะได้รายการอาหาร (จากตารางด้านขวามือ) ดังรูปที่ 4

รูปที่ 4 แสดงผลจากการเรียงข้อมูล ตามแบบที่ 2

โดยใน Sort Dialog Box จะต้องกำหนดตาม รูปที่ 5

รูปที่ 5 แสดงวิธีการกำหนดเงื่อนไขตามตัวอย่างที่ 2 ใน Sort Dialog Box

จากทั้ง 2 ตัวอย่าง จะเห็นได้ว่า เครื่องมือใน Excel มีวิธีการในการเรียงข้อมูลได้หลายวิธีด้วยกัน ซึ่งคุณๆ ต้องทดลองเล่นเครื่องมือนี้ ตามโจทย์ต่างๆ แต่ถ้าคุณนึกโจทย์ไม่ออกให้ลอง ทำแบบฝึกหัดที่นี่

Read More