دوازده نکته که حرفه ای ها در اکسل می دانند

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

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

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

• هر ستون دارای یک هدر است
• هیچ ستونی خالی بین داده‌های شما وجود ندارد
• هیچ جمع‌بندی (خلاصه‌سازی مجموعه‌ای سلول‌ها) در جدول داده‌ها وجود ندارد

این تضمین می‌کند؛ که جدول داده‌های خام شما در قالب لیست داده قرار گیرد. یک روش کارآمد، این است؛ که جدول داده‌های خام خود را به یک جدول اکسل تبدیل کنید. چرا؟ از آنجا که لحظه‌ای که داده جدیدی را به جدول داده خام خود اضافه می‌کنید؛ داده‌ها در Pivot (پس از Refresh کردن) بروز می‌شوند؛ و لازم نیست مجدد منبع داده را به روز کنید؛ تبدیل داده‌های خود به یک جدول، همچنین بدان معنی است؛ که هر ستون دارای یک هدر است؛ بنابراین با ایجاد یک Pivot خطایی نخواهید داشت.

• در هر نقطه از جدول داده‌های خود کلیک کنید. از قسمت Insert ، جدول محوری را انتخاب کنید.
• بر table/range کلیک کنید؛ و تصمیم بگیرید که pivot خود را می‌خواهید؛ روی یک worksheet جدید یا worksheet موجود ایجاد نمایید.

3. قدم بعدی طراحی گزارش شماست. شما می‌توانید؛ با کشیدن و رها کردن (Drag and Drop) هر قسمت به بخش‌های مختلف گزارش، این کار را انجام دهید. در اینجا راهنمایی برای هر بخش آورده شده است:

Report Filter: این بخش، شما را از طریق خلاصه داده‌ها، قادر به فیلتر کردن می‌سازد
Column Labels: سرستون فیلد در ستون‌ها
Row Labels: سرسطر فیلد در سطرها

Values: این قسمت مقادیر داده‌های شما است. قلب گزارش شما. شما گزینه‌های مختلفی برای ارائه دارید. به عنوان مثال: می‌توانید انتخاب کنید؛ که داده‌ها باید جمع‌بندی شوند؛ یا شمارش شوند.

4. شما می‌توانید در گزارش خود اصلاحات را انجام دهید. از میان مجموعه‌ای از طرح‌های طراحی شده انتخاب کنید؛ یا خود آن را ایجاد کنید. اگر قالب خاصی دارید و همیشه دوست دارید؛ از آن استفاده کنید، بر روی گزینه طراحی (design) کلیک راست کرده، و آن را به عنوان پیش‌فرض خود تنظیم کنید.

5. از تب Options ، ویژگی‌های زیر را دارید:

می توانید با کلیک راست بر روی فیلد pivot خود، به اکثر این ابزارها دسترسی پیدا کنید.

نکات مفید هنگام کار با جداول محوری

• برای استخراج اطلاعات با جزئیات بیشتر (واکاوی دقیق) یک مقدار خاص، فقط کافی است روی سلول مورد نظر دوبار کلیک کنید. اکسل یک sheet جدیدی شامل آیتم‌های دقیق ایجاد می‌کند (جزئیات در آن شیت نشان داده می‌شود)
• فرمول‌های خلاصه شده را با کلیک راست بر روی pivot خود تغییر داده و گزینه ” summarize data by” را انتخاب کنید.
• pivot خود را هنگامی که منبع داده‌هایتان تغییر یافت؛ Refresh کنید.
• با کلیک بر روی نمودار محوری، داده‌های خود را در یک نمودار مشاهده کنید.
• فیلد گزارش را با قراردادن یک فضا پس از نامی، که در زیر نشان‌داده شده‌است؛ به نام فیلد pivot، در نظر بگیرید.

• از گزینه برش (Slicer option) (قابل دسترسی در اکسل 2010 به بعد) برای ساختندکمه‌های فیلترکردنگروه‌ها استفاده کنید. این ابزار، به شما یک نگاه داینامیک و داشبورد مانند، می‌دهد.

• برای ایجاد یک فیلد محاسبه‌‌ای جهت آنالیز Pivot خودتان مانند تفاوت % بین بودجه برنامه‌ای، در مقابل واقعی، مراحل زیر را دنبال کنید.

• میتوانید ازتابع GetPivotData برای محاسبات پیچیده‌تر مبتنی بر داده‌های حاصل از جدول محوری استفاده کنید. GetPivotData به طور پیش‌فرض، زمانی که به یک سلول در جدول محوری رفرنس دادین؛ ظاهر می‌شود. اگر این اتفاق نیفتاد، دقت کنید؛ که گزینه Use GetPivotData functions for… جدول محوری را با علامت تیک در اکسل فعال کنید.

دلیل عملکرد بهتر  GetPivotData در مقایسه با ارجاع ساده به سلول‌ها در جدول محوری، با استفاده از فرمول‌های نرمال، این است؛ که اگر حل داده‌های موجود در جدول محوری تغییر کند؛ مقادیر به همان شکل باقی می‌ماند.

این به این معنی است؛ که حتی اگر ارجاعات (Reference) شما در برخی از سلول‌ها تغییر یابد؛ و چیدمان تغییرات جدول محوری تغییر کند؛ منابع GetPivotData  بدون تغییر باقی خواهند ماند. نکته مهم این است؛ که فیلدهایی که شما به آن اشاره می‌کنید؛ باید در جدول محوری قابل مشاهده باشد (مخفی نباشد)

• همچنین می‌توانید از گروه‌ها برای سازماندهی بهتر داده‌های خود استفاده کنید. اکسل گزینه‌های دسته‌بندی هوشمندانه را برای تاریخ‌ها پیشنهاد می‌دهد.

• شما همچنین می‌توانید قالب‌بندی شرطی را برای جدول محوری اعمال کنید؛ تا داده‌های شما بهتر قابل نمایش باشد.

• شما می‌توانید به راحتی یک نمودار محوری  را از یک جدول محوری بسازید. نوع نمودار محوری را انتخاب کنید. توجه داشته باشید؛ که همه انواع نمودارها با جدول محوری سازگار نیستند. اگر نمودارهای پیچیده‌تری را ایجاد می‌کنید، به احتمال زیاد برای جدولی که از توابع GetPivotData استفاده خواهید کرد، به یک “جدول آماده‌سازی داده” به صورت جداگانه نیاز خواهید داشت. اگر یک نمودار ساده‌تری را طراحی می‌کنید؛ از گزینه‌های استاندارد نمودار محوری استفاده کنید.

همچنین می‌توانید با دنبال‌‌کردن مراحل زیر، عناوین نمودار محوری را به صورت پویا ایجاد کنید:

• همچنین می‌توانید از جداول محوری  برای گرفتن مقادیر منحصر به فرد از یک لیست استفاده کنید. به سادگی یک جدول محوری را بر روی داده‌ها قرار دهید؛ و فیلد/هایی را که ارزش منحصر به فرد برای بخش ردیف‌ها دارند؛ وارد کنید. آن را در جایی که دوست دارید؛ کپی و الصاق کنید.

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