Excel-säätöketju

[mpr]

Käytä HAKU-funktion sijaan PHAKU- tai VHAKU-funktiota ja laita viimeiseen kenttään "EPÄTOSI".

En saa tuolla tavalla näkyviin kuin #VIITTAUS! kommentin. Kokeilin molempia funktioita sekä TOSI että EPÄTOSI asetuksilla. Data on järjestetty ensimmäisen sarakkeen mukaan aakkosjärjestykseen.
=PHAKU(A106;D$106:D$139;E$106:E$139;EPÄTOSI)

Tuollaista funktiota käytin ja seuraavaa tietoa on sarakkeissa.
A Urheilijan nimi
B Tässä sarakkeessa tuo haku funktio
D Urheilijan nimi tulosluettelossa
E Urheilijan tulos

EDIT: Jos urheilija ei ole tulosluettelossa, nyt tulee #puuttuu! ilmoitus, eli jotain menee oikein, mutta ei kaikki.

[credit]

Käytä HAKU-funktion sijaan PHAKU- tai VHAKU-funktiota ja laita viimeiseen kenttään "EPÄTOSI".

En saa tuolla tavalla näkyviin kuin #VIITTAUS! kommentin. Kokeilin molempia funktioita sekä TOSI että EPÄTOSI asetuksilla. Data on järjestetty ensimmäisen sarakkeen mukaan aakkosjärjestykseen.
=PHAKU(A106;D$106:D$139;E$106:E$139;EPÄTOSI)

Tuollaista funktiota käytin ja seuraavaa tietoa on sarakkeissa.
A Urheilijan nimi
B Tässä sarakkeessa tuo haku funktio
D Urheilijan nimi tulosluettelossa
E Urheilijan tulos

EDIT: Jos urheilija ei ole tulosluettelossa, nyt tulee #puuttuu! ilmoitus, eli jotain menee oikein, mutta ei kaikki.

Käytät funktiota ihan väärin - mikä on varsin yleistä, jos on tottunut käyttämään pelkästään HAKU-funktiota eikä ole koskaan käyttänyt PHAKUA tai VHAKUA.

Tässä täsmällisemmät ohjeet:

Tee kaksi erillistä taulukkoa. Laita ensimmäiseen taulukkoon urheilijoiden nimet ensimmäiseen sarakkeeseen ja kirjoita toiseen sarakkeeseen kaavat, joilla haet tulokset tulosluettelosta. (Yksittäisten kisojen tulosluettelot voivat olla omia taulukoitaan.) Kaavassa laita ensimmäiseen kenttään viittaus 1. taulukon 1. sarakkeeseen; toiseen kenttään maalaat koko sen taulukon, josta tulokset haetaan; kolmanteen kenttään laitat numeron 2, joka kertoo sen, mistä tulosluettelotaulukon sarakkeesta tulokset haetaan; neljänteen kenttään sitten se "EPÄTOSI".

EDIT: Kahden aivan erillisen taulukon tekeminen ei tietenkään ole täysin välttämätöntä, vaan voit pitää tulosluettelotaulukon sarakkeet edelleen kiinni siinä taulukossa, jossa urheilijoiden nimet ja PHAKU-kaavat ovat. Sinun pitäisi kuitenkin kaavoja kirjoittaessasi mieltää taulukot erillisiksi. Itse olen tottunut rakentamaan omat Excel-taulukkoni siten, että datamassa on yleensä kokonaan erillisellä välilehdellä, mistä sitten haen kaavojen avulla haluamaani dataa jollekin toiselle välilehdelle (esim. voimaluku- tai tn-laskentaa varten).

[mpr]

No nyt toimii

Kiitokset avusta.

[Arrows]

Jatketaanpas sitten vähän kinkkisemmällä kysymyksellä ehkäpä.

Data on sarakkeittain vaikkapa näin:
A Sarja
B Kausi
C Kotijoukkue
D Vierasjoukkue
E Kotimaalit
F Vierasmaalit

miten saan laskettua kaavoilla kaikkein yksinkertaisimmin sarakkeille G ja H monesko kauden ottelu on kysessä pelaaville joukkueille? Vai meneekö tämä jo niin monimutkaiseksi että parhaiten auttaisi VBA-makro? Plussaa tuossa olisi tietysti vielä se että se osaisi automaattisesti erottaa sarjat ja kaudet toisistaan noiden A&B-sarakkeiden avulla ettei joka kautta tarvitsisi tehdä aina erikseen.

[credit]

Jatketaanpas sitten vähän kinkkisemmällä kysymyksellä ehkäpä.

Data on sarakkeittain vaikkapa näin:
A Sarja
B Kausi
C Kotijoukkue
D Vierasjoukkue
E Kotimaalit
F Vierasmaalit

miten saan laskettua kaavoilla kaikkein yksinkertaisimmin sarakkeille G ja H monesko kauden ottelu on kysessä pelaaville joukkueille? Vai meneekö tämä jo niin monimutkaiseksi että parhaiten auttaisi VBA-makro? Plussaa tuossa olisi tietysti vielä se että se osaisi automaattisesti erottaa sarjat ja kaudet toisistaan noiden A&B-sarakkeiden avulla ettei joka kautta tarvitsisi tehdä aina erikseen.

Olisiko näistä kaavoista apua?

G-sarakkeeseen: =LASKE.JOS($C$1:D1;C1)
H-sarakkeeseen: =LASKE.JOS($C$1:D1;D1)

Tämän yksinkertaisempaan en pysty.

[Arrows]

Olisiko näistä kaavoista apua?

G-sarakkeeseen: =LASKE.JOS($C$1:D1;C1)
H-sarakkeeseen: =LASKE.JOS($C$1:D1;D1)

Tämän yksinkertaisempaan en pysty.

Hmm, tosiaan... Sitä se teettää kun alkaa ajattelemaan liian vaikeasti. Kiitokset avusta!

[maig]

Sarakkeessa H on rankingluokka, joka on haettu jo funktiolla erikseen. Ranking on liukuva, joten arvo solussa Hx vaihtelee jatkuvasti. Ranking on jaettu kolmeen luokkaan, esim A+,A ja A-, jota tämä funktio haluaa hakea.

Kuinka nämä kaikki saadaan yhdistettyä yhdeksi IF-funktioksi?

Luokka A: =IF(H2="A",IF(I2<-0.5,$K$4,IF(I2<0.5,$K$2,IF(I2<10,$K$3,$K$3))))
Luokka B: =IF(H2="B",IF(I2<-0.5,$K$7,IF(I2<0.5,$K$5,IF(I2<10,$K$6,$K$6))))
Luokka C: =IF(H2="C",IF(I2<-0.5,$K$11,IF(I2<0.5,$K$9,IF(I2<10,$K$10,$K$10))))
Luokka D: =IF(H2="D",IF(I2<-0.5,$K$14,IF(I2<0.5,$K$12,IF(I2<10,$K$13,$K$13))))
Luokka E: =IF(H2="E",IF(I2<-0.5,$K$17,IF(I2<0.5,$K$15,IF(I2<10,$K$16,$K$16))))
Luokka F: =IF(H2="F",IF(I2<-0.5,$K$20,IF(I2<0.5,$K$18,IF(I2<10,$K$19,$K$19))))

edit: soluissa Kx ovat siis listattu rankingin alaluokat

[S.Mäenala]

Re: maig

Jos ymmärsin oikein, niin jokin tämäntyyppinen voisi toimia:

=IF(I2<-0.5;H2 & "-";IF(I2<0.5;H2;H2 & "+"))

p.s mun office versio hyväksyy erottimina vain puolipisteet. jos sun versiossa käytetään pilkkuja niin käytä sitten niitä puolipisteiden paikalla:

=IF(I2<-0.5,H2 & "-",IF(I2<0.5,H2,H2 & "+"))

[maig]

En nyt saanut tuota edellistä mitenkään toimimaan. Isketään seuraava.


A1: Pelaaja 1 B1: Hyökkääjä
A2: Pelaaja 2 B2: Puolustaja
A3: Pelaaja 3 B3: Puolustaja
jne alaspäin...
A18: Pelaaja 18 B18: Hyökkääjä

Oletetaan, että listassa on 12 hyökkääjää ja 6 puolustajaa kuten perus lätkäjengissä. Millä saan automaattisesti kaikki hyökkääjät listattua järjestyksessä soluihin C1-C12 ja puolustajat D1-D6?

[S.Mäenala]

En nyt saanut tuota edellistä mitenkään toimimaan.

Tuo mun kirjoittama lauseke on melko yksinkertainen, ei sen toimimaan saamisessa pitäisi mitään ongelmia olla. Sinun edeltävästä kysymyksestäsi päättelin että:
- käytät desimaalierottimena pistettä, (mulla myös piste)
- sinun office-versiosi käyttää kaavoissa kenttien erottimena pilkkua (mulla se on puolipiste).

Tossa kaavassa olevat kaksi JA-merkkiä (=näppiksessä numeron 6 yläpuolella oleva merkki) näyttää tämän sivuston fontilla melkein tekstiltä "Et" mutta ne ovat siis mainittuja JA-merkkejä.

=IF(I2<-0.5,H2 & "-",IF(I2<0.5,H2,H2 & "+"))

Mutta varmuuden vuoksi: jos sinulla on solussa H2 esimerkiksi "B" ja solussa I2 luku 1 ja tuo kaava tulee soluun J2, niin minkä arvon haluat kaavan siihen soluun kirjoittavan? Oletin että siihen pitäisi saada B+.
Tuo yo. kaava tekee just sen. Kopiot sen sellasenaan soluun J2 ja kaavakopiointia käyttäen kopioit siitä sitten edelleen soluihin J3, J4, jne.

Isketään seuraava.

Miksi et yksinkertaisesti "sorttaa" taulukkoasi sarakkeen B mukaan nousevaan järjestykseen? Kun sitten haet hyökkääjiä saat ne suoraan soluista A1 - A12. Ja kun tarvitset puolustajia, viittaat soluihin A13 - A18.

[credit]

- käytät desimaalierottimena pistettä, (mulla myös piste)
- sinun office-versiosi käyttää kaavoissa kenttien erottimena pilkkua (mulla se on puolipiste).

Se, käytetäänkö Excelissä desimaalierottimena pilkkua vai pistettä ja kaavoissa erottimena puolipistettä vai pilkkua, johtuu aluekohtaisista asetuksista eikä Office-paketista. Pisteet voi vaihtaa pilkuiksi ja pilkut puolipisteiksi ohjauspaneelin kautta valitsemalla aika-, kieli- ja alueasetuksista maaksi Suomen.

[S.Mäenala]

Se, käytetäänkö Excelissä desimaalierottimena pilkkua vai pistettä ja kaavoissa erottimena puolipistettä vai pilkkua, johtuu aluekohtaisista asetuksista eikä Office-paketista. Pisteet voi vaihtaa pilkuiksi ja pilkut puolipisteiksi ohjauspaneelin kautta valitsemalla aika-, kieli- ja alueasetuksista maaksi Suomen.

Oikeassapa olet credit! Mutta huomasitko että maig oli tuolla aiemmin käyttänyt kaavoissa erottimena pilkkua. Halusin vain kiinnittää hänen huomionsa tohon seikkaan.
Tosin en tiedä mikä on tilanne Mac-versioissa kun en ko. käyttistä tunne. Itse joudun vähän tyhmästi yhden jenkkisoftan bugin vuoksi käyttämään desimaalierottimena pistettä.

[maig]

=IF(I2<-0.5,H2 & "-",IF(I2<0.5,H2,H2 & "+"))

Sainpas toimimaan, kun tajusin tuon logiikan lopulta. Kiitos!

Isketään seuraava.

Miksi et yksinkertaisesti "sorttaa" taulukkoasi sarakkeen B mukaan nousevaan järjestykseen? Kun sitten haet hyökkääjiä saat ne suoraan soluista A1 - A12. Ja kun tarvitset puolustajia, viittaat soluihin A13 - A18.

Sorttaukseen pitäisi kaiketi tehdä macro, koska nuo solut Ax-Bx päivittyvät jatkuvasti? Haluan tästä täysin automaattisen systeemin ja etsin funktiota tyyliin

ETSII 1. HYÖKKÄÄJÄN (esim B1)=> KOPIOI B1 => C1
ETSII 2. HYÖKKÄÄJÄN (E1) => KOPIOI E1 => C2

Eli tavallaan noukkisi tuon hyökkääjä parametrin ja sen jälkeen sorttaisi itse JÄRJESTYKSESSÄ C1 alaspäin. Sama tietysti puolustajille

[S.Mäenala]

Sorttaukseen pitäisi kaiketi tehdä macro, koska nuo solut Ax-Bx päivittyvät jatkuvasti?

Luultavasti olet oikeassa siinä että tuontapaisen 'epätyypillisen' tehtävän suorittaminen Excelissä vaatii Visual Basicin käyttöä.

Yleensä tälläisiin tilanteisiin joudutaan kun yritetään tehdä vääriä temppuja väärillä työkaluilla. Sinä yrität tässä tehdä tyypillisesti tietokantaohjelmalla tehtävää hommelia. Accessilla tälläset haut ja filtteröinnit hoituu kädenkäänteessä ja hakutulokset voi sitten 'automaattisesti' tuoda Exceliin laskentaa varten. Tietokantaohjelma myös päivittää omat hakutaulukkonsa automaattisesti, joten tiedot pysyvät aina helposti up-to-date.

Usein on myös niin, että asioita yritetään tehdä liian 'vaikeasti'. Jos et pahastu, niin tässäkin tapauksessa tuli ekana mieleen että mitä kumman tarkoitusta varten nuo tiedot on saatava just tohon kuvaamaasi muotoon C- ja D-sarakkeille? Miksi niitä ei voi käyttää suoraan A- ja B-sarakkeilta.

[MattiN]

Mulla on seuraavanlainen ongelma.

Eli tuossa kuvassa olen laskenut maalikeskiarvoja siten, että vaaka-akselilla on sarjakierros ja y-akselilla maalikeskiarvot. Olen laskenut tuossa kaavassa maalimäärät sum.jos.joukko-toiminnolla, mutta en millään saa tuota sellaiseksi, että sen voisi kopioida vetämällä seuraaviin soluihin. Toki tuossa nyt puuttuu dollaritkin, mutta tuo kaava ei ymmärrä jos laitan tuonne ympyröidyn ykkösen tilalle solun A3 arvon. Miten se tuli sinne laittaa? Oon koittanu kaikkee "A3", "=A3" jne, mutta ei vaan pelaa. Nyt joudun sitten syöttään sinne kaikki noi sarjakierrokset (1,2,3..) käsin.

Tää sama ongelma työllistää myös monessa muussa yhteydessä, niin olis kiva tietää ratkaisu.

PS. Ei kannata yrittää hyödyntää yhtään mitään tuosta kuvaajasta suoraan. Siellä on monenlaista sudenkuoppaa.

[S.Mäenala]

Oon koittanu kaikkee "A3", "=A3" jne, mutta ei vaan pelaa. Nyt joudun sitten syöttään sinne kaikki noi sarjakierrokset (1,2,3..) käsin.

Pelkkä A3 riittää (ilman lainausmerkkejä).

[MattiN]

Oon koittanu kaikkee "A3", "=A3" jne, mutta ei vaan pelaa. Nyt joudun sitten syöttään sinne kaikki noi sarjakierrokset (1,2,3..) käsin.

Pelkkä A3 riittää (ilman lainausmerkkejä).

Kiitti ihan perhanasti!

[samsalonen]

VB ei ole meikäläisen alaa, joten apuja kaivataan seuraavaan:

- Seuranta-Exceliin kaipaisin nappia joka poimisi tarvittavat tiedot lakanasta ja koostaisi niinden perusteella vihje-tagin leikepöydälle. Sisältö siis mallia:

Koodi: Valitse kaikki

[vihje
KOHDE= G10 + " - " + H10 (kotijoukkue - vierasjoukkue)
VETO= J10 + " " + K10 + " " + L10 (vetomuoto merkki handicap)
KERROIN= N10 
PANOS= R10
BOOKKERI=  
PVM= B10 + C10 (pvm aika)
laji= D10 + " - " + E10 + " - " + F10 (laji maa sarja)
rajakerroin= M10
/]

[MarkusM]

VB ei ole meikäläisen alaa, joten apuja kaivataan seuraavaan:

- Seuranta-Exceliin kaipaisin nappia joka poimisi tarvittavat tiedot lakanasta ja koostaisi niinden perusteella vihje-tagin leikepöydälle. Sisältö siis mallia:

Koodi: Valitse kaikki

[vihje
KOHDE= G10 + " - " + H10 (kotijoukkue - vierasjoukkue)
VETO= J10 + " " + K10 + " " + L10 (vetomuoto merkki handicap)
KERROIN= N10 
PANOS= R10
BOOKKERI=  
PVM= B10 + C10 (pvm aika)
laji= D10 + " - " + E10 + " - " + F10 (laji maa sarja)
rajakerroin= M10
/]

Jos ei välttämättä tarvitse kopioida tagia leikepöydälle niin myös normaali tekstikaava / ketjuta funktio ajaa tuossa asian.

Tyyliin:
=CONCATENATE("[vihje";CHAR(10);"KOHDE= ";G10;" - ";H10;CHAR(10);"VETO= ";J10;" ";K10;" ";L10;CHAR(10);"KERROIN= ";N10;CHAR(10);"PANOS= ";R10;CHAR(10);"BOOKKERI= A";CHAR(10);"PVM= ";DAY(B10) & "." & MONTH(B10) & "." & YEAR(B10) & " " & HOUR(C10) & ":" & TEXT(MINUTE(C10);"00");"/]")

[samsalonen]

Jos ei välttämättä tarvitse kopioida tagia leikepöydälle niin myös normaali tekstikaava / ketjuta funktio ajaa tuossa asian.

Tyyliin:
=CONCATENATE("[vihje";CHAR(10);"KOHDE= ";G10;" - ";H10;CHAR(10);"VETO= ";J10;" ";K10;" ";L10;CHAR(10);"KERROIN= ";N10;CHAR(10);"PANOS= ";R10;CHAR(10);"BOOKKERI= A";CHAR(10);"PVM= ";DAY(B10) & "." & MONTH(B10) & "." & YEAR(B10) & " " & HOUR(C10) & ":" & TEXT(MINUTE(C10);"00");"/]")

Erittäin suuri kiitos, tuo ajaa mukavasti asian!

[S.Mäenala]

VB ei ole meikäläisen alaa, joten apuja kaivataan seuraavaan:

- Seuranta-Exceliin kaipaisin nappia joka poimisi tarvittavat tiedot lakanasta ja koostaisi niinden perusteella vihje-tagin leikepöydälle. Sisältö siis mallia...

Jos haluat 'nappula' -version niin tässä on sellainen.

1. Luo ensin nappula (button) työkirjaasi, nappulan teksti saa olla ihan mitä haluat.
2. 'Assign macro' -ikkunassa klikkaa 'New'.
3. Kopioi avautuvaan koodi-ikkunaan seuraava koodi:

Koodi: Valitse kaikki

Sub Button1_Click()

Dim spa, hyp, sig As String
Dim game As Long

spa = "  "
hyp = " - "
game = ActiveCell.Row
If Cells(game, 12).Value < 0 Then
    sig = Chr(45)
Else
    sig = Chr(43)
End If
Cells(1, 1000).Value = "kohde = " + Cells(game, 7).Value + hyp + Cells(game, 8).Value
Cells(2, 1000).Value = "veto = " + Cells(game, 10).Value + spa + CStr(Cells(game, 11).Value) + sig + CStr(Abs(Cells(game, 12).Value))
Cells(3, 1000).Value = "kerr = " + CStr(Format(Cells(game, 14).Value, "Standard"))
Cells(4, 1000).Value = "panos = " + CStr(Format(Cells(game, 14).Value, "#0.0")) + " yks"
Cells(5, 1000).Value = "bookk = "
Cells(6, 1000).Value = "aika = " + CStr(Cells(game, 2).Value) + spa + CStr(Format(Cells(game, 3).Value, "hh:mm"))
Cells(7, 1000).Value = "laji = " + Cells(game, 4).Value + spa + Cells(game, 5).Value + spa + Cells(game, 6).Value
Cells(8, 1000).Value = "raja = " + CStr(Cells(game, 13).Value)
Range(Cells(1, 1000), Cells(8, 1000)).Copy

End Sub

Koodi kopioi tuon kuvaamasi tekstin Leikepöydälle.
Koodi on hyvin yksinkertainen ja voisi olettaa että se toimii vähän vanhemmillakin Excel-versioillla.
Kopioitava rivi valitaan aktiivisen solun perusteella: jos haluat kopioida rivin 10 tiedot, klikkaa ensin jotain 10. rivin solua.

Koodi olettaa että numeeriset kentät on esitetty numeroina ja tekstikentät tekstinä ilman mitään kikkailuja. Tarvittavat konversiot tehdään koodin toimesta.
Koodi käyttää apuna sarakkeen 1000 soluja.

Edit: leikepöydälle

[h]

Voiko Excelissä ympätä lajittelutoiminnon suoraan kaavaan eli tehdä matriisista hakemisen (esim. PHAKU) toiminnon ohessa sorttauksen yhden tai useamman sarakkeen tietojen perusteella siten, että määritellyn hakuehdon täyttävät arvot saadaan tulosmatriisiin toivotussa järjestyksessä?

Esim. Jos minulla on soluissa A1-D9 arvot:
1 8 11 a
2 7 11 b
3 8 10 g
2 6 10 d
3 8 11 e
1 8 11 f
3 8 10 g
2 7 10 h
2 6 11 i

Haluaisin poimia kaikki A-sarakkeen 2:set ja lajitella ne ensisijaisesti sarakkeen B ja toissijaisesti sarakkeen C perusteella saaden nelirivisen tulosmatriisin:
2 6 10 d
2 6 11 i
2 7 10 h
2 7 11 b

Jos ajatellaan vain tulosmatriisin viimeistä saraketta, niin mitä alla olevaan kaavaan tulisi lisätä, jotta kirjaimet d, i, h, b tulisivat jokainen vuorollaan, eikä joka riville tyrkkäisi pelkkää beetä?
=PHAKU(2;$A$1:$D$9;4;EPÄTOSI)

[S.Mäenala]

Voiko Excelissä ympätä lajittelutoiminnon suoraan kaavaan eli tehdä matriisista hakemisen (esim. PHAKU) toiminnon ohessa sorttauksen yhden tai useamman sarakkeen tietojen perusteella siten, että määritellyn hakuehdon täyttävät arvot saadaan tulosmatriisiin toivotussa järjestyksessä?

Ei voi!
Access'issä tuollainen poiminta olisi 'piece of cake', mutta Excel'issä joudut kirjoittamaan VBA-makron sen toteuttamiseksi.

Mutta jos Sinulle pelkkä filtteröinti ja lajittelu riittää niin se on helppo tehdä:

- maalaa taulukko
- valitse Sort & Filter ==> Filter


- valitse ensin C-sarakkeelle Sort Smallest To Largest
- valitse sitten B-sarakkeelle sama
- valitse lopuksi A-sarakkeelle Number Filters ==> Equals ja kirjoita arvoksi 2

Lajittelu puretaan lopuksi valitsemalla uudelleen Sort & Filter ==> Filter

Hope this helps...

[h]

Ei voi!
Access'issä tuollainen poiminta olisi 'piece of cake', mutta Excel'issä joudut kirjoittamaan VBA-makron sen toteuttamiseksi.

Olen kyllä tehdyt valikon kautta "manuaalisuodatuksia", kuitenkin joissain tapauksissa olisi kätevää, jos tiedot järjestyisivät automaattisesti sorttaus suoraan kaavaan ymppäämällä. Mutta jos ei voi, niin sitten ei voi. Eipähän tarvitse enempiä tuota pähkäillä, vaan on keksittävä jotakin muuta. Eli apua vastauksestasi oli joka tapauksessa. Kiitos!

[telso]

Tällainen ongelma tuli. Copy-pastetan numerodataa ja listaan tulee ärsyttävä pieni vittumaisuus, kun saavutetaan yli tuhannen menevä luku.

ensin numerot tulee tyylillä:

768
985
999

etc.

kun menee yli tuhannen niin tulee näin:

1 043
1 353
1 694

eli nakkaa tuommoisen välin tuohon ja sitä ei sitten excel tunnista, jos vaikka haluaisin tehdä laskutoimituksen 1x (tuo luku). Tulee value errorit tuon välilyönnin takia. Miten tuon välin sais pois?