هیچچیز بدتر از این نیست که یک فایل Excel را که ماهها روی آن کار کردهاید باز کنید و فقط با واردکردن یک مقدار، نشانگر ماوس از حرکت بایستد و Excel هنگ کند. کارهای سادهای مثل واردکردن مقادیر جدید یا جمعزدن چند سلول، بیشتر از حد معمول طول میکشند و فشار بیشتری از حد لازم به سیستم وارد میکنند.
ممکن است تصور کنید هر بار که با چنین کندی مواجه میشوید، باید یک ماکرو در Excel بسازید، فرمول خود را از نو بنویسید یا حتی برای بخش IT تیکت ثبت کنید. بااینحال، من متوجه شدهام که در بسیاری از مواقع، اضافهکردن دو نقطه کوچک به فرمولها برای برطرفکردن این مشکل کافی است. وقتی متوجه شوید این دو نقطه چگونه کار میکنند، احتمالاً از این به بعد شیوه نوشتن فرمولهای Excel را تغییر خواهید داد.
در ادامه کاربرد نقطه در ریفرنس دادن به سلولها در اکسل را توضیح میدهیم. با اینتوتک همراه باشید.
TRIMRANGE و Trim References
دو نقطهای که به Excel میگویند وقتش را برای سلولهای خالی هدر ندهد
وقتی به یک ستون کامل مانند A:A ارجاع میدهید، Excel سلولهای خالی زیر دادههای شما را نادیده نمیگیرد. در عوض، هر بار که فرمول دوباره محاسبه میشود، تمام سلولهای آن ستون را پردازش میکند؛ یعنی بیش از یک میلیون ردیف خالی.
راهکار مایکروسافت برای این مشکل، تابع TRIMRANGE است. این تابع برای حذف ردیفها و ستونهای خالی از لبههای بیرونی یک محدوده طراحی شده است. به این ترتیب، آرایههای پویا در Excel دیگر صفرهای اضافی را در انتهای محدوده نمایش نمیدهند و فرمولها هم منابع سیستم را صرف پردازش فضای خالی نمیکنند.
TRIMRANGE از لبههای محدوده به سمت داخل حرکت میکند تا به داده واقعی برسد. درواقع، محدوده را طوری کوچک میکند که فقط سلولهایی را دربر بگیرد که واقعاً مهم هستند. ساختار این تابع به شکل زیر است:
آرگومانهای مربوط به Trim از یک مقیاس ساده ۰ تا ۳ استفاده میکنند:
- 0 هیچ چیزی را حذف نمیکند.
- 1 فقط سلولهای خالی ابتدای محدوده را حذف میکند.
- 2 فقط سلولهای خالی انتهای محدوده را حذف میکند.
- 3 که حالت پیشفرض است، سلولهای خالی ابتدا و انتهای محدوده را حذف میکند.
این قابلیت بهخودیخود کاربردی است، اما ماجرا حتی بهتر هم میشود. Trim References یا Trim Refs به شما اجازه میدهند همین رفتار را فقط با تغییر نقطه در یک ارجاع معمولی به محدوده فعال کنید. سه حالت مختلف برای این کار وجود دارد:
| نوع | نمونه | عملکرد |
|---|---|---|
| Trim All .:. | A1.:.E10 | سلولهای خالی را از ابتدا و انتهای محدوده حذف میکند. |
| Trim Trailing :. | A1:.E10 | فقط سلولهای خالی انتهای محدوده را حذف میکند. |
| Trim Leading .: | A1.:E10 | فقط سلولهای خالی ابتدای محدوده را حذف میکند. |
در عمل، احتمالاً بیشتر از نسخه Trim Trailing استفاده خواهید کرد. بیشتر صفحات گسترده با اضافهشدن ردیفهای جدید به سمت پایین رشد میکنند. بنابراین فضای خالی که معمولاً میخواهید Excel آن را نادیده بگیرد، پایین دادهها قرار دارد، نه بالای آنها.
Trim Refs قابلیت جدیدی ارائه نمیکند که TRIMRANGE نتواند آن را انجام دهد. مزیت اصلی آن این است که بهجای اینکه ارجاع خود را داخل یک تابع کامل TRIMRANGE قرار دهید، میتوانید فقط با چند کلید همین رفتار را فعال کنید.
A.:.A یا A:.A چگونه مشکل کندی ارجاع به ستون کامل را حل میکند؟
سرعت فرمولهای ستون کامل، LAMBDA و آرایههای پویا را افزایش دهید
ارجاع به یک ستون کامل بسیار راحت است، اما بهسادگی میتواند به یک مشکل عملکردی تبدیل شود. بهعنوان مثال، فرمول زیر را درنظر بگیرید:
فرض کنید میخواهید طول متن تمام Order IDها را در ستون G بررسی کنید تا مطمئن شوید همه آنها با فرمت درست وارد شدهاند. این فرمول در نگاه اول کاملاً بیضرر بهنظر میرسد، اما Excel آن را بهعنوان درخواستی برای پردازش تمام ۱٬۰۴۸٬۵۷۶ ردیف ستون G درنظر میگیرد.
این یعنی حتی ردیفهایی که هیچ دادهای ندارند نیز پردازش میشوند. بهعنوان مثال، اگر فقط ۱۰ ردیف دارای اطلاعات باشند، بیش از یک میلیون ردیف دیگر همچنان خالی هستند، اما Excel باید آنها را بررسی کند و برای همه آنها نتیجه تولید کند که اغلب بهشکل رشتهای از صفرها دیده میشود.
حالا همین موضوع را در یک فایل Excel با دهها فرمول مشابه تصور کنید. نتیجه چیزی جز یک فایل کند و سنگین نخواهد بود.
حالا ارجاع ستون را با یکی از این دو فرمول جایگزین کنید:
=LEN(G:.G)
=LEN(G.:.G)
رفتار Excel بهشکل محسوسی تغییر میکند.
بهجای اینکه کل ستون را بررسی کند، فقط روی بخش دارای داده تمرکز میکند و بقیه سلولها را طوری نادیده میگیرد که انگار اصلاً وجود ندارند. بنابراین همچنان راحتی استفاده از ارجاع به کل ستون را دارید، اما دیگر با مشکل پردازش بیدلیل بیش از یک میلیون سلول خالی مواجه نیستید.
این روش بهویژه هنگام استفاده از توابع LAMBDA و آرایههای پویا کاربرد دارد. بهعنوان مثال، فرض کنید یک تابع LAMBDA سفارشی به نام COUNTCHAR ساختهاید که تعداد دفعات ظاهرشدن یک حرف مشخص در یک بخش متنی را میشمارد.
حالا میخواهید ستون Order Priority را به آن بدهید تا ببینید حرف H چند بار در موجودی شما ظاهر شده است. بدون استفاده از Trim Refs ممکن است فرمولی شبیه این بنویسید:
=BYROW(E:E, LAMBDA(row, COUNTCHAR(row, "H")))
مشکل اینجاست که توابع LAMBDA و توابع کمکی مانند BYROW دادهها را بهصورت ترتیبی پردازش میکنند. وقتی E:E را به آن میدهید، منطق سفارشی شما مجبور میشود بیش از یک میلیون ردیف را بررسی کند؛ درحالیکه بیشتر این ردیفها کاملاً خالی هستند.
Excel همچنان باید این ردیفها را در پسزمینه پردازش کند و همین موضوع میتواند محاسبات را کند کرده و حتی باعث شود فایل Excel برای مدتی پاسخگو نباشد.
استفاده از Trim Ref این مشکل را برطرف میکند:
=BYROW(E.:.E, LAMBDA(row, COUNTCHAR(row, "H")))
ازآنجاکه E.:.E بهصورت خودکار محدوده را به سلولهایی که واقعاً دارای داده هستند محدود میکند، حلقه LAMBDA بسیار سریعتر تمام میشود.
بهجای اینکه Excel وقت خود را صرف بررسی میلیونها ردیف خالی کند، فقط روی دادههایی تمرکز میکند که واقعاً اهمیت دارند.
Trim Refs برای آرایههای پویا نیز کاربرد دارد. در این حالت، از سرریزشدن فرمولها در محدودههای بسیار بزرگ و پرشدن صفحه با صفرهای اضافی جلوگیری میکند.
بهطور کلی، هر فرمولی که بارها محدودههای بزرگ را بررسی میکند، گزینه مناسبی برای استفاده از Trim Ref است. محدودکردن محاسبات به سلولهایی که واقعاً دارای داده هستند، میتواند در فایلهای Excel که محاسبات سنگینی دارند، تفاوت محسوسی ایجاد کند.
البته این به این معنی نیست که باید استفاده از ارجاع به کل ستون را کاملاً کنار بگذارید. ارجاعهایی مثل A:A همچنان یکی از سریعترین و انعطافپذیرترین روشها برای ساخت فرمول هستند.
روش بهتر این است که هنگام ساخت یک فرمول ساده و سریع همچنان از A:A استفاده کنید، اما اگر متوجه شدید فایل Excel شما بهتدریج کند شده است، سراغ A:.A بروید.
قبل از بازنویسی فایل Excel، اول اضافهکردن دو نقطه را امتحان کنید
چیزی که این راهکار را جذاب میکند، سادگی آن است. TRIMRANGE کنترل دقیق و کاملی روی بخشهایی از محدوده که باید نادیده گرفته شوند دراختیار شما میگذارد. Trim Refs هم همین قابلیت را در قالبی بسیار سادهتر ارائه میکند و تنها با اضافهکردن چند نقطه به ارجاعی که همین حالا در فرمول خود استفاده میکنید، میتوانید آن را فعال کنید.
بنابراین دفعه بعد که احساس کردید فایل Excel شما بهکندی کار میکند و دیگر مثل قبل سریع نیست، نوار فرمول را باز کنید و بهدنبال ارجاعهای مربوط به کل ستون بگردید. سپس اضافهکردن دو نقطه را امتحان کنید.
ممکن است این کوچکترین تغییری باشد که در فایل خود ایجاد میکنید، اما درعینحال میتواند همان تغییری باشد که بیشترین زمان را برایتان ذخیره میکند.
اینتوتک