Kahden luettelon yhdistäminen ilman kaksoiskappaleita

Klassinen tilanne: sinulla on kaksi luetteloa, jotka on yhdistettävä yhdeksi. Lisäksi alkuluetteloissa voi olla sekä ainutlaatuisia elementtejä että vastaavia elementtejä (sekä luetteloiden välillä että sisällä), mutta lähdössä sinun on saatava luettelo ilman kaksoiskappaleita (toistoja):

Tarkastellaan perinteisesti useita tapoja ratkaista tällainen yleinen ongelma - primitiivisestä "otsasta" monimutkaisempaan, mutta eleganttiin.

Tapa 1: Poista kaksoiskappaleet

Voit ratkaista ongelman yksinkertaisimmalla tavalla – kopioi molempien luetteloiden elementit manuaalisesti yhdeksi ja käytä sitten työkalua tuloksena olevaan joukkoon. Poista kaksoiskappaleet välilehdeltä Päiväys (Data – poista kaksoiskappaleet):

Tämä menetelmä ei tietenkään toimi, jos lähdeluetteloiden tiedot muuttuvat usein – sinun on toistettava koko toimenpide jokaisen muutoksen jälkeen uudelleen. 

Menetelmä 1a. pivot-taulukko

Tämä menetelmä on itse asiassa looginen jatko edelliselle. Jos luettelot eivät ole kovin suuria ja niissä olevien elementtien enimmäismäärä on tiedossa etukäteen (esimerkiksi enintään 10), voit yhdistää kaksi taulukkoa yhdeksi suorilla linkeillä, lisätä sarakkeen oikealla olevilla ja rakentaa yhteenvetotaulukko tuloksena olevan taulukon perusteella:

Kuten tiedät, pivot-taulukko jättää huomioimatta toistot, joten lähdössä saamme yhdistetyn luettelon ilman kaksoiskappaleita. Apusarake, jossa on 1, tarvitaan vain, koska Excel voi rakentaa yhteenvetotaulukoita, joissa on vähintään kaksi saraketta.

Kun alkuperäisiä listoja muutetaan, uudet tiedot menevät yhdistettyyn taulukkoon suorien linkkien kautta, mutta pivot-taulukko on päivitettävä manuaalisesti (klikkaa hiiren oikeaa painiketta - Päivitä & Tallenna). Jos et tarvitse uudelleenlaskentaa lennossa, on parempi käyttää muita vaihtoehtoja.

Menetelmä 2: Array Formula

Voit ratkaista ongelman kaavoilla. Tässä tapauksessa tulosten uudelleenlaskenta ja päivitys tapahtuu automaattisesti ja välittömästi, heti alkuperäisten luetteloiden muutosten jälkeen. Mukavuuden ja lyhyyden vuoksi annetaan luetteloillemme nimet. List 1 и List 2käyttämällä Name Manager kieleke kaava (Kaavat — Name Manager — Luo):

Nimeämisen jälkeen tarvitsemamme kaava näyttää tältä:

Ensi silmäyksellä se näyttää kammottavalta, mutta itse asiassa kaikki ei ole niin pelottavaa. Haluan laajentaa tätä kaavaa useille riveille käyttämällä Alt+Enter-näppäinyhdistelmää ja sisennystä välilyönneillä, kuten teimme esimerkiksi täällä:

Logiikka tässä on seuraava:

• Kaava INDEX(Lista1;MATCH(0;COUNTIF($E$1:E1;List1); 0) valitsee kaikki yksilölliset elementit ensimmäisestä luettelosta. Heti kun ne loppuvat, se alkaa antaa #N/A-virheen:

  

• Kaava INDEKSI(Lista2;MATCH(0;COUNTIF($E$1:E1;List2); 0)) poimii yksilölliset elementit toisesta luettelosta samalla tavalla.
• Kaksi toistensa sisäkkäistä IFERROR-funktiota toteuttavat tulostuksen ensin listan 1 yksilöllisistä ja sitten listasta 2 peräkkäin.

Huomaa, että tämä on taulukkokaava, eli kirjoittamisen jälkeen se on syötettävä soluun, joka ei ole tavallinen enter, mutta pikanäppäimellä Ctrl+siirtyminen+enter ja kopioi (vetämällä) alas marginaalilla oleviin soluihin.

Excelin englanninkielisessä versiossa tämä kaava näyttää tältä:

=IFERROR(IFERROR(INDEKSI(Lista1, MATCH(0, COUNTIF($E$1:E1, List1), 0)), INDEKSI(Lista2, VASTAA(0, COUNTIF($E$1:E1, Lista2), 0)) ), ") 

Tämän lähestymistavan haittapuolena on se, että taulukkokaavat hidastavat huomattavasti työtä tiedoston kanssa, jos lähdetaulukoissa on suuri (useita satoja tai enemmän) elementtejä. 

Tapa 3. Power Query

Jos lähdeluetteloissasi on suuri määrä elementtejä, esimerkiksi useita satoja tai tuhansia, hitaan taulukkokaavan sijaan on parempi käyttää täysin erilaista lähestymistapaa, nimittäin Power Query -aputyökaluja. Tämä apuohjelma on oletusarvoisesti sisäänrakennettu Excel 2016:een. Jos sinulla on Excel 2010 tai 2013, voit ladata ja asentaa sen erikseen (ilmaiseksi).

Toimintojen algoritmi on seuraava:

1. Avaa asennetun lisäosan erillinen välilehti Tehokysely (jos sinulla on Excel 2010–2013) tai siirry välilehteen Päiväys (jos sinulla on Excel 2016).
2. Valitse ensimmäinen luettelo ja paina -painiketta Taulukosta/alueesta (alueelta/taulukosta). Kun kysytään "älykkään taulukon" luomisesta luettelostamme, olemme samaa mieltä:

   

3. Kyselyeditori-ikkuna avautuu, jossa näet ladatut tiedot ja kyselyn nimen Taulukko 1 (voit vaihtaa sen omaksi, jos haluat).
4. Kaksoisnapsauta taulukon otsikkoa (sana List 1) ja nimeä se uudelleen joksikin muuksi (esim Ihmiset). Mitä tarkalleen nimetään, ei ole tärkeää, mutta keksitty nimi on muistettava, koska. sitä on käytettävä uudelleen myöhemmin, kun tuodaan toista taulukkoa. Kahden taulukon yhdistäminen tulevaisuudessa toimii vain, jos niiden sarakeotsikot täsmäävät.
5. Laajenna vasemmassa yläkulmassa olevaa avattavaa luetteloa sulje ja lataa Ja valitse Sulje ja lataa… (Sulje&Lataa…):

   

6. Valitse seuraavassa valintaikkunassa (se voi näyttää hieman erilaiselta – älä huolestu). Luo vain yhteys (Luo vain yhteys):

   

7. Toistamme koko menettelyn (kohdat 2-6) toiselle listalle. Sarakeotsikon uudelleennimeämisessä on tärkeää käyttää samaa nimeä (Ihmiset) kuin edellisessä kyselyssä.
8. Välilehden Excel-ikkunassa Päiväys tai välilehdellä Tehokysely Valita Hae tiedot – Yhdistä pyynnöt – Lisää (Hae tiedot – Yhdistä kyselyt – Liitä):

   

9. Valitse avautuvassa valintaikkunassa pyyntömme avattavista luetteloista:

   

10. Tuloksena saamme uuden kyselyn, jossa kaksi listaa yhdistetään toistensa alle. Jäljelle jää kaksoiskappaleiden poistaminen painikkeella Poista rivit - Poista kaksoiskappaleet (Poista rivit - Poista kaksoiskappaleet):

    

11. Valmis kysely voidaan nimetä uudelleen asetuspaneelin oikealla puolella antamalla sille järkevä nimi (tämä on itse asiassa tulostaulukon nimi) ja kaikki voidaan ladata arkille komennolla sulje ja lataa (Sulje&Lataa):

    

Jatkossa, jos alkuperäisiin luetteloihin tehdään muutoksia tai lisäyksiä, riittää, kun napsautat hiiren kakkospainikkeella tulostaulukon päivittämistä.

• Kuinka kerätä useita taulukoita eri tiedostoista Power Queryn avulla
• Ainutlaatuisten kohteiden poimiminen luettelosta
• Kuinka vertailla kahta luetteloa keskenään vastaavuuksien ja erojen suhteen