EXCELでマクロを使った検索・一覧抽出の方法を教えてください。

質問

社内文章の表を作成しているのですが、あまりにもデータ量が多く、関数での算出が難しくなってきたので、
マクロを導入したく思っているのですが、検索しても当てはまるものが無く、困っています。。。

　｜　A　　｜　B　　｜　C　　｜　D　　｜　E　　｜　F　　｜　G　　｜　H　　｜　I　　｜　J　　｜　K　　｜
１｜名称1｜名称2｜数量　｜種類1｜数量1｜種類2｜数量2｜種類3｜数量3｜条件1｜条件2｜・・・
２｜　　　　｜　　　　｜　　　　｜　　　　｜　　　　｜　　　　｜　　　　｜　　　　｜　　　　｜　(1)　｜　(1)　｜・・・
３｜*****｜*****｜*****｜***** ｜***** ｜*****｜*****｜*****｜*****｜ (2)　 ｜ (2)　 ｜・・・

上記のような表があり、今までは関数にて、(1)のセルに検索条件を入力し、(2)のセルに「=SUMIF(D2:H2,A2(1),E2:I3)という式を入れ計算していました。

種類1～種類3に入力されている情報と(1)が合致したら、数量1～数量3を合計した値を(2)へ記入したいのです。

この検索条件が60項目あり、行数も2000行を超えているのですが、、全てのセルに関数を入れている状態です。

回答No.5

-yellowtail-

#2さんへのお礼欄を読むと、各行ごとに合計をとれば良いというように見えます。

なので、まず3行目だけ考え
もしD3がJ2と同じなら変数(合計)にE3の数量を足す
もしF3がJ2と同じなら変数(合計)にG3の数量を足す
もしH3がJ2と同じなら変数(合計)にI3の数量を足す
と言う処理を行います。
で、この変数(合計)の値をJ3に書きこみます。

たとえば…
If Cells(3, 4) = Cells(2, 10) Then a = a + Cells(3, 5)
こんな感じとか。

後は、J列→xなどの変数にしてFor～Loopで60列
3行目→yなどの変数にして同じくForで2000列回せばOKだと思います。

aを毎回リセットするのを忘れずに！（a=0で良いかと）

こんな感じでマクロを書いてみて下さい！

回答No.4

135ok

No.3です。
私の回答で良ければ、次のような方法があるかと。
一例ですので、ご参考程度で。
添付資料を参照ください。

目的は、条件に合った値を表から検索し値を表示する。として
表は、添付資料の通り作成し
Ｃ２　=IF(B2="","",IF(COUNTIF($E:$F,$B2),VLOOKUP($B2,$E:$F,2,0),IF(COUNTIF($G:$H,$B2),VLOOKUP($B2,$G:$H,2,0),VLOOKUP($B2,$I:$J,2,0))))
　　　*Ｂ列が空欄の時は空欄。以外は、条件の値を種類１から３の中から検索し、値を表示。条件が無い場合はエラー。
　　　*下にコピー

これで、関数の数は６０個になるので作業が早くなると思います。
指定の書式で検索しなければいけないのであれば、的外れの回答です。

お礼

ありがとうございます。
ただ、表のフォーマット変更は残念ながら社内共通フォーマットの為、出来ないので、断念させていただきます。

回答No.3

135ok

違っている気もしますがとりあえず。
的外れの場合は、スルーしてください。

Ｊ３　=IF(J$2="","",IF(J$2=$D3,$E3,IF(J$2=$F3,$G3,IF(J$2=$H3,$I3,""))))
　　　*Ｊ２に入力した値に対し、各種類の中から値に一致するものを選び、右の値を表示する。
　　　*表示させたい範囲にコピー

お礼

回答ありがとうございます。
したい事はあっています。
質問が悪くすみません。

式の形状は違いましたが、このような式を
60列×2000列＝12000セルに入力している為、データが重く困っています。。。
なので、これをマクロで実行するなどして、軽くする方法はないかなと。。。

回答No.2

imogasi

質問の表現がすっきりしないため、誤解箇所があるかもしれないが（下記最後に疑問点を書いた)
例データ
A-E列　B,D列は他のデータ（＝数量）が入っている場合（下記では数量データ略）
X Y Z
a aa bb
s a s
d f g
h i a
r r g
a v a
A,C,E列のaを検索できる。
こういう３列のどれかにaが見つかれば、該当有りとする問題か？
ーーー
そうならVBA云々を言うなら、検索操作のマクロの記録を採って勉強を始めたらどうです。
Range("A:A,C:C,E:E").Select
Selection.Find(What:="a", ・・・
のようにすれば離れた列も１度に検索できる。
そのときある行でaが見つかったら、他の列（A、C,E)は見ず、次の行の検索に行くようにする。
このFindメソッドはFindNextと対で、検索終了のタイミングや見つからない場合の捉え方が、初心者には難しいよ。
見つかったらその行の集計したい列を足しこめば良い。
ーーー
上記aに当たる６０語句について繰り返すのも良いが、処理時間が何分か、かかるかも。
６０語句はどうしてシートに出されるのか（手入力か）.
一斉にやるにはシートのどこかに６０語句があるほうが良いが。
ーー
抜き出しはデーターフィルターフィルタオプションの設定で出来るのではないか。それをVBAにする。マクロの記録を採って見ればコードの骨格は判る。
条件は
X Y Z
a - -
- a -
- - a
のようにする。－は空白で左詰防止のため入れたもので実際は空白。
結果
X Y Z
a aa bb
s a s
h i a
a v a
のようになる。各行にaが最低１つ現れる。
ーーー
本格的にやるには、ACCESSのSQLか、その系統のエクセルのMSクエリが
良いと思う。SQL文でOR条件を書けるので、簡便だか、そこにいたる勉強が大変。
＝＝＝＝＝＝＝＝
質問表現について
=SUMIF(D2:H2,A2(1),E2:I3)
の（１）とはなに？
A2セルのこと？だったら（１）は書かないこと。ややこしい。
＞、(1)のセルに検索条件を入力し
具体的にA2とか書いたら。
しかし、６０語句を処理するときは、A2に順次語句を入れたのか
関数では１箇所をA2を見るが、A2は時どきに変化させ、そのときどき
の結果を別行に出していくのは出来ないはず。この辺の今やっている
内容が、質問に説明されていない。
＞検索条件が60項目あり
この６０項目＝６０語句（私の上記例でa)はどうして決まり、何処にあったのか。都度A2セルに入れていたのか？
＞あまりにもデータ量が多く、
＞行数も2000行を超えているのですが
とイメージ的に合わないが。言うほどの量でもないと思うが。

補足

説明が悪く申し訳ありません。。。

＞本格的にやるには、ACCESSのSQLか、その系統のエクセルのMSクエリが
＞良いと思う。SQL文でOR条件を書けるので、簡便だか、そこにいたる勉強が大変。
⇒ACCESSですか。。。別途検討が必要ですね。

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

すみません、根本的に質問内容がおかしいですね。。。
検索条件はJ2,K2,L2...と60項目あり、下記表のように式を入力しています。
これが、検索条件60で2000行ですので、120000セルに式が入っている状態です。
　｜　　　 　　　　J　　　　　　　　｜　　　　　　　　K　　　　　　　｜・・・
１｜　　　　条件1(表題)　　　　｜　　　　条件2(表題)　　　　｜
２｜　　 検索条件(手入力)　　｜　　 検索条件(手入力)　　｜
３｜「SUMIF(D2:H2,J2,E2:I2)」｜「SUMIF(D2:H2,K2,E2:I2)」｜
４｜「SUMIF(D3:H3,J2,E3:I3)」｜「SUMIF(D3:H3,K2,E3:I3)」｜
・
・
・
検索したい内容は、J3セルで説明すると、
D3がJ2と一致すれば、E3の値をJ3へ表示。
F3がJ2と一致すれば、G3の値をJ3へ表示。
H3がJ2と一致すれば、I3の値をJ3へ表示。
という風に検索したいのです。

回答No.1

nobby-frog

マクロを組むよりも、アクセスにデータを流し込んだほうが
手っ取り早い印象を受けます。

エクセルのデータからなら、簡単にインポートできますし。