Excel>結合したセルとINDIRECT

質問

お世話になっております。

INDIRECTを使用し、記号(橙）を入力すると自動的に表の数字(緑)が埋まるシートを作っています。
(説明では伝わりにくいと思うので、添付画像をご覧ください。)

シート１はリストです。

シート２に記載される数字(緑)は記号(橙)ごとに分別されています。
実際のシート２は膨大な量があるので、間違い防止と作業効率化のため、記号(橙)を入力すれば自動的に数字(緑)が埋まるようにしたいのです。

当初、「八百屋」と「スーパー」のみの入力だったのでうまくいっていたのですが、
追加で「りんご」「桃」「苺」の入力をすることになってから、エラーが出ています。

式が違うのか、INDIRECTの使い方が違うのか、セルの大きさの違いに問題があるのか、
自分では分かりません。
現状を記載しますので、みなさまの知恵をお貸しください。

よろしくお願い致します。

【現状】

緑枠セル
=IFERROR(INDIRECT(VLOOKUP($B$21,List,2,TRUE)),"")

シート１の青枠範囲を名前定義で「J2」としています。
名前定義に「-」が使用できないので赤枠範囲を名前定義で「List」とし、J-2→J2としています。

ちなみに、シート２は取引先から指定されたものであり、形状や表記を変更する事はできません。
シート２では、J-2と表すしかありません。

目的は、橙枠を記入するだけで緑枠が埋まるようにする事です。
後々取引先にデータで渡すため、VBAは使用できません。

説明不足があればおっしゃってください。
よろしくお願い致します。

回答No.3

tom04

こんばんは！
横からお邪魔します。

オーソドックスにコツコツやってみました。
名前定義せずに関数だけで処理してみてはどうでしょうか？

↓の画像で
Sheet2のB2セルに
=IFERROR(INDEX(Sheet1!B:B,MATCH($A4,Sheet1!$A:$A,0)),"")
という数式を入れ隣のC2セルまでコピー！
B7セルはB2セルの数式をそのままコピー＆ペースト
同様にB12セルにも貼り付けてC列までコピーします。

そして、Sheet2のD2セルに
=IFERROR(INDEX(Sheet1!D:D,MATCH($A4,Sheet1!$A:$A,0)),"")

D3セルに
=IFERROR(INDEX(Sheet1!D:D,MATCH($A4,Sheet1!$A:$A,0)+1),"")

という数式を入れD2・D3セルを範囲指定　→　D3セルのフィルハンドルでG列までコピー！
最後にD2～G3セルをすべてコピー！　→　D7セルに貼り付け　→　D12セルに貼り付け・・・

とすべての行に地道に貼り付けると画像のような感じになります。

※　ご希望の方法でなかったらごめんなさいね。m(_ _)m

回答No.2

kagakusuki

今仮に、シート1において「J-1」、「J-2」、「J-3」等の記号が入力されている列がSheet1のC列であり、御質問欄の添付画像中のシート2において「田中」と入力されているセルがSheet2のB4セルであるものとします。

　まず、Sheet2のC4セルに次の関数を入力して下さい。

=IF($B6="","",IF(ISERROR(1/(INDEX(Sheet1!$E:$J,MATCH($B6,Sheet1!$C:$C,0),COLUMNS($C:C))<>"")),"",INDEX(Sheet1!$E:$J,MATCH($B6,Sheet1!$C:$C,0),COLUMNS($C:C))))

　次に、Sheet2のC5セルに次の関数を入力して下さい。

=IF($B6="","",IF(ISERROR(1/(INDEX(Sheet1!$E:$J,MATCH($B6,Sheet1!$C:$C,0)+1,COLUMNS($C:C))<>"")),"",INDEX(Sheet1!$E:$J,MATCH($B6,Sheet1!$C:$C,0)+1,COLUMNS($C:C))))

　次に、Sheet2のC4～C5の範囲をコピーして、Sheet2のD4～H5の範囲に貼り付けて下さい。
　次に、Sheet2のC4～H5の範囲をコピーして、添付画像において「佐藤」(?)と入力されているセルの右隣のセル(添付画像におけるC19セル)や、「小野」(?)と入力されているセル(添付画像におけるC24セル)の右隣のセルに貼り付けて下さい。

　以上です。

回答No.1

kagakusuki

何をやりたいのかという事に関して、不明な点が幾つかありますので、追加情報を御教え願います。

(1)B列に入力する「J-1」等の記号は、「田中」や「小野」と言った取引先の各1件ごとに、必ず1つの記号しか入れないのでしょうか？
　それとも、例えば「田中」という1件の取引先に対して、「J-1」と「J-3」の2つの記号が同時に入る事もあり得るのでしょうか？

(2)Sheet1のG列に入力される丸で囲まれた数字は、どの記号に対しても必ず(1)と(2)の2つが入っていて、(1)だけとか、(2)だけの場合や、(3)等の別の値が入る事は無いのでしょうか？

(3)もしも、例えば「田中」という1件の取引先に対して、「J-1」と「J-3」の2つの記号が同時に入る事がある場合には、「J-1」と「J-3」の合計値を表示させれば良いのでしょうか？

(4)Sheet1に入力される「J-1」等の記号は、同じものが重複して入力される事は無いと考えても宜しいのでしょうか？

補足

回答ありがとうございます。説明が分かりずらく申し訳ありません。
(1)B列に入力する「J-1」等の記号は、「田中」などの取引先の各1件ごとに、必ず1つだけ入ります。複数はありません。

(2)Sheet1のG列に入力される丸で囲まれた数字は、どの記号に対しても必ず(1)と(2)の2つが入っていますし、変化しません。

(3)１人の名前に対し、「J-2」などの記号も１つですので合計は不要です。

(4)Sheet1に入力される「J-1」等の記号は、Sheet1では重複しません。

よろしくお願いします。