اخیراً، ما در مورد استفاده از تابع FILTER.XML برای وارد کردن داده های XML از اینترنت بحث کردیم - وظیفه اصلی که این تابع در واقع برای آن در نظر گرفته شده است. با این حال، در طول راه، یکی دیگر از استفاده های غیرمنتظره و زیبا از این تابع ظاهر شده است – برای تقسیم سریع متن چسبنده به سلول ها.
فرض کنید یک ستون داده مانند این داریم:
البته، برای راحتی، می خواهم آن را به ستون های جداگانه تقسیم کنم: نام شرکت، شهر، خیابان، خانه. شما می توانید این کار را به روش های مختلف انجام دهید:
- استفاده کنید متن به ستون از زبانه داده ها (داده - متن به ستون) و سه قدم برو تجزیه کننده متن. اما اگر فردا داده ها تغییر کند، باید کل فرآیند را دوباره تکرار کنید.
- این داده‌ها را در Power Query بارگیری کنید و آنها را در آنجا تقسیم کنید، و سپس آنها را دوباره در برگه آپلود کنید، و سپس زمانی که داده‌ها تغییر کردند، کوئری را به‌روزرسانی کنید (که از قبل آسان‌تر است).
- اگر نیاز به به‌روزرسانی دارید، می‌توانید چند فرمول بسیار پیچیده بنویسید تا کاما را پیدا کنید و متن بین آنها را استخراج کنید.
و شما می توانید آن را زیباتر انجام دهید و از تابع FILTER.XML استفاده کنید، اما چه ربطی به آن دارد؟
تابع FILTER.XML به عنوان آرگومان اولیه خود یک کد XML را دریافت می کند - متنی که با برچسب ها و ویژگی های خاص علامت گذاری شده است، و سپس آن را به اجزای خود تجزیه می کند و قطعات داده مورد نیاز ما را استخراج می کند. کد XML معمولاً چیزی شبیه به این است:
در XML، هر عنصر داده باید در تگ محصور شود. برچسب مقداری متن است (در مثال بالا مدیر، نام، سود است) که در پرانتزهای زاویه ای محصور شده است. برچسب ها همیشه به صورت جفت می آیند - باز و بسته می شوند (با یک اسلش اضافه شده به ابتدا).
تابع FILTER.XML به راحتی می تواند محتویات تمام برچسب های مورد نیاز ما را استخراج کند، برای مثال نام همه مدیران، و (مهمتر از همه) همه آنها را به یکباره در یک لیست نمایش دهد. بنابراین وظیفه ما اضافه کردن برچسب به متن منبع، تبدیل آن به کد XML مناسب برای تجزیه و تحلیل بعدی توسط تابع FILTER.XML است.
اگر اولین آدرس را از لیست خود به عنوان مثال در نظر بگیریم، باید آن را به این ساختار تبدیل کنیم:
همه تگ متن باز و بسته شدن را فراخوانی کردم t، و تگ های قاب بندی هر عنصر هستند s.، اما می توانید از هر نام دیگر استفاده کنید - مهم نیست.
اگر تورفتگی و شکست خط را از این کد حذف کنیم - به هر حال، به طور کامل، اختیاری است و فقط برای وضوح اضافه شده است، همه اینها به یک خط تبدیل می شود:
و در حال حاضر می توان نسبتاً به راحتی از آدرس منبع با جایگزین کردن کاما در آن با چند تگ به دست آورد. با استفاده از تابع جایگزین (جایگزین) و چسب زدن با نماد & در ابتدا و انتهای تگ های باز و بسته شدن:
برای گسترش دامنه حاصل به صورت افقی، از تابع استاندارد استفاده می کنیم TRANSP (انتقال)، فرمول خود را در آن قرار می دهیم:
یکی از ویژگی‌های مهم کل این طراحی این است که در نسخه جدید آفیس 2021 و آفیس 365 با پشتیبانی از آرایه‌های پویا، هیچ حرکت خاصی برای ورودی لازم نیست - کافیست وارد شده و روی وارد - خود فرمول تعداد سلول های مورد نیاز خود را اشغال می کند و همه چیز با صدای بلند کار می کند. در نسخه‌های قبلی که هنوز آرایه‌های پویا وجود نداشت، ابتدا باید تعداد کافی سلول خالی را قبل از وارد کردن فرمول انتخاب کنید (می‌توانید با یک حاشیه) و پس از ایجاد فرمول، میانبر صفحه کلید را فشار دهید. کلیدهای Ctrl+تغییر+واردتا آن را به عنوان فرمول آرایه وارد کنید.
ترفند مشابهی را می توان در هنگام جداسازی متن چسبیده به هم در یک سلول از طریق شکست خط استفاده کرد:
تنها تفاوتی که با مثال قبلی دارد این است که به جای کاما، در اینجا کاراکتر نامرئی Alt + Enter line break را جایگزین می کنیم که با استفاده از تابع CHAR با کد 10 می توان آن را در فرمول مشخص کرد.
- ظرافت های کار با شکست خط (Alt + Enter) در اکسل
- تقسیم متن بر ستون در اکسل
- جایگزینی متن با SUBSTITUTE