9/16 11:23 の回答の件

質問

nishi6さんへ
先日は早速のご回答ありがとうございます。
教えられた通りに9組でテストしてみました。計算式を入力しましたが、Sheet5のA3に「7」、B3に「き」が表示されただけで、4行目以降26行目迄、何も表示されません。計算式の入力でミスがあったのかもと思い、見直しましたが入力ミスはありませんでした。セルのコピーですが、セルA4を下にオートフィルでコピーしていけばいいのですよね？
セルA4に入力した計算式の中の
INDIRECT("Sheet10!C$"&(A3+2)&":$C$26"),の意味を教えてください。
私はリタイア組で、EXCELの初級者です。よろしくお願いします。
第1回目の回答のテストが終わったら、「修正」の方に入っていきます。

回答No.1

nishi6

まず式は、26を189に修正した方を使ってください。
入力するのではなく、回答の式を選択してコピーしてExcelの該当セルに貼り付けます。
再度書くと、私のExcelでは、
A3セルは、
=IFERROR(MATCH(A$1,Sheet10!$C$2:$C$189,0),"")
A4セルは
=IF(A3<25,IFERROR(A3+MATCH(A$1,INDIRECT("Sheet10!C$"&(A3+2)&":$C$189"),0),""),"")
これをコピーして貼り付けてみてください。入力ミスを避けるためです。
A5セル以下はA4セルをコピーします。

INDIRECT("Sheet10!C$"&(A3+2)&":$C$26") の意味ですが、
INDIRECT関数は「参照するセルのアドレスを文字列で指定できる」関数です。
分かりづらい説明ですが、「参照」は関数の中で使います。したがって、関数の中で使ったINDIRECT関数の表す文字列が
関数の中で有効に機能します。関数の一部を「変数」にできるわけです。超強力な表記法で、ものすごく長い算式を短くできることがあります。

この質問の場合、９組の
　（１）１番目をA3で求め、
　（２）２番目をどうやって求めるか
　　　　　（３番目以降はこれの繰り返し）
の（２）が重要な命題です。
これは、（１）のA3で求めた方法と同じ考え方で、Sheet10の１番目を求めた次からの領域で１番目の９組を求めればいいことになります。
結局、前の検索結果を使って、検索領域をINDIRECT関数で計算したセル領域で定義しています。MATCH関数が見つかった最初を返すので、検索領域の最初をずらしているわけです。
算式中の「A3+2」は「A3+1」が最初に見つかった結果A3の値の次のセル、「1」はSheet10の表題行の分の調節です。
後は、見つからなかった時の対応や領域がなくなった場合などを考慮してIFERRORやIFで算式をガードしています。

よく見ると、A4の算式に、A3の算式が検索領域を変更しながら組み込まれているのが（多分）分かるはずです。
上手くいけばいいんですが。