Sisällys
Meillä on kaksi taulukkoa (esimerkiksi hinnaston vanha ja uusi versio), joita meidän on vertailtava ja löydettävä nopeasti erot:
On heti selvää, että jotain on lisätty uuteen hinnastoon (taatelit, valkosipuli…), jotain on kadonnut (karhunvatukat, vadelmat…), joidenkin tuotteiden hinnat ovat muuttuneet (viikunat, melonit…). Kaikki nämä muutokset on löydettävä ja näytettävä nopeasti.
Jokaiselle tehtävälle Excelissä on lähes aina useampi kuin yksi ratkaisu (yleensä 4-5). Ongelmaamme voidaan käyttää monia erilaisia lähestymistapoja:
- toiminto VPR (HAKU) — etsi tuotteiden nimet uudesta hinnastosta vanhasta ja näytä vanha hinta uuden vieressä ja huomaa sitten erot
- yhdistä kaksi listaa yhdeksi ja rakenna sen pohjalta pivot-taulukko, jossa erot näkyvät selvästi
- käytä Excelin Power Query -apuohjelmaa
Otetaan ne kaikki järjestyksessä.
Tapa 1. Taulukoiden vertailu VLOOKUP-funktiolla
Jos tämä upea ominaisuus ei ole sinulle täysin tuttu, katso ensin täältä ja lue tai katso siitä opetusvideo – säästä pari vuotta elämääsi.
Tyypillisesti tätä toimintoa käytetään tietojen hakemiseen taulukosta toiseen sovittamalla yhteen yleiset parametrit. Tässä tapauksessa käytämme sitä työntämään vanhat hinnat uuteen hintaan:
Ne tuotteet, joille #N/A-virhe paljastui, eivät ole vanhassa listassa, eli lisättiin. Myös hintamuutokset näkyvät selvästi.
Plussat tämä menetelmä: yksinkertainen ja selkeä, "genren klassikko", kuten he sanovat. Toimii missä tahansa Excel-versiossa.
MIINUKSET on myös siellä. Uuteen hinnastoon lisättyjen tuotteiden etsimiseksi sinun on tehtävä sama toimenpide päinvastaiseen suuntaan, eli nostettava uudet hinnat vanhaan hintaan VLOOKUPin avulla. Jos pöytien koot muuttuvat huomenna, kaavoja on muutettava. No, ja todella suurilla pöydillä (> 100 tuhatta riviä) kaikki tämä onnellisuus hidastuu kohtuullisesti.
Tapa 2: Taulukoiden vertailu pivotin avulla
Kopioidaan taulukot toistensa alle ja lisätään sarake hinnaston nimellä, jotta myöhemmin ymmärrät mistä listasta mikä rivi:
Nyt luomme luodun taulukon perusteella yhteenvedon Lisää – Pivot-taulukko (Lisää — Pivot-taulukko). Heitetään peltoa Tuotteet viivojen alueelle, kenttään Hinta sarakealueelle ja kentälle Цena alueelle:
Kuten näet, pivot-taulukko luo automaattisesti yleisen luettelon kaikista tuotteista vanhoista ja uusista hinnastoista (ei toistoja!) ja lajittelee tuotteet aakkosjärjestykseen. Näet selkeästi lisätyt tuotteet (niillä ei ole vanhaa hintaa), poistetut tuotteet (niillä ei ole uutta hintaa) ja mahdolliset hintamuutokset.
Tällaisen taulukon loppusummat eivät ole järkeviä, ja ne voidaan poistaa käytöstä välilehdellä Rakentaja – Loppusummat – Poista käytöstä riveille ja sarakkeille (Suunnittelu – Grand Totals).
Jos hinnat muuttuvat (mutta ei tavaroiden määrä!), riittää, että päivität luodun yhteenvedon napsauttamalla sitä hiiren oikealla painikkeella – virkistää.
Plussat: Tämä lähestymistapa on suuruusluokkaa nopeampi suurilla taulukoilla kuin VLOOKUP.
MIINUKSET: sinun on kopioitava tiedot manuaalisesti toistensa alle ja lisättävä sarake hinnaston nimellä. Jos pöytien koot muuttuvat, sinun on tehtävä kaikki uudelleen.
Tapa 3: Taulukoiden vertailu Power Queryn kanssa
Power Query on ilmainen Microsoft Excelin apuohjelma, jonka avulla voit ladata tietoja Exceliin melkein mistä tahansa lähteestä ja sitten muuttaa nämä tiedot haluamallasi tavalla. Excel 2016:ssa tämä apuohjelma on jo oletusarvoisesti sisäänrakennettu välilehdelle Päiväys (Data), ja Excel 2010-2013 varten sinun on ladattava se erikseen Microsoftin verkkosivustolta ja asennettava se - hanki uusi välilehti Tehokysely.
Ennen kuin hinnastomme ladataan Power Queryyn, ne on ensin muutettava älykkäiksi taulukoiksi. Voit tehdä tämän valitsemalla datavälin ja painamalla näppäimistön yhdistelmää Ctrl+T tai valitse nauhan välilehti Etusivu – Muotoile taulukoksi (Etusivu — Muotoile taulukkona). Luotujen taulukoiden nimet voidaan korjata välilehdellä Rakentaja (Jätän standardin Taulukko 1 и Taulukko 2, jotka saadaan oletuksena).
Lataa vanha hinta Power Queryyn -painikkeella Taulukosta/alueesta (taulukosta/alueelta) välilehdeltä Päiväys (Päivämäärä) tai välilehdeltä Tehokysely (riippuen Excelin versiosta). Latauksen jälkeen palaamme Power Querysta Exceliin komennolla Sulje ja lataa – Sulje ja lataa… (Sulje ja lataa – Sulje ja lataa…):
… ja valitse sitten näkyviin tulevasta ikkunasta Luo vain yhteys (Vain yhteys).
Toista sama uuden hinnaston kanssa.
Luodaan nyt kolmas kysely, joka yhdistää ja vertaa kahden edellisen tiedot. Voit tehdä tämän valitsemalla Excelissä välilehdeltä Tiedot – Hanki tiedot – Yhdistä pyynnöt – Yhdistä (Data – Hae tiedot – Yhdistä kyselyt – Yhdistä) tai paina painiketta Yhdistää (Yhdistää) kieleke Tehokysely.
Valitse liitosikkunan pudotusluetteloista taulukomme, valitse sarakkeet, joissa on tavaroiden nimet ja aseta alareunasta liitostapa – Täydellinen ulkoinen (täysi ulkoinen):
Napsauttamisen jälkeen OK tulee näkyviin kolmen sarakkeen taulukko, jossa kolmannessa sarakkeessa sinun on laajennettava sisäkkäisten taulukoiden sisältöä otsikon kaksoisnuolen avulla:
Tuloksena saamme molempien taulukoiden tietojen yhdistämisen:
On tietysti parempi nimetä otsikon sarakkeiden nimet uudelleen kaksoisnapsauttamalla ymmärrettävämpiä:
Ja nyt mielenkiintoisin. Siirry välilehteen Lisää sarake (Lisää sarake) ja napsauta painiketta Ehdollinen sarake (Ehdollinen sarake). Syötä sitten avautuvaan ikkunaan useita testiehtoja ja niitä vastaavia lähtöarvoja:
Jäljelle jää klikata OK ja lataa tuloksena saatu raportti Exceliin käyttämällä samaa painiketta sulje ja lataa (Sulje ja lataa) kieleke Koti (Koti):
Kauneus.
Lisäksi, jos hinnastoissa tapahtuu tulevaisuudessa muutoksia (rivejä lisätään tai poistetaan, hinnat muuttuvat jne.), riittää, että päivität pyyntömme pikanäppäimellä Ctrl+muut+F5 tai painikkeella Päivitä kaikki (Päivitä kaikki) kieleke Päiväys (Päivämäärä).
Plussat: Ehkä kaunein ja kätevin tapa. Toimii älykkäästi suurten pöytien kanssa. Ei vaadi manuaalisia muokkauksia taulukoiden kokoa muuttaessa.
MIINUKSET: Edellyttää Power Query -apuohjelman (Excel 2010–2013) tai Excel 2016:n asentamista. Lähdetietojen sarakkeiden nimiä ei saa muuttaa, muuten saamme virheilmoituksen "Saraketta ei löytynyt!" kun yrität päivittää kyselyä.
- Tietojen kerääminen kaikista Excel-tiedostoista tietyssä kansiossa Power Queryn avulla
- Kuinka löytää vastaavuuksia kahden luettelon välillä Excelissä
- Kahden luettelon yhdistäminen ilman kaksoiskappaleita