قالب های شرطی که نقاط خالی و صفر را تشخیص می دهند در اکسل

Question

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

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

شکل 1. کادر محاوره ای New Formatting Rule.

1. محدوده ای را که می خواهید به صورت مشروط قالب بندی شود انتخاب کنید. (برای این مثال، فرض می‌کنیم که محدوده A2:A99 را انتخاب کرده‌اید.)
2. در حالی که تب صفحه اصلی نوار نمایش داده می شود، روی گزینه Conditional Formatting در گروه Styles کلیک کنید. اکسل پالتی از گزینه های مربوط به قالب بندی شرطی را نمایش می دهد.
3. روی Manage Rules کلیک کنید. اکسل کادر گفتگوی Conditional Formatting Rules Manager را نمایش می دهد.
4. روی New Rule کلیک کنید. اکسل کادر محاوره ای New Formatting Rule را نمایش می دهد.
5. در ناحیه Select a Rule Type در بالای کادر محاوره ای، Format Only Cells That Contain را انتخاب کنید. (شکل 1 را ببینید.)
6. با استفاده از اولین لیست کشویی برای قانون، Blanks را انتخاب کنید.
7. روی OK کلیک کنید. اکسل کادر محاوره‌ای New Formatting Rule را می‌بندد و دوباره کادر محاوره‌ای Conditional Formatting Rules Manager را نمایش می‌دهد، این بار قانون جدید شما قابل مشاهده است. (توجه داشته باشید که هیچ قالب بندی برای این قانون مشخص نکردید، خوب است.)
8. مطمئن شوید که چک باکس Stop If True برای قانون انتخاب شده باشد.
9. روی New Rule کلیک کنید. اکسل دوباره کادر محاوره ای New Formatting Rule را نمایش می دهد.
10. در ناحیه Select a Rule Type در بالای کادر محاوره ای، Format Only Cells That Contain را انتخاب کنید.
11. با استفاده از اولین لیست کشویی برای قانون، Cell Value را انتخاب کنید.
12. با استفاده از دومین لیست کشویی برای قانون، Equal To را انتخاب کنید.
13. در کادر مقدار برای شرط 2، 0 را وارد کنید.
14. روی دکمه Format کلیک کنید. اکسل کادر محاوره ای Format Cells را نمایش می دهد.
15. از کنترل های موجود در کادر محاوره ای برای تغییر قالب بندی، به دلخواه استفاده کنید.
16. برای بستن کادر محاوره ای Format Cells روی OK کلیک کنید.
17. روی OK کلیک کنید تا کادر محاوره ای New Formatting Rule بسته شود. اکسل دوباره Conditional Formatting Rules Manager را نمایش می دهد و قاعده ای که به تازگی تعریف کرده اید اولین قاعده در لیست است. (همچنین باید انتخاب شود.)
18. روی فلش رو به پایین کلیک کنید تا قانونی که ایجاد کرده اید به جایگاه دوم لیست قوانین منتقل شود.
19. برای بستن کادر محاوره ای Conditional Formatting Rules Manager روی OK کلیک کنید. قالب بندی در محدوده سلول هایی که در مرحله 1 انتخاب کرده اید اعمال می شود.

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

شکل 2. کادر محاوره ای Format Cells.

1. محدوده ای را که می خواهید به صورت مشروط قالب بندی شود انتخاب کنید. (برای این مثال، فرض می‌کنیم که محدوده A2:A99 را انتخاب کرده‌اید.)
2. در حالی که تب صفحه اصلی نوار نمایش داده می شود، روی گزینه Conditional Formatting در گروه Styles کلیک کنید. اکسل پالتی از گزینه های مربوط به قالب بندی شرطی را نمایش می دهد.
3. روی New Rule کلیک کنید. اکسل کادر محاوره ای New Formatting Rule را نمایش می دهد.
4. در قسمت Select a Rule Type در بالای کادر محاوره ای، Use a Formula To Determine Who Cells Format را انتخاب کنید.
5. در کادر فرمول فرمول =AND(A2=0,A2<>"") را وارد کنید .
6. روی دکمه Format کلیک کنید. اکسل کادر محاوره ای Format Cells را نمایش می دهد. (شکل 2 را ببینید.)
7. از کنترل های موجود در کادر محاوره ای برای تغییر قالب بندی، به دلخواه استفاده کنید.
8. برای بستن کادر محاوره ای Format Cells روی OK کلیک کنید.
9. روی OK کلیک کنید تا کادر محاوره ای New Formatting Rule بسته شود. قالب بندی در محدوده سلول هایی که در مرحله 1 انتخاب کرده اید اعمال می شود.

فرمول استفاده شده در مرحله 5 بررسی می کند که مقدار 0 باشد و سلول خالی نباشد. تابع AND مطمئن می شود که تنها زمانی که هر دو معیار برآورده شوند، فرمول True را برمی گرداند و قالب اعمال می شود.

هر تعداد فرمول دیگری نیز وجود دارد که می توان از آنها استفاده کرد. به عنوان مثال، هر یک از فرمول های زیر را می توان در مرحله 5 جایگزین کرد:

• =AND(COUNT(A2)=1,A2=0)
• =AND(A2=0،NOT(ISBLANK(A2)))
• =AND(A2=0,LEN(A2)>0)
• =NOT(ISBLANK(A2))*(A2=0)

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

Sub FormatRed()
    TotalRows = 5000
    ColNum = 1

    For i = 1 To Cells(TotalRows, ColNum).End(xlUp).Row
        Cells(i, ColNum).Interior.ColorIndex = xlAutomatic
        If IsNumeric(Cells(i, ColNum).Value) Then
            If Cells(i, ColNum).Value = 0 Then
                Cells(i, ColNum).Interior.ColorIndex = 3
            End If
        End If
    Next
End Sub

ماکرو سلول‌های ستون A را بررسی می‌کند. (سلول‌های ردیف‌های 1 تا 5000 را بررسی می‌کند؛ در صورت تمایل می‌توانید آن را تغییر دهید.) اگر سلول حاوی یک مقدار عددی باشد و آن مقدار صفر باشد، سلول با رنگ قرمز پر می‌شود. اگر سلول حاوی چیز دیگری باشد، سلول به رنگ عادی خود باز می گردد.