پیوندها در اکسل - مطلق، نسبی و ترکیبی. خطاها هنگام کار با پیوندهای نسبی در اکسل

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

1. پیوندهای نسبی برای محاسبات ساده استفاده می شود. کپی کردن یک فرمول مستلزم تغییر مختصات است.
2. لینک های مطلق اگر نیاز به تولید محاسبات پیچیده تری دارید، این گزینه مناسب است. برای تثبیت از نماد "$" استفاده کنید. مثال: $A$1.
3. لینک های مختلط از این نوع آدرس دهی در محاسبات زمانی استفاده می شود که لازم باشد یک ستون یا خط به طور جداگانه ثابت شود. مثال: $A1 یا A$1.

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

مرجع سلولی نسبی در اکسل

این مجموعه ای از کاراکترها است که مکان یک سلول را مشخص می کند. لینک ها در برنامه به طور خودکار با آدرس دهی نسبی نوشته می شوند. به عنوان مثال: A1، A2، B1، B2. حرکت به یک سطر یا ستون دیگر کاراکترهای فرمول را تغییر می دهد. به عنوان مثال، موقعیت شروع A1. حرکت به صورت افقی حرف را به B1، C1، D1 و غیره تغییر می دهد. به همین ترتیب، تغییرات در هنگام حرکت در امتداد یک خط عمودی رخ می دهد، فقط در این حالت عدد تغییر می کند - A2، A3، A4 و غیره. در صورت نیاز به تکرار یک محاسبه از همان نوع در یک سلول مجاور، یک محاسبه با استفاده از یک مرجع نسبی انجام می شود. برای استفاده از این ویژگی، چند مرحله را دنبال کنید:

1. به محض ورود داده ها به سلول، مکان نما را حرکت داده و با ماوس کلیک کنید. برجسته کردن با یک مستطیل سبز نشان دهنده فعال شدن سلول و آمادگی برای کار بیشتر است.
2. با فشردن کلیدهای ترکیبی Ctrl + C مطالب را در کلیپ بورد کپی می کنیم.
3. سلولی را که می خواهید داده ها یا فرمول نوشته شده قبلی را به آن منتقل کنید، فعال می کنیم.
4. با فشار دادن ترکیب Ctrl + V داده های ذخیره شده در کلیپ بورد سیستم را منتقل می کنیم.

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

مثال پیوند نسبی

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

ترتیب اقدامات:

1. مثال نشان می دهد که از ستون های B و C برای پر کردن مقدار کالای فروخته شده و قیمت آن استفاده شده است. بر این اساس، برای نوشتن فرمول و دریافت پاسخ، ستون D را انتخاب کنید. فرمول به شکل زیر است: = B2 *C

توجه کنید! برای تسهیل فرآیند نوشتن فرمول، از یک ترفند کوچک استفاده کنید. علامت "=" را قرار دهید، روی مقدار کالای فروخته شده کلیک کنید، علامت "*" را تنظیم کنید و روی قیمت محصول کلیک کنید. فرمول بعد از علامت مساوی به طور خودکار نوشته می شود.

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

با کلیک بر روی سلول D3 می بینید که مختصات سلول ها به طور خودکار تغییر کرده اند و اکنون به شکل زیر در می آیند: =B3 *C3 نتیجه این است که پیوندها نسبی بوده اند.

خطاهای احتمالی هنگام کار با پیوندهای نسبی

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

1. یک جدول ایجاد کنید و آن را پر کنید: A – نام محصول. ب - مقدار فروخته شده؛ ج - هزینه D مبلغ دریافتی است. فرض کنید فقط 11 مورد در مجموعه وجود دارد. بنابراین با احتساب توضیحات ستون ها 12 سطر پر شده و مجموع سود D می باشد.
2. روی سلول E2 کلیک کرده و وارد شوید =D2/D13.
3. پس از فشردن دکمه “Enter”، ضریب سهم نسبی فروش اولین کالا نمایان می شود.
4. ستون را به سمت پایین بکشید و منتظر نتیجه باشید. با این حال، سیستم خطای #DIV/0 را می دهد.

دلیل خطا استفاده از مرجع نسبی برای محاسبات است. در نتیجه کپی کردن فرمول، مختصات تغییر می کند. یعنی برای E3 فرمول به این صورت خواهد بود =D3/D13. از آنجایی که سلول D13 پر نیست و از نظر تئوری مقدار صفر دارد، برنامه با این اطلاعات خطا می دهد که تقسیم بر صفر غیرممکن است.

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

چگونه می توان یک لینک مطلق در اکسل ایجاد کرد؟

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

1. روی E2 کلیک کنید و مختصات لینک را وارد کنید =D2/D13. از آنجایی که پیوند نسبی است، باید یک نماد برای اصلاح داده ها تنظیم شود.
2. مشخص کردن مختصات سلول D برای انجام این عمل، با قرار دادن علامت "$"، قبل از حرفی که ستون و شماره ردیف را نشان می دهد، قرار دهید.

مشاوره تخصصی! برای سهولت در ورود، کافی است سلول ویرایش فرمول را فعال کنید و کلید F4 را چندین بار فشار دهید. تا زمانی که به مقادیر رضایت بخش دست یابید. فرمول صحیح به صورت زیر است: =D2/$D$13.

3. دکمه "Enter" را فشار دهید. در نتیجه اقدامات انجام شده، نتیجه صحیح باید ظاهر شود.
4. برای کپی کردن فرمول، نشانگر را به خط پایین بکشید.

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

نحوه قرار دادن لینک ترکیبی در اکسل

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

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

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

1. برای محاسبات دقیق وارد کنید =D1/$D$3 و "Enter" را فشار دهید. برنامه پاسخ دقیق را می دهد.
2. برای انتقال فرمول به سلول‌های بعدی در ستون و دریافت نتایج دقیق، دسته را به سلول پایین بکشید.
3. در نتیجه، برنامه محاسبات صحیح را ارائه می دهد.

توجه! اگر علامت $ را در جلوی حرف قرار دهید، اکسل یک خطای "#DIV/0!" می دهد که به این معنی است که این عملیات انجام نمی شود.

آدرس دهی "فوق مطلق".

در پایان، بیایید نمونه دیگری از یک پیوند مطلق - آدرس دهی "SuperAbsolute" را بررسی کنیم. چه ویژگی ها و تفاوت هایی دارد. عدد تقریبی 30 را بردارید و آن را در سلول B2 وارد کنید. این شماره است که اصلی خواهد بود، لازم است یک سری اقدامات با آن انجام شود، به عنوان مثال، آن را به یک قدرت برسانید.

1. برای اجرای صحیح تمامی اقدامات فرمول زیر را در ستون C وارد کنید: =$B$2^$D2. در ستون D مقدار درجه ها را وارد می کنیم.

2. پس از فشار دادن دکمه "Enter" و فعال کردن فرمول، نشانگر را به سمت پایین ستون می کشیم.
3. ما نتایج صحیح را دریافت می کنیم.

نکته اصلی این است که تمام اقدامات انجام شده به یک سلول ثابت B2 اشاره دارد، بنابراین:

• کپی کردن فرمول از سلول C3 به سلول E3، F3 یا H3 نتیجه را تغییر نمی دهد. بدون تغییر باقی خواهد ماند - 900.
• اگر نیاز به درج ستون جدید داشته باشید، مختصات سلول با فرمول تغییر می کند، اما نتیجه نیز بدون تغییر باقی می ماند.

این ویژگی پیوند "SuperAbsolute" است: در صورت نیاز به حرکت، نتیجه تغییر نخواهد کرد. با این حال، شرایطی وجود دارد که داده ها از منابع شخص ثالث درج می شوند. بنابراین، ستون ها به کنار منتقل می شوند و داده ها به روش قدیمی در ستون B2 تنظیم می شوند. در این مورد چه اتفاقی می افتد؟ هنگامی که مخلوط می شود، فرمول با توجه به عمل انجام شده تغییر می کند، یعنی دیگر به B2 اشاره نمی کند، بلکه به C2 اشاره می کند. اما از آنجایی که درج در B2 انجام شده است، نتیجه نهایی اشتباه خواهد بود.

ارجاع! برای اینکه بتوانید ماکروها را از منابع شخص ثالث وارد کنید، باید تنظیمات توسعه دهنده را فعال کنید (آنها به طور پیش فرض غیرفعال هستند). برای انجام این کار، به گزینه ها بروید، تنظیمات نوار را باز کنید و در ستون سمت راست مقابل «توسعه دهنده» علامت بزنید. پس از آن، دسترسی به بسیاری از توابع که قبلا از چشم کاربر معمولی پنهان بودند باز می شود.

این سوال پیش می‌آید: آیا می‌توان فرمول سلول C2 را طوری تغییر داد که با وجود درج ستون‌های داده جدید، عدد اصلی از سلول B جمع‌آوری شود؟ برای اطمینان از اینکه تغییرات جدول بر تعیین کل در هنگام نصب داده ها از منابع شخص ثالث تأثیر نمی گذارد، باید مراحل زیر را انجام دهید:

1. به جای مختصات سلول B2، نشانگرهای زیر را وارد کنید: =INDIRECT("B2"). در نتیجه، پس از جابجایی، ترکیب فرمولاسیون به شکل زیر خواهد بود: =INDIRECT("B2")^$E2.
2. به لطف این تابع، پیوند همیشه به مربع با مختصات B2 اشاره می کند، صرف نظر از اینکه ستون ها در جدول اضافه یا حذف شوند.

باید درک کرد که سلولی که حاوی داده ای نیست همیشه مقدار "0" را نشان می دهد.

نتیجه

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