Kako primerjati dva stolpca v Excelu - metode primerjave podatkov v Excelu

To je poglavje iz knjige: Michael Girwin. Ctrl + Shift + Enter. Obvladovanje matričnih formul v Excelu.

Vzorci na podlagi enega ali več pogojev.Vrstica funkcije Excel uporabite operatorje primerjave. Na primer SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF in AVERAGEIFS. Te funkcije izbirajo na podlagi enega ali več pogojev (meril). Težava je v tem, da lahko te funkcije samo dodajajo, štejejo in povprečujejo. Kaj pa, če želite iskanju naložiti pogoje, na primer največjo vrednost ali standardni odklon? Ker v teh primerih ni vgrajene funkcije, morate izumiti matrično formulo. To je pogosto posledica uporabe operatorja primerjave nizov. Prvi primer v tem poglavju prikazuje, kako izračunati najmanjšo vrednost pod enim pogojem.

S funkcijo IF izberemo elemente matrike, ki izpolnjujejo pogoj. Na sl. 4.1 v levi tabeli je stolpec z imeni mest in stolpec s časom. Najti je treba minimalni čas za vsako mesto in to vrednost umestiti v ustrezno celico v desni tabeli. Pogoj za izbiro je ime mesta. Če uporabljate funkcijo MIN, lahko najdete najmanjšo vrednost za stolpec B. Kako pa izberete samo tiste številke, ki so specifične za Oakland? In kako kopirate formule v stolpec? Ker Excel nima vgrajene funkcije MINESLI, morate napisati izvirno formulo, ki združuje funkciji IF in MIN.

Slika: 4.1. Namen formule: izberite najmanjši čas za vsako mesto

Prenesite opombo v obliki ali obliki

Kot je prikazano na sl. 4.2, morate začeti vnašati formulo v celico E3 s funkcijo MIN. Vendar se ne morete prepirati številka 1 vse vrednosti v stolpcu B!? Izbrati želite samo tiste vrednosti, ki so značilne za Auckland.

Kot je prikazano na sl. 4.3 v naslednjem koraku vnesite funkcijo IF kot argument številka 1 za MIN. IF vstavite v MIN.

S postavitvijo kazalca, kamor je vnesen argument izraz_dnevnika funkcija IF (slika 4.4), izberete obseg z imeni mest A3: A8 in nato pritisnete F4, da postanejo sklici na celice absolutni (glejte, na primer, za več podrobnosti). Nato vtipkate operator primerjave, znak enačbe. Na koncu boste izbrali celico levo od formule - D3, pri čemer bo referenca nanjo relativna. Formuliran pogoj vam bo omogočil, da izberete samo Aucklands med ogledom obsega A3: A8.


Slika: 4.4. Ustvari operator array na argumentu izraz_dnevnika funkcije IF

Torej ste ustvarili operator matrike z uporabo primerjalnega operaterja. Kadar koli med obdelavo matrike je operator matrike operater primerjave, tako da bo rezultat njegovega delovanja matrika TRUE in FALSE vrednosti. Če želite to preveriti, izberite matriko (če želite to narediti, kliknite v opisu argumenta izraz_dnevnika) in pritisnite F9 (slika 4.5). Običajno uporabite en argument izraz_dnevnika, vrnitev TRUE ali FALSE; tukaj bo nastala matrika vrnila več vrednosti TRUE in FALSE, zato bo funkcija MIN izbrala najmanjše število mest, ki se ujemajo z vrednostjo TRUE.


Slika: 4.5. Če si želite ogledati niz TRUE in FALSE vrednosti, kliknite argument v opisu orodja izraz_dnevnika in pritisnite F9

V tabelo imamo kopirani dve tabeli naročil. Primerjati je treba podatke dveh tabel v Excelu in preveriti, kateri položaji so v prvi tabeli, v drugi pa ne. Ni smiselno ročno primerjati vrednosti vsake celice.

Primerjajte dva stolpca za ujemanja v Excelu

Kako narediti primerjavo vrednosti v Excelu dveh stolpcev? Za rešitev te težave priporočamo uporabo pogojno oblikovanjeza hitro poudarjanje elementov, ki so v samo enem stolpcu. Delovni list s tabelami:

Prvi korak je dodelitev imen obema tabelama. Tako je lažje razumeti, kateri obsegi celic se primerjajo:

  1. Izberite orodje FORMULAS - Določena imena - Dodeli ime.
  2. V okno, ki se odpre, v polje "Ime:" vnesite vrednost - Tabela_1.
  3. Z levo miškino tipko kliknite vnosno polje "Razpon:" in izberite obseg: A2: A15. In kliknite V redu.


Za drugi seznam naredite enako, vendar dodelite ime - Tabela_2. In navedite obseg C2: C15 -.

Koristni nasvet! Imena obsegov lahko hitreje dodelite z uporabo imena. Nahaja se levo od vrstice s formulami. Preprosto izberite obsege celic in v polje z imenom vnesite ustrezno ime za obseg in pritisnite Enter.

Zdaj pa uporabimo pogojno oblikovanje za primerjavo dveh seznamov v Excelu. Dobiti moramo naslednji rezultat:

Pozicije v tabeli_1, ne pa v tabeli_2, bodo prikazane zeleno. Hkrati bodo položaji v tabeli_2, ki pa v tabeli_1 ne obstajajo, označeni z modro.

Načelo primerjave podatkov dveh stolpcev v Excelu

S funkcijo COUNTIF smo določili pogoje formatiranja celic stolpcev. V tem primeru ta funkcija preveri, kolikokrat se vrednost drugega argumenta (na primer A2) pojavi na seznamu prvega argumenta (na primer tabela_2). Če je število krat \u003d 0, potem formula vrne TRUE. V tem primeru je celici dodeljena oblika po meri, določena v pogojnih možnostih oblikovanja. Referenca v drugem argumentu je relativna, kar pomeni, da bodo vse celice izbranega obsega preverjene po vrsti (na primer A2: A15). Druga formula deluje na podoben način. Enak princip lahko uporabimo za različne podobne naloge. Na primer za primerjavo dveh cen v Excelu, celo

Pri delu v Excelu je dokaj pogosta naloga primerjati različne vrste seznamov vrednosti. Za primerjavo obsegov vrednosti na splošno in zlasti stolpcev vrednosti je mogoče uporabiti standardna orodja Excel, na primer pogojno oblikovanje in funkcije. Poleg tega lahko takšne naloge rešite z makri VBA in dodatki za Excel, ki temeljijo na njih.

Potreba po primerjanju obsegov na splošno in zlasti stolpcev se pojavi pri posodabljanju različnih poročil, seznamov in zbirk podatkov, ustvarjenih v Excelu, in sčasoma zahteva spremembe.IN internetna omrežja opisanih je kar nekaj primerov, ki uporabljajo različne funkcije (formule) in pogojno oblikovanje za primerjavo vrednosti v stolpcih. Za tiste, ki jim takšne metode iz kakršnega koli razloga niso primerne, lahko uporabite drugo orodje, ki omogoča primerjavo obeh stolpcev in vseh obsegov vrednosti, sestavljenih iz poljubnega števila vrstic in stolpcev. Z dodatkom za Excel lahko primerjate katera koli dva obsega ali natančneje primerjate vsak element enega obsega z vsakim elementom drugega in najdete tako ujemanja kot razlike med njimi po vaši izbiri.

Dodatek za primerjavo Excela za vrednosti v dveh obsegih

Dodatek, o katerem bomo razpravljali spodaj, je zasnovan za primerjavo vrednosti v dveh obsegih, sestavljenih iz poljubnega števila vrstic in stolpcev. Ta dodatek uporablja postopek za primerjavo vsakega elementa v enem obsegu z vsakim elementom v drugem obsegu, ugotavljanje, kateri elementi v teh obsegih so enaki in kateri različni, in celice napolni z uporabniško določeno barvo.

Kot lahko vidite iz pogovornega okna, je vse povsem preprosto, uporabnik izbere dva obsega, ki ju želite primerjati, določi, kaj je treba iskati, različne vrednosti ali enako, izbere barvo in zažene program. Končni rezultat uporabe dodatka so celice z določeno barvo, kar kaže na razlike ali prekrivanja v vrednostih izbranih obsegov.

Pri primerjavi vrednosti v Excelu obstaja en odtenek. Številke lahko oblikujete kot besedilo, ki ga ni vedno mogoče vizualno prepoznati (). To pomeni, da je številka v Excelu lahko številčna ali besedilna vrednost in ti dve vrednosti med seboj nista enaki. Ta pojav zelo pogosto povzroča različne vrste napak. Za izključitev takšnih napak je uporabljena možnost "Primerjaj številke kot besedilo", ki je privzeto omogočena. Z uporabo te možnosti lahko primerjate ne številske vrednosti, temveč besedilo, pretvorjeno iz njih.

Dodatek omogoča:

1. Z enim klikom miške odprite pogovorno okno makra neposredno iz Excelove orodne vrstice;

2. poiščite elemente obsega # 1, ki niso v obsegu # 2;

3. poiščite elemente obsega # 2, ki niso v obsegu # 1;

4. poiščite elemente obsega # 1, ki so v obsegu # 2;

5. poiščite elemente obsega # 2, ki so v obsegu # 1;

6. izberite eno od devetih barv polnila za celice z želenimi vrednostmi;

7. hitro izberite obsege z možnostjo "Omeji obsege", medtem ko lahko izberete celotne vrstice in stolpce, pri čemer se izbrani obseg na uporabljenega samodejno izvede;

8. namesto primerjave številskih vrednosti uporabite primerjavo besedilnih vrednosti z možnostjo »Primerjaj številke kot besedilo«;

9. primerjajte vrednosti v celicah obsega, ne da bi upoštevali odvečne presledke;

10. Primerjajte vrednosti v celicah obsega, tako da ne razlikuje med velikimi in malimi črkami.

Kako primerjati dva stolpca z uporabo makra (dodatka) za Excel?

Primerjava stolpcev je poseben primer primerjanja poljubnih obsegov. V obsegih št. 1 in št. 2 izberite dva stolpca in lahko natančno izberete stolpce in z miško ne povlečete izbirnega okvira preko obsegov s celicami (za udobje je privzeto omogočena možnost "Omeji obsege", izbira po uporabljenem obsegu), izberite zahtevano dejanje, da poiščete razlike ali ujemanja, izberite barvo zapolnitve celic in zaženite program. Spodaj si lahko ogledate rezultat iskanja ujemajočih se vrednosti v dveh stolpcih.

Sposobnost primerjave dveh naborov podatkov v Excelu je pogosto koristna za ljudi, ki obdelujejo velike količine podatkov in delajo z ogromnimi tabelami. Primer: primerjavo lahko uporabimo pri pravilnosti vnosa podatkov ali pravočasnega vnosa podatkov v tabelo. V spodnjem članku je opisanih več tehnik primerjave dveh stolpcev s podatki v Excelu.

Uporaba pogojnega operatorja IF

Metoda uporabe pogojnega operatorja IF se razlikuje po tem, da se za primerjavo dveh stolpcev uporablja le tisti del, ki je potreben za primerjavo, in ne celotnega polja. Spodaj so navedeni koraki za izvedbo ta metoda:

Postavite oba stolpca za primerjavo v stolpca A in B delovnega lista.

V celico C2 vnesite naslednjo formulo \u003d IF (ISERROR (ISKANJE (A2; $ B $ 2: $ B $ 11; 0)); ""; A2) in jo razširite na celico C11. Ta formula zaporedno išče vsak element iz stolpca A v stolpcu B in vrne vrednost elementa, če ga najdemo v stolpcu B.


Uporaba nadomestne formule VLOOKUP

Princip formule je podoben prejšnji metodi, razlika je v, namesto ISKANJU. Posebnost te metode je tudi zmožnost primerjave dveh vodoravnih nizov s formulo HLP.

Če želite primerjati dva stolpca s podatki v stolpcih A in B (podobno kot pri prejšnji metodi), vnesite naslednjo formulo \u003d VLOOKUP (A2; $ B $ 2: $ B $ 11; 1; 0) v celico C2 in jo povlecite v celica C11.


Ta formula skenira vsak element iz osnovnega polja v primerjanem polju in vrne njegovo vrednost, če je bila najdena v stolpcu B. V nasprotnem primeru bo Excel vrnil napako # N / A.

Uporaba makra VBA

Uporaba makrov za primerjavo dveh stolpcev poenoti postopek in skrajša čas priprave podatkov. Odločitev o tem, kateri rezultat primerjave bo prikazan, je popolnoma odvisna od vaše domišljije in spretnosti uporabe makrov. Spodaj je tehnika, objavljena na uradni Microsoftovi spletni strani.

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

Sub Find_Matches ()
Dim PrimerjajRange As Variant, x As Variant, y As Variant
"Nastavitev spremenljivke CompareRange na primerjano območje
Nastavite CompareRange \u003d Range ("B1: B11")
"Če je obseg, ki ga primerjamo, na drugem listu ali delovnem zvezku,
"uporabite naslednjo skladnjo
"Set CompareRange \u003d Workbooks (" Book2 "). _
"Delovni listi (" Sheet2 "). Obseg (" B1: B11 ")
"
"Primerjava vsakega elementa v izbranem obsegu z vsakim elementom
"spremenljivka CompareRange
Za vsak x v izboru
Za vsak y v CompareRange
Če je x \u003d y, je x.Offset (0, 2) \u003d x
Naslednji y
Naslednji x
Končaj pod

V tej kodi je spremenljivki CompareRange dodeljen obseg s primerjano matriko. Nato zažene zanko, ki se vrti skozi vsak element v izbranem obsegu in ga primerja z vsakim elementom v primerjanem obsegu. Če so bili najdeni elementi z enakimi vrednostmi, makro vnese vrednost elementa v stolpec C.

Če želite uporabiti makro, se vrnite na delovni list, izberite glavni obseg (v našem primeru so to celice A1: A11), pritisnite Alt + F8. V pogovornem oknu izberite makro Najti_Tekmein kliknite gumb za izvajanje.


Po izvedbi makra mora biti rezultat naslednji:

Uporaba dodatka Inquire

Izid

Preučili smo torej več načinov primerjave podatkov v Excelu, ki vam bodo pomagali rešiti nekatere analitične težave in olajšali iskanje podvojenih (ali edinstvenih) vrednosti.

Pri primerjavi več primerljivih elementov v Excelove preglednice, podatki so pogosto organizirani v stolpce, tako da je priročno primerjati značilnosti teh predmetov po vrsticah. Na primer modeli avtomobilov, telefoni, eksperimentalne in kontrolne skupine, številne trgovske verige itd. Vizualna analiza z velikim številom linij ne more biti zanesljiva. Funkcije VLOOKUP, INDEX, ISKANJE (VLOOKUP, INDEX, MATCH) so primerne za primerjavo podatkov med celicami in ne dajejo celotne slike. Kako ugotoviti, kako podobni so si stolpci na splošno? Ali so stolpci enaki?

Dodatek Stolpci zemljevidov omogoča preslikavo stolpcev in ogled celotne slike:

  • Primerjajte dva ali več stolpcev med seboj
  • Primerjajte stolpce z referenčnimi vrednostmi
  • Izračunajte natančen odstotek ujemanja
  • Rezultat predstavite v vizualni vrtilni tabeli

Video jezik: angleščina. Podnapisi: ruščina, angleščina. (Pozor: video se morda ne odraža najnovejše posodobitve... Uporabite spodnja navodila.)

V stolpec Excel 2016, 2013, 2010, 2007 dodajte »Ustrezni stolpci«

Primerno za: Microsoft Excel 2016 - 2007, namizni Office 365 (32-bitni in 64-bitni).

Kako delati z dodatkom:

Kako primerjati dva ali več stolpcev med seboj in izračunati odstotek ujemanja

Razmislite o primeru razvoja izdelka. Recimo, da morate primerjati več končnih prototipov med seboj in ugotoviti, kako podobni, drugačni in morda celo enaki so.

  1. V orodni vrstici XLTools kliknite gumb Stolpci zemljevidov\u003e Izberite drug drugemu stolpce.

  3. Kliknite V redu\u003e


Nasvet:
Prosim izberite zbirna tabela rezultat\u003e Kliknite ikono Express Analysis\u003e Uporabi "Color Scale".

Branje rezultata: prototipa tipa 1 in tip 3 sta skoraj enaka, stopnja ujemanja 99% pomeni, da je 99% njihovih parametrov v vrsticah enakih. Tip 2 in tip 4 sta si najmanj podobna - njihova parametra sovpadata le za 30%.

Kako primerjati stolpce z referenčnimi vrednostmi in izračunati stopnjo ujemanja

Razmislite o primeru razvoja izdelka. Recimo, da morate primerjati več končnih prototipov z določenim ciljnim standardom in izračunati tudi stopnjo, v kateri prototipi ustrezajo tem standardom.

  1. Izberite stolpce za primerjavo.
    Na primer stolpci s prototipnimi podatki.
  2. V orodni vrstici XLTools kliknite gumb Map Stolpci.
  3. Izberite Zemljevid do obsega referenčnih stolpcev\u003e Izberite referenčne stolpce.
    Na primer stolpci s standardi.
  4. Če je tako, označite "Stolpci vsebujejo naslove".
  5. Potrdite polje »Pokaži odstotek ujemanja«, da se ujemanje prikaže v odstotkih.
    V nasprotnem primeru se rezultat prikaže kot 1 (popolno ujemanje) ali 0 (brez ujemanja).
  6. Navedite, kam naj bo rezultat postavljen: na nov list ali na obstoječi list.
  7. Kliknite V redu\u003e Dokončaj, rezultat je prikazan v vrtilni tabeli.


Nasvet: Za lažjo razlago rezultata uporabite pogojno oblikovanje zanj:
Izberite vrtilno tabelo rezultata\u003e Kliknite ikono Hitra analiza\u003e Uporabi "Barvno lestvico".

Branje rezultata: Prototip tipa 2 je 99% skladen s standardom 2, tj. 99% njihovih parametrov v nizih je enakih. Izdelek 5 je najbližji standardu 3 - 96% njihovih parametrov je enakih. Hkrati pa izdelek 4 še zdaleč ne izpolnjuje nobenega od treh standardov. Zdaj lahko ugotovimo, koliko vsak prototip odstopa od ciljnih referenčnih vrednosti.

Pri katerih nalogah vam bo pomagal dodatek Match Columns?

Dodatek skenira celice po vrsticah in izračuna odstotek enakih vrednosti v stolpcih. Stolpci XLTools Match niso primerni za običajno primerjavo vrednosti v celicah - ni namenjen iskanju dvojnikov ali unikatnih vrednosti.

Dodatek Stolpci zemljevidov ima drugačen namen. Njo glavna naloga - ugotovite, kako na splošno so nabori podatkov (stolpci) podobni ali drugačni. Dodatek pomaga pri analizi velikih količin podatkov, kadar morate na primer pogledati širšo makro raven. odgovorite na naslednja vprašanja:

  • Kako podobni so rezultati eksperimentalnih skupin
  • Kako podobni so rezultati eksperimentalne in kontrolne skupine
  • Kako podobni / različni so številni izdelki iste kategorije
  • Kako blizu so KPI zaposlenih za načrtovane kazalnike?
  • Kako podobni so kazalniki več trgovin na drobno itd.


Priporočamo branje