آموزش تابع CONCAT در اکسل
معرفی تابع CONCAT
تابع CONCAT در اکسل برای اتصال یا چسباندن چند متن، عدد، مقدار سلول یا محدوده به یکدیگر استفاده میشود. این تابع نسخه جدیدتر و بهتر تابع CONCATENATE است و به شما کمک میکند اطلاعات پراکنده را در یک سلول ترکیب کنید.
برای مثال اگر در سلول A2 نام و در سلول B2 نام خانوادگی قرار داشته باشد، میتوانید با تابع CONCAT نام کامل را بسازید.
=CONCAT(A2," ",B2)
اگر مقدار A2 برابر «علی» و مقدار B2 برابر «رضایی» باشد، نتیجه فرمول برابر میشود با: «علی رضایی».
نکته مهم این است که CONCAT برخلاف تصور بعضی کاربران، بهصورت خودکار فاصله، خط تیره یا جداکننده اضافه نمیکند. بنابراین اگر بین دو مقدار فاصله لازم دارید، باید آن را بهصورت دستی داخل فرمول وارد کنید.
کاربردهای اصلی تابع CONCAT
- ترکیب نام و نام خانوادگی در یک سلول
- ساخت کدهای اختصاصی مانند کد مشتری، کد کالا یا شماره پرونده
- اتصال متن ثابت با مقدار سلول، مثل ساخت پیام یا توضیح آماده
- ترکیب چند ستون اطلاعاتی برای گزارشگیری
- ساخت آدرس کامل از چند بخش مثل شهر، خیابان و پلاک
- چسباندن پیشوند یا پسوند به اعداد و کدها
- ترکیب نتیجه توابع دیگر مثل SUM، COUNTIF، XLOOKUP، AND و OR با متن
- ساخت متنهای قابل استفاده در داشبوردها و گزارشهای مدیریتی
ساختار (Syntax)
ساختار انگلیسی تابع CONCAT به شکل زیر است:
=CONCAT(argument1, argument2, ...)
ساختار فارسی مفهومی تابع به شکل زیر است:
=CONCAT(آرگومان۱; آرگومان۲; ...)
هر آرگومان میتواند یک متن مستقیم، عدد، آدرس سلول، محدوده سلولی یا نتیجه یک تابع دیگر باشد.
آرگومانها
argument1 / مقدار اول برای اتصال
این آرگومان اولین مقدار یا متنی است که میخواهید در خروجی قرار بگیرد. این مقدار میتواند متن مستقیم، عدد، سلول یا یک محدوده باشد. وارد کردن این آرگومان الزامی است.
=CONCAT(A2)
argument2 / مقدار دوم برای اتصال
این آرگومان مقدار دوم برای ترکیب با مقدار اول است. وارد کردن آن اختیاری است، اما در بیشتر کاربردها حداقل دو مقدار را با هم ترکیب میکنیم.
=CONCAT(A2,B2)
argument3 و سایر آرگومانها / مقادیر بعدی
میتوانید چندین مقدار دیگر نیز به تابع اضافه کنید. تابع CONCAT در اکسل میتواند تا ۲۵۳ آرگومان را دریافت کند. همچنین میتوانید بهجای وارد کردن سلولها بهصورت جداگانه، از محدودهها استفاده کنید.
=CONCAT(A2:C2)
مثالهای ساده و پایه
مثال ۱: ترکیب نام و نام خانوادگی
فرض کنید نام در سلول A2 و نام خانوادگی در سلول B2 قرار دارد. برای ساخت نام کامل از فرمول زیر استفاده میکنیم:
=CONCAT(A2," ",B2)
در این فرمول، مقدار A2، سپس یک فاصله و بعد مقدار B2 به هم متصل میشوند.
مثال ۲: ساخت کد کارمندی
فرض کنید در سلول A2 کلمه ثابت «EMP» و در سلول B2 شماره کارمند قرار دارد. برای ساخت کد کارمندی میتوانید از فرمول زیر استفاده کنید:
=CONCAT(A2,"-",B2)
اگر A2 برابر EMP و B2 برابر 1025 باشد، خروجی برابر EMP-1025 خواهد بود.
مثال ۳: ترکیب متن مستقیم با مقدار سلول
اگر در سلول A2 نام مشتری قرار داشته باشد، میتوانید یک پیام ساده بسازید:
=CONCAT("مشتری گرامی ",A2," خوش آمدید")اگر مقدار A2 برابر «آقای احمدی» باشد، نتیجه میشود: «مشتری گرامی آقای احمدی خوش آمدید».
مثال ۴: ترکیب محدوده سلولی
یکی از مزیتهای CONCAT نسبت به CONCATENATE این است که میتواند محدوده سلولی را هم ترکیب کند.
=CONCAT(A2:C2)
اگر A2 برابر «تهران»، B2 برابر «منطقه ۵» و C2 برابر «پلاک ۱۲» باشد، خروجی همه این مقادیر را پشت سر هم نمایش میدهد. البته بدون جداکننده، مگر اینکه جداکنندهها در سلولها وجود داشته باشند.
مثالهای کاربردی و واقعی
مثال ۱: ساخت عنوان گزارش با SUM
فرض کنید فروش ماهانه در محدوده B2:B10 قرار دارد. میخواهید در یک سلول جملهای بسازید که مجموع فروش را نمایش دهد.
=CONCAT("مجموع فروش این دوره: ",SUM(B2:B10)," تومان")این فرمول ابتدا مجموع فروش را با SUM محاسبه میکند و سپس آن را داخل یک متن گزارشگونه قرار میدهد.
مثال ۲: ساخت پیام وضعیت با AND
فرض کنید نمره آزمون در سلول B2 و درصد حضور در سلول C2 قرار دارد. اگر نمره بیشتر از 70 و حضور بیشتر از 80 باشد، میخواهید پیام وضعیت بسازید.
=CONCAT("وضعیت قبولی: ",AND(B2>70,C2>80))خروجی این فرمول TRUE یا FALSE را در کنار متن نمایش میدهد. اگر هر دو شرط برقرار باشند، نتیجه TRUE خواهد بود.
مثال ۳: ساخت پیام با OR
فرض کنید اگر فروش در سلول B2 بیشتر از 10000000 باشد یا امتیاز مشتری در C2 بیشتر از 90 باشد، مشتری ویژه محسوب شود.
=CONCAT("مشتری ویژه: ",OR(B2>10000000,C2>90))اگر حداقل یکی از شرطها برقرار باشد، خروجی TRUE خواهد بود.
مثال ۴: ترکیب CONCAT با COUNTIF
فرض کنید در محدوده A2:A100 نام محصولات فروختهشده قرار دارد و میخواهید تعداد فروش محصول «لپتاپ» را در یک جمله نمایش دهید.
=CONCAT("تعداد فروش لپتاپ: ",COUNTIF(A2:A100,"لپتاپ"))این فرمول تعداد دفعاتی را که کلمه «لپتاپ» در محدوده مشخصشده آمده است، محاسبه و به متن اضافه میکند.
مثال ۵: نمایش اطلاعات مشتری با XLOOKUP
فرض کنید کد مشتری در سلول E2 وارد میشود، کدها در محدوده A2:A100 و نام مشتریان در محدوده B2:B100 قرار دارد. برای نمایش پیام شامل نام مشتری میتوانید از فرمول زیر استفاده کنید:
=CONCAT("نام مشتری: ",XLOOKUP(E2,A2:A100,B2:B100,"یافت نشد"))این فرمول ابتدا با XLOOKUP نام مشتری را پیدا میکند و سپس آن را با متن «نام مشتری:» ترکیب میکند.
مثال ۶: ساخت آدرس کامل
فرض کنید شهر در A2، خیابان در B2، کوچه در C2 و پلاک در D2 قرار دارد.
=CONCAT("شهر ",A2,"، خیابان ",B2,"، کوچه ",C2,"، پلاک ",D2)این فرمول یک آدرس کامل و خوانا میسازد.
ترکیب تابع CONCAT با فرمولهای دیگر
- ترکیب CONCAT با SUM برای نمایش عدد محاسبهشده در متن گزارش
=CONCAT("جمع کل: ",SUM(B2:B20))- ترکیب CONCAT با AVERAGE برای نمایش میانگین
=CONCAT("میانگین فروش: ",AVERAGE(B2:B20))- ترکیب CONCAT با COUNTIF برای شمارش موارد خاص
=CONCAT("تعداد سفارشهای تهران: ",COUNTIF(C2:C100,"تهران"))- ترکیب CONCAT با IF برای ساخت پیامهای شرطی
=CONCAT("نتیجه بررسی: ",IF(B2>50,"قبول","رد"))- ترکیب CONCAT با XLOOKUP برای ساخت خروجیهای جستجویی
=CONCAT("قیمت محصول: ",XLOOKUP(E2,A2:A50,C2:C50,"ناموجود"))- ترکیب CONCAT با TODAY برای ساخت پیام تاریخدار
=CONCAT("تاریخ گزارش: ",TODAY())- ترکیب CONCAT با TEXT برای کنترل فرمت عدد، تاریخ و درصد
=CONCAT("مبلغ فاکتور: ",TEXT(B2,"#,##0")," تومان")- ترکیب CONCAT با AND برای نمایش نتیجه چند شرط
=CONCAT("همه شرایط برقرار است: ",AND(B2>80,C2>80))- ترکیب CONCAT با OR برای بررسی حداقل یک شرط
=CONCAT("حداقل یک شرط برقرار است: ",OR(B2>80,C2>80))خطاهای رایج و روش رفع آنها
۱. فراموش کردن فاصله بین متنها
یکی از رایجترین اشتباهات این است که کاربران دو سلول را بدون فاصله ترکیب میکنند.
=CONCAT(A2,B2)
اگر A2 برابر «علی» و B2 برابر «رضایی» باشد، خروجی «علیرضایی» میشود. برای رفع مشکل باید فاصله را بهصورت دستی اضافه کنید.
=CONCAT(A2," ",B2)
۲. انتظار داشتن جداکننده خودکار
تابع CONCAT بین مقادیر جداکننده قرار نمیدهد. اگر میخواهید بین مقادیر ویرگول، خط تیره یا فاصله باشد، باید خودتان آن را وارد کنید.
=CONCAT(A2," - ",B2," - ",C2)
اگر میخواهید یک محدوده را با جداکننده مشخص ترکیب کنید، بهتر است بهجای CONCAT از TEXTJOIN استفاده کنید.
۳. نمایش نامناسب تاریخ
وقتی تاریخ را مستقیماً با CONCAT ترکیب میکنید، ممکن است اکسل آن را بهصورت عدد سریالی نمایش دهد.
=CONCAT("تاریخ ثبت: ",A2)برای رفع این مشکل، از تابع TEXT برای قالببندی تاریخ استفاده کنید.
=CONCAT("تاریخ ثبت: ",TEXT(A2,"yyyy/mm/dd"))۴. نمایش نامناسب اعداد بزرگ یا مبلغها
اگر مبلغ را مستقیم با متن ترکیب کنید، ممکن است خوانایی آن کم باشد.
=CONCAT("مبلغ: ",B2)برای نمایش عدد با جداکننده هزارگان، از TEXT استفاده کنید.
=CONCAT("مبلغ: ",TEXT(B2,"#,##0")," تومان")۵. دریافت خطای #VALUE!
اگر خروجی نهایی فرمول بیش از محدودیت مجاز اکسل باشد، ممکن است با خطای #VALUE! روبهرو شوید. حداکثر طول متن خروجی در یک سلول اکسل ۳۲,۷۶۷ کاراکتر است.
برای رفع مشکل، تعداد مقادیر ترکیبشده را کاهش دهید یا خروجی را در چند سلول جداگانه تقسیم کنید.
۶. استفاده از CONCAT در نسخههای قدیمی اکسل
تابع CONCAT در نسخههای جدید اکسل ارائه شده است. اگر فایل را در نسخههای قدیمیتر باز کنید، ممکن است تابع شناسایی نشود.
برای رفع مشکل، در نسخههای قدیمیتر میتوانید از CONCATENATE یا عملگر & استفاده کنید.
=CONCATENATE(A2," ",B2)
=A2&" "&B2
۷. وارد کردن متن بدون علامت نقلقول
هر متن ثابت در فرمول اکسل باید داخل علامت نقلقول دوتایی قرار بگیرد. در غیر این صورت اکسل آن را بهعنوان نام تابع، نام محدوده یا مقدار ناشناخته تفسیر میکند.
=CONCAT("کد مشتری: ",A2)نکات حرفهای و ترفندهای مهم
- برای خوانایی بهتر، همیشه جداکنندهها مثل فاصله، خط تیره، ویرگول یا عبارتهای توضیحی را خودتان در فرمول وارد کنید.
- اگر قصد دارید یک محدوده را با جداکننده مشخص ترکیب کنید، معمولاً TEXTJOIN انتخاب بهتری از CONCAT است.
- برای ترکیب تاریخ، درصد، مبلغ و اعداد خاص، بهتر است از تابع TEXT در کنار CONCAT استفاده کنید.
- اگر خروجی شما برای گزارش یا داشبورد استفاده میشود، متنهای توضیحی را واضح و استاندارد بنویسید.
- برای جلوگیری از خطا در فایلهای مشترک، قبل از استفاده از CONCAT بررسی کنید کاربران فایل از نسخه سازگار اکسل استفاده میکنند.
- اگر فقط دو یا سه مقدار ساده را ترکیب میکنید، عملگر & هم میتواند سریع و کاربردی باشد.
- برای ترکیب مقدارهای خالی، بدانید که CONCAT سلول خالی را مانند متن خالی در نظر میگیرد.
- اگر دادهها دارای فاصلههای اضافی هستند، میتوانید قبل از اتصال از TRIM استفاده کنید.
=CONCAT(TRIM(A2)," ",TRIM(B2))
- برای ساخت کدهای استاندارد، میتوانید از TEXT برای ثابت نگه داشتن تعداد رقمها استفاده کنید.
=CONCAT("INV-",TEXT(A2,"0000"))اگر A2 برابر 25 باشد، خروجی به شکل INV-0025 نمایش داده میشود.
تفاوت تابع CONCAT با توابع مشابه
تفاوت CONCAT با CONCATENATE
تابع CONCAT نسخه جدیدتر تابع CONCATENATE است. CONCAT میتواند محدوده سلولی را مستقیماً دریافت کند، اما CONCATENATE معمولاً برای ترکیب سلولها یا مقادیر جداگانه استفاده میشود.
=CONCAT(A2:C2)
=CONCATENATE(A2,B2,C2)
مایکروسافت توصیه میکند در نسخههای جدید اکسل بهجای CONCATENATE از CONCAT یا TEXTJOIN استفاده شود.
تفاوت CONCAT با TEXTJOIN
تابع TEXTJOIN پیشرفتهتر از CONCAT است، چون میتواند بین مقادیر جداکننده مشخص قرار دهد و همچنین امکان نادیده گرفتن سلولهای خالی را دارد.
=TEXTJOIN(" - ",TRUE,A2:C2)اگر میخواهید مقادیر یک محدوده را با خط تیره، ویرگول یا فاصله ترکیب کنید، TEXTJOIN معمولاً گزینه بهتری است.
تفاوت CONCAT با عملگر &
عملگر & یک روش سریع برای اتصال متنها در اکسل است. از نظر نتیجه، در بسیاری از موارد مشابه CONCAT عمل میکند، اما برای فرمولهای طولانی ممکن است خوانایی کمتری داشته باشد.
=A2&" "&B2
=CONCAT(A2," ",B2)
اگر تعداد بخشهای ترکیبی زیاد باشد، استفاده از CONCAT یا TEXTJOIN معمولاً خواناتر است.
تفاوت CONCAT با Flash Fill
Flash Fill یک ابزار هوشمند در اکسل است که بر اساس الگوی واردشده، دادهها را تکمیل میکند. اما فرمول نیست و با تغییر دادههای اصلی همیشه بهصورت خودکار بهروزرسانی نمیشود. CONCAT فرمولی است و با تغییر سلولهای ورودی، خروجی آن نیز تغییر میکند.
سازگاری با نسخههای مختلف اکسل
تابع CONCAT در نسخههای جدید اکسل پشتیبانی میشود، از جمله:
- Excel 2016
- Excel 2019
- Excel 2021
- Microsoft 365
- Excel Online
- نسخههای جدید اکسل برای Mac
در نسخههای قدیمیتر مانند Excel 2013 و Excel 2010، تابع CONCAT ممکن است در دسترس نباشد. در این نسخهها میتوانید از CONCATENATE یا عملگر & استفاده کنید.
=CONCATENATE(A2," ",B2)
=A2&" "&B2
اگر فایل شما بین کاربران مختلف جابهجا میشود، بهتر است نسخه اکسل آنها را در نظر بگیرید. برای بیشترین سازگاری با نسخههای قدیمی، CONCATENATE یا & انتخاب امنتری است؛ اما برای فایلهای جدید و حرفهای، CONCAT و TEXTJOIN پیشنهاد میشوند.
سؤالات پرتکرار درباره تابع CONCAT
آیا تابع CONCAT بهصورت خودکار بین متنها فاصله میگذارد؟
خیر. اگر فاصله، ویرگول، خط تیره یا هر جداکننده دیگری نیاز دارید، باید آن را داخل فرمول وارد کنید.
=CONCAT(A2," ",B2)
آیا CONCAT میتواند یک محدوده را ترکیب کند؟
بله. یکی از مزیتهای CONCAT این است که میتواند محدوده سلولی را دریافت کند.
=CONCAT(A2:C2)
برای ترکیب محدوده با جداکننده، CONCAT بهتر است یا TEXTJOIN؟
برای ترکیب محدوده با جداکننده، TEXTJOIN بهتر است؛ چون جداکننده را بهصورت خودکار بین مقادیر قرار میدهد.
=TEXTJOIN(", ",TRUE,A2:A10)آیا CONCAT سلولهای خالی را نادیده میگیرد؟
سلول خالی در CONCAT مانند متن خالی در نظر گرفته میشود. اما اگر بخواهید سلولهای خالی را هوشمندانه نادیده بگیرید، بهتر است از TEXTJOIN استفاده کنید.
آیا میتوان عدد و متن را با CONCAT ترکیب کرد؟
بله. CONCAT میتواند متن، عدد، مقدار سلول و نتیجه فرمولها را با هم ترکیب کند.
=CONCAT("تعداد سفارش: ",B2)چرا تاریخ در CONCAT به شکل عدد نمایش داده میشود؟
چون اکسل تاریخها را در پشت صحنه بهصورت عدد ذخیره میکند. برای نمایش درست تاریخ، از TEXT استفاده کنید.
=CONCAT("تاریخ: ",TEXT(A2,"yyyy/mm/dd"))حداکثر طول خروجی CONCAT چقدر است؟
حداکثر طول متن در یک سلول اکسل ۳۲,۷۶۷ کاراکتر است. اگر خروجی از این مقدار بیشتر شود، ممکن است با خطا مواجه شوید.
جمعبندی و پیشنهاد یادگیری بعدی
تابع CONCAT یکی از توابع ساده اما بسیار کاربردی اکسل برای ترکیب متنها، اعداد، سلولها و محدودههاست. با این تابع میتوانید نام کامل بسازید، کدهای اختصاصی تولید کنید، خروجی گزارشها را خواناتر کنید و نتیجه توابع دیگر را در قالب جمله نمایش دهید.
مهمترین نکته در استفاده از CONCAT این است که این تابع جداکننده را بهصورت خودکار اضافه نمیکند. بنابراین اگر نیاز به فاصله، ویرگول یا خط تیره دارید، باید آن را در فرمول بنویسید. همچنین برای نمایش صحیح تاریخ، مبلغ و عددهای قالببندیشده، ترکیب CONCAT با تابع TEXT بسیار مفید است.
برای یادگیری بعدی پیشنهاد میشود توابع زیر را تمرین کنید:
- TEXTJOIN برای ترکیب متنها با جداکننده
- TEXT برای قالببندی عدد، تاریخ و درصد
- IF برای ساخت متنهای شرطی
- XLOOKUP برای جستجو و ترکیب نتیجه با متن
- COUNTIF و SUMIF برای ساخت گزارشهای متنی و عددی