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

چگونگی حذف سلول های خالی در اکسل

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

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

1. محدوده ای که میخوایم سلول های خالی اون حذف بشن رو انتخاب میکنیم. برای انتخاب تمام داده های داخل شیت، میتونیم بالاترین سلول در گوشه سمت چپ شیت رو انتخاب کرده و کلید های ترکیبی Ctrl + Shift + End رو فشار بدیم. با این کار تا اخرین سلول حاوی اطلاعات انتخاب میشه.
2. کلید F5 یا Ctrl+G رو میزنیم و Special رو کلیک می‌کنیم، و یا از طریق تب Home و گروه Editing، بر روی Find & Select کلیک کرده و از منو باز شده گزینه Go To Special رو انتخاب می‌کنیم.

شکل ۱ ـ انتخاب محدوده سلول ها

3. در منو باز شده گزینه Blanks رو انتخاب کرده و روی Ok کلیک می‌کنیم. این کار تمام سلول های خالی رو انتخاب می‌کنه.

شکل ۲ ـ منو Go To Special

4. روی یکی از سلول های خالی راست کلیک کرده و delete رو انتخاب می‌کنیم.

شکل ۳ ـ راست کلیک و حذف سلول خالی

5. با توجه به ساختار داده ها یک گزینه رو انتخاب کرده و روی Ok کلیک می‌کنیم. ما در این مثال گزینه Shift cells left رو انتخاب می‌کنیم. این گزینه به این معنی هست که هر سلولی که حذف میشه، سلول بعدی (سمت راستی در شیت های چپ به راست)، جایگزینش بشه. مثلا اگه shift cells up انتخاب بشه، هر سلولی که حذف میشه، داده موجود در سلول زیری، جایگزین سلول حذف شده میشه.

شکل ۴ ـ انتقال سلول ها به چپ یا راست

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

شکل ۵ ـ لیست بدون سلول خالی

چه زمانی نباید سلول های خالی رو با انتخاب blanks حذف کنیم؟

گزینه Blanks در منو Go To Special برای یک ردیف یا ستون خوب کار میکنه. این روش همچنین میتونه سلول های خالی در یک محدوده رو هم حذف کنه، درست مثل مثال بالا. هرچند، این روش ممکنه ساختار داده ها رو بهم بریزه یا حتی از بین ببره. پس برای جلوگیری از این اتفاق باید خیلی مراقب بود و همچنین موارد زیر رو یادمون باشه:

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

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

2. این کار برای جدول های اکسل (Table) کار نمیکنه

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

3. ممکنه به فرمول ها یا محدوده های نامگذاری شده صدمه وارد بشه

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

چگونگی استخراج یک لیست از داده ها بدون جاهای خالی

بعد از حذف سلول های خالی، برای جلوگیری از بریده شدن داده ها در ستون ها، میتونیم ستون اصلی رو همونطور که هست نگه داشته و داده ها رو بدون سلول های خالی از ستون استخراج کنیم و در یک جای دیگه قرار بدیم. این روش خیلی کاربردیه مخصوصا وقتی که میخوایم یک custom list یا drop-down data validation list درست کنیم و میخوایم که هیچ سلول خالی در اونها نباشه.

با لیست منبع در A2:A11، فرمول زیر رو در C2 وارد می‌کنیم و Ctrl+Shift+Enter رو می‌زنیم و بعد فرمول رو تا چند سلول بعدی کپی می‌کنیم. تعداد سلول هایی که فرمول رو در اونها کپی می‌کنیم باید برابر یا بزرگتر از تعداد موارد لیست باشن.

فرمول برای استخراج سلول های غیر خالی:

=IFERROR(INDEX($A$2:$A$11, SMALL(IF(NOT(ISBLANK($A$2:$A$11)), ROW($A$1:$A$10),””), ROW(A1))),””)

در تصویر زیر نتیجه کار مشاهده میشه:

شکل ۶ ـ استخراج سلول های غیر خالی

چگونگی کارکرد فرمول

توجه داشته باشید برای درک این فرمول، باید با تابع IF, Small, Index, Row, Not, Isblank, Iferror و منطق آرایه ای آشنا باشید. هر کدوم از این توابع رو جداگانه مطالعه کنید و بعد ادامه مقاله رو مطالعه کنید.

در نگاه اول ممکنه پیچیده بنظر بیاد، ولی با نگاه دقیق تر میبینیم که منطق آسونی داره. به زبان پارسی فرمول در C2 اینطور خونده میشه:

اولین مقدار در محدوده A2:A11 رو برگردون تنها در صورتی که اون سلول خالی نباشه. اگر به error برخورد کردی، یک رشته خالی (“”) رو برگردون.

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

ما از تابع INDEX میخوایم که یک مقدار از $A$۲:$A$۱۱ رو براساس شماره ردیف مشخص شده (نه یک شماره ردیف واقعی، یک شماره ردیف مرتبط در محدوده) برگردونه. به زبان ساده تر، ما INDEX($A$2:$A$11, 1) رو در C2 قرار میدیم و اون مقدار A2 رو به ما برمیگردونه. فقط مشکل اینجاست که ما باید دو چیز دیگه هم فراهم کنیم:

• مطمئن شدن از اینکه A2 خالی نیست.
• برگردوندن دومین مقدار غیر خالی در C3، سومین مقدار غیر خالی در C4 و …

هر دو این موارد با تابع SMALL(array, k) قابل حل شدن هستن:

SMALL(IF(NOT(ISBLANK($A$2:$A$11)), ROW($A$1:$A$10),””), ROW(A1))

آرایه ای بودن فرمول در این قسمت هست. یعنی:

• NOT(ISBLANK($A$2:$A$11)) مشخص میکنه که کدوم سلول ها در محدوده هدف خالی نیستن و مقدار TRUEرو برای اونها برمیگردونه، در غیر این صورت FALSE. نتیجه آرایه از TRUE و FALSE به آرگومان اول تابع IF یعنی logical test میره.
• طبق این فرمول IF(NOT(ISBLANK($A$2:$A$11)), ROW($A$1:$A$10),””) ، تابع If تمام عناصر آرایه TRUE/FALSE رو ارزیابی میکنه، و یک عدد (شماره ردیف از یک تا ده) متناظر برای TRUE، و یک رشته خالی برای FALSE برمیگردونه:

IF({TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE},ROW($A$1:$A$10),””)

ROW($A$1:$A$10) یک آرایه از اعداد ۱ تا ۱۰ رو برمیگردونه (چون ۱۰ سلول در محدوده ما وجود داره از A2 تا A11) که IF بتونه یک عدد برای مقادیر TRUE انتخاب کنه. پس به ازای هر True در آرگومان اول فرمول بالا، یک شماره ردیف متناظر از یک تا ده تعلق میگیره.

در نتیجه خروجی فرمول IF ما این آرایه {۱;۲;””;۴;۵;””;””;””;۹;۱۰}  هست که در آرگومان اول تابع Small قرار میگیره و تابع SMALL، به این تابع ساده تغییر شکل پیدا میکنه :

SMALL( {1;2;””;4;5;””;””;””;9;10} , ROW(A1))

همونطور که مشاهده میشه، قسمت array فقط شامل مکان سلول های غیر خالی هست (میدونیم که، این ها موقعیت های مرتبط با عناصر آرایه هستند.). حالا باید فقط اعداد رو فراخوانی کنیم و کاری به رشته های خالی نداشته باشیم، که تابع small همین کار رو انجام میده.

در قسمت k، ما ROW(A1) رو قرار میدیم که به تابع SMALL دستور میده که اولین عدد کوچک رو برگردونه. با توجه به کاربرد relative cell reference، همونطور که فرمول رو کپی می‌کنیم، عدد ردیف هم یکی یکی افزایش پیدا می‌کنه. بنابراین در C3، k به ROW(A2) تغییر پیدا می‌کنه و فرمول، عدد متناظر با دومین سلول غیر خالی رو برمیگردونه و …

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

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

حالا که تشریح این فرمول رو مطالعه کردید، یکبار هم debug کنید و مطالب بالا رو در حین debug کردن مرور کنید، این کار خیلی به درک بهتر مطلب کمک میکنه.

چگونگی پاک کردن سلول های خالی بعد از آخرین سلول حاوی داده

سلول های خالی که حاوی کاراکتر های formatting یا غیر قابل پرینت هستن ممکنه در اکسل خیلی مشکل ساز بشن. برای مثال، فضاهای خالی بدون استفاده، موجب افزایش حجم فایل و سنگین شدن آن میشن، یا باعث میشن چندین صفحه خالی رو پرینت گرفته بشه و… برای جلوگیری از این مشکلات، ما ردیف و ستون هایی که حاوی formatting، space یا هر نوع کاراکتر غیر قابل دیدن دیگه هستن رو حذف می‌کنیم.

چگونگی پیدا کردن اخرین سلول استفاده شده در شیت

برای رفتن به آخرین سلول در شیت حاوی هرگونه داده، روی یک سلول کلیک کرده و کلید های Ctrl + End رو فشار میدیم.

اگر کلید های میانبر بالا مارو به آخرین سلول استفاده شده ببره، به این معنیه که تمام سلول های باقی مانده خالی هستن و نیازی به دستکاری اضافه ای نیست. اما اگر ما رو به یک سلول خالی ببره به این معنیه که اکسل اون سلول رو خالی در نظر نگرفته. اون سلول ممکنه حاوی یک کاراکتر space باشه که اتفاقی با فشردن کلیک ایجاد شده یا یک custom number format تنظیم شده برای اون سلول و یا حتی یک کاراکتر غیرقابل پرینت از یک دیتابیس خارجی. به هر دلیل، اون سلول خالی نیست.

پاک کردن سلول های خالی بعد از آخرین سلول حاوی داده

برای این کار طبق مراحل زیر پیش میریم:

1. روی بالای اولین ستون خالی در سمت راست داده ها کلیک می‌کنیم و کلید های Ctrl + Shift + End رو فشار میدیم. این کار یک محدوده سلول بین داده های ما و اخرین سلول استفاده شده در اون شیت انتخاب می‌کنه.
2. از مسیر Home tab > Editing group > Clear رفته و Clear All رو انتخاب می‌کنیم. و یا روی محدوده راست کلیک کرده و پس از انتخاب delete، گزینه Entire column رو انتخاب می‌کنیم.

شکل ۷ ـ حذف سلول های خالی بعد از آخرین سلول

3. روی بالای اولین ستون خالی در پایین داده ها کلیک می‌کنیم و کلیدهای Ctrl+Shift+End رو فشار میدیم.
4. از مسیر Home tab>Editing group>Clear رفته و Clear All رو انتخاب می‌کنیم. و یا روی محدوده راست کلیک کرده و پس از انتخاب delete، گزینه Entire column رو انتخاب می‌کنیم.
5. Ctrl + s رو میزنیم تا ورک‌بوک ذخیره بشه.

محدوده استفاده شده رو چک می‌کنیم تا مطمئن بشیم فقط سلول های حاوی داده (بدون سلول خالی) انتخاب شدن. اگر Ctrl + End دوباره یک سلول خالی انتخاب کرد، ورک‌بوک رو سیو کرده و اون رو میبندیم. وقتی که دوباره اون رو باز می‌کنیم، باید آخرین سلول استفاده شده سلول حاوی داده باشه.