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

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

این تابع ساده با یک نسخه کاربردی تر از آن جایگزین شده است - STsEPIT. در واقع، در نسخه های مدرن مایکروسافت آفیس، این تابع دیگر وجود ندارد، به طور کامل با تابع جایگزین شده است STEP. در حال حاضر هنوز قابل استفاده است، برای سازگاری با نسخه‌های عقب موجود است، اما پس از مدتی ممکن است اینطور نباشد. بنابراین توصیه می شود در اکسل 2016، آنلاین و نسخه های جدیدتر از تابع استفاده کنید STEP.

تابع CNCATENATE - توضیحات مفصل

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

به طور کلی قوانین استفاده از این قابلیت به شرح زیر است:

1. برای جداسازی آرگومان ها از نقطه ویرگول استفاده می شود. اگر کاربر تصمیم به استفاده از کاراکترهای دیگر داشته باشد، نتیجه نمایش در علامت نقل قول خواهد بود.
2. اگر مقداری در قالب متن به عنوان آرگومان تابع استفاده می شود و مستقیماً در فرمول وارد می شود، باید در علامت نقل قول قرار داده شود. اگر اشاره ای به چنین مقداری وجود داشته باشد، هیچ نقل قولی لازم نیست. در مورد مقادیر عددی هم همینطور. اگر نیاز به اضافه کردن یک رقم به یک رشته دارید، نقل قول لازم نیست. اگر این قوانین را نقض کنید، خطای زیر نمایش داده می شود - #NAME؟
3. اگر نیاز به اضافه کردن فاصله بین عناصر متصل دارید، باید به عنوان یک رشته متن جداگانه، یعنی در علامت نقل قول اضافه شود. مثل این: " " .

حال بیایید نگاهی دقیق تر به نحو این تابع بیندازیم. او بسیار ساده است. 

نحو

بنابراین، در واقع، تنها یک آرگومان وجود دارد - این یک رشته متنی است که باید درج شود. همانطور که می دانیم هر آرگومان با یک نقطه ویرگول از هم جدا می شود. شما می توانید حداکثر 255 آرگومان را مشخص کنید. آنها خودشان به روش خودشان تکراری هستند. استدلال اول لازم است. و همانطور که قبلاً می دانیم، می توانید آرگومان ها را در سه قالب تعیین کنید: متن، شماره و پیوند. 

کاربردهای تابع CONCATENATE

تعداد مناطق کاربردی تابع STsEPIT بزرگ. در واقع، تقریباً در همه جا قابل استفاده است. بیایید به برخی از آنها با جزئیات بیشتری نگاه کنیم:

1. حسابداری. به عنوان مثال، یک حسابدار وظیفه دارد سری و شماره سند را برجسته کند و سپس این داده ها را به صورت یک خط در یک سلول وارد کند. یا باید به سری و شماره سندی که توسط چه کسی صادر شده است اضافه کنید. یا چندین رسید را همزمان در یک سلول فهرست کنید. در واقع، یک دسته کامل از گزینه ها وجود دارد که می توانید به طور نامحدود لیست کنید. 
2. گزارش های دفتر به خصوص اگر نیاز به ارائه داده های خلاصه دارید. یا نام و نام خانوادگی را ترکیب کنید.
3. بازیگونهسازی. این یک روند بسیار محبوب است که به طور فعال در آموزش، فرزندپروری و همچنین در برنامه های وفاداری شرکت های مختلف استفاده می شود. بنابراین در زمینه آموزش و تجارت نیز این عملکرد می تواند مفید باشد. 

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

تابع معکوس CONCATENATE در اکسل

در واقع، چنین تابعی وجود ندارد که کاملاً مخالف تابع "CONCATENATE" باشد. برای انجام تقسیم سلولی از عملکردهای دیگری مانند LEVSIMV и RIGHTو PSTR. اولی تعداد مشخصی کاراکتر را از سمت چپ رشته استخراج می کند. دومی در سمت راست است. ولی PSTR می تواند آن را از یک مکان دلخواه انجام دهد و به یک مکان دلخواه ختم شود. 

همچنین ممکن است نیاز به انجام کارهای پیچیده تری داشته باشید، اما فرمول های جداگانه ای برای آنها وجود دارد. 

مشکلات رایج در عملکرد CONCATENATE

در نگاه اول، عملکرد STsEPIT خیلی ساده. اما در عمل معلوم می شود که یک سری مشکلات ممکن است. بیایید نگاهی دقیق تر به آنها بیندازیم. 

1. نقل قول ها در رشته نتیجه نمایش داده می شوند. برای جلوگیری از این مشکل، باید از نقطه ویرگول به عنوان جداکننده استفاده کنید. اما همانطور که در بالا ذکر شد، این قانون در مورد اعداد صدق نمی کند.
2. کلمات بسیار نزدیک هستند. این مشکل به این دلیل رخ می دهد که یک فرد تمام تفاوت های ظریف استفاده از تابع را نمی داند STsEPIT. برای نمایش کلمات به صورت جداگانه، باید یک کاراکتر فاصله به آنها اضافه کنید. یا می توانید آن را مستقیماً بعد از آرگومان متن وارد کنید (هم در داخل سلول و هم اگر متن را جداگانه در فرمول وارد کنید). به عنوان مثال به این صورت: =CONCATENATE ("سلام"، "عزیز"). می بینیم که در اینجا یک فاصله به آخر کلمه سلام اضافه شده است. 
3. #نام؟ این نشان می دهد که هیچ نقل قولی برای آرگومان متن مشخص نشده است. 

توصیه هایی برای استفاده از تابع

برای کارآمدتر کردن کار با این عملکرد، باید چندین توصیه مهم را در نظر بگیرید:

1. استفاده و تا حد امکان. اگر نیاز به پیوستن فقط دو خط متن دارید، دیگر نیازی به استفاده از یک تابع جداگانه برای این کار نیست. بنابراین صفحه گسترده سریعتر اجرا می شود، به خصوص در رایانه های ضعیف با مقدار کمی رم. یک مثال فرمول زیر است: =A1 و B1. شبیه فرمول = استSECTION(A1,B1). به خصوص گزینه اول هنگام وارد کردن دستی فرمول آسان تر است.
2. اگر لازم است یک واحد پول یا تاریخ را با یک رشته متنی و همچنین اطلاعاتی در هر قالب دیگری غیر از موارد ذکر شده در بالا ترکیب کنید، ابتدا باید آن را با تابع پردازش کنید. TEXT. این برای تبدیل اعداد، تاریخ ها، نمادها به متن طراحی شده است.

همانطور که می بینید، درک این تفاوت های ظریف به هیچ وجه دشوار نیست. و از اطلاعات بالا پیروی می کنند. 

کاربردهای متداول برای تابع CONCATENATE

بنابراین فرمول کلی این است: CONCATENATE([text2];[text2];…). متن خود را در مکان های مناسب درج کنید. لازم به ذکر است که لازمه متن دریافتی به شرح زیر است: باید از طول فیلدی که مقدار در آن وارد شده است کمتر باشد. به عنوان ویژگی، می توانید نه تنها از مقادیر از پیش تعریف شده، بلکه از اطلاعات موجود در سلول ها و همچنین نتایج محاسبات با استفاده از فرمول های دیگر استفاده کنید.

در این طرح، توصیه اجباری برای استفاده از داده ها برای ورودی در قالب متن وجود ندارد. اما نتیجه نهایی در قالب "متن" نمایش داده می شود.

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

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

اما در حالی که مبتدی هستید، باید از یک پنجره خاص استفاده کنید.

پس چگونه آن را صدا کنیم؟ اگر به خط ورودی فرمول نگاه کنید، در سمت چپ آن یک دکمه کوچک "fx" وجود دارد. اگر آن را فشار دهید، منوی زیر ظاهر می شود. باید تابع مورد نظر را از لیست انتخاب کنیم.

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

اگر داده ها را به صورت دستی وارد کنید، ورودی انجام می شود و با علامت "برابر" شروع می شود. یعنی به این صورت:

=CONCATENATE(D2;"";E2)

پس از تمام عملیات انجام شده توسط ما، در سلول حاصل متن "21.09" را خواهیم دید که از چندین بخش تشکیل شده است: عدد 21 که در سلول نمایه شده به عنوان D2 و خط 09 که در سلول E2 است یافت می شود. . برای اینکه آنها با یک نقطه از هم جدا شوند، از آن به عنوان آرگومان دوم استفاده کردیم. 

صحافی نام

برای وضوح، بیایید به مثالی نگاه کنیم که نحوه اتصال نام ها را توضیح می دهد. 

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

بر اساس این جدول، می‌دانیم که ارجاع به نام‌ها باید در ستون B، و نام‌های خانوادگی – A آورده شود. خود فرمول در خانه اول تحت عنوان «نام کامل» نوشته می‌شود.

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

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

بعد از اینکه آرگومان ها را تعریف کردیم، آنها را در فرمول به ترتیب مناسب می نویسیم. 

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

گاهی اوقات می توانید یک کاما معمولی بین آرگومان ها قرار دهید. اگر از نسخه انگلیسی اکسل استفاده شود، یک کاما گذاشته می شود. اگر نسخه -language، سپس نقطه ویرگول. بعد از اینکه Enter را فشار دادیم، نسخه ادغام شده ظاهر می شود.

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

دقیقاً به همین ترتیب می توانید ایالت و شهر را به هم وصل کنید.

پیوند دادن اعداد و متن

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

ما به نتیجه نهایی زیر نیاز داریم.

در این مورد نیز به سه آرگومان نیاز داریم و نحو همچنان یکسان است. اما بیایید سعی کنیم وظیفه کمی افزایش پیچیدگی را تکمیل کنیم. فرض کنید باید رشته پیچیده "25 سیب داریم" را بنویسیم. بنابراین، ما باید یک خط دیگر "We have" را به سه آرگومان موجود اضافه کنیم. نتیجه نهایی اینگونه به نظر می رسد.

=CONCATENATE("ما داریم ";F17;" ";F16)

در صورت تمایل، کاربر می تواند تقریباً به تعداد دلخواه آرگومان اضافه کند (در حد بالا).

اتصال VLOOKUP و CONCATENATE

اگر از توابع استفاده می کنید VPR и STsEPIT با هم، می تواند یک ترکیب بسیار جالب و مهمتر از همه کاربردی باشد. با استفاده از تابع VPR ما یک جستجوی عمودی روی جدول را با توجه به یک معیار خاص انجام می دهیم. سپس می توانیم اطلاعات یافت شده را به خط موجود اضافه کنیم.

بنابراین، فرض کنید چنین جدولی داریم. توضیح می دهد که چه کالاهایی در حال حاضر در انبارهای اول و دوم هستند. 

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

پس از آن داده ها را به هم متصل می کنیم. 

این کار را می توان با این فرمول انجام داد:

=B2&»/»&C2

یا از این قبیل.

=CONCATENATE(B2;”/”;C2)

بنابراین، ما دو ستون را با استفاده از یک اسلش رو به جلو به عنوان جداکننده بین دو مقدار به هم متصل کردیم. بعد، ما این فرمول را به کل ستون A منتقل کردیم. چنین جدولی به دست می آید.

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

بعد، یک سلول را انتخاب کنید K2 ، و فرمول زیر را در آن بنویسید. 

{=ВПР(G2&»/»&H2;A2:E6;5;0)}

یا می توان آن را از طریق تابع نوشت STsEPIT.

{=ВПР(СЦЕПИТЬ(G2;»/»;H2);A2:E6;5;ЛОЖЬ)}

نحو در این مورد شبیه به نحوه ترکیب اطلاعات در مورد تعداد و انبار است. 

باید یک تابع اضافه کنید VPR از طریق کلید ترکیبی "Ctrl" + "Shift" + "Enter".

همانطور که می بینید، همه چیز ساده است.