۲ روش شمارش مقادیر تکراری در اکسل (ساده ترین متدها)

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

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

ویدیوی آموزشی

اگر به توضیحات بیشتری نیاز دارید یا به آموزش گام به گام علاقه دارید به خواندن ادامه دهید:

تابع COUNTIF

COUNTIF بدون شک یکی از قدرتمند ترین و مناسب ترین توابع آماری اکسل است. COUNTIF با پیدا کردن تعداد کل سلول های داخل یک محدوده انتخاب شده که با یک معیار خاص مطابقت دارند، کار می کند. برای مثال، می توانید از COUNTIF بخواهید تا به شما بگوید چند سلول در ستون D حاوی عبارت «اکسل بسیار شیک است» می باشد. این تابع در اکسل به این صورت است:

=COUNTIF (range, criteria)

محدوده (Range) سلول هایی هستند که می خواهید معیار (Criteria) را در آن ها جستجوکنید و معیار چیزی است که می خواهید تابع، آن را برایتان بشمارد. اما چگونه ازCOUNTIF بخواهیم تا مقادیر تکراری را بشمارد؟

ابتدا، تعدادی داده‌ی فرضی را در یک صفحه گسترده خالی اکسل وارد کنید. مقادیر ۴۵، ۲۵۲، ۵۲، ۴۵، ۲۵۲ و ۴۵ را در سلول های A2:A7 وارد کنید. صفحه گسترده شما باید مانند صفحه ای مطابق شکل زیر باشد.

فرض کنیم می خواهید بدانید چند سلول حاوی مقدار تکراری ۴۵ است. دستور COUNTIF در یک چشم بر هم زدن آن را به شما خواهد گفت! سلول A49 را انتخاب کنید و سپس روی fx کلیک کنید. COUNTIF را انتخاب کرده و OK را بزنید تا پنجره ی نشان داده شده در زیر، باز شود. (اگر با فرمول های اکسل راحت هستید، می توانید فرمول را مستقیما، بدون استفاده از کادر محاوره، در سلول تایپ کنید).

روی گزینه ی Range کلیک کنید و محدوده سلول A2:A7 را انتخاب کنید. در ادامه، ۴۵ را در کادر متنی Criteria وارد کنید. روی OK کلیک کنید تا پنجره بسته شود. اکنون A9 نتیجه را ۳ نشان خواهد داد. بنابراین، در محدوده انتخاب شده، سه سلول وجود دارد که دارای عدد ۴۵ است.

تابع برای متن نیز به همین شکل عمل می کند. برای مثال، pear، apple و orange را در سلول های A11:14 وارد کنید. صفحه گسترده بایستی شامل لیست کوتاهی از میوه ها، به شکل زیر باشد.

سلول A16 را برای افزودن تابع COUNTIF به آن، انتخاب کنید. روی fx کلیک، COUNTIF را انتخاب و روی OK کلیک کنید.

این بار، سلول های A11:14 را به عنوان محدوده انتخاب کنید. «apple» را در کادر متنی معیار، مطابق آنجه که در زیر نشان داده شده است، وارد کنید. اکنون اگر روی OK کلیک کنید، A16 مقدار ۲ را نشان می دهد. پس دو سلول وجود دارد که شامل تکرار apple است. به یاد داشته باشید که سلول ها در محدوده انتخاب شده، حاوی هیچ فاصله ای (space) نباشند. اگر باشند، اکسل آن ها را به عنوان تکرار شمارش نخواهد کرد (مگر اینکه معیار نیز دقیقا شامل همان فاصله ها وارد شده باشد).

دانلود فیلم های آموزش صفر تا صد اکسل +جزوه

شمارش مقادیر تکراری متعدد

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

در صفحه گسترده یتان A9 را انتخاب کنید. سپس در نوار fx کلیک کنید تا تابع اصلی را ویرایش کنید.  COUNTIF(A2:A7,252) را به تابع اضافه کنید و Enter را بزنید.

تابع کلی به شکل زیر درخواهد آمد:

=COUNTIF(A2:A7,45)+COUNTIF(A2:A7,252)

همانطور که در تصویر زیر نشان داده شده است. A9 مقدار ۵ را نشان خواهد داد. بدین ترتیب، تابع تکرارهای هر دوی ۴۵ و ۲۵۲ را جمع زده است که برابر ۵ است.

این تابع می تواند مقادیر را در محدوده سلول های بین صفحات گسترده متعدد نیز شمارش کند. که این کار نیازمند اصلاح محدوده سلول های مدنظر می باشد که با این کار، توابع شامل یک صفحه مرجع مانند Sheet2! یا Sheet3!، در سلول مرجع می شوند. برای مثال، برای اینکه محدوده ای از سلول های شیت ۳ شامل شوند، تابع باید چیزی شبیه زیر باشد:

=COUNTIF(A2:A7,45)+COUNTIF(Sheet3!C3:C8,252)

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

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

در صفحه گسترده خودتان روی سلول B2 کلیک کنید. روی fx کلیک کرده و انتخاب کنید تا پنجره ی COUNTIF Function Arguments باز شود. در کادر Range عبارت

$A$2:$A$7

را وارد کنید. $A2 را در کادر Criteria وارد کرده و روی OK کلیک کنید تا تابع به صفحه گسترده اضافه شود. سلول B2، همانطور که در پایین نشان داده شده است، مقدار ۳ را نشان خواهد داد.

اکنون لازم است که تابع را به همه ی سلول های زیرین تا B7 کپی کنید. B2 را انتخاب کنید، روی گوشه پایین سمت راست سلول کلیک چپ کرده و آن را تا B7 بکشید. این کار تابع را در تمامی سلول های دیگر کپی می کند؛ همانطور که در زیر نشان داده شده است.

ستون B در تصویر بالا تمامی مقادیر داخل محدوده A2:A7 را به طور موثری شمارش می کند. و نشان می دهد که ۴۵ سه بار و ۲۵۲ دوبار در محدوده انتخابی، تکرار شده است. اکنون شما می توانید به سرعت، همه ی مقادیر تکراری را در ستون ها یا ردیف های صفحه گسترده با اختصاص دادن مراجع سلولی مطلق داخل تابع COUNTIF، پیدا کنید.

اکنون شما می توانید هر تعداد مقدار یا آیتم تکراری را در صفحه گسترده اکسلتان با COUNTIF بشمارید. این صفحه یوتیوب را باز کنید تا عملکرد COUNTIF را در عمل ببینید.

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

پارامترهای قابل تنظیم

محدوده خروجی: محدوده خروجی را با تغییر سلول مرجع (“E5”) در کد VBA انتخاب کنید.

محدوده داده ها: محدوده ای را که می خواهید تکرارها را بررسی کنید با تغییر محدوده مرجع (“B5:B9”) در کد VBA، انتخاب کنید.

مقدار تکراری: سلولی که مقداری را می گیرد که می خواهید تعداد دفعات وقوع آن را در محدودی خاصی بررسی کنید، با تغییر سلول مرجع (“D5”) در کد VBA یا با تغییر مقدار در سلول (“D5”)، انتخاب کنید.

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

پارامترهای قابل تنظیم

محدوده خروجی: محدوده خروجی را با تغییر سلول مرجع (“E5”) در کد VBA انتخاب کنید.

محدوده داده ها: محدوده ای را که می خواهید تکرارها را بررسی کنید با تغییر محدوده مرجع (“B5:B9”) در کد VBA، انتخاب کنید.

مقدار تکراری: سلولی که مقداری را می گیرد که می خواهید تعداد دفعات وقوع آن را در محدودی خاصی بررسی کنید، با تغییر سلول مرجع (“D5”) در کد VBA یا با تغییر مقدار در سلول (“D5”)، انتخاب کنید.

آموزش ضرب در اکسل (با مثال و به زبان ساده)