در این مقالهی کوتاه یاد میگیرید که چگونه چندین ستون اکسل را بدون از دست دادن دادهها در یکی ادغام کنید.
شما یک جدول در اکسل دارید و آنچه میخواهید این است که دو ستون را، سطر به سطر، در یکدیگر ادغام کنید. به عنوان مثال، شما میخواهید ستونهای Name (نام) و Lastname (نام خانوادگی) را در یک ادغام کنید، یا چندین ستون مانند خیابان، شهر، کد پستی، کوچه را به در یک ستون «آدرس» ترکیب کنید، مقادیر را با کاما از هم جدا کنید تا بعداً بتوانید آدرسها را بر روی پاکت چاپ کنید.
متأسفانه، اکسل هیچ ابزاری داخلی برای دستیابی به این هدف ارائه نمیدهد. البته دکمهی Merge (“Merge & Center” و غیره) وجود دارد، اما اگر ۲ سلول مجاور را برای ترکیب آنها انتخاب کنید، همانطور که در تصویر مشاهده میکنید
پیام خطایی دریافت خواهید کرد “Merging cells only keeps the upper-left cell value, and discards the other values.” (اکسل۲۰۱۳) یا “The selection contains multiple data values. Merging into one cell will keep the upper-left most data only.” (اکسل ۲۰۱۰، ۲۰۰۷)
در ادامهی این مقاله، 2 روش ارائه خواهیم کرد که به شما امکان میدهد دادهها را از چندین ستون در یک ستون بدون از دست رفتن دادهها و بدون استفاده از ماکروی VBA ادغام کنید.
ادغام دو ستون با استفاده از فرمولهای اکسل
مثلاً، شما یک جدول حاوی اطلاعات مشتری خود دارید و میخواهید دو ستون First و Last names (نام و نام خانوادگی) را در یک ستون Full Name (نام کامل) ترکیب کنید.
- ستون جدیدی در جدول خود ایجاد کنید. نشانگر ماوس را در عنوان ستون قرار دهید (در مثال ما ستون D)، روی ماوس راست کلیک کرده و “Insert” را از منوی زمینه انتخاب کنید. ستون تازه اضافهشده را به عنوان مثال “Full Name” نامگذاری میکنیم.
- در سلول D2 این فرمول را بنویسید:
=CONCATENATE(B2," ",C2)
در اینجا B2 و C2 به ترتیب آدرس سلولهای نام و نام خانوادگی هستند. توجه داشته باشید که یک فاصله بین علامتهای نقل قول ” “ در فرمول وجود دارد. این یک جداکننده است که بین نامهای ادغام شده قرار میگیرد، می توانید از هر نمادی دیگر به عنوان جداکننده استفاده کنید، به عنوان مثال ویرگول.
با روشی مشابه، میتوانید دادهها را از چندین سلول به یکدیگر بپیوندید و از هر جداکنندهای به انتخاب خودتان استفاده کنید. به عنوان مثال، میتوانید آدرسها را از ۳ ستون (Street, City, Zip) در یک سلول ترکیب کنید.
- فرمول را در تمام سلولهای ستون Full Name کپی کنید. برای این کار دستهی پر کردن را بکشید یا روی علامت بهعلاوه دو بار کلیک کنید تا فرمول در کل ستون کپی شود.
- خوب، ما نامها را از ۲ ستون در یکی دیگر ترکیب کردهایم، اما این هنوز فرمول است. اگر First name و/یا Last name را حذف کنیم، دادههای مربوطه در ستون Full Name نیز از بین میروند.
- حال باید فرمول را به یک مقدار تبدیل کنیم تا بتوانیم ستونهای غیر ضروری را از کاربرگ اکسل خود حذف کنیم. تمام سلولهای دارای داده در ستون ادغامشده را انتخاب کنید (اولین سلول را در ستون “Full Name” انتخاب کنید و سپس Ctrl+Shift+ArrowDown (مکاننمای پایین) را فشار دهید). محتویات ستون را کپی کنید (Ctrl+C). سپس بر روی سلولی در همان ستون (“Full Name“) راست کلیک کرده و “Paste Special” را از منوی زمینه انتخاب کنید. دکمهی رادیویی “Values” را انتخاب کرده و OK را بزنید.
- ستونهای “First Name” و “Last Name” را حذف کنید، که دیگر نیازی به آن نیست. برای این منظور بر روی عنوان ستون B کلیک کرده، Ctrl را فشار داده و نگه دارید و بر روی عنوان ستون C کلیک کنید (یک روش جایگزین انتخاب یک سلول در ستون B است، Ctrl+Space را فشار دهید تا کل ستون B را انتخاب کنید، سپس Ctrl+Shift+ArrowRight (مکاننمای راست) را فشار دهید تا کل ستون C انتخاب شود). بعد از آن، بر روی هر یک از ستونهای انتخابشده کلیک راست کرده و Delete را از منوی زمینه انتخاب کنید:
خوب، ما نامها را از ۲ ستون در یک ستون ادغام کردهایم! اگرچه، به مقداری تلاش و زمان نیاز داشت.
ادغام دادههای ستونها از طریق Notepad
این روش نسبت به روش قبلی سریعتر است، نیازی به فرمول ندارد، اما فقط برای ترکیب ستونهای مجاور و استفاده از یک تعیینکننده برای همهی آنها مناسب است.
در اینجا یک مثال آورده شدهاست: ما میخواهیم ۲ ستون First Names و Last Names را در یکی ترکیب کنیم:
- هر دو ستون مورد نظر را برای ادغام انتخاب کنید: برای این منظور بر روی B1 کلیک کنید، کلید Ctrl+Shift+ArrowRight (مکاننمای راست) را فشار دهید تا C1 را انتخاب کنید، سپس Ctrl+Shift+ArrowDown (مکاننمای پایین) را فشار دهید تا تمام سلولهای دارای داده در دو ستون انتخاب شوند.
- دادهها را کپی کنید (با فشردن کلید Ctrl+C).
- Notepad را باز کنید: Start-> All Programs -> Accessories -> Notepad
- دادهها را در Notepad پیست کنید (با فشردن کلید Ctrl+V).
- کاراکتر Tab را در کلیپبورد کپی کنید. برای این کار دکمهی Tab را در Notepad فشار دهید، Ctrl+Shift+ArrowLeft (مکاننمای چپ) را فشار دهید، سپس Ctrl+X را فشار دهید.
- کاراکتر Tab را در Notepad با جداکنندهی مورد نظرتان جایگزین کنید. Ctrl+H را باز کردن پنجرهی “Replace” فشار دهید، کاراکتر Tab را از کلیپبورد در قسمت “Find what” پیست کنید، جداکنندهی مورد نظرتان را در قسمت “Replace with” تایپ کنید، به عنوان مثال. فاصله، وبرگول و غیره. دکمهی “Replace All” را فشار دهید. سپس “Cancel” را فشار دهید تا پنجره بسته شود.
- Ctrl+A را فشار دهید تا تمام متن موجود در Notepad انتخاب شود، سپس Ctrl+C را فشار دهید تا در کلیپبورد کپی شود.
- به ورکشیت اکسل خود برگردید (با فشردن Alt+Tab)، فقط سلول B1 را انتخاب کرده و متن را از کلیپبورد به جدول خود پیست کنید.
- ستون B را به نام مورد نظرتان در اینجا “Full Name” تغییر دهید و ستون دیگر “Last name” را حذف کنید.
این روش مراحل بیشتری نسبت به روش قبلی دارد، اما باور کنید یا خودتان آن را امتحان کنید، این روش سریعتر است!
سلام
با ابزار flash fill خیلی راحت تر میشه انجام داد
سلام. برعکس؛ ابزار flash fill برای جدا کردن است نه ادغام.
Awesome post! Keep up the great work! 🙂
Great content! Super high-quality! Keep it up! 🙂
This post actually made my day.
You can’t imagine just how much time I had spent for this information! Thanks!
عااااالی با سپاس و درود فراوان
This page really has all the information I needed about this subject.
Hi my family member! I wɑnt to say that this post is aweѕome, great written and include almost all significant infos. I’d like to see extrɑ posts like this.
Excellent, what a website it is! This web site provides helpful
data to us, keep it up.
Way cool! Some extremely valid points! I appreciate you penning this post and
also the rest of the website is very good.
آقا خیلی بدرد خورد دمت گرم
عالی و کاربردی بود ممنون و سپاس
بسیار عالی بود. توضیحات دقیق و واضح بود و به راحتی توانستم انجام دهم. تشکر فراوان
ممنون از بابت زحماتتون
ولی فرمول صحیحش فکر کنم اینه:
=CONCATENATE(C3؛” “؛D3)
به جای کاما (،) باید نقطه ویرگول (؛) بذاریم
درود. بلە جداکنندهی آرگومانها در هر سیستم میتواند متفاوت باشد و کاما، سمیکالن و سمیکالن برعکس باشد. هر کس باید آن را مطابق سیستم خودش تغییر دهد.
عالی بود ممنون کمک خوبی بود
عالی بود ممنون و سپاسگزارم
مرسی خوب بود
سلام خداخیرتون بده واقعا کارم رو راه انداخت
حدود دو هزار تا سطر رو باید کپی می کردم که به کمک شما در حد دو دقیقه انجام شد
تشکر
سلام توضیحاتتون خیلی شیوا و رسا بود ومشکل من حل شد، ازخداوند طلب بهترین هارو براتون مسئلت میکنم.همیشه پایدارو سربلند باشید
سلام روز بخیر
خیلی عالی بود لطف کردید این اطلاعات رو در سایت قرار دادید برای ادغام دو ستون
با درود و سپاس.
باسلام و احترام
تشكر از جنابعالي خيلي مفيد بود.
ممنون عالی