Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。

  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
911 / 13645 ﾂﾘｰ ←次へ　｜　前へ→
▼ 【77504】負荷のなくシートないのデータを取得する方法 Show 15/10/17(土) 12:34 質問[未読]
【77506】Re:負荷のなくシートないのデータを取得す... β 15/10/17(土) 16:10 発言[未読]
【77507】Re:負荷のなくシートないのデータを取得す... β 15/10/17(土) 17:26 発言[未読]
【77508】Re:負荷のなくシートないのデータを取得す... β 15/10/17(土) 17:37 発言[未読]
【77514】Re:負荷のなくシートないのデータを取得す... Show 15/10/18(日) 5:11 お礼[未読]
【77564】Re:負荷のなくシートないのデータを取得す... Show 15/10/23(金) 16:36 質問[未読]
【77566】Re:負荷のなくシートないのデータを取得す... β 15/10/23(金) 19:09 発言[未読]
【77567】Re:負荷のなくシートないのデータを取得す... β 15/10/23(金) 19:17 発言[未読]
【77569】Re:負荷のなくシートないのデータを取得す... β 15/10/24(土) 1:58 発言[未読]
【77571】Re:負荷のなくシートないのデータを取得す... Show 15/10/25(日) 5:14 お礼[未読]
【77572】Re:負荷のなくシートないのデータを取得す... β 15/10/25(日) 19:42 発言[未読]
【77596】Re:負荷のなくシートないのデータを取得す... Show 15/10/31(土) 11:23 お礼[未読]
【77504】負荷のなくシートないのデータを取得する...
質問  Show  - 15/10/17(土) 12:34 -
引用なし
パスワード
   シート上にある全てのテキストを負荷を少なく取得する方法ってありますか？
行と列が膨大なファイルがあってできれば、Range で一つ一つ
セルごとにデータを集めるようなことをしたくありません。

目的は何かというと要するに、データの検索に正規表現を使いだけです。
似たようなデータが何行もあるので、検索するに時間がかかります。
正規表現を使っていくつかのキーを組み合わせることによって、
データ内をバックグラウンドでVBAコードで検索し、位置を割り出して、
目視で検索の負荷を減らすの目的です
【77506】Re:負荷のなくシートないのデータを取得...
発言  β  - 15/10/17(土) 16:10 -
引用なし
パスワード
   ▼Show さん：

データの取り出しには、大小は別にして負荷はかかりますね。
セル領域をメモリーに取り込む際に、負荷を減らす効果が期待できる方法の１つが
配列に取り込みことだと思います。たとえば

Variant型変数 = Range("A1:Z1000").Value

こうすれば、瞬時に　26000セルの値が 1000行、26列の２次元配列に格納されます。

Variant型変数 = ActiveSheet.UsedRange.Value

こうすれば、シート上の使用領域の矩形部分が取り込まれます。

取り込んだ後は、メモリー内ですから、１つ１つ処理してもセルを直接参照するより
負荷が、ぐ〜んと減りますが、正規表現を使うなら、できるだけまとめて処理したほうが
よろしいですよね。

いろいろ手立てはあると思います。
２次元配列の各行を１次元配列にして Join関数で文字列にすることもできると思いますし
それこそ、２次元配列内の各要素を、いったん１次元配列に収めて、どさっとJoinしてやれば
１つの文字列になりますので、正規表現チェックも１回ですみますね。

ただ、この場合、該当の抽出部分が、どのセルだったかということをどう把握するか、ここが
工夫のしどころだと思います。

具体的な要件を説明されれば、皆さんからグッドアイデアが寄せられると思います。
【77507】Re:負荷のなくシートないのデータを取得...
発言  β  - 15/10/17(土) 17:26 -
引用なし
パスワード
   ▼Show さん：

たとえば、シートの行単位に処理をすればいいということなら、以下のコードは
シートの行ごとに、その値を１つの文字列に連結するサンプルです。
列数にもよりますが、そんなに大きな負荷にはならないと思います。

Sub Sample()
　　Dim v As Variant
　　Dim s As String
　　Dim i As Long
　　
　　v = WorksheetFunction.Transpose(Range("A1", ActiveSheet.UsedRange))
　　
　　For i = 1 To UBound(v, 2)
　　　　s = Join(WorksheetFunction.Transpose(Application.Index(v, Evaluate("row(1:" & UBound(v, 1) & ")"), Array(i))), "")
　　　　MsgBox s
　　　　's に現在の行のセルの値が連結されているので、それに対する正規表現などによる処理をここで
　　Next
　　
End Sub
【77508】Re:負荷のなくシートないのデータを取得...
発言  β  - 15/10/17(土) 17:37 -
引用なし
パスワード
   ▼Show さん：

もう１つ、シート上のセルの値をすべて１つの文字列として連結させるなら。

Sub Sample2()
　　Dim r As Range
　　Dim s As String
　　
　　Set r = Range("A1", ActiveSheet.UsedRange)
　　
　　r.Copy
　　
　　With CreateObject( _
　　　"new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") 'Ms Forms x.x Object Linrary
　　　　.GetFromClipboard　　　 'クリップボードからDataObjectにデータを取得
　　　　s = .GetText　　　　　'そのデータを取り込み
　　End With
　　
　　Application.CutCopyMode = False
　　
　　s = Replace(Replace(s, vbTab, ""), vbCrLf, "")
　　
　　'ここで領域内のセルがすべて連結されて１つの文字列になっているので、その文字列に対して必要な処理を
　　
End Sub
【77514】Re:負荷のなくシートないのデータを取得...
お礼  Show  - 15/10/18(日) 5:11 -
引用なし
パスワード
   ▼β さん：

丁寧にサンプルと共に説明いただき、ありがとうございます。まだ、提示いただいたコードをまだ試していませんが、まずはお礼がてら返信しました。分からない部分があれば、また質問させてください。
【77564】Re:負荷のなくシートないのデータを取得...
質問  Show  - 15/10/23(金) 16:36 -
引用なし
パスワード
   ▼β さん：

時間がかかりましたが、やりたいことは、一つの問題を除いてできるようになりました。基本的には、提案頂いた方法で(UsedRange)テキストをクリップボードにコピーして、それに対して、改行やタブは残したまま、正規表現で検索してます。改行やタブの情報から、位置情報を割り出して、見つけた値が実際にどのセルにあるのか計算してその後Selectしてます。

一つの問題とは、UsedRange を使っているのでフィルターが掛かっている列がテキストとして取れないことです。UsedRange　を使わずにフィルターされているテキストも取り込む方法はありますか？教えて下さい。何か私の説明に不明な点があれば、教えて下さい。（実際問題として、フィルタされている情報は必要ないのですが、位置情報を割り出すときに、改行を数えて、見つけた列はワークシートの何行目か計算するときに、フィルタされて実際のワークシートの行数とコピーされたデータ内の行数が違うので、行の計算ができないのが問題です。UsedRange から生成されるRangeに対して、Cells を使って相対的にセルを特定できるかやってみましたが、だめでした。）
【77566】Re:負荷のなくシートないのデータを取得...
発言  β  - 15/10/23(金) 19:09 -
引用なし
パスワード
   ▼Show さん：

提示のコードはコピペ利用ですから、フィルターで非表示になっている行は取得不可能です。
私自身は使ったことがないのですが ADO を使ってシートを扱う方法もあるのかなとは思います。
そのほか身の、いくつか手はあるかもしれません。
でも、どうなんでしょうか、処理効率としてはコピペが早いのでは？

ところで、すべての行が必要なら、フィルターをかける前に、この処理をしたらいかがですか？
【77567】Re:負荷のなくシートないのデータを取得...
発言  β  - 15/10/23(金) 19:17 -
引用なし
パスワード
   ▼Show さん：

ふと思いつきました。
フィルターされていても列はすべて表示されていますね。
わからないのは、それが何行目だったかということでしょうかね。
処理の前に、フィルターリストの最後の列の次に、行番号をふっておく。

　　With ActiveSheet.AutoFilter.Range
　　　　With .Offset(, .Columns.Count).Resize(, 1)
　　　　　　.Formula = "=ROW()"
　　　　End With
　　End With

で、その列も含めてコピペすれば、行番号が最後のセルに入っていますね。
【77569】Re:負荷のなくシートないのデータを取得...
発言  β  - 15/10/24(土) 1:58 -
引用なし
パスワード
   ▼Show さん：

↑でいう「処理の前」というのは、「コピペの前」という意味です。
【77571】Re:負荷のなくシートないのデータを取得...
お礼  Show  - 15/10/25(日) 5:14 -
引用なし
パスワード
   ▼β さん：
>▼Show さん：
>
>↑でいう「処理の前」というのは、「コピペの前」という意味です。

回答ありがとうございます。他の人と共有されているファイルなので、残念ながら行番号を追加することはできないんです。それから、フィルタを掛ける前に、データを取るという提案ですが、確かにこの方法しかないですね。フィルタは、データは絶えず入力されるので実際は、現状はフィルタを解除して実行しています。しょうがないですね。

ありがとうございました。
【77572】Re:負荷のなくシートないのデータを取得...
発言  β  - 15/10/25(日) 19:42 -
引用なし
パスワード
   ▼Show さん：

もう、ご覧にならないかもしれませんが。

>他の人と共有されているファイルなので、残念ながら行番号を追加することはできないんです。

提案したコードは現在使われているリスト範囲の外の右側に行番号をセットしています。

ですから

　　With ActiveSheet.AutoFilter.Range
　　　　With .Offset(, .Columns.Count).Resize(, 1)
　　　　　　.Formula = "=ROW()"
　　　　　　●ここでCopy して、クリップボードからの取り込み処理
　　　　　　.ClearContents
　　　　End With
　　End With

このようにすれば、問題はないと思いますが？
【77596】Re:負荷のなくシートないのデータを取得...
お礼  Show  - 15/10/31(土) 11:23 -
引用なし
パスワード
   ▼β さん：
お礼が遅くなって申し訳ありません。確かにうまく行きました。ありがとうございます。

フィルタの掛かっている状態を記憶するような周りくどいことせずにすみました。本当に助かりました。
  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
911 / 13645 ﾂﾘｰ ←次へ　｜　前へ→
ページ：  ┃  記事番号：
2610219
(SS)C-BOARD v3.8 is Free