Thep Excel
WEBSERVICEฟังก์ชันเรียกเว็บเซอร์วิส
WEBSERVICE ส่งคำขอ GET ไปยัง URL ของ web service (API) บนอินเทอร์เน็ตหรือ Intranet และส่งกลับข้อมูลที่ได้รับ ใช้เพื่อดึงข้อมูลแบบ real-time เช่น ราคาหุ้น อัตราแลกเปลี่ยน ข้อมูลสภาพอากาศ โดยปกติใช้ร่วมกับ FILTERXML เพื่อแยกข้อมูลที่ต้องการจากผลลัพธ์ XML และกับ ENCODEURL เพื่อเข้ารหัส URL ให้ปลอดภัย
Syntax & Arguments
| อาร์กิวเมนต์ | ชนิด | คำอธิบาย |
|---|---|---|
| url | text | URL ของ web service (API) ที่ต้องการเรียกในรูปแบบข้อความ (string) โดยต้องเป็น URL ที่ถูกต้องและ web service ต้องรองรับการขอ GET request สูงสุด 2,048 ตัวอักษร ตัวอักษรพิเศษในพารามิเตอร์ต้องเข้ารหัสด้วย ENCODEURL |
Additional Notes
WEBSERVICE เป็นฟังก์ชันที่ส่งคำขอ GET ไปยัง web service (API) บนอินเทอร์เน็ตหรือ Intranet แล้วดึงข้อมูลดิบที่ตอบกลับมาใส่เซลล์ตรงๆ ทำให้ Excel ดึงข้อมูล real-time ได้โดยไม่ต้องเขียน VBA หรือเปิด Power Query
ที่ต้องระวังคือ WEBSERVICE คุยได้เฉพาะ HTTP/HTTPS แบบ GET เท่านั้น ส่งพารามิเตอร์ผ่าน header หรือ Authorization token ไม่ได้เลย ใช้ได้แค่กับ API ที่ไม่ต้อง authentication หรือรับ API key แบบใส่ใน URL ตรงๆ และผลลัพธ์ที่ได้เป็นข้อความดิบ ถ้า API ตอบกลับเป็น XML ค่อยเอาไปต่อกับ FILTERXML ได้ แต่ถ้าตอบกลับเป็น JSON (ซึ่ง API สมัยใหม่ส่วนใหญ่เป็นแบบนี้) FILTERXML จะพังทันทีเพราะมันแกะได้แต่ XML
ส่วนตัวผม จุดที่คนเจอบ่อยที่สุดคือเจอ #VALUE! แล้วงงว่าทำไม — สาเหตุหลักมี 3 อย่าง: หนึ่ง องค์กรบล็อกการเชื่อมต่อภายนอกผ่าน Trust Center ทำให้ใช้ได้ที่บ้านแต่พังที่ออฟฟิศ สอง ผลลัพธ์ยาวเกิน 32,767 ตัวอักษรจนเซลล์รับไม่ไหว สาม API เปลี่ยนไปตอบเป็น JSON แล้วแต่สูตรยังพยายามใช้ FILTERXML แกะอยู่
How it works
ดึงข้อมูลราคาหุ้นแบบ real-time
ใช้ WEBSERVICE ร่วมกับ FILTERXML เพื่อดึงราคาหุ้นล่าสุดจาก web API การทำงาน WEBSERVICE จะส่งคำขอไปยัง API ของผู้ให้บริการข้อมูลหุ้น และส่งกลับข้อมูล XML ของราคาปัจจุบัน
ดึงข้อมูลสภาพอากาศจากเซอร์วิสอากาศ
ใช้ WEBSERVICE เพื่อเรียก web API ของบริการสภาพอากาศเพื่อดึงข้อมูลอุณหภูมิ ความชื้น ลมแบบ real-time ตามสถานที่ที่ระบุ
เรียก RESTful API ของระบบภายใน
ใช้ WEBSERVICE เพื่อเรียก API ที่สร้างขึ้นเอง (Custom API) บน Intranet เพื่อดึงข้อมูลจากฐานข้อมูลบริษัท หรือจากระบบ ERP ต่าง ๆ
Examples
ตัวอย่างที่ 1: เรียก web service ด้วย URL ธรรมชาติWEBSERVICE("http://mywebservice.com/serviceEndpoint?searchString=Excel")
สูตรนี้ส่งคำขอ GET ไปยัง http://mywebservice.com/ ด้วยพารามิเตอร์ searchString=Excel ผลลัพธ์คือข้อมูล XML ทั้งหมดที่ส่งกลับมาจาก web service หลังจากนั้นอาจใช้ FILTERXML เพื่อแยกข้อมูลที่ต้องการจาก XML นี้
ตัวอย่างที่ 2: เรียก web service ด้วย URL จากเซลล์WEBSERVICE(A1)
สูตรนี้ดึง URL จากเซลล์ A1 และส่งคำขอไปยัง URL นั้น ทำให้สามารถเปลี่ยน URL แบบไดนามิก โดยแค่เปลี่ยนค่าในเซลล์ A1 ผลลัพธ์คือข้อมูลที่ส่งกลับจาก web service
ตัวอย่างที่ 3: รวม WEBSERVICE กับ FILTERXML และ ENCODEURL หาราคาหุ้นFILTERXML(WEBSERVICE("http://dev.markitondemand.com/MODApis/Api/Quote/xml?symbol="&ENCODEURL(C2)),"//QuoteApiModel/Data/LastPrice")
สูตรนี้รวม WEBSERVICE กับ FILTERXML และ ENCODEURL เข้าด้วยกัน ขั้นแรก ENCODEURL เข้ารหัส URL จากรหัสหุ้นในเซลล์ C2 ประมาณเช่น MSFT จากนั้น WEBSERVICE ส่งคำขอไปยัง web service เพื่อดึงข้อมูล XML ของราคาหุ้น สุดท้าย FILTERXML ดึงเฉพาะราคาปิดล่าสุด (LastPrice) จากผลลัพธ์ XML โดยใช้ XPath expression
ตัวอย่างที่ 4: ดึงอัตราแลกเปลี่ยนจาก ECB มาแปลงมูลค่าใบแจ้งหนี้ (งานบัญชี)FILTERXML(WEBSERVICE("https://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml"),"//*[local-name()='Cube'][@currency='THB']/@rate")
ธนาคารกลางยุโรป (ECB) เผยแพร่ feed XML อัตราแลกเปลี่ยนรายวันแบบสาธารณะไม่ต้อง API key WEBSERVICE ดึงไฟล์ XML ทั้งชุดมา แล้ว FILTERXML ใช้ XPath ดึงเฉพาะแอตทริบิวต์ rate ของสกุล THB งานบัญชีที่ต้องแปลงมูลค่าใบแจ้งหนี้สกุลต่างประเทศเป็นบาททุกวันใช้แพทเทิร์นนี้อัปเดตอัตราอัตโนมัติแทนการก๊อปวางมือ
ตัวอย่างที่ 5: เช็คตำแหน่งประเทศจาก IP สำหรับทีมวิเคราะห์เว็บ (IT/การตลาด)FILTERXML(WEBSERVICE("http://www.geoplugin.net/xml.gp?ip="&A2),"//geoplugin/geoplugin_countryName")
ทีม IT หรือการตลาดที่มีรายชื่อ IP ผู้เข้าเว็บอยู่ในคอลัมน์ A ใช้ WEBSERVICE เรียก geoplugin (บริการ geolocation ฟรีที่ตอบกลับเป็น XML) แล้ว FILTERXML ดึงเฉพาะชื่อประเทศออกมา ทำให้แปลง IP เป็นประเทศได้ทั้งคอลัมน์โดยไม่ต้องเปิดเครื่องมือแยก เหมาะกับรายงานสรุปที่มาผู้เข้าชมเว็บแบบเร็วๆ
FAQs
WEBSERVICE รองรับ protocol ใดบ้าง?+
WEBSERVICE รองรับเฉพาะ HTTP และ HTTPS เท่านั้น ไม่รองรับ FTP, FILE หรือ protocol อื่น ๆ ดังนั้นต้องใช้ web service ที่เข้าถึงได้ผ่าน HTTP/HTTPS
WEBSERVICE มีข้อจำกัดความยาว URL หรือไม่?+
ใช่ URL ต้องไม่เกิน 2,048 ตัวอักษร และผลลัพธ์ที่ส่งกลับต้องไม่เกิน 32,767 ตัวอักษร (ขีดจำกัดของเซลล์ Excel) หากข้อมูลมากกว่านี้จะเกิดข้อผิดพลาด #VALUE!
WEBSERVICE รองรับ POST request หรือไม่?+
ไม่ WEBSERVICE รองรับเฉพาะ GET request เท่านั้น ถ้า web service ต้องใช้ POST ต้องใช้วิธีอื่น เช่น Power Query หรือ VBA
ความแตกต่างระหว่าง WEBSERVICE กับ FILTERXML คืออะไร?+
WEBSERVICE เป็นฟังก์ชันที่ส่งคำขอและส่งกลับข้อมูล XML ทั้งหมด ส่วน FILTERXML เป็นฟังก์ชันที่แยกข้อมูลเฉพาะส่วนจาก XML มักใช้ร่วมกันคือ WEBSERVICE ดึง XML จาก web service แล้ว FILTERXML แยกข้อมูลที่ต้องการ
WEBSERVICE ใช้กับ API ที่ต้องใส่ API key ในแบบ Authorization header ได้ไหม?+
ไม่ได้ WEBSERVICE ส่งได้แค่ GET request ธรรมดา ไม่มีทางแนบ HTTP header หรือ token แบบ Bearer/OAuth เลย ใช้ได้เฉพาะ API ที่ไม่ต้อง authentication หรือ API ที่ยอมให้ใส่ key เป็นพารามิเตอร์ใน URL ตรงๆ (เช่น …?apikey=XXXX) ถ้า API บังคับใช้ header เท่านั้น ต้องเปลี่ยนไปใช้ Power Query หรือ VBA แทน
Resources & Related
ฟังก์ชันที่ผู้เขียนโยงไว้กับ WEBSERVICE จัดกลุ่มตามหมวด · ชี้ที่ชื่อใดชื่อหนึ่ง แล้วฟังก์ชันที่ใช้คู่กันจะมีจุดทอง
ENCODEURL แปลงข้อความธรรมชาติเป็น URL-encoded string โดยแทนที่อักขระพิเศษ (เช่น ช่องว่าง สัญลักษณ์พิเศษ) ด้วยรหัสเลขฐานสิบหก ทำให้ข้อความปลอดภัยสำหรับใช้ในการขอ URL โดยมักใช้ร่วมกับ WEBSERVICE และ FILTERXML ในการค้นหาข้อมูลจากเว็บ API
FILTERXML ดึงข้อมูลเฉพาะส่วนจาก XML content โดยใช้ XPath expression เพื่อระบุตำแหน่งของข้อมูลที่ต้องการ ทำให้ง่ายในการแยกข้อมูลที่เป็นประโยชน์จากผลลัพธ์ XML ที่ส่งกลับมาจาก WEBSERVICE มักใช้ร่วมกับ WEBSERVICE และ ENCODEURL เพื่อดึงข้อมูลจาก web API
IFERROR ช่วยดักจับ Error ทุกประเภทที่เกิดขึ้นในสูตร (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, #NULL!) แล้วเปลี่ยนเป็นค่าที่เราต้องการแทน
.
ที่เจ๋งคือมันช่วยให้รายงานและ Dashboard ดูสะอาด ไม่มี Error แสดงให้ผู้ใช้งานเห็น โดยถ้าสูตรไม่มี Error ก็จะ return ผลลัพธ์ปกติ
.
ส่วนตัวผมคิดว่าฟังก์ชันนี้เป็น "ตัวช่วยมหาเทพ" สำหรับคนทำรายงานเลยครับ 😎
TEXTSPLIT เป็นฟังก์ชัน Dynamic Array ที่ช่วยแยกข้อความในเซลล์ออกเป็นอาร์เรย์ของค่า (Spill) ตามตัวคั่นที่ระบุ สามารถแยกข้อมูลออกไปทางขวา (คอลัมน์) หรือลงด้านล่าง (แถว) หรือทั้งสองอย่างพร้อมกัน เหมาะสำหรับการจัดการข้อมูลนำเข้าที่รวมกันอยู่ในเซลล์เดียว
ยังไม่มีบทความที่เกี่ยวข้องกับฟังก์ชันนี้
Comments
อีเมลของคุณจะไม่ถูกเผยแพร่