エクセル雑感
スピらない スピル数式 スピらせる

ExcelマクロVBAとエクセル関数についての私的雑感
公開日:2023-12-06 最終更新日:2023-12-06

スピらない スピル数式 スピらせる


タイトルというのはなかなか難しい。
当初思ったお題は、
・スピらぬなら 壊してしまえ スピル数式
・スピらぬなら スピらせてみよう スピル数式
・スピらぬなら スピルまで待とう スピル数式
もちろん、壊してしまう訳には行かないし、待っていたってスピルする訳ないし・・・
当然「スピらぬなら スピらせてみよう スピル数式」と言う事になる。


それじゃあ内容はというと、結局いかにスピらせるかって話になってしまう。
だったら、それをそのままタイトルにしたほうが良いかなと思って、掲題になりました。
前置きはこのくらいにして、本題はいかにスピルする数式を作成するかについて。


スピルとラムダの前提知識

スピルについての基本は以下を参照してください。
スピルについて|エクセル入門
2019年にOffice365のExcelに実装された革新的な機能としてスピルがあります。数式を入力したセルから結果があふれて隣接したセルにも出力されるのがスピルです。今までは数式を入れたセルにしか結果を出せませんでしたが、スピルでは隣接するセルにまで結果が表示されます。

また、以下ではLAMBDAとヘルパー関数も使いますので、こちらも合わせてご参照ください。
LAMBDA以降の新関数について|エクセル入門
2022年2月頃にLAMBDA関数と関係するいくつかの新関数がMicrosoft365(Office365)で提供されました。これらはLAMBDA関数とLAMBDA関数を引数に指定できるヘルパー関数群になります。2022年3月頃には「OfficeInsiderProgram」でTEXTSPLITやVSTACKをはじ…


今回のお題

以下の表を使います。

エクセル Excel スピル数式

A1:A11は「テーブル1」
G2にスピル数式を入れて、G2:I11を完成させます。
「合計」「最大」「最小」
もちろん各行ごとの「合計」「最大」「最小」です。


スピル数式の作成

まずは「合計」から作成してみましょう。
1行だけなら、

エクセル Excel スピル数式

=SUM(テーブル1[@])
テーブルじゃなければ、
=SUM(A2:E2)

この数式を複数行でスピルさせようと入力範囲を複数行にすると、

エクセル Excel スピル数式

=SUM(テーブル1)
テーブルじゃなければ、
=SUM(A2:E11)

結果を見れば当たり前なのですが、、、
とはいえ、一度はやってしまう、一度は通り過ぎる間違いでもあります。
SUM関数は引数を全て合計しますので当然こうなります。

では、どうやったら1数式で全行のスピルする数式にできるか・・・
これにはLAMBDAを使います。
いきなり数式です。

エクセル Excel スピル数式

=BYROW(テーブル1,LAMBDA(x,SUM(x)))

「スピルとラムダの前提知識」のところでLAMBDAヘルパー関数を紹介しましたが、その中で今回使用したのはBYROW関数です。
BYROW関数(配列の行単位にLAMBDAを適用し列を集約)|エクセル入門
BYROW関数はLAMBDAヘルパー関数(LAMBDAと一緒に使う)の一つです。配列の各行ごとにLAMBDAを適用して、行単位での計算結果を元配列と同じ行数(列数は1)の配列で返します。行単位ではなく列単位で処理して行を集約する場合はBYCOL関数を使います。
BYROW関数は配列の各行ごとにLAMBDAを適用して行単位で計算します。

エクセル Excel スピル数式

これで「合計」は出来ました。
同様に「最大」「最小」もそれぞれ入れれば一応は完成です。

エクセル Excel スピル数式

=BYROW(テーブル1,LAMBDA(x,SUM(x)))
=BYROW(テーブル1,LAMBDA(x,MAX(x)))
=BYROW(テーブル1,LAMBDA(x,MIN(x)))

これで3列別々ではありますがスピルしています。
ですが、一番最初に提示したお題としては、1つの式で全部スピルしていました。
それにはHSTACK関数を使って横にくっ付ければ良いですね。
HSTACK関数(配列を横方向に順に追加・結合)|エクセル入門
配列を横方向(水平方向)に順番に追加し、1つの大きな配列を作成して返します。HSTACK関数は各配列引数を列単位で左から順に(つまり右へ右へ)追加して新しい配列を作成します。縦方向(垂直方向)に結合する場合はVSTACK関数を使用します。
エクセル Excel スピル数式

=HSTACK(
BYROW(テーブル1,LAMBDA(x,SUM(x))),
BYROW(テーブル1,LAMBDA(x,MAX(x))),
BYROW(テーブル1,LAMBDA(x,MIN(x))))


これでひとまずは完成です。


数式を、より汎用的に…より抽象的に…

数式を、より汎用的に、より抽象的に、
それはつまり、より難しく…そんなことも無いのですが(笑)
同じセル範囲が何回も出てきたり、同じ数式が何回も出てきたり・・・
後々の保守を考えれば、出来ればこういうのは1回で済ませたいところです。

入力範囲の「テーブル1」が3回出てきています。
まずはこれを1回にまとめます。
それにはLET関数を使います。
LET関数(数式で変数を使う)|エクセル入門
LET関数は、関数内で計算結果やセル範囲に名前を定義できます。これにより、数式の中間計算に名前を定義したり、後ろの引数で定義した名前を式に使う事が出来ます。これはプログラミングにおける変数と同じ機能になります。
LET関数で入力範囲(ここでは「テーブル1」)を変数に割り当てて、その変数を使って数式を書きます。

エクセル Excel スピル数式

=LET(in,テーブル1,
HSTACK(BYROW(in,SUM),BYROW(in,MAX),BYROW(in,MIN)))


大分すっきりはしました。
もうこれで良さそうですが、、、

BYROW(...)
これが3回もでてきています。
この記述も1回にしたいですよね。
こうなったら、最新の365Insiderの「イータ縮小ラムダ」も使っちゃいましょう。
イータ縮小ラムダ(eta reduced lambda)|エクセル入門
LAMBDAヘルパー関数のLAMBDA関数の記述部分を縮小記述するものです。明示的にLAMBDA関数を記述する場合に比べてイータ縮小ラムダは記述が短く扱いやすくなっています。もちろん明示的にLAMBDA関数を記述しても構いませんが、イータ縮小ラムダの記述が可能な場合は極力使うようにすることで数式も短く見やすくなりま…
合計・最大・最小の関数は「イータ縮小ラムダ」で切り替えるとして、
その元となる自作関数でBYROWを定義しておきます。

エクセル Excel スピル数式

=LET(in,テーブル1,
fn,LAMBDA(x,BYROW(in,x)),
HSTACK(fn(SUM),fn(MAX),fn(MIN)))


最後のこの数式はInsiderでしか使えませんが・・・
どうですかね、どのあたりの数式が見やすいでしょうか。
ここまでの数式を見比べやすいように、改行を同じように合わせて掲載しておきます。

=BYROW(テーブル1,LAMBDA(x,SUM(x)))
=BYROW(テーブル1,LAMBDA(x,MAX(x)))
=BYROW(テーブル1,LAMBDA(x,MIN(x)))


=HSTACK(
BYROW(テーブル1,LAMBDA(x,SUM(x))),
BYROW(テーブル1,LAMBDA(x,MAX(x))),
BYROW(テーブル1,LAMBDA(x,MIN(x)))
)


=LET(
in,テーブル1,
HSTACK(
BYROW(in,SUM),
BYROW(in,MAX),
BYROW(in,MIN)
))


=LET(
in,テーブル1,
fn,LAMBDA(x,BYROW(in,x)),
HSTACK(
fn(SUM),
fn(MAX),
fn(MIN)
))





同じテーマ「エクセル雑感」の記事

プログラミングとは
「ネ申Excel」問題 への同意と反論
「Excel3ステップ理論」3階層システムの応用
「ポケモンを確実に見つける方法」をExcelで数学してみた
エクセルで「もういくつ寝るとお正月」
エクセルで連立方程式を解く(MINVERSE,MMULT)
VBAが消えてしまった!マクロが壊れて動かない!
スピらない スピル数式 スピらせる
難しい数式とは何か?
いくつかの数式の計算中にリソース不足になりました。
無効な前方参照か、コンパイルされていない種類への参照です。


新着記事NEW ・・・新着記事一覧を見る

テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。|エクセル雑感(2023-12-28)
VBAでクリップボードへ文字列を送信・取得する3つの方法|VBA技術解説(2023-12-07)
難しい数式とは何か?|エクセル雑感(2023-12-07)
スピらない スピル数式 スピらせる|エクセル雑感(2023-12-06)
イータ縮小ラムダ(eta reduced lambda)|エクセル入門(2023-11-20)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.条件分岐(IF)|VBA入門
9.セルのクリア(Clear,ClearContents)|VBA入門
10.マクロとは?VBAとは?VBAでできること|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ