تابع TEXTSPLIT در اکسل – توضیح به زبان ساده + مثال

مایکروسافت اکسل ابزاری قدرتمند برای تحلیل داده است و هر داده‌ای، ساده یا پیچیده، در اکسل مرتب و قابل فهم می‌شود. با استفاده از توابع اکسل می‌توان روی داده‌ها کنترل کامل داشت و با اعمال کمی خلاقیت در استفاده از آن‌ها، سرعت کار را به‌طرز چشم‌گیری افزایش داد. یکی از این توابع، تابع جدید تقسیم‌کننده «TEXTSPLIT» در اکسل است که محتوای سلول‌های متنی (Text) را در سطر و ستون تقسیم‌بندی می‌کند. در این مقاله از مجله فرادرس به معرفی این تابع خواهیم پرداخت. در ابتدای مطلب به کاربرد TEXTSPLIT و معرفی سینتکس و امکانات آن می‌پردازیم و در ادامه چندین مثال کاربردی تشریح می‌کنیم. در این مثال‌ها تقسیم‌‌‌‌‌‌‌‌‌‌بندی داده‌های های متنی موجود در یک سلول را در شرایط و جدول‌های گوناگون آموزش خواهیم داد.

کاربرد تابع TEXTSPLIT در اکسل

این تابع محتوای متنی موجود در یک سلول را در سلول های متعدد (ردیفی، ستونی یا هر دو) تقسیم می‌کند. این تقسیم‌بندی بر اساس کاراکترهایی جداکننده انجام می‌شود که کاربر آن‌ها تعریف می‌کند. بعضی از قابلیت‌های این تابع در قابلیت تبدیل متن به ستون در اکسل ممکن است، اما TEXTSPLIT امکانات بیشتری را در اختیار کاربران قرار می‌دهد. تابع TEXTSPLIT می‌تواند برای ایجاد آرایه‌های پیچیده در کنار باقی توابع اکسل استفاده شود. کاربرانی که در استفاده از از توابع پیشرفته اکسل حرفه‌ای هستند می‌توانند از TEXTSPLIT بهره زیادی ببرند.

سینتکس تابع TEXTSPLIT در اکسل

سینتکس کامل این تابع به شرح زیر است:

=TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty], [match_mode], [pad_with])

این سینتکس شامل ۶ آرگومان و پارامتر است.

• آرگومان اول text داده‌ای را مشخص می‌کند که قصد تقسیم محتوای آن را دارید. این داده می‌تواند در یک سلول یا گروهی از سلول‌ها قرار داشته باشد یا حتی متنی باشد که خودتان در دستور تابع وارد می‌کنید.
• آرگومان دوم col_delimiter کاراکترهای جداکننده را مشخص می‌کند، داده‌های قبل و بعد از این کاراکترها هر یک در ستون جدیدی درج خواهند شد.
• آرگومان سوم row_delimiter  نیز کاری مشابه آرگومان قبلی انجام می‌دهد، با این تفاوت که هر یک از داده‌های بین هر جداکننده، در ردیف جدیدی قرار خواهند گرفت.

کاراکترهای جداکننده می‌توانند نقطه، ویرگول، فاصله، اعداد یا حتی مجموعه‌ای از کاراکترهای متفاوت باشد. برای مشخص‌کردن بیش از یک گروه کاراکتر جداکننده نیاز است از آکلاد {} استفاده کنید:

=TEXTSPLIT("Sample text",{"e","t"})

به‌عنوان مثال در فرمول بالا از دو حرف e و t به عنوان جداکننده استفاده شده است.

• آرگومان چهارم ignore_empty در حالت TRUE مانع ایجاد سلول‌های خالی می‌شود. در حالت پیش‌فرضِ FALSE اگر بین کاراکترهای جداکننده داده‌ای وجود نداشته باشد و کاراکترها پشت هم تکرار شده باشند، تابع TEXTSPLIT سلول خالی ایجاد خواهد کرد.
• آرگومان پنجم، پارامتر match_mode  در حالت پیش‌فرض 0 ، کاراکترهای جداکننده را نسبت به بزرگ و کوچکی حروف حساس نگه می‌دارد. اگر این پارامتر روی 1 قرار بگیرد، حساسیت روی کوچکی و بزرگی حروف کاراکترهای جداکننده برداشته می‌شود. این پارامتر در عمل زمانی کاربرد دارد که کاراکترهای جداکننده شامل حروف و کلمات باشند.
• در آخر آرگومان ششم pad_with در سلول‌هایی که بعد از اجرای تابع مقداری به آنها تعلق نمی‌گیرد، مقدار دلخواه شما را قرار خواهد داد. در حالت پیش‌فرض در این سلول‌ها خطای #N/A نوشته می‌شود. اگر این خطا در تحلیل داده‌ها برای شما مشکل ایجاد می‌کند، می‌توانید مقداری دلخواه برای این آرگومان مشخص کنید.

تقسیم‌بندی کلماتی که با فاصله از هم جدا شده‌اند

در این مثال ساده، تک‌تک کلمات یک عبارت متنی در سلولی جداگانه قرار می‌دهیم:

• داده (سلول A2): Dakota Lennon Sanchez
• فرمول (سلول A5): =TEXTSPLIT(A2, " ")
• داده (سلول A3): To be or not to be
• فرمول (سلول A5): =TEXTSPLIT(A3, " ")

فیلم آموزش اکسل Microsoft Excel 2021 در فرادرس

کلیک کنید

نتیجه در زیر آورده شده است.

در مثال بالا در آرگومان ابتدایی فرمول‌ها (آرگومانِ text )، آدرس سلول داده‌های مدنظر ( A2 و A3  ) وارد شده و در آرگومان دوم ( col_delimiter  ) پارامتر جداکننده ردیفی، «یک فاصله " " » در نظر گرفته شده است. نتیجه را در ردیف ۵ و ۶ تصویر ملاحظه می‌کنید.

تقسیم‌بندی داده‌ها در یک جدول ۲ در ۳

در این مثال، داده‌های متوالی متنی موجود در یک سلول را در یک جدول ۲ در ۳ تقسیم‌بندی می‌کنیم:

• داده (سلول A2): 1,2,3;4,5,6
• فرمول (سلول A5): =TEXTSPLIT(A2,",",";")

نتیجه در زیر آورده شده است.

در مثال بالا، در آرگومان ابتداییِ فرمول (آرگومانِ text ) آدرس سلول داده‌های مدنظر A2  ، در آرگومان دوم ( col_delimiter  ) پارامتر جداکننده ردیفی "," و در در آرگومان سوم ( row_delimiter  ) پارامتر جداکننده ستونی ";" وارد شده است. نتیجه را در ردیف ۴ و ۵ تصویر ملاحظه می‌کنید.

تقسیم‌بندی با چند کاراکتر جداکننده و ایجاد سلول خالی

در این مثال از آرگومان چهارم تابع برای جلوگیری از ایجاد سلول خالی بعد از تقسیم‌بندی استفاده ‌می‌کنیم:

• داده (سلول A2): Do. Or do not. There is no try. -Anonymous
• فرمول (سلول A4): =TEXTSPLIT(A2,".")
• فرمول (سلول A5): =TEXTSPLIT(A2,{".","-"})
• فرمول (سلول A6): =TEXTSPLIT(A2,{".","-"},,FALSE)

نتیجه در زیر آورده شده است.

در این مثال در ردیف ۵ و ۶، برای آرگومان دوم مقادیر "."  و  "-" در نظر گرفته شده که باعث می‌شود دو جداکننده قبل از واژه Anonymous پشت هم قرار بگیرند و یک خانه خالی ایجاد شود. در فرمول خط ششم نیز برای آرگومان چهارم ignore_empty حالت پیش‌فرض FALSE را نوشته است. نوشتن یا ننوشتن مقادیر پیش‌فرض آرگومان‌ها در فرمول اکسل، تغییری در نتیجه ایجاد نمی‌کند.

خطای #N/A

در این مثال کلمات یک جمله را در چندین خانه و چندین سلول پخش می‌کنیم:

• داده (سلول A2): Do. Or do not. There is no try. -Anonymous
• فرمول (سلول A4): =TEXTSPLIT(A2," ",".",TRUE)

فیلم آموزش تحلیل داده های کسب و کار با اکسل Excel در فرادرس

کلیک کنید

نتیجه در زیر آورده شده است.

در مثال بالا، در آرگومان دوم داده‌ها با جداکننده ردیفی " " و در آرگومان سوم جداکننده ستونی "." در نظر گرفته شده است. طبق این تقسیم‌بندی، با توجه به نقطه‌ها عبارت موجود به چهار قسمتِ «Do»، «Or do not»، «There is no try» و «-Anonymous» تقسیم می‌شود که هر بخش در یک ردیف قرار می‌گیرد و هر کدام از این بخش‌ها با توجه به فاصله بین کلمات در ستون‌ها نیز تقسیم‌بندی می‌شوند. از جایی این تقسیم‌بندی ۴ در ۴ است، سلول‌های خالی ایجاد می‌شود. با توجه به مقدار TRUE در آرگومان چهارم سلول‌های خالی باید حذف می‌شوند، لذا خطای #NA در این سلول‌ها ایجاد شده است. برای پرکردن این خانه‌ها با مقادیر دلخواه، می‌توانید آرگومان پنجم تابع را پر کنید.

استفاده از تابع TRIM

در این مثال تابع TEXTSPLIT را با تابع دیگری ترکیب می‌کنیم:

• داده (سلول A1): PowerPoint, Excel, Word, Outlook
• فرمول (سلول A2): =TEXTSPLIT(A2, “,”)
• فرمول (سلول A3): =TRIM(TEXTSPLIT(A2, “,”) ) یا =TEXTSPLIT(A2, “, ”)

نتیجه در زیر آورده شده است.

در این مثال تابع TRIM با تابع TEXTSPLIT برای حذف فاصله قبل از داده استفاده شده است.

تهیه لیست اسامی و حذف القاب

در این مثال از مجله فرادرس فهرستی از اسامی می‌سازیم که به‌طور پشت سر هم در یک سلول نوشته شده‌اند.

• داده‌ها: ستون A
• فرمول (ستون ‌B): =TEXTSPLIT(A2,{ " ", "Mr. ", "Ms. " }, , TRUE)

نتیجه در زیر آورده شده است.

در این مثال کاراکتر های جدا کننده  " ", "Mr. ", "Ms. " در نظر گرفته شده اند. برای جلوگیری از ایجاد سلول خالی، آرگومان چهارم در مقدار TRUE  تنظیم شده است.

تهیه جدول دو ستونه

در این مثال، لیستی ساده و دو ستونه از داده‌های پشت سر هم در یک خانه درست می‌کنیم:

• داده‌: Leila=20000, Tom=50000, Kamil=60000, Anna=70000
• فرمول (ستون ‌B): =TEXTSPLIT(A2, "=", ", " )

نتیجه در زیر آورده شده است.

در این مثال از مجله فرادرس نیز مشابه مثال‌های قبلی، آرگومان دوم =TEXTSPLIT(A2, "=" نوشته شده که داده اصلی را به چهار ردیف تقسیم می‌کند و آرگومان دوم ", "  نوشته شده که هر یک از این چهار بخش را به دو ستون تقسیم می‌کند.