ادغام دو لیست بدون تکرار

فهرست

یک موقعیت کلاسیک: شما دو لیست دارید که باید در یک لیست ادغام شوند. علاوه بر این، در لیست های اولیه می تواند هم عناصر منحصر به فرد و هم عناصر مطابق (هم بین لیست ها و هم در داخل) وجود داشته باشد، اما در خروجی باید یک لیست بدون تکرار (تکرار) دریافت کنید:

بیایید به طور سنتی به چندین روش برای حل چنین مشکل رایج نگاه کنیم - از ابتدایی "روی پیشانی" تا پیچیده تر، اما ظریف.

روش 1: موارد تکراری را حذف کنید

می توانید مشکل را به ساده ترین راه حل کنید - عناصر هر دو لیست را به صورت دستی کپی کنید و سپس ابزار را در مجموعه به دست آمده اعمال کنید. موارد تکراری را حذف کنید از زبانه داده ها (داده ها - حذف موارد تکراری):

ادغام دو لیست بدون تکرار

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

روش 1a. جدول محوری

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

ادغام دو لیست بدون تکرار

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

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

روش 2: فرمول آرایه

می توانید با فرمول ها مشکل را حل کنید. در این صورت، محاسبه مجدد و به روز رسانی نتایج به صورت خودکار و آنی بلافاصله پس از تغییرات در لیست های اصلی انجام می شود. برای راحتی و اختصار، بیایید نام لیست های خود را ذکر کنیم. فهرست 1 и فهرست 2با استفاده از مدیر نام برگ فرمول (فرمول ها - مدیر نام - ایجاد):

ادغام دو لیست بدون تکرار

پس از نامگذاری، فرمول مورد نیاز به شکل زیر خواهد بود:

ادغام دو لیست بدون تکرار

در نگاه اول، ترسناک به نظر می رسد، اما، در واقع، همه چیز چندان ترسناک نیست. اجازه دهید این فرمول را در چندین خط با استفاده از کلید ترکیبی Alt+Enter و تورفتگی با فاصله گسترش دهم، همانطور که برای مثال در اینجا انجام دادیم:

ادغام دو لیست بدون تکرار

منطق در اینجا به شرح زیر است:

فرمول INDEX(List1;MATCH(0;COUNTIF($E$1:E1;List1); 0) همه عناصر منحصر به فرد را از لیست اول انتخاب می کند. به محض تمام شدن آنها، شروع به دادن خطای #N/A می کند:
فرمول INDEX(List2;MATCH(0;COUNTIF($E$1:E1;List2); 0)) عناصر منحصر به فرد را از لیست دوم به همان روش استخراج می کند.
دو تابع IFERROR که در یکدیگر قرار گرفته اند ابتدا خروجی های منحصر به فرد را از لیست-1 و سپس از لیست-2 یکی پس از دیگری پیاده سازی می کنند.

توجه داشته باشید که این یک فرمول آرایه است، یعنی بعد از تایپ باید در سلولی وارد شود که معمولی نیست. وارد، اما با یک میانبر صفحه کلید کلیدهای Ctrl+تغییر+وارد و سپس به سلول های فرزند با حاشیه کپی کنید (کشش کنید).

در نسخه انگلیسی اکسل، این فرمول به صورت زیر است:

=IFERROR(IFERROR(INDEX(List1, MATCH(0, COUNTIF($E$1:E1, List1), 0)), INDEX(List2, MATCH(0, COUNTIF($E$1:E1, List2, 0)) ) "")

نقطه ضعف این رویکرد این است که فرمول های آرایه به طور قابل توجهی کار با فایل را کند می کنند اگر جداول منبع دارای تعداد زیادی (چند صد یا بیشتر) عناصر باشند.

روش 3. Power Query

اگر فهرست های منبع شما دارای تعداد زیادی عنصر، به عنوان مثال، چند صد یا هزاران است، به جای فرمول آرایه آهسته، بهتر است از یک رویکرد اساساً متفاوت استفاده کنید، یعنی ابزارهای افزودنی Power Query. این افزونه به طور پیش فرض در اکسل 2016 تعبیه شده است. اگر اکسل 2010 یا 2013 دارید، می توانید آن را به صورت جداگانه (رایگان) دانلود و نصب کنید.

الگوریتم اقدامات به شرح زیر است:

یک برگه جداگانه از افزونه نصب شده باز کنید پرس و جو برق (اگر اکسل 2010-2013 دارید) یا فقط به تب بروید داده ها (اگر اکسل 2016 دارید).
اولین لیست را انتخاب کنید و دکمه را فشار دهید از جدول/محدوده (از محدوده/جدول). هنگامی که در مورد ایجاد یک "جدول هوشمند" از لیست ما سؤال شد، ما موافقت می کنیم:
پنجره ویرایشگر پرس و جو باز می شود، جایی که می توانید داده های بارگذاری شده و نام پرس و جو را ببینید جدول 1 (در صورت تمایل می توانید آن را به خودتان تغییر دهید).
روی هدر جدول (word فهرست 1) و نام آن را به هر دیگری تغییر دهید (مثلا مردم). اینکه دقیقا چه نام گذاری شود مهم نیست، اما نام اختراع شده را باید به خاطر بسپارید، زیرا. بعداً هنگام وارد کردن جدول دوم باید دوباره استفاده شود. ادغام دو جدول در آینده تنها در صورتی کار خواهد کرد که عنوان ستون آنها مطابقت داشته باشد.
لیست کشویی را در گوشه سمت چپ بالا باز کنید ببندید و دانلود کنید و انتخاب کنید بستن و بارگیری در… (بستن و بارگذاری به…):
در کادر محاوره ای بعدی (ممکن است کمی متفاوت به نظر برسد - نگران نباشید)، را انتخاب کنید فقط یک اتصال ایجاد کنید (فقط ایجاد اتصال):
کل روش (نقاط 2-6) را برای لیست دوم تکرار می کنیم. هنگام تغییر نام یک عنوان ستون، مهم است که از همان نام (People) مانند درخواست قبلی استفاده کنید.
در پنجره اکسل در تب داده ها یا روی برگه پرس و جو برق را انتخاب کنید دریافت داده – ترکیب درخواست ها – افزودن (دریافت داده - جستارهای ادغام - پیوست):
در کادر محاوره ای که ظاهر می شود، درخواست های ما را از لیست های کشویی انتخاب کنید:
در نتیجه، یک پرس و جو جدید دریافت می کنیم که در آن دو لیست زیر یکدیگر متصل می شوند. باقی مانده است که موارد تکراری را با دکمه حذف کنید سطرها را حذف کنید - موارد تکراری را حذف کنید (حذف ردیف - حذف موارد تکراری):
پرس و جو تمام شده را می توان در سمت راست پانل گزینه ها تغییر نام داد و به آن یک نام منطقی داد (در واقع این نام جدول نتیجه خواهد بود) و همه چیز را می توان با دستور در برگه بارگذاری کرد. ببندید و دانلود کنید (بستن و بارگیری):