فرمول بندی مسئله
ما محدوده ای از سلول ها با داده هایی داریم که حاوی سلول های خالی هستند:
وظیفه حذف سلول های خالی است و تنها سلول ها با اطلاعات باقی می مانند.
روش 1. خشن و سریع
- انتخاب محدوده اصلی
- کلید را فشار دهید F5، دکمه بعدی نماد (ویژه). در پنجره باز شده را انتخاب کنید سلول های خالی(جاهای خالی) کلیک کنید و OK.
تمام سلول های خالی در محدوده انتخاب شده اند.
- در منو دستور حذف سلول های انتخاب شده را می دهیم: راست کلیک کنید- سلول ها را حذف کنید (حذف سلول ها) با تغییر به سمت بالا
روش 2: فرمول آرایه
برای ساده‌تر کردن، بیایید محدوده‌های کاری خود را با استفاده از آن نامگذاری کنیم مدیر نام (نام مدیر) برگ فرمول (فرمول ها) یا در اکسل 2003 و بالاتر، منو درج - نام - اختصاص (درج - نام - تعریف)
محدوده را B3:B10 نامگذاری کنید خالی داشته باشید، محدوده D3:D10 – NoneEmpty. محدوده ها باید کاملاً یک اندازه باشند و می توانند در هر نقطه نسبت به یکدیگر قرار گیرند.
حالا سلول اول محدوده دوم (D3) را انتخاب کنید و این فرمول ترسناک را در آن وارد کنید:
=IF(ROW() -ROW(NoEmpty)+1>NOTROWS(YesEmpty)-COUNTBLANK(YesEmpty);””;INDIRECT(ADDRESS(LOWEST((IF(Empty<>“”,ROW(Empty);ROW() + ROWS(خالی هستند)))؛ LINE()-ROW(بدون خالی)+1؛ COLUMN(خالی هستند)؛ 4)))
در نسخه انگلیسی این خواهد بود:
=IF(ROW()-ROW(NoEmpty)+1>ROWS(خالی)-COUNTBLANK(خالی)،””,INDIRECT(ADDRESS(SMALL((IF(Empty<>“”,ROW(خالی),ROW() +ROWS(HaveEmpty)))،ROW()-ROW(NoEmpty)+1)،COLUMN(HaveEmpty)،4)))
علاوه بر این، باید به عنوان یک فرمول آرایه وارد شود، یعنی بعد از چسباندن فشار دهید وارد (طبق معمول) و Ctrl + Shift + Enter. اکنون فرمول را می توان با استفاده از تکمیل خودکار کپی کرد (صلیب سیاه را در گوشه سمت راست پایین سلول بکشید) - و ما محدوده اصلی را دریافت می کنیم، اما بدون سلول های خالی:
روش 3. تابع سفارشی در VBA
اگر این ظن وجود دارد که اغلب مجبور می شوید روش حذف سلول های خالی از محدوده ها را تکرار کنید، بهتر است یک بار تابع خود را برای حذف سلول های خالی به مجموعه استاندارد اضافه کنید و در همه موارد بعدی از آن استفاده کنید.
برای انجام این کار، ویرایشگر ویژوال بیسیک (ALT + F11، یک ماژول خالی جدید (منو) وارد کنید درج - ماژول) و متن این تابع را در آنجا کپی کنید:
تابع NoBlanks(DataRange As Range) As Variant() Dim N به عنوان Long Dim N2 به عنوان Long Dim Rng به عنوان محدوده Dim MaxCells به عنوان Long Dim Result() به عنوان متغیر Dim R به عنوان Long Dim C به عنوان Long MaxCells = Application.WorksheetFunction.Max( _ Application.Caller.Cells.Count, DataRange.Cells.Count) ReDim Result(1 به MaxCells، 1 به 1) برای هر Rng در DataRange.Cells اگر Rng.Value <> vbNullString سپس N = N + 1 Result(N, 1) ) = Rng.Value End If Next Rng برای N2 = N + 1 To MaxCells Result(N2, 1) = vbNullString Next N2 If Application.Caller.Rows.Count = 1 سپس NoBlanks = Application.Transpose(نتیجه) Else NoBlanks = Result تابع End If End
فراموش نکنید که فایل را ذخیره کنید و از ویرایشگر ویژوال بیسیک به اکسل برگردید. برای استفاده از این تابع در مثال ما:
- محدوده کافی از سلول های خالی را انتخاب کنید، به عنوان مثال F3:F10.
- به منو بروید درج - تابع (درج - تابع)یا روی دکمه کلیک کنید درج تابع (درج تابع) برگ فرمول (فرمول ها) در نسخه های جدیدتر اکسل در دسته بندی تعریف شده توسط کاربر (تعریف شده توسط کاربر) عملکرد ما را انتخاب کنید نوبلنک ها.
- محدوده منبع را با voids (B3:B10) به عنوان آرگومان تابع مشخص کنید و فشار دهید Ctrl + Shift + Enterبرای وارد کردن تابع به عنوان یک فرمول آرایه.
:
- با یک ماکرو ساده همه سطرهای خالی جدول را به یکباره حذف کنید
- با استفاده از افزونه PLEX، تمام ردیف های خالی یک کاربرگ را به یکباره حذف کنید
- سریع تمام سلول های خالی را پر کنید
- ماکروها چیست، کد ماکرو را کجا در VBA وارد کنیم