اطلاعات فروش یک فروشگاه , به فرم زیر در فایل اکسل ثبت می شود .

مدیر فروشگاه می خواهد با ورود اطلاعات جدید , گزارش فروش بصورت خودکار در کاربرگ دیگری خلاصه شود. گزارش باید شامل , تاریخ فروش , مصرف کننده , تعداد و میزان خرید هر مصرف کننده باشد. به نظر شما , بهتر است از کدام ابزار اکسل در تهیه این گزارش استفاده کنیم.

بله , استفاده از Pivot Table (جدول محوری) بهترین گزینه است. تنها مشکل این است که , هر بار که داده های اولیه را تغییر می دهید ، جدول محوری را باید refresh (بازسازی) کنید.

***در کتاب جداول محوری , نحوه باز سازی خودکار جدول محوری را شرح داده ام.***

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

ایجاد جدول اکسل

سلولی از داده های اولیه را انتخاب کنید. از زبانه Insert دکمه‌ Table را کلیک کنید. از کلید های میانبر CTRL+T  نیز می توانید استفاده کنید.

در کادر محاوره ای ظاهر شده , اکسل محدوده کامل داده ها را به صورت خودکار درج می کند ، مطمئن شوید که آدرس درج شده درست است. روی دكمه OK كليك كنيد تا جدول داده ها به جدول اکسل تبدیل شود.

ایجاد جدول محوری

روی یکی از سلول های جدول اکسل کلیک کنید.

زبانه Insert نوار ریبون را کلیک کنید.

دکمه Pivot table را انتخاب کنید.

ادامه مراحل را از تصویر متحرک زیر دنبال کنید:

همانطور که اشاره شد , بعد از هر تغییر در داده های اولیه بایستی جدول محوری را باز سازی کنید. برای این کار , روی یکی از سلول‌های جدول محوری راست كليك کرده و در لیست باز شده گزينه‌‌ Refresh را انتخاب كنيد.

شکل نهایی گزارش

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

و , بهترین راه این است که نکته ها , ترفندها و مثال های کاربردی را که در کتاب جداول محوری ذکر کرده ام و می تواند به شما کمک کند مطالعه کنید.

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

فرمول آرایه ای سلول A2

=INDEX(Data!$B$2:$C$21, MATCH(0, COUNTIFS($B$1:B1, Data!$C$2:$C$21, $A$1:A1, Data!$B$2:$B$21), 0), COLUMN(A1))

چگونه یک فرمول آرایه ای ایجاد کنیم

سلول A2 را انتخاب کنید.

فرمول را Paste کنید.

کلیدهای Ctrl+Shift را همزمان نگه داشته و کلید Enter را فشار دهید.

چگونه فرمول آرایه ای را کپی کنیم

سلول A2 را انتخاب و کپی کنید (Ctrl+c).

سلول B2 را انتخاب کنید.

کلید Paste را کلیک کنید (Ctrl+V).

محدوده A2: B2 را انتخاب کنید.

کلید Copy را کلیک کنید (Ctrl+c).

محدوده A3: B15 را انتخاب کنید.

کلید Paste را کلیک کنید.

فرمول سلول C2

=COUNTIFS(Data!$C$2:$C$21, B2, Data!$B$2:$B$21, A2)

نحوه کپی فرمول در سلول C2

سلول C2 را انتخاب کنید.

فرمول را Paste کنید.

کلید Copy را کلیک کنید (Ctrl+c).

محدوده C3: C15 را انتخاب کنید.

کلید Paste را کلیک کنید (Ctrl+V).

فرمول سلول D2

=SUMIFS(Data!$E$2:$E$21, Data!$C$2:$C$21, B2, Data!$B$2:$B$21, A2)

نحوه کپی فرمول در سلول D2

سلولD2 را انتخاب کنید.

فرمول را Paste کنید.

کلیدCopy را کلیک کنید (Ctrl+c).

محدوده D3: D15 را انتخاب کنید.

کلید Paste را کلیک کنید (Ctrl+V).

در فرمول های آرایه ای به جای آدرس سلولها می توانید از محدوده های نامگذاری شده پویا نیز استفاده کنید.