การจัดกลุ่มหรือการสรุปแถว

ใน Power Query คุณสามารถจัดกลุ่มค่าในแถวต่าง ๆ ให้เป็นค่าเดียวได้โดยการจัดกลุ่มแถวตามค่าในหนึ่งคอลัมน์หรือมากกว่า คุณสามารถเลือกการดําเนินการจัดกลุ่มได้สองชนิด:

• การจัดกลุ่มคอลัมน์

• การจัดกลุ่มแถว

สําหรับบทช่วยสอนนี้ คุณจะใช้ตารางตัวอย่างต่อไปนี้

ตารางที่มีคอลัมน์ที่แสดง Year (2020), Country (USA, Panama หรือ Canada), Product (Shirt หรือ Shorts), ช่องทางการขาย (ออนไลน์หรือตัวแทนจําหน่าย) และหน่วย (ค่าต่างๆ ตั้งแต่ 55 ถึง 7500)

ตําแหน่งที่จะค้นหาปุ่ม จัดกลุ่มตาม

คุณสามารถค้นหาปุ่ม จัดกลุ่มตาม ในสามตําแหน่ง:

• บนแท็บ หน้าแรก ในกลุ่ม แปลง

  

• บนแท็บ แปลงในกลุ่ม ตาราง

  

• บนเมนูทางลัดเมื่อคุณคลิกขวาเพื่อเลือกคอลัมน์

  

ใช้ฟังก์ชันการรวมเพื่อจัดกลุ่มตามคอลัมน์อย่างน้อยหนึ่งคอลัมน์

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

1. เลือกจัดกลุ่มตามบนแท็บหน้าแรก
2. เลือกตัวเลือก ขั้นสูง เพื่อให้คุณสามารถเลือกหลายคอลัมน์ที่จะจัดกลุ่มตามได้
3. เลือกคอลัมน์ ประเทศ
4. เลือก เพิ่มการจัดกลุ่ม
5. เลือก คอลัมน์ช่องทาง การขาย
6. ใน ชื่อคอลัมน์ใหม่ ให้ใส่ หน่วยทั้งหมด ใน การดําเนินการ เลือก ผลรวม และใน คอลัมน์ เลือก หน่วย
7. เลือก ตกลง

การดําเนินการนี้จะให้ตารางต่อไปนี้แก่คุณ

การดําเนินงานที่พร้อมใช้งาน

ด้วยคุณลักษณะ จัดกลุ่มตาม การดําเนินการที่พร้อมใช้งานสามารถจัดประเภทได้สองวิธี:

• การดําเนินการระดับแถว
• การดําเนินการในระดับคอลัมน์

ตารางต่อไปนี้อธิบายการดําเนินการเหล่านี้แต่ละรายการ

ชื่อการดําเนินการ	หมวดหมู่	คำอธิบาย
Sum	การดําเนินการคอลัมน์	รวมค่าทั้งหมดจากคอลัมน์
Average	การดําเนินการคอลัมน์	คํานวณค่าเฉลี่ยจากคอลัมน์
มัธยฐาน	การดําเนินการคอลัมน์	คํานวณค่ามัธยฐานจากคอลัมน์
Min	การดําเนินการคอลัมน์	คํานวณค่าต่ําสุดจากคอลัมน์
Max	การดําเนินการคอลัมน์	คํานวณค่าสูงสุดจากคอลัมน์
เปอร์เซ็นไทล์	การดําเนินการคอลัมน์	คํานวณเปอร์เซ็นไทล์โดยใช้ค่าอินพุตจาก 0 ถึง 100 จากคอลัมน์
นับจํานวนค่าที่แตกต่างกัน	การดําเนินการคอลัมน์	คํานวณจํานวนค่าที่แตกต่างกันจากคอลัมน์
นับจํานวนแถว	การดําเนินการของแถว	คํานวณจํานวนแถวทั้งหมดจากกลุ่มที่ระบุ
นับจํานวนแถวที่แตกต่างกัน	การดําเนินการของแถว	คํานวณจํานวนแถวที่แตกต่างกันจากกลุ่มที่ระบุ
แถวทั้งหมด	การดําเนินการของแถว	แสดงแถวที่จัดกลุ่มทั้งหมดค่าตารางที่ไม่มีการรวม

หมายเหตุ

ค่านับจํานวนที่แตกต่างกันและการดําเนินการเปอร์เซ็นต์ไทล์พร้อมใช้งานใน Power Query Online เท่านั้น

ดําเนินการเพื่อจัดกลุ่มตามคอลัมน์อย่างน้อยหนึ่งรายการ

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

1. ใช้คอลัมน์ต่อไปนี้เป็น จัดกลุ่มตาม คอลัมน์:

   • ประเทศ
   • ช่องทางการขาย

2. สร้างสองคอลัมน์ใหม่โดยทําตามขั้นตอนเหล่านี้:

   1. รวมคอลัมน์ หน่วย โดยใช้การดําเนินการผลรวม ตั้งชื่อคอลัมน์ นี้ว่า หน่วยทั้งหมด
   2. เพิ่มคอลัมน์ผลิตภัณฑ์ใหม่โดยใช้การดําเนินการแถวทั้งหมด

   

หลังจากการดําเนินการดังกล่าวเสร็จสมบูรณ์ ให้สังเกตว่าคอลัมน์ ผลิตภัณฑ์ มีค่า [ตาราง] ภายในแต่ละเซลล์อย่างไร แต่ละค่า [ตาราง] ประกอบด้วยแถวทั้งหมดที่ถูกจัดกลุ่มโดย คอลัมน์ประเทศ และ ช่องทาง การขายจากตารางต้นฉบับของคุณ คุณสามารถเลือกช่องว่างสีขาวภายในเซลล์เพื่อดูตัวอย่างของเนื้อหาของตารางที่ด้านล่างของกล่องโต้ตอบ

หมายเหตุ

บานหน้าต่างแสดงตัวอย่างรายละเอียดอาจไม่แสดงแถวทั้งหมดที่ใช้สําหรับการดําเนินการ group-by คุณสามารถเลือกค่า [ตาราง] เพื่อดูแถวทั้งหมดที่เกี่ยวข้องกับการดําเนินการ group-by ที่สอดคล้องกัน

ถัดไป คุณจําเป็นต้องแยกแถวที่มีค่าสูงสุดในคอลัมน์ Units ของตารางภายในคอลัมน์ Products ใหม่ และเรียกใช้คอลัมน์ใหม่นั้น ผลิตภัณฑ์ที่มีประสิทธิภาพสูงสุด

แยกข้อมูลผลิตภัณฑ์ที่มีประสิทธิภาพสูงสุด

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

ตั้งชื่อคอลัมน์ ใหม่ของคุณว่า ผลิตภัณฑ์ที่มีประสิทธิภาพสูงสุด ใส่สูตร Table.Max([Products], "Units" ) ภายใต้ สูตรคอลัมน์แบบกําหนดเอง

ผลลัพธ์ของสูตรนั้นสร้างคอลัมน์ใหม่ด้วยค่า [Record] ค่าเรกคอร์ดเหล่านี้เป็นตารางที่มีแถวเดียวเป็นหลัก ระเบียนเหล่านี้ประกอบด้วยแถวที่มีค่าสูงสุดสําหรับคอลัมน์ Units ของแต่ละค่า [Table] ในคอลัมน์ Products

ด้วยคอลัมน์ผลิตภัณฑ์ผู้ทํางานยอดเยี่ยมที่ประกอบด้วยค่า [ระเบียน] ใหม่ คุณสามารถเลือกไอคอนขยาย เลือกเขตข้อมูลผลิตภัณฑ์และหน่วย จากนั้นเลือกตกลงได้

หลังจากลบคอลัมน์ผลิตภัณฑ์ของคุณและตั้งค่าชนิดข้อมูลสําหรับคอลัมน์ที่เพิ่งขยายใหม่ผลลัพธ์ของคุณจะคล้ายกับรูปภาพต่อไปนี้

การจัดกลุ่มแบบไม่ฟุซซี่

หมายเหตุ

คุณลักษณะต่อไปนี้พร้อมใช้งานใน Power Query Online เท่านั้น

หากต้องการสาธิตวิธีการ "การจัดกลุ่มแบบไม่เป็นทางการ" ให้พิจารณาตารางตัวอย่างที่แสดงในรูปต่อไปนี้

เป้าหมายของการจัดกลุ่มแบบไม่ลับคือการดําเนินจัดกลุ่มตามที่ใช้อัลกอริทึมการจับคู่โดยประมาณสําหรับสตริงข้อความ Power Query ใช้อัลกอริทึมความคล้ายคลึงกันของ Jaccard เพื่อวัดความคล้ายคลึงกันระหว่างคู่ของอินสแตนซ์ จากนั้นใช้การคลัสเตอร์แบบลําดับชั้นเชิงรวมกับอินสแตนซ์กลุ่มเข้าด้วยกัน รูปภาพต่อไปนี้แสดงผลลัพธ์ที่คุณคาดหวัง ซึ่งตารางจะถูกจัดกลุ่มตามคอลัมน์ Person

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

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

อย่างไรก็ตาม คุณสามารถควบคุมการดําเนินการจัดกลุ่มแบบไม่น่าสนใจได้มากขึ้นโดยการขยาย ตัวเลือกกลุ่ม Fuzzy

ตัวเลือกต่อไปนี้จะพร้อมใช้งานสําหรับการจัดกลุ่มแบบไม่เป็นทางการ:

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

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

• จาก: สตริงข้อความที่จะค้นหาในตารางของคุณ
• ไปยัง: สตริงข้อความที่จะใช้เพื่อแทนที่สตริงข้อความในคอลัมน์ From

รูปภาพต่อไปนี้แสดงตารางการแปลงที่ใช้ในตัวอย่างนี้

สำคัญ

สิ่งสําคัญคือตารางการแปลงมีคอลัมน์และชื่อคอลัมน์เดียวกันตามที่แสดงด้านบน (ต้องเป็น "จาก" และ "ถึง") มิฉะนั้น Power Query จะไม่จดจําสิ่งเหล่านี้

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

หลังจากที่คุณเลือกตารางการแปลงแล้ว ให้เลือก ตกลง ผลลัพธ์ของการดําเนินการดังกล่าวให้ข้อมูลต่อไปนี้แก่คุณ:

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

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

หมายเหตุ

เมื่อจัดกลุ่มตามหลายคอลัมน์ ตารางการแปลงจะดําเนินการแทนที่ในคอลัมน์ทั้งหมดถ้าการแทนที่ค่าจะเพิ่มคะแนนความคล้ายคลึงกัน

ดูเพิ่มเติม

เพิ่มคอลัมน์แบบกําหนดเอง
ลบรายการซ้ํา