エクセル(VBA)での特殊な商品名検索

画像例を添付いたしますが、先方からの請求書を精査し一部商品の合計数及び合計金額を計算する必要があるらしく、手作業でコピペによる別シートへのリスト化を行っていた物を自動化できないかと言われとりかかっておりますが、形式が特殊な為、通常の検索が作用せず難航しております。

商品名で検索を行い、「紙」「コ」「ッ」「プ」のどれで検索しても該当の3行(商品名から金額まで)を抽出、別シートへ転記を行い、リスト化したいということです。
いちおう、商品名を検索の後、必ず入力されているはずの中央行にある数量又は金額を軸に範囲指定＆コピーができないかといろいろやっているのですが、うまく作用しておりません。有識者の方々にお知恵をお貸しいただければと思います。宜しくお願いします。


実際の商品名はかなり長い＆ほぼ固定ワードで検索する為、被り対策等は今回考えておりません。
また、横方向には広がっておらず、ひたすら下へ下へと改ページ換算で200ページ近くあります。

No.4 ベストアンサー 回答者： tatsumaru77 回答日時： 2021/01/31 19:44

下記URLにアップしました。

標準モジュールに登録してください。
https://ideone.com/yMncNW

検索結果の合計の行は、あなたの判断で適切と思われる行に設定してください。マクロは合計の行があることを意識しません。

この回答へのお礼

アップして頂いたコードを元に適切な運用、設定をさせて頂きます。
いろいろとありがとうございました。

お礼日時：2021/01/31 21:20

No.3 回答者： tatsumaru77 回答日時： 2021/01/31 14:46

１．マクロを格納するブックと検索対象シート及び検索結果を保存するシートは同一ブックであると

いう前提で良いでしょうか。
（各先から毎日請求書が送られてくるなら、その都度、マクロのあるブックにその請求書を取り込む必要があります。）

２．検索結果を保存するシートですが、添付図のレイアウトでよろしいでしょうか。
シート名は、「検索結果」とします。
このシートのH2のセルに検索対象のシート名を入力します。（緑のセル）
このシートのI2のセルに検索対象の文字を入力します。（緑のセル）

３．添付図の黄色の部分が、検索の都度、設定されるセルです。
初期化のマクロ実行時は、この領域のみクリアされます。
行数は可変ですが、この行の数は３の倍数になります。

４．添付図の赤の部分が合計が表示される個所です。このセルには、あなたが関数式を埋め込むことが
前提です。この合計欄の行は、特に何行目という指定はありませんが下方のほうにある前提です。
合計の文字は、A列にあってはいけません。添付図ではB列にしています。
合計の文字がA列にあると、その行がデータの最後の行と判断して、誤作動します。
上記の仕様で宜しいでしょうか。

５．部分一致で検索時（A列のみ検索）、複数のセルに一致したとしても、最初に一致したセルのみを採用対象とします。
①検索にマッチした行のC列及びF列にともにデータがあれば（空白でないならば）、その行を
該当商品の２行目と判断します。
②上記①が成立しない場合、検索にマッチした行の１つ前の行のC列及びF列にともにデータがあれば、その行を該当商品の２行目と判断します。（検索にマッチした行の１つ前の行が該当商品の2行目）
③上記①②が成立しない場合、検索にマッチした行の１つ後の行のC列及びF列にともにデータがあれば、その行を該当商品の２行目と判断します。（検索にマッチした行の１つ後の行が該当商品の2行目）
④上記①②③のいずれも成立しない場合は、検索エラーとします。
上記の仕様で宜しいでしょうか。

６．念のため確認ですが、
請求書のA列の商品名は１商品について３行全て、何らかの文字列がある前提で良いでしょうか。
以下のような例はない。
　　　A列
3行　紙
4行　コッ
5行　空白

上記例ではA5が空白になっていますが、このようなケースはないという前提です。
（"空白"という文字列があるのではなく、A5のセルにはなにも文字がないという意味です）

この回答へのお礼

1、同一ブック内で問題ございません。
2、似たような形式で作成を進めていましたので問題ございません。
3、これに関しては、抽出数がかなり多くなる場合がある為、下方面に余裕があるのが望ましいです。
4、問題ございません。
5、こちらの仕様を想定して作成しております。問題ございません。
6、基本的にないはずです。少なくとも直近ではないため、今回はこちらの想定をしていただかなくて問題ございません。

お礼日時：2021/01/31 19:32

No.2 回答者： ママチャリ 回答日時： 2021/01/31 10:54

右の方の空いている列を作業列として利用し、そこに表品名を表示する式を設定しておけば、あとは簡単に検索できると思うのですが、いかがでしょう。

例えば、Ｇ２セルに次のような式を入れて、下にフィルコピーします。
【Ｇ２セル】=IF(C2="","",A1&A2&A3)

この回答へのお礼

手軽に検索効率を上げられそうな為、こちらを利用した検索マクロも考えてみます。
ありがとうございました。

お礼日時：2021/01/31 12:16

No.1 回答者： tatsumaru77 回答日時： 2021/01/31 10:05

１．検索する文字はどのように指定するのですか。

1案）inputbox
2案）別に検索用シートを設け、その特定のセルに検索用文字を打ち込ませ、そのあとでマクロを実行
が考えられます。

２．請求書のシート名はなんですか。
３．「別シートへ転記を行い、リスト化したい」ということですが、
１）別シートのシート名はなんですか。
２）別シートのレイアウトは、どうなってますか。

４．一回の検索では、１つの商品のみ（３行分）しか検索できません。
合計数及び合計金額を算出するためには、何回か検索を繰り返す必要があります。
従って、以下のような操作が必要になります。
１）1回目の検索に先立ち、別シートを一旦クリア（見出しの行があるなら、それはクリアしない）
２）検索を行い、別シートに転記、２回目以降の検索結果の転記は、前の行の後に逐次、追記して行く。
３）検索が終わった為、合計の数、合計金額の算出。

上記の１）２）３）は、別々のマクロを作り、それを人間が個別に呼び出す必要があります。
１つのマクロで、上記１）２）３）を一気にはできません。
上記のような想定であってますか？

５．「紙」「コ」「ッ」「プ」のどれで検索してもということですが、
画像をみると、”コッ”の商品名に対しては、「コッ」で検索ではないでしょうか。・・・完全一致
それとも、「コ」又は「ッ」が一致すれば、一致なのでしょうか。・・・部分一致
部分一致の場合、一文字で検索すると、かなりヒットしてしまい、誤動作の原因となりかねないような気がしますが、それで良いのでしょうか。
部分一致は、例えば、
東京
京都
東仙台
があったとき、
東で検索すると、東京、東仙台にマッチします。
都で検索すると、東京、京都にマッチします。

この回答へのお礼

１．検索する文字はどのように指定するのですか。
基本的に2案で考えておりました。

２．請求書のシート名はなんですか。
先方がそれぞれ別の送り方をしてくるため決まった名前はありません。
いちおう同じ形式の他請求書に転用することが考えられる為、「ActiveSheet.Previous.Name」でひとつ前のシートに対象を取って検索をかける方式で考えておりました。

３．「別シートへ転記を行い、リスト化したい」ということですが、
元から空のシートに転記していたようなので、シート名及びレイアウトへの指定はありません。

４．一回の検索では、１つの商品のみ（３行分）しか検索できません。
合計数及び合計金額を算出するためには、何回か検索を繰り返す必要があります。
従って、以下のような操作が必要になります。
1番2番の動作まで想定しております。見出し行はどの道ある程度必要な為3行ほど余裕を持たせる方向で考えておりました。
3番は見出し行の特定セルに列合計を設定しておけばほぼ問題ないだろうと考えておりますので、今回のマクロ作成では想定しておりませんでした。紛らわしい書き方になってしまったことをお詫び致します。

５．「紙」「コ」「ッ」「プ」のどれで検索してもということですが、
画像をみると、”コッ”の商品名に対しては、「コッ」で検索ではないでしょうか。
部分検索でお願いいたします。本題にも書いております通り、実際の商品名は30文字以上になるようなかなり長い名称が扱われております。また略称がある程度決まっておりますので、被りによる誤作動は今回想定して頂かなくて問題ございません。

お礼日時：2021/01/31 12:08