مطالب برتر
حضرت علی (ع): «زَکوةُ العِلمِ نَشرُهُ؛ زکات علم نشر آن است».
وبلاگ "ترفندها" (سایت پی‌سی‌ترفند) برای عمل به این حدیث شریف و با هدف ارتقاء دانش و مهارت‌های مرتبط با رایانه در ۳۰ مهرماه ۱۳۹۱ ایجاد شده است. به امید آنکه مفید واقع شود.
🔵 کانال پی‌سی‌ترفند در تلگرام:
telegram.me/pctarfandir
🔵 ارتباط با ادمین:
telegram.me/pctarfand_admin
تبليغات
۰۵
بهمن ۰۲
نظرات
۰
پیدا کردن داده های مشابه از طریق مقایسه دو ستون در اکسل + ماکرو
مرادی
شناسایی مقادیر تکراری در دو ستون در اکسل یک کار رایج در تجزیه و تحلیل داده ها است. این به شناسایی دقیق خطاهای ورود داده، سوابق تکراری یا برای اهداف پاکسازی داده ها کمک می کند. در ادامه مطلب با پی‌سی‌ترفند همراه باشید تا با چگونگی شناسایی موثر و دقیق موارد تکراری یا مشابه در دو ستون آشنا شوید.
برای یافتن مقادیر تکراری بین دو ستون، بسته به نیاز شما روش‌های مختلفی وجود دارد، مانند برجسته کردن موارد تکراری برای یک نمای کلی بصری یا استخراج آنها برای تجزیه و تحلیل عمیق. در این قسمت به معرفی چند ترفند سریع برای انجام این کار در اکسل می پردازیم.
استفاده از کدنویسی ماکرو برای انتخاب موارد تکراری در دو ستون
با استفاده از فایل زیر که شامل یک کد به زبان VBA است می توانید مقادیر تکراری را بین دو ستون شناسایی و انتخاب کنید.
پس از دانلود و باز کردن فایل اکسل فوق، مسیر زیر را طی کنید:
اطلاعات خودتان را کپی گرفته و در این فایل پیست کنید. سپس مطابق راهنمای درون فایل، روی دکمه اجرای ماکرو کلیک کنید تا پنجره زیر ظاهر شود. لیست داده‌هایی را که می‌خواهید موارد تکراری آن مشخص شود را انتخاب کنید و سپس، روی OK کلیک کنید.
در کادر دوم، فهرست داده‌ای را که می‌خواهید با آن مقایسه کنید انتخاب کنید، و سپس روی OK کلیک کنید:
اکنون، مقادیر تکراری از ستون‌های A و B در ستون A انتخاب می‌شوند و به شما این امکان را می‌دهند که سلول‌ها را با رنگ پر کنید یا در صورت نیاز آنها را کپی کنید.
نکات: این کد VBA به حروف بزرگ و کوچک حساس است.
اگر می خواهید موارد تکراری را از ستون B انتخاب کنید، فقط باید دو ستون انتخاب شده را هنگام انتخاب محدوده داده عوض کنید.
استفاده از Conditional Formatting
برجسته کردن موارد تکراری در دو ستون در اکسل یک روش کارآمد برای شناسایی داده های تکراری است، به ویژه در مجموعه داده های بزرگ که بررسی دستی غیرعملی است. در این مورد، استفاده از Conditional Formatting یک ابزار مفید برای این کار است.
مرحله 1: داده ها را در ستونی که می خواهید موارد تکراری را برجسته کنید، انتخاب کنید.
بطور مثال در تصویر زیر ردیف های A2:A10 انتخاب شده است:
مرحله 2: ویژگی Conditional Formatting را اعمال کنید
1. مطابق تصویر زیر از تب Home روی Conditional Formatting و سپس New rule کلیک کنید:
2. در کادر محاوره‌ای New Formatting Rule، لطفاً عملیات زیر را انجام دهید:
2.1 از فهرست Select a Rule Type مورد Use a formula to determine which cells to format را انتخاب کنید.
2.2 فرمول زیر را در جعبه متنی Format values where this formula is true وارد کنید.
=COUNTIF($B$2:$B$10, A2)>0
توجه داشته باشید در فرمول بالا، B2:B10 فهرست داده‌ای را نشان می‌دهد که می‌خواهید با آن مقایسه کنید، A2 اولین سلول ستونی است که می‌خواهید موارد تکراری را از آنجا برجسته کنید. این فرمول بررسی می‌کند که آیا مقدار سلول A2 در هر نقطه‌ای از ستون B یافت می‌شود. آدرس سلول ها را مطابق با داده‌های خود تغییر دهید.
2.3 سپس روی دکمه Format کلیک کنید.
3. در کادر محاوره‌ای Format Cells که ظاهر می‌شود، یک رنگ را مشخص کنید که می‌خواهید موارد تکراری را برجسته کنید. و روی OK کلیک کنید.
4. در پایان در کادر محاوره ای New Formatting Rule، روی دکمه OK کلیک کنید.
اکنون، مقادیری که در هر دو ستون A و B تکراری هستند، در ستون A مانند تصویر زیر نشان داده شده اند:
نکات: شناسایی موارد تکراری در Conditional Formatting به حروف بزرگ و کوچک حساس نیست.
اگر می‌خواهید موارد تکراری ستون B را برجسته کنید، فقط باید ابتدا ستون B را انتخاب کنید و سپس فرمول زیر را در قالب‌بندی شرطی اعمال کنید:
=COUNTIF($A$2:$A$10, B2)>0
استفاده از فرمول
برای یافتن و استخراج موارد تکراری بین دو ستون، میتوانید از فرمول برای مشخص کردن و بیرون کشیدن موارد تکراری استفاده کنید. فرمول زیر را در یک سلول خالی که می‌خواهید نتیجه را در آن قرار دهید، کپی کرده و به پایین بکشید تا این فرمول در سلول‌های دیگر ستون اعمال شود.
=IF(ISERROR(MATCH(A2,$B$2:$B$10,0))،"""،A2)
نکته: در فرمول بالا، A2 اولین سلول ستونی است که می‌خواهید موارد تکراری را از آن پیدا کنید. B2:B10 نشان دهنده لیست داده هایی است که می خواهید با آن مقایسه کنید. نتیجه: همانطور که می بینید، اگر داده های ستون A در ستون B وجود داشته باشد، مقدار نمایش داده می شود. در غیر این صورت، سلول ها خالی خواهند ماند.
همانطور که می بینید، اگر داده های ستون A در ستون B وجود داشته باشد، مقدار نمایش داده می شود. در غیر این صورت، سلول ها خالی خواهند ماند.
نکته: این فرمول به حروف بزرگ و کوچک حساس نیست.
برای مقایسه دو ستون بصورت سطر به سطر، راه های مختلفی وجود دارد، از فرمول های آسان گرفته تا ویژگی های خاص، بنابراین می توانید بهترین را برای نیازهای داده های خود انتخاب کنید.
با فرض اینکه داده‌هایی در ستون A و ستون B دارید، برای بررسی اینکه آیا داده‌های دو ستون مطابقت دارند می توانید از عملگر مساوی با (=)، تابع IF و تابع EXACT مطابق تصویر زیر استفاده کنید:
مثال ها و توضیحات بیشتر در سایت زیر موجود است.
برای مطلع شدن از جدیدترین مطالب سایت، کانال پی‌سی‌ترفند در تلگرام را با آی‌دی pctarfandir@ دنبال کنید:
|
سوالات خود در زمینه ورد و اکسل و ... را میتوانید از طریق ایمیل و تلگرام با ما مطرح کنید. آدرس ایمیل: tarfandha.blog@gmail.com آی‌دی تلگرام: pctarfand_admin@ |
