فهرست
سطرها و ستون‌های خالی می‌توانند در بسیاری از موارد دردسرساز شوند. توابع استاندارد برای مرتب‌سازی، فیلتر کردن، خلاصه‌سازی، ایجاد جداول محوری، و غیره، ردیف‌ها و ستون‌های خالی را به‌عنوان شکست جدول درک می‌کنند، بدون اینکه داده‌هایی را که بیشتر در پشت آنها قرار دارند جمع‌آوری کنند. اگر چنین شکاف‌های زیادی وجود داشته باشد، حذف دستی آنها می‌تواند بسیار پرهزینه باشد، و حذف یکباره "به صورت عمده" با استفاده از فیلتر کردن کارساز نخواهد بود، زیرا فیلتر همچنین در هنگام شکست "لغز می‌کند".
بیایید به چندین روش برای حل این مشکل نگاه کنیم.
روش 1. جستجوی سلول های خالی
شاید این راحت ترین راه نباشد، اما قطعاً ساده ترین راه شایسته ذکر است.
فرض کنید با چنین جدولی روبرو هستیم که شامل سطرها و ستون های خالی زیادی در داخل است (برای وضوح برجسته شده است):
فرض کنید مطمئن هستیم که ستون اول جدول ما (ستون B) همیشه حاوی نام یک شهر است. سپس سلول های خالی در این ستون نشانه ردیف های خالی غیر ضروری خواهد بود. برای حذف سریع همه آنها، موارد زیر را انجام دهید:
- انتخاب محدوده با شهرها (B2:B26)
- کلید را فشار دهید F5 و سپس فشار دهید نماد (به قسمت ویژه بروید) یا در برگه انتخاب کنید صفحه اصلی — یافتن و انتخاب — گروهی از سلول ها را انتخاب کنید (صفحه اصلی - یافتن و انتخاب - رفتن به بخش ویژه).
- در پنجره باز شده گزینه را انتخاب کنید سلول های خالی (جاهای خالی) و مطبوعات OK - تمام سلول های خالی در ستون اول جدول ما باید انتخاب شوند.
- حالا در تب انتخاب کنید صفحه اصلی فرمان حذف - حذف ردیف ها از صفحه (حذف - حذف ردیف ها) یا میانبر صفحه کلید را فشار دهید کلیدهای Ctrl+منهای - و تکلیف ما حل شد.
البته می توانید دقیقا به همین روش و با استفاده از هدر جدول، از شر ستون های خالی خلاص شوید.
روش 2: ردیف های خالی را جستجو کنید
همانطور که ممکن است قبلاً متوجه شده باشید، روش قبلی تنها در صورتی کار می کند که داده های ما لزوماً حاوی ردیف ها و ستون های کاملاً پر شده باشند، که می توان در هنگام جستجوی سلول های خالی به آنها متصل شد. اما اگر چنین اطمینانی وجود نداشته باشد، و داده ها ممکن است حاوی سلول های خالی نیز باشند، چه؟
برای مثال برای چنین موردی به جدول زیر نگاه کنید:
در اینجا رویکرد کمی پیچیده تر خواهد بود:
- تابع را در سلول A2 وارد کنید COUNT (COUNTA)، که تعداد سلول های پر شده در ردیف سمت راست را محاسبه می کند و این فرمول را در کل جدول کپی می کند:
- سلول A2 را انتخاب کرده و با دستور فیلتر را روشن کنید داده - فیلتر (داده ها - فیلتر) یا میانبر صفحه کلید کلیدهای Ctrl+تغییر+L.
- بیایید صفرها را با ستون محاسبه شده فیلتر کنیم، یعنی تمام ردیف هایی که داده ای وجود ندارد.
- باقی مانده است که خطوط فیلتر شده را انتخاب کنید و آنها را با دستور حذف کنید صفحه اصلی - حذف - حذف ردیف‌ها از صفحه (صفحه اصلی - حذف - حذف ردیف ها) یا میانبر صفحه کلید کلیدهای Ctrl+منهای.
- فیلتر را خاموش می کنیم و داده های خود را بدون خطوط خالی دریافت می کنیم.
متأسفانه، این ترفند دیگر با ستون ها قابل انجام نیست – اکسل هنوز نحوه فیلتر کردن بر اساس ستون را نیاموخته است.
روش 3. ماکرو برای حذف تمام سطرها و ستون های خالی در یک صفحه
همچنین می توانید از یک ماکرو ساده برای خودکارسازی این کار استفاده کنید. میانبر صفحه کلید را فشار دهید دگرساز+F11 یا از برگه انتخاب کنید توسعه دهنده - ویژوال بیسیک (توسعه دهنده - ویرایشگر ویژوال بیسیک). اگر زبانه ها توسعه دهنده قابل مشاهده نیست، می توانید آن را از طریق فعال کنید فایل - گزینه ها - راه اندازی نوار (فایل - گزینه ها - سفارشی کردن نوار).
در پنجره ویرایشگر ویژوال بیسیک که باز می شود، دستور منو را انتخاب کنید درج - ماژول و در ماژول خالی که ظاهر می شود، خطوط زیر را کپی و پیست کنید:
Sub DeleteEmpty() Dim r As Long, rng As Range 'удаляем пустые строки برای r = 1 به ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count If Application.CountA(Rows(r)) = 0 سپس اگر rng چیزی نیست سپس تنظیم rng = ردیف (r) دیگر تنظیم rng = اتحاد (rng، ردیف (r)) پایان اگر بعدی r اگر نیست rng چیزی نیست پس rng.حذف 'удаляем пустые столбцы تنظیم rng = هیچ چیز برای r = 1 به ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count If Application.CountA(Columns(r)) = 0 سپس اگر rng چیزی نیست سپس Rng را تنظیم کنید = Columns(r) Else Rng = Union(rng, Columns( r)) End If Next r اگر نه rng چیزی نیست سپس rng.End Sub را حذف کنید
ویرایشگر را ببندید و به اکسل برگردید.
حالا ترکیب را بزنید دگرساز+F8 یا دکمه ماکرو برگ توسعه دهنده. پنجره ای که باز می شود همه ماکروهایی را که در حال حاضر برای اجرا در دسترس شما هستند، از جمله ماکرویی که ایجاد کرده اید، فهرست می کند. DeleteEmpty. آن را انتخاب کنید و روی دکمه کلیک کنید دویدن (اجرا کن) - تمام سطرها و ستون های خالی برگه فوراً حذف می شوند.
روش 4: Power Query
یک راه دیگر برای حل مشکل ما و یک سناریوی بسیار رایج، حذف سطرها و ستون های خالی در Power Query است.
ابتدا، بیایید جدول خود را در ویرایشگر Power Query Query بارگذاری کنیم. می توانید با میانبر صفحه کلید Ctrl+T آن را به یک "هوشمند" پویا تبدیل کنید یا فقط محدوده داده ما را انتخاب کنید و نامی برای آن بگذارید (به عنوان مثال داده ها) در نوار فرمول، تبدیل به نام:
اکنون از دستور Data – Get data – From table / range (Data – Get Data – From table/ range) استفاده می کنیم و همه چیز را در Power Query بارگذاری می کنیم:
سپس همه چیز ساده است:
- خطوط خالی را با دستور Home – Reduce lines – Delete lines – Delete خالی خطوط (Home – Remove Rows – Remove خالی ردیف ها) را حذف می کنیم.
- بر روی عنوان اولین ستون City کلیک راست کرده و دستور Unpivot Other Columns را از منوی زمینه انتخاب کنید. جدول ما، همانطور که از نظر فنی به درستی نامیده می شود، خواهد بود، عادی شده – تبدیل به سه ستون: شهر، ماه و مقدار از تقاطع شهر و ماه از جدول اصلی. ویژگی این عملیات در Power Query این است که از سلول‌های خالی در داده‌های مبدأ عبور می‌کند، چیزی که ما به آن نیاز داریم:
- اکنون عملیات معکوس را انجام می دهیم - جدول به دست آمده را به یک جدول دو بعدی تبدیل می کنیم تا آن را به شکل اولیه خود برگردانیم. ستون با ماه و در برگه را انتخاب کنید دگرگونی یک تیم انتخاب کنید ستون محوری (تبدیل - ستون محوری). در پنجره ای که باز می شود، به عنوان ستونی از مقادیر، آخرین (Value) و در گزینه های پیشرفته - عملیات را انتخاب کنید. تجمیع نکنید (تجمیع نکنید):
- باقی مانده است که نتیجه را با دستور به اکسل بارگذاری کنید صفحه اصلی — بستن و بارگذاری — بستن و بارگیری در… (صفحه اصلی - بستن و بارگیری - بستن و بارگیری به…)
- ماکرو چیست، چگونه کار می کند، متن یک ماکرو را کجا کپی کنیم، چگونه یک ماکرو را اجرا کنیم؟
- پر کردن تمام سلول های خالی لیست با مقادیر سلول های والد
- حذف تمام سلول های خالی از یک محدوده مشخص
- حذف تمام ردیف های خالی در یک کاربرگ با افزونه PLEX