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

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

اگر به صورت منظم از این سایت دیدن کرده باشید، تا به حال با فرمول های اکسل برای شمارش تعداد مقادیر تکراری آشنا شده اید. امروز، به دنبال شیوه هایی هستیم که مقادیر منحصر به فرد را شمارش کنند. ولی برای روشن شدن موضوع ابتدا واژه ها را تعریف میکنیم:

• مقادیر منحصر به فرد (غیر تکراری) – مقادیری که فقط یکبار در لیست ظاهر شده اند.
• مقادیر متمایز – مقادیر متفاوت در لیست که عبارت هستند از مقادیر منحصر به فرد به علاوه اولین تکرار مقادیر تکراری

تصویر زیر تفاوت این دو مقادیر را نشان میدهد:

نحوه شمارش مقادیر غیر تکراری در اکسل

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

شمارش مقادیر منحصر به فرد در یک ستون

فرض کنید ستونی از نام ها را در ورک شیت خود در اختیار دارید و میخواهید نام های غیر تکراری را در آن ستون شمارش کنید. برای انجام این کار می بایست از تابع Sum  در ترکیب با تابع if و تابع Countif استفاده کنید:

=SUM(IF(COUNTIF(range, range)=1,1,0))

نکته:

این فرمول آرایه ای است، بنابراین می بایست حتماً کلید های Ctrl + Shift + Enter را برای تکمیل آرایه فشار دهید. زمانی که این کار را انجام دهید، اکسل به صورت اتوماتیک فرمول را شبیه به تصویر زیر در درون {} قرار خواهد داد. هیچ وقت نباید به صورت دستی فرمول خود را در درون {} قرار دهید. زیرا در این صورت فرمول به درستی کار نخواهد کرد.

در این مثال، مقادیر منحصر به فرد را در محدوده A2:A8 شمارش میکنیم بنابراین فرمول ما به شکل زیر خواهد شد:

=SUM(IF(COUNTIF(A2:A8,A2:A8)=1,1,0))

در قسمت های بعدی این مطلب، در مورد فرمول هایی که انواع مختلف مقادیر منحصر به فرد را شمارش میکنند بحث خواهیم کرد. از آنجایی که تمام این فرمول ها شکلی از فرمول اولیه بالا هستند بهتر است این فرمول شکسته شده و درک بهتری از آن ایجاد شود تا در نتیجه مطالب بعدی را بهتر متوجه شوید. در صورتی که به مسائل فنی در این زمینه علاقه ای ندارید بهتر است مستقیماً به مثال فرمولی بعدی مراجعه کنید.

فرمول شمارش مقادیر منحصر به فرد چگونه کار میکند

همانطور که مشاهده میکنید، از 3 تابع متفاوت در فرمول بالا استفاده شده است – Sum,if,countif . کاری که این سه تابع در فرمول انجام میدهند در زیر توضیح داده شده است:

• تابع Countif تعداد تکرار مقادیر را در محدوده مشخص شده محاسبه میکند.

در این مثال ، COUNTIF(A2:A8,A2:A8) آرایه {2;1;2;1;1;1;2} را برمیگرداند.

• تابع if مقادیری که توسط تابع countif برگردانده شده را مورد ارزیابی قرار میدهد تمام 1ها را نگه میدارد و بقیه مقادیر را با صفر جایگزین میکند. در نتیجه تابع IF(COUNTIF(A2:A8,A2:A8)=1,1,0) تبدیل به IF(2;1;2;1;1;1;2)=1,1,0, میشود که تبدیل به آرایه {0;1;0;1;1;1;0} میشود که در آن 1 مقدار منحصر به فرد و 0 مقدار تکراری است.
• در آخر،تابع sum مقادیر برگردانده شده توسط تابع if را جمع میزند و مقدار کل آن را برمیگرداند. عدد برگردانده شده همان چیزی است که ما میخواهیم.

نکته:

برای دیدن اینکه هر قسمت از فرمول شما چه کاری انجام میدهد، همان قسمت را در  formula barانتخاب کنیدو کلید f9 را فشار دهید.

شمارش مقادیر متنی غیر تکراری در اکسل

اگر لیست شما هم دارای مقادیر عددی و هم مقادیر متنی است میخواهید صرفاً مقادیر متنی غیر تکراری را شمارش کنید، به فرمول آرایه ای که در بالا توضیح داده شد تابع istext را اضافه کنید.

=SUM(IF(ISTEXT(A2:A8)*COUNTIF(A2:A8,A2:A8)=1,1,0))

همانطور که میدانید، تابع istext در صورتی که مقدار مورد نظر متن باشد true و در غیر اینصورت false را برمیگرداند. از آنجایی که علامت * در فرمول های آرایه بعنوان (و) عمل میکند، فرمول در صورتی 1 را برمیگرداند که مقدار موردنظر هم متن باشد و هم غیر تکراری. در غیر اینصورت 0 را برمیگرداند. و در نهایت تابع sum مقادیر 1 را با هم جمع میکند و جمع این مقادیر تعداد کل متن های غیر تکراری را نشان میدهد.

کلیدهای Ctrl + Shift + Enter را فراموش نکنید. نتایج فرمول بالا در تصویر زیر قابل مشاهده است:

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

شمارش مقادیر غیر تکراری عددی در اکسل

برای شمارش اعداد غیر تکراری در یک لیست، از فرمول آرایه ای شبیه به نمونه بالا استفاده کنید با این تفاوت که به جای istext از isnumber استفاده کنید.

=SUM(IF(ISNUMBER(A2:A10)*COUNTIF(A2:A8,A2:A8)=1,1,0))

از آنجایی که اکسل تاریخ و زمان را به صورت شماره سریال ذخیره میکند، این مقادیر نیز شمارش میشوند.

شمارش مقادیر غیر تکراری و حساس به حروف بزرگ و کوچک در اکسل

در صورتی که جدول شما دارای مقادیر متفاوت به لحاظ نوع حروف است، ساده ترین راه برای شمارش مقادیر غیر تکراری ایجاد یک ستون کمکی و همچنین فرمول زیر است:

=IF(SUM((–EXACT($A$2:$A$10,A2)))=1,"Unique","Dupe")

سپس، با استفاده از یک تابع countif ساده مقادیر مورد نظر را شمارش میکنیم:

=COUNTIF(B2:B10, "unique")

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

برای شمارش مقادیر متمایز در یک لیست از فرمول زیر استفاده کنید:

=SUM(1/COUNTIF(range, range))

کلید های Ctrl + Shift + Enter را فراموش نکنید.

همچنین میتوانید به جای تابع بالا از sumproduct استفاده کنید. در این صورت نیازی به زدن کلیدهای Ctrl + Shift + Enter نیز نخواهد بود.

=SUMPRODUCT(1/COUNTIF(range, range))

برای مثال برای شمارش مقادیر متمایز در محدوده A2:A8 میتوانید از یکی از فرمول های زیر استفاده کنید:

=SUM(1/COUNTIF(A2:A8,A2:A8))

یا

=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))

نحوه کار فرمول مقادیر متمایز

از تابع countif برای شمارش هرکدام از مقادیر استفاده میشود. در مثال بالا، نتیجه تابع Countif آرایه زیر است: {3;1;3;1;1;1;3}

سپس، چند عمل تقسیم انجام میشود. هر کدام از مقادیر آرایه بعنوان مقسوم علیه و از 1 بعنوان مقسوم استفاده میشود. این کار باعث میشود تمام مقادیر تکراری تبدیل به اعداد کسری شوند که تعداد آنها در آرایه به تعداد تکرار این مقادیر مربوط میشود. برای مثال، اگر یک مقدار 2 بار در لیست تکرار شده باشد، 2 بار در آرایه به شکل 0.5 یا 1/2 ظاهر میشود. اگر یک مقدار 3 بار تکرار شده باشد عدد 0.3 3 بار در آرایه تکرار میشود. در مثال ما نتیجه 1/COUNTIF(A2:A8,A2:A8)) آرایه {0.3(3);1;0.3(3);1;1;1;0.3(3)} شده است.

هنوز متوجه نشده اید؟ به این دلیل است که ما هنوز تابع sum یا Sumproduct را اضافه نکرده ایم. زمانی که با استفاده از یکی از این تابع ها مقادیر را با هم جمع کنیم مقدار کل مقادیر کسری برای هر مقدار تبدیل به عدد 1 میشود. تعداد تکرار یک مقدار هرچقدر هم که باشد تأثیری در نتیجه نخواهد داشت. و از آنجایی که مقادیر غیر تکراری در لیست بعنوان 1(1/1) در آرایه ظاهر میشوند، نتیجه نهایی که توسط فرمول برگردانده میشود تعداد کل اعداد متمایز است.

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

اگر ستونی که میخواهید مقادیر متمایز را در آن شمارش کنید دارای سلول های خالی است، می بایست با استفاده از یک تابع if خالی بودن سلول را چک کنید. (فرمول شمارش مقادیر متمایز که در بالا توضیح داده شد در صورت خالی بودن سلول خطای #div/0 را برمیگرداند)

=SUM(IF(range<>"",1/COUNTIF(range, range), 0))

برای مثال برای شمارش مقادیر متمایز در محدوده A2:A8 از فرمول آرایه زیر استفاده کنید:

=SUM(IF(A2:A8<>"",1/COUNTIF(A2:A8, A2:A8), 0))

فرمول هایی برای شمارش مقادیر متنی متمایز

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

همانطور که ممکن است حدس زده باشید، در فرمول شمارش مقادیر متمایز خود یک تابع istext اضافه میکنیم:

=SUM(IF(ISTEXT(range),1/COUNTIF(range, range),""))

یک نمونه فرمول در مورد مقادیر واقعی در زیر آورده شده است:

=SUM(IF(ISTEXT(A2:A10),1/COUNTIF(A2:A10, A2:A10),""))

فرمولی برای شمارش اعداد متمایز

برای شمارش اعداد متمایز(اعداد،تاریخ، زمان)، از تابع isnumber استفاده کنید:

=SUM(IF(ISNUMBER(range),1/COUNTIF(range, range),""))

برای شمارش مقادیر عددی متمایز در محدوده a2 تا a10 از فرمول زیر استفاده میکنیم:

=SUM(IF(ISNUMBER(A2:A10),1/COUNTIF(A2:A10, A2:A10),""))

شمارش مقادیر متمایز در اکسل (حساس به حروف بزرگ و کوچک)

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

=IF(SUM((–EXACT($A$2:$A2,$A2)))=1,"Distinct","")

به یاد داشته باشید که تمام فرمول های آرایه ای در اکسل نیازمند کلیدهای Ctrl + Shift + Enter هستند.

پس از اینکه فرمول بالا تمام شد، میتوانید مقادیر متمایز را با استفاده از یک فرمول Countif معمولی شمارش کنید:

=COUNTIF(B2:B10, "distinct")

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

=SUM(IFERROR(1/IF($A$2:$A$10<>"",FREQUENCY(IF(EXACT($A$2:$A$10,TRANSPOSE($A$2:$A$10)),

MATCH(ROW($A$2:$A$10),ROW($A$2:$A$10)),""),

MATCH(ROW($A$2:$A$10), ROW($A$2:$A$10))), 0), 0))

شمارش ردیف های متمایز و غیر تکراری در اکسل

شمارش ردیف های متمایز و غیر تکراری در اکسل شبیه به شمارش مقادیر غیر تکراری و متمایز است با این تفاوت که از Countifs به جای countif استفاده می کنیم که این امکان را به شما میدهد که به جای یک ستون چندین ستون را برای وجود مقادیر غیر تکراری چک کنید.

برای مثال برای شمارش نام های غیر تکراری و متمایز در ستون A(نام) و B(نام خانوادگی)، از یکی از فرمول های زیر استفاده میکنیم:

فرمول برای شمارش ردیف های غیر تکراری:

=SUM(IF(COUNTIFS(A2:A10,A2:A10, B2:B10,B2:B10)=1,1,0))

فرمول برای شمارش ردیف های متمایز:

=SUM(1/COUNTIFS(A2:A10,A2:A10,B2:B10,B2:B10))

طبیعتاً در بررسی تعداد ستون ها هیچ مشکلی وجود ندارد زیرا تابع Countifs میتواند تا 127 جفت محدوده/شرط  را بررسی کند.