แนวทางฝึกฝน Excel ให้เก่งขึ้น

หลายคนถามว่าทำยังไงถึงจะเก่ง Excel มากขึ้น คำตอบง่ายๆ คือพยายามใช้มันแก้ปัญหาใหม่ๆ เรื่อยๆ เพราะยิ่งเจอปัญหาเยอะแล้วเราพยายามแก้ปัญหาจนผ่านไปได้ เราก็ะเก่งขึ้นเหมือนกับสู้กับ Monster เก่งๆ ในเกมจนเรา Level Up ได้นั่นแหละครับ ถ้าเราสู้แต่ Monster อ่อนๆ จะได้ Exp เยอะพอได้ไง? (คนเล่นเกมคงพอเข้าใจเนอะ)

แต่ปัญหาคือ หลายคนไม่รู้จะเอาปัญหาจากไหนมาแก้? (ซึ่งก็อาจจะแปลกนิดนึง เพราะงานที่ตัวเองทำก็น่าจะมีปัญหาให้แก้อ่ะนะ 55) แต่ไม่เป็นไร สมมติว่างานที่ทำไม่มีปัญหายากๆ แล้วกัน…

วันนี้ผมจะมาแนะนำว่าเราจะหาปัญหาจากไหนมาฝึก Excel แบบ Advance กันดี??

แนะนำ Challenge จาก Excel Wizard

คำตอบก็คือการฝึกจากโจทย์ที่มีคนมาถามใน Internet ซึ่งเผอิญทาง Excel Wizard ได้เอาโจทย์ที่น่าสนใจจากต่างประเทศมาคอยถามให้พวกเราลองแก้ รวมถึงช่วยกันแชร์วิธีการแก้ปัญหาหลายๆ แบบดู ซึ่งสามารถไปดูได้ที่ https://web.facebook.com/hashtag/excelchallenge

ซึ่งปัญหาที่มาถามก็จะมีทั้งประเภทที่ออกแบบมาให้แก้ด้วยสูตรปกติ (เป็นตัวเลขเฉยๆ) กับแบบที่ออกแบบมาให้แก้ด้วย Power Query (มีคำว่า PQตามด้วยเลข) แต่เอาจริงๆ คนแก้จะใช้วิธีไหนแก้ก็ได้ ไม่ซีเรียสหรอก

แต่บอกก่อนว่าโจทย์ที่ Excel Wizard นำมา post นี่เรียกได้ว่าเป็น Monster ระดับ Mini Boss ได้เลย ดังนั้นใครที่ทักษะยังน้อยอยู่อาจรู้สึกว่าตามไม่ทัน อันนี้ไม่เป็นไร ให้ลองอ่านไปก่อนนะครับ จะได้รู้ว่า Excel จริงๆ แล้วทำอะไรได้ และลึกซึ้งกว่าที่คิดแค่ไหน

ไว้คุณฝึกฝนนพื้นฐานจยเก่งขึ้นค่อยมาลองตี Mini Boss เหล่านี้อีกทีในอนาคตก็ได้

แก้ปัญหาไม่ได้ทำยังไง?

หากพยายามลองแก้ปัญหา แล้วแก้ไม่ได้ไม่ต้องซีเรียส ส่วนสำคัญคือการได้พยายามลองแก้ด้วยตัวเอง

แก้ไม่ออกไม่เป็นไร ขอให้คิดจนปวดหัวก่อน แล้วค่อยไปพัก Relax เช่น ไปอาบน้ำ รดน้ำต้นไม้ ขับรถ แล้วค่อยพยายามใหม่อีกทีภายหลัง บางทีตอนพักเราจะปิ๊งไอเดียใหม่ๆ ขึ้นมาได้เอง (การทำงานของสมองเป็นแบบนี้นะ คือต้องคิดหนักๆ ก่อน แล้วค่อยพัก ถึงจะ work)

มาลองดูตัวอย่างการแก้โจทย์

สมมติเอาโจทย์ที่ผมเอามาเป็น Screen Shot

• โจทย์ Original : https://www.linkedin.com/posts/excelbi_powerbi-powerquery-dax-activity-6984725315071676416-haHm
• ไฟล์ Excel ประกอบการฝึก : https://lnkd.in/dkmqAJib

ให้ Save ไฟล์ออกมาไว้ในเครื่องตัวเองแล้วเริ่มแก้โจทย์

เดี๋ยวผมจะลองแก้ด้วย Power Query ดูจะเป็นประมาณนี้

ให้เลือกข้อมูลแล้วคลิ๊กขวา -> Get Data from Table/Range

• มันจะบังคับให้สร้าง Table
  (เราไม่ต้องติ๊ก My Table has Header เพราะตัวตารางยังผิดอยู่)
• จากนั้น Excel จะดูดข้อมูลนี้เข้าสู่ Power Query Editor

ให้เราลบ Step Change Type ออกไปก่อน เพราะหัตารางยังไม่ถูก ไม่ควรทำ Change Type ตอนนี้

หัวตารางต้องถูกต้องก่อน

หลักการแก้ปัญหาของ Power Query ที่สำคัญสุดคือ “ต้องทำหัวตารางต้องถูกต้องก่อน”

เคสนี้ผมคิดว่าเราควรจัดการเรื่องปีที่ Merge มาก่อน ดั้งนั้นผมจะ Transpose ข้อมูลลงมาก่อนแล้ว คลิ๊กขวา Fill Down ปี ที่ Column 1 ลงมา จะได้แบบนี้

ทีนี้ผมคิดว่าเดี๋ยวพอ Transpose กลับไปจะมีปัญหาหัวตารางซ้ำ แล้วพอต้อง Unpivot ข้อมูลลงมาก็จะมีปัญหาอีก ดังนั้นผมจะพยายามสร้างหัวตารางใหม่ให้ไม่ซ้ำ โดยผมจะ Add Index Column ขึ้นมาก่อนดังนี้

ทำการ Merge Columns (ในเมนู Trannsform) กับ Column1 และ Index เข้าด้วยกันด้วยตัวคั่นแปลกๆ เช่น |

จากนั้น Transpose กลับไปจะได้แบบนี้

คราวนี้เราจะ Promote Header แต่ว่าต้องเอาบรรทัดสุดท้ายขึ้นไปก่อน ดังนั้นก็ไปที่ Transform -> Reverse Row แล้วค่อย Promote Header แล้วลบ Change Type ออกไปซะ จะได้แบบนี้

ที่นี้ให้เราคลิ๊กขวา Column แรกแล้ว Unpivot Other Columns ลงมาให้หมด จะได้แบบนี้

จากนั้นให้ Split Attribute ด้วย Delimiter คือ | จะได้แบบนี้

ถ้าหัวตารางไม่มีปัญหาแล้ว อะไรก็ง่ายละ

คราวนี้ให้ลบ Attribute.2 ออก แล้วเลือก คอลัมน์ Attribute.1 แล้ว Transform -> Pivot Columns โดยเอา Value เป็นคอลัมน์ value แบบนี้

แล้วเราก็จะได้ผลลัพธ์แบบที่โจทย์ต้องการดังนี้

สรูป M Code ที่ระบบ Gen ออกมาให้ (เรายังไม่ได้เขียนเองเลย)

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Transposed Table" = Table.Transpose(Source),
    #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1"}),
    #"Added Index" = Table.AddIndexColumn(#"Filled Down", "Index", 0, 1, Int64.Type),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Added Index", {{"Column1", type text}, {"Index", type text}}, "th-TH"),{"Column1", "Index"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"),
    #"Transposed Table1" = Table.Transpose(#"Merged Columns"),
    #"Reversed Rows" = Table.ReverseRows(#"Transposed Table1"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Reversed Rows", [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Sales Man|0"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", Int64.Type}, {"Attribute.2", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Attribute.2"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Attribute.1", type text}}, "th-TH"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Attribute.1", type text}}, "th-TH")[Attribute.1]), "Attribute.1", "Value", List.Sum)
in
    #"Pivoted Column"

วิธีอื่นๆ เช่น การใช้สูตร

ถ้าไปดู Post นั้นจะเห็นว่ามีวิธีการทำมากมาย เช่น Excel Wizard ก็แสดงวิธีใช้สูตรขั้นสูงแบบนี้ ซึ่งทำเอาไว้ 2 วิธีด้วยกัน

1

=LET(y,SCAN(,C2:Q2,LAMBDA(a,v,IF(v,v,a))),u,UNIQUE(y,1),HSTACK(B2:B9,MMULT(--C2:Q9,N(u=TRANSPOSE(y)))))

2

=LET(z,C2:Q9,REDUCE(B2:B9,C2:Q2,LAMBDA(a,v,LET(x,CHOOSECOLS(z,COLUMNS(C2:v)),IF(v,HSTACK(a,x),HSTACK(DROP(a,,-1),TAKE(a,,-1)+x))))))

จะเห็นว่าวิธีของ Excel Wizard เขียนสูตรได้สั้นมาก แต่ก็มีความลึกซึ้งสูงมากด้วยเช่นกัน พวกเราสามารถศึกษาวิธีที่ตัวเองชอบได้เลยครับ

เดี๋ยวผมจะอธิบายวิธีแรกของ Excel Wizard ให้

เผื่อเพื่อนๆ จะได้เรียนรู้เทคนิคเจ๋งๆ หลายอย่างที่อยู่ในสูตรนี้ด้วย

เราลองมาคลี่สูตรดูก่อน จะพบว่ามันมีการประกาศตัวแปรด้วย LET แบบนี้

=LET(
y,SCAN(,C2:Q2,LAMBDA(a,v,IF(v,v,a))),
u,UNIQUE(y,1),
HSTACK(B2:B9,MMULT(--C2:Q9,N(u=TRANSPOSE(y))))
)

ซึ่งแปลว่า

• ให้สร้างตัวแปร y ขึ้นมาด้วยสูตร SCAN(,C2:Q2,LAMBDA(a,v,IF(v,v,a)))
• สร้างตัวแปร u ด้วยสูตร UNIQUE(y,1)
• แล้วสุดท้ายให้คำนวณสิ่งนี้ออกมา
  HSTACK(B2:B9,MMULT(–C2:Q9,N(u=TRANSPOSE(y))))

เรามาทำความเข้าใจทีละส่วนกันครับ

คำสั่ง SCAN เป็น Lambda Helper Function ตัวนึงซึ่งสามารถสร้าง Array ที่เป็นการคำนวณสะสม (สะสมในที่นี้ไม่ได้แปลว่ารวมเสมอไป) โดยแสดงผลสะสมระหว่างทางด้วย ใครยังไม่คุ้นกับ LAMBDA และ Helper ลองดูบทความนี้ก่อนครับ

Excel Wizard สร้างตัวแปร y ออกมาเพื่อสร้างเลขปีให้มันไม่ว่าง ดังนี้

วิธีทำงานคือ ให้ Scan รับค่า C2:Q2 (Range ปีที่มีแหว่งๆ) เข้ามา แล้วส่งเข้า LAMBDA โดยที่ในนั้นมี Parameter 2 ตัวคือ a กับ v (ตั้งมาแทน Accumulator กับ Value)

• โดยที่ a (Accumulator) คือค่าสะสมที่ได้แต่ละขั้น แต่ว่าแรกสุดจะเอามาจาก Initial Value ซึ่งในที่นี้ปล่อยว่างไว้ คือเป็น 0
• โดยที่ v (Value) คือค่า Array ที่รับเข้ามาจาก Scan ในที่นี้คือ C2:Q2 ซึ่งคือ {2014,0,0,2015,0,0,0,0,2016,0,0,0,2017,2018,0}
• โดยที่ LAMBDA มีการสั่งให้คำนวณโดย IF(v,v,a) แปลว่า ถ้า v มีค่าที่ไม่ใช่ 0 จะเป็น TRUE ก็คือให้ผลเป็น v นั้นๆ นอกนั้นให้มีผลเป็น a (ค่าผลลัพธ์เดิม)
• ขั้นตอนจะเริ่มที่ a = initial = 0 ก่อน แล้วทำ step ถัดไป
• ขั้นแรก v=2014 ทำให้ IF(v,v,a) เป็น TRUE เลยเอา v คือ 2014 ซึ่งผลที่ได้เก็บไว้ใน a
• ขั้นสอง v=0 ทำให้ IF(v,v,a) เป็น FALSE เลยเอา a คือ 2014 ซึ่งผลที่ได้เก็บไว้ใน a ต่ออีก
• ขั้นสาม v=0 ทำให้ IF(v,v,a) เป็น FALSE เลยเอา a คือ 2014 ซึ่งผลที่ได้เก็บไว้ใน a ต่ออีก
• ขั้นสี่ v=2015 ทำให้ IF(v,v,a) TRUE เลยเอา v คือ 2015 ซึ่งผลที่ได้เก็บไว้ใน a ต่ออีก
• ทำไปเรื่อยๆ…. จนครบทุกกค่าใน Array ก็จะได้ผลเป็นปีแบบไม่แหว่ง

ต่อไป u คือ การเอาค่าแบบไม่ซ้ำออกมา จาก v ก็จะได้ดังนี้ อันนี้ง่าย

อันสุดท้าย HSTACK(B2:B9,MMULT(–C2:Q9,N(u=TRANSPOSE(y))))

แปลว่าให้เอา Range B2:B9 มาต่อข้างๆ (Horizontal Stack) ด้วยอีกก้อน
คือ MMULT(–C2:Q9,N(u=TRANSPOSE(y)))

ซึ่งตัวที่ซับซ้อนคือก้อนหลังนี่แหละ

–C2:Q9 คือการบังคับให้ทุกช่องเป็นตัวเลข อันนี้ไม่มีอะไรซับซ้อน

u=TRANSPOSE(y) คือ การเทียบว่า u เท่ากับ TRANSPOSE(y) หรือไม่?

หมายเหตุ : หากเราเอา Array 2 ตัวที่อยู่คนละทิศกัน คือแนวตั้ง กับ แนวนอน มาทำ operation กัน ผลจะออกมาเป็นตาราง cross กัน

พอเอา N มาครอบมันก็จะแปลง TRUE/FALSE เป็น 1 กับ 0 แบบนี้ (เพื่อให้ใช้กับ Matrix Multiplication ได้) ซึ่งจริงๆ อันนี้ใช้ — แทน N ก็ได้นะ

พอใช้ MMULT จับ Matrix 2 ตัวคูณกัน

MMULT(–C2:Q9,N(u=TRANSPOSE(y)))

จะได้แบบนี้

การคูณ Matrix จะจับคู่ Range แถวของตารางแรก คูณ Range คอลัมน์ของตารางสอง ทีละคู่ๆ แล้วหาผลรวม

2014 เกิดจาก 2014*1 + 0*1 + 0*1 + 2015*0 +0*0+….

183 เกิดจาก 54*1 + 37*1 + 92*1 + 37*0 +40*0+….

ทำไปเรื่อยๆ จนครบทุกช่อง จะได้แบบนี้ ซึ่งพูดง่ายๆ คือ ในแต่ละแถวจะรวมเลขของปีเดียวกันเข้าด้วยกันนั่นเอง

พอเอาไป HSTACK ต่อกับ B2:B9 ก็จบเลย จะได้แบบนี้

นี่แค่โจทย์เดียวนะ…

ลองคิดดูว่าการลองฝึกโจทย์ข้อเดียวจาก Challenge ของ Excel Wizard ยังได้เทคนิคหลายอย่างเยอะแยะขนาดนี้ หากคุณไปลองฝึกฝนกับโจทย์ข้ออื่นๆ อีก จะได้ความรู้เยอะขนาดไหน!!

ใครที่เริ่มจัดการกับ Mini Boss เหล่านี้ด้วยตนเองได้ ก็จะเริ่มตี Mini Boss ตัวอื่นได้ด้วยเช่นกัน และสึดท้ายจะตี Boss ใหญ่ที่คุณจะเจอในอนาคตได้อย่างแน่นอนครับ