--- title: แนวทางฝึกฝน Excel ให้เก่งขึ้น url: https://www.thepexcel.com/practice-advanced-excel/ type: post date: 2023-04-02 updated: 2025-12-22 author: Sira Ekabut categories: [Excel ทั่วไป, Excel, Excel Advanced Formula] tags: [DROP, HSTACK, MMULT, TAKE, TRANSPOSE, N, LAMBDA, CHOOSECOLS, UNIQUE, COLUMNS, LET, excel wizard, practice, IF, REDUCE, SCAN] --- # แนวทางฝึกฝน Excel ให้เก่งขึ้น หลายคนถามว่าทำยังไงถึงจะเก่ง Excel มากขึ้น คำตอบง่ายๆ คือพยายามใช้มันแก้ปัญหาใหม่ๆ เรื่อยๆ เพราะยิ่งเจอปัญหาเยอะแล้วเราพยายามแก้ปัญหาจนผ่านไปได้ เราก็ะเก่งขึ้นเหมือนกับสู้กับ Monster เก่งๆ ในเกมจนเรา Level Up ได้นั่นแหละครับ ถ้าเราสู้แต่ Monster อ่อนๆ จะได้ Exp เยอะพอได้ไง? (คนเล่นเกมคงพอเข้าใจเนอะ) แต่ปัญหาคือ หลายคนไม่รู้จะเอาปัญหาจากไหนมาแก้? (ซึ่งก็อาจจะแปลกนิดนึง เพราะงานที่ตัวเองทำก็น่าจะมีปัญหาให้แก้อ่ะนะ 55) แต่ไม่เป็นไร สมมติว่างานที่ทำไม่มีปัญหายากๆ แล้วกัน… วันนี้ผมจะมาแนะนำว่าเราจะหาปัญหาจากไหนมาฝึก Excel แบบ Advance กันดี?? ## แนะนำ Challenge จาก Excel Wizard คำตอบก็คือการฝึกจากโจทย์ที่มีคนมาถามใน Internet ซึ่งเผอิญทาง Excel Wizard ได้เอาโจทย์ที่น่าสนใจจากต่างประเทศมาคอยถามให้พวกเราลองแก้ รวมถึงช่วยกันแชร์วิธีการแก้ปัญหาหลายๆ แบบดู ซึ่งสามารถไปดูได้ที่ [https://web.facebook.com/hashtag/excelchallenge](https://web.facebook.com/hashtag/excelchallenge) ![1](https://www.thepexcel.com/wp-content/uploads/2023/04/Adv-Excel-001.png) ซึ่งปัญหาที่มาถามก็จะมีทั้งประเภทที่ออกแบบมาให้แก้ด้วยสูตรปกติ (เป็นตัวเลขเฉยๆ) กับแบบที่ออกแบบมาให้แก้ด้วย 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](https://www.linkedin.com/posts/excelbi_powerbi-powerquery-dax-activity-6984725315071676416-haHm) - **ไฟล์ Excel ประกอบการฝึก** : [https://lnkd.in/dkmqAJib](https://lnkd.in/dkmqAJib?fbclid=IwAR16mPfwbZ_JHRh06e7Rw-KvsHBenKbtLTfSYXT7ITiYRqUFZovS17Qb2x8) ให้ Save ไฟล์ออกมาไว้ในเครื่องตัวเองแล้วเริ่มแก้โจทย์ เดี๋ยวผมจะลองแก้ด้วย Power Query ดูจะเป็นประมาณนี้ **ให้เลือกข้อมูลแล้วคลิ๊กขวา -> Get Data from Table/Range** ![2](https://www.thepexcel.com/wp-content/uploads/2023/04/Adv-Excel-002.png) - มันจะบังคับให้สร้าง Table (เราไม่ต้องติ๊ก My Table has Header เพราะตัวตารางยังผิดอยู่) - จากนั้น Excel จะดูดข้อมูลนี้เข้าสู่ Power Query Editor ให้เราลบ Step Change Type ออกไปก่อน เพราะหัตารางยังไม่ถูก ไม่ควรทำ Change Type ตอนนี้ ![3](https://www.thepexcel.com/wp-content/uploads/2023/04/Adv-Excel-003-1024x199.png) ### หัวตารางต้องถูกต้องก่อน **หลักการแก้ปัญหาของ Power Query ที่สำคัญสุดคือ “ต้องทำหัวตารางต้องถูกต้องก่อน”** เคสนี้ผมคิดว่าเราควรจัดการเรื่องปีที่ Merge มาก่อน ดั้งนั้นผมจะ Transpose ข้อมูลลงมาก่อนแล้ว คลิ๊กขวา Fill Down ปี ที่ Column 1 ลงมา จะได้แบบนี้ ![4](https://www.thepexcel.com/wp-content/uploads/2023/04/Adv-Excel-004-1024x315.png) ทีนี้ผมคิดว่าเดี๋ยวพอ Transpose กลับไปจะมีปัญหาหัวตารางซ้ำ แล้วพอต้อง Unpivot ข้อมูลลงมาก็จะมีปัญหาอีก ดังนั้นผมจะพยายามสร้างหัวตารางใหม่ให้ไม่ซ้ำ โดยผมจะ Add Index Column ขึ้นมาก่อนดังนี้ ทำการ Merge Columns (ในเมนู Trannsform) กับ Column1 และ Index เข้าด้วยกันด้วยตัวคั่นแปลกๆ เช่น | ![5](https://www.thepexcel.com/wp-content/uploads/2023/04/Adv-Excel-014-1024x434.png) จากนั้น Transpose กลับไปจะได้แบบนี้ ![6](https://www.thepexcel.com/wp-content/uploads/2023/04/Adv-Excel-015-1024x211.png) คราวนี้เราจะ Promote Header แต่ว่าต้องเอาบรรทัดสุดท้ายขึ้นไปก่อน ดังนั้นก็ไปที่ Transform -> Reverse Row แล้วค่อย Promote Header แล้วลบ Change Type ออกไปซะ จะได้แบบนี้ ![7](https://www.thepexcel.com/wp-content/uploads/2023/04/Adv-Excel-016-1024x240.png) ที่นี้ให้เราคลิ๊กขวา Column แรกแล้ว Unpivot Other Columns ลงมาให้หมด จะได้แบบนี้ ![8](https://www.thepexcel.com/wp-content/uploads/2023/04/Adv-Excel-017-1024x259.png) จากนั้นให้ Split Attribute ด้วย Delimiter คือ | จะได้แบบนี้ ![9](https://www.thepexcel.com/wp-content/uploads/2023/04/Adv-Excel-018-1024x287.png) ### ถ้าหัวตารางไม่มีปัญหาแล้ว อะไรก็ง่ายละ คราวนี้ให้ลบ Attribute.2 ออก แล้วเลือก คอลัมน์ Attribute.1 แล้ว Transform -> Pivot Columns โดยเอา Value เป็นคอลัมน์ value แบบนี้ ![10](https://www.thepexcel.com/wp-content/uploads/2023/04/Adv-Excel-019-1024x424.png) แล้วเราก็จะได้ผลลัพธ์แบบที่โจทย์ต้องการดังนี้ ![11](https://www.thepexcel.com/wp-content/uploads/2023/04/Adv-Excel-020-1024x312.png) สรูป 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 ลองดูบทความนี้ก่อนครับ](https://www.thepexcel.com/lambda-excel-365/) Excel Wizard สร้างตัวแปร y ออกมาเพื่อสร้างเลขปีให้มันไม่ว่าง ดังนี้ ![12](https://www.thepexcel.com/wp-content/uploads/2023/04/Adv-Excel-021-1024x100.png) วิธีทำงานคือ ให้ 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 คือ 201**4 ซึ่งผลที่ได้เก็บไว้ใน a - ขั้นสอง v=0 ทำให้ **IF(v,v,a) เป็น FALSE เลยเอา a คือ 201**4 ซึ่งผลที่ได้เก็บไว้ใน a ต่ออีก - ขั้นสาม v=0 ทำให้ **IF(v,v,a) เป็น FALSE เลยเอา a คือ 2014** ซึ่งผลที่ได้เก็บไว้ใน a ต่ออีก - ขั้นสี่ v=2015 ทำให้ **IF(v,v,a) ****TRUE เลย**เอา v คือ 201****5 ซึ่งผลที่ได้เก็บไว้ใน a ต่ออีก - ทำไปเรื่อยๆ…. จนครบทุกกค่าใน Array ก็จะได้ผลเป็นปีแบบไม่แหว่ง **ต่อไป u คือ การเอาค่าแบบไม่ซ้ำออกมา จาก v** ก็จะได้ดังนี้ อันนี้ง่าย ![13](https://www.thepexcel.com/wp-content/uploads/2023/04/Adv-Excel-022-1024x148.png) อันสุดท้าย HSTACK(B2:B9,MMULT(–C2:Q9,N(u=TRANSPOSE(y)))) แปลว่าให้เอา Range B2:B9 มาต่อข้างๆ (Horizontal Stack) ด้วยอีกก้อน คือ MMULT(–C2:Q9,N(u=TRANSPOSE(y))) ซึ่งตัวที่ซับซ้อนคือก้อนหลังนี่แหละ –C2:Q9 คือการบังคับให้ทุกช่องเป็นตัวเลข อันนี้ไม่มีอะไรซับซ้อน ![14](https://www.thepexcel.com/wp-content/uploads/2023/04/Adv-Excel-023-1024x311.png) u=TRANSPOSE(y) คือ การเทียบว่า u เท่ากับ TRANSPOSE(y) หรือไม่? **หมายเหตุ** : หากเราเอา Array 2 ตัวที่อยู่คนละทิศกัน คือแนวตั้ง กับ แนวนอน มาทำ operation กัน ผลจะออกมาเป็นตาราง cross กัน ![15](https://www.thepexcel.com/wp-content/uploads/2023/04/Adv-Excel-025-1024x506.png) พอเอา N มาครอบมันก็จะแปลง TRUE/FALSE เป็น 1 กับ 0 แบบนี้ (เพื่อให้ใช้กับ Matrix Multiplication ได้) ซึ่งจริงๆ อันนี้ใช้ — แทน N ก็ได้นะ ![16](https://www.thepexcel.com/wp-content/uploads/2023/04/Adv-Excel-026.png) พอใช้ MMULT จับ Matrix 2 ตัวคูณกัน MMULT(–C2:Q9,N(u=TRANSPOSE(y))) จะได้แบบนี้ การคูณ Matrix จะจับคู่ **Range แถวของตารางแรก คูณ Range คอลัมน์ของตารางสอง ทีละคู่ๆ แล้วหาผลรวม** 2014 เกิดจาก 2014*1 + 0*1 + 0*1 + 2015*0 +0*0+…. ![17](https://www.thepexcel.com/wp-content/uploads/2023/04/Adv-Excel-030-1024x387.png) 183 เกิดจาก 54*1 + 37*1 + 92*1 + 37*0 +40*0+…. ![18](https://www.thepexcel.com/wp-content/uploads/2023/04/Adv-Excel-031-1024x387.png) ทำไปเรื่อยๆ จนครบทุกช่อง จะได้แบบนี้ ซึ่งพูดง่ายๆ คือ **ในแต่ละแถวจะรวมเลขของปีเดียวกันเข้าด้วยกันนั่นเอง** ![19](https://www.thepexcel.com/wp-content/uploads/2023/04/Adv-Excel-032.png) พอเอาไป HSTACK ต่อกับ B2:B9 ก็จบเลย จะได้แบบนี้ ![20](https://www.thepexcel.com/wp-content/uploads/2023/04/Adv-Excel-033.png) ## นี่แค่โจทย์เดียวนะ… ลองคิดดูว่าการลองฝึกโจทย์ข้อเดียวจาก Challenge ของ Excel Wizard ยังได้เทคนิคหลายอย่างเยอะแยะขนาดนี้ **หากคุณ[ไปลองฝึกฝนกับโจทย์ข้ออื่นๆ อีก](https://www.facebook.com/hashtag/excelchallenge) จะได้ความรู้เยอะขนาดไหน!!** ใครที่เริ่มจัดการกับ Mini Boss เหล่านี้ด้วยตนเองได้ ก็จะเริ่มตี Mini Boss ตัวอื่นได้ด้วยเช่นกัน และสึดท้ายจะตี Boss ใหญ่ที่คุณจะเจอในอนาคตได้อย่างแน่นอนครับ --- _Source: [https://www.thepexcel.com/practice-advanced-excel/](https://www.thepexcel.com/practice-advanced-excel/)_