آموزش جامع فرمول ARRAY_CONSTRAIN در گوگل شیت

• آیا تا به حال نیاز داشته‌اید که فقط بخش مشخصی از یک آرایه یا نتیجه یک فرمول بزرگ را در گوگل شیت نمایش دهید؟
• چگونه می‌توان بدون کپی و پیست دستی، ابعاد یک محدوده داده را به صورت پویا محدود کرد؟
• فرمول ARRAY_CONSTRAIN در گوگل شیت دقیقاً چه کاری انجام می‌دهد و چگونه می‌تواند گزارش‌های شما را تمیزتر و بهینه‌تر کند؟
• آیا می‌دانید ترکیب این فرمول با توابع دیگری مانند QUERY یا FILTER چه قابلیت‌های قدرتمندی را در اختیار شما قرار می‌دهد؟

در این مقاله جامع، به تمام این سوالات و بیشتر از آن پاسخ خواهیم داد. ما به صورت عمیق و با مثال‌های کاربردی، به شما نشان می‌دهیم که فرمول ARRAY_CONSTRAIN در گوگل شیت چگونه کار می‌کند و چطور می‌توانید از آن برای کنترل دقیق خروجی داده‌های خود استفاده کنید. اگر به دنبال راهی برای مدیریت آرایه‌های بزرگ، ساده‌سازی داشبوردها و افزایش کارایی صفحات گسترده خود هستید، این آموزش برای شما نوشته شده است. بیایید شروع کنیم و این تابع قدرتمند را از نزدیک بررسی کنیم.

فرمول ARRAY_CONSTRAIN در گوگل شیت چیست؟

تصور کنید یک مجموعه داده بسیار بزرگ دارید، مثلاً نتیجه یک فرمول `QUERY` یا `FILTER` که صدها ردیف و ده‌ها ستون را برمی‌گرداند. اما شما برای گزارش خود فقط به ۵ ردیف اول و ۳ ستون اول آن نیاز دارید. در حالت عادی، شاید وسوسه شوید که داده‌ها را کپی کرده و در جای دیگری پیست کنید یا با فرمول‌های پیچیده دیگری تلاش کنید تا خروجی را محدود سازید. اینجاست که فرمول ARRAY_CONSTRAIN در گوگل شیت مانند یک قهرمان وارد می‌شود.

این تابع به شما اجازه می‌دهد تا یک محدوده ورودی (یا یک آرایه حاصل از فرمول دیگر) را گرفته و آن را به ابعاد مشخصی (تعداد ردیف‌ها و ستون‌های دلخواه) محدود کنید. در واقع، این فرمول یک «قاب» یا «محدودیت» روی آرایه شما اعمال می‌کند و فقط بخشی از آن را که شما تعیین کرده‌اید، نمایش می‌دهد. این کار باعث می‌شود صفحات شما تمیزتر، محاسبات سریع‌تر و مدیریت داده‌ها بسیار ساده‌تر شود.

سینتکس (Syntax) فرمول ARRAY_CONSTRAIN

ساختار این فرمول بسیار ساده و قابل فهم است. این فرمول سه آرگومان اصلی دارد:

=ARRAY_CONSTRAIN(input_range, num_rows, num_cols)

بیایید هر یک از این آرگومان‌ها را با دقت بیشتری بررسی کنیم:

• input_range (محدوده ورودی): این اولین و مهم‌ترین آرگومان است. این بخش می‌تواند یک محدوده ثابت از سلول‌ها (مانند `A1:Z1000`) یا خروجی یک فرمول دیگر باشد که یک آرایه تولید می‌کند (مانند `FILTER(A:C, A:A > 100)`). در واقع، هر آرایه‌ای که گوگل شیت تولید می‌کند، می‌تواند به عنوان ورودی این فرمول قرار گیرد.
• num_rows (تعداد ردیف‌ها): این آرگومان یک عدد صحیح است که مشخص می‌کند شما چند ردیف از بالای آرایه ورودی را می‌خواهید نمایش دهید.
• num_cols (تعداد ستون‌ها): این آرگومان نیز یک عدد صحیح است که تعیین می‌کند شما چند ستون از سمت چپ آرایه ورودی را نیاز دارید.

نکته مهم این است که این فرمول همیشه از گوشه بالا سمت چپ محدوده ورودی شروع به کار می‌کند. شما نمی‌توانید با این فرمول به تنهایی، داده‌ها را از وسط یک آرایه استخراج کنید.

مثال‌های کاربردی فرمول ARRAY_CONSTRAIN

تئوری کافی است! بیایید با چند مثال عملی ببینیم این فرمول چطور کار می‌کند. فرض کنید یک مجموعه داده از فروش محصولات در محدوده `A1:D20` داریم که شامل ستون‌های «نام محصول»، «تعداد فروش»، «قیمت واحد» و «درآمد کل» است.

مثال ۱: نمایش ۵ محصول برتر

فرض کنید می‌خواهید فقط ۵ ردیف اول از داده‌های فروش خود را (بدون در نظر گرفتن سرستون) در یک گزارش خلاصه نمایش دهید. داده‌های اصلی شما در محدوده `A2:D20` قرار دارند.

فرمول به این شکل خواهد بود:

=ARRAY_CONSTRAIN(A2:D20, 5, 4)

تحلیل فرمول:

• A2:D20: محدوده داده‌های ما است.
• 5: ما به ۵ ردیف اول نیاز داریم.
• 4: ما هر ۴ ستون موجود را می‌خواهیم.

خروجی این فرمول، یک آرایه جدید با ابعاد ۵ در ۴ خواهد بود که فقط شامل ۵ ردیف اول از داده‌های اصلی شماست.

مثال ۲: نمایش فقط نام و درآمد دو محصول اول

حالا تصور کنید فقط به نام محصول (ستون اول) و درآمد کل (ستون چهارم) برای ۲ محصول اول نیاز دارید. در این حالت، ابتدا باید داده‌ها را مرتب کنیم و سپس `ARRAY_CONSTRAIN` را اعمال کنیم. فرض کنیم ستون نام محصول `A` و ستون درآمد `D` است.

ابتدا با یک فرمول دیگر ستون‌های مورد نظر را انتخاب می‌کنیم (مثلاً با استفاده از `{}`):

={'Sheet1'!A2:A20, 'Sheet1'!D2:D20}

حالا این آرایه مجازی را به فرمول ARRAY_CONSTRAIN در گوگل شیت می‌دهیم:

=ARRAY_CONSTRAIN({'Sheet1'!A2:A20, 'Sheet1'!D2:D20}, 2, 2)

تحلیل فرمول:

• {'Sheet1'!A2:A20, 'Sheet1'!D2:D20}: یک آرایه مجازی ایجاد می‌کند که فقط شامل ستون‌های نام محصول و درآمد است.
• 2: ما فقط ۲ ردیف اول را می‌خواهیم.
• 2: آرایه مجازی ما ۲ ستون دارد و ما هر دو را نیاز داریم.

این فرمول فقط نام و درآمد دو محصول اول را به شما نمایش می‌دهد.

ترکیب ARRAY_CONSTRAIN با توابع قدرتمند دیگر

قدرت واقعی فرمول ARRAY_CONSTRAIN در گوگل شیت زمانی آشکار می‌شود که آن را با توابع آرایه‌ای دیگر مانند `SORT`, `FILTER` و `QUERY` ترکیب کنید. این ترکیب‌ها به شما امکان می‌دهند تا گزارش‌های پویا و بسیار دقیقی بسازید.

ترکیب با تابع SORT

فرض کنید می‌خواهید ۱۰ محصول پرفروش را از لیست فروش خود استخراج کنید. ابتدا باید داده‌ها را بر اساس ستون «تعداد فروش» (فرض کنیم ستون B) به صورت نزولی مرتب کنید و سپس ۱۰ ردیف اول را جدا کنید.

=ARRAY_CONSTRAIN(SORT(A2:D20, 2, FALSE), 10, 4)

تحلیل فرمول:

1. SORT(A2:D20, 2, FALSE): ابتدا کل محدوده `A2:D20` را بر اساس ستون دوم (تعداد فروش) به صورت نزولی (`FALSE`) مرتب می‌کند.
2. ARRAY_CONSTRAIN(...): سپس نتیجه این مرتب‌سازی را به عنوان ورودی می‌گیرد و آن را به ۱۰ ردیف و ۴ ستون محدود می‌کند.

با این فرمول ساده، شما همیشه لیست ۱۰ محصول پرفروش خود را به صورت زنده و به‌روز در اختیار دارید.

ترکیب با تابع FILTER

حالا فرض کنید می‌خواهید فقط ۳ فروش اول مربوط به یک محصول خاص (مثلاً «لپ‌تاپ») را نمایش دهید. ابتدا با `FILTER` داده‌های مربوط به «لپ‌تاپ» را جدا کرده و سپس با `ARRAY_CONSTRAIN` خروجی را محدود می‌کنیم.

=ARRAY_CONSTRAIN(FILTER(A2:D20, A2:A20="لپ‌تاپ"), 3, 4)

تحلیل فرمول:

1. FILTER(A2:D20, A2:A20="لپ‌تاپ"): این بخش تمام ردیف‌هایی را که در ستون `A` مقدار «لپ‌تاپ» را دارند، برمی‌گرداند. خروجی این تابع یک آرایه با تعداد ردیف‌های نامشخص است.
2. ARRAY_CONSTRAIN(...): نتیجه فیلتر را دریافت کرده و آن را به ۳ ردیف اول محدود می‌کند. اگر کمتر از ۳ فروش برای لپ‌تاپ وجود داشته باشد، فرمول فقط همان تعداد موجود را نمایش می‌دهد و خطا نمی‌دهد.

ترکیب با تابع QUERY (پیشرفته)

تابع `QUERY` یکی از قدرتمندترین ابزارهای گوگل شیت است. ترکیب آن با `ARRAY_CONSTRAIN` به شما کنترل بی‌نظیری می‌دهد. فرض کنید می‌خواهید نام محصول و درآمد را برای ۵ محصولی که بیشترین درآمد را داشته‌اند، نمایش دهید.

=ARRAY_CONSTRAIN(QUERY(A1:D20, "SELECT A, D ORDER BY D DESC"), 6, 2)

تحلیل فرمول:

1. QUERY(A1:D20, "SELECT A, D ORDER BY D DESC"): این کوئری ستون‌های `A` (نام محصول) و `D` (درآمد) را انتخاب کرده و نتایج را بر اساس درآمد به صورت نزولی مرتب می‌کند. ما از `A1` شروع کردیم تا سرستون‌ها هم شامل شوند.
2. ARRAY_CONSTRAIN(...): نتیجه کوئری را گرفته و آن را به ۶ ردیف (۱ ردیف برای سرستون + ۵ ردیف برای داده) و ۲ ستون محدود می‌کند.

این روش به شما اجازه می‌دهد تا گزارش‌های بسیار پیچیده و خلاصه‌ای را به سادگی ایجاد کنید.

نکات و ترفندهای مهم در استفاده از ARRAY_CONSTRAIN

• مدیریت خطا: اگر تعداد ردیف‌ها یا ستون‌هایی که درخواست می‌کنید بیشتر از ابعاد آرایه ورودی باشد، فرمول خطا نمی‌دهد. بلکه کل آرایه ورودی را نمایش می‌دهد. این ویژگی برای ساخت داشبوردهای پویا بسیار مفید است.
• استفاده در داشبوردها: این فرمول برای ساخت بخش‌هایی مانند «آخرین ۵ فعالیت» یا «۳ مشتری برتر ماه» در داشبوردها ایده‌آل است. شما می‌توانید محاسبات پیچیده را در یک شیت دیگر انجام دهید و فقط نتیجه نهایی و محدود شده را در داشبورد نمایش دهید.
• بهینه‌سازی عملکرد: وقتی با توابعی کار می‌کنید که آرایه‌های بسیار بزرگی تولید می‌کنند (مانند `IMPORTRANGE` از یک شیت دیگر)، استفاده از فرمول ARRAY_CONSTRAIN در گوگل شیت می‌تواند عملکرد صفحه شما را به شدت بهبود بخشد، زیرا گوگل شیت فقط نیاز به رندر کردن بخش کوچکی از داده‌ها دارد.

جدول مقایسه: ARRAY_CONSTRAIN در مقابل توابع مشابه

شاید بپرسید تفاوت این فرمول با توابع دیگر مانند `INDEX` یا `OFFSET` چیست. جدول زیر به شما کمک می‌کند:

نتیجه‌گیری

فرمول ARRAY_CONSTRAIN در گوگل شیت ابزاری ساده اما فوق‌العاده کاربردی برای هر کسی است که با حجم زیادی از داده‌ها سر و کار دارد. این تابع به شما کمک می‌کند تا خروجی فرمول‌های خود را کنترل کرده، گزارش‌های تمیز و خوانا بسازید و از شلوغی بی‌مورد در صفحات گسترده خود جلوگیری کنید. با ترکیب این فرمول با توابع دیگر، می‌توانید داشبوردهای پویا و هوشمندی طراحی کنید که فقط اطلاعات ضروری را به نمایش می‌گذارند. اکنون که با زیر و بم این تابع آشنا شدید، آن را در پروژه‌های بعدی خود به کار بگیرید و تاثیر آن را در افزایش بهره‌وری و نظم صفحات خود مشاهده کنید.