当サイトはWordとExcelの実力を鍛える最高水準の特訓問題集です。リンクを張るのは自由です。【Excel】INDIRECT関数の応用問題(プルダウンリスト、連番の配列、名前を付けたセル)2019-11-02【Excel】INDIRECT関数の応用問題(プルダウンリスト、連番の配列、名前を付けたセル)ExcelExcelのINDIRECT関数は文字列をセル範囲に変換する関数ですが、文字列をセル範囲に変換することだけ理解しても実際にINDIRECT関数を使うことはできません(参考:「INDIRECT関数」完全理解!別シートやセルを参照するメリットと使い方)。INDIRECT関数の第1引数である文字列はセル番地だけでなく、セルやセル範囲に定義した名前も使えます。これによって条件付き書式や入力規則のドロップダウンリストと連携させることができます。別のシートの表を参照することもできるので、VLOOKUP、INDEX、MATCHなどの関数と組み合わせてデータを取得することもできます。そこで、今回は、名前を付けたセルやセル範囲への参照、プルダウンリストの連動、連番の配列を利用する問題など、INDIRECT関数を使った応用事例を出題します。目次0.INDIRECT関数の基本(復習)1.名前を定義したセルを参照する(1)名前の定義、変更、削除(復習)(2)セルの参照(3)セル範囲の参照2.INDEX関数の第1引数として使う(1)別のシートからデータを取得する(2)列の合計を求める3.範囲演算子(半角スペース)(1)セル範囲の共通範囲(2)名前と名前の共通範囲4.入力規則とINDIRECT関数(1)ドロップダウンリストで名前を切り替える(2)リストに並べる項目をINDEX関数で取得する(3)条件付き書式とINDIRECT関数5.ドロップダウンリストを連動させる6.MATCH、VLOOKUP関数との組み合わせ7.INDIRECT関数のオートフィル8.連番の配列(1)整数の総和(2)1~Nの連番を用いた応用問題0.INDIRECT関数の基本(復習)INDIRECT関数の基本的な使い方についてはこちらの記事をご覧ください。ワード&エクセル問題集1.名前を定義したセルを参照する(1)名前の定義、変更、削除(復習)問題セルA5に「もも」という名前を定義しなさい。「もも」をセルA4に変更しなさい。名前「もも」を削除しなさい。解説セルA5を選択します。名前ボックスに「もも」と入力します。Enterキーで確定となり、A5というセル番地の代わりに「もも」が使えるようになります。数式タブの名前の管理をクリックします。定義された名前の一覧を見ることができ、編集することも可能です。編集ボタンを押します。参照範囲をA4にします。これで「もも」はセルA4となります。名前の定義を間違えたときは、名前の管理で修正すればよいです。定義した名前を削除することができます。数式タブの名前の管理をクリックします。「もも」を選択して、削除ボタンで消すことができます。問題セルA5に「もも」という名前を定義して、INDIRECT関数で参照しなさい。解説もう一度、セルA5に対して「もも」を定義します。「=A5」と参照しても良いですが、その代わりに「=もも」と参照しても良いです。INDIRECT関数を用いる場合はダブルクォーテーションが必要です。「=INDIRECT("もも")」です。問題セル範囲A3:A7に「数量」という名前を定義して、INDIRECT関数で合計を求めなさい。「数量」という文字を入力した場合にそれを利用して合計を求めなさい。解説セル範囲A3:A7を選択します。「数量」という名前を定義します。合計は「=SUM(A3:A7)」と求めても良いですが、「=SUM(数量)」としてもよいです。INDIRECT関数を用いる場合はダブルクォーテーションが必要です。「=SUM(INDIRECT("数量"))」です。セルB1に「数量」と入力したとします。セルまたはセル範囲に定義した名前を、別のセルに入力した時はその文字列をINDIRECT関数で使用することができます。「=SUM(INDIRECT(B1))」となります。2.INDEX関数の第1引数として使う(1)別のシートからデータを取得する問題2つのシート「Sheet1」「Sheet2」がある。支店名を上から順に取得しなさい。解説Sheet1のセル範囲B4:C8を選択して「東日本」という名前を付けます。セル範囲B10:C13に「西日本」という名前を付けます。これらはセル範囲に定義した名前です。INDIRECT関数を使います。支店名を取得するのはINDEX関数です。INDEX関数で検索する範囲はINDIRECT("東日本")、行番号はA列にある数字です。=INDEX(INDIRECT(B$3),$A4,1)これで東日本、西日本で定義した範囲の1列目を取得することができます。空白のときにエラーになるので、IFまたはIFERROR関数で空白にします。=IFERROR(INDEX(INDIRECT(B$3),$A4,1),"")セルに名前を入力し、それをINDIRECTでセル範囲に変換することによって、シート名を入力したり、シングルクォーテーションなどを気にしたりすることなく参照することができます。売上金額の合計を求めなさい。名前を定義しているセル範囲を合計すればよいです。うち1列目の支店名は文字列なので合計には関係なく、2列目だけの合計になります。別解2列目だけを指定するにはINDEX関数を使い、行番号を0、列番号を2にします。問題セル範囲F3:F7とセル範囲C7:F7の共通範囲であるセルを参照しなさい。「=F3:F7」と入力します。半角スペースを入れて、C7:F7を選択します。これで共通範囲であるF7の値を求めることができます。この半角スペースのことを「範囲演算子」といいます。2つの範囲の共通部分を求める演算子で、その共通範囲が1つのセルであればその値が返ります。これをINDIRECT関数で囲むとエラーです。範囲演算子は範囲同士の演算をする(文字列ではない)のでINDIRECT関数の外に出さなければなりません。INDIRECT()」という感じでINDIRECTを2つにします。問題セル範囲F3:F7に「名古屋」、セル範囲C7:F7に「合計」という名前を定義して、共通範囲であるセルを参照しなさい。解説セル範囲F3:F7に「名古屋」という名前を付けます。セル範囲C7:F7に「合計」という名前をつけます。名前も範囲演算子が使えます。合計」で名古屋の合計になります。この場合もINDIRECTは2つになります。INDIRECT関数であれば、これらのセルを参照して求めることができます。INDIRECT(B1)東京にするとエラーになります。「東京」という名前は無いからです。セル範囲D3:D7を「東京」という名前にします。これで東京の合計が求められます。4.入力規則とINDIRECT関数(1)ドロップダウンリストで名前を切り替える問題2つのシート「Sheet1」「Sheet2」がある。Sheet1のセル範囲B4:C8に「東日本」、セル範囲B10:C13に「西日本」という名前をそれぞれ定義した。Sheet2のセルA4に入力規則(リスト)を設定して、ドロップダウンリストで東日本と西日本が選べるようにしなさい。売上金額の合計を求めなさい。解説セルA4を選択します。データタブ、データの入力規則をクリックします。入力値の種類を「リスト」にします。ここで、「ドロップダウンリストから選択する」にチェックが入っていることを確認します。元の値を「東日本,西日本」とします。東日本と西日本の間に半角のカンマが必要です。セル範囲の名前であり、SUMで合計を求められます。=SUM(INDIRECT(A4))=SUM(INDEX(INDIRECT(A4),0,2))(2)リストに並べる項目をINDEX関数で取得する問題Sheet2のセルA6に入力規則(リスト)を設定して、ドロップダウンリストで西日本の支店名を選べるようにしなさい。西日本の支店名は「西日本」の1列目であり、INDEX関数で「INDEX(西日本,0,1)」となります。セルA6を選択します。データタブ、データの入力規則をクリックします。入力値の種類を「リスト」にします。元の値を「=INDEX(西日本,0,1)」とします。これで西日本の支店を選択することができます。問題セルA6の入力規則を変更して、セルA4で選んだエリアの支店名を選べるようにしなさい。条件付き書式を設定して、セルA4で選んだエリアでない支店名の場合に赤色の文字にしなさい。解説セルA4は東日本と西日本を選択できるように入力設定したセルです。これを用いる場合はINDIRECT関数を使います。セルA6を選択します。データタブ、データの入力規則をクリックします。元の値を「=INDEX(西日本,0,1)」となっているのを変更して、「=INDEX(INDIRECT(A4),0,1)」とします。セルA4が東日本であれば東日本の支店を選択することができます。セルA4を西日本に変えると西日本の支店を選択することができます。ここで大阪支店を選んだあとで、セルA4を東日本に切り替えても、大阪支店は残ったままなので注意が必要です。この場合はエラーを表示するなどの工夫が必要です。セルA6に条件付き書式を設定する方法が考えられます。セルA6の入力規則のリストは「=INDEX(INDIRECT(A4),0,1)」でしたが、そのリストに存在しない支店であればCOUNTIFが0になるはずです。=COUNTIF(INDEX(INDIRECT(A4),0,1),A6)=0これで赤色になります。5.ドロップダウンリストを連動させる問題2つのシート「Sheet1」「Sheet2」がある。選べるようにしなさい。Sheet2のセル範囲B4:B13に入力規則(リスト)を設定して、セル範囲A4:A13(左隣)の分類に属する費用の項目を選べるようにしなさい。入力規則でリストにするものが分かっている場合は名前を付けたほうが良いです。Sheet1のセル範囲B3:D3に「分類」という名前を付けたとします。Sheet2のセル範囲A4:A13を選択します。データタブ、データの入力規則をクリックします。入力値の種類を「リスト」にします。元の値を「=分類」とします。これで分類を選択することができます。B4:B6に「光熱費」、C4:C9に「通信費」、D4:D9に「生活雑貨」という名前を付けます。3行目の分類名をそのままセル範囲の名前にします。Sheet2のセル範囲B4:B13を選択します。データタブ、データの入力規則をクリックします。入力値の種類を「リスト」にします。元の値を「=INDIRECT(A4)」(相対参照であることに注意!)とします。これで、A列の分類を変えると対応する項目を選択することができます。別解1列ずつ名前を付けるのが面倒な場合はB4:D9に一括で名前を付けます。A2:C7に「費用」という名前を付けたとします。この場合、B列の入力規則は「=OFFSET(費用,0,0,COUNTA(INDEX(費用,0,MATCH(A4,分類,0))),1)」と長い数式になります。INDIRECTは不要です。6.MATCH、VLOOKUP関数との組み合わせ問題シート「東日本」と「西日本」にそれぞれ表を入力した。(1)各シートのB列の合計を求めなさい。(2)3行目の項目のなかで「電話番号」が左から何列目にあるかを求めなさい。(3)東日本仙台支店と西日本福岡支店の売上金額を求めなさい。解説シート名がA列に入力されていることから、INDIRECT関数が使えます。セルにシート名を入力し、それをINDIRECTでセル範囲に変換することによって、異なるシートのデータを参照することができます。シート名は念のためシングルクォーテーションで囲んで、「"'"&A2&"'"」とします(参考:「INDIRECT関数」完全理解!別シートやセルを参照するメリットと使い方)。SUMのなかにINDIRECTを入れて「=SUM(INDIRECT("'"&A2&"'!B:B"))」とします。B列の参照はB:Bです。2つのシートは表の形が異なり、東日本の電話番号は左から5列目、西日本の電話番号は左から3列目です。この場所を数字で返す関数はMATCH関数です。検索値はC$1、検索する範囲はINDIRECTで、3行目の参照は3:3です。=MATCH(C$1,INDIRECT("'"&$A2&"'!3:3"),0)左から何列目かが分かるということはINDEX関数やVLOOKUP関数を使えば、その列のデータを取得することも可能であるということです。シート名と支店名があります。これを用いて売上金額を求めるにはVLOOKUP関数を使います。検索値はB6、検索する範囲はINDIRECTで、参照はA:Bです。A$4:B$10等でもよいです。=VLOOKUP(B6,INDIRECT("'"&A6&"'!A:B"),2,0)7.INDIRECT関数のオートフィル問題シート「東日本」と「西日本」にそれぞれ表を入力した。セルB1に入力したシートのセルA4~B8のデータを取得しなさい。解説セルA1にシート名が入力されているのでINDIRECT関数が使えます。セルA4を参照します。=INDIRECT("'"&$B$1&"'!A4")これをオートフィルします。すべてA4になります。INDIRECTは参照先を絶対に変えない参照方法なので当然です。オートフィルをして文字列を変えるためにはADDRESS(ROW,COLUMN)を使います。ADDRESSはセル参照を文字列に変える関数です。=INDIRECT("'"&$B$1&"'!"&ADDRESS(ROW(A4),COLUMN(A4)))これで相対参照ができます。セルA4を参照しているため4行目を削除するとエラーになることに注意しなければなりません。別解OFFSET関数を使う方法もあります。4行目を削除してもエラーにはなりません。問題SUMPRODUCT関数とROW関数を用いて、1~100の整数の総和を求めなさい。1~100の整数のうち3でも5でも割り切れない数の総和を求めなさい。解説ROW関数の引数がセル範囲で行番号が複数ある場合、縦方向の配列になります。ROW(1:100)やROW(A1:A100)などとすると、{1;2;3;4;・・・;100}という縦方向の配列(縦ベクトル)となります。SUMPRODUCT関数は引数が1つの配列しかない場合、配列の中身の総和になります。したがって、SUMPRODUCTのなかに、ROW(1:100)を入れると1から100までの総和となります。=SUMPRODUCT(ROW(1:100))ところが、オートフィルをすると答えが変わってしまいます。相対参照で数式が変わるからです。3行目を削除すると、4950になります。数式が「=SUMPRODUCT(1:99)」になってしまいます。相対参照や絶対参照のような参照方法の場合、参照先に変動があった場合、その影響を受けます。そこでINDIRECT関数を使います。オートフィルや行削除をしても変わりません。=SUMPRODUCT(ROW(INDIRECT("1:100")))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))INDIRECT関数を使うと次のようになります。問題セルA1とA2にそれぞれ文字列を入力した。セルA1の文字列の中に、セルA2の文字列が何個含まれているかを求めなさい。LEN(A1)>LEN(A2)>0とする。「BCBCBCB」のなかに「BCB」が3個含まれているものとする。解説セルA1の文字列の先頭から順に、セルA2の文字列と比較します。MID関数を使います。取得する文字数はLEN(A2)です。=MID(A1,開始番号,LEN(A2))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))SUMPRODUCTでこの合計を求めたら一致する回数、つまりA2が含まれる回数が求められます。したがって次のような式になります。=SUMPRODUCT((MID(A1,ROW(INDIRECT("1:"&LEN(A1)-LEN(A2)+1)),LEN(A2))=A2)*1)解説は以上です。わえなびチャンネルhttp://www.youtube.com/c/waenavijpチャンネル登録をすると最新の動画をチェックできます。初心者向けの動画も大量にあります。平成三十年八月]スターをつけました引用をストックしましたストック一覧を見る閉じる引用するにはまずログインしてくださいログイン閉じる引用をストックできませんでした。再度お試しください閉じる限定公開記事のため引用できません。