ساخت صورت جریان نقدی در اکسل
ساخت صورت جریان نقدی در اکسل : چرا باید جریان نقدی کسب‌وکار را کنترل کنیم؟...
آموزش حسابداری در اکسل
آموزش Conditional Formatting در اکسل برای هایلایت بدهکاران
فهرست مطالب در این مقاله:
آموزش Conditional Formatting در اکسل : وقتی تعداد مشتریان و فاکتورهای پرداخت‌نشده زیاد می‌شود، تشخیص سریع بدهکاران و پیگیری مطالبات اهمیت زیادی پیدا می‌کند. بررسی ردیف‌به‌ردیف اطلاعات نه‌تنها زمان‌بر است، بلکه ممکن است باعث شود بعضی بدهی‌های مهم یا سررسیدگذشته از چشم حسابدار دور بمانند.
قابلیت Conditional Formatting یا «قالب‌بندی شرطی» در اکسل به شما کمک می‌کند سلول‌ها و ردیف‌ها را بر اساس شرط‌های مشخص، به‌صورت خودکار رنگی کنید. برای مثال، می‌توانید بدهی‌های بیشتر از یک مبلغ مشخص را قرمز، فاکتورهای پرداخت‌شده را سبز و بدهی‌های سررسیدگذشته را با رنگی متمایز نمایش دهید.
در این آموزش یاد می‌گیرید چگونه با ابزارهای آماده اکسل و فرمول‌های شرطی، یک جدول حساب‌های دریافتنی خوانا و کاربردی بسازید.
Conditional Formatting در اکسل چیست؟
قالب‌بندی شرطی قابلیتی است که ظاهر سلول‌ها را بر اساس مقدار یا نتیجه یک شرط تغییر می‌دهد. این تغییر می‌تواند شامل رنگ پس‌زمینه، رنگ و نوع قلم، حاشیه یا نوارهای داده باشد.
برخلاف رنگ‌آمیزی دستی، قالب‌بندی شرطی با تغییر داده‌ها دوباره ارزیابی می‌شود. بنابراین اگر وضعیت پرداخت یک فاکتور را از «پرداخت‌نشده» به «پرداخت‌شده» تغییر دهید، رنگ مربوط به آن نیز می‌تواند به‌صورت خودکار به‌روزرسانی شود.
این قابلیت در حسابداری برای موارد زیر مفید است:
- شناسایی مشتریان بدهکار
- تشخیص فاکتورهای پرداخت‌نشده
- مشخص‌کردن بدهی‌های سررسیدگذشته
- هشدار درباره مبالغ بالای مطالبات
- بررسی سریع وضعیت دریافت وجه
- کنترل اطلاعات و کاهش احتمال نادیده‌گرفتن موارد مهم
مثال کاربردی: ساخت جدول بدهکاران در اکسل
فرض کنید جدولی با ستون‌های زیر دارید:
| ستون | عنوان | توضیح |
|---|---|---|
| A | شماره فاکتور | شناسه فاکتور فروش |
| B | نام مشتری | نام شخص یا شرکت |
| C | مبلغ فاکتور | مبلغ کل فاکتور |
| D | مبلغ پرداخت‌شده | وجه دریافت‌شده |
| E | مانده بدهی | مبلغ باقی‌مانده |
| F | سررسید | تاریخ سررسید فاکتور |
| G | وضعیت | پرداخت‌شده یا پرداخت‌نشده |
برای محاسبه مانده بدهی، در سلول E2 فرمول زیر را وارد کنید:
=C2-D2سپس فرمول را برای ردیف‌های بعدی کپی کنید.
اگر مبلغ فاکتور ۱۲,۰۰۰,۰۰۰ تومان و مبلغ پرداخت‌شده ۵,۰۰۰,۰۰۰ تومان باشد، مانده بدهی برابر با ۷,۰۰۰,۰۰۰ تومان خواهد بود.
نکته: در این مثال فرض شده است که مبلغ پرداخت‌شده از مبلغ فاکتور بیشتر نیست. اگر اضافه‌پرداخت، برگشت وجه یا اصلاحیه دارید، باید آن‌ها را نیز مطابق رویه حسابداری خود مدیریت کنید.
روش اول: هایلایت بدهکاران با ابزار آماده Conditional Formatting
اگر می‌خواهید تمام مانده‌های بدهی بزرگ‌تر از صفر رنگی شوند، می‌توانید از قانون آماده اکسل استفاده کنید.
مراحل انجام کار
- محدوده مانده بدهی، برای مثال
E2:E100، را انتخاب کنید. - از تب Home وارد Conditional Formatting شوید.
- گزینه Highlight Cells Rules را انتخاب کنید.
- روی Greater Than کلیک کنید.
- عدد
0را وارد کنید. - رنگ موردنظر را انتخاب کنید؛ برای مثال، پس‌زمینه قرمز روشن.
- روی OK کلیک کنید.
اکنون سلول‌هایی که مانده بدهی آن‌ها بیشتر از صفر است، با رنگ انتخاب‌شده نمایش داده می‌شوند.
این روش برای شناسایی سریع مانده‌های بدهی مناسب است؛ اما به‌تنهایی نشان نمی‌دهد که بدهی سررسید گذشته است یا خیر. برای این کار، باید تاریخ سررسید را هم در نظر بگیرید.
روش دوم: رنگی‌کردن فاکتورهای پرداخت‌نشده
اگر وضعیت پرداخت در ستون G ثبت شده باشد، می‌توانید سلول‌های این ستون را بر اساس وضعیت رنگی کنید.
مراحل انجام کار
- محدوده
G2:G100را انتخاب کنید. - از مسیر Home → Conditional Formatting → Highlight Cells Rules → Text that Contains وارد شوید.
- عبارت
پرداختنشدهرا وارد کنید. - قالب رنگی مناسب را انتخاب کنید.
- روی OK کلیک کنید.
برای وضعیت «پرداخت‌شده» نیز می‌توانید قانون جداگانه‌ای بسازید و رنگ سبز انتخاب کنید.
این روش زمانی بهترین عملکرد را دارد که وضعیت‌ها به‌شکل یکسان نوشته شده باشند. برای مثال، استفاده هم‌زمان از عبارت‌های «پرداخت نشده»، «پرداخت‌نشده» و «تسویه نشده» ممکن است مدیریت قانون‌ها را دشوار کند.
روش سوم: هایلایت بدهی‌های سررسیدگذشته با فرمول
یکی از کاربردی‌ترین روش‌ها برای حسابداران، رنگی‌کردن بدهی‌هایی است که هنوز تسویه نشده‌اند و تاریخ سررسیدشان گذشته است.
در این مثال فرض می‌کنیم:
- ستون
Eشامل مانده بدهی است. - ستون
Fشامل تاریخ سررسید است. - اطلاعات از ردیف ۲ شروع می‌شود.
مراحل ساخت قانون
- محدوده جدول، مثلاً
A2:G100، را انتخاب کنید. - از تب Home وارد Conditional Formatting شوید.
- گزینه New Rule را انتخاب کنید.
- گزینه Use a formula to determine which cells to format را انتخاب کنید.
- فرمول زیر را وارد کنید:
=AND($E2>0,$F2<TODAY())- روی Format کلیک کنید و رنگی مشخص، مانند قرمز روشن، انتخاب کنید.
- تنظیمات را تأیید کنید.
این قانون تمام ردیف‌هایی را رنگی می‌کند که مانده بدهی آن‌ها بیشتر از صفر و تاریخ سررسیدشان قبل از امروز باشد.
فرمول چگونه کار می‌کند؟
$E2>0بررسی می‌کند که مانده بدهی هنوز مثبت باشد.$F2<TODAY()بررسی می‌کند که تاریخ سررسید از تاریخ امروز قدیمی‌تر باشد.ANDفقط زمانی مقدار TRUE می‌دهد که هر دو شرط برقرار باشند.
علامت $ قبل از حرف ستون باعث می‌شود قانون همیشه ستون E و F را بررسی کند، درحالی‌که شماره ردیف متناسب با هر ردیف تغییر می‌کند.
توجه: این فرمول فرض می‌کند تاریخ‌های ستون F تاریخ واقعی اکسل هستند، نه متن. همچنین تاریخ سررسید برابر با امروز در این قانون سررسیدگذشته محسوب نمی‌شود؛ اگر می‌خواهید بدهی‌های سررسیدشده در خود امروز نیز رنگی شوند، شرط تاریخ را به <=TODAY() تغییر دهید.
روش چهارم: رنگی‌کردن کل ردیف بدهکار
گاهی رنگی‌کردن فقط سلول مانده بدهی کافی نیست و می‌خواهید تمام اطلاعات مشتری، شماره فاکتور و تاریخ سررسید در یک ردیف مشخص شوند.
برای این کار:
- محدوده
A2:G100را انتخاب کنید. - وارد Conditional Formatting → New Rule شوید.
- گزینه Use a formula to determine which cells to format را انتخاب کنید.
- فرمول زیر را وارد کنید:
=$E2>0- از بخش Format رنگ پس‌زمینه موردنظر را انتخاب کنید.
- قانون را تأیید کنید.
با این کار، هر ردیفی که مانده بدهی آن مثبت باشد، رنگی می‌شود.
اگر می‌خواهید فقط بدهکاران سررسیدگذشته مشخص شوند، به‌جای فرمول بالا از این فرمول استفاده کنید:
=AND($E2>0,$F2<TODAY())در این حالت تمام ردیف‌هایی که هم بدهی دارند و هم از تاریخ سررسیدشان گذشته است، قالب‌بندی می‌شوند.
روش پنجم: هشدار برای بدهی‌های بالاتر از یک مبلغ مشخص
ممکن است بخواهید بدهی‌های بزرگ را زودتر بررسی کنید؛ برای مثال، مانده‌های بیشتر از ۵۰,۰۰۰,۰۰۰ تومان.
اگر مانده بدهی در ستون E قرار دارد:
- محدوده
E2:E100را انتخاب کنید. - وارد Conditional Formatting → Highlight Cells Rules → Greater Than شوید.
- عدد
50000000را وارد کنید. - یک رنگ برجسته انتخاب کنید.
اگر ترجیح می‌دهید قانون با فرمول ساخته شود، از این فرمول استفاده کنید:
=$E2>50000000برای انعطاف بیشتر، مبلغ حد هشدار را در سلول J1 وارد کنید و فرمول زیر را به کار ببرید:
=$E2>$J$1اکنون با تغییر مبلغ در سلول J1، حد هشدار نیز تغییر می‌کند.
روش ششم: نمایش وضعیت‌های مختلف با رنگ‌های جداگانه
می‌توانید برای وضعیت‌های مختلف جدول، چند قانون قالب‌بندی شرطی تعریف کنید.
| وضعیت | شرط نمونه | رنگ پیشنهادی |
|---|---|---|
| پرداخت کامل | مانده بدهی برابر صفر | سبز |
| دارای بدهی | مانده بدهی بیشتر از صفر | زرد یا نارنجی |
| بدهی سررسیدگذشته | مانده مثبت و سررسید قبل از امروز | قرمز |
| بدون تاریخ سررسید | سلول تاریخ خالی | خاکستری یا هشدار جداگانه |
برای رنگی‌کردن ردیف‌های تسویه‌شده، اگر ستون E مانده بدهی باشد، می‌توانید از فرمول زیر استفاده کنید:
=$E2=0برای رنگی‌کردن بدهی‌های مثبت:
=$E2>0برای بدهی‌های سررسیدگذشته:
=AND($E2>0,$F2<TODAY())این قانون‌ها را می‌توان روی محدوده یکسانی اعمال کرد. با این حال، بهتر است منطق قانون‌ها را طوری تنظیم کنید که رنگ‌های متناقض ایجاد نشود. در صورت نیاز، ترتیب قانون‌ها و گزینه Stop If True را در Manage Rules بررسی کنید.
روش هفتم: مشخص‌کردن تاریخ سررسید خالی
اگر برخی فاکتورها مانده بدهی دارند اما تاریخ سررسیدشان وارد نشده است، می‌توانید آن‌ها را جداگانه مشخص کنید.
محدوده A2:G100 را انتخاب کنید و یک قانون مبتنی بر فرمول بسازید:
=AND($E2>0,$F2="")با انتخاب رنگی مانند زرد، ردیف‌هایی مشخص می‌شوند که مانده بدهی دارند اما تاریخ سررسید آن‌ها خالی است.
این قانون کمک می‌کند اطلاعات ناقص پیش از تهیه گزارش مطالبات بررسی شوند. البته اگر تاریخ‌ها به‌صورت متن یا با قالب نامناسب وارد شده باشند، بهتر است اعتبار داده‌های تاریخ را نیز کنترل کنید.
خطاهای رایج در Conditional Formatting و روش رفع آن‌ها
چرا ردیف اشتباهی رنگی می‌شود؟
یکی از دلایل رایج، انتخاب نادرست محدوده یا استفاده اشتباه از علامت $ است. برای رنگی‌کردن کل ردیف بر اساس مانده بدهی ستون E، محدوده باید از ردیف ۲ شروع شود و فرمول به این شکل باشد:
=$E2>0اگر فرمول را به‌شکل =$E$2>0 بنویسید، همه ردیف‌ها بر اساس مقدار یک سلول ثابت بررسی می‌شوند.
چرا بدهی سررسیدگذشته رنگی نمی‌شود؟
دلایل احتمالی:
- تاریخ در ستون F به‌صورت متن ذخیره شده است.
- مانده بدهی به‌جای عدد، متن است.
- محدوده قانون اشتباه انتخاب شده است.
- شرط تاریخ یا شماره ردیف در فرمول درست نیست.
چند سلول را بررسی کنید و مطمئن شوید تاریخ‌ها و مبالغ به‌صورت داده‌های قابل محاسبه در اکسل ذخیره شده‌اند.
چرا رنگ‌ها پس از تغییر اطلاعات به‌روزرسانی نمی‌شوند؟
ابتدا بخش Conditional Formatting → Manage Rules را باز کنید و محدوده Applies to را بررسی کنید. ممکن است قانون فقط به بخشی از جدول اعمال شده باشد یا فرمول به سلول‌های اشتباه ارجاع داده باشد.
چطور یک قانون اشتباه را حذف یا اصلاح کنم؟
محدوده موردنظر را انتخاب کنید و از مسیر Home → Conditional Formatting → Manage Rules وارد مدیریت قانون‌ها شوید. در این بخش می‌توانید فرمول، محدوده اعمال قانون، قالب رنگی یا ترتیب قانون‌ها را اصلاح کنید.
نکات حرفه‌ای برای استفاده حسابداری
- از رنگ به‌عنوان تنها معیار استفاده نکنید. وضعیت پرداخت را در یک ستون جداگانه ثبت کنید و رنگ را برای مشاهده سریع به کار ببرید.
- قانون‌ها را روی محدوده درست اعمال کنید. هنگام اضافه‌شدن فاکتورهای جدید، بررسی کنید قانون‌ها ردیف‌های جدید را نیز پوشش دهند.
- از رنگ‌های قابل تشخیص استفاده کنید. قرمز برای بدهی‌های سررسیدگذشته و سبز برای تسویه‌شده‌ها می‌تواند خوانایی گزارش را افزایش دهد.
- داده‌ها را یکدست نگه دارید. تاریخ، مبلغ و وضعیت پرداخت باید با قالب مشخص ثبت شوند.
- قانون‌های متعدد را بازبینی کنید. چند قانون متداخل ممکن است باعث شود نتیجه بصری با انتظار شما متفاوت باشد.
- پیش از گزارش نهایی، اطلاعات را کنترل کنید. قالب‌بندی شرطی خطاهای داده‌ای را به‌طور خودکار اصلاح نمی‌کند؛ فقط وضعیت‌هایی را که تعریف کرده‌اید برجسته می‌کند.
پرسش‌های متداول
چگونه بدهکاران را در اکسل قرمز کنیم؟
ستون مانده بدهی را انتخاب کنید و از مسیر Home → Conditional Formatting → Highlight Cells Rules → Greater Than شرط بزرگ‌تر از صفر را تعریف کنید؛ برای رنگی‌کردن کل ردیف نیز می‌توانید از قانون فرمولی =$E2>0 استفاده کنید.
چطور فاکتورهای سررسیدگذشته را مشخص کنیم؟
اگر مانده بدهی در ستون E و تاریخ سررسید در ستون F باشد، محدوده جدول را انتخاب کنید و قانون زیر را بسازید:
=AND($E2>0,$F2<TODAY())آیا Conditional Formatting با تغییر مبلغ به‌روزرسانی می‌شود؟
بله. اگر قانون به‌درستی تنظیم شده باشد، با تغییر مقادیر سلول‌ها اکسل شرط را دوباره بررسی می‌کند و قالب‌بندی را به‌روزرسانی می‌کند.
آیا می‌توان چند شرط را هم‌زمان بررسی کرد؟
بله. با فرمول‌هایی مانند AND و OR می‌توانید چند شرط را در یک قانون ترکیب کنید؛ برای مثال، هم مانده بدهی و هم تاریخ سررسید را بررسی کنید.
آیا قالب‌بندی شرطی اطلاعات را تغییر می‌دهد؟
خیر. این قابلیت فقط ظاهر سلول‌ها را تغییر می‌دهد و مقدار اصلی داده را اصلاح نمی‌کند. برای محاسبه مانده بدهی یا تغییر وضعیت پرداخت باید از فرمول‌ها یا ورود داده مناسب استفاده کنید.
جمع‌بندی
قابلیت Conditional Formatting به شما کمک می‌کند جدول حساب‌های دریافتنی را به گزارشی خواناتر تبدیل کنید. با چند قانون ساده می‌توانید بدهکاران، فاکتورهای پرداخت‌نشده، بدهی‌های سررسیدگذشته و مبالغ بالاتر از حد هشدار را به‌صورت خودکار مشخص کنید.
برای شروع، ابتدا ستون مانده بدهی را با فرمول محاسبه کنید، سپس یک قانون برای مانده‌های مثبت و قانون دیگری برای بدهی‌های سررسیدگذشته بسازید. در نهایت، محدوده اعمال قانون‌ها و صحت تاریخ‌ها را بررسی کنید تا گزارش شما قابل اتکاتر و پیگیری مطالبات آسان‌تر شود.
