مقایسه دو لیست – روش جدید

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

به عنوان یک کاربر اکسل ممکن هست این موضوع براتون پیش اومده باشه که بخواین اطلاعات دو تا لیست رو با هم مقایسه کنید، به طور مثال اسامی افرادی که در دو لیست هستن یا کالاهایی که در دو جدول وجود دارن. اولین راه حلی هم که به ذهن می رسه استفاده از فرمول نویسی هست اما استفاده از فرمول نویسی برای مقایسه دو لیست هم می تونه وقت گیر باشه و هم پیچیده (البته ما همیشه با توجه به نیاز و شرایط مسئله راه حل رو انتخاب میکنیم پس بهتره همه روش ها رو بدونیم تا بتونیم تصمیم درستی بگیریم.) پس پیشنهاد می کنیم با مطالعه این مقاله سرعت انجام کارهاتون رو در اکسل بالا ببرید. در این مقاله قصد داریم این مساله رو با یک ابزار قدرتمند اکسل که در مقالات قبلی معرفی کردیم، یعنی پاور کوئری حل کنیم. برای این منظور با چند مثال، این قابلیت ابزار پاور کوئری رو توضیح خواهیم داد (در واقع این کار یه جور مغایرت گیری با استفاده از پاور کوئری هست و میشه با فرمول نویسی در پاور کوئری، مغایرت گیری های پیشرفته تری هم انجام داد). یاد گرفتن این روش عملا به ما اجازه مقایسه دو جدول در فایل های مختلف اکسل رو میده و میتونیم مقایسه دو فایل اکسل رو با هم انجام بدیم.

مثال۱: استخراج اسامی یکسان دو لیست

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

شکل ۱- جدول اسامی شرکت کننده در Event 1 و Event 2

حالا می خواهیم با استفاده از پاور کوئری اسامی افرادی که در هر دو مراسم شرکت کردند رو استخراج کنیم. برای این کار به شکل زیر عمل می کنیم:

1. ابتدا باید جدول ها رو در پاور کوئری بارگذاری کنیم. برای این منظور روی جدول اول (Event 1) کلیک می کنیم و از تب DATA و زیر مجموعه Get & Transform Data گزینه From Table/Range رو مطابق شکل زیر انتخاب می کنیم.

شکل ۲- بارگذاری جدول در پاور کوئری

2. پس از انتخاب گزینه From Table/Range پنجره Power Query Editor باز میشه و در این پنجره مطابق شکل زیر گزینه Close & Load To رو انتخاب می کنیم. (اگر بخواهیم که مقایسه دو فایل در اکسل رو انجام بدیم، کافیه که به جای انتخاب گزینه From Table/Range، از قسمت Get Data و از بخش From File گزینه From Excel Workbook رو انتخاب کنیم و فایل مورد نظر برای مقایسه رو انتخاب کنیم)

شکل ۳- پنجره Power Query Editor

3. بعد از انتخاب گزینه Close & Load To، پنجره Import Data مطابق شکل زیر نمایش داده می شه که در این پنجره گزینه Only Create Connection رو انتخاب می کنیم:

شکل ۴- پنجره Import Data

بعد از زدن دکمه OK جدول مربوطه در پنجره Queries & Connections به صورت یک کوئری نمایش داده می شه.

شکل ۵- اضافه شدن جدول به صورت یک کوئری در پنجره Queries & Connections

همین مراحل رو برای اضافه کردن جدول دوم (Event 2) طی می کنیم که در نهایت پنجره Queries &Connections بعد از اضافه کردن جدول دوم به شکل زیر در خواهد آمد:

شکل ۶- پنجره Queries & Connections بعد از اضافه شدن جدول دوم

4. از تب Data روی گزینه Get Data کلیک می کنیم و از زیر مجموعه Combine Queries مطابق شکل زیر گزینه Merge رو انتخاب می کنیم.

شکل ۷- ادغام کوئری ها

5. پنجره ای با نام Merge مطابق شکل زیر نمایش داده می شه که در این پنجره در فیلد اول اسم جدول اول (Event 1) و در فیلد دوم اسم جدول دوم (Event 2) رو انتخاب می کنیم.
6. چون اسامی مشترک در دو جدول رو می خواهیم، با کلیک روی ستون Name در هر دو جدول، این ستون رو به حالت انتخاب در میاریم.
7. در فیلد Join Kind گزینه Inner رو انتخاب می کنیم (برای درک بهتر شکل ۱۶ رو ملاحظه کنید). همون طور که در قسمت پایینی فیلد Join Kind در شکل ۸ مشخص شده در دو جدول تعداد ۸ اسم مشترک (از ۲۶ تا) وجود داره. حالا روی دکمه OK کلیک می کنیم.

شکل ۸- نحوه انتخاب گزینه ها در پنجره Merge برای نمایش اسامی مشترک در دو جدول

8. بعد از زدن دکمه OK در مرحله قبل، پنجره Power Query Editor مطابق شکل ۹ نمایش داده میشه. همان طور که در شکل ۹ مشخص هست این جدول حاوی ۸ ردیف هست که همون اسامی مشترک در دو جدول هست. برای انتقال جدول به اکسل، در تب Home روی گزینه Close & Load کلیک می کنیم. می تونیم اسم کوئری رو در پنجره سمت راست در قسمت Query Settings تغییر بدیم.

شکل ۹- انتقال جدول حاوی اسامی مشترک به اکسل

بعد از زدن دکمه Close & Load جدول به صورت زیر در اکسل نمایش داده می شه.

شکل ۱۰- جدول حاوی اسامی مشترک بین دو جدول Event1 , Event2

همان طور که که در شکل ۱۰ مشخص هست این جدول حاوی هشت ردیف از اسامی مشترک هست.

مثال۲: تعیین اسامی افرادی که فقط در Event 2 شرکت کردند

جهت مقایسه دو لیست به این صورت، مراحل زیر رو طی می کنیم:

1. از تب Data روی گزینه Get Data کلیک می کنیم و از زیر مجموعه Combine Queries، گزینه Merge رو انتخاب می کنیم؛
2. بعد از باز شدن پنجره Merge مطابق شکل زیر فیلدهای مربوطه رو انتخاب می کنیم. همان طور که در شکل زیر مشخص هست تمامی فیلدها دقیقا مشابه مثال قبل هست به جز فیلد Join Kind که در اینجا گزینه Right Anti رو انتخاب می کنیم تا اسامی که فقط در جدول دوم قرار دارند نمایش داده بشه و دکمه OK رو می زنیم.

شکل ۱۱- نحوه انتخاب گزینه ها در پنجره Merge برای نمایش اسامی موجود در جدول Event 2

3. در پنجره Power Query Editor و در قسمت Query Settings اسم کوئری رو به Event2Only تغییر می دیم.
4. با توجه به شکل ۱۲ در جدول نمایش داده شده هیچ سطری وجود نداره و این به خاطر این هست که اسامی رو می خواهیم که فقط در جدول دوم (Event 2) یا همون جدول سمت راست موجود باشن. برای نمایش اسمی مطابق شکل ۱۲ روی علامت مشخص شده کلیک می کنیم.

شکل ۱۲- نحوه نمایش اسامی در جدول دوم (Event 2)

5. بعد از کلیک بر روی علامت نمایش داده شده پنجره ای به صورت زیر نمایش داده میشه که گزینه های مشخص در شکل رو انتخاب می کنیم و دکمه OK رو می زنیم.

شکل ۱۳- تنظیمات مربوط به نمایش اسامی جدول Event 2

6. اسامی افراد در جدول دوم مطابق شکل زیر در پنجره Power Query Editor نمایش داده می شه:

شکل ۱۴- نمایش اسامی افراد شرکت کننده در Event 2 در Power Query Editor

7. دو ستون اول رو حذف می کنیم و عبارت Event 2 رو هم از ابتدای عناوین دو ستون بعدی حذف می کنیم.
8. از تب Home روی گزینه Close & Load کلیک می کنیم تا جدول در یک شیت جداگانه از فایل اکسل مطابق شکل زیر نمایش داده بشه.

شکل ۱۵- جدول اسامی شرکت کنندگان که صرفا در Event 2 حاضر بودند

  برای درک بهتر این مفاهیم (انواع حالت های ترکیب جداول) ، شکل زیر (شماره ۱۶) خیلی واضح با رسم شکل، نشون میده که هر اصطلاح چه معنی میده.

شکل ۱۶-معنی اصطلاحات ترکیب جداول در پاورکوئری

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

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

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

ثبت ایمیل جهت دانلود فایل

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

آدرس ایمیل

گروه تخصصی اکسل پدیا | همراه شما در مسیر یادگیری

بارگذاری...