Thep Excel
FILTERXMLฟังก์ชันดึงข้อมูลจาก XML โดยใช้ XPath
FILTERXML ดึงข้อมูลเฉพาะส่วนจาก XML content โดยใช้ XPath expression เพื่อระบุตำแหน่งของข้อมูลที่ต้องการ ทำให้ง่ายในการแยกข้อมูลที่เป็นประโยชน์จากผลลัพธ์ XML ที่ส่งกลับมาจาก WEBSERVICE มักใช้ร่วมกับ WEBSERVICE และ ENCODEURL เพื่อดึงข้อมูลจาก web API
Syntax & Arguments
| อาร์กิวเมนต์ | ชนิด | คำอธิบาย |
|---|---|---|
| xml | text | ข้อมูล XML ในรูปแบบข้อความที่ถูกต้อง (valid XML format) อาจมาจากเซลล์ที่มี XML string หรือจากผลลัพธ์ของ WEBSERVICE function |
| xpath | text | XPath expression ในรูปแบบข้อความ (string) ซึ่งใช้ระบุเส้นทางและตำแหน่งของข้อมูลที่ต้องการดึงจาก XML เช่น //element, //parent/child, //@attribute ฯลฯ |
Additional Notes
FILTERXML เป็นฟังก์ชันที่ใช้ดึงข้อมูลเฉพาะส่วนจากข้อมูล XML โดยใช้ XPath expression ฟังก์ชันนี้มีประโยชน์เมื่อใช้ร่วมกับ WEBSERVICE เพื่อดึงข้อมูลจาก web service และดึงเฉพาะข้อมูลที่ต้องการ เช่น ราคาหุ้น อัตราแลกเปลี่ยน หรือข้อมูลสภาพอากาศ
อีกมุมที่หลายคนไม่รู้คือ FILTERXML ไม่จำเป็นต้องรอ XML จริงจาก web service เท่านั้น คุณสร้าง XML ปลอมขึ้นเองในสูตรด้วย SUBSTITUTE ก็ได้ ตรงนี้แหละที่คนใช้เป็น workaround แยกข้อความที่คั่นด้วยจุลภาคก่อนที่ Excel จะมี TEXTSPLIT ครับ
ข้อควรระวังคือ FILTERXML ใช้ได้เฉพาะ Excel for Windows (365/2024/2021/2019/2016) เท่านั้น ถ้าไฟล์ต้องเปิดบน Mac หรือ Excel for the web สูตรนี้จะพังทันที ต้องเช็คก่อนว่าคนที่ใช้ไฟล์ต่อจากคุณเปิดด้วยอะไร
How it works
ดึงราคาหุ้นจาก XML API
ใช้ FILTERXML ร่วมกับ WEBSERVICE เพื่อดึงราคาปิดล่าสุดของหุ้นจากเซอร์วิสที่ส่งกลับข้อมูล XML โดยใช้ XPath expression เพื่อหาตำแหน่งของข้อมูลราคา
ดึงข้อมูลจากฐานข้อมูล XML
หากมีไฟล์ XML ที่เก็บข้อมูลต่าง ๆ เช่น ข้อมูลพนักงาน ข้อมูลสินค้า สามารถใช้ FILTERXML เพื่อดึงข้อมูลที่ต้องการจากไฟล์ XML นั้นได้
แยกข้อมูลจากหลายแหล่ง XML
ใช้ FILTERXML เพื่อดึงข้อมูลที่จำเป็นจากผลลัพธ์ XML ที่ได้จากหลาย web service ต่างๆ เช่น ข้อมูลอากาศ อัตราแลกเปลี่ยน ราคาน้ำมัน
Examples
ตัวอย่างที่ 1: ดึงธาตุพื้นฐาน (element) จาก XMLFILTERXML(A1, "//element")
สูตรนี้ดึงข้อมูล XML จากเซลล์ A1 และแยกค่าทั้งหมดที่อยู่ใน element ที่ชื่อ 'element' ผลลัพธ์จะแสดงทั้งหมดที่ตรงกับ XPath expression //element (เครื่องหมาย // หมายถึงค้นหาจากที่ใด ๆ ในเอกสาร)
ตัวอย่างที่ 2: ดึงข้อมูลราคาจาก web serviceFILTERXML(WEBSERVICE("http://dev.markitondemand.com/MODApis/Api/Quote/xml?symbol="&ENCODEURL(C2)),"//QuoteApiModel/Data/LastPrice")
สูตรนี้ส่งคำขอไปยัง web service ด้วย WEBSERVICE เพื่อดึงข้อมูล XML ของราคาหุ้น จากนั้น FILTERXML ใช้ XPath expression //QuoteApiModel/Data/LastPrice เพื่อดึงข้อมูลราคาปิดล่าสุดจากผลลัพธ์ XML ENCODEURL จะเข้ารหัสรหัสหุ้นจากเซลล์ C2 ให้ปลอดภัยสำหรับ URL
ตัวอย่างที่ 3: ดึง attribute จาก XML elementFILTERXML(A1, "//element/@title")
สูตรนี้ดึงค่า attribute ชื่อ 'title' จากทุก element ที่ตรงกับเส้นทาง XPath เครื่องหมาย @ ใช้เพื่อระบุว่ากำลังดึง attribute เช่น @title @id @href ผลลัพธ์คือค่า title attribute ทั้งหมดจากผลลัพธ์ XML
ตัวอย่างที่ 4: แยกข้อความที่คั่นด้วยจุลภาคโดยไม่ต้องใช้ TEXTSPLITFILTERXML(""&SUBSTITUTE(A1,",","")&"","//s")
เทคนิคนี้ SUBSTITUTE เปลี่ยนจุลภาคทุกตัวใน A1 ให้เป็น </s><s> ก่อน แล้วห่อด้วย <t><s>…</s></t> ให้กลายเป็น XML ที่ถูกต้อง จากนั้น FILTERXML ใช้ //s ดึงค่าทุก tag s ออกมาเป็น array วิธีนี้ใช้แยกข้อความคั่นจุลภาคได้ในทุกเวอร์ชันที่มี FILTERXML แม้จะไม่มี TEXTSPLIT ก็ตาม แต่ระวังถ้าข้อความมีอักขระ < > & ปนอยู่ ต้อง SUBSTITUTE อักขระเหล่านั้นออกก่อน ไม่งั้น XML จะเสียรูปแล้วขึ้น #VALUE!
ตัวอย่างที่ 5: กรองเฉพาะ element ที่ attribute ตรงเงื่อนไขFILTERXML("102030","//item[@type='A']")
XPath รองรับเงื่อนไขในวงเล็บเหลี่ยม [@attribute='ค่า'] เพื่อกรองเฉพาะ element ที่ตรงเงื่อนไขก่อนดึงค่า ในตัวอย่างนี้มี item 3 ตัว แต่ FILTERXML ดึงเฉพาะ 2 ตัวที่ type="A" คือ 10 และ 30 ออกมา เป็นเทคนิคที่มีประโยชน์เวลาต้องแยกข้อมูลตามหมวดหมู่จาก XML ที่มีหลาย record ปนกัน
FAQs
XPath expression คืออะไร?+
XPath (XML Path Language) เป็นภาษาที่ใช้ระบุเส้นทางของข้อมูลในเอกสาร XML โดยใช้สัญลักษณ์พิเศษ เช่น / สำหรับระบุเส้นทาง // สำหรับค้นหาที่ใด ๆ [] สำหรับระบุเงื่อนไข @ สำหรับ attribute ตัวอย่างเช่น //element[@id='123'] หมายถึงค้นหา element ที่มี id เท่ากับ 123
ความแตกต่างระหว่าง FILTERXML กับ WEBSERVICE คืออะไร?+
WEBSERVICE เป็นฟังก์ชันที่ส่งคำขอไปยัง web service และส่งกลับข้อมูล XML ทั้งหมด ส่วน FILTERXML เป็นฟังก์ชันที่ดึงข้อมูลเฉพาะส่วนจาก XML โดยใช้ XPath มักใช้ร่วมกัน WEBSERVICE จะดึง XML และ FILTERXML จะแยกข้อมูลที่ต้องการจาก XML นั้น
FILTERXML รองรับข้อมูลอะไรบ้าง?+
FILTERXML รองรับเฉพาะข้อมูล XML เท่านั้น ไม่รองรับ JSON หรือรูปแบบข้อมูลอื่น ๆ หากข้อมูลจากเว็บเซอร์วิสเป็น JSON ต้องใช้เมธอดอื่นในการประมวลผล
เมื่อ FILTERXML ไม่พบข้อมูล จะเกิดอะไรขึ้น?+
FILTERXML จะแสดงข้อผิดพลาด #VALUE! ถ้า XML ไม่ถูกต้อง (invalid) หรือถ้า XPath expression ไม่ถูกต้อง หากค้นหาข้อมูลแต่ไม่พบจะไม่แสดงข้อมูล (ว่าง) ใช้ IFERROR เพื่อจัดการข้อผิดพลาดได้
FILTERXML คืนหลายค่าพร้อมกัน แล้วผมอยากได้แค่ค่าแรกทำยังไง?+
ห่อด้วย INDEX ครับ เช่น =INDEX(FILTERXML(A1,"//element"),1) จะได้เฉพาะค่าแรกของ array ที่ FILTERXML คืนมา ใน Excel รุ่นเก่าที่ไม่มี dynamic array บางทีต้องกด Ctrl+Shift+Enter ตอนใส่สูตร ไม่งั้นจะเห็นแค่ค่าแรกเป็นค่า default อยู่แล้วโดยไม่รู้ว่ามันคืน array มาจริงๆ
Resources & Related
ฟังก์ชันที่ผู้เขียนโยงไว้กับ FILTERXML จัดกลุ่มตามหมวด · ชี้ที่ชื่อใดชื่อหนึ่ง แล้วฟังก์ชันที่ใช้คู่กันจะมีจุดทอง
ENCODEURL แปลงข้อความธรรมชาติเป็น URL-encoded string โดยแทนที่อักขระพิเศษ (เช่น ช่องว่าง สัญลักษณ์พิเศษ) ด้วยรหัสเลขฐานสิบหก ทำให้ข้อความปลอดภัยสำหรับใช้ในการขอ URL โดยมักใช้ร่วมกับ WEBSERVICE และ FILTERXML ในการค้นหาข้อมูลจากเว็บ API
WEBSERVICE ส่งคำขอ GET ไปยัง URL ของ web service (API) บนอินเทอร์เน็ตหรือ Intranet และส่งกลับข้อมูลที่ได้รับ ใช้เพื่อดึงข้อมูลแบบ real-time เช่น ราคาหุ้น อัตราแลกเปลี่ยน ข้อมูลสภาพอากาศ โดยปกติใช้ร่วมกับ FILTERXML เพื่อแยกข้อมูลที่ต้องการจากผลลัพธ์ XML และกับ ENCODEURL เพื่อเข้ารหัส URL ให้ปลอดภัย
TEXTJOIN รวมข้อความจากหลายเซลล์หรือทั้งช่วงเป็นข้อความเดียว โดยกำหนดตัวคั่นเองได้และสั่งข้ามเซลล์ว่างได้ในคำสั่งเดียว ต่างจาก CONCATENATE ที่ต้องพิมพ์ตัวคั่นและเชื่อมทีละเซลล์เอง งานที่ใช้บ่อยคือรวมที่อยู่หลายบรรทัด สร้างลิสต์คั่นด้วยคอมม่าไว้ส่งออก หรือรวมผลลัพธ์จาก FILTER ให้ออกมาเป็นข้อความเดียว
TEXTSPLIT เป็นฟังก์ชัน Dynamic Array ที่ช่วยแยกข้อความในเซลล์ออกเป็นอาร์เรย์ของค่า (Spill) ตามตัวคั่นที่ระบุ สามารถแยกข้อมูลออกไปทางขวา (คอลัมน์) หรือลงด้านล่าง (แถว) หรือทั้งสองอย่างพร้อมกัน เหมาะสำหรับการจัดการข้อมูลนำเข้าที่รวมกันอยู่ในเซลล์เดียว
Comments
อีเมลของคุณจะไม่ถูกเผยแพร่