HotXLS kääntää R1C1-viittausmuotoiset kaavat TXLSCalculator.GetCompiledFormulaR1C1-metodilla. Se hyväksyy muodot kuten R2C3 (absoluuttinen), R[-1]C[2] (suhteellinen poikkeama) ja RC (nykyinen solu), muuntaa ne A1-esitykseen kaavan sijaintisolun rivin ja sarakkeen mukaan ja syöttää tuloksen samalle kääntäjälle, joka käsittelee tavalliset A1-kaavat. Delphin ja C++Builderin koodissa, joka tuottaa saman kaavan sadoille riveille, tämä menetelmä poistaa kokonaisen luokan merkkijonojen yhdistämiseen liittyviä virheitä (string-concatenation bugs)
Tämä virheluokka on tuttu kaikille, jotka ovat täyttäneet sarakkeita ohjelmallisesti. Rivien läpi käytävässä silmukassa rakennetaan jokaiselle riville A1-kaavamerkkijono muodossa Format('D%d*E%d', [Row, Row]). Jokainen iteraatio liittää rivinumerot tekstiin, ja rivinumerot ovat ainoa muuttuva osa. Jos teet poikkeamassa pienenkin virheen, sekoitat 0-pohjaisen silmukkamittarin 1-pohjaisiin A1-rivinumeroihin tai siirrät tietoaluetta yhdellä otsikkorivillä, jokainen sarakkeen kaava osoittaa väärälle riville. Mitään poikkeusta ei tapahdu, luvut ovat vain väärin. Kaava, jota todellisuudessa tarkoitit: "kerro kaksi vasemmalla puolellani olevaa solua keskenään", ei koskaan viittaa rivinumeroon, ja R1C1-esitystapa mahdollistaa sen kirjoittamisen juuri näin
Mitä on R1C1-esitystapa ja milloin sitä pitäisi käyttää?
R1C1-esitystapa osoittaa soluun rivi- ja sarakeluvun avulla sarakekirjaimen ja rivinumeron sijaan, ja se merkitsee suhteelliset viittaukset selkeinä poikkeamina kaavasolusta. R2C3 on absoluuttinen solu rivillä 2 sarakkeessa 3, jonka A1 kirjoittaa muotoon $C$2. R[-1]C[2] on yksi rivi ylöspäin ja kaksi saraketta oikealle kaavan sijainnista. RC on itse kaavasolu. Hakasulkeilla merkityt poikkeamat ovat se juttu: suhteellinen viittaus R1C1-muodossa säilyy samana riippumatta siitä, mihin soluun se sijoitetaan, kun taas saman viittauksen A1-esitys muuttuu jokaisella rivillä
Tämä esitystapa osoittaa arvonsa erityisesti yhdessä yleisessä skenaariossa: mallipohjien luonnissa, jossa sama suhteellinen kaava on sijoitettava tietoalueen jokaiselle riville. A1-muodossa kaavateksti on luotava uudelleen jokaiselle riville. R1C1-muodossa teksti pysyy vakiona. Tämä on myös lähempänä sitä, miten taulukkolaskentatiedostot toimivat sisäisesti: jaetut kaavatietueet tallentavat suhteelliset viittaukset rivi- ja sarake-eroina isäntäsolusta (host cell), joten rivikohtainen A1-merkkijono on jotain, mitä koodisi luo vain jotta jäsennin purkaisi sen heti takaisin poikkeamiksi. R1C1 ohittaa tämän edestakaisen kierroksen. Vuorovaikutteisissa, ihmisen kirjoittamissa kaavoissa A1 säilyy luonnollisena valintana, minkä vuoksi se on oletuksena kaikkialla HotXLS-komponentissa
Miten HotXLS kääntää R1C1-kaavan?
HotXLS tarjoaa tämän toiminnon kahdella tasolla, jotka lisättiin versiossa v2.175.0. TXLSCalculator.GetCompiledFormulaR1C1(UncompiledFormula: String; SheetID, CurRow, CurCol: Integer): TXLSCompiledFormula on useimmiten käytetty: se palauttaa käännetyn kaavan valmiina arvioitavaksi, aivan kuten sen A1-sisarus GetCompiledFormula, mutta kahdella lisäparametrilla, jotka nimeävät kaavalle kuuluvan solun 0-pohjaisen rivin ja sarakkeen. Sen alla TXLSFormula.GetCompiledR1C1 tuottaa raa'an syntaksipuun, ja erillinen funktio R1C1ToA1(const AFormula: String; CurRow, CurCol: Integer): String suorittaa varsinaisen esitystapamuunnoksen. Piirtoputki on tietoisen yksinkertainen: käännä R1C1-teksti vastaavaksi A1-tekstiksi käyttäen isäntäsolun koordinaatteja, ja käännä sitten A1-teksti olemassa olevan moottorin läpi — eli saman moottorin, joka ratkaisee nimettyjä alueita ja taulukoiden välisiä viittauksia sekä välittää mukautettuja taulukkolaskentafunktioita
Koska muunnos tapahtuu ennen kääntämistä, kaikki sen jälkeiset vaiheet toimivat aivan kuin olisit itse kirjoittanut A1-kaavan. R1C1ToA1('R2C3', 4, 3) palauttaa arvon '$C$2' isäntäsolusta riippumatta, sillä molemmat koordinaatit ovat absoluuttisia. R1C1ToA1('SUM(R[-3]C[0]:R[-1]C[0])', 4, 3) — eli kaava solussa D5, koska 0-pohjaiset CurRow = 4 and CurCol = 3 viittaavat siihen — palauttaa arvon 'SUM(D2:D4)': poikkeamat ratkaistiin suhteessa riviin 5, sarakkeeseen D ja tuotettiin tavallisina suhteellisina A1-viittauksina. Alueet eivät vaadi erikoiskäsittelyä; kaksoispiste säilyy ja kumpikin päätepiste muunnetaan itsenäisesti
// Kaava sijaitsee solussa D5: CurRow = 4, CurCol = 3 (molemmat 0-pohjaisia)
S := R1C1ToA1('R2C3', 4, 3);
// S = '$C$2' (absoluuttinen rivi ja sarake)
S := R1C1ToA1('SUM(R[-3]C[0]:R[-1]C[0])', 4, 3);
// S = 'SUM(D2:D4)' (poikkeamat ratkaistu suhteessa soluun D5)
S := R1C1ToA1('ROUND(R[-1]C[0], 2)', 4, 3);
// S = 'ROUND(D4, 2)' (ROUND-sanan R-kirjain jätetään rauhaan)Sarakkeen täyttäminen yhdellä suhteellisella kaavalla
Hyöty näkyy silmukassa. Vertaa A1-versiota, joka luo kaavatekstin uudelleen jokaisella iteraatiolla, R1C1-versioon, jossa kaava on vakio ja vain isäntäsolun koordinaatit liikkuvat. Molemmat kääntyvät TXLSCalculator-luokan läpi ja ne arvioidaan metodilla GetValue. Laskimelle annetaan alustuksen yhteydessä takaisinkutsu solujen tarjoamiseen (cell-provider callback), jotta arvioija voi hakea solujen arvot tietolähteestäsi
// A1-tyyli: eri kaavamerkkijono jokaiselle riville
for Row := 1 to 500 do
begin
FormulaText := Format('D%d*E%d', [Row + 1, Row + 1]); // 1-päätteiset A1-rivit
Compiled := Calc.GetCompiledFormula(FormulaText, 0);
// ... arvioi, tallenna, vapauta ...
end;const
AmountFormula = 'RC[-2]*RC[-1]'; // kaksi solua vasemmalle, sama rivi
var
Calc: TXLSCalculator;
Compiled: TXLSCompiledFormula;
Value: Variant;
Row: Integer;
begin
Calc := TXLSCalculator.Create(nil, Provider.GetValue);
try
for Row := 1 to 500 do
begin
Compiled := Calc.GetCompiledFormulaR1C1(AmountFormula, 0, Row, 5);
try
if Calc.GetValue(0, Compiled, Row, 5, Value, 1) = lxOk then
StoreResult(Row, 5, Value);
finally
Compiled.Free;
end;
end;
finally
Calc.Free;
end;
end;R1C1-silmukassa kaavatekstissä ei ole lainkaan rivi-aritmetiikkaa. 'RC[-2]*RC[-1]' tarkoittaa "sama rivi, kaksi saraketta vasemmalle, kerrottuna sama rivi, yksi sarake vasemmalle" niin rivillä 2 kuin rivillä 500. Jos tietoalue myöhemmin siirtyy alaspäin otsikkorivin myötä, kaavavakio ei muutu — vain silmukan rajat muuttuvat. A1-versiossa on kaksi paikkaa, joissa + 1 -korjauksen voi tehdä väärin; R1C1-versiossa niitä on nolla
Mitkä R1C1-muodot muunnin hyväksyy?
R1C1ToA1-muunnin tunnistaa versiossa v2.175.0 dokumentoidut muodot: R[n]C[m] suhteellisille poikkeamille molempiin suuntiin, RnCm absoluuttisille riveille ja sarakkeille, R[-n]C[m] negatiivisilla poikkeamilla, pelkkä RC itse kaavasolulle ja alueet kuten R1C1:R3C3 tai R[-1]C:R[1]C. Rivi- ja sarakeosat jäsennetään toisistaan riippumatta, joten myös sekamuodot kuten R[1]C3 — suhteellinen rivi, absoluuttinen sarake — toimivat. Kirjaimet ovat myös merkkikoosta riippumattomia, joten r[-1]c[2] kääntyy samalla tavalla kuin sen isoin kirjaimin kirjoitettu vastine. Hakasulkeilla merkityt osat muuttuvat ankkuroimattomiksi (suhteellisiksi) A1-koordinaateiksi; pelkät numerot muuttuvat $-ankkuroituneiksi absoluuttisiksi koordinaateiksi
Mielenkiintoinen kysymys on se, miten muunnin välttää kaiken muun sotkemisen kaavassa, sillä R ja C ovat hyvin yleisiä kirjaimia. Sen erottelusääntö perustuu tokeneihin: R-kirjainta pidetään viittauksen alkuna vain silloin, kun sitä ei edellä toinen kirjain, ja ehdokkaan on sen jälkeen jäsennyttävä kokonaan — valinnainen riviosa, pakollinen C, valinnainen sarakeosa — tai muuten teksti palautetaan ennalleen. Siksi ROUND(R[-1]C[0], 2) muuntaa vain sisäisen viittauksen: ROUND-sanan R-kirjainta seuraa O-kirjain numeron, hakasulkeen tai C-kirjaimen sijaan, joten jäsennys epäonnistuu ja funktion nimi säilyy sellaisenaan. Sama logiikka suojaa myös ROW()-funktiota, ja C-kirjaimella alkavat funktionimet eivät ole koskaan ehdokkaita, sillä vain R voi aloittaa viittauksen. Version v2.175.0 julkaisutiedot kertovat, että myös merkkijonoliteraalit ja tunnisteet ohitetaan; silti, jos kaavasi lainausmerkeissä oleva teksti sattuisi sisältämään juuri R1C1-viittauksen muotoista tekstiä, käännetty tulos on hyvä tarkistaa kertaalleen ennen tuotantoon viemistä
Koordinaattikannat ja ankkurointiyksityiskohdat, jotka on hyvä tietää
Tässä rajapinnassa kohtaa kaksi eri käytäntöä, ja niiden erillään pitäminen auttaa välttämään ainoan todellisen sudenkuopan. GetCompiledFormulaR1C1-metodin parametrit CurRow ja CurCol ovat 0-pohjaisia laskimen rajapintaa noudattaen, kun taas itse esitystavan sisällä olevat numerot ovat 1-pohjaisia Excelin näyttöä vastaten: R2C3 on rivi 2, sarake 3, mikä tarkoittaa solua $C$2, ei solua $D$3. Jos silmukkamittarisi on jo 0-pohjainen, välität sen sellaisenaan parametrina CurRow; + 1 -muunnos tapahtuu muuntimen sisällä, ei koodissasi
Vielä yksi yksityiskohta on tärkeä, jos käännetty kaava säilyy pidempään kuin solu, jolle se käännettiin. Kun jätät rivi- tai sarakeosan pois — muodoissa RC[-1] tai R[2]C — puuttuva koordinaatti kohdistuu isäntäsoluun ja tuotetaan absoluuttisena, $-ankkuroituna koordinaattina muunnetussa A1-tekstissä. Arviointihetkellä tällä ei ole merkitystä, sillä arvo on kummallakin tavalla sama. Mutta suhteellinen vs. absoluuttinen ankkurointi ratkaisee sen, miten viittaukset siirtyvät, kun rivejä tai sarakkeita lisätään tai poistetaan myöhemmin, mistä kerrotaan aiheeseen liittyvässä artikkelissa kaavaviittausten siirtämisestä. Jos tarvitset koordinaatin säilyvän suhteellisena rakenteellisten muokkausten läpi, kirjoita poikkeama näkyviin — esimerkiksi R[0]C[-1] muodon RC[-1] sijaan — jotta muunnin tuottaa ankkuroimattoman A1-viittauksen
R1C1-kääntäminen on osa kaavasolujen moottoria HotXLS Delphi Excel Component -tuotteessa, yhdessä A1-kääntäjän, uudelleenlaskentagraafin ja yllä esitetyn arviointirajapinnan kanssa. Jos koodisi luo taulukoita suorittamalla kaavasilmukoita sarakkeissa, silmukoiden siirtäminen merkkijonoista kootuista A1-kaavoista yhteen R1C1-vakioon on yksi edullisimmista luotettavuuden parannuksista, mitä voit tehdä