انواع آدرس دهی در اکسل – به زبان ساده + آدرس دهی نسبی، مطلق و ترکیبی
آدرسدهی یا ارجاع در اکسل مشخص کردن محل دقیق یک یا چند سلول به روشهای مختلف با هدف سادهسازی فرمولنویسی و مدیریت دادهها در جدول است. بهعنوان مثال با استفاده از انواع آدرس دهی در اکسل میتوانیم فرمول را با قفل کردن یک سطر یا ستون بهراحتی در دیگر سلولها کپی کنیم، بدون اینکه آن سطر یا ستون تغییر کند. همچنین آدرسدهی به یک نام کوتاه دلخواه، جدول یا کاربرگ دیگر در زمان فرمولنویسی نیز به سادهتر کردن فرمول کمک میکند. برای آشنایی بیشتر با نحوه انجام کار در این مطلب از مجله فرادرس بعد از بررسی سه سبک نامگذاری و مشخص کردن محل سلول، شش نوع مختلف آدرسدهی در اکسل را همراه مثال یاد میگیریم.
- سه سبک آدرسدهی در اکسل را یاد خواهید گرفت.
- روش آدرسدهی نسبی، مطلق و ترکیبی را همراه مثال یاد خواهید گرفت.
- با روش آدرسدهی به یک فایل و کاربرگ دیگر آشنا خواهید شد.
- روش آدرسدهی به یک جدول را همراه مثال یاد خواهید گرفت.
انواع سبک های آدرس دهی در اکسل
«انواع یا سبک های آدرس دهی» (Cell Reference Style) در اکسل به معنی نحوه نوشتن موقعیت سلول است. در تعریف ساده، سبک آدرسدهی بهعنوان یک ابزار کاربردی در اکسل مانند مشخص کردن نشانی خانههای جدول اکسل یا همان سلولها بیان میشود. در اکسل سه سبک مختلف آدرسدهی به شرح زیر وجود دارد.
- سبک آدرسدهی A1
- سبک آدرسدهی R1C1
- سبک آدرسدهی با نامگذاری
هر یک از این سبکها کاربرد مخصوص خود را در سادهتر کردن نامگذاری سلول هنگام فرمولنویسی دارند.
سبک آدرسدهی A1
این سبک، حالت پیشفرض نرمافزار اکسل و رایجترین روش است که در آن نشانی یک سلول با نام ستون و شماره ردیف آن نمایش داده میشود.
همچنین برای نوشتن نشانی دو یا چند سلول کنار یکدیگر در جدول اکسل، آدرس اولین سلول بالایی سمت چپ و آخرین سلول پایینی سمت راست را همراه علامت «:» مینویسیم. بهعنوان مثال محدوده A1:C2 در جدول زیر شامل ۶ سلول تک از A1 تا C2 است.
سبک آدرسدهی R1C1
بهجز حالت پیشفرض آدرس دهی سلول، سبک دیگری نیز به نام «R1C1» نیز وجود دارد که در آن سطرها و ستونها با اعداد مشخص میشوند. بهعنوان مثال، عبارت «R2C1» یعنی سلولی که در «ردیف» (Row) دوم و «ستون» (Column) اول قرار دارد. از سبک آدرسدهی «R1C1» بیشتر برای نوشتن فرمولهای دینامیک، برنامهنویسی با اکسل یا در جدولهای بسیار بزرگ و پیچیده استفاده میکنیم. در این سبک آدرسدهی نحوه نمایش جدول اکسل متفاوت است و همانطور که در تصویر زیر میبینیم، ردیف و ستون در جدول اکسل هر دو عدد هستند و نیاز به تنظیمات دارد.
برای تغییر وضعیت از حالت پیشفرض به سبک R1C1 از زبانه «File» در نوار بالایی اکسل، روی بخش «Options» کلیک میکنیم. سپس در پنجره باز شده از قسمت «Formula» باکس «R1C1 reference style» را تیک میزنیم.
سبک آدرسدهی با نامگذاری
در این سبک به جای استفاده از روشهای آدرسدهی معمول در فرمول با حروف و اعداد مانند =SUM(A2:A5) ، برای سلول یک نام دلخواه تعیین میکنیم. به این شکل با نوشتن عبارتی مانند =SUM(مجموع) خوانایی فرمول بهخصوص در موارد پیچیده راحتتر خواهد بود. برای این کار بهعنوان مثال مراحل زیر را انجام میدهیم.
۱. محدوده سلول مورد نظر (A2:A5) را انتخاب میکنیم. سپس در زبانه «Formula» روی بخش «Define name» کلیک میکنیم.
۳. بعد از کلیک روی دکمه آبشاری، در پنجره باز شده، بخش «Name» نام دلخواه خود (مجموع) را مینویسیم و دکمه OK را میزنیم.
۴. در این حالت کافی است برای نوشتن فرمول مورد نظر خود (به عنوان مثال SUM)، داخل تابع بهجای تایپ A2:A5 کلمه «مجموع» را بنویسیم. در این حالت اکسل به صورت خودکار نام را شناسایی میکند.
البته برای جلوگیری از پیام خطا در فرمولنویسی، اگر در نام مورد نظر فاصله وجود داشته باشد، حتما بین آنها خط تیره میگذاریم. بهعنوان مثال بهجای عبارت «مجموع فروش» از عبارت «مجموع-فروش» استفاده میکنیم.
انواع آدرس دهی در اکسل
علاوه بر سبکهای آدرس دهی در اکسل که پایه اصلی نامگذاری سلولها با اعداد یا حروف هستند، شش نوع مختلف آدرسدهی (Cell Reference Type) به شرح زیر برای مدیریت دادهها در فرمولنویسی وجود دارد. از این میان سه مورد اول متداولتر هستند و در بیشتر مراجع بهعنوان انواع آدرس دهی در اکسل بهآنها اشاره میشود. اما سه نوع دیگر آدرسدهی یا ارجاع پیشرفتهتر هم وجود دارد که برای یادگیری تکمیلی آنها را نیز توضیح میدهیم.
۱. آدرسدهی پایه شامل موارد زیر است:
- آدرسدهی نسبی
- آدرسدهی مطلق
- آدرسدهی ترکیبی
۲. آدرسدهی پیشرفته نیز به انواع زیر تقسیم میشود:
- آدرسدهی به کاربرگ دیگر
- آدرسدهی به فایل دیگر
- آدرسدهی به جدول
یادگیری انواع آدرس دهی در اکسل موقع کپی کردن فرمول در سلولهای دیگر اهمیت دارد و بهخصوص محاسبات در تعداد داده زیاد جدول را بسیار سادهتر میکند. در ادامه بحث هر یک از این روشها را همراه مثال توضیح میدهیم. با این حال برای تکمیل مسیر یادگیری، پیشنهاد میکنیم فیلم آموزش استفاده از توابع و فرمولنویسی اکسل در فرادرس را نیز با تمرکز بر درس اول آن مشاهده کنید.
روشهای پایه آدرس دهی در اکسل
آدرسدهی نسبی، مطلق و ترکیبی متداولترین انواع آدرس دهی در اکسل یا ارجاع سلولها در فرمولنویسی هستند که هر یک کاربرد مخصوص خود را دارند.
آدرسدهی نسبی
آدرسدهی نسبی یکی از سادهترین انواع آدرس دهی در اکسل است که در آن محل سلول را با نام ستون و شماره ردیف در فرمول مشخص میکنیم. اگر فرمولنویسی را با روش آدرسدهی نسبی انجام دهیم، موقع کپی کردن یا جابجایی فرمول در محلهای دیگر، اکسل آدرس سلول را متناسب با محل جدید تغییر میدهد.
مثال
فرض میکنیم قیمت واحد چند کالای مختلف را مطابق جدول زیر در اختیار داریم. حال اگر بخواهیم قیمت کل هر یک را به صورت جداگانه بهدست آوریم، کافی است فرمول =B2*C2 را در ردیفهای پایین آن کپی کنیم. بنابراین فرمول بهصورت خودکار با توجه به شماره سطر و نام ستون بعدی تغییر میکند.
آدرسدهی نسبی برای تکرار محاسبه یکسان در چند ردیف یا چند ستون از جدول بهترین انتخاب است.
آدرسدهی مطلق
در روش آدرسدهی مطلق، جلوی نام ستون یا شماره سطر علامت «$» قرار میدهیم. به این شکل موقع کپی کردن فرمول در سلولهای دیگر هیچ تغییری در نام ستون یا شماره سطر ایجاد نمیشود. بنابراین بسته به اینکه میخواهیم چه چیزی بدون تغییر باقی بماند، آن را داخل علامت «$» قرار میدهیم. بهعنوان مثال سه حالت زیر برای سلول A1 ممکن است اتفاق بیفتد.
- A$1$ : سطر و ستون هر دو ثابت هستند و اگر فرمول را کپی کنیم، هیچ کدام تغییری نمیکنند.
- A1$ : ستون A ثابت است، اما سطر اول با کپی کردن یا جابجایی فرمول تغییر میکند.
- A$1 : سطر اول ثابت است، اما ستون A با کپی کردن یا جابجایی فرمول تغییر میکند.
مثال
فرض میکنیم مطابق جدول زیر متراژهای مختلفی از یک پارچه با قیمت واحد ثابت را خریداری کردهایم. حال اگر بخواهیم هزینه کل هر یک را بهدست آوریم، کافیاست فرمول اصلی را بهشکل =A2*$B$7 بنویسیم و سپس آن را در سلولهای دیگر کپی کنیم.در این حالت مقدار B7 در کل ردیفها ثابت باقی میماند.
از آدرسدهی مطلق زمانی استفاده میکنیم که بخواهیم بدون هیچ تغییری فقط با یک سلول مشخص محاسبه را انجام دهیم. محاسبه مالیات ارزش افزوده یک کالا با نرخ ثابت یا تعیین مقدار سود بانکی با یک مقدار ثابت سالانه نمونههای کاربردی استفاده از آدرسدهی مطلق در فرمولنویسی اکسل هستند.
آدرسدهی ترکیبی
در مواقعی که میخواهیم فقط مشخصات یک ستون یا یک ردیف تغییری نکنند، از روش آدرسدهی ترکیبی استفاده میکنیم. در این حالت یکی از ستونها یا ردیفها را با نوشتن علامت «$» و آدرسدهی مطلق قفل میکنیم و ردیف یا ستون بدون تغییر را با آدرسدهی نسبی مینویسیم.
مثال
در جدول فرضی زیر میخواهیم اعداد ستون A را در سه ضریب متفاوت نوشته شده در سلولهای C2، B2 و D2 ضرب کنیم. برای انجام این کار در سلول B3 فرمول =$A3*B$2 را مینویسیم، سپس آن را در سلولهای دیگر کپی میکنیم.
در این حالت، فرمول برای ستون B به شکل زیر عمل میکند:
- با نوشتن $A3 مشخصات ستون A را قفل میکنیم. چون فرمول باید همیشه اعداد اصلی ستون A را ضرب کند. اما شماره ردیف (عدد ۳) نسبی است، چون در هر ردیف تغییر میکند.
- با نوشتن B$2 مشخصات ردیف را قفل میکنیم تا اکسل همیشه ضریب نوشته شده در ردیف دوم را انتخاب کند. اما مشخصات ستون B نسبی است. چون ضرایب در سه ستون مختلف قرار دارند و فرمول متناسب با آنها تغییر میکند.
به این شکل همه محاسبات فقط با یک فرمول انجام میشود و بعد از کپی کردن در ردیفها و ستونهای دیگر بهدرستی تغییر میکنند. در مطلب زیر از مجله فرادرس نکات تکمیلی را توضیح دادهایم که در صورت علاقهمندی به مطالعه مثالهای بیشتر، میتوانید آن را بررسی کنید:
یادگیری ترفندهای حرفهای فرمولنویسی اکسل همراه با فرادرس
آدرس دهی در اکسل یکی از ترفندها برای سادهتر کردن محاسبات و فرمولنویسی به خصوص در تعداد دادههای زیاد است. ترفندهای دیگری نیز در این اکسل وجود دارند که با یادگیری آنها علاوه بر کمتر کردن اشتباهات و پیامهای خطا، میتوانیم در وقت خود هم صرفهجویی کنیم. در این مسیر مجموعه فیلمهای آموزشی تهیه شده در فرادرس با توجه به تنوع بالای خود روشی میانبر برای علاقهمندان هستند.
پیشنهاد اول برای یادگیری تکمیلی از سطح مقدماتی تا پیشرفته فیلمهای منتخب آموزشی زیر هستند.
- فیلم آموزش ترفندهای کاربردی اکسل همراه گواهینامه در فرادرس
- فیلم آموزش ابزارهای کاربردی اکسل همراه گواهینامه در فرادرس
- فیلم آموزش استفاده از توابع و فرمولنویسی در اکسل همراه گواهینامه در فرادرس
- فیلم آموزش رایگان آدرسدهی نسبی و مطلق در فرادرس
همچنین در مجموعه فیلم آموزش زیر امکان انتخاب بیشتری با توجه به علاقه و نیاز وجود دارد.
روشهای پیشرفته آدرس دهی سلول در اکسل
بهجز موارد متداول آدرس دهی در اکسل، سه روش خاص نیز وجود دارند که بیشتر با نام ارجاع سلول در مراجع به آنها اشاره میشود. با این حال هر یک از این موارد نیز نوعی آدرسدهی پیشرفته هستند که بهخصوص در فرمولنویسیهای پیچیده کار را سادهتر میکنند.
آدرسدهی به یک کاربرگ دیگر
برای آدرسدهی به یک سلول یا محدودهای از سلولها در کاربرگ دیگر در همان فایل اکسل، نام کاربرگ هدف را قبل از آدرس سلول با علامت «!» مینویسیم. بهعنوان مثال برای آدرسدهی سلول A1 در کاربرگ با نام «Sheet2» عبارت =Sheet2!A1 را در سلول دلخواه خود تایپ میکنیم.
اگر نام کاربرگ، فاصله یا کاراکترهای غیر حرفی داشته باشد، برای جلوگیری از پیام خطا، لازم است نام کاربرگ را داخل علامت تک کوتیشن ' ' بنویسیم. بهعنوان مثال آدرسدهی سلول A1 به کاربرگی با نام فرضی «Target Sheet» به شکل ='Target sheet'!A1 خواهد بود.
البته همه این مراحل به شکل خودکار در اکسل قابل انجام است و برای جلوگیری از اشتباه میتوانیم بدون تایپ دستی نیز مراحل را مطابق مثال زیر انجام دهیم.
مثال
میخواهیم نتیجه محاسبه میانگین دو عدد در کاربرگ با نام «Sheet2» را در کاربرگ اول نمایش دهیم. برای اینکار مراحل زیر را انجام میدهیم.
۱. در سلول دلخواه از کاربرگ اول «Sheet1»، ابتدای فرمول را به شکل =AVERAGE( مینویسیم.
۲. برای آدرسدهی به اعداد مورد نظر در کاربرگ دوم، به «Sheet2» میرویم و محدوده سلول مورد نظر برای محاسبه را انتخاب میکنیم. با این کار اکسل بهصورت خودکار فرمول =AVERAGE(Sheet2!A3:B3 را مینویسد.
۳. با زدن دکمه «Enter» در کاربرگ دوم، نتیجه در کاربرگ اول نمایش داده میشود.
برای یادگیری کامل کار با تابع AVERAGE پیشنهاد میکنیم فیلم آموزش رایگان محاسبه میانگین در اکسل در فرادرس را مشاهده کنید.
آدرسدهی به یک فایل دیگر
در این حالت یک سلول یا محدودهای از سلولها را به یک فایل اکسل یا کارپوشه (Workbook) دیگر آدرسدهی میکنیم.. برای این کار ابتدا نام کارپوشه را داخل علامت کروشه «[ ]» قرار میدهیم. سپس نام کاربرگ را میآوریم ، بعد از آن علامت تعجب «!» و در نهایت آدرس سلول را مینویسیم. ساختار کلی برای سلول فرضی A1 به شکل =[Book1.xlsx]Sheet1!A1 است.
اگر نام فایل یا کاربرگ کاراکتر غیر حرفی داشته باشند، لازم است هر یک را داخل علامت تک کوتیشن ' ' مانند ='[Target file.xlsx]Sheet1'!A1 بنویسیم.
برای یادگیری تکمیلی و بررسی مثال در این نوع آدرسدهی پیشنهاد میکنیم فیلم آموزش رایگان استفاده از علامت تعجب در فرمولنویسی اکسل در فرادرس را نیز مشاهده کنید.
آدرسدهی به یک جدول
در این نوع آدرسدهی، نام یک جدول اکسل را بهجای سلول در فرمول مینویسیم. برای استفاده از این روش حتما لازم است از بخش «Insert» یک جدول رسم کنیم. زیرا این مدل آدرسدهی با جدول دادههای اکسل قابل استفاده نیست.
مثال
بهعنوان مثال برای محاسبه میانگین مبلغهای «فروش» از جدول تصویر زیر با نام «Table1» فرمول =AVERAGE(Table1[فروش]) را مینویسیم.
در صورت علاقهمندی به مطالعه بیشتر و ترفندهای تکمیلی انواع آدرسدهی اکسل در مطلب زیر مثالهای بیشتری را توضیح دادهایم.
جمعبندی پایانی
آدرس دهی در اکسل یکی از ابزارهای ساده کردن کار با فرمولها و انجام محاسبات است. با این ابزار میتوانیم موقعیت مکانی یک سلول یا محدودهای از سلولها در جدول اکسل را به انواع روشهای مختلف مانند نسبی، مطلق یا ترکیبی و سبکهای A1 یا R1C1 مشخص کرد. به این شکل فرمولها خواناتر و انعطافپذیرتر نوشته میشوند و دادههای جدول اکسل بهخصوص در تعداد زیاد با کمترین خطا مدیریت میشوند. در این مطلب از مجله فرادرس سبکها و انواع مختلف آدرسدهی را همراه مثال یاد گرفتیم که پایهای برای مسیر حرفهای فرمولنویسی با اکسل است. برای یادگیری نکات بیشتر در سطح پیشرفته علاقهمندان میتوانند به مجموعه فیلم آموزش استفاده از توابع و فرمول اکسل در فرادرس مراجعه کنند.
