پاکسازی و اعتبارسنجی فایل اکسل عوارض نوسازی پیش از ارسال به خزانه
کارشناس عوارض یک شهرداری، فایل Excel محاسبه عوارض نوسازی را از همکار قبلی تحویل گرفته است. در شیت «عوارض»، ستون A کد نوسازی نه‌رقمی، ستون B مساحت اعیان، ستون C کاربری (مسکونی/تجاری/اداری/انبار) و ستون D مبلغ عوارض قرار دارد. در شیت دوم با نام «نرخ»، جدولی هست: A2:A5 نام کاربری و B2:B5 نرخ هر مترمربع. چهار مشکل گزارش شده است: (۱) سلول D500 که حاوی =SUM(D2:D499) است، عددی حدود یک‌سوم مقدار مورد انتظار می‌دهد؛ (۲) در چند سطر مبلغ به‌صورت متن و همراه با واژه «ریال» تایپ شده؛ (۳) برای برخی کدها VLOOKUP مقدار #N/A برمی‌گرداند؛ (۴) به نظر می‌رسد بعضی کدها تکراری ثبت شده‌اند. مهلت ارسال فایل به خزانه‌داری پایان امروز است و امکان بازسازی فایل از ابتدا وجود ندارد.
1. علت اصلی کم‌شدن نتیجه SUM را توضیح دهید و مسیر اصلاح آن را با ذکر توابع مناسب بنویسید.
Read the worked answer
تشخیص. تابع SUM فقط سلول‌هایی را جمع می‌زند که واقعاً عدد باشند. اگر مبلغ به‌صورت متن ذخیره شده باشد (تایپ واژه «ریال»، فاصله اضافی، یا فرمت متن روی ستون)، اکسل آن سلول را نادیده می‌گیرد و خطا هم نمی‌دهد؛ به همین دلیل جمع، کوچک‌تر از انتظار است. علت شماره دو دقیقاً همین است.
کنترل سریع. در یک سلول کمکی بنویسید =COUNT(D2:D499) و آن را با =COUNTA(D2:D499) مقایسه کنید؛ اختلاف، تعداد سلول‌های متنی را نشان می‌دهد. همچنین =ISNUMBER(D2) در کنار هر سطر وضعیت را روشن می‌کند.
اصلاح.
- حذف واژه و فاصله: =TRIM(SUBSTITUTE(D2,"ریال","")) و سپس تبدیل به عدد با =VALUE(...) یا با -- قبل از آن.
- یا روش بدون فرمول: Data → Text to Columns روی ستون D، یا Paste Special → Multiply روی سلول حاوی عدد ۱.
- پس از اصلاح، ستون کمکی را به مقادیر تبدیل و جای ستون اصلی بگذارید (Copy → Paste Values) و ستون قدیمی را حذف کنید.
کنترل نهایی. جمع ستون اصلاح‌شده باید با جمع دستی چند سطر نمونه و با انتظار خزانه‌داری بخواند.
2. فرمول درست محاسبه عوارض هر سطر را با استفاده از جدول نرخ بنویسید و دلیل استفاده از ارجاع مطلق را توضیح دهید.
Read the worked answer
فرمول پیشنهادی در سلول D2:
=IFERROR(B2*VLOOKUP(TRIM(C2),نرخ!$A$2:$B$5,2,FALSE),"بررسی شود")
تحلیل اجزا.
- B2 × (...) یعنی مساحت ضرب در نرخ هر مترمربع؛ ترتیب ضرب اهمیتی ندارد.
- VLOOKUP ابتدا کاربری را در ستون اول جدول نرخ جست‌وجو می‌کند، ستون دوم (نرخ) را برمی‌گرداند و FALSE تضمین می‌کند تطابق دقیق باشد؛ با TRUE یا حذف آن، اکسل نزدیک‌ترین مقدار را می‌دهد و نتیجه بی‌معنا می‌شود.
$A$2:$B$5ارجاع مطلق است تا هنگام کشیدن فرمول به پایین، محدوده جدول نرخ جابه‌جا نشود. اگر نسبی نوشته شود، در سطرهای بعد محدوده پایین‌تر می‌رود و نتیجه غلط یا #N/A می‌شود.- TRIM فاصله‌های ابتدا و انتهای متن کاربری را حذف می‌کند؛ یکی از رایج‌ترین علل #N/A همین فاصله پنهان است.
- IFERROR به جای نمایش خطا، پیام قابل پیگیری می‌گذارد تا سطرهای مشکوک قبل از ارسال بررسی شوند.
نکته محلی: در برخی نسخه‌ها جداکننده آرگومان‌ها «;» است و باید متناسب با تنظیمات سیستم نوشته شود.
3. چگونه کدهای تکراری را پیدا و تعداد آن‌ها را گزارش می‌کنید؟ به تعامل مشکل متن/عدد نیز توجه کنید.
Read the worked answer
روش فرمولی. در ستون کمکی E2 بنویسید:
=COUNTIF($A$2:$A$499,A2)>1
و تا انتها بکشید. مقدار TRUE یعنی این کد حداقل یک بار دیگر تکرار شده است. برای شمارش تعداد سطرهای درگیر: =SUMPRODUCT(--(COUNTIF($A$2:$A$499,$A$2:$A$499)>1)) که به جای «تعداد کدهای تکراری»، «تعداد سطرهای تکراری» را می‌دهد و برای گزارش دقیق‌تر مناسب‌تر است.
روش بدون فرمول. Conditional Formatting → Highlight Cells Rules → Duplicate Values روی ستون A؛ سریع است و برای بازبینی چشمی مناسب است، ولی خروجی عددی برای گزارش نمی‌دهد.
تله متن/عدد. اگر بخشی از کدها به‌صورت عدد و بخشی به‌صورت متن ذخیره شده باشد (مثلاً کدهای دارای صفر ابتدایی یا کدهایی که هنگام ورود با آپاستروف وارد شده‌اند)، COUNTIF آن‌ها را یکسان نمی‌بیند و هم تکراری‌ها را از دست می‌دهد و هم VLOOKUP نتیجه #N/A می‌دهد. راه‌حل: یکسان‌سازی نوع داده با VALUE یا TEXT در ستون کمکی، حذف فاصله‌ها با TRIM، و حفظ صفر ابتدایی با قالب متن یا قالب سفارشی.
گزارش. پیش از ارسال، تعداد کدهای تکراری، تعداد سطرهای اصلاح‌شده و فهرست سطرهای نیازمند بررسی را در یک شیت «کنترل» ثبت کنید تا قابل ردیابی باشد.