شاید فکر کنید که PivotTables سادهترین و کماسترسترین روش برای خلاصهسازی دادهها در Excel هستند. مدت طولانیایی این را هم باور داشتم. این موضوع زمانی تغییر کرد که به اندازه کافی در استفاده از GROUPBY و PIVOTBY راحت شدم. این توابع به من این امکان را میدهند که خلاصههایی ایجاد کنم که مثل بخشهای زندهٔ برگه کاری من رفتار میکنند، نه جزایر گزارش جداگانهای که هر بار دادههای پایه تغییر میکنند نیاز به تازهسازی، بازسازی یا تنظیم دارند.
شما ممکن است فکر کنید PivotTables آسانترین و کماسترسترین راه برای خلاصهسازی دادهها در Excel است. برای مدت طولانی من هم همینطور فکر میکردم. این مسأله وقتی تغییر کرد که بهانداز کافی با استفاده از GROUPBY و PIVOTBY راحت شدم. این توابع به من اجازه میدهند خلاصهها را بسازم که مانند بخشهای زندهٔ کاربرگم رفتار میکنند نه جزیرههای گزارش جداگانهای که هر بار دادههای پایه تغییر میکنند نیاز به تازهسازی، بازسازی یا تنظیم دارند.
من نمیگویم PivotTables دیگر بیفایده است، زیرا هنوز برخی مشکلات را بسیار خوب حل میکند. اما پس از اینکه دیدم GROUPBY و PIVOTBY چقدر انعطافپذیر، پاسخگو و قابل سازگاری هستند، متوجه شدم که ابتدا آنها را به کار میگیرم و کمتر از PivotTables استفاده میکنم نسبت به قبل.
GROUPBY: شگفتی تک‑محور
چون چه کسی واقعاً به تزیینات و جزئیات برای خلاصههای پایه نیاز دارد
تابع GROUPBY برای تولید گزارشها با دستهبندیهای سطری و مقادیر تجمیعشده ساخته شده است. من آن را بهعنوان یک جایگزین پرسرعت برای PivotTables ساده که فقط به تجزیه سطر‑محور نیاز دارند میدانم.
همچنین وظایفی را که PivotTables در آنها مشکل دارند، مانند تجمیع متن، بر عهده میگیرد. برای مثال، میتوانید فهرستی از نمایندگان فروش برای هر تیم فروش به صورت لیست کاما‑جدا شده ایجاد کنید با ترکیب GROUPBY با ARRAYTOTEXT یا تابع TEXTJOIN در Excel.
نحوۀ پایه به این شکل است:
=GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array], [field_relationship])
باوجود این فهرست طولانی از آرگومانها، GROUPBY فقط به سه ورودی اجباری نیاز دارد: آرگومان row_fields محدودهای را که Excel برای گروهبندی سطرها و تولید سرعنوانها استفاده میکند تعریف میکند؛ آرگومان values دادهای را که میخواهید تجمیع کنید مشخص میکند؛ و آرگومان function نحوهٔ انجام این تجمیع را تعیین میکند، چه یک SUM ساده باشد، یک محاسبهٔ PERCENTOF یا حتی یک LAMBDA سفارشی. وقتی میخواهم کنترل بیشتری داشته باشم، آرگومانهای اختیاری به من امکان تنظیم دقیق سرعنوانها، مجموعها، مرتبسازی و ارائه کلی را میدهند.
اکثر مردم از LAMBDA صرفنظر میکنند، اما نسبت به ظاهر سادهتر است و میتواند کارهای روزمرهٔ شما در Excel را بهبود بخشد.
فرض کنید میخواهید فروش کل هر نماینده فروش را محاسبه کنید و نتایج را از بیشترین به کمترین مرتب کنید. میتوانید این کار را با یک فرمول انجام دهید:
=GROUPBY(B2:B37, E2:E37, SUM, 3, 0, -2)
در اینجا، آرگومان row_fields از ستون نمایندهٔ فروش استخراج میشود، در حالی که آرگومان values به ستون فروش ارجاع میدهد. چون تابع تجمیع SUM است، Excel مقادیر فروش هر نماینده را جمع میکند. عدد ۳ به Excel میگوید تا سرعنوانها را در خروجی نشان دهد؛ ۰ ردیف مجموع کلی را مخفی میکند؛ و -۲ نتایج را بر اساس ستون دوم (فروش) به صورت نزولی مرتب میکند. نتیجه یک خلاصهٔ تمیز است که مستقیماً در کاربرگ شما ظاهر میشود و هر زمان دادههای منبع تغییر کنند بهطور خودکار بهروز میشود.
با این حال، GROUPBY محدودیتهایی دارد. اگر ستونهایی که میخواهید گروهبندی کنید در دادههای منبع بهصورت پیوسته نباشند، باید آنها را با HSTACK ترکیب کنید که فرمول را پیچیدهتر میکند. چون GROUPBY یک آرایهٔ پویا برمیگرداند، نمیتوانید آن را با ویژگی «Format as Table» در Excel استفاده کنید، بنابراین باید هر سلول را بهصورت دستی قالببندی کنید. دسترسی نیز نکتهٔ دیگری است: GROUPBY فقط در Microsoft 365 و Excel 2024 موجود است، بنابراین هرکسی که فایل شما را در Excel 2021 یا نسخههای قبلی باز کند، بهجای نتایج خطا خواهد دید.
با این حال هیچیک از اینها برای من مانعی نیستند. تنها مانعی که ظاهر میشود این است که وقتی نیاز دارم خلاصههایم را همزمان در سطرها و ستونها پخش کنم، که دقیقاً همان زمان است که به PIVOTBY انتقال میدهم.
PIVOTBY: حرکت قدرتمند دو‑محور
دستآورد پیشرو در خلاصههای دادهای
PIVOTBY تمام قابلیتهای GROUPBY را با افزودن محور دوم برای ستونها گسترش میدهد. این معادل فرمولی یک PivotTable کاملویژگی با هر دو فیلد ردیف و ستون است. وقتی گزارشی بسیار طولانی میشود و اسکرول کردن دشوار است، PIVOTBY به من امکان میدهد یک دستهبندی، مانند سال، را به محور ستون منتقل کنم و چیدهسازیای فراهم کنم که خواندن آن آسانتر باشد.
این انعطافپذیری افزوده با یک نحو پیچیدهتر همراه است، زیرا تابع از گروهبندی در دو بُعد پشتیبانی میکند:
PIVOTBY(row_fields, col_fields, values, function, [field_headers], [row_total_depth], [row_sort_order], [col_total_depth], [col_sort_order], [filter_array], [relative_to])
علاوه بر سه آرگومان اجباری مورد استفاده در GROUPBY، PIVOTBY همچنین به آرگومان col_fields برای تعریف نحوهٔ گروهبندی دادهها در ستونها نیاز دارد. یکی از بزرگترین مزایای آن نسبت به PivotTables نحوهٔ مدیریت درصدهاست. بهجای پیمایش منوها برای نمایش مقادیر بهعنوان درصدی از مجموع، PIVOTBY یک آرگومان relative_to شامل میشود که به شما امکان محاسبهٔ درصدهای مجموع سطرها، مجموع ستونها، مجموع کلی یا مجموع والدین را مستقیماً در داخل فرمول میدهد.
در مجموع، PIVOTBY یازده آرگومان دارد که به شما کنترل سرعنوانهای ستون، مجموعها و مرتبسازی را میدهد. تنظیم همهٔ اینها، بهویژه وقتی تازهکار هستید، میتواند پیچیدهتر از ساختن یک PivotTable بهنظر برسد، اما اکثر این آرگومانها اختیاری هستند. برای مثال، اگر بخواهید فروش بر حسب نمایندهٔ فروش را در سالهای مختلف ببینید، میتوانید از فرمولی به سادگی زیر استفاده کنید:
Excel را آسانتر کنید: برای راهنمای GROUPBY/PIVOTBY مشترک شوید
Excel را آسانتر کنید: برای راهنمای GROUPBY/PIVOTBY مشترک شوید
با اشتراک، شما موافقت میکنید که خبرنامه و ایمیلهای بازاریابی دریافت کنید و Terms of Use و Privacy Policy Valnet را بپذیرید. میتوانید در هر زمان اشتراک خود را لغو کنید.
=PIVOTBY(B2:B37, YEAR(A2:A37), E2:E37, SUM)
این یک خلاصه ایجاد میکند که نمایندگان فروش در سطرها فهرست شدهاند و سالها در ستونها نمایش داده میشوند. فرمول براساس ستون نمایندهٔ فروش برای سطرها گروهبندی میکند، سال را از ستون تاریخ استخراج میکند تا سرعنوان ستونها را تولید کند و مقادیر فروش را جمع میزند. چون نتیجه بهعنوان یک آرایهٔ پویا استاندارد پخش میشود، میتوانم آن را بهصورت مستقیم در تقریباً هر نوع نموداری، از جمله نمودارهای نقشه، بدون تنظیمات اضافی وارد کنم.
تغییرات کوچک، تأثیر بزرگ.
با این حال، PIVOTBY بدون معایبی نیست. بر خلاف PivotTables که میتوانند بهصورت خودکار تاریخها را بر اساس ماه یا سهماهه با کلیک راست گروهبندی کنند، PIVOTBY از شما میخواهد تا تاریخها را بهصورت دستی با پوشاندن ستون تاریخ در توابعی مانند YEAR یا TEXT گروهبندی کنید. نحوهٔ نمایش سرعنوانها نیز میتواند کمتر از نمایهای صیقلی که برگهٔ Design در PivotTable تولید میکند، شهودی باشد. کارایی نیز نکتهٔ دیگری است: برای مجموعهدادههای بسیار بزرگ، PivotTables اغلب تجمیع را کارآمدتر انجام میدهند زیرا از یک حافظهٔ موقت اختصاصی استفاده میکنند، در حالی که فرمولهای آرایهٔ پویا سنگین میتوانند گاهی سرعت کتاب کار شما را کم کنند.
PivotTables منسوخ نشدهاند، اما ممکن است زمان بیشتری روی نیمکت بگذرانند
PivotTables هنوز برای کسی که ترجیح میدهد از فرمولها استفاده نکند، آسانتر ساخته میشود و در گروهبندی خودکار تاریخها یا مدیریت مجموعهدادههای خارجی بزرگ از منابعی مانند SQL Server یا Power BI مهارت دارد. همچنین ویژگیهای تعاملی داخلی مانند drill‑down را ارائه میدهند که تنها با فرمولها سخت است بازتولید شوند.
با این حال، من اکنون کمتر از آنها استفاده میکنم. GROUPBY و PIVOTBY بهصورت خودکار بهروزرسانی میشوند، وظایفی را که PivotTables نمیتواند انجام دهد، مانند برگرداندن متنی با کاما با ARRAYTOTEXT یا اجرای محاسبات سفارشی LAMBDA بدون مدل داده، بر عهده میگیرند و مستقیماً به هر نوع نموداری میخرند. اگر زمان بگذارید تا آنها را مسلط شوید، ممکن است متوجه شوید که این همانطور برای شما نیز صادق است.