Funkcija za upoređivanje dva stupca u excelu. Kako usporediti dva stupca u programu Excel - metode upoređivanja podataka u programu Excel
Instrukcije
Koristite ugrađenu funkciju uspoređivanja ćelija COUNTIF ako želite usporediti vrijednosti teksta u ćelijama stupca tablice s uzorkom teksta i ponovno izračunati sve što se podudara. Započnite popunjavanjem stupca s tekstualnim vrijednostima, a zatim u drugom stupcu kliknite ćeliju u kojoj želite vidjeti rezultat brojanja i unesite odgovarajuću formulu. Na primjer, ako su provjerene vrijednosti u stupcu A, a rezultat treba smjestiti u prvu ćeliju stupca C, tada bi njegov sadržaj trebao biti sljedeći: \u003d COUNTIF ($ A: $ A; "Grožđe") Ovdje "Grožđe" je vrijednost niza s kojom se uspoređuju vrijednosti svih ćelija u stupcu A. Možete preskočiti specificiranje u formuli, ali je smjestiti u zasebna ćelija (na primjer - u B1) i umetnite odgovarajuću vezu u formulu: \u003d COUNTIF ($ A: $ A; B1)
Koristite opcije uslovno formatiranjeako trebate vizualno istaknuti rezultat usporedbe varijabli niza u tablici. Na primjer, ako trebate odabrati ćelije u stupcu A, čiji se tekst podudara sa uzorkom u ćeliji B1, započnite odabirom ovog stupca - kliknite njegov naslov. Zatim kliknite gumb Uvjetno oblikovanje u naredbenoj grupi Stilovi na kartici Početna u izborniku Excel. Idite na odjeljak "Pravila za odabir ćelija" i odaberite redak "Jednako". U prozoru koji se otvori navedite uzorak ćelije (kliknite ćeliju B1) i s padajućeg popisa odaberite opciju za podudaranje redova. Zatim kliknite gumb "U redu".
Koristite kombinaciju ugrađenih funkcija IF i CONCATENATE kada trebate uskladiti više od jedne ćelije teksta s uzorkom. Funkcija CONCATENATE spaja vrijednosti koje navodi u jednu varijablu niza. Na primjer, naredba CONCATE (A1; "i"; B1) će dodati redak iz ćelije A1 u tekst "i", a nakon nje će smjestiti liniju iz ćelije B1. Niz konstruiran na ovaj način može se zatim podudarati sa uzorkom pomoću funkcije IF. Kada trebate usporediti više od jednog niza, prikladnije je dati vlastito ime ćeliji uzorka. Da biste to učinili, kliknite je i lijevo od žice formule, umjesto imena ćelije (na primjer, C1), upišite njegovo novo ime (na primjer, "sample"). Zatim kliknite ćeliju u kojoj bi trebao biti rezultat usporedbe i unesite formulu: IF (CONCATE (A1; "i"; B1) \u003d sample; 1; 0) Ovdje je jedinica vrijednost koju će ćelija s formulom sadrže ako će usporedba dati pozitivan rezultat, a nula za negativan rezultat. Pomnožite ovu formulu za sve žice Tabele koje treba usporediti s uzorkom vrlo su jednostavne - pomaknite kursor u donji desni kut ćelije i kada se kursor promijeni (postane crni križić) pritisnite lijevu tipku miša i povucite ovu ćeliju dolje do posljednje da se uporedi žice.
Ako radite sa proračunske tablice velikog obima (mnogo podataka / stupaca), vrlo je teško kontrolirati pouzdanost / relevantnost svih informacija. Stoga je vrlo često potrebno analizirati dva ili više stupaca u Excel dokumentu radi otkrivanja duplikata. A ako korisnik nema informacije o svim funkcionalnostima programa, logično može imati pitanje: kako usporediti dva stupca u Excelu?
Odgovor su odavno izmislili programeri ovog programa koji su u početku postavili naredbe u njemu kako bi pomogli u upoređivanju informacija. Generalno, ako uđete u dubinu ovog problema, možete pronaći desetak različitih načina, uključujući pisanje pojedinačnih makronaredbi i formula. Ali praksa pokazuje: dovoljno je znati tri ili četiri pouzdana načina za suočavanje s novim potrebama u usporedbi.
Kako usporediti dvije kolone u Excelu za podudarnosti
Podaci u Excelu obično se uspoređuju između redova, između stupaca, a vrijednost je navedena kao referenca. Ako trebate usporediti stupce, možete koristiti ugrađenu funkcionalnost, odnosno radnje "Podudaranje" i "Ako". A sve što vam treba je Excel ne ranije od sedme godine "izdanja".
Počinjemo s funkcijom "Match"". Na primjer, podaci koji se uspoređuju nalaze se u stupcima s adresama C3 i B3. Rezultat poređenja mora se staviti u okvir, na primjer, D3. Kliknemo na ovu ćeliju, uđemo u direktorij izbornika "formule", pronađemo redak "biblioteka funkcija", otvorimo funkcije smještene na padajućem popisu, pronađemo riječ "tekst" i kliknemo na "Podudaranje".
Za trenutak ćete na ekranu vidjeti novi obrazac, gdje će biti samo dva polja: "tekst jedan", "tekst dva". Moraju popuniti, samo adrese upoređenih stupaca (C3, B3), a zatim kliknite na poznato dugme "OK". Kao rezultat, vidjet ćete rezultat s riječima "Tačno" / "Netačno". U principu, ništa previše komplicirano čak ni za početnika! Ali ovo nije daleko od jedine metode. Pogledajmo funkciju If.
Mogućnost upoređivanja dva stupca u Excelu za podudaranja pomoću "Ako" omogućuje vam unos vrijednosti koje će se prikazati kao rezultat nakon operacije. Kursor je postavljen u ćeliju u koju će se izvršiti unos, otvorit će se direktorij izbornika "knjižnica funkcija", na padajućem popisu odabran je "logički" redak, a naredba "Ako" zauzeće prvo mjesto u njemu . Mi to biramo.
Slijedi oblik obrazloženog punjenja. "Log_Expression" je formulacija same funkcije. U našem slučaju ovo je usporedba dva stupca, pa unosimo "B3 \u003d C3" (ili adrese vaših stupaca). Daljnja polja "value_if true", "value_if_false". Ovdje morate unijeti podatke (oznake / riječi / brojevi), koji moraju odgovarati pozitivnom / negativnom rezultatu. Nakon punjenja pritisnemo, kao i obično, "ok". Upoznajmo se s rezultatom.
Ako trebate izvršiti linijsku analizu dvaju stupaca, u treći stupac stavljamo bilo koju funkciju o kojoj je ranije bilo riječi ("ako" ili "podudara se"). Proširujemo njegovo djelovanje na cijelu visinu ispunjenih stupaca. Zatim odaberite treći stupac, kliknite karticu "home", potražite riječ "stilovi" u grupi koja se pojavi. Otvorit će se "Pravila za odabir stupca / ćelije".
U njima trebate odabrati naredbu "jednak", zatim kliknuti na prvi stupac i kliknuti na "Enter". Kao rezultat, ispada da "nijansiraju" stupce u kojima postoje odgovarajući rezultati. I odmah ćete vidjeti potrebne informacije. Dalje, u analizi teme "kako upoređivati \u200b\u200bvrijednosti dva stupca u Excelu", prijeći ćemo na takvu metodu kao što je uvjetno formatiranje u Excelu.
Excel: Uvjetno oblikovanje
Uvjetno formatiranje omogućit će vam ne samo usporedbu dva različita stupca / ćelije / linije, već i označavanje različitih podataka u njima u zadanoj boji (crvena). Odnosno, ne tražimo slučajnosti, već razlike. Da bismo je dobili, ponašamo se ovako. Odaberite tražene stupce bez dodirivanja njihovih imena, idite u direktorij "glavnog" izbornika i u njemu pronađite pododjeljak "stilovi".
Sadržat će niz "uslovno formatiranje". Klikom na njega dobivamo listu na kojoj trebamo stavku-funkciju "kreiraj pravilo". Sljedeći korak: u liniji "format" morate voziti u formuli \u003d $ A2<>$ B2. Ova formula pomoći će Excelu da shvati šta nam tačno treba, naime da crvenom bojom obojimo sve vrijednosti u stupcu A koje nisu jednake vrijednostima u stupcu B. Nešto složeniji način primjene formula odnosi se na sudjelovanje konstrukcije poput HLOOKUP / VLOOKUP. Ove formule se odnose na horizontalno / vertikalno traženje vrijednosti. Razmotrimo ovu metodu detaljnije.
HLOOKUP i VLOOKUP
Ove dvije formule omogućuju vam pretraživanje podataka vodoravno / okomito. Odnosno, H je vodoravna, a V vertikalna. Ako se podaci za usporedbu nalaze u lijevom stupcu u odnosu na onaj u kojem se nalaze upoređene vrijednosti, upotrijebite konstrukciju VLOOKUP. Ali ako se podaci za usporedbu nalaze vodoravno na vrhu tablice iz stupca u kojem su naznačene referentne vrijednosti, koristimo konstrukciju HLOOKUP.
Da bismo razumjeli kako upoređivati \u200b\u200bpodatke u dva dijela excel kolone vertikalno, trebali biste ovo koristiti kompletna formula: lookup_value, table_array, col_index_num, range_lookup.
Vrijednost koju treba pronaći označena je kao "lookup_value". Stupci za pretraživanje vođeni su poput "niza tablice". Broj stupca trebao bi biti naveden kao "col_index_num". Štoviše, ovo je stupac čija se vrijednost podudarala i koji treba vratiti / ispraviti. Naredba pretraživanja dometa ovdje nije obavezna. Može naznačiti da li vrijednost treba biti precizna ili približna.
Ako ova naredba nije navedena, vrijednosti će se vratiti za obje vrste. Cijela HLOOKUP formula izgleda ovako: lookup_value, table_array, row_index_num, range_lookup. Rad s njim gotovo je identičan onome gore opisanom. Istina, postoji izuzetak. Ovo je indeks reda koji definira liniju čije vrijednosti treba vratiti. Ako naučite kako jasno primijeniti sve gore navedene metode, postaje jasno: ne postoji prikladniji i univerzalniji program za rad s velikom količinom podataka različitih vrsta od Excela. Međutim, uspoređivanje dvije kolone u excelu samo je pola posla. Napokon, sa dobijenim vrijednostima treba učiniti nešto drugo. Odnosno, pronađena podudaranja i dalje treba nekako obraditi.
Metoda rukovanja dvostrukim vrijednostima
Dakle, postoje pronađeni brojevi u prvom, pretpostavimo, stupcu, koji se u potpunosti ponavljaju u drugom stupcu. Jasno je da je ručno ispravljanje ponavljanja nestvaran posao koji oduzima puno dragocjenog vremena. Zbog toga biste trebali koristiti gotovu tehniku \u200b\u200bza automatsku korekciju.
Da bi ovo uspjelo, prvo trebate imenovati stupce ako ne postoje. Podesite kursor na prvi redak, kliknite desnim gumbom miša, u izborniku koji se pojavi odaberite "paste". Recimo da su naslovi "ime" i "duplikati". Dalje, potreban vam je direktorij "date", u njemu - "filter". Kliknite sićušnu strelicu pored "duplikata" i uklonite sve "ptice" sa popisa. Sada kliknite "ok", a dvostruke vrijednosti u stupcu A postaju vidljive.
Dalje, biramo ove ćelije, jer smo trebali ne samo da uporedimo tekst u dva stupca u Excelu, već i da uklonimo duplikate. Odabrali smo, kliknuli desnim klikom, odabrali „delete row“, kliknuli „ok“ i dobili tablicu bez podudarnih vrijednosti. Metoda djeluje ako su stupci na istoj stranici, odnosno susjedni su.
Stoga smo analizirali nekoliko načina za usporedbu dva stupca u Excel datoteci. Namjerno vam nisam pokazao snimke zaslona, \u200b\u200bjer biste se u njima zbunili.
ALI pripremio sam izvrstan video o jednom od najpopularnijih i jednostavni načini uspoređujući dva stupca u dokumentu i sada predlažem da se upoznate s njim kako biste objedinili pokriveni materijal.
Ako vam je članak i dalje bio koristan, podijelite ga na društvenim mrežama. mreže ili ocijenite klikom na broj zvjezdica koji smatrate prikladnim. Hvala, sve za danas, vidimo se uskoro.
© Aleksandar Ivanov.
Sposobnost upoređivanja dvaju skupova podataka u programu Excel često je korisna za ljude koji obrađuju velike količine podataka i rade s ogromnim tablicama. Na primjer, usporedba se može koristiti u ispravnosti unosa podataka ili unošenja podataka u tablicu na vrijeme. Članak u nastavku opisuje nekoliko tehnika za usporedbu dva stupca s podacima u programu Excel.
Korištenje IF uslovnog operatora
Metoda korištenja uvjetnog operatora IF razlikuje se po tome što se za usporedbu dva stupca koristi samo dio potreban za usporedbu, a ne i cijeli niz. Koraci za primenu ove metode opisani su u nastavku:
Smjestite oba stupca za usporedbu u stupce A i B radnog lista.
U ćeliju C2 unesite sljedeću formulu \u003d IF (ISERROR (PRETRAGA (A2; $ B $ 2: $ B $ 11; 0)); ""; A2) i proširite je na ćeliju C11. Ova formula uzastopno pretražuje svaku stavku iz stupca A u stupcu B i vraća vrijednost stavke ako je pronađena u stupcu B.
Koristeći formulu supstitucije VLOOKUP
Princip formule sličan je prethodnoj metodi, razlika je u, umjesto u PRETRAŽIVANJU. Karakteristična karakteristika ove metode je i mogućnost upoređivanja dva vodoravna niza pomoću HLP formule.
Za usporedbu dva stupca s podacima u stupcima A i B (slično prethodnoj metodi), u ćeliju C2 unesite sljedeću formulu \u003d VLOOKUP (A2; $ B $ 2: $ B $ 11; 1; 0) i povucite je ćelija C11.
Ova formula gleda svaki element iz osnovnog niza u upoređenom nizu i vraća njegovu vrijednost ako je pronađen u stupcu B. U suprotnom, Excel će vratiti grešku # N / A.
Korišćenje VBA makronaredbe
Korištenje makronaredbi za usporedbu dva stupca može objediniti proces i smanjiti vrijeme pripreme podataka. Odluka o tome koji rezultat prikazivanja treba prikazati u potpunosti ovisi o vašoj mašti i vještinama korištenja makronaredbi. Ispod je tehnika objavljena na službenoj Microsoftovoj web lokaciji.
1 | Sub Find_Matches () |
U ovom kodu, varijabli CompareRange dodjeljuje se raspon s upoređenim nizom. Zatim pokreće petlju koja petlja kroz svaku stavku u odabranom rasponu i uspoređuje je sa svakom stavkom u rasponu koji se uspoređuje. Ako su pronađeni elementi s istim vrijednostima, makronaredba unosi vrijednost elementa u stupac C.
Da biste koristili makronaredbu, vratite se na radni list, odaberite glavni raspon (u našem slučaju to su ćelije A1: A11), pritisnite Alt + F8. U dijaloškom okviru koji se pojavi odaberite makronaredbu Find_Mečevii kliknite gumb za izvršenje.
Nakon izvršavanja makronaredbe, rezultat bi trebao biti sljedeći:
Korištenje dodatka Inquire
Ishod
Dakle, pogledali smo nekoliko načina za usporedbu podataka u Excelu koji će vam pomoći riješiti neke analitičke probleme i olakšati pronalaženje dupliciranih (ili jedinstvenih) vrijednosti.
Imamo dvije tablice naloga kopirane u jedan radni list. Potrebno je usporediti podatke dviju tablica u Excelu i provjeriti koje su pozicije u prvoj tablici, ali ne i u drugoj. Nema smisla ručno uspoređivati \u200b\u200bvrijednost svake ćelije.
Uporedite dva stupca za podudaranja u Excelu
Kako izvršiti upoređivanje vrijednosti u Excelu iz dva stupca? Da biste riješili ovaj problem, preporučujemo upotrebu uvjetnog oblikovanja koje brzo može istaknuti stavke koje su u samo jednom stupcu u boji. Radni list sa tabelama:
Prvi korak je dodjeljivanje imena objema tablicama. Ovo olakšava razumijevanje raspona ćelija koje se uspoređuju:
- Odaberite alat FORMULAS - Definirana imena - Dodijeli ime.
- U prozor koji se prikaže, u polje "Name:" unesite vrijednost - Table_1.
- Kliknite lijevim klikom na polje za unos "Raspon:" i odaberite opseg: A2: A15. I kliknite OK.
Za drugu listu učinite isto, ali dodijelite ime - Tabela_2. I naznačite opseg C2: C15 - respektivno.
Korisni savjeti! Imena raspona mogu se brže dodijeliti pomoću polja imena. Nalazi se lijevo od trake formule. Samo odaberite opsege ćelija i u polje za ime upišite odgovarajuće ime za raspon i pritisnite Enter.
Sada upotrijebimo uslovno oblikovanje za usporedbu dvije liste u Excelu. Moramo dobiti sljedeći rezultat:
Pozicije u tablici_1, ali ne u tablici_2, bit će prikazane zelenom bojom. Istodobno, položaji smješteni u Tabeli_2, ali odsutni u Tablici_1, bit će označeni plavom bojom.
Princip upoređivanja podataka iz dva stupca u programu Excel
Pomoću funkcije COUNTIF definirali smo uvjete za formatiranje ćelija stupca. U ovom primjeru ova funkcija provjerava koliko se puta vrijednost drugog argumenta (na primjer, A2) pojavljuje na popisu prvog argumenta (na primjer, Tabela_2). Ako je broj puta \u003d 0, formula vraća TRUE. U ovom slučaju, ćeliji se dodjeljuje prilagođeni format naveden u uvjetnim opcijama formatiranja. Referenca u drugom argumentu je relativna, što znači da će se sve ćelije odabranog raspona provjeravati redom (na primjer, A2: A15). Druga formula djeluje na sličan način. Isti se princip može primijeniti na razne slične zadatke. Na primjer, da usporedimo dvije cijene u Excelu, čak