VBAを使ってデータを追加させるコードを教えて下さい

Question

http://www.geocities.jp/etctransformation/newpage5.html

のデータに、次のHPのテキストデータのalpha2 alpha4 alpha6を追加して
http://wwwndc.jaea.go.jp/nucldata/mass/KTUY05_m246.dat


例えば、
Fe[26,70] = 69.96146;（変更前）→Fe[26,70] = {69.96146,.083, ?.018, ?.006 };（変更後）
Fe[26,71] = 70.96672;（変更前）→Fe[26,71] ={70.96672, .085, ?.025, ?.003 };（変更後）
のように変更させたいです。

ExcelのVBAを使って、データを追加させるコードを教えて下さい。



詳細説明

例えばFe[26,70] の場合ですと元素記号[原子番号（陽子数）,質量数]なので　Fe(元素）[26（原子番号)、70(質量数）]を示します。
従って
ZZ   NN   Mcal    Esh alpha2 alpha4 alpha6　　のZZは原子番号（陽子数）NN（中性子数）を示しまので

26（原子番号） 44（中性子数）が、70=26+44（質量数＝原子番号（陽子数）＋中性子数）なので、Fe[26,70] になります。
よって
26   44 -37.81  -1.22  0.083 -0.018 -0.006
のデータのalpha2 alpha4 alpha6である 0.083 -0.018 -0.006を追加し

Fe(元素）[26（原子番号),70(質量数）]={69.96146(既に入力済データ),0.083(alpha2),　-0.018(alpha4), -0.006(alpha6)};

とさせたいです。


宜しくお願い致します。

WindFaller · Accepted Answer

昨日の回答者です。

>Fe[26,70] = 69.96146;（変更前）→Fe[26,70] = {69.96146,.083, -.018, -.006 };（変更後）になるはずで御座います。

KTUY04-m246S12np の資料からでは、そのデータは出てきません。
昨日作ったものを、Basp21をやめて、RegExp に変更したぐらいで、ほとんど変えていません。それなら正しく出るようです。

Fe[26,70] = {69.96146,.083,-.018,-.006};
Fe[26,71] = {70.96672,.085,-.025,-.003};
Fe[26,72] = {71.96962,.084,-.028,-.004};

'//
Sub AddData_2()
　Dim sh1 As Worksheet　　'元資料
　Dim sh2 As Worksheet　　'加工されるデータ
　Dim Rng As Range, tRng As Range
　Dim fnd_el As String
　Dim j As Long, rw As Long, c As Variant
　Dim buf As String
　Dim Z, a2, a4, a6, fnd_no, fnd_dt, Ele
　Set sh1 = Worksheets("Sheet1")
　Set sh2 = Worksheets("Sheet2")
　Dim RegEx As Object
　Dim Ms, dif As Long
　Set RegEx = CreateObject("VBScript.RegExp")
　With RegEx
　　 .Global = True: .IgnoreCase = False
　End With
　　On Error GoTo ErrHandler
　''-----
　Application.ScreenUpdating = False
　With sh2
　　'1番上がA1の時
　　For Each c In .Range("A1", .Cells(Rows.Count, 1).End(xlUp))
　　rw = c.Row
　　buf = c.Value
　　 If buf <> "" Then
　　　If c.Value <> "" And c.Value Like "[A-Z]* = [0-9][0-9]*" Then
　　　　Ele = Left$(c.Value, InStr(c.Value, "="))
　　　　RegEx.Pattern = "\[(\d{1,}),(\d{1,})\s*\]\s?=\s?(\d[^;]+?);"
　　　　With RegEx.Execute(buf)(0)
　　　　Z = .subMatches(0)
　　　　fnd_no = .subMatches(1)
　　　　fnd_dt = .subMatches(2)
　　　　End With
　　　　Set Rng = sh1.Columns(1).Find(CLng(Z), sh2.Cells(1, 1), xlValues, xlWhole, , , False, True)
　　　　If Not Rng Is Nothing Then
　　　　With Rng
　　　　　dif = CLng(fnd_no) - CLng(Z)
　　　　　j = 0
　　　　　Do
　　　　　　If .Offset(j, 1).Value = dif Then
　　　　　　　Set tRng = .Offset(j)
　　　　　　　Exit Do
　　　　　　End If
　　　　　　j = j + 1
　　　　　Loop While .Offset(j).Value = CLng(Z)
　　　　End With
　　　　If Not tRng Is Nothing Then
　　　　　a2 = tRng.Offset(, 4).Value
　　　　　a4 = tRng.Offset(, 5).Value
　　　　　a6 = tRng.Offset(, 6).Value
　　　　End If
　　　　buf = " {" & fnd_dt & "," & a2 & "," & a4 & "," & a6 & "};"
　　　　buf = Replace(buf, ",0.", ",.")　　'0省略
　　　　buf = Replace(buf, ",-0.", ",-.")
　　　　c.Offset(, 2).Value = Ele & buf
　　　　End If
　　　　Set Rng = Nothing: Set tRng = Nothing
　　　End If
　　 End If
　　Next
　End With
　Application.ScreenUpdating = True
　MsgBox "正常終了しました。", vbInformation
　Exit Sub
ErrHandler:
　MsgBox Err.Number & ": " & Err.Description & " on Rw:" & rw
End Sub

'//
データは、添付画面のように、データの[区切り位置]によって切り分けられています。
位置関係が正しければ、問題は発生しないはずです。
以下はSheet1 A列からG列まで。

WindFaller · Answer

#10のマクロに関して。
マクロよりも、データの位置関係ですが、私のほうは、C列に出力するようになっています。後は画像をご覧ください。

fujillin · Answer

ANo9です。

＞E2がうまく表示しないです。
いろいろな機能を利用してしまい、申し訳ありません。m(__)m
「名前の定義」を利用しています。
（セル範囲に好きな名前を設定しておくと、いちいち範囲指定しなくてもその名前で参照してくれます。今回の場合は第二表全体に名前を設定しました。）
面倒なら、式のなかの table2 とあるところを $G$2:$N$9437 に置き換えてしまえば良いはずです。（式の中に3か所あります）

この際だから「名前の定義」も覚えてしまえ、とおっしゃるでしたら…
「数式」タブの「定義された名前」-「名前の定義」にあります。
説明は例によって…
http://www.eurus.dti.ne.jp/~yoneyama/Excel/name.htm
http://www4.synapse.ne.jp/yone/excel2010/excel2010_name_teigi.html

fujillin · Answer

ANo5です。画像が判然としないので、半分憶測ですが・・・関数式を入れるセルの内容は、式になっていますか？例えば、C2を例にすると　『C2 =IF(A2<>"",MID(A2,SEARCH("[",A2),LEN(A2)),"")』と入力されているような気がしますが、関数式は『=』から始まりますので　C2セルに、『=IF(A2<>"",MID(A2,SEARCH("[",A2),LEN(A2)),"")』を入力するという意味です。（上記のうち『』は入力しません）違うかな…？

fujillin · Answer

ANo7です。

後から気付きましたが、第一表もスペースで区切って、「-」の列を削除すれば、TRIMは不要でしたね。

また、ANo3で提示した表も一回の作業の前提でしたが、繰り返し利用の可能性があるなら・・・
例えば、
『Sheet2のA1に第一表をコピペ、Sheet3のA1に第二表をコピペ』
するだけで、データの区切りや整形も関数で自動的に行うようにセットするなどしておけば、更新も簡単にできるようになると思います。
ANo5の表も、使いやすい順序や表示状態にセッティングしておいたほうが良いでしょう。
（構想や関数の設定など、準備に少し時間がかかると思いますので、利用頻度とのバランスですね。）

fujillin · Answer

ANo3、5です。

＞画像のようにA列にすべてのデータが入ってしまいます。
＞どのようにしたら、A～G列にコピペできるでしょうか？
回答にも書きましたが「データ区切り」を利用します。
コマンドは「データ」タブの「データツール」-「区切り位置」にあります。
第二表はスペースで分割するだけでＯＫのはずです。
第一表は「=」で分割してから、TRIMで余分なスペースを取り除き、さらに行末の「；」も取り除きました。

「データ区切り」の説明は、言葉で書くよりも解説サイトの方がわかりやすいと思いますので。
http://www.becoolusers.com/excel/text-to-columns.html
http://www4.synapse.ne.jp/yone/excel2010/faq/kugiri.html

※ エクセルは解説してくれているサイトが豊富にありますので（旧いものもありますが）、キーワードさえわかっていれば、それで検索する方が早い場合も多いですよ。

ついでながら、関数式などで表示させている値を、実際の値として変換したい場合は、対象セル範囲をコピーしてから、「形式を選択して貼り付け」で「値」をペーストすることで変換が可能です。

tatsu99 · Answer

No1です。
下記URLに結果を載せました。
http://climbi.com/b/10006/0

fujillin · Answer

ANo3です少し時間が取れたので、表計算で行ってみました。(提案した手前…（笑）関数を調べながらでしたので、若干時間がかかったものの、30分弱程度でした。元となる表の値が変わって再計算する場合でも、元の表データを入れ替えるだけで済みますので、以後は5分程度の作業でできるものと思います。（当初はNI[53,28,70 ] = 69.93650;形式のデータだったのを、実際に2分くらいで差し替えています）＞行の整形は可能でしょうか？データ区切りを利用してください。また、余分なスペースを取り除くのに、trim関数などを利用しました。以下、添付図を基にした説明です。 (若干見えにくいですが、式は以下に示しましたので、雰囲気が伝わればＯＫ）・A、B列が、コピペ後整形した第一表のデータ・H～Ｎ列が、コピペした第二表のデータ・C,D,G列は作業列で関数が入っています（D列は予定にありませんでしたが、第一表の[]内に余分なスペースがあったりするようなので、後から追加しています）・E列がご質問で求めていらっしゃる値。（太字にしてあります） 2行目各列の関数式は以下です。 C2 =IF(A2<>"",MID(A2,SEARCH("[",A2),LEN(A2)),"") D2 =IFERROR(REPLACE(C2,FIND(" ",C2),1,""),C2) E2 =IF(D2<>"",A2&" = {"&B2&","&VLOOKUP(D2,table2,6,0)&","&VLOOKUP(D2,table2,7,0)&","&VLOOKUP(D2,table2,8,0)&"}","") G2 ="["&H2&","&(H2+I2)&"]" ※ E2の式中にtable2とありますが、第二表の範囲（$G$2:$N$9437）を「table2」と名前の定義で設定してあります。 ※ 2行目のセルで式を設定後、フィルハンドルをダブルクリックすると、表の下まで式がコピーされますので、フィルコピーのために大きな表をドラッグする必要はありません。

WindFaller · Answer

途中でのままで終わってしまった前回の回答者です。
成功することをお約束できないのは仕方がありませんが、もう少し余裕を持って展開をみていただきたく存じます。

一応、昨日の段階で出来上がりました。pdf からのデータの完全な抜き出しにも成功しました。

昨日のデータからのものですが、yahooからのもの643個すべての変換は終わりました。
結果は以下の通りです。VBAマクロによるものですが、Perl 仕様のBasp21(BregExp.dll)を利用しています。ただし、細かく内容的に精査されていません。

fujillin · Answer

こんにちは

VBAの回答ではありませんが・・・

1回だけ行う作業ではないかと想像しました。
それなので、VBAでプログラムを作ってデバッグしているよりも、エクセル上で表計算と手作業を用いた方が遥かに早そうに思います。

ご指定のサイトは、両方とも表型のデータになっているので、コピペでエクセルに取り込み、区切り位置指定で各セルに配分することが可能です。
二つ目のデータ数が多いですが、それでも9400程度なので、エクセルの行数で十分間に合います。
試しに、手作業で両方のデータを取り込んで、無駄なスペースを削除などの整形をするまでで5分も要しませんでした。