کلینیک: پنج مشکلی که هر هفته پیش می‌آید
این درس به شکل «علامت، علت، درمان» نوشته شده تا هنگام مواجهه با مشکل سریع پیدایش کنید.
۱. فایل کند و حجیم
علامت: فایلی با چند هزار سطر ۳۰ مگابایت است و هر تغییر چند ثانیه «Calculating» نشان می‌دهد.
| علت | تشخیص | درمان |
|---|---|---|
| محدوده‌ی استفاده‌شده باد کرده | Ctrl+End به سطری دور از داده می‌رود | سطرها و ستون‌های بعد از داده را Delete (نه Clear) کنید و ذخیره |
| توابع Volatile | OFFSET، INDIRECT، TODAY، RAND با هر تغییر حساب می‌شوند | INDEX به‌جای OFFSET، ارجاع مستقیم به‌جای INDIRECT |
| ارجاع کل ستون در فرمول آرایه‌ای | SUMPRODUCT((A:A="x")*B:B) | ارجاع جدول (Structured Reference) |
| Conditional Formatting تکه‌تکه | Manage Rules صدها قانون تکراری | حذف همه و یک قانون روی کل جدول |
| نام‌های سرگردان از کپی شیت | Name Manager پر از #REF! | فیلتر Names with Errors و حذف |
| فرمت فایل | xlsx بزرگ | Save As ← Excel Binary Workbook (.xlsb) |
۲. External Links شکسته
علامت: هنگام باز شدن پیام «This workbook contains links to one or more external sources». درمان: Data ← Edit Links (در 365: Workbook Links) ← Change Source به فایل جدید، یا Break Link تا فرمول‌ها مقدار شوند. اگر پس از Break Link باز هم پیام آمد، لینک جایی غیر از سلول پنهان است: Name Manager، منبع Data Validation، قوانین Conditional Formatting، سری نمودارها و دکمه‌های متصل به ماکروی فایل دیگر. Ctrl+F با عبارت .xls، Within: Workbook و Look in: Formulas بیشترشان را پیدا می‌کند.
۳. #SPILL!
علت‌های اصلی را در فصل ۴ دیدیم (سلول پر، ادغام، داخل جدول). دو مورد کمتر شناخته‌شده: Spill range is unknown وقتی اندازه‌ی خروجی به تابع Volatile وابسته است (مثلاً SEQUENCE(RANDBETWEEN(1,10)))، و Spill range too big برای ارجاع کل ستون. اگر فایل در Excel 2019 باز شود، فرمول پویا به آرایه‌ی قدیمی {=...} با اندازه‌ی ثابت تبدیل می‌شود و با تغییر داده بزرگ نمی‌شود.
۴. تاریخ متنی
علامت: تاریخ‌ها چپ‌چین‌اند، Pivot آن‌ها را گروه نمی‌کند و =ISNUMBER(A2) مقدار FALSE می‌دهد. علت رایج: CSV با قالب dd/mm/yyyy روی ویندوزی با تنظیم mm/dd/yyyy. خطرناک این‌که 05/03/2025 بی‌صدا به ۳ مه تبدیل می‌شود و فقط 25/03/2025 متن می‌ماند؛ یعنی بخشی از داده بدون هیچ هشداری غلط است.
درمان سریع: ستون را انتخاب ← Data ← Text to Columns ← Next ← Next ← Date: DMY ← Finish
با فرمول (متن dd/mm/yyyy):
=DATE(RIGHT(A2,4), MID(A2,4,2), LEFT(A2,2))
تاریخ شمسی متنی (جدول تقویم فصل ۱):
=XLOOKUP(A2, Cal[Shamsi], Cal[Miladi])
Power Query: Change Type ← Using Locale ← Date، English (United Kingdom)۵. CSV فارسی به‌هم‌ریخته
علامت: دابل‌کلیک روی CSV به‌جای «فرش کاشان» نویسه‌هایی مثل «Ùرش» نشان می‌دهد. علت: اکسل CSV بدون BOM را با Code Page پیش‌فرض ویندوز (ANSI) باز می‌کند، در حالی که فایل UTF-8 است. فایل خراب نیست، فقط اشتباه خوانده شده؛ هرگز آن را در همین حالت ذخیره نکنید.
- Data ← Get Data ← From Text/CSV ← انتخاب فایل.
- File Origin را 65001: Unicode (UTF-8) بگذارید؛ برای خروجی نرم‌افزارهای قدیمی ایرانی 1256: Arabic (Windows) را امتحان کنید.
- Delimiter را بررسی و Transform Data را برای اصلاح ی و ک و نوع داده بزنید.
برای خروجی دادن به سامانه‌ی دیگر: Save As ← CSV UTF-8 (Comma delimited). گزینه‌ی ساده‌ی «CSV (Comma delimited)» با ANSI ذخیره می‌کند و حروف فارسی به «?» تبدیل می‌شوند.
نکته‌هایی که کمتر کسی می‌داند
- Review ← Check Performance در Excel 365 قالب‌بندی سلول‌های خالیِ بی‌استفاده را پیدا و پاک می‌کند؛ پیش از Delete دستی امتحانش کنید.
- Name Manager نام‌های مخفی را نشان نمی‌دهد؛ اگر لینک خارجی از هیچ‌جا پیدا نشد، Inspect Document ← Hidden Names یا یک خط VBA (
n.Visible = Trueروی همه‌ی نام‌ها) آن را آشکار می‌کند. - CSV با BOM (که اکسل در حالت CSV UTF-8 می‌نویسد) را بعضی اسکریپت‌ها نمی‌پذیرند و نام ستون اول را با یک کاراکتر نامرئی (U+FEFF) می‌خوانند؛ اگر ستون اول در سامانه‌ی مقصد «پیدا نمی‌شود»، علت همین است.
- برای پیدا کردن کندترین شیت، Calculation را Manual کنید و روی هر شیت Shift+F9 (محاسبه‌ی فقط همان شیت) بزنید و زمان بگیرید.
- فایلی که باز نمی‌شود را با File ← Open ← فلش کنار Open ← Open and Repair باز کنید؛ گزینه‌ی Extract Data دست‌کم مقادیر و فرمول‌ها را نجات می‌دهد.