maanantai 3. helmikuuta 2014

Yleisökysymys: Yhteenvedon tekeminen - ratkaisu erääseen ongelmaan

Minulle pingattiin Twitterissä viikonloppuna ylläoleva Emmin kysymys. Pienen lisäselvittelyn jälkeen sain selville miten tieto on taulukoitu rakenteen puolesta ja mitä sillä halutaan tehdä.


Eli kuten kuvassa vieressä. Selvisi myös, että tuotteita on useampia ja jokaiselle tuotteelle on oma taulunsa omalla välilehdellä. Kuulostaa melko työläältä ylläpidettävältä minun korvaani. Kaikki kaavat pitää monistaa, rakenne säilyttää samana kaikissa, data-alueita muualta viittaavissa kaavoissa pitää riittävän suurena, ja ja ja...





Suosittelin kuitenkin SUMIFS (SUMMA.JOS.JOUKKO) -kaavaa, koska kysymys oli vain siitä miten saisi yhteenvedon tehtyä. Kerroin myös sivulauseessa, että kaavalle voisi "ampua" välilehden nimen, jos haluaa hifistellä, mutta esimerkin tasolle en mennyt. Blogi ei rajaa viestiä 140 merkkiin, joten hifistellään tässä. SUMIFS-kaava ottaa seuraavat argumentit:
  • Summattava alue: $C$3:$C$500
    • Tein siitä ylisuuren (riviin 500 saakka), jotta uusi lisättävä tieto mahtuisi mukaan.
    • Kaikkien tuotteiden välilehdet ovat rakenteeltaan saman alaisia, joten summattava aluekin on sama. Vain välilehden viittaus (normaalisti "Ruuvit!") vaihtuu
  • Ehtoalue: $A$3:$A$500
    • Taas ylisuuri alue
    • Haetaan viikkonumeroista
  • Ehto jonka mukaan summataan: B$1
    • Rivillä 1 on sarakeotsikkona juokseva viikkonumero
    • ehtopareja voi olla enintään 127 yhdessä kaavassa
SUMMA.JOS.JOUKKO -artikkeli Microsoftin Excel-tuessa.

Jos tuon kaavan kopioisi B2:sta oikealle, sekaviittaus B$1 huolehtisi, että viikkosumma menisi oikein. Sarakeviittaus muuttuu, riviviittaus pysyy dollarimerkin ansiosta. Alaspäin kopioitaessa kaava kuitenkin hajoaisi. Excel ei osaa automaattisesti hakea välilehtien nimiä tässä yhteydessä. Se antaisi kaikille viittauksen samalle välilehdelle.

Täytyy siis kikkailla. Minä käytän esimerkissä INDIRECT (EPÄSUORA), jolla voin rakentaa tuotenimellä A-sarakkeessa oman viittauksen jokaiselle välilehdelle. Välilehden nimi täytyy olla merkilleen sama kuin A-sarakkeessa oleva nimi. Nyt kaavan voi kopioida B2:sta myös alaspäin. Koska kaava on jos melko pitkä, olen rivittänyt sen kaavaikkunaan ALT+Enter näppäinkomennolla SUMIFS-kaavan argumenttien mukaisesti.

EPÄSUORA-artikkeli Microsoftin Excel-tuessa.


No pakottaa, mutta voisihan nuo ylimääräiset viikkonumerot "piilottaa" jollain ehdollisella muotoilulla, joka vaihtaa kirjasimen väriksi saman kuin taustan. Excel pystyy laskemaan tiedolla, mutta käyttäjälle se ei näkyisi. Valitse ensin kaikki viikkonumeron sisältävät solut A-sarakkeessa. Lisätään ehdollinen muotoilu kaavalla. Kaava =WEEKDAY(B3;2) laskee järjestysnumeron päivämäärälle solussa B3. Toinen argumentti, arvo 2, määrittelee, että maanantai on 1, tiistai 2, jne. Haluamme piiloon ne jotka eri kuin "< >" 1, joten lisäämme sen ehdoksi kaavan perään. Lopuksi määrittelemme väriksi valkoisen. Paina okokokok ja ylimääräiset viikkonumerot katoavat taianomaisesti.



Grande Finale

Ja sitten se tapa, jolla olisin itse tehnyt alusta alkaen. Tiedot datatauluun (tein taulusta dtTuotantomäärät -nimisen) sitä mukaa kun niitä tulee. Voivat olla missä järjestyksessä tahansa, kunhan oikea tieto on oikean sarakeotsikon alla. Lasketaan kaavalla =WEEKNUM([Tuotantopäivä];21) viikkonumero C-sarakkeeseen. Minulla on siis kaikki tieto yhdessä taulussa. Ei tuotekohtaisia tauluja erillisillä välilehdillä, ei datan eheysongelmia (ainakaan moneen kertaan), ei tarvitse välittää viikkonumeroiden piilottamisesta (näet kohta miksi), ei kikkailua INDIRECT-kaavalla, ei ylisuuria data-alueita kaavoissa, ei tyhjää kahvikuppia...


Lisätään uudelle välilehdelle pivot-taulu, syötetään tietolähteeksi dtTuotantomäärät, täytetään (tuotteet ja tuotantopäivät riveille, viikot sarakkeisiin), lisätään osittajat, muotoillaan kaikki ja avot! Huomaa, että pivot-taulukko ei toista samaa tietoa useaan kertaa (ellet nimenomaan halua). Viikkonumerot näkyvät sarakeotsikkoina vain kertaalleen.


Pieni plus-merkki tuotenimen vieressä tarkoittaa, että alla on lisää tietoa piilossa. Minä laitoin sinne päiväkohtaiset tuotantomäärät. Tuote-osittajalla olen alla lisäksi suodattanut pelkästään "Ruuvit" näkyviin.


Pivot-taulukot ovat niin mahtava ominaisuus Excelissä, että niistä on kirjoitettu monia, monia kirjoja. En siksi mene niihin nyt tässä artikkelissa syvemmälle. Muistinko jo mainita, että pivot-taulukot ovat mahtavia?

Ei kommentteja:

Lähetä kommentti