یکی از توابع پرکاربرد به ویژه برای کسانی که با رشته های متنی و مقادیر توصیفی کار میکنند، تابع Concatenate اکسل است. در این مطلب شیوه های مختلف چسباندن رشته های متنی، سلول ها و محدوده ها و … را با استفاده از تابع concatenate و عملگر & بررسی میکنیم. همیشه داده هایی که در ورک بوک های شما قرار دارد بر وفق مراد شما ساختار نیافته اند و برای استفاده از آنها معمولا نیاز است تغییراتی ایجاد کنید و پردازش هایی را انجام دهید. برای مثال در بعضی از موارد نیاز است که محتوای موجود در یک سلول را در چند سلول تقسیم کنید یا برعکس، داده های دو یا چند ستون را در یک ستون با هم ترکیب کنید و … .

مفهوم “concatenate” در اکسل

اساسا دو راه برای ترکیب داده ها در اکسل وجود دارد: • ادغام سلول ها (merge) • چسباندن مقادیر سلول ها به یکدیگر (concatenate) زمانی که از دستور merge استفاده میکنید، در واقع به صورت فیزیکی، دو یا چند سلول را با هم ادغام میکنید و بنابراین نیاز به یک سلول بزرگتر خواهید داشت. احتمالا همه شما از این دستور اکسل استفاده کرده اید و نیاز به توضیح بیشتر نخواهیم داشت. اما زمانی که از تابع Concatenate استفاده میکنید، شما فقط محتوای سلول ها را با هم ترکیب میکنید و با فیزیک سلول ها کاری نخواهید داشت. معمولا زمانی از این روش استفاده میشود که متن مورد نظر ما در سلول های مختلف قرار گرفته است و ما میخوایم محتوای متنی را در سلول های مختلف به هم متصل کنیم و در یک سلول نمایش دهیم. تصویر زیر به خوبی تفاوت دو روش گفته شده را نمایش میدهد:

تابع Concatenate در اکسل

در مورد عملیات چسباندن یا concatenate در اکسل در بخش قبل توضیح دادیم. تابع concatenate اکسل نیز دقیقا همین عملیات را برای شما انجام میدهد. ترکیب تابع concatenate به شکل زیر است:

CONCATENATE(text1, [text2], …)

در ترکیب بالا، text یک رشته متنی، آدرس سلول یا مقدار یک فرمول است. نکته: در نسخه های 2016، 2019، excel online، و excel mobile تابع concatenate با تابع concat جایگزین شده است. البته در نسخه های گفته شده هم تابع concatenate موجود است ولی ما پیشنهاد میکنیم در این ورژن های اکسل از تابع concat استفاده نمایید. هیچ تفاوتی در ترکیب و نحوه استفاده از این دو تابع در اکسل وجود ندارد.

فیلم آموزشی چسباندن مقادیر با استفاده از تابع concatenate

چسباندن مقادیر چند سلول به یکدیگر

ساده ترین فرمول Concatenate برای چسباندن مقادیر دو سلول A1 و B1 به شکل زیر است:

=CONCATENATE(A1, B1)

توجه داشته باشید که مقادیر خواسته شده بدون هیچ جداکننده ای در کنار هم قرار میگیرند( شبیه به ردیف دوم تصویر زیر) برای استفاده از جداکننده در بین مقادیر، از ” ” در دومین پارامتر فرمول استفاده کنید( شبیه به ردیف 3 در تصویر زیر)

=CONCATENATE(A1, " ", B1)

چسباندن یک متن با مقدار یک سلول

تابع concatenate اکسل فقط محدوده به چسباندن مقادیر سلول های مختلف نیست و شما میتوانید رشته های متنی مختلف را نیز با استفاده از این تابع به هم متصل کنید. برای مثال:

=CONCATENATE(A1, " ", B1, " completed")

فرمول بالا به کاربر اطلاع میدهد که پروژه مورد نظر انجام شده است( شبیه به ردیف 2 در تصویر زیر). توجه داشته باشید که ما قبل از عبارت completed یک فصله اضافه کردیم تا بین دو عبارت چسبانده شده فاصله ایجاد شود. طبیعتا شما میتوانید یک رشته متنی را در ابتدا یا انتهای فرمول خود قرار دهید؛ برای مثال:

=CONCATENATE("See ", A1, " ", B1)

یک فاصله (” “) در بین مقادیر ترکیب شده استفاده است. در نتیجه نتیجه به صورت “Project 1” نمایش داده میشود. در صورتی که از این فاصله استفاده نمیکردید، نتیجه به صورت “Project1” می بود.

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

در بعضی از مواقع نیاز است نتیجه بدست آمده توسط یک فرمول را کمی قابل فهم تر کنید. برای انجام این کار میتوانید نتیجه فرمول را با یک مقدار متنی توضیح دهنده به هم متصل کنید. برای مثال :

=CONCATENATE("Today is ",TEXT(TODAY(), "dd-mmm-yy"))

نکات مهم در استفاده از تابع Concatenate در اکسل

برای اینکه همیشه تابع concatenate شما به درستی کار کند، همیشه موارد زیر را به یاد داشته باشید: • تابع concatenate همیشه حداقل نیاز به یک پارامتر متنی دارد. • در یک فرمول concatenate میتوانید تا 255 رشته را به هم متصل کنید. البته مجموع کاراکترها نباید از 8,192 بیشتر شود. • نتیجه فرمول Concatenate همیشه یک رشته متنی(text) خواهد بود، حتی اگر تمام مقادیر استفاده شده مقادیر عددی بوده باشند. • تابع Concatenate آرایه ها را شناسایی نمیکند. هرکدام از آدرس هایی که استفاده میکنید می بایست به صورت جداگانه استفاده شود. برای مثال میبایست از (CONCATENATE(A1, A2, A3= به جای (CONCATENATE(A1:A3= استفاده کنید. • اگر حداقل یکی از توابع concatenate غیر معتبر باشد، فرمول خطای #VALUE! را برمیگرداند.

اپراتور “&” برای چسباندن رشته ها در اکسل

یکی از راههای جایگزین برای چسباندن مقادیر در اکسل عملگر & است. در بسیاری از موارد کاربران ترجیح میدهند به جای تابع Concatenate از این عملگر استفاده کنند زیرا تایپ کردن & بسیار از concatenate آسانتر است. درست شبیه به تابع Concatenate شما میتوانید از & برای چسباندن مقادیر متنی، آدرس های سلول، و نتایج فرمول ها استفاده کنید.

مثال هایی از عملگر & در اکسل

در این قسمت برای فهم بهتر موضوع دقیقا از مثال هایی استفاده میکنیم که در بخش قبل و در مورد تابع Concatenate استفاده کردیم: چسباندن مقادیر سلول های A1 و B1:

=A1&B1

چسباندن مقادیر سلول های A1 و B1 که با استفاده از فاصله از هم جدا شده اند:

=A1&" "&B1

چسباندن مقادیر سلول های A1، B1 و یک رشته متن:

=A1 & B1 & " completed"

فیلم آموزشی چسباندن متن با استفاده از عمگر &

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

="Today is " & TEXT(TODAY(), "dd-mmm-yy")

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

نکته: تنها تفاوت در استفاده از تابع concatenate و عملگر & این است که در تابع concatenate شما میتونید تا 255 رشته در هر فرمول استفاده کنید در حالی که هیچ محدودیتی در این زمینه در مورد عملگر & وجود ندارد. هیچ تفاوت سرعتی نیز در این دو شیوه وجود ندارد و اینکه بهتر است از کدام یک از این دو شیوه در اکسل استفاده کرد، کاملا بستگی به ترجیحات و سلیقه شما دارد که در استفاده از کدام یک از شیوه ها راحت تر هستید.

چسباندن سلول ها با استفاده از فاصله، کاما و کاراکتر های دیگر

معمولا در دنیای واقع، در بین رشته هایی که میخواهیم به هم متصل کنیم می بایست از کاما، فاصله، نشانه های سجاوندی، یا دیگر کاراکترها مثل خط تیره و ممیز استفاده کنیم. برای انجام این کار، کافیست کاراکتر مورد نظر را به فرمول concatenate خود اضافه کنید. به یاد داشته باشید که این کاراکترها را حتما می بایست در درون کوتیشن “” قرار دهید. برای مثال: چسباندن دو سلول با استفاده از فاصله:

=CONCATENATE(A1, " ", B1) یا =A1 & " " & B1

اتصال دو سلول با استفاده از کاما:

=CONCATENATE(A1, ", ", B1) یا =A1 & ", " & B1

اتصال دو سلول با استفاده از خط تیره:

=CONCATENATE(A1, "-", B1) یا =A1 & "-" & B1

تصویر زیر نتیجه کار را نمایش میدهد:

اتصال رشته های متنی با استفاده از line break

ما همیشه در بین متن ها و نوشته های خود از نشانه هایی مثل کاما، خط تیره و … استفاده نمیکنیم. در بسیاری از مواقع نیاز است در بین نوشته ها از یک line break استفاده کنیم و به عبارت دیگر یک خط پایینتر برویم. اما مسئله این است که شما نمیتوانید شبیه دیگر کاراکترها، line break را در فرمول خود تایپ کنید. اما برای انجام این کار نیز در اکسل راه حل وجود دارد و شما میتوانید از یک تابع char در دروم فرمول concatenate خود استفاده کنید با استفاده از این تابع کد ASCII مورد نیاز را به فرمول تزریق کنید: • در Windows، از (CHAR(10 استفاده کنید.( 10 کد ASCII مربوط به line feed است) • در سیستم mac، از (CHAR(13 استفاده کنید. در این مثال، آدرس های مورد نظر در ستون A تا F قرار دارند و ما این آدرس ها را با استفاده از عملگر & در ستون G به هم میچسبانیم. مقادیر ادغام شده با استفاده از یک کاما، فاصله و line break از هم تفکیک شده اند.

=A2 & " " & B2 & CHAR(10) & C2 & CHAR(10) & D2 & ", " & E2 & " " & F2

نکته: زمانی که از line break در بین مقادیر چسبانده شده خود استفاده میکنید، می بایست گزینه “Wrap text” را فعال کنید. برای انجام این کار، Ctrl + 1 را فشار دهید تا Format cell باز شود. در تب alignment، باکس Wrap text را فعال کنید.

نحوه چسباندن ستون ها به یکدیگر در اکسل

برای چسباندن ستون های مختلف به همدیگر در اکسل کافیست در سلول اول یک فرمول ساده concatenate وارد کنید و سپس با استفاده از دستگیره Fill فرمول وارد شده را به ردیف های دیگر درگ کنید. برای مثال برای چسباندن دو ستون A و B و تفکیک مقادیر آنها با استفاده از فاصله، فرمول زیر را در سلول C2 وارد کنید و سپس آن را به دیگر سلول ها کپی کنید. زمانی که دستگیره Fill را درگ میکنید، علامت اشاره گر ماوس به یک علامت به اضافه تبدیل میشود: نحوه چسباندن محدوده ای از سلول ها در اکسل

چسباندن مقادیری که مربوط به محدوده ای سلول هاست ممکن است کمی چالش برانگیز باشد. زیرا تابع Concatenate اکسل آرایه ها را شناسایی نمیکند و شما می بایست در هر پارامتر یک آدرس سلول را وارد کنید. برای چسباندن محتوای مربوط به سلول های A1 تا A4 شما نیاز به یکی از فرمول های زیر دارید:

=CONCATENATE(A1, A2, A3, A4)

=A1 & A2 & A3 & A4

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

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

یک راه ساده برای انتخاب تعدادی زیادی از سلول ها استفاده از کلید Ctrl است. شما میتوانید این کلید را در صفحه کلید خود نگه دارید و سپس سلول های مورد نظر را به سرعت انتخاب کنید.

روش 2: استفاده از تابع Transpose برای بدست آوردن محدوده

زمانی که میخواهید یک محدوده بسیار بزرگ شامل دهها و صدها سلول را به هم بچسبانید، استفاده از روش شماره 1 نیز منطقی نیست. در این موارد روش صحیح تر این است که با استفاده از تابع Transpose، یک آرایه را برگردانید و سپس آن را با آدرس های سلول جایگزین کنید: • سلولی را که میخواهید نتیجه مورد نظر در آن قرار گیرد را انتخاب کنید. • فرمول transpose را در این سلول وارد کنید( در این مثال از فرمول TRANSPOSE(A1:A10)= استفاده میکنیم) • در قسمت Formula bar کلید F9 را انتخاب کنید تا مقادیر محاسبه شده را به جای فرمول نمایش دهد. • شبیه به تصویر زیر علامت های آکولاد را که یک محدوده معمولی را به یک آرایه تبدیل میکنند، حذف کنید. در نتیجه شما آدرس های فرمول را که می بایست در فرمول concatenate خود داشته باشید، در اختیار دارید. • در ابتدای آدرس های سلول در قسمت formula bar عبارت )CONCATENATE= را تایپ کنید، پرانتز را ببندید و Enter را فشار دهید.

چسباندن اعداد و تاریخ با فرمت های مختلف

زمانی که یک متن را به یک تاریخ یا عدد میچسبانیم، ممکن است بخواهیم فرمت نتیجه بدست آمده را متفاوت با داده های استفاده شده قرار دهیم. برای انجم این کار از تابع Text استفاده میکنیم. تابع Text دارای دو پارامتر به شکل زیر است: • در اولین پارامتر (value)، یک مقدار تاریخ یا عدد یا آدرس یک سلول که شامل مقدار عددی است، قرار داده میشود تا تبدیل به text (متن) شود. • در پارامتر دوم(format_text)، فرمت مورد نظر را با استفاده از کدهایی که برای تابع TEXT قابل شناسایی باشد، قرار میدهیم. البته یادآور میشویم که زمانی که یک تاریخ و متن را به هم میچسبانیم، می بایست از تابع Text برای نمایش دادن تاریخ به فرمت مورد نظر استفاده کنید:

=CONCATENATE("Today is ", TEXT(TODAY(), "mm/dd/yy"))

یا

="Today is " & TEXT(TODAY(), "mm/dd/yy")

در زیر چند نمونه فرمول دیگر برای چسباندن مقادیر عددی با متن را آورده ایم: برای نمایش عدد با دو رقم اعشار و علامت $

=A2 & " " & TEXT(B2, "$#,#0.00")

عدم نمایش صفرهای اضافی و علامت$

=A2 & " " & TEXT(B2, "0.#")

برای نمایش اعداد به صورت کسری

=A2 & " " & TEXT(B2, "# ?/???")

نحوه تقسیم( خرد کردن) سلول ها ( برعکس کار تابع Concatenate)

در صورتی که میخواهید برعکس کار تابع Concatenate را در اکسل انجام دهید؛ به این معنی که محتوای یک سلول را به چند بخش تقسیم کنید، اکسل چند راه حل برای شما قرار داده است: • خاصیت Text to Columns • خاصیت Flash Fill در نسخه های 2013 و 2016 اکسل • استفاده از فرمول های Mid، Right و Left

=concatenate(A1,"-",B1,"-",C1,"-",D1)