Sefyllfa glasurol: mae gennych ddwy restr y mae angen eu huno yn un. Ar ben hynny, yn y rhestrau cychwynnol gall fod elfennau unigryw a rhai cyfatebol (rhwng y rhestrau a'r tu mewn), ond ar yr allbwn mae angen i chi gael rhestr heb ddyblygiadau (ailadroddiadau):
Yn draddodiadol, gadewch i ni edrych ar sawl ffordd o ddatrys problem mor gyffredin – o “gyntefig ar y talcen” i fwy cymhleth, ond cain.
Dull 1: Dileu Dyblygiadau
Gallwch chi ddatrys y broblem yn y ffordd symlaf - copïwch elfennau'r ddwy restr â llaw yn un ac yna cymhwyso'r offeryn i'r set sy'n deillio o hynny. Tynnwch y Dyblygion o'r tab Dyddiad (Data - Dileu Dyblygiadau):
Wrth gwrs, ni fydd y dull hwn yn gweithio os bydd y data yn y rhestrau ffynhonnell yn newid yn aml - bydd yn rhaid i chi ailadrodd y weithdrefn gyfan ar ôl pob newid eto.
Dull 1a. bwrdd colyn
Mae'r dull hwn, mewn gwirionedd, yn barhad rhesymegol o'r un blaenorol. Os nad yw'r rhestrau'n fawr iawn a bod y nifer uchaf o elfennau ynddynt yn hysbys ymlaen llaw (er enghraifft, dim mwy na 10), yna gallwch gyfuno dau dabl yn un trwy ddolenni uniongyrchol, ychwanegu colofn gyda rhai ar y dde a adeiladu tabl cryno yn seiliedig ar y tabl canlyniadol:
Fel y gwyddoch, mae'r tabl colyn yn anwybyddu ailadroddiadau, felly yn yr allbwn byddwn yn cael rhestr gyfun heb ddyblygiadau. Mae angen y golofn ategol gydag 1 yn unig oherwydd gall Excel adeiladu tablau crynhoi sy'n cynnwys o leiaf dwy golofn.
Pan fydd y rhestrau gwreiddiol yn cael eu newid, bydd y data newydd yn mynd i'r tabl cyfunol trwy ddolenni uniongyrchol, ond bydd yn rhaid diweddaru'r tabl colyn â llaw (cliciwch ar y dde - Diweddaru ac Arbed). Os nad oes angen ailgyfrifo ar y hedfan, yna mae'n well defnyddio opsiynau eraill.
Dull 2: Fformiwla Arae
Gallwch chi ddatrys y broblem gyda fformiwlâu. Yn yr achos hwn, bydd y canlyniadau'n cael eu hailgyfrifo a'u diweddaru'n awtomatig ac yn syth, yn syth ar ôl newidiadau yn y rhestrau gwreiddiol. Er hwylustod a chryno, gadewch i ni roi enwau i'n rhestrau. Rhestrwch 1 и Rhestrwch 2defnyddio Rheolwr Enw tab fformiwla (Fformiwlâu - Rheolwr Enw - Creu):
Ar ôl enwi, bydd y fformiwla sydd ei hangen arnom yn edrych fel hyn:
Ar yr olwg gyntaf, mae'n edrych yn iasol, ond, mewn gwirionedd, nid yw popeth mor frawychus. Gadewch imi ehangu'r fformiwla hon ar sawl llinell gan ddefnyddio'r cyfuniad bysell Alt + Enter a mewnoliad gyda bylchau, fel y gwnaethom ni, er enghraifft yma:
Mae'r rhesymeg yma fel a ganlyn:
- Mae'r fformiwla INDEX(Rhestr1; MATCH(0;COUNTIF($E$1:E1;List1); 0) yn dewis pob elfen unigryw o'r rhestr gyntaf. Cyn gynted ag y byddan nhw'n dod i ben, mae'n dechrau rhoi #N/A gwall:
- Mae'r fformiwla INDEX(List2;MATCH(0;COUNTIF($E$1:E1;List2); 0)) yn tynnu'r elfennau unigryw o'r ail restr yn yr un modd.
- Yn nythu yn ei gilydd mae dwy swyddogaeth IFERROR yn gweithredu'r allbwn yn gyntaf o'r rhai unigryw o'r rhestr-1, ac yna o'r rhestr-2 un ar ôl y llall.
Sylwch mai fformiwla arae yw hon, hy ar ôl teipio, rhaid ei nodi mewn cell nad yw'n gyffredin Rhowch, ond gyda llwybr byr bysellfwrdd Ctrl+Symud+Rhowch ac yna copïwch (llusgwch) i lawr i gelloedd y plentyn gydag ymyl.
Yn y fersiwn Saesneg o Excel, mae'r fformiwla hon yn edrych fel:
=IFERROR(IFERROR(INDEX(Rhestr1, MATCH(0, COUNTIF($E$1:E1, List1), 0)), INDEX(Rhestr2, MATCH(0, COUNTIF($E$1:E1, List2), 0))) ), “”)
Anfantais y dull hwn yw bod fformiwlâu arae yn amlwg yn arafu gwaith gyda'r ffeil os oes gan y tablau ffynhonnell nifer fawr (sawl cant neu fwy) o elfennau.
Dull 3. Pŵer Ymholiad
Os oes gan eich rhestrau ffynonellau nifer fawr o elfennau, er enghraifft, cannoedd neu filoedd, yna yn lle fformiwla arae araf, mae'n well defnyddio dull sylfaenol wahanol, sef yr offer ychwanegu Power Query. Mae'r ychwanegiad hwn wedi'i ymgorffori yn Excel 2016 yn ddiofyn. Os oes gennych Excel 2010 neu 2013, gallwch ei lawrlwytho a'i osod ar wahân (am ddim).
Mae'r algorithm gweithredoedd fel a ganlyn:
- Agorwch dab ar wahân o'r ychwanegyn sydd wedi'i osod Ymholiad Pwer (os oes gennych Excel 2010-2013) neu ewch i'r tab Dyddiad (os oes gennych Excel 2016).
- Dewiswch y rhestr gyntaf a gwasgwch y botwm O'r Tabl / Ystod (O'r Ystod / Tabl). Pan ofynnwyd am greu “tabl smart” o'n rhestr, rydym yn cytuno:
- Mae ffenestr golygydd yr ymholiad yn agor, lle gallwch weld y data wedi'i lwytho ac enw'r ymholiad Tabl 1 (gallwch ei newid i'ch un chi os dymunwch).
- Cliciwch ddwywaith ar bennawd y tabl (gair Rhestrwch 1) a'i ailenwi i unrhyw un arall (er enghraifft Pobl). Nid yw beth yn union i'w enwi yn bwysig, ond rhaid cofio'r enw a ddyfeisiwyd, oherwydd. bydd yn rhaid ei ddefnyddio eto yn ddiweddarach wrth fewnforio'r ail dabl. Bydd uno dau dabl yn y dyfodol ond yn gweithio os yw penawdau eu colofnau yn cyfateb.
- Ehangwch y gwymplen yn y gornel chwith uchaf cau a llwytho i lawr a dewis Cau a llwytho i mewn… (Yn agos&lwyth i…):
- Yn y blwch deialog nesaf (efallai y bydd yn edrych ychydig yn wahanol - peidiwch â dychryn), dewiswch Dim ond creu cysylltiad (Dim ond creu cysylltiad):
- Rydym yn ailadrodd y weithdrefn gyfan (pwyntiau 2-6) ar gyfer yr ail restr. Wrth ailenwi pennawd colofn, mae'n bwysig defnyddio'r un enw (Pobl) ag yn yr ymholiad blaenorol.
- Yn y ffenestr Excel ar y tab Dyddiad neu ar y tab Ymholiad Pwer Dewiswch Cael Data - Cyfuno Ceisiadau - Ychwanegu (Cael Data - Ymholiadau Cyfuno - Atodi):
- Yn y blwch deialog sy'n ymddangos, dewiswch ein ceisiadau o'r cwymplenni:
- O ganlyniad, byddwn yn cael ymholiad newydd, lle bydd dwy restr yn cael eu cysylltu o dan ei gilydd. Mae'n parhau i gael gwared ar ddyblygiadau gyda'r botwm Dileu Rhesi - Dileu Dyblygiadau (Dileu Rhesi - Dileu Dyblygiadau):
- Gellir ailenwi'r ymholiad gorffenedig ar ochr dde'r panel opsiynau, gan roi enw call iddo (dyma fydd enw'r tabl canlyniad mewn gwirionedd) a gellir uwchlwytho popeth i'r ddalen gyda'r gorchymyn cau a llwytho i lawr (Cau a Llwyth):
Yn y dyfodol, gydag unrhyw newidiadau neu ychwanegiadau i'r rhestrau gwreiddiol, bydd yn ddigon clicio ar y dde i ddiweddaru'r tabl canlyniadau.
- Sut i gasglu tablau lluosog o wahanol ffeiliau gan ddefnyddio Power Query
- Tynnu Eitemau Unigryw o Restr
- Sut i gymharu dwy restr â'i gilydd ar gyfer paru a gwahaniaethau