آشنایی با کامل‌ترین روش‌های فرمول نویسی نسبی و مطلق در اکسل

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

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

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

 

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

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

به طور پیش فرض، همه مراجع سلولی منابع نسبی هستند. هنگام کپی کردن چندین سلول، آن‌ها بر اساس موقعیت نسبی ردیف‌ها و ستون‌ها تغییر می‌کنند. به عنوان مثال، اگر فرمول = A1 + B1 را از ردیف ۱ به سطر ۲ کپی کنید، فرمول به = A2 + B2 تبدیل خواهد شد. ارجاعات نسبی در زمانی که نیاز به تکرار همان محاسبه در چندین سطر یا ستون باشد، فرمول نویسی‌های بسیار مناسبی هستند.

ایجاد و کپی یک فرمول با استفاده از فرمول نویسی نسبی:

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

• گام اول فرمول نویسی نسبی و مطلق در اکسل:

سلولی را که شامل فرمول است انتخاب کنید. در مثال ما سلول D2 را انتخاب می‌کنیم.

• گام دوم :

فرمول را برای محاسبه مقدار دلخواه وارد کنید. در مثال ما  = B2 * C2 را تایپ می‌کنیم.

• گام سوم فرمول نویسی نسبی و مطلق در اکسل:

Enter را روی صفحه کلید خود فشار دهید. فرمول محاسبه می‌شود و نتیجه در سلول نمایش داده می‌شود.

• گام چهارم فرمول نویسی نسبی و مطلق در اکسل:

 the fill handleرا در گوشه سمت راست پایین سلول مورد نظر قرار دهید. در مثال ما دسته پر کننده سلول D2 را پیدا می‌کنیم.

• گام پنجم :

کلیک کنید، نگه دارید و دسته پر را بر روی سلول‌های مورد نظر بکشید. در مثال ما سلول‌های D3: D12 را انتخاب می‌کنیم.

• گام ششم فرمول نویسی نسبی و مطلق در اکسل:

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

• گام هفتم فرمول نویسی نسبی و مطلق در اکسل:

می‌توانید سلول‌های پر شده را دوبار کلیک کنید تا فرمول‌های آن‌ها را برای صحت بررسی کنید. تعداد سلول‌های نسبی بستگی به ردیف آن برای هر سلول متفاوت است.

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

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

 

 

یک مرجع مطلق در یک فرمول با اضافه کردن علامت دلار ($) قبل از ستون و سطر تعیین می‌شود. اگر قبل از ستون یا ردیف(اما نه هر دو) باشد، به عنوان یک مرجع مختلط شناخته می‌شود.

$A$2	هم سطر و هم ستون هنگام کپی کردن تغییر نمی کنند.
A$2	ردیف هنگام کپی کردن تغییر نمی کند.
$A2	ستون هنگام کپی کردن تغییر نمی کند.

***در اکثر فرمول‌ها از قالب‌های نسبی (A2) و مطلق ($A$2) استفاده می‌شود. آدرس‌دهی مختلط کمتر مورد استفاده قرار می‌گیرند.

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

ایجاد و کپی فرمول با استفاده از آدرس دهی مطلق در اکسل:

در مثال ما از نرخ مالیات فروش ۷٫۵٪ در سلول E1 برای محاسبه مالیات فروش برای همه موارد موجود در ستون D. استفاده خواهیم کرد. برای این کار باید در فرمول خود از مرجع مطلق سلول E $ 1 $ استفاده کنیم.

 

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

• گام اول فرمول‌نویسی نسبی و مطلق در اکسل:

سلولی را که شامل فرمول است انتخاب کنید. در مثال ما سلول D3 را انتخاب می‌کنیم.

• گام دوم فرمول‌نویسی نسبی و مطلق در اکسل:

فرمول را برای محاسبه مقدار دلخواه وارد کنید. در مثال ما =(B3*C3)*$E$1 را تایپ می‌کنیم.

• گام سوم فرمول نویسی نسبی و مطلق در اکسل:

 Enter را روی صفحه کلید خود فشار دهید. فرمول محاسبه می‌شود و نتیجه در سلول نمایش داده می‌شود.

• گام چهارم فرمول نویسی نسبی و مطلق در اکسل:

دسته‌پر را در گوشه سمت راست پایین سلول مورد نظر قرار دهید. در مثال دسته‌پر کننده سلول D3 را پیدا می‌کنیم.

• گام پنجم فرمول نویسی نسبی و مطلق در اکسل:

دسته را نگه دارید و دسته‌پر را بر روی سلول‌های مورد نظر بکشید. در مثال مورد نظر سلول‌های D4:D13 است.

• گام ششم فرمول نویسی نسبی و مطلق در اکسل:

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

• گام هفتم فرمول نویسی نسبی و مطلق در اکسل:

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

ثابت نگه داشتن محتوای یک سلول در اکسل:

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

اصل سوم  فرمول نویسی نسبی و مطلق در اکسل: استفاده از منابع سلول با چند کاربرگ

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

توجه داشته باشید که اگر یک صفحه کاربر حاوی یک فضا باشد، لازم است علامت‌های نقل قول واحدی (”) را در اطراف نام درج کنید. به عنوان مثال، اگر می خواهید سلول A1 را روی یک برگه به نام بودجه ژوئیه مرجع کنید، مرجع سلول  ‘July Budget’!A1خواهد بود.

 

 

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

• مرحله اول :

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

• مرحله دوم :

حرکت به سمت کاربرگ مورد نظر. در مثال ما کاربرگ Catering Invoice را انتخاب می‌کنیم.

 

• مرحله سوم :

کاربرگ انتخاب شده ظاهر می‌شود.

• مرحله چهارم:

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

• مرحله پنجم:

علامت برابر (=)، نام برگه و به دنبال آن یک تعجب (!) و آدرس سلول را تایپ کنید. در مثال =’Menu Order’!E14 را تایپ می‌کنیم!

• مرحله ششم:

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

***اگر برگه کار خود را بعدها تغییر نام دهید، مرجع سلول به صورت خودکار به روز می‌شود تا نام کاربرگ جدید را منعکس کند.