ادغام دو ستون در اکسل
ادغام دو ستون در اکسل به روش های متعددی قابل انجام است که به شما امکان می دهد داده ها را از دو یا چند فیلد جداگانه ترکیب کرده و در یک ستون واحد نمایش دهید. این کار برای سازماندهی بهتر اطلاعات آماده سازی داده ها برای گزارش گیری یا استفاده در سایر برنامه ها بسیار کاربردی است.
در کار با صفحات گسترده مانند اکسل اغلب با داده هایی روبرو می شویم که در ستون های مختلفی پراکنده اند اما برای تحلیل یا نمایش نیاز داریم که آن ها را در یک ستون واحد جمع آوری کنیم. متداول ترین مثال برای این نیاز ترکیب نام و نام خانوادگی است که معمولاً در دو ستون مجزا وارد می شوند اما برای ایجاد لیست کامل نیاز به چسباندن آن ها در یک سلول داریم. مثال دیگر می تواند ترکیب اجزای آدرس شامل خیابان شهر و کد پستی باشد. برخلاف تصور اولیه دکمه ادغام سلول (Merge & Center) که در نوار ابزار اصلی اکسل وجود دارد برای این منظور طراحی نشده است. این ابزار بیشتر برای ترکیب بصری سلول ها و مرکز قرار دادن محتوا در آن ها کاربرد دارد و نکته مهم اینجاست که با استفاده از آن فقط مقدار اولین سلول انتخاب شده باقی می ماند و اطلاعات سایر سلول ها از دست می رود. بنابراین برای ادغام داده ها بدون از دست رفتن آن ها باید از روش های دیگری استفاده کرد که در ادامه به تفصیل به آن ها می پردازیم.
ادغام دو ستون با استفاده از فرمول های اکسل
یکی از پرکاربردترین و منعطف ترین روش ها برای ترکیب اطلاعات از ستون های مختلف استفاده از فرمول ها در اکسل است. این روش به شما امکان می دهد تا علاوه بر ادغام ساده جداکننده های دلخواهی مانند فاصله ویرگول خط تیره یا هر کاراکتر دیگری را بین داده های ادغام شده قرار دهید. انعطاف پذیری فرمول ها باعث می شود بتوانید خروجی دقیقی مطابق با نیاز خود ایجاد کنید و حتی داده ها را قبل از ادغام قالب بندی کنید. برای استفاده از این روش ابتدا باید یک ستون جدید در صفحه گسترده خود ایجاد کنید تا نتیجه ادغام در آن قرار گیرد. سپس با نوشتن فرمول مناسب در اولین سلول این ستون جدید و کپی کردن آن برای سطر های بعدی می توانید داده های مورد نظر را از ستون های مبدأ در ستون جدید ترکیب کنید.
فرمول نویسی برای ادغام ستون ها در اکسل بر پایه عملگرها یا توابعی استوار است که وظیفه اتصال رشته های متنی را بر عهده دارند. این روش به خصوص زمانی مفید است که نیاز به کنترل دقیق بر نحوه نمایش داده های ادغام شده و جداکننده های بین آن ها دارید. همچنین با استفاده از فرمول ها می توانید قبل از ادغام تغییراتی در داده های مبدأ ایجاد کنید مثلاً حروف را بزرگ یا کوچک کنید یا قالب بندی اعداد و تاریخ ها را تنظیم نمایید. این سطح از کنترل فرمول ها را به ابزاری قدرتمند برای آماده سازی و ترکیب داده ها تبدیل می کند. در ادامه با دو ابزار اصلی فرمول نویسی برای ادغام ستون ها آشنا می شویم: عملگر & و تابع TEXTJOIN.
آشنایی با عملگر & در اکسل
عملگر & که به آن آمپرسند (&) گفته می شود ساده ترین و شاید رایج ترین روش برای اتصال یا چسباندن مقادیر موجود در سلول های مختلف در اکسل است. این عملگر به شما اجازه می دهد تا محتوای دو یا چند سلول را پشت سر هم قرار دهید و یک رشته متنی واحد ایجاد کنید. نحوه کار با این عملگر بسیار آسان است: کافیست آدرس سلول هایی که می خواهید محتوای آن ها را ادغام کنید با استفاده از این عملگر به هم وصل کنید.
برای مثال اگر نام در سلول A2 و نام خانوادگی در سلول B2 باشد برای ادغام آن ها در یک سلول می توانید فرمول `=A2 & B2` را بنویسید. نتیجه این فرمول چسباندن مستقیم محتوای A2 و B2 بدون هیچ فاصله ای خواهد بود. برای اضافه کردن فاصله یا هر جداکننده دیگری بین مقادیر باید آن جداکننده را به صورت یک رشته متنی در داخل علامت نقل قول دوتایی (” “) قرار داده و با استفاده از عملگر & به اجزای فرمول اضافه کنید. مثلاً برای چسباندن نام و نام خانوادگی با یک فاصله بین آن ها فرمول به شکل `=A2 & ” ” & B2` خواهد بود. در این فرمول `” “` یک رشته متنی حاوی یک فاصله است که بین محتوای A2 و B2 قرار می گیرد. شما می توانید به جای فاصله از هر کاراکتر دیگری مانند کاما (`,`) خط تیره (`-`) یا حتی چندین کاراکتر به عنوان جداکننده استفاده کنید. این انعطاف پذیری باعث می شود عملگر & برای طیف وسیعی از نیازهای ادغامی مناسب باشد.
استفاده از عملگر & محدود به ادغام محتوای سلول ها نیست؛ شما می توانید رشته های متنی ثابتی را نیز مستقیماً در فرمول وارد کرده و با محتوای سلول ها ترکیب کنید. برای مثال فرمول `=”نام کامل: ” & A2 & ” ” & B2` عبارت “نام کامل: ” را به ابتدای نام و نام خانوادگی ادغام شده اضافه می کند. نکته مهم در استفاده از این عملگر این است که تمام خروجی به صورت یک رشته متنی در نظر گرفته می شود حتی اگر سلول های مبدأ حاوی اعداد یا تاریخ باشند. این موضوع می تواند در محاسبات بعدی مشکل ساز شود مگر اینکه خروجی را به نوع داده مناسب تبدیل کنید. با این حال برای ادغام ساده متن ها یا ترکیب متن و اعداد عملگر & ابزاری سریع و کارآمد است.
استفاده از تابع TEXTJOIN در اکسل 2019
تابع TEXTJOIN یکی از توابع جدید و بسیار کاربردی در اکسل است که از نسخه 2019 به بعد معرفی شده است. این تابع به طور خاص برای حل مشکل ادغام تعداد زیادی سلول یا یک محدوده از سلول ها با یک جداکننده مشخص طراحی شده است. قبل از معرفی این تابع اگر می خواستید محتوای ۱۰ سلول را با یک جداکننده خاص ادغام کنید مجبور بودید از عملگر & به صورت ۱۰ بار و اضافه کردن جداکننده به صورت ۹ بار در فرمول استفاده کنید که نوشتن و مدیریت آن بسیار دشوار بود. تابع TEXTJOIN این فرآیند را به شدت ساده کرده است.
ساختار کلی تابع TEXTJOIN به صورت `TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)` است. این تابع سه آرگومان اصلی دارد: ۱. `delimiter`: کاراکتر یا رشته متنی است که می خواهید بین هر عنصر ادغام شده قرار گیرد. این جداکننده باید داخل علامت نقل قول دوتایی (” “) قرار گیرد. ۲. `ignore_empty`: یک مقدار منطقی (TRUE یا FALSE) است که مشخص می کند آیا سلول های خالی در محدوده انتخاب شده نادیده گرفته شوند یا خیر. اگر TRUE باشد سلول های خالی در ادغام نادیده گرفته می شوند و جداکننده برای آن ها اضافه نمی شود. اگر FALSE باشد سلول های خالی نیز در نظر گرفته می شوند و جداکننده برای آن ها نیز اعمال می شود. معمولاً از TRUE برای نادیده گرفتن سلول های خالی استفاده می شود. ۳. `text1, [text2], …`: این آرگومان ها می توانند آدرس یک یا چند سلول محدوده سلول ها (مانند A1:A10) یا رشته های متنی ثابت باشند که می خواهید ادغام کنید. شما می توانید چندین محدوده یا سلول را با کاما از هم جدا کرده و به تابع بدهید.
برای مثال اگر می خواهید محتوای سلول های A1 تا C1 را با کاما و فاصله (“, “) به عنوان جداکننده ادغام کنید و سلول های خالی را نادیده بگیرید فرمول به شکل `=TEXTJOIN(“, “, TRUE, A1:C1)` خواهد بود. این فرمول به طور خودکار جداکننده را بین محتوای هر سلول در محدوده A1:C1 قرار می دهد. استفاده از TEXTJOIN به خصوص برای ادغام محدوده بزرگی از سلول ها (مانند لیست کلمات کلیدی یا اجزای آدرس) بسیار کارآمد است و فرمول ها را خواناتر و کوتاه تر می کند. این تابع ابزاری قدرتمند برای کار با داده های متنی و ترکیب آن ها به شیوه ای ساختاریافته است.
چسباندن نام و نام خانوادگی در اکسل
یکی از رایج ترین سناریوهایی که کاربران اکسل نیاز به ادغام دو ستون پیدا می کنند چسباندن نام و نام خانوادگی است که معمولاً در دو ستون جداگانه در جداول داده وارد می شوند. هدف از این کار ایجاد یک ستون واحد برای نمایش نام کامل افراد است. این نیاز در تهیه لیست های مخاطبین گزارش های پرسنلی یا هر نوع داده ای که شامل اطلاعات افراد باشد بسیار متداول است. همانطور که قبلاً اشاره شد استفاده از دکمه “Merge & Center” در این مورد اشتباه است زیرا تنها نام (سلول اول) باقی می ماند و نام خانوادگی از دست می رود. بنابراین برای چسباندن نام و نام خانوادگی به صورت صحیح و بدون از دست دادن داده باید از روش های مبتنی بر فرمول یا ابزارهای خاص اکسل استفاده کرد.
چسباندن نام و نام خانوادگی معمولاً شامل قرار دادن یک جداکننده اغلب یک فاصله بین نام و نام خانوادگی است. مثلاً اگر “علی” در یک ستون و “احمدی” در ستون دیگر باشد نتیجه مطلوب “علی احمدی” با یک فاصله بین آن هاست. روش های فرمول نویسی مانند استفاده از عملگر & یا تابع TEXTJOIN (برای نسخه های جدیدتر) به خوبی این نیاز را برآورده می کنند. با استفاده از عملگر & همانطور که توضیح داده شد می توانید فرمولی مانند `=A2 & ” ” & B2` بنویسید (فرض بر این است که نام در A2 و نام خانوادگی در B2 است). این فرمول محتوای A2 را به یک فاصله و سپس به محتوای B2 می چسباند. اگر از اکسل 2019 یا بالاتر استفاده می کنید و محدوده بزرگی از نام و نام خانوادگی دارید می توانید از TEXTJOIN نیز استفاده کنید هرچند برای دو ستون & معمولاً ساده تر است. اما اگر مثلاً نام نام میانی و نام خانوادگی در سه ستون جداگانه باشند استفاده از TEXTJOIN با جداکننده فاصله می تواند گزینه ای کارآمدتر باشد: `=TEXTJOIN(” “, TRUE, A2:C2)`.
پس از نوشتن فرمول در اولین سلول ستون جدید کافیست آن فرمول را برای تمام سطر های حاوی داده کپی کنید. ساده ترین راه برای کپی کردن فرمول در یک ستون استفاده از دستگیره پر کردن (Fill Handle) است. کافیست روی سلول حاوی فرمول کلیک کرده ماوس را روی مربع کوچک در گوشه پایین سمت راست سلول ببرید تا نشانگر ماوس به شکل علامت مثبت (+) تبدیل شود و سپس روی آن دو بار کلیک کنید. اکسل به طور خودکار فرمول را تا آخرین سطر حاوی داده در ستون های مجاور کپی می کند. این روش برای چسباندن نام و نام خانوادگی در تعداد زیادی سطر بسیار سریع و کارآمد است و نیاز به کشیدن ماوس تا انتهای جدول را از بین می برد. پس از کپی کردن فرمول ستون جدید حاوی نام های کامل خواهد بود اما این مقادیر نتیجه فرمول هستند و وابسته به ستون های اصلی نام و نام خانوادگی هستند. برای اینکه این مقادیر ثابت شوند و بتوانید ستون های اصلی را حذف کنید نیاز به تبدیل فرمول ها به مقادیر ثابت دارید که در بخش های بعدی به آن می پردازیم.
چسباندن نام و نام خانوادگی در اکسل بدون فرمول نویسی با Flash Fill
یکی از هوشمندانه ترین و سریع ترین ابزارهای اکسل برای انجام عملیات متنی بر روی داده ها بدون نیاز به نوشتن فرمول قابلیت Flash Fill است. این ویژگی که از نسخه اکسل 2013 به بعد در دسترس قرار گرفت با شناسایی الگوها در داده های شما می تواند به طور خودکار اطلاعات را در یک ستون جدید پر کند. Flash Fill به خصوص برای چسباندن نام و نام خانوادگی جدا کردن بخش های مختلف متن یا تغییر قالب بندی داده ها بسیار کارآمد است و فرآیند را برای کاربرانی که با فرمول نویسی راحت نیستند فوق العاده ساده می کند.
نحوه کار با Flash Fill بسیار بصری است. فرض کنید ستون A شامل نام و ستون B شامل نام خانوادگی است و می خواهید در ستون C نام کامل را داشته باشید. ۱. در اولین سلول ستون C (مثلاً C2) نتیجه مطلوب خود را به صورت دستی تایپ کنید. برای مثال اگر در A2 “علی” و در B2 “احمدی” است در C2 تایپ کنید “علی احمدی”. ۲. پس از تایپ نتیجه در C2 به سلول C3 بروید (یا در همان C2 بمانید). ۳. حالا از دو روش می توانید Flash Fill را فعال کنید: * بر روی تب Data در نوار ابزار کلیک کنید و در گروه Data Tools روی دکمه Flash Fill کلیک کنید. * ساده ترین روش استفاده از کلید میانبر Ctrl + E است. با فشردن این کلید ترکیبی اکسل سعی می کند الگوی شما را از سلول C2 تشخیص دهد و بر اساس آن تمام سلول های باقی مانده در ستون C را به طور خودکار پر کند.
اگر الگوی شما واضح باشد (مانند چسباندن نام و نام خانوادگی با یک فاصله) Flash Fill به سرعت و با دقت تمام سطرها را پر می کند. اکسل هوشمند است و می تواند الگوهای مختلفی را تشخیص دهد؛ مثلاً اگر در مثال نام و نام خانوادگی در C2 فقط “علی” را تایپ کنید Flash Fill ممکن است تشخیص دهد که شما فقط بخش اول (نام) را می خواهید و فقط نام ها را کپی کند. اگر در C2 “احمدی علی” را تایپ کنید ممکن است تشخیص دهد که شما می خواهید نام خانوادگی را ابتدا آورده و با کاما و فاصله از نام جدا کنید. قدرت Flash Fill در توانایی آن در یادگیری از مثال شما نهفته است. این ابزار نیازی به فرمول نویسی ندارد و نتایج آن به صورت مقادیر ثابت در سلول ها قرار می گیرند نه فرمول. این بدان معناست که می توانید ستون های اصلی نام و نام خانوادگی را پس از استفاده از Flash Fill بدون نگرانی از از دست رفتن داده های ادغام شده حذف کنید. Flash Fill یک راه حل سریع و بدون دردسر برای بسیاری از وظایف ساده ترکیب و تبدیل داده های متنی است.
Flash Fill در اکسل (نسخه 2013 به بعد) یک ابزار قدرتمند و بدون فرمول است که با تشخیص الگوها از روی نمونه ای که شما تایپ می کنید می تواند داده های متنی را به سرعت در یک ستون جدید پر کند. این ابزار برای چسباندن نام و نام خانوادگی یا جدا کردن بخش های مختلف متن بسیار مفید است.
تبدیل نتایج فرمول به مقادیر ثابت (Paste Special – Values)
همانطور که اشاره شد هنگامی که از فرمول هایی مانند & یا TEXTJOIN برای ادغام داده ها در یک ستون جدید استفاده می کنید مقادیر در این ستون در واقع نتایج آن فرمول ها هستند. این بدان معناست که این مقادیر به داده های موجود در ستون های اصلی (ستون هایی که از آن ها برای ادغام استفاده کرده اید) وابسته هستند. اگر بعداً ستون های اصلی را حذف کنید یا داده های موجود در آن ها را تغییر دهید مقادیر در ستون حاوی فرمول ها نیز تغییر کرده یا از بین خواهند رفت (معمولاً با خطای #REF! مواجه می شوید). برای جلوگیری از این اتفاق و مستقل کردن نتایج ادغام از ستون های مبدأ باید نتایج فرمول ها را به مقادیر ثابت تبدیل کنید. این کار با استفاده از گزینه Paste Special و انتخاب گزینه Values (مقادیر) انجام می شود.
مراحل تبدیل نتایج فرمول به مقادیر ثابت به شرح زیر است: ۱. ستونی که حاوی نتایج فرمول های ادغام است (ستون جدیدی که ایجاد کرده اید) را به طور کامل انتخاب کنید. ساده ترین راه برای انتخاب یک ستون کامل کلیک بر روی سربرگ آن ستون (حرف انگلیسی بالای ستون) است. ۲. محتوای ستون انتخاب شده را کپی کنید. می توانید از کلید میانبر Ctrl + C استفاده کنید یا بر روی محدوده انتخاب شده راست کلیک کرده و گزینه Copy را انتخاب نمایید. ۳. در حالی که محدوده کپی شده همچنان در حافظه موقت (Clipboard) قرار دارد بر روی اولین سلول همان ستون (یا هر جای دیگری که می خواهید مقادیر ثابت را قرار دهید) راست کلیک کنید. ۴. از منوی ظاهر شده گزینه Paste Special (چسباندن ویژه) را انتخاب کنید. یک پنجره جدید باز خواهد شد. ۵. در پنجره Paste Special در قسمت Paste گزینه Values (مقادیر) را انتخاب کنید. این گزینه به اکسل می گوید که به جای فرمول ها فقط مقادیر حاصل از آن ها را بچسباند. ۶. بر روی دکمه OK کلیک کنید.
با انجام این مراحل فرمول های موجود در ستون انتخاب شده با مقادیر ثابتی که در زمان کپی کردن محاسبه شده بودند جایگزین می شوند. حالا می توانید با خیال راحت ستون های اصلی که حاوی داده های اولیه بودند را حذف کنید بدون اینکه نتایج ادغام شده در ستون جدید از بین بروند. در نسخه های جدیدتر اکسل (مانند Microsoft 365) یک کلید میانبر سریع تر برای Paste Values وجود دارد: پس از کپی کردن محدوده می توانید در سلول مقصد کلیدهای Ctrl + Shift + V را فشار دهید تا مستقیماً مقادیر چسبانده شوند. تبدیل فرمول به مقدار یک گام ضروری پس از استفاده از فرمول ها برای ترکیب داده هاست به خصوص اگر قصد دارید داده های اصلی را حذف کنید.
ادغام ستون ها با استفاده از Power Query
Power Query یکی از ابزارهای قدرتمند و مدرن در اکسل است که برای جمع آوری تبدیل و مدل سازی داده ها به کار می رود. این ابزار به خصوص برای کارهای تکراری یا پیچیده تر در زمینه آماده سازی داده ها از جمله ادغام ستون ها بسیار کارآمد است. برخلاف روش های فرمول نویسی که نتایج آن ها در همان صفحه گسترده اصلی نمایش داده می شوند و نیاز به تبدیل به مقدار ثابت دارند Power Query یک پرس وجو (Query) ایجاد می کند که می تواند داده ها را از منابع مختلف (از جمله جدول های موجود در همان فایل اکسل) خوانده تبدیل های لازم را انجام داده و سپس نتیجه را به صورت یک جدول جدید در یک صفحه گسترده یا مدل داده بارگذاری کند. فرآیند ادغام ستون ها در Power Query کاملاً بصری و بدون نیاز به نوشتن فرمول های پیچیده انجام می شود.
مراحل کلی ادغام ستون ها با استفاده از Power Query به شرح زیر است: ۱. داده های مبدأ (جدول یا محدوده ای که شامل ستون هایی است که می خواهید ادغام کنید) را انتخاب کرده و آن را به عنوان یک جدول در اکسل فرمت کنید (Ctrl + T). این کار مدیریت داده ها را آسان تر می کند. ۲. در حالی که یک سلول از جدول انتخاب شده است به تب Data در نوار ابزار بروید و در گروه Get & Transform Data بر روی From Table/Range کلیک کنید. این کار داده های شما را به محیط Power Query Editor می برد. ۳. در Power Query Editor ستون هایی را که می خواهید ادغام کنید انتخاب نمایید. برای انتخاب چندین ستون بر روی سربرگ اولین ستون کلیک کرده کلید Ctrl را نگه دارید و بر روی سربرگ ستون های بعدی کلیک کنید. ۴. پس از انتخاب ستون ها به تب Add Column (اضافه کردن ستون) بروید و در گروه From Text بر روی Merge Columns (ادغام ستون ها) کلیک کنید. ۵. در پنجره Merge Columns یک جداکننده (Separator) از لیست کشویی انتخاب کنید یا گزینه Custom (سفارشی) را انتخاب کرده و جداکننده دلخواه خود را تایپ کنید. ۶. یک نام برای ستون جدید ادغام شده در قسمت New column name (نام ستون جدید) وارد کنید. ۷. بر روی OK کلیک کنید.
Power Query یک مرحله (Step) جدید به لیست مراحل اعمال شده بر روی داده های شما اضافه می کند که عملیات ادغام ستون ها را انجام می دهد. ستون جدید با داده های ادغام شده ظاهر خواهد شد و ستون های اصلی به طور پیش فرض باقی می مانند (می توانید بعداً آن ها را حذف کنید). پس از اتمام تبدیل ها در Power Query Editor به تب Home بروید و بر روی Close & Load (بستن و بارگذاری) کلیک کنید. Power Query نتیجه را به صورت یک جدول جدید در یک صفحه گسترده اکسل بارگذاری می کند. مزیت اصلی این روش این است که فرآیند ادغام کاملاً قابل تکرار و به روزرسانی است. اگر داده های اصلی در جدول مبدأ تغییر کنند کافیست بر روی جدول نتیجه راست کلیک کرده و Refresh (تازه سازی) را انتخاب کنید تا Power Query تمام مراحل تبدیل (از جمله ادغام) را مجدداً اجرا کرده و جدول نتیجه را به روز کند. این ویژگی Power Query را برای کارهای تکراری و گزارش گیری های منظم بسیار مناسب می سازد.
Power Query یک ابزار پیشرفته در اکسل برای تبدیل داده هاست که به شما امکان می دهد ستون ها را به صورت بصری و بدون نیاز به فرمول نویسی ادغام کنید. این روش برای کارهای تکراری و به روزرسانی داده ها بسیار مؤثر است و نتیجه را به صورت یک جدول جدید و مستقل ارائه می دهد.
ادغام ستون های عددی و تاریخ
در حالی که اغلب نیاز به ادغام ستون های متنی مانند نام و نام خانوادگی داریم گاهی لازم است ستون های حاوی اعداد یا تاریخ ها را نیز با هم ترکیب کنیم. به عنوان مثال ممکن است بخواهید کد منطقه و شماره تلفن را در یک ستون واحد ادغام کنید یا سال ماه و روز را که در ستون های جداگانه قرار دارند به صورت یک رشته متنی واحد نمایش دهید. روش های فرمول نویسی (& TEXTJOIN) و ابزارهایی مانند Flash Fill و Power Query همگی می توانند برای ادغام این نوع داده ها نیز به کار روند اما نکته مهمی که باید به آن توجه داشت نوع داده نتیجه است.
هنگامی که از عملگر & یا تابع TEXTJOIN برای ادغام هر نوع داده ای استفاده می کنید نتیجه نهایی همیشه یک رشته متنی (Text String) خواهد بود. این بدان معناست که اگر دو ستون عدد را ادغام کنید حاصل ادغام به عنوان یک عدد در نظر گرفته نمی شود بلکه به عنوان یک متن حاوی ارقام دیده می شود. مثلاً ادغام عدد ۱۲۳ و عدد ۴۵۶ با یک جداکننده خط تیره (“-“) با فرمول `=123 & “-” & 456` نتیجه متنی “123-456” را خواهد داد نه یک مقدار عددی. این موضوع می تواند بر روی قابلیت هایی مانند مرتب سازی عددی فیلتر کردن با شرایط عددی (بزرگتر از کوچکتر از) و انجام محاسبات ریاضی بر روی ستون ادغام شده تأثیر بگذارد. اگر نیاز دارید که نتیجه ادغام شده به عنوان یک عدد در نظر گرفته شود (مثلاً برای ادغام بخش های یک کد عددی که بعداً باید در محاسبات استفاده شود) ممکن است نیاز به تبدیل نوع داده پس از ادغام داشته باشید. تابع VALUE می تواند یک رشته متنی حاوی اعداد را به مقدار عددی متناظر تبدیل کند. همچنین ضرب کردن نتیجه متنی در عدد ۱ نیز یک ترفند رایج برای تبدیل متن به عدد در اکسل است.
برای ادغام تاریخ ها نیز همین قاعده برقرار است. اگر تاریخ ها در ستون های مختلف (مثلاً سال ماه روز) قرار دارند و می خواهید آن ها را به صورت یک رشته متنی استاندارد (مانند “YYYY/MM/DD”) ادغام کنید فرمول هایی مانند & یا TEXTJOIN کارساز هستند. اما برای اطمینان از اینکه تاریخ ها با قالب بندی مناسب ادغام شوند (به خصوص اگر اعداد ماه یا روز تک رقمی هستند و نیاز به صفر پیشوند دارند) بهتر است قبل از ادغام از تابع TEXT برای قالب بندی اعداد به صورت متن استفاده کنید. تابع TEXT به شما اجازه می دهد یک عدد یا تاریخ را با یک قالب بندی مشخص به متن تبدیل کنید. مثلاً `=TEXT(A2,”00″)` عدد ۵ را به متن “05” تبدیل می کند. بنابراین برای ادغام سال (A2) ماه (B2) و روز (C2) با قالب “YYYY/MM/DD” می توانید از فرمول `TEXT(A2,”0000″) & “/” & TEXT(B2,”00″) & “/” & TEXT(C2,”00″)` استفاده کنید. Flash Fill و Power Query نیز معمولاً داده های عددی و تاریخ را به صورت متن ادغام می کنند اما در Power Query گزینه های دقیق تری برای کنترل نوع داده خروجی و قالب بندی قبل از ادغام وجود دارد.
در نهایت انتخاب روش مناسب برای ادغام ستون ها به نوع داده تعداد ستون ها نسخه اکسل شما و نیاز به به روزرسانی خودکار بستگی دارد. برای ادغام ساده دو ستون متنی با جداکننده عملگر & سریع و آسان است. برای ادغام محدوده ای از ستون ها با یک جداکننده TEXTJOIN (در نسخه های جدید) بهترین گزینه است. برای کارهای تکراری یا داده های بزرگ Power Query ابزاری قدرتمند و کارآمد است. و برای کارهای ساده و بدون فرمول نویسی Flash Fill یک راه حل هوشمندانه است.
مقایسه روش های مختلف ادغام ستون ها
برای انتخاب بهترین روش جهت ادغام ستون ها در اکسل مهم است که مزایا و معایب هر یک از رویکردهای مبتنی بر فرمول Flash Fill و Power Query را در نظر بگیریم. هر کدام از این روش ها برای سناریوهای خاصی مناسب تر هستند و درک تفاوت های آن ها به شما کمک می کند تا ابزار مناسب کارتان را انتخاب کنید.
روش های مبتنی بر فرمول ( & و TEXTJOIN): * مزایا: بسیار منعطف در افزودن جداکننده های مختلف و ترکیب متن ثابت با محتوای سلول ها. امکان انجام تغییرات و قالب بندی داده ها قبل از ادغام (مانند استفاده از تابع TEXT). در دسترس در تقریباً تمام نسخه های اکسل (&). * معایب: نیاز به نوشتن فرمول. نتایج وابسته به ستون های مبدأ هستند و برای مستقل شدن نیاز به تبدیل به مقدار ثابت دارند. نوشتن فرمول برای ادغام تعداد زیادی ستون با عملگر & می تواند طولانی و پیچیده شود (TEXTJOIN این مشکل را حل می کند). * کاربرد: ادغام دو یا چند ستون با جداکننده خاص نیاز به قالب بندی پیش از ادغام در دسترس بودن در نسخه های قدیمی اکسل.
روش Flash Fill: * مزایا: بسیار سریع و آسان برای الگوهای مشخص. بدون نیاز به فرمول نویسی. نتایج به صورت مقادیر ثابت هستند. * معایب: فقط برای الگوهای ساده و قابل تشخیص کار می کند. ممکن است برای الگوهای پیچیده یا نامنظم به درستی عمل نکند. نیاز به حداقل یک نمونه دستی از خروجی. در نسخه های قدیمی تر از اکسل 2013 موجود نیست. * کاربرد: چسباندن نام و نام خانوادگی جدا کردن بخش های متن تغییر قالب بندی ساده متنی زمانی که کاربر با فرمول راحت نیست.
روش Power Query: * مزایا: روشی ساختاریافته و قابل تکرار. مناسب برای داده های بزرگ و منابع خارجی. رابط کاربری بصری و بدون نیاز به فرمول نویسی در اکثر موارد. نتایج به صورت یک جدول جدید و مستقل بارگذاری می شوند. امکان انجام سایر تبدیل های داده قبل یا بعد از ادغام در همان پرس وجو. * معایب: ممکن است برای کارهای بسیار ساده (مثل ادغام دو ستون برای یک بار) کمی زمان بر به نظر برسد. مفهوم پرس وجو و محیط Power Query Editor نیاز به آشنایی اولیه دارد. * کاربرد: ادغام ستون ها به صورت منظم و تکراری کار با داده های بزرگ یا از منابع مختلف نیاز به انجام چندین مرحله تبدیل داده زمانی که به روزرسانی خودکار نتایج مورد نیاز است.
انتخاب بین این روش ها بستگی به نیاز خاص شما حجم داده ها تکراری بودن کار و سطح آشنایی شما با اکسل دارد. برای ادغام سریع نام و نام خانوادگی در یک لیست کوتاه Flash Fill عالی است. برای ادغام دو ستون با یک جداکننده خاص و کنترل دقیق عملگر & مناسب است. برای ادغام چندین ستون در نسخه های جدید TEXTJOIN بهترین گزینه فرمولی است. و برای فرآیندهای تکراری داده های بزرگ یا نیاز به تبدیل های پیچیده تر Power Query قدرتمندترین راه حل است.
هر روش ادغام ستون در اکسل (فرمول Flash Fill Power Query) مزایا و معایب خاص خود را دارد. فرمول ها انعطاف پذیری بالایی دارند Flash Fill برای کارهای ساده و سریع بدون فرمول عالی است و Power Query ابزاری قدرتمند برای داده های بزرگ و فرآیندهای قابل تکرار است. انتخاب روش به نیازهای خاص شما بستگی دارد.
رسیدگی به سلول های خالی هنگام ادغام
یکی از چالش هایی که ممکن است هنگام ادغام ستون ها با آن روبرو شوید وجود سلول های خالی در ستون های مبدأ است. نحوه برخورد با این سلول های خالی می تواند بر نتیجه نهایی تأثیر بگذارد و لازم است بدانید هر روش ادغام چگونه با آن ها برخورد می کند و چگونه می توانید رفتار مورد نظر خود را تنظیم کنید.
در روش های فرمول نویسی (& و CONCATENATE): هنگامی که از عملگر & برای ادغام سلول ها استفاده می کنید و یکی از سلول های مبدأ خالی است آن سلول خالی به عنوان یک رشته متنی خالی (“”) در نظر گرفته می شود. اگر جداکننده قبل یا بعد از سلول خالی قرار گرفته باشد جداکننده همچنان در نتیجه نهایی ظاهر خواهد شد. به عنوان مثال اگر در فرمول `=A2 & ” ” & B2` سلول A2 حاوی “علی” و سلول B2 خالی باشد نتیجه “علی ” خواهد بود (فاصله بعد از نام باقی می ماند). اگر A2 خالی و B2 حاوی “احمدی” باشد نتیجه ” احمدی” خواهد بود (فاصله قبل از نام خانوادگی باقی می ماند). این ممکن است منجر به ظاهر شدن فاصله ها یا جداکننده های اضافی در نتیجه نهایی شود که مطلوب نیست.
برای حل این مشکل با استفاده از عملگر & می توانید از توابع منطقی مانند IF استفاده کنید تا بررسی کنید آیا سلول خالی است یا خیر و فقط در صورت خالی نبودن محتوای آن سلول و جداکننده مربوط به آن را اضافه کنید. این کار می تواند فرمول ها را پیچیده تر کند.
در تابع TEXTJOIN: یکی از مزایای بزرگ تابع TEXTJOIN آرگومان `ignore_empty` است. اگر این آرگومان را برابر TRUE قرار دهید (که حالت پیش فرض و رایج است) تابع TEXTJOIN به طور خودکار سلول های خالی را در محدوده انتخاب شده نادیده می گیرد و جداکننده را فقط بین محتوای سلول هایی که خالی نیستند قرار می دهد. این باعث می شود نتایج ادغام شده تمیزتر باشند و از ظاهر شدن جداکننده های اضافی جلوگیری می کند. این ویژگی TEXTJOIN را برای ادغام محدوده هایی که ممکن است شامل سلول های خالی باشند به گزینه ای بسیار کارآمد تبدیل می کند.
در Flash Fill: Flash Fill بر اساس الگویی که شما ارائه می دهید عمل می کند. اگر در نمونه ای که تایپ می کنید سلول های مبدأ خالی وجود داشته باشند Flash Fill سعی می کند این الگو را در سطر های بعدی نیز تکرار کند. معمولاً Flash Fill در برخورد با سلول های خالی هوشمند عمل می کند و اگر الگوی شما نشان دهد که سلول های خالی باید نادیده گرفته شوند (مثلاً در نمونه اولیه نام و نام خانوادگی اگر نام میانی خالی باشد شما آن را در خروجی تایپ نمی کنید) Flash Fill این رفتار را در سطر های بعدی نیز اعمال خواهد کرد.
در Power Query: در Power Query هنگامی که ستون ها را ادغام می کنید به طور پیش فرض سلول های خالی (Null values در Power Query) نادیده گرفته می شوند و جداکننده فقط بین مقادیری که خالی نیستند قرار می گیرد. این رفتار شبیه به TEXTJOIN با آرگومان `ignore_empty` برابر TRUE است و معمولاً خروجی تمیزی را ارائه می دهد. Power Query همچنین گزینه های پیشرفته تری برای مدیریت مقادیر خالی قبل یا بعد از ادغام ارائه می دهد مانند جایگزینی مقادیر خالی با یک متن خاص.
به طور خلاصه اگر از TEXTJOIN یا Power Query استفاده می کنید مدیریت سلول های خالی معمولاً به سادگی و به طور خودکار انجام می شود. در روش فرمول نویسی با & باید با استفاده از توابع کمکی مانند IF منطق لازم برای نادیده گرفتن سلول های خالی و جداکننده های اضافی را به فرمول خود اضافه کنید.
نکات تکمیلی و ترفندها
علاوه بر روش های اصلی که برای ادغام ستون ها در اکسل شرح داده شد نکات و ترفندهای دیگری نیز وجود دارد که می تواند فرآیند کار شما را بهبود بخشد و انعطاف پذیری بیشتری به شما بدهد.
استفاده از تابع CONCAT: تابع CONCAT نیز مشابه CONCATENATE و عملگر & برای اتصال رشته های متنی به کار می رود و در نسخه های جدیدتر اکسل معرفی شده است. مزیت CONCAT نسبت به CONCATENATE این است که می تواند یک محدوده از سلول ها را به عنوان ورودی بپذیرد هرچند قابلیت تعیین جداکننده و نادیده گرفتن سلول های خالی مانند TEXTJOIN را ندارد. فرمول `=CONCAT(A1:C1)` محتوای سلول های A1 تا C1 را بدون هیچ جداکننده ای به هم می چسباند.
فرمت بندی داده ها قبل از ادغام: همانطور که در مورد تاریخ ها اشاره شد قبل از ادغام ستون ها به خصوص ستون های عددی یا تاریخ ممکن است نیاز به قالب بندی محتوای آن ها به صورت متنی دلخواه داشته باشید. تابع TEXT ابزار اصلی برای این کار است. این تابع به شما اجازه می دهد تا فرمت نمایش اعداد تاریخ ها زمان ها یا حتی مقادیر پولی را کنترل کنید و آن ها را به صورت یک رشته متنی با فرمت مشخص برای ادغام آماده کنید. این کار به خصوص زمانی مهم است که می خواهید مطمئن شوید اعداد با تعداد مشخصی اعشار یا تاریخ ها با ترتیب خاصی از سال ماه و روز ظاهر می شوند.
افزودن کاراکترهای خاص: گاهی اوقات ممکن است بخواهید کاراکترهای خاصی مانند Enter (برای ایجاد خط جدید در یک سلول) یا Tab را در رشته ادغام شده خود قرار دهید. برای افزودن کاراکتر Enter در یک فرمول می توانید از تابع `CHAR(10)` استفاده کنید. برای مشاهده نتیجه این کاراکتر در سلول باید گزینه Wrap Text (چینش متن) برای آن سلول فعال باشد. این ترفند برای ایجاد آدرس های چندخطی یا لیست های آیتم در یک سلول مفید است. کاراکتر Tab نیز با `CHAR(9)` قابل اضافه شدن است هرچند کاربرد کمتری در ادغام ساده دارد.
مدیریت خطاها: هنگام استفاده از فرمول ها ممکن است با خطاهایی مانند #VALUE! یا #NAME? روبرو شوید. خطای #VALUE! معمولاً زمانی رخ می دهد که فرمول شما سعی در انجام عملیاتی بر روی نوع داده ای نامناسب دارد مثلاً تلاش برای انجام یک عملیات عددی بر روی متنی که قابل تبدیل به عدد نیست. خطای #NAME? زمانی اتفاق می افتد که اکسل نام یک تابع یا محدوده را در فرمول شما تشخیص نمی دهد که ممکن است به دلیل املای نادرست نام تابع یا استفاده از تابعی باشد که در نسخه اکسل شما موجود نیست (مانند استفاده از TEXTJOIN در نسخه های قبل از 2019). برای جلوگیری یا مدیریت این خطاها می توانید از توابع مدیریت خطا مانند IFERROR استفاده کنید که به شما اجازه می دهد در صورت بروز خطا مقدار یا متن دلخواهی را نمایش دهید.
با در نظر گرفتن این نکات و ترفندها می توانید عملیات ادغام ستون ها در اکسل را با دقت سرعت و انعطاف پذیری بیشتری انجام دهید و خروجی نهایی را دقیقاً مطابق با نیازهای خود تنظیم کنید.
فرق ادغام ستون با ادغام سلول در اکسل
یکی از رایج ترین اشتباهات کاربران تازه کار اکسل خلط مفهوم “ادغام ستون” با “ادغام سلول” است. در حالی که هر دو عبارت شامل کلمه “ادغام” هستند اما به دو عملیات کاملاً متفاوت با اهداف و نتایج مختلف اشاره دارند.
ادغام سلول (Merge & Center): این عملیات با استفاده از دکمه Merge & Center که معمولاً در تب Home و گروه Alignment قرار دارد انجام می شود. ادغام سلول برای ترکیب *بصری* دو یا چند سلول مجاور در یک سلول بزرگتر به کار می رود. هدف اصلی آن قالب بندی و زیباسازی ظاهر صفحه گسترده است مثلاً برای ایجاد یک سربرگ بزرگ برای چند ستون داده یا ترکیب سلول ها برای قرار دادن عنوان در وسط یک محدوده. نکته بسیار مهم و کلیدی در مورد ادغام سلول این است که اگر سلول های انتخابی حاوی داده باشند اکسل یک پیام هشدار نمایش می دهد و پس از تأیید کاربر *فقط محتوای سلول بالا سمت چپ* (Upper-Left cell) را نگه می دارد و محتوای تمام سلول های دیگر را *حذف می کند*. بنابراین ادغام سلول برای ترکیب *داده ها* از چندین سلول بدون از دست رفتن آن ها مناسب *نیست*. نتیجه این عملیات یک سلول واحد بزرگتر است که در ظاهر چند سلول را پوشش می دهد.
ادغام ستون (Merging Data from Columns): این عملیات که موضوع اصلی این مقاله است به فرآیند ترکیب *محتوای* داده های موجود در ستون های مختلف در *سطرهای متناظر* آن ها و قرار دادن نتیجه در یک ستون *جدید* اشاره دارد. هدف از ادغام ستون جمع آوری اطلاعات پراکنده در یک مکان واحد برای استفاده های بعدی مانند نمایش نام کامل ترکیب اجزای آدرس یا ساخت کدهای ترکیبی است. این فرآیند با استفاده از روش هایی مانند فرمول نویسی (& TEXTJOIN) Flash Fill یا Power Query انجام می شود. در این روش هیچ داده ای از دست نمی رود؛ بلکه داده ها از ستون های مبدأ خوانده شده ترکیب شده و در ستون جدیدی قرار می گیرند. ستون های اصلی حاوی داده های اولیه همچنان وجود دارند و می توانید پس از اطمینان از صحت نتایج در ستون جدید آن ها را حذف کنید (البته اگر از فرمول استفاده کرده اید باید ابتدا نتایج را به مقدار ثابت تبدیل کنید).
به زبان ساده ادغام سلول یک عملیات *قالب بندی بصری* است که می تواند منجر به از دست رفتن داده شود در حالی که ادغام ستون یک عملیات *ترکیب داده ای* است که هدف آن حفظ و جمع آوری اطلاعات از چندین منبع در یک مکان واحد است. درک این تفاوت برای انجام صحیح عملیات مورد نظر در اکسل ضروری است.
ادغام ستون ها برای ایجاد کدهای یکتا یا شناسه ترکیبی
یکی دیگر از کاربردهای مهم ادغام ستون ها در اکسل ایجاد کدهای یکتا یا شناسه های ترکیبی بر اساس داده های موجود در ستون های مختلف است. این نیاز در سناریوهایی مانند مدیریت موجودی (ترکیب کد محصول و رنگ یا اندازه) مدیریت مشتریان (ترکیب کد مشتری و تاریخ سفارش) یا ایجاد شناسه های منحصر به فرد برای رکوردها در پایگاه های داده مطرح می شود. با ترکیب داده ها از ستون های مختلف می توان یک شناسه جدید و منحصر به فرد برای هر سطر ایجاد کرد که برای شناسایی دقیق تر و جلوگیری از تکرار داده ها مفید است.
برای مثال فرض کنید جدولی دارید که شامل ستون های “کد محصول” (مثلاً ABC) “رنگ” (مثلاً قرمز) و “اندازه” (مثلاً L) است و می خواهید یک کد شناسه موجودی یکتا مانند “ABC-RED-L” ایجاد کنید. برای این کار می توانید از روش های ادغام ستون استفاده کنید. با استفاده از عملگر & فرمول به شکل `=A2 & “-” & B2 & “-” & C2` خواهد بود (فرض بر این است که کد محصول در A2 رنگ در B2 و اندازه در C2 است). در این فرمول از کاراکتر خط تیره (“-“) به عنوان جداکننده استفاده شده است. اگر از اکسل 2019 به بالا استفاده می کنید و تعداد ستون هایی که می خواهید برای ساخت شناسه ترکیب کنید زیاد است تابع TEXTJOIN با تعیین جداکننده خط تیره نیز گزینه مناسبی است: `=TEXTJOIN(“-“, TRUE, A2:C2)`.
ایجاد کدهای یکتا با ادغام ستون ها می تواند در فرآیندهای تطبیق داده ها (Data Matching) نیز بسیار مفید باشد. اگر در دو جدول مختلف نیاز به تطبیق رکوردها بر اساس ترکیبی از چند ستون دارید می توانید با ایجاد یک ستون شناسه ترکیبی در هر دو جدول و سپس استفاده از توابعی مانند VLOOKUP یا XLOOKUP بر روی این ستون های جدید رکوردهای متناظر را پیدا کنید. این روش اغلب دقیق تر از تطبیق بر اساس یک ستون واحد است به خصوص زمانی که ممکن است در ستون های منفرد تکرار وجود داشته باشد اما ترکیب آن ها منحصر به فرد باشد.
هنگام ایجاد کدهای ترکیبی به نوع داده های مبدأ و قالب بندی آن ها توجه کنید. اگر ستون های مبدأ شامل اعداد یا تاریخ ها هستند مطمئن شوید که قالب بندی آن ها به گونه ای است که در کد نهایی خوانا و قابل استفاده باشند. استفاده از تابع TEXT قبل از ادغام می تواند برای افزودن صفرهای پیشوند به اعداد فرمت بندی تاریخ ها به صورت استاندارد یا کنترل تعداد ارقام اعشار مفید باشد. پس از ایجاد ستون کدهای ترکیبی با استفاده از فرمول حتماً نتایج را به مقادیر ثابت تبدیل کنید تا این کدها مستقل از داده های مبدأ شوند و بتوانید از آن ها به عنوان شناسه های ثابت استفاده کنید.
Power Query نیز ابزار قدرتمندی برای ایجاد کدهای ترکیبی است. در محیط Power Query Editor پس از انتخاب ستون های مورد نظر و استفاده از قابلیت Merge Columns می توانید جداکننده دلخواه خود را تعیین کرده و نام ستون جدید را مشخص کنید. مزیت Power Query در این زمینه قابلیت انجام سایر تبدیل ها (مانند تبدیل حروف به بزرگ یا کوچک حذف فاصله های اضافی) قبل از ادغام و همچنین قابلیت به روزرسانی پرس وجو در صورت تغییر داده های مبدأ است.
با استفاده از این تکنیک ها می توانید با ادغام هوشمندانه داده های موجود در ستون های مختلف شناسه های یکتا و معناداری ایجاد کنید که برای مدیریت و تحلیل داده های شما بسیار ارزشمند خواهند بود.
سوالات متداول
چگونه دو ستون را در اکسل با فاصله یا جداکننده ادغام کنیم؟
برای ادغام دو ستون با فاصله یا جداکننده می توانید از فرمول هایی مانند `=A1 & ” ” & B1` (برای فاصله) یا `=A1 & “,” & B1` (برای ویرگول) استفاده کنید. در نسخه های جدید اکسل تابع TEXTJOIN نیز با تعیین جداکننده در آرگومان اول آن کارآمد است مانند `=TEXTJOIN(” “, TRUE, A1:B1)`.
چگونه دو ستون را در اکسل بدون فرمول ادغام کنیم؟
ساده ترین راه بدون فرمول برای ادغام ستون ها استفاده از قابلیت Flash Fill است. کافیست در اولین سلول ستون مقصد خروجی مورد نظر خود را دستی تایپ کنید و سپس با فشردن Ctrl + E اکسل به طور خودکار بقیه سلول ها را بر اساس الگوی شما پر می کند.
فلش فیل (Flash Fill) در کدام نسخه های اکسل موجود است؟
قابلیت Flash Fill از نسخه اکسل 2013 به بعد معرفی شده است و در تمام نسخه های جدیدتر اکسل از جمله Microsoft 365 در دسترس کاربران قرار دارد. این ابزار در نسخه های قدیمی تر از 2013 موجود نیست.
آیا می توان دو ستون عدد را در اکسل ادغام کرد؟
بله می توانید ستون های عددی را ادغام کنید اما نتیجه نهایی یک رشته متنی خواهد بود نه یک عدد. برای ادغام از همان روش های فرمول (& TEXTJOIN) یا Flash Fill و Power Query استفاده می شود. در صورت نیاز به انجام محاسبات بعدی باید نتیجه را به مقدار عددی تبدیل کنید.
فرق ادغام ستون با ادغام سلول در اکسل چیست؟
ادغام سلول (Merge & Center) یک عملیات قالب بندی بصری است که سلول ها را ترکیب می کند و معمولاً منجر به از دست رفتن داده می شود. ادغام ستون به ترکیب داده های موجود در ستون های مختلف در یک ستون جدید اشاره دارد که با استفاده از فرمول ها Flash Fill یا Power Query انجام شده و داده ها حفظ می شوند.
چگونه نتایج ادغام دو ستون را به صورت مقدار ثابت نگه داریم؟
اگر از فرمول برای ادغام استفاده کرده اید نتایج وابسته به ستون های مبدأ هستند. برای ثابت نگه داشتن آن ها ستون حاوی فرمول ها را کپی کرده سپس در همان محل راست کلیک کرده و از Paste Special گزینه Values را انتخاب کنید. این کار فرمول ها را با مقادیر حاصل از آن ها جایگزین می کند.
خطا: هیچ نوشته مرتبطی پیدا نکرد.