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

سلام. اگر به این صفحه آمده‌اید احتمالا از کاربران تازه کار اکسل هستید و ما هم با همین فرض این مقاله آموزشی را برای شما نوشته‌ایم تا نکته مبهی در ذهن شما باقی نماند و بتوانید مسئله خود راحل کنید. احتمالا شما دو یا چند ستون اطلاعات دارید و می‌خواهید که آن‌ها را با هم ترکیب کنید (یا به هم بچسبانید) مانند چسباندن نام و نام خانوادگی در اکسل که به ترتیب در سلول‌های A1  و B1 تایپ شده‌اند. برای اینکار از عملگر & به سادگی می‌توانید به شکل زیر استفاده کنید:

=A1 & " " & B1 

آشنایی با عملگر & در اکسل

بگذارید اینگونه شروع کنیم که حاصل عبارت 2+2 در اکسل مقدار 4 می‌شود و اگر شما به جای علامت "+" از علامت "/" استفاده کنید، حاصل مقدار 1 خواهد شد. به علامت‌های "+" و یا "/" در دنیای کامیپوتر عملگر (Operator) می‌گویند و وظیفه آنهاست که مشخص نمایند که بر روی دو مقدار چه محاسبه‌ای بایستی انجام شود. ما در اکسل یک علمگر داریم که وظیفه آن چسباندن (ترکیب) دو  مقدار می‌باشد.

این علمگر "&" است و آنرا اَمپِرسَند تلفظ می‌کنیم و به آن "اَند" هم می‌گوییم. برای تایپ & باید از کلید ترکیبی Shift + 7 استفاده کنید.

بنابراین حاصل فرمول = 2 & 2  در اکسل، مقدار 22 خواهد شد.

نکته 1: باید بدانیم که هرگاه در فرمولی بخواهیم یک متن بنویسیم مثلا کلمه OK ، حتما باید آنرا در داخل علامت "" قرار دهیم،  به همین دلیل برای چسباندن دو کلمه x و y در اکسل اگر بنویسیم   =x & y ، حاصل فرمول خطای #Name? خواهد شد، و حتما باید بنویسیم = "x" & "y"

نکته 2: در فرمول نویسی اکسل فاصله‌های بین مقادیر و عملگرها مهم نیستند.

ادغام اطلاعات دو ستون در اکسل، ترکیب نام و نام خانوادگی

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

=A1 & " " & B1 

 و سپس این فرمول را برای همه سلول‌ها کپی کنید. یعنی به اکسل بگوییم که ابتدا نام (سلول A2)  را به یک Space بچسبان و سپس آن را به فامیلی (سلول B2) بچسبان:

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

چسباندن نام و نام خانوادگی در اکسل بدون فرمول نویسی با Flash Fill

در اکسل ابزار جدیدی داریم به نام Flash Fill‌ که با آن بدون نیاز به فرمول نویسی می‌توانیم از اکسل بخواهیم که کارهایی را بر روی متن‌ها انجام دهد. کافی است که یک یا چند نمونه از خروجی که می‌خواهیم را تایپ کنیم و سپس در یکی از سلول‌های آن ستون باشیم و کلید Ctrl + E را بزنیم.

اکسل از روی نمونه‌های ما حدس می‌زند که باید بر روی سایر داده‌ها چه کاری را انجام دهید و بر اساس آن حدس تمام سلول‌ها را پر خواهد کرد.

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

 = A2 & B2
 =CONCATENATE( A2 , B2 )

خروجی عملگر & و تابع CONCATENATE متن است!

بسیار مهم است که بدانید عمل چسباندن یک عملیات ریاضی نیست و بنابراین خروجی فرمول 2 & 2= عدد 22  نیست بلکه یک متن است و آنرا نمی‌توانید با یک عدد دیگر جمع بزنید.

در ضمن آنکه متن بودن حاصل یک فرمول در هنگام Sort و یا Filter کردن هم تاثیر گذار است. مثلا فرض کنید که فرمولی نوشته اید که سال و ماه و روز را که در سه ستون جداگانه هستند را به هم بچسباند و یک تاریخ داشته باشید. این تاریخ یک عدد نیست و شما نمی‌توانید از فیلترهایی که روی اعداد دارید ( مثلا بزرگتر و یا کوچکتر) بر روی این ستون تاریخ استفاده کنید.

برای تبدیل یک متن به عدد از تابع value  باید استفاده کنید.

ترفند) برای تبدیل یک متن،  به جای تابع VALUE می‌توان حاصل فرمول را در عدد ۱ ضرب کرد:

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

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

شرح فرمول سلول A2:    
اگر بخواهید در یک متن پیامک از ENTER استفاده کنید باید از تابع CHAR(10) برای اضافه کردن آن در فرمول استفاده کنید. توجه کنید که حتما باید گزینه Wrap Text (در Format Cells) برای دیدن Enter فعال باشد در غیر اینصورت شما متن را یک سطری خواهید دید.

شرح فرمول سلول A3 و A4:

هر گاه بخواهید در یک فرمول ظاهر عددی را تنظیم کنید باید از تابع TEXT کمک بگیرید.

استفاده از تابع TEXTJOIN در اکسل 2019

ما تا قبل از اکسل 2019 راهی برای چسباندن متن تعداد زیادی سلول بجز نوشتن تک به تک آدرس سلول‌ها نداشتیم. یعنی اگر در سلول A1 تا F1 مقادیری را داشتیم،‌ باید در اکسل می‌نوشتیم:

=A1 & B1 & C1 & D1 & E1 & F1

با اضافه شدن تابع TEXTJOIN‌ در اکسل به سادگی می‌توانیم تمامی این سلول‌ها را به هم چسبانیم:

= TEXTJOIN(" ", TRUE, A1:F1)

همانطور که می‌بینید این تابع حداقل ۳ ورودی دارد که شرح آن را در تصویر زیر می‌توانید مشاهده کنید:

مثال) فرض کنید که در سلول‌های A1 تا F10 تعدادی از هشتگ‌های اینستاگرام فرساران تایپ شده است و می‌خواهیم به ابتدای آنها علامت # را اضافه کنیم و در نهایت به هم بچسبانیم. برای اینکار فرمول زیر را می‌نویسیم:

=TEXTJOIN(" #", TRUE, A1:F1)

و خروجی ما به شکل زیر خواهد شد:

فرساران #آموزش #تخصصی #اکسل #کاربردی #پیشرفته

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

="#" & TEXTJOIN(" #", TRUE, A1:F1)

و خروجی نهایی به شکل زیر خواهد شد:    
#فرساران #آموزش #تخصصی #اکسل #کاربردی #پیشرفته

کپی کردن نتیجه یک فرمول در اکسل - Paste Values

اگر خواستید که نتیجه یک فرمول را در اکسل کپی کنید (مثلا در یک شیت دیگر)، باید از گزینه paste values در اکسل استفاده کنید که در تصویر زیر آن را می‌توانید مشاهده نمایید:

نکته) در نسخه‌های جدید اکسل (بعد از نسخه ۲۰۲۱) برای Paste Values می‌توانید از کلید CTRL+SHIFT+V استفاده کنید.