یکی از کاربردهای اکسل، ایجاد لیست اشخاص و اطلاعات مربوط به ایشان است. اگر نام و نام خانوادگی در یک ستون وارد شده باشد، می‌توان آن را به سادگی به دو ستون مجزا تبدیل کرد. در واقع در نرم‌افزار Excel مجموعه‌ی آفیس مایکروسافت قابلیت تبدیل کردن جملات و عبارت‌هایی که با اسپیس، خط فاصله، ویرگول و غیره جدا شده‌اند به چند عبارت وجود دارد.
در این مقاله به موضوع خاصی که جدا کردن نام و نام خانوادگی و تبدیل به دو ستون است می‌پردازیم. روش جدا کردن تبدیل کردن عبارت‌های دیگر به چند ستون، مشابه است.
چگونه نام و نام خانوادگی افراد را در اکسل جدا کنیم؟
یکی از ابزارهای کاربردی اکسل، Text to Column Wizard نام دارد که با پرسش چند سوال ساده، متن را جدا کرده و به چند ستون تبدیل می‌کند.
دقت کنید که بهتر است پس از ستونی که نام و نام خانوادگی در آن وارد شده، ستون خالی وجود داشته باشد و سپس مراحل پردازش متن را شروع کنید. البته اگر ستون خالی وجود نداشته باشد هم می‌توانید مقصد دلخواهی را برای درج کردن اطلاعات انتخاب کنید.
اگر نام و نام خانوادگی افراد را با اسپیس یا فاصله جدا کرده‌اید، مراحل زیر را طی کنید:
- ستون یا سلول‌هایی که حاوی نام و نام خانوادگی است را انتخاب کنید.
- در نوار ابزار بالای پنجره‌ی اکسل روی تب Data کلیک کنید و در این تب روی Text to Columns کلیک کنید.
- در پنجره‌ای که باز می‌شود، حالت Delimited را انتخاب کنید و روی Next کلیک کنید.
- در مرحله‌ی بعدی تیک که انتخاب Delimiter است، تیک Tab را حذف کنید و گزینه‌ی Space را انتخاب کنید. روی Next کلیک کنید.
- در بخش Column data format یا فرمت ستون داده، حالت Text را انتخاب کنید. در صورت نیاز می‌توانید ستون مقصد را با کلیک روی Destination به صورت دلخواه انتخاب کنید تا ستون پس از ستون نام و نام خانوادگی با داده‌های جدید بازنویسی نشود.
- در پایین و بخش Data preview که پیش‌نمایشی از نتیجه‌ی جدا کردن عبارت‌ها ارایه می‌شود، خروجی را بررسی کنید و در نهایت روی Finish کلیک کنید.
با این روش ساده می‌توانید عبارت‌های دو بخشی شامل نام و نام خانوادگی را جدا کنید اما اگر نام و نام خانوادگی دارای سه بخش باشد و هر بخش با فاصله از دیگری جدا شده باشد، طبعاً به جای دو ستون، سه ستون حاصل می‌شود که مطلوب نیست. برای این موارد خاص، علاوه بر روش دستی جدا کردن نام و نام خانوادگی، دو روش دیگر وجود دارد:
- روش اصلی و اساسی این است که نام‌ها یا نام‌های خانوادگی دو بخشی را به جای اسپیس، با نیم‌فاصله یا با خط فاصله و غیره جدا کنید.
- روش دوم استفاده از فرمول‌نویسی و توابع پردازش متن و رشته در اکسل است که در ادامه بررسی خواهیم کرد و به جز مسأله‌ی پیچیدگی کار، محدودیت‌هایی دارد.
تبدیل اسم و فامیلی که با ویرگول جدا شده به دو سلول
اگر نام و نام خانوادگی افراد را با ویرگول جدا کرده باشید، در مرحله‌ی انتخاب Delimiters می‌بایست به جای انتخاب Space، تیک Comma را بزنید. البته اگر پس از ویرگول‌ها اسپیس نیز استفاده شده باشد، می‌بایست تیک هر دو گزینه را بزنید و همین‌طور تیک گزینه‌ی Treat consecutive delimiters as one را اضافه کنید تا این موارد جداکننده‌ی پشت‌سر‌هم به عنوان یک کاراکتر جداکننده در نظر گرفته شوند.
مراحل بعدی مشابه حالت قبلی است و به سادگی سلول‌ها به دو ستون تبدیل می‌شود.
جدا کردن نام افراد و حذف برخی داده‌ها
فرض کنید برخی از نام‌ها چندبخشی هستند و نمی‌خواهید پیشوندها یا پسوندها را حفظ کنید. در این صورت روش جدا کردن اسامی و تبدیل به چند ستون، مثل حالت‌های قبلی است با این تفاوت که در مرحله‌ی آخر تنظیمات متفاوت است:
- در آخرین مرحله که انتخاب فرمت ستون‌های داده است، تیک گزینه‌ی Do not import column (Skip) را بزنید.
- در بخش پیش‌نمایش ستون‌های داده، روی ستونی که می‌خواهید از خروجی حذف شود، کلیک کنید تا هایلایت شود.
- در نهایت روی Finish کلیک کنید.
به این ترتیب نام و نام خانوادگی به چند ستون تبدیل می‌شود اما بخش میانی یا ابتدا و یا انتها حذف می‌شود.
استفاده از توابع پردازش متن و رشته برای جدا کردن عبارت‌ها
همان‌طور که دیدید ابزار Text to Column Wizard کار تبدیل متن به چند ستون را بسیار ساده و سریع انجام می‌دهد و در اغلب کاربردها بسیار عالی است اما بی‌نقص نیست. همان‌طور که اشاره کردیم اگر نام و نام خانوادگی دارای پیشوندها و پسوندهایی باشد و به جای دو بخش، چند بخش داشته باشد، نمی‌توان با این ابزار به نتیجه‌ی مطلوب رسید.
برای این موارد خاص می‌توانید فرمول‌نویسی کنید هر چند در حالت‌های پیچیده، فرمول‌نویسی هم پاسخ‌گو نیست و باید شخصی که نام و نام خانوادگی را وارد می‌کند، شیوه‌ی وارد کردن داده‌ها را تصحیح کند تا مشکلات بعدی به وجود نیاید.
اگر بخواهید متن و رشته را به صورت دلخواه و با شیوه‌ی خاصی پردازش کنید، می‌بایست از توابع استفاده کنید و فرمول موردنظر را با صبر و حوصله بنویسید. در واقع محدودیت‌های ابزار Text to Column Wizard، کاربر را مجبور به فرمول‌نویسی می‌کند که وقت‌گیر و پیچیده‌تر است.
جدا کردن اسم و فامیل سه بخشی در اکسل
برای درج کردن اسم افراد، ابتدا سلولی که قرار است حاوی نام فرد باشد را انتخاب کنید و اگر نام و نام خانوادگی در سلولی مثل A2 واقع شده، فرمول زیر را در سلول نام بنویسید:
=LEFT(A2,FIND(" ",A2,1)-1)
و برای نام خانوادگی نیز از فرمول زیر استفاده کنید:
=RIGHT(A2,LEN(A2)-FIND(" ",A2,1))
بخش میانی نام و نام خانوادگی نیز با فرمول زیر محاسبه می‌شود:
=MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)-SEARCH(" ",A2,1))
پس از فرمول‌نویسی در یک سلول، می‌توانید با روش کپی و پیست یا با روش درگ کردن مربع کوچک پایین سلول‌ها، فرمول را در سلول‌های پایین‌تر یا بالاتر کپی کنید.
برای اطلاعات تکمیلی در مورد توابع استفاده شده، به مقاله‌ی زیر رجوع کنید:
جدا کردن نام دارای پیشوند با حذف کردن پیشوند
اگر برخی از نام‌ها دارای پیشوند هستند، برای محاسبه کردن مقدار نام از فرمول زیر استفاده کنید و البته دقت کنید که نام و نام خانوادگی فرد در سلول A2 درج شده است و لذا باید به جای A2، نام سلول منبع را قرار بدهید:
=MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)-(SEARCH(" ",A2,1)+1))
و برای نام خانوادگی نیز از فرمول زیر استفاده کنید:
=RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,1)+1))
جدا کردن نام‌های دارای پسوند و قرار دادن پسوند در ستون مجزا
در این موارد می‌توانید از فرمول زیر برای درج کردن نام استفاده کنید:
=LEFT(A2, SEARCH(" ",A2,1))
و برای نام خانوادگی نیز از فرمول زیر استفاده کنید:
=MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)-(SEARCH(" ",A2,1)+1))
و پیشوند نیز با فرمول زیر به دست می‌آید:
=RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,1)+1))
تبدیل کردن اسم‌های دو بخشی افراد به چند ستون
در این حالت برای نام از فرمول زیر استفاده کنید:
=LEFT(A2, SEARCH(" ",A2,SEARCH(" ",A2,1)+1))
و برای نام خانوادگی از فرمول زیر استفاده کنید:
=RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)+1))
جدا کردن نام خانوادگی دو بخشی
برای این موارد نام فرد با فرمول زیر محاسبه می‌شود:
=LEFT(A2, SEARCH(" ",A2,1))
و نام خانوادگی از رابطه‌ی زیر به دست می‌آید:
=RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1))
جدا کردن نام خانوادگی سه‌بخشی در اکسل
برای نام افراد از رابطه‌ی زیر استفاده کنید:
=LEFT(A2, SEARCH(" ",A2,1))
و برای نام خانوادگی از فرمول زیر استفاده کنید:
=RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1))
به این ترتیب در حالت‌های مختلف می‌توانید نام و نام خانوادگی اشخاص را با فرمول‌های مختلف، جدا کنید.
زمانی که لیست نام و نام خانوادگی کاملاً پیچیده است و به عنوان مثال گاهی نام شخص دو بخشی است و گاهی نام خانوادگی دو بخشی یا سه بخشی است، تشخیص اینکه کدام قسمت از عبارت، اسم است و کدام بخش فامیلی است، با روش فرمولی امکان‌پذیر نیست. در این موارد می‌بایست پیش‌بینی لازم را کرده باشید و برای جدا کردن این دو بخش، به جای استفاده از اسپیس، از ویرگول یا خط فاصله استفاده کنید. در این صورت جدا کردن این بخش‌ها با ابزار Text to Column Wizard بسیار ساده و سریع انجام می‌شود.
makeuseofسیاره‌ی آی‌تی