目次
あなたのような読者は、MUOをサポートするために役立ちます。当サイトのリンクを使って購入された場合、アフィリエイト報酬が発生する場合があります。もっと読む
配列式は、Excelを多機能にするツールキットの重要な部分です。しかし、これらの式は、初心者にはとっつきにくいものです。
一見、複雑そうに見えますが、その基本は簡単です。配列は、複雑なExcelのデータ管理を簡素化する数式なので、覚えておくと便利です。
Excelの行列式とは？
配列とは、列、行、または組み合わせのセルをまとめて1つのグループにしたものです。
Microsoft Excel では、配列式という用語は、このようなセル範囲に対して 1 つまたは複数の操作を、1 セルずつではなく、一度に実行する数式のファミリーを指します。
以前のバージョンのExcelでは、配列関数を作成する際にCtrl Shift Enterキーを押す必要があったため、CSE（Ctrl、Shift、Escape）関数と呼ばれていましたが、Excel 365ではこのようなことはなくなりました。
例えば、ある日の100ドル以上の売上高の合計を求める関数が考えられます。あるいは、5つのセルに格納されている5種類の数値の長さ（桁数）を求める関数。
配列式は、大規模なデータセットからサンプルデータを抽出するのにも最適な方法です。引数には、1つの列のセル範囲、1つの行のセル範囲、複数の行と列にまたがるセルを使用できます。
また、配列式にデータを取り込む際に、特定の値以上または以下の数値のみを取り込む、あるいはn番目の値までを取り込むといった条件文の適用が可能です。
このきめ細かなコントロールにより、データの正確なサブセットを抽出したり、不要なセル値をフィルタリングしたり、テスト用に項目のランダムサンプルを取得したりすることができます。
Excelでの配列の定義と作成方法
配列の数式は、最も基本的なレベルでは、簡単に作成することができます。簡単な例として、顧客の部品注文の請求書に、商品のSKU（Stock Keeping Unit）、購入数量、購入商品の累積重量、購入単位当たりの価格という項目があると考えることができる。
請求書を完成させるには、下段の表の最後に、部品の小計、送料、各項目の合計金額の3つの欄を追加する必要があります。
単純な計算式を使って、各項目を独立して計算することができます。しかし、請求書が多くの項目のものであった場合、すぐにはるかに複雑になる可能性があります。そこで、代わりにセルE4に置かれた1つの数式で"Subtotal"を計算することができます。
=B4:B8 * D4:D8
乗算する数値は、どちらも単一のセルではなく、セル範囲です。各行で、数式は配列から適切なセル値を取り出し、ユーザがさらに入力しなくても、結果を正しいセルに配置します。
送料を一律1.50ドル/ポンドとすると、セルF4に同様の数式を入力して送料欄を計算することができます。
=C4:C8 * B11
今回は乗算演算子の左側にある配列のみを使用しました。結果は相変わらず自動入力されますが、今回はそれぞれ静的な値に対して乗算しています。
最後に、小計で使ったのと同じタイプの数式を使って、セルG4に小計と送料の配列を追加して合計を求めることができます。
=E4:E8 F4:F8
また、5つの数式を1つの数式にする、シンプルな配列式ができました。
将来的には、このスプレッドシートに税金の計算を追加する場合、各項目を変更するのではなく、一つの数式を変更するだけで済むようになります。
Excel の配列で条件式を管理する
前の例では、配列にまたがる基本的な算術式を使って結果を出しましたが、より複雑な状況では、単純な算術式ではもはや通用しません。
例えば、先程のインボイスを作成した会社が、別の運送会社に変更したとします。その場合、通常の配送料は下がるかもしれませんが、一定の重量を超えるものには手数料がかかる可能性があります。
新しい送料は、標準送料が1ポンドあたり1.00ドル、高重量送料が1ポンドあたり1.75ドルとなります。High-weightは、7ポンド以上の重量に適用されます。
行ごとに送料を計算するよりも、配列式にIF条件文を入れることで、各行ごとの適切な送料を判断することができます。
=IF(C4:C8 < 7, C4:C8*B11, C4:C8*B12)
1つのセルの関数を変更し、料金表に新しい送料を追加するだけで、送料欄全体が重量に基づいて計算されるだけでなく、合計も自動的に新しい価格をピックアップすることができます。
このため、将来的にコスト計算を変更する場合でも、1つの機能だけを変更すれば済むようになっています。
複数の、あるいは異なる条件式を組み合わせることで、さまざまなアクションを実行することができます。このロジックは、同じデータの配列に対して複数の方法で使用することができます。たとえば
- 小計が一定額以上の場合、送料を無視するIF関数を追加することができます。
- 税金のテーブルを追加して、異なるカテゴリーの製品に異なる税率で課税することができます。
どんなに複雑な請求書でも、1つのセルを編集するだけで、請求書のどの部分も再計算されます。
例N 番目の数値の平均化
大規模なデータの計算や更新を簡単に行えるだけでなく、テスト用にデータセットの一部を取り出して使用することもできます。
次の例で示すように、検証のために大規模なデータセットからデータの一部を抜き出すことは、配列式を使えば簡単にできます。
上記の例では、セル D20 はセンサ 1 からの n 番目の結果を平均化する簡単な関数を使用している。この場合、セル D19 の値を使って nth を定義することで、平均を決定するサンプルサイズを制御することができる。
=AVERAGE(IF(MOD(ROW(B2:B16)-2,D19)=0,B2:B16,""))
これにより、大規模なデータセットを迅速かつ容易にサブセットに分割することができます。
Excelで配列式をマスターすることは必須です。
これらの簡単な例は、配列式の背後にある論理を把握するための良い出発点です。いくつかのセル参照と関数を調整するだけで、現在も将来も複数の操作を迅速に実行できるように、これらの例を少し考えてみてください。行列式は、データを切り刻むための軽量で効率的な方法です。高度な統計計算を自信をもって行えるように、Excel学習の最初の段階でマスターしておきましょう。
