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

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

تابع Concatenate در اکسل چیست؟

به طور خلاصه در اکسل دو روش برای ادغام کردن داده‌ها وجود دارد که در ادامه می‌بینیم:

• ادغام سلولی یا «Merge Cell»‌
• چسباندن محتوای درون سلول‌ها

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

فیلم مجموعه آموزش اکسل – مقدماتی تا پیشرفته در فرادرس

کلیک کنید

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

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

تابع Concatenate در اکسل

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

1CONCATENATE(text1, [text2], …)

آرگومان «Text» در این تابع یک رشته متنی است که می‌تواند ارجاع سلولی یا مقدار محاسبه شده با فرمول باشد. همچنین شما می‌توانید از نسخه اکسل 2007 به بعد از این تابع استفاده کنید.

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

کلیک کنید

به عنوان مثال، برای یادگیری بهتر، در نظر بگیرید که می‌خواهیم مقادیر سلول‌های «B۶» و «C۶» را با قرار دادن یک علامت «کاما» (,) در بین آن‌ها، ترکیب کنیم. به این منظور از فرمول زیر کمک می‌گیریم:

1=CONCATENATE(B6, ",", C6)

می‌توانید در تصویر زیر، مثال‌های متنوع بیشتری را نیز ببینید.

توجه داشته باشید که در نسخه اکسل 2019 به بعد و همچنین اکسل 365، تابعی به نام «Concat» نیز وجود دارد که درواقع جایگزین مدرن تابع Concatenate در اکسل محسوب می‌شود و دقیقا همان فرمول را دارد. اگرچه تابع Concatenate به دلایل مربوط به سازگاری، همچنان نگه داشته شده است اما مایکرسافت هیچ قولی مبنی بر پشتیبانی از آن در نسخه‌های بعدی نمی‌دهد.

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

برای اینکه مطمئن شوید همیشه با استفاده از تابع Concatenate در اکسل به نتایج درست می‌رسید، نکات زیر را فراموش نکنید:

• این تابع حداقل به یک آرگومان Text نیاز دارد.
• در یک فرمول می‌توانید تا ۲۵۵ رشته که می‌شود مجموعا ۸۱۹۲ حرف را محاسبه کنید.
• نتیجه تابع Concatenate در اکسل همیشه یک رشته متنی است، حتی اگر تمام مقادیر منبع، عددی باشند.
• برخلاف تابع Concat، تابع Concatenate، نمی‌تواند آرایه‌ها را تشخیص دهد. به عنوان مثال باید فرمول این تابع را به شکل CONCATENATE(A1, A2, A3)  بنویسید و نه CONCATENATE(A1:A3)
• اگر هریک از آرگومان‌ها نادرست باشند، این تابع خطای «VALUE» را نشان می‌دهد.

استفاده از عملگر «&» برای ادغام رشته‌ها در اکسل

یکی دیگر از روش‌های ادغام سلول‌ها در اکسل، استفاده از عملگر «&» است. از آن‌جایی که نوشتن این عملگر بسیار سریع‌تر و راحت‌تر از نوشتن نام CONCATENATE است، در بسیاری از مواقع بیشتر به کار می‌آید.

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

1=A2&" "&B2

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

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

ادغام دو سلول یا بیشتر بدون فاصله

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

1=CONCATENATE(A3, B3)

یا به شکل زیر از عملگر کمک بگیرید:

1=A3&B3

توجه داشته باشید که با این فرمول‌ها، مقادیر بدون هیچ فاصله‌ای در کنار هم قرار خواهند گرفت، به شکلی که در تصویر زیر می‌بینید.

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

1=CONCATENATE(A3, B3, C3)

یا عملگر را مثل فرمول زیر بین آن‌ها بنویسید:

1=A3&B3&C3

نتیجه را در تصویر بعدی می‌بینید.

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

ادغام سلول‌ها با فاصله

در بیشتر مواقع در دنیای واقعی نیاز است که بین دو محتوای موردنظر برای ترکیب و ادغام، از فاصله یا علامتی مانند ویرگول یا خط فاصله استفاده شود. برای انجام این کار کافی است عملگر موردنظر را به‌سادگی وارد فرمول کنید. البته حتما باید آن را درون «علامت نقل قول» ("") بگذارید. در ادامه مثالی در این رابطه می‌بینیم.

1=CONCATENATE(A2, " ", B2)

یا

1=A2 & " " & B2

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

1=CONCATENATE(A2, "، ", B2)

یا فرمول بعدی را به کار می‌بریم:

1=A2 & "، " & B2

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

1=CONCATENATE(A2, "-", B2)

یا

1=A2 & "-" & B2

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

همچنان می‌توانید با استفاده از تابع Cpncat در اکسل 2019 و بالاتر، عملیات ادغام را به روشی سریع‌تر انجام دهید و حتی از آرایه‌ها به عنوان ورودی آن کمک بگیرید.

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

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

1=CONCATENATE(A2, " ",B2," ", "تکمیل شد")

این فرمول به کاربر اطلاع می‌دهد که پروژه موردنظر تمام شده است. نتیجه را می‌توانید در تصویر زیر ببینید. توجه داشته باشید که در این فرمول قبل از کلمه «تکمیل شد» از فاصله استفاده کرده‌ایم تا رشته‌های متنی را جدا کرده باشیم. همچنین یک عملگر فاصله «" "» نیز بین مقادیر ترکیبی قرار گرفته است تا نتیجه نیز به درستی و با فاصله‌های مناسب نشان داده شود.

این فرمول را می‌توانید بدون تابع Concatenate و مانند مثال زیر نیز بنویسید:

1=A2&" "&B2&" "&"تکمیل شد"

به همین شکل همچنین می‌توانید رشته‌های متنی موردنظر را در ابتدا یا میانه‌ی فرمول ادغام خود وارد کنید که شبیه به مثال‌های زیر خواهد شد:

1=CONCATENATE("اتمام"," ",A2," ",B2)

1="اتمام"&" "&A2&" "&B2

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

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

1=CONCATENATE("تاریخ امروز: ",TEXT(TODAY(), "mmmm d, yyyy"))

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

1="تاریخ امروز: " & TEXT(TODAY(), "dd-mmm-yy")

در ادامه نتیجه استفاده از این فرمول را می‌بینیم.

اگر قصد دارید محتوای منبع را حذف کنید اما آسیبی به نتایج فرمول وارد نشود، از گزینه پیست «Paste special-values only» برای تبدیل فرمول‌ها به مقادیرشان استفاده کنید.

فیلم آموزش اکسل Microsoft Excel 2019 – مقدماتی در فرادرس

کلیک کنید

تابع Text در اکسل چه کاری می‌کند؟

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

روش ادغام رشته‌های متنی با جدا کننده خطوط

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

مشکل اصلی اینجاست که امکان نوشتن و وارد کردن علامت جدا کننده خطوط، مانند سایر علائم نگارشی در فرمول وجود ندارد. به جای آن باید از تابع «Char» برای جایگزینی کد اسکی مربوطه در فرمول تابع Concatenate در اکسل استفاده کنید. در ادامه روش انجام این کار را می‌بینیم.

• در ویندوز از فرمول CHAR(10)   استفاده کنید. در اینجا عدد «۱۰» کد کاراکتر «Line feed» است.
• در مک فرمول CHAR(13)  را بنویسید که عدد «۱۳» کد کاراکتر «Carriage return» است.

در این مثال، اطلاعات آدرس را در ستون‌های A تا F داریم و می‌خواهیم آن‌ها را به کمک عملگر ادغام &، در ستون G در کنار یکدیگر قرار دهیم. مقادیر ادغامی به کمک علامت «کاما» (,)، «فاصله» (" ") و یک «جداکننده خط» (Char(10)) از هم جدا شده‌اند.

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

همین فرمول را می‌شود به کمک تابع Concatenate در اکسل مانند مثال زیر نوشت:

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

در نهایت به نتایجی مانند تصویر بعدی خواهیم رسید.

توجه داشته باشید که وقتی از جدا کننده خط برای فاصله انداختن بین حروف استفاده می‌کنید باید گزینه «Wrap text» را فعال کرده باشید تا نتایج درست نشان داده شوند. برای انجام این کار دکمه مربوط به آن را در زبانه «Home»‌ بزنید یا دکمه‌های «Ctrl+1» را روی کیبورد فشار دهید تا پنجره «Format Cells» باز شود. به زبانه «Alighment» بروید و تیک گزینه Wrap text را بزنید.

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

• «علامت نقل قول» ("") - فرمول CHAR(34)
• «Forward Slash» (/) - فرمول CHAR(47)
• «ستاره» (*) - فرمول CHAR (42)

آموزش ادغام ستون‌ها با تابع Concatenate در اکسل

برای ادغام ستون‌ها در اکسل به کمک تابع Concatenate در اکسل کافی است فرمول مربوطه را در اولین سلول بنویسید و سپس به کمک «Flash fill» آن را پایین بکشید تا در تمام سلول‌های دیگر نیز کپی شود. Flash Fill دسته کوچکی است که در گوشه سمت راست پایین سلول قرار دارد.

فیلم مجموعه آموزش اکسل – مقدماتی تا پیشرفته در فرادرس

کلیک کنید

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

1=CONCATENATE(A2, " ", B2)

و

1= A2 & " " & B2

روش سریع‌تر برای منتقل کردن فرمول به سلول‌های بعدی این است که سلول حاوی فرمول را انتخاب کرده و دوبار روی دسته Flash Fill کلیک کنید.

Flash Fill در اکسل چیست؟

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

ادغام اعداد و متن با حفظ قالب بندی

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

اما ادغام این دو را می‌توانید با قرار دادن کد قالب بندی در فرمول تابع «Text» انجام دهید و سپس آن را درون تابع Concatenate در اکسل بگذارید.

البته در مثال‌های قبلی از فرمولی که شامل متن و تاریخ بود صحبت کردیم. اما در ادامه نیز چندین فرمول مثالی دیگر برای ترکیب متن و عدد در این تابع را می‌بینیم. در مثال زیر عدد را با دو رقم اعشار و علامت «دلار» ($) استفاده می‌کنیم. توجه کنید که بهتر است قبل از انجام این کار، قالب بندی سلول در اکسل (Format Cell) را برای پذیرفتن ارز تغییر دهید.

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

مثال بعدی برای عدد بدون وجود صفرهای مشخص و علامت دلار است.

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

فرمول لازم برای عدد کسری را نیز در ادامه می‌بینید.

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

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

1=A9 & " " & TEXT(B9, "0.00%")

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

1=A9 & " " & TEXT(B9, "0%")

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

نحوه ادغام محدوده‌ای از سلول‌ها در اکسل

از آن‌جایی که تابع Concatenate در اکسل آرایه‌ها را به عنوان ورودی قبول نمی‌کند، ادغام چندین سلول در یک محدوده می‌تواند کاری زمان‌بر باشد. به همین منظور برای ادغام محدوده‌ای مثلا از A1 تا A4 باید یکی از دو فرمول زیر را به کار ببرید.

1=CONCATENATE(A1, A2, A3, A4)

فرمول دوم را به صورت زیر می‌توانیم بنویسیم.

1=A1 & A2 & A3 & A4

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

فیلم آموزش اکسل Microsoft Excel 2019 – مقدماتی در فرادرس

کلیک کنید

روش اول، استفاده از دکمه Ctrl

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

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

روش دوم، کمک گرفتن از تابع Transpose

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

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

کلیک کنید

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

1=TRANSPOSE(A1:A10)

سپس در نوار فرمول کلیک کرده و دکمه «F۹» را فشار دهید تا فرمول با مقدار محاسبه شده، جابه‌جا شود. در نتیجه، آرایه‌ای از مقادیر خواهید داشت که می‌توانید آن‌ها را به کمک تابع Concatenate در اکسل به‌هم پیوند دهید. حالا براکت‌های اطراف فرمول را حذف کنید.

در این مرحله، عبارت =CONCATENATE(  را قبل از اولین مقدار بنویسید، پرانتز را پس از آخرین مقدار ببنید و دکمه «Enter» را بزنید.

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

روش سوم، استفاده از تابع Concat

در اکسل 365 و اکسل 2021 می‌توانید با فرمول زیر که شکل بهبودیافته تابع Concatenate در اکسل است، از آرایه‌ها به عنوان ورودی ادغام استفاده کنید.

1=CONCAT(A1:A10)

روش چهارم، استفاده از ادغام سلولی

روش بعدی سریع و بدون نیاز به استفاده از فرمول است. می‌توانید به کمک افزونه «Merge Cells» و خاموش بودن گزینه «Merge all area in selection» این کار را انجام دهید.

عملگر «&» اکسل در مقابل تابع Concatenate در اکسل

ممکن است برای بسیاری از کاربران سوال باشد که آیا استفاده از عملگر & روشی سریع‌تر برای ادغام است یا به کار بردن تابع Concatenate در اکسل؟

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

البته از آن‌جایی که 255 رشته، عدد بسیار بزرگی است و احتمالا هیچگاه نیاز به این تعداد ادغام نداشته باشید، تفاوت بین دو روش تنها به راحتی استفاده از آن‌ها خلاصه می‌شود. برای برخی کاربران، خوانش تابع Concatenate آسان‌تر است اما امکان دارد برخی دیگر، نحوه نگارش عملگر & را ساده‌تر بدانند.

سخن پایانی

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