فرمول را یک بار بنویسید، هزار بار کپی کنید
قدرت اکسل در این است که یک فرمول را می‌نویسید و به هزاران سطر کپی می‌کنید. اما اکسل ارجاع‌ها را «نسبی» ذخیره می‌کند: =B2*C2 در سلول D2 در واقع یعنی «سلول دو ستون قبل ضرب در سلول یک ستون قبل، در همین سطر». وقتی فرمول را به D3 کپی می‌کنید، همان دستور به =B3*C3 تبدیل می‌شود. این رفتار در بیشتر مواقع همان چیزی است که می‌خواهید، و در بقیه‌ی مواقع منشأ خطاهای پنهان.
چهار حالت ارجاع
| نوشتار | هنگام کپی | کاربرد رایج |
|---|---|---|
| A1 | سطر و ستون هر دو تغییر می‌کنند | محاسبه‌ی سطر به سطر |
| $A$1 | هیچ‌کدام تغییر نمی‌کند | نرخ مالیات، نرخ ارز، یک پارامتر ثابت |
| A$1 | سطر ثابت، ستون متغیر | سرستون‌های یک جدول دوبعدی |
| $A1 | ستون ثابت، سطر متغیر | ستون برچسب‌های یک جدول دوبعدی |
هنگام نوشتن یا ویرایش فرمول، مکان‌نما را روی ارجاع بگذارید و F4 را بزنید؛ هر بار فشار، حالت بعدی را می‌سازد: A1 ← $A$1 ← A$1 ← $A1 ← A1.
مثال ۱: پارامتر ثابت
فهرست قیمت فرش‌ها در ستون B (قیمت هر متر مربع به تومان) و متراژ در ستون C است؛ نرخ مالیات بر ارزش افزوده در سلول F1 قرار دارد، چون هر سال ممکن است تغییر کند و نباید داخل فرمول نوشته شود.
D2: =B2*C2*(1+$F$1)
کپی به D3: =B3*C3*(1+$F$1) ← B و C جابه‌جا شدند، F1 ثابت ماند
اشتباه رایج: =B2*C2*(1+F1) → در D3 می‌شود F2 که خالی است و مالیات صفر حساب میشودمثال ۲: جدول دوبعدی با ارجاع ترکیبی
می‌خواهید جدولی بسازید که ردیف‌هایش متراژهای مختلف (A3 تا A10) و ستون‌هایش درصدهای تخفیف (B2 تا F2) باشد و هر خانه قیمت نهایی را نشان دهد. قیمت پایه‌ی هر متر در H1 است. فقط یک فرمول در B3 بنویسید و به کل جدول بکشید:
B3: =$A3*$H$1*(1-B$2)
$A3 → همیشه ستون A (متراژ)، سطر آزاد
B$2 → همیشه سطر 2 (تخفیف)، ستون آزاد
$H$1 → قیمت پایه، کاملاً ثابتقاعدهی ذهنی ساده: از خودتان بپرسید «وقتی این فرمول را به راست می‌کشم، این ارجاع باید حرکت کند؟» و «وقتی به پایین می‌کشم؟». جواب هر سؤال، وجود یا نبود $ پیش از ستون و پیش از سطر را تعیین می‌کند.
نکته‌هایی که کمتر کسی می‌داند
- اگر هنگام ویرایش فرمول یک محدوده (مثلاً B2:B20) را کامل انتخاب کنید و F4 بزنید، هر دو سر محدوده هم‌زمان مطلق می‌شوند.
- F4 خارج از حالت ویرایش کار دیگری می‌کند: آخرین عمل را تکرار می‌کند (رنگ، درج سطر، Merge و…). همین تفاوت گاهی کاربران را گیج می‌کند.
- Cut کردن یک سلول ارجاع‌های وابسته را همراه خود جابه‌جا می‌کند، اما Copy این کار را نمی‌کند. برای جابه‌جا کردن داده‌ی ورودی بدون خراب کردن فرمول‌ها، Cut کنید.
- برای کپی یک بلوک فرمول بدون تغییر هیچ ارجاعی: با Find & Replace علامت = را موقتاً با # جایگزین کنید، کپی کنید و دوباره # را به = برگردانید.
- Ctrl+` (کلید زیر Esc) همه‌ی فرمول‌های شیت را به‌جای نتیجه نشان می‌دهد؛ بهترین راه برای یافتن سلولی که «دستی» تایپ شده و فرمول نیست.