آموزش تصویری مراجع سلولی (رفرنس دهی سلولی) در نرم افزار اکسل

آدرس دهی، رفرنس دهی یا ایجاد مراجع سلولی ساده و ترکیبی یکی از مهم ترین موضوعات در کار با سلول ها در نرم افزار اکسل است. در این پست بصورت تصویری این موضوع را که از سری آموزش های کاربردی نرم افزار اکسل است به شما آموزش می دهیم. قبل از معرفی روش های آدرس دهی در اکسل ابتدا باید ببینیم Reference در اکسل به چه معناست؟ Reference به یک سلول و یا یک محدوده از سلول ها گفته می شود که اطلاعات درون این سلول ها در داخل توابع و فرمول های اکسل مورد استفاده قرار می گیرند. برای آدرس دهی در اکسل از دو روش استفاده می شود.

• آدرس دهی نسبی
• آدرس دهی مطلق

دو نوع مرجع سلولی وجود دارد. نسبی و مطلق. مراجع مطلق و نسبی وقتی که کپی شوند یا سلولی های دیگر را پر کنند به طور متفاوت رفتار می کنند. مراجع نسبی هنگامی که یک فرمول سلول دیگر کپی شده باشد، تغییر میکند. مراجع مطلق در هر صورت ثابت باقی میمانند و فرقی ندارد که کجا کپی شده باشند.

مراجع نسبی

به طور فرض، همه ی مراجع سلولی، مراجع نسبی هستند. وقتی در اطراف سلول های چندگانه کپی شدند، بر اساس جایگاه نسبی سطر ها و ستون ها، تغییر می کنند. به عنوان مثال، اگر شما فرمول را از سطر ۱ به سطر ۲، کپی کنید: فرمول خواهد شد. قبل از شروع بهتر است نحوه کار با سلول ها در نرم افزار اکسل را بلد باشید!

مراجع نسبی، هر جا که شما نیاز به تکرار محاسبه مشابه در اطراف سطرها یا ستون های مشابه داشته باشید، فوق العاده مناسب هستند. برای ساختن و کپی یک فرمول، مراجع نسبی را استفاده کنید. در مثال زیر فرمولی می خواهیم بسازیم که قیمت هر آیتم آن به وسیله مقدار آن، افزایش خواهد یافت. یعنی قیمت در تعداد هر چیزی ضرب خواهد شد. به جای ساختن یک فرمول جدید برای هر سطر،ما میتوانیم یک فرمول واحد در سلول D2 بسازیم و سپس آن را در سایر ردیف ها کپی کنیم. مراجع نسبی را انتخاب خواهیم کرد. بنابراین، آن فرمول به طور صحیح، کل هر آیتم را حساب می کند.

۱- سلولی که فرمول را شامل خواهد شد، انتخاب کنید. در مثال ما، سلول D2 را انتخاب خواهیم کرد.

۲- فرمول را به منظور محاسبه ارزش دلخواه، وارد کنید. در مثال ما، B2-C2 را تایپ خواهیم کرد.

۳- کلید ENTER روی کیبورد (صفحه کلید) را انتخاب کنید. فرمول محاسبه خواهد شد و نتیجه در آن سلول، نشان داده خواهد شد.

۴- در گوشه سمت راست سلول مورد نظر، یک دستگیره مربعی شکل تو پر وجود دارد، در مثال ما دستگیره مورد نظر را در سلول D2 شناسایی خواهیم کرد.

۵- کلیک کنید، نگه دارید و دستگیره توپر را به سلول های دیگری که می خواهید پر شوند، درگ کنید در مثال ما، سلول های (D12-D3)D3:D12 را انتخاب خواهیم کرد.

۶- موس را رها کنید. فرمول از طریق مراجع نسبی در تمام سلول های انتخاب شده، کپی خواهد شد و مقادیر در هر سلول، محاسبه می شوند.

به منظور دقت بیشتر، می توانید برای چک کردن فرمول ها، در سلول های پر شده دابل کلیک کنید. مراجع سلولی نسبی، باید بر حسب سطر هایشان، برای هر سلول متفاوت باشند.

مراجع مطلق

شاید بارها پیش بیاید که شما نخواهید هنگام پر کردن سلول ها یک مرجع سلولی تغییر کند. بر خلاف مراجع نسبی، مراجع هنگام کپی یا پر شدن، تغییر نمیکنند. شما می توانید برای ثابت نگه داشتن یک سطر یا یک ستون، از مرجع مطلق استفاده کنید. یک مرجع مطلق در یک فرمول با افزودن علامت دلار ($) مشخص می شود. آن علامت می تواند جلو تر از مرجع ستون، ردیف یا هر دو قرار بگیرد. شما به طور کلی فرمت $A$2 را برای ساختن فرمول جدید که مراجع مطلق را شامل می شود، استفاده کنید. آن دو فرمت دیگر اغلب کاربردی ندارند. $A2 –A$2 وقتی که در حال نوشتن فرمول هستید، می توانید کلید F4 روی صفحه کلید را فشار دهید و بین مراجع سلول های نسبی و مطلق ، جابجا شوید. این یک راه اسان برای وارد کردن سریع یک مرجع مطلق می باشد.

ساختن و کپی یک و استفاده از مراجع مطلق

در مثال ما، ۷٫۵% از میزان مالیات فروش را در سلول E1 برای محاسبه مالیات فروش همه ی کالا ها در ستون D را استفاده خواهیم کرد. ما به مرجع سلول مطلق $E$1 در فرمولمان نیاز خواهیم داشت. به این خاطر که هر فرمولی که برای میزان مالیات مشابه به کار برده می شود، هدف ما این است که وقتی فرمول کپی می شود و سایر سلول های ستون D را پر میکند، مرجع ماثابت باقی بماند.

1. سلولی که فرمول در آن باید باشد را انتخاب کنید. در مثال ما، D3 را انتخاب خواهیم کرد.

۲- فرمول را برای محاسبه مقدار مورد نظر وارد کنید. در مثال ما، فرمولی که در تصویر می بینید را تایپ می کنیم.

۳- کلید ENTER از صفحه کلید را فشار دهید. فرمول محاسبه خواهد کرد و نتیجه در سلول، نشان داده خواهد شد.

۴- دستگیره ی مربع توپر در گوشه سمت راست سلول مورد نظر را پیدا کنید. در مثال ما، در سلول D3 این دستگیره پیدا می شود.

۵- کلیک کنید، روی دستگیره نگه دارید و دستگیره را تا سلول هایی که میخواهید پر شوند، درگ کنید، در مثال ما از سلول D4 تاD13 (D4:D13).

۶- موس را رها کنید. فرمول، با ی مرجع مطلق به سلول های انتخاب شده کپی خواهد شد، و مقادیر در هر سلول، محاسبه خواهند شد.

با هر بار دابل کلیک کردن، می توانید با دقت بسیار، فرمول ها را چک کنید. مرجع مطلق باید برای هر سلول، مشابه باشد،در حالی که مرجع های دیگر برای سطر سلولی آن، نسبی هستند.

اطمینان پیدا کنید که علامت$ در هر جایی که مرجع مطلق می سازید، حول سلول های چندگانه باشد. در مثال زیر، علامت های دلار ، حذف شده اند. این امر باعث می شود که اکسل، آن را به عنوان یک مرجع نسبی تشخیص دهد و یک نتیجه غلط بدست آمده و به سایر سلول ها انتقال داده شود.

نکته مهم! چگونگی تجزیه و تحلیل داده های آماری پرسشنامه ای در برنامه اکسل

کاربرد مراجع سلول با چندین صفحه کار

اکسل به شما اجازه می دهد که به هر سلول در هر صفحه کاری، اشاره کنید. که این می تواند فوق العاده به شما کمک کند که به یک مقدار ویژه از یک صفحه به صفحه ی دیگر، برای انجام این کار، فقط نیاز به آغاز مرجع سلول با نام صفحه ی کار به همراه یک علامت هشدار (!) دارید. به عنوان مثال اگر می خواهید به سلول A1 روی صفحه ی ۱ اشاره کنید، مرجع سلول آن sheet1!a1 خواهد شد. به عنوان یادداشت که اگر نام صفحه کار، شامل یک دوره می شود، شما نیاز به علامت گیومه ساده (‘ ‘ ) دارید که دور نام آن بگذارید. به عنوان مثال، اگر می خواهید به سلول مرجع A1 روی صفحه ی کاری که نام آن بودجه یجولای است، اشاره کنید ، مرجع سلولی آن!A1’بودجه جولای’ خواهد بود. (‘July bedget’A1).

• اشاره به سلول های اطراف صفحه ی کار
• در مثال زیر، ما به سلولی با یک مقدار محاسبه شده بین دو صفحه ی کار، اشاره خواهیم کرد.
• این کار به ما اجازه می دهد که بدون نوشتن دوباره ی فرمول و کپی کردن داده بین صفحات کار مقدار مشابه دقیق را روی دو صفحه کار متفاوت ، به کار ببریم.

۱- سلولی را که میخواهید به آن مراجعه کنید، شناسایی کنید، (پیدا کنید) و صفحه کار آن را یادداشت کنید. در مثال ما، می خواهیم به سلول E14 روی صفحه کار سفارش منو اشاره کنیم.

۲- به سمت صفحه ی کار مورد نظر حرکت کنید. در مثال ما، صفحه ی کار فاکتور تهییه غذا را انتخاب می کنیم.

۳- صفحه ی کار انتخاب شده، ظاهر خواهد شد.

۴- سلولی را که می خواهید مقدار در آن ظاهر شود را پیدا و انتخاب کنید. در مثال ما، سلول B2 را انتخاب می کنیم.

۵- علامت مساوی(=) را تایپ کنید، نام صفحه کار به همراه علامت هشدار(!) ، و آدرس سلول.

۶- در مثال ما،!E14’سفارش منو’= را تایپ می کنیم. ، !E14’menu order’ =

۷- کلید enter روی صفحه کلید را فشار دهید. مقدار سلول اشاره شده ظاهر خواهد شد.اگر مقدار سلولE14 روی صفحه کار سفارش منو تغییر کرد، آن مقدار ، به صورت اتوماتیک روی صفحه کار فاکتور تهییه غذا آپدیت (به روز) می شود.

اگر نام صفحه کار را تغییر دهید، مرجع سلولی به صورت اتوماتیک ، نام جدید صفحه کار را نشان می دهد.

نکته مهم! آموزش کامل نوشتن فرمول ها در نرم افزار اکسل ۲۰۱۰-۲۰۱۶ (تصویری)

اگر نام صفحه کار را به صورت اشتباه وارد کنید، خطای #REF! در سلول، ظاهر خواهد شد. در مثال زیر، ما نام صفحه کار را اشتباه تایپ کردیم. روی علامت (!) کلیک می کنیم و گزینه ی مرد نظر را از منو انتخاب کرده، و اشتباه را ویرایش کرده یا نادیده می گیریم.

نکته مهم! نرم افزار اکسل را در کمتر از ۳ هفته بصورت حرفه ای بیاموزید! (بدون نیاز به کلاس)

اگر هرگونه سوالی در این زمینه دارید در بخش نظرات مطرح کنید تا سریع وآسان به آن پاسخ دهیم.