آدرس دهی در اکسل چگونه انجام می شود؟ راهنمای آدرس دهی قدم به قدم
آدرس دهی در اکسل یکی از مهارتهای مهم برای کار با فرمولها و محاسبات است که مشخص میکند هر فرمول باید از کدام سلول یا محدوده اطلاعات دریافت کند. این کار شامل موارد نحوه آدرسدهی سلولها و محدودهها، تفاوت آدرسدهی نسبی، مطلق و ترکیبی، روش ثابت کردن آدرس یک سلول با علامت $ و کاربرد کلید F4 می باشد که شما می توانید در دوره های آموزشی مانند دوره icdl، تمام این موارد را یاد بگیرید.
آدرس دهی در اکسل چیست؟ بررسی انواع آدرسدهی و نحوه عملکرد هرکدام
آدرس دهی در اکسل برای مشخص کردن محل یک سلول یا محدودهای از سلولها استفاده میشود. هر سلول با ترکیبی از حرف ستون و شماره ردیف شناخته میشود؛ برای مثال، A1 یعنی سلول ستون A است که در ردیف 1 قرار دارد. تفاوت انواع آدرسدهی در این است که وقتی یک سلول را جابهجا یا کپی میکنیم، آدرس آن تغییر کند یا ثابت بماند. این قابلیت در کارهای مختلف، از محاسبات ساده تا پروژههایی مانند آموزش ساخت نرم افزار حسابداری با اکسل، برای مدیریت و ارجاع دقیق به اطلاعات کاربرد دارد.
|
نوع آدرسدهی |
نمونه |
اگر یک خانه پایینتر برویم |
نحوه کار |
|
نسبی |
A1 |
A2 |
آدرس همراه با جابهجایی تغییر میکند. |
|
مطلق |
$A$1 |
$A$1 |
آدرس ثابت میماند و تغییر نمیکند. |
|
ترکیبی با ستون ثابت |
$A1 |
$A2 |
ستون ثابت میماند و ردیف تغییر میکند. |
|
ترکیبی با ردیف ثابت |
A$1 |
B$1 |
ردیف ثابت میماند و ستون تغییر میکند. |
|
سهبعدی |
Sheet1:Sheet3!A1 |
— |
|
|
هایپرلینک |
لینک به یک مقصد |
— |
با کلیک، به فایل، صفحه وب یا محل مشخصی در اکسل میرود. |
آدرسدهی نسبی در اکسل
در آدرسدهی نسبی، آدرس سلول به موقعیتی که در آن قرار دارد وابسته است. به همین دلیل، اگر سلول جابهجا یا کپی شود، آدرس آن نیز متناسب با موقعیت جدید تغییر میکند. برای مثال، آدرس A1 اگر یک خانه به پایین منتقل شود، به A2 و اگر یک خانه به سمت راست منتقل شود، به B1 تبدیل میشود. در این نوع آدرسدهی، هیچ بخشی از آدرس ثابت نشده است.
آدرسدهی مطلق در اکسل
در آدرسدهی مطلق، آدرس سلول کاملا ثابت میشود و با جابهجایی یا کپی کردن سلول تغییر نمیکند. برای مشخص کردن این حالت، قبل از حرف ستون و شماره ردیف علامت $ قرار میگیرد؛ مانند $A$1. بنابراین، چه سلول یک خانه پایینتر برود و چه به سمت راست منتقل شود، آدرس آن همچنان $A$1 باقی میماند.
آدرسدهی ترکیبی با ستون ثابت
در آدرسدهی ترکیبی با ستون ثابت، ستون بدون تغییر باقی مانده و ردیف متغیر است. برای مثال، در $A1 علامت $ قبل از حرف A قرار گرفته و باعث میشود ستون A هنگام جابهجایی تغییر نکند. در نتیجه، اگر سلول یک خانه پایینتر برود، آدرس از $A1 به $A2 تغییر میکند؛ اما اگر به سمت راست منتقل شود، همچنان $A1 باقی میماند.
آدرسدهی ترکیبی با ردیف ثابت
در مدل آدرسدهی ترکیبی با ردیف ثابت، ردیف بدون تغییر و ستون متغیر است. در آدرس A$1، علامت $ قبل از شماره 1 قرار دارد؛ بنابراین ردیف 1 همیشه ثابت میماند. اگر سلول یک خانه پایینتر برود، آدرس همچنان A$1 است، اما با یک خانه حرکت به سمت راست، به B$1 تبدیل میشود.
آدرسدهی سهبعدی در اکسل
آدرس دهی در اکسل به صورت سهبعدی برای ارجاع به یک سلول یا محدوده مشابه در چند شیت مختلف استفاده میشود؛ در این حالت، بهجای اینکه فقط یک شیت مشخص شود، محدودهای از شیتها تعیین میشود؛ برای مثال، Sheet1:Sheet3!A1 به سلول A1 در شیتهای بین Sheet1 تا Sheet3 اشاره دارد. تفاوت اصلی آن با آدرسدهی نسبی، مطلق و ترکیبی این است که تمرکز آن روی چند شیت است.
آدرس هایپرلینک در اکسل
آدرس دهی هایپرلینک در اکسل، اکثرا برای انتقال کاربر به یک مقصد مشخص استفاده میشود و با آدرسدهی سلولها تفاوت دارد. این مقصد میتواند یک صفحه وب، فایل دیگر، شیت خاص یا حتی یک سلول مشخص در همان فایل اکسل باشد؛ برای مثال، با کلیک روی یک هایپرلینک میتوان کاربر را مستقیما به سلول A1 در یک شیت دیگر منتقل کرد.
آدرس سلول در اکسل چگونه مشخص میشود؟
در اکسل، هر سلول یا محدوده با یک آدرس مشخص شده که برای شناسایی و ارجاع به آن، از آدرس دهی استفاده میکنیم؛ این موضوع بستگی به این دارد که بخواهیم به یک سلول، چند سلول، یک ردیف یا یک ستون اشاره شود. در ادامه، انواع آدرس دهی را توضیح خواهیم داد.
آدرس یک سلول
آدرس هر سلول از حرف ستون و شماره ردیف تشکیل میشود. برای مثال، A1 یعنی سلولی که در ستون A و ردیف 1 قرار دارد.
|
آدرس |
محل سلول |
|
A1 |
ستون A، ردیف 1 |
|
B3 |
ستون B، ردیف 3 |
|
D7 |
ستون D، ردیف 7 |
آدرس یک محدوده
وقتی بخواهیم چند سلول را بهعنوان یک محدوده مشخص کنیم، آدرس اولین و آخرین سلول را با علامت : به هم وصل میکنیم. برای مثال، A1:A5 یعنی سلولهای A1 تا A5.
|
آدرس |
محدوده مورد نظر |
|
A1:A5 |
از A1 تا A5 |
|
A1:C1 |
از A1 تا C1 |
|
A1:C5 |
تمام سلولهای بین A1 و C5 |
آدرس یک ردیف و ستون کامل
اگر بخواهیم به یک ستون یا ردیف کامل اشاره کنیم، از ساختار متفاوتی استفاده میکنیم. برای یک ستون، حرف ستون را دو بار با : مینویسیم و برای یک ردیف نیز شماره آن را به همین شکل تکرار میکنیم.
- A:A → کل ستون A
- A:C → ستونهای A تا C
- 1:1 → کل ردیف 1
- 1:5 → ردیفهای 1 تا 5
آموزش آدرسدهی نسبی در اکسل
در آدرسدهی نسبی، وقتی یک سلول را به محل دیگری کپی میکنیم، آدرس آن نیز متناسب با محل جدید تغییر میکند؛ به زبان ساده، اکسل موقعیت سلول را نسبت به محل قرارگیری آن در نظر میگیرد و هنگام جابهجایی، آدرس را هم تغییر میدهد. در ادامه، با یک مثال فرآیند و هدف از انجام آدرس دهی نسبی را توضیح خواهیم داد.
مرحله ۱-ساخت جدول اطلاعات
ابتدا اطلاعات را در اکسل وارد میکنیم. در جدول زیر، ستون مبلغ فروش هنوز خالی مانده و قرار است برای هر محصول محاسبه شود.
|
محصول |
تعداد |
قیمت |
مبلغ فروش |
|
کیف |
2 |
500000 |
|
|
کفش |
3 |
800000 |
|
|
شال |
4 |
200000 |
مرحله ۲- محاسبه مبلغ فروش محصول اول
در این مرحله، حالا روی اولین سلول خالی در ستون مبلغ فروش کلیک میکنیم. فرض کنیم این سلول D2 است. برای بهدست آوردن مبلغ فروش، تعداد را در قیمت ضرب میکنیم که به صورت زیر می شود:
=B2*C2
یعنی اکسل باید مقدار موجود در B2 را در مقدار موجود در C2 ضرب کند.
مرحله ۳- انتقال فرمول محاسبه به ردیفهای بعدی
در این قسمت ما نمیخواهیم برای کفش و شال دوباره محاسبه را از اول وارد کنیم؛ برای این کار گوشه( سمت راست پایین) سلول D2 را میگیریم و به سمت پایین میکشیم تا فرمول برای ردیفهای بعدی هم اعمال شود. در این مرحله اکسل متوجه شده که ما همان محاسبه را برای ردیف بعدی میخواهیم؛ بنابراین آدرسها را هم متناسب با جای جدید تغییر داده است.
آموزش آدرسدهی مطلق در اکسل
آدرسدهی مطلق در اکسل، زمانی کاربرد دارد که بخواهیم آدرس یک سلول هنگام کپی یا جابهجایی ثابت بماند. در این نوع آدرسدهی، با استفاده از علامت $ مشخص میکنیم که ردیف و ستون یک سلول نباید تغییر کنند. در ادامه، با یک مثال ساده و مرحلهبهمرحله میبینیم که آدرسدهی مطلق چگونه عمل میکند و در چه شرایطی باید از آن استفاده کنیم.
مرحله ۱- ساخت جدول اطلاعات
فرض کنید همان جدول فروش را داریم، اما این بار میخواهیم مالیات هر محصول را نیز محاسبه کنیم. نرخ مالیات برای همه محصولات یکسان است و در یک سلول جداگانه قرار دارد؛ فرض کنید نرخ مالیات 10 درصد است و آن را در سلول F1 وارد کردهایم.
|
محصول |
تعداد |
قیمت |
مبلغ فروش |
مالیات |
|
کیف |
2 |
500000 |
1000000 |
10% |
|
کفش |
3 |
800000 |
2400000 |
|
|
شال |
4 |
200000 |
800000 |
مرحله ۲- محاسبه مالیات محصول اول
برای محاسبه مالیات کیف، باید مبلغ فروش آن را در نرخ مالیات ضرب کنیم. پس سلول E2 را انتخاب میکنیم و مینویسیم:
=D2*F1
یعنی مبلغ فروش موجود در D2 را در نرخ مالیات موجود در F1 ضرب کن.
مرحله ۳- انتقال محاسبه به ردیفهای پایین
حالا میخواهیم همین محاسبه برای کفش و شال نیز انجام شود؛ اما اگر سلول E2 را به سمت پایین بکشیم، اکسل آدرسها را هم جابهجا میکند. در این قسمت ممکن است برای شما چالش به وجود بیاید زیرا نرخ مالیات فقط در F1 قرار دارد و نباید با پایین رفتن، به F2 و F3 تغییر کند.
|
ردیف |
آدرس محاسبه |
|
کیف |
D2*F1 |
|
کفش |
D3*F2 |
|
شال |
D4*F3 |
مرحله ۴- ثابت کردن سلول نرخ مالیات
در این مرحله است که باید از آدرسدهی مطلق استفاده میکنیم. با ورود فرمول زیر به اکسل میگوییم سلول F1 باید همیشه ثابت بماند. نحوه ورود فرمول به ترتیب به این صورت است:
کلیک رو ستون E2← = ← کلیک روی ستون D2 ← * ← کلیک روی ستون F2 ← زدن دکمه F4 روی کیبورد←Enter
=D2*$F$1
علامت $ باعث میشود آدرس F1 ثابت بماند.
مرحله ۵: دوباره محاسبه را به پایین منتقل کنیم
در این مرحله پس از ایجاد فرمول مطلق، مانند مورد قبل آن را به ردیفهای بعدی بکشیم تا روی بقیه مبالغ اعمال شود. د این مدل D2 به D3 و D4 تغییر کرده، چون باید مبلغ فروش هر ردیف محاسبه شود؛ اما $F$1 در تمام ردیفها ثابت مانده است.
|
محصول |
فرمول |
|
کیف |
=D2*$F$1 |
|
کفش |
=D3*$F$1 |
|
شال |
=D4*$F$1 |
مرحله ۶- اعمال شدن فرمول مطلق روی مبالغ
بعد از انجام مراحلی که گفتیم، به نتیجه نهایی جدول زیر می رسیم:
|
محصول |
مبلغ فروش |
نرخ مالیات |
مالیات |
|
کیف |
1000000 |
10٪ |
100000 |
|
کفش |
2400000 |
10٪ |
240000 |
|
شال |
800000 |
10٪ |
80000 |
آموزش آدرسدهی ترکیبی در اکسل
آدرسدهی ترکیبی زمانی استفاده میشود که بخواهیم فقط ردیف یا فقط ستون یک سلول ثابت بماند و بخش دیگر آن با جابهجایی تغییر کند. برای درک بهتر، دو حالت آدرسدهی ترکیبی را با یک مثال ساده بررسی میکنیم.
مرحله ۱- یک جدول ساده بسازید
فرض کنید میخواهیم قیمت محصولات را در تعداد مختلف بررسی کنیم. برای این کار ابتدا اطلاعات مورد نیاز را وارد جدول می کنیم. در این مثال، تعدادهای 1، 2 و 3 در ردیف اول قرار گرفتهاند و قیمت هر محصول در ستون اول جدول است.
|
B |
C |
D |
|
|
1 |
2 |
3 |
|
|
کیف |
500000 |
||
|
کفش |
800000 |
||
|
شال |
200000 |
مرحله ۲- آدرسدهی ترکیبی با ستون ثابت
فرض کنید در یکی از سلولها به قیمت کیف، یعنی A2، اشاره میکنیم و میخواهیم وقتی آن را به سمت پایین جابهجا میکنیم، ستون A ثابت بماند اما شماره ردیف تغییر کند.
برای این کار از این آدرس استفاده میکنیم:
$A2
اگر آن را یک خانه پایینتر ببریم:
$A2 → $A3 → $A4
میبینیم که A ثابت مانده اما شماره ردیف تغییر کرده است.
پس در $A2:
- $A → ستون ثابت است.
- 2 → ردیف میتواند تغییر کند.
مرحله ۳: آدرسدهی ترکیبی با ردیف ثابت
حالا حالت برعکس را در نظر بگیرید. فرض کنید به سلول B1 اشاره میکنیم و میخواهیم وقتی آن را به سمت راست میبریم، ردیف 1 ثابت بماند اما ستون تغییر کند.
در این حالت مینویسیم:
B$1
اگر آن را یک خانه به سمت راست ببریم:
B$1 → C$1 → D$1
اینجا ستون تغییر کرده، اما ردیف 1 ثابت مانده است.
پس در B$1:
- B → ستون میتواند تغییر کند.
- $1 → ردیف ثابت است.
مرحله ۴- تفاوت دو حالت را ببینید
در آدرس دهی در اکسل به صورت ترکیبی، تفاوت این دو روش فقط در محل قرار گرفتن علامت $ است. اگر $ قبل از حرف ستون باشد، ستون ثابت میماند؛ اگر قبل از شماره ردیف باشد، ردیف ثابت میماند.
|
نوع آدرسدهی ترکیبی |
نمونه |
یک خانه پایینتر |
یک خانه به راست |
|
ستون ثابت |
$A2 |
$A3 |
$A2 |
|
ردیف ثابت |
B$1 |
B$1 |
C$1 |
تفاوت آدرس دهی نسبی، مطلق و ترکیبی
برای انتخاب نوع مناسب آدرسدهی در اکسل، باید بدانید هرکدام هنگام جابهجایی یا کپی کردن سلول چه رفتاری دارند و فرمول نويسي در اكسل باید چگونه صورت بگیرد. در جدول زیر، تفاوت آدرسدهی نسبی، مطلق، ترکیبی، سهبعدی و هایپرلینک را از نظر نحوه عملکرد و کاربرد با هم مقایسه کردهایم.
|
نوع |
چه چیزی را کنترل میکند؟ |
با جابهجایی چه اتفاقی میافتد؟ |
|
نسبی |
ردیف و ستون آزاد هستند |
هر دو میتوانند تغییر کنند |
|
مطلق |
ردیف و ستون ثابت هستند |
هیچکدام تغییر نمیکنند |
|
ترکیبی با ستون ثابت |
فقط ستون ثابت است |
ردیف تغییر میکند |
|
ترکیبی با ردیف ثابت |
فقط ردیف ثابت است |
ستون تغییر میکند |
کلید F4 در آدرس دهی اکسل چه کاربردی دارد؟
کلید F4 در اکسل، برای تغییر سریع نوع آدرسدهی سلول استفاده میشود. بهجای اینکه هر بار علامت $ را بهصورت دستی وارد کنیم، میتوانیم آدرس سلول را انتخاب کرده و با فشردن F4 بین حالتهای مختلف آدرسدهی جابهجا شویم. در ادامه، بیشتر درباره این موضوع توضیح خواهیم داد.
مرحله 1- یک آدرس معمولی داشته باشید
فرض کنید آدرس سلول A1 است. در این حالت، هم ستون و هم ردیف میتوانند هنگام جابهجایی تغییر کنند.
مرحله ۲- کلید F4 را فشار دهید
با فشردن F4، آدرس به حالت مطلق تبدیل میشود؛ حالا ستون A و ردیف 1، هردو ثابت هستند.
A1 → $A$1
مرحله ۳- با هر بار فشردن F4، حالت آدرس تغییر میکند
بعد از اینکه درباره کاربرد دکمه F4 اطلاعات لازم را کسب کردید، در این مرحله، به شما توضیح می دهیم که با هر بار فشردن این دکمه چه اتفاقاتی می افتد؛ کاربرد اصلی F4 این است که بدون تایپ کردن $، نوع آدرسدهی را سریع تغییر دهیم. در ادامه، میتوانیم بهصورت زیر این تغییر را ببینیم:
|
وضعیت |
آدرس |
چه چیزی ثابت است؟ |
|
حالت نسبی |
A1 |
هیچکدام |
|
حالت مطلق |
$A$1 |
ستون و ردیف |
|
ترکیبی با ردیف ثابت |
A$1 |
فقط ردیف |
|
ترکیبی با ستون ثابت |
$A1 |
فقط ستون |
|
بازگشت به نسبی |
A1 |
هیچکدام |
خطاهای رایج در آدرس دهی اکسل چیست؟
اشتباه در آدرسدهی میتواند باعث شود نتیجه فرمول با چیزی که انتظار دارید متفاوت باشد؛ این خطاها معمولا هنگام کپی کردن فرمول، ثابت کردن سلول یا انتخاب محدوده اشتباه اتفاق میافتند. مهمترین موارد عبارتاند از:
- فراموش کردن علامت $: اگر بخواهید آدرس یک سلول ثابت بماند اما از $ استفاده نکنید، با کپی کردن فرمول، آدرس آن نیز تغییر میکند.
- استفاده نادرست از آدرسدهی ترکیبی: در $A1 ستون ثابت است، اما در A$1 ردیف ثابت میماند؛ جابهجا کردن $ باعث میشود نتیجه موردنظر به دست نیاید.
- انتخاب محدوده اشتباه: مثلاً بهجای A1:A10، محدوده A1:A9 انتخاب شود و یکی از دادهها در محاسبه وارد نشود.
- اشتباه در نام شیت: هنگام ارجاع به شیت دیگر، وارد کردن نام نادرست شیت یا استفاده نکردن صحیح از علامت ! باعث ایجاد خطا در Reference میشود.
- تغییر ناخواسته آدرس هنگام کپی فرمول: قبل از کپی کردن فرمول باید مشخص شود کدام آدرسها باید تغییر کنند و کدام آدرسها باید ثابت بمانند.
- اشتباه گرفتن آدرس سلول با مقدار آن: در فرمول، A1 به معنی اشاره به سلول A1 است، نه اینکه خود عبارت A1 یک عدد یا متن باشد.
نتیجه گیری؛ کاربرد انواع آدرس دهی در اکسل چیست؟
آدرسدهی در اکسل، مشخص میکند سلولها در فرمولها چگونه مورد استفاده قرار بگیرند؛ آدرسدهی نسبی برای آدرسهای قابل تغییر، مطلق برای ثابت نگه داشتن سلول و ترکیبی برای ثابت کردن ردیف یا ستون کاربرد استفاده می شود. اگر میخواهید کار با نرمافزارهای اداری را بهصورت اصولی و با روشهای بهروز یاد بگیرید، آموزشگاه فنی حرفهای رادمان با بهرهگیری از اساتید مجرب، دورههای آموزشی کاربردی را برای تقویت مهارتهای شما برگزار میکند.
تیم تحریریه رادمان با ارائه مقالات آموزشی و کاربردی در زمینه فناوری، کسبوکار، مهارتهای فنی و توسعه فردی، تلاش میکند دانش بهروز و عملی را در اختیار کاربران قرار دهد. هدف ما تولید محتوای مفید و قابل اجرا برای کمک به رشد و پیشرفت شما در دنیای دیجیتال و حرفهای است.
نویسنده مقاله : رادمان
