در این آموزش نحوه استفاده از تابع textjoin در اکسل را به همراه چند مثال توضیح خواهیم داد.تا چند وقت پیش، دو راه برای ادغام متن ها در اکسل وجود داشت: عملگرد “&” و تابع concatenate. اما به نظر میرسد که تابع textjoin جایگزین قدرتمندی برای دو روش قبلی هست. این تابع انعطاف پذیر تر است و شما را قادر میسازد بدون استفاده از هیچ جداکننده ای متن ها را ادغام کنید. در حقیقت ویژگی های بیشتری دارد!!

تابع textjoin در اکسل

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

این تابع در اکسل 2019 و آفیس 365 در دسترس است.

ساختار این تابع در زیر نشان داده شده است:

<dir=”ltr”>

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

آرگومان های تابع

• Delimiter: (آرگومان اجباری)- نشان دهنده جداکننده ای است که میخواهید در بین متن هایی که با هم ترکیب میشوند؛ قرار گیرد. معمولا، به عنوان یک رشته متنی در نظر گرفته میشود و در میان دابل کوتیشن قرار میگرید یا به یک سلول که شامل یک رشته متنی است ارجاع داده میشود. اگر عددی در این قسمت وارد شود، آن را به عنوان یک متن در نظر میگیرد.
• Ignore_empty: آرگومان اختیاری- تعیین میکند که سلول خالی نادیده گرفته شود یا خیر:
  • True: تمام سلول های خالی را نادیده بگیر.
  • False: تمام سلول های خالی را در نتیجه در نظر بگیر.
• Text1: آرگومان اجباری- اولین مقدار برای ادغام. که میتواند یک رشته متنی، یک مرجع به یک سلول شامل یک رشته، یا یک آرایه مانند یک محدوده سلول باشد.
• Text2: آرگومان اختیاری- مقادیر متن اضافی برای ادغام شدن. حداکثر تا 252 آرگومان text مجاز است. (text 252)

در مثال زیر، میخواهیم در سلول D2، متن سلول های A2، B2 و C2 را ادغام کنیم و آنها را با علامت کاما از یکدیگر جدا نماییم. این مسئله را با دو روش حل میکنیم:

روش اول: با استفاده از تابع concatenate، لازم است که شما هر سلول را به صورت جدا گانه انتخاب کنید ، یک جداکننده )”,”) بعد از هر مرجع قرار دهید، که اگر تعداد متن هایی که میخواهیم ادغام کنیم در تعداد سلول های زیادی قرار گرفته باشند این کار بسیار آزاردهنده خواهد بود. فرمول مثال بالا به شکل زیر است:

=concatenate(A2,”,”,B2,”,”,C2)

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

=textjoin(“,”,true,A2:C2)

Textjoin در اکسل- نکاتی که باید به یاد داشته باشی

برای درک بهتر مزایای تابع textjoin، اجازه دهید نگاهی به نحوه استفاده از این تابع در زندگی واقعی بیندازیم.

یک ستون را به لیستی که با کاما از هم جدا شده اند تبدیل کنید

وقتی میخواهید یک ستون عمودی را ادغام کنید به طوری که متون داخل هر سلول با استفاده از یک کاما (یا هر جدا کننده دیگری) از یکدیگر جدا شوند، تابع textjoin انتخاب مناسبی است.

برای مثال، ما میخواهیم برد و باخت هر تیم را در جدول زیر ادغام کنیم. با استفاده از فرمول های زیر این مسئله قابل حل است. که در هر فرمول محدوده سلول هایی که میخواهیم ادغام شوند، متفاوت هستند.

برای تیم 1:

=textjoin(“,”,false,B2:B6)

برای تیم 2:

=textjoin(“,”,false,C2:C6)

برای تیم های دیگر نیز به همین شکل فرمول مربوطه را مینویسیم.

در تمام فرمول ها آرگومان های زیر استفاده شده است:

• Delimiter- یک کاما (“,”)
• Ignore-empty: این آرگومان را false قرار داده ایم تا سول های خالی را نیز شامل شود زیرا سلول خالی نشان دهنده بازی است که تیم بازی نکرده است.

در نتیجه شما لیستی دارید که توسط چهار کاما جدا شده اند؛ که برد و باخت هر تیم را نشان میدهد:

ادغام کردن سلول هایی که جداکننده های متفاوتی دارند

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

فرض کنید میخواهید از سلول هایی شامل قسمت های مختلف نام است؛ نتیجه زیر حاصل شود:

Last name, First name Middle name.

همانطور که مشاهده کردید، نام خانوادگی و نام با استفاده از یک جداکننده کاما و یک فاصله (” ,”) جدا شده اند؛ در حالیکه نام و نام میانی فقط با یک فاصله (” “) از هم جدا شده اند. بنابراین، در نتیجه ما این دو جدا کننده را در یک آرایه ثابت {“, “,” “} قرار میدهیم و فرمول زیر را مینویسیم:

=textjoin({“, “,” “},true,A2:C2)

A2:C2 محدوده اسامی است که میخواهیم با هم ادغام شوند.

از طرف دیگر، شما میتوانید جدا کنند ها را در سلول های خالی (کاما و فاصله را در سلول F3 و فاصله را در سلول g وارد کنید.) و محدوده $F$3:$G$3 (برای آشنایی با ثابت کردن آدرس سلول این مطلب را مطالعه کنید.) را برای آرگومان جداکننده در فرمول استفاده کنید:

=textjoin($f$3:$G$3, true, A2:C2)

با استفاده از این روش کلی، میتوانید محتوای داخل سلول را به شکل های مختلف ادغام کنید.

برای مثال، اگر بخواهید نتیجه به صورت نام اولین کاراکتر نام میانی نام خانوادگی باشد، سپس از تابع left برای استخراج اولین کاراکتر از سلول C2 استفاده کنید. برای جداکننده ها، ما یک فاصله (” “) بین نام و مخفف نام میانی؛ و یک نقطه فاصله (“. “) بین کاراکتر اول نام میانی و نام خانوادگی قرار میدهیم:

=textjoin({“ “,”. “},true,B2,left(C2,1),A2)

ادغام کردن متن و تاریخ در اکسل

در شرایط خاصی که شما میخواهید یک متن و اسم را ادغام کنید، تاریخ مستقیما در فرمول textjoin  کار نمیکند. اگر به خاطر داشته باشید، اکسل تاریخ را به صورت یک شماره سریال ذخیره میکند، بنابراین فرمول یک شماره به نمایندگی از تاریخ برمیگرداند. این موضوع در تصویر زیر قابل مشاهده است:

=textjoin(“ “,true,A2:B2)

برای رفع این مشکل، لازم است قبل از ادغام، تاریخ به یک رشته متنی تبدیل شود. در اینجا تابع text با کد فرمت (“mm/dd/yyyy”) مورد نظر مشکل ما را حل خواهد کرد:

=textjoin(“”,true,A2,text(B2,”mm/dd/yyyy”))

ادغام کردن متن در چند خط

اگر میخواهید متن را در اکسل طوری ادغام کنید که هر قسمت از یک خط جدید شروع شود، از CHAR(10) به عنوان جداکننده استفاده کنید.

=textjoin(char(10),true,A2:B2)

نکته: مانند تصویر بالا اگر میخواهید در چندین خط نمایش داده شود، مطمئن شوید که ویژگی wrap text فعال است. ابزار Wrap text از تب Home قابل دسترس است.

ادغام شرطی متن های سلول

با توجه به توانایی textjoin اکسل در به کاربردن آرایه های متنی، میتوان از آن برای ادغام شرطی دو یا چند سلول استفاده کرد. برای انجام این کار، از تابع IF  در آرگومان text1 تابع textjoin برای ارزیابی یک محدوده از سلول ها و برگرداندن یک آرایه از مقادیر با شرطی که تعیین شده است، استفاده کنید.

با توجه به جدولی که در تصویر زیر نشان داده شده است، فرض کنید شما میخواهید اعضای تیم 1 را بازیابی کنید. برای این کار، فرمول If که در زیر نوشته ایم را در آرگومان text1 تابع textjoin قرار میدهیم:

=if($B$2:$B$9=1,$A$2:$A$9,””)

فرمول بالا بیان میکند که اگر ستون b برابر یک است، یک مقدار از ستون A را در همان ردیف برگردان، در غیر این صورت رشتن خالی را برگردان.

شکل کامل فرمول برای تیم 1 به صورت زیر خواهد بود:

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

به همین ترتیب فرمول مربوطه برای تیم 2 به شکل زیر خواهد بود:

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

توجه کنید. با توجه به ویژگی آرایه های پویا که در آخرین نسخه اکسل 365 پیاده سازی شده است، فرمولی که در تصویر بالا نشان داده شده است یک فرمول معمولی است. در اکسل 2019 باید بعد از نوشتن فرمول آرایه ای، برای اجرای فرمول، کلیدهای میانبر ctrl+shift+enter را به صورت همزمان فشار دهید.

پیدا کردن چندین مورد که با هم مطابقت دارند و برگرداندن آنها به صورت لیستی که با کاما جدا شده اند.

همانطور که میدانید، تابع vlookup اکسل فقط میتواند اولین انطباق یافت شده را برگرداند. اما اگر شما نیاز داشته باشید تمام تطابق هایی که برای یک ID خاص یا هرچیز دیگر وجود دارد برگرداند چه؟

برای به دست آوردن نتایج در سلول های جداگانه، از یکی از فرمول های توضیح داده شده در مطلب چگونه مقادیر مختلف را در اکسل پیدا کنیم (how to vlookup multiple values in excel)، استفاده کنید.

برای پیدا کردن و برگرداندن تمام مقادیر مطابقت داده شده در یک سلول به صورت لیست با جدا کننده کاما، از تابع textjoin استفاده کن.

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

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

A2:A12 نام فروشنده ها است، B2:B12 محصولات تولیدی و D2 فروشنده مورد علاقه است.

فرمول بالا در سلول E2 تمام مطابقت ها با فروشنده D2 (Adam) را برمیگرداند.برای اینکه هوشمندانه از روابط (برای فروشنده مورد نظر) استفاده کنید؛ بازه مربوط به Seller و Product رو ثابت کنید (مانند فرمول ما، که این بازه ها را با علامت های $ ثابت کردیم) تا وقتی فرمول را به سمت پایین گسترش میدهید، برای دو فروشنده دیگر(Robert و Sally) نیز به درستی عمل کند.

توجه داشته باشید. مانند مثال قبلی، این به عنوان یک فرمول معمولی در اکسل 365 مورد استفاده قرار میگیرد. اما در اکسل 2019 بعد از نوشتن فرمول به جای enter باید کلیدهای ترکیبی ctrl+shift+enter را بزنید.

منطق فرمول دقیقا مشابه با مثال قبلی است:

تابع IF نام های موجود در A2:A12 را در برابر نام هدف در سلول D2 (Adam در مورد مثال ما) مقایسه میکند:

IF($A$2:$A$12=D2, $B$2:$B$12, "")

اگر شرط true باشد (مثلا نام D2 با نامی که در ستون A مطابقت داشته باشد)، فرمول محصول را از ستون B باز میگرداند؛ در غیر اینصورت یک رشته خالی (“”)  بر میگرداند. نتیجه If، آرایه زیر است:

{"";"";"Bananas";"Apples";"";"";"";"Oranges";"";"Lemons";""}

آرایه به عنوان آرگومان text1 در تابع textjoin وارد میشود. از آنجایی که textjoin به این شکل عمل میکند که مقادیر را با کاما و فاصله (“, “) جدا کنیم، در نتیجه رشته زیر به عنوان نتیجه نهایی برگردانده خواهد شد:

Bananas, Apples, Oranges, Lemons

Textjoin در اکسل کار نمیکند

وقتی فرمول textjoin یک خطا را بر میگرداند؛ به احتمال زیر به یکی از دلایل زیر است:

• خطای #name زمانی اتفاق می افتد که textjoin در یک ورژن قدیمی اکسل استفاده شده است و آن نسخه این تابع را پشتیبانی نیمکند(نسخه های قبل از 2019). یا زمانی که نام تابع در فرمول اشتباه تایپ شده باشد.
• خطای #value وقتی اتفاق می افتد که نتیجه بازگشتی بیشن از 32767 کاراکتر باشد.
• خطای #value! ممکنه زمانی که اکسل جداکننده را به عنوان متن تشخصی ندهد، به عنوان مثال اگر کاراکتر غیر قابل چاپ (non-printable) مانند char(0) را وارد کنید.