تاریخ انتشار: ۲۰ مرداد ۱۴۰۵

بازدید: 193 بازدید

زمان مطالعه: 30 دقیقه

ادغام متن در سلول: راهنمای کامل CONCATENATE ,CONCAT و TEXTJOIN (با مثال کاربردی)

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

ما در مقاله کاربردی راهنمای کامل CONCATENATE ,CONCAT و TEXTJOIN به دنبال ارائه یک توضیح ساده برای این مفاهیم می باشیم. گروه آموزشی ما در این مقاله قصد دارد تا به کاربران بیاموزد چگونه با استفاده از سه ابزار اصلی اکسل یعنی CONCATENATE ,CONCAT و TEXTJOIN داده های خام را به اطلاعات ارزشمند تبدیل نماید. در نتیجه مقاله راهنمای کامل CONCATENATE ,CONCAT و TEXTJOIN برای این منظور طراحی شده تا کاربران را از افرادی مبتدی به متخصصان مدیریت داده تبدیل نماید.

ادغام متن در اکسل چیست و چه کاربردی دارد؟

ادغام متن در اکسل به معنی ترکیب نمودن محتوای عنوان شده در دو یا چند سلول و یا رشته های متنی مختلف می باشد. این کار به شما کمک خواهد نمود تا داده های خام را برای گزارش دهی، ساخت ایمیل های شخصی سازی شده و یا ایجاد شناسه های یکتا آماده کنید. در نتیجه بدون این قابلیت شما مجبور خواهید بود تا دادهای خود را به صورت دستی تایپ نمایید. فرایند تایپ دستی داده ها در اکسل عموما زمان بسیار زیادی را از کاربران خواهد گرفت. همچنین در این مسیر ممکن است تا شما به عنوان یک کاربر اکسل با احتمال بروز خطای انسانی مواجه گردید. در ادامه به بررسی مقاله راهنمای کامل CONCATENATE ,CONCAT و TEXTJOIN خواهیم پرداخت.

مثال کاربردی: ترکیب نام و نام خانوادگی

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

ستون A ( نام): Ali
ستون B ( نام خانوادگی): Alavi
نتیجه ادغام: Ali Alavi

تابع CONCATENATE چیست و چطور کار می‌کند؟

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

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

ساختار تابع CONCATENATE

=CONCATENATE(text1, [text2], …)

text1: اولین متن یا سلولی که می خواهید ادغام نمایید که جز داده های اجباری در اکسل به شمار می رود.
text2: متن و یا سلول دوم می باشد که قصد ادغام داده های آن را داریم که یکی از داده های اختیاری به شمار می رود.

مثال ساده:

فرض نمایید شما تمایل دارید تا کلمه سلام را با نام موجود در سلول A1 اغام نمایید، ترکیب فوق به شکل زیر به نمایش در خواهد آمد:

=CONCATENATE(“سلام”,”Ali”)

در این مثال اگر کاربر در سلول A1 از کلمه Faezeh استفاده نماید خروجی که نرم افزار اکسل در اختیار کاربر قرار خواهد داد به شکل سلام Faezeh خواهد بود.

مثال استفاده از تابع CONCATENATE برای ترکیب متن در اکسل

بررسی محدودیت های اصلی تابع CONCATENATE

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

=CONCATENATE(A1, A2, A3…)

در نهایت اجرای دستی این فرایند می تواند برای کاربر بسیار زمان بر و خسته کننده به شمار رود. همچنین تابع CONCATENATE فاقد توانایی مدیریت خودکار فاصله می باشد. بنابراین این تابع نمی تواند به صورت خودکار بین کلمات فاصله مورد نیاز را ایجاد نمی نماید و شما باید خودتان از ” ” برای ایجاد این فاصله بهره ببرید. برای شناخت این مفاهیم نیاز است مقاله راهنمای کامل CONCATENATE ,CONCAT و TEXTJOIN به درستی بررسی شود.

محدودیت تابع CONCATENATE در ادغام چند سلول در اکسل

تابع CONCAT چه فرقی با CONCATENATE دارد؟

باید به این موضوع اشاره نماییم که تابع CONCAT 0نسخه های جدید تر و بهینه تر از تابع CONCATENATE می باشد. در نتیجه هدف از معرفی این تابع توسط شرکت مایکروسافت ساده تر نمودن روش ادغام متن در نرم افزار اکسل است.

مهمترین تفاوت تابع CONCAT با تابع CONCATENATE در این است که کاربر به کمک این تابع می تواند علاوه بر سلول های تکی محدوده ای از سلول های مختلف را نیز دریافت نماید. در نتیجه اگر شما نیز برای مدیریت فرایند ادغام متن نیازمند استفاده از توابع فوق می باشید تابع CONCAT می تواند برای شما گزینه مناسبی به شمار رود.

ساختار تابع CONCAT

=CONCAT(text1, [text2], …)

در نگاه اول تفاوت چندانی میان دو تابع CONCAT و CONCATENATE مشاهده نشود. با این حال در تابع CONCAT از آرایه ها و محدوده ها بهتر پشتیبانی می گردد.

مثال استفاده از CONCAT با محدوده سلولی

فرض نمایید شما در سلول های A1 تا A3 شاهد حضور داده های زیر باشید:

A1: ALI
A2: REZA
A3: SARA

فرمول حاصل برای این داده ها به شکل مقابل خواهد بود:

 =CONCAT(A1:A3)

در نهایت خروجی داده های وارد شده به تابع فوق به شکل مقابل خواهد بود: ALIEEZASARA

مثال استفاده از تابع CONCAT برای ادغام محدوده سلول‌ها در اکسل

مقایسه مستقیم این دو تابع با یکدیگر

ویژگی	CONCATENATE	CONCAT
قدمت تابع	قدیمی‌تر	جدیدتر
پشتیبانی از محدوده سلولی	❌ ندارد	✅ دارد
سازگاری با فایل‌های قدیمی	✅ بسیار مناسب	✅ مناسب
ادغام محدوده A1:A10	❌ باید سلول‌ها جداگانه وارد شوند	✅ به صورت مستقیم پشتیبانی می‌شود
مدیریت سلول‌های خالی	❌ هوشمند نیست	❌ هوشمند نیست

چرا مایکروسافت تابع CONCAT را جایگزین CONCATENATE کرد؟

دلیل اصلی معرفی تابع CONCAT، ارائه روشی ساده‌تر و انعطاف‌پذیرتر برای ترکیب متن در اکسل بود. تابع CONCATENATE برای ترکیب چند سلول کاربرد دارد، اما در استفاده از محدوده‌های سلولی محدودیت‌هایی دارد و معمولاً لازم است سلول‌ها به‌صورت جداگانه در فرمول مشخص شوند. در مقابل، تابع CONCAT امکان استفاده مستقیم از محدوده‌هایی مانند A1:A10 را فراهم می‌کند و به همین دلیل برای کار با داده‌های گسترده و فرمول‌های ساده‌تر، گزینه مناسب‌تری محسوب می‌شود. همچنین CONCAT به‌عنوان تابع مدرن‌تر، جایگزین پیشنهادی CONCATENATE در نسخه‌های جدید اکسل است.

پشتیبانی بهتر CONCAT از محدوده‌های سلولی

تابع TEXTJOIN چیست و چه زمانی باید از آن استفاده کرد؟

تابع TEXTJOIN یکی از کاربردی‌ترین توابع اکسل برای ترکیب متن‌ها با استفاده از جداکننده و مدیریت سلول‌های خالی است. در نهایت زمانی که قصد دارید در بین بخش های مختلف از جداکننده های مشخص استفاده نمایید و یا سلول های خالی را نادیده بگیرید می توانید از تابع TEXTJOIN استفاده کنید. این تابع در واقع از هر دو تابع فوق عملکردی هوشمندانه تر از خود به نمایش می گذارد. در نتیجه اگر شما در واقع برای مدیریت داده ها به دقت بیشتری نیاز دارید بهتر است از تابع TEXTJOIN استفاده نمایید.

ساختار تابع TEXTJOIN

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

delimiter : جداکننده بین متن ها است؛ مثلا ” ” یا “, “
ignore_empty : اگر در تابع به صورت TRUE باشد، سلول های خالی نادیده گرفته می شوند.
text1, text2 , … : این عبارت شامل متن ها یا سلول هایی است که باید ترکیب شوند.

مثال استفاده از TEXTJOIN

فرض کنید در سلول های A1 و B1 ما شاهد حضور داده های ALI و AHMADI باشیم. در نهایت با قرار گرفتن داده ها در فرمول TEXTJOIN داده ها به صورت ALI AHMADI به نمایش در خواهد آمد.

مثال استفاده از تابع TEXTJOIN برای ترکیب نام و نام خانوادگی در اکسل

مهم‌ترین مزیت تابع TEXTJOIN

در واقع مهم ترین مزیتی که برای تابع TEXTJOIN قید شده است توانایی آن در نادیده گرفتن سلول های خالی می باشد. فرض نمایید اگر در سلول های A1:ALI و در سلول فضای خالی:B1 و در نهایت نیز در سلول C1: AHMADI قرار گیرد داده خروجی به صورت ALI AHMADI درج خواهد شد.

=TEXTJOIN(” “,TRUE,A1:C1)

نتیجه:

نادیده گرفتن سلول خالی با تابع TEXTJOIN در اکسل

چه زمان هایی استفاده از تابع TEXTJOIN انتخاب بهتری است؟

زمانی که به یک جداکننده ثابت بین متن‌ها نیاز دارید.
زمانی که می‌خواهید سلول‌های خالی را نادیده بگیرید.
زمانی که با محدوده یا لیست طولانی از داده‌ها کار می‌کنید و می‌خواهید فرمولی کوتاه‌تر و خواناتر داشته باشید.

مقایسه کامل سه تابع: کدام را انتخاب کنیم؟

ویژگی	CONCATENATE	CONCAT	TEXTJOIN
قدمت تابع	قدیمی‌تر	جدیدتر	جدیدتر
پشتیبانی از محدوده سلولی	محدود	بله	بله
استفاده از جداکننده	خیر	خیر	بله
نادیده گرفتن سلول‌های خالی	خیر	خیر	بله
مناسب برای داده‌های زیاد	محدود	خوب	بسیار خوب
خوانایی فرمول	متوسط	خوب	خوب
کاربرد پیشنهادی	فایل‌های قدیمی	ادغام ساده و محدوده‌ها	ادغام حرفه‌ای و کنترل جداکننده‌ها

استفاده از علامت & برای ادغام متن

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

فرض نمایید که در سلول A1: ALI و در سلول B1: AHMADI درج شده باشد در نتیجه فرمول برای این داده ها به صورت زیر خواهد بود.

فرمول:

=A1&” “&B1

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

مثال ۲:

=B1&” – “&A1

بنابراین با ترکیب هم زمان کد و نام در سلول های A1:125 و B1: LAPTOP کاربر می تواند شاهد خروجی LAPTOP – ۱۲۵ باشد.

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

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

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

چه زمانی استفاده از توابع برای کاربر انتخاب بهتری است؟

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

اشتباهات رایج در ادغام متن و روش رفع آن‌ها

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

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

یکی از رایج ترین اشتباهات در ادغام متن در سلول ها این است که قرار دادن فاصله میان کلماتی مانند نام و نام خانوادگی فراموش می شود. در این شرایط برای فرمول = A1&B1 خروجی به صورت ALIAHMADI خواهد بود.

راه حل نوشتن فرمول بصورت =A1&” “&B1 است:

اضافه شدن جداکننده های اضافی

وقتی در تایپ متن در اکسل به صورت دستی اقدام می نمایید ممکن است با یک سلول خالی مواجه شوید. معمولا این سلول های خالی در اکسل به صورت کاما و یا فاصله اضافی به نمایش در می آیند.

مثلا:

=A1&”, “&B1&”, “&C1

اگر یکی از سلول‌ها خالی باشد، استفاده از جداکننده به‌صورت دستی می‌تواند باعث ایجاد جداکننده اضافی در خروجی شود.

اگر B1 خالی باشد، ممکن است نتیجه چیزی شبیه این شود:

مقایسه TEXTJOIN با علامت & در مدیریت سلول‌های خالی اکسل

راه حل:

=TEXTJOIN(“, “,TRUE,A1:C1)

نتیجه:

به‌هم ریختن فرمت عدد هنگام ادغام متن

گاهی کاربران نسبت به ادغام اعدادی مثل تاریخ، مبلغ یا درصد با متن اقدام می نمایند که خروجی به شکل خام و عددی نمایش داده خواهد شد.

فرض کن A1 شامل تاریخ باشد، در نهایت اگر بنویسید:

=A1&”: تاریخ “

ممکن است خروجی به صورت یک عدد سریالی در اکسل به نمایش گذاشته شود.

راه حل رفع این مورد این است که:

=”تاریخ: “& TEXT(A1,”yyyy/mm/dd”)

خطای VALUE !

در زمان ادغام متن عموما کاربران کمتر با خطای VALUE ! مواجه می گردند. البته گاهی نیز ترکیب فرمول ها می تواند فرایند را برای کاربر بسیار پیچیده کند. همچنین زمان هایی که از توابع دیگر در کنار متن استفاده می شود نیز ممکن است این خطا رخ دهد.

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

وقتی عدد را با متن ترکیب می کنید خروجی نهایی به شکل یک متن خواهد بود. این موضوعی کاملا طبیعی است. با این حال در شرایطی که شما بخواهید روی آن محاسبه خاصی انجام دهید با مشکل مواجه خواهید شد. در ادامه مقاله راهنمای کامل CONCATENATE ,CONCAT و TEXTJOIN ما به بررسی برخی از سوالات اساسی کاربران خواهیم پرداخت.

سوالات متداول درباره ادغام متن در اکسل

آیا می‌ توان بیش از ۲۵۵ سلول را با هم ادغام کرد؟

بله. در نسخه‌های جدید اکسل می‌توانید با استفاده از CONCAT و TEXTJOIN محدوده‌های بزرگی از سلول‌ها را با یکدیگر ترکیب کنید. مزیت این توابع این است که می‌توانند یک محدوده مانند A1:A1000 را به‌عنوان ورودی دریافت کنند و نیازی نیست تمام سلول‌ها را به‌صورت جداگانه در فرمول وارد کنید.

چطور بین ادغام ‌شده‌ ها خط فاصله یا کاما اضافه کنیم؟

برای اضافه کردن جداکننده‌هایی مانند فاصله، کاما یا خط تیره می‌توانید جداکننده را مستقیماً در فرمول قرار دهید. برای تعداد محدودی از سلول‌ها می‌توان از علامت `&` استفاده کرد؛ اما برای محدوده‌های بزرگ، تابع TEXTJOIN گزینه مناسب‌تری است. برای مثال: TEXTJOIN(” – “,TRUE,A1:C1)= در این فرمول، بین مقادیر هر سلول یک خط تیره قرار می‌گیرد.

آیا TEXTJOIN در اکسل ۲۰۱۶ کار می کند؟

بله. تابع TEXTJOIN در اکسل ۲۰۱۶ معرفی شده است؛ با این حال در برخی نسخه‌ها و شرایط نصب ممکن است در دسترس نباشد. اگر تابع TEXTJOIN در نسخه اکسل شما شناسایی نمی‌شود، نسخه و نوع نصب Office را بررسی کنید.

چطور عدد و متن را با هم ادغام کنیم بدون اینکه عدد خراب شود؟

برای ترکیب عدد و متن، در صورتی که قالب نمایش عدد اهمیت داشته باشد، از تابع TEXT استفاده کنید. این موضوع برای تاریخ، درصد، مبلغ و اعداد دارای قالب خاص اهمیت بیشتری دارد. برای مثال، اگر A1 شامل یک تاریخ باشد، می‌توانید از فرمول زیر استفاده کنید: “تاریخ: “&TEXT(A1,”yyyy/mm/dd”)= زیرا تابع TEXT باعث می‌شود مقدار قبل از ادغام، با قالب موردنظر شما به متن تبدیل شود.