Excel keskustelu

[slarti]

Eivät toimi tuossa versiossa. Pitää olla Office365.

 

[mario]

Eivät toimi tuossa versiossa. Pitää olla Office365.

Siis tuota kaavaa ei voi saada toimimaan tässä?

 

[edup]

Siis tuota kaavaa ei voi saada toimimaan tässä?

Joo ei. Pitää olla 365 tai 2021 käytössä.

Tuolta näkee kutakin funktiota klikkaamalla että missä ne toimii: Excel functions (alphabetical)

 

[kalinka]

Tuo olisi tehtävissä muuten siten että tsekkaisit ensin onko vasemman taulun rivin ID tuon oikean taulun ID ja sitten vertaisit alkupäivää aikaväliin sekä loppupäivää aikaväliin. Jos molemmat tosia -> prosentti, jos molemmat epätosia -> 100%, jos toinen tosi ja toinen ei -> tee jotain. Isoksi ongelmaksi tässä muodustuu se, että tuossa oikealla olevassa taulussa tuo ID on moneen kertaan ja excelin erilaiset lookup-funktiot pysähtyvät kun löytävät ekan matchin. Uusimmassa excelissä on olemassa tietynlaisia loop-funktiota tehtävissä LAMBDA-funktiolla mutta juuri nyt ei järki riitä sellaiseen.

Joo tuolla tavoin asiaa ajattelin ja luulisin osaavani hakea, mutta useampi rivi oikeassa taulussa menee ohi oman osaamistason. Jotain ehtoajattelua yritin miettiä, mutta ei riittänyt osaaminen. LAMBDA-funktiosta en ole aiemmin kuullutkaan, pitää googletella.

Hauska selailla ketjua ja huomata oman osaamisensa olevan hyvin rajoittunutta yhtään isommassa kuvassa. Töissä yksinkertaisia kaavoja käyttäessä on lähes gurufiilis omassa yhteisössä.

 

[slarti]

Hakufunktiot ja päivämäärät ovat meikäläiselle aina vaikeita. Ei kai tämä kovin vaikea ongelma pitäisi olla ratkaistavaksi, mutta eipä toimi oma päättely riittävästi. Mitenköhän taulukon 1 Prosentti (sarake D) voisi päätellä taulukon 2 tietojen perusteella? Poimintaehtoja ovat matchäävä ID ja päivämääräväli. Käytännössä taulukossa kaksi on merkittynä aikaväli, jolloin voimassa kyseinen prosentti. Aikavälien ulkopuolella prosentti on 100.

Lisäksi on rivin 10 kaltaisia tapauksia, joissa alkuperäinen ajanjakso osuu useammalla aikavälille taulukossa kaksi. Voisi olla myös useampi eri prosenttirivi, esimerkissä vain osittain prosentti on 60 ja osittain 100. Näistä riittäisi vaikka kun saisi vain jonkun error-tilan, ne voisi katsoa vaikka käsin lopuksi.

Tämä olikin mielenkiintoinen ja voi olla että mietin tätä ihan liian monimutkaisesti, mutta filtteröimällä tuota aluetta sopivasti ID:llä ja päivämäärillä erikseen saadaan jotain semijärkevää aikaiseksi. Kun lasketaan filttereiden palauttamien alueiden rivit, saadaan mukavasti tietoa miten alku ja loppupäivämäärät osuvat alueille vai osuvatko ollenkaan. Saadaan myös tietää onko aikavälillä kokonaisia ajanjaksoja jotka jäävät päivämäärien väliin.

Tässä alku jolla saat alku- ja loppuprosentit sekä onko välillä kokonaisia ajanjaksoja jotka omilla riveillään. Tuon saisi varmasti vielä laskemaan painotetun prosentin kun kertoisi jokaisen filtterin palauttaman alueen päiivämäärävälin päivien määrän prosentilla ja jakaisi kokonaisaikavälin päivillä. Menee vaan melko monimutkaiseksi mutta ei mahdottomaksi. Table4 on tuo oikeanpuoleinen taulu ja toisessa taulussa olevien sarakkeiden kaavat alapuolella. Vaatii että nuo taulut ovat tosiaan tauluobjekteja jotta nimet toimivat.

HUOM! Vaatii Office365:n tai 2021:n

 

[mario]

Joo ei. Pitää olla 365 tai 2021 käytössä.

Tuolta näkee kutakin funktiota klikkaamalla että missä ne toimii: Excel functions (alphabetical)

Pystyiskö tuon tekeen sitten Libre Officella?

 

[edup]

Pystyiskö tuon tekeen sitten Libre Officella?

Kyllä tuohon siis varmasti löytyy vanhalle Excelin versiollekin tapa tehdä tuo. Toi mun ehdotus nyt vaan oli sellainen mikä oli lähestulkoon identtinen kuin mitä oon itse aiemmin käyttänyt jossain, niin heitin sen siksi. Mutta ei oo nyt aikaa perehtyä siihen että millä vanhemmilla Excelin funktioilla tuon saisi tehtyä näppärästi.

 

[mario]

Kyllä tuohon siis varmasti löytyy vanhalle Excelin versiollekin tapa tehdä tuo. Toi mun ehdotus nyt vaan oli sellainen mikä oli lähestulkoon identtinen kuin mitä oon itse aiemmin käyttänyt jossain, niin heitin sen siksi. Mutta ei oo nyt aikaa perehtyä siihen että millä vanhemmilla Excelin funktioilla tuon saisi tehtyä näppärästi.

Periaatteessa riittäis että saisi ne luvut 1-6 kahteen alekkaiseen kenttään siten että ei voisi olla sama numero. Eikö tälle muka löydy mitään keinoa, pitääkö tässä vain random numberg generatorilla lähteä pelaileen.

 

[slarti]

Periaatteessa riittäis että saisi ne luvut 1-6 kahteen alekkaiseen kenttään siten että ei voisi olla sama numero. Eikö tälle muka löydy mitään keinoa, pitääkö tässä vain random numberg generatorilla lähteä pelaileen.

Vanha tapa vaatii vähän apusoluja. Laita kuuteen soluun =RAND() ja kahteen muuhun soluun =RANK(solu;alue). Nuo kaksi solua muuttuvat sitten joka kerta kun teet minkä tahansa muutoksen työkirjassa. Johtuu siitä että rand() on "volatiili"-funktio joka päivittyy aina kun taulukko lasketaan. Nuo uudemmat funktiot tekisivät ihan samaa, eli joutuisit sen kerran luotuasi joko muuttamaan arvoiksi tai ottamaan laskennan pois päältä työkirjasta. Tarvitset jokaiselle päivälle oman apusolualueen, muuten kaikille päiville tulee sama pari.

 

[mario]

Ei saakeli ja kun kotona ei ole exceliä ollenkaan niin tässähän on ihan solmussa. Taas näkyy vain

 

[slarti]

Etsi vastaava funktio suomeksi. Tai vielä parempi vaihtoehto on vaihtaa kakkien taulukkolaskentaohjelmien kieli englanniksi.

 

[kalinka]

Tämä olikin mielenkiintoinen ja voi olla että mietin tätä ihan liian monimutkaisesti, mutta filtteröimällä tuota aluetta sopivasti ID:llä ja päivämäärillä erikseen saadaan jotain semijärkevää aikaiseksi. Kun lasketaan filttereiden palauttamien alueiden rivit, saadaan mukavasti tietoa miten alku ja loppupäivämäärät osuvat alueille vai osuvatko ollenkaan. Saadaan myös tietää onko aikavälillä kokonaisia ajanjaksoja jotka jäävät päivämäärien väliin.

Tässä alku jolla saat alku- ja loppuprosentit sekä onko välillä kokonaisia ajanjaksoja jotka omilla riveillään. Tuon saisi varmasti vielä laskemaan painotetun prosentin kun kertoisi jokaisen filtterin palauttaman alueen päiivämäärävälin päivien määrän prosentilla ja jakaisi kokonaisaikavälin päivillä. Menee vaan melko monimutkaiseksi mutta ei mahdottomaksi. Table4 on tuo oikeanpuoleinen taulu ja toisessa taulussa olevien sarakkeiden kaavat alapuolella. Vaatii että nuo taulut ovat tosiaan tauluobjekteja jotta nimet toimivat.

HUOM! Vaatii Office365:n tai 2021:n

Aivan mahtavaa, tämä vaikutti toimivan erinomaisesti mitä silmäilin testiaineistolla. Tosiaan uusimman Excelin vaatii toimiakseen. Kiitos paljon!

 

[hkultala]

Excel oletuksena käyttää koneen/käyttäjän kieliasetuksia, mikä saa sen käyttäytymään todella typerästi kun omat kieliasetukset on suomenkieliset:

1) jos sinne kirjoittaa vaikka "1.5" se ei tulkitse sitä numerona 1.5 vaan "1. toukokuuta"
2) jos sinne yrittää importata .csv:tä, jossa erottimena on pilkku, se ei tulkitse tuota pilkkua erotinmerkiksi vaan desimaalipilkuksi ja importin tuloksena on täyttä roskaa.
3) jos muutta .csv:n erottimeksi; sen datan saa kyllä luettua sisään, mutta excel ei tulkitse sen sisältöä, jossa desimaalipilkkuna käytetty pisteenä numeroina vaan stringeinä, jolloin sen sisällöllä ei voi laskea.

Saako excelille jotenkin sanottua, että ". on aina desimaalipilkku, , on aina eroitinmerkki, enkä koskaan halua käsitellä mitään päivämääriä"?

Ilman että tarvii koko windowsin kieliasetuksia vaihtaa pois suomalaisista?

 

[hrk]

Excel oletuksena käyttää koneen/käyttäjän kieliasetuksia, mikä saa sen käyttäytymään todella typerästi kun omat kieliasetukset on suomenkieliset:

1) jos sinne kirjoittaa vaikka "1.5" se ei tulkitse sitä numerona 1.5 vaan "1. toukokuuta"
2) jos sinne yrittää importata .csv:tä, jossa erottimena on pilkku, se ei tulkitse tuota pilkkua erotinmerkiksi vaan desimaalipilkuksi ja importin tuloksena on täyttä roskaa.
3) jos muutta .csv:n erottimeksi; sen datan saa kyllä luettua sisään, mutta excel ei tulkitse sen sisältöä, jossa desimaalipilkkuna käytetty pisteenä numeroina vaan stringeinä, jolloin sen sisällöllä ei voi laskea.

Saako excelille jotenkin sanottua, että ". on aina desimaalipilkku, , on aina eroitinmerkki, enkä koskaan halua käsitellä mitään päivämääriä"?

Ilman että tarvii koko windowsin kieliasetuksia vaihtaa pois suomalaisista?

Lue esim.
How to Change Commas to Decimal Points and Vice Versa in Excel (5 Ways) (avantixlearning.ca)

tai excelin helpistä hakusanalla "separators".

 

[zepi]

Excelissä ei taida saada oikein mitenkään noita päivämäärätulkintoja pois.

Ja pahinta on se, että se ei vain muuta esitysmuotoa, vaan se muuttaa itse datan johonkin timestamp-muotoon jolloin 1.5 -> 1. toukokuuta muuttuu raakadata on tyyliin biljardi miljardia sekuntia joka on 1.5.2022 00:00:00 laskettuna jostain windowssin hetkestä 0 tjsp fiksua.

R:ää kehiin.

 

[Xiyng]

Meillä kehitetään töissä ohjelmistoa, jossa on joissain kohdissa myös datan vientiä jonkinlaiseen taulukkoformaattiin. Excelin ja CSV-tiedostojen suhteesta on todettu meillä sen verran, että Excel ei osaa noudattaa mitään "standardia" vaan tekee kaiken ihan eri tavalla kuin ~kaikki muut ohjelmistot. Toisin sanottuna jos haluaa jotakuinkin järkevästi toimivan formaatin, jonka saa auki myös Excelissä, meillä tuupataan viennissä silloin ulos Excelin omaa formaattia (olikohan .xls vai xlsx). Ei tästä varmaan hirveästi apua ole, mutta ehkä se jotain kertoo siitä, että CSV-tiedostojen käsittely Excelissä voi ihan oikeasti olla hieman tuskallista.

1) jos sinne kirjoittaa vaikka "1.5" se ei tulkitse sitä numerona 1.5 vaan "1. toukokuuta"

"1.5" ei kyllä ole suomessa sen enempää desimaaliluku kuin päivämääräkään. Desimaaliluku olisi "1,5", ja päivämäärä olisi "1.5.". Excel siis arpoo omatoimisesti, kumpaa tuossa tarkoitetaan, ja arpoo lopulta väärin.

 

[hrk]

Meillä kehitetään töissä ohjelmistoa, jossa on joissain kohdissa myös datan vientiä jonkinlaiseen taulukkoformaattiin. Excelin ja CSV-tiedostojen suhteesta on todettu meillä sen verran, että Excel ei osaa noudattaa mitään "standardia" vaan tekee kaiken ihan eri tavalla kuin ~kaikki muut ohjelmistot. Toisin sanottuna jos haluaa jotakuinkin järkevästi toimivan formaatin, jonka saa auki myös Excelissä, meillä tuupataan viennissä silloin ulos Excelin omaa formaattia (olikohan .xls vai xlsx). Ei tästä varmaan hirveästi apua ole, mutta ehkä se jotain kertoo siitä, että CSV-tiedostojen käsittely Excelissä voi ihan oikeasti olla hieman tuskallista.

Eihän siinä mitään ihmeellistä tai vaikeaa ole, kunhan muistaa, että jos desimaalierotin on suomalaisittain pilkku, niin csv-arvojen erotin on tietenkin jotain muuta eli puolipiste. Myös päivämääräformaatin kanssa pitää olla tarkkana.

"1.5" ei kyllä ole suomessa sen enempää desimaaliluku kuin päivämääräkään. Desimaaliluku olisi "1,5", ja päivämäärä olisi "1.5.". Excel siis arpoo omatoimisesti, kumpaa tuossa tarkoitetaan, ja arpoo lopulta väärin.

Riippuu kentän tyypistä. Siis jos on date-tyyppinen kenttä, ja käytössä suomalainen päivämääräformaatti, ja kirjoitat kenttään jotain epätäydellistä, esim. "21.1" (ilman lainausmerkkejä), ja siirryt kentästä pois, niin excel älykkäästi arvaa, että kenties tarkoitit 21.1.2022? Jos taas on oletustyyppinen (general) kenttä, niin 21.1 muuntuu muotoon 21.Jan tai 21.tammikuuta. Mutta jos on text-tyyppinen, niin mitään täydennystä ei yritetä.

Pointsi on tässä se, että Excelissä riittää opeteltavaa. Mutta ei se rikki ole.

 

[hrk]

Excelissä ei taida saada oikein mitenkään noita päivämäärätulkintoja pois.

Ja pahinta on se, että se ei vain muuta esitysmuotoa, vaan se muuttaa itse datan johonkin timestamp-muotoon jolloin 1.5 -> 1. toukokuuta muuttuu raakadata on tyyliin biljardi miljardia sekuntia joka on 1.5.2022 00:00:00 laskettuna jostain windowssin hetkestä 0 tjsp fiksua.

R:ää kehiin.

Kyllä kaikissa niissä ohjelmointijärjestelmissä ja -kielissä (mukaanlukien excel ja R), jotka tuntevat date- tai datetime-tietotyypin, tiedon sisäinen esitysmuoto on jotain ihan muuta kuin se ihmisen luettavaksi tarkoitettu. Eikä tässä ole mitään yhtä standardia.

 

[edup]

Excel oletuksena käyttää koneen/käyttäjän kieliasetuksia, mikä saa sen käyttäytymään todella typerästi kun omat kieliasetukset on suomenkieliset:

1) jos sinne kirjoittaa vaikka "1.5" se ei tulkitse sitä numerona 1.5 vaan "1. toukokuuta"
2) jos sinne yrittää importata .csv:tä, jossa erottimena on pilkku, se ei tulkitse tuota pilkkua erotinmerkiksi vaan desimaalipilkuksi ja importin tuloksena on täyttä roskaa.
3) jos muutta .csv:n erottimeksi; sen datan saa kyllä luettua sisään, mutta excel ei tulkitse sen sisältöä, jossa desimaalipilkkuna käytetty pisteenä numeroina vaan stringeinä, jolloin sen sisällöllä ei voi laskea.

Saako excelille jotenkin sanottua, että ". on aina desimaalipilkku, , on aina eroitinmerkki, enkä koskaan halua käsitellä mitään päivämääriä"?

Ilman että tarvii koko windowsin kieliasetuksia vaihtaa pois suomalaisista?

Excelissä CSV:t (ja muutkin ei Excelin omat formaatit) kannattaa tuoda aina Power Queryn läpi. Eli Data -> From text/csv -> Valitse tiedosto ja klikkaa Import -> Transform Data. Eli älä klikkaa tuonti-ikkunassa Loadia, vaan transformilla pääsee sinne power queryn puolelle. Tuolla voit sitten vapaasti määritellä tuotaville sarakkeille tietotyypit, tehdä mahdollisesti noita desimaalipisteiden ym. korvauksia, jne.

 

[Xiyng]

Eihän siinä mitään ihmeellistä tai vaikeaa ole, kunhan muistaa, että jos desimaalierotin on suomalaisittain pilkku, niin csv-arvojen erotin on tietenkin jotain muuta eli puolipiste. Myös päivämääräformaatin kanssa pitää olla tarkkana.

Henkilökohtaisesti en ole tainnut joutua puljaamaan Excelin kanssa, mutta kuulemieni yksityiskohtien perusteella vaikealta on vaikuttanut. Ehkä CSV:n toki jotenkin saa toimimaan sekä Excelissä että muissa CSV-tiedostoja ymmärtävissä sovelluksissa, ja voihan toki olla niinkin, että meillä on oletettu CSV-tiedoston käyttäjän tekevän lopulta jotain heikommin toimivaa. Pitää varmaan haastaa aiheesta lisääkin, kun seuraavan kerran pitää jostain saada ulos taulukkomuotoista dataa.

Riippuu kentän tyypistä. Siis jos on date-tyyppinen kenttä, ja käytössä suomalainen päivämääräformaatti, ja kirjoitat kenttään jotain epätäydellistä, esim. "21.1" (ilman lainausmerkkejä), ja siirryt kentästä pois, niin excel älykkäästi arvaa, että kenties tarkoitit 21.1.2022? Jos taas on oletustyyppinen (general) kenttä, niin 21.1 muuntuu muotoon 21.Jan tai 21.tammikuuta. Mutta jos on text-tyyppinen, niin mitään täydennystä ei yritetä.

CSV-tiedostoista oli käsittääkseni alun perin puhe, mikä saattoi vähän hämärtyä, kun alkuperäinen ketju yhdistettiin tähän. CSV-tiedostoissa taas ei ole kentillä tyyppejä sen enempää kuin Excel niille arpoo jollain heuristiikalla, ja näin ollen jätin tyypit huomiotta ja keskityin kielelliseen puoleen. Jos kentän tyyppi on tiedossa (toisin kuin CSV-tiedostoissa), on virheellisenkin sisällön aiotun merkityksen arvaaminen luonnollisesti paljon helpompaa.

 

[Robbs]

E;

 

[Ad_Astra]

Miksei onnistu päivämäärien eron laskeminen? Suomeksi siis ja DATEDIF toimisi englanniksi ja PVMERO pitäisi olla suomenkielinen versi, mutta ei toimi. Mitä teen väärin?

Tavoitteena siis tämmöinen, mutta että kaivaa vain kuukaudet syötetyistä päivämääristä

 

[edup]

Miksei onnistu päivämäärien eron laskeminen? Suomeksi siis ja DATEDIF toimisi englanniksi ja PVMERO pitäisi olla suomenkielinen versi, mutta ei toimi. Mitä teen väärin?

Tavoitteena siis tämmöinen, mutta että kaivaa vain kuukaudet syötetyistä päivämääristä

Kun valittelee että jokin ei toimi, niin kannattaa kertoa että miten se ei toimi.

Mutta arvaan että saat tuolla "kaavassa on virhe" tms. ilmoituksen. Ja syy on se, että suomenkielisessä versiossa vakioerotin funktioiden parametreille on puolipiste eikä pilkku. Eli todennäköisesti "=PVMERO(A2;B2)" toimii.

 

[Ad_Astra]

Kun valittelee että jokin ei toimi, niin kannattaa kertoa että miten se ei toimi.

Mutta arvaan että saat tuolla "kaavassa on virhe" tms. ilmoituksen. Ja syy on se, että suomenkielisessä versiossa vakioerotin funktioiden parametreille on puolipiste eikä pilkku. Eli todennäköisesti "=PVMERO(A2;B2)" toimii.

Joo siis kokeiltu kaikilla eri versioilla

MUOKS: Eli halusi sen erottimen mukaan =PVMERO(A2;B2;"m")

 

[Cumbo]

Solun muotoilusta: numeroitahan saa esitettyä ryhmissä solun muotoilun kautta esim. "000 000" tai "### ###". Miten tällaista ryhmittelyä voisi tehdä solun muotoilussa tai ehdollisen muotoilun kautta, jos solu sisältää merkkijonon, joka koostuu numeroista ja kirjaimista?

Pitäisi siis saada A1B2C3D4E --> A1B 2C3 D4E.

 

[otaar]

Koska tuommoinen data tuskin tulee ihmisen syöttämänä voinee sen muotoilla silloin kun sen tuo exceliin?

 

[Cumbo]

Koska tuommoinen data tuskin tulee ihmisen syöttämänä voinee sen muotoilla silloin kun sen tuo exceliin?

Luonnollisesti voi, mutta tavoite olisikin saada excel tekemään se automaattisesti, jotta tiedon voi vain kopsata tiettyyn sarakkeeseen eikä hakata speissiä.

 

[otaar]

Teet taulukon missä data muokataan kakkossarakkeeseen =mid(a1,1,3)&" "&mid(a1,4,3)&" "&...jne

 

[Cumbo]

Teet taulukon missä data muokataan kakkossarakkeeseen =mid(a1,1,3)&" "&mid(a1,4,3)&" "&...jne

Kyllä minäkin toiseen sarakkeeseen saan ne eroteltua, mutta muotoilun tai ehdollisen muotoilun keinoin tässä olisi tavoite.

 

[Pentu]

Irtoaisi useampikin kysymys Excelin liittyen, mutta aloitetaan nyt selkeimmästä ongelmasta.

Olen tilastoinut exceliin pienen porukan talvikalastus kilpailuiden punnituspöytäkirjat kymmenen vuoden ajalta.

Ensimmäinen ongelma liittyy sijaan, minulla on sarakkeeseen (N) laskettu kilpailijan keräämät pisteet ja toisessa sarakkeessa (P) kilpailijan keräämä kauden yhteis saalis. Nykyisella kaavalla sijoitus on jaettu sellaisten henkiöiden kesken, joilla on sama määrä pisteitä. Kesken kauden tämä ei haittaa, mutta kauden päätteeksi nämä saman pistemäärän omaavat kilpailijat eivät jaa sijoitustaan.

Nykyinen kaava sijoituksen laskemiselle on: =RANK(N2; $N$2 : $N$13; 0)

Miten tuota tulisi muokata, jotta saisin saman pistemäärän omaavat kilpailijat järjestykseen saaliin perusteella.

 

[otaar]

apusarakkeeseen "1000000*pisteet + saalis" ja sen mukaan

 

[Pentu]

apusarakkeeseen "1000000*pisteet + saalis" ja sen mukaan

Toki apusarake toimisi, sen voi sitten piilottaa tarvittaessa.

Ratkaisin homman jonkinverran monimutkaisemmin:


Mikäli kilpailijalle ei ole merkitty punnitustuloksia, jätetään sijoitus tyhjäksi. Muutoin Rankataan kilpailijan pisteet muihin kilpailijihin verrattuna. Tasapisteiden kohdalla Verrataan kokonais saalista ja kasvatetaan sijoitusta tarpeen mukaan. Jos Pisteet ja saalis on useammalla kilpailijalla sama, määräytyy sija kirjausjärjestyksen mukaan.

Myös pisteidenlasku on hoidettu melko vaikealla kaavalla:

Minulla on toisella välilehdellä nk. sarjataulukko:


Miten saisin tuon järjestettyä sijoituksen mukaan siten, että järjestys päivittyisi sitä mukaa, kun punnitus tuloksia kirjataan kauden mittaan.

 

[VoNpo]

Tarvitsisin macron kanssa apuja. Olen saanut kasattua rivejä piilottavan macron, mutta tuntuu siltä että macro aktivoituu jokaisella nuolinäppäimen painalluksella tehden taulukon käyttökokemuksesta tahmaista. Onko macroa mahdollista muokata siten, että se aktivoituisi vain jos määriteltyihin soluihin tulee muutos? Macron tulisi kuitenkin toimia automaattisesti ilman manuaalista macron ajamista.

Aiempaa kokemusta macrojen tekemisestä ei ole, joten "koodi" saattaa olla hyvinkin ala-arvoista. Kyseisiä rivejä on tarkotus lisätä huomattavasti tuosta 3 osion erimerkistä.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'Osio 1
If Range("G248").Value = 0 Then
Rows("250:279").EntireRow.Hidden = True
ElseIf Range("G248").Value = 1 Then
Rows("250:279").EntireRow.Hidden = False
End If

'Osio 2
If Range("G281").Value = 0 Then
Rows("283:310").EntireRow.Hidden = True
ElseIf Range("G281").Value = 1 Then
Rows("283:310").EntireRow.Hidden = False
End If

'Osio 3
If Range("G312").Value = 0 Then
Rows("314:341").EntireRow.Hidden = True
ElseIf Range("G312").Value = 1 Then
Rows("314:341").EntireRow.Hidden = False
End If

End Sub

 

[Overij]

Tarvitsisin macron kanssa apuja. Olen saanut kasattua rivejä piilottavan macron, mutta tuntuu siltä että macro aktivoituu jokaisella nuolinäppäimen painalluksella tehden taulukon käyttökokemuksesta tahmaista. Onko macroa mahdollista muokata siten, että se aktivoituisi vain jos määriteltyihin soluihin tulee muutos? Macron tulisi kuitenkin toimia automaattisesti ilman manuaalista macron ajamista.

Aiempaa kokemusta macrojen tekemisestä ei ole, joten "koodi" saattaa olla hyvinkin ala-arvoista. Kyseisiä rivejä on tarkotus lisätä huomattavasti tuosta 3 osion erimerkistä.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'Osio 1
If Range("G248").Value = 0 Then
Rows("250:279").EntireRow.Hidden = True
ElseIf Range("G248").Value = 1 Then
Rows("250:279").EntireRow.Hidden = False
End If

'Osio 2
If Range("G281").Value = 0 Then
Rows("283:310").EntireRow.Hidden = True
ElseIf Range("G281").Value = 1 Then
Rows("283:310").EntireRow.Hidden = False
End If

'Osio 3
If Range("G312").Value = 0 Then
Rows("314:341").EntireRow.Hidden = True
ElseIf Range("G312").Value = 1 Then
Rows("314:341").EntireRow.Hidden = False
End If

End Sub

Tuo koodi ajetaan aina, kun taulukon valittu solu vaihtuu eli esim. juurikin silloin kun nuolinäppäimillä liikutaan. "_SelectionChange" funktion (nojoo, Excelin tapauksessa subproseduurin...) nimessä ilmaisee, että funktio suoritetaan aina tuon tapahtuman (eventin) lauetessa. Parempi tapahtuma tässä tapauksessa olisi Change, jolloin koodi suoritetaan vain kun taulukon sisältö muuttuu.

Vaihda:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Tähän:

Private Sub Worksheet_Change(ByVal Target As Range)

EDIT: Jos noita osioita tulee tosiaan paljon lisää, niin ihan jo selkeyden puolesta lähtisin tuota jatkojalostamaan. Nythän tuossa on pelkkää toistoa. Tietyn solun arvo ohjaa tiettyjen rivien näkyvyyttä eli näistä voisi muodostaa Key-Value-parin. Eventin parametristä Target saadaan muuttunut solu ja sen perusteella voidaan piilottaa tai näyttää tietyt rivit.

Jotenkuten tämän mallisella menisin:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim r As Range
    Dim c As New Collection
    
    ' Lisätään Collectioniin rivit ja niitä ohjaavan solun absoluuttinen osoite
    c.Add Rows("250:279"), "$G$248"
    c.Add Rows("283:310"), "$G$281"
    c.Add Rows("314:341"), "$G$312"
    
    ' Katsotaan onko muuttunut solu Collectionissa
    On Error Resume Next
    Set r = c(Target.Address)
    If Err.Number <> 0 Then
        Err.Clear
        Exit Sub
    End If
    On Error GoTo 0
    
    ' Muuttuneen solun arvon perusteella piilotetaan tai näytetään rivit
    ' (muutetaan solun arvo booleaniksi ja siitä negaatio)
    r.EntireRow.Hidden = Not CBool(Target.Value)
    
End Sub

 

[VoNpo]

Tuo koodi ajetaan aina, kun taulukon valittu solu vaihtuu eli esim. juurikin silloin kun nuolinäppäimillä liikutaan. "_SelectionChange" funktion (nojoo, Excelin tapauksessa subproseduurin...) nimessä ilmaisee, että funktio suoritetaan aina tuon tapahtuman (eventin) lauetessa. Parempi tapahtuma tässä tapauksessa olisi Change, jolloin koodi suoritetaan vain kun taulukon sisältö muuttuu.

Vaihda:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Tähän:

Private Sub Worksheet_Change(ByVal Target As Range)

EDIT: Jos noita osioita tulee tosiaan paljon lisää, niin ihan jo selkeyden puolesta lähtisin tuota jatkojalostamaan. Nythän tuossa on pelkkää toistoa. Tietyn solun arvo ohjaa tiettyjen rivien näkyvyyttä eli näistä voisi muodostaa Key-Value-parin. Eventin parametristä Target saadaan muuttunut solu ja sen perusteella voidaan piilottaa tai näyttää tietyt rivit.

Jotenkuten tämän mallisella menisin:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim r As Range
    Dim c As New Collection

    ' Lisätään Collectioniin rivit ja niitä ohjaavan solun absoluuttinen osoite
    c.Add Rows("250:279"), "$G$248"
    c.Add Rows("283:310"), "$G$281"
    c.Add Rows("314:341"), "$G$312"

    ' Katsotaan onko muuttunut solu Collectionissa
    On Error Resume Next
    Set r = c(Target.Address)
    If Err.Number <> 0 Then
        Err.Clear
        Exit Sub
    End If
    On Error GoTo 0

    ' Muuttuneen solun arvon perusteella piilotetaan tai näytetään rivit
    ' (muutetaan solun arvo booleaniksi ja siitä negaatio)
    r.EntireRow.Hidden = Not CBool(Target.Value)

End Sub

Jostain syystä kun "Worksheet_SelectionChange" muuttaa "Worksheet_Change" lakkaa macro kokonaan toimimasta. Myöskään tuo alla oleva ei toimi, olisikohan sama syy?
Soluissa joilla olisi tarkoitus ohjata piilotusta on kaava =JOS(C248>1;1;0) mikäli tällä on jotain merkitystä.

EDIT: Eli klikkaan excel tiedoston haluttua taulukkoa hiiren oikealla näppäimellä ja avautuvasta valikosta "näytä koodi". Avautuvasta ikkunasta klikkaan vasemmasta reunasta taulukon nimeä johon haluan macron lisätä. copy-pastella koodi avautuneeseen ruutuun ja exceltiedoston tallennus. Meneekö oikein?

 

[Overij]

Jostain syystä kun "Worksheet_SelectionChange" muuttaa "Worksheet_Change" lakkaa macro kokonaan toimimasta. Myöskään tuo alla oleva ei toimi, olisikohan sama syy?
Soluissa joilla olisi tarkoitus ohjata piilotusta on kaava =JOS(C248>1;1;0) mikäli tällä on jotain merkitystä.

EDIT: Eli klikkaan excel tiedoston haluttua taulukkoa hiiren oikealla näppäimellä ja avautuvasta valikosta "näytä koodi". Avautuvasta ikkunasta klikkaan vasemmasta reunasta taulukon nimeä johon haluan macron lisätä. copy-pastella koodi avautuneeseen ruutuun ja exceltiedoston tallennus. Meneekö oikein?

Noin sen pitäisi mennä. En osaa nyt suoriltaan sanoa mikä tuossa voisi mättää Taulukon koodiosiossa ei varmaan ole mitään muuta koodia, kun tuon liität sinne?

 

[VoNpo]

Noin sen pitäisi mennä. En osaa nyt suoriltaan sanoa mikä tuossa voisi mättää Taulukon koodiosiossa ei varmaan ole mitään muuta koodia, kun tuon liität sinne?

Ei ole muuta koodia. Ei myöskään muissa taulukoissa.

 

[Marquette]

Periaatteessa riittäis että saisi ne luvut 1-6 kahteen alekkaiseen kenttään siten että ei voisi olla sama numero. Eikö tälle muka löydy mitään keinoa, pitääkö tässä vain random numberg generatorilla lähteä pelaileen.

Kun ei oikein tarkkaan tiedä, mitä olet tekemässä niin väänsin huvikseni tuommoisen, joka arpoo satunnaisparin, missä parin jäsenet ovat aina eri numeroita ... ja toimii vanhemmallakin excelillä. Idea on siis, että arvotaan randbetween:illä luku 1..30 ja haetaan sitten sitä vastaava pari, pari-listasta on karsittu pois 1 & 1, 2 & 2 jne.

 

[Marquette]

Periaatteessa riittäis että saisi ne luvut 1-6 kahteen alekkaiseen kenttään siten että ei voisi

Tuossa on lyhyempi versio samasta, vlookupit lähti pois

 

[Overij]

Ei ole muuta koodia. Ei myöskään muissa taulukoissa.

Ihan testinä voisi kokeilla laukeaako tuo Change-event ollenkaan, pelkästään tämmöinen pätkä taulukon koodiin:

Private Sub Worksheet_Change(ByVal Target As Range)
    MsgBox "Solu muuttui"
End Sub

Muutat jotain taulukon solua ja pitäisi tulla popuppi. Jos ei niin menee jo mystiseksi.

 

[VoNpo]

Ihan testinä voisi kokeilla laukeaako tuo Change-event ollenkaan, pelkästään tämmöinen pätkä taulukon koodiin:

Private Sub Worksheet_Change(ByVal Target As Range)
    MsgBox "Solu muuttui"
End Sub

Muutat jotain taulukon solua ja pitäisi tulla popuppi. Jos ei niin menee jo mystiseksi.

Tämä koodin pätkä toimii moitteitta.

EDIT:
Nyt sain tuon oman koodinpätkän toimimaan "Private Sub Worksheet_Change(ByVal Target As Range)" muodossa.
Aktivointi vaatii sen, että johonkin soluun tekee käsin muutoksen. Kaavan tekemä muutos ei itsessään aktivoi macroa kuten " Private Sub Worksheet_SelectionChange(ByVal Target As Range) " jollin aktivointiin riitti se, että klikkasi taulukkoa jostain kohdasta. Tosin tällöin nuolella liikkuminen aiheutti hidastelua kun macro päivittyi jokaisella klikkauksella.

EDIT2:
Tekemäsi versio jossa collectioneja toimii, jos ohjaavaan soluun vaihtaa käsin 1 ja 0 vuorotellen. Solussa olevan JOS kaavan mukaisesti muuttuva 1 tai 0 ei kuitenkaan triggeröi macroa.
Tulkitseeko solun kenties vain kaavana, eikä kaavan loppusummaa numerona?

 

[masaf2]

Onks jollain hajua, miten saa VBA-makrot toimimaan verkkoasemalla sijaitsevassa taulukossa? Tullut Win10 ja Office -tietoturva niin hyväksi, ettei toimi kuin työpöydälle tiedoston siirtämällä. Synologyn NASsilta ei toimi ei sitte mitenkään, vaikka mitä luotettavuuskeskusjuttuja räpeltäisi ja regedittiä jne...

 

[slarti]

Onks jollain hajua, miten saa VBA-makrot toimimaan verkkoasemalla sijaitsevassa taulukossa? Tullut Win10 ja Office -tietoturva niin hyväksi, ettei toimi kuin työpöydälle tiedoston siirtämällä. Synologyn NASsilta ei toimi ei sitte mitenkään, vaikka mitä luotettavuuskeskusjuttuja räpeltäisi ja regedittiä jne...

Luottamiskeskuksessa ota luotetut tiedostot käyttöön ja tee polusta jossa tiedosto sijaitsee luotettu. Sitten tietysti kaikki mahdolliset VBA:han liittyvät jutut myös käyttöön luottamiskeskuksen asetuksissa vaikka kuinka niitä ei suositella.

 

[masaf2]

Luottamiskeskuksessa ota luotetut tiedostot käyttöön ja tee polusta jossa tiedosto sijaitsee luotettu. Sitten tietysti kaikki mahdolliset VBA:han liittyvät jutut myös käyttöön luottamiskeskuksen asetuksissa vaikka kuinka niitä ei suositella.

Ei valitettavasti enää onnaa tuo uusien päivitysten kera Toimi KB5015424 ja KB5014699 asti ihan mallikkaasti eli viikko sitten MS särki nuo vba-makrot verkosta toimimattomiksi (paitsi paikalliselta asemalta avattuna).

 

[Theemy]

Terve, mitenkäs tämmöinen melko simppeliltä vaikuttava päivittäisten minimi- ja maksimiarvojen haku tuntisarjasta onnistuu? Itse en nyt jostakin syystä osaa ajatella ja saa toimimaan. Erinäisillä loogisilla ja hakukaavoilla koittanut ratkaista . Aineistossa on siis jokaiselle vuoden tunnille hinta, joista pitäisi saada haettua päivittäiset 3 alinta hintaa sekä 5 korkeinta hintaa.

 

[Marquette]

=LARGE(A1:A99;1) antaa suurimman
=LARGE(A1:A99;2) toiseksi suurimman
=SMALL( ... pienimmän

 

[Theemy]

=LARGE(A1:A99;1) antaa suurimman
=LARGE(A1:A99;2) toiseksi suurimman
=SMALL( ... pienimmän

Niin tuolla saan koko joukon suurimmat ja pienimmät arvot. Tarkoituksena on löytää arvot erikseen jokaiselta päivältä.

 

[Marquette]

No laita tuohon A1:A99 tilalle yksi päivä

 

[Theemy]

No laita tuohon A1:A99 tilalle yksi päivä

Tässä ongelmana on päiviä vastaavan alueen määrittely. En viitsisi 365 kertaa manuaalisesti määrittää väliä.

 

[edup]

Terve, mitenkäs tämmöinen melko simppeliltä vaikuttava päivittäisten minimi- ja maksimiarvojen haku tuntisarjasta onnistuu? Itse en nyt jostakin syystä osaa ajatella ja saa toimimaan. Erinäisillä loogisilla ja hakukaavoilla koittanut ratkaista . Aineistossa on siis jokaiselle vuoden tunnille hinta, joista pitäisi saada haettua päivittäiset 3 alinta hintaa sekä 5 korkeinta hintaa.

Jos haluat kaavoilla tuon tehdä, niin esim. tuohon H3-soluun:
=SMALL(IF($F3=A3:A;B3:B); 1)

Eli tuolla IF:llä haet arrayn niistä arvoista, mitkä osuu ko. päivälle, ja sitten SMALL palauttaa sulle sen 1. pienimmän arvon.

Toinen vaihtoehto on tietty tehdä pivot-taulu, mitä kannattaa opetella hyödyntämään jos tämänkaltaisia aggregointeja tarvitsee tehdä enemmänkin.