بهترین فرمولهای اکسل، فرمولهایی هستند که خودشان مفهومشان را توضیح میدهند. به همین دلیل، هر زمان که امکانش باشد از نامهای معنادار برای محدودهها استفاده میکنم. اما ساختن این نامها به صورت تکبهتک، مخصوصاً در فایلهای بزرگ، بیشتر شبیه کار اداری اضافه است تا یک کار مفید. از زمانی که میانبر Ctrl + Shift + F3 را پیدا کردهام، این میانبر به یکی از پرکاربردترین میانبرهای اکسل برای من تبدیل شده است.
با سادهگو همراه باشید تا یکی از شورتکاتهای مفید اکسل و کاربرد آن را توضیح دهیم.
من سعی میکنم برای همه چیز در اکسل یک نام معنادار انتخاب کنم
نامها درک فرمولها را آسانتر میکنند
یکی از بهترین تغییراتی که طی سالها در فایلهای اکسل خودم ایجاد کردهام، عادت به جایگزین کردن ارجاعهای نامفهوم با نامهای معنادار است. برای جدولهای اکسل از ارجاعهای ساختاریافته استفاده میکنم تا ستونها نامهای مشخصی داشته باشند. اما برای سایر بخشها، محدودههای نامگذاریشده معمولاً گزینه مناسبتری هستند.
این کار با موارد ساده شروع میشود. اگر مقداری داشته باشم که در بخشهای مختلف یک فایل استفاده میکنم، معمولاً به جای ارجاع مکرر به همان سلول، یک نام برای آن تعیین میکنم. به عنوان مثال، فرض کنید نرخ مالیات فروش در بخش فرضیات فایل قرار دارد و قرار است از آن برای محاسبه مالیات هر کالا استفاده شود. فرمولی مانند این:
=D2*$H$2
کاملاً درست کار میکند، اما باید به خاطر داشته باشم که سلول $H$2 چه مقداری دارد. بعد از اینکه برای آن سلول نامی تعیین شود، فرمول به این شکل درمیآید:
=D2*Sales_Tax_Rate
هر دو فرمول نتیجه یکسانی دارند، اما فرمول دوم بلافاصله مشخص میکند که این مقدار چه مفهومی دارد. در نتیجه، بررسی فرمول سادهتر میشود و دیگران نیز سریعتر متوجه ساختار فایل خواهند شد.
Ctrl + Shift + F3 عنوانها را به محدودههای نامگذاریشده تبدیل میکند
چند ثانیه آمادهسازی میتواند چند دقیقه کار دستی را حذف کند
تنها ایراد محدودههای نامگذاریشده این است که ساختن آنها به صورت دستی در فایلهای بزرگ چندان بهصرفه نیست. اگر یک فایل شامل دهها مقدار ثابت یا مقدار قابل استفاده مجدد باشد، تعریف کردن تکتک آنها خیلی زود خستهکننده میشود. اینجا است که Ctrl + Shift + F3 وارد عمل میشود.
به جای اینکه هر نام را جداگانه تایپ کنید، اکسل میتواند نامها را از برچسبهایی که از قبل در کاربرگ قرار دادهاید ایجاد کند؛ البته به شرطی که هر برچسب دقیقاً در کنار سلول یا محدودهای باشد که آن را توصیف میکند.
در این مثال، کاربرگ شامل چند مقدار قابل استفاده مجدد است که قرار است در بخشهای مختلف فایل به آنها ارجاع داده شود. پس از انتخاب کل محدوده، شامل برچسبها، Ctrl + Shift + F3 را فشار دهید. اکسل از شما میپرسد برچسبها در کدام بخش قرار دارند. در اینجا برچسبها در ستون سمت چپ هستند، بنابراین گزینه Left column را انتخاب کرده و روی OK کلیک کنید.
در عرض چند ثانیه، هر برچسب به یک محدوده نامگذاریشده تبدیل میشود. برای بررسی سریع نتیجه نیز میتوانید فهرست کشویی Name Box یا Name Manager را باز کنید.
به مسیر زیر بروید:
Formulas > Name Manager
برچسبها باید از قوانین نامگذاری اکسل پیروی کنند
اکسل برای نامهای معتبر قوانین نسبتاً دقیقی دارد و در بعضی موارد، گزینه Create from Selection بخشی از تبدیل را به صورت خودکار انجام میدهد. به عنوان مثال، اکسل میتواند Sales Tax Rate را به Sales_Tax_Rate تبدیل کند.
با این حال، میانبر نمیتواند تمام محدودیتهای نامگذاری اکسل را دور بزند، بنابراین بهتر است پیش از ایجاد نامها موارد زیر را بررسی کنید:
- نام باید با یک حرف یا زیرخط _ شروع شود. پس از آن میتوان از حروف، اعداد، نقطه و زیرخط استفاده کرد.
- نام نباید شبیه ارجاع سلول باشد؛ مانند A1، R1C1 یا Z100.
- نام میتواند حداکثر ۲۵۵ کاراکتر داشته باشد، هرچند در بیشتر فایلها به چنین طولی نیاز نخواهید داشت.
- نامها به حروف بزرگ و کوچک حساس نیستند. بنابراین Sales_Tax_Rate و SALES_TAX_RATE و sales_tax_rate از نظر اکسل یک نام محسوب میشوند و نمیتوانید چند نام را تنها با تغییر حروف بزرگ و کوچک ایجاد کنید.
نوشتن فرمول با نامها بسیار سادهتر است
اکسل حتی هنگام تایپ، نامها را به صورت خودکار پیشنهاد میدهد
بعد از ایجاد نامها، دیگر لازم نیست مدام به محل سلولها فکر کنید. کافی است در فرمول به جای ارجاع سلولی از نام استفاده کنید. به عنوان مثال، اگر جدول شما ستونی با نام Spend داشته باشد و محدودههای نامگذاریشده Sales_Tax_Rate و Discount_Rate را ایجاد کرده باشید، فرمولی مانند:
=([@Spend]*(1+$F$2))*(1-$F$3)
میتواند به این شکل نوشته شود:
=([@Spend]*(1+Sales_Tax_Rate))*(1-Discount_Rate)
در این فرمول، [@Spend] یک ارجاع ساختاریافته است که به مقدار Spend در ردیف فعلی جدول اشاره میکند و Sales_Tax_Rate و Discount_Rate نیز محدودههای نامگذاریشدهای هستند که با Create from Selection ساخته شدهاند.
هر دو فرمول نتیجه یکسانی دارند، اما فرمول دوم بدون نیاز به مراجعه دوباره به کاربرگ، مشخص میکند هر مقدار چه مفهومی دارد.
مزیت دیگر این است که لازم نیست تمام نامهایی را که ایجاد کردهاید به خاطر بسپارید. به محض شروع تایپ فرمول، اکسل نامهای مرتبط را به صورت خودکار پیشنهاد میدهد. به عنوان مثال، اگر بنویسید:
=Disc
اکسل Discount_Rate را به عنوان پیشنهاد تکمیل خودکار نمایش میدهد. سپس میتوانید با کلید Down Arrow آن را انتخاب کنید و با فشردن Tab، نام را وارد فرمول کنید.
یک مزیت دیگر این است که نامهایی که با Create from Selection ساخته میشوند، به صورت پیشفرض در سطح کل فایل تعریف میشوند. بنابراین میتوان از آنها در فرمولهای هر کاربرگ استفاده کرد.
محدودههای نامگذاریشده نیز فقط به سلولهای منفرد محدود نیستند. با پیشرفتهتر شدن فایلهای اکسل، میتوانید از آنها برای محدودهها، مقادیر ثابت و فرمولها نیز استفاده کنید.
من فرمولهای قدیمی را دستی بازنویسی نمیکنم
Apply Names فرمولهای موجود را به صورت خودکار بهروزرسانی میکند
نکته مهم این است که ایجاد محدودههای نامگذاریشده، فرمولهایی را که قبلاً نوشتهاید به صورت خودکار تغییر نمیدهد. به عبارت دیگر، اگر فایل شما از قبل فرمولهایی داشته باشد که در آنها از ارجاع سلولی استفاده شده است، این فرمولها حتی پس از ایجاد نامها نیز همان ارجاعها را حفظ میکنند.
خوشبختانه لازم نیست تکتک فرمولها را ویرایش کنید. به مسیر زیر بروید:
Formulas > Defined Names > Define Name > Apply Names
اکسل به صورت پیشفرض همه نامهای موجود را انتخاب میکند و در بیشتر موارد کافی است روی OK کلیک کنید. سپس اکسل فایل را بررسی کرده و در صورت امکان، ارجاعهای سلولی را با محدودههای نامگذاریشده متناظر جایگزین میکند.
در بیشتر موارد میتوانید دو گزینه پایین پنجره را نیز فعال نگه دارید. گزینه اول باعث میشود اکسل بدون توجه به نوع ارجاع، چه نسبی و چه مطلق، آن را جایگزین کند. گزینه دوم نیز امکان استفاده از نامهای ردیف و ستون را در صورت وجود فراهم میکند.
چند مورد میتواند مانع کار کردن میانبر شود
بیشتر مشکلات بهسادگی قابل رفع هستند
Create from Selection معمولاً قابلیت قابل اعتمادی است، اما اگر نتیجهای که انتظار دارید ایجاد نشد، احتمالاً یکی از موارد زیر علت آن است:
- برچسبها به دادهها نچسبیدهاند. وجود ردیف یا ستون خالی بین برچسبها و دادهها میتواند مانع ایجاد صحیح نامها شود.
- برچسبهای تکراری دارید. نامها باید یکتا باشند. اگر در یک فایل چند برچسب یکسان داشته باشید، اکسل نمیتواند چند ارجاع در سطح فایل با نام یکسان ایجاد کند.
- بعضی برچسبها با قوانین نامگذاری اکسل سازگار نیستند. Create from Selection معمولاً فاصلهها و بعضی کاراکترهای پشتیبانینشده را با زیرخط جایگزین میکند، اما نمیتواند هر نامی را ایجاد کند. اگر نامی را در فهرست پیدا نکردید، Name Manager را بررسی کنید.
- محل برچسبها را اشتباه انتخاب کردهاید. اگر عنوانها در ستون سمت چپ قرار دارند اما گزینه Top row را انتخاب کنید، اکسل نامهایی را که انتظار دارید ایجاد نخواهد کرد. پیش از تأیید، گزینههای موجود در پنجره Create Names from Selection را دوباره بررسی کنید.
نامگذاری محدودهها ارزش این زحمت را دارد
برای من، Ctrl + Shift + F3 تنها بخش آزاردهنده محدودههای نامگذاریشده، یعنی ایجاد دستی آنها، را حذف کرده است. مرحله بعدی استفاده از تابع LET برای اختصاص نام به مقادیر داخل فرمولها و استفاده از LAMBDA برای ساخت توابع سفارشی قابل استفاده مجدد با پارامترهای نامگذاریشده بود.
ترکیب این قابلیتها باعث شده است نوشتن، خواندن و نگهداری فرمولها بسیار سادهتر شود.
سادهگو
