Excel -kysymys nro 256352451256

[Oliver]

Tarkoitus on luoda mahdollisimman automaattinen kassanseuranta. Pystyykö Excel 98:ssa kaavioita (graafeja) tekemään niin, että käppyrän lähdetietoalue muuttuu jonkin solun arvon mukaan?

Siis esim. solu A1 laskisi vetojen määrän, ja aina kun A1 muuttuu, niin graafin lähdetietoalue kasvaisi automaattisesti.

[Berrie]

Vaikka en olekaan mikään mestari noissa kaavioissa, uskallan väittää ettei onnistu.

Perustan väitökseni siihen, ettei INDIRECT-niminen funktio (sillä voi määrittää alueita muiden solujen arvon mukaan) toimi SERIES-funktion kanssa jolla kaavion käppyrät määritellään. Muiden funktioiden kanssa tuo toimii, mutta tuon Seriesin kanssa ei näytä toimivan.

Jos joku keksii ratkaisun niin äkkiä tänne, olen mielelläni väärässä tässä asiassa.

[Oliver]

Joo, eipä näytä onnistuvan, ei. Täytyy sit kai tehdä joku makro, mikä hakee jostain solusta alueen osoitteen ja kopioi sen käppyrän lähdetiedoksi. Ei niin tyylikäs tapa, mutta sillä tavalla se ainakin onnistunee.

[Oliver]

Täytyy sit kai tehdä joku makro, mikä hakee jostain solusta alueen osoitteen ja kopioi sen käppyrän lähdetiedoksi. Ei niin tyylikäs tapa, mutta sillä tavalla se ainakin onnistunee.

Ei sit onnistunu noinkaan...

[Berrie]

Vieläkö olet yrittänyt tehdä tuota?

Kokeilin hieman makroja ja vba:ta. Tuommoista kun sijoittaa johonkin vba-funktioon ja kutsuu tuota vaikka jostakin napista niin...

A1 kentästä otetaan se rivi johon alue päättyy.
Kaavioon tulevat luvut tulevat B-sarakkeesta alkaen riviltä yksi ja tosiaan loppuu riville jonka numero otetaan A1-kentästä.

Range(Cells(1, 2), Cells(Cells(1, 1), 2)).Select
Charts.Add
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.Location Where:=xlLocationAsObject, Name:="Taul1"

Testasin tuota Excel 2003:ssa ja soomiversiossa, mutta voisi ehkä toimia vanhemmissakin. Huonona puolena voisi sanoa sen että tekee joka napin painalluksella uuden taulukon entisen päälle. Kannattaa tehdä omalle välilehdelle ja vaikkapa poistaa entinen taulukko ennen uuden luomista, pitää nimen vakiona niin osaa poistaakin sen joka kerta.

[vande]

Lisäisin Betting-summary taulukkoosi yhden sarakkeen, joka kertoo lyötyjen vetojen määrän. Sitten uusi taulukko, jossa "x"-sarake vetojen määrälle (arvo sama kuin tuossa seurannassasi). Sitten kaksi saraketta: tod kassa ja odotettu kassa (toteutus ehdollisena, eli jos x:lla arvo, niin y:lläkin arvo...) . Kuvaajan lähdealueet riittävän isoiksi ja kuvaaja elää sopeutuen uuteen tilanteeseen.

[Oliver]

Kappas vaan, tällainenkin topicci on joskus tullut avattua. :)

Juu, tattista vaan Berrie vinkistä. Ratkasin kylläkin ongelman jo tekemällä makron, joka hakee arvot taulusta ja kopioi ne Liitä määräten/Arvot -metodilla toiselle sheetille, jossa on kaavion arvoalue. Tällöin kaavio osaa jättää loppupään tyhjät arvot näyttämättä (koska niissä ei ole edes kaavoja). Siinä joutuu kyllä painamaan yhtä nappulaa että tuon makron saa käyntiin. Mutta ei se niin kauhiasti haittaa...

[Oliver]

Lisäisin Betting-summary taulukkoosi yhden sarakkeen, joka kertoo lyötyjen vetojen määrän. Sitten uusi taulukko, jossa "x"-sarake vetojen määrälle (arvo sama kuin tuossa seurannassasi). Sitten kaksi saraketta: tod kassa ja odotettu kassa (toteutus ehdollisena, eli jos x:lla arvo, niin y:lläkin arvo...) . Kuvaajan lähdealueet riittävän isoiksi ja kuvaaja elää sopeutuen uuteen tilanteeseen.

Hmmm... Ei kai tuo noin voi toimia? Nuo Excelin kaaviot kun on sellasia, että jos solussa on kaava, niin se ei silloin ole tyhjä, jolloin solu tulee aina kaavioon mukaan.

Tajusinkohan tuon idean edes oikein?

EDIT: Silleen toki toimii, että kirjottais vedon numeron manuaalisesti jokaisen vedon kohdalle. Mutta siitä aiheutuis vaan liikaa näpyttelyä... :)

[vande]

Solun sisältö voi olla ehdollisesti tyhjä. Esim. laskukaavasta eli tuo kaava on solussa b2. =Jos(a2="";"";c20). Niin kauan kun a2 on tyhjä b2:kin on tyhjä, mutta heti kun lykkäät arvon a2:een, saat b2:een c20:n arvon.

EDIT: Jos peliseurantataulukossa pelattujen rivien määrä -solun arvo on ehdollisesti tyhjä, ja viittaat siihen tuossa graafisi taulukossa, niin silloin et tarvitse manuaalista päivitystä. Laskukaava huolehtii siitä!

[Berrie]

Solun sisältö voi olla ehdollisesti tyhjä. Esim. laskukaavasta eli tuo kaava on solussa b2. =Jos(a2="";"";c20). Niin kauan kun a2 on tyhjä b2:kin on tyhjä, mutta heti kun lykkäät arvon a2:een, saat b2:een c20:n arvon.

EDIT: Jos peliseurantataulukossa pelattujen rivien määrä -solun arvo on ehdollisesti tyhjä, ja viittaat siihen tuossa graafisi taulukossa, niin silloin et tarvitse manuaalista päivitystä. Laskukaava huolehtii siitä!

Testailin tätä ja niin pitkään toimi kun käsin värkkäsin ottelunro:ja.

Jos ottelunro-kenttään laittaa vaikkapa kaavan (oletukset ottelunro-sarake on A, juuri syötettävän rivinro 100, pvm-sarake on B):

=if(B100<>"";A99+1;"")

Eli tuohan on tyhjä jos pvm-sarakkeessa ei ole arvoa, mutta tuo tulee taulukkoon mukaan kuitenkin vaikka se olisikin tyhjä. Ainakin Excel 97:ssa...

Sikäli mikäli minäkin tuon tajusin.

[Oliver]

...

Tuo sinun ratkaisu ei aukee mulle vieläkään. Ehkä ollaan ymmärretty toisemme väärin. Eli ongelmahan on se, että kaksi saraketta täynnä ehdollisia kaavoja muodostaa kaavion lähdejoukon. Nämä pitäisi saada näkymään kaaviossa siten, ettei kaavion loppuun tule ylimääräistä nollajonoa niistä soluista, jotka antavat arvokseen "".

[vande]

Taitaa muuten toimia ilman sarakkeen lisäystä tuohon vedonlyöntitaulukkoosi seuraavasti.

Eli nämä kaavat ovat kaavio-taulukon souluissa sarakkeet vaikka A, B ja C
Taulukossa vaikka otsikot(solussa): Vetoja(a1), Kassa(b1), Odotus(c1)

a2:ssa arvo 1
b2:ssa kaava: =JOS(A2="";"";K20), Tässä K20 kassan alkuarvo
c2:ssa kaava: =JOS(A2="";"";L20), Tässä L20 odotettu kassan arvo
a3:ssa kaava: =JOS(K21="";"";A2+1), Eli heti, kun K21:een vetotaulukossa ilmestyy arvo, tämän arvo kasvaa yhdellä (2:ksi) jne
a3:n kaavaa "kopioit" täyttämällä alas, vastaavasti kopioit b2:n ja c2:n kaavoja alas

Vetotaulukon kassasolujen arvojen tulisi olla tyhjiä tuolla ehtolauseella, niin kaavio-taulukon a-sarakkeen alimpana olisi viimeisen vedon juokseva numero

Mä luulen, että kun tätä vähän sulattelet, niin eiköhän ala aueta. Jos ei kertaistumalta aukea, tee välillä jotain muuta, ja anna alitajunnan työskennellä!

[epe]

Toinen Excel-kysymys Muistaakseni täältä ei löytynyt vielä tähän vastausta (nimim. moneen kertaan excel hakusanalla foorumilta hakenut.)

Helppo kyssä. Kuinka saan näppärästi poistettu ylimääräiset tyhjät rivit excelissä?

[Arrows]

Helppo kyssä. Kuinka saan näppärästi poistettu ylimääräiset tyhjät rivit excelissä?

Epäilyttävän helpolta kuullostaa, mutta maalat vasemmalta rivinumeroiden päältä haluamasi rivit ja edit-valikosta painat deleteä?!?

[epe]

Epäilyttävän helpolta kuullostaa, mutta maalat vasemmalta rivinumeroiden päältä haluamasi rivit ja edit-valikosta painat deleteä?!?

Ei aivan näin helppo Tarkennataan hieman kysymyksenasettelua eli jos minulla on esimerkiksi 2000 tietueen taulukko ja haluan tyhjät rivit (joka toinen rivi) pois. Kyseistä manuaalista tapaahan olen hyödyntänyt jo pitkään, mutta tämä tapaus tuntuu vähän tuskaiselta.

Onko mitään funktiota tms. millä VBA:ta osaamaton voisi poistaa rivit (tiedän siis että on, mutta miten ko. homma onnistuisi).

[Oliver]

...

Juu, kyllähän se noin toki toimii. Mutta tuo ei ole ratkaisu meikeläisen ongelmaan. Mä haluan sellasen "loppukäyttäjän" pohjan, jossa ei enää kopioida kaavoja minnekkään vaan keskitytään tekemään sillä sheetillä sitä mihin se on luotu. Okei, eihän toi kopioiminen tietenkään olis liian paha rasti. Mutta on eteen tullu myös tilanteita, joissa kopiointia ei pystyis edes käyttämään. Siksi kaipaisin jotain muuta ratkaisua.

Automaattista (=taustalla toimivaa) ratkaisua siis haen. Nyt mulla on sentään semiautomaatti, kun tuon makron väänsin, eli homma hoituu nappulan painamisella.

Epe: lajittelemalla rivit (Tiedot/Lajittele) tyhjät rivit putoaa viimeiseksi.

[epe]

Epe: lajittelemalla rivit (Tiedot/Lajittele) tyhjät rivit putoaa viimeiseksi.

Kiitos hyvistä vinkeistä, mutta valitettavasti tämäkään ei pätenyt tähän caseen. Tätä olenkin yleensä käyttänyt. Tarkennetaan vielä... rivien poistaminen ilman alkuperäisen rivien järjestyksen sekoittamista.

[Oliver]

Kiitos hyvistä vinkeistä, mutta valitettavasti tämäkään ei pätenyt tähän caseen. Tätä olenkin yleensä käyttänyt. Tarkennetaan vielä... rivien poistaminen ilman alkuperäisen rivien järjestyksen sekoittamista.

Teet yhteen sarakkeeseen kasvavan sarjan, eli tavallaan numeroit rivit. Nyt sulla on jokaiselle riville tunniste. Nyt vaan lajittelet jonkun toisen sarakkeen mukaan ja käyt tuhoamassa tyhjät rivit. Tämän jälkeen lajittelet tunnistesarakkeen mukaan rivit taas nousevaan järjestykseen jonka jälkeen tunnistesarakkeen voi tuhota. Ei niin kaunista mutta ainakin ne tyhjät rivit lähtee.

[epe]

Teet yhteen sarakkeeseen kasvavan sarjan, eli tavallaan numeroit rivit. Nyt sulla on jokaiselle riville tunniste. Nyt vaan lajittelet jonkun toisen sarakkeen mukaan ja käyt tuhoamassa tyhjät rivit. Tämän jälkeen lajittelet tunnistesarakkeen mukaan rivit taas nousevaan järjestykseen jonka jälkeen tunnistesarakkeen voi tuhota. Ei niin kaunista mutta ainakin ne tyhjät rivit lähtee.

Ok. Kiitos Sain toimimaan. Helppoa kuin heinänteko...
A1(sisältää dataa) soluun: 1
A2(ei dataa) soluun: "tyhjä"
A3(sisältää dataa) soluun: =A1+1
A4(ei dataa) soluun, ja eteenpäin COPY-PASTELLA A3-solu.
Ja sitten Lajittele by Sarake A

[Oliver]

A1(sisältää dataa) soluun: 1
A2(ei dataa) soluun: "tyhjä"
A3(sisältää dataa) soluun: =A1+1
A4(ei dataa) soluun, ja eteenpäin COPY-PASTELLA A3-solu.
Ja sitten Lajittele by Sarake A

Ei se kyllä noin voi toimia :)

Siis: laitat vaikka sarakkeeseen A1 luvun 1. Sen jälkeen muokkaa/täyttö/sarjat -valikosta valitset "sarakkeissa", "lineaarinen", "askeleen arvo = 1" ja "loppuarvoksi" alimman dataa sisältävän rivin numeron. Tämän jälkeen sarakkeessa A on luvut:
1
2
3
4
jne.

Tämän jälkeen lajittelet sarakkeen B mukaan koko taulukon, tuhoat lopusta tyhjät rivit tunnisteineen ja lajittelet lopuksi taulukon sarakkeen A mukaan.

Näin siis itse teen, tähän voisi löytyä jotain fiksumpiakin tapoja...

[epe]

Sorry Oliver, toimii Molemmilla tavoilla saa näköjään saman aikaan

Solu "kirjoitetaa" --> mitä ruudulle tulostuu
-------------------
A1 "1" --> 1
A2 "wfekprtyo" --> wfekprtyo
A3 "=A1+1" --> 2
A4 "=A2+1" --> #VALUE
A5 "=A3+1" --> 3
A6 "=A4+1" --> #VALUE

[pokram]

Tässä seliteltynä Oliverin tämän viestiketjun ekaan kysymykseen eräs tapa tehdä graafi, joka kasvaa omaan tahtiin, mutta jättää huomiotta data-alueelle ennalta kirjoitetut kaavat (ja niiden nolla-arvot):

Esimerkissä luodaan uusi työkirja (Auto_update_chart.xls)
Jätetään työkirjan eka välilehti oletusnimelleen (Taul1)

Ravistellaan hellepiposta muutamia rivejä dataa:
A1 = 'Pelinro' (otsikko)
A2 = '1'
A3 = '=JOS(ONLUKU(B3);A2+1;"")'
A4 = '=JOS(ONLUKU(B4);A3+1;"")'
jne
B1 = 'Saldo' (otsikko)
B2 = '500'
B3 = '=JOS(B2<1500;B2*1,05;"")'
B4 = '=JOS(B3<1500;B3*1,05;"")'
jne


Määritetään dynaamisille alueille nimet:
Lisää/Nimi -> Määritä nimi
Arvo1 = '=SIIRTYMÄ(Taul1!$A$2;0;0;LASKE.JOS(Taul1!$A:$A;">0");1)'
Arvo2 = '=SIIRTYMÄ(Taul1!$B$2;0;0;LASKE.JOS(Taul1!$B:$B;">0");1)'

Piirretään kaavio ja muokataan lopuksi siihen:
Luokka-akselin (X) otsikot = '=Auto_update_chart.xls!Arvo1'
Arvot = '=Auto_update_chart.xls!Arvo2'

Näin siis finskien Excel taipuu "elävien graafien" teossa.
Käytetyt funktiot ovat sikäli perusjuttuja, että enkkuilijoilla ei pitäisi tulla hiki niitä käännellessä.

[KR]

Arvo1 = '=SIIRTYMÄ(Taul1!$A$2;0;0;LASKE.JOS(Taul1!$A:$A;">0");1)'
Arvo2 = '=SIIRTYMÄ(Taul1!$B$2;0;0;LASKE.JOS(Taul1!$B:$B;">0");1)'

Piirretään kaavio ja muokataan lopuksi siihen:
Luokka-akselin (X) otsikot = '=Auto_update_chart.xls!Arvo1'
Arvot = '=Auto_update_chart.xls!Arvo2'

Näin siis finskien Excel taipuu "elävien graafien" teossa.
Käytetyt funktiot ovat sikäli perusjuttuja, että enkkuilijoilla ei pitäisi tulla hiki niitä käännellessä.

Ahaa! "Siirtymä" lienee OFFSET joka on hyvin tuttu ja kätevä, kannattaa muidenkin opetella jos on vieras funktio! Monessa paikassa voi hyödyntää. Siirtymöinkin tästä golf-kentälle...

-kr-

[epe]

Tässä on muuten muutama osoite mistä löytyy suomi-englanti -excelin funktiosanakirjat... käyttökelpoisia sivuja.

http://www.tekstiviestit.fi/sihteeriopa ... ktiot.html
http://cherbe.free.fr/traduc_fonctions_xl97.html