Excel関数で、複数条件を検索して値があれば○

質問

Excel関数の質問です。

シートAは貸出台帳で、シートBは返却台帳です。
貸出台帳に入力されているコードが、返却台帳にあれば、
貸出台帳の確認に○を表示したいです。
ややこしい点としては、返却台帳には以前返却された履歴(例えばAAが二つ)が
入力されているため、貸出した日付以降のコードのみを検索するようにしたいです。

シートAに、下記のような値があります。

　　A　　　　B　　　　C　　　
1　貸出日　コード　返却確認
2 　1/1　　　AA　　　　　　　　　　←ここには関数で○を入れたい
3　 1/2　　　BB　　　　　　　　　　←ここは×
4　 1/3　　　AA　　　　　　　　　　←ここは○

シートBには下記のような値があります。

　　A　　　　B
1　返却日　コード
2　1/1　　　BB
3　1/2　　　AA
4　1/3　　　AA

この場合、どのような関数を入力すればよいでしょうか？

回答No.3

tom04 · ベストアンサー

こんばんは！
一例です。

↓の画像で左側がシートAとしています。

シートAのC2セルに
=IF(COUNTBLANK(A2:B2),"",IF(A2<=MAX(IF(シートB!$B$1:$B$1000=B2,シートB!$A$1:$A$1000)),"○","×"))

これは配列数式になってしまいますので、この画面からC2セルにコピー＆ペーストした後に数式バー内で一度クリックします。
編集可能になりますので、Shift＋Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。

これをオートフィルで下へコピーすると画像のような感じになります。

外していたらごめんなさいね。m(__)m

回答No.10

layy

式入れたとして、どう運用しますか？。

貸出入れたときは返却はないし、返却入れたとき貸出台帳側が×から〇にリアルに変わる使い方にする？。
貸出台帳と返却台帳の並びは保証されてる？。
並び変えられても大丈夫？。

貸出があって返却なので、＋１と‐１としたら、±０で貸出可能の意。ステータスをもちたいなら、返却から見た貸出の情報は要らない？。

別のシートに貸出返却がわかるレイアウト、でも良い。

貸出返却のパターン考えるとこの３件は少ないし、既存回答も十分テストして回答しているのか怪しい。

日付とコードで１つしかないような受付番号とかエントリ番号を付けるべき。これならＡＡが重なっても悩まなくて良い。

きちんと動くシステムか不安ですがどうなのでしょう。

回答No.9

KURUMITO

回答No3です。そのごにシートAのA5に1/3,B5にAAさらにA6に1/3、B6にBBと入力し、シートBではA5に1/3,B5にBBと入力して試験したところ、先の式では誤りとなることがわかりました。次のような式をC2セルに入力して下方にオートフィルドラッグしてください。

=IF(A2="","",IF(COUNTIF(INDIRECT("Sheet2!B"&MATCH(INDIRECT("A"&MATCH(B2,B:B,0)),Sheet2!A:A,0)&":B"&COUNT(Sheet2!A:A)+1),B2)>=COUNTIF(B$2:B2,B2),"○","×"))

回答No.8

mzon

各シートにキーをなるフィールド（非表示も可）を作成し、COUNTIFで検索を実行し0であれば×、その他は○
としてやればいいでは？

例）各D列キーを作成（XX部分は行番号）
D列『=AXX&BXX』
貸出のC列『=IF(COUNTIF(Sheet1!D:D,DXX)=0,"×","○")』

以上です。

回答No.7

Wendy02

回答に直接は答えていませんが、こちらのアイデアとして、一考してもよいかと思います。関数は関数としても、まず、シートを別々にするのは管理上良くないと思います。私が作るなら、最低でも、以下のようにすると思います。

　A　　　　B　　　C　　　　D
貸出日　コード　返却日　返却確認
1/1　　　AA　　　1/2　
1/2　　　BB　　　　　　　　　×
1/3　　　AA　　　　　　　　　×
1/4　　　AC　　　　　　　　　×
1/5　　　AD　　　　　　　　　×
1/6　　　AE　　　　　　　　　×

D2 ～の数式
=IF(AND(B2<>"",C2="",TODAY()-A2>10),"×","")

つまり、貸出日から、10日経ったら、×　にする。

大事なのは、この後です。
フィルタオプションを使います。レイアウトは、もう少し工夫したほうがよいですが、簡易データベースにしました。例えば、AA を入れれば、返却確認がないものが出てきます。

　F　　　　　G　　　　　　H
貸出日　　　コード　　　返却日
<=40595　　　AA　　　　　　=

貸出日の数式
="<="&TODAY()

コード
AA　　←変更・入力するのはここだけ

返却日
="="
--------
手作業では面倒なので、ちょっとしたマクロを作りました。

Alt + F11 --> 挿入　-->標準モジュール

手入力は大変ですから、ボタンは、以下は、フォームのボタンにしましたので、標準モジュールです。

Sub ボタン1_Click()
　'フィルタオプションのマクロ
　Dim rng As Range
　With ActiveSheet
　　If .FilterMode = True Then
　　　.ShowAllData
　　End If
　　Set rng = .Range("A1", Cells(Rows.Count, 1).End(xlUp).Offset(, 3))
　　rng.AdvancedFilter _
　　Action:=xlFilterInPlace, _
　　CriteriaRange:=.Range("F1:H2"), _
　　Unique:=False
　　If Application.Subtotal(3, rng.Columns(1).Offset(1)) = 0 Then
　　　MsgBox "該当データがありません。", vbInformation
　　　.ShowAllData
　　End If
　End With
End Sub
Sub ボタン2_Click()
'全画面表示
　 With ActiveSheet
　 If .FilterMode = True Then
　　　.ShowAllData
　 End If
　 End With
End Sub

回答No.6

imogasi

質問者自身が、まず考えて、条件を文章で定式化（箇条書き等）してみるべきです。
そしてその文章内容で、質問者の場合で、漏れや不都合（該当しないのに該当とされる）が無いか、詳しく思考の上で検証すべきです。
ーー
それが本質門では不足しているため、今までの回答で回答者に疑問や、＃５のような疑問が出てくるのです。
上記で私が言っていることは、エクセルの関数の知識とは直接関係ない話で、そこまでは思考力や、自分のデータの情況の洞察力の問題です。
エクセルのことは知らなくても、そこまでは考えないといけません。
コンピュターを使う場合は、人間の賢い洞察力といったものは期待できませんので、その辺を、そぎ落として考える訓練が必要です。
場合によってはデータに新しい必要項目を付け加えないといけない場合があるかもしれません。
それに例示の場合（自分の業務を隠すため、思いついた簡略化した内容の質問例に置き換えている場合が多い）の環境や業務上やその会社では常識に、寄りかかっている質問は、良く考えないと、いけません（例　図書で在れば同じものを
同一日に２回返却はない、しかし別人がその図書を同一日に借りてその日に返すなど）
長い間にはまれなことが起こって、不都合に気づかず、業務などでは大問題になる場合もあり恐ろしいケースもおこります。
鉄道などで、プログラムの不都合のために交通機関がとまり、大影響が出たなども、そういうタイプのケースが多いと思われます。
本件ももう一度情況とデータを良く考えてください。
多分いままで出た回答であてはまるとは思うものの。

回答No.5

KURUMITO

同じ日に貸し出しの操作が2回行われた場合にはこれまで提案されている式では十分な対応ができないでしょう。例えばシートAの5行目、A5に1/3、B5にAAと入力した場合にはC5セルは×となるのですが○になってしまいますね。
それらを解消するためには次の式でどうでしょう。

=IF(A2="","",IF(AND(COUNTIF(INDIRECT("シートB!B"&COUNTIF(シートB!A:A,"<"&A2)+2&":B"&COUNT(シートB!A:A)+1),B2)>0,COUNTIF(B$2:B2,B2)<=COUNTIF(シートB!B:B,B2)),"○","×"))

回答No.4

kagakusuki

返却日が必ず日付順に並んでいるとした場合には、次の数式をシートAのC2セルに入力してから、シートAのC2セルをコピーして、シートAのC3以下に貼り付けると良いと思います。

=IF(OR($A2="",$B2=""),"",IF(COUNTIF(OFFSET(シートB!$B$1,COUNTIF(シートB!$A:$A,"<"&$A2)+1,,COUNTIF(シートB!$A:$A,">="&$A2)),$B2)=0,"×","○"))

回答No.2

mshr1962

コードが同じでかつ、貸出日以降の返却日があれば○、なければ×という判定で

Office2003までなら
C2=IF(SUMPRODUCT((シートB!$A$2:$A$65535>=$A2)*(シートB!$B$2:$B$65535=$B2)),"○","×")

Office2007以降なら
C2=IF(COUNTIFS(シートB!$A$2:$A$65535,">="&$A2,シートB!$B$2:$B$65535,$B2),"○","×")

回答No.1

keithin

＃ご相談に直接回答していないので，不要ならスルーしてください。

ご相談の例示で実は無理があるように思われるのは，１／１に「まだ貸し出されていない」BBが返却されたことになっている点です。
勿論人間的な意味合いとしては「去年貸したんだ」といった事でしょうけど，エクセル的なデータの整合性という点では，そういうイレギュラーは排除した方が色々「簡単」になります。

この場合去年の貸し出し記録を見ても，最後に貸し出したBBが「返却されていない」で終わっている格好です。例えば年またぎで返却された分は「去年のリスト」に返却を記入し，全部返却されたのを確認して初めて去年のリストを閉じるようにしてみたらどうでしょう。

そういう前提で作成してみると，「貸した数だけ返ってきていれば全部○，また返却が１個不足なら最後の記録に×」というだけの判定で出来ます。

作成例
C2:
=IF(B2="","",IF(AND(COUNTIF(B:B,B2)>COUNTIF(Sheet2!B:B,B2),COUNTIF(B:B,B2)=COUNTIF($B$2:B2,B2)),"×","○"))
以下コピー