نحوه تبدیل چند ستون به یک ستون در اکسل

در مواجهه با حجم زیاد داده‌ها،‌ گاه نیاز است محتوای دو ستون با هم ترکیب شوند و در کنار یکدیگر در یک ستون اکسل قرار بگیرند. خوشبختانه ترکیب ستون‌ها در اکسل به‌راحتی اتفاق می‌افتد. در این مطلب از مجله فرادرس به سه روش ساده برای تبدیل چند ستون به یک ستون در اکسل می‌پردازیم. در روش ابتدایی از قابلیت ساده و هوشمند اکسل برای پر کردن خودکار سلول‌ها و در دو روش دیگر از «امپرسند» (&) و تابع CONCAT استفاده می‌کنیم.

۱. ترکیب سلول‌ها در اکسل با استفاده از Flash Fill

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

فیلم آموزش ترفندهای کاربردی در اکسل Excel در فرادرس

کلیک کنید

حال به شرح مثال می‌پردازیم. فرض کنید ستون A حاوی نام و ستون B حاوی نام خانوادگی باشد و قصد داریم در ستون C نام و نام خانوادگی موجود در ستون A و B را با یکدیگر ترکیب کنیم. اگر ستون C حاوی داده باشد، می‌توانید با راست کلیک روی سرستون C و زدن گزینه «Insert» یک ستون خالی اضافه کنید تا محتوای ترکیبی در آن قرار بگیرد.

ترکیب اول را دستی وارد می‌کنیم و اولین نام و نام خانوادگی در ستون‌های A و B را در سلول متناظر آن‌ها در ستون C تایپ می‌کنیم. دقت کنید که روش Flash Fill می‌تواند الگوهای مختلفی را بیابد. برای مثال اگر ستون A حاوی پیش شماره باشد و ستون B حاوی شماره، این دو عدد را از ستون C به طور دستی با یکدیگر ترکیب کنید تا Flash Fill الگوی این کار را پیدا کند و آن را تکرار کند. حتی می‌توانید علائمی برای جداسازی محتوای دو ستون وارد کنید. اگر علائم وارد شده فرمول را بیش از حد برای اکسل پیچیده نکند، Flash Fill می‌تواند الگوی موجود را بیابد و آن را تکرار کند. برای مثال می‌توانید بین پیش شماره و شماره اصلی، یک خط تیره وارد کنید.

بعد از وارد کردن حاصل ترکیب به صورت دستی در اولین سلول متناظر ستون C، دکمه Enter را بزنید تا Flash Fill الگو را بیابد و تکرار کند. خواهید دید که در سلول‌های بعدی، ستون C اکسل ترکیب صحیح را بر اساس الگویی که پیدا کرده، به شما پشنهاد می‌دهد. اگر پیشنهاد اکسل صحیح است، مجدد دکمه Enter را بزنید تا در سلول ثبت شود و به سلول بعدی برود. با این روش به سادگی بدون اینکه از فرمولی استفاده کرده باشید محتوای دو ستون با یکدیگر ترکیب شدند. در این روش می‌توانید به سادگی دو ستون ابتدایی را حذف کنید، چراکه از فرمولی استفاده نشده است و حذف کردن این ستون‌ها تغییری در ستون سوم ایجاد نمی‌کند.

تسلط بر اکسل با فرادرس

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

• فیلم آموزش ترفند‌های کاربردی اکسل فرادرس
• فیلم آموزش رایگان اکسل در ۱۸۰ دقیقه فرادرس
• آموزش رایگان ترفندهای اکسل برای ناشنوایان فرادرس

اگر مایلید تمامی فیلم‌های آموزشی اکسل فرادرس را یکجا مشاهده کنید، روی لینک زیر کلیک کنید:

• مجموعه فیلم‌های آموزشی اکسل فرادرس از مقدماتی تا پیشرفته

۲. ترکیب ستون ‌ها با استفاده از &

با استفاده از عملگر امپرسند (&)   می‌توان فرمولی ساده خلق کرد تا تبدیل چند ستون به یک ستون در اکسل را انجام دهد. با تکرار این فرمول ساده برای سایر سلول‌های متناظر، محتوای ترکیبی مطلوب را به دست آورد.

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

سپس عبارت =&" " را در سلول وارد کنید. بین دو گیومه یک فاصله وجود دارد. این فاصله بین محتوای و ستون A و B بعد از ترکیب شدن قرار می‌گیرد. می‌توانید به جای فاصله از خط تیره، ویرگول یا هر نشان دیگری استفاده کنید.

بار دیگر &   را وارد کنید و پس از آن آدرس سرستون دوم را وارد کنید که در این مثال سلول B1 است. فرمول نهایی چیزی شبیه فرمول زیر خواهد بود:

=A1&" "&B1

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

=A1&B1

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

در این روش برای ترکیب سلول‌ها موجود در دو ستون، از فرمول نویسی استفاده کردیم و در واقع ستون C حاوی فرمول است. در این حالت با حذف کردن ستون A و B، محتوای ستون C نیز از بین می‌رود. برای رفع این مشکل کافیست سلول‌هایی را انتخاب کنیم که حاوی محتوای ترکیبی هستند و محتوای آن‌ها را کپی کنیم. سپس سلول مقصد را انتخاب کنیم و در سربرگ Home در بخش Paste Special گزینه Value را انتخاب کنیم. با این کار تنها محتوای متنی حاصل آن فرمول‌ها در سلول‌های جدید جایگذاری می‌شود.

۳. ترکیب ستون‌ها با استفاده از تابع Concat

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

فیلم آموزش اکسل Microsoft Office Excel 2016 در فرادرس

کلیک کنید

مثالی را که پیشتر مطرح شد مجدد در نظر بگیرید. این بار بعد از وارد کردن علامت تساوی، تابع CONCAT را وارد می‌کنیم. اگر نسخه‌های اکسل قبل از ۲۰۱۹ استفاده می‌کنید به جای CONCAT عبارت CONCATNATE را وارد کنید. پیش‌تر در مجله فرادرس به تفصیل به معرفی تابع CONCATNATE پرداخته‌ایم.

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

سپس در آرگومان دوم ," ", را وارد کنید. در آرگومان دوم علامت یا محتوایی را تعیین می‌کنیم که قصد داریم بین محتوای ترکیبی قرار بگیرد. در این مثال بین محتوای ترکیبی فاصله قرار دادیم.

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

=CONCAT(A1," ",B1)

اگر قصد ندارید بین محتوای این دو ستون علامتی یا فاصله‌ای وارد شود به سادگی آرگومان دوم را حذف کنید.

=CONCAT(A1,B1)

با زدن Enter نتیجه تابع نمایش داده خواهد شد. حال با کشیدن مربع کوچک پایین سلول تا انتهای جدول مد نظر این فرمول برای سایر سلول‌های متناظر نیز اجرا خواهد می‌شود.

مشابه روش قبل محتوای ستون C از جنس فرمول است و اگر ستون‌های A و B حذف شوند، این سلول‌ها نیز از بین خواهد رفت. برای از بین بردن این مشکل کافی‌ست سلول‌هایی که حاوی نتیجه ترکیب هستند را کپی کنیم و سپس مشابه روشی که در بخش قبل مطرح کردیم، در بخش Paste Specials در سربرگ Home این مقادیر را به عنوان Value جایگذاری کنیم. داده‌های جایگذاری شده وابسته به ستون‌های ابتدایی نخواهند بود.