Thep Excel
IMPORTRANGEดึงข้อมูลข้ามไฟล์ Google Sheets แบบ Real-time
IMPORTRANGE ดึงข้อมูลจาก Google Sheets ไฟล์อื่นมาแสดงแบบ Real-time โดยใช้ URL หรือ Spreadsheet ID ข้อมูลจะ sync ตามต้นฉบับอัตโนมัติทุก 30 นาที (หรือเร็วกว่า) ต้อง Allow Access ครั้งแรกที่เชื่อมต่อระหว่างไฟล์คู่ใดๆ หลังจากนั้น Editor ทุกคนในไฟล์ปลายทางสามารถใช้ IMPORTRANGE จากไฟล์ต้นทางนั้นได้เลย ข้อมูลที่ดึงมาเป็น Read-only เหมาะสำหรับการรวมศูนย์ข้อมูล สร้าง Dashboard และแชร์ข้อมูลบางส่วนโดยไม่ต้องเปิดเผยไฟล์ต้นฉบับทั้งหมด
Syntax & Arguments
| อาร์กิวเมนต์ | ชนิด | คำอธิบาย |
|---|---|---|
| spreadsheet_url | Text | URL หรือ Spreadsheet ID ของไฟล์ต้นทาง (ต้องอยู่ในเครื่องหมายคำพูด)
รูปแบบ URL: https://docs.google.com/spreadsheets/d/[SPREADSHEET_ID]/edit สามารถใส่ได้ทั้ง: |
| range_string | Text | ชื่อ Sheet และช่วงข้อมูลที่ต้องการดึง (ต้องอยู่ในเครื่องหมายคำพูด)
รูปแบบ: “SheetName!Range” หรือ “Range” (ถ้าไม่ระบุชื่อ Sheet จะดึงจาก Sheet แรก) ตัวอย่าง: |
Additional Notes
IMPORTRANGE เป็นฟังก์ชันที่มีเฉพาะใน Google Sheets ใช้สำหรับดึงข้อมูลจาก Spreadsheet ไฟล์อื่นมาแสดงในไฟล์ปัจจุบันแบบ Real-time โดยข้อมูลจะ sync ตามต้นฉบับอัตโนมัติ
.
ที่เจ๋งคือคุณสามารถรวมข้อมูลจากหลายไฟล์มาไว้ที่เดียวได้ โดยไม่ต้องคอย Copy-Paste เองอีกต่อไป เช่น ดึงยอดขายจากไฟล์สาขา 10 แห่งมารวมกันในไฟล์สำนักงานใหญ่ ข้อมูลจะอัปเดตตลอดเวลา
.
ส่วนตัวผมใช้ IMPORTRANGE เป็น backbone หลักของทุก Dashboard เลยครับ เพราะมันทำให้แยกการจัดการข้อมูลกับการ visualize ออกจากกันได้ ทีมกรอกข้อมูลที่ไฟล์หนึ่ง ผมสร้างรายงานที่อีกไฟล์ ไม่มีใครมายุ่งกับสูตรของกันและกัน 😎
How it works
รวมศูนย์ข้อมูลจากหลายสาขา
ดึงยอดขายจากไฟล์ของแต่ละสาขา (กรุงเทพ, เชียงใหม่, ขอนแก่น, ภูเก็ต) มารวมไว้ในไฟล์ HQ เพื่อสร้าง Dashboard รวม ข้อมูลจะอัปเดตอัตโนมัติเมื่อสาขาแก้ไขไฟล์ของตัวเอง
แชร์ข้อมูลบางส่วนอย่างปลอดภัย
ดึงเฉพาะคอลัมน์ที่ไม่ sensitive (ชื่อลูกค้า, สถานะ) ไปแสดงในไฟล์ใหม่ที่แชร์ให้ทีมอื่น โดยไม่ต้องเปิดเผยข้อมูล sensitive ในไฟล์ต้นฉบับ (เช่น รายได้, เบอร์โทรศัพท์)
สร้าง Dashboard แยกจากข้อมูล Raw
แยกไฟล์ข้อมูล Raw (ที่ทีมกรอกข้อมูล) ออกจากไฟล์ Dashboard (ที่มีสูตรซับซ้อน) เพื่อป้องกันทีมไปแก้สูตรผิด และให้ Dashboard ทำงานได้เร็วขึ้น
Backup ข้อมูลอัตโนมัติ
ใช้ IMPORTRANGE ดึงข้อมูลทั้งหมดไปเก็บไว้อีกไฟล์หนึ่งเป็นการ Backup แบบ Real-time ถ้าไฟล์ต้นฉบับมีปัญหา ยังมีสำเนาล่าสุดอยู่
Examples
ตัวอย่างที่ 1: ดึงข้อมูลพื้นฐานด้วย URL เต็มIMPORTRANGE("https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit", "Sheet1!A1:C10")
สูตรนี้ดึงข้อมูลจาก Sheet1 ช่วง A1:C10 ของไฟล์ต้นทาง มาแสดงในไฟล์ปัจจุบัน
**Permission ครั้งแรก**: ครั้งแรกที่ใช้จะขึ้น #REF! error พร้อมปุ่ม "Allow access" ให้กดอนุญาต หลังจากนั้นข้อมูลจะแสดงออกมาและ sync อัตโนมัติ
ข้อควรระวัง: ผลลัพธ์เป็น Array ที่ขยายออกไปหลาย cell ถ้ามีข้อมูลขวางอยู่จะขึ้น #REF! ครับ
ตัวอย่างที่ 2: ใช้ Spreadsheet ID แทน URL เต็ม (สูตรสั้นลง)IMPORTRANGE("1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms", "Sales!A:D")
แทนที่จะใช้ URL เต็ม สามารถใช้เฉพาะ Spreadsheet ID ได้ (ส่วนที่อยู่หลัง /d/ และก่อน /edit ใน URL)
ใช้ A:D แทน A1:D100 เพื่อดึงทุกแถวของคอลัมน์นั้น ถ้ามีการเพิ่มข้อมูลใหม่ที่ไฟล์ต้นทาง จะดึงมาด้วยอัตโนมัติ
**Permission**: ต้องกด Allow access ครั้งแรกเหมือนตัวอย่างที่ 1 หลังจากนั้น Editor ทุกคนในไฟล์นี้สามารถใช้ IMPORTRANGE จากไฟล์ต้นทางเดียวกันได้เลย
ส่วนตัวผมชอบใช้ ID มากกว่า URL เต็ม เพราะสูตรสั้นและอ่านง่ายกว่าครับ
ตัวอย่างที่ 3: รวมกับ QUERY เพื่อกรองและจัดการข้อมูลQUERY(IMPORTRANGE("1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms", "Orders!A:E"), "SELECT Col1, Col2, Col5 WHERE Col3 = 'Completed' AND Col5 > 1000 ORDER BY Col5…
ใช้ QUERY กับ IMPORTRANGE เพื่อกรองและจัดการข้อมูลก่อนแสดงผล
**QUERY SQL-like Syntax**:
– SELECT Col1, Col2, Col5 = เลือกคอลัมน์ที่ต้องการ (ใช้ Col1, Col2… แทน A, B เพราะข้อมูลมาจาก Array ไม่ใช่ Range โดยตรง)
– WHERE Col3 = 'Completed' AND Col5 > 1000 = เงื่อนไขการกรอง (เหมือน SQL)
– ORDER BY Col5 DESC = เรียงลำดับจากมากไปน้อย
สมมติข้อมูลต้นทาง: Col1=OrderID, Col2=Customer, Col3=Status, Col4=Date, Col5=Amount
**Permission**: ต้อง Allow access ก่อนใช้ครั้งแรก หลังจากนั้นสูตรจะ sync ข้อมูลอัตโนมัติ
ที่เจ๋งคือคุณดึงมาเฉพาะที่ต้องการได้ ไม่ต้องเอาทั้งหมดมาแล้วค่อยกรอง ประหยัด bandwidth ครับ
ตัวอย่างที่ 4: รวมข้อมูลจากหลายไฟล์ด้วย {} (Curly Braces)QUERY( { IMPORTRANGE("ID_BKK", "Sales!A2:D"); IMPORTRANGE("ID_CNX", "Sales!A2:D"); IMPORTRANGE("ID_KKC", "Sales!A2:D") }, "SELECT Col1, Col2, Col3, SUM(Col4) GR…
สูตรนี้รวมข้อมูลจาก 3 ไฟล์เข้าด้วยกันด้วย {} (ใช้ ; คั่นเพื่อรวมแนวตั้ง)
สมมติ: ID_BKK, ID_CNX, ID_KKC คือ Spreadsheet ID ของไฟล์สาขากรุงเทพ, เชียงใหม่, ขอนแก่น ตามลำดับ
Col1=Product, Col2=Region, Col3=Month, Col4=SalesAmount
**QUERY SQL-like Syntax**:
– SELECT Col1, Col2, Col3, SUM(Col4) = เลือกคอลัมน์และรวมยอด
– GROUP BY Col1, Col2, Col3 = จัดกลุ่มตาม Product, Region, Month
– LABEL SUM(Col4) 'Total Sales' = ตั้งชื่อ Header ให้คอลัมน์ผลรวม
**Permission สำคัญ**:
1. ทุกไฟล์ต้อง Allow Access ก่อน (กด Allow ทีละไฟล์)
2. จำนวนคอลัมน์ต้องเท่ากันทุกไฟล์
3. ใช้ A2:D (เริ่มจากแถว 2) เพื่อข้าม Header ป้องกัน Header ซ้ำกัน
4. ใส่ 0 เป็น argument สุดท้ายของ QUERY เพราะข้อมูลไม่มี Header แล้ว
ส่วนตัวผมใช้ท่านี้ทำ Master Dashboard ที่รวมข้อมูลจากทุกสาขาบ่อยมากครับ
FAQs
ขึ้น #REF! error พร้อมข้อความ "You don't have permissions" ต้องทำอย่างไร?+
ปัญหานี้เกิดจาก 2 กรณี:
1. ยังไม่ได้ Allow Access (ครั้งแรก)
– เอาเมาส์ไปชี้ที่ cell ที่ error
– จะมีปุ่ม "Allow access" ขึ้นมา ให้กด
– หลังจากนั้นข้อมูลจะแสดงออกมา
2. ไม่มีสิทธิ์เข้าถึงไฟล์ต้นทาง
– ต้องขอให้เจ้าของไฟล์ต้นทาง Share ให้ก่อน (อย่างน้อย View access)
– หรือเปลี่ยนการ Share ของไฟล์ต้นทางเป็น "Anyone with the link can view"
เมื่อ Allow Access แล้ว Editor ทุกคนในไฟล์ปลายทางสามารถใช้ IMPORTRANGE จากไฟล์ต้นทางนั้นได้ทันที ไม่ต้อง Allow ใหม่ครับ 😅
IMPORTRANGE โหลดช้ามาก หรือค้างที่ "Loading…" ตลอด แก้ยังไง?+
สาเหตุและวิธีแก้:
1. ข้อมูลมีขนาดใหญ่เกินไป
– Google จำกัด 10MB ต่อการดึงข้อมูล 1 ครั้ง
– แก้โดยแบ่งช่วงข้อมูลออกเป็นหลาย IMPORTRANGE
– หรือใช้ QUERY กรองเฉพาะที่ต้องการก่อน
2. ไฟล์ต้นทางมีสูตรซับซ้อน
– IMPORTRANGE รอให้ไฟล์ต้นทางคำนวณเสร็จก่อนจึงดึงข้อมูล
– ถ้าไฟล์ต้นทางช้า ปลายทางก็ช้าตาม
3. ใช้ IMPORTRANGE ซ้อนกันหลายชั้น (Chain)
– หลีกเลี่ยงการ IMPORTRANGE จากไฟล์ที่ IMPORTRANGE มาอีกที
– แต่ละ chain เพิ่ม delay ขึ้นไปเรื่อยๆ
4. Internet ช้า
– IMPORTRANGE ต้องใช้ Internet ทุกครั้งที่ refresh
– ลองเปลี่ยน Network หรือรอสักครู่ครับ 💡
ข้อมูลที่ดึงมาแก้ไขได้ไหม? ถ้าแก้จะกลับไปที่ไฟล์ต้นทางด้วยไหม?+
ไม่ได้ครับ ข้อมูลที่ดึงมาเป็น Read-only
– cell ที่แสดงผลจาก IMPORTRANGE จะแก้ไขไม่ได้
– ถ้าต้องการแก้ไขข้อมูล ต้องไปแก้ที่ไฟล์ต้นทางโดยตรง
– การแก้ไขที่ไฟล์ต้นทางจะ sync มาที่ปลายทางอัตโนมัติ (ภายใน 30 นาที หรือเร็วกว่า)
ข้อดีคือไม่ต้องกลัวว่าคนดู Dashboard จะไปแก้ข้อมูลผิดพลาดครับ 😎
ขึ้น "Formula parse error" คืออะไร?+
หมายความว่า Syntax ของสูตรผิด สาเหตุที่พบบ่อย:
1. ลืมใส่ Quotation Marks
❌ =IMPORTRANGE(1BxiMVs0XRA…, Sheet1!A:C)
✅ =IMPORTRANGE("1BxiMVs0XRA…", "Sheet1!A:C")
2. URL หรือ ID ผิด
– ตรวจสอบว่า Copy URL มาถูกต้อง
– ถ้าใช้ ID ตรวจสอบว่าได้ส่วนที่ถูกต้อง (หลัง /d/ และก่อน /edit)
3. ชื่อ Sheet มี space หรืออักขระพิเศษ
– ชื่อ Sheet ที่มี space ต้องใส่ใน range_string ด้วย
– ตัวอย่าง: "'Sales Data'!A:C" (ใช้ single quote ครอบชื่อ Sheet)
4. Range ผิดรูปแบบ
– ✅ "Sheet1!A1:C10" หรือ "A:C"
– ❌ "Sheet1!A1-C10" (ใช้ – แทน 🙂
IMPORTRANGE รองรับ Named Range ไหม?+
รองรับครับ! สามารถใช้ชื่อ Named Range แทนการระบุ Range โดยตรงได้เลย
=IMPORTRANGE("1BxiMVs0XRA…", "SalesData")
สมมติว่าในไฟล์ต้นทางมี Named Range ชื่อ "SalesData" ที่ชี้ไปที่ Sales!A1:D100 สูตรนี้จะดึงข้อมูลจาก Range นั้นมาให้
ข้อดีคือถ้าไฟล์ต้นทางย้าย Range หรือเปลี่ยนขนาด แค่แก้ Named Range ที่ไฟล์ต้นทาง ไม่ต้องแก้สูตรที่ไฟล์ปลายทางเลยครับ 💡
Resources & Related
ฟังก์ชันที่ผู้เขียนโยงไว้กับ IMPORTRANGE จัดกลุ่มตามหมวด · ชี้ที่ชื่อใดชื่อหนึ่ง แล้วฟังก์ชันที่ใช้คู่กันจะมีจุดทอง
ARRAYFORMULA กระจายผลลัพธ์จากสูตรเดียวออกเป็นหลายแถวหรือหลายคอลัมน์พร้อมกัน โดยไม่ต้องลากสูตร ช่วยประหยัดเวลา ลดข้อผิดพลาด และอัปเดตอัตโนมัติเมื่อมีข้อมูลใหม่ ทำให้ชีททำงานเร็วขึ้นเพราะลดจำนวนสูตรจากหลายพันเหลือแค่หนึ่ง
GOOGLEFINANCE ช่วยดึงข้อมูลหุ้น กองทุน และค่าเงินจาก Google Finance มาแสดงในตารางได้ทันที รองรับทั้งราคาปัจจุบัน (Real-time, ดีเลย์ 20 นาที) และราคาย้อนหลัง (Historical Data) สามารถดึงค่าอื่นๆ เช่น P/E, Market Cap หรือปริมาณการซื้อขายได้ด้วย
QUERY ใช้ภาษา SQL-like (Google Visualization API Query Language) ในการจัดการข้อมูลจาก Range ที่กำหนด รองรับ 9 clauses ตามลำดับ: SELECT, WHERE, GROUP BY, PIVOT, ORDER BY, LIMIT, OFFSET, LABEL, FORMAT พร้อม Aggregate Functions (SUM, COUNT, AVG, MIN, MAX) และ Scalar Functions สำหรับวันที่และข้อความ ทำให้สามารถกรอง จัดเรียง และสรุปข้อมูลได้แบบ Dynamic ด้วยสูตรเดียว เหมาะสำหรับการสร้างรายงานและ Dashboard ที่ซับซ้อน
ยังไม่มีบทความที่เกี่ยวข้องกับฟังก์ชันนี้
Comments
อีเมลของคุณจะไม่ถูกเผยแพร่