Tafla samanburðaraðferðir í Microsoft Excel

Oft eru Excel notendur frammi fyrir því að bera saman tvær töflur eða lista til að bera kennsl á mismun eða vanta þætti í þeim. Hver notandi tekst á við þetta verkefni á sinn hátt en oftast er frekar mikill tími gefinn í að leysa þetta mál þar sem ekki eru allar aðferðir við þetta vandamál rökréttar. Á sama tíma eru til nokkrir sannaðir reiknirit fyrir aðgerðir sem gera þér kleift að bera saman lista eða töflugripi á nokkuð stuttum tíma með lágmarks fyrirhöfn. Við skulum skoða þessa valkosti nánar.

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

Samanburðaraðferðir

Það eru til nokkrar leiðir til að bera saman borðrými í Excel, en þeim er öllum hægt að skipta í þrjá stóra hópa:

Byggt á þessari flokkun eru í fyrsta lagi valdar samanburðaraðferðir, auk þess sem sérstakar aðgerðir og reiknirit eru ákvörðuð fyrir verkefnið. Til dæmis þegar þú berð saman í mismunandi bókum þarftu að opna tvær Excel skrár á sama tíma.

Að auki ætti að segja að það er aðeins skynsamlegt að bera saman borðsvæði þegar þau hafa svipaða uppbyggingu.

Aðferð 1: einföld uppskrift

Auðveldasta leiðin til að bera saman gögn í tveimur töflum er að nota einfalda jafnréttisformúlu. Ef gögnin samsvara, þá gefur það SANN vísir, og ef ekki, þá FALSE. Þú getur borið saman bæði tölulegar og textagögn. Ókosturinn við þessa aðferð er að hún er aðeins hægt að nota ef gögnunum í töflunni er raðað eða raðað á sama hátt, samstillt og með sama fjölda lína. Við skulum sjá hvernig á að nota þessa aðferð í reynd með dæminu um tvær töflur settar á eitt blað.

Svo höfum við tvö einföld borð með lista yfir starfsmenn og laun þeirra. Nauðsynlegt er að bera saman lista starfsmanna og greina ósamræmi milli dálkanna sem nöfnin eru sett í.

1. Til að gera þetta þurfum við viðbótar dálk á blaðið. Við komum inn skilti þar "=". Síðan smellum við á fyrsta atriðið sem þú vilt bera saman á fyrsta listanum. Við setjum táknið aftur "=" frá lyklaborðinu. Smelltu síðan á fyrstu reitinn í dálkinum sem við erum að bera saman í annarri töflunni. Niðurstaðan er tjáning af eftirfarandi gerð:

   = A2 = D2

   

   Þó auðvitað, í hverju tilviki, hnitin verða önnur, en kjarninn verður sá sami.

2. Smelltu á hnappinn Færðu inntil að fá samanburðarárangur. Eins og þú sérð, þegar fyrstu frumur beggja listanna voru bornar saman, gaf forritið vísbendingu „SANNT“, sem þýðir samsvörun gagna.



3. Nú verðum við að framkvæma svipaða aðgerð og aðrar frumur beggja töflanna í dálkunum sem við erum að bera saman. En þú getur einfaldlega afritað formúluna sem mun verulega spara tíma. Þessi þáttur er sérstaklega mikilvægur þegar bornir eru saman listar við mikinn fjölda lína.

   Aðferðin við afritun er auðveldlega framkvæmd með því að nota fyllimerkið. Við sveima yfir neðra hægra horni klefans, þar sem við fengum vísinn „SANNT“. Á sama tíma ætti að breyta því í svartan kross. Þetta er fyllimerkið. Við ýtum á vinstri músarhnappinn og drögum bendilinn niður á fjölda lína í samanburðartöflunni.



4. Eins og þú sérð birtast nú í viðbótardálki allar niðurstöður samanburðar á gögnum í tveimur dálkum töflugripa. Í okkar tilviki voru gögnin á einni línunni ekki samsvörun. Þegar samanburður var gerður skilaði formúlan niðurstöðunni FALSE. Eins og við sjáum framleiddar samanburðarformúlan vísir fyrir allar aðrar línur „SANNT“.



5. Að auki er mögulegt að reikna út fjölda misræmis með sérstakri formúlu. Til að gera þetta, veldu þáttinn á blaði þar sem hann verður sýndur. Smelltu síðan á táknið „Setja inn aðgerð“.



6. Í glugganum Töframaður töframaður í hópi rekstraraðila „Stærðfræði“ veldu nafnið SUMPRODUCT. Smelltu á hnappinn „Í lagi“.



7. Aðgerðarglugginn er virkur. SUMPRODUCTsem hefur aðalverkefni að reikna summan af afurðum valins sviðs. En þessa aðgerð er hægt að nota í okkar tilgangi. Setningafræðin er frekar einföld:

   = SUMPRODUCT (fylki1; fylki2; ...)

   Alls er hægt að nota netföng allt að 255 fylki sem rök. En í okkar tilviki munum við aðeins nota tvö fylki, auk þess sem ein rök.

   Settu bendilinn í reitinn „Array1“ og veldu á blaði samanburðargagnasviðið á fyrsta svæðinu. Eftir það skaltu setja skilti á akurinn ekki jafnir () og veldu samanburðarsvið annars svæðisins. Næst skaltu vefja tjáningunni sem fæst í sviga áður en við setjum tvo stafi "-". Í okkar tilviki reyndist þessi tjáning:

   - (A2: A7D2: D7)

   Smelltu á hnappinn „Í lagi“.



8. Rekstraraðili reiknar út og sýnir niðurstöðuna. Eins og þú sérð, í okkar tilfelli, er niðurstaðan jöfn og fjöldinn "1", það er, það þýðir að eitt misræmi fannst í samanburðarlistunum. Ef listarnir væru alveg eins væru niðurstöðurnar jafnar tölunni "0".

Á sama hátt er hægt að bera saman gögn í töflum sem eru á mismunandi blöðum. En í þessu tilfelli er æskilegt að línurnar í þeim séu tölusettar. Annars er samanburðarferlið næstum nákvæmlega það sama og lýst er hér að ofan, nema þá staðreynd að þegar þú slærð inn formúluna þarftu að skipta á milli blaða. Í okkar tilviki mun tjáningin líta svona út:

= B2 = Blað2! B2

Það er, eins og við sjáum, áður en hnit gagna, sem eru staðsett á öðrum blöðum, annað en þar sem niðurstaðan af samanburðinum er sýnd, er blaðnúmerið og upphrópunarmerki gefið til kynna.

Aðferð 2: veldu frumuhópa

Hægt er að gera samanburð með vali tólflokkshópsins. Það er einnig hægt að nota til að bera saman aðeins samstillta og pantaða lista. Að auki, í þessu tilfelli, ættu listarnir að vera staðsettir við hliðina á hvor öðrum á sama blaði.

1. Við veljum samanburðinn fylki. Farðu í flipann „Heim“. Næst skaltu smella á táknið Finndu og undirstrikaðustaðsett á borði í verkfærakistunni „Að breyta“. Listi opnast til að velja staðsetningu "Að velja hóp frumna ...".

   

   Að auki getum við komist að viðeigandi glugga til að velja hóp frumna á annan hátt. Þessi valkostur mun vera sérstaklega gagnlegur fyrir þá notendur sem hafa sett upp útgáfu af forritinu fyrr en Excel 2007, þar sem aðferðin er í gegnum hnappinn Finndu og undirstrikaðu þessar umsóknir styðja ekki. Við veljum fylkin sem við viljum bera saman og ýtum á takkann F5.

2. Lítill umskiptagluggi er virkur. Smelltu á hnappinn „Veldu ...“ í neðra vinstra horninu.



3. Eftir það, hvor af þessum tveimur af ofangreindum valkostum sem þú velur, er glugginn til að velja hópa frumna ræstur. Stilltu rofann í stöðu „Veldu línu fyrir línu“. Smelltu á hnappinn „Í lagi“.



4. Eins og þú sérð, eftir þetta verða ósamsvarandi gildi línanna auðkennd með öðrum lit. Að auki, eins og hægt er að dæma út frá innihaldi formúlulínunnar, mun forritið gera virkan af frumunum sem staðsettar eru í tilgreindum ósamsvarandi línum.

Aðferð 3: skilyrt snið

Þú getur borið saman með skilyrta sniðunaraðferðinni. Eins og í fyrri aðferð, ættu samanburðar svæðin að vera á sama Excel vinnublöð og vera samstillt við hvert annað.

1. Í fyrsta lagi veljum við hvaða borðsvæði við munum líta á sem aðalatriðið og til að leita að mismuninum. Við skulum gera það síðasta í annarri töflunni. Þess vegna veljum við lista yfir starfsmenn sem staðsettir eru í honum. Með því að fara í flipann „Heim“smelltu á hnappinn Skilyrt sniðsem er staðsett á borði í reitnum Stílar. Farðu í fellivalmyndina Reglustjórnun.



2. Gluggi reglustjórans er virkur. Smelltu á hnappinn í honum Búðu til reglu.



3. Veldu staðsetningu í glugganum sem byrjar Notaðu formúlu. Á sviði „Snið frumur“ skrifaðu formúlu sem inniheldur heimilisföng fyrstu hólfanna á sviðum samanburðar dálkanna, aðskilin með „ekki jafn“ merki () Aðeins þessi tjáning stendur frammi fyrir að þessu sinni. "=". Að auki verður að beita algerri takmörkun á öll dálkahnit í þessari formúlu. Til að gera þetta skaltu velja formúluna með bendilinn og ýta þrisvar á takkann F4. Eins og þú sérð birtist dollaramerki nálægt öllum dálkföngum, sem þýðir að breyta hlekkjum í algera. Að því er varðar okkar sérstaka tilfelli mun formúlan taka eftirfarandi form:

   = $ A2 $ D2

   Við skrifum þessa tjáningu á ofangreindu sviði. Eftir það skaltu smella á hnappinn „Snið ...“.



4. Gluggi er virkur Klefi snið. Farðu í flipann „Fylltu“. Hér á listanum yfir liti hættum við valinu á litnum sem við viljum lita þá þætti þar sem gögnin passa ekki saman. Smelltu á hnappinn „Í lagi“.



5. Farðu aftur í gluggann til að búa til sniðreglu, smelltu á hnappinn „Í lagi“.



6. Eftir að hafa sjálfkrafa flutt í gluggann Reglustjóri smelltu á hnappinn „Í lagi“ og í því.



7. Nú í annarri töflunni verða þættir sem hafa gögn sem ekki fara saman við samsvarandi gildi fyrsta töflusvæðisins auðkenndir í völdum lit.

Það er önnur leið til að nota skilyrt snið á verkefnið. Eins og fyrri valkostir krefst það staðsetningu beinna samanburðarsvæða á sama blaði, en ólíkt þeim aðferðum sem áður hefur verið lýst er skilyrðið fyrir samstillingu eða flokkun gagna ekki skylt, sem aðgreinir þennan valkost frá þeim sem áður voru lýst.

1. Við veljum svæðin sem ber að bera saman.



2. Farðu í flipann sem heitir „Heim“. Smelltu á hnappinn Skilyrt snið. Veldu staðsetningu á virku listanum Reglur um val á klefi. Í næsta matseðli tökum við val um staðsetningu Tvítekið gildi.



3. Glugginn til að stilla val á afritagildum byrjar. Ef þú gerðir allt rétt, þá er það aðeins í þessum glugga að smella á hnappinn „Í lagi“. Þó, ef óskað er, í samsvarandi reit þessa glugga, getur þú valið annan hápunkt lit.



4. Eftir að við höfum framkvæmt tiltekna aðgerð verða allir endurteknir þættir auðkenndir í völdum lit. Þeir þættir sem passa ekki verða áfram málaðir í upprunalegum lit (sjálfgefið hvítur). Þannig geturðu strax sjónrænt séð hver munurinn er á fylki.

Ef þess er óskað getur þú, þvert á móti, litað þá þætti sem ekki eru samsvaraðir, og þessir vísar sem passa, láta fyllinguna vera með sama lit. Í þessu tilfelli er reiknirit aðgerða næstum það sama, en í stillingarglugganum til að auðkenna afrit gildi í fyrsta reitnum í stað breytunnar Afrit ætti að velja „Einstakt“. Eftir það skaltu smella á hnappinn „Í lagi“.

Þannig verður einmitt bent á þá vísa sem ekki fara saman.

Lexía: Skilyrt snið í Excel

Aðferð 4: flókin uppskrift

Þú getur einnig borið saman gögn með flókinni formúlu sem byggist á aðgerðinni TALA. Með því að nota þetta tól geturðu reiknað út hversu mikið hver þáttur úr völdum dálki í annarri töflunni er endurtekinn í þeirri fyrstu.

Rekstraraðili TALA átt við tölfræðilegan hóp aðgerða. Verkefni þess er að telja fjölda frumna sem gildi uppfylla tiltekið skilyrði. Setningafræði þessa rekstraraðila er sem hér segir:

= COUNTIF (svið; viðmiðun)

Rök „Svið“ táknar heimilisfang fylkisins þar sem samsvarandi gildi eru reiknuð út.

Rök „Viðmiðun“ setur samsvörunarskilyrði. Í okkar tilviki verður það hnit sérstakra frumna á fyrsta töflusvæðinu.

1. Við veljum fyrsta þáttinn í viðbótarsúlunni þar sem fjöldi leikja verður taldur. Næst skaltu smella á táknið „Setja inn aðgerð“.



2. Ræsir upp Töframaður töframaður. Farðu í flokkinn "Tölfræðilegt". Finndu nafnið á listanum „COUNTIF“. Eftir að þú hefur valið það, smelltu á hnappinn „Í lagi“.



3. Gagnrýni glugga rekstraraðila ræst TALA. Eins og þú sérð samsvara nöfn reitanna í þessum glugga nöfnum á rökum.

   Stilltu bendilinn í reitinn „Svið“. Eftir það heldurðu vinstri músarhnappi og velur öll gildi dálksins með nöfnum seinni töflunnar. Eins og þú sérð falla hnitin strax inn í tiltekinn reit. En í okkar tilgangi ætti þetta heimilisfang að vera alger. Til að gera þetta, veldu þessi hnit á sviði og ýttu á takkann F4.

   Eins og þú sérð hefur hlekkurinn tekið algera mynd sem einkennist af tilvist dollaramerkja.

   Farðu síðan á akurinn „Viðmiðun“með því að stilla bendilinn þar. Við smellum á fyrsta þáttinn með eftirnöfnum í fyrsta töflu sviðinu. Í þessu tilfelli skaltu skilja tengilinn eftir. Eftir að það er birt á reitnum geturðu smellt á hnappinn „Í lagi“.



4. Niðurstaðan birtist í blaðaeiningunni. Það er jafnt og fjöldinn "1". Þetta þýðir að á listanum yfir nöfn seinni töflunnar er eftirnafnið "Grinev V.P.", sem er sá fyrsti á listanum yfir fyrstu töflunni, kemur einu sinni fram.



5. Nú verðum við að búa til svipaða tjáningu fyrir alla aðra þætti fyrstu töflunnar. Til að gera þetta, munum við afrita með því að nota fyllingarmerkið, eins og við gerðum áður. Settu bendilinn neðst til hægri í blaðinu sem inniheldur aðgerðina TALA, og eftir að hafa umbreytt því í áfyllingarmerki, haltu vinstri músarhnappnum niður og dragðu bendilinn niður.



6. Eins og þú sérð reiknaði forritið saman tilviljana með því að bera saman hverja reit fyrstu töflunnar við gögn sem eru í öðru töflu sviðinu. Í fjórum tilvikum kom niðurstaðan út "1", og í tveimur tilvikum - "0". Það er, forritið gat ekki fundið í annarri 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ísa, í núverandi mynd, en það er tækifæri til að bæta það.

Við tryggjum að þessi gildi sem eru í annarri töflunni en séu ekki í þeirri fyrstu séu sýnd á sérstökum lista.

1. Í fyrsta lagi munum við vinna upp formúluna okkar örlítið TALA, nefnilega, við gerum það að einu af rökum rekstraraðila EF. Til að gera þetta, veldu fyrstu reitinn sem rekstraraðilinn er í TALA. Bætið tjáningunni við formúlulínuna á undan henni EF án tilvitnana og opna krappann. Næst, til að auðvelda okkur að vinna, veldu gildi á formúlunni EF og smelltu á táknið „Setja inn aðgerð“.



2. Aðgerðarglugginn opnast EF. Eins og þú sérð er fyrsti reitur gluggans þegar fylltur með gildi rekstraraðila TALA. En við verðum að bæta einhverju öðru við þennan reit. Við setjum bendilinn þar og bætum við núverandi tjáningu "=0" án tilboða.

   Eftir það, farðu á akurinn „Merking ef satt“. Hér munum við nota aðra hreiðuraðgerð - LÍN. Sláðu inn orðið LÍN án tilvitnana, opnaðu þá sviga og tilgreindu hnit fyrstu hólfsins með eftirnafninu í annarri töflunni og lokaðu síðan sviga. Sérstaklega í okkar tilviki á þessu sviði „Merking ef satt“ Eftirfarandi tjáning reyndist:

   Lína (D2)

   Nú rekstraraðilinn LÍN mun tilkynna um aðgerðir EF númer línunnar sem tiltekið eftirnafn er í, og ef skilyrðið sem tilgreint er í fyrsta reitnum er fullnægt, aðgerðin EF birtir þetta númer í klefanum. Smelltu á hnappinn „Í lagi“.



3. Eins og þú sérð er fyrsta niðurstaðan birt sem FALSE. Þetta þýðir að verðmætið fullnægir ekki skilyrðum rekstraraðila. EF. Það er, fyrsta eftirnafnið er til á báðum listunum.



4. Með því að nota fyllimerkið afritum við stjórnandatjáninguna á venjulegan hátt EF á allan dálkinn. Eins og þú sérð, fyrir tvær stöður sem eru til staðar í annarri töflunni, en ekki í þeirri fyrstu, gefur formúlan línurúmer.



5. Við förum frá borðsvæðinu til hægri og fyllum dálkinn með tölum í röð, frá og með 1. Fjöldi talna verður að passa við fjölda lína í annarri töflunni sem ber saman. Til að flýta fyrir númeraferlinu geturðu einnig notað fyllingarmerkið.



6. Eftir það skaltu velja fyrstu reitinn hægra megin við dálkinn með tölum og smella á táknið „Setja inn aðgerð“.



7. Opnar Lögun töframaður. Farðu í flokkinn "Tölfræðilegt" og gera val um nafnið „SÍÐAST“. Smelltu á hnappinn „Í lagi“.



8. Virka MINNSTsem rifrunarglugginn hefur verið opnaður, er ætlað að sýna minnsta gildi sem tilgreint er á reikningnum.

   Á sviði Fylking tilgreindu hnit sviðs viðbótarsúlunnar „Fjöldi leikja“sem við höfum áður breytt með aðgerðinni EF. Við gerum alla tengla algera.

   Á sviði "K" gefur til kynna hvaða reikning lægsta gildi þarf að sýna. Hér gefum við til kynna hnit fyrstu frumunnar í dálkinum með tölunúmerum, sem við bættum við nýlega. Við skiljum eftir ættingja. Smelltu á hnappinn „Í lagi“.



9. Rekstraraðilinn sýnir niðurstöðuna - númer 3. Það er það minnsta af tölustöfunum sem eru ósamsvarandi raðir af borðfellum. Notaðu áfyllingarmerkið og afritaðu formúluna alveg til botns.



10. Nú, með því að þekkja línunúmer ósamstæðra þátta, getum við sett inn í klefann gildi þeirra með aðgerðinni INDEX. Veldu fyrsta þáttinn í blaði sem inniheldur formúluna MINNST. Eftir það skaltu fara í formúlulínuna og á undan nafninu „SÍÐAST“ bæta við nafninu INDEX án tilvitnana skaltu opna krappann strax og setja semíkolón (;) Veldu síðan nafnið í formúlulínunni INDEX og smelltu á táknið „Setja inn aðgerð“.



11. Eftir það opnast lítill gluggi þar sem þú þarft að ákvarða viðmiðunarskjáinn ætti að hafa aðgerð INDEX eða hannað til að vinna með fylki. Við þurfum seinni kostinn. Það er sjálfgefið sett upp, svo í þessum glugga smellirðu bara á hnappinn „Í lagi“.



12. Aðgerðarglugginn byrjar INDEX. Þessum rekstraraðila er ætlað að gefa út gildi sem er staðsett í ákveðinni fylki í tilgreindum streng.

    Eins og þú sérð, akurinn Línunúmer þegar fyllt með virknigildi MINNST. Af gildinu sem þegar er til staðar ætti að taka mismuninn á milli númera Excel-blaðsins og innri númerunar töflusvæðisins. Eins og þú sérð höfum við aðeins haus yfir töflugildin. Þetta þýðir að munurinn er ein lína. Þess vegna bætum við við á sviði Línunúmer gildi "-1" án tilboða.

    Á sviði Fylking tilgreinið heimilisfang gildissviðs annarrar töflu. Á sama tíma gerum við öll hnit alger, það er að við setjum fyrir þau dollaramerkið á þann hátt sem við höfum áður lýst.

    Smelltu á hnappinn „Í lagi“.



13. Eftir að niðurstaðan hefur verið sýnd á skjánum, lengjum við aðgerðina með því að nota fyllimerkið til botns í súlunni. Eins og þú sérð eru bæði eftirnöfn sem eru til staðar í seinni töflunni, en eru ekki í þeim fyrsta, birt í sérstöku svið.

Aðferð 5: bera saman fylki í mismunandi bókum

Þegar þú berð saman svið í mismunandi bókum geturðu notað ofangreindar aðferðir, nema þá valkosti þar sem þú vilt setja bæði borðsvæðin á eitt blað. Aðalskilyrði fyrir samanburðarferlið í þessu tilfelli er að opna glugga beggja skrár samtímis. Fyrir útgáfur af Excel 2013 og síðar, svo og fyrir útgáfur fyrir Excel 2007, eru engin vandamál með þetta ástand. En í Excel 2007 og Excel 2010, til að opna báða gluggana á sama tíma, er þörf á viðbótaraðgerðum. Hvernig á að gera þetta er lýst í sérstakri kennslustund.

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

Eins og þú sérð eru ýmsir möguleikar til að bera saman töflur sín á milli. Hvaða valkostur sem á að nota veltur á því hvar nákvæmlega töflugögnin eru staðsett miðað við hvert annað (á einu blaði, í mismunandi bókum, á mismunandi blöðum), og einnig hvernig nákvæmlega notandinn vill að þessi samanburður verði sýndur á skjánum.