همه می دانند که می توانید یک فرمول را در اکسل وارد کنید. (بالاخره یک صفحه گسترده بدون فرمول چگونه خواهد بود؟) اگر از ارجاعات آدرس در فرمول استفاده می کنید، اگر سلول ها، ردیف ها یا ستون ها را وارد یا حذف کنید، آن مراجع به طور خودکار به روز می شوند و آن تغییرات به نحوی بر مرجع آدرس تأثیر می گذارد. به عنوان مثال، فرمول ساده زیر را در نظر بگیرید:
=IF(A7=B7,"YES","NO")
اگر سلولی را بالای B7 وارد کنید، فرمول به طور خودکار توسط اکسل تنظیم می شود تا به این صورت ظاهر شود:
=IF(A7=B8,"YES","NO")
با این حال، اگر نمی خواهید اکسل فرمول را تنظیم کند، چه؟ ممکن است سعی کنید چند علامت دلار به آدرس اضافه کنید، اما این فقط روی آدرس هایی در فرمول هایی که بعداً کپی می شوند تأثیر می گذارد. اگر سلول هایی را که بر فرمول تأثیر می گذارند وارد یا حذف کنید، روی خود فرمول تأثیر نمی گذارد.
بهترین راه برای ایجاد ارجاعات فرمول "غیر تنظیم" این است که خود فرمول را برای استفاده از توابع مختلف کاربرگ تغییر دهید. به عنوان مثال، می توانید از این فرمول در سلول C7 استفاده کنید:
=IF(INDIRECT("A"&ROW(C7))=INDIRECT("B"&ROW(C7)),"YES","NO")
این فرمول یک آدرس را بر اساس هر سلولی که فرمول در آن ظاهر می شود می سازد. تابع ROW شماره ردیف سلول را برمی گرداند (در این مورد C7، بنابراین مقدار برگشتی 7 است) و سپس از تابع INDIRECT برای ارجاع به آدرس ساخته شده استفاده می شود. مانند A7 و B7. اگر سلول های بالای A7 یا B7 را وارد کنید (یا حذف کنید)، مرجع در سلول C7 مختل نمی شود، زیرا به سادگی یک آدرس کاملاً جدید ایجاد می کند.
روش دیگر استفاده از تابع OFFSET برای ساخت نوع مشابهی از مرجع است:
=IF(OFFSET($A$1,ROW()-1,0)=OFFSET($B$1,ROW()-1,0),"YES","NO")
این فرمول به سادگی به جایی که قرار دارد (در ستون C) نگاه می کند و مقادیر سلول های سمت چپ آن را مقایسه می کند. اگر سلول ها را در ستون A یا B درج یا حذف کنید، این فرمول به طور مشابه مختل نمی شود.
یک رویکرد نهایی (و شاید ظریف ترین) استفاده از فرمول های نامگذاری شده است. این یکی از ویژگی های قابلیت های نامگذاری اکسل است که به ندرت توسط اکثر افراد استفاده می شود. این مراحل را دنبال کنید:
شکل 1. کادر محاوره ای New Name.
=IF(A2=B2,"YES","NO")
- سلول C2 را انتخاب کنید.
- تب Formulas روبان را نمایش دهید.
- در گروه نام های تعریف شده، روی ابزار تعریف نام کلیک کنید. اکسل کادر محاوره ای New Name را نمایش می دهد. (شکل 1 را ببینید.)
- در کادر Name نام CompareMe را وارد کنید . (در صورت تمایل می توانید از نام دیگری استفاده کنید.)
- هر آنچه در کادر Refers To است را پاک کنید و با فرمول زیر جایگزین کنید:
- روی OK کلیک کنید.
در این مرحله شما فرمول نامگذاری شده خود را ایجاد کردید. اکنون می توانید آن را در هر سلولی در ستون C به این صورت استفاده کنید:
=CompareMe
هر چیزی را که در دو سلول سمت چپ وجود دارد مقایسه می کند، درست همانطور که فرمول اصلی شما برای انجام آن طراحی شده است. با این حال بهتر است، هنگام وارد کردن یا حذف سلول ها، فرمول به طور خودکار تنظیم نمی شود.
