Excel 配列数式が一番役に立つ例と欠点

配列数式の活用場面は積算

Sub Macro1()
' ActiveSheet.UsedRange.Clear ’配列数式の場合、これを入れないと作り直せない
    Range("A1") = "A1"
    Range("A2") = "A2"
    Range("A3") = "A3"
    Range("B1") = "1"
    Range("B2") = "2"
    Range("B3") = "4"
    Range("B3") = "3"
    Range("C1") = "10"
    Range("C2") = "100"
    Range("C3") = "1000"
    Range("E1") = "B2*C2"
    Range("E1") = "B1*C1"
    Range("D1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"
    Range("D1").Select
    Selection.Copy
    Range("D2").Select
    ActiveSheet.Paste
    Range("D3").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "B2*C2"
    Range("E3").Select
    ActiveCell.FormulaR1C1 = "B3*C3"
    Range("D4").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
    Range("C4").Select
    Selection.FormulaArray = "=SUM(R[-3]C[-1]:R[-1]C[-1]*R[-3]C:R[-1]C)"
    Range("B4") = "配列数式"
    Range("B5") = "'=SUM(B1:B3*C1:C3) CSE {=SUM(B1:B3*C1:C3)}"
    Range("E4") = "'=SUM(D1:D3)"

End Sub

配列数式とは 教えて！HELPDESK
通常、単価×数量をDの列にいれて、合計を出す。
配列数式はD列が不要で、C列の下に一度に結果を出せる。

マイクロソフトの熱心な解説

配列数式を作成する
Create an array formula

Range.FormulaArray プロパティ (Excel)
2021/06/08

Rangeの配列式を設定または返します。 1 つの数式または 1 つの配列を返します (または、Visual Basicできます。 指定した範囲に配列数式が含まれている場合、このプロパティは null を返 します。 値の取得と設定が可能なバリアント型 (Variant) の値です。
注釈
FormulaArray プロパティの文字数は 255 に制限されています。
例
次の例では、シート 1 のセル範囲 A1:C5 に、配列定数として数値 3 を入力します。

Worksheets("Sheet1").Range("A1:C5").FormulaArray = "=3"

意味不明なので、上記の表に合わせてみた

Sub Macro2()
ActiveSheet.Range("B1:C3").FormulaArray = "=3"
End Sub

この配列定数は、主としてVlookUPで表を作らずに表引きをする場合に使う。
配列定数を配列数式の中で使う

配列数式の長所

エラーをスキップして集計

エラーを無視して合計する－配列数式
たとえば上記でいえばD列を合計するときエラーがあった場合それを回避できる。

Sub Macro3()
Range("D4").FormulaArray = "=SUM(IF(ISERROR(R[-3]C:R[-1]C),0,R[-3]C:R[-1]C))"
' または
Range("D4").FormulaArray = "=SUM(IF(ISERROR(D1:D3),0,D1:D3))"
End Sub

税込価格

切り捨てとすると、各列の場合は
=ROUNDDOWN(1.1*(B1:B3*C1:C3),0)
合計欄のときは、
=SUM(ROUNDDOWN(1.1*(B1:B3*C1:C3),0))
いちいち税込価格の行を設けるとかは必要がない。概算するときには重宝するだろう。

定数として数字を一気に入力

Sub Macro5()
'行を一列選択
    Range("A8:I8").Select
    Selection.FormulaArray = "={1,2,3,4,5,6,7,8,9}"
End Sub

Sub Macro6()
' 4列×3行を選択
  Range("A9:D11").Select
    Selection.FormulaArray = "={1,2,3,4;5,6,7,8;9,10,11,12}"
End Sub

正直、予めマクロで組んでいないと、入力で失敗しやすい。
ポイントはコンマ区切りで、セミコロンが改行という扱いになっていること。

VLOOKUPで表がいらない

Sub Macro7()
    Range("A7") = "みかん"
    Range("B7").FormulaArray = "=VLOOKUP(B7,{""みかん"",150;""りんご"",120},2,TRUE)"
End Sub

文字は他の例と同じく、ダブルクォートが2つ必要となる。
ただ、これは複雑になるとすぐ255文字の限界が来てしまうと思われる。

VLOOKUPを他の関数でやる方法 - Excelの真髄
MODE.MULT関数で複数の最頻値を求める

返り値が配列になるものは配列数式を使う。このため統計に関する関数で、配列数式でなければいけないものが若干存在する。

つまりVBAのArrayをワークシートで行うものが配列数式だと言える。

配列定数の定義だけはCSEがいらない時がある

VlookUP中の配列定数は{}自分で中かっこを入力する。

その他

配列研究室
配列はデータセットである。

配列数式の弱点

入力が必ずCSE

F2を押して数式を見たあとでもCSEである。
つまり、すぐ壊れてしまう。

結合したセルに配列数式を入力することはできない

上記ではB列、C列の配列数式をD列に入れている。
しかし、D列とG列が結合されている場合には配列数式を入力することができず、エラーになる。

この2つが多分最大の弱点だと思われる。

いろいろなルールや制約がある

配列数式を変更するためのルール

関数内の配列を操作する場合の制限事項Excel

配列数式の限界

Excelの仕様と制限

ワークシート間の配列数式の依存 使用可能メモリに依存

ということはワークシートを超えた配列数式は基本的に使わないほうが良い。

定数として一気に入力すると変更ができない

エラーが発生する。
入力だけなら、値の複写をおこない、解除しておいたほうがよい。

VBAでは255文字以上入力できない

Excel VBAで 255文字超の 配列数式 FormulaArrayを設定する

困った…
VBAで256文字以上の配列数式を
入力したいのだが、配列数式を設定する
FormulaArrayは255文字までしか
入力できないらしい( ´・ω・` )

数式の一部を文字列として
変数に入れてもダメだったし、
Sendkeysで擬似的に手入力する方法も
ループさせるとおかしくなる…
(´；ω；｀)#VBA #Excel

— 蒼き双魚 (@PusyaPisces) April 16, 2021

演算誤差の処理方法が不明

基本的に整数で扱うこと。
どこにも記載はないが、展開式から考えると、
個々に人日をかけた結果を四捨五入して合計を出すのはこれ。
=ROUND(SUM(IF(ISERROR(D1:D3),0,D1:D3)),0)

F2を押したとき、必ずCTRL+SHIFT+Enterを押す。

そうしないとただの数式に戻る。

まとめ

• 配列数式は数量×単価、や人日計算には強い。
• 各合計に消費税を乗じるのも強い。
• 概算するときに使えたほうが便利。
• 入力に癖があるので、VBAで定義すると良い。
• 入力がCSE、結合セルには使えないという点で、一般的な事務でExcelを使用する場合は使わないほうが良い。
• VBAで入力する方法が正確だが、この場合255文字という限界がある。他にもさり気なく限界がある。
• 可読性に欠ける。特に、検証方法に欠ける。
• 例えばVBAの上記のMACRO1でシートを作り直すときは必ずオールクリアにしないと上書きできない
• 目的を絞って限定的（他の方法により計算結果が確認できる場合）に使う。
• MOSのエキスパートで出てくる特に、統計に関する関数で必要となる

掛け算の九九、上記のリストを使わないVlookUp、人日、数量×単価のような場合を覚えておくと良いと思う。

追記

結合セルに配列数式が入力できないことについて

公式はこの欠点についての解説があまりない

この点については公式は解説が非常に薄くなぜかSpill(こぼし配列、流出配列、拡張展開配列数式）と一緒に書いてある。
Excel での #SPILL! error - 結合セルにスピルする
日本語では全く意味不明だが、英語でも分かりづらい。
#SPILL! error - Spill into merged cell

Spilled array formulas cannot spill into merged cells. Please un-merge the cells in question, or move the formula to another range that doesn't intersect with merged cells.

　タイトルを意訳すると「配列数式及びスピル配列数式（拡張展開配列数式）は結合したセルに入力できず、入力しようとするとSpirll Errorなどのメッセージが出てエラーになる」ということになるだろう。機械翻訳では無理。そもそも原文のタイトルが分かりづらい。
　また、原文はあたかもSpilled array formulas、つまり拡張展開配列数式が結合セルに入力できないように読めるが、実際はSpilled（拡張展開）ではないArray Formula (配列数式）も結合セルに入力できない。

　なお、スピル配列数式は2019年に追加された機能である。未だに訳がピンとこないが、原文からするとスピル配列数式とすべきだろう。公式では機械翻訳で動的配列とされている場合があるが、意味を考えると、動的配列数式ではなく、拡張展開配列数式とすべきだと考えている。一般的にITの翻訳では、動的はDynamicが当てられているので、ここでも動的を使うのは使いすぎで混乱を招くと思われる。
Excel での #SPILL! エラー-ワークシートの端を超えて拡張する