ظرافت های کار با خط شکن در اکسل

خط در همان سلول، با استفاده از میانبر صفحه کلید اضافه می شود دگرساز+وارد یک چیز بسیار رایج و رایج است. گاهی اوقات توسط خود کاربران ساخته می شوند تا به متن طولانی زیبایی ببخشند. گاهی اوقات چنین انتقال هایی به طور خودکار هنگام تخلیه داده ها از هر برنامه کاری اضافه می شوند (سلام 1C، SAP، و غیره) مشکل این است که شما نه تنها باید چنین جداول را تحسین کنید، بلکه باید با آنها کار کنید - و سپس این انتقال کاراکترهای نامرئی می تواند یک مسئله. و ممکن است تبدیل نشوند - اگر بدانید که چگونه آنها را به درستی مدیریت کنید.

بیایید این موضوع را با جزئیات بیشتری بررسی کنیم.

حذف خطوط شکسته با تعویض

اگر باید از شر خط فاصله خلاص شویم، اولین چیزی که معمولاً به ذهن می رسد تکنیک کلاسیک "یافتن و جایگزین کردن" است. متن را انتخاب کنید و سپس پنجره جایگزین را با یک میانبر صفحه کلید فراخوانی کنید کلیدهای Ctrl+H و یا از طریق صفحه اصلی – یافتن و انتخاب – جایگزینی (صفحه اصلی - یافتن و انتخاب - جایگزینی). یک ناهماهنگی - نحوه ورود به فیلد بالا خیلی واضح نیست برای پیدا کردن (چه چیز را پیدا کنم) شخصیت شکست خط نامرئی ما دگرساز+وارد در اینجا، متأسفانه، دیگر کار نمی کند، کپی کردن این نماد به طور مستقیم از سلول و چسباندن آن در اینجا نیز ناموفق است.

یک ترکیب کمک خواهد کرد کلیدهای Ctrl+J - این جایگزین است دگرساز+وارد در کادرهای محاوره ای اکسل یا فیلدهای ورودی:

لطفا توجه داشته باشید که پس از قرار دادن مکان نما چشمک زن در قسمت بالا و فشار دادن کلیدهای Ctrl+J - هیچ چیزی در خود میدان ظاهر نمی شود. نترسید - این طبیعی است، نماد نامرئی است 🙂

به قسمت پایین جایگزین (تعویض با) یا چیزی را وارد نکنید، یا یک فاصله را وارد کنید (اگر بخواهیم نه تنها خط تیره را حذف کنیم، بلکه آنها را با یک فاصله جایگزین کنیم تا خطوط در یک کل واحد به هم نچسبند). فقط دکمه را فشار دهید همه چیز را جایگزین کنید (جایگزین همه) و خط تیره های ما ناپدید می شوند:

نیسان: پس از انجام تعویض وارد شده با کلیدهای Ctrl+J شخصیت نامرئی در میدان باقی می ماند برای پیدا کردن و ممکن است در آینده تداخل ایجاد کند - فراموش نکنید که با قرار دادن مکان نما در این قسمت و چندین بار فشار دادن کلیدها (برای اطمینان) آن را حذف کنید. حذف и Backspace.

حذف خطوط شکسته با یک فرمول

اگر نیاز به حل مشکل با فرمول دارید، می توانید از تابع داخلی استفاده کنید چاپ (تمیز)، که می تواند متن را از همه کاراکترهای غیرقابل چاپ پاک کند، از جمله شکستگی خط بد ما:

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

جایگزین کردن شکست های خط با یک فرمول

و اگر می خواهید نه فقط حذف کنید، بلکه جایگزین کنید دگرساز+وارد به عنوان مثال، در یک فضا، سپس یک ساختار دیگر، کمی پیچیده تر مورد نیاز است:

برای تنظیم خط فاصله نامرئی از تابع استفاده می کنیم سمبل (CHAR)، که یک کاراکتر را با کد خود (10) خروجی می دهد. و سپس تابع جایگزین (جایگزین) خط تیره های ما را در داده های منبع جستجو می کند و آنها را با هر متن دیگری، به عنوان مثال، با فاصله جایگزین می کند.

تقسیم به ستون ها با شکست خط

ابزاری بسیار آشنا و بسیار کاربردی متن به ستون از زبانه داده ها (داده ها - متن به ستون) همچنین می تواند با شکستن خطوط عالی کار کند و متن را از یک سلول به چند سلول تقسیم کند و آن را با شکستن آن به چند خانه تقسیم کند دگرساز+وارد. برای انجام این کار، در مرحله دوم جادوگر، باید یک نوع از کاراکتر جداکننده سفارشی را انتخاب کنید. دیگر (سفارشی) و از میانبر صفحه کلیدی که قبلاً می شناسیم استفاده کنید کلیدهای Ctrl+J به عنوان جایگزین دگرساز+وارد:

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

پس از کلیک روی بعدی (بعد) و با طی کردن هر سه مرحله ویزارد به نتیجه دلخواه میرسیم:

لطفاً توجه داشته باشید که قبل از انجام این عملیات، لازم است تعداد کافی ستون خالی در سمت راست ستون تقسیم شده وارد کنید تا متن حاصل، مقادیر (قیمت‌های) سمت راست را بازنویسی نکند.

با Alt + Enter از طریق Power Query به خطوط تقسیم کنید

کار جالب دیگر این است که متن چند خطی را از هر سلول نه به ستون، بلکه به خطوط تقسیم کنید:

انجام این کار به صورت دستی زمان زیادی می برد، با فرمول ها دشوار است، همه نمی توانند ماکرو بنویسند. اما در عمل، این مشکل بیشتر از آنچه ما دوست داریم رخ می دهد. ساده ترین و آسان ترین راه حل استفاده از افزونه Power Query برای این کار است که از سال 2016 در اکسل ساخته شده است و برای نسخه های قبلی 2010-2013 می توان آن را کاملاً رایگان از وب سایت مایکروسافت دانلود کرد.

برای بارگیری داده های منبع در Power Query، ابتدا باید آن را با یک میانبر صفحه کلید به یک «جدول هوشمند» تبدیل کنید. کلیدهای Ctrl+T یا با دکمه قالب بندی به صورت جدول برگ صفحه اصلی (صفحه اصلی - قالب بندی به عنوان جدول). اگر به دلایلی نمی خواهید یا نمی توانید از "جدول های هوشمند" استفاده کنید، می توانید با موارد "احمقانه" کار کنید. در این حالت فقط محدوده اصلی را انتخاب کرده و در برگه نامی برای آن بگذارید فرمول ها - مدیر نام - جدید (فرمول ها - مدیر نام - جدید).

پس از آن، در زبانه داده ها (اگر اکسل 2016 یا جدیدتر دارید) یا روی برگه پرس و جو برق (اگر اکسل 2010-2013 دارید) می توانید روی دکمه کلیک کنید از جدول / محدوده (از جدول/محدوده)برای بارگذاری جدول خود در ویرایشگر Power Query:

پس از بارگذاری، ستونی را با متن چند خطی در سلول ها انتخاب کنید و دستور را در تب Main انتخاب کنید ستون تقسیم - توسط جداکننده (صفحه اصلی - تقسیم ستون - بر اساس جداکننده):

به احتمال زیاد، Power Query به طور خودکار اصل تقسیم را تشخیص می دهد و خود نماد را جایگزین می کند #(lf) کاراکتر تغذیه خط نامرئی (lf = تغذیه خط = تغذیه خط) در قسمت ورودی جداکننده. در صورت لزوم، کاراکترهای دیگر را می توان از لیست کشویی در پایین پنجره انتخاب کرد، اگر ابتدا کادر را علامت بزنید. تقسیم با شخصیت های خاص (تقسیم بر اساس کاراکترهای خاص).

به طوری که همه چیز به ردیف تقسیم می شود و نه ستون - فراموش نکنید که انتخابگر را تغییر دهید ردیف (بر اساس ردیف) در گروه گزینه های پیشرفته

تنها چیزی که باقی می ماند این است که روی آن کلیک کنید OK و به آنچه می خواهید برسید:

جدول تمام شده را می توان با استفاده از دستور دوباره بر روی صفحه بارگیری کرد بستن و بارگیری – بستن و بارگیری در… برگ صفحه اصلی (صفحه اصلی - بستن و بارگیری - بستن و بارگیری به…).

توجه به این نکته ضروری است که هنگام استفاده از Power Query، باید به خاطر داشته باشید که وقتی داده های منبع تغییر می کند، نتایج به طور خودکار به روز نمی شوند، زیرا. اینها فرمول نیستند برای آپدیت باید روی جدول نهایی شیت کلیک راست کرده و دستور را انتخاب کنید به روز رسانی و ذخیره (تازه کردن) یا دکمه را فشار دهید همه را بروز رسانی کن برگ داده ها (داده ها - بازخوانی همه).

ماکرو برای تقسیم به خطوط با Alt+Enter

برای تکمیل تصویر به حل مشکل قبلی به کمک ماکرو نیز اشاره می کنیم. ویرایشگر ویژوال بیسیک را با استفاده از دکمه ای به همین نام در برگه باز کنید توسعه دهنده (توسعه دهنده) یا میانبرهای صفحه کلید دگرساز+F11. در پنجره ای که ظاهر می شود، یک ماژول جدید را از طریق منو وارد کنید درج - ماژول و کد زیر را در آنجا کپی کنید:

زیر Split_By_Rows () سلول کم نور به عنوان محدوده، n به عنوان سلول مجموعه عدد صحیح = ActiveCell برای i = 1 To Selection.Rows.Count ar = Split(cell, Chr(10)) 'تعیین تعداد قطعات سلول.Offset(1, 0) .Resize(n, 1).EntireRow.Insert 'درج ردیف های خالی زیر سلول.Resize(n + 1, 1) = WorksheetFunction.Transpose(ar) 'داده هایی را از آرایه به آنها وارد کنید Set cell = cell.Offset(n + 1، 0) 'به سلول بعدی تغییر دهید بعدی i End Sub  

به اکسل برگردید و سلول‌های دارای متن چندخطی را که می‌خواهید تقسیم کنید انتخاب کنید. سپس از دکمه استفاده کنید ماکرو برگ توسعه دهنده (توسعه دهنده - ماکروها) یا میانبر صفحه کلید دگرساز+F8برای اجرای ماکرو ایجاد شده، که تمام کارها را برای شما انجام می دهد:

وویلا! برنامه نویسان در واقع افراد بسیار تنبلی هستند که ترجیح می دهند یک بار سخت کار کنند و بعد هیچ کاری انجام ندهند.

• پاک کردن متن از کاراکترهای ناخواسته و اضافی
  
• جایگزینی متن و حذف فضاهای بدون شکست با عملکرد SUBSTITUTE
• نحوه تقسیم متن چسبنده به قطعات در اکسل