نحوه ترکیب دو ستون نام و نام خانوادگی در اکسل
این آموزش چند روش مختلف برای ترکیب نام ها در اکسل را به شما نشان می دهد: فرمول ها، Flash Fill و ابزار Merge Cells.
کاربرگ های اکسل اغلب برای ذخیره داده های گروه های مختلف مردم – مشتریان، دانش آموزان، کارمندان و غیره استفاده می شوند. در اکثر مواقع، نام و نام خانوادگی در دو ستون جداگانه ذخیره می‌شوند، اما گاهی اوقات ممکن است لازم باشد دو نام را در یک سلول ترکیب کنید. خوشبختانه، روزهای ادغام دستی هر چیزی به پایان رسیده است. در زیر چند ترفند سریع برای پیوستن به نام ها در اکسل پیدا خواهید کرد که باعث صرفه جویی در زمان خسته کننده می شود.
فرمول اکسل برای ترکیب نام و نام خانوادگی
هر زمان که نیاز دارید نام و نام خانوادگی را با هم در یک سلول ادغام کنید، سریع‌ترین راه این است که دو سلول را با استفاده از عملگر علامت (&) یا تابع CONCATENATE به هم متصل کنید ، همانطور که در مثال‌های زیر نشان داده شده است.
فرمول 1. نام و نام خانوادگی را در اکسل ترکیب کنید
فرض کنید، در کاربرگ خود، یک ستون برای نام داده شده و یک ستون دیگر برای نام خانوادگی دارید و اکنون می خواهید این دو ستون را به یک ستون متصل کنید.
در فرم عمومی، فرمول های ترکیب نام و نام خانوادگی در اکسل در اینجا آمده است:
نکته مهم: اگر فرمول ها در اکسل از جمله CONCATENATE، کار نکرد(هیچ حاصلی نشان نداد)، به احتمال زیاد مشکل از Activate نبودن برنامه اکسل است. به عکس زیر توجه کنید:
در فرمول اول، الحاق با کاراکتر آمپر یا اند (&) انجام می شود. فرمول دوم بر تابع مربوطه متکی است (کلمه “Concatenate” تنها راه دیگری برای گفتن “به یکدیگر پیوستن” است). لطفاً توجه داشته باشید که در هر دو مورد، یک کاراکتر فاصله (” “) در بین آنها قرار دهید تا قسمت های نام از هم جدا شوند
=A2&" "&B2
=CONCATENATE(A2, " ", B2)
فرمول را در سلول C2 یا هر ستون دیگری در همان ردیف وارد کنید، Enter را فشار دهید، سپس دستگیره پر را بکشید تا فرمول به تعداد سلول‌هایی که نیاز دارید کپی شود. در نتیجه، ستون های نام و نام خانوادگی را در ستون نام کامل ترکیب خواهید کرد:
فرمول 2. نام خانوادگی و نام را با کاما ترکیب کنید
اگر به دنبال ادغام نام ها در قالب نام خانوادگی، نام مشت هستید ، از یکی از فرمول های زیر برای پیوستن نام و نام خانوادگی با کاما استفاده کنید:
فرمول ها اساساً مانند مثال قبلی هستند، اما در اینجا نام ها را به ترتیب معکوس به هم متصل می کنیم و آنها را با کاما و فاصله (“”) از هم جدا می کنیم.
در تصویر زیر، سلول C2 حاوی این فرمول است:
=B2&", "&A2
و سلول D2 حاوی این یکی است:
=CONCATENATE(B2, ", ", A2)
هر فرمولی را که انتخاب کنید، نتایج یکسان خواهد بود:
فرمول 3. نام، وسط و نام خانوادگی را در یک سلول بپیوندید
با وجود قسمت های مختلف نام که در 3 ستون جداگانه فهرست شده اند، در اینجا نحوه ادغام همه آنها در یک سلول آورده شده است:
از نظر فنی، فقط یک آرگومان دیگر به فرمول های آشنا اضافه می کنید تا نام میانی را ادغام کنید.
با فرض اینکه نام کوچک در A2، نام میانی در B2 و نام خانوادگی در C2 باشد، فرمول های زیر به خوبی کار می کنند:
=A2&" "&B2&" "&C2
=CONCATENATE(A2," ",B2," ",C2)
تصویر زیر اولین فرمول را در عمل نشان می دهد:
در شرایطی که ستون B ممکن است حاوی نام میانی باشد یا نباشد، می‌توانید هر مورد را به صورت جداگانه مدیریت کنید و سپس دو فرمول را با کمک یک دستور IF ترکیب کنید :
=IF(B2="", A2&" "&C2, A2&" "&B2&" "&C2)
این کار از ظاهر شدن فاصله های اضافی بین کلمات در ردیف هایی که نام میانی وجود ندارد جلوگیری می کند:
نکته. در Excel 2016 – 365، می توانید از تابع CONCAT برای ترکیب نام ها نیز استفاده کنید.
فرمول 4. نام اول و نام خانوادگی را ادغام کنید
این مثال نحوه ترکیب دو نام را در یک نام در اکسل و تبدیل یک نام کامل به نام کوتاه نشان می دهد.
به طور کلی، شما از تابع LEFT برای استخراج حرف اول نام قبلی استفاده می کنید و سپس آن را با نام خانوادگی جدا شده با یک کاراکتر فاصله ترکیب می کنید.
با نام کوچک در A2 و نام خانوادگی در B2، فرمول به شکل زیر است:
=LEFT(A2,1)&" "&B2
یا
=CONCATENATE(LEFT(A2,1), " ", B2)
بسته به نتیجه مورد نظر، یکی از تغییرات زیر از فرمول بالا ممکن است مفید باشد.
یک نقطه بعد از اولیه اضافه کنید:
=LEFT(A2,1)&". "&B2
حرف اول را با نام خانوادگی بدون فاصله ادغام کنید:
=LEFT(A2,1)&B2
نام اولیه و نام خانوادگی را با هم ترکیب کنید و نام ترکیبی را به حروف کوچک تبدیل کنید:
=LOWER(LEFT(A2,1))&LOWER(B2)
برای راحتی شما، جدول زیر تمام فرمول ها را به همراه نتایج آنها نشان می دهد:
| آ | ب | سی | دی | E | |
|---|---|---|---|---|---|
| 1 | نام کوچک | نام خانوادگی | نام ترکیبی | فرمول | شرح |
| 2 | Jane | Doe | J Doe | =LEFT(A2,1)&” “&B2 | نام اولیه + نام خانوادگی با فاصله جدا شده است |
| 3 | J. Doe | =LEFT(A2,1)&”. “&B2 | نام اولیه + نام خانوادگی که با نقطه و فاصله از هم جدا شده اند | ||
| 4 | JDoe | =LEFT(A2,1)&B2 | نام اولیه + نام خانوادگی بدون فاصله | ||
| 5 | jdoe | =LOWER(LEFT(A2,1))&LOWER(B2) | حرف اول + نام خانوادگی با حروف کوچک بدون فاصله |
نکات و نکاتی در مورد ترکیب نام ها در اکسل
همانطور که مشاهده کردید، ادغام نام و نام خانوادگی در اکسل با یک فرمول بسیار آسان است. اما اگر برخلاف تمام انتظارات، فرمول شما ناقص عمل می کند یا اصلاً کار نمی کند، نکات زیر ممکن است به شما کمک کند در مسیر درست قرار بگیرید.
فضاهای اضافی را کوتاه کنید
در صورتی که اطلاعات شما از یک پایگاه داده خارجی گرفته شده باشد، به احتمال زیاد ستون های اصلی دارای برخی فضاهای انتهایی هستند که برای چشم انسان نامرئی هستند، اما کاملاً توسط اکسل خوانده می شوند. در نتیجه، ممکن است فاصله های اضافی بین نام های ادغام شده مانند جدول سمت چپ زیر ظاهر شود. برای حذف فاصله زیاد بین کلمات به یک کاراکتر فاصله، هر مرجع سلول را در تابع TRIM بپیچید و سپس به هم متصل کنید. مثلا:
=TRIM(A2)&" "&TRIM(B2)
حرف اول هر نام را بزرگ بنویسید
اگر با فهرستی از پرسنل ایجاد شده توسط شخص دیگری کار می کنید، و آن شخصی فرد چندان دقیقی نیست، ممکن است برخی از نام ها با حروف کوچک و برخی دیگر با حروف بزرگ نوشته شوند. یک راه حل آسان استفاده از تابع PROPER است که اولین کاراکتر هر کلمه را به حروف بزرگ و بقیه را به حروف کوچک مجبور می کند:
=PROPER(A2)&" "&PROPER(B2)
همانطور که میدانید. می توانید حرف اول هر سلول را با حروف بزرگ بنویسید.
فرمول ها را با مقادیر جایگزین کنید و ستون های اصلی را حذف کنید
اگر هدف شما این است که لیستی از نام‌های کامل مستقل از ستون‌های اصلی به دست آورید یا می‌خواهید ستون‌های منبع را پس از ادغام نام‌ها حذف کنید، می‌توانید با استفاده از دستور Pates Special به راحتی فرمول‌ها را به مقادیر تبدیل کنید. پس از آن، می توانید ستون های اصلی حاوی قسمت های نام را حذف کنید.
برای نگاهی دقیق تر به فرمول های مورد بحث در قسمت اول این آموزش، می توانید نمونه کار ما را برای ترکیب نام ها در اکسل دانلود کنید.
نحوه ادغام نام و نام خانوادگی در اکسل به صورت خودکار
هنگام استفاده از فرمول ها، نتیجه و داده های اصلی از نزدیک به هم مرتبط هستند – هر تغییری که در مقادیر اصلی ایجاد شود بلافاصله در خروجی فرمول منعکس می شود. اما اگر انتظار به‌روزرسانی برای نام‌های ترکیبی ندارید، از قابلیت Flash Fill اکسل برای پر کردن خودکار داده‌ها بر اساس یک الگو استفاده کنید.
در اینجا نحوه ترکیب نام ها در یک ثانیه با Flash Fill آورده شده است:
- برای اولین ورودی، نام و نام خانوادگی را در ستون مجاور به صورت دستی تایپ کنید.
- شروع به تایپ نام در ردیف بعدی کنید و اکسل بلافاصله نام کامل را برای کل ستون پیشنهاد می کند.
- اطلاعات لازم برای پذیرش پیشنهادات انجام شده!
زیبایی این روش در این است که اکسل الگو، حروف بزرگ و نقطه گذاری شما را کاملاً «تقلید» می کند، بنابراین می توانید نام ها را دقیقاً همانطور که می خواهید به هم بپیوندید. ترتیب قسمت های نام در ستون های اصلی مهم نیست! فقط مطمئن شوید که نام را در سلول اول دقیقاً همانطور که می خواهید همه نام ها ظاهر شوند تایپ کنید.
به عنوان مثال، ببینید چقدر راحت می توانید نام ها را با کاما ترکیب کنید:
نحوه ترکیب نام و نام خانوادگی با ادغام سلول ها
یکی دیگر از راه های سریع برای ترکیب نام ها در اکسل، ادغام سلول های حاوی قطعات نام است. نه، من در مورد ویژگی ادغام داخلی صحبت نمی کنم زیرا فقط مقدار سلول بالا سمت چپ را حفظ می کند. لطفاً با ابزار Ablebits Merge Cells آشنا شوید که تمام مقادیر شما را در حین ادغام سلول ها حفظ می کند 🙂
برای پیوستن به نام و نام خانوادگی با ادغام سلول‌ها، کاری که باید انجام دهید این است:
- دو ستون نامی را که می خواهید ترکیب کنید انتخاب کنید.
- در تب Ablebits ، در گروه Merge ، روی پیکان کشویی Merge Cells کلیک کنید و Merge Columns into One را انتخاب کنید :
- کادر محاوره ای Merge Cells نمایش داده می شود. شما یک کاراکتر فاصله را در کادر Separate values with تایپ می‌کنید و همه گزینه‌های دیگر را به صورت پیش‌فرض رها می‌کنید:
نکته. اگر می‌خواهید ستون‌های نام و نام خانوادگی اصلی را حفظ کنید، مطمئن شوید که کادر Backup this worksheet انتخاب شده است.
- روی دکمه Merge کلیک کنید .
در نتیجه، نام و نام خانوادگی در یک ادغام شده و در ستون سمت چپ قرار می گیرند:
