Bi zerrenda bikoiztu gabe bateratzea

Egoera klasiko bat: bi zerrenda dituzu bakarrean batu behar direnak. Gainera, hasierako zerrendetan elementu bakarrak eta bat datozenak egon daitezke (bai zerrenden artean eta baita barruan ere), baina irteeran bikoiztu gabeko zerrenda bat lortu behar duzu (errepikapenak):

Ikus ditzagun tradizionalki arazo arrunt bat konpontzeko hainbat modu: "kopetean" primitibotik konplexuago, baina dotoreagora.

1. metodoa: Kendu bikoiztuak

Arazoa modu errazenean ebatzi dezakezu: eskuz kopiatu bi zerrendetako elementuak bakarrean eta aplikatu tresna emaitza multzoari. Kendu bikoiztuak fitxatik Data (Datuak — Kendu bikoiztuak):

Jakina, metodo honek ez du funtzionatuko iturri-zerrendetako datuak askotan aldatzen badira - aldaketa bakoitzaren ondoren prozedura osoa errepikatu beharko duzu berriro. 

1a metodoa. mahai pibota

Metodo hau, hain zuzen, aurrekoaren jarraipen logikoa da. Zerrendak ez badira oso handiak eta haietako elementuen gehienezko kopurua aldez aurretik ezagutzen bada (adibidez, 10 baino gehiago), orduan bi taula batean konbina ditzakezu esteka zuzenen bidez, gehitu zutabe bat eskuinean daudenak eta sortu laburpen-taula bat ondoriozko taulan oinarrituta:

Dakizuenez, taula dinamikoak errepikapenak alde batera uzten ditu, beraz irteeran bikoiztu gabeko zerrenda konbinatua lortuko dugu. 1 duen zutabe laguntzailea bakarrik behar da Excel-ek gutxienez bi zutabe dituzten laburpen-taulak eraiki ditzakeelako.

Jatorrizko zerrendak aldatzen direnean, datu berriak taula konbinatuetara joango dira zuzeneko esteken bidez, baina taula dinamikoa eskuz eguneratu beharko da (eskuineko botoiarekin klik egin - Eguneratu eta Gorde). Ez baduzu birkalkularik behar hegan, hobe da beste aukera batzuk erabiltzea.

2. metodoa: Array Formula

Arazoa formulekin ebatzi dezakezu. Kasu honetan, emaitzen birkalkulua eta eguneratzea automatikoki eta berehala egingo da, jatorrizko zerrendetan aldaketak egin eta berehala. Erosotasunerako eta laburtasunerako, eman ditzagun gure zerrendei izenak. Zerrendatu 1 и Zerrendatu 2erabiliz Izen kudeatzailea fitxa formula (Formulak — Izenaren kudeatzailea — Sortu):

Izena eman ondoren, behar dugun formula honela izango da:

Lehen begiratuan, beldurgarria dirudi, baina, egia esan, dena ez da hain beldurgarria. Utzidazu formula hau zabaltzen hainbat lerrotan Alt+Sartu tekla-konbinazioa erabiliz eta zuriuneekin koska erabiliz, guk egin genuen bezala, adibidez hemen:

Hemen logika hau da:

• INDEX(List1;MATCH(0;COUNTIF($E$1:E1;List1); 0) formulak lehen zerrendako elementu esklusibo guztiak hautatzen ditu. Agortu bezain pronto, #N/A errorea ematen hasten da:

  

• AURKIBIDEA(Lista2;MATCH(0;ZENBATUBA($E$1:E1;Lista2); 0)) modu berean ateratzen ditu bigarren zerrendako elementu bakarrak.
• Bata bestean habiaratuta IFERROR bi funtzioek lehenik eta behin zerrendako bakarren irteera inplementatzen dute, eta, ondoren, zerrenda-1tik bata bestearen atzetik.

Kontuan izan hau matrize formula bat dela, hau da, idatzi ondoren, arrunta ez den gelaxka batean sartu behar da Sartu, baina teklatuko lasterbide batekin Ctrl+Shift+Sartu eta gero kopiatu (arrastatu) behera marjina duten gelaxka umeetara.

Excel-en ingelesezko bertsioan, honelakoa da formula hau:

=ERROREA BAZAN(ERROREA(AURKIBIDEA(Zerrenda1, BATEKIN(0, ZENBATZENBIDE($E$1:E1, Zerrenda1), 0)), INDEX(2.Zerrenda, BATEKIN(0, ZENBATUZ($E$1:E1, Zerrenda2), 0)) ), "") 

Ikuspegi honen alde txarra da matrize-formulek fitxategiarekin lana nabarmen moteltzen dutela iturburu-taulek elementu kopuru handia (zenbait ehunka edo gehiago) badute. 

3. metodoa. Power Query

Zure iturburu-zerrendek elementu kopuru handia badute, adibidez, ehunka edo milaka, orduan matrize formula motel baten ordez, hobe da funtsean beste ikuspegi bat erabiltzea, hots, Power Query gehigarri tresnak. Gehigarri hau Excel 2016-n dago lehenetsita. Excel 2010 edo 2013 baduzu, bereizita deskargatu eta instala dezakezu (doan).

Ekintzen algoritmoa honakoa da:

1. Ireki instalatutako gehigarriaren aparteko fitxa Power kontsulta (Excel 2010-2013 baduzu) edo joan fitxara Data (Excel 2016 baduzu).
2. Hautatu lehen zerrenda eta sakatu botoia Taula/Barrutitik (Barrutitik/Taulatik). Gure zerrendatik "mahai adimendun" bat sortzeari buruz galdetuta, ados gaude:

   

3. Kontsulten editorearen leihoa irekitzen da, non kargatutako datuak eta kontsultaren izena ikus ditzakezun Table 1 (nahi izanez gero, zurea alda dezakezu).
4. Egin klik bikoitza taularen goiburuan (hitza Zerrendatu 1) eta beste edozein izen jarri (adibidez Pertsonak). Zehazki zer izendatu ez da garrantzitsua, baina asmatutako izena gogoratu behar da, zeren. bigarren taula inportatzerakoan berriro erabili beharko da. Etorkizunean bi taula bateratzeak haien zutabeen goiburuak bat datozenean bakarrik funtzionatuko du.
5. Zabaldu goitibeherako zerrenda goiko ezkerreko izkinan itxi eta deskargatu eta aukeratu Itxi eta kargatu… (Itxi&Kargatu...):

   

6. Hurrengo elkarrizketa-koadroan (apur bat desberdina izan daiteke, ez kezkatu), hautatu Konexio bat sortu besterik ez dago (Sortu konexioa soilik):

   

7. Prozedura osoa errepikatzen dugu (2-6 puntuak) bigarren zerrendarako. Zutabe-izenburua aldatzean, garrantzitsua da aurreko kontsultako izen bera (Pertsonak) erabiltzea.
8. Excel leihoan fitxan Data edo fitxan Power kontsulta Aukeratu Lortu datuak – Konbinatu eskaerak – Gehitu (Lortu datuak — Bateratu kontsultak — Erantsi):

   

9. Agertzen den elkarrizketa-koadroan, hautatu gure eskaerak goitibeherako zerrendetatik:

   

10. Ondorioz, kontsulta berri bat lortuko dugu, non bi zerrenda elkarren azpian konektatuko diren. Botoiarekin bikoiztuak kentzea geratzen da Ezabatu errenkadak - Kendu bikoiztuak (Ezabatu errenkadak — Ezabatu bikoiztuak):

    

11. Amaitutako kontsultari aukeren panelaren eskuineko aldean izena alda daiteke, izen sanoa emanez (hau izango da emaitza-taularen izena hain zuzen) eta dena orrira igo daiteke komandoarekin itxi eta deskargatu (Itxi&Kargatu):

    

Etorkizunean, jatorrizko zerrendetan aldaketak edo gehiketak eginez gero, nahikoa izango da eskuineko botoiarekin klik egitea emaitzen taula eguneratzeko.

• Nola bildu hainbat taula fitxategi ezberdinetatik Power Query erabiliz
• Zerrenda batetik elementu bakarrak ateratzea
• Nola alderatu bi zerrenda bata bestearekin partida eta desberdintasunetarako