---
title: วิธีใช้ Power Query ดึงข้อมูลจากไฟล์ Excel เดียวกัน โดยไม่ต้องแปลงเป็น Table ก่อน
url: https://www.thepexcel.com/power-query-normal-range/
type: post
date: 2020-01-03
updated: 2020-03-30
author: Sira Ekabut
categories: [Power Query]
tags: [OFFSET]
---

# วิธีใช้ 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

 

ก่อนอื่นให้โหลด[ไฟล์ประกอบอันนี้ก่อน](https://drive.google.com/open?id=1EJNJc6hQLOi28TH_6eodLvl-aDg_uRVU)

 

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

 

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

 

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

 ![1](https://www.thepexcel.com/wp-content/uploads/2020/01/GetCurrentWorkbook-00-1024x532.png) 

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

 

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

 ![2](https://www.thepexcel.com/wp-content/uploads/2020/01/GetCurrentWorkbook-10-1024x425.png) 

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

 ![3](https://www.thepexcel.com/wp-content/uploads/2020/01/GetCurrentWorkbook-11.png) 

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

 ![4](https://www.thepexcel.com/wp-content/uploads/2020/01/GetCurrentWorkbook-01.png) 

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

 

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

 ![5](https://www.thepexcel.com/wp-content/uploads/2020/01/GetCurrentWorkbook-02.png) 

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

 

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

 ![6](https://www.thepexcel.com/wp-content/uploads/2020/01/GetCurrentWorkbook-03-1024x230.png) 

**ขั้นตอนที่ 3**

 

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

 ![7](https://www.thepexcel.com/wp-content/uploads/2020/01/GetCurrentWorkbook-04.png) 

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

 ![8](https://www.thepexcel.com/wp-content/uploads/2020/01/GetCurrentWorkbook-05.png) 

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

 

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

 ![9](https://www.thepexcel.com/wp-content/uploads/2020/01/GetCurrentWorkbook-06.png) 

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

 ![10](https://www.thepexcel.com/wp-content/uploads/2020/01/GetCurrentWorkbook-05-1.png) 

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

 

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

 

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

 

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

 

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

 ![11](https://www.thepexcel.com/wp-content/uploads/2020/01/GetCurrentWorkbook-07-1024x206.png) 

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

 ![12](https://www.thepexcel.com/wp-content/uploads/2020/01/GetCurrentWorkbook-08-1024x439.png) 

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

 ![13](https://www.thepexcel.com/wp-content/uploads/2020/01/GetCurrentWorkbook-09.png) 

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

 

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

---

_Source: [https://www.thepexcel.com/power-query-normal-range/](https://www.thepexcel.com/power-query-normal-range/)_