[[20080205165028]] 『入力規則を使用してシート間をリンクさせるには?』(AHNY) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『入力規則を使用してシート間をリンクさせるには?』(AHNY)

いつもお世話になっております。

以前に入力規則について質問させていただきましたが、また壁にぶち当たったので…ご指導お願いいたします。

【シート1】

A4とA5、A6とA7というように2つずつセルが結合しています。ここは「整理番号」として入力されており、A503まで続いています。

同じくB4とB5が結合し、「氏名」として入力されており、B503まであります。

C列はC4が前半、C5が後半、C6が前半、C7が後半というように交互に「前半」「後半」となりC503まであります。

D列以降はAB列まで、授業名が書かれてあります。

D4からAB503までは空白です。

【シート2】

A列は「整理番号」が一列ごとに規則的に並び、A254まで続いています。シート1と同じ番号です。

B列は「氏名」が一列ごとに規則的に並び、B254まで続いています。シート1と同じ名前です。

C列以降はAV列まで授業名が書かれてあります。

【依頼内容】

@シート2のM5が"未提出"ならば、シート1のD4に「2」だけを入力できるようにしたいのです。しかも、リストのようにクリックだけで入力できるようにしたいです。

 また、シート2のM5が"未提出"でも、シート1のD4が空欄のままの場合もあります。

Aシート2のR5が"未提出"ならば、シートD5に「1」だけを入力できるようにしたいです。Aと少し違うのは、入力できる数値が「1」だけにしたいのです。

 こちらも同様にリストのようにクリックだけで入力できるようにしたいです。

拙い説明で本当にすみません。ご指導お願いいたします。


 仕様がわからないと混乱の元ですので、以前のトピックをリンクしておきます。
[[20080110140312]] 『入力規則を使用してシート間とセル間をリンクさせる』(AHNY)
(みやほりん)(-_∂)b


仕様がわからないというのは、一体どういうことなのでしょうか?知識不足ですみません。

今回、自分で入力規則に設定してみましたが…エラーが出ました。
もともと、リストで「2」や「1」のみ出るように設定していたのですが、条件が加わりました。

みやほりんさんご意見への回答としてズレているかもしれません、すみません。
(AHNY)


 「仕様」というのは、この場合、AHNYさんのブックの状態のことを指します。
この[[20080205165028]]で提示された命題だけで回答すると、以前の流れと
矛盾・競合が出るのではないでしょうか。シート1のD5が両方の相談で重複
して提示されているので、別個に考えるべきではないでしょう。
 
>条件が加わりました。 
どのように?
(みやほりん)(-_∂)b

ご説明ありがとうございます。私の説明不足で申し訳ありません。

前回の質問に使用したBOOKとは違うものですので、「以前の流れと矛盾・競合が出る」ということを、どのように考えていけばいいのか私はわかりません…。

データを管理していく目的は同じなのですが、対象が違うのでBOOKを変えているのですが…。

また、条件が加わったと言う点ですが、こちらも説明不足でした。すみません。

もともとは、例:シート1のD4に「入力規則」で「2」のみを入れるように設定していましたが、【依頼内容】で述べましたように、「シート2とのリンクが必要になった」=「条件が加わった」と表現しました。

説明不足でした、すみません。

よろしくご指導お願いいたします。(AHNY)


 まだ説明が不足しているように思えます。
シート2のM5、R5がそれぞれ"未提出"ではないときには、
シート1のD4、D5はそれぞれどのようになるのでしょう。
シート2のM5、R5へ入力される値に対応するシート1のD4、D5の値が
固定的ならば、関数による処理の方が適しています。
 
命題例:
シート2のM5が"未提出"ならば、シート1のD4に「必ず2」、それ以外は空白
回答例:
シート1のD4へ =IF(シート2!M5="未提出",2,"")
 
(みやほりん)(-_∂)b

みやほりんさん、本当にありがとうございます。すみません。

@シート2のM5が"未提出"ではないときには、シート1のD4は「空欄」で「何も入力できないようにしたい」です。

Aシート2のM5が"未提出"ならば、「2」かそのまま「空欄」にしたいです。その場合、入力規則のリストのようにクリックのみで設定できれば、と思います。

Bシート2のR5が"未提出"ではないときには、シート1のD5は「空欄」で「何も入力できないようにしたい」です。

Cシート2のR5が"未提出"ならば、「1」かそのまま「空欄」にしたいです。

Dシート1のD4、D5といった隣接するセルに関連(関係?)はありません。

もう一点の「シート2のM5、R5へ入力される値に対応するシート1のD4、D5の値が固定的ならば、関数による処理の方が適しています。」の件は

その後の集計の関係と、作業上エクセルを全く知らない方がクリックだけで処理していくため、単純な作業で入力できるようにしたいのと、ロックをかけるという点で「入力規則」で処理したいと考えました。

本当に拙い説明でご迷惑おかけしております。よろしくお願いいたします。(AHNY)


 こういうのはワンステップずつやってみると提示されている数式も理解できます。
 
【下準備】
任意のセル一つに「'」を入力し、そのセルを選択した状態で 挿入>名前>定義 
で例えば「範囲0」と名前を定義します。
「範囲0」のすぐ下の行に「2」を入力し、そのセルと「範囲0」を含む範囲を選択
した状態で同様に 「範囲1」と名前定義します。
 
【実験1】
Sheet1のD4とSheet2のM5の関係においてのみ、まず考えて見ます。
Sheet1のD4を選択した状態で入力規則のリスト設定。
リストとして次の数式を入力。
=IF(INDIRECT("Sheet2!M5")="未提出",範囲1,範囲0)
 
さて、いかがでしょうか。
(みやほりん)(-_∂)b

ご指導ありがとうございます。

ご教授のとおり設定し、シート2のM5が「未提出」のときにシート1のD4に「2」を入れられるか確認しましたところ「#VALUE」となりました。

何度も私の設定が間違ってないか確認しましたが、間違ってなさそうです。

そして、幾度となく私の説明不足の繰り返しなのですが…シート2のM5にはIF関数が入っています。

そのためシート1のD4が#VALUEとなるのでしょうか…。

あまりにお恥ずかしいまでの説明不足ですみません。

説明を補足したほうがよいと思われる点について、あげさせていただきます。

シート1のA列とシート2のA列はそれぞれ同じ出席番号です。

シート1のB列とシート2のB列はそれぞれ同じ氏名です。

シート1のC列とシート2のM列はそれぞれ同じ授業名です。

実は、「シート3」というシート2と全く同じ様式で、シート3のデータをシート2にリンクさせているシート3があります。

そのため、シート2のM5:M503には関数が入っています。

補足になっているかどうか心配ですが、何卒よろしくお願いいたします。(AHNY)


 下から失礼します。

 みやほりんさんご呈示の式を
 直接D4セルに入力しておられませんかね?
【実験1】では
 ◆Sheet1のD4を選択した状態で入力規則のリスト設定。
 ◆リストとして次の数式を入力~~~~~~~~~~~~~~~~~~~~
   ~~~~~~~~~~~~
 ということになっていると思いますが。

 (HANA)

 表現がまずかったですね。入力規則でリストを選択して「元の値」へ提示した
数式を入力です。失礼しました。
(数式は少し変更してます)
ちなみに、先に進めるに当たって気になっているのは、次の点。
Sheet1は 4:503 の 500行。授業名入力範囲がC:AB の 26列。
                 もしくはD:AB の 25列。
Sheet2は 5:254 の 250行。授業名入力範囲がM:AV の 36列。
 
Sheet1がSheet2のA:B列のデータが1行飛びで入力されている、というのは今まで
の書き込みや上記で推測できますが、列数が合わない。
(みやほりん)(-_∂)b


HANAさん

ありがとうございます。シート1のD4に直接ではなく、入力規則のリストに数式をいれております。

みやほりんさん

これもまた私の説明不足でした。すみません(>。<)

シート1の行は、250名分が「前半」と「後半」が交互にあるので、みやほりんさんのおっしゃるように2倍の500行です。

シート1の列は、授業名36個あるうちの25個を抽出しています。この25個はシート2のM、R、S、T、U、W、X、AA、AC、AE、AG、AI:AVと同じです。

シート2の行は、250名分なので250行です。列は36種類ある授業のうち、すべての情報を管理しているので36列です。

何卒よろしくお願いいたします。(AHNY)


 >シート1のD4が#VALUE
もしセルに「#VALUE!」が表示されているなら、
この現象はSheet1のD4に直接 =IF(INDIRECT("Sheet2!M5")="未提出",範囲1,範囲0)
を入力しているときの現象です。
もう一度手順を書き直しますので、もう一度設定しなおしてみてください。
 
【下準備】
任意のセル一つに「'」を入力し、そのセルを選択した状態で 挿入>名前>定義 
で例えば「範囲0」と名前を定義します。
「範囲0」のすぐ下の行に「2」を入力し、そのセルと「範囲0」を含む範囲を選択
した状態で同様に 「範囲1」と名前定義します。
【実験1】
Sheet1のD4とSheet2のM5の関係においてのみ、まず考えて見ます。
Sheet1のD4を選択した状態で入力規則のリスト設定。
リストの「元の値」の入力欄に次の数式を入力。   ←*ここの表現がおかしかった。
=IF(INDIRECT("Sheet2!M5")="未提出",範囲1,範囲0)
 
(みやほりん)(-_∂)b

#VALUE!については、もともと「元の値」に数式を入力していましたが表示されていました。私の入力ミスでしょうか…。

今回も「元の値」に、数式を=IF(INDIRECT("Sheet2!M5")="未提出",範囲1,範囲0)にしたところ問題なかったです!!!

そこで、シート2のM5をシート1のD4と関連させることは完成してしただきました。

同じようにシート2のM6をシート1のD6に、シート2のM7をシート1のD8に反映させていく場合、一つのセルごとに入力規則の設定をしなければいけないのでしょうか?

フィルコピーでは、「M5」のままでコピーされてしまいますのでさらに関数が必要ということでしょうか?

質問ばかりで申し訳ありません。よろしくお願いいたします。(AHNY)


 >「M5」のままでコピーされてしまいますのでさらに関数が必要ということでしょうか? 
その通りです。
 
「INDIRECT("Sheet2!M5")="未提出"」の部分でINDIRECT関数に与える文字列のパラメータ
(引数)を「Sheet2!M5」に固定してしまっているので、そのようになります。
 
Sheet1   Sheet2
D4   → M5
D6   → M6
D8   → M7
・
・
と言う風にINDIRECT関数の引数の文字列が変化するようにそのように数式を工夫します。
【実験2】
新規ブックでよいので、Sheet1とSheet2を用意します。
Sheet1のD4に次の数式を入力します。
 
="Sheet2!M"&(MAX(ROW())-2)/2+4
コレを5行目以下へコピーしていくと次のような文字列が生成されます。
	D
4	Sheet2!M5
5	Sheet2!M5
6	Sheet2!M6
7	Sheet2!M6
8	Sheet2!M7
9	Sheet2!M7
10	Sheet2!M8
11	Sheet2!M8 
 
4、6、8行目のセルでそれぞれ関連付けたいセルを表現する「文字列」が作成されます。
コレが上手くできましたら、ホンバンのブックで次のように設定してみてください。
Sheet1!D4に設定した入力規則のリストの「元の値」に、数式を
=IF(INDIRECT("Sheet2!M"&(MAX(ROW())-2)/2+4)="未提出",範囲1,範囲0)
 
そして、D4のセルをD6、D8・・・へコピーします。
 
【ちょっと解説】
INDIRECT関数は「参照を表現する文字列を引数として参照を行う」関数です。
と言う説明をしても何のことやら分かりませんが、適当なブックで下記のような
例を試してみてください。
 
	A	B
1	5	
2	7	=INDIRECT(B1)
3	13	=INDIRECT("B1")
 
B2、B3にそれぞれ提示したようなINDIRECT関数を入力します。
この二つのINDIRECT関数引数の違いは、
B2:B1へのセル参照
B3:"B1"という文字列 です。
 
結果は入力してみればわかりますが、
B2には「#REF!」エラーが返ります。「参照できません」の意味です。
B3には「0」が返ります。
 
次に、B1に「A1」と文字列を入力します。
B2には「5」が返ります。
B3には「A1」が返ります。
 
B1に「A2」「A3」と文字列を入力するとそれぞれ。
B2には「7」「13」が返ります。
B3には「A2」「A2」が返ります。
 
このことから、
=INDIRECT(B1) は「B1に入力された文字列で表現されたセルアドレスを参照する」
=INDIRECT("B1") は「B1を参照する」
 
ということで、INDIRECT関数でセル参照を行うにはその引数に
「セルアドレスを表現する文字列を与えてやればよい」ということです。
で、何がいいたいか、というと、
【実験2】でやっているのは、INDIRECT関数の引数になっているのは、
「関連するセル」のセルアドレスを文字列として作成する数式ですよ、ということです。
 
回答者が提示した数式をそのまま使っているだけでは、応用は利きませんから、
そういう仕組みは理解しておいた方が便利でしょう。
早く結果を得たい人にはまどろっこしいでしょうが。
 
【さらに解説】
入力規則のリストの「元の値」の部分では直接他のシートへの参照ができません。
=IF(Sheet2!M5="未提出",範囲1,範囲0) は
「他のワークブックまたは他のシートへの参照は使用しません」というアラートが出る、
ということです。
「名前定義された範囲名による参照」
または
「INDIRECT関数による参照」を利用することで他のシートへの参照が可能になります。
[[20050726084509]] 『別シート参照』(みやほりん)
 
(みやほりん)(-_∂)b


ご無沙汰しております。入院しており、せっかくのご指導をそのままに放置しておりました。

継続してご指導お願いいたします。

みやほりんさんのINDIRECT関数のご説明はわかりやすかったです。ありがとうございます。

実験2を行いました。しかし、この時点で壁にぶち当たりました。

>="Sheet2!M"&(MAX(ROW())-2)/2+4

>コレを5行目以下へコピーしていくと次のような文字列が生成されます。

私が行ったところ・・・

	D
4	Sheet2!M5
5	Sheet2!M5.5
6	Sheet2!M6
7	Sheet2!M6.5
8	Sheet2!M7
9	Sheet2!M7.5
10	Sheet2!M8
11	Sheet2!M8.5

となりました。

よろしくお願いいたします。(AHNY)


 ご退院(?)おめでとうございます。
↑の数式はAHNYさん指摘の通り。そのようになるようです。
数式の貼り付け間違いだったようです。申し訳ありません。
正確には下記。
="Sheet2!M"&INT((MAX(ROW())-2)/2+4)
 
(みやほりん)(-_∂)b

みやほりん さん

継続してご協力いただきありがとうございます。私の体調は、なんとかパソコンをできるまでになってます。今後ともよろしくお願いいたします。

しかし、私の頭が、まだついていってない状態なのですが…

Sheet1のD4の入力規則のリスト「元の値」に

=IF(INDIRECT("Sheet2!M"&INT((MAX(ROW())-2)/2+4))="未提出",範囲1,範囲0)

の入力でよろしいのでしょうか?

そうしましたところ、Sheet2のM5が"未提出"の場合にSheet1のD4に、「2」か空欄のままにしたいのですが

空欄のドロップダウンになります。

なにをどうしたらよいのやら…体も頭もリハビリ中です。汗  (AHNY)


 こちらではその「元の値」の数式ではAHNYさんが希望として書かれている
動作はしていますので、どこかの手順が違っているのでしょう。
 
「範囲1」、「範囲0」の名前定義の参照範囲が間違っている、
M5に入力されているのが「未提出」ではない(スペースが含まれている)、
という原因が考えられます。
(みやほりん)(-_∂)b

みやほりんさん

う〜ん、私の設定がやはり間違っているのでしょうか…φ(;ェ;`)゚ ゚。

「範囲0」=Sheet1のAF4に’を入力

「範囲1」=Sheet1のAF5に2を入力して、AF4とAF5の2つのセルを選択

そして、Sheet1のD4=IF(INDIRECT("Sheet2!M5"&INT((MAX(ROW())-2)/2+4))="未提出",範囲1,範囲0)

と入力しました。Sheet2のM5が「未提出」なのですが、Sheet1のD4のドロップダウンが空欄になります。

他になにか考えられる間違いはありますでしょうか?

ご指摘願います。(AHNY)


 私の式
=IF(INDIRECT("Sheet2!M"&INT((MAX(ROW())-2)/2+4))="未提出",範囲1,範囲0) 
 AHNYさんの式
=IF(INDIRECT("Sheet2!M5"&INT((MAX(ROW())-2)/2+4))="未提出",範囲1,範囲0) 
 
どこかが違います。
(みやほりん)(-_∂)b

みやほりんさん

o(>▽<o)(o>▽<)oできました!!!!!!

本当に本当にありがとうございます。

順を追って説明していただき、他の行・列にもきちんと応用できました。

感謝いたします(。TωT)ノ☆・゚:*: (AHNY)


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.