تابع GROUPBY
داده‌ها را بر اساس یک یا چند ستون گروه‌بندی می‌کند و برای هر گروه جمع، تعداد یا محاسبه‌ی دیگری برمی‌گرداند
ساختار تابع
=GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array], [field_relationship])
برای مشاهده توضیح هر ورودی، نشانگر ماوس را روی نام آن نگه دارید.
توضیح کامل
تابع GROUPBY با یک فرمول، گزارش خلاصه می‌سازد. ستونی را که می‌خواهید بر اساس آن گروه‌بندی شود، ستون اعداد و نوع محاسبه را می‌گیرد و برای هر گروه یک ردیف نتیجه می‌دهد. نتیجه با تغییر داده‌ها خودکار به‌روز می‌شود.
مثال: جمع فروش هر شهر (شهر در ستون B و مبلغ در ستون D)
=GROUPBY(B2:B100, D2:D100, SUM)
مثال: همان گزارش، مرتب‌شده از بیشترین فروش به کمترین
=GROUPBY(B2:B100, D2:D100, SUM, , , -2)
مشخصات فنی
ویژگی‌های فنی و سازگاری تابع
▣
دسته‌بندی اکسلی
جستجو و مرجع
◷
اولین نسخه
Excel 365
↦
نوع خروجی
آرایه
ϟ
Volatile
خیر
⌗
Dynamic Array
بله
★
محبوبیت
۵۵ از ۱۰۰
ورودی‌های تابع
| نام ورودی | نوع | وضعیت | توضیح | ||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
row_fields
|
محدوده | الزامی | ستون یا ستون‌هایی که گروه‌بندی بر اساس آن‌ها انجام می‌شود | ||||||||||||||||||
values
|
محدوده | الزامی | ستون اعدادی که باید محاسبه شوند | ||||||||||||||||||
function
|
هر نوع | الزامی | نوع محاسبه؛ مثل SUM، AVERAGE یا COUNT | ||||||||||||||||||
field_headers
|
عدد | اختیاری | وضعیت عنوان ستون‌هاپیش‌فرض: خودکار | ||||||||||||||||||
|
حالت‌های مجاز ورودی
field_headers۴ حالت
|
|||||||||||||||||||||
total_depth
|
عدد | اختیاری | نمایش جمع کل و جمع‌های جزءپیش‌فرض: خودکار | ||||||||||||||||||
|
حالت‌های مجاز ورودی
total_depth۵ حالت
|
|||||||||||||||||||||
sort_order
|
عدد | اختیاری | شماره‌ی ستونی که مرتب‌سازی بر اساس آن انجام می‌شود (منفی: نزولی) | ||||||||||||||||||
filter_array
|
آرایه | اختیاری | شرطی که مشخص می‌کند کدام ردیف‌ها در محاسبه بیایند | ||||||||||||||||||
field_relationship
|
عدد | اختیاری | رابطه‌ی ستون‌ها وقتی گروه‌بندی چندستونی استپیش‌فرض: 0 | ||||||||||||||||||
|
حالت‌های مجاز ورودی
field_relationship۲ حالت
|
|||||||||||||||||||||
داده‌ها را بر اساس یک یا چند ستون گروه‌بندی می‌کند و برای هر گروه جمع، تعداد یا محاسبه‌ی دیگری برمی‌گرداند
کاربردهای تابع
✓
گزارش خلاصه
جمع فروش، هزینه یا تعداد به تفکیک شهر، مشتری، محصول یا هر گروه دیگر
✓
رتبه‌بندی گروه‌ها
مرتب کردن گروه‌ها از بیشترین به کمترین برای پیدا کردن برترین‌ها
✓
جایگزین PivotTable ساده
گزارشی که بدون Refresh همیشه به‌روز است
خطاهای رایج و راه‌حل
#VALUE!
تعداد سطرهای محدوده‌های ورودی یکسان نیست
راه‌حل: هم‌اندازه کردن محدوده‌ها
#SPILL!
سلول‌هایی که نتیجه باید در آن‌ها پخش شود خالی نیستند
راه‌حل: خالی کردن سلول‌های کناری و پایینی فرمول
#NAME?
این تابع در نسخه اکسل شما وجود ندارد
راه‌حل: استفاده از Microsoft 365
نکات مهم
- این تابع به PivotTable ربطی ندارد و نیازی به Refresh ندارد.
- تعداد سطرهای ورودی اول و دوم باید یکسان باشد.
- عدد ورودی ششم شماره‌ی ستونی از نتیجه است که مرتب‌سازی بر اساس آن انجام می‌شود؛ عدد منفی یعنی نزولی.
- برای حذف ردیف جمع کل، ورودی پنجم را 0 بنویسید.
توضیحات بیشتر
در ورودی سوم می‌توان نام توابعی مثل SUM، AVERAGE، COUNT، MAX و MIN را نوشت. اگر ورودی اول چند ستون باشد، گروه‌بندی چندسطحی انجام می‌شود. ردیف جمع کل به صورت پیش‌فرض در انتهای نتیجه اضافه می‌شود.