فهرست
جداول محوری یکی از قدرتمندترین ابزارهای اکسل است. آنها به شما این امکان را می دهند که تنها با چند کلیک ماوس، خلاصه های مختلفی از مقادیر زیادی داده را تجزیه و تحلیل و خلاصه کنید. در این مقاله با جداول محوری آشنا می‌شویم، می‌فهمیم آنها چیست، نحوه ایجاد و سفارشی کردن آنها را یاد می‌گیریم.
این مقاله با استفاده از Excel 2010 نوشته شده است. مفهوم PivotTables در طول سال ها تغییر چندانی نکرده است، اما نحوه ایجاد آنها در هر نسخه جدید اکسل کمی متفاوت است. اگر نسخه ای از اکسل دارید نه 2010، پس آماده باشید که اسکرین شات های این مقاله با آنچه در صفحه خود می بینید متفاوت است.
کمی تاریخ
در روزهای اولیه نرم افزار صفحه گسترده، توپ قانون لوتوس 1-2-3. تسلط آن به قدری کامل بود که تلاش مایکروسافت برای توسعه نرم افزار خود (اکسل) به عنوان جایگزینی برای لوتوس، اتلاف وقت به نظر می رسید. در حال حاضر سریع به جلو به 2010! اکسل بیشتر از کدهای لوتوس در تاریخ خود بر صفحات گسترده تسلط دارد و تعداد افرادی که هنوز از لوتوس استفاده می کنند نزدیک به صفر است. چگونه ممکن است این اتفاق بیفتد؟ دلیل چنین تحول چشمگیری چه بود؟
تحلیلگران دو عامل اصلی را شناسایی می کنند:
- ابتدا، لوتوس تصمیم گرفت که این پلتفرم رابط کاربری گرافیکی جدید به نام ویندوز فقط یک مد گذرا است که مدت زیادی دوام نخواهد آورد. آنها از ساخت نسخه ویندوزی لوتوس 1-2-3 امتناع کردند (اما فقط برای چند سال) و پیش بینی کردند که نسخه DOS نرم افزار آنها تمام نیاز مصرف کنندگان خواهد بود. مایکروسافت به طور طبیعی اکسل را به طور خاص برای ویندوز توسعه داده است.
- دوم، مایکروسافت ابزاری به نام PivotTables در اکسل معرفی کرد که در Lotus 1-2-3 موجود نبود. PivotTables، انحصاری برای اکسل، بسیار مفید بودند که مردم تمایل داشتند به مجموعه نرم افزار جدید اکسل بچسبند تا اینکه با Lotus 1-2-3 که آنها را نداشت، ادامه دهند.
PivotTables، همراه با دست کم گرفتن موفقیت ویندوز به طور کلی، راهپیمایی مرگ را برای Lotus 1-2-3 انجام داد و موفقیت مایکروسافت اکسل را آغاز کرد.
جداول محوری چیست؟
بنابراین، بهترین راه برای مشخص کردن PivotTables چیست؟
به زبان ساده، جداول محوری خلاصه‌ای از برخی داده‌ها هستند که برای تسهیل تجزیه و تحلیل این داده‌ها ایجاد شده‌اند. بر خلاف مجموع ایجاد شده به صورت دستی، جدول محوری اکسل تعاملی است. پس از ایجاد، می‌توانید به راحتی آنها را تغییر دهید، اگر تصویر مورد نظرتان را ارائه ندهند. تنها با چند کلیک ماوس، مجموع ها را می توان برگرداند تا عناوین ستون ها به عنوان ردیف ها تبدیل شوند و بالعکس. شما می توانید بسیاری از کارها را با جداول محوری انجام دهید. به جای تلاش برای توصیف با کلمات تمام ویژگی های جدول محوری، نشان دادن آن در عمل آسان تر است…
داده هایی که با PivotTables تجزیه و تحلیل می کنید نمی توانند تصادفی باشند. باید داده های خام باشد، مانند فهرستی از نوعی. به عنوان مثال، می تواند لیستی از فروش های انجام شده توسط شرکت در شش ماه گذشته باشد.
به داده های نشان داده شده در شکل زیر نگاه کنید:
توجه داشته باشید که این داده های خام نیست، همانطور که قبلاً خلاصه شده است. در سلول B3 ما شاهد 30000 دلار هستیم که احتمالاً کل نتیجه ای است که جیمز کوک در ژانویه انجام داد. پس داده اصلی کجاست؟ رقم 30000 دلار از کجا آمده است؟ لیست اصلی فروش که این کل ماهانه از آن استخراج شده است کجاست؟ واضح است که شخصی کار بزرگی را در سازماندهی و مرتب کردن تمام داده های فروش در شش ماه گذشته انجام داده و آن را به جدول کل که می بینیم تبدیل کرده است. به نظر شما چقدر طول کشید؟ ساعت؟ ساعت ده؟
واقعیت این است که جدول بالا یک جدول محوری نیست. این به صورت دستی از داده های خام ذخیره شده در جاهای دیگر ساخته شد و پردازش آن حداقل چند ساعت طول کشید. چنین جدول خلاصه ای را می توان با استفاده از جداول محوری تنها در چند ثانیه ایجاد کرد. بیایید بفهمیم چگونه…
اگر به لیست فروش اصلی برگردیم، چیزی شبیه به این خواهد بود:
شاید تعجب کنید که از این لیست معاملات با کمک جداول محوری و تنها در چند ثانیه می توانیم گزارش فروش ماهانه را در اکسل ایجاد کنیم که در بالا به بررسی آن پرداختیم. بله، ما می توانیم این کار را انجام دهیم و بیشتر!
چگونه یک جدول محوری ایجاد کنیم؟
ابتدا، مطمئن شوید که برخی از داده های منبع را در یک برگه اکسل دارید. فهرست تراکنش‌های مالی معمول‌ترین موردی است که رخ می‌دهد. در واقع، این می تواند فهرستی از هر چیزی باشد: اطلاعات تماس کارکنان، مجموعه سی دی، یا داده های مصرف سوخت شرکت شما.
بنابراین، اکسل را راه اندازی می کنیم ... و چنین لیستی را بارگذاری می کنیم ...
پس از باز کردن این لیست در اکسل، می توانیم شروع به ایجاد جدول محوری کنیم.
هر سلولی را از این لیست انتخاب کنید:
سپس روی زبانه الحاق (درج) دستور را انتخاب کنید جدول محوری (جدول محوری):
یک جعبه گفتگو ظاهر خواهد شد PivotTable را ایجاد کنید (ایجاد جدول محوری) با دو سوال برای شما:
- از چه داده هایی برای ایجاد یک جدول محوری جدید استفاده کنیم؟
- جدول محوری را کجا قرار دهیم؟
از آنجایی که در مرحله قبل یکی از سلول های لیست را انتخاب کرده ایم، کل لیست به طور خودکار برای ایجاد جدول محوری انتخاب می شود. توجه داشته باشید که ما می توانیم محدوده متفاوت، جدول متفاوت و حتی برخی از منابع داده خارجی مانند جدول پایگاه داده Access یا MS-SQL را انتخاب کنیم. علاوه بر این، ما باید محل قرار دادن جدول محوری جدید را انتخاب کنیم: روی یک صفحه جدید یا روی یکی از صفحات موجود. در این مثال، ما گزینه را انتخاب می کنیم - کاربرگ جدید (به یک برگه جدید):
اکسل یک صفحه جدید ایجاد می کند و یک جدول محوری خالی روی آن قرار می دهد:
به محض اینکه روی هر سلولی در جدول محوری کلیک می کنیم، کادر محاوره ای دیگری ظاهر می شود: لیست فیلدهای PivotTable (فیلدهای جدول محوری).
لیست فیلدها در بالای کادر محاوره ای لیستی از تمام عناوین لیست اصلی است. چهار ناحیه خالی در پایین صفحه به شما این امکان را می دهد که به PivotTable بگویید چگونه می خواهید داده ها را خلاصه کنید. تا زمانی که این مناطق خالی هستند، هیچ چیزی در جدول وجود ندارد. تنها کاری که باید انجام دهیم این است که عناوین را از قسمت بالا به قسمت های خالی زیر بکشیم. در همان زمان، یک جدول محوری به طور خودکار مطابق با دستورالعمل های ما ایجاد می شود. اگر اشتباه کردیم، می‌توانیم عنوان‌ها را از قسمت پایین حذف کنیم یا دیگران را بکشیم تا جایگزین شوند.
منطقه ارزش‌ها (معانی) احتمالاً مهمترین آن چهار است. اینکه کدام عنوان در این ناحیه قرار می گیرد تعیین می کند که چه داده هایی خلاصه می شوند (جمع، میانگین، حداکثر، حداقل و غیره) اینها تقریباً همیشه مقادیر عددی هستند. یک نامزد عالی برای یک مکان در این زمینه داده های زیر عنوان است میزان (هزینه) جدول اصلی ما. این عنوان را به منطقه بکشید ارزش‌ها (ارزش های):
لطفا توجه داشته باشید که عنوان میزان اکنون با یک علامت چک و در منطقه مشخص شده است ارزش‌ها (مقادیر) ورودی ظاهر شده است مجموع مبلغ (فیلد مقدار Amount)، نشان می دهد که ستون میزان خلاصه کرد.
اگر به خود جدول محوری نگاه کنیم، مجموع تمام مقادیر ستون را خواهیم دید میزان میز اصلی
بنابراین، اولین جدول محوری ما ایجاد شد! راحت است، اما به خصوص چشمگیر نیست. ما احتمالاً می خواهیم اطلاعات بیشتری در مورد داده های خود نسبت به آنچه در حال حاضر داریم به دست آوریم.
بیایید به داده های اصلی بپردازیم و سعی کنیم یک یا چند ستون را شناسایی کنیم که می توان از آنها برای تقسیم این مجموع استفاده کرد. به عنوان مثال، ما می توانیم جدول محوری خود را به گونه ای تشکیل دهیم که کل مبلغ فروش برای هر فروشنده به صورت جداگانه محاسبه شود. آن ها ردیف هایی با نام هر فروشنده در شرکت و مبلغ کل فروش آنها به جدول محوری ما اضافه می شود. برای رسیدن به این نتیجه، فقط عنوان را بکشید فروشنده (نماینده فروش) به منطقه ردیف برچسب ها (رشته های):
جالب تر میشه! PivotTable ما در حال شکل گیری است…
مزایای آن را ببینید؟ با چند کلیک، جدولی ایجاد کردیم که ایجاد دستی آن زمان بسیار زیادی طول می کشید.
دیگر چه کاری می توانیم انجام دهیم؟ خوب، به یک معنا، جدول محوری ما آماده است. ما یک خلاصه مفید از داده های اصلی ایجاد کرده ایم. اطلاعات مهم قبلا دریافت شده است! در ادامه این مقاله، راه‌هایی برای ایجاد PivotTableهای پیچیده‌تر و همچنین نحوه سفارشی‌سازی آنها را بررسی خواهیم کرد.
راه اندازی PivotTable
ابتدا می توانیم یک جدول محوری دو بعدی ایجاد کنیم. بیایید این کار را با استفاده از عنوان ستون انجام دهیم روش پرداخت (روش پرداخت). فقط عنوان را بکشید روش پرداخت به منطقه برچسب ستون (ستون ها):
نتیجه را می گیریم:
خیلی باحال به نظر می رسد!
حالا بیایید یک جدول سه بعدی بسازیم. چنین میزی چه شکلی خواهد بود؟ اجازه بدید ببینم…
سربرگ را بکشید بسته (مجتمع) به منطقه گزارش فیلترها (فیلترها):
توجه کنید کجاست…
این به ما این فرصت را می دهد که گزارش را بر اساس "برای کدام مجتمع تعطیلات پول پرداخت شده" فیلتر کنیم. به عنوان مثال، ما می‌توانیم برای همه مجتمع‌ها خرابی بر اساس فروشندگان و روش‌های پرداخت را ببینیم، یا با چند کلیک ماوس، نمای جدول محوری را تغییر دهیم و همان خرابی را فقط برای کسانی که مجتمع را سفارش داده‌اند نشان دهیم. آفتاب جویان.
بنابراین، اگر این را به درستی متوجه شده باشید، جدول محوری ما را می توان سه بعدی نامید. بیایید راه اندازی را ادامه دهیم…
اگر به طور ناگهانی مشخص شد که فقط پرداخت با چک و کارت اعتباری (یعنی پرداخت بدون نقد) باید در جدول محوری نمایش داده شود، می توانیم نمایش عنوان را خاموش کنیم. پول دادن و سكس - پول دادن و كس كردن (نقدی). برای این، در کنار برچسب ستون روی فلش رو به پایین کلیک کنید و علامت کادر را در منوی کشویی بردارید پول دادن و سكس - پول دادن و كس كردن :
بیایید ببینیم جدول محوری ما در حال حاضر چگونه به نظر می رسد. همانطور که می بینید، ستون پول دادن و سكس - پول دادن و كس كردن از او ناپدید شد
قالب بندی PivotTables در اکسل
PivotTables واضح است که ابزار بسیار قدرتمندی هستند، اما تا کنون نتایج کمی ساده و خسته کننده به نظر می رسند. برای مثال، اعدادی که جمع میکنیم شبیه مقادیر دلاری نیستند - آنها فقط اعداد هستند. بیایید این را درست کنیم.
وسوسه انگیز است که در چنین شرایطی کاری را انجام دهید که به آن عادت دارید و به سادگی کل جدول (یا کل برگه) را انتخاب کنید و از دکمه های قالب بندی اعداد استاندارد در نوار ابزار برای تنظیم قالب مورد نظر استفاده کنید. مشکل این روش این است که اگر در آینده ساختار جدول محوری را تغییر دهید (که با احتمال 99٪ اتفاق می افتد)، قالب بندی از بین می رود. چیزی که ما نیاز داریم راهی است که آن را (تقریبا) دائمی کنیم.
ابتدا اجازه دهید ورودی را پیدا کنیم مجموع مبلغ in ارزش‌ها (مقادیر) و روی آن کلیک کنید. در منوی ظاهر شده، مورد را انتخاب کنید تنظیمات فیلد ارزش (گزینه های فیلد مقدار):
یک جعبه گفتگو ظاهر خواهد شد تنظیمات فیلد ارزش (گزینه های فیلد مقدار).
مطبوعات قالب شماره (فرمت شماره)، یک کادر محاوره ای باز می شود. سلولها را قالب بندی کنید (فرمت سلولی):
از لیست دسته بندی (فرمت های اعداد) را انتخاب کنید حسابداری (Accounting) (مالی) و تعداد ارقام اعشار را صفر کنید. حالا چند بار فشار دهید OKتا به جدول محوری خود برگردیم.
همانطور که می بینید، اعداد به صورت مقادیر دلاری فرمت می شوند.
در حالی که در حال کار با قالب‌بندی هستیم، اجازه دهید قالب را برای کل PivotTable تنظیم کنیم. راه های مختلفی برای این کار وجود دارد. ما از یکی که ساده تر است استفاده می کنیم…
با کلیک بر روی ابزارهای PivotTable: طراحی (کار با PivotTables: Constructor):
سپس با کلیک بر روی فلش در گوشه سمت راست پایین بخش، منو را گسترش دهید سبک های PivotTable (PivotTable Styles) برای دیدن مجموعه گسترده ای از سبک های درون خطی:
هر سبک مناسبی را انتخاب کنید و به نتیجه در جدول محوری خود نگاه کنید:
سایر تنظیمات PivotTable در اکسل
گاهی اوقات باید داده ها را بر اساس تاریخ فیلتر کنید. به عنوان مثال، در لیست معاملات ما تاریخ های بسیار بسیار زیادی وجود دارد. اکسل ابزاری برای گروه بندی داده ها بر اساس روز، ماه، سال و غیره فراهم می کند. بیایید ببینیم چگونه انجام می شود.
ابتدا ورودی را حذف کنید. روش پرداخت از منطقه برچسب ستون (ستون ها). برای انجام این کار، آن را به لیست عناوین برگردانید و در جای خود، عنوان را جابجا کنید تاریخ رزرو (تاریخ رزرو):
همانطور که می بینید، این به طور موقت جدول محوری ما را بی فایده کرد. اکسل برای هر تاریخی که در آن معامله انجام شده است یک ستون جداگانه ایجاد می کند. در نتیجه، ما یک میز بسیار گسترده دریافت کردیم!
برای رفع این مشکل، روی هر تاریخی کلیک راست کرده و از منوی زمینه انتخاب کنید گروه (گروه):
کادر محاوره ای گروه بندی ظاهر می شود. ما انتخاب میکنیم ماه گذشته (ماه) و کلیک کنید OK:
وویلا! این جدول بسیار مفیدتر است:
به هر حال، این جدول تقریباً مشابه جدولی است که در ابتدای مقاله نشان داده شده است، جایی که مجموع فروش به صورت دستی جمع آوری شده است.
نکته بسیار مهم دیگری وجود دارد که باید بدانید! شما می توانید نه یک، بلکه چندین سطح از عنوان ردیف (یا ستون) ایجاد کنید:
و به این شکل خواهد بود…
همین کار را می توان با عنوان ستون (یا حتی فیلترها) انجام داد.
بیایید به شکل اصلی جدول برگردیم و ببینیم که چگونه میانگین ها را به جای مجموع نمایش دهیم.
برای شروع روی آن کلیک کنید مجموع مبلغ و از منوی ظاهر شده انتخاب کنید تنظیمات فیلد ارزش (گزینه های فیلد مقدار):
لیست فیلد مقدار را خلاصه کنید (عملیات) در کادر محاوره ای تنظیمات فیلد ارزش (گزینه های فیلد مقدار) را انتخاب کنید میانگین (میانگین):
در عین حال، تا زمانی که اینجا هستیم، بیایید تغییر کنیم نام سفارشی (نام سفارشی) با میانگین مقدار (فیلد مقدار Amount) به چیزی کوتاهتر. در این قسمت چیزی شبیه به آن را وارد کنید میانگین:
رسانه ها و مطبوعات OK و ببینید چه اتفاقی می افتد توجه داشته باشید که همه مقادیر از مجموع به میانگین تغییر کرده اند و سرفصل جدول (در سلول بالا سمت چپ) به میانگین:
در صورت تمایل، می توانید بلافاصله مقدار، میانگین و تعداد (فروش) قرار داده شده در یک جدول محوری را دریافت کنید.
در اینجا یک راهنمای گام به گام در مورد نحوه انجام این کار وجود دارد که با یک جدول محوری خالی شروع می شود:
- سربرگ را بکشید فروشنده (نماینده فروش) به منطقه برچسب ستون (ستون ها).
- عنوان را سه بار بکشید میزان (هزینه) به منطقه ارزش‌ها (ارزش های).
- برای رشته اول میزان عنوان را به جمع (مقدار) و فرمت عددی در این قسمت می باشد حسابداری (Accounting) (مالی). تعداد ارقام اعشار صفر است.
- فیلد دوم میزان نام میانگینe، عملیات را برای آن تنظیم کنید میانگین (متوسط) و فرمت اعداد در این قسمت نیز به تغییر می کند حسابداری (Accounting) (مالی) با اعشار صفر.
- برای رشته سوم میزان یک عنوان تعیین کنید شمردن و یک عمل جراحی برای او - شمردن (تعداد)
- در برچسب ستون فیلد (ستون ها) به طور خودکار ایجاد می شود ارزش ها (مقادیر Σ) - آن را به منطقه بکشید ردیف برچسب ها (خطوط)
در اینجا چیزی است که ما به آن خواهیم رسید:
مقدار کل، ارزش متوسط و تعداد فروش - همه در یک جدول محوری!
نتیجه
جداول محوری در مایکروسافت اکسل دارای ویژگی ها و تنظیمات زیادی هستند. در چنین مقاله کوچکی، آنها حتی به همه آنها نزدیک نیستند. برای توصیف کامل تمام امکانات جدول محوری، یک کتاب کوچک یا یک وب سایت بزرگ لازم است. خوانندگان جسور و کنجکاو می توانند به کاوش خود در جداول محوری ادامه دهند. برای انجام این کار، تقریباً بر روی هر عنصر از جدول محوری کلیک راست کرده و ببینید چه توابع و تنظیماتی باز می شوند. در روبان دو تب پیدا خواهید کرد: ابزارهای PivotTable: گزینه ها (تحلیل) و طرح (سازنده). از اشتباه کردن نترسید، همیشه می توانید PivotTable را حذف کرده و از نو شروع کنید. شما فرصتی دارید که کاربران طولانی مدت DOS و Lotus 1-2-3 هرگز نداشتند.