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

• چگونه می‌توانم N رکورد برتر یا بدترین را در گوگل شیتس استخراج کنم؟
• فرمول SORTN چه تفاوتی با SORT معمولی دارد و چرا برای برخی نیازها مناسب‌تر است؟
• چگونه می‌توان حالت‌های تساوی (Ties) را در هنگام مرتب‌سازی N رکورد مدیریت کرد؟
• آیا می‌توان رکوردهای منحصربه‌فرد را در بین N رکورد برتر یا بدترین فیلتر کرد؟
• پارامترهای مختلف فرمول SORTN چه کاربردها و تأثیری بر نتیجه نهایی دارند؟

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

فرمول SORTN در گوگل شیت چیست و چرا به آن نیاز داریم؟

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

تصور کنید لیستی از فروشندگان دارید و می‌خواهید تنها ۵ فروشنده برتر را ببینید، یا در یک جدول نمرات دانش‌آموزان، ۱۰ نمره بالاتر را بدون نیاز به مرتب‌سازی کل لیست استخراج کنید. در چنین سناریوهایی، SORTN به عنوان یک راه حل سریع و قدرتمند عمل می‌کند. مزیت اصلی این فرمول در قابلیت مدیریت حرفه‌ای حالت‌های تساوی (ties) و همچنین توانایی استخراج ردیف‌های منحصربه‌فرد در بین N رکورد برتر است که آن را از سایر روش‌های مرتب‌سازی متمایز می‌کند.

ساختار و پارامترهای فرمول SORTN

ساختار کلی فرمول SORTN به شکل زیر است:

SORTN(range, [n], [display_ties_mode], [sort_column1, is_ascending1], ...)

در ادامه، به توضیح هر یک از پارامترها می‌پردازیم:

• range (محدوده): این پارامتر اجباری است و به محدوده‌ای از سلول‌ها اشاره دارد که می‌خواهید مرتب‌سازی و فیلتر را روی آن انجام دهید. این محدوده می‌تواند شامل یک یا چند ستون باشد.
• n (تعداد): این پارامتر اختیاری است و عددی را مشخص می‌کند که نشان‌دهنده تعداد ردیف‌هایی است که می‌خواهید از بالای لیست مرتب‌شده بازگردانده شوند. اگر این پارامتر حذف شود، به طور پیش‌فرض روی ۱ تنظیم می‌شود، یعنی فقط یک ردیف برتر را برمی‌گرداند.
• display_ties_mode (حالت نمایش تساوی‌ها): این پارامتر نیز اختیاری و یکی از مهمترین ویژگی‌های SORTN است. این پارامتر نحوه برخورد با ردیف‌هایی را که در رتبه Nام دارای مقدار مشابه (تساوی) هستند، تعیین می‌کند. این پارامتر می‌تواند یکی از ۴ مقدار زیر را داشته باشد:
  • ۰ (پیش‌فرض): فقط n ردیف را برمی‌گرداند. اگر ردیف Nام با ردیف‌های بعدی برابر باشد، تنها ردیف Nام نمایش داده می‌شود و ردیف‌های دارای تساوی حذف می‌شوند.
  • ۱: n ردیف را به همراه تمامی ردیف‌هایی که با ردیف Nام دارای تساوی هستند، برمی‌گرداند. این حالت برای اطمینان از نمایش همه موارد دارای امتیاز یکسان مفید است.
  • ۲: n ردیف را برمی‌گرداند و ردیف‌های تکراری را حذف می‌کند. این حالت ابتدا ردیف‌های تکراری را بر اساس ستون‌های مرتب‌سازی حذف می‌کند و سپس N ردیف برتر را انتخاب می‌کند.
  • ۳: n ردیف را به همراه تمامی ردیف‌های دارای تساوی با ردیف Nام، با حذف تکراری‌ها، برمی‌گرداند. این حالت ترکیبی از حالت‌های ۱ و ۲ است.
• sort_column1 (ستون مرتب‌سازی ۱): این پارامتر اختیاری است و شاخص ستونی (شماره ستون) در range را مشخص می‌کند که می‌خواهید بر اساس آن مرتب‌سازی اولیه انجام شود.
• is_ascending1 (صعودی/نزولی ۱): این پارامتر نیز اختیاری است و یک مقدار منطقی (TRUE یا FALSE) است که جهت مرتب‌سازی برای sort_column1 را مشخص می‌کند. TRUE به معنای صعودی (کوچکترین به بزرگترین) و FALSE به معنای نزولی (بزرگترین به کوچکترین) است.
• sort_column2, is_ascending2 و غیره: می‌توانید جفت‌های ستون و جهت مرتب‌سازی بیشتری را برای مرتب‌سازی ثانویه، ثالثیه و … مشخص کنید.

کاربردهای عملی فرمول SORTN با مثال‌های گوناگون

یافتن N رکورد برتر/بدترین (Top/Bottom N)

یکی از رایج‌ترین کاربردهای SORTN، استخراج N مقدار برتر یا بدترین از یک مجموعه داده است. فرض کنید جدولی با ستون‌های “نام محصول” و “فروش” دارید و می‌خواهید ۵ محصول با بیشترین فروش را پیدا کنید.

=SORTN(A2:B100, 5, 0, 2, FALSE)

در این مثال:

• A2:B100 محدوده داده‌ها است.
• 5 به این معنی است که ۵ ردیف برتر را می‌خواهیم.
• 0 حالت نمایش تساوی‌ها (فقط ۵ ردیف).
• 2 ستون دوم (فروش) برای مرتب‌سازی است.
• FALSE به معنای مرتب‌سازی نزولی (بیشترین فروش).

مدیریت حالت‌های تساوی (Handling Ties)

پارامتر display_ties_mode انعطاف‌پذیری فوق‌العاده‌ای در برخورد با ردیف‌های دارای تساوی فراهم می‌کند. بیایید فرض کنیم می‌خواهیم ۳ رتبه برتر را ببینیم و چندین نفر امتیاز یکسانی دارند.

جدول داده‌ها:

مثال‌ها برای حالت‌های مختلف display_ties_mode برای استخراج ۳ نفر برتر:

• =SORTN(A2:B8, 3, 0, 2, FALSE) (حالت ۰):

  نتیجه: علی (۹۵)، سارا (۹۰)، رضا (۸۸). (مریم با ۸۸ امتیاز حذف می‌شود زیرا تعداد N به ۳ محدود شده است).

• =SORTN(A2:B8, 3, 1, 2, FALSE) (حالت ۱):

  نتیجه: علی (۹۵)، سارا (۹۰)، رضا (۸۸)، مریم (۸۸). (مریم با رضا دارای امتیاز یکسان ۸۸ است و چون رضا در رتبه سوم قرار می‌گیرد، مریم نیز نمایش داده می‌شود).

• =SORTN(A2:B8, 3, 2, 2, FALSE) (حالت ۲):

  اگر فرض کنیم نام‌ها یا مقادیر دیگری در ستون‌های مرتب‌سازی تکراری باشند و بخواهیم حذف شوند. در این مثال خاص، اگر تنها بر اساس امتیاز مرتب‌سازی کنیم و مقادیر امتیاز منحصر به فرد باشند، این حالت مشابه حالت ۰ عمل می‌کند. اگر ستون دیگری برای تشخیص تکرار وجود داشت، تفاوت مشخص می‌شد. (این حالت ابتدا ردیف‌های تکراری را بر اساس ستون‌های مرتب‌سازی حذف می‌کند و سپس N ردیف برتر را انتخاب می‌کند).

• =SORTN(A2:B8, 3, 3, 2, FALSE) (حالت ۳):

  نتیجه: علی (۹۵)، سارا (۹۰)، رضا (۸۸)، مریم (۸۸). (در این حالت نیز مشابه حالت ۱ عمل می‌کند زیرا در امتیازها تکرار واضحی وجود ندارد که نیاز به حذف داشته باشد و هدف نمایش تساوی‌هاست. اگر داده‌های تکراری در ستون‌های مرتب‌سازی وجود داشتند، این حالت ابتدا تکرارها را حذف می‌کرد و سپس تساوی‌ها را نمایش می‌داد.)

استخراج رکوردهای N منحصربه‌فرد (Unique N Records)

با استفاده از display_ties_mode=2 یا 3، می‌توانید N رکورد برتر را با حذف تکراری‌ها استخراج کنید. این کاربرد زمانی مفید است که می‌خواهید لیست منحصر به فردی از موارد برتر را داشته باشید. به عنوان مثال، اگر چندین محصول با قیمت یکسان وجود دارد و شما می‌خواهید N محصول گران‌تر منحصر به فرد را داشته باشید.

=SORTN(A2:C100, 10, 2, 3, FALSE)

این فرمول ۱۰ ردیف برتر را از محدوده A2:C100، بر اساس ستون سوم (3) به صورت نزولی (FALSE) بازمی‌گرداند و هرگونه ردیف تکراری را قبل از انتخاب N رکورد حذف می‌کند. تکراری بودن بر اساس تمام ستون‌های مرتب‌سازی (در اینجا فقط ستون 3) و ترتیب آنها تعیین می‌شود.

ترکیب SORTN با سایر فرمول‌ها

قدرت SORTN زمانی بیشتر می‌شود که آن را با سایر توابع گوگل شیت ترکیب کنید. به عنوان مثال:

• FILTER و SORTN: ابتدا داده‌ها را با FILTER بر اساس یک شرط خاص فیلتر کنید و سپس با SORTN، N رکورد برتر از داده‌های فیلتر شده را استخراج کنید.

  =SORTN(FILTER(A2:C100, C2:C100 > 50), 5, 0, 2, FALSE)

  این فرمول ۵ رکورد برتر را از محدوده A2:C100 که مقدار ستون C آنها بیشتر از ۵۰ است، استخراج می‌کند.

• QUERY و SORTN: برای فیلتر، مرتب‌سازی و محدود کردن نتایج، می‌توانید از تابع QUERY استفاده کنید که در بسیاری از موارد قدرتمندتر است، اما SORTN برای موارد خاص مدیریت تساوی و تعداد N می‌تواند مکمل خوبی باشد یا جایگزینی ساده‌تر برای برخی کوئری‌ها.

نکات مهم و خطاهای رایج در استفاده از SORTN

• نوع داده‌ها: مطمئن شوید که ستون‌های مرتب‌سازی دارای نوع داده سازگار (مثلاً همه اعداد یا همه متن) هستند. مرتب‌سازی بین انواع داده مختلف می‌تواند نتایج غیرمنتظره‌ای داشته باشد.
• انتخاب محدوده صحیح: همیشه محدوده range را به درستی انتخاب کنید تا شامل تمام ستون‌هایی باشد که می‌خواهید در خروجی نمایش داده شوند و همچنین ستون‌هایی که بر اساس آن‌ها مرتب‌سازی می‌کنید.
• فهم پارامتر display_ties_mode: این پارامتر کلیدی است و فهم دقیق آن برای دستیابی به نتایج مطلوب ضروری است. اشتباه در انتخاب آن می‌تواند منجر به از دست دادن ردیف‌های مهم یا نمایش ردیف‌های ناخواسته شود.
• عملکرد با داده‌های بزرگ: برای مجموعه‌های داده بسیار بزرگ (مثلاً صدها هزار ردیف)، استفاده از SORTN ممکن است کمی کند باشد. در این موارد، استفاده از QUERY می‌تواند کارآمدتر باشد.
• شماره ستون‌ها: به یاد داشته باشید که sort_column شاخص ستون را نسبت به محدوده range می‌پذیرد، نه شماره ستون مطلق در شیت. مثلاً اگر range شما C2:E100 باشد، ستون C اولین ستون (شماره ۱)، ستون D دومین (شماره ۲) و ستون E سومین (شماره ۳) خواهد بود.

مقایسه SORTN با سایر توابع مرتب‌سازی

SORT در مقابل SORTN

• SORT: تمام ردیف‌های محدوده داده شده را بر اساس ستون‌های مشخص شده مرتب می‌کند و کل مجموعه داده مرتب‌شده را برمی‌گرداند.

  =SORT(A2:B100, 2, FALSE)

  این فرمول تمام داده‌های محدوده A2:B100 را بر اساس ستون ۲ به صورت نزولی مرتب می‌کند.

• SORTN: فقط N ردیف برتر یا بدترین را از یک مجموعه داده مرتب‌شده برمی‌گرداند. همچنین قابلیت مدیریت تساوی‌ها و حذف تکراری‌ها را دارد. این تابع برای زمانی مناسب است که فقط به زیرمجموعه‌ای از داده‌های مرتب‌شده نیاز دارید.

QUERY و SORTN

تابع QUERY یکی از قدرتمندترین توابع گوگل شیت است که می‌تواند عملیات فیلتر، مرتب‌سازی، گروه‌بندی و حتی Pivot Table را انجام دهد. QUERY نیز می‌تواند N رکورد برتر را استخراج کند:

=QUERY(A:B, "SELECT A, B ORDER BY B DESC LIMIT 5")

این کوئری ۵ ردیف برتر را از ستون‌های A و B بر اساس ستون B به صورت نزولی برمی‌گرداند. در مقایسه:

• QUERY: بسیار انعطاف‌پذیر است و می‌تواند عملیات پیچیده‌تری را انجام دهد. برای کاربرانی که با SQL آشنایی دارند، یادگیری آن آسان‌تر است. با این حال، مدیریت تساوی‌ها در QUERY به سادگی SORTN و با پارامترهای مشخص آن نیست و ممکن است نیاز به منطق پیچیده‌تری داشته باشد.
• SORTN: برای استخراج N رکورد برتر/بدترین با قابلیت‌های خاص مدیریت تساوی و حذف تکرار، ساده‌تر و مستقیم‌تر است. در مواردی که نیاز به فیلترینگ پیچیده قبل از مرتب‌سازی ندارید و تمرکز بر N رکورد برتر است، SORTN گزینه بهتری است.

نتیجه‌گیری

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