Excelの使い方で相談させて下さい。下記のような仕様で、本・支社を選

質問

Excelの使い方で相談させて下さい。下記のような仕様で、本・支社を選ぶことでリストボックスに表示される作業項目と料金をINDIRECT関数で連動させたいと思っています。

<本・支社区分>　<作業項目>　　<料金>
東京本社 東京AAA　　　　1000
　　　　東京BBB　　　　2000
　　　　東京CCC　　　　3000
　　　　東京DDD　　　　4000
　　　　東京EEE　　　　5000
名古屋支社 名古屋AAA　　　1500
　　　　名古屋BBB　　　2500
　　　　名古屋CCC　　　3500
　　　　名古屋DDD　　　4500
　　　　名古屋EEE　　　5500
大阪支社 大阪AAA　　　　10000
　　　　大阪BBB　　　　20000
　　　　大阪CCC　　　　30000
　　　　大阪DDD　　　　40000
　　　　大阪EEE　　　　50000
例えば、A1セルに本・支社の選択ボックスを設け、A2に作業項目、A3に料金が表示されるようなシートで考えると、INDIRECT関数をどのように使ったらいいのかが、いまいちよく分かっていません。名前を定義するという方法も見つけたのですが、その使い方も分かっていません。唐突に質問させて頂いて恐縮ですが、どなたかご教授下さい。宜しくお願い致します。

回答No.1

kmetu · ベストアンサー

こちらを参考にしてください

ワークシート上でリスト入力
[元の値]を動的に変化させる
http://officetanaka.net/excel/function/tips/tips05.htm

東京AAA～東京EEE
のところを東京本社という名前にして、リストのい元の値を
=INDIRECT($A$1)
にするという感じですね。

お礼

この度はどうもありがとうございます。参考サイトも見させて頂きました。
今後のExcel活用の参考にさせて頂きたいと思います。ありがとうございました。

回答No.7

MackyNo1

参考までに、

提示した名前定義の数式は、Sheet1に一覧データがあり、Sheet1のセルに名前を定義したのですべてのセル参照に「Sheet1!」がついていますが、別シートで設定する場合はそのシートで名前定義を開始して「Sheet1!E2」の部分を「E2」のようにしてください（単純なセル参照で自動的にシート名が取得されます）。

たとえば、別シートのA列に本支社名が入力されていて、B列にリストを表示するなら、B2セルを選択して「A2」と入力することになります。

=OFFSET(Sheet1!$A$1,MATCH(A2,Sheet1!A:A,0)-1,1,MIN(INDEX((OFFSET(Sheet1!$A$1,MATCH(A2,Sheet1!A:A,0),0,100,1)="")*100+ROW(Sheet1!$A$1:$A$100),)),1)

お礼

この度はどうもありがとうございます。
今後のExcel活用の参考にさせて頂きたいと思います。ありがとうございました。

回答No.6

KURUMITO

シート1のＡ，Ｂ，Ｃ列にお示しの表が入力されているとします。
1行目は項目名で2行目から下方にデータがあるとします。
作業列をＤ列に作るとしてＤ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(OR(COUNTIF(A$2:A2,A2)=1,AND(B1<>"",B2="")),INT(MAX(D$1:D1))+1,IF(AND(A2="",B2<>""),MAX(D$1:D1)+0.01,""))

また、Ｆ２セルには次の式を入力して下方にオートフィルドラッグします。

=IF(ROW(A1)>MAX(D:D)-1,"",INDEX(A:A,MATCH(ROW(A1),D:D,0)))

つぎにお求めの表ですがシート2に作るとしてＡ列には本・支社区分のＢ列には作業項目、Ｃ列には料金を表示させるとします。

初めにＡ列を例えばＡ1セルからＡ100セルを選択して「データの入力規則」から「リスト」を選択し元のデータには次の式を入力します。

=INDIRECT("Sheet1!F1:F100")

同様にＢ列を例えばＢ1セルからＢ１０0セルを選択して、元のデータには次の式を入力します。

=INDEX(INDIRECT("Sheet1!B:B"),MATCH(A1,INDIRECT("Sheet1!A:A"),0)):INDEX(INDIRECT("Sheet1!B:B"), MATCH(INDEX(INDIRECT("Sheet1!D:D"),MATCH(A1,INDIRECT("Sheet1!A:A"),0))+1,INDIRECT("Sheet1!D:D"),0)-1)

Ｃ1セルには次の式を入力して下方にオートフィルドラッグします。

=IF(B1="","",INDEX(Sheet1!C:C,MATCH(B1,Sheet1!B:B,0)))

データの入力規則では他のシートを参照するにはINDIRECT関数を使うことで対応できませね。

お礼

この度はどうもありがとうございます。
今後のExcel活用の参考にさせて頂きたいと思います。ありがとうございました。

回答No.5

MackyNo1

間違えたレイアウトの違う添付画像を送付してしましましたので、再度アップします。

お礼

この度は画像まで付けて頂いて、どうもありがとうございます。
今後のExcel活用の参考にさせて頂きたいと思います。ありがとうございました。

回答No.4

MackyNo1

提示された、1行目が項目名でA２セルから下に入力されているレイアウトの表をそのまま使う場合（ただし、データの最終行のA列の１つ下のセル、添付画像のデータならA14セルにはデータの終了を示す空白スペースを入力してください）、以下のような名前定義を行えば、たとえばE2セルに入力された本・支社名から自動的にF２セルにその作業項目のリストが表示され、G列に金額が自動的に入力されるシートを作成することができます（添付画像参照）。

入力規則を作成するF2セルを選択して、「挿入」「名前」「定義」で、たとえば「作業項目」と名前を付け、参照範囲に以下の式を入力します。

=OFFSET(Sheet1!$A$1,MATCH(Sheet1!E2,Sheet1!A:A,0)-1,1,MIN(INDEX((OFFSET(Sheet1!$A$1,MATCH(Sheet1!E2,Sheet1!A:A,0),0,100,1)="")*100+ROW(Sheet1!$A$1:$A$100),)),1)

「データ」「入力規則」で「リスト」を選択して「=作業項目」と入力します。

その横のG2セルには以下の式を入力します。

=IF(F2="","",VLOOKUP(F2,B:C,2,0))

お礼

この度はどうもありがとうございます。追加で頂いた画像も見させて頂きました。
今後のExcel活用の参考にさせて頂きたいと思います。ありがとうございました。

回答No.3

tom04

No.2です！
たびたびごめんなさい。

今日は画像のアップが上手くできません。
どうも調子が悪いみたいです。
当方のコンピュータが悪いのかどうか判りませんが・・・

少し時間をあけて、後ほど画像のアップに挑戦してみます。

どうも何度も失礼しました。m(__)m

お礼

この度はどうもありがとうございます。
またの機会に、宜しくお願い致します。

回答No.2

tom04

こんばんは！
一例です。
当方使用のExcel2003の場合ですが、
別Sheetのデータを入力規則のリスト範囲に設定できないと思いますので、
質問にあるように名前定義しておきます。
表の作成方法を上手に行えば名前定義は簡単だと思います。
ただ、今回の場合は料金を表示させなくてはならないので、↓の画像のような表にしてみました。

名前定義の方法は幾通りかありますが、一番簡単な方法で説明します。
Sheet2のA2～A4セルを範囲指定　→　名前ボックスに直接「本・支社」と入力
同様に、C2～C6を範囲指定　→　「東京本社」と名前定義
C8～C12を範囲指定　→　「名古屋支社」と名前定義・・・
といった具合にすべて本・支社部分のA列の名前定義したものと一致するように名前定義します。

そして、Sheet1のA1セルをアクティブにし、メニュー　→　データ　→　入力規則　→　リスト　を選択し、
元の値の欄に
=本・支社　（←これは先ほど名前定義したものを入力）

A2セルも同様にリストの「元の値」の欄に
=INCIRECT(A1)
としてOK！
これでA2セルはA1セルで選んだものの中からリスト候補が絞られます。

最後にA3セルに
=IF(COUNTBLANK(A1:A2),"",INDEX(Sheet2!D:D,MATCH(A2,Sheet2!C:C,0)))

という数式を入れています。

以上、長々と書いてしまいました。
参考になればよいのですが・・・m(__)m

お礼

この度はどうもありがとうございます。
今後のExcel活用の参考にさせて頂きたいと思います。ありがとうございました。