excel／不具合の特定データがどこにあるか探したい

質問

助けてください。Excel初心者です。
以下のようなExcelデータがあります。
行番号，あて先A,　あて先B，　送/受
　1,　　○○　,　　■■　,　　送
　2,　　■■　,　　○○　,　　受
　3,　　△△　,　　●●　　　．．．．．

※相手側に荷物を送って(送)、届いたことが確認できた場合は
　あて先が逆の(受)の情報が存在する一覧です。
　上の例では1と2行目がセットですが、このような行が
　複数行存在します。
　必ずあて先が逆になっているもので「送」「受」が
　セット(対)になっていないといけません。
　データは、対が存在しなかったり、対が1つだけではなかったり
　すると問題があるので、それを抽出したいのです。
　各行の横の空白セルにでも　問題ありの行にNGと表示させたいので　　すが、どのような方法があるでしょうか。

　※対になっているものは行で連続とは限りませんが、
　正しく処理できているものであれば、前後15行くらいに
　対のものが必ず存在します。
　宜しく御願いします。

回答No.8

mitarashi

#1です。盛り上がってきましたね。
「送」であって、下側15行以内に対応する「受」が存在しない場合にNGを表示するのを実現してみました。「送」の後、「受」が来る前に再度「送」があっても辻褄が合うと思いますが、試してみて下さい。
.................A...............B...........C....................D....................E....................................................................
..1...あて先A...あて先B...送/受..........作業列......チェック....................................................................
..2.........○○.........■■.........送................................................................................................................
..3.........■■.........○○.........受......○○■■..........................................................................................
..4.........△△.........●●.........送........................................NG....................................................................

D2の式=IF(C2="送","",B2 & A2)
E2の式=IF(C2="送",IF(COUNTIF(INDIRECT("$D$" & ROW()+1 & ":$D$" & ROW()+15),A2 & B2)>0,"","NG"),"")

お礼

返信遅くなり申し訳ありません。「8回答」でできました。
長々とお付き合い頂き有難う御座いました。
とりあえず、コピペして解決しただけで、この式がどういう意味なのか
不明なので、それについては別途　学習します。
(時間はかかると思いますが)
助かりました。関数を一個しか使わない式とかSUMとかしか使ったことがなく、苦戦していたので．．．
(教えて頂いた式ではCONUTIFぐらいしか見たことがありませんでした)

回答No.7

noname#70958

横から失礼します。
地味ですが、組み合わせごとに付番してツレの有無を調べるのが意外と近道かも。

A列：あて先Ａ
B列：あて先Ｂ
C列：送/受
とすると……

１．あて先の順番を送/受に応じて入れ替え、連結する
　D2：　=C2&IF(C2="送",A2&B2,B2&A2)

２．上方について同パターンを数えて付番する
　E2：　=D2&COUNTIF(D$2:D2,D2)

３．送/受を切り替えて同パターン同番号を全行について数え、なければNG
　F2：　=IF(COUNTIF(E:E,REPLACE(E2,1,1,IF(C2="送","受","送")))=0,"NG","")

Excel2003で動作確認。以上ご参考まで。

回答No.6

imogasi

要素が１つ足りないのでは。
Ａ－Ｂは何度も別物品・別便を送ることが考えられる。従って送り状番号のようにその荷物便を特定する番号があるはず。
人間なら近い方の上の行のＢ－＞Ａを見つけ、先の方の物品が着いたという処理をしたことはあるが。
ーー
送り状番号がない場合、
上記のロジックを実現するのは、関数では難しく、ＶＢＡででもやらないとダメでしょう。
ＶＢＡでやるときも、ロジック（考え方の筋道）が必要で、質問者でも考えられるはず。エクセルの初心者と必ずしも関係ない。考えてみましたか？質問にも書いてない。ただ私の考えでは、むつかしいと思う。
ーーー
VBAはわからないかもしれないが、フリーソフトでも使うように、内部処理はわからなくても、実例でやってみたら。
例データ　Ａ－Ｅ列、Ｆ，Ｇ列プログラムでの結果。
行番号 あて先A, あて先B 送/受
1 a b 送 着済
2 c d 送 着済
3 e f 送 着済
4 b a 受 送有
5 g h 送
6 d c 受 送有
7 i j 送 着済
8 j i 受 送有
9 f e 受 送有
10 k l 送
11 L k 受 送りなし
ーー
シートでＡＬＴキー押したままＦ１１キーを押し
出てきた画面（ＶＢＥの画面）のメニューの挿入ー標準モジュールを選ぶ。
出てきた画面に
下記を貼り付け。
Ｆ５キーを押す（実行）。
ーー
Sub test01()
d = Range("A65536").End(xlUp).Row
For i = 2 To d
If Cells(i, "D") = "受" Then
x = Cells(i, "B")
For j = 2 To i - 1
If Cells(j, "C") = x And Cells(j, "D") = "送" And Cells(j, "E") = "" _
And Cells(j, "B") = Cells(i, "C") Then
Cells(j, "E") = "着済"
Cells(i, "E") = "送有"
GoTo nxt
Else
End If
Next j
Cells(i, "F") = "送りなし"
Else
End If
nxt:
Next
End Sub
ーーー
行番号１１の「送りなし」は送り先と受け先でｌとＬ（大文字）が違う入力ミスのため起こっている例。
これは事情を調べる必要がある。
Ｅ列が空白の行の「送り」が未解決（未処理とか、運び中とか、行方不明とか）。
これをチェックする。
ーーー
「受」を中心に、送りを処理すしているのは考えた末のコツ。
上から自分の手前まで探しているのは、「受」けの行の前に「送」りがなければならない、という時間的前後事項を織り込んだロジック。

回答No.5

mitarashi

#1～4です。
>最終行だけでなくその全ての送/受　行に「NG」が反映されてしまいました。
そういう意味でしたか。
>とりあえず個数が奇数なら注意喚起する式を提案いたします。
と#2に記したのは、「注意喚起しますので、最近のデータからチェックしていって下さいね」
という意味で、ご指摘の通り、完全な解になってはおりません。
全体として送／受のペアが成り立っておらず、「受」のデータのみを対象にして、その前15行内に、対応する「送」がひとつもなければエラー表示する等、やりたい事を絞れば可能かもしれませんが、面倒ですね。

回答No.4

mitarashi

#1～#3です。
乗りかかった船なので考えてみました。
前後15個の間に、1対であるか、片割れしか存在しないと決まっている場合は、
=IF(COUNTIF(INDIRECT("$D$" & IF(ROW()-15<2,2,ROW()-15) & ":$D$" & ROW()+15),D2)=2,"","NG")
をE列に入れると前後15個の範囲をチェック可能ですが、実際には2対であったり、2.5対である場合も考えられますので、並び順だけでチェックするのは難しいと思います。

回答No.3

mitarashi

#1&#2です。
ISEVENは分析ツールの中にある関数です。この機会に分析ツールを組み込んでいただくのも良いかと思いますが、下記で代替できます。
=IF(MOD(COUNTIF($D$2:$D$4,D2),2)=0,"","NG")

参考URL：

http://excel.onushi.com/function/iseven.htm

お礼

(3のお礼)
有難う御座います。
エラーは解消されました。
が、#1も#2も結果は同じで、NGのケースでは(奇数時)、
最終行だけでなくその全ての送/受　行に「NG」が反映されてしまいました。(分析ツールについては知識がないのでMODを使用しました)
ISEVENで試していないので、ちよっと使い方を調べて
後ほど試してみます。

回答No.2

mitarashi

#1です。
発送日とかの情報はなくて、セルの並びだけなのでしょうか？それも怖い話の様な感じがしますが。
とりあえず個数が奇数なら注意喚起する式を提案いたします。
E2の式=IF(ISEVEN(COUNTIF($D$2:$D$4,D4)),"","NG")

お礼

(回答2のお礼)
返信ありがとうございます。

セルの並びだけなんです。教えて頂いた方法だと結果が「#NAME?」と
なってしまいます。関数一覧にISEVENというものがなかったのですが
何か違うのでしょうか。あと式を見る限り奇数であった場合、その
最後の行がNGとなるのはどの部分で考慮されているか　わからなかったので　宜しければ教えていただけますか。
宜しく御願いします。

回答No.1

mitarashi

単純ですが、下記の様な方法でいかがでしょうか。
COUNTIFのデータの範囲は適宜変更して下さい。
.................A...............B...........C....................D..........E
..1...あて先A...あて先B...送/受..........作業列チェック
..2.........○○.........■■.........送......■■○○............
..3.........■■.........○○.........受......■■○○............
..4.........△△.........●●.........送......●●△△........NG

D2の式=IF(C1="送",A2 & B2,B2 & A2)
E2の式=IF(COUNTIF($D$2:$D$4,D2)=2,"","NG")

お礼

早々の返信ありがとうございます。
恐れ入ります、追加で確認なのですが、
この式だと２個セットのもの以外をNGとしているのでしょうか。
説明が足りなくて申し訳なかったのですが、あて先ABは繰り返し
使われることもございます。以下例に出すと、一番近いデータを対とみなして5番目だけにNGと出したいのですが、この式で可能でしょうか。
また別の方法は何か御座いますか。
1,　　○○　,　　■■　,　　送
2,　　■■　,　　○○　,　　受
3,　　○○　,　　■■　,　　送
4,　　■■　,　　○○　,　　受
5　　　○○　,　　■■　,　　送
宜しく御願いします。