در این مطلب 5 مورد از مهمترین توابع متنی در اکسل و به اعتقاد من پرکاربرد ترین توابع در این زمینه توضیح داده خواهد شد. همچنین به شما امکان داده شده است که کاربرد این توابع را به صورت زنده و از همین صفحه، مشاهده کنید و نیازی به باز کردن صفحه اکسل نمی باشد.
چندین ماه است که با power query کار میکنم و یکی از دردسر های همیشگی من در این مدت، نام فرمول ها در power query است که با نام فرمول های موجود در محیط اصلی اکسل متفاوت است. به شخصه معتقد هستم که این مسئله یکی از نقاط ضعف اکسل و power query است و در نسخه های بعدی می بایست این عدم سازگاری حل شود. (توابع dax در power pivot هم نام با توابع موجود در اکسل هستند، پس چرا نباید در power query اینگونه باشد)
زمینه های مهم
قبل از اینکه کامل درگیر کار با power query شویم، توجه کنید که دو چیز وجود دارد که واقعا بین فرمول های اکسل و power query متفاوت است:
حساسیت به حروف بزرگ و کوچک:
در حالی که فرمول های اکسل نسبت به حروف بزرگ و کوچک حساس نیستند، ولی فرمول های power query در این زمینه حساس هستند. اگر در فرمول power query ابتدا از “Text.Start” سپس “TEXT.START” یا “text.start” استفاده شود، این فرمول کار نخواهد کرد.
Base 1 vs Base 0
فرمول های اکسل را بعنوان “Base 1” میشناسیم. به این معنی که شما شمارش را از 1 شروع میکنید. ولی در Power Query شمارش را از 0 شروع میکند و نه از 1 . برای مشاهده تأثیر این تفاوت، قسمت زیر را که به فرمول جایگزین تابع mid در اکسل پرداخته است، بررسی کنید.
فرمول های معادل اکسل در power query
ببینیم فرمول هایی که در آن مطلب اول به آن پرداخته شده است چه تفاوتی با فرمول معادل آنها در power query دارد:
Left
برای استخراج x کاراکتر از سمت چپ یک رشته، اساس text.start را جایگزین left میکنیم:
| ترکیب | مثال | نتیجه | |
| اکسل |
=LEFT(text,num_chars) |
LEFT(“Excel”,2) |
Ex |
| Power query |
=Text.Start(text,num_chars) |
=Text.Start(“Excel”,2) |
Ex |
می بینید که زیاد هم دشوار نیست. اگرچه من ترجیح میدهم که نام فرمول ها با هم یکسان باشد بهتر است.
Right
برای استخراج تعداد X کاراکتر از سمت راست یک رشته نیز از همان حالت استفاده میکنیم. فقط می بایست نام تابع را از right به text.end تغییر دهیم:
| Syntax | Example | Result | |
| Excel |
=RIGHT(text,num_chars) |
=RIGHT(“Excel”,2) |
el |
| Power Query |
=Text.End(text, num_chars) |
=Text.End(“Excel”,2) |
el |
احتمالاً حالا، کاملاً با این فرایند آشنا شده اید، فقط کافیست نام تابع را تغییر دهیم و بقیه کار شبیه به محیط اصلی اکسل است. درست است؟ اووووم نه.
Mid
این مورد کمی متفاوت است.ابتدا می بایست mid را با text.range جایگزین کنیم. تا اینجا هیچ مشکلی وجود ندارد. ولی، ببنید اگر از پارامترهای مشابه استفاده کنیم چه اتفاقی می افتد:
| Syntax | Example | Result | |
| Excel |
=MID(text,start,num_chars) |
=LEFT(“Excel”,2,2) |
xc |
| Power Query |
=Text.Range(text,start,num_chars) |
=Text.Range(“Excel”,2,2) |
ce |
نتیجه کمی متفاوت است. دلیل این موضوع ناشی از base1 و base0 است که قبلا به آن اشاره شد. در حالیکه تابع mid در اکسل حروف E را شماره 1 در نظر میگیرد و لی در اینجا و تابع text.range این حروف را شماره 0 در نظر میگیرد. در نتیجه وقتی به power query گفته میشود که از حروف شماره 2 شروع به برگرداندن کند، حروف شماره 2 ، حروف سوم و c میشود. همچنین پارامتر آخر نیز می بایست 2 باشد، تا دو کاراکتر استخراج شود.
Len
بدست آوردن طول رشته در power query کمی شهودی تر از نسخه موجود در اکسل آن است زیرا در اینجا نام تابع با کاری که انجام میدهد بسیار سازگار تر است. در اینجا به جای len می بایست از text.lenght استفاده کرد.
| Syntax | Example | Result | |
| Excel |
=LEN(text) |
=LEN(“Excel”) |
5 |
| Power Query |
=Text.Length(text) |
=Text.Length(“Excel”) |
5 |
توجه کنید که نتیجه، 5 کاراکتر را برمیگرداند. همانطور که انتظار داشتید. در نتیجه میتوانید مشاهده کنید که در این مورد هم اکسل و هم power query از base1 استفاده میکنند.
Find
در انتها، به تابع find می پردازیم. این مورد کمی گیج کننده است. در این مورد می بایست 3 مطلب را مورد توجه داشته باشیم:
- نام تابع از find به PositionOf تغییر کرده است
- پارامتر های متنی که میخواهیم پیدا کنیم و متنی که میخواهیم جستجو کنیم get flipped around
- نتیجه بر اساس base0 نشان داده میشود و نه base1
| Syntax | Example | Result | |
| Excel |
=FIND(find_text,within_text) |
=FIND(“xc”,“Excel”) |
2 |
| Power Query |
=Text.PositionOf(text, find_text) |
=Text.PositionOf(“Excel”,”xc”) |
1 |
بنابراین در زمان استفاده از find، ابتدا xc را قرار دادیم و سپس excel را ، ولی در مدل power query، کاملا برعکس است. حال به نتیجه ها دقت کنید. در اکسل x کاراکتر دوم در نظر گرفته میشود . در power query نیز به همین شکل است ولی از آنجایی شمارش از 0 شروع میشود، نتیجه 1 میشود.
چند نکته
با وجود اینکه مدت هاست از power query استفاده میکنم ولی در بعضی از مواقع می بایست برای یادآوری نام و ترکیب توابع در power query به مطالب و آموزش ها مراجعه کنم. قسمتی از این مشکل به دلیل نبود خاصیت autocomplete در power query است (مطمئن هستم که این ویژگی به مرور اضافه میشود) ولی قسمت دیگر این مشکل به دلیل این است که در زمان نوشتن فرمول به صورت غریزی فرمول های اکسل را مینوسیم و مشکل سوم این است که خاصیت base0 در مقابل base1 دائماً من را با مشکل مواجه میکند.
از آنجایی که این ابزار برای کمک به کاربران اکسل طراحی شده است، من از مدل نامگذاری توابع در این ابزار کمی ناخرسندم. و آرزو دارم که در ادامه توابع این ابزار شبیه به اکسل شود. شاید روزی تیم اکسل، شرایطی را فراهم کند که بتوانیم برای مثال از توابعی مثل in addition در این ابزار استفاده کنیم. برای مثال:
=Text.Right(text,characters)
یا
=Text.Mid(text,start,characters)
همچنین بهتر است شکل فرمول به شکلی تغییر یابد که یک پارامتر را برای کاراکتر شروع نیاز داشته باشد. به این شکل میتواند با انتظارات متخصصان اکسل به دلیل تجربه آنها در مورد تابع mid نیز همخوانی داشته باشد.
فرمول زیر را توجه کنید:
=Text.Find(find_text,within_text)
میتوانید مشاهده کنید که اگر فرمول ها، از همات ترتیب پارامتر موجود در توابع اصلی اکسل استفاده کنند، و از یک سیستم base1 استفاده شود،( به همان شکلی که در تابع find استفاده شده است) تا چه حد یادگیری آن آسانتر است
علارغم تمام پیشنهاداتی که ارائه شد می بایست توجه کنید که در حال حاضر فقط یک راه برای انجام کارها در این ابزار وجود دارد و شما نیز می بایست این شیوه را فراگیرید. من معتقد هستم که متخصصان اکسل اگر بتوانند با شیوه های مرسوم در اکسل دانش خود را منتقل کنند، کار بسیار راحت تری خواهند داشت.
در زمینه پاورکوئری میتوانید به آموزش زیر مراجعه کنید و از آموزش های رایگان در این زمینه استفاده کنید:
[/vc_column_text][/vc_column][/vc_row]