人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

入金確認するための、行列関係のエクセルマクロをつくってください!



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


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



1354853314
●拡大する

●質問者: naranara19
●カテゴリ:コンピュータ 科学・統計資料
○ 状態 :終了
└ 回答数 : 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


naranara19さんのコメント
ぜひ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

naranara19さんのコメント
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

naranara19さんのコメント
ありがとうございました。 私の勘違いで、空白や改行はそれらがない状態にしたとしてもかわりありませんでした。 そこが原因ではなかったようです。 そして、ご指摘どおり、金額が入っていなかったことが原因でした。金額をとにかく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

naranara19さんのコメント
MOOKさんへ ありがとうございました。 こちらで変数を調べるスキルがないため、恐れ入りますが、 hatenataro19 のグーグルアカウントで同じアカウントのGMAILあてに 添付ファイルでメール送信いたしました。 パスワードは 123abc123abc でログインできます。 GMAILログイン後、 こちらのチェックをお願いできますでしょうか。 大変お手数をおかけしますがよろしくお願いいたします。

Mookさんのコメント
ファイルを確認しましたので、老婆心ながら該当メールを削除いたしました。 当該アカウントのパスワードを変更して置いてください。

naranara19さんのコメント
お気づかいありがとうございました。パスワードを変更いたしました。いつもお手数をおかけしております。ご丁寧に感謝いたします。

Mookさんのコメント
>まとめシート B301の同じ行のF列、 G?L 列までの内容 の解釈がずれているようです。 G?L列がないため、現在は処理されていませんが、F列も同等の対象ということでしょうか。 そうであれば、 For c = 7 To 12 '// G?L 列 を For c = 6 To 12 '// F?L 列 に変更してみてください。

naranara19さんのコメント
MOOKさんへ!おかげさまでうまくいきました! それと私の質問からは読み取れなかったと思い失礼なのですが 【1】301行目以降の結果も結果シート、結果シート2の「C列に同じように固有ID」をひっぱってきてほしいのです。 これは追加依頼です。 【2】1?1500行目までの判定で、ヒットしたとき、原本シートAT列のステータスを元がどんな状態であるかにかかわらず、"入金準備"と替えていただけますでしょうか? なお、【1】、特に【2】はあきらかに本最初の依頼内容とことなるため、ポイントは元の設定より、50%以上増額することをお約束します。お手数ですが、最後もう少しだけお付き合いください。

Mookさんのコメント
301 行名以降のチェックでは、結果シート2に出力ということは 原本シートにマッチしなかったという判断だと思うのですが、違っているでしょうか。 現在はいずれにせよ、まとめシートの ID を結果シートに転記していますが、 マッチしない場合はどのIDを表示するのでしょう。 現在提示されている仕様では判断がつきませんので、詳細に仕様をいただけるでしょうか。 今提示されている説明では、金額と名前が両方一致しない場合は該当なし と判断する仕様と理解しています。

Mookさんのコメント
【2】は夜にでもコードを修正アップします。

naranara19さんのコメント
MOOK再度お手数をおかけしてすみませんでした。 【1】ですが、誤っておりました。 302行目以降の成功時のみ、原本シートから該当すると思われる名前と同じ行のD列から 固有IDをひっぱって、結果シートのC列に出力をお願いします。 結果シート2には一致しないのですから、引っ張りようがありませんでした。すみません。 【2】の方もどうかよろしくお願いいたします。 感謝いたします。 それと、今回は【1】【2】で終了ですが、本マクロを少し修正したい際には、 再度はてなでMOOKさんあてにポイントをともなって新規依頼したいのですが、よろしいでしょうか? お手数ですが、こちらもご返信いただければ幸いです。 よろしくお願いいたします。

naranara19さんのコメント
MOOKさんと言葉足らずでした。申し訳ございません。

Mookさんのコメント
一応コメント頂いた内容に対応したつもりです。 マクロの修正に関して ご指名のお話は光栄ですが、現在ポイント送信の機能は停止しているようです。 また、はてなを長期に見ないこともありますので、連絡いただいても気が付かない可能性もあります。 質問に、回答者のリクエストという機能もあるようですので、こちらを利用されてはどうでしょうか。

naranara19さんのコメント
ありがとうございました!今後も宜しくお願いいたします。ポイント送信機能って、便利だったのですが・・・。ヤフー等がやりはじめたのに、逆にはてなさんのメリットを消している気がいたしますね。それでも増額いたします。このたびは本当にありがとうございました。今後もリクエストさせていただきます。よろしくお願いいたします。
関連質問

●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ