プログラムの作り方

質問

まったくの素人です。
1列にあるデータをキーにして情報の抽出をしたいのですが
うまくコードが書けません。
何卒、助勢頂ければ幸いです。
よろしくお願いいたします。
具体的な内容)
B列　C列
x　　11
・・適当な不要なデータが数行
y 　(2A)
・・適当な不要なデータが数行
x　　12
・・適当な不要なデータが数行
y 　(44)
・・適当な不要なデータが数行
x　　39
・・適当な不要なデータが数行
y 　(7)
・・適当な不要なデータが数行
から、xのデータとyのデータの表を作りたい。
ただし、yのデータ()内はB列のyの次の行。
できれば、抽出したデータはシート2に並べたい。
Sub macro()
Dim a As String
Dim b As Variant
Dim c As range
Dim i As Integer
a = x
b = "y"
i = 1
Worksheets("sheet1").range("B1").Activate
For i = 1 To 5000
With ActiveCell
If .Value = a Then
ActiveCell.Offset(0, 1).Copy
Worksheets("sheet2").Select
Cells(i, 1).Select
ActiveSheet.Paste
Sheets("sheet1").Select
Else
ActiveCell.Offset(1, 0).Activate
End If
If .Value = b Then
ActiveCell.Offset(1, 0).Copy
Worksheets("sheet2").Select
Cells(i, 2).Select
ActiveSheet.Paste
Sheets("sheet1").Select
Else
ActiveCell.Offset(1, 0).Activate
End If
End With
Next
End Sub

回答No.7

o_chi_chi · ベストアンサー

#6です。
少し勘違いしてました。
Sheet2はAにx、Bにyですね。
--
Sub Macro1()
Dim x As String '変数の定義
Dim y As String
Dim i As Long
Dim j As Long
j = 0
x = 10 'キーワードの割り当て
y = "AA"
With Worksheets("Sheet1")
For i = 1 To 5000 'ループ検索
Select Case True
Case .Cells(i, 2).Value = x
j = j + 1
Worksheets("Sheet2").Cells(j, 1).Value = .Cells(i, 3).Value
Case .Cells(i, 2).Value = y
Worksheets("Sheet2").Cells(j, 2).Value = .Cells(i + 1, 2).Value
Case Else
End Select
Next i
End With
End Sub

お礼

ご回答ありがとうございます。

動作確認しました。
完璧でした。。
ありがとうございます。

小生のプログラムとは異なりすっきりしており
処理時間が断然早くなりました。びっくりしました。

本当にありがとうございます。
プログラムを見ると、なるほどこれでいいんだなと
動作は分かる（つもり？）のですが、
自分で作る時は全く思いつきませんでした。
もっと、色々見てさらに勉強していきます。

回答No.6

o_chi_chi

横から失礼します。
jが条件yの時しか増加させてないからではないでしょうか。
質問者さんのPGを整理すると下記のようになります。
---
Sub Macro1()
Dim x As String '変数の定義
Dim y As String
Dim i As Long
Dim j As Long
j = 0
x = "10" 'キーワードの割り当て
y = "AA"
With Worksheets("Sheet1")
For i = 1 To 5000 'ループ検索
Select Case True
Case .Cells(i, 2).Value = x
j = j + 1
Worksheets("Sheet2").Cells(j, 1).Value = .Cells(i, 3).Value
Case .Cells(i, 2).Value = y
j = j + 1
Worksheets("Sheet2").Cells(j, 1).Value = .Cells(i + 1, 2).Value
Case Else
End Select
Next i
End With
End Sub

回答No.5

noname#245936

No.1-4の者です。

いま、ふと、Excelだけで根性でできる簡単な方法を思いつきました。
思考の理屈は先のSQLの考え方と同じです。

１．B列・C列の隣、D列・E列に各行の「次の行」をコピーペースト
　　します。Excelなので簡単だと思います。
２．F列に、B列を審査し、XならB列、YならD列を取る関数を埋めます。
　　同様に、G列に、B列を審査し、XならC列、YならE列を取るように
　　します。IF関数でできるでしょう。
３．B列に対しExcel機能のフィルタリングをかけます。
４．欲しいデータが、F列とG列にできています。

以上でどうでしょうか？

お礼

色々と考えていただきましてありがとうございます。

またまた、考えてみます。

ちなみに、ステップインで見ても小生の書いたコードは
順番に動いているような感じなのですが、
なぜ、二つ目のif文の内容だけ結果に出てくるのか
ご教示いただけましたらよろしくお願いいたします。
結構時間かけて考えてもこの疑問が気になってしまい。。
未練かもしれませんが、アドバイスをお願いいたします。

補足

>なぜ、二つ目のif文の内容だけ結果に出てくるのか
>ご教示いただけましたらよろしくお願いいたします。
>結構時間かけて考えてもこの疑問が気になってしまい。。
>未練かもしれませんが、アドバイスをお願いいたします。

たくさんのアドバイスありがとうございます。
色々技術力不足を痛感しました。
とりあえず、プログラムは動き上記の問題もクリアできました。
二つ目のif文のelseを削除すれば所望の動きが実現できました。
これからいままで頂いたアドバイスや、他のプログラムに
挑んでいきたいと思います。
ありがとうございました。

回答No.4

noname#245936

No.1,2,3の者です。

当方、Accessでやってみて、できました。
（Accessは、サーバ製品よりある意味難しいですね…）

SQLなどあまり考えずにやる方法があります。

１．テーブルを作成する。
　　先に述べたテーブルを作成します。
　　RowNum、Coodinate、Pointです。
２．Excelのデータを放り込む。
　　インポートウィザードなどを利用します。
３．１つめのクエリを作成します。
　　「選択クエリ」で上記１のテーブル、全項目を参照し、
　　一番右に、NextRowNumと言う名前のRowNum + 1のフィールドを
　　作成し、保存します。
４．２つめのクエリを作成します。
　　１のテーブルと３のクエリを呼び出し、リレーションシップを
　　かけます。
　　１のテーブルのNextRowNumに対して、３のクエリのRowNumを
　　くっつけ、全フィールドを出力するようにします。
　　で、抽出条件として、１つめのテーブルのCoodinateが
　　XかYのもののみに絞り込みます。
　　これを保存します。
５．３つめのクエリを作成します。
　　４のクエリのCoodinateがXのときはテーブルのCoodinateを
　　そのまま表示。４のクエリのCoodinateがYのときはクエリの
　　Coodinate（次の行）を表示。
　　IIFと言う関数でできました。
　　あと、RowNumでソートを忘れずにかけておきます。

以上で、完成です。

利用手順は、
１．Excelからテーブルへデータをインポートする。
２．上記５のクエリをKickするとすでに結果ができているので
　　ExcelなりTextなりに出力します。

以上でいかがでしょうか？
ちょっと難しいですが、Accessの基本機能で、ほとんどの操作を
マウス操作で作れます。頑張ってみてください。

ちなみに、私のNo.3のメッセージにバグがありました。（笑）
訂正しておきます。
誤：On tDataDump1.RowNum = tDataDump2.RowNum
正：On tDataDump1.NextRowNum = tDataDump2.RowNum

ま、これで難しすぎれば…Excelマクロも手ではあります。
が、データベースを使うと、大量のデータをたやすく扱えます。

回答No.3

noname#245936

No.1/2の者です。

…なんとなし、判ってきました。
これって、工業用機械のヘッド位置など制御座標指示系のダンプ
データ（データ羅列）を重複データを除いたりしつつ、データ量を
最適化したいと伺えますがよろしいでしょうか？

で、この情報を一瞬で出せる仕組みが欲しいということですね？

多分なんですが、貴方の欲しい情報はExcelマクロを組むより
Accessなどデータベースを使うのが手っ取り早いと思います。
（Excelはセル座標系の制御がとても面倒なので、データベースで
　一瞬でデータ集合を取り扱う方が早いです）

理屈的には、DBに放り込んで、自己結合型のSQLを発行し、
データを一撃で出力する仕組みです。

１．まず、Accessなどでテーブルを作ります。
　　Table名をTDataDumpと仮にしまして。
　　フィールドを仮に　　
　　１．RowNum (長整数型：主キー）
　　　　ExcelのA列相当。順序を保持。
　　２．Coordinate（文字型(1））
　　　　ExcelのB列相当。「X」「Y」を入れる。
　　３．Point（長整数型か小数があれば10進型など）
　　　　ExcelのC列相当。座標系を入れる。
　　とします。
２．これに一度データをExcelなどからインポートします。
　　A列がない場合はExcelで先に作成をしてから入れます。
３．これに対して、クエリ（SQL）を作成します。
４．そのSQLを実行すると、欲しいデータがすぐに取り出せるように
　　します。

なお、SQLの考え方はこちらです。
※当方、SQL Serverの構文で書きますので、Accessではちょっと
　方言があり、エラーになるかもしれません。

１．全部のデータを昇順でとるには。
　　SELECT RowNum,Coordinate,Point From TDataDump
　　Order By RowNum
　　になります。
２．Y列のみ、次の行が欲しい場合がありますので、
　　SELECT RowNum,Coordinate,Point,RowNum + 1 AS NextRowNum
　　From TDataDump Order By RowNum
　　と加工。D列相当に次の行番号を作成しておきます。
３．Y列の場合、次のデータが欲しいのでNextRowNumに対し、
　　データを再結合します。
　　SELECT
tDataDump1.RowNum, 　　--該当行番号
tDataDump1.Coordinate, --該当行のXY
tDataDump1.Point, --該当行の座標
tDataDump1.RowNum + 1 AS NextRowNum,--次の行番号
tDataDump2.RowNum AS NextRowNum2, --結合された次行番号
tDataDump2.Coordinate AS NextCoordinate2, -- 次行XY
tDataDump2.Point AS NextPoint2 -- 次行座標
　　From TDataDump AS tDataDump1
　　Left Join TDataDump AS tDataDump2
On tDataDump1.RowNum = tDataDump2.RowNum
Order By RowNum
これにXとYでWhere縛りをつけ、PickUpしたいところだけ取り出す
SQLに整形すれば完了です。
…実際書いてみましたが…長いSQLになるので貴方にとって正しい
回答かどうか判らないので割愛します。（笑）

何にせよ、
Excelでデータ整形 -> Accessへインポート -> SQLのKick ->
結果を別で保存という形にすれば貴方のやりたいことは一撃で
できると思います。

お礼

ご回答ありがとうございます。

すごいですね。
専門の方でしたか。
生産された製品のデータ値を吸い取り
そのデータ値をまとめ分布を取りたいと
考えてます。

上記に記載いただきました内容専門用語すぎて
小生には理解できませんでした。
(すみません。アクセスの知識もほとんど無く、ほとんど使わないので。)

回答No.2

noname#245936

No.1の者です。

気になるのは、
>必要に迫られているので
の程度が、「そのデータ自身が欲しい」のか。
はたまた、「プログラム自信が欲しい」のか。
これによって回答が違うと思います。

また、
>フィルタすると抽出できるデータ(ｘ)とその後のデータ(ｙ)の
>相関が取れなくなることが問題になり悩んでいます。
の点については。

>yのデータ()内はB列のyの次の行。
の意味がよくわかりません…。

なんとなく、データだけならば。
１．A列に、Row Numberをつける。１から順に連番を振る。
　　（データ順序を維持するため）
２．B列でフィルタをかけ、"X"で絞る。
　　結果をコピーし、別シートにペースト。
　　このとき、右クリックで「データのみ」にする。
３．同様にB列でフィルタをかけ、"Y"で絞る。
　　結果をコピーし、またまた別シートにペースト。
　　このとき、右クリックで「データのみ」にする。
４．XとYのシートが別々でできるので、そこから考える。
…ってのはだめですか？

お礼

またまた、ご回答ありがとうございます。
できることならプログラムが欲しいですが、頑張ってみます。

>yのデータ()内はB列のyの次の行。
>の意味がよくわかりません…。
上記ついて、失礼しました。
小生の書き方がよくなかったです。
データ内のキーワードで検索し、そのキーワードをx,yに位置づけて
います。
ただし、x,yともに全体の中には同じ値がたくさんあります。
(この考え方も正しいのか少し疑問ですが・・・。)
何かを固定しないと先に進めないと思ったので・・。

で、yのデータがキーワードyの次の行というのは
キーワードyが入れられているB列と同じであるということを表現しました。xはキーワード検索しているB列と異なりC列にあるのでB列でキーワード検索し、該当した場合にはその行のC列を見なさいとoffsetプロパティで実現しようとしました。

今回のプログラムを作るにあたって小生の基本的な考えはB列にある二つのキーワードx,yをB列の上から順番に探しxが適合すればそのセルの右側を、yが適合すればそのセルの下側のデータをそれぞれ順番にシート2に転記すれば良いと考えました。

再回答いただきました中で“１．A列に、Row Numberをつける。１から順に連番を振る。”この連番を振るようにすることに気がつきません
でした。ぜひ参考にさせていただきます。
３．の“Ｙ”でフィルタをかけるとＹのキーワードが入力されているセルの下のセルのデータがついてこないのでややこしくなってしまいます。
これが解決できればなんとか見えてきそうな予感がするのですが・・。
コードにたくさん間違いがあったり、色々と参考を頂けましたので
もう少し考えてみます。ありがとうございます。

補足

以下のようにプログラムを修正してみました。
if文をそれぞれ個別に動作させると所望の動作になるのですが
現状のプログラムでは2つあるif文の後側の処理しか表示されず
煮詰まってしまいました。
for文に問題があるのかと思いましたが、やはり分かりません。
どなたか、何卒ご助言いただきたくよろしくお願いいたします。
また、一つ疑問なのはif文一つなら検索結果の行が5000ですが
二つにすると約2倍になってしまいます。
プログラム上問題なければいいのですが・・。

Sub Macro1()

　 Dim x　　　　　　　　　'変数の定義
Dim y
Dim i As Integer
Dim j As Integer
　　i = 1
j = 1

x = 10　　　　　　　　　'キーワードの割り当て
y = "AA"

Worksheets("sheet1").range("B1").Activate　'セルの初期設定

For i = 1 To 5000　　　　　　　　　　　 'ループ検索

With ActiveSheet　　　　　　　　　　　　'条件分岐
If ActiveCell.Value = x Then
ActiveCell.Offset(0, 1).Copy　　'キーワードのデータ取得
ActiveCell.Offset(1, 0).Select　'次行へ

Worksheets("sheet2").Select　　 '抽出データの表示
ActiveSheet.range("B1").Activate
ActiveCell.Offset(j, 0).Select
ActiveSheet.Paste

Sheets("sheet1").Select　　　　　'次の検索準備

Else
ActiveCell.Offset(1, 0).Activate　'次のキーワード検索
End If

If ActiveCell.Value = y Then　　　　　'条件分岐
ActiveCell.Offset(1, 0).Copy　　　'キーワードのデータ取得
Worksheets("sheet2").Select 　'抽出データの表示
ActiveSheet.range("B1").Activate
ActiveCell.Offset(j, 1).Select
ActiveSheet.Paste
j = j + 1

Sheets("sheet1").Select　　　　　　'次の検索準備

Else
ActiveCell.Offset(1, 0).Activate　 '次のキーワード検索
End If
End With
Next
End Sub

回答No.1

noname#245936

IT屋のものです。

んー、私も昔、そういうプログラムから始めたような気がします。
Excelマクロは入門しやすいので。
何でもやってみるのはいいことだと思います。

さて、結論から申しますと。

エクセルのセル上、つまり画面上のデータを直接プログラムで
ゴソゴソいじる貴方の方法は、ちょっとしんどいと思います。
何故かというと、
・Excelの表は各セルの条件がとても緩く、入力審査しづらい。
　（例えば、数字を入力すべき所に文字を入れたりできる）
・画面に依存したプログラムを作ると、
　(1)処理が遅い、プログラムがとてつもなく長くなりやすい。
　(2)データの構成が変わったとき（例えば１列でも増えると）
　　プログラムの変更箇所が膨大になる。
　(3)画面依存プログラムは、画面であるソフトのバージョン（Excel）
　　に依存するため、バージョンアップに弱い。
…という理由で、普通はこういうテーブルを扱うプログラムは、
画面とデータを分けて作るのが一般です。
「データベース -> プログラム -> 画面や帳票、Excel」
という感じです。

データベースは一般に、SQLと呼ばれる汎用構文で操作できるので
貴方のDBの場合なら
「SELECT B列,C列 FROM Table Where B列 IN ('X','Y')」という
構文で読めます。
DBアクセスのプログラミングに関しては、別途書籍など参照ください。

なお、Excelのセルを、PickUpする、グループ集計をするなどは
プログラムが無くても、フィルタリング、ソート、ピボット、集計
関数で目的の動きを一瞬でさせることができるので、どうしても
プログラムを書きたければ、そこを制御するプログラムを書いた方が
とても短いプログラムになることが一つ。

また、単にプログラムで行列の配列を単純に取り扱ってみたいと
いうことであれば、構造体配列を使った方がいいことも一つ。
例）
'列の定義
private type typTableCols
ColX as integer
ColY As string
End type
'表の定義
Private tblData() As typTableCols 'コレで２次元配列
…アクセスするときは、tblData(1).ColX = "X" というように
見やすい文章でアクセス可能

もっといえば、単に50,000件以下のDBを扱うなら、手っ取り早く
FileMakerなどの商品を使うのも手。

…と思います。

最近は、何でもかんでもプログラムを書かず、各ソフトの特徴を知り
便利な部分だけをつなぎ合わせたようなプログラムを書くほうが
効率的ではあります。
…が、何でもチャレンジはいいことだと思います。
がんばってください。

なお、Excelで完全にDBアプリを作ろうとすると、まず難問に
かかるのは、「行確定の判断」になるでしょう。

お礼

早速のご回答、誠にありがとうございます。

仰せのとおり、出来上がっているものを使用することは
考えました。(必要に迫られているので。汗)
オートフィルタなどで、フィルタすると抽出できるデータ(ｘ)とその後のデータ(ｙ)の相関が取れなくなることが問題になり悩んでいます。

やはり、小生のレベルでチャレンジするのは無理があったのか
う～ん、どうしよう。。