آشنایی با انواع آدرس‌دهی در اکسل
آشنایی با انواع آدرس‌دهی در اکسل به دلیل اهمیت فوق‌العاده زیادی که این مفهوم در این نرم‌افزار پرکاربرد دارد برای هر کاربر اکسل ضروری است. برای اینکه هرگونه محاسبه یا تغییراتی روی داده‌های موجود در فایل اکسل انجام دهید باید به سلول مورد نظر دسترسی داشته باشید و آن را فراخوانی کنید. به همین دلیل درک نحوه آدرس‌دهی و کاربرد آن یکی از اصول اساسی است که هر متخصص اکسل باید آن را به خوبی یاد بگیرد. قبل از اینکه بخواهیم شما را با انواع آدرس‌دهی آشنا کنیم باید این نکته را بگوییم که همه آنها به دو دسته کلی نسبی و مطلق تقسیم می‌شوند. آدرس نسبی هنگام کپی شدن فرمول در سلول دیگر دچار تغییر می‌شود اما آدرس دهی مطلق ثابت می‌ماند. در ادامه سعی می‌کنیم انواع آدرس دهی‌های اکسل را بررسی کنیم تا این تفاوت‌ها بهتر مشخص شوند.
آدرس دهی نسبی
در میان انواع آدرس‌دهی در اکسل، آدرس دهی نسبی بیشتر مورد استفاده قرار می‌گیرد. در واقع آدرس دهی نسبی حالت پیش فرض اکسل است و اگر آن را تغییر ندهید تمام آدرس‌دهی‌ها به این روش انجام می‌شود. وقتی آنها را در چند سلول کپی می‌کنید، براساس موقعیت نسبی ردیف و ستون‌ها ممکن است تغییر کنند. به عنوان نمونه اگر فرمول =A1+B1 را از ردیف ۱ به ۲ انتقال دهید کل فرمول نیز بر اساس A2 و B2 محاسبه خواهد شد. آدرس دهی نسبی زمانی کاربرد دارد که نیاز به تکرار محاسبه یکسان در چند ردیف یا ستون داشته باشید.
مثالی از آدرس دهی نسبی
برای درک بهتر انواع آدرس‌دهی در اکسل می‌خواهیم یک مثال از آدرس دهی نسبی با هم بررسی کنیم. در این مثال می‌خواهیم فرمولی به وجود آوریم که قیمت هر کالا را در تعداد آن ضرب کرده و جمع کل را به ما نشان دهد. به جای اینکه فرمول جدیدی برای هر سطر به وجود آوریم می‌توانیم یک فرمول مشخص را در سلول D2 ایجاد کرده و سپس آن را در ردیف‌های دیگر کپی کنیم. در این مرحله ما از آدرس‌دهی نسبی استفاده می‌کنیم تا فرمول به درستی مجموعه هر کالا را حساب کند. برای اینکه این مثال را بهتر درک کنید در ادامه آن را با چند تصویر خدمتتان توضیح می‌دهیم:
- در تصویر بالا ما سلولی را انتخاب کرده‌ایم که حاوی فرمول است.در واقع همونطور که قبلاً هم گفتیم، سلول D2 شامل فرمول است و ما آن را انتخاب می‌کنیم.
- در تصویر بالا همانطور که می‌بینید ما فرمول محاسبه جمع کل را با عبارتB2*C2 درج می‌کنیم. این فرمول دقیقاً باید در ستون مربوطه درج شود تا نتیجه درست حاصل گردد.
- اکنون می‌توانید کلید اینتر را بزنید تا نتیجه را مشاهده کنید.مشاهده خواهید کرد که محاسبات انجام شده و نتیجه برای اولین سطر خیلی راحت درج می‌شود.
- حالا مثل تصویر بالا،گوشه کادر مربوط به فرمول را پیدا کنید و آن را به سمت پایین تا آخرین سطر بکشید. در حال حاضر فرمول مد نظر شما برای تمام ستون‌ها تکرار می‌شود و نیاز نیست که آن را مجدداً در آنها درج کنید. یعنی به صورت اتوماتیک نتایج محاسبه و درج می‌شوند.
- در گام آخر کافی استماوس را رها کنید تا نتایج به شما نشان داده شوند. مشاهده می‌کنید که بدون تکرار فرمول در دیگر سطرها، جمع کل برای تمام سطرها محاسبه شده است بدون آنکه ارقام تکراری باشند.
آشنایی با آدرس دهی مطلق
در ادامه بررسی انواع آدرس‌دهی در اکسل، سراغ آدرس دهی مطلق می‌رویم. با وجود اینکه در اکثر موارد ما ترجیح می‌دهیم از آدرس دهی نسبی استفاده کنیم و آن را تغییر ندهیم اما گاهی اوقات هم لازم است فرمول‌های متفاوتی برای سلول‌های گوناگون در نظر بگیریم. در این شرایط سراغ آدرس دهی‌های مطلق می‌رویم که نمی‌توانیم آنها را برای تمام سطرهای یک ستون اعمال کرده و انتظار نتایج متفاوت داشته باشیم. معمولا از این روش برای ثابت نگه داشتن یک ردیف یا ستون در فایل اکسل استفاده می‌شود. برای آدرس دهی مطلق در یک فایل اکسل معمولاً علامت دلار قبل از فرمول مورد استفاده قرار می‌گیرد. اگر برای فقط ستون یا فقط ردیف از این نماد استفاده کنیم آنگاه آدرس دهی ترکیبی داشته‌ایم که بعداً به آن می‌پردازیم.
چگونه آدرس دهی مطلق را انتخاب کنیم؟
طبیعتاً بعد از شناخت انواع آدرس‌دهی در اکسل اینجا باید به این مورد اشاره کنیم که چگونه می‌توان آدرس دهی پیش فرض را به مطلق یا ترکیبی تغییر داد. البته همانطور که قبلاً هم گفتیم آدرس دهی پیش فرض، همان آدرس دهی نسبی است اما اگر بخواهید آن را تغییر دهید باید کلید F4 را فشار دهید. اکنون می‌توانید بین آدرس دهی‌های نسبی، مطلق و ترکیبی یکی را انتخاب کنید.
مثالی از آدرس دهی مطلق
تا اینجا سعی کردیم نکات مختلفی را در مورد انواع آدرس دهی در اکسل با هم مورد بررسی قرار دهیم. برای درک بهتر آدرس دهی مطلق می‌خواهیم یک مثال جذاب را با هم بررسی کنیم. در این مثال قصد داریم نرخ مالیات فروش ۷.۵٪ در سلول E1 را برای محاسبه مالیات فروش همه اقلام ستون D مورد استفاده قرار دهیم. در این مرحله باید از آدرس دهی مطلق $E$1 در فرمول خود استفاده کنیم. در ادامه این مثال را به طور مفصل با هم بررسی می‌کنیم:
- مثل عکس بالا ابتدا سلولی که می‌خواهید فرمول را در آن قرار دهید انتخاب کنید.همانطور که مشاهده می‌کنید در مثال ما سلول D3 انتخاب شده است.
- در گام بعدی فرمول محاسبه مقدار مورد نظر را در ردیفی که انتخاب کرده‌اید وارد کنید.در مثال ما فرمول =(B3*C3)*$E$1 درج شده است.
- بعد از اینکه فرمول را درج کردید کافی است روی کلید اینتر ضربه بزنید تا فرمول محاسبه شده و نتیجه در سلول نمایش داده شود.حالا طبق تصویر بالا کادر سبز رنگی که اطراف محل درج فرمول مشاهده می‌شود را پیدا کرده و آن را به سمت پایین بکشید.
- اکنون مشاهده خواهید کرد که فرمول با یک ارجاع مطلق در سلول‌های مختلف کپی شده و مقادیر هر سلول به طور دقیق محاسبه شده‌اند.
- برای اینکه تفاوت بین آدرس دهی مطلق و نسبی را بهتر درک کنید پیشنهاد می‌کنیم روی سلول‌هایی که اخیراً پر شده‌اند دوبار کلیک کنید تا فرمول‌های آنها را مشاهده نمایید.مشاهده خواهید کرد که به جز نماد مطلق، بقیه نمادها بر اساس هر ردیف متفاوت هستند.
آدرس دهی ترکیبی در اکسل
در ادامه بررسی انواع آدرس دهی در اکسل سراغ آدرس دهی ترکیبی در اکسل می‌رویم. در آدرس دهی ترکیبی به طور همزمان از آدرس دهی نسبی و مطلق در اکسل استفاده می‌شود. به عنوان نمونه می‌توانیم آدرس دهی $A1 یا A$1 را مثال بزنیم. این آدرس این زمان کاربرد دارد که شما بخواهید فقط یکی از مختصات خودتان مثل ستون یا ردیف را ثابت نگه دارید اما بقیه را متغیر در نظر بگیرید. مثلاً زمانی که می‌خواهید ک ستون از اعداد را در سه عدد مختلف ضرب کنید می‌توانید از فرمول =$A3*B$2 کمک بگیرید. در فرمول گفته شده $A3 مختصات ستون را حفظ می‌کند زیرا فرمول همیشه باید اعداد اصلی را در ستون A ضرب کند. در B$2 اما مختصات ردیف حفظ می‌شود تا اکسل بفهمد همیشه ضریب را در ردیف ۲ انتخاب کند. در واقع تمام محاسبات با یک فرمول واحد انجام می‌پذیرد تا برای هر ردیف و ستونی که کپی می‌شود تغییرات به درستی اعمال شوند.
استفاده از آدرس دهی در اکسل برای چندین کاربرگ
یکی از کاربردهای مهمی که انواع آدرس‌دهی در اکسل دارند، امکان استفاده از آنها برای چندین کاربرگ مختلف است. با استفاده از این کاربرد جذاب اکسل می‌توانید آدرس یک مقدار خاص را در یک کاربرگ به کاربرگ دیگری ارجاع دهید. برای این کار تنها اقدامی که باید انجام دهید نوشتن فرمول با نام کاربرگ و استفاده از علامت ! در آن است. مثلاً اگر می‌خواهید به سلول A1 در Sheet1 آدرس دهی کنید کافی است فرمول آن را به شکل Sheet1!A1 بنویسید. دقت داشته باشید که در این حالت اگر نام کاربرگ فاصله دارد باید حتماً این عبارت را ‘ ‘ در آن به کار ببرید. مثلاً بنویسید ‘July Budget’!A1.
مثالی برای آدرس دهی بین کاربرگ‌های مختلف
اگر می‌خواهید از انواع آدرس دهی در اکسل برای آدرس دهی بین چندین کاربرگ استفاده کنید بهتر است به این مثال توجه نمایید. متال زیر به شما کمک می‌کند تا درک بهتری از این کار داشته باشید:
- ابتدا سلولی که قرار است به آن آدرس دهی کنید را پیدا کرده و کاربرگ آن را یادداشت کنید. مثلاً ما در مثال این تصویر می‌خواهیم بهسلول E14 در کاربرگ Menu Order worksheet آدرس دهی کنیم.
- حال به ورک‌شیت مورد نظر می‌رویم و آن را مشاهده می‌کنیم.همانطور که در تصویر می‌بینید کاربرگ مورد نظر ما فاکتور پذیرایی است.
- اکنون که کاربرگ ظاهر شده می‌توانید سلول مورد نظر خودتان را پیدا کرده و آن را انتخاب کنید.مثلاً در تصویر بالا ما ترجیح داده‌ایم سلول B2 را برای این هدف انتخاب کنیم.
- همانطور که در تصویر مشاهده می‌کنید در فرمولمورد نظر باید علامت مساوی به عنوان نام شیت و ! و آدرس سلول تایپ شوند. در مثال ما همانطور که مشاهده می‌کنید =’Menu Order’!E14 تایپ شده است.
- در گام آخر کافی است کلید اینتر را از روی صفحه کلید فشار دهید و مقدار سلول آدرس‌دهی شده را مشاهده کنید.اگر مقدار سلول E14 در منو سفارش تغییر کند، به طور خودکار در برگه فاکتور پذیرایی هم به‌روزرسانی می‌شود. در واقع این مدل آدرس دهی در اکسل به شما کمک می‌کند تا مجبور نباشید مقادیر مختلف را در تمام صفحات مدام تغییر دهید و هم وقت خودتان را کمتر تلف می‌کنید و هم احتمال بروز خطا را کاهش می‌دهید.
آدرس دهی سه بعدی در اکسل چگونه است؟
یکی از متفاوت‌ترین انواع آدرس‌دهی در اکسل که اخیراً مورد توجه قرار گرفته و جذابیت‌های زیادی هم دارد آدرس‌دهی سه بعدی است. اگر بخواهید مثلاً آدرس‌دهی سه بعدی میانگین را انجام دهید، بهتر است مراحل زیر را دنبال کنید:
- در اولین گام لازم است فرمول ساده AVERAGE را مثل همیشه در یک سلول خیلی عادی تایپ کنید.
- اکنون باید روی تب اولین کاربرگ کلیک کنیدتا در آدرس سه بعدی قرار گیرد. در گام بعدی کلید شیفت را نگه دارید و روی تب آخرین کاربرگ کلیک نمایید.
- محدوده‌ای از سلول‌ها که قرار است در این محاسبه مورد استفاده قرار گیرند را انتخاب کنید.اکنون می‌توانید تایپ فرمول را تمام کرده و برای تکمیل کلید اینتر را فشار دهید.
چگونه بین انواع آدرس‌دهی در اکسل جابجا شویم؟
اگر می‌خواهید بین انواع آدرس‌دهی در اکسل جابجا شوید باید علامت دلار را به صورت دستی تایپ کرده یا حذف نمایید و یا از میانبر F4 استفاده کنید. نحوه استفاده از این روش‌ها را در ادامه با هم بررسی می‌کنیم:
- در ابتدا روی سلولی که حاوی فرمول است دو بار کلیک کنید و آدرسی که می‌خواهید تغییر دهید را انتخاب نمایید.
- برای تغییر بین هر کدام از انواع آدرس می‌توانید کلید F4 را فشار دهید. با فشار دادن مکرر این کلید می‌توانید آدرس‌ها را به ترتیب تغییر دهید.
در صورت تغییر نام کاربرگ‌ها، چگونه تنظیمات آدرس‌دهی را به روز رسانی کنیم؟
در صورتی که نام‌های مختلف را در اکسل تغییر دادید طبیعتاً شاید بخواهید تنظیمات انواع آدرس دهی در اکسل را هم به‌روزرسانی نمایید. برای این کار کافی است نکات زیر را رعایت کنید:
- ابتدا سلول‌هایی که فرمول‌هایی در آنها نوشته شده را پیدا کنید و آدرس‌دهی سلول‌ها را به نام‌های جدید تغییر دهید.
- در گام بعدی جایگزینی آدرس‌ها با نام‌های جدید در تمام فرمول‌های صفحه می‌توانید از سلول‌های خالی استفاده کنید.
- برای اینکه تنظیمات جدید اعمال شوند کافی است به برگه فرمول‌ها و گروه نام‌ها در آن بروید و روی فلش کنار نام‌های تعریف شده کلیک کرده و نام‌های جدید را اعمال کنید.
- برای اینکه مطمئن شوید تنظیمات جدید اعمال شده‌اند کافی است چند سطر یا ستون را بررسی کنید و از تغییر اطلاعات آنها مطمئن شوید.
جمع‌بندی
آشنایی با انواع آدرس‌دهی در اکسل حتی برای افرادی که تجربه زیادی از کار با این ابزار قدرتمند تحلیلگری دارند، جالب و جذاب است. اکنون می‌دانیم که در اکسل انواع مختلفی از آدرس‌دهی وجود دارند و هر کدام از آنها می‌توانند در شرایط خاصی به کمک ما بیایند. مهم این است که براساس شرایط پروژه و هدفی که از کار روی اکسل دنبال می‌کنیم یکی از این آدرس‌دهی‌ها را انتخاب نماییم.
