Sameina tvo lista án afrita

Klassísk staða: þú ert með tvo lista sem þarf að sameina í einn. Þar að auki, í upphafslistunum geta verið bæði einstakir þættir og samsvörun (bæði á milli listanna og inni), en við úttakið þarftu að fá lista án afrita (endurtekningar):

Við skulum venjulega skoða nokkrar leiðir til að leysa slíkt algengt vandamál - allt frá frumstæðu „á enninu“ til flóknari, en glæsilegra.

Aðferð 1: Fjarlægðu afrit

Þú getur leyst vandamálið á einfaldasta hátt - afritaðu handvirkt þætti beggja lista í einn og notaðu síðan tólið á settið sem myndast. Fjarlægðu afrit af flipanum Gögn (Gögn - Fjarlægðu afrit):

Auðvitað mun þessi aðferð ekki virka ef gögnin í heimildalistunum breytast oft - þú verður að endurtaka alla ferlið eftir hverja breytingu aftur. 

Aðferð 1a. snúningstöflu

Þessi aðferð er í raun rökrétt framhald af þeirri fyrri. Ef listarnir eru ekki mjög stórir og hámarksfjöldi þátta í þeim er þekktur fyrirfram (til dæmis ekki fleiri en 10), þá er hægt að sameina tvær töflur í eina með beinum tenglum, bæta við dálki með þeim til hægri og búa til yfirlitstöflu sem byggir á töflunni sem myndast:

Eins og þú veist hunsar snúningstaflan endurtekningar, þannig að við úttakið fáum við sameinaðan lista án afrita. Hjálpardálkurinn með 1 er aðeins nauðsynlegur vegna þess að Excel getur byggt yfirlitstöflur sem innihalda að minnsta kosti tvo dálka.

Þegar upprunalegu listunum er breytt munu nýju gögnin fara í sameinuðu töfluna með beinum hlekkjum, en snúningstöfluna verður að uppfæra handvirkt (hægrismelltu - Uppfærðu og vistaðu). Ef þú þarft ekki endurútreikning á flugu, þá er betra að nota aðra valkosti.

Aðferð 2: Fylkisformúla

Þú getur leyst vandamálið með formúlum. Í þessu tilviki mun endurútreikningur og uppfærsla niðurstaðna eiga sér stað sjálfkrafa og samstundis, strax eftir breytingar á upprunalegu listunum. Til þæginda og styttingar skulum við gefa listunum okkar nöfn. Listi 1 и Listi 2með Nafnastjóri flipi uppskrift (Formúlur - Nafnastjóri - Búa til):

Eftir nafngift mun formúlan sem við þurfum líta svona út:

Við fyrstu sýn lítur það út fyrir að vera hrollvekjandi, en í raun er allt ekki svo skelfilegt. Leyfðu mér að stækka þessa formúlu á nokkrum línum með Alt+Enter lyklasamsetningu og inndráttur með bilum, eins og við gerðum, til dæmis hér:

Rökfræðin hér er eftirfarandi:

• Formúlan INDEX(List1;MATCH(0;COUNTIF($E$1:E1;List1); 0) velur alla einstaka þætti af fyrsta listanum. Um leið og þeir klárast byrjar hún að gefa #N/A villu:

  

• Formúlan INDEX(List2;MATCH(0;COUNTIF($E$1:E1;List2); 0)) dregur út einstaka þætti úr öðrum listanum á sama hátt.
• Tvær IFERROR aðgerðir, sem hreiður eru inn í hvor aðra, útfæra úttakið fyrst af þeim einstöku úr lista-1, og síðan frá lista-2 hver á eftir öðrum.

Athugið að þetta er fylkisformúla, þ.e. eftir innslátt verður að slá hana inn í hólf sem er ekki venjulegt Sláðu inn, en með flýtilykla Ctrl+Shift+Sláðu inn og afritaðu síðan (dragaðu) niður í barnafrumur með spássíu.

Í ensku útgáfunni af Excel lítur þessi formúla svona út:

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

Gallinn við þessa nálgun er að fylkisformúlur hægja verulega á vinnu með skránni ef frumtöflurnar eru með stóran (nokkur hundruð eða fleiri) fjölda þátta. 

Aðferð 3. Power Query

Ef heimildalistar þínir hafa mikinn fjölda þátta, til dæmis nokkur hundruð eða þúsundir, þá er betra að nota í grundvallaratriðum aðra nálgun, í stað þess að vera hægfara fylkisformúlu, þ.e. Power Query viðbótarverkfærin. Þessi viðbót er sjálfgefið innbyggð í Excel 2016. Ef þú ert með Excel 2010 eða 2013 geturðu hlaðið því niður og sett upp sérstaklega (ókeypis).

Reiknirit aðgerða er sem hér segir:

1. Opnaðu sérstakan flipa af uppsettu viðbótinni Orkufyrirspurn (ef þú ert með Excel 2010-2013) eða farðu bara í flipann Gögn (ef þú ert með Excel 2016).
2. Veldu fyrsta listann og ýttu á hnappinn Frá borði/sviði (Frá svið/borði). Þegar spurt er um að búa til „snjallborð“ af listanum okkar erum við sammála:

   

3. Fyrirspurnarritarglugginn opnast þar sem þú getur séð hlaðin gögn og nafn fyrirspurnarinnar Tafla 1 (þú getur breytt því í þitt eigið ef þú vilt).
4. Tvísmelltu á töfluhausinn (word Listi 1) og endurnefna það í annað (til dæmis Fólk). Hvað nákvæmlega á að nefna er ekki mikilvægt, en finna verður nafnið verður að muna, vegna þess. það verður að nota það aftur síðar við innflutning á seinni töflunni. Að sameina tvær töflur í framtíðinni mun aðeins virka ef dálkafyrirsagnir þeirra passa saman.
5. Stækkaðu fellilistann í efra vinstra horninu loka og hlaða niður Og veldu Lokaðu og hlaðið inn… (Loka&hlaða til...):

   

6. Í næsta valmynd (það gæti litið aðeins öðruvísi út – ekki vera brugðið), veldu Búðu bara til tengingu (Búa bara til tengingu):

   

7. Við endurtökum alla málsmeðferðina (liður 2-6) fyrir seinni listann. Þegar þú endurnefnir dálkafyrirsögn er mikilvægt að nota sama nafn (People) og í fyrri fyrirspurn.
8. Í Excel glugganum á flipanum Gögn eða á flipanum Orkufyrirspurn Veldu Fá gögn - Sameina beiðnir - Bæta við (Fá gögn - sameina fyrirspurnir - bæta við):

   

9. Í glugganum sem birtist skaltu velja beiðnir okkar úr fellilistanum:

   

10. Í kjölfarið fáum við nýja fyrirspurn, þar sem tveir listar verða tengdir undir hvorn annan. Það er eftir að fjarlægja afrit með hnappinum Eyða línum - Fjarlægðu afrit (Eyða línum - Eyða afritum):

    

11. Hægt er að endurnefna fullunna fyrirspurn hægra megin á valmöguleikaborðinu, gefa henni skynsamlegt nafn (þetta verður reyndar nafnið á niðurstöðutöflunni) og allt er hægt að hlaða upp á blaðið með skipuninni loka og hlaða niður (Loka&hlaða):

    

Í framtíðinni, með öllum breytingum eða viðbótum við upprunalegu listana, verður nóg að hægrismella til að uppfæra niðurstöðutöfluna.

• Hvernig á að safna mörgum töflum úr mismunandi skrám með Power Query
• Að draga einstaka hluti úr lista
• Hvernig á að bera tvo lista saman fyrir samsvörun og mismun