สมมติว่าเรามีข้อมูลอยู่แบบซ้ายมือของรูป แต่อยากให้ผลลัพธ์รวมเป็นข้อความเดียวกันกลายเป็นแบบด้านขวามือ เราจะใช้วิธีไหนได้บ้าง? มาดูกันครับ คิดว่าบทความสั้นๆ อันนี้น่าจะช่วยเปิดหูเปิดตาให้เพื่อนๆ ได้รู้จักวิธีแก้ปัญหาที่หลากหลายมากขึ้นนะครับ
ก่อนอื่นเราทำข้อมูลให้เป็น Table (Insert->Table หรือ Ctrl+T) ก่อน จะได้รองรับ Data mี่เพิ่มขึ้นได้ง่ายๆ ครับ และตั้งชื่อว่า MyData
วิธีใช้สูตรของ Excel 365 รวมเป็นข้อความเดียวกัน
วิธีที่เข้าใจง่ายที่สุดเลย ก็คือสูตรของ Excel 365 ซึ่งมีความสามารถแบบ Array Formula ที่ขี้โกงและใช้ง่ายกว่าสูตรใน Version เก่ามากๆ
โดยเราจะเริ่มจากเอา Group ทั้งหมดออกมาก่อนด้วย UNIQUE (และถ้าอยากเรียงด้วยก็ซ้อน SORT เข้าไปก็ได้)
=UNIQUE(MyData[Group])หรือ
=SORT(UNIQUE(MyData[Group]))
จากนั้นเราจะทำการคำนวณ item แต่ละอันภายใต้แต่ละ Group ออกมาด้วยฟังก์ชัน FILTER ดังนี้
=FILTER(MyData[Item],MyData[Group]=D2)
แต่เราอยากจะให้เอาข้อมูลที่ Filter มาได้ เอามาเชื่อมต่อเป็นข้อความเดียวกัน จึงใช้ TEXTJOIN มาช่วยอีกที
=TEXTJOIN(", ",TRUE,FILTER(MyData[Item],MyData[Group]=D2))
ถ้าเรา Copy สูตรมาข้างล่างก็จะได้ครบทุกตัว
สูตรนี้เข้าใจง่าย แต่ก็มีข้อเสียว่า สูตรด้านขวาสุดมันไม่งอก item ของ group ใหม่ออกมาอัตโนมัติเมื่อมี Group เพิ่ม ถ้าอยากให้งอกสูตรจะยากกว่านี้ ซึ่งผมขอเก็บปัญหานี้ไว้ก่อนนะ
วิธีใช้ DAX รวมเป็นข้อความเดียวกัน
ก่อนอื่นเราก็เอาข้อมูล MyTable ต้นฉบับเข้าไป Pivot แบบ Data Model ซะ เพื่อให้มันรองรับการใช้ DAX
จากนั้นเราลาก Group ไปที่ Rows แล้ว คลิ๊กขวาที่ชื่อตารางแล้ว + Add Measure…
จากนั้นตั้งชื่อ Measure แล้วเขียนสูตรดังนี้ ก็จบเลย
=CONCATENATEX(MyData,[Item],", ")ซึ่งภายใต้แต่ละ group มันก็จะมองเห็นเฉพาะ item ของตัวมันเอง (Concept ของ Filter Context) ผมจึงสามารถใช้ CONCATENATEX รวมทุก item เข้าด้วยกันในลักษณะเดียวกับ TEXTJOIN ของสูตร Excel ได้เลย โดยไม่ต้องมีฟังก์ชัน FILTER แบบสูตร Excel อีก
แต่ถ้าหากคิดว่า รายการ item ย่อย อาจมีซ้ำกันได้ ก็ให้ใส่ DISTINCT ครอบ MyData[Item] เพื่อให้แน่ใจว่าได้ item ย่อยที่ไม่ซ้ำกันก่อน
=CONCATENATEX(DISTINCT(MyData[Item]),[Item],", ")
จากนั้นเราจะได้ Measure ใหม่ที่สัญลักษณ์ fx ก็ให้ลากลง Values ได้เลย
การใช้ CONCATENATEX จริงๆ แล้วมีความยืดหยุ่นกว่า TEXTJOIN มากๆ ตรงที่สามารถกำหนดการเรียง item ได้อีกว่าจะให้เรียงตามอะไร เรียงทิศทางไหน เช่น ให้เรียงตามยอดขายแบบมากไปน้อยก็ยังได้ ดังนั้นผมจึงบอกได้เลยว่าวิธีใช้ DAX คือสุดยอดที่สุดแล้วครับ
วิธีใช้ Power Query รวมเป็นข้อความเดียวกัน
หากใช้ Power Query ก็ให้ Get Data from Table เข้าไปก่อนเลยครับ
จากนั้นทำการ Group by แบบ all rows ดังนี้
ผลลัพธ์มันจะทำให้เหลือ Group แบบไม่ซ้ำกัน แต่ภายใต้แต่ละ Group จะมี Table ที่มองเห็นเฉพาะทุกคอลัมน์ใน Group นั้นเท่านั้น (ซึ่งดูได้หากคลิ๊ก “ข้างๆ คำว่า Table” ของแต่ละ Group)
ทีนี้เราต้องการผลลัพธ์เฉพาะคอลัมน์ item ให้ออกมาเป็น list ดังนั้นผมแนะนำให้ Add Custom Column ใหม่แล้วใส่สูตรดังนี้ เพื่อเอามาเฉพาะคอลัมน์ item จากตารางใน all (ซึ่งการเขียน [ ] เป็นสัญลักษณ์การอ้างอิง Record ในภาษา M ซึ่งพิมพ์เล็กพิมพ์ใหญ่ต้องเป๊ะนะ)
=[all][Item]
พอลิ๊กดูที่ข้างๆ คำว่า List จะเห็นเฉพาะคอลัมน์ Item แล้ว
จากนั้นเราจะสามารถคลิ๊กที่มุมขวาบนของคอลัมน์ แล้วเลือก Extract Values… เพื่อรวม list เข้าด้วยกันได้เลย
จากนั้นเราเลือกตัวคั่นได้ แต่ผมอยากให้คั่นด้วย comma และมีเว้นวรรคด้วย ก็เลยต้องเลือกแบบ Custom
แล้วมันก็จะแสดงผลลัพธ์ออกมาได้ในที่สุด
ถ้าเราไม่ต้องการคอลัมน์ all ก็ลบทิ้งไปได้เลย จากนั้นก็ Close & Load เอาผลลัพธ์ออกมาได้เลย
สรุป
ส่วนตัวแล้ว ถ้าการแสดงผลแบบนี้คือผลลัพธ์สุดท้ายที่ต้องการ ผมจะเลือกใช้วิธี DAX ก่อน เพราะเป็นวิธีที่ง่าย และยืดหยุ่นที่สุดในความคิดของผม แต่ถ้าผมต้องรวมรายการแบบนี้ เพื่อนำไป Merge หรือไปทำอะไรบางอย่างกับตารางอื่นต่ออีก ผมก็จะใช้ Power Query ทำครับ
อย่างไรก็ตามทั้งวิธี DAX และ Power Query มันจะต้องมีการ Refresh ข้อมูลถ้าข้อมูลต้นทางเปลี่ยน ซึ่งวิธีใช้สูตรไม่ต้อง Refresh เลย มันจะเปลี่ยนอัตโนมัติครับ (แค่ตอนนี้สูตรที่ผมใช้มันยังไม่งอก item ของ group ใหม่เอง)
ซึ่งจริงๆ แล้วยังมีวิธีอื่นๆ ที่ทำงานแบบนี้ได้อีกนะครับ เช่น VBA ก็ทำได้ ดังนั้นใครมีวิธีอื่นเจ๋งๆ อีก ก็ comment แนะนำมาได้เลยนะครับ
