شروع جشنواره نوروزی و تخفیف های ویژه 1402 (جهت مشاهده تخفیف ها اینجا کلیک کنید)
سبد خرید
0

هیچ محصولی در سبد خرید نیست.

تمام دسته ها
  • تمام دسته ها
  • Power BI
  • Power Pivot
  • Power Query
  • ابزارها
  • افزونه ها
  • اکسل مدیا
  • توابع اکسل
  • دسته‌بندی نشده
  • دیده بان
  • گوگل شیت
  • مسائل کاربردی
  • معرفی کتاب
  • نمودار ها
  • وی بی - ماکرو

چهار گام ایجاد پیوت تیبل (Pivot Table)

Pivot Table - جداول پاشنه ای
۴.۸/۵ - (۱۹ امتیاز)

برای ایجاد پیوت تیبل (Pivot Table) باید چه کنیم؟

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

Pivot Table - ایجاد پیوت

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

 انتخاب محدوده ایجاد گزارش

شکل ۱- انتخاب محدوده ایجاد گزارش در پیوت تیبل (Pivot Table)

حالا با زدن Ok شیت جدید ایجاد میشه و صفحه ای مطابق شکل ۲ ظاهر میشه که در قسمت بالا، فیلدهای بانک اطلاعاتی و در قسمت پایین ۴ قسمت رو میبینید که از طریق اونها میتونید چیدمان گزارشتون رو تعیین کنید.

صفحه ایجاد گزارش پیوت تیبل

شکل ۲- صفحه ایجاد گزارش پیوت تیبل (Pivot Table)

  1. در این مرحله باید چیدمان گزارش رو بسازید. این کار با درگ کردن Drag & Drop امکان پذیر است. به شکل ۳ دقت کنید. کافیست فیلدهای مورد نظر رو با موس به هر یک از قسمت های چهارگانه گزارش منتقل کنید.

Pivot table - نحوه تعیین چیدمان گزارش

شکل ۳- نحوه تعیین چیدمان گزارش در پیوت تیبل (Pivot Table)

در شکل ۳ در قسمت Values نوع محاسباتی که بر روی فیلد مورد نظر باید انجام بشه رو مشاهده میکنید. در قسمت Row ردیف های گزارش و در قسمت Column ستون های گزارش قرار میگیرند.  قسمت Filter هم که برای اعمال فیلتر بر روی گزارش تهیه شده است.

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

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

دیدگاه کاربران
  • حیدری ۹ خرداد ۱۴۰۲ / ۹:۰۱ ق٫ظ

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

    • آواتار
      حسنا خاکزاد ۹ خرداد ۱۴۰۲ / ۶:۱۲ ب٫ظ

      درود بر شما
      سوال خوبیه
      از طریق grouping اضافه شده

  • حیدری ۶ خرداد ۱۴۰۲ / ۵:۱۶ ب٫ظ

    با سلام و احترام
    اگر بخواهیم در پیویت تیبل یک فیلد غیر محاسباتی ایجاد کنیم که در دیتا پیویت تیبل نباشد چه کار باید انجام بدهیم ؟ ممنون میشم راهنمایی کنید

    • آواتار
      حسنا خاکزاد ۶ خرداد ۱۴۰۲ / ۶:۲۸ ب٫ظ

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

  • اکبر ۲۳ اردیبهشت ۱۴۰۰ / ۳:۴۴ ب٫ظ

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

    Range(“A1”, Range(“A1”).End(xlDown).End(xlToRight)).Select
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    “Sheet1!R1C1:R1048576C41”, Version:=6).CreatePivotTable TableDestination:= _
    “Sheet2!R3C1″, TableName:=”PivotTable3”, DefaultVersion:=6
    Sheets(“Sheet2”).Select

    • آواتار
      حسنا خاکزاد ۲۵ اردیبهشت ۱۴۰۰ / ۱۲:۱۰ ب٫ظ

      درود
      اگر منظورتون دیتابیس هست
      کافیه Table کنید
      Table خودش تغییر میکنه محدودش و کافیه داخل کد فقط از تیبل مورد نظر استفاده کنید

  • حسین جهانگیریان ۱۲ اسفند ۱۳۹۹ / ۱۲:۳۹ ب٫ظ

    سلام و سپاس- سوال از محضرتون اینکه :
    آیا داده های ستونی پیوت تیبل بایستی عددی باشند یا داده ی متنی هم میتونه باشه – در مثال شما داده های ستون عددی هست. مثلا یک ستون ، نام شعبه رو مشخص کنه؟
    من این رو امتحان کردم و بیش از یک ستون با داده متنی نمیتونم در پیوت قرار بدم- پس از کشیدن و رها کردن فیلد متنی دوم در قسمت ستون ها، برای هر داده یک ستون جداگانه میسازه…
    ممنون از شما

    • آواتار
      حسنا خاکزاد ۱۲ اسفند ۱۳۹۹ / ۶:۰۲ ب٫ظ

      درود بر شما
      نه میشه
      فقط باید منطق ار مشخص باشه
      هدفتون از قرار دادن اون فیلد چیه
      در واقع اول باید مشخص کنید چی میخواید تا بشه راهنمایی کرد که برای رسیدن بهش باید چکار کنید

  • نیلوفر ساعی ۱۶ دی ۱۳۹۸ / ۵:۱۰ ب٫ظ

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

    • آواتار
      حسنا خاکزاد ۱۶ دی ۱۳۹۸ / ۹:۵۵ ب٫ظ

      درود بر شما
      یک جدول درست کنید و همه واحدهای پول و شرایبشون نسبت به خروجی که میخواید رو وارد کنید
      و بعد با تابع Vlookup فراخوان یکنید

  • نیلوفر ساعی ۸ دی ۱۳۹۸ / ۷:۱۳ ب٫ظ

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

    • آواتار
      حسنا خاکزاد ۸ دی ۱۳۹۸ / ۹:۳۳ ب٫ظ

      درود بر شما
      ماکرو ضبط کنید تا کد ها رو ببینید
      نحوه ضبط ماکرو در این آموزش ارائه شده:
      https://excelpedia.net/excel-macro/

  • حمید ۲۸ آذر ۱۳۹۸ / ۴:۴۲ ب٫ظ

    با سلام
    یک بانک اطلاعاتی دارم که روزانه قدار زیادی اطلاعات واردش میشه و بعضی روزها اطلاعات جدید واردش میشه و یا بعضی از اطلاعات تغییر میکنه.
    مشکل اینجاست که وقتی پیوت ساختم اطلاعاتی که تغییر میکنه در لیبل جدول نمایش داده میشه در حالیکه دیگه اون لیبل وجود نداره.
    فرض کنید که در ستون A روز های ماه قرار بگیره و در ستون B یک کلمه و در ستون C عدد، بعد از اینکه پیوت را بسازیم ممکنه یک کلمه از داخل ستون B را نیاز باشه که تغیر بدیم کلمه جدیدی بگذاریم وقتی که رفرش کنیم در لیبل فیلترها باز کلمه قبلی نمایش داده میشه و مقدار عددی مقابلش نداره. چطور میشه این لیبل ها را از فیلترها حذف کرد.ممنون

    • آواتار
      حسنا خاکزاد ۲۹ آذر ۱۳۹۸ / ۶:۱۴ ب٫ظ

      درود بر شما
      این اتفاق در Slicer میفته
      شما میتونید از قسمت slicer setting تیک مربوط به گزینه show items deleted from data source رو بردارید

  • سینا ۲ مهر ۱۳۹۸ / ۱۰:۲۸ ق٫ظ

    با سلام و خدا قوت
    من یه لیست محصولات دارم که امکان خرید هر کدام از محصولات از چند شرکت مقدور می باشد . از طرفی هر کدام از شرکت ها دارای مشخصات آدرس و …. است لذا در نظر دارم چند کار انجام بدم
    ۱- لیست کشویی ایجاد شود در زمان وارد کردن اطلاعات تا زمان کمتری در ایجاد لیست داشته باشه
    ۲- پس از ایجاد لیست نیاز دارم تا با سرج کردن یک محصولات اطلاعتی از قبیل اینکه امکان خرید از کدام شرکت مقدور است بصورت کلی نشان دهد مثلا اگر محصول شماره ۱ را میتوان از ۱۰ شرکت تهیه کنم این ۱۰ شرکت به من نمایش داده شود و اطلاعات مربوط به هر شرکت نیز نمایش داده شود.
    ۳- پس از نمایش نیز در صورت نیاز امکان ویرایش وجود داشته باشد. البته این مورد فکر میکنم در زمان تهیه دیتا بیس مقدور و معقول تر باشد.

    • آواتار
      حسنا خاکزاد ۳ مهر ۱۳۹۸ / ۱۱:۴۷ ق٫ظ

      درود بر شما
      باری این موضوع ایجاد لیست های وابسته رو مطالعه بفرمایید

      بله مورد سوم رو به درست یفمرودید
      باید دیتابیس درست باشه و امکان اصلاح رو بخواید بذارید احتمال بروز اشتباه افزایش پیدا میکنه

  • مهسا ۱ مهر ۱۳۹۸ / ۱۰:۰۷ ق٫ظ

    سلام و خسته نباشید
    من یه اکسل دارم شامل ستون A و B. ستون A دارای ۳۴۰ هزار سطر هست و ستون B شامل ۵۰ هزار سطر. تمام اعداد ستون B بصورت پراکنده در ستون A وجود دارند. حالا من میخوام فقط و فقط اعداد غیر تکراری از ستون A رو جدا کنم. چجوری میتونم این کار رو انجام بدم؟

    • آواتار
      حسنا خاکزاد ۱ مهر ۱۳۹۸ / ۱۲:۴۱ ب٫ظ

      درود بر شما
      اگر منظورتون پیدا کردن سلولهایی هست که در b نیستن، با COUNTIF میتونید انجام بدید

  • saviz ۲۵ اردیبهشت ۱۳۹۸ / ۱۰:۰۲ ق٫ظ

    با سلام و تشکر از سایت خوبتان
    سوال بنده در خصوص تفاوت بین count و count number در value fields setting جدول pivot table چیست.
    با تشکر

    • آواتار
      حسنا خاکزاد ۲۵ اردیبهشت ۱۳۹۸ / ۱۲:۰۱ ب٫ظ

      درود بر شما
      count معادل Counta عمل میکنه یعنی سلول های متنی ،عددی و … کلا پر رو میشماره

      Countnumber هم معادل Count برای شمارش عدد بکار میره

  • دکتر بهرام جباری ۲۴ دی ۱۳۹۷ / ۱:۵۷ ب٫ظ

    خانم خاکزاد سلام
    ممنون از راهنماییتون
    من از روش pivot tables پیش رفتم ولی وقتی تو جداول خودم تغییرات میدم اون تغییرات رو اتومات اعمال نمیکنه….
    این امکان وجود داره ریموت بدم خدمتتون یا تماس بگیرم
    ممنون میشم راهنمایی بفرمایید
    سپاسگذارم
    ۰۹۹۰۰۹۹۴۸۹۳
    جباری

    • آواتار
      حسنا خاکزاد ۲۴ دی ۱۳۹۷ / ۳:۰۱ ب٫ظ

      درود بر شما
      بعد از هر تغییر Refresh رو باید بزنید

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

  • amirhossein_maz ۲۲ آبان ۱۳۹۷ / ۱۰:۳۷ ق٫ظ

    سلام . خسته نباشید .
    من مدت کوتاهیه که مشغول به کار با اکسل شدم و تو این مدت سایت شما علاوه بر آموزش های خوبش منو به اکسل علاقمند کرد.ممنونم از آموزش های خوبتون .
    سوالی که بهش برخوردم اینه که چطور PIVOT TABLE خودش آپدیت بشه ؟
    من توی یک صفحه ، که اسمش رو میذارم MASTER داده ها رو وارد میکنم . (ستون ها : نقطه ی مصرف / سطر ها : ماده ی مصرفی موردنیاز / و در سلول ها مقدار مورد نیاز به صورت عدد وارد میکنم)
    الان میخوام یکاری کنم که هر وقت در صفحه ی master مقدار عدد مورد نیاز بیشتر از صفر(یا هر عدد دیگه) شد ، در صفحه ای جدا اطلاعات سطر و ستون رو برام درج کنه .
    تا جایی که تلاش کردم ، وقتی مقداری بیش از صفر بشه ، خود به خود به اون جدول مورد نظر اضافه نمیشه . و یا اگه مقداری مجدد صفر بشه ، خود به خود حذف نمیشه .

    ممنون از پاسخگویی تون .

    • آواتار
      حسنا خاکزاد ۲۲ آبان ۱۳۹۷ / ۱۲:۳۴ ب٫ظ

      درود بر شما
      پایدار باشید
      خودبخود اپدیت نمیشه. و باید گزینه refresh رو بزنید

  • متین ۴ آبان ۱۳۹۷ / ۱۲:۱۲ ب٫ظ

    سلام
    من داده‎هام به این شکل هست که خریدارها با کد نام‎گذاری شده و یک هزینه خرید دارند که الان از طریق پیوت میخوام حالت جمع شده خریدها رو ببینم ولی وقتی sum رو میزنم همه هزینه خرید رو میزنه صفر ولی در شمارش میگه که هر خانواده چند خرید داشته..مشکلش چیه و چه کنم؟
    حتی با consolidate هم انجام میدمُ ارور میده که داده‎ای برا این عمل موجود نیست.
    ممنون از راهنماییتون

    • آواتار
      حسنا خاکزاد ۵ آبان ۱۳۹۷ / ۹:۴۳ ق٫ظ

      درود بر شما
      به نظر میرسه جنس داده ها عددی نیست

      لینک زیر رو مطالعه کنید:

      https://excelpedia.net/text-to-number/

  • عادل ۲۳ خرداد ۱۳۹۷ / ۱۲:۴۷ ب٫ظ

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

    • آواتار
      حسنا خاکزاد ۲۳ خرداد ۱۳۹۷ / ۱:۴۶ ب٫ظ

      درود بر شما
      بخواید خودکار انجام بشه باید کد بزنید

  • اشرف معصومی ۹ اسفند ۱۳۹۶ / ۴:۵۹ ب٫ظ

    با سلام و احترام
    من دنبال یک جای مطمئن برای یادگیری اکسل و مشاوره هستم تا حدودی اکسل را نمی دانم.
    باتشکر

    • آواتار
      حسنا خاکزاد ۱۱ اسفند ۱۳۹۶ / ۷:۵۷ ب٫ظ

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

      توضیحات این دوره رو کامل مطالعه کنید. هر لحظه میتونید به جمع دانشجویان این دوره بپیوندید.

  • شاهین ۱۳ آبان ۱۳۹۶ / ۲:۱۲ ب٫ظ

    بسیار روان و با حوصله آموزش داده شد. لذت بردم

ارسال دیدگاه

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

توسط
تومان