การกรองข้อมูลใน Excel ด้วย Filter & Sort และ Advanced Filter

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

excel-advanced-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 เอาไว้ติ๊กหากต้องการกำจัดข้อมูลที่ซ้ำกันออก

เทคนิคการลบข้อมูลเก่าที่ค้างอยู่ใน Pivot Table

เวลาที่เราทำ Pivot Table เสร็จแล้วมีการเปลี่ยนแปลงข้อมูลใน Source Data อาจทำให้มีเหตุการณ์ที่มี Item ที่เคยอยู่ใน Source Data เก่าตกค้างใน Drop Down List ใน Row Label, Column Label, หรือ Filter ได้

pivot-old-item

หากเพื่อนๆใช้ Excel ตั้งแต่ Version 2007 ขึ้นไป สามารถแก้ไขเหตุการณ์ดังกล่าวได้ง่ายมากๆ โดย

  1. คลิ๊กขวาที่ตาราง Pivot
  2. เลือก PivotTable options
  3. เลือก Data tab
  4. ในหมวด Retain Items เลือก None
  5. OK แล้วกด Refresh ตาราง Pivot อีกครั้ง

pivot-old-item2

สำหรับ Excel Version เก่า สามารถไปโหลด Script VBA เพื่อแก้ไขได้ที่ http://www.contextures.com/xlPivot04.html

Lookup ข้อมูลใน Excel ไม่ยากเลย

วีดีโอชุดนี้จะสอนคุณใช้ฟังก์ชั่นในการ Lookup ข้อมูลตั้งแต่พื้นฐาน โดยสอนเทคนิคการ Lookup ข้อมูลใน Excel ด้วย VLOOKUP, MATCH, INDEX ด้วยตัวอย่างที่เข้าใจง่าย ดูแล้วทำตามได้เลยครับ

ไฟล์ประกอบ : Lookup-Tutorial-Inwexcel2(.xlsx)

ตอนที่1/4

ตอนที่2/4

ตอนที่3/4

ตอนที่4/4

บทความเสริมความรู้ที่เกี่ยวข้อง

ลูกเล่นเกี่ยวกับ Gridlines

คำว่า Gridlines ในที่นี้คือ เส้นแบ่งช่อง ไม่ใช่กรอบของช่องแต่ละช่อง หรือ Border นะครับ

การซ่อน Gridlines ให้มองไม่เห็นบนหน้าจอ

เราสามารถซ่อนไม่ให้มองเห็นบนหน้าจอคอมพ์ได้ ซึ่งทำได้ 2 วิธี ซึ่งดีกว่าการใช้วิธีถม Fill Color ด้วยสีขาวแน่นอน

excel grid lines

  1. ไปที่ Ribbon แถบ View แล้วติ๊ก Gridlines ออก
  2. ไปที่ Ribbon แถบ Page Layout => Sheet Options แล้วติ๊ก View ตรงแถบ Gridlines ออก

การ Print เส้น Gridlines

ซึ่งปกติ Gridline จะ มองไม่เห็นเวลา Print ลงกระดาษอยู่แล้วนะครับ แต่เราสามารถตั้งค่าให้มันพิมพ์แล้วมองเห็นได้ด้วย โดย

ไปที่

Ribbon แถบ Page Layout => Sheet Options แล้วติ๊ก Print ตรงแถบ Gridlines ให้ติ๊กค้างเอาไว้

เล่นแร่แปรสูตร: หาว่าใน cell นั้นๆ มีตัวอักษรที่เรากำหนดอยู่กี่ตัว

สถานการณ์

ในช่อง A1 มีคำว่า THE*DARK*KNIGHT*IS*COOL

ผมต้องการหาว่ามีตัว * กี่ตัว จะทำยังไง??

ส่วนประกอบ

  • LEN เพื่อนับจำนวนตัวอักษรทั้งหมด
  • SUBSTITUTE(text,old_text,new_text,[instance_num]) เพื่อแทนที่ตัวอักษร

Concept การผสมสูตร

  • ใช้ LEN เพื่อนับตัวอักษรทั้งหมดว่ามีกี่ตัว
  • ใช้ SUBSTITUTE แทนที่ตัวอักษรที่เราต้องการหาด้วย “” (เหมือนลบตัวอักษรนั้นทิ้ง)
  • ใช้ LEN นับคำที่ SUBSTITUTE แล้ว จากนั้นเอาไปหักออกจากที่นับไว้ตอนแรก

ขั้นตอนการผสมสูตร

ในช่อง A1 เขียนว่า THE*DARK*KNIGHT*IS*COOL

ในช่องอื่น ช่องไหนก็ได้

  • =LEN(A1)  ได้ 23
  • =SUBSTITUTE(A1,”*”,””)  ได้ THEDARKKNIGHTISCOOL
    • เราตัดตัว * ทิ้งทั้งหมด ด้วยการไม่กำหนด [instance_num] ว่าจะแทนที่ตัวไหนเป็นพิเศษ
  • นับ THEDARKKNIGHTISCOOL
    • =LEN(SUBSTITUTE(A1,”*”,””)) ได้ 19
  • เอามาลบกัน
    • =LEN(A1) – LEN(SUBSTITUTE(A1,”*”,””)) ได้ 4 ครับ