Excel VBA

Question

高齢者です。
一念発起してExcelでデータベースを作っています。

A列からM列までデータが入っています。
J列のメモのデータを検索して、部分一致で同じ言葉が複数行あったら
見つかった行のA列からM列までをすべてコピーし、新しく作ったシートに貼り付けたいのです。
Webで検索したりしていますが、思い通りの結果になるコードが見つかりません。
お手数ですが、今後の勉強のためにコードの説明もしていただくと助かります。

どうぞよろしくお願いいたします。
尚、Excelは2016で5000行くらいのデータです。

tom04 · Accepted Answer

こんにちは！

No.2さんの補足を読ませていただいて・・・
元データはSheet1にあり、Sheet2に表示するとします。
標準モジュールです。

Sub Sample1()
　Dim myStr As String, wS As Worksheet
　Dim lastRow As Long
　　Set wS = Worksheets("Sheet2")
　　　myStr = Application.InputBox("検索文字を入力")
　　　　wS.Range("A:M").ClearContents
　　　　With Worksheets("Sheet1")
　　　　　lastRow = .Cells(Rows.Count, "J").End(xlUp).Row
　　　　　.Range("J:J").AutoFilter field:=1, Criteria1:="*" & myStr & "*"
　　　　　　If .Cells(Rows.Count, "A").End(xlUp).Row > 1 Then
　　　　　　　Range(.Cells(1, "A"), .Cells(lastRow, "M")).SpecialCells(xlCellTypeVisible).Copy wS.Range("A1")
　　　　　　Else
　　　　　　　MsgBox "該当データなし"
　　　　　　End If
　　　　　.AutoFilterMode = False
　　　　End With
　　　wS.Activate
　　　MsgBox "完了"
End Sub

こんな感じではどうでしょうか？m(_ _)m

WindFaller · Answer

#7の回答者です。

>なんだか難しい言葉ばかりでよくわかりません。
>私なりにもう少しExcelを勉強しようと思います。

Excelが登場して、かれこれ40年になります。事実上、Microsoft がIBMと契約が切れる少し前頃から、Lotus123が絶好調の時、1980年後半から、その存在は知られていますが、実は、1980年の始め頃、Mac用のExcelがあったし、すでに、Multiplan というDOSの表計算にExcelのDNAが埋め込まれています。その時に、データベース機能は取り入れられていたわけです。

それぞれの訳のわからない単語が、その時々を彩るExcelの歴史の一片なのです。全部のデータベース機能は学ぶ必要もないけれども、Excelの標準的な機能は、メジャーであろうが、なかろうが、学ぶことが可能な限りは、身につけておいたほうが良いとは思います。

その中でも、#4さんの示されたAdvancedFilter（フィルターオプション） はその初期から存在していた機能のひとつです。マクロよりも、まず、手作業でできるようにしてから、マクロを当てはめてみたらいかがでしょうか。

WindFaller · Answer

ちょっと割り込ませていただきます。

>InputBoxで思いつく言葉を入力し、
>その言葉と部分一致しているものを抽出し
>その行を別のシートに貼り付けられたら便利だなと思っています。

それでしたら、もうすでに#4さんの回答が出ていますが、私も、AdvancedFilter で十分だと思います。表計算上の名称は「フィルターオプション」で、マクロで行うというのが、ミソなのです。
私は、Worksheet_Change イベントを使うことが多いのですが。

ただ、別途、Excelはデータベースもかねています。おそらく、初期の製作者の悲願だったのだろうと思います。Excelの表計算上の構造に、そのデータベースの要素が残っているのです。VBAマクロを学習する中で、ADODBなど、避けては通れないので、自然に覚えてしまうというものなのです。

Access は、後発ですし、かなり仕様が違うので、別会社で作ったということは想像つきます。それと、私には、Accessには、経験的にしか分からない、予約語を始め、不可解なエラーをいちいち探さなくてはならない面倒さがあります。

Excelには、単品で購入しても、OLAPやら、データベースJet などがついています。単に、ほとんどの人がマニュアルもなくて使いこなせないだけではないでしょうか。それに、インターフェィスのODBC がついています。かつては、Excelを購入すると「Microsoft Query」というマニュアルがついてきました。

それで、ADODBやDAOというならいざ知らず、このODBC のQueryを簡単に使いこなせる人間が、掲示板の回答者に今もいるということも、驚きでしたね。

書籍では、安価で、エクスメディアさんが、唯一、Excelのデータベースの使い方の書籍を出していましたが、出版社が倒産してしまいました。民間で多く出ているのは、Excelをフロントエンドにするということで、MYSQLなどが知られています。

それよりも小規模というよりも、まったく別の仕組みで、かつてのVLOOK等の関数の不便さからAdvancedFilter というデータベース機能を、かなり初期の頃から搭載されました。また、別の方にも紹介しましたが、ODBCによる、パラメータクエリは、なかなか便利なものです。ただ、今は、PCの仕様も格段と上がり、VLOOKUPでも何の問題もない段階に入っても、AdvancedFilter は健在だとは言えます。

私自身、表計算とカード型データベースというのは、かつての初期のMultiplan と DataBox （タイトルの漢字がつかない頃）の組み合わせで何年も使ってきましたから、その便利度は測り知れません。だから、FileMaker は何度も食指が動きましたが、ついにそれだけの出費には相成りませんでした。

lv4u · Answer

＞＞Excelが表計算ソフトだということは十分理解しております。
おっしゃるようにデータベースみたいな使い方ができればと思い、挑戦中です。

パソコンがこんなに広まる以前のWindowsじゃあない真っ黒画面のDOS時代、「カード型データベース」という素人でも、お手軽に使えるソフトが市場に広まったことがありました。
桐、The Card、漢字データボックス 、パーソナルパール・・・。
これらのソフトは、その後のリレーショナル・データベース全盛時代に、機能競争に敗れたりで市場から消えてしまいました。
でも、リレーショナル機能とか、開発機能を加えることでいまでも使われ続けているのが、FileMakerというソフトです。
現在は、初心者向けのカード型データベース機能だけじゃあなく、Ｗｅｂ対応、スマートフォン、タブレット向けアプリケーションの開発機能までも可能になっているようです。

フリーソフトじゃあなく３万６千円くらいしますが、たぶんやりたいことは、Excelよりも手軽に実現できると思いますよ。

「苦労してなにかをやりとげる」という目的であれば、Excelでもいいのですけど、「できるだけ楽して目的を果たしたい。あわよくば、Ｗｅｂサイトのソフトも作れるかも？」なんてのもありうるFileMakerを選ぶってのもありかな？なんておもいます。

ママチャリ · Answer

この要件の場合、AdvancedFilterメソッドの使用が最適と考えます。
 添付画像を見て頂けると3つのシートがあることが分かって頂けると思います。
まず、「データベース」シートですが、1行目が見出し行になっていて、J1セルの見出しは「メモ」となっている前提です。このシートは既に作成済みですよね？
次に、添付画像の通りに「条件指定」シートを作成してください。さらに「抽出結果」シートも必要になりますが、これは空のシートでOKです。
シートの準備が出来たら次のVBAを実行してみて下さい。

Sub Macro1()
    Sheets("データベース").Columns("A:M").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Sheets("条件指定").Range("A1:A2"), _
        CopyToRange:=Sheets("抽出結果").Range("A1:M1"), _
        Unique:=False
End Sub

AdvancedFilterメソッド一発で事が完了してしまうため、コードの説明は省略させていただきます。
AdvancedFilterはVBAだけではなく、通常のExcelのマニュアル操作でも使用可能です（「データ」タブ－「並べ替えとフィルター」の「詳細設定」）。どんなことが出来るのかをマニュアル操作で確認して頂ければ、いろいろ応用が利くことが分かって頂けると思います。

tatsu99 · Answer

補足要求です。
１）データの入っているシートのシート名は何でしょうか。（全角／半角、大文字／小文字についても正確に提示ください）
２）上記シートの1行目は見出し行で２行目以降からがデータ行の前提で良いですか？
２）新しく作ったシートのシート名は何でしょうか。（検索結果表示用のシート）（全角／半角、大文字／小文字についても正確に提示ください）
３）InputBoxに検索文字列を指定するのではなく、新しく作ったシートのO2（添付図の黄色の部分）に検索文字列を指定し、
マッチした行を緑の部分に表示するのはいかがでしょうか。（InputBoxよりは使い勝手がよいかと思います）

neKo_deux · Answer

> 部分一致で同じ言葉が複数行あったら

そのJ列に入力されているメモと「何が」一致するかどうかを調べるの？

J列の内容が、
「なごや」は部分一致は無い。
「とうきょう」は「う」の言葉が部分一致してる。
とかって話？

例えば、I列に入ってる言葉が、J列のメモの中に含まれる、部分一致するか？って話なら、新規のN列に、
N1:=NOT(ISERR(FIND(I1, J1)))
とかで、含まれるならTRUE、含まれないならFALSEって結果を表示できます。

その上で、新しいシートに全体をコピー、値として貼り付け。
N列で並べ替えして、N列がFALSEの行を削除、N列を削除。
とか、手動で処理しちゃダメなんでしょうか？

lv4u · Answer

>>一念発起してExcelでデータベースを作っています。

Excelでは、データベースは作れません。Excelは表計算ソフトです。
データベースっぽいものは作れますけど、本来のデータベースが持っている機能は全然使えません。

>>Webで検索したりしていますが、思い通りの結果になるコードが見つかりません。

Web検索でも使えそうなものは見つかると思いますが、なかなかピッタリくるものは無いかもしれません。
この場合、Wxcel VBA逆引き大全なんてタイトルの書籍を購入しましょう。
サンプル例文が沢山掲載されています。

＞＞J列のメモのデータを検索して、部分一致で同じ言葉が複数行あったら・・・

まともなデータベースを使うと、複雑な検索条件で抽出したり、結果を並べ替えたりとか、かなり自由なデータ操作が可能です。
ただ、初心者には、まともなデータベースの勉強は、難しすぎるかもしれませんね・・・。

Excel VBA

こんにちは！

#7の回答者です。

ちょっと割り込ませていただきます。

＞＞Excelが表計算ソフトだということは十分理解しております。

この要件の場合、AdvancedFilterメソッドの使用が最適と考えます。

補足要求です。

> 部分一致で同じ言葉が複数行あったら

>>一念発起してExcelでデータベースを作っています。

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング