ساختار Pivot Table در اکسل

در مقاله “تعریف و ایجاد Pivot Table در اکسل” با داده های مناسب تولید Pivot Table در اکسل آشنا شدیم، همچنین در مورد Dynamic Range مطالبی را آموختیم و یاد گرفتیم که چطور با استفاده از Table ها میتوان Pivot Table را بوجود آورد. در این مقاله ضمن یادگیری مطالب تکمیلی مبحث قبل، با ساختار Pivot Table در اکسل آشنا میشویم و در مقالات آتی به تفصیل هر بخش را مورد بحث قرار میدهیم.

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

همانطور که از مقاله “تعریف و ایجاد Pivot Table در اکسل” به خاطر داریم، تولید یک Pivot Table در اکسل با انجام چند کلیک ساده میسر میباشد، لیکن درک مفهوم نحوه عملکرد و ساختار Pivot Table مورد نیاز، امری است که نیاز به تمرین بیشتر و تجربه دارد. در اینجا به نکاتی اشاره میکنیم که میتواند در این خصوص مفید باشد:

• پیش از ایجاد Pivot Table در اکسل حتماً داده های ورودی خود (Source Data) را چک کنید. Table ها را بر اساس استاندارد ذکر شده در مقاله جدول در اکسل بررسی کنید و از مواردی مانند خالی نبودن سطر (آیتم)، Unique Key، یکسان بودن نوع داده های یک Field، نام مناسب برای Field ها و… اطمینان حاصل کنید.
• پس از مرتب کردن داده ها و درک آنها، برای Pivot Table خود هدف انتخاب کنید، در این مورد فکر کنید که Pivot Table قرار است چه چیزی را نمایش دهد، چه نوع گزارشی قرار است ایجاد کنید و کدام Field ها قرار است در Pivot Table مورد نظر شما نقش ایفا کنند. برای اینکار میتوانید از Recommended Pivot Table استفاده کنید، این قابلیت به شما کمک میکند درک بهتری از Pivot Table در اکسل داشته باشید.

ساختار Pivot Table در اکسل

به منظور بررسی ساختار Pivot Table در اکسل، یک جدول نمونه را در نظر میگیریم. جدول زیر شامل ۶۱۰ سطر و ۸ ستون یا Field میباشد، در هر سطر شماره ردیف، نام فروشنده، نام کالا، نام شهر محل فروش، سال فروش، قیمت فروش، امتیاز فروش و شرایط نقدی / غیر نقدی فروش مشخص شده است. تعداد فروشنده ها ۸ نفر میباشد که ۸ کالای متفاوت را در ۱۰ شهر مختلف در طول سال های ۱۳۹۰ تا ۱۳۹۵ به فروش رسانده اند. امتیاز فروش، یک ارزیابی کیفی هر فروش میباشد و عددی از ۱ تا ۱۰ است. در شکل زیر نمایی از جدول توصیف شده نشان داده شده است:

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

با استفاده از جدول توصیف شده در بالا و با استفاده از آموزش های ذکر شده در مقاله تعریف و ایجاد Pivot Table، جدول محوری مورد نیاز را ایجاد میکنیم، در ادامه مقاله با بخش های مختلف یک Pivot Table در اکسل آشنا میشویم.

اجزای تشکیل دهنده Pivot Table

بعد از ایجاد یک جدول محوری در اکسل، کاربران میتوانند در قسمت Pivot Table Field List سطرها و ستون های Pivot Table خود را مشخص کنند. شکل زیر Pivot Table Field List جدول محوری ایجاد شده را نمایش میدهد، این قسمت در واقع Panel مدیریتی Pivot Table در اکسل میباشد. پنجره Pivot Table Field در واقع یک Task Pan میباشد.

به دکمه مثلی شکل گوشه بالا و سمت راست Task Pan توجه کنید، با کلیک کردن بر روی این دکمه منوی این پنجره باز میشود در این منو با کلیک کردن بر روی گزینه Move به کاربرد این امکان داده میشود که پنجره Task Pan را به محل دلخواه در شیت جابجا کند و با کلیک کردن بر روی گزینه Size میتوانید این پنجره را به سایز دلخواه تغییر دهید.

در قسمت بالا و سمت راست Panel مدیریتی Pivot Table در اکسل یا Pivot Table Field List، منوی باز شونده تنظیمات وجود دارد، در این قسمت میتوانید Layout پنجره Pivot Table Field List را انتخاب کنید و یا در مورد نحوه Sort کردن Filed ها در قسمت Field Section تصمیم گیری نمایید.با کلیک کردن بر روی منوی Setting پنجره Pivot Table Field میتوانید Layout دلخواه این پنجره را انتخاب نمایید. در منوی Setting همچنین این امکان به کابر داده شده است که Field های نمایش داده شده را Sort نماید. به صورت پیشفرض نام Field ها به همان ترتیبی که در جدول مبدا بوده در اینجا Sort شده اند در واقع به صورت پیشفرض Field ها Sort in Data Source Order هستند، کاربر میتوان Field ها را به ترتیب حروف الفبا نیز مرتب سازی کند.

در صورتی که پنجره Pivot Table Field در شیت وجود نداشت، این پنجره با کلیک بر روی Pivot Table فعال میگردد. در صورتی که فعال نشد ابتدا بر روی Pivot Table کلیک نمایید سپس در تب Analyze در Pivot Table Tools و در گروه Show نمایش این پنجره را فعال کنید.

همانطور که در شکل بالا مشاهده میکنیم، Panel مدیریتی Pivot Table در اکسل یا Pivot Table Field List دارای دو بخش اصلی Field Section و Area Section میباشد. در بخش Field Section تمام Field های Source Data نشان داده شده اند و کادر جستجوی بالای این قسمت به منظور جستجوی Field ها، در صورت زیاد بودن تعداد آنها تعبیه شده است.

در قسمت Area Section چهار بخش اصلی وجود دارد:

• Filters: در این قسمت فیلد (Field) هایی که میخواهیم کل داده های Pivot Table ایجاد شده، بر اساس آنها فیلتر شوند را قرار میدهیم.
• Columns: در این قسمت فیلد هایی که میخواهیم به عنوان ستون (Field) جدول محوری مورد نظر قرار بگیرند قرار میدهیم.
• Rows: در این قسمت فیلد هایی که میخواهیم به عنوان سطر (Item) جدول محوری مورد نظر قرار بگیرند قرار میدهیم.
• Values: در این قسمت فیلد هایی که میخواهیم بر روی آنها محاسبات انجام شود را قرار میدهیم.

در ادامه این مقاله، با موارد بالا به تفصیل آشنا خواهیم شد. در قسمت پایین Panel مدیریتی Pivot Table در اکسل (یا پنجره Pivot Table Field List) گزینه Defer Layout Update وجود دارد، به صورت پیشفرض، با هر تغییر ایجاد شده در Panel مدیریتی Pivot Table (یا پنجره Pivot Table Field List) جدول Pivot بلافاصله به روز رسانی میشود، با تیک زدن گزینه Defer Layout Update تغییرات ایجاد شده پس از کلیک بر روی دکمه Update در Pivot Table اعمال میشوند.

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

تشکیل Pivot Table

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

به عنوان مثال، جدول محوری بالا نشان میدهد آرش در اصفهان ۲۱۰۰ واحد فروش داشته است. حال ممکن است تمایل داشته باشیم مجموع فروش اعلام شده در جدول فوق را برای کالاهای متفاوت داشته باشیم. به عنوان مثال میخواهیم بدانیم مجموع فروش کلیه اقلام بدون در نظر گرفتن فروش مربوط به گندم چه میشود. در این صورت میبایست Field مربوط به کالاها را در قسمت Filter قرار دهیم. به این ترتیب یک قسمت به Pivot Table ترسیم شده اضافه میگردد که میتوان در آن مشخص کرد که داده ها بر چه اساسی فیلتر شوند. شکل زیر تنظیمات Pivot Table و خروجی را نمایش میدهد، در شکل زیر کالای گندم از جدول حذف شده و مجموع فروش هر نفر در هر شهر بدون در نظر گرفتن گندم محاسبه شده است:

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

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

در مقالات بعدی جزییات بیشتری از Pivot Table در اکسل فرا میگیریم.