5月 2, 2008

10進数をExcelのカラム表現に変換

Excelのワークシート上の任意のセルをAppleScriptで表記するのに、昔はR1C1形式が用いられていました。行数を「R」のあとに、カラム数を「C」の後に書くだけでよく、R1C1形式はプログラムを書く側にとってひじょうに便利な表記方法でした。

 

しかし最近では、この記述が容易なR1C1形式ではなく、「A1」形式が用いられるようになっており、これがかなりの難物です。できれば、R1C1形式に近い表記が使えると楽ですが……頼みの綱の海外のメーリングリストでも、相互変換については一切触れられていない気配です。

AppleScriptで、縦軸を表すcellオブジェクトと横軸を表すcolumnオブジェクトを使って、

 

set a to formula of cell 3 of column 2 –セル「B3」にアクセス

 

といった表記は可能ですが、範囲指定する場合には残念ながら使えません。何か「A1」形式を使わずに指定する方法はないかと探しまわったものの、あいにく見つかりませんでした。WindowsのExcel VBAの世界では、Excel側にこの相互変換を行う機能があることまでは分ったのですが、この変換機能はどうもMac版のExcel 2008には搭載されていないようです(Windows版とは別の名称で実装されている可能性はあります)。

 

Excel 2004が出たときにこのR1C1形式からA1形式への変更は行われており、10進数からA1形式に変換するAppleScriptのサブルーチンは作ってありました。Excel 2008で表の縦が1,048,576まで、横が16,384まで拡張されたことにともない、検算のために逆の働きをするプログラムを作り動作検証を行ったところ、大きな数を与えると誤りが出ることが判明。この変換ルーチンを仕事で使ったことはなかったものの、早い段階でバグが見つかったことは不幸中の幸い。XLASを本格的にバージョンアップする前に、作り直しておくしかなさそうです。

 

10進数からA1形式に変換するプログラムで一番簡単な方法は、「1 -> A」のような対応表を作っておいて、この対応表を用いて求めるというものです。Excel 2008では横方向には16,384セルが有効であるため、要素数が16,384のリスト型変数を作っておき、実行するたびに読み込んでしまえば、実用上問題はないでしょう。

 

しかし、要素数が16,384もあると……非力なマシンで動かしたときのオーバーヘッドは無視できません。C言語などと違い、なるべくAppleScript上ではデータを細分化して処理するような配慮が必要です(最近のIntel Macだけ相手にするなら問題ないのですが)。また、アプリケーション形式で動かすScriptなら起動時に読み込んでおくだけで済むものの、メニューから呼び出すタイプのスクリプトでは、毎回毎回同じ処理を行わなくてはならず、そうした意味でも表方式は使いたくなかったのです。

 

また、対応表でカタを付けるというのは……何か人としてやりかたが安直すぎるような気もしました。ここは、真っ正面から立ち向かうべきでしょう。

 

……よくよく考えてみると、ExcelのA1形式というのは頭の痛いシロモノです。もともとは、Lotus 1-2-3Multiplan、もしかしたらApple II上のVisiCalcで採用されたものかもしれませんが……どうしてこのような表記が定着してしまったのでしょう? Excel 2008の環境設定で「R1C1参照形式を使用する」という項目もあるのですが、これはあくまで「表示が変わるだけ」であり、マクロ言語からそのようにアクセスできるようになるわけではありません。

 

A1形式のどこがどうおかしいのか、実例をあげてみると……

 

  1 -> A

  2 -> B

  3 -> C

  (中略)

  26 -> Z

 

ここまでは納得できるのですが、次の27は……

 

  AA

 

と表記することになります。ちょっと待って、それじゃあ「A」は数値として考えると1なの? それとも0なの? 

 

10進数で言うならば……「9の次が11」という話になってしまうものでしょうか。自分は理数系ではないため、この手の課題はえらく不得手です。

 

しばし考えたのちに、「実際の値の保持と表示のための表現(エンコーディング)を分離して、桁ごとに別々のものとして考えないとダメだ」と気付き、上位桁と下位桁では「文字表示方式(エンコーディング方式)」が異なるルールで行われていると仮定。さらに、1〜26は別途処理を行うようにしました。ベタな方法ですが、桁ごとに世界を切り分けて考えないと頭がおかしくなりそうです。

 

これで、ようやく納得のいく変換が行えるようになった……と、思いきや、A1形式から10進数への逆変換ルーチンを用いて相互チェックを行ったところ、AZからBAに繰り上がるあたりで動きがおかしいことに気付きました。

 

おかしな現象が発生する原因は、やはり……

 

  「A」が「0」を意味するのか「1」を意味するのかという根本的な問題

 

を放置したままプログラムを組んだことにあるようです。しかし、こんな些細な処理を作るのにかなり時間をかけてしまっていたので、ここで場当たり的な対応を行うことを決意。状態を検出して桁上がりの直前だけ例外処理を行うことにしました。当初、表変換方式を「安直」と非難していた割には軟弱な対応です。

 

これでどうやら、AAからZZまでの2桁の間は正確に変換できるようになりました。3桁(AAA)に移行してしばらくすると桁上がり問題が再燃。泥縄式な対応を再び迫られることになるわけですが、3桁までは考慮しない仕様にして、なんとか乗り切ったのでした。(N)

Leave a comment