Excel で配列数式を使用する方法

配列数式は、Excel を用途の広いものにするツール キットの重要な部分です。ただし、これらの表現は初心者にとっては困難な場合があります。

一見複雑に見えますが、基本は簡単に理解できます。これらの数式は複雑な Excel データの管理を簡素化するため、配列の学習は便利です。

Excel の配列数式とは

配列は、グループにまとめられた列、行、または組み合わせのセルのコレクションです。

Microsoft Excel では、配列数式という用語は、一度に 1 つのセルではなく、そのような範囲のセルに対して 1 つまたは複数の操作を一度に実行する一連の数式を指します。

以前のバージョンの Excel では、Ctrl + Shift + Enterを押して配列関数を作成する必要があったため、CSE (Ctrl、Shift、Escape) 関数という名前が付けられましたが、Excel 365 ではそうではなくなりました。

例として、特定の日の 100 ドルを超えるすべての売上の合計を取得する関数が考えられます。または、5 つのセルに格納されている 5 つの異なる数値の長さ (桁数) を決定します。

配列数式は、大規模なデータセットからサンプル データを取得するための最適な方法でもあります。1 つの列のセル範囲、1 つの行のセル範囲、または複数の行と列にまたがるセルを含む引数を使用できます。

また、特定の値の上または下の数値のみをプルしたり、n 番目ごとの値をプルしたりするなど、配列数式にデータをプルするときに条件ステートメントを適用することもできます。

この詳細な制御により、データの正確なサブセットを取得したり、不要なセル値を除外したり、テスト目的で項目のランダム サンプルを取得したりすることができます。

Excel で配列を定義および作成する方法

配列数式は、最も基本的なレベルで簡単に作成できます。簡単な例として、次の品目情報を持つ顧客の部品注文の請求書​​を考えることができます: 製品の WeU (在庫管理単位)、購入した数量、購入した製品の累積重量、および購入した単位あたりの価格。

請求書を完成させるには、下のテーブルの最後に 3 つの列を追加する必要があります。1 つは部品の小計、1 つは送料、3 番目は各項目の合計価格です。

簡単な式を使用して、各項目を個別に計算できます。しかし、請求書が多数の明細項目の場合、すぐに複雑になる可能性があります。代わりに、「小計」は、セル E4 に配置された単一の数式で計算できます。

=B4:B8 * D4:D8

乗算している両方の数値は、単一セルではなくセル範囲です。各行で、数式は配列から適切なセル値を取得し、ユーザーからの追加入力なしで結果を正しいセルに配置します。

1 ポンドあたり 1.50 ドルの均一配送料を仮定すると、セル F4 に配置された同様の数式を使用して配送料列を計算できます。

=C4:C8 * B11

今回は、乗算演算子の左側の配列のみを使用しました。結果は引き続き自動的に入力されますが、今回はそれぞれが静的な値に対して乗算されます。

最後に、小計に使用したのと同じタイプの数式を使用して、セル G4 に小計と送料の配列を追加して合計を取得できます。

=E4:E8 + F4:F8

繰り返しますが、単純な配列数式により、そうでなければ 5 つの数式が 1 つになりました。

将来、このスプレッドシートに税金の計算を追加する場合、各明細項目を変更するのではなく、単一の式を変更するだけで済みます。

Excel 配列での条件の管理

前の例では、配列全体で基本的な算術式を使用して結果を生成していますが、より複雑な状況では単純な計算は機能しなくなります。

たとえば、前の例で請求書を作成した会社が別の運送業者に変更されたとします。その場合、通常配送は値下げされる可能性がありますが、特定の重量を超えるオブジェクトには料金が適用される可能性があります.

新しい配送料では、標準配送料は1 ポンドあたり 1.00 ドルになり、重量の大きい配送料は1 ポンドあたり 1.75 ドルになります。重量のある配送は、7 ポンドを超えるものに適用されます。

行ごとに各配送料を計算するのではなく、配列数式に IF 条件ステートメントを含めて、各行項目の適切な配送料を決定できます。

=IF(C4:C8 < 7, C4:C8*B11, C4:C8*B12)

1 つのセルの関数をすばやく変更し、送料表に新しい送料を追加すると、送料の列全体が重量に基づいて計算されるだけでなく、合計も自動的に新しい送料が反映されることがわかります。価格。

したがって、請求書は将来にわたって保証され、コスト計算に対する将来の変更が 1 つの機能のみを変更するだけで済みます。

複数または異なる条件を組み合わせることで、さまざまなアクションを実行できます。このロジックは、同じデータ配列に対していくつかの方法で使用できます。例えば、

• 小計が特定の金額を超える場合に出荷を無視するために、追加の IF 関数を追加できます。
• 税表を追加して、さまざまなカテゴリの製品にさまざまな税率で課税することができます。

請求書がどんなに複雑であっても、1 つのセルを編集するだけで請求書の一部を再計算できます。

例: N 番目の数値の平均化

大量のデータの計算と更新を簡単にするだけでなく、配列数式はテスト目的でデータセットのスライスを取得できます。

次の例が示すように、配列数式を使用すると、検証目的で大規模なデータセットからデータのスライスを簡単に取得できます。

上記の例では、セル D20 は単純な関数を使用して、センサー 1 からの n 番目ごとの結果を平均化します。この場合、セル D19 の値を使用して n 番目を定義し、平均を決定するサンプル サイズを制御できます。

=AVERAGE(IF(MOD(ROW(B2:B16)-2,D19)=0,B2:B16,""))

これにより、大規模なデータセットをサブセットにすばやく簡単にセグメント化できます。

Excelで配列数式をマスターすることは不可欠です

これらの単純な例は、配列数式の背後にあるロジックを理解するための出発点として適しています。いくつかのセル参照と関数を微調整するだけで、現在および将来に複数の操作を迅速に実行できるように、いくつか考えてみてください。配列数式は、データを細分化するための軽量で効率的な方法です。高度な統計計算を自信を持って行うことができるように、Excel 学習の旅の最初にそれらを習得してください。