شمارش مقادیر غیر تکراری (متمایز) در اکسل با پاورپیوت

با ابزار پاورپیوت اکسل به سادگی و بدون نیاز به فرمول نویسی‌های پیچیده اکسل می‌توانید گزارشی بسازید که مقادیر غیر تکراری را برای شما شمارش کند. اگر با پیوت تیبل اکسل و یا پاورپیوت تا کنون کار نکرده‌اید نگران نباشید و می‌توانید با دقت مراحل آموزش داده شده در این مقاله را برای شمارش غیر تکراری‌ها انجام دهید و سپس پیشنهاد می‌شود تا مقاله «آموزش قدم به قدم گزارش گیری با پیوت تیبل اکسل» را مطالعه کنید و برای تسلط کامل بر ابزار پیوت تیبل «دوره آموزش ویدئویی ساخت گزارش در اکسل با پیوت تیبل» فرساران را خریداری نمایید.

استفاده از PowerPivot در اکسل

یک خبر خوب، احتیاج به نصب هیچ چیز جدیدی در این دو نسخه اکسل ندارید! در واقع در اکسل 2013 و سایر نسخه‌های بعدی ابزار پاورپیوت وجود دارد و جزئی از خود اکسل شده است. حتی نیازی به فعال سازی add-in آن هم نیست!

کافی است که از همان گزینه PivotTable که در تب Insert است یک پیوت تیبل جدید را بسازید و فقط گزینه Add this data to the Data Model را تیک بزنید!  

مفهوم Add this data to Data Model چیست؟

ابزار پاورپیوت بر روی داده‌ها محاسباتی را انجام می­دهید و این داده­‌ها باید در یک دیتابیسی بارگذاری خواهند شد. به دیتابیس PowerPivot اصطلاحا Data Model گفته می­شود و تعریف دقیق تر Data Model‌ از سایت مایکروسافت:

A data model is embedded data inside an Excel workbook...

شمارش مقادیر غیر تکراری در اکسل با پاورپیوت

1- ابتدا فایل اکسل انتهای این مقاله را دانلود کنید.

2- بر روی یکی از سلول­ها لیستی که در شیت اول آن است کلیک کنید. (لازم نیست که حتما لیست را انتخاب کنید)

3- از تب Insert گزینه Pivot Table را بزنید.

4- قبل از زدن Ok مطمئن شوید که محدوده لیست شما کاملا صحیح انتخاب شده است.

5- قبل از زدن Ok ، گزینه Add this data to Data Model را فعال کنید.

6- کلید Ok را بزنید تا یک PowerPivot برای شما ساخته شود.

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

می­خواهیم بدانیم که هر مشتری چند محصول ما را خریداری کرده است. احتمالا شما در ابتدا یک پیوت به شکل زیر را تصور می­کنید:

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

و اگر بخواهیم به اشتباه سلولهای پُر جلوی نام آبتین را شمارش کنیم، خواهیم دید که 9 سلول خواهد شد اما اگر بپرسیم که او از چند محصول متفاوت خرید کرده است با توجه به این تصویر باید بگوییم که از ۳ محصول!

در واقع ما باید تعداد سلولهای غیر تکراری با مقادیر متفاوتِ هر مشتری را شمارش کنیم و از شمارش تکراری­ها خودداری کنیم یعنی ما Distinct Count را لازم داریم. خوشبختانه در زبان DAX ما این تابع را داریم و اکنون می­توانید از آن استفاده کنید.

الف) روی گزینه Count of Product کیک کنید و سپس گزینه Value Filed Settings را بزنید.

ب) گزینه Distinct Count (شماره مقادیر متمایر) همان چیزی است که سالها در اکسل نداشتیم.

شمارش مقادیر غیر تکراری با توابع جدید اکسل 2021

در اکسل ۲۰۲۱ توابع بسیاری از جمله تابع unique اضافه شده است که با این تابع می‌توان به سادگی مقادیر یکتا (منحصر بفرد) را استخراج کرد . برای ساخت گزارشی مانند آنچه با پیوت در مثال قبل انجام داده‌ایم این فرمول را باید بنویسیم:

=ROWS( UNIQUE( FILTER(A:B, B:B = J10) ) )

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