読者です 読者をやめる 読者になる 読者になる

溢れ出る加齢臭

カレー臭に加え加齢臭が溢れ出る年齢になりました。

Excelのセル参照はメンテナンス性を重視して作るべきではないか

部下にアンケートの集計をさせたところ、
ある設問Xの回答者の合計があっていないことに気づいた。
設問Xの回答の対象は、別の設問Mで(2)と回答した人が対象となるので、
クロス集計をする必要がある。


Excelでクロス集計をするときには、計算過程の確認ができるように、
ピボットテーブルは使わせず、SUMPRODUCT関数を使わせている。
そのセル参照をR1C1形式にして、かつ列要素の参照を数値で指定するのではなく、
別のセルに列番号を書いておいて、INDIRECT関数で参照させている。

=SUMPRODUCT((INDIRECT("Source!R"&1&"C"&2&":R"&$F$1&"C"&2,FALSE)=J$2)
*INDIRECT("Source!R1C90:R"&$F$1&"C90",FALSE)=0)
*INDIRECT("Source!R"&1&"C"&MAX($C$1:$C860)&":R"&$F$1&"C"&MAX($C$1:$C860),FALSE)=$E860))

これだけ見てもさっぱりわからない。ムダも多い。(実際は当然のことながら改行はない)


これは集計結果のシートと元データのシート(Sourceという名前にした)を
同じファイルにおいたので、ちょっとだけスッキリしている。
元々は集計結果のファイルと、元データのファイルを分けているので、
参照先はセルではなくファイル名まで書いている。
したがって、数式だけ見ても、何をしているのかさっぱりわからない。


この形式は、一度作れば別の集計もほぼコピペでできるので、作業の省力化には役立つ。
しかし、検証しようとするとかなり時間がかかる。
昨日は検証に4時間費やした。
検証用に元データに判定列を挿入すると、元の集計結果がずれてしまう。


セル参照を数式内で明示していれば、列挿入すると参照先のセルもずれるのだが、INDIRECT関数を使って拾った数値で参照させると、参照列がずれてしまう(何言ってるかわかりにくいな)


こういうのは、後からメンテナンスしやすいように、大変だろうけれどINDIRECTを使わず、セル範囲をちゃんと参照させる書くか(できればA1方式でセル参照させた方が見やすい。R1C1方式は見づらい。)、データの各列に名前定義をしておくようにしてもらいたいものである。