vsebina
Imamo dve tabeli (na primer staro in novo različico cenika), ki ju moramo primerjati in hitro najti razlike:
Takoj je jasno, da je bilo v novem ceniku nekaj dodano (dateljni, česen …), nekaj izginilo (robide, maline …), nekatere vrste blaga so se spremenile cene (fige, melone …). Vse te spremembe morate hitro najti in prikazati.
Za vsako nalogo v Excelu je skoraj vedno več kot ena rešitev (običajno 4-5). Za naš problem lahko uporabimo veliko različnih pristopov:
- funkcija VPR (VLOOKUP) — poiščite imena izdelkov iz novega cenika v starem in prikažite staro ceno zraven nove, nato pa ujemite razlike
- združite dva seznama v enega in nato na podlagi tega sestavite vrtilno tabelo, kjer bodo razlike jasno vidne
- uporabite dodatek Power Query za Excel
Vzemimo jih vse po vrsti.
Metoda 1. Primerjava tabel s funkcijo VLOOKUP
Če še niste seznanjeni s to čudovito funkcijo, potem najprej poglejte sem in preberite ali si oglejte video vadnico o njej – prihranite si nekaj let življenja.
Običajno se ta funkcija uporablja za pridobivanje podatkov iz ene tabele v drugo z ujemanjem nekega skupnega parametra. V tem primeru ga bomo uporabili, da stare cene potisnemo v novo ceno:
Tisti izdelki, pri katerih se je izkazala napaka #N/A, niso na starem seznamu, tj. so bili dodani. Jasno so vidne tudi spremembe cen.
Prednosti ta metoda: preprosta in jasna, "klasika žanra", kot pravijo. Deluje v kateri koli različici Excela.
Proti je tudi tam. Če želite poiskati izdelke, ki so dodani v novi cenik, boste morali narediti enak postopek v obratni smeri, torej nove cene potegnite navzgor na staro ceno s pomočjo VLOOKUP-a. Če se velikosti tabel jutri spremenijo, bo treba prilagoditi formule. No, in na res velikih mizah (> 100 tisoč vrstic) se bo vsa ta sreča spodobno upočasnila.
2. način: Primerjava tabel z uporabo vrtilne točke
Kopirajmo naše tabele eno pod drugo in dodamo stolpec z imenom cenika, da boste kasneje razumeli, iz katerega seznama je katera vrstica:
Zdaj bomo na podlagi ustvarjene tabele ustvarili povzetek skozi Vstavi – vrtilna tabela (Vstavi — vrtilna tabela). Vrzimo polje Izdelek na območje črt, polje Cena na območje stolpca in polje ЦEna v obseg:
Kot lahko vidite, bo vrtilna tabela samodejno ustvarila splošen seznam vseh izdelkov iz starega in novega cenika (brez ponavljanja!) in izdelke razvrstila po abecedi. Jasno so vidni dodani izdelki (nimajo stare cene), odstranjeni izdelki (nimajo nove cene) in morebitne spremembe cen.
Skupni seštevki v takšni tabeli niso smiselni in jih lahko na zavihku onemogočite Konstruktor – Skupne vsote – Onemogoči za vrstice in stolpce (Oblikovanje — skupni seštevek).
Če se cene spremenijo (vendar ne količina blaga!), potem je dovolj, da preprosto posodobite ustvarjen povzetek z desnim klikom nanj – Osveži.
Prednosti: Ta pristop je za red velikosti hitrejši pri velikih tabelah kot VLOOKUP.
Proti: podatke morate ročno kopirati enega pod drugega in dodati stolpec z imenom cenika. Če se velikosti tabel spremenijo, morate vse narediti znova.
3. način: Primerjava tabel s Power Queryjem
Power Query je brezplačen dodatek za Microsoft Excel, ki omogoča nalaganje podatkov v Excel iz skoraj vseh virov in nato preoblikovanje teh podatkov na poljuben način. V Excelu 2016 je ta dodatek že privzeto vgrajen na zavihku datum (Podatki), in za Excel 2010-2013 ga morate prenesti ločeno z Microsoftovega spletnega mesta in namestiti – dobite nov zavihek poizvedba o moči.
Preden naše cenike naložimo v Power Query, jih moramo najprej pretvoriti v pametne tabele. Če želite to narediti, izberite obseg s podatki in pritisnite kombinacijo na tipkovnici Ctrl+T ali izberite zavihek na traku Domov – Oblikuj kot tabelo (Domov — Oblikuj kot tabelo). Imena izdelanih tabel lahko popravljamo na zavihku Konstruktor (Zapustil bom standard Tabela 1 и Tabela 2, ki so pridobljeni privzeto).
Z gumbom naložite staro ceno v Power Query Iz tabele/razpona (Iz tabele/razpona) iz zavihka datum (Datum) ali iz zavihka poizvedba o moči (odvisno od različice Excela). Po nalaganju se bomo iz Power Query z ukazom vrnili nazaj v Excel Zapri in naloži – Zapri in naloži v… (Zapri & naloži — Zapri in naloži v ...):
… in v oknu, ki se prikaže, izberite Samo ustvarite povezavo (Samo povezava).
Enako ponovite z novim cenikom.
Zdaj pa ustvarimo tretjo poizvedbo, ki bo združila in primerjala podatke iz prejšnjih dveh. Če želite to narediti, izberite v Excelu na zavihku Podatki – Pridobi podatke – Združi zahteve – Združi (Podatki — Pridobi podatke — Združi poizvedbe — Spoji) ali pritisnite gumb Združite (Združi) tab poizvedba o moči.
V oknu za združevanje na spustnih seznamih izberite naše tabele, izberite stolpce z imeni blaga v njih in na dnu nastavite način združevanja – Popolna zunanja (Popoln zunanji):
Po kliku OK prikaže se tabela treh stolpcev, kjer morate v tretjem stolpcu razširiti vsebino ugnezdenih tabel z dvojno puščico v glavi:
Kot rezultat dobimo združitev podatkov iz obeh tabel:
Seveda je bolje, da imena stolpcev v glavi preimenujete z dvojnim klikom na bolj razumljiva:
In zdaj najbolj zanimivo. Pojdite na zavihek Dodaj stolpec (Dodaj stolpec) in kliknite na gumb Pogojni stolpec (pogojni stolpec). Nato v okno, ki se odpre, vnesite več preskusnih pogojev z ustreznimi izhodnimi vrednostmi:
Ostaja še klik OK in z istim gumbom naložite nastalo poročilo v Excel zaprite in prenesite (Zapri in naloži) tab Domov (Domov):
Lepota.
Poleg tega, če bo v prihodnosti prišlo do kakršnih koli sprememb v cenikih (dodane ali izbrisane vrstice, spremembe cen itd.), potem bo dovolj, da posodobimo svoje zahteve samo z bližnjico na tipkovnici. Ctrl+druga+F5 ali z gumbom Osveži vse (Osveži vse) tab datum (Datum).
Prednosti: Morda najlepši in najbolj priročen način od vseh. Pametno deluje z velikimi mizami. Ne zahteva ročnega urejanja pri spreminjanju velikosti tabel.
Proti: Zahteva namestitev dodatka Power Query (v Excelu 2010-2013) ali Excel 2016. Imen stolpcev v izvornih podatkih ne smete spreminjati, sicer bomo dobili napako »Stolpec tak in tak ni bil najden!« ko poskušate posodobiti poizvedbo.
- Kako zbrati podatke iz vseh Excelovih datotek v določeni mapi s pomočjo Power Query
- Kako najti ujemanja med dvema seznamoma v Excelu
- Spajanje dveh seznamov brez dvojnikov