ا
اکسلدان شو | آموزش اکسل ویژه بازار کار
[فهرست آموزش] پارت سوم: آدرسدهی مطلق در اکسل توی پارت قبلی با آدرسدهی نسبی آشنا شدیم و دیدیم وقتی یک فرمول رو کپی یا درگ میکنیم، آدرس سلولها هم همراهش تغییر میکنن. یعنی اکسل آدرسها رو نسبت به محل جدید فرمول تنظیم میکنه. اما همیشه این چیزی نیست که ما بخوایم. گاهی وقتها یک سلول داریم که میخوایم در همه فرمولها ثابت بمونه و هر چقدر هم فرمول رو کپی کنیم، آدرس اون تغییر نکنه. اینجاست که از آدرسدهی مطلق استفاده میکنیم. برای اینکه یک آدرس رو ثابت کنیم از علامت $ استفاده میکنیم. مثلاً این آدرس: $A$۱ یعنی هم ستون A ثابت است و هم سطر ۱ ثابت است. بنابراین اگر فرمول را هرجا کپی کنیم، اکسل همچنان به همان سلول A۱ اشاره میکند. حالا یک مثال کاربردی بزنیم. فرض کنید در یک جدول بهای تمام شده کالای ساخته شده را داریم و میخواهیم با استفاده از حاشیه سود مورد انتظار، قیمت فروش را محاسبه کنیم. مثلاً: A۲ = بهای تمام شده کالا B۱ = درصد حاشیه سود مورد انتظار برای محاسبه قیمت فروش در سلول B۲ مینویسیم: =A۲*(۱+$B$۱) در اینجا بهای تمام شده هر کالا در ۱ + درصد حاشیه سود ثابت ضرب میشود. حالا اگر این فرمول را به پایین درگ کنیم: =A۳*(۱+$B$۱) =A۴*(۱+$B$۱) =A۵*(۱+$B$۱) میبینیم که آدرس A۲ تغییر میکند چون برای هر کالا متفاوت است، اما B۱ ثابت میماند چون ما آن را مطلق کردهایم. اگر علامت $ را نگذاریم و بنویسیم: =A۲*(۱+B۱) وقتی فرمول را به سمت پایین درگ کنیم، آدرس B۱ هم تغییر میکند و تبدیل میشود به: =A۳*(۱+B۲) =A۴*(۱+B۳) =A۵*(۱+B۴) که در این حالت محاسبات ما اشتباه میشود. یک نکته کاربردی هم اینجا هست. وقتی داخل فرمول روی یک آدرس قرار بگیری و کلید F۴ را بزنی، اکسل خودش علامت $ را اضافه میکند و آدرس را مطلق میکند. پس به طور خلاصه: آدرسدهی مطلق یعنی ثابت نگه داشتن آدرس یک سلول در فرمولها تا با کپی یا درگ کردن تغییر نکند. در پارت بعدی میرویم سراغ آدرسدهی ترکیبی (مختلط) که ترکیبی از آدرسدهی نسبی و مطلق است.
[فهرست آموزش]
پارت چهارم: آدرسدهی ترکیبی در اکسل
تا اینجا با دو نوع آدرسدهی در اکسل آشنا شدیم. اولی آدرسدهی نسبی بود که با جابهجا شدن فرمول، آدرسها هم تغییر میکردند. دومی آدرسدهی مطلق بود که با استفاده از علامت $ آدرس یک سلول را ثابت نگه میداشتیم.
اما در بعضی از مسائل اکسل ما نه میخواهیم آدرس کاملاً تغییر کند و نه میخواهیم کاملاً ثابت بماند.
گاهی لازم داریم فقط ستون ثابت باشد یا فقط سطر ثابت باشد. اینجاست که آدرسدهی ترکیبی به کار میآید.
در آدرسدهی ترکیبی، فقط یک بخش از آدرس را ثابت میکنیم؛ یعنی یا ستون را ثابت میکنیم یا سطر را.
در واقع دو حالت داریم:$A۱A$۱
بیایید هر کدام را ساده بررسی کنیم.حالت اول:ستون ثابت و سطر نسبی$A۱
در این حالت ستون A ثابت است اما شماره سطر تغییر میکند. یعنی اگر فرمول را به پایین درگ کنیم، سطرها تغییر میکنند ولی اگر به سمت جلو درگ بکنیم ستون A ثابت میماند.
مثلاً در سلول E۱ فرمول زیر را بنویسید:=$A۱*B۱در ادامه این فرمول را یکبار به سمت پایین و یکبار به سمت جلو درگ یا کپی کنید.یعنی سلول E۱ تا F۲ حاوی فرمول بشن.حالا دقت کنید در سلول های زیر فرمول ها چه تغییراتی داشتند:
سلول E۲ : چون فرمول یک رکورد به سمت پایین درگ شده و تمام سطرها در فرمولی که نوشتیم نسبی هستند در نتیجه سطرها تغییر میکنند.=$A۲*B۲
نکته : زمانی که به سمت بالا یا پایین حرکت میکنیم نسبی یا مطلق بودن در عنوان ستون تاثیری ندارد.
سلول F۱ : چون فرمول یک رکورد به سمت جلو درگ شده و در فرمول ستون A ثابت هست و B نسبی ، در نتیجه ستون B تغییر میکند.=$A۱*C۱ نکته : زمانی که به سمت چپ یا راستی حرکت میکنیم نسبی یا مطلق بودن در شماره سطر تاثیری ندارد.
سلول F۲ : چون فرمول یک رکورد به سمت جلو و یک رکورد به سمت پایین درگ شده و در فرمول ستون A ثابت هست و B نسبی ، در نتیجه ستون B تغییر و شماره سطر چون به سمت پایین درگ شده و نسبی هم بوده پس تغییر می کند.=$A۲*C۲
حالت دوم:A$۱در این حالت سطر ۱ ثابت است اما ستون میتواند تغییر کند.
مثلاً اگر در سلول E۱ بنویسیم:=A$۱*B۲و فرمول را به سمت پایین کپی کنیم، نتیجه چیزی شبیه این در سلول E۲ نمایان میشود:=A$۱*B۳میبینید که سطر ۱ ثابت مانده ولی سطر ۲ تغییر کردهاند.اگر فرمول سلول E۱ را بر روی F۲ کپی شود چون هم یک ستون رو به جلو و هم یک سطر رو به پایین اومد فرمول به صورت زیر تغییر میکند: =B$۱*C۳آدرسدهی ترکیبی معمولاً در جدولهای دوطرفه خیلی کاربرد دارد. مثلاً وقتی یک طرف جدول قیمتها هستند و طرف دیگر درصدها یا ضرایب هستند و ما میخواهیم فرمول را هم به پایین و هم به سمت جلو کپی کنیم.
در این حالت با استفاده از آدرسدهی ترکیبی میتوانیم دقیق مشخص کنیم که کدام بخش از آدرس باید ثابت بماند و کدام بخش اجازه تغییر دارد.
یک نکته کاربردی دیگر هم اینجا هست. اگر روی یک آدرس در فرمول قرار بگیری و کلید F۴ را چند بار بزنی، اکسل حالتهای مختلف آدرسدهی را برایت عوض میکند:
پیش فرض اکسل » A۱یکبار کلید F۴ رو بزنید » $A$۱دوبار کلید F۴ رو بزنید » A$۱سه بار کلید F۴ رو بزنید » $A۱چهاربار کلید F۴ رو بزنید » A۱
این کار کمک میکند خیلی سریع نوع آدرسدهی مورد نظرت را انتخاب کنی.
پس اگر بخواهیم خیلی ساده جمعبندی کنیم:
در آدرسدهی ترکیبی ما فقط بخشی از آدرس سلول را ثابت نگه میداریم؛ یعنی یا سطر ثابت میشود یا ستون.
در ادامه چند مثال کاربردی از این مبحث حل میکنیم تا موضوع کاملاً جا بیفتد. برای این مثالها به صفحات مربوط به مثالها در جزوه مقدماتی اکسل مراجعه میکنیم و همان تمرینها را با هم مرحلهبهمرحله بررسی خواهیم کرد.
پارت چهارم: آدرسدهی ترکیبی در اکسل
تا اینجا با دو نوع آدرسدهی در اکسل آشنا شدیم. اولی آدرسدهی نسبی بود که با جابهجا شدن فرمول، آدرسها هم تغییر میکردند. دومی آدرسدهی مطلق بود که با استفاده از علامت $ آدرس یک سلول را ثابت نگه میداشتیم.
اما در بعضی از مسائل اکسل ما نه میخواهیم آدرس کاملاً تغییر کند و نه میخواهیم کاملاً ثابت بماند.
گاهی لازم داریم فقط ستون ثابت باشد یا فقط سطر ثابت باشد. اینجاست که آدرسدهی ترکیبی به کار میآید.
در آدرسدهی ترکیبی، فقط یک بخش از آدرس را ثابت میکنیم؛ یعنی یا ستون را ثابت میکنیم یا سطر را.
در واقع دو حالت داریم:$A۱A$۱
بیایید هر کدام را ساده بررسی کنیم.حالت اول:ستون ثابت و سطر نسبی$A۱
در این حالت ستون A ثابت است اما شماره سطر تغییر میکند. یعنی اگر فرمول را به پایین درگ کنیم، سطرها تغییر میکنند ولی اگر به سمت جلو درگ بکنیم ستون A ثابت میماند.
مثلاً در سلول E۱ فرمول زیر را بنویسید:=$A۱*B۱در ادامه این فرمول را یکبار به سمت پایین و یکبار به سمت جلو درگ یا کپی کنید.یعنی سلول E۱ تا F۲ حاوی فرمول بشن.حالا دقت کنید در سلول های زیر فرمول ها چه تغییراتی داشتند:
سلول E۲ : چون فرمول یک رکورد به سمت پایین درگ شده و تمام سطرها در فرمولی که نوشتیم نسبی هستند در نتیجه سطرها تغییر میکنند.=$A۲*B۲
نکته : زمانی که به سمت بالا یا پایین حرکت میکنیم نسبی یا مطلق بودن در عنوان ستون تاثیری ندارد. سلول F۱ : چون فرمول یک رکورد به سمت جلو درگ شده و در فرمول ستون A ثابت هست و B نسبی ، در نتیجه ستون B تغییر میکند.=$A۱*C۱
نکته : زمانی که به سمت چپ یا راستی حرکت میکنیم نسبی یا مطلق بودن در شماره سطر تاثیری ندارد. سلول F۲ : چون فرمول یک رکورد به سمت جلو و یک رکورد به سمت پایین درگ شده و در فرمول ستون A ثابت هست و B نسبی ، در نتیجه ستون B تغییر و شماره سطر چون به سمت پایین درگ شده و نسبی هم بوده پس تغییر می کند.=$A۲*C۲
حالت دوم:A$۱در این حالت سطر ۱ ثابت است اما ستون میتواند تغییر کند.
مثلاً اگر در سلول E۱ بنویسیم:=A$۱*B۲و فرمول را به سمت پایین کپی کنیم، نتیجه چیزی شبیه این در سلول E۲ نمایان میشود:=A$۱*B۳میبینید که سطر ۱ ثابت مانده ولی سطر ۲ تغییر کردهاند.اگر فرمول سلول E۱ را بر روی F۲ کپی شود چون هم یک ستون رو به جلو و هم یک سطر رو به پایین اومد فرمول به صورت زیر تغییر میکند: =B$۱*C۳آدرسدهی ترکیبی معمولاً در جدولهای دوطرفه خیلی کاربرد دارد. مثلاً وقتی یک طرف جدول قیمتها هستند و طرف دیگر درصدها یا ضرایب هستند و ما میخواهیم فرمول را هم به پایین و هم به سمت جلو کپی کنیم.
در این حالت با استفاده از آدرسدهی ترکیبی میتوانیم دقیق مشخص کنیم که کدام بخش از آدرس باید ثابت بماند و کدام بخش اجازه تغییر دارد.
یک نکته کاربردی دیگر هم اینجا هست. اگر روی یک آدرس در فرمول قرار بگیری و کلید F۴ را چند بار بزنی، اکسل حالتهای مختلف آدرسدهی را برایت عوض میکند:
پیش فرض اکسل » A۱یکبار کلید F۴ رو بزنید » $A$۱دوبار کلید F۴ رو بزنید » A$۱سه بار کلید F۴ رو بزنید » $A۱چهاربار کلید F۴ رو بزنید » A۱
این کار کمک میکند خیلی سریع نوع آدرسدهی مورد نظرت را انتخاب کنی.
پس اگر بخواهیم خیلی ساده جمعبندی کنیم:
در آدرسدهی ترکیبی ما فقط بخشی از آدرس سلول را ثابت نگه میداریم؛ یعنی یا سطر ثابت میشود یا ستون.
در ادامه چند مثال کاربردی از این مبحث حل میکنیم تا موضوع کاملاً جا بیفتد. برای این مثالها به صفحات مربوط به مثالها در جزوه مقدماتی اکسل مراجعه میکنیم و همان تمرینها را با هم مرحلهبهمرحله بررسی خواهیم کرد.
۸۱۱