Efnisyfirlit
Við erum með tvær töflur (til dæmis gamla og nýja útgáfuna af verðskránni), sem við þurfum að bera saman og finna fljótt muninn:
Það er strax ljóst að eitthvað hefur verið bætt við nýja verðskrána (döðlur, hvítlaukur ...), eitthvað hefur horfið (brómber, hindber ...), verð hefur breyst fyrir suma vöru (fíkjur, melónur ...). Þú þarft að finna og birta allar þessar breytingar fljótt.
Fyrir hvaða verkefni sem er í Excel eru næstum alltaf fleiri en ein lausn (venjulega 4-5). Fyrir vandamál okkar er hægt að nota margar mismunandi aðferðir:
- virka VPR (SKRÁNING) — leitaðu að vöruheitum úr nýju verðskránni í þeim gamla og sýndu gamla verðið við hliðina á því nýja og taktu svo mismuninn
- sameina tvo lista í einn og búa síðan til snúningstöflu út frá því þar sem munurinn verður greinilega sýnilegur
- notaðu Power Query viðbótina fyrir Excel
Við skulum taka þær allar í röð.
Aðferð 1. Samanburður á töflum við VLOOKUP fallið
Ef þú ert algjörlega ókunnugur þessum frábæra eiginleika, skoðaðu þá fyrst hér og lestu eða horfðu á kennslumyndband um það - sparaðu þér nokkur ár af lífi.
Venjulega er þessi aðgerð notuð til að draga gögn frá einni töflu til annarrar með því að passa við einhverja algenga færibreytu. Í þessu tilviki munum við nota það til að ýta gömlu verðinum inn í nýja verðið:
Þessar vörur, sem #N/A villan reyndist gegn, eru ekki á gamla listanum, þ.e. var bætt við. Verðbreytingar eru líka vel sýnilegar.
Kostir þessi aðferð: einföld og skýr, "klassísk tegund", eins og sagt er. Virkar í hvaða útgáfu af Excel sem er.
Gallar er þar líka. Til að leita að vörum sem bætt er við nýja verðlistann verður þú að gera sömu aðferð í gagnstæða átt, þ.e. draga upp nýtt verð upp í gamla verðið með hjálp VLOOKUP. Ef stærðir á töflunum breytast á morgun, þá þarf að laga formúlurnar. Jæja, og á mjög stórum borðum (> 100 þúsund raðir), mun öll þessi hamingja hægja á sér.
Aðferð 2: Að bera saman töflur með því að nota pivot
Við skulum afrita töflurnar okkar hverja undir aðra, bæta við dálki með nafni verðskrárinnar, svo að þú getir síðar skilið af hvaða lista hvaða röð:
Nú, byggt á búinu töflunni, munum við búa til samantekt í gegnum Setja inn - PivotTable (Setja inn - snúningstafla). Við skulum kasta akri vara að svæði lína, reit Verð að dálkasvæði og reit ЦEna inn á sviðið:
Eins og þú sérð mun snúningstaflan sjálfkrafa búa til almennan lista yfir allar vörur úr gamla og nýja verðlistanum (engar endurtekningar!) og raða vörunum í stafrófsröð. Þú getur greinilega séð þær vörur sem bættust við (þær eru ekki með gamla verðið), vörurnar sem voru fjarlægðar (þær eru ekki með nýja verðið) og verðbreytingar, ef einhverjar eru.
Stórar heildartölur í slíkri töflu eru ekki skynsamlegar og þær geta verið óvirkar á flipanum Smiður - Stórar heildartölur - Slökkva fyrir línur og dálka (Hönnun - Grand Totals).
Ef verð breytast (en ekki vörumagnið!), þá er nóg að uppfæra yfirlitið sem búið var til með því að hægrismella á það - Uppfæra.
Kostir: Þessi nálgun er stærðargráðu hraðar með stórum töflum en VLOOKUP.
Gallar: þú þarft að afrita gögnin handvirkt undir hvert annað og bæta við dálki með nafni verðlistans. Ef stærðir borðanna breytast, þá þarf að gera allt upp á nýtt.
Aðferð 3: Samanburður á töflum við Power Query
Power Query er ókeypis viðbót fyrir Microsoft Excel sem gerir þér kleift að hlaða gögnum inn í Excel frá nánast hvaða uppruna sem er og síðan umbreyta þessum gögnum á þann hátt sem þú vilt. Í Excel 2016 er þessi viðbót þegar innbyggð sjálfgefið á flipanum Gögn (Gögn), og fyrir Excel 2010-2013 þarftu að hlaða því niður sérstaklega af Microsoft vefsíðunni og setja það upp - fáðu nýjan flipa Orkufyrirspurn.
Áður en verðlistum okkar er hlaðið inn í Power Query verður fyrst að breyta þeim í snjalltöflur. Til að gera þetta skaltu velja svið með gögnum og ýta á samsetninguna á lyklaborðinu Ctrl+T eða veldu flipann á borðinu Heim - Snið sem töflu (Heima - Snið sem töflu). Hægt er að leiðrétta nöfn stofnaðra taflna á flipanum Framkvæmdaaðili (Ég mun yfirgefa staðalinn Tafla 1 и Tafla 2, sem fást sjálfgefið).
Hladdu gamla verðinu í Power Query með því að nota hnappinn Frá borði/sviði (Úr töflu/sviði) af flipanum Gögn (Dagsetning) eða af flipanum Orkufyrirspurn (fer eftir útgáfu af Excel). Eftir hleðslu munum við fara aftur í Excel frá Power Query með skipuninni Lokaðu og hlaða - Lokaðu og hlaðaðu inn... (Loka og hlaða — Loka og hlaða til...):
… og í glugganum sem birtist skaltu velja Búðu bara til tengingu (Aðeins tenging).
Endurtaktu það sama með nýju verðskránni.
Nú skulum við búa til þriðju fyrirspurnina sem mun sameina og bera saman gögnin frá fyrri tveimur. Til að gera þetta skaltu velja í Excel á flipanum Gögn – Fáðu gögn – Sameina beiðnir – Sameina (Gögn - Fá gögn - Sameina fyrirspurnir - Sameina) eða ýttu á hnappinn Sameina (Sameina) flipi Orkufyrirspurn.
Í samtengingarglugganum, veldu töflurnar okkar í fellilistanum, veldu dálkana með nöfnum vörunnar í þeim og neðst stilltu sameiningaraðferðina – Heill ytri (Fullt ytra):
Eftir að smella á OK tafla með þremur dálkum ætti að birtast, þar sem í þriðja dálknum þarftu að stækka innihald hreiðra taflna með því að nota tvöfalda örina í hausnum:
Fyrir vikið fáum við sameiningu gagna úr báðum töflunum:
Það er auðvitað betra að endurnefna dálknöfnin í hausnum með því að tvísmella á skiljanlegri:
Og nú það áhugaverðasta. Farðu í flipa Bæta við dálki (Bæta við dálki) og smelltu á hnappinn Skilyrt dálkur (Skilyrt dálkur). Og síðan í glugganum sem opnast, sláðu inn nokkur prófunarskilyrði með samsvarandi úttaksgildum:
Það á eftir að smella á OK og hladdu upp skýrslunni sem myndast í Excel með því að nota sama hnapp loka og hlaða niður (Loka og hlaða) flipi Heim (Heim):
Fegurð.
Þar að auki, ef einhverjar breytingar verða á verðlistunum í framtíðinni (línum er bætt við eða eytt, verð breytast osfrv.), þá mun það vera nóg að uppfæra beiðnir okkar með flýtilykla. Ctrl+Alt+F5 eða með hnappi Endurnýjaðu allt (Endurnýja allt) flipi Gögn (Dagsetning).
Kostir: Kannski fallegasta og þægilegasta leiðin af öllum. Virkar vel með stórum borðum. Krefst ekki handvirkra breytinga þegar stærð töflur er breytt.
Gallar: Krefst þess að Power Query viðbótin (í Excel 2010-2013) eða Excel 2016 sé uppsett. Ekki má breyta dálkinöfnunum í upprunagögnunum, annars fáum við villuna „Dálkur svo og slíkur fannst ekki!“ þegar reynt er að uppfæra fyrirspurnina.
- Hvernig á að safna gögnum úr öllum Excel skrám í tiltekinni möppu með Power Query
- Hvernig á að finna samsvörun milli tveggja lista í Excel
- Sameina tvo lista án afrita