エクセル(Excel)で貸し出し管理表を作りたいのですが・・・

質問

エクセル(Excel)で貸し出し管理表を作りたいのですが・・・
以下のようなことをしたいのですが、どのようにしたら良いでしょうか？教えてくださいませ。

1つのシート内の上部に、以下のような表があるとします。

┌──────────┐
│貸出品　│　ＮＯ１　│
│──────────│
│貸出時刻│１５：００│
│──────────│
│返却時刻│１６：００│
└──────────┘

下部には以下のような入力用の表があるとします。

┌────────────────┐
│貸出ＮＯ│貸出時刻　│返却時刻　│
│────────────────│
│　　１　│１５：００│１６：００│
│────────────────│
│　　１　│１６：３０│１７：３０│←（Ａ）
│────────────────│

貸し出し管理用のファイルを作りたいのですが、上記のように最初にＮＯ１を貸し出した際に、
貸し出しＮＯ欄には「１」を入力して、貸し出し時刻を入力します。
その後、返却された際には返却時刻を入力します。

次に、返却されてから30分後（１６：３０）に再度ＮＯ１を貸し出し・返却があったとします。
その際には（Ａ）欄のように入力するわけですが、

入力された際に、上部の表が以下のようにかわるようにしたいのです。

┌──────────┐
│貸　　出│　ＮＯ１　│
│──────────│
│貸出時刻│１６：３０│
│──────────│
│返却時刻│１７：３０│
└──────────┘

つまり、貸し出し品ＮＯが同じ品の場合、貸し出し、返却の動きがあった場合、
上部表には常に最新の状態が入ってくるようにしたいのです。

このようにしたいのですが、どのようにしたら良いでしょうか？

教えてくださいますようお願いいたします。

回答No.5

tom04 · ベストアンサー

こんにちは！
外していたらごめんなさい。

A列は全ての貸出NOが入力されるとしています。
↓の画像のF2・F3セルに入る数式はいずれも配列数式になってしまいますので、
この画面からコピー＆ペーストしただけではエラーになると思います。
F2・F3セルに貼り付け後、F2キーを押す、又は貼り付けセルをダブルクリック、又は数式バー内で一度クリックします。
編集可能になりますので、Shift＋Ctrl＋Enterキーで確定します。
数式の前後に｛　｝マークが入り配列数式になります。

F2セルに
=IF(F1="","",INDEX($B$2:$B$100,LARGE(IF($A$2:$A$100=$F$1,ROW($A$1:$A$99)),1)))
としてShift＋Ctrl＋Enterキーです。

同じくF3セルに
=IF(F1="","",INDEX($C$2:$C$100,LARGE(IF($A$2:$A$100=F1,ROW($A$1:$A$99)),1)))
として配列数式！

これでB・C列のデータの中で、F1セルに入力された貸出NOの一番したの行が表示されます。
尚、数式は100行目まで対応できるようにしていますが、
データ量によって範囲指定の領域はアレンジしてください。

以上、参考になれば良いのですが
的外れなら読み流してくださいね。m(__)m

お礼

移動させると機能いたしませんでしたが、教えていただいた方法ではうまくいきました！

ありがとうございました！

補足

画像のとおりですとうまくいくのですが、、、

右の表を上に移動させたとたん機能しなくなります。。。

回答No.4

KURUMITO

Ａ1セルに貸出品、Ａ２セルに貸出時刻、Ａ３セルに返却時刻と入力し、Ｂ1セルには例えばＮＯ１と入力します。
Ａ6セルには貸出ＮＯ、Ｂ6セルには貸出時刻、Ｃ6セルには返却時刻の文字列をそれぞれ入力します。
その下の行には例えばお示しの入力データがあるとします。
そこで作業列としてＥ7セルには次の式を入力して下方にオートフィルドラッグします。
=IF(A7="","",A7&"/"&COUNTIF(A$7:A7,A7))
最後にＢ2セルに次の式を入力し、Ｂ3セルまでオートフィルドラッグします。
=INDEX(B:C,MATCH(RIGHT(B$1,LEN(B$1)-2)&"/"&COUNTIF(A:A,RIGHT(B$1,LEN(B$1)-2)),E:E,0),ROW(A1))
Ｂ2セルからＢ3セルのセルの表示形式を時刻に設定することでＢ1セルで指定した貸出品の最新のデータが表示されます。

お礼

ありがとうございます！

しかし、私のやり方が間違っているのか、、、うまくいきませんでした。。。

回答No.3

kagakusuki

今仮に、上部の表が入力されているセル範囲をA1～B3、下部の表が入力されているセル範囲をA5～C99とします。
　まず最初に、２つの表が入力されているファイル内の適当な場所に、下部の表と同じ行数を持つ、縦１列の表を作って下さい。（印刷や表示の邪魔にならない場所が良いと思います。別のシートでも可）
　ここでは仮に、D5～D99の範囲に作る事にします。
　そして、D6セルに

=IF(A6="","",A6&"番"&COUNTIF(A$6:A6,A6))

という数式を入力して下さい。
　D6に数式を入力した後、D6セルを、D7セル～D99セルにコピーして下さい。
　次に、B2セルに

=INDEX($B$6:$C$99,MATCH(SUBSTITUTE(B$1,"ＮＯ","",1)&"番"&COUNTIF($A$6:$A$99,RIGHT(B$1,LEN(B$1)-2)),$D$6:$D$99,0),1)

と入力して下さい。
　続いて、B3セルに

=INDEX($B$6:$C$99,MATCH(SUBSTITUTE(B$1,"ＮＯ","",1)&"番"&COUNTIF($A$6:$A$99,RIGHT(B$1,LEN(B$1)-2)),$D$6:$D$99,0),2)

と入力して下さい。
　以上で完了です。

　尚、上下の表中の、貸出時刻や返却時刻の欄は、[セルの書式設定]の[表示形式]を、全て共通にして下さい。
　この数式は、下部の表の「貸出ＮＯ」の欄に入力される一連の番号（又は文字列）の中に、B1セルに入力されている文字列から、「ＮＯ」の文字列を削除した文字列が、存在している複数の行の中の、最も下に位置する行を探し出して、その行の貸出時刻や返却時刻を表示しております。
　ですから、B1セルに入力されている文字列を、「ＮＯ１」を「ＮＯ２」に変えると、貸出ＮＯ２の最後の貸出・返却時刻を表示します。
　しかし、「ＮＯ１」を「ＮＯ.１」や「NO１」に変えたり、B1セルと「貸出ＮＯ」の欄に入力される一連の番号の中に、全角文字と半角文字が混ざり合っている場合には、正常に動作しませんので、御注意願います。

　後、数式中の

番

と書かれている部分は、仮に決めたものですから、貸出ＮＯに使用する可能性のない文字列であれば、他の文字列に変更されても構いません。

お礼

私のやり方が間違っていたのか、うまくいきませんでした。。。

ありがとうございました！

回答No.2

mimeu

類似の質問が 2月21日の23時にありました。
『エクセルの関数で「最新の結果」を反映させるためには』 です。
結論としてワークシート関数では不可能です。

しかし、ＶＢＡなら簡単にできます。
Worksheet.Change イベントを処理すればよいと思います。

お礼

やはりVBAなら簡単なのですか・・・

そのあたりも視野に検討してみます。

ありがとうございました！

回答No.1

nattocurry

貸出時刻だけ入力されていて、返却時刻が入力されていない場合は、上部の表はどうなれば良いのでしょうか？

＞つまり、貸し出し品ＮＯが同じ品の場合、

これは、何と何が同じ場合ですか？
上部の表の番号と、入力された番号が同じ？
それとも、入力された番号と、その前に入力された番号？

貸出品番号が違う場合は、どうするんですか？

お礼

表のNOのことでありました。。。

説明が下手で申し訳ありませんでした。。。

ありがとうございました！