مقدمه
با عرض سلام خدمت کاربران محترم سایت پی وی لرن و دوستانی که در مجموعه آموزشی اکسل ۲۰۱۹ همراه ما هستند. در مورد ایجاد فرمول های پیچیده در اکسل ، ایجاد فرمول پیچیده با استفاده از ترتیب عملیات در اکسل و… صحبت شد.با ایجاد و کپی فرمول با مرجع نسبی در اکسل ، سلول های مرجع در کاربرگهای اکسل و مراجع مطلق در اکسل و …داریم.پس در ادامه توضیحاتی ضروری در مورد مراجع نسبی و مطلق و سلول های مرجع در کاربرگهای اکسل خواهیم داشت
مرجع نسبی و مطلق در EXCEL
دو نوع مرجع سلولی وجود دارد: نسبی و مطلق. مراجع نسبی و مطلق هنگام کپی و پر شدن به سلول های دیگر رفتار متفاوتی دارند.
هنگامی که یک فرمول به سلول دیگری کپی می شود، مرجع نسبی تغییر می کند.
از سوی دیگر، مرجع مطلق، بدون توجه به جایی که کپی شده اند، ثابت باقی می مانند.
مراجع نسبی در اکسل
به طور پیش فرض، تمام مراجع سلولی، مراجع نسبی هستند.
هنگامی که در سلول های متعدد کپی می شوند، آنها براساس موقعیت نسبی ردیف ها و ستون ها تغییر می کنند.
برای مثال، اگر فرمول = A1 + B1 را از ردیف ۱ به ردیف ۲ کپی کنید، فرمول = A2 + B2 خواهد شد.
مراجع نسبی هر وقت که نیاز به تکرار همان محاسبات را در سطر یا ستون چندگانه دارید،مناسب هستند.
ایجاد و کپی فرمول با مرجع نسبی در اکسل
در مثال زیر ما می خواهیم یک فرمول ایجاد کنیم که قیمت هر محصول را با کمترین مقدار افزایش دهد.
به جای ایجاد فرمول جدید برای هر ردیف، ما می توانیم یک فرمول واحد را در سلول D4 ایجاد کنیم.
سپس آن را به سطر های دیگر کپی کنیم.
ما از منابع نسبی استفاده می کنیم، بنابراین فرمول محاسبه مقدار کل هر مورد را به درستی محاسبه می کند.
۱٫سلول را انتخاب کنید که حاوی فرمول باشد. در مثال ما، سلول D4 را انتخاب می کنیم.
ایجاد و کپی فرمول با مرجع نسبی در اکسل
۲٫ فرمول را برای محاسبه ارزش مورد نظر وارد کنید در مثال ما، = B4 * C4 تایپ شده است.
ایجاد و کپی فرمول با مرجع نسبی در اکسل
۳٫ Enter را وارد کنید از روی صفحه کلید خود . فرمول محاسبه خواهد شد و نتیجه در سلول نمایش داده خواهد شد.
۴٫ fill handle را در قسمت پایین گوشه سمت راست سلول مورد نظر قرار دهید.
در مثال ما، fill handle را برای سلول D4 قرار می دهیم.
ایجاد و کپی فرمول با مرجع نسبی در اکسل
۵٫ بر روی سلول هایی که می خواهید پر کنید، fill handle را قرار دهید و آن را بکشید.
در این مثال ما سلول های D5: D13 را انتخاب می کنیم.
ایجاد و کپی فرمول با مرجع نسبی در اکسل
۶٫ ماوس را آزاد کنید فرمول به سلول های انتخاب شده با ارجاعات نسبی کپی می شود.
نتیجه در هر سلول نمایش داده می شود.
ایجاد و کپی فرمول با مرجع نسبی در اکسل
شما می توانید سلول های پر شده را دوبار کلیک کنید تا فرمول های آنها را برای دقت بررسی کنید.
مراجع نسبی برای هر سلول، بسته به ردیف آنها، باید متفاوت باشد.
ایجاد و کپی فرمول با مرجع نسبی در اکسل
مراجع مطلق در اکسل
ممکن است زمانی وجود داشته باشد که شما نمی خواهید که مرجع سلولی وقتی در سلول های دیگر کپی می شود تغییر کند.
بر خلاف نسبی، مراجع مطلق هنگام کپی یا پر شدن تغییر نمی کنند.
شما می توانید از یک مرجع مطلق برای ثابت بودن ردیف و یا ستون استفاده کنید.
مرجع مطلق در یک فرمول با اضافه کردن علامت دلار ($) تعیین می شود.
این می تواند مقدم باشد بر مرجع ستون، مرجع ردیف یا هر دو .
مراجع مطلق در اکسل
شما معمولا از فرمت $A$2 در هنگام ایجاد فرمولهای حاوی مرجع مطلق استفاده می کنید. دو فرمت دیگر در موارد بسیار کمتری استفاده می شوند.
هنگام نوشتن یک فرمول، می توانید کلید F4 روی صفحه کلید خود را برای تغییر بین مراجع سلولی نسبی و مطلق فشار دهید.
این یک راه آسان برای سریع قرار دادن مرجع مطلق است.پ
ایجاد و کپی فرمول با مرجع مطلق در اکسل
در مثال زیر، از سلول E2 (که شامل نرخ مالیاتی ۷٫۵٪) است، برای محاسبه مالیات بر فروش برای هر مورد در ستون D استفاده می کنیم.
برای اطمینان از اینکه مرجع نرخ مالیات ثابت است، حتی زمانی که فرمول کپی شده و پر شده به سلول های دیگر – ما باید سلول $E$2 را یک مرجع مطلق بسازیم.
۱٫سلولی را انتخاب کنید که حاوی فرمول باشد. در مثال ما، سلول D4 را انتخاب می کنیم.
ایجاد و کپی فرمول با مرجع مطلق در اکسل
۲٫ فرمول را برای محاسبه ارزش مورد نظر وارد کنید در مثال ما، =(B4*C4)*$E$2, را تایپ می کنیم، و $E$2 را یک مرجع مطلق می سازیم.
ایجاد و کپی فرمول با مرجع مطلق در اکسل
۳٫ فشار دهید Enter را از صفحه کلید خود . فرمول محاسبه خواهد شد و نتیجه در سلول نمایش داده خواهد شد.
۴٫ fill handle را در قسمت پایین گوشه سمت راست سلول مورد نظر قرار دهید.
در مثال ما، fill handle را برای سلول D4 قرار می دهیم.
ایجاد و کپی فرمول با مرجع مطلق در اکسل
۵٫ fill handle راروی سلول مورد نظر خود را که می خواهید پر کنید قراردهید و بکشید. (سلول D5: D13 در مثال ما).
ایجاد و کپی فرمول با مرجع مطلق در اکسل
۶٫ ماوس را آزاد کنید فرمول با مرجع مطلق به سلول های انتخاب شده کپی می شود و مقادیر در هر سلول محاسبه می شود.
ایجاد و کپی فرمول با مرجع مطلق در اکسل
شما می توانید سلول های پر شده را دوبار کلیک کنید تا فرمول های آنها را برای دقت بررسی کنید.
مرجع مطلق باید برای هر سلول یکسان باشد، در حالی که سایر مراجع مربوط به همان ردیف سلول هستند.
ایجاد و کپی فرمول با مرجع مطلق در اکسل
مطمئن شوید که علامت دلار ($) را هر وقت که مرجع مطلق در سلول های مختلف ایجاد می کنید، قرار دهید.
علائم دلار در مثال زیر حذف شده است. این باعث شد که اکسل آن را به عنوان یک مرجع نسبی تفسیر کند.
همچنین نتیجه نادرستی را در هنگام کپی به سایر سلول ها ایجاد کرد.
ایجاد و کپی فرمول با مرجع مطلق در اکسل
استفاده از مراجع سلولی با چندین کاربرگ در اکسل
اکسل به شما اجازه می دهد تا به هر سلولی بر روی هر برگه مراجعه کنید، که می تواند به ویژه مفید باشد.
اگر شما می خواهید یک مقدار خاص را از یک برگه به دیگری ارجاع دهید. برای انجام این کار، شما تنها باید مرجع سلولی را با نام کاربرگ و سپس علامت تعجب (!) شروع کنید. برای مثال، اگر شما می خواهید سلول A1 را در Sheet1 ارجاع دهید، مرجع سلول آن Sheet1!A1 است.
توجه داشته باشید اگر یک نام برگه حاوی یک فضای باشد.
شما باید علامت نقل قول (”) را در اطراف نام قرار دهید.
به عنوان مثال، اگر شما می خواهید سلول A1 را در یک برگه با نام July Budget ارجاع دهید، مرجع سلول آن July Budget’!A1′ خواهد بود.
سلول های مرجع در کاربرگهای اکسل
خب رسیدیم به سلول های مرجع در کاربرگهای اکسل پس نکاتی در مورد سلول های مرجع در کاربرگهای اکسل داریم:
در مثال زیر ما به یک سلول با ارزش محاسبه شده بین دو برگه مراجعه خواهیم کرد.
این به ما اجازه می دهد که از همان مقدار یکسان در دو صفحه مختلف استفاده کنیم بدون بازنویسی فرمول یا کپی کردن داده ها.
۱٫سلولی را که می خواهید به آن مراجعه کنید تعیین کنید و آن را به لیست کاربرگ خود اضافه کنید.
در مثال ما، می خواهیم سلول E14 را در کاربرگ Menu Order ارجاع دهیم.
سلول های مرجع در کاربرگهای اکسل
۲٫ به برگه مورد نظر بروید در مثال ما، کار برگ Catering Invoice را انتخاب می کنیم.
سلول های مرجع در کاربرگهای اکسل
۳٫ تعیین محل و انتخاب کنید سلولی را که در آن شما می خواهید مقدار ظاهر شود. در مثال ما ، سلول C4 را انتخاب می کنیم.
سلول های مرجع در کاربرگهای اکسل
۴٫ علامت برابر (=)، نام ورق و سپس علامت تعجب (!) و آدرس سلول را وارد کنید. در مثال ما، ‘ll type =’Menu Order’!E14
سلول های مرجع در کاربرگهای اکسل
۵٫ Enter را وارد کنیداز روی صفحه کلید خود. مقدار سلول ارجاع نشان داده خواهد شد. در حال حاضر، اگر مقدار سلول E14 در کار برگ Menu Order تغییر کند، به طور خودکار بر روی برگه Catering Invoice نیز به روز می شود.
سلول های مرجع در کاربرگهای اکسل
اگر شما یک برگه خود را تغییر نام دهید، مرجع سلول به صورت خودکار به روز می شود تا منعکس کننده نام برگه جدید شود.
اگر یک نام برگه را به درستی وارد نکنید،#REF! خطا در سلول ظاهر می شود.
در مثال زیر ما نام کاربرگ را عوض کردیم. برای ویرایش، نادیده گرفتن یا بررسی خطا، روی دکمه Error در کنار سلول کلیک کنید و یک گزینه از منو را انتخاب کنید.
سلول های مرجع در کاربرگهای اکسل
و پایان سلول های مرجع در کاربرگهای اکسل
چالش!
کتاب کار تمرین عملی ما را باز کنید
روی برگه Paper Goods در پایین سمت چپ کتاب کار کلیک کنید.
در سلول D4، یک فرمول را وارد کنید که قیمت واحد در B4، مقدار C4 و نرخ مالیات در E2 را افزایش دهد.
اطمینان حاصل کنید که از مرجع مطلق سلول برای نرخ مالیات استفاده کنید، زیرا هر سلول یکسان خواهد بود.
از fill handle برای کپی فرمول هایی که برای سلول D5: D12 ایجاد کرده اید،استفاده کنید.
نرخ مالیات در سلول E2 را به ۶٫۵٪ تغییر دهید. توجه داشته باشید که تمام سلول های شما به روز می شوند.
هنگامی که تمرین شما به پایان رسید، کتاب کار شما باید شبیه به این باشد:
چالش!
روی برگه Invoice Catering کلیک کنید.
ارزش را در سلول C5 حذف کنید و آن را با یک منبع برای کل هزینه کالاهای paper جایگزین کنید. نکته: هزینه Paper Goods در سلول E13 در کاربرگ Paper Goods قرار دارد.
از مراحل بالا استفاده کنید تا محاسبات مالیات فروش برای هر آیتم در صفحه برگزیده فهرست انتخاب شود. هزینه کل در سلول E14 باید به روز شود. سپس،
در سلول C4 کاربرگ Catering Invoice، یک مرجع سلولی را به مجموعه کل که فقط محاسبه کرده اید ایجاد کنید.
وقتی کارتان تمام شد، برگه صورتحساب Catering Invoice باید چیزی شبیه به این باشد: D5: D12 …..
نرخ مالیات در سلول E2 را به ۶٫۵٪ تغییر دهید. توجه داشته باشید که تمام سلول های شما به روز می شوند.پ
هنگامی که شما به پایان رسید، کتاب کار شما باید شبیه به این باشد:
چالش!
کلام پایانی
مطالبی راجع به ایجاد و کپی فرمول با مرجع نسبی در اکسل ، سلول های مرجع در کاربرگهای اکسل ومرجع نسبی دونستیم. بعد از مراجع نسبی و مطلق و سلول های مرجع در کاربرگهای اکسل سراغ توابع میریم.
فروشگاه
فیلم های آموزشی
کتاب های آموزشی
مقالات آموزشی
وردپرس
