فهرست
اکسل یک برنامه پردازش داده کارآمد است. و یکی از روش های تحلیل اطلاعات مقایسه دو لیست است. اگر دو لیست را در اکسل به درستی مقایسه کنید، سازماندهی این فرآیند بسیار آسان خواهد بود. کافی است برخی از نکاتی را که امروز به آن پرداخته می شود رعایت کنید. اجرای عملی این روش کاملاً به نیازهای فرد یا سازمان در یک لحظه خاص بستگی دارد. بنابراین باید چندین مورد احتمالی را در نظر گرفت.
مقایسه دو لیست در اکسل
البته می توانید دو لیست را به صورت دستی مقایسه کنید. اما زمان زیادی طول خواهد کشید. اکسل جعبه ابزار هوشمند خود را دارد که به شما امکان می دهد نه تنها به سرعت داده ها را مقایسه کنید، بلکه اطلاعاتی را نیز به دست آورید که بدست آوردن آنها با چشمان شما چندان آسان نیست. فرض کنید دو ستون با مختصات A و B داریم. برخی از مقادیر در آنها تکرار می شود.
فرمول بندی مسئله
بنابراین باید این ستون ها را با هم مقایسه کنیم. روش مقایسه دو سند به شرح زیر است:
- اگر سلول های منحصر به فرد هر یک از این لیست ها یکسان باشد و تعداد کل سلول های منحصر به فرد یکسان باشد و سلول ها یکسان باشند، می توان این لیست ها را یکسان در نظر گرفت. ترتیبی که مقادیر در این لیست روی هم چیده شده اند چندان مهم نیست.
- اگر مقادیر منحصر به فرد خود یکسان باشند، اما تعداد تکرارها متفاوت است، می توانیم در مورد همزمانی جزئی لیست ها صحبت کنیم. بنابراین، چنین لیست هایی ممکن است حاوی تعداد متفاوتی از عناصر باشند.
- این واقعیت که این دو لیست مطابقت ندارند با مجموعه متفاوتی از مقادیر منحصر به فرد نشان داده می شود.
همه این سه شرط به طور همزمان شرایط مشکل ما هستند.
راه حل مشکل
بیایید دو محدوده پویا ایجاد کنیم تا مقایسه لیست ها آسان تر شود. هر یک از آنها با هر یک از لیست ها مطابقت دارد. 
برای مقایسه دو لیست، موارد زیر را انجام دهید:
- در یک ستون جداگانه، لیستی از مقادیر منحصر به فرد را ایجاد می کنیم که مختص هر دو لیست است. برای این ما از فرمول استفاده می کنیم: ЕСЛИОШИБКА(ЕСЛИОШИБКА( ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0)); ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0))); «»). خود فرمول باید به صورت یک فرمول آرایه نوشته شود.
- بیایید تعیین کنیم که هر مقدار منحصر به فرد چند بار در آرایه داده رخ می دهد. در اینجا فرمول های انجام این کار وجود دارد: =COUNTIF(List1,D5) و =COUNTI(List2,D5).
- اگر هم تعداد تکرارها و هم تعداد مقادیر منحصر به فرد در همه لیست هایی که در این محدوده ها گنجانده شده اند یکسان باشد، تابع مقدار 0 را برمی گرداند. این نشان می دهد که تطابق XNUMX٪ است. در این صورت سرفصل های این لیست ها پس زمینه سبز رنگی پیدا می کنند.
- اگر تمام محتوای منحصر به فرد در هر دو لیست باشد، توسط فرمول ها برگردانده می شود =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;E5:E34;0) и =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;F5:F34;0) مقدار صفر خواهد بود. اگر E1 حاوی صفر نباشد، اما چنین مقداری در سلول های E2 و F2 وجود دارد، در این صورت محدوده ها به عنوان تطبیق شناخته می شوند، اما فقط تا حدی. در این صورت سرفصل های لیست های مربوطه نارنجی می شوند.
- و اگر یکی از فرمول های توضیح داده شده در بالا مقدار غیر صفر را برگرداند، لیست ها کاملاً غیر منطبق خواهند بود.
این پاسخ به این سوال است که چگونه می توان ستون ها را برای مطابقت با استفاده از فرمول ها تجزیه و تحلیل کرد. همانطور که می بینید، با استفاده از توابع، می توانید تقریباً هر کاری را که در نگاه اول به ریاضیات مرتبط نیست، پیاده سازی کنید.
تست نمونه
در نسخه ما از جدول، سه نوع لیست از هر نوع شرح داده شده در بالا وجود دارد. دارای تطبیق جزئی و کامل و همچنین عدم تطبیق است.
برای مقایسه داده ها، از محدوده A5:B19 استفاده می کنیم که به طور متناوب این جفت لیست ها را درج می کنیم. در مورد اینکه نتیجه مقایسه چه خواهد شد، با رنگ لیست های اصلی متوجه خواهیم شد. اگر آنها کاملاً متفاوت باشند، پس زمینه قرمز خواهد بود. اگر بخشی از داده ها یکسان است، زرد است. در صورت هویت کامل، عناوین مربوطه سبز خواهند بود. چگونه می توان یک رنگ را بسته به نتیجه حاصل کرد؟ این نیاز به قالب بندی شرطی دارد.
یافتن تفاوت در دو لیست به دو روش
بیایید دو روش دیگر را برای یافتن تفاوت ها، بسته به اینکه لیست ها همزمان هستند یا نه، شرح دهیم.
گزینه 1. لیست های همزمان
این یک گزینه آسان است. فرض کنید چنین لیست هایی داریم.
برای تعیین اینکه چند بار مقادیر همگرا نشده اند، می توانید از فرمول استفاده کنید: =SUMPRODUCT(—(A2:A20<>B2:B20)). اگر در نتیجه 0 بدست آوریم، به این معنی است که این دو لیست یکسان هستند.
گزینه 2: فهرست های مختلط
اگر لیست ها از نظر ترتیب اشیاء حاوی یکسان نیستند، باید ویژگی هایی مانند قالب بندی شرطی و رنگ آمیزی مقادیر تکراری را اعمال کنید. یا از تابع استفاده کنید COUNTIF، با استفاده از آن تعیین می کنیم که یک عنصر از یک لیست چند بار در لیست دوم رخ می دهد.
نحوه مقایسه 2 ستون ردیف به ردیف
وقتی دو ستون را با هم مقایسه می کنیم، اغلب نیاز داریم که اطلاعاتی را که در ردیف های مختلف قرار دارند با هم مقایسه کنیم. برای انجام این کار، اپراتور به ما کمک می کند اگر بیایید نگاهی به نحوه عملکرد آن در عمل بیاندازیم. برای انجام این کار، چندین موقعیت گویا را ارائه می دهیم.
مثال. نحوه مقایسه 2 ستون برای مطابقت و تفاوت در یک ردیف
برای تجزیه و تحلیل اینکه آیا مقادیری که در یک ردیف هستند اما ستون های مختلف یکسان هستند یا خیر، تابع را می نویسیم IF. فرمول در هر ردیف قرار داده شده در ستون کمکی که در آن نتایج پردازش داده ها نمایش داده می شود، درج می شود. اما اصلاً لازم نیست آن را در هر ردیف تجویز کنید، فقط آن را در سلول های باقی مانده این ستون کپی کنید یا از نشانگر تکمیل خودکار استفاده کنید.
ما باید چنین فرمولی را بنویسیم تا بفهمیم آیا مقادیر در هر دو ستون یکسان هستند یا خیر: =IF(A2=B2، "Match"، ""). منطق این تابع بسیار ساده است: مقادیر سلول های A2 و B2 را با هم مقایسه می کند و در صورت یکسان بودن، مقدار "Coincide" را نمایش می دهد. اگر داده ها متفاوت باشد، هیچ مقداری را بر نمی گرداند. همچنین می‌توانید سلول‌ها را بررسی کنید تا ببینید آیا مطابقت بین آنها وجود دارد یا خیر. در این مورد، فرمول استفاده شده به صورت زیر است: =IF(A2<>B2، "مطابق نیست"، ""). اصل یکسان است، ابتدا بررسی انجام می شود. اگر معلوم شود که سلول ها معیار را برآورده می کنند، مقدار "مطابقت ندارد" نمایش داده می شود.
همچنین می‌توانید از فرمول زیر در فیلد فرمول استفاده کنید تا هم «Match» را در صورت یکسان بودن مقادیر و هم «Do not match» را در صورت متفاوت بودن نشان دهید: =IF(A2=B2؛ «مطابقت»، «تطابق ندارد»). همچنین می توانید به جای عملگر برابری از عملگر نابرابری استفاده کنید. فقط ترتیب مقادیری که در این مورد نمایش داده می شود کمی متفاوت خواهد بود: =IF(A2<>B2، "مطابق نیست"، "مصادف"). پس از استفاده از نسخه اول فرمول، نتیجه به شرح زیر خواهد بود.
این تغییر فرمول به حروف بزرگ و کوچک حساس نیست. بنابراین، اگر مقادیر موجود در یک ستون تنها به این دلیل که با حروف بزرگ نوشته شده اند با سایرین تفاوت داشته باشند، برنامه متوجه این تفاوت نخواهد شد. برای اینکه مقایسه به حروف کوچک و بزرگ حساس باشد، باید از تابع در معیارها استفاده کنید دقیق. بقیه استدلال ها بدون تغییر باقی می مانند: =IF(EXACT(A2,B2)، "Match"، "Unique").
چگونه چندین ستون را برای مطابقت در یک ردیف مقایسه کنیم
تجزیه و تحلیل مقادیر موجود در لیست ها با توجه به مجموعه ای از معیارها امکان پذیر است:
- سطرهایی را پیدا کنید که در همه جا مقادیر یکسانی دارند.
- آن ردیف هایی را که در آنها منطبق است فقط در دو لیست پیدا کنید.
بیایید به چند نمونه از نحوه ادامه در هر یک از این موارد نگاه کنیم.
مثال. نحوه یافتن موارد منطبق در یک ردیف در چندین ستون یک جدول
فرض کنید یک سری ستون داریم که حاوی اطلاعات مورد نیاز ما هستند. ما با وظیفه تعیین ردیف هایی روبرو هستیم که در آنها مقادیر یکسان است. برای این کار باید از فرمول زیر استفاده کنید: =IF(AND(A2=B2,A2=C2)، "مطابقت"، "").
اگر تعداد ستون ها در جدول بسیار زیاد است، فقط باید از آن همراه با تابع استفاده کنید IF اپراتور COUNTIF: =IF(COUNTIF($A2:$C2,$A2)=3;"مطابقت";" "). عدد استفاده شده در این فرمول نشان دهنده تعداد ستون های مورد بررسی است. اگر متفاوت است، پس باید تا آنجایی که برای موقعیت شما صادق است بنویسید.
مثال. نحوه پیدا کردن موارد منطبق در یک ردیف در هر 2 ستون از یک جدول
فرض کنید باید بررسی کنیم که آیا مقادیر موجود در یک ردیف در دو ستون با مقادیر موجود در جدول مطابقت دارند یا خیر. برای این کار باید از تابع به عنوان شرط استفاده کنید OR، جایی که به طور متناوب برابری هر یک از ستون ها را با ستون دیگر بنویسید. به عنوان مثال.
ما از این فرمول استفاده می کنیم: =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “). ممکن است موقعیتی وجود داشته باشد که تعداد زیادی ستون در جدول وجود داشته باشد. در این حالت، فرمول بسیار بزرگ خواهد بود و ممکن است زمان زیادی برای انتخاب تمام ترکیبات لازم صرف شود. برای حل این مشکل باید از تابع استفاده کنید COUNTIF: =IF(COUNTIF(B2:D2,A2)+COUNTIF(C2:D2,B2)+(C2=D2)=0؛ "رشته منحصر به فرد"؛ "رشته منحصر به فرد")
می بینیم که در کل دو تابع داریم COUNTIF. با اولی به طور متناوب تعیین می کنیم که چند ستون با A2 شباهت دارند و با دومی تعداد شباهت ها را با مقدار B2 بررسی می کنیم. اگر در نتیجه محاسبه با این فرمول، مقدار صفر به دست آوریم، این نشان می دهد که تمام ردیف های این ستون منحصر به فرد هستند، اگر بیشتر باشد، شباهت هایی وجود دارد. بنابراین، اگر در نتیجه محاسبه با دو فرمول و جمع کردن نتایج نهایی، مقدار صفر به دست آوریم، مقدار متنی "رشته منحصر به فرد" برگردانده می شود، اگر این عدد بزرگتر باشد، نوشته می شود که این رشته منحصر به فرد نیست.
نحوه مقایسه 2 ستون در اکسل برای مطابقت
حالا بیایید یک مثال بزنیم. فرض کنید یک جدول با دو ستون داریم. باید بررسی کنید که آیا مطابقت دارند یا خیر. برای انجام این کار، باید فرمول را اعمال کنید، جایی که تابع نیز استفاده خواهد شد IF، و اپراتور COUNTIF: =IF(COUNTIF($B:$B،$A5)=0، "هیچ منطبقی در ستون B وجود ندارد"، "در ستون B مطابقت دارد")
هیچ اقدام دیگری لازم نیست. پس از محاسبه نتیجه توسط این فرمول، اگر مقدار آرگومان سوم تابع را بدست آوریم IF مسابقات. اگر هیچ کدام وجود ندارد، پس محتویات استدلال دوم.
نحوه مقایسه 2 ستون در اکسل برای مطابقت و هایلایت با رنگ
برای سهولت تشخیص بصری ستون های مطابق، می توانید آنها را با یک رنگ برجسته کنید. برای انجام این کار، باید از تابع "قالب بندی شرطی" استفاده کنید. بیایید در عمل ببینیم.
پیدا کردن و برجسته کردن مسابقات بر اساس رنگ در چندین ستون
برای تعیین منطبقات و برجسته کردن آنها، ابتدا باید محدوده داده ای را انتخاب کنید که در آن بررسی انجام می شود و سپس مورد "قالب بندی شرطی" را در برگه "صفحه اصلی" باز کنید. در آنجا، "Duplicate Values" را به عنوان قانون انتخاب سلول انتخاب کنید.
پس از آن، یک کادر محاوره ای جدید ظاهر می شود که در آن در لیست پاپ آپ سمت چپ گزینه "تکرار" را پیدا می کنیم و در لیست سمت راست رنگی را انتخاب می کنیم که برای انتخاب استفاده می شود. پس از کلیک بر روی دکمه "OK"، پس زمینه تمام سلول های دارای شباهت انتخاب می شود. سپس ستون ها را با چشم مقایسه کنید.
پیدا کردن و برجسته کردن خطوط منطبق
تکنیک بررسی مطابقت رشته ها کمی متفاوت است. ابتدا باید یک ستون اضافی ایجاد کنیم و در آنجا با استفاده از عملگر & از مقادیر ترکیبی استفاده خواهیم کرد. برای انجام این کار، باید فرمولی از فرم را بنویسید: =A2&B2&C2&D2.
ستونی که ایجاد شده و حاوی مقادیر ترکیبی است را انتخاب می کنیم. در مرحله بعد، همان دنباله ای از اقدامات را انجام می دهیم که در بالا برای ستون ها توضیح داده شده است. خطوط تکراری با رنگی که شما مشخص می کنید برجسته می شوند.
می بینیم که هیچ چیز سختی در جستجوی تکرار وجود ندارد. اکسل شامل تمام ابزارهای لازم برای این کار است. مهم است که قبل از به کار بردن این همه دانش، فقط تمرین کنید.