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

به‌گونه‌ای که تا سال ۲۰۱۰ این ابزار به‌عنوان قوی‌ترین ابزار اکسل شناخته‌شده است. این ابزار در سال ۱۹۸۶ توسط پیتو سالاس[۱] معرفی‌شده است و برای اولین بار در نرم‌افزار لوتوس در سال ۱۹۹۱ مورداستفاده قرارگرفته است.

برای آشنایی با این ابزار و کاربرد آن بهتر است مثال زیر را باهم بررسی نماییم.

مثال کاربردی پیووت تیبل

فرض کنیم که هنگام کار با دیتابیس یک فروشگاه اطلاعات مربوط به فروش به‌صورت ذیل نگه‌داری شده باشد.:

 

اگر بخواهیم با استفاده از دیتابیس فوق به سؤالات ذیل پاسخ دهیم، روش‌های متفاوتی وجود دارد.

• درآمد هر ویزیتور چند ریال می‌باشد؟
• درآمد حاصل از فروش هر برند چند ریال می‌باشد؟
• درآمد حاصل از فروش هر محصول از هر برند چند ریال می‌باشد؟

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

گام اول ساخت پیووت تیبل

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

• محدوده داده‌ها را انتخاب نموده.
• از تب Insert دستور Pivot Table را انتخاب کرده تا پنجره تعریف جدول محوری به‌صورت ذیل نمایش داده شود.

 

همان‌طور که در شکل فوق نمایش داده‌شده است، تعریف جدول محوری از سه قسمت تشکیل‌شده است. در قسمت اول محل داده‌هایی که می‌خواهیم از روی آن‌ها جدول محوری را بسازیم مشخص می‌شود که به‌صورت آدرس‌دهی مطلق تعریف‌شده است (در صورت غلط بودن آدرس، یا ناقص بودن محدوده، کاربر می‌تواند این قسمت را به‌صورت دستی وارد نمایید). در قسمت دوم محل ایجاد جدول محوری مشخص می‌شود. که با انتخاب گزینه اول، یک شیت جدید ایجاد می‌شود و جدول محوری در شیت جدید ساخته می‌شود و با انتخاب گزینه دوم، کاربر می‌تواند به‌صورت دستی با واردکردن آدرس سلول مدنظر در قسمت Location محل ایجاد جدول محوری را مشخص کند. در قسمت سوم این پنجره با فعال کردن چک باکس مدنظر، داده‌ها در قسمت دیتا مدل اضافه می‌شود[۲].

• در صورت انتخاب New Worksheet در قسمت دوم از پنجره تعریف جدول محوری، و تائید کردن آن، یک شیت جدید ساخته می‌شود که در آن جدول محوری قابل ایجاد است. (همانند شکل ذیل)

 

 

 

گام دوم ساخت جدول محوری

این شیت مشابه با سایر شیتهای اکسل می‌باشد و تنها تفاوت آن پنجره سمت راست تحت عنوان مجموعه فیلدهای جدول محوری [۳] می‌باشد.

 

در صورت غیرفعال بودن مجموعه فیلدها در شیت مربوط به جدول محوری، می‌توان با کلیک کردن بر روی سلول A3، از تب Analysis قسمت Show  گزینه Field Headers را فعال نموده تا این قسمت نمایش داده شود. (مانند شکل ذیل)

پنجره مجموعه فیلدها اصلی بخش در ایجاد جدول محوری است، این پنجره از دو بخش اصلی تشکیل‌شده است (مطابق با شکل ذیل) در قسمت اول لیست تمامی فیلدها (هدرها یا سرستون‌ها)ی داده‌ها در جدول اولیه آورده شده است که کاربر می‌تواند متناسب با گزارش مدنظر، از بین آن‌ها انتخاب نماید و قسمت دوم شامل چهار باکس مختلف Rows, Columns, Values و Filters می‌باشد.

 

در این قسمت کافی است کاربر از قسمت اول از پنجره مجموعه فیلدها، فیلد مدنظر را انتخاب نماید و دریکی از باکس‌های پایین (Rows, Columns, Values و Filters)  قرار دهد.

برای مثال اگر کاربر بخواهد درآمد هر ویزیتور را مشاهده نماید، کافی است از لیست فیلدها فیلد “ویزیتور” را انتخاب کند و با نگه‌داشتن کلید موس (درگ کردن) آن را در باکس Rows رها نماید سپس از بین لیست فیلدها، فیلد “درآمد” را انتخاب نماید و با درگ کردن آن را در قسمت Vales رها کن، در این حالت درآمد هر فروشنده به تفکیک به‌صورت شکل ذیل نمایش داده می‌شود.

 

در همین مسئله اگر به‌جای فیلد “ویزیتور”، فیلد “برند” را انتخاب نماییم و در قسمت Rows قرار دهیم، نتیجه به‌صورت شکل ذیل، درآمد حاصل از فروش هر برند را نمایش خواهد داد.

 

در حالت دیگر اگر بخواهیم میزان درآمد حاصل از فروش محصول در هر برند را به دست آوریم، کافی است علاوه بر اضافه کردن فیلد “برند” در قسمت Rows و فیلد “درآمد” در قسمت Values، فیلد “محصول” را دریکی از قسمت‌های Columns یا Rows اضافه نماییم که با اضافه کردن آن در قسمت Columns نتیجه به‌صورت شکل ذیل و در صورت اضافه کردن آن به قسمت Rows نتیجه به‌صورت شکل ذیل نمایش داده خواهد شد.

 

[۱] Pito Salas

[۲] این قسمت فراتر از موضوع این کتاب می باشد و به آن پرداخته نشده است.

[۳] PivotTable Field