บทความนี้เป็นตัวอย่างตอนที่ 3 ของ Series เนื้อหา Power Query ซึ่งเป็นตัวอย่างจากหนังสือ Excel Power Up! (ใครสนใจตอนก่อนหน้านี้ให้ไปดูสารบัญด้านล่างนะครับ)
ดูแบบคลิปวีดีโอได้ที่นี่
สารบัญ
Power Query คืออะไรกันแน่?
Power Query ก็คือ เครื่องมือที่สามารถ “ดึงข้อมูลอย่างทรงพลัง” นั่นคือมีความสามารถในการดึงข้อมูลได้จากแหล่งข้อมูลที่หลากหลายและสามารถปรับเปลี่ยนรูปแบบหน้าตาข้อมูลผลลัพธ์ได้ตามต้องการ
จุดประสงค์หลักเพื่อทำให้ข้อมูลเน่าๆ (ที่อาจจะมาจากหลายแหล่ง หลาย Format หลายหน้าตา) ให้มาอยู่ในรูปแบบที่ Database เพื่อให้สามารถนำไปวิเคราะห์ต่อใน PivotTable, Power Pivot, Power BI ได้
และเมื่อทำเสร็จแล้ว 1 ที หากจะทำซ้ำครั้งถัดไป แค่กด Refresh ทุกอย่างก็จบได้อย่างง่ายดาย
Power Query มีจุดเด่นยังไง?
- รวบรวมข้อมูลได้จากหลายที่ หลายรูปแบบ
- ใช้งานง่าย มีปุ่มเมนูให้กด ไม่จำเป็นต้องเขียนสูตร
- ทุกการกระทำจะถูกบันทึกเป็น Steps เอาไว้ (คล้ายๆ Record Macro แต่ง่ายกว่า)
- ทำปุ๊ปเห็น Preview ผลลัพธ์ปั๊ป ไม่ต้องจินตนาการมากเท่าการเขียนสูตรหรือ VBA
- สามารถแก้ไข สลับลำดับ หรือลบ Steps ที่ทำไว้ได้อย่างง่ายดาย
- ทำซ้ำง่าย แค่กด Refresh มันจะทำซ้ำ Action ที่บันทึกไว้ทันที
- แยกส่วน Query แล้วเอามาเชื่อมต่อกันทีหลังได้ ทำให้สร้างระบบที่ซับซ้อนได้
- รองรับการทำงานที่ Advance มากขึ้นด้วย M Code ซึ่งเป็นภาษาพิเศษของ Power Query
สรุป คือ Power Query ง่ายพอที่จะทำงานโหดๆ ที่เดิมต้องใช้สูตรยากๆ หรือ VBA ได้ด้วยแค่ปุ่มเมนูมาตรฐาน แต่ก็ยืดหยุ่นพอที่จะยอมให้เราแก้ไขเรื่อง Advance มาก ๆ ได้ด้วย M Code
ข่าวดีคือ ส่วนที่ง่ายของ Power Query นั้นใช้เวลาเรียนรู้แป๊ปเดียวก็เข้าใจ แถมยังครอบคลุมการทำงานได้ซัก 70%-80% แล้วครับ! ดังนั้นมันเป็นอะไรที่ทุกคนสามารถทำได้จริงๆ
Computer คุณพร้อมใช้ Power Query หรือยัง?
หากคุณมี Excel 2016 ขึ้นไป (ทั้ง Excel 2016, Excel 2019 หรือ Excel 365) แสดงว่าคุณมี Power Query อยู่แล้วใน Ribbon ชื่อ Data → Get & Transform นะครับ ไม่ต้องทำอะไรเพิ่มก็ใช้ได้เลย (แต่อย่าลืม check การอัปเดทให้ล่าสุดเสมอ ไม่งั้นอาจมีเครื่องมือไม่ครบได้นะ)
หากคุณมี Excel 2010/2013 ต้องไปโหลด Power Query Add-in มาลงเพิ่ม วิธีคือพิมพ์คำว่า “power query add in” ใน Google ก็น่าจะเจอครับ (อย่าลืมหา Link ที่ดูใหม่หน่อย และต้องเลือกเรื่อง 32bit/64bit ให้ตรงกับ Version ของ Excel คุณด้วยนะ ถ้าไม่แน่ใจก็โหลดมันทั้งคู่เลย ดูว่าตัวไหนลงได้ก็ตัวนั้นแหละ)
พอโหลดมาลงได้แล้วจะมี Ribbon ชื่อ Power Query โผล่ขึ้นมาให้เลือกครับ
แต่ถ้าคุณมี Excel เก่ากว่า 2010 หรือว่ามีปัญหาอะไรซักอย่างจนลง Power Query ไม่ได้ ก็ยังเหลืออีกทางเลือกนึง คือ ให้ไปใช้ Power Query ในโปรแกรม Power BI Desktop เลย เพราะมันทั้งฟรี ดีกว่า แถมทำงานเร็วกว่า Power Query ของ Excel ด้วยซ้ำ (ซึ่งจริงๆ จะมี Excel version ไหนก็ตามหรือไม่มี Excel เลย ก็ไปโหลดมาใช้ได้นะ)
Power BI Desktop คืออะไร?
Power BI Desktop เป็นโปรแกรม Business Intelligent ที่มีความสามารถของ 3 เครื่องมือรวมกัน คือ
- Power Query – รวบรวมและดัดแปลงข้อมูล
- Power Pivot – คำนวณสรุปข้อมูล
- Power View – นำเสนอข้อมูล
ความเจ๋งคือ พวกเราสามารถโหลดมาใช้ได้ฟรี แม้จะไม่มีโปรแกรม Microsoft Office ในเครื่องเลยด้วยซ้ำ
วิธีโหลดมี 2 วิธี คือ
- โหลดเป็นโปรแกรม ซึ่งจะโหลดมาเป็นโปรแกรมที่เราต้องคอยอัปเดทเองทุกเดือน ซึ่ง Search ผ่าน Google ได้เลยว่า “Power BI Desktop Download” หรือไปที่ https://powerbi.microsoft.com/en-us/desktop/ ก็ได้
- โหลดเป็น App ผ่าน Microsoft Store ซึ่งจะดีกว่าวิธีบนเพราะมันจะสามารถ Auto Update ตัวเองผ่าน Microsoft Store ได้ครับ (ผมแนะนำแบบนี้ครับ) ใครสนใจคลิ๊ก link นี้ครับ https://www.microsoft.com/store/productId/9NTXR16HNW1T?fbclid=IwAR1QWyuYhlENqnQ2Cc-4owlHor5HX861zHC-JETl34aqnaeURfjFhupjLso
ที่เจ๋งมากๆ คือ เจ้า Power BI Desktop เนี่ยมีการอัปเดทความสามารถใหม่ๆ ทุกเดือนด้วย (โคตรเจ๋ง!)
ดังนั้นผมขอแนะนำให้ผู้อ่านทุกท่านเลือกเอาว่าจะใช้ Power Query ใน Excel หรือจะใช้ใน Power BI Desktop ก็ได้ครับ แต่ถ้าใช้ใน Power BI ก็จะมั่นใจว่าได้เวอร์ชันใหม่ล่าสุดแน่นอน แต่ตัวอย่างในหนังสือเล่มนี้จะใช้ Power Query จากใน Excel365 ครับ
Computer ที่ออฟฟิศลงโปรแกรมเองไม่ได้?
ผมแนะนำว่า หาก Computer ที่คุณใช้ไม่สามารถลงโปรแกรมดังกล่าวเองได้ ให้ลองไปเจรจากับทาง IT ให้ลงโปรแกรมให้ครับ ถ้าทาง IT ไม่ยอม ผมแนะนำให้ลองขอหัวหน้าของเราให้ไปช่วยคุยอีกทีครับ 555
สาเหตุเพราะโปรแกรม Power Query มันมีประโยชน์มากและช่วยลดเวลาทำงานได้มหาศาลเลยล่ะ หากคำนึงถึงเรื่องราคาก็ไม่ต้องห่วงเพราะมันฟรีครับ และไม่ผิดลิขสิทธิ์ด้วย คุ้มกว่านี้ไม่มีแล้วครับ
ลองคิดดูว่างานที่เดิมต้องทำ 2 ชั่วโมง อาจจะเสร็จได้ในเวลาไม่ถึง 5 นาทีเลยก็ได้นะ!
สัมผัสความเจ๋งของ Power Query
ในส่วนนี้ผมอยากให้คุณเห็นถึงความเจ๋งของ Power Query จะได้นึกภาพออกว่ามันดีกว่าวิธีการเดิมๆ ยังไง? ดังนั้นให้คุณลองทำตามทีละ Step โดยที่ ณ ตอนนี้ยังไม่ต้องเข้าใจว่ามันทำงานยังไงก็ได้นะครับ เพราะในบทต่อๆ ไปจะมีการอธิบายการทำงานแต่ละอันโดยละเอียดให้แน่นอน
สมมติว่าเรามีข้อมูลดังนี้ใน Excel ซึ่งเป็นข้อมูลที่ยังไม่ได้อยู่ในรูปแบบ Database ที่เหมาะสม โดยมีปัญหา 2 เรื่องคือ สินค้าที่ Blank ไว้ และคอลัมน์ sales ก-ง ไม่ได้อยู่ในคอลัมน์เดียวกัน (โหลดไฟล์ประกอบได้ที่นี่)
ให้เราคลิ๊กตรงไหนก็ได้ในตาราง เช่น A3 แล้วไปที่ Data → From Table/Range (บางคนอาจจะเห็นเป็น From Table เฉยๆ) แล้วกด Ok แล้วมันจะเปิดหน้าต่าง Power Query Editor ขึ้นมา
จะเห็นว่าข้อมูลในคอลัมน์สินค้าช่องที่ว่างๆ อยู่จะขึ้นว่า null ซึ่งเราไม่ต้องการให้มันว่าง ให้เราเลือกคอลัมน์สินค้าแล้วไปที่ Transform → Fill → Down จะพบว่า Power Query จะถมช่องว่างได้อย่างง่ายดาย!
ปัญหาต่อไปคือ คอลัมน์ sales ก ถึง sales ง ควรจะมาอยู่ในคอลัมน์เดียวกันมากกว่าที่จะแยกไปเป็นหลายๆ คอลัมน์ ดังนั้นให้เราเลือกคอลัมน์ sales ก – ง โดยคลิ๊กที่คอลัมน์ sales ก ก่อนแล้วกด Shift ค้างแล้วกดคอลัมน์ sales ง
จากนั้นไปที่ Transform → Unpivot Columns จะได้ผลลัพธ์ที่เราต้องการทันที (ง่ายอะไรอย่างงี้!!)
หากเราต้องการเปลี่ยนชื่อคอลัมน์ ก็ให้ Double Click ที่หัวตารางแล้วพิมพ์ชื่อคอลัมน์ที่ต้องการได้เลย เช่น ผมเปลี่ยนคำว่า Attribute เป็น ผู้ขาย และเป็น Value เป็นจำนวนชิ้น
ถ้าเรา ok กับผลลัพธ์แล้วให้ไปที่ Home → Close & Load มันจะแสดงผลลัพธ์ออกมาเป็น Table อีกอันนึงใน Sheet ใหม่ ซึ่ง Table ผลลัพธ์ปกติจะเป็นสีเขียว (ไม่ใช่ Table เดิมนะ Table เดิมปกติจะเป็นสีฟ้า)
ทีนี้หากเราไปแก้ไขข้อมูลใน Table ต้นฉบับเดิม (สีฟ้า) เช่น เพิ่มเสื้อผ้าแบบเงินสดเข้าไปในบรรทัดสุดท้าย ให้เฉพาะ sales ก 50 ชิ้น กับ sales ค 60 ชิ้น
ทีนี้เราไปที่ Table ผลลัพธ์แล้วกดคลิ๊กขวา → Refresh จะเห็นว่ามีข้อมูลใหม่มาทันที (เจ๋งป่ะล่ะ!)
และนี่คือความสามารถของ Power Query ซึ่งหากใช้วิธีอื่นจะยากกว่ากันมากๆ ครับ ในบทถัดๆ ไปเราจะเริ่มลงรายละเอียดเพื่อทำความเข้าใจว่ามันทำอะไรได้บ้าง ซึ่งในบทหลังๆ เราจะรวมข้อมูลทั้ง Folder มาทำรายงานรวมกันได้เลยด้วยซ้ำ ถ้าอยากรู้ก็อ่านต่อไปได้เลยครับ!
บทความนี้มีที่มายังไง?
บทความนี้เป็นส่วนหนึ่งของตัวอย่างจาก หนังสือ 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 นี้ครับ
