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

ابتدا محدوده‌ی داده‌ها را انتخاب کنید، سپس از زبانه‌ی Insert، در بخش Tables، بر روی دکمه‌ی Pivot Table کلیک کنید.

پنجره‌ی Create Pivot Table باز خواهد شد. در این پنجره، محدوده جدول مشخص شده است که در صورت نیاز در همینجا می توانید محدوده را اصلاح کنید.

در قسمت دوم این پنجره، محل قرار گیری جدول محوری را انتخاب کنید که آیا در یک شیت جدید ایجاد شود (New Worksheet) و یا در یکی از شیت های موجود قرار بگیرد (Existing Worksheet) که در اینصورت پس از کلیک بروی علامت فلش سمت راست فیلد Location، شیت مورد نظر را انتخاب کنید.

در قسمت پایین پنجره، با فعال کردن گزینه Add this data to Data Model می توان هنگام گزارش گیری، همزمان از داده های چندین جدول محوری مختلف گزارش گرفت. سپس کلید Ok کنید.

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

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

در قسمت پایین، چهار جعبه یا بخش جهت جاگذاری و چیدمان گزارش قرار گرفته اند.

ایجاد چیدمان گزارش برای جدول محوری

چیدمان گزارش، با درگ کردن و دراپ کردن (Drag & Drop) با ماوس، امکان پذیر است. بدین صورت که پس از کلیک بروی گزینه مورد نظر، کلید ماوس را نگه داشته و گزینه را به سمت جعبه مورد نظر بکشید و در آنجا، کلید ماوس را رها کنید. فیلدهای مورد نظر را به این روش به هر یک از جعبه های چهارگانه گزارش منتقل کنید. هر کدام از این جعبه ها کاربرد خاصی دارد که در ادامه توضیح میدهیم:

ردیف فرمول کلی

هنگام ایجاد جدول محوری، اکسل یک ردیف در زیر این جدول با نام Grand Total اضافه می کند. در این ردیف بصورت پیش فرض مقدار Count در زیر هر ستون نمایش داده میشود. جهت تغییر این تابع، بر روی Pivot Table راست کلیک کرده و سپس گزینه Summarize Values By را انتخاب و سپس بعنوان مثال، بر روی گزینه Sum کلیک کنید تا مقدار جمع هر ستون نمایش داده شود.

تغییر ظاهر جدول پیوت

پس از ساخت جدول محوری یا همان Pivot Table، همانند جدول Table معمولی، با انتخاب آن، اکسل منوی جدیدی با نام Pivot Table Tools به نوار منو بالا صفحه اضافه میکند. این منو شامل دو زیر منو با نام های Analyze و Design میباشد.

جهت تغییر ظاهر جدول بنا به نیاز خود، پس از انتخاب آن، از منوی Design، از قسمت Pivot Table Style Option، با فعال و یا غیر فعال کردن گزینه ها می توانید ظاهری متفاوت به جدول خود بدهید و همچنین از قسمت Pivot Table Styles، با انتخاب یکی از طرح ها و رنگ های پیش فرض، جدول خود را شخصی سازی نمایید.

جهت مخفی کردن بخش Pivot Table Field سمت راست صفحه، پس از کلیک بروی جدول، از منوی Analyze قسمت Show، روی گزینه Field List کلیک کنید. در صورت نیاز به مشاهده این بخش، مجددا روی همین گزینه کلیلک نمایید.
گزینه Field Headers در این قسمت عناوین ستون ها را مخفی می کند که در صورت نیاز به نمایش، مجددا روی این گزینه کلیلک نمایید.

فیلتر سریع اطلاعات

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

در این مثال، جهت مشاهده اطلاعات فروش فقط دو کالا، ابتدا با کلیک بروی علامت منوی کنار گزینه Row Labels (که با توجه به تنظیمات اولیه جدول، لیست کد کالا ها می باشد)، لیست تمامی کدها نمایش داده می شود. در بخش پایینی این لیست که کدها قرار دارند، ابتدا تیک فعال سازی تمام کدها فعال است. در بالای لیست کدها، گزینه Select All قرار دارد، که اگر این تیک غیر فعال شود، تیک تمامی گزینه های دیگر نیز غیر فعال می شود. حالا جهت فیلتر کردن، فقط تیک گزینه های مورد نظر را فعال نمایید.

در این مثال دو کد کالا انتخاب شده است، که با زدن دکمه Ok، تمامی اطلاعات فروش فقط مربوط به این دو کد را مشاهده خواهیم کرد. اطلاعاتی مثل اینکه چه مشتریانی این کالاها را خریده اند و حجم خریدشان چقدر بوده، و همچنین قیمت کالا چقدر بوده است.

چندین فیلتر برای یک گزارش

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

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

مشاهده جزئیات یکی از آیتم ها

یکی دیگر از ویژگی های Pivot Table این است که میتوان داده های مرتبط با هر ردیف از گزارش را، با جزییات مشاهده کرد. به عنوان مثال، جهت مشاهده جزئیات خرید یک مشتری، کافیست روی عدد قسمت Value مرتبط با آن مشتری، دو بار کلیک کنید. با این کار یک شیت جدید ایجاد شده و ریز اطلاعات مربوط به این شخص درون یک جدول، فراخوانی میشود.

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

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

روش دیگر برای دریافت این جزییات، می توانید در Pivot Table با کلیک بروی خانه شامل Value و راست کلیک روی آن، گزینه Show Details را انتخاب کنید، مجددا همین شیت و اطلاعات برایتان باز خواهد شد.

گزارش جزئیات، از فیلد های جعبه Filter

گزارش جزئیات از فیلدهایی که هنگام ساخت جدول Pivot به درون جعبه Filter کشیده شده اند، از روش زیر انجام میشود:

در این مثال، هنگام ساخت جدول، تاریخ به درون جعبه Filter کشیده شده است که برای گزارش گیری با فیلتر تاریخ، ابتدا جدول پیوت را انتخاب نموده، از منوی Pivot Table Tools وارد منوی Analyze شوید. از قسمت Pivot Table، منوی Option را باز کنید، بروی گزینه Show Report Filter Pages کلیک کنید، بلافاصله به تعداد تاریخ های موجود، در فایل تان شیت ایجاد می شود که در هر شیت، جزییات اطلاعات مربوط به آن تاریخ را می توان به تفکیک مشاهده کرد.

استفاده از Timeline در جدول Pivot

در صورت وجود تاریخ در داده ها، می توانید با کمک فیلتر Timeline انواع گزارش ها با بازه های زمانی متفاوت را مشاهده کنید. برای اینکار از منوی Pivot Table Tools، منوی Analyze، قسمت Filter، روی گزینه Timeline کلیک نمایید. اکسل یک نمودار هوشمند زمان ایجاد می کند که در واقع گزارشی بر اساس تاریخ از جدول تان ارائه میدهد. به کمک منوی قرار گرفته روی این نمودار میتوان گزارش هایی بر اساس روز، ماه، فصل و یا سال مشاهده کرد (تاریخ ها بصورت میلادی میباشد).

درصورت نیاز به مشاهده مجدد جدول کلی بدون فیلتر، از منوی Analyze، قسمت Filter، روی گزینه Filter Connection کلیک کرده، از کادر باز شده تیک کنار گزینه تاریخ را برداشته و Ok کنید.

اصلاح داده های جدول Pivot

اصلاح یا تغییر داده ها در جدول Pivot، از درون خود این جدول امکان پذیر نیست و بایستی از طریق داده های اولیه اقدام نمایید.

جهت این کار، به شیت داده های اولیه رجوع کرده و پس از انجام تغییرات، به شیت Pivot Table برگردید. جهت اعمال تغییرات در جدول Pivot Table، از منوی Pivot Table Tools، منوی Analyze، قسمت Data، روی گزینه Refresh کلیک نمایید. اطلاعات جدید جاگذاری خواهد شد.

جهت تغییر محدوده دامنه های انتخابی نیز، از قسمت Data، روی گزینه Change Data Source کلیک نمایید. شیت داده های اولیه باز می شود و کادر تغییر محدوده در اختیار قرار می گیرد. در این قسمت، روش تغییر محدوده جدول همانند Table میباشد، فقس پس از Ok کردن و تایید، بایستی روی گزینه Refresh کلیک نمایید تا اطلاعات جدید، در جدول Pivot جاگذاری گردد.

زیر منوی Action

امکانات منوی Analyze، قسمت Action:

Clear All کل جدول پیوت را پاک می کند.

Clear Filters تمامی فیلتر های اعمال شده روی جدول را غیر فعال کرده و جدول به حالت اولیه نمایش داده میشود.

زیر منوی Select، میتوان کل جدول، ردیف ها، ستون ها، مقدار ها را به تفکیک انتخاب کرد.

Move Pivot Table می توان جدول به شیت های دیگر و یا فایل اکسل دیگر منتقل نمود.

اضافه کردن فیلد محاسباتی

جهت اضافه کردن فیلد محاسباتی به جدول Pivot، از منوی Pivot Table Tools، منوی Analyze، قسمت Calculation، روی گزینه Field, Items & Sets کلیک نمایید. از منوی باز شده گزینه Calculated Field را انتخاب نمایید، در کادر باز شده، ابتدا نام فیلد مورد نظر را وارد کنید و در کادر Formula، فرمول محاسباتی مورد نظر را وارده نمایید. جهت درج فرمول با استفاده از فیلدهای قبلی، میتوانید با انتخاب فیلدها از باکس پایین این بخش و فشردن کلید Insert Filed، آنرا وارد فرمول خود کرد، عملیات محاسباتی را نوشته و سپس Ok کنید.

در این مثال، فیلد “مبلغ فاکتور” ایجاد شده است که فرمول داده شده، حاصل ضرب “حجم خرید کالا” در “قیمت واحد” کالا می باشد.

فیلد “مبلغ فاکتور” اضافه شده و در هر کجای جدول که داده های فرمول آن وجود داشته باشد، نتیجه فرمول در خانه مربوطه نوشته شده است.