大量のセルアドレスを個別に書き換える方法が分からず困っています。 A

Question

大量のセルアドレスを個別に書き換える方法が分からず困っています。
            A              B
1     =$A$1       101
2     =$A$1       201
3     =$A$1       301

A列にはセルアドレス、B列には置換したい行が入力されています。これを下記A列のように一気に置換したいのですが、出来るでしょうか。

A              B
1     =$A$101    101
2     =$A$201    201
3     =$A$301    301
ここではアドレスだけの式にしましたが、実際には関数内の引数になります。
アドレスは絶対／相対どちらでも構いません。

ご教示頂ければ助かります。

tatsu99 · Accepted Answer

No8です。
文字数がおおいので、下記サイトに記述しました。
標準モジュールへ登録し実行してください。
http://climbi.com/b/8465/0
使用時の注意事項はNo8に記述した通りです。
不明点、不具合、改造要望などがあれば、補足してください。

tatsu99 · Answer

補足ありがとうございました。
補足に対しての質問です。

質問１：
　A　　　B
1 $B$1, 4
2 $B$1, 10
3 $B$1, 14

この例では、A列の$1を$4,$10,$14に変えますが、その変換先の値は、右隣のセル（B列）に記述されています。
そうすると、行の変換は、$1を右隣のセルの値で変換する。(例 $1->$4)(右隣が4の場合)
ということで良いでしょうか。

質問２：
列の変換は、$A->$X等のパターンで良いですか。
変換前：$A～$Zの何れか
変換後：$A～$Zの何れか

質問３：
変換用の外部シートを以下のように定めますがよろしいでしょうか。(シート名は変換定義シート)
もし、変更（又は追加）してほしい部分があれば、その旨、補足ください。
シート名：変換対象のシート名を定義する。（例 Sheet1)
変換対象列：変換対象の列を定義する。（例　B）（１列のみ定義可能）
変換前列：変換前の列を定義する。（例　$A）(有効値は$A～$Z)（空白の時、列の変換は行わない）
変換後列：変換後の列を定義する。（例　$F）(有効値は$A～$Z)（空白の時、列の変換は行わない）
行変換：TRUE,FALSEの何れかを指定する。（TRUEの場合、実施する）変換方法は質問1の方法で行う。

添付の図を参照ください。
実際の定義を参照するのは、2行目のA列からE列です。

上記の仕様で問題ないか確認をお願いいたします。

tatsu99 · Answer

＞B列の数値をもう少し説明しておくと、測定器から出力されるcsv形式のファイルで、ここからアドレスを指定して対象のデータを読み込むことになります。出力されるデータのフォームが一定ではないため、読み出し対象アドレスが一定間隔ではありません。
＞この中から対象データのアドレスを抽出した外部参照リストを別途作っておき、行アドレスを抽出しています。これがB列に書いた数値です。

すみません。全くイメージがわいてきません。
具体的な例で示して頂けませんでしょうか。
１）CSVデータの例と
２）外部参照リストの例と
３）excelのB列の例

を提示していただければ判りやすいです。
もし、外部参照リストの例にいくつかのパターンがあるなら、
その例も示してください。
多分、それが行を変換するときの（あなたが提示された）以下のパターンにつながっているかと思います。
>最初の例の場合はご呈示の通りです。一方、下二桁が01で固定ということではありません。
＞変動数が3桁の固定数の場合や不等間隔の場合もあります。

WindFaller · Answer

こんにちは。

>測定器から出力されるcsv形式のファイルで、ここからアドレスを指定して対象のデータを読み込むことになります

最初から、お書きなったほうが良かったですね。掲示板では一番嫌がられるスタイルになってしまいました。それは、私は、なんとなく勘が働いて疑心暗鬼で、ツッコミはしませんでしたが。

もし、きちんと回答者側に質問内容が正しく伝わっていれば、このような内容にはならないような気がします。ふつうその場合は、アドレス位置があっちゃこっちゃということはないのではないでしょうか。ある一定の範囲で、規則性があるはずです。一つ目処が立てば、後は、全部、それに右ならえで決まっていくはずです。

>その他ご不明点などあればお知らせください。

不明な点ではなくて、全体的な作業を把握していくところから必要だと思います。個別のアドレスの書き換えなんていうことではないと思うのです。

CSVデータがどのような出力で、どのようにデータを収拾していくか、設計の段階から、経験者さんたちを交えて相談をしたほうがよいと思います。後出しの条件はダメなのです。

>出力されるデータのフォームが一定ではないため、読み出し対象アドレスが一定間隔ではありません。

人間が、その場所(アドレス)を判断しているわけではないでしょうか。もし、そうなら、たいていのことは可能だと思います。

WindFaller · Answer

マクロで行なうのは一例です。

'//
Sub Test1()
　Dim re As Object
　Dim Ms As Object
　Dim c As Variant
　Set re = CreateObject("VBScript.RegExp")
　With re
　　.Global = True: .IgnoreCase = False: .MultiLine = True
　End With
　re.Pattern = "\$(\d+)"　'行
　''re.Pattern = "\$([A-Z]+)" '列  '上記の行とは、二者択一
　For Each c In Range("A1", Cells(Rows.Count, 1).End(xlUp))
　　Set Ms = re.Execute(c.Formula)
　　c.Formula = Replace(c.Formula, Ms(0).SubMatches(0), c.Offset(, 1).Value)　'隣のセルに置換値を置く
　Next c
End Sub

'//
単純なものなら、これで済みます。しかし、複雑な数式の場合は、
re.Pattern = "\$(\d+)"　'行
この部分を具体的に、"\$(100)" などとします。

tatsu99 · Answer

>>>質問１：変更対象になるのは、A列のみですか。
>必ずしもA列ではありません。列数は１列のみです。

その場合、どの列なのかを情報として外部から与えておく必要があります。
例えば、別シートのA1セルにCと記述した場合、C列が変更対象、というようにです。
（以降、別シートに記述しておくことを外部定義と呼びます））

>>>質問２：変更対象となるのはセルアドレスを示す$1のみでよいですか？
>現在は行のみ対象です。が、将来は列を示すセルと行を示すセルの両方から読んでアドレスを規定出来ると便利だと思っています。余り複雑にならなければ併せて教えていただけると助かります。

その場合、
変更対象が行のみか、またどの行が変更対象か（今回は$1）、
変更対象が列もあるのか、またどの列の値が変更対象か
の情報を外部に定義する必要があります。

>>>質問３：
>$1の変更後の値は、
>1行の場合、$101になりますが、
>10行の場合は、$1001
>11行の場合は、$1101　以降略させてください
>最初の例の場合はご呈示の通りです。一方、下二桁が01で固定ということではありません。
>変動数が3桁の固定>数の場合や不等間隔の場合もあります。

変更方法（変更のパターン）を決めて外部定義にしておく必要があります。
今回のパターンは、行番号＋"01"
３固定のパターン
不等間隔のパターン
などです。
不等間隔のパターンのパターンの場合
1回目の$1->$100
2回目の$1->$123
3回目の$1->$156
などのようにランダムで規則性がない場合は、その回目毎の変更値を決めておく必要があります。
１０００行のセルを変更するなら１０００個ぶんの変更値が必要です。

結論としては、汎用的な変換を望むのであれば
１．変換ルールを決める外部定義が必要、（別のシートが最も簡単）
２．外部定義に変換するためのルールを決めておく。
各セルの位置がどの変換の役割についての定義なのかも決めておく必要あり。
となります。

しかしながら、最初に多くの変換機能を欲張らずに、まずはシンプルな変換のみを実現するマクロを
作成し、徐々に機能を追加して行くのが実現しやすいと思います。

外部定義の方法の１例です。(添付の画像を参照ください)
シート名：変換定義
A1：シート名（見出し）　　 B1：変換するシート名(sheet1と記述した場合、sheet1のシートが変換対象)
A2：変換対象列（見出し）　 B2：変換対象の列（B,C,D等）
A3：変換前列（見出し）　　 B3：変換前の列の値（＄A,$V等）　空白可能
A4：変換後列（見出し）　　 B4：変換後の列の値（＄X,$Y等）　B4が空白の場合参照しない
A5：変換行列（見出し）　　 B5：変換前の行の値（＄1,$10等）　空白可能（但しB3、B5がともに空白はNG）
A6：行変換方式（見出し）　 B6：行を変換する方法　B5が空白の場合参照しない
　　　　　　　　　　　　　　　　1:固定文字を変換後の値とする。
　　　　　　　　　　　　　　　　2:行番号＋固定文字を変換後の値とする。今回のケースなら２を定義
　　　　　　　　　　　　　　　　3:ランダム値（今回サポートしない）
A7：固定文字（見出し）　　 B7：行変換方式が１または２の場合の固定文字。今回のケースなら("01")を定義

追加質問：
提示された例は見出しがないと見受けました。
見出しがあるケースもありますか。
見出しがある場合、２行目以上が変換対象ですが、
2行　$1->$201でしょうか。それとも$1->$101でしょうか。
質問の意図は、行番号＋固定文字("01")の時、セルの2行目を行番号=2とするのか、行番号=1とするのかの意味です。

ママチャリ · Answer

こんな感じでしょうか？
とりあえず、A1~A5を置換してみました。

Sub sample()
    Dim r As Range
    For Each r In Range("A1:A5")
        r.Formula = Replace(r.Formula, "$1", "$" & Cells(r.Row, "B"))
    Next r
End Sub

tatsu99 · Answer

No1です。
追加質問です。
質問４：１つのセル中に$1は１つだけでしょうか。
それとも、複数存在しますか。

tatsu99 · Answer

補足要求です。
質問１：変更対象になるのは、A列のみですか。
質問２：変更対象となるのはセルアドレスを示す$1のみでよいですか。
質問３：
$1の変更後の値は、
1行の場合、$101になりますが、
10行の場合は、$1001
11行の場合は、$1101
100行の場合は、$10001
101行の場合は、$10101
1000行の場合は、$100001
1001行の場合は、$100101でよいですか？

大量のセルアドレスを個別に書き換える方法が分からず困っています。 A

No8です。

補足ありがとうございました。

＞B列の数値をもう少し説明しておくと、測定器から出力されるcsv形式のファイルで、ここからアドレスを指定して対象のデータを読み込むことになります。

こんにちは。

マクロで行なうのは一例です。

>>>質問１：変更対象になるのは、A列のみですか。

こんな感じでしょうか？

No1です。

補足要求です。

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

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

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