آدرس نسبی و مطلق سلول در فرمول اکسل — راهنمای کاربردی (+ دانلود فیلم آموزش رایگان)

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

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

آدرس نسبی و مطلق سلول در فرمول اکسل

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

فیلم آموزش استفاده از توابع و فرمول نویسی در اکسل Excel در فرادرس

کلیک کنید

نکته: از این به بعد و در متن پیش‌رو، زمانی که می‌خواهیم یک فرمول را نشان دهیم از قالب خاصی برای نمایش فرمول‌ها استفاده می‌کنیم. به این منظور ابتدا نام سلول وابسته سپس علامت «:» و بعد تساوی و فرمول را قرار می‌دهیم. برای مثال وقتی که سلول A2 یک سلول وابسته باشد و بخواهیم مقدار A1 را با ۱۰ جمع کرده و نتیجه را در سلول A2 به دست آوریم، خواهیم نوشت:

$$ \large \text {A2:} \; = A1 + 10 $$

این امر به این معنی است که باید فرمولی که بعد از علامت «:» ظاهر شده است را در سلول A2 وارد کنید. حتما به یاد دارید که فرمول‌ها درون سلول با «=» آغاز می‌شوند.

• سلول مستقل: اگر سلولی شامل مقدار متنی، عددی، تاریخ و ... باشد، یک «سلول مستقل» (Independent Cell) است، زیرا مقدار آن تحت تاثیر سلول‌های دیگر کاربرگ قرار ندارد. برای مثال سلول‌ زیر مستقل است.

$$ \large \text{A1:} \; 10 $$

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

$$ \large \text{A2:} \; = 10 + 5 $$

• سلول وابسته: زمانی که مقدار یک سلول به سلول‌های دیگر مرتبط باشد، یک سلول وابسته خواهیم داشت. سلول وابسته را براساس فرمول و ذکر نام یا آدرس سلول‌های دیگر در فرمول، معرفی می‌کنیم. به عنوان مثال، سلول‌های زیر، وابسته هستند.

$$ \large \text{A2:} \; = A1 + 10 $$

$$ \large \text{A3:} \; = SUM ( A1 : A2 ) $$

همانطور که اشاره کردیم، یک «سلول وابسته» (Dependent Cell) را به کمک فرمول ایجاد می‌کنیم، بطوری که از آدرس سلول‌های دیگر کاربرگ، کمک گرفته‌ و رابطه یا فرمولی را ایجاد یا ثبت می‌کنیم. سلول‌ها که آدرس آن‌ها در فرمول به کار رفته، «سلول‌های پیش‌نیاز» (Precedent Cell) گفته می‌شوند. واضح است که با تغییر مقدار در سلول‌های پیش‌نیاز، فرمول‌ها به روز شده و بدون آنکه فرمول سلول وابسته تغییر کند، مقدار سلول آن مجدد محاسبه خواهد شد.

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

سلول‌های پیش‌نیاز و وابسته

کاربرگ (Worksheet) زیر را در نظر بگیرید. در سلول A1 و A2 دو مقدار ۱ و ۲ نوشته شده است. قرار است حاصل جمع این دو سلول در سلول B1 محاسبه شود. واضح است که فرمول باید به شکل زیر باشد.

$$ \large \text{B1:} \; = A1 + A2 $$

همانطور که می‌دانید با فشردن کلید Enter، فرمول ثبت شده، مقدار محاسبه شده در سلول B1 ظاهر می‌شود. در این حالت B1 را سلول وابسته به A1 و A2 نامیده، A1 و A2 را پیش‌نیازهای B1 می‌گوییم.

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

• سلول وابسته (در اینجا B1) را انتخاب کنید.
• از برگه Formula گزینه Trace Precedent (دنبال کردن سلول پیش‌نیاز) را از بخش Formula Auditing (ممیزی فرمول) کلیک کنید.

نتیجه اجرای این عملیات برای کاربرگ بالا به صورت زیر خواهد بود.

این بار می‌خواهیم تشخیص دهیم که یک سلول (مثل A1) پیش‌نیاز کدام سلول‌ها است. در حقیقت قرار است سلول‌های وابسته به A1 را شناسایی کنیم. برای انجام این کار مراحل زیر را طی کنید.

• سلول پیش‌نیاز (در اینجا A1) را انتخاب می‌کنید.
• از برگه Formula گزینه Trace Dependent (دنبال کردن سلول وابسته) را از قسمت Formula Auditing کلیک کنید.

به این ترتیب، به کمک فلش‌هایی، سلول‌های وابسته به سلول پیش‌نیاز مورد نظر، مشخص می‌شوند. برای کاربرگ بالا، نتیجه به صورت زیر خواهد بود.

نکته: اگر بخواهید این فلش‌ها را از صفحه نمایش خارج کنید، باید گزینه Remove Arrows (حدف فلش‌ها) را از قسمت Formula Auditing کلیک کنید. توجه داشته باشید که هنگام چاپ، این خطوط نیز توسط چاپگر، روی کاغذ ظاهر می‌شوند.

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

ارجاع به آدرس نسبی و مطلق سلول

سه نوع شیوه ارجاع به سلول در اکسل وجود دارد: نسبی (Relative)، مطلق (Absolute) و ترکیبی (Mixed). هر یک از این شیوه‌ها را می‌توانید برای مشخص کردن آدرس سلول پیش‌نیاز در یک سلول وابسته به کار برید.

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

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

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

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

اغلب آدرس‌هایی که برای فرمول‌های مربوط به سلول‌های پیش‌نیاز به کار می‌بریم، مراجع یا آدرس‌های نسبی هستند. همانطور که در تصویر زیر مشاهده می‌کنید، آدرس سلول‌های A3، A2 و A4 به شکل مرجع یا آدرس نسبی نوشته شده‌اند.

$$ \large \text{B2:} \; = A2 * 5 $$

اهمیت به کار بردن این نوع آدرس در اینجا، صرفه‌جویی در نوشتن فرمول‌های متعدد است. در تصویر بالا، مشخص است که فرمول مربوط به سلول B2، حاصل‌ضرب سلول متناسب با همین سطر از ستون A در مقدار 5 است. در حقیقت سلول B2 نتیجه ضرب A2 در 5 را نشان خواهد داد.

فیلم آموزش نمایش داده ها و ترسیم نمودار در اکسل Excel در فرادرس

کلیک کنید

برای این که همین عمل را در سلول‌های B3 و B4 نیز انجام دهیم، کافی است سلول B2 را در آن‌ها کپی کنیم. با این کار به جای آنکه مقدار یا نتیجه فرمول B2 در آن‌ها ظاهر شود، خود فرمول مربوطه کپی شده و همان عمل یعنی ضرب کردن مقدار 5 در سطر متناسب از ستون A را انجام می‌دهد. به این ترتیب مقدارهای ۵ و ۱۰ و ۱۵ در این سلول‌ها دیده می‌شود.

پس مشخص است که با کپی کردن سلول وابسته B2 به سلول B3 که شامل ۱ سطر تغییر مکان است، آدرس سلول پیش‌نیاز نیز به همین نسبت تغییر کرده و از A2 به A3‌ تبدیل شده است. به همین علت این نحوه ارجاع به سلول پیش‌نیاز را «آدرس نسبی» (Relative Reference) نامیده‌اند.

نکته: فرمول نوشته شده در سلول B2 را می‌توان به صورت زیر نیز در نظر گرفت؛ «سلول مجاور (یا سمت چپ) را در ۵ ضرب کن». با توجه به این موضوع همین رابطه نیز برای سلول‌های B3 و B4 انجام خواهد شد و به همین دلیل، فرمول B2 را در سلول‌های بعدی کپی می‌کنیم زیرا عملی که می‌خواهیم در آن‌ها صورت بگیرد با سلول B2 یکسان است.

به یاد دارید که عمل کپی کردن در اکسل طی مراحلی انجام می‌شود. برای یادآوری این عملیات را متذکر می‌شویم.

• انتخاب سلول مبدا (ناحیه) کاربرگ.
• اجرای دستور کپی به کمک کلیدهای میانبر Ctrl+C یا دستور Copy از برگه Home.
• انتخاب سلول مقصد (ناحیه) کاربرگ.
• اجرای دستور چسباندن به کمک کلیدهای میانبر Ctrl+V یا دستور Paste از برگه Home.

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

خطای !REF# در فرمول نویسی با آدرس نسبی و مطلق سلول

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

برای مثال فرض کنید سلول B2 را در سلول A5 کپی کنیم. از آنجایی که سلولی در سمت چپ سلول A5 قرار ندارد که اکسل آن را در مقدار ۵ ضرب کند، پیغام خطای !REF# در سلول A5 توسط اکسل نشان داده می‌شود که بیانگر ناموجود بودن آدرس سلول پیش‌نیاز است.

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

همانطور که می‌دانید آدرس هر سلول در اکسل شامل دو بخش است. بخش اول، نشان دهنده نام ستون (Column) مربوط به سلول و بخش دوم شماره ردیف (Row) آن را مشخص می‌کند. برای مثال آدرس اولین سلول کاربرگ در اکسل با آدرس A1 مشخص می‌شود، نشان می‌دهد این سلول مربوط به ستون A و سطر اول (ردیف ۱) کاربرگ است.

اگر قبل از نام ستون و شماره ردیف آدرس از علامت $ استفاده کنیم، این بخش‌ها از آدرس سلول‌، هنگام کپی کردن سلول وابسته مطلقاً تغییر نخواهند کرد. برای مثال آدرس سلول A1 به عنوان سلول پیش‌نیاز در فرم مرجع یا «آدرس مطلق» (Absolute Reference) در رابطه زیر دیده می‌شود.

$$ \large $ \text{A} $ 1 $$

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

داده‌های مربوط به تصویر ۱ را در نظر بگیرید. فرض کنید مقدار 5 در سلول C1 نوشته شده و قرار است هر یک از مقدارهای A2 تا A4 را در C1 ضرب کنیم و حاصل را در سلول‌های B2 تا B4 نمایش دهیم.

اگر آدرس C1 را به صورت نسبی در سلول مربوط به سلول B2 وارد کنیم، با کپی کردن آن در سلول‌های B3 و B4، آدرس C1 به C2 و C3 تبدیل خواهد شد که مشخص است هدف ما چنین چیزی نبوده است.

نکته: از آنجایی که مقداری برای سلول‌های C2 و C3 وجود ندارد، در محاسبه فرمول‌ها، صفر در نظر گرفته شده و نتیجه فرمول در سلول‌های B3 و B4 در این حالت صفر خواهد بود.

فیلم آموزش جداول محوری در اکسل یا پیوت تیبل Pivot Tables در فرادرس

کلیک کنید

راه حل، استفاده از آدرس مطلق سلول C1 است. در نتیجه فرمول مربوط به سلول B2 را به صورت زیر خواهیم نوشت:

$$ \large \text{B2 :} \; = A2 * $C$1 $$

در نتیجه اگر سلول B2 را در سلول‌های بعدی کپی کنیم، هر یک از سلول‌های مربوط به سطرهای متناظر در ستون A، فقط در مقدار سلول C1 ضرب خواهند شد.

نکته: همانطور که می‌دانید عمل کپی‌کردن یک سلول را به کمک دکمه «دستگیره پر کردن» (Fill Handle) نیز می‌توانید انجام دهید. این دکمه در انتهای سمت راست پایین سلول قرار دارد و در تصویر زیر مشخص شده است.

کافی است نشانگر ماوس را روی آن قرار داده و زمانی که به شکل + در آمد عمل «کشیدن و رها کردن» (Drag and Drop) را انجام دهید. این عمل هنگامی که فرمول‌هایی با آدرس نسبی و مطلق سلول دارید نیز موثر است.

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

در مرجع یا «آدرس ترکیبی» (Mixed Reference)، بخشی از آدرس سلول پیش‌نیاز به صورت نسبی و بخشی دیگر به صورت مطلق است. برای مثال، آدرس‌های زیر نمونه‌ای از شیوه ارجاع به سلول A1 به صورت ترکیبی هستند.

$$ \large $ \text{A}1  $$

که در آن ستون مطلق (ثابت) و سطر نسبی (تغییر پذیر) است. همچنین مرجع یا آدرس

$$ \large \text{ A} $1  $$

که در آن ردیف ۱ ثابت ولی ستون A نسبی و قابل تغییر است.

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

برای تشکیل جدول ضرب از اعداد ۱ تا ۵ در اکسل، کاربرگ زیر را تهیه کرده‌ایم. مشخص است که در سلول B2 باید حاصل‌ضرب سلول A2 را در B1 بدست آوریم. همچنین در سلول C3 نیز باید حاصل‌ضرب A3 را در C1 محاسبه کنیم.

در این دو فرمول، کدام اجزای آدرس سلول‌های پیش‌نیاز تغییر نیافته است؟ واضح است که همیشه در آدرس سلول اول در ضرب، ستون A در قسمت اول فرمول ثابت است ولی ردیف‌ها تغییر می‌کنند. در آدرس سلول دوم عمل ضرب هم همیشه ردیف ۱ ثابت بوده ولی ستون‌ها تغییر کرده‌اند.

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

در نتیجه اگر سلول B2 را در سلول‌های دیگر کپی کنیم، جدول ضرب تکمیل خواهد شد.

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

نکته: زمانی که می‌خواهید نوع مرجع سلول پیش‌نیاز را تعیین کنید، پس از انتخاب سلول پیش‌نیاز، کلید $$ \fbox{F4} $$ را از صفحه کلید بزنید.

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

مثال ۱

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

در بخش اول فرمول آدرس سلول A2 که کل بودجه را مشخص می‌کند به صورت مطلق ثبت شده تا هنگام کپی کردن سلول B2، تغییر نکند ولی آدرس سلول B1 در این فرمول به شکل ترکیبی نوشته شده تا هنگام کپی کردن، فقط ستون‌ آن تغییر کرده و ردیف آن ثابت باقی بماند.

مثال ۲

همچنین در نظر بگیرید که می‌خواهیم برای هر یک از افراد و کارکنان شرکت، میزان بیمه و مالیات را با توجه به درصدی که در نظر گرفته‌ایم، بدست بیاوریم. حقوق افراد در ستون A نوشته شده و درصد مالیات و بیمه نیز در ستون‌های B و C در ردیف اول دیده می‌شود. الگوی جدول ضرب به ما کمک می‌کند که با یک فرمول، محاسبات همه کارکنان را با سرعت انجام دهیم. به تصویر 6 دقت کنید. نحوه استفاده از مرجع یا آدرس ترکیبی در اینجا کاملا با جدول ضرب مطابقت دارد.

همانطور که مشخص است، مرجع یا آدرس سلول A3 به صورت ترکیبی نوع دوم (یعنی به فرم A3‎‎$) و سلول B2 به صورت ترکیبی نوع اول (یعنی B$2) است.

خلاصه و جمع‌بندی

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

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