ارجاعات سلولی هسته اصلی نحوه عملکرد مایکروسافت اکسل هستند. چه با محاسبات ساده و چه با فرمول‌های پیچیده کار می‌کنید، درک مراجع سلولی و تسلط بر انواع مختلف ارجاعات، کلید رسیدن به نتایج دقیق است.
خلاصه عناوین
- مراجع سلولی در اکسل چیست؟
- مراجع سلولی نسبی
- مراجع مطلق سلول
- مراجع ترکیبی سلولی
نکات کلیدی
- مراجع سلولی در اکسل مانند آدرس هایی هستند که به سلول های خاصی اشاره می کنند.
- سه نوع مرجع سلولی اکسل نسبی، مطلق و مختلط هستند که هر کدام دارای عملکردهای منحصر به فردی هستند.
- تسلط بر منابع سلولی برای ایجاد صفحات گسترده انعطاف پذیر بسیار مهم است و کارهایی مانند بودجه بندی و تجزیه و تحلیل داده ها را بسیار ساده تر و کارآمدتر می کند.
ارجاعات سلولی هسته اصلی نحوه عملکرد مایکروسافت اکسل هستند. چه با محاسبات ساده و چه با فرمول‌های پیچیده کار می‌کنید، درک مراجع سلولی و تسلط بر انواع مختلف ارجاعات، کلید رسیدن به نتایج دقیق است.
مراجع سلولی در اکسل چیست؟
مرجع سلولی به سادگی راهی برای شناسایی یک سلول خاص در صفحه گسترده شما است.
هر کاربرگ اکسل دارای چندین ردیف و ستون است که در هر سطر و ستون سلول ها (جعبه ها) وجود دارد. همانطور که کار می کنید، مقادیر یا متنی را در این سلول ها وارد می کنید. در ردیف اول، حروفی را خواهید دید که برای نامگذاری ستون ها استفاده می شوند. سپس، در ستون اول، اعدادی را خواهید یافت که برای نامگذاری ردیف ها استفاده می شوند.
برای شناسایی یک سلول، فقط باید حرف و شماره نامگذاری را شناسایی کنید. به عنوان مثال، سلول در ستون و ردیف دوم به طور پیش فرض A1 است (حرف همیشه قبل از عدد است). A1، در این مثال، یک مرجع سلولی است.
هنگامی که از یک مرجع سلول در فرمول استفاده می کنید، می توانید به راحتی به داده های آن سلول ارجاع دهید. هر بار که نیاز به استفاده از یک عملگر محاسباتی دارید، نیازی به کپی و پیست کردن مقادیر ندارید.
برای مثال، اگر تابع SUM را برای سلول‌های A1 تا A5 [=SUM(A1:A5)] انجام می‌دهید، محاسبات شما همیشه با تغییر داده‌های سلول‌ها به‌طور خودکار تنظیم می‌شوند. تا زمانی که مقدار در سلول تغییر می کند، نتیجه عملیات تغییر می کند زیرا شما به سلول ارجاع می دهید، نه مقدار موجود در سلول.
[=SUM(A1:A5)]
ارجاعات سلولی امکان ساخت صفحات گسترده انعطاف پذیر را فراهم می کند که به راحتی با تغییرات داده ها سازگار می شوند. این به ویژه برای کارهایی مانند بودجه بندی، تجزیه و تحلیل داده ها و گزارش دهی مفید است، جایی که داده ها می توانند اغلب تغییر کنند.
سه نوع مرجع سلولی در مایکروسافت اکسل وجود دارد.
- مرجع نسبی
- مرجع مطلق
- مرجع مختلط که اساساً فقط ترکیبی از ارجاعات نسبی و مطلق است
هر نوع مرجع سلول از نظر نحوه رفتار مایکروسافت اکسل با سلول ارجاع شده هنگام کپی یا جابجایی فرمول ها متفاوت است.
مراجع سلولی نسبی
به طور پیش فرض، مراجع سلولی در مایکروسافت اکسل نسبی هستند. به یاد داشته باشید که مرجع سلول اساساً یک آدرس سلول است، مانند نحوه رسیدن به سلول (به عنوان مثال، A2). وقتی از A2 در فرمول استفاده می کنید، یک مرجع نسبت به سطر و ستون است. اگر فرمول را به سطر یا ستون دیگری منتقل کنید، مرجع نسبت به سلول در سطر یا ستون جدید تغییر می کند.
تصور کنید فهرستی از ارقام فروش در ستون A دارید و می خواهید برای هر رقم فروش 10 درصد کمیسیون در ستون B درج کنید. می توانید از فرمول [=A2*0.1] در سلول B2 استفاده کنید و فرمول را در کل ستون B به پایین بکشید.
[=A2*0.1]
اکسل به طور خودکار مراجع را به A3، A4 و غیره تنظیم می کند.
اگر به جای آن فرمول را به سمت راست گسترش دهید، مرجع نسبت به مکان جدید تغییر خواهد کرد. به جای A2 در فرمول [=A2*0.1]، اکسل به طور خودکار مراجع را به B2، C2 و غیره تنظیم می کند.
[=A2*0.1]
اساساً، اگر فرمولی حاوی یک مرجع سلولی (مثلاً [=A2+B2]) را از یک سلول (مثلاً C2) کپی کنید و آن را در سلول دیگری (مثلاً C3) جای‌گذاری کنید، اکسل مرجع را بر اساس موارد جدید تغییر می‌دهد. محل فرمول بنابراین، فرمول از [=A2+B2] به [=A3+B3] در سلول جدید (C3) تغییر خواهد کرد.
[=A2+B2]
[=A2+B2]
[=A3+B3]
این به ویژه زمانی مفید است که می‌خواهید بدون تنظیم دستی فرمول، عملکرد مشابهی را در بسیاری از ردیف‌ها یا ستون‌ها انجام دهید.
مراجع مطلق سلول
این نوع مرجع سلول بدون توجه به جایی که فرمول را جابجا می کنید ثابت می ماند. بهترین حالت برای زمانی است که می‌خواهید فرمول همیشه به همان سلول اشاره داشته باشد، صرف نظر از اینکه کجا استفاده می‌شود.
یک مرجع مطلق با علامت دلار ($) قبل از هر ستون و شماره ردیف (مثلاً $A$2) نشان داده می شود. اگر فقط یک علامت دلار را قبل از ستون یا ردیف قرار دهید، یک مرجع مختلط خواهد بود. به عنوان مثال، $A2 فقط ستون را ثابت می کند، در حالی که A$2 فقط ردیف را اصلاح می کند. با این حال، $A$2 ردیف و ستون را اصلاح می کند. مهم نیست فرمول را به کجا منتقل می کنید، مرجع همیشه به سلول A2 اشاره می کند.
فرض کنید یک نرخ مالیات ثابت در سلول E1 و لیستی از قیمت ها در ستون A دارید. برای محاسبه مالیات هر قیمت، می توانید از فرمول [=A2*$E$1] در سلول B2 استفاده کنید.
[=A2*$E$1]
علائم دلار تضمین می کند که هر فرمول در ستون B همیشه به D1 برای نرخ مالیات اشاره می کند، حتی زمانی که فرمول را به پایین بکشید. با کپی کردن فرمول، ارجاع به A2 تغییر می‌کند، اما ارجاع به $E$1 ثابت باقی می‌ماند.
بدون این مرجع مطلق، اکسل به سلول‌های E2، E3 و غیره اشاره می‌کند که نتایج صحیحی را ارائه نمی‌دهد.
هنگامی که با ثابت ها کار می کنید، ارجاعات سلول مطلق عالی هستند – مقادیری که تغییر نمی کنند، مانند نرخ مالیات، نرخ بهره یا هر مقدار ثابتی که می خواهید به طور مداوم در داده های خود اعمال شود.
مراجع ترکیبی سلولی
مرجع سلولی ترکیبی ترکیبی از مرجع نسبی و مطلق است. به یاد داشته باشید که هر مرجع سلول دارای دو بخش است – سطر و ستون سلول. در یک مرجع سلولی مختلط، یک بخش نسبی و دیگری مطلق است. به این معنی که ستون می تواند ثابت شود در حالی که ردیف نسبی باشد یا برعکس.
همانطور که در مثال بالا، $A2 ستون را ثابت می کند و ردیف را نسبی نگه می دارد، در حالی که A$2 ردیف را ثابت می کند و ستون را نسبی نگه می دارد.
فرض کنید می خواهید درصدهای متفاوتی از ارقام مختلف فروش را در اکسل بدست آورید. برای سرصفحه های ردیف خود، می توانید ارقام فروش را قرار دهید. سپس، درصد کمیسیون را به عنوان سرفصل ستون خود قرار می دهید. هدف هر سلول این است که مقدار سطر مربوطه آن را در مقدار ستون ضرب کند.
در سلول اول (در مورد من B3)، می توانید از این فرمول استفاده کنید:
=$A3*B$2
برای قسمت اول فرمول، ستون A ثابت است. به این ترتیب، وقتی فرمول را در ستون‌ها کپی می‌کنید، همیشه به مقادیر ستون A اشاره می‌کند. با این حال، شماره ردیف که ثابت نیست، همیشه بر اساس ردیفی که فرمول در آن کپی شده است تغییر می‌کند.
برای قسمت دوم فرمول، ردیف 2 ثابت است. در نتیجه، وقتی فرمول را در ردیف‌های پایین کپی می‌کنید، همیشه به مقادیر ردیف 2 اشاره می‌کند. با این حال، حرف ستونی که ثابت نیست، بسته به ستونی که فرمول در آن کپی شده است، تنظیم می‌شود.
اگر روی Formulas > Show Formulas کلیک کنید، می‌توانید ببینید که چگونه فرمول در سلول‌ها تغییر کرده است.
با مراجع ترکیبی سلول، می توانید یک نقطه مرجع ثابت برای یک سطر یا ستون حفظ کنید و در عین حال اجازه دهید دیگری تغییر کند.