تابع TEXTJOIN در اکسل – ترکیب حرفه ای متن ها در اکسل با مثال کاربردی

تابع TEXTJOIN یکی از توابع متنی اکسل است که برای ترکیب حرفه‌ای عبارت‌های متنی چند سلول همراه با یک جداکننده دلخواه مانند کاما یا خط فاصله استفاده می‌شود. این تابع در نسخه‌های جدید ۲۰۱۹، ۲۰۲۱ اکسل و آفیس ۳۶۵ به این نرم‌افزار اضافه شده است و جایگزین تابع CONCAT به‌حساب می‌آید. تفاوت مهم تابع TEXTJOIN نسبت به تابع CONCAT امکان استفاده از علامت‌های جداکننده و تصمیم‌گیری در مورد حذف سلول‌های خالی در آن است. به این شکل گزارش‌های تهیه شده در اکسل خواناتر و منظم‌تر می‌شوند. در این مطلب از مجله فرادرس فرمول‌نویسی ساده و پیشرفته با تابع TEXTJOIN در اکسل را همراه چند مثال مختلف یاد می‌گیریم.

فرمول کلی تابع TEXTJOIN در اکسل

فرمول کلی تابع TEXTJOIN در اکسل به شکل زیر است:

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

در این فرمول هر یک از آرگومان‌ها تعریف‌های زیر را دارند.

• آرگومان delimiter (اجباری): جداکننده‌های دلخواه مانند کاما یا خط فاصله که برای قرارگیری بین دو عبارت متنی تعریف می‌کنیم.
• آرگومان ignore_empty (اجباری): عبارتی که مشخص می‌کند آیا سلول‌های خالی در ترکیب دو عبارت نادیده گرفته شوند یا خیر. نوشتن عبارت «TRUE» نشان‌دهنده نادیده گرفتن سلول‌های خالی و عبارت «FALSE» نشانه در نظر گرفتن آن‌ها است.
• آرگومان text1 (اجباری): اولین عبارت متنی برای ترکیب کردن است که می‌تواند ارجاع به یک سلول یا یک محدوده سلول نیز باشد.
• آرگومان text2 (اختیاری): سایر عبارت‌های متنی که با اولین عبارت ادغام می‌شوند. در این قسمت حداکثر ۲۵۲ آرگومان قابل تعریف است.

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

مثال‌ فرمول‌نویسی ساده با تابع TEXTJOIN در اکسل

برای درک بهتر نحوه کار با تابع TEXTJOIN چند مثال مختلف را در این بخش توضیح می‌دهیم. این موارد را با توجه به کاربردهای ساده تا پیشرفته تابع برای ترکیب عبارت‌های متنی دسته‌بندی می‌کنیم. بخش اول مثال‌های کاربردی، فرمول‌نویسی‌های ساده زیر با تابع TEXTJOIN بدون استفاده از توابع دیگر اکسل است.

1. ترکیب متن‌های قرار گرفته در یک ستون
2. ترکیب متن با علامت‌های مختلف
3. ترکیب متن با تاریخ میلادی و شمسی
4. ترکیب متن در دو خط جداگانه

در ادامه بحث هر یک از این موارد را توضیح می‌دهیم.

فیلم آموزش اکسل – ابزارهای کاربردی Excel + گواهینامه در فرادرس

کلیک کنید

مثال ۱: ترکیب متن‌های قرار گرفته در ستون

در جدول فرضی زیر نتیجه بازی چهار تیم را داریم.

برای تحلیل بهتر می‌خواهیم نتایج هر تیم را در یک ردیف کنار یکدیگر قرار دهیم. در ادغام این نتایج از علامت اِسلش همراه با یک فاصله به شکل « /» استفاده می‌کنیم. فرمول نمونه برای «تیم ۱» را به دو صورت زیر می‌توانیم بنویسم.

• نوشتن نام تمام سلول‌ها در فرمول

=TEXTJOIN("/ ", FALSE,B2,B3,B4,B5,B6)

• نوشتن محدوده سلول

=TEXTJOIN("/ ", FALSE,B2:B6)

حالت اول برای تعداد سلول‌های کم کاربرد دارد. معمولا برای ترکیب بیشتر از دو سلول از حالت دوم استفاده می‌کنیم تا فرمول ساده‌تر شود. در این مثال نیز حالت دوم را در نظر می‌گیریم.

در این فرمول به‌جای آرگومان عبارت «FALSE» را می‌نویسیم تا بازی‌های انجام نشده (سلول‌های خالی جدول) را نیز در نظر بگیریم. بعد از کپی کردن فرمول در سلول‌های دیگر نتیجه به شکل جدول زیر درمی‌آید.

همان‌طور که مشخص است از آنجا که سلول‌های خالی هم درر نظر گرفته شده‌اند، خروجی این سلول‌ها با یک فاصله نشان داده می‌شود.

مثال ۲: ترکیب متن با علامت‌های مختلف

برخی مواقع برای متمایز کردن متن‌ها می‌خواهیم هر عبارت را با یک علامت از هم جدا کنیم. دو روش زیر برای این کار وجود دارد.

• نوشتن همه علامت‌ها به شکل یک ثابت آرایه‌ای در فرمول
• نوشتن هر یک از علامت‌ها در سلول‌‌های دلخواه از جدول و ارجاع به آن در فرمول

فرض می‌کنیم در جدول زیر نام خانوادگی، نام اصلی و نام میانی چند نفر را داریم.

حال می‌خواهیم ترکیب آن‌ها به شکلی باشد که نام خانوادگی و نام اصلی را با علامت کاما و فاصله یعنی (", ") و نام اصلی و نام میانی با یک فاصله یعنی (" ") در هر ردیف بنویسیم.

در روش اول همه علامت‌ها را در یک ثابت آرایه‌ای به شکل {", "," "} تعریف می‌کنیم و فرمول زیر را می‌نویسیم.

=TEXTJOIN({", "," "}, TRUE, A2:C2)

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

=TEXTJOIN($B$9:$C$9, TRUE, A2:C2)

در این حالت لازم است علامت‌ها را کاملا به همان شکلی که تعریف کرده‌ایم در سلول‌های B9 و C9 بنویسیم، حتی اگر علامت فاصله مشخص نباشد. برای این کار علامت «$» را برای تعریف فرمول جدید در هر ردیف بدون تغییر جداکننده‌ها استفاده می‌کنیم که به آن ارجاع مطلق می‌گوییم.

در مطلب زیر از مجله فرادرس دو روش آدرس‌دهی سلول‌ها در فرمول اکسل را توضیح داده‌ایم.

مثال ۳: ترکیب متن با تاریخ میلادی

در حالت معمولی فرمول TEXTJOIN برای ترکیب متن با تاریخ میلادی درست عمل نمی‌کند. زیرا اکسل تاریخ‌های میلادی را به‌عنوان یک عدد سریالی در نظر می‌گیرد و نتیجه ادغام سلول‌ها چند عدد پشت سر هم است.

به‌عنوان مثال در جدول زیر اسامی و تاریخ تولد میلادی چند نفر را داریم. اگر فرمول معمولی TEXTJOIN را استفاده کنیم، نتیجه به شکل زیر درمی‌آید.

برای رفع این مشکل لازم است تاریخ تولد را قبل از ترکیب سلول مانند زیر به شکل یک رشته متنی داخل فرمول تعریف کنیم.

=TEXTJOIN(" ", TRUE, A2, TEXT(B2, "yyy/mm/dd"))

در این حالت نتیجه مانند تصویر زیر خواهد بود.

اما برای تاریخ‌های شمسی این مشکل وجود ندارد. زیرا اکسل این تاریخ را به صورت یک رشته کاراکتر ذخیره می‌کند. بنابراین در مورد تاریخ‌های شمسی نیازی به تغییر فرمول نداریم و همان حالت معمولی تابع ‌TEXTJOIN نتیجه درست را می‌دهد.

برای آشنایی بیشتر با تغییر فرمت سلول‌های اکسل و رفع اشکالات احتمالی نکات تکمیلی را در مطلب زیر توضیح داده‌ایم.

مثال ۴: ترکیب متن در دو خط جداگانه داخل سلول

اگر بخواهیم بعد از ترکیب متن‌ها هر عبارت سلول در یک خط جداگانه قرار گیرد، به‌جای آرگومان delimiter  عبارت «CHAR(10)» را می‌نویسیم. این عبارت یکی از کدهای مخفی اکسل است که عدد «۱۰» در آن به جدا کردن دو خط اشاره می‌کند.

به عنوان مثال در جدول زیر که اسامی افراد و سمت شغلی آن‌ها قرار دارد، فرمول زیر را برای ردیف اول می‌نویسیم تا نتیجه در دو خط نشان داده شود.

=TEXTJOIN(CHAR(10), TRUE, A2:B2)

یادگیری فرمول‌نویسی با توابع پرکاربرد اکسل همراه با فرادرس

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

پیشنهاد اول در مسیر یادگیری بهتر توابع اکسل فیلم‌های منتخب آموزشی زیر است.

• فیلم آموزش استفاده از توابع و فرمول‌نویسی در اکسل همراه گواهینامه در فرادرس
• فیلم آموزش ابزارهای کاربردی اکسل همراه گواهینامه در اکسل
• فیلم آموزش ترفندهای کاربردی اکسل همراه گواهینامه در فرادرس
• فیلم آموزش رایگان ساخت فرمول‌های پیچیده با توابع مختلف در فرادرس

همچنین در دو مجموعه فیلم آموزشی زیر امکان انتخاب موارد با توجه به نیاز در سطح مقدماتی تا پیشرفته وجود دارد.

• مجموعه فیلم آموزش توابع و فرمول‌ در اکسل در فرادرس
• مجموعه فیلم آموزش اکسل برای کسب و کار در فرادرس

مثال فرمول‌نویسی ترکیبی با تابع TEXTJOIN در اکسل

با ترکیب تابع TEXTJOIN و سایر توابع اکسل کارهای جالب دیگری را می‌توانیم انجام دهیم. این نوع فرمول‌نویسی در دسته مهارت پیشرفته اکسل است و نیاز به آشنایی کامل با دیگر توابع اکسل دارد.

فیلم پرکاربردترین توابع اکسل چیست؟ + معرفی و نحوه استفاده (فیلم آموزش رایگان) در فرادرس

کلیک کنید

در این بخش دو ترکیب پرکاربرد زیر را با تابع TEXTJOIN همراه مثال توضیح می‌دهیم.

1. ترکیب با توابع شرطی اکسل
2. ترکیب با تابع UNIQUE

ترکیب با توابع شرطی اکسل

اگر بخواهیم سلول‌ها را فقط در زمان برقراری شرط خاص با یکدیگر ترکیب کنیم، از تابع TEXTJOIN و توابع شرطی اکسل استفاده می‌کنیم.

مثال ۱. ترکیب حرفه‌ای متن با تعریف یک شرط

جدولی فرضی از اعضای دو تیم مختلف داریم که با شماره‌های «۱» و «۲» مشخص هستند.

حال می‌خواهیم اعضای هر تیم را در یک ردیف به تفکیک مشخص کنیم. برای این کار از تابع IF به‌جای آرگومان text استفاده می‌کنیم.

بنابراین برای مشخص کردن اعضای «تیم ۱» فرمول زیر را می‌نویسیم.

=TEXTJOIN(", ", TRUE, IF($B$2:$B$6=1, $A$2:$A$6, ""))

در این فرمول اگر تابع IF تشخیص دهد که اعداد قرار گرفته در ستون B برابر «۱» است، عبارت نوشته شده در سلول را برمی‌گرداند. در غیر این‌صورت یک رشته خالی را به‌عنوان خروجی برمی‌گرداند. سپس تابع TEXTJOIN هر خروجی را با علامت کاما داخل سلول E می‌نویسد.

به‌همین ترتیب فرمول زیر را برای اعضای «تیم ۲» می‌نویسیم.

=TEXTJOIN(", ", TRUE, IF($B$2:$B$6=2, $A$2:$A$6, ""))

نتیجه نهایی به شکل تصویر زیر درمی‌آید.

مثال ۲. ترکیب حرفه‌ای متن با تعریف چند شرط

برای تعریف چند شرط در فرمول تابع TEXTJOIN از عملگر ستاره * بین هر شرط از تابع IF استفاده می‌کنیم. به‌عنوان مثال در جدول زیر فهرستی از فروشندگان محصولات یک شرکت بر حسب استان محل فعالیت داریم. برخی فروشندگان در چند استان فعالیت می‌کنند. می‌خواهیم محصولات یک فروشنده خاص به‌نام «محمد نعیمی» را در استان‌های محل فعالیت خود فهرست کنیم.

بنابراین نیاز به تعریف دو شرط برای تابع IF داریم. یک شرط جستجوی نام نماینده و شرط دیگر استان محل فعالیت است. بر این اساس فرمول زیر را برای اولین نام «محمد نعیمی» در جدول «نتیجه» می‌نویسیم.

=TEXTJOIN(", ", TRUE, IF(($A$2:$A$11=E2)*($B$2:$B$11=F2), $C$2:$C$11, ""))

در این فرمول ابتدا تابع IF در ستون A2:A11 برقراری تطابق نام فروشنده نوشته شده در سلول E2 و در ستون B2:B11 تطابق نام استان نوشته شده در سلول F2 را بررسی می‌کند. عملگر * مانند AND عمل می‌کند و در صورت برقرار بودن هر دو شرط، خروجی را به تابع TEXTJOIN منتقل می‌کند. در نتیجه تابع TEXTJOIN خروجی‌های دریافتی را با علامت کاما ترکیب می‌کند و نتیجه نهایی را در جدول نمایش می‌دهد.

بعد از کپی کردن فرمول در سلول‌های دیگر، نتیجه نهایی به شکل زیر درمی‌آید.

ترکیب متن و حذف عبارت‌های تکراری با تابع UNIQUE

در مواقعی که می‌خواهیم بعد از ترکیب متن چند سلول، فقط عبارت‌های غیرتکراری به‌عنوان نتیجه نشان داده شوند از تابع TEXTJOIN همراه تابع UNIQUE استفاده می‌کنیم. به‌عنوان مثال در جدول زیر اسامی کارمندان یک شرکت و واحد محل فعالیت آن‌ها را داریم.

حال می‌خواهیم در گزارش خود به رئیس شرکت، یک تیتر برای این جدول بنویسیم که شامل اسامی واحدها در کنار یکدیگر باشد. همچنین برای خوانایی بهتر متن، این اسامی با علامت کاما از یکدیگر جدا می‌شوند.

در این جدول اسامی واحد محل فعالیت برای بعضی افراد مشترک است. اگر با فرمول معمولی تابع TEXTJOIN بخواهیم نام این واحدها را کنار هم قرار دهیم، خروجی شامل عبارت‌های تکراری بسیاری خواهد بود. بنابراین با استفاده از تابع UNIQUE ابتدا اسامی غیرتکراری را از ستون جدا می‌کنیم. سپس با تابع TEXTJOIN آن‌ها را همراه علامت کاما کنار هم قرار می‌دهیم. برای این کار فرمول زیر را می‌نویسیم.

=TEXTJOIN(", ", TRUE, UNIQUE(B3:B8))

نتیجه نهایی نیز به شکل تصویر زیر درمی‌آید.

پیام‌های خطا در فرمول‌نویسی با تابع TEXTJOIN

در صورت فرمول‌نویسی صحیح معمولا مشکلی هنگام کار با تابع TEXTJOIN نداریم. اما اگر در فرمول‌نویسی با تابع TEXTJOIN به پیام خطا برخورد کنیم، یکی از موارد زیر ممکن است دلیل آن باشد.

فیلم آموزش استفاده از توابع و فرمول نویسی در اکسل Excel + گواهینامه در فرادرس

کلیک کنید

1. پیام خطای #NAME? زمانی اتفاق می‌افتد که از تابع TEXTJOIN در نسخه‌های قدیمی اکسل استفاده کنیم که این تابع در آن‌ها تعریف نشده است.
2. پیام خطای #VALUE!  زمانی اتفاق می‌افتد که عبارت متنی ترکیبی خروجی فرمول از ۳۲۷۶۷ کاراکتر بیشتر شود. همچنین، اگر اکسل علامت جداکننده را به‌عنوان متن قابل تعریف تشخیص ندهد، با این پیام روبرو می‌شویم. رایج‌ترین حالت نوشتن برخی کدها مانند «CHAR(0)» در فرمول است.

البته در اکسل ابزاری برای بررسی فرمول اشتباه نیز وجود دارد که برای یادگیری آن پیشنهاد می‌کنیم فیلم آموزش رایگان بررسی و تصحیح فرمول‌های اشتباه با Evaluate Formula در اکسل در فرادرس را مشاهده کنید.

جمع‌بندی پایانی

از تابع TEXTJOIN برای ترکیب خودکار دو متن در اکسل استفاده می‌کنیم. با این کار علاوه بر خواندن ساده‌تر متن، زمان کمتری را برای ادغام دو متن به‌صورت دستی صرف می‌کنیم. در این مطلب از مجله فرادرس دو مدل فرمول‌نویسی ساده و ترکیبی با تابع TEXTJOIN در اکسل را همراه مثال یاد گرفتیم. هر چند کار با این تابع ساده است، با این حال برای یادگیری نکات تکمیلی در سطح پیشرفته علاقه‌مندان می‌توانند مجموعه فیلم آموزش توابع و فرمول‌ در اکسل در فرادرس را مشاهده کنند.