「配列」ってなんでしょう？
「1,2,3,4,5」「A,B,C,D」のように複数の値の集合体を配列と呼びます。
「そんなの使ったことない…」という方もいるかもしれません。
しかし、無意識に使っている場合も多い、意外と身近な存在です。
配列の計算の考え方を使えば、とても効率的な数式や関数を組めます。
さらに Excel2021 以降ではスピル機能が利用でき、配列を扱うことがとても簡単になりました。
この記事では、
- 配列数式とスピルの違い
- 2次元配列
- 配列の計算方法
- 配列のデメリット
- 配列の応用例
についてわかりやすく解説しています。
配列とは
配列とは英語で「ARRAY」
冒頭で述べたとおり、複数の値の集合体です。
配列の例 {1,8,5,3,12} {“りんご”,”みかん”,”すいか”｝
それぞれの値をその配列の「要素」といいます。
配列数式とスピル
配列の複数の要素を、そのままセル上に表記させるには通常の入力ではできません。
ひとつのセルには、ひとつの値しか入力できないからです。
従来、エクセルのセル上に配列を入力するには「配列数式」という特殊な入力が必要でした。
そして Excel 2021 以降のバージョンで「スピル」という機能が導入され、通常の入力と同じように配列をセル上に表記できるようになりました。
またスピル機能に対応した関数も次々と導入され、配列を手軽にあつかえるようになりました。
「配列数式」と「スピル」についてそれぞれ解説していきます。
配列数式
まずは「配列数式」。
例として {10,20,30} という配列をセル上に表記します。
すでに何度か登場していますが、
エクセルで配列を入力するには、配列全体を { } で囲み、要素を , で区切ります。
まず配列の要素数分のセルを選択。
( ここでは3セル )
A1セルに「={10,20,30}」と入力。
入力を確定するときに
Ctrl + Shift + Enter を押して確定。
通常の入力時、Enter を押して確定するところを、
Ctrl + Shift + Enter を押して確定します。
すると、全体が { } で囲まれた数式が入力されます。
この数式が「配列数式」です。
配列数式をあらわす外側の{ } は、手動で入力しても配列数式にはなりません。
かならずCtrl + Shift + Enter を押して数式を確定しなければなりません。
そのため「CSE数式」と呼ばれたりもします。
単純に配列を表示するだけでなく、数式の一部に配列を使用するときなどもこの入力方法が必要です。
セル参照の配列の例
セル参照をして、セルの値を配列としてあつかうときも配列数式で入力できます。
配列数式の各セルには、かならず同じ数式が入ります。
そのため、配列の一部だけ編集することはできません。
配列全体を選択してから編集し、再度 Ctrl + Shift + Enter を押して確定します。
配列数式まとめ
・ まず配列の範囲を選択
・ アクティブセルに数式を入力
・ Ctrl + Shift + Enter を押して確定
・ 配列の一部だけ変更することは不可
スピル
Excel 2021 以降のバージョンでは「スピル」という機能が使えます。
スピルとは「こぼれる」「あふれる」という意味の英語です。
直接的に、配列を入力できます。
配列数式との違いは
- 事前に範囲を選択する必要がない
- Ctrl + Shift + Enter のような特殊な入力は不要
スピルの特徴
スピルした範囲は、青い線で囲まれる
先頭セル以外のセルの数式は、グレーで表示される
「ゴースト」といいます
実際に数式が入力されているのは先頭セルだけです。
数式を編集するときは、数式の入力された先頭セルだけを編集します。
スピルする範囲にデータがすでに入っているときは、エラーになります。
スピル範囲全体を、参照することが可能です。
「A1#」のように、セル番地のあとに # を入力。
A1セルのスピル範囲全体を参照することになります。この # が「スピル範囲演算子」です。
ダイアログの中でも、 # （スピル範囲演算子）が使えます。
セル範囲を配列として参照できるのは、配列数式と同様です。
配列の要素数が変わっても、スピルは自動的に対応してくれます。
このように、要素数があとから変わるのが「動的配列」です。
そのため、スピルを「動的配列数式」とも呼びます。
スピルまとめ
・ 事前の範囲選択は必要ないが、じゅうぶんに空白がないとエラーになる
・ 通常の入力方法でOK
・ # を使ってスピル範囲を参照できる
・要素数の変化にも対応
配列の入力にあまり慣れていない人でも、スピルでの入力は非常に楽にできるはずです。
「値が多いときは、勝手にスピル (こぼれて) してくれる」という感覚で、じゅうぶんだからです。
対応したバージョンなら、スピル機能に特別な設定は必要ありません。
以降の章では、スピル機能の使用を前提に話をすすめていきます。
スピル以前のバージョンのかたは、配列数式で置きかえてください。
2次元配列
配列には「次元」という概念があります。
１次元は１方向だけ。２次元では「タテ」「ヨコ」の２つの方向があります。
セル上に２次元配列として入力するには、, (カンマ)と ; (セミコロン)の２つの記号が必要です。
={1,2,3;4,5,6;7,8,9}
, はヨコ(行方向)
; はタテ(列方向)の区切り
VBA ( マクロ ) であつかう配列変数は、３次元でも４次元でも設定できます。しかし一般的な使用は、２次元まででしょう。
エクセルのセルは行と列の２次元なので、２次元の配列と相性がいいからです。
実際、セルの値を２次元の配列変数に代入したり、逆にセル上に２次元配列の値を入力したり、といった操作はマクロではひんぱんに行われます。
配列の計算
いろいろな配列の計算を検証してみましょう。
={1,2,3}*10
1次元配列と、１つの値の演算。
１つの値と、それぞれの要素との演算結果になります。
それぞれの要素に 10をかけた配列になる
* 以外でも同様です。
セル参照を使った使用例
D2:D8 の金額に、D10の消費税率をかけて、それぞれの税額をだします。
数式は F2セルにしか入力してないので、D10を絶対参照にする必要はありません。
配列を文字で演算した例
={1,2,3;4,5,6}*10
2次元配列と、１つの値の演算です。
それぞれの要素に 10をかけた配列に
={1,2,3}*{10,20,30}
同じ要素数の１次元配列と１次元配列の演算です。
「1*10」「2*20」「3*30」というように、同じ順序の要素をそれぞれ計算した配列になります。
要素数がちがう配列同士ではどうなるでしょう。
たりない要素の分、エラーになってしまいます。
使用例 同じ要素数の配列*配列
「金額」列に、「単価」かける「在庫数」の結果を入力
={1,2,3;4,5,6}*{10;20}
２行３列の２次元配列と、２行１列の１次元配列の計算です。
１行目、２行目のそれぞれの要素を計算した配列になる
={1,2,3}*{10;20;30}
ヨコ１行 かける タテ１列の計算です。
それぞれの行・列をかけあわせた
3 x 3 の２次元配列になる
使用例
かけ算の九九表は、スピルを使うと簡単につくれます。
なんとなく配列同士の計算がイメージできたでしょうか？
「同じ要素数同士の配列」または「配列と１つの値」の計算が基本になります。
配列のデメリット
配列にはデメリットもあります。
配列は「表に組み込みにくい」
配列数式・スピルにかぎらず、配列そのもののデメリットです。
並べ替え(フィルター）ができない
前項の「配列の計算」で紹介した表です。
B列の数値と C列の数値をかけ算した数式が、D2セルに入力されています。D2:D8の範囲にスピルしています。
この表にフィルターを設定。
並べ替えしようとするとエラーになる！
スピルと関係のない、他の列を操作しても同じです。
とにかく配列を並べ替えることはできません。
テーブルに組み込めない
テーブル内で配列数式やスピルを使用することはできません。
データベース型の表には向かない
「フィルター」「テーブル」、ともに「データベース型の表」に向いた機能です。
「データベース型の表」とはどんな表でしょう？
データベースとは、日々追加されるデータをひたすら蓄積していくための表です。
集計・ソート(並び替え)・フィルター・グラフ化などの機能を使ってデータを分析するとき、その元となるのがデータベースです。
行ごとにまとまったデータとして、データベースが構成されています。行ごとのデータ順序が固定されている、タテ方向 (列方向) の配列数式はデータベースには向いていないのは当然と言えます。
したがって、これはデメリットとは考えない方がいいでしょう。
「テーブル」「パワークエリ」「フィルター」など、データベース型の表を扱う優秀な機能がエクセルにはあるからです。
使いどころの問題ということですね。
配列数式・スピルの使用例
配列数式 (スピル) はアイデア次第で、いろいろな場面で使えます。
前章をふまえると、「表 (データベース) の外でデータを抽出・集計する」のが基本形です。
〇〇県〇〇市の形にする
配列の要素と1文字の結合
県名と市名の配列に「県」と「市」を加えて、両者を結合します。
配列に１文字加えると、すべての要素に演算されるということがポイントです。
はじめの２次元配列を、２次元のままにするパターンもあります。
干支(えと)を表示
関数の引数に、配列を入れる
A2セルの年を干支にして B2セルに表示します。
INDEX関数の引数は
INDEX(配列,行,列)
という形です。
まず INDEX関数の第１引数に、干支を配列にして入れます。１行・要素数 12の1次元配列です。
行はすべて 1行なので省略。
列は MOD関数を利用します。「 MOD(A2,12) 」は A2セルの数字を 12で割った余りです。0～11を1～12にするために＋１します。
空白セルがある行数を表示
論理値の配列
表の中の、空白セルがある行をまとめて表示します。
ここで使われている関数を順に説明しましょう。
まず「=A1:A15<>”” 」という式は、「A1:A15」の範囲が空白か空白でないかという「論理値の配列」になります。
同様に B列と C列の配列。これらをかけ合わせると…
同じ行に1つでも FALSEがあれば 0、すべて TRUEなら 1という配列ができます。
これを IF関数の第1引数に。
1は TRUE、0は FALSEに換算です。
- 1 (TRUE) の場合は空白 ( “” )
- 0 (FALSE) の場合は ROW(A1:A15)
という配列になります。
ROW(A1:A15) とは A1:A15 の行数、つまり 1から 15の数字です。
最後に UNIQUE関数 (エクセル2021以降) で、配列の要素をまとめます。
空白セルのない行は「空白」、空白セルのある行はその行数が表示されます。
FILTER関数 (おなじくエクセル2021以降) で空白セルにフィルターをかけてもいいです。
「種類」「サイズ」を結合して検索
結合した配列からデータを検索する
表からデータを検索するときに、複数の要素を合体させて検索したいとき、ありますよね。
以下の例では「Tシャツ」の「Mサイズ」を検索するため、検索値と検索範囲を結合させています。
VLOOKUP関数の互換として、INDEX + MATCH 関数を使っています。
まとめ
アイデア次第で非常に効率的な数式が組めるのが、配列です。
スピル機能以降に導入された関数は、配列を操作する関数も多いです。対応したバージョンを使用している方はどんどんスピル機能を使用してみましょう。
最後に「互換性」についてすこし触れます。
スピルを利用したファイルを、古いバージョンのエクセルで開くと、自動的に配列数式に変換されます。
ただ、スピル範囲演算子 (#) を使用しているとエラーになります。
もちろん、スピル以降の新しい関数を古いバージョンで使うことはできません。