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

公開日：2023-12-06　最終更新日：2023-12-06

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

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

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

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

スピルについての基本は以下を参照してください。

スピルについて｜エクセル入門

2019年にOffice365のExcelに実装された革新的な機能としてスピルがあります。数式を入力したセルから結果があふれて隣接したセルにも出力されるのがスピルです。今までは数式を入れたセルにしか結果を出せませんでしたが、スピルでは隣接するセルにまで結果が表示されます。

また、以下ではLAMBDAとヘルパー関数も使いますので、こちらも合わせてご参照ください。

LAMBDA以降の新関数について｜エクセル入門

2022年2月頃にLAMBDA関数と関係するいくつかの新関数がMicrosoft365（Office365）で提供されました。これらはLAMBDA関数とLAMBDA関数を引数に指定できるヘルパー関数群になります。2022年3月頃には「OfficeInsiderProgram」でTEXTSPLITやVSTACKをはじ…

今回のお題

以下の表を使います。

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

スピル数式の作成

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

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

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

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

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

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

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

「スピルとラムダの前提知識」のところでLAMBDAヘルパー関数を紹介しましたが、その中で今回使用したのはBYROW関数です。

BYROW関数（配列の行単位にLAMBDAを適用し列を集約）｜エクセル入門

BYROW関数はLAMBDAヘルパー関数（LAMBDAと一緒に使う）の一つです。配列の各行ごとにLAMBDAを適用して、行単位での計算結果を元配列と同じ行数（列数は1）の配列で返します。行単位ではなく列単位で処理して行を集約する場合はBYCOL関数を使います。

BYROW関数は配列の各行ごとにLAMBDAを適用して行単位で計算します。

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

=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関数を使用します。

=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」）を変数に割り当てて、その変数を使って数式を書きます。

=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を定義しておきます。

=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」問題への同意と反論

「ネ申Excel」、私は流行に疎いので、ネ申の意味が最初分からなかった、どうやら、「神Excel」のことのようです、大学教授が書いた、Excelの問題点と解決策の提案がかかれた論文です、この論文に対する、私なりの同意と反論です。Excelを論ずるのに、なぜネ申などという言葉を使ったのか、私には少々理解しがたい部分も…

「Excel3ステップ理論」3階層システムの応用

エクセルでシステムを作成する時に、念頭に置くべき3階層の考え方になります、本来はExcelに限った事ではなく、システム作成では常に3階層を考えて設計しますが、さらに考えを推し進めて3ステップとして考えます、つまり、「Excel3ステップ理論」です。データを入力するシートで、さらに集計し、さらに印刷までやろうとしたら、

「ポケモンを確実に見つける方法」をExcelで数学してみた

ポケモンGO関連の記事をみていたら、【ポケモンGO】かくれているポケモンを確実に見つける方法、という記事を読んだ。普通にそうだなという感想なのですが、もっとまじめに数学したら、もっと良い方法があるのではないかと。元記事はあちこちで見た気がするが、多分これでしょ「【ポケモンGO】かくれているポケモンを確実に見つける…

エクセルで「もういくつ寝るとお正月」

今日は2020年1月31日です。早いもので1月が終わろうとしています。つまり、2020年も12分の1がすでに終わろうとしています。この調子では、あっという間に今年（2020年）も終わってしまいそうです。

エクセルで連立方程式を解く（MINVERSE,MMULT）

とつぜん連立方程式を解くことになりました。なぜ連立方程式を解くことになったのか、そして、どうやって答えを導き出したのか… これらを自身の覚え書きの意味も含めて記事にしておきます。ただし、行列の難しい説明は抜かして、どうやって解決したかの経緯の説明が中心になります。

VBAが消えてしまった！マクロが壊れて動かない！

2020年4月15日に配信されたWindows10用セキュリティ更新プログラムの不具合で、なんと、VBAが全て消えてしまうという事が発生しています。今後もあり得る事なので、このような場合の対策について記しておきます。VBAが消えてしまうとはどんな状態なのかエクセルファイルを開くと、以下のようなメッセージが出力され…

スピらないスピル数式スピらせる
難しい数式とは何か?

エクセルでは難しい数式は要らない、𝕏ではそんな話を良く目にします。同じことをするのなら、難しいより簡単な方が良い、それは当たり前ですね。では「難しい」とは何を指しているのでしょうか? 普段良く「数字で語る」みたいなことを言いますが、「難しい」の基準もなくそれを計る尺度も存在しない極めて曖昧な話…

いくつかの数式の計算中にリソース不足になりました。

365のエクセルで最近になって急に出始めたエラーメッセージです。「いくつかの数式の計算中にリソース不足になりました。そのため、これらの数式の値を求められません。」何が原因で出力されるメッセージなのでしょうか… どのような対処をしたら良いのでしょうか… エラーメッセージの詳細かつて見た覚えのないメッセージです。

無効な前方参照か、コンパイルされていない種類への参照です。

365のエクセルで最近（2023年の後半くらい）になって急に出始めたエラーメッセージです。いくつかのプログ等で対策が書かれているのを見かけましたが、これと言った決め手も無さそうに見受けられました。つまり、書かれている方法で解決した人もいれば解決しない人もいるといった状況に見受けられます。

エクセルが起動しない、Excelが立ち上がらない

365のエクセルで最近（昨年くらい）から急に出始めた不可解な謎現象です。一時的な現象もしくは特定（私）のPCだけの現象だと思っていたのですが、時がたつにつれて一般的に発生している現象ということがわかってきました。なにより、最近新しいWindows11のPCを購入しましたが、そのPCでも発生するようになったからです。

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

1.最終行の取得（End,Rows.Count）｜VBA入門
2.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
3.変数宣言のDimとデータ型｜VBA入門
4.繰り返し処理（For Next)｜VBA入門
5.RangeとCellsの使い方｜VBA入門
6.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
7.セルのクリア（Clear,ClearContents）｜VBA入門
8.メッセージボックス(MsgBox関数)｜VBA入門
9.条件分岐（Select Case）｜VBA入門
10.ブック・シートの選択（Select,Activate）｜VBA入門

ホーム
エクセル全般
エクセル雑感
スピらないスピル数式スピらせる

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

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

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