چه باید بدانیم
- برای محاسبه چندین مورد بر اساس سلول‌های جاهای دیگر، و ثابت نگه داشتن سطر یا ستون، از یک مرجع سلول مطلق استفاده کنید.
- در این معادله، مرجع سلول مطلق A$12 است:=+B2+B2+A$12.
- نماد $ حتی در هنگام کپی کردن یا پر کردن یک ستون یا ردیف با فرمول یکسان، سطر یا ستون را ثابت نگه می دارد.
این مقاله نحوه استفاده از مرجع سلول مطلق در اکسل 2010 و نسخه های بعدی را توضیح می دهد تا از تنظیم خودکار فرمول ها نسبت به تعداد سطر یا ستونی که پر کرده اید جلوگیری شود.
مرجع مطلق سلول در اکسل چیست؟
دو نوع راه برای ارجاع سلول در داخل فرمول در اکسل وجود دارد. می توانید از یک مرجع سلول نسبی یا یک مرجع سلول مطلق استفاده کنید.
- مرجع سلول نسبی: آدرس سلولی که نماد $ را در جلوی مختصات سطر یا ستون ندارد. این مرجع به‌طور خودکار ستون یا سلول را نسبت به سلول اصلی به‌روزرسانی می‌کند، وقتی که آن را پر کنید یا آنطرف را پر کنید.
- مرجع سلول مطلق: آدرس سلولی که حاوی نماد $ در جلوی مختصات ردیف یا ستون است. این سطر یا ستون مرجع را ثابت نگه می‌دارد حتی زمانی که یک ستون یا ردیف را با فرمول یکسان پر می‌کند.
ارجاع سلولی می تواند برای کسانی که تازه با آن آشنا هستند مفهومی انتزاعی به نظر برسد.
چگونه از مرجع مطلق سلول در اکسل استفاده کنیم
چند راه مختلف برای استفاده از مرجع مطلق در اکسل وجود دارد. روشی که استفاده می کنید بستگی دارد که کدام قسمت از مرجع را می خواهید ثابت نگه دارید: ستون یا ردیف.
-
برای مثال، صفحه‌گسترده‌ای را انتخاب کنید که در آن می‌خواهید مالیات بر فروش واقعی را برای چندین مورد محاسبه کنید، بر اساس سلول‌های مرجع در جاهای دیگر برگه.
Image -
در سلول اول، فرمول را با استفاده از مراجع سلولی نسبی استاندارد برای قیمت اقلام وارد می کنید. اما برای وارد کردن مالیات بر فروش صحیح در محاسبه، باید از یک مرجع مطلق برای ردیف استفاده کنید، اما نه برای سلول.
Image -
این فرمول به دو سلول به روش های مختلف ارجاع می دهد. B2 سلول سمت چپ اولین سلول مالیات فروش، نسبت به موقعیت آن سلول است. مرجع A$12 به شماره مالیات فروش واقعی فهرست شده در A12 اشاره می کند. نماد $ ارجاع به ردیف 12 را ثابت نگه می دارد، صرف نظر از اینکه سلول های مجاور را در کدام جهت پر می کنید. تمام سلول های زیر این مورد اول را پر کنید تا این مورد را در عمل ببینید.
Image -
می توانید ببینید که وقتی ستون را پر می کنید، سلول در ستون دوم از ارجاع سلولی نسبی برای مقادیر هزینه در ستون B استفاده می کند و ستون را برای مطابقت با ستون مالیات بر فروش افزایش می دهد. با این حال، ارجاع به A12 به دلیل نماد $ ثابت می ماند، که مرجع ردیف را ثابت نگه می دارد. اکنون ستون D را با شروع از سلول C2 پر کنید.
Image -
اکنون، می‌توانید ببینید که برای پر کردن ردیف سمت راست از مالیات فروش دولتی (ایندیانا) استفاده می‌شود، زیرا مرجع ستون نسبی است (یکی را درست مانند سلولی که پر می‌کنید به سمت راست منتقل می‌کند). با این حال، مرجع نسبی هزینه در حال حاضر نیز یکی را به راست منتقل کرده است، که نادرست است. می‌توانید این مشکل را با تبدیل فرمول اصلی در C2 به ستون B با یک مرجع مطلق حل کنید.
Image -
با قرار دادن نماد $ در مقابل "B"، یک مرجع مطلق برای ستون ایجاد کرده اید. اکنون وقتی به سمت راست پر کنید، خواهید دید که ارجاع به ستون B برای هزینه ثابت باقی می ماند.
Image -
اکنون، وقتی ستون D را به سمت پایین پر می کنید، خواهید دید که تمام ارجاعات سلولی نسبی و مطلق دقیقاً همانطور که مد نظر شماست کار می کنند.
Image -
ارجاعات سلولی مطلق در اکسل برای کنترل سلول‌هایی که هنگام پر کردن ستون‌ها یا ردیف‌ها ارجاع داده می‌شوند، حیاتی هستند. برای ارجاع داده‌های صحیح در سلول صحیح، از نماد $ هر طور که نیاز دارید در مرجع سلول برای ثابت نگه داشتن ردیف یا ستون مرجع استفاده کنید. وقتی مراجع سلولی نسبی و مطلق را مانند این ترکیب می کنید، به آن مرجع سلولی مختلط می گویند.
اگر از یک مرجع ترکیبی استفاده می کنید، مهم است که ستون یا ردیف داده های منبع را با ستون یا ردیفی که در آن فرمول را تایپ می کنید، تراز کنید. اگر مرجع سطر را نسبی کرده‌اید، به خاطر داشته باشید که وقتی به طرفین پر می‌کنید، شماره ستون داده‌های منبع همراه با ستون سلول فرمول افزایش می‌یابد. این یکی از مواردی است که آدرس دهی نسبی و مطلق را برای کاربران جدید پیچیده می کند، اما زمانی که آن را یاد بگیرید، کنترل بسیار بیشتری بر روی آوردن سلول های داده منبع به مراجع سلول فرمول خود خواهید داشت.
استفاده از مراجع مطلق سلول برای پین کردن مرجع یک سلول
رویکرد دیگر برای استفاده از ارجاع سلول مطلق، اعمال آن بر روی ستون و سطر است تا اساساً فرمول را برای استفاده از تنها یک سلول بدون توجه به جایی که باشد، "پین" کنید.
با استفاده از این روش، می توانید به پهلو یا پایین پر کنید و مرجع سلول همیشه ثابت می ماند.
استفاده از یک مرجع سلول مطلق در ستون و ردیف تنها زمانی مؤثر است که فقط به یک سلول در تمام سلول هایی که پر می کنید ارجاع دهید.
-
با استفاده از همان صفحه گسترده مثال بالا، می توانید با افزودن نماد $ به ستون و ردیف مرجع، فقط به نرخ مالیات یک ایالت اشاره کنید.
Image -
این باعث می شود که هر دو ستون "A" و ردیف "12" ثابت بمانند، صرف نظر از جهتی که سلول ها را پر می کنید. برای مشاهده عملی این موضوع، پس از اینکه فرمول را با مرجع مطلق ستون و ردیف به روز کردید، کل ستون مالیات بر فروش ME را پر کنید. می بینید که هر سلول پر شده همیشه از مرجع مطلق $A$12 استفاده می کند. نه ستون و نه ردیف تغییر نمی کند.
Image -
از همان فرمول برای ستون مالیات فروش ایندیانا استفاده کنید، اما این بار از مرجع مطلق برای ستون و ردیف استفاده کنید. در این مورد، $B$12 است.
Image -
این ستون را پر کنید، و دوباره خواهید دید که ارجاع به B12 به لطف مرجع مطلق هم برای ستون و هم برای سطر، هرگز در سلول تغییر نمی کند.
Image -
همانطور که می بینید، راه های متعددی وجود دارد که می توانید از مراجع مطلق سلول در اکسل برای انجام کارهای مشابه استفاده کنید. آنچه که ارجاعات مطلق به شما ارائه می دهند، انعطاف پذیری برای حفظ ارجاعات سلولی ثابت است، حتی زمانی که تعداد زیادی ستون یا ردیف را پر می کنید.
می توانید با برجسته کردن مرجع و سپس فشار دادن F4 در میان مراجع سلولی نسبی یا مطلق گردش کنید، هر بار که F4 را فشار می دهید، مرجع مطلق به هر ستون اعمال می شود. ردیف، هر دو ستون و سلول، یا هیچ یک از آنها. این یک راه ساده برای تغییر فرمول شما بدون نیاز به تایپ نماد $ است.
زمانی که باید از مراجع مطلق سلول استفاده کنید
تقریباً در هر صنعت و زمینه ای، دلایل زیادی وجود دارد که ممکن است بخواهید از منابع سلول مطلق در اکسل استفاده کنید.
- استفاده از ضریب های ثابت (مانند قیمت در هر واحد) در لیست بزرگی از اقلام.
- هنگامی که اهداف سود سالانه را پیش بینی می کنید، برای هر سال یک درصد واحد اعمال کنید.
- هنگام ایجاد فاکتورها، از ارجاعات مطلق برای اشاره به نرخ مالیات یکسان در همه موارد استفاده کنید.
- از مراجع مطلق سلولی در مدیریت پروژه برای ارجاع به نرخ‌های در دسترس بودن ثابت برای منابع فردی استفاده کنید.
- از ارجاعات ستون نسبی و ارجاعات ردیف مطلق برای تطبیق محاسبات ستون در سلول های ارجاع شده خود با مقادیر ستون در جدول دیگری استفاده کنید.
اگر از ترکیبی از ارجاعات نسبی و مطلق برای ستون‌ها یا ردیف‌ها استفاده می‌کنید، فقط باید مطمئن شوید که موقعیت ستون‌ها یا ردیف‌های داده مبدا با ستون یا ردیف سلول‌های مقصد مطابقت داشته باشد. دوباره فرمول را تایپ کنید).
