Menetelmä 1. Kaavat
Aloitetaan lämmittelyä varten yksinkertaisimmalla vaihtoehdolla - kaavoilla. Jos syötteenä on pieni päivämäärän mukaan lajiteltu taulukko, tarvitsemme peruskaavan laskeaksemme juoksevan kokonaissumman erillisessä sarakkeessa:
Tärkein ominaisuus tässä on alueen hankala kiinnitys SUM-funktion sisällä – viittaus alueen alkuun tehdään absoluuttiseksi (dollarimerkeillä) ja loppuun – suhteelliseksi (ilman dollareita). Vastaavasti kun kaava kopioidaan koko sarakkeeseen, saadaan laajeneva alue, jonka summa lasketaan.
Tämän lähestymistavan haitat ovat ilmeiset:
- Taulukko on lajiteltava päivämäärän mukaan.
- Kun lisäät uusia rivejä datalla, kaavaa on laajennettava manuaalisesti.
Menetelmä 2. Pivot-taulukko
Tämä menetelmä on hieman monimutkaisempi, mutta paljon miellyttävämpi. Ja pahentaa vielä vakavampaa ongelmaa – 2000 rivin tietotaulukkoa, jossa ei ole lajittelua päivämääräsarakkeen mukaan, mutta toistoja esiintyy (eli voimme myydä useita kertoja samana päivänä):
Muunnamme alkuperäisen taulukkomme "älykkääksi" (dynaamiseksi) pikanäppäimeksi Ctrl+T tai joukkue Etusivu – Muotoile taulukoksi (Etusivu — Muotoile taulukkona), ja sitten rakennamme siihen pivot-taulukon komennolla Lisää – Pivot-taulukko (Lisää — Pivot-taulukko). Laitamme päivämäärän yhteenvedon rivialueelle ja myytyjen tavaroiden lukumäärän arvoalueelle:
Huomaa, että jos sinulla ei ole aivan vanha Excel-versio, päivämäärät ryhmitellään automaattisesti vuosien, vuosineljännesten ja kuukausien mukaan. Jos tarvitset toisenlaisen ryhmittelyn (tai et tarvitse sitä ollenkaan), voit korjata sen napsauttamalla hiiren kakkospainikkeella mitä tahansa päivämäärää ja valitsemalla komennot Ryhmittele / Pura ryhmä (Ryhmittele / Pura ryhmä).
Jos haluat nähdä sekä tuloksena saadut summat jaksoittain että juoksevan summan erillisessä sarakkeessa, on järkevää heittää kenttä arvoalueelle Myyty uudelleen saadaksesi kentän kaksoiskappaleen – siinä otamme käyttöön juoksevien kokonaismäärien näytön. Tee tämä napsauttamalla kenttää hiiren kakkospainikkeella ja valitsemalla komento Lisälaskelmat – kumulatiivinen summa (Näytä arvot muodossa – käynnissä olevat summat):
Siellä voit myös valita vaihtoehdon kokonaissumman kasvattamisesta prosentteina, ja seuraavassa ikkunassa sinun on valittava kenttä, johon kertyminen menee – meidän tapauksessamme tämä on päivämääräkenttä:
Tämän lähestymistavan edut:
- Suuri määrä tietoa luetaan nopeasti.
- Kaavoja ei tarvitse syöttää manuaalisesti.
- Lähdetietoja muutettaessa riittää, että päivität yhteenvedon hiiren oikealla painikkeella tai Data – Refresh All -komennolla.
Haitat johtuvat siitä, että tämä on yhteenveto, mikä tarkoittaa, että et voi tehdä siinä mitä haluat (lisätä rivejä, kirjoittaa kaavoja, rakentaa kaavioita jne.) ei enää toimi.
Tapa 3: Power Query
Ladataan "älykäs" taulukko lähdetiedoilla Power Queryn kyselyeditoriin komennolla Tiedot – taulukosta/alueesta (Tiedot – taulukosta/alueesta). Excelin uusimmissa versioissa se muuten nimettiin uudelleen - nyt sitä kutsutaan Lehtien kanssa (Luettelosta):
Sitten suoritamme seuraavat vaiheet:
1. Lajittele taulukko päivämääräsarakkeen mukaan nousevaan järjestykseen komennolla Lajittele nousevaan järjestykseen taulukon otsikon avattavassa suodatinluettelossa.
2. Hieman myöhemmin juoksevan summan laskemiseksi tarvitsemme apusarakkeen, jossa on järjestysrivin numero. Lisätään se komennolla Lisää sarake – hakemistosarake – 1:sta (Lisää sarake — Hakemistosarake — 1:sta).
3. Lisäksi tarvitsemme viittauksen sarakkeeseen juoksevan kokonaissumman laskemiseksi Myyty, jossa yhteenvetotietomme ovat. Power Queryssä sarakkeita kutsutaan myös listeiksi (listaksi) ja saadaksesi linkin siihen napsauta hiiren kakkospainikkeella sarakkeen otsikkoa ja valitse komento Detailing (Näytä yksityiskohdat). Tarvittava lauseke tulee näkyviin kaavapalkkiin, joka koostuu edellisen vaiheen nimestä #"Hakemisto lisätty", josta otamme taulukon ja sarakkeen nimen [Myynti] tästä taulukosta hakasulkeissa:
Kopioi tämä lauseke leikepöydälle myöhempää käyttöä varten.
4. Poista tarpeettomat viimeinen vaihe Myyty ja lisää sen sijaan laskettu sarake juoksevan summan laskemista varten komennolla Sarakkeen lisääminen – Mukautettu sarake (Lisää sarake — Muokattu sarake). Tarvittava kaava näyttää tältä:
Tässä toiminto List. Range ottaa alkuperäisen luettelon (sarake [Myynti]) ja poimii siitä elementit ensimmäisestä alkaen (kaavassa tämä on 0, koska Power Queryn numerointi alkaa nollasta). Haettavien elementtien määrä on sarakkeesta ottamamme rivinumero [Indeksi]. Joten tämä ensimmäisen rivin funktio palauttaa vain yhden sarakkeen ensimmäisen solun Myyty. Toiselle riville - jo kaksi ensimmäistä solua, kolmannelle - kolme ensimmäistä jne.
No sitten funktio List.Sum summaa erotetut arvot ja saamme jokaiselle riville kaikkien aiempien elementtien summan eli kumulatiivisen kokonaissumman:
Jäljelle jää enää tarpeettomien hakemistosarakkeiden poistaminen ja tulosten lataaminen takaisin Exceliin Home – Close & Load to -komennolla.
Ongelma on ratkaistu.
Nopea ja raivoissaan
Periaatteessa tämän olisi voitu pysäyttää, mutta pieni kärpänen voidessa on – luomamme pyyntö toimii kilpikonnan nopeudella. Esimerkiksi ei heikoimmalla PC:lläni vain 2000 rivin taulukko käsitellään 17 sekunnissa. Entä jos dataa on enemmän?
Nopeuttamiseksi voit käyttää puskurointia erityisellä List.Buffer-funktiolla, joka lataa sille argumenttina annetun listan (listan) RAM-muistiin, mikä nopeuttaa huomattavasti sen käyttöä tulevaisuudessa. Meidän tapauksessamme on järkevää puskuroida #”Lisätty indeksi”[Myydyt] -luettelo, jota Power Queryn on käytettävä laskeessaan juoksevaa summaa 2000 rivin taulukkomme jokaisella rivillä.
Voit tehdä tämän napsauttamalla Pää-välilehden Power Query -editorissa Advanced Editor -painiketta (Home – Advanced Editor) avataksesi kyselymme lähdekoodin Power Queryn sisäänrakennetulla M-kielellä:
Ja sitten lisää siihen rivi muuttujalla Minun listani, jonka arvon puskurointifunktio palauttaa, ja seuraavassa vaiheessa korvaamme kutsun listalle tällä muuttujalla:
Näiden muutosten jälkeen kyselystämme tulee huomattavasti nopeampi ja se selviää 2000 rivin taulukosta vain 0.3 sekunnissa!
Toinen asia, eikö? 🙂
- Pareto-kaavio (80/20) ja sen rakentaminen Excelissä
- Avainsanahaku tekstissä ja kyselypuskurointi Power Queryssä