わえなび ワード&エクセル問題集 waenavi

当サイトはWordとExcelの実力を鍛える最高水準の特訓問題集です。リンクを張るのは自由です。みんなで勉強しましょう!

【Excel関数】シンプルな数式を作るのに「1次元の配列定数」という方法があります(基礎編)


配列定数(array constants)は、複数のデータを1つの固まりとして数式内に入力したものです。例えば、Excelに「春」「夏」「秋」「冬」と入力すると、これらは別々の4つのデータですが、中カッコで囲んで「{"春","夏","秋","冬"}」と入力すると、1つの固まりとして扱われます。

この配列定数を知ることはExcelの数式を作るうえで多くのメリットがあるのですが、最も重要なメリットは「数式がシンプルになる」ということです。同じ式を何度も繰り返して入力する必要が無くなるので、読みやすく、メンテナンスしやすい数式になります。

そこで、今回は、配列定数・基礎編として、配列定数の意味、特に1次元の配列定数の基本的な考え方、配列定数を使って数式を短くする方法について出題します。配列定数は、現在利用可能なExcelのほぼすべてのバージョンで使えますので、ぜひ習得してみてください。

目次

1.さっそく使ってみましょう

問題

A列の年齢のうち、35・40・45・50・55歳に該当する場合はTRUE、そうでなければFALSEと表示しなさい。また、これらの年齢に該当する人に「*」を付けなさい。

f:id:waenavi:20201228064345j:plain

 

解説

OR関数を挿入します(参考:【Excel】AND関数やOR関数を使うなら、まず「論理値を返す関数」であることを理解せよ)。

f:id:waenavi:20201228064502j:plain

 

OR関数の中でそれぞれ、A列の数値と35・40・45・50・55を比較します。

  • =OR(A2=35,A2=40,A2=45,A2=50,A2=55)

f:id:waenavi:20201228064546j:plain

 

これで完成です。該当する年齢の場合はTRUE、そうでない場合はFALSEと表示されますが、1つずつ比較しているのが気になります。

f:id:waenavi:20201228064623j:plain

 

そこで、配列定数を使います。「A2=」の部分はそのままで、数値データをカンマで区切り、それを中カッコ「{ }」で囲みます。中カッコの部分が配列定数です。

  • =OR(A2={35,40,45,50,55})

中カッコのいずれかの年齢に該当する場合はTRUE、そうでない場合はFALSEと表示されます。これでだいぶ数式がすっきりしました。

f:id:waenavi:20201228064712j:plain

 

IF関数を挿入します。

f:id:waenavi:20201228064800j:plain

 

どれか1つに該当すれば「*」をつけ、そうでなければ空白を返します(参考:【Excel】空白セルの判定をして、IF関数で「空白文字列」を返す方法と注意点)。

  • =IF(OR(A2={35,40,45,50,55}),"*","")

f:id:waenavi:20201228064923j:plain

 

2.配列定数とは何か

(1)配列定数の文法

配列定数array constants)とは、複数の数値データや文字列データをカンマで区切って、1つの固まりとして表現したものです。配列定数のことを単に「配列」と言うこともあります。また、データを横1列に並べたものを「1次元の配列定数」と言うことがあります。

「{35,40,45,50,55}」と入力すると、5つの数値データ(要素)で構成される、1つの配列定数(セット)となります。配列定「数」とは言っても、1つの数でなく「セット」のことです。「{35,40,45,50,55}」と入力しても、5つのデータを1つのセットとして入力しただけで何らかの計算をするわけではありません。

f:id:waenavi:20201228065342j:plain

 

ここで、配列定数の書き方をおさらいしましょう。

  • 配列全体を「{ }」で囲みます。
  • 「,」(カンマ)は要素の区切りを表します。
  • 文字列の場合はそれぞれダブルクォーテーションで囲みます。
    (例){"バーガー","ドリンク","ポテト"}は、3つの要素で、1つのセットです。
  • 数値と文字列は混ぜてもいいです。文字列だけダブルクォーテーションで囲みます。
    (例){100,"500円",200,"ポテト"}

 

(2)数式やセル参照は不可

配列定数の要素として入力できるデータは、数値、文字列、論理値(TRUE、FALSE)、エラー値 (#N/A など) です。

数値の場合、小数点やマイナスを使用することはできますが、カンマ桁区切り、パーセント、円記号のような表示形式を使ってはいけません。もし、使いたい場合は「"10%"」のように文字列としてダブルクォーテーションで囲みます。

f:id:waenavi:20201228070306j:plain

 

また、配列定数には、セル範囲の参照、計算式、関数を含めることはできません。{A1,10+20,SUM(C1:D1)}といった記述は不可です。

f:id:waenavi:20201228070453j:plain

 

3.単一のデータと配列定数の演算

(1)なぜ、OR関数で配列定数が使えるのか

さきほどの問題で「A2={35,40,45,50,55}」と入力しましたが、単一のセルと配列定数を比較すると、単一のセルと配列内の数値が全て比較演算された結果の配列定数となります。

f:id:waenavi:20201229205058j:plain

 

例えば、A列の数値が35の場合、35={35,40,45,50,55}となり、それぞれ比較して「{TRUE,FALSE,FALSE,FALSE,FALSE}」という5つの論理値で構成される配列定数が作られます。

f:id:waenavi:20201229210445j:plain

 

マイクロソフトの解説によると、OR関数の引数には「TRUEまたはFALSEなどの論理値に評価される論理式、または論理値を含む配列または参照を指定する」とされています。そして、配列内にTRUEが1つでもあれば、TRUEを返します。

f:id:waenavi:20201229205722j:plain

 

A列の数値が31の場合、31={35,40,45,50,55}となり、それぞれ比較して「{FALSE,FALSE,FALSE,FALSE,FALSE}」という5つの論理値で構成される配列定数が作られます。全部FALSEなので、OR関数もFALSEを返します。

f:id:waenavi:20201229210234j:plain

 

(2)四則演算もできる

問題

さきほどの問題で、「A2={35,40,45,50,55}」と入力する代わりに、「A2={7,8,9,10,11}*5」と入力してもよいか。

解説

{7,8,9,10,11}*5と入力したとします。さきほどと同じ結果になります。この数式は正しいです。

  • =IF(OR(A2={7,8,9,10,11}*5),"*","")

f:id:waenavi:20201229210803j:plain

 

イコールのような比較演算だけでなく、算術演算(足し算・引き算・掛け算・割り算など)をすることも可能です。配列定数が先でも後でも構いません。配列定数と単一のデータを演算した場合、配列内のすべての数値が、セルとそれぞれ演算された結果の配列定数が作られます。「{7,8,9,10,11}*5」はそれぞれの要素が5倍され「{35,40,45,50,55}」という5つの数値で構成される配列定数になります。

f:id:waenavi:20201229211213j:plain

 

<補足>

文字列連結演算も同様の計算ができます。例えば、「{35,40,45,50,55}&"歳"」と入力すると、すべての要素に連結され、「{"35歳","40歳","45歳","50歳","55歳"}」という5つの文字列で構成される配列定数になります。

f:id:waenavi:20201229211357j:plain

 

4.他の関数を使ってみましょう

(1)AND関数

問題

C列に「東野」「南田」「西村」「北尾」以外のデータがあれば「*」を表示しなさい。

f:id:waenavi:20201229214036j:plain

 

解説

C列を見ると、ほとんど「東野」「南田」「西村」「北尾」の4人であることが分かります。そこでOR関数を用いて、この4人のいずれかであれば空白を返し、それ以外の場合に「*」を表示します。

  • =IF(OR(C2={"東野","南田","西村","北尾"}),"","*")

1か所だけ別の人が入力されていることが分かります。

f:id:waenavi:20201229215456j:plain

 

別解

不一致の比較演算子「<>」を使うこともできます。この場合はAND関数を使います。

  • =IF(AND(C2<>{"東野","南田","西村","北尾"}),"*","")

AND関数の引数として配列定数を指定すると、配列内の要素が全てTRUEの場合に限りTRUEを返します。4人とは異なる人が入力されている場合は「{TRUE,TRUE,TRUE,TRUE}」となりますから、TRUEを返します。

f:id:waenavi:20201231170310j:plain

 

(2)上位3つの合計

「=SUM({1,2,3})」と入力すると6になります。SUM、AVERAGE、MAX、MIN、COUNT、COUNTAなどの集計関数は、引数として配列定数を指定することができます。

f:id:waenavi:20201231170414j:plain

 

問題

A列の数値のうち、上位3つの合計を求めなさい。

f:id:waenavi:20201231170557j:plain

 

解説

大きいほうから1~3番目の値を求めるにはLARGE関数を使います。

  • =LARGE(A1:A10,1) + LARGE(A1:A10,2) + LARGE(A1:A10,3)

f:id:waenavi:20201231170645j:plain

 

数式が長いので、順位の部分を配列定数にしてまとめます。さらに、足し算ではなくSUMを使います。これで、1位~3位の合計になります。

  • =SUM(LARGE(A1:A10,{1,2,3}))

f:id:waenavi:20201231170730j:plain

 

<補足>

「=SUM({1,2,3})」は=SUM(1,2,3)と同じなので答えは6になります。

しかし、「=SUM({1,2,"X"})」のように文字列が含まれる場合の動作が異なります。配列定数の場合は文字列の要素を除外して集計するので、3という答えになります。

SUM関数の引数に「文字列を含む配列やセル範囲」を指定することは可能ですが、文字列だけを指定することはできないので「=SUM(1,2,"X")」はエラーとなります。

 

(3)各桁の数の和

問題

セルA1に6桁の数値を入力した。各桁の和を求めなさい。

f:id:waenavi:20201231170803j:plain

 

解説

例えば、1万の位は、1万で割った数を10で割ったときの余りです(参考:MOD関数で小数部分や下位の桁を取り出す、下の位の一致判定)。

  • =MOD(INT(A1/10000),10)

f:id:waenavi:20201231170904j:plain

 

したがって、各桁の和は次のようになります。

  • =MOD(INT(A1/1),10) + MOD(INT(A1/10),10) + MOD(INT(A1/100),10) + MOD(INT(A1/1000),10) + MOD(INT(A1/10000),10) + MOD(INT(A1/100000),10)

数式が長いので、変化している部分を配列定数にしてまとめます。さらに、足し算ではなくSUMを使います。

  • =SUM(MOD(INT(A1/{1,10,100,1000,10000,100000}),10))

f:id:waenavi:20201231170941j:plain

 

累乗を用いて次のように表すこともできます。

  • =SUM(MOD(INT(A1/10^{0,1,2,3,4,5}),10))

f:id:waenavi:20201231171101j:plain

 

(4)COUNTIF関数の和

問題

セル範囲A1:A10のうち先頭が「X」「Y」「Z」の個数の合計を求めなさい。

f:id:waenavi:20201231171355j:plain

 

解説

先頭がXになっているデータの個数は、COUNTIF関数を使って求めます。検索条件としてワイルドカード「*」を使います。(参考:【Excel】COUNTIFやSUMIFの「検索条件」総まとめ、ワイルドカード、比較演算子との違い)。

  • =COUNTIF(A1:A10,"X*")

f:id:waenavi:20201231171611j:plain

 

XまたはYまたはZの場合は、COUNTIFを3つ足します。

  • =COUNTIF(A1:A10,"X*") + COUNTIF(A1:A10,"Y*") + COUNTIF(A1:A10,"Z*")

f:id:waenavi:20201231171636j:plain

 

数式が長いので、検索条件の部分を配列定数にしてまとめます。さらに、足し算ではなくSUMを使います。これで、Aの個数とBの個数とCの個数の合計になります。

  • =SUM(COUNTIF(A1:A10,{"X","Y","Z"}&"*"))

f:id:waenavi:20201231171712j:plain

 

<補足>

この問題のように、OR条件の場合は配列定数を使うと便利です。文字列だけでなく数値や比較演算子のような記号も使えます。

  • =SUM(COUNTIF(A1:A10,{10,20,30}))
  • =SUM(COUNTIF(A1:A10,{"<10",40,">=60"}))

また、SUMIF関数の検索条件に配列定数を使うこともできます。

  • =SUM(SUMIF(A1:A10,{10,20,30},B1:B10))
  • =SUM(SUMIF(A1:A10,{"<10",40,">=60"},B1:B10))

 

(5)いずれかを含むことの判定

問題

A列の文字列のなかに、数字(0~9)を含む場合は「○」を表示しなさい。

f:id:waenavi:20201231172927j:plain

 

解説

数字の0を含むことを判定するには、COUNTIF関数を使う方法があります。含む場合は1と表示され、含まない場合は0と表示されます。

  • =COUNTIF(A1,"*0*")

f:id:waenavi:20201231182250j:plain

 

数字の0~9のいずれかを含むかどうかを判定するには足し算をすればよいです。いずれかを含む場合は1以上になり、数字を含まない場合は0となります。

  • =COUNTIF(A1,"*0*") + COUNTIF(A1,"*1*") + ・・・【中略】・・・ + COUNTIF(A1,"*8*") + COUNTIF(A1,"*9*")

数式がものすごく長いので、検索条件を配列定数にします。配列定数「{0,1,2,3,4,5,6,7,8,9}」の前後にアスタリスク「*」を連結します。また、足し算をSUMにします。2種類の場合は2となります。

  • =SUM(COUNTIF(A1,"*"&{0,1,2,3,4,5,6,7,8,9}&"*"))

f:id:waenavi:20201231182342j:plain

 

これが1以上の場合に「○」を表示します。

  • =IF(SUM(COUNTIF(A1,"*"&{0,1,2,3,4,5,6,7,8,9}&"*")),"○","")

f:id:waenavi:20201231182416j:plain

 

別解

数字の0を含むことを判定するのに、FIND関数を使う方法もあります(参考:【Excel】LEN関数は文字数、FIND関数は左から何文字目にあるかを数える関数である)。FIND関数は検索文字列が最初に現れる位置を左端から数え、その位置を番号で返します。見つからなければエラー値「#VALUE!」を返します。含む場合は数値を返し、含まなければエラーを返します。

  • =FIND(0,A1)

f:id:waenavi:20201231182507j:plain

 

COUNT関数はエラー値を無視して数値を数えるので、0~9の数字が見つかったかどうかを判定することができます。見つかった場合は1以上になり、見つからなければ0になります。

  • =COUNT(FIND(0,A1), FIND(1,A1), FIND(2,A1), FIND(3,A1), FIND(4,A1), FIND(5,A1), FIND(6,A1), FIND(7,A1), FIND(8,A1), FIND(9,A1))

数式が長いので、配列定数を使います。

  • =COUNT(FIND({0,1,2,3,4,5,6,7,8,9},A1))

f:id:waenavi:20201231182541j:plain

 

これが1以上の場合に「○」を表示します。

  • =IF(COUNT(FIND({0,1,2,3,4,5,6,7,8,9},A1)),"○","")

f:id:waenavi:20201231182605j:plain

 

<補足>集計関数のうちエラーを無視する関数はCOUNT(COUNTA)だけです。SUM、MAX、MINなどは配列定数の中に1つでもエラー値があるとエラーを返します。

 

(6)SUMPRODUCT関数

問題

セル範囲A1:F1にそれぞれ数字を入力した。左から順に7,6,5,4,3,2を掛けて合計を求めなさい。

f:id:waenavi:20201231182712j:plain

 

解説

足し算と掛け算を使うと次のようになります。

  • =A1*7+B1*6+C1*5+D1*4+E1*3+F1*2

f:id:waenavi:20201231182736j:plain

 

SUMPRODUCTは、掛け算をした値の総合計を求める関数です。セル範囲と配列定数を掛けることもできます。

  • =SUMPRODUCT(A1:F1,{7,6,5,4,3,2})

f:id:waenavi:20201231182801j:plain

 

4.配列定数を用いた検索

配列定数を用いた検索または変換、二次元の配列定数の基本についてはこちらの記事をご覧ください。

 


解説は以上です。


 


Copyright(C)2018-2021 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]
Youtube | 公式サイト | twitter | 著作権 | 運営者情報 | お問い合わせ