Excel แสดงเฉพาะข้อมูลที่ต้องการ

เทคนิคง่ายๆ กับการใช้ Excel : การหาค่าที่กัน

เทคนิคในการใช้งาน Microsoft Excel : การหาค่าที่ซ้ำกัน

    ใน Microsoft excel มีเครื่องมีที่ง่ายต่อการใช้งาน ซึ่งเราไม่ได้ใช้งานหรือมองข้ามไปในหัวข้อนี้เรามาทดลองใช้เมนูนี้กัน

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

    1. เลือกข้อมูลที่ต้องการ คลุมคอลัมน์ที่ต้องการ
    2. ไปที่ Ribbon หน้าแรก เลือก "การจัดรูปแบบตามเงือนไข"
    3. เลือกเมนูแรก "กฏการไฮไลต์เซลล์"
    4. เลือกเมนู "ค่าที่ซ้ำกัน" ตามภาพด้านล่างในรูปที่ 1 
    5. โปรแกรมจะแสดงกล่องคำสั่ง ให้เราเลือกใส่ระดับสีเพื่อจะแยกข้อมูลได้ชัดเจนขึ้น เช่น การเติมสีแดงอ่อนด้วยข้อความสีแดงเข้มขึ้น ตามตัวอย่างรูปภาพที่ 2 แล้วกดตกลง ก็จะแสดงข้อมูลที่ซ้ำกันมีสีแดงอ่อน

รูปที่ 1 แสดงลำดับการทำงาน

รูปที่ 2 แสดงการแสดงผลของการใช้งานเมนู

วิธีใช้คำสั่ง Data > Filter

Filter เป็นการกรองข้อมูลให้แสดงเฉพาะข้อมูลที่ต้องการเท่านั้นโดย Excel จะจัดการซ่อนบรรทัดของรายการอื่นให้เอง วิธีนี้ดีกว่าการสั่ง Sort เพราะไม่ทำให้โครงสร้างตารางเปลี่ยนไปจากเดิม แต่ต้องเสียเวลามาคลิกเลือกข้อมูลที่ต้องการกรองใหม่เป็นครั้งๆไป

แทนที่จะต้องเสียเวลาคลิกเข้าไปเลือกข้อมูลรายการที่ต้องการกรองใหม่ แนะนำให้เพิ่ม column ใหม่เพื่อใช้สูตร And หรือ OR ตรวจสอบรายการแต่ละบรรทัดว่าเป็นข้อมูลรายการที่ต้องการหรือไม่ จากนั้นให้เลือกกรอง column ใหม่นี้เพียง column เดียวตามข้อมูลที่คืนค่าออกมาเป็น True จะสะดวกต่อการใช้งานยิ่งขึ้น

หากต้องการคำนวณจากยอดที่เหลือจากการกรอง แนะนำให้ใช้สูตร =SubTotal(109,NumberRange) คู่กับการกรอง เพราะสูตรนี้จะแสดงยอดรวมของตัวเลขเฉพาะที่เห็น (Visible Cells Only) เท่าที่เหลือจากการกรองให้อีกด้วย และแทนที่จะใช้เลข 109 ยังมีเลขอื่นตั้งแต่เลข 101 - 111ซึ่งทำให้สูตร SubTotal นี้หายอดประเภทอื่นๆได้อีก เช่น

• เลข 101 จะทำหน้าที่เป็นสูตร Average
• เลข 102 จะทำหน้าที่เป็นสูตร Count
• เลข 103 จะทำหน้าที่เป็นสูตร CountA

นอกจากนี้หากสั่ง Copy ตารางข้อมูลที่เหลือจากการกรอง จะได้เฉพาะพื้นที่เท่าที่เห็นออกไป Paste เท่านั้น โดยไม่ติดพื้นที่ row ที่ถูกซ่อนตามไปด้วย

แม้การใช้คำสั่ง Data > Sort และ Data > Filter สามารถช่วยค้นหาข้อมูลที่ต้องการมาแสดงได้ง่าย ถ้าใช้คำสั่ง Sort ย่อมส่งผลทำให้ลำดับรายการของข้อมูลเดิมเปลี่ยนไป ต่างจากการสั่ง Filter ซึ่งไม่กระทบกับลำดับข้อมูลที่เก็บไว้แต่อย่างใด ดังนั้นการใช้ Filter จึงเหมาะกว่าการสั่ง Sort แต่โปรดสังเกตว่าผลจากการใช้คำสั่งดังกล่าวจะแสดงข้อมูลที่ต้องการค้นหาให้เห็นในบริเวณพื้นที่ของตารางเดิมเท่านั้น หากต้องการนำข้อมูลที่ต้องการค้นหาไปแสดงที่อื่น จำเป็นต้องอาศัยสูตร VLookup หรือใช้คำสั่ง Advanced Filter

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

• การ Sort
  • ต้องการ Sort คอลัมน์เดียว
  • ต้องการ Sort แบบเป็นลำดับขั้นหลายๆ ชั้นซ้อนกัน
  • เมื่อเรียงไปมาแล้ว จะยกเลิกการเรียง ให้กลับเรียงแบบตอนแรกยังไง?
  • ถ้าหัวตารางมีหลายบรรทัดทำยังไง?
• การ Filter
  • วิธีสังเกตว่ามีการกรองข้อมูลเกิดขึ้น
  • การกรองด้วย Filter มีอยู่ 2 ลักษณะ ดังนี้
  • Filter VS Hide

การ Sort

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

เครื่องมือในการ Sort ข้อมูลนั้น  แรกสุดควรเตรียมข้อมูลให้อยู่ในลักษณะ Database ซะก่อนโดยที่เราสามารถกดใช้เครื่องมือ Sort ซึ่งเลือกได้ 2 ที่ คือ

1. ที่ [Home] –> Editing –> Sort & Filter –> เลือกการ Sort ที่ต้องการ
2. ที่ [Data] –> Sort & Filter –> เลือกการ Sort ที่ต้องการ

โดยการ Sort ข้อมูลมีอยู่ 2 ประเภทใหญ่ๆ คือ

ต้องการ Sort คอลัมน์เดียว

แบบนี้เป็นแบบ Basic สุด คือ ไม่สนใจว่าคอลัมน์อื่นจะเรียงยังไง ซึ่งเทคนิคนี้สามารถใช้ปุ่มสี่เหลี่ยม Filter ช่วยในการ Sort ได้ (อย่างที่บอกไปแล้วตอนต้นว่าเครื่องมือ Filter มัน Sort ได้ด้วย) ซึ่งการ Filter นั้นเลือกได้ 2 ที่ คือ

1. ที่ [Home] –> Editing –> Sort & Filter –> Filter
2. ที่ [Data] –> Sort & Filter –> Filter
   • ถ้าข้อมูลเป็น Text จะมีให้เลือก Sort A->Z และในทิศกลับกัน
   • ถ้าข้อมูลเป็น Number จะมีให้เลือก Smallest to Largest และในทิศกลับกัน

การ Sort ตรวจหาสิ่งผิดปกติ

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

ถ้า Sort แล้วเจอข้อมูลแปลกๆ ก็ควรที่จะกลับไปตรวจสอบแล้วจัดการข้อมูลตั้งแต่ต้นตอว่าเกิดจากอะไร เพราะหากไม่ตรวจให้ดีก่อน คุณอาจจะทำงานแบบสูญเปล่าไปอีกหลายชั่วโมงเลยก็ได้ (Garbage in, Garbage Out)

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

ต้องการ Sort แบบเป็นลำดับขั้นหลายๆ ชั้นซ้อนกัน

เช่น ถ้ามีคอลัมน์ A B C มีข้อมูลดังนี้

แล้วผมอยากได้ผลลัพธ์สุดท้ายว่า

• ให้เรียงรหัสพนักงานขาย (A) จากน้อยไปมาก
• ถ้าเป็นพนักงานคนเดียวกันให้เรียงวันที่ขาย (B) จากก่อนไปหลัง
• ถ้าสุดท้ายยังเป็นวันเดียวกันอีก ให้เรียงยอดขายแต่ละรายการ (C) จากยอดขายมากไปน้อย

ใช้เครื่องมือ Sort บน Ribbon

วิธีนี้เป็นการใช้ Tool สำเร็จรูปมาช่วย ให้ไปที่ [Data] –> Sort & Filter –> Sort โดยที่แต่ละ Level ที่จะ Sort เราจะต้องเลือกว่าจะเป็น Sort by คอลัมน์อะไร เรียงโดยอะไร (มักจะเป็น Value) และเรียงจากน้อยไปมากหรือมากไปน้อย โดยที่สามารถกด Add Level เพื่อเพิ่มลำดับการ Sort ได้

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

ซึ่งจะเห็นว่าลำดับการเรียงจะต้องทำตรงกันข้ามกับวิธีใช้ปุ่ม Filter นะครับ

เมื่อเรียงไปมาแล้ว จะยกเลิกการเรียง ให้กลับเรียงแบบตอนแรกยังไง?

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

เทคนิคที่จะช่วยให้ Sort กลับมาเป็นแบบเดิมได้คือ ต้องสร้างคอลัมน์เพิ่ม 1 คอลัมน์ แล้วใส่เลข Running แบบการเรียงตั้งต้นเอาไว้ก่อน เพื่อที่จะได้เรียงกลับมาเหมือนเดิมได้หากต้องการภายหลัง ซึ่งคุณสามารถใช้ Fill Handle มาช่วยทำเลข Running ได้อย่างรวดเร็วครับ

ถ้าหัวตารางมีหลายบรรทัดทำยังไง?

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

วิธีแก้คือ ให้เลือกเฉพาะ Cell ที่เป็นหัวตารางจริงๆ ของเราบรรทัดสุดท้ายก่อนจะกด Filter (ในที่นี้ ตัวอย่างของผมคือบรรทัดที่ 3 ช่อง A3:C3) หรือจะให้ง่ายก็สามารถกดเลือกทั้งบรรทัดเลยก็ได้ แล้วค่อยกด Filter จะได้ผลว่าปุ่ม Filter โผล่ออกมาที่บรรทัดที่ต้องการได้อย่างถูกต้อง

การ Filter

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

เมื่อเตรียมข้อมูลเป็น Database แล้ว ให้ Click ที่ช่องใดช่องหนึ่งของตารางข้อมูล (เช่น A1) แล้วกดใช้เครื่องมือ Filter ซึ่งเลือกได้ 2 ที่ คือ

1. ที่ [Home] –> Editing –> Sort & Filter –> Filter
2. ที่ [Data] –> Sort & Filter –> Filter

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

เราจะลองทำการกรองดู โดยให้ลองเลือกให้ติ๊กถูกเหลือแค่ Food โดยให้ติ๊ก Select  All ออกก่อน แล้วค่อยกดเลือก Food และ/หรืออย่างอื่นที่ต้องการ แล้วกด Ok

วิธีสังเกตว่ามีการกรองข้อมูลเกิดขึ้น

จะเห็นว่าเมื่อกรองข้อมูลแล้ว จะมีเครื่องหมายกรวยกรอง โผล่ขึ้นมาที่คอลัมน์ที่มีการกรองเกิดขึ้น รวมถึงจะเห็นว่า Row Number จะหลายเป็นสีฟ้า แถมเลขก็มีการข้ามลำดับด้วย

การกรองด้วย Filter มีอยู่ 2 ลักษณะ ดังนี้

1. การกรองค่าแบบเจาะจง :เวลากดแล้วมีCheck Boxอันไหนที่ไม่อยากเห็น ก็ติ๊กออกได้
2. การกรองค่าแบบกำหนดเงื่อนไข จะขึ้นกับประเภทของข้อมูลในคอลัมน์นั้น

Text Filters 

จะเลือกได้กรณีข้อมูลเป็น Text ซึ่งมีเงื่อนไขให้เลือกดังนี้

Number Filters 
ถ้าข้อมูลเป็น Number จะมีให้เลือก Number Filter ซึ่งเลือกเงื่อนไขได้ดังนี้

Filter ด้วยสี (Filter by Color)

นอกจากนี้เรายังสามารถใช้การ Filter ด้วยสี (Filter by Color) ได้ด้วย อันนี้มีประโยชน์เวลานั่งตรวจงานแล้วทำ Highlight สีบางช่องที่สนใจเอาไว้ ทำให้สามารถเช็คหรือหาตอนหลังได้ง่ายขึ้น

โดยมันจะดูว่าเรามีการใส่สี Fonts หรือพื้นหลังแบบไหนบ้าง แล้วมันจะขึ้นมาให้เลือกตามนั้นเลย

Filter VS Hide

Filter กับ Hide ก็เป็นการทำให้ข้อมูลมองไม่เห็นเหมือนกัน แต่มีจุดที่ต่างกันพอสมควร แต่ประเด็นที่สำคัญเวลาเราทำงานกับมันมีอยู่ 2 เรื่องที่เจอบ่อยๆ คือ 1. การ Copy ข้อมูลออกมา Paste ที่อื่น กับ 2. การ Input ข้อมูลหรือสูตรลงไปในบริเวณข้อมูลที่ Filter/Hide

การ Copy ข้อมูลออกมา Paste ที่อื่น

หากเรา Filter ข้อมูลแล้ว Copy ไป Paste ที่อื่น ข้อมูลที่ถูกกรองทิ้งจะไม่ถูก copy ไปด้วย แต่ข้อมูลที่ถูก Hide ไว้ยังถูก Copy ไปอยู่ดี (เหมือนกับตอนปกติที่ไม่มีการ Hide)

Filter	Hide
ข้อมูลมีการ  “Filter เอานาย ก ออกไป” แล้ว Copy/Paste ไปไว้ที่อื่น	ข้อมูลมีการ  “Hide เอานาย ก ออกไป” (ไม่มีการ Filter) แล้ว Copy/Paste ไปไว้ที่อื่น
จะเห็นว่ามีเส้นประขึ้นมาเป็นกรอบหลายๆ ชุด (เส้นประมีการแบ่งแยกบริเวณกัน) ซึ่งบ่งบอกว่า มีการแบ่งข้อมูลออกเป็นหลายก้อนเมื่อ Paste ลงที่อื่นแล้ว จะเหลือแค่ข้อมูลที่มองเห็นหลัง Filter จริงๆ	จะเห็นว่ามีเส้นประขึ้นมาเป็นกรอบเพียงชุดเดียว (เส้นประไม่แบ่งแยกบริเวณ) ซึ่งบ่งบอกว่า ข้อมูลมีเพียงก้อนเดียวเมื่อ Paste แล้วข้อมูลที่เคยถูก Hide อยู่ก็กลับถูกแสดงออกมา ไม่เหมือนการใช้วิธี Filter ครับ

การ Input ข้อมูลลงไป

หากเรา Filter ข้อมูลแล้ว Input ข้อมูลลงไปในบริเวณข้างเคียง แล้วลาก Fill Handle หรือ Copy สูตรลงมา มันจะข้ามบรรทัดที่ข้อมูลถูกกรองทิ้งไป คือมีการกรอกข้อมูลแต่บรรทัดที่มองเห็นอยู่เท่านั้น แต่ข้อมูลที่ถูก Hide ไว้จะถูกกรอกข้อมูลหรือสูตรอยู่เช่นเดิม (เหมือนกับตอนปกติที่ไม่มีการ Hide)

Filter	Hide
ข้อมูลมีการ  “Filter เอานาย ก ออกไป” จากนั้นมีการกรอกข้อมูลข้างๆ แล้วลาก Copy ลงมา	ข้อมูลมีการ  “Hide เอานาย ก ออกไป” (ไม่มีการ Filter) จากนั้นมีการกรอกข้อมูลข้างๆ แล้วลาก Copy ลงมา
กรอกข้อมูลตอน Filter เอาไว้ หลัง Clear Filter	กรอกข้อมูลตอน Hide เอาไว้ หลัง Unhide
จะเห็นข้อมูลขึ้นมาเฉพาะบรรทัดที่มองเห็นข้อมูล (ไม่ได้ถูกกรอกทิ้ง) เท่านั้น	จะเห็นข้อมูลขึ้นมาทุกบรรทัดตามปกติ (ที่บรรทัดที่ 2 ไม่ขึ้น เพราะตอนกรอกข้อมูล ผมเริ่มที่บรรทัดที่ 3 แล้วลากลง)

แชร์ความรู้ให้เพื่อนๆ ของคุณ