Kuinka toteuttaisitte sen että, solun kaavan laskeman lukeman päälle voisi kirjoittaa oman lukeman, jolloin kaava ohitettaisiin väliaikaisesti? Kuitenkin niin että, muita lähtöarvoja muutettaessa kaava palaisi taas takaisin käyttöön.
Kaavan arvon "pakkosyöttö"
Anonyymi
542375
0Äänestä
Muut lukivat myös
Ketjusta on poistettu 6 sääntöjenvastaista viestiä.
Odottaa vastausta
Näihin keskusteluihin ei ole vielä vastattu. Ole ensimmäinen.
Vastaukset 54
Vanhimmat
Ei ole mahdollista toteuttaa ilman makroja, koska arvon/kaavan syöttäminen soluun hävittää aina aiemman arvon/kaavan. Jos sen syötetyn arvon/kaavan poistaa, ei se aiempi ilmesty takaisin vaan solu jää tyhjäksi.
Viruksella.
Ei onnistu edes viruksella, vaan tarvitset nimenomaan makron.
Vaihdoin UBUNTUUN! Windows kun saastuu aina.
Kopioi sarake viereiseen sarakkeeseen ja muokkaa sitä manuaalisesti. Vaihtoehtoisen tuloksen näet alareunassa. Kun et enää tarvitse, voit poistaa vaihtoehtoisen sarakkeen.
Ei tosiaan onnistu suoraan, mutta mutkan kautta käyttämällä eri solua ylikirjoitukseen kyllä. Eli kaavassa pitää tarkastaa onko "apusoluun" syötetty arvo, ja jos on, niin käytetään sitä. Muussa tapauksessa sitten toista vaihtoehtoa.
Ei sekään toimi, kun jos kirjoitat siihen soluun, jossa kaava on, kaava katoaa.
Käytä IF - operaattoria valitsemaan kaavan ja apusarakkeen sisältämän arvon välillä. Testaa ifillä onko apusarakkeessa kelvollinen numero.
Ei se auta vaikka laittaisit seitsemän iffiä, kun se kaava ylikirjoittuu kun syötät siihen samaan soluun, jossa kaava on, jonkin muun arvon.
Jos myöhemmin poistat sen syöttämäsi arvon, jäljelle jää tyhjä solu, ei se entinen kaava, joka haki oletusarvon apusolusta.
Eipä sitä lukua samaan soluun olla syöttämässä vaan apusoluun. Iffilä tsektaan vaan kumpaa arvoa käytetään. Jos apusolu on tyhjä, niin käytetään kaavalla laskettua arvoa, muutoin apusolun arvoa.
Aaa. Nyt ymmärsin, mitä yritit sanoa. Juu, tuo kyllä toimii, muttei vastaa aloittajan esittämään tarpeeseen.
Ainoa tapa tehdä se, mitä aloittaja halusi, on käyttää makroja, mutta jos se on ok, että tiedot syötetään yhteen soluun (apusoluun) ja luetaan toisesta (siitä jossa on kaava), niin silloin tuo ehdotuksesi toimii hyvin.
Pahoittelut ymmärtämättömyydestäni.
Otahan välillä kauhasessiosi.
Tein makron, mutta suomi24 sensuroi sen jatkuvasti. Se tulee ko. välilehden muduliin ja aktivoituu aina kun jotain solua muutetaan. Toimii seuraavasti: Jos johonkin soluun kirjoitetaan makrossa määritelty komento, solu tyhjätään ja siihen tulee tyhjä kommentti. Jos soluun kirjoitetaan jotain muuta ja siinä on tyhjä kommentti, solun sisältö kopioidaan myös kommenttiin. Jos solu tyhjennetään ja kommentissa on jotain, kommentin sisältö kopioidaan soluun.
En ymmärrä miten tuo toimisi?
Eihän sitä alkuperäistä kaavaa tallennneta mihinkään?
Kaava talletetaan kommenttiin. Kokeile.
No nyt pääsin jyvälle.
Kaava kirjoitetaan koodilla tyhjättyyn soluun, ja ylikirjoitus pitää erikseen deltoida pois.
Tuo on vähän hämäävää kun ei voi nähdä mitenkään onko solussa kaavan laskema arvo, vaiko käsin syötetty lukema.
Onko jotain funktiota joka palauttaisi tiedon siitä onko solussa kaava vai pelkästään tekstiä? Ehdollisella muotoilun avulla voisi solun pohjavärillä ilmaista asian.
Ehdollisella muotoilulla voi muuttaa väriä, jos solussa ei ole kaavaa, mutta se ei tunnista eroa, silloin jos soluun kirjoitettu erilainen kaava, tai alkuperäinen olikin paljas luku. Tämä muuttaa pohjavärin keltaiseksi, kommentissa on jotain ja solussa on jotain muuta kuin kommentissa.
Private Sub Worksheet_Change(ByVal Target As Range)
Const komento As String = "xx" ' tähän mikä tahansa sopiva merkkijono
Dim solu As Range
For Each solu In Target
If solu.FormulaR1C1 = komento Then
Application.EnableEvents = False
solu.FormulaR1C1 = ""
Application.EnableEvents = True
On Error Resume Next
solu.Comment.Delete
solu.AddComment Text:=""
ElseIf Not solu.Comment Is Nothing Then
If solu.Comment.Text = "" Then
solu.Comment.Text solu.FormulaR1C1
Else
If solu.FormulaR1C1 = "" Then
solu.FormulaR1C1 = solu.Comment.Text
solu.Interior.Color = xlNone
Else
solu.Interior.Color = RGB(255, 255, 0)
End If
End If
End If
Next
End Sub
Löytyy funktio jo Excel 4 versiosta alkaen... ;-)
GET.CELL(type_num, reference)
manuaalissa alkaen sivulta 339
manuaali funktioihin
https://exceloffthegrid.com/download/4095/
Keep EXCELLing
@Kunde
Joo, nuo funktiot toimivat vain makroissa, ei taulukon puolella.
Nyt tämä makro alkaa olla jo valmis "tuotantoon".
Jos solulla on joku pohjaväri ennestään, siihen ylemmäksi voi editoida paluuvärin RGB-koodin.
Voi myös miettiä, voisiko ylikirjoituksen deletointi olla automaattinen, kun jotain muuta solua muutetaan?
höpö höpö...
Takuulla toimii taulukossa, pitää vaan osata käyttää niitä.
https://www.dropbox.com/s/5z984r7fqx434wy/hönö2.mp4?dl=0
Keep EXCELing
@Kunde
Jos noita funktiota tarjoaa kaavariville, saatta lopputulema olla; #NAME?
Katso linkittämäni video, siinä näet miten juurikin GET.CELL funktiota käytin tutkimaan, onko solussa kaava vai ei.
Kuten jo aiemmassa postauksessa mainitsin, pitää tietää miten noita funktioita käytetään. Toki VBA:lla voi aivan hyvin tehdä ton paljon yksinkertaisemmin (xxx.HasFormula), mutta noilla vanhoilla EXCEL4 funktioilla voi tehdä useita juttuja kätevästi ilman VBA:ta ja samallahan oppii käyttämään nimeämistä hyväkseen kaavoissa...i
Vika on tällä kertaa käyttäjässä ;-)
Keep EXCELing
@Kunde
Jos poikkeavan arvon käyttö on niin tilapäistä, että vain jonkin solun muuttaminen palauttaa alkuperäisen sisällön, eikö siinä tapauksessa ole yksinkertaisinta painaa Ctrl Z.
Seuraava versio muistaa ja palauttaa myös entisen pohjavärin. Väri talletetaan kommentin alkuun.
Private Sub Worksheet_Change(ByVal Target As Range)
Const komento As String = "xx" ' tähän mikä tahansa sopiva merkkijono
Dim solu As Range
Dim väri
For Each solu In Target
Application.EnableEvents = False
If solu.FormulaR1C1 = komento Then
solu.FormulaR1C1 = ""
On Error Resume Next
solu.Comment.Delete
solu.AddComment Text:=""
ElseIf Not solu.Comment Is Nothing Then
If solu.Comment.Text = "" And solu.FormulaR1C1 <> "" Then
If solu.Interior.Pattern = xlNone Then väri = "" Else väri = solu.Interior.Color
solu.Comment.Text väri & ";" & solu.FormulaR1C1
Else
If solu.FormulaR1C1 = "" Then
pos = InStr(solu.Comment.Text, ";")
If pos = 1 Then
solu.Interior.Pattern = xlNone
Else
solu.Interior.Color = Left(solu.Comment.Text, pos - 1)
End If
solu.FormulaR1C1 = Mid(solu.Comment.Text, pos 1)
Else
solu.Interior.Color = RGB(255, 255, 0)
End If
End If
End If
Application.EnableEvents = True
Next
End Sub
Minkä virheen käyttäjä on tässä tehnyt, kun nimivirhettä pukkaa?
https://ibb.co/ymC3rSd
Käyttää enkkuversion funktionimiä suomiversiossa...
suomiversiossa viittaus =NOUDA.SOLU(48;SIIRTYMÄ(EPÄSUORA("rs";EPÄTOSI);0;-1))
Keep EXCELing
@Kunde
Luulen, että tuo edellinen kommentoija vain luuli puhuttavan VBA-funktioista. Niitähän nimenomaan ei voi käyttää soluissa, vaan vain makroissa.
Luulo ei ole tiedon väärti...
moduuliin funktio
Function OnkoKaava(Solu As Range) As Boolean
OnkoKaava = Solu.HasFormula
End Function
ja jos tsekataan esim. onko solussa D4 kaava
niin vastaussoluun kaava =OnkoKaava(D4)
Keep EXCELing
@Kunde
Nyt toimii tuokin :) Tuota "rc" merkkijonoa ei käy suomentaminen, ei toiminut "rs"-muodossa.
Siis mitä funktioita nämä oikein ovat, ovat vieläpä suomennettujakin? Onko niistä jossain luetteloa excelissä tai muualla?
Itsellä EXCEl 2021 ja siinä vaihdoin kielen suomeksi ja enkkuvesion rc ainakin piti muuttaa rs, jotta toimi suomiversiossa.
Kandee ekana kokeilla enkkuversiolla ja tallentaa ja sitten muuttaa kielen suomeksi ja testailee.
Dokumentaatio on vähissä, mutta 1990- luvulla noilla mentiin aika pitkällekin ;-)
Nykyäänhän noi samat voi tehdä VBA:lla
Funktiot ovat Excel 4 version makrofunktioita ja ne toimivat edelleen jopa uusimmissa versioissakin.
Tuolla linkin sivulla hiukan asiasta ja löytyy se latauslinkki manuaalinkin, sama minkä aiemmin jo olen postannut
https://exceloffthegrid.com/using-excel-4-macro-functions/
Minulla excel 2007 ilmoitti viittausvirhettä "rs":llä vaikka se on suomenkielinen.
Tämä ei toimi minulla, tulee "run-time error 5" virheilmoitus kun ylikirjoituksen poistaa.
Rivillä; solu.Interior.Color = Left(solu.Comment.Text, pos - 1)
kokeile absoluutisella viittauksilla, nyt tsekkaa solua F5
=NOUDA.SOLU(48;!$F$5)
=NOUDA.SOLU(48;!F5)
Mitä on kommentissa ja mikä oli solun pohjaväri?
Molemmilla noista toimii, samoin sillä aiemmallakin kun laittaa "rc".
Voiko muuten noita, mitä ne nyt nimeltään ovatkaan, muokata sellaiseksi että parametri annettaisiin vasta käytettäessä kaavariville? Nythän ne ovat sellaisia valmiita täsmäfunktioita.
"Invalid procedure or argument."
Pohjaväri oli vaaleanvihreä 204,255,204
Tuo oli saamasi virheilmoitus. Tarkoitin sitä solun kommenttia, jonka makro teki ja mihin se talletti alkuperäiset tiedot. Siinä pitäisi olla 13434828; ja perässä se kaava R1C1-muodossa.
Jaa se, ei siinä ole tuollaista pitkää numerosarjaa lainkaan, ainoastaan tämä =R[-1]C[-2] RC[-2]
Hoksasin juuri että olisi pitänyt käyttää sitä "xx" koodia että kommenttikin olisi päivittynyt uusiksi. No, lisäsin käsin komenttiin sen väri koodin, nyt tämä viimeisin versiokin toimii. :)
Nuo makrothan toimii samoin kuin normikaavatkin eli voit jossitella yms ihan normitapaan, mutta EXCEL4 makrot pitää olla tehtynä nimiin tai sitten MacroSheetille.koko proseduuri
Kandee tsekata youtubesta missä ainakin tää hemmo niitä esittelee(useampikin video)
https://www.youtube.com/watch?v=b1S4UJsL62w
Oletus on, että kommentin alussa on väriä vastaava numero ja puolipiste tai pelkkä puolipiste, mutta makro ei tee tarkistuksia. Vanhan makron tekemässä kommentissa ei ole näin, joten tulee virhe.
MS on tae sonnasta!
Yksi tapa on tehdä makro, joka "kuuntelee" soluja, joiden muuttaminen pakottaa kohdesoluun kaavan. Kaava siis kirjoitetaan makrolla siihen samaan soluun mikä oli ylikirjoitettu käsin.
Tuossa linkissä on asia selostettu kohtuullisen selvästi.
https://www.thespreadsheetguru.com/blog/trigger-your-vba-macros-to-run-based-on-specific-cell-value-change
https://www.dropbox.com/s/xfo05g7pkpe3hlj/Hönö.mp4?dl=0
Keep EXCELing
@Kunde
Linkki ei toimi kun siinä on skandeja. Pitäisi olla "hono".
Linkki on toimiva, mutta suomi24 on kökkö. Ellei aukea suoraan (minulla aukeaa), maalaa se ja valitse sitten hiiren oikealla avautumaan uuteen välilehteen.
Kiinnostaisi tietää, mitä tällä pyritään samaan aikaan. Tuntuu varsin oudolta, jos luku muuttuu arvaamattomasti takaisin. Itse kopioin koko taulukon ja sitten tekisin KO. muutoksen kopioon.
Tuon tyypillinen tarkoitus on "entä jos kurkistus" todellisella parametrilla, jossain laskennan välivaiheessa. Esim. 3 tai 4 tuuman naula vs. laskennallinen 3,48 tuuman naula. 😁
Yksi käyttötarkoitus voisi olla lähtötietojen syöttö. Jos jotkin arvot ovat yleensä samoja, tai ne saadaan samalla tavalla aiemmin syötetyistä tiedoista, soluissa on kaava, minkä voi tarvittaessa ylikirjoittaa hukkaamatta alkuperäistä.
Tuossa nyt tosi simppeli makro (oli käytössä videolinkissäni aiemmin)
soluhin E3 ja G3 syötetään tietoja ja vastaussolussa F4 summataan ne.
Vaihtaa vastaussolun väriä sen mukaan onko solussa kaava vai ei ja palauttaa originaali kaavan jos solun tyhjentää tai E3 tai G3 solujen arvo muuttuu.
Tosta nyt voi räätälöidä vaikka mitä pikku vaivalla... ;-)
koodi ko. taulukon moduuliin...
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Union(Range("E3"), Range("G3"))) Is Nothing Then
Range("F4").FormulaR1C1 = "=R[-1]C[-1] R[-1]C[1]"
Range("F4").Interior.Color = 5287936
End If
If Not Intersect(Target, Range("F4")) Is Nothing Then
If Target.HasFormula Then
Range("F4").Interior.Color = 5287936
Else
If Target = "" Then
Target.FormulaR1C1 = "=R[-1]C[-1] R[-1]C[1]"
Else
Range("F4").Interior.Color = 255
End If
End If
End If
End Sub
Keep EXCELing
@Kunde
Nyt on vähän liian automaattista, kun makro osaa kirjoittaa laskentakaavatkin käyttäjän puolesta valmiiksi taulukkoon...
Ei vainenkaan, tällä hoituu homma sujuvammin kuin ylikirjoitusta tarvitse välttämättä erikseen deletoida, jos päivittää lähtötietoja.
Union funktioon vain ne lähtötietosolut /-alueet jotka vaikuttavat ylikirjoitettavaan kaavaan. Eikä sitä mahdollisesti pitkää kaavaa tarvitse R1C1 muodossa makroon kirjoitella, vaan soluviittaus varsinaiseen kaavaan, joka on näkymättömissä.
Kuten sanoin, räätälöinti helppoa... ;-)
Riippuu ihan tarpeesta, mikä toteutustapa on sopivin.