آموزش حذف ردیف های خاص در اکسل (ساده ترین روش)

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

مثال های زیر را در نظر بگیرید:

۱- شما داده های فروش دارید و می خواهید تمام سوابق مربوط به یک منطقه یا محصول خاص را حذف کنید.

۲- می خواهید همه رکوردها را که ارزش فروش آن ها کمتر از ۱۰۰ است، حذف کنید.

۳- می خواهید همه ردیف ها را که دارای یک سلول خالی هستند، حذف کنید.

روش های مختلفی برای حذف این نوع داده ها در اکسل وجود دارد.

روشی که شما برای حذف ردیف ها انتخاب می کنید به نحوه ساختار داده ها و مقدار یا شرایط سلول بستگی دارد.

در این آموزش روش های مختلفی را برای حذف ردیف ها در اکسل بر اساس مقدار سلول یا یک شرط، به شما یاد خواهیم داد.

این آموزش شامل:

۱- فیلتر ردیف ها بر اساس مقدار/ شرط و سپس حذف آن

۱- حذف ردیف های حاوی یک متن خاص

۲- حذف ردیف ها بر اساس یک شرط عددی

۲- مرتب سازی بر اساس مجموعه داده و سپس حذف ردیف ها

۳- یافتن و انتخاب سلول ها بر اساس مقدار سلول و سپس حذف ردیف ها

۴- حذف همه ردیف های دارای یک سلول خالی

۵- فیلتر و حذف ردیف ها بر اساس مقدار سلول (با استفاده از VBA)

فیلتر ردیف ها را بر اساس مقدار/ شرط و سپس حذف آن

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

فیلتر اکسل کاملا متنوع است و می توانید براساس معیارهای زیادی (مانند متن، اعداد، تاریخ و رنگ) فیلتر کنید.

بیایید دو نمونه را ببینیم که در آن می توانید ردیف ها را فیلتر کرده و آن ها را حذف کنید.

حذف ردیف های حاوی یک متن خاص

فرض کنید مجموعه داده ای دارید که در شکل زیر نشان داده شده است و می خواهید تمام ردیف هایی را که منطقه آن ها Mid-West است، حذف کنید (در ستون B).

در حالی که در این مجموعه داده کوچک می توانید انتخاب کنید که این ردیف ها را به صورت دستی حذف کنید، اما اغلب مجموعه داده ها بسیار بزرگند که حذف ردیف به صورت دستی به راحتی امکان پذیر نخواهد بود.

در این حالت می توانید تمام رکورد هایی را که منطقه در Mid-West قرار دارد فیلتر کنید و سپس همه این ردیف ها را حذف کنید (در حالی که ردیف های دیگر دست نخورده باقی می مانند).

در زیر مراحل حذف ردیف ها بر اساس مقدار (کلیه رکورد های Mid-West) آورده شده است:

۱- هر سلول از مجموعه داده ای را که می خواهید ردیف هایش را حذف کنید، انتخاب کنید.

۲- بر روی تب Data کلیک کنید.

۳- در گروه ” Sort & Filter” ، روی نماد Filter کلیک کنید. این گزینه فیلترها را برای همه سلول های هدر در مجموعه داده اعمال می کند.

۴- بر روی نماد Filter در سلول هدر Region کلیک کنید (یک نماد مثلث رو به پایین در سمت راست بالای سلول است)

۵- غیر از گزینه Mid-West گزینه های دیگر را انتخاب کنید (یک راه سریع برای این کار با کلیک کردن روی گزینه Select All و سپس کلیک بر روی گزینه Mid-West). این کار مجموعه داده ها را فیلتر می کند و فقط رکورد منطقه Mid-West را به شما نشان می دهد.

۶- تمام رکوردهای فیلتر شده را انتخاب کنید.

۷- بر روی هر یک از سلول های انتخابی راست کلیک کرده و روی “Delete Row” کلیک کنید.

۸- در کادر گفتگوی باز شده ، بر روی OK کلیک کنید. در این مرحله ، هیچ رکوردی در مجموعه داده مشاهده نمی کنید.

۹- روی تب Data کلیک کنید و آیکون Filter را انتخاب کنید. با این کار فیلتر حذف می شود و تمام رکوردها به جز حذف شده ها را مشاهده خواهید کرد.

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

برخی از کلید های میانبر مفید برای سرعت بخشیدن به روند کار:

۱- برای اعمال یا حذف فیلتر ، Control + Shift + L را کنترل کنید

۲- Control + – (کلید کنترل را نگه دارید و کلید منها را فشار دهید) تا سلول / ردیف های انتخاب شده حذف شوند.

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

در صورت داشتن دسته بندی ها / منطقه های زیاد، می توانید نام را در قسمت بالای کادر تایپ کنید (که این شامل نام های منطقه است) و اکسل فقط آن دسته از فایل های مطابق با متن وارد شده را به شما نشان می دهد (مطابق شکل زیر). هنگامی که متنی را بر اساس آن می خواهید فیلتر کنید، کلید Enter را بزنید.

توجه داشته باشید که هنگام حذف یک ردیف هر چیزی که ممکن است در سلول های دیگر در این ردیف ها داشته باشید از بین می رود. یک راه برای دستیابی به این مسئله ایجاد یک کپی از داده ها در کاربرگ دیگر و حذف سطرها در داده های کپی شده است. پس از اتمام دوباره آن را در محل داده اصلی کپی کنید.

یا

می توانید از روش هایی که بعدا در این آموزش نشان داده شده استفاده کنید.

حذف ردیف ها بر اساس یک شرط عددی

درست همانطور که از روش فیلتر برای حذف تمام ردیف هایی که متن Mid-West را دارند استفاده کردیم، همچنین می توانید از یک عدد (یا شرط تاریخ) استفاده کنید.

به عنوان مثال، فرض کنید مجموعه داده زیر را داریم و می خواهیم تمام ردیف هایی را که مقدار فروش کمتر از ۲۰۰ است حذف کنیم.

در زیر مراحل انجام این کار آورده شده است:

۱- هر سلول را در داده انتخاب کنید.

۲- بر روی تب Data کلیک کنید.

۳- در گروه “Sort & Filter” روی نماد Filter کلیک کنید. این کار فیلترها را برای همه سلول های هدر در مجموعه داده اعمال می کند.

۴- بر روی نماد Filter در سلول هدر Sales کلیک کنید (یک نماد مثلث کوچک به سمت پایین در سمت راست سلول است)

۵- مکان نما را روی گزینه Number Filters قرار دهید. این گزینه تمام اعداد مربوط به فیلتر را در اکسل به شما نشان می دهد.

۶- روی گزینه “Less than” کلیک کنید.

۷- در کادر گفتگوی “Custom Autofilter” که باز می شود، مقدار “۲۰۰” را در فیلد وارد کنید.

۸- روی OK کلیک کنید. با این کار فقط آن دسته از فایل های فیلتر شده نشان داده می شوند که ارزش فروش کمتر از ۲۰۰ دارند.

۹- تمام رکورد های فیلتر شده را انتخاب کنید.

۱۰- بر روی هر یک از سلول ها راست کلیک کرده و بر روی Delete Row کلیک کنید.

۱۱- در کادر گفتگوی باز شده بر روی OK کلیک کنید. در این مرحله، هیچ رکوردی در مجموعه داده مشاهده نمی کنید.

۱۲- بر روی تب Data کلیک کنید و آیکون Filter را انتخاب کنید. با این کار فیلتر حذف می شود و تمام رکوردها به جز حذف شده ها را مشاهده خواهید کرد.

تعداد فیلترهای زیادی وجود دارد که می توانید از آن ها در اکسل استفاده کنید – مانند کمتر از / بیشتر از، مساوی / نا مساوی، بین، ۱۰ تای برتر ، بالاتر یا پایین تر از متوسط ​​و غیره.

توجه: می توانید از چندین فیلتر نیز استفاده کنید. به عنوان مثال می توانید تمام ردیف هایی را که مقدار فروش بیشتر از ۲۰۰ است اما کمتر از ۵۰۰ است حذف کنید. در این حالت باید از دو شرط فیلتر استفاده کنید. کادر گفتگوی Custom Autofilter اجازه می دهد تا دو معیار فیلتر (و همچنین OR) را داشته باشید.

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

در حالی که فیلتر یک روش عالی برای حذف سریع ردیف ها بر اساس یک مقدار یا یک شرط است، اما یک اشکال دارد – کل ردیف را حذف می کند. به عنوان مثال، در مورد زیر، تمام داده های موجود در سمت راست مجموعه داده فیلتر شده را حذف می کند.

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

شما نمی توانید این کار را با فیلتر انجام دهید ، اما می توانید این کار را با مرتب سازی انجام دهید.

مرتب سازی بر اساس مجموعه داده و سپس حذف ردیف ها

اگرچه مرتب سازی روش دیگری برای حذف ردیف ها بر اساس مقدار است اما در بیشتر موارد، بهتر است از روش فیلتر که در بالا گفته شد، استفاده کنید.

این روش مرتب سازی فقط زمانی توصیه می شود که سلول ها را با مقادیر حذف کنید و نه کل ردیف ها را.

فرض کنید دیتابیس همانطور که در زیر نشان داده شده است را دارید و می خواهید تمام سوابق موجود در منطقه Mid-West را حذف کنید.

در زیر مراحل انجام این کار با استفاده از مرتب سازی ارائه شده است:

۱- هر سلول را در داده انتخاب کنید.

۲- بر روی تب Data کلیک کنید.

۳- در گروه Sort & Filter بر روی نماد Sort کلیک کنید.

۴- در کادر گفتگوی Sort که باز می شود، Region را در Sort by انتخاب کنید.

با این آموزش اکسل صفر تا صد اکسل، رو توی کمترین زمان ممکن یاد بگیر.بهترین پک آموزش اکسل در ایران همین الان خرید و دانلود کنید!

۵- در گزینه Sort on مطمئن شوید که Cell Values ​​انتخاب شده است.

۶- در گزینه Order ، A تا Z را انتخاب کنید (یا Z به A ، مهم نیست).

۷- روی OK کلیک کنید. مجموعه داده های مرتب شده مانند شکل زیر را نشان می دهد (مرتب شده با ستون B).

۸- همه رکورد ها را با منطقه Mid-West را انتخاب کنید (تمام سلول های ردیف ، نه فقط ستون region).

۹- پس از انتخاب، راست کلیک کرده و سپس بر روی Delete کلیک کنید. با این کار کادر محاوره ای Delete باز می شود.

۱۰- مطمئن شوید که گزینه “Shift cells up” انتخاب شده است.

۱۱- روی OK کلیک کنید.

مراحل فوق کلیه رکوردها را در جایی که منطقه Mid-West قرار دارد حذف می کند اما کل سطر را حذف نمی کند. بنابراین، اگر داده ای را در سمت راست یا چپ مجموعه داده خود داشته باشید، باقی خواهد ماند.

در مثال بالا، داده ها را بر اساس مقدار سلول مرتب سازی کرده ایم، اما همچنین می توانید همان مراحل را برای مرتب سازی بر اساس اعداد ، تاریخ ، رنگ سلول یا رنگ قلم و غیره استفاده کنید.

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

یافتن و انتخاب سلول ها بر اساس مقدار سلول و سپس حذف ردیف ها

اکسل دارای قابلیت Find and Replace است که می توانید سلول هایی با یک مقدار خاص را پیدا کرده و انتخاب کنید.

پس از انتخاب این سلول ها، می توانید ردیف ها را به راحتی حذف کنید.

فرض کنید دیتابیس را مطابق شکل زیر در اختیار داشته باشید و می خواهید تمام ردیف هایی را که در منطقه Mid-West قرار دارد حذف کنید.

در زیر مراحل انجام این کار آورده شده است:

۱- کل مجموعه داده را انتخاب کنید.

۲- روی تب Home کلیک کنید.

۳- در گروه Editing، بر روی گزینه “Find & Select” کلیک کرده و سپس بر روی Find (همچنین می توانید از میانبر صفحه کلید Control + F استفاده کنید) کلیک کنید.

۴- در کادر گفتگوی Find and Replace ، متن “Mid-West” را در قسمت “Find what:” وارد کنید.

۵- روی Find All کلیک کنید. این کار فوراً تمام موارد متنی Mid-West را که اکسل قادر به یافتن آن بود، به شما نشان می دهد.

۶- از میانبرهای صفحه کلید Control + A استفاده کنید تا تمام سلول هایی را که اکسل پیدا کرده انتخاب کنید. همچنین می توانید تمام سلول های انتخاب شده را در مجموعه داده مشاهده کنید.

۷- بر روی هر یک از سلول های انتخاب شده راست کلیک کرده و بر روی Delete کلیک کنید. با این کار کادر محاوره ای Delete باز می شود.

۸- گزینه “Entire row” را انتخاب کنید.

۹- روی OK کلیک کنید.

مراحل فوق کلیه سلول هایی را که مقدار منطقه Mid-West است، حذف می کند.

توجه: از آنجا که Replace and Find می تواند کاراکترهای قرانویسه را کنترل کند، می توانید هنگام پیدا کردن داده در اکسل از این موارد استفاده کنید. به عنوان مثال اگر می خواهید تمام ردیف هایی را که در منطقه Mid-West یا South-West قرار دارد حذف کنید، می توانید از ‘*West‘ به عنوان متن برای یافتن در کادر گفتگوی Find and Replace استفاده کنید. این به شما تمام سلول هایی را که متن با کلمه West پایان می یابد، می دهد.

حذف همه ردیف های دارای یک سلول خالی

در صورتی که می خواهید تمام ردیف هایی که سلول های خالی در آن وجود دارد را حذف کنید، می توانید به راحتی این کار را با تابع داخلی در اکسل انجام دهید.

این گزینه Go-To Special Cells است – به شما امکان می دهد به سرعت همه سلول های خالی را انتخاب کنید. و پس از انتخاب همه سلول های خالی، حذف این موارد بسیار ساده است.

فرض کنید مجموعه داده را مطابق شکل زیر دارید و می خواهید تمام سطرهایی را که ارزش فروش ندارند، حذف کنید.

در زیر مراحل انجام این کار آورده شده است:

۱- کل مجموعه داده را انتخاب کنید (در این حالت A1: D16).

۲- کلید F5 را فشار دهید. این کار کادر گفتگوی “Go To” را باز می کند (همچنین می توانید این کادر گفتگو را از Home –> Editing –> Find and Select –> Go To) دریافت کنید.

۳- در کادر گفتگوی “Go To”، بر روی دکمه Special کلیک کنید. این کار کادر گفتگوی “Go To Special” را باز می کند.

۴- در کادر گفتگوی Go To Special ، “Blanks” را انتخاب کنید.

۵- روی OK کلیک کنید.

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

پس از انتخاب سلول های خالی، بر روی هر یک از سلول ها راست کلیک کرده و روی Delete کلیک کنید.

در کادر محاوره ای Delete ، گزینه “Entire row” را انتخاب کرده و روی OK کلیک کنید. با این کار تمام ردیف هایی که سلول خالی در آن دارند حذف می شوند.

فیلتر و حذف ردیف ها بر اساس مقدار سلول (با استفاده از VBA)

آخرین روشی که قصد داریم به شما نشان دهیم شامل کمی VBA است.

در صورت نیاز به حذف ردیف ها بر اساس یک مقدار خاص در یک ستون، می توانید از این روش استفاده کنید. می توانید یک بار کد VBA را اضافه کرده و ورک بوک شخصی Macro خود را اضافه کنید. به این ترتیب برای استفاده در همه ورک بوک های اکسل قابل استفاده خواهد بود.

این کد مانند روش فیلتر در بالا کار می کند (به جز این که تمام مراحل موجود را پنهانی انجام می دهد و چند کلیک را برای شما ذخیره می کند).

فرض کنید دیتابیس را مطابق شکل زیر در اختیار داشته باشید و می خواهید تمام ردیف هایی را که در منطقه Mid-West قرار دارد حذف کنید.

کد VBA زیر این کار را انجام می دهد.

Sub DeleteRowsWithSpecificText()

‘Source:https://trumpexcel.com/delete-rows-based-on-cell-value/

ActiveCell.AutoFilter Field:=2, Criteria1:=”Mid-West”

ActiveSheet.AutoFilter.Range.Offset(1, 0).Rows.SpecialCells(xlCellTypeVisible).Delete

End Sub

کد فوق از روش VBA Autofilter برای فیلتر کردن ردیف ها بر اساس معیارهای مشخص شده (که “Mid-West” است) استفاده می کند، سپس تمام ردیف های فیلتر شده را انتخاب کرده و حذف می کند.

توجه داشته باشید که از Offset در کد بالا استفاده کردیم تا مطمئن شویم که ردیف سربرگ حذف نشده است.

اگر داده های شما در جدول اکسل باشد، کد فوق کار نمی کند. دلیل این امر این است که اکسل جدول را به عنوان شیء لیست در نظر می گیرد. بنابراین اگر می خواهید ردیف هایی را که در یک جدول هستند حذف کنید، باید کد را کمی تغییر دهید.

قبل از حذف ردیف ها، هشداری به شما نشان می دهد که در زیر مشاهده می کنید. این کار مفید است زیرا به ما امکان می دهد ردیف فیلتر شده را قبل از حذف دوباره بررسی کنید.

به یاد داشته باشید وقتی ردیف ها را با استفاده از VBA حذف می کنید، نمی توانید این تغییر را خنثی سازی کنید. بنابراین فقط وقتی مطمئن هستید، این کار را انجام دهید. همچنین ایده خوبی است که در صورت اشتباه بودن، یک نسخه پشتیبان از داده ها را نگه دارید.

اگر داده های شما در جدول اکسل قرار دارند، از کد زیر برای حذف ردیف ها با یک مقدار خاص در آن استفاده کنید:

Sub DeleteRowsinTables()

‘Source:https://trumpexcel.com/delete-rows-based-on-cell-value/

Dim Tbl As ListObject

Set Tbl = ActiveSheet.ListObjects(1)

ActiveCell.AutoFilter Field:=2, Criteria1:=”Mid-West”

Tbl.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete

End Sub

از آنجا که VBA جدول اکسل را به عنوان شیء لیست (و نه یک دامنه) در نظر می گیرد، مجبور شدیم کد را بر این اساس تغییر دهیم.

کد VBA را کجا قرار دهیم؟

این کد باید در باطن VB Editor در یک ماژول قرار گیرد.

در زیر مراحلی که نحوه انجام این کار را به شما نشان می دهند آمده است:

۱- ورک بوکی را که می خواهید این کد را اضافه کنید باز کنید.

۲- برای باز کردن پنجره VBA Editor از میانبر صفحه کلید ALT + F11 استفاده کنید.

۳- در پنجره VBA Editor ، در سمت چپ ، یک صفحه “Project Explorer” ( شامل همه ورک بوک ها و کار برگ ها) وجود دارد. با کلیک راست بر روی هر شیء موجود در ورک بوک (که در آن شما می خواهید این کد کار کند) ، مکان نما را روی “Insert” قرار دهید و سپس روی “Module” کلیک کنید. با این کار شیء Module به ورک بوک اضافه می شود و همچنین پنجره Module code در سمت راست باز می شود.

۴- در پنجره ماژول (که در سمت راست ظاهر می شود) کد بالا را کپی و جایگذاری کنید.

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

۱- خطی را درون کد انتخاب کنید و کلید F5 را بزنید.

۲- بر روی دکمه Run در نوار ابزار در ویرایشگر VB کلیک کنید.

۳- ماکرو را به یک دکمه یا یک شکل اختصاص داده و با کلیک کردن روی آن در کار برگ خود آن را اجرا کنید.

۴- آن را به نوار ابزار Quick Access اضافه کرده و کد را با یک کلیک اجرا کنید.

توجه: از آنجا که ورک بوک شامل یک کد ماکرو VBA است ، باید آن را با فرمت ماکرو (xlsm) ذخیره کنید.

بسیار کاربردی برای اکسل

• آموزش محاسبه انحراف معیار در اکسل (ساده ترین روش)
• گونه در اکسل رادیکال (ریشه دوم) بنویسیم؟ (گام به گام)
• آموزش نوشتن توان در اکسل (و نکات کاربردی)
• آموزش تبدیل متن به عدد در اکسل (همه ی روش های موجود)
• ۴۰ کلید میانبر در اکسل که سرعت کارتان را دو برابر می کنند
• دانلود ۱۸ درس کاربردی آموزش نرم افزار اکسل (رایگان+جزوه)
• آموزش مدیریت فرمول ها در اکسل (Copy, Paste, Autofill)
• روش تبدیل ساعت و دقیقه به روز در اکسل (ساده ترین روش)