مقایسه دو لیست در اکسل – از صفر تا صد + مثال

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

روش‌های مختلف مقایسه دو لیست در اکسل

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

۱. ابزارهای ساده اکسل عبارت‌اند از:

• قالب‌بندی شرطی
• Go to Special

۲. توابع و فرمول‌نویسی برای مقایسه دو لیست در اکسل عبارت‌اند از:

• عملگر مساوی
• تابع IF
• تابع COUNTIF
• تابع VLOOKUP و XLOOKUP

۳. ابزار پیشرفته اکسل که به صورت زیر است:

• POWER QUERY

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

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

در ادامه بحث روش کار با هر یک از روش‌ها را با مثال‌های ساده توضیح می‌دهیم.

مقایسه دو لیست با ابزارهای ساده داخلی اکسل

ابزارهای داخلی اکسل که در قسمت «نوار اکسل» (Ribbon) قرار دارند، روشی ساده و سریع برای مقایسه دو لیست در اکسل هستند. ویژگی مهم این روش امکان هایلایت کردن خودکار اختلاف‌های دو لیست است و از نظر دیداری جذابیت بیشتری دارند. اما این روش‌ها برای داده‌های تعداد کم بیشتر مورد استفاده قرار می‌گیرند.

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

کلیک کنید

دو ابزار قالب‌بندی شرطی و «Go to Special» برای مقایسه دو لیست در اکسل کاربرد دارند که هر یک را توضیح می‌دهیم.

ابزار قالب‌بندی شرطی

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

مثال

فرض می‌کنیم برای یک کمپین بازاریابی ایمیلی دو لیست از مشتریان فعلی و مشتریان جدید مانند تصویر زیر داریم.

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

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

• از منوی آبشاری باز شده گزینه «Highlight Cells Rules» و سپس «Duplicate Values» را انتخاب می‌کنیم.

• قالب دلخواه خود را از منوی آبشاری انتخاب می‌کنیم و دکمه «OK» را می‌زنیم.

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

• حال برای هایلایت کردن افراد جدید یا داده‌های غیر مشابه در دو لیست، اولین بخش از پنجره باز شده را روی «Unique» می‌گذاریم، سپس رنگ دلخواه خود را انتخاب می‌کنیم.

با این کار اسامی غیرتکراری با رنگ دلخواه زرد مشخص می‌شوند. البته از ابتدای کار نیز می‌توانیم با انتخاب گزینه «Unique» فقط داده‌های غیر تکراری را مشخص کنیم.

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

 ابزار Go to Special

«Go to Special» نیز یکی از ابزارهای داخلی اکسل است که از آن برای مقایسه سطر به سطر دو لیست از جدول و هایلایت کردن آن استفاده می‌کنیم. بر خلاف ابزار قالب‌بندی شرطی، این ابزار قادر به شناسایی داده‌های تکراری در کل جدول نیست و فقط یک سطر از لیست اول را با سطر روبروی آن در لیست دوم مقایسه می‌کند.

مثال

دو فهرست فرضی از اسامی دانشجویان دو کلاس مطابق جدول زیر داریم.

حال برای مقایسه سطر به سطر این دو لیست مراحل زیر را انجام می‌دهیم.

• بعد از انتخاب جدول، از زبانه «Home» در بخش «Editing» روی علامت «Find & Select» و سپس دکمه «Go to Special» کلیک می‌کنیم.

• در پنجره باز شده بخش «Row Differences» را علامت می‌زنیم.

• بعد از فشار دادن دکمه «OK» اکسل به شکل خودکار هر سطر از جدول را با یکدیگر مقایسه می‌کند و مواردی که یکسان نیستند را هایلایت می‌کند.

مقایسه دو لیست با فرمول‌نویسی و توابع اکسل

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

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

کلیک کنید

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

فرمول‌نویسی با عملگر مساوی

در فرمول‌نویسی با عملگر مساوی نیز سطر به سطر هر لیست با یکدیگر مقایسه می‌شوند. به این شکل اگر داده‌ها یکسان باشند، خروجی فرمول عبارت «True» خواهد بود.

مثال

فرض می‌کنیم لیست قبلی مربوط به کمپین به شکل زیر است.

• برای مقایسه سطر به سطر ابتدا یک ستون جدید به نام «نتیجه» ایجاد می‌کنیم. سپس در اولین سلول مانند C2، فرمول =A2=B2 را می‌نویسیم.

• از آنجا که داده‌های دو سطر یکسان هستند، خروجی فرمول عبارت «True» است.

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

توابع جستجوی VLOOKUP و XLOOKUP

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

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

کلیک کنید

مثال

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

ابتدا یک ستون اضافی به‌نام «نتیجه» ایجاد می‌کنیم. سپس در سلول C2 فرمول =VLOOKUP(B2,$A$2:$B$9,1,FALSE) را می‌نویسیم.

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

• B2 : اولین عبارت از لیست اول که می‌خواهیم در جدول جستجو کنیم.
• A2:B9 : کل محدوده مورد نظر برای جستجو درجدول
• 1 :‌ عدد یک مشخص می‌کند که جستجو در اولین ستون از محدوده انتخاب شده انجام شود.
• FALSE : نشان‌دهنده این موضوع است که جستجو به شکل تطابق دقیق عبارت انجام گیرد.

با اجرای فرمول در سلول C2 از آنجا که نام «سارا محمدی» در لیست دوم تکراری است، همان نام بار دیگر به‌عنوان خروجی نمایش داده می‌شود.

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

برای خواناتر شدن نتیجه در این مثال می‌توانیم تابع IFERROR را به شکل ترکیبی استفاده کنیم. طوری‌که در صورت متفاوت بودن اسم‌ها در لیست دوم، عبارت «مشتری جدید» به‌جای #NA نوشته شود. فرمول و نتیجه نهایی در این حالت به شکل تصویر زیر خواهد بود.

=IFERROR(VLOOKUP(B2,$A$2:$B$9,1,FALSE),"مشتری جدید")

تابع شرطی IF

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

مثال

فرض می‌کنیم دو لیست از اسامی دانش‌آموزان به شرح جدول زیر داریم.

اگر بخواهیم سطرهای این دو لیست را از نظر یکسان بودن اسامی مقایسه کنیم، مراحل زیر را انجام می‌دهیم.

ابتدا یک ستون جدید در کنار دو لیست درست می‌کنیم. سپس فرمول =IF(A2=B2,"متفاوت", "یکسان") را در اولین سلول می‌نویسیم.

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

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

تابع شمارش COUNTIF

تابع COUNTIF یکی از توابع کاربردی و انعطاف‌پذیر اکسل برای شمارش تعداد دفعات تکرار در لیست است. بنابراین زمانی از آن استفاده می‌کنیم که هدف اصلی شمارش تکرار داده‌های یک لیست در دیگری است.

فیلم آموزش شمارش سلول ها در اکسل + تابع COUNTIF و COUNTIFS (رایگان) در فرادرس

کلیک کنید

مثال

برای شمارش تعداد داده‌های تکراری در جدول فرضی اسامی دانشجویان، مراحل زیر را انجام می‌دهیم.

• یک ستون جدید ایجاد می‌کنیم و سپس فرمول =COUNTIF($B$2:$B$8,A2) را در اولین سلول می‌نویسیم. این فرمول مشخص می‌کند که عبارت سلول A2 از لیست اول چند بار در لیست دوم، یعنی محدوده B2:B8 تکرار شده است.

همان‌طور که در تصویر مشخص است، در اولین عملیات شمارش، فرمول اسم «سارا عزیزی» از لیست اول را در لیست دوم جستجو می‌کند و چون نمونه مشابهی وجود ندارد، نتیجه را با عدد «صفر» در ستون نتیجه نشان می‌دهد. به این معنا که عبارت تکراری نیست.

• حال فرمول را در سایر سلول‌ها کپی می‌کنیم. مانند تصویر، تعداد تکرار هر عبارت نوشته شده در سلول‌های لیست اول در لیست دوم مشخص است.

یادگیری سریع کار با توابع و ابزارهای اکسل در فرادرس

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

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

• فیلم آموزش ابزارهای کاربردی اکسل همراه گواهینامه در فرادرس
• فیلم آموزش استفاده از توابع و فرمول‌نویسی در اکسل همراه گواهینامه در فرادرس
• فیلم آموزش رایگان مغایرت‌گیری در اکسل در فرادرس
• فیلم آموزش رایگان ابزار Power Query در اکسل در فرادرس

همچنین در دو مجموعه فیلم آموزشی زیر امکان انتخاب موارد بیشتر وجود دارد.

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

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

ابزار PowerQuery روش پیشرفته برای مقایسه دو لیست در اکسل به‌خصوص تعداد داده‌های بالا است. این ابزار علاوه بر مقایسه دو لیست، ویژگی‌هایی مانند امکان تغییر یا پاک کردن داده‌ها را نیز دارد. بنابراین تحلیل دو لیست دقیق‌تر و حرفه‌ای‌تر است. در ادامه بحث به‌صورت خلاصه یک مثال ساده برای کار با این ابزار را توضیح می‌دهیم. اما برای یادگیری تکمیلی پیشنهاد می‌کنیم فیلم آموزش رایگان ابزار Power Query در اکسل در فرادرس را نیز مشاهده کنید.

مثال

• در جدول فرضی اسامی دانشجویان، ابتدا کل سطرها و ستون‌ها را انتخاب می‌کنیم. سپس از زبانه «Insert» روی بخش «Table» کلیک می‌کنیم و در پنجره باز شده تیک گزینه «My table has headers» را می‌زنیم تا یک جدول ایجاد شود.

• در زبانه «Data» روی بخش «Get Data» کلیک می‌کنیم.

• از منوی آبشاری مربوط به ترتیب بخش «From other sources» و سپس «From Table/Range» را انتخاب می‌کنیم.

• در این حالت جدول اکسل وارد پنجره «Power Query» می‌شود.

• هر دو ستون جدول را انتخاب می‌کنیم، سپس از زبانه «Transform» به گزینه «unpivot table» می‌رویم.

• بعد از انتخاب این دکمه، جدول به شکل تصویر زیر در می‌آید.

• روی هدر جدول یعنی بخش «value» کلیک راست می‌کنیم و روی گزینه «Remove Duplicates» می‌زنیم. با این کار داده‌های تکراری از لیست دوم حذف می‌شوند.

• همچنین می‌توانیم با کلیک روی دکمه آبشاری «value» داده‌ها را به ترتیب صعودی یا نزولی مرتب کنیم.

جمع‌بندی پایانی

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

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