شناسایی داده های تکراری در اکسل — به زبان ساده + فیلم آموزشی رایگان

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

برای آشنایی بیشتر با محیط اکسل بهتر است مطلب آموزش اکسل (Excel) مقدماتی — به زبان ساده از مجله فرادرس را مطالعه کنید. همچنین خواندن فرمول نویسی در اکسل – آموزش مقدماتی نیز به عنوان پیش‌زمینه برای این نوشتار خالی از لطف نیست.

شناسایی داده های تکراری در اکسل

در بیشتر اوقات با یک لیست یا یک جدول بزرگ اطلاعاتی در اکسل مواجه هستیم. شناسایی داده های تکراری در اکسل، یکی از مسائلی است که به ما کمک می‌کند، دو لیست را با یکدیگر مقایسه کنیم یا جلوی ورود داده‌های تکراری را بگیریم.

فیلم آموزش جداول محوری در اکسل یا پیوت تیبل Pivot Tables در فرادرس

کلیک کنید

برای مثال فرض کنید در یک شرکت لیستی از چک‌های پرداخت شده وجود دارد. چنین لیستی نیز از بانک دریافت شده است. می‌خواهیم مقدارهای تکراری برای شماره چک‌ها را در این دو لیست مطابقت دهیم و متوجه شویم کدام چک‌ها دچار مغایرت شده‌اند و ممکن است در لیست بانک وجود نداشته و یا در لیست چک‌های ما به عنوان صادر شده در ماه جاری قرار ندارند.

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

شناسایی داده های تکراری با مرتب‌سازی (Sort)

یکی از روش‌های ساده و آسان برای پیدا کردن داده یا مقدار تکراری در اکسل، مرتب‌سازی است. واضح است که با مرتب‌سازی یک جدول براساس یک ستون، مقادیر تکراری در آن ستون در سطرهایی در کنار یکدیگر قرار می‌گیرند. به این ترتیب به راحتی می‌توانیم مقدارهای تکراری را شناسایی کنیم. به تصویر زیر توجه کنید. این کاربرگ، شامل مقادیر تکراری در ستون $$A$$‌ است. با استفاده از مرتب‌سازی به سادگی می‌توانید مقدارهای تکراری را تشخیص دهید. واضح است که سطر اول، سوم، پنجم، ششم، هشتم و نهم دارای مقدار تکراری هستند.

حال این داده‌ها را مرتب می‌کنیم. برای مرتب‌سازی ساده این ستون کافی است که یکی از سلول‌های مربوط به ناحیه اطلاعاتی $$A1:A9$$ را انتخاب کنید و از برگه Home دستور مرتب‌سازی را به کمک دکمه Sort & Filter و کلیک روی Sort Smallest to Largest اجرا کنید.

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

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

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

شناسایی داده های تکراری در اکسل با قالب بندی شرطی

ابزار اکسل برای قالب‌بندی سلول‌ها براساس یک یا چندین شرط، به نام «قالب‌بندی شرطی» (Conditional Formatting) مشهور است. در اکسل ۲۰۰۷ به بعد، این ابزار، بهینه شده و گزینه‌های زیادی به آن اضافه شده است. یکی از این گزینه‌ها، شناسایی داده‌های تکراری (Duplicate Value) است. به این ترتیب می‌توانید با تنظیماتی که انجام می‌دهید، سلول‌های با مقدارهای تکراری را شناسایی و مثلا با رنگ متمایزی نسبت به دیگران نشان دهید.

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

کلیک کنید

کاربرگ اطلاعاتی مانند تصویر بالا را در نظر بگیرید. مشخص است که در ستون A‌، مقدارهای تکراری وجود دارند. به کمک قالب‌بندی شرطی (Conditional Formatting) می‌خواهیم این سلول‌ها را با زمینه رنگ قرمز مشخص کنیم. برای شناسایی این داده‌ها مراحل زیر را طی کنید.

• ناحیه اطلاعاتی را انتخاب کنید. در اینجا با توجه به کاربرگی که در تصویر بالا دیده می‌شود، ناحیه اطلاعاتی سلول‌های $$A1:A9$$ است.
• برای دستیابی به ابزار قالب‌بندی شرطی از برگه Home، لیست مربوط به گزینه Conditional Formatting را باز کنید.

• گزینه Duplicate Value را از زیر فهرست Highlight Cell Rules انتخاب کنید و از لیست Values with رنگ دلخواه برای سلول‌های تکراری را انتخاب کنید.

نتیجه انجام این عملیات، به صورت زیر خواهد بود. مشخص است که با انتخاب گزینه Light Red Fill with Dark Red Text زمینه سلول‌های تکراری به صورت قرمز کم‌رنگ و محتویات آن‌ها نیز با قرمز پر رنگ مشخص شده است.

اگر بخواهید به جای پیدا کردن، مقدار تکراری در اکسل، مقدارهای غیرتکراری یا منحصر به فرد (Unique) را در یک لیست شناسایی کنید، کافی است به جای گزینه Duplicate در پنجره Duplicate Values در فهرست بازشو Format cells that contain گزینه Unique را انتخاب کنید.

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

حذف داده های تکراری در اکسل با ابزار Remove Duplicates

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

برای انجام این عملیات، مراحل زیر را طی کنید.

• ناحیه مورد نظر ($$A1:A9$$) را انتخاب کنید.
• برای استفاده از این ابزار از برگه Data گزینه Remove Duplicates را انتخاب کنید.
• در پنجره ظاهر شده، ستون یا ستون‌هایی که باید ملاک برای تکراری بودن سطرها باشند را مشخص کنید.
• با فشردن دکمه OK سطرهای تکراری از کاربرگ حذف می‌شوند.

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

اینطور به نظر می‌رسد که سطرهای ۵ و ۶ شامل مشخصات افرادی است که کد ملی تکراری دارند و باید اطلاعات آن‌ها دوباره بررسی شده و در حال حاضر در گزارشات به کار نروند. پس بهتر است آن‌ها را حذف کنیم. دستور Remove Duplicates را اجرا می‌کنیم. ولی توجه داشته باشید که برای شناسایی افراد با کد ملی تکراری فقط ستون «کد ملی» را در پنجره ظاهر شده، فعال کنید زیرا نام و نام خانوادگی ملاکی برای شناسایی افراد تکراری نیست.

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

نکته: اگر به جای انتخاب ستون کد ملی همه ستون‌ها را انتخاب می‌کردید، اکسل به دنبال رکوردهایی می‌گشت که همه ستون‌های آن تکراری باشد. از آنجایی که رکوردهایی که همه مقادیر ستون‌های آن‌ها تکراری نباشد، وجود ندارد، ابزار Remove Duplicates سطری را تکراری تشخیص نداده و در نتیجه با پیغام زیر مواجه خواهید شد.

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

در این قسمت به کمک فرمول Countif، تعداد داده تکراری در اکسل را شمارش کرده و زمانی که این تعداد بزرگتر از ۱ باشد، در سلول مجاور پیغامی مبنی بر تکراری بودن مقدار ثبت شده، ظاهر خواهد شد.

سلول‌های $$A1:A10$$ از یک کاربرگ را در نظر بگیرید. می‌خواهیم در سلول مجاور یعنی $$B1:B10$$ مشخص کنیم که آیا مقدار ثبت شده در ستون قبلی تکراری است یا خیر؟ برای انجام این کار مراحل زیر را طی کنید.

• سلول‌های $$B1:B10$$ را انتخاب کنید.
• از طریق دکمه $$fx$$ یا شیوه‌های مشابه تابع Countif را ظاهر کنید.
• پارامترهای این تابع را مطابق با تصویر زیر تنظیم کنید.

این تابع را در سلول $$B1$$ ثبت و در بقیه سلول‌ها کپی کنید. کاملا مشخص است که اگر در این ستون،‌ مقداری بزرگتر از ۱ مشاهده کنید، نشان از تکراری بودن سلول مجاور در ستون $$A$$ است. بنابراین به کمک Conditional Formatting یا تابع IF می‌توانید این سلول‌ها را متمایز کرده یا پیغامی را نشان دهید.

البته به کمک فیلتر (Filter) نیز می‌توانید سطرهای با مقادیر تکراری در ستون اول را مشخص کنید. کافی است که فیلتر را روی ستون $$B$$ اجرا کرده و گزینه «داده تکراری» را از لیست اقلام فیلتر انتخاب کنید. به این ترتیب فقط سطرهایی که در ستون اول مقداری تکراری دارند، نمایش داده شده و می‌توانید آن‌ها را حذف کرده یا به کاربرگ دیگری کپی کنید.

در ادامه به کمک تابع Countif، از ثبت داده‌های تکراری در کاربرگ جلوگیری خواهیم کرد.

عدم ثبت داده های تکراری در اکسل با Data Validation

گاهی لازم است که هنگام ورود داده‌ها، ثبت مقدار تکراری در اکسل را کنترل کنیم. ابزار Data Validation به منظور بررسی و اعتبار سنجی داده‌های ورودی یا موجود در کاربرگ مورد استفاده قرار می‌گیرد. در اینجا هم به منظور شناسایی و عدم ثبت مقدار تکراری از Data Validation استفاده خواهیم کرد.

فیلم آموزش مدل سازی مالی با اکسل – تحلیل پروژه های اقتصادی و سبد سهام و سرمایه گذاری در فرادرس

کلیک کنید

یک کاربرگ را در نظر بگیرید که باید در ستون آن کد ملی مشتریان بانک ثبت شود. می‌دانیم که این ستون نباید دارای مقدارهای تکراری باشد. بنابراین ابتدا تعداد سطرهایی که دارای مقداری برابر با سلول مورد نظرمان هست را با تابع Countif، شمارش می‌کنیم. اگر مقدار این تابع بزرگتر از ۱ باشد، نشانگر آن است که مقدار تکراری است و باید توسط Data Validation جلوی ورود اطلاعات گرفته شود.

فرض کنید ناحیه مربوط به کد ملی مشتریان از سلول $$A1:A10$$ است. بنابراین گام‌های زیر را برای انجام این کار طی می‌کنیم.

• ناحیه $$A1:A10$$ را انتخاب کنید.
• از برگه Data دستور Data Validation را اجرا کنید.
• در پنجره ظاهر شده تنظیمات را به شکل تصویر زیر درآورید. توجه داشته باشید که در لیست Allow گزینه Custom را فعال کنید تا بخش Formula در پایین پنجره فعال شود.

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

نکته: به علامت‌های $ در کنار آدرسی که در فرمول Countif نوشته شده دقت کنید. با این کار این ناحیه برای سلول‌های سطرهای بعدی نیز ثابت خواهد ماند.

خلاصه و جمع‌بندی

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

غیر از ابزارهای استاندارد و فرمول‌هایی که در این نوشتار بیان شد، روش‌های دیگری نیز برای شناسایی داده های تکراری در اکسل وجود دارد که توسط شرکت‌های واسط برای اکسل تهیه شده‌اند. البته ناگفته نماند که ممکن است برای تهیه و استفاده از آن‌ها مجبور به پرداخت هزینه نیز باشید.