Pinagsasama ang dalawang listahan nang walang mga duplicate

Isang klasikong sitwasyon: mayroon kang dalawang listahan na kailangang pagsamahin sa isa. Bukod dito, sa mga paunang listahan ay maaaring magkaroon ng parehong mga natatanging elemento at tumutugma sa mga (kapwa sa pagitan ng mga listahan at sa loob), ngunit sa output kailangan mong makakuha ng isang listahan nang walang mga duplicate (mga pag-uulit):

Tradisyonal na tingnan natin ang ilang mga paraan upang malutas ang isang karaniwang problema - mula sa primitive na "sa noo" hanggang sa mas kumplikado, ngunit eleganteng.

Paraan 1: Alisin ang Mga Duplicate

Maaari mong lutasin ang problema sa pinakasimpleng paraan - manu-manong kopyahin ang mga elemento ng parehong listahan sa isa at pagkatapos ay ilapat ang tool sa resultang set. Alisin ang Mga Doble mula sa tab data (Data — Alisin ang mga Duplicate):

Siyempre, hindi gagana ang pamamaraang ito kung madalas na nagbabago ang data sa mga listahan ng pinagmulan – kakailanganin mong ulitin ang buong pamamaraan pagkatapos ng bawat pagbabagong muli. 

Paraan 1a. pivot table

Ang pamamaraang ito ay, sa katunayan, isang lohikal na pagpapatuloy ng nauna. Kung ang mga listahan ay hindi masyadong malaki at ang maximum na bilang ng mga elemento sa mga ito ay alam nang maaga (halimbawa, hindi hihigit sa 10), pagkatapos ay maaari mong pagsamahin ang dalawang talahanayan sa isa sa pamamagitan ng direktang mga link, magdagdag ng isang haligi na may mga nasa kanan at bumuo ng isang talahanayan ng buod batay sa resultang talahanayan:

Tulad ng alam mo, binabalewala ng pivot table ang mga pag-uulit, kaya sa output makakakuha tayo ng isang pinagsamang listahan nang walang mga duplicate. Ang auxiliary column na may 1 ay kailangan lang dahil ang Excel ay makakagawa ng summary table na naglalaman ng hindi bababa sa dalawang column.

Kapag binago ang mga orihinal na listahan, mapupunta ang bagong data sa pinagsamang talahanayan sa pamamagitan ng mga direktang link, ngunit kailangang manu-manong i-update ang pivot table (i-right-click – I-update at I-save). Kung hindi mo kailangan ang muling pagkalkula sa mabilisang, pagkatapos ay mas mahusay na gumamit ng iba pang mga pagpipilian.

Paraan 2: Array Formula

Maaari mong lutasin ang problema gamit ang mga formula. Sa kasong ito, ang muling pagkalkula at pag-update ng mga resulta ay magaganap nang awtomatiko at kaagad, kaagad pagkatapos ng mga pagbabago sa mga orihinal na listahan. Para sa kaginhawahan at kaiklian, bigyan natin ng mga pangalan ang aming mga listahan. listahan 1 и listahan 2paggamit Pangalan ng Tagapamahala tab pormula (Mga Formula — Name Manager — Lumikha):

Pagkatapos ng pagbibigay ng pangalan, ang formula na kailangan namin ay magiging ganito:

Sa unang tingin, ito ay mukhang katakut-takot, ngunit, sa katunayan, ang lahat ay hindi nakakatakot. Hayaan akong palawakin ang formula na ito sa ilang linya gamit ang Alt+Enter key na kumbinasyon at indent na may mga puwang, tulad ng ginawa namin, halimbawa dito:

Ang lohika dito ay ang sumusunod:

• Pinipili ng formula na INDEX(List1;MATCH(0;COUNTIF($E$1:E1;List1); 0) ang lahat ng natatanging elemento mula sa unang listahan. Sa sandaling maubos ang mga ito, magsisimula itong magbigay ng #N/A error:

  

• Ang formula na INDEX(List2;MATCH(0;COUNTIF($E$1:E1;List2); 0)) ay kinukuha ang mga natatanging elemento mula sa pangalawang listahan sa parehong paraan.
• Naka-nested sa isa't isa dalawang function ng IFERROR ang nagpapatupad ng output una sa mga natatangi mula sa listahan-1, at pagkatapos ay mula sa listahan-2 nang sunud-sunod.

Tandaan na ito ay isang array formula, ibig sabihin, pagkatapos mag-type, dapat itong ilagay sa isang cell na hindi karaniwan Magpasok, ngunit may keyboard shortcut Ctrl+Ilipat+Magpasok at pagkatapos ay kopyahin (i-drag) pababa sa mga child cell na may margin.

Sa English na bersyon ng Excel, ang formula na ito ay mukhang:

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

Ang downside ng diskarteng ito ay ang array formula ay kapansin-pansing nagpapabagal sa paggana sa file kung ang source table ay may malaking (ilang daan o higit pa) na bilang ng mga elemento. 

Paraan 3. Power Query

Kung ang iyong mga listahan ng pinagmulan ay may malaking bilang ng mga elemento, halimbawa, ilang daan-daan o libo-libo, sa halip na isang mabagal na formula ng array, mas mainam na gumamit ng isang kakaibang diskarte, lalo na ang Power Query add-in na mga tool. Ang add-in na ito ay binuo sa Excel 2016 bilang default. Kung mayroon kang Excel 2010 o 2013, maaari mong i-download at i-install ito nang hiwalay (nang libre).

Ang algorithm ng mga aksyon ay ang mga sumusunod:

1. Magbukas ng hiwalay na tab ng naka-install na add-on kapangyarihan query (kung mayroon kang Excel 2010-2013) o pumunta lang sa tab data (kung mayroon kang Excel 2016).
2. Piliin ang unang listahan at pindutin ang pindutan Mula sa Table/Range (Mula sa Saklaw/Talahanayan). Kapag tinanong tungkol sa paglikha ng isang "matalinong talahanayan" mula sa aming listahan, sumasang-ayon kami:

   

3. Bubukas ang window ng editor ng query, kung saan makikita mo ang na-load na data at ang pangalan ng query Table 1 (maaari mong baguhin ito sa iyong sarili kung gusto mo).
4. I-double click ang table header (word listahan 1) at palitan ang pangalan nito sa anumang iba pa (halimbawa Mga tao). Kung ano ang eksaktong pangalan ay hindi mahalaga, ngunit ang naimbentong pangalan ay dapat tandaan, dahil. kakailanganin itong gamitin muli sa ibang pagkakataon kapag nag-import ng pangalawang talahanayan. Ang pagsasama-sama ng dalawang talahanayan sa hinaharap ay gagana lamang kung tumugma ang kanilang mga heading ng column.
5. Palawakin ang listahan ng dropdown sa kaliwang sulok sa itaas isara at i-download at pumili Isara at i-load sa… (Isara at I-load sa…):

   

6. Sa susunod na dialog box (maaaring iba ang hitsura nito – huwag maalarma), piliin Gumawa lang ng koneksyon (Gumawa lamang ng koneksyon):

   

7. Ulitin namin ang buong pamamaraan (mga puntos 2-6) para sa pangalawang listahan. Kapag pinapalitan ang pangalan ng heading ng column, mahalagang gamitin ang parehong pangalan (Mga Tao) tulad ng sa nakaraang query.
8. Sa window ng Excel sa tab data o sa tab kapangyarihan query Piliin Kumuha ng Data – Pagsamahin ang Mga Kahilingan – Magdagdag (Kumuha ng Data — Pagsamahin ang Mga Query — Idagdag):

   

9. Sa lalabas na dialog box, piliin ang aming mga kahilingan mula sa mga drop-down na listahan:

   

10. Bilang resulta, makakakuha tayo ng bagong query, kung saan ang dalawang listahan ay ikokonekta sa ilalim ng bawat isa. Ito ay nananatiling alisin ang mga duplicate gamit ang pindutan Tanggalin ang Mga Hilera – Alisin ang Mga Duplicate (Delete Rows — Delete Duplicates):

    

11. Ang natapos na query ay maaaring palitan ng pangalan sa kanang bahagi ng panel ng mga pagpipilian, na nagbibigay dito ng isang matino na pangalan (ito ang magiging pangalan ng talahanayan ng resulta sa katunayan) at lahat ay maaaring i-upload sa sheet na may utos isara at i-download (Isara at I-load):

    

Sa hinaharap, sa anumang mga pagbabago o pagdaragdag sa mga orihinal na listahan, sapat na ang pag-right-click lamang upang i-update ang talahanayan ng mga resulta.

• Paano mangolekta ng maramihang mga talahanayan mula sa iba't ibang mga file gamit ang Power Query
• Pagkuha ng Mga Natatanging Item mula sa isang Listahan
• Paano ihambing ang dalawang listahan sa isa't isa para sa mga tugma at pagkakaiba