Ikki ro'yxatni dublikatsiz birlashtirish

Klassik vaziyat: sizda ikkita ro'yxat bor, ularni birlashtirish kerak. Bundan tashqari, dastlabki ro'yxatlarda ham noyob elementlar, ham mos keladiganlar (ro'yxatlar orasida ham, ichkarida ham) bo'lishi mumkin, ammo chiqishda siz takroriy (takrorlashlarsiz) ro'yxatni olishingiz kerak:

Keling, an'anaviy ravishda bunday keng tarqalgan muammoni hal qilishning bir nechta usullarini ko'rib chiqaylik - ibtidoiy "peshonada" dan murakkabroq, ammo oqlangangacha.

1-usul: dublikatlarni olib tashlang

Muammoni eng oddiy tarzda hal qilishingiz mumkin - ikkala ro'yxatning elementlarini qo'lda bittaga ko'chiring va keyin vositani natijada olingan to'plamga qo'llang. Dublikatlarni olib tashlang tabdan ma'lumotlar (Ma'lumotlar - dublikatlarni olib tashlash):

Albatta, agar manbalar ro'yxatidagi ma'lumotlar tez-tez o'zgarib tursa, bu usul ishlamaydi - har bir o'zgarishdan keyin yana butun protsedurani takrorlashingiz kerak bo'ladi. 

1a-usul. aylanish jadvali

Bu usul, aslida, avvalgisining mantiqiy davomidir. Agar ro'yxatlar unchalik katta bo'lmasa va ulardagi elementlarning maksimal soni oldindan ma'lum bo'lsa (masalan, 10 dan ko'p bo'lmasa), unda siz ikkita jadvalni to'g'ridan-to'g'ri havolalar orqali birlashtirishingiz, o'ngdagilar bilan ustun qo'shishingiz va Olingan jadval asosida xulosa jadvalini tuzing:

Ma'lumki, pivot jadvali takrorlanishlarni e'tiborsiz qoldiradi, shuning uchun chiqishda biz dublikatsiz birlashtirilgan ro'yxatni olamiz. 1 dan iborat yordamchi ustun faqat Excel kamida ikkita ustundan iborat jamlama jadvallarini yaratishi mumkinligi uchun kerak.

Dastlabki ro'yxatlar o'zgartirilganda, yangi ma'lumotlar to'g'ridan-to'g'ri havolalar orqali birlashtirilgan jadvalga o'tadi, lekin pivot jadvalni qo'lda yangilash kerak bo'ladi (o'ng tugmasini bosing - - Yangilash va saqlash). Agar tezda qayta hisoblash kerak bo'lmasa, boshqa variantlardan foydalanish yaxshiroqdir.

2-usul: massiv formulasi

Muammoni formulalar yordamida hal qilishingiz mumkin. Bunday holda, natijalarni qayta hisoblash va yangilash avtomatik ravishda va dastlabki ro'yxatlardagi o'zgarishlardan so'ng darhol amalga oshiriladi. Qulaylik va qisqalik uchun keling, ro'yxatlarimizga nom beraylik. 1 ro'yxat и 2 ro'yxatfoydalanish Ism menejeri tab formula (Formulalar - Ism menejeri - Yaratish):

Nom bergandan so'ng, bizga kerak bo'lgan formula quyidagicha ko'rinadi:

Bir qarashda, bu dahshatli ko'rinadi, lekin aslida hamma narsa unchalik qo'rqinchli emas. Ushbu formulani Alt+Enter tugmalar birikmasidan foydalanib bir necha qatorga kengaytirib, bo‘shliqlar bilan cheklab qo‘yishga ruxsat bering, masalan, bu yerda:

Bu erda mantiq quyidagicha:

• INDEX(List1;MATCH(0;COUNTIF($E$1:E1;List1); 0) formulasi birinchi roʻyxatdagi barcha noyob elementlarni tanlaydi. Ular tugashi bilan #N/A xatosi bera boshlaydi:

  

• INDEX(List2;MATCH(0;COUNTIF($E$1:E1;List2); 0)) formulasi ikkinchi roʻyxatdagi noyob elementlarni xuddi shu tarzda ajratib oladi.
• Bir-biriga o'rnatilgan ikkita IFERROR funksiyasi birinchi navbatda 1-ro'yxatdagi, so'ngra 2-ro'yxatdagi noyoblarning chiqishini birin-ketin amalga oshiradi.

E'tibor bering, bu massiv formulasi, ya'ni terilgandan so'ng uni oddiy bo'lmagan katakka kiritish kerak. kiriting, lekin klaviatura yorlig'i bilan Ctrl+smena+kiriting va undan keyin chekka bilan pastki katakchalarga nusxa ko'chiring (surang).

Excelning inglizcha versiyasida ushbu formula quyidagicha ko'rinadi:

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

Ushbu yondashuvning salbiy tomoni shundaki, agar manba jadvallari ko'p (bir necha yuz yoki undan ortiq) elementlarga ega bo'lsa, massiv formulalari fayl bilan ishlashni sezilarli darajada sekinlashtiradi. 

3-usul. Power Query

Agar manba ro'yxatlaringiz ko'p sonli elementlarga ega bo'lsa, masalan, bir necha yuzlab yoki minglab elementlar bo'lsa, unda sekin massiv formulasi o'rniga, tubdan boshqacha yondashuvdan, xususan, Power Query plagin vositalaridan foydalanish yaxshiroqdir. Ushbu plagin sukut bo'yicha Excel 2016 da o'rnatilgan. Agar sizda Excel 2010 yoki 2013 bo'lsa, uni alohida yuklab olishingiz va o'rnatishingiz mumkin (bepul).

Amallar algoritmi quyidagicha:

1. O'rnatilgan plaginning alohida yorlig'ini oching Quvvat so'rovi (agar sizda Excel 2010-2013 bo'lsa) yoki shunchaki yorlig'iga o'ting ma'lumotlar (agar sizda Excel 2016 bo'lsa).
2. Birinchi ro'yxatni tanlang va tugmani bosing Jadval/diapazondan (Diapazon/jadvaldan). Bizning ro'yxatimizdan "aqlli stol" yaratish haqida so'ralganda, biz rozi bo'lamiz:

   

3. So'rovlar muharriri oynasi ochiladi, unda siz yuklangan ma'lumotlar va so'rov nomini ko'rishingiz mumkin 1 stol (agar xohlasangiz, uni o'zingizga o'zgartirishingiz mumkin).
4. Jadval sarlavhasini ikki marta bosing (word 1 ro'yxat) va uni boshqasiga o'zgartiring (masalan odamlar). Nimani aniq nomlash muhim emas, lekin ixtiro qilingan ismni eslab qolish kerak, chunki. ikkinchi jadvalni import qilishda uni keyinroq qayta ishlatish kerak bo'ladi. Kelajakda ikkita jadvalni birlashtirish faqat ularning ustun sarlavhalari mos kelsa ishlaydi.
5. Yuqori chap burchakdagi ochiladigan ro'yxatni kengaytiring yoping va yuklab oling tanlang va tanlang Yopish va yuklash… (Yopish va yuklash…):

   

6. Keyingi dialog oynasida (bir oz boshqacha ko'rinishi mumkin - xavotirlanmang) tanlang Faqat ulanishni yarating (Faqat ulanish yaratish):

   

7. Ikkinchi ro'yxat uchun butun protsedurani takrorlaymiz (2-6 bandlar). Ustun sarlavhasini qayta nomlashda oldingi so'rovdagi kabi bir xil nomdan (Odamlar) foydalanish muhim ahamiyatga ega.
8. Yorliqdagi Excel oynasida ma'lumotlar yoki yorliqda Quvvat so'rovi tanlang Ma'lumot olish - So'rovlarni birlashtirish - Qo'shish (Ma'lumotlarni olish - so'rovlarni birlashtirish - qo'shish):

   

9. Ko'rsatilgan dialog oynasida ochiladigan ro'yxatlardan so'rovlarimizni tanlang:

   

10. Natijada, biz yangi so'rovni olamiz, bu erda ikkita ro'yxat bir-biri bilan bog'lanadi. Tugma yordamida dublikatlarni olib tashlash qoladi Qatorlarni o'chirish - dublikatlarni o'chirish (Qatorlarni o'chirish - dublikatlarni o'chirish):

    

11. Tugallangan so'rovni variantlar panelining o'ng tomonida qayta nomlash mumkin, unga aqlli nom berish mumkin (bu aslida natijalar jadvalining nomi bo'ladi) va hamma narsani buyruq bilan varaqqa yuklash mumkin. yoping va yuklab oling (Yopish va yuklash):

    

Kelajakda asl ro'yxatlarga har qanday o'zgartirish yoki qo'shimchalar bilan natijalar jadvalini yangilash uchun sichqonchaning o'ng tugmachasini bosish kifoya qiladi.

• Power Query yordamida turli fayllardan bir nechta jadvallarni qanday yig'ish mumkin
• Ro'yxatdan noyob elementlarni ajratib olish
• Moslik va farqlar uchun ikkita ro'yxatni bir-biri bilan qanday solishtirish mumkin