تابع TRIMRANGE یکی از قابلیت‌های کاربردی جدید اکسل است که برای مدیریت محدوده‌های داده و حذف ردیف‌ها و ستون‌های خالی از ابتدا و انتهای یک محدوده استفاده می‌شود. این تابع به‌خصوص زمانی مفید است که اطلاعات یک فایل اکسل به‌مرور افزایش پیدا می‌کنند و می‌خواهید فرمول‌ها بدون در نظر گرفتن سلول‌های خالی، نتایج دقیق‌تری نمایش دهند.
یکی از مشکلات رایج هنگام استفاده از توابعی مانند SORT و UNIQUE این است که اگر محدوده بزرگی از سلول‌ها را به فرمول اختصاص دهید، سلول‌های خالی نیز وارد محاسبات می‌شوند و ممکن است نتیجه به شکل صفرهای اضافی نمایش داده شود. تابع TRIMRANGE این مشکل را برطرف می‌کند و به شما اجازه می‌دهد محدوده واقعی داده‌ها را به‌صورت پویا تشخیص دهید.
تابع TRIMRANGE در اکسل چیست و چه کاربردی دارد؟
تابع TRIMRANGE در اکسل برای حذف ردیف‌ها و ستون‌های خالی از لبه‌های یک محدوده استفاده می‌شود. منظور از لبه‌ها، ردیف‌های خالی بالای داده‌ها یا پایین آن‌ها و ستون‌های خالی سمت چپ یا راست محدوده است.
به عنوان مثال، فرض کنید فهرستی از محصولات را در ستون A وارد کرده‌اید و محدوده A1 تا A100 را برای اضافه کردن محصولات جدید در آینده در نظر گرفته‌اید. در حال حاضر فقط ۱۰ محصول ثبت شده‌اند و بقیه سلول‌ها خالی هستند.
اگر از یک فرمول آرایه‌ای پویا برای مرتب‌سازی این محدوده استفاده کنید، ممکن است سلول‌های خالی نیز در نتیجه ظاهر شوند. با استفاده از TRIMRANGE می‌توانید محدوده را به قسمت دارای اطلاعات محدود کنید و از نمایش مقادیر اضافی جلوگیری کنید.
این تابع برای موارد زیر کاربرد دارد:
- مرتب‌سازی فهرست‌های در حال افزایش بدون نمایش صفرهای اضافی
- ترکیب اطلاعات چند شیت بدون انتقال ردیف‌های خالی انتهایی
- استفاده بهینه از توابع آرایه‌ای پویا
- ساخت محدوده‌های پویا بدون نیاز به تعیین دستی آخرین ردیف اطلاعات
البته اگر اطلاعات شما ساختار جدولی معمولی دارند، قابلیت Excel Table همچنان انتخاب مناسبی است؛ زیرا با اضافه شدن اطلاعات جدید، محدوده جدول به‌صورت خودکار گسترش پیدا می‌کند. مزیت TRIMRANGE بیشتر زمانی مشخص می‌شود که بخواهید از محدوده‌های معمولی در فرمول‌های آرایه‌ای پویا استفاده کنید.
آموزش استفاده از تابع TRIMRANGE در اکسل و آشنایی با آرگومان‌ها
ساختار این تابع ساده است و از سه آرگومان تشکیل می‌شود که فقط آرگومان اول آن الزامی است.
فرمول اصلی به شکل زیر نوشته می‌شود:
=TRIMRANGE(range,[trim_rows],[trim_cols])
معنی آرگومان‌های این فرمول به شرح زیر است:
- range: محدوده سلول‌هایی که می‌خواهید ردیف‌ها و ستون‌های خالی اطراف آن حذف شوند.
- trim_rows: مشخص می‌کند ردیف‌های خالی ابتدا و انتهای محدوده چگونه حذف شوند.
- trim_cols: مشخص می‌کند ستون‌های خالی ابتدا و انتهای محدوده چگونه حذف شوند.
دو آرگومان آخر اختیاری هستند و اگر آن‌ها را مشخص نکنید، اکسل به‌صورت پیش‌فرض ردیف‌ها و ستون‌های خالی هر دو طرف محدوده را حذف می‌کند.
مقادیر آرگومان trim_rows و trim_cols چه تفاوتی دارند؟
هر دو آرگومان اختیاری از چهار مقدار عددی پشتیبانی می‌کنند. تفاوت آن‌ها به این بستگی دارد که می‌خواهید سلول‌های خالی ابتدای محدوده، انتهای آن یا هر دو طرف حذف شوند.
|
مقدار |
عملکرد |
|---|---|
| 0 |
هیچ ردیف یا ستونی حذف نمی‌شود |
| 1 |
فقط موارد خالی ابتدای محدوده حذف می‌شوند |
| 2 |
فقط موارد خالی انتهای محدوده حذف می‌شوند |
| 3 |
موارد خالی ابتدا و انتهای محدوده حذف می‌شوند |
به عنوان مثال، اگر بخواهید فقط ردیف‌های خالی انتهای محدوده A1:A100 حذف شوند، می‌توانید از فرمول زیر استفاده کنید:
=TRIMRANGE(A1:A100,2,0)
در این فرمول، عدد ۲ مشخص می‌کند ردیف‌های خالی انتهایی حذف شوند و عدد صفر مانع حذف ستون‌ها می‌شود.
اگر بخواهید هم ردیف‌ها و هم ستون‌های خالی ابتدا و انتهای محدوده حذف شوند، کافی است از فرمول زیر استفاده کنید:
=TRIMRANGE(A1:C100,3,3)
آموزش استفاده از TRIMRANGE همراه با توابع SORT و DROP
یکی از کاربردهای مهم تابع TRIMRANGE ترکیب آن با توابع آرایه‌ای پویا مانند SORT و DROP است. این ترکیب به شما کمک می‌کند فهرست‌های طولانی را مرتب کنید، بدون اینکه ردیف‌های خالی انتهای محدوده وارد نتیجه شوند.
مرتب‌سازی خودکار اطلاعات بدون نمایش صفرهای اضافی
فرض کنید نام محصولات در ستون A قرار دارند و سلول A1 نیز عنوان فهرست است. می‌خواهید محصولات به ترتیب حروف الفبا مرتب شوند و با اضافه شدن محصول جدید، نتیجه نیز به‌صورت خودکار به‌روزرسانی شود.
اگر از فرمول زیر استفاده کنید، کل ستون مرتب می‌شود:
=SORT(A:A)
اما این فرمول عنوان ستون را نیز وارد مرتب‌سازی می‌کند و سلول‌های خالی پایین فهرست ممکن است به شکل صفر نمایش داده شوند.
برای حذف عنوان از محدوده می‌توانید از DROP استفاده کنید:
=SORT(DROP(A:A,1))
با این حال، سلول‌های خالی همچنان در نتیجه باقی می‌مانند.
راه‌حل بهتر استفاده هم‌زمان از سه تابع است:
=SORT(DROP(TRIMRANGE(A:A),1))
در این فرمول:
- TRIMRANGE ردیف‌ها و ستون‌های خالی اطراف اطلاعات را حذف می‌کند.
- DROP ردیف اول را که شامل عنوان فهرست است کنار می‌گذارد.
- SORT اطلاعات باقی‌مانده را به ترتیب حروف الفبا مرتب می‌کند.
به این ترتیب، نتیجه فقط شامل نام محصولات خواهد بود و با اضافه شدن اطلاعات جدید به فهرست اصلی، خروجی نیز به‌روزرسانی می‌شود.
البته بهتر است در فهرست اصلی بین محصولات ردیف خالی وجود نداشته باشد؛ زیرا TRIMRANGE ردیف‌های خالی داخل محدوده را حذف نمی‌کند.
ترکیب اطلاعات چند شیت با تابع TRIMRANGE و VSTACK
اگر اطلاعات مشابهی در چند شیت مختلف دارید و می‌خواهید آن‌ها را در یک محدوده واحد نمایش دهید، تابع VSTACK می‌تواند داده‌ها را به‌صورت عمودی زیر یکدیگر قرار دهد.
مشکل زمانی ایجاد می‌شود که برای هر شیت محدوده بزرگی در نظر گرفته باشید؛ زیرا ردیف‌های خالی انتهای محدوده نیز به نتیجه منتقل می‌شوند.
به عنوان مثال، فرض کنید اطلاعات محصولات در دو شیت Sheet1 و Sheet2 قرار دارند و هر دو محدوده A2 تا C100 را پوشش می‌دهند.
فرمول معمولی به این شکل است:
=VSTACK(Sheet1!A2:C100,Sheet2!A2:C100)
این فرمول اطلاعات هر دو شیت را ترکیب می‌کند؛ اما ردیف‌های خالی نیز در خروجی باقی می‌مانند.
برای برطرف کردن این مشکل، قبل از ترکیب هر محدوده از TRIMRANGE استفاده کنید:
=VSTACK(TRIMRANGE(Sheet1!A2:C100),TRIMRANGE(Sheet2!A2:C100))
در این حالت ابتدا سلول‌های خالی ابتدا و انتهای هر محدوده حذف می‌شوند و سپس VSTACK اطلاعات باقی‌مانده را زیر یکدیگر قرار می‌دهد.
یک نکته مهم درباره تعداد ستون‌ها: محدوده‌های ورودی بهتر است پس از حذف ستون‌های خالی، تعداد ستون‌های یکسانی داشته باشند. اگر یکی از محدوده‌ها باریک‌تر شود، VSTACK برای ستون‌های کمبود از مقدار خطای #N/A استفاده می‌کند. بنابراین بهتر است ساختار اطلاعات شیت‌ها را پیش از ترکیب بررسی کنید.
همچنین ردیف‌های خالی میان اطلاعات همچنان در خروجی باقی می‌مانند؛ زیرا TRIMRANGE فقط لبه‌های محدوده را اصلاح می‌کند.
روش کوتاه‌تر استفاده از TRIMRANGE با عملگر نقطه
علاوه بر تابع TRIMRANGE، اکسل روش کوتاه‌تری برای حذف سلول‌های خالی لبه‌های محدوده ارائه می‌دهد که با نام Trim References شناخته می‌شود.
در این روش به‌جای نوشتن نام تابع، از نقطه در کنار علامت دونقطه در آدرس محدوده استفاده می‌کنید.
به عنوان مثال، فرمول زیر:
=SORT(TRIMRANGE(A1:A100))
را می‌توان به شکل کوتاه‌تر زیر نوشت:
=SORT(A1.:.A100)
در این ساختار، محل قرار گرفتن نقطه مشخص می‌کند که کدام سمت محدوده اصلاح شود.
|
ساختار ارجاع |
عملکرد |
|---|---|
|
A1:.A100 |
حذف ردیف‌ها و ستون‌های خالی انتهایی |
|
A1.:A100 |
حذف ردیف‌ها و ستون‌های خالی ابتدایی |
|
A1.:.A100 |
حذف ردیف‌ها و ستون‌های خالی ابتدا و انتها |
این روش برای فرمول‌های طولانی کاربردی است و می‌تواند خواندن برخی فرمول‌های تو در تو را ساده‌تر کند.
تفاوت Trim References با تابع TRIMRANGE چیست؟
از نظر عملکرد، این دو روش برای حذف سلول‌های خالی از لبه‌های محدوده طراحی شده‌اند و نتیجه یکسانی دارند.
تفاوت اصلی در نحوه نوشتن فرمول است. تابع TRIMRANGE خواناتر است و مشخص می‌کند که چه عملیاتی روی محدوده انجام می‌شود؛ اما عملگر نقطه‌ای فرمول کوتاه‌تری ایجاد می‌کند.
اگر فرمول‌های پیچیده دارید و قرار است افراد دیگری نیز آن‌ها را بررسی کنند، استفاده از TRIMRANGE معمولاً انتخاب قابل‌فهم‌تری است. در مقابل، برای فرمول‌های شخصی و کوتاه می‌توانید از Trim References استفاده کنید.
محدودیت‌های تابع TRIMRANGE در اکسل که باید بدانید
با وجود کاربردهای متعدد، تابع TRIMRANGE محدودیت‌هایی دارد که هنگام طراحی فرمول‌های پیچیده باید به آن‌ها توجه کنید.
- حذف نکردن ردیف‌های خالی داخلی: اگر میان دو ردیف دارای اطلاعات یک ردیف خالی وجود داشته باشد، این تابع آن را حذف نمی‌کند.
- حساسیت به سلول‌های دارای فاصله: اگر در سلولی که ظاهراً خالی است یک فاصله یا کاراکتر نامرئی وجود داشته باشد، ممکن است اکسل آن را خالی تشخیص ندهد.
- خطای محدوده کاملاً خالی: اگر تمام محدوده ورودی خالی باشد، نتیجه می‌تواند خطای #REF! باشد.
- تفاوت عرض محدوده‌ها: هنگام ترکیب چند محدوده با VSTACK ممکن است به دلیل حذف ستون‌های خالی، تعداد ستون‌های خروجی متفاوت شود.
- نیاز به نسخه سازگار اکسل: این تابع برای Excel for Microsoft 365 ارائه شده و نباید انتظار داشته باشید در نسخه‌های قدیمی مانند Excel 2021 یا Excel 2019 در دسترس باشد. همچنین نسخه Google Sheets و LibreOffice Calc از این تابع پشتیبانی نمی‌کنند.
اگر هدف شما حذف تمام ردیف‌های خالی میان اطلاعات است، باید از روش‌های دیگری مانند فیلتر کردن داده‌ها، قابلیت Go To Special یا ترکیب توابعی مانند FILTER استفاده کنید.
در مجموع، TRIMRANGE برای مرتب‌سازی داده‌های پویا، ترکیب اطلاعات چند شیت و مدیریت محدوده‌هایی که قرار است در آینده گسترش پیدا کنند، گزینه بسیار مناسبی است؛ اما جایگزین تمام روش‌های حذف ردیف‌های خالی در اکسل نیست.
اینتوتک