من هر بار که دسته پر کردن را میکشیدم، بازی رولت فرمولها را انجام میدادم، به این امید که اکسل هدفم را اشتباه تفسیر نکند. آیا باید ستون را قفل میکردم، سطر را، یا هر دو؟ سپس، علامتهای دلار که در فرمولهایم پراکنده بودند، مثل مینهای زمینی به نظر میرسیدند که منتظر انفجار منطق من هستند. من ساعتهای بیشتری از آنچه میخواهم بپذیرم صرف کردم تا بفهمم چرا یک مرجع بهظاهر کاملاً بیگناه مثل Sheet3!$AB$142 اصرار دارد به چیز اشتباهی اشاره کند.
هر بار که دستگیره پرکردن را میکشیدم، مثل رولت فرمول بازی میکردم تا امید داشتم اکسل نیتهای من را اشتباه تفسیر نکند. آیا باید ستون، ردیف یا هر دو را قفل میکردم؟ سپس، علامتهای دلار که در فرمولهایم پراکنده بودند، مانند مینهای زمینی احساس میشدند که منتظر انفجار منطق من هستند. ساعتهای بیشتری از آنچه میخواهم اعتراف کنم صرف کردم تا بفهمم چرا ارجاعی به ظاهر کاملاً بیگناه مثل Sheet3!$AB$142 اصرار دارد به چیز اشتباه اشاره کند.
آنچه در نهایت به من کمک کرد این چرخه را بشکنم، یادگیری استفاده از محدودههای نامدار در اکسل بود، نه فقط به عنوان لنگرهای ثابت بلکه به عنوان ارجاعات نسبی انعطافپذیر که دقیقاً همانطور که میخواستم رفتار میکردند. وقتی به آنها تکیه کردم، دیگر اکسل را مجبور به محاسبه مجدد همان چیزها هزاران بار نکردم و دیگر نیازی به رمزگشایی آدرسهای سلولی یا شیتی پیچیده برای جلوگیری از فروپاشی فرمول نداشتم.
استفاده از محدودههای نامدار به عنوان ارجاعات ثابت و نسبی
آنها فقط درباره خوانایی نیستند
بیشتر افراد ابتدا درباره محدودههای نامدار به عنوان ابزاری برای خوانایی میآموزند، و من هم همینطور. در ابتدا فرض میکردم آنها فقط روشی برای جایگزینی ارجاعات سلولی پیچیده اکسل مانند A1:A100 با چیزی واضحتر مانند UnitsSold هستند. اما با گذشت زمان متوجه شدم محدودههای نامدار کاری بیش از تمیز کردن یا آسانتر کردن فرمولها انجام میدهند.
اگر تا به حال از $A$1 برای قفل کردن یک ارجاع استفاده کردهاید، میدانید حفظ این علائم دلار در دهها فرمول چقدر خستهکننده است. یک ردیف که از هم میافتد میتواند کل جمعها را بههم بریزد، جستجوها را به مقادیر غلط برساند و بررسی خطا را غیرممکن کند. استفاده از یک محدوده نامدار این مشکلات را از بین میبرد.
زمانی که سلولی دارید که باید همیشه ثابت بماند، مانند تاریخ امروز، نرخ تخفیف، نرخ تبدیل یا هر ورودی ثابت دیگر، میتوانید به آن نامی مانند TodayDate یا DiscountRate اختصاص دهید. وقتی این نام وجود داشته باشد، میتوانید آن را در هر فرمولی بگنجانید و اکسل همیشه مقدار صحیح را از سلول مربوطه استخراج میکند، صرفنظر از مکان فرمول یا چقدر کپی شده باشد:
=B5*(1-(DiscountRate))
محدودههای نامدار همچنین انعطافپذیری بیشتری نسبت به آنچه بسیاری تصور میکنند دارند. آنها محدود به ارجاعات مطلق نیستند؛ میتوانند با تعریفهای دقیق، ارجاعات نسبی را شبیهسازی کنند. به عنوان مثال، اگر در فیلد «Refers to» یک محدوده نامدار چیزی شبیه =!A$1 را استفاده کنید، اکسل همیشه سلول A1 شیتی که فرمول در آن است را برمیگرداند. معمولاً یک محدوده نامدار شامل یک ارجاع مطلق به شیت است مانند =’Sheet1′!$A$1 که آن را برای همیشه به آن شیت وصل میکند. اما اگر یک محدوده نامدار بدون نام شیت تعریف کنید یا از ارجاعات سلولی نسبی داخل تعریف استفاده کنید، میتوانید یک محدوده نامدار نسبی بسازید. این نام را به یک مؤلفه فرمولی قابل استفاده مجدد تبدیل میکند که بسته به شیتی که در آن ظاهر میشود تنظیم میشود.
فرمولهای هوشمند اکسل با سادگی زبان روزمره.
به عنوان مثال، تصور کنید یک کتابکار بودجه دارید که برای هر ماه یک برگه جداگانه دارد، جایی که سلول B7 همیشه درآمد خالص آن ماه را دارد. ممکن است بخواهید روشی ساده و یکسان برای ارجاع به آن مقدار در هر برگه داشته باشید. کلید Ctrl + F3 را فشار دهید یا به منوی فرمولها → مدیریت نامها → جدید بروید. سلول را با نام NetIncome نامگذاری کرده و موارد زیر را در فیلد «Refers to» وارد کنید:
=!$B$7
سپس، روی OK کلیک کنید. علامت تعجب بدون نام شیت به اکسل میگوید به شیت جاری نگاه کند، و چون هر دو ردیف و ستون قفل هستند، نام همیشه به سلول B7 در هر برگی که فرمول استفاده میکند اشاره میکند.
پس از تعریف، میتوانید از NetIncome در هر برگهای از کتابکار استفاده کنید. اگر از یک ارجاع مطلق مانند \”=SheetA!$B$7\” استفاده میکردید و سپس \”=NetIncome\” را در Sheet B تایپ میکردید، اکسل به اشتباه درآمد خالص از Sheet A را برمیگرداند. محدوده نامدار نسبی این مشکل را بهطور کامل حل میکند و اطمینان میدهد که ارجاع بهصورت متنیمحور باقی میماند.
این تکنیک برای زمانی که باید گزارشها یا قالبهایی بسازید که همان ساختار را در چندین برگه تکرار میکند، بهترین است. هر زمان داده کلیدی در همان مکان نسبی ظاهر شود، چه عنوانی در A1، تاریخی در B1، یا مجموعی در Z10، یک محدوده نامدار نسبی فرمولهای شما را تمیز، ثابت و بسیار آسان برای نگهداری نگه میدارد.
برای مدیریت مؤثر توابع ناپایدار
یک نام که هزاران محاسبه مجدد را جایگزین میکند
توابعی مانند TODAY، NOW، OFFSET و INDIRECT هر زمان که کتابکار شما بهروزرسانی شود، مجدداً محاسبه میشوند، حتی اگر هیچیک از پارامترهای آنها تغییر نکرده باشد. اگر در جدول شما تنها چند نمونه از این توابع وجود داشته باشد، همه چیز بهخوبی کار میکند. اما اگر این توابع در هزاران ردیف ظاهر شوند، کتابکار شما شروع به پیچیده شدن میکند.
به جای اینکه \”=TODAY()\” را در هر فرمولی که به تاریخ جاری نیاز دارد بگنجانید، یک بار آن را محاسبه کنید، یا در یک سلول کمکی یا بهعنوان یک محدوده نامدار که از مدیریت نامها ایجاد میشود. سپس میتوانید از نام تعریفشده (مانند \”TodayDate\”) در هرجا که نیاز دارید استفاده کنید. یک محاسبه میتواند به جای صدها یا هزاران محاسبه تکراری عمل کند، که در اصل نسخهٔ جدولمحور کشبینگ مرورگر است. و میتوانید همین کار را برای سایر توابع ناپایدار نیز انجام دهید.
با این حال، اگر بهطور نادرست از محدودههای نامدار استفاده کنید، میتوانند ناپایداری این توابع را افزایش دهند. وقتی یک محدوده نامدار با تابع ناپایدار مانند OFFSET تعریف میکنید، هر فرمولی که به آن نام وابسته باشد نیز ناپایدار میشود، که گاهی میتواند فاجعهبار باشد.
به عنوان مثال، تصور کنید یک محدوده نامدار به نام WholeSales ایجاد میکنید تا تمام دادههای ستون A را بهطور خودکار پوشش دهد همانطور که تعداد ردیفها با OFFSET افزایش یا کاهش مییابد:
=OFFSET($A$2,0,0,COUNTA($A:$A)-1,1)
هر فرمولی که شامل WholeSales باشد، هر بار که کتابکار شما تغییر میکند، مجدداً محاسبه میشود، فقط به ایندلیل اینکه شامل OFFSET است. اگر ده فرمول به WholeSales اشاره کنند، هر دهتا در هر تغییر مجدداً محاسبه میشوند. اگر یک داشبورد پیچیده شامل هزار فرمول متصل به آن محدوده باشد، تمام هزار فرمول بهصورت مداوم محاسبه میشوند، حتی وقتی دادههای ورودی آنها تغییر نکرده باشد. نتیجه محاسبههای مداوم و غیرضروری است که میتواند مدلهای بزرگ یا پیشرفته را کند کند و باعث تاخیر یا سقوط برنامه شود.
بهترین روش این است که از توابع ناپایدار مانند OFFSET در محدودههای نامدار خود اجتناب کنید و به جای آن از جایگزینهای غیرنابایدار مانند INDEX استفاده کنید:
=$A$2:INDEX($A:$A,COUNTA($A:$A))
با این حال، نیازی به اجتناب کامل از توابع ناپایدار ندارید. میتوانید آنها را در محدودههای نامدار زمانی که هدف ضروری دارند استفاده کنید، بهشرطی که هزینهٔ عملکرد را درک کنید و با دقت بهکار ببرید.
هنگامی که اینها را درک کنید، صفحاتگسترده حس زنده بودن میدهند.
جنبهٔ تاریک محدودههای نامدار
قوی، بله، اما بهراحتی میتوان از آن سوءاستفاده کرد
محدودههای نامدار بسیار مفید هستند، اما بهراحتی میتوان دلیل اینکه برخی افراد از آنها کاملاً اجتناب میکنند، را درک کرد. بزرگترین نگرانی این است که وقتی یک کتابکار صدها نام را جمع میکند که هر کدام به سلول یا فرمول متفاوتی اشاره دارند، ردیابی منطق زیرین دشوار میشود. من افراد را شنیدهام که این هرج و مرج را «نیمهاسمپاستا» مینامند و این نام درست است. چندین سناریو میتوانند باعث مشکل شوند، اما اگر از آنها آگاه باشید، یک قدم به جلوگیری از آنها نزدیکتر میشوید.
MUO Report
MUO Report
نخست، محدودههای نامدار میتوانند در سطح برگه (محلی) یا سطح کتابکار (سراسری) باشند. وقتی برگهای را که دارای نامهای محلی است کپی میکنید، اکسل بهصورت خودکار آن نامها را کپی کرده و فیلدهای «Refers to» آنها را بهروزرسانی میکند. بهعنوان مثال، Sheet1!$D$11 به Sheet1 (2)’!$D$11 تبدیل میشود و اگر بعدها نام برگه را تغییر دهید، اکسل ارجاع را مطابقاً تنظیم میکند، مانند Change!$D$11. این رفتار خوب است تا زمانی که شروع به ترکیب کتابکارها یا مواجهه با وضعیتی کنید که یک نام سراسری قبلاً وجود دارد. وقتی به برگهای که دارای نام محلی است که با نام سراسری یکسان است کپی میکنید، اکسل ممکن است از شما بخواهد کدام نام را نگهدارید. در این لحظه انتخاب گزینه صحیح میتواند دشوار باشد، به همین دلیل بهتر است پیش از ترکیب فایلها، نامهای واضح و منحصربهفرد داشته باشید.
اکسل همچنین نامها را بهصورت داخلی ذخیره میکند، بنابراین میتوانید بهراحتی صدها نام استفادهنشده یا یتیم را در کتابکارهای قدیمی یا بهصورت زیاد ویرایششده خود جمع کنید. ایدهٔ خوب این است که بهطور منظم مدیر نامها را تمیز کنید، زیرا اگر به افزودن محدودههای نامدار در بسیاری از فایلها ادامه دهید، بهسراسر میتوانید به بیش از ۱۰۰ هزار نام برسید. در آنجا، عملکرد کند میتواند مسألهای باشد، بسته به نسخهٔ اکسل شما.
این پاکسازی منظم به دلیل دیگری مهم است: وقتی سلولهایی که قبلاً جزئی از یک محدوده نامدار بودهاند را حذف میکنید، نام خود را تعمیر نمیکند. در عوض، به یک ارجاع نامعتبر تبدیل میشود که خطاهای #REF! را در هرجایی که محدوده نامدار ظاهر میشود منتشر میکند، و اینها یکی از رایجترین خطاهای اکسل برای رفع هستند.
این توابع برگههای اکسل شما را مقاوم میکند.
فرمولهای کمتر خراب، برگههای ثابتتر و روشی سادهتر برای ساخت
وقتی بهطور هوشمندانه از محدودههای نامدار استفاده کنید، فرمولهای شما تمیزتر میشوند، برگهها بهراحتی مدیریت میشوند و پارامترهای کلیدی یک خانهٔ متمرکز و واضح خواهند داشت. بهجای گشتن در ردیفها و ستونها برای بهیاد آوردن اینکه کدام سلول نرخ تبدیل یا مقدار آستانه را دارد، میتوانید به نامهای معناداری تکیه کنید که منطق شما را بههر جایگاهی که میرود دنبال میکند.
آنها بهویژه در مدلهای ساختاریافته که ثابتها در یک برگهٔ اختصاصی قرار دارند، قدرتمند هستند، که بهروزرسانیها را ساده میکند و احتمال خطا را بهطرز چشمگیری کاهش میدهد. وقتی نیاز به بررسی یک فرمول داشته باشید، انتخاب نام و فشار دادن Ctrl + G شما را مستقیماً به منبع آن میبرد و دقیقههایی را که ممکن است به ساعتها تبدیل شوند، صرفهجویی میکند.