[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『必要な行データだけを配列へ格納』(やっぱり初歩)
[A] [B] [C] [D] [E] [F] 日付 名前 D1 D2 D3 D4 [1] 02/10 必要 500 800 1200 400 ←欲しいデータ [2] 05/18 xxxx 6000 50 300 900 [3] 06/23 yyyy 250 660 1800 200 [4] 09/15 必要 500 350 100 800 ←欲しいデータ [5] 12/06 zzzz 1000 200 1160 700 |
この様なシートから名前[必要]だけのデータ[A列-F列]を配列へ格納するには どうしたらいいのでしょうか?
不細工な方法は考えました。 (1) 該当する名前の行を検索する。 (2) 必要な行の列範囲を別の場所へコピーする。 (3) 最後に、集めたデータ範囲で配列変数へ格納する。 この手順で操作すれば何とかなる。
しかし、何とも泥臭くだらだら長いコードなります。 お知恵をお貸し下さい。
補足です。
シートは読込だけとして、VBAの中だけ処理が出来ればと思います。 宜しくお願いします。 (やっぱり初歩) 2014/10/26(日) 14:42
< 使用 Excel:Excel2010、使用 OS:Windows8 >
どんなふうに泥臭くなるのか.... 私だったら、ループするけど
参考まで
Sub test() Dim a, x With Cells(1).CurrentRegion With .Offset(1).Resize(.Rows.Count - 1) a = .Value x = Filter(.Parent.Evaluate("transpose(if(" & _ .Columns(2).Address & "=""必要"",row(1:" & .Rows.Count & "),char(2)))"), Chr(2), 0) End With If UBound(x) > -1 Then a = Application.Transpose(Application.Index(Application.Transpose(a), _ Evaluate("row(1:" & UBound(a, 2) & ")"), x)) .Offset(, .Columns.Count + 2).Resize(UBound(a, 1), UBound(a, 2)).Value = a End If End With End Sub (seiya) 2014/10/26(日) 19:01
seiyaさん
早速のご返答有難うございます。 まことに申し訳ありません。今日は疲れていて理解する事が出来ません。 明日の昼までに理解したいと思っています。必ずレスします。 お休みなさい。
(やっぱり初歩) 2014/10/26(日) 20:44
seiyaさんへ
追記 実行した所、私の望んだ結果となっていました。(内容は把握していません) 初見では一旦検索結果をシートに書出した後に、配列へ格納している様に思います。 私としては、シート書出しせずに出来ないものかと思っています。 ※内容把握が出来れば必ずレスします。
(やっぱり初歩) 2014/10/26(日) 20:56
>初見では一旦検索結果をシートに書出した後に、配列へ格納している様に思います。 そのように見えるんですかね... (seiya) 2014/10/26(日) 21:28
>しかし、何とも泥臭くだらだら長いコードなります。 と言う事は、コードは一旦書いてみたのでしょうか?
手順のうち (2) 必要な行の列範囲を別の場所へコピーする。 ↓ (2) 必要な行の列範囲を配列に格納する にしたら出来そうに思いますが。
つくってみたコードを載せてみられてはどうでしょう? (HANA) 2014/10/26(日) 21:54
昨日の最後に何も考えずレスした事をお詫びします。 今日の朝から内容把握に努め、不明な点は原文を分解しながら行ってきました。 私とって全てを理解する事は到底出来ないものと思いました。此の儘の利用なら良いが・・・ 昨日約束した把握状況を報告します。(昼迄のリミットが来たからです) 尚、不明な点は★印を付けています。 更には、まだ考察中です・・・
Sub Test() Dim Ar, x, y, z '変数を確認の為に追加 Dim aa, bb, cc, dd
With Cells(1).CurrentRegion With .Offset(1).Resize(.Rows.Count - 1) Ar = .Value
'↓リテラル値作成 aa = "Transpose(If(" & .Columns(2).Address & "=""必要""" bb = ",Row(1:" & .Rows.Count & ")" ' ←,Row(1:5) cc = aa & bb dd = cc & ",char(2)))" ' 最終結果は Transpose(If($B$2:$B$6="必要",Row(1:5),char(2)))
z = .Parent.Evaluate(dd) '←★(1) .Parent は何故必要?(約束事だからと理解する) x = Filter(z, Chr(2), 0) '←★(2) Chr(2) は除外の為?
' [原文] 'x = Filter(.Parent.Evaluate("Transpose(if(" & _ ' '.Columns(2).Address & "=""必要"",Row(1:" & .Rows.Count & "),char(2)))"), Chr(2), 0) End With If UBound(x) > -1 Then 'UBound(x)が[0]は→要素が1個の時
aa = Application.Transpose(Ar) bb = "Row(1:" & UBound(Ar, 2) & ")" ' "Row(1:6)" cc = Evaluate(bb)
Ar = Application.Transpose(Application.Index(aa, cc, x)) ' ★(3) Application.Index(aa, cc, x) ここが良く分からない
'[原文] 'Ar = Application.Transpose(Application.Index(Application.Transpose(Ar), _ ' 'Evaluate("Row(1:" & UBound(Ar, 2) & ")"), x))
' ※↓の行が実行結果としてシートに反映される為に誤解しました。 ' .Offset(, .Columns.Count + 2).Resize(UBound(Ar, 1), UBound(Ar, 2)).Value = Ar End If End With End Sub
★(4)『↓リテラル値作成』の行以降で使用されている[Row]は配列に対する[Row]でしょうか?
丁寧に書いて頂いた原文を汚して済みません。宜しくお願いします。
(やっぱり初歩) 2014/10/27(月) 11:57
質問は 「配列の特定行を抜き出す」 であるのでIndex関数を用いて実行する。
Index関数で配列内の複数の特定 列・行 を抜き出すには、第二、第三引数に配列を指定する必要がある。 そして、第二引数は二次配列であることが求められるので、Evaluate method を使用している。
以下のコードをDebugすれば、よりわかりやすいとおもいますよ?
Sub test() Dim a, x, y, z With Cells(1).CurrentRegion With .Offset(1).Resize(.Rows.Count - 1) a = .Value x = Filter(.Parent.Evaluate("transpose(if(" & _ .Columns(2).Address & "=""必要"",row(1:" & .Rows.Count & "),char(2)))"), Chr(2), 0) y = Evaluate("{" & Join(x, ";") & "}") z = Evaluate("transpose(row(1:" & .Columns.Count & "))") End With If UBound(x) > -1 Then a = Application.Index(a, y, z) .Offset(, .Columns.Count + 2).Resize(UBound(a, 1), UBound(a, 2)).Value = a End If End With End Sub
ちなみに >z = .Parent.Evaluate(dd) '←★(1) .Parent は何故必要?(約束事だからと理解する) Evaluate method を用いたセル参照のある計算では、特定シートの指定が無い限り ActiveSheetのセルを参照する。 もし対象シートがActiveでない場合は予測しない結果になるので要注意。 (seiya) 2014/10/27(月) 13:01
↑ Evaluate method の subscript には文字数制限(256文字) があるのでデータが多い場合は使用できない。 これは現実的なものでは無く、あくまでも参考程度,,, (seiya) 2014/10/27(月) 13:08
seoyaさん
何度も見ていますが、『やっぱり初歩』の私です。ただ茫然と画面を見て既に3時間以上経過しています。 Ar = Application.Transpose(Application.Index(aa, cc, x)) ★(3) Application.Index(aa, cc, x) ここが良く分からない aa, cc, x が配列である事は分かりました。そして、夫々の内容と式から漠然とながら想像出来ます。
然し、何でこうなるのか理解出来ません。出来ればもっと噛み砕いた表現で説明して頂けないでしょうか。 何とも済みません・・・ (やっぱり初歩) 2014/10/27(月) 17:53
上記のコードをLocalWindowを表示してDebugすれば、変数がわかるはず。 Index, Evaluate その他特定の関数/メソッドの内容を質問してるのかな? (seiya) 2014/10/27(月) 18:11
seiyaさんへ
未だ理解する事が出来ません。何とか理解したいと思っています。Debug中です。 おっしゃれる様にIndex, Evaluate などの関数も最初から勉強している状況です。 これは自分で少しずつでも物にしたいとです。 配列を使いこなす事は大変な作業だなと感じる次第です。(自分には重荷かな…)
最後に .Columns(2).Address & "=""必要"",row(1:" ・・・
ターゲットの『必要』{= Sub xxx(Target As String)の引数} は どの様に記述するのでしょうか。色々試しましたが["必要"]では無く[必要]となってしまいます。 宜しくご指導下さい。 (やっぱり初歩) 2014/10/28(火) 11:51
> ターゲットの『必要』{= Sub xxx(Target As String)の引数} は > どの様に記述するのでしょうか。色々試しましたが["必要"]では無く[必要]となってしまいます。 質問が理解できません。 (seiya) 2014/10/28(火) 12:58
seiyaさん 拙い説明で済みません。質問の具体的内容は他からの呼出し時です。
他から Call Test("家族") と出呼出す
Sub Test(Target As String) (プロシージャ内) : ・・・"transpose(if(" & .Columns(2).Address & "=" & Target & ",row(1:" & .Rows・・・ この行で『型が一致しません』というエラーが出ます。
調べた結果のリテラル値 本来は["]で囲まれる ・・・"transpose(if($C$5:$C$17="家族",row(1:13),char(2)))" ↓ 実体は["]が付かない ・・・"transpose(if($C$5:$C$17=家族,row(1:13),char(2)))"
引数の文字列を["]で囲んむ方法が分かりません。色々と試行はしましたが… こんな事も分からなくて済みません。宜しくお願いします。
(やっぱり初歩) 2014/10/28(火) 15:08
質問の追記 >Evaluate method の subscript には文字数制限(256文字) があるのでデータが多い場合は使用できない。 >これは現実的なものでは無く、あくまでも参考程度,,,
(1)この事は例えば1万行とか大きなテーブルを意味するのでしょうか? (2)それとも、subscript の文字数制限(256文字) に関わる事なんでしょう? (3)非現実的であるという事とはどんな事でしょうか?
これらの事も気になりました。何度も済みません。
(やっぱり初歩) 2014/10/28(火) 15:20
"transpose(if($C$5:$C$17=""" & Target & """,row(1:13),char(2)))" かな?
Evaluate(256 文字以内) ということです。 もし抽出行が多数発生した場合 y = Evaluate("{" & Join(x, ";") & "}") の Join(x, ";") の文字数が制限を超えるかもしれないということです。
エラーの可能性が大であり、かつ可読性が乏しい、という意味で現実にはそぐわない 手法でしょう。
シートを使用しないで処理したいなら、私は素直にループしますね、 (seiya) 2014/10/28(火) 16:26
質問者さんが何に悩んでいるか分かりませんが、seiyaさんの解答を応用すれば ループでも同じくらい簡単に記述出来ますよ。
こんな感じに。 Sub コール用() Call ループ("必要") End Sub Private Sub ループ(ByVal Target As String) Dim tbl: tbl = Cells(1).CurrentRegion.Value Dim dic As Object: Set dic = CreateObject("Scripting.Dictionary") Dim App As Application: Set App = Application Dim i As Long, Result dic.Add 1, "タイトル行" For i = 2 To UBound(tbl, 1) If tbl(i, 2) = Target Then dic.Add i, "データ取得行番号" Next i If dic.Count > 1 Then Result = App.Transpose(App.Index(tbl, dic.keys, [{1;2;3;4;5;6}])) 'Sheets.Add: Cells(1).Resize(UBound(Result, 1), UBound(Result, 2)).Value = Result '//出力 End If End Sub ※17:48ちょっと修正
(稲葉) 2014/10/28(火) 17:33
seiyaさん 早速のご返答有難うございます。 私の現実は粗筋は何とか理解すれど、一歩踏込むと知識不足でその先が全く見えません。
>シートを使用しないで処理したいなら、私は素直にループしますね、 実際に、それでは何をループするのかをも分かりません。この様な次第です。
10/26 seiyaさんの最初の記述 x = Filter(.Parent.Evaluate("transpose(if(" & _ .Columns(2).Address & "=""必要"",row(1:" & .Rows.Count & "),char(2)))"), Chr(2), 0)
では6500行のテーブルを走らせましたが問題ありませんでした。つまり、Evaluate method の subscript への影響は殆ど無いからだと思います。 ※他にも問題が有れば別ですが・・・ 私としては一から勉強してseiyaさんのご指導内容を最後まで把握したいと思っています。
>『可読性が乏しい』 この点は自分で処理します。(見るのは誰も居ません。利用する者は私だけです。) ■出来ますれば、私の★印の付いた質問事項をご教示して下さい。
稲葉さん 有難うございます。取敢えず送信します。後ほど良く理解したいと思います。
(やっぱり初歩) 2014/10/28(火) 17:57
ループするなら単純にしかも確実にエラーフリーとなるように。
Sub test() Dim a, i As Long, ii As Long, n As Long With Cells(1).CurrentRegion a = .Value: n = 1 For i = 2 To UBound(a, 1) If a(i, 2) = "必要" Then n = n + 1 For ii = 1 To UBound(a, 2) a(n, ii) = a(i, ii) Next End If Next .Offset(, .Columns.Count + 2).Resize(n).Value = a End With End Sub
Tanspose/Index も制限あり。 (seiya) 2014/10/28(火) 18:13
seiyaさん/稲葉さん
大変有難うございます。この方法は大変理解し易いです。多分、今迄の1/10位で理解出ると思います。 直ぐに自分のPGの中に取込む事が出来ると思います。 (これがループですね。確かに!)
率直に言います。実はシート関数の使い方等は99%知りません。いつも調べてばかりで事が進みません。 その事をお伝えせず質問していた事は済みませんでした。VBAも素人なのですが・・・
結果は明日の昼までには結果をお伝えします。有難うございました。
(やっぱり初歩) 2014/10/28(火) 18:32
こんにちは。
> 最後に .Columns(2).Address & "=""必要"",row(1:" ・・・ こういうときこそマクロの記録です(^^) 最終的に作りたい数式をどこかに書いておいて マクロの記録開始、適当なセルに↑の数式を書き込み、マクロの記録終了。 記録されたコードを見ると、「"」の扱いがわかります。 (R1C1方式で記録されるので、セル参照は慣れないといや〜んな感じですが^^;
( 佳 ) 2014/10/28(火) 18:46
配列を使用して確実な結果を期待するなら、配列に対してのTranspose/Index 等の関数の使用は極力 避けた方が無難でしょう。
最大処理可能行数 = 2^26-1 = 65536 の他にも制限があるので要注意。 (seiya) 2014/10/28(火) 18:52
>ループするなら単純にしかも確実にエラーフリーとなるように。 確かにそうですよね・・・いつも真似してばかりですみません。
やっぱり初歩さんが最後まで勉強したいということなら、ということで少し seiyaさんが最初に提示されたコードに変数を加えて、出来るだけ分かりやすいように しました。 自分も昔seiyaさんに教えてもらったときがありましたので、少しでも参考になれば。
Sub test() Dim a, x, y, z Dim exp1 As String Dim exp2 As String Dim exp3 As String Dim exp4 As String With Cells(1).CurrentRegion With .Offset(1).Resize(.Rows.Count - 1) a = .Value exp1 = "Transpose(If(" & .Columns(2).Address & "=""必要"",Row(1:" & .Rows.Count & "),Char(2)))"
exp2 = .Parent.Evaluate(exp1) x = Filter(exp2, Chr(2), 0) exp3 = "{" & Join(x, ";") & "}"
y = Evaluate(exp3)
exp4 = "Transpose(Row(1:" & .Columns.Count & "))" z = Evaluate(exp4) Stop End With If UBound(x) > -1 Then a = Application.Index(a, y, z) .Offset(, .Columns.Count + 2).Resize(UBound(a, 1), UBound(a, 2)).Value = a End If End With End Sub
expシリーズを追加しました。 exp1の中身を見てください。 "Transpose(If($B$2:$B$6="必要",Row(1:5),Char(2)))" こんな感じになっていますよね。(Transposeは後述します) IF以降の式では、B列が必要なら行番号(ROW(1:5))、 そうでなければ文字コード2番(Char(2))の配列を返しなさい という、ワークシートの関数を文字列型で記述したものです。
exp2の中身を見てください。 Evaluate関数で、exp1の式を実行した結果です。 exp2(1) = "1" exp2(2) = "・" exp2(3) = "・" exp2(4) = "4" exp2(5) = "・" こんな感じになっていると思います。 "・"がChar(2)の文字です。
xを見てください。 Filter関数でexp2の"・"を除外した配列を入れています。 x(0) = "1" x(1) = "4"
exp3を見てください "{1;4}"という文字が入力されています。 これはワークシートの数式に使う配列を表しています。 ; が行(1次配列)の区切りです。
exp4を見てください "Transpose(Row(1:6))"となっていますね。 こちらもワークシートの関数です。 Row関数は配列を返しますが、下記のように列番号を含んだ2次配列になります。()内は添え字 (1,1) = 1 (2,1) = 2 (3,1) = 3 (4,1) = 4 (5,1) = 5
これを下記のような一時配列に置き換えるためにTranspose関数を使っています。 (1) = 1 (2) = 2 (3) = 3 (4) = 4 (5) = 5
そうすると、最後のIndexではこのようになります。 Index(a, {1;4} , {1;2;3;4;5;6}) Index関数は、Index(2次配列,行番号,列番号) となっていますので、a配列の1行目と4行目の1〜6列を抽出しなさい という感じです。
※yのところで2次配列になっているのは私もよくわかっていませんが・・・ (稲葉) 2014/10/28(火) 18:55
こんにちは^2
理屈です。
文字列は、「"」と「"」で挟みます。 こんな感じです。"あああいいい" 。
「あああ"いいい」としたい場合、 ちょっと考えると「"あああ"いいい"」としたくなりますが エクセル君の立場で考えてみると、、、、
ああ、エクセル君はコードを前から順に読んで解釈するのですが まず最初に「"」があるのでエクセル君は「文字列が始まるんだな」と考えます。 それから「あああ」とすすんで「"」が来たので、「はい、文字列終了っと」 済んだ済んだとお茶でも飲んでいるところに、続けて「いいい」と来るので 「なんでやねん!」と、エラーになるのです。
2回目に「"」が来たとき、「これは文字列終了の意味じゃないよ」とエクセル君に 伝えられるといいのですが。 それで、VBという言語を作ったプログラマさんは、 「"」を2つ重ねて「""」としたら、文字列終了の意味じゃない、「"」という文字 そのものなんだ というルールを決めて、出荷前のエクセル君に教え込んだのでした。
( 佳 ) 2014/10/28(火) 19:02
こんにちは^3
> 実体は["]が付かない ・・・"transpose(if($C$5:$C$17=家族,row(1:13),char(2)))"
違っていたらすみません。 実体、、、って、なんですか。 やっぱり初歩さんご自身の言葉で説明できますが?
いや、理解できない言葉を使ってものを考えることはできないので。 難しいことをするときは使い慣れた道具(リアリティのある単語)を使いましょう^^
( 佳 ) 2014/10/28(火) 19:18
やっぱり初歩さん
ループしない方のコードは忘れてください。 そのコードを投稿した理由は、
>しかし、何とも泥臭くだらだら長いコードなります。
この一言で、ループコードは書いたけどしっくりこない、と感じてしまいました。
>(これがループですね。確かに!)
最初からループコードを投稿していれば、必要もない変なコードで考え込んでしまうことも 無かったでしょう....
とにかく、初めのコードは私も実際には使用しないような不完全なコードなので忘れてください。
(seiya) 2014/10/28(火) 19:54
seiyaさんへ どうも有り難うございました。最初の段での”泥臭い…”という言葉がご迷惑をお掛けした事をお詫びします。 又、私はseiyaさんのご返答を理解するのに時間が掛かって、自分勝手な言葉で質問を続けてきた事も反省しています。 長い間、有難うございました。
稲葉さんへ 私にも分かり易く記述して頂き有難うございます。 実際には未だに Evaluate と Index の理解が十分ではないのですが改めて、納得が行くまで理解に努めます。 夫々の引数が配列ですが、各々の要素数の違いが私を混乱させていました。
佳さんへ > 実体は["]が付かない ・・・"transpose(if($C$5:$C$17=家族,row(1:13),char(2)))" この事は昨日の稲葉さんのコードの中の exp1 に当たります。 この exp1 を Evaluate へ渡す時は文字列でなければならない。つまり、『家族』→『"家族"』。 Sub Test(Target As String) の Target を exp1 へ組込むと『家族』なってしまいます。 尚、 exp2 As Variant とします。
私の理解が間違っていれば申し訳ないのですが、リテラル値というのは Evaluate 等へ渡す文字列を 指す言葉として使っています。文字列であっても意味の有る文字列という意味で捉えています。 間違っていましたらご指摘ください。
皆さん有難うございました。
(やっぱり初歩) 2014/10/29(水) 07:30
ああ、exp2の型宣言すみません。 コメント書きながらコード付けたしていたので、後で直すの忘れました。
(稲葉) 2014/10/29(水) 08:36
seiyaさん
昨日のseiyaさんの最後のコードでは最初のテーブルデータと同じものが出来上がります。 ここから必要なレコードだけをどの様に抽出した良いのでしょうか?
最初、今回の投稿をする前に悩んでいたのは Ar=Cells(1).CurrentRegion として 目的のレコード検索迄は良いのですが、それをどの様にして必要なデータだけを 配列(新しい配列?)へ取込むのか分からなかったのです。 2次元配列ではRedim Preserveも使用出来ない。(もやもや・・・) あれもダメ、これもダメで結局シートへの書出ししかないのかな思っていました。 でも、何とかシート不使用でループ処理だけで出来ないかと思い質問しました。
何とかお力添えをお願いします。 (やっぱり初歩) 2014/10/29(水) 09:14
データのレイアウトが同じなら(B列に 必要 がある行を抽出)、二つのコードは同じ結果になるはずですが? (seiya) 2014/10/29(水) 09:20
もしかして、こういうことですか?
Sub test() Dim a, b, x As Long, i As Long, ii As Long, n As Long With Cells(1).CurrentRegion x= Application.CountIf(.Columns(2), "必要") If x > 0 Then a = .Value ReDim b(1 To x, 1 To UBound(a,2)) For i = 2 To UBound(a, 1) If a(i, 2) = "必要" Then n = n + 1 For ii = 1 To UBound(a, 2) b(n, ii) = a(i, ii) Next End If Next .Offset(, .Columns.Count + 2).Resize(n).Value = b Else MsgBox "No record" End if End With End Sub
変数 b に結果。 (seiya) 2014/10/29(水) 11:01
seiyaさん 早速のレス、大変有難うございます。 直ぐに理解したいのですがたった今、皆さんのコードを参考にしながら仕上げたものを書出します。 送信した後にジックリseiyaさんのコードを勉強したいと思います。
Private Sub ループ(ByVal Target As String) Dim tbl: tbl = Cells(1).CurrentRegion.Value Dim dic As Object: Set dic = CreateObject("Scripting.Dictionary") Dim i As Long, j As Long Dim dCn As Long, cn As Long Dim Ar, kCn
dCn = UBound(tbl, 2) For i = 1 To UBound(tbl, 1) If tbl(i, 2) = Target Then cn = cn + 1 dic.Add i, "データ取得行番号" End If Next If cn = 0 Then Exit Sub kCn = dic.keys ReDim Ar(1 To cn, 1 To dCn) For i = 1 To cn For j = 1 To dCn Ar(i, j) = tbl(kCn(i - 1), j) Next Next End Sub
この様にしました。十分なエラーチェックはしていませんが、取敢えず動いています。 今からseiyaさんのコードを勉強します。
(やっぱり初歩) 2014/10/29(水) 11:45
seiyaさん
見させて頂きました。拙い説明を理解して頂き有難うございました。 seiyaさんのはやっぱり違いますね。私には本当に綺麗なコードとして映ります。 私はもっともっと頑張らなければなりませんね!
(やっぱり初歩) 2014/10/29(水) 11:57
>私には本当に綺麗なコードとして映ります。 ありがとうございます。素直にうれしいです。
コードの内容をしっかり理解する努力はとても大事なことだと思います。 質問して、解決コードが投稿されて、お礼が付いてそこでおしまい。 というスレが殆どですが、わからないところは質問しないと、その先で問題が生じても 対処できないことになるでしょう。
頑張ってください。 (seiya) 2014/10/29(水) 12:14
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.