جشنواره تابستانی اکسل پدیا (یک روز تا پایان)
سبد خرید
1
تمام دسته ها
  • تمام دسته ها
  • Power BI
  • Power Pivot
  • Power Query
  • ابزارها
  • افزونه ها
  • اکسل مدیا
  • توابع اکسل
  • دسته‌بندی نشده
  • دیده بان
  • گوگل شیت
  • مسائل کاربردی
  • معرفی کتاب
  • نمودار ها
  • وی بی - ماکرو

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

مقایسه دو لیست
۳.۷/۵ - (۳ امتیاز)

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

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

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

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

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

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

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

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

مقایسه دو لیست - بارگذاری جدول در پاور کوئری

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

  1. پس از انتخاب گزینه From Table/Range پنجره Power Query Editor باز میشه و در این پنجره مطابق شکل زیر گزینه Close & Load To رو انتخاب می کنیم.

پنجره Power Query Editor

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

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

پنجره Import Data

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

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

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

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

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

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

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

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

مقایسه دو لیست - ادغام کوئری ها

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

نکته:
در صورتی که بخواهیم اسامی افرادی که در مراسم اول (Event 1) حضور داشتن و در مراسم دوم نیستن رو استخراج کنیم، کافیه در پنجره Merge در شکل ۱۱ و در فیلد Join Kind، گزینه Left Anti رو انتخاب کنیم.

 

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

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

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

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

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

دانلود فایل این آموزش

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

126

من سامان چراغی هستم. دانش آموخته مقطع فوق لیسانس دانشگاه تربیت مدرس در رشته مهندسی صنایع. از سال 1388 اکسل و برنامه نویسی VBA رو به صورت حرفه ای شروع کردم.

دیدگاه کاربران
  • موسوی ۶ تیر ۱۴۰۰ / ۹:۲۵ ب٫ظ

    سلام خداقوت.ضمن تقدیروتشکراززحمات کلیه عزیزان.لازم به ذکراست که فایل های ضمیمه آموزش دانلودنمی شوند.!!!

    • سامان چراغی ۷ تیر ۱۴۰۰ / ۹:۱۳ ق٫ظ

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

ارسال دیدگاه

نشانی ایمیل شما منتشر نخواهد شد.

توسط
تومان