วิธีใช้ Power Query ดึงข้อมูลจากไฟล์ Excel เดียวกัน โดยไม่ต้องแปลงเป็น Table ก่อน

ตามปกติแล้ว เวลาเราใช้ Power Query เพื่อGet Data จากไฟล์ Excel เดียวกันกับตัว Power Query ที่กำลังสร้าง เราจะต้อง Get Data from Table/Range เท่านั้น ซึ่งหากใช้วิธีนี้ Excel จะบังคับให้แปลงข้อมูลเป็น Table ก่อนเสมอ และนั่นก็เป็นวิธีมาตรฐานที่ทาง Microsoft ออกแบบเอาไว้

แต่ในหลายๆ สถานการณ์ เราอาจจะไม่ได้อยากแปลงข้อมูลเป็น Table ก็ได้ (ไม่ว่าด้วยเหตุผลอะไรก็ตาม)

วิธีแก้ปัญหาสำหรับสถานการณ์นี้จริงๆ มีอยู่ 2 วิธี คือ

1. ใช้ Get Data From Excel Workbook แล้วมา Browse หาไฟล์ปัจจุบันที่ทำงานอยู่ วิธีนี้ทำง่าย แต่ผมไม่แนะนำ เพราะว่าวิธีนี้มีข้อเสียสำคัญคือ ต้อง save ไฟล์ Excel ก่อน Power Query จึงจะสามารถ Refresh ข้อมูลล่าสุดมาได้
2. ตั้งชื่อ (Define Name) ให้กับ Range ที่ต้องการเพื่อสร้าง Named Range ก่อน

ซึ่งเดี๋ยวผมจะแสดงวิธีทำของแบบที่ 2 โดยละเอียดให้ดูครับ

อ่อ! ขอบอกก่อนว่าบทความนี้อาจจะเหมาะกับคนที่อ่านหนังสือ Excel Power Up! หรือเคยใช้ Power Query มาบ้างแล้วนะครับ ไม่งั้นอาจจะไม่เข้าใจว่าทำไมถึงต้องมาทำอะไรยุ่งยากแบบนี้ด้วย

วิธีเอาข้อมูลจาก Name เข้า Power Query

ก่อนอื่นให้โหลดไฟล์ประกอบอันนี้ก่อน

ในไฟล์จะมีข้อมูล 2 sheet คือ NormalData (เป็นข้อมูลปกติ) กับ Table-ForPivot (เป็น Table)

เดี๋ยวสิ่งที่เราพยายามจะทำคือ เอาข้อมูลในชีท NormalData นี้เข้าสู่ Power Query โดยไม่แปลงเป็น Table ก่อน

ขั้นตอน 1 : ตั้งชื่อให้กับข้อมูลก่อน โดยเลือกข้อมูลที่เกี่ยวข้องแล้วตั้งชื่อซะ ในที่นี้ผมตั้งชื่อว่า MyDataName

ขั้นตอน 2 : สามารถทำได้ 2 วิธี

ขั้นตอน 2 วิธีที่ 1 : หลังจากตั้งชื่อให้ข้อมูลแล้ว จากนั้นเลือกที่ข้อมูลแล้วกด Get Data From Table/Range ได้เลย โดยหากตั้งชื่อแบบเป็นพื้นที่เอาไว้แล้ว มันจะไม่บังคับให้สร้าง Table ก่อน (วิธีนี้คุณ Bo แห่ง Excel Wizard บอกมา) ซึ่งวิธีนี้ดีตรงใช้ง่ายมาก

จะเห็นว่าข้อมูลของเราเข้าไปสู่ Power Query เรียบร้อยแล้ว โดยไม่ต้องทำเป็น Table ก่อน

ขั้นตอน 2 วิธีที่ 2 : สร้าง Blank Query ดังนี้ (ถ้าใช้เครื่องมือนี้บ่อย ก็อย่าลืมกด Add to Quick Access Toolbar ไว้ล่ะ) วิธีนี้จะดีตรงที่ว่ามัน List Object ทุกอย่างในไฟล์ Excel ให้เลย เผื่อต้องการจะรวมข้อมูลจากหลายๆ ชื่อ หรือหลายๆ Table เข้าด้วยกันก็ทำได้ง่าย

จากนั้นพิมพ์ใน Formula Bar ของ Power Query Editor ว่า =Excel.CurrentWorkbook() แล้วกด Enter

จะเห็นว่ามีทั้งข้อมูลที่เป็น Table จริงๆ และข้อมูลที่มาจาก Name ด้วย ซึ่งมันเข้าทางเราล่ะ!! เพราะเราจะใช้ Name นี่แหละในการอ้างอิงข้อมูลเข้า Power Query

Tips : ถ้ายังไม่มี Formula Bar ให้ไปกดติ๊กที่ View->Formula Bar ก่อน

จากนั้นคลิ๊กคำว่า Table สีเขียวๆ ที่ตรงกับ Name ที่ต้องการ คือ MyDataName เราก็จะได้ Table นั้นออกมาใน Power Query แล้วล่ะ

ขั้นตอนที่ 3

หลังจากแก้ไขข้อมูลตามต้องการ ก็สามารถ Load ออกมาเป็น Table ผลลัพธ์ โดยกด Close & Load จะเห็นว่าผลลัพธ์ออกมาได้แล้ว

ทีนี้เราลองไปแก้ข้อมูลต้นฉบับ ให้แอปเปิ้ลขายได้ 500 ชิ้น แล้วค่อยไปคลิ๊กขวา ที่ตารางผลลัพธ์ แล้วกด Refresh จะเห็นว่าผลลัพธ์เปลี่ยนตามทันที! (แม้ไม่ได้ save ไฟล์)

ปัญหาสำคัญที่ควรจะต้องจัดการ

ทีนี้จะเหลือปัญหาสำคัญอย่างนึงคือ ตอนที่เราตั้งชื่อ MyDataName เนี่ย เราสร้างด้วยวิธีปกติ ทำให้หลังจากเราไปเพิ่มข้อมูลในต้นฉบับภายหลัง เจ้า MyDataName นี้มันจะไม่รู้จักข้อมูลนั้น เช่น ผมไปเพิ่มมังคุดอีก 1 บรรทัด

จะเห็นว่า แม้จะลอง Refresh ที่ตารางผลลัพธ์ มันก็จะไม่รู้จักมังคุด (เพราะมันอยู่นอกขอบเขต Name ที่ตั้งเอาไว้นั่นเอง)

แก้ปัญหาด้วยการตั้งชื่อแบบ Dynamic Range

แทนที่เราจะตั้งชื่อแบบ fix ตำแหน่งไปเลยว่าเอาที่ =NormalData!$A$1:$C$5 แบบปกติ คราวนี้เราจะตั้งชื่อด้วยสูตรที่มันสามารถขยายพื้นที่ตามข้อมูลได้ ซึ่งเรียกว่าสูตรแบบ Dynamic Range นั่นเอง

ซึ่งสูตรแบบ Dynamic Range นั้นหลักๆ แล้วจะสร้างได้ 2 ฟังก์ชัน คือใช้ INDEX หรือไม่ก็ OFFSET ซึ่งในที่นี้ผมจะใช้ OFFSET ละกัน เพราะดูแล้วน่าจะง่ายสำหรับสถานการณ์นี้มากกว่า (ใครใช้ OFFSET ไม่เป็นอ่านได้ที่นี่)

ก่อนอื่นเราเขียนสูตร OFFSET ให้อ้างอิงจุดเริ่มต้นที่ จุดซ้ายบนของตาราง โดยไม่เลื่อนแถว ไม่เลื่อนคอลัมน์ และให้มีความสูงโดยนับจำนวนข้อมูลในคอลัมน์ A และมีความกว้างโดยนับข้อมูลในแถวที่ 1 โดยที่เรากด F4 ใส่ $ เพื่อ Fix ตำแหน่งให้หมด ดังนั้นสูตรจะเป็นแบบนี้

=OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))

จากนั้นให้เรา Cut สูตรนี้ไป Paste แทน Range เดิมในชื่อ MyDataName (โดยกด Edit Name ใน Formula –> Name Manager) แล้วกด OK

ทีนี้ลองไปที่ตารางผลลัพธ์ แล้วกดคลิ๊กขวา Refresh ดู จะเห็นว่าผลลัพธ์อัปเดทแล้วววว

อย่างไรก็ตาม การสร้าง Dynamic Named Range ไว้ก่อนจะไม่สามารถเอาเข้า Power Query ได้โดยตรงด้วยวิธีที่ 1 (กด Get Data From Table/Range) แต่ยังสามารถทำด้วยวิธีที่ 2 ( สูตร = Excel.CurrentWorkbook() ) ได้อยู่ครับ

และนี่ก็คือเทคนิคที่ผมอยากจะนำเสนอครับ หากใช้เทคนิคนี้จะทลายข้อจำกัดหลายๆ อย่างของการใช้ Power Query ไปได้เยอะเลย ดังนั้น มาใช้ Power Query กันเยอะๆ นะครับ ^^