۶ بازدید

آخرین به‌روزرسانی: ۲۳ بهمن ۱۴۰۲

زمان مطالعه: ۴ دقیقه

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

استفاده از تابع COUNTIF برای پیدا کردن اعداد تکراری در اکسل

در این مطلب ۵ روش مختلف برای پیدا کردن اعداد تکراری معرفی می‌کنیم و در این روش‌ها از مثالی که در تصویر زیر آمده استفاده می‌کنیم. در این جدول نام تعدادی محصول به همراه شماره سفارش و تاریخ ثبت سفارش ذکر شده است. همانطور که ملاحظه می‌کنید در ستون شماره سفارش تعدادی از اعداد تکرار شده اند.

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

برای استفاده از این تابع ابتدا ستونی در سمت راست جدول اضافه می‌کنیم و در سلول اول این ستون (سلول E5) فرمول زیر را وارد می‌کنیم:

=COUNTIF($D$5:$D$12,D5)>1

با این فرمول اکسل تکراری بودن عددی را  که در ستون کناری آمده، تشخیص می‌دهد. اگر عدد موجود در سلول کناری در ستون D5:D12

  تکرار شده باشد، تابع مقدار True و در غیر این صورت تابع مقدار False را برمی‌گرداند. بعد از وارد کردن فرمول در سلول، با زدن Enter نتیجه نمایش داده می‌شود.

حال با کشیدن مربع کوچک سمت راست سلول E5 تا انتهای جدول، این فرمول برای سایر اعداد متناظر در ستون D نیز اجرا می‌شود.

اعداد تکراری در اکسل با استفاده از ترکیب توابع IF و COUNTIF

در روش دوم پیداکردن اعداد تکراری در اکسل، دو تابع IF و COUNTIF را با یکدیگر ترکیب می‌کنیم. با استفاده از تابع IF می‌توانیم پاسخ تابع را شخصی سازی کنیم تا عبارتی بجز False یا True نمایش داده شود. مشابه روش قبل ستون جدیدی در سمت راست جدول ایجاد می‌کنیم و در سلول اول این ستون (سلول E5) فرمول زیر را وارد می‌کنیم:

=IF(COUNTIF($D$5:$D$12,D5)>1,"Repeated","")

بخش COUNTIF($D$5:$D$12,D5)>1

  فرمول، تست منطقی را اجرا می‌کند، به این معنی که اگر عدد متناظر در ستون D بیش از یک بار در این ستون تکرار شده باشد، مقدار True را برمی‌گرداند و در غیر این صورت مقدار False برگردانده می‌شود.

با قرار دادن این فرمول به عنوان آرگومان اول تابع IF به صورت زیر تابع مقدار ذکر شده در آرگومان دوم و سوم را به عنوان خروجی برمی‌گرداند. یعنی اگر در آرگومان اول مقدار True داشته باشیم، عبارت «Repeated» برگردانده می‌شود و اگر مقدار False داشته باشیم تابع سلول را خالی می‌گذارد و هیچ نتیجه‌ای را برنمی‌گرداند.

 =IF(COUNTIF($D$5:$D$12,D5)>1,”Repeated”,””) ،

با وارد کردن فرمول در سلول و زدن Enter، نتیجه در سلول نمایش داده می‌شود:

حال با کشیدن مربع کوچک سمت راست سلول تا پایین، مشاهده می‌کنید که تمام اعدادی که در ستون D تکرار شده‌اند با عبارت «Repeated» مشخص می‌شوند. مقابل اعداد غیر تکراری نیز خالی باقی می‌ماند.

پیدا کردن اعداد تکراری بدون در نظر گرفتن اولین عدد

در این روش عدد را تنها در صورتی پیدا می‌کنیم که برای بار دوم یا بیشتر در جدول تکرار شده باشد. یعنی بار اولی را که عدد در ستون آمده در نظر نمی‌گیریم. دقت کنید که حرکت اکسل از بالا به پایین است لذا اولین عدد در واقع عددی است که در بالاترین سلول نسبت به سایر اعداد تکراری آمده باشد. در این روش نیز از دو تابع IF و COUNTIF استفاده می‌کنیم. مشابه روش‌های قبل ستونی در سمت راست جدول ایجاد می‌کنیم در سلول E5 فرمول زیر را وارد می‌کنیم:

=IF(COUNTIF($D$5:$D5,D5)>1,"Duplicate","")

این فرمول مشخص می‌کند که عدد متناظر در ستون D، در سلول‌های بالاتر ستون D تکرار شده است یا نه. اگر عدد قبلاً تکرار شده باشد مقدار «Duplicate» برگردانده می‌شود. در غیر این صورت سلول خالی می‌ماند.

حال با کشیدن مربع کوچک سمت راست پایین سلول تا انتهای جدول، این فرمول برای سایر سلول‌های متناظر در ستون D انجام می‌شود.

دقت کنید که برای سلول E6 اکسل مقدار Duplicate را برگردانده است، چرا که سلول نظیر آن در ستون D برای بار دوم آمده است.

اعمال قالب‌بندی شرطی برای پیدا کردن اعداد تکراری در اکسل

حال با اعمال کردن قالب‌بندی شرطی، اعداد تکراری را در جدول داده‌های اکسل پیدا می‌کنیم. برای این کار در این مثال ستون D،  D5 تا D12 را انتخاب می‌کنیم.

به سربرگ «Home» می‌رویم «Conditional Formatting» را انتخاب می‌کنیم و در منوی باز شده روی «Highlight Cell Rules» کلیک می‌کنیم. منویی باز می‌شود که در این منو روی «Duplicate Values» کلیک می‌کنیم.

پنجره‌ای باز خواهد شد در این پنجره «Duplicate» را در منوی سمت چپ و در منوی سمت راست «Light Red Fill with Dark Red Text» را انتخاب می‌کنیم. سپس روی OK کلیک می‌کنیم.

با این کار اکسل تمام اعداد تکراری را به رنگ قرمز هایلایت می‌کند.

استفاده از جدول پیوت برای پیدا کردن اعداد تکراری در اکسل

برای پیدا کردن اعداد تکراری می‌توانید از «Pivot Table» نیز استفاده کنید که در ادامه این مطلب از مجله فرادرس به آن می‌پردازیم.

برای این کار کل جدول داده را انتخاب کنید، سپس در سربرگ «Insert» در بخش «Table» روی «Pivot Table» کلیک کنید.

پنجره‌ای باز می‌شود. در این پنجره گزینه «New Worksheet» را انتخاب کنید و سپس روی Ok کلیک کنید. با این کار یک جدول پیوت در صفحه جدید باز می‌شود.

در تصویر زیر جدول پیوت ایجاد شده توسط اکسل را می‌بینید.

پس از آن شماره سفارش (Order No) را به دو بخش «Raws» و «Values» منتقل کنید. اکسل به طور پیش فرض شماره سفارش‌ها را با هم جمع خواهد زد. نیاز است که این حالت پیش فرض را تغییر دهید.

برای این کار روی  «Sum of Order No» کلیک کنید تا منویی باز شود. در این منو گزینه آخر، یعنی «Value Field Settings» را انتخاب کنید.

پنجره باز شده در منوی پایین پنجره بجای «Sum»، گزینه «Count» را انتخاب کنید و روی Ok کلیک کنید.

سپس مشاهده خواهید کرد که اکسل دفعات تکرار شماره‌های سفارش را شمرده و در جدولی در اختیار شما می‌گذارد.

با مشاهده این جدول تنها در یک نگاه می‌توانیم متوجه شویم که هر یک از اعداد 125، 220 و 222 هر یک چند بار در ستون شماره سفارش (Oder No) تکرار شده‌اند و در انتهای این مطلب از مجله فرادرس، به‌راحتی یاد گرفتید که اعداد تکراری در اکسل را چطور پیدا کنید و حالا، روش های مختلف آن را می‌دانید.