اگر در اکسل جدول اطلاعات کوچک باشد، برای مخفی کردن ردیف‌های خالی می‌توانید از روش دستی انتخاب ردیف و Delete کردن ردیف استفاده کنید که سریع‌ترین روش است. اما اگر ردیف‌های اطلاعات وارد شده زیاد باشد، این روش وقت‌گیر است و احتمال خطا و اشتباه نیز وجود دارد.
یک ترفند حذف فضای خالی در اکسل این است که از قابلیت ساخت جدول دارای فیلتر استفاده کنیم که برای تعداد زیادی ردیف قابل اسفاده است و با توجه به اینکه حذف ردیف های خالی در اکسل با فرمول انجام می‌شود، خطا و اشتباه وجود ندارد. در این مقاله به آموزش حذف ردیف‌های خالی در اکسل به کمک فرمول و فیلتر می‌پردازیم. با سیاره‌ی آی‌تی همراه باشید.
با فعال کردن فیلتر می‌توان به سادگی ردیف‌های اطلاعات را مخفی یا نمایان کرد. اما قبل از ساخت جدول و استفاده از فیلتر، به فرمولی نیاز داریم که ردیف‌های خالی را متمایز کند.
جستجو و حذف ردیف‌ها در اکسل با کلید میانبر Ctrl + G
در نرم‌افزار اکسل برای جستجو کردن و انتخاب سلول‌ها می‌توان از کلید میانبر Ctrl + G استفاده کرد. با زدن این کلید میانبر، پنجره‌ی Go To باز می‌شود که امکانات جالب و پیشرفته‌ای دارد. به عنوان مثال می‌توانید روی Special کلیک کنید و سپس تیک گزینه سلول خالی یا Blank را اضافه کنید. با انتخاب سلول خالی و استفاده از گزینه Delete و سپس Delete rows یا حذف ردیف در اکسل می‌توان ردیفی که سلول خالی در آن قرار دارد را حذف کرد.
نکته مهم این روش برای مواردی که فقط بعضی از سلولهای یک ردیف خالی است، کاربرد ندارد. در واقع شاید بخواهید چنین ردیف‌هایی در جدول اطلاعات شما باقی بماند.
فرمول پیدا کردن ردیف های خالی در اکسل
برای پیدا کردن ردیف‌های خالی در جدولی که شاید هزاران ردیف داشته باشد، می‌توان از یک تابع ساده به اسم CountBlank استفاده کرد. می‌توانید در ستونی خارجی از محدوده‌ی جدول، فرمولی با CountBlank بنویسید و محدوده‌ی سلولهای همان ردیف از جدول را به این تابع بدهید. تعداد سلولهای خالی شمارش می‌شود. اگر تعداد سلولهای خالی برابر با کل سلول‌ها باشد، طبعاً آن ردیف جدول کاملاً خالی است و اگر صفر باشد، همه‌ی سلولهای آن ردیف از جدول پر است.
به عنوان مثال اگر جدولی مثل تصویر زیر داشته باشیم، می‌توان در ستون i و ردیف دوم، فرمول زیر را نوشت:
=CountBlank(A2:G2)
خروجی این فرمول برای ردیف دوم جدول ما، عدد ۲ است چرا که ۲ سلول خالی در محدوده سلولهای A2 تا G2 دیده می‌شود.
یک ترفند ساده برای حذف ردیف های خالی در اکسل این است که از تابع CountBlank برای شمارش سلولهای خالی استفاده کنید. در ادامه می‌توانید جدول را بر اساس همین ستون مرتب کنید تا ردیف‌های خالی کنار هم قرار بگیرد و به راحتی ردیف‌ها را انتخاب کرده و حذف کنید.
برای مرتب‌سازی کافی است روی فلش فیلتر کلیک کنید و گزینه Sort Largest To Smallest را انتخاب کنید.
عدم نمایش سلولهای خالی در اکسل با ساخت جدول و فیلتر
برای ساخت جدول در اکسل، به صورت زیر عمل کنید:
- روی یکی از سلولهای پر در جدول کلیک کنید.
- کلید Ctrl را نگه دارید و کلید A را یک مرتبه فشار دهید تا محدوده جدول انتخاب شود.
- در بالای صفحه روی تب Home کلیک کنید.
- روی Format as Table کلیک کنید.
- یکی از استایل‌ها و رنگ‌بندی‌ها را انتخاب کنید.
- روی OK کلیک کنید.
به همین سادگی بخشی از صفحه اکسل به جدول دارای فیلتر تبدیل می‌شود. منظور از فیلتر، گزینه‌هایی است که با کلیک روی فلش‌ها در ردیف اول جدول ظاهر می‌شود و می‌توان نمایش ردیف‌ها و اطلاعات جدول را به کمک این گزینه‌ها کنترل کرد.
اگر نمی‌خواهید از قابلیت جدولی کردن اطلاعات در اکسل استفاده کنید، برای اضافه کردن فیلتر در ردیف اول، می‌توانید از گزینه فیلتر در تب مدیریت داده‌ها استفاده کنید.
برای فعال کردن فیلتر در ردیف اول صفحه اکسل به صورت زیر عمل کنید:
- ردیف اول جدول را انتخاب کنید.
- روی تب Data در نوار ابزار بالای صفحه کلیک کنید.
- روی گزینه Filter کلیک کنید.
عدم نمایش ردیف های خالی با فرمول شمارش سلول های خالی
همان‌طور که پیش‌تر توضیح دادیم، می‌توانید یک ستون جدید برای شمارش تعداد سلولهای خالی داشته باشید و با استفاده از قابلیت فیلتر در این ستون، کاری کنید که تمام سلولهای خالی به راحتی و با یک کلیک، مخفی یا ظاهر شود. طبعاً انتخاب کردن و حذف ردیف های خالی در اکسل نیز به کمک همین ستون و فیلتر امکان‌پذیر است.
برای نوشتن فرمول پیدا کردن ردیف خالی در اکسل، مراحل زیر را طی کنید:
- روی سلولی در ردیف دوم و اولین ستون خالی پس از محدوده جدول اطلاعات کلیک کنید.
- تعداد ستون‌های جدول را شمارش کنید، فرضاً تعداد n است.
- فرمول زیر را وارد کنید و به جای RANGE، محدوده سلولهای آن ردیف را بنویسید.
=n - countblank(RANGE)
- مربع کوچک گوشه پایین سلول را با کلید چپ موس بگیرید.
- موس را حرکت بدهید و تا آخرین ردیف جدول درگ کنید و سپس کلید چپ موس را رها کنید.
- روش ساده‌تر دبل‌کلیک کردن روی مربع گوشه پایین و راست سلول است.
- فرمول ردیف ۲ در تمام ردیف‌های بعدی کپی می‌شود.
- اکنون ردیف اول صفحه اکسل را انتخاب کنید.
- روی تب Data در بالای صفحه کلیک کنید.
- روی گزینه Filter کلیک کنید.
- اکنون روی فلش کنار عنوان ستون فرمول شمارش سلولهای خالی کلیک کنید.
- تیک همه موارد به جز 0 را حذف کنید تا فقط ردیف‌های خالی نمایش داده شود.
- با کلیک و درگ کردن موس در ستون شماره ردیف‌ها، ردیف‌های خالی را انتخاب کنید.
- روی سلول‌ها راست‌کلیک کنید و گزینه Delete را انتخاب کنید.
- برای نمایش ردیف های غیرخالی، روی فلش فیلتر کلیک کنید.
- همه موارد به جز 0 را انتخاب کنید.
در این راهنما تعداد سلولهای پر را به کمک فرمول n - countblank محاسبه کردیم اما می‌توانید برعکس عمل کنید به این صورت که سلولهای خالی را به کمک countblank شمارش کنید. در این صورت برای نمایش ردیف‌های خالی نیز باید برعکس عمل کنید به این صورت که پس از کلیک روی فلش فیلتر، می‌بایستی تیک همه موارد به جز عددی که با تعداد ستون‌های جدول برابر است را بزنید.
به این ترتیب فقط ردیف‌هایی نمایش داده می‌شود که تعداد سلولهای خالی آن کمتر از کل ستون‌های جدول است.
همان‌طور که متوجه شده‌اید استفاده از فرمول ساده‌ای که با تابع CountBlank نوشته می‌شود و قابلیت فیلتر کردن اطلاعات در اکسل، می‌توان به راحتی ردیف‌های خالی یا ردیف‌های دارای ۱ سلول خالی یا ردیف‌های دارای ۲ سلول خالی و بیشتر را پیدا کرد و برای پر کردن داده‌ها یا بررسی بیشتر، اقدام کرد.
اگر فایل اکسل آماده‌ای به دست شما رسیده و مشکلی در حذف کردن ردیف‌ها و به طور کلی ویرایش کردن فایل وجود دارد، دقت کنید که ممکن است علت حذف نشدن سطر در اکسل این باشد که فایل دارای رمز عبور است و ویرایش آن بدون پسورد غیرممکن است.
howtogeekسیاره‌ی آی‌تی