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

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

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

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

เลือกคอลัมน์

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

เมื่อต้องการเปิดคิวรี ให้ค้นหาคิวรีที่โหลดไว้ก่อนหน้านี้จากตัวแก้ไข Power Query เลือกเซลล์ในข้อมูล แล้วเลือก คิวรี > แก้ไข สําหรับข้อมูลเพิ่มเติม ให้ดู สร้าง โหลด หรือแก้ไขคิวรีใน Excel
เลือกลูกศรลง ถัดจากคอลัมน์ที่มีค่าข้อความที่คุณต้องการกรอง
เลือก ตัวกรองข้อความ แล้วเลือกชื่อชนิดความเท่ากันของ เท่ากับ, Does Not Equal, ขึ้นต้นด้วย, ไม่ได้ขึ้นต้นด้วย, ลงท้ายด้วย, ไม่ได้ลงท้ายด้วย, มี, และ ไม่มี
ในกล่องโต้ตอบ กรองแถว ให้ทําดังนี้
- ใช้ โหมดพื้นฐาน เพื่อใส่หรืออัปเดตตัวดําเนินการและค่าสองตัว
- ใช้ โหมดขั้นสูง เพื่อใส่หรืออัปเดตส่วนคําสั่ง การเปรียบเทียบ คอลัมน์ ตัวดําเนินการ และค่ามากกว่าสองส่วน
เลือก ตกลง

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

เมื่อต้องการเปิดคิวรี ให้ค้นหาคิวรีที่โหลดไว้ก่อนหน้านี้จากตัวแก้ไข Power Query เลือกเซลล์ในข้อมูล แล้วเลือก คิวรี > แก้ไข สําหรับข้อมูลเพิ่มเติม ให้ดู สร้าง โหลด หรือแก้ไขคิวรีใน Excel
เลือกลูกศรลง ของคอลัมน์ที่มีค่าตัวเลขที่คุณต้องการกรอง
เลือก ตัวกรองตัวเลข แล้วเลือกชื่อชนิดความเท่ากันเท่ากับ ไม่เท่ากับมากกว่ามากกว่าหรือเท่ากับน้อยกว่าน้อยกว่า หรือเท่ากับ หรือ ระหว่าง
ในกล่องโต้ตอบ กรองแถว ให้ทําดังนี้
- ใช้ โหมดพื้นฐาน เพื่อใส่หรืออัปเดตตัวดําเนินการและค่าสองตัว
- ใช้ โหมดขั้นสูง เพื่อใส่หรืออัปเดตส่วนคําสั่ง การเปรียบเทียบ คอลัมน์ ตัวดําเนินการ และค่ามากกว่าสองส่วน
เลือก ตกลง

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

เมื่อต้องการเปิดคิวรี ให้ค้นหาคิวรีที่โหลดไว้ก่อนหน้านี้จากตัวแก้ไข Power Query เลือกเซลล์ในข้อมูล แล้วเลือก คิวรี > แก้ไข สําหรับข้อมูลเพิ่มเติม ให้ดู สร้าง โหลด หรือแก้ไขคิวรีใน Excel
เลือกลูกศรลง ของคอลัมน์ที่มีค่าวันที่/เวลาที่คุณต้องการกรอง
เลือก ตัวกรองวันที่/เวลา แล้วเลือกชื่อชนิดความเท่ากันของ เท่ากับ, ก่อน, หลัง, ระหว่าง, ในถัดไป, ก่อนหน้า, เป็นเวอร์ชันแรกสุด, เป็นเวอร์ชันล่าสุด, ไม่ใช่ชื่อแรกสุด, ไม่ใช่ล่าสุด และ ตัวกรองแบบกําหนดเอง

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

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

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

ผลลัพธ์การกรอง

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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