อ่านในภาษาอังกฤษ

แชร์ผ่าน


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

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

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

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

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

ตัวอย่างตารางเริ่มต้น

ภาพหน้าจอของตารางที่มีคอลัมน์ที่แสดงปี (2020) ประเทศ (สหรัฐอเมริกา ปานามา หรือแคนาดา), ผลิตภัณฑ์ (เสื้อหรือกางเกงขาสั้น), ช่องทางการขาย (ออนไลน์หรือผู้จําหน่าย) และหน่วย (ค่าต่างๆ ตั้งแต่ 55 ถึง 7500)

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

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

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

    ภาพหน้าจอของริบบอน Power Query ที่มีตัวเลือก จัดกลุ่มตาม ที่เน้นในแท็บ หน้าแรก

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

    ภาพหน้าจอของริบบอน Power Query ที่มีตัวเลือก จัดกลุ่มตาม ที่เน้นในแท็บแปลง

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

    สกรีนช็อตของตารางที่มีตัวเลือก จัดกลุ่มตาม ที่เน้นในเมนูทางลัด

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

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

  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 ใหม่ และเรียกใช้คอลัมน์ใหม่นั้น ผลิตภัณฑ์ที่มีประสิทธิภาพสูงสุด

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

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

ภาพหน้าจอของริบบอน Power Query พร้อมตัวเลือกคอลัมน์แบบกําหนดเองที่เน้นในแท็บเพิ่มคอลัมน์

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

ภาพหน้าจอของกล่องโต้ตอบคอลัมน์แบบกําหนดเองที่ใส่สูตรสําหรับ Table.Max

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

สกรีนช็อตของผลลัพธ์ของสูตรคอลัมน์แบบกําหนดเองที่มี Table.Max

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

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

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

สกรีนช็อตของตารางสุดท้ายที่มีการแปลงทั้งหมด

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

หมายเหตุ

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

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

สกรีนช็อตของตารางที่มีเก้าแถวของรายการที่มีการสะกดและตัวอักษรใหญ่ต่าง ๆ ของชื่อ Miguel และ William

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

ภาพหน้าจอของตารางที่แสดงรายการสําหรับบุคคลในรูปของ Miguel และ Mike และความถี่เป็น 3 และ 2 ตามลําดับ

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

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

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

สกรีนช็อตของตารางสุดท้ายของตัวอย่างการจัดกลุ่มแบบไม่ลับไม่มีตารางการแปลง

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

ภาพหน้าจอของกล่องโต้ตอบจัดกลุ่มตาม พร้อมตัวเลือกกลุ่มที่ไม่ชัด

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

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

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

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

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

ภาพหน้าจอของตารางที่แสดงจากค่าของไมค์และวิลเลียม และค่า To ของ Miguel และ Bill

ข้อสำคัญ

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

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

สกรีนช็อตของเมนูดรอปดาวน์ตารางการแปลงตัวอย่างการจัดกลุ่มแบบไม่เลือน

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

สกรีนช็อตของตารางขั้นสุดท้ายของการจัดกลุ่มแบบไม่ลับที่มีตารางการแปลง

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

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

หมายเหตุ

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

สําหรับข้อมูลเพิ่มเติมเกี่ยวกับวิธีการทํางานของตารางการแปลง ให้ไปที่ ค่าตารางการแปลง

ดูเพิ่มเติม

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