Pri primerjavi več primerljivih nepremičnin v Excel tabele, so podatki pogosto organizirani v stolpce, da je lažje primerjati značilnosti teh objektov vrstico za vrstico. Na primer modeli avtomobilov, telefoni, eksperimentalne in kontrolne skupine, številne trgovske verige itd. Pri velikem številu vrstic vizualna analiza ne more biti zanesljiva. funkcije VLOOKUP, INDEX, SEARCH (VLOOKUP, INDEX, MATCH) so priročni za primerjavo podatkov med celicami in ne zagotavljajo velika slika. Kako lahko ugotovite, kako na splošno so si stolpci podobni? Ali sta stolpca enaka?
Dodatek za ujemanje stolpcev vam omogoča, da ujemate stolpce in si ogledate celotno sliko:
- Primerjajte dva ali več stolpcev med seboj
- Primerjajte stolpce z referenčnimi vrednostmi
- Izračunajte odstotek natančnega ujemanja
- Predstavite rezultat v vizualni zbirni tabeli
Jezik videa: angleščina. Podnapisi: ruski, angleški. (Upoštevajte: videoposnetek morda ne odraža Zadnje posodobitve. Uporabite spodnja navodila.)
Dodajte »Ujemanje stolpcev« v Excel 2016, 2013, 2010, 2007
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
Poglejmo si primer razvoja izdelka. Recimo, da morate primerjati več dokončanih prototipov in ugotoviti, kako podobni, različni in morda celo enaki so.
- Kliknite gumb Ujemi stolpce na plošči XLTools > Izberite Ujemaj stolpce med seboj.
- Kliknite V redu >
Nasvet:
Izberite vrteča miza rezultat > Kliknite ikono za hitro analizo > Uporabi “Barvno lestvico”.
Branje rezultata: prototipa tipa 1 in tipa 3 sta skoraj enaka, 99-odstotna stopnja ujemanja pomeni, da je 99 % njunih parametrov v nizih enakih. Tip 2 in tip 4 sta najmanj podobna - njuni parametri se ujemajo le za 30%.
Kako primerjati stolpce z referenčnimi vrednostmi in izračunati ustreznost
Poglejmo si primer razvoja izdelka. Recimo, da morate primerjati več končnih prototipov z določenim ciljnim standardom in tudi izračunati stopnjo skladnosti prototipov s temi standardi.
- Izberite stolpce za primerjavo.
Na primer stolpci s prototipnimi podatki. - Kliknite gumb »Ujemaj stolpce« na plošči XLTools.
- Izberite Ujemanje z obsegom referenčnih stolpcev > Izberite stolpce referenčne vrednosti.
Na primer stolpci s standardi. - Če je temu tako, označite »Stolpci vsebujejo glave«.
- Označite »Prikaži odstotek ujemanja«, da prikažete stopnjo ujemanja v odstotkih.
V nasprotnem primeru bo rezultat prikazan kot 1 (polno ujemanje) ali 0 (ni ujemanja). - Določite, kam naj bo rezultat postavljen: na nov list ali na obstoječi list.
- Kliknite V redu > Dokončaj, rezultat je predstavljen v tabeli s povzetkom.
Nasvet: Za lažjo interpretacijo rezultata uporabite pogojno oblikovanje:
Izberite tabelo s povzetkom rezultatov > Kliknite ikono Express Analysis > Uporabi »Barvno lestvico«.
Branje rezultata: prototip tipa 2 je 99 % skladen s standardom 2, tj. 99% njihovih parametrov v vrsticah je enakih. Izdelek 5 je najbližji standardu 3 - 96% njihovih parametrov je enakih. Hkrati izdelek 4 še zdaleč ne izpolnjuje nobenega od treh standardov. Zdaj lahko sklepamo, koliko posamezni prototip odstopa od ciljnih referenčnih vrednosti.
Katere težave vam lahko pomaga rešiti dodatek »Match Columns«?
Dodatek pregleduje celice vrstico za vrstico in izračuna odstotek enakih vrednosti v stolpcih. XLTools Match Columns ni primeren za splošno primerjavo vrednosti v celicah - ni zasnovan za iskanje dvojnikov ali edinstvenih vrednosti.
Dodatek Match Columns ima drugačen namen. Njegova glavna naloga je ugotoviti, v čem so si na splošno nabori podatkov (stolpci) podobni ali različni. Dodatek pomaga pri analizi velikih količin podatkov, ko morate pogledati širše, na primer na makro ravni. odgovorite na naslednja vprašanja:
- Kako podobni so si kazalniki eksperimentalnih skupin?
- Kako podobni so si rezultati eksperimentalne in kontrolne skupine?
- Kako podobnih/različnih je več izdelkov iste kategorije?
- Kako blizu so kazalniki KPI zaposlenih načrtovanim?
- Kako podobni so kazalniki več maloprodajnih trgovin itd.
Navodila
Uporabite vgrajeno funkcijo primerjave celic COUNTIF, če morate primerjati besedilne vrednosti v celicah stolpca tabele z vzorčnim besedilom in ponovno izračunati vse ujemajoče se vrednosti. Začnite tako, da izpolnite stolpec z besedilnimi vrednostmi, nato pa v drugem stolpcu kliknite celico, v kateri želite videti rezultat štetja, in vnesite ustrezno formulo. Na primer, če so vrednosti, ki se preverjajo, v stolpcu A in je treba rezultat postaviti v prvo celico stolpca C, mora biti njegova vsebina naslednja: =COUNTIF($A:$A;"Grozdje") Tukaj je "Grozdje" vrednost niza, s katero se primerjajo vrednosti vseh celic stolpca A. Ne morete ga navesti v formuli, ampak ga postavite v ločeno celico (na primer v B1) in vstavite ustrezno. povezava v formulo: = COUNTIF($A:$A;B1)
Uporabite možnosti pogojno oblikovanje, če morate vizualno poudariti rezultat primerjave nizovnih spremenljivk v tabeli. Na primer, če želite izbrati celice v stolpcu A, katerih besedilo se ujema z vzorcem v celici B1, začnite z izbiro tega stolpca – kliknite njegovo glavo. Nato kliknite gumb Pogojno oblikovanje v skupini ukazov Slogi na zavihku Domov v Excelovem meniju. Pojdite v razdelek »Pravila za označevanje celic« in izberite vrstico »Enako«. V oknu, ki se odpre, izberite vzorčno celico (kliknite celico B1) in na spustnem seznamu izberite možnost oblikovanja za ujemajoče se vrstice. Nato kliknite gumb "V redu".
Uporabite kombinacijo vgrajenih funkcij IF in CONCATENATE, če ne želite primerjati samo ene besedilne celice, ampak več, z vzorcem. Funkcija CONCATENATE združi vrednosti, ki so ji podane, v eno spremenljivko niza. Na primer, ukaz CONCATENATE(A1;" in ";B1) bo nizu iz celice A1 dodal besedilo “in” in za njim postavil niz iz celice B1. Tako sestavljen niz lahko nato primerjamo z vzorcem s funkcijo IF. Ko morate primerjati več kot eno vrstico, je bolj priročno, da vzorčni celici daste svoje ime. Če želite to narediti, kliknite nanj in na levi strani vrstice formule namesto oznake celice (na primer C1) vnesite njeno novo ime (na primer »vzorec«). Nato kliknite celico, v kateri naj bo rezultat primerjave, in vnesite formulo: IF(CONCATENATE(A1;" and ";B1)=sample;1;0)Tukaj je enota vrednost, ki jo bo vsebovala celica s formulo, če Primerjava bo dala pozitiven rezultat, nič pa negativen rezultat. Pomnožite to formulo z vsemi vrstice tabele, ki jih je treba primerjati z vzorcem, so zelo enostavne - s kazalcem se pomaknite na spodnji desni kot celice in ko se kazalec spremeni (postane črn križ), pritisnite levi gumb miške in povlecite to celico navzdol do zadnje se primerja vrstice.
To je poglavje iz knjige: Michael Girvin. Ctrl+Shift+Enter. Obvladovanje matričnih formul v Excelu.
Vzorci na podlagi enega ali več pogojev. Vrsti Excelove funkcije uporabite primerjalne operatorje. Na primer SUMIF, SUMIFS, COUNTIFS, COUNTIFS, AVERAGEIF in AVERAGEIFS. Te funkcije izbirajo na podlagi enega ali več pogojev (meril). Težava je v tem, da lahko te funkcije samo seštevajo, štejejo in povprečijo. Kaj pa, če želite za iskanje določiti pogoje, na primer največjo vrednost ali standardno odstopanje? 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 glede na en pogoj.
Uporabimo funkcijo IF za izbiro elementov polja, ki izpolnjujejo pogoj. Na sl. 4.1 v levi tabeli sta stolpec z imeni mest in stolpec s časom. Za vsako mesto morate najti minimalni čas in to vrednost postaviti v ustrezno celico desne tabele. Pogoj za vzorčenje je ime mesta. Če uporabite funkcijo MIN, lahko najdete najmanjšo vrednost stolpca B. Kako pa izberete samo tiste številke, ki so specifične za Auckland? In kako kopirate formule navzdol po stolpcu? Ker Excel nima vgrajene funkcije MINESLI, boste morali napisati izvirno formulo, ki združuje funkciji IF in MIN.
riž. 4.1. Namen formule: izberite minimalni čas za vsako mesto
Prenesite opombo v obliki ali obliki
Kot je prikazano na sl. 4.2, bi morali začeti vnašati formulo v celico E3 s funkcijo MIN. Ampak ne moreš se prepirati številka 1 vse vrednosti stolpca B!? Želite izbrati samo tiste vrednosti, ki so specifične za Auckland.
Kot je prikazano na sl. 4.3, v naslednjem koraku vnesite funkcijo IF kot argument številka 1 za MIN. Vstavite IF znotraj MIN.
S postavitvijo kazalca na mesto vnosa argumenta log_expression Funkcija IF (slika 4.4), izberete obseg z imeni mest A3:A8 in nato pritisnete F4, da postanejo sklice na celice absolutne (za več podrobnosti glejte, na primer). Nato vnesete primerjalni operator, znak enačaja. Nazadnje boste izbrali celico levo od formule – D3, pri čemer boste sklic nanjo pustili relativno. Formulirani pogoj vam bo omogočil, da izberete samo Auckland, ko si ogledujete obseg A3:A8.
riž. 4.4. V argumentu ustvarite matrični operator log_expression IF funkcije
Torej ste ustvarili matrični operator z uporabo primerjalnega operatorja. Kadar koli med obdelavo matrike je operator matrike primerjalni operator, zato bo njegov rezultat matrika, sestavljena iz vrednosti TRUE in FALSE. Če želite to preveriti, izberite matriko (za to kliknite argument v opisu orodja log_expression) in pritisnite F9 (slika 4.5). Običajno uporabite en argument log_expression, vrne TRUE ali FALSE; tukaj bo nastala matrika vrnila več vrednosti TRUE in FALSE, zato bo funkcija MIN izbrala najmanjše število samo za tista mesta, ki se ujemajo z vrednostjo TRUE.
riž. 4.5. Če si želite ogledati matriko, sestavljeno iz vrednosti TRUE in FALSE, kliknite argument v opisu orodja log_expression in pritisnite F9
V en delovni list imamo kopirani dve tabeli naročil. V Excelu je treba primerjati podatke iz dveh tabel in preveriti, katera mesta so v prvi tabeli, v drugi pa ne. Nima smisla ročno primerjati vrednosti vsake celice.
Primerjajte dva stolpca za ujemanja v Excelu
Kako primerjati vrednosti v Excelu dveh stolpcev? Za rešitev te težave priporočamo uporabo pogojnega oblikovanja, ki hitro označi elemente, ki so v samo enem stolpcu. Delovni list s tabelami:
Prvi korak je poimenovanje obeh tabel. Tako lažje razumemo, kateri obsegi celic se primerjajo:
- Izberite orodje FORMULE – Definirana imena – Dodeli ime.
- V oknu, ki se prikaže, v polje »Ime:« vnesemo vrednost – Tabela_1.
- Z levim gumbom miške kliknite na vnosno polje “Obseg:” in izberite obseg: A2:A15. In kliknite OK.
Za drugi seznam izvedite enake korake, le dajte mu ime – Tabela_2. In določite obseg C2:C15 - oz.
Koristen nasvet!
Zdaj pa uporabimo pogojno oblikovanje za primerjavo dveh seznamov v Excelu. Dobiti moramo naslednji rezultat:
Prikazani bodo elementi, ki so v tabeli_1, ne pa v tabeli_2 zelena. Hkrati bodo modro označene pozicije, ki so v Tabeli_2, ne pa v Tabeli_1.
Načelo primerjave podatkov med dvema stolpcema v Excelu
Pri definiranju pogojev za oblikovanje celic stolpcev smo uporabili funkcijo COUNTIF. IN 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 = 0, potem formula vrne TRUE. V tem primeru je celici dodeljena oblika po meri, določena v možnostih pogojnega oblikovanja. Povezava v drugem argumentu je relativna, kar pomeni, da bodo vse celice izbranega obsega preverjene ena za drugo (na primer A2:A15). Druga formula deluje podobno. Enako načelo je mogoče uporabiti za različne podobne naloge. Na primer za primerjavo dveh cenikov v Excelu, celo
