آشنایی با ثابت سازی فرمولها بوسیله ارجاعهای ثابت، مطلق و ترکیبی بهمراه مثال
در زمان فرمول نویسی اکسل، گاهاً نیاز پیدا میکنیم محتوا یک سلول را به سلولهای بسیاری ارجاع دهیم؛ مثلاً یک مقدار عددی را در تمام سلولهای یک سطر یا ستون ضرب کنیم. در این زمان دو راه حل داریم: یکی این که برای هر سلول اقدام به نوشتن یک فرمول مجزا کنیم و یا اینکه با استفاده از آموزه‌های این درس که مرتبط با موضوع ارجاعات نسبی، مطلق و ترکیبی میباشد، محتوا مورد نظر را در کمترین زمان ممکن به تمامی سلولهای هدف ارجاع دهیم. دانش‌پژوهان در این درس با یکی از مهمترین و پرکاربردترین مباحث فرمول نویسی اکسل آشنا میشوند.
فهرست مطالب آموزش
فرمول نویسی اکسل از روش مرجع نسبی یا relative reference
ما الان داخل شیت Absolute از فایل 03 – Creating Formulas and Functions هستیم. در این لیست آماده، میخواهیم در سلول F2، new salary یا حقوق جدید را برای لیزا حساب کنیم. البته نه تنها برای لیزا بلکه برای همه افرادی که در لیست ما هستند میخواهیم این کار را انجام بدهیم. در حال حاضر، اگر همه افراد بیشتر از 2000 دلار بگیرند، ما میتوانیم اینجا یک فرمول ساده بنویسیم. خب؛ من علامت مساوی را تایپ میکنم، بعد یا می‌آیم، E2 را تایپ میکنم یا اینکه روی سلول E2 کلیک میکنم، بعد از آن به علاوه 2000 را مینویسم. حالا میتوانم آنرا در سلولهای پایین کپی کنم. البته حتی قبل از اینکه دکمه اینتر صفحه کلید را بزنیم میشود از طریق گوشه پایین سمت راست سلول، کپی را انجام بدهیم.
خب؛ روی این گوشه کلیک میکنم و آنرا به سمت پایین میکشم. فرمول در همه این سلولها کپی شد. فرمول اصلی ما E2 به علاوه 2000 بود، و وقتی اینجا کپی شد به صورت اتوماتیک فرمول به، E3 به علاوه 2000 تبدیل شد، و این چیزی است که ما به آن مرجع نسبی یا relative reference میگوییم. یعنی اگر به فرمول هر سطری نگاه کنیم میبینیم که مرجع آن تغییر کرده، مثلاً سطر 2، سطر 3، سطر 4 یا این پایین سطر 13. پس میبینید که به صورت اتوماتیک تغییر میکند.
پس به همین دلیل به آن مرجع نسبی گفته میشود.
فرمول نویسی اکسل از روش مرجع مطلق یا absolute reference
اما حالا اگر ما بخواهیم حقوق را بر اساس یک درصد افزایشی مثلاً 2.1 درصد حساب کنیم، چه کاری باید انجام بدهیم؟ یعنی اینکه ما میخواهیم به کارمندان 2.1 درصد از حقوقشان را پاداش بدهیم. خب؛ میخواهم فرمول اینجا را تغییر بدهم. بنابراین در سلول F2 دوباره شروع میکنم به نوشتن فرمول. علامت مساوی را میگذارم، روی سلول E2 کلیک میکنم، حالا علامت ستاره را از صفحه کلید تایپ میکنم، علامت ستاره برای عمل ضرب استفاده میشود، حالا روی سلول H2 کلیک میکنم. الان اگر روی دکمه اینتر از صفحه کلید بزنم، فقط مبلغ افزایشی را بدست میاورم. شبیه به این.
ولی ما میخواهیم این مقدار اضافی، با حقوق جمع شود و در نهایت به حقوق کل ماه آنها بر اساس این درصد افزایش برسیم. پس در سلول یک به علاوه E2 هم میگذارم. البته در حال حاضر روشهای مختلفی برای نوشتن این فرمول وجود دارد. بعضی از افراد می‌آیند این قسمت را داخل پرانتز میگذارند. ما هم قطعاً میتوانیم با این روش یا یک روش دیگری این کار را انجام بدهیم. اما نکته مهم این است که الان با روشی که خود ما استفاده کردیم جواب درست را بدست آوردیم. پس مقدار new salary برای لیزا 55.965 دلار شد. اگر ما همین را به سمت پایین درگ یا در واقع کپی کنیم؛ متوجه میشویم که فقط برای لیزا حقوق افزایش پیدا کرده است.
و بقیه سلولهای پایینی تغییری نکردند. به نظر شما الان، فرمول سلول F3 چه میتواند باشد؟ خب؛ ارجاع به E2 درست تغییر پیدا کرده ولی H1 هم تغییر کرده و اینجا H2 شده است. حالا حدس بزنید اینجا چی شده، به H7 ارجاع داده شده و این پایینتر. خب؛ این به H10 رجوع میکند و بقیه هم به همین صورت تغییر کردند. خب؛ یک راه‌حل ضعیف، این است که بیایم همه این سلولها را با مقدار2.1 پر کنیم. ولی تصور کنید لیست ما شامل صدها یا هزاران و یا دهها هزار نفر باشد. پس نتیجه میگیریم این بهترین روش برای انجام کار نیست.
بنابراین در این وضعیت ما نمیخواهیم اینجا به H1 به عنوان یک مرجع نسبی اشاره کند و میخواهیم H1 به صورت مطلق در نظر گرفته شود. به عبارت دقیقتر ما نمیخواهیم این مکان تغییر کند. بنابراین مرجع را با اضافه کردن علامت $ به صورت مطلق در میآورم. و این نه به صورت حسی یا بصری است و نه به صورت منطقی. ما حتی نمیتوانیم بگوییم که غیر منطقی. اما اگر بیایم یک علامت $ قبل از H و یکی قبل از 1 بگذاریم، به یک مرجع مطلق تبدیل میشود. الان ممکن است شما اشتباهاً بگویید ” من این را فهمیدم”.
علامت $ مربوط به حقوق است. آن چیزی است که من الان اینجا دارم میبینم. الان این علامت اینجا هیچ ربطی به حقوق ندارد. علامت $ را اینجا به خاطر این اضافه میکنیم که میخواهیم وقتی فرمول را کپی کردیم، این مرجع تغییری نکند. از این به بعد این مرجع یک آدرس مطلق دارد. الان به جای تایپ کردن علامت $، کاری که شما میتوانید انجام بدهید، این است که با مکان نما ماوس، در فرمول، روی H1 قرار بگیرید، مهم نیست که H1 به طور کامل انتخاب شود، میتوانید قبل یا بعد از H، یا 1، یا مابین آنها قرار بگیرید، حالا از صفحه کلید روی دکمه F4 بزنید.
خب؛ هیچ فرقی نمیکند، وقتی شما دکمه F4 را کلیک کنید، خود اکسل می‌آید علامتهای $ را برای شما تایپ میکند. دوباره تأکید میکنم این یک چیز دلخواه یا برای قشنگی نیست. در هر روشی یک چیز حسی یا بصری نیست. اینکار یعنی اضافه کردن علامتهای $ به شما این اطمینان را میدهد که اگر این فرمول را کپی کنید، دیگر این قسمت تغییر نمیکند. بنابراین میخواهم از طریق گوشه پایین سمت راست سلول، فرمول را به سلولهای پایینی کپی کنم. اگر یادتان باشد وقتی از صفحه کلید دکمه اینتر را میزدیم سلول پایینی به عنوان سلول فعال انتخاب میشد، حالا اگر به جای دکمه اینتر دکمههای کنترل و اینتر را فشار بدهم، همین سلولی که داخل آن هستم، به عنوان سلول فعال انتخاب میشود. پس با این گوشه فرمول را به پایین درگ میکنم. حقوق برای همه بدست آمد.
بیاید هر کدام از اینها را ببینیم. اینجا دابل کلیک میکنم. داریم میبینیم چه اتفاقی افتاده، همه آنها به سلول H1 رجوع میکنند. الان اگر ما از اول بخواهیم این فرمول را بنویسیم، میدانیم باید بنویسیم مساوی E2 ستاره H1. حالا در این نقطه قرار میگیریم و به راحتی روی دکمه F4صفحه کلید فشار میدهیم. حالا علامت به علاوه را مینویسیم و روی E2 کلیک میکنیم. بنابراین شما بیشتر و بیشتر نیاز به این را احساس میکنید. هر کسی که با اکسل کار میکند به این ویژگی با هر عمقی، نیاز پیدا میکند.
پس این یک مرجع مطلق نامیده میشود.
فرمول نویسی اکسل از روش مرجع ترکیبی یا mixed reference
بعضی مواقع فراتر از مباحث این دوره اتفاق میافتد. ممکن است شما نیاز داشته باشید که اطمینان پیدا کنید که در آدرس سلول شما فقط سطر به صورت مطلق در بیاید؛ پس میتوانید علامت $ را فقط قبل از سطر بگذارید یا اینکه فقط ستون مرجع مطلق باشد که اینبار باید بیاید علامت $ را قبل از ستون بگذارید، مثل اینجا.
در این فرمول ما فقط احتیاج داریم یک علامت $ جلو ردیف بگذاریم به خاطر اینکه لازم نیست ما در ستونهای مختلف جا به جا شویم یا در واقع نمیخواهیم فرمول را در ستونهای مختلف کپی کنیم، پس به گذاشتن علامت $ قبل از H نیازی نیست، اما دیدید که خیلی راحت با زدن دکمه F4 صفحه کلید این کار را انجام دادیم.
در این حالت هیچ مشکلی پیش نمی‌آید و به خوبی فرمول کار میکند. این یک مرجع مطلق است، و داشتن اطلاعات در مورد آن برای کسی که با اکسل کار میکند کاملاً ضروری است.