Excelの関数に詳しい方、教えてください

Excelの関数が得意な方、どうか教えて頂きたいことがあります。


会社の備品の、貸出し表を作成してほしいとボスに頼まれました。

作ったシートは添付したものとほぼ同じで、とても簡単なものです。

Sheet1に、備品と貸出し状態。
sheet2～　Sheet１にのせた備品の、貸出し帳（誰がいつ借りて、いつ返したかを入力）。
　　　　　　　ひとつのSheetに、ひとつの備品の貸出し帳です。
　　　　　　　履歴がずっと残ります。


このsheetで、以下のことができるようにしたいのです。

(1)Sheet１の「状態」のところには、貸出し帳に何も入力がない場合は、常に貸出し可と表示される

(2)貸出し帳の「貸出し日」に入力が入るとｓheet１の「状態」は「貸し出し中」に変化する。

(3)備品が返却され、「返却日」に入力が入ると、またsheet1の「状態」はまた、貸出し可に戻る。

以下この繰り返し。



難しいのですが、これは関数で対応可能でしょうか？
インターネットでいろいろ調べて、この関数使えそうかな？と思ったのをいろいろ試してますが、
なかなかうまくいきません。
（たとえば、Sheet2のセルC15に入力が入った場合のみ・・なら、うまくいくのですが、
　私がやりたいのはセルC１５以下のどこでも、入力が入ったら貸出し中に切り替わってほしいのです。そして、E15以下のどこでも、入力が入ったら貸出し中に戻ってほしいのです。
このあたりがうまくいきません。
複数セルを選択すると、とたんにエラーになります。


どなたか詳しい方、Sheet１の「状態」のセル（C３以下）に入れる計算式はどうなるのか、
教えて頂けると嬉しいです。
　
　

No.4 ベストアンサー 回答者： kagakusuki 回答日時： 2012/12/05 04:23

　ANo.3です。

　済みません、後から気づいたのですが、Sheet１の管理No.欄に、各備品のシートの中には存在しない管理番号を入力した際にも、「貸出可」となっておりました。
　その様な事が無い様にするために、Sheet1のC5セルに入力する関数を次の様なものに変更して下さい。

=IF(ISERROR(INDIRECT("'"&INDEX($D:$D,ROW())&"'!IV65536")),"",IF(IF(ISNUMBER(1/(MATCH(9E+99,INDIRECT("'"&INDEX($D:$D,ROW())&"'!C:C"))>MATCH("貸出日",INDIRECT("'"&INDEX($D:$D,ROW())&"'!C:C"),0))),ISNUMBER(1/(MATCH(9E+99,INDIRECT("'"&INDEX($D:$D,ROW())&"'!C:C"))>MATCH(9E+99,INDIRECT("'"&INDEX($D:$D,ROW())&"'!E:E")))),FALSE),"貸出中","貸出可"))

　それから、Sheet1のB5セルに入力する関数に関してですが、ANo.3で挙げさせて頂いた関数でも、問題なく動作致しますが、次の様にした方が、より適切だと思います。

=IF(INDEX($D:$D,ROW())="","",IF(ISERROR(INDIRECT("'"&INDEX($D:$D,ROW())&"'!IV65536")),"【該当No.無し】",INDIRECT("'"&INDEX($D:$D,ROW())&"'!B3")&""))

　尚、ANo.3では説明してはおりませんでしたが、添付画像に写っているExcelBookの例では、Sheet1のB5以下のセルに対して、セルの値が

="【該当No.無し】"

となった場合に、文字の色を赤に変える様にする、条件付き書式を設定しております。

この回答へのお礼

めちゃくちゃ丁寧な回答！！びっくりしました。
これだけの内容を説明してくださるのに、とてもお時間がかかっただろうに、見ず知らずの者にこんな親身になって頂き、ありがとうございます！

こちらの関数も、成功しました！ひとりであれだけ時間をかけてもだめだったのに、ここで聞いたらものの1日たらずで解決してしまいました。ほんとにもっと早く聞けばよかった・・・・

お二人にベストアンサーを差し上げたいですが、ここまで詳細に説明してくださって感激したので、
kagakusukiさんをベストアンサーにさせて頂きました。

でも、お二人に大感謝しています。
ほんとにほんとにありがとうございました！！！

お礼日時：2012/12/05 20:04

No.3 回答者： kagakusuki 回答日時： 2012/12/05 03:59

　まず、各貸出品目のシートのシート名がSheet2やSheet3としたままでは、貸出や返却が行われて、記録を残さなければならなくなった際に、どのシートにどの品目の記録を残せば良いのかが解り難いため、作業に手間取る事になるかと思います。

　ですから、各貸出品目のシートのシート名を、111や112と言った管理No.と同じ名称にして下さい。（備品名と同じにしますと、シート見出の幅が長くなり過ぎる恐れがあります）
　又、、各貸出品目のシートのB3セルには、その品目の備品名を入力する様にして下さい。
　上記の２点の条件は、Sheet1に自動表示させる際のデータとして用いますから、必ず行って下さい。
　その上で以下の様に致します。

　まず、各貸出品目のシートの雛型となる「雛型」シートを作成します。
　この雛型シートのB列は、使用者名の入力欄として使用し、C列は貸出日の入力欄、E列は返却日の入力欄として使用するものとし、C14セル（C列のセルでさえあれば、14行目以外の別の行でも構いません）には「貸出日」と入力されているものとします。
　又、Sheet1の表では、D列が「備品を指定するために管理No.を入力する」ための管理No.欄、Sheet1のその他の列は自動的に情報を表示させるための欄で、B列が備品名欄、C列が「貸出中」なのか「貸出可」なのかを表示する状態欄、E列が「状態が貸出中」の場合において最後の貸出日を表示させるための貸出日欄、F列が「状態が貸出中」の場合において最後の使用者を表示させるための使用者欄とします。
　そして、Sheet1のB5セル（B列の「備品名」と入力されているセルの直ぐ下のセルでさえあれば、B列の別の行のセルに入力しても構いません）に次の関数を入力して下さい。

=IF(INDEX($D:$D,ROW())="","",IF(ISNUMBER(ROW(INDIRECT("'"&INDEX($D:$D,ROW())&"'!A1"))),INDIRECT("'"&INDEX($D:$D,ROW())&"'!B3")&"","【該当No.無し】"))

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

=IF(INDEX($D:$D,ROW())="","",IF(IF(ISNUMBER(1/(MATCH(9E+99,INDIRECT("'"&INDEX($D:$D,ROW())&"'!C:C"))>MATCH("貸出日",INDIRECT("'"&INDEX($D:$D,ROW())&"'!C:C"),0))),ISNUMBER(1/(MATCH(9E+99,INDIRECT("'"&INDEX($D:$D,ROW())&"'!C:C"))>MATCH(9E+99,INDIRECT("'"&INDEX($D:$D,ROW())&"'!E:E")))),FALSE),"貸出中","貸出可"))

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

=IF(INDEX($C:$C,ROW())="貸出中",LOOKUP(9E+99,INDIRECT("'"&INDEX($D:$D,ROW())&"'!C:C")),"")

　次に、Sheet1のE5セルの書式設定の表示形式を[日付]の

2001年3月14日

として下さい。

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

=IF(INDEX($C:$C,ROW())="貸出中",LOOKUP(9E+99,INDIRECT("'"&INDEX($D:$D,ROW())&"'!C:C"),INDIRECT("'"&INDEX($D:$D,ROW())&"'!B:B")),"")

　次に、Sheet1のB5～F5の範囲をコピーして、同じ列の6行目以下に貼り付けて下さい。

　次に、雛型シートのコピーシートを、各貸出品目の数だけ複製して下さい。
　次に、それらのコピーシートのシート名を、各々の管理No.と同じ名称に変更して下さい。
　次に、そうして作った各備品毎のシートのB3セルに、各備品の名称を入力して下さい。
　次に、Sheet1のD列に各品目毎の管理No.を並べて入力して下さい。

　以上で準備は完了で、後は、各備品のシート毎に、貸出履歴の情報を入力しますと、Sheet1に「備品名」、貸出の「状況」、それに貸出中の場合には、最終の「貸出日」と現在の「使用者名」が、自動的に表示されます。


　後、それから、印刷した際に解り易くするためにも、各備品のシートの適当なセル（ここでは仮にE3セルとします）に、管理No.を入力・明記しておいた方が良いと思います。
　その際には、シート名と管理番号は同じなのですから、雛型シートを作成する際に、「管理No.（＝シート名）」を表示させるセルに、次の関数を入力しておきますと、シート名に合わせて自動的に表示が変更されますので、一々入力し直す手間が省けますし、管理番号の記載を変更し忘れる恐れもなくなりますので、便利です。（下記の関数はE3セルに入力する場合のものです）

="管理No.："&REPLACE(CELL("filename",E3),1,FIND("]",CELL("filename",E3)),)

No.2 回答者： tom04 回答日時： 2012/12/04 20:37

No.1です！

たびたびごめんなさい。

画像をよくよく見ると備品はもっとたくさんあるわけですね？
それでは1セルずつ数式を入れるのも大変ですので、
前回の方法でOKだとすると、

Sheet2以降のSheet名がSheet1のB列に表示してあるSheet名（各備品のSheet名）になっていれば
Sheet1のC3セルに↓の数式を入力しオートフィルで可能です。

=IF(COUNT(INDIRECT(B3&"!C:C"))=COUNT(INDIRECT(B3&"!E:E")),"貸出可","貸出中")

※　各Sheetとも同じ配置だという前提です。m(_ _)m

この回答へのお礼

こんなことなら、もっと早くここで質問すればよかったと思いました！
すごいです。あっという間に成功しました～！
ほんとに感謝です。ありがとうございます。
みなさん、ほんとに関数を使いこなしていてすごいの一言です。

お二人にベストアンサーを差し上げたいですが、一人にしかあげれないなんて、ほんとに悲しい。

でもものすごく助かりました。
ほんとにほんとにありがとうございました！

お礼日時：2012/12/05 19:59

No.1 回答者： tom04 回答日時： 2012/12/04 20:14

こんばんは！

Sheet2・Sheet3ともC・E列はシリアル値が入るとして・・・

Sheet1のC3セルに
=IF(COUNT(Sheet2!C:C)=COUNT(Sheet2!E:E),"貸出可","貸出中")

C4セルに
=IF(COUNT(Sheet3!C:C)=COUNT(Sheet3!E:E),"貸出可","貸出中")

としてみてはどうでしょうか？

※　Sheet2・Sheet3のC・E列は他に文字列セルがあっても良いのですが、
数値セルはない！という前提です。m(_ _)m