วิธีการกรองข้อมูล แบบ selection

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

เปิดหน้าข้อมูลที่เราต้องการจะกรองขึ้นมา คลิกเลือก Sort & Filter > แล้วเลือก Filter

เมื่อเราเลือก Filter แล้วจะเห็นว่าส่วนหัวของคอลัมน์จะมีเลือกศรเพิ่มเข้ามา ทำการคลิกลูกศรแล้วเลือกข้อมูลที่เราต้องการให้แสดง

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

เครื่องมือ Filter (โดยเฉพาะแบบ Basic) ถือว่าเป็นทักษะพื้นฐาน ที่บริษัท/โรงงาน คาดหวังให้คนทำงานเกี่ยวกับ Excel ทำได้ ดังนั้นใครยังไม่เข้าใจหรือยังใช้ไม่เป็น ผมอยากให้ดูวีดีโอชุดนี้ครับ

ไฟล์ประกอบ : inwexcel-filter-practice (.Xlsx)

กรองข้อมูลใน Excel ตอนที่ 1/2 : Filter & Sort

ประเด็นสำคัญ ของ Filter

• เวลาจะ Filter ข้อมูล ควรเตรียมข้อมูลให้บรรทัดบนสุดเป็นหัวตาราง ไม่ใช่ตัวข้อมูลจริง
• กด Filter ได้ 2 ที่ คือ
  • ที่ Ribbon Home => บริเวณ Editing เลือก Sort & Filter => Filter
  • ที่ Ribbon Data => บริเวณ Sort & Filter เลือก Filter
• เวลากด Filter แล้วจะมีปุ่มสี่เหลี่ยมขึ้นมาที่หัวตาราง เราสามารถกดปุ่มนั้นเพื่อเข้าสู่เมนูการกรองข้อมูล
• เวลากดปุ่มกรองที่คอลัมน์ไหน มันก็จะกรองข้อมูลโดยใช้หลักเกณฑ์จากคอลัมน์ที่เรากำลังกด
  • ถ้าจะคัดกรองค่าแบบเจาะจง เวลากดแล้วมี Check Box อันไหนที่ไม่อยากให้เห็นให้ติ๊กออกได้เลย
  • ถ้าจะคัดกรองค่าแบบกำหนดเงื่อนไข
    • ถ้าข้อมูลเป็น Text จะมีให้เลือก Text Filter ซึ่งเลือกได้ว่าจะเอาช่องที่มีข้อความ xxx, ขึ้นต้นด้วยคำนี้, ลงท้ายด้วยคำนี้ เป็นต้น
    • ถ้าข้อมูลเป็น Number จะมีให้เลือก Number Filter ซึ่งเลือกได้ว่า  เอาค่าที่มากกว่าเท่าไหร่ น้อยกว่าเท่าไหร่ หรือจะแสดง Top10 ของข้อมูลก็ได้ เป็นต้น
• Filter ต่างจาก Hide ตรงที่หากเรา Copy ไป Paste ที่อื่น ข้อมูลที่ถูกกรองทิ้งจะไม่ถูก copy ไปด้วย (แต่ข้อมูลที่ Hide ไว้ยังถูก Copy ไป)

ประเด็นสำคัญ ของ Sort

หาก Sort จากคอลัมน์เดียว ให้กดที่ปุ่มสี่เหลี่ยมที่หัวตาราง จะง่ายกว่า โดย

• ถ้าข้อมูลเป็น Text จะมีให้เลือก Sort A->Z และในทิศกลับกัน
• ถ้าข้อมูลเป็น Number จะมีให้เลือก Smallest to Largest และในทิศกลับกัน

หากต้องการ Sort แบบเป็นลำดับขั้น ให้เลือกที่เครื่องมือ Sort ที่อยู่บน Ribbon ข้างๆเครื่องมือ Filter

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

กรองข้อมูลใน Excel ตอนที่ 2/2 : Advanced Filter

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

ประเด็นสำคัญ ของ Advanced Filter

• สามารถใช้เครื่องมือได้ที่ Ribbon Data => บริเวณ Sort & Filter เลือก Advanced
• เลือก Location ได้ ว่า
  • จะ Filter อยู่กับที่ (Filter the list, in-place) หรือ
  • จะเอาข้อมูลที่กรองแล้ว Copy ออกไปไว้ที่อื่นเลย (Copy to another location แต่ต้องเป็นชีทเดียวกัน)
    • ตรงนี้สามารถไปสร้างหัวตาราง ที่เรียงลำดับและจำนวนอาจไม่เหมือนต้นฉบับได้ เครื่องมือมันจะโปรยข้อมูลลงให้ถูกช่องเอง
• List Range ให้เลือก Data Source ของเรา
• Criteria Range ให้เลือกว่าเรามีเงื่อนไขการคัดกรองอย่างไร (ต้องไปสร้างเป็นตารางเงื่อนไขเพิ่ม ดูรายละเอียดได้ในวีดีโอครับ)
• Unique Records Only เอาไว้ติ๊กหากต้องการกำจัดข้อมูลที่ซ้ำกันออก

0

0

ปกติเวลาใส่เลข Running ให้กับข้อมูล 1,2,3,4… หากข้อมูลถูก Filter เลข Running เหล่านั้นอาจจะขาดตอน เช่น 1,3,4,6 แต่เทคนิคนี้สามารถให้เลขยังคงแสดงเป็น 1,2,3,4 ได้ แม้จะถูก Filter แล้วก็ตาม ทำยังไง มาดูกันครับ!

วีดีโอสอนการใช้งาน

สรุปเทคนิคที่ใช้

• เคล็ดลับอยู่ที่การใช้ Function SUBTOTAL ซึ่งมีพฤติกรรมเฉพาะตัวอย่างนึงที่เหมาะกับปัญหาของเรามาก นั่นคือ SUBTOTAL จะทำการคำนวณเฉพาะข้อมูลที่ยังคงมองเห็นอยู่หลังถูก Filter ไปแล้ว
• ด้วยเหตุนี้ เราจึงใช้ SUBTOTAL แล้วเลือก Function ย่อยเป็น COUNTA (รหัส 3) เพื่อให้นับจำนวนข้อมูลจากแถวแรกถึงแถวที่จะใส่เลขลำดับนั้นๆ เพื่อให้รู้ว่ามีข้อมูลกี่อันหลังถูก Filter ไปแล้วนั่นเอง
  • เช่น สมมติเราจะใส่เลขลับดัยในคอลัมน์ A ในแถวที่ 20 ก็จะเขียนสูตรว่า =SUBTOTAL(3,B2:B20) นั่นเอง โดยคอลัมน์ B ต้องมีข้อมูลอยู่ ห้ามเป็นช่องว่าง
  • ที่นี้เพื่อให้ copy สะดวก เราจึงทำการ Lock ช่องเริ่มต้นเอาไว้ เช่น =SUBTOTAL(3,B$2:B20)
• แต่เทคนิคสำคัญอีกอย่างคือ หากเราใช้ Subtotal ตรงๆ Excel มันจะเกิดอาการเอ๋อ ไม่ยอมเอาแถวสุดท้ายมา Filter ด้วย วิธีแก้คือให้เอาผลที่ได้จาก SUBTOTAL ไปคูณ 1 อีกที
  • จะได้ว่า =SUBTOTAL(3,B$2:B20) *1 นั่นเอง ครับ

ขอบคุณสำหรับคำถาม

เทคนิคนี้ ถูกสอบถามโดยคุณ Farzeed Phewkam ใน Facebook Page ของ inwexcel ซึ่งมีประโยชน์มากครับ ขอบคุณมากสำหรับคำถามดีๆครับ

0

0

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

การ Sort

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

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

แบบนี้ ok เลย

เพราะไม่มีหัวตาราง

เพราะรอบๆ ตารางมีข้อมูลที่ไม่เกี่ยวข้องติดอยู่

โดยที่เราสามารถกดใช้เครื่องมือ 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)

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

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

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

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

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

ผมสามารถทำได้ 2 วิธี คือ 1.ใช้ปุ่มสี่เหลี่ยม Filter ช่วย Sort และ 2.ใช้เครื่องมือ Sort บน Ribbon

ใช้ปุ่มสี่เหลี่ยม Filter ช่วย Sort

วิธีนี้ต้องกด Sort หลายรอบ ขึ้นอยู่กับว่าจะเรียงกี่คอลัมน์ โดยให้กด Sort ที่ตัวลำดับความสำคัญอันรองๆ ก่อน แล้วค่อยกด Sort ตัวที่เป็นลำดับความสำคัญหลักใน Step หลังสุด นั่นคือ

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

ใช้เครื่องมือ 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อันไหนที่ไม่อยากเห็น ก็ติ๊กออกได้
   • หากมี Choice ให้เลือกเยอะจนเลือกไม่ไหว อาจใช้เครื่องมือ Search ใน Filter มาช่วย เช่น ผมพิมพ์คำว่า f ลงไปในช่อง search จะขึ้นทุก Choice ที่มีคำว่า f อยู่
     
     
   • จากนั้นพอกด ok ก็จะ Filter เหลือแต่สิ่งที่เลือกไว้
   • หากคุณกด Filter ใหม่ คราวนี้ลองเปลี่ยนคำบ้าง พอกด ok มันก็จะทับผลการ Filter เดิมที่ได้เคยทำไว้เลย
     Tips : แต่ถ้าหากคุณอยากจะให้มัน add item เพิ่มจากรายการเดิมที่เลือกไว้แล้ว ให้ติ๊กช่อง Add current selection to filter ด้วย เช่น
     
     

1. การกรองค่าแบบกำหนดเงื่อนไข จะขึ้นกับประเภทของข้อมูลในคอลัมน์นั้น

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)

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

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

0

0

อย่างที่ผมเคยบอกไปแล้วว่าการแก้ปัญหาหนึ่งๆ ใน Excel นั้นสามารถทำได้หลายวิธีมาก ซึ่งปัญหาการนับข้อมูลแบบไม่ซ้ำกันในคอลัมน์ก็เช่นกัน จะมีวิธีไหนบ้างมาดูกันครับ!!

สมมติว่าเรามีข้อมูลอยู่ตารางหนึ่ง เป็นตารางการขายของในช่วงเดือนที่ผ่านมา…
ลักษณะข้อมูลคือ 1 บรรทัดเป็น 1 order ซึ่งแต่ละ order ก็จะมี Sales นักขายกำกับอยู่ว่าชื่ออะไร

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

สมมติข้อมูลเป็นแบบนี้ (มีแค่ 10 บรรทัดพอ) => distinct-count

ถ้าเราเจอปัญหาแบบนี้ จะแก้ไขได้ยังไงมาดูกันครับ!

วิธีที่ 1 : ใช้ PivotTable

วิธีนี้ง่ายสุดๆ นั่นคือให้สร้าง PivotTable ลากสิ่งที่เราต้องการจะนับแบบไม่ซ้ำไปที่ Row Label แค่นี้มันก็จะ List item แบบไม่ซ้ำกันมาให้แล้วครับ ซึ่งถ้าจะนับ จะลากเม้าส์แล้วดูที่ Status Bar หรืออาจใช้ COUNTA มาช่วยอีกทีก็ได้ครับ ซึ่งจะได้ 4 คนครับ

แต่วิธีนี้ก็มีข้อเสียคือ ต้องคอย Refresh Pivot หากข้อมูล Source Data เปลี่ยนนั่นเองครับ

จะลาก item มานับจำนวนว่าแต่ละ sales มี order กี่ครั้งด้วยก็ได้

วิธีที่ 2 : ใช้ Remove duplicates

วิธีนี้ค่อนข้างลูกทุ่งครับ ให้ Copy คอลัมน์ที่ต้องการออกมา แล้วไปที่เครื่องมือ [Data] –> Data Tools –> Remove Duplicates จากนั้น ok เป็นอันจบ

วิธีที่ 3 : ใช้ Advanced Filter ก็ได้

วิธีนี้ง่ายเหมือนกัน คือ ไปที่ [Data] –> Sort & Filter –> Advanced แล้ว ต้องติ๊ก Unique records only แล้วเลือก copy to โดยใส่ชื่อ Field ที่ต้องการจะ list แบบไม่ซ้ำ

แต่มีข้อเสียคือ ถ้าข้อมูลเปลี่ยน ต้องกดใหม่ทุกครั้ง ซึ่งในระยะยาวถือว่าไม่ค่อยเหมาะครับ

วิธีที่ 4 : ถ้ายอมให้ Sort ได้ใช้ IF ก็ได้นะ

วิธีนี้ค่อนข้างง่ายครับ แต่มีข้อจำกัดคือ คุณต้องเรียงคอลัมน์ที่คุณจะนับซะก่อนครับ

พอเรียงแล้ว เราก็แค่เช็คว่า “ถ้าช่องที่อยู่ติดกันข้างบนมันซ้ำกันกับบรรทัดตัวเอง ก็ไม่ต้องนับ”
พอนึกถึงคำว่า ถ้า เราก็ใช้ IF มาเช็ค เช่นดังนี้ครับ

วิธีที่ 5 : ถ้าไม่ให้ Sort ก็ใช้ COUNTIF ซิ

ถ้าข้อมูลของเราไม่สามารถ Sort ได้ เช่น ถ้า Sort ปุ๊ป VLOOKUP พัง หรือ Sort แล้วทำให้กรอกข้อมูลลำบาก เราก็ต้องใช้วิธีอื่น เช่น COUNTIF ครับ

ซึ่ง COUNTIF จะเป็นการนับว่าใน range มีคำที่เราสนใจ (ตั้งว่าเป็น criteria) อยู่กี่ตัว?

หากเราลองเขียนดูจะได้ดังนี้

มันก็นับออกมาจริงๆ นั่นแหละว่าแต่ละบรรทัดมีตัวซ้ำกันกี่ตัว…
เช่น
สมเทพ แต่ละบรรทัดจะนับได้ 4 ตัว ซึ่งเลข 4 นี้ก็จะโผล่ขึ้นมา 4 รอบ ทั้งๆ ที่จริงๆ ต้องนับแค่ 1 เอง
สมหญิง แต่ละบรรทัดจะนับได้ 2 ตัว ซึ่งเลข 2 นี้ก็จะโผล่ขึ้นมา 2 รอบ ทั้งๆ ที่จริงๆ ต้องนับแค่ 1 เอง

แล้วเราจะกำจัดตัวที่เกินยังไงดี?

ถ้า 4 + 4 + 4 + 4 เราต้องทำให้เป็น แค่ 1… แปลว่าแต่ละอันเราต้องหารด้วย 4 ถึง 4 ครั้งนั่นเอง ( 4×4 หรือ 4^2)

= E2/(E2^2) = 1/E2 หรือ = 1/COUNTIF($C$2:$C$11,C2) นั่นเองครับ

ซึ่งพอ SUM รวมกันก็จะได้ 4 อยู่ดี!

แต่จะเห็นว่าวิธีนี้เอาไว้หาจำนวนนับว่ามีกี่ตัวที่ไม่ซ้ำ แต่ไม่สามารถคัดเลือกมาโชว์มาอย่างละบรรทัดได้ (ไม่ได้ flag 1, 0 แบบการใช้ IF ข้างบน)

วิธีที่ 6 : ใช้ COUNTIF แบบ Array Formula

วิธีนี้คล้ายๆ กับวิธีข้างบนแหละครับ ถ้าสังเกตุคือ วิธีข้างบน = 1/COUNTIF(xxx) แล้วเอาผลลัพธ์มาบวกกัน

แต่แทนที่เราจะให้ผลลัพธ์มันแสดงแยกช่องกัน แล้วค่อยเอามาบวกทีหลัง คราวนี้เราจะใช้การเขียนสูตรแบบ Array เพื่อให้ทุกอย่างอยู่ในช่องเดียว เช่น =1/COUNTIF(data_range,data_range)

=1/COUNTIF(C2:C11,C2:C11)
ไอ้ตรงสีแดงเนี่ย ปกติต้องการแค่ค่าเดียว แต่นี่เราใส่เป็น Range เลยเพื่อให้ได้ค่าแบบ Array

ถ้าลองลากแล้วกด F9 จะเห็นแบบนี้ครับ
=1/{4;4;2;2;4;2;4;2;2;2}
={0.25;0.25;0.5;0.5;0.25;0.5;0.25;0.5;0.5;0.5}

นั่นแสดงว่าถ้าเรารวบตอนจบด้วย SUM ก็จะได้ 4 เช่นกัน นั่นคือ =SUM(1/COUNTIF(C2:C11,C2:C11)) แต่เราต้องกด Ctrl+Shift+Enter ด้วยเพราะเป็นการเขียนสูตรแบบ Array Formula

แต่ถ้าเราเปลี่ยน SUM เป็น SUMPRODUCT เราจะกด Enter ได้ตามปกติ เพราะ SUMPRODUCT รองรับการเขียนแบบ Array ในตัวอยู่แล้ว

ถ้างั้นผมก็สามารถเขียนสูตรแบบ Array ได้ว่า  =SUMPRODUCT(1/COUNTIF(data_range,data_range)) หรือ =SUMPRODUCT(1/COUNTIF(C2:C11,C2:C11)) ก็จะได้ 4 เช่นกันครับ

แต่จะเห็นว่าวิธีนี้เอาไว้หาจำนวนนับว่ามีกี่ตัวที่ไม่ซ้ำ แต่ไม่สามารถคัดเลือกมาโชว์มาอย่างละบรรทัดได้ (ไม่ได้ flag 1, 0 แบบการใช้ IF ข้างบน)

วิธีที่ 7 : ใช้ COUNTIF แต่ Flag 1, 0 ทำไง?

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

ทีนี้ก็ใส่เงื่อนไขเพิ่ม ว่าถ้านับแล้วมากกว่า 1 ก็ให้เป็น 0 ไปซะ เพราะแสดงว่าซ้้ำแล้ว

วิธีที่ 8 : ใช้ฟังก์ชั่น FREQUENCY

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

ฟังก์ชั่น FREQUENCY นี้ออกแบบมาใช้เป็นทำตารางแจกแจงความถี่โดยเฉพาะ ดังนั้นมันจึงออกแบบมารองรับข้อมูลที่เป็นตัวเลขเท่านั้น โดยให้เราใส่ค่าที่ต้องการนับ และใส่ช่วงที่เป็นค่าขอบบนของการจัดกลุ่มลงไป เช่น

=FREQUENCY(data_array,bins_array)
=FREQUENCY(ช่วงข้อมูล,ช่วงค่าขอบบน)

• ช่วงข้อมูลผมคือ A2:A9 อันนี้ไม่มีอะไรแปลก
• ส่วนค่าขอบบนของกลุ่มเป็น C2:C5 ซึ่งมีค่า 1, 3, 5, 10 มีความหมายดังนี้
  • กลุ่มแรก <=1
  • 1<กลุ่มสอง<=3
  • 3<กลุ่มสาม<=5
  • 5<กลุ่มสี่<=10
  • กลุ่มสุดท้าย >10 (ถ้าต้องการกลุ่มนี้ต้องลากเผื่อไปอีก 1 ช่อง)

เนื่องจาก FREQUENCY เป็นฟังก์ชั่นประเภท Array ดังนั้นวิธีเขียนต้องลากช่วง Range ที่จะแสดงผลลัพธ์ก่อน แล้วค่อยเขียนสูตร จากนั้นค่อยกด Ctrl+Shift+Enter

ซึ่งถ้าเราใส่ช่วงค่าขอบบนซ้ำกัน มันจะแสดงแค่อันแรกอันเดียว อันอื่นจะเป็น 0 หมด

ทีนี้ถ้าเราลองให้ bin_array เป็นค่า data_array ไปเลยล่ะ จะเกิดอะไรขึ้นมาดูกัน?

ทีนี้แปลว่าเราเอาความสามารถนี้มาประยุกต์ใช้ได้ หากใช้ IF เช็คว่า FREQUENCY มันนับ >0 แปลว่าเป็นตัวที่ไม่ซ้ำนั่นเอง เราจะนับเป็น 1 แต่ถ้านับได้ 0 เราก็แสดงเป็น 0 ไป

ซึ่งเราจะเขียน IF หรือ จะใส่ให้เป็น TRUE/FALSE แล้วแปลงเป็น 1,0 ด้วยการใส่ — ครอบไปก็ได้

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

แต่ถ้าข้อมูลที่ต้องการนับเป็น Text มันจะต้องพลิกแพลงมากหน่อยครับ ( ค่อนข้าง Advanced มาก)

Concept ก็คือ เราต้องแปลง Text ให้กลายเป็นเลขซะก่อน โดยใช้ MATCH แบบ Exact Match มาช่วย
ซึ่งการหาตำแหน่งแบบ Exact match จะได้ตำแหน่งผลลัพธ์แรกสุดมาเท่านั้น เช่น

จากนั้นเราก็ใช้ FREQUENCY ได้แล้ว เพราะทุกอย่างกลายเป็นเลข ตัวไหนที่ซ้ำกันก็จะแสดงเป็นตำแหน่งแรกเหมือนกันหมด

วิธีที่ 9 : ใช้ Measure ในเครื่องมือ PowerPivot

วิธีนี้เป็นการใช้เครื่องมือ PowerPivot ที่สามารถใช้ได้ตั้งแต่ Excel 2010 ขึ้นไป แต่หลายคนอาจยังไม่เคยลองใช้ แต่จริงๆ แล้วมันทรงพลังมากๆ ครับ เพราะเราสามารถใส่สูตรคำนวณซับซ้อนลงไป (เรียกว่า Measure) แล้วหมุนด้วยเครื่องมือคล้ายๆ PivotTable ได้เลย พูดง่ายๆ คือ แทนที่จะสรุปข้อมูลด้วยฟังก์ชั่นพื้นฐานอย่าง SUM COUNT AVERAGE ที่ทำใน PivotTable ถ้าคุณเปลี่ยนไปใช้ PowerPivot คุณจะสร้างสูตรซับซ้อนแค่ไหนมารองรับก็ได้!! (แต่อาจต้องเรียนรู้ฟังก์ชั่นของ PowerPivot เพิ่มนิดหน่อย)

แต่ในเคสนี้ ไม่ต้องใช้สูตรอะไรยากมากมาย

ขั้นแรก ให้สร้าง Measure โดยใส่สูตรที่ต้องการก่อน

ถ้าเป็น PowerPivot version ใหม่ๆ หน่อย ก็จะมีฟังก์ชั่น DISTINCTCOUNT ให้เลย สามารถเขียนว่า 
=DISTINCTCOUNT(Table1[Sales]) เท่านี้ถือว่าจบเลยครับ!!

แล้วมันก็จะกลายเป็น Field List ตัวใหม่ ให้ลากไปอยู่ใน Value ของ PowerPivot ได้เลย ซึ่งมันนับไม่ซ้ำจริงๆ เอาไปประกอบกับ FieldList อื่นๆ อย่างวันที่ได้อีก เจ๋งสุดๆ

วิธีที่ 10 : คุณล่ะว่าไง?

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

0

0

แต่ก่อนตอนผมใช้ Excel ใหม่ๆ มีปัญหาอย่างหนึ่งที่ผมหงุดหงิดมาก นั่นคือ ผมต้องการลาก Fill Handle แล้วให้ Excel สร้างตัวอักษร A-Z หรือ ก-ฮ โดยอัตโนมัติ แต่สิ่งที่ Excel ทำให้มันกลับแค่เป็นการ Copy ตัว A หรือ ก ที่ผมพิมพ์ลงมาให้ซะงั้น!

ผมงงมากเลย เพราะขนาด series ที่เป็นตัวเลขดูเหมือนว่าจะยากๆ เช่น 1,4,7,10,… มันยังฉลาดใส่มาให้ได้ แต่ทำไมแค่ A-Z หรือ ก-ฮ ดันไม่ใส่มาให้ฟะ!? ตอนนั้นก็ต้องใช้วิธีพิมพ์เอาเองบ้าง ใช้ฟังก์ชัน CHAR มาช่วยบ้าง ซึ่งก็ไม่ใช่วิธีที่ดีเท่าไหร่

จนผมได้พยายามค้นใน Internet ก็รู้ว่าจริงๆ แล้ว Excel ทำแบบที่ผมต้องการได้ ด้วยการสร้างสิ่งที่เรียกว่า Custom List นั่นเอง

Custom List

Custom List เป็นเครื่องมือที่ช่วยให่เราสามารถสร้าง List รายการแบบที่เรากำหนดได้เอง ซึ่งจะทำให้ Excel สามารถ Auto Fill ข้อมูล หรือ Sort ข้อมูลตาม List ที่เรากำหนดได้

วิธีการสร้าง Custom List

1. สร้าง List ข้อมูลเตรียมไว้ก่อน
   • ผมสร้าง List A-Z รวมถึง ก-ฮ ไว้ก่อน ด้วยการลากตัวเลขลงมา 255 ตัว แล้วใช้ฟังก์ชัน CHAR ครอบลงไป
     
     
   • จากนั้นทำการ Copy แล้ว Paste Value เพื่อให้สูตรกลายเป็น Value (ถ้าค้างเป็นสูตรไว้ จะเอาไปใส่ Custom List ไม่ได้)
     
     
2. เอาไปใส่ใน Custom List
   • ไปที่ Excel Options -> แถบ Advanced -> เลื่อนลงล่างๆ -> Edit Custom Lists…
     
     
   • ไปที่ช่องข้างๆ ปุ่ม Import แล้วลากครอบพื้นที่ ที่เตรียมตัว A-Z ไว้
     
     
   • กดปุ่ม Import ก็เสร็จการใส่ List A-Z แล้ว
     
     
3. ทำแบบเดิมกับ ก-ฮ หรือ List อื่นๆ ที่คุณต้องการ
   
   
   

การเรียกใช้ Custom List ที่สร้างไว้แล้ว

ใช้ใน AutoFill

วิธีใช้ Custom List ก็ง่ายแสนง่าย แค่พิมพ์ตัวใดตัวหนึ่งที่อยู่ใน List แล้วลาก Fill Handle ยาวลงมาก็ใช้ได้แล้ว

ใช้ใน Sort

เวลาจะเรียงข้อมูลด้วย Custom List จะมีวิธียุ่งยากเล็กน้อย คือ ให้เราเลือก Sort แบบ Custom Sort (หรือไปที่ Ribbon [Data] => Sort ก็ได้)

จากนั้นตรง Order ให้เลือก Custom List… แล้วเลือก List ที่ต้องการ จากนั้นกด OK

จากนั้น Excel ก็จะเรียงข้อมูลตาม List ที่เราต้องการครับ

ก่อนจากลา…

ก็จบแล้วนะครับกับบทความเรื่อง Custom List นี้ หวังว่าจะเป็นประโยชน์กับเพื่อนๆ นะครับ เพราะถ้าใครรู้เทคนิคนี้ ก็สามารถเลิกพิมพ์ List อะไรซ้ำๆ ไปอีกตลอดชีวิต อย่าง A-Z เนี่ย ผมใช้บ่อยมากๆ เลย ลดเวลาไปได้มากครับ

แต่มันจะเจ๋งกว่านี้อีก ถ้าเรามี Custom List อื่นๆ เตรียมไว้ด้วย ดังนั้นถ้าใครมี Custom List เจ๋งๆ ก็แชร์กันมาได้นะครับ เผื่อจะเป็นประโยชน์กับเพื่อนๆ คนอื่นด้วย ^^