۶ بازدید

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

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

اکسل رایج‌ترین و محبوب‌ترین ابزار مدیریت داده است به کمک اکسل می‌توانیم هزاران کار مختلف را در ابعاد گوناگون روی داده‌ها انجام دهیم گاهی اوقات در حجم زیاد داده‌ها با اعداد تکراری مواجه می‌شویم در این مطلب از مجله فرادرس ۵ روش ساده برای پیدا کردن اعداد تکراری در اکسل آموزش می‌دهیم. ابتدا اعداد تکراری را دو تابع 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 VALUE که در چهار مرحله از سربرگ Home با اعدا قرمز شماره گذاری شده است.
برای مشاهده در ابعاد بزرگتر، روی تصویر کلیک کنید.

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

مراحل وارد کردن تنظیمات در پنجره Duplicate Values که در سه مرحله برای نشان گذاری کردن اعداد تکراری در اکسل با رنگ قرمز شماره گذاری شده است.

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

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

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

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

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

مراحل ایجاد جدول پیوت که با رنگ قرمز شماره گذاری شده اند.

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

جدول ایجاد جدول پیوت که New Worksheet با کادر قرمز نشان داده شده است.

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

جدول پیوت ایجاد شده توسط اکسل در یک Worksheet جدید در اکسل

برای مشاهده تصویر در ابعاد بزرگتر، روی آن کلیک کنید

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

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

باز کردن منو و انتخاب Value Field Settings که با کادر قرمز و شماره گذاری مشخص شده اند.

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

ایجاد تغییرات در تنظیمات جدول پیوت برای Value نمایش داده شده که در پنجره تنظیمات به رنگ قرمز نمایش داده شده است.

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

جدول پیوت برای نمایش تعداد دفعات تکرار اعداد تکراری در اکسل

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

شقایق جعفری
شقایق جعفری (+)

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


source

توسط expressjs.ir