تابع FILTERXML در اکسل — معرفی و راهنمای کاربردی

تابع FILTERXML یکی از توابع مفید اکسل برای استخراج داده‌ها از رشته‌های XML با استفاده از عبارات XPath است. این تابع مخصوص مواقعی است که داده‌ها به‌صورت XML درون یک سلول یا خروجی سرویس‌های وب قرار دارند و شما می‌خواهید گره‌ها یا مقدارهای مشخصی را بیرون بکشید و به جدول یا آرایه تبدیل کنید.

ساختار و پارامترها

خروجی تابع یک آرایه است؛ در نسخه‌های جدید اکسل (Office 365 / Excel 2019+) این آرایه به‌صورت spill نمایش داده می‌شود. در نسخه‌های قدیمی‌تر خروجی ممکن است نیاز به وارد کردن فرمول به‌عنوان فرمول آرایه (Ctrl+Shift+Enter) داشته باشد.

مثال پایه — استخراج عناوین

=FILTERXML(A1, "//book/title")

فرض کنید در سلول A1 یک سند XML مثل زیر دارید:

<library>
  <book id="b1"><title>Excel Tips</title><author>Ali</author></book>
  <book id="b2"><title>Advanced XPath</title><author>Sara</author></book>
</library>

فرمول بالا همه عنوان‌های کتاب (<title>) را برمی‌گرداند و در سلول جاری و سلول‌های زیر آن نمایش داده می‌شود. اگر هیچ گره‌ای مطابق XPath پیدا نشود، معمولا مقدار خطا #N/A برمی‌گردد؛ و اگر XML نامعتبر باشد، معمولا #VALUE! نمایش داده می‌شود.

استخراج مقدار صفت (Attribute)

=FILTERXML(A1, "//book/@id")

این فرمول مقدار صفت id از هر گره <book> را بازمی‌گرداند (مثل b1، b2). برای دسترسی به صفت‌ها از نماد @ در XPath استفاده می‌شود.

ترکیب با WEBSERVICE برای واکشی و تحلیل داده‌ی آنلاین

اگر منبع XML از یک API یا آدرس وب باشد، می‌توانید WEBSERVICE را با FILTERXML ترکیب کنید:

=FILTERXML(WEBSERVICE("https://example.com/api/books.xml"), "//book/title")

این ترکیب ابتدا محتوای XML را از آدرس وب می‌گیرد و سپس عناوین را استخراج می‌کند. باید توجه داشته باشید که WEBSERVICE در برخی از نسخه‌ها (مثلاً برخی نسخه‌های مک یا اکسل آنلاین) محدود یا غیر فعال باشد و همچنین ممکن است نیاز به دسترسی اینترنت یا تنظیمات پروکسی داشته باشید.

نکات پیشرفته و نکات فنی (Expert tips)

• نام‌فضاها (Namespaces): FILTERXML به‌صورت پیش‌فرض با نام‌فضاهای XML مشکل دارد. اگر XML شما از namespace استفاده می‌کند، ساده‌ترین راه استفاده از تابع local-name() در XPath است. مثال: //*[local-name()='book']/*[local-name()='title'].
• محدودیت طول رشته: در برخی نسخه‌ها محدودیت کاراکتر برای ورودی توابع وجود دارد؛ اگر XML خیلی بزرگ است ممکن است به خطا بخورید یا نیاز به پیش‌پردازش داشته باشید.
• خروجی آرایه‌ای: در اکسل‌های جدید خروجی به‌صورت spill می‌شود. اگر می‌خواهید نتایج را در یک ستون داشته باشید، کافی است فرمول را در یک سلول وارد کنید و خروجی اتوماتیکاً پخش می‌شود.
• خطاها: XML نامعتبر -> #VALUE!؛ XPath بدون نتیجه -> #N/A؛ گاهی WEBSERVICE خطاهای شبکه‌ای برمی‌گرداند که باید مدیریت شوند.

نمونه کاربرد واقعی — تبدیل خروجی API به جدول

فرض کنید یک API باز اطلاعات کتاب‌ها را به‌صورت XML بازمی‌گرداند. شما می‌خواهید عنوان و نویسنده را در دو ستون نمایش دهید.

=FILTERXML(WEBSERVICE("https://example.com/api/books.xml"), "//book/title")
=FILTERXML(WEBSERVICE("https://example.com/api/books.xml"), "//book/author")

می‌توانید دو فرمول بالا را در ستون‌های جداگانه وارد کنید تا عناوین و نویسندگان به‌صورت دو ستون هم‌ردیف استخراج شوند. برای کارایی بهتر بهتر است خروجی WEBSERVICE را در یک سلول ذخیره کنید سپس از آن سلول برای چندین فراخوانی FILTERXML استفاده کنید تا چند بار درخواست HTTP ارسال نشود.

کار با نام‌فضاها — نمونه عملی

<ns:library xmlns:ns="http://example.com/ns">
  <ns:book><ns:title>XML Tricks</ns:title></ns:book>
</ns:library>

=FILTERXML(A1, "//*[local-name()='book']/*[local-name()='title']")

در این مثال از local-name() استفاده شده تا با پیشوندهای نام‌فضا کار کند؛ این روش زمانی کاربرد دارد که نتوانید یا نخواهید پیشوندها را در XPath معرفی کنید.

اشکال‌زدایی و رفع خطاها

• ابتدا مطمئن شوید XML در سلول به‌صورت صحیح و well-formed قرار دارد (باز و بسته شدن تگ‌ها، کاراکترهای ویژه‌ای مثل & باید به صورت &amp; باشند).
• برای بررسی سریع‌تر می‌توانید محتوای XML را در یک ویرایشگر XML یا مرورگر باز کنید تا از صحت آن مطمئن شوید.
• اگر خروجی خالی است، XPath را در ابزارهای آنلاین تست کنید یا از عبارات عمومی‌تر (مثلاً //*) برای بررسی وجود گره‌ها استفاده کنید.

مقایسه با روش‌های دیگر

برای داده‌های JSON می‌توانید از تابع FILTERJSON (در صورت وجود) یا Power Query استفاده کنید. برای XML بزرگ یا پیچیده، Power Query معمولاً انعطاف‌پذیری بیشتری دارد و می‌تواند بهتر با نام‌فضاها، تبدیل‌ها و بارگذاری به جدول کار کند. FILTERXML مناسب سناریوهای سبک، فرمول‌محور و زمانی است که می‌خواهید سریع داخل شیت اکسل پردازش انجام دهید.

جمع‌بندی و توصیه‌ها

• از FILTERXML برای استخراج سریع گره‌ها و صفت‌ها از XML استفاده کنید.
• برای اسناد بزرگ یا پیچیده از Power Query یا پردازش بیرونی استفاده کنید.
• اگر با نام‌فضاها روبه‌رو شدید، local-name() یا Power Query را در نظر بگیرید.
• همیشه ابتدا صحت XML را بررسی کنید و در صورت استفاده از WEBSERVICE، پاسخ‌ها را کش کنید تا از درخواست‌های تکراری جلوگیری شود.

با آگاهی از این نکات و مثال‌ها می‌توانید تابع FILTERXML را به شکل مؤثر در پروژه‌های اکسل خود به‌کار بگیرید و داده‌های XML را به سرعت به جداول تحلیل‌پذیر تبدیل کنید.

لطفاً از کمبود ها و مشکلات این محتوا برای ما بنویسید

ارسال