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

خبر خوب این است که یادگیری نحوه ساخت pivot table در اکسل بسیار ساده‌تر از آن چیزی است که فکر می‌کنید.

ما شما را در فرآیند ساخت pivot table راهنمایی می‌کنیم و نشان می‌دهیم که چقدر ساده است. ابتدا اجازه دهید یک قدم به عقب برگردیم و مطمئن شویم که دقیقاً می‌دانید pivot table چیست و چرا ممکن است نیاز به استفاده از آن داشته باشید.

pivot table چیست؟

pivot table خلاصه‌ای از داده‌های شماست که در یک نمودار بسته‌بندی شده و به شما امکان می‌دهد روندها را بر اساس اطلاعات خود گزارش دهید و کاوش کنید.

pivot tableها به ویژه در صورتی مفید هستند که ردیف‌ها یا ستون‌هایی طولانی داشته باشید که مقادیری را که برای ردیابی مجموع آن‌ها و مقایسه آن‌ها با یکدیگر نیاز دارید را در خود نگه می‌دارند.

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

بخش «پیوت» pivot table از این واقعیت ناشی می‌شود که می‌توانید داده‌های جدول را بچرخانید (یا محوری) کنید تا آن‌ها را از منظر دیگری مشاهده کنید. اگر بخواهیم واضح‌تر بگوییم، هنگام ایجاد یک محور یا پیوت، داده‌های خود را اضافه و کم نمی‌کنید و یا تغییر نمی‌دهید. در عوض، شما به سادگی داده‌ها را سازماندهی مجدد می‌کنید تا بتوانید اطلاعات مفیدی را کشف کنید.

pivot table برای چه مواردی استفاده می‌شود؟

اگر هنوز در مورد کاری که pivot table واقعا انجام می‌دهد، کمی سردرگم هستید، نگران نباشید. چون یکی از آن فناوری‌هایی است که وقتی آن را در عمل ببینید، درک آن بسیار آسان‌تر است.

هدف از pivot table ارائه راه‌های کاربر پسند برای خلاصه‌سازی سریع مقادیر زیاد داده است. می‌توان از آنها برای درک بهتر، نمایش و تجزیه و تحلیل داده‌های عددی با جزئیات استفاده کرد.

با این اطلاعات، می‌توانید به شناسایی و پاسخگویی به سوالات پیش بینی نشده پیرامون داده‌ها کمک کنید.

در اینجا هفت سناریو فرضی وجود دارد که یک pivot table می‌تواند برای آنها مفید باشد:

1. مقایسه مجموع فروش محصولات مختلف

فرض کنید یک کاربرگ یا ورک شیت دارید که حاوی داده‌های فروش ماهانه برای سه محصول مختلف است – محصول 1، محصول 2 و محصول 3. می‌خواهید بفهمید کدام یک از این سه محصول بیشترین درآمد و سود را به همراه داشته است.

یک راه این است که از طریق ورک شیت نگاه کنید و هر بار که محصول 1 ظاهر می‌شود، رقم فروش مربوطه را به صورت دستی به مجموع فروش محصول 1 اضافه کنید. سپس می‌توان همین فرآیند را برای محصول 2 و محصول 3 انجام داد تا زمانی که مجموع همه آنها را بدست آورید. خیلی راحت است، مگر نه؟

حالا تصور کنید که ورک شیت فروش ماهانه شما هزاران هزار ردیف دارد. مرتب‌سازی دستی هر داده ضروری می‌تواند به معنای واقعی کلمه یک عمر طول بکشد.

با pivot table، می‌توانید به طور خودکار تمام ارقام فروش را برای محصول 1، محصول 2 و محصول 3 جمع آوری کنید – و مجموع مربوط به آنها را در کمتر از یک دقیقه محاسبه کنید.

pivot table

2. نمایش فروش محصول به عنوان درصدی از کل فروش

pivot tableها به طور خود به خود مجموع هر سطر یا ستون را هنگام ایجاد نشان می‌دهند. با این حال با pivot table این تنها موردی نیست که می‌توانید به طور خودکار تولید کنید.

فرض کنید اعداد فروش سه ماهه برای سه محصول جداگانه را در یک برگه یا شیت اکسل وارد کرده‌اید و این داده‌ها را به pivot table تبدیل کرده‌اید. pivot table به طور خودکار سه مجموع را در پایین هر ستون به شما نشان می‌دهد – با جمع آوری فروش سه ماهه هر محصول.

اما اگر بخواهید به جای مجموع فروش آن محصولات، درصدی را که فروش این سه محصول نسبت به کل فروش شرکت داشته، بیابید چه؟

با یک pivot table، به جای مجموع کل ستون، می‌توانید هر ستون را طوری پیکربندی کنید که نسبت درصد آن ستون از مجموع کل سه ستون را به شما بدهد.

فرض کنید سه محصول در مجموع 200,000 دلار فروش داشتند. فروش اولین محصول 45,000 دلار است، می‌توانید pivot table را ویرایش کنید و در عوض بگویید این محصول 22.5 درصد از کل فروش شرکت را به خود اختصاص داده است.

برای نشان دادن فروش محصول به عنوان درصدی از کل فروش در یک pivot table، کافیست روی سلولی که کل فروش را نشان می‌دهد کلیک راست کرده و Show Values ​​As > % of Grand Total را انتخاب کنید.

pivot table فروش

3. ترکیب داده‌های تکراری

در این سناریو، شما به تازگی طراحی مجدد وبلاگ را تکمیل کرده‌اید و مجبور شده‌اید URL‌های زیادی را به روز کنید. متأسفانه، نرم افزارِ گزارش وبلاگِ شما، این تغییر را به خوبی مدیریت نکرده و میزان «بازدید» هر پست‌ را بین دو URL مختلف تقسیم کرده است.

در اسپردشیت خودتان، اکنون دو نمونه جداگانه از هر پست وبلاگ دارید. برای به دست آوردن داده‌های دقیق، باید مجموع بازدیدها را برای هر یک از این موارد تکراری جمع کنید.

به جای جستجوی دستی و جمع زدن تمام بازدیدهای پست‌های تکراری، می‌توانید داده‌ها را (از طریق pivot table) بر اساس عنوان پست وبلاگ خلاصه کنید.

سپس، معیارهای بازدید از آن پست‌های تکراری به طور خودکار جمع می‌شوند.

4. دریافت تعداد کارمندان برای بخش‌های جداگانه

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

به عنوان مثال، فرض کنید فهرستی از کارمندان در یک برگه اکسل دارید. در کنار اسامی کارمندان بخش‌های مربوطه‌ای که به آنها تعلق دارند، آمده است. شما می‌توانید یک pivot table از این داده‌ها ایجاد کنید که نام هر بخش و تعداد کارکنان متعلق به آن بخش‌ها را به شما نشان دهد.

توابع خودکار pivot table به طور مؤثر وظیفه شما را برای مرتب کردن برگه اکسل بر اساس نام بخش و شمارش هر ردیف به صورت دستی را بر عهده می‌گیرند.

5. افزودن مقادیر پیش فرض به سلول‌های خالی

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

اینجاست که pivot tableها وارد کار می‌شوند.

می‌توانید به راحتی یک pivot table را تنظیم کنید تا سلول‌های خالی را با مقدار پیش‌فرض پر کند، مانند $0 یا TBD (برای «تعیین شدن»). برای جداول داده‌های بزرگ، زمانی که افراد زیادی در حال بررسی یک برگه و شیت اکسل هستند، امکان برچسب گذاری سریع این سلول‌ها یک ویژگی ارزشمند است.

برای قالب‌بندی خودکار سلول‌های خالی جدول پیوت، روی جدول خود راست کلیک کرده و روی PivotTable options کلیک کنید.

در پنجره‌ای که ظاهر می‌شود، کادر با عنوان Empty Cells را علامت بزنید و آنچه را که می‌خواهید در زمانی که یک سلول مقداری ندارد نمایش داده شود، وارد کنید.

نحوه ایجاد یک pivot table

اکنون که درک بهتری از اینکه pivot table برای چه مواردی می‌تواند مورد استفاده قرار گیرد، دارید، بیایید به نحوه ایجاد آن بپردازیم.



مرحله 1. داده‌های خود را در محدوده‌ای از ردیف‌ها و ستون‌ها وارد کنید

هر pivot table در اکسل با یک جدول اصلی اکسل شروع می‌شود، جایی که تمام داده‌های شما در آن قرار می‌گیرند. برای ایجاد این جدول، به سادگی مقادیر خود را در یک مجموعه خاص از ردیف‌ها و ستون‌ها وارد کنید. از بالاترین سطر یا بالاترین ستون برای دسته‌بندی مقادیر خود، استفاده کنید.

به عنوان مثال، برای ایجاد یک جدول اکسل از داده‌های عملکرد پست وبلاگ، ممکن است موارد زیر را داشته باشیم:

  • ستونی که “صفحات برتر” را فهرست می‌کند
  • ستونی که “کلیک‌های” هر URL را فهرست می‌کند
  • ستونی که “impression” هر پست را فهرست می‌کند

از این مثال در مراحل بعدی استفاده خواهیم کرد.

مرحله 2. داده‌های خود را بر اساس یک ویژگی خاص مرتب کنید

هنگامی که تمام داده‌های خود را در برگه اکسل وارد کردید، باید آنها را بر اساس ویژگی مرتب کنید. این کار مدیریت اطلاعات شما را پس از تبدیل شدن به pivot table آسان‌تر می‌کند.

برای مرتب‌سازی داده‌های خود، روی تب Data در نوار پیمایش بالا کلیک کنید و آیکون Sort را در زیر آن انتخاب کنید. در پنجره‌ای که ظاهر می‌شود، می‌توانید داده‌های خود را بر اساس هر ستونی که می‌خواهید و به هر ترتیبی مرتب کنید.

به عنوان مثال، برای مرتب‌سازی برگه اکسل بر اساس تاریخ یا “Views to Date”، عنوان ستون را در زیر Column انتخاب کنید و سپس انتخاب کنید که آیا می‌خواهید پست‌های خود را از کوچک‌ترین به بزرگ‌ترین یا از بزرگ‌ترین به کوچک‌ترین مرتب کنید.

OK را در سمت راست پایین پنجره مرتب سازی انتخاب کنید.

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

مرحله 3. سلول‌های خود را برای ایجاد pivot table هایلایت کنید

هنگامی که داده‌ها را وارد و مرتب کردید، سلول‌هایی را که می‌خواهید در جدول pivot خلاصه شوند، هایلایت کنید. روی Insert در امتداد پیمایش بالا کلیک کنید و آیکون PivotTable را انتخاب کنید.

همچنین می‌توانید در هر نقطه از کاربرگ یا ورک شیت خود کلیک کنید، «PivotTable» را انتخاب کنید، و به صورت دستی محدوده سلول‌هایی را که می‌خواهید در PivotTable گنجانده شود، وارد کنید.

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

از طرف دیگر، می‌توانید سلول‌های خود را هایلایت کنید، Recommended PivotTables را در سمت راست آیکون PivotTable انتخاب کنید، و یک جدول پیوت با پیشنهادهای از پیش تنظیم شده برای نحوه سازماندهی هر سطر و ستون باز کنید.

توجه: اگر از نسخه قدیمی اکسل استفاده می‌کنید، «PivotTables» ممکن است در زیر جدول‌ها یا داده‌ها در امتداد پیمایش بالا قرار داشته باشد، نه «Insert». در Google Sheets، می‌توانید pivot tableها را از منوی کشویی Data در امتداد پیمایش بالا ایجاد کنید.

مرحله 4. یک فیلد را در قسمت “لیبل‌های ردیف” بکشید و رها کنید.

پس از اتمام مرحله 3، اکسل یک جدول پیوت خالی برای شما ایجاد می‌کند.

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

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

توجه: pivot table شما ممکن است بسته به نسخه‌ای از اکسل که با آن کار می‌کنید متفاوت به نظر برسد. با این حال، اصول کلی یکسان است.

مرحله 5. یک فیلد را در ناحیه “Values” بکشید و رها کنید

هنگامی که نحوه سازماندهی داده‌های خود را مشخص کردید، گام بعدی شما اضافه کردن مقادیری با کشیدن یک فیلد به ناحیه Values ​​است.

برای مثال، فرض کنید می‌خواهید بازدیدهای پست وبلاگ را بر اساس عنوان خلاصه کنید. برای انجام این کار، به سادگی فیلد “Views” را به قسمت Values ​​بکشید.

مرحله 6. محاسبات خود را دقیق تنظیم کنید

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

در کامپیوتر مک، می‌توانید این کار را با کلیک بر روی i کوچک در کنار یک مقدار در ناحیه “Values”، انتخاب گزینه مورد نظر و کلیک روی “OK” انجام دهید. هنگامی که انتخاب خود را انجام دادید، جدول پیوت شما بر این اساس به روز می‌شود.

اگر از رایانه شخصی یا pc استفاده می‌کنید، برای دسترسی به منو باید روی مثلث وارونه کوچک در کنار مقدار کلیک کنید و Value Field Settings را انتخاب کنید.

هنگامی که داده‌ها را مطابق میل خود دسته‌بندی کردید، کار خود را ذخیره کنید و هر طور که می‌خواهید از آن استفاده کنید.

نمونه‌های pivot table

از مدیریت پول گرفته تا پیگیری تلاش‌های بازاریابی خود، pivot tableها می‌توانند به شما در پیگیری داده‌های مهم کمک کنند. امکانات بی پایان هستند!

سه نمونه جدول پیوت را در زیر ببینید تا به شما الهام ببخشد.

1. ایجاد جدول مرخصی و پیگیری آنها

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

تنها کاری که باید انجام دهید این است که داده‌های شناسایی کارمندان خود را به همراه داده‌های زیر وارد کنید:

  • زمان بیماری
  • ساعتهای مرخصی
  • تعطیلات شرکت
  • ساعات اضافه کاری
  • تعداد ساعات منظم کارمند

از آنجا، می توانید جدول پیوت خود را بر اساس هر یک از این دسته‌ها مرتب کنید.

2. ساختن بودجه

چه در حال اجرای یک پروژه باشید و چه اینکه در حال مدیریت پول خودتان باشید، pivot tableها ابزاری عالی برای ردیابی هزینه‌ها هستند.

ساده‌ترین بودجه فقط به دسته‌های زیر نیاز دارد:

  • تاریخ معامله
  • برداشت / هزینه
  • سپرده / درآمد
  • شرح
  • یک دسته‌بندی فراگیر (مانند تبلیغات پولی یا هزینه‌های پیمانکاری)

با این اطلاعات می‌توانید بزرگترین هزینه‌های خود را ببینید و راه‌های پس‌انداز را در نظر بگیرید.

3. پیگیری عملکرد کمپین شما

pivot table می‌تواند به تیم شما کمک کند تا عملکرد کمپین‌های بازاریابی را ارزیابی کند.

در این مثال، عملکرد کمپین بر اساس منطقه تقسیم می‌شود. شما به راحتی می‌توانید بفهمید در طول کمپین‌های مختلف کدام کشور بیشترین نرخ تبدیل را داشته است.

این کار می‌تواند به شما کمک کند تاکتیک‌هایی را شناسایی کنید که در هر منطقه و جاهایی که تبلیغات نیاز به تغییر دارند، عملکرد خوبی دارند.

جداول پیوت

اکنون با اصول ایجاد جدول پیوت در اکسل آشنا شدید. با این درک، می‌توانید از pivot table خود به آنچه نیاز دارید پی ببرید و راه‌حل‌هایی را که به دنبال آن هستید، بیابید.

به عنوان مثال، ممکن است متوجه شوید که داده‌های جدول پیوت، شما آن طور که می‌خواهید مرتب نشده است. اگر این طور است، تابع مرتب سازی اکسل می‌تواند به شما کمک کند. از طرف دیگر، ممکن است لازم باشد داده‌هایی را از منبع دیگری در گزارش خود بگنجانید، در این صورت تابع VLOOKUP می‌تواند مفید باشد

از همین نویسنده: