اکثریت کاربران اکسل هنوز از جداول محوری استفاده می‌کنند، اما این عملکرد جدید سریع‌تر است.

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

در یک زمان، جداول محوری بزرگ‌ترین ویژگی در اکسل بودند، اما مانند اکثر ابزارها و ترفندها، دیگر این‌طور نیست. به جای تنظیم یک جدول محوری، تنظیم طرح و به‌روزرسانی آن هر بار که داده‌ها را اضافه یا جایگزین می‌کنید، اکنون می‌توانید یک فرمول واحد بنویسید که همان نتایج را با تلاش و زمان بسیار کمتر تولید کند.

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

PIVOTBY فقط یک جدول محوری دیگر نیست

این یک فرمول واحد است که بلافاصله یک خلاصه کامل می‌سازد

علیرغم تولید خروجی‌های شبیه به هم، PIVOTBY هیچ ارتباطی با ویژگی سنتی PivotTable در اکسل ندارد. این یک فرمول آرایه‌ای پویاست که در Excel برای Microsoft 365، Excel 2024 و Excel 2021 (از جمله نسخه‌های مک) قابل استفاده است و داده‌ها را گروه‌بندی، تجمیع، مرتب‌سازی و فیلتر می‌کند. تنها یک فرمول می‌نویسید، Enter می‌زنید و اکسل جدول خلاصه کامل را مستقیماً در شیت شما پخش می‌کند بدون اینکه نیازی به تنظیمات اضافی باشد.

نحو این‌گونه است:

=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])

فقط به چهار آرگومان نیاز دارید: ستون یا ستون‌هایی که می‌خواهید به‌عنوان ردیف باشند (row_fields)، ستون یا ستون‌هایی که می‌خواهید در بالای جدول باشند (col_fields)، داده‌هایی که می‌خواهید تجمیع کنید (values) و روش تجمیع. این روش می‌تواند چیزی آشنایی مانند SUM، AVERAGE یا COUNT باشد، یا حتی یک تابع سفارشی LAMBDA. سایر آرگومان‌ها اختیاری‌اند و در صورت نیاز می‌توانید از آن‌ها استفاده کنید.

به‌عنوان مثال، فرض کنید می‌خواهید سود کلی را بر اساس نوع محصول (ردیف‌ها) و کانال فروش (ستون‌ها) خلاصه کنید و بر اساس بیشترین سود مرتب کنید. فرمول به این شکل خواهد بود:

=PIVOTBY(C2:C5000, D2:D5000, N2:N5000, SUM,,,-2)

در این مثال، ستون C شامل انواع محصول، ستون D شامل کانال فروش و ستون N شامل سودهاست. مقدار ‑2 در آرگومان مرتب‌سازی می‌گوید اکسل نتایج را به‌صورت نزولی مرتب کند. همچنین قبل از آن سه کاما وجود دارد چون آرگومان row_sort_order در موقعیت هفتم قرار دارد و برای آرگومان‌های میان‌گذار باید نگهدارنده (placeholder) قرار داد.

همانند یک جدول محوری سنتی، می‌توانید ترکیبی از چندین گروه‌بندی ردیف و ستون داشته باشید:

=PIVOTBY(HSTACK(YEAR(F2:F5000), C2:C5000), D2:E5000, N2:N5000, SUM)

در اینجا، سال سفارش از ستون F را همراه با انواع محصول از ستون C به‌عنوان دسته‌بندی ردیف‌ها ترکیب می‌کنم، در حالی که کانال فروش و اولویت سفارش را از ستون‌های D و E به‌عنوان دسته‌بندی ستون‌ها استفاده می‌کنم. چون گروه‌بندی ردیف‌ها از ستون‌های جداگانه می‌آیند، از تابع HSTACK برای ترکیب آن‌ها در یک آرایه استفاده کرده‌ام. همان‌طور که گروه‌بندی ستون‌ها در کنار هم هستند، فقط محدوده را گسترش دادم. همچنین بازه تاریخ سفارش را داخل تابع YEAR قرار دادم تا اکسل بر اساس سال گروه‌بندی کند، نه بر اساس تاریخ‌های منفرد.

آیا می‌خواهید توابع تاریخ اکسل را به‌خوبی مسلط شوید؟ این‌ها چند تا از توابع ضروری هستند که باید بدانید.

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

PIVOTBY چگونه در جریان کار من جای می‌گیرد

خلاصه‌های سریع و انعطاف‌پذیر مستقیماً از فرمول‌ها

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

با این حال، جداول محوری سنتی هنوز جای خود را دارند. اگر ورک‌بوک خود را با همکارانی به اشتراک بگذارید که Microsoft 365، Excel 2024 یا Excel 2021 ندارند، PIVOTBY برای آن‌ها کار نمی‌کند. در محیط‌های همکاری که همه با ویرایش فرمول‌ها راحت نیستند، رابط کشیدن و رها کردن یک جدول محوری کلاسیک نگهداری را آسان‌تر می‌کند. همچنین PivotTable‌ها برای تحلیل‌های اکتشافی سریع و سلسله‌مراتب‌های قابل گسترش که دیگران می‌توانند به صورت تعاملی به داده‌ها دسترسی پیدا کنند، مزیت دارند.

در اکثر موارد دیگر، PIVOTBY به‌سوی جریان کار من به‌صورت یکپارچه می‌گنجد. من اغلب از آن برای خلاصه‌سازی یک مجموعه داده واحد استفاده می‌کنم چون نتایج بسیار سریع‌تر تولید می‌شود و نیازی به تنظیمات اضافی نیست. اما همچنین می‌تواند فراتر از یک شیت واحد گسترش یابد. با تو درون‌سازی VSTACK داخل PIVOTBY می‌توانید داده‌های چندین شیت را به‌سرعت ترکیب کنید. برای مثال، اگر داده‌های فروش سه کارمند، Mercy، Mike و Mitchell، در شیت‌های جداگانه داشته باشید و بخواهید درآمد را بر اساس مکان و درجه کارمند خلاصه کنید، می‌توانید این بنویسید:

=PIVOTBY(VSTACK(Mercy!C2:C5, Mike!C2:C5, Mitchell!C2:C5), VSTACK(Mercy!D2:D5, Mike!D2:D5, Mitchell!D2:D5), VSTACK(Mercy!B2:B5, Mike!B2:B5, Mitchell!B2:B5), SUM)

همچنین می‌توانید خلاصه‌هایتان را تعاملی کنید با اتصال آرگومان filter_array به یک منوی کشویی. برای مثال، این فرمول سفارش‌ها را بر اساس کشور و اولویت می‌شمارد، اما فقط برای نوع موردی که در سلول V2 انتخاب شده است:

گزارش MUO: مشترک شوید و هرگز از مهم‌ترین چیزها غافل نمانید

=PIVOTBY(B2:B5000, E2:E5000, G2:G5000, COUNT,,,,,,C2:C5000=V2)

عبارت C2:C5000=V2 به‌عنوان ماسک بولی عمل می‌کند که هر ردیف را ارزیابی می‌کند و فقط رکوردهای منطبق را برمی‌گرداند. اگر می‌خواهید فرمول تمام سفارش‌ها را وقتی V2 شامل «All» باشد نمایش دهد، آرگومان filter_array را داخل یک دستور IF بپیچید:

=PIVOTBY(B2:B5000, E2:E5000, G2:G5000, COUNT,,,,,,IF(V2="all", TRUE, (C2:C5000=V2)))

اگر می‌خواهید خلاصه‌های خود را به‌صورت نمودار ببینید، می‌توانید نمودارهای استاندارد را مستقیماً به بازه پخش شده توسط PIVOTBY متصل کنید، که تضمین می‌کند نمایش‌های شما به‌صورت خودکار به‌روز می‌شوند؛ بنابراین نباید از Pivot Charts غفلت کنید.

وقتی اعداد خسته‌کننده می‌شوند، بگذارید این نمودارها و گراف‌ها صحبت کنند.

راهی سریع‌تر برای خلاصه‌سازی داده‌ها

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

بهترین روش برای آشنایی با PIVOTBY این است که یکی از PivotTableهای موجود خود را بگیرید و با استفاده از این فرمول آن را بازسازی کنید. احتمالاً تعجب خواهید کرد که چقدر ساده‌تر و سریع‌تر می‌توانید آن را بازسازی کنید.

منبع مقاله