- 北の空からみなみへ -
exblog staff

<< 雨が上がって、霧が晴れて、日が射した 私にとっての昭和の歌姫 >>
OpenOffice.org Calc Portable (動的集最新集計表の作成 )
2010年 07月 30日
先日に OpenOffice.org Calc Portable で書いた集計式は、文字列形式で記述した式を必要に応じて再計算させるものでした。

月報や季報などで、締期を迎えてその部分だけ計算をさせる目的には、あのような方法が適切だと思っています。いわば、必要時にコンパイルするみたいな感覚で運用するケースでは、常時(=つまり再計算される度に)式を再評価させるのを避ける工夫の結果、あの方法を私は採用しています。

ただし、日々の状態を刻々と知っておきたい場合もあります。
この動的な計算を、最終日(データの最終行)を取得して算出する方法をこちらに記述します。
例によって、あまり初心者にわかりやすくはないかと思いますが、ご容赦ください。
(説明能力不足なので、そこに配慮しようとすると記事が仕上がらないという自分本位の理由です)


脇道な話ですが、式を書くときに特定のセル範囲に「名前」を設定するやり方を。私は今はほとんどやりません。(昔はけっこうやってみた)
 やめた理由は、式のメンテにかえって苦労した経験からです。
 名前の範囲を壊さないように、挿入や削除に気を使ったり、壊してしまった範囲名の回復や再設定に窮したり、困ったことがよくありましたから。

そして、このようにスタイルを変えて(文字式にしたり、直接の式にしたり、範囲名を使ったり、参照をインデックスにしたり)あれこれやると、「習うより慣れろ」効果が働いてマニュアルを反復して読むよりも関数って使えるようになる。< 無知なやつのいいわけかな ^^;w


ここで示す方法は、次のことが前提となる。

  1. 日付順にレコード(データの行)がならんでいること。
    いわば昇順ソートされた状態。

  2. 日付列(フィールド)には、空白データが存在しないこと。
    つまり空白データは最終行の次にあること。

この前提を満たしていると、match関数の第1引数(最初のパラメータ)に「""」を指定することで最終行を算定できる。また、途中の日付の始まる(または終わる)行も求められるから、あとはoffset関数を使えば前回のような「計算範囲指定」での計算式を記述できることになります。

そして、試した範囲では、OpenOffice.org Calc Portable は、csvファイルを外部参照しても統計を出すことができるみたいで、応用範囲は広そうです。



先日の表から、またはじめます。

c0062295_038318.gif
 ←こんな表でした

F列が撮影日。
昇順です。
上下の入れ替わりはありません。

このシートの下のほう、最終行はここになっています。

c0062295_2048813.gif

このシート($LogData.)の F列 に対し、次式で最終行がわかります。

 MATCH("";$LogData.$F$1:$F$65536)

この式を、先日の集計用シート(CalcBase)の G40 セルに書いてF9で再計算させました。
次の図のように、最終行の数をだすことができています。

c0062295_21112246.gif

この最終行の日付を求める式は、いまの行数をだす式を再度利用する形のINDEX関数で求まります。

 INDEX($LogData.$F$1:$F$65536;MATCH("";$LogData.$F$1:$F$65536))

この式を、計算シート(CalcBase)の F40 セルに書いたのが次の図。
さらに、F39 に一週間前の日付を。
その上、F38 にさらに一週間前。
さらに、・・・・と、ここはコピペ(セル右下のポインタつまんでドラッグ)で。

こうして、最終日〜一週間刻みで日付のリストにして、各日付の最終行を示す式を、G39から上に書いてゆきます。その式は、MATCH関数で次のように書きます。(G39 セルの例)

 MATCH($F39;$LogData.$F$1:$F$65536)

この式を上にドラッグでコピペした結果が次の図です。
(拡大ズーム率200% < 老眼向け)

c0062295_22354030.gif

この式を下に(つまり G40 に)コピペしてもそれまでと同じく最終行を表示します。

 G40に次のどちらを入れても同じ値ということです。
 ┏ =MATCH("";$LogData.$F$1:$F$65536)
 ┗ =MATCH($F40;$LogData.$F$1:$F$65536)

MATCH関数(3番目のパラメータないやつ)の定義からは当然なんですけど、最初は意外な気がしました。


この日付と行数の対応っぷりを検証して、スクリーンショットを並べると・・・

c0062295_22593818.gif
c0062295_2322582.gif
c0062295_23245539.gif
c0062295_23265331.gif
c0062295_23273144.gif
c0062295_2328435.gif
c0062295_23293599.gif


対応はとれているようですねぇ。


ここから指定行範囲で統計を取るために、OFFSET関数を利用します。
ヘルプによるOFFSET関数の説明を引用します。

OFFSET


基準セルから指定の行数と列数を移動した位置にあるセルの値を返します。

構文


OFFSET(参照; 行; 列; 高さ; 幅)

参照 は、関数が新しい参照を検索する参照元です。

行 は、参照を上 (負の値) または下に修正した行の番号です。

列 (オプション) は、参照を左 (負の値) または右に修正した列の数です。

高さ (オプション) は、新しい参照位置が開始される範囲の垂直方向の高さです。

幅 (オプション) は、新しい参照位置が開始される範囲の水平方向の幅です。

引数 行 と 列 は、ゼロまたは負の開始行または列にならないようにします。*1
引数 高さ と 幅 は、ゼロまたは負の行または列数にならないようにします。

OpenOffice.org Calc の関数では、「オプション」としてマークされているパラメータを省略できるのは、その後にパラメータが続かない場合のみです。たとえば、4 つのパラメータがあり、その最後 2 つのパラメータが「オプション」としてマークされている関数では、パラメータ 4 を省略したり、パラメータ 3 と 4 を省略することはできますが、パラメータ 3 のみを省略することはできません。


=OFFSET(A1;2;2) は、セル C3 の値を返します。これは、A1 から 2 行と 2 列下に移動したからです。C3 が値 100 を含む場合、この関数は値 100 を返します。

=OFFSET(B2:C3;1;1) は、B2:C3 から 1 行 1 列右下に移動した C3:D4 への参照を返します。

=OFFSET(B2:C3;-1;-1) は、B2:C3 から 1 行 1 列左に移動した A1:B2 への参照を返します。

=OFFSET(B2:C3;0;0;3;4) は、B2:C3 を3行4列にリサイズした B2:E4 への参照を返します。

=OFFSET(B2:C3;1;0;3;4) は、B2:C3 を 3 行 4 列 のサイズに変更し、1 行下に移動した B3:E5 への参照を返します。

=SUM(OFFSET(A1;2;2;5;6)) はセル C3 から始まる範囲の合計を決定し、高さ 5 行、幅 6 列です (範囲 = C3:H7)。
*1 負の数指定すると、行なら上方向・列なら左方向、という説明と矛盾しているかと重い違いそうですね。正確には、『行または列の修正移動(Shift)先が負とかゼロ──A1より上や左のセル指定──にはならないように』ということらしいです。
おそらく言外には、65536行と1024列を超えてはいけないという意味もありそうです。
(OpenOffice Calc は、AMJ65536 のセルが右下の端っこうで、その右もその下もシートからはみ出てしまう)


上のシート(CalcBase)例で計数(COUNT関数)をやってみると、次のようになります。

 COUNT(OFFSET($LogData.$F$1;$G39;0;$G40-$G39))

c0062295_2146085.gif
 得られる計数は、(7/2の翌日である)2010年7月3日から7月9日まで1週間の範囲で、撮影テイク数として155件がえられます。
(7月2日のラストが6488行
 7月9日のラストが6643行
 なので、7/3〜7/9の期間では6489〜6643行までとなり、
 155行分。
 ・・・これってカウントするまでもないか ^^)

続けて、条件付き計数(COUNTIF関数)をやってみます。
LogDataシートのF列は日付(G列は時刻)であり、H列はモダリテイ(静止画とか)でした。

7/3〜7/9の範囲のH列の要素が "静止画" となっているセル数をだすのは、次式。

 COUNTIF(OFFSET($LogData.$H$1;$G39;0;$G40-$G39);"静止画")

これをコピペ対応にちょこっと一般化すると次の式になります。

 COUNTIF(OFFSET($LogData.$H$1;$G39;0;$G40-$G39);AH$1)

c0062295_22121211.gif
c0062295_22115676.gif

コピペ対応用に修正したら、ドラッグでコピペします。

c0062295_221455100.gif

どうやら、全ての計数と、モダリティ別の集計との合計は一致しました。正しそう。

c0062295_2391139.gif

あとは、AG40〜AL40 のセルにある式を、上方向にずりずりっとコピペして、計数部分は完成。

c0062295_23182282.gif
c0062295_23183678.gif

正しいかどうかを検算するために、F34に2009/09/30、F35に2009/10/31 を仮に入れてみて、先日紹介した月別集計のほうと照らし合わせてみたら、OK。



次には、条件別の合計(SUMIF関数)も同様に作成します。

LogDataシートの I列に、シーン数があるのでそのモダリティ別の合計です。
式は、AH40セルの式を、Z40にコピペして修正しましょう。

COUNTIF(OFFSET($LogData.$H$1;$G39;0;$G40-$G39);Z$1)


コピー元(AH40)では AH$1 だったのが、Z$1になっています。
そして、COUNTIF(計数)の関数を SUMIF(合計)に変えて、3番目のパラメータ(実際に合計させるセル)で I列を指定しました。

SUMIF(OFFSET($LogData.$H$1;$G39;0;$G40-$G39);Z$1;OFFSET($LogData.$I$1;$G39;0;$G40-$G39))


c0062295_07125.gif

この式も、ドラッグ方式でコピペして、さきほどと同様に2009/10月のデータで検算してOK。


シーン数と同様に、画像数も動的集計表に。(式は、合計対象を I列から J列に変えただけ)

SUMIF(OFFSET($LogData.$H$1;$G39;0;$G40-$G39);R$1;OFFSET($LogData.$J$1;$G39;0;$G40-$G39))

c0062295_023266.gif



ディスク容量計算の表部分は、先日の文字列方式の式と、今回のOFFSET関数方式とのハイブリッドなものになりました。
R40セルを、H40セルにコピペ。(範囲内の枚数となる)
その式に、モダリティ種類別の画像容量表である先日のパートを組み合わせる。
c0062295_21333829.gif

そうして容量単位を [GB] にするするため /10^9 として、表示形式に単位を入れる。
できたのは、こんな式である。

c0062295_13136.gif


=SUMIF(OFFSET($LogData.$H$1;$G39;0;$G40-$G39);I$1;OFFSET($LogData.$J$1;$G39;0;$G40-$G39))*INDEX($C$5:$C$10;MATCH(I$1;$B$5:$B$10;0);0)/10^9


先日の文字式では、【*10^-9】 を掛ける景気にして、今回の式では【/10^9】と除算のかたちにした。こうしたのは、こんな小さな世界でも多様化を確保したかったからである。ひとつの形式だけというのは、一貫性があって規格化されていれ、効率的にも思えるのだが、実のところ今回のセルの三章先が化けるようなトラブルがおこったときに一気に連鎖的にあらゆる段階で式が壊れてゆくこともある。
このあたりは、MultiPlanいぢりで何となく体得してしまったことであります。

言ってみれば、一貫性の維持と多様性の確保は相反するものではなく。局所最適を残す(多様性を壊さない)ことと全体整合(いわゆる調和)を確保する(部分を類似な全体に波及させやすい形で記述しておく)こととは、相補的であるものだと思った次第。


(実の話、こちらのディスク容量計算式を先に仕上げてから、画像数・シーン数・テイク数・・・ってスケールダウンしたのです。説明上、簡単な式からスケールアップとしましたが・・・それでもうまく説明しきれていないのかも。)


とりあえず、語り残したこともあるが、将来内容も方法も忘れ去っていること必至であろう私自身の備忘録として、動的計算式の記述方法解説を以上として書き残す。
[PR]
by bucmacoto | 2010-07-30 01:27 | &Tips;&code;
<< 雨が上がって、霧が晴れて、日が射した 私にとっての昭和の歌姫 >>
<< 雨が上がって、霧が晴れて、日が射した 私にとっての昭和の歌姫 >>