همانطور که می دانید در جداول اکسل دو نوع آدرس دهی وجود دارد: نسبی و مطلق. در حالت اول ، پیوند در جهت کپی کردن با مقدار تغییر نسبی تغییر می کند و در حالت دوم ثابت است و در حین کپی کردن بدون تغییر باقی می ماند. اما به طور پیش فرض ، تمام آدرس های موجود در اکسل مطلق هستند. در عین حال ، اغلب اوقات نیاز به استفاده از آدرس دهی مطلق (ثابت) وجود دارد. بیایید دریابیم که از چه طریق می توان این کار را انجام داد.
با استفاده از آدرس دهی مطلق
ما ممکن است نیاز به آدرس دهی مطلق داشته باشیم ، به عنوان مثال ، در مواردی که یک فرمول را کپی می کنیم ، که یک قسمت از آن شامل یک متغیر است که در یک سری اعداد نمایش داده می شود ، و بخش دوم یک مقدار ثابت دارد. یعنی این عدد نقش یک ضریب ثابت را بازی می کند ، که با استفاده از آن شما نیاز به انجام یک عمل خاص (ضرب ، تقسیم و غیره) برای کل سری اعداد متغیر دارید.
در اکسل دو راه برای تنظیم آدرس دهی ثابت وجود دارد: با ایجاد یک لینک مطلق و استفاده از عملکرد INDIRECT. بیایید به تفصیل به هر یک از این روش ها بپردازیم.
روش 1: پیوند مطلق
تا کنون ، معروف ترین و اغلب استفاده از راه برای ایجاد آدرس دهی مطلق استفاده از لینک های مطلق است. پیوندهای مطلق نه تنها عملکردی ، بلکه ترکیبی نیز دارند. آدرس نسبی دارای نحوی زیر است:
= A1
در یک آدرس ثابت ، یک علامت دلار در مقابل مقدار مختصات قرار دارد:
= $ 1 دلار
علامت دلار را می توان به صورت دستی وارد کرد. برای انجام این کار ، مکان نما را در مقابل مقدار اول مختصات آدرس (به صورت افقی) واقع در سلول یا در نوار فرمول قرار دهید. بعد ، در طرح بندی صفحه کلید انگلیسی زبان ، روی دکمه کلیک کنید "4" حروف بزرگ (با کلید نگه داشته شده پایین تغییر) این جایی است که نماد دلار در آن واقع شده است. سپس شما باید همین روش را با مختصات عمودی انجام دهید.
یک راه سریعتر وجود دارد لازم است مکان نما را در سلولی که آدرس در آن قرار دارد قرار دهید و بر روی کلید عملکرد F4 کلیک کنید. پس از آن ، علامت دلار فوراً همزمان در مقابل مختصات افقی و عمودی آدرس داده شده ظاهر می شود.
حال بیایید ببینیم که چگونه آدرس دهی مطلق در عمل با استفاده از پیوندهای مطلق اعمال می شود.
جداولی را بگیرید که دستمزد کارگران را محاسبه می کند. این محاسبه با ضرب حقوق شخصی آنها با ضریب ثابت انجام می شود که برای همه کارکنان یکسان است. ضریب خودش در یک سلول جداگانه از ورق قرار دارد. ما با محاسبه دستمزد همه کارگران در اسرع وقت مواجه هستیم.
- بنابراین ، در سلول اول ستون "حقوق" ما فرمول ضرب نرخ کارمند مربوطه را با ضریب معرفی می کنیم. در مورد ما ، این فرمول فرم زیر را دارد:
= C4 * G3 - برای محاسبه نتیجه نهایی ، روی دکمه کلیک کنید وارد شوید روی صفحه کلید کل در سلول حاوی فرمول نمایش داده می شود.
- ما ارزش حقوق و دستمزد را برای اولین کارمند محاسبه کردیم. حال باید این کار را برای همه خطوط دیگر انجام دهیم. البته می توان یک عملكرد را برای هر سلول در یك ستون نوشت. "حقوق" دستی با وارد کردن یک فرمول مشابه با تصحیح افست ، اما ما وظیفه داریم محاسبات را در اسرع وقت انجام دهیم و ورودی دستی زمان زیادی را می طلبد. بله ، و چرا تلاش برای ورودی دستی ، اگر این فرمول به سادگی در سایر سلول ها کپی شود ، هدر می دهید؟
برای کپی کردن فرمول ، از ابزاری مانند نشانگر پر کردن استفاده کنید. ما در گوشه سمت راست پایین سلول که در آن قرار دارد ، مکان نما می شویم. در همان زمان ، خود مکان نما باید به شکل یک صلیب به همین نشانگر پر شود. دکمه سمت چپ ماوس را نگه دارید و مکان نما را به انتهای جدول بکشید.
- اما ، همانطور که می بینیم ، به جای محاسبه درست حقوق برای بقیه کارمندان ، یک صفر گرفتیم.
- ما دلیل این نتیجه را بررسی می کنیم. برای این کار سلول دوم در ستون را انتخاب کنید "حقوق". نوار فرمول عبارتی متناسب با این سلول را نشان می دهد. همانطور که می بینید اولین عامل (C5) با نرخ کارمندی که حقوق ما انتظار داریم مطابقت دارد. تغییر مختصات نسبت به سلول قبلی به دلیل خاصیت نسبیت بود. با این حال ، در این مورد خاص ما به این نیاز داریم. با تشکر از این ، اولین عامل نرخ کارمند مورد نیاز ما بود. اما تغییر مختصات با عامل دوم اتفاق افتاد. و اکنون آدرس وی به ضریب اشاره نمی کند (1,28) اما به سلول خالی در زیر.
این دقیقاً به همین دلیل است که محاسبه دستمزد کارمندان بعدی از لیست نادرست است.
- برای اصلاح اوضاع ، باید آدرس دهی فاکتور دوم را از نسبت به ثابت تغییر دهیم. برای این کار به اولین سلول ستون برگردید "حقوق"با برجسته کردن آن بعد ، ما به نوار فرمول حرکت می کنیم ، جایی که عبارت مورد نیاز ما نمایش داده می شود. فاکتور دوم را انتخاب کنید (G3) و بر روی کلید عملکرد در صفحه کلید کلیک کنید.
- همانطور که می بینید ، یک علامت دلار در نزدیکی مختصات عامل دوم ظاهر شد و این ، همانطور که به یاد می آوریم ، یک ویژگی آدرس دهی مطلق است. برای نمایش نتیجه در صفحه ، کلید را فشار دهید وارد شوید.
- حال ، مانند گذشته ، با قرار دادن مکان نما در گوشه پایین سمت راست اولین عنصر ستون ، نشانگر fill را صدا می کنیم. "حقوق". دکمه سمت چپ ماوس را نگه دارید و آن را به پایین بکشید.
- همانطور که مشاهده می کنید در این حالت محاسبه به درستی انجام شده و میزان دستمزد همه کارکنان بنگاه اقتصادی به درستی محاسبه شده است.
- نحوه تهیه فرمول را بررسی کنید. برای انجام این کار ، عنصر دوم ستون را انتخاب کنید "حقوق". ما به عبارت موجود در خط فرمول ها نگاه می کنیم. همانطور که مشاهده می کنید مختصات عامل اول (C5) ، که هنوز هم نسبی است ، در مقایسه با سلول قبلی ، یک نقطه به پایین منتقل شد. اما عامل دوم ($ 3 دلار) ، آدرسی که در آن ثابت کردیم ، بدون تغییر باقی ماند.
اکسل همچنین از آدرس دهی مختصر استفاده می کند. در این حالت یا ستون یا سطر در آدرس عنصر ثابت شده است. این امر به گونه ای حاصل می شود که نشان دلار فقط در مقابل یکی از مختصات آدرس قرار گیرد. در اینجا مثالی از پیوند مختلط معمولی آورده شده است:
= 1 دلار
این آدرس همچنین مختلط در نظر گرفته شده است:
= A1 $
یعنی آدرس دهی مطلق در یک لینک مختلط فقط برای یکی از دو مقدار مختصات استفاده می شود.
بیایید ببینیم که چگونه می توان با استفاده از همان جدول حقوق و دستمزد برای کارمندان شرکت به عنوان نمونه ، چنین پیوند مختلط را به کار برد.
- همانطور که می بینید ، قبلاً ما آن را ساخته ایم تا همه مختصات عامل دوم کاملاً مورد توجه قرار گیرد. اما بیایید ببینیم در این حالت ، هر دو مقدار باید ثابت باشند؟ همانطور که مشاهده می کنید ، هنگام کپی کردن ، یک تغییر عمودی رخ می دهد و مختصات افقی بدون تغییر باقی می مانند. بنابراین ، استفاده از آدرس دهی مطلق فقط در مختصات ردیف کاملاً امکان پذیر است و مختصات ستون را همانطور که به طور پیش فرض هستند ، رها کنید.
عنصر ستون اول را انتخاب کنید "حقوق" و در خط فرمول ها دستکاری فوق را انجام می دهیم. فرمول فرم زیر را دریافت می کنیم:
= C4 * G 3 دلارهمانطور که مشاهده می کنید آدرس دهی ثابت در عامل دوم فقط در مختصات خط اعمال می شود. برای نمایش نتیجه در سلول ، روی دکمه کلیک کنید وارد شوید.
- پس از آن ، با استفاده از نشانگر پر ، این فرمول را در محدوده سلولهایی که در پایین قرار دارد کپی کنید. همانطور که مشاهده می کنید ، حقوق و دستمزد همه کارمندان به درستی انجام شد.
- ما می بینیم که چگونه فرمول کپی شده در سلول دوم ستونی که دستکاری را روی آن انجام داده ایم نمایش داده می شود. همانطور که در خط فرمول ها مشاهده می کنید ، پس از انتخاب این عنصر ورق ، با وجود اینکه فقط مختصات خطوط آدرس دهی مطلق را در فاکتور دوم داشتند ، تغییر در مختصات ستون اتفاق نیفتاد. این به این دلیل است که ما به صورت افقی ، بلکه به صورت عمودی کپی نکردیم. اگر قرار بود به صورت افقی کپی کنیم ، برعکس ، در یک مورد مشابه ، برعکس ، باید آدرس دهی ثابت مختصات ستون ها را انجام دهیم ، و برای ردیف ها این روش اختیاری است.
درس: پیوندهای مطلق و نسبی در اکسل
روش 2: عملکرد غیرمستقیم
راه دوم برای ساماندهی آدرس دهی مطلق در صفحه گسترده اکسل ، استفاده از عملگر است هند. عملکرد مشخص به گروه اپراتورهای داخلی تعلق دارد. منابع و آرایه ها. وظیفه آن ایجاد پیوند به سلول مشخص شده با خروجی در عنصر برگه ای است که اپراتور در آن قرار دارد. در این حالت ، پیوند به حتی بیشتر از استفاده از علامت دلار به مختصات متصل می شود. بنابراین ، بعضی اوقات مرسوم است که از لینک ها استفاده کنید هند "عالی مطلق" این عبارت دارای نحوی زیر است:
= INDIRECT (cell_link؛ [a1])
این تابع دارای دو آرگومان است که اولین مورد از وضعیت اجباری برخوردار است و دومی آن را ندارد.
استدلال پیوند سلولی پیوندی به عنصر صفحه اکسل در فرم متن است. یعنی این یک پیوند معمولی است اما به صورت علامت گذاری درج می شود. این دقیقاً همان چیزی است که اطمینان از خواص آدرس دهی مطلق را ممکن می کند.
استدلال "a1" - اختیاری است و در موارد نادر مورد استفاده قرار می گیرد. استفاده از آن فقط در مواردی ضروری است که کاربر گزینه انتخاب آدرس دهی جایگزین را انتخاب کند ، بجای استفاده معمول از مختصات بر اساس نوع "A1" (ستون ها دارای حروف مشخص هستند ، و ردیف ها - دیجیتال). یک جایگزین استفاده از یک سبک است "R1C1"، که در آن ستون ها ، مانند ردیف ها ، با شماره ها نشان داده می شوند. می توانید از طریق پنجره گزینه های Excel به این حالت عملکرد تغییر دهید. سپس با اعمال اپراتور هندبه عنوان یک استدلال "a1" مقدار باید نشان داده شود جعلی. اگر در حال کار در حالت نمایش معمولی پیوندها هستید ، مانند اکثر کاربران دیگر ، پس از آن به عنوان یک استدلال "a1" می توانید یک مقدار را تعیین کنید "حقیقت". با این حال ، این مقدار به طور پیش فرض ضمنی است ، بنابراین آرگومان در این مورد به طور کلی بسیار ساده تر است. "a1" مشخص نکنید
بیایید نگاهی بیندازیم که آدرس دهی مطلق سازمان یافته با استفاده از عملکرد چگونه کار خواهد کرد. هندمثلاً جدول حقوق و دستمزد ما
- اولین عنصر ستون را انتخاب می کنیم "حقوق". ما نشانه ای می گذاریم "=". همانطور که به یاد می آوریم ، اولین عامل در فرمول محاسبه حقوق مشخص شده باید توسط یک آدرس نسبی ارائه شود. بنابراین ، فقط کافی است روی سلول حاوی مقدار حقوق مربوطه کلیک کنید (C4) به دنبال نحوه نمایش آدرس آن در این عنصر برای نمایش نتیجه ، روی دکمه کلیک کنید ضرب کنید (*) روی صفحه کلید سپس باید به سمت استفاده از اپراتور حرکت کنیم هند. روی نماد کلیک کنید "درج عملکرد".
- در پنجره ای که باز می شود جادوگران عملکرد برو به دسته منابع و آرایه ها. در بین لیست ارائه شده اسامی ، ما نام را تشخیص می دهیم "هند". سپس بر روی دکمه کلیک کنید "خوب".
- پنجره آرگومان های اپراتور فعال می شود هند. این شامل دو بخش است که با استدلال های این عملکرد مطابقت دارد.
مکان نما را در قسمت میدان قرار دهید پیوند سلولی. فقط کافی است روی عنصر برگه که ضریب محاسبه حقوق و دستمزد آن است کلیک کنید (G3) آدرس بلافاصله در قسمت پنجره Argument ظاهر می شود. اگر با یک عملکرد معمولی سر و کار داشتیم ، می توان معرفی آدرس را کاملاً در نظر گرفت ، اما ما از این عملکرد استفاده می کنیم هند. همانطور که به یاد می آوریم آدرس های موجود در آن باید به صورت متن باشند. بنابراین ، مختصاتی را که در قسمت پنجره قرار دارند با علامت نقل قول می پوشانیم.
از آنجا که ما در حالت نمایش مختصات استاندارد کار می کنیم ، فیلد "A1" خالی بگذارید. روی دکمه کلیک کنید "خوب".
- برنامه محاسبه را انجام می دهد و نتیجه را در یک عنصر ورق حاوی فرمول نمایش می دهد.
- اکنون این فرمول را در تمام سلولهای ستون دیگر کپی می کنیم "حقوق" با استفاده از نشانگر پر ، همانطور که قبلاً انجام دادیم. همانطور که مشاهده می کنید ، تمام نتایج به درستی محاسبه شده اند.
- بیایید ببینیم که چگونه فرمول در یکی از سلولهای کپی شده نمایش داده می شود. عنصر دوم ستون را انتخاب کنید و به خط فرمول ها نگاه کنید. همانطور که مشاهده می کنید ، اولین عامل ، که یک ارتباط نسبی است ، مختصات خود را تغییر داده است. در همان زمان ، استدلال عامل دوم ، که توسط عملکرد نشان داده می شود هندبدون تغییر ماند. در این حالت از تکنیک آدرس دهی ثابت استفاده شده است.
درس: IFRS اپراتور در اکسل
آدرس دهی مطلق در جداول اکسل از دو طریق قابل دستیابی است: استفاده از عملکرد INDIRECT و استفاده از لینک های مطلق. در عین حال ، این تابع اتصال سفت و سخت تری را برای آدرس فراهم می کند. آدرس دهی کاملاً مطلق نیز با استفاده از پیوندهای مختلط قابل استفاده است.
