人力検索はてな
ﾓﾊﾞｲﾙ版を表示しています｡PC版はこちら
i-mobile
入金確認するための､行列関係のｴｸｾﾙﾏｸﾛをつくってください！



【概要】
やりたいことは､2つのｼｰﾄにある金額を比較して､一致したら､その行同士に存在する名前を検索､
その結果を成功なら結果ｼｰﾄに､失敗なら結果ｼｰﾄ2に出力していくというものです｡


詳細は画像と､下記追記しますので､ご確認ください｡



1354853314
●拡大する
●質問者: ﾘｾｰﾙ京都買取
●ｶﾃｺﾞﾘ:ｺﾝﾋﾟｭｰﾀ 科学･統計資料
○ 状態 :終了
└ 回答数 : 2/2件
▽最新の回答へ
● 質問者から

【詳細】
ｼｰﾄは4つあります｡
まとめｼｰﾄと､原本､結果ｼｰﾄ､結果ｼｰﾄ2というものです｡
詳しくは添付画像をご確認ください｡







【まとめｼｰﾄ】 (いずれも1行目は見出しです )
A,B,C,F,G-Lまで
名前, 金額 ,固有ID, 検索ﾜｰﾄﾞ,名前の候補


【原本ｼｰﾄ】
D列,F 列,G列 ,AI列
固有 ID,名前漢字, 名前ｶﾅ ,金額

となっています｡



【検索順序と結果ﾙｰﾙ】 (行の範囲によって検索方法が少し違います )

【 2-300行までは】
1･まとめｼｰﾄ C列の固有ID を原本ｼｰﾄ D2から下に向かってｾﾙが空白になるまで検索し､一致した場合下記のように比べます｡



＜固有ID一致による金額比較＞(まとめｼｰﾄ1行?300行目迄の処理)
まとめｼｰﾄの同じ行の B列(金額)
↑比較↓
原本ｼｰﾄでﾋｯﾄした行の AI列(金額)



【検索後の処理(ﾋｯﾄしたとき)】
結果ｼｰﾄにまとめｼｰﾄと同じように下に向かって順番に名前(まとめA列の該当行)･金額･固有 IDを出力します｡


【検索後の処理(ﾋｯﾄしないとき)】
固有IDがﾋｯﾄしない､ IDがﾋｯﾄしても金額があわなかったときには結果ｼｰﾄ2に同じように名前･金額(もともとの検索している方の金額)･固有 IDを出力します｡



【 301行以降は】

1･まとめｼｰﾄの B301以降の金額を原本ｼｰﾄの AI2から下に向かってｾﾙが空白になるまで検索し､一致した場合､下記の｢名前｣を比べます｡



＜金額一致による名前のあいまい比較＞(まとめｼｰﾄ301行?1500行目迄の処理)
例･
まとめｼｰﾄ B301の同じ行のF列､ G?L 列までの内容
↑比較↓
原本ｼｰﾄでﾋｯﾄした行の F列､G 列





原本ｼｰﾄのほうが名前がﾌﾙﾈｰﾑとなっているため､｢含まれるかどうか｣で検索してください｡

301行の例でいえば､

B301の4000が原本AI3と一致しますので､そのとき､同じ行のまとめｼｰﾄの検索ﾜｰﾄﾞF301の｢ｻｲﾄｳ｣と同じ行のG?Lの斉藤､斎藤､齊藤､西東､西藤と､
原本のﾋｯﾄした3行目のF列｢斎藤 三郎｣とG3のｻｲﾄｳ ｻﾌﾞﾛｳ｣に含まれるかどうか検索してほしいのです｡1つでも含まれれば結果ｼｰﾄに出力します｡

上記ですでにでた【検索後の処理】と同じ処理をします｡1つのまとめｼｰﾄの金額の中で､名前が何度ﾋｯﾄしても1つだけしか結果ｼｰﾄには出力させません｡ｴﾗｰ時も同様です｡


【これらを繰り返し1500行まで行ってほしいです】
※途中､まとめｼｰﾄの金額の数字が0もしくは空白のときは比較せずに､次の行の金額にすぐに移ります｡



※1?300までのくくり､301?1500まではかわることがありますので､かえられるようにわかりやすくしていただけると助かります｡(なお前半､後半の検索の仕方は変わりませんのでご安心ください｡変えるのは前半1?500､後半501?2000まで等の検索範囲のみです)
※G?Lまでのくくりを変える可能性(例L?AA迄等)がありますので､それもかえられるように分かりやすくしていただけると助かります｡(ただし､変えても連続しているものとします)
※原本の固有ID･名前･ｶﾅ･金額の列もこれもかわる可能性がありますので､ご注意ください｡
(縦に連続することはかわりません｡)列指定だけできるようにわかりやすくお願いいたします｡

※途中ｴﾗｰが入っているｾﾙがありますが､ｴﾗｰは空白と考えて検索を続けます｡


かなりわかりにくいかもしれませんので､ご質問は何なりとお願いたします｡

▽1 ● sinrabanshyo
●5ﾎﾟｲﾝﾄ

関数のVLOOKUPとASUMIF,SUM関数を駆使するとできそうですが､1500行ともなると
重たくなりそうですね｡
僕なら､VBAで組みます｡

VBAで良ければｺｰﾃﾞｨﾝｸﾞします｡

@elephantbeetle

ﾘｾｰﾙ京都買取さんのｺﾒﾝﾄ
ぜひVBAでお願い致します｡回答ありがとうございました｡
▽2 ● Mook
●800ﾎﾟｲﾝﾄ ﾍﾞｽﾄｱﾝｻｰ

仕様を理解しきれていない部分もありますし､説明と絵が矛盾しているように見える部分もありますが､できるだけ説明の意図に沿った処理にしたつもりです｡

ご確認ください｡

Option Explicit

Sub CheckWS()
'// 処理範囲の設定
 Const Method1Rows = 2
 Const DataEnd1Row = 300
 Const Method2Rows = 301
 Const DataEnd2Row = 1500
 
'// ｼｰﾄの設定
 Dim matomeWS As Worksheet
 Set matomeWS = Worksheets("まとめｼｰﾄ")
 
 Dim masterWS As Worksheet
 Set masterWS = Worksheets("原本")
 
 Dim result1WS As Worksheet
 Set result1WS = Worksheets("結果ｼｰﾄ")
 
 Dim result2WS As Worksheet
 Set result2WS = Worksheets("結果ｼｰﾄ2")
 
 Dim isFound As Boolean
 
'// 前半の処理
 Dim r As Long
 Dim idRng As Range
 For r = Method1Rows To DataEnd1Row
 isFound = False
 Set idRng = masterWS.Columns("D:D").Find(what:=matomeWS.Cells(r, "C").Value, lookat:=xlWhole)
 If Not idRng Is Nothing Then
 If masterWS.Cells(idRng.Row, "AI").Value = matomeWS.Cells(r, "B").Value Then
 isFound = True
 End If
 End If
 If isFound = True Then
 result1WS.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Resize(1, 3).Value _
 = matomeWS.Cells(r, "A").Resize(1, 3).Value
 masterWS.Cells(idRng.Row, "AT").Value = "入金準備" '// 【2】対応
 Else
 result2WS.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Resize(1, 3).Value _
 = matomeWS.Cells(r, "A").Resize(1, 3).Value
 End If
 Next
'// 後半の処理
 Dim c As Long
 Dim priceRng As Range
 Dim firstRng As Range
 For r = Method2Rows To DataEnd2Row
 If IsNumeric(matomeWS.Cells(r, "B").Value) = True Then
 If matomeWS.Cells(r, "B").Value > 0 Then
 isFound = False
 Set priceRng = masterWS.Columns("AI:AI").Find(what:=matomeWS.Cells(r, "B").Value, lookat:=xlWhole)
 If Not priceRng Is Nothing Then
 Set firstRng = priceRng
 Do
  '// 名前のﾁｪｯｸ
 For c = 6 To 12  '// F?L 列との比較
 If Len(matomeWS.Cells(r, c).Value) = 0 Then Exit For
 If InStr(masterWS.Cells(priceRng.Row, "F"), matomeWS.Cells(r, c).Value) > 0 Then
 isFound = True
 Exit Do
 End If
 If InStr(masterWS.Cells(priceRng.Row, "G"), matomeWS.Cells(r, c).Value) > 0 Then
 isFound = True
 Exit Do
 End If
 Next
 Set priceRng = masterWS.Columns("AI:AI").FindNext(priceRng)
 Loop While firstRng.AddressLocal <> priceRng.AddressLocal
 End If
 If isFound = True Then
 With result1WS.Cells(Rows.Count, "A").End(xlUp)
 .Offset(1, 0).Resize(1, 2).Value = matomeWS.Cells(r, "A").Resize(1, 2).Value
 .Offset(1, 2).Value = masterWS.Cells(priceRng.Row, "D").Value  '// 【1】対応
 masterWS.Cells(priceRng.Row, "AT").Value = "入金準備" '// 【2】対応
 End With
 Else
 result2WS.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Resize(1, 3).Value _
 = matomeWS.Cells(r, "A").Resize(1, 3).Value
 End If
 End If
 End If
 Next
End Sub
ﾘｾｰﾙ京都買取さんのｺﾒﾝﾄ
MOOKさん､いつもありがとうございます｡やってみたのですが､300行まではよい感じのようです｡ありがとうございます｡しかし､下記がうまくいきません｡ 301?1200はﾋｯﾄするはずなのに､結果ｼｰﾄ2になってしまう｡ 1201行以降はどちらの結果ｼｰﾄにも表示されない｡ よく理由をしらべていたのですが､まとめｼｰﾄのF列の名前の抽出の仕方ですが､ ｻｲﾄｳ ｻﾌﾞﾛｳを=LEFT(A340,SEARCH(" ",A340,1)-1)という式で切り取って､ ｻｲﾄｳとしているのですが､ﾒﾓ帳などに貼り付けると ｻｲﾄｳだけでなく､ｻｲﾄｳ＋改行 となっているようなのです｡これが影響しているのでしょうか？どのようにすればよいでしょうか？お手数ですがよろしくお願いいたします｡
Mookさんのｺﾒﾝﾄ
最初の回答では金額に空欄があったら処理を終了するようにしていたのですが､ 1200行あたりに金額がないﾃﾞｰﾀがなかったでしょうか｡ 空白や改行があると一致しない可能性もありますので､F:L列を選択した状態で､空白や改行(Ctl+J)を置換してはどうでしょうか｡
Mookさんのｺﾒﾝﾄ
http://www.relief.jp/itnote/archives/000248.php
ﾘｾｰﾙ京都買取さんのｺﾒﾝﾄ
ありがとうございました｡ 私の勘違いで､空白や改行はそれらがない状態にしたとしてもかわりありませんでした｡ そこが原因ではなかったようです｡ そして､ご指摘どおり､金額が入っていなかったことが原因でした｡金額をとにかく0でも入るようにして､再度 詳細にﾃｽﾄしてみました｡ すると､301行目以降がすべて結果ｼｰﾄ2になってしまいました｡ 例 【まとめｼｰﾄ】 A,B,F,G?L ﾐﾔｻﾞｷ ｻﾄｼ,6000,ﾐﾔｻﾞｷ,空欄(G-Lすべて) 送金 角田 五郎,ﾂﾉﾀﾞｺﾞﾛｳ,5000,角田,炭田,隅田,清田,ﾂﾉﾀﾞ,ｽﾐﾀ ※まとめｼｰﾄのG?Lは空白だったり､0だったり､ｴﾗｰの時があります(その時はそのｾﾙだけ検索候補からは無視してもらいたいです) 【原本】 F,G,AI 宮崎敏,ﾐﾔｻﾞｷｻﾄｼ,6000 角田 五郎,ﾂﾉﾀﾞｺﾞﾛｳ,5000 ※原本にはｶﾅがはいっていないときもまれにあり ↑上記条件以外でもすべて､結果ｼｰﾄ2に移ってしまいます｡ もう少しだと思いますので､ぜひお教えいただけないでしょうか？ お手数ですがよろしくお願いいたします｡
Mookさんのｺﾒﾝﾄ
ｴﾗｰ判定はしていませんが､空白時は比較処理をしないようにしています｡ ｺｰﾄﾞの判定処理を調整しましたので､ｺﾋﾟｰし直して試してもらえるでしょうか｡ VBA のｽﾃｯﾌﾟ実行､ﾌﾞﾚｰｸ､変数の確認などをしようして､1ﾃﾞｰﾀの処理をﾄﾚｰｽしてもらえると､詳細な原因がわかると思うのですが､そのあたりは難しいでしょうか｡ http://www.asahi-net.or.jp/~ef2o-inue/vba_k/sub04_030.html
ﾘｾｰﾙ京都買取さんのｺﾒﾝﾄ
MOOKさんへ ありがとうございました｡ こちらで変数を調べるｽｷﾙがないため､恐れ入りますが､ hatenataro19 のｸﾞｰｸﾞﾙｱｶｳﾝﾄで同じｱｶｳﾝﾄのGMAILあてに 添付ﾌｧｲﾙでﾒｰﾙ送信いたしました｡ ﾊﾟｽﾜｰﾄﾞは 123abc123abc でﾛｸﾞｲﾝできます｡ GMAILﾛｸﾞｲﾝ後､ こちらのﾁｪｯｸをお願いできますでしょうか｡ 大変お手数をおかけしますがよろしくお願いいたします｡
Mookさんのｺﾒﾝﾄ
ﾌｧｲﾙを確認しましたので､老婆心ながら該当ﾒｰﾙを削除いたしました｡ 当該ｱｶｳﾝﾄのﾊﾟｽﾜｰﾄﾞを変更して置いてください｡
ﾘｾｰﾙ京都買取さんのｺﾒﾝﾄ
お気づかいありがとうございました｡ﾊﾟｽﾜｰﾄﾞを変更いたしました｡いつもお手数をおかけしております｡ご丁寧に感謝いたします｡
Mookさんのｺﾒﾝﾄ
>まとめｼｰﾄ B301の同じ行のF列､ G?L 列までの内容 の解釈がずれているようです｡ G?L列がないため､現在は処理されていませんが､F列も同等の対象ということでしょうか｡ そうであれば､ For c = 7 To 12 '// G?L 列 を For c = 6 To 12 '// F?L 列 に変更してみてください｡
ﾘｾｰﾙ京都買取さんのｺﾒﾝﾄ
MOOKさんへ！おかげさまでうまくいきました！ それと私の質問からは読み取れなかったと思い失礼なのですが 【1】301行目以降の結果も結果ｼｰﾄ､結果ｼｰﾄ2の｢C列に同じように固有ID｣をひっぱってきてほしいのです｡ これは追加依頼です｡ 【2】1?1500行目までの判定で､ﾋｯﾄしたとき､原本ｼｰﾄAT列のｽﾃｰﾀｽを元がどんな状態であるかにかかわらず､"入金準備"と替えていただけますでしょうか？ なお､【1】､特に【2】はあきらかに本最初の依頼内容とことなるため､ﾎﾟｲﾝﾄは元の設定より､50％以上増額することをお約束します｡お手数ですが､最後もう少しだけお付き合いください｡
Mookさんのｺﾒﾝﾄ
301 行名以降のﾁｪｯｸでは､結果ｼｰﾄ2に出力ということは 原本ｼｰﾄにﾏｯﾁしなかったという判断だと思うのですが､違っているでしょうか｡ 現在はいずれにせよ､まとめｼｰﾄの ID を結果ｼｰﾄに転記していますが､ ﾏｯﾁしない場合はどのIDを表示するのでしょう｡ 現在提示されている仕様では判断がつきませんので､詳細に仕様をいただけるでしょうか｡ 今提示されている説明では､金額と名前が両方一致しない場合は該当なし と判断する仕様と理解しています｡
Mookさんのｺﾒﾝﾄ
【2】は夜にでもｺｰﾄﾞを修正ｱｯﾌﾟします｡
ﾘｾｰﾙ京都買取さんのｺﾒﾝﾄ
MOOK再度お手数をおかけしてすみませんでした｡ 【1】ですが､誤っておりました｡ 302行目以降の成功時のみ､原本ｼｰﾄから該当すると思われる名前と同じ行のD列から 固有IDをひっぱって､結果ｼｰﾄのC列に出力をお願いします｡ 結果ｼｰﾄ2には一致しないのですから､引っ張りようがありませんでした｡すみません｡ 【2】の方もどうかよろしくお願いいたします｡ 感謝いたします｡ それと､今回は【1】【2】で終了ですが､本ﾏｸﾛを少し修正したい際には､ 再度はてなでMOOKさんあてにﾎﾟｲﾝﾄをともなって新規依頼したいのですが､よろしいでしょうか？ お手数ですが､こちらもご返信いただければ幸いです｡ よろしくお願いいたします｡
ﾘｾｰﾙ京都買取さんのｺﾒﾝﾄ
MOOKさんと言葉足らずでした｡申し訳ございません｡
Mookさんのｺﾒﾝﾄ
一応ｺﾒﾝﾄ頂いた内容に対応したつもりです｡ ﾏｸﾛの修正に関して ご指名のお話は光栄ですが､現在ﾎﾟｲﾝﾄ送信の機能は停止しているようです｡ また､はてなを長期に見ないこともありますので､連絡いただいても気が付かない可能性もあります｡ 質問に､回答者のﾘｸｴｽﾄという機能もあるようですので､こちらを利用されてはどうでしょうか｡
ﾘｾｰﾙ京都買取さんのｺﾒﾝﾄ
ありがとうございました！今後も宜しくお願いいたします｡ﾎﾟｲﾝﾄ送信機能って､便利だったのですが･･･｡ﾔﾌｰ等がやりはじめたのに､逆にはてなさんのﾒﾘｯﾄを消している気がいたしますね｡それでも増額いたします｡このたびは本当にありがとうございました｡今後もﾘｸｴｽﾄさせていただきます｡よろしくお願いいたします｡
関連質問
●質問をもっと探す●

0.人力検索はてなﾄｯﾌﾟ
8.このﾍﾟｰｼﾞを友達に紹介
9.このﾍﾟｰｼﾞの先頭へ
□対応機種一覧
□お問い合わせ
□ﾍﾙﾌﾟ/お知らせ
□ﾛｸﾞｲﾝ
□無料ﾕｰｻﾞｰ登録
□はてなﾄｯﾌﾟ