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

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

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

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

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

فیلم آموزش استفاده از توابع و فرمول نویسی در اکسل 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 را نشان خواهد داد.

فیلم آموزش رسم نمودار در اکسل – طراحی انواع نمودار و تنظیمات در فرادرس

کلیک کنید