配列定数と言えば、数式の中に配列定数を入れることによって数式の中の無駄な繰り返しが無くなり、その結果としてシンプルな数式になるというメリットがあります。そして、もう一つの効果として、シート上に表を入力しなくても簡単な検索または変換ができるということです。
ところで、Excelでは「データを検索するなら別の表を入力すべき」という大原則があります。それはシート上に入力したほうが分かりやすく、検索元の表に変更があったときに修正がしやすいからです。しかし、なんでもかんでもシート上に入力したほうが良いわけではありません（後述）。状況に応じて使い分けるべきです。
そこで、今回は、簡単な配列定数を数式の中に記述することによって検索または変換をする数式の練習問題と、二次元の配列定数の基本について解説します。
目次
１．配列定数の基本（復習）
問題
A列にアルファベット＋数字で構成されるコード番号をランダムに入力した。AまたはBまたはCから始まるセルの個数の合計を求めなさい。
解説
一次元の配列定数（array constants）の入力方法についてはすでに別の記事で解説しています。詳しくはこちらの記事をご覧ください。
中カッコ{ }のなかに複数の値を入力することができて、カンマで区切ります。COUNTIFでA、B、Cから始まるセルの個数を数えて合計をします。なお、「Aから始まる」「Bから始まる」「Cから始まる」の３つは重複することが無いので（排反事象）、合計するだけで良いです。
- =SUM(COUNTIF(A:A,{"A*","B*","C*"}))
同じ形をした複数の計算式を1つの式で表すことができるので便利です。また、アスタリスクを外に出して連結演算をしても良いです。配列定数は通常の定数と演算をすると、配列定数内の各要素と演算をします。
- =SUM(COUNTIF(A:A,{"A","B","C"}&"*"))
２．一次元の配列定数を用いて変換する
（１）連続する整数値に変換する
問題
A列の都道府県のうち、東京、大阪、福岡、北海道をそれぞれ1、2、3、4としなさい。また、東、大、福、北から始まる都道府県をそれぞれ1、2、3、4としなさい。ただし、該当しない都府県は5とする。
解説
複数の文字列を連続する整数値に変換するのにIF関数を使ってはいけません。
MATCH関数で、範囲の代わりに配列定数を指定すると左から数えた場所を返します。
第3引数は完全一致なのでFALSEまたは０です。完全一致なのでこれら4つの都道府県に該当しないものはエラーとなります。
- =MATCH(A2,{"東京","大阪","福岡","北海道"},0)
IFERROR関数でエラーを回避します（参考：【Excel】IFERROR関数を用いてエラーを回避する方法、ISERRORとの違いについて）。これで完成です。
- =IFERROR(MATCH(A2,{"東京","大阪","福岡","北海道"},0),5)
次に、完全な一致ではなく先頭にあるかどうかを判定するにはFIND関数を使います。
FIND関数によって、都道府県名のなかに、東、大、福、北の文字が含まれていれば見つかった場所を表す数値、含まれていなければエラー値#VALUE!とする配列定数に変換することができます。
先頭にあるということは文字の位置が「1」なので、この配列定数の中から「1」を検索すればよいです。第3引数は完全一致なのでFALSEまたは０です。
- =MATCH(1,FIND({"東","大","福","北"},A2),0)
これら4つの都道府県に該当しなければエラーになります。IFERROR関数でエラーを回避します。これで完成です。
- =IFERROR(MATCH(1,FIND({"東","大","福","北"},A2),0),5)
別解
COUNTIF関数やSEARCH関数を用いる方法もあります。COUNTIF関数で数えることによって、東、大、福、北から始まる場合は１個、そうでない場合は０個となりますから、「1」を検索すればよいです。
- =IFERROR(MATCH(1,COUNTIF(A2,{"東","大","福","北"}&"*"),0),5)
（２）数値の範囲を連続する整数値にする
問題
A列の年齢のうち18歳未満を10、18歳以上65歳未満を20、65歳以上を30としなさい。
解説
範囲を特定の値に変換するときには必ず範囲に漏れや重複がないかを確認します。特に境界での処理に注意します。18歳未満、18歳以上65歳未満、65歳以上の３つの範囲は漏れも重複もなさそうです。また、ちょうど18歳のときは「18歳以上」、ちょうど65歳のときは「65歳以上」にそれぞれ含まれるので問題ありません。
MATCH関数は、第2引数に昇順の配列定数を指定し、第3引数にTRUEまたは1を指定するか、または省略すると「～以上」の検索をすることができます。
これを利用して、0以上、18以上、65以上をそれぞれ1，2，3に変換します。
- =MATCH(C2,{0,18,65})
連番に変換できたら、足し算・引き算・掛け算・割り算をすることでいろいろな数列に変えることができます。
- =MATCH(C2,{0,18,65})*10
（３）LOOKUP関数による検索
問題
A列の時刻のうち8:00～11:59をA、13:00～17:59をB、18:00～22:59をCとしなさい。ただし、1分未満については考慮しなくてよい。
解説
範囲を特定の値に変換するときには必ず範囲に漏れや重複がないかを確認します。8:00～11:59、13:00～17:59、18:00～22:59の３つの範囲以外に0時～7時台、12時台、23時台があります。これらは空白とします。
連続する範囲を特定の値に変換するのにIF関数を使ってはいけません。
ここではLOOKUP関数を使ってみましょう。時刻のシリアル値の形式で入力されているセルは24倍すると時間単位の小数になります（参考：【Excel】時間の掛け算、シリアル値と時間の換算、時間の合計を求めるときの注意点）。なお、表示形式を標準とすることを忘れてはいけません（以下、省略）。
- =A1*24
この値を用いて、{0,8,12,13,18,23}時以上の範囲でそれぞれ検索をします。
- =LOOKUP(A1*24,{0,8,12,13,18,23},{"","A","","B","C",""})
３．定数であることを理解しよう
（１）本当に別の表を用意する必要があるのか？
さきほどのLOOKUP関数の例では、変換する前の値と変換後の値を表として入力し、VLOOKUP関数等で検索する方法が一般的です。
- =VLOOKUP(A1*24,$E$2:$F$7,2)
それに対し、18歳未満、18歳以上65歳未満、65歳以上を「連番」にするだけであればわざわざ別表を作る必要もありませんし、連番を求めるのにVLOOKUP関数を使う必要性もありません。前述のようにMATCH関数を使ったほうがシンプルです。
（２）セル範囲か配列定数か
ところで、「A列の数値を24倍せよ」と言われたら、通常は数式の中で「*24」と数値を入力します。数式の中で数値を用いることを「定数」ということがあります。「24」は定数です。
しかし、1日＝24時間であることを別のセルに入力してそれを絶対参照によって参照したほうが良いのでは？という考え方もあります。いったんセルに入力してからそれを参照する手法を「セル参照」といいます。
- =A1*$E$1
数式の中で利用する値を定数（固定された値）として入力せず、わざわざセルに入力してから参照するのは、「変数」として入力して再計算をするのが表計算の本質であり、それが表計算ソフトを使う最大のメリットだからです（参考：【Excel】関数の前に「変数」を理解して表計算の本質を知れ！）。
簡単に言えば、Excelの数式は「数式の中で使用する値が変化するかもしれない」という前提で作るべきなのです。
しかし、1日＝24時間が変化することは考えにくく、また、単に2倍、3倍するだけであれば、直接「*2」「*3」と入力したほうが簡単です。定数を使うのかセル参照を使うのかは明確な基準はありません。メンテナンス性等を考慮しながら状況に応じて選択したほうが良いです。
このことは、配列定数やセル範囲の場合も同様です。配列定数は「定数」です。配列定数は原則として次のような場合に使います。
- 変化しないことがあらかじめ分かっている場合
- 検索する表が非常に簡単な場合
- VLOOKUPやINDEXを使う必要が無く、簡単な計算で処理できる場合
表の内容が変わる可能性がある場合や検索する表が大きい場合には、配列定数を使わず別の表を作成して参照したほうが良いでしょう。
４．二次元の配列定数
（１）二次元の配列定数の文法
ここまで、一次元の配列定数について解説してきましたが、Excelでは二次元の配列定数を作ることも可能です。二次元の配列定数はCSV（カンマ区切り形式）と同じ考え方です。CSVについては別の記事で詳しく解説しています。詳しくはこちらの記事をご覧ください。
「,」（カンマ）はデータの区切りを表しますが、「;」（セミコロン）で行の区切りを表すことができます。セミコロンが途中に2つあれば、3行の配列定数です。
すべてのデータをセミコロンで区切ると、縦1列の配列定数になります。これを縦ベクトルまたは垂直定数（vertical constant）といいます。
これに対して、横1行の一次元配列（カンマ区切り）のことを横ベクトルまたは水平定数（horizontal constant）といいます。
（２）二次元の配列定数の演算
問題
次の計算結果を表示しなさい。
- ={1,2;3,4;5,6}*2
- ={1,2;3,4;5,6}-{1;2;3}
- ={24;12;6}/{3,2}
解説
={1,2;3,4;5,6}と入力します。1つのセルには1つのデータしか表示されません。配列定数の場合は先頭のデータが表示されます。
セル範囲A1:B3を選択します。F2キーを押して編集モードにします。
CtrlキーとShiftキーを同時に押しながら、Enterキーで確定します（略してCSEという）。これで、１～６が表示されます。このように複数のセルに配列の内容を表示する数式の入力の仕方を「配列数式（array formula）」と言います。
- {={1,2;3,4;5,6}}
セル範囲A1:B3を選択します。F2キーを押して編集モードにします。そして、2倍します。
この状態で、CtrlキーとShiftキーを同時に押しながら、Enterキーで確定します。これで全ての要素が2倍されます。二次元配列と定数を演算した場合、全ての要素に対して演算されます。これは足し算・引き算・掛け算・割り算のほか文字列連結演算や累乗等の演算子でも同じです。
セル範囲A1:B3を選択します。F2キーを押して編集モードにします。今度は縦ベクトル{1;2;3}を引いてみましょう。
CtrlキーとShiftキーを同時に押しながら、Enterキーで確定します。
二次元配列と、同じ行数の縦ベクトルを演算した場合、対応する行同士で演算されます。同様に、二次元配列と、同じ列数の一次元配列（横ベクトル）を演算した場合は、対応する列同士で演算されます。
いったんセル範囲A1:B3を選択して削除します。セル範囲A1:B3を選択して={24;12;6}/{3,2}を計算してみましょう。
縦ベクトルと横ベクトルを演算した場合は各要素を総当たりで演算します。その結果、3行の縦ベクトルと2列の横ベクトルを演算すると3行2列の二次元配列になります。
５．配列定数を用いた簡単な検索の例
（１）VLOOKUP関数、HLOOKUP関数
問題
配列定数を用いて、A列の年齢のうち18歳未満を「こども」、18歳以上65歳未満を「一般」、65歳以上を「高年齢」としなさい。
解説
データを検索するには検索する数値を縦に入力する方法と、横に入力する方法があります。それぞれ、VLOOKUP関数、HLOOKUP関数を使います。
VLOOKUP関数の第2引数で配列定数を指定することができます。セミコロンの位置に注意してください。
- =VLOOKUP(A2,{0,"こども";18,"一般";65,"高年齢"},2)
HLOOKUP関数を使うこともできます。
- =HLOOKUP(A2,{0,18,65;"こども","一般","高年齢"},2)
VLOOKUP関数、HLOOKUP関数のいずれを使うべきかは好みの問題です。検索値とデータの組が分かりやすいのがVLOOKUP関数、年齢の区切りが分かりやすいのがHLOOKUP関数です。
（２）INDEX関数
問題
セルD1に1から10までの整数、セルD2に1から3までの整数を入力する。セルD2の値が1，2，3であればそれぞれ消費税を10%とする税抜、税込、税額の金額を表しているものとする。
セル範囲A1:A10の金額を税抜価格として、該当する行番号の金額（税抜、税込、税額）を求めなさい。
解説
対応する行番号の価格（税抜価格）を求めるだけであれば、INDEX関数を使えばよいです。
- =INDEX(A1:A10,D1)
また、1、2、3をそれぞれ1倍、1.1倍、0.1倍に変換するのもINDEX関数が使えます。
- =INDEX({1,1.1,0.1},D2)
これらを総当たりで検索するには、縦ベクトルであるセル範囲A1:A10と、横ベクトルである配列定数{1,1.1,0.1}を演算すればよいです。
10行3列の二次元配列ができますから、これを用いてINDEX関数で検索すればよいです。
- =INDEX(A1:A10*{1,1.1,0.1},D1,D2)
（３）IF関数
問題
セル範囲A2:E9のデータの中からVLOOKUP関数を用いて、セルH1に入力した守備位置の選手を検索し、その選手名を表示しなさい。なお、中はセンターポジション、くろまてはウォーレン・クロマティ元選手のことです。
解説
VLOOKUP関数は左端のデータを検索する関数なので、右の列を検索して左の列の値を取得することはできません。
ところで、IF関数は論理式に１と０を入力すると、それぞれTRUEとFALSEとみなされるので真の場合と偽の場合を返します（参考：IF関数の条件に「等号や不等号が必要」と思っているなら、IF関数を勉強し直してほしい）。
これを利用して、論理式を横ベクトルの配列定数、真の場合と偽の場合を縦ベクトル（セル範囲）とすることによって総当たりとなり、結果として１つの二次元配列を作ることができます。
- IF({1,0},D2:D9,B2:B9)
これによって、任意の列を左端にすることができ、VLOOKUP関数での検索が可能となります。このようにIF関数を使うと、複数の縦ベクトルを統合して1つの2次元配列にすることができます。ただし、大きな表になると配列同士の計算に時間を要するため、動作が遅くなります。
- =VLOOKUP(H1,IF({1,0},D2:D9,B2:B9),2,0)
解説は以上です。
