Хоёр жагсаалтыг давхардалгүйгээр нэгтгэж байна

Сонгодог нөхцөл байдал: танд нэгтгэх шаардлагатай хоёр жагсаалт байна. Нэмж дурдахад, эхний жагсаалтад өвөрмөц элементүүд болон тохирох элементүүд (жагсаалтын хооронд болон дотор нь хоёулаа) байж болно, гэхдээ гаралт дээр та давхардалгүй (давталт) жагсаалтыг авах хэрэгтэй:

Ийм нийтлэг асуудлыг шийдэх хэд хэдэн арга замыг уламжлалт байдлаар авч үзье - энгийн "духан дээр" -ээс илүү төвөгтэй, гэхдээ гоёмсог.

Арга 1: Давхардсан файлуудыг устгах

Та асуудлыг хамгийн энгийн аргаар шийдэж болно - хоёр жагсаалтын элементүүдийг гараар хуулж, дараа нь үүссэн багцад хэрэглүүрийг хэрэглээрэй. Хуулбаруудыг устгах табаас Өгөгдөл (Өгөгдөл - Давхардлыг арилгах):

Мэдээжийн хэрэг, эх сурвалжийн жагсаалт дахь өгөгдөл байнга өөрчлөгддөг бол энэ арга ажиллахгүй - өөрчлөлт бүрийн дараа та бүх процедурыг давтах шаардлагатай болно. 

Арга 1a. тэнхлэгийн хүснэгт

Энэ арга нь үнэндээ өмнөхийн логик үргэлжлэл юм. Жагсаалт нь тийм ч том биш бөгөөд тэдгээрийн доторх элементүүдийн хамгийн их тоо нь урьдчилан мэдэгдэж байгаа бол (жишээлбэл, 10-аас ихгүй), та хоёр хүснэгтийг шууд холбоосоор нэг болгон нэгтгэж, баруун талд байгаа баганыг нэмж болно. Хүснэгт дээр үндэслэн хураангуй хүснэгтийг байгуулна:

Таны мэдэж байгаагаар пивот хүснэгт нь давталтыг үл тоомсорлодог тул гаралт дээр бид давхардалгүй нэгдсэн жагсаалтыг авах болно. Excel нь дор хаяж хоёр багана агуулсан хураангуй хүснэгтийг бүтээх боломжтой тул 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+Shift+оруулна уу дараа нь захын зайтай хүүхдийн нүднүүд рүү хуулж (чирнэ).

Excel-ийн англи хувилбарт энэ томъёо дараах байдалтай байна.

=БӨГӨӨЛӨГӨӨ(ИНДЕКС(Жагсаалт1, ТОХИРУУЛАХ(0, COUNTIF($E$1:E1, Жагсаалт1), 0)), INDEX(Жагсаалт2, ТАЙЛБАР(0, COUNTIF($E$1:E1, Жагсаалт2), 0)) ), "") 

Энэ аргын сул тал нь эх хүснэгтүүд нь олон тооны (хэдэн зуу ба түүнээс дээш) элементтэй бол массивын томъёо нь файлтай ажиллахыг мэдэгдэхүйц удаашруулдаг. 

Арга 3. Power Query

Хэрэв таны эх сурвалжийн жагсаалтад олон тооны элементүүд, жишээлбэл, хэдэн зуу эсвэл мянга байдаг бол удаан массивын томъёоны оронд тэс өөр арга, тухайлбал Power Query нэмэлт хэрэгслийг ашиглах нь дээр. Энэхүү нэмэлтийг анхдагчаар Excel 2016-д суулгасан болно. Хэрэв танд Excel 2010 эсвэл 2013 байгаа бол тусад нь татаж аваад суулгаж болно (үнэгүй).

Үйлдлийн алгоритм дараах байдалтай байна.

1. Суулгасан нэмэлтийн тусдаа табыг нээнэ үү Эрчим хүчний асуулга (хэрэв танд Excel 2010-2013 байгаа бол) эсвэл зүгээр л таб руу очно уу Өгөгдөл (хэрэв танд Excel 2016 байгаа бол).
2. Эхний жагсаалтыг сонгоод товчийг дарна уу Хүснэгтээс/Мужаас (Муж/Хүснэгтээс). Бидний жагсаалтаас "ухаалаг ширээ" бий болгох талаар асуухад бид санал нэг байна:

   

3. Асуулга засварлагч цонх нээгдэх бөгөөд та ачаалагдсан өгөгдөл болон асуулгын нэрийг харах боломжтой Хүснэгт 1 (хэрэв та хүсвэл үүнийг өөрийнхөөрөө өөрчилж болно).
4. Хүснэгтийн толгой дээр давхар товшино уу (word 1 жагсаалт) болон нэрийг нь өөр болгон өөрчилнө (жишээ нь Хүмүүс). Яг юу гэж нэрлэх нь чухал биш, гэхдээ зохион бүтээсэн нэрийг санаж байх ёстой, учир нь. Энэ нь дараа нь хоёр дахь хүснэгтийг импортлох үед дахин ашиглах шаардлагатай болно. Ирээдүйд хоёр хүснэгтийг нэгтгэх нь зөвхөн баганын гарчиг таарч байвал л ажиллана.
5. Зүүн дээд буланд байрлах унадаг жагсаалтыг өргөжүүлнэ үү хааж татаж аваарай Сонгох Хаах болон ачаалах... (Хаах, ачаалах...):

   

6. Дараагийн харилцах цонхонд (энэ нь арай өөр харагдаж магадгүй - санаа зовох хэрэггүй) сонгоно уу Зүгээр л холболт үүсгэ (Зөвхөн холболт үүсгэх):

   

7. Бид хоёр дахь жагсаалтын бүх процедурыг (2-6-р зүйл) давтана. Баганын гарчгийн нэрийг өөрчлөхдөө өмнөх асуулгатай ижил нэрийг (Хүмүүс) ашиглах нь чухал.
8. Таб дээрх Excel цонхонд Өгөгдөл эсвэл таб дээр Эрчим хүчний асуулга сонгоно уу Мэдээлэл авах - Хүсэлтийг нэгтгэх - Нэмэх (Өгөгдөл авах - Асуултуудыг нэгтгэх - Хавсаргах):

   

9. Гарч ирэх харилцах цонхонд унадаг жагсаалтаас бидний хүсэлтийг сонгоно уу:

   

10. Үүний үр дүнд бид хоёр жагсаалт бие биенийхээ доор холбогдсон шинэ асуулга авах болно. Товчлуурыг ашиглан хуулбарыг арилгахад л үлддэг Мөр устгах - Давхардсан хэсгийг устгах (Мөрүүдийг устгах - Давхардсан хэсгийг устгах):

    

11. Дууссан асуулгын нэрийг сонголтын самбарын баруун талд өөрчилж, түүнд эрүүл нэр өгөх боломжтой (энэ нь үнэндээ үр дүнгийн хүснэгтийн нэр байх болно) бөгөөд бүх зүйлийг тушаалаар хуудсанд байршуулж болно. хааж татаж аваарай (Хаах, ачаалах):

    

Цаашид анхны жагсаалтад ямар нэгэн өөрчлөлт, нэмэлт оруулсан тохиолдолд үр дүнгийн хүснэгтийг шинэчлэхийн тулд хулганы баруун товчийг дарахад л хангалттай.

• Power Query ашиглан өөр өөр файлуудаас олон хүснэгтийг хэрхэн цуглуулах вэ
• Жагсаалтаас өвөрмөц зүйлсийг задлах
• Хоёр жагсаалтыг хоорондоо таарч, ялгааг хэрхэн харьцуулах вэ