Kuidas võrrelda kahte veergu Excelis - Exceli andmete võrdlemise meetodid

See on peatükk raamatust: Michael Girwin. Ctrl + Tõst + Enter. Massiivivalemite valdamine Excelis.

Proovid, mis põhinevad ühel või mitmel tingimusel.Rida exceli funktsioonid kasutage võrdlusoperaatoreid. Näiteks SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF ja AVERAGEIFS. Need funktsioonid teevad valikuid ühe või mitme tingimuse (kriteeriumi) alusel. Probleem on selles, et need funktsioonid saavad ainult lisada, loendada ja keskmistada. Mis siis, kui soovite oma otsingule kehtestada tingimusi, näiteks maksimaalse väärtuse või standardhälbe? Nendel juhtudel, kuna sisseehitatud funktsiooni pole, peate leiutama massiivi valemi. See on sageli tingitud massiivi võrdlusoperaatori kasutamisest. Selle peatüki esimene näide näitab, kuidas arvutada miinimumväärtus ühe tingimuse korral.

Kasutame tingimusele vastavate massiivi elementide valimiseks funktsiooni IF. Joonisel fig. 4.1 vasakpoolses tabelis on veerg linnanimedega ja veerg ajaga. Iga linna jaoks on vaja leida minimaalne aeg ja asetada see väärtus parempoolse tabeli vastavasse lahtrisse. Valiku tingimuseks on linna nimi. Funktsiooni MIN kasutamisel leiate veeru B minimaalse väärtuse. Kuidas aga valida ainult need numbrid, mis on omased Oaklandile? Ja kuidas kopeerite valemeid veerust alla? Kuna Excelis pole sisseehitatud MINESLI funktsiooni, peate kirjutama originaalse valemi, mis ühendab funktsioonid IF ja MIN.

Joonis: 4.1. Valemi eesmärk: valige iga linna jaoks minimaalne aeg

Laadige märkus alla vormingus või vormingus

Nagu on näidatud joonisel fig. 4.2, peaksite valemisse lahtrisse E3 sisestama funktsiooniga MIN. Kuid te ei saa argumenteerida number1 kõik väärtused veerus B!? Soovite valida ainult need väärtused, mis on omased ainult Aucklandile.

Nagu on näidatud joonisel fig. 4.3, järgmises etapis sisestage argumendina funktsioon IF number1 MIN. Paned KUI MIN-i sisse.

Kursori asetamisega sinna, kuhu argument sisestatakse logi väljendus funktsioon IF (joonis 4.4), valite linnanimedega vahemiku A3: A8 ja vajutage seejärel lahtriviidete absoluutseks muutmiseks klahvi F4 (vt lähemalt näiteks). Seejärel sisestate võrdlusoperaatori, võrdusmärgi. Lõpuks valite valemist vasakule jääva lahtri - D3, hoides viidet sellele suhteliseks. Sõnastatud tingimus võimaldab teil vahemiku A3: A8 vaatamisel valida ainult Aucklandsi.


Joonis: 4.4. Looge argumendil massiivioperaator logi väljendus toimib IF

Nii et olete loonud massiivioperaatori, kasutades võrdlusoperaatorit. Massiivi töötlemise ajal on massiivioperaator võrdlusoperaator, nii et selle toimimise tulemuseks on TÕENE ja VALE väärtuste massiiv. Selle kontrollimiseks valige massiiv (selleks klõpsake argumendil tööriistavihjet logi väljendus) ja vajutage klahvi F9 (joonis 4.5). Tavaliselt kasutate ühte argumenti log_expression, tagastades kas TRUE või FALSE; siin tagastab saadud massiiv mitu TRUE ja FALSE väärtust, nii et MIN funktsioon valib minimaalse arvu linnadele, mis vastavad TRUE väärtusele.


Joonis: 4.5. TRUE ja FALSE väärtuste massiivi nägemiseks klõpsake tööriistavihis oleval argumendil logi väljendus ja vajutage klahvi F9

Meil on kaks tabelit tellimustest, mis on kopeeritud ühte töölehte. Excelis on vaja võrrelda kahe tabeli andmeid ja kontrollida, millised positsioonid on esimeses tabelis, kuid mitte teises. Iga lahtri väärtust pole mõtet käsitsi võrrelda.

Võrrelge Exceli vastete kahte veergu

Kuidas võrrelda Exceli väärtusi kahest veerust? Selle probleemi lahendamiseks soovitame kasutada tingimuslik vormindamineainult ühes veerus olevate üksuste kiireks esiletoomiseks. Tööleht koos tabelitega:

Esimene samm on mõlemale tabelile nimede määramine. See hõlbustab võrreldavate lahtrivahemike mõistmist:

  1. Valige tööriist FORMULAS - Defined Names - Assign Name.
  2. Sisestage ilmuvas aknas väljale "Nimi:" väärtus - Tabel_1.
  3. Paremklõpsake sisestusväljal "Range:" ja valige vahemik: A2: A15. Ja klõpsake nuppu OK.


Teise loendi puhul tehke sama, kuid määrake nimi - tabel_2. Ja märkige vahemik C2: C15 - vastavalt.

Kasulikud nõuanded! Vahemikunimesid saab kiiremini määrata nimevälja abil. See asub valemiribast vasakul. Valige lihtsalt lahtrivahemikud ja tippige väljale nimi sobiv vahemiku nimi ja vajutage sisestusklahvi.

Nüüd kasutame Exceli kahe loendi võrdlemiseks tingimuslikku vormindamist. Peame saama järgmise tulemuse:

Tabelis_1, kuid tabelis_2 olevad positsioonid kuvatakse roheliselt. Samal ajal tõstetakse tabelis_2 asetsevad, kuid tabelis_1 puuduvad positsioonid esile sinisega.

Exceli kahe veeru andmete võrdlemise põhimõte

Veergude lahtrite vormindamise tingimuste määratlemiseks kasutasime funktsiooni COUNTIF. Selles näites kontrollib see funktsioon, mitu korda esineb teise argumendi väärtus (näiteks A2) esimese argumendi loendis (näiteks tabel_2). Kui kordade arv \u003d 0, tagastab valem TÕENE. Sellisel juhul määratakse lahtrile tingimusliku vormindamise suvandites määratud kohandatud vorming. Teise argumendi viide on suhteline, mis tähendab, et kõiki valitud vahemiku lahtrit kontrollitakse kordamööda (näiteks A2: A15). Teine valem töötab sarnaselt. Sama põhimõtet saab rakendada erinevate sarnaste ülesannete puhul. Näiteks Excelis kahe hinna võrdlemiseks isegi

Excelis töötades on üsna tavaline ülesanne võrrelda erinevat tüüpi väärtuste loendeid. Väärtuste vahemike üldiselt ja eriti väärtuste veergude võrdlemiseks saab kasutada Exceli standardseid tööriistu nagu tingimuslik vormindamine ja funktsioonid. Lisaks saate selliseid ülesandeid lahendada VBA makrode ja nende põhjal Exceli lisandmoodulite abil.

Vajadus vahemike üldiselt ja eriti veergude võrdlemiseks tekib erinevate Excelis loodud aruannete, loendite ja andmebaaside värskendamisel ning aja jooksul muudatuste nõudmisel.IN interneti-võrgud on kirjeldatud üsna palju näiteid, mis kasutavad veergudes olevate väärtuste võrdlemiseks erinevaid funktsioone (valemeid) ja tingimuslikku vormindamist. Neile, kellele sellised meetodid mingil põhjusel ei sobi, võite kasutada mõnda muud tööriista, mis võimaldab võrrelda nii veerge kui ka suvalisest arvust ridadest ja veergudest koosnevaid väärtuste vahemikke. Exceli lisandmooduli abil saate võrrelda mis tahes kahte vahemikku või pigem võrrelda ühe vahemiku iga elementi teise elemendiga ja leida nii vasted kui ka nende vahelised erinevused.

Exceli võrdluslisand väärtuste jaoks kahes vahemikus

Allpool käsitletav lisandmoodul on loodud väärtuste võrdlemiseks kahes vahemikus, mis koosneb suvalisest arvust ridadest ja veergudest. See lisandmoodul kasutab protseduuri, mis võimaldab võrrelda iga vahemiku üksust teise vahemiku iga üksusega, teha kindlaks, millised üksused nendes vahemikes on ühesugused ja millised erinevad, ning täidab lahtrid kasutaja määratud värviga.

Nagu dialoogiboksist näha, on kõik üsna lihtne, kasutaja valib kaks võrreldavat vahemikku, määrab, mida otsida, erinevad väärtused või sama, valib värvi ja käivitab programmi. Pistikprogrammi kasutamise lõpptulemuseks on konkreetse värviga täidetud lahtrid, mis näitavad valitud vahemike väärtuste erinevusi või kattumisi.

Exceli väärtuste võrdlemisel on üks hoiatus. Numbreid saab vormindada tekstina, mis pole alati visuaalselt tuvastatav (). See tähendab, et Exceli arv võib olla kas arvuline või tekstiline väärtus ja need kaks väärtust ei ole üksteisega võrdsed. Väga sageli põhjustab see nähtus mitmesuguseid vigu. Selliste vigade välistamiseks kasutatakse valikut "Võrdle numbreid tekstina", mis on vaikimisi lubatud. Selle valiku kasutamine võimaldab võrrelda mitte arvväärtusi, vaid nendest teisendatud teksti.

Lisand võimaldab:

1. Avage ühe hiireklõpsuga otse Exceli tööriistaribalt makrodialoog;

2. leida vahemiku # 1 elemente, mis ei kuulu vahemikku # 2;

3. leida vahemiku # 2 elemente, mis ei kuulu vahemikku # 1;

4. leida vahemiku # 1 elemente, mis jäävad vahemikku # 2;

5. leida vahemiku # 2 elemente, mis jäävad vahemikku # 1;

6. valige üks soovitud väärtustega lahtrite üheksast täitevärvist;

7. Valige vahemikud kiiresti, kasutades valikut "Piirata vahemikke", samal ajal kui saate valida terveid ridu ja veerge, vähendades valitud vahemikku kasutatuks tehakse automaatselt;

8. numbriliste väärtuste võrdlemise asemel kasutage tekstiväärtuste võrdlemist, kasutades valikut "Võrdle numbreid tekstina";

9. võrrelda vahemiku lahtrites olevaid väärtusi, võtmata arvesse lisaruume;

10. Võrrelge vahemiku lahtrites olevaid väärtusi juhtutundetult.

Kuidas võrrelda kahte veergu Exceli makro (lisandmooduli) abil?

Veergude võrdlus on suvaliste vahemike võrdlemise erijuhtum. Vahemikus nr 1 ja nr 2 valige kaks veergu ja saate valida täpselt veerud ning mitte lohistada valimisraami hiirtega lahtritega üle vahemiku (mugavuse huvides on vaikimisi lubatud valik "Vahemike piiramine", mis , kui tervete veergude või ridade valimine piirab sellist valikut vahemiku abil), valige erinevuste või vastete otsimiseks vajalik toiming, valige lahtrite täitevärv ja käivitage programm. Allpool näete kahes veerus vastavate väärtuste otsimise tulemust.

Võimalus Excelis kahte andmekogumit võrrelda on sageli kasulik inimestele, kes töötlevad suuri andmemahte ja töötavad tohutute tabelitega. Näiteks saab võrrelda andmete sisestamise õigsust või õigeaegset tabelisse sisestamist. Allpool olevas artiklis kirjeldatakse mitut tehnikat kahe veeru võrdlemiseks Exceli andmetega.

IF-tingimusoperaatori kasutamine

Tingimusliku operaatori IF kasutamise meetod erineb selle poolest, et kahe veeru võrdlemiseks kasutatakse ainult võrdlemiseks vajalikku osa, mitte kogu massiivi. Allpool on toodud rakendamise etapid seda meetodit:

Asetage mõlemad veerud võrdluseks töölehe veergudesse A ja B.

Lahtrisse C2 sisestage järgmine valem \u003d IF (ISERROR (OTSI (A2; $ B $ 2: $ B $ 11; 0)); ""; A2) ja laiendage seda lahtrile C11. See valem otsib järjestikku iga veeru B veeru A üksust ja tagastab üksuse väärtuse, kui see leitakse veerus B


VLOOKUP asendusvalemi kasutamine

Valemi põhimõte sarnaneb eelmise meetodiga, erinevus peitub otsingu asemel. Selle meetodi eripära on ka võime võrrelda kahte horisontaalset massiivi HLP valemi abil.

Kahe veeru võrdlemiseks veergude A ja B andmetega (sarnaselt eelmisele meetodile) sisestage lahtrisse C2 järgmine valem \u003d VLOOKUP (A2; $ B $ 2: $ B $ 11; 1; 0) ja lohistage see lahter C11.


See valem vaatleb võrreldud massiivi põhimassiivi kõiki elemente ja tagastab selle väärtuse, kui see leiti veerust B. Vastasel juhul tagastab Excel tõrke # N / A.

VBA makro kasutamine

Makrode kasutamine kahe veeru võrdlemiseks ühtlustab protsessi ja vähendab andmete ettevalmistamise aega. Otsus selle kohta, milline võrdlustulemus tuleks kuvada, sõltub täielikult teie kujutlusvõimest ja oskustest makrode kasutamisel. Allpool on tehnika avaldatud Microsofti ametlikul veebisaidil.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

Alamleht_Matches ()
Hämardada Võrdle vahemik Variant, x Variant, y Variant
Msgstr "Muutuja võrdlusala määramine võrdse vahemikuga
Määra CompareRange \u003d Vahemik ("B1: B11")
"Kui võrreldav vahemik asub teisel lehel või töövihikus,
"kasuta järgmist süntaksit
"Set CompareRange \u003d töövihikud (" Book2 "). _
"Töölehed (" Leht2 "). Vahemik (" B1: B11 ")
"
"Valitud vahemiku iga elemendi võrdlemine iga elemendiga
"muutuja Võrdlusvahemik
Iga x valikul
Igaühe kohta võrdleb
Kui x \u003d y, siis x. Offset (0, 2) \u003d x
Järgmine y
Järgmine x
Lõpeta alam

Selles koodis määratakse muutujale CompareRange võrreldava massiiviga vahemik. Seejärel käivitab silmus, mis teeb silmuse läbi iga valitud vahemiku üksuse ja võrdleb seda iga võrreldava vahemiku üksusega. Kui leiti samade väärtustega elemendid, sisestab makro veeru C elemendi väärtuse.

Makro kasutamiseks pöörduge tagasi töölehele, valige põhivahemik (meie puhul on need lahtrid A1: A11), vajutage klahve Alt + F8. Valige ilmuvas dialoogis makro Leia_Tikudja klõpsake nuppu Käivita.


Pärast makro käivitamist peaks tulemus olema järgmine:

Päringu lisandmooduli kasutamine

Tulemus

Niisiis vaatasime Excelis andmete võrdlemiseks mitmeid viise, mis aitavad teil lahendada mõningaid analüütilisi probleeme ja hõlbustavad duplikaatide (või ainulaadsete) väärtuste leidmist.

Kui võrrelda mitut võrreldavat kirjet Exceli arvutustabelid, on andmed korraldatud sageli veergudesse, nii et nende objektide omadusi on mugav rea kaupa võrrelda. Näiteks automudelid, telefonid, katse- ja kontrollgrupid, mitmed jaekettide kauplused jne. Suure hulga liinide korral ei saa visuaalne analüüs olla usaldusväärne. Funktsioonid VLOOKUP, INDEX, SEARCH (VLOOKUP, INDEX, MATCH) on mugavad lahtrite andmete võrdlemiseks ega anna üldpilti. Kuidas saate teada, kui sarnased veerud on üldiselt? Kas veerud on identsed?

Kaardiveergude lisandmoodul võimaldab teil veerge kaardistada ja näha suurt pilti:

  • Võrrelge kahte või enamat veergu üksteisega
  • Veergude võrdlemine võrdlusväärtustega
  • Arvutage täpne vaste protsent
  • Esitage tulemus visuaalses pöördetabelis

Video keel: inglise. Subtiitrid: vene, inglise. (Tähelepanu: video ei pruugi kajastuda viimased värskendused... Kasutage allolevaid juhiseid.)

Lisage rakendustesse Excel 2016, 2013, 2010, 2007 "Veerude vastendamine"

Sobilik: Microsoft Excel 2016 - 2007, töölaua Office 365 (32- ja 64-bitine).

Lisandmooduliga töötamine:

Kuidas võrrelda kahte või enamat veergu omavahel ja arvutada vaste protsent

Vaatleme tootearenduse näidet. Oletame, et peate mitu valmis prototüüpi omavahel võrdlema ja välja selgitama, kui sarnased, erinevad ja võib-olla isegi identsed nad on.

  1. Klõpsake XLToolsi tööriistariba nuppu Kaardiveerud\u003e Vali üksteisele kaardiveerud.

  3. Klõpsake nuppu OK\u003e


Nõuanne:
Palun vali kokkuvõtlik tabel tulemus\u003e Klõpsake ikooni Ekspressanalüüs\u003e Rakenda "Värviskaala".

Tulemuse lugemine: 1. ja 3. tüüpi prototüübid on peaaegu identsed, 99% vastavuse määr tähendab, et 99% nende parameetritest ridades on ühesugused. Tüüp 2 ja tüüp 4 on kõige vähem sarnased - nende parameetrid langevad kokku vaid 30%.

Kuidas võrrelda veerge võrdlusväärtustega ja arvutada vastavusaste

Vaatleme tootearenduse näidet. Oletame, et peate võrdlema mitut valmis prototüüpi kindla sihtstandardiga ja arvutama ka prototüüpide vastavuse nendele standarditele.

  1. Valige võrdlemiseks veerud.
    Näiteks prototüübi andmetega veerud.
  2. Klõpsake XLToolsi tööriistaribal nuppu Kaardiveerud.
  3. Valige Map to Reference Column Range\u003e Select Reference Columns.
    Näiteks standarditega veerud.
  4. Märkige "Veerud sisaldavad pealkirju", kui jah.
  5. Märkige "Kuva vaste protsent", et kuvada vaste protsentides.
    Vastasel juhul kuvatakse tulemus 1 (täielik vaste) või 0 (vaste puudub).
  6. Märkige, kuhu tulemus tuleks panna: uuele lehele või olemasolevale lehele.
  7. Klõpsake nuppu OK\u003e Lõpeta, tulemus kuvatakse pöördtabelis.


Nõuanne: Tulemuse tõlgendamise hõlbustamiseks rakendage sellele tingimusvormingut:
Valige tulemuse pöördtabel\u003e klõpsake ikooni Kiiranalüüs\u003e Rakenda "Värviskaala".

Tulemuse lugemine: 2. tüüpi prototüüp vastab 99% standardile 2, st. 99% nende parameetritest stringides on samad. Toode 5 on standardile 3 kõige lähemal - 96% nende parameetritest on identsed. Samal ajal ei vasta toode 4 kaugeltki ühelegi kolmest standardist. Nüüd võime järeldada, kui palju kumbki prototüüp kaldub sihtmärgi kontrollväärtustest kõrvale.

Millistes ülesannetes aitab lisandmoodul Veeruveered?

Lisandmoodul skaneerib lahtrid rida haaval ja arvutab veergudes identsete väärtuste protsendi. XLToolsi vaste veerud ei sobi lahtrites olevate väärtuste tavapäraseks võrdlemiseks - see ei ole mõeldud duplikaatide või kordumatute väärtuste leidmiseks.

Kaardiveergude lisandmoodulil on teistsugune eesmärk. Tema peamine ülesanne - saada teada, kuidas üldiselt on andmekogumid (veerud) sarnased või erinevad. Lisandmoodul aitab analüüsida suures koguses andmeid, kui peate vaatama näiteks laiemat makrotaset. vasta järgmistele küsimustele:

  • Kui sarnased on katserühmade tulemused
  • Kui sarnased on katse- ja kontrollrühmade tulemused
  • Kui sarnased / erinevad on mitu sama kategooria toodet
  • Kui lähedal on töötajate KPI-d kavandatud näitajatele?
  • Kui sarnased on mitme jaekaupluse jne näitajad.


Soovitame lugeda