آدرس دهی در اکسل و کاربرد آن در فرمول نویسی
آدرس دهی در اکسل از مفاهیم پایه و اساسی است. درک آدرس و کاربرد صحیح آن از اصول اولیه آموزشی اکسل است. هر سلول در اکسل دارای دو مشخصه اصلی مقدار و نام است. به عبارت دیگر زمانی که یک داده در سلولی در اکسل ذخیره میشود، دو موجودیت از آن برای ما اهمیت مییابد. اول این که مقدار داده چقدر است و دوم این که این مقدار در کجا ذخیره شده است. مقدار توسط کاربر به روشهای مختلف و به صورت مستقیم یا غیر مستقیم وارد اکسل میشود. ولی نام (آدرس) سلول موجودیتی است که در نحوه تشکیل سلول نهفته است. همانطوری که در محدوده در اکسل گفته شد، هر سلول از تقاطع یک ستون و یک سطر تشکیل میشود. آدرس سلول نیز از نام ستون و سطر گرفته میشود. سلول A1 از تقاطع ستون A و سطر ۱ تشکیل شده است.
اغلب از اکسل برای دو منظور کلی ذخیره دادهها و محاسبات بر روی دادهها استفاده میشود. انجام هر گونه محاسبه بر روی دادهها مستلزم مراجعه به سلولِ محتوی داده و فراخوانی آن است. در فرمول و توابع در اکسل بخشهای مختلف فرمول و تابع تشریح شد (نام فرمول و آرگومانهای فرمول). دادهها در قسمت آرگومان فرمول به کار گرفته میشوند و به عبارت دیگر ورودی فرمول یا تابع، آرگومان آن است. دادهها خود به دو صورت وارد فرمول میشوند. یا به صورت مستقیم توسط کاربر تایپ میشوند یا از طریق سلولها فراخوانی میشوند. به هنگام فراخوانی داده، آدرس سلولی که داده در آن ذخیره شده است، در آرگومان وارد میشود.
آدرس دهی در اکسل و تقسیم بندی انواع داده
با توجه به مشخصههای مهم ذکر شده برای یک داده (مقدار و آدرس)، میتوان دو نوع اصلی تقسیم بندی دادهها را به صورت زیر تعریف کرد. این تعاریف صرفاً برای استفاده در مفهوم آدرس دهی به کار برده میشوند.
داده متغییر: دادهای که مقدار یا آدرس آن در آرگومان فرمول تغییر کند داده متغییر گفته میشود. ممکن است در یک فرمول از دو داده با دو آدرس متفاوت ولی با مقدار مساوی استفاده شود، این دادهها نیز به واسطه تغییر کردن آدرسشان، متغییر محسوب میشوند.
در شکل زیر و در فرمول های نوشته شده در سلولهای B8 و B9 با اینکه مقدار سلولهای C8 و C9 مساوی و برابر ۲/۴۷ است، ولی به واسطه تغییر در آدرس، داده متغییر محسوب میشوند.
داده ثابت: دادهای که فارغ از مقدار، صرفاً آدرسش در آرگومان فرمول ثابت باشد داده ثابت گفته میشود. ممکن است در فرمولی از یک داده با آدرس ثابت استفاده شود ولی مقدار داده به وسیله کاربر یا توابع دیگر (مانند تابع RAND) تغییر کند، این داده نیز ثابت در نظر گرفته میشود. اعداد ثابتی که بعضاً در فرمولها استفاده میشوند نیز، نوعی داده ثابت هستند.
آدرس دهی در اکسل چگونه انجام میشود؟
زمانی که فرمولی در یک سلول نوشته میشود و در آرگومان آن دادههایی فراخوانی میشوند، اکسل به صورت پیش فرض، آدرس سلول حاوی فرمول (سلول فرمول) و آدرس سلول حاوی داده (سلول داده) را نسبت به یکدیگر میسنجد. به عبارت دیگر به هنگام آدرس دهی در اکسل، صرفاً فاصله سطرها و ستونهایی که سلول فرمول و سلول داده از هم دارند اندازه گیری میشود.
آدرس دهی در اکسل به صورت نسبی
در شکل زیر فرمولی در سلول B6 نوشته شده است که وزن کل آرماتور شماره ۱۶ را با استفاده از طول (سلول E6)، تعداد (سلول D6) و وزن واحد (سلول C6) آن محاسبه میکند. این یک فرمول ساده بوده و با استفاده از عملگر ضرب مقدار سلولهای D6، E6 و C6 را در هم ضرب کرده و حاصل را در سلول B6 مینویسد. در این فرمول آدرس ها به صورت نسبی نوشته شدهاند و اکسل برای اجرای این فرمول (با توجه به آدرسی که داده شده است) به ترتیب مقادیرِ یک ستون، دو ستون و سه ستون بعد از سلول فرمول را در هم ضرب میکند. یعنی سطرها ثابت بوده و سلول دادهها، فواصل یک، دو و سه ستونی از سلول فرمول دارند.
اگر این فرمول را در سلول B7 کپی کنید، با توجه به نسبی بودن آدرس ها، این بار اکسل مقدار سلولهای D7، E7 و C7 را در هم ضرب کرده و حاصل را در سلول B7 مینویسد.
باز اگر این فرمول را در سلول A8 کپی کنید، با توجه به نسبی بودن آدرس ها، این بار نیز اکسل، مقدار سلولهای C8، D8 و B8 را در هم ضرب کرده و حاصل را در سلول A8 مینویسد. و چون سلول B8 مقداری ندارد، نتیجه صفر خواهد بود.
در جدول مثال، فرمولی با ساختار ثابت در یک ستون از جدول تکرار شده و با استفاده از یک یا چند ستون عملیات مشابهی انجام میدهد. در مواردی شبیه به این از آدرس دهی نسبی استفاده کنید. در آدرس دهی نسبی، فرمول مورد نظر را در اولین سلول از ستون حاصل ( در اینجا ستون B) نوشته و با درگ کردن، آن را در دیگر سلولها نیز اعمال نمایید. نحوه درگ کردن را از محدوده در اکسل مطالعه نمایید.
آدرس دهی در اکسل به صورت مطلق
در برخی موارد محاسباتی اتفاق میافتد که در آرگومان فرمول یک سری داده متغیر و یک سری داده ثابت وجود دارد. به عنوان مثال یک ستون از دادهها در یک داده خاص (عدد ثابت) ضرب شده و حاصل در ستونی دیگر نوشته میشود. در این موارد به دلیل تغییر نکردن آدرس دادههای ثابت، بهتر است از آدرس دهی مطلق استفاده شود.
اهمیت آدرس دهی مطلق زمانی آشکار میشود که بخواهید این چنین فرمول هایی را درگ کنید و در دیگر سلول ها نیز تعمیم بدهید. در این شکل از آدرس دهی مطلق استفاده شده و سپس فرمول نوشته شده در سلول B3 به سلولهای دیگر تعمیم داده شده است.
به هنگام استفاده از آدرس دهی مطلق، اکسل از آدرس ثابت دادهها استفاده میکند. در تعمیم فرمول، زمانی که سلول فرمول جابجا میشود، سلول داده به صورت هماهنگ با این جابجایی تغییری نکرده و آدرسی که برای اولین فرمول استفاده شده را به کار میگیرد. در این مثال از آدرس J3 در کل فرمولهای ستون B استفاده شده است.
ولی در شکل زیر از آدرس دهی نسبی استفاده شده و سپس فرمول نوشته شده در سلول B3 به سلولهای دیگر تعمیم داده شده است. به تفاوت دو نوع آدرس دهی نسبی و مطلق و نتایج حاصله از آنها در دو شکل بالا و پایین توجه فرمایید.
در کل، زمانی که بخواهید فرمولی را به سلولهای مختلف تعمیم بدهید برای دادههای ثابت از آدرس دهی مطلق استفاده نمایید. در صورتی که از این نوع آدرس دهی استفاده نکنید، باید فرمول هر سلول را به صورت دستی تایپ نمایید!
در آدرس دهی نسبی در اکسل چه اتفاق میافتد؟
اگر از آدرس دهی نسبی استفاده کنید، در تعمیم فرمول، زمانی که سلول فرمول جابجا میشود، سلول داده نیز به صورت هماهنگ با این جابجایی حرکت کرده و آدرس جدیدی برای هر فرمول به خود میگیرد. اگر جابجایی سلول فرمول فقط در طول ستونها باشد، سلول داده نیز به همان مقدار در طول ستونها حرکت خواهد کرد. اگر جابجایی سلول فرمول فقط در طول سطرها باشد، سلول داده نیز به همان مقدار در طول سطرها حرکت خواهد کرد. حرکت ترکیبی سلول فرمول، منجر به حرکت ترکیبی سلول داده به همان مقدار و در همان جهت خواهد شد. در این مثال فرمول از B3 تا B13 (در طول ستونها و به سمت پایین) حرکت کرده، هماهنگ با آن سلول داده نیز از J3 تا J13 حرکت کرده است.
چگونه یک آدرس دهی در اکسل را مطلق کنیم؟
به صورتی که در اشکال بالا نیز مشاهده میشود، از علامت دلار ($) برای مطلق کردن آدرس دهی در اکسل استفاده میشود. همانطوری که در قسمت ابتدایی اشاره شد، آدرس هر سلول از ترکیب آدرس یک ستون و یک سطر تشکیل شده است. اگر علامت $ قبل از آدرس ستون (قسمت حرف آدرس) گذاشته شود، حرکت در طول سطرها محدود شده و اگر این علامت قبل از آدرس سطر (قسمت عدد آدرس) گذاشته شود، حرکت در طول ستونها محدود خواهد شد. بدیهی است، استفاده همزمان از علامت $ قبل از آدرس ستون و آدرس سطر، حرکت در هر دو جهت را محدود کرده و سلول کلاً ثابت خواهد ماند.
مثال برای آدرس دهی در اکسل به صورت مطلق و ترکیبی
در مثال زیر که جدول محاسبه میزان مصالح مصرفی بتن ریزی در یک کارگاه ساختمانی است، از آدرس دهی مطلق و نسبی استفاده شده است. در این کارگاه از دو طرح اختلاط برای بتن ریزی های استفاده شده است. برای هر طرح، احجام بتن ریزی به طور جداگانه در سلولهای C7 و C8 نوشته شده است.
در جدول فوق هر یک از مقادیر مصالح (سیمان، ماسه، نخودی و بادامی) در طرح اختلاط، باید به حجم بتن ریزی ضرب شوند تا مقدار کل مصالح مصرفی برای هر طرح بدست آید. با نوشتن فرمول در سلول D7 مقدار سیمان مصرفی به ازای ۵۸۵ متر مکعب بتن ریزی محاسبه میشود. با تعمیم این فرمول به صورت سطری (در سلولهای F7، E7 و G7 ) برای اینکه با جابجایی فرمول، آدرس سلول مقدار بتن ریزی (۵۸۵ متر مکعب) تغییری نکند، این آدرس به صورت مطلق و با محدود کردن حرکت در جهت سطرها نوشته شده است. برای طرح شماره ۲ طرح اختلاط، فرمول در سلول D8 نوشته شده با این تفاوت که آدرس حجم بتن ریزی کاملاً مطلق نوشته شده است. بسته به مورد از هر ترکیب آدرس دهی مطلق میتوان استفاده کرد. با کنترل مناسب ، نتایج یکسان خواهد بود.
آدرس دهی در اکسل و نکات مهم آن
این مفهوم کلی آدرس دهی در اکسل است. همواره در آدرس دهی نسبی، فاصله سطر و ستونِ سلولِ داده نسبت به سطر و ستون سلولِ فرمول سنجیده میشود، و با جابجایی یا تعمیم سلول فرمول، سلول داده نیز به همان نسبت و در همان جهت حرکت میکند. در آدرس دهی مطلق، سلول داده همواره آدرس ثابتی دارد و با جابجایی یا تعمیم سلول فرمول، سلول داده هیچ حرکتی نخواهد داشت.
برای تبدیل آدرس نسبی به مطلق از کلید میانبر F4 نیز میتوان استفاده کرد. آدرس سلول مورد نظر را در فرمول انتخاب کرده با یک بار فشردن کلید F4 آدرس کلا مطلق میشود، با دومین بار فشردن این کلید حرکت در جهت ستونها محدود شده و با سومین بار فشردن این کلید حرکت در جهت سطرها محدود میشود، چهارمین بار فشردن کلید منجر به نسبی شدن مجدد آدرس خواهد شد.
همواره باید توجه کرد که بدون دلیل هیچ آدرسی نباید مطلق شود، چون هم کنترل محدودیت حرکت در جهت مناسب زمان بر است و هم بعضاً مطلق سازی بی دلیل آدرس، در محاسبات حجیم و گستره باعث بروز خطاهای فاحش میشود.
