آدرس دهي در اكسل و كاربرد آن در فرمول نويسي

آدرس دهي در اكسل از مفاهيم پايه و اساسي است. درك آدرس و كاربرد صحيح آن از اصول اوليه آموزشي اكسل است. هر سلول در اكسل داراي دو مشخصه اصلي مقدار و نام است. به عبارت ديگر زماني كه يك داده‌ در سلولي در اكسل ذخيره مي‌شود، دو موجوديت از آن براي ما اهميت مي‌يابد. اول اين كه مقدار داده چقدر است و دوم اين كه اين مقدار در كجا ذخيره شده است. مقدار توسط كاربر به روش‌هاي مختلف و به صورت مستقيم يا غير مستقيم وارد اكسل مي‌شود. ولي نام (آدرس) سلول موجوديتي است كه در نحوه تشكيل سلول نهفته است. همانطوري كه در محدوده در اكسل گفته شد، هر سلول از تقاطع يك ستون و يك سطر تشكيل مي‌شود. آدرس سلول نيز از نام ستون و سطر گرفته مي‌شود. سلول 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 آدرس كلا مطلق مي‌شود، با دومين بار فشردن اين كليد حركت در جهت ستون‌ها محدود شده و با سومين بار فشردن اين كليد حركت در جهت سطرها محدود مي‌شود، چهارمين بار فشردن كليد منجر به نسبي شدن مجدد آدرس خواهد شد.

همواره بايد توجه كرد كه بدون دليل هيچ آدرسي نبايد مطلق شود، چون هم كنترل محدوديت حركت در جهت مناسب زمان بر است و هم بعضاً مطلق سازي بي دليل آدرس، در محاسبات حجيم و گستره باعث بروز خطاهاي فاحش مي‌شود.

منبع: omransoft.ir