-
hoehoe1234
- 1178
- 0
- 0
- 0
-
- いいね！0
ほえほえ＠スプシマン
@hoehoe1234
ワークシートでの動的範囲、スピル版。スピルは「スピル全体」をたとえば他の関数から使用する場合にA:1#と指定できるのでとても便利。ただし行または列のどちらか一方しか自動拡張できないみたいです。 pic.twitter.com/CdoxVG18IS
2021-06-14 16:10:58
拡大
ほえほえ＠スプシマン
@hoehoe1234
旧来の方法。参照用のシートを用意することによりスピルとして扱えるので便利。名前の定義は範囲拡張に対応していなかったみたいです。 pic.twitter.com/MoamLhEXoK
2021-06-14 16:30:52
拡大
ほえほえ＠スプシマン
@hoehoe1234
一方、フィルター関数による動的拡張は、式を名前定義してもうまく動くので、行、または列の一方しか拡張する必要がなければfilter関数＋名前定義による動的拡張であれば余計なシートを作成する必要がなくなります。 pic.twitter.com/3nr5ShpTuW
2021-06-14 16:40:13
拡大
ほえほえ＠スプシマン
@hoehoe1234
レンジはカレントリージョンがとれるし、スピルしてるのならちゅんさんが投稿したようにスピル範囲も取れます。VBAから範囲を確定する手法が増えたことはうれしですね。さて、エクセルのワークシート関数でこれらのどのようにすればよいでしょうか？
2021-06-14 16:46:25
ほえほえ＠スプシマン
@hoehoe1234
今の所 ①テーブル機能を使う ②offset+match ③filter+名前定義 の３つの手法がありそうです。ユーザ定義関数はどうしても引数と戻り値の自己参照が発生してしまいうまく使用することができませんでした。
2021-06-14 17:03:55
ほえほえ＠スプシマン
@hoehoe1234
元データはいじらない。という原則があるので、動的範囲がエクセルの数式で欲しい場合は、いきなりフィルタ＋名前定義よりもやはり作業シートを１つ導出するほうがわかりやすさから理にかなっているように思います。元シートはガツンとコピーされますしね。
2021-06-14 17:40:37
ほえほえ＠スプシマン
@hoehoe1234
検証はしていませんが、フィルターを使う場合、他の関数と組み合わせればフィルター関数でも行・列指定で動的範囲が取得できると思います。
2021-06-14 17:42:10
ほえほえ＠スプシマン
@hoehoe1234
フィルター関数を使った動的縦横参照でけた。とはいえ、indirectをつかっているし、countaも使っているので実質、offset+counta方式のほうが優れている・・・・。しかし、できるには、できた！ pic.twitter.com/nodOsa6ByZ
2021-06-16 04:35:37
拡大
ほえほえ＠スプシマン
@hoehoe1234
動的拡張される元データシートは隣に参照用のシートつくったほうがいいのかな？そうすればそのシートのA1を起点に「参照用!A1#」で参照できるし、名前つけてもちゃんとうごきそうだね。絞り込み条件とか考慮すると「導出表」って感じなんだろうね。VBAで導出表の自動さくせもできそうだしね。
2021-06-17 16:19:03
ほえほえ＠スプシマン
@hoehoe1234
これで動的範囲がスピルで取れるので、他の数式は「動的範囲４#」という指定で動的範囲が対象にできますね。 pic.twitter.com/iccudd23gf
2021-06-17 16:24:42
拡大
ほえほえ＠スプシマン
@hoehoe1234
いまさらだけど@とか#がテーブル、新関数でつながってきてうれしいね。新関数のなにが楽しいって、index+matchに類するイデオム、組み合わせをどんどん自分で作っていけること。かつ、他の機能との組み合わせ性。新機能は「セル転記」のために生まれたんや！
2021-06-21 02:02:03
ほえほえ＠スプシマン
@hoehoe1234
何らかの事情で元シートデータがテーブル化できないばあいの、動的参照範囲。どうして一旦このようにするかといえば、シート名!A1#で全動的範囲が取れるから。 しかし、なにか間違っている感が？？？。。。 pic.twitter.com/nvQvYx7RZg
2021-06-21 02:58:03
拡大
ほえほえ＠スプシマン
@hoehoe1234
これを更に、直接名前として登録したものがこれ。 名前で動的範囲が取れています。う～～～ん。ちょっとなんか違うような？ pic.twitter.com/FFxo3MpGq3
2021-06-21 03:00:31
拡大
ほえほえ＠スプシマン
@hoehoe1234
スピル範囲の１行目（行ヘッダ）だけを取得するのはこんな感じで簡単。 pic.twitter.com/hIpxUZazdg
2021-06-21 03:02:29
拡大
ほえほえ＠スプシマン
@hoehoe1234
テーブルのDataBodyRange相当の、行ヘッダを除いたデータ部だけをスピル#から取得。「空白」はインターセター演算子なのでデータ部分のみが取得できています。しかし、なんか違う気がｗｗｗ。 pic.twitter.com/NHg9Vf20H9
2021-06-21 03:04:06
拡大
ほえほえ＠スプシマン
@hoehoe1234
今日のワクVでも生徒さんとはなしたんだけど、生データがシートにあって、必要な勘定科目シートがあれば、VBAで勘定科目シートを自動作成して、そのシートのA1にスピル式を設定することにより、勘定科目に合致するデータだけをfilter等で自動的に引っ張ってこれそうだね。自動振り分け機能みたいな？
2021-06-21 03:06:22
ほえほえ＠スプシマン
@hoehoe1234
もちろん、VBAで全部やればいいんだけど、現場ではVBAが分かる人ばかりではないので、シートのA1にスピル式をいれておけば、やってることと自分でも手作業で追加したり、直したりできる。からね。
2021-06-21 03:07:17
ほえほえ＠スプシマン
@hoehoe1234
これからはますます、 ①テーブル機能 ②スピル式 ③パワークエリ ④VBA の組み合わせ方がいろいろ出てきそうですね。VBAはファイル周りとかどうしても機能ではできな部分をカバーしそうですね。
2021-06-21 03:08:51
ほえほえ＠スプシマン
@hoehoe1234
何らかの事情でテーブルを使わないで動的範囲を利用する場合はこれでいいかな。名前定義に直接indirectの式を書ける。とはいえ、スピルしないと意味がないのでスピル前提になりますが。 pic.twitter.com/4H5l0IUEBE
2021-07-02 05:37:13
拡大