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

پیدا کردن داده های تکراری در اکسل یکی از موضوعاتی است که معمولا افرادی که از اکسل استفاده میکنند با آن مواجه خواهند شد. این کار را میتوان با استفاده از ابزارهای پیش فرض اکسل(Data Validation و Remove Duplicate Value) و یا فرمول نویسی در اکسل انجام داد. مهمترین موضوعی که در این مقاله ارائه شده حذف محدودیت اکسل در این زمینه با استفاده از ماژول برنامه نویسی شده در محیط ماکرونویسی اکسل است که میتوان با استفاده از آن داده های تکراری را در اکسل را در رنگ های متفاوت مشخص نمود. معمولا یکی از مواردی که حسابداران و مدیران مالی در این رابطه مواجه میشوند مرتبط با مغایرت گیری بین دو کاربرگ است.

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

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

 

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

یکی از روش‌های ساده و آسان برای پیدا کردن داده یا مقدار تکراری در اکسل، مرتب‌سازی(Sort) است. واضح است که با مرتب‌سازی یک جدول براساس یک ستون، مقادیر تکراری در آن ستون در سطرهایی در کنار یکدیگر قرار می‌گیرند. به این ترتیب به راحتی می‌توانیم مقدارهای تکراری را شناسایی کنیم. به ” تصویر شماره 1 “توجه کنید. این کاربرگ، شامل مقادیر تکراری در ستون C است. با استفاده از مرتب‌سازی به سادگی می‌توانید مقدارهای تکراری را تشخیص دهید.

برای اینکار باید داده‌ها را مرتب کنیم. برای مرتب‌سازی ساده این ستون کافی است که کلیه جدول را انتخاب کنید و سپس مسیر زیر را طی کنید:

Data > Filter

سپس مطابق تصویر زیر داده ها را مرتب کنید:

داده‌ها مرتب شده و مقادیر تکراری در کنار یکدیگر قرار می‌گیرند. و نتیجه بصورت زیر خواهد بود:

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

 

یافتن داده های تکراری در اکسل با قالب بندی شرطی(Conditional Formatting)

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

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

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

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

نکته: هر چند این روش ساده است ولی همه مقادیر تکراری را با یک رنگ نشان می‌دهد. برای اینکه بتوانیم بصورت خودکار دادهای تکراری در اکسل را در رنگهای متفاوت نشان دهیم در ادامه مقاله با ما همراه باشید.

 

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

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

Data > Remove Duplicates

سپس ستونی که میخواهید مورد مقایسه گردد و بر اساس آن ردیف های تکراری حذف شود را بصورت زیر انتخاب کنید و OK را بزنید:

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

 

مشخص کردن داده های تکراری در اکسل در رنگ های مختلف

برای مشخص کردن داده های تکراری در اکسل قابلیتی تعریف نشده است(یا حداقل ما از آن اطلاع نداریم) لذا برای اینکار باید وارد محیط برنامه نویسی یا توسعه دهنده اکسل شویم و فرمولهای مورد نظر خودمان را اضافه کنیم، فقط باید توجه داشته باشید که شما باید به زبان برنامه نویسی Visual Basic تسلط داشته باشید تا بتوانید در این محیط فرمول خودتان را اضافه کنید.

با توجه به اینکه اکثر افراد این زبان برنامه نویسی را نمیشناسند ما ماژول لازم را در این رابطه تهیه و نمودیم و لینک آن را در انتهای نوشته قرار دادیم تا شما بدون دانش برنامه نویسی آن را به اکسل خودتان اضافه کنید و بتوانید داده های تکراری را در اکسل در رنگهای مختلف مشخص کنید. برای اضافه کردن ماژول مذکور فقط لازم است مسیر زیر را طی کنید:

• بعد از دانلود فایل زیپ از فروشگاه سایت باید بصورت زیر آنرا از حالت زیپ خارج کنید:

• بر روری Extract files کلیک کنید و سپس بر روی OK کلیک کنید:

• وارد محیط اکسل شوید و دکمه های ترکیبی Alt + F11 را فشار دهید تا پنجره زیر در اکسل نمایان شود و سپس مراحل تصویر زیر را طی و روی Import File کلیک کنید:

• حالا پنجره زیر باز میشود، باید مسیر فایلی که از سایت دانلود کرده اید را مشخص کنید:

• بعد از زدن دکمه Open در قسمت ماژول ها بصورت زیر Module1 اضافه میشود و شما لازم است بر روی آن راست کلیک کنید و View codes را بزنید:

• بعد از مرحله بالا برروی دکمه Run Sub کلیک کنید تا کدها اجرا شوند:

• سپس پنجره اصلی اکسل بصورت زیر باز میشود که شما باید داده های مورد نظر خودتان را در آن انتخاب کنید و بر روی OK کلیک کنید:

• در نهایت نتیجه بصورت زیر ظاهر میشود:

ویدیو آموزشی اضافه کردن ماژول پیدا کردن داده های تکراری در رنگهای متفاوت

 

دانلود ماژول داده تکراری