نویسنده
درباره نویسنده
بهنام خیرالهی
تعداد پست ها 64

درباره:

دانش آموخته مقطع کارشناسی دانشگاه Staffordshire University در رشته علوم کامپیوتر گرایش امنیت و مقطع کارشناسی دانشگاه Asia Pacific University در رشته فناوری اطلاعات هستم. از سال 1392 به ایران برگشتم و به صورت تخصصی در زمینه طراحی وب، طراحی گرافیک و برنامه نویسی NET. فعالیت دارم.

  • آدرس‌دهی سلول‌ها

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

آدرس سلول‌ها

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

آدرس‌دهی سطر و ستون

استفاده از ترکیب حروف برای نمایش ستون و عدد برای نمایش سطر است. حروف از A شروع شده و تا XFD ادامه پیدا می‌کنند، یعنی تعداد 16,384 ستون در اکسل وجود دارد. اعداد سطر نیز از 1 شروع شده و تا 1,048,576 ادامه پیدا می‌کنند.

آدرس‌دهیمثال
سلول ستون A و سطر 1A1
محدوده سلول‌های ستون A، از سطر 1 تا سطر 10A1:A10
محدوده سلول‌های سطر 5، از ستون A تا ستون BA5:B5
تمام سلول‌های سطر 10، شامل تمامی ستون‌ها10:10
تمام سلول‌های سطرهای 5 تا 10، شامل تمامی ستون‌ها5:10
تمام سلول‌های ستون AA:A
تمام سلول‌های ستون A تا F، شامل تمامی سطرهاA:F
محدوده سلول‌های ستون‌های A تا F و سطرهای 5 تا 10A5:F10

آدرس‌دهی سلول‌ها و محدوده‌ها

آدرس‌دهی به شیت‌های دیگر

همانطور که در فرمول زیر می‌بینید، از شیت Sheet2 محدوده سلول‌های B1:B5 آدرس‌دهی شده است. علامت جداساز بین نام شیت و محدوده سلول‌های آن، علامت تعجب ! است.

=AVERAGE(Sheet2!B1:B5)

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

در اکسل 2 نوع آدرس‌دهی وجود دارد، آدرس‌دهی نسبی و آدرس‌دهی مطلق.

آدرس‌دهی نسبی

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

مثال

فرض کنید می‌خواهیم در سلول B1 فرمول زیر را تعریف کنیم.

=A1+A2

اگر این فرمول را در محدوده سلول‌های B2 تا B4 درگ کنیم، اعداد 5، 7 و 4 به ترتیب برای این سلول‌ها محاسبه می‌شوند. می‌خواهیم بدانیم که این اعداد از کجا به‌ دست آمده‌اند. وقتی در سلول B1 فرمول بالا را تعریف کنیم، در حقیقت سلول سمت چپ و یک سلول پایین آن با هم جمع می‌شوند. پس برای سلول B2، سلول سمت چپ آن یعنی A2 و سلول پایین آن یعنی A3 با هم جمع می‌شوند كه نتیجه به دست آمده 5 خواهد بود. برای سلول‌های بعدی هم به همین ترتیب محاسبات انجام می‌شود. بنابراین فرمول سلول B2 به صورت زیر آدرس‌دهی می‌شود.

=A2+A3

آدرس‌دهی نسبی

آدرس‌دهی نسبی

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

آدرس‌دهی مطلق همیشه به سلول مشخص شده‌ای اشاره دارد و با درگ کردن فرمول در هر سلولی، مقدار آدرس‌دهی شده تغییر پیدا نمی‌کند. مثلا اگر سلول C1 که دارای فرمول $A$10 است را در سلول C2 درگ کنیم، آدرس‌دهی مطلق ثابت می‌ماند و فرمول سلول C2 نیز همان $A$10 می‌شود.

مثال

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

=B2-B2*D2

این فرمول را در سلول C2 تعریف کرده و آن را برای دیگر کارمندان درگ می‌کنیم. نتیجه این فرمول فقط برای سلول C2 به درستی عمل کرده و برای بقیه سلول‌ها همان مقدار پایه حقوق را برمی‌گرداند. در زمان درگ کردن فرمول، اکسل از آدرس‌دهی نسبی استفاده کرده است و چون در سلول C2 فرمول B2-B2*D2 بوده، برای C3 فرمول B3-B3*D3 در نظر گرفته می‌شود که مقدار سلول D3 در آن صفر است. پس همان حقوق پایه به عنوان نتیجه برگرداننده می‌شود.

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

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

برای حل این مسئله باید برای سلول D2 از آدرس‌دهی مطلق استفاده کنیم، برای انجام این‌کار در فرمول $D$2 را جایگزین D2 می‌کنیم. سپس سلول C2 را برای دیگر کارمندان درگ می‌کنیم.

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

=B2-B2*$D$2

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

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

وقتی می‌خواهیم سطر یا ستون را مطلق کنیم باید یک علامت $ پشت شماره سطر یا نام ستون قرار دهیم و یا می‌توانیم از کلید F4 استفاده کنیم. با هر بار زدن کلید F4 یکی از 4 حالت آدرس‌دهی ظاهر می‌شود.

حالت‌های آدرس‌دهی

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

در نتیجه، 4 حالت برای آدرس‌دهی در اکسل به وجود می‌آید.

حالتعملکردمثال
ستون نسبی − سطر نسبیبا درگ کردن در ستون شماره ردیف، و با درگ کردن در ردیف نام ستون تغییر می‌کند.=A1
ستون مطلق − سطر مطلقبا درگ کردن در ستون شماره ردیف، و با درگ کردن در ردیف نام ستون تغییر نمی‌کند.=$A$1
ستون مطلق − سطر نسبیبا درگ کردن در ستون شماره ردیف تغییر می‌کند، اما با درگ کردن در ردیف نام ستون تغییر نمی‌کند.=$A1
ستون نسبی − سطر مطلقبا درگ کردن در ستون شماره ردیف تغییر نمی‌کند، اما با درگ کردن در ردیف نام ستون تغییر می‌کند.=A$1

حالت‌های آدرس‌دهی

آدرس‌دهی سه بعدی

اگر بخواهیم از داده‌های یک سلول یا محدوده‌ای از سلول‌های چند شیت استفاده کنیم، از آدرس‌دهی سه بعدی استفاده می‌کنیم. مثلا فرمول زیر سلول‌های محدوده B5:G10 در 10 شیت را جمع می‌کند.

=SUM(Sheet1:Sheet10!B5:G10)

مبحث آدرس‌دهی در اکسل بسیار مهم و کاربردی بوده و در فرمول‌نویسی باید به آن تسلط کامل داشته باشیم.