Küsimus kasutajalt

Tere!

Mul on üks ülesanne ja ma olen juba kolmandat päeva oma ajusid raputanud – ma ei tea, kuidas seda täita. Tabeleid on 2 (igas ca 500-600 rida), ühest tabelist tuleb võtta veerg toote nimetusega ja võrrelda seda teisest toote nimetusega ning kui tooted kattuvad, kopeerida ja kleepige väärtus tabelist 2 tabelisse 1. Segaselt seletatud , kuid arvan, et saate fotolt ülesandest aru ( u. : foto lõikas välja tsensuur, see on ikkagi isiklik teave).

Ette tänades. Andrei, Moskva.

Head päeva kõigile!

Kirjeldatud viitab üsna populaarsetele ülesannetele, mida on Exceli abil suhteliselt lihtne ja kiire lahendada. Kõik, mida pead tegema, on sisestada oma kaks tabelit programmi ja kasutada funktsiooni VLOOKUP. Lisateavet tema töö kohta allpool ...

Funktsiooni VLOOKUP kasutamise näide

Näitena võtsin kaks väikest silti, need on näidatud alloleval ekraanipildil. Esimeses tabelis (veerud A, B- toode ja hind) veerus andmed puuduvad B; teises täidetakse mõlemad veerud (toode ja hind). Nüüd tuleb mõlemas tabelis esimesed veerud üle kontrollida ja vaste leidmisel automaatselt kopeerida hind esimesse tabelisse. Tundub lihtne ülesanne...

Kuidas seda teha...

Asetage hiirekursor lahtrisse B2- see tähendab, et veeru esimesse lahtrisse, kus meil väärtust pole, ja kirjutage valem:

=VLOOKUP(A2,$E$1:$F$7,2,FALSE)

A2- esimese tabeli esimese veeru väärtus (mida otsime teise tabeli esimesest veerust);

$E$1:$F$7- täielikult valitud teine ​​tabel (millest tahame midagi leida ja kopeerida). Pöörake tähelepanu märgile "$" - see on vajalik, et valemi kopeerimisel valitud teise tabeli lahtrid ei muutuks;

2 - veeru number, kust väärtust kopeerime (pange tähele, et meie valitud teises tabelis on ainult 2 veergu. Kui oleks 3 veergu, siis saab väärtuse kopeerida 2. või 3. veerust);

VALETA- otsime täpset vastet (muidu asendatakse esimene sarnane, mis meile ilmselgelt ei sobi).

Tegelikult saate valmis valemit oma vajadustele kohandada, muutes seda veidi. Valemi tulemus on toodud alloleval pildil: hind leiti teisest tabelist ja sisestati automaatrežiimis. Kõik töötab!

Teiste tootenimede hinna määramiseks lihtsalt laiendage (kopeerige) valem teistesse lahtritesse. Näide allpool.

Pärast seda, nagu näete, võrreldakse tabelite esimesi veerge: ridadest, kus lahtri väärtused langevad kokku, kopeeritakse ja asendatakse vajalikud andmed. Üldiselt on selge, et lauad võivad olla palju suuremad!

Märge: Pean ütlema, et funktsioon VLOOKUP on arvutiressursside suhtes üsna nõudlik. Mõnel juhul võib liiga suure dokumendi puhul tabelite võrdlemine võtta üsna kaua aega. Nendel juhtudel tasub kaaluda kas teisi valemeid või täiesti erinevaid lahendusi (iga juhtum on individuaalne).

See on kõik, palju õnne!

Artikkel annab vastused järgmistele küsimustele:

• Kuidas võrrelda kahte tabelit Excelis?
• Kuidas võrrelda keerukaid tabeleid Excelis?
• Kuidas võrrelda tabeleid Excelis funktsiooni VLOOKUP() abil?
• Kuidas luua kordumatuid reaidentifikaatoreid, kui nende kordumatuse määrab algselt mitmes veerus olev väärtuste komplekt?
• Kuidas määrata valemite kopeerimisel valemite lahtrite väärtusi?

Suure teabehulgaga töötades võib kasutaja ees seisa olla näiteks kahe tabeli andmeallika võrdlemine. Andmete salvestamisel ühtsesse raamatupidamissüsteemi (näiteks 1C Enterprise’il põhinevad süsteemid, SQL andmebaase kasutavad süsteemid) saab andmete võrdlemiseks kasutada süsteemi või DBMS-i sisseehitatud võimalusi. Reeglina piisab selleks programmeerija kaasamisest, kes kirjutab päringu andmebaasi, või tarkvara aruandlusmehhanismi. Kogenud kasutaja, kellel on 1C- või SQL-päringute kirjutamise oskus, saab päringuga hakkama.

Probleemid algavad siis, kui on kiire vajadus täita andmete võrdlusülesanne ning programmeerija palkamine ja päringu või programmiaruande kirjutamine võib ületada ülesande lahendamiseks seatud tähtaegu. Teine sama levinud probleem on vajadus võrrelda erinevatest allikatest pärinevat teavet. Sel juhul kõlab programmeerija probleemi avaldus kahe süsteemi integreerimisena. Sellise probleemi lahendamine nõuab kõrgema kvalifikatsiooniga programmeerijat ja võtab ka rohkem aega kui ühes süsteemis arendamine.

Nende probleemide lahendamiseks on ideaalne viis andmete võrdlemiseks kasutada Microsoft Exceli tabeliredaktorit. Enamik levinumaid juhtimis- ja regulatiivseid raamatupidamissüsteeme toetavad Exceli vormingusse üleslaadimist. See ülesanne nõuab selle kontorikomplektiga töötamiseks ainult teatud kasutajakvalifikatsiooni ega vaja programmeerimisoskusi.

Vaatame näite abil lahendust Excelis tabelite võrdlemise probleemile. Meil on kaks tabelit korterite nimekirjadega. Laadige üles allikad - 1C Enterprise (ehitusarvestus) ja Exceli tabel (müügiarvestus). Tabelid paigutatakse Exceli töövihikus vastavalt esimesele ja teisele lehele.

Meie ülesanne on võrrelda neid loendeid aadresside järgi. Esimeses tabelis on kõik maja korterid. Teises tabelis on ainult müüdud korterid ja ostja nimi. Lõppeesmärgiks on iga korteri (nende korterite puhul, mis on müüdud) esimeses tabelis kuvada ostja nimi. Ülesande raskendab asjaolu, et igas tabelis on korteri aadressiks hoone aadress ja see koosneb mitmest väljast: 1) hoone (maja) aadress, 2) sektsioon (sissepääs), 3) korrus, 4) number korrus (näiteks 1 kuni 4) .

Kahe Exceli tabeli võrdlemiseks peame tagama, et mõlemas tabelis oleks iga rida tähistatud ühe väljaga, mitte neljaga. Sellise välja saate, kui kombineerite nelja aadressivälja väärtused funktsiooniga Concatenate(). Funktsiooni Concatenate() eesmärk on ühendada mitu tekstiväärtust üheks stringiks. Funktsiooni väärtused on loetletud eraldatuna sümboliga ";". Väärtused võivad olla kas lahtri aadressid või suvaline jutumärkides määratud tekst.

Samm 1. Sisestame esimese tabeli algusesse tühja veeru "A" ja kirjutame valem selle veeru lahtrisse esimese andmetega rea ​​vastas:
=CONCATENATE(B3;"-";C3;"-";D3;"-";E3)
Visuaalse tajumise hõlbustamiseks oleme ühendatavate lahtrite väärtuste vahele seadnud sümbolid "-".

2. samm. Kopeerime valemi veeru A järgmistesse lahtritesse.

4. samm. Exceli tabelite võrdlemiseks väärtuste järgi kasutage funktsiooni VLOOKUP(). Funktsiooni VLOOKUP() eesmärk on otsida väärtust tabeli kõige vasakpoolsemast veerust ja tagastada lahtri väärtus, mis asub sama rea ​​määratud veerus. Esimene parameeter on soovitud väärtus. Teine parameeter on tabel, millest väärtust otsitakse. Kolmas parameeter on veeru number, mille lahtrist leitud reas väärtus tagastatakse. Neljas parameeter on otsingu tüüp: false – täpne vaste, tõene – ligikaudne vaste. Kuna väljundinfo tuleks paigutada esimesse tabelisse (sellesse oli vaja lisada klientide nimed), siis kirjutame sinna valemi. Loome tabeli esimese andmerea vastas asuvas vabas veerus valemi:
=VLOOKUP(A3,Sheet2!$A$3:$F$10,6,FALSE)
Kui kopeerite valemeid, muudab Smart Excel automaatselt lahtri aadressi. Meie puhul muutub iga rea ​​otsitav väärtus: A3, A4 jne, kuid tabeli aadress, milles otsitakse, peab jääma muutumatuks. Selleks fikseerime tabeli aadressi parameetri lahtrid sümbolitega “$”. "Sheet2!A3:F10" asemel teeme "Sheet2!$A$3:$F$10".

Võib-olla seisavad kõik, kes Excelis andmetega töötavad, silmitsi küsimusega, kuidas võrrelda Exceli kahte veergu sarnasuste ja erinevuste osas. Selleks on mitu võimalust. Vaatame igaüks neist lähemalt.

Kuidas võrrelda Exceli kahte veergu ridade kaupa

Kahe andmeveeru võrdlemisel on sageli vaja võrrelda iga üksiku rea andmeid vastete või erinevuste leidmiseks. Sellise analüüsi saame teha funktsiooni abil. Vaatame allolevate näidete abil, kuidas see toimib.

Näide 1: kuidas võrrelda kahte veergu sama rea ​​vastete ja erinevuste jaoks

Exceli kahe veeru iga rea ​​andmete võrdlemiseks kirjutame lihtsa valemi. Valem tuleks sisestada külgneva veeru igale reale tabeli kõrvale, milles peamised andmed asuvad. Olles loonud valemi tabeli esimese rea jaoks, saame selle venitada/kopeerida ülejäänud ridadele.

Selleks, et kontrollida, kas sama rea ​​kaks veergu sisaldavad samu andmeid, vajame valemit:

=IF(A2=B2; “Sobivus”; “”)

Valem, mis määrab ühe rea kahe veeru andmete erinevused, näeb välja järgmine:

=IF(A2<>B2; "Ei sobi"; ")

Saame sobitada vaste ja erinevuse testi kahe veeru vahel ühele reale ühes valemis:

=IF(A2=B2; "Sobib"; "Ei sobi")

=IF(A2<>B2; "Ei sobi"; "Kokku sobima")

Näidisarvutuse tulemus võib välja näha selline:

Sama rea ​​kahe veeru andmete võrdlemiseks tõstutundlikul viisil kasutage valemit:

=IF(MATCH(A2,B2), "Sobimine", "Unikaalne"

Kuidas võrrelda mitut veergu vastete leidmiseks ühes Exceli reas

Excel võimaldab võrrelda andmeid sama rea ​​mitmes veerus, kasutades järgmisi kriteeriume.

• Otsige tabeli kõigist veergudest samade väärtustega read;
• Otsige tabeli kahest veerust samade väärtustega read;

Näide 1. Kuidas leida ühest reast vasteid tabeli mitmes veerus

Kujutagem ette, et meie tabel koosneb mitmest andmeveerust. Meie ülesanne on leida ridu, mille väärtused kattuvad kõigis veergudes. Excel ja funktsioonid aitavad meid selles. Sobivuse määramise valem on järgmine:

=IF(JA(A2=B2,A2=C2); " langeb kokku"; " ")

Kui meie tabelis on palju veerge, on funktsiooni lihtsam kasutada koos:

=IF(COUNTIF($A2:$C2,$A2)=3;"Sobivus";" ")

Valemis tähistab "5" tabeli veergude arvu, mille jaoks me valemi koostasime. Kui teie tabelis on rohkem või vähem veerge, peaks see väärtus olema võrdne veergude arvuga.

Näide 2: kuidas leida sama rea ​​vasteid tabeli mis tahes kahes veerus

Kujutagem ette, et meie ülesandeks on tuvastada mitmes veerus andmetega tabelist need read, kus andmed on samad või korduvad vähemalt kahes veerus. Funktsioonid ja aitavad meid selles. Kirjutame kolmest andmeverust koosneva tabeli valem:

=IF(VÕI(A2=B2,B2=C2,A2=C2);"Sobib";" ")

Juhtudel, kui meie tabelis on liiga palju veerge, on meie valem funktsiooniga väga suur, kuna selle parameetrites peame määrama tabeli iga veeru sobituskriteeriumid. Lihtsam viis on sel juhul kasutada .

=IF(COUNTIF(B2:D2,A2)+COUNTIF(C2:D2,B2)+(C2=D2)=0; "Unikaalne string"; "Pole kordumatu string"

=IF(COUNTIF($B:$B,$A5)=0; "Veerus B pole vasteid"; "Veerus B on vasteid"

See valem kontrollib väärtusi veerus B, et näha, kas need vastavad veeru A lahtritele.

Kui teie tabel koosneb kindlast arvust ridadest, saate määrata valemis selge vahemiku (näiteks $ B2: $ B10). See kiirendab valemit.

Kuidas võrrelda kahte Exceli veergu vastete leidmiseks ja neid värviliselt esile tõsta

Kui otsime Excelis vasteid kahe veeru vahel, peame võib-olla visualiseerima leitud vasted või andmete erinevused, kasutades näiteks värvide esiletõstmist. Lihtsaim viis värvide vastete ja erinevuste esiletõstmiseks on kasutada Excelis tingimusvormingut. Vaatame allolevate näidete abil, kuidas seda teha.

Värviliste vastete otsimine ja esiletõstmine Excelis mitmes veerus

Juhtudel, kui peame leidma vasteid mitmest veerust, vajame selleks:

• Valige veerud andmetega, milles soovite vasteid arvutada;
• Tööriistariba vahekaardil "Kodu" klõpsake menüükäsku "Tingimuslik vormindamine" -> "Lahte esiletõstmise reeglid" -> "Korduvad väärtused";
• Hüpikdialoogiboksis valige vasakpoolses ripploendis "Kordumine" ja parempoolses ripploendis valige, mis värviga korduvad väärtused esile tõstetakse. Klõpsake nuppu "OK":
• Pärast seda tõstetakse vasted valitud veerus esile:

Otsige ja tõstke Excelis esile sobivad read

Kahes või enamas veerus olevate andmetega sobivate lahtrite leidmine ja andmetega tervete ridade vastete otsimine on erinevad mõisted. Pange tähele kahte allolevat tabelit:

Ülaltoodud tabelid sisaldavad samu andmeid. Nende erinevus seisneb selles, et vasakpoolses näites otsisime sobivaid lahtreid ja paremalt terveid korduvaid ridu andmetega.

Vaatame, kuidas tabelist sobivaid ridu leida:

• Andmetega tabelist paremale loome abiveeru, milles iga andmetega rea ​​vastas paneme valemi, mis ühendab kõik tabelirea väärtused ühte lahtrisse:

=A2&B2&C2&D2

Abiveerus näete kombineeritud tabeliandmeid:

Nüüd tehke tabelis sobivate ridade määramiseks järgmised toimingud.

• Valige abiveerus andmetega ala (meie näites on see lahtrite vahemik E2:E15 );
• Tööriistariba vahekaardil "Kodu" klõpsake menüükäsku "Tingimuslik vormindamine" -> "Lahte esiletõstmise reeglid" -> "Korduvad väärtused";
• Hüpikdialoogiboksis valige vasakpoolses ripploendis "Kordumine" ja parempoolses ripploendis valige, mis värviga korduvad väärtused esile tõstetakse. Klõpsake nuppu "OK":
• Pärast seda tõstetakse valitud veerus esile topeltread:

Tabelite kujul esitatud teavet on palju mugavam analüüsida ja erinevates arvutustes kasutada, kuid kui on vaja võrrelda mitme sarnase tabeli andmeid, on seda kõike visuaalselt väga raske teha. Õige tarkvara saab sellises olukorras alati abiks olla ning järgmisena vaatame, kuidas võrrelda Excelis kahte tabelit erinevate analüüsimeetodite abil.

Kahjuks ei saa te Excelis ühe nupuvajutusega tabeleid võrrelda, pealegi võib juhtuda, et peate andmed kuidagi ette valmistama ja võrdlemiseks valemi kirjutama.

Sõltuvalt soovitud tulemusest valitakse tabelitest andmete võrdlemise meetod. Lihtsaim viis on võrrelda kahte näiliselt identset veergu, et tuvastada read, milles see erinevus veel eksisteerib. Sel viisil saate võrrelda nii arvväärtusi kui ka teksti.

Võrdleme kahte digitaalsete väärtuste veergu, milles on erinevus vaid mõnes lahtris. Kirjutades külgnevasse veergu lihtsa valemi, on kahe lahtri võrdsuse tingimus "=B3=C3", saame tulemuse "TÕSI", kui lahtrite sisu on sama, ja "VALE", kui lahtrite sisu on erinev. Venitades valemi kogu võrreldavate väärtuste veeru kõrgusele, on erineva lahtri leidmine väga lihtne.

Kui peate lihtsalt kontrollima veergude erinevuste olemasolu või puudumist, saate kasutada menüükäsku "Otsi ja valige", vahekaardil "Kodu". Selleks tuleb esmalt valida võrreldavad veerud ja seejärel valida vajalik menüüpunkt. Rippmenüüst peate valima "Select a group of cell..." ja valige kuvatavas aknas "erinevused ridade kaupa".

Järjestatud väärtuste erinevuste tingimuslik vormindamine
Soovi korral saab erinevatele lahtritele rakendada tingimusvormingut, täites lahtrit, muutes teksti värvi jne. Sel juhul peate valima üksuse "Tingimuslik vormindamine", mille ripploendist valime "Reeglite haldamine".

Valige reeglite halduris üksus "Loo reegel" ja reeglite loomisel valime . Nüüd saame määrata valemi "=$ B3<>$ C3" vormindatava lahtri määramiseks ja selle vormingu määramiseks nuppu klõpsates "Formaat".

Nüüd on meil lahtri valimise reegel, vormindamine on määratud ja võrreldavate lahtrite vahemik on määratletud. Pärast nupu vajutamist "OKEI", rakendatakse meie seatud reeglit.

Järjestamata väärtuste erinevuste võrdlemine ja vormindamine
Exceli tabelite võrdlemine ei piirdu ainult järjestatud väärtuste võrdlemisega. Mõnikord peate võrdlema segaväärtuste vahemikke, milles peate kindlaks tegema, kas üks väärtus sobib muude väärtuste vahemikku.

Näiteks on meil kahe veeruna vormindatud väärtuste komplekt ja teine ​​sama tüüpi väärtuste komplekt. Esimeses komplektis on meil kõik väärtused vahemikus 1 kuni 20 ja teises komplektis puuduvad mõned väärtused ja need dubleeritakse teiste väärtustega. Meie ülesanne on kasutada tingimusvormingut, et tõsta esile esimeses komplektis olevad väärtused, mida teises komplektis pole.

Protseduur on järgmine: valige esimene andmekogum, mida kutsume "Veerg 1" ja menüüs "Tingimuslik vormindamine" valige üksus "Loo reegel...". Valige kuvatavas aknas , sisestage vajalik valem "=COUNTIF($C$3:$D$12,A3)=0" ja valige vormindamismeetod.

Meie valem kasutab funktsiooni "COUNTIF", mis loendab, mitu korda teatud lahtri väärtust korratakse "A3" etteantud vahemikus "$ C$3:$D$12", mis on meie teine ​​veerg. Võrdluslahter peab olema esimene lahter väärtuste vahemikus, millele vormindatakse.

Pärast loodud reegli rakendamist tõstetakse määratud värviga esile kõik lahtrid, mille väärtused ei kordu teises väärtuste komplektis.

Muidugi on Excelis kahe tabeli võrdlemiseks keerulisemad võimalused, näiteks kaupade sentide võrdlemine uues ja vanas hinnakirjas. Oletame, et seal on kaks tabelit hindadega ja uues tabelis tuleb hindade kõrvale märkida iga toote vanad hinnad ning nimekirjades olevate toodete järjestust ei peeta kinni.

Uue tabeli hindade kõrvale järgmise veeru lahtrisse tuleb kirjutada valem, mis valib väärtused. Valemis kasutame funktsiooni "VPR", mis võib tagastada väärtuse mis tahes veerust reas, kus otsingutingimus oli täidetud. Funktsiooni korrektseks toimimiseks on vajalik, et iga rea ​​veerg sisaldaks unikaalseid väärtusi, mida otsitakse. Kui väärtusi korratakse, võetakse arvesse ainult esimene leitud.

Vajalik valem näeb välja selline: "=VLOOKUP(B18,$B$3:$C$10,2,FALSE)". Esimene väärtus "B18" vastab soovitud tootenime esimesele lahtrile. Teine tähendus "$B$3:$C$10" tähendab vana hinnatabeli vahemiku alalist aadressi, mille väärtusi meil vaja läheb. Kolmas tähendus "2" tähendab veeru numbrit valitud vahemikust, mille lahtrisse võtame toote vana hinna. Ja viimane tähendus "VALE" määrab otsingu ainult väärtuste täpse vaste järgi. Pärast valemi lohistamist üle kogu uue tabeli veeru saame sellesse veergu iga uues tabelis saadaoleva kauba vanad hinnaväärtused. Viimase toote nime vastas kuvab valem veateate "#N/A", mis viitab selle nime puudumisele vanas hinnakirjas.

Tabelite võrdlemiseks Excelis võib olla lugematu arv võimalusi ja mõnda neist saab teha ainult VBA lisandmooduli abil.

Meil on kaks tellimuste tabelit kopeeritud ühele tööle. Excelis on vaja võrrelda kahe tabeli andmeid ja kontrollida, millised positsioonid on esimeses tabelis, aga mitte teises. Iga lahtri väärtust pole mõtet käsitsi võrrelda.

Võrrelge Excelis vastete leidmiseks kahte veergu

Kuidas võrrelda Excelis kahe veeru väärtusi? Selle probleemi lahendamiseks soovitame kasutada tingimusvormingut, mis tõstab kiiresti esile ainult ühes veerus olevad üksused. Tööleht tabelitega:

Esimene samm on nimetada mõlemad tabelid. Nii on lihtsam mõista, milliseid lahtrivahemikke võrreldakse:

1. Valige tööriist VALEMID – Määratletud nimed – Määra nimi.
2. Sisestage ilmuvas aknas väljale „Nimi:” väärtus – Tabel_1.
3. Kasutage hiire vasakut nuppu, et klõpsata sisestusväljal "Range:" ja valida vahemik: A2:A15. Ja klõpsake nuppu OK.

Teise loendi jaoks tehke samad toimingud, andke sellele ainult nimi - Tabel_2. Ja määrake vastavalt vahemik C2:C15.

Abistav nõuanne! Vahemikunimesid saab kiiremini määrata, kasutades nimede välja. See asub valemiribast vasakul. Valige lihtsalt lahtrite vahemikud ja sisestage nimeväljale vahemiku sobiv nimi ja vajutage sisestusklahvi.

Nüüd kasutame tingimusvormingut kahe loendi võrdlemiseks Excelis. Peame saama järgmise tulemuse:



Üksused, mis on tabelis_1, kuid mitte tabelis_2, kuvatakse roheliselt. Samal ajal tõstetakse sinisega esile positsioonid, mis on tabelis_2, kuid mitte tabelis_1.

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

Veeru lahtrite vormindamise tingimuste määratlemisel kasutasime funktsiooni COUNTIF. Selles näites kontrollib see funktsioon, mitu korda kuvatakse teise argumendi väärtus (näiteks A2) esimese argumendi loendis (näiteks Tabel_2). Kui kordade arv = 0, tagastab valem TRUE. Sel juhul määratakse lahtrile tingimusvormingusuvandites määratud kohandatud vorming.

Teise argumendi link on suhteline, mis tähendab, et kõiki valitud vahemiku lahtreid kontrollitakse ükshaaval (näiteks A2:A15). Näiteks võrrelda kahte hinnakirja Excelis kasvõi erinevatel lehtedel. Teine valem töötab sarnaselt. Sama põhimõtet saab rakendada erinevate sarnaste ülesannete puhul.