“อยากเขียนสูตร Excel ดึงข้อมูลข้ามไฟล์ ทำไงดีครับ?” ลูกศิษย์คนหนึ่งถาม
“พิมพ์ = แล้วคลิกเซลล์ที่ต้องการไม่ได้หรือครับ?” ผมถามกลับ
“คืออยากให้สูตรปรับเปลี่ยนได้น่ะครับ เช่น ดึงข้อมูลจากไฟล์เดือนมกรา หรือดึงข้อมูลจากไฟล์เดือนกุมภาก็ได้” ลูกศิษย์อธิบาย
“อ้อ บางที อาจอยากให้เปลี่ยนชื่อ Sheet ได้ด้วยนะครับ เช่น Sheet1 หรือ Sheet2 ก็ได้” ลูกศิษย์อธิบายต่อ
“หรือถ้าเขียนสูตรแบบระบุโฟลเดอร์ได้ยิ่งดีเลยครับ เช่น ดึงข้อมูลจากโฟลเดอร์ A หรือดึงข้อมูลจากโฟลเดอร์ B บางทีผมตั้งชื่อไฟล์เป็นชื่อเดือนแต่ข้อมูลคนละอย่างกันครับ” ความต้องการเริ่มพรั่งพรู
ความต้องการดูเยอะมากเลยใช่ไหมครับ
แต่ถ้าเรารู้จักฟังก์ชันที่ชื่อ INDIRECT โจทย์ข้อนี้ก็คือขนมดี ๆนี่เอง 😊
เพื่อให้เห็นภาพชัดเจน ขออธิบายด้วยตัวอย่างนี้ครับ
สมมติเก็บข้อมูลไว้ในไฟล์ชื่อ 2016_Jan.xlsx ในชีตชื่อ Sheet1
ข้อมูลที่ว่าหน้าตาแบบนี้
ไฟล์ 2016_Jan.xlsx ถูกเก็บไว้ในไดร์ฟ D โฟลเดอร์ชื่อ RawData และอยู่ในโฟลเดอร์ย่อยชื่อ DataByMonth
หรือถ้าเขียนที่อยู่ไฟล์แบบเต็มๆก็คือ D:\RawData\DataByMonth
ถ้าต้องการดึงข้อมูลในเซลล์ B2 จากไฟล์ดังกล่าว ปกติเราก็แค่พิมพ์ = เลือกไฟล์นั้น แล้วคลิกไปยังเซลล์ที่ต้องการ (B2)
พอคลิกแล้วได้ผลลัพธ์แบบนี้
=[2016_Jan.xlsx]Sheet1!$B$2
นี่คือสูตรการอ้างอิงแบบย่อครับ
แล้วการอ้างอิงแบบเต็มล่ะ?
ถ้าลองปิดไฟล์ 2016_Jan.xlsx สูตรที่เคยลิงค์ไว้จะเปลี่ยนเป็น
=’D:\RawData\DataByMonth\[2016_Jan.xlsx]Sheet1′!$B$2
สูตรจะเพิ่ม “ที่อยู่” ของไฟล์เข้าไปด้วยนั่นเอง
มาลองทำความเข้าใจการอ้างอิงแบบนี้กันครับ ^__^
=’D:\RawData\DataByMonth\[2016_Jan.xlsx]Sheet1‘!$B$2
D:\RawData\DataByMonth คือที่อยู่ไฟล์
สังเกตว่าการอ้างอิงในสูตรจะลงท้ายด้วยเครื่องหมาย \ (Backslash) หรือเขียนเต็มๆก็คือ
D:\RawData\DataByMonth\
2016_Jan.xlsx คือชื่อไฟล์ (ควรมีนามสกุลไฟล์ต่อท้ายเพื่อป้องกันความผิดพลาด)
สังเกตว่าการอ้างอิงในสูตรจะมีวงเล็บก้ามปู [ ] (Square Bracket) ครอบหัวท้าย หรือเขียนเต็มๆก็คือ
[2016_Jan.xlsx]
Sheet1 คือชื่อชีต
สังเกตว่า ด้านหน้าที่อยู่ไฟล์ (D:\RawData\DataByMonth) และด้านหลังชื่อชีตจะถูกปิดหัวท้ายด้วยเครื่องหมาย ‘ (Apostrophe, ฝนทอง) หรือเขียนเต็มๆก็คือ
‘D:\RawData\DataByMonth\[2016_Jan.xlsx]Sheet1‘
นี่คือไวยากรณ์การอ้างอิงชื่อ ต้องขึ้นด้วยเครื่องหมาย ‘ และลงท้ายด้วยเครื่องหมาย ‘
$B$2 คือชื่อเซลล์ที่เก็บข้อมูลนั้น
สังเกตว่าการอ้างอิงชื่อเซลล์ต้องขึ้นต้นด้วยเครื่องหมายตกใจ ! (Exclamation Mark) หรือเขียนเต็มๆก็คือ
!$B$2
ถ้าแทนคำว่า “ที่อยู่ไฟล์”, “ชื่อไฟล์”, “ชื่อชีต” และ “ชื่อเซลล์” ลงไปในสูตร จะได้ผลลัพธ์เป็น
=‘ที่อยู่ไฟล์ \[ ชื่อไฟล์ ] ชื่อชีต ‘! ชื่อเซลล์
นี่คือไวยากรณ์การอ้างอิงเซลล์แบบเต็ม
(ต้องใช้เครื่องหมาย ‘ \ [ ] ! ให้ถูกต้อง)
ถึงตรงนี้เราน่าจะเข้าใจการอ้างอิงเซลล์แล้ว ดังนั้นถ้าใช้ไวยากรณ์การอ้างอิงเซลล์แบบนี้ แต่เปลี่ยนตำแหน่งเซลล์จาก B2 เป็น B3 ก็น่าจะง่ายนิดเดียว
เพื่อให้ข้อมูลปรับเปลี่ยนได้แบบไดนามิค ก็น่าจะพิมพ์ ที่อยู่ไฟล์ ชื่อไฟล์ ชื่อชีต ชื่อเซลล์ ไว้ในเซลล์ใดๆ แล้วเขียนสูตรลิงค์ค่าได้ เช่น
ระบุ ที่อยู่ไฟล์ ในเซลล์ A5
ระบุ ชื่อไฟล์ ในเซลล์ B5
ระบุ ชื่อชีต ในเซลล์ C5
ระบุ ชื่อเซลล์ ในเซลล์ D5
หรือระบุแล้วได้หน้าตาแบบนี้
จากสูตร
=’ ที่อยู่ไฟล์ \[ ชื่อไฟล์ ] ชื่อชีต ‘! ชื่อเซลล์
แทนค่า A5, B5, C5, D5 ลงไป
(เพื่อความสะดวก พิมพ์เครื่องหมาย \ ต่อท้าย ที่อยู่ไฟล์ เลย หรือระบุ ที่อยู่ไฟล์ เป็น D:\RawData\DataByMonth\)
เชื่อมกับเครื่องหมาย ‘ [ ] ! ด้วยเครื่องหมาย & (Ampersand)
เนื่องจากเครื่องหมาย ‘ [ ] ! ไม่สามารถพิมพ์ตรงๆในสูตรได้ เวลาพิมพ์ต้องครอบด้วยเครื่องหมาย ” “ (Quotation Marks, เครื่องหมายคำพูด)
หรือเขียนสูตรได้เป็น
=”‘“&A5&”[“&B5&”]“&C5&”‘!“&D5
สูตรนี้เขียนตรงตามไวยากรณ์ทุกอย่าง แทนที่จะได้ค่าในเซลล์ B3 (จากตัวอย่างคือ 7262) แต่กลับได้ผลลัพธ์เป็น
‘D:\RawData\DataByMonth\[2016_Jan]Sheet1’!B3
ซะงั้นอ่ะ !
ทำไมจึงเป็นเช่นนั้น?
เพราะการเขียนสูตรในลักษณะนี้ เอ็กเซลจะเข้าใจว่าผลลัพธ์คือตัวหนังสือ (Text) การเชื่อมด้วยเครื่องหมาย & ก็คือการเชื่อมตัวหนังสือเข้าด้วยกันนั่นเอง
งั้นไม่ต้องเชื่อมด้วย & ได้ไหม เอ็กเซลจะได้ไม่เข้าใจผิด?
ไม่ได้ครับ ถ้าไม่เชื่อมด้วย & เอ็กเซลจะไม่ยอมให้กด Enter ครับ จะขึ้นข้อความเตือน (Error Message) แบบนี้
แล้วจะทำยังไงดี?
เรามาถูกทางแล้วครับ เพียงแต่ถ้าพิมพ์แค่นี้ เอ็กเซลจะมองว่าผลลัพธ์คือตัวหนังสือ (Text)
ถ้าต้องการเปลี่ยนตัวหนังสือให้เป็นการอ้างอิงเซลล์ ต้องครอบด้วยฟังก์ชัน INDIRECT
หรือจาก
“‘”&A5&”[“&B5&”]”&C5&”‘!”&D5
เป็น
=INDIRECT(“‘”&A5&”[“&B5&”]”&C5&”‘!”&D5)
จะได้ผลลัพธ์ที่ต้องการ
เยส !!
ถ้าต้องการเปลี่ยนเป็นดึงข้อมูลจากไฟล์อื่น ชีตอื่น หรือเซลล์อื่น ก็ใช้สูตรเดิมได้เลย เปลี่ยนแค่ตัวแปรก็พอ
เยส เยส !!
ถ้าต้องการเปลี่ยนโฟลเดอร์ ก็พิมพ์ที่อยู่โฟลเดอร์ที่ต้องการ (ในคอลัมน์ A) ได้เลย
เยส เยส เยส !!
เจ๋งสุดๆป่ะล่ะ ^^
อ้อ! เวลาดึงข้อมูล ต้องเปิดไฟล์ที่ต้องการดึงข้อมูล (ในตัวอย่างคือ 2016_Jan.xlsx, 2016_Feb.xlsx, 2016_Mar.xlsx) ด้วยครับ ไม่งั้นผลลัพธ์จะกลายเป็น #REF!
หมายเหตุ: ถ้าชื่อไฟล์ที่ถูกดึงข้อมูลไม่มีโอกาสซ้ำกับไฟล์ในโฟลเดอร์อื่น การอ้างอิงไม่จำเป็นต้องระบุ ที่อยู่ไฟล์ ก็ได้ หรืออาจเขียนสูตรเป็น
=INDIRECT(“‘[“&A5&”]“&B5&”‘!“&C5)
(การระบุ ที่อยู่ไฟล์ ในตัวอย่างแรก เพื่อป้องกันกรณีตั้งชื่อไฟล์ซ้ำกับข้อมูลในโฟลเดอร์อื่น และเผลอเปิดไฟล์ผิด)
ทำไมการครอบด้วยฟังก์ชัน INDIRECT จึงได้ผลลัพธ์ที่ต้องการ?
ก็เพราะนี่คือความสามารถของฟังก์ชัน INDIRECT นั่นเอง (ตอบแบบกำปั้นทุบดินหรือเปล่าเนี่ย ^^)
ความสามารถของฟังก์ชัน INDIRECT คือการเปลี่ยน “ข้อความ” ให้กลายเป็น การอ้างอิงเซลล์ (Cell Reference) หรือใช้กับเคสแบบนี้นั่นเอง
งงไหมครับ?
มาทำความรู้จักฟังก์ชัน INDIRECT กันสักหน่อย
โครงสร้างฟังก์ชันคือ
INDIRECT( ref_text, [a1] )
ref_text คือ ข้อความที่ระบุการอ้างอิงเซลล์ (หรือช่วงเซลล์)
a1 คือ รูปแบบของ ref_text
(จากโครงสร้างฟังก์ชัน จะเห็นได้ว่า a1 อยู่ภายใต้วงเล็บก้ามปู [a1] หมายความว่าจะระบุหรือไม่ระบุ a1 ในสูตรก็ได้)
ถ้าระบุ a1 เป็น TRUE (หรือไม่ระบุ) หมายถึง ref_text นั้นอ้างอิงด้วยระบบ A1 (เช่น C2, D7, E9)
ถ้าระบุ a1 เป็น FALSE หมายถึง ref_text นั้นอ้างอิงด้วยระบบ R1C1 (เช่น R2C3, R7C4, R9C5) ซึ่งปัจจุบันไม่ค่อยมีใครอ้างอิงเซลล์ด้วยระบบนี้แล้ว
(เวลาเรียก D7 เราก็เรียก D7 ไม่ได้เรียก R7C4 ใช่ไหมครับ ^^)
ref_text หรือข้อความที่ระบุการอ้างอิงเซลล์ มีได้ 4 แบบ คือ
- ชื่อเซลล์ที่ระบุด้วยระบบ A1 (เช่น C2, D7, E9)
- ชื่อเซลล์ที่ระบุด้วยระบบ R1C1 (เช่น R2C3, R7C4, R9C5)
- ชื่อที่ตั้งขึ้นในชีตหรือไฟล์นั้น (Name Defined) อาจตั้งชื่อโดยการใช้ Name Box หรือ Define Name (Ctrl+Alt+F3) หรือ Create From Selection (Ctrl+Shft+F3) ก็ได้
- ข้อความที่ระบุชื่อเซลล์หรือช่วงเซลล์ (Text String) เช่นเคสของตัวอย่างนี้ (“‘”&A6&”[“&B6&”]”&C6&”‘!”&D6)
ขออธิบาย ref_text แบบแรก (ชื่อเซลล์ที่ระบุด้วยระบบ A1) เพิ่มเติมเล็กน้อยครับ
เวลาใช้ ถ้าครอบด้วยเครื่องหมายคำพูด (เช่น INDIRECT(“A2”) ) จะหมายถึงเซลล์ A2 ค่าที่ส่งกลับมาก็คือข้อมูลที่อยู่ในเซลล์ A2
แต่ถ้าไม่ครอบด้วยเครื่องหมายคำพูด (เช่น INDIRECT(A2) ) จะหมายถึงให้แปลง A2 เป็นข้อมูลที่อยู่ในเซลล์ A2 ก่อน แล้วค่อยแปลงข้อมูลนั้นอีกที (แปลง 2 รอบ)
ถ้าข้อมูลที่อยู่ในเซลล์ A2 คือตัวอักษรที่เกี่ยวข้องกับการอ้างอิง (เช่น เซลล์ A2 เก็บค่า B2 เอาไว้) จะได้ผลลัพธ์เป็นการอ้างอิงนั้นก่อน (แปลงจาก A2 เป็น B2) และ INDIRECT จะแปลงการอ้างอิงเป็นผลลัพธ์อีกทีหนึ่ง (เช่น ถ้า B2 เก็บค่า 20 ไว้ ผลลัพธ์ที่ได้จาก INDIRECT(A2) คือ 20)
หรือจะมองว่าเป็นการอ้างอิงแบบ 2 ชั้นก็ได้
INDIRECT(A2) –> INDIRECT(B2) –> 20
แต่ถ้าข้อมูลในเซลล์ A2 เป็นตัวอักษรที่ไม่เกี่ยวข้องกับการอ้างอิง หรือไม่ใช่ชื่อที่ตั้งขึ้นในไฟล์นั้น (Name Defined) จะได้ผลลัพธ์เป็น #REF!
โดยส่วนตัว ผมคิดว่าฟังก์ชัน INDIRECT คือฟังก์ชันที่ ‘ชวนงง’ ที่สุดแล้ว !
เป็นฟังก์ชันที่ใช้เวลานานมากกว่าจะเข้าใจ (ตอนนี้ยังไม่รู้เลยว่าเข้าใจถูกหรือเปล่า ^^)
การใช้งานแบบตรงๆก็ชวนงงแล้ว พอประยุกต์ใช้งานก็จะยิ่งชวนงง แถมประยุกต์ใช้งานได้หลากหลายอีกต่างหาก
สูตรอาร์เรย์ (Array Formula) ก็มักใช้ฟังก์ชัน INDIRECT ร่วมด้วยเสมอ (เช่น ROW(INDIRECT(“1:”&LEN(A2)))
ถ้ายังไม่เข้าใจการใช้งาน INDIRECT ผมขอให้เข้าใจเบื้องต้นว่า INDIRECT คือการเปลี่ยน ตัวหนังสือ (Text String) ให้กลายเป็น การอ้างอิงเซลล์ (Cell Reference) ก่อน
ถ้าจับจุดนี้ได้ จะค่อยๆเข้าใจตัวอย่างประยุกต์อื่นๆครับ ^__^
INDIRECT เจ๋งขนาดนี้ งั้นก็ควรใช้บ่อยๆสิ?
ข้อเสียของฟังก์ชัน INDIRECT นอกจากเข้าใจยากแล้ว ยังเป็นฟังก์ชันที่เปลี่ยนแปลงอย่างทันทีทันใด (Volatile Function)
ขออธิบายคำว่า “ฟังก์ชันที่แปลงแปลงอย่างทันทีทันใด” (Volatile Function) แบบสั้นๆนะครับ
ความหมายคือ ถ้าไฟล์นี้เกิดการเปลี่ยนแปลง ฟังก์ชันนั้นจะคำนวณใหม่ (Recalculate) ทันที แม้ว่าการเปลี่ยนแปลงนั้นจะไม่เกี่ยวข้องกับอาร์กิวเมนต์ในฟังก์ชันก็ตาม
(อาร์กิวเมนต์ คือองค์ประกอบในฟังก์ชัน)
Volatile Function ที่พบบ่อยๆคือ
- NOW()
- TODAY()
- RAND()
- OFFSET
- INDIRECT
(จะเห็นได้ว่า INDIRECT อยู่ในลิสต์นี้ด้วย)
ถ้าใช้ฟังก์ชันพวกนี้นิดหน่อยก็คงไม่เป็นไร แต่ถ้าใช้ดึงข้อมูลในลักษณะตัวอย่างนี้ พอเขียนสูตรดึงข้อมูล 50,000 บรรทัด แล้วนำไปคำนวณต่อ จะรู้เลยว่า “ความช้า” เป็นยังไง
บางทีพอไฟล์คำนวณปุ๊ป ลุกไปเข้าห้องน้ำเลยก็ได้ พอกลับมาไฟล์น่าจะคำนวณเสร็จพอดี (ช้าขนาดนั้นจริงๆ T_T)
ทางที่ดีที่สุดคือ นำข้อมูลในไฟล์ทั้งหมดมาต่อกันในลักษณะของฐานข้อมูล (Database) จะได้ไม่ต้องเขียนสูตรดึงข้อมูล เวลาจะสรุปหรือดึงข้อมูลไปใช้ต่อก็ง่ายเหมือนปอกกล้วยเข้าปาก (ช้าง ^^)
เช่นเคสนี้ เดิมทีแยกเก็บข้อมูลเป็น 4 ไฟล์
- 2016_Jan.xlsx
- 2016_Feb.xlsx
- 2016_Mar.xlsx
- 2016_Apr.xlsx
เราก็รวมข้อมูลจากทั้ง 4 ไฟล์เข้าด้วยกันเป็นไฟล์เดียว หรือรวมแล้วมีหน้าตาประมาณนี้
(ระบุด้วยว่าข้อมูลนี้เป็นของปีใด เดือนใด)
พอรวมเป็นไฟล์เดียวแล้ว จะทำอะไรต่อก็สบายบรื๋อ ไม่ต้องเขียน INDIRECT ให้ยุ่งยากด้วย ชีวิตดีขึ้น 10 เท่า
แต่ถ้าแยกไฟล์เหมือนเดิม แล้วพอมีข้อมูลของปีอื่นๆร่วมด้วย เช่น 2017, 2018, 2019 พอสรุปข้อมูลที ชีวิตจะเศร้าเป็น 10 เท่า T_T
ไฟล์คำนวณที่ดีไม่จำเป็นต้องมีสูตรที่ดูคูล ดูเท่ หรือดูเทพ แต่ขอให้เป็นไฟล์ที่คำนวณได้รวดเร็วและถูกต้อง ก็พอแล้ว
จริงไหมครับ ^__^
ใครสนใจไฟล์ตัวอย่าง สามารถดาวน์โหลดไฟล์ด้านล่างได้เลยครับ มีทั้งหมด 6 ไฟล์ครับ
เพื่อให้สูตรทำงานได้ตามตัวอย่าง รบกวนสร้างโฟลเดอร์ชื่อ RawData ในไดร์ฟ D ก่อน จากนั้นสร้างโฟลเดอร์ย่อยชื่อ DataByMonth ภายในโฟลเดอร์ RawData
แล้วนำไฟล์ทั้งหมดไปเก็บในโฟลเดอร์ DataByMonth ครับ
INDIRECT_DynamicFileReference_180119
INDIRECT_DynamicFileReference_NoFolderPath_180119
.
หากคุณชอบบทความแนวนี้ สามารถอัพเดตบทความใหม่โดยคลิก Like เฟซบุ๊กแฟนเพจ วิศวกรรีพอร์ต หรือคลิก ที่นี่
อย่าลืมแชร์ให้เพื่อนอ่านเพื่อเป็นกำลังใจให้คนเขียนด้วยนะครับ ^__^
อ้อ! ตอนนี้ผมมีคอร์สออนไลน์ชื่อ “Excel In Essence: รู้ Excel เท่านี้ ชีวิตดีขึ้นมากมาย” สนใจดูรายละเอียดได้จาก ลิงค์นี้ ครับ
อ้างอิงข้อมูลจาก
http://www.decisionmodels.com/calcsecretsi.htm
https://msdn.microsoft.com/en-us/library/office/bb687891.aspx#sectionSection2