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

ورود

گذرواژه خود را فراموش کرده اید؟

ثبت نام

  • ۰۹۲۱۷۷۰۹۵۶۹
  • Info@arshad-hesabdar.ir
مطالب کمیاب در اکسل و حسابداری
  • صفحه اصلی
  • دوره های آموزشی اکسل
  • آموزش اکسل
    • آموزش اکسل مقدماتی
    • آموزش اکسل پیشرفته
    • آموزش اکسل در حسابداری
    • آموزش ترفند اکسلی
    • پاسخگویی به سوالات اکسلی
  • فرمول ها و توابع اکسلی
  • آموزش گوگل شیت
  • درباره ما
آخرین اطلاعیه ها
جهت نمایش اطلاعیه باید وارد سایت شوید
شروع کنید
0
مطالب کمیاب در اکسل و حسابداری > آموزش اکسل پیشرفته > آموزش Pivot Table در اکسل همراه با 1 نکته طلایی

آموزش Pivot Table در اکسل همراه با 1 نکته طلایی

آموزش اکسل پیشرفته
آموزش-Pivot-Table-در-اکسل

مثال اول Pivot Table در اکسل

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

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

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

در این مثال می خواهیم با توجه به جدولی که در زیر مشاهده می کنید گزارشی مبنی بر آنکه در هر ماه و در هر منطقه ما از چه کارشناس هایی فروشی داریم .

راه حلی که به ذهن خیلی ها میرسد استفاده ازPivot Table می باشد و ما نیز می خواهیم از روش پیوت تیبل استفاده کنیم اما یک محدودیت داریم که این محدودیت و راه حل آن را در این آموزش به شما خواهیم گفت.

Pivot Table

قدم اول :ساخت پیوت تیبل (pivot table)

از تبInsert گزینه‌یPivot Table ، را می زنیم .

در پنجره باز شده همانطور که در تصویر زیر به آن اشاره شده است سوالی مبنی بر اینکه نتیجه کار در کجا نمایش داده شود وجود دارد که ما گزینه یExisting Worksheet یعنی در همین شیت میخواهیم بگذاریم را انتخاب کرده وOk می کنیم.

Pivot Table

با انتخاب گزینه Ok در سمت راست پنجره ای که محیط Pivot Table می باشد نمایش داده می شود.

حال گزینه منطقه را در قسمتRows 

ماه را در قسمت Columns

و کارشناس را در قسمت Value قرار می دهیم

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

Pivot Table

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

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

Pivot Table

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

نکته Pivot Table

ابتدا باید جدولمان را تبدیل به Table کنیم.

از تبHome گزینه‌ی Format as Table یکی از فرمت‌ها را انتخاب می کنیم یا کلیدCtrl + T را میزنیم و در پنجره باز شده Ok را می زنیم.

نام این جدول را میتوانیم تغییر داده و یا با همین نام پیش فرض ادامه می دهیم.

حال در تب Insert گزینه‌ی Pivot Table را می زنیم و مشخص میکنیم که نتیجه‌ی کار در کجا قرار بگیرد، این بار حتماً همانطور که در تصویر مشخص شده است دقت کنید تا تیک Add this data to Data Model را فعال کنید

5 3

حال منطقه را در قسمت سطر، ماه را در قسمت ستون واگر کارشناس را در قسمت Values قرار دهیم همچنان آن محدودیت را خواهیم داشت!!!

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

در پنجره باز شده در قسمت Measure Name نام این واحد را فرضاً SalesPerson یا کارشناس فروش میگذاریم.(شماره 1 در تصویر)
در قسمت Formula باید از تابعی به نام تابع CONCATENATEX استفاده کنیم.

یکی از توابع اکسلی CONCATENATE می باشد که احتمالا شما در اکسل با این تابع  آشنایی دارید اما اینجا توابع مربوط به DAX هست و با اکسل متفاوت می باشد.

CONCATENATEX، پرانتز باز نام جدول را تایپ میکنیم کهTable1 بود کاما، قسمت بعدی باید ستون مورد نظر خود را انتخاب کنیم.

اول نام جدول را مینویسیم Table1 و به دنبال ستون مورد نظر که نام کارشناس هست در Table1 ، دابل کلیک میکنیم در قسمت بعدی باید جداکننده‌ی خود را مشخص کنیم و جداکننده را حتما داخل دابل کوتیشن بگذاریم، به اینصورت که دابل کوتیشن، اسپیس، کاما، اسپیس، دابل کوتیشن و پرانتز را می بندیم .( شماره 2 در تصویر )

حال گزینه‌ی Check DAX Formula را میتوانیم بزنیم تا ببینیم که فرمول را درست وارد کردیم یا خیر که در اینجا همانطور که در قسمت شماره 3 تصویر مشاهده می کنید تأیید داده و مشخص است که فرمول خود را درست وارد کردیم و Ok را میزنیم.

Pivot Table

 با انتخاب گزینه Ok یک فیلد بهPivot Table ما به نام SalesPerson اضافه میشود که این فیلد را خیلی راحت میتوانیم به قسمت Values اضافه کنیم

و همان طور که در تصویر زیرمیبینید، ما در اینجا نام کارشناس فروش ها را کنار هم در یک سلول داریم.به طور مثال برای منطقه جنوب، در خرداد ماه فرضاً 3 تا کارشناس داشتیم و در اردیبهشت ماه هیچ کارشناسی نداشتیم.

Pivot Table

در انتها ستون جمع چون به کار نمی آید به راحتی می توانیم بر روی Grand Total کلیک راست کنیم و Remove Grand Total را میزنیم.

جهت مشاهده آموزش  ویدئویی پیوت تیبل در اکسل بر روی لینک زیر کیلیک کرده و آن را داخل پیج ایسنتاگرام مطالب کمیاب در اکسل و حسابداری ( softwaretrain ) مشاهده فرمایید .

آموزش مثال اول پیوت تیبل در اکسل

مثال دوم Pivot Table در اکسل

همانطور که در تصویر زیر مشاهده می فرمایید ، یک فایل اکسلی حاوی اطلاعات خرید مشتریان د راختیار ما می باشد که ما با استفاده از داده های درون اکسل می خواهیم به موارد آخرین تاریخ فروش ، فاصله زمانی و…. پی ببریم ( فایل تمرین این آموزش را می توانید دانلود کرده و مرحله به مرحله با من پیش بروید )

لینک دانلود فایل تمرین PIVOTE TABLE
گزارش گیری در اکسل

اولین گزارش در فروش : آخرین تاریخ

دراین مرحله می خواهیم بدانیم آخرین فروش ما در چه تاریخی بوده است ؟!

ابتدا فایل خود را باز کرده ،سپس از تب  insert گزینه pivote table  را انتخاب می کنیم .

سپس از پنجره باز شده تیک گزینه add this data to the data model  می زنیم چون می خواهیم فرمول نویسی dax را انجام دهیم .

فرمول نویسی در پیوت تیبل

از پنجره باز شده ، بر روی گزینه تیبلtable   که اینجا  sales  می باشد کلیک  راست کرده و گزینه addmeasure  را انتخاب می نماییم .

گزینه addmeasure در پیوت تیبل

ازپنجره باز شده قسمت measure name   عنوان فرمول را last name  نوشته

و قسمت formula   تابع max  را می نویسیم . می خواهیم ستون تاریخ را انتخاب کنیم ، پس مطابق فرمول زیر عمل می نماییم .

 

max(Sales[Invoice Date])=

 

و در نهایت گزینه check DAX  formula را انتخاب  می نماییم تا ببینیم آیا فرمول را درست نوشتیم یا خیر

فرمول نویسی در پیوت تیبل

سپس فیلد ایجاد شده را در قسمت valu  درگ می نماییم .

فرمول نویسی در پیوت تیبل

اگر در این حالت ما از قسمت جدول ، اسم مشتری ها client name  را به قسمت ROW  درگ نماییم ، در این صورت می توانیم آخرین تاریخی که هر مشتری از ما خرید کرده است را نمایش می دهد .

ROW در پیوت تیبل

نکته :

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

MAX(Sales[Persian Date])=

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

دومین گزارش گیری در اکسل : مشخص کردن فاصله زمانی از آخرین فروش

با کمک PIVOTE TABLE  می توانیم بفهمیم که از اخرین خرید مشتری چند روز گذشته است ؟!

و با کمک این فرمول می توانیم فروش خود را افزایش دهیم و با مشتریان قدیمی در ارتباط باشیم.

برای این کار کافی است مطابق با آموزش بالا فرمول زیر را ایجاد کرده و قسمت VALUE  درگ نماییم .

فرمول TODAY را نوشته و فرول بازو بسته سپس منها گذاشته و از آخرین روز میلادی که فرمول آن رابا LASR DATE تعریف نمودیم وارد کرده و اوکی را می زنیم .

TODAY()-[Last Date]=

( اگر فرمت تاریخی به شما نمایش داده کافی است از تب HOME قسمت NUMEBR فرمت را به حالت  GENERAL  تغییر دهیم .و به کمک سورت می توانیم از بزرگتر به کمتر مرتب سازی کنیم و بفهمیم کدام مشتری خیلی وقت است از ما خرید نکرده است )

مشخص کردن فاصله زمانی در اکسل

نکته :

اگر از پنجره گزینه PRODUCT را برداشته و به قسمت ROWS  درگ کنیم می توانیم بفهیم کدام محصول است که خیلی وقته بفروش نرفته

گزارش فروش در اکسل
آموزش ویدئویی مثال دوم پیوت تیبل در اکسل
برچسب ها: آموزش پیوت در اکسلپیوت تیبلمحدودیت پیوت تیبل
آواتار محمود بنی اسدی (مدیر سایت)
درباره محمود بنی اسدی (مدیر سایت)

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

نوشته‌های بیشتر از محمود بنی اسدی (مدیر سایت)
جدیدتر آموزش هایلایت سطر انتخاب شده در اکسل با 2 تابع
قبلی 4نکته کاربردی اکسل که هر کاربر باید بداند!

دیدگاهتان را بنویسید لغو پاسخ

نوشته های اخیر
  • آموزش تابع Concatenate در اکسل|چسباندن متن‌ها به یکدیگر
  • آموزش0تا100قفل کردن فایل اکسل
  • Hyperlink در اکسل چیست و چطور از آن استفاده کنیم؟
  • آموزش نحوه محاسبه تاریخ در اکسل
  • آموزش ابزار جستجو (Find) و جایگزینی داده در اکسل+نکات کلیدی
  • آموزش صفر تا 100حذف ردیف در اکسل همراه با ویدئو آموزشی
  •  2 راز ناگفته تابع getpivotdata دراکسل – استخراج داده از پیوت تیبل
  • تابع TEXT را فقط با 3 نکته کاربردی قورت بده !
  • آموزش صفر تا 100 طراحی فرم در word وانتقال آن به اکسل
  • آموزش رایگان فرمول نویسی مالیات حقوق 1401 در اکسل

محصولات
  • کتاب-ها-و-منابع-آموزشی-رایگان-اکسل-و-پاور-بی-آی کتاب ها و منابع آموزشی رایگان اکسل و پاور بی آی
    رایگان!
  • محاسبه-کارکرد1401 نرم افزار محاسبه کارکرد سال1401 در اکسل
    تومان390.000 تومان280.000
  • حقوق-دستمزد-1401 نرم افزار پیشرفته محاسبه حقوق و دستمزد 1401
    تومان390.000 تومان290.000
  • نمودار سود و زیان به سبکی زیبا نمودار سود و زیان به سبکی زیبا
    تومان50.000
  • ایجاد لیست کشویی جستجوگر اکسل ایجاد لیست کشویی جستجوگر اکسل
    تومان70.000 تومان35.000
  • آموزش ساخت نمودار گیج (Gauge) آموزش ساخت نمودار گیج (Gauge)
    تومان15.000
  • دوره آموزش پایه ای برنامه نویسی در پاورکوئری و پاور بی آی دوره آموزش پایه ای برنامه نویسی در پاورکوئری و پاور بی آی
    امتیاز 5.00 از 5

    تومان590.000
  • برنامه مغایرت گیری بانکی (تحت اکسل) برنامه مغایرت گیری بانکی (تحت اکسل)
    تومان150.000 تومان130.000
  • دوره بی نظیر آموزش اکسل-بخش چهارم دوره بی نظیر آموزش اکسل-بخش چهارم
    تومان200.000
  • دوره آموزشی بی نظیر اکسل دوره آموزشی بی نظیر اکسل
    امتیاز 5.00 از 5

    تومان950.000

دسته‌های محصولات
  • آموزش پاور بی آی
  • دوره اکسل در حسابداری
  • دوره صفر تا صد اکسل
  • دوره گوگل شیت
  • دوره های رایگان
  • سایر
مطالب کمیاب در اکسل و حسابداری

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

دسترسی سریع
  • آموزش اکسل مقدماتی
  • آموزش اکسل پیشرفته
  • دوره های آموزشی اکسل
  • فرمول ها و توابع اکسلی
  • آموزش ترفند اکسلی
  • آموزش اکسل در حسابداری

تماس باما
  • تهران، گیشا ،خیابان 16 ام
  • ۰۹۲۱۷۷۰۹۵۶۹
  • Info@arshad-hesabdar.ir

تمامی حقوق برای سایت مطالب کمیاب در اکسل و حسابداری محفوظ می باشد.
اشتراک گذاری در شبکه های اجتماعی
ارسال به ایمیل
https://arshad-hesabdar.ir/?p=9225
صفحه اینستاگرام
صفحه یوتیوب
صفحه اینستاگرام

اینستاگرام سافت ور

صفحه یوتیوب

کانال یوتیوب سافت ور

Instagram Youtube
مرورگر شما از HTML5 پشتیبانی نمی کند.

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

Logo

مطالب کمیاب در اکسل و حسابداری

  • ۰۹۲۱۷۷۰۹۵۶۹
  • شنبه تا چهارشنبه از ساعت 8 تا 17
  • Info@arshad-hesabdar.ir