新規に質問を投稿する
SUBTOTALをアクションに持たせ複数列で使いたいのですが…
2020-02-13 12:06
お世話になっております。
先日ここでフィルターをかけた後の合計値についての質問を投げた際にsubtotal関数のことを教えていただいたのですが、いざ使おうとしたときにまた壁に当たったので質問します。
[具体的にやりたいこと]
取得した行範囲の値(列は同じ)を集計する関数を作成したい。
その際関数はアクション上に持たせ、複数列に使用するため汎用的な関数が望ましいです。
[やってみてダメだったこと]
複数列に設定するので、列はCOLUMN()にしました。
その際、作成したのが以下のような関数です。
="=SUBTOTAL(9,SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&2:SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&300)"
２行目から300行目までの値を集計する関数にしたかったのですがうまくいきませんでした。#VALUEエラーが出ていたのでもしかしてADDRESS関数で取得した値が文字列となってしまうため、SUBTOTAL関数がうまく動いていないのではと思い、VALUE関数で囲ってみようと思いましたが、CELFには存在せず困っています。
どうするのが良いでしょうか。
ご助力のほどよろしく願いたします。
回答を投稿するにはログインが必要です。
Yexiongmao様。
丁寧なご回答ありがとうございます。
追記の通りに実装したところうまく動いてくれました。
INDIRECTが文字列を参照に変えるのですか。ふんわりと間接的に読みに行くという認識だったので勉強になりました。また、アクションに式を埋め込む際、=""で囲むということは覚えていたのですが、すでにある""をさらに囲むということは失念してました。
いろいろと世界が広がりそうです。ありがとうございました。
丁寧なご回答ありがとうございます。
追記の通りに実装したところうまく動いてくれました。
INDIRECTが文字列を参照に変えるのですか。ふんわりと間接的に読みに行くという認識だったので勉強になりました。また、アクションに式を埋め込む際、=""で囲むということは覚えていたのですが、すでにある""をさらに囲むということは失念してました。
いろいろと世界が広がりそうです。ありがとうございました。
ベストアンサー
こんばんは。
アクション内で計算した値をセルに書き込むには、式を「="～"」で囲んではいけません。
「="～"」形式はセルに式そのものを書き込みたい場合です。
=SUBTOTAL(9,～)の～の部分にはセルの参照が入りますが、
SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"2:"&SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&300)はセル範囲の文字列が返ります。（「&2:」は「&"2:"&」とする必要があります。）
セル範囲の文字列をセル参照に変えるには「INDIRECT」関数を使います。
すなわち
=SUBTOTAL(9,INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"2:"&SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&300)))となります。
この式を計算するには、現在のセル位置の行が1または301以上で無いと循環参照してしまいます。
また因みに、式そのものを書き込む場合は「”」で囲まれた部分は「"」を2つづつつける必要があります。
2/14追記
アクションで値をセルに書き込むとしましたが、=COLUMNはアクションが発生したときのセルのカラム位置を返します。
なので、ボタンが押されたときのアクションならボタン位置のカラムが返りますし、別のアクションから呼ばれた場合なら、その時点のセル位置のカラムが返ります。
ならばご質問のように式を書き込むことになるかもしれません。
="=SUBTOTAL(9,INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),""1"","""")&""2:""&SUBSTITUTE(ADDRESS(1,COLUMN(),4),""1"","""")&300)))"
汎用性も大切ですが、式が長くメンテナンスしにくいというデメリットもあります。
いずれにしてもアクションからということになると、書き込むセルを決める手順が必要なので、それを利用すればCOLUMN関数を使用するよりシンプルな式にすることができると思います。
アクション内で計算した値をセルに書き込むには、式を「="～"」で囲んではいけません。
「="～"」形式はセルに式そのものを書き込みたい場合です。
=SUBTOTAL(9,～)の～の部分にはセルの参照が入りますが、
SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"2:"&SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&300)はセル範囲の文字列が返ります。（「&2:」は「&"2:"&」とする必要があります。）
セル範囲の文字列をセル参照に変えるには「INDIRECT」関数を使います。
すなわち
=SUBTOTAL(9,INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&"2:"&SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&300)))となります。
この式を計算するには、現在のセル位置の行が1または301以上で無いと循環参照してしまいます。
また因みに、式そのものを書き込む場合は「”」で囲まれた部分は「"」を2つづつつける必要があります。
2/14追記
アクションで値をセルに書き込むとしましたが、=COLUMNはアクションが発生したときのセルのカラム位置を返します。
なので、ボタンが押されたときのアクションならボタン位置のカラムが返りますし、別のアクションから呼ばれた場合なら、その時点のセル位置のカラムが返ります。
ならばご質問のように式を書き込むことになるかもしれません。
="=SUBTOTAL(9,INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),""1"","""")&""2:""&SUBSTITUTE(ADDRESS(1,COLUMN(),4),""1"","""")&300)))"
汎用性も大切ですが、式が長くメンテナンスしにくいというデメリットもあります。
いずれにしてもアクションからということになると、書き込むセルを決める手順が必要なので、それを利用すればCOLUMN関数を使用するよりシンプルな式にすることができると思います。
Q&A一覧へ