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

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

【Excel】INDIRECT関数の応用問題(プルダウンリスト、連番の配列、名前を付けたセル)

ExcelのINDIRECT関数は文字列をセル範囲に変換する関数ですが、文字列をセル範囲に変換することだけ理解しても実際にINDIRECT関数を使うことはできません(参考:「INDIRECT関数」完全理解!別シートやセルを参照するメリットと使い方)。

INDIRECT関数の第1引数である文字列はセル番地だけでなく、セルやセル範囲に定義した名前も使えます。これによって条件付き書式や入力規則のドロップダウンリストと連携させることができます。また、別のシートの表を参照することもできるので、VLOOKUP、INDEX、MATCHなどの関数と組み合わせてデータを取得することもできます。

そこで、今回は、名前を付けたセルやセル範囲への参照、プルダウンリストの連動、連番の配列を利用する問題など、INDIRECT関数を使った応用事例を出題します。

f:id:waenavi:20191102091807j:plain

目次

0.INDIRECT関数の基本(復習)

INDIRECT関数の基本的な使い方についてはこちらの記事をご覧ください。

なお、この記事の内容を理解するにはINDIRECTのほか、ROW、COLUMN、VLOOKUP、INDEX、MATCH、SUMPRODUCT、条件付き書式、入力規則の知識を必要とします。

 

1.名前を定義したセルを参照する

(1)名前の定義、変更、削除(復習)

問題

  1. セルA5に「もも」という名前を定義しなさい。
  2. 「もも」をセルA4に変更しなさい。
  3. 名前「もも」を削除しなさい。

f:id:waenavi:20191101210631j:plain

 

解説

セルA5を選択します。名前ボックスに「もも」と入力します。

f:id:waenavi:20191101210730j:plain

 

Enterキーで確定となり、A5というセル番地の代わりに「もも」が使えるようになります。

f:id:waenavi:20191101210906j:plain

 

数式タブの名前の管理をクリックします。

f:id:waenavi:20191101211558j:plain

 

定義された名前の一覧を見ることができ、編集することも可能です。編集ボタンを押します。

f:id:waenavi:20191101212128j:plain

f:id:waenavi:20191101212205j:plain

 

参照範囲をA4にします。

f:id:waenavi:20191101212245j:plain

 

これで「もも」はセルA4となります。名前の定義を間違えたときは、名前の管理で修正すればよいです。

f:id:waenavi:20191101212335j:plain

 

定義した名前を削除することができます。数式タブの名前の管理をクリックします。

f:id:waenavi:20191101211558j:plain

 

「もも」を選択して、削除ボタンで消すことができます。

f:id:waenavi:20191101212445j:plain

f:id:waenavi:20191101212538j:plain

 

(2)セルの参照

問題

セルA5に「もも」という名前を定義して、INDIRECT関数で参照しなさい。

f:id:waenavi:20191101212734j:plain

 

解説

もう一度、セルA5に対して「もも」を定義します。「=A5」と参照しても良いですが、その代わりに「=もも」と参照しても良いです。

f:id:waenavi:20191101212812j:plain

 

30となります。

f:id:waenavi:20191101212842j:plain

 

INDIRECT関数を用いる場合はダブルクォーテーションが必要です。「=INDIRECT("もも")」です。

f:id:waenavi:20191101212923j:plain

 

(3)セル範囲の参照

問題

セル範囲A3:A7に「数量」という名前を定義して、INDIRECT関数で合計を求めなさい。また、「数量」という文字を入力した場合にそれを利用して合計を求めなさい。

f:id:waenavi:20191101213013j:plain

 

解説

セル範囲A3:A7を選択します。

f:id:waenavi:20191101213041j:plain

 

「数量」という名前を定義します。

f:id:waenavi:20191101213112j:plain

 

合計は「=SUM(A3:A7)」と求めても良いですが、「=SUM(数量)」としてもよいです。

f:id:waenavi:20191101213138j:plain

 

INDIRECT関数を用いる場合はダブルクォーテーションが必要です。「=SUM(INDIRECT("数量"))」です。

f:id:waenavi:20191101213203j:plain

 

セルB1に「数量」と入力したとします。セルまたはセル範囲に定義した名前を、別のセルに入力した時はその文字列をINDIRECT関数で使用することができます。
「=SUM(INDIRECT(B1))」となります。

f:id:waenavi:20191101213247j:plain

 

2.INDEX関数の第1引数として使う

(1)別のシートからデータを取得する

問題

2つのシート「Sheet1」「Sheet2」がある。Sheet1のセル範囲B4:C8に「東日本」、セル範囲B10:C13に「西日本」という名前をそれぞれ定義し、「東日本」、「西日本」の支店名を上から順に取得しなさい。

f:id:waenavi:20191101223922j:plain

f:id:waenavi:20191101224000j:plain

 

解説

Sheet1のセル範囲B4:C8を選択して「東日本」という名前を付けます。

f:id:waenavi:20191101224050j:plain

 

セル範囲B10:C13に「西日本」という名前を付けます。

f:id:waenavi:20191101224121j:plain

 

Sheet2の表には「東日本」「西日本」と入力されています。これらはセル範囲に定義した名前です。INDIRECT関数を使います。また、支店名を取得するのはINDEX関数です。INDEX関数で検索する範囲はINDIRECT("東日本")、行番号はA列にある数字です。複合参照です。

  • =INDEX(INDIRECT(B$3),$A4,1)

f:id:waenavi:20191101224322j:plain

 

これで東日本、西日本で定義した範囲の1列目を取得することができます。

f:id:waenavi:20191101224409j:plain

 

なお、空白のときにエラーになるので、IFまたはIFERROR関数で空白にします。

  • =IFERROR(INDEX(INDIRECT(B$3),$A4,1),"")

f:id:waenavi:20191101224506j:plain

 

セルに名前を入力し、それをINDIRECTでセル範囲に変換することによって、シート名を入力したり、シングルクォーテーションなどを気にしたりすることなく参照することができます。

 

(2)列の合計を求める

問題

「東日本」、「西日本」の売上金額の合計を求めなさい。

f:id:waenavi:20191101224557j:plain

 

解説

名前を定義しているセル範囲を合計すればよいです。

  • =SUM(INDIRECT(B3))

f:id:waenavi:20191101224647j:plain

 

「東日本」「西日本」のうち1列目の支店名は文字列なので合計には関係なく、2列目だけの合計になります。

f:id:waenavi:20191101225017j:plain

 

別解

2列目だけを指定するにはINDEX関数を使い、行番号を0、列番号を2にします。

  • =SUM(INDEX(INDIRECT(B3),0,2))

f:id:waenavi:20191101225051j:plain

 

3.範囲演算子(半角スペース)

(1)セル範囲の共通範囲

問題

セル範囲F3:F7とセル範囲C7:F7の共通範囲であるセルを参照しなさい。

f:id:waenavi:20191102092244j:plain

 

解説

「=F3:F7」と入力します。

f:id:waenavi:20191102092341j:plain

 

半角スペースを入れて、C7:F7を選択します。これで共通範囲であるF7の値を求めることができます。

f:id:waenavi:20191102092429j:plain

 

この半角スペースのことを「範囲演算子」といいます。2つの範囲の共通部分を求める演算子で、その共通範囲が1つのセルであればその値が返ります。

f:id:waenavi:20191102092525j:plain

 

これをINDIRECT関数で囲むとエラーです。

f:id:waenavi:20191102092651j:plain

 

範囲演算子は範囲同士の演算をする(文字列ではない)のでINDIRECT関数の外に出さなければなりません。「INDIRECT() INDIRECT()」という感じでINDIRECTを2つにします。

  • =INDIRECT("F3:F7") INDIRECT("C7:F7")

f:id:waenavi:20191102092736j:plain

 

(2)名前と名前の共通範囲

問題

セル範囲F3:F7に「名古屋」、セル範囲C7:F7に「合計」という名前を定義して、共通範囲であるセルを参照しなさい。

f:id:waenavi:20191102091303j:plain

 

解説

セル範囲F3:F7に「名古屋」という名前を付けます。

f:id:waenavi:20191102091348j:plain

 

また、セル範囲C7:F7に「合計」という名前をつけます。

f:id:waenavi:20191102091423j:plain

 

名前も範囲演算子が使えます。「=名古屋 合計」で名古屋の合計になります。

f:id:waenavi:20191102091511j:plain

f:id:waenavi:20191102091513j:plain

 

この場合もINDIRECTは2つになります。

  • =INDIRECT("名古屋") INDIRECT("合計")

f:id:waenavi:20191102091558j:plain

 

「名古屋」「合計」と入力したとします。

f:id:waenavi:20191102091638j:plain

 

INDIRECT関数であれば、これらのセルを参照して求めることができます。

  • =INDIRECT(A1) INDIRECT(B1)

f:id:waenavi:20191102091807j:plain

 

東京にするとエラーになります。「東京」という名前は無いからです。

f:id:waenavi:20191102091833j:plain

 

セル範囲D3:D7を「東京」という名前にします。これで東京の合計が求められます。

f:id:waenavi:20191102092133j:plain

 

4.入力規則とINDIRECT関数

(1)ドロップダウンリストで名前を切り替える

問題

2つのシート「Sheet1」「Sheet2」がある。Sheet1のセル範囲B4:C8に「東日本」、セル範囲B10:C13に「西日本」という名前をそれぞれ定義した。

f:id:waenavi:20191102093255j:plain

 

Sheet2のセルA4に入力規則(リスト)を設定して、ドロップダウンリストで東日本と西日本が選べるようにしなさい。また、「東日本」、「西日本」の売上金額の合計を求めなさい。

f:id:waenavi:20191102093447j:plain

 

解説

セルA4を選択します。

f:id:waenavi:20191102093708j:plain

 

データタブ、データの入力規則をクリックします。

f:id:waenavi:20191102122701j:plain

 

入力値の種類を「リスト」にします。ここで、「ドロップダウンリストから選択する」にチェックが入っていることを確認します。

f:id:waenavi:20191102122806j:plain

 

元の値を「東日本,西日本」とします。なお、東日本と西日本の間に半角のカンマが必要です。

f:id:waenavi:20191102122849j:plain

 

これで東日本と西日本を選択できるようになります。

f:id:waenavi:20191102122917j:plain

 

「東日本」「西日本」はセル範囲の名前であり、SUMで合計を求められます。

  • =SUM(INDIRECT(A4))
  • =SUM(INDEX(INDIRECT(A4),0,2))

f:id:waenavi:20191102123024j:plain

f:id:waenavi:20191102123027j:plain

 

(2)リストに並べる項目をINDEX関数で取得する

問題

Sheet2のセルA6に入力規則(リスト)を設定して、ドロップダウンリストで西日本の支店名を選べるようにしなさい。

f:id:waenavi:20191102124138j:plain

 

解説

西日本の支店名は「西日本」の1列目であり、INDEX関数で「INDEX(西日本,0,1)」となります。

f:id:waenavi:20191102124325j:plain

 

セルA6を選択します。

f:id:waenavi:20191102124706j:plain

 

データタブ、データの入力規則をクリックします。

f:id:waenavi:20191102122701j:plain

 

入力値の種類を「リスト」にします。元の値を「=INDEX(西日本,0,1)」とします。

f:id:waenavi:20191102124807j:plain

 

これで西日本の支店を選択することができます。

f:id:waenavi:20191102124832j:plain

 

(3)条件付き書式とINDIRECT関数

問題

セルA6の入力規則を変更して、セルA4で選んだエリアの支店名を選べるようにしなさい。

f:id:waenavi:20191102125515j:plain

 

また、条件付き書式を設定して、セルA4で選んだエリアでない支店名の場合に赤色の文字にしなさい。

f:id:waenavi:20191102125511j:plain

 

解説

セルA4は東日本と西日本を選択できるように入力設定したセルです。これを用いる場合はINDIRECT関数を使います。

f:id:waenavi:20191102124908j:plain

 

セルA6を選択します。

f:id:waenavi:20191102124930j:plain

 

データタブ、データの入力規則をクリックします。

f:id:waenavi:20191102122701j:plain

 

元の値を「=INDEX(西日本,0,1)」となっているのを変更して、「=INDEX(INDIRECT(A4),0,1)」とします。

f:id:waenavi:20191102125057j:plain

 

セルA4が東日本であれば東日本の支店を選択することができます。

f:id:waenavi:20191102125511j:plain

 

セルA4を西日本に変えると西日本の支店を選択することができます。

f:id:waenavi:20191102125206j:plain

 

ただし、ここで大阪支店を選んだあとで、セルA4を東日本に切り替えても、大阪支店は残ったままなので注意が必要です。

f:id:waenavi:20191102125256j:plain

 

この場合はエラーを表示するなどの工夫が必要です。例えば、セルA6に条件付き書式を設定する方法が考えられます。セルA6の入力規則のリストは「=INDEX(INDIRECT(A4),0,1)」でしたが、そのリストに存在しない支店であればCOUNTIFが0になるはずです。

  • =COUNTIF(INDEX(INDIRECT(A4),0,1),A6)=0

f:id:waenavi:20191102125509j:plain

 

これで赤色になります。

f:id:waenavi:20191102125511j:plain

 

5.ドロップダウンリストを連動させる

問題

2つのシート「Sheet1」「Sheet2」がある。

f:id:waenavi:20191102131333j:plain

f:id:waenavi:20191102131541j:plain

 

Sheet2のセル範囲A4:A13に入力規則(リスト)を設定して、ドロップダウンリストで光熱費、通信費、生活雑貨を選べるようにしなさい。

f:id:waenavi:20191102131751j:plain

 

また、Sheet2のセル範囲B4:B13に入力規則(リスト)を設定して、セル範囲A4:A13(左隣)の分類に属する費用の項目を選べるようにしなさい。

f:id:waenavi:20191102131516j:plain

 

解説

入力規則でリストにするものが分かっている場合は名前を付けたほうが良いです。Sheet1のセル範囲B3:D3に「分類」という名前を付けたとします。

f:id:waenavi:20191102131915j:plain

 

Sheet2のセル範囲A4:A13を選択します。

f:id:waenavi:20191102132238j:plain

 

データタブ、データの入力規則をクリックします。

f:id:waenavi:20191102122701j:plain

 

入力値の種類を「リスト」にします。元の値を「=分類」とします。

f:id:waenavi:20191102132403j:plain

 

これで分類を選択することができます。

f:id:waenavi:20191102133024j:plain

 

B4:B6に「光熱費」、C4:C9に「通信費」、D4:D9に「生活雑貨」という名前を付けます。つまり、3行目の分類名をそのままセル範囲の名前にします。

f:id:waenavi:20191102133315j:plain

 

Sheet2のセル範囲B4:B13を選択します。

f:id:waenavi:20191102133428j:plain

 

データタブ、データの入力規則をクリックします。

f:id:waenavi:20191102122701j:plain

 

入力値の種類を「リスト」にします。元の値を「=INDIRECT(A4)」(相対参照であることに注意!)とします。

f:id:waenavi:20191102133550j:plain

 

これで、A列の分類を変えると対応する項目を選択することができます。

f:id:waenavi:20191102133634j:plain

 

別解

1列ずつ名前を付けるのが面倒な場合はB4:D9に一括で名前を付けます。A2:C7に「費用」という名前を付けたとします。

f:id:waenavi:20191102133720j:plain

 

この場合、B列の入力規則は「=OFFSET(費用,0,0,COUNTA(INDEX(費用,0,MATCH(A4,分類,0))),1)」と長い数式になります。INDIRECTは不要です。

f:id:waenavi:20191102133828j:plain

 

6.MATCH、VLOOKUP関数との組み合わせ

問題

シート「東日本」と「西日本」にそれぞれ表を入力した。

f:id:waenavi:20191102135819j:plain

f:id:waenavi:20191102135823j:plain

 

(1)各シートのB列の合計を求めなさい。
(2)3行目の項目のなかで「電話番号」が左から何列目にあるかを求めなさい。
(3)東日本仙台支店と西日本福岡支店の売上金額を求めなさい。

f:id:waenavi:20191102140009j:plain

 

解説

シート名がA列に入力されていることから、INDIRECT関数が使えます。セルにシート名を入力し、それをINDIRECTでセル範囲に変換することによって、異なるシートのデータを参照することができます。なお、シート名は念のためシングルクォーテーションで囲んで、「"'"&A2&"'"」とします(参考:「INDIRECT関数」完全理解!別シートやセルを参照するメリットと使い方)。

SUMのなかにINDIRECTを入れて「=SUM(INDIRECT("'"&A2&"'!B:B"))」とします。B列の参照はB:Bです。

f:id:waenavi:20191102140218j:plain

 

2つのシートは表の形が異なり、東日本の電話番号は左から5列目、西日本の電話番号は左から3列目です。この場所を数字で返す関数はMATCH関数です。

f:id:waenavi:20191102140422j:plain

f:id:waenavi:20191102140425j:plain

 

検索値はC$1、検索する範囲はINDIRECTで、3行目の参照は3:3です。複合参照です。

  • =MATCH(C$1,INDIRECT("'"&$A2&"'!3:3"),0)

f:id:waenavi:20191102140548j:plain

 

左から何列目かが分かるということはINDEX関数やVLOOKUP関数を使えば、その列のデータを取得することも可能であるということです。

シート名と支店名があります。これを用いて売上金額を求めるにはVLOOKUP関数を使います。検索値はB6、検索する範囲はINDIRECTで、参照はA:Bです。A$4:B$10等でもよいです。

  • =VLOOKUP(B6,INDIRECT("'"&A6&"'!A:B"),2,0)

f:id:waenavi:20191102140715j:plain

 

7.INDIRECT関数のオートフィル

問題

シート「東日本」と「西日本」にそれぞれ表を入力した。

f:id:waenavi:20191102135819j:plain

f:id:waenavi:20191102135823j:plain

 

セルB1に入力したシートのセルA4~B8のデータを取得しなさい。

f:id:waenavi:20191102140915j:plain

 

解説

セルA1にシート名が入力されているのでINDIRECT関数が使えます。とりあえずセルA4を参照します。

  • =INDIRECT("'"&$B$1&"'!A4")

f:id:waenavi:20191102141552j:plain

 

これをオートフィルします。すべてA4になります。INDIRECTは参照先を絶対に変えない参照方法なので当然です。

f:id:waenavi:20191102141705j:plain

 

オートフィルをして文字列を変えるためにはADDRESS(ROW,COLUMN)を使います。ADDRESSはセル参照を文字列に変える関数です。

  • =INDIRECT("'"&$B$1&"'!"&ADDRESS(ROW(A4),COLUMN(A4)))

f:id:waenavi:20191102142128j:plain

 

これで相対参照ができます。ただし、セルA4を参照しているため4行目を削除するとエラーになることに注意しなければなりません。

f:id:waenavi:20191102142227j:plain

 

別解

OFFSET関数を使う方法もあります。4行目を削除してもエラーにはなりません。

  • =OFFSET(INDIRECT("'"&$B$1&"'!A4"),ROW()-ROW($A$3),COLUMN()-COLUMN($A$3))

f:id:waenavi:20191102142308j:plain

 

8.連番の配列

(1)整数の総和

問題

SUMPRODUCT関数とROW関数を用いて、1~100の整数の総和を求めなさい。また、1~100の整数のうち3でも5でも割り切れない数の総和を求めなさい。

解説

ROW関数の引数がセル範囲で行番号が複数ある場合、縦方向の配列になります。例えば、ROW(1:100)やROW(A1:A100)などとすると、{1;2;3;4;・・・;100}という縦方向の配列(縦ベクトル)となります。

f:id:waenavi:20191102142758j:plain

 

SUMPRODUCT関数は引数が1つの配列しかない場合、配列の中身の総和になります。したがって、SUMPRODUCTのなかに、ROW(1:100)を入れると1から100までの総和となります。5050です。

  • =SUMPRODUCT(ROW(1:100))

f:id:waenavi:20191102143405j:plain

f:id:waenavi:20191102143421j:plain

 

ところが、オートフィルをすると答えが変わってしまいます。相対参照で数式が変わるからです。

f:id:waenavi:20191102143459j:plain

 

また、3行目を削除すると、4950になります。

f:id:waenavi:20191102143528j:plain

 

数式が「=SUMPRODUCT(1:99)」になってしまいます。相対参照や絶対参照のような参照方法の場合、参照先に変動があった場合、その影響を受けます。

f:id:waenavi:20191102143559j:plain

 

そこでINDIRECT関数を使います。オートフィルや行削除をしても変わりません。

  • =SUMPRODUCT(ROW(INDIRECT("1:100")))

f:id:waenavi:20191102143642j:plain

 

3で割り切れない数はMOD関数で「mod(X,3)>0」です。また、5で割り切れない数は「mod(X,5)>0」です。掛け算をすればAND条件(論理積)となり、2632となります。

  • =SUMPRODUCT(ROW(1:100)*(mod(ROW(1:100),3)>0)*(mod(ROW(1:100),5)>0))

f:id:waenavi:20191102143836j:plain

 

INDIRECT関数を使うと次のようになります。

  • =SUMPRODUCT(ROW(INDIRECT("1:100"))*(mod(ROW(INDIRECT("1:100")),3)>0)*(mod(ROW(INDIRECT("1:100")),5)>0))

 

(2)1~Nの連番を用いた応用問題

問題

セルA1とA2にそれぞれ文字列を入力した。セルA1の文字列の中に、セルA2の文字列が何個含まれているかを求めなさい。ただし、LEN(A1)>LEN(A2)>0とする。

f:id:waenavi:20191102144737j:plain

 

たとえば、「BCBCBCB」のなかに「BCB」が3個含まれているものとする。

f:id:waenavi:20191102223738j:plain

 

解説

セルA1の文字列の先頭から順に、セルA2の文字列と比較します。MID関数を使います。取得する文字数はLEN(A2)です。

  • =MID(A1,開始番号,LEN(A2))

f:id:waenavi:20191102145441j:plain

 

MIDで取り出したものと、A2が一致すれば1、不一致ならば0とします。

  • =(MID(A1,開始番号,LEN(A2))=A2)*1
  • =EXACT(MID(A1,開始番号,LEN(A2)),A2)*1

開始番号は1からLEN(A1)-LEN(A2)+1までです。ROW関数とINDIRECT関数で連番の配列を作ります。

  • ROW(INDIRECT("1:"&LEN(A1)-LEN(A2)+1))

f:id:waenavi:20191102150907j:plain

 

SUMPRODUCTでこの合計を求めたら一致する回数、つまりA2が含まれる回数が求められます。したがって次のような式になります。

  • =SUMPRODUCT((MID(A1,ROW(INDIRECT("1:"&LEN(A1)-LEN(A2)+1)),LEN(A2))=A2)*1)

f:id:waenavi:20191102151112j:plain

 


解説は以上です。


 


Copyright(C)2018-2019 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]