آدرس‌دهی حرفه‌ای در اکسل – مرجع نسبی، مطلق و مختلط | فرمول‌نویسی در اکسل

فهرست مطالب و زمان بندی ویدیو

آیا می‌خواهید فرمول‌های اکسل‌تان هوشمندانه و قابل‌انتشار باشند؟ در این مقاله و ویدیو، با مفهوم کلیدی آدرس‌دهی در اکسل آشنا می‌شوید تا هنگام کپی و پر کردن فرمول‌ها دیگر نگران تغییر ارجاعات نباشید. با یادگیری مرجع نسبی، مطلق و مختلط، سرعت و دقت تحلیل‌های شما افزایش می‌یابد — حتما ویدیو را تا انتها ببینید.

 

معرفی آدرس‌دهی در اکسل

آدرس‌دهی در اکسل به معنای اشاره فرمول‌ها به سلول‌ها یا محدوده‌های خاص است. نحوه آدرس‌دهی تعیین می‌کند وقتی فرمول را کپی یا جا‌به‌جا می‌کنید، ارجاعات چگونه تغییر کنند. تسلط بر آدرس‌دهی پایه‌ای برای فرمول‌نویسی پیشرفته و تهیه گزارش‌های دقیق است.

در این مقاله به سه نوع اصلی آدرس‌دهی خواهیم پرداخت: نسبی، مطلق و مختلط، و نشان می‌دهیم هر کدام چه کاربردی دارند و چه زمانی باید از آن‌ها استفاده کنید.

ارجاع به سلول و اصول پایه

هر ارجاع در اکسل با نشانی ستون و سطر مشخص می‌شود، برای مثال A1 به سلول ستون A و سطر 1 اشاره دارد. وقتی فرمولی حاوی A1 نوشته می‌شود و آن را به سلول دیگر کپی می‌کنید، بسته به نوع آدرس (نسبی یا مطلق) ممکن است اشاره تغییر کند یا ثابت بماند.

درک اصول پایه مانند نحوه نمایش محدوده‌ها (مثل A1:B5)، انتخاب سلول‌ها و نحوه استفاده از کلید F4 برای تغییر نوع ارجاع، کمک می‌کند سریع‌تر فرمول‌های صحیح بسازید.

آدرس‌دهی نسبی (Relative Reference)

آدرس‌دهی نسبی، حالت پیش‌فرض اکسل است. اگر در سلولی فرمولی بنویسید که از A1 استفاده می‌کند و آن فرمول را یک سطر پایین کپی کنید، ارجاع خود را به A2 تغییر می‌دهد. این ویژگی برای کپی کردن فرمول‌ها در سطرها یا ستون‌های متعدد بسیار کاربردی است، چون ارجاعات به‌صورت نسبی نسبت به محل جدید تغییر می‌کنند.

مثال ساده: اگر در B1 بنویسید =A1*2 و فرمول را به B2 کپی کنید، در B2 فرمول به‌صورت =A2*2 خواهد بود. این رفتار برای عملیات تکراری و پردازش داده‌های ستونی مفید است.

آدرس‌دهی مطلق (Absolute Reference)

آدرس‌دهی مطلق با قرار دادن علامت دلار ($) قبل از ستون و سطر (مثلاً $A$1) مشخص می‌شود. ارجاع مطلق هنگام کپی یا پر کردن فرمول تغییر نمی‌کند و همواره به همان سلول ثابت اشاره می‌کند. این حالت برای زمانی مناسب است که باید یک مقدار ثابت (مثل نرخ مالیات یا ضریب خاص) در تمام فرمول‌ها استفاده شود.

برای تبدیل یک ارجاع نسبی به مطلق می‌توانید از کلید F4 استفاده کنید تا نشانی به صورت $A$1 درآید. در فرمول‌هایی که ترکیبی از ثابت‌ها و ارجاعات متغیر دارند، آدرس‌دهی مطلق نقش حیاتی ایفا می‌کند.

آدرس‌دهی مختلط (Mixed Reference)

آدرس‌دهی مختلط ترکیبی از نسبی و مطلق است: یا ستون ثابت و سطر نسبی ($A1) یا ستون نسبی و سطر ثابت (A$1). این حالت زمانی مفید است که بخواهید هنگام کپی فقط یکی از دو مؤلفه (ستون یا سطر) تغییر کند و دیگری ثابت بماند.

مثال: برای ساختن جدول ضرب که هر ردیف به یک عدد و هر ستون به عدد دیگری ارجاع می‌دهد، معمولاً از آدرس‌دهی مختلط استفاده می‌شود تا هنگام کپی سطرها یا ستون‌ها، ارجاعات درست حفظ شوند.

مثال عملی: جدول ضرب با انواع آدرس‌دهی

فرض کنید در ردیف اول اعداد 1 تا 10 و در ستون اول همان اعداد داریم. برای پر کردن داخلی جدول با حاصل‌ضرب‌ها، می‌توانیم از فرمول =A2*$B$1 یا نسخه‌ای مختلط بسته به ساختار استفاده کنیم. اگر سرستون‌ها در سطر 1 و سرسطرها در ستون A باشند، بهترین گزینه استفاده از آدرس‌دهی مختلط است تا با کپی افقی و عمودی ارجاعات درست بمانند.

در ویدیو، مثال عملی جدول ضرب نمایش داده می‌شود که نشان می‌دهد چگونه با تغییر ترکیب $ و بدون $، نتیجه نهایی تفاوت می‌کند و چرا انتخاب صحیح آدرس‌دهی مهم است.

استفاده از آدرس‌دهی در توابع اکسل

انواع آدرس‌دهی فقط در محاسبات ساده کاربرد ندارند. می‌توان آن‌ها را در توابعی مانند SUM، IF، COUNTIF، SUMIF و XLOOKUP نیز استفاده کرد.

ترکیب آدرس‌دهی با تابع SUM

برای محاسبه مجموع سلول‌های B2 تا B10 از فرمول زیر استفاده می‌شود:

=SUM(B2:B10)

اگر این فرمول باید همیشه همین محدوده را جمع کند، می‌توان محدوده را مطلق کرد:

=SUM($B$2:$B$10)

ترکیب آدرس‌دهی با تابع IF

فرض کنید حداقل نمره قبولی در سلول F1 قرار دارد. برای بررسی وضعیت دانشجو در سطر 2 می‌توان از فرمول زیر استفاده کرد:

=IF(B2>=$F$1,"قبول","مردود")

در این فرمول، نمره هر دانشجو تغییر می‌کند اما حداقل نمره قبولی همیشه از سلول F1 خوانده می‌شود.

ترکیب آدرس‌دهی با تابع COUNTIF

اگر شرط شمارش در سلول E1 نوشته شده باشد، می‌توان از فرمول زیر استفاده کرد:

=COUNTIF($B$2:$B$100,E1)

محدوده شمارش ثابت است اما شرط می‌تواند هنگام کپی فرمول تغییر کند.

ترکیب آدرس‌دهی با تابع SUMIF

برای جمع‌کردن فروش یک محصول که نام آن در سلول F2 قرار دارد، می‌توان از فرمول زیر استفاده کرد:

=SUMIF($A$2:$A$100,F2,$C$2:$C$100)

محدوده نام محصولات و محدوده فروش ثابت شده‌اند اما سلول معیار می‌تواند در سطرهای بعد تغییر کند.

ترکیب آدرس‌دهی با تابع XLOOKUP

برای جستجوی کد محصول موجود در E2 داخل یک جدول ثابت می‌توان از فرمول زیر استفاده کرد:

=XLOOKUP(E2,$A$2:$A$100,$B$2:$B$100,"یافت نشد")

در این فرمول، مقدار جستجو متغیر است اما محدوده جستجو و محدوده نتیجه ثابت هستند.

ارجاع به محدوده سلول‌ها در اکسل

برای ارجاع به چند سلول پیوسته، بین آدرس اولین و آخرین سلول از علامت دونقطه استفاده می‌شود.

برای مثال، محدوده B2 تا B10 در فرمول زیر جمع می‌شود:

=SUM(B2:B10)

یک محدوده نیز می‌تواند نسبی، مطلق یا مختلط باشد.

محدوده نسبی

=SUM(B2:B10)

محدوده مطلق

=SUM($B$2:$B$10)

محدوده مختلط

=SUM(B$2:B$10)

انتخاب نوع محدوده به این بستگی دارد که فرمول در چه جهتی کپی می‌شود و کدام سطر یا ستون باید ثابت بماند.

ارجاع به سلول در یک شیت دیگر

گاهی داده موردنیاز در شیت دیگری از همان فایل قرار دارد. در این حالت ابتدا نام شیت، سپس علامت تعجب و در پایان آدرس سلول نوشته می‌شود.

برای مثال، فرمول زیر مقدار سلول B2 از شیت Sales را نمایش می‌دهد:

=Sales!B2

اگر نام شیت دارای فاصله باشد، باید نام آن را داخل علامت نقل‌قول تکی قرار دهید:

='گزارش فروش'!B2

مرجع یک شیت دیگر نیز می‌تواند مطلق شود:

='گزارش فروش'!$B$2

تفاوت کپی و جابه‌جایی فرمول در اکسل

رفتار آدرس‌ها هنگام کپی‌کردن فرمول با جابه‌جاکردن آن یکسان نیست.

• هنگام کپی فرمول، مراجع نسبی براساس محل مقصد تغییر می‌کنند.
• هنگام جابه‌جایی فرمول، آدرس‌های داخل فرمول معمولاً به همان شکل باقی می‌مانند.
• مرجع مطلق هنگام کپی تغییر نمی‌کند.
• مرجع مختلط فقط در بخش متغیر خود تغییر می‌کند.

بنابراین، پیش از Copy و Paste یا استفاده از Fill Handle باید بررسی کنید که هر آدرس چه رفتاری در مقصد خواهد داشت.

استفاده از Named Range به‌جای آدرس سلول

در فایل‌های بزرگ می‌توان به‌جای آدرس‌هایی مانند $F$1، برای سلول‌ها نام مشخص تعریف کرد. این روش باعث می‌شود فرمول‌ها خواناتر و مدیریت آن‌ها ساده‌تر شود.

برای مثال، اگر سلول نرخ مالیات را با نام TaxRate تعریف کنید، فرمول زیر قابل استفاده خواهد بود:

=B2*TaxRate

این فرمول نسبت به نسخه‌ای که از $F$1 استفاده می‌کند، مفهوم روشن‌تری دارد و بررسی آن برای سایر کاربران آسان‌تر است.

استفاده از Structured Reference در جدول‌های اکسل

اگر محدوده داده را به Table تبدیل کنید، اکسل می‌تواند به‌جای آدرس‌های معمولی از نام ستون‌ها در فرمول استفاده کند.

برای مثال، اگر نام جدول Sales و نام ستون مبلغ Amount باشد، مجموع ستون با فرمول زیر محاسبه می‌شود:

=SUM(Sales[Amount])

Structured Referenceها برای جدول‌های درحال‌گسترش مناسب هستند؛ زیرا با اضافه‌شدن سطرهای جدید، محدوده جدول نیز به‌صورت خودکار گسترش پیدا می‌کند.

خطاهای رایج در آدرس‌دهی اکسل و روش رفع آن‌ها

1. تغییر ناخواسته سلول ثابت هنگام کپی فرمول

این خطا زمانی رخ می‌دهد که برای سلولی مانند نرخ مالیات یا نرخ ارز از مرجع نسبی استفاده شده باشد.

فرمول نادرست:

=B2*F1

فرمول صحیح:

=B2*$F$1

برای رفع مشکل، آدرس سلولی را که باید ثابت بماند به مرجع مطلق تبدیل کنید.

2. ثابت‌کردن اشتباه سطر یا ستون

گاهی کاربر به‌جای $A1 از A$1 استفاده می‌کند. در نتیجه هنگام کپی افقی یا عمودی، بخش اشتباهی از آدرس تغییر می‌کند.

قبل از کپی فرمول مشخص کنید که کدام مورد باید ثابت باشد:

• اگر ستون باید ثابت باشد، دلار را قبل از حرف ستون قرار دهید.
• اگر سطر باید ثابت باشد، دلار را قبل از شماره سطر قرار دهید.

3. استفاده از مرجع مطلق برای تمام سلول‌ها

ثابت‌کردن تمام آدرس‌ها همیشه راه‌حل مناسبی نیست. اگر تمام مراجع را مطلق کنید، فرمول در تمام سطرها به همان سلول‌ها اشاره می‌کند و محاسبات ردیفی درست انجام نمی‌شوند.

فقط بخش‌هایی را ثابت کنید که واقعاً نباید هنگام کپی تغییر کنند.

4. خراب‌شدن جدول ضرب هنگام کپی فرمول

استفاده از مرجع نسبی برای هر دو سلول باعث می‌شود هنگام کپی دوبعدی، سطر و ستون‌های مبنا تغییر کنند.

فرمول مناسب جدول ضرب باید از دو مرجع مختلط استفاده کند:

=$A2*B$1

5. فراموش‌کردن علامت نقل‌قول در نام شیت

اگر نام شیت دارای فاصله باشد، باید نام آن داخل علامت نقل‌قول تکی قرار گیرد.

فرمول صحیح:

='گزارش ماهانه'!B2

6. انتخاب محدوده ناقص در توابع

در توابعی مانند SUMIF، COUNTIF یا XLOOKUP ممکن است محدوده‌ها هنگام کپی تغییر کنند و بخشی از داده‌ها خارج از محاسبه قرار بگیرد.

در صورت ثابت‌بودن جدول اصلی، محدوده‌ها را مطلق کنید:

=COUNTIF($A$2:$A$100,E2)

7. اشتباه‌گرفتن علامت دلار با قالب پولی

علامت دلار داخل فرمول برای قفل‌کردن سطر و ستون استفاده می‌شود. این علامت با قالب Currency یا Accounting که برای نمایش واحد پول به کار می‌رود متفاوت است.

نکات حرفه‌ای و ترفندهای مهم

• قبل از کپی فرمول، مشخص کنید کدام سلول‌ها باید تغییر کنند و کدام سلول‌ها باید ثابت بمانند.
• برای تغییر سریع نوع مرجع از کلید F4 استفاده کنید.
• پس از نوشتن اولین فرمول، آن را در یک یا دو سلول آزمایش کنید و سپس در کل جدول کپی کنید.
• برای داده‌های ثابت مانند نرخ مالیات، نرخ ارز و درصد تخفیف از مرجع مطلق استفاده کنید.
• برای جدول‌های دوبعدی مانند جدول ضرب یا سناریوهای قیمت‌گذاری از مرجع مختلط استفاده کنید.
• برای افزایش خوانایی فایل‌های بزرگ، از Named Range استفاده کنید.
• برای داده‌هایی که مرتباً سطر جدید دریافت می‌کنند، محدوده را به Excel Table تبدیل کنید.
• فرمول‌های پیچیده را به بخش‌های کوچک‌تر تقسیم کنید و رفتار هر مرجع را جداگانه بررسی کنید.
• هنگام بررسی فرمول، به محل دقیق علامت‌های دلار توجه کنید.
• از ثابت‌کردن بی‌دلیل تمام آدرس‌ها خودداری کنید.

روش انتخاب نوع آدرس مناسب

برای انتخاب نوع Reference می‌توانید این سه سؤال را از خود بپرسید:

آیا سطر باید هنگام کپی تغییر کند؟

• اگر بله، شماره سطر را بدون دلار بنویسید.
• اگر خیر، قبل از شماره سطر دلار قرار دهید.

آیا ستون باید هنگام کپی تغییر کند؟

• اگر بله، نام ستون را بدون دلار بنویسید.
• اگر خیر، قبل از نام ستون دلار قرار دهید.

فرمول در چه جهتی کپی می‌شود؟

• برای کپی عمودی، تغییر شماره سطر اهمیت بیشتری دارد.
• برای کپی افقی، تغییر نام ستون اهمیت بیشتری دارد.
• برای کپی دوبعدی، معمولاً باید از مراجع مختلط استفاده شود.

تمرین‌های پیشنهادی آدرس‌دهی در اکسل

تمرین اول: محاسبه مبلغ فاکتور

جدولی شامل قیمت، تعداد و مبلغ کل ایجاد کنید. با استفاده از مرجع نسبی، مبلغ هر محصول را محاسبه کنید.

=B2*C2

تمرین دوم: محاسبه مالیات

در یک سلول درصد مالیات را وارد کنید و با استفاده از مرجع مطلق، مالیات تمام محصولات را محاسبه کنید.

=D2*$F$1

تمرین سوم: محاسبه قیمت نهایی

مبلغ محصول و مالیات را با یکدیگر جمع کنید:

=D2+E2

تمرین چهارم: ساخت جدول ضرب

با استفاده از دو مرجع مختلط، جدولی از حاصل‌ضرب اعداد 1 تا 10 ایجاد کنید.

=$A2*B$1

تمرین پنجم: جدول سناریوی تخفیف

قیمت محصولات را در یک ستون و درصدهای تخفیف را در یک سطر قرار دهید. سپس با مرجع مختلط، قیمت نهایی هر محصول را با درصدهای مختلف محاسبه کنید.

=$B2*(1-C$1)

سازگاری آدرس‌دهی با نسخه‌های مختلف اکسل

مراجع نسبی، مطلق و مختلط از قابلیت‌های پایه Excel هستند و در نسخه‌های رایج دسکتاپ، نسخه مک و Excel برای وب قابل استفاده‌اند.

ممکن است روش استفاده از کلیدهای تابع در بعضی لپ‌تاپ‌ها یا سیستم‌عامل‌ها متفاوت باشد. برای مثال، در برخی دستگاه‌ها برای اجرای F4 باید از ترکیب Fn و F4 استفاده کنید.

خود مفهوم آدرس‌دهی و استفاده از علامت دلار در نسخه‌های مختلف تغییر نمی‌کند و فایل‌های دارای این نوع فرمول‌ها معمولاً میان نسخه‌های مختلف سازگار هستند.

سؤالات پرتکرار درباره آدرس‌دهی در اکسل

آدرس‌دهی نسبی در اکسل چیست؟

در آدرس‌دهی نسبی، نام ستون و شماره سطر هنگام کپی فرمول متناسب با محل جدید تغییر می‌کنند. A1 نمونه‌ای از مرجع نسبی است.

آدرس‌دهی مطلق در اکسل چیست؟

در آدرس‌دهی مطلق، ستون و سطر هر دو ثابت هستند و هنگام کپی فرمول تغییر نمی‌کنند. $A$1 نمونه‌ای از مرجع مطلق است.

آدرس‌دهی مختلط چیست؟

در آدرس‌دهی مختلط، فقط سطر یا فقط ستون ثابت می‌شود. $A1 و A$1 دو شکل اصلی مرجع مختلط هستند.

علامت دلار در اکسل چه کاربردی دارد؟

علامت دلار برای ثابت‌کردن سطر، ستون یا هر دو در آدرس سلول استفاده می‌شود.

چگونه یک سلول را در فرمول اکسل ثابت کنیم؟

قبل از حرف ستون و شماره سطر علامت دلار قرار دهید. برای مثال، برای ثابت‌کردن سلول F1 از $F$1 استفاده کنید.

چرا فرمول بعد از کپی اشتباه می‌شود؟

یکی از دلایل رایج این است که سلولی که باید ثابت باشد با مرجع نسبی نوشته شده یا علامت دلار در محل نامناسب قرار گرفته است.

کلید میانبر ثابت‌کردن سلول چیست؟

هنگام ویرایش فرمول و انتخاب آدرس سلول، کلید F4 میان انواع مرجع نسبی، مطلق و مختلط جابه‌جا می‌شود.

برای جدول ضرب از چه نوع آدرس‌دهی استفاده می‌شود؟

برای جدول ضرب باید از مرجع مختلط استفاده شود تا ستون اعداد عمودی و سطر اعداد افقی ثابت بمانند.

=$A2*B$1

تفاوت $A1 و A$1 چیست؟

در $A1 ستون A ثابت است اما سطر تغییر می‌کند. در A$1 سطر 1 ثابت است اما ستون تغییر می‌کند.

آیا می‌توان در توابع اکسل از مرجع مطلق استفاده کرد؟

بله. مراجع مطلق و مختلط را می‌توان در تمام فرمول‌ها و توابعی که از آدرس سلول یا محدوده استفاده می‌کنند به کار برد.

جمع‌بندی آموزش آدرس‌دهی در اکسل

آدرس‌دهی در اکسل یکی از ضروری‌ترین مهارت‌ها برای یادگیری فرمول‌نویسی است. آدرس‌دهی نسبی زمانی استفاده می‌شود که مرجع باید هنگام کپی تغییر کند. آدرس‌دهی مطلق برای سلول‌هایی مناسب است که باید همیشه ثابت باقی بمانند. آدرس‌دهی مختلط نیز زمانی کاربرد دارد که فقط سطر یا فقط ستون باید ثابت شود.

• A1 یک مرجع نسبی است.
• $A$1 یک مرجع مطلق است.
• $A1 مرجع مختلط با ستون ثابت است.
• A$1 مرجع مختلط با سطر ثابت است.

برای تسلط کامل، پیشنهاد می‌شود ابتدا مثال‌های ساده فروش و مالیات را انجام دهید و سپس جدول ضرب و جدول سناریوی تخفیف را بسازید. بعد از یادگیری این مبحث، می‌توانید آموزش عملگرهای محاسباتی، توابع پایه، فرمول‌های شرطی و توابع جستجو در اکسل را دنبال کنید.

ویدیوی آموزش آدرس‌دهی در اکسل

در ویدیوی این آموزش، مرجع نسبی، مطلق و مختلط به‌صورت مرحله‌به‌مرحله توضیح داده شده و در پایان، کاربرد آن‌ها با مثال جدول ضرب بررسی می‌شود.

[کد یا لینک ویدیوی آموزش آدرس‌دهی در اکسل در این بخش قرار گیرد]

پیشنهاد برای مطالعه بعدی

• [لینک آموزش فرمول‌نویسی در اکسل]
• [لینک آموزش عملگرهای محاسباتی در اکسل]
• [لینک آموزش تابع IF در اکسل]
• [لینک آموزش تابع SUMIF در اکسل]
• [لینک آموزش تابع XLOOKUP در اکسل]

آنچه در ویدیو گفته ام

سلام! به جلسه اول از بخش فرمول‌نویسی در اکسل خوش آمدید. در این جلسه قرار است درباره آدرس‌دهی یا Reference در نرم‌افزار اکسل با هم کار کنیم. به‌جرئت می‌توانم بگویم بعد از بیش از سال کار با اکسل، این آموزش شاید مهم‌ترین بخش آموزش فرمول‌نویسی در اکسل باشد. با یادگیری این بخش می‌توانید خیلی سریع‌تر از امکانات جذاب اکسل استفاده کنید. پس حتماً تا پایان این آموزش همراه من باشید. من پویا حیاتی هستم و آموزش‌های تخصصی را به زبان ساده به شما یاد می‌دهم. همان‌طور که می‌دانیم، صفحه‌گسترده اکسل از ستون‌هایی تشکیل شده که نام آن‌ها A، B، C، D و به همین ترتیب تا انتهاست؛ ردیف‌ها هم با اعداد ۱، ۲، و... مشخص می‌شوند. هر سلول از تقاطع یک ستون و یک ردیف تشکیل شده است. اگر بخواهیم این سلول را نام‌گذاری کنیم، ابتدا حرف ستون و سپس شماره ردیف را می‌نویسیم. برای مثال، سلولی که انتخاب کرده‌ام G7 است. به همین شکل می‌توانیم به سلول‌های موجود در اکسل Reference بدهیم. اگر دقت کنید، هر سلولی را که انتخاب می‌کنم، در کادر Name Box نام آن نمایش داده می‌شود. مثلاً نام این سلول G8 و سلول کناری آن H8 است. با حرکت به پایین نیز شماره ردیف‌ها تغییر می‌کند. خیلی راحت در اکسل با نوشتن علامت مساوی، از اکسل می‌خواهیم محاسبه‌ای انجام دهد، فرمولی را اجرا کند یا ارجاعی ایجاد کند. وقتی علامت مساوی را می‌نویسم، در واقع به اکسل اعلام می‌کنم که قرار است دستوری به آن بدهم. فرض کنید می‌خواهم سلولی را که عبارت «کتاب» داخل آن است انتخاب کنم. می‌توانم با ماوس روی آن بروم و زمانی که علامت مساوی فعال است، روی سلول «کتاب» کلیک کنم. اکسل فرمول را کامل می‌کند و می‌نویسد =F4؛ یعنی از این سلول به ستون F و ردیف ارجاع داده‌ام. اگر Enter را بزنم، مقدار داخل آن سلول نمایش داده می‌شود و این سلول همیشه برابر با F4 خواهد بود. اگر مقدار سلول اصلی را ویرایش کنم و مثلاً بنویسم «کتاب فارسی»، می‌بینید سلولی که به آن ارجاع داده بودیم نیز تغییر می‌کند؛ چون همیشه باید برابر با F4 باشد و محتوای همان سلول را به ما نمایش دهد. این ارجاع را به‌صورت دستی هم می‌توان نوشت. مثلاً اگر بخواهم به «دفتر» ارجاع بدهم، علامت مساوی را می‌نویسم. نام آن سلول چیست؟ F5. بنابراین به‌راحتی می‌توانم =F5 را بنویسم و به سلول موردنظر ارجاع بدهم. پس با توجه به آدرس هر سلول، می‌توانم به همان سلول ارجاع بدهم، مقدار آن را دریافت کنم یا بعداً در فرمول‌نویسی از آن استفاده کنم تا محاسبات و کارهای مختلفی را انجام دهم. اما چند نوع آدرس‌دهی داریم؟ سه نوع آدرس‌دهی بسیار مهم عبارت‌اند از: آدرس‌دهی نسبی، آدرس‌دهی مطلق و آدرس‌دهی مختلط که در این جلسه به آن‌ها می‌پردازیم و چند مثال کاربردی هم بررسی می‌کنیم. آدرس‌دهی نسبی چیست؟ همان چیزی است که اکنون می‌بینید؛ علامت مساوی را می‌نویسم و به یک سلول ارجاع می‌دهم. این یک آدرس نسبی است. در آدرس نسبی، زمانی که فرمول را کپی و Paste می‌کنیم، آدرس تغییر می‌کند. در این سلول =F4 نوشته شده است. من آن را کپی می‌کنم و در سلول پایینی Paste می‌کنم. ببینید به چه چیزی تبدیل شد: =F5. یعنی آدرس با توجه به محلی که فرمول را در آن Paste می‌کنیم، تغییر می‌کند. می‌توانم آن را از گوشه سلول تا پایین بکشم. در نتیجه عبارت‌های «کتاب فارسی»، «دفتر» و «خودکار» نمایش داده می‌شوند. اگر یک ردیف دیگر پایین بروم، چون سلول مرجع خالی است، مقدار صفر نمایش داده می‌شود. آدرس نسبی برای ما بسیار پرکاربرد است. حالا اولین مثال آدرس نسبی را بررسی کنیم. در این جدول، اجازه بدهید دوباره آن را به حالت قبل برگردانم. میزان فروش کتاب را در فصل‌های بهار، تابستان، پاییز و زمستان به تومان نوشته‌ام و می‌خواهم ببینم در مجموع چقدر کتاب فروخته‌ام. می‌توانم از یک فرمول استفاده کنم. چون هنوز فرمول‌ها را یاد نگرفته‌ایم و جلسه اول است، خیلی ساده این کار را انجام می‌دهم؛ ابتدا این سلول را انتخاب می‌کنم، به‌اضافه این سلول، به‌اضافه سلول بعدی و به‌اضافه سلول آخر. بعداً از تابع SUM استفاده می‌کنیم؛ اما فعلاً استفاده از این روش کمک می‌کند با آدرس‌دهی و فرمول‌نویسی بهتر آشنا شوید. پس من نوشته‌ام: مساوی، این چهار سلول را با هم جمع کن؛ درست مثل یک ماشین‌حساب که محاسبه را برای من انجام می‌دهد. وقتی Enter را بزنم، این چهار مقدار را جمع می‌کند و نتیجه ۱۲٬۹۰۰ را نمایش می‌دهد. آیا لازم است برای سلول بعدی دوباره همین فرمول را بنویسم؟ یعنی دوباره بنویسم این سلول به‌اضافه این سلول، به‌اضافه این و به‌اضافه این؟ این کار انجام می‌شود، اما دشوار و زمان‌بر است. ما می‌خواهیم از اکسل برای ساده‌تر شدن کارها استفاده کنیم. اینجا آدرس‌دهی نسبی دقیقاً به ما کمک می‌کند. همان‌طور که می‌بینید، سلول‌های G4، H4، I4 و J4 را با هم جمع کرده است. کافی است ماوس را روی مربع کوچک گوشه سلول، یعنی Fill Handle، قرار بدهم و آن را به پایین بکشم؛ در واقع تقریباً همان کار کپی و Paste را انجام می‌دهم. با این کار چه اتفاقی افتاد؟ چون فرمول را یک ردیف پایین بردم، آدرس‌ها نیز یک ردیف پایین آمدند و تمام شماره ردیف‌های به تبدیل شدند. اگر روی سلولی که فرمول دارد دوبار کلیک کنم، می‌بینید سلول‌هایی که در فرمول به آن‌ها ارجاع داده شده است، نمایش داده می‌شوند. همین کار را می‌توانم این بار با کپی و Paste انجام دهم؛ یعنی Ctrl+C را بزنم و در سلول بعدی Ctrl+V یا Paste کنم. اگر دوباره روی سلول دوبار کلیک کنم، سلول‌های مرجع فرمول را به من نشان می‌دهد. اگر آدرس‌دهی نسبی نبود، کار ما بسیار دشوار می‌شد. با این روش می‌توانم فرمول‌ها را به‌راحتی بنویسم و کپی کنم تا نتایج مربوط به ردیف‌های جدید محاسبه شوند. همچنین می‌توانم روی همان فرمول اول که مطمئن شده‌ام درست کار می‌کند، از مربع گوشه سلول دوبار کلیک کنم تا اکسل تمام فرمول‌ها را تا آخرین ردیف دارای داده، به‌صورت هوشمند پر کند. پس در آدرس‌دهی نسبی، با تغییر محل فرمول و کپی کردن آن، آدرس‌ها هم تغییر می‌کنند و این ویژگی بسیار پرکاربرد است. حالا به آدرس‌دهی مطلق یا Absolute Reference می‌پردازیم و می‌بینیم چه کاری برای ما انجام می‌دهد. در آدرس مطلق، با کپی کردن فرمول، آدرس تغییر نمی‌کند. در مثال قبلی G4 به G5 تبدیل شد، اما اینجا می‌خواهیم از این تغییر جلوگیری کنیم. مثال عملی آن چیست؟ فرض کنید می‌خواهم جمع کل فروش را به دست بیاورم. فعلاً از توابع اکسل استفاده نمی‌کنم و می‌نویسم: K4 به‌اضافه K5، به‌اضافه K6. این می‌شود جمع کل فروش. نتیجه را اینجا قرار می‌دهم و عنوان آن را هم «فروش کل» می‌نویسم. کمی رنگ و قالب‌بندی هم به آن می‌دهم. حالا می‌خواهم مثال دیگری بزنم. می‌خواهیم ببینیم کتاب چند درصد از فروش کل بوده، دفتر چند درصد و خودکار چند درصد از فروش ما را تشکیل داده است. برای این کار یک ستون جدید با عنوان «درصد از فروش» ایجاد می‌کنم. برای محاسبه درصد، سلول مربوط به فروش کتاب را انتخاب می‌کنم. فرمول برابر است با فروش کل کتاب، تقسیم بر فروش کل همه محصولات؛ یعنی =K4/K7. قالب سلول را روی Percentage قرار می‌دهم و نتیجه درصد می‌شود. اگر روی سلول کلیک کنم، فرمول =K4/K7 را می‌بینم. اما اگر این فرمول را تا پایین بکشم، چه اتفاقی می‌افتد؟ اکسل خطا می‌دهد. چرا؟ چون هنگام کپی کردن فرمول، هر دو آدرس تغییر می‌کنند. ارجاع‌های نسبی به ردیف‌های بعدی منتقل می‌شوند؛ زیرا در حال حاضر آدرس‌دهی ما نسبی است. حالا دلیل خطا را بررسی کنیم. مخرج کسر به یک سلول خالی می‌رسد و تقسیم بر صفر انجام می‌شود؛ بنابراین خطای #DIV/0! نمایش داده می‌شود. چگونه می‌توانم این مشکل را برطرف کنم؟ مقدار فروش کل در سلول K7 قرار دارد و جای آن تغییر نمی‌کند، اما هنگامی که فرمول را کپی می‌کنم و پایین می‌برم، آدرس K7 نیز تغییر می‌کند. باید جلوی این تغییر را بگیرم. در فرمول اول، K4 بر K7 تقسیم شده و این ارجاع، یعنی K7، باید قفل شود تا با جابه‌جایی فرمول تغییر نکند. ساده‌ترین روش این است که K7 را در فرمول انتخاب کنم و کلید F4 را بزنم. اکسل قبل از حرف K و قبل از عدد علامت دلار قرار می‌دهد و فرمول به $K$7 تبدیل می‌شود. با این کار ارجاع قفل می‌شود. یک نکته کاربردی دیگر را هم ببینید: اگر دوباره F4 را بزنم، حالت قفل تغییر می‌کند؛ یک بار فقط ردیف قفل می‌شود، بار بعد فقط ستون K قفل می‌شود و با بار دیگر به حالت نسبی برمی‌گردد. بنابراین می‌توانم از کلید F4 استفاده کنم یا علامت دلار را دستی قبل از K و بنویسم؛ یعنی با $K$7 هم ستون K و هم ردیف را قفل کنم. حالا Enter را می‌زنم. دوباره به فرمول برمی‌گردم و آن را برای سه ردیف کپی می‌کنم. می‌بینید که صورت کسر از K4 به K5 و K6 تغییر می‌کند، اما $K$7 قفل شده و دیگر تغییر نمی‌کند. به همین سادگی درصد فروش هر محصول را محاسبه کردیم و مجموع آن‌ها درصد است. اجازه بدهید اینجا با تابع SUM مجموع درصدها را هم محاسبه کنیم. نتیجه درصد می‌شود و گزارش را کامل می‌کند. پس در آدرس‌دهی مطلق، هنگام کپی کردن فرمول، سلول موردنظر را قفل می‌کنیم تا در همه فرمول‌ها فقط از مقدار همان سلول استفاده شود و آدرس آن جابه‌جا نشود. نوع دیگری از آدرس‌دهی نیز داریم به نام آدرس‌دهی مختلط یا Mixed Reference. این نوع آدرس‌دهی چگونه کار می‌کند؟ در حالت اول نه ردیف و نه ستون را قفل نکردیم. در حالت دوم، برای $K$7 هم ستون و هم ردیف را قفل کردیم. در آدرس‌دهی مختلط، یکی را باز می‌گذاریم و دیگری را قفل می‌کنیم. کاربرد آن چیست؟ فرض کنید می‌خواهیم با وجود ستون‌های بهار، تابستان، پاییز و زمستان، مشخص کنیم فروش هر فصل چه درصدی از کل فروش سالانه کتاب یا دفتر را تشکیل داده است. جدول را در این قسمت کپی می‌کنم. اکنون ردیف کتاب و دفتر را داریم. عنوان‌های بهار، تابستان، پاییز و زمستان را هم به این بخش منتقل می‌کنم. می‌خواهیم ببینیم فروش کتاب در بهار چند درصد از کل فروش آن بوده، تابستان چند درصد، و همین‌طور پاییز و زمستان. برای محاسبه درصد می‌نویسیم: مساوی با مقدار فروش فصل، تقسیم بر کل فروش همان محصول. قالب را درصدی می‌کنم و نتیجه درصد می‌شود. حالا اگر این فرمول را به سمت راست بکشم، چه اتفاقی می‌افتد؟ در ابتدا خطا ایجاد می‌شود. پس باید مخرج را قفل کنم، چون می‌خواهم همه فصل‌ها بر کل فروش همان محصول تقسیم شوند. روی آدرس مخرج F4 را می‌زنم و فرمول را به سمت راست می‌کشم. همان‌طور که می‌بینید، هر ستون مقدار فصل خودش را می‌خواند؛ پاییز از ستون پاییز و زمستان از ستون زمستان، و همه آن‌ها بر فروش کل محصول تقسیم می‌شوند. مجموع این درصدها را هم با تابع SUM محاسبه می‌کنم. نتیجه برابر با یا همان درصد است. اما اگر فرمول نوشته‌شده برای کتاب را بخواهم برای ردیف دفتر نیز استفاده کنم، آن را کپی می‌کنم و به ردیف پایین می‌کشم. می‌بینید نتیجه درست نیست و مجموع درصدهای دفتر درصد شده، درحالی‌که باید درصد باشد. بیایید بررسی کنیم چه اتفاقی افتاده است. جدول را کمی بالاتر می‌برم تا فضای بیشتری داشته باشیم. روی سلول مربوط به دفتر کلیک می‌کنم؛ صورت کسر درست از فروش دفتر خوانده می‌شود، اما مخرج همچنان فروش کل کتاب را می‌گیرد. دلیلش این است که در فرمول قبلی گفته بودیم سلول K4 را به‌صورت $K$4 کاملاً قفل کن. اما حالا می‌خواهیم فرمول را پایین بکشیم و نباید شماره ردیف ثابت بماند. چه اصلاحی باید انجام دهیم؟ وقتی فرمول را به سمت پایین می‌کشم، مخرج باید از K4 به K5 و سپس K6 تغییر کند. حرف K ثابت است، اما شماره ردیف‌های ۴، و باید تغییر کنند. پس باید اجازه بدهیم شماره ردیف تغییر کند. برای این کار فرمول را اصلاح می‌کنم. قفل شماره ردیف را برمی‌دارم و فقط ستون K را قفل می‌کنم؛ یعنی آدرس $K4. چون همه مجموع‌ها در ستون K هستند، هنگام کپی افقی باید ستون K ثابت بماند، اما هنگام کشیدن فرمول به پایین، عدد باید به و سپس تبدیل شود. بنابراین علامت دلار قبل از عدد را حذف می‌کنم. سپس فرمول را دوباره به تمام ستون‌های همان ردیف کپی می‌کنم؛ در حرکت افقی تغییری در مخرج نمی‌بینید. حالا فرمول‌ها را تا ردیف‌های پایین می‌کشم. نتیجه کاملاً درست است و هر سلول از مقدار مربوط به همان فصل و همان محصول استفاده می‌کند. یعنی با استفاده از آدرس‌دهی مختلط، یک بخش آدرس را قفل و بخش دیگر را آزاد گذاشتیم تا فرمول در همه جهت‌ها خروجی صحیح بدهد. حالا سراغ یک مثال جذاب می‌رویم: ساخت جدول ضرب با انواع آدرس‌دهی. در این مثال، مفهوم آدرس‌دهی را دوباره مرور می‌کنیم. این تمرین را حتماً خودتان نیز انجام دهید؛ با انجام آن، در استفاده از Reference بسیار حرفه‌ای‌تر می‌شوید. اگر بخواهم جدول ضرب را فقط با آدرس‌دهی نسبی بسازم، باید تک‌تک فرمول‌ها را بنویسم؛ مثلاً مساوی با این سلول ضرب‌در آن سلول. نمی‌توانم همان فرمول را به پایین بکشم، چون نتیجه اشتباه می‌شود؛ زیرا آدرس‌ها هم‌زمان تغییر می‌کنند و سلول‌های نامناسب در هم ضرب می‌شوند. پس این روش کار ما را راه نمی‌اندازد و مجبور می‌شوم برای ساخت جدول ضرب فقط با آدرس‌دهی نسبی، فرمول هر سلول را جداگانه بنویسم؛ برای ردیف بعد نیز دوباره بنویسم مساوی با این سلول ضرب‌در آن سلول. برای کامل شدن جدول ضرب با این روش، باید فرمول بنویسم که اصلاً منطقی نیست. تا رسیدن به ضرب‌در ۱۰، باید فرمول سلول را به‌صورت جداگانه وارد کنم تا جدول کامل شود. ما از اکسل استفاده می‌کنیم تا کارها سریع‌تر انجام شوند، نه اینکه زمان بیشتری صرف کنیم. حالا ببینیم با آدرس‌دهی مطلق چه می‌توان کرد. در اولین سلول می‌نویسم: مساوی با A2 ضرب‌در B1. وقتی می‌خواهم فرمول را به پایین کپی کنم، A2 باید تغییر کند، چون محاسبات به‌ترتیب ۱×۱، ۲×۱، ۳×۱ و ۴×۱ هستند؛ یعنی شماره ردیف A تغییر می‌کند، اما B1 باید ثابت بماند. بنابراین B1 را انتخاب می‌کنم و با زدن F4 آن را به $B$1 تبدیل و کاملاً قفل می‌کنم. حالا اگر فرمول را تا انتهای ستون کپی کنم، نتیجه درست است و هر عدد در ضرب می‌شود. برای ستون دوم نیز می‌توانم همین کار را انجام دهم؛ فرمول جدید را می‌نویسم تا همه اعداد این بار در ضرب شوند. سلول مربوط به عدد را هم با F4 قفل می‌کنم و فرمول را تا پایین کپی می‌کنم. همین کار را برای ستون‌های بعدی نیز به‌سرعت انجام می‌دهم. با این روش می‌توانم جدول ضرب را با فرمول کامل کنم؛ هر فرمول را یک‌بار می‌نویسم و تا انتهای ستون می‌کشم. در مجموع بار فرمول می‌نویسم. همین کار را می‌توانیم در جهت افقی هم انجام دهیم؛ یعنی عدد ۱، ۲، و در مقدار ثابت ردیف ضرب شوند. کافی است یکی از آدرس‌ها آزاد و دیگری ثابت باشد. در این حالت سلول A2 را کاملاً قفل می‌کنم و فرمول را به سمت راست می‌کشم. برای ردیف بعد نیز فرمول مشابهی می‌نویسم، سلول مرجع را با F4 قفل می‌کنم و فرمول را افقی ادامه می‌دهم. هنگام فرمول‌نویسی باید دقت کنید فرمول را در کدام جهت کپی می‌کنید. اگر افقی کپی می‌کنیم، بخشی از آدرس باید آزاد و سلول مرجع مناسب ثابت باشد. وقتی فرمول را عمودی می‌کشیم، برعکس، آدرس متناسب با همان جهت را آزاد می‌گذاریم و سلول مرجع را قفل می‌کنیم. این نکته را حتماً به خاطر بسپارید. اما در هر صورت، برای تکمیل جدول ضرب با آدرس‌دهی مطلق باید فرمول بنویسم و بقیه را کپی کنم. کار ساده‌تر شده، اما هنوز بهترین روش نیست. حالا می‌خواهم از آدرس‌دهی مختلط استفاده کنم. در اولین سلول جدول قرار می‌گیرم. اجازه بدهید آن را کمی بزرگ‌تر کنم تا بهتر ببینید. در این سلول می‌نویسم: =A2*B1. حالا باید درباره جهت کپی فرمول فکر کنیم. وقتی فرمول را به سمت پایین می‌کشم، چه اتفاقی باید بیفتد؟ می‌خواهم A2 به A3، A4 و... تغییر کند و این تغییر مشکلی ندارد؛ اما شماره ردیف B1 نباید تغییر کند. پس در بخش B1، ردیف را ثابت نگه می‌دارم، درحالی‌که A2 باید با حرکت رو به پایین تغییر کند. اجازه بدهید فرمول را در جهت مناسب بنویسم تا توضیح آن ساده‌تر باشد. پس A2، A3 و A4 باید تغییر کنند، چون فرمول را به سمت پایین می‌کشم؛ اما ردیف در B1 باید قفل شود. نکته اینجاست که هنگام کشیدن فرمول به پایین، تنها شماره ردیف تغییر می‌کند؛ یعنی B1 می‌تواند به B2، B3 و B4 تبدیل شود. حرف B تغییر نمی‌کند، چون هنوز در همان ستون هستیم. بنابراین فقط عدد را قفل می‌کنم و قبل از آن علامت دلار می‌گذارم؛ یعنی B$1. چون هنگام حرکت رو به پایین، ستون B خودبه‌خود ثابت می‌ماند و فقط عدد ممکن است به ۲، ۳، و... تغییر کند. پس فقط ردیف را قفل می‌کنم. حالا حرکت افقی را بررسی می‌کنیم. اگر فرمول را به سمت راست بکشم، می‌خواهم B1 به C1، D1 و... تبدیل شود. این بخش درست است، چون ستون B را آزاد گذاشته‌ام. عدد نیز ثابت می‌ماند، چون در همان ردیف حرکت می‌کنیم. اما در آدرس A2 باید ستون A ثابت بماند؛ زیرا با حرکت به سمت راست، نمی‌خواهم A2 به B2 یا C2 تبدیل شود. در حرکت افقی، شماره ردیف خودبه‌خود تغییر نمی‌کند؛ بنابراین فقط ستون A را قفل می‌کنم تا مرجع اعداد عمودی جدول ثابت بماند. در نتیجه آدرس A2 را به $A2 تبدیل می‌کنم. پس هنگام کشیدن افقی، ستون A قفل است و هنگام کشیدن عمودی، ردیف در B$1 قفل است. فرمول نهایی می‌شود =$A2*B$1. حالا Enter را می‌زنم. با نوشتن همین یک فرمول، می‌توانم آن را به هر دو جهت بکشم و کل جدول ضرب به‌درستی تکمیل می‌شود. همچنین می‌توانم فرمول را کپی کنم، تمام سلول‌های جدول را انتخاب کنم و Ctrl+V را بزنم. یعنی با یک فرمول توانستم جدول ضرب را ایجاد کنم. این هنر استفاده از Reference و فرمول‌نویسی در اکسل است. نوشتن تعداد زیادی فرمول و سخت‌تر کردن کار، هدف اکسل نیست. هدف این است که از نوع مناسب آدرس‌دهی، یعنی نسبی، مطلق و مختلط، استفاده کنید تا به بهترین نتیجه برسید. در پایان، چند نوع آدرس‌دهی دیگر را هم در این جدول آورده‌ام که می‌توانید مشاهده کنید. این مدل‌ها در فرمول‌های خاص کاربرد دارند و زمانی که در ادامه دوره به آن فرمول‌ها برسیم، در جای مناسب توضیحشان می‌دهم و نحوه استفاده از این آدرس‌ها را به شما آموزش خواهم داد. موارد آخر را من «آدرس‌دهی خیلی مطلق» نام‌گذاری کرده‌ام که در بعضی کارها بسیار جذاب و کاربردی است. امیدوارم این آموزش برایتان مفید بوده باشد. حتماً پلی‌لیست آموزش فرمول‌نویسی در اکسل را دنبال کنید تا تمام بخش‌ها را به‌ترتیب به شما آموزش بدهم.