กรองข้อมูล (Power Query)

ใน Power Query คุณสามารถรวมหรือแยกแถวโดยยึดตามค่าของคอลัมน์ได้ คอลัมน์ที่กรองจะมีไอคอนตัวกรองขนาดเล็ก ( ) ในส่วนหัวของคอลัมน์ เมื่อต้องการเอาตัวกรองคอลัมน์ออก ให้เลือก ที่อยู่ถัดจากคอลัมน์ แล้วเลือก ล้างตัวกรอง

กรองโดยใช้ตัวกรองอัตโนมัติ

ใช้ฟีเจอร์ ตัวกรองอัตโนมัติ เพื่อค้นหา แสดง หรือซ่อนค่า และเพื่อให้ระบุเกณฑ์การกรองได้ง่ายขึ้น ตามค่าเริ่มต้น คุณจะเห็นเฉพาะค่าที่แตกต่างกัน 1,000 ค่าแรกเท่านั้น หากข้อความแจ้งว่ารายการตัวกรองอาจไม่สมบูรณ์ ให้เลือก โหลดเพิ่มเติม คุณอาจเห็นข้อความนี้มากกว่าหนึ่งครั้ง ทั้งนี้ขึ้นอยู่กับจํานวนข้อมูล

1. เมื่อต้องการเปิดคิวรี ให้ค้นหาคิวรีที่โหลดไว้ก่อนหน้านี้จากตัวแก้ไข Power Query เลือกเซลล์ในข้อมูล แล้วเลือกแก้ไขคิวรี> สําหรับข้อมูลเพิ่มเติม ให้ดู สร้าง โหลด หรือแก้ไขคิวรีใน Excel
2. เลือก ที่อยู่ถัดจากคอลัมน์ที่คุณต้องการกรอง
3. ล้างกล่องกาเครื่องหมาย (เลือกทั้งหมด) เพื่อยกเลิกการเลือกคอลัมน์ทั้งหมด
4. เลือกกล่องกาเครื่องหมายของค่าคอลัมน์ที่คุณต้องการกรอง แล้วเลือก ตกลง

กรองโดยใช้ตัวกรองข้อความ

คุณสามารถกรองตามค่าข้อความที่ระบุได้โดยใช้เมนูย่อย ตัวกรองข้อความ

1. เมื่อต้องการเปิดคิวรี ให้ค้นหาคิวรีที่โหลดไว้ก่อนหน้านี้จากตัวแก้ไข Power Query เลือกเซลล์ในข้อมูล แล้วเลือกแก้ไขคิวรี> สําหรับข้อมูลเพิ่มเติม ให้ดู สร้าง โหลด หรือแก้ไขคิวรีใน Excel

2. เลือก ถัดจากคอลัมน์ที่มีค่าข้อความที่คุณต้องการกรอง

3. เลือก ตัวกรองข้อความ แล้วเลือกชื่อชนิดความเท่ากันของ เท่ากับ, D oes Not Equal, ขึ้นต้นด้วย, ไม่ได้ขึ้นต้นด้วย, ลงท้ายด้วย, ไม่ได้ลงท้ายด้วย, มี, และ ไม่มี

4. ในกล่องโต้ตอบ กรองแถว ให้ทําดังนี้

   • ใช้ โหมดพื้นฐาน เพื่อใส่หรืออัปเดตตัวดําเนินการและค่าสองตัว
   • ใช้ โหมดขั้นสูง เพื่อใส่หรืออัปเดตส่วนคําสั่ง การเปรียบเทียบ คอลัมน์ ตัวดําเนินการ และค่ามากกว่าสองส่วน

5. เลือก ตกลง

กรองโดยใช้ตัวกรองตัวเลข

คุณสามารถกรองตามค่าตัวเลขโดยใช้เมนูย่อย ตัวกรองตัวเลข

1. เมื่อต้องการเปิดคิวรี ให้ค้นหาคิวรีที่โหลดไว้ก่อนหน้านี้จากตัวแก้ไข Power Query เลือกเซลล์ในข้อมูล แล้วเลือกแก้ไขคิวรี> สําหรับข้อมูลเพิ่มเติม ให้ดู สร้าง โหลด หรือแก้ไขคิวรีใน Excel

2. เลือก ของคอลัมน์ที่มีค่าตัวเลขที่คุณต้องการกรอง

3. เลือก ตัวกรองตัวเลข แล้วเลือกชื่อชนิดความเท่ากันเท่ากับ ไม่เท่ากับมากกว่ามากกว่าหรือเท่ากับน้อยกว่าน้อยกว่า หรือเท่ากับ หรือ ระหว่าง

4. ในกล่องโต้ตอบ กรองแถว ให้ทําดังนี้

   • ใช้ โหมดพื้นฐาน เพื่อใส่หรืออัปเดตตัวดําเนินการและค่าสองตัว
   • ใช้ โหมดขั้นสูง เพื่อใส่หรืออัปเดตส่วนคําสั่ง การเปรียบเทียบ คอลัมน์ ตัวดําเนินการ และค่ามากกว่าสองส่วน

5. เลือก ตกลง

กรองโดยใช้ตัวกรองวันที่/เวลา

คุณสามารถกรองตามค่าวันที่/เวลาโดยใช้เมนูย่อย ตัวกรองวันที่/เวลา

1. เมื่อต้องการเปิดคิวรี ให้ค้นหาคิวรีที่โหลดไว้ก่อนหน้านี้จากตัวแก้ไข Power Query เลือกเซลล์ในข้อมูล แล้วเลือกแก้ไขคิวรี> สําหรับข้อมูลเพิ่มเติม ให้ดู สร้าง โหลด หรือแก้ไขคิวรีใน Excel

2. เลือก ลูกศรของคอลัมน์ที่มีค่าวันที่/เวลาที่คุณต้องการกรอง

3. เลือก ตัวกรองวันที่/เวลา แล้วเลือกชื่อชนิดความเท่ากันของ เท่ากับ, ก่อน, หลัง, ระหว่าง, ในถัดไป, ก่อนหน้า, เป็นเวอร์ชันแรกสุด, เป็นเวอร์ชันล่าสุด, ไม่ใช่ชื่อแรกสุด, ไม่ใช่ล่าสุด และ ตัวกรองแบบกําหนดเอง

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

4. ในกล่องโต้ตอบ กรองแถวของ:

   • ใช้ โหมดพื้นฐาน เพื่อใส่หรืออัปเดตตัวดําเนินการและค่าสองตัว
   • ใช้ โหมดขั้นสูง เพื่อใส่หรืออัปเดตส่วนคําสั่ง การเปรียบเทียบ คอลัมน์ ตัวดําเนินการ และค่ามากกว่าสองส่วน

5. เลือก ตกลง

กรองหลายคอลัมน์

เมื่อต้องการกรองหลายคอลัมน์ ให้กรองคอลัมน์แรก แล้วทําซ้ําตัวกรองคอลัมน์สําหรับแต่ละคอลัมน์เพิ่มเติม

ในตัวอย่างแถบสูตรต่อไปนี้ ฟังก์ชัน Table.SelectRows จะส่งกลับคิวรีที่ถูกกรองตาม รัฐ และ ปี

กรองตามค่า Null หรือค่าว่าง

ค่า Null หรือค่าว่างเกิดขึ้นเมื่อไม่มีเซลล์ใดอยู่ในเซลล์นั้น มีสองวิธีในการเอาค่า Null หรือค่าว่างออก:

ใช้ตัวกรองอัตโนมัติ

1. เมื่อต้องการเปิดคิวรี ให้ค้นหาคิวรีที่โหลดไว้ก่อนหน้านี้จากตัวแก้ไข Power Query เลือกเซลล์ในข้อมูล แล้วเลือกแก้ไขคิวรี> สําหรับข้อมูลเพิ่มเติม ให้ดู สร้าง โหลด หรือแก้ไขคิวรีใน Excel
2. เลือก ที่อยู่ถัดจากคอลัมน์ที่คุณต้องการกรอง
3. ล้างกล่องกาเครื่องหมาย (เลือกทั้งหมด) เพื่อยกเลิกการเลือกคอลัมน์ทั้งหมด
4. เลือก ลบค่าว่างออก แล้วเลือก ตกลง

วิธีนี้จะตรวจสอบแต่ละค่าในคอลัมน์โดยใช้สูตรนี้ (สําหรับคอลัมน์ "ชื่อ"):

Table.SelectRows(#"Changed Type", each ([Name] <> null and [Name] <> ""))

ใช้คําสั่ง เอาแถวว่างออก

1. เมื่อต้องการเปิดคิวรี ให้ค้นหาคิวรีที่โหลดไว้ก่อนหน้านี้จากตัวแก้ไข Power Query เลือกเซลล์ในข้อมูล แล้วเลือกแก้ไขคิวรี>ข้อมูล> สําหรับข้อมูลเพิ่มเติม ให้ดู สร้าง โหลด หรือแก้ไขคิวรีใน Excel
2. เลือกเอาแถว>ว่างออกหน้าแรก>

เมื่อต้องการล้างตัวกรองนี้ ให้ลบขั้นตอนที่เกี่ยวข้องภายใต้ ขั้นตอนที่นําไปใช้ ใน การตั้งค่าคิวรี

วิธีนี้จะตรวจสอบทั้งแถวเป็นระเบียนโดยใช้สูตรนี้:

Table.SelectRows(#"Changed Type", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))

กรองตามตําแหน่งแถว

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

เมื่อต้องการเก็บแถวบนสุดไว้

1. เมื่อต้องการเปิดคิวรี ให้ค้นหาคิวรีที่โหลดไว้ก่อนหน้านี้จากตัวแก้ไข Power Query เลือกเซลล์ในข้อมูล แล้วเลือกแก้ไขคิวรี> สําหรับข้อมูลเพิ่มเติม ให้ดู สร้าง โหลด หรือแก้ไขคิวรีใน Excel
2. เลือกเก็บแถว>หน้าแรก>ให้เก็บแถวบนสุดไว้
3. ในกล่องโต้ตอบ เก็บแถวบนสุด ให้ใส่จํานวนแถวใน จํานวนแถว
4. เลือก ตกลง

เมื่อต้องการเก็บแถวด้านล่างไว้

1. เมื่อต้องการเปิดคิวรี ให้ค้นหาคิวรีที่โหลดไว้ก่อนหน้านี้จากตัวแก้ไข Power Query เลือกเซลล์ในข้อมูล แล้วเลือกแก้ไขคิวรี> สําหรับข้อมูลเพิ่มเติม ให้ดู สร้าง โหลด หรือแก้ไขคิวรีใน Excel
2. เลือกเก็บแถว>หน้าแรก>ให้เก็บแถวด้านล่างไว้
3. ในกล่องโต้ตอบ เก็บแถวด้านล่าง ให้ใส่จํานวนใน จํานวนแถว
4. เลือก ตกลง

เมื่อต้องการเก็บช่วงของแถว

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

1. เมื่อต้องการเปิดคิวรี ให้ค้นหาคิวรีที่โหลดไว้ก่อนหน้านี้จากตัวแก้ไข Power Query เลือกเซลล์ในข้อมูล แล้วเลือกแก้ไขคิวรี>ข้อมูล> สําหรับข้อมูลเพิ่มเติม ให้ดู สร้าง โหลด หรือแก้ไขคิวรีใน Excel
2. เลือก หน้าแรก>เก็บแถว>เก็บช่วงของแถว
3. ในกล่องโต้ตอบ เก็บช่วงของแถว ให้ใส่ตัวเลขใน แถวแรก และ จํานวนแถว เมื่อต้องการทําตามตัวอย่าง ให้ใส่หกเป็นแถวแรก และเจ็ดเป็นจํานวนแถว
4. เลือก ตกลง

เมื่อต้องการเอาแถวบนสุดออก

1. เมื่อต้องการเปิดคิวรี ให้ค้นหาคิวรีที่โหลดไว้ก่อนหน้านี้จากตัวแก้ไข Power Query เลือกเซลล์ในข้อมูล แล้วเลือกแก้ไขคิวรี>ข้อมูล> สําหรับข้อมูลเพิ่มเติม ให้ดู สร้าง โหลด หรือแก้ไขคิวรีใน Excel
2. เลือกเอาแถว>ออกหน้าแรก>เอาแถวบนออก
3. ในกล่องโต้ตอบ เอาแถวบนสุดออก ให้ใส่ตัวเลขใน จํานวนแถว
4. เลือก ตกลง

เมื่อต้องการเอาแถวด้านล่างออก

1. เมื่อต้องการเปิดคิวรี ให้ค้นหาคิวรีที่โหลดไว้ก่อนหน้านี้จากตัวแก้ไข Power Query เลือกเซลล์ในข้อมูล แล้วเลือกแก้ไขคิวรี>ข้อมูล> สําหรับข้อมูลเพิ่มเติม ให้ดู สร้าง โหลด หรือแก้ไขคิวรีใน Excel
2. เลือก เอา>แถว>หน้าแรกออก เอาแถวด้านล่างออก
3. ในกล่องโต้ตอบ เอาแถวด้านล่างออก ให้ใส่ตัวเลขใน จํานวนแถว
4. เลือก ตกลง

กรองโดยการเอาแถวที่สลับกันออก

คุณสามารถกรองตามแถวที่สลับกันและคุณยังสามารถกําหนดรูปแบบแถวสํารองได้ ตัวอย่างเช่น ตารางของคุณมีแถวข้อคิดเห็นต่อจากแถวข้อมูลแต่ละแถว คุณต้องการเก็บแถวคี่ (1, 3, 5 และอื่นๆ) แต่เอาแถวคู่ (2, 4, 6 และอื่นๆ) ออก

1. เมื่อต้องการเปิดคิวรี ให้ค้นหาคิวรีที่โหลดไว้ก่อนหน้านี้จากตัวแก้ไข Power Query เลือกเซลล์ในข้อมูล แล้วเลือกแก้ไขคิวรี>ข้อมูล> สําหรับข้อมูลเพิ่มเติม ให้ดู สร้าง โหลด หรือแก้ไขคิวรีใน Excel

2. เลือกเอาแถว>หน้าแรก>ออก เอาแถวที่สลับกันออก

3. ในกล่องโต้ตอบ เอาแถวที่สลับกันออก ให้ใส่ค่าต่อไปนี้

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

4. เลือก ตกลง

ผลลัพธ์

Power Query มีรูปแบบที่จะทําตามสําหรับแถวทั้งหมด ในตัวอย่างนี้ แถวคี่จะถูกเอาออกและแถวคู่จะถูกเก็บไว้

ดูเพิ่มเติม

ความช่วยเหลือ Power Query สำหรับ Excel

เอาแถวที่มีข้อผิดพลาดออกหรือเก็บแถวไว้

เก็บหรือเอาแถวที่ซ้ํากันออก

กรองตามตําแหน่งแถว (docs.com)

กรองตามค่า (docs.com)