Excelillä kuntopuntari

[Poye]

Monet ovat varmaan jo murtaneet ongelman, johon itse en keksi ratkaisua. Tavallisen sarjataulukon vielä osasin tehdä, mutta miten saisi excelillä tehtyä kuntopuntari tyyppisen sarjataulukon, vaikkapa viimeiset kuusiottelua, seuraavilla tiedoilla.

Sarake A: Päivämäärä
Sarake B: Kotijoukkue
Sarake C: Kotimaalit
Sarake D: -
Sarake E: Vierasmaalit
Sarake F: Vierasjoukkue

Edellä mainituissa sarakkeissa on siis noin 500 riviä sisältäen tämän kauden ottelut. Lisäksi on käytössä apusarakkeet H, I ja J, joihin tulee arvo 1 tai 0 sen mukaan päättyikö ottelu 1:een, X:iin vai 2:een. Idea olisi siis sellainen että taulukko päivittyisi mahdollisimman automaattisesti.

Sitten ois vielä toinen arvoitus. Miten saisi haettua joukkueen 6 viimeisintä ottelua samoilla lähtötiedoilla? Siis siten että johonkin soluun kirjottaisi joukkueen nimen ja viereiseen sarakkeeseen joku kaava hakisi 6 edellistä ottelua. Joo, kertokaa jos tehtävän annosta ei saa mitään selvää

[rensenbrink]

Jos tuo kuntopuntari määritellään kolme viimeisintä kotiottelua ja kolme viimeisintä vierasottelua, päästään yksinkertaisempaan malliin. Kotikoneessani ei ole exceliä, mistä syystä vetelen hatusta noita funktioita. Jos tekijät tulevat väärään järjestykseen, oikaise.

1. Ota käyttöön apusarake, johon yhdistät kotijoukkueen nimen ja ottelun numeron kahden merkin ("00") levyisenä. Esimerkiksi tietokannan ensimmäiselle riville: b2 & teksti("00"; laske.jos(b$2..b2; b2)) . Pelaamattomat ottelut eivät välttämättä tarvitse ottelunumeroa, mutta voit antaa niille ottelunumeroksi vaikka arvon 99.

2. Jos ottelulla on tulos, liitä se em. soluun jatkamalla kaavaa merkillä 2 (kotivoitto), 1 (tasapeli) tai 0 (vierasvoitto).

3. Tee vastaavat toimenpiteet vierasotteluille.

4. Kuntopuntariin seuran nimen lisäksi pelattujen kotiottelujen määrä ja pelattujen vierasottelujen määrä. Kotivoitot saat laske.jos-funktiolla asettamalla hakuarvoksi seurannimen, ottelunumeron ja tuloksen yhdistelmän. Esimerkiksi Arsenal, jolla 14 kotiottelua takana: laske.jos(Ottelut!$h$2..$h$500; "Arsenal142") + laske.jos(Ottelut!$h$2..$h$500; "Arsenal132") + laske.jos(Ottelut!$h$2..$h$500; "Arsenal122") . Vastaavalla tavalla joudut laskemaan tasapelit ja tappiot. Tehdyt ja päästetyt maalit joudut laskemaan summa.jos-funktiolla vastaavalla tavalla (voit ottaa käyttöön myös uuden apusarakkeen ilman ottelun lopputulosta, jolloin sinun ei tarvitse laskea yhteen maaleja voitetuista otteluista, tasapeleistä ja hävityistä otteluista). Hakuarvot on helpoin määrittää funktioilla, esim. b2 & teksti("00"; c2 - 2) & "2" .

Tästä käynee idea selväksi. Soluun kirjoitettavat kaavat on hyvä suunnitella mahdollisimman toimiviksi, jotta kopiointi on helppoa. Murheena on kuten taulukkolaskennassa yleensäkin virhealttius.

[pjl]

Tälläisin pistin privana Poyelle, mutta isketään tänneki jos jollain muullakin on käyttöä. Eli tälläisen funktion kun pudottaa halutussa taulukossa Tools => Macro => Visual Basic Editor kohdasta Insert Module valinnalla, niin pitäisi toimia seuraavasti.

Koodi: Valitse kaikki

Sarake A Sarake B Sarake C Sarake D
Suomi    Ruotsi      3        0

Eli jos tiedot ovat kuten yllä (toki rivejä paljon enempi) niin halutun joukkueen tiedot saa tuolla funktiolla siten (Insert => Function, löytyy UserDefined kohdasta nimellä LaskeSumma) että HakuArvo ikkunaan kerrotaan joukkueen nimi tai solu mistä nimi löytyy. Alue kohtaan kerrotaan mistä joukkueen nimeä pitää hakea. N kysyy kuinka monta viimeistä haluat hakea ja TulosOffset kohtaan montako saraketta oikealle pitää siirtyä, jotta tulee oikeat tulokset. Eli tässä tapauksessa 2.

Eli solussa kaava näyttää tälläiseltä:

=LaskeSumma(G5;A:A;3;2)

Haetaan solussa G5 olevaa joukkuetta koko A-sarakkeesta. 3 Viimeistä peliä ja tulokset ovat 2 saraketta oikealle eli sarakkeessa C.

Käyttö on helpompaa kuin sen selittäminen

Function LaskeSumma(HakuArvo As Range, Alue As Range, n As Integer, TulosOffset As Integer)

Dim Summa
Dim I As Integer

Summa = 0
I = 0

For Each solu In Alue
If solu.Value = HakuArvo.Value Then
I = I + 1
If I > Application.WorksheetFunction.CountIf(Alue, HakuArvo.Value) - n Then
Summa = Summa + solu.Offset(0, TulosOffset).Value
End If
End If
Next solu

LaskeSumma = Summa
End Function