สรุปทุกอย่างที่ควรรู้เกี่ยวกับการ Link ข้อมูลข้ามไฟล์ Excel

คำถามเกี่ยวกับการ Link ข้อมูลข้ามไฟล์ Excel เป็นคำถามยอดฮิตอีกอันนึงที่คนถามกันมาเยอะมาก แต่ผมก็ยังไม่เคยสรุปเนื้อหาเกี่ยวกับเรื่องนี้ซักที และผมก็คิดว่าถึงเวลาแล้วล่ะที่จะสรุปเนื้อหาเรื่องนี้ให้ทุกคน และมันก็ออกมาเป็นบทความนี้นั่นเองนะครับ

โดยที่ผมจะมีไฟล์อยู่ 2 ไฟล์ คือ Book1 กับ Book2 โดยที่ผมต้องการ Link ข้อมูลจาก Book1 ไปยัง Book2 นะครับ

version วีดีโอ

การ Link ข้อมูลข้ามไฟล์ Excel ผมก็แบ่งออกเป็น 3 วิธีหลักๆ นั่นคือ ใช้สูตร ใช้ VBA และ ใช้ Power Query ครับ

ใช้สูตร

การใช้สูตรแบบที่ Simple ที่สุด ก็คือ การใช้เครื่องหมายเท่ากับ link ไปทีละช่องที่ต้องการเลย คือ

1. ไปที่ช่องปลายทาง
2. พิมพ์เครื่องหมายเท่ากับ
3. แล้วเอา Mouse จิ้มไปที่ช่องต้นทาง (ใช้วิธีเอา Mouse ไปจิ้มข้ามไฟล์เอานะ ไม่ต้องไปพิมพ์พวก [ ] เอง)
4. กด Enter

แต่ถ้า Link แบบนี้ เราจะ Copy Paste ให้คลุมทุกช่องที่ต้องการทันทีไม่ได้ เพราะมัน Lock$ ไว้ทำให้ได้ตัวเดิมตลอด ดังนั้นเราจะต้องกด F4 เพื่อปลด $ ออกก่อนดังนี้ แล้วค่อย Copy Paste

แต่ถ้าเรา Link สูตรแบบ Array (Link ทีเดียวหลายช่อง) ก็ทำได้เช่นกัน ดังนี้

=[Book1.xlsx]Sheet1!$A$1:$B$3

• ถ้าใช้ Excel 365 เราแค่เลือกที่ A1 ของปลายทาง แล้ว =คลุมทุกช่องต้นทาง แล้วกด Enter ได้เลย
• ถ้าใช้ Excel version เก่า ต้องเลือก A1:B3 ของปลายทางไว้ก่อน แล้ว=คลุมทุกช่องต้นทาง แล้วกด Ctrl+Shift+Enter

ถ้าที่ต้นทางข้อมูลบางช่องขาดหายไป มันจะขึ้นเลข 0 มาแทน

เราอาจใช้ IF ดักไว้ก็ได้

=IF([Book1.xlsx]Sheet1!$A$1:$B$3="","",[Book1.xlsx]Sheet1!$A$1:$B$3)

การใช้ฟังก์ชันต่างๆ

นอกจากใช้ =Link ตรงๆ แล้ว เราก็สามารถเขียนสูตร ใช้ฟังก์ชันต่างๆ ข้ามไฟล์ได้ตามปกติ (ใช้วิธีเอา Mouse ไปจิ้มข้ามไฟล์เอานะ ไม่ต้องไปพิมพ์พวก [ ] เอง)

การ Link ด้วยสูตรทั่วไป รวมถึงพวก VLOOKUP เราจะสามารถปิดไฟล์ต้นทางได้ ไฟล์ปลายทางจะยังเห็นข้อมูลอยู่โดยที่สูตรไม่พัง (เพราะมันเก็บไว้ใน Cache ของไฟล์ปลายทาง ขนาดของไฟล์ปลายทางจะใหญ่ขึ้นด้วย)

แต่มันจะมีสูตรบางตัว ที่จะไม่สามารถใช้ได้ หากปิดไฟล์ต้นทาง เช่น พวก SUMIFS COUNTIFS รวมถึงสูตรที่เป็น Volatile Function (ต้องคำนวณใหม่ตลอดเวลา) เช่น OFFSET INDIRECT เป็นต้น

พอปิดไฟล์ต้นทางแล้ว หากมีการเข้าไปแก้สูตรแล้ว Enter ใหม่ หรือแก้ bb เป็น cc เพื่อบังคับให้ Calculate ใหม่ มันจะเจ๊งเลย

การเปลี่ยนตำแหน่งไฟล์ต้นทาง

หากเรา Link สูตรไว้แล้ว เช่น VLOOKUP แล้วมีการเปลี่ยนตำแหน่งไฟล์ต้นทาง เวลาเปิดไฟล์ปลายทางขึ้นมาใหม่ มันจะบอกว่าหาไฟล์ต้นทางไม่เจอ

ถ้าเรากด Continue มันจะเอาค่าที่จำไว้มาใช้ (ซึ่งอาจไม่ใช่ค่าที่อัปเดทล่าสุดก็ได้) ดังนั้นทางแก้ที่เหมาะสมคือ ต้องกด Edit Links… แล้วกด Change Source… เพื่อแก้ที่อยู่ไฟล์ใหม่ซะ

ใช้ VBA

วิธีต่อไปคือการใช้ VBA ซึ่งเราสามารถสั่งให้มันเอาค่าจากอีกไฟล์มาใส่ยังไฟล์ที่เราต้องการได้ ซึ่งถ้าเขียน Code ไม่เป็น ก็ใช้ Macro Recorder ที่อยู่ใน Ribbon Developer แล้ว Copy Paste ตามปกติได้ ซึ่งถ้าไล์เปิดไว้อยู่แล้ว ก็จะได้ Code ออกมาดังนี้

Sub Macro1()
 '
 ' Macro1 Macro
 '
 '
     Windows("Book1.xlsx").Activate
     Range("A1:B3").Select
     Selection.Copy
     Windows("Book2.xlsx").Activate
     Range("A1").Select
     ActiveSheet.Paste
 End Sub

แต่ code ทั้งบนจะใช้ได้ก็ต่อเมื่อเปิดไฟล์ค้างไว้ทั้งคู่ ดังนั้นเวลาเรา Record Macro ควรจะปิดไฟล์ต้นทางไว้ก่อน มันจะได้บันทึกการเปิดไฟล์ต้นทางไว้ด้วย ดังนี้

Sub Macro2()
 '
 ' Macro2 Macro
 '
 '
     Workbooks.Open Filename:="D:\ThepExcel\link file\sub\Book1.xlsx"
     Range("A1:B3").Select
     Selection.Copy
     Windows("Book2.xlsx").Activate
     Range("A1").Select
     ActiveSheet.Paste
 End Sub

จากนั้นเราค่อยเอา Macro2 นี้ไปสั่ง Run เมื่อกดปุ่ม หรือมี event บางอย่างอีกทีก็ได้

ใช้ Power Query

วิธีสุดท้ายที่จะแนะนำคือ Power Query ซึ่งทำงานได้ดีขณะที่ไฟล์ต้นทางปิดอยู่ครับ (ถ้าเปิดอยู่ ก็ต้อง save ก่อน จึงจะเห็นข้อมูลล่าสุด) ให้เลือก Get Data จากไฟล์ Excel แล้วเลือกไฟล์ต้นทางที่เราต้องการ

จากนั้นเลือก sheet หรือ table ที่ต้องการ และถ้าอยากตรวจสอบหรือดัดแปลงข้อมูลก่อนก็ให้กด Transform Data แต่ถ้ามั่นใจว่าข้อมูล ok ก็จะกด Load Data ออกมาเลยก็ได้

พอกด Transform Data ก็จะเปิด Power Query Editor ขึ้นมา ซึ่งในนี้เรายังทำอะไรได้อีกมาก เช่น ลบคอลัมน์ที่ไม่ต้องการ หรือ Filter ให้เหลือเฉพาะข้อมูลที่ต้องการได้เลย

ถ้าข้อมูลดู ok แล้วกดกด Close & Load to… ได้เลย

จากนั้นก็เลือกได้ว่า จะเอา Data ออกมาเป็นอะไร เช่น Table และจะไว้ตรงไหนก็ได้

จะได้ผลลัพธ์ออกมาเป็น Table แบบนี้

ถ้ามีแก้ข้อมูลต้นทาง แล้ว Save แล้ว ให้มาที่ข้อมูลปลายทางแล้วกด คลิ๊กขวาที่ตารางปลายทาง แล้ว Refresh ได้เลย

ถ้ามีการย้ายตำแหน่งไฟล์ต้นทาง ให้ไปแก้ที่อยู่ได้ที่ Data Source Setting ตามรูปได้เลย

พอแก้ตำแหน่งไฟล์แล้ว คราวนี้ก็จะ Refresh ได้ครับ

จบแล้ว

ถ้าอ่านจบแล้ว ใครสงสัยตรงไหนหรือไม่คำถามอะไรก็ถามไว้ได้เลยนะครับ