آموزش فرمول نویسی در اکسل / بخش سوم
آموزش فرمول نویسی در اکسل
آموزش فرمول نویسی در اکسل : در بخش قبلی با فرمول نویسی پیچیده آشنا شدید. در این بخش با مرجع سلولی نسبی و مطلق آشنا خواهید شد.
دو نوع مرجع سلولی وجود دارد: نسبی و مطلق. مراجع نسبی و مطلق هنگام کپی کردن به سلول های دیگر رفتار متفاوت دارند. ارجاعات نسبی هنگامی که یک فرمول به سلول دیگری کپی می شود، تغییر می کند. از سوی دیگر، ارجاعات مطلق، بدون توجه به جایی که کپی شده اند، ثابت باقی می ماند.
فرمول نویسی اکسل ، فرمول در اکسل
آموزش فرمول نویسی در اکسل ، مراجع نسبی :
به طور پیش فرض، تمام منابع سلولی، مرجع نسبی هستند. هنگامی که در سلول های متعدد کپی می شوند، براساس موقعیت نسبی ردیف ها و ستون ها تغییر می کنند. به عنوان مثال، اگر فرمول ” A1 + B1 = ” را از ردیف ۱ به ردیف ۲ کپی کنید، فرمول ” A2 + B2 = ” خواهد شد. مراجع نسبی زمانی که نیاز به تکرار محاسبات در چند ردیف یا ستون دارید، بسیار مناسب هستند.
ایجاد و کپی فرمول در اکسل با استفاده از مراجع نسبی:
در مثال زیر می خواهیم یک فرمول ایجاد کنیم که قیمت هر محصول را در تعداد ضرب کند. به جای ایجاد مجدد فرمول در اکسل برای هر ردیف، ما می توانیم یک فرمول واحد را در سلول D2 ایجاد کنیم و سپس آن را در سطر های دیگر کپی کنیم. ما از ارجاع نسبی استفاده می کنیم، بنابراین فرمول به درستی هر مورد را محاسبه می کند.
سلولی را انتخاب کنید که حاوی فرمول باشد. در این مثال، سلول D2 را انتخاب می کنیم.
فرمول را برای محاسبه ارزش مورد نظر وارد کنید در مثال ” B2 * C2 = ” را تایپ می کنیم.
روی صفحه کلید خود Enter را فشار دهید. فرمول محاسبه و نتیجه در سلول نمایش داده خواهد شد.
مربع تو پر را در قسمت پایین سمت راست سلول مورد نظر، بیابید.
روی مربع کلیک کنید، نگه دارید و روی سلولهای مجاور بکشید. در مثال، سلولهای D3: D12 را انتخاب می کنیم.
ماوس را رها کنید تا فرمول در سلول های انتخاب شده با مرجع نسبی کپی شود و مقادیر در هر سلول محاسبه گردد.
شما می توانید سلول های پر شده را دابل کلیک کنید تا فرمول های آنها را با دقت بررسی کنید. مراجع سلولی نسبی برای هر سلول، بسته به ردیف آن، باید متفاوت باشند.
گزینه اول ، پاسخ سوال فوق می باشد.
مرجع مطلق برای فرمول در اکسل :
گاهی ممکن است شما نخواهید مرجع سلولی با تغییر سلول ها تغییر کند. بر خلاف مرجع نسبی، مراجع مطلق هنگام کپی شدن تغییر نمی کنند. شما می توانید از مرجع مطلق برای حفظ یک ردیف و یا ستون ثابت استفاده کنید.
مرجع مطلق در یک فرمول با اضافه کردن علامت دلار ($) تعیین می شود. این علامت می تواند پیش از مرجع ستون، ردیف و یا هر دو باشد.
معمولا از فرمول هایی مانند ۲$A$ هنگام ایجاد فرمول هایی که دارای مرجع های مطلق هستند، استفاده می شود. دو فرمت دیگر کمتر استفاده می شوند.
هنگام فرمول نویسی اکسل ، می توانید کلید F4 روی صفحه کلید خود را برای تغییر بین مرجع نسبی و مطلق فشار دهید. این یک راه آسان برای سریع نوشتن مرجع مطلق است.
ایجاد و کپی فرمول با استفاده از منابع مطلق :
در مثال، از نرخ ۷.۵٪ مالیات فروش در سلول E1 برای محاسبه مالیات فروش برای همه اقلام ستون D استفاده خواهیم کرد. ما باید از مرجع مطلق ۱$E$ در فرمول استفاده کنیم. چرا که هر فرمول از همان نرخ مالیاتی استفاده می کند، ما می خواهیم که مرجع ثابت باشد تا فرمول کپی شده در سلول های دیگر ستون D صحیح محاسبه شود.
سلولی را انتخاب کنید که حاوی فرمول باشد. در مثال ما سلول D3 را انتخاب می کنیم.
فرمول را برای محاسبه ارزش مورد نظر وارد کنید. در مثال ” B3 * C3) * $E$1) = ” را تایپ می کنیم.
روی صفحه کلید خود Enter را فشار دهید. فرمول محاسبه و نتیجه در سلول نمایش داده خواهد شد.
در قسمت پایین سمت راست سلول مورد نظر، مربع تو پر را نمایان کنید. در مثال ، مربع را برای سلول D3 قرار می دهیم.
کلیک کنید، نگه دارید و روی سلول های مجاور بکشید. در مثال، فرمول را در سلول های D4: D13 کپی می کنیم.
ماوس را رها کنید. فرمول با مرجع مطلق در سلول های انتخاب شده کپی می شود و مقادیر در هر سلول محاسبه می گردد.
شما می توانید روی سلول ها دو بار کلیک کنید تا فرمول های آنها را به دقت بررسی کنید. مرجع مطلق باید برای همه سلول ها یکسان باشد، در حالی که سایر مراجع مربوط به ردیف سلول هستند.
مطمئن شوید که علامت دلار ($) را هنگامی که مرجع مطلق ایجاد می کنید، قرار داده اید. علائم دلار در مثال زیر حذف شده است. این امر موجب شده است که صفحه گسترده، فرمول را به عنوان یک مرجع نسبی تفسیر کند و نتیجه نادرستی را هنگام کپی به سایر سلول ها ایجاد می کند.
آموزش فرمول نویسی در اکسل ، استفاده از مراجع سلولی با چندین برگه :
اکثر برنامه های صفحه گسترده به شما اجازه می دهند به هر سلولی در برگه های مختلف ارجاع دهید. اگر مایل به ارجاع یک مقدار خاص از یک برگه به برگه دیگری باشید، این ویژگی می تواند مفید باشد. فرمول نویسی اکسل نیز این ویژگی را دارا می باشد.
برای این کار، باید در مرجع سلولی، نام برگه را بنویسید و بعد از آن علامت تعجب (!) قرار دهید. به عنوان مثال، اگر شما می خواهید سلول A1 را در Sheet1 ارجاع دهید، روش ارجاع آن به صورت Sheet1!A1 است.
توجه کنید که اگر نام برگه حاوی فضای خالی باشد، باید علامت نقل قول (‘ ‘) را در اطراف نام قرار دهید. برای مثال، اگر شما می خواهید سلول A1 را در یک برگه به نام July Budget ارجاع دهید، مرجع سلولی آن
July Budget’!A1′ خواهد بود.
سلول های مرجع در صفحات مختلف:
در مثال زیر ما به ارجاع یک سلول بین دو برگه می پردازیم. این امر به ما اجازه می دهد که از مقدار یکسان بدون نیاز به بازنویسی فرمول یا کپی کردن داده ها بین صفحات دو برگه مختلف استفاده کنیم.
سلول مورد نظر خود را تعیین کنید. در مثال، می خواهیم به سلول E14 در برگه سفارش منو ارجاع دهیم.
به برگه مورد نظر خود بروید. در مثال، برگه Catering Invoice را انتخاب می کنیم.
برگه انتخابی ظاهر خواهد شد.
سلول مورد نظر خود را انتخاب کنید. در مثال، سلول B2 را انتخاب می کنیم.
علامت برابر (=)، سپس نام برگه و بعد از آن علامت تعجب (!) و در آخر آدرس سلول را وارد کنید. در مثال ،
” Menu Order’!E14′ = ” را تایپ می کنیم.
بر روی صفحه کلید خود Enter را فشار دهید. مقدار سلول نشان داده خواهد شد. اگر مقدار سلول E14 در برگه سفارش منو تغییر کند، به طور خودکار بر روی برگه Catering Invoice به روز می شود.
اگر شما نام برگه خود را تغییر دهید، مرجع سلولی به صورت خودکار به روز می شود تا منعکس کننده نام برگه جدید باشد.
آموزش فرمول نویسی در اکسل
ترجمه شده در سایت خلایق
منبع:
gcflearnfree.org
مقالات پیشنهادی :
آموزش فرمول نویسی در اکسل / بخش اول
آموزش فرمول نویسی در اکسل / بخش دوم
