Sisu
Meil on kaks tabelit (näiteks hinnakirja vana ja uus versioon), mida peame võrdlema ja kiiresti erinevused leidma:
Kohe on näha, et uude hinnakirja on midagi lisandunud (datlid, küüslauk…), midagi on kadunud (murakaid, vaarikaid…), mõne kauba puhul on hinnad muutunud (viigimarjad, melonid…). Peate kõik need muudatused kiiresti üles leidma ja kuvama.
Iga Exceli ülesande jaoks on peaaegu alati rohkem kui üks lahendus (tavaliselt 4-5). Meie probleemi lahendamiseks saab kasutada mitmeid erinevaid lähenemisviise:
- funktsioon VPR (OTSING) — otsige vanast uuest hinnakirjast tootenimetusi ja kuvage uue kõrval vana hind ning märkige siis erinevused
- liita kaks loendit üheks ja seejärel koosta selle põhjal pivot-tabel, kus erinevused on selgelt nähtavad
- kasutage Exceli Power Query lisandmoodulit
Võtame need kõik järjekorras.
1. meetod. Tabelite võrdlemine funktsiooniga VLOOKUP
Kui sulle see imeline omadus täiesti võõras on, siis vaata esmalt siia ja loe või vaata selleteemalist videoõpetust – säästa endale paar aastat elu.
Tavaliselt kasutatakse seda funktsiooni andmete tõmbamiseks ühest tabelist teise, sobitades mõne ühise parameetri. Sel juhul kasutame seda vanade hindade nihutamiseks uude hinda:
Need tooted, mille puhul ilmnes viga #N/A, ei ole vanas nimekirjas ehk lisati. Ka hinnamuutused on selgelt näha.
Plusse see meetod: lihtne ja selge, "žanri klassika", nagu öeldakse. Töötab igas Exceli versioonis.
Miinused on ka seal. Uude hinnakirja lisatud toodete otsimiseks tuleb teha sama toiming vastupidises suunas ehk tõmmata VLOOKUPi abil uued hinnad vanale hinnale. Kui homme laudade suurused muutuvad, siis tuleb valemeid kohendada. Noh, ja tõesti suurtel laudadel (> 100 tuhat rida) aeglustub kogu see õnn korralikult.
2. meetod: tabelite võrdlemine pivoti abil
Kopeerime oma tabelid üksteise alla, lisades veeru hinnakirja nimega, et hiljem aru saada, millisest nimekirjast millise rea järgi:
Nüüd koostame loodud tabeli põhjal kokkuvõtte läbi Sisesta – PivotTable (Sisesta – Pivot Table). Viskame põldu Toode joonte alale, väljale Hind veeru alale ja väljale ЦENA vahemikku:
Nagu näete, genereerib pivot tabel automaatselt kõigi vanade ja uute hinnakirjade toodete üldnimekirja (ilma kordusteta!) ja sorteerib tooted tähestikulises järjekorras. Näete selgelt lisatud tooteid (neil pole vana hinda), eemaldatud tooteid (neil pole uut hinda) ja hinnamuutusi, kui neid on.
Sellises tabelis pole üldsummasid mõtet ja need saab vahekaardil keelata Konstruktor – üldsummad – keelake ridade ja veergude puhul (Disain – Grand Totals).
Kui hinnad muutuvad (aga mitte kauba kogus!), siis piisab lihtsalt loodud kokkuvõtte uuendamisest, tehes sellel paremklõpsu – värskendama.
Plusse: See lähenemine on suurte tabelite puhul suurusjärgu võrra kiirem kui VLOOKUP.
Miinused: tuleb käsitsi kopeerida andmed üksteise alla ja lisada veerg hinnakirja nimetusega. Kui laudade suurused muutuvad, siis tuleb kõik uuesti läbi teha.
3. meetod: tabelite võrdlemine Power Queryga
Power Query on Microsoft Exceli tasuta lisandmoodul, mis võimaldab laadida Excelisse andmeid peaaegu igast allikast ja seejärel neid soovitud viisil teisendada. Excel 2016-s on see lisandmoodul juba vaikimisi vahekaardile sisse ehitatud kuupäev (Andmed), ja Excel 2010-2013 jaoks peate selle Microsofti veebisaidilt eraldi alla laadima ja installima – hankige uus vahekaart Toite päring.
Enne meie hinnakirjade laadimist Power Querysse tuleb need esmalt nutikatabeliteks teisendada. Selleks valige andmetega vahemik ja vajutage klaviatuuril vastavat kombinatsiooni Ctrl+T või valige lindil vahekaart Avaleht – vorminda tabelina (Avaleht — vorminda tabelina). Loodud tabelite nimesid saab vahekaardil parandada Ehitaja (Ma jätan standardi Tabel 1 и Tabel 2, mis saadakse vaikimisi).
Laadige Power Querysse vana hind nupu abil Tabelist/vahemikust (Tabelist/vahemikust) vahekaardilt kuupäev (Kuupäev) või vahekaardilt Toite päring (olenevalt Exceli versioonist). Pärast laadimist naaseme Power Queryst käsuga Excelisse tagasi Sulgege ja laadige – sulgege ja laadige sisse… (Sule ja laadi – sulge ja laadi…):
… ja valige kuvatavas aknas Looge lihtsalt ühendus (Ainult ühendus).
Korrake sama uue hinnakirjaga.
Nüüd loome kolmanda päringu, mis ühendab ja võrdleb kahe eelmise andmeid. Selleks valige Excelis vahekaardil Andmed – hanki andmed – ühenda taotlused – ühenda (Andmed – Hangi andmed – Ühenda päringud – Ühenda) või vajutage nuppu Ühendama (Ühenda) tab Toite päring.
Liitumisaknas valige ripploenditest meie tabelid, valige veerud nendes olevate kaupade nimetustega ja allosas määrake liitmisviis – Täielik väline (Täielik välimine):
Pärast klõpsamist nupul OK peaks ilmuma kolmest veerust koosnev tabel, kus kolmandas veerus peate päises oleva topeltnoole abil laiendama pesastatud tabelite sisu:
Selle tulemusena saame mõlemast tabelist andmete liitmise:
Parem on muidugi päises olevad veergude nimed ümber nimetada, topeltklõpsates arusaadavamatel nimedel:
Ja nüüd kõige huvitavam. Mine vahekaardile Lisage veerg (Lisa veerg) ja klõpsake nuppu Tingimuslik veerg (tingimuslik veerg). Seejärel sisestage avanevas aknas mitu katsetingimust koos nende vastavate väljundväärtustega:
Jääb üle klõpsata OK ja laadige saadud aruanne sama nupu abil Excelisse sulgege ja laadige alla (Sule ja laadi) tab Avaleht (Kodu):
Ilu.
Veelgi enam, kui hinnakirjades peaks edaspidi toimuma muudatusi (ridu lisandub või kustutatakse, hinnad muutuvad jne), siis piisab meie soovide värskendamisest kiirklahviga. Ctrl+muu+F5 või nupuga Värskenda kõike (Värskenda kõike) tab kuupäev (Kuupäev).
Plusse: Võib-olla kõige ilusam ja mugavam viis üldse. Töötab nutikalt suurte laudadega. Ei vaja tabelite suuruse muutmisel käsitsi redigeerimist.
Miinused: nõuab Power Query lisandmooduli (Excel 2010–2013) või Excel 2016 installimist. Lähteandmetes veergude nimesid muuta ei tohi, vastasel juhul kuvatakse tõrge “Sellist ja sellist veergu ei leitud!” kui proovite päringut värskendada.
- Andmete kogumine kõigist antud kaustas olevatest Exceli failidest Power Query abil
- Kuidas leida Excelis vasteid kahe loendi vahel
- Kahe loendi ühendamine ilma duplikaatideta