آدرس دهی در اکسل

آدرس دهی در اکسل یکی از مهارت‌های اساسی برای استفاده مؤثر از این نرم‌افزار قدرتمند است. این مفهوم اگرچه در ابتدا ساده به نظر می‌رسد؛ اما برای بسیاری از افراد گیج‌کننده است. درک صحیح نحوه آدرس‌دهی به سلول‌ها و محدوده‌ها می‌تواند کارایی شما را در ایجاد فرمول نویسی در اکسل و توابع افزایش دهد و از بروز خطاهای رایج جلوگیری کند. در این مقاله به بررسی دقیق انواع آدرس‌دهی مطلق و نسبی در اکسل خواهیم پرداخت.

مفاهیم پایه آدرس دهی در اکسل

قبل‌از ورود به جزئیات آدرس‌دهی نسبی و مطلق در اکسل، باید با مفاهیم پایه آدرس دهی در اکسل آشنا شوید. هر سلول در اکسل دارای یک آدرس منحصربه‌فرد است که از ترکیب حروف (نشان‌دهنده ستون) و اعداد (نشان‌دهنده سطر) تشکیل شده است. برای مثال، A1 به سلول در تقاطع ستون A و سطر ۱ اشاره دارد؛ B2 به سلول دوم در ستون B اشاره دارد و ….

آدرس دهی در اکسل، هنگامی‌ که در یک فرمول استفاده می‌شود، به اکسل کمک می‌کند تا مقادیری را که فرمول باید محاسبه کند، پیدا کند. به‌عنوان‌مثال، برای آوردن مقدار A1 به سلول دیگر می‌توانید از فرمول ساده زیر استفاده کنید:

=A1

برای جمع‌کردن مقادیر در سلول‌های A1 و A2، از این فرمول استفاده می‌شود:

=A1+A2

در جدول زیر انواع روش های آدرس دهی و مفهوم آن را درک می کنید:

نوع آدرس‌دهی	توضیحات	مثال
آدرس‌دهی نسبی	نسبت به موقعیت فرمول در سلول تغییر می‌کند.	A1
آدرس‌دهی مطلق	موقعیت سلول ثابت بوده و هنگام کپی کردن تغییر نمی‌کند.	$A$1
آدرس‌دهی ترکیبی	ترکیبی از آدرس‌دهی نسبی و مطلق؛ یا ستون یا ردیف ثابت است.	$A1 یا A$1

در ادامه نیز انواع روش های آدرس دهی در اکسل را که در بالا توضیح داده شده است، با جزئیات مرور می کنیم.

آدرس‌دهی نسبی در اکسل

آدرس‌دهی نسبی، روش پیش‌فرض در اکسل است. در این روش، آدرس سلول‌ها نسبت به موقعیت فرمول تعریف می‌شود. وقتی یک فرمول را کپی می‌کنید یا به سلول دیگری منتقل می‌کنید، آدرس‌ها به‌طور خودکار تنظیم می‌شوند تا موقعیت نسبی خود را حفظ کنند.

فرض کنید فرمول زیر را در سلول B1 دارید:

=A1*10

اگر این فرمول را به سطر دیگری در همان ستون، مثلاً به سلول B2 کپی کنید، فرمول برای سطر ۲ تنظیم می‌شود (A2*10)؛ زیرا اکسل فرض می‌کند که می‌خواهید مقدار هر سطر از ستون A را در ۱۰ ضرب کنید.

اگر فرمول را با یک مرجع سلولی نسبی به ستون دیگری در همان سطر کپی کنید، اکسل مرجع ستون را به‌طور مناسب تغییر خواهد داد:

و اگر یک فرمول اکسل با یک مرجع سلولی نسبی را به سطر و ستون دیگری کپی یا جابه‌جا کنید، هر دو مرجع ستون و سطر تغییر خواهند کرد:

همان‌طور که مشاهده می‌کنید، استفاده از آدرس‌دهی نسبی در فرمول‌های اکسل یک روش بسیار راحت برای انجام محاسبات یکسان در کل ورک‌شیت است. برای نشان‌دادن بهتر این موضوع بیایید یک مثال واقعی را بررسی کنیم:

فرض کنید ستونی از قیمت‌های دلار آمریکا (ستون B) در ورک‌شیت خود دارید و می‌خواهید آن‌ها را به یورو تبدیل کنید. با فرض ۰.۹۳ برای نرخ تبدیل دلار آمریکا به یورو، فرمول برای سطر ۲ به صورت زیر است:

 =B2*0.93

فشاردادن کلید Enter باعث محاسبه فرمول می‌شود و نتیجه بلافاصله در سلول ظاهر می‌شود.

همان‌طور که گفته شد، آدرس دهی در اکسل به‌طور پیش فرض آدرس‌دهی نسبی است. بنابراین، هنگام نوشتن یک فرمول می‌توانید به جای تایپ دستی یک مرجع سلول با کلیک‌کردن روی سلول مربوطه در ورک‌شیت، یک مرجع نسبی اضافه کنید.

برای کپی‌کردن فرمول به پایین ستون، ماوس را روی دسته پرکردن (یک مربع کوچک در گوشه پایین سمت راست سلول انتخاب‌شده) قرار دهید. با این کار، مکان‌نما به یک ضربدر سیاه نازک تغییر می‌کند. آن را نگه دارید و روی سلول‌هایی که می‌خواهید پُر کنید، بکشید.

شما می‌توانید اطلاعات موجود در یک سلول را در سلول‌های دیگر کپی کنید. برای این کار، طبق آنچه که در آموزش اکسل برای کارهای اداری در سایت ۲۴ وب لرن گفته شده است، باید چند سلول را که قرار است داده‌ها در آن پیست شوند، انتخاب کنید. سپس در سلول آخر، اطلاعاتی را که باید کپی شوند، بیفزایید. با فشردن کلیدهای Ctrl + Enter اجازه دهید تا داده‌های متعلق به آخرین سلول کپی و در سلول‌های منتخب درج شوند.

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

مزایای آدرس‌دهی نسبی شامل موارد زیر هستند:

• انعطاف‌پذیری: به‌راحتی می‌توانید فرمول‌ها را در سراسر صفحه گسترده کپی کنید.
• سازگاری خودکار: هنگام اضافه یا حذف سطرها و ستون‌ها، فرمول‌ها به‌طور خودکار تنظیم می‌شوند.
• کاهش خطا: احتمال خطای دستی در تنظیم فرمول‌ها را کاهش می‌دهد.

قبل از شروع آموزش آدرس دهی مطلق در اکسل به نقل قولی از سایت edu.gcfglobal.org درباره آدرس دهی نسبی خواهیم پرداخت:

By default, all cell references are relative references. When copied across multiple cells, they change based on the relative position of rows and columns. For example, if you copy the formula =A1+B1 from row 1 to row 2, the formula will become =A2+B2. Relative references are especially convenient whenever you need to repeat the same calculation across multiple rows or columns.

آدرس‌دهی مطلق در اکسل

در آدرس‌دهی مطلق در اکسل، شما یک سلول یا محدوده خاص را «قفل» می‌کنید تا حتی با کپی‌کردن یا جابه‌جایی فرمول تغییر نکند. برای ایجاد یک آدرس مطلق، از علامت دلار ($) قبل‌از حرف ستون و/یا شماره سطر استفاده می‌کنید.

انواع آدرس‌دهی مطلق در اکسل شامل موارد زیر هستند:

• مطلق کامل: $A$1 (هم ستون و هم سطر ثابت هستند)؛
• مطلق ستون: $A1 (فقط ستون ثابت است)؛
• مطلق سطر: A$1 (فقط سطر ثابت است).

به‌عنوان‌مثال، اگر ۱۰ را در سلول A1 دارید و از یک مرجع سلول مطلق ($A$1) استفاده می‌کنید، فرمول =$A$1+5 همیشه ۱۵ را برمی‌گرداند؛ صرف‌نظر از اینکه این فرمول به سلول‌های دیگر کپی شود. از سوی دیگر، اگر همان فرمول را با یک مرجع سلولی نسبی (A1) بنویسید و سپس آن را به سلول‌های دیگر در ستون کپی کنید، مقدار متفاوتی برای هر سطر محاسبه می‌شود. تصویر زیر تفاوت را نشان می‌دهد:

توجه داشته باشید که اگرچه گفته‌ایم که یک مرجع مطلق در اکسل هرگز تغییر نمی‌کند، اما در واقع زمانی که سطرها و/یا ستون‌ها را در ورک‌شیت خود اضافه یا حذف می‌کنید و این کار مکان سلول مرجع‌شده را تغییر می‌دهد، تغییر می‌کند. در مثال بالا، اگر یک سطر جدید را در بالای ورک‌شیت وارد کنیم، اکسل به اندازه کافی هوشمند است که فرمول را برای انعکاس آن تغییر تنظیم کند:

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

در ادامه و قبل از ادامه مطلب آدرس دهی در اکسل پیشنهاد می شود که از مقاله آموزش تب data در اکسل دیدن فرمائید.

آدرس‌دهی نسبی و مطلق در اکسل

در بسیاری از موارد، ترکیبی از آدرس‌دهی مطلق و نسبی در اکسل می‌تواند بهترین راه‌حل باشد. این روش به شما اجازه می‌دهد از مزایای هر دو آدرس‌دهی بهره‌مند شوید.

در مثال قبلی با قیمت‌های دلار و یورو، ممکن است نخواهید نرخ ارز را به‌صورت ‌هاردکد در فرمول وارد کنید. در عوض می‌توانید آن عدد را در سلولی مانند C1 وارد کنید و با استفاده از علامت دلار ($) آن مرجع سلولی را در فرمول ثابت کنید، همان‌طور که در تصویر زیر نشان داده شده است:

در این فرمول (B4*$C$1)، دو نوع مرجع سلولی وجود دارد:

• B4- مرجع سلولی نسبی که برای هر سطر تنظیم می‌شود؛
• $C$1- مرجع سلولی مطلق که صرف نظر از اینکه فرمول به کجا کپی شود، هرگز تغییر نمی‌کند.

مزیت این روش این است که می‌توانید قیمت‌های یورو را براساس نرخ ارز متغیر بدون تغییر فرمول محاسبه کنید. هنگامی که نرخ تبدیل تغییر کرد، تنها کاری که باید انجام دهید، این است که مقدار را در سلول C1 به‌روزرسانی کنید.

برای تغییر نوع مرجع سلولی طبق آنچه که در وب‌سایت رسمی‌ مایکروسافت آورده شده است:

• سلولی را که حاوی مرجع سلولی مورد نظر برای تغییر است، انتخاب کنید.

• در نوار فرمول ، روی مرجع سلولی که می‌خواهید تغییر دهید، کلیک کنید.

• کلید ctrl+T در ویندوز یا cmd+T در مک را فشار دهید تا بین ترکیب‌ها جابه‌جا شوید.

رفع اشکالات رایج آدرس دهی در اکسل

برخی اشکالاتی که ممکن است هنگام آدرس دهی در اکسل با آن مواجه شوید و نحوه رفع آن‌ها عبارت‌اند از:

• خطای #REF!: این خطا معمولاً زمانی رخ می‌دهد که آدرس به یک سلول یا محدوده نامعتبر اشاره دارد. بررسی کنید که آیا سلول‌های مرجع حذف نشده‌اند یا خارج از محدوده صفحه نیستند.
• نتایج غیرمنتظره پس‌از کپی فرمول‌ها: اطمینان حاصل کنید که از نوع صحیح آدرس دهی در اکسل (مطلق یا نسبی) برای هدف خود استفاده کرده‌اید.
• فرمول‌های بسیار پیچیده: در صورت امکان، فرمول‌های پیچیده را به چند مرحله ساده‌تر تقسیم کنید تا عیب‌یابی و نگهداری آن‌ها آسان‌تر شود.

در ادامه و قبل از ادامه مطلب آدرس دهی در اکسل پیشنهاد می شود که از مقاله رسم نمودار در اکسل دیدن فرمائید.

چگونه مهارت‌های اکسل خود را به سطح حرفه‌ای ارتقا دهید؟

اگر می‌خواهید مهارت‌های خود را در اکسل بالا ببرید و با این کار فرصت‌های شغلی متعددی برای خود ایجاد کنید، دوره آموزش اکسل از مبتدی تا پیشرفته ۲۴ وب لرن را از دست ندهید! این دوره که توسط پدرام تقی‌زاده، متخصص برجسته اکسل، تدریس می‌شود، شما را قدم‌به‌قدم از سطح مبتدی تا حرفه‌ای می‌رساند.

این دوره با بیش‌از ۹ ساعت محتوای آموزشی غنی در قالب ۱۸ ویدیو، تمام جنبه‌های اکسل را از مفاهیم پایه گرفته تا تکنیک‌های پیشرفته مانند Pivot Tables، Data Validation و فرمول‌نویسی حرفه‌ای پوشش می‌دهد.

در ادامه بخوانید: چگونه در ورد جدول بکشیم

مزایای ویژه این دوره شامل موارد زیر هستند:

• دسترسی مادام‌العمر به تمام محتوا و به‌روزرسانی‌ها؛
• پشتیبانی دائمی ‌برای رفع اشکالات شما؛
• تضمین کیفیت با امتیاز ۴.۹۲ از ۵ براساس نظر کاربران؛
• قیمت استثنایی فقط ۱۹۹,۰۰۰ تومان به جای ۴۹۹,۰۰۰ تومان!

این دوره الگوبرداری‌شده از بهترین آموزش‌های اکسل Udemy است و با استفاده از بهترین تجهیزات صدابرداری ضبط شده است تا تجربه یادگیری فوق‌العاده‌ای را برای شما فراهم کند.

چه در حوزه حسابداری فعالیت می‌کنید، چه مدیر کسب‌وکار هستید یا دانشجویی که می‌خواهد در بازار کار متمایز شود، این دوره برای شماست. فرصت ارتقای مهارت‌های خود و افزایش ارزش حرفه‌ای‌تان را از دست ندهید.

همین حالا از صفحه محصول دوره جامع اکسل ۲۴ وب لرن دیدن کنید و اولین قدم را به سوی تسلط بر اکسل بردارید. فراموش نکنید، موفقیت در دنیای دیجیتال امروز با تسلط بر ابزارهایی مانند اکسل آغاز می‌شود!

 

سوالات متداول

تفاوت اصلی بین آدرس‌دهی مطلق و نسبی چیست؟

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

چگونه می‌توانم به‌سرعت بین حالت‌های مختلف آدرس‌دهی جابه‌جا شوم؟

با انتخاب آدرس در فرمول و فشردن کلید F4 می‌توانید به‌سرعت بین حالت‌های مختلف آدرس‌دهی (نسبی، مطلق کامل، مطلق ستون، و مطلق سطر) جابه‌جا شوید.

آیا استفاده از نام‌های محدوده به‌جای آدرس‌های سلول توصیه می‌شود؟

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

سخن پایانی

تسلط بر آدرس دهی در اکسل، کلید ایجاد اکسل‌شیت‌های قدرتمند و قابل‌اعتماد است. با درک عمیق مفاهیم آدرس‌دهی نسبی و مطلق در اکسل و تمرین مداوم می‌توانید کارایی خود را در استفاده از اکسل به‌طور قابل‌توجهی افزایش دهید.