Keskustelu: Yleinen keskustelu: Excel ongelma, hiihtokisat ja sijoituksen reaaliaikaisuus

Hei! Minulla on pienimuotoinen ongelma, jonka sain kiertämällä kuitenkin ratkaistua..

Eli:

Hiihtokilpailussa on joitakin osallistujia

1 Antti
2 Matti
3 Kaarlo
4 Juha
5 Timo
6 Simo

Nimet ovat ilmoittautumisjärjestyksessä, josta numero syntyy.

Porukat laitetaan hiihtämään ja tipotellen tulee aikoja (lähtöjärjestys on eri kuin ilmoittautuminen, se arvotaan). Esimerkkimäärä on pieni, osallistujia voi olla vaikka 100.

Kuinka saisi pidettyä "Tulostaulua", joka pysyy ajan tasalla sitä mukaan kun aikoja saadaan mitattua ja taulukkoon syötettyä (tai suoraan ajanottokellosta)?

Elikkä joukko hiihtäjiä, mutta siten, että aina ensimmäiseksi menee se jolla on pienin aika kierrokselta ja lopussa lista, jossa kaikki ovat nopeusjärjestyksessä?

Tulostaululla näkyy siten:
Osallistujan nimi, ehkä numero, aika ja sijanumero ajan perusteella.

Miten toteuttaisitte excelillä, taulukossa suoraan vai luomalla funktion ja jos, niin millaisen?

Helppoa olisi toki tehdä nappi, joka leikkaa ja liimaa, sekä sorttaa luettelon, mutta entä jos se ei ole sallittua, vaan toteutus pitäisi tehdä joko funktiolla soluun/soluihin tai excelin omilla funktioilla suoraan taulukossa..

Onnistuu suht helposti käyttäen SMALL- ja MATCH-funktioita. Jos kilpailijoiden tiedot on merkitty sarakkeisiin A:C (numero, nimi, aika) niin aikatulossarakkeeseen D saadaan ajat järjestyksessä SMALL-funktiolla ja aikaa vastaava nimi/numero MATCH-haulla.

kuinka kirjoittaisit ensimmäisen solun kaavan ko. funktioilla?

A-C jos on kisaajat ja D olisi sija koko joukosta?

Ensimmäisen saan minäkin poimittua, mutta entä seuraava ja sitä seuraava jne..?

Jos ajat ovat alueella C1:C6, seuraava kaava omaan sarakkeeseen laitettuna antaa tiedon, monesko rivi sisältää kyseisen sijoituksen:

=MATCH(SMALL(C$1:C$6;ROW(A1));C$1:C$6;0)

Jos tämä tieto laitetaan sitten sarakkeeseen F, seuraavilla kaavoilla saadaan kilpailijoiden nimet (B) ja ajat (C) kopioitua oikeassa järjestyksissä uusiin sarakkeisiin:

=INDEX(B$1:B$6;F1)
=INDEX(C$1:C$6;F1)

Ajattomat henkilöt jäävät listasta pois. Tähän on kaksi ratkaisutapaa: (a) IFERROR-suodatuksella piilotetaan virheet, (b) aikasarakkeesta tehdään ensin apusarake, jossa ilman aikaa oleville laitetaan jokin suuri ”aika” vain järjestämistä varten.

Lisäksi tasatilanteet jäävät tässä käsittelemättä, eli tuloslistaan tulee tasatilanteessa sama henkilö kahteen kertaan ja toinen jää pois. Tämänkin voi ratkaista (b)-kohdan apusarakkeella, eli lisätään jokaisen aikaan vaikka kilpailunumeron mukaan sekunnin miljoonasosia, jolloin ei pääse tulemaan täysin tasapeliä.

Ilmeisesti uusimmassa Excelissä on myös valmis SORT-funktio, jolla ongelma ratkeaa suoraan, mutta tätä ei ole esimerkiksi LibreOfficessa, joten en tiedä tarkemmin.

Kiitoksia, apusarakkeilla tuon jouduin itse ratkaisemaan, koska minulla on niin vanha excel, ettei se tunne small-funktiota. Joudun siten käyttämään min-funktiota ja karsia joukosta aina pienimmän pois ja jatkamaan seuraavaan apusarakkeeseen, josta taas valitaan pienin.. eli jos 10 henkilön tulostaulu, niin 20 saraketta kuluu piilossa. Tasapeliin auttaa kun kisatulokset ovat tarkkoja, eli sadasosat mukaan, mutta hyvä idea on antaa niitä laskemalla (kuten ehdotit). Lopullinen tulos kaikkien sijoituksista julkaistaan sorttaamalla, jolloin tasapelit tulevat mukaan, sitä listaa ei tarvitse reaaliaikaisesti päivitellä.

kiitos Teille!

Tätähän voisi kivasti jatkaa miettimällä kuinka kirjoitetaan small-funktio..

Kävipä hassusti, että tuo funktio sittenkin löytyi myös minun excel-versiosta. Luulin nuo kaikki tutkineeni, mutta jotain noinkin olennaista on jäänyt huomaamatta. Oli siis erittäin hyvä kun tätä lähdin kyselemään, opin jotain uutta vanhasta!

Itse asiassa SMALL ja MATCH voidaan korvata yksinkertaisesti COUNTIF-funktiolla, jolla voidaan siis laskea, montako pienempää tulosta löytyy eli mikä on kyseisen kilpailijan sijoitus. Tästä saadaan palautettua VLOOKUP-funktiolla vastaava rivinumero ja sen perusteella taas INDEX-funktiolla ne vastaavat tiedot osallistujalistasta.

Sarakkeet ovat siis:

A  (numero)
B  (nimi)
C  (aika)
D  =IF(C1; C1; 999,99999) + ROW()/86400/100/1000
E  =COUNTIF(D$1:D$6; "<" & D1) + 1
F  =ROW()
G  =VLOOKUP(ROW(); E$1:F$6; 2; 0)
H  =INDEX(B$1:B$6;G1)
I  =INDEX(C$1:C$6;G1)

Tasapelit saa tulostaululle kätevästi kun vertailee ajan perässä esim, onko samoja aikoja päällekkäisillä riveillä. Onnistuu juurikin kun poimitaan alkuperäisiä aikoja, ei noita jotka ottaa kaikki osallistujat mukaan. Tämä oli hyvä taas!!

IF(I1=I2;"tasapeli";"")

Tuosta saa paremmin siirrettävän taulukossa kun asettaa row() rivin (F) sijasta ne ihan vaan numeroilla 1, 2, 3 jne..

ja muuttaa tuon: VLOOKUP(F1; E$1:F$6; 2; 0)