บทที่ 18 : การดึงข้อมูลจากทุก File ที่ต้องการใน Folder 1

บทที่ 18 : การดึงข้อมูลจากทุก File ที่ต้องการใน Folder

การดึงข้อมูลจากทั้ง Folder มาทีเดียวพร้อมกันได้นี่ ผมคิดว่ามันเป็นสุดยอดแห่งการ Get Data ที่หลายคนปรารถนาเลยล่ะ  ลองคิดดูว่าหากเราต้องทำ Regular Report ทุกอาทิตย์หรือทุกเดือนแล้วเราสามารถโยนข้อมูลเดือนใหม่ที่ได้ไฟล์มาจากฝ่ายงานต่างๆ เข้าไปใน Folder ที่เราออกแบบไว้แล้ว หลังจากนั้นกด Refresh แล้วรายงานทุกอย่างก็ถูก Power Query + Pivot Table ปั่นออกมาจนเสร็จได้เลยมันจะสุดยอดขนาดไหน!! ประเด็นที่น่าสนใจในการดึงข้อมูลจาก Folder มีอยู่ 3 เรื่องหลักๆ เลยคือ  มีไฟล์อื่นปนมาหรือไม่ ? : ถ้ามีโอกาสที่จะมีไฟล์อื่นปนมา ก็ควรจะมีการ Filter เลือกเอาเฉพาะสิ่งที่ต้องการเท่านั้น มีข้อมูลกี่รูปแบบ? : ถ้าข้อมูลมีอยู่หลายรูปแบบ (Pattern) ให้เลือกมาทำทีละรูปแบบ แล้วสุดท้ายค่อยเอามารวมกันหรือเชื่อมกันทีหลังต้องการรวมไฟล์แบบ Auto หรือทำแบบสร้าง Custom Column? : แบบ Auto จะง่ายกว่า แต่แบบ…
power query กำจัด

บทที่ 7 : การกำจัดข้อมูลที่ไม่ต้องการ

กำจัดค่าว่างและค่า Error ในบางครั้งข้อมูลในตารางที่เรามี มันก็ไม่ได้ดีขนาดที่จะเอาไปใช้ได้ทันที แต่ว่าต้องจัดการบางอย่างก่อน ดังนั้นความรู้ในส่วนนี้จึงเป็นเรื่องที่สำคัญมากครับ ยกตัวอย่างเช่น สมมติผม Get Data ข้อมูลจาก Excel แบบนี้ ซึ่งมีข้อมูลหลากหลายรูปแบบ (โหลดไฟล์ประกอบได้ที่นี่) ใน Power Query ก็จะตีความออกมาเป็นแบบนี้ จุดที่น่าสังเกต ช่องที่เป็นค่าว่างจริงๆ จะขึ้นเป็น nullช่องที่เป็น empty text (มาจากสูตร ="") จะกลายเป็นช่องว่างๆ ที่ไม่มีคำว่า null ซึ่งหากลองกด Filter ดูจะเห็นเป็นคำว่า (blank)ข้อมูล Error ใน Excel ไม่ว่าจะเป็นแบบใดก็ถูกตีความเป็น Error ทั้งหมด การจัดการข้อมูล Error ถ้าข้อมูลยังมี Error อยู่แบบนี้ จะ Transform ต่อแล้วเกิดปัญหา เช่น จะ…
บทที่ 18 : การดึงข้อมูลจากทุก File ที่ต้องการใน Folder 2

บทที่ 6 : การคำนวณเบื้องต้น

การสรุปข้อมูลโดยใช้เครื่องมือ Statistics คำสั่ง Statistics ต่างๆ เช่น Sum, Minimum, Count Rows อะไรพวกนี้มันทำงานได้ 2 แบบ คือ หากเลือกคอลัมน์เดียวแล้วกดแบบ Transform จะให้ผลลัพธ์สรุปข้อมูลจากคอลัมน์ที่กำหนดออกมาเป็นตัวเลขค่าเดียวเลยหากเลือกหลายคอลัมน์ก่อนแล้วกดแบบ Add Column ก็จะเป็นการคำนวณสำหรับข้อมูลในแต่ละแถวแยกกันออกมาเป็นคอลัมน์ใหม่ โหลดไฟล์ตัวอย่างได้ที่นี่ => คลิ๊ก ตัวอย่าง กรณีที่ใช้ Statistics → Minimum สมมติผมมีข้อมูลแบบนี้ หากเลือกคอลัมน์ราคาในห้าง → Transform → Statistics → Minimum จะได้ค่าที่น้อยสุดในคอลัมน์นั้นออกมาเป็นค่าเดียว หากเลือกคอลัมน์ราคาในตลาด และราคาในห้างพร้อมกัน → Add Column  → Statistics → Minimum มันจะหาว่าค่าที่น้อยสุดในแต่และแถวเป็นค่าอะไร? ซึ่งทั้ง 2 วิธีมีประโยชน์ทั้งคู่…
บทที่ 18 : การดึงข้อมูลจากทุก File ที่ต้องการใน Folder 3

บทที่ 5 : การจัดการหัวตาราง ใน Power Query

เรื่องพื้นฐานที่ควรจะรู้เลยคือ การจัดการหัวตาราง ไม่ว่างจะเป็นการ Promote บรรทัดแรกให้กลายเป็นหัวตาราง หรือการปรับหัวตารางให้กลับมาเป็น Data บรรทัดแรก รวมถึงการจัดการบรรทัดข้อมูลที่ไม่ต้องการออกไปก่อน กรณีหัวตารางไม่ได้อยู่บรรทัดบนสุด  บทนี้ให้ Get Data → From Table/Range จากไฟล์ตัวอย่าง (โหลดที่นี่) เพื่อเอาข้อมูลเข้าไปใน Power Query แต่คราวนี้เราไม่ต้องติ๊ก My table has headers เนื่องจากเพราะบรรทัดแรกมันไม่ใช่หัวตาราง จะได้ Query หน้าตาแบบนี้ ซึ่งจะเห็นว่าหัวตารางจะชื่อ Column1 Column2… ซึ่งไม่ใช่ชื่อหัวตารางที่ควรจะเป็น สิ่งที่เราควรทำคือ พยายามทำให้ชื่อหัวตารางที่แท้จริงมาอยู่ที่บรรทัดแรกให้ได้ ซึ่งทำได้ 2 วิธี แล้วแต่ความเหมาะสม คือ เลือก Home→ Remove Rows→ Remove Top Rows แล้วใส่เลข 2…
บทที่ 18 : การดึงข้อมูลจากทุก File ที่ต้องการใน Folder 4

บทที่ 4 : งานที่ยุ่งยากใน Excel กลับง่ายมากใน Power Query

Power Query นั้นมีจุดแข็งอย่างมากเรื่องความสามารถในการดัดแปลงข้อมูลให้อยู่ในรูปแบบที่เหมาะสม โดยมีเครื่องมือมากมายที่จะช่วยเราในเรื่องนั้นได้ เรามาดูกันรวมๆ ก่อนว่ามันมีเครื่องมืออะไรที่น่าสนใจบ้าง ซึ่งผมคัดเลือกเครื่องมือที่ผมคิดว่าเจ๋งๆ มาให้บางส่วนดังนี้ ลองอ่านดูคร่าวๆ ก่อน จะได้เห็นภาพว่า Power Query นั้นทำอะไรได้แบบที่กดด้วยเครื่องมือได้เลยบ้าง สำหรับบางตัวที่มีความซับซ้อนมากๆ ผมจะมีการอธิบายโดยละเอียดอีกทีนึงด้วยครับ เครื่องมือจัดการโครงสร้างหัวตาราง ชื่อเครื่องมือใน Power Queryความสามารถคล้ายอะไรใน Excel?Use First Row as Headersทำให้แถวแรกสุดเป็นหัวตารางInsert Table แล้วติ๊กหัวตารางUse Headers as First Rowทำให้หัวตารางมาเป็นแถวแรกConvert Table to RangeChange Data Typeเปลี่ยนประเภทข้อมูล=A1*1 แปลงเป็นเลข=A1&"" แปลงเป็น Text จัดการแถว ชื่อเครื่องมือใน Power Queryความสามารถคล้ายอะไรใน Excel?Remove Rows → Remove Top/Bottom Rowsลบแถวบนสุด/ล่างสุด xxx…
บทที่ 18 : การดึงข้อมูลจากทุก File ที่ต้องการใน Folder 5

บทที่ 3 : ภาพรวมการทำงานกับ Power Query

Power Query นั้นมีขั้นตอนการทำงานหลักๆ อยู่ 3 ขั้น คือ Get Data : เอาข้อมูลจากไหน?Transform Data : ดัดแปลงข้อมูลยังไง?Load Data to… : เสร็จแล้วจะเอา Data ไปไว้ไหน? ก่อนอื่นให้โหลดไฟล์ตัวอย่างอันนี้ แล้วเรามาดูรายละเอียดแต่ละขั้นตอนกันครับ Step1 : Get Data เอาไว้เลือกว่าเอาแหล่งข้อมูลจากไหน? Get Data เป็นขั้นตอนแรกที่เราต้องทำ ซึ่งเป็นการเลือกว่าอยากให้ Power Query เอาข้อมูลจากไหน? เราสามารถเลือกข้อมูลได้หลากหลายรูปแบบมากๆ เช่น ข้อมูลจากในไฟล์ Excel เดียวกับที่เปิดอยู่ หรือเอาจากแหล่งอื่น?  ถ้าเอาจากแหล่งอื่นก็จะต้องกดปุ่ม Get Data (หรือ บางคนจะเห็นเป็นปุ่ม New Query) ก็จะดึงข้อมูลเช่น จาก Excel…
บทที่ 18 : การดึงข้อมูลจากทุก File ที่ต้องการใน Folder 6

บทที่ 2 : ก้าวสู่การเตรียมข้อมูลยุคใหม่ด้วย Power Query

บทความนี้เป็นตัวอย่างตอนที่ 3 ของ Series เนื้อหา Power Query ซึ่งเป็นตัวอย่างจากหนังสือ Excel Power Up! (ใครสนใจตอนก่อนหน้านี้ให้ไปดูสารบัญด้านล่างนะครับ) ดูแบบคลิปวีดีโอได้ที่นี่ https://youtu.be/XETSwW33lVQ Power Query คืออะไรกันแน่? Power Query ก็คือ เครื่องมือที่สามารถ “ดึงข้อมูลอย่างทรงพลัง” นั่นคือมีความสามารถในการดึงข้อมูลได้จากแหล่งข้อมูลที่หลากหลายและสามารถปรับเปลี่ยนรูปแบบหน้าตาข้อมูลผลลัพธ์ได้ตามต้องการ  จุดประสงค์หลักเพื่อทำให้ข้อมูลเน่าๆ (ที่อาจจะมาจากหลายแหล่ง หลาย Format หลายหน้าตา) ให้มาอยู่ในรูปแบบที่ Database เพื่อให้สามารถนำไปวิเคราะห์ต่อใน PivotTable, Power Pivot, Power BI ได้ และเมื่อทำเสร็จแล้ว 1 ที หากจะทำซ้ำครั้งถัดไป แค่กด Refresh ทุกอย่างก็จบได้อย่างง่ายดาย Power Query มีจุดเด่นยังไง? รวบรวมข้อมูลได้จากหลายที่ หลายรูปแบบใช้งานง่าย มีปุ่มเมนูให้กด…
บทที่ 18 : การดึงข้อมูลจากทุก File ที่ต้องการใน Folder 7

บทที่ 1 : เข้าใจขั้นตอนการทำรายงานสรุป / วิเคราะห์ข้อมูล

ในเมื่อเราจะมาเจาะลึกวิธีใช้ Excel ทำรายงานให้มีประสิทธิภาพสูงสุด ดังนั้นเราควรวิเคราะห์ดูก่อนว่า การจะทำรายงานให้สำเร็จได้นั้นโดยภาพรวมมีขั้นตอนอะไรและความเชื่องช้าอยู่ตอนไหนบ้าง? Step การทำรายงาน เตรียมฐานข้อมูลคำนวณแปลงข้อมูลดิบให้เป็นผลสรุปVisualizationสรุปและตีความหมายนำข้อมูลไปใช้ประโยชน์จริง ๆทำซ้ำหากข้อมูลมีการเปลี่ยนแปลง เช่น ต้องทำข้อมูลเดือนใหม่ในรูปแบบเดิมอีก เตรียมฐานข้อมูล ขั้นตอนนี้ ความซับซ้อนและความเสียเวลาขึ้นอยู่กับว่าแหล่งข้อมูลที่แท้จริงเรามาจากไหน และมีลักษณะอย่างไร? เช่น ข้อมูลอยู่ในไฟล์ Excel เดียวกับที่ทำงานอยู่ หรือ อยู่ที่อื่น?ข้อมูลมีตารางเดียว หรือ หลายตาราง? เป็นแบบเพิ่มคอลัมน์ หรือ เพิ่มเแถว หรือ ทั้งคู่เลย?การรวมข้อมูลนั้นรวมได้ตรงๆ เลย หรือต้องมีการคัดเลือกข้อมูลบางส่วน ถ้าอยู่ในไฟล์เดียวกัน มีตารางเดียว นี่จะง่ายที่สุด เพราะเราเอา Data ตัวนั้นให้เป็นแหล่งข้อมูลหลักที่จะเอาไปทำต่อได้เลย แต่ถ้าข้อมูลอยู่ที่อื่น หรือมีหลายตาราง เราก็ต้องพยายามรวบรวมข้อมูลทุกที่ให้มาอยู่ในที่เดียวกันก่อน  ซึ่งทำได้หลายแบบ หลายวิธีการ เช่น Copy Paste เอาเองแบบ Manual เขียนสูตรพวก LOOKUP อย่างเช่น VLOOKUP, INDEX, MATCH,…
บทที่ 18 : การดึงข้อมูลจากทุก File ที่ต้องการใน Folder 8

บทนำ : ทำไมต้องเรียนรู้ Power Query?

ขยันแล้วทำไมไม่ได้ดีอย่างที่หวัง? เบื่อมั้ย? ที่คุณต้องทำงานรายงานแบบเดิม ซ้ำๆ เดิม ทุกเดือน หรือ ทุกอาทิตย์... เบื่อมั้ย? ที่คุณต้องคอยขอร้องให้คนอื่นช่วยทำรายงานให้ ซึ่งบางทีก็ไม่ว่าง กว่าจะได้ก็ช้า หรือทำมาผิด… เบื่อมั้ย? ขยันทำงานหนักแทบตาย ทำงานหนัก เลิกงานดึก ไม่มีเวลาดูแลตัวเอง ไม่มีเวลาดูแลคนที่เรารัก  แต่ทำไมไม่ได้โปรโมทซะที…(ฟะ) ที่ผ่านมา ผมได้พบกับ “คนขยัน” มากมาย ที่อาจจะยังขยันไม่ถูกวิธี เช่น ขยันในสิ่งที่ให้คอมพิวเตอร์ทำแทนได้ ซึ่งคอมพิวเตอร์ย่อมทำได้เร็วและแม่นยำกว่ามนุษย์อยู่แล้ว ซึ่งวิธีขยันที่ถูกต้อง คือ ขยันหาความรู้และฝึกทักษะใหม่ๆ ที่จะช่วยให้เราทำงานหรือใช้ชีวิตได้ดีขึ้นต่างหาก จากประสบการณ์ที่ผมที่ได้มีโอกาสไปอบรมบุคลากรบริษัทต่างๆ มามากมาย สาเหตุที่ทำให้หลายคนไม่เก่ง Excel เท่าที่ควร เป็นเพราะมีความอดทนในการทำงานถึกๆ ซ้ำๆ มากเกินไป จนไม่คิดว่าสิ่งที่ทำอยู่มันเป็นปัญหา พอไม่คิดว่าเป็นปัญหาก็จะไม่หาทางพัฒนาหาวิธีที่ดีขึ้นกว่าเดิม ทั้งๆ ที่บางเรื่องแค่ Google นิดเดียวก็รู้แล้ว โดยเฉพาะการใช้ Excel ซึ่งเป็นโปรแกรมที่คนใช้เก่งกับไม่เก่งสร้างผลลัพธ์ที่แตกต่างกันมาก หากนำมาใช้อย่างเหมาะสม…