در ادامه آموزش گسترش اندیشه پویا:
تابع TEXTJOIN یکی ازتوابع متنی اکسلاست که برای ترکیب حرفهای عبارتهای متنی چند سلول همراه با یک جداکننده دلخواه مانند کاما یا خط فاصله استفاده میشود.این تابع در نسخههای جدید ۲۰۱۹، ۲۰۲۱ اکسل و آفیس ۳۶۵ به این نرمافزار اضافه شده است و جایگزین تابع CONCAT بهحساب میآید. تفاوت مهم تابع TEXTJOIN نسبت به تابع CONCAT امکان استفاده از علامتهای جداکننده و تصمیمگیری در مورد حذف سلولهای خالی در آن است. به این شکل گزارشهای تهیه شده در اکسل خواناتر و منظمتر میشوند. در این مطلب از مجله فرمولنویسیساده و پیشرفته با تابع TEXTJOIN در اکسل را همراه چند مثال مختلف یاد میگیریم.
- با فرمول کلی تابع TEXTJOIN در اکسل آشنا میشوید.فرمولنویسی ساده با تابع TEXTJOIN را همراه چهار مثال یاد میگیرید.فرمولنویسی شرطی برای ترکیب حرفهای متنها را میآموزید.فرمولنویسی ترکیب حرفهای متن با حذف عبارتهای تکراری را درک میکنید.با پیامهای خطای فرمولنویسی تابع TEXTJOIN در اکسل آشنا خواهید شد.
فرمول کلی تابع TEXTJOIN در اکسل
فرمول کلی تابع TEXTJOIN در اکسل به شکل زیر است:
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
در این فرمول هر یک از آرگومانها تعریفهای زیر را دارند.
- آرگومانdelimiter(اجباری):جداکنندههای دلخواه مانند کاما یا خط فاصله که برای قرارگیری بین دو عبارت متنی تعریف میکنیم.آرگومانignore_empty(اجباری):عبارتی که مشخص میکند آیا سلولهای خالی در ترکیب دو عبارت نادیده گرفته شوند یا خیر. نوشتن عبارت «TRUE» نشاندهنده نادیده گرفتن سلولهای خالی و عبارت «FALSE» نشانه در نظر گرفتن آنها است.آرگومانtext1(اجباری):اولین عبارت متنی برای ترکیب کردن است که میتواند ارجاع به یک سلول یا یک محدوده سلول نیز باشد.آرگومانtext2(اختیاری):سایر عبارتهای متنی که با اولین عبارت ادغام میشوند. در این قسمت حداکثر ۲۵۲ آرگومان قابل تعریف است.
با مشخص کردن دقیق هر یک از این آرگومانها بهراحتی میتوانیم از تابع TEXTJOIN استفاده کنیم. اما در فرمولنویسیهای پیشرفته مانند ترکیب سایر توابع با تابع TEXTJOIN نیاز به آشنایی با ترفندهای کار و افزایش مهارت در این خصوص داریم. بنابراین پیشنهاد میکنیم در کنار مطالعه ادامه مطلب، برای یادگیری تکمیلیفیلم آموزش استفاده از توابع و فرمولنویسی اکسلدر را مشاهده کنید.
مثال فرمولنویسی ساده با تابع TEXTJOIN در اکسل
برای درک بهتر نحوه کار با تابع TEXTJOIN چند مثال مختلف را در این بخش توضیح میدهیم. این موارد را با توجه به کاربردهای ساده تا پیشرفته تابع برای ترکیب عبارتهای متنی دستهبندی میکنیم. بخش اول مثالهای کاربردی، فرمولنویسیهای ساده زیر با تابع TEXTJOIN بدون استفاده از توابع دیگر اکسل است.
- ترکیب متنهای قرار گرفته در یک ستونترکیب متن با علامتهای مختلفترکیب متن با تاریخ میلادی و شمسیترکیب متن در دو خط جداگانه
در ادامه بحث هر یک از این موارد را توضیح میدهیم.
مثال ۱: ترکیب متنهای قرار گرفته در ستون
در جدول فرضی زیر نتیجه بازی چهار تیم را داریم.
برای تحلیل بهتر میخواهیم نتایج هر تیم را در یک ردیف کنار یکدیگر قرار دهیم. در ادغام این نتایج از علامت اِسلش همراه با یک فاصله به شکل « /» استفاده میکنیم. فرمول نمونه برای «تیم ۱» را به دو صورت زیر میتوانیم بنویسم.
- نوشتن نام تمام سلولها در فرمول
=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 همراه مثال توضیح میدهیم.
- ترکیب با توابع شرطی اکسلترکیب با تابع 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 به پیام خطا برخورد کنیم، یکی از موارد زیر ممکن است دلیل آن باشد.
- پیام خطای#NAME?زمانی اتفاق میافتد که از تابع TEXTJOIN در نسخههای قدیمی اکسل استفاده کنیم که این تابع در آنها تعریف نشده است.پیام خطای#VALUE!زمانی اتفاق میافتد که عبارت متنی ترکیبی خروجی فرمول از ۳۲۷۶۷ کاراکتر بیشتر شود. همچنین، اگر اکسل علامت جداکننده را بهعنوان متن قابل تعریف تشخیص ندهد، با این پیام روبرو میشویم. رایجترین حالت نوشتن برخی کدها مانند «CHAR(0)» در فرمول است.
البته در اکسل ابزاری برای بررسی فرمول اشتباه نیز وجود دارد که برای یادگیری آن پیشنهاد میکنیمفیلم آموزش رایگان بررسی و تصحیح فرمولهای اشتباه با Evaluate Formula در اکسلدر را مشاهده کنید.
از تابع TEXTJOIN برای ترکیب خودکار دو متن در اکسل استفاده میکنیم. با این کار علاوه بر خواندن سادهتر متن، زمان کمتری را برای ادغام دو متن بهصورت دستی صرف میکنیم. در این مطلب از مجله دو مدل فرمولنویسی ساده و ترکیبی با تابع TEXTJOIN در اکسل را همراه مثال یاد گرفتیم. هر چند کار با این تابع ساده است، با این حال برای یادگیری نکات تکمیلی در سطح پیشرفته علاقهمندان میتوانندمجموعه فیلم آموزش توابع و فرمول در اکسلدر را مشاهده کنند.
- مجموعه فیلم آموزش توابع و فرمول در اکسل – مقدماتی تا پیشرفتهفیلم آموزش اکسل – ابزارهای کاربردی Excel + گواهینامهمجموعه فیلم آموزش اکسل برای کسب و کارتابع TRANSPOSE در اکسل – از صفر تا صد + مثالمقایسه دو لیست در اکسل – از صفر تا صد + مثال
این آموزش توسط تیم تخصصی گسترش اندیشه پویا (GAP) گردآوری و ویرایش شده است. برای مشاوره و خدمات تخصصی در این حوزه با ما در ارتباط باشید.