في الآونة الأخيرة ، ناقشنا استخدام وظيفة FILTER.XML لاستيراد بيانات XML من الإنترنت - المهمة الرئيسية التي تهدف هذه الوظيفة ، في الواقع ، من أجلها. على طول الطريق ، ظهر استخدام آخر غير متوقع وجميل لهذه الوظيفة - لتقسيم النص اللاصق بسرعة إلى خلايا.
لنفترض أن لدينا عمود بيانات مثل هذا:
بالطبع ، للراحة ، أود تقسيمها إلى أعمدة منفصلة: اسم الشركة ، والمدينة ، والشارع ، والمنزل. يمكنك القيام بذلك بعدة طرق مختلفة:
- استعمل نص حسب الأعمدة من علامة التبويب البيانات (البيانات - نص إلى أعمدة) وتذهب ثلاث خطوات محلل النص. ولكن إذا تغيرت البيانات غدًا ، فسيتعين عليك تكرار العملية بأكملها مرة أخرى.
- قم بتحميل هذه البيانات في Power Query وقسمها هناك ، ثم قم بتحميلها مرة أخرى إلى الورقة ، ثم قم بتحديث الاستعلام عندما تتغير البيانات (وهو الأمر الأسهل بالفعل).
- إذا كنت بحاجة إلى التحديث سريعًا ، فيمكنك كتابة بعض الصيغ المعقدة جدًا للعثور على الفواصل واستخراج النص بينها.
ويمكنك القيام بذلك بشكل أكثر أناقة واستخدام وظيفة FILTER.XML ، ولكن ما علاقتها بها؟
تستقبل وظيفة FILTER.XML كوسيطة أولية رمز XML - نص مرمز بعلامات وسمات خاصة ، ثم يوزعه إلى مكوناته ، ويستخرج أجزاء البيانات التي نحتاجها. عادةً ما يبدو رمز XML مشابهًا لما يلي:
في XML ، يجب تضمين كل عنصر بيانات في علامات. العلامة عبارة عن نص (في المثال أعلاه هو مدير ، اسم ، ربح) محاط بأقواس زاوية. تأتي العلامات دائمًا في أزواج - الفتح والإغلاق (مع إضافة شرطة مائلة إلى البداية).
يمكن لوظيفة FILTER.XML استخراج محتويات جميع العلامات التي نحتاجها بسهولة ، على سبيل المثال ، أسماء جميع المديرين ، و (الأهم من ذلك) عرضها جميعًا مرة واحدة في قائمة واحدة. لذا فإن مهمتنا هي إضافة علامات إلى النص المصدر ، وتحويله إلى كود XML مناسب للتحليل اللاحق بواسطة وظيفة FILTER.XML.
إذا أخذنا العنوان الأول من قائمتنا كمثال ، فسنحتاج إلى تحويله إلى هذا البناء:
اتصلت بعلامة الفتح والإغلاق العالمية لكل النص t، والعلامات التي تؤطر كل عنصر هي s. ، ولكن يمكنك استخدام أي تسميات أخرى - لا يهم.
إذا أزلنا المسافات البادئة وفواصل الأسطر من هذا الرمز - بالمناسبة ، اختياريًا وأضفناها فقط من أجل الوضوح ، فسيتحول كل هذا إلى سطر:
ويمكن بالفعل الحصول عليه بسهولة نسبيًا من عنوان المصدر عن طريق استبدال الفاصلات الموجودة فيه ببضع علامات باستخدام الوظيفة استبدل (بديل) ولصقها بالرمز & في بداية ونهاية علامتي الافتتاح والختام:
لتوسيع النطاق الناتج أفقيًا ، نستخدم الوظيفة القياسية ترانسب (تبديل موضع)، نلف صيغتنا فيه:
من الميزات المهمة لهذا التصميم بالكامل أنه في الإصدار الجديد من Office 2021 و Office 365 مع دعم المصفوفات الديناميكية ، لا توجد إيماءات خاصة مطلوبة للإدخال - فقط أدخل وانقر فوق أدخل - الصيغة نفسها تشغل عدد الخلايا التي تحتاجها وكل شيء يعمل بشكل مذهل. في الإصدارات السابقة ، حيث لم تكن هناك مصفوفات ديناميكية حتى الآن ، ستحتاج أولاً إلى تحديد عدد كافٍ من الخلايا الفارغة قبل إدخال الصيغة (يمكنك ذلك بهامش) ، وبعد إنشاء الصيغة ، اضغط على اختصار لوحة المفاتيح CTRL+تغير+أدخللإدخالها كصيغة صفيف.
يمكن استخدام خدعة مماثلة عند فصل النص الملتصق معًا في خلية واحدة من خلال فاصل سطر:
الاختلاف الوحيد مع المثال السابق هو أنه بدلاً من الفاصلة ، نقوم هنا باستبدال حرف فاصل السطر Alt + Enter غير المرئي ، والذي يمكن تحديده في الصيغة باستخدام وظيفة CHAR برمز 10.
- التفاصيل الدقيقة للعمل مع فواصل الأسطر (Alt + Enter) في Excel
- قسّم النص على أعمدة في Excel
- استبدال النص بـ SUBSTITUTE