وقتی فرمول از مرز شیت عبور می‌کند
ارجاع به شیت دیگر به شکل =فروش!B5 است. اگر نام شیت فاصله یا کاراکتر خاص داشته باشد، اکسل آن را داخل آپاستروف می‌گذارد: ='فروش فروردین'!B5. بهترین روش نوشتنش تایپ نیست؛ = را بزنید، روی تب شیت کلیک کنید، سلول را انتخاب و Enter.
ارجاع سه‌بعدی؛ جمع دوازده ماه با یک فرمول
اگر دوازده شیت ماهانه با ساختار کاملاً یکسان دارید (فروردین تا اسفند، کنار هم)، جمع سلول B5 همه‌ی آن‌ها این است:
=SUM(فروردین:اسفند!B5)
=SUM('فروردین:اسفند'!B5:B20)
=AVERAGE(Start:End!C7) ← دو شیت خالی به نام Start و End به‌عنوان «کتاب‌نگهدار»
ارجاع پویا به شیتی که نامش در سلول A2 است:
=INDIRECT("'"&A2&"'!B5")ترفند Start و End: دو شیت خالی بسازید و شیت‌های ماهانه را بینشان بگذارید. هر شیتی که بین آن دو کشیده شود خودکار در جمع می‌آید و هر شیتی بیرون برود حذف می‌شود. INDIRECT انعطاف زیادی دارد اما Volatile است و اگر نام شیت عوض شود، بی‌صدا خطا می‌دهد.
ارجاع به فایل دیگر (External Link)
ارجاع به فایل دیگر مثل ='[قیمت‌ها.xlsx]Sheet1'!$B$2 است. وقتی فایل مبدأ بسته باشد، مسیر کامل در فرمول ذخیره می‌شود. این لینک‌ها شکننده‌اند: جابه‌جا کردن یا تغییر نام فایل مبدأ، پیام «This workbook contains links» و مقادیر قدیمی را به دنبال دارد. مدیریت آن‌ها از Data ← Edit Links (در نسخه‌های جدید Workbook Links) است و در کلینیک فصل ۸ مفصل بررسی می‌شود. برای داده‌ی مشترک بین فایل‌ها، Power Query جایگزین بسیار بهتری است.
حالت‌های محاسبه
| کلید / گزینه | کار |
|---|---|
| Formulas ← Calculation Options ← Automatic | پیش‌فرض؛ هر تغییر، وابسته‌ها را محاسبه می‌کند |
| Automatic except for Data Tables | برای فایل‌هایی با Data Table سنگین (فصل ۶) |
| Manual | فقط با فشار کلید محاسبه می‌شود |
| F9 | محاسبه‌ی همه‌ی فایل‌های باز |
| Shift+F9 | فقط شیت جاری |
| Ctrl+Alt+F9 | محاسبه‌ی کامل، حتی سلول‌هایی که «تغییرنکرده» فرض شده‌اند |
| Ctrl+Alt+Shift+F9 | بازسازی زنجیره‌ی وابستگی‌ها و محاسبه‌ی کامل |
ارجاع دوری (Circular Reference)
وقتی فرمولی مستقیم یا غیرمستقیم به خودش ارجاع دهد، اکسل هشدار می‌دهد و نوار وضعیت نشانی آن را می‌نویسد (Formulas ← Error Checking ← Circular References). بیشتر وقت‌ها اشتباه است، مثل =SUM(B2:B10) در سلول B10. گاهی عمدی است؛ مثلاً پاداشی که درصدی از سود پس از کسر همان پاداش است. برای این موارد File ← Options ← Formulas ← Enable iterative calculation را فعال کنید و تعداد تکرار و حداکثر تغییر را تعیین کنید.
نکته‌هایی که کمتر کسی می‌داند
- حالت محاسبه تنظیم «برنامه» است، نه «فایل»: اگر اولین فایلی که باز می‌کنید روی Manual ذخیره شده باشد، همه‌ی فایل‌هایی که بعد از آن باز می‌کنید هم دستی محاسبه می‌شوند. اگر فرمول‌هایتان «به‌روز نمی‌شوند»، اول نوار وضعیت را برای کلمه‌ی Calculate نگاه کنید.
- توابع TODAY، NOW، RAND، OFFSET، INDIRECT و CELL «فرار»اند و با هر تغییری در هر جای فایل دوباره محاسبه می‌شوند؛ ده هزار INDIRECT فایل را به‌شدت کند می‌کند.
- برای نوشتن یک فرمول در چند شیت هم‌زمان، شیت‌ها را با Ctrl یا Shift گروهی انتخاب کنید (Group). فراموش نکنید بعد از کار از گروه خارج شوید، وگرنه هر تایپی در همه‌ی شیت‌ها تکرار می‌شود.
- ارجاع دوری عمدی را با یک سلول «کلید» ایمن کنید:
=IF($Z$1=0,0,…)؛ اگر محاسبه واگرا شد، Z1 را صفر کنید تا حلقه قطع و مقادیر بازنشانی شوند. - نام شیت را از داخل فرمول می‌توانید با
=TEXTAFTER(CELL("filename",A1),"]")بگیرید (فایل باید ذخیره شده باشد) تا عنوان گزارش هر شیت خودکار نام همان شیت باشد.