【Excel】配列を使った数式を使ってみよう!(配列数式と配列定数)

PC

今回は、Excelにおける配列についてです。配列を上手く用いることで、計算を効率良く行うことができます。

配列数式と配列定数

配列を用いた数式のことを配列数式と言います。また、配列において定数(数値、文字列、論理値、エラー値)で構成されるものを配列定数と言います。

言葉ではわからないので、配列がどのようなものかを見てみましょう。

水平(1行のみ)の配列定数

A1セルからC1セルを選択した状態で、数式バーに下記のように入力します。

={1,2,3}

tayuyu
tayuyu

この{1,2,3}が配列だよ。

図1:水平定数配列(例)

配列の行の要素はカンマ「,」で区切ります。

CtrlキーとShiftキーとEnterキー(以下、Ctrl+Shift+Enter)で数式を確定させます。(※)

{={1,2,3}}

{」、「}」(黄色中括弧)は、配列定数を作成するために数式バーに自ら入力した中括弧です。「{」、「}」(赤色中括弧)は、Ctrl+Shift+Enterで数式を確定させたことによる中括弧です。

配列数式を使用する場合は、Ctrl+Shift+Enterを押して数式を確定させます。これによって、数式が中括弧「{」、「}」で囲まれます。Ctrl+Shift+Enterで数式を確定させないと配列数式として機能しないので注意しましょう。

(※Excel365を使用している場合は除きます。)

tayuyu
tayuyu

配列を使いたいときは、Ctrl+Shift+Enterを押すのを忘れないようにしよう。

tayuyu
tayuyu

1行\(j\)列のイメージだね。

垂直(1列のみ)の配列定数

A1セルからA3セルを選択した状態で、数式バーに下記のように入力します。

={1;2;3}

図2:垂直定数配列(例)

配列の列の要素はセミコロン「;」で区切ります。

Ctrl+Shift+Enterで数式を確定させます。

{={1;2;3}}

tayuyu
tayuyu

\(i\)行1列のイメージだね。

2次元の配列定数

A1セルからB2セルを選択した状態で、数式バーに下記のように入力します。

={1,2;3,4}

図3:2次元定数配列(例)

Ctrl+Shift+Enterで数式を確定させます。

{={1,2;3,4}}

tayuyu
tayuyu

\(i\)行\(j\)列のイメージだね。

配列定数には、数値、文字列、論理値、エラー値を使用できます。

文字列は、ダブルクォーテーション「”」で囲みましょう。

配列数式を使用した場合としない場合

表のような場合を考えます。

ABC
1商品名金額(日本産)金額(アメリカ産)
2りんご300250
3ぶどう800900
4もも500400
5なし300200
6オレンジ30050
表:例

表は商品の日本産とアメリカ産の金額です。例えば、日本産のりんごは300円、アメリカ産のりんごは250円という感じです。

ここで、日本産とアメリカ産の金額差の最大値を調べたいとします。

一般的な計算の流れとしては、次のようになると思います。

計算の流れ
  • STEP1
    各商品の金額差を計算する
  • STEP2
    MAX関数で金額が一番大きい値を取得する

    MAX関数は、最大値を取得する関数です。

配列数式を使用しない場合

STEP1 金額差の表示

各商品について、日本産の金額からアメリカ産の金額を引きます。

図4:配列を使用しない場合(STEP1)

今回は、D2セルに「=B2-C2」、D3セルに「=B3-C3」、…、D6セルに「=B6-C6」を入力しました。

SETP2 金額差が一番大きい値の取得

MAX関数を使用して、金額差の最大値を取得します。

図5:配列を使用しない場合(STEP2)

今回は、D8セルに下記のように入力します。

=MAX(D2:D6)

金額差(D2セルからD6セル)の最大値である「250」が取得できました。

配列数式を使用する場合

配列を使用することでSTEP1は省略できます。ここでは、配列を用いた例のひとつとして、配列を使用してD列に金額差を表示してみたいと思います。

STEP1(省略可)(※配列を使用してD列に金額差を表示させたい場合)

先程と同様に、各商品について、日本産の金額からアメリカ産の金額を引きますが、今回は金額(日本産)の範囲(配列)である「B2:B6」から金額(アメリカ産)の範囲(配列)である「C2:C6」を引きます。

図6:配列を使用する場合(STEP1(省略可))

D2からD6を選択した状態で次のように入力します。

=B2:B6-C2:C6

ここで、Ctrl+Shift+Enterで数式の入力を確定させると数式バーに次のように表示されます。

{=B2:B6-C2:C6}

D2セルからD6セルの数式がすべて「{=B2:B6-C2:C6}」となりました。D2セルからD6セルの数式がすべて同じ数式であるため数式に一貫性があります。

また、配列の一部のみ(例えば、D2セルのみ)を変更しようとしても、図7のように表示されるため、誤って数式を変えてしまうなどの想定外のことも起こりません。

図7:注意

SETP2 金額差が一番大きい値の取得

MAX関数を使用して、金額差の最大値を取得します。

図8:配列を使用する場合(STEP2)

数式バーに、次のように入力します。

=MAX(B2:B6-C2:C6)

Ctrl+Shift+Enterで数式の入力を確定させます。

{=MAX(B2:B6-C2:C6)}

1つの数式を入力するだけで、最大の金額差である「250」が取得できました。

tayuyu
tayuyu

範囲は配列と考えることもできるんだね。

配列数式を使用しない場合は、それぞれの商品の金額差をD列に表示させてからMAX関数で取得するという流れになりましたが、配列数式を使用すれば、それぞれの商品の金額差をD列に表示することなく、1つの数式で求めたい値を取得することができました。このように配列の使い方次第では、効率よく計算ができることになります。

数式バーで数式を選択している状態(数式の一部も可能)で「F9」キーを押すと、参照を値に変換するとができます。元に戻したい場合は、確定する前に「ESC」ボタンで戻すことができます。

例えば、図8において、数式バーの「B2:B6-C2:C6」を選択した状態で「F9」キーを押すと「{50;-100;100;100;250}」と値に変換されます。

この機能は、配列の内容の確認などに便利です。(※もちろん、配列以外でも使用できます。)

まとめ

配列数式のメリット・デメリット

配列数式のメリット・デメリットをまとめると次のような感じになります。

  • 効率よく計算できる
  • 使用する数式の数を少なくすることができる(ファイルサイズを少なくできる)
  • 数式に一貫性がでる
  • 配列の一部のみの変更はできない(入力ミスなどを防ぐことができる)
  • Ctrl+Shift+Enterを使うのが面倒・忘れやすい
  • Excelで配列を使ったことがない方にはわかりにくい

コメント

タイトルとURLをコピーしました