سبد خرید 0

وبلاگ

Pivot Table (پیوت تیبل) معجزه اکسل برای گزارش گیری

Pivot Table چیست؟

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

اقدامات قبل از استفاده از پیوت تیبل :

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

1_ رعایت اصول ذخیره سازی اطلاعات در دیتابیس:

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

2_ تبدیل محدوده داده ها به Table

Table خود یکی از ابزارهای مهم و کاربردی اکسل می باشد که آموزش مفصلی بصورت ویدئو تهیه شده که می توانید در این لینک مشاهده کنید.برای مشاهده ویدئو می بایست ابتدا در سایت عضو شده سپس بصورت کاملا رایگان در دوره آموزش مقدماتی اکسل ثبت نام کنید و به تمام ویدئوها دسترسی داشته باشید.

نکته:

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

مراحل استفاده :

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

جدول اطلاعات برای تهیه گزارش
شکل شماره 1 – جدول اطلاعات برای تهیه گزارش

ابتدا بعد از تبدیل محدوده خود به Table، محدوده تیبل را انتخاب کرده و مسیر زیر را بروید:

با این کار پنجره Create Pivot Table باز می شود (شکل شماره 2).

ایجاد Pivot Table
شکل شماره 2 – پنجره ایجاد Pivot Table

  1. Select a table or range : محدوده ای که میخواهید بصورت پیوت تیبل از آن گزارش بگیرید را انتخاب کنید.که اینجا محدوده من بصورت تیبل و با نام Table13 می باشد.
  2. Use an external data source : زمانی که بخواهید از داده های خارجی برای گزارشگیری استفاده کنید از این گزینه استفاده می کنید.
  3. New Worksheet : اگر میخواهید گزارشگیری در شیت دیگری ایجاد شود این گزینه را انتخاب کنید.با این کار یک شیت جدیدی ایجاد می شود و جدول گزارش گیری داخل آن ساخته می شود.
  4. Existing Worksheet : اگر میخواهید گزارشگیری در شیت جاری ایجاد شود این گزینه را انتخاب کنید.در قسمت Location محدوده یا سلولی را که میخواهید جدول گزارش گیری از آنجا ساخته می شود را انتخاب کنید.

نکته :

همیشه سعی کنید گزارش های خود را خارج از شیت های دیتابیس ایجاد کنید تا ناخواسته تغییراتی در اطلاعات خود ایجاد نکنید.پس توصیه می کنم تا حدامکان از گزینه 3 یعنی New Worksheet استفاده کنید.

معرفی قسمت های مختلف این ابزار

بعد از OK کردن پنجره محیطی مثل شکل زیر ایجاد می شود:

محیط گزارش گیری در اکسل با pivot table
شکل شماره 3 – محیط تهیه گزارش با pivot table

درستون سمت راست نام ستون های جدول شما نمایش داده می شود که در زیر آن نیز 4 عنوان مختلف دیده می شود

  1. Columns : از لیست نام داده هایی که هایی که می خواهید بصورت ستونی در گزارش گیری نشان داده شود در این قسمت قرار دهید
  2. Rows : از لیست نام داده هایی که می خواهید بصورت سطری در گزارش گیری نشان داده شود در این قسمت قرار دهید
  3. Values : از لیست نام داده های عددی که میخواهید بصورت جمع کل در گزارش گیری نمایش داده شود را اینجا قرار دهید.
  4. Filter : از لیست نام داده های عددی که میخواهید براساس آن فیلتر انجام دهید را اینجا قرار دهید.
حتما بخوانید:   حرفه ای شدن در اکسل و 11 گام رسیدن به آن

حالا چگونه باید از این Pivot Table گزارش بگیریم؟

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

حال می خواهم نوع پرداختی های مشتری بصورت ستونی در گزارش من آورده شود.برای اینکار نام عنوان ستون داده که “نوع” می باشد را از لیست با کلیک چپ موس میگیرم و در کادر Columns رها می کنم.

در ادامه می خواهم تاریخ پرداختی های مشتری بصورت سطری و زیرمجموعه ای از نام مشتری در گزارش من آورده شود.برای اینکار نام عنوان ستون داده که “تاریخ سررسید” می باشد را از لیست با کلیک چپ موس میگیرم و در کادر Rows رها می کنم.

حتما بخوانید:   فرمول نویسی در اکسل و نکات کاربردی برای مبتدیان

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

گزارش نهایی که توسط پیوت تیبل ساخته می شود به شکل زیر خواهد بود:

گزارش نهایی ساخته شده با Pivot Table
شکل شماره 4 – گزارش نهایی ساخته شده با Pivot Table

نکته مهم:

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

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

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

نکته:

در پشت نام مشتریان یک علامت منها وجود دارد.اگر نمی خواهید زیرمجموعه های هر مشتری را ببینید کافیست روی علامت منها کلیک چپ بزنید تا زیرمجموعه بسته شود و فقط نام مشتری نشان داده شود.
نحوه بروزرسانی Pivot Table :

نکته مهمی که درمورد پیوت تیبل باید بدان توجه کرد، نحوه بروزرسانی گزارشات ساخته شده توسط پیوت تیبل می باشد.باید گفت که پیوت تیبل اتوماتیک گزارش خود را بروزرسانی نمی کند و برای اینکه بتوانید آخرین تغییرات انجام شده داده ها را در گزارش خود مشاهده کنید، باید بصورت دستی پیوت تیبل را بروزرسانی کنید.برای این کار کافیست روی گزارش پیوت تیبل کلیک راست نموده و گزینه Refresh را بزنید تا گزارش بروزرسانی شود.

حتما بخوانید:   تابع SUMIFS و کاربرد آن برای جمع های چند شرطی

یا می توانید از تب Data و گروه Connections لیست مربوط به Refresh All را باز کرده و گزینه Refresh را بزنید.اگر هم شما چندین پیوت تیبل ایجاد کرده باشید و می خواهید سریع همه این گزارش ها بروزرسانی شود، گزینه Refresh All را بزنید.

چرا گزارش اتوماتیک آپدیت نمی شود…؟

سوالی که اکثر افراد می پرسند این است که چرا پیوت تیبل هنگام تغییر در داده ها خودش اتوماتیک آپدیت نمی شود…!!؟ آیا مایکروسافت قصد اذیت کردن ما را دارد یا این یک ضعف در کدنویسی برای این ابزار قدرتمند توسط تیم مایکروسافت می باشد…!!؟

چیزی که مسلم است این است که مایکروسافت تلاش می کند تا بهترین و بهینه ترین روش ها را برای استفاده از امکانات در مجموعه آفیس اعمال کند.باید گفت که Pivot Table یک ابزار کاملا سورس پذیر با پروسه آنالیز پیشرفته داده ها می باشد.به همین دلیل زمانی که بانک اطلاعاتی شما بسیار حجیم باشد یا چندین پیوت تیبل ایجاد کرده باشید، آنالیز و بروزرسانی داده ها ممکن است چندین ثانیه طول بکشد.حالا تصور کنید آپدیت Pivot Table اتوماتیک بود و شما می خواهید یک مقداری در دیتابیس خود وارد کنید.به نظر شما چه اتفاقی می افتد…؟ آری… شما برای تغییر هر داده در سلول باید چندین ثانیه منتظر بمانید تا اول بروزرسانی پیوت تیبل ها تمام شود تا بتوانید ادامه تغییرات را در دیتابیس خود اعمال کنید.به نظر شما آیا این وضعیت مطلوبی برای شما ایجاد می کند…!!؟مسلما از این وضعیت کلافه خواهید شد.

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


منابع:

support.office.com

exceljet.net

اشتراک گذاری:

مطالب زیر را حتما مطالعه کنید

13 دیدگاه

به گفتگوی ما بپیوندید و دیدگاه خود را با ما در میان بگذارید.

  • سلام من یک ردیف داده دارم که اعداد زیر صفر و بالای صفر رو در برمیگیره میخوام بدونم چطور میتونم بفهمم که در اون ردیف چند درصد داده ها بالای صفر و چند درصد پایین صفر هستند؟

    • با سلام.
      فرض کنید اعداد در محدوده A1:A18 قرار دارد.
      برای بدست آوردن درصد سهم یک مقدار به روش زیر عمل کنید:

      1-با تابع (COUNTIF(A1:A18,”>=0″)/Count(A1:A18= درصد اعداد صفر و بیشتر بدست می آید.
      2-با تابع (COUNTIF(A1:A18,”<0")/Count(A1:A18= درصد اعداد کمتر از صفر بدست می آید.

      حال برای نمایش نتیجه با نماد درصد کافیه فرمت سلول هایی که فرمول رو داخلش نوشتین به Percentage تغییر بدین.

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

  • سلام من یک سری داده از سال 2000 تا 2017 دارم میخوام اون ها رو سریع فیلتر کنم نه به صورت دستی ، چه جوری از ابزار فیلتر استفاده کنم؟

    • با سلام.

      اگر هدفتان فیلتر بین دو تاریخ هست.بستگی به فرمت تاریخ شما داره.

      اگر فرمت تاریخ شما، فرمت استاندارد یک تاریخ رو داشته باشه (مثلا: 2019/08/04) با کلیک بر روی دکمه فیلتر گزینه Date Filter رو زده و از بین گزینه ها Between رو بزنید و در پنجره باز شده تاریخ شروع و پایان رو مشخص کنید.

      اگر فرمت تاریخ شما خارج از فرمت تعیین شده در سیستمتان باشد گزینه Text Filters رو زده و Custom Filter را انتخاب کنید.در پنجره باز شده شرط اول را روی is greater than و شرط دوم را روی is less than تنظیم کرده و تاریخ شروع و پایان را وارد کنید.

      • سلام
        من نمیخوام بگم از فلان تاریخ تا فلان تاریخ بلکه میخوام در هر سال یک ماه رو انتخاب کنم مثلا از سال 2005 تا 2010 فقط برج هفتمو فیلتر کنم که میشه 6 داده و هر کدام فقط ماه هفتمه. اینجوری میشه فیلتر کرد؟

        • سلام.

          اگر گزینه Date Filters در بین گزینه های فیلتر تاریخ نیست این بدین معنی می باشد که فرمت داده های شما تاریخ نیست.
          شما ابتدا تاریخ را با Replace کردن اسلش(/) بجای نقطه ، از حالت مثلا 1998.01.01 به حالت 1998/01/01 تبدیل کنید.بعد گزینه Date Filters در بین فیلترها ظاهر خواهد شد.حال از همین گزینه Date Filters و از قسمت All Dates in The Period می تونید هم براساس فصل بندی فیلتر کنید هم براساس ماه.

          البته خود پیوت تیبل ابزار Time line رو هم داره.ولی نخواستم دیگه مباحث رو سخت کنم.

          درمورد فیلتر سال هم میتونید یک ستون کمکی ایجاد کنید و با تابع LEFT فقط سال رو جدا کنید.بعد اول بر اساس سال و بعد براساس ماه فیلتر کنید.

  • سلام اقای مختاری
    ممنون از اطلاعات مفیدتون
    من یه مصاحبه کاری درخصوص پرستاری از دستگاه پوز دارم
    به این صورت که یه سری اطلاعات بانکی میرسه و باید با pivot table گزارش گیری بشه
    من تجربه کای تو این قضیه ندارم
    اما pivot table رو در همین حد که گفتین بلدم
    به نظرتون به دنبال چه اطلاعات دیگه ای باید باشم برای یادگیری

    • سلام. وقت بخیر…

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

  • جناب آقای مختاری باسلام و عرض ادب سوال اینکه وقتی یک پیوت می سازیم چگونه می توان مقادیری که در پیوت ساخته شده را وقتی استخراج می کنیم در شیت دیگه ثابت شود و با تعقیر همان پیوت تیبل تشکر میکنم از زحمات جنابعلی

    • سلام. وقت بخیر

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

      لطفا بیشتر توضیح بدین

    • سلام.

      بسیار عالی. امیدوارم موفق باشید.

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

  • ممنونم
    فقط اعتماد به نفس
    اگر تمام مطالب درخواستی را بلد باشیم، اما اعتماد به نفس نداشته باشیم، رد میشیم توی مصاحبه
    در مورد اکسل هم باید مطالب اولیه رو خوب بلد باشیم، مابقی در طول کار آموزش داده میشه

دیدگاهتان را بنویسید