ในเมื่อเราจะมาเจาะลึกวิธีใช้ Excel ทำรายงานให้มีประสิทธิภาพสูงสุด ดังนั้นเราควรวิเคราะห์ดูก่อนว่า การจะทำรายงานให้สำเร็จได้นั้นโดยภาพรวมมีขั้นตอนอะไรและความเชื่องช้าอยู่ตอนไหนบ้าง?
Step การทำรายงาน
- เตรียมฐานข้อมูล
- คำนวณแปลงข้อมูลดิบให้เป็นผลสรุป
- Visualization
- สรุปและตีความหมายนำข้อมูลไปใช้ประโยชน์จริง ๆ
- ทำซ้ำหากข้อมูลมีการเปลี่ยนแปลง เช่น ต้องทำข้อมูลเดือนใหม่ในรูปแบบเดิมอีก
เตรียมฐานข้อมูล
ขั้นตอนนี้ ความซับซ้อนและความเสียเวลาขึ้นอยู่กับว่าแหล่งข้อมูลที่แท้จริงเรามาจากไหน และมีลักษณะอย่างไร? เช่น
- ข้อมูลอยู่ในไฟล์ Excel เดียวกับที่ทำงานอยู่ หรือ อยู่ที่อื่น?
- ข้อมูลมีตารางเดียว หรือ หลายตาราง?
- เป็นแบบเพิ่มคอลัมน์ หรือ เพิ่มเแถว หรือ ทั้งคู่เลย?
- การรวมข้อมูลนั้นรวมได้ตรงๆ เลย หรือต้องมีการคัดเลือกข้อมูลบางส่วน
ถ้าอยู่ในไฟล์เดียวกัน มีตารางเดียว นี่จะง่ายที่สุด เพราะเราเอา Data ตัวนั้นให้เป็นแหล่งข้อมูลหลักที่จะเอาไปทำต่อได้เลย แต่ถ้าข้อมูลอยู่ที่อื่น หรือมีหลายตาราง เราก็ต้องพยายามรวบรวมข้อมูลทุกที่ให้มาอยู่ในที่เดียวกันก่อน
ซึ่งทำได้หลายแบบ หลายวิธีการ เช่น
- Copy Paste เอาเองแบบ Manual
- เขียนสูตรพวก LOOKUP อย่างเช่น VLOOKUP, INDEX, MATCH, OFFSET, INDIRECT
- ใช้ VBA (เขียนโปรแกรมใน Excel) มาช่วย
- Power Query
ซึ่ง Power Query นี่แหละเป็นวิธีที่ง่ายแต่ว่าทรงพลังมากๆ ซึ่งเป็นสิ่งที่ผมจะเน้นในหนังสือเล่มนี้ครับ
คำนวณแปลงข้อมูลดิบให้เป็นผลสรุป
ขั้นตอนนี้ มีแนวทางการทำหลักๆ 2 วิธี คือ
- การเขียนสูตรสรุปผล เช่น พวก SUMIFS, COUNTIFS
- การใช้ PivotTable จะสรุปเบื้องต้นด้วยลาก Field มาเลย หรือจะสร้างการคำนวณพิเศษจาก Calculated Field หรือสร้างสูตรสรุปข้อมูลที่ Advance ขึ้นไปอีกด้วย Measure ก็ได้
ซึ่งส่วนตัวผมเองขอเชียร์การใช้เครื่องมืออย่าง PivotTable มากกว่าเพราะมันทั้งง่ายในการสร้างและปรับเปลี่ยนมุมมองได้ง่ายด้วย คนทั่วไปใครๆ ก็ใช้ได้ แถมยังใช้เวลาเรียนรู้น้อยกว่าการเขียรสูตรมากเลย และยังผนวกกับการใช้เรื่องมือ Slicer ที่สามารถใช้ Filter Pivot Table ได้หลายตารางพร้อมกันยิ่งเจ๋งเลย
ดังนั้นในหนังสือเล่มนี้เราจะเน้นกันที่การใช้ Pivot Table กันครับ คนไหนที่ยังใช้ไม่เป็นก็ไม่ต้องกังวล เพราะเราจะทบทวนพื้นฐานความรู้ Pivot Table ให้ในบทที่เป็น Workshop ด้วยครับ
Visualization
ขั้นตอนนี้ก็มักขึ้นอยู่กับวิธีที่เราเลือกสรุปข้อมูลเช่นกัน ถ้าตอนแรกเขียนสูตรมา ก็ต้องสร้างกราฟแบบปกติ
ถ้าใช้ Pivot Table ก็ต้องสร้างกราฟแบบ Pivot Chart…
ซึ่งกราฟแบบปกติจะมีความยืดหยุ่นมากกว่า เช่น สามารถใช้กราฟได้ทุกประเภทรวมถึง Scatter Plot ด้วย (Pivot Chart ทำ Scatter Plot ไม่ได้) รวมถึงการเขียนสูตรจะช่วยเตรียมข้อมูลในการทำกราฟแบบพิสดารได้ดีกว่า PivotTable มากเลย
ตรงนี้เหมือนว่าการใช้ Pivot Table จะเสียเปรียบพอสมควร ซึ่งทาง Microsoft เองเลยทำเครื่องมือใหม่มาใช้เพื่อทำกราฟหรือ Visualization เจ๋งๆ โดยเฉพาะ นั่นก็คือ Power BI นั่นเอง
โดยเจ้าตัวนี้มีความคล้ายกับการใช้งาน Pivot Table / Pivot Chart ค่อนข้างมาก แต่เจ๋งกว่าในหลายๆ ด้าน เช่น สามารถกดที่กราฟเพื่อ Filter ข้อมูลส่งไปควบคุมกราฟตัวอื่นแสดงเฉพาะสิ่งที่เลือก (ทำเหมือนว่ากราฟเป็น Slicer นั่นแหละ) หรือ Drill Down ขุดลึกลงไปดูข้อมูลชั้นถัดไปได้ แถมยังรองรับการสร้างหรือดาวน์โหลดกราฟแบบแปลกๆ ขึ้นมาเองได้ด้วย (เรียกว่า Custom Visual)
อย่างไรก็ตามกราฟส่วนใหญ่ที่ทุกคนทำก็มักจะไม่พ้นกราฟแท่ง กราฟเส้น กราฟวงกลม แบบธรรมดาๆ นี่แหละครับ ดังนั้นการใช้ Pivot Table ธรรมดาแล้วสร้างเป็น Pivot Chart เมื่อประกอบกับ Slicer แล้วก็สามารถช่วยให้เราสร้าง Dashboard เจ๋งๆ ได้แล้ว
สรุปและตีความหมายนำข้อมูลไปใช้ประโยชน์จริง ๆ
ขั้นตอนนี้ต้องอาศัยการใช้เวลากับการสำรวจผลลัพธ์ที่ได้ว่ามีประเด็นอะไรที่น่าสนใจหรือไม่? เช่น
- สินค้าไหนขายดี/ไม่ดี อะไรคือสินค้าระดับ Top ไม่กี่ตัวที่สร้างรายได้มหาศาล
- ลูกค้ารายได้คือลูกค้าที่สร้างรายได้ให้บริษัทมากที่สุดอันดับต้นๆ
- สัดส่วนการขายสินค้าแต่ละประเภทเป็นกี่ %
- sales คนไหนประสิทธิภาพสูง/ต่ำ
- ทำกราฟแสดงความสัมพันธ์ของตัวแปรต่างๆ input แล้ว Output มีความสัมพันธ์อย่างไร?
ซึ่งเรื่องพวกนี้เราสามารถทำตารางสรุปออกมาได้เลยอยู่แล้วใน Pivot Table เช่น
- แสดงสัดส่วนด้วยการปรับการแสดงข้อมูล Show Value As ให้เป็น %
- หาสินค้าระดับ Top ด้วยใช้ Sort เรียงข้อมูลจัดอันดับ หรือการ Filter แบบ Top N เพื่อแสดงเฉพาะข้อมูลตัว Top หรือ Bottom ตามที่ต้องการ
ทั้งนี้เพื่อหาทางนำ Insight ที่ได้ไปพัฒนาตัวธุรกิจของบริษัทเราต่อไป ตรงขั้นตอนนี้แหละถึงจะเกิดคุณค่าจริงๆ กับบริษัทของคุณ ซึ่งตรงนี้มันแล้วแต่ธุรกิจใครธุรกิจมัน หนังสือสอน Excel คงสอนเรื่องนี้ไม่ได้นะครับ
แต่สิ่งที่ผมจะแนะนำได้คือ…หากเราใช้เวลาทำงาน Step ก่อนหน้าเยอะเกินไป จนไม่มีเวลาเพียงพอที่จะมานั่งวิเคราะห์ข้อมูลต่อ คุณค่าที่แท้จริงก็จะไม่เกิดขึ้นเลย เราควรทำให้ Step ก่อนหน้าเสร็จเร็วที่สุด เพื่อให้มีเวลามาปรับปรุงธุรกิจมากที่สุดนั่นเอง
จำเอาไว้ให้ดีว่า การได้แค่ตัวเลขในรายงานออกมา แต่ไม่ได้ตีความหมายนำไปปรับปรุงการทำงานอะไรเลย มันก็ไม่มีประโยชน์อยู่ดีครั
ทำซ้ำหากข้อมูลมีการเปลี่ยนแปลง
การทำซ้ำนี่แหละคือหัวใจของงาน Routine วิธีไหนสามารถทำซ้ำได้รวดเร็วเท่าไหร่ วิธีนั้นยิ่งมีประสิทธิภาพสูง ยิ่งเราลดการ Manual ได้มากเท่าไหร่ ยิ่งสามารถทำซ้ำได้รวดเร็วมากขึ้นเท่านั้น
ซึ่งหนังสือเล่มนี้จะเน้นช่วยให้ขั้นตอนนี้ทำได้ง่ายที่สุด ด้วย Power Query + Pivot Table จะทำให้คุณสามารถ “แค่กดปุ่ม Refresh เท่านั้น”
คิดดูว่าจะเจ๋งแค่ไหน ถ้าจะทำรายงานเดือนใหม่ได้แค่การกด Refresh แค่ปุ่มเดียวจบ (นอกจากจะกด Refresh เอง เราสามารถตั้งให้มัน Auto Refresh ทุกๆ xx นาทีได้ด้วยนะ) ซึ่งผมคิดว่าไม่มีอะไรเจ๋งและน่าทึ่งกว่านี้อีกแล้วล่ะครับ ^^
ปัญหาของการเตรียมข้อมูลเพื่อทำ Pivot Table
ถ้าใครเคยใช้ Pivot Table มาก่อนจะรู้ว่าตอนทำ Report ด้วย Pivot Table นี่มันง่ายมากจริงๆ นะ เพราะเราแค่ลาก Field ไปลง Block 4 ช่องที่กำหนดไม่กี่วินาทีก็เสร็จแล้ว
ดังนั้นความยากของการทำ Pivot Table จริงๆ แล้วไม่ได้อยู่ที่ตอนทำ Pivot Table แต่อยู่ที่การเตรียมข้อมูลให้อยู่ในรูปแบบที่เหมาะสมตั้งแต่แรก หรือที่เรียกว่า “การ Clean ข้อมูล” ต่างหากล่ะ
ปัญหา 1 : ข้อมูลไม่อยู่ในรูปแบบ Database
ปัญหาแรกเลยคือ ข้อมูลที่เตรียมไว้ไ่ม่อยู่ในรูปแบบ Database
ลักษณะข้อมูลที่เป็น Database เป็นยังไง?
- เก็บข้อมูลเป็นตาราง โดยมีหัวตาราง 1 บรรทัด แค่ครั้งเดียวพอ
- หัวตารางบอกว่าข้อมูลในคอลัมน์นั้นคือเรื่องอะไร และมีครบทุกคอลัมน์
- แต่ละคอลัมน์เก็บข้อมูลเรื่องเดียวกันทั้งคอลัมน์
- ไม่มีการละเว้นข้อมูลเอาไว้ในฐานที่เข้าใจ ยกเว้นจะหมายถึงช่องที่เป็นค่าว่างจริงๆ
- เก็บแค่ตัวข้อมูล ไม่ต้องมีบรรทัดสรุปข้อมูล (เพราะเดี๋ยวเราจะไปใช้ Pivot Table สรุปไง)
ประเด็นที่จะทำให้หลายคนมีปัญหาในเรื่องนี้ คือ บางครั้งข้อมูลที่ได้มา ไม่ได้เก็บข้อมูลเรื่องเดียวกันทั้งคอลัมน์ ซึ่งสาเหตุมักจะเกิดจากเราต้องการให้ง่ายต่อคนกรอกข้อมูล จึงเลือกที่จะให้กรอกข้อมูลลงใน Excel ในรูปแบบของแบบฟอร์ม หรือกรอกลงไปใน Report เลยมากกว่า ทั้งนี้เพราะการ Input ข้อมูลลงไปในรูปแบบ Database ตรงๆ เลยมันยากและซ้ำซ้อนมากนั่นเอง
บางทีเราอาจได้ข้อมูลมาแบบนี้ ซึ่งเรียกว่าอยู่ในรูปแบบของ Report มากกว่า Database ซึ่งแม้จะดูแล้วเข้าใจง่าย แต่เอาไปทำงานต่อได้ยาก เพราะจะอ้างอิงข้อมูลค่อนข้างลำบาก
ซึ่งหลายองค์กรแก้ปัญหานี้ด้วยการมีโปรแกรมสำเร็จรูปในการกรอกข้อมูล แล้วสามารถ Export ข้อมูลออกมาทำต่อใน Excel ได้ ซึ่งการ Export ข้อมูลจากโปรแกรมจะมี 2 ลักษณะใหญ่ๆ คือ
- ออกมาเป็นรูปแบบ Report : ถ้าเป็นแบบนี้ต้องมานั่งแปลงข้อมูลก่อน ก็จะยากกว่า
- ออกมาเป็นรูปแบบ Database : ถ้าเป็นแบบนี้ก็จะสบายหน่อยครับ แต่ถ้ามีหลายไฟล์ก็เริ่มยาก
ปัญหา 2 : ข้อมูลกระจัดกระจายอยู่หลายที่
ไฟล์ต้นทางอาจมีหลายไฟล์หรือหลาย Table ซึ่งจะมี 2 ลักษณะ คือ
- เป็นฐานข้อมูลเดียวกัน หน้าตาโครงสร้างเหมือนกัน แต่ข้อมูลคนละชุด เช่น เป็นคนละเดือน เป็นต้น
- เป็นฐานข้อมูลคนละเรื่องเลย แต่มีความเกี่ยวข้องกับข้อมูลหลักที่เราต้องการใช้ เช่น เป็นตารางอ้างอิงที่บอกว่ารหัสสินค้าแต่ละ code คือสินค้าอะไร กลุ่มไหน เป็นต้น
ถ้าเป็นแบบข้อ 1 เราก็ต้อง Copy Data มาต่อท้ายรวมกันเป็นไฟล์เดียว (จำนวนแถว หรือ Record เยอะขึ้น)
ถ้าเป็นแบบข้อ 2 เราก็ต้องใช้พวกกลุ่ม LOOKUP เพื่อ Map ดึง Field ต้องการมาใช้อีกที (คอลัมน์ หรือ Field เยอะขึ้น)
ซึ่งทั้งสองแบบนี้เสียเวลามากๆ จริงมั้ยครับ??
ปัญหา 3 : ข้อมูลมีไฟล์หลาย Format
ไฟล์ต้นทางอาจจะอยู่ในหลายรูปแบบ เช่น Excel, Text File, CSV, Website, Access, Database จริงๆ และอีกมากมาย ทำให้เราต้องหาเอาข้อมูลเหล่านั้นมายัดลงในไฟล์ Excel อีกที ซึ่งก็เสียเวลาอีก
ปัญหา 4 : ยังมี Field ที่ต้องการไม่ครบ
อันนี้ต้องใช้พลังและความรู้ในการเขียนสูตรเพื่อดึงค่าหรือสกัดเอาค่าที่ต้องการมาให้ได้
เช่น ถ้าอยากจะวิเคราะห์ว่าวันธรรมดากับวันหยุดสุดสัปดาห์มีผลต่อยอดขายต่างกันอย่างไร? เราก็ต้องสร้าง Field ที่บอกว่าวันที่เราสนใจนั้นๆ เป็นวันธรรมดา หรือ วันหยุด ให้ได้ซะก่อน
ซึ่งเราอาจจะใช้ฟังก์ชันพวก WEEKDAY มาเช็คก็ได้ครับ แล้วอาจใช้ IF มาช่วยอีกทีว่าถ้าออกมาเป็นวันอะไรแล้วจะให้ขึ้นแสดงว่าอะไร? ซึ่งก็ต้องใช้ความรู้เรื่องการเขียนสูตรมาเติมเต็มเรื่องพวกนี้อีก ซึ่งบางคนอาจจะยังเขียนสูตรได้ไม่เก่ง ก็จะติดปัญหาอีก
แล้วจะทำไงให้ข้อมูลพร้อมใช้?
ยิ่งข้อมูลต้นฉบับมีหน้าตาต่างจากรูปแบบ Database ที่ต้องการมากเท่าไหร่ เราจะยิ่งปวดหัวมากขึ้นในการแปลงข้อมูลพวกนี้ ซึ่งแนวทางในการทำสามารถทำได้หลายแบบ เช่น
- เตรียมด้วยสูตรกลุ่ม Lookup เช่น VLOOKUP, INDEX, MATCH, OFFSET, INDIRECT
- เตรียมด้วย VBA เช่น เขียนให้ Copy ข้อมูลจากหลายที่มารวมกันในที่เดียว
ในเมื่อไม่ใช่ทุกคนที่จะทำเรื่องสูตรยากๆ และ VBA ได้ สุดท้ายคนส่วนใหญ่ก็จบลงที่การทำแบบ Manual นี่แหละครับ ซึ่งอาจใช้การ Copy Paste หรือตัดใจไม่ทำ Pivot Table แต่ไป Manual เขียนสูตรทีละช่องที่ตัว Report เลย ซึ่งก็จะทำให้เกิดความยากอีกแบบนึงอีก ซึ่งเสียเวลามากๆ
ทางออกคือ เครื่องมือที่ชื่อว่า Power Query นี่แหละ ซึ่งจะช่วยให้เราสามารถแปลงข้อมูลให้อยู่ในรูปแบบ Database ที่ต้องการได้อย่างง่ายดายมากๆ ซึ่งต่อจากส่วนนี้เราจะไปลงรายละเอียดเรื่อง Power Query กันแล้วล่ะครับ!!
บทความนี้มีที่มายังไง?
บทความนี้เป็นส่วนหนึ่งของตัวอย่างจาก หนังสือ Excel Power Up! เพิ่มพลังการใช้ Excel ของคุณด้วย Power Query โดยผมเอาเนื้อหาบทแรกๆ ซัก 25-30% มาลงในเว็บให้อ่านกันฟรีๆ เลย คนอ่านจะได้ตัดสินใจได้ว่าอยากจะรู้เรื่องราวหลังจากนั้นอีกมั้ย? ซึ่งแค่นี้ก็น่าจะช่วยงานคุณได้เยอะพอสมควรแล้วล่ะ
หากสนใจอ่านตัวอย่างบทอื่นๆ ของหนังสือ ลองดูที่สารบัญข้างล่างได้เลยครับ ^^
สารบัญ Power Query
บทนำ : ทำไมต้องเรียนรู้ Power Query? [ไฟล์ประกอบ]
บทที่ 1 : เข้าใจขั้นตอนการทำรายงานสรุป / วิเคราะห์ข้อมูล [ไฟล์ประกอบ]
บทที่ 2 : ก้าวสู่การเตรียมข้อมูลยุคใหม่ด้วย Power Query [ไฟล์ประกอบ]
บทที่ 3 : ภาพรวมการทำงานกับ Power Query [ไฟล์ประกอบ]
บทที่ 4 : งานที่ยุ่งยากใน Excel กลับง่ายมากใน Power Query [ไฟล์ประกอบ]
บทที่ 5 : การจัดการหัวตาราง [ไฟล์ประกอบ]
บทที่ 6 : การคำนวณเบื้องต้น [ไฟล์ประกอบ]
บทที่ 7 : การกำจัดข้อมูลที่ไม่ต้องการ [ไฟล์ประกอบ]
บทที่ 8 : การสร้างคอลัมน์ใหม่แบบกำหนดเองด้วย Custom Column [ไฟล์ประกอบ]
บทที่ 9 : การสร้างคอลัมน์ใหม่ตามเงื่อนไข [ไฟล์ประกอบ]
บทที่ 10 : การรวมกลุ่มข้อมูลด้วย Group By [ไฟล์ประกอบ]
บทที่ 11 : การพลิกคอลัมน์เป็นหัวตารางด้วย Pivot Column [ไฟล์ประกอบ]
บทที่ 12 : การยุบหัวตารางหลายคอลัมน์ให้เหลือคอลัมน์เดียวด้วย Unpivot [ไฟล์ประกอบ]
บทที่ 13 : การแยกข้อมูลในคอลัมน์เดียวออกจากกันด้วย Split Column [ไฟล์ประกอบ]
บทที่ 14 : การใช้ Query เป็นตัวแปร [ไฟล์ประกอบ]
บทที่ 15 : การรวมข้อมูลจากหลาย Query [ไฟล์ประกอบ]
บทที่ 16 : การดึงข้อมูลจาก Excel ไฟล์อื่น [ไฟล์ประกอบ]
บทที่ 17 : การดึงข้อมูลจาก Text File/ CSV File [ไฟล์ประกอบ]
บทที่ 18 : การดึงข้อมูลจากทุก File ที่ต้องการใน Folder [ไฟล์ประกอบ]
บทที่ 19 : การดึงข้อมูลจากแหล่งอื่นๆ
บทที่ 20 : การเตรียม Data เพื่อทำ Dashboard
บทที่ 21 : การทำ Pivot Table เพื่อสร้าง Dashboard
บทที่ 22 : เจาะลึก M Code หัวใจของ Power Query
บทที่ 23 : Function คือ ขุมพลังที่แท้จริงของ M Code [ไฟล์ประกอบ]
บทที่ 24 : ตัวอย่างการสร้าง Custom Function [ไฟล์ประกอบ]
บทที่ 25 : การวน Loop [ไฟล์ประกอบ]
บทส่งท้าย : เทพที่แท้จริง
อ่านเนื้อหาบท 22 เป็นต้นไปแบบปรับปรุงใหม่ได้ฟรี ที่นี่ (อัปเดทเรื่อยๆ)
Facebook Group : Power Query Thailand
ผู้ที่สนใจ Power Query อย่างคุณที่มาอ่านบทความนี้ ผมขอเชิญชวนเข้ากลุ่มปิด Power Query Thailand ได้ตาม Link นี้ครับ
