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

توسط Svetlana Cheusheva ، به روز شده در 21 مارس 2023

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

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

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

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

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

و اکنون ، بیایید ببینیم که چگونه می توانید مقادیر منحصر به فرد و متمایز را در اکسل با استفاده از فرمول ها و ویژگی های قابل توجه بشمارید.

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

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

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

به فرض شما یک ستون از نام ها در صفحه کار Excel خود دارید و باید اسامی منحصر به فرد را در آن ستون بشمارید. راه حل استفاده از عملکرد SUM در ترکیب با IF و Countif است:

= جمع (if (countif (دامنه ، دامنه) = 1،1،0))

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

در این مثال ، ما اسامی منحصر به فرد را در محدوده A2: A10 شمارش می کنیم ، بنابراین فرمول ما شکل زیر را می گیرد:

= جمع (if (countif (a2: a10 ، a2: a10) = 1،1،0))

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

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

همانطور که می بینید ، از 3 عملکرد مختلف در فرمول مقادیر منحصر به فرد ما استفاده می شود - جمع ، اگر و Countif. با نگاه از داخل به بیرون ، در اینجا آنچه هر عملکرد انجام می دهد آورده شده است:

• عملکرد Countif چند بار هر مقدار فردی در محدوده مشخص شده ظاهر می شود. در این مثال ، Countif (A2: A10 ، A2: A10) آرایه را برمی گرداند.
• عملکرد IF هر مقدار را در آرایه ای که توسط Countif برگشته است ، ارزیابی می کند ، تمام 1 (مقادیر منحصر به فرد) را نگه می دارد و تمام مقادیر دیگر را با صفرها جایگزین می کند. بنابراین ، عملکرد اگر (countif (a2: a10 ، a2: a10) = 1،1،0) اگر (1 ؛ 2 ؛ 2 ؛ 2 ؛ 2 ؛ 2 ؛ 2 ؛ 2) = 1،1،0 شود.، که به آرایه ای تبدیل می شود که 1 یک مقدار منحصر به فرد است و 0 یک مقدار تکراری است.
• سرانجام ، عملکرد SUM مقادیر موجود در آرایه ای را که توسط IF و تعداد کل مقادیر منحصر به فرد را باز می کند ، اضافه می کند ، این دقیقاً همان چیزی است که ما می خواستیم.

نکته. برای دیدن اینکه چه بخش خاصی از فرمول مقادیر منحصر به فرد Excel شما را ارزیابی می کند ، آن قسمت را در نوار فرمول انتخاب کرده و کلید F9 را فشار دهید.

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

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

همانطور که می دانید ، اگر یک مقدار ارزیابی شده متن باشد ، در غیر این صورت ، عملکرد iStext Excel درست باز می گردد. از آنجا که ستاره (*) به عنوان اپراتور و در فرمول های آرایه کار می کند ، عملکرد IF فقط اگر یک مقدار هم متن باشد و هم منحصر به فرد باشد ، در غیر این صورت 1 باز می گردد. و بعد از اینکه عملکرد جمع همه 1 را اضافه می کند ، تعداد مقادیر متنی منحصر به فرد را در محدوده مشخص دریافت خواهید کرد.

فراموش نکنید که Ctrl + Shift + را وارد کنید تا به طور صحیح فرمول آرایه را وارد کنید ، و نتیجه مشابهی با این نتیجه می گیرید:

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

مقادیر عددی منحصر به فرد را در اکسل بشمارید

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

= جمع (if (isNumber (a2: a10)*countif (a2: a10 ، a2: a10) = 1،1،0))

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

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

اگر جدول شما حاوی داده های حساس به مورد است ، ساده ترین راه برای شمارش مقادیر منحصر به فرد ایجاد یک ستون یاور با فرمول آرایه زیر برای شناسایی موارد تکراری و منحصر به فرد است:

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

= Countif (B2: B10 ، "منحصر به فرد")

مقادیر متمایز را در اکسل شمارش کنید (وقایع تکراری منحصر به فرد و 1 ST)

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

= جمع (1/countif (دامنه ، دامنه))

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

از طرف دیگر ، می توانید با فشار دادن کلید Enter از عملکرد Sumproduct استفاده کرده و فرمول را به روش معمول تکمیل کنید:

= sumproduct (1/countif (دامنه ، دامنه))

به عنوان مثال ، برای شمارش مقادیر متمایز در محدوده A2: A10 ، می توانید با هر دو مورد:

= sumproduct (1/countif (A2: A10 ، A2: A10))

چگونه فرمول متمایز اکسل کار می کند

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

پس از آن تعدادی از عملیات تقسیم انجام می شود ، جایی که از هر مقدار آرایه به عنوان تقسیم کننده با 1 به عنوان سود سهام استفاده می شود. این همه مقادیر کپی را به تعداد کسری مربوط به تعداد وقایع تکراری تبدیل می کند. به عنوان مثال ، اگر یک مقدار 2 بار در لیست ظاهر شود ، 2 مورد را در آرایه با مقدار 0. 5 (1/2 = 0. 5) تولید می کند. و اگر یک مقدار 3 بار ظاهر شود ، 3 مورد در آرایه با مقدار 0. 3 (3) تولید می کند. در مثال ما ، نتیجه 1/countif (A2: A10 ، A2: A10)) آرایه است.

آیا تاکنون معنی زیادی ندارد؟به این دلیل است که ما هنوز عملکرد SUM / SUMPRODUCT را اعمال نکرده ایم. هنگامی که یکی از این توابع مقادیر موجود در آرایه را اضافه می کند ، مجموع اعداد کسری برای هر مورد جداگانه همیشه 1 را به دست می آورد ، مهم نیست که چند مورد از آن مورد در لیست وجود داشته باشد. و از آنجا که تمام مقادیر منحصر به فرد در آرایه به عنوان 1 (1/1 = 1) ظاهر می شوند ، نتیجه نهایی که توسط فرمول بازگردانده می شود ، تعداد کل تمام مقادیر مختلف در لیست است.

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

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

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

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

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

= جمع (اگر (دامنه<>"" ، 1/countif (دامنه ، دامنه) ، 0))

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

= جمع (اگر (A2: A10<>"" ، 1/countif (A2: A10 ، A2: A10) ، 0)

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

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

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

= جمع (if (istext (دامنه) ، 1/countif (دامنه ، دامنه) ، ""))

و در اینجا یک مثال فرمول زندگی واقعی وجود دارد:

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

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

= sum (if (isNumber (دامنه) ، 1/countif (دامنه ، دامنه) ، ""))

به عنوان مثال ، برای شمارش همه اعداد مختلف در محدوده A2: A10 ، از فرمول زیر استفاده کنید:

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

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

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

پس از اتمام فرمول فوق ، می توانید مقادیر "متمایز" را با یک فرمول معمول کانتی مانند این حساب کنید:

= Countif (B2: B10 ، "مجزا")

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

= جمع (iferror (1/if ($ 2: $ 2: 10 دلار<>"" ، فرکانس (اگر دقیق ($ 2: 2 دلار: 10 دلار ، انتقال ($ 2: $ 10 $)) ، مسابقه (ردیف ($ 2 دلار: $ 10 دلار) ، ردیف ($ 2: $ a10)) ، "") ، مسابقه (ردیف ($ 2 $: $ 10 $) ، ردیف ($ 2 $: $ 10 $))) ، 0) ، 0))

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

شمارش ردیف های منحصر به فرد / متمایز در اکسل شبیه به شمارش مقادیر منحصر به فرد و مجزا است ، تنها این تفاوت که شما از عملکرد Countifs به جای Countif استفاده می کنید ، که به شما امکان می دهد چندین ستون را برای بررسی مقادیر منحصر به فرد مشخص کنید.

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

فرمول برای شمارش ردیف های منحصر به فرد:

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

= جمع (1/countifs (A2: A10 ، A2: A10 ، B2: B10 ، B2: B10))

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

مقادیر متمایز را در اکسل با استفاده از یک pivottable بشمارید

آخرین نسخه های Excel 2013 و Excel 2016 دارای یک ویژگی خاص هستند که به شمارش مقادیر متمایز به طور خودکار در یک جدول محوری امکان می دهد. تصویر زیر ایده ای در مورد چگونگی شمارش مجزا اکسل به نظر می رسد:

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

1. داده هایی را انتخاب کنید که در یک جدول محوری گنجانده شود ، به برگه درج ، گروه جداول تغییر دهید و روی دکمه Pivottable کلیک کنید.
2. در گفتگوی ایجاد Pivottable ، انتخاب کنید که آیا جدول محوری خود را در یک برگه جدید یا موجود قرار دهید ، و حتماً افزودن این داده را به کادر انتخاب مدل داده انتخاب کنید.
3. هنگامی که جدول محوری شما باز می شود ، ردیف ها ، ستون ها و ارزش ها را به روشی که می خواهید ترتیب دهید. اگر تجربه زیادی با جداول محوری اکسل ندارید ، دستورالعمل های دقیق زیر ممکن است مفید باشد: ایجاد یک pivottable در اکسل.
4. فیلدی را که شمارش مجزا می خواهید محاسبه کنید (قسمت مورد در این مثال) به قسمت مقادیر منتقل کنید ، روی آن کلیک کنید و تنظیمات مقدار Field را انتخاب کنید ... از منوی کشویی:
5. پنجره گفتگوی تنظیمات Value Field باز خواهد شد ، شما به سمت Count مشخص می شوید که آخرین گزینه در لیست است ، آن را انتخاب کرده و OK را کلیک کنید.

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

انجام شده! جدول Pivot تازه ایجاد شده تعداد متمایز مانند اولین تصویر را در این بخش نشان می دهد.

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

اینگونه است که شما مقادیر متمایز و منحصر به فرد را در اکسل حساب می کنید. اگر کسی بخواهد نگاهی دقیق تر به فرمول های مورد بحث در این آموزش داشته باشد ، شما از بارگیری نمونه کتاب کار منحصر به فرد Sample Excel Count استقبال می کنید.

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

شما همچنین ممکن است به آن علاقه مند باشید

• نحوه شمارش مقادیر منحصر به فرد در اکسل 365 - از توابع جدید آرایه پویا برای شمارش مقادیر منحصر به فرد در اکسل استفاده کنید.
• حذف نسخه کپی برای Excel-ابزاری همه در یک برای یافتن ، برجسته کردن ، حذف ، کپی و حرکت مقادیر تکراری و منحصر به فرد.
• تعداد اکسل و عملکردهای شمارش با نمونه های فرمول - 2 توابع اساسی برای شمارش سلول ها در اکسل.