EXCEL TöFLUREIKNI SAMANBURðUR

Vissulega eru Excel notendur að takast á við það að bera saman tvær töflur eða listi til að greina mismun eða vantar þætti í þeim. Hver notandi klárar þetta verkefni á sinn hátt, en oft er frekar mikill tími til að leysa þetta mál, þar sem ekki eru allir aðferðir við þetta vandamál rökrétt. Á sama tíma eru nokkrir sannaðar reikniritaraðferðir sem leyfa þér að bera saman lista eða töfluuppsetningar á tiltölulega stuttan tíma með lágmarksátaki. Við skulum skoða þessar valkostir nánar.

Sjá einnig: Samanburður á tveimur skjölum í MS Word

Samanburðaraðferðir

Það eru nokkrar leiðir til að bera saman töflur í Excel en allir geta skipt í þrjá stóra hópa:

samanburður á listum sem eru á sama blaði;

samanburður á töflum staðsett á mismunandi blöðum;

samanburður á borði sviðum í mismunandi skrám.

Það er á grundvelli þessarar flokkunar að fyrst og fremst eru samanburðaraðferðir valdar og ákveðnar aðgerðir og reiknirit til að sinna verkefninu eru ákvörðuð. Til dæmis, þegar þú gerir samanburð í mismunandi bækur, þarftu samtímis að opna tvö Excel skrár.

Að auki ætti að segja að samanburðarrými sé aðeins skynsamlegt þegar þeir hafa svipaða byggingu.

Aðferð 1: einföld formúla

Auðveldasta leiðin til að bera saman gögn í tveimur borðum er að nota einfalda jafnréttisformúlu. Ef gögnin passa, þá gefur það TRUE gildi, og ef ekki þá - FALSE. Það er hægt að bera saman, bæði tölulegar upplýsingar og texta. Ókosturinn við þessa aðferð er að það er aðeins hægt að nota ef gögnin í töflunni eru pantaðar eða raðað á sama hátt, samstillt og jafnmargar línur. Við skulum sjá hvernig á að nota þessa aðferð í reynd á dæmi um tvær töflur sem settar eru á eina blað.

Svo höfum við tvær einfaldar töflur með lista yfir starfsmenn og laun þeirra. Nauðsynlegt er að bera saman lista yfir starfsmenn og greina ósamræmi á milli dálka þar sem nöfnin eru sett.

Til þess þurfum við auka dálk á blaðinu. Sláðu inn skilti þar "=". Smelltu síðan á fyrsta atriði sem á að bera saman í fyrstu listanum. Aftur settum við táknið "=" frá lyklaborðinu. Smelltu síðan á fyrsta reit dálksins, sem við borum saman, í seinni töflunni. Tjáningin er af eftirfarandi gerð:
= A2 = D2
Þó að sjálfsögðu í hverju tilfelli mun hnitin vera öðruvísi en kjarni verður áfram sú sama.
Smelltu á hnappinn Sláðu inntil að fá samanburðar niðurstöður. Eins og sjá má, þegar borið er saman fyrstu frumurnar í báðum listum, sýndu forritið vísbendingu "Sannur"sem þýðir gögn samsvörun.

Nú þurfum við að framkvæma svipaða aðgerð með eftirstandandi frumum af báðum borðum í dálkunum sem við bera saman. En þú getur einfaldlega afritað formúluna, sem mun verulega spara tíma. Þessi þáttur er sérstaklega mikilvægt þegar samanburður er á listum með fjölda lína.
Afritunin er auðveldast að framkvæma með því að nota fyllahandfangið. Við setjum bendilinn í neðra hægra horninu á reitnum, þar sem við fengum vísbendingu "Sannur". Á sama tíma ætti það að vera breytt í svört kross. Þetta er fylla merkið. Smelltu á vinstri músarhnappinn og dragðu bendilinn niður með fjölda lína í samanburðarborðið.

Eins og við sjáum, eru nú í viðbótarsúlunni allar niðurstöður gagnajafnaðar í tveimur dálkum töfluformi. Í okkar tilviki passa gögnin ekki aðeins í einni línu. Þegar miðað var saman gaf formúlan niðurstöðuna "FALSE". Fyrir allar aðrar línur, eins og þú sérð, gaf samanburðarformúlan vísbendingu "Sannur".

Að auki er hægt að reikna út fjölda misræma með því að nota sérstaka formúlu. Til að gera þetta skaltu velja þætti lagsins, þar sem það verður birt. Smelltu síðan á táknið "Setja inn virka".

Í glugganum Virkni meistarar í hópi rekstraraðila "Stærðfræði" veldu nafnið SUMPRODUCT. Smelltu á hnappinn "OK".

Aðgerðarglugginn er virkur. SUMPRODUCTHelstu verkefni þess er að reikna summa vörunnar á völdum sviðinu. En þessi aðgerð er hægt að nota í tilgangi okkar. Setningafræði hennar er frekar einfalt:
= SUMPRODUCT (array1; array2; ...)
Alls er hægt að nota heimilisföng allt að 255 fylki sem rök. En í okkar tilviki munum við aðeins nota tvö fylki, auk þess sem eitt rök.
Settu bendilinn í reitinn "Massive1" og veldu samanburða gagnasviðið í fyrsta svæði á blaðinu. Eftir það settum við merki í reitinn. "ekki jafn" () og veldu samanburðarsvið annars svæðisins. Næst skaltu hylja tjáninguna með sviga, áður en við setjum tvö stafi "-". Í okkar tilviki fáum við eftirfarandi tjáningu:
- (A2: A7D2: D7)
Smelltu á hnappinn "OK".

Rekstraraðilinn reiknar út og birtir niðurstöðuna. Eins og við sjáum, í okkar tilviki er niðurstaðan jöfn tölunni "1", það þýðir það að í samanburðarlistunum var ein misræmi fundið. Ef listarnir voru alveg eins, þá yrði niðurstaðan sú sama "0".

Á sama hátt geturðu borið saman gögn í töflum sem eru staðsettar á mismunandi blöðum. En í þessu tilfelli er æskilegt að línurnar í þeim séu númeruð. The hvíla af samanburðaraðferðinni er nánast nákvæmlega það sama og lýst er hér að framan, nema fyrir því að þegar þú gerir formúlu þarftu að skipta á milli blaða. Í okkar tilviki mun tjáningin hafa eftirfarandi form:

= B2 = Sheet2! B2

Það er, eins og við sjáum, fyrir hnit gagna, sem eru staðsettar á öðrum blöðum, frábrugðin því sem niðurstaðan er af birtingunni, er fjöldi blaða og upphrópunarmerkisins tilgreint.

Aðferð 2: Veldu hópa af frumum

Samanburður er hægt að gera með því að nota valmyndarval Með því er einnig hægt að bera saman aðeins samstillt og pantað lista. Að auki, í þessu tilfelli, skulu listarnir staðsettir við hliðina á hvoru öðru á sama blaði.

Veldu samanburðar fylki. Farðu í flipann "Heim". Næst skaltu smella á táknið "Finndu og auðkenna"sem er staðsett á borði í blokkinni af verkfærum Breyting. Listi opnast þar sem þú ættir að velja stöðu. "Val á hópi frumna ...".
Að auki er hægt að nálgast á annan hátt í viðeigandi gluggi við val á hópi frumna. Þessi valkostur mun vera sérstaklega gagnlegur fyrir þá notendur sem hafa sett upp útgáfuna af forritinu fyrr en Excel 2007, vegna þess að aðferðin í gegnum hnappinn "Finndu og auðkenna" Þessar umsóknir styðja ekki. Veldu fylki sem við viljum bera saman og ýttu á takkann F5.
Lítill umskipti gluggi er virkur. Smelltu á hnappinn "Hápunktur ..." í neðri vinstra horninu.

Eftir það, hvort tveggja af ofangreindum tveimur valkostum sem þú velur, er gluggi til að velja hópa af frumum hleypt af stokkunum. Stilltu rofann í stöðu "Veldu eftir röð". Smelltu á hnappinn "OK".

Eins og þú getur séð, eftir þetta mun ósamræmi gildi raða vera auðkenndur með mismunandi lit. Að auki, eins og hægt er að dæma af innihaldi formúulínu, mun forritið gera einn af frumunum virkan í tilgreindum ósamþykktum línum.

Aðferð 3: Skilyrt snið

Þú getur gert samanburð með því að nota skilyrt formatting aðferð. Eins og í fyrri aðferðinni skulu samanburðarröðin vera á sama Excel verkstæði og vera samstillt við hvert annað.

Fyrst af öllu veljum við hvaða borðrými við munum íhuga helstu og hverjir að leita að mismunandi. Síðast munum við gera í seinni töflunni. Því skaltu velja lista yfir starfsmenn sem eru staðsettir í henni. Flutningur á flipann "Heim", smelltu á hnappinn "Skilyrt snið"sem er staðsett á borði í blokkinni "Stíll". Í fellilistanum skaltu halda áfram "Reglustjórnun".

Reglustjórans glugginn er virkur. Við ýtum á það á hnappinn "Búðu til reglu".

Í upphafsglugganum skaltu velja stöðu "Notaðu formúlu". Á sviði "Format frumur" skrifaðu formúluna sem inniheldur heimilisföng fyrstu frumanna á sviðum samanburðarsúlna, aðskilin með tákninu "ekki jafn" (). Aðeins þessi tjáning mun hafa tákn í augnablikinu. "=". Að auki ætti alger heimilisfang á öllum dálkhnitum í þessari formúlu. Til að gera þetta skaltu velja formúluna með bendilinn og smella þrisvar sinnum á takkann F4. Eins og þú sérð birtist dollara skilti nálægt öllum dálkföngunum, sem þýðir að snúa tenglum í algera sjálfur. Fyrir okkar sérstöku tilviki mun formúlan taka eftirfarandi form:
= $ A2 $ D2
Við skrifum þetta tjáning í ofangreindum reit. Eftir það smellirðu á hnappinn "Format ...".

Virkjaður gluggi "Format frumur". Farðu í flipann "Fylltu". Hér í listanum yfir litum hættum við valið á litnum sem við viljum lita þá þætti þar sem gögnin passa ekki saman. Við ýtum á hnappinn "OK".

Fara aftur á gluggann til að búa til formunarreglu, smelltu á hnappinn. "OK".

Eftir sjálfkrafa að fara í gluggann Reglurastjóri smelltu á hnappinn "OK" og í því.

Nú í annarri töflunni verða þættir sem hafa gögn sem passa ekki við samsvarandi gildi fyrsta töflustigs auðkenndar í valdum lit.

Það er önnur leið til að nota skilyrt formatting til að ná fram verkefninu. Eins og fyrri valkostir þurfa það að staðsetja bæði samanburðarsvæðin á sama blaði, en ólíkt áður lýstum aðferðum er skilyrði fyrir samstillingu eða flokkun gagna ekki nauðsynlegt, sem greinir þennan möguleika frá áður lýstum.

Gerðu úrval af svæðum sem þarf að bera saman.

Framkvæma umskipti í flipann sem heitir "Heim". Smelltu á hnappinn. "Skilyrt snið". Í virku skránni skaltu velja stöðu "Reglur um val á klefi". Í næstu valmynd gerum við val á stöðu. "Afrit gildi".

Glugginn til að setja val á afrita gildir er hleypt af stokkunum. Ef þú gerðir allt rétt, þá er aðeins í þessum glugga að smella á hnappinn. "OK". Þó, ef þú vilt, getur þú valið annað val lit í samsvarandi reit í þessum glugga.

Eftir að við framkvæmum tilgreint aðgerð verða allar tvíhliða þættir auðkenndir í völdum lit. Þeir þættir sem passa ekki saman verða áfram lituð í upprunalegum lit (hvítt sjálfgefið). Þannig geturðu strax séð sjónrænt hvað er munurinn á fylki.

Ef þú vilt getur þú þvert á móti málað ósamliggjandi þætti og þær vísbendingar sem passa geta skilið eftir með sama litafyllingu. Í þessu tilviki er reiknirit aðgerða nánast það sama, en í stillingarglugganum til að auðkenna afrit gildi í fyrsta reitnum í stað þess að stika "Afrit" veldu valkost "Einstakt". Eftir það skaltu smella á hnappinn "OK".

Þannig verður það lögð áhersla á þá vísbendingar sem passa ekki saman.

Lexía: Skilyrt snið í Excel

Aðferð 4: flókin formúla

Þú getur líka borið saman gögn með flóknu formúlu sem byggir á virkni COUNTES. Með því að nota þetta tól getur þú reiknað út hversu mikið hver þáttur úr völdum dálki í seinni töflunni endurtekur í fyrsta.

Flugrekandi COUNTES vísar til tölfræðilegra hópa virka. Verkefni hans er að telja fjölda frumna sem gildi uppfylla ákveðin skilyrði. Samantekt þessarar símafyrirtækis er sem hér segir:

= COUNTERS (svið, viðmiðun)

Rök "Svið" er heimilisfang fylkisins þar sem samsvarandi gildi eru reiknuð.

Rök "Viðmiðun" setur leikskilyrði. Í okkar tilviki verður það hnit ákveðinna frumna í fyrsta borði.

Veldu fyrsta þáttinn í viðbótar dálknum þar sem fjöldi samsvörunar verður reiknuð. Næst skaltu smella á táknið "Setja inn virka".

Sjósetja á sér stað Virkni meistarar. Fara í flokk "Tölfræðileg". Finndu á listanum nafnið "COUNTES". Þegar þú hefur valið það skaltu smella á hnappinn. "OK".

Rammaglugga stjórnandans er hleypt af stokkunum. COUNTES. Eins og þú sérð eru nöfn reitanna í þessum glugga samsvarandi nöfn rökanna.
Settu bendilinn í reitinn "Svið". Eftir það heldurðu vinstri músarhnappi, veldu öll gildi dálksins með nöfnum seinni töflunnar. Eins og sjá má falla hnitin strax í tilgreint reit. En í okkar tilgangi ætti þetta heimilisfang að vera algert. Til að gera þetta skaltu velja hnitin í reitnum og smella á takkann F4.
Eins og þú sérð hefur hlekkurin tekið algeru formi, sem einkennist af því að dollara merki eru til staðar.
Farðu síðan á akurinn "Viðmiðun"með því að setja bendilinn þar. Við smellum á fyrsta frumefni með eftirnöfnum í fyrsta borðinu. Í þessu tilfelli skaltu skilja ættingja tengilinn. Eftir að það er sýnt í reitnum getur þú smellt á hnappinn "OK".

Niðurstaðan birtist í lakseiningunni. Það er jafn fjöldi "1". Þetta þýðir að á listanum yfir nöfn annars töflu er eftirnafnið "Grinev V.P."sem er sá fyrsti í listanum yfir töflu í fyrsta töflunni, kemur einu sinni fram.

Nú þurfum við að búa til svipaða tjáningu fyrir alla aðra þætti fyrstu töflunnar. Til að gera þetta, afritaðu það með fylla merkinu, eins og við höfum gert áður. Settu bendilinn í neðri hægra megin á lakseiningunni sem inniheldur þá aðgerð COUNTES, og eftir að hafa breytt því í fylla merkið skaltu halda niðri vinstri músarhnappi og draga bendilinn niður.

Eins og hægt er að sjá gerði forritið útreikninga á samsvörun með því að bera saman hverja frumu fyrstu töflunnar með gögnum sem eru staðsettar í annarri töfluflokki. Í fjórum tilvikum kom niðurstaðan út "1", og í tveimur tilvikum - "0". Það er, forritið gat ekki fundið í seinni töflunni tvö gildi sem eru í fyrsta töflunni.

Auðvitað er hægt að nota þessa tjáningu til að bera saman töfluvísbendingar í núverandi formi, en það er tækifæri til að bæta það.

Við skulum gera svo að þessi gildi sem eru í boði í seinni töflunni, en eru fjarverandi í fyrsta, birtast á sérstökum lista.

Fyrst af öllu, skulum endurskoða formúluna okkar COUNTES, þ.e. gera það eitt af rökum rekstraraðila IF. Til að gera þetta skaltu velja fyrsta reitinn þar sem símafyrirtækið er staðsett COUNTES. Í formúlu barinu áður en við bætum við tjáningu "IF" án vitna og opna krappinn. Næstum, til að auðvelda okkur að vinna, veljum við gildi í formúlu bar. "IF" og smelltu á táknið "Setja inn virka".

Aðgerðarglugginn opnast. IF. Eins og sjá má er fyrsta reit gluggans þegar fyllt með verðmæti rekstraraðila. COUNTES. En við verðum að bæta við eitthvað annað á þessu sviði. Við setjum bendilinn þar og við bætum við núverandi tjáningu "=0" án tilvitnana.
Eftir það fara á völlinn "Gildi ef satt". Hér munum við nota annan hreiður virka - LINE. Sláðu inn orðið "LINE" án tilvitnana, þá opnarðu sviga og tilgreinir hnit fyrsta frumunnar með síðasta nafninu í seinni töflunni og lokaðu síðan svigunum. Sérstaklega í okkar tilviki á þessu sviði "Gildi ef satt" fékk eftirfarandi tjáningu:
LINE (D2)
Nú rekstraraðili LINE mun tilkynna aðgerðir IF Línanúmerið þar sem tiltekið eftirnafn er staðsett og í því tilfelli þegar ástandið sem tilgreint er í fyrsta reitnum er uppfyllt, virkar hlutverkið IF mun framleiða þetta númer í reitinn. Við ýtum á hnappinn "OK".

Eins og þú sérð birtist fyrsta niðurstaðan sem "FALSE". Þetta þýðir að verðmæti uppfyllir ekki skilyrði rekstraraðila. IF. Það er, fyrsta eftirnafn er til staðar í báðum listum.

Notaðu fylla merkið, á venjulegum hátt, afritum við tjáning rekstraraðila IF á öllu dálknum. Eins og þú getur séð, í tveimur stöðum sem eru til staðar í seinni töflunni, en ekki í fyrsta, gefur formúlan lína númer.

Fara frá borðstofu til hægri og fylla dálkinn með tölum í röð, frá og með 1. Fjöldi tölur verður að passa við fjölda raða í annarri samanburðarborðið. Til að flýta fyrir númerunaraðferðinni geturðu einnig notað fylla merkið.

Eftir það skaltu velja fyrsta reitinn hægra megin við dálkinn með tölum og smelltu á táknið "Setja inn virka".

Opnar Virka Wizard. Fara í flokk "Tölfræðileg" og valið nöfn "THE NAME". Smelltu á hnappinn "OK".

Virka Minnst, þar sem rökarglugginn hefur verið opnaður, er hannaður til að birta lægsta gildi sem tilgreint er af reikningnum.
Á sviði "Array" tilgreindu hnit sviðsins í viðbótarsúlunni "Fjöldi leikja"sem við breyttum áður með því að nota aðgerðina IF. Við gerum allar tenglar algerlega.
Á sviði "K" Tilgreindu hvaða reikning lægsta gildi ætti að birtast. Hér bendir við hnit fyrsta flokks dálksins með númerun, sem við höfum nýlega bætt við. Heimilisfangið er vinstri ættingja. Smelltu á hnappinn "OK".

Rekstraraðili birtir niðurstöðuna - númerið 3. Þetta er minnsti fjöldi mismatched raðir af fylki borð. Notaðu fylla merkið, afritaðu formúluna neðst.

Núna, með því að vita línu númerin sem ekki er samsvörun, getum við sett inn í reitinn og gildi þeirra með því að nota virknina INDEX. Veldu fyrsta þáttinn í lakinu sem inniheldur formúluna Minnst. Eftir það fara á formúlunni og fyrir nafnið "THE NAME" bæta við heiti INDEX án tilvitnana skaltu opna strax og setja hálfkúluna (;). Veldu síðan nafnið á formúlunni. INDEX og smelltu á táknið "Setja inn virka".

Eftir það opnast lítill gluggi þar sem þú þarft að ákvarða hvort tilvísunin ætti að virka INDEX eða hannað til að vinna með fylki. Við þurfum aðra valkost. Það er stillt sjálfgefið, svo í þessum glugga einfaldlega smellt á hnappinn. "OK".

Aðgerðarglugginn byrjar. INDEX. Þessi yfirlýsing er hönnuð til að sýna gildi sem er staðsett í tilteknu fylki í tilgreindum línu.
Eins og þú getur séð, reitinn "Línanúmer" þegar fyllt með virknistölum Minnst. Frá því gildi sem þegar er til staðar dregurðu frá muninn á númerinu á Excel-blaði og innri númerun borðhlutans. Eins og sjá má, ofan við borðgildi höfum við aðeins hettu. Þetta þýðir að munurinn er ein lína. Þess vegna bætum við við í reitinn "Línanúmer" merkingu "-1" án tilvitnana.
Á sviði "Array" tilgreinið heimilisfang gildissviðs seinni töflunnar. Á sama tíma gerum við öll hnit alger, það er að við setjum dollara skilti fyrir framan þá eins og áður hefur verið lýst af okkur.
Við ýtum á hnappinn "OK".

Eftir að útkoman hefur verið skilað út á skjánum teygðum við virkni með því að fylla merkið til loka dálksins niður. Eins og þú sérð eru bæði eftirnöfn sem eru til staðar í seinni töflunni, en ekki í fyrstu, sýndar á sérstökum sviðum.

Aðferð 5: Samanburður á fylki í mismunandi bókum

Við samanburð á sviðum í mismunandi bókum er hægt að nota aðferðirnar hér að ofan, að undanskildum þeim valkostum sem krefjast þess að báðar töflur séu settar á eitt blað. Helstu skilyrði fyrir því að framkvæma samanburðaraðferðina í þessu tilfelli er að opna glugga beggja skrárnar á sama tíma. Það eru engar vandamál fyrir útgáfur af Excel 2013 og síðar, svo og útgáfum fyrir Excel 2007. En í Excel 2007 og Excel 2010, til að hægt sé að opna bæði glugga á sama tíma, er þörf á frekari aðgerðum. Hvernig á að gera þetta er lýst í sérstökum lexíu.

Lexía: Hvernig á að opna Excel í mismunandi gluggum

Eins og þú sérð eru mörg tækifæri til að bera saman töflur við hvert annað. Hvaða möguleiki á að nota fer eftir því hvar töflu gögnin eru staðsett miðað við hvert annað (á einu blaði, í mismunandi bækur, á mismunandi blöðum) og einnig um nákvæmlega hvernig notandinn vill að þessi samanburður sé birtur á skjánum.