Laadin tänään erään erittelyn suositulla Excel-taulukkolaskentaohjelmalla. Olin listannut erittelyyn henkilöiden nimet ja heidän kustannuspaikat. Laskelmassa oli näiden tietojen lisäksi myös kustannukset, jotka jakautuivat eri kustannuspaikoille. Taulukon loppuun laitoin yhteenlasketut arvot, joita olivat henkilöiden määrä sekä kustannukset yhteensä. Nopeasti tarkasteltuna taulukko näytti helpolta ja yksinkertaiselta. Lukuarvojen lisäksi tarvittiin vain summausfunktiota, joka laski kustannusten yhteissumman. Henkilömäärän laskeminen sen sijaan ei ollut läpihuutojuttu. Osoittautui nimittäin, että erään henkilön työpanoksesta puolet kohdistui eri kustannuspaikalle. Toisin sanoen kyseinen henkilö työskenteli puolet ajastaan yhdelle ja toisen puolet toiselle kustannuspaikalle. Taulukkolaskentaohjelmassa olisi ollut helppoa laskea henkilömäärä käyttämällä esimerkiksi COUNTA()-funktiota, joka laskee rivien määrän, mikäli solussa on jokin arvo. Funktio ei kuitenkaan ota huomioon, jos taulukossa on henkilön nimi useaan kertaan. Alla olevassa taulukossa on yksinkertainen esimerkki taulukosta, jonka kimpussa tänään ahkeroin.

A-sarakkeessa on henkilön nimi ja B-sarakkeessa on hänen osuutensa eri kustannuspaikoissa. C-sarakkeessa on itse kustannus euroissa. Ensimmäisessä harjoitteessa käytin COUNTA()-funktiota, joka laskee yksinkertaisesti vain rivien määrän. Funktio ei ota huomioon, että Robert on taulukossa kahteen kertaan, koska hän työskentelee kahdessa eri projektissa. Ryhdyin hienosäätämään laskentaa ja vaihdoin COUNTA()-funktion SUMPRODUCT()-funktioon. Parannellussa taulukossa alla henkilömäärä on nyt laskettu oikein.

Mitenkään automaattisesti määrä ei kuitenkaan syntynyt. Käytin määrän laskennassa hyväkseni B-sarakkeessa olevaa suhdelukua sekä C-sarakkeessa olevaa arvoa. Määrä saadaan selville esimerkiksi siten, että muunnetaan C-sarakkeen arvo ykköseksi ja lasketaan sitten ykkösten määrä riveittäin. B-sarakkeen suhdeluvulla kuitenkin otetaan tästä ykkösestä vain osa. Kun suhdeluku on 100 %, saadaan arvoksi 1 ja suhdeluvun ollessa 50 %, saadaan arvoksi puolestaan 0,5. Taulukossa on kolme ykköstä, joiden yhteenlaskettu summa on siis 3. Lisäksi taulukossa on Robertin kaksi puolikasta, joiden summaksi tulee 1. Kun kaikki nämä luvut 3 + 1 lasketaan yhteen, saadaan lopulta määräksi 4, joka samalla kertoo henkilömäärän. Robert lasketaan yhdeksi henkilöksi, vaikka hän esiintyykin taulukossa kahteen kertaan.

Määrä lasketaan SUMPRODUCT()-funktiolla kertomalla B- ja C-sarakkeet keskenään ja summaamalla tulot. B-sarakkeen prosenttiluku toimii kertojana ja on 1 silloin, kun arvo on 100 % ja 0,5, kun arvo on 50 %. C-sarakkeen luku on kerrottava ja se pitää ensin muuntaa ykköseksi, jotta voidaan laskea rivien määrä oikein. Muunnos voidaan tehdä esimerkiksi vertailulla, jolla verrataan, onko lukuarvo suurempi kuin 0. Jos arvo on suurempi kuin nolla, antaa vertailu totuusarvon, joka tässä tapauksessa on TOSI. Taulukkolaskenta esittää totuusarvon numerolla 1. EPÄTOSI-arvo puolestaan esitetään numerolla 0. Koska C-sarakkeen kaikki arvot ovat nollaa suurempia, saadaan muunnoksen tuloksena arvoksi aina 1. Mikäli jokin arvo olisi ollut pienempi tai yhtä suuri kuin nolla tai jos arvo puuttuisi kokonaan, olisi totuusarvoksi tullut 0, joka antaa kertolaskun tuloksi niin ikään 0. Oheisessa taulukossa on esitetty lukuarvot sellaisina, kuin funktio niitä käyttää.

SUMPRODUCT()-funktion kaava kirjoitetaan henkilömäärä-riville C-sarakkeeseen seuraavasti:

Sulkujen sisällä olevan kertolaskun vasemmalla puolella on kertoja eli arvoalue B-sarakkeesta. Laskutoimituksen oikealla puolella on kerrottava luku. Sisäkkäisten sulkujen sisällä suoritetaan kuitenkin ensin vertailu, jossa C-sarakkeen arvoja verrataan nollaan ja tutkitaan, josko ne ovat nollaa suurempia. Jokaisen rivin tulo lasketaan yhteen eli 1 + 1 + 0,5 + 0,5 + 1, jolloin saadaan summaksi 4. Henkilömäärän olisi ehkä voinut laskea toisellakin tavalla, mutta tässä esittelemäni ratkaisu sopi tarkoitukseeni ja syntyi helposti. Vaikka nyt käytinkin Excel-ohjelmaa, pätevät nämä ratkaisumallit myös LibreOfficen taulukkolaskentaohjelmassa.