آیا با داده های زیادی کار می کنید؟ در اینجا نحوه استفاده از PivotTables اکسل برای آسانتر و سریعتر کردن تجزیه و تحلیل داده ها آورده شده است.
شما نباید جداول محوری در اکسل را دست کم بگیرید. به این دلیل که PivotTables اکسل هر چیزی را که برای تجزیه و تحلیل داده ها نیاز دارید ارائه می دهد. از متراکم کردن ردیف‌های داده‌ها تا استخراج مجموع و گروه‌بندی ستون‌های مشابه، کمی از همه چیز در جداول محوری وجود دارد.
با این حال، تجزیه و تحلیل داده ها ممکن است آنقدر که به نظر می رسد ساده نباشد. بنابراین، برای بهبود داده‌های خود، می‌توانید از یک سری توابع پیشرفته PivotTable اکسل برای بهبود داده‌های خود استفاده کنید و زندگی خود را کمی آسانتر کنید.
اگر قبلاً با PivotTables آشنا هستید و به دنبال فراتر رفتن از برخی توابع پیشرفته هستید، می توانید توابع PivotTable پیشرفته زیر را بررسی کنید.
آماده کردن مجموعه داده شما
برای درک توابع پیشرفته، ابتدا باید مجموعه داده اولیه را آماده کنید. برای راحتی، از سطرها و ستون های زیر برای نشان دادن توابع پیشرفته PivotTable استفاده می کنیم.
می‌توانید این داده‌ها را مستقیماً از منبع دانلود کنید تا عملکردهای فهرست شده در زیر را تمرین کنید.
دانلود: نمونه سوپراستور (رایگان)
چگونه یک جدول محوری در اکسل بسازیم؟
داده ها را برای جدول محوری اکسل خود انتخاب کنید. برای درج جدول محوری، روی زبانه Insert در بالا و سپس گزینه PivotTable کلیک کنید.
گزینه From Table/Range را انتخاب کنید.
از آنجایی که قبل از درج پیوت محدوده داده را انتخاب کرده اید، محدوده داده به طور خودکار در کادر محاوره ای محور به روز می شود. شما می توانید مقصد جدول محوری را انتخاب کنید — می توانید آن را در یک کاربرگ موجود یا یک صفحه جدید قرار دهید.
پس از کلیک بر روی Ok، محور شما آماده بررسی است. می توانید نماهای سطر/ستون/مقدار لازم را از لیست فیلد اضافه کنید.
از طرف دیگر، می توانید به طور خودکار جداول محوری را در اکسل با VBA ایجاد کنید تا در زمان و تلاش صرفه جویی کنید.
1. داده ها را با برش برش دهید
اسلایسرها به عنوان بخشی از مجموعه Microsoft Excel 2010 معرفی شدند و در نسخه های بالاتر MS Office موجود هستند. مایکروسافت به انتشار توابع جدید اکسل ادامه می‌دهد تا به کاربران کمک کند تا از وظایف معمول داده خود حداکثر استفاده را ببرند.
مانند برخی دیگر از توابع اکسل، حتی اسلایسرها نیز راهی عالی برای کنترل محورهای شما از طریق فیلتر محوری خارجی هستند.
برخلاف فیلتر محوری داخلی، می‌توانید برش‌کننده‌ها را سفارشی کنید و محتویات را همیشه نمایش دهید. کاربران برش‌کننده‌ها را کاملاً مؤثر می‌دانند، زیرا می‌توانید چندین مورد را فیلتر کرده و آنها را برای دیدن همه نمایش دهید.
افزودن اسلایسرها در جدول محوری
برای افزودن اسلایسر به جدول محوری خود، بر روی جدول محوری خود کلیک کرده و به تب PivotTable Analyze و سپس گزینه Insert Slicer بروید.
هنگامی که روی دکمه Slicer کلیک کردید، می توانید یک یا چند ستون را برای استفاده به عنوان برش انتخاب کنید. تمام گزینه‌هایی که انتخاب می‌کنید در کادرهای فیلتر جداگانه در کنار pivot برای راحتی نشان داده می‌شوند. آیتم های برش دهنده جدول محوری را فیلتر می کنند و هر آنچه مرتبط است را نمایش می دهند.
اسلایسر مفید است، به خصوص زمانی که می خواهید چندین جدول محوری را به طور همزمان با پنجره های اسلایسر (فیلتر) کنترل کنید.
2. کار با فیلدهای محاسبه شده
فیلدهای محاسبه شده راهی عالی برای اضافه کردن محاسبات به جدول محوری شما هستند که هنگام کار با چندین ستون عددی برای محاسبه مقادیر جدید در پرواز ضروری هستند.
به جای مبارزه با گزینه های PivotTable، می توانید یک ستون(های) جدید در جدول محوری خود ایجاد کنید. هر زمان که تغییری در داده های ذاتی ایجاد کنید، تغییرات به طور خودکار در جدول محوری منعکس می شود. با این حال، ابتدا باید جدول محوری را تازه کنید.
نحوه ایجاد یک فیلد محاسبه شده در Pivot Table
فرض کنید می خواهید کل فروش را برای هر زیر مجموعه محاسبه کنید. فرمول ظاهری به دست آوردن محصول فروش و فیلدهای کمیت است.
برای انجام موثر چنین محاسباتی، می توانید یک فیلد محاسبه شده در جدول محوری خود ایجاد کنید.
برای ایجاد یک فیلد محاسبه شده، روی جدول محوری خود کلیک کرده و به تب PivotTable Analyze بروید. از نوار روی دکمه فیلدها، آیتم ها و مجموعه ها کلیک کنید.
از منوی کشویی روی فیلد محاسبه شده کلیک کنید. در کادر محاوره ای، در ستون Name، یک سربرگ ستون اختصاص دهید. سپس، از لیست فیلدها، نام ستون‌هایی را که می‌خواهید در فرمول استفاده کنید، انتخاب کنید.
به دنبال گزینه فروش باشید. بر روی دکمه Insert Field کلیک کنید. گزینه فروش به صورت خودکار به ستون Formula اضافه می شود. به همین ترتیب، می‌توانید روی مقدار و سپس دکمه Insert Field کلیک کنید.
یک علامت ستاره (*) بین دو نام فیلد در نوار فرمول اضافه کنید. بر روی دکمه افزودن کلیک کنید.
در جدول محوری، ستون جدید ایجاد شده در دسترس است.
به طور مشابه، می توانید چندین فیلد محاسبه شده را در جداول محوری خود ایجاد کنید تا محاسبات خود را افزایش دهید.
3. چگونه چندین جدول محوری را بازخوانی کنیم
هنگامی که با داده های پیچیده سروکار دارید، امکان ایجاد چندین جدول محوری به طور همزمان وجود دارد.
بسته به ساختار داده‌هایتان، ممکن است ترجیح دهید چند محور روی یک برگه قرار دهید، در حالی که بسیاری دیگر می‌توانند روی برگه‌های دیگر در همان کتاب کار قرار بگیرند.
دو روش وجود دارد که می توانید چندین جدول محوری را در کتاب کار خود بازخوانی کنید. اولین گزینه استفاده از Excel VBA برای خودکار کردن فرآیند تازه سازی است.
از طرف دیگر، می توانید این کار را به صورت دستی نیز انجام دهید. روی هر جدول محوری کلیک کنید و به تب PivotTable Analyze در منوی نوار بالا بروید. روی منوی کشویی دکمه Refresh کلیک کنید. Refresh All را از لیست انتخاب کنید.
به محض اینکه روی این دکمه کلیک کنید، متوجه رفرش شدن تمام جداول محوری در کتاب کار خواهید شد. هر تغییر جدیدی در جدول محوری منعکس خواهد شد.
4. گروه های مرتبط با هم
شما اغلب می خواهید نقاط داده خاصی را گروه بندی کنید تا کاربران نهایی بتوانند ساختار داده را بهتر درک کنند. به‌عنوان یک متخصص، احتمالاً داده‌ها و ارتباط آنها با دسته‌های گسترده‌تر را به خوبی درک می‌کنید. برعکس، کاربران نهایی ممکن است اغلب برای درک این گروه‌بندی‌ها در جداول محوری دچار مشکل شوند.
برای تسهیل فرآیند، می‌توانید از تابع Group برای گروه‌بندی زیرمجموعه‌ها و تغییر نام گروه برای ایجاد بینش معنادار استفاده کنید.
برای ایجاد یک گروه، کلید ctrl را فشار دهید و چندین مورد را از لیست آیتم ها به صورت یکجا انتخاب کنید. در جدول محوری کلیک راست کرده و روی گزینه Group کلیک کنید.
اکسل گروه های پیش فرض ایجاد می کند که می توانید با انتخاب چندین مورد آن ها را ویرایش کنید.
پس از آماده شدن گروه ها، می توانید نام گروه ها را بر اساس نیاز خود تغییر نام دهید.
برای حذف گروه ها، روی جدول محوری کلیک راست کرده و گزینه Ungroup را انتخاب کنید.
استفاده از توابع Pivot پیشرفته در اکسل
توابع جدول محوری فقط به Microsoft Excel محدود نمی شوند. بسیاری از زبان های برنامه نویسی چنین عملکردی را ارائه می دهند و پایتون نیز از این قاعده مستثنی نیست.
شما می توانید از جداول محوری در پایتون استفاده کنید و با ویژگی های منحصر به فرد آن بازی کنید تا داده های خود را به طور موثر دستکاری و تجزیه و تحلیل کنید. مطمئن باشید پایتون ویژگی های زیادی را برای آسان تر و سریع تر کردن کار شما ارائه می دهد.